Googleスプレッドシート 表の値を一列に並べ替えて再構築した表を作成[FLATTEN]

Googleスプレッドシートで表を作成するのは便利ではあるのですが、値をキレイに並べられただけで満足していませんか？

「必要事項を規則に従って入力できたけど、この表から何が分かるの？」

セルの1枠1枠に値を入力して表を完成させても、それだけだと情報としては不十分。一つ一つの値は大事な情報なのに、入力されただけでは唯の文字列でしかありません。大事な値を使って作成した表を活かすには、値の並べ方・扱い方を再構築して情報を“見える化”するのが重要。

「とは言え、せっかく入力した値を並べ替えるのは…」

Googleスプレッドシートの関数を使えば表の値を簡単に並べ替えられます。つまり表の値を再構築して新たな切り口の表を作成するのも容易。ただ単に値を並べただけの表では気づけなかった事が、再構築によって新たに発見できるかもしれません。

この記事は、Googleスプレッドシートで作成した表の値を関数[FLATTEN]で並べ替えて、情報を“見える化”する方法を紹介します。

関数[FLATTEN]で表の値を一列に並べ替える

Googleスプレッドシートで複数のセルの値を、1つの列に並べる関数

FLATTEN()

引数に表の範囲を指定すると、範囲に含まれる全ての値が1列に並びます。

一週間の食事のメニューをまとめた表を作りました。この状態では、ただ単に朝昼晩、曜日ごとに料理名を並べただけですが、コレを再構築して別の情報を表してみたいと思います。

表の値を一列に並べる

空白のセルを選択し、FLATTEN()を使った数式を入力します。

=FLATTEN(B2:H4)

FLATTENの引数に　FLATTEN(範囲)　と範囲を指定すると、範囲内に含まれる全ての値が順番に追加されます。値が入力された範囲[B2:H4]を指定しました。

指定した範囲[B2:H4]に含まれる全ての値が一列に並びました。

値は、行ごとに左から右へ、そして2行目3行目と上から順番に追加されます。

項目別に一列に並べる

FLATTENの引数には　FLATTEN(範囲)　と値が入力された範囲を指定できますが、参照する範囲以外も指定できます。

=FLATTEN("■朝食",B2:H2,"■昼食",B3:H3,"■晩食",B4:H4)

参照する範囲を行ごとに分け、行の項目「■朝食」「■昼食」「■晩食」の文字列を指定しました。

行ごとに項目名と料理名が順番に一列に並びました。

値の被りを解消

指定した範囲の全ての値を一列に追加・並べることができましたが、同じ値だったり空白があったりと、データ表として好ましくありません。値の被りや空白を無くして見やすくしたいと思います。

[UNIQUE]で値の被りを無くす

関数UNIQUE()を使うと重複する行を破棄する事ができます。

=UNIQUE(FLATTEN(B2:H4))

引数にFLATTEN()の数式を指定しました。

関数UNIQUE()で重複した値が削除されたので、同じ料理名は無く一つずつしか追加されません。

空白の行を無くす

関数UNIQUE()で重複する値は削除できましたが、空白の値は残ったままです。

関数FILTER()を使用すれば空白をの行を削除する事ができますが、今回は別の関数SORT()を使って空白を無くします。

=SORT(UNIQUE(FLATTEN(B2:H4)))

引数に関数UNIQUE()の重複の値を削除した数式を指定しました。

見た目上、空白の行が無くなりました。正しくは、ソートの結果空白の行が一番下に移動しただけですが、データの利用には問題なく簡単なのでSORT()を使いました。

重複や空白の行を消した結果、一週間に食べた料理の数が見やすく確認できました。

■関数FILTER()の使い方

関数FILTER()を使用して空白を削除する事もできますが、範囲を指定する列が余分に必要になるので関数SORT()を使いました。

=FILTER(D8:D19, NOT(ISBLANK(D8:D19)))

FILTER()の引数にはフィルター対象の範囲を指定します。ISBLANK()で空白を確認でき、NOT(ISBLANK())で空白を排除できます。

指定する範囲の列が必要になるので、今回はSORT()を使いました。

同じ値が幾つ存在するか

関数UNIQUE()で重複する値は削除できました。今度は同じ料理を何回食べたのか調べたいと思います。

関数SUMPRODUCT()で指定した配列または範囲に含まれる同じ要素・値を合計した数を取得します。

=SUMPRODUCT(FLATTEN($B$2:$H$4)=E8)

SUMPRODUCT()の引数にFLATTEN()の数式を指定、そして料理名の参照セルを指定しました。

料理名毎に食べた回数が表示されました。

別の表と掛わせた新たな情報

一つの表だけでは活かせないデータでも、他の表のデータと組み合わせる事で有意義な情報が取得できる事もあります。

参考：タニタ　摂取カロリー早見表
https://www.tanita.co.jp/content/calorism/table/index2.html

「タニタの摂取カロリー早見表」を参考にしてカロリー表を作成しました。先の表と、このカロリー表を組み合わせてみます。

検索の関数VLOOKUP()を使って料理ごとのカロリーを取得。
MULTIPLY(因数1, 因数2)でカロリー(Kcal)と食べた回数の積を計算します。

=MULTIPLY(VLOOKUP(E8, $J$2:$K$19, 2, FALSE),F8)

VLOOKUP()の引数には、料理名の参照セル , カロリー表の範囲 , 結果範囲の列(カロリー) を指定しました。カロリー表の範囲で料理名を検索。ヒットしたら同じ行の2列目の値が返されます。次にMULTIPLY()で返された値(カロリー)と食べた回数の積を計算します。

カロリー表で取得したカロリー(Kcal)と食べた回数の積が表示されました。

総カロリーの合計を取得。一週間に食べた料理と、総カロリーの表が完成しました。

まとめ：再構築で大事な表を最大限の活用

Googleスプレッドシートで作成した表の値を関数[FLATTEN]で並べ替えて、情報を“見える化”する方法を紹介しました。

苦労して作成したデータ表も活用できなければ唯単に値を並べただけの一覧でしかありません。表の見た目をキレイに整えるのは視認性の観点でも大切ですが、データを活かす事はそれ以上に重要です。

この記事で紹介した関数[FLATTEN]を含め、さまざまな関数を活用することで、表の価値を何倍にも高められる可能性があります。時間をかけ入力したデータを活かす為にも、今回紹介した内容を参考にしてみてはどうでしょうか？

𝕏 ( 旧Twitter ) Facebook Pocket LINE はてブ