エクセル2003で、入力した数値のまとめを自動で

質問

エクセル2003で、仕入金額をとにかく打ち込んだシートがあります。別シートに打ち込んだデータを元に仕入先別に合計金額を出す方法はありますでしょうか

図の様に”仕入記入台帳”に記入していくのですが仕入先の件数はどんどん増えます(新規で買う場合もあるので)
そして別シートの”仕入先別集計”に合計を出したいのです

全て入力完了後に≪ツール≫→≪集計≫で集計すればいいのはわかっていますが、
とにかく仕入記入台帳にどんどん入力していき、その集計がどんどん仕入先別集計表に反映させるようにしたいのです

仕入記入台帳は1月(つき)1シートで管理していますが、記入する行数はだいたい500行くらいです
仕入先の件数はだいたい50社くらいです

何か良い方法はございませんでしょうか
お分かりの方いらっしゃいましたらご教授宜しくお願い致します

回答No.5

MackyNo1 · ベストアンサー

>可能であれば合計するシートに集計表示させる仕入先名の順番を
A-Z、0-9順で上から順に表記できるものでしょうか

関数で表示することも可能ですが、エラー処理などを含めてきわめて複雑な配列数式になるため、あまりお勧めできません。
このような項目名の表示順を変えたいような場合は、すでに回答したピボットテーブルを利用すれば項目の並べ替えを一発で行うことができます。

ちなみに関数で表示するなら以下のような数式で昇順に並べ替えて表示できます。

1つ目にデータは以下の式で表示できます（例えばH2セルに入力する場合）。

=INDEX(A:A,MAX(INDEX((LARGE(COUNTIF($A$2:$A$100,">"&$A$2:$A$100),ROW(A1))=COUNTIF($A$2:$A$100,">"&$A$2:$A$100))*ROW($A$2:$A$100),)))

2つ目以下のデータは以下の式で表示できます。

=IF(OR(H2="",COUNTA($A$2:$A$100)<COUNTIF($A$2:$A$100,"<="&H7)+1),"",INDEX(A:A,MAX((LARGE(COUNTIF($A$2:$A$100,">"&$A$2:$A$100),COUNTIF($A$2:$A$100,"<="&H2)+1)=COUNTIF($A$2:$A$100,">"&$A$2:$A$100))*($A$2:$A$100<>"")*ROW($A$2:$A$100))))

入力後Ctrl+Shift+Enterで確定してください。

回答No.6

KURUMITO

遅くなってごめんなさい。
会社名にはコード番号もあるとのことですね。
例えばシート3のＡ1セルから下方にコード番号が、それに対応する社名がＢ1セルから下方にあるとすれば、そのＢ列をコピーしてシート2の社名の列に貼り付ければよいように思いますね。

回答No.4

tom04

こんばんは！
横からお邪魔します。

No.3さんの補足に
＞可能であれば合計するシートに集計表示させる仕入先名の順番を・・・
とありますが、

数式で表示されているデータで並び替えを行っても何も変化しないはずです。
元データそのものを並び替える必要があります。
ただ元データには手を付けないという前提で！

VBAになってしまいますが、一例です。
Sheetの配置は↓の画像のようになっているとします。

画面左下のSheet2（仕入先集計Sheet）の
Sheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）
この際ですのでB列の数式も一緒に入れてしまいました。

Sub test() 'この行から
Dim i, k As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1") '←「Sheet1」の部分は実際のSheet名に！
k = Cells(Rows.Count, 1).End(xlUp).Row
If k > 1 Then
Range(Cells(2, 1), Cells(k, 2)).ClearContents
End If
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Columns(1), ws.Cells(i, 1)) = 0 Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws.Cells(i, 1)
End If
Next i
k = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(k, 1)).Sort key1:=Cells(1, 1), order1:=xlAscending
Range(Cells(2, 2), Cells(k, 2)).Formula = "=SUMIF(Sheet1!A:A,A2,Sheet1!C:C)"
End Sub 'この行まで

※　関数でないのでSheet1のデータ変更があってもすぐに反映されません。
Sheet1のデータ変更があるたびにマクロを実行する必要があります。

ご希望の方法でなかったらごめんなさいね。m(_ _)m

回答No.3

MackyNo1

関数で対応するならA2セルに以下の式を入力して、下方向に50個程度オートフィルコピーして仕入先の一覧を取得します(元データがSheet1にある場合）。

=INDEX(Sheet1!A:A,SMALL(INDEX((MATCH(Sheet1!$A$2:$A$500&"",Sheet1!$A$2:$A$30&"",0)<>ROW($A$2:$A$500)-1)*1000+ROW($A$2:$A$500),),ROW(A1)))&""

B2セルには以下の式を入力して下方向にオートフィルします。

=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!B:B)）

しかし、表示データ数が増えると再計算に時間がかかるので、このようなケースでは関数ではなくピボットテーブルの機能を利用することをお勧めします。

「データ」「ピボットテーブルと・・・」でウィザードを起動し、フィールドリストから行フィールドに「仕入先」、データフィールドに「金額」をドラッグしてください。

今後データが増加するならウィザードの最初で元データの範囲を大きめに設定しておき、作成されたピボットテーブルで「(空白）」の集計項目の上で右クリックし「表示しない」にしてください。

データを追加したときは、ピボットテーブルの上で右クリックして「更新」してください。

補足

ありがとうございます！すばらしいです！

全く違う関数になってしまうかもしれませんが・・・
可能であれば合計するシートに集計表示させる仕入先名の順番を
A-Z、0-9順で上から順に表記できるものでしょうか

回答No.2

KURUMITO

回答Ｎｏ１です。シート２のＡ列にも自動で社名を表示させるのでしたら次のようにします。
シート１のＤ列を作業列としてＤ２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,""))

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

=IF(ROW(A1)>MAX(Sheet1!D:D),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!D:D,0)))

B2セルへの入力の式は回答１のとおりです。

補足

ありがとうございます！！試しに作成した表に入れてみたらうまくできました！ありがとうございます

しかし実際は社名を一件一件手打ちしていては手間なので、
A列の横に”取引先コード”列を入れていて、各仕入先にコード番号を振り当て、
名前の定義をし、例えば取引先コード列に”001”と入力するとが仕入先列に”A社”と表示されるようにしてあるのです

その中にお教え頂いた数式を入れたのですが、合計を表示するシートに社名も金額も#N/Aと表示されてしまいうまくできません。。。

再度お教えいただけると幸いです
何卒宜しくお願い申し上げますｍ(_ _)ｍ

回答No.1

KURUMITO

シート１に元のデータが有るとしてシート２のＢ２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!C:C))