目的
今回は複数シートに散らばったデータを取得してまとめるために使う関数を作成しました。
あくまで一例です。たぶんもっといい方法があると思います。
エクセルやスプレッドシートは職人さんがたくさんいるので、
人のやり方を見て、勉強になることが非常に多いです。
限りある関数の中で柔軟に対応していく点は、
どんな仕事にも通じる点があるのではないでしょうか。
私もまだまだ勉強不足ですが、誰かの参考になれば幸いです。
名前付き関数
いきなりですが、名前付き関数の紹介から始めます。
名前付き関数はざっくりいうと、複数の関数を組み合わせた関数や固定値などを、
名前を付けて呼び出せるようにするものです。
作成方法はGoogleの公式のドキュメントに詳しく書かれています。
引数が同じで統一できそうな箇所は統一すると、タイプミス等のミスも減らせます。
これから紹介する関数も同じ引数でまとめられるところ、冗長な表現も多いので、
名前付き関数を作成して利用することをお勧めします。
スプレッドシート内で名前付き関数を作成すると共有するときなどは注意が必要です。
簡単にインポートエクスポートできるので、関数ライブラリのシートを作っておくと便利です。
サンプルデータ
本題に入っていきます。
今回は生徒の情報が載ったシートが複数クラス分あると思って進めていきます。
こんな感じのシートが、いくつかあるとします。
ただしクラスによって人数が違うとします。
データ取得
最終的にある程度自動でデータ範囲にあるデータを取得するように、関数を作っていきます。
まずはデータ範囲を取得する関数から定義していきます。
最終行取得
下記関数で取得できます。調べるとよく出てくる関数です。
ここで’A Class’!A4:Cと指定してるのはシート名と範囲です。
今回は開始セルを指定しています。
データ開始列までにデータがない場合や、固定値が入っている場合は自動化できそうですが、
その場合は上部は固定の場合が大半だと思うので、今回は直接指定します。
他にもCOUNTAとかMATCHを使うやり方もあります。
用途に合わせて使い分けてください。
=MAX(ArrayFormula(('A Class'!A4:C<>"")*(ROW(A!A4:C))))
この関数を使って、下記のような表を作ります。
最終的にこのデータを使ってすべてのデータを取得します。
一気に書くと、関数が長すぎるので、段階を踏んで書いてます。
F列は文字列を結合しただけです。下記のような数式で作成できます。
="'"&シート名&"'!"&開始行&開始列&":"&終了行&終了列
データ取得
最終的に下記の数式を利用してデータをまとめました。
範囲の部分には上図のF2:F4を指定しています。
長いので名前付き関数にしていますが、実態は下記の数式です。
今回は３シートだったので={INDIRECT(範囲1); INDIRECT(範囲2); INDIRECT(範囲3)}のような書き方でも書けますが、シートが多い場合はまとめてからの方が対応しやすいし、可読性もあがります。
数式は長いですが、やってることは上で定義した文字列をINDIRECTで評価してあげて、
あとは配列で返却できないところを１列にして、また配列に戻して
みたいなことをずっとやってるだけです。
=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(JOIN(";", BYROW(範囲, lambda(row, JOIN(";", BYROW(INDIRECT(row), lambda(r, JOIN(",", r))))))), ";")),","))
クラスも表示する
無理やりですが下記のような関数で、まとめの表を作った時の情報から作成しました。
担任は書いたけどやってません。同じようなやり方でできると思います。
=TRANSPOSE(SPLIT(JOIN("", MAP({"class";"A"; "B";"C"},BYROW('データ取得'!B2:E5, lambda(r, index(r, 1,4)- index(r, 1,2)+1)), lambda( class, num, REPT(class&",",num)))),","))
おまけ
開始行取得
開始行と列はあまり需要はないかもしれないので、おまけです。
普通は左上から始まるので。
何か使う機会があればご利用ください。
=MIN(ArrayFormula(IF(('A Class'!A4:C<>""), ROW('A Class'!A4:C), ROWS('A Class'!A4:C))))
開始列取得
=MIN(ArrayFormula(IF(('A Class'!A4:C<>""), COLUMN('A Class'!A4:C), COLUMNS('A Class'!A4:C))))
最終列取得
=MAX(ArrayFormula(IF(('A Class'!A4:C<>""), COLUMN('A Class'!A4:C), 0)))
さいごに
今回のやり方でもシートと開始行は指定してあげなければいけません。
開始行は固定だとしてもシート名は、スプレッドシートの関数では取得できないので
手で作業してあげる必要があります。
自動化したい場合は、GAS(Google Apps Script)で実装あげるしかないですね。
今回紹介した関数もGASを学んだ方が、
よっぽど簡単に同じことが実現できると思います。
ただGASは遅いし、使いすぎると同時実行数の制限、１日の起動時間の制限
等を気にしないといけないです。
なのでシートの関数として使う時には、注意をしないといけません。
MAPとかFILTERなどはなんとなくあるのは知っていましたが、
BYROW関数やLAMBDA関数などの便利関数も使いこなしていきたいです。
あとは名前付き関数で便利な関数をいっぱい作っておくと
普段の作業もより楽になるのではないでしょうか。
参考文献
Googleスプレッドシートの「名前付き関数」って便利なの？ 早速使ってみました