ITで業務改善！スキルUP応援メディア
作りながら学ぶ！
シリーズ連載を通して、勤怠管理システムを作りながらスプレッドシートを体系的に学ぶ方法を解説。
こんにちは、たま(@closuppo)です！
今回は 「第6回： IF関数にArrayFormula関数を組み合わせで使う方法」 について説明していきます。
1つの数式で複数のセルに稼働時間や残業時間を自動計算&自動反映されるようにしていきましょう。
全シリーズは以下シートにまとめています
※目次をクリックすると
タイトルの先頭まで移動します（↓）
前回の記事ではIF関数・DAY関数・OR関数を使って、閏年(うるうどし)を自動判定しカレンダー月末の29日・30日・31日を自動表示＆自動非表示するシートを作成しました。
今回は、その続きで IF関数やArrayFormula関数を使って、1つの数式で複数のセルに稼働時間や残業時間を自動計算&自動反映する方法について解説します。
サンプルシートを用意しています
のでコピーして参考にしてください
1ヶ月の合計勤務時間を集計するためには、1日の【出勤時間】【退勤時間】および【休憩時間】を入力する必要があります。
他の月でも勤怠シートを活用できるように1日から31日まで数式を適用していきましょう。
以下のように、出勤時間・退勤時間・休憩時間を入力して1日あたりの労働時間を計算します。
退勤時刻から出勤時間と休憩時間を減算することで、その日の稼働時間を算出できます。
セル「F2」に入力する数式はこうなります。
こちらは、退勤のセルD2「20:00」から出勤のセルC2「10:00」と休憩のセルE2「1:00」をマイナスし稼働時間を計算しています。
1日の稼働が8時間を超えた分の時間を「残業時間」として計算しましょう。
「残業時間」はIF関数を使って計算します。
セルG2に入力する数式は以下の通りです。
この数式を日本語にすると、
という意味になります。
こちらは、 2つのIF関数が組み合わされた複数条件を判定する数式 となっています。
少し複雑になり、数式を作るのが難しくなりますが諦めずにゆっくりと進めていきましょう。
それでは、詳しく解説していきます。
まずはじめに、休日を判定する処理を行うIF関数を作ります。
出勤のセル「C2」が空白の場合は「"" (空白)」と表示されるようにしていきましょう。
IF関数の構文はこうでしたね。
まずは【❶条件】の指定から。
セル「C2」が休日かどうかを判定したいので【❶条件】には「C2= ""」と指定します。
演算子「=」は「等しい」を意味するので「C2は空白と等しい」との条件になっています。
セル「C2」が空白と等しい場合は【❷TRUE の処理】が実行され、そうでない場合は【❸FALSE の処理】が実行されます。
「""」は空白を意味します！
次に、【❷TRUE の処理】を行います。
セル「C2」が空白と等しい場合、セルG2の残業時間は空白にしたいので【❷TRUE の処理】には「""」と指定します。
セルC2が空白と等しくない場合は【❸FALSE の処理】で残業時間の計算を行うのですが、この処理は2つ目のIF関数を使って計算します。
2つ目のIF関数を完成させたら【❸FALSE の処理】に2つ目のIF関数を組み込みましょう。
1つ目のIF関数の【❸FALSE の処理】に指定する 2つ目のIF関数を作っていきましょう。
稼働時間のセル「F2」が8時間を超える場合は残業時間の計算をし、そうでない場合は「 0 」と表示されるようにしていきます。
ここから少し難しくなってきます。しっかりと理解できるよう、ゆっくり進めていきましょう！
まずは【❶条件】の指定から。
ここではセルF2が8時間を超えるかを判定したいので【❶条件】には「F2>1/3」と指定します。
こちらは「セルF2は8時間より大きい」との条件になり、セルF2が8時間より大きい場合は【❷TRUE の処理】が実行され、 そうでない場合は【❸FALSE の処理】が実行されます。
演算子「>」は「より大きい」を意味し、「1/3」は、1日の時間計算のうち8時間をあらわします。（24時間×1/3＝8時間）
深く考えずに「1/3＝8時間」と
覚えておきましょう
次に、【❷TRUE】の処理 を行います。
【❶条件】が満たされている場合は残業時間を計算したいので【❷TRUE の処理】 には「 F2-1/3」と指定します。
こちらは、稼働時間セル「F2」から「1/3（8:00）」をマイナスして残業時間を計算しています。
【❶条件】 を満たしていない場合は【❸FALSE の処理】となります。稼働時間は8時間未満となり残業時間は発生していないので、【❸FALSE の処理】 には「0」と指定します。
作成した２つのIF関数を組み合わせて残業時間を求める数式を完成させましょう。
1つ目のIF関数の【❸FALSE の処理】に 2つ目のIF関数 を組み合わせることで完成します。
作業が完了したら、稼働時間を入力して2列目から32列目までが自動集計されるようになっているか試してみましょう。
2つ目のIF関数はIFの前の「=」を記入しないよう注意してください。（「=」を入れるとエラーにとなります。）
IF関数の組み合わせは実用性が高く使用頻度も高いです。使いこなせるよう諦めずに頑張って習得しましょう。
残りのセル30行目から30行目までの稼働時間や残業時間は、ARRAYFORMULA関数を使ってまとめて計算していきます。
ARRAYFORMULA(アレイフォーミュラ)関数は1つの数式で複数のセルに値を反映することができます。
セルに1つずつ関数を入力する手間が省けるだけでなく、ブラウザへの負担も軽減されるのでスプレッドシートの表示速度が早くなるとても便利な関数です。
このARRAYFORMULA関数は、Googleスプレッドシートのオリジナル関数となりエクセルには用意されていない関数となります。
ARRAYFORMULA関数の構文はこちら。
この、ARRAYFORMULA関数を使って稼働時間や残業時間が自動計算されるように作っていきましょう。
まずは稼働時間にARRAYFORMULA関数を適用します。
稼働時間のセルF2の数式にARRAYFORMULA関数を適用するとこうなります。
構文【＝ARRAYFORMULA（配列数式）】の（配列数式）に数式を入れるのですが、ここで気をつけて欲しいのがARRAYFORMULA関数を「適用した数式」と「適用していない数式」とではセルの範囲指定が異なっているという点です。
「適用した数式」では2行目から32行目までの複数のセルを指定しています。その結果、2行目から32行目までをそれぞれ計算し値を返してくれるというわけです。
一方、「適用していない数式」では2行目のセルしか指定していないため1つの行しか計算されません。
次に、残業時間の数式にARRAYFORMULA関数を適用させましょう。
残業時間のセルG2の数式にARRAYFORMULA関数を使った数式がこちら。
稼働時間のセルF2と同様に、ARRAYFORMULA関数を「適用した数式」と「適用していない数式」とではセルの範囲指定が異なります。
時刻がうまく表示されていない場合は表示形式を変更してください。
メニューバーの表示形式から数字 > カスタム日時 より変更することができます。
表示形式については以下の
記事で詳しく解説しています
ショートカットキーを使うと一発でARRAYFORMULA関数を入力することができます。
ARRAYFORMULA関数を手打ちする手間が省け、作業効率が大幅に改善されるのでおススメです。
Mac：Command ＋ Shift ＋ Enter
Windows：Ctrl ＋ Shift ＋ Enter
Chrome OS：Ctrl + Shift + Enter
セル内の数式にカーソルを合わせた状態でショットカットキーを押すと、冒頭に「ARRAYFORMULA」が追加されます。
あとは通常の関数の対象セルを範囲にすればARRAYFORMULA関数の完成です。
今回はGoogleスプレッドシートでIF関数にARRAYFORMULA関数を組み合わせて使う方法をについてお伝えしました。
少し難しい関数ではありますが慣れれば簡単に使いこなせるようになりますので、ショートカットキーを使ってARRAYFORMULA関数も積極的に活用していきましょう。
次回は、データの入力規則を使ったプルダウンリストの作り方についてお伝えします。
シリーズ連載を通して、勤怠管理システムを作りながらスプレッドシートを体系的に学ぶ方法を解説しています。
