Dùng Formula Trong Word / TOP 6 # Xem Nhiều Nhất & Mới Nhất 4/2023 # Top View

Insert Table Formulas in Word: Overview

You can insert table formulas in Word tables to perform simple mathematical functions on data. To insert table formulas in Word that add, subtract, multiply, and divide numbers in the table cells, you insert formulas into cells where you want to show the answers to the mathematical operations performed by the formulas.

The Parts of Table Formulas in Word

When you insert table formulas in Word, you insert a field that performs calculations on values in other table cells. Formulas always start with an equal sign (=). They often refer to the cell addresses from which they gather the data for their calculations. These cell addresses can be linked together with standard mathematical operators. These include the plus sign (+), minus sign (-), multiplication sign (*), and division sign (/), among others. You can also perform functions, like SUM, on a cell range in a table. So, a formula might be expressed “=SUM(Above),” which adds the values of the cells above the cell into which you inserted this formula.

A cell address is a way of referring to a cell. A cell address is the relative location of a cell in a table. Imagine there are letters at the top of each column, starting with “A” at the far left and then continuing to increase one letter at a time to the right. In addition, imagine each row has a number assigned to it. The topmost row is row “1.” The row numbering then continues downward, increasing by one for each row. The cell address is the column letter, followed by the row number. For example, the top left cell is always cell A1. B1 is always to the right of A1. Here is a table with the cell addresses entered into the corresponding cells to help you see the cell address naming convention.

Instead of showing the formula itself in the cell, the cell shows the to the formula. Why? Because when you insert table formulas in Word in a cell, Word knows it should show the answer to the formula, not the formula itself. Formulas display their results by default, not their actual contents.

How to Insert Table Formulas in Word

When the “Formula” dialog box first opens, Word tries to guess the formula you want. For example, if you insert table formulas in Word in a cell at the end of a column of continuous numbers, Word assumes you want to add the cell values in the column above the cell. Therefore, Word enters the formula =SUM(Above) as the default formula in the “Formula” dialog box.

After entering the formula into the “Formula:” field, you can then use the “Number format:” drop-down to select a numeric pattern. This helps show the result in a specific numeric format.

In Word, you can use the terms “LEFT,” “RIGHT,” “ABOVE,” and “BELOW” to refer to adjacent cells in the row or column to the left of, to the right of, above, or below the cell within which you insert table formulas in Word. This is a convenient way of selecting the cell range for the function. You can also enter a cell range by typing the cell address of the upper-left cell in the cell range, followed by a colon symbol (:), then followed by the cell address of the lower-right cell in the range. For example, you could also type =SUM(A1:A4) into the “Formula:” field to add the contents of cells A1 through A4.

The word SUM is a formula function. If want to perform one mathematical operation on a range of cells, you can use functions like SUM, AVERAGE, MAX, and MIN when you insert table formulas in Word, instead of individually writing the cell addresses and mathematical operators. Word provides many standard functions in the “Paste function:” drop-down. Selecting any function from the list of functions in the drop-down menu adds it to the formula in the “Formula:” field.

Insert Table Formulas in Word: Instructions

Instructions on How to Insert Table Formulas in Word

Optionally, to select a function to add to the formula shown in the “Formula:” field, use the “Paste function:” drop-down.

Optionally, to format the display of the numeric formula’s result, use the “Number format:” drop-down.

Insert Table Formulas in Word: Video Lesson

The following video lesson, titled ” Inserting Table Formulas,” shows how to insert table formulas in Word. It is from our complete Word tutorial, titled ” Mastering Word Made Easy v.2019 and 365.”

Want to get the word count in Excel? Believe it or not, Excel does not have an inbuilt word counter.

But don’t worry.

A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you.

In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell.

Formula to Get Word Count in Excel

Before I give you the exact formula, let’s quickly cover the logic to get the word count.

Suppose I have a sentence as shown below for which I want to get the word count.

While Excel cannot count the number of words, it can count the number of spaces in a sentence.

So to get the word count, we can count these spaces instead of words and add 1 to the total (as the number of space would be one less the number of words).

Now there can be two possibilities:

There is a single space between each word

There are multiple spaces between words.

So let’s see how to count the total number of words in each case.

Example 1 – When there is a single space between words

Let’s say I have the following text in cell A1: Let the cat out of the bag

To count the number of words, here is the formula I would use:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

This would return ‘7’ as a result.

Here is how this formula works:

LEN(A1) – This part of the formula returns 26, which is the total number of characters in the text in cell A1. It includes the text characters as well as the space characters.

SUBSTITUTE(A1,” “,””) – This part of the formula removes all the spaces from the text. So the result, in this case, would be Letthecatoutofthebag.

LEN(SUBSTITUTE(A1,” “,“”) – This part of the formula counts the total number of characters in the text that has no spaces. So the result of this would be 20.

LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”)) – This would subtract the text length without spaces from the text length with spaces. In the above example, it would be 26-20 which is 6.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”))+1 – We add 1 to the overall result as the total number of spaces is one less than the total number of words. For example, there is one space in two words and two spaces in three words.

Now, this works well if you have only one space character between words. But it wouldn’t work if you have more than one space in between words.

In that case, use the formula in the next example.

Example 2: When there are multiple spaces between words

Let’s say you have the following text: Let the cat   out of    the bag

In this case, there are multiple space characters between words.

To get the word count, we first need to remove all the extra spaces (such that there is only one space character between two words) and then count the total number of spaces.

Here is the formula that will give us the right number of words:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

This is a similar formula used in the above example, with a slight change – we have also used the TRIM function here.

Excel TRIM function removes any leading, trailing, and extra spaces (except single spaces between words).

The rest of the formula works the same (as explained in Example 1).

Note: If there are no spaces between words, it is considered as one word.

Using VBA Custom Function to Count Words in Excel

While the above formulas work great, if you have a need to calculate the word count often, you can use VBA to create a custom function (also called a User Defined Function).

The benefit of using a custom function is that you can create it once and then use it like any other regular Excel function. So instead of creating a long complex formula as we did in the two examples above, you have a simple formula that takes the cell reference and instantly gives you the word count.

Here is the code that will create this custom function to get the word count in Excel.

Function WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1

This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria.

As everyone knows, Excel is all about storing and crunching numbers. However, apart from calculating values, you may also need to count cells with values – with any value, or with specific value types. For example, you may want a quick count of all items in a list, or the total of inventory numbers in a selected range.

Microsoft Excel provides a couple of special functions for counting cells: COUNT and COUNTA. Both all very straightforward and easy-to-use. So let’s take a quick look at these essential functions first, and then I will show you a few Excel formulas to count cells that meet certain condition(s), and clue you in on the quirks in counting some value types.

Excel COUNT function – count cells with numbers

You use the COUNT function in Excel to count the number of cells that contain numerical values.

The syntax of the Excel COUNT function is as follows:

COUNT(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.

In the modern versions of Excel 2016, Excel 2013, Excel 2010, and Excel 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 ‘values’.

For example, the following formula returns the total number of numeric cells in range A1:A100:

=COUNT(A1:A100)

Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.

Using COUNT function in Excel – things to remember

Below are the two simple rules by which the Excel COUNT function works.

If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.

If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like “5”).

For example, the following COUNT formula returns 4, because the following values are counted: 1, “2”, 1/1/2016, and TRUE.

=COUNT(1, "apples", "2", 1/1/2016, TRUE)

Excel COUNT formula examples

And here are a few more examples of using the COUNT function in Excel on different values.

To count cells with numeric values in one range, use a simple count formula like

=COUNT(A2:A10)

The following screenshot demonstrates which types of data are counted and which are ignored:

To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:

=COUNT(B2:B7, D2:D7)

Tips:

If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.

Excel COUNTA function – count cells with values (non-blank cells)

The COUNTA function in Excel counts the number of cells in a range that are not empty.

The syntax of the Excel COUNTA function is akin to that of COUNT:

COUNTA(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges where you want to count non-blank cells.

For example, to count cells with value in range A1:A100, use the following formula:

=COUNTA(A1:A100)

To count non-empty cells in several non-adjacent ranges, use a COUNTA formula similar to this:

=COUNTA(B2:B10, D2:D20, E2:F10)

As you can see, the ranges supplied to an Excel COUNTA formula do not necessarily need to be of the same size, i.e. each range may contain a different number of rows and columns.

Please keep in mind that Excel’s COUNTA function counts cells containing any type of data, including:

Numbers

Dates / times

Text values

Boolean values of TRUE and FALSE

Error values like #VALUE or #N/A

Empty text strings (“”)

In some cases, you may be perplexed by the COUNTA function’s result because it differs from what you see with your own eyes. The point is that an Excel COUNTA formula may count cells that visually look empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.

In other words, the only cells that the COUNTA function does not count are absolutely empty cells.

The following screenshot demonstrates the difference between Excel COUNT and COUNTA functions:

Tip. If you just want a quick count of non-blank cells in a selected range, simply have a look at Status Bar at the bottom right corner of your Excel window:

If you just want a quick count of, simply have a look at Status Bar at the bottom right corner of your Excel window:

Excel COUNTIF function – count cells that meet one condition

The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:

COUNTIF(range, criteria)

In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.

For example, to count how many cells in range A2:A15 are “Apples”, you use the following COUNTIF formula:

=COUNTIF(A2:A15, "apples")

Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:

For more information about using the COUNTIF function in Excel, check out the following tutorial: COUNTIF in Excel – count if not blank, greater than, duplicate or unique

Excel COUNTIFS function – count cells that match several criteria

The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010, 2013, and 2016.

For example, to count how many “Apples” (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:

And again, to make your COUNTIFS formula more versatile, you can supply cell references as the criteria:

You will find plenty more formula examples here: How to use Excel COUNTIFS function with multiple criteria.

Count the number of cells in a range (ROWS and COLUMNS functions)

If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:

=ROWS(range)*COLUMNS(range)

For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:

=ROWS(A1:D7)*COLUMNS(A1:D7)

You may also be interested in

1. Cách dùng chức năng replace trong word

Chức năng replace là một chức năng khá phổ biến và rất được ưa dùng, tuy không có vai trò quan trọng như một số các chức năng khác nhưng replace khiến cho bạn phải hài lòng bởi vì sự tiện lợi của nó. Không chỉ có việc thay thế các từ hay dùng mà còn sử dụng để đổi vị trí các từ.

Thay thế từ

3. Cách dùng chức năng replace trong word

Chọn thay thế tất cả Replace all và tất cả các từ UniCA sẽ được thay đổi thành UNICA theo ý bạn muốn.

Đảo vị trí các từ

4. Cách dùng chức năng replace trong word

Thực hiện các thao tác như trên hình khi nhấn vào Replace all bạn sẽ thấy xuất hiện hộp thoại và nhấn vào No.

Như vậy việc thay thế hay đảo vị trí các từ thật đơn giản chỉ cần sử dụng chức năng replace trong word thì mọi việc trở nên thật dễ dàng. Ngoài ra chức năng còn được kết hợp với các chức năng khác để thực hiện những công việc nâng cao hơn khó hơn. Nhưng Replace đã rất là hữu ích khi sử dụng chức năng chính của nó, đây là một trong những tính năng được dùng rất phổ biến. Bởi không cần những đến thao tác các bước trên màn hình mà có thể sử dụng phím tắt để tìm một cách nhanh chóng hơn, nhấn tổ hợp phím Ctrl + H để trực tiếp thay thế không chỉ ở word mà còn có thẻ sử dụng được cả những công cụ tạo văn bản khác.

Cách sử dụng tính năng Replace để đảo vị trí các từ trong Word

Tính năng Replace trong Word các bạn thường biết đến với chức năng thay thế từ để tìm kiếm và thay thế từ trong Word. Ngoài chức năng mà các bạn thường sử dụng ra thì Replace cón có tính năng đảo vị trí từ trên Word.

Bước 1: Chọn từ cần bôi đen. Bước 2: Mở cửa sổ Find and Replace bằng cách ấn tổ hợp Ctrl + H. Bước 3: Xuất hiện hộp thoại Find anh Replace, trong thẻ Replace các bạn nhấn More để mở rộng tùy chọn. Bước 4: Các bạn thiết lập một tùy chỉnh:

– Đánh dấu chọn các tùy chọn Use Wildcards.

– Trong ô Replace With các bạn nhập 2 1 giữa 2 và 1 là dấu cách.

Bước 5: Sau khi thiết lập xong, các bạn chọn Replace All để đảo vị trí các từ.

Khi xuất hiện các thông báo, bạn có muốn mở rộng trên toàn văn bản không thì bạn chọn No để từ chối.

Chắc chắn bạn nào cũng muốn thành thạo về word để tạo ra những file word chuyên nghiệp, nhưng vấn đề thời gian tài chính chất lượng là những lo ngại của bạn, giới thiệu cho bạn khóa học LÀM CHỦ WORD 2016 TỪ CƠ BẢN ĐẾN NÂNG CAO của UNICA giải đáp cho bạn toàn bộ những vấn đề của bạn.