面倒なデータ入力を自動で片付ける！
【現役エンジニアが教えるスプレッドシート #1】

はじめまして、トンガルマンのおしゃべりエンジニアのカズさんです！

肩書きはエンジニアなのですが、実はプログラミング言語に負けないくらいスプレッドシートの関数に詳しいので、誰かが資料作成で困っているときは頼まれてなくても首を突っ込むことで社内で恐れられています。

スプレッドシートは誰もが知っている便利ツールなのに、苦手意識が強い方や、出来てるつもりでも意外と使いこなせてない人がとっても多いです ← これ本当！

なので僕が実際にこれまで調べてきた情報たちを共有すれば、きっと世界が少し平和になるだろうという思いから、今回から記事シリーズとして紹介させていただくこととなりました！

面倒な仕事は関数にやらせよう

まず始めにご紹介するのは、僕のところに届いたある質問です…

8,000日分の情報入力とはこれまた大変なタスクです… (絶対に手伝いたくないです)
こういった情報入力の手間を省くために関数をつかうのはとっても賢いです。

日付の入力に限らず、このような大きなシートを編集するときに全てを手作業でやろうとすると

• 同じような入力作業の繰り返し
• 上から下まで (または左から右まで) たくさんスクロールしながらの作業

などが発生し、手間も時間もかかりますし、その分だけ誤入力も起こりやすくなります。また、一度入力が出来たとしても、あとから修正や編集が必要になった時になどに作業のやり直しが発生するかも知れません。

こんな単調で面倒な作業、繰り返しやるなんて、なんかバカっぽいですよね。

そこで今回は、日付の入力と、大量のデータをシートにまとめる際に役立つ４つの関数をご紹介したいと思います！

ROW：コピペでいい感じに番号振ってくれる奴

例えば1から100までの数字を順番に入力したい場合、スプレッドシートやエクセルの「オートフィル」という機能を使うことができます。

1~2まで手で入力して、オートフィルで続きを入力

はじめ2行だけ値を入力したら、あとはこのようにセル右下の四角をドラッグするだけで自動で数値などを入力してくれます。
とても便利な機能なのですが、用途によっては以下のような弱点もあります。

• 1,000行や10,000行など大量に入力したい場合に不便
• 入力した後に列の削除や追加をすると連番じゃなくなる
  (場合によっては勝手に振り直されないというメリットにもなる)

ここでこのような問題を解消すべく、ROW関数の出番です。

ROW関数を真面目っぽく説明すると「セルの参照を渡すと、そのセルが何行目かを返してくれる」関数です。(列は関係ありません)
例えば=ROW(C12)と入力すると12と表示されていますね。

「C12セルは12番目のセルです」という当たり前のことを返してくれる

これだけです。「だからなんなの？」という感じですね。その感想であってます。
ではいつROW関数が本力を発揮するかと言いますと

• コピペで大量に情報入力をする時
• 行番号を使って別の計算をする時

といった場合です。
例えば1〜100の数字を入力したあとに、「3の倍数または3を含む数」の横にだけ「トン！」という文字を表示したいとしましょう。よくあるシチュエーションですね。そんな時にROW関数は使えます。

3の倍数または3を含む数の横にだけ「トン！」を表示する例

使ってる関数によらず、セルへの参照を含む式を隣にコピペした時に、セルへの参照も自動的にひとつズレていることに注目してください。

コピー＆ペーストしたROW()の中身がA2,A3とインデントされている

この「勝手にズレる」のが、わざわざROWを使って行を取得する理由のひとつです。1などの数値をセルに直接入力してからコピペしても、同じような自動変換は行われません。
(行ではなく列を返す関数COLUMNもよく使われます)

ARRAYFORMULA：コピペすら勝手にやってくれる奴

ROWの使い方はとても簡単なので、上記のような1〜100という程度の作業量であれば「関数勉強するくらいなら手でやるわ」と思った方もいるかもしれません。
でも例えばこれが「1〜10,000」だったらどうでしょう？データが１列ではなく複数行あるような大きなテーブルだったら？それでも手入力でやりますか？YESと答えたトンチンカンな方は勝手にどうぞ。

そうじゃないお利口な方に紹介したいのがこちらARRAYFORMULA関数です。読み方は「アレイフォーミュラ」です。名前からしてとってもギークですね。
ARRAYFORMULA関数の機能を正確に説明するのは難しのですが、ざっくり言うと上の例のような「関数をコピペして繰り返し入力するのを手伝ってくれる」やつです。

例えばこちらご覧ください。

ROWを使った入力サンプル

今からこのA1に入力した関数をA10000までの1万セル分入力するときに、実際に1万セル分も画面をスクロールしてペーストするのはとても面倒です。処理も重くなるでしょう。さらにあとから修正が必要になったら…考えたくないですね。

この面倒な作業をいい感じにやってくれるのがこのARRAYFORMULAです。こちらをご覧ください。

A1にARRAYFORMULA関数を使用

A1のARRAYFORMULA()がA10000までのセルに自動で値を入力している

これはA1からA10000までのセルにX日目と表示している例ですが、実際に僕が式を入力をしたのはA1セルのみです。
このようにARRAYFORMULAは入力された関数と同じ計算を複数のセルに対して自動で行い、実際にセルに表示してくれるのです！

また自動で計算と表示してくれるだけでなく、同じ量だけ関数をコピペして計算しちゃうよりも処理が軽くなる傾向があるようです。とっても優秀ですね！
なんだかハイテクでいかにも「自動化うぇい！」て感じの関数ですが、使い方は実はとても簡単です。

1. A1のように単一のセルを参照していたところをA1:A10000のように範囲の参照に変更する
2. 関数全体をARRAYFORMULA()で囲む

これだけです。最初のセルの関数を修正するだけで、続きのセルに何も入力しなくても「コピペしたらこうなるでしょ」という値を勝手に表示してくれるんです。
ひとつだけ注意が必要なのは、ARRAYFORMULAを入力した関数本体以外のセル
(今回の例ではA2からA10000のセル)
には値や関数を入力してはいけないという点です。

A3に入力した値が原因で、A1のARRAYFORMULA()がエラーを返している

特に「元の関数をコピペして正しく動くこことをチェックした後に、ARRAYFORMULAに変更する」という場合は気を付けてください！

DATE : そっちのデートじゃない奴

dateという単語から何を連想しましたか？ロマンチックな方のデートを想像した人がいるかもしれませんが、実はそっちデートも英語ではdateと綴ります。もとは日付を表す言葉だったdateが、「あの人と会う日」という意味を込めて、ロマンチックな方のデートの意味で使われるようになったんだと思います。

スプレッドシートに登場するDATE関数は、残念ながらロマンチックじゃない方のdate、つまり日付を扱う時に使われる関数です。

窓の外を見ながらロマンチックな思いにふけるカズさん

さて、単に行番号を振ったり数値計算するだけであれば、先に紹介したROWやARRAYFORMULAで足りる場合が多いのですが、日付を表示するにはもう一工夫が必要です。

スプレッドシートに慣れてる方であれば、日付を入力するときに最初に思い浮かぶのはシンプルに「2022/2/22」のように直接キーボードで入力する方法だと思います。

表示が右揃えになっている = 文字列ではなく日付として認識されてる証拠

スプレッドシートはとても賢いので、これだけで自動的に「これは単なる文字じゃなくて日付だね！」と理解してくれます。その証拠に一つだけ日付を入力したら、あとはオートフィルで次々と日付を入力することができます。もちろん月や年をまたいでも、自動で正しい日付を表示してくれます。

1週間や1ヶ月程度の入力であればたったこれだけでできるスプレッドシートは優秀ですね！

ここで「オートフィルで出来るんかい…！さっきまでの話なんやねん…」とツッコミを入れちゃうトーシロな方はいないと思いますが、念のためにオートフィルする時にも少しデメリットがあるのでいくつか挙げておきますね。

1. 10,000行など大量に入力するのはオートフィルでも大変
2. かといってコピペは出来ない

オートフィルだと１日ずつ入力されるが、コピペだと同じ日付になってしまう

こういったデメリットを解消したのがDATE関数です。

DATE関数の使い方はとてもシンプルで、画像のように「年、月、日」の順番で数字を入れるだけで、あとは直接入力した時と同じように日付を表示してくれます。

年月日を入力すると、日付として表示してくれる

ここまでの説明だと「普通に入力するのと同じでは…？」と思うかもしれませんが、DATE関数のちょっと便利なところのひとつは「年月日の指定に関数やセルの参照が使える」という点です。

「またわかりにくい説明しやがって…」と怒っちゃいそうな方はこちらをご覧いただければ意味がわかると思います。

日付にROW(A1)のような関数を使うことができる

そしてDATE関数が便利な点はもう一つあります。

例えばDATE(2022,3,32)のように実際にはあり得ない日付を指定したとしても「3/31の次の日は4月でしょ？」と勝手に続きの月と日付を表してくれるところです。

「3月の32日目 = 4/1」と自動で解釈してくれる

これの何が便利なのか、もう感のいい方なら気づいてると思います。そうです、ROWやARRAYFORMULAによる自動入力ととっても相性がいいんです！

例えば１ヶ月分の日付だけではなく、さらに続けてたくさんの入力をしたいときには、こうです！

元の質問である「8000行分の日付入力」の解決例がこちら

壮観ですね、もう何も言うことはありません
(この記事長くない？て自分でも思い始めている人)。

「できる！」と思わせるための、もうひと工夫

ROWやCOLUMNによる日付の入力が便利なのは、データが大量にあるときだけではありません。例えばカレンダーやガントチャートなどをスプレッドシートで作る際にも、とっても役に立ちます。

例えば毎月ごとのシートを作成し、データを集計する場合を考えてみましょう。A1セルに年、B1セルに月を入力し、あとは日付をROWで求めてDATE関数に渡します。

どこにコピペをした時も常に年はA1セルを、月はB1セルを参照するように
「$マーク」を付けて「絶対参照」している

今回は見やすさのためにコピペで月末までの日付を入力しましたが、もちろんARRAYFORMULAを使うこともできます。

これだけでもひと月分の日付を自動で表示することには成功していますね。しかしこのままでは、とっても変なカレンダーに仕上がっていることにお気づきでしょうか？

万が一気づかない人がいたら、ちょっと色々と欠けていると思います。もう一度よ〜く見てください…

市販のカレンダーではあり得ない表記です…

そうです！日付の表示形式がとっても不細工です！

3月の専用のシートを作成して、1行目に「2022,3」と入力されているのに、さらに各日付のセルにまで「2022/3/1」という具合に、年や月が表示されてしまっています！これは大変です。

「別に気にならないのだが…」「入力ができればOKじゃね？」という声を漏らしたブサイクなハートの持ち主は、この記事をそっと閉じしてください。お疲れ様でした。

引き続き記事をご覧のこちら側の皆様、ようこそ。

TEXT：数値や日付をおしゃれに整えてくれる奴

数値や日付をおしゃれに整えてくれる奴

• 日付を曜日付きで表示する
• 未省略の形で表示する
• 月や日を常に２桁で表示する

のような表示をしたい時には、TEXT関数が使えます。

日付とカッコ付き曜日を表示する例

TEXT関数には二つの値を渡します。

上のスクショの例では、日付に当たるDATE($A$1,$B$1,ROW(A1))
と、それをどう表示するかの形式を指定する"d (DDD)”
のふたつの値を渡しています。

この表示形式の部分の書き方には規則があります。

上の例の"d (DDD)”と実際の表示1 (火)を見比べればお分かりの通り、dは日付の数字へ、DDDは曜日へと自動で変換され表示されます。

()のような記号は変換されずに表示されるので、お好みで他の記号を使うこともできますし、省くことももちろん可能です。

他にも"yyyy/m/d"とすれば通常通り「年/月/日」とように表示させることもできます。

いろんな表示形式に対応可能です

ちなみにTEXT関数で表示を整えられるのは日付だけではありません。
例えば金額計算をするシートで¥ 123,456.78`のように

• 先頭に¥マークを追加
• 3ケタ区切りのコンマを入れる
• 3ケタ区切りのコンマを入れる

金額などの表記も整えることができる

このようにTEXT関数を使えばかなり色々な表示パターンに対応することができます。

些細なことに思えるかもしれません。でも、ちょっと見た目を整えてあげるだけで、あなたの資料を見てくれる人のエネルギーを節約してあげられるという点で、TEXTは実はとってもパワフルな関数なんです。

さて、筆者がなんか良いことを言った気分に浸ったところで、今回の記事をクロージングへを向かわせることとします (ドヤァ)

 

---

 
 

今回は初回ということもあり、かなり汎用的な内容をピックアップしましたがいかがだったでしょうか？

DATEやTEXT関数はデータの入力だけでなく、ガントチャートなどのカスタマイズしたカレンダーの作成にも活用していただけると思います。今後もスプレッドシートに留まらずツールのTipsやお役立ち情報を、社内の事例など交えて紹介していこうと考えています。

今回は最後までご覧いただきありがとうございました！

トンガルマンには他にもこんなメンバーがいます!
トンガルマン採用サイト