Bạn đang xem bài viết How To Use The Excel Sort Function được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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.
How To Use The Excel Roundup Function
The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:
Digits Behavior
Round up to nearest .1, .01, .001, etc.
Round up to nearest 10, 100, 1000, etc.
=0 Round up to nearest 1
Example #1 – round to right
To round up values to the right of the decimal point, use a positive number for digits:
=
ROUNDUP
(
A1,
1
)
// Round up to 1 decimal place
=
ROUNDUP
(
A1,
2
)
// Round up to 2 decimal places
=
ROUNDUP
(
A1,
3
)
// Round up to 3 decimal places
=
ROUNDUP
(
A1,
4
)
// Round up to 4 decimal places
Example #2 – round to left
To round up values to the left of the decimal point, use zero or a negative number for digits:
=
ROUNDUP
(
A1,
0
)
// Round up to nearest whole number
=
ROUNDUP
(
A1,
-
1
)
// Round up to nearest 10
=
ROUNDUP
(
A1,
-
2
)
// Round up to nearest 100
=
ROUNDUP
(
A1,
-
3
)
// Round up to nearest 1000
=
ROUNDUP
(
A1,
-
4
)
// Round up to nearest 10000
Example #3 – nesting
Other operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:
=
ROUNDUP
(
A1/
B1,
0
)
// round up result to nearest whole number
Rounding functions in Excel
To round normally, use the ROUND function.
To round to the nearest multiple, use the MROUND function.
To round down to the nearest specified place, use the ROUNDDOWN function.
To round down to the nearest specified multiple, use the FLOOR function.
To round up to the nearest specified place, use the ROUNDUP function.
To round up to the nearest specified multiple, use the CEILING function.
To round down and return an integer only, use the INT function.
To truncate decimal places, use the TRUNC function.
How To Use The Excel Sumif Function
SUMIF 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 SUMIF requires a cell range for the range argument, you can’t use an array.
SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate values that appear in the range argument (i.e. extract the year from dates to use in criteria) see the SUMPRODUCT and/or FILTER functions.
Videos
Basic usage
In the worksheet shown, there are three SUMIF examples. In the first example (G6), SUMIF is configured to sum values greater than 100. In the second example (G7), SUMIF returns the sum of values where the color is “red”. In the last example (G8), SUMIF is configured to sum values where the state is “CA” (California).
=
SUMIF
(
B6:B10,
"Jim"
,
D6:D10)
// Rep = Jim
=
SUMIF
(
C6:C10,
"ca"
,
D6:D10)
// State = CA
Notice the equals sign (=) is not required when constructing “is equal to” criteria. Also notice SUMIF is not case-sensitive. You can sum values where the Rep is Jim using “jim” or “Jim”.
Criteria in another cell
Not equal to
To express "not equal to" criteria, use the "" operator surrounded by double quotes (""):
Again notice SUMIF is not case-sensitive.
Blank cells
SUMIF can calculate sums based on cells that are blank or not blank. In the example below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:
=
SUMIF
(
D5:D9,
""
,
C5:C9)
// blank
The best way to use SUMIF with dates is to refer to a valid date in another cell, or use the DATE function. The example below shows both methods:
=
SUMIF
(
B5:B9,
"<"
&
DATE
(
2019
,
3
,
1
),
C5:C9)
Wildcards
The SUMIF function supports wildcards, as seen in the example below:
=
SUMIF
(
B5:B9,
"mi*"
,
C5:C9)
// begins with "mi"
=
SUMIF
(
B5:B9,
"*ota"
,
C5:C9)
// ends with "ota"
=
SUMIF
(
B5:B9,
"????"
,
C5:C9)
// contains 4 characters
See below for more SUMIF formula examples.
Notes
SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.
When sum_range is omitted, the cells in range will be summed.
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 find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
SUMIFS requires a range, you can't substitute an array.
How To Sort In Excel
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
One Column
To sort on one column, execute the following steps.
Result:
Multiple Columns
To sort on multiple columns, execute the following steps.
The Sort dialog box appears.
2. Select Last Name from the ‘Sort by’ drop-down list.
4. Select Sales from the ‘Then by’ drop-down list.
Result. Records are sorted by Last Name first and Sales second.
Cập nhật thông tin chi tiết về How To Use The Excel Sort Function trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!