初心者でも使える！Spreadsheetでデータを見える化したいときの神関数5選
Ateam Brides Inc. Advent Calendar 2020 23日目の記事です。
はじめに
これがnote初の投稿です。
Advent Calendarに参加していながら、私はエンジニアでもクリエイターでもありません。世間一般で言うところの、マーケティング・企画職にあたる立ち位置で仕事をしています。
（弊社では、ビジネスプロフェッショナル職という職域になります）
毎年12月になると、弊社のエンジニア・デザイナ達が、楽しそうにアドベントカレンダーに投稿している姿を見て、私も参加したいな～～！と思って、昨年から参加し始めました。わたしは12月に、なんちゃってエンジニアになる事を非常に楽しみにしているのです。
今年は、去年投稿した内容 をアップデートして、Glide Appsとスプレッドシートを駆使して、農業を営む父のため、野菜注文アプリを作る予定でした。わが社の若手エンジニアが、「スプレッドシートでアプリは作れる！」と豪語し、彼の提唱するところの「スプジニア」になる気満々だったのです。12月初旬までは。
休日に未曾有の忙しさに見舞われ、業務においても、GoogleよりもSalesforceと仲良くする必要性に迫られ、ちょっぴり執筆を後回しにしていたら、アプリを作るような余裕もなく、現在に至ります。言い訳ですね。
いつになく、心が凪いています。
前置き
さて、Glide Appsというのは、Googleスプレッドシートを「データベース」として利用してアプリを作る事の出来る、プログラミング知識を持たない自分にとって神様のようなツールです。(すべて先述の若手エンジニアからの受け売り)
実は、私は、ExcelとSpreadsheetが大好きです。Excelは神。
第二新卒で入社してから3年間、WEB広告運用を担当していた私は、毎日SpreadsheetやExcelでローデータを加工して加工して加工して、Csvで保存して管理画面へアップロードしたり、数値の傾向を見て施策を考えてPDCAを回したり、とにかく毎日のようにExcelとSpreadsheetを触っていました。
そのうち、もっと楽にサボr、いえ、効率化をしたくなり、関数を組みに組んで、色々なマシーンを作っていました。
そうやって試行錯誤をするなかで、なんやかんやあって（この辺はいずれ）、事業運営におけるデータの大切さを知り、データを揃える事の重要さを学びました。
だからこそGlideAppsを知ったときの、Googleスプレッドシートを「データベース」として利用してアプリを作る事の出来る、なんて謳い文句は、夢のような響きでした。
これは神が私にやれと言っている…！と奮い立ったわけですが、アドベントカレンダーぜ担当日の前日になり、そうだ、まずSpreadsheetでデータを整える所を書こう！と逃g、いえ、思い立ちました。
以上、言いわk、いえ、前置きです。
というわけで、本日はExcelとSpreadsheetと切磋琢磨してきた私が送る、実体験から厳選した
Spreadsheetでデータを見える化したいときの神関数5選
をお届けします。
データを整形・加工して集計しよう！
ちょうどよい例がありました。私が今戦っている、Salesforceの日付の加工、これ凄く面倒なんですよね。
Salesforceから、取引先IDに取引先名、取引開始日時と営業担当を紐づけたデータをダウンロードした、と仮定し、このデータを整形・加工して、いろいろ集計しようと思います。
日時順に並び替えたい！
Salesforceの日時のデータは、テキストで出力されています。これをまず日時形式に変換します。
①LEFT関数
まずは、テキスト形式の日時から、タイムゾーンを消します。私は、LEFT関数はそれはもうよく使うので、「ひだり」と打ったら、下記関数が出るように辞書登録しています。
" =LEFT(B2,FIND("/",B2)-1) "
↓
日本語に変換すると、
「B2のセルの中から、「／」を探して、その左の文字まで抜き出してね」
となります。今回は、日付とタイムゾーンの間に共通する「T」があったので、そこを探してもらい、その左までを抜き出しました。
スプレッドシートは、「YYYY/MM/DD」形式でも、「YYYY-MM-DD」形式でも日付とみなしてくれるので、取引開始日の抽出はこれでOKです。
ちなみに、「YYYY-MM-DD」を「YYYY/MM/DD」に替えたいときはSUBSTITUTE関数を使うとよいでしょう。
あまりに便利なので私は↓の関数を「ちかん」で辞書登録しています。
" =SUBSTITUTE(A1,"-","/") "
↓
日本語にすると、
「A1のセルにある、 - という文字を / に置き換えて」データの追加に対応したい！
日付の形式に置き換える事はできましたが、今後、データが増えていったとき、同じように毎度関数を打っていては割と大変です。
そんな時便利なのが、ARRAYFORMULA関数。Excelにはなく、スプレッドシートでしか使えない神関数です。
②ARRAYFORMULA関数
" =ARRAYFORMULA(LEFT(B2:B,FIND("/",B2:B)-1)) "「あれいふぉーみゅら」と読みます。私は間違えて「あらい」で辞書登録してしまい、「あらいふぉーみゅら関数が～～」と話していたら、後輩に鼻で笑われました。皆様はお気を付けください。
ARRAYFORMULAは配列式なので、数式を適応する範囲を定める必要があります。よって、先に使用したLEFT関数のセル指定を、C2から、C2:C に変更しました。
これで、データの入っている10行目まで、自動で数式が展開されます。
↓↓↓↓ A~C行にデータを足してもこの通り。
但し、この状態でフィルタをかけて並び替えると、範囲が狂ってしまい、数式の入っている行が下に下がります。
これを避けるため、私はよく2行目にARRAYFORMULA関数を入れ、2行目でフィルタをかける事が多いです。
数値のない行には、#VALUEが入っていますね。目障りです。これを消したいときは、IFERROR関数を使っています。
③IFERROR関数
" =IFERROR(C2,"〇〇") "
↓
日本語にすると、
「もしエラーが起きたら〇〇と置き替えて」
これで、取引開始日順にデータを並べる事ができました！
続いて、集計するときに使える関数をご紹介します。
営業担当者の担当会社数を出したい
こういう時、便利な関数は「UNIQUE関数」です。
④UNIQUE関数
これも、ARRAYFORMULA関数に続く、スプレッドシート限定関数です。
範囲を指定すると、その範囲で入っているデータの重複を削除して抽出してくれます。
" =UNIQUE(A1:A) "
↓
日本語で言うと
「A行に入っているデータを重複を除いて抽出して」
これで、カウントすべき営業担当者の選択肢を抽出する事ができました。
⑤COUNTIF関数
あとはExcelでもよくある、COUNTIF関数で、条件に当てはまるデータ数を数えれば、営業担当がもっている会社の数は簡単に集計できます。
"=COUNTIF(E3:E,"山田") "
↓
日本語で言うと、
「Hey、E列から山田と入力されているデータの個数を数えて」
ですが、この時、営業担当者の名前が表記ゆれを起こしていた場合、正確なカウントを行う事ができません。データ入力時、自由記述で、選択肢型ではないときに起きやすいですね。
スプレッドシートは便利ですが、あくまでも表計算用のツールです。
たまに、スプレッドシートに直接データを入力して、野良データベースを作る事例をよくみますが、このような「揺れによる集計ミス」が起こってしまう事を念頭に置きましょう。
正確な状況を把握したり、正しい見解を得るために、データを揃える、という事は、とても大事です。
このあたりのお話は、また次の記事でお伝え出来たらと思います。
おわりに
最後に注意点をお伝えしましたが、それはそれとして、Excelやスプレッドシートはとっても便利。可能性は無限大です。
思い起こせば若かりしあの頃。
会社の健康診断で、待ち時間用に渡されたタブレットに、
できるポケットExcel関数 基本&活用マスターブック Office
が入っていたのがすべての始まりでした。
これを読み進めているうちに、Excelってこんなこともできるんだ！と感動し、採血に呼ばれる声にも気づかず、早く業務で使いたい！！！と興奮しました。
その興奮のまま、私は業務効率化・PDCA改善に全力投球し、見事成功したのです。
そんな興奮と恩恵が、少しでも誰かのためになれば幸いです。
本日はこの辺で。
明日の Ateam Brides Inc. Advent Calendar 2020 24日目は、umako66さんです！！
私はumako66さんの文章の大ファンなので、とても楽しみです。
もうすぐアドベントカレンダーも終わりですね。
良いクリスマス、そして良い仕事納めを！