- 締切済み
エクセルで困ってます。
母が仕事上、エクセルで表を作ってるのですが、最後のまとめが出来なくて困ってます。 (母も私もあんまりエクセルは詳しくないです。) 1、材料屋さんから仕入れたものを打ち込んでます。内容は A列 B列 C列 D列 E列 F列 G列 日付 業者名 材料名 品名 数量 単価 金額 6/1 aaaa 野菜 人参 5 10 50 6/3 bbbb 野菜 大根 3 15 45 6/3 cccc 肉 牛 6 8 48 など 1か月分打ち込んでます。ホントは肉とか野菜じゃなくって もっと専門的な用語が書いてあるのですが、よくわかんない ので、仮に人参とか大根にしました。 2、一ヶ月ごとに集計したいのですが、 C列の材料ごとに合計をまとめたい(別のシートでもいい) 例えば、野菜 一式 12300 肉 一式 8000 魚 一式 3600 という風に、したいと言ってます。 3、D列の品名ごとにも集計したいのです。これは数量と 金額両方です。(別のシートでもいい) 例えば 人参 130 80000 大根 500 35000 などです。 ホントは平均単価も一緒に知りたいって言ってたのですが なんせ初級者なので結構いっぱい いっぱいです。 私も母も足し算と掛け算しか、使ったことがありません。 3月まで、手書きで電卓で計算して集計していたのを見て、 私がまとめ表をつくってあげました。 なんとか最後の集計までパソコンでやってあげたいので、 力を貸してください。 宜しくお願いします!
- みんなの回答 (4)
- 専門家の回答
みんなの回答
- mshr1962
- ベストアンサー率39% (7417/18945)
あくまで数式にこだわるならですが 2の場合で例えば2004年6月なら AA1=2004 AB1=6 AC1="野菜"として =SUMPRODUCT(($A$2:$A$65535>=DATE(AA1,AB1 ,1))*(($A$2:$A$65535<DATE(AA1,AB1+1,1))*($C$2:$C$65535=AC1)*($G$2:$G$65535)) で6月の野菜の金額合計が出ます。 3で品名ごとなら ($C$2:$C$65535=AC1)の部分をC列からD列参照に 数量は ($G$2:$G$65535)の部分をG列からE列参照にすれば代用できます。 単価の平均も応用できますよ。 単価の合計は上の応用だというのは分かりますよね。 カウントの場合は最後の*($G$2:$G$65535)の部分を消してやればできます。 後は単純な割り算です。 計算する行はEXCEL2000での最大(65535)にしてあります。 旧バージョンの場合は少なくなるので注意してください。 別シートで数式を作る場合は、各列の範囲の前にシート名を入れてください。 Sheet1!$A$2:$A$65535のようになります。 「挿入」「名前」「定義」で"日付"や"品名"のように名前付けしてやれば楽になると思います。その場合は =SUMPRODUCT((日付>=DATE(AA1,AB1 ,1))*((日付<DATE(AA1,AB1+1,1))*(材料名=AC1)*(金額)) のようにできます。
- imogasi
- ベストアンサー率27% (4737/17069)
ピボットテーブルの利用が最適でしょう。 一度やったら他のやり方は、馬鹿らしくなる?? (テスト例データ)A1:G8 日付 業者名 材料名 品名 数量 単価 金額 6月1日 築地青果 野菜 人参 5 10 50 6月3日 グリーン館 野菜 大根 3 15 45 6月3日 大田ミート 肉 牛 6 8 48 6月4日 大田ミート 肉 豚 3 5 15 6月5日 明石物産 野菜 なす 1 7 7 6月6日 築地青果 野菜 大根 4 15 60 6月7日 今井精肉 肉 牛 5 10 50 (操作) A1:G8を範囲指定 メニューの「データ」をクリック 「ピボットテーブルと・・・」をクリック (1/3の画面で)「次へ」をクリック (2/3の画面で)「次へ」をクリック (3/3の画面で)(左下の)「レイアウト」ボタンをクリック (レイアウトの画面で) 真中の表の、「行」の位置へ、右の「材料名」ボタンを ドラッグアンドドロップ 真中の表の、「データ」の位置へ、右の「金額」ボタンを ドラッグアンドドロップ OK 完了 (結果) 標準では別シートに出来あがります。 合計 : 金額 材料名 計 肉 113 野菜 162 総計 275 (注)普通は行・列にそれぞれ項目名を配置し、2次元の表にするのですが、今回は単に「材料」の合計にしてます。 ------ (品名の表) 上記操作の途中の「レイアウト」の前まで同じ繰り返し (レイアウトの画面で) 真中の表の、「行」の位置へ、右の「品名」ボタンを ドラッグアンドドロップ 真中の表の、「データ」の位置へ、右の「数量」ボタンを ドラッグアンドドロップ OK 完了 (結果) 合計 : 数量 品名 計 なす 1 牛 11 人参 5 大根 7 豚 3 総計 27 ------ (表の構成のバラエティ) 行・列・データにドラッグするものをいろいろ変えて やって見てください。 ------ (平均などについて) 出来た表の一番上行の「合計」部分で右クリックすると リストが垂れ下がり、「フィールドの設定」があるから クリックすると、、「フィールドの設定」画面になり、そこに個数・平均・最大値などがあります。 それをクリックし、OKをクリックで瞬時に平均等の内容の 表に変ります。
- SAKURAMYLOVE
- ベストアンサー率30% (162/533)
分類ごとの集計は、SUMIF関数を使います。 1.C列に20行までデータが入っているとして、分類の合計を出すのであれば、C21に(C1は、フィールド名とする) C21=SUMIF($C$2:$C$20,"野菜",$G$2:$G$20) C22=SUMIF($C$2:$C$20,"肉",$G$2:$G$20) とします。 2.月単位の項目が許せるのであれば、仮にH列に6月、7月、・・・と入れ、6月のC列の集計であれば、 H21=SUM(IF(($H4$2:$H$20="6月")*($C$1:$C$20="野菜"),$G$2:$G$20,"")) ここまで入力(もしくはコピー) したら、Ctrl+Shftを押しながらエンタキーをおします。 すると、数式バーには、中括弧でかこまれた数式が表示されます。 あとは、月と分類を変えてください。 これで、月ごとの分類の集計ができます。 3.D列の分類で数量計(E列)は、 D21=SUMIF($D$2:$D$20,"人参",$E$2:$E$20) D22=SUMIF($D$2:$D$20,"大根",$E$2:$E$20) 金額計は、 G21=SUMIF($D$2:$D$20,"人参",$G$2:$G$20) G22=SUMIF($D$2:$D$20,"大根",$G$2:$G$20)
- goomika
- ベストアンサー率26% (66/251)
データベース機能を使われたら楽ですよ。 まずは、材料のフィールドを昇順または降順に 並べ換えておきます。 データ→集計。 グループの基準は材料、集計の方法を選択、 集計するフィールドを、金額。 という具合に・・。 更に色んな角度で分析ができます、 ピボットテーブルなどがお薦めではあるのですが・・。
お礼
ありがとうございます。 データ→集計をやってみました。 こんなに便利な機能があるんですね。 ピボットテーブルは参考URLを見てみましたけど、 今回の母の集計にはちょっと使いづらいみたいです。 牛の時は絶対左が肉ですし、大根の時は絶対野菜だからです。 でも、覚えておくと とっても役に立ちそうなので 是非覚えてみたいと思います!!