私は、多数の従業員の毎日の出勤記録を追跡するための Excel スプレッドシートを作成しています。ここで示すすべてのデータ例は、データ構造を含めて架空のものです。厳重にロックダウンされたコンピューター (つまり、インターネットなし、VBA などのスクリプト形式は禁止、データ操作には Microsoft Office スイートのみ) で機密性の高い個人データを扱っているため、説明のためにデータ構造の要点のみを示しています。
人事記録を格納するためのメイン テーブルは次のようになります。
Master List
ID Name Home Address Phone Number Remarks
1 Alice 12 Somewhere Rd. 123456 remark 1 goes here
2 Bob 23 Someplace Dr. 234567 remark 2 goes here
3 Charlie 34 Somewhen Blvd. 345678 remark 3 goes here
ご覧のとおり、出席を取る際にすべての情報が必要なわけではありません。そのため、Power Queryを実行して、必要な情報だけに絞り込みます。
Attendance
ID Name Remarks
1 Alice remark 1 goes here
2 Bob remark 2 goes here
3 Charlie remark 3 goes here
その後、表の右側に出席エントリを追加しました。
Attendance
ID Name Remarks 2020/6/25 2020/6/26 ...............
1 Alice remark 1 goes here P P ...............
2 Bob remark 2 goes here P VL ...............
3 Charlie remark 3 goes here P P ...............
最後に、私自己参照テーブルを設定するこれにより、出席テーブルを更新するたびに、出席エントリが元の行に保持されるため、途中のどこかに人員を挿入しても問題は発生しません。
しかし、特別な出勤エントリ (病気休暇など) のすべてに、休暇自体に関する詳細をタグ付けする必要があることに気付き、すぐに問題に遭遇しました。最初は出勤セルに直接コメントを追加しようとしましたが、コメントは Power Query によって生成されたテーブルに従わないことに気付きました (つまり、元の絶対セル位置にとどまり、新しい人物を挿入すると、テーブルの下半分が 1 行だけずれてしまいます)。
その後、ハイパーリンクを使用して、各出勤エントリを休暇に関するコメント/詳細を含む別のテーブルにリンクしようとしましたが、クエリの更新後にリンクがクリックできなくなりました。
1 日 1 人あたりの出勤管理システムのコメント システムを設計するにはどうすればよいでしょうか。また、最初にマスター リストをクエリし、列を減らしてから列を追加するというアプローチは、そもそも正しいアプローチでしょうか。
ありがとう！
答え1
最初にマスター リストをクエリし、列を減らしてからさらに列を追加するという私のアプローチは、そもそも正しいアプローチでしょうか?
これは 1 つの方法です。日付ごとにコメント列を追加し、すでに実行したアプローチに従って自己参照テーブルを作成するだけで、コメント用のスペースを確保できると思います。次のようになります。
個人的には、私はそうしません。なぜなら、いつものように「アリスとボブが過去30営業日に一緒に欠勤した回数を教えてください。」や「欠勤日数の平均を教えてください。」などの質問が来た場合、それに対処するのがより困難になるからです（それ公平にするのは難しいですが、それでもです。
したがって、入力には正規化されたデータを使用し、それをレポート用にピボット処理することを好みます。
それを念頭に置いて、私は 2 つのテーブルから始めました。
- 人々
- 日付
Include 列では次の式を使用します (理由は後で明らかになります)。
=[@Dates]<=TODAY()
これらの各テーブルに対してクエリを作成しました。日付クエリでは、Include=TRUE をフィルターし、Include 列を削除しました。次に、日付クエリを参照するために、人クエリにカスタム列を追加しました。次のようになります。
3 番目の列が人クエリに追加されたことがわかります。これを展開すると、すべての人のすべての日付のわかりやすいリストが表示されます (これは基本的にデカルト結合です)。
これをワークブックに読み込み、出席とコメントの 2 つの列を追加します。これの利点は、各人日に注釈やマークアップを付けたい列を必要なだけ追加できることです。
この時点で、私はリンク投稿で参照したページです。最初は間違えました。その後また間違えました。そして、それが実はうまく機能し、自己参照テーブルが作成されました。これにより、すべての情報が適切な場所に保持されたまま、自分の出勤状況やコメントを編集したり、新入社員向けに更新したりできるようになりました。
これのもう 1 つの利点は、毎日ワークブックを開いてクエリを更新すると、現在の日付の各従業員の新しい行セットがクエリに自動的に追加されることです (これが、日付テーブルで Include 列を定義する最終的な目的です)。
編集：
PowerQuery を使用すると、投稿の 3 番目のテーブルの形式を含むさまざまなレポート形式にデータを変換できます。
上記の回答の最終結果に対してクエリを作成し、次の操作を実行します。
出席と備考を選択し、変換 > 任意の列 > 列のピボット解除 > 選択した列のみのピボット解除を使用します。
このアクションにより、null セルが削除されることに気付くでしょう。これらのセルを保持したい場合は、Unpivot を使用する前に入力する必要があります。これは、null から任意の値に値を置換することで実行できます。
ピボット解除後、列 Attribute と Value が作成されます。ここで、date.Dates と Attribute を選択し、右クリックして [列の結合] を使用します。区切り記号を " - " に設定し (もちろん、好きなものを使用できます)、日付とそれが参照するデータのタイプ (出席またはコメント) を表す単一の列が作成されます。
次に、この新しい結合された列を選択し、[変換] > [任意の列] > [ピボット列] を使用して、次のように構成します。
最終結果は、あなたが求めていた形式です。繰り返しになりますが、出席が入力されているかどうかに関係なく、このレポートにすべての人を含める場合は、Unpivot を使用する前に出席列にダミー値を設定する必要があります。
口語表現をお許しいただければ、これはすべて、やや家中をうろうろしているように思えるかもしれません。一般的に言えば、ソリューションを決定する前に、このデータがどのように使用されるかについて考えることをお勧めします。私の経験では、最初にデータを正規化し (私が提案したように)、そのテーブルからレポートを作成する方が、より持続可能な方法であり、より柔軟性があります。