今年も一年お疲れさまでした。これはSpreadsheets/Excel Advent Calendar 2020 - Adventarのやつです。 一昨年に引き続きSpreadsheetを讃えにやってまいりました。V8。
昨日はTeedaさんの以下記事（記事？）でした。列にカラー入れられるの知らなかった。芸術ですね。すごい。
ちなみに前回の2018年、自分はこんな記事を書いていたよ。
Query関数の範囲をふたつ指定出来ることを知った程度でキャッキャしている非常に可愛らしい自分の様子が見て取れます。笑止。
余談ですが当該記事で作った歌い出しドンクイズ、自分以外に23人もチャレンジャーがおられたようで大変喜ばしかったです。
俺の他に23人もチャレンジャーおるやん！！！インターネット！！！ pic.twitter.com/qcoueozxXK
— yone yama (@Tower_of_yone) April 9, 2020
当アドベントカレンダーは今回を含み複数回書く心づもりでおり、後半には VLOOKUP vs INDEX MATCH論争に終止符を打つ という大風呂敷をおっ広げました。しかして今回は敵を作らない話題で書きます。題して「Spreadsheetパンチライン」です。
説明しよう！Spreadsheetパンチラインとは
Spreadsheetのパンチラインです。
HIP HOPにおけるパンチラインといえば当然に 「地球がこんなに丸いんだ 鉛筆とB-BOY 死ぬまで尖れ」(MU-TON)、「お前と違ってドブネズミ 綴ったリリックがイルな刺青」(Scooby J) などが惹起されますが、Spreadsheetのパンチラインとは、日々の作業をより良く進めるための関数群というほどの意味です。
ということで第一回はこちら。 DJ CELORYスピンダッシッッッ
対象にしたい列の位置・名称が変わるときに自動追従するためのパンチライン
=LEFT(ADDRESS(1,MATCH("*hoge*",indirect(A1&"!A1:AA1"),0),4),1)
解説
例えば以下のようなテーブル シートA があるとしましょう。広告界隈の人には見慣れたアレですね。
今回は売上管理の用途として、定常的にD列『売上金額』のSUMを別シートへ参照したいものとしましょう。
シートA↓
| 日付 | impression | click | 売上金額 |
|---|---|---|---|
| 2020-12-01 | 1,000 | 100 | ¥50,000 |
この時点では、定常的にD列『売上金額』のSUMを別シートへ参照したいを満たすためには=SUM(シートA!D:D)と書けば終了です。
しかしながらこれがデータ提供元の仕様変更などの理由で以下みたいになるケースがあります。 D列からF列へ、かつ列見出しも『売上金額』から『売上(税込)』へ変わってしまっています。
シートA' ↓
| 日付 | platform | impression | click | CTR | 売上(税込) | CPC |
|---|---|---|---|---|---|---|
| 2020-12-01 | hoge | 10,000 | 100 | 1% | ¥50,000 | 500 |
素朴に思いをいたせばまずもってこの手の変更を無告知で行うデータ提供元に一発食らわせにいく方面の不穏なアイデアが浮かんでしまいますが、 理知的人類たる我々におかれては穏当な代替策として、列・列名の変更に追従する関数を組んでおくことが望ましいでしょう。
改めてパンチラインはこちら。
=LEFT(ADDRESS(1,MATCH("\*売\*",indirect(A1&"!A1:AA1"),0),4),1)
このパンチラインの要素を分解すると以下のような形になります。入れ子構造の中心から順に見ていきましょう。
INDIRECTで範囲指定
INDIRECT(A1&"!A1:AA1")
まずA1に書いたシート名(シートA)のA:AA列を範囲指定します。
特定文字を含む列をMATCH
MATCH("\*売\*", INDIRECT~,0)
ワイルドカードで 『売』を含む 列がA列から数えて何列目に位置するかを返します。最後の0忘れると結果変わるので注意。 今回のシートAならこの結果が4(D列), A'なら6(F列)になるはず。
MATCHした列でADDRESS
ADDRESS(1,MATCH~,4)
ADDRESSは行・列を番号で指定した場合のセル番号を返します。
セルの表記は3つめのパラメータで指定するのですが、4にするとA1のような素朴な表記になります。
今回のシートAならこの結果がD1, A'ならF1になります。
ADDRESSからアルファベットを抜く
LEFT(ADDRESS,1
これはシンプルにA1のAを抜き出しています。今回の場合、結果はDとかFになるわけです。
ということで結果として、『売』が見出しに含まれる列のアルファベットが出力される という関数がこのパンチラインでした。
これを指定してSUM(INDIRECT パンチライン)なりQUERY(SELECT パンチライン)なりしてあげればSUMが出せます。
ちなみに応用すると以下のように、『これを書いたセルの列アルファベットをゲット』みたいなことも出来ます。
=LEFT(ADDRESS(ROW(),COLUMN(),4),1)
ということで平和が訪れた
このパンチラインにより、売上の列が前後にズレてしまったり、万が一列名が『スーパー爆裂売上太郎』になろうと永遠に売上が参照できます。こともあろうに列名を スーパー爆裂売上太郎 と命名した狼藉者に食らわせを発生させる必要もなくなり、便利でありかつ平和が獲得されました。めでたしめでたし。
いかがでしたか
パンチラインを披露してドヤれたので私は満足です。明日も私がさらなるパンチラインを披露してドヤる運びです。よろしくお願いします。
