[Googleスプレッドシート]タイムカードを作りながら、関数や日付計算、データの扱い方を学ぶ

    Contents
  • はじめに … マイタイムカードを用意すべき重大な理由
  • Google スプレッドシートで、タイムカードを作る
    • 連続する日付を簡単に入れる
    • 四則演算と関数を使う
    • 日付から曜日を自動表示
    • 所要時間を計算する
    • 複数のセルを一気に足すには
    • セルの表示形式を変える
    • 使わない数式は消しておく
    • スプレッドシートで時間を計算に使う
    • ”起点日(基準日、基準点)” の考え方
    • データの効率的な管理方法

元 Excel マスター、現 Chromebook & Google サービス推進委員会会長の Hana です。

同僚部屋で話題にのぼっていた件で、タイムカードの作り方をシェアします。

こんな人に★

  • スプレッドシート初心者 (四則演算、時間計算)
  • 関数を使って時短したい人 (SUM, TEXT, WEEKDAY, VALUE)
  • データの効率的な管理方法を知りたい人 (データ型、数式と値、フラグ)

「関数はちょっと・・・とりあえず枠だけあればいいや」という方には、こちらをシェアします。

シンプルなタイムカード(枠のみ)

・・・と思いましたが、枠だけならすぐだし、セキュリティも気になるので、一緒にイチから作ってみましょうか!クリックしちゃった人ごめんなさい。

時間が無い人は、画面イメージと水色のボックス内だけ見て行ってください。中身の作り方だけやります。見た目の体裁はお好みで。

スプレッドシート自体は視覚的に使えるようになっているはずです。デフォルトでは用途毎にメニューが一ヶ所に固まっています。メニューアイコンの意味が分からない時は、アイコン上にカーソルを当てると説明がポップアップで出て来ます。

はじめに … マイタイムカードを用意すべき重大な理由

この項は Internet Assesor として働いている人へ向けたものです。スプレッドシートを学ぶ目的で使われる方は、飛ばして次の項へどうぞ。

「時間はトラッキングできるし、タイムカードはシステムで管理している。タスクは自動でカウントされる。なのに自分でタイムカードを用意する必要ってあるの?」と思う人が、いるかもしれません。

もちろん、しなくて良いのですよ。

用意するのは単に自分のためです。何でも相手任せにすると、いざという時にバカを見ます。

マイタイムカードを LB に提出した話を求人記事に書きましたが、同様の経験を他の複数の方々もされているようです。

たとえ信用できる相手だとしても、何かあった時に自分の身を守るためには、多少面倒であっても、自分に有利な「証拠」を残しておく必要があります。

証拠と共に「情報」も残しておけば、後から確認質問にも使え、自分の仕事の質を上げることにも繋がります。

[FAQ] タイムシートを直せと言われた

「数万円・数十時間程度、削れと言われても別にいいや」という方には、これはもちろん不要な作業です。

Google スプレッドシートで、タイムカードを作る

私のタイムカードは Google スプレッドシートで作っています。以前は Excel で作っていました。

関数が組まれたシートは仕組みを分からないまま使うと、ある日とんだ大ミスが起きます。それもあって、モノじゃなく知識をシェアしようと思いました。

シンプルなタイムカードを「自分で関数を使って作る方法」を、これから書いて行きますね。

私はシートを2 枚に分けて管理しています。

  • A.タイムシート … 勤怠管理用
  • B.タスクシート … タスク中に開くシート

今日は、タイムシートを一緒に作りましょう。

A. タイムシート

  1. 日付 (Date型、手入力)
  2. 開始時刻と終了時刻 (Time型、手入力)
  3. タスクカウント (数値型、手入力)
  4. かかった時間 (Time型、手入力)

こういうものを楽に・速く作るには、関数やスプレッドシートの小技を使います。

連続する日付を簡単に入れる

まず普通のタイムシートを作る場合、こんなイメージになりますかね。

まずは列 A に「日付」を入れて行きます。

最初の一つだけ手入力。後はカーソルをセルの右下に当て、ドラッグで下のセルにコピー。

「出勤」「退勤」を一日に一回ずつ打刻するイメージのタイムシートは、これで OK。

普通のタイムシート

ですが、私は数式をコピペして作っています。

私の場合、稼働時間が「朝」「昼」「晩」のように複数回に分かれているからです。

なので、欲しいのはこんなタイムカード。

タスクの開始&終了を管理

    A2 は手入力。
    A3〜 A6 は A2 の値をコピペ。
    A7 〜 A11 には数式を作成。A7 に “= A2 + 1” と入れ、後はコピペ。

A12 以降は後で(他の列とまとめてコピペするので)。

四則演算と関数を使う

スプレッドシートを触ったことが無い人は、まずここが始まりです。

スプレッドシートや Excel って、「表計算ソフト」ですよね。計算やデータ加工、分析が超得意。ワープロソフトではない。

その能力を駆使すれば、Web サイトのデータを拾ってきたり、VBA できれいな書類やグラフを作ったり、本当に色々なことができます。使う人は、キーワード一つ入力するだけ、ボタン一つ押すだけ。

はじめの第一歩として、四則演算とその周辺の関数は、必ず覚えましょう。

基礎だけやります。

セルに「=」を入力すると、計算を始める合図。

あとは小学校で習ったのと同じです。記号がちょっと違うだけ。

= A1 + B1 ・・・足し算

= A1 – B1 ・・・引き算

= A1 * B1 ・・・掛け算

= A1 / B1 ・・・割り算

日付から曜日を自動表示

タイムシートに戻って、次は列 B に「曜日」を入れます。

方法が 2 つあり、同じ結果が出ます。セルに手打ちしちゃった方が、イッパツでできて楽かも。

    • 「=Text(セル, “ddd”)」と計算式に入れる
    • 「=Weekday(セル)」と計算式に入れ、書式設定で曜日を設定

ちなみに “dddd” なら、「月曜日、火曜日・・・」と表示されます。

menu → [Format] → [Number] → [More Formats] → [More date and time formats]で書式設定を開き、表示形式を曜日にする

この時、「月火水・・・」ではなく、「Mon, Tue, Wed …」と表示されるかもしれません(私だけかも!?)。そんな時は、そのスプレッドシートの設定を「日本語」に直せばOK。

menu → [File] → [Spreadsheet settings] の Locale を “Japan” に

所要時間を計算する

列 E に「所要時間」を計算する式を入れて行きます。

多分もう分かると思うので、とりあえずやってみてください。

上手く行かない時は、四則演算と関数を使う の所をもう一度見てみて。

作ったら、動作確認をします。時間を入力してみて。

複数のセルを一気に足すには

列 F に「合計時間」を入れて行きます。これで一段落。

セルの値を合計したい時、Excel の場合は、キーかマウスでセル上をドラッグして囲めば良いです。

スプレッドシートの場合は、囲めば右下のシート外に答えが出ますが、計算は上手く行きません。

なら、関数を使いましょう。

    合計値を表示したいセルに

    =sum(

    と打ち、対象範囲を囲んで Enter。

計算されましたか?

セルの表示形式を変える

え?表示がおかしい?

なら、そのセルの表示形式を確認しましょうね。曜日計算の所でやったように menu → [Format] で書式設定を開き、好きな表示形式を選びます。

曜日の表示形式を選ぶ

時刻の表示形式を選ぶ


経過時間の表示形式を選ぶ

注意したいのは、「所要時間」「合計時間」の表示形式は “Duration(継続した時間)” を選ぶこと。でないと 24 時間を超えるとまた 0 からカウントしだすので。

タイムシートはここで一応完成です。

使わない数式は消しておく

日付と曜日の数式は、この後もう使わないので、「値貼り付け」で消しておくと良いです。数式か多いほど、シートの動作が重くなり、間違いが起きやすくなるので。

おつかれさまでした!

スプレッドシートで時間を計算に使う

ここからは余談で、「時間」を他の計算に使いたい人への情報です。「タイムシートにつける数値」や「かかった時間に対する生産性」を得る時などに必要です。

「時間」は 60 進法なので、そのままだと普通の計算 (10 進法) には使えません。時間と時間以外の計算に使う時は、先に Value 関数で変換する必要があります。

Value は、() で囲んだ値を、スプレッドシートが計算で使用できる「シリアル値」に変換する関数です。「文字列」「日付」「時間」など数値以外の値を、数値に変換します。日付や時間を変換した際の戻り値は ”起点日からの経過日数” です。

”起点日/基準日/基準点” の考え方

「日付」の起点日は 1900/01/01 で、スプレッドシートや Excel で扱える最も古い日時です。1900/01/02 は 2、1900/01/03 は 3・・・と増えて行きます。

「時間」の起点日は 0:00 です。なので、

=value(セル)

は、「0:00 から開始したとして、その経過時間」を「日数で」表します。

1日= 24 時間、1 時間 = 60 分、1分= 60 秒だから、答えが「時間」で欲しいのなら、「日数」に 24 をかけます。「分」で欲しいのなら、更に「時間」に 60 をかけます。

=value(セル) ・・・日

=value(セル)*24 ・・・時間

=value(セル)*24*60 ・・・分

=value(セル)*24*60*60 ・・・秒

「シリアル値」と「Value 関数」、理解できちゃいましたよね。

B. タスクシート

  1. 日付
  2. タスクの種類(記号で)
  3. クエリ名(コピペ)
  4. タスクのURL(コピペ)
  5. 評価
  6. コメント
  7. 後で使うものに、フラグ

こちらは「いつ・何をやったか・結果はどうだったか」を管理するためのシートです。数式は使わず、全て手入力です。

1 と 2 だけ、使う時に一つ入力して、後は「Ctrl + V」またはドラッグでコピー。

タスク中に必ずやるのは、3 と 4 をシステムからコピペすること。あとは自分が業務後に再利用したい情報を入れるのみです。

データの効率的な管理方法

B-7 のフラグをつける時の基本は、意味をもたせた「数字」を入れて行くことです。

セルに色をつける・・・とかは止めましょう。後で目視で探さなければならなくなるので。適当な文字列をバラバラと入れるのも止めましょう。絞り込みしたい時に困るので。

具体的に言うと、たとえば以下のようにします。使うのは自分が管理できる分だけ。

  1. 質問する
  2. 後で見る

「日付」「タスクの種類」も、後で絞り込みに使えるよう、入力するデータの形式を揃えておきます。前半角漢数字混合とか、バラバラと色んな形で入力しない

このページに出てきた「四則演算」「単位換算」「シリアル値」「起点日」「フラグ」は、データや数値を扱う上での大切な基本です。覚えておくとすごく便利。

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA