エクセルで利益計算などがしたい

質問

輸入スケジュール＆売掛金、買掛金、利益をエクセルを使ってしたいと思っています。

これらを1個のエクセルでシートは別でかまいません。

1.輸入スケジュール＆利益計算
PURCHASE ORDER No.　
顧客名
部品番号
注文数量
単価（FOB)
ETD/HK（日付）
ETA/JAN（日付）
倉庫納入日（日付）
納入日（日付）※顧客に納入した日
納入数量
売り単価
経費（送料など）

これらをできればオートフィルターで顧客名と納入日を選択したら、
○月の納入数量や利益が出たらいいな～なんて思っているんですが、
複雑なのでフィルターだけでは無理ですよね？？

データ管理などでよく聞く、ピボットテーブルやIFよりも難しい関数を使わないと無理でしょうか？
当方、エクセルの基本、IF関数などしかわからず、困惑しております。

回答No.5

MarcoRossiItaly

＞実際やってみたんですが、なんとなく出来あがった表はしっくりきません。
　でも、懲りずに時間をかけてやってみます。

●ピボットテーブルでは、ドラッグ・アンド・ドロップで項目を移動させるだけで、様々な形状に表を作り変えることができます。例えば、項目の順番を入れ替えることによって、「部品番号」ごとの「納入日」を集計することもできるし、その逆の「納入日」ごとの「部品番号」とすることもできます。

●たくさん出てくる小計の欄は、右クリックなどで表示される「フィールドの設定」ダイアログの中でオン／オフを切り替えることができます。項目が何階層もある場合は、一つ一つの小計に対してこの設定を行うことで、ある種類の小計は表示させ、他のものは表示させないというふうに、選ぶことができます。

●表より上の部分にはフィルタ用のフィールドがありますが、ここにも複数の項目を置くことができます。

●データの集計の方法としては、合計などもありますが、個数を求めることも多いです。また、数値の集計はせずに、表の左側（行ラベル）に項目を表示させることによってリストアップするというだけの目的で使うこともできます。

●抽出された表は、コピーして、別のシートなどで利用できます。書式を設定して体裁を整えるなどしたい場合は、このコピーした表に対して行うとよいでしょう。

回答No.4

KURUMITO

回答2です。ある行を入力した時点で該当月の納入総量や利益が先の回答ではシート2に表示されるようにしましたが、シート１のもとの表で表示させるようにするのでしたら利益の列は回答2と同じＭ列に式を入力して表示させます。
作業列はＰ列にしてＰ2セルには次の式を入力して下方にドラッグコピーします。

=IF(I2="","",B2&DATE(YEAR(I2),MONTH(I2),1))

Ｎ1セルには当月納入総量、Ｏ1セルには当月利益とそれぞれ文字を入力します。
Ｎ2セルには次の式を入力してＯ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTA($I2:$M2)<4,"",SUMIF($P:$P,$B2&DATE(YEAR($I2),MONTH($I2),1),IF(COLUMN(A1)=1,$J:$J,$M:$M)))

これで該当者に納入日や納入量、売上単価などが入力された時点でその月の納入総量やその月の利益がＮ列やＯ列に表示されます。

回答No.3

MarcoRossiItaly

ピボットテーブル（Excelにおけるクロス集計の機能）は、初めてだととっつきにくく感じる人も多いようですが、慣れれば全くどうということはないですし、超便利ですよ。関数などの数式と違って何か覚えないと使えないということもないし、たいへん楽チンです。しかも強力で、いろんな表に対応できる、表計算ソフトの中心的な機能です。もしかすると盲点かもしれませんが、数値を含まない表に対して使うこともできます。ピボットテーブルでできる作業は、なるべく関数ではなく、ピボットテーブルを使われるよう皆さんにお勧めしています。難しく考える必要はありません。どんな動きをするのか、ちょっと実験して、いろいろいじり回してみればいいのです。言葉では説明しにくいですが、自在に表の作りを組み替えることができます。

お礼

回答ありがとうございます。
実際やってみたんですが、なんとなく出来あがった表はしっくりきません。
でも、懲りずに時間をかけてやってみます。

回答No.2

KURUMITO

お示しの項目名がシート1のＡ1セルからＬ1セルまでに並んでいるとします。
Ｂ1セルには顧客名、Ｅ1セルには単価、Ｉ1セルには納入日、Ｊ1セルには納入数量、Ｋ1セルには売上単価、Ｌ1セルには経費
Ｍ1セルには追加で利益の項を設けます。
Ｍ2セルには次の式を入力して下方にドラッグコピーします。

=IF(I2="","",J2*(K2-E2)-L2)

利益がこの式で良いのかどうかはわかりません。
Ｎ列は作業列としてＮ2セルには次の式を入力して下方にドラッグコピーします。

=IF(I2="","",B2&DATE(YEAR(I2),MONTH(I2),1))

そこでお求めの表をシート2に表示させるとしてＡ1セルには顧客名、Ｂ1セルには選択年月、Ｃ1セルには月の納入数量、Ｄ1セルには月の利益とそれぞれ項目名を入力します。
Ａ列に顧客名を入力し、Ｂ列には今年の3月でしたら2012/3と入力します。これでパソコンでは2012/3/1の入力と解釈しますのでセルの表示形式で日付から2001年3月を選択すればよいでしょう。2012年3月と表示されますね。
そこでＣ2セルには次の式を入力してＤ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTA($A2:$B2)<>2,"",SUMIF(Sheet1!$N:$N,$A2&$B2,IF(COLUMN(A1)=1,Sheet1!$J:$J,Sheet1!$M:$M)))

これで顧客名やお望みの年月を入力することでその月の納入数量や利益が表示されます。

お礼

ご丁寧にありがとうございます。
やってみました。
シート2の部分の2012年3月までは表示されたのですが、
=IF...と入力、ドラッグコピーからうまくいきません。
シート1に2012年3月の取引があるにもかかわらず、
０と表示されてしまいます・・。

回答No.1

keithin

確かにピボットテーブルレポートを使えば，関数とか一切使わずにほぼ一発で欲しいまとめが完成します。
やったことないよーと泣いている間にダマされたと思って手を動かしてみると，思っていたよりも簡単に結果が現れてビックリすると思います。

準備：
今の表にもう一列追加して，各行ごとの「利益」を計算する

手順：
ピボットテーブルレポートの作成を開始する
行に顧客をドラッグして放り込む
行の続きに納入日を放り込む
データ（Σ）に利益を放り込む

自動作成された表に付いている▼をプルダウンして，顧客とか納入日とかを絞り込む。
必要に応じて他にやりたいことを（このご質問で説明できていない，実はこうしたいことを）追加する。

以上です。

＃補足
＞IFよりも難しい関数を使わないと無理でしょうか？

丁寧に丁寧に手間を惜しまず作成していけば，IF関数とSUM関数だけ使っても出来ます。

たとえばシート１の表の右に
=IF(AND(顧客が=指定の顧客なら,納入日が=指定の納入日なら),利益を再掲,"")

といったIFの式をずらり並べておいて，その列をSUMすればコタエは計算できます。

他にも，アナタが手を動かして最低限「顧客の一覧表」をエクセルの機能を使うなどして用意すれば，SUMIF関数とかSUMPRODUCT関数，ご利用のエクセルのバージョンが不明ですがSUMIFS関数とかCOUNTIFS関数などを駆使して集計します。