串刺し、統計

質問

複数のシートに複数の伝票があり、
それをまとめのシートに合算したいのですが
どの関数を用いればいいでしょうか。

各シートの伝票については
同じ形式ですが、シートに複数伝票があります。

また、
画像のようなまとめの表ですと、
品番、品名を記入しておき、
個数だけ計算させる方法と、

品目が多数ある場合、
出なかった品目を表示させない、
すなわち出た品番のみピックアップして
それぞれ個数を出力する方法も
可能であればご教示願います。

http://www.evernote.com/shard/s114/sh/fcbc2a50-9b9b-4186-b513-f389e397641c/791a9745320430662e9b5dbbb4daec63

回答No.2

KURUMITO · ベストアンサー

シート1からシート3までで伝票1・２・３の表は10行目から下方にあるとします。
そこでシート4ですが単なる指定されたセルの合計では串刺しなどが容易ですが、例えばCOUNTIF関数などではそれができません。シートが3までですから式が多少長くなりますが次のようにしてはどうでしょう。
シート4ではA1セルに合計の文字がA2セルに品番、B2セルに品名、C2セルに個数の文字をそれぞれ入力します。
A3とB3セルから下方にはシート1で示されている対応表と同じデータを入力します。
C3セルには次の式を入力して下方にドラッグコピーします。

=IF(B3="","",(COUNTIF(Sheet1!B$10:B$100,B3)+COUNTIF(Sheet2!B$10:B$100,B3)+COUNTIF(Sheet3!B$10:B$100,B3)))

なお、伝票に記載のない品名は表に含めない場合には別に作業用の表を設けることが必要でしょう。
例えばF3セルから下方にすべての品の品番を、G3セルから下方にすべての品の品名を入力した表があるとします。そこでH3セルには次の式を入力して下方にドラッグコピーします。

=IF(G3="","",IF((COUNTIF(Sheet1!B$12:B$102,G3)+COUNTIF(Sheet2!B$10:B$100,G3)+COUNTIF(Sheet3!B$10:B$100,G3))>0,MAX(H$2:H2)+1,""))

その上でA3セルには次の式を入力しB3セルまぢょこにドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX($H:$H),"",INDEX($F:$G,MATCH(ROW(A1),$H:$H,0),COLUMN(A1)))

C3セルへの式は最初と同じ式で良いですね。
ところでお求めのデータは個数といっても伝票に載っている品名の数ですね。実際にはその品名で何個売れたかなどの個数が必要でしょう。その場合にはCOUNTIF関数よりもSUMIF関数を使うことになりますね。

補足

ご回答ありがとうございます。
この方法は
品名を全て数えているってことですよね。

品名の隣に個数という列を作ったほうが、
品目が多数ある場合、
出なかった品目を表示させない
という時にやりやすいでしょうか。

回答No.4

bunjii

各シートの伝票1、伝票2、伝票3に数量欄が無いので伝票としては不完全のように思います。
また、対応表にも数量欄が無いので串刺し集計ができません。
考え方としては対応表、および各伝票に数量欄を設けて各シートの集計を対応表にまとめて、対応表について串刺し集計を行えば良いことになります。

あなたのソフトはOpenOfficeと推測します。
各シートの対応表については品番をキーとしてVLOOKUP関数で伝票1、2、3を其々の数量をピックアップして加算すれば良いことになります。
尚、VLOOKUP関数でエラーを防ぐためにはIF関数とISERROR関数を使います。
対応表の数量欄をC列とすれば以下の式をC6に記載して9行目までコピーします。

=IF(ISERROR(VLOOKUP(A6;A$13:C$15;3;0));0;VLOOKUP(A6;A$13:C$15;3;0))+IF(ISERROR(VLOOKUP(A6;A$19:C$21;3;0));0;VLOOKUP(A6;A$19:C$21;3;0))+IF(ISERROR(VLOOKUP(A6;A$25:C$27;3;0));0;VLOOKUP(A6;A$25:C$27;3;0))

取り敢えず、ここまで実行してみて良ければシート2とシート3へC6からC9をコピーします。
串刺し集計はSUM関数または単純に相対セルを加算します。

ExcelとOpenOfficeでは関数の書式が若干異なりますのでExcelを対象とした回答については読み替えが必要です。

回答No.3

KURUMITO

回答No2です。
補足のご質問ですが出なかった品目を表示させないという操作の中で伝票に別の列があればより都合がよいのですか？といった内容に取れますが、そのことは全く関係ありません。
あなたのお示しの伝票では例えばりんごという文字列がB10以下の行に何回出てきたかを数えることで良いことになっていますね。実際には何回出てきたかではなく、りんごならりんごが何個売れたのかが伝票に記載されるべきなのではないかと思ったからです。今回はそれは必要が無いということですね。

回答No.1

yasuto07

アクセスやファイルメーカーが良いでしょう。

お礼

ありがとうございます。