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

最新の投稿

Pixel 5でSuicaが使えないときはGoogle PayでSuicaを有効にしてみる

Pixel 5への機種変でSuicaが使えない人向けに書きました。 MISSION Pixel 5でSuicaを使えるようにする SOLUTION 旧端末で機種変更の設定を行う NFCをオンにする Google PayでSuicaを有効にする PROBLEMS 以前...