To sort a set of data by two levels, the generic formula is: =SORTBY(array,by_array1,sort_order1,by_array2,sort_order2) To sort by grades in ascending order use: =SORTBY(B5:B14,C5:C14,1) // sort by grades ascending The output in E5:E14 is the list of names sorted by scores in descending order. In the example shown above, the formula in E5 is: =SORTBY(B5:B14,C5:C14,-1) // sort by grades descending Note the dimensions or the range argument must be compatible with those of the by_array argument. By default, sort order is ascending: =SORTBY() In the example below, we sort the first four letters in the alphabet in a custom order using the numeric values in the second array. The SORTBY function can be used with arrays and array constants as well. To switch the sort order to descending, use: =SORTBY(range1,range2,-1)// sort range1 by range2, descending =SORTBY(range1,range2,1)// sort range1 by range2, ascending To sort range1 in ascending order using values in range2: =SORTBY(range1,range2// sort range1 by range2, ascending
When by_array is a horizontal range, SORTBY sorts horizontally by columns. When by_array is a vertical range, SORTBY sorts vertically by rows. Instead, the range or array provided for the by_array argument will determine the sort orientation automatically. Unlike the SORT function, the SORTBY function does not have an argument that explicitly controls sorting by rows versus sorting by columns. To sort by more than one level, provide additional sort_by and sort_order arguments in pairs. By default, SORTBY will sort in ascending order. Use 1 for ascending order and -1 for descending order. Finally, the optional sort_order argument determines sort direction. For example, if array contains ten rows, by_array should also contain ten rows. However, by_array must have dimensions compatible with array. Note that by_array values do not need to be part of the source data, and do not need to appear in the output. These values can come from an existing range, or from an array created by a formula. The second argument, by_array, contains the values to be used for sorting.
The first argument, array, is the range or array to be sorted.
The SORTBY function takes three primary arguments: array, by_array, and sort_order.
#SORTY SORTY UPDATE#
If values in the source data change, the output from SORTBY will update automatically. The result from SORTBY is a sorted copy of the data which will " spill" onto the worksheet into a range. The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array with a formula.
0 kommentar(er)
