《Excel实战技巧精粹》示例目录.xls

第第二二篇篇 ExcelExcel基基本本功功能能第第三三篇篇 数数据据分分析析第第四四篇篇 函函数数导导读读 第第五五篇篇 函函数数技技巧巧第第六六篇篇 函函数数高高级级应应用用第第七七篇篇 图图表表与与图图形形第第八八篇篇 VBAVBA实实例例与与技技巧巧技技巧巧第第二二篇篇ExcelExcel基基本本功功能能技技巧巧2222 简简繁繁转转换换不不求求人人技技巧巧2424 为为工工作作簿簿减减肥肥技技巧巧2929 快快速速填填充充所所有有空空白白单单元元格格技技巧巧3939 隔隔行行插插入入的的绝绝招招技技巧巧4040 在在非非空空行行之之间间自自动动插插入入空空行行技技巧巧4141 快快速速删删除除所所有有空空行行技技巧巧5252 自自动动填填充充的的威威力力技技巧巧5858 提提取取超超链链接接信信息息技技巧巧6767 ExcelExcel的的摄摄影影功功能能技技巧巧6868 奇奇妙妙的的自自定定义义数数字字格格式式技技巧巧7070 自自定定义义数数字字格格式式的的经经典典应应用用技技巧巧7272 合合并并单单元元格格的的同同时时保保留留所所有有数数值值技技巧巧7373 同同一一个个单单元元格格里里的的文文本本设设置置不不同同格格式式技技巧巧7474 制制作作斜斜线线表表头头技技巧巧7676 工工作作表表背背景景图图片片的的妙妙用用技技巧巧7979 在在名名称称中中使使用用常常量量与与函函数数1 1技技巧巧7979 在在名名称称中中使使用用常常量量与与函函数数2 2技技巧巧8282 创创建建动动态态名名称称技技巧巧8383 图图片片自自动动更更新新技技巧巧8686 以以图图形形方方式式查查看看名名称称技技巧巧8888 美美丽丽的的数数据据区区域域技技巧巧8989 永永恒恒的的间间隔隔底底纹纹技技巧巧9090 快快速速比比较较不不同同区区域域的的数数值值1 1技技巧巧9090 快快速速比比较较不不同同区区域域的的数数值值2 2技技巧巧9292 自自动动醒醒目目的的小小计计技技巧巧9393 在在工工作作日日历历中中突突显显周周休休日日技技巧巧9494 突突显显活活动动单单元元格格的的坐坐标标技技巧巧9595 生生日日提提醒醒技技巧巧9696 特特殊殊数数值值提提醒醒技技巧巧9797 标标记记重重复复值值技技巧巧9898 标标记记含含有有公公式式的的单单元元格格技技巧巧9999 标标记记匹匹配配目目标标数数值值的的单单元元格格技技巧巧100100 八八种种颜颜色色区区分分不不同同数数值值技技巧巧101101 无无限限条条件件的的条条件件格格式式技技巧巧102102 在在单单元元格格中中创创建建下下拉拉列列技技巧巧103103 另另类类的的批批注注技技巧巧104104 快快速速切切换换有有效效性性的的来来源源技技巧巧106106 只只能能录录入入某某种种特特征征的的数数据据技技巧巧107107 限限制制重重复复值值的的录录入入技技巧巧108108 杜杜绝绝负负数数库库存存技技巧巧109109 强强制制序序时时录录入入技技巧巧110110 多多级级选选择择录录入入技技巧巧113113 在在同同一一页页上上打打印印不不连连续续的的区区域域技技巧巧116116 在在报报表表的的每每一一页页上上打打印印公公司司LogoLogo技技巧巧117117 在在页页眉眉或或页页脚脚增增加加分分隔隔线线技技巧巧119119 在在ExcelExcel中中定定时时打打印印第第三三篇篇 数数据据分分析析技技巧巧121121 对对超超过过3 3列列的的数数据据排排序序技技巧巧122122 按按照照特特定定的的顺顺序序排排序序技技巧巧123123 按按笔笔划划排排序序技技巧巧124124 按按行行来来排排序序技技巧巧125125 按按字字符符数数量量排排序序技技巧巧126126 按按颜颜色色排排序序或或筛筛选选技技巧巧127127 随随机机排排序序技技巧巧128128 排排序序字字母母与与数数字字的的混混合合内内容容技技巧巧129129 返返回回排排序序前前的的表表格格技技巧巧131131 在在受受保保护护的的工工作作表表中中使使用用自自动动筛筛选选技技巧巧132132 ExcelExcel多多字字段段分分类类汇汇总总技技巧巧134134 多多层层次次的的数数据据浏浏览览技技巧巧135135 在在受受保保护护的的工工作作表表中中调调整整分分级级显显示示视视图图技技巧巧136136 用用二二维维表表创创建建数数据据透透视视表表技技巧巧137137 组组合合数数据据透透视视表表内内的的日日期期项项技技巧巧138138 快快速速统统计计重重复复项项目目技技巧巧139139 在在数数据据透透视视表表中中添添加加计计算算项项第第四四篇篇 函函数数导导读读技技巧巧147147 单单元元格格的的引引用用方方法法第第三三篇篇 数数据据分分析析技技巧巧153153 函函数数公公式式的的限限制制与与突突破破技技巧巧155155 内内存存数数组组技技巧巧157157 多多单单元元格格数数组组公公式式技技巧巧158158 数数组组的的转转置置和和变变换换第第五五篇篇 函函数数技技巧巧技技巧巧161161 取取得得单单元元格格信信息息技技巧巧162162 取取得得当当前前工工作作表表表表名名技技巧巧164164 字字符符转转换换技技巧巧三三则则技技巧巧165165 重重复复文文本本技技巧巧二二则则技技巧巧166166 字字符符串串比比较较及及清清理理垃垃圾圾字字符符技技巧巧169169 计计算算字字符符出出现现的的次次数数技技巧巧170170 提提取取字字符符串串技技巧巧171171 文文本本合合并并符符号号的的妙妙用用技技巧巧172172 重重复复记记录录自自动动加加编编号号技技巧巧174174 解解读读身身份份证证编编码码信信息息技技巧巧175175 身身份份证证位位数数的的变变换换技技巧巧176176 金金额额数数字字分分列列技技巧巧177177 数数字字转转英英文文序序数数第第四四篇篇 函函数数导导读读技技巧巧178178 人人民民币币金金额额大大写写公公式式技技巧巧179179 根根据据生生日日计计算算农农历历生生肖肖年年份份技技巧巧181181 日日期期与与数数字字格格式式的的互互换换技技巧巧182182 英英文文日日期期的的转转换换技技巧巧183183 将将中中文文日日期期文文本本转转为为日日期期值值技技巧巧184184 计计算算指指定定间间隔隔的的日日期期技技巧巧185185 月月度度季季度度相相关关日日期期计计算算技技巧巧186186隐隐秘秘函函数数DATEDIFDATEDIF技技巧巧187187 星星期期相相关关计计算算技技巧巧188188 工工作作日日相相关关计计算算技技巧巧189189 解解读读时时间间格格式式代代码码技技巧巧190190 将将角角度度显显示示为为度度分分秒秒以以及及转转换换计计算算技技巧巧191191 常常用用数数值值舍舍入入计计算算技技巧巧192192 按按人人民民币币面面额额估估算算备备钞钞数数技技巧巧193193 余余数数的的妙妙用用技技巧巧194194 数数值值的的修修约约技技巧巧195195 产产生生规规定定范范围围内内的的随随机机数数技技巧巧196196 求求解解多多元元一一次次方方程程技技巧巧197197 求求出出成成绩绩最最高高分分技技巧巧198198 设设置置目目标标数数据据的的上上下下限限技技巧巧199199 统统计计选选定定区区域域数数据据个个数数技技巧巧200200认认识识COUNTIFCOUNTIF函函数数技技巧巧201201 单单字字段段多多条条件件计计数数技技巧巧202202 动动态态统统计计及及格格人人数数技技巧巧203203 认认识识SUMIFSUMIF函函数数技技巧巧204204 单单字字段段多多条条件件求求和和技技巧巧205205 使使用用通通配配符符模模糊糊求求和和技技巧巧206206 日日计计帐帐中中的的余余额额累累计计技技巧巧207207 数数据据表表实实现现动动态态小小计计技技巧巧208208 SUMSUM数数组组公公式式计计数数与与求求和和技技巧巧209209SUMSUM函函数数与与SUMPRODUCTSUMPRODUCT函函数数的的区区别别技技巧巧210210 部部门门评评价价等等级级转转换换技技巧巧211211 分分级级累累进进求求和和技技巧巧212212 自自动动筛筛选选_ _隐隐藏藏_ _组组合合下下的的统统计计技技巧巧213213 取取得得各各科科成成绩绩的的最最高高分分技技巧巧214214 统统计计不不及及格格人人数数技技巧巧215215 指指定定条条件件下下求求平平均均值值技技巧巧216216 对对称称剔剔除除极极值值求求平平均均值值技技巧巧217217认认识识FREQUENCYFREQUENCY函函数数技技巧巧218218 统统计计不不重重复复值值的的数数量量技技巧巧219219 单单个个区区域域排排名名技技巧巧220220 多多个个区区域域排排名名技技巧巧221221 中中国国式式排排名名技技巧巧222222 求求第第N N大大的的不不重重复复值值技技巧巧223223 根根据据首首行行（列列）查查找找记记录录技技巧巧224224 多多条条件件区区间间判判断断取取值值技技巧巧225225 确确定定查查找找值值是是否否存存在在技技巧巧226226 根根据据行行列列条条件件返返回回结结果果技技巧巧227227 返返回回引引用用的的单单元元格格地地址址技技巧巧228228 逆逆向向查查询询数数据据技技巧巧229229 模模糊糊查查找找数数据据技技巧巧230230 返返回回字字符符串串中中连连续续数数值值技技巧巧231231 定定位位最最后后非非空空单单元元格格技技巧巧232232 生生成成垂垂直直_ _水水平平序序列列技技巧巧233233 理理解解OFFSETOFFSET函函数数技技巧巧234234 批批量量生生成成工工资资条条技技巧巧235235 建建立立超超链链接接技技巧巧236236 自自动动跳跳转转到到未未输输入入的的单单元元格格技技巧巧237237 统统计计指指定定月月份份的的销销量量汇汇总总技技巧巧238238 取取得得目目标标汉汉字字的的拼拼音音首首字字母母技技巧巧239239 根根据据双双列列条条件件进进行行查查找找技技巧巧240240 返返回回最最后后一一条条数数据据记记录录技技巧巧241241 按按单单条条件件筛筛选选记记录录技技巧巧242242 按按多多条条件件筛筛选选记记录录技技巧巧243243 对对数数据据进进行行排排序序技技巧巧244244 多多关关键键字字排排名名应应用用技技巧巧245245 返返回回单单列列中中的的唯唯一一值值列列表表技技巧巧246246 返返回回双双列列中中的的唯唯一一值值列列表表技技巧巧247247 返返回回数数据据区区域域中中的的唯唯一一值值列列表表技技巧巧249249 取取得得工工作作表表名名技技巧巧250250 取取指指定定路路径径下下所所有有文文件件名名技技巧巧251251 取取单单元元格格属属性性值值技技巧巧252252 取取得得表表达达式式技技巧巧253253 算算式式和和计计算算结结果果技技巧巧254254 养养成成良良好好逻逻辑辑分分析析习习惯惯技技巧巧255255 学学会会逆逆向向思思维维技技巧巧256256 去去除除冗冗余余判判断断技技巧巧257257 使使用用动动态态引引用用技技巧巧258258 使使用用辅辅助助列列和和普普通通公公式式第第六六篇篇 函函数数高高级级应应用用技技巧巧261261 三三维维引引用用取取不不重重复复数数据据技技巧巧262262 跨跨多多表表查查询询技技巧巧263263 跨跨多多表表汇汇总总技技巧巧265265 求求出出各各项项目目极极值值的的合合计计数数技技巧巧266266 数数组组变变换换技技巧巧三三则则技技巧巧267267 N_TN_T函函数数生生成成内内存存数数组组技技巧巧268268 空空行行自自动动填填满满数数据据的的内内存存数数组组技技巧巧269269 生生成成除除当当前前工工作作表表外外的的工工作作表表名名称称技技巧巧270270 对对文文本本进进行行排排序序技技巧巧271271 MMULTMMULT函函数数应应用用技技巧巧273273 记记录录单单元元格格操操作作时时间间技技巧巧274274 产产生生不不变变随随机机数数技技巧巧275275 求求固固定定总总和和的的组组合合技技巧巧276276 有有记记忆忆功功能能的的单单元元格格第第七七篇篇 图图表表与与图图形形技技巧巧277277 去去除除分分类类轴轴上上的的空空白白日日期期技技巧巧278278 折折线线图图中中处处理理空空单单元元格格的的三三种种样样式式技技巧巧279279 让让图图表表自自动动适适应应窗窗口口大大小小技技巧巧280280 快快速速设设置置图图表表字字体体技技巧巧281281 隐隐藏藏接接近近于于零零的的数数据据标标签签技技巧巧282282 在在图图表表中中处处理理负负值值技技巧巧283283 快快速速添添加加图图表表数数据据系系列列技技巧巧284284 在在图图表表中中使使用用透透明明填填充充色色技技巧巧285285 背背景景透透明明的的图图表表技技巧巧286286 在在图图表表中中使使用用QQQQ图图片片技技巧巧287287 制制作作完完全全静静态态的的图图表表技技巧巧288288 控控制制数数据据标标志志的的显显示示位位置置技技巧巧289289 快快速速统统一一多多个个图图表表大大小小技技巧巧290290 使使条条形形图图分分类类轴轴的的标标签签与与源源数数据据列列显显示示顺顺序序一一致致技技巧巧291291 自自定定义义图图表表技技巧巧292292图图表表公公式式SERIESSERIES的的使使用用技技巧巧技技巧巧293293 在在图图表表中中使使用用对对数数刻刻度度技技巧巧294294 制制作作瀑瀑布布图图技技巧巧295295 美美化化三三维维图图表表技技巧巧296296 快快速速制制作作组组合合图图表表技技巧巧297297 随随单单元元格格自自动动变变化化的的图图表表标标题题技技巧巧298298 图图表表背背景景横横向向分分割割技技巧巧299299 图图表表背背景景纵纵向向分分割割技技巧巧300300 图图表表背背景景四四象象限限图图表表技技巧巧301301 复复合合饼饼图图技技巧巧302302 利利用用误误差差线线制制作作数数据据点点交交叉叉线线技技巧巧303303 N N合合一一图图表表技技巧巧304304 在在图图表表中中画画直直线线技技巧巧305305 始始终终突突出出显显示示最最大大值值和和最最小小值值技技巧巧307307 动动态态图图表表辅辅助助列列技技巧巧308308 动动态态图图表表定定义义名名称称技技巧巧309309 动动态态图图表表最最后后7 7天天的的数数据据技技巧巧310310 动动态态图图表表活活动动单单元元格格技技巧巧311311 求求任任意意点点的的坐坐标标技技巧巧312312 美美丽丽的的万万花花规规技技巧巧313313 批批量量绘绘图图技技巧巧314314 数数据据系系列列快快速速着着色色技技巧巧315315 批批量量添添加加数数据据标标签签技技巧巧316316 快快速速将将图图表表输输出出为为图图片片技技巧巧317317 Xbar-RXbar-R控控制制图图技技巧巧318318 双双层层饼饼图图技技巧巧319319柏柏拉拉图图(Pareto(Paretochart)chart)技技巧巧320320 直直方方图图和和正正态态分分布布图图技技巧巧321321 立立体体雷雷达达图图技技巧巧322322 自自定定义义多多轴轴图图表表技技巧巧323323 不不等等宽宽柱柱形形图图技技巧巧324324 任任意意函函数数曲曲线线图图技技巧巧325325 步步进进图图技技巧巧326326 甘甘特特图图技技巧巧327327 比比较较柱柱形形图图技技巧巧328328 盈盈亏亏平平衡衡分分析析图图技技巧巧329329 波波士士顿顿矩矩阵阵图图技技巧巧330330 组组织织结结构构图图技技巧巧331331 ExcelExcel的的五五种种图图示示技技巧巧333333 两两个个阴阴影影的的图图形形技技巧巧334334 图图表表转转为为自自选选图图形形技技巧巧335335 图图形形对对齐齐技技巧巧技技巧巧336336 自自选选图图形形群群的的编编辑辑技技巧巧337337 用用ExcelExcel画画画画技技巧巧338338 自自选选图图形形的的变变形形技技巧巧339339 在在批批注注中中插插入入图图表表技技巧巧340340 条条码码图图形形第第八八篇篇 VBAVBA实实例例与与技技巧巧技技巧巧343343 利利用用日日期期控控件件输输入入时时间间技技巧巧344344 确确定定当当前前单单元元格格所所在在的的页页数数技技巧巧345345 利利用用VBAVBA取取不不重重复复值值技技巧巧346346 提提取取字字符符中中指指定定类类型型的的字字符符技技巧巧347347 替替换换工工具具栏栏按按钮钮的的图图标标技技巧巧348348 制制作作路路径径选选择择对对话话框框技技巧巧349349 从从其其他他工工作作簿簿取取值值技技巧巧350350 自自动动添添加加控控件件及及事事件件代代码码技技巧巧351351定定义义ApplicationApplication级级别别的的事事件件代代码码技技巧巧352352 批批量量修修改改工工作作表表中中的的批批注注技技巧巧353353 快快速速隐隐藏藏周周围围区区域域技技巧巧354354 设设置置缺缺省省目目录录和和文文件件名名技技巧巧355355 在在长长时时间间运运算算中中使使用用友友好好提提示示技技巧巧356356 制制作作个个性性化化启启动动画画面面-1-1技技巧巧356356 制制作作个个性性化化启启动动画画面面-2-2技技巧巧357357 记记录录工工作作簿簿最最后后更更新新时时间间技技巧巧357357 记记录录工工作作簿簿最最后后更更新新时时间间-1-1技技巧巧357357 记记录录工工作作簿簿最最后后更更新新时时间间-2-2技技巧巧358358 判判断断文文件件是是否否已已经经打打开开和和是是否否存存在在技技巧巧359359 人人民民币币大大写写转转换换函函数数技技巧巧360360 按按颜颜色色求求和和与与计计数数函函数数ExcelExcel实实战战技技巧巧精精粹粹示示例例文文件件目目录录示示例例简简介介本文件提供了两个加载项的安装程序，安装后就可以在Excel中使用简繁转换功能。本例展示了一些常见的Excel文件体积虚增的处理方法某些情况下，用户需要在工作表的数据区域中，用“0”或者其他的数字去填充所有的空白单元格，在空白单元格很多的情况下（如本例），利用介绍的方法可既快又准地完成工作借助辅助列和排序的方法，轻松实现隔行插入的方法本例表格中，需要在非空行之间插入空行，这时需要借助Excel的公式来实现如果用户的数据区域中有大量的空行，如何快速找到它们并快速删除呢？本例介绍了两种方法很多Excel用户对“自动填充”这个威力无比巨大的编辑功能知之甚少。本例展示了4种填充方式：等差数字填充、日期的自动填充、文字自动填充及特定内容的填充当用户从网页或其他的超文本中复制数据到Excel工作表中时，所得到的数据中会包含大量的超链接，许多有价值的信息隐藏在这些超链接里面。如本例，用户希望看到真正的邮件地址，可是它们都被包含在B列的超链接中，没有显示出来。Excel并没有提供用于提取超链接信息的功能。在这个需求中，用户可以通过自定义函数的方法来实现如果用户希望在Excel中实现把某一区域的数据同步显示在另外一个地方，除了使用粘贴链接，利用公式来引用原始区域的内容外，还可以使用Excel的摄影功能，方便您更好地完成任务数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格式化。Excel内置的数字格式多种多样，本示例文件用多个例子说明同样的数字被设置为不同数字格式后，显示效果会有哪些改变本技巧中，读者可以学习到一些自定义数字格式的经典实例，内容包括零值不显示、快速放缩数值、智能显示百分比、显示分数以及隐藏某些类型的内容合并单元格是用户在制作表格时常用的命令，它可以把多个单元格显示成一个单元格。通常情况下，如果把几个含有数据的单元格进行合并，Excel会提示“在合并单元格时，如果选择的单元格中不止一个含有数据，Excel将保留左上角单元格中的数据，并删除其余的所以数据”。这在很多时候会让用户觉得为难，利用介绍的方法可以突破Excel的这种局限，在合并单元格的同时保留所有数值在Excel里面，设置单元格格式的对象并不必是整个单元格，也可以只是单元格里面的一部分内容前提是这个单元格存储的是文本型内容。利用这个特性，用户可以把一个单元格中的内容设置成不同的格式，以满足外观上的需要在报表里面制作斜线表头是中国人特有的习惯，遗憾的是Excel并没有直接提供对这一样式的良好支持。本例介绍的方法，可以尽可能完美地解决这个问题在默认情况下，背景图片会平铺在整个工作表中，而且无法被打印出来，本例介绍的技巧能够巧妙地突破这些限制，帮助用户更灵活地发挥背景图片的威力在名称中不但能够使用单元格引用，还能够使用常量与函数。使用常量名称的优点是，可简化公式的编写并使工作表更加整洁，并且可随时可以修改常量名称的定义，以实现对表格中的大量计算公式快速修改。使用函数，如本例的工作簿创建了一个带有求和函数的名称利用OFFSET函数与COUNTA函数的组合，可以创建一个动态的名称，动态名称是名称的高级用法，能够实现对一个未知大小的区域的引用，此用法在Excel的诸多功能中都能发挥强大的威力，见本例在Excel中，使用动态名称与ActiveX控件，能够轻松地实现工作表中的图片自动更新的特殊效果，本技巧中将以制作一个简单的职员资料表为例，使职员的相片能够随着姓名的改变而改变在Excel中有一个鲜为人知的特性，就是能够使用图形方式查看定义了名称的区域，见本例利用Excel的条件格式功能，用户可以为自己的数据区域设置精美而又宜于阅读的格式，这种设置效果不同于的单元格格式，它是完全动态的。本例展示了国际象棋棋盘式底纹和奇偶行不同底纹的风格利用Excel的条件格式功能可实现数据表的间隔底纹效果，但如果对此数据表进行自动筛选操作，则间隔底纹效果就被破坏了，此时可以通过本例的方法设置的间隔底纹就不会受到自动筛选或者隐藏行操作的影响了本例1介绍了利用条件格式对比不同区域中的数值一种方法，本例2条件格式的设置要相对复杂一些在实际工作中，常常需要在表格中使用小计行或小计列来汇总某类数据。使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式很多用户喜欢使用Excel制作工作日历，如本例所示的是某公司人力资源部的工作日历的一部分。使用条件格式可以实现把所有的周休日都醒目地标识出来，避免一些时间安排上的问题了 在Excel中，当激活一个单元格时，活动单元格会被一个黑色边框包围，同时活动单元格的行号与列标也会高亮显示。但是，当表格较大时，这种显示方法对辨别活动单元格在表格中的位置的作用力不足，使用条件格式并借助宏的帮助，可以更美观实用的突显活动单元格的效果许多公司都有向员工赠送生日礼物的传统，对于人事部门而言，需要经常翻阅大量的员工档案，才能保证按时送出每一位员工的生日礼物，这不是件容易的工作。利用Excel的条件格式，可以轻松地解决这个问题，见本例在阅读报表时，如果数据按照其内在的意义类别区别显示，一定能够增加可读性。如本例学生成绩报表中，以60分与90分把所有的分数按不同颜色表示出来，能够让教师更容易关注到学生的最新学习动态在输入数据时，很可能会重复输入每项内容，条件格式可以帮助用户在输入后立即检查刚才的数据是否为重复值，如果是，则标记出来以供进一步核对在较复杂的表格中，有些单元格的数据来自输入，而另一些来自公式的计算。为了避免在公式单元格中误输入数据，能够清楚地区分哪些单元格中包括公式显得很有意义，条件格式能够达成这一目标在许多的科学研究中，需要从大量试验数据中找到与目标数值相匹配的部分，利用Excel的条件格式，能够迅速完成任务。如本例所示的表格中，第一行是目标值，后面的是各组试验数据，现在需要从试验数据中标记出匹配第一行数值的所有单元格同时使用单元格自定义格式与条件格式，能够在一个数据区域中最多使用8种字体颜色来区分不同的数值，如本例Excel的条件格式，最多只能同时设置3个条件，借助VBA的帮助，完全可以实现同时设置无限条件的条件格式。本例用一段简单VBA代码来演示这种效果有许多读者在工作中第一次见到如本例所示的工作表时，会惊奇于单元格中是如何能够做出下拉列表的。本例工作表中，如果选定B2单元格，就会在单元格右侧出现一个倒三角的箭头，单击箭头，会出现一个下拉列表，单击其中的任何一项，该项的内容就被输入到B2单元格中 Excel的批注有两个不足之处：一是当单元格的行高进行设置后，批注框的大小尺寸也会受到影响；二是默认情况下，必须把光标悬停在单元格上方批注内容才会显示出来，利用数据有效性功能，我们可以实现另类的批注效果，克服以上不足，见本例在通常情况下，当用户使用数据有效性时，只能处理一组数据来源，而在某些场合中，数据来源不止一组，是否有某种方法可以为有效性快速切换数据来源呢？方法见本例在许多场合中，用户需要大量录入带有某种特征的数据，并且限制为只能录入符合特征的数据，此时利用数据有效性就能够较好地处理这样的需求。如果数据的特征较为复杂，就必须使用“自定义”条件，通过编写公式来实现在大量录入数据时，避免录入重复的数据是平常工作中经常会遇到的需求。借助数据有效性功能，能够严格限制重复值的录入，确保所有输入值唯一。本例展示了针对单个关键字限制重复录入和针对关键字限制重复值在一般情况下，用户在处理销售业务时需要依据库存余额来确定最大销售量，以避免产生销售指令数量大渝库存余额，出现负数库存的后果。本例中，针对销售表的B2:B10的单元格设置数据有效性，实现禁止输入大于库存数量的销售量的效果用户在输入数据时，经常需要遵循序时录入的规则，即新录入数据的日期绝不能早于已有记录的最大日期，利用Excel数据有效性，可以轻松地完成这样的限制在许多信息系统的单据录入界面中，都有多级选择的功能，即先选择录入某大项，然后选择录入属于此大项的小项。这是非常实用的功能，能够有效避免串项和项目混乱。在Excel中，利用数据有效性能够很容易地实现单级的选择录入，如果配合名称与公式，也能够实现两级和两级以上的选择性录入效果 在Excel中能够实现把多个不连续的区域打印到同一页上，这个办法就是利用Excel的摄影功能在使用Excel制作报表时，为了能让打印效果显得更加精美和专业，在每一页加入公司的Logo是一个很好的方法在Microsoft Office Word里面，当用户在文档中设置了页眉以后，页眉内容与正文之间，会自动出现一条分隔线，使整篇文档的布局显得更加专业。在Excel中，虽然在默认情况下，设置页眉后也不会有同样的分隔线，但是用户仍然可以手动添加一条。本技巧只适应于Excel 2002和更高版本某些用户需要按照计划的时间或频率打印特定的工作表，利用一小段宏代码，就能够在Excel中实现定时打印的功能 对于许多经常使用Excel排序功能的用户来说，“排序”对话框只允许一次性设置3个关键字的限制实在难以满足需要。事实上，Excel的排序的关键字并不受此对话框所限，是可以按任意数量的列作为关键字来排序的，如本例当把表格的数据按数字或字母顺序进行排序时，Excel的排序功能能够很好地工作，但是如果用户希望把某些数据按照自己的想法来排序，在默认情况下，Excel是无法完成任务的。这时就需要创建一个自定义序列，如本例用户需要创建一个有关职务大小的序列在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，然而，在中国人的习惯中，常常是按照“笔划”的顺序来排列姓名的，方法如本例许多用户都一直认为Excel只能按列进行排序，而实际上，Excel不但能按列排序，也能够按行来排序，如本例在实际工作中，用户有时候需要按照字符的数量进行排序。本例展示的是一份歌曲清单，人们习惯按照歌曲名字的字数来把它们分门别类。按时Excel并不能直接按字数排序，如果要达到目的，需要先计算出每首歌曲名字的字数，然后再进行排序在实际工作中，用户经常会通过为单元格设置背景色或者字体颜色来标注表格中较特殊的数据，但是却无法对有颜色的单元格做进一步操作，比如排序和筛选。此时，需要借助Excel的宏表函数，才能够得到单元格的颜色信息在某些情况下，用户并不希望按照既定的规则来排序数据，而是希望数据能够“乱序”，也就是对数据进行随机排序在日常工作中，用户的表格经常会包含字母和数字混合的数据，在对这样的数据排序时，结果总是令人无法满意的。如果希望让Excel改变排序的规则，需要将数据做一些改变，如本例当用户反复对表格进行各种排序以后，表格的原有次序将被打破，如果在排序后做了其他某些必要的操作，就不方便再使用Excel的撤销功能。那如何让表格返回到排序前的状态呢？用户常常需要把重要的工作表进行保护，如果在保护工作表的同时，又希望对工作表中的数据使用自动筛选功能以便进行一些数据分析工作，则需要进行一些设置如果希望按多个字段对数据列表进行分类汇总，只需按照分类次序，多次执行分类汇总功能即可。如本例先按业务员进行汇总，再按部门进行汇总本例所显示是一份在实际工作中经常出现的数据列表，其中使用了底纹的单元格包含了各种汇总公式，无论从行还是列来看，数据都存在多个层次，虽然所有的数据一目了然，但可读性较差，如果为这样的数据列表创建分集显示视图，则情况会改善很多如果用户为数据列表创建了分级显示视图，而后对工作表执行了“保护工作表”命令，则无法再调整分级显示视图的数据显示级别。如果希望在保护工作表的同时，能够分级显示视图进行调整，需要借助宏代码数据列表是以列表形式存在的数据表格，它是一维的表格。而在实际工作中，用户的数据往往是以二维表格的形式存在的。对于这样的数据表，用户无法以它为数据源创建利息的数据透视表，只有将二维的数据表格进行转换，才能成为数据透视表的数据源在实际工作中，如果根据本例Sheet1工作表所示的表格生成数据透视表进行销售量的汇总分析，得到的数据透视表中，因为日期仍然是按原始项目排列的，往往难有分析价值。如果能将销售数据按年、月来汇总，则有助于进一步对比分析。虽然数据源中没有年、月字段，但用户可以利用数据透视表的项目组合功能来实现使用Excel函数能够统计表格中某字段的重复项，但更方便的方法是使用数据透视表来完成，以本例所示的表格为例，统计各销售区域在表格中出现的次数本例展示了一张已经创建成功的数据透视表，通过添加计算项，来达到对比国内产品与进口产品的差异的目的本例展示的单元格引用方法有：A1引用样式、R1C1引用样式及三维引用本例展示了突破30个函数参数的限制数组通过数组公式运算后所生成的新数组通常称为“内存数组”，它是在内存中提供给其他函数公式进行再次运算的一类特殊数组。本例展示了3个例子，例1“计算1到10的自然数求和”，例2“求两个数组进行相乘后的总和”和例3“使用公式将多个不相邻区域进行组合”如本例，能产生多个计算结果并在多个单元格显示出来的单一数组公式，称为“多单元格数组公式”数组转置就是将数组的行列进行互换，形成一个新数组，即把数组在屏幕上旋转了90。数组的转置需要借助ERANSPOSE函数来完成的，如本例信息函数CELL的功能非常强大。它能够返回某一引用区域的左上角单元格的格式，位置或内容，以及文件所在路径等信息，是Excel中为数不多的可以返回单元格属性的函数之一，见本例详细介绍将CELL函数、NOW函数与定义名称方法结合起来使用，常常能完成一些较高难度的计算任务。如本例要在各个工作表中返回当前工作表的表名，即要在Sheet1的A1单元格显示“Sheet1，在Sheet2的A1单元格显示“Sheet2，依此类推本示例展示的技巧内容包括：英文字母大小写转换、字符与ANSI字符代码转换及半角与全角的转换函数REPT用来将特定字符按给定的次数重复填充。1、填充字符，本例中将A列存放的数字转化为10位数的编码，原位数不足的前面补0。2、制作文本直方图，本例中利用PEPT函数，可以在不创建Excel图表的情况下制作一个块状的直方图在Excel中针对某些数据进行查找与引用操作时，表面看起来相同的字符串，却经常无法查询到相匹配的结果，这往往是因为目标字符串与关键字不符导致的。本例中，A1=John smith,B1=John Smith，利用介绍的公式将两个字符串进行比较在实际应用中，经常要统计某个字符或字符串出现的次数，一般有两种情况：1、某字符在某单元格内出现的次数，2、包含某字符的单元格字数。具体应用见本例提取字符串包括：1、提取字符串常用函数。从原有文本中截取一部分用于形成新的文本是常见的文本运算，主要的文本截取函数有LEFT函数、MID函数和RIGHT函数；2、从完整路径中提取文件名；3、文本和数字分离；4、提取连续的汉字。具体应用见本例文本合并符“&”将符号两边的内容作为文本加以连接，除了比CONCATENATE函数更加简洁灵活，没有30个参数限制外，还有多种妙用，如连接名字与姓氏，连接注释文字和计算结果及去除引用空单元格返回的0。具体应用见本例在一些情况下，用户希望将重复记录按出现的先后顺序进行编号以作区别，不重复记录则保持不变。本例B2单元格输入介绍的公式，可以为重复记录加上数字编号本例内容包括：1、提取籍贯地区信息；2、身份证提取出生日期；3、提取性别信息本例内容包括：1、将身份证号码18位转为15位；2、将15位身份证号码设计为18位许多财会人员在登记日记账或支票填写等工作中，经常需要将金额数字分列填写在对应的格子中，有的还需要在金额钱加上￥符号，如本例。使用文本函数的嵌套，将金额作为字符串进行处理，能较容易地达到这种效果当数字需要转换为英文序数词，添加后缀的规则利用公式能将本例的A1单元格用阿拉伯数字表示的金额转为大写生肖是中国古老的民俗之一，以12种动物作为属相，每12年一个轮回，其计算方法比较简单，仅与年份有关。本例介绍的两个公式均能返回其对应的生肖本例内容包括：1、文本格式转为日期；2、将日期转为文本本例内容包括：1、将标准日期转为英文序数日期；2、将英文月份转为数字某些时候，用户以中文形式输入日期，如果需要用其进行相关计算，则需要将其转为日期值。在本例所示的表格中，A列全部为中文形式的日期文本，利用介绍的两种公式技巧将它们转化为真正的日期值Excel的日期是数字的一种特殊显示格式，因此用户能够以天为单位对日期值进行加减运算，并设置单元格格式为所需日期格式。本例内容包括：1、计算指定间隔后的日期；2、闰年判断本例内容包括：1、返回月末、季度末日期；2、求每季度止息日期；3、确定一个日期所属的季度、半年度DATEDIF函数用于计算两个日期之间的天数、月数或年数。本例内容包括：1、计算日期间隔特殊应用；2、计算日期间相差的年月日本例内容包括：1、计算一个日期是星期几；2、返回最近的星期天的日期；3、计算两个日期相距的星期几的个数；4、计算一个日期是一年中的第几周本例内容包括：1、计算两日期之间工作日天数；2、计算经过若干工作日后的日期本例展示的是军事时间显示及转换Excel的日期是以天为单位的序列号，数学三角函数运算的角度以弧度为单位，转换为弧度的角度是以度为单位。角度的转换计算具体见本例本例内容包括：1、按指定小数位数舍入；2、按指定基数倍数舍入；3、数值取整在出纳工作中经常会遇到备钞的计算问题，即各种面额的钞票各需要准备多少张。本例用INT函数制作一个备钞张数估算表Excel提供了MOD函数来实现数值的取余数计算。本例内容