Excel实用技巧32招.xls

技巧1 数据有效性中的批注 姓 名 编 号 所 属 部 门 操作步骤 1 选择相应单元格 2 数据 有效性 输入信息 技巧2 冻结窗格保持标题可见 编号品名库位数量 305868W02771 437022H029421操作步骤 353296D0341341 选择A4 241746J0355142 窗口 冻结窗格 412233T013263 734639V029046 301040L012670 440421V028173 145872W029841 449784R01899 882882C021607 772241X018047 893046P028277 542203D025255 153522S032887 993273H035413 978659T019404 792003E029305 276678I024004 228928L016907 972853H036621 907860O036397 300408M024941 330992P035662 909671K028790 374207J023722 721211C03710 403638T031345 944231V036317 941814D021643 798524J019226 784173R019516 751461J021931 217651R026970 921635A032382 988431Y025006 373620V024571 393610T016119 512531R019507 714189D026744 348777L023305 419975G026432 509590X022319 297823S017185 551477D034907 870926L011957 869766Q012704 133141I019143 789223N033067 547533F02182 393798M027866 187863N025913 562554D037040 872304F013251 792987X03340 168986H02591 483470K028807 465340V02266 404689J013001 082062D015049 271185B022274 571958Q01952 310667V012345 190768P033034 693027Y03216 084021C023902 238233K012125 996637S038728 165334C027175 021671G022273 809363B025520 210848B029346 923247Y033536 185843S036410 754033C02613 322778P03678 303821F012543 141910R024981 801322L025878 960841U024848 347787F037549 498125X028724 110391C027526 953257G039330 609734H033247 135068M023814 888757C023772 028636T025483 535706W035797 668562H032751 148069U023185 358350W029826 914161D028635 252735F023634 026360B037872 345728W034029 188587R015694 022558V028551 420586A023311 867764T036542 534416B012022 365444J028993 571140K025138 628847B022294 497851N039321 796454V025834 458112O022257 474529O022345 718098V014901 343224U037775 640604H027703 456244L037995 888862M027857 568800Q022756 513026B023238 669705C038269 158504N039295 780681E02219 601146I027134 677139H034536 419421P036061 903271Q031283 368124W032490 997710W028678 191901L036397 612261N029364 576823J026223 525650L027990 212952F022287 213143Q027760 605533D024710 782306V036754 612231N014324 206268M014775 275787D026298 913008W024047 430241W02750 240058K018241 278429V014449 179034C011551 935683H022321 983994O012529 450852M022235 089274W014994 606906M019799 928679N011238 155208K025667 912679U028416 982956U016259 630715D021367 582088N021481 336427W025156 582514R031430 288646S022272 662189A014921 671959J034684 393091J022148 725410V029370 技巧3 巧填日期 2006年1月31日操作步骤 1 选择A3 2 将鼠标箭头指向单元格右下角 出现一个黑十字指针 3 按下右键向下或向左拖动若干个单元格 4 放开右键 出现一个快捷菜单 5 选择填充选项 按月 按年 按工作日等 将鼠标箭头指向单元格右下角 出现一个黑十字指针 技巧4 输入身份证号码 3 52223E 15操作步骤 在输入时数字前加 3522225730219050或 35222257302190501 选择单元格A6 可以是整列 整行或一个区域 2 格式 单元格 数值 文本 3 最后输入数字 选择单元格A6 可以是整列 整行或一个区域 技巧5 在单元格里创建下拉列表 月份姓名数量1月张三 2月李四 3月王五 4月赵六 5月田七 6月董八 7月 8月 9月 10月 11月 12月 操作步骤 1 选择需要设置下拉列表的单元格区域 此例为B4 B14 或 C4 C14 2 数据 有效性 设置 允许 序列 3 来源 选择相应的G3 G14或H3 H8 技巧6 在单元格里同时显示文本和数值 文本形式数字形式 5 KK5 KK操作步骤 8 KK8 KK1 选择D4 D12 3 KK3 KK2 格式 单元格 数字 自定义 7 KK7 KK3 输入自定义格式 G 通用格式 KK 10 KK10 KK 12 KK12 KK 4 KK4 KK 合计049 KK 格式 单元格 数字 自定义 输入自定义格式 G 通用格式 KK 技巧7 显示超过24小时的时间 日期小时 1月1日8 30 1月2日3 00 1月3日5 40 1月4日5 15 1月5日2 30 1月6日1 30 1月7日3 45 1月8日2 48 1月9日6 10 1月10日4 35 显示错误19 43 格式 h mm 显示正确43 43 格式 h mm 技巧8 在单元格内换行 这个单元格的文本太长了操作步骤 格式 单元格 文本控制 自动换行 这个单元格的文本太长了ok 或 这个单元 格的文本 太长了 ok编辑单元格 选择合适的位置按Alt Enter可手动换行 这个单 元格的 文本太 长了 ok 格式 单元格 文本控制 自动换行 编辑单元格 选择合适的位置按Alt Enter可手动换行 技巧9 使用条件格式突出超过计划的数据 计划实际 4 5144 378操作步骤 7 6512 4381 选择C4 C11 6 6941 3372 格式 条件格式 1 5657 9443 设置条件 单元格数值 小于 B4 1 6032 5294 设置满足条件的单元格背景 格式 图案 选中颜色 6 8672 130 3 7794 632 5 9593 606 设置满足条件的单元格背景 格式 图案 选中颜色 技巧10 使用相对引用 绝对引用和混合引用 相对引用 A1 绝对引用 A 1 混合引用 A 1 A1 相对引用示例 项目数量价格合计项目数量价格 电脑5300015000椅子3100 打印机210002000桌子3200 传真机1500500台灯250 操作步骤 1 选择E11单元格可以看到随着公式所在单元格位置的不同 2 输入 C11 D11被复制的公式中的行列号也发生了相应的改变 3 复制E11单元格 4 选择E12 E13单元格 5 按住Ctrl 选择J11 J13单元格 6 鼠标右键 粘贴 绝对引用示例 项目数量价格合计税额税率17 电脑53000150002550 打印机210002000340 传真机150050085 操作步骤 1 选择F26单元格 符号的作用是让公式在复制时行列不随单元格的位置改变 2 输入 E26 I 25 3 按住单元格下拖复制至F28 混合引用示例 宽度 1234 长 度 11234 22468 336912 4481216 55101520 操作步骤 1 选择D40单元格 2 输入 D 39 C40 3 按住单元格向右向下拖动复制到G44 D 39表示单公式复制的位置改变时 39这个行号不改变 列号可改变 C40表示单公式复制的位置改变时 C这个列号不改变 行号可改变 快速改变引用模式 0 操作步骤 1 选择单元格 按F2 2 选择公式中的引用文本 3 按F4 即可在相对应用 绝对引用和混合引用中切换 合计 300 600 100 可以看到随着公式所在单元格位置的不同 被复制的公式中的行列号也发生了相应的改变 符号的作用是让公式在复制时行列不随单元格的位置改变 技巧11 筛选状态下的自动求和 姓名部门加班时间 林晓东内控科36 9 黄彩金内控科23 9 吕苑如内控科40 6 杨三华内控科45 4 使用SUBTOTAL函数146 8只对筛选可见的记录进行合计 使用SUM函数248对所有记录进行合计 技巧12 将公式转换成数值 5 407 848 649 036 490 701 811 02 5 157 222 101 569 780 943 916 94 5 376 551 972 834 854 307 911 97 6 597 605 089 896 512 436 037 83 577 873 162 516 651 845 508 92 663 901 539 276 733 492 063 45 449 857 103 845 671 713 075 23 1 141 479 754 663 023 731 528 03 260 914 657 378 789 993 246 66 5 803 913 344 145 932 988 187 91 3 706 347 590 711 155 895 931 61 5 439 341 797 275 010 481 163 55 操作步骤 1 选定区域 2 右键 复制 3 右键 选择性粘贴 显示选择性粘贴对话框 4 选择对话框中的 数值 选项 确定 5 按ESC键取消 复制 模式 技巧13 保留公式的情况下删除数值 项目小组姓名奖金操作步骤 A张三5001 选定区域 A李四4002 编辑 定位 定位条件 A王五3003 选择 常量 数值 小计12004 按Del删除 B赵六700 B钱七800 B董八400 小计1900 C孙二900 C邱十200 C林一1000 小计2100 合计5200 技巧14 将所有数值乘一个倍数 项目小组姓名奖金公司决定将所有人的奖金加10 1 1 A张三500 A李四400操作步骤 A王五3001 在空白单元格输入1 10 小计12002 复制该单元格 B赵六7003 选择表格区域 B钱七8004 编辑 定位 定位条件 显示对话框 B董八4005 选择 常量 再选择 数字 确定 小计19006 右键点击选择区域 选择性粘贴 C孙二9007 选择 公式 乘 确定 C邱十200 C林一1000 小计2100 合计5200 编辑 定位 定位条件 显示对话框 选择 常量 再选择 数字 确定 技巧15 显示公式 项目小组姓名奖金 A张三500 A李四400 A王五300 小计1200 B赵六700 B钱七800 B董八400 小计1900 C孙二900 C邱十200 C林一1000 小计2100 合计5200 操作步骤 按住Ctrl 再按 键可在公式和计算结果之间切换显示 技巧16 避免除零错误 DIV 0 数量单价1单价2金额 5161680 109990 0 DIV 0 100 207 57 5150 单价1公式 C E4 B4 单价2公式 IF B4 0 E4 B4 技巧17 使用列表和名称动态求积和 品品名名数数量量单单价价操作步骤 A2041 选定区域 B3052 数据 列表 创建列表 此时出现一个列表边框 C4063 选定区域 D5074 插入 名称 指定 选择 首行 确定 E6085 在G12单元格输入 SUMPRODUCT 数量 单价 F702 G809 合计 数据 列表 创建列表 此时出现一个列表边框 插入 名称 指定 选择 首行 确定 在G12单元格输入 SUMPRODUCT 数量 单价 技巧18 计算加权平均值 采购单价采购数量 12200 17100 15700 16400 22500 12100 20200 算术平均单价16 29 AVERAGE B4 B10 加权平均单价16 91 SUMPRODUCT C4 C10 B4 B10 SUM C4 C10 技巧19 按数值大小排名 销售人员销售额名次 张三96 6292 李四97 9681 王五49 0466 赵六80 7773 钱七80 7773 董八10 2629 孙二40 8678 邱十73 2725 林一42 7167 名次公式 RANK C4 C 4 C 12 技巧20 判断某天为星期几 日期星期 2006 04 20星期4C4单元格公式 WEEKDAY B4 2 2006 05 20星期6单元格数字格式 DBNum1 星期 804 G 通用格式 2006 06 20星期2 2006 07 20星期4 DBNum1 804 表示简体中文小写形式 2006 08 20星期7 2006 09 20星期3 2006 10 20星期5 2006 11 20星期1 2006 12 20星期3 2007 01 20星期6 单元格数字格式 DBNum1 星期 804 G 通用格式 技巧21 判断某天所在月份的最后一天日期 日期该月最后一天日期 2006 04 202006 04 30C4单元格公式 DATE YEAR B4 MONTH B4 1 0 2006 05 202006 05 31 2006 06 202006 06 30 2006 07 202006 07 31 2006 08 202006 08 31 2006 09 202006 09 30 2006 10 202006 10 31 2006 11 202006 11 30 2006 12 202006 12 31 2007 01 202007 01 31 C4单元格公式 DATE YEAR B4 MONTH B4 1 0 技巧22 四舍五入 数量单价金额1金额2 132 9 3758 12381238 102 1 5199 155155 54 15 5291 839839 111 2 0832 231231 104 9 2199 959959 88 16 9133 14881488 59 16 6555 983983 合计58925893 金额1公式 B4 C4 金额2公式 ROUND B4 C4 0 技巧23 累计金额 日期消费金额累计 2006 12 1451451D4单元格公式 SUM C 4 C4 2006 12 2500951 2006 12 34131 364 2006 12 47042 068 2006 12 55282 596 2006 12 62142 810 2006 12 71292 939 2006 12 81013 040 2006 12 93213 361 合计3 361 技巧24 条件计数求和 姓名部门加班时间 林晓东内控科36 9 王锦艺会计科6 2 黄彩金内控科23 9 唐磊会计科33 8 吕苑如内控科40 6 林雅真会计科13 5 杨三华内控科45 4 匡小容会计科25 林平会计科10 2 陈海燕会计科40 6 计算内控科总加班人数4 COUNTIF C4 C13 内控科 计算加班时间大于40的人数3 COUNTIF D4 D13 40 计算内科加班时间超过40小时人数2 SUMPRODUCT C4 C13 内控科 D4 D13 40 计算内控科总加班时间146 8 SUMIF C4 C13 内控科 D4 D13 计算内控科加班时间大于40的时间总和126 6 SUMIF D4 D13 40 D4 D13 计算内科加班时间超过40小时的总时间86 0 SUMPRODUCT C4 C13 内控科 D4 D13 40 D4 D13 SUMPRODUCT C4 C13 内控科 D4 D13 40 SUMPRODUCT C4 C13 内控科 D4 D13 40 D4 D13 技巧25 查找特定值 员工编号 员工姓名 E01赵 六输入员工编号E19 E02张 三 E03杨 三 华查找员工姓名陈海燕 E04王 五 E05王 锦 艺 E06唐 磊F9单元格公式 VLOOKUP F4 B3 C22 2 0 E07孙 二 E08邱 十 E09钱 七 E10吕 苑 如 E11林 一 E12林 雅 真 E13林 平 E14李 四 E15匡 小 容 E16林 晓 东 E17黄 彩 金 E18董 八 E19陈 海 燕 技巧26 双向查找和条件定位 销售金额 千元 员工姓名1月2月3月4月5月 赵六6 5687 1969 7935 7457 863 张三5 2574 8943 3355 7463 263 王五6189 5363 2788 8968 972 孙二5 7037932 7595 4704 026 邱十7 8884142 2148 539979 钱七2 8438 4174 9031 2212 212 李四5 7924 2429 9416 4884 994 员工姓名张三销售金额5 746 月 份4月 F16的双向查找公式 INDEX C 5 G 11 MATCH C15 B 5 B 11 0 MATCH C16 C 4 G 4 0 C5 G11的条件定位公式 B5 C 15 C 4 C 16 F16的双向查找公式 INDEX C 5 G 11 MATCH C15 B 5 B 11 0 MATCH C16 C 4 G 4 0 技巧27 双列查找 合作项目 项目参与人 项目奖金合作项目J J李四2300项目参与人王五 N李四2000 H钱七9500项目奖金9700 M钱七8800 G邱十4900G7公式 INDEX D4 D17 MATCH G3 G4 B4 B17 C4 C17 0 H孙二6600 M孙二4600 J王五9700 N王五6700 K张三6200 B张三700 I张三3000 B赵六6500 I赵六7100 G7公式 INDEX D4 D17 MATCH G3 G4 B4 B17 C4 C17 0 技巧28 等级判定 得分等级产品得分等级 0EPrduct138EH4公式 VLOOKUP F4 B 4 C 8 2 40DPrduct240D 70CPrduct334E 80BPrduct483B 90APrduct524E Prduct675C P