Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2013)
複数の条件に一致するデータを上から順番に表示させたい
投稿日時: 18/07/08 15:36:08
投稿者: monjiro

お世話になります。
 
複数の条件を指定して、該当するデータを上から順番に最大で5件表示させたいのですが
2番目からのデータの表示方法が分かりません。
 
 A列  B列 C列  D列
1 日付 お店 商品 個数
2 6/1 A  みかん 4
3 6/2 B  みかん 3
4 6/3 A  りんご 8
5 6/3 A  みかん 6
6 6/4 A  みかん 2
 
というデータがあったとして、下記の条件を設定して
 
   H列  I列  J列  K列
1 開始日 終了日 お店 商品
2 6/1   6/4  A   みかん
 
結果
  H列  I列
3 日付  個数
4 6/1  4
5 6/3  6
6 6/4  2
7       ※該当データが無いので何も表示しない
8       ※該当データが無いので何も表示しない
 
という表示をしたいです。
現時点では該当するデータを上から順番に5件表示出来ればよく、該当するデータが5件未満の場合は
該当する分だけ表示し、残りは何も表示しないようにしたい。
2番目以降に該当するデータの求め方がよく分かりません。
 
宜しくお願いします。
 
 

回答
投稿日時: 18/07/08 19:22:03
投稿者: んなっと

《方法1》なるべく使わないほうがいい
 
H4
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$500)/(A$2:A$500>=H$2)/(A$2:A$500<=I$2)/(B$2:B$500=J$2)/(C$2:C$500=K$2),ROW(H1))),"")
I4
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($2:$500)/(A$2:A$500>=H$2)/(A$2:A$500<=I$2)/(B$2:B$500=J$2)/(C$2:C$500=K$2),ROW(H1))),"")
  
  
《方法2》作業列を使う
 
  A   B    C   D  E F G    H    I   J    K
1 日付 お店  商品 個数 No.    開始日 終了日 お店  商品
2  6/1   A みかん   4  1      6/1   6/4   A みかん
3  6/2   B みかん   3  1     日付  個数       
4  6/3   A りんご   8  1      6/1    4       
5  6/3   A みかん   6  2      6/3    6       
6  6/4   A みかん   2  3      6/4    2       
 
E2
=N(E1)+AND(A2>=H$2,A2<=I$2,B2=J$2,C2=K$2)
下方向・↓
H4
=IFERROR(INDEX(A:A,MATCH(ROW(H1),E:E,0)),"")
下方向・↓
I4
=IFERROR(INDEX(D:D,MATCH(ROW(H1),E:E,0)),"")
下方向・↓

回答
投稿日時: 18/07/08 21:03:40
投稿者: Mike

1.範囲 A1:D1000 を選択 ⇒ Alt+MC ⇒ “上端行”以外のチェック外し ⇒ [OK]
2.「m/d」に書式設定したセル H4 に次式を入力して、此れを右隣にオートフィル
 ̄ ̄ =IFERROR(INDEX(INDIRECT(H$4),SMALL(IF((INDIRECT($H$4)>=$H$2)*(INDIRECT($H$4)<=$I$2)*(INDIRECT($J$1)=$J$2)*(INDIRECT($K$1)=$K$2),ROW(A$2:A$10)),ROW(A1))-1),"")
 ̄ ̄【お断り】上式は必ず配列数式として入力のこと
3.セル I4 の書式を「標準」に変更
4.範囲 H4:I4 を下方にオートフィル

回答
投稿日時: 18/07/09 00:08:18
投稿者: Mike

[フィルタオプションの設定]に依る別解です。
 
   A  B   C   D  E F G H  I  J   K
  1 日付 お店 商品  個数    始日 終日 お店 商品
  2 6/1 A  みかん  4    6/1 6/4 A  みかん
  3 6/2 B  みかん  3    日付 個数
  4 6/3 A  りんご  8    6/1  4      1
  5 6/3 A  みかん  6    6/3  6
  6 6/4 A  みかん  2    6/4  2
  7
……
1000
 
最初は範囲 H3:I3 より下は空白を呈しています。
1.セル K4 に式 =(A2>=H$2)*(A2<=I$2)*(B2=J$2)*(C2=K$2) を入力。此処でその上のセル(K3)を空白のままに放置しておくのがミソです。
2.範囲 A1:D1000 を選択 ⇒ Alt+AQ ⇒ [抽出先]として“選択範囲”に目玉入れ ⇒ [リスト範囲]ボックスに $A$1:$D$1000 が表示されていることを確認 ⇒ [検索条件範囲]ボックス内にマウスカーソルを置いて、範囲 $K$3:$K$4 を撫で撫で ⇒ [抽出範囲]ボックス内にマウスカーソルを置いて、範囲 $H$3:$I$3 を撫で撫で
3.最後に[OK]をパシーッ
 
貴方は「おお〜ッ!」と感動する筈です。(^_^)
だって、IFERROR、INDEX、INDIRECT、IF、ROW等々の英語文字の関数を一切使わないのですから。

投稿日時: 18/07/09 11:50:54
投稿者: monjiro

んなっとさん回答有り難うございます。
  
AGGREGATE関数始めてしりました。
こんな関数あったんだ〜と感動しましたw
 
因みに、AGGREGATE関数を使用した方法が、おすすめでない理由は何故ですか?
この関数自体の使用をお勧めしないとかでしょうか
宜しければ、ご教授下さい。
 

投稿日時: 18/07/09 12:01:32
投稿者: monjiro

Mikeさん回答有り難うございます。
 
[フィルタオプションの設定]に依る別解は、まさしく”おぉぉ〜すげ〜〜”でしたw
 
ただ、私の環境では、条件を変更しても自動で結果が更新されず、
毎回、 Alt+AQ からフィルタオプションの
設定を呼び出さないとダメなのですが、これは何か設定が足りないのでしょうか?
 

回答
投稿日時: 18/07/09 16:42:32
投稿者: Suzu

引用:
条件を変更しても自動で結果が更新されず、

これは仕様です。
 
オートフィルターは、条件を変えれば即座に抽出内容を変えてくれますが
フィルターオプションは、手動で更新しなければなりません。
 
あとは、別にボタンを用意しVBAで設定するかでしょうか。

回答
投稿日時: 18/07/09 21:38:18
投稿者: んなっと

長所
・式を貼り付けるだけで、楽に結果が得られる
 
短所
・データが多いときは重くなりがち
・式が長いので修正する時に苦労する

回答
投稿日時: 18/07/10 08:10:53
投稿者: んなっと

重さ解消の工夫で
 
 H4:I9を選択してから
→数式バーに
=IFERROR(INDEX($A:$D,AGGREGATE(15,6,ROW($2:$500)/(A$2:A$500>=H$2)/(A$2:A$500<=I$2)/(B$2:B$500=J$2)/(C$2:C$500=K$2),ROW(1:5)),{1,4}),"")
Ctrl+Shift+Enter同時押し
 
もあります。結構速くなります。
式を修正するときはどれか一つのセルを選択して修正→再度Ctrl+Shift+Enter同時押し。
しかし、ますます式が複雑になってしまうのが残念ですね。

投稿日時: 18/07/10 17:44:34
投稿者: monjiro

んなっとさん、Mikeさん、Suzuさん有り難うございます。
 
どちらも今まで使った事の無い機能で勉強になりました。
 
今回は、運用の観点から、んなっとさんのなるべく使わない方が良いを使わせて頂きました。
 
有り難うございました。