【個人的に好きなExcel関数】ちょっと難しい配列関数?!でも使いこなせればVLOOKUPより便利!!ちょー便利

7326社会人へのアドバイス
simacat.com
スポンサーリンク

【個人的に好きなExcel関数】ちょっと難しい配列関数?!でも使いこなせればVLOOKUPより便利!!ちょー便利

はじめに

マイクロソフトの表計算ソフト「Excel」を使って、お仕事をしている人は、少なくないと思います。

「Excel」は、データを入力して、そのデータの計算を簡略化させる事で、実力が発揮されます。

例えば、「Excel」を使って、「10日間の勤務時間」を入力する場合、勤務時間だけを入力するだけでは、単なるワープロです。

「Excel」を利用するならば、最低でも10日間での「合計の勤務時間」などの「実績を知る」事を目的とするべきです。

こうした作業を「集計作業」と言います。

「集計作業」を行っていると、「存在するデータを多くの目的で利用」したくなります。

「人の能力」を判断し、「効率良く、お仕事を消化する」為にも、「担当別の作業時間」や「作業内容まで詳細化した作業時間」などは、誰でも知りたい情報となります。

この時、一つ一つの集計を「VLOOKUP関数」にて検索を行い、集計するのも良いのですが、「VLOOKUP関数」では「検索条件」が1つしか指定する事ができません。

「複数の検索条件」や「複雑な検索条件」は「VLOOKUP関数」での対応は難しくなります。

そのような時に利用したいのが「配列関数」となります。

「配列関数」を利用する事で、「複数の検索条件」や「複雑な検索条件」の設定が可能となる為、「集計作業」が楽になります。

「勤務時間の集計」を例えと致しますが、基本を知れば、他の集計にも応用が可能となります。

少々、難しいのですが、「配列関数」を簡単にご紹介致しますので、まずは慣れて頂ければと思います。

なお、画面は「マイクロソフトExcel For Mac」となっております。

OS(WindowsやMac)やバージョンの違いで、画面が違う、もしくは利用できない場合がありますので、予めご了承ください。

[スポンサーリンク]

配列関数のメリットとデメリット

「配列関数」について、「メリット」と「デメリット」を先に挙げます。

「欲しいのは、この情報でない」と思われた方は、ここで終了としてください。

メリット
  1. 複数条件の利用が可能。
  2. 基本的に条件式(IF関数)なので、大なり(>、=>)や、小なり(<、=<)などの条件指定が可能。
  3. 閉じているBOOKへのアクセスが可能。
デメリット
  1. 関数作成が複雑。
  2. 集計範囲が広くなればなるほど、処理が遅くなる(重くなる)。
  3. 他のBOOKへの影響がある。(他のBOOKを共有参照している時、そのBOOKの処理も遅くなる)

「メリット」と「デメリット」はそれぞれありますが、まず「VBA」を熟知されている人は、無理に「配列関数」の利用は考えず、「VBA」で集計された方が、より細かく高度な集計作業が可能です。

そして、「デメリット」で挙げている、「3.他のBOOKへの影響がある・・・」ですが、他のBOOKを参照する時は、それらBOOKのコピーを行い、コピーしたBOOKを検索対象とすれば、他の人への影響は発生しません。

「配列関数」は、「メリット」で挙げている「2.基本的に条件式(IF関数)なので・・・」による所が大きく、「IF関数」の利用に慣れている事が、前提となります。

ここでは、「IF関数」の詳細には触れませんが、「Excel」を利用するにあたり、「IF関数」の利用には慣れておいても損はありません。

と、いいますか、「IF文」は「Excel」意外にも様々なツールや言語にて利用されていますので、「IF文の概念」は、理解しておきましょう。

勤務時間の集計

「勤務時間表」を作り、勤務時間の集計を行います。

まずは、「10日間の勤務時間の集計」を行い、「合計勤務時間」を「配列関数」で求めます。

シート「DATA」に2020年10月1日から2020年10月15日までの勤務時間表を作成しました。

7326-2
simacat.com

実際に「Excel」で検証をされる時は、「DATA」シートを作成して、以下の表をご利用ください。

開始時間終了時間勤務時間
2020/10/19:0018:009:00
2020/10/29:0018:309:30
2020/10/39:0019:0010:00
2020/10/49:0018:159:15
2020/10/59:0020:0011:00
2020/10/611:0018:007:00
2020/10/79:0018:009:00
2020/10/89:0020:0011:00
2020/10/97:0016:009:00
2020/10/109:0018:009:00
2020/10/119:0018:009:00
2020/10/129:0018:309:30
2020/10/139:0019:0010:00
2020/10/149:0018:159:15
2020/10/159:0020:0011:00

シート「配列関数」にて、「合計勤務時間」を集計します。

2020年10月1日から、2020年10日までの「合計勤務時間」を集計する為、対象セルに「

=sum(IF((DATA!$A$2:$A$16<=DATE(2020,10,10)),DATA!$D$2:$D$16,0))

」を入力してください。

この関数は以下の形で作成しています。

=SUM関数(

IF関数(

(シートDATAの検索範囲<=検索値)、

真の時は、検索値の行と一致する指定した列の値を取得、

偽の時は0))

注意1:検索範囲はドルマーク($)をつけて、絶対参照とする(コピペ時の検索範囲、検索値のズレを防止します)

注意2:検索範囲の「開始行」と「終了行」には、十分にご注意ください。(検索範囲のズレにより、誤った集計結果となる事が、よくあります。)

入力完了時はエンター(Enter)ではなく、「Control + Shift + Enter」としてください。

入力完了を「Control + Shift + Enter」とする事で、数式が「{ 」「}」で囲まれます。

この結果、2020年10月1日から2020年10月15日までの勤務時間表から、10月10日までの「合計勤務時間」を求める事ができます。

なお、詳細は省きますが、「セルの書式設定」の「表示形式」にて、「ユーザー書式」を「 [h]:mm 」にする事を忘れないようにしてください。

7326-4
simacat.com

少し、詳細をお話しします。

今回は、勤務時間の合計を求めたいので、「SUM関数」を使用しております。

そして、「IF関数」の条件が真の時、「検索範囲より検索値を取得」、条件が偽の時は検索結果を「0」とする事で、2020年10月1日から10日までの勤務時間の取得を行っています。

取得条件を追加する

条件が1つだけでは、「配列関数」を利用する意味があまりありません。

再度、シート「DATA」に2020年10月1日から2020年10月15日までの勤務時間表を作成しますが、今度は担当者を追加致しました。

7326-5
simacat.com

実際に「Excel」で検証をされる時は、「DATA」シートを作成して、以下の表をご利用ください。

担当者開始時間終了時間勤務時間
2020/10/1山田9:0018:009:00
2020/10/2山田9:0018:009:00
2020/10/2高橋9:0019:0010:00
2020/10/3田中9:0018:159:15
2020/10/3山田9:0020:0011:00
2020/10/4田中11:0018:007:00
2020/10/5高橋9:0018:009:00
2020/10/6高橋9:0020:0011:00
2020/10/7山田7:0016:009:00
2020/10/10山田9:0018:009:00
2020/10/11高橋9:0018:009:00
2020/10/11田中9:0018:309:30
2020/10/12山田9:0019:0010:00
2020/10/12高橋9:0018:159:15
2020/10/15田中9:0020:0011:00

シート「配列関数」にて、「担当者別合計勤務時間」を作成します。

今回は、シート「配列関数」に「担当者」と「合計勤務時間」の表を作成します。

7326-6
simacat.com

2020年10月1日から、2020年10日までで、「担当者別合計勤務時間」を集計する為、対象セルに「

=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の担当者の検索範囲=検索値)、

真の時は、検索値の行と一致する指定した列の値を取得、

偽の時は0))

注意:今回はシート「DATA」の担当者の検索値として、シート「配列関数」の担当者のセルを指定しています。列を絶対位置($)とする事で、入力した「配列関数」をコピーするだけで、他の2人の勤務時間の合計を求める事ができます。

検索の条件を「*」で繋げる事で、AND条件となり、「どの条件も一致」にて真となります。

この「*」にて条件を連結する事により、複数の条件指定が行えるようになります。

なお、OR条件には対応していないので、「どれかの条件に一致」を真とする事はできません。

7326-7
simacat.com

このように、検索条件を複数指定する事ができました。

「VLOOKUP関数」ですと、「担当者」を指定した集計は可能ですが、「日付」による期間を指定する事ができません。

複数の条件をつなぎ合わせて、「全て一致する値」を集計対象としています。

担当者のごとの作業時間や作業内容まで詳細化して集計する例

最後に、これまでの応用として「担当者のごとの作業時間や作業内容まで詳細化して集計する例」をご紹介します。

詳細については割愛致しますので、予めご了承ください。

「DATA」シートに、以下の表を作成します。(「DATA」シートを作成し、データ部分のみを貼り付けてください)

担当者作業内容開始時間終了時間勤務時間
2020/10/1高橋作業9:0018:009:00
2020/10/1田中社内打合せ10:0013:003:00
2020/10/1田中社内打合せ13:0018:005:00
2020/10/1田中問合せ対応9:0010:001:00
2020/10/1山田客先打合せ15:0019:004:00
2020/10/1山田社内打合せ9:0013:004:00
2020/10/1山田問合せ対応13:0015:002:00
2020/10/2高橋作業9:0018:009:00
2020/10/2田中社内打合せ10:0013:003:00
2020/10/2田中社内打合せ13:0019:006:00
2020/10/2田中問合せ対応9:0010:001:00
2020/10/2山田社内打合せ9:0018:009:00
2020/10/3高橋作業9:0018:009:00
2020/10/3田中社内打合せ10:0013:003:00
2020/10/3田中社内打合せ13:0020:007:00
2020/10/3田中問合せ対応9:0010:001:00
2020/10/3山田作業9:0014:005:00
2020/10/3山田問合せ対応15:0020:005:00
2020/10/4高橋作業9:0018:009:00
2020/10/4田中社内打合せ10:0013:003:00
2020/10/4田中社内打合せ13:0018:005:00
2020/10/4田中問合せ対応9:0010:001:00
2020/10/4山田作業9:0018:009:00
2020/10/5高橋作業9:0018:009:00
2020/10/5田中社内打合せ10:0013:003:00
2020/10/5田中社内打合せ13:0019:006:00
2020/10/5田中問合せ対応9:0010:001:00
2020/10/5山田作業9:0019:0010:00
2020/10/6高橋作業9:0018:009:00
2020/10/6田中社内打合せ10:0013:003:00
2020/10/6田中社内打合せ13:0020:007:00
2020/10/6田中問合せ対応9:0010:001:00
2020/10/6山田客先打合せ9:0014:005:00
2020/10/6山田社内打合せ14:0018:004:00
2020/10/7高橋作業9:0018:009:00
2020/10/7田中社内打合せ10:0013:003:00
2020/10/7田中社内打合せ13:0018:005:00
2020/10/7田中問合せ対応9:0010:001:00
2020/10/7山田作業9:0018:009:00
2020/10/8高橋作業9:0018:009:00
2020/10/8田中社内打合せ10:0013:003:00
2020/10/8田中社内打合せ13:0019:006:00
2020/10/8田中問合せ対応9:0010:001:00
2020/10/8山田作業9:0019:0010:00
2020/10/9高橋作業9:0018:009:00
2020/10/9田中社内打合せ10:0013:003:00
2020/10/9田中社内打合せ13:0020:007:00
2020/10/9田中問合せ対応9:0010:001:00
2020/10/9山田問合せ対応9:0020:0011:00
2020/10/10高橋作業9:0018:009:00
2020/10/10田中社内打合せ10:0013:003:00
2020/10/10田中社内打合せ13:0018:005:00
2020/10/10田中問合せ対応9:0010:001:00
2020/10/10山田社内打合せ9:0011:002:00

以下のように貼り付けました。

7326-12
simacat.com

「日別担当者別勤務時間表」を作成します。

7326-8
simacat.com

2行目から4行目までのB列の合計は、C列からL列までの合計とします。

5行目のC列からL列までのは、同じ列のC列からL列までの合計とします。

いずれも、「SUM関数」で設定してください。

7326-9
simacat.com

2行目の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列まで「数式のコピー」を行います。(「{」と「}」も一緒にコピーされます。)

7326-10
simacat.com

「DATA」シートの一覧より、「担当者」が「高橋さん」で、「日付」が各列の1行目と一致する時、勤務時間の取得を行い、その合計を1行目の日付ごとに合計しています。

「高橋さん」では分かりづらいので、他の担当者の集計も行います。

集計は、2行目のC列からL列を「田中さん」と「山田さん」に「数式のコピー」をして下さい。

7326-11
simacat.com

コピーが完了すれば、「日別担当者別勤務時間表」は、完成となります。

同様に、検索条件を追加する事により、「日別担当者別作業別勤務時間表」の作成も可能となります。

「配列関数」を実際に作って頂ければと思います。

7326-13
simacat.com

このような結果となります。

7326-14
simacat.com

参考までに、2行目のD列と16行目のD列の「配列関数」の内容を記載いたします。

いずれも、「Control + Shift + Enter」で入力完了となります。

2行目の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))

16行目の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」を利用されている方は多いと思います。

その際、「配列関数」が利用できれば、集計作業を早く、そして楽に行う事ができますので、是非、参考にして頂ければと思います。

コメント

タイトルとURLをコピーしました