24
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Livesense - 学Advent Calendar 2017

Day 19

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

Last updated at Posted at 2017-12-22

こんばんは。遅れてごめんなさい……前厄の力を全て今週出し切りました(風邪)。
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時間を削れたのは大きいです。

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

24
7
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
24
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?