煤化工网

Excel函数公式:IF函数强大却不为人知的技巧

网友投稿
后台-系统设置-扩展变量-手机广告位-内容正文顶部

 

Excel中,如果提起函数,大家第一时间想到的肯定是SUM,AVERAGE,IF等函数。其中IF函数看似简单,但其功能非常的强大……


ROduct="%7B%22productData%22%3A%5B%7B%22ad_block_status%22%3A0%2C%22appid%22%3A%22wx831660fe3ded4389%22%2C%22book%22%3A%7B%22author%22%3A%5B%22Excel%20Home%22%5D%2C%22book_desc%22%3A%22%E3%80%8AExcel%202016%E5%BA%94%E7%94%A8%E5%A4%A7%E5%85%A8%E3%80%8B%E5%85%A8%E9%9D%A2%E7%B3%BB%E7%BB%9F%E5%9C%B0%E4%BB%8B%E7%BB%8DExcel%202016%20%E7%9A%84%E6%8A%80%E6%9C%AF%E7%89%B9%E7%82%B9%E5%92%8C%E5%BA%94%E7%94%A8%E6%96%B9%E6%B3%95%EF%BC%8C%E6%B7%B1%E5%85%A5%E6%8F%AD%E7%A4%BA%E8%83%8C%E5%90%8E%E7%9A%84%E5%8E%9F%E7%90%86%E6%A6%82%E5%BF%B5%EF%BC%8C%E5%B9%B6%E9%85%8D%E5%90%88%E6%9C%89%E5%A4%A7%E9%87%8F%E5%85%B8%E5%9E%8B%E5%AE%9E%E7%94%A8%E7%9A%84%E5%BA%94%E7%94%A8%E6%A1%88%E4%BE%8B%EF%BC%8C%E5%B8%AE%E5%8A%A9%E8%AF%BB%E8%80%85%E5%85%A8%E9%9D%A2%E6%8E%8C%E6%8F%A1Excel%20%E5%BA%94%E7%94%A8%E6%8A%80%E6%9C%AF%E3%80%82%E5%85%A8%E4%B9%A6%E5%88%86%E4%B8%BA7%20%E7%AF%87%E5%85%B150%20%E7%AB%A0%EF%BC%8C%E5%86%85%E5%AE%B9%E5%8C%85%E6%8B%ACExcel%20%E5%9F%BA%E6%9C%AC%E5%8A%9F%E8%83%BD%E3%80%81%E5%85%AC%E5%BC%8F%E4%B8%8E%E5%87%BD%E6%95%B0%E3%80%81%E5%9B%BE%E8%A1%A8%E4%B8%8E%E5%9B%BE%E5%BD%A2%E3%80%81Excel%20%E8%A1%A8%E6%A0%BC%E5%88%86%E6%9E%90%E4%B8%8E%E6%95%B0%E6%8D%AE%E9%80%8F%E8%A7%86%E8%A1%A8%E3%80%81Excel%20%E9%AB%98%E7%BA%A7%E5%8A%9F%E8%83%BD%E3%80%81%E4%BD%BF%E7%94%A8Excel%20%E8%BF%9B%E8%A1%8C%E5%8D%8F%E5%90%8C%E3%80%81%E5%AE%8F%E4%B8%8EVBA%20%E7%AD%89%E3%80%82%E9%99%84%E5%BD%95%E4%B8%AD%E8%BF%98%E6%8F%90%E4%BE%9BExcel%202016%20%E8%A7%84%E8%8C%83%E4%B8%8E%E9%99%90%E5%88%B6%EF%BC%8CExcel%202016%20%E5%B8%B8%E7%94%A8%E5%BF%AB%E6%8D%B7%E9%94%AE%E5%8F%8AExcel%202016%20%E6%9C%AF%E8%AF%AD%E7%AE%80%E7%B9%81%E8%8B%B1%E6%96%87%E8%AF%8D%E6%B1%87%E5%AF%B9%E7%85%A7%E8%A1%A8%E7%AD%89%E5%86%85%E5%AE%B9%EF%BC%8C%E6%96%B9%E4%BE%BF%E8%AF%BB%E8%80%85%E9%9A%8F%E6%97%B6%E6%9F%A5%E9%98%85%E3%80%82%E6%9C%AC%E4%B9%A6%E9%80%82%E5%90%88%E5%90%84%E4%B8%AA%E5%B1%82%E6%AC%A1%E7%9A%84Excel%20%E7%94%A8%E6%88%B7%EF%BC%8C%E6%97%A2%E5%8F%AF%E4%BD%9C%E4%B8%BA%E5%88%9D%E5%AD%A6%E8%80%85%E7%9A%84%E5%85%A5%E9%97%A8%E6%8C%87%E5%8D%97%EF%BC%8C%E5%8F%88%E5%8F%AF%E4%BD%9C%E4%B8%BA%E4%B8%AD%E3%80%81%E9%AB%98%E7%BA%A7%E7%94%A8%E6%88%B7%E7%9A%84%E5%8F%82%E8%80%83%E6%89%8B%E5%86%8C%E3%80%82%E4%B9%A6%E4%B8%AD%E5%A4%A7%E9%87%8F%E7%9A%84%E5%AE%9E%E4%BE%8B%E8%BF%98%E9%80%82%E5%90%88%E8%AF%BB%E8%80%85%E7%9B%B4%E6%8E%A5%E5%9C%A8%E5%B7%A5%E4%BD%9C%E4%B8%AD%E5%80%9F%E9%89%B4%E3%80%82%22%2C%22publisher%22%3A%22%E5%8C%97%E4%BA%AC%E5%A4%A7%E5%AD%A6%E5%87%BA%E7%89%88%E7%A4%BE%22%7D%2C%22category_id%22%3A3%2C%22commission_ratio%22%3A%2210.00%25%22%2C%22has_commission%22%3Atrue%2C%22img_url%22%3A%22https%3A%2F%2Fres.wx.qq.com%2Fproduct_material%2FN0nzBJAdh4S765PzIhOIDpMQcPRWYQG3f_MuVOfwrHZb5HCtpNxAKtSz63yK9mhC%22%2C%22movie%22%3A%7B%22actor%22%3A%5B%5D%2C%22alt_name%22%3A%5B%5D%2C%22classify%22%3A%5B%5D%2C%22director%22%3A%5B%5D%7D%2C%22pid%22%3A%2225242264%22%2C%22source_logo_url%22%3A%22http%3A%2F%2Fmmbiz.qpic.cn%2Fmmbiz_png%2F6nVmK0mHaRr8fd8C4yNUm5BrcwmJ17I867w7sZwrxtAmG0NdTKhZj8eAEeRt0Ycgc9pnL4ib77PJ0UKDoT43MsA%2F0%22%2C%22source_name%22%3A%22%E5%BD%93%E5%BD%93%22%2C%22title%22%3A%22Excel%202016%E5%BA%94%E7%94%A8%E5%A4%A7%E5%85%A8%22%2C%22cps_desc%22%3A%22Excel%20Home%22%2C%22cps_desc_long%22%3A%22%E4%BD%9C%E8%80%85%EF%BC%9AExcel%20Home%22%7D%5D%7D" data-smartnum="" data-templateid="list" data-type="1" data-uid="1529242952673" frameborder="0">

 

一、条件判断。

目的:判断成绩是否及格。

 

方法:

在目标单元格中输入公式:=IF(C3>=60,"及格","不及格")。


二、多层级嵌套条件判断。

目的:判断成绩所属区间。

方法:

在目标单元格中输入公式:=IF(C3=100,"满分",IF(C3>=95,"优秀",IF(C3>=80,"良好",IF(C3>=60,"及格","不及格"))))。

 


三、复杂多条件判断。

目的:判断相关人员是否退休。

方法:

在目标单元格中输入公式:=IF((C3>=60)*(D3="男")+(C3>=55)*(D3="女"),"退休","")。


四、根据条件求和。

目的:计算“男”同学的成绩之和。

方法:

1、在目标单元格中输入公式:=SUM(IF(D3:D9="男",C3:C9)),按Ctrl+Shift+Enter填充。

2、在目标单元格中输入公式:=SUMIF(D3:D9,"男",C3:C9),按Ctrl+Enter填充。


五、排除错误值求和。

目的:排除原数据中的错误值并求和。

方法:

 

在目标单元格中输入公式:=SUM(IF(ISERROR(C3:C9),0,C3:C9))。并按Ctrl+Shift+Enter填充。


 

标签:

后台-系统设置-扩展变量-手机广告位-内容正文底部
留言与评论(共有 0 条评论)
   
验证码: