《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 标标记记重重复复值值 第第二二篇篇 ExcelExcel基基本本功功 能能 技技巧巧9898 标标记记含含有有公公 式式的的单单元元格格 技技巧巧9999 标标记记匹匹配配目目 标标数数值值的的单单元元格格 技技巧巧100100 八八种种颜颜色色区区 分分不不同同数数值值 技技巧巧101101 无无限限条条件件的的 条条件件格格式式 技技巧巧102102 在在单单元元格格中中 创创建建下下拉拉列列 技技巧巧103103 另另类类的的批批注注 技技巧巧104104 快快速速切切换换有有 效效性性的的来来源源 技技巧巧106106 只只能能录录入入某某 种种特特征征的的数数据据 技技巧巧107107 限限制制重重复复值值 的的录录入入 技技巧巧108108 杜杜绝绝负负数数库库 存存 技技巧巧109109 强强制制序序时时录录 入入 技技巧巧110110 多多级级选选择择录录 入入 技技巧巧113113 在在同同一一页页上上 打打印印不不连连续续的的区区域域 技技巧巧116116 在在报报表表的的每每 一一页页上上打打印印公公司司LogoLogo 技技巧巧117117 在在页页眉眉或或页页 脚脚增增加加分分隔隔线线 技技巧巧119119 在在ExcelExcel中中 定定时时打打印印 第第三三篇篇 数数据据 分分析析 技技巧巧121121 对对超超过过3 3列列 的的数数据据排排序序 第第二二篇篇 ExcelExcel基基本本功功 能能 技技巧巧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数数组组公公 式式计计数数与与求求和和 技技巧巧209209 SUMSUM函函数数与与 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 Pareto chart 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 按按颜颜色色求求和和 与与计计数数函函数数 第第八八篇篇 VBAVBA 实实例例与与技技巧巧 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函数来实现数值的取余数计算 本例内容包括 1 判断奇偶数 2 获得循环等差序列 3 隔行 隔列取数 根据 数字修约规则 在科学技术与生产活动中试验测定和计算得出的各种数值 要根据指定的修约有效位数进行修约以得到规范的数值 本例展示以0 5为单位修约与以0 2为单位修约 随机数经常用于模拟数据以测试某个算法或参数是否可行 本例所示 利用介绍的公式将产生指定范围的不重复随机数值 利用矩阵和矩阵的逆函数 可以用Excel求解多元一次方程 如本例求解的三元一次方程 本例展示的表格为某年级期末考试成绩表 利用介绍的函数公式可实现表中的要求 某些时候 用户为了规范数据的取值 需要对数据设置一定的上限或下限 即当数据超过上下限范围时 只返回极限值 使用MAX函数和MIN函 数比使用IF函数更为简单 见本例 本例内容包括 1 统计不重复数字的个数 2 填写数据检查 对数据进行有条件地统计 计数是一项经常性的工作 COUNTIF函数就是最常见的Excel统计函数之一 其应用见本例 在Excel数据表中 以某个字段进行多条件统计是一项常见的工作 如本例所示的数据表中 有 工号 商品 和 销售量 这3个字段 利用介绍的公式可以在由其中一个字段构成的多个条件下进行统计 本例所示的工作表中包含一份学员成绩表 利用介绍的公式将返回第3班学员中成绩 70分的学员人数 本示例内容包括 1 多区域求和 2 条件求和基本应用 3 SUMIF关键字查找 本例所示的工作表中 A2 B10单元格区域存放日期和销售量 要求在D2单元格求出单日最高销售量 在D4单元格求出销售量单日最高日期 模糊求和计算的首要任务是查找 即用户通过使用查找函数或统计函数的嵌套 对符合条件的值进行求和 如本例 在会计工作中经常需要计算日计帐的实时余额 除了使用逐级累加的方法外 还可以通过SUM函数来实现这种累计 如本例所示 如果需要在数据表中添加多个小计 仅仅使用SUM函数构造各处小计的公式会非常麻烦 而且不能适应记录的增减变化 本例所示的表格中 利用介绍的公式对这种计算会有帮助 除了COUNTIF函数和SUMIF函数可以进行条件计数 求和外 SUM函数和SUMPRODUCT函数进行数组运算也可以实现多条件计数和求和 如本例 本例内容包括 1 计算高于平均分的人数 例中所示的工作表中包含一份月考成绩表 利用介绍的公式统计男 女生各科成绩高于班级平均 分的人数 2 统计材料的消耗汇总 例中所示的工作表中包含某公司产品产量和配料明细表 利用介绍的公式可以统计各种材料的消耗汇总 本例所示的工作表中 B2 E12单元格区域是部门综合考评表 G2 H5单元格区域为优良中差登记对应的分数 C3 E3单元格区域为三个考核项 目的系数 要求计算各部分的综合评分 即各部门的各项评价等级转为分数后与对应项目系数的乘积和 本例内容包括 1 销售提成计算 在所示的工作表中 要求根据B11单元格的销售总额和D2 E11单元格区域的提成标准计算某业务员的累计提 成 2 个人所得税计算 所示的工作表是一个典型的个税累进求和实例 本例内容包括 1 隐藏行下的数据统计 2 动态生成序号 有一份期末考试成绩表 要求计算出各科成绩的最高分 使用MAX函数或SUBTOTAL函数可以实现之 本例展示了一份学员成绩表格 利用介绍的数组公式可以统计表格中有两科以上不及格的人数 如本例所示的学员考试成绩表中 要求计算忽略缺考 单元格为空 后的各科平均分 有两种解决方法 本例的表格为某单位歌唱比赛的评分表 由9位评委对7名选手分别打分 利用介绍的公式 实现计算 去掉一个最高分和一个最低分 后的平 均值为最后得分 本例内容包括 1 各分数段人数统计 2 计算连续相同值的最多个数 本例数据图表所示 要求统计A2 A11单元格区域中不重复记录的个数 有三种方法可以实现之 计算排名在学校的成绩统计 商业销售业绩等方面有着广泛的应用 如本例所示的表格中 如果要对A2 A11单元格区域中的数据进行升序或降 序排名 有三种方法可以实现之 本例所示的某年级两个班级的考试成绩表格中 利用介绍的两种方法均可以对成绩进行全年级统一排名 在排名计算方法中 中国人的习惯是 无论有几个并列第2名 之后的排名仍应该是第3名 即并列排名不占用名次 如本例希望实现中国式排 名效果 可以用3种方法实现之 本例所示的表格中 如果需要求出A2 A11单元格区域中第4大的值 可以使用介绍的数组公式 本例中 D1 G7单元格区域是一个简单的员工信息表 利用介绍的公式可以根据A列中的员工编号返回相应的员工姓名 本例成绩检索表中 要求按照D2 E6单元格区域中的成绩与等级的对应关系 返回A列分数所属的成绩等级 在所得税计算表中 个人所得税的 区间登记被划分为9级 利用介绍的公式可以返回所得税额 本例所示的工作表中 D列为员工编号 利用介绍的两种方法 可以确认员工编号为A0795的员工在员工信息表中是否存在 一张某公司产品价格表 要求根据B3单元格和B4单元格中的产品型号和规格条件 利用介绍