Excel で基準に基づいてリストを生成する方法 (4 つの方法)

Excel では、条件に基づいてリストを生成する必要がある場合があります。今日は、基準に基づいてリストを生成する方法を紹介します。このセッションでは、Excel 365 を使用していますが、このバージョンを使用することをお勧めしますが、自由に使用してください。

まず最初に、例のベースとなるデータセットについて知りましょう。

ここには、さまざまな場所にいる数人の人物とその車両のデータセットがあります。このデータを使用して、基準に基づいてリストを作成します。

これは、単純にするためのダミー データを含む基本的なテーブルであることに注意してください。実際のシナリオでは、はるかに大規模で複雑なデータセットに遭遇する可能性があります。

練習用ワークブック

次のリンクから練習用ワークブックをダウンロードしてください。

基準に基づいてリストを生成

たとえば、地域に基づいて人々のリストを作成します。

これは小さなデータセットであるため、4 つの地域があることがわかっています。地域の名前を保存し、地域に基づいてリストを見つけます。

1. INDEX-SMALL の組み合わせを使用してリストを生成する

ここではリストが必要なので、テーブルから複数の値を取得する式にする必要があります。そのタスクでは、 INDEX の組み合わせを使用できます そして小さい 関数。

これらの機能については、次の記事をご覧ください:INDEX, SMALL.

これら 2 つに加えて、いくつかのヘルパー関数 IF が必要になります。 、行 と IFERROR .詳細については、IF、ROW、IFERROR の記事を確認してください。

式を調べてみましょう

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

ここでは、すべての機能に目的があります。 インデックス 関数は配列 B2:B12 から値を返します (名前列) と大きな SMALL 部分は、フェッチされる行番号を提供します。

もし、 SMALL 内で、 基準が一致するかどうかをチェックし、 ROW 関数は列のセルを反復します。

次に、外側の ROW SMALL の k 番目の値を示します 関数。これらの関数を組み合わせると、行番号と INDEX が返されます 結果を返します。

IFERROR 数式から発生する可能性のあるエラーに対処します。

下にドラッグすると、指定された地域のすべての人が取得されます。

同様に、他の領域の式を書きます (式は同じで、セルをシフトするだけです)。

代替の INDEX-SMALL の組み合わせ

別の方法で式を書くことができます。数式に使用される関数は、以前と同じになります。表示のみが異なります。

式を見てみましょう

 =IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

ここでも、CTRL + SHIFT + ENTER を押す必要があります 式を実行するため。

これら 2 つの式にはわずかな違いがあります。区別できますか?

はい、前の式では、 SMALL の最後で 1 を引きました。 部分ですが、ここでは IF 内で 1 を減算しています

1 を減算する目的は、適切な行番号にチャネライズすることです。先ほど、ついにそれを行いました。ここでは以前にそれを行い、さらなる操作に進みます。

リストを完成させるために、その他の基準の公式を書いてください。

続きを読む:Excel でセル内にリストを作成する方法 (3 つの簡単な方法)

2. AGGREGATE 関数を使用してリストを生成する

Excel には、 AGGREGATE という関数があります。 さまざまなタスクを実行するために使用できます。ここで関数を使用して、基準に基づいてリストを生成できます。

集計 関数は、AVERAGE、COUNT、MAX などの集計計算を返します。

AGGREGATE の構文 関数は次のとおりです:

AGGREGATE(function_number,behavior_options, range)

機能番号: この数値は、どの計算を行うべきかを指定します。

動作オプション: これを数値で設定します。この数値は、関数がどのように動作するかを示します。

範囲: 集計したい範囲。

集計 function はいくつかのタスクを実行するため、多数の関数が定義済みです。使用頻度の高い機能番号をいくつか記載しています

機能	機能番号
平均	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
SUM	9
LARGE	14
小	15

この機能の詳細については、Microsoft サポートをご覧ください。

式を見てみましょう。

=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

AGGREGATE とともに 関数、INDEX を使用しました . 索引 式の後半で見つかった一致に基づいて値を返す配列を保持します。

15 を使用したことがわかります function_number として 集約で .上の表から、15 を確認できます。 SMALL の呼び出し 機能操作。共感できますか?

はい、INDEX-SMALL を実行しました AGGREGATE のような式 関数。

6 エラー値を無視することを示す動作オプション .

残りの値の式を書きます。

類似の読み物

• Excel で To Do リストを作成する方法 (3 つの簡単な方法)
• Excel でメーリング リストを作成する (2 つの方法)
• Excel で番号付きリストを作成する方法 (8 つの方法)

3. INDEX-MATCH-COUNTIF を使用して一意のリストを生成

基準に基づいて独自のリストを作成できます。そのために、INDEX の組み合わせを使用できます 、マッチ 、および COUNTIF .

COUNTIF 1 つの条件を満たす範囲内のセルをカウントします。そしてマッチ 範囲内のルックアップ値の位置を特定します。これらの機能の詳細については、次の記事をご覧ください: マッチ、COUNTIF。

式を調べてみましょう

=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") 

この式では:B2:B12 抽出する一意の値を含む列の範囲です。C2:C12 G2 に基づく基準を含む列です 基準を示します。

MATCH 内 関数、lookup_array として 0 を指定しました。 lookup_range の場合 IF を使用しました COUNTIF を含む部分 .したがって、この部分は 0 が見つかる限り値を返します。ここの値は INDEX の行番号として機能します .

下にドラッグすると、すべての一意の値が表示されます。

CTRL+SHIFT + ENTER を使用することを忘れないでください 数式を実行します。

これは、一意のリストを生成するアプローチについての名誉ある言及でした。この記事に従って、基準に基づく固有のリストの生成について理解してください。 .

4. FILTER 関数を使用して基準に基づいてリストを生成する

Excel 365 を使用している場合は、FILTER という単一の組み込み関数でタスクを実行できます。 .

フィルター 関数は、指定された基準に基づいてデータの範囲をフィルタリングし、一致するレコードを抽出します。この機能については、次の記事をご覧ください:FILTER .

ここで、式は次のようになります。

=FILTER($B$2:$B$12,$C$2:$C$12=G$2)

B2:B12 フィルタリングされる配列です。次に、リストを生成する内容に基づいて条件を指定しました。

ここでは、式を下にドラッグする必要はありません。一度にすべての値が提供され、リストが満たされます。

続きを読む: Excel でアルファベット順のリストを作成する方法 (3 つの方法)

結論

それが今日のすべてです。基準に基づいてリストを生成する方法をいくつか挙げました。これが役立つことを願っています。わかりにくいところがあれば遠慮なくコメントください。ここで見逃した他の方法をお知らせください。

参考文献

• Excel で箇条書きリストを作成する方法 (9 つの方法)
• Excel でコンマ区切りのリストを作成する (5 つの方法)
• Excel で価格表を作成する方法 (ステップ バイ ステップのガイドライン)