【個人的に好きなExcel関数】ちょっと難しい配列関数？！でも使いこなせればVLOOKUPより便利！！ちょー便利
はじめに
マイクロソフトの表計算ソフト「Excel」を使って、お仕事をしている人は、少なくないと思います。
「Excel」は、データを入力して、そのデータの計算を簡略化させる事で、実力が発揮されます。
例えば、「Excel」を使って、「１０日間の勤務時間」を入力する場合、勤務時間だけを入力するだけでは、単なるワープロです。
「Excel」を利用するならば、最低でも１０日間での「合計の勤務時間」などの「実績を知る」事を目的とするべきです。
こうした作業を「集計作業」と言います。
「集計作業」を行っていると、「存在するデータを多くの目的で利用」したくなります。
「人の能力」を判断し、「効率良く、お仕事を消化する」為にも、「担当別の作業時間」や「作業内容まで詳細化した作業時間」などは、誰でも知りたい情報となります。
この時、一つ一つの集計を「VLOOKUP関数」にて検索を行い、集計するのも良いのですが、「VLOOKUP関数」では「検索条件」が１つしか指定する事ができません。
「複数の検索条件」や「複雑な検索条件」は「VLOOKUP関数」での対応は難しくなります。
そのような時に利用したいのが「配列関数」となります。
「配列関数」を利用する事で、「複数の検索条件」や「複雑な検索条件」の設定が可能となる為、「集計作業」が楽になります。
「勤務時間の集計」を例えと致しますが、基本を知れば、他の集計にも応用が可能となります。
少々、難しいのですが、「配列関数」を簡単にご紹介致しますので、まずは慣れて頂ければと思います。
なお、画面は「マイクロソフトExcel For Mac」となっております。
OS（WindowsやMac）やバージョンの違いで、画面が違う、もしくは利用できない場合がありますので、予めご了承ください。
配列関数のメリットとデメリット
「配列関数」について、「メリット」と「デメリット」を先に挙げます。
「欲しいのは、この情報でない」と思われた方は、ここで終了としてください。
- 複数条件の利用が可能。
- 基本的に条件式（IF関数）なので、大なり（＞、＝＞）や、小なり（＜、＝＜）などの条件指定が可能。
- 閉じているBOOKへのアクセスが可能。
- 関数作成が複雑。
- 集計範囲が広くなればなるほど、処理が遅くなる（重くなる）。
- 他のBOOKへの影響がある。（他のBOOKを共有参照している時、そのBOOKの処理も遅くなる）
「メリット」と「デメリット」はそれぞれありますが、まず「VBA」を熟知されている人は、無理に「配列関数」の利用は考えず、「VBA」で集計された方が、より細かく高度な集計作業が可能です。
そして、「デメリット」で挙げている、「3.他のBOOKへの影響がある・・・」ですが、他のBOOKを参照する時は、それらBOOKのコピーを行い、コピーしたBOOKを検索対象とすれば、他の人への影響は発生しません。
「配列関数」は、「メリット」で挙げている「2.基本的に条件式（IF関数）なので・・・」による所が大きく、「IF関数」の利用に慣れている事が、前提となります。
ここでは、「IF関数」の詳細には触れませんが、「Excel」を利用するにあたり、「IF関数」の利用には慣れておいても損はありません。
と、いいますか、「IF文」は「Excel」意外にも様々なツールや言語にて利用されていますので、「IF文の概念」は、理解しておきましょう。
勤務時間の集計
「勤務時間表」を作り、勤務時間の集計を行います。
まずは、「１０日間の勤務時間の集計」を行い、「合計勤務時間」を「配列関数」で求めます。
シート「DATA」に２０２０年１０月１日から２０２０年１０月１５日までの勤務時間表を作成しました。
実際に「Excel」で検証をされる時は、「DATA」シートを作成して、以下の表をご利用ください。
| 開始時間 | 終了時間 | 勤務時間 | |
|---|---|---|---|
| 2020/10/1 | 9:00 | 18:00 | 9:00 |
| 2020/10/2 | 9:00 | 18:30 | 9:30 |
| 2020/10/3 | 9:00 | 19:00 | 10:00 |
| 2020/10/4 | 9:00 | 18:15 | 9:15 |
| 2020/10/5 | 9:00 | 20:00 | 11:00 |
| 2020/10/6 | 11:00 | 18:00 | 7:00 |
| 2020/10/7 | 9:00 | 18:00 | 9:00 |
| 2020/10/8 | 9:00 | 20:00 | 11:00 |
| 2020/10/9 | 7:00 | 16:00 | 9:00 |
| 2020/10/10 | 9:00 | 18:00 | 9:00 |
| 2020/10/11 | 9:00 | 18:00 | 9:00 |
| 2020/10/12 | 9:00 | 18:30 | 9:30 |
| 2020/10/13 | 9:00 | 19:00 | 10:00 |
| 2020/10/14 | 9:00 | 18:15 | 9:15 |
| 2020/10/15 | 9:00 | 20:00 | 11:00 |
シート「配列関数」にて、「合計勤務時間」を集計します。
２０２０年１０月１日から、２０２０年１０日までの「合計勤務時間」を集計する為、対象セルに「
=sum(IF((DATA!$A$2:$A$16<=DATE(2020,10,10)),DATA!$D$2:$D$16,0))
」を入力してください。
この関数は以下の形で作成しています。
＝SUM関数（
IF関数（
（シートDATAの検索範囲＜＝検索値）、
真の時は、検索値の行と一致する指定した列の値を取得、
偽の時は０））
注意１：検索範囲はドルマーク（＄）をつけて、絶対参照とする（コピペ時の検索範囲、検索値のズレを防止します）
注意２：検索範囲の「開始行」と「終了行」には、十分にご注意ください。（検索範囲のズレにより、誤った集計結果となる事が、よくあります。）
入力完了時はエンター（Enter)ではなく、「Control ＋ Shift ＋ Enter」としてください。
入力完了を「Control ＋ Shift ＋ Enter」とする事で、数式が「{ 」「}」で囲まれます。
この結果、２０２０年１０月１日から２０２０年１０月１５日までの勤務時間表から、１０月１０日までの「合計勤務時間」を求める事ができます。
なお、詳細は省きますが、「セルの書式設定」の「表示形式」にて、「ユーザー書式」を「 [h]:mm 」にする事を忘れないようにしてください。
少し、詳細をお話しします。
今回は、勤務時間の合計を求めたいので、「SUM関数」を使用しております。
そして、「IF関数」の条件が真の時、「検索範囲より検索値を取得」、条件が偽の時は検索結果を「０」とする事で、２０２０年１０月１日から１０日までの勤務時間の取得を行っています。
取得条件を追加する
条件が１つだけでは、「配列関数」を利用する意味があまりありません。
再度、シート「DATA」に２０２０年１０月１日から２０２０年１０月１５日までの勤務時間表を作成しますが、今度は担当者を追加致しました。
実際に「Excel」で検証をされる時は、「DATA」シートを作成して、以下の表をご利用ください。
| 担当者 | 開始時間 | 終了時間 | 勤務時間 | |
|---|---|---|---|---|
| 2020/10/1 | 山田 | 9:00 | 18:00 | 9:00 |
| 2020/10/2 | 山田 | 9:00 | 18:00 | 9:00 |
| 2020/10/2 | 高橋 | 9:00 | 19:00 | 10:00 |
| 2020/10/3 | 田中 | 9:00 | 18:15 | 9:15 |
| 2020/10/3 | 山田 | 9:00 | 20:00 | 11:00 |
| 2020/10/4 | 田中 | 11:00 | 18:00 | 7:00 |
| 2020/10/5 | 高橋 | 9:00 | 18:00 | 9:00 |
| 2020/10/6 | 高橋 | 9:00 | 20:00 | 11:00 |
| 2020/10/7 | 山田 | 7:00 | 16:00 | 9:00 |
| 2020/10/10 | 山田 | 9:00 | 18:00 | 9:00 |
| 2020/10/11 | 高橋 | 9:00 | 18:00 | 9:00 |
| 2020/10/11 | 田中 | 9:00 | 18:30 | 9:30 |
| 2020/10/12 | 山田 | 9:00 | 19:00 | 10:00 |
| 2020/10/12 | 高橋 | 9:00 | 18:15 | 9:15 |
| 2020/10/15 | 田中 | 9:00 | 20:00 | 11:00 |
シート「配列関数」にて、「担当者別合計勤務時間」を作成します。
今回は、シート「配列関数」に「担当者」と「合計勤務時間」の表を作成します。
２０２０年１０月１日から、２０２０年１０日までで、「担当者別合計勤務時間」を集計する為、対象セルに「
=SUM(IF((DATA!$A$2:$A$16<=DATE(2020,10,10)*(DATA!$B$2:$B$16=$A2)),DATA!$E$2:$E$16,0))
」を入力してください。（入力完了は「Control ＋ Shift ＋ Enter」となります。）
この関数は以下の形で作成しています。
＝SUM関数（
IF関数（
（シートDATAの日付の検索範囲＜＝検索値）
*・・・条件を「＊」にて繋げます。
（シートDATAの担当者の検索範囲＝検索値）、
真の時は、検索値の行と一致する指定した列の値を取得、
偽の時は０））
注意：今回はシート「DATA」の担当者の検索値として、シート「配列関数」の担当者のセルを指定しています。列を絶対位置（＄）とする事で、入力した「配列関数」をコピーするだけで、他の2人の勤務時間の合計を求める事ができます。
検索の条件を「*」で繋げる事で、AND条件となり、「どの条件も一致」にて真となります。
この「*」にて条件を連結する事により、複数の条件指定が行えるようになります。
なお、OR条件には対応していないので、「どれかの条件に一致」を真とする事はできません。
このように、検索条件を複数指定する事ができました。
「VLOOKUP関数」ですと、「担当者」を指定した集計は可能ですが、「日付」による期間を指定する事ができません。
複数の条件をつなぎ合わせて、「全て一致する値」を集計対象としています。
担当者のごとの作業時間や作業内容まで詳細化して集計する例
最後に、これまでの応用として「担当者のごとの作業時間や作業内容まで詳細化して集計する例」をご紹介します。
詳細については割愛致しますので、予めご了承ください。
「DATA」シートに、以下の表を作成します。（「DATA」シートを作成し、データ部分のみを貼り付けてください）
| 担当者 | 作業内容 | 開始時間 | 終了時間 | 勤務時間 | |
|---|---|---|---|---|---|
| 2020/10/1 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/1 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/1 | 田中 | 社内打合せ | 13:00 | 18:00 | 5:00 |
| 2020/10/1 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/1 | 山田 | 客先打合せ | 15:00 | 19:00 | 4:00 |
| 2020/10/1 | 山田 | 社内打合せ | 9:00 | 13:00 | 4:00 |
| 2020/10/1 | 山田 | 問合せ対応 | 13:00 | 15:00 | 2:00 |
| 2020/10/2 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/2 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/2 | 田中 | 社内打合せ | 13:00 | 19:00 | 6:00 |
| 2020/10/2 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/2 | 山田 | 社内打合せ | 9:00 | 18:00 | 9:00 |
| 2020/10/3 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/3 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/3 | 田中 | 社内打合せ | 13:00 | 20:00 | 7:00 |
| 2020/10/3 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/3 | 山田 | 作業 | 9:00 | 14:00 | 5:00 |
| 2020/10/3 | 山田 | 問合せ対応 | 15:00 | 20:00 | 5:00 |
| 2020/10/4 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/4 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/4 | 田中 | 社内打合せ | 13:00 | 18:00 | 5:00 |
| 2020/10/4 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/4 | 山田 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/5 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/5 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/5 | 田中 | 社内打合せ | 13:00 | 19:00 | 6:00 |
| 2020/10/5 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/5 | 山田 | 作業 | 9:00 | 19:00 | 10:00 |
| 2020/10/6 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/6 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/6 | 田中 | 社内打合せ | 13:00 | 20:00 | 7:00 |
| 2020/10/6 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/6 | 山田 | 客先打合せ | 9:00 | 14:00 | 5:00 |
| 2020/10/6 | 山田 | 社内打合せ | 14:00 | 18:00 | 4:00 |
| 2020/10/7 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/7 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/7 | 田中 | 社内打合せ | 13:00 | 18:00 | 5:00 |
| 2020/10/7 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/7 | 山田 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/8 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/8 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/8 | 田中 | 社内打合せ | 13:00 | 19:00 | 6:00 |
| 2020/10/8 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/8 | 山田 | 作業 | 9:00 | 19:00 | 10:00 |
| 2020/10/9 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/9 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/9 | 田中 | 社内打合せ | 13:00 | 20:00 | 7:00 |
| 2020/10/9 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/9 | 山田 | 問合せ対応 | 9:00 | 20:00 | 11:00 |
| 2020/10/10 | 高橋 | 作業 | 9:00 | 18:00 | 9:00 |
| 2020/10/10 | 田中 | 社内打合せ | 10:00 | 13:00 | 3:00 |
| 2020/10/10 | 田中 | 社内打合せ | 13:00 | 18:00 | 5:00 |
| 2020/10/10 | 田中 | 問合せ対応 | 9:00 | 10:00 | 1:00 |
| 2020/10/10 | 山田 | 社内打合せ | 9:00 | 11:00 | 2:00 |
以下のように貼り付けました。
「日別担当者別勤務時間表」を作成します。
２行目から４行目までのB列の合計は、C列からL列までの合計とします。
５行目のC列からL列までのは、同じ列のC列からL列までの合計とします。
いずれも、「SUM関数」で設定してください。
２行目のC列に以下の「配列変数」を入力します。
=SUM(IF((DATA!$A$1:$A$54=C$1)*(DATA!$B$1:$B$54=$A2),DATA!$F$1:$F$54,0))
「Control ＋ Shift ＋ Enter」にて、入力を完了してください。
その後、入力したセルをD列からL列まで「数式のコピー」を行います。（「{」と「}」も一緒にコピーされます。）
「DATA」シートの一覧より、「担当者」が「高橋さん」で、「日付」が各列の１行目と一致する時、勤務時間の取得を行い、その合計を１行目の日付ごとに合計しています。
「高橋さん」では分かりづらいので、他の担当者の集計も行います。
集計は、２行目のC列からL列を「田中さん」と「山田さん」に「数式のコピー」をして下さい。
コピーが完了すれば、「日別担当者別勤務時間表」は、完成となります。
同様に、検索条件を追加する事により、「日別担当者別作業別勤務時間表」の作成も可能となります。
「配列関数」を実際に作って頂ければと思います。
このような結果となります。
参考までに、２行目のD列と１６行目のD列の「配列関数」の内容を記載いたします。
いずれも、「Control ＋ Shift ＋ Enter」で入力完了となります。
２行目のD列
=SUM(IF((DATA!$A$1:$A$54=D$1)*(DATA!$C$1:$C$54=$B2)*(DATA!$B$1:$B$54=$A2),DATA!$F$1:$F$54,0))
１６行目のD列
=SUM(IF((DATA!$A$1:$A$54=D$1)*(DATA!$C$1:$C$54=$B2)*(DATA!$B$1:$B$54=$A2),DATA!$F$1:$F$54,0))
コピーを行う時、「検索範囲」や「検索値」の絶対位置（＄）の指定に誤りがあると、値の取得が思い通りにいきません。
結果の検証行いながら、少しずつコピーを行ってください。
また、「セルの書式設定」にも注意が必要です。
とくに「日付や時刻」は、「表示形式」が異なると、まったく違う結果となります。
今回の例では省いてしまいましたが、時刻の計算をする時は、極力、「数値形式」に変換をしてから、集計作業を行うようにしましょう。
simacatより一言
作業時間の管理をする時、「Excel」を利用されている方は多いと思います。
その際、「配列関数」が利用できれば、集計作業を早く、そして楽に行う事ができますので、是非、参考にして頂ければと思います。
コメント