はじめに

皆さん、あけましておめでとうございます。
本年も頑張っていろいろ書いていきますので、よろしくおねがいします。

では早速本編をどうぞ

いつもの

※ JC：鬼滅の刃(吾峠呼世晴) 9巻 p59〜p62 より、雑コラを作成させていただきました。

まず用語集

名称(和)	名称(英)	説明
ワークブック	WorkBook	Googleドライブに保存される１ファイル
スプレッドシート GoogleSpreadSheet	SpreadSheet	行と列による表計算が可能な機能
タブ	Tab	ワークブックに対して複数のスプレッドシートを管理する際に用いる機能
セル	Cell	スプレッドシート上にある1つの入力枠
ファンクション	Function	セル上で実行可能なプログラム
アドオン	AddOn	GSSで追加可能な拡張機能
スクリプト GoogleAppScript(GAS)	Script	GSS上で実行可能なJavaScript likeな言語

GSSの制限について

参考: Sheets Tips - Google Sheets Tips and Tricks | G Suite Tips

対象	範囲	条件
作成・編集可能なセル数	1Spreadsheetあたり	500万セル
一度の編集(コピペ)可能な行数	1Spreadsheetあたり	40,000行
最大作成列数	1Spreadsheetあたり	18,278列
最大タブ数	1WorkBookあたり	200タブ
GOOGLEFINANCE Function	1Spreadsheetあたり	1,000セル
IMPORTRANGE Function	ワークブック間の1Functionあたり	50セル
IMPORTDATA IMPORTHTML IMPORTFEED IMPORTXML	1Spreadsheetあたり	50セル
文字列数	1セルあたり	50,000文字

リスト作成

1. ヘッダーを決めよう

リストにおいてヘッダーはとても重要！
どんな情報を見せてどんな情報を見せないかを決める！！

f:id:DTM3110:20191229220403p:plain

2. Index番号を振ろう

普通に番号を一つづつ入れていってもいいけど、
コピペしたり、削除したときに間が飛んでしまったりするので、
ARRAYFORMULA関数とROW関数を使って連番を勝手に振るようにする。

f:id:DTM3110:20191229221049p:plain

【A3】Index番号

=ARRAYFORMULA(
  IFS(
    ROW(A3:A)=3,
      "#",
    B3:B="",
      "",
    ROW(B3:B) > 3,
      ROW(A3:A)-3
  )
)

3. データを取得してみよう

参考: SpreadSheetでスクレイピング。Importxml他、便利な関数9+1 - Qiita

上記記事をいろいろ参考にそれぞれの情報を取得していきましょう！！

①対象のURLを列挙(今回はQiitaのAdventCalendarを使っています。)してリストを作成
※ ~~リスト作成のためにリストを作成とは…~~
【J1J】: 年数
【K1:K】: URL

【J1】：どうせなら年数はURLから正規表現で取得してみる

=VALUE(
  REGEXEXTRACT(
    K1,
    "[0-9]{4}"
  )
)

コメント:
後々使うことを考えて、正規表現で取得した文字列はVALUE関数を使って数値に変換しておく

②対象のタイトルを取得しよう
f:id:DTM3110:20191230081621p:plain

【I1:I】タイトル

【I1】のとき

=IMPORTXML(
  K1,
  "//*[@class='adventCalendarJumbotron_heading']"
)

コメント:
XPATHはChromeの開発者ツールを使えば簡単に取得(コピー)できます。
が
"が入ったり、Body要素から参照したりして無駄が多いので、取得したい要素にClassやIDが入っている場合は上記のように直接参照したほうが楽でしょう。

③セレクトボックスを設定しようセレクトボックスに指定したいところで、右クリック→[データの入力規則]→条件: リストを範囲で指定 を選択し、上記で取得したタイトルを指定
無効なデータの場合：入力を拒否
↑にしておくと、後々使う関数でわざわざIFERRORを入れなくて良いので指定しておくと良いでしょう。

f:id:DTM3110:20191230085403p:plain

④日付を生成しよう AdventCalendarは毎年12月のブログのお祭りなので、基本的にYY年12月dd日となります。今回はセレクトボックスで選択された対象から年のデータを取ってきます

【B4】

=ARRAYFORMULA(
  IF(
    C4:C="",
      "",
      TEXT(
        DATE(
          VLOOKUP($B$2,I:J, 2,False),
          12,
          ROW(B4:B)-3
        ),
        "YY年MM月dd日"
      )
  )
)

コメント:
Cの列に文字が入っていなかった場合は空にする。
※C列はARRAYFORMULAでデータを入れて、該当しないセルには""を入れるので、ISBLANKが使えない文字列がある場合、年、月、日の情報をDATE関数を用いて日付型のデータを作成する。
セレクトボックスで選ばれたタイトルをもとにJ列の年の値をVLOOKUP関数を使って取得する。
月の値は12で固定する。
日の値はROW関数を用いて取得する。

⑤作者情報を取得する(Icon編)

【C4】

=ARRAYFORMULA(
  IMAGE(
    IMPORTXML(
      VLOOKUP($B$2, I:K, 3,FALSE),
      "//*[@class='adventCalendarCalendar_authorIcon']/@src"
    )
  )
)

コメント:
VLOOKUP関数を用いて、選択されたタイトルから対象のURLを取得する。
IMPORTXML関数を用いて対象のimgタグのsrc要素を取得する。
※要素内のデータを参照する場合は@srcのように指定する。
IMAGE関数を使ってURLから画像を表示する。
※IMAGE関数は非配列関数なのでARRAYFORMULA関数を使って配列関数の振る舞いを再現する。

⑥作者情報を取得する(ユーザ名編)

【D4】

=IMPORTXML(
  VLOOKUP($B$2, I:K, 3,FALSE),
  "//*[@class='adventCalendarCalendar_authorIcon']/@alt"
)

コメント:
残念ながら、表示されているユーザ名は下記のとおり、Imgタグと一緒に入ってしまっているので、そのまま取得すると不要なデータが入ってしまうため、今回はimg要素のaltを取得しています。

<a href="/Z-me">
    <img
      alt="Z-me" 
      class="adventCalendarCalendar_authorIcon"
      src="https://qiita-user-profile-images.imgix.net/{{略}}"
      width="18"
      height="18"
    />
    &nbsp;Z-me
</a>

⑦記事のリンクを取得する

【F4】

=IMPORTXML(
  VLOOKUP($B$2, I:K, 3,FALSE),
  "//*[@class='adventCalendarCalendar_comment']/a/@href"
)

⑧記事のタイトルを取得する

【E4】

=ARRAYFORMULA(
  IF(
    ISBLANK(F4:F),
      "",
      HYPERLINK(F4:F,
        IMPORTXML(
          VLOOKUP($B$2, I:K, 3,FALSE),
          "//*[@class='adventCalendarCalendar_comment']"
        )
      )
  )
)

コメント:
例によって、対象の文字列を取得する。
先程取得したリンクを使って、HYPERLINK関数により、文字列にリンクを埋め込む。
HYPERLINK関数も非配列関数なので、ARRAYFORMULA関数を使って配列関数の振る舞いを再現する。

4. チェックボックスを入れてみよう

これはとても簡単!

チェックボックスを入れたいところを選択
ツールバーの[挿入]を選択
[チェックボックス]を選択

5. 自動表示させてみよう

① チェックされたらタイトルのリンクを削除させてみる

【E4】

=ARRAYFORMULA(
  IFS(
    ISBLANK(F4:F), "",
    G4:G, IMPORTXML(VLOOKUP($B$2, I:K, 3,FALSE),"//*[@class='adventCalendarCalendar_comment']"),
    NOT(G4:G), HYPERLINK(F4:F, IMPORTXML(VLOOKUP($B$2, I:K, 3,FALSE), "//*[@class='adventCalendarCalendar_comment']"))
  )
)

コメント:
チェックボックスはBoolean値をそのまま格納しているので G4:G のように指定するだけで、IF文の判定に使える。

②チェックされたら書式が変わるようにしてみる
該当の範囲を選択し、[書式ルール]から[カスタム数式]を選択し、チェックボックスの範囲を選択する。
※ このときGの前に$をつけないと、指定された一番左の列しか書式が反映されないので注意
あとはチェックされたときになってほしい書式を設定

f:id:DTM3110:20191230111426p:plain

6. 表示をきれいにしてみよう

①表示しなくてもいい列を非表示にする
②交互の背景を設定する
リスト全体を選択し、[表示形式]から[交互の背景色]を選択
好きな感じのフォーマットを選んだり、自分で色を選んで見る
f:id:DTM3110:20191230113722p:plain

③行固定する
固定したい行まで移動し、ツールバーの[表示]を選択、[固定]を選択し、[現在の行まで固定]を選択

番外編. ミニグラフをセルに表示してみよう

いいね数の時間推移グラフを表示してみる
※Qiita記事以外はいいねがないので今回は非表示

①いいね数を取得します。

【H4:H】

【H4】のとき

=IF(
  F4:F="","",
  IFERROR(
    IMPORTXML(
      F4,
      "//*[@class='it-Footer_likeCount']"
    ),
    ""
  )
)

コメント:
IMPORTXMLはARRAYFORMULA関数が使えないので、残念ながらそれぞれのセルにコピペする

②SPARKLINE関数を使ってセル内に結果を出力

=SPARKLINE(
  H4:H,
  {
    "charttype","line";
    "color","deepskyblue";
    "empty","ignore";
    "linewidth",5
  }
)

"charttype","line"
- グラフの種類をLineChartに指定
"color","deepskyblue"
- 色をdeepskyblueに指定
"empty","ignore"
- 空のセルは無視する
"linewidth",5
- 先の太さを指定

f:id:DTM3110:20191230133559p:plain

サンプル

グラフ作成

沢山の種類のグラフがあるので、サンプル中心に紹介します。

時系列データサンプル

日付	値
12/01	59
12/02	54
︙	︙
12/10	98

※ 以下のサンプルグラフはランダム生成されたデータを利用しています(一部を除く)

Line Chart

直線

曲線

Area Chart

Staircase Chart

Bar Chart

Time Scale Chart

カテゴリ別データ

カテゴリ	値
知識	4
度胸	5
器用さ	4
優しさ	5
魅力	5

Pie Chart

Donuts Chart

Rader Chart

Horizontal Bar Chart

その他変わり種

Map Chart

国名	人口
中国	1,411,415
インド	1,324,171
アメリカ	322,180
インドネシア	261,115
ブラジル	207,653
日本	127,749
︙	︙

組織図

項目	親項目	ツールチップ
必須		生きていくには必要
朝食	必須	しっかり食べよう
昼食	必須	しっかり食べよう
夕食	必須	遅くならないうちに食べよう
シリアル	朝食
ヨーグルト	朝食
コンビニ弁当	夕食	脂っこいものは気をつけろ
おにぎり	昼食

データ集計

1. 関数を使って複雑な計算をしよう

①データ(範囲)から和を算出 =SUM({範囲})

②データ(範囲)から平均を算出 =AVERAGE({範囲})

③データ(範囲)から相乗平均を算出 =GEOMEAN({範囲})

④データ(範囲)から中央値を算出 =MEDIAN({範囲})

データ(範囲)から標準偏差を算出
=STDEV({範囲})

2. T検定・Z検定をやってみよう

【A:A】データA
【B:B】データB

①標準偏差を求める

【D1】

=STDEV(A:B)

②Z検定をしてp値を求める

【F2】対象

【D2】

=ZTEST(
  A:B,
  F2,
  D1
)

コメント:
データ範囲と、標準偏差と、対象となる数値を指定

③T検定をしてp値を求める

【D3】

=TTEST(
  A:A,
  B:B,
  2,
  1
)

サンプル

アドオンを使ってみよう

プロジェクト管理

gsuite.google.com

こちらのアドオンをSpreadSheetに追加してあげるだけで、いろいろかけるスグレモノ

f:id:DTM3110:20191230172306p:plain

WBS(Work Breakdown Structure)を作成可能
↑で作成したタスクに期限と進捗を入力することでガントチャートが自動生成

詳しくは↓こちら

いろんな図を描いてみよう

Lucidchart Diagrams

外部の描画ツールを使っている
- 上記のツールを使うとディレクトリ形式で保存管理ができる
UMLやシステム図、オフィスのレイアウトなど多岐にわたって図を書くことができる

詳しくは↓こちら

使えるFunction リスト

Function名	使い方	詳細
ARRAYFOMULA	ARRAYFORMULA(array_formula)	複数のデータを配列として扱う
GOOGLETRANSLATE	GOOGLETRANSLATE(text, [source_language], [target_language])	Google翻訳機能を使って翻訳結果を表示
IMAGE	IMAGE(url, [mode], [height], [width])	URLから画像を表示
QUERY	QUERY(data, query, [headers])	SQLっぽく任意のデータを整形し出力
SPARKLINE	SPARKLINE(data, [options])	セル内に小さいグラフを表示
IMPORTDATA	IMPORTDATA(url)	URLからCSVやTSVデータを取得
IMPORTHTML	IMPORTHTML(url, query, index)	HTMLからListやTableの情報を取得
IMPORTXML	IMPORTXML(url, xpath_query)	HTMLからXMLデータを取得
IMPORTRANGE	IMPORTRANGE(spreadsheet_url, range_string)	他のSpreadsheetからデータを取得
REGEXEXTRACT	REGEXEXTRACT(text, regular_expression)	任意の文字列から正規表現に一致する部分を取得
REGEXMATCH	REGEXMATCH(text, regular_expression)	任意の文字列が正規表現に一致するかどうかを判別
REGEXREPLACE	REGEXREPLACE(text, regular_expression, replacement)	任意の文字列を正規表現により置換
SUBSTITUTE	SUBSTITUTE(search_txt, search_for, replace_with, [occurrence_number])	文字列の置換
TEXT	TEXT(number, format)	任意のフォーマット形式の文字列に変換
TRIM	TRIM(text)	文字列前後の半角スペースを削除
SPLIT	SPLIT(text, delimiter, [splitByEach], [removeEmptyTxt])	文字列を任意のセパレーターで分割
FILTER	FILTER(range, condition1, [condition2])	該当範囲内にあるデータをフィルタリング
SORT	SORT(range, col, asc, [col2], [asc2])	該当範囲内にあるデータをソート
UNIQUE	UNIQUE(range)	該当範囲内にある重複データを削除して表示
HLOOKUP	HLOOKUP(search_key, range, index, [is_sorted])	任意範囲における横方向の検索
VLOOKUP	VLOOKUP(search_key, range, index, [is_sorted])	任意範囲における縦方向の検索
INDEX	INDEX(reference, [row], [column])	該当Cellにあるデータを取得
ROW	ROW([cell_reference]	該当Cellの行数を取得
ISBLANK	ISBLANK(value)	対象が空のCellであるかを判別
RAND	RAND()	0〜1の範囲の数値の乱数を取得
RANDBETWEEN	RANDBETWEEN(low, high)	任意の範囲の数値の乱数を取得
ROUND	ROUND(value, [places])	該当数値の該当桁数四捨五入値を取得
ROUNDDOWN	ROUNDDOWN(value, [places])	該当数値の該当桁数の切り捨て値取得
ROUNDUP	ROUNDUP(value, [places])	該当数値の該当桁数の切り上げ値取得
COUNT	COUNT(value1, [value2, ...])	該当範囲内の数値の入っているセルの数を取得
COUNTA	COUNTA(value1, [value2, ...])	該当範囲内のデータがないっているセルの数を取得
ZTEST	ZTEST(data, value, [standard_deviation])	Z検定によるP値を取得
AND	AND(logical_1, [logical_2, ...])	論理式における論理積
OR	OR(logical_1, [logical_2, …])	論理式における論理和
NOT	NOT(logical)	論理式における否定
IFERROR	IFERROR(value, [value_if_error])	与えられた関数がエラー終了した場合に任意のデータを出力
IFS	IFS(condition1, value1, [condition2, value2], …)	複数の論理式に応じたそれぞれの出力を指定
SWITCH	SWITCH(exp, case, val, [default or case2, val2], …)	複数の論理式に応じたそれぞれの出力を指定