毎月送られてくるエクセルデータから、その月の日数の間隔でデータを抽出する方法

エクセルデータで縦に日数、横に時間で日にち別時間別データが顧客の数だけ1つのシートにまとめて送られてきます。

そのデータがA1からW300まであったとすると、11月は30日までなので、1番目の顧客のデータではW30のセルの値がその月の合計値とします。次の顧客の合計値は日数の間隔なのでW60になります。あとは30行毎に各顧客の合計値があります。

現在は月によって行数の間隔が違ってくるので、結局毎月手作業で数値を拾うことになっています。

このように、月によって変動する間隔でも、顧客の合計値を抽出する関数はないでしょうか？その月の日数を決まったセルに入力すれば、算出できるという数式でもかまいません。
宜しくお願いします。

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

• ありがとうございます。日データが31行目になるのはわかっていましたが、回答をいただくうえで区切りのいい数字のほうが私が理解しやすいと思いまして、そのようにしました。
  また、顧客ごとに番号があるので、その番号から各顧客の合計値を検索はできないでしょうか？
  ぜひ教えていただいた関数をご教授ください。よろしくおねがいします。

    補足日時：2018/11/30 16:30

No.1 回答者： angkor_h 回答日時： 2018/11/30 16:00

> 11月は30日までなので、1番目の顧客のデータではW30のセルの値がその月の合計値とします。

これは変です。日データーが30行あるので、合計行があるならば、それは31行目のはずで、
次は32-61行、その合計は62行目、となるはずです。

月の日数を求める場合は、
「次月の1日の前日を示すシリアル値から「日」を求める」が一般的です。閏年でも適用できます。
次に、データーの顧客順番リストから「順番」を求めてそれを乗じれば、
開始行と末尾行、あるいは合計行が計算できます。

この回答へのお礼

ありがとうございます。開始日が、案件によってバラバラなので、その点も悩みです。月によって日数が違いますが、開始行と末尾行を設定しうまくそこにデータを張り付けて合計を出してます。

お礼日時：2018/12/05 09:56

No.2 ベストアンサー 回答者： 銀鱗 回答日時： 2018/11/30 16:46

一つずつ手作業で確認をするとき、どんな手順で処理をしていますか？

その手順通りにマクロなり関数なりを記述すれば良いのです。

11月は30日まで…をどうやって調べたらいいのか分からない。
ということであれば「12月1日の1日前が11月の末日」であることを利用すれば良い。
11月の翌月の1日から1日前の日付を求めれば良いってこと。
これは、うるう年でも正確に日数を求めてくれるので便利です。
　＝DAY(”2018/12/1”-1)
　＝TEXT(”2018/12/1”-1,"dd")
とでもしてみてください。
”2018/12/1”の１日前の日付けを表示してくれます。
2018/12/1の部分は
　DATE(2018,11+1,1)
としても良いでしょう。
「2018年11月の翌月の1日」
ってことです。
さらにこのまま引き算を省略するために
　DATE(2018,11+1,0)
としても良いでしょう。
するとこうなります。
　＝DAY(DATE(2018,11+1,0))
　＝TEXT(DATE(2018,11+1,0),"dd")

年（2018）と月（11）がデータのどこか決まったセルに入力されているなら、
そのセルを参照させれば毎月決まった処理をさせることができます。

これで11月は「30日（30行）」毎にデータが並ぶと判断させられます。

・・・
のように一つずつ不明点を解決しながら処理方法を考えましょう。
　「代わりに作って」
は【疑問の解決】ではなく、単なる【作業依頼】です。



・・・余談・・・
ちなみに自分ならW列のデータを30行毎に拾う場合は
OFFSET関数か、INDIRECT関数を使って拾います。

OFFSET関数の場合
1件目
　=OFFSET(W1,DAY(DATE(2018,11+1,0))*1-1,0,1,1)
2件目
　=OFFSET(W1,DAY(DATE(2018,11+1,0))*2-1,0,1,1)
3件目
　=OFFSET(W1,DAY(DATE(2018,11+1,0))*3-1,0,1,1)
…
100件目
　=OFFSET(W1,DAY(DATE(2018,11+1,0))*100-1,0,1,1)

INDIRECT関数の場合
1件目
　＝INDIREXT(”W"&DAY(DATE(2018,11+1,0))*1)
2件目
　＝INDIREXT(”W"&DAY(DATE(2018,11+1,0))*2)
3件目
　＝INDIREXT(”W"&DAY(DATE(2018,11+1,0))*3)
…
100件目
　＝INDIRECT(”W"&DAY(DATE(2018,11+1,0))*100)

この回答へのお礼

ありがとうございます。INDIRECT関数は初めてなので、挑戦してみます。
また経過を報告させてください。ありがとうございます。

お礼日時：2018/12/05 09:56

No.3 回答者： googoo900 回答日時： 2018/12/01 14:26

エクセルの関数とフィルター機能を使ったやり方を紹介します。

◆前提
データはA1からW300まであるとします。
1行目は見出し行になっているものとします。
1行目が見出し行でなければ追加して見出し行にして、連番を入れておきます。
A列は見出し列になっているものとします。
A列の顧客ごとの合計値が記入されているセルに、共通の語句(たとえば『合計』など)が記載されていると便利です。

◆手順
１．データ最下行から2行下の行(1行あける)に集計値を記載します。セルB302に『=SUBTOTAL(9,B2:B300)』を入力して、右方向にコピペします。（W列だけでよいのかもしれませんが…）
２．1行目が見出し行になっていなければ、行を追加して連番を入れておきます。(見出し行になっていれば不要)
３．1行目全体を選択して、メニュー　→　データ　→　フィルター　とします。
４．A1セルに表示された▼マークをクリックして、テキストフィルター　→　指定の値を含む　→　合計値共通の語句を入力して　→　OKをクリック。
共通の語句がない場合は、合計値の項目にのみチェックをつけます。
５．最下行のSUBTOTAL関数を入れたセルに、表示セルの合計値が表示されます。（完了）

※添付図参照です。

ちなみに、フィルターをなくして全表示する際は、データ　→　クリア(フィルターの右隣)　とします。

この回答へのお礼

ありがとうございます。早速挑戦してみます。また経過をご報告したいと思います。

お礼日時：2018/12/05 09:56