在电子表格中计算金额是使用Excel等电子表格程序或(Excel)Google 表格(Google Sheets)等电子表格网站的最基本原因之一。它在处理大量数据、自动费用跟踪(automating expense tracking)等时很有用。
自动添加单元格的公式比拉出计算器手动计算要容易得多。其他数学计算也是如此。您所需要的只是您将使用的值，我们将在下面查看的公式将为您完成所有繁重的工作。
大多数电子表格软件(spreadsheet software work)在加法、减法、乘法和除法方面的工作方式完全相同，因此无论您使用哪种电子表格工具，这些步骤都应该有效。
在电子表格中自动添加和减去(Automatically Add & Subtract In a Spreadsheet)
考虑一个包含费用、存款和当前余额的简单电子表格。你从一个显示你有多少钱的余额开始，它需要减去费用并增加存款以保持最新状态。公式用于轻松计算余额。
这是一个如何从余额中减去大笔费用的简单示例：
我们希望当前余额低于现有的 10,000 美元。为此，我们选择了要显示计算的单元格，然后在计算后放置一个=符号。
=符号对于电子表格中的任何公式都是必需的。剩下的很简单：用当前余额 (C2) 减去费用 (A3)，就像在纸上减去这些值一样。完成公式后按Enter 键会自动计算 $9,484.20 的值。(Enter)
同样，如果我们想在余额中添加存款，我们将选择我们希望数据显示在其中的单元格，在其中输入一个=符号，然后继续进行简单的数学运算以获取我们需要添加的内容：C3+B4 .
到目前为止，我们所做的是展示如何在电子表格中进行简单的加法和减法，但是我们可以使用一些高级公式在您输入费用或存款(expense or deposit)后立即计算这些结果。使用它们可以让您在这些列中输入数字以自动显示最终余额(balance show)。
为此，我们需要创建 if/then 公式。如果这是您第一次查看长公式，可能会有些困惑，但我们会将其分解为小块，以了解它们的含义。
=ifs(A5>0,C4-A5,B5>0,C4+B5,TRUE,””)
ifs部分只是说我们想要匹配多个“if”，因为我们不知道是否会填写费用或押金。如果填写了费用，我们希望运行一个公式（这将是如上所示的减法），如果输入了存款，我们希望运行一个不同的公式（加法）。
- A5>0：这是第一个判断 A5 是否大于 0 的 if 语句（即，如果那里有一个值），然后执行以下操作……
- C4-A5：如果 A5 中有值，就会发生这种情况；我们将余额减去 A5 中的值。
- B5>0：这是另一个“if”语句，询问是否填写了存款字段。(deposit field)
- C4+B5：如果有存款，则将其添加到余额中以计算新余额。
- TRUE,””：这是一个占位符，除非有要计算的内容，否则该单元格将一无所有。如果你忽略它，那么每个使用公式但没有计算内容的单元格将显示#N/A，这看起来不太好。
现在我们有了一个可以自动计算这些金额的公式，我们可以将公式拖到电子表格中，为我们在费用或存款列(expense or deposit column)中输入的任何条目做准备。
当您填写这些值时，余额列(balance column)将立即计算金额。
电子表格程序可以同时处理两个以上的单元格，因此如果您需要同时添加或减去多个单元格，有几种方法可以做到：
- =ADD(B2,B30)
- =MINUS(F18,F19)
- =C2+C3+C4+C5
- =A16-B15-A20
如何除法、乘法等(How To Divide, Multiply, & More)
除法和乘法与加法和减法一样简单。使用*进行乘法和 /进行除法。但是，当您需要将所有这些不同的计算合并到一个单元格中时，可能会有些混乱。
例如，当除法和加法(division and addition)一起使用时，它可能被格式化为=sum(B8:B9)/60。这需要B8 和 B9(B8 and B9)的总和，然后将答案(that)除以 60。由于我们需要先执行加法，所以我们先将其写在公式中。
这是另一个示例，其中所有乘法都嵌套在各自的部分中，以便一起完成，然后将这些个人的答案加在一起：=(J5*31)+(J6*30)+(J7*50)。
在此示例中，=40-(sum(J3:P3))，当计算 J3 到 P3 的总和时，我们正在确定 40 小时中剩下多少小时。由于我们要从 40 中减去总和，因此我们将 40 像常规数学问题(math problem)一样放在首位，然后从中减去总和。
嵌套计算时，请记住操作顺序以了解如何计算所有内容：
- 首先执行括号计算。
- 接下来是指数。
- 然后是乘法和除法。
- 加法和减法是最后的。
这是一个简单的数学问题(math problem)中正确和不正确使用运算顺序的示例：
30 除以 5 乘以 3(30 divided by 5 times 3)
计算它的正确方法是取 30/5（即 6）并乘以 3（得到 18）。如果你出问题了，先取 5*3（得到 15），然后取 30/15，你会得到 2 的错误答案。
How To Format Spreadsheet Cells To Automatically Calculate Amounts
Сalculating amounts in a spreadsheet is one оf the mоst basic reasons to use a spreadsheet program like Excel or a spreadsheet webѕite like Google Shеets. It’s useful whеn dealіng with large sets of data, automating expense tracking, and more.
A formula that automatically adds cells is much easier than pulling out a calculator to do the math manually. The same is true for other math calculations. All you need are the values that you’ll be working with, and the formulas we’ll look at below will do all the heavy lifting for you.
Most spreadsheet software work the exact same when it comes to adding, subtracting, multiplying, and dividing, so these steps should work no matter what spreadsheet tool you’re using.
Automatically Add & Subtract In a Spreadsheet
Consider a simple spreadsheet that holds expenses, deposits, and the current balance. You start with a balance that shows how much money you have available, and it needs to have expenses subtracted and deposits added to stay current. Formulas are used to easily calculate the balance.
Here’s a simple example of how to subtract a large expense from a balance:
We’re wanting the current balance to show up below the existing one of $10,000. To do that, we’ve selected the cell where we want the calculation to show up in, and then put an = sign followed by the calculation.
The = sign is always necessary to start off any formula in a spreadsheet. The rest is pretty straightforward: Take the current balance (C2) minus the expense (A3), just like you would if you were subtracting these values on paper. Pressing Enter when finished with the formula automatically calculates the value of $9,484.20.
Similarly, if we wanted to add a deposit to the balance, we’d select the cell we want the data to show up in, put an = sign in it, and then continue with simple math for what we need added: C3+B4.
What we’ve done so far is show how to do simple adding and subtracting in a spreadsheet, but there are some advanced formulas we could use that calculate these results right after you enter the expense or deposit. Using them will let you enter numbers into those columns to have the final balance show up automatically.
To do this, we need to create if/then formulas. It can be a little confusing if this is your first time looking at a long formula, but we’ll break it all down into small chunks to see what they all mean.
=ifs(A5>0,C4-A5,B5>0,C4+B5,TRUE,””)
The ifs part is simply saying that we’re wanting to match more than one “if” because we don’t know if the expense or the deposit will be filled out. We want one formula to run if the expense is filled out (this would be subtraction like shown above) and a different one (addition) if the deposit if entered.
- A5>0: This is the first if statement that says if A5 is greater than 0 (i.e., if there’s a value there at all), then do the following…
- C4-A5: This is what happens if there’s a value in A5; we’ll take the balance minus the value in A5.
- B5>0: This is the other ‘if’ statement that asks whether the deposit field is filled out.
- C4+B5: If there’s a deposit, then add it to the balance to calculate the new balance.
- TRUE,””: This is a placeholder that will mark the cell with nothing unless there’s something to calculate. If you omit this, then every cell that uses the formula but doesn’t have something to calculate, will show #N/A, which doesn’t look very nice.
Now that we have a formula that will automatically calculate these amounts, we can drag the formula down the spreadsheet to prepare for any entries we make in the expense or deposit column.
As you fill out these values, the balance column will calculate the amounts immediately.
Spreadsheet programs can deal with more than two cells at once, so if you need to add or subtract multiple cells simultaneously, there are a couple ways to do it:
- =ADD(B2,B30)
- =MINUS(F18,F19)
- =C2+C3+C4+C5
- =A16-B15-A20
How To Divide, Multiply, & More
Dividing and multiplying is just as easy as adding and subtracting. Use * to multiply and / to divide. However, what can get a little confusing is when you need to merge all these different calculations into one cell.
For example, when division and addition is used together, it might be formatted as =sum(B8:B9)/60. This takes the sum of B8 and B9 and then takes that answer divided by 60. Since we need the addition to be performed first, we write it first in the formula.
Here’s another example, where all the multiplication is nested in their own sections so that they’re done together, and then those individuals answers are added together: =(J5*31)+(J6*30)+(J7*50).
In this example, =40-(sum(J3:P3)), we’re determining how many hours are left out of 40 when the sum of J3 through P3 is calculated. Since we’re subtracting the sum from 40, we put 40 first like a regular math problem, and then subtract from it the total sum.
When nesting calculations, remember the order of operations to know how everything will be calculated:
- Parentheses calculations are performed first.
- Exponents are next.
- Then multiplication and division.
- Adding and subtracting are last.
Here’s an example of the proper and improper use of the order of operations in a simple math problem:
30 divided by 5 times 3
The correct way to calculate this is by taking 30/5 (which is 6) and multiplying it by 3 (to get 18). If you go out of order and take 5*3 first (to get 15) and then take 30/15, you get the wrong answer of 2.
