许多Google 表格(Google Sheets)用户在简单任务上花费了太多时间。这主要是因为他们不知道一些最有用的Google 表格(Google Sheets)公式。公式可以让您的生活更轻松、加快流程并消除人为错误。
让我们看看Google 表格(Google Sheets)中的 10 个最有用的公式，这些公式旨在为您节省时间。
设置公式
在Google 表格(Google Sheets)上使用公式很容易。在文本框中(text box)，只需键入“=”，后跟要使用的公式。当您键入公式时，应该会弹出一个小问号框。(question mark box)选择该框以了解有关使用该公式的更多信息。
例如，如果我们输入“=add(”，我们将了解有关如何使用此函数的更多信息。Sheets 告诉我们它返回两个数字的总和，我们可以通过添加值 1(value 1)来格式化它，一个逗号，然后括号内的值为 2 。(value 2)
这意味着输入“ =add (1,3)”应该返回 1+3 的答案。
除了输入“1”和“3”，我们还可以引用Sheets上的单元格。通过添加单元格名称(cell name)（由后跟行的列表示），表格(Sheets)将自动为这些给定单元格执行公式。
因此，如果我们输入“=add(B3,C3)”，单元格B3 和 C3(B3 and C3)中的值将被添加。它给了我们同样的答案。
此方法适用于以下部分中的大多数公式。
Google表格(Google Sheets)中的10个有用公式(Helpful Formulas)可节省您的时间
以下是Google 表格(Google Sheets)中最有用的 10 个公式，可帮助您节省时间。有很多隐藏的表格公式可以帮助您，但这些是日常用户可以使用并从中受益的公式。
1.用 TODAY() 抛弃日历(Ditch the Calendar with TODAY())
感觉就像我们发现自己在问“今天是几号？” 每一天。在Sheets中，它变成了一个更大的问题——跟踪截止日期和倒计时需要每天更新。也就是说，除非您使用TODAY () 函数。
将其放入单元格后，您将获得今天的日期。它每天更新，(day and doesn)不需要您(t require)做任何事情。
Excel具有一整套内置的日期和时间公式(date and time formulas)。这使得很容易找到两个日期之间的差异。使用TODAY () 函数，您可以拥有一个不断变化的第二个变量。
2. COUNTIF ()使条件计数更容易(Makes Conditional Counting Easier)
表格还允许您使用所谓的“条件计数”进行一些编码。这是当您仅在项目符合特定条件时才计算项目。
例如，如果您想知道您的调查参与者中有多少人拥有两辆或更多汽车，您可以使用COUNTIF () 语句。
条件将是一个大于或等于 2 的值。
如果不使用此公式，您将不得不手动扫描数据并找到您要查找的数字。
您还可以使用其他类似的 IF 函数，例如SUMIF、AVERAGEIF等。
3. 使用 SUM() 添加单元格
我们最喜欢的函数之一是SUM () 函数。它将选定范围内的所有数量相加。总和只是一堆数字相加。例如，2、3 和 4 的总和为 9。
您可以使用SUM () 将一整列相加。
随着您不断添加新行，SUM () 函数将自动更新并添加新项目。它可用于正数、负数或零值数。
想要快速制定预算？SUM () 将是你最好的朋友。
4.使用 CONCATENATE() 将文本放在一起(Put Text Together with CONCATENATE())
如果要将文本字符串添加在一起，则不能只使用加法。您需要使用称为串联的编程术语。该术语是指将文本添加在一起的想法。
您可以将多个单独的单元格连接在一起，并创建一个包含所有文本的单元格。使用CONCATENATE () 是执行此过程的自动方式。
如果没有此公式，您将不得不将文本分别复制并粘贴到单个单元格中。当您查看包含多行的文件时，此过程会花费大量时间。
如果您想组合人们的名字和姓氏或创建个性化消息，此公式非常有用。
5. TRIM() 删除不需要的额外空格(TRIM() Removes Unwanted Extra Spaces)
每当从其他来源复制和粘贴文本块时，格式化就变成了一场噩梦。很多时候，您会有不想要的空间破坏整体格式。
您可以手动删除每个空格，也可以使用Google 表格中的(Google Sheets)TRIM () 命令。此命令减少了消息中的空格数，并且单词之间只有一个空格。
这对于设置为接受用户书面回复的表格也很有帮助。它消除了意外(accident and results)双倍行距的人为错误，并使文档看起来非常干净。
6. TEXT() 将值转换为其他格式(TEXT() Converts Values to Other Formats)
Google 表格(Google Sheets)中最令人头疼的问题之一是重新格式化放入单元格中的值。表格会尝试猜测您想要哪种格式，但您可能会更改整个单元格部分。
或者，您可以尝试TEXT () 函数。此公式将更改一般文本、单词字符串、值、美元金额、百分比、日期和许多其他选项的格式。
例如，如果您要复制和粘贴需要转换为美元值的数据块，则可以使用此公式来执行此操作。您还可以更改值后显示的小数位数。
7. GOOGLETRANSLATE() 的内置翻译(Built-In Translation with GOOGLETRANSLATE())
表格还可以加快您的翻译速度。如果您有一列使用您知道的语言的短语，您可以创建多列翻译成不同语言的短语。
例如，我们可以在一个文档中将常用短语从英语翻译(English)成西班牙语、法语和日语。(French)这可以帮助国际团队理解同一份文件。
此公式由 Google 的翻译服务提供。您还可以使用此公式，通过将某个文本复制并粘贴到Sheets(Sheets)中来找出它是什么语言。
您可以将源语言(source language)保持为“自动”，表格(Sheets)将确定它是什么语言，然后将其翻译成您可以理解的所选语言。
8.快速 SORT() 数据(Quickly SORT() Data)
维护一个排序的数据列表可能是一件令人头疼的事情。当输入新数据或更改数据时，您的手动排序列表将被打乱。
您可以使用内置的SORT(SORT) () 命令，而不是每次插入数据时手动对数据进行排序。这会自动生成升序或降序值列表。
运行后，它会在自己的row 或 column(row or column)中编译排序列表。然后，您可以使用此范围在工作表中执行其他步骤。
9.使用TRANSPOSE ()交换行和列(Swap Rows and Columns)
您(Did)是否在第一次制作工作表时不小心交换了工作表的行和列？(row and column)或者，也许客户向(client sent)您发送了您想要添加到文档中的数据，但需要切换行和列。
不管怎样，你都可以试试TRANSPOSE ()命令。
它是这样工作的：行和列被翻转。表中的相应数据也发生了变化，以确保翻转后它在正确的位置。
随着旧数据的变化，转置表也随之更新和变化。
10.快速找到 AVERAGE()(Quickly Find the AVERAGE())
在Sheets(Sheets)中使用AVERAGE () 命令将输出一组给定数字的平均值。如果你不记得(t remember)了，平均值是你把所有的数字加起来除以有多少个数字。
这是一个有用的数学命令(math command)。如果没有AVERAGE () 公式，您将只能用纸和计算器来解决它。
只需选择您要为其求平均值的数字范围，然后Google 表格(Sheets)将完成剩下的工作。
使用公式(Use Formulas)提高您的 Google 表格效率(Your Google Sheets Efficiency)
是时候打开表格并尝试一下您刚刚学到的这 10 个有用的公式了。(helpful formulas)它们旨在节省您的时间，让您的生活更轻松(life easier)。自己尝试一下，看看它们有多容易。
有我们错过的最喜欢的公式吗？发表评论，让我们知道。
10 Helpful Formulas in Google Sheets to Save You Time
Many Google Sheets users spend too much time on simple tasks. This iѕ largely because they don’t know some of the most helpful Google Sheets formulas. Formulas are here to makе your life easier, expedite the proceѕs, and takе out human error.
Let’s look at 10 of the most helpful formulas in Google Sheets that are designed to save you time.
Setting Up a Formula
Using a formula on Google Sheets is easy. In a text box, just type “=” followed by the formula you want to use. There should be a little question mark box that pops up as you type a formula. Select that box to learn more about using that formula.
For example, if we type in “=add(“ we’ll learn more about how to use this function. Sheets tells us that it returns the sum of two numbers and that we can format it by adding value 1, a comma, then value 2 within the parenthesis.
That means that typing “=add(1,3)” should return the answer to 1+3.
Instead of typing “1” and “3”, we can also refer to cells on Sheets. By adding the cell name (denoted by the column followed by the row), Sheets will automatically perform the formula for those given cells.
So, if we type in “=add(B3,C3)”, the values from cells B3 and C3 will be added. It gives us the same answer.
This approach works for most of the formulas in the following sections.
10 Helpful Formulas in Google Sheets to Save You Time
The following are 10 of the most helpful formulas in Google Sheets to save you time. There are plenty of hidden Sheets formulas that can help you, but these are ones that the everyday user can use and benefit from.
1. Ditch the Calendar with TODAY()
It feels like we catch ourselves asking “what’s today’s date?” every single day. In Sheets, it becomes an even bigger issue — tracking deadlines and counting down dates requires a daily update. That is, unless you use the TODAY() function.
After putting it in a cell, you’re left with today’s date. It updates every day and doesn’t require you to do anything.
Excel has a whole range of built-in date and time formulas. This makes it easy to find a difference between two dates. With the TODAY() function, you can have a constantly changing second variable.
2. COUNTIF() Makes Conditional Counting Easier
Sheets also lets you do a little bit of coding using what’s known as “conditional counting”. This is when you only count an item if it falls within certain criteria.
For example, if you want to know how many of your survey participants own two or more cars, you can use a COUNTIF() statement.
The condition would be a value that’s greater than or equal to two.
Without using this formula, you would have to scan through the data by hand and find the number you’re looking for.
There are other IF functions like this you can use as well such as SUMIF, AVERAGEIF, and more.
3. Add Cells with SUM()
One of our favorite functions is the SUM() function. It adds together all the quantities within a selected range. A sum is just a bunch of numbers added together. For example, the sum of 2, 3, and 4 is 9.
You can use SUM() to add a whole column together.
As you keep adding new rows, the SUM() function will automatically update and add the new item. It can be used for positive, negative, or zero-value numbers.
Want to make a quick budget? SUM() will be your best friend.
4. Put Text Together with CONCATENATE()
If you want to add strings of text together, you can’t just use addition. You’ll need to use a programming term called concatenation. This term refers to the idea of adding text together.
You can concatenate multiple separate cells together and create a single cell that includes all of the text. Using CONCATENATE() is the automatic way of performing this process.
Without this formula, you would have to copy and paste text separately into a single cell. This process takes a lot of time when you’re looking at files that contain multiple rows.
This formula is great if you want to combine people’s first and last names or create personalized messages.
5. TRIM() Removes Unwanted Extra Spaces
Whenever blocks of text are copied and pasted from other sources, formatting becomes a nightmare. Many times, you’ll have unwanted spaces that ruin the overall format.
You can either manually delete every space, or you can use the TRIM() command in Google Sheets. This command reduces the number of spaces in a message and leaves you with only one space between words.
This could also be helpful for Sheets that are set up to accept written responses from users. It removes the human error of double-spacing by accident and results in a document that looks really clean.
6. TEXT() Converts Values to Other Formats
One of the big headaches in Google Sheets is reformatting values that are put into a cell. Sheets will try to guess which format you want, but you could be left changing a whole section of cells.
Alternatively, you can try the TEXT() function. This formula will change the formatting from general text, strings of words, values, dollar amounts, percentages, dates, and a number of other options.
If you’re copying and pasting a chunk of data that you need to turn into dollar values, for example, you can use this formula to do it. You can also change the number of decimals shown after the value.
7. Built-In Translation with GOOGLETRANSLATE()
Sheets can also expedite your translations. If you have a column of phrases in a language you know, you can create multiple columns of a translated phrase into different languages.
For example, we can translate common phrases from English to Spanish, French, and Japanese in a single document. This can help an international team understand the same document.
This formula is courtesy of Google’s translation services. You can also use this formula to find out what language a certain text is by copying and pasting it into Sheets.
You can keep the source language as “auto”, and Sheets will determine what language it is, then translate it into the selected language you can understand.
8. Quickly SORT() Data
Maintaining a sorted list of data can be a headache. As new data is inputted or data is changed, your hand-sorted list will get disrupted.
Rather than manually sorting the data every time it’s inserted, you can use the built-in SORT() command. This automatically generates a list of ascending or descending values.
After running, it compiles the sorted list in its own row or column. You can then use this range to take additional steps in your Sheet.
9. Swap Rows and Columns with TRANSPOSE()
Did you accidentally swap the row and column of your sheet when you were first making it? Or maybe a client sent you over data that you’d like to add to your document, but the rows and columns need to be switched.
Whatever the case, you can try the TRANSPOSE() command.
Here’s how it works: The rows and columns are flipped around. The corresponding data in the table is also changed to ensure it’s in the correct place after the flip.
As the old data is changed, the transposed table also updates and changes.
10. Quickly Find the AVERAGE()
Using the AVERAGE() command in Sheets will spit out the mean of a given set of numbers. If you don’t remember, the average is when you add up all the numbers and divide by how many numbers there are.
This is a useful math command. Without the AVERAGE() formula, you’ll be left solving it with paper and a calculator.
Just select the range of numbers you want to find an average for, then Sheets will do the rest.
Use Formulas to Improve Your Google Sheets Efficiency
Time to open Sheets and try out these 10 helpful formulas you just learned. They’re designed to save you time and make your life easier. Try them out on your own and see how easy they are.
Have a favorite formula that we missed? Leave a comment and let us know.
