spreadsheet
関数

超基礎的な関数と、超基本的なspreadsheetの機能だけを使って、膨大なデータを見やすく分かりやすく加工する方法

こんばんは。遅れてごめんなさい……前厄の力を全て今週出し切りました(風邪)。
Livesense - 学 Advent Calendar 2017 の担当します。

大量のデータを、Googleのspreadsheetの機能と、関数を使って見やすくしたら
評判が良かったので、その際にした事をご紹介したいと思います。

非エンジニアなので、技術は一切なくても出来ます。超簡単です。

超基礎的な関数と、超基本的なspreadsheetの機能だけを使って、膨大なデータを見やすく分かりやすく加工する方法

完成図はこちら。
「入力欄」と記載されている所に文字を入れるだけで、それ以外のセル、もしくはグラフが自動的に切り替わります。

image.png

使ったもの

【超基礎的な関数】
* VLOOKUP
* RANK
* IF
以上!

【超基本的なspreadsheetの機能】
* グラフ
* 条件付き書式
以上!

何故やろうと思ったのか

見辛いから。

とにかくデータ多すぎて見辛い!だから見やすくしたい!いや、した!

そもそもどんなデータ

たとえば下記ようなシート。
これが5シート分、あったとします。

1219-2342-2.png

※諸事情で加工

中身云々は置いといて、これだけの文字量がシートいっぱいに並んでいたら抵抗ありますよね。

しかも、このデータをドンと渡されて「データのチェックお願いします」と言われた場合、
どこを見ないといけないのか、どう見ればいいのか…。
むむむ。困ったぞ。

問題は、

①読み取く量
②データを見る為の、アクション(シート移動、カーソル移動)

の2点にとにかく時間がかかることです。
(※人によりますが、データを理解する為に、コミュニケーションコストがかかる場合もあります。というか絶対かかる)

相手に色んな情報を整理してもらい、こちらの意図を汲み取ってもらう必要があります。
中々しんどいですね。

ではこの問題を解決するには何をすれば良いのか。
今回は下を目指して、問題を解消してみます。

①必要な情報だけが見えている、かつ分かりやすくなっている(データ読み解き時間の短縮)
②見る側のアクションが極限まで少ないこと(作業時間の短縮)

元データを用意する

データはサンプルなので、今回は駅名ごとの飲食店データを適当に作って、駅同士のデータ比較をします。
対象は「浅草駅」を主軸とし、比較対象を「川崎駅」にします。

【用意したデータ】
*客単価
*飲食店のジャンル
*客層

データの数値はデタラメです。
元データのシート名は「data1」と今回は設定します。

image.png

元データを集約するシートを作る

アクションは1つだけで完了するようにしたいで、入力欄だけ最初に用意しましょう。
配置などは好きなように考えていただくのが良いと思います。

image.png
赤い枠に文字をいれるだけ。

集約シートに、元データを引っ張るようにする

ではやっと使った方法を紹介します。
主にVLOOKUPを使い、+して出したいデータ方法ごとに、IF関数やRANK関数を合わせて元データを引っ張ってきます。

VLOOKUPの検索値は、最初に設定した「入力欄」のセルが対象です。
今回はそのセルを「B1」とします。

①そのまま持ってくるだけのデータはVLOOKUP
ただ引っ張ってくるだけではなく、出来るだけ同じグループ同士は近くに配置しておきましょう。

image.png

=VLOOKUP(B1,'data1'!A:X,3,0)

②比較したいデータはVLOOKUP+IF
まずは元データと比較データをVLOOKUP。※①の手順を参照。

image.png

その上でIF関数で比較対象を並べ、元データが比較データに対して優っているのか劣っているのかを出します。

image.png

=IF(D18>=E18,"高い","低い")

③順位を付けて比較したい場合はVLOOKUP+IF+RANK

比較データが1つではなく、複数を対象とする場合。 順位をつけてみます。

1つのシートに抽出したデータを見やすくする(グラフ)

抽出シートを対象に、グラフを準備します。
特に難しいことはしなくても、対象を設定しておけば、入力したワードに連動して自動的に切り替わるので大丈夫です。
どんなグラフが良いかは、そのデータに依存するので、割愛します。

必要なデータだけ見せたい(条件付き書式設定)

①不要なデータを見えなくする(条件付き書式設定)

条件に該当するものだけ出すor出さないの条件を、関数だけで再現が困難だった場合は、条件付き書式に頼るのが1番です。

ここは逆おじさんこと、ジョージジョースター思考でいきます。
逆に考えるんだ、「全部出しちゃえばいいさ」と…(その上でカラーでごまかすんだと…)

とりあえず、比較データはIF関数で良いにしろ悪いにしろ出してしまいます。

image.png

その上で、良い値が出た時だけ、文字を見せるようにします。
今回は良い値はIF関数で「高い」と出た場合とし、悪い数値は「低い」と返ったきた時になります。

なので、「条件付き書式設定」で「低い」の文字に該当した時はフォントカラーを白にします。
「書式設定のスタイル」にあるフォント設定です。
これだけで見た目上は、データが返ってこなかったときと同じようにみえます。
我ながらせこい。

image.png

これが新しく入力したデータに合わせて切り替わったら完成です。

どうだい、本当に簡単なものでしか構成されてないんだぜ。ビックリするだろ、私もビックリしてる。

単純作業ほど、積み重ねると無駄な作業になる

例えばデータ加工前は、1セットのデータの読み解き作業に平均5分かかっていたとして、
それが全1000セット分あったとし、データ確認に4人でこの作業にあたった場合を考えてみます。

1グループの平均作業時間×全データ÷作業人数 = 1人あたりの作業時間
5(分)×1000(セット)÷4(人)= 1250分

1人あたり20時間かかる計算になります。

これが、今回のシート加工によって5分から3分まで短縮できたとします。

3(分)×1000(セット)÷4(人)= 750分

1人あたり12時間になりました。

おおよそ半分くらいまで、作業時間を減らす事ができます。
1セットあたりの作業時間は大したことなくても、総作業時間を出すと、4人分の無駄な32時間を削れたのは大きいです。

難しい関数、難しい機能を使わなくても、組み合わせ次第で分かりやすく、かつ時間の短縮に繋がった例の紹介でしたฅʕ•̫͡•ʔฅ