Excel数据分析基础入门篇（四）

※食用指南文章内容为‘PAPAY电脑教室—Excel基础教学’视频学习笔记旨在夯实已掌握的Excel基础※需要表格数据请私信领取PAPAY电脑教室—Excel基础教学https://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNMhttps://www.youtube.com/playlist?listPL7enJ2-v6SPm-EHMuRMCG7R7C-vXQugNM目录三十一SUMPRODUCT1、布林逻辑Boolean Logic2、AND/OR函数3、SUMPRODUCT函数4、布林逻辑SUMPORDUCT三十二动态阵列1、UNIQUE2、SPILLING3、SUMIF溢出4、FILTER5、SORT三十三、柏拉图1、客诉分析-直接组合2、业绩分析-透视辅助3、业绩分析-内建柏拉图三十四、XLOOKUP1、基础介绍2、向左查询3、自带错误提示4、模糊查找5、横向查询6、通配符万用字元模糊查找7、逆向查询8、双向筛选三十五、使用Excel获取网页表格数据三十六、使用Excel设计线上问卷、数据分析三十七、无三十八、LET1、函数解释2、基本使用3、动态阵列LET三十九、数据组合1、CONCATENATE2、CONCAT3、Ampersand4、CHAR四十、模拟分析功能1、目标搜索单变量求解2、运算列表模拟运算表3、分析蓝本管理员三十一SUMPRODUCT1、布林逻辑Boolean Logic布林是一种数据类型区别于数字、文字、日期只有两种变化TRUE、FALSE应用于公式中逻辑判断具有简化公式的作用1判断消费金额是否达到1000元一般可以使用IF函数来判断IF(E31000,TURE,FALSE)使用布林逻辑可直接判断E310002判断是否包含指定文字布林逻辑除了大小比较之外也可以针对文字进行核对查看客户是都有缴年费C3有也可以使用不等于号得到相反的结果2、AND/OR函数通常需要判断一个以上的条件就需要用到AND和OR1AND且AND条件1条件2条件3只有3个条件都符合TURE才会返回正确结果TURE其中任意一个条件有误FALSE,就会返回错误结果FALSEVIP条件消费次数满5次以上并且消费金额达到1000的会员AND(D35,E31000)修改次数和金额判断结果随之变化2OR或OR条件1条件2条件3任意1个条件符合TURE就会返回正确结果TURE3个条件都有误FALSE,才会返回错误结果FALSEVIP条件消费金额达到1000或者有缴年费的会员OR(C3有,E31000)3AND和OR同时使用VIP条件有缴年费消费金额大于1000元且消费次数大于5次OR(C3有,AND(E31000,D35))4TURE、FALSE转换中文表达使用IF函数来完成IF(OR(C3有,AND(E31000,D35)),可升等,条件不符)布林逻辑也可应用在格式化条件复制OR(C3有,AND(E31000,D35)3、SUMPRODUCT函数SUMPRODUCT函数SUM加总、PRODUCT乘积对应两个单元格相乘后再将乘积加总起来1商品金额计算一般会将数量和单价相乘后再在底部加总2分数总合、平均值计算不同科目有各自的加权倍数用SUMPRODUCT函数计算加权后的总分加权平均值用总分除以权重总合就可以3计算在花费在某一项的金额计算每个班级花费在文具的费用4、布林逻辑SUMPORDUCT使用布林逻辑进行运算时TRUE和FALSE分别代表1和0TURE11*11FALSE00*101查询已缴年费的会员一共有几位使用SUMPRODUCTTRUE、FALSE需要转换为1和0来计算❗使用--、*1、0将逻辑值转换为数值才能正常求和❗转换方法一SUMPRODUCT((AF3:AF9有)*1)转换方法二SUMPRODUCT(--(AF3:AF9有))转换方法三SUMPRODUCT((AF3:AF9有)0)2有缴年费且住在东区的会员使用辅助单元格SUMPRODUCT((AF3:AF9AF11)*(AG3:AG9AG11))3有缴年费且住在东区的会员的总消费金额SUMPRODUCT((AF3:AF9AF11)*(AG3:AG9AG11)*AI3:AI9)三十二动态阵列动态阵列Dynamic Arrays一般一个公式返回一个值比如VLOOKUP动态阵列使用一行简单的公式回传多个值❗ 设定表格 ❗如果不需要颜色可以取消1、UNIQUE去掉表格中的重复值查看表格中有哪几种重复值返回的是一数组清单一次列出多个查询结果这种情况在Excel称为SPILLING溢出2、SPILLING①周围会显示蓝色的细框②在第一个单元格修改溢出范围的公式其他单元格为灰色无法修改③如果在单元格输入其他内容则无法顺利溢出把它移走就可以正常溢出④在数据新增数据时溢出范围会自动调整大小无需手动修改3、SUMIF溢出计算每个产品的销售总金额SUMIF($G$5:$G$20,K5,$I$5:$I$20)下拉公式自动计算其他产品但表格新增数据时不会自动计算新的数据需要手动复制因为SUMIF不具备溢出的能力如果希望能自动填写需要在选定的条件单元格后加#SUMIF($G$5:$G$20,K5#,$I$5:$I$20)如此当数组新增数据时SUMIF函数也可以即时更新销售的总金额4、FILTER根据设定的条件筛选表格中的数据FILTER(数据范围筛选条件)1查找所有原子笔的销售订单可以用筛选栏辅助FILTER($F$5:$I$20,G5:G20P2)修改筛选栏就可以获取不同的数据当清楚筛选栏数据FILTER函数显示错误FILTER可以自定义显示错误或者保留空白FILTER($F$5:$I$20,G5:G20P2,查无数据)FILTER($F$5:$I$20,G5:G20P2,)将筛选栏设置为下拉菜单无需手动输入当数据更新也会随之更新2查找所有业务员为小美的原子笔的销售订单FILTER($F$5:$I$21,(G5:G21P2)*(H5:H21Q2),)5、SORTSORT(排序范围)将所选数据排序繁体-笔划简中-拼音SORT(UNIQUE(G5:G21))指定排序SORT(排序范围排序列排序方式)按销售金额由高到低SORT(FILTER($F$5:$I$21,(G5:G21P2)*(H5:H21Q2),),4,-1)三十三、柏拉图80/20定律一个公司的80%利润由20%的客户贡献一个人80%的工作付出换取20%的成果这个定理名为帕累托图在Excel中称为柏拉图可以使用这个找到关键的20%柏拉图由一条柱形图和折线图组成柱形代表每件事件发生的概率折线图则是事件发生的累计百分比1、客诉分析-直接组合1对投诉次数降序处理、计算百分比、累计百分比2建立图表基本雏形3细节优化修改右纵坐标、以纵坐标为基准设置网格线、添加曲线标记修改柱形分类间距、设置渐变填充、添加数据标签由此可看出价格太贵、容易订不到位这两项解决能减少客诉2、业绩分析-透视辅助表格无法将原始数据进行细节汇总可以借助数据透视来操作例如针对业务员的业绩总合插入柏拉图无法直接获得1数据透视拉取业务员、销售金额*2去掉总计右键点击总计删除即可修改行名称为业务员、业绩、累计百分比修改业绩排序为降序并设置金额符号设置累计百分比值的显示方式按某一字段汇总的百分比2表格美化3、业绩分析-内建柏拉图WPS没有此功能Excel可以一键生成柏拉图三十四、XLOOKUP1、基础介绍XLOOKUP(查找值数据范围返回范围)XLOOKUP(B3,F3:F11,G3:G11)修改姓名返回相应的业绩2、向左查询查询所在单位XLOOKUP(F8,$Q$3:$Q$11,$P$3:$P$11)3、自带错误提示XLOOKUP(查找值数据范围返回范围错误提示)XLOOKUP(B3,F3:F11,G3:G11,查无此人)4、模糊查找XLOOKUP(查找值数据范围返回范围错误提示近似对比)计算获得的奖金金额1首先计算提成百分比可以拿业绩中多少钱XLOOKUP(G3,$B$6:$B$8,$C$6:$C$8,,-1)返回值0精准匹配-1精准匹配或下一个较小的项1精准匹配或下一个较大的项2通配符匹配2计算奖金金额XLOOKUP(G3,$B$6:$B$8,$C$6:$C$8,,-1)*G35、横向查询如果查询数据为横向可以使用HLOOKUP也可以用XLOOKUPXLOOKUP(G3,$Y$2:$AC$2,$Y$3:$AC$3,,-1)*G36、通配符万用字元模糊查找如果记不清业务员名字XLOOKUP(B3,F3:F11,G3:G11,查无此人,2)7、逆向查询XLOOKUP(查找值数据范围返回范围错误提示近似对比查询方向)默认查询方向自上而下返回顺序1从第一项到最后一项进行搜索-1从最后一项到第一项进行搜索2二分搜索升序排序-2二分搜索降序排序XLOOKUP(L3,$P$3:$P$11,Q3:$Q$11,,,-1)可以找到东区年资最浅的业务员8、双向筛选两个XLOOKUP嵌套XLOOKUP(AH3,AJ3:AJ6,XLOOKUP(AH2,AK2:AN2,AK3:AN6))添加下拉菜单便于查询数据-有效性-序列三十五、使用Excel获取网页表格数据在网页看到表格呈现的数据想要复制到本地Excel中分析使用简单复制粘贴当网页数据更新时需要重新抓取*本节内容无法在WPS Excel实现因为暂无法启动Power Query*如你在使用Excel可以学习本节如何用Excel截取网页上的数据 #35三十六、使用Excel设计线上问卷、数据分析本节联动Forms设计线上问卷因为我忘记我的Gmail密码所以跟三十五节一样有兴趣的可以自行学习本节内容如何用Excel设计线上问卷、数据分析 #36三十七、无暂未找到相关内容三十八、LET1、函数解释LET函数可以理解为“代词”用来替代比较长的一段公式减少重复简化公式LET(名称1名称值1名称2名称值2计算公式...)最多126组举例如下毕设毕业设计绩点平均学分绩点背调背景调查一般简历上都会附上毕业设计和平均学分绩点配合公司进行背景调查使用LET效果LET毕设毕业设计绩点平均学分绩点背调背景调查一般简历上都会附上毕设和绩点配合公司进行背调苹果、香蕉略过2、基本使用LET可以用来定义表格中的范围1统计全班人数、成绩平均值LET(全班,D3:D14,COUNTA(全班))LET(全班,D3:D14,國文,E3:E14,SUM(國文)/COUNTA(全班))2计算成绩等第通常会使用IFSAVERAGEIFS(AVERAGE(E3:G3),90,A,AVERAGE(E3:G3),80,B,...)使用LET精简LET(成绩,AVERAGE(E3:G3),IFS(成绩90,A,成绩80,B,成绩80,C))3、动态阵列LET设定满800元减免80运费①插入表格、设置主题颜色无、取消筛选UNIQUE这里不赘述看三十二节的内容②计算消费总额SUMIF($O$3:$O$16,U3#,$Q$3:$Q$16)③计算运费金额和总金额IF(V3800,0,80)V3#W3#④使用LET进行简化如此就可以去掉小计、运费这两列直接获取总金额即可LET(小計,SUMIF($O$3:$O$16,U3#,$Q$3:$Q$16),運費,IF(小計800,0,80),小計運費)三十九、数据组合1、CONCATENATECONCATENATE(文字1文字2文字3)选取多个单元格进行合并CONCATENATE(E2,F2,G2)可以在公式中加入文字CONCATENATE(地址,E2,F2,G2)2、CONCATCONCAT(数据范围)选取一定数据范围进行合并CONCAT(E2:G2)3、Ampersand(单元格1)(单元格2)“文字”选取多个单元格进行合并B2C2收可以使用空白键设置文字间距B2 C2 收把地址加在收件人后面B2 C2 收CONCAT(E2:G2)4、CHAR收件人和地址自动断行CHAR(数值)不同的数值代表不同的标点符号断行符号数值Window(10)Mac(13)B2 C2 收CHAR(10)CONCAT(E2:G2)*本节后面涉及Power Query无法在WPS Excel中操作可自行前往学习如何使用PowerQuery合并数据 #39四十、模拟分析功能1、目标搜索单变量求解预售350个产品A计算出来的净利润为-45001需要卖多少个产品净利润才能和成本打平打平所以目标值为0数量为可变单元格2需要卖多少钱产品净利润才能和成本打平打平所以目标值为0售价为可变单元格3银行贷款金额期数换款等计算PMT(利率,期数,货代总额)❗ 付款期数利率的单位必须一致所以利率/12才是每月①计算每个月还款金额PMT(R5/12,R4,R3)金额为负数在R3加上-负负得正PMT(R5/12,R4,-R3)如果还款金额超负荷可以使用目标搜索选择合适的方案②假设一个月只能还10000期数和利率不变计算可贷款金额③假设一个月只能还10000想贷款60万利率不变计算还款期数2、运算列表模拟运算表将这些制成表格比较不同变数的变化1不同条件下每个月要还款的金额单个变量将数据先整理到一个表上金额以1万的差额填写下拉2不同条件还款的期数两个变量3表格美化①使用不同颜色进行标注红-黄-绿颜色越绿还款金额越小②修改单元格格式自定义显示文字4销售数量与售价对利润的影响5去除模拟运算模拟运算是一个需要大量计算的功能如果怕影响表格运行可以去掉模拟运算关闭后只有按F9模拟运算才会更新数据3、分析蓝本管理员自由替换表格内的数值来模拟不同方案的执行成果比较不同产品的销售情况这一部分的内容因为无法找到WPS Excel方案管理器暂时无法制作想要进一步学习可前往连接如何用Excel的模拟分析功能找出竞争力 #40————TBC