再びお世話になります。
シート1の対象年月と商品が一致した売上をシート2で集計したいのですが、上手く集計が取れなかったのでご教示ください。

【表】シート1のA1からC5にデータが入力されているとして。
   (CSVで出力の為、すべて文字列状態)
  A列      B列      C列
1 日付      商品     売上金額
2 20150401   卵      100
3 20150409   卵      110
4 20150502   パン     200
5 20160102   野菜     50
6 20160110   野菜     80


シート2で集計します。
【作業日が2017/04/16として、前年度の前月と同月の売上を集計します】
・A1=作業日当日日付、B1=前年前月とC1=前年同月はDATE関数でA1を元に年月を出しています。
・D列はシート1のA列を、E列でTEXT関数で日付表示にしてからD列でDATE関数でこの表記に変えていますので、5000行程続いています。(TEXT関数だと形式が一致しないようだったので…)

  A列      B列      C列     D列(日付作業列)
1 (作業日日付)  2015/03/16 2015/04/16 2015/04/01  
2 卵.............................................................2015/04/09
3 パン..........................................................2015/05/02
4 野菜

このような状態で、
B2には、B1とD列の前7文字が一致(年月が一致) かつ A2とシート1のB列の表示が一致(商品が一致)したものの売上合計を表示。
いきなり全体の式を組むのは私には無理なので、少しずつ計算していこうとしたのですが、
B2に「=SUMIF(D:D,LEFT($B$1,7)&"*",シート1!C:C)」と入れてみたものの計算が反映せず行き詰ってしまいまいた…。
皆さまのお知恵をお借りしたく、宜しくお願いいたします。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

まず、やりたい事を順番にまとめて、それに応じた適切な作業列を設けましょう。



やりたい事について
①作業日の日付(シート2!A1)から前年前月(シート2!B1)と前年同月(シート2!C1)を表示させる。
②シート2のB列・C列に「シート1のA列の月が、シート2の1行目に表示した月と一致するシート1のデータから、シート1のB列がシート2のA列と一致するデータの、シート1のC列に表示された金額を合計したもの」を表示させる。

作業列例について
①については特に作業列は必要ありませんが、検索したいのは年月の分かる6桁の数字なので、
シート2!B2=TEXT(DATE(YEAR(A1)-1,MONTH(A1)-1,1),"yyyymm")
シート2!C2=TEXT(DATE(YEAR(A1)-1,MONTH(A1),1),"yyyymm")
としましょう。
日にちは関係ないので、年月をA1とから算出し、日は1日にしています。
質問者の例の状態では、B2=201503、C2=201504、とそれぞれ表示されるはずです。

②についてですが、
合計を計算するためには、シート1のC列を文字列ではなく数値で表示させる必要があります。
よってシート1のD列を作業列とし、
シート1!D2=C2*1
これをデータの数だけ↓にコピーしましょう。
*1とすることで、C2が数値として利用できるデータが文字列として入っている場合、数値として扱って計算結果を表示してくれます。
*1なので、値としては同じですね。+0としてもかまいません。

これで準備は整いました。
シート2のD列で表示させたデータは特に必要ないというわけですね(汗
あとは結果を表示させるだけです。
シート2!B2=SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,B$1&"*",Sheet1!$B:$B,$A2)
これを表の分だけ(例の場合C4まで)コピーしましょう。

あくまでシート1のデータが全て文字列として入力されていた場合です。
結果が合わない場合は、おそらく文字列として入力されていると思い込んでいるだけ、
といった可能性もあります。(CSVについては良く知りませんので)
    • good
    • 0
この回答へのお礼

引き続きのご回答ありがとうございます。
行き詰っていたせいで分かりにくい表記の質問になってしまっていましたが、分かりやすく整理して頂き、頭の中がすっきりしました。
②はシート1に他にも多くの項目が入っているため、シート2のD列を入れ替えで使ってみました。
詳しく解説頂きありがとうございます!解決しましたm(__)m

お礼日時:2017/04/16 23:17

>(CSVで出力の為、すべて文字列状態)


まず、特に、加工した状態でなければ、文字列状態とはいいませんが……。
また、書式やプログラムで、そんな面倒なことをしてはいないはずです。
Sheet1 のCSVは×インポート◯オープンしたものになるのですが、たぶん、単に開いただけのものだと思います。(インポートは、データ加工してから表に入れること)

私は前の経緯を読んでいないのですが、少し内容的にわかりにくく感じています。

Sheet1 CSVを、
>E列でTEXT関数で日付表示にしてから
>D列でDATE関数でこの表記に変えていますので、5000行程続いています。
Text関数から、日付値(シリアル値)に変更出来ますが、DATE関数は使いません。

その日付値をどう処理するかで決まるはずです。
SUMIF でも出来ますが、文字列にすべて置き換えていくのも問題ですね。
正統派としたら、シリアル値の日付表示にするところだと思います。
-------------------------
そうすると、#2様のおっしゃっているように、
「SUMIF」ではなく、「SUMIFS」関数を選ばなくてはなりません。
ということは、また最初から降り出しに戻らなくてはなりません。

1.
Sheet1 D列は、タイトルは[日付SR(仮)]
=TEXT(A2,"##!/##!/##")*1  これで、シリアル値になります。
  ↓
とします。シリアル番号そのものが出てきてしまいます。表示が気になるなら、書式を設定してください。

2.
Sheet2:B2~(右へ)
=SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,$A2,Sheet1!$D:$D,">=" &DATE(YEAR(B$1),MONTH(B$1),1),Sheet1!$D:$D,"<=" &DATE(YEAR(B$1),MONTH(B$1)+1,0))

なお、表示が遅いようでしたら、C:C や D:D を使わずに、C$2:C$10000 と範囲を限定した方がよいかもしれません。Excel2003までは、そのスタイルは問題なかったようですが、仕様が変わったそうです。

'-----------------
>  A列      B列      C列     D列(日付作業列)
1 (作業日日付)  2015/03/16 2015/04/16 2015/05/16 ←5月になるはず  

1行目は、書式で、YYYY年M月としたほうがよいでしょう。
    • good
    • 0

ちょっと意味不明な点があります。



(CSVで出力の為、すべて文字列状態)とありますが、シート1のC列はCSVファイルを読み込んでも、EXCELシートでは数値に変換されているはずですが。

シート2のA列の品名は手入力したものですよね?

D列はシート1のA列(文字列の日付)を通常の日付に変換したものなので、シート2のD列ではなく、シート1のD列に表示すべきものではありませんか?

E列に日付作業列をもうけなくてもD列に次の式で直接変換できます。

=DATEVALUE(LEFT(Sheet1!A2,4)&"/"&MID(Sheet1!A2,5,2)&"/"&MID(Sheet1!A2,7,2))

なお、該当するセルの書式設定はyyyy/mm/ddとします。

上の式を使って日付をシート1のD列に表示するようにしたとすると画像のようになります。

複数条件での合計はSUMIF関数を使うのですが、今回の場合hSUMIFS関数を使ってみたのですがうまくいきませんでした。

なので、SUMPRODUCTを使っています。

シート2のB2に下の式を入力し、B列とC列にコピーして下さい。

=SUMPRODUCT((Sheet1!$B$2:$B$6=$A2)*(YEAR(Sheet1!$D$2:$D$6)=YEAR(B$1))*(MONTH(Sheet1!$D$2:$D$6)=MONTH(B$1))*Sheet1!$C$2:$C$6)

なお、式中の$6の部分は$5000のように、データーの列数によって適宜書き換えて下さい。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
売上数字については自分の思い込みで、数値になっておりました。
シート1については、実は他にCZ列あたりまで項目があるという事と、毎月データの更新で1000行程が増えるという事情があるので、触らないようにしてシート2で作業列を作りました。
教えて頂いた通りのやり方で、シート1のD列を変えた場合だとうまく集計がとれました。シート2に作業列を持ったままの場合ももう少し頑張ってみます!

お礼日時:2017/04/16 22:19

シート2のB1セルは、日付に見えていますが、シリアル値となっていますのでその式ではダメかと思います。


シリアル値に対して、LEFT対応では意味がありません。

良く分からないのですが、元データ(全売上)はシート2なのですか? 5000行もあるっていうのなら。
SUMIF関数じゃなくて、普通に SUMIFS関数(複数条件を満たすものの合計)を使ってはどうですか。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
行き詰って頭が混乱しており、分かりにくい表記になってしまってすみません。。
元データはすべてシート1です。日付に見えてシリアル値というお言葉に、あ…、と思いました。確かにLEFTでは意味がなかったですね…。

お礼日時:2017/04/16 22:10

致命的な障害が1点あります。

シート1はすべて文字列とのことですが、C列の売上金額も文字なのですよね?SUM系の関数は、文字を相手にしてくれないので、このままでは集計することができません。売上金額だけでも数字にすべきです。

売上金額が数字であれば、次の数式で集計できるはずです。

【シート2!B2セル】=SUMIFS(シート1!C:C,シート1!A:A,TEXT($B$1,"yyyymm")&"*",シート1!B:B,$A2)
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
売上数字は文字列だと思い込んでいただけでした…。
教えて頂いた式は上記のサンプル表では問題なく動いたのですが、集計したい表に入れると上手く集計が取れませんでした。もう一度日付のあたりを見直します!

お礼日時:2017/04/16 22:06

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルの関数を教えて下さい。 例 シート3 11月の売上集計 A 列 B列 C列 D列 1行 い

エクセルの関数を教えて下さい。

例 シート3 11月の売上集計
A 列 B列 C列 D列
1行 い ろ は
2行 りんご 3 2 4‥
3行 累計 15 12 13‥
4行 みかん 2 5 6‥
5行 累計 12 14 16‥
と売上の集計が続いています。
この時、2行目と4行目は手入力してます。
B3は=B2+10月シートB3となります。
C3はC2+10月シートC3となります。
5行目も同様です。
このシートをコピーして12月分を作った時、
B3は=B2+10月シートB3の10月を11月に
変更してます。
自動的に変更できる方法はないですか❓

Aベストアンサー

11月の
>シートをコピーして12月分を作った時、
>B3は=B2+10月シートB3の10月を11月に
>変更してます。
>自動的に変更できる方法はないですか❓
…と言う事ですか?
ならば月の数字をいじれば良いだけですので、CELL関数でシート名を取得して、それを加工しましょう。

 =CELL("filename",A1)
これで、このCELL関数が使われたシート名を含んだ文字列を取得できます。
 C:\Users\あさご\Documents\Excel_file\[売り上げとか.xlsx]12月の売上集計
のような値が返ってきますので、ファイル名の後ろにある「 ] 」の位置をFIND関数で見つけて、その次の文字から最後の文字までをシート名としてMID関数などで切り出し、
”月の売上集計”をSUBSTITUTE関数で削除する(空欄に置き換える)か、得られたシート名の先頭から「月」の文字の前の文字までを別途切り出す。
これで月の数字の部分を得ることができます。
あとはこの数字を足したり引いたりしてINDIRECT関数に入れて参照したいシート名にすればよいです。
1月には12月のシートを参照したいという事になるでしょうから、そのあたりも考慮して作りましょう。
(MOD関数を使うと良いかもしれない)

11月の
>シートをコピーして12月分を作った時、
>B3は=B2+10月シートB3の10月を11月に
>変更してます。
>自動的に変更できる方法はないですか❓
…と言う事ですか?
ならば月の数字をいじれば良いだけですので、CELL関数でシート名を取得して、それを加工しましょう。

 =CELL("filename",A1)
これで、このCELL関数が使われたシート名を含んだ文字列を取得できます。
 C:\Users\あさご\Documents\Excel_file\[売り上げとか.xlsx]12月の売上集計
のような値が返ってきますので、ファイル名の後ろ...続きを読む

QA列とB列の重複を抽出したいのですがA列とB列の値は一部だけ同じ文字です。ご教示お願いします。

エクセル初心者です。重複を見つけるのが仕事です。いろいろやってみたのですがうまくできません。
お知恵をお貸しください。

A列には企業名が入力されています。
B列にも企業名が入力されていますが全く同じ文字ではないのです。

たとえばこういうことです。
A1 (有)雪見酒      B1  雪見
A2 株式会社豪雪地帯   B2 (株)豪雪地帯
A3 ゆきかき本舗     B3 (有)ゆきかき本舗

A列にある企業名とB列にある企業名が同じであればセルを塗りつぶすか○を表示させるように
したいのです。
重複を見つけるのが目的なので、ほかの方法でもかまいません。
すみません、A列のセルとB列のセルが全く同じ名前ならば重複が見つけられたのですが
ここから先がどうしてもわからないのです。。。
申し訳ありませんがどうか教えてください。。。

Aベストアンサー

No4です。以下のマクロを標準モジュールへ登録してください。
--------------------------------------------------
Option Explicit
Public Sub 重複チェック()
Dim maxrow1 As Long
Dim maxrow2 As Long
Dim row1 As Long
Dim row2 As Long
Dim nameT1() As String
Dim nameT2() As String
Dim t1, t2 As Variant
t1 = Time
maxrow1 = Cells(Rows.Count, "A").End(xlUp).row '最大行取得
maxrow2 = Cells(Rows.Count, "B").End(xlUp).row '最大行取得
ReDim nameT1(maxrow1)
ReDim nameT2(maxrow2)
Range("C1:" & "D" & maxrow2).Value = ""
Call makeTable(nameT1, "A", maxrow1)
Call makeTable(nameT2, "B", maxrow2)
For row1 = 1 To maxrow1
For row2 = 1 To maxrow2
If Cells(row2, "C") = "" Then
If Mymatch(nameT1(row1), nameT2(row2)) = True Then
Cells(row2, "C").Value = "○"
Cells(row2, "D").Value = row1
End If
End If
Next
Next
t2 = Time
MsgBox ("チェック完了 処理時間=" & Minute(t2 - t1) & "分" & Second(t2 - t1) & "秒")
End Sub
'余分な文字を削除した結果をテーブルに格納する
Private Sub makeTable(ByRef nameT() As String, ByVal col As String, ByVal maxrow As Long)
Dim row As Long
Dim ary As Variant
Dim name As String
Dim i As Long
ary = Array("㈱", "(株)", "株式", "(有)", "有限", "会社")
For row = 1 To maxrow
name = Cells(row, col).Value
For i = 0 To UBound(ary)
name = Replace(name, ary(i), "")
Next
nameT(row) = name
Next
End Sub
'企業名が一致かどうか判定する
Private Function Mymatch(ByVal name1 As String, ByVal name2 As String) As Boolean
Mymatch = False
Dim pos As Variant
pos = InStr(1, name1, name2, vbTextCompare)
If pos > 0 Then Mymatch = True
End Function
-----------------------------------------------------
一致の精度が悪ければその旨補足してください。
(一致すべきものが一致しない、一致してはいけないものが一致している)
100%解決できる保証はありませんが、多少のチューニングは行います。

No4です。以下のマクロを標準モジュールへ登録してください。
--------------------------------------------------
Option Explicit
Public Sub 重複チェック()
Dim maxrow1 As Long
Dim maxrow2 As Long
Dim row1 As Long
Dim row2 As Long
Dim nameT1() As String
Dim nameT2() As String
Dim t1, t2 As Variant
t1 = Time
maxrow1 = Cells(Rows.Count, "A").End(xlUp).row '最大行取得
maxrow2 = Cells(Rows.Count, "B").End(xlUp).row '最大行取得
ReDim ...続きを読む

QA列とB列が一致するものをC列に合計する場合の関数

エクセル2010を使用しています。
★1のデータA列とB列が一致するものは計算してC列に合計し、
★2のデータにしたい場合の関数を教えて下さい。

初歩的なことですがすみませんどなたかよろしくお願いします。

Aベストアンサー

こんばんは!

>★2のデータにしたい場合の関数を教えて下さい。
というコトですので、一例です。
元データはSheet1にあり、Sheet2に表示させるとします。

↓の画像のようにSheet1に作業用の列を2列設けます。
作業列1のD2セルに
=IF(COUNTBLANK(A2:B2),"",A2&"_"&B2)

作業列2のE2セルに
=IF(OR(D2="",COUNTIF(D$2:D2,D2)>1),"",ROW())

という数式を入れ → D2・E2セルを範囲指定 → E2セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。

そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$E:$E,ROW(A1))),"")
という数式を入れ隣のB2セルまでフィル&コピー!

C2セルに
=IF(A2="","",SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2))
という数式を入れ、A2~C2セルを範囲指定 → C2セルのフィルハンドルで下へコピー!

これで画像のような感じになります。

※ 作業列が目障りであれば遠く離れた列にするか
非表示にしてください。m(_ _)m

こんばんは!

>★2のデータにしたい場合の関数を教えて下さい。
というコトですので、一例です。
元データはSheet1にあり、Sheet2に表示させるとします。

↓の画像のようにSheet1に作業用の列を2列設けます。
作業列1のD2セルに
=IF(COUNTBLANK(A2:B2),"",A2&"_"&B2)

作業列2のE2セルに
=IF(OR(D2="",COUNTIF(D$2:D2,D2)>1),"",ROW())

という数式を入れ → D2・E2セルを範囲指定 → E2セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。

そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SM...続きを読む

Qエクセル【A列とB列の不一致を知りたいです】

初めまして。
お忙しい中、大変申し訳ございませんが、下記に関して、ご指導のほど、宜しくお願い致します。

***************
【例】
A列    B列

あめ    チョコ
ガム    ポテトチップス
チョコ   あめ
       ガム


という列があり、A列はA1000まで続き、B列はB3000まで続きます。
必ずしも、A列の横に同じお菓子名があるわけではありません。

そこで、A列とB列を比較し、B列にしかないもの(上記「例」ではポテトチップス)のセルに色づけ、もしくはC列に、不一致するものだけ「NG」等が記載される数式を教えてください。

お忙しい中、大変申し訳ございませんが、
何卒宜しくお願い申し上げます。

Aベストアンサー

条件付書式で、B列のセルに
「数式が」
=ISERROR(VLOOKUP(B1,A:A,1,FALSE))
で書式を設定し下にコピーすると、無いものだけが書式変更されます。

同じように、B列の隣のC列に
=IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"NG","")
として下にコピーしても隣にNGと表示できます。

Q【EXCEL】条件付き書式、A列、C列、D列、E列が同じ値の時にB列の背景色を変えたい

お世話になります。

エクセルの条件付き書式の数式の使い方について教えて下さい。
A列、C列、D列、E列の値が同じ時にB列のセルの背景を青に
塗りつぶしたいと思っています。

なので、「数式を使用して、書式設定するセルを決定」、
「次の数式を満たす場合に値を書式設定」で、数式を
「=A1=C1=D1=E1」と入力し、書式、塗りつぶし、青、
を選択して、条件付き書式を設定しました。
すると、、、画像のような結果になります。

私の感覚だと画像でセルの値が「11」~「15」の時だけ
B列が青になると思うのですが実際の結果は画像の通りです。

先の話の通りA列、C列、D列、E列の値が同じ時だけ
B列のセルの背景を青にしたい場合はどうい数式になるのでしょうか?

すいませんが、詳しい方、数式(関数?)で説明できる方よろしくお願い致します。

追記
可能でしたらA列、C列、D列、E列が空白の時はB列の背景色をなしにしたいと思っております。
→A列、C列、D列、E列に値があって、かつ同じ値の場合のみB列の背景を青にしたいです。

お世話になります。

エクセルの条件付き書式の数式の使い方について教えて下さい。
A列、C列、D列、E列の値が同じ時にB列のセルの背景を青に
塗りつぶしたいと思っています。

なので、「数式を使用して、書式設定するセルを決定」、
「次の数式を満たす場合に値を書式設定」で、数式を
「=A1=C1=D1=E1」と入力し、書式、塗りつぶし、青、
を選択して、条件付き書式を設定しました。
すると、、、画像のような結果になります。

私の感覚だと画像でセルの値が「11」~「15」の時だけ
B列が青にな...続きを読む

Aベストアンサー

AND関数を使えば判定ができるのでは?
 =AND(A2<>"",A2=C2,C2=D2,D2=E2)

A列には常に判定する数値が入っているなら「A2<>""」は不要です。

参考サイト
http://www.relief.jp/itnote/archives/003940.php


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報