常用的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;">常用的excel函数公式大全</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1、</span>数字处理</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1.取绝对值</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">公式：=ABS(数字)</span></p>
<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-qvj2lq49k0/644355faafbe4becb3ec33cfe00c80f8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=RRJZZ%2BKlv0jmSR2G6T7ZqJWgzEk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2.取整，</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=INT(数字)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">int是直接取整数,它会直接返回一个整数。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e9b9e2f84c534b6fa4bf0edcaa746052~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=vHV8%2FJpRkfhyOn%2Fx%2F0Ql1dGG2WU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/855630f672834f8a9a3636def73bfd46~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=AZjj6F%2BhPjFQwTapiP3kuSOHv2U%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3.四舍五入</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=ROUND(数字,小数位数)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">返回按指定位数进行四舍五入的数值。roundup向上四舍五入，rounddown向下舍去</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ef602ceddb5f45c48780038af5e3971e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=ArDmRelNGuqf7Uj4g2QvAeGyCnI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/87e7d26f18d4408397208030f24d12d5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=i5ecGZCzSeuhH%2FQGoEKt9m5y87w%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;">4.截取</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=trunc（）</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">trunc函数<span style="color: black;">指的是</span>定位数的截取，它<span style="color: black;">能够</span><span style="color: black;">按照</span><span style="color: black;">需要</span>进行位数的截取，第二个参数默认为0，“1”<span style="color: black;">暗示</span>截取至1位小数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5.取余</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=mod()</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">mod返回余数,经过算法之后，他只会把余数返回来</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a8f79c72b01c4b2698e693fbb11154d3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=gUNjpMhjh4Xur4gWcH7RF%2B%2FK1as%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;">2、</span>判断公式</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1.把公式产生的错误值<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;">=IFERROR(A2/B2,"错误")</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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0159a083c5a54c32a3ceab68ccf25af5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=ifpfTRs0rPtKwSdehGqZmg6zHEM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2.IF多<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;">=IF(AND(E2>240,D2>80),"优秀","")，=IF(OR(E2>240,D2>80),"优秀","")</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>成立用AND,任一个成立用OR函数。等级1优秀<span style="color: black;">要求</span>为总分大于240，且英语成绩大于80分。等级2优秀<span style="color: black;">要求</span>为总分大于240分<span style="color: black;">或</span>英语成绩大于80分。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7825f9b7bf63429b83c4e31680f60466~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=rjc0fWHwhoU1IeTmGPAeZaqmro0%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;">3、</span>统计公式</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1.统计两个表格重复的内容</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式: <span style="color: black;">=COUNTIF($e$2:$e$13,A2)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">倘若</span>返回值大于0说明在另一个表中存在，0则不存在。<span style="color: black;">查询</span>e2:e13范围内A列值<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-qvj2lq49k0/68478d16c0254ebfb6206506ec7cab1c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=nXvYnk1j64P%2BsJQPFsPLu2f4lT0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2.统计不重复的内容</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明:用COUNTIF统计出每人的<span style="color: black;">显现</span>次数，用1除的方式把<span style="color: black;">显现</span>次数变<span style="color: black;">成份</span>母，<span style="color: black;">而后</span>相加。A<span style="color: black;">显现</span>3次，B<span style="color: black;">显现</span>2次，C<span style="color: black;">显现</span>2次，D<span style="color: black;">显现</span>1次，E<span style="color: black;">显现</span>1次，F<span style="color: black;">显现</span>1次，G<span style="color: black;">显现</span>1次。求解过程为：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/134b324b638d411ead818a6913099205~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=FMu%2BkX3oXAPff2aGc1IvDEC%2BCCo%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/8c0875e02525472495a3356d06a9cab4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=afPDZMj3kIcfTM39EXosLeMQ5gA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0fea8eeda79e49e3900778fdb3992117~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=SnOohwEyTje07clvWjQb4MdqdIQ%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;">=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">4、</span>求和公式</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1.隔列求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：<span style="color: black;">=SUMIF($A$2:$G$2,H$2,A3:G3)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">或<span style="color: black;">=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=1)*B3:G3)</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>规则用第2个公式，第2个公式中的*<span style="color: black;">暗示</span>且的关系，b3:g3,统计被2整除余1的列号对应的值进行相加运算。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/07129b292c7c4afd811e5498ff733f16~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=sYBx0m26qnPFFfcC3vvuwKv%2Fe1o%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">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;">=SUMIF(A:A,E2,C:C)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：SUM的基本用法</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/62c0176f190340c79b7d2fabbbc3d90b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=JybWpfdplCgKNSXF6sp6PCSEdHw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3.单<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;">说明：<span style="color: black;">倘若</span>需要进行模糊求和，就需要<span style="color: black;">把握</span>通配符的<span style="color: black;">运用</span>，其中星号是<span style="color: black;">暗示</span>任意多个字符，如"*A*"就<span style="color: black;">暗示</span>a前和后有任意多个字符，即<span style="color: black;">包括</span>A。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/922ea9b99ef747a38af2879b25596de4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=GHCK5EveBYIu3%2B%2FmaTSxhzVW4Wk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4.多<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;">=SUMIFS(C2:C7,A2:A7,E2&"*",B2:B7,F2)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：在sumifs中<span style="color: black;">能够</span><span style="color: black;">运用</span>通配符*</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/cfcf9b5fb5204ee2aad2667ef7039cb5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=pSfTIinfPw%2Fg73lK5xE%2BC8AU3gs%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5.多工作表相同单元格求和</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;">=SUM(Sheet1:Sheet19!B2)</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-qvj2lq49k0/852f5fa7e6fa40adace7cc4745ab56c7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=jGpNvrgl4nH5TFrKwvVIPaTsa9A%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>只对sheet2表到sheet4、sheet6表到sheet19求和，中间要跳过sheet5呢？ <span style="color: black;">那样</span><span style="color: black;">能够</span>输入<span style="color: black;">这般</span>的公式：<span style="color: black;"><span style="color: black;">=SUM(Sheet2:Sheet19!B2)-sheet5!A1</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">倘若</span>想对当前工作簿内除当前工作表以外所有B2单元格求和，<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;">=sum(*!B2)</span></span></p>按日期和<span style="color: black;">制品</span>求和<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;">=SUMPRODUCT((MONTH($A$2:$A$15)=F$1)*($B$2:$B$15=$E2)*$C$2:$C$15)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：SUMPRODUCT<span style="color: black;">能够</span>完成多<span style="color: black;">要求</span>求和</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a2c9f28f5cf045ab947078500adf2345~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727970241&x-signature=qFgZYKTIfqwEMXDFmY8z8G0lEr4%3D" style="width: 50%; margin-bottom: 20px;"></div>
页:
[1]