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”运行公式。
将光标悬停在包含公式的单元格的右下角。您会看到一个小的粗体“+”符号出现。
虽然您可以双击此符号以使用公式自动填充整个列，但您也可以手动单击并向下拖动光标以仅填充特定长度的列。到达要输入公式的列中的最后一个单元格后，松开鼠标以复制公式。然后，只需检查每个新值以确保它对应于正确的单元格。