エクセル関数を使って売上比較表を作成

質問

【Ｓｈｅｅｔ１】～【Ｓｈｅｅｔ３】の３つを統合して、３年間の売上推移表を作成しています。
その年によって顧客の入れ替わりがあり、その年に受注のない顧客のセルには、ゼロを入力します。
こちらのＱ＆Ａもかなり参考にさせていただきましたが、関数の基礎がないため未完成の状態です。
皆様のお力をお借りできればと思います。よろしくお願いします。

回答No.1

KURUMITO · ベストアンサー

シート1からシート3まではA2セルに顧客名が有り、D2セルまでに項目名が入力されているとします。
各項目のデータは3行目から下方に入力されているとします。
各シートには作業列を作って対応します。
シート1のE3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(COUNTIF(A$3:A3,A3)=1,MAX(E$2:E2)+1,""))

シート2のE3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(COUNTIF(Sheet1!A:A,A3)>0,"",IF(COUNTIF(A$3:A3,A3)=1,MAX(Sheet1!E:E)+COUNT(E$2:E2)+1,"")))

シート3のE3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF((COUNTIF(Sheet1!A:A,A3)+COUNTIF(Sheet2!A:A,A3))>0,"",IF(COUNTIF(A$3:A3,A3)=1,MAX(Sheet2!E:E)+COUNT(E$2:E2)+1,"")))

そこでお望みの表ですがシート4に作成することにして次のようにします。
2行目までは作業のための行とします。
B1セルにはSheet1、C1セルにはSheet2、D1セルにはSheet3と入力します。
A2セルには0と入力します。
B2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B$1="","",MAX(INDIRECT(B$1&"!E:E")))

A5セルには顧客名と入力したのちにB5セルにはシート1のB2セルからD2セルの項目をコピーして貼り付け、その後にそれらの項目名を右横方向にドラッグコピーします。
B4セルにはSheet1と入力したのちにB４セルからD4セルを結合セルにします。その後にそれらを右横方向にドラッグコピーします。
A6セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX($2:$2),"",INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1)-0.1,$2:$2,1)+1)&"!A:A"),MATCH(ROW(A1),INDIRECT(INDEX($1:$1,MATCH(ROW(A1)-0.1,$2:$2,1)+1)&"!E:E"),0)))

B6セルには次の式を入力したのちに横方向にJ6セルまでドラッグコピーし、その後下方にもドラッグコピーします。

=IF(OR($A6="",COLUMN(A1)>3*3),"",IFERROR(INDEX(INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:D"),MATCH($A6,INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:A"),0),MOD(COLUMN(A1)-1,3)+2),""))

この式の場合には該当するシートに顧客名が記載されていない場合には空白のセルとなるようになっています。
空白ではなく0に表示するのでしたら次の式を使ってください。

=IF(OR($A6="",COLUMN(A1)>3*3),"",IFERROR(INDEX(INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:D"),MATCH($A6,INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:A"),0),MOD(COLUMN(A1)-1,3)+2),0))

なお、これらの式はエクセルのバージョンが2007以上の場合です。そうでない場合には次のような式に変えることが必要です。

=IF(OR($A6="",COLUMN(A1)>3*3),"",IF(ISERROR(INDEX(INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:D"),MATCH($A6,INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:A"),0),MOD(COLUMN(A1)-1,3)+2)),"",INDEX(INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:D"),MATCH($A6,INDIRECT(INDEX($1:$1,ROUNDUP(COLUMN(A1)/3,0)+1)&"!A:A"),0),MOD(COLUMN(A1)-1,3)+2)))

お礼

KURUMITO様
お礼が遅くなって申し訳ありませんでした。
早速のご教授、本当にありがとうございました。社内では誰も理解できないこの数式を相手に、現在、一人苦戦しながら作表をしております。結果はあらためてご報告申し上げます。取り急ぎお礼を申し上げます。