大師級Excel取巧工作術：一秒搞定搬、找、換、改、抄，資料 ...

讓異常資料跳出來——設定格式化條件輸入數字立刻算好，用公式別按計算機最後 ... 在相對參照的狀態下，如果複製「A1」儲存格的公式，並向下貼至「A2」 ... 跳至主要內容 GutenbergBlocks CombinedBlocks ColumnsBlock Image&GalleryBlock QuoteBlock TableBlock Button&Separator Pages NarrowWidth DefaultWidth RightSidebar LeftSidebar AboutUs Contact PhysicalAddress 304NorthCardinalSt.DorchesterCenter,MA02124 Facebook Instagram Line YouTube Telegram 資料分析一鍵結果就出來，不必手工抓 一般來說，想提升數字的敏感度，必須下功夫練習，但如果學會「設定格式化的條件」功能，即使沒經過訓練，也能馬上從字海裡找到需要的數字。

同時，利用「SUMIF」（譯註：SUMIF函數是數學與三角函數之一，可加總符合多項準則的所有引數）等函數或「樞紐分析表」功能，也可在短時間內統計所有資料，簡單按下一鍵就自動分析出結果。

讓異常資料跳出來——設定格式化條件 輸入數字立刻算好，用公式別按計算機 最後一欄加總？公式別一一複製，瞬間套用 １讓異常資料跳出來——設定格式化條件 使用時間序列分析數字時（如製作借貸對照表），一般人較常將注意力放在數字變動較大的項目上。

因此本節要說明如何使用「設定格式化的條件」，讓報表中數字變動較大（正負變動在10%以上）的會計科目變得更醒目。

①開啟「新增規則」的功能方塊 選擇想要設定格式化條件的儲存格，點擊「常用」索引頁籤中的「設定格式化的條件」，並點選「新增規則」（見圖一）。

②製作新增格式化規則 從「新增格式化規則」功能方塊中的「選取規則類型」裡，選擇「只格式化包含下列的儲存格」（見圖二）。

之後，再從「編輯規則說明」中選取「不介於」，並於左邊的空格輸入「-10%」，在右邊的空格輸入「10%」後，點擊「格式」。

③設定滿足條件時的儲存格格式 從「儲存格格式」的功能方塊中，選擇「字型」頁籤，設定字型樣式為粗體、文字色彩為紅色（見下頁圖三）。

設定完成後，點擊「確定」，就會回到圖二的「新增格式化規則」功能方塊，再點擊一次「確定」。

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

２輸入數字立刻算好，用公式別按計算機 若能了解參照公式的架構，就能大幅縮短製作資料的時間。

因此本節將說明①參照公式的四種類型、②按下F4變換參照公式、③絕對參照的使用方式（基本）、④絕對參照的使用方式（應用）、⑤參照公式的總整理，這五個設定要點。

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

❶相對參照（例：D1） 在「A1」儲存格中輸入「=D1」。

就表示，「A1」為設定參照公式儲存格，而「D1」儲存格則稱為參照來源。

在相對參照的狀態下，如果複製「A1」儲存格的公式，並向下貼至「A2」及「A3」，參考來源也會向下移動為「=D2」及「=D3」（見圖一）。

另外，如果複製「A3」儲存格的公式，並向右貼至「B3」及「C3」，參照來源也會向右移動為「=E3」及「=F3」。

簡單來說，相對參照是一種連動關係，會隨著帶有參照公式儲存格的移動，而變動參照來源的儲存格。

❷行列的絕對參照（例：$D$1） 在「A1」儲存格中輸入「=$D$1」。

在行與列是絕對參照的狀態下，將「A1」的公式複製並貼在「A2」、「A3」、「B3」、「C3」這幾個L字型的儲存格中，參照來源的儲存格並不會產生變動（見圖二）。

在行與列都是絕對參照的狀態下，帶有參照公式的儲存格就算發生變動，參照來源也不會隨著改變。

❸行是相對參照，列是絕對參照（例：D$1） 在「A1」儲存格中輸入「=D$1」。

在行是相對參照，列是絕對參照的狀態下，就算複製「A1」儲存格的公式，並向下貼至「A2」及「A3」儲存格，因為列是絕對參照，因此參考來源並不會從「=D1」產生變動（見圖三）。

但是，如果複製「A3」儲存格的公式，並向右貼至「B3」及「C3」儲存格，因為行是相對參照，參考來源也會跟著向右移動為「=E1」及「=F1」。

❹列是相對參照，行是絕對參照（例：$D1） 在「A1」儲存格中輸入「=$D1」。

列是相對參照，行是絕對參照的狀態下，複製「A1」儲存格的公式，並向下貼至「A2」及「A3」儲存格，因為列是相對參照，因此參考來源也會向下移動為「=D2」及「=D3」（見圖四）。

但是，如果複製「A3」儲存格的公式，並向右貼至「B3」及「C3」儲存格，因為行是絕對參照，參考來源並不會從「D3」向右移動為「=E3」及「=F3」。

②按下F4變換參照公式 選定帶有參照公式的儲存格（例如D1）後，按「F4」鍵一～四次，參照公式就會如下變化。

③絕對參照的使用方式（基本） 以下就用計算2014年3月季度的資產總計中，各項資產的結構比為例，說明絕對參照的使用方式（見圖五）。

首先，先計算資產總計中，現金存款占了多少百分比。

在「D5」儲存格中輸入「=」後，使用滑鼠點選「C5」儲存格，再使用鍵盤輸入「/」，再次用滑鼠點選「C14」儲存格，並按一下鍵盤上的「F4」鍵。

這樣一來，就會像圖五一樣，D5儲存格中出現了「=C5/$C$14」。

如果只是要計算現金存款的結構比例，不需要使用絕對參照的公式，但若你要接著計算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分鐘調整，還是不夠方便。

最初在D5儲存格中輸入「=C5/$C$14」，意為「C14」不論是在行或列的方向，都被固定不變動，但就算不約束行的變化（意即將行改為相對參照），在D5儲存格中輸入「=C5/C$14」（見圖七），並在D行向下複製貼上，也可得到同樣的計算結果。

此外，因為此公式中沒有行的絕對參照，當我們將D5儲存格的公式直接複製貼上到F5時，公式就會自動轉變成「=E5/E$14」，如此一來，就能正確計算出2015年3月季度的結構比。

⑤參照公式的總整理 ❶行列的絕對參照（例：$C$14） 因為行跟列都被固定，因此只能在一定要參照C14儲存格的狀況下使用。

如同圖五說明的一樣，如果只是單獨計算2014年3月季度同一行的計算，使用行列的絕對參照不會有問題。

❷列是絕對參照，行是相對參照（例：C$14） 只有橫向的列被固定，因此可將此公式直接複製貼上在行方向（縱向）的儲存格，並快速得到正確答案。

此外，當其他行需要使用同樣的公式計算時，也可直接複製貼上。

❸行是絕對參照，列是相對參照（例：$C14） 只有縱向的行被固定，因此可將此公式直接複製貼上在列方向（橫向）的儲存格，並快速得到正確答案。

此外，當其他列需要使用同樣的公式計算時，也可直接複製貼上。

３最後一欄加總？公式別一一複製，瞬間套用 製作比較報表分析數字增減額或增減率時，經常得在待分析的數字旁，輸入同樣的公式。

此時，我們大多使用滑鼠拖曳複製，但如果需要複製貼上的儲存格數量較多，就會耗上許多時間。

因此本節要說明，如何瞬間將執行分析所需的公式套用到工作表。

為了計算現金存款的增減額，我們先在「G4」儲存格中輸入「=F4-E4」；在「H4」儲存格中輸入「=G4/E4」，可計算增減率，完成後會得到兩個數據（見圖一）。

接著，同時選取「G4」及「H4」儲存格，並將滑鼠游標移到「H4」儲存格的右下角邊線上。

此時游標會變成「+」的形狀，在此狀態下，連續點擊滑鼠左鍵兩次。

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

以上內容由大是文化授權刊登，未經允許請勿轉載。

◎更多精彩內容，請見：《大師級Excel取巧工作術》 推薦閱讀 如何成為可靠的人？ 時間是超越的最大祕訣 女人是夢想與現實差異的平衡者 很多時候，我們忽略了老天爺安排的劇本….幸福其實是一場「內心戲」 停止去做「非做不可的事」 明日若我離去，記得加醬油 焦慮不是病，只是提醒你需要改變 焦慮不用隱藏，你比想像中更有掌控力 財富自由，為的是人生有選擇的自由 誰說只能「二選一」？增加選項！增加選項！增加選項！