[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数のデータ抽出 CountifとIndexを使いましたが、うまく作れません。』（ＫＥ）
Ａ Ｂ Ｃ Ｄ Ｅ
1 検索
2 5月1日 なし 5月1日
3 5月6日 みかん
4 5月1日 りんご 0
5 5月10日 ぶどう #NUM!
5月1日に該当するＣ列データを、Ｅ４から下にリスト化したく、
Ｅ２を検索ボックスとし、
Ｅ４に
=IF(COUNTIF(B:B,$E$3)<ROW(B1),"",INDEX(C:C,SMALL(IF(B:B=$E$3,ROW(B:B)),ROW(B1))))
と入れると、0,#NUM!と表示されてしまいます。
何がいけないのでしょうか。教えてください。
＜ 使用 Excel：Excel2016、使用 OS：unknown ＞
B列、C列には何行ぐらいのデータがあるんですか？ まさか100万行もないですよね？
うまくいかない原因ではありませんが、配列数式で「列全体」を指定するのはやめた方がいいです。 あと、COUNTIF 関数を使わなくても、エラー処理は IFERROR でできます。
検索値が E2セル だとして
E4 =IFERROR(INDEX(C:C,SMALL(IF($B$2:$B$100=$E$2,ROW($B$2:$B$100)),ROW(B1))),"")
~~~
Ctrl＋Shift＋Enter で確定し、下にコピー
※波線部は列全体を指定すること
※データ範囲は実際の表に合わせて下さい
または E4 =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($B$2:$B$100)/($B$2:$B$100=$E$2),ROW(B1))),"")
普通に Enter だけで確定し、下にコピー
参考まで （笑） 2019/08/19(月) 17:23
3 5月1日 なし 5月10日
4 5月6日 みかん みかん
5 5月1日 りんご
6 5月10日 ぶどう
7 5月10日 もも
（笑）さんのとおり、入力しました。
B列3行目から月日、C列3行目から果物があります。
E3を検索ボックスとして、5月10日を入れたところ、E4に「みかん」が出て、E５が空欄となってしまいました。
E4=IFERROR(INDEX(C:C,SMALL(IF($B$2:$B$100=$E$3,ROW($B$2:$B$100)),ROW(B1))),"")
E5=IFERROR(INDEX(C:C,SMALL(IF($B$2:$B$100=$E$3,ROW($B$2:$B$100)),ROW(B2))),"")
としています。何が間違っているのでしょうか？
データ数は今のところ100件程度ですが、増えることを見越して列全体を指定しておけば安心だと思ったのですが、配列数式ではやめた方がよいのですね。
IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($B$2:$B$100)/($B$2:$B$100=$E$2),ROW(B1))),"")
ですが、Aggregateの中のROW($B$2:$B$100)/($B$2:$B$100=$E$2),ROW(B1)))はどういう意味でしょうか？
たびたびすみませんが、教えてください。よろしくお願いします。
（ＫＥ） 2019/08/20(火) 10:26
＞Ctrl＋Shift＋Enter で確定
↑ をしていないのでは？
数式を入力後、Ctrlキーと Shiftキーを押しながら Enter で確定です（配列数式）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数式バーを見てください。
正しく確定されていれば ↓ のように数式が { } で囲まれます。
{=IFERROR(……（略）……)),"")}
既に入力済みの数式が正しく確定されていない場合 数式バーにカーソルを置いて Ctrl＋Shift＋Enter してみてください。
確定後は普通にオートフィルで下にコピー
＞E4=IFERROR(INDEX(C:C,SMALL(IF($B$2:$B$100=$E$3,ROW($B$2:$B$100)),ROW(B1))),"")
~~~~~ ~~~~~
3行目から始まっているのなら、波線部（2か所）は $B$3 にしましょう。
数式を1文字直すだけでも、修正後 Ctrl＋Shift＋Enter で確定です。
＞データ数は今のところ100件程度ですが
最終的にどのくらいになりそうなんですか？ それを見越して範囲を指定してください。 範囲を限定しても、あまりに範囲が広いようだと重くなると思いますよ。
それと、AGGREGATE を使った式（Enter だけで確定）も試してくれたんですかね？ できたのか、できなかったのか、それが分からないと意味を説明する気にはなれません。
とりあえず以上です （笑） 2019/08/20(火) 12:30
大変失礼しました。式を修正後にctrlとshiftを押していなかったようです。無事できました。
Aggregateも試し、できたのですが、なぜこのような式でできるのか理解したかったので、、説明不足ですみません。
データは今100件で、1年で50件くらいずつ増えていく見込みです。あまり重くなっても困るので、もしかしたら数年ごとにシートを変えるかファイルを変えた方がいいのかな、と考えています。
（KE） 2019/08/20(火) 13:27
＞データは今100件で、1年で50件くらいずつ増えていく見込みです。
1年で50行ぐらいしか増えないってこと？ 100年でも5000行ですよね。
それぐらいならどうってことないでしょ。 同じ日付が最大で何個になるのか知りませんけど。
ちなみに、元表を「テーブル」にしておけば 現在、7行目までしかデータがないとして
=IFERROR(INDEX(C:C,SMALL(IF($B$3:$B$7=$E$3,ROW($B$3:$B$7)),ROW(B1))),"")
~~~~~~~~~ ~~~~~~~~~~
↑ ↑
現時点での範囲にしておく。
元表にデータが追加されると、数式の範囲も自動的に拡張されます。
詳しくは「 Excel テーブル 」でネット検索してみてください。
ところで AGGREGATE の説明もした方がいいんですかね？ 必要ならしますけど･･･
とりあえず以上です （笑） 2019/08/20(火) 14:57
作業列を使って説明します。
A B C D E F G H I 1 2 3 5/1 なし 5/1 TRUE 3 3 4 5/6 みかん FALSE #DIV/0! 5 5 5/1 りんご TRUE 5 #NUM! 6 5/10 ぶどう FALSE #DIV/0! #NUM! 7 5/10 もも FALSE #DIV/0! #NUM!
G3 =B3=$E$3 H3 =ROW()/G3 I3 =AGGREGATE(15,6,$H$3:$H$7,ROW(A1))
下コピー
1）G列は、B列の日付が E3セルの日付と同じかどうか（同じなら TRUE、違うなら FALSE）
2）H列は、行番号÷G列の値（TRUE、FALSE） 四則演算すると、TRUE は「1」、FALSEは「0」で計算されるので H3セルは 3÷1 で「3」 H4セルは 4÷0 でエラー（#DIV/0!） H5セルは 5÷1 で「5」
つまり日付が条件と同じなら、その行番号に、違うとエラーになる。
3）I列、AGGREGATE 関数の第1引数を「15」にすると、数値を昇順に表示する 第2引数を「6」 にすると、エラー値（#DIV/0!）を無視する
つまり条件に一致する行番号だけを取り出すことができる。
I列の式を作業列を使わないでやると =AGGREGATE(15,6,ROW($B$3:$B$7)/($B$3:$B$7=$E$3),ROW(B1))
これで分かりますか？ （笑） 2019/08/20(火) 15:16
数千件程度のデータなら、問題ないんですね。複数ファイルにまたがってデータを抽出しなくてはならなかったので、大変そうだと思っていたのですが、1つのファイルで済みそうなので良かったです。
AGGREGATEの式で何をやっているのか理解できました。とても丁寧にご説明いただきありがとうございます。
自分ではまったく思いつかないです。。
（KE） 2019/08/21(水) 13:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.