スプレッドシート　ARRAYFORMULA関数とCOUNTIF関数、CONCATENATE関数を組み合わせて使いたい

前提

ファイル内に２つのスプレッドシートがあります。
1)入力シート
2)マージシート

マージシートのC列～G列は入力シートから転記し作成します。
マージシートには、入力シートの内容が追記、累積されていくので最終行
が可変です。
H列～L列の内容は、関数でD列、G列の内容をもとに自動生成させたいです。
H列以降の各列の数式案は下イメージ参照。

<マージシートイメージ>

実現したいこと、発生している問題

最終行が可変のため、H列～L列の数式は可能な限りARRAYFORMULA関数
を使用し、最終行まで自動反映させたいと思っています。
が、緑アミのI列とK列の数式の部分がうまくいきませんでした。

各行に数式コピーせずに最下行まで数式反映させる何かよい方法はない
でしょうか?

①　I列　最終行まで◆、◇判定をさせる方法があるか
②　K列　最終行までI列、J列の合体処理をさせる方法があるか
③　L列　現案以外に対象、対象外判定をさせるよい方法があるか
④　H列　H列の判定結果、D列に値ありの時だけ結果表示に変えたい※

※現イメージでは、D列値なしの時にも、H列の判定結果が表示されて
しまっています。ISBLANK関数と組み合わせれば、D列に値ありの時だけ、
判定結果が表示される仕様に変えられるのでは? と思ったのですが、
自力で数式修正ができませんでした。
何かよい方法がないか、アドバイスいだたけないでしょうか?

試したこと

▼K列(作業用)の場合
ARRAYFORMULAを使おうと下の数式を入れると、
下画像のとおりの合体処理となってしまいます。
=ARRAYFORMULA(CONCATENATE(I12:I,J12:J))

<(誤)　イメージ>

補足情報（FW/ツールのバージョンなど）

▼L列の「対象」「対象外」判定
I列、J列の内容を見て一発で判定させることも考えましたが、
あとでメンテナンスが必要になった際、別の担当者が煩雑な数式を
読み解かなければならないのを回避したかったので、
(そして、自分も後で見返したら混乱してしまいそうなので、、)
K列を噛ませて判定をさせるつくりにしました。

お忙しいところ大変申し訳ありませんが、何かよい方法がないか
アドバイスをいただけないでしょうか?

行動規範の内容に同意します

回答1件

ベストアンサー

下記でいかがでしょうか。
①　I列　最終行まで◆、◇判定をさせる方法

GSpread
1「I列12行目」に下記の数式を入力：
2=ARRAYFORMULA(IF(REGEXMATCH(D12:D, "000$"), "◇", "◆"))

　
②　K列　最終行までI列、J列の合体処理をさせる方法

GSpread
1「K列12行目」に下記の数式を入力：
2=ARRAYFORMULA(I12:I & J12:J)

　

③　L列　現案以外に対象、対象外判定をさせるよい方法があるか
→現状で問題ないと思います。
仮に、あえてＫ列（I列とJ列の結合）を挟んで計算するのを省略したいならば

GSpread
1「L列12行目」に下記の数式を入力：
2=ARRAYFORMULA(IF((I12:I="◆")*(J12:J="初回"), "対象", "対象外"))

とすることもできます。ただし今回はわかりやすさのためにＫ列を作っているとのことですので、これを行う必要はないと思います。

　
④　H列　H列の判定結果、D列に値ありの時だけ結果表示に変えたい

GSpread
1「H列12行目」に下記の数式を入力：
2=ARRAYFORMULA(IF(D12:D="" , "", IF(REGEXMATCH(G12:G, "②"), "②", "①")))

または、ISBLANK()関数を使いたければ

GSpread
1「H列12行目」に下記の数式を入力：
2=ARRAYFORMULA(IF(ISBLANK(D12:D), "", IF(REGEXMATCH(G12:G, "②"), "②", "①")))

　
※ARRAYFORMULAを含む式を入力するときは、式入力前に、ARRAYFORMULAによって展開される先のセル範囲の値を削除しておいてください。そうしておかないと#REF!エラーが出ます。

投稿2022/04/02 02:43

編集2022/04/02 03:04

退会済みユーザー

総合スコア0

donguriko

2022/04/02 06:44

qnoirさまお忙しい中、回答ありがとうございます!! ご回報内容につき何点か教えてください。 ① 「"000$"」で末尾「000」の意味になるのですね。　勉強になりました。 ②文字列の合体させたければCONCATENATE　と　思い込んでいましたが、& での合体もアリなのですね。　全然思いつかなかったです。。。 ③K列を挟まない場合の数式の*の意味と使い方について教えてください。　IF((I12:I="◆")*(J12:J="初回"), "対象", "対象外") 　1個目の条件(I12:I="◆")かつ2個目の条件(J12:J="初回")　の&条件　という意味合いという理解であっていますか? <さら問い> 　参考まで、同様にor条件の場合の記号? とかもありますか? ④頭にIF関数でD列がブランク(TRUE)の時はブランク、　元々記載していた内容をFALSEの場合として記載なのですね。　勉強になりました。スプレッドシートの普通の関数も、よく使う関数以外はまだまだ　知らないことだらけです。　しっかり覚えて次からは自分でできるようになりたいと思います。いつも丁寧に教えていただき本当にありがとうございます!!!! 　

退会済みユーザー

2022/04/02 12:31

＞ ③K列を挟まない場合の数式の*の意味と使い方について教えてください。＞　IF((I12:I="◆")*(J12:J="初回"), "対象", "対象外") ＞　1個目の条件(I12:I="◆")かつ2個目の条件(J12:J="初回")　の&条件＞　という意味合いという理解であっていますか? →その理解で合っています。＞　参考まで、同様にor条件の場合の記号? とかもありますか? →プラス記号（+）でつなぐと、OR条件になります。

donguriko

2022/04/03 00:55

qnoirさまお忙しい中、回答ありがとうございます。 & 条件は「*」 or条件は「+」　覚えて、早速今日から使いたいと思います!! 回答ありがとうございました。

行動規範の内容に同意します