エクセル2010 合計値について

質問

お世話になります。
添付画像のように下記4つのシートで構成されたエクセルファイルがあります。
【名簿　シート】
【テスト1　シート】
【テスト2　シート】
【合計　シート】

【名簿　シート】をデータベースとして、名前及び所属を他のシートが参照
している状態です。

表示の順番を変更しなければ添付画像(1)のようにＢさんの合計値も問題無いの
ですが、例えば【テスト2　シート】で順列を変更することで、
【合計　シート】の参照セルの位置は変わらないため、添付画像(2)のように
Ｂさんの合計値にＤさんの点数が合わさってしまいます。

途中でシートの順番を並び替えても対象となる人に対して数字が連動して
合計値が算出される関数はどのように組めば宜しいでしょうか？

≪目的≫
セルの数値を入れ替えても“対象者＝点数”を選択する計算式を作りたい。
そのために【合計　シート】のＢさん（Ｓ6）に正しい計算式をいれる。

宜しくお願い致します。

回答No.1

keithin

元のエクセルの作り方が悪いのでどんどん悪循環にはまっていく、初心者さんに典型的な症状ですね。

とは言え、エクセルの作り方を今から全部やり換えることも出来ないので（やればできる事でもやりたくないので）、今のままとにかく正しい結果を計算するには

合計シートのＣ２セル：
=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2) + SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2)
といった具合に、各シートごと「Ａさん」「店名」をイチイチ検索して正しい値を引っ張ってきて加算する、こういったアタマの悪いやり方をするしかありません。

お礼

ご回答いただきありがとうございます。
スマートなやり方があれば是非とも教えてください。

宜しくお願い致します。