1235607360 【Excel】



1、「2009年1月に通販で売れた紅茶の合計金額」を求めるには、どうしたらいいでしょうか?
2、1月のコーヒーの販売額合計はどのようにしたら求めることができるでしょうか?



添付ファイルをご参照下さい。
実際には過去5年分くらい、5,000行くらいのデータで、上記のようなデータを月毎に出していかなければなりません。


よろしくお願いします。

回答の条件
  • 1人10回まで
  • 登録:2009/02/26 09:16:01
  • 終了:2009/02/26 13:03:07

ベストアンサー

id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692009/02/26 10:16:35

ポイント100pt

まず、そのまま計算できるのかどうか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などで一括で変換するコードを提示します。

id:curry21

wao!

ありがとうございます。

明快なご回答、感謝です。


> もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。


なってはいますが、コードは見てみたいです^^;

2009/02/26 10:23:29

その他の回答(3件)

id:h_i No.1

h_i回答回数2ベストアンサー獲得回数02009/02/26 10:14:52

「ピボット」 機能を使うと30秒ぐらいで作れると思いますよ。

参考サイトはたくさんありますが、たとえばこちら。

Excelのピボットテーブルでデータの集計/分析を自在に行うには?

(月毎の集計例も載ってますよ)

id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692009/02/26 10:16:35ここでベストアンサー

ポイント100pt

まず、そのまま計算できるのかどうか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などで一括で変換するコードを提示します。

id:curry21

wao!

ありがとうございます。

明快なご回答、感謝です。


> もしもそれぞれ日付と数値になっていない場合は、VBAなどで一括で変換するコードを提示します。


なってはいますが、コードは見てみたいです^^;

2009/02/26 10:23:29
id:lide No.3

lide回答回数1ベストアンサー獲得回数02009/02/26 10:16:48

ポイント1pt

これでいいのかな

1、オートフィルタで「1月1日以上1月31日以下」と「紅茶」で抽出してから、集計機能でグループの基準も販売種別を選ぶ

2、オートフィルタで「1月1日以上1月31日以下」と抽出してから、集計機能でグループの基準に「商品分類」を選ぶ

id:curry21

そうですね。

2009/02/26 10:25:06
id:SALINGER No.4

SALINGER回答回数3454ベストアンサー獲得回数9692009/02/26 11:03:45

ポイント100pt

ついでに先ほどの式から、発展させて月ごと、項目ごとの表を作る方法です。

(ピボットテーブルのようなものですが)


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
id:curry21

SUGEEEE!


見ず知らずの私のようなものものにご親切にありがとうございました!!

2009/02/26 13:01:53
  • id:standard_one
    EXCELのソート機能を使って合計額を出す手順の説明でもいいんですか?
  • id:curry21
    あ、すみません。そういうのではなく・・・。


    なんといえばいいのかわからないのですが。
  • id:airplant
    回答者No1さんの方法が一番良いと思います。0ポイントはかわいそうですね。
    このような分析はVBAや式ではなく、ピボットテーブルが一番適しています。
    一度使い始めると止められないですよ。
    簡単にグラフにもできるし、トレンドを見るためのツールとしては一番ですので、一度使ってみることをお勧めします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません