1、「2009年1月に通販で売れた紅茶の合計金額」を求めるには、どうしたらいいでしょうか?
2、1月のコーヒーの販売額合計はどのようにしたら求めることができるでしょうか?
添付ファイルをご参照下さい。
実際には過去5年分くらい、5,000行くらいのデータで、上記のようなデータを月毎に出していかなければなりません。
よろしくお願いします。
まず、そのまま計算できるのかどうか2つほど確認が必要です。
① 販売日(A列)の書式設定の表示形式は日付でシリアル値になっているか?
シリアル値ならば年月の比較がやりやすくなります。
② 金額(E列)の書式設定の表示形式はそのまま合計できる数値か?
例えば表示形式がユーザー定義で
#,###"円"
とかになっていれば、そのまま合計できます。文字列になっている場合は合計できないので変換が必要。
上記を満たしていれば、仮に5000行ならば
1、「2009年1月に通販で売れた紅茶の合計金額」
=SUM(IF((YEAR(A2:A5000)=2009)*(MONTH(A2:A5000)=1)*(C2:C5000="紅茶"),E2:E5000,0)) を入力して、Shift+Ctrl+Enter({}で囲まれる)
2、1月のコーヒーの販売額合計
=SUM(IF((MONTH(A2:A5000)=1)*(C2:C5000="コーヒー"),E2:E5000,0)) を入力して、Shift+Ctrl+Enter({}で囲まれる)
①と②は合計を出すためには必要なことであるのですが、
もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。
「ピボット」 機能を使うと30秒ぐらいで作れると思いますよ。
参考サイトはたくさんありますが、たとえばこちら。
Excelのピボットテーブルでデータの集計/分析を自在に行うには?
(月毎の集計例も載ってますよ)
まず、そのまま計算できるのかどうか2つほど確認が必要です。
① 販売日(A列)の書式設定の表示形式は日付でシリアル値になっているか?
シリアル値ならば年月の比較がやりやすくなります。
② 金額(E列)の書式設定の表示形式はそのまま合計できる数値か?
例えば表示形式がユーザー定義で
#,###"円"
とかになっていれば、そのまま合計できます。文字列になっている場合は合計できないので変換が必要。
上記を満たしていれば、仮に5000行ならば
1、「2009年1月に通販で売れた紅茶の合計金額」
=SUM(IF((YEAR(A2:A5000)=2009)*(MONTH(A2:A5000)=1)*(C2:C5000="紅茶"),E2:E5000,0)) を入力して、Shift+Ctrl+Enter({}で囲まれる)
2、1月のコーヒーの販売額合計
=SUM(IF((MONTH(A2:A5000)=1)*(C2:C5000="コーヒー"),E2:E5000,0)) を入力して、Shift+Ctrl+Enter({}で囲まれる)
①と②は合計を出すためには必要なことであるのですが、
もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。
wao!
ありがとうございます。
明快なご回答、感謝です。
> もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。
なってはいますが、コードは見てみたいです^^;
これでいいのかな
1、オートフィルタで「1月1日以上1月31日以下」と「紅茶」で抽出してから、集計機能でグループの基準も販売種別を選ぶ
2、オートフィルタで「1月1日以上1月31日以下」と抽出してから、集計機能でグループの基準に「商品分類」を選ぶ
そうですね。
ついでに先ほどの式から、発展させて月ごと、項目ごとの表を作る方法です。
(ピボットテーブルのようなものですが)
F | G | H | I |
---|---|---|---|
_ | コーヒー | 紅茶 | ジュース |
2009年1月1日 | 数式 | ||
2009年2月1日 | |||
2009年3月1日 |
のような表を作り、数式と書いてるところの数式を
=SUM(IF((YEAR($A$2:$A$5000)=YEAR($F2))*(MONTH($A$2:$A$5000)=MONTH($F2))*($C$2:$C$5000=G$1),$E$2:$E$5000,0)) を入力して、Shift+Ctrl+Enter({}で囲まれる)
後は、表の大きさに合わせて縦横にコピー。
因みに①の表示形式を文字列→日付にするのは、そのまま表示形式を日付にすればいいだけで、
②の文字列→数値はこんな感じ。
(注意。上の表とかを設定して下のコードを実行するとかなりの重い処理になると思われるので、
実行する場合は計算方法を一時的に手動にしたほうがいいかもしれません)
Sub Macro1() Dim str As String Dim lastRow As Long Dim i As Long Application.ScreenUpdating = False lastRow = Cells(Rows.Count, 5).End(xlUp).Row For i = 2 To lastRow str = Cells(i, 5).Value If Right(str, 1) = "円" Then str = Left(str, Len(str) - 1) End If Cells(i, 5).NumberFormatLocal = "#,###""円"" " Cells(i, 5).Value = CLng(str) Next i Application.ScreenUpdating = True End Sub
SUGEEEE!
見ず知らずの私のようなものものにご親切にありがとうございました!!
wao!
ありがとうございます。
明快なご回答、感謝です。
> もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。
なってはいますが、コードは見てみたいです^^;