hello_nika

组合1    VLOOKUP+MATCH 常用套路： =VLOOKUP(C12,B2:G9,MATCH(E11,B1:G1,),) 套路指南： 1、MATCH函数用于返回指定值在某个行或列中的位置。 2、VLOOKUP函数是在表格数组的首列查找指定的值，并返回对应行中的其他列的内容。其中的第三参数是要指定返回数据区域中的哪一列。 3、以MATCH函数作为VLOOKUP函数的第三参数，能够实现动态的查询，而无需手工修改参数值。 组合2    SUM+SUMIF 常用套路： =SUM(SUMIF(B2:B9,C12:C13,G2:G9)) 套路指南： 1、给SUMIF函数指定多个单元格作为求和条件，依次计算出符合条件的总和。 2、再使用SUM函数，对SUMIF函数返回的多个结果进行求和汇总。 3、需要按Shift+ctrl+回车输入 组合3    INDIRECT+ & 常用套路： =SUM(INDIRECT(C5&”!G:G”)) 套路指南： 1、先使用&符号连接 C5&”!G:G”，使其变成具有引用样式的文本字符串”一月份!G:G”。 2、再使用INDIRECT函数将文本字符串变成真正的引用。 3、最后用SUM函数对返回的引用范围求和汇总。 组合4    INDEX+ ROW 常用套路： =INDEX(C:C,ROW(A1)*2) 套路指南： 1、ROW(A1)*2 部分结果为2，公式向下复制时，依次变成4、6、8……，也就是以2递增的序列值。 2、再使用INDEX函数，从C列返回对应位置的内容。 组合5    LEFT+ LEN 常用套路：…

今天咱们一起来学习XLOOKUP函数。这个函数目前可以在Microsoft 365和网页版Excel使用，还在使用低版本的小伙伴，可以在网页版来练练手。 先来说说这个函数的常规用法： =XLOOKUP(查找内容,查找区域,回传区域) 如下图所示，要根据G3单元格中的姓名，在左侧数据区域中查询所属部门。 H3单元格公式为： =XLOOKUP(G3,D2:D7,B2:B7) 公式中的G3，是要查询的姓名，D2:D7是姓名所在的区域，B2:B7就是咱们要回传信息的区域了。 利用Microsoft 365的自动溢出功能，下面这个公式可以同时获取部门、职务信息。 =XLOOKUP(G3,D2:D7,B2:C7) 如果要使用多个条件来查询，该怎么办呢？ 如下图所示， 要根据G3和H3的姓名以及部门，来查询对应的职务，可以使用以下公式来实现： =XLOOKUP(G3&H3,D2:D7&B2:B7,C2:C7) 第一参数，把两个查询条件使用连接符合并到一起，第二参数把两个查询区域也合并到一起，就这么简单。 如果找不到指定的内容，咱们还可以使用第四参数让他返回指定的内容或者是其他的计算公式。 比如下面这个公式，在查找不到关键字时，就会返回指定的内容“找不到啊”。 =XLOOKUP(G3,D2:D7,B2:B7,”找不到啊”) 在查询数值时，还可以使用第五参数来指定使用匹配方式。 如下图，要根据右侧的对照表，将E2单元格中的考核分变成对应的等级，可以使用以下公式： =XLOOKUP(E2,H:H,I:I,,-1) 因为要使用近似匹配方式，所以不需要屏蔽错误值，这里XLOOKUP函数的第四参数省略就可以了。 第五参数可以使用0、1或是-1来指定不同的匹配方式，本例中使用-1，表示在H列中找不到E2单元格的值时，就以比E2小的最接近值来匹配。也就是在找不到66时，就以60来匹配，并返回I列对应的等级标准“巴结”。 如果要在第一参数中使用通配符来实现关键字的查询，第五参数需要写成2。是不是微软的工程师们觉得这种写法比较二图片。 =XLOOKUP(G3&”*”,B2:B7,D2:D7,,2) 如果查询区域中有多项符合条件的记录，还可以通过第6参数来指定返回第一个还是最后一个。 如下图所示，B列有两个采购部的记录，使用以下公式会以最后一个记录来匹配。 =XLOOKUP(G3,B2:B7,D2:D7,”找不到”,,-1) 如果要以第一个记录匹配，这里的参数可以省略，或者使用1就好了。 使用XLOOKUP函数，还能够从二维表中查询数据。 如下图所示，要根据I2单元格的部门和I3单元格的月份，从左侧表格中查询对应的数值，可以使用以下公式。 =XLOOKUP(I2,A2:A7,XLOOKUP(I3,B1:G1,B2:G7)) 这个公式里，用到了两个XLOOKUP。 先来看XLOOKUP(I3,B1:G1,B2:G7)部分，目的是根据I3单元格的月份，在B1:G1单元格区域中查询到该月份，并返回B2:G7单元格对应的内容，得到的是“4月”所在列的全部数值： 最外层的XLOOKUP，以I2单元格中的部门为查询值，以A列作为查询区域，以第二个XLOOKUP返回的结果作为回传区域，最终在二维表中返回了咱们需要的结果。

SUMPRODUCT，这个函数从字面来理解，SUM是求和，PRODUCT是乘积。综合到一起，就是对各个数组参数计算乘积，并返回乘积之和。 啥是数组？咱们就简单的把它理解成一组数好了，没啥高科技含量。 接下来，咱们就一起看看这个函数有哪些典型的用法。 1、计算商品总价 如下图所示，C列是商品单价，D列是商品数量，要计算所有商品的总价，可以使用下面这个公式： =SUMPRODUCT(C2:C12,D2:D12) 公式把每一行中的单价与数量对应相乘，然后再求和。 计算过程相当于C2*D2+C3*D3+C4*D4…… 本例中，也可以将中间的逗号换成乘号： =SUMPRODUCT(C2:C12*D2:D12) 那这个乘号和逗号啥区别呢？ 当求和区域中有文本的时候，在两个数组之间使用逗号，会把文本当成0来处理。要是使用乘号的话，如果求和区域中有文本，就返回错误值了。 但是使用逗号也是有前提条件的，那就是两个数组的行、列数必须一样，否则会返回错误值。 2、计算指定条件的总价 如下图所示，要计算部门为“大食堂”的所有商品总价，可以使用以下公式： =SUMPRODUCT((A2:A12=”大食堂”)C2:C12D2:D12) 这个公式中，先使用(A2:A12=”大食堂”)，判断A列的部门是不是等于指定的部门，得到一组由TRUE和FALSE构成的逻辑值。 但是逻辑值不能直接作为数组参数，但是可以参加四则运算，所以咱们用乘号，分别乘以C列的单价和D列的数量。如果(A2:A12=”大食堂”)这部分的结果里是逻辑值TRUE，就相当于1，而逻辑值FALSE的作用就相当于是0。换句话说，符合指定部门的，就用1*单价*数量，不符合指定部门的就用0*单价*数量，最后再将各个乘积进行求和。 3、计算指定月份的总金额 如下图，要根据A列的日期，计算2月份的总金额。 公式为： =SUMPRODUCT((MONTH(A2:A12)=2)*1,D2:D12,E2:E12) MONTH(A2:A12)=2部分，先使用MONTH函数计算出A列日期的月份，然后用等式，判断是不是等于2，这部分得到的，也是一组由TRUE和FALSE构成的逻辑值。 接下来把逻辑值乘以1，TRUE*1结果为1，FALSE*1结果为0，最终变成由数值1和0构成的新数组。再用这个新数组作为参数1，分别与第二参数D2:D12和第三参数E2:E12相乘，最后计算出乘积之和。 也可以直接把各个参数之间的逗号写成乘号; =SUMPRODUCT((MONTH(A2:A12)=2)D2:D12E2:E12) 4、计算指定月份、指定部门的总金额 如下图，要计算2月份大食堂的总金额。 公式为： =SUMPRODUCT((MONTH(A2:A12)=2)*(B2:B12=”大食堂”),D2:D12,E2:E12) 公式看起来很长，但是和第三个例子是一样的，就是多了一个部门的判断条件(B2:B12=”大食堂”)。 5、根据两个条件计算采购量 如下图，要根据B1:E1单元格中的日期和A2:A12单元格中的品名，来计算对应的采购量。 公式为： =SUMPRODUCT((B1:E1=1“2021-2-2”)(A2:A12=”黄瓜”)*B2:E12) 这个数据表格和前面几个表格的结构不一样了，两个条件分别在行方向和列方向，对于这种结构的表格，计算时有一个小套路，就是分别对比水平和垂直方向的两个条件，然后乘以数值区域。 这个数值区域的行数，要与垂直方向条件区域的行数一样，并且列数要与水平方向条件区域的列数一样。 上面这句话可能不太好理解，其实结合到数据中，就可以看出来了： 数值区域是B2:E12，这里的行数是2~12行，和(A2:A12=”黄瓜”)的行数是一样的。而列数是B:E，和(B1:E1=1*”2021-2-2″)的列数是一样的。 另外，在公式中直接写日期的时候，还要注意，先加上一对半角引号，然后再乘以1变成日期序列值，否则Excel会把2021-2-2当成减法了。…

今天和大家分享一个名字拖沓，但是应用非常广泛的函数——SUMPRODUCT。 开门见山，咱们通过几个例子，一起来看下SUMPRODUCT具体能干什么样的工作。 假设上面这张图，是某个公司工资发放的部分记录表，A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取的工资金额。 那么，问题来了： 一，西门庆同志领取了几次工资？ 这是一个单条件计数的问题,通常我们使用COUNTIF函数,如果使用SUMPRODUCT函数，一般写成这样： =SUMPRODUCT((C2:C13=”西门庆”)*1) 先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组，SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。 二，西门庆同志一共领取了多少工资？ 这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数，我们可以写成这样： =SUMPRODUCT((C2:C13=”西门庆”)*D2:D13) 依然首先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘，最后统计求和得出结果。 看完了上面两个问题，有些表亲心里就嘀咕了，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么还要SUMPRODUCT干啥？ 乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对工作环境不挑不拣，它对参数类型没有啥特别要求，COUNTIF和SUMIF就不同咧，他俩要求个别参数，必须是区域数组（Range），比如下面这两个问题，COUNTIF和SUMIF就犯难了。 三，六月份财务部发放了几次工资？累计发放了多少？ 六月份财务部发放了几次工资，这是一个多条件计数的问题哈： 第一个条件，发放工资的时间必须是六月份； 第二个条件，发放工资的部门必须是财务部。 如果使用多条件计数函数COUNTIFS，判断发放工资的时间是否属于六月份，会简单问题复杂化。 而使用SUMPRODUCT函数，咱们可以简单利索的写成这样： =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”)) 统计六月份财务部发放了多少工资？ 表亲们都晓得，这是一个常见的多条件求和问题。如果使用SUMIFS函数，判断发放工资的时间是否属于六月份，和COUNTIFS类似，也会简单问题复杂化。 SUMPRODUCT跃然而至： =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”),D2:D13) 上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦，可以归纳为: =SUMPRODUCT((条件一)*(条件二)……,求和区域) 四，六月份财务部和市场部合计发放了多少工资？ 通过上面第三个问题，表亲们已经晓得如何计算六月份财务部发了多少工资，那么六月份财务部和市场部合计发了多少工资，又当怎么计算呢？ 我们经常见有些表亲把公式写成这样： =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”)*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”市场部”)*D2:D13) 这些表亲们估计心想，不就是计算两个部门吗？甭说两个，二十个咱也能算，一个加一个，一直加到二十个，世上无难事，只怕有心人嘛…… 公式写的那么长，先不谈计算速度，首先它累手啊，其次万一写错了，要修改，那也是麻烦他妈哭麻烦——麻烦死了。 其实我们可以写成这样： =SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={“财务部”,”市场部”})*D2:D13)

函数是 excel 中最重要的分析工具，但是单个函数的功能都是比较单一的，我们在解决实际问题时，往往需要多个函数组合使用，本文整理了 12 组常用Excel 函数组合，希望有助于提高工作效率。 1、MIN+IF 组合 功能：计算指定条件下的最小值 例：要计算人力部的最低分数。 G3 单元格公式：=MIN(IF(A2:A9=F3,D2:D9)) 说明：先用IF函数判断A列的部门是否等于F3指定的部门，如果条件成立，则返回D列对应的分数，否则返回逻辑值FALSE； 接下来再使用MIN函数计算出其中的最小值。MIN函数有一个特性，就是可以自动忽略逻辑值，所以只会对数值部分计算，最终得到指定部门的最低分数。 注：执行多项计算，在输入公式时要按 Shift+ctrl+Enter 键 2、IF+AND 组合 功能：并列多条件判断 例：在C列设置公式，如果A列值小于500且B列值为未到期，则返回”补款“，否则显示为空。 C4 单元格公式：=IF(AND(A4<500,B2=”否”),”补款”,””) 注：两个条件同时成立用 AND,任一个成立用 OR 函数。 3、INDEX+MATCH 组合 功能：根据条件查询 例：根据月份和费用项目，查找金额。 C10 单元格公式：=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0)) 说明：先用MATCH函数查找3月在第一行中的位置 =MATCH(B10,$A$2:$A$6,0) 再用MATCH函数查找费用项目在A列的位置 =MATCH(A10,$B$1:$G$1,0) 最后用INDEX根据行数和列数提取数值 =INDEX(区域,行数,列数） 4、VLOOKUP+MATCH…

COUNTIF 函数常规用法一 基本简介作用 用来计算区域中满足给定条件的单元格的个数。 语法讲解：COUNTIF(range,criteria) Range为需要计算其中满足条件的单元格数目的单元格区域，即（范围）。 Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本，即（条件）。 例如：条件可以表示为 32 、”32″ 、”>32″ 或 “apples” 。　 说明：Microsoft Excel提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF工作表函数， 基本运用运用举例 返回包含值12 的单元格数量：=COUNTIF （DATA,12 ） 返回包含负值的单元格数量：=COUNTIF （DATA ，”<0″ ） 返回不等于0 的单元格数量：=COUNTIF （DATA ，”<>0″ ） 返回大于5 的单元格数量：=COUNTIF （DATA ，”>5″ ） 返回等于单元格A1 中内容的单元格数量：=COUNTIF （DATA ，A1…

Excel函数：分为13类，涵盖了505个函数；本文将深入剖析：每个类别的函数，为你揭示它们的：应用技巧和使用注意事项。无论是统计数据、查找数据，还是文本处理，你都能从中：找到合适的函数，并学会如何妥善运用！ > 1、Excel统计函数：共111个函数 Excel统计函数：是Excel中最常用和强大的函数之一，可以帮助我们轻松地进行：数据统计和分析！ Excel通用计数函数： COUNT函数 Count函数：是Excel中最基本的统计函数，用于计算给定数据范围中的：数字个数！ 比如，你可以使用Count函数，来统计一组销售数据中的：订单数量。 > 函数用法：COUNT(数值1, 数值2, …)； 只需输入函数=COUNT()，然后用鼠标选中【销售额】列即可。 Count函数：=COUNT(C2:C6)； Excel条件计数函数：COUNTIF函数 Countif函数：是Excel中，更为灵活和强大的统计函数，它可以根据指定条件，统计数据的个数。 例如，你可以使用Countif函数：来统计某个部门的销售数量！ > 函数用法：COUNTIF(区域, 条件)； 第1个参数A:A：代表需要进行统计的范围，也就是地区列；第2个参数E2：代表计数条件，即部门为魏国的员工； Countif函数：=COUNTIF(A:A, E2)； Excel多条件计数函数： COUNTIFS函数 Countifs函数：是Excel中更为强大的统计函数，与Countif函数相比，Countifs函数可以根据：多个条件进行筛选和计数。 例如，你可以使用Countifs函数：来统计魏国【男性】员工的销量！ > 函数用法：COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2], …)； 第1组条件A:A,F2，代表魏国员工；第2组条件C:C,G2，代表男性员工； Countifs函数：=COUNTIFS(A:A,F2,C:C,G2)； 2、Excel三角函数：共76个函数 Excel三角函数：能够进行高精度的数字计算，确保结果的准确性和可靠性。使数据更加直观易懂。我们常用的求和函数，就是三角函数！ Excel通用求和函数：Sum函数 Sum函数：是最常用的求和函数，它的作用是对：一系列数值进行求和运算。无论是处理简单的工作表，还是复杂的工作簿，Sum函数，都能为你节省大量时间和精力。 >…

下面是一组常用Excel函数公式的用法，学会这些套路，让工作效率再高一丢丢。 1、IF函数条件判断 IF函数是最常用的判断类函数之一，能完成非此即彼的判断。 如下图，考核得分的标准为9分，要判断B列的考核成绩是否合格。 =IF(B4>=9,”合格”,”不合格”) IF，相当于普通话的“如果”，常规用法是： IF(判断的条件,符合条件时的结果,不符合条件时的结果) 2、多条件判断 如下图所示，如果部门为生产、岗位为主操 有高温补助。在D列使用公式： =IF(AND(B2=”生产”,C2=”主操”),”有”,”无”) AND函数对两个条件判断，如果同时符合，IF函数返回“有”，否则为无。 3、条件求和 如下图所示，使用SUMIF函数计算一班的总成绩： =SUMIF(D2:D5,F2,C2:C5) SUMIF用法是： =SUMIF(条件区域,指定的求和条件,求和的区域) 用通俗的话描述就是： 如果D2:D5区域的班级等于F2单元格的“一班”，就对C2:C5单元格对应的区域求和。 4、多条件求和 如下图所示，要统计部门为生产,并且岗位为主操的补助总额。 公式为： =SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2) SUMIFS用法是： =SUMIFS(求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……) 5、条件计数 如下图，要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。 =COUNTIF(B2:B12,E3) COUNTIF函数统计条件区域中，符合指定条件的单元格个数。常规用法为： =COUNTIF(条件区域,指定条件) 6、多条件计数 要求：统计统计部门为生产,并且岗位为主操的人数 公式为： =COUNTIFS(B2:B9,F2,C2:C9,G2) COUNTIFS函数统计条件区域中，符合多个指定条件的单元格个数。常规用法为： =COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……) 7、条件查找 VLOOKUP函数一直是大众情人般的存在，函数的语法为： VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找) 如下图，要查询F5单元格中的员工姓名是什么职务。…

第一章（基本功能） 1.1 工作表的新增、删除、命名、修改颜色、隐藏（鼠标单击右键）。 1.2 如何绘制表格。 1.3 制作斜线表头 Ctrl+1设置单元格格式——点击边框——选择样式——选择斜线。 1.4冻结窗口 冻结首行——选中首行——点击视图——冻结首行 首行冻结后的效果，方便浏览（冻结首列同理） 现在冻结前2行前2列（多行多列），选中C3单元格——点击冻结窗格 效果如下 第二章（行和列） 2.1 行的删除、插入、设置列宽、隐藏（鼠标选中行单击右键或者点击开始-插入/删除） 2.2 行列的移动 选中列，光标变成十字箭头，单击鼠标左键不放就行拖动，行同方法。 2.3 行列的复制 选中列，光标变成十字箭头，按住ctrl键不放，再单击鼠标左键不放就行拖动，行同方法。 第三章（快捷键) 1、 Ctrl+C 复制 2、 Ctrl+X 剪切 3、 Ctrl+V 粘贴 4、 Ctrl+Z 撤销上一步操作 5、Ctrl+S 保存文件 6、 Ctrl+A 全选…