Cập nhật thông tin chi tiết về How To Use The Excel Sort Function mới nhất trên website Trucbachconcert.com. 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.
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 Counta Function
Random list of names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…
Add row numbers and skip blanks
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))
The IF function first checks if cell C5 has…
Cell contains all of many things
The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…
Count cells that are blank
The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…
Last row in mixed data with no blanks
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…
Count unique values
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…
Dynamic named range with OFFSET
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of nonempty cells.
The first argument in OFFSET represents the first cell in the…
Running count group by n size
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and…
Count cells not equal to many things
First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:
But this doesn…
Project complete percentage
In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…
Count sold and remaining
The COUNTA function counts nonblank cells that contain numbers or text. The first COUNTA counts nonblank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11)
The second COUNTA function…
Generate random text strings
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6character codes. The randomness is handled by…
Sort by random
The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…
Score quiz answers with key
This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(–(C7:G7=…
Reverse a list or range
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is…
How To Use The Excel Vlookup Function
VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the first column of the table passed into VLOOKUP. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.
V is for vertical
The purpose of VLOOKUP is to get information from a table organized like this:
Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:
=
VLOOKUP
(
1004
,
B5:F9,
4
,
FALSE
)
// returns "Sue Martin"
For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.
VLOOKUP is based on column numbers
When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, provide the appropriate number as the “column index”. For example, the column index to retrieve the first name below is 2:
The last name and email can be retrieved with columns 3 and 4:
=
VLOOKUP
(
H3,
B4:E13,
2
,
FALSE
)
// first name
=
VLOOKUP
(
H3,
B4:E13,
3
,
FALSE
)
// last name
=
VLOOKUP
(
H3,
B4:E13,
4
,
FALSE
)
// email address
VLOOKUP only looks right
VLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values:
If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.
Exact and approximate matching
VLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is “range_lookup“. This is a confusing name, because it seems to have something to do with cell ranges like A1:A10. Actually, the word “range” in this case refers to “range of values” – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades.
It is important to understand that range_lookup defaults to TRUE, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set range_lookup to FALSE to force exact matching:
=
VLOOKUP
(
value,
table,
col_index)
// approximate match (default)
=
VLOOKUP
(
value,
table,
col_index,
TRUE
)
// approximate match
=
VLOOKUP
(
value,
table,
col_index,
FALSE
)
// exact match
Note: You can also supply zero (0) instead of FALSE for an exact match.
Exact match
In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:
The formula in H6 to find Year, based on an exact match of movie title, is:
=
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
)
// FALSE = exact match
Approximate match
In cases when you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:
=
VLOOKUP
(
C5,
$G$5:$H$10,
2
,
TRUE
)
// TRUE = approximate match
VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will “step back” and match the previous row.
Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.
First match
=
VLOOKUP
(
E5,
B5:C11,
2
,
FALSE
)
// returns 17
Wildcard match
The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table after typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify the exact match mode by providing FALSE or 0 for the last argument, range_lookup. The formula in H7 retrieves the first name, “Michael”, after typing “Aya” into cell H4:
=
VLOOKUP
(
$H$4&
"*"
,
$B$5:$E$104,
2
,
FALSE
)
Read a more detailed explanation here.
Twoway lookup
Inside the VLOOKUP function, the column index argument is normally hardcoded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic twoway lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:
=
VLOOKUP
(
H4,
B5:E13,
MATCH
(
H5,
B4:E4,
0
),
0
)
For more details, see this example.
Note: In general, INDEX and MATCH is a more flexible way to perform twoway lookups.
Multiple criteria
The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example below, Column B is a helper column that concatenates first and last names together with this formula:
=
C5&
D5// helper column
VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:
=
VLOOKUP
(
H4&
H5,
B5:E13,
4
,
0
)
For details, see this example.
Note: INDEX and MATCH and XLOOKUP are more robust ways to handle lookups based on multiple criteria.
VLOOKUP and #N/A errors
If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just means “not found”. For example, in the screen below, the lookup value ”Toy Story 2″ does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:
One way to “trap” the NA error is to use the IFNA function like this:
The formula in H6 is:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
"Not found"
)
The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string like this:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
""
)
// no message
The #N/A error is useful because it tells you something is wrong. In practice, there are many reasons why you might see this error, including:
The lookup value does not exist in the table
The lookup value is misspelled, or contains extra space
Match mode is exact, but should be approximate
The table range is not entered correctly
You are copying VLOOKUP, and the table reference is not locked
Read more: VLOOKUP without #N/A errors
More about VLOOKUP
Other notes
Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow nonexact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a nonexact match.
If range_lookup is TRUE (the default setting), a nonexact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
When range_lookup is omitted, the VLOOKUP function will allow a nonexact match, but it will use an exact match if one exists.
If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.
How To Use The Excel Timevalue Function
Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIMEVALUE function is meant to parse a time that appears as a text value into a valid Excel time. A native Excel time is more useful than text because it is a numeric value that can be formatted as time and directly manipulated in a formula.
The TIMEVALUE function takes just one argument, called time_text. If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (“”). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string.
The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format. TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM. Because the maximum value returned by TIMEVALUE is less than 1, hours will reset every 24 hours (like a clock).
Examples
The formulas below show the output from TIMEVALUE:
=
TIMEVALUE
(
"12:00"
)
// returns 0.5
=
TIMEVALUE
(
"12:00 PM"
)
// returns 0.5
=
TIMEVALUE
(
"18:00"
)
// returns 0.75
To display the output from TIMEVALUE as a formatted time, apply a time number format.
Alternative formula
Notice that the TIMEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid time. This is a limitation of the TIMEVALUE function. If you have a mix of valid and invalid dates, you can use the simple formula below as an alternative:
=
A1+
0
The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel is able parse the text into a proper time it will return a valid time as a decimal number. If the time is already a valid Excel time, adding zero will have no effect, and generate no error.
Notes
TIMEVALUE will return a #VALUE error if time_text does not contain time formatted as text.
Bạn đang xem bài viết How To Use The Excel Sort Function trên website Trucbachconcert.com. 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. Chúc bạn một ngày tốt lành!