いまさら聞けないExcelの使い方講座

【Excel】日々更新される巨大なリストでデータの上位20件を自動抽出するテクニック

データが追加されても「上位20件」を自動的に取り出す

上位20件だけを取り出すには?

 Excelで大量のデータを管理していると、上位や下位の数件だけを取り出したいことがあります。例えば、売上金額の大きい商品や、作業に時間がかかった案件などです。元の表を並べ替えて必要な行をコピーする方法もありますが、データが追加されるたびに同じ操作を繰り返すのは面倒ですよね。

 また、上位○件を確認するだけなら、フィルターの[トップテン]を使う方法もあります。件数は自由に指定できますが、フィルターは元の表の表示を切り替える機能です。別の場所に抽出結果を表示し、データの追加や修正に応じて自動更新したい場合は、関数を使う方法が適しています。

例えば、問い合わせ管理表で対応時間が長い上位20件を取り出す場合、データが追記されるたびに並べ替えとコピー&ペーストを繰り返すのは非効率です

 このような時に役立つのがTAKE関数です。指定した範囲の先頭や末尾から、必要な行数・列数だけを取り出せます。SORTBY関数と組み合わせれば、元の表を変更せずに並べ替えた結果から上位20件を取り出せます。なお、TAKE関数はMicrosoft 365のExcelとExcel 2024で利用できます。

SORTBY関数で対応時間の長い順に並べ替える

 サンプルの問い合わせ管理表には、受付日時や問い合わせ内容、対応時間などが記録されており、新しい問い合わせがあるたびにデータが追加される前提です。

 対応に時間がかかった問い合わせを確認するため、まずはSORTBY関数で対応時間の長い順に並べ替えます。SORTBY関数は、指定した列の値を基準に、表全体を並べ替える関数です。

=SORTBY(配列, 基準配列, [並べ替え順序])

 引数[配列]には並べ替える表、[基準配列]には並べ替えの基準となる列を指定します。[並べ替え順序]には「1」(昇順)または「-1」(降順)を指定します。省略した場合は昇順を指定したものとして扱われます。

 管理表は「問い合わせ一覧」という名前のテーブルに変換してあります。引数[配列]にはテーブル名、[基準配列]には「対応時間」列を指定します。対応時間の長い順に並べ替えるため、[並べ替え順序]は「-1」とします。

=SORTBY(問い合わせ一覧,問い合わせ一覧[対応時間],-1)
上記の数式を入力する(①)と、対応時間の長い順の結果が表示されます(②)。

 なお、セルの表示形式は変更していないため、ここでは[標準]の書式のまま表示されています。

TAKE関数で上位20件を取り出す

 並べ替えた結果から上位20件だけを取り出すには、TAKE関数を使います。TAKE関数は、指定した配列の先頭や末尾から、必要な数の行や列を取り出す関数です。基本的な構文は次のようになります。

=TAKE(配列, 行, [列])

 例えば、「=TAKE(A2:F100,20)」という数式であれば、セルA2~F100を対象に、先頭から20行を取り出します。引数[列]を省略した場合は、指定範囲のすべての列が対象になります。

 SORTBY関数で対応時間の長い順に並べ替えた結果から先頭20行を取り出せば、対応時間が長かった上位20件に絞り込めます。先ほどの数式にTAKE関数を組み合わせます。

=TAKE(SORTBY(問い合わせ一覧,問い合わせ一覧[対応時間],-1),20)

 SORTBY関数で並べ替えた結果を、TAKE関数の第1引数に指定しています。第2引数の「20」は、先頭から20行を取り出すという意味です。

TAKE関数を組み合わせると(③)、対応時間の長い上位20件だけが表示されます(④)

 元データに問い合わせが追加されたり、対応時間が修正されたりした場合も、結果は自動的に更新されます。

例えば、対応時間が「250分」と「200分」のデータを追加します(⑤)
TAKE関数とSORTBY関数を使った数式の結果は自動的に更新されます(⑥)

 なお、SORT関数、FILTER関数、LARGE関数を組み合わせても、対応時間の長い上位20件を取り出すことはできますが、数式は長くなります。

=SORT(FILTER(問い合わせ一覧,問い合わせ一覧[対応時間]>=LARGE(問い合わせ一覧[対応時間],20)),6,-1)

 SORT関数の「6」は、並べ替えの基準となる「対応時間」が6列目にあることを示します。この方法では、20位と同じ対応時間のデータが複数ある場合、20件を超えて抽出されます。一方、TAKE関数は並べ替えた結果から指定した20件だけを取り出せます。件数を固定したい場合はTAKE関数が適しています。

SORT / FILTER / LARGE関数を組み合わせて、対応時間の長い上位20件を取り出した結果です(⑦)。同順位のデータは複数取り出されます

対応時間の短い上位20件を取り出すには?

 対応時間の短い順に20件を取り出したいなら、SORTBY関数の引数[並べ替え順序]を「1」に変更するだけです。省略しても構いません。

=TAKE(SORTBY(問い合わせ一覧,問い合わせ一覧[対応時間],1),20)
=TAKE(SORTBY(問い合わせ一覧,問い合わせ一覧[対応時間]),20)
SORTBY関数の引数[並べ替え順序]を「1」と指定する(⑧)と、対応時間の短い上位20件だけが表示されます(⑨)

 なお、TAKE関数の引数[行]には、マイナスの値を指定することもできます。正数を指定すると先頭から○行を取り出すのに対して、負数を指定した場合は末尾から取り出します。

 例えば、サンプルのように日付が古い順に並んでいる表から最新20件のデータを取り出したいなら、以下のような数式になります。

=TAKE(問い合わせ一覧,-20)
TAKE関数の引数[行]に「-20」と指定すると(⑩)、対象の「問い合わせ一覧」の末尾から20行を取り出せます(⑪)。元の表が「受付日」の古い順で並んでいるので、結果として最新20件のデータが取り出されます

 このように、TAKE関数は並べ替えをしなくても、表の先頭や末尾から指定した件数を取り出せます。データが追記されていく管理表なら、最後に登録された20件だけを別の場所に表示して、最近の問い合わせ内容を確認する用途にも使えます。

 なお、対応状況が「未対応」の問い合わせや、特定の期間に受け付けた問い合わせを取り出したい場合は、FILTER関数が適しています。取り出したい件数で指定するのか、条件に合うデータを探すのかによって、関数を使い分けましょう。