エクセルの関数について。

質問

エクセルの関数。

Sheet1の作業列1のA2セルに
=IF(COUNTA(Sheet2!2:2)=0,"",IF(Sheet2!A2="",A1,Sheet2!A2)) →数式1とする
作業列2のB2セルに
=IF(A2=$D$2,ROW(),"") →数式2とする
という数式を入れA2・B2セルを範囲指定 → B2セルのフィルハンドルで下へコピーしておきます。
（Sheet2の行数以上コピーしておく）

そしてE2セルに
=IF(COUNT($B:$B)<ROW(A1),"",INDEX(Sheet2!B:B,SMALL($B:$B,ROW(A1)))&"") →数式3とする
という数式を入れオートフィルで列方向・行方向にコピーする。

教えて頂きたいことは、数式1、数式2、数式3が何を意味しているのか。
と、この数式を使って、表を作成したのですが、ある特定の数字（コード、Ｄ2セルに入力）を入力しても反応してくれない。
（Ｅ2～Ｈ2まで、空白セルの状態になります。）
（sheet2は約1200行あります。）

教えて頂けないでしょうか？

回答No.3

MarcoRossiItaly · ベストアンサー

こういうQAサイトは本来、質問をする場です。質問者の方々の代わりに、回答者に作業させることが目的ではないのですよね。ですから質問者さんは、前回のご質問のときに、分からない部分があれば回答者に確認し、自分なりにでも内容を理解した上で数式を使うようにされるべきでした。

さて、式の意味について。まず、「数式3」の「Sheet2!B:B」という箇所に「$」が付いていないという件ですが。参照先はSheet2ですから、相対参照、すなわち「$」なしで、問題ないと思います。

各式の意味は、添付図に表示されているとおりにSheet2の値をSheet1に持ってくることで、同一コードのレコードだけを抜き取って表示しているわけですね。

式全体を見て、何か問題があるという感じは、今のところしていません。試しに私の手元でも添付図どおりに入力してみましたが、添付図どおりの結果が表示されました。

添付図は、No.2さんが前のご質問というので回答されたときの図の使い回しですよね？質問者さんが新たに入力されたわけではないのですね？

質問者さんは、正確に添付図どおりに入力されましたか？E～H列に異常があるとのことですが、他の箇所の計算結果はどうですか？また、Sheet1の1行目とか、Sheet2の内容も、そっくり同じにしていますか？さらに、数式は、ウェブページからコピーしたものを使われたでしょうか？

「ある特定の数字を入力しても」とは、一部のコードのみのことですか、それともどんなコードでもダメですか？

使用条件が覆ると、うまく行くものも行かなくなります。この場合、データ量は関係ありません。きちんとご説明ください。

回答No.2

tom04

こんばんは！
前回この数式を投稿した者です。

数式の詳しい説明はNo.1さんがしっかりしてくださっているので、まさにその通りです。

本来であれば作業用の列はSheet2に作成して方が判りやすいと思うのですが、
前回の投稿でSheet2には手を加えられない！という条件があったと思います。
そこでSheet1に作業用の列を設けた次第です。

作業列の目的だけ・・・
作業列1の目的はSheet2のA列（コード）が空白だと検索がかなり難しくなります。
そこで、1セルでもデータが入っているものすべてにコードを表示させています。

作業列2は
Sheet1のD2セルと一致するデータのみの行番号を表示させます。

最後の数式で作業列2に入っている数だけ行番号の小さい順に表示させています。
※　最後の　&""　の部分はSheet2の空白セルを表示してしまうと「0」となりますので、
それを回避するためです。

ただ・・・
D2セルにデータを入れても結果が表示されない！というコトですね？
考えられる原因としては
（1）作業列1・2のオートフィルがSheet2のデータがある最終行まで行われていない。
（2）D2セルデータがSheet2のA列にない。

原因としてはこの程度しか思いつきません。

追伸
前回のお礼欄に複数データ抽出が希望だと書いてありましたが、
お示しのレイアウトでは、関数で表示させるとなるとかなり難しくなると思います。

マクロでやれば可能かもしれません。

この程度でごめんなさいね。m(_ _)m

回答No.1

tsubuyuki

最終的にどうしたいのかがわからないので、
とりあえず式の解説だけでも。

数式１
=IF(COUNTA(Sheet2!2:2)=0,"",IF(Sheet2!A2="",A1,Sheet2!A2))
COUNTA関数は、範囲内の「空白以外」のセルの個数を返す関数ですから、
わかりづらく日本語で読んでいくと

Sheet2の2行目の空白以外のセルが0個だったら、空白を、
0個じゃなかった場合、Sheet2のA2セルが空白だったら（Sheet1の）A1セルを、
そうじゃない場合はSheet2のA2セルを、返しなさい。

と言う意味です。
Sheet1のA1セルは"作業列1"と言う文字列が格納されていますから、
ここで既におかしなことになりそうな気がします。

数式２
=IF(A2=$D$2,ROW(),"")
ROW関数は行番号を返す関数ですから、

A2セルがD2セルと同じなら、行番号を、
違ったら空白を返しなさい。

と言う意味でしょうか。
> フィルハンドルで下へコピー
しているようですが、D2が絶対参照されていますので、
どれだけ下にコピーして行っても必ずD2セルを見てしまいます。
大丈夫でしょうか。

数式３
=IF(COUNT($B:$B)<ROW(A1),"",INDEX(Sheet2!B:B,SMALL($B:$B,ROW(A1)))&"")
COUNTは範囲内の数値が入力されているセルの個数を返す関数、
INDEX関数はINDEX(範囲,行,列)の形で使用し、範囲内の●行目・○列目のセルを返す関数、
SMALL関数は範囲内で○番目に小さい値を返す関数ですから、これらをまとめると

B列内の数値データがROW(A1)つまり、１未満・・0個なら、空白を
そうじゃなかったら、INDEX以下の処理をさせる・・と言う意味です。
ここで、SMALL関数を使って、B列でROW(A1)＝１番小さい値を求め（■）、
INDEX関数にその値を与えて、B列で上から■行目のセルの値を最終的に返してきます。

これを
> E2セルに
> オートフィルで列方向・行方向にコピー
すると、今度は相対参照ですので、
F2ではINDEX以下の参照列がC列になってしまいます。
見た感じ、C列は全空白のようですので、これは上手くいかないのでは？と想像できます。

最終的な形がハッキリしないので、すいませんが、コレが限界です。