實(shí)用技巧：excel有哪些實(shí)用公式

提問人：周麗平發(fā)布時(shí)間：2021-07-09

01、同時(shí)對(duì)多個(gè)單元格執(zhí)行相同計(jì)算

在編輯工作表中的數(shù)據(jù)時(shí)，可以利用【選擇性粘貼】命令在粘貼數(shù)據(jù)的同時(shí)對(duì)數(shù)據(jù)區(qū)域進(jìn)行計(jì)算。例如，要將銷售表中的各產(chǎn)品的銷售單價(jià)快速乘以 2，具體操作步驟如下。

首先復(fù)制數(shù)據(jù)。選擇一個(gè)空白單元格作為輔助單元格，這里選擇 F2單元格，并輸入【2】，然后單擊【開始】選項(xiàng)卡【剪貼板】組中的【復(fù)制】按鈕，將單元格區(qū)域數(shù)據(jù)放入剪貼板中。

然后選擇粘貼命令。

選擇要修改數(shù)據(jù)的 B2:B7 單元格區(qū)域，然后單擊【剪貼板】組中的【粘貼】按鈕再在彈出的下拉菜單中選擇【選擇性粘貼】命令。

接下來打開【選擇性粘貼】對(duì)話框，在【運(yùn)算】欄中選中【乘】單選按鈕，單擊【確定】按鈕。

經(jīng)過上步操作，表格中選擇區(qū)域的數(shù)字都增加了 1 倍~

在【選擇性粘貼】對(duì)話框的【粘貼】欄中可以通過設(shè)置，更改粘貼的數(shù)類型、格式等，在【運(yùn)算】欄中除了可以選擇運(yùn)算方式為【乘】外，還可以選擇【加】【減】【除】3種運(yùn)算方式。

02、快速指定單元格以列標(biāo)題為名稱

日常工作中制作的表格都具有一定的格式，如工作表每一列上方的單元格一般都有表示該列數(shù)據(jù)的標(biāo)題（也稱為【表頭】），而公式和函數(shù)的計(jì)算通常也可以看作是某一列（或多列）與另一列（或多列）的運(yùn)算。為了簡(jiǎn)化公式的制作，可以先將各列數(shù)據(jù)單元格區(qū)域以列標(biāo)題命名。在 Excel 中如果需要?jiǎng)?chuàng)建多個(gè)名稱，而這些名稱和單元格引用的位置有一定的規(guī)則，這時(shí)除了可以通過【新建名稱】對(duì)話框依次創(chuàng)建名稱外，還可使用【根據(jù)所選內(nèi)容創(chuàng)建】功能自動(dòng)創(chuàng)建名稱，尤其是在需要定義大批量名稱時(shí)，該方法更能顯示其優(yōu)越性。例如，在工資表中要指定每列單元格以列標(biāo)題為名稱，具體操作步驟如下。首先根據(jù)所選內(nèi)容創(chuàng)建名稱。在下圖所示的表格中，選擇需要定義名稱的單元格區(qū)域（包含表頭），這里選擇A2:N14單元格區(qū)域，然后單擊【公式】選項(xiàng)卡【定義的名稱】組中的【根據(jù)所選內(nèi)容創(chuàng)建】按鈕。

接下來設(shè)置創(chuàng)建名稱的值。

打開【根據(jù)所選內(nèi)容創(chuàng)建名稱】對(duì)話框，選擇要作為名稱的單元格位置，這里選中【首行】復(fù)選框，即 A2:N2 單元格區(qū)域中的內(nèi)容，然后單擊【確定】按鈕，即可完成區(qū)域的名稱設(shè)置。此處將 A3:A14 單元格區(qū)域定義為【編號(hào)】，將 B3:B14 單元格區(qū)域定義為【姓名】，將 C3:C14 單元格區(qū)域定義為【所屬部門】，依次類推。

03、通過粘貼名稱快速

完成公式的輸入定義名稱后，就可以在公式中使用名稱來代替單元格的地址了，而使用輸入名稱的方法還不是最快捷的。在 Excel 中為了防止在引用名稱時(shí)輸入錯(cuò)誤，可以直接在公式中粘貼名稱。例如，要在工資表中運(yùn)用定義的名稱計(jì)算應(yīng)發(fā)工資，具體操作步驟如下。首先將定義的名稱用于公式。選擇I3單元格，然后在編輯欄中輸入【=】，接下來單擊【公式】選項(xiàng)卡【定義的名稱】組中的【用于公式】按鈕，在彈出的下拉菜單中選擇需要應(yīng)用于公式中的名稱，這里選擇【基本工資】選項(xiàng)。

然后查看計(jì)算結(jié)果。

經(jīng)過上步操作后即可將名稱輸入公式中，使用相同的方法繼續(xù)輸入該公司中需要運(yùn)用的名稱，并計(jì)算出結(jié)果。

構(gòu)建公式時(shí)，輸入一個(gè)字母后，Excel 會(huì)顯示一列匹配的選項(xiàng)。這些選項(xiàng)中也包括名稱，選擇需要的名稱后按【Tab】鍵，即可將該名稱插入公式中。

04、取消名稱的自動(dòng)引用

在公式中引用某個(gè)單元格或單元格區(qū)域時(shí)，若曾經(jīng)為該單元格或單元格區(qū)域定義了名稱，在編輯欄中即可查看在公式中自動(dòng)以名稱來代替單元格的地址。雖然這項(xiàng)功能可以使公式作用更清晰，但在某些情況下，可能并不希望采用名稱代替單元格地址，此時(shí)可以進(jìn)行一定的設(shè)置取消自動(dòng)應(yīng)用名稱。Excel 中并沒有提供直接的方法來實(shí)現(xiàn)不應(yīng)用名稱，也就是說使用名稱代替單元格地址的操作是不可逆的。然而，可以通過在【Excel 選項(xiàng)】對(duì)話框中設(shè)置來實(shí)現(xiàn)此操作，具體操作步驟如下。首先設(shè)置Lotus兼容性。打開【Excel選項(xiàng)】對(duì)話框，選擇【高級(jí)】選項(xiàng)卡，在【Lotus 兼容性設(shè)置】欄右側(cè)的下拉列表中選擇需要設(shè)置 Lotus兼容性參數(shù)的工作表，然后選中【轉(zhuǎn)換Lotus 1-2-3 表達(dá)式】復(fù)選框和【轉(zhuǎn)換Lotus 1-2-3公式】復(fù)選框，單擊【確定】按鈕完成設(shè)置。

然后返回 Excel 表格中，再次選擇公式所在的單元格或單元格區(qū)域并進(jìn)行編輯，即可看到雖然在公式編輯欄中公式還是使用的名稱，但是在單元格中則顯示單元格地址。

05、使用快捷鍵查看公式的部分計(jì)算結(jié)果

逐步查看公式中的計(jì)算結(jié)果，有時(shí)非常耗時(shí)間，在審核公式時(shí)，可以選擇性地查看公式某些部分的計(jì)算結(jié)果，具體操作步驟如下。

首先選擇公式的部分。選擇包含公式的單元格，這里選擇 L3 單元格，然后在公式編輯欄中移動(dòng)鼠標(biāo)指針，選擇該公式中需要顯示計(jì)算結(jié)果的部分，如【I3 * 0.15】。

在選擇查看公式中的部分計(jì)算結(jié)果時(shí)，注意要選擇包含整個(gè)運(yùn)算對(duì)象的部分，例如，選擇一個(gè)函數(shù)時(shí)，必須包含整個(gè)函數(shù)名稱、左括號(hào)、參數(shù)和右括號(hào)。

然后查看公式所選部分的計(jì)算結(jié)果。按【F9】鍵即可顯示出該部分的計(jì)算結(jié)果，整個(gè)公式顯示為【=IF(I3>6000,1620,0)】。

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

如果僅想查看某部分公式并不希望改變?cè)?，則按【Esc】鍵返回。

06、隱藏編輯欄中的公式

在制作某些表格時(shí)，如果不希望讓其他人看見表格中包含的公式內(nèi)容，可以直接將公式計(jì)算結(jié)果通過復(fù)制的方式粘貼為數(shù)字。但如果還需要利用這些公式進(jìn)行計(jì)算，就需要對(duì)編輯欄中的公式進(jìn)行隱藏操作了，即要求選擇包含公式的單元格時(shí)，在公式編輯欄中不顯示公式。例如，要隱藏工資表中所得稅的計(jì)算公式，具體操作步驟如下。首先選擇菜單命令。選擇包含要隱藏公式的單元格區(qū)域，這里選擇L3:L14 單元格區(qū)域，然后單擊【開始】選項(xiàng)卡【單元格】組中的【格式】按鈕，在彈出的下拉菜單中選擇【設(shè)置單元格格式】命令。接下來打開【設(shè)置單元格格式】對(duì)話框，選擇【保護(hù)】選項(xiàng)卡，選中【隱藏】復(fù)選框，單擊【確定】按鈕。