_Excel公式教程
(2016-01-28 23:18:45)
转载
▼
标签： excel公式教程 单元格格式 简化公式分类： Excel公式教程-原理篇
Excel单元格的显示值跟单元格的值可能不一致。大多数情况下公式计算用的是单元格的值，而不是显示值。利用单元格格式可以简化公式。
【例1】下图是一个考勤表，在区域B2:AF2输入数字1，单元格显示“√”。
第一步，设置数据有效性，使区域内只能输入数字1。
选定区域B2:AF2，打开“数据有效性”对话框，在“设置”选项卡“允许”下面选择“自定义”，在“公式”编辑框输入=B2=1（当前单元格为B2），单击“确定”按钮。
第二步，设置单元格格式为“√”。
然后在区域B2:AF2中输入1就能显示“√”。
用公式=SUM(B2:AF2)即可统计张三的出勤天数。
【例2】如图，在区域B2:B10输入数字1显示“男”，输入数字0显示“女”。
第一步，设置数据有效性，使区域内只能输入数字1或0。
选定区域B2:B10，打开“数据有效性”对话框，在“设置”选项卡“允许”下面选择“整数”，设置数据介于最小值0和最大值1之间。单击“确定”按钮。
第二步，设置单元格格式为“男;;女”。
然后在区域B2:B10中输入数字1就能显示“男”，输入数字0就能显示“女”。
如果要统计“男”的人数，可以用=SUM(B:B)而不需用COUNTIF函数；统计“女”的人数，可用总人数减“男”的人数，或者用=COUNTIF(B:B,0)。
【例3】下图是一个借贷余三栏账。余额栏以绝对值显示，正数表示借方余额，方向栏显示“借”；负数表示贷方余额，方向栏显示“贷”，零则显示“-”，方向栏显示“平”。各行的余额=上一行余额+本行借方-本行贷方。
第一步，设置单元格格式。
设置好表格字段名和边框线后，在单元格输入数据和公式之前，首先设置单元格格式：
（1）A列设置日期格式并居中；
（2）B、C列设置格式“#,##0.00”；
（3）D列设置居中；
（4）E列设置格式“#,##0.00;#,##0.00;"-"??”。
（5）第一行字段名设置居中和粗体，
第二步，编写E列计算余额的公式。
首先在单元格E2输入期初余额-5000，然后在单元格E3输入以下公式并向下复制：
=E2+B3-C3
该公式有如下弊端：公式本意是引用上一行的余额，但如果在公式上面插入一行，则公式引用的是上两行的余额；如果删除公式上面的一行，则公式返回错误值#REF!，因为公式原来引用的单元格被删除了。
可把上述公式改进如下：
=INDEX(E:E,ROW()-1)+B3-C3
公式中ROW()返回公式所在行的行号，ROW()-1就是公式上一行的行号，INDEX(E:E,ROW()-1)就是取E列上一行的余额数。
第三步，设置D栏的“借贷余”方向。
在D2输入公式=SIGN(E2)或直接=E2，并设置单元格格式为“借;贷;平”。把D2的公式和格式向下复制。
D列如果不设置单元格格式，可在D2输入公式=IF(E2>0,"借",IF(E2<0,"贷","平"))并向下复制。
【例4】假设单元格A1是日期序列数，要在单元格B1、C1、D1分别显示该日期的年、月和日。
第一步，选择区域B1:D1，输入公式=$A1，按Ctrl+Enter。
第二步，分别设置单元格B1、C1、D1的格式为“e”“m”“d”。
