1629424670 Excelで3つの条件に一致する行のみを抽出したいです


Excelの関数を使って、条件に一致する行のみを抽出したいと考えています。
添付の画像が、理想の形です。

注文数 = 1 かつ 提供店舗 = 1 かつ 時間帯 = 0

という条件に一致する行のみを抽出したいのです。

自分で色々と調べてみたのですが、うまくいかず…

回答の条件
  • 1人20回まで
  • 登録:
  • 終了:2021/08/22 09:14:53
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。
id:moon-fondu

条件に合うデータをエクセル関数で全て順に抽出できる数式の作り方 - 病院SEにゃんとのパソコントラブル解決&エクセル関数・VBA活用術

https://nyanto.jimdofree.com/%EF%BD%B4%EF%BD%B8%EF%BD%BE%EF%BE%9...


を参考に、Sheet1のI列に、以下の関数を入れてみました。


=IFERROR(INDEX($A$3:$F$14,MATCH(LARGE(($C$3:$C$14 = 1 and $D$7:$D$14 = 1 and $E$7:$E$14 = 0)*1/ROW($A$3:$A14)),ROWS($A$3:$A$3),1/ROW($A$3:$A$14),0),COLUMNS($A$3:$A$3)),"")


しかし「この数式には問題があります」というエラーになってしまいます。


この方法でなくてもよいのですが、3つの条件に一致する行を抽出する方法がありましたら、お教えいただけないでしょうか。


質問に使用しているファイルはこちらです。https://xfs.jp/AeUGj8


よろしくお願い致します<m(__)m>

ベストアンサー

id:a-kuma3 No.4

回答回数4972ベストアンサー獲得回数2154

ポイント1100pt

提示された条件の範囲での答えはついているようなので、汎用的なやつを行きます。

多少の癖はありますけれど、数式は使いません。

ポチポチ触るだけです。

それなりに新しい Excel が必要ですけれど、その辺りは仕事で使ってるなら、問題はないはず。


文字で説明すると、ちょっと長いですけれど、実際に触りながら確認すると、それほど難しくありません。

ただ、前述の「癖」があるので、事前準備として Sheet1 の表に以下の条件のデータを作ってください。

  • 注文数が 1
  • 提供店舗が 1
  • 時間帯が 0以外

  1. まず、Sheet1 の対象の表のどこかのセルを選択します。
  2. リボンの「データ」で、左にある「データの取得と変換」で「テーブルまたは範囲から」をクリック。
  3. 「テーブルの作成」という小さいダイアログが出てきます。
  4. 表の範囲が選択されてるはずですが、もし期待する範囲を外れていれば選択し直して、「OK」をクリック。
  5. すると、「Power Query エディター」という大きめのダイアログが開きます。
  6. メインの領域が、フィルターを指定した感じの Excel の見た目になっているので、「注文数」、「提供店舗」、「時間帯」のフィルターを順番に設定していきます。
  7. まず、「注文数」は、"1" 以外のチェックを外します。
  8. 次に、「提供店舗」で、"1" 以外のチェックを外します。
  9. 事前準備をやっていれば、「時間帯」にも 0以外のデータがあるので、"0" 以外のチェックを外します。
  10. このダイアログのリボンの一番左の「閉じて読み込む」をクリック。
  11. こうすると、新しいシートが追加されて絞り込まれた状態で表が表示されます。

Excel のフィルターでポチポチやってることを、まとめてやってくれます。

なので、もっと複雑な条件でもフィルターでできることならできます。

例えば、こんなのもできます。

  • 注文数が 3以上
  • 提供店舗が 1 or 2
  • 売り上げが 5以上

フィルターで行けるので、文字列での絞り込みも、ある程度は可能。


Sheet1 の表が更新されたときは、Sheet2 に移って、リボンの「データ」の「クエリと接続」の「すべて更新」をクリック。


条件を変えたければ、「データ」の「クエリと接続」の「クエリと接続」をクリックすると、右側にクエリの一覧(先の手順のままであれば「テーブル1」というのがあるはず)を右クリックして「編集」を選択。

フィルターの条件を変えてください。


Sheet1 の表は、Excel の「テーブル」という機能を使ってます。

選択したエリアの右下のセルにに、ちょっと太めの "┛" が表示されてるはず。

そこまでがクエリの対象範囲です。

Sheet1 のデータを更新した場合は、そのマークが期待する範囲に入ってるかどうかの確認をしてください。


長くなりましたけど、クエリを使うときの長所と短所。


■メリット

  • 訳の分からないエラーに悩むことが無い(期待通りの絞り込みができてない、ということはありうる
  • 複数の絞り込み条件を持つ場合に楽ちん
  • 数式のコピーが要らない(複数の条件が欲しくなったときに顕著

■デメリット

  • 古い Excel では使えない
  • 対象のデータの行数がやたら多いと遅いかも(数式も同じだとは思う
id:moon-fondu

a-kuma3さんありがとうございます、すごいです!

Excelにこんな機能があったんですね!

power queryエディター、いいです。

普通にフィルターをかけるよりたくさんの機能があるようですし。

普通のフィルター機能だと、他の列にも影響が及んで同じシートにソート結果とかを載せれなかったですが、これなら対応できますね。

うまくソートできました。

ありがとうございました。

2021/08/22 09:13:55

その他の回答3件)

id:in899040115z No.1

回答回数1ベストアンサー獲得回数0

ポイント100pt

moon-fonduさん こんちにわ!


かなーり雑にサンプルを作成してみました。

Excel関数だけで一応対応しております。

これ

注文履歴(moon-fonduさん提示のデータ)と抽出結果の表示シートを分けました。

注文履歴シートでやってること

 抽出条件に一致する列で、何番目かを別列表示

抽出結果表示シートでやってること

 抽出結果シート側に一致する列の1列目から順に表示


ざっくり説明で済みません。

ご要望が実現できているかご確認いただければと存じます。

どうぞよろしくお願いいたします。

他1件のコメントを見る
id:in899040115z

moon-fonduさん ご要望に沿えていないようで申し訳ありません。

抽出結果シートに設けた条件1~3は可変入力可能な形にしてあります。

注文数 = 1、提供店舗 = 1、時間帯 = 0 という条件を

注文数 = 3、提供店舗 = 1、時間帯 = 0 と変更していただくと

結果が変わると思います。条件の指定が間違っていたら申し訳ありません。

2021/08/23 09:02:05
id:moon-fondu

in899040115zさんありがとうございます、そういうことだったんですね!

最初、シートの使い方がわからなかったのですが、抽出結果シートの1~3に希望の条件の値を入れると、その行を抽出してくれました。

ありがとうございます<m(__)m>

2021/08/24 09:40:34
id:rsc96074 No.2

回答回数4502ベストアンサー獲得回数437

ポイント1100pt

 こちらは参考になるでしょうか。括弧の位置の修正と「ROWS($A$3:$A3)」と「COLUMNS($A$3:A$3)」のとこの修正と「and」を掛け算にしてみました。(^_^;

=IFERROR(INDEX($A$3:$F$14,MATCH(LARGE((($C$3:$C$14 = 1) * ($D$3:$D$14 = 1) * ($E$3:$E$14 = 0))*1/ROW($A$3:$A$14),ROWS($A$3:$A3)),1/ROW($A$3:$A$14),0),COLUMNS($A$3:A$3)),"")

他2件のコメントを見る
id:rsc96074

 ポイント、沢山ありがとうございました。参考までに、VBAでもやってみました。(^_^;

https://rsc.hatenablog.com/entry/2021/08/22/135358

2021/08/22 13:57:18
id:moon-fondu

VBAありがとうございます。

実行してみました。

希望の行がうまく抽出できました、ありがとうございます(^^;)

2021/08/24 09:41:12
id:takashi_m17 No.3

回答回数120ベストアンサー獲得回数20

ポイント50pt

G列に 「=C3&D3&E3」 として

そのG列のフィルタで「110」のものをフィルタ掛けるのはダメなんですかね。

id:moon-fondu

たかさん、そうですよね。

その方法なら関数も単純化できるかもです。

2021/08/22 09:13:23
id:a-kuma3 No.4

回答回数4972ベストアンサー獲得回数2154ここでベストアンサー

ポイント1100pt

提示された条件の範囲での答えはついているようなので、汎用的なやつを行きます。

多少の癖はありますけれど、数式は使いません。

ポチポチ触るだけです。

それなりに新しい Excel が必要ですけれど、その辺りは仕事で使ってるなら、問題はないはず。


文字で説明すると、ちょっと長いですけれど、実際に触りながら確認すると、それほど難しくありません。

ただ、前述の「癖」があるので、事前準備として Sheet1 の表に以下の条件のデータを作ってください。

  • 注文数が 1
  • 提供店舗が 1
  • 時間帯が 0以外

  1. まず、Sheet1 の対象の表のどこかのセルを選択します。
  2. リボンの「データ」で、左にある「データの取得と変換」で「テーブルまたは範囲から」をクリック。
  3. 「テーブルの作成」という小さいダイアログが出てきます。
  4. 表の範囲が選択されてるはずですが、もし期待する範囲を外れていれば選択し直して、「OK」をクリック。
  5. すると、「Power Query エディター」という大きめのダイアログが開きます。
  6. メインの領域が、フィルターを指定した感じの Excel の見た目になっているので、「注文数」、「提供店舗」、「時間帯」のフィルターを順番に設定していきます。
  7. まず、「注文数」は、"1" 以外のチェックを外します。
  8. 次に、「提供店舗」で、"1" 以外のチェックを外します。
  9. 事前準備をやっていれば、「時間帯」にも 0以外のデータがあるので、"0" 以外のチェックを外します。
  10. このダイアログのリボンの一番左の「閉じて読み込む」をクリック。
  11. こうすると、新しいシートが追加されて絞り込まれた状態で表が表示されます。

Excel のフィルターでポチポチやってることを、まとめてやってくれます。

なので、もっと複雑な条件でもフィルターでできることならできます。

例えば、こんなのもできます。

  • 注文数が 3以上
  • 提供店舗が 1 or 2
  • 売り上げが 5以上

フィルターで行けるので、文字列での絞り込みも、ある程度は可能。


Sheet1 の表が更新されたときは、Sheet2 に移って、リボンの「データ」の「クエリと接続」の「すべて更新」をクリック。


条件を変えたければ、「データ」の「クエリと接続」の「クエリと接続」をクリックすると、右側にクエリの一覧(先の手順のままであれば「テーブル1」というのがあるはず)を右クリックして「編集」を選択。

フィルターの条件を変えてください。


Sheet1 の表は、Excel の「テーブル」という機能を使ってます。

選択したエリアの右下のセルにに、ちょっと太めの "┛" が表示されてるはず。

そこまでがクエリの対象範囲です。

Sheet1 のデータを更新した場合は、そのマークが期待する範囲に入ってるかどうかの確認をしてください。


長くなりましたけど、クエリを使うときの長所と短所。


■メリット

  • 訳の分からないエラーに悩むことが無い(期待通りの絞り込みができてない、ということはありうる
  • 複数の絞り込み条件を持つ場合に楽ちん
  • 数式のコピーが要らない(複数の条件が欲しくなったときに顕著

■デメリット

  • 古い Excel では使えない
  • 対象のデータの行数がやたら多いと遅いかも(数式も同じだとは思う
id:moon-fondu

a-kuma3さんありがとうございます、すごいです!

Excelにこんな機能があったんですね!

power queryエディター、いいです。

普通にフィルターをかけるよりたくさんの機能があるようですし。

普通のフィルター機能だと、他の列にも影響が及んで同じシートにソート結果とかを載せれなかったですが、これなら対応できますね。

うまくソートできました。

ありがとうございました。

2021/08/22 09:13:55

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません