Excel多需需要和，你必须会sumproduct公式，太好用
<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>一个工作表来进行计算的</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>流水信息</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">咱们</span>的统计表，需要跨表引用数据</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/12aa338343624aaeafc4660171c51689~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=9BCKzvaW7B0N2YsEiPBbS%2FZ%2F%2BWM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">业务流水表的格式如下所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/0749b1112db143e8adb9862501821208~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=s2Z98%2FOuiq3kbKkyi%2Fu0cIRJ0uw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">统计表的格式如下所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/1b57b526780a467f8b0ece5f2739db91~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=iwBTjWAphMHCPX4scrrJeH%2BDM%2B0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>sumifs来进行汇总，<span style="color: black;">咱们</span>输入的公式是：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">sumifs的用法是：</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><span style="color: black;">咱们</span>输入的公式是：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIFS(Sheet2!$E:$E,Sheet2!$B:$B,$A2,Sheet2!$C:$C,B$1)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">重视</span>a2列要固定列标</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">b1要固定行标</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/e5832a22a0df4964b1a4b673d7680272~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=uO%2FgefEFaQYpupl%2BRtPDMUOsGXI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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;"><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;">它会提示<span style="color: black;">咱们</span>点击更新</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p6-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/263957faa9f34216a13a2f3acf79715f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=VbeiBhvgqE6I4vZ0ysubZrdRSfw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/5c1ebf58e4d34afdad918ba18ec28b93~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=RJqWUaLuH2CnIt4UF0iJa5IWYV8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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;"><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>是打开的状态，它更新才不会出错</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/7860b697ec8643b5ab7be724c6d53e95~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=SWf5meAeoYDLmSOuhsuSlF2IUl0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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;">答案<span style="color: black;">便是</span>用sumproduct公式来计算<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;">=sumproduct(iferror((求和列)*(<span style="color: black;">要求</span>列1=<span style="color: black;">要求</span>值1)*(<span style="color: black;">要求</span>列2=<span style="color: black;">要求</span>值2),0))</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>的公式是：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMPRODUCT(IFERROR((Sheet2!$E:$E)*(Sheet2!$B:$B=$A2)*(Sheet2!$C:$C=B$1),0))</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/70fe99d2eb6141b48af306bfeb1f5468~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727948264&x-signature=Vt2qrhe%2Fxt0AYSbH3%2BP%2BZjyO1bY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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;">动手试试吧！</p>
外贸网站建设方法 http://www.fok120.com/ 请问、你好、求解、谁知道等。
页:
[1]