excel有哪些實用公式

2020-07-14 14:34:17

熟練掌握excel表中各種實用公式能夠快速完成各種任務，比如可以同時對多個單元格進行相同的計算或者隱藏編輯列中的公式不被他人竊取，那麼掌握這種技巧就是非常有必要的，下文介紹了幾種日常在生活中常常用到的公式，一起看看吧！

01 同時對多個單元格執行相同計算

在編輯工作表中的資料時，可以利用【選擇性貼上】命令在貼上資料的同時對資料區域進行計算。例如，要將銷售表中的各產品的銷售單價快速乘以 2，具體操作步驟如下。

首先複製資料。選擇一個空白單元格作為輔助單元格，這裡選擇 F2單元格，並輸入【2】，然後單擊【開始】索引標籤【剪貼簿】組中的【複製】按鈕，將單元格區域資料放入剪貼簿中。

然後選擇貼上命令。

選擇要修改資料的 B2:B7 單元格區域，然後單擊【剪貼簿】組中的【貼上】按鈕再在彈出的下拉式選單中選擇【選擇性貼上】命令。

接下來開啟【選擇性貼上】對話方塊，在【運算】欄中選中【乘】無線電鈕，單擊【確定】按鈕。

經過上步操作，表格中選擇區域的數位都增加了 1 倍~

在【選擇性貼上】對話方塊的【貼上】欄中可以通過設定，更改貼上的數型別、格式等，在【運算】欄中除了可以選擇運算方式為【乘】外，還可以選擇【加】【減】【除】3種運算方式。

02 快速指定單元格以列標題為名稱

日常工作中製作的表格都具有一定的格式，如工作表每一列上方的單元格一般都有表示該列資料的標題（也稱為【錶頭】），而公式和函數的計算通常也可以看作是某一列（或多列）與另一列（或多列）的運算。為了簡化公式的製作，可以先將各列資料單元格區域以列標題命名。在 Excel 中如果需要建立多個名稱，而這些名稱和單元格參照的位置有一定的規則，這時除了可以通過【新建名稱】對話方塊依次建立名稱外，還可使用【根據所選內容建立】功能自動建立名稱，尤其是在需要定義大批次名稱時，該方法更能顯示其優越性。例如，在工資表中要指定每列單元格以列標題為名稱，具體操作步驟如下。首先根據所選內容建立名稱。在下圖所示的表格中，選擇需要定義名稱的單元格區域（包含表頭），這裡選擇A2:N14單元格區域，然後單擊【公式】索引標籤【定義的名稱】組中的【根據所選內容建立】按鈕。

接下來設定建立名稱的值。

開啟【根據所選內容建立名稱】對話方塊，選擇要作為名稱的單元格位置，這裡選中【首行】核取方塊，即 A2:N2 單元格區域中的內容，然後單擊【確定】按鈕，即可完成區域的名稱設定。此處將 A3:A14 單元格區域定義為【編號】，將 B3:B14 單元格區域定義為【姓名】，將 C3:C14 單元格區域定義為【所屬部門】，依次類推。

03 通過貼上名稱快速

完成公式的輸入定義名稱後，就可以在公式中使用名稱來代替單元格的地址了，而使用輸入名稱的方法還不是最快捷的。在 Excel 中為了防止在參照名稱時輸入錯誤，可以直接在公式中貼上名稱。例如，要在工資表中運用定義的名稱計算應發工資，具體操作步驟如下。首先將定義的名稱用於公式。選擇I3單元格，然後在編輯列中輸入【=】，接下來單擊【公式】索引標籤【定義的名稱】組中的【用於公式】按鈕，在彈出的下拉式選單中選擇需要應用於公式中的名稱，這裡選擇【基本工資】選項。

然後檢視計算結果。

經過上步操作後即可將名稱輸入公式中，使用相同的方法繼續輸入該公司中需要運用的名稱，並計算出結果。

構建公式時，輸入一個字母後，Excel 會顯示一列匹配的選項。這些選項中也包括名稱，選擇需要的名稱後按【Tab】鍵，即可將該名稱插入公式中。

04 取消名稱的自動參照

在公式中參照某個單元格或單元格區域時，若曾經為該單元格或單元格區域定義了名稱，在編輯列中即可檢視在公式中自動以名稱來代替單元格的地址。雖然這項功能可以使公式作用更清晰，但在某些情況下，可能並不希望採用名稱代替單元格地址，此時可以進行一定的設定取消自動應用名稱。Excel 中並沒有提供直接的方法來實現不應用名稱，也就是說使用名稱代替單元格地址的操作是不可逆的。然而，可以通過在【Excel 選項】對話方塊中設定來實現此操作，具體操作步驟如下。首先設定Lotus相容性。開啟【Excel選項】對話方塊，選擇【高階】索引標籤，在【Lotus 相容性設定】欄右側的下拉選單中選擇需要設定 Lotus相容性引數的工作表，然後選中【轉換Lotus 1-2-3 表示式】核取方塊和【轉換Lotus 1-2-3公式】核取方塊，單擊【確定】按鈕完成設定。

然後返回 Excel 表格中，再次選擇公式所在的單元格或單元格區域並進行編輯，即可看到雖然在公式編輯列中公式還是使用的名稱，但是在單元格中則顯示單元格地址。

05 使用快捷鍵檢視公式的部分計算結果

逐步檢視公式中的計算結果，有時非常耗時間，在稽核公式時，可以選擇性地檢視公式某些部分的計算結果，具體操作步驟如下。

首先選擇公式的部分。選擇包含公式的單元格，這裡選擇 L3 單元格，然後在公式編輯列中移動滑鼠指標，選擇該公式中需要顯示計算結果的部分，如【I3 * 0.15】。

在選擇檢視公式中的部分計算結果時，注意要選擇包含整個運算物件的部分，例如，選擇一個函數時，必須包含整個函數名稱、左括號、引數和右括號。

然後檢視公式所選部分的計算結果。按【F9】鍵即可顯示出該部分的計算結果，整個公式顯示為【=IF(I3>6000,1620,0)】。

按【F9】鍵後，如果要用計算後的結果替換原公式選擇的部分，按【Enter】鍵或【Ctrl+Shift+Enter】組合鍵即可。

如果僅想檢視某部分公式並不希望改變原公式，則按【Esc】鍵返回。

06 隱藏編輯列中的公式

在製作某些表格時，如果不希望讓其他人看見表格中包含的公式內容，可以直接將公式計算結果通過複製的方式貼上為數位。但如果還需要利用這些公式進行計算，就需要對編輯列中的公式進行隱藏操作了，即要求選擇包含公式的單元格時，在公式編輯列中不顯示公式。例如，要隱藏工資表中所得稅的計算公式，具體操作步驟如下。首先選擇選單命令。選擇包含要隱藏公式的單元格區域，這裡選擇L3:L14 單元格區域，然後單擊【開始】索引標籤【單元格】組中的【格式】按鈕，在彈出的下拉式選單中選擇【設定單元格格式】命令。接下來開啟【設定單元格格式】對話方塊，選擇【保護】索引標籤，選中【隱藏】核取方塊，單擊【確定】按鈕。