社内のジョブ原価計算用の提案/価格表を更新しようとしています。プロジェクト リーダーは従業員の月ごとの時間を見積もり、その見積もりに料金を適用して合計価格を算出します。すべてのジョブですべての従業員が使用されるわけではありません。
| 従業員数/時間 | 1月21日 | 2月21日 | 3月21日 |
|---|---|---|---|
| ジョンスミス | 10 | 15 | 20 |
| ジェーン・ドウ | 20 | 20 | 10 |
料金表は別のタブにあり、列 A にすべての従業員がリストされ、列 B 以降に各従業員の月ごとの時給が、時間見積と同じ日付ヘッダーで表示されます。料金表には、時間タブの月よりも前または後の月も含まれる場合があります。
| 料金表 | 12月21日 | 1月21日 | 2月21日 | 3月21日 |
|---|---|---|---|---|
| ジョンスミス | 12ドル | 12ドル | 12ドル | 13ドル |
| ジム・ドゥ | 13ドル | 13ドル | 13ドル | 13ドル |
| ジェーン・ドウ | 12ドル | 12ドル | 13ドル | 13ドル |
これまでは、時間見積タブの上部に時間表を配置し、そのすぐ下に別の表を配置して、INDEX MATCH MATCH を使用して従業員ごとの月別の適切な料金を見つけ、それをその従業員の上の勤務時間に掛けていました。これはうまくいきましたが、そうすると各従業員の月別の総コストを表示しなければならなくなり、これは本当に必要ありません。各月の列の下にその月の総コストを 1 行表示し、何度も数式を書かなくて済むようにしたいです。
=(B2 * INDEXMATCHMATCH)+(B3 * INDEXMATCHMATCH)+(B4 * INDEXMATCHMATCH)
[明らかにこれは実際の数式ではありません。非常に長くなるからです]。
列 B の時間を、その月の従業員の関連レートで乗算し、1 つのセルに合計する方法はありますか?
| 従業員数/時間 | 1月21日 | 2月21日 | 3月21日 |
|---|---|---|---|
| ジョンスミス | 10 | 15 | 20 |
| ジェーン・ドウ | 20 | 20 | 10 |
| 総費用 | 360ドル | 380ドル | 390ドル |
答え1
ぜひ試していただきたい方法は次のとおりです。
=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)
(私はいくつかの方法を見つけました。いくつかは興味深いものでしたが、それらはこの式よりも長く、書き出されており、あなたはそのINDEX/MATCHアプローチに慣れているようなので、おそらく使用および維持が簡単でしょう。)
したがって、記述されている数式では、データ テーブルを「レート テーブル」の場合は A1:E4 に、また「従業員/時間」テーブルの場合は G1:J3 にコピーしました。数式のアドレス指定は、これらの範囲で機能します。実際の状況に合わせて調整する必要があります。
最後まで読んでください。私が愚かにも出力テーブルについて話しているからといって、心配しないでください。(質問と回答を読み直したときに初めて、結果を「従業員/時間」テーブルのデータ行の下に表示したいとおっしゃっていることに気付きました。) 上記の数式は、正式な出力テーブルを生成する際に参照を修正したため、その目的に適っており、必要に応じて貼り付けることができます。)
出力テーブルを作成するために、=H1出力テーブルの左と上のヘッダー (名前と月) を入力するような数式を使用しました。これらは明らかに既知のものなので、設定する計算に応じて必要に応じて簡単に調整できます。同様に、私は標準的な=SUM(M2#)合計数式を使用しました。これは、最初の行の数式が生成する SPILL 配列全体を参照するため、調整が不要であるという利点があります。
私が試したすべてのアプローチの本当の難しさは、私が最終的に得た式が二最終出力に SPILL 配列を含めることはできませんが、Excel ではそのようなことは評価されません。それぞれに異なる用途があるため、出力テーブルのコスト領域を構成するセル ブロックの左上セルに 1 つの数式を配置できる単一の SPILL 配列にまとめることができませんでした。SPILL 配列を横方向ではなく縦方向に行うことに決めた後、問題の「核心」である従業員 1 人あたりの月間コストに取り組む準備ができました。
いわゆる「本質」、つまり特定の職務における各従業員の 1 か月あたりのドル費用については、次の式を使用します。
まずINDEX/MATCH、: (現時点では最初の月の列) は、出力テーブルの月を「レート テーブル」の列と照合し、ドル領域の列 2 が必要であることを検出します。ドル領域を INDEX すると、1 列 3 行の出力配列サイズが与えられ、これは外側のXLOOKUP()1 列 3 行のルックアップ配列に対応します。このように一致しない場合は、エラーが発生します。次に、XLOOKUP()従業員の名前を検索します。これには、出力テーブルにリストされているすべての従業員の出力を取得するために配列 ($L2#この場合は) を使用します。これらは、必要に応じて下方向に SPILL されます。
次に、出力テーブルの各列に数式をコピーすると、すべての従業員の毎月のコストが入力され、合計が更新されます。
(ちなみに、サンプル出力の「Feb/21」の合計は正しくないため、数式では 380 ドルではなく 440 ドル (15*$12=$180、20*$13=$260、$180+$260=$440) が生成されます。)
したがって、修正するには、出力テーブルの最初の数式を選択し、 を押してF2-Edit数式を強調表示してコピーし、「従業員/時間」テーブルに移動して、時間データの後の最初の空の行に「=SUM(」と入力し、コピーした数式を貼り付けて「)」と入力し、Enter キーを押すだけで済みました。参照を調整して「従業員/時間」テーブルのデータと一致させると、セルには不要な出力テーブルで計算された情報の合計が表示されました。セルをコピーして右側に貼り付けると、必要な出力が得られました。
最後に、合計コスト セル全体で 1 つの SPILL 配列を使用できるようにするのに役立ちませんでした。残念なことに、使用されている列の数に関係なく、コピーして貼り付ける必要があります。