エクセルの空白を詰めて別シートに表示

質問

sheet１のデータを参照して、空白を詰めてsheet２へ表示させたいんです！

sheet１（元データになるもの）
　A列　｜B列｜C列・・・
１行目A｜100｜100
２行目B｜　 ｜200
３行目C｜100｜
４行目D｜　 ｜200
５行目E｜100｜
６行目F｜　 ｜100
・
・

sheet２（sheet１でB列に入力があるものを抽出）
　A列　｜B列
１行目A｜100
２行目C｜100
３行目E｜100

sheet３（sheet１でC列に入力があるものを抽出）
　A列　｜B列
１行目A｜100
２行目B｜200
３行目D｜200
４行目F｜100

sheet2のA1に下記の式を入力してA列とB列に数式をコピー
=IF(COUNT(Sheet1!$B$1:$B$6)<ROW(A1),"",INDEX(Sheet1!A$1:A$6,SMALL(IF(Sheet1!$B$1:$B$6<>"",ROW($A$1:$A$6)),ROW(A1))))

すると下記のように表示されます。
A1＝A　　　　　B1＝１００
A2＝＃ＮＵＭ！ B2＝＃ＮＵＭ！
A3＝＃ＮＵＭ！ B3＝＃ＮＵＭ！
４行目から空白

sheet２のA２のところにエラーが出ていますが、「関数の引数」のところで「数式の結果」には「１００」と
正解が表示されています。（Ｂ２、Ａ３、Ｂ３も同様に）数式の結果のところには正解が表示されています。

答えの「＃ＮＵＭ！」のところに正解を表示させるには、どうしたらいいですか？

見よう見まねでつくったのもで。。。関数にあまり詳しくありません。
よろしくお願い致します。

回答No.2

tom04 · ベストアンサー

No.1です！
たびたびごめんなさい。

投稿した後で質問をもう一度読み返して見ました。

数式は合っていると思いますよ。

ただ、配列数式になっていないのでエラーになっているだけみたいですね。

そのままの数式で
Shift＋Ctrl＋Enterキーで確定してみてください。
ちゃんと表示されるはずです。

ということは・・・
前回の私の回答は余計なお世話になってしまいました。

どうも何度も失礼しました。m(__)m

お礼

Shift＋Ctrl＋Enterキーで解決できました！

数日間悩んで、いろんなサイトを見て解決できずにいたので助かりました！
ありがとうございます。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

計算速度の点で質問のような数式は好ましくありません。
が、配列数式は面白いので ちょっとお遊び。
好ましいし数式ではありませんが、計算速度は多少改善されます。
Sheet3!A1:B1セルを選択して
=IF(COUNT(Sheet1!$C$1:$C$6)<ROW(A1),"",
INDEX(Sheet1!A$1:C$6,
SMALL(IF(Sheet1!$C$1:$C$6<>"",ROW($A$1:$A$6)),
ROW(A1)),
{1,3}))
[Ctrl]+[Shift] ＋[Enter] で確定({}で囲まれる)
下へオートフィル

ちなみに、オートフィルタで代替できないのでしょうか？

お礼

Sheet1にデータが順次追加されていくので、オートフィルタはあまりつかえないのです。。。

[Ctrl]+[Shift] ＋[Enter] でできました！
ありがとうございました。

回答No.1

tom04

こんばんは！
色々方法はあると思いますが・・・
一例です。

↓の画像で説明させていただきます。
Sheet2のみの回答ですが、Sheet3も数式を少しアレンジすれば同様にできます。

尚、A列は配列数式になりますので
もし、この画面から数式をコピー＆ペーストしただけではエラーになると思います。

貼り付けた後にF2キーを押すか、数式バー内を一度クリックします。
そうすると編集可能になりますので
Shift＋Ctrl＋Enterキーを押してみてください。
数式の前後に｛　｝マークが入り配列数式になります。
（ご自分で数式を作られるときは最後がOKではなく、Shift＋Ctrl＋Enterです）

Sheet2のA1セルに
=IF(COUNTA(Sheet1!$B$1:$B$100)>=ROW(A1),INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$B$1:$B$100<>"",ROW($A$1:$A$100)),ROW(A1))),"")

B1セルに（こちらは配列数式ではありません）
=IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$C$100,2,0))

という数式をいれ、A1・B1セルを範囲指定し、B1セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、余計なお世話かもしれませんが、
今回はタイトル行がないのでこのような数式になりましたが、
通常1行目はタイトル行があるかと思います。
その場合のA2セルの数式は
=IF(COUNTA(Sheet1!$B$2:$B$100)>=ROW(A1),INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100<>"",ROW($A$1:$A$99)),ROW(A1))),"")
（配列数式）

B2セルは
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$C$100,2,0))
となります。

数式は100行まで対応できるようにしていますが、
範囲指定の領域はデータ量によってアレンジしてみてください。

以上、参考になれば幸いですが、
他によい方法があれば読み流してくださいね。m(__)m