Googleスプレッドシート困った表を集計する為の加工数式（結合セルに負けない）

mir

2023年6月18日 09:53

週１とは言え、毎回Googleスプレッドシートだけでよくそんだけ書けますね。なんてことを言われますが、バズるかはともかく書きたいことはいくらでもあるんですよね。

本当はもっと１回あたりのテーマを絞った 短めの noteをいっぱい書いた方がいんでしょうが、どうしても書き出すとアレもコレもと話が広がって長くなっちゃいます。（逆に書き出すまでが腰が重いタイプ）

前回までの フィルタ表示シリーズは、まさにそんな感じで１回あたりが長い上に４週もフィルタ表示ネタで引っ張っちゃいましたｗ

しかも、SUBTOTALの挙動や QUERY関数なんかも盛り込む、マシマシnoteで普通の人には重すぎる内容ｗ

ジロリアンならぬ スプシアンだけが喜ぶ noteってのも良くないなと。

かつての格ゲーみたいに、新参が入りづらくなったコンテンツは衰退していくものです。

なるべく裾野を広げるネタも書いていきたいなと思います。

というわけで、今回は「あるある」ネタです。

シリーズ前回の記事

でも、やっぱ瞬間風速だけ高いその時だけしか注目浴びない noteよりも、後々困った人が検索でたどり着くような 残る noteを書いていきたいんですよねー。（鮮度も大事だけど）

そうすると、やっぱ 麺固め、濃いめ、脂多め みたいな noteになっちゃうｗ

「あるある」な困った表と向き合う

皆さんの会社にもこんな表がないでしょうか？

「あるある」と思った方には、今回の noteが役立つかもしれません。
一部の式は Excelでも使えるので、Excelユーザーの方にも役立つかも。

見た目を優先すると、データとして扱いづらくなる

見た目重視の表とデータベースとして使える集計しやすい表は、なかなか相容れないものです。

他の人が作った表をベースに集計しようとして、結構困ることがあります。

たとえば、上のような部署、社員ごとの実績管理表があった場合、「見やすさ」を優先して部署がセル結合してあったり・・・。

気持ちはわかりますが、これやられちゃうと集計や絞り込みをする時に困るんですよね。

そもそも縦に結合したセルがあると、先週までシリーズ連載していた フィルタ（フィルタ表示）でのソートが使えません。

もちろん、気軽に関数による抽出、検索も出来ません。

結合セルがなくても、見やすさを優先した結果、各データの 先頭行にだけ記述するといった表も「あるある」ですね。

これも同じく日付で絞り込もうとすると、小笠原さん以外の人はシステからは 日付なしと判断されてしまい、うまく集計ができません。

とはいえ、

データベースファーストな表の方が良いんです！

と、他の社員の意見を無視して以下のような表に変えると・・・

みっちりと同じ文字が繰り返される表になってしまい、閲覧者や普段利用する人からは「見づらい」「使いにくい」といった声が上がってしまいます。

で、いつのまにか最初の結合セル表に戻ってるってパターンも多いのです。

デザイン系の記事でよく見かける「余白」 の重要性

この「余白」が人間にとっては重要なんだけど、データベース的には困りもんってことですね。

両方を考慮した、人間からもシステムからも扱いやすい表はどうすれば良いか？

落としどころ（一番簡単な解決策）背景色と同じ色の文字を使う

一番簡単な解決策は、結合セルは諦めてもらうとして、先頭行の文字だけ見えるようにして、以降の繰り返し文字を白（セルの背景色と同じ）にして 見えなくする方法です。

これなら余白多めで見やすさは確保しつつ、システム的にもデータとして扱え、フィルタでの絞り込みや FILTER関数や SUMIF関数、QUERY関数が使えます。

注意点としては、文字が見えないので 誤操作で消されてしまった時に気づかないという点。

それを回避する為に、アラートを出す保護をかけたり、条件付き書式で消されたことがわかるようにしておくと良いです。

空白があった場合は、セルに色を付けて目立たせる。条件付き書式でこれを設定しておくだけでも、誤操作発見に繋がります。

でも、この隠し文字を入れる方法が使えるのは、あくまでも 自分が管理できる表だけなんですよね。。

怖い上司が作った表や、自分が出しゃばって勝手に書き換えることが難しい他部署の表にはこの方法は使えません。

そもそも、自分が触れない保護されている表ってパターンもあります。

では、結合セル多用、もしくは塊ごとの先頭にしかデータが入っていない 困った表から直接集計する方法はないのか？

お題形式で考えていきましょう。

作業列（作業用シート）を使って集計

元の表をさわらずにデータとして使いやすい形にしたい場合、まず考えられるのが作業列や作業用シートに一度出力する方法です。

簡単ですが、まずはこれをお題として考えてみましょう。

Q1. 上の画像のように縦結合のセルがある A列を F列のようにしたい。下にフィルコピーするとして、最初のF2セルにはどんな式を入れればよいか？

いきなり 1つの式で処理はハードルが高いので、下にフィルコピーする前提で F2に入れる式を考えてみましょう。

どうでしょうか？基本ですが、普段Excelやスプレッドシートを使ってる人でもこれが出来ない人が半数くらいいます。

まずは自力で考えてみましょう。

↓↓↓
回答は以下
↓↓↓

A1. 縦結合の列のデータをバラす式

F2に入れる式は

=IF(A2<>"", A2 , F1 )

または

=IF(A2="", F1 , A2 )

結果はどっちも一緒

こちらになります。式の見栄えとしては下の方がよいですが、動作を理解するには上の式が分かりやすいかと思います。

まず、 結合セルの中身のデータがどうなっているか？

結合セルは結合されているセル範囲の 左上だけにデータが入っており、それ以外は空白 となっています。（結合を解除すると実際そうなっている）

だから、A2:A4 が結合されて「東京第1営業部」と入っていた場合、

A2　東京第1営業部
A3　空白
A4　空白

このようになっています。

統合セルの文字の表示位置が上下真ん中になっていても、値が入ってるセルが左上であることに変わりはありませんｗ

というわけで F2 に入れる式は、

A2（A列）が空白でなければ、A2をそのまま返す
A2（A列）が空白なら式を入れている F2の1つ上 F1を返す

このように記述すればよいわけです。

空白かどうか？で結果を分岐させるので IF関数の出番ですね。
それを式にしたものが ↓ です。

=IF(A2<>"", A2 , F1 )

当然ですが、開始の A2が空白でないことが前提となっています。あとは、これを下にフィルコピーで OＫ。

簡単ですね。

他のシート（作業用シート）に出力する時も、考え方は一緒です。

作業列を組み合わせて集計する

この作業列 F2:F30 に出力したデータを組み合わせることで、関数での集計が可能となります。

H2に部署名のプルダウンをセットして、その部署の合計契約件数（D列）を集計する場合は I2に

=SUMIF(F2:F30,H2,D2:D30)

ユーSUMIFしちゃいなよ

このように SUMIFを入れればOK。

F2:F30 で H2 と一致した行の D2:D30 の値を SUMするという式です。

SUMIFやSUMIFSはスピル以前のExcelでも使えるんで、根強い人気の便利な関数ですね。

さらに、Googleスプレッドシートではお馴染みの QUERY関数を使えば、一気に部署ごとの目標と実績の集計表を作成することも可能です。

=QUERY(C1:F30,"select F,sum(C),sum(D) where C is not null group by F")

元の表の並びに作業列を作ったので、関係ない E列を含めて C1:F30 を範囲として Select 句で必要な列だけ取り出す。

Query関数ならではの処理ですね。

もちろん範囲側を必要なものだけに加工しておく書き方もあります。

=QUERY({F1:F30,C1:D30},"select Col1,sum(Col2),sum(Col3) where Col1 is not null group by Col1")

対象が配列の時は Col1,Col2といった指定になる

少し脱線しますが、Query関数のオプション句を長々と記述すれば、

sum(D)/sum(C) で計算した達成率をformat句で %表記 に、その達成率が高い順で order by で並び替え、さらに label でラベルを修正。

こんなことも出来ます。

=QUERY(C1:F30, "select F, sum(C), sum(D), sum(D)/sum(C) where C is not null group by F order by sum(D)/sum(C) desc label sum(C) '目標', sum(D) '実績', sum(D)/sum(C) '達成率' format sum(D)/sum(C) '0.0%' ")

句は順番が違うとエラーになるので注意

=QUERY(C1:F30,
 "select F, sum(C), sum(D), sum(D)/sum(C) 
  where C is not null 
  group by F 
  order by sum(D)/sum(C) desc 
  label sum(C) '目標', sum(D) '実績', sum(D)/sum(C) '達成率'  
  format sum(D)/sum(C) '0.0%' "
)

そのうち QUERY関数についても書きたいと思うんですが、結構他の人がやりつくしてるんで、いまいちモチベが上がらないｗ

↓ 公式は丁寧じゃないんで見てもよくわからないと思いますが、一応

作業列と元の表のデータを組み合わせて集計することが出来ました。

作業列の式を 1つの式で記述する

最終目標は 作業列なしで困った表を集計する！ なので、そのためには作業列の式を 一つの式でスピらせる必要があります。

どう記述すればよいでしょうか？

Q2. 縦結合のセルがある A列を F列のようにしたい。F2セルにだけ式を入れて実現することは可能か？

先ほどのフィルコピーした式

=IF(A2<>"", A2 , F1 )

↑ こちらは式を入れる F列の一つ上を参照する式なんで、そのまま Arrayformulaに出来ません。

循環参照エラーがでちゃいます。

じゃあ、どのような方法があるか？

少しハードルがあがりますが、自信のある人は考えてみましょう！

要件として範囲は A1:A30として、

このように下のデータがない部分の判定に関しては、B列が空白かどうかを条件として使えることにしましょう。

↓↓↓
回答は以下
ちなみに LAMBDAヘルパー関数を使う方法と、
LAMBDAや新関数を使わず Arrayformula で処理する方法があります。
↓↓↓

A2. 1つの式で縦結合の列のデータをバラす

まずは比較的簡単な LAMBDAヘルパー関数を使う方法から解説していきます。

処理の中でポイントとなるのは、

A2（A列）が空白なら式を入れている F2の1つ上 F1を返す

この処理です。

一つ上の値（つまり 一つ前の計算処理の結果）を利用したい。まさにこんな時にうってつけの関数があります。

LAMBDAヘルパー関数の中でも最強クラスの　REDUCE関数と SCAN関数 です。

今回の場合は途中経過を出力する SCANを使いましょう。

ちなみに、ありがたいことに

SCAN関数　スプレッドシート

で検索すると、公式の次に mirのnoteが表示されます！

で、回答はこちら ↓

=SCAN(,A2:A30,
LAMBDA(pv,cv,IFS(cv<>"",cv,offset(cv,,1)<>"",pv,true,)))

A2が空白じゃないことを前提としているので、初期値は空でOKです。A2:A30を順に処理していくので冒頭は

=SCAN(,A2:A30,

となります。で、

一つ前の処理の結果（累積値）を　pv
A2:A30　を一つずつ取り出したもの（現在の値）を　cv

と置いています。

=SCAN(,A2:A30,LAMBDA(pv,cv

あとは IFS関数の処理ですね。

IFS(cv<>"",cv,offset(cv,,1)<>"",pv,true,)

IFSは左（前）から順に判定して、条件が合致した時の結果を返します。

IFS(条件1, 値1, [条件2, 値2, …])

今回の場合は

IFS(
　cv<>"",cv,　
　※条件式１ cv が空でないなら cv を返す。
　　これが次の処理の pv になる

　↓ 通過した場合は cvは空であるということ

　offset(cv,,1)<>"",pv,
　※条件式2 cvの一つ右、つまり B列が空でなければ
　　pv（一つ上の結果）を返す。これが次の式の pv になる

　↓ 通過した場合は B列が空であるということ

　true,
　※ 条件式1，2にヒットしないその他は全て空白を返す
　カンマで終わってますが、これは空白を返すという意味
　)

このような処理がされています。

IFS関数で「それ以外の時」の処理は一番後ろに

true, (それ以外の時の処理）

と記述します。

cv は意外にもセル参照を一つずつわたしてるんですね。

だから offsetで今処理している A列のセルの隣、同じ行のB列セルを取得することが出来ます。

別のケースでも同じように使えます。

SCAN関数が、不慣れな人には理解が難しいかもしれませんが、LAMBDAヘルパー関数の登場で、だいぶすっきり記述できるようになりました。

じゃあ、LAMBDA登場以前はどのような式で処理していたか？

別解として、それも見ておきましょう。

A2（別解）. 1つの式で縦結合の列のデータをバラす

=Arrayformula(IF(B2:B30="",,
XLOOKUP(ROW(A2:A30),IF(A2:A30="",,ROW(A2:A30)),A2:A30,,-1)))

こちらになります。

いやいや、XLOOKUPって・・新関数使ってるじゃん！

ってツッコまれそうなんで、一応 LOOKUPでも出来るって回答も書いておきます。

=Arrayformula(IF(B2:B30="",,
LOOKUP(ROW(A2:A30),IF(A2:A30="",,ROW(A2:A30)),A2:A30)))

※VLOOKUPを使う方法もある

ポイントは

IF(A2:A30="",,ROW(A2:A30))

この部分です。A列が空白の場合は空白、空白でない場合は行番号を返す配列を生成しています。

この配列を対象に XLOOKUPの第4引数 -1 による 近似値一致小、もしくはLOOKUP の近似値一致で行番号 ROW(A2:A30) を1つずつ検索していきます。

そうすると、例えば ROW(A3) つまり行番号 3 の時は、検索配列に 3がないので、3以下のもっとも3に近い値 2がヒットします。

結果列に A2:A30を指定しているので A2の値、東京第1営業部が返ることになります。

近似値一致を応用したテクニックです。

この困った表がとんでもない行数の場合は、別解のArrayformulaを使った処理の方が若干早いかもしれませんが、普通に利用するなら 今どきは SCAN式の方がよいでしょう。式も短いし。

あと、例外的に「条件付き書式」で利用したい場合は、別解の式の方が人によっては簡単に感じるかもしれません。

これは後ほど触れます。

困った表から直接データ集計する

SCAN関数を使うことで、一つの式で結合セル入りの列を データとして扱える状態に出来ました。

じゃあ、この式をそのまま組み込めば、作業列なしで直接困った表からデータ集計できそうですね！

やってみましょう。

困った表から直接SUMIFする

作業列なしで困った表から SUMIFで集計できているのがわかりますね。
さらに、条件付き書式で選択している部門も色付けできています。

まずは F2に入っている集計の式

=LET(range,A2:D30,key,F2,SUMIF(SCAN(,index(range,,1),LAMBDA(pv,cv,IFS(cv<>"",cv,offset(cv,0,1)<>"",pv,true,))),key,index(range,,4)))

ちょっと長いんでわかりづらいですね。コード記述でインデントつけましょう。

=LET(
  range,A2:D30,
  key,F2,
  SUMIF(
    SCAN(
      ,index(range,,1),
      LAMBDA(
        pv,cv,
        IFS(
          cv<>"",cv,
          offset(cv,0,1)<>"",pv,
          true,
        )
      )
    ),
    key,
    index(range,,4)
  )
)

セルの指定記述を LETを使って前半にまとめて変数化しています。

そこから使う列を indexで取り出して、SUMIFと先ほどの SCAN式を組み合わせればOK。

困った表を条件付き書式で可変色付けする

F2で選択した部署のデータに色付けする条件付き書式、こちらの設定方法も確認しましょう。

カスタム数式はなかなか複雑です。

=Arrayformula(IF($B2="",,LOOKUP(ROW(A2),IF($A$2:$A$30="",,ROW($A$2:$A$30)),$A$2:$A$30)))=$F$2

こんな感じ。

色付けするセルから見た式をつくり、範囲内で相対的に動くことを考慮して式を作る必要があるので、一部は絶対参照にする必要がありますし、一部は単体セルとして指定とする必要があります。

ここで、一気に配列を返してしまう先ほどの SCAN式をそのまま使ってもうまくいきません。

ちょっとしたアレンジが必要で、SCANの配列から 色付けするセルと相対的な位置の値を indexで取得した上で、$F$2と一致するかを判定する式にしています。

カスタム数式パターン2

=index(SCAN(,$A$2:$A$30, LAMBDA(pv,cv,IFS(cv<>"",cv,offset(cv,0,1)<>"",pv,true,))),ROW(A2)-1,)=$F$2

2行目からなので ROW(A2)-1としている

条件付き書式のカスタム数式はエラーが見つけづらいので、一度セルで動かして動作確認してからコピペすることをお勧めします。

また、条件付き書式でカスタム数式を使う時のポイントは、カレンダーの回で触れています。

困った表から直接QUERY関数で集計表を生成する

=LET(
  range,A1:D30,
  dept,
  SCAN(
    ,index(range,,1),
    LAMBDA(
      pv,cv,
      IFS(
        cv<>"",cv,
        offset(cv,0,1)<>"",pv,
        true,
      )
    )
  ),
  QUERY(
    {dept,CHOOSECOLS(range,3,4)},
    "select Col1,sum(Col2),sum(Col3)
      where Col1 is not null
      group by Col1"
  )
)

これも組み合わせるだけですね。

SCAN関数で生成した A列をクレンジングした配列を dept と置いて、CHOOSECOLSで取り出した C列、D列と { , } で横連結して配列化、Query関数で グループ集計しています。

Query関数でグループ集計すると、部署名の並びが変わってしまうのがネックです。

これを並びを変えを発生させずに処理するには、行番号を組み合わせた一工夫が必要なんですが、今回のテーマからはズレるし、結構長くなるのでQuery関数を特集する回にでも紹介したいと思います。

困った表と上手に付き合う

今回は「あるある」なセル結合されていたり、記述が先頭行のみといった、見やすいけど少し困った表からデータ集計する方法を紹介しました。

冒頭に書いた通り、人間側からの見やすい・作業しやすい表と、コンピュータ側からデータとして扱うのに適した表は別モノです。

集計の為に 人間側にストレスをかけては意味がありませんし、だからといって集計の際に手作業が発生しては元も子もないです。

最新関数を理解して、上手に困った表と付き合っていきましょう。

次回は、もう一つの困った表 手作業で作られたクロス表を扱う方法。いわゆるアンピボットに挑戦してみましょう。

これもネット上で結構紹介されてるネタなんで、少し複雑なケースにも触れたいと思います。

Googleスプレッドシート 困った表を集計する為の 加工数式（結合セルに負けない）

「あるある」な困った表と向き合う

見た目を優先すると、データとして扱いづらくなる

落としどころ（一番簡単な解決策） 背景色と同じ色の文字を使う

作業列（作業用シート）を使って集計

Q1. 上の画像のように縦結合のセルがある A列を F列のようにしたい。下にフィルコピーするとして、最初のF2セルには どんな式を入れればよいか？

A1. 縦結合の列のデータをバラす 式

作業列を組み合わせて集計する

作業列の式を 1つの式で記述する

Q2. 縦結合のセルがある A列を F列のようにしたい。F2セルにだけ式を入れて 実現することは可能か？

A2. 1つの式で 縦結合の列のデータをバラす

A2（別解）. 1つの式で 縦結合の列のデータをバラす

困った表から 直接 データ集計する

困った表から 直接SUMIFする

困った表を 条件付き書式で 可変色付けする

困った表から 直接QUERY関数で集計表を生成する

困った表と 上手に付き合う

いいなと思ったら応援しよう！