エクセルにて、各部署の物品の集計を行っております。

質問

エクセルにて、各部署の物品の集計を行っております。

全部の部署の物品を集計後、全体の数を出したいのですが、簡単に出来るアイディア等はありませんでしょうか？

Ａセルに部署名、Ｂセルに物品名、Ｃセルには物品の規格、Ｄセルに商品コード、Ｅセルは固定で１の数字、Ｆセルに単位（個、枚etc）、Ｇセルに単価、Ｈセルに集計した数が書いてあります。

このうち、Ａの部署名は無視して、他の部分が共通してる項目を併せて、合計を出したいのです。

たとえば・・・
２のセルにはＡセル「営業部」Ｂセル「鉛筆」Ｃセル「２０ｃｍ」Ｄセル「１９００」Ｅセル「１」Ｆセル「本」Ｇセル「３０」Ｈセル「２５０」

３のセルにはＡセル「総務部」Ｂセル「鉛筆」Ｃセル「２０ｃｍ」Ｄセル「１９００」Ｅセル「１」Ｆセル「本」Ｇセル「３０」Ｈセル「１００」

４のセルにはＡセル「受付」Ｂセル「鉛筆」Ｃセル「２０ｃｍ」Ｄセル「１９００」Ｅセル「１」Ｆセル「本」Ｇセル「３０」Ｈセル「５０」

５のセルにはＡセル「営業部」Ｂセル「消しゴム」Ｃセル「５ｃｍ」Ｄセル「１９１１」Ｅセル「１」Ｆセル「個」Ｇセル「１００」Ｈセル「２０」

６のセルにはＡセル「総務部」Ｂセル「消しゴム」Ｃセル「５ｃｍ」Ｄセル「１９１１」Ｅセル「１」Ｆセル「個」Ｇセル「１００」Ｈセル「３０」

・
・
・

とこういった形で並んでいるのを、別のシートにして、

２のＡセルは空白、Ｂセル「鉛筆」Ｃセル「２０ｃｍ」Ｄセル「１９００」Ｅセル「１」Ｆセル「本」Ｇセル「３０」Ｈセル「４００（２５０＋１００＋５０＝４００）」

３のＡセルは空白、Ｂセル「消しゴム」Ｃセル「５ｃｍ」Ｄセル「１９１１」Ｅセル「１」Ｆセル「個」Ｇセル「１００」Ｈセル「５０（２０＋３０）」
・
・
・

としたいのです。
簡単にいうとＨの合計を出したい、という形になるのですが、セルの形式などはそのまま残して行いたいです。

わかりにくい説明で申し訳ありませんが、宜しくお願いいたします。

回答No.4

tom04 · ベストアンサー

No.2です！
昨日回答しながら思っていたことなのですが・・・
やはり規格は一種類だけではないのですね！

とりあえず、物品と規格の種類別集計の方法です。
↓の画像のようにSheet1に2列作業用の列を使わせてもらいます。

作業列H2セルは
=B2&C2
I2セルは
=IF(OR(H2="",COUNTIF($H$2:H2,H2)<>1),"",ROW(A1))
という数式を入れ、H2・I2セルを範囲指定しI2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

そして、Sheet2のB2セルに
=IF(COUNT(Sheet1!$I$2:$I$1000)<ROW(A1),"",INDEX(Sheet1!B$2:B$1000,SMALL(Sheet1!$I$2:$I$1000,ROW(A1))))
とし、列方向にF2セルまでコピーします。

G2セルの数式は
=IF(B2="","",SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$C$2:$C$1000=C2),Sheet1!$G$2:$G$1000))
とします。

最後にB2～G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
これで同じ物品でも規格が異なればそれぞれの集計が出来るはずです。

尚、余計なお世話かもしれませんが、
今回はコードは無視していますが、もし同一物品でもコードが違う場合は
Sheet1の作業列H2セルを
=B2&C2&D2　としてI2セルの式はそのままで下へコピーします。

そしてSheet2のB2セルに入れる数式は変わりありません。
集計結果のG2セルの数式を
=IF(B2="","",SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$C$2:$C$1000=C2)*(Sheet1!$D$2:$D$1000=D2),Sheet1!$G$2:$G$1000))
とすれば物品・規格・コードが違ってもそれぞれの集計が出来るはずです。

今回はSheet1の1000行目まで対応できる数式にしてみました。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

ありがとうございます！無事にできました（＾＾）
ご丁寧に説明していただき感謝感激です。

数式の意味も調べてなんとな・・くわかったので、これからに活かしたいと思います。
ありがとうございました。

回答No.3

keithin

＞出来ればエクセルのセルごと、といった感じで分けたい感じです

もしも，あなたが今Excelの2007を使っているというお話で困っていた場合は。
作成したピボットテーブルレポートの中で右クリックしてオプションを出し，表示タブで従来のレイアウトを使用するチェックを入れて使います。

＞数式

実際にあなたがセルに入れて，間違った答えが出たその「間違っている式」を，あなたのエクセルの数式バーからコピーして「この式を入れました」「計算ネタにしたのはこのようなデータです」「こういう結果がが欲しいのに，こんな間違った結果が出て困りました」，と書いて追加ご質問を投稿してください。
お詳しい方から，即座に「それはここが違うのでこう直します」と，具体的なアドバイスが寄せられてあっという間に解決です。

お礼

従来のレイアウトという表示もできたのですね・・・気づきませんでした。

確かに自分の数式を乗せるべきでした、申し訳ありません；
また質問することがありましたらそうします＾＾

回答No.2

tom04

こんばんは！
一例です。
元データの物品ごとの、規格・商品コード等は全て同じだとしての
方法です。

↓の画像でSheet1のデータをSheet2にまとめるようにしています。
Sheet1に作業用の列を設けています。
作業列I2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式を入れオートフィルで下へずぃ～～～！っとコピーします。

Sheet2のB2セルに
=IF(COUNT(Sheet1!$I$2:$I$100)<ROW(A1),"",INDEX(Sheet1!B$2:B$100,SMALL(Sheet1!$I$2:$I$100,ROW(A1))))
という数式を入れ、列（右）方向に「単価」のG2セルまでコピーします。
H2セルは数式が変わってきます。
H2セルは
=IF(B2="","",SUMIF(Sheet1!$B$2:$B$100,B2,Sheet1!$H$2:$H$100))
という数式を入れます。
最後にB2～H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、数式はSheet1の100行目まで対応できる数式ですが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

お礼

すみません、自分の説明が足りなかったかも・・・
うまく出来たように見えたのですが、鉛筆の規格も「２０ｃｍ」「１５ｃｍ」「１０ｃｍ」と色々あって、tom04さんの方法だと鉛筆の規格も無視してすべて鉛筆２０ｃｍというふうに集計されてしまいました＞＜
説明不足で申し訳ないです。

補足

自分で調べてみて、教えていただいた数式に加えてみたりしましたが、なかなかうまくいきませんでした・・・。
どういった数式を加えれば上記のように規格別でも出せるのでしょうか？

回答No.1

keithin

方法１（推奨）
元の一覧でデータメニューのピボットテーブルレポートを開始します。
最初に見るとびっくりして止めてしまう方も多いようですが，そこをこらえてちょっと使ってみると，５分以内にすべての集計結果がゼロから作成できます。

行の部分に物品，規格，コードなど必要な項目を投入
データの部分に集計数の項目を投入
手順は以上です。
この手順が最も優れているのは，関数などでは計算するのにあなたが用意しなきゃならない「縦項目の一覧」を，エクセルが自動で全部集めて一覧にしてくれるところです。
また面倒くさい難しい数式をひとつも考えなくても，マウスのドラッグとクリックだけで結果が得られるのもポイントです。

方法２（あまり良くない方法）
ご自分で，集計したい項目が並んだ一覧を漏れなく作成できるなら（たとえばデータメニューのフィルタから「フィルタオプションの設定」などを使って），数式で
H2
=SUMPRODUCT((Sheet1!A$1:A$100=A2)*(Sheet1!B$1:B$100=B2)*(Sheet1!C$1:C$100=C2)*(Sheet1!D$1:D$100=D2)*(Sheet1!E$1:E$100=E2)*(Sheet1!F$1:F$100=F2)*(Sheet1!G$1:G$100=G2),Sheet1!H$1:H$100)
のような計算も出来ます。

やれば出来ますが，なんでもかんでも詰め込むと加速度的にシートの計算が重くなって実用的では無くなります。漠然と「全部一致したら」とかではなく，どの範囲を調べて合計の集計をしたらいいのかよく考えて数式を作成してください。

Excel2007を使っているなら，SUMIFS関数を使うことも出来ます。

お礼

ピポットテーブルというものを初めて使ったのですが、すごい機能ですね！
しかし出来ればエクセルのセルごと、といった感じで分けたい感じです。
ピポットテーブルの存在を知っただけでも今回は勉強になりました。ありがとうございます！

方法２のほうはすみません、うまく出来ませんでした。
実力不足で申し訳ないです。