フィルター後のデータから一致するデータを別シートから拾って合計を出す

Excel2016使用です。

元データシートにA列～M列の表があります。
1行で1件のデータで、プロジェクト名と場所は重複しません。
担当が最大5名までつき、プロジェクトによって1名や2名の時もあります。
担当者は複数のプロジェクトを担当しますが、プロジェクトごとに人件費が違います。
実際は100行程あります。

プロジェクト名 場所 予算 担当1 人件費1 担当2 人件費2 担当3 人件費3 担当4 人件費4 担当5 人件費5
プロジェクト1 実験室1 800000 佐藤 1000 田中 9000 鈴木 4000 山田 3000 高橋 5000
プロジェクト2 実験室2 400000 田中 2000 佐藤 8000
プロジェクト3 実験室3 200000 田中 5000 山田 7000 佐藤 2000
プロジェクト4 実験室4 700000 山田 3000 高橋 6000 田中 4000 鈴木 2000
プロジェクト5 実験室5 600000 鈴木 4000 佐藤 2000 高橋 8000

入力シートにA列～G列の表があります。日々入力して行が増えていきます。
1行1件のデータです。日付と器具は手入力です。
データの入力規則でプロジェクト名を選ぶと元データシートと同じ場所が入ります。
データの入力規則で担当を選びます。
例えばプロジェクト2の場合、担当は田中か佐藤しか選べません。
以下の表はオートフィルターで担当の佐藤を選んでいます。
フィルターで担当を選ぶと、一番下に表示された中身が2個下のセルに表示されるようになっています。
この場合はD7の佐藤がD9に表示されます。
この状態で、佐藤のプロジェクトごとの人件費の合計を元データシートから拾ってD10に表示させたいです。
この時、重複してる分は1つとして数えて下さい。
この場合はプロジェクト1,2,3,5の佐藤の人件費合計なので、1000＋8000＋2000＋2000で13000と表示させたいです。
フィルターで佐藤以外を選んでも同様になるようにしたいです。
フィルターで担当を選ぶ操作で完結するようにしたいです。
作業列等の追加は可能ですが、現在の状態から行や列を非表示にして表示部分が減るのは×です。

日付 プロジェクト名 場所 担当 器具1 器具2 器具3
4月1日 プロジェクト1 実験室1 佐藤 1 2
4月2日 プロジェクト2 実験室2 佐藤 1 3
4月2日 プロジェクト3 実験室3 佐藤 2 1 1
4月5日 プロジェクト3 実験室3 佐藤 2
4月8日 プロジェクト5 実験室5 佐藤 5 2 2
4月9日 プロジェクト2 実験室2 佐藤 4 1

詳しい方、ご教示下さい。
よろしくお願い致します。

質問者からの補足コメント

• 元データシートが対応する部分をまとめると以下になります。
  
  プロジェクト名 担当 元データのセル番号 元データの人件費
  プロジェクト1 佐藤 E2 1000
  プロジェクト2 佐藤 G3 8000
  プロジェクト3 佐藤 I4 2000
  プロジェクト3 佐藤 I4 2000
  プロジェクト5 佐藤 G6 2000
  プロジェクト2 佐藤 G3 8000
  
  除外するのは、プロジェクト名が重複している5行目（プロジェクト3）と7行目（プロジェクト2）になります。
  分かりづらくて申し訳ございません。
  よろしくお願い致します。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/03/04 09:25

No.3 ベストアンサー 回答者： fujillin 回答日時： 2022/03/04 10:14

こんにちは

人工算出だとすると、「プロジェクト名が重複しているものを除く」という意味がよく分かりませんけれど、それで間違いはなさそうなので・・

作業列を用いても良いということなので、作業列をJ列と仮定しました。
また、「元データシート」のシート名は「Sheet1」と仮定しています。

＞合計を元データシートから拾ってD10に表示させたいです。
D10セルにはデータが入力される可能性があると思いますので、ひとまずフィルターで消えることのないI1セルに合計を出すようにしてあります。
（循環参照にならないようなセル位置なら、合計蘭はどこのセルでもかまいません）


添付図では、上段がフィルター操作前の（仮の）状態を、下段がフィルター後の状態を示しています。
J列を作業列として、その行の担当者に対応する人件費を算出していますが、プロジェクトが重複しているものは空白となるようにしてあります。
I1セルには、SUBTOTAL関数でJ列の合計を求めています。

具体的には、事前に、J2セルに
=IF((D2="")+(COUNTIFS(B$1:B1,B2,D$1:D1,D2)>0),"",INDEX(Sheet1!E:M,MATCH(B2,Sheet1!A:A,0),MATCH(D2,OFFSET(Sheet1!D$1:L$1,MATCH(B2,Sheet1!A:A,0)-1,0),0)))
の式を入力し、充分に下方までフィルコピーしておきます。
I1セルには
=SUBTOTAL(109,J:J)
の式を入れ、非表示の行は合計から外されるようにしておきます。

・添付上段の状態では、全て表示状態なのでJ列全部の合計が表示されています。
・下段のようにフィルターを掛けると、表示されているJ列の合計が算出されるので、ご質問のように「13000」となります。

この回答へのお礼

ご回答ありがとうございます。
実際に使うファイルに合わせてセル番号などを変えて試したところ、ところどころに#N/Aが表示されました。
データは存在しています。

プロジェクト1 佐藤 1000
プロジェクト1 佐藤 空白
プロジェクト2 田中 #N/A
プロジェクト2 田中 空白
プロジェクト5 佐藤 #N/A
プロジェクト2 田中 空白
プロジェクト4 山田 3000

というような感じです。
#N/Aになるパターンがよく分かりませんでした。
入力シートにプロジェクト5の鈴木、佐藤、高橋のデータがあるとすると、最初に出てくる行がすべての担当者で#N/Aです。
プロジェクト1の佐藤しかデータがない場合は正常になっていますが、プロジェクト2の佐藤しかデータがない場合は#N/Aとなっていたりします。
担当者が複数の場合と、担当2～5の場合に#N/Aになっているのが多いです。
実際に使うファイルが列数が多く、合わせて変更したつもりですがこれが数式に影響を与えているのでしょうか？

お礼日時：2022/03/04 17:21

No.7 回答者： fujillin 回答日時： 2022/03/05 13:10

No5です

ご説明がさっぱりわかないのですが・・・

式の説明をしておくと、最終的には
　=INDEX(担当と人件費の表, プロジェクトが合致する行, 担当が一致する列+1）
という形式で求めていますので、それぞれの位置を正しく求めて、表から選べば抽出できるはずです。

表形式が違うのなら、当然ながらそれに合わせた計算にする必要があります。


＞現時点で元データシートに出てこない担当がいます。
元データシートの担当名を連続セルに入れるようにしておけば、それをそのままリストに利用することで、当該プロジェクトに関連する担当者だけを「入力規則」にすることが可能だと想像しますけれど・・・

実際の、データ整備の状況と、利用するタイミング等がどうなっているのかわかりませんけれど、該当者が登録されていない状態で計算しようということが妙に思われます。
全体の流れや関係性をきちんと整理したうえで、再構築なさるのが良いのかも知れません。

この回答へのお礼

ご回答ありがとうございます。
説明下手で申し訳御座いません。
実際使うファイルに合わせて変更しているので合ってると思うんですけど、プロジェクト名と担当が一致していて、正常に数値を拾っているのとエラーになるのがあるのが原因不明です。
入力規則のは、別シート一覧の30名を全員元データシートでは選んではいないという意味でした。（30名中10名しか使ってない）
プロジェクト発足で元データに登録、担当は決まってないから空欄。
プロジェクト進行で入力シートに入力。担当は登録忘れて元データは空欄のままだけど、入力規則に設定している列にはあるから選べる。というような状態のものがありました。
一度整理してみます。
ありがとうございました。

お礼日時：2022/03/05 14:23

No.6 回答者： mike_g 回答日時： 2022/03/04 22:51

[No.4お礼]へのコメント、

》 プロジェクト名の数だけ名前の定義を設定するという意味でしょうか？
はい。

》 プロジェクト名が100行程あり都度追加していきます。
それがどうだと仰るの？
範囲選択⇒Alt+MC⇒“左端列”のみにチェック入れ⇒[OK]
のホンの一瞬の操作ですけどォ～ｯ

この回答へのお礼

フィルターで担当を選ぶ操作で完結するようにしたいです。
と書いている通り、追加の都度名前の定義の設定する操作をしたくないという意味ですけど。

お礼日時：2022/03/05 14:29

No.5 回答者： fujillin 回答日時： 2022/03/04 17:33

No3です。

＞実際に使うファイルが列数が多く、合わせて変更したつもりですが
＞これが数式に影響を与えているのでしょうか？
まずは、変更などせずにそのままテストしてください。

ご質問文にご提示のデータ（担当5まで）に対応して作成してありますので、担当5までに存在しない担当者を指定した場合は（当然ですが）エラーになります。

この回答へのお礼

ご回答ありがとうございます。
こちらに投稿用に作成したものではうまくいきました。
実際に使うファイルですが、

元データシート
5行目が見出し行、6行目から中身です。
A列番号、B列プロジェクト名、C~K項目1~9、L列場所、M列項目10、N列～AG列が担当1、人件費1、担当2、人件費2…と人件費10までです。

入力シート
2行目が見出し行、3行目から中身です。
A列日付、B列番号、C列プロジェクト名、D列場所、E列担当、F列～Y列まで器具1～20です。

元データシートと入力シートの担当名は別のシートのA列を入力規則で設定しており30名程いて、現時点で元データシートに出てこない担当がいます。

これに合わせてAB3セルに教えて頂いた数式を入力しました。
=IF((E3="")+(COUNTIFS(C$2:C2,C3,E$2:E2,E3)>0),"",INDEX(元データ!O:AG,MATCH(C3,元データ!B:B,0),MATCH(E3,OFFSET(元データ!N$5:AF$5,MATCH(C3,元データ!B:B,0)-1,0),0)))

状況としては、
担当1、人件費1は数値を拾ってきているけど、担当2以降は#N/Aになっているものがあります。
この中で1件だけ、元データで設定していない担当者を入力シートで入力していて人件費を拾っているものがあります。（プロジェクト名は元データにあります）
人件費はプロジェクト名の行に入力されているもの一致していて、担当は1つ下のプロジェクト名のセルと一致しています。

元データで担当1、人件費1までしかないもので、入力シートで担当1を選んでも#N/Aになっているものがあります。
元データで担当4、人件費4まで設定されているもので、入力シートで担当1～4を選んだ分がすべて#N/Aになっているものがあります。
担当が2以降のものは全部#N/Aになっているようです。

複数人が使用しているファイルで、元データで設定していないものを入力していたり空欄だったり、把握しきれていない部分があるのですが、こういうおかしい部分で1つでも#N/Aになっているのがあると、この部分以外も全部おかしくなるというような事でしょうか？
よろしくお願い致します。

お礼日時：2022/03/05 11:42

No.4 回答者： mike_g 回答日時： 2022/03/04 12:46

添付図参照(Excel 2019)

I2: =COUNTIF(B$2:B2,B2)
J2: =INDEX(INDIRECT(B2),MATCH(D2,INDIRECT(B2),0)+1)*(I2=1)
J8: =SUM(J2:J7)

この回答へのお礼

ご回答ありがとうございます。
プロジェクト名の数だけ名前の定義を設定するという意味でしょうか？
実際に使うファイルが、プロジェクト名が100行程あり都度追加していきます。

お礼日時：2022/03/04 17:00

No.2 回答者： 銀鱗 回答日時： 2022/03/04 10:12

SUBTOTAL関数を使えば良さそうな ”気がする” んですけど、いかがでしょう。

非表示の行を無視してくれます。
これを条件で値を表示させる作業列に対して行えば良いと思います。


・・・余談・・・

「教えて!goo」の仕様では、
　・連続した半角スペースは一つの半角スペースに置き換わる。
　・行頭の半角スペースは省略される。
　・文頭の全角スペースは省略される。
　・Tabは省略される。
……ので、表のような形で表示させたい場合はチョットだけ気を遣うようにしましょう。

この回答へのお礼

ご回答ありがとうございます。
気を付けます。

お礼日時：2022/03/04 16:57

No.1 回答者： mike_g 回答日時： 2022/03/04 08:28

》 プロジェクト1,2,3,5の佐藤の人件費合計なので、

》 1000＋8000＋2000＋2000で13000と表示させたい
それって、「入力シート」の１～５行目が参照するデータですよね？
同シートの６、７行にも「佐藤」が登場してるけど、それらを除外する理由をご説明ください。