仕組みと動作原理
SORTBY関数はExcel業務を効率化する代表的な関数です。Sorts a range based on values in a separate range or array. 複雑な集計や参照処理を簡素化し、データ分析の精度と生産性を飛躍的に高めます。
構文.
標準Excel表記法=SORTBY(array, by_array1, [sort_order1], ...)引数一覧.
この関数で指定できるすべての引数について詳しく解説します。
The range to sort and return.
指定例:A2:A5The criteria range to sort by.
指定例:B2:B51 for Ascending (default), -1 for Descending.
初期値: 1具体的な計算例
ステップ別の実務シナリオOrder Roster - Sort Names by Score
Extract only product names, sorted by their revenue column.
| Product | Revenue | Sorted List |
|---|---|---|
| Product A | 120 | — |
| Product B | 500 | — |
| Product C | 310 | — |
=SORTBY(A2:A5, B2:B5, -1)Evaluates SORTBY over the sample table cells, placing the verified output in cell C2.
数式ジェネレーター
ウィザードを開く引数を入力するだけで、シートにそのまま貼り付けて使える数式を作成できます。
=SORTBY()出力される値の特性
Returns "Product B" based on sample evaluation criteria and active cell references.
実務上のベストプラクティス
- Can sort by multiple priority levels: =SORTBY(names, department, 1, salary, -1).
よくあるミスと注意点
- The by_array must have the exact same number of rows as the target array or #VALUE! occurs.
各プラットフォーム対応表
実際のExcelおよびGoogleスプレッドシートの計算エンジン仕様に基づいた検証結果です。
In Excel 2019, sort by secondary key in Data > Sort or use multiple RANK calculations.
Introduced in Excel 365 and Excel 2021. Not available in Excel 2019.
Supported in Google Sheets (uses SORTN or standard SORT with external array keys).
よくある質問と回答
技術解説Can I use SORTBY across different worksheets in Excel?↓
Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =SORTBY(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.
What is the most common reason for errors in SORTBY?↓
The by_array must have the exact same number of rows as the target array or #VALUE! occurs.
Does SORTBY work identically in Google Sheets?↓
Yes, SORTBY is supported in both Microsoft Excel and Google Sheets with standard evaluation behavior.