大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

資料分析一鍵結果就出來,不必手工抓

一般來說,想提升數字的敏感度,必須下功夫練習,但如果學會「設定格式化的條件」功能,即使沒經過訓練,也能馬上從字海裡找到需要的數字。同時,利用「SUMIF」(譯註:SUMIF 函數是數學與三角函數之一,可加總符合多項準則的所有引數)等函數或「樞紐分析表」功能,也可在短時間內統計所有資料,簡單按下一鍵就自動分析出結果。

讓異常資料跳出來——設定格式化條件
輸入數字立刻算好,用公式別按計算機
最後一欄加總?公式別一一複製,瞬間套用

1讓異常資料跳出來——設定格式化條件

使用時間序列分析數字時(如製作借貸對照表),一般人較常將注意力放在數字變動較大的項目上。因此本節要說明如何使用「設定格式化的條件」,讓報表中數字變動較大(正負變動在10%以上)的會計科目變得更醒目。

①開啟「新增規則」的功能方塊

選擇想要設定格式化條件的儲存格,點擊「常用」索引頁籤中的「設定格式化的條件」,並點選「新增規則」(見圖一)。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

②製作新增格式化規則

從「新增格式化規則」功能方塊中的「選取規則類型」裡,選擇「只格式化包含下列的儲存格」(見圖二)。之後,再從「編輯規則說明」中選取「不介於」,並於左邊的空格輸入「-10%」,在右邊的空格輸入「10%」後,點擊「格式」。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

③設定滿足條件時的儲存格格式

從「儲存格格式」的功能方塊中,選擇「字型」頁籤,設定字型樣式為粗體、文字色彩為紅色(見下頁圖三)。設定完成後,點擊「確定」,就會回到圖二的「新增格式化規則」功能方塊,再點擊一次「確定」。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

④滿足條件時的顯示方式
如此一來,超過正負10% 的儲存格,就會因為滿足設定條件,而自動以步驟③的設定方式(粗體紅字)顯示(見圖四)。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

2輸入數字立刻算好,用公式別按計算機

若能了解參照公式的架構,就能大幅縮短製作資料的時間。因此本節將說明①參照公式的四種類型、②按下F4 變換參照公式、③絕對參照的使用方式(基本)、④絕對參照的使用方式(應用)、⑤參照公式的總整理,這五個設定要點。

①參照公式的四種類型

參照公式共有四種類型:分別為❶相對參照、❷行列的絕對參照、❸行是相對參照,列是絕對參照、❹列是相對參照,行是絕對參照。

❶相對參照(例:D1)
在「A1」儲存格中輸入「=D1」。就表示,「A1」為設定參照公式儲存格,而「D1」儲存格則稱為參照來源。在相對參照的狀態下,如果複製「A1」儲存格的公式,並向下貼至「A2」及「A3」,參考來源也會向下移動為「=D2」及「=D3」(見圖一)。
另外,如果複製「A3」儲存格的公式,並向右貼至「B3」及「C3」,參照來源也會向右移動為「=E3」及「=F3」。簡單來說,相對參照是一種連動關係,會隨著帶有參照公式儲存格的移動,而變動參照來源的儲存格。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

❷行列的絕對參照(例:$D$1)
在「A1」儲存格中輸入「=$D$1」。在行與列是絕對參照的狀態下,將「A1」的公式複製並貼在「A2」、「A3」、「B3」、「C3」這幾個L字型的儲存格中,參照來源的儲存格並不會產生變動(見圖二)。在行與列都是絕對參照的狀態下,帶有參照公式的儲存格就算發生變動,參照來源也不會隨著改變。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

❸行是相對參照,列是絕對參照(例:D$1)
在「A1」儲存格中輸入「=D$1」。在行是相對參照,列是絕對參照的狀態下,就算複製「A1」儲存格的公式,並向下貼至「A2」及「A3」儲存格,因為列是絕對參照,因此參考來源並不會從「=D1」產生變動(見圖三)。但是,如果複製「A3」儲存格的公式,並向右貼至「B3」及「C3」儲存格,因為行是相對參照,參考來源也會跟著向右移動為「=E1」及「=F1」。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

❹列是相對參照,行是絕對參照(例:$D1)
在「A1」儲存格中輸入「=$D1」。列是相對參照,行是絕對參照的狀態下,複製「A1」儲存格的公式,並向下貼至「A2」及「A3」儲存格,因為列是相對參照,因此參考來源也會向下移動為「=D2」及「=D3」(見圖四)。但是,如果複製「A3」儲存格的公式,並向右貼至「B3」及「C3」儲存格,因為行是絕對參照,參考來源並不會從「D3」向右移動為「=E3」及「=F3」。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

②按下F4 變換參照公式

選定帶有參照公式的儲存格(例如D1)後,按「F4」鍵一~四次,參照公式就會如下變化。

③絕對參照的使用方式(基本)

以下就用計算2014年3月季度的資產總計中,各項資產的結構比為例,說明絕對參照的使用方式(見圖五)。首先,先計算資產總計中,現金存款占了多少百分比。在「D5」儲存格中輸入「=」後,使用滑鼠點選「C5」儲存格,再使用鍵盤輸入「/」,再次用滑鼠點選「C14」儲存格,並按一下鍵盤上的「F4」鍵。這樣一來,就會像圖五一樣,D5儲存格中出現了「= C5/$C$14」。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

如果只是要計算現金存款的結構比例,不需要使用絕對參照的公式,但若你要接著計算B行底下,所有應收帳款、流動資產總計等結構比,在「D5」儲存格設定好絕對參照的計算公式,並將計算公式向下複製貼上,作業會更有效率。

④絕對參照的使用方式(應用)

前文已在D5儲存格中輸入「= C5/$C$14」。這時,如果我們將D5儲存格的公式,以複製貼上的方式,將此公式逐項貼在代表2014年3月季度結構比的D行,與2014年3月季度的資產合計金額4000萬相除,就能正確算出各資產的占比。
但是,如果將D5的公式,貼在代表2015年3月季度結構比的F5儲存格中,公式會變成「= E5/$C$14」,因而無法算出正確占比(見下頁圖六)。當然,如果把F5 儲存格的計算公式,手動修改成「=E5/$E$14」,也能得到正確答案,但這樣的修正可能會額外多花我們1分鐘調整,還是不夠方便。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

最初在D5儲存格中輸入「= C5/$C$14」,意為「C14」不論是在行或列的方向,都被固定不變動,但就算不約束行的變化(意即將行改為相對參照),在D5 儲存格中輸入「= C5/C$14」(見圖七),並在D 行向下複製貼上,也可得到同樣的計算結果。此外,因為此公式中沒有行的絕對參照,當我們將D5儲存格的公式直接複製貼上到F5時,公式就會自動轉變成「= E5/E$14」,如此一來,就能正確計算出2015 年3 月季度的結構比。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

⑤參照公式的總整理

❶行列的絕對參照(例:$C$14)
因為行跟列都被固定,因此只能在一定要參照C14儲存格的狀況下使用。如同圖五說明的一樣,如果只是單獨計算2014年3月季度同一行的計算,使用行列的絕對參照不會有問題。

❷列是絕對參照,行是相對參照(例:C$14)
只有橫向的列被固定,因此可將此公式直接複製貼上在行方向(縱向)的儲存格,並快速得到正確答案。此外,當其他行需要使用同樣的公式計算時,也可直接複製貼上。

❸行是絕對參照,列是相對參照(例:$C14)
只有縱向的行被固定,因此可將此公式直接複製貼上在列方向(橫向)的儲存格,並快速得到正確答案。此外,當其他列需要使用同樣的公式計算時,也可直接複製貼上。

3最後一欄加總?公式別一一複製,瞬間套用

製作比較報表分析數字增減額或增減率時,經常得在待分析的數字旁,輸入同樣的公式。此時,我們大多使用滑鼠拖曳複製,但如果需要複製貼上的儲存格數量較多,就會耗上許多時間。因此本節要說明,如何瞬間將執行分析所需的公式套用到工作表。
為了計算現金存款的增減額,我們先在「G4」儲存格中輸入「= F4-E4」;在「H4」儲存格中輸入「= G4/E4」,可計算增減率,完成後會得到兩個數據(見圖一)。接著,同時選取「G4」及「H4」儲存格,並將滑鼠游標移到「H4」儲存格的右下角邊線上。此時游標會變成「+」的形狀,在此狀態下,連續點擊滑鼠左鍵兩次。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

上述操作完成之後,列表下方的儲存格就會自動套用公式並完成計算(見圖二)。

大師級Excel取巧工作術:一秒搞定搬、找、換、改、抄,資料分析一鍵結果就出來,對方秒懂、服你專業。

以上內容由大是文化授權刊登,未經允許請勿轉載。
◎更多精彩內容,請見:《大師級Excel取巧工作術