15 個 Excel 公式、鍵盤快捷鍵和技巧，將為您節省大量時間

已發表: 2022-07-27

對於大多數營銷人員來說，如果您不熟悉 Excel 公式，嘗試在 Microsoft Excel 中組織和分析電子表格可能會感覺像是反复走進磚牆。您正在手動複製列並在一張紙上草草寫下長篇數學題，同時對自己說：“必須有更好的方法來做到這一點。”

說實話，有——只是你還不知道而已。

Excel 可能會很棘手。一方面，它是報告和分析營銷數據的異常強大的工具。它甚至可以幫助您使用圖表和數據透視表來可視化數據。另一方面，如果沒有適當的培訓，很容易感覺它對你不利。對於初學者來說，Excel 可以自動為您運行十幾個關鍵公式，因此您無需在桌面上使用計算器來梳理數百個單元格。為營銷人員下載 10 個 Excel 模板 [免費工具包]

什麼是excel公式？

Excel 公式可幫助您識別電子表格單元格中的值之間的關係，使用這些值執行數學計算，並在您選擇的單元格中返回結果值。您可以自動執行的公式包括求和、減法、百分比、除法、平均值，甚至日期/時間。

我們將在這篇博文中討論所有這些以及更多內容。

如何在 Excel 中插入公式

您可能想知道 Excel 頂部導航工具欄上的“公式”選項卡是什麼意思。在最新版本的 Excel 中，此水平菜單（如下所示）允許您查找 Excel 公式並將其插入電子表格的特定單元格中。

公式工具欄 Excel

您在 Excel 中使用的各種公式越多，就越容易記住它們並手動執行它們。儘管如此，上面的圖標套件是一個方便的公式目錄，您可以在磨練電子表格技能時瀏覽和參考。

Excel 公式也稱為“函數”。要將一個插入電子表格，請突出顯示要在其中運行公式的單元格，然後單擊最左側的圖標“插入函數”以瀏覽流行的公式及其作用。該瀏覽窗口將如下所示：

插入功能菜單想要更有序的瀏覽體驗？使用我們突出顯示的任何圖標（在上面第一個屏幕截圖中的紅色長矩形內）查找與各種常見主題相關的公式 - 例如金融、邏輯等。找到適合您需要的公式後，單擊“插入函數”，如上面的窗口所示。

現在，讓我們深入研究一些最關鍵的 Excel 公式，以及如何在典型情況下執行每個公式。

為了幫助您更有效地使用 Excel（並節省大量時間），我們編制了一份基本公式、鍵盤快捷鍵和其他您應該知道的小技巧和功能的列表。

注意：以下公式適用於最新版本的 Excel。 如果您使用的是稍舊版本的 Excel，則下面提到的每個功能的位置可能會略有不同。

1. 總和

所有 Excel 公式都以等號 = 開頭，後跟一個特定的文本標記，表示您希望 Excel 執行的公式。

Excel 中的 SUM 公式是您可以輸入到電子表格中的最基本的公式之一，可讓您找到兩個或多個值的總和（或總計）。要執行 SUM 公式，請使用=SUM(value 1, value 2, etc)格式輸入要相加的值。

您在 SUM 公式中輸入的值可以是實際數字，也可以等於電子表格特定單元格中的數字。

例如，要查找 30 和 80 的總和，請在電子表格的單元格中鍵入以下公式： =SUM(30, 80) 。按“Enter”，單元格將產生兩個數字的總和：110。
例如，要查找單元格 B2 和 B11 中值的總和，請在電子表格的單元格中鍵入以下公式： =SUM(B2, B11) 。按“Enter”，單元格將生成當前填寫在單元格 B2 和 B11 中的數字的總和。如果任一單元格中都沒有數字，則公式將返回 0。

請記住，您還可以在 Excel 中找到數字列表的總值。要查找單元格 B2到B11 中值的總和，請在電子表格的單元格中鍵入以下公式： =SUM(B2:B11) 。注意兩個單元格之間的冒號，而不是逗號。看看這在內容營銷人員的 Excel 電子表格中可能看起來如何，如下所示：

求和公式 Excel

2.如果

Excel 中的 IF 公式表示為=IF(logical_test, value_if_true, value_if_false) 。這允許您在“如果”電子表格中的其他內容為真或假的單元格中輸入文本值。例如，如果單元格 D2 包含單詞“Gryffindor”，=IF(D2=”Gryffindor”,”10″,”0″) 將獎勵 10 分給單元格 D2。

有時我們想知道一個值在我們的電子表格中出現了多少次。但也有一些時候，我們想要找到包含這些值的單元格，並在其旁邊輸入特定的數據。

我們將回到 Sprung 的這個例子。如果我們想給屬於格蘭芬多學院的每個人獎勵 10 分，而不是在每個格蘭芬多學生的名字旁邊手動輸入 10，我們將使用 IF-THEN 公式說：如果學生在格蘭芬多，那麼他或者她應該得到十分。

公式： IF(logical_test, value_if_true, value_if_false)
- Logical_Test：邏輯測試是語句的“IF”部分。在這種情況下，邏輯是 D2=”Gryffindor”。確保您的 Logical_Test 值用引號引起來。
- Value_if_True：如果值為真——也就是說，如果學生住在格蘭芬多——這個值就是我們想要顯示的值。在這種情況下，我們希望它是數字 10，表示該學生獲得了 10 分。注意：如果您希望結果是文本而不是數字，請僅使用引號。
- Value_if_False：如果值為假——並且學生不住在格蘭芬多——我們希望單元格顯示“0”，即 0 分。
下例中的公式： =IF(D2=”Gryffindor”,”10″,”0″)

Excel 公式和鍵盤快捷鍵：IF THEN

3. 百分比

要在 Excel 中執行百分比公式，請以=A1/B1格式輸入要查找百分比的單元格。要將生成的十進制值轉換為百分比，請突出顯示單元格，單擊主頁選項卡，然後從數字下拉列表中選擇“百分比”。

百分比本身沒有 Excel“公式”，但 Excel 可以輕鬆地將任何單元格的值轉換為百分比，這樣您就不必自己計算和重新輸入數字。

將單元格值轉換為百分比的基本設置位於 Excel 的“主頁”選項卡下。選擇此選項卡，突出顯示要轉換為百分比的單元格，然後單擊條件格式旁邊的下拉菜單（此菜單按鈕最初可能顯示為“常規”）。然後，從出現的選項列表中選擇“百分比” 。這會將您突出顯示的每個單元格的值轉換為百分比。請參閱下面的此功能。

Excel 中的百分比公式

請記住，如果您使用其他公式（例如除法公式（表示為=A1/B1 ））來返回新值，則默認情況下您的值可能顯示為小數。只需在執行此公式之前或之後突出顯示您的單元格，然後從“主頁”選項卡將這些單元格的格式設置為“百分比” - 如上所示。

4.減法

要在 Excel 中執行減法公式，請以=SUM(A1, -B1)格式輸入要減去的單元格。這將通過在要減去的單元格之前添加一個負號，使用 SUM 公式減去一個單元格。例如，如果 A1 為 10，B1 為 6，則 =SUM(A1, -B1) 將執行 10 + -6，返回值 4。

與百分比一樣，減法在 Excel 中也沒有自己的公式，但這並不意味著它不能完成。您可以通過兩種不同的方式減去任何值（或單元格內的值）。

Excel中的減法公式

使用 =SUM 公式。 要相互減去多個值，請以 =SUM(A1, -B1) 格式輸入要減去的單元格，在要減去的單元格之前加上負號（用連字符表示）。按回車鍵返回括號中包含的兩個單元格之間的差異。在上面的屏幕截圖中查看它的外觀。
使用格式 =A1-B1 。要相互減去多個值，只需鍵入等號，後跟第一個值或單元格、連字符以及要減去的值或單元格。按 Enter 鍵返回兩個值之間的差值。

5. 乘法

要在 Excel 中執行乘法公式，請以=A1*B1格式輸入要相乘的單元格。此公式使用星號將單元格 A1 乘以單元格 B1。例如，如果 A1 為 10，B1 為 6，則 =A1*B1 將返回值 60。

您可能認為 Excel 中的值相乘有其自己的公式，或者使用“x”字符來表示多個值之間的相乘。實際上，它就像星號一樣簡單 - *.

Excel中的乘法公式

要在 Excel 電子表格中將兩個或多個值相乘，請突出顯示一個空單元格。然後，以=A1*B1*C1等格式輸入要相乘的值或單元格。星號將有效地將公式中包含的每個值相乘。

按 Enter 鍵返回您想要的產品。在上面的屏幕截圖中查看它的外觀。

6.分工

要在 Excel 中執行除法公式，請以=A1/B1格式輸入要除法的單元格。此公式使用正斜杠“/”將單元格 A1 除以單元格 B1。例如，如果 A1 為 5，B1 為 10，則 =A1/B1 將返回十進制值 0.5。

Excel 中的除法是您可以執行的最簡單的功能之一。為此，請突出顯示一個空單元格，輸入一個等號“=”，然後在其後面加上您想用正斜杠“/”分隔的兩個（或更多）值。結果應採用以下格式： =B2/A2 ，如下面的屏幕截圖所示。

excel-除法公式

按 Enter，您所需的商應出現在您最初突出顯示的單元格中。

7. 日期

Excel DATE 公式表示為=DATE(year, month, day) 。此公式將返回與括號中輸入的值相對應的日期——甚至是從其他單元格引用的值。例如，如果 A1 是 2018 年，B1 是 7，C1 是 11，則 =DATE(A1,B1,C1) 將返回 7/11/2018。

在 Excel 電子表格的單元格中創建日期可能時常變化無常。幸運的是，有一個方便的公式可以讓您輕鬆設置日期格式。有兩種方法可以使用此公式：

從一系列單元格值創建日期。 為此，請突出顯示一個空單元格，輸入“=DATE”，然後在括號中輸入值創建所需日期的單元格 - 從年份開始，然後是月份編號，然後是日期。最終格式應如下所示：=DATE（年、月、日）。在下面的屏幕截圖中查看它的外觀。
自動設置今天的日期。 為此，請突出顯示一個空單元格並輸入以下文本字符串： =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) 。按 Enter 將返回您在 Excel 電子表格中工作的當前日期。

excel日期公式

在 Excel 的日期公式的任何一種用法中，您返回的日期都應該採用“mm/dd/yy”的形式——除非您的 Excel 程序的格式不同。

8. 數組

Excel 中的數組公式使用格式=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)在大括號字符中包圍一個簡單的公式。通過按 ctrl+shift+center，這將計算並返回多個範圍的值，而不僅僅是將單個單元格相加或相乘。

計算單個單元格的總和、乘積或商很容易 - 只需使用 =SUM 公式並輸入要對其執行算術運算的單元格、值或單元格範圍。但是多個範圍呢？你如何找到一大組單元格的組合值？

數值數組是在單個單元格中同時執行多個公式的有用方法，因此您可以看到一個最終的和、差、乘積或商。例如，如果您要查找多個已售商品的總銷售收入，Excel 中的數組公式非常適合您。以下是你的做法：

要開始使用數組公式，請鍵入“=SUM”，然後在括號中輸入要相乘的兩個（或三個或四個）單元格區域中的第一個。您的進度可能如下所示： =SUM(C2:C5
接下來，在公式中包含的第一個範圍的最後一個單元格之後添加一個星號。這代表乘法。在這個星號之後，輸入您的第二個單元格範圍。您將把第二個單元格範圍乘以第一個單元格。您在此公式中的進度現在應如下所示： =SUM(C2:C5*D2:D5)
準備好按 Enter 了嗎？沒那麼快……因為這個公式太複雜了，Excel 為數組保留了不同的鍵盤命令。關閉數組公式上的括號後，按C trl+Shift+Enter 。這會將您的公式識別為一個數組，將您的公式包裝在大括號字符中並成功返回兩個範圍組合的乘積。

Excel數組公式

在收入計算中，這可以顯著減少您的時間和精力。請參閱上面屏幕截圖中的最終公式。

9. 計數

Excel 中的 COUNT 公式表示為=COUNT(Start Cell:End Cell) 。此公式將返回一個值，該值等於在所需單元格範圍內找到的條目數。例如，如果有八個單元格的輸入值介於 A1 和 A10 之間，則 =COUNT(A1:A10) 將返回值 8。

Excel 中的 COUNT 公式對於大型電子表格特別有用，其中您想查看有多少單元格包含實際條目。不要被愚弄：這個公式不會對單元格本身的值進行任何數學運算。 這個公式只是為了找出選定範圍內有多少單元格被某物佔用。

使用上面的粗體公式，您可以輕鬆地計算電子表格中的活動單元格。結果看起來有點像這樣：

excel中的計數公式

10. 平均

要在 Excel 中執行平均公式，請以=AVERAGE(number1, number2, etc.)或 =AVERAGE(Start Value:End Value) 格式輸入要計算平均值的值、單元格或單元格範圍. 這將計算括號中包含的所有值或單元格範圍的平均值。

在 Excel 中查找一系列單元格的平均值使您不必找到單獨的總和，然後對總和執行單獨的除法方程。使用=AVERAGE作為您的初始文本輸入，您可以讓 Excel 為您完成所有工作。

作為參考，一組數字的平均值等於這些數字的總和除以該組中的項目數。

11. 蘇米夫

Excel 中的 SUMIF 公式表示為=SUMIF(range, criteria, [sum range]) 。這將返回所有滿足一個標準的所需單元格範圍內的值的總和。例如，=SUMIF(C3:C12,">70,000") 將僅從大於 70,000 的單元格返回單元格 C3 和 C12 之間的值的總和。

假設您想確定從與特定區號相關聯的潛在客戶列表中產生的利潤，或計算某些員工的工資總和——但前提是他們低於特定金額。至少可以說，手動執行此操作聽起來有點耗時。

使用 SUMIF 函數，它不必是 - 您可以輕鬆地將滿足某些條件的單元格的總和相加，就像上面的薪水示例中一樣。

公式： = SUMIF（範圍，標準，[sum_range]）
- 範圍：使用您的標準測試的範圍。
- Criteria：確定 Criteria_range1 中哪些單元格將被添加在一起的條件
- [Sum_range]：除了輸入的第一個Range之外，您要添加的可選單元格範圍。該字段可以省略。

在下面的示例中，我們想要計算大於 70,000 美元的工資總和。 SUMIF 函數使用公式=SUMIF(C3:C12,">70,000")將單元格 C3 到 C12 中超過該數字的美元金額相加。

12.修剪

Excel 中的 TRIM 公式表示為=TRIM(text) 。此公式將刪除在單元格中輸入的文本之前和之後輸入的所有空格。例如，如果 A2 包含名稱“Steve Peterson”，並且在名字之前有多餘的空格，則 =TRIM(A2) 將在新單元格中返回沒有空格的“Steve Peterson”。

電子郵件和文件共享是當今工作場所的絕佳工具。也就是說，直到您的一位同事向您發送了一個帶有一些非常時髦的間距的工作表。這些流氓空間不僅會使搜索數據變得困難，而且當您嘗試將數字列相加時，它們也會影響結果。

您可以使用 TRIM 函數清除任何不規則的空格，而不是根據需要費力地刪除和添加空格，該函數用於從數據中刪除多餘的空格（單詞之間的單個空格除外）。

公式： =TRIM（文本）。
- 文本：要從中刪除空格的文本或單元格。

這是一個示例，說明我們如何使用 TRIM 函數刪除名稱列表前的多餘空格。為此，我們在公式欄中輸入=TRIM(“A2”) ，並將其下方的每個名稱複製到帶有不需要空格的列旁邊的新列中。

以下是一些其他 Excel 公式，隨著數據管理需求的增長，您可能會發現這些公式很有用。

13. 左、中、右

假設您在單元格中有一行文本，您想將其分解為幾個不同的段。用戶可以根據需要利用一系列字符串函數來解構序列：LEFT、MID 或 RIGHT，而不是手動將每段代碼重新輸入到相應的列中。

剩下

用途：用於提取單元格中的前 X 個數字或字符。
公式： = LEFT（文本，number_of_characters）
- 文本：您希望從中提取的字符串。
- Number_of_characters ：您希望從最左邊的字符開始提取的字符數。

在下面的示例中，我們將=LEFT(A2,4)輸入到單元格 B2 中，並將其複製到 B3:B6 中。這使我們能夠提取代碼的前 4 個字符。

中

用途：用於根據位置提取中間的字符或數字。
公式： = MID(text, start_position, number_of_characters)
- 文本：您希望從中提取的字符串。
- Start_position ：要開始提取的字符串中的位置。例如，字符串中的第一個位置是 1。
- Number_of_characters ：您希望提取的字符數。

在本例中，我們在單元格 B2 中輸入=MID(A2,5,2) ，並將其複製到 B3:B6 中。這使我們能夠提取從代碼的第五個位置開始的兩個數字。

正確的

用途：用於提取單元格中的最後 X 個數字或字符。
公式： = RIGHT(text, number_of_characters)
- 文本：您希望從中提取的字符串。
- Number_of_characters ：要從最右邊的字符開始提取的字符數。

為了這個示例，我們在單元格 B2 中輸入=RIGHT(A2,2) ，並將其複製到 B3:B6 中。這使我們能夠提取代碼的最後兩個數字。

14. VLOOKUP

這是一個老歌，但是一個好東西——它比我們在這裡列出的其他一些公式更深入一些。但是，當您在兩個不同的電子表格上擁有兩組數據並希望將它們合併到一個電子表格中時，它特別有用。

我的同事 Rachel Sprung 以姓名、電子郵件地址和公司列表為例，他的“如何使用 Excel”教程是任何想要學習的人的必讀之物。如果您在一個電子表格中的電子郵件地址旁邊有一個人員姓名列表，在另一個電子表格中他們的公司名稱旁邊有這些人的電子郵件地址列表，但您想要這些人的姓名、電子郵件地址和公司名稱出現在一個地方——這就是 VLOOKUP 的用武之地。

注意：使用此公式時，您必須確保兩個電子表格中至少有一列相同。搜索您的數據集以確保您用於組合信息的數據列完全相同，包括沒有多餘的空格。

公式： VLOOKUP(查找值，表格數組，列號，[範圍查找])
- 查找值：兩個電子表格中的值相同。在您的第一個電子表格中選擇第一個值。在隨後的 Sprung 示例中，這意味著列表中的第一個電子郵件地址，或單元格 2 (C2)。
- 表數組：您要從中提取數據的表 2 上的列範圍，包括與表 1 中的查找值（在我們的示例中為電子郵件地址）相同的數據列以及您的數據列重新嘗試複製到工作表 1。在我們的示例中，這是“工作表 2！A:B”。 “A”表示表 2 中的 A 列，即表 2 中的列，其中列出了與表 1 中的查找值（電子郵件）相同的數據。 “B”表示 B 列，其中包含僅在工作表 2 中可用的信息，您希望將其轉換為工作表 1。
- 列號：表格數組告訴 Excel 您要復製到工作表 1 的新數據的位置（哪一列）。在我們的示例中，這將是“House”列，我們表數組中的第二個列，使其成為第 2 列。
- 範圍查找：使用 FALSE 確保您只提取精確值匹配。
帶有以下 Sprung 示例中的變量的公式： =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

在此示例中，表 1 和表 2 包含描述同一個人的不同信息的列表，兩者之間的共同點是他們的電子郵件地址。假設我們想要合併兩個數據集，以便將表 2 中的所有房屋信息轉換為表 1。這是如何工作的：

Excel 公式和快捷方式：VLOOKUP

15. 隨機化

有一篇很棒的文章將 Excel 的 RANDOMIZE 公式比作洗牌。整副牌是一列，每張牌（一副牌中有 52 張牌）是一行。 “要洗牌，”史蒂夫麥克唐納寫道，“你可以計算一個新的數據列，用隨機數填充列中的每個單元格，然後根據隨機數字段對工作簿進行排序。”

在營銷中，當您想為聯繫人列表分配一個隨機數字時，您可能會使用此功能——例如，如果您想嘗試一個新的電子郵件活動並且必須使用盲標來選擇接收它的人。通過為上述聯繫人分配號碼，您可以應用規則，“任何數字為 6 或以上的聯繫人都將被添加到新活動中。”

公式：蘭德()
- 從單列聯繫人開始。然後，在與其相鄰的列中，鍵入“RAND()”（不帶引號），從頂部聯繫人的行開始。
- RANDBETWEEN 允許您指定要分配的數字範圍。在本例中，我想使用 1 到 10。
- 底部：範圍內的最小數字。
- top:範圍內的最高數字，如下例： RANDBETWEEN (bottom,top)
- 以下示例中的公式： =RANDBETWEEN(1,10)

excel公式和鍵盤快捷鍵示例：隨機化
有用的東西，對吧？現在是錦上添花：一旦您掌握了所需的 Excel 公式，您將希望在不重寫公式的情況下將其複製到其他單元格。幸運的是，還有一個 Excel 功能。看看下面。

有時，您可能希望在電子表格的整行或整列中運行相同的公式。例如，假設您在電子表格的 A 列和 B 列中有一個數字列表，並且想要將每行的單獨總計輸入到 C 列中。

顯然，為每個單元格調整公式的值太繁瑣了，因此您要找到每行各自數字的總和。幸運的是，Excel 允許您自動完成該列；您所要做的就是在第一行輸入公式。查看以下步驟：

在空單元格中輸入公式，然後按“Enter”運行公式。
將光標懸停在包含公式的單元格的右下角。您會看到一個小的粗體“+”符號出現。
雖然您可以雙擊此符號以使用公式自動填充整個列，但您也可以手動單擊並向下拖動光標以僅填充特定長度的列。到達要輸入公式的列中的最後一個單元格後，鬆開鼠標以復制公式。然後，只需檢查每個新值以確保它對應於正確的單元格。