【3月6日実施】システムメンテナンス実施のお知らせ

分析用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です。

質問者からの補足コメント

  • へこむわー

    fujillinさん、ご返信ありがとうございます。
    この式で、「0」が返りました。更に「日別使用量sheet」の日付列20150101を2015としたところ、合計値が返って来ました。(@_@)
    質問した計算式に間違い(抜け)がありました、正しくは次の通りとしたいのです、
    =SUMIFS([使用量.xlsx]日別使用量!$I$5:$I$63235,[使用量.xlsx]日別使用量!$H$5:$H$63235,"H5*",LEFT([使用量.xlsx]日別使用量!$A$5:$A$63235,4),"A5") ※両sheetの列名は同じです。
    即ち、年別合計としたいので、日別使用量sheetでは日付(A列)が「20150101」と8桁なので、LEFT関数にて「2015」に変換した上で、合致させたいのですが、改善策が見つけれません。再度ご指導願えませんか?。

      補足日時:2023/04/16 19:00
  • つらい・・・

    tatsumaru77さん,ありがとうございます。
    >以下、VBA前提での補足要求です。

    1~4項=OKですが、3項のクリアの意味が分かりません。

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

    No.3の回答に寄せられた補足コメントです。 補足日時:2023/04/17 22:32
  • つらい・・・

    tatsumaru77さん、ありがとうございます。取敢ず「マクロの記録」を使って作成しました。
    ※文字数over警告なので一部のみ、お許しを‥。
    Range("B6").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIFS([使用量.xlsx]日別使用量!R5C3:R63718C3,[使用量.xlsx]日別使用量!R5C9:R63718C9,RC7,[使用量.xlsx]日別使用量!R5C1:R63718C1,RC1)/1000"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIFS([使用量.xlsx]日別使用量!R5C4:R63718C4,[使用量.xlsx]日別使用量‥‥以下略
    毎月dataが増えるので、「R5c3:最終行C3」にする方法は?。

      補足日時:2023/04/19 00:00
  • tatsumaru77さん、度々のご指導ありがとうございます。文字数制限がありますのでいくつかに分けてご返事します。
    その1
    ●この度のご回答を考え、商品名ごとにsheetを作成する事にしました。
    ●日別使用量fileのdataは行5から始まっています。年別使用量file-sheetは行6から始めます。(各品名sheetも同じ)。
    ●fujillinさんの回答(#2)でsumifs関数内で年数取り出しのleft関数が使えない事が分かり、
    日別使用量fileの日付列の左に列を挿入し、B列となった日付からLeft式にて「年(4桁)」を取り出し行5~行613718迄copy、その上で、A列COPY重ねでA列に「値」(Left式は消滅)貼りつけ、よって、投稿時の列名は1づつ右にずれました。結果:A列2015(書式標準)/B列20150101(8桁標準書式)です。

      補足日時:2023/04/19 22:04
  • その2
    ●日別使用量file===data値(J~AF)には空白もあります。品名には空白行はありません。
    A-    B-  C- ~ G- H-  I- J- ~ AFまで-
    使用年-使用年月日-品種A~品種G-検証用-品名-品番aa~品番DA
    (4桁)-(8桁)-C~GはJ~AF行の集計
    ●年間使用量file===空白列のdataは0として計算したい。
    A-  B- ~ G- H- I- J- ~ AFまで-
    使用年-品種A~品種G-検証用-品名-品番aa~品番FA
    (4桁)-B~GはJ~AEの集計。J~AFは日別使用量fileの年値の集計(sumifs式)。

      補足日時:2023/04/19 22:07
  • その3
    >5.マクロの記録でC6を選択し、‥‥略
    これは意味はありません。6行目にしたのは、5行目をコメント行として使いたいので。。

    >6.年別使用量の空白行について
    これは、上述の通り品名別にsheetを作る事に変更したので無くなりました。

    ご質問の1~4はその1、2で説明したつもりですが‥‥、ご理解できますでしょうか?。

      補足日時:2023/04/19 22:10
  • うーん・・・

    tatsumaru77さん,ありがとうございます。

    あの~、tatsumaru77さんに2回目の補足説明で記載したマクロでどうなのでしょう?。
    日別使用量fileからダイレクトに年間使用量fileをマクロで作れるのですが‥‥、
    これまでのご指導でマクロ化ができましたので後、分からないのは「列の最終行の指定方法」です。即ち、下記「R63718C3」の部分です。
    「Cntl+↓」をマクロで記述するにはどの様に?、ご教示願えませんでしょうか?。
    なお、URLのクリックでは、USER登録が必要なのですね、個人登録となりますので遠慮させて頂きたく存じます。悪しからず。
    Range("B6").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIFS([使用量.xlsx]日別使用量!R5C3:R63718C3,‥‥略

      補足日時:2023/04/21 00:52

A 回答 (10件)

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



了解しました。完了ということですので、このスレッドを離れます。
    • good
    • 0
この回答へのお礼

tatsumaru77さん,ありがとうございました。
重ねて御礼申し上げます。

お礼日時:2023/04/22 23:30

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


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

ActiveCell.FormulaR1C1 = _
"=SUMIFS([使用量.xlsx]日別使用量!R5C3:R1048576C3,‥‥略
にしておけば、それで十分かと思います。

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

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

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

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

tatsumaru77さん、ありがとうございます。

度重ねご指導、ありがとうございます。
ご回答#8/#9に対し、補足かねて御礼申し上げます。
>最大行を設定しなくても求める結果が得られるはずです。1,048,576 行が最大行なので、
※そうでしたね、excelは1百万行超えもあるので、十分範囲内になります。
そこで、最終行取得方法として$A:$A、$H:$Hをマクロ内に記述する事にしました。ctrl+↓は都度修正が必要でした。また、ctrl+↓は途中に空白行があるとそこを最終行としますので。

>又、日別使用量が更新されるのは、どのような間隔でしょうか。毎日ですか、それとも、数日に1回でしょうか。
※これは、日別は毎日増えています、年間使用量fileは極端に言えば年1でも良いわけです。実態としては毎月の数値変化も見たいので、月1回程度のマクロ実行になります。

>但し、使用量.xlsxを開いておく必要があります。又、データ量が増えると、excelが重くなる欠点があります。
※そうですね、年間使用量file(xlsm)は日別使用量file(xlsx)を開かないと全セルが「#value!」になっています。

>そのため、1品名分の年別使用量シートを手作業で作成し、それを全品名分コピーするマクロを作成すれば良いと思った次第です。
※先の補足で、日別使用量file、年間使用量fileと申し上げていますので
既に貴第2案を参考にしていますよ。年間File(xlsm)に品名別のsheetを作成し、title行など生成のうえ、年data先頭行にSUMIFS式をマクロとして作成、以降コピペにて、sheet完成させ、次の品目sheetの年data行、同様繰り返しとしました。マクロには前回作成済み年間、dataの消去を含めています。

>63718を最大行に変えるマクロです。Public Sub 最大行算出()
※せっかくご指導頂いたのですが、VBAでなくとも上述内容のマクロで十分目的達成できました。悪しからず。

以上にて本件私の目的が達成できましたので、完了とさせてください。ありがとうございました。

お礼日時:2023/04/21 23:57

>下記「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の計算式に設定しています。
    • good
    • 0

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


年別使用量は、品名毎にシートをつくるということなので、以下のようにしては、いかがでしょうか。
1.年別使用量の1シートのみ手作業で作成する。(品名は任意 仮にXとします)
このシートのシート名は「年別使用量雛型」とします。

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


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

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

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

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

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


1.シート:日別使用量のデータの開始行は5行からでしょうか。
同様に、シート:年別使用量データの開始行は5行からでしょうか。

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

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

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

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

6.年別使用量の空白行について
提示された例では
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が表示されますが、それで良いのでしょうか。
それとも、計算式に、値が0なら空白を表示するような仕組みを追加することを考えているのでしょうか。

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

No3です。


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

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

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

No3です。


>3項のクリアの意味が分かりません。
マクロを実行すると、シート:年別使用量の2行目以降をマクロで、設定します。従って、赤線で囲んだところは、(あなたが事前に何かを設定していても)空白になります。
という意味です。
「SUMIFSと日付変換」の回答画像4
    • good
    • 0

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


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

No1です。



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

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

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


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

fujillinさん、ご返信ありがとうございます。
>SUMIFS関数内では、セル範囲に対してLEFT関数は使えないと思います。多分、式の入力時点ではねられると思います。

やはり、sumif関数内での使用はダメなのですね。ありがとうございました。

>作業列にLEFT関数の結果を表示するようにして、SUMIFS関数ではそちらの列の値を参照するようにすれば良いでしょう。

検証に少し手間取り、遅くなりました。この方式(年列作成)にて本件正常な数値が得られました。ありがとうございました。

お礼日時:2023/04/16 23:46

こんにちは



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

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


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

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

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