エクセルでの集計方法

質問

A列に商品名、B列に出荷した数量が入力された一枚のシートがあり、これが12ヶ月分＝12枚のシートがあります。これら全てのシートを集計し、それぞれの商品が12ヶ月でいくつ出荷されたのかを集計したいです。
この場合は串刺し演算を応用するのでしょうか？・・・・
初心者で申し訳ありませんが、最も効率的な方法をご教授いただければと思います。

回答No.4

kagakusuki · ベストアンサー

>12枚のシートにて、「商品名」、「数量」の他に、「単価」を集計したい場合、どの部分の関数を変更すれば良いでしょうか？

　今仮に、Sheet1～Sheet12のC列に単価が入力されているものとします。
　その場合、まず、作業用シートのE2セルに入力する関数を次の様なものに変更してから、作業用シートのE2セルをコピーして、作業用シートのE3以下に貼り付けて下さい。

=IF(ISERROR(1/(ROWS($2:2)<LOOKUP(9E+307,$B:$B)+0)),"",INDIRECT("'"&INDEX($A:$A,MATCH(ROWS($2:2),$B:$B)+1)&"'!A"&ROWS($2:2)-INT(LOOKUP(ROWS($2:2),$B:$B))+ROW(Sheet1!$B$3))&TRIM(TEXT(SUM(INDIRECT("'"&INDEX($A:$A,MATCH(ROWS($2:2),$B:$B)+1)&"'!C"&ROWS($2:2)-INT(LOOKUP(ROWS($2:2),$B:$B))+ROW(Sheet1!$B$3))),"[=0] ;◆-0000000;◆0000000")))

　尚、上記の関数は、単価が9,999,999円までの場合にしか対応しておりません。
　もし、単価の桁数が7桁を上回る事もあり得る場合には、上記の関数内に2箇所ある「0000000」の部分の0の個数を、想定される最高額の桁数に合わせて増やして下さい。

　次に、集計用シートのA4セルに入力する関数を次の様なものに変更して下さい。

=IF(ROWS($4:4)>COUNT(作業用!$D:$D),"",LEFT(VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE),FIND("◆",VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE)&"◆")-1))

　次に、集計用シートのB4セルに入力する関数を次の様なものに変更して下さい。

=IF($A4="","",SUMIF(作業用!$E:$E,VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE),作業用!$F:$F))

　次に、集計用シートのC4セルに次の関数を入力して下さい。

=IF(ROWS($4:4)>COUNT(作業用!$D:$D),"",REPLACE(VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE),1,FIND("◆",VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE)&"◆"),)+0)

　次に、集計用シートのA4～C4の範囲をコピーして、同じ列範囲の4行目以下に貼り付けて下さい。

　その他は回答No.3と同様です。

お礼

細かくご回答いただいたにも関わらず、お礼遅くなりまして申し訳ありません。少しバタバタしていました。
ご回答に基づいて編集することができました。画像なども付けてくださり、本当にありがとうございました。

回答No.3

kagakusuki

>この場合は串刺し演算を応用するのでしょうか？

　確かに串刺し計算を使用した方が簡単ですが、同一の商品のデータを入力する位置は、12枚のシートに全てに亘って、必ず同じセルとしなければならず、商品名が並んでいる順序を順不同にする事も出来ない事は無論の事、例え12箇月の間に途中で出荷しなくなった商品があったとしても、空いた行を詰めて次の行に入力されていた商品のデータを入力したり、新に別の商品のデータを入れる事は出来ません。（正確に言いますと、その様な事をすると合計値として誤った値が表示されてしまいます）

　以下の方法は、必ずしも効率的とは申せませんが、12枚の各月のシートに商品名を入力する際に順序を気にする事無く入力する事が出来、その月に出荷した商品のデータのみを入力するだけで済む方法です。

　今仮に、月毎のデータを入力するための12枚のシートのシート名がSheet1～Sheet12であるものとし、それらのシートの3行目に「商品名」や「数量」といった項目名が入力されていて、実際の商品名データや数量データは4行目以下に入力されているものとします。

　まず、各月のデータを入力する12枚のシートの他に、新たに2枚のシートを作成し、その各々のシート名を例えば「集計用」と「作業用」等として下さい。
　次に、作業用シートの
A2セルに 　　　Sheet1
A3セルに 　　　Sheet2
A4セルに 　　　Sheet3
A5セルに 　　　Sheet4
A6セルに 　　　Sheet5
A7セルに 　　　Sheet6
A8セルに 　　　Sheet7
A9セルに 　　　Sheet8
A10セルに　　　Sheet9
A11セルに　　　Sheet10
A12セルに　　　Sheet11
A13セルに　　　Sheet12
という具合に、各月のデータを入力する全てのシート名を残らず入力して下さい。（入力する順序は必ずしも1月から12月までの順番ではなくとも構いません）
　次に、作業用シートのB1セルに0.5と入力して下さい。
　次に、作業用シートのB2セルに次の関数を入力して下さい。（式の中で指定されているSheet1!$B$3セルは、項目名が入力されているセルの事です）

=IF($A1="","",MAX(B$1:B1)+IF(ISNUMBER(1/COUNT(INDIRECT("'"&$A2&"'!B:B"))),MATCH(9E+307,INDIRECT("'"&$A2&"'!B:B"))-ROW(Sheet1!$B$3),0))

　次に、作業用シートのB2セルをコピーして、作業用シートのB3～B13の範囲に貼り付けて下さい。（誤ってB14以下にまで貼り付けてしまったとしても、特に問題にはなりません）
　次に、作業用シートのE2セルに次の関数を入力して下さい。

=IF(ISERROR(1/(ROWS($2:2)<LOOKUP(9E+307,$B:$B)+0)),"",INDIRECT("'"&INDEX($A:$A,MATCH(ROWS($2:2),$B:$B)+1)&"'!A"&ROWS($2:2)-INT(LOOKUP(ROWS($2:2),$B:$B))+ROW(Sheet1!$B$3))&"")

　次に、作業用シートのF2セルに次の関数を入力して下さい。

=IF($E2="","",INDIRECT("'"&INDEX($A:$A,MATCH(ROWS($2:2),$B:$B)+1)&"'!B"&ROWS($2:2)-INT(LOOKUP(ROWS($2:2),$B:$B))+ROW(Sheet1!$B$3)))

　次に、作業用シートのG2セルに次の関数を入力して下さい。

=IF($E2="","",IF(COUNTIF(E$1:E2,$E2)=1,"#"&$E2,""))

　次に、作業用シートのD2セルに次の関数を入力して下さい。

=IF($G2="","",COUNTIF($G:$G,"*?")-COUNTIF($G:$G,">"&$G2))

　次に、作業用シートのD2～G2の範囲をコピーして、同じ列範囲の3行目以下に（商品の種類数の12倍の数を上回るのに十分な行数となるまで）貼り付けて下さい。

　次に、集計用シートのA4セルに次の関数を入力して下さい。

=IF(ROWS($4:4)>COUNT(作業用!$D:$D),"",VLOOKUP(ROWS($4:4),作業用!$D:$E,2,FALSE))

　次に、集計用シートのB4セルに次の関数を入力して下さい。

=IF($A4="","",SUMIF(作業用!$E:$E,$A4,作業用!$F:$F))

　次に、集計用シートのA4～B4の範囲をコピーして、同じ列範囲の4行目以下に（商品の種類数を上回るのに十分な行数となるまで）貼り付けて下さい。

　これで、集計用シートの4行目以下に、各商品名毎の年間の出荷数量が表示されます。

補足

とてもご丁寧にご回答いただき、大変恐縮です。本当にありがとうございます。
一度にお聞きすれば良かったのですが、追加で質問させていただきます。
12枚のシートにて、「商品名」、「数量」の他に、「単価」を集計したい場合、どの部分の関数を変更すれば良いでしょうか？
また、実はこちらの都合上、「商品名」が同じでも「単価」が違う商品があるのですが、同じ名前の商品でも「単価」の違いで判別させて分けて集計するのは難しいでしょうか？
何から何まで申し訳ありません。
お手間でしたら無視していただいて構いません。

回答No.2

tom04

こんばんは！
すべてのSheetのレイアウトが同じであれば「串刺し計算」が利用できます。

↓の画像のように12Sheet（Sheet1～Sheet12）がすべて同じ配置で、数量が「0」の場合でもA列にそのままの順序で品名があるとすると

合計用のSheet（仮にSheet13とします）
Excel2007以降の場合として・・・
Sheet13のB2セルを選択　→　画面左上のオートサムのアイコン（Σ）をクリック　
→　Sheet1のB2セルを選択　→　Shiftキーを押しながらSheet12のSheet見出しをクリック　→　Enter

これで数式バー内には
=SUM(Sheet1:Sheet12!B2)
という数式が入りますので、これをオートフィルで下へコピー！
これで完了です。

※　各Sheetのレイアウトがバラバラ（品名順が違う）等の場合は
マクロ等別の方法を考える必要があります。m(_ _)m

回答No.1

kazukichi_0914

違うシートのセルを参照するコマンドがあるので、それをぐぐってください。
できますよ。