EXCEL案例(高).xls

指指 示示 1 在在黃黃色色格格子子中中輸輸入入你你的的名名字字 2 在在綠綠色色格格子子中中輸輸入入你你的的年年齡齡 3 在在紅紅色色格格子子輸輸入入你你你你目目前前工工作作的的公公司司名名稱稱 4 在在橘橘色色格格子子中中輸輸入入你你公公司司董董事事長長的的名名子子 4 在在藍藍色色格格子子中中輸輸入入 START 並並按按下下 ENTER 鍵鍵 現在馬上回去工作 停止浪費時間在垃圾郵件上 歲了 真是丟臉 不是付你薪水來玩耍的 0 董事長 你已經 操作概述 有原始数据表 1月 2月 将原始数据贴到 集团 北京 成都 表中 操作目的 练习函数VLOOKUP IF ISERROR MATCH及工作组SUM操作 VL00KUP 在在规规定定范范围围内内 不不需需理理会会行行的的排排列列如如何何 可可任任意意引引用用与与目目标标值值所所在在行行的的相相关关 列列 的的信信息息 基基本本格格式式 VLOOKUP 目目标标 范范围围 N列列 逻逻辑辑值值 逻辑值为 TRUE 或 省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于 lookup value 的最大数值 若若为为 FALSE FALSE 将将返返回回精精确确匹匹配配值值 如如果果找找不不到到 则则返返回回错错误误值值 N A N A 注意事项 1 范围的第一列必须是目标所在地 2 2 唯一性 目标 范围 3 可帮助增加条件设置 减少唯一性的限制 4 复制使用时 对固定位置的判断 ISERROR 判判断断是是否否错错误误 若若是是错错误误就就显显示示 TRUE 否否则则 FALSE TRUE FALSE MATCH 查查找找目目标标值值在在规规定定范范围围 列列或或行行 中中的的位位置置 基基本本格格式式 MATCH 目目标标 范范围围 type 如果 match type 为 1 函数 MATCH 查找小于或等于 lookup value 的最大数值 Lookup array 必须按升序排列 2 1 0 1 2 A Z FALSE TRUE 如如果果 match typematch type 为为 0 0 函函数数 MATCHMATCH 查查找找等等于于 lookup valuelookup value 的的第第一一个个数数值值 Lookup arrayLookup array 可可以以按按任任何何顺顺序序排排列列 如果 match type 为 1 函数 MATCH 查找大于或等于 lookup value 的最小数值 Lookup array 必须按降序排列 TRUE FALSE Z A 2 1 0 1 2 等等 如果省略 match type 则假设为 1 eg A B3 C D VLOOKUP与与MATCH连连用用 只只要要title不不变变 可可任任意意引引用用指指定定的的 行行 列列 数数据据源源 北京成都广东 IBM100020003000 CISCO100200300 SUN102030 HP100002000030000 APL123 报报表表 VLOOKUPHLOOKUP 成都广东北京成都 APL231APL2 SUN203010SUN20 CISCO200300100CISCO200 IBM200030001000IBM2000 HP200003000010000HP20000 OFFSET 引引用用偏偏离离目目标标值值的的数数据据 基基本本格格式式 OFFSET 目目标标 行行 列列 高高度度 宽宽度度 简单引用 eg1 数数据据源源 ABCD公公式式 G EFGH IJKL与MATCH连用 A eg2 数数据据源源 北京成都广东 1000 IBM20003000 100 CISCO200300 10 SUN2030 10000 HP2000030000 1 APL23 报报表表 成都广东北京成都 APL231APL10 SUN203010SUN CISCO200300100CISCO IBM200030001000IBM HP200003000010000HP OFFSET与与MATCH连连用用 功功能能类类似似VLOOKUP 且且可可不不受受 目目标标必必须须在在范范围围第第一一列列 限限制制 但但其其修修改改 复复制制使使用用不不够够VLOOKUP灵灵活活 VL00KUP 在在规规定定范范围围内内 不不需需理理会会行行的的排排列列如如何何 可可任任意意引引用用与与目目标标值值所所在在行行的的相相关关 列列 的的信信息息 逻辑值为 TRUE 或 省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于 lookup value 的最大数值 如果 match type 为 1 函数 MATCH 查找小于或等于 lookup value 的最大数值 Lookup array 必须按升序排列 2 1 0 1 2 A Z FALSE TRUE 如如果果 match typematch type 为为 0 0 函函数数 MATCHMATCH 查查找找等等于于 lookup valuelookup value 的的第第一一个个数数值值 Lookup arrayLookup array 可可以以按按任任何何顺顺序序排排列列 如果 match type 为 1 函数 MATCH 查找大于或等于 lookup value 的最小数值 Lookup array 必须按降序排列 TRUE FALSE Z A 2 1 0 1 2 等等 广东北京 31 3010 300100 30001000 3000010000 广东北京 OFFSET与与MATCH连连用用 功功能能类类似似VLOOKUP 且且可可不不受受 目目标标必必须须在在范范围围第第一一列列 限限制制 但但其其修修改改 复复制制使使用用不不够够VLOOKUP灵灵活活 如果 match type 为 1 函数 MATCH 查找小于或等于 lookup value 的最大数值 Lookup array 必须按升序排列 2 1 0 1 2 A Z FALSE TRUE 如果 match type 为 1 函数 MATCH 查找大于或等于 lookup value 的最小数值 Lookup array 必须按降序排列 TRUE FALSE Z A 2 1 0 1 2 等等 1234567 库存库存库存出货出货出货 北京成都合计北京成都合计 APL5712131528 IBM 81018161834 ORA 101222182038 HP 7916151732 CISCO APC SUN 91120171936 库存库存库存出货出货出货 北京成都合计北京成都合计 APL5000700012000130001500028000 IBM 80001000018000160001800034000 SUN 90001100020000170001900036000 ORA 100001200022000180002000038000 IMA 6000800014000140001600030000 HP 7000900016000150001700032000 库存分析 分公司 集团 共54页 第32页 2020 1 14日1 05打印 4747 商品商品描述1月2月3月4月5月6月 编码库存出货库存出货库存出货库存出货库存出货库存 APL122812 00028 000 IMA14 00030 000 HP163216 00032 000 IBM183418 00034 000 SUN203620 00036 000 ORA223822 00038 000 合计88168102 000198 000 88 00168 00 库存分析 分公司 集团 共54页 第33页 2020 1 14日1 05打印 7月8月9月10月11月12月 出货库存出货库存出货库存出货库存出货库存出货库存出货 库存分析 分公司 集团 共54页 第34页 2020 1 14日1 05打印 库存分析 分公司 北京 共54页 第35页 2020 1 14日1 05打印 2525 商品商品描述1月2月3月4月5月6月 编码库存出货库存出货库存出货库存出货库存出货库存 APL5135 00013 000 IMA6 00014 000 HP7157 00015 000 IBM8168 00016 000 SUN9179 00017 000 ORA101810 00018 000 合计397945 00093 000 库存分析 分公司 北京 共54页 第36页 2020 1 14日1 05打印 7月8月9月10月11月12月 出货库存出货库存出货库存出货库存出货库存出货库存出货 库存分析 分公司 北京 共54页 第37页 2020 1 14日1 05打印 库存分析 分公司 成都 共54页 第38页 2020 1 14日1 05打印 3636 商品商品描述1月2月3月4月5月6月 编码库存出货库存出货库存出货库存出货库存出货库存 APL7157 00015 000 IMA8 00016 000 HP9179 00017 000 IBM101810 00018 000 SUN111911 00019 000 ORA122012 00020 000 合计498957 000105 000 库存分析 分公司 成都 共54页 第39页 2020 1 14日1 05打印 7月8月9月10月11月12月 出货库存出货库存出货库存出货库存出货库存出货库存出货 库存分析 分公司 成都 共54页 第40页 2020 1 14日1 05打印 操作概述 从原始数据表中建立透视表 操作目的 练习建立数据透视表 设置单元格格式 组 公式计算 销售区域应收已收出 入仓单号帐龄 重庆 10137570000 CX2000082100987203 成都市 120004530 CX2000082201018202 成都市 1427200 CX2000082901161195397 重庆 202250 CX2000082901161195 重庆 207500 CX200012200331682 重庆 380060 CX200012210333281 成都市 3225611842 CX200012220333780 成都市 70470 CX200012220333880 重庆 124500 CX200012220335680 重庆 33000 CX200012220335680 重庆 1580889 CX200012220335680 成都市 128000 CX200012220337780 成都市 128000 CX200012220337780 成都市 230666 CX200012250336277 成都市 1786740 CX200012250340077 成都市 280980 CX200012250340077 成都市 3950 CX200012250340077 出 入仓单号 全部 销售区域数据 成都市 帐龄2帐龄求和项 应收求和项 已收应收 千元 100以上77207397666207 397 80649031184264 903 81 82 100以上 汇总27230012508272 3 100以下195142720142 72 20212000453012 203 100以下 汇总1547204530154 72 总计42702017038427 02 重庆求和项 应收 的求和 求和项 应收求和项 已收应收 千元 207397 1733088917 3382233 3800603 83800 2075020 7520750 4188094941 88314180 2022520 225162945 12000 10137570000101 375101375 12160070000121 6276320 16348070949163 48590500 求和项 已收 的求和应收 千元 的求和 666207 397 1273182 233 603 8 20 75 13457314 18 162 945 453012 70000101 375 74530276 32 87987590 5 操作概述 用SUMIF做科目汇总表 一 SUM的运用 借方贷方 现金299 应收帐款299 应付帐款3000 银行存款3000 现金5701 应收帐款5701 应付帐款8402 银行存款8402 合计1740217402 二 SUBTOTAL的运用3480434804 三 SUMIF的运用 科目汇总表 借方汇总贷方汇总 应收帐款06000 应付帐款114020 现金60000 银行存款011402 或者6000 IF语句 1 正 19 大于10 1 负 30 大于20 40 大于20 6 正 21 大于20 20 大于10 11 大于10 10 正 mid left right 及find APPLE A P APPLE APPLE E A P rank small large与条件格式 ranksmalllarge 774 4149100 497 725294 803 336080 942 247777 526 658060 1001 169452 605 5710049 min max abs round minmaxabsround 123456 789 300123456 789 123456 8123456 789 200200123456 79 300300123456 8 400400123457 500500123460 123500 123000 today 与 now 2002 9 5 2002 9 5 15 05 upper lower proper trim a APPLEA APPLE a apple A Apple a APPLE A Apple count counta average 77 大 4 小 count 统计有多少个数 2 counta 统计有多少个非空白的值 4 average 求平均 40 5 average 对单元格中的数值求平均时 应牢记空空单单元元格格与与含含零零值值单单元元格格的区别 尤其在 选项 对话框中的 视图 选项卡上已经清除了 零值 复选框的条件下 空单元格不计算在内 但计算零值 average 对单元格中的数值求平均时 应牢记空空单单元元格格与与含含零零值值单单元元格格的区别 尤其在 选项 对话框中的 视图 选项卡上已经清除了 零值 复选框的条件下 空单元格不计算在内 但计算零值 操作概述 有原始数据表 月出货数据 根据不同的需求制定不同类型的图表 操作目的 练习图表数据源 图表类型的选定及图表的编辑与格式化 XX产产品品线线分分公公司司1 101 10月月出出货货分分布布 月月份份 分分公公司司北北京京 占占集集团团 比比例例广广东东 占占集集团团 比比例例上上海海 占占集集团团 比比例例集集团团合合计计 月出货比例 1月月38019 90 43022 51 1 10057 59 1 9106 01 2月月76031 28 84034 57 83034 16 2 4307 65 3月月70016 67 1 50035 71 2 00047 62 4 20013 22 4月月36016 67 1 00046 30 80037 04 2 1606 80 5月月60022 14 1 30047 97 81029 89 2 7108 53 6月月69022 70 1 35044 41 1 00032 89 3 0409 57 7月月1 65037 33 1 27028 73 1 50033 94 4 42013 91 8月月64028 57 40017 86 1 20053 57 2 2407 05 9月月1 10032 35 1 30038 24 1 00029 41 3 40010 70 10月月3 15059 77 1 30024 67 82015 56 5 27016 58 合合计计10 03031 56 10 69033 64 11 06034 80 31 780 图1 分公司月出货对比图 柱型图 作用 清晰可见集团内各分公司间每月出货的差异对比 分公司1 10月出货对比 1月2月3月4月5月6月7月8月9月 10月 0 500 1 000 1 500 2 000 2 500 3 000 3 500 0 1 000 2 000 3 000 4 000 5 000 6 000 1月2月3月4月5月6月7月8月9月10月 集团合计 1 910 2 430 4 200 2 160 2 710 3 040 4 420 2 240 3 400 5 270 上海1 100 830 2 000 800 810 1 000 1 500 1 200 1 000 820 广东430 840 1 500 1 000 1 300 1 350 1 270 400 1 300 1 300 北京380 760 700 360 600 690 1 650 640 1 100 3 150 图2 集团月出货走势 折线图 作用 从量上反映当期市场需求及季节变换状况 图3 面积图 作用 体现部分与整体的关系 集团月出货趋势图 0 1 000 2 000 3 000 4 000 5 000 6 000 1月2月3月4月5月6月7月8月9月10月 集团合计 1 910 2 430 4 200 2 160 2 710 3 040 4 420 2 240 3 400 5 270 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 1月 2月 3月 4月 5月6月7月 8月 9月10月 1月 2月3月 4月 5月6月 7月 8月 9月 10月 6 000 5 000 4 000 3 000 2 000 1 000 0 1月2月3月4月5月6月7月8月9月10月 北京 集团合计 月平均出货量 分公司1 10月出货对比 1月2月3月4月5月6月7月8月9月 10月 0 500 1 000 1 500 2 000 2 500 3 000 3 500 0 1 000 2 000 3 000 4 000 5 000 6 000 1月2月3月4月5月6月7月8月9月10月 集团合计 1 910 2 430 4 200 2 160 2 710 3 040 4 420 2 240 3 400 5 270 上海1 100 830 2 000 800 810 1 000 1 500 1 200 1 000 820 广东430 840 1 500 1 000 1 300 1 350 1 270 400 1 300 1 300 北京380