A2:B30に表1があります。
使用者がこのシートに入力するさまざまな条件により、A列がTRUEかFALSEになります。
表1ではA列がTRUEのとき、B列に文字列が表示されるようにB列に式が入れてあります。
A列がすべてTRUEになることはありません。したがってB列の文字列の表示は飛び飛びというか歯抜けのような表になっています。
このB列に表示された文字列を、表2(別シート)に上から歯抜けが無いように2行目以降に順番で表示させたいのです。
考えたのはどこかのセルに =B2&B3&B4&~略~&B30 として文字列をまとめ、(文字列の区切りがわかるように、それぞれの文字列の頭に※印をつけておく)、さらにこれを※印を目標に分割する関数はないか?ということなのですがわかりません。
あるいは他の方法でもかまいません。ただし、表1をおもてに出したくないので表1をソートするやりかたは使えません。
どうかお教えください。お願いします。
No.1ベストアンサー
- 回答日時:
こんにちは。
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
- 回答日時:
配列数式については、以下のページを参考にして下さい。
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
- 回答日時:
表1にも少し加工が必要ですが、次の方法でもできると思います。
表1(Sheet1にあるとします)のA列の前に1列追加します。(もともとの表1の範囲はB2:C30になります)
A2に、
=IF(C2<>"",COUNTIF(INDIRECT("B2:B"&ROW()),TRUE),"")
と入力し、オートフィルでA30までコピーします。
(A列は非表示にしてしまってもよいと思います。)
表2(別シートの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までコピーします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) エクセルのことで教えてください。 A1セルに、 別シートのA1セルが1のときは「A」、2のときは「B 4 2023/02/24 23:08
- Excel(エクセル) Excel 文字列を結合するときに重複をなくしたい 関数・VBA 2 2022/12/12 10:40
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- Excel(エクセル) 【マクロ】リボン、行列、数式・ステータスバを非表示に 4 2022/12/12 07:32
- Excel(エクセル) <スプレッドシート>IF関数の複数条件について 5 2022/10/27 14:38
- その他(プログラミング・Web制作) テキストエディタで複数行にわたる文字列の行頭に番号を振る方法 4 2023/03/11 12:57
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/09 08:20
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelのセルを飛ばして入力する
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excel 2019 のピボットテーブル...
-
エクセルの行の抽出について質...
-
【マクロ】エクセルにかいてあ...
-
スプレッドシート クエリ関数 1...
-
エクセルでセルに「氏名を入力...
-
MOS365 Excel Expert / Excel R...
-
excelの不要な行の削除ができな...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelで全角を半角にしたいので...
-
Excel初心者です。 詳しい方、...
-
エクセルの数式で教えてください。
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報