仕組みと動作原理
SORT関数はExcel業務を効率化する代表的な関数です。Sorts the contents of a range or array dynamically. 複雑な集計や参照処理を簡素化し、データ分析の精度と生産性を飛躍的に高めます。
構文.
標準Excel表記法=SORT(array, [sort_index], [sort_order], [by_col])引数一覧.
この関数で指定できるすべての引数について詳しく解説します。
The range or array to sort.
指定例:A2:B5Column index number to sort by (1-based, default is 1).
初期値: 11 for Ascending (default), -1 for Descending.
初期値: 1具体的な計算例
ステップ別の実務シナリオLeaderboard - Top Performing Products
Sort product table by sales volume in descending order.
| Product | Sales | Sorted Product | Sorted Sales |
|---|---|---|---|
| Product A | 150 | — | — |
| Product B | 450 | — | — |
| Product C | 200 | — | — |
=SORT(A2:B5, 2, -1)Evaluates SORT over the sample table cells, placing the verified output in cell D2.
数式ジェネレーター
ウィザードを開く引数を入力するだけで、シートにそのまま貼り付けて使える数式を作成できます。
=SORT()出力される値の特性
Returns number 450 based on sample evaluation criteria and active cell references.
実務上のベストプラクティス
- Nest with UNIQUE: =SORT(UNIQUE(A2:A100)) generates a sorted alphabetical list of unique names.
よくあるミスと注意点
- If any cell in the output spill range contains existing data, Excel returns a #SPILL! error.
各プラットフォーム対応表
実際のExcelおよびGoogleスプレッドシートの計算エンジン仕様に基づいた検証結果です。
In Excel 2019, sort tables via Data > Sort or use helper columns with the RANK function.
Introduced in Excel 365 and Excel 2021. Not available in Excel 2019.
Fully supported in Google Sheets with identical arguments =SORT(range, sort_column, is_ascending).
よくある質問と回答
技術解説Can I use SORT 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: =SORT(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 SORT?↓
If any cell in the output spill range contains existing data, Excel returns a #SPILL! error.
Does SORT work identically in Google Sheets?↓
Yes, SORT is supported in both Microsoft Excel and Google Sheets with standard evaluation behavior.