エクセル 文字と関連セルを順に抜き出し

質問

エクセル2010を使っています。

画像の様なシートが有り、B139に文字が入力されたら、E列から同じ文字を探しG、H列に上から順に表示させたいと思います。

出来るだけ簡単な関数で教えて頂けませんか？

ちなみに、J列にも文字入力欄があり、列方向に、合計で８箇所同じ作業があります。

よろしくお願いします。

回答No.2

bunjii · ベストアンサー

>出来るだけ簡単な関数で教えて頂けませんか？
次の数式では如何でしょうか？
G139=IFERROR(OFFSET(E$1,SUMPRODUCT(LARGE(($E$139:$E$1000=$B$139)*ROW(G$139:G$1000),COUNTIF($E$139:$E$1000,$B$139)-ROWS(G$139:G139)+1))-1,0),"")
G139セルをH139へコピーし、2つのセルを選択した状態で下へ必要数コピーしてください。
元データの最大行番号を1000にしてありますので、実情に合わせて修正してください。

貼付画像はExcel 2013で検証した結果ですが、Excel 2007以降のバージョンで同じ結果が得られます。

お礼

回答ありがとう御座います。

一発で目的を果たせました、もう思い残すことはありませ・・・・・あります！

大変感謝します、ありがとうございました。

回答No.4

tom04

No.1・3です。

No.3の方の数式で
11000　という数値になっているところがありますが、入力ミスです。
他の列の配列と同じように　1000　に合わせてください。

※　そのままの数式でもちゃんと表示されると思いますが、
配列数式の場合、極端に多くの範囲を指定すると計算速度が落ちてしまいます。
3000行程度であれば問題ないとは思います。m(_ _)m

お礼

再度の回答いただきありがとうございます！

3000行以内ですのでこれで利用できます！

回答No.3

tom04

No.1です。

＞137行目までにたくさん同じ数字があり・・・
というコトですので、数式を変更してみてください。
前回の画像通りの配置とします。
G139セルは
=IFERROR(INDEX(E$139:E$11000,SMALL(IF($E$139:$E$11000=$B$139,ROW($E$139:$E$1000)-138),ROW(A1))),"")

O139セルは
=IFERROR(INDEX(M$139:M$1000,SMALL(IF($M$139:$M$1000=$J$139,ROW($M$139:$M$1000)-138),ROW(A1))),"")

どちらも前回同様配列数式です。m(_ _)m

回答No.1

tom04

こんばんは！

＞出来るだけ簡単な関数で教えて頂けませんか？
というコトですので、一例です。

↓の画像でG139セルに
=IFERROR(INDEX(E$1:E$1000,SMALL(IF($E$1:$E$1000=$B$139,ROW($A$1:$A$1000)),ROW(A1))),"")
としてCtrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　G139セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式となります。
これを隣りのH139セルまでフィルハンドルでコピー　→　そのまま下へコピーすると
画像のような感じになります。

＞ちなみに、J列にも文字入力欄があり、列方向に、合計で８箇所同じ作業があります。
この数式を他の列にコピー＆ペーストしてもダメですので、
↓の画像ではO139セルに
=IFERROR(INDEX(M$1:M$1000,SMALL(IF($M$1:$M$1000=$J$139,ROW($A$1:$A$1000)),ROW(A1))),"")
（これも配列数式です。）
としてフィル＆コピーしています。

列がずれても数式そのものは規則性がありますので、
G列とO列の数式の列参照が違うところを注意しながら他の列にも数式を入れてみてください。

※　数式内の　「ROW($A$1:$A$1000」の部分は必ず1行目から指定してください。
（厳密にいえば行数合わせが必要だと思いますが判り難くなりますので、どちらも1000行目までの範囲としています）
※　各列137行目までのデータがあるかないか判らないのですが、
検索対象列の137行目までに同じデータがあると、とんでもない表示になってしまいます。m(_ _)m

補足

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

早速やって見たのですが、たしかに137行目までにたくさん同じ数字がありとんでもない結果になってしまいました。