_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”。
