Excel中多需需要和办法大全，专治各样不服！
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在<span style="color: black;">平常</span>工作中，<span style="color: black;">针对</span>求和<span style="color: black;">大众</span>都不陌生，但<span style="color: black;">非常多</span>人只会简单<span style="color: black;">运用</span>sum进行求和，而对多<span style="color: black;">要求</span>求和<span style="color: black;">把握</span>的却很生疏，<span style="color: black;">实质</span>在excel中，有三个可用于多<span style="color: black;">要求</span>求和的函数，分别是sum，sumifs，sumproduct，只要<span style="color: black;">把握</span>其中的任意一种，<span style="color: black;">针对</span>再<span style="color: black;">繁杂</span>的多<span style="color: black;">要求</span>求和都是信手拈来。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKYjeRJZ5TwRYXkTAeQ63gZlpZpIkLHxKJ6fwHhfoJd84aoibaImGT2fYQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">公式1：</span>=SUMIF(B:B,G2,C:C)+SUMIF(B:B,G2,D:D)+SUMIF(B:B,G2,E:E)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKY6vG8zEFoG7Xhia2KPyYgxlObiaWC2JZ4cFPAK7f38z2AUD7TQTXAJ8qw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">刚才说过<span style="color: black;">没法</span>直接用一个sumif函数求和，<span style="color: black;">由于</span>sumif<span style="color: black;">需求</span><span style="color: black;">要求</span>区域和求和区域<span style="color: black;">体积</span>相同，而本例显然不满足这个<span style="color: black;">需求</span>。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">用三个sumif分别求和后再相加，这不难理解，<span style="color: black;">然则</span><span style="color: black;">倘若</span><span style="color: black;">需求</span>和的列<span style="color: black;">更加多</span>的话，还是有点麻烦。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">公式2：</span>=SUM(IF(B$2:B$16=G2,C$2:E$16))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKYib0yxGR9QoWXMvbGHPC7yNvhkZmDmjfDLNdeaX0R9lcWuHtAEXQWdDQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这是一个数组公式，需要按住Ctrl、shift和回车键完成输入。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">数组有自扩展性，利用这个特性就<span style="color: black;">能够</span>将一列<span style="color: black;">要求</span>与三列数据进行判断。满足<span style="color: black;">要求</span>的时候为对应数字，不满足<span style="color: black;">要求</span>时得到FALSE，这是if函数省略第三参数以及第三参数前逗号的用法。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在这个公式中，用if做<span style="color: black;">要求</span>判断得到需<span style="color: black;">需求</span>和的数字，再用sum实现<span style="color: black;">最后</span>的求和结果。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">公式3：<span style="color: black;">SUMIFS函数是Excel中用于多<span style="color: black;">要求</span>求和的函数。</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">该函数的语法如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">SUMIFS(求和范围，<span style="color: black;">要求</span>范围1，<span style="color: black;">要求</span>1，<span style="color: black;">要求</span>范围2，<span style="color: black;">要求</span>2，…)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">其中，求和范围为<span style="color: black;">需求</span>和的数据范围，<span style="color: black;">要求</span>范围1为<span style="color: black;">第1</span>个<span style="color: black;">要求</span>的数据范围，<span style="color: black;">要求</span>1为<span style="color: black;">第1</span>个<span style="color: black;">要求</span>的值，条件范围2为第二个<span style="color: black;">要求</span>的数据范围，<span style="color: black;">要求</span>2为第二个<span style="color: black;">要求</span>的值，以此类推。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">举个例子，假设<span style="color: black;">咱们</span>有以下数据表格：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| <span style="color: black;">制品</span> | <span style="color: black;">营销</span>额 | <span style="color: black;">营销</span>量 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| ---- | ---- | ---- |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| A | 100 | 50 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| B | 200 | 60 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| C | 150 | 40 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| D | 120 | 30 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">| E | 80 | 20 |</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">咱们</span>想要<span style="color: black;">根据</span><span style="color: black;">营销</span>额和<span style="color: black;">营销</span>量对<span style="color: black;">制品</span>进行求和操作，求和范围分别为B2:B6和C2:C6。<span style="color: black;">倘若</span><span style="color: black;">咱们</span>只需要<span style="color: black;">根据</span><span style="color: black;">营销</span>额进行求和，<span style="color: black;">能够</span><span style="color: black;">运用</span>以下公式：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIFS(B2:B6,A2:A6,"A")</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">该公式的意思是，在A2:A6中<span style="color: black;">查询</span>值为“A”的单元格，<span style="color: black;">而后</span>将对应的B2:B6单元格中的值相加。<span style="color: black;">倘若</span><span style="color: black;">咱们</span>需要<span style="color: black;">根据</span><span style="color: black;">营销</span>量和<span style="color: black;">营销</span>额进行求和，<span style="color: black;">能够</span><span style="color: black;">运用</span>以下公式：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIFS(B2:B6,A2:A6,"A",C2:C6,">40")</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">该公式的意思是，在A2:A6中<span style="color: black;">查询</span>值为“A”的单元格，并且在C2:C6中<span style="color: black;">查询</span>大于40的单元格，<span style="color: black;">而后</span>将对应的B2:B6单元格中的值相加。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">4.IF+AND函数：满足多个<span style="color: black;">要求</span>进行判断</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">AND函数是<span style="color: black;">表率</span>“且”的从属关系，函数中的<span style="color: black;">要求</span>需<span style="color: black;">所有</span>满足</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">举例：<span style="color: black;">倘若</span>语文、数学、外语三科成绩分别≥60分的时候，成绩视为合格，反之不合格</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=IF(AND(C2>=60,D2>=60,E2>=60),"合格","不合格")</span></p><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKYjCG05kumYnLgAeozDfgG2d4RT2Wv7JwTwlcwLnibUBITibQsRJjOdEhA/640?wx_fmt=other&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">5、IF+OR函数：满足其中任一<span style="color: black;">要求</span>进行判断</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">OR函数是<span style="color: black;">表率</span>“或”的从属关系，函数中的<span style="color: black;">要求</span>只需满足任一一个<span style="color: black;">就可</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">举例：<span style="color: black;">倘若</span>语文、数学、外语三科任一科成绩＜60分，成绩视为不合格</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">反之即为：所有科目成绩都≥60分视为合格，同上</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=IF(OR(C2<60,D2<60,E2<60),"不合格","合格")</span></p><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKY71JSBEtaIkcsnFNt7yAH46N8y0rJLgYAQBxUKIonicbiaWhM5ruugPtQ/640?wx_fmt=other&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">6.</span><strong style="color: blue;"><span style="color: black;">多<span style="color: black;">要求</span>求和</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">例1：如下图所示，<span style="color: black;">需求</span>计算21寸电视机的销量。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokROAXZ8SIvtEOaOyDlia1EmKYlcn4Z4Fp4oicvxicqUCcEjgSLnQJnxytpR7eXW8Vr3x2dBRvCSlG28RA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">公式：</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">▲1、=SUMIFS(C2:C7,A2:A7,A11,B2:B7,B11)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">▲</span>2、=SUM((A2:A7=A11)*(B2:B7=B11)*C2:C7)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">▲</span>3、=SUMPRODUCT((A2:A7=A11)*(B2:B7=B11)*C2:C7)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">▲</span>4、=MMULT(TRANSPOSE((A2:A7=A11)*(B2:B7=B11)),C2:C7)数组公式</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">▲</span>5、=DSUM(A1:C7,3,A10:B11)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">公式说明：</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、公式2、4属数组公式，需要按ctrl+shift+enter三键完成输入。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、公式2,3,4属数组运算，运算效率较低，公式太多时会拖慢excel。而公式1和5属excel自带的多<span style="color: black;">要求</span>求和函数，运算效率较高。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">7、</span><span style="color: black;"><span style="color: black;">包括</span>关键字的多<span style="color: black;">要求</span>求和</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">需求</span>：统计<span style="color: black;">分部</span><span style="color: black;">包括</span>“生产”,并且岗位为主操的<span style="color: black;">补贴</span>总额</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIFS(D2:D9,B2:B9,”*”&F2&”*”,C2:C9,G2)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">SUMIFS函数支持<span style="color: black;">运用</span>通配符。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">以上7个<span style="color: black;">相关</span>Excel多<span style="color: black;">要求</span>求和的公式和<span style="color: black;">办法</span>，<span style="color: black;">亦</span>有比较难的，有你能看懂能<span style="color: black;">运用</span>的，<span style="color: black;">亦</span>有你暂时还<span style="color: black;">没法</span>理解的。但不管<span style="color: black;">怎么样</span>，相信你都能<span style="color: black;">经过</span>今天的内容有<span style="color: black;">有些</span>新的收获。</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">倘若</span>想要系统学习Excel,<span style="color: black;">或</span>直接套用模板，<span style="color: black;">能够</span><span style="color: black;">瞧瞧</span>点击下方链接或<span style="color: black;">照片</span>查看:</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/UpMchEYYgCpZqufeeibRs0aZtPQdPr9fVyqC4DicnNE6aO4s5mHwefQia9icJ2Y9ic8j7LPuUmVDf6LZiaAVriaBHmx5Q/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp" style="width: 50%; margin-bottom: 20px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/UpMchEYYgCpZqufeeibRs0aZtPQdPr9fVyqC4DicnNE6aO4s5mHwefQia9icJ2Y9ic8j7LPuUmVDf6LZiaAVriaBHmx5Q/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp" style="width: 50%; margin-bottom: 20px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/UpMchEYYgCpZqufeeibRs0aZtPQdPr9fVyqC4DicnNE6aO4s5mHwefQia9icJ2Y9ic8j7LPuUmVDf6LZiaAVriaBHmx5Q/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;"><strong style="color: blue;"><span style="color: black;">Excel模板表格图表模板4000套、甘特图580套、工作计划255套！（另函数零<span style="color: black;">基本</span>入门视频透视表VBA课程）</span></strong></a></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;"><span style="color: black;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/fHLuoBwokRNiaTiclxf9ZiaKOsEtiaQptVqojMXSNxFPjb7IehAnxAmJJCRFiaB7IKBmu9rpmXoznic1yqk34ZpjibP2Q/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1&tp=webp" style="width: 50%; margin-bottom: 20px;"></span></a></p>
你说得对，我们一起加油，未来可期。
页:
[1]