多くのソリューションでは、単純な関数やフィルター以上のものが必要です。例えば、会社が出張手当を支給している場合、出張日には全従業員に同額を加算するでしょう。では、勤務地ごとに異なる手当を支給し、従業員が1日に複数の手当を受け取る可能性があるとしたらどうでしょうか？ 解決策は思ったほど難しくありませんが、出張日に一定額を加算するためにIF()文を使用するよりも複雑です。
この記事では、VLOOKUP() 関数、データ検証リスト、ピボットテーブルを組み合わせて、オフサイト勤務の従業員への手当の支給状況を追跡するシンプルなアプリケーションを作成します。このアプリケーションは柔軟な構造になっているため、1日に複数のオフサイト勤務を行う従業員にも対応できます。VLOOKUP() 関数は、各勤務地レコードの正しい手当額を返します。データ検証コントロールは、入力を特定の勤務地に限定することで、タイプミスや無効な勤務地を回避します。最後に、ピボットテーブルを使用して、従業員ごとに日付別に支給された手当の合計額を返します。
私はOffice 365のExcel（デスクトップ版）を使用していますが、以前のバージョンでもご利用いただけます。ご自身のデータを使用することも、デモ用の.xlsxファイルと.xlsファイルをダウンロードして使用することもできます。多くのソリューションとは異なり、このソリューションはブラウザ版で作成・使用できます。VLOOKUP()関数や検証リスト、ピボットテーブルの作成方法についての知識は必要ありませんが、これらの機能について知っておくと役立ちます。
参照: Windows 7 からの移行戦略の選択: 4 つのオプション (Tech Pro Research)
問題点
図Aに示すシンプルなシートには、2つのTableオブジェクトが含まれています。左側のTableオブジェクトは、各従業員が特定の職場で働いた時間を追跡します。右側のTableオブジェクトには、各職場とその日額の手当がリストされています。金額を記憶して時間単位の記録と一緒にリストすることもできますが、そうすると問題が発生します。間違った金額を入力してしまう可能性があり、手動で値を入力するたびにタイプミスが発生するリスクがあります。安定したリストを作成することで、データの妥当性を確保できます。しかし、どのように一致させるのでしょうか？最も簡単な答えは、VLOOKUP()関数を使用することです。
先に進む前に、いくつか注目すべき点があります。
- 各従業員は1日に1つ以上の現場を訪問する可能性があります。つまり、それらの金額を合計して日額手当を算出しなければなりません。
- 各従業員は数時間または丸一日を自宅オフィスで過ごすこともありますが、これには手当は支給されません。
- データ入力は非常に重要です。エラーを避けるため、時間単位リストの場所の値は、手当リストの場所の値と一致している必要があります。
この記事全体を通して、左側のリストを時間給リスト、右側のリストを手当リストと呼びます。Tableオブジェクトを使用しているため、参照を更新することなく手当リストを簡単に変更できます。通常のデータ範囲をTableオブジェクトに変換するには、以下の手順に従います。
- データ範囲内の任意の場所をクリックします。
- [挿入]タブをクリックし、[表]グループの[表]をクリックします。
- データにヘッダーがあるかどうかを示します (デモ データにはヘッダーがあります)。
- [OK]をクリックします。
独自のデータで作業している場合は、Table オブジェクトを使用する必要はありませんが、この記事の残りの部分では、使用するものと想定しています。
VLOOKUP()関数
各職場の手当額を時間単位リストに追加する最も簡単な方法は、次の構文を使用して VLOOKUP() 関数を追加することです。
VLOOKUP(lookup_value, table, column_index, range)
ここで、検索値は、検索する時間別リストの値 (この場合は場所 (列 E)) を含むセルまたは範囲です。テーブルは、検索テーブル (H4:I6 (ヘッダーを除いた奨学金リスト テーブル)) を識別します。列インデックスは、検索値 (奨学金) に関連して返す値を含む列を表します。範囲は、完全一致を強制する (または強制しない) TRUE/FALSE 値です。
ここで、セル F4 に次の VLOOKUP() 関数を入力します。
=VLOOKUP([Location],Table2,2,FALSE)
Table オブジェクトを使用していない場合は、代わりに次の関数を入力します。
=VLOOKUP($E$4:$E$10,$H$4:$I$6,2,FALSE)
2 つの範囲 (通常のデータ範囲の場合) は絶対参照である必要があることに注意してください。
図Bは、新しい列を通貨として書式設定し、ヘッダーテキストを追加した結果を示しています。表は自動的に調整され、新しい列が挿入されます。
明らかに問題が2つあります。本社とMcValey（行8）の両方が同じエラーメッセージを返します。行4のエラーは簡単に修正できますが、行8のエラーは少し手間がかかります。
問題1
Excelは4行目でエラーを返します。これは、奨学金リストに一致する場所の値がないためです。このエラーを修正するには、図Cに示すように、奨学金リストに新しいレコードを追加するだけです。ご覧のとおり、この簡単な修正で4行目のエラーは解決されます。これがTableオブジェクトを使用する理由です。VLOOKUP()関数は新しい行を自動的に更新するため、関数を変更する必要はありません。
問題2
4行目のエラーは簡単にトラブルシューティングして修正できました。8行目のVLOOKUP()関数がエラーを返す理由を特定できますか？ 給与リストにはMcValleyの求人サイトのレコードがあるので、次に確認すべきは時間給リストの「場所」の値です。給与リストの値と完全に一致していますか？ ああ！ そうです、lが抜けています。場所のスペルミスです。
最も簡単な解決策はタイプミスを修正することですが、将来的に新たなタイプミスを完全に防ぐことはできません。代わりに、時間別テーブルにデータ検証リストを追加しましょう。これにより、ユーザーが入力できる項目がリスト内のみに制限され、将来のエラーを回避できます。具体的には、リストには給付金リストの場所の値が含まれるため、各場所を手動で入力する代わりに、リストを使用して場所を入力できます。
次のようにリストを追加します。
- E4:E10 を選択します (検証リストを E3 に追加しても、既存のレコードまたは新しいレコードにコントロールは追加されません)。
- データ タブをクリックし、データ ツール グループの [データの入力規則] をクリックします。
- 表示されるダイアログで、[許可] ドロップダウンから [リスト] を選択します。
- ソース管理の奨学金グループ内の場所の値を指定します (図 D )。
- [OK]をクリックします。
次に、E8を選択し、検証コントロールのドロップダウンリストを使って「McValley」と入力します（図Eを参照）。ご覧の通り、場所の値のスペルを修正すると、VLOOKUP()関数が期待通りに動作します。さらに、コントロールは動的に動作します。まさにテーブルマジックの真髄です。給付金リストを更新すると、検証コントロールのリストも更新されます。通常のデータ範囲では、このようなことはできません。
VLOOKUP() エラーの詳細については、「VLOOKUP() 式のトラブルシューティング」を参照してください。
毎日の合計
元の構造に内在する2つの問題を修正した結果、各拠点レコードの手当合計額を取得できるようになりました。ただし、現在の時間単位のリスト構造では、各従業員の1日の合計額は返されません。従業員は1日に複数の拠点で働く場合があることに注意してください。例えば、E-3は5月1日に2つの拠点で勤務し、どちらの拠点にも手当のレートが適用されているとします。この場合、E-3は5月1日の手当として合計75ドルを受け取るべきであり、各拠点のレートである40ドルや35ドルを受け取るべきではありません。
これを実現する方法はいくつかありますが、おそらく最も簡単なのはピボットテーブルを使うことです。これを行うには、時間別リスト内の任意の場所をクリックし、「挿入」タブをクリックします。次に、以下の操作を行います。
- 「テーブル」グループの「ピボットテーブル」をクリックします。表示されるダイアログで「OK」をクリックします。
- ピボットテーブル フレーム内をクリックすると、リスト ペインが表示されます。
- フィールド ペインで、従業員および現場手当フィールドをチェックします (図 F )。
現状では、ピボットテーブルには各従業員の総計が表示されています。図Fに示すように、E-3の合計は75ドルです。これは、同じ日に2つの拠点で発生した合計です。日別の小計が必要な場合は、図Gに示すように、ピボットテーブルに日付フィールドを追加してください。
ピボットテーブル オブジェクトの詳細については、「便利なヒントを使用して Excel ピボットテーブルを最大限に活用する」を参照してください。
コンボソリューション
よくあることですが、効率的な解決策には複数の取り組みを組み合わせる必要がありました。今回は、VLOOKUP()関数を使用して、追跡リストに重要な詳細を追加しました。具体的には、勤務地ごとに手当の金額を追加しました。次に、ピボットテーブルを使用して、2つの異なる方法でこれらの手当率を集計しました。今後の記事では、Power Queryを使用してこのソリューションシナリオを引き続き紹介します。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
参照
- Word の差し込み印刷で条件付きフィールドを使用する方法 (TechRepublic)
- Office Q&A: 見出しの折りたたみと送信遅延設定は万能ではないが、それに近い効果はある (TechRepublic)
- Word の定型句とオートコレクトエントリを文書化する方法 (TechRepublic)
- Word のキーボード コマンドを文書化する方法 (TechRepublic)
- シンプルなフォーマットをいくつか使って、普通のスパークラインを意味のある情報に変える方法 (TechRepublic)
- Microsoft Word と Excel の無料代替ソフト 10 選 (TechRepublic ダウンロード)
- ビジネス向け Microsoft Office 365: 知っておくべきことすべて (ZDNet)
- 史上最も重要なiPhoneアプリ10選（Download.com）
- オンラインでデータのプライバシーを守るには努力が必要です。これらのアプリが役立ちます（CNET）
- プログラミング言語と開発者キャリアリソースのカバー範囲（Flipboard の TechRepublic）
