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

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

googlespreadsheet_flatten_topimage

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

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

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

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

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

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


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

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

FLATTEN()

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

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

一週間の食事のメニュー

表の値を一列に並べる

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

=FLATTEN(B2:H4)

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

=FLATTEN(B2:H4)


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

範囲[B2:H4]に含まれる全ての値がフラット化


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

2行目3行目と上から順番に追加されます

項目別に一列に並べる

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

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

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

2行目3行目と上から順番に追加されます


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

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

値の被りを解消

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

[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()を使いました。

今回は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)と食べた回数の積が表示されました。

カロリー(Kcal)と食べた回数の積


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

一週間に食べた料理と、総カロリーの表

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

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

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

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






【UdemyのおすすめGoogleスプレッドシート学習コース】

ネットショッピングをする感覚で講座を購入し、収録された動画を視聴するスタイルなので自分のペースで学習できるUdemy

そのUdemyにはGoogleスプレッドシートが学べる講座もあります。 Googleスプレッドシートの基本から一歩踏み込んだところまでを学べる評価の高い講座で、スプレッドシート以外の表計算ソフトでも使えるテクニックが習得できるので、コスパも高く実務ですぐに使えます。

【IT初心者でもわかる!Google スプレッドシート入門編】基本機能を活用したワンランク上の使い方、情報処理をマスター
Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験


BTOパソコンの草分け的ブランドFRONTIER(フロンティア)

3DCG/動画編集/そしてゲームプレイに。
用途に合わせてCPUやGPUなどパーツを自由に選べて、コスパにもこだわる方にオススメ。

半期決算売り尽くしセール!開催中\RTX 30シリーズ搭載PCが13万円台~!/8/6(金)15時まで

特典付き&メモリ無償アップが大好評。

Core i5-10400F + RTX 3060 Ti搭載モデルが[15万円台]
Core i9-11900F + RTX 3080 Ti搭載ハイエンドモデルが[33万円台]

見逃し厳禁!SALE製品は台数に限りがあるので売り切れる前に是非

\MSIとFRONTIERがコラボした特別仕様のゲーミングPCが発売開始/
人気のMSI製マザーボード、 グラフィックスカード、水冷CPUクーラー、電源をMSI製ケースに搭載した 特別仕様のゲーミングPCが発売中です

>>【FRONTIER】


【デジハリONLINEの 基礎動画 + AdobeCC一年ライセンス のお得プラン】

デジタルハリウッドのオンラインCGスクール「デジハリONLINE」が提供する、60時間の基礎動画とAdobe creative cloudの一年版ライセンスがパッケージされた、コスパ最強の講座プラン

「Adobeマスター講座」

受講ノルマは一切無し、Adobe製品の利用を目的に契約してもOK!

>>デジハリONLINE講座申込

▶︎基礎動画教材付きAdobe Creative Cloudを年額39,980円(税込)のお得な価格で使用する方法


Twitter Facebook Pocket LINE はてブ

おすすめの関連記事
コチラの記事も読まれています
あわせてよく読まれている記事