エクセルでこんな関数は？

質問

A2:B30に表１があります。
使用者がこのシートに入力するさまざまな条件により、A列がTRUEかFALSEになります。

表１ではA列がTRUEのとき、B列に文字列が表示されるようにB列に式が入れてあります。
A列がすべてTRUEになることはありません。したがってB列の文字列の表示は飛び飛びというか歯抜けのような表になっています。

このB列に表示された文字列を、表２（別シート）に上から歯抜けが無いように２行目以降に順番で表示させたいのです。

考えたのはどこかのセルに =B2&B3&B4&～略～&B30 として文字列をまとめ、（文字列の区切りがわかるように、それぞれの文字列の頭に※印をつけておく）、さらにこれを※印を目標に分割する関数はないか？ということなのですがわかりません。
あるいは他の方法でもかまいません。ただし、表１をおもてに出したくないので表１をソートするやりかたは使えません。

どうかお教えください。お願いします。

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

仮にシート2のA2から順に表示させるとします。
A2をクリックして数式バーに、

=IF(ROW(A1)>SUMPRODUCT((Sheet1!$B$2:$B$30<>"")*1),"",INDEX(Sheet1!$B$2:$B$30,SMALL(IF(Sheet1!$B$2:$B$30<>"",ROW(Sheet1!$B$2:$B$30)-1,65537),ROW(A1))))

と入力し、数式バーにまだカーソルがある状態で、[Ctrl]と[Shift]を押しながら[Enter]を押します。
うまく行くと、数式の両端に「{}」が付きます。
（このような式を配列数式といいます。）
うまくいったら、下の行（最大30行までで十分）へオートフィルコピーします。
なお、[Sheet1]は表1があるシート名です。

お礼

ありがとうございます！
出来ました。驚きです。

しかし、式の意味がさっぱりわかりませんのでわたしには他に応用がきかないと思います。

よろしければ式の意味を解説していただけないでしょうか？
もちろんIF、ROW、INDEX、SMALL関数の意味はわかりますが、配列数式は使った事がなくおぼろげな知識です。SUMPRODUCTもよくわかってはいません。したがって全体の意味がほとんど理解できないんです。

勝手を言ってすみません。

回答No.3

maruru01

配列数式については、以下のページを参考にして下さい。
SUMPRODUCT関数についても少し載っています。

http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

式については端折って説明します。
まず、

IF(Sheet1!$B$2:$B$30<>"",ROW(Sheet1!$B$2:$B$30)-1,65537)

では、Sheet1のB列に値があれば（行番号-1）を返し、なければ（""ならば）65537を返します。
それが配列になっているので、

{65537,2,3,65537,65537,6・・・}

のような配列が返ります。
なお、65537という数値は、Excelの最大行数+1です。
（つまり、返される行番号より"必ず大きい"ということです。）
-1しているのは、B列のデータが2行目から始まっているためです。
で、この配列でROW(A1)番目に小さい値を、SMALL関数で返しています。
ROW関数の引数は相対参照なので、行方向へコピーすると、順にずれていき、先頭行から順に、
1番目（ROW(A1)）に小さい値
2番目（ROW(A2)）に小さい値
・・・
となるわけです。
ここで、B列に何も値がない場合（65537）は飛ばして数えられます。
このSMALL関数で返される値は、B列範囲の行位置を表しています。
したがって、INDEX関数を使用してB列の値を参照出来るわけです。
あとは、下のほうの行がエラーにならないように、IF文の条件式で、

ROW(A1)>SUMPRODUCT((Sheet1!$B$2:$B$30<>"")*1)

としています。
ここで、SUMPRODUCT関数は、B列範囲で値がある個数を返していますので、それ以上の行はいらないということです。

参考URL：

http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

お礼

ご丁寧にありがとうございました。
じっくり勉強します。
たすかりました。感謝感激です。

回答No.2

MSZ006

表１にも少し加工が必要ですが、次の方法でもできると思います。

表１（Sheet1にあるとします）のA列の前に１列追加します。（もともとの表１の範囲はB2:C30になります)
A2に、
=IF(C2<>"",COUNTIF(INDIRECT("B2:B"&ROW()),TRUE),"")
と入力し、オートフィルでA30までコピーします。
（A列は非表示にしてしまってもよいと思います。）

表２(別シートのA2から始まるとします）のA2に、
=IF(ISNA(VLOOKUP(ROW()-1,Sheet1!$A$2:$C$30,3,FALSE)),"",VLOOKUP(ROW()-1,Sheet1!$A$2:$C$30,3,FALSE))
と入力し、オートフィルでA30までコピーします。

お礼

ありがとうございました。
これはわかりやすいようですね。
今回はNo1さんの方法でやりましたが勉強してみます。