エクセル 検索と抜き出し

質問

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

画像の様なデータがあり、機器Aの点数を上から順に抜き出したいと思います。

VLOOKUPの様に簡単な方法が理想なのですが、どのような関数がありますか？

詳しい方よろしくお願いいたします。

回答No.7

bunjii

>ただ、BT列の文字列と検索の対象の文字が違うようで、正常な数値を返してくれません><
？、何を言っているのか判読できません。
BT列には機器名を正しく入力されていますよね？
CB1と同じ文字列の機器名とBT列の文字列が一致する行番号を若い順に拾い出す処理なのでBT列の文字列に誤りがあれば拾えないでしょう。
例えばBT9の値が"機器A　"や"機器　A"のようになっていると対応するBV9の96は拾い出せません。
但し、半角英文字の大文字と小文字は同じ扱いになりますので"機器A"と"機器a"は同じと見做されます。

回答No.6

tom04

No.3です。

補足に書かれている数式でもちゃんと表示されると思いますが、
IFERROR関数を使用しているために、エラーと判断され空白になっているものと思われます。
ちゃんと配列数式になっているでしょうか？
今一度確認してみてください。

尚、CA1セルが空白の場合は「0」が表示されてしまうと思いますので、
IFERROR関数を使わずに同じような数式にしてみました。

表示したいセルに
=IF(COUNTIF(BT:BT,CA$1)<ROW(A1),"",INDEX(BV$1:BV$1000,SMALL(IF(BT$1:BT$1000=CA$1,ROW(BT$1:BT$1000)),ROW(A1))))
としてフィルハンドルで下へコピーしてみてください。
今回も配列数式です。
（数式の最後の「A1」部分はA列でなくても構いません「BT1」のように1行目であればどの列でもOKです）

上記数式をドラッグ＆コピー　→　表示したいセルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定してみてください。

※　補足の数式を拝見するとデータ範囲が10000行になっていますが、
実際にその程度のデータ量になるのでしょうか？
その場合、配列数式はPCに極端に負担をかけ計算速度が遅くなります。
3000行程度であれば問題ないと思いますが、10000行程度のデータ量がある場合は
作業用の列を設ける方法か、VBAをオススメします。m（_ _）m

回答No.5

bunjii

>実際は　検索したい値はCA１にあり、検索したい範囲は、BT列、取り出したい数値はBV列にありますので以下の様に改変してCtrl+SHIFT+ENTERで確定してみたのですが、空白が返されてしまいます。
修正後の数式に誤りがあるようです。

=IFERROR(INDEX($BT$1:$BU$1000,SMALL(($BT$1:$BT$1000=CA$1)*ROW(BT$1:BT$1000)+$BT$1:$BT$1000<>CA$1)*10000,ROW()),2),"")
INDEX関数の第1引数（配列）の範囲にBV列を含めないと抽出対象がありません。
また、第3引数（列番号）が違います。
更に、SMALL関数の括弧内の数式で ( が1個欠落しています。

前回の回答で提示しましたINDEX関数の第1引数（$A$1:$B$1000）は隣接していたのでA列とB列を対象にしましたが抽出目的のB列のみでも問題ありませんでした。
但し、B列のみを対象にしたときは第3引数を1に変更することになります。

以上の要件を考慮して数式を以下のように変更してください。
=IFERROR(INDEX($BV$1:$BV$1000,SMALL(($BT$1:$BT$1000=CA$1)*ROW(BT$1:BT$1000)+($BT$1:$BT$1000<>CA$1)*10000,ROW()),1),"")

補足

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

試してみた所、ダミーデータでは正常に動いています。

ただ、BT列の文字列と検索の対象の文字が違うようで、正常な数値を返してくれません><

見た目は同じなんですが、、、

お心当たりないでしょうか？><

回答No.4

bunjii

>VLOOKUPの様に簡単な方法が理想なのですが、どのような関数がありますか？
VLOOKUP関数は検索範囲に複数の検索値が存在する場合は使えません。
配列と検索値の配列演算を行った結果を利用して行番号に置き換えてSMALL関数またはLARGE関数で順番に抽出すると良いでしょう。

行番号の若い順に抽出するときは配列演算を (配列範囲=検索値)*ROW(配列範囲)+(配列範囲<>検索値)*最大値 のようにします。
配列演算の結果をSMALL関数で行番号の若い順に選択してINDEX関数で目的の値を取り出します。

貼付画像はExcel 2013で検証した結果ですがExcel 2010でも同じ結果を得られます。

E1=IFERROR(INDEX($A$1:$B$1000,SMALL(($A$1:$A$1000=D$1)*ROW(A$1:A$1000)+($A$1:$A$1000<>D$1)*10000,ROW()),2),"")
数式の入力後、Ctrl+Shift+Enterの打鍵で確定してください。
尚、処理対象は1行目から1000行目までとなっていますが範囲は実情に合わせて変更してください。
E1セルを下へ必要数コピーします。
F、H、J、L、N列に検索値を入力すればG、I、K、M、O列はE列をコピーすることで目的の処理ができます。

補足

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

やってみました、実際は　検索したい値はCA１にあり、検索したい範囲は、BT列、取り出したい数値はBV列にありますので以下の様に改変してCtrl+SHIFT+ENTERで確定してみたのですが、空白が返されてしまいます。

入力した数式
=IFERROR(INDEX($BT$1:$BU$1000,SMALL(($BT$1:$BT$1000=CA$1)*ROW(BT$1:BT$1000)+$BT$1:$BT$1000<>CA$1)*10000,ROW()),2),"")

もしかしたら、CA列が他セルを参照しているためでしょうか？

もしそうであれば対応策を教えて頂けないでしょうか？

ちなみにCAセルの参照元は頻繁に更新されますので、現実的な方法がベターなのですが。

よろしくお願いいたします。

回答No.3

tom04

こんにちは！
画像通りの配置だとして、

E1セルに
=IFERROR(INDEX(B$1:B$1000,SMALL(IF(A$1:A$21000=D$1,ROW(A$1:A$1000)),ROW(A1))),"")

G1セルに
=IFERROR(INDEX(B$1:B$1000,SMALL(IF(A$1:A$1000=F$1,ROW(A$1:A$1000)),ROW(A1))),"")

どちらも配列数式ですので、Ctrl＋Shift＋Enterで確定！
これをフィルハンドルで下へコピーしてみてください。m(_ _)m

補足

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

やってみました、実際は　検索したい値はCA１にあり、検索したい範囲は、BT列、取り出したい数値はBV列にありますので以下の様に改変してCtrl+SHIFT+ENTERで確定してみたのですが、空白が返されてしまいます。

入力した数式
=IFERROR(INDEX(BV$1:BV$10000,SMALL(IF(BT$1:BT$21000=CA$1,ROW(BT$1:BT$10000)),ROW(BT1))),"")

もしかしたら、CA列が他セルを参照しているためでしょうか？

もしそうであれば対応策を教えて頂けないでしょうか？

ちなみにCAセルの参照元は頻繁に更新されますので、現実的な方法がベターなのですが。

よろしくお願いいたします。

回答No.2

Cupper-2

作業列の作成とＭＡＴＣＨ関数とＩＮＤＥＸ関数の組み合わせで何とかなりそうですが、面倒ですよ。

作業列にＣＯＵＮＴＩＦ関数で対象のセルがいくつあるのかを数える
以下、Ｉ，Ｊ列を作業列とした場合

　Ｉ１＝ＣＯＵＮＴＩＦ（Ａ＄１：Ａ１，Ｄ＄１）
　Ｊ１＝ＣＯＵＮＴＩＦ（Ａ＄１：Ａ１，Ｆ＄１）
を必要行数 下へコピーする

で、
　Ｅ１＝ＩＮＤＥＸ（Ｂ：Ｂ，ＭＡＴＣＨ（ＲＯＷ（），Ｉ：Ｉ，０））
　Ｇ１＝ＩＮＤＥＸ（Ｂ：Ｂ，ＭＡＴＣＨ（ＲＯＷ（），Ｊ：Ｊ，０））
を必要行数 下へコピーする。

簡単ではありませんが、後から編集を行う場合、
配列数式を使わないやり方のほうが挙動の理解はしやすいと思います。

回答No.1

mshr1962

配列数式を使う方法でE1に

=LARGE(($A$1:$A$13=$D$1)*$B$1:$B$13,ROW())

と入力してCtrlキーとShiftキーを押しながらEnterで確定

この数式をE2,E3にコピーしてください。

補足

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

やってみましたが、私の説明が悪かったですね。

大きい順ではなく　上から順に抜き出すと言う意味です。