Đề Xuất 6/2023 # How To Use The Excel Subtotal Function # Top 15 Like | Beiqthatgioi.com

# Đề Xuất 6/2023 # How To Use The Excel Subtotal Function # Top 15 Like

Cập nhật nội dung chi tiết về How To Use The Excel Subtotal Function mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Use the SUBTOTAL function to get a subtotal in a list or database. Despite the name, SUBTOTAL has the ability to perform a variety of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list). SUBTOTAL returns an aggregate result from a set of data. By default, SUBTOTAL excludes values in rows hidden by a filter, which makes SUBTOTAL very useful in Excel Tables.

The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

Examples

Below are a few examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the function_num argument:

=

SUBTOTAL

(

109

,

range

)

// SUM

=

SUBTOTAL

(

103

,

range

)

// COUNT

=

SUBTOTAL

(

101

,

range

)

// AVERAGE

Available calculations

SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. There are 11 functions available, each with two options, as seen in the table below. Notice the values are “paired” (e.g. 1-101, 2-102, 3-103, and so on). This is related to how SUBTOTAL deals with manually hidden rows. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. When function_num is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden.

Function Include hidden Ignore hidden

AVERAGE 1 101

COUNT 2 102

COUNTA 3 103

MAX 4 104

MIN 5 105

PRODUCT 6 106

STDEV 7 107

STDEVP 8 108

SUM 9 109

VAR 10 110

VARP 11 111

Note: SUBTOTAL always ignores values in cells that are hidden with a filter. Values in rows that have been “filtered out” are never included, regardless of function_num.

SUBTOTAL in Excel Tables

The SUBTOTAL function is used when you display a Total row in an Excel Table. Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc.  The reason Excel uses SUBTOTAL for calculations in the Total row of an Excel Table is because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table. That is, as you filter rows in a table with a Total row, you’ll see the calculations update automatically to respect the filter.

SUBTOTAL with outlines

Note: although the Outline feature is an “easy” way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice.

Notes

When function_num is between 1-11, SUBTOTAL includes values that are hidden

When function_num is between 101-111, SUBTOTAL excludes values that are hidden

In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.

SUBTOTAL ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

SUBTOTAL is designed to work with vertical data values arranged vertically. In horizontal ranges, values in hidden columns are always included.

## How To Use The Excel Countifs Function

The COUNTIFS function in Excel counts the number of cells in a range that match one supplied criteria. Unlike the older COUNTIF function, COUNTIFS can apply more more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first pair is required.  For each additional condition, you must supply another range/criteria pair. Up to 127 range/criteria pairs are allowed.

COUNTIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIFS requires a cell range for range arguments, you can’t use an array.

Basic example

With the example shown, COUNTIFS can be used to count records using 2 criteria as follows:

=

COUNTIFS

(

C5:C14

,

"red"

,

D5:D14

,

"tx"

)

// red and TX

Notice the COUNTIFS function is not case-sensitive.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:

=

COUNTIFS

(

A1:A10

,

100

)

// count equal to 100

=

COUNTIFS

(

A1:A10

,

"jim"

)

// count equal to "jim"

Note: showing one condition only for simplicity. Additional conditions must follow the same rules.

Value from another cell

When using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but the cell reference is not:

=

COUNTIFS

(

A1:A10

,

"<"

&

B1

)

// count cells less than B1

Note: COUNTIFS is one of several functions that split conditions into two parts: range + criteria. This causes some inconsistencies with respect to other formulas and functions.

Not equal to

To construct "not equal to" criteria, use the "" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10:

Blank cells

COUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=

COUNTIFS

(

A1:A10

,

""

)

// blank

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:

The safest way hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:

=

COUNTIFS

(

A1:A10

,

"<"

&

DATE

(

2020

,

9

,

1

))

// dates less than 1-Sep-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=

COUNTIFS

(

A1:A5

,

"*apple*"

)

// count cells that contain "apple"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.

Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.

To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

## How To Use The Excel Countif Function

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can’t use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Basic example

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

=

COUNTIF

(

B5:B12

,

"jim"

)

// count name = "jim"

=

COUNTIF

(

C5:C12

,

"ca"

)

// count state = "ca"

Notice COUNTIF is not case-sensitive, “CA” and “ca” are treated the same.

Double quotes (“”) in criteria

In general, text values need to be enclosed in double quotes (“”), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

=

COUNTIF

(

A1:A10

,

100

)

// count cells equal to 100

=

COUNTIF

(

A1:A10

,

"jim"

)

// count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=

COUNTIF

(

A1:A10

,

"<"

&

B1

)

// count cells less than B1

Not equal to

To construct “not equal to” criteria, use the “” operator surrounded by double quotes (“”). For example, the formula below will count cells not equal to “red” in the range A1:A10:

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=

COUNTIF

(

A1:A10

,

""

)

// blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this

=

COUNTIF

(

A1:A10

,

"<"

&

DATE

(

2020

,

4

,

1

))

// dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=

COUNTIF

(

A1:A5

,

"*apple*"

)

// cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=

COUNTIF

(

A1:A5

,

"???"

)

// cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

COUNTIF requires a range, you can't substitute an array.

COUNTIF returns incorrect results when used to match strings longer than 255 characters.

COUNTIF will return a #VALUE error when referencing another workbook that is closed.

## How To Use The Excel Sort Function

The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will “spill” onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically.

The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.

Basic Examples

To sort a range by the first column in ascending order:

=

SORT

(

range

)

// sort by column 1, ascending

=

SORT

(

range

,

1

,

1

)

// sort by column 1, ascending

To sort a range by the first column in descending order:

=

SORT

(

range

,

1

,

-

1

)

// sort by column 1, descending

To sort a range by the second column in descending order:

=

SORT

(

range

,

2

,

-

1

)

// sort by column 1, ascending

To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:

=

SORT

(

range

,

1

,

-

1

,

TRUE

)

=

SORT

(

range

,

1

,

-

1

,

1

)

Example – sort by score

In the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:

=

SORT

(

B5:C14

,

2

,

-

1

)

// sort by scores in descending order

The SORT function extracts all values, sorted in descending order by score, and results “spill” into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:

=

SORT

(

B5:C14

,

2

)

// sort by score in ascending order

=

SORT

(

B5:C14

,

2

,

1

)

// sort by score in ascending order

Sort by more than one level

Unlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.

SORT is a new function available in Excel 365 only.

Bạn đang đọc nội dung bài viết How To Use The Excel Subtotal Function trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!