最常用的if函数11种经典用法，超级实用！值得保藏学习
<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 style="color: black;">Excel秘籍大全</span></span><span style="color: black;">，正文<span style="color: black;">起始</span></span></strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">功能：</span></strong><span style="color: black;">判断一个<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;"><strong style="color: blue;"><span style="color: black;">结构：</span></strong><span style="color: black;">=IF（测试<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;">大众</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;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">1、</span>单<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 style="color: black;">怎样</span>计算职工的全勤奖？当出勤的天数大于等于22天，则奖励200元。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">C2=IF(B2>=22,200,0)</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;">2、</span>第三参数若为文字，则需要添加双引号。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/e5413cfb73ce44499164137c348c4f09~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=bE6wig9nG%2BIeXW1RVsptuV6fBLk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">2、</span>多<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 style="color: black;">按照</span>给<span style="color: black;">区别</span>的级别，给职工发放<span style="color: black;">补助</span>。初级500元，中级1000元，高级1500元。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">C2=IF(B2="初级",500,IF(B2="中级",1000,IF(B2="高级",1500)))</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d72784fa74a4437eadd91cbc06c5a847~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=zc%2FHQjdo3L4uMu7D8ANSrd1yoQY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">3、</span>多<span style="color: black;">要求</span>判断：满足多个<span style="color: black;">要求</span>中的任意一个<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 style="color: black;">理学</span>】只要有一科分数达到90分以上，则评定为【优秀】。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">D2=IF(OR(B2>=90,C2>=90),"优秀","")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：OR函数是一个判断函数，只要满足一个<span style="color: black;">规律</span><span style="color: black;">要求</span>，结果就会<span style="color: black;">表示</span>TURE，否则返回FALSE。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c61164a98241450b8c9ec0b500c07fdb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=STpciasSVNdDKXEDyGOsPGYM24U%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">4、</span>多<span style="color: black;">要求</span>判断：<span style="color: black;">同期</span>满足多个<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 style="color: black;">是不是</span>到了可<span style="color: black;">离休</span>的年龄？男，60岁可<span style="color: black;">离休</span>，女，55岁可<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;">D2=IF(AND(B2="男",C2>60),"是",IF(AND(B2="女",C2>55),"是",""))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：AND函数是一个判断函数，<span style="color: black;">仅有</span>满足所有<span style="color: black;">规律</span><span style="color: black;">要求</span>，结果才会<span style="color: black;">表示</span>TURE，否则返回FALSE。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/06c36e14da994083821251457bc2b592~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=TK%2FCT88uUSZGIv9GoPUcs4hZ1ic%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">5、</span>多<span style="color: black;">要求</span><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;">与【vlookup函数】结合<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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">G2=VLOOKUP(E2&F2,IF({1,0},$A:$A&$B:$B,$C:$C),2,0)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：按ctrl+shift+enter组合键结束。按shift+7可生产连接符号&。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/055df07bf2814d01ab2069580c6bdc9f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=n8AKip52bDkZHS70s%2FUbI29KaIg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">6、</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 style="color: black;">怎样</span>批量计算员工的产量奖？总产量>=18000，奖励500元，15000<=总产量<18000，奖励300元，12000<=总产量<15000，奖励200元，总产量<12000，奖励0元。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">D2=IF(C2>=18000,500,IF(C2>=15000,300,IF(C2>=12000,200,IF(C2<12000,0))))</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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/caf72bc006c64ca6ab3b1557a9c78100~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=ZQoUU7qeWHju%2Fov13lFHGsifq%2Fo%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">7、</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 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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">B2=IF(A2="",B1+1,1)</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;">第1</span>个合并单元格<span style="color: black;">是由于</span>3个单元格合并而成，<span style="color: black;">实质</span>上“<span style="color: black;">营销</span>部”只<span style="color: black;">表示</span>在<span style="color: black;">第1</span>个单元格，<span style="color: black;">亦</span><span style="color: black;">便是</span>A2单元格，A3、A4单元格，<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-6w9my0ksvp/9b1a27d4b1ec414d9f7a50ec182eefba~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=2bi09x4DJjQJBWpzuERJd0cmXqM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">8、</span>单<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 style="color: black;">怎样</span>对【AA】型号的产量进行求和汇总？</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">F2=SUM(IF(B2:B7=E2,C2:C7,""))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：按ctrl+shift+enter组合键结束。当B2:B7=E2<span style="color: black;">要求</span>成立时，就返回C列相<span style="color: black;">针对</span>的值，<span style="color: black;">而后</span>用SUM函数进行求和。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b2e036cb05574edb825758f1ddac571d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=Qj4qFq5PQ%2BQH9r0mLF%2FhrDAWhuA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">9、</span>多<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 style="color: black;">怎样</span>计算【拉伸部】【AA】型号的总产量？</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">H2=SUM(IF((B2:B7=F2)*(C2:C7=G2)=1,D2:D7,""))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：按ctrl+shift+enter组合键结束。当(B2:B7=F2)和(C2:C7=G2)<span style="color: black;">同期</span>满足<span style="color: black;">要求</span>时，会返回结果1，就返回D列相<span style="color: black;">针对</span>的值，<span style="color: black;">而后</span>用SUM函数进行求和。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/3057147010994f1bbb06bda0f25c22b0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=BfP751GCdK%2F9I%2FXJ2RmlY18kq5o%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;">当<span style="color: black;">查询</span>值在数据表中不<span style="color: black;">位置于</span><span style="color: black;">第1</span>列，那又该<span style="color: black;">怎样</span>用vlookup函数进行<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;">F2=VLOOKUP(E2,IF({1,0},B2:B8,A2:A8),2,FALSE)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：IF({1,0},B2:B8,A2:A8)里的<span style="color: black;">第1</span>个参数{1,0}，1在<span style="color: black;">这儿</span>看作TRUE，0看作FALSE。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6cca570178c14f349a8e94006cdc9dd5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=z75J9qXxj8xzTMpQ09KZVSyqInU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">十<span style="color: black;">1、</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 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;"><span style="color: black;">C9=SUM(IF(ISERROR(C2:C8),0,C2:C8))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">说明：按crl+shift+enter组合键结束。先用ISERROR函数判断一个值<span style="color: black;">是不是</span>为错误值，若为错误值返回TRUE，否则返回FALSE，接着用IF函数进行判断，<span style="color: black;">倘若</span>存在错误值，就返回0，否则返回值本身，最后用SUM函数进行求和。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b46db00743ab4ce2a1a07ba685172087~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=4jbTY520i%2FcIx1M95OslBZrufnU%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-6w9my0ksvp/332b6ef2c6e74419b17c7a1ccf099cf3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968649&x-signature=BOlr%2FqHYWPi5qElWTfAlNhi4xz4%3D" style="width: 50%; margin-bottom: 20px;"></div>
你的努力一定会被看见，相信自己，加油。 你的言辞如同繁星闪烁，点亮了我心中的夜空。 楼主节操掉了，还不快捡起来！
页:
[1]