Excel處理資料方面,毋庸置疑,很強大。但有它自身的一套規則,excel很看重資料屬性,一是一,二是二。標準規範的是一個單元格記錄一個屬性
工具/原料
Excel軟體
筆記本
方法/步驟
下圖左邊所示的金額含“元”,右邊的金額是規範的數值
左邊的金額需要合計時,大大增加了計算難度,而掌握這些技能是需要花時間的。
左邊含“元”的單元格求和,兩個公式均為陣列公式:
=SUM(--SUBSTITUTE(B2:B5,"元",))
=SUM(--LEFT(B2:B5,LEN(B2:B5)-1))
如果錄入資料時,資料來源規範,遵循一個單元格記錄一個屬性,合計就非常非常容易,=SUM(E2:E5),就統計出合計。
合併單元格,僅限於需要列印的表單,不需要進行下一步運算統計彙總,比如 招聘表等等。
在源資料表中,禁止使用合併單元格。一個規範的資料來源表格應該是所有單元 格填滿,有一條記錄一條,每一行資料完整、結構整齊
A列是合併單元格,表面看似很簡潔,處理資料時會遇到很多問題,同樣的,解決這些問題,也是需要一些技能才能解決
.公式
比如對張三的銷量進行彙總,用公式:=SUMIF(A2:A5,A2,C2:C5),結果為:161。顯然不正確,因為合併單元格以後的資料,只有首個單元格有資料,其餘單元格均為空白
2.篩選
進行篩選時,也只能得到一條記錄,比如篩選“張三”的銷量
3.排序
如果需要按銷量來排序,會出現下面提示錯誤的對話方塊
三、標題不放在工作表
Excel文件的標題可以顯示在工作薄和工作表名稱中,如下右圖所示。
Excel標題行用於儲存每列資料的屬性,如“年齡”、“職務”、“銷售量”欄位,是篩選和排序的欄位依據。
上圖左邊所示的標題“員工銷量統計表”,無非就是告訴大家這是一張什麼表,除此以外不具有任何功能。
所以,可以不用標題佔用工作表首行。用右邊所示的在工作表和工作薄名稱中標識出來就可以。
四、勿用空行空列隔斷資料
如下圖所示,第4、7行為兩個空行隔斷資料。需要篩選下面所有資料,得先選中區域A1:G9,然後篩選。如果中間沒有空行,不需要選中區域,滑鼠放在任意有資料的單元格,直接篩選就可以。
如果中間沒有空行隔斷,滑鼠放在任意有資料的單元格,按ctrl+A就可以全部選定資料,但是有隔斷之後,就不能了。
不僅是篩選,在寫公式時也一樣,對於資料來源,保持資料之間的連續性很重要。
如果確實需要將資料分隔開,可以將單元格邊框加粗、改變單元格填充色等等來實現
五、勿新增多餘的合計行
一張規範的資料來源表格,不應該有下圖的那些合計行。一邊輸入資料,一邊合計求和,方法是不可取的。
資料來源還有隨時增加、刪除的可能性,所以合計也會隨之變動。
正確的做法是,先錄入資料,然後再合計。
源資料表為一張表,彙總表為另一張工作表或其餘區域,彙總可以使用函式、資料透視表等完成。
六、去掉多餘表頭和同一屬性不佔用多列
下面所示截圖,如果僅用於顯示和列印輸出,沒有任何問題,且清晰直觀。但是,做為一張資料來源表,需要下一步分析資料,這種做法就不可取。
同種屬性的資料分列記錄,對資料篩選、排序等等設定了障礙。假設,要同時篩選出事假和年假明細,按正常操作步驟就無法實現。
事假、年假、病假同屬於請假類別,具有相同屬性,應該被記錄在同一列。
下面這種做法才是中規中矩的一維資料來源表
七、同類型資料儘量使用一張工作表
不在同一張工作表,篩選、排序、引用和彙總,難度增大不少,非常不利於操作。
如下圖所示,所有資料放在一張工作表裡面,按月份篩選、彙總,都很容易實現
儘管幾十幾百字元的長公式或者一段VBA程式碼能完成多表合併,不過前提還是得看資料來源零散到什麼程度。因此,實際應用,沒必要自己為自己設定障礙和難題。