☰

Excel で共通点を持つ日付を抽出する

tag-icon • tag-icon microsoft-excel
Excel で共通点を持つ日付を抽出する

雨が降ったとき、どのような状態だったかを記録したデータの山を想像してみてください。

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

特定の州で雨が降った日付のリストを表示する数式を作成するにはどうすればよいでしょうか? たとえば、ネバダ州で雨が降った日付を知りたい場合、02Jan, 04Janセルで答えを得ることはできますか?

答え1

以下の方法で問題を解決します。

ここに画像の説明を入力してください

使い方：

  • より速く結果を得るには、セル A28 に州のドロップダウンを作成します。
  • [データ] タブを押し、[データの検証] を選択します。
  • リスト ソースとして、D28:D31 を選択します。

ここに画像の説明を入力してください

  • 次に、雨または雨なしの場合、データの検証を使用してセル B28 にドロップダウンを作成し、リスト ソースに Yes と No を追加します。

ここに画像の説明を入力してください

  • 最後に、セル A35 に配列 (CSE) 数式を入力します。

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

注意

  • 式を次のように完成させるCtrl+Shift+Enter＆記入してください。

  • 必要に応じて数式内のセル参照を調整します。

答え2

正直に言うと、データからピボット テーブルを作成するのがおそらく最善のアプローチでしょう。しかし、数式によるアプローチが必要な場合は、次のようになります。

たとえば、データがヘッダーを含むセル A1:F5 にあるとします。検索値がセル G8 にある場合は、次のようになります。

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

次のような出力が得られます。

1月2日、1月4日

「ネバダ」を検索する場合。

等々。

基本的に、最初のものを使用してXLOOKUP()、その状態に当てはまる YES および NO データの行を検索します。IF()ラップは、NO の場合は 0 を、YES の場合はヘッダー行の日付を返します。これは、IF()前の結果をそれ自体で割ったもの (ERROR と 1 が生成される) を使用して、結果が 0 より大きいかどうか (1 は 0 より大きい) を確認する によってラップされます。これらの ERROR は先に進み続けますが、2 番目のものIF()は 1 を再び日付に置き換えます。次に、IFERROR()それをラップして、ERROR ではなく日付であった項目のみに結果を減らします。この時点では、これらは Excel の整数日付値 (たとえば 43563) であり、ヘッダー行に表示される形式で必要なため、TEXT()そのようにフォーマットします。そうすることで「数値性」が失われるという事実は、リストされている場合はいずれにせよそうなるため、問題ではありません (まあ、1 つの日付のみが返される場合を除いて...「技術的」に言うと...)。最後に、TEXTJOIN()読みやすくするために「カンマとスペース」の区切り文字を付けます (そのため、今後の作業で必要になった場合に、1 つまたはすべてを抽出するために使用できる区切り文字が付きます)。

おそらく他のアプローチもあるでしょう。XLOOKUP()長年使われている「ダブル INDEX/MATCH」テクニックのバージョンがその 1 つかもしれません。ただし、複数の応答を取得するとXLOOKUP()複数の応答 (予想どおり 5 つ) が返されますが、それらは 01-Jan の 3 つと 02-Jan の 2 つであり、たとえば、私が求めていた 02-Jan と 04-Jan ではありませんでした。ただし、ここは午前 3 時なので、何か見逃したのかもしれません。があまりにもFILTER()素早く行動したため、ネバダの 2 つの日付を取得するための位置情報が失われました (すぐに 2 つの結果だけが表示されましたが、今度は 1-2 で、2-4 ではないため、ヘッダーから日付を取得する適切な方法がありません)。他のアイデアがあるかもしれません。ただし、午前 3 時です...

ただし、それぞれが上記のような要素の山であるため、見た目が単純なものに削減することはおそらく不可能なので、おそらく問題にはなりません。

関連情報