|
シフト表をピボットテーブルでクロス集計 前述のように各種関数を駆使して集計を行えますが、 |
完成例
元のシフト表
クロス集計例
０や空白が並んだ行や列があるのは、数式が設定されている空白行も入力範囲として自動認識しているため。
（計算式が設定されている部分を全てデータとみなしているため－たとえ空白となっていても・・）
データの入力範囲を手動またはVBAで調整にて、結果が空白以外の部分になるように範囲を調整すれば綺麗な集計表ができます。
メンバーごとの勤務別日数
メンバーごとの勤務別時間合計－左端列は「休み」を表す
メンバーごとの曜日別勤務時間合計
日ごとの勤務別時間合計－早出の左列は「休み」を表す
日ごとの区分別時間合計－主任の左列は無意味
区分ごとの曜日別時間合計－土曜日の右列は無意味
作成手順
ピボットテーブルを使用するためには、帳票形式のデータベースが必要となります。
（帳票形式とは、見出しと、それに対応するデータが一覧形式になったもの）
まずは、シフト表および付随するテーブルから帳票形式のデータベースを作成する必要があります。
作成する帳票は、行見出しを「日付」、列見出しを各項目名（氏名や勤務名など）とし、それぞれの値（または名前）を一覧形式に表示します。
２次元の表を一覧表にするためには少し工夫が必要。
ここでは、下図の様に、２次元形式のシフト表内のセルに、連続する仮想番号を割り振ることで、１次元の表に変換します。
（実際に下図のような表を作るわけではありません。計算上、下図のように連番を想定して一意の行列番号を求めるためです。）
この形式のデータベースが出来ると、後は簡単にピボットテーブルを利用できます。
行数は人数、列数は日数に対応
相対行列は計算式で簡単に求められます。（都合上、０から開始）
仮想番号：連番を必要な数（人数×３１）分作っておく
相対行：仮想番号を列数で割った値を整数単位に繰り上げて１を引く ROUNDUP(A7/$C$3,0)-1
相対列：仮想番号－１を列数で割った余り MOD(A7-1,$C$3)
以降、ID～勤務時間は相対行／相対列を使用して計算式で求める
ピボットテーブルの使用方法はここでは割愛。
ピボットテーブルの使用方法に疎い方は、「無料EXCEL教材－分析ツール－ピボットテーブル」を参考にして下さい。
注：５行目とD列が空白になっているのは、ピボットテーブルの入力範囲を自動認識させるため。
Ｅ６を選択して「ピボットテーブルとピボットグラフテーブル」を起動すると、「$E$6:$L$484」が自動認識されます。
入力範囲が空白セルで囲まれていない場合、自動で正常に認識できません。
その場合は、手動で範囲を指定しなくてはならず、面倒です。
使用している関数（ヘルプより）
| OFFSET | |
| 基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。 返されるセル参照は、セル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。 書式 OFFSET(基準,行数,列数,高さ,幅) 基準 基準となるセル範囲の参照を指定します。 基準が、セルまたは隣接するセル範囲以外を参照する場合は、エラー値 #VALUE! が返されます。 行数 基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。 行数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 行下方向へシフトします。 行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。 列数 基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。 列数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 列右方向へシフトします。 列数に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。 行数と列数で基準のセル範囲をシフトした結果、オフセット参照の左上隅のセルがワークシートの外へ出てしまう場合、エラー値 #REF! が返されます。 |
|
| VLOOKUP | |
| 指定された配列の左端の列で特定の値を検索し、範囲内の対応するセルの値を返します。 VLOOKUP 関数は、比較する値がデータ テーブルの左端の列に入力され、その位置から指定された列だけ右にある値を取り出す場合に使用します。 比較する値が目的のデータと同じ列にある場合は、HLOOKUP 関数を使用してください。 書式 VLOOKUP(検索値,範囲,列番号,検索の型) 検索値 範囲の左端の列で検索する値を指定します。検索値には、値、セル参照、または文字列を指定します。 範囲 目的のデータが含まれるテーブルを指定します。セル範囲の参照、または List、Database のような名前を指定します。 検索の型に TRUE を指定した場合、範囲の左端の列のデータは、昇順に並べ替えておく必要があります。 並べ替えないと、正しく計算が行われません。 昇順の並べ替えでは、数値は 1 〜 9、アルファベットは A 〜 Z、かなは "あ" 〜 "ん"、ブール型の値は FALSE から TRUE、日付は古い順に配列されます。 検索の型に FALSE を指定した場合は、範囲のデータを並べ替えておく必要はありません。 範囲の左端の列のデータは、文字列、数値、論理値のいずれでもかまいません。 英字の大文字と小文字は区別されません。 列番号 範囲内で目的のデータが入力されている列を、左端からの列数で指定します。 列番号に 1 を指定すると、範囲の左端の列の値が返され、列番号に 2 を指定すると、範囲の左から 2 列目の値が返されます。 列番号が 1 より小さいときは、エラー値 #VALUE! が返され、列番号が範囲の列数より大きいときは、エラー値 #REF! が返されます。 検索の型 検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値で指定します。 TRUE を指定するか省略すると、検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。 FALSE を指定すると、検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。 |
|
| TEXT | |
| 数値を書式設定した文字列に変換します。 書式 TEXT(値,表示形式) 数値 数値、戻り値が数値となる数式、または数値を含むセルの参照を指定します。 表示形式 [分類] ボックス ([書式] - [セル] コマンドの [表示形式] タブ) に表示される数値書式を、半角の二重引用符 (") で囲んで指定します。 解説 表示形式にアスタリスク (*) を使用することはできません。 [表示形式] タブ ([書式] - [セル]) のオプションを使用し、数値を含むセルに数値書式を設定しても、表示が変わるだけで、文字列には変換されません。 TEXT 関数を使用すると、数値は書式設定された文字列に変換され、その計算結果は数値として計算に利用できなくなります。 |
|