SUMIFSと日付変換

Question

分析用fileで年別使用量sheetを作りたい。
下記、日別使用量sheetのA列から年2015～2023に4桁変換(Left関数)し、年別の品名(H列)別の合計(I列)を作成したい。
A列 (年)  　　      H列(品名) 　　　         I列(使用量合計)
2015　　　　　　X　　　　　　　1,234,567
2016　　　　　　X　　　　　　　X,XXX,XXX
　↓　　　　　　 ↓　　　　　　　　　↓
2023　　　　　　X　　　　　　　X,XXX,XXX

2015　　　　　　Y　　　　　　　X,XXX,XXX
2016　　　　　　Y　　　　　　　X,XXX,XXX
　↓　　　　　　 ↓　　　　　　　　　↓
2023　　　　　　Y　　　　　　　X,XXX,XXX

2015　　　　　　Z　　　　　　　X,XXX,XXX
2016　　　　　　Z　　　　　　　X,XXX,XXX
　↓　　　　　　 ↓　　　　　　　　　↓
2023　　　　　　Z　　　　　　　X,XXX,XXX

全data記載の「日別使用量sheet」に数年分の毎日dataがあります。
：日別使用量の日付：A列は「20150101～20230405（単純文字列数値です）」です。
：日別使用量の品名：H列に合致する「複数の品名data(H列)」があります。
：日別使用量の毎日使用量：I列に合計したい「日毎の使用量data(I)」があります。

計算式（分析用file：年別使用量sheetに）
=SUMIFS([使用量.xlsx]日別使用量!$I$5:$I$63235,[使用量.xlsx]日別使用量!$H$5:$H$63235,"H5*",[使用量.xlsx]日別使用量!$A$5:$A$63235,"A5")  ※両sheetの列名は同じです。

としたのですが、式エラーとなり合計できません。どこで間違っていますでしょうか？、日付の年変換（取り出し）で間違っているかと思うのですが‥‥、ご指導願いませんか。
なお、本件はVBAで作成できればさらにbetterです。

tatsumaru77 · Accepted Answer

>本件私の目的が達成できましたので、完了とさせてください。

了解しました。完了ということですので、このスレッドを離れます。

tatsumaru77 · Answer

素朴な疑問なのですが、最大行を求める必要があるのでしょうか。
関数式の場合、十分大きな行を設定しておけば、それで問題ないはずで、
きちんとした最大行を設定しなくても求める結果が得られるはずです。
1,048,576 行が最大行なので、
ActiveCell.FormulaR1C1 = _
"=SUMIFS([使用量.xlsx]日別使用量!R5C3:R63718C3,‥‥略
を
ActiveCell.FormulaR1C1 = _
"=SUMIFS([使用量.xlsx]日別使用量!R5C3:R1048576C3,‥‥略
にしておけば、それで十分かと思います。

又、日別使用量が更新されるのは、どのような間隔でしょうか。
毎日ですか、それとも、数日に１回でしょうか。
又、更新されたタイミングで、即時に年別使用量に反映させたいのでしょうか。

マクロで算出する方法としては、
１案：算出の計算式を年別使用量に設定する。（現在、あなたが行っている方法です）
この場合、日別使用量が更新されると、その内容が、即座に年別使用量に反映されます。
但し、使用量.xlsxを開いておく必要があります。
又、データ量が増えると、excelが重くなる欠点があります。

２案：算出した結果の値を、年別使用量に設定する。（当初私が提案した方法です）
マクロを実行した時点の結果が年別使用量に設定されるので、日別使用量が更新されても、
マクロを実行しないと、年別使用量に反映されないという欠点があります。
その為、即座に反映したいなら、日別使用量更新時、その都度マクロを実行する必要があります。
但し、データ量が増えても、excelは重くなりません。

私としては、当初２案のつもりでしたが、あなたが１案を採用と考えていたので、２案は提示するのをやめた次第です。
又、１案の場合、最終行にこだわる必要はないはずなので、マクロで作成せずに、手作業で全て計算式を埋め込むことが可能である考えてます。
そのため、１品名分の年別使用量シートを手作業で作成し、それを全品名分コピーするマクロを作成すれば良いと思った次第です。

tatsumaru77 · Answer

>下記「R63718C3」の部分です。
>「Cntl+↓」をマクロで記述するにはどの様に？、ご教示願えませんでしょうか？。

63718を最大行に変えるマクロです。

Public Sub 最大行算出()
    Dim wb1 As Workbook     '使用量.xlsx
    Dim sh1 As Worksheet    '日別使用量
    Dim sh2 As Worksheet    '年別使用量
    Dim ws As Worksheet     '作業用
    Dim maxrow1 As Long     '日別使用量　A列の最大行
    Dim fmt As String
    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & "使用量.xlsx")
    Set sh1 = wb1.Worksheets("日別使用量")
    maxrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    Set sh2 = ThisWorkbook.Worksheets("年別使用量")
    fmt = "=SUMIFS([使用量.xlsx]日別使用量!R5C3:R63718C3,[使用量.xlsx]日別使用量!R5C9:R63718C9,RC7,[使用量.xlsx]日別使用量!R5C1:R63718C1,RC1)/1000"
    fmt = Replace(fmt, "63718", maxrow1)
    sh2.Range("B6").FormulaR1C1 = fmt
End Sub

上記のマクロは、使用量.xlsxをオープンし、日別使用量（A列）の最大行を求めています。(maxrow1に最大行が設定されます)
この値で、63718を置き換えればよいので、Replace関数で置き換えています。置き換えた結果を年別使用量のB6の計算式に設定しています。

tatsumaru77 · Answer

補足ありがとうございました。
年別使用量は、品名毎にシートをつくるということなので、以下のようにしては、いかがでしょうか。
１．年別使用量の１シートのみ手作業で作成する。（品名は任意　仮にXとします）
このシートのシート名は「年別使用量雛型」とします。

２．下記の機能をもつマクロを作成し、実行する。（シートコピー用マクロ）
①シート：年別使用量雛型をコピーし、全品名分のシートを作成する。
②全品名を登録したシートを予め作成しておき（シート名は「品名一覧」とする）、
　そのシートの品名を参照し、コピーを行う。
③コピーされたシートのシート名は、その品名となる。
（品名：Aが処理された場合、シート名は「A」となる）
④コピーされたシートのH列（品名）は、全て、その品名で置き換わる。
（品名：Aが処理された場合、H列の品名は全て"A"となる）

両シートの詳細は下記URL参照
年別使用量雛型
https://gyazo.com/3df1e21a9250938af67547b3b7f4fabb
黄色のセルが置き換わります。

品名一覧
https://gyazo.com/f230d7ab82e83c8731aaaf787108031a
緑色のセルが処理対象の品名となります。

３．上記により1年間は、メンテナンスが不要になるかと思います。
来年になった場合、以下のようにします。
①2024年分の行を手作業で年別使用量雛型へ追加する。
②既存の各品名毎のシートを全て削除する。・・・・シート削除用マクロを作成する。
③マクロを起動して、シートコピー用マクロを実行する。

上記で、いかがでしょうか。
上記で良ければ、シートコピー用マクロとシート削除用マクロの提供は可能です。
念のため確認ですが、
①年別使用量のH列が品名で間違いないでしょうか。
②H列の品名を置換すれば、置換後の品名のデータが算出可能と理解していますが、それであってますか。
（他に置き換える箇所はないと理解していますが、間違いないでしょうか）

tatsumaru77 · Answer

いくつか不明点があるので、確認します。
１．シート：日別使用量のデータの開始行は５行からでしょうか。
同様に、シート：年別使用量データの開始行は５行からでしょうか。

２．日別使用量のどこかの列にLEFT(A5,4)を設定したと思われますが、どの列でしょうか。

３．年別使用量のI列に計算式を設定し、使用量合計を算出していますが、
①A列の年は、どのような式を入れてますか？
②H列の品名は、どのような式を入れてますか？

４．マクロの記録でB6を選択し、計算式を入れてますが、これは、B5の誤りではないでしょうか。
又、B列の意味と設定したい値を、計算式ではなく、日本語で説明していただけませんでしょうか。

５．マクロの記録でC6を選択し、計算式を入れてますが、これは、C5の誤りではないでしょうか。
又、C列の意味と設定したい値を、計算式ではなく、日本語で説明していただけませんでしょうか。

６．年別使用量の空白行について
提示された例では
A列 (年) 　　 H列(品名) 　　　 I列(使用量合計)
2015　　　　　　X　　　　　　　1,234,567
2016　　　　　　X　　　　　　　X,XXX,XXX
　↓　　　　　　 ↓　　　　　　　　　↓
2023　　　　　　X　　　　　　　X,XXX,XXX
　　　　　　　　　　　　　　　　　　　　　・・・・空白行
2015　　　　　　Y　　　　　　　X,XXX,XXX

のように空白行がありますが、I列のI5に計算式を入れて、オートフィルで下へコピーすると、
空白行の箇所は、0が表示されますが、それで良いのでしょうか。
それとも、計算式に、値が０なら空白を表示するような仕組みを追加することを考えているのでしょうか。

７．年別使用量の他の列について
D,E,F,G,J,K等の列について、どのような項目なのか、提示していただけませんでしょうか。
上記列が全て空白でよいなら、説明は不要です。
設定が必要なら、その列の意味と計算式も提示してください。
（B,Cについては、項番４、項番５で説明されるので不要です）

tatsumaru77 · Answer

No3です。
>あと、私はVBAは素人なのですがよろしいでしょうか？。
当面は、マクロをコピペして、使えばよいので、マクロの登録方法と実行方法が判れば問題ないです。
将来的に、業務内容に変更が発生し、マクロの修正が必要になったとき、
どうするかですね。
自力で修正できれば問題ないですが、できないときに問題が発生します。
（ここで質問し解決する場合もありますが、解決に至らない場合もあります）
今後、マクロを覚える気がないなら、そのリスクを避る為にも、マクロは使用しないほうが良いかと思います。
マクロを覚える気があるなら、今回、提示されたマクロを試しに使用してみる価値はあるかと思います。

>※VBAで‥‥と言ったのは、本件計算式が随分多いのです。毎月SUMIFS式の最終行変更が必要なのです。
>１．分析用fileは品名ごとにsheetを作り、SUMIFS式作成→横にcopyぺ→縦にもcopyぺが大変な上、
>２．使用量fileのdataは月単位で増えます。日別使用量!$I$5:$I$63235dataの様に先頭行は固定ですが、最終行は実は可変($I63235)なのです。

上記の件を全て、マクロで行うことは可能かと思いますが、仕様が不明なので、具体的なマクロを提示することは、出来ません。
仕様を提示していただければ、検討いたします。
但し、あまりにも、作業量が膨大になる場合は、私の方でギブアップするかもしれませんが・・・・。

tatsumaru77 · Answer

No3です。
>3項のクリアの意味が分かりません。
マクロを実行すると、シート：年別使用量の2行目以降をマクロで、設定します。従って、赤線で囲んだところは、（あなたが事前に何かを設定していても）空白になります。
という意味です。

tatsumaru77 · Answer

以下、VBA前提での補足要求です。
１．分析用fileにマクロを格納しますがよろしいでしょうか。拡張子が.xlsmになります。
２．分析用fileと使用量.xlsxは同じフォルダ内にある前提で、よろしいでしょうか。
３．シート：年別使用量のB列～G列、J列以降は、全て、クリアされますが、よろしいでしょうか。
４．品名が変わったとき、1行あけて、次の品名を設定していますが、この機能を実装するために、
作業用のシートが１つ必要になりますが、よろしいでしょうか。

fujillin · Answer

No1です。

＞この式で、「0」が返りました。
何をどのようにして、どの式で計算したのかさっぱりわかりません。
SUMIFS関数を使っているのでしょうから、想像するところ、計算はできていて、該当するものが無いので「0」の結果になっているものと思います。
ヒットしない理由については、No1に記した通りです。

＞LEFT関数にて「2015」に変換した上で、合致させたいのですが、改善策が見つけれません
SUMIFS関数内では、セル範囲に対してLEFT関数は使えないと思います。
多分、式の入力時点ではねられると思います。

どうしてもそのロジックで行いたい場合には、一番簡単な方法としては、
作業列にLEFT関数の結果を表示するようにして、SUMIFS関数ではそちらの列の値を参照するようにすれば良いでしょう。
一方で、SUMIFS関数ではなく、SUMPRODUCT関数を利用する方法で計算を行えば、LEFT関数を関数内で使用することは可能です。

※ No1の回答内容もきちんと伝わっていないように見受けられますので、こちらの内容はもっと伝わらないだろうと推測します。
　理解できない場合は、スルーしてください。

fujillin · Answer

こんにちは

通常の場合、SUMIFS関数は該当がなければ0を返すだけなので、エラーにはなりにくいのですが・・

＞式エラーとなり合計できません。
セル範囲の参照が反映できないものと推測します。
まず、参照先のブックが開いている状態でないと、ご提示の参照アドレスでは参照できずにエラーになります。
また、開いていても該当シートが存在しない等の場合もエラーになります。

なお、ご質問には直接関係ありませんけれど・・
検索値として指定している「"H5*"」、「"A5"」は「H5***」（*はワイルドカード）のような文字列と、「A5」という文字列を検索する指定になっています。
想像するところ、質問者様の意図とは違いそうに思われます。

また、ご提示では「"H5*"」となっていますけれど、もしも、A列に対してワイルドカードを用いようと考えているのであれば、A列の値が文字列になっていないと上手くいかないと思われます。
実際にどのような形式になっているかが不明ですが、ご提示の「20150101」は、エクセルには数値として解釈される可能性もありますので・・

SUMIFSと日付変換

>本件私の目的が達成できましたので、完了とさせてください。

素朴な疑問なのですが、最大行を求める必要があるのでしょうか。

>下記「R63718C3」の部分です。

補足ありがとうございました。

いくつか不明点があるので、確認します。

No3です。

No3です。

以下、VBA前提での補足要求です。

No1です。

こんにちは

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング