LANG SELRCT

Google Apps Scriptのコードを書く場所  (新規作成: スプレッドシート | スクリプトエディタ

2020年5月28日木曜日

Arrayformulaで値が入っている最終行までを対象にしたい



以下のような計算をするシートを作りたくて

今回最終的に作った関数はこのような形です。

=ARRAYFORMULA(countif(B:B,D2:INDIRECT("D"&COUNTA(D2:D))))


STEP1〜8に分解して書いていきます。



STEP1
以下のようにA列に日付の入ったデータがあって



STEP2
B2に7文字目までの年/月を取得するLEFT関数を入れて
=LEFT(A2,7)



STEP3
ARRAYFORMULA関数で以降の行にも反映させる
=ARRAYFORMULA(LEFT(A2:A,7))



ここまでならこれでOK。



問題はここからで


STEP4
D1に年/月の分類をするためにUNIQUE関数を入れて
=unique(B:B)


STEP5
E2に年/月の件数を計算するCOUNTIF関数を入れて
=countif(B:B,D2)


STEP6
ARRAYFORMULA関数で展開すると
E4以降に不要な値が入ってしまう…

=ARRAYFORMULA(countif(B:B,D2:D))


STEP7
これを避けるために
「D列で値が入っている行まで」を範囲指定したい
=ARRAYFORMULA(countif(B:B,D2:D3))


A, B, D列のデータ、行数が変わらなければこれでOK

STEP8
A, B, D列のデータ、行数が変わる場合は
その都度範囲指定をしなくて良いように
INDIRECT関数とCOUNTA関数を入れる
=ARRAYFORMULA(countif(B:B,D2:INDIRECT("D"&COUNTA(D2:D))))

これで今回作りたかった関数は完成しました。


参考

LEFT

ARRAYFORMULA

UNIQUE

COUNTIF

INDIRECT

COUNTA

最新の投稿

選択したテキストを配列に入れてテキストエリアに出力したい

今回やりたいこと アプリケーションの中で 選択したテキストを取得する。 選択したテキストを テキストエリアに入れる。 選択したテキストを 配列に追加していく。 STEP1 「テキストを」を選択するとテキストエリアに「テキストを」が入る。 STEP2 その後、「追加して」を選択する...