Excelソート機能 - 式を使用してデータソートデータを自動化します

チュートリアルでは、ソート関数を使用してデータ配列を動的にソートする方法を示しています。 Excelでアルファベット順にソートする式を学習し、昇順または降順で数字を配置し、複数の列でソートするなどを学びます。

ソート機能は長い間存在しています。しかし、Excel 365にダイナミックアレイが導入されたことで、式で驚くほど簡単な方法があるように見えました。この方法の美しさは、ソースデータが変更されたときに結果が自動的に更新されることです。

• Excelソート機能
• 基本的なソート式
• フォーミュラを使用してExcelでデータを並べ替える方法 - 例
  • 列ごとに並べ替えます
  • 異なる順序で複数の列でソートします
  • Excelでソートしてフィルタリングします
  • 並べ替えて、nトップまたはボトムの値を取得します
• ソート配列を取得して自動的に展開する方法
• Excelソート機能が機能していません

Excelソート機能

Excelのソート関数は、昇順または下降順に、列または行で配列または範囲の内容を並べ替えます。

ソートは、動的配列関数のグループに属します。その結果、ソースアレイの形状に応じて、隣接する細胞に垂直または水平方向に自動的にこぼれる動的配列ができます。

ソート関数の構文は次のとおりです。

sort（array、[sort_index]、[sote_order]、[by_col]）

どこ：

配列（必須） - ソートする値の配列またはセルの範囲です。これらは、テキスト、数字、日付、時間などを含む任意の値にすることができます。

SORT_INDEX （オプション） - 並べ替える列または行を示す整数。省略すると、デフォルトのインデックス1が使用されます。

SORT_ORDER （オプション） - ソート順序を定義します。

• 1または省略（デフォルト） - 昇順、つまり最小から最大へ
• -1-下降順序、つまり最大から最小まで

by_col（オプション） - ソートの方向を示す論理的値：

• falseまたは省略（デフォルト） - 行ごとにソートします。ほとんどの場合、このオプションを使用します。
• true-列ごとにソートします。この例のような列にデータが水平に整理されている場合は、このオプションを使用してください。

Excelソート機能 - ヒントとメモ

ソートは新しい動的配列関数であるため、次のことを認識する必要があるいくつかの特異性があります。

• 現在、ソート関数はMicrosoft 365およびExcel 2021でのみ利用可能です。Excel2019、Excel 2016は動的配列式をサポートしていないため、これらのバージョンではソート機能は使用できません。
• ソート式で返された配列が最終結果である場合（つまり、別の関数に渡されません）、Excelは適切にサイズの範囲を動的に作成し、ソートされた値に浸透します。したがって、フォーミュラを入力するセルの右側には常に十分な空のセルがあることを確認してください。そうしないと、#spillエラーが発生します。
• 結果は、ソースデータが変更されると動的に更新されます。ただし、式に提供される配列は、参照された配列の外側に追加された新しいエントリを含めるように自動的に拡張されません。このようなアイテムを含めるには、式の配列参照を更新するか、この例に示すようにソース範囲をテーブルに変換するか、動的な名前の範囲を作成する必要があります。

基本的なExcelソートフォーミュラ

この例は、昇順と下降順序でExcelでデータをソートするための基本的な式を示しています。

以下のスクリーンショットに示すように、データがアルファベット順に配置されると仮定します。データを壊したり混合したりせずに、列Bの番号を並べ替えたいと考えています。

昇順で並べ替えるフォーミュラ

列Bの値を最小から最大に並べ替えるには、次の式を次に示します。

=SORT(A2:B8, 2, 1)

どこ：

• A2：B8はソースアレイです
• 2は、並べ替える列番号です
• 1は昇順のソートオーダーです

データは行で編成されているため、最後の引数は、デフォルトでfalse -sort by行に省略できます。

空のセル（この場合はD2）に式を入力するだけで、 Enterを押すと、結果はD2：E8に自動的に流出します。

降順で並べ替えるフォーミュラ

下降データをソートするには、つまり最大から最小まで、 sort_order引数を-1に設定します。

=SORT(A2:B8, 2, -1)

宛先範囲の左上セルに式を入力すると、この結果が得られます。

同様に、テキスト値をAからZまで、またはZからAからAまでのアルファベット順に並べ替えることができます。

式を使用してExcelでデータを並べ替える方法

以下の例は、Excelおよびいくつかの非自明な関数の種類関数のいくつかの典型的な使用を示しています。

列ごとに並べ替えます

Excelでデータをソートすると、ほとんどの場合、行の順序を変更します。ただし、データがレーベルを含む行とレコードを含む列を含む行で水平に整理されている場合、上から下までではなく、左から右に並べ替える必要があります。

Excelで列ごとに並べ替えるには、 by_col引数をtrueに設定します。この場合、 sort_indexは列ではなく行を表します。

たとえば、以下のデータを数量で並べ替えるには。最高から最低まで、この式を使用してください。

=SORT(B1:H2, 2, 1, TRUE)

どこ：

• B1：H2はソートデータです
• 2は2列目に番号を並べ替えているため、2は並べ替えインデックスです
• -1降順のソート順を示します
• trueは、行ではなく列を並べ替えることを意味します

異なる順序で複数の列でソート（マルチレベルソート）

複雑なデータモデルを使用する場合、多くの場合、マルチレベルのソートが必要になる場合があります。それは式で行うことができますか？うん、簡単！あなたがしていることは、 SORT_INDEXおよびSORT_ORDER引数の配列定数を提供することです。

たとえば、最初に以下のデータを領域（列A）からZからZ、次に数量で並べ替えること。 （列c）最小から最大まで、次の引数を設定します。

• 配列は、A2：C13のデータです。
• SORT_INDEXは、最初に領域（1 ^ST列）、次に数量で並べ替えるため、配列定数{1,3}です。 （3 ^rd列）。
• SORT_ORDERは、1つ目の列は昇順で並べ替え、3 ^RD列を降順で並べ替えるため、配列定数{1、-1}です。
• by_colは、デフォルトである行を並べ替えるため省略されています。

引数をまとめると、この式が得られます。

=SORT(A2:C13, {1,3}, {1,-1})

そして、それは完全に機能します！最初の列のテキスト値はアルファベット順にソートされ、3番目の列の数字は最大から最小までです。

Excelでソートしてフィルタリングします

いくつかの基準でデータをフィルタリングし、出力を順番に配置しようとしている場合、ソートとフィルター機能を一緒に使用します。

sort（filter（array、 criteria_range = criteria ）、[SORT_INDEX]、[SORT_ORDER]、[by_col]））

フィルター関数は、定義する基準に基づいて値の配列を取得し、その配列を最初の引数に渡します。

この式の最大の利点は、Ctrlシフトの入力を押すか、コピーするセルの数を推測することなく、結果を動的な流出範囲として出力することです。いつものように、あなたは最も上部のセルに式を入力し、Enterキーを押します。

例として、A2：B9のソースデータから30以上（> = 30）以上の量のアイテムを抽出し、結果を昇順でアレンジします。

このために、以下の画像に示すように、最初にセルE2で条件を設定します。そして、このようにしてExcelソートフォーミュラを構築します。

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

フィルター関数によって生成された配列とは別に、 sort_index引数（列2）のみを指定します。残りの2つの引数は省略されています。これは、デフォルトが必要に応じて正確に機能するためです（並べ替え、行ごと）。

n最大値または最小値を取得し、結果を並べ替えます

情報の場合は巨大なバルクを分析するとき、特定の数の最高値を抽出する必要があることがよくあります。抽出するだけでなく、目的の順序で配置することもあります。理想的には、結果に含める列を選択します。トリッキーに聞こえますか？新しい動的配列関数ではありません！

これが一般的な式です：

index（sort（…）、sequence（ n ）、{ column1_to_return 、 column2_to_return 、…}）

ここで、 nは返品する値の数です。

以下のデータセットから、列Cの数字に基づいてトップ3のリストを取得すると仮定します。

それを完了するには、最初にArray A2：C13を3^列目で下降順に並べ替えます。

SORT(A2:C13, 3, -1)

そして、インデックス関数の最初の（配列）引数に上記の式をネストして、アレイを最高から最小までソートします。

2番目の（ row_num ）引数の場合、返す行の数を示しますが、シーケンス関数を使用して必要なシーケンシャル番号を生成します。 3つのトップ値が必要なため、シーケンス（3）を使用します。これは、式に垂直配列定数{1; 2; 3}を直接供給することと同じです。

返す列の数を定義する3番目の（ col_num ）引数の場合、列数を水平配列定数の形で供給します。列bとcを返したいので、配列{2,3}を使用します。

最終的に、次の式が取得されます。

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

そして、それは私たちが望む結果を正確に生成します：

3つのボトム値を返すには、元のデータを最小から最大まで並べ替えるだけです。このために、 sort_order引数を-1から1に変更します。

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

特定の位置でソートされた値を返します

別の角度から見ると、特定のソート位置を返したい場合はどうなりますか？と言ってください、1番目、2番目のみ、またはソートされたリストからの3番目のレコードのみですか？それを実行するには、上記のインデックスソート式の簡素化されたバージョンを使用してください。

index（sort（…）、 n 、{ column1_to_return 、 column2_to_return 、…}）

ここで、 nは関心のある位置です。

たとえば、上から特定の位置を取得するには（つまり、下降するデータから）この式を使用します。

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

底から特定の位置を取得するには（つまり、昇順でソートされたデータから）、これを使用してください。

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

A2：C13はソースデータ、F1は上部からの位置、i1は下部からの位置、{2,3}は返される列です。

Excelテーブルを使用してソート配列を取得して自動的に展開します

すでにご存知のように、元のデータに変更を加えると、ソートされた配列が自動的に更新されます。これは、ソートを含むすべての動的配列関数の標準動作です。ただし、参照された配列の外側に新しいエントリを追加すると、それらは式に自動的に含まれていません。式にこのような変更に応答したい場合は、ソース範囲を完全に機能するExcelテーブルに変換し、式で構造化された参照を使用します。

実際にどのように機能するかを確認するには、次の例を検討してください。

以下のExcelソートフォーミュラを使用して、アルファベット順に範囲A2：B8に値をアレンジするとします。

=SORT(A2:B8, 1, 1)

次に、列9に新しいエントリを入力します…そして、新しく追加されたエントリが流出範囲から除外されていることを確認することに失望します。

次に、ソース範囲をテーブルに変換します。このために、列ヘッダー（A1：B8）を含む範囲を選択し、 Ctrl tを押します。式を構築するときは、マウスを使用してソース範囲を選択すると、テーブル名が式に自動的に挿入されます（これは構造化された参照と呼ばれます）：

=SORT(Table1, 1, 1)

最後の行のすぐ下に新しいエントリを入力すると、テーブルは自動的に展開され、新しいデータがソートフォーミュラの流出範囲に含まれます。

Excelソート機能が機能していません

ソートフォーミュラがエラーをもたらす場合、次の理由が原因である可能性が高いです。

#Nameエラー：古いExcelバージョン

ソートは新しい関数であり、Excel 365およびExcel 2021でのみ機能します。この関数がサポートされていない古いバージョンでは#Name？エラーが発生します。

#spillエラー：何かが流出範囲をブロックします

流出範囲内の1つ以上のセルが完全に空白またはマージされていない場合、#spill！エラーが表示されます。それを修正するには、閉塞を取り除いてください。詳細については、Excel #Spillをご覧ください！エラー - それが意味するものと修正方法。

#valueエラー：無効な引数

#Valueに遭遇するたびに！エラー、 SORT_INDEXとSORT_ORDER引数を確認してください。 sort_indexは列の数を超えてはならず、 sort_orderは1（昇順）または-1（下降）である必要があります。

#REFエラー：ソースワークブックは閉じられています

ダイナミックアレイはワークブック間の参照に対するサポートが限られているため、ソート関数では両方のファイルを開く必要があります。ソースワークブックが閉じられている場合、式が#refを投げます！エラー。修正するには、参照されたファイルを開くだけです。

これが、式を使用してExcelでデータを並べ替える方法です。お読みいただきありがとうございます。来週のブログでお会いしましょう！

ダウンロード用のワークブックを練習します

式（.xlsxファイル）を使用したExcelの並べ替え

以上がExcelソート機能 - 式を使用してデータソートデータを自動化しますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。