エクセル 関数式の応用

質問

こんにちは　エクセル2010を使っています。

画像の様なデータがあって、次のような式を教えてもらいました。

=IFERROR(INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=C$1,ROW($A$1:$A$7)),ROW(A1))),"")

画像では　A列　B列の　データが7行目までなのですが、これを3万行まで広げようと次のような式に変更してみましたが上手く行きません。（空白が返される）
=IFERROR(INDEX($B$1:$B$30000,SMALL(IF($A$1:$A$30000=C$1,ROW($A$1:$A$30000)),ROW(A1))),"")

詳しい方、教えていただけませんか？
よろしくお願いします。

回答No.2

MackyNo1 · ベストアンサー

>面白い？事に　15000行なら抜き出してくれて、20000行はダメなんです。

実際にセル番地を間違えていないなら、「INDEX($B$1:$B$30000,SMALL(IF($A$1:$A$30000=C$1,ROW($A$1:$A$30000)),ROW(A1)))」の部分がエラーを返しているパターンになります。

すなわち、A15000:A20000の行にエラー値（#N/Aや#VALUEエラーなど）が含まれているものと思われます。
A15000セルまでなら正常な値を返すなら、A15001セル以下を選択してDeleteしてみてください。

＃ちなみにこのようなデータ範囲の大きい配列数式を多数のセルに入力すると、シートの再計算に時間がかかり、シートの動きが重くなるなど、実用上の問題がでますので、オートフィルタやフィルタオプションの設定を利用されたほうが良いと思います（この操作をマクロの記録をしてコードを一部編集すれば、簡単にマクロ化することもできます）。

お礼

回答ありがとうございます。

まさにそれでした、エラーが含まれていました。

エラー行を削除したら正常に動いています、助かりました、ありがとうございます。

回答No.1

bunjii

提示の数式はSMALL関数の範囲（配列数）をIF関数で前処理していますので数式の確定時にCtrlとShiftを押しながらEnterを打鍵してください。
確定結果を数式バーで確認すると数式全体を｛=数式｝のように大かっこで括られていますので違いが分かります。

お礼

回答ありがとうございます。

配列数式ですよね、ですから確かにCtrlとShiftを押しながらEnterを打鍵しています。
（複数回やってみましたが、結果は変わりません）

そして、7行に戻すとちゃんと抜き出してくれるんです。

A列　B列のデータは今のところ、21000行あって今後増えていく予定ですが、面白い？事に　15000行なら抜き出してくれて、20000行はダメなんです。