お世話になっております。とても困っています。
皆さんよろしくお願いします。
1月、2月、3月・・・12月というシート名で以下のようなデータが入っているとします。
”1月”のシートの場合
月 日 人 (1)データ (2)データ
1 1 A 10 100
1 2 A 20 150
1 9 A 15 100
1 1 B 10 200
1 5 B 10 100
・ ・ ・ ・ ・
・ ・ ・ ・ ・
このようなデータが1月~12月まで存在します。
このデータから、別のシート(”入力”というシート)で入力した期間に該当する日付のシートから、(1)のデータの合計値と(2)のデータの合計値を”結果”というシートに計算されるという方法を知りたいです。
是非、よろしくお願いします。
”入力”というシート
月 日 月 日
期間 1 1 ~ 2 5
”結果”というシート
(1)計 (2)計
A 50 550
B 35 600
No.2ベストアンサー
- 回答日時:
一発で算出出来る案を提示される強者が現れるかもしれませんが、とりあえず前座という事で、配列数式のお勉強をした結果を報告いたします。
基本通りで何のひねりもありませんが…1.まず、日付での抽出がやりにくいので、作業列を各月のシートに設けてください。入れる式の例: =DATEVALUE("2008/" & A2 & "/" & B2)
........A........B........C..................D........E......................F..................
..1....月......日......月日............人......(1)データ......(2)データ..
..2....1........1........2008/1/1....A......10....................100..............
..3....1........2........2008/1/2....A......20....................150..............
簡便のため、1月のデータはSheet1,2月のデータはSheet2,以下同様とします。集計は、SheetAを設けてそこで行います。
............A.......................B.......................C........
..1....開始月日.........終了月日...................
..2....2008/1/2.........2008/2/3...................
..3................................................................
..4.............................(1)データ.................
..5.............................1月...................2月....
..6....A.....................35.....................30......
..7....B.....................10.....................10......
..8................................................................
..9.............................(2)データ.................
10....A.....................250...................250....
11....B.....................100...................200....
B6セルに入れる式 {=SUM(IF((Sheet1!$D$2:$D$100=SheetA!$A6)*(Sheet1!$C$2:$C$100>=SheetA!$A$2)*(Sheet1!$C$2:$C$100<=SheetA!$B$2),Sheet1!$E$2:$E$100,""))}
配列数式ですので、Ctrl+Shift+Enterで入力確定してください。
・行方向は算出対象範囲が変わる行は修正の必要があります。
・列方向は抽出先シート名を変更する必要があります。置換を使うと楽です。
・データは100行目までと余裕を持たせてありますが、目的に合わせて変更してください。大きくても(再計算に時間はかかるでしょうが)支障は無い様です。
・言わずもがなですが、12月ヶ月のデータを集計列を設けて集計してください。
この回答への補足
ありがとうございます。
さっそく教えていただいた式で試してみましたが、”#VALUE”のエラーとなってしまします。
SUM関数の最後(Sheet1!$E$2:$E$100の後ろ)の「,""」はどういう意味でしょうか。
これを除くと、セルB6の結果は0となります。
よろしくお願いします。
No.5
- 回答日時:
各シートの列タイトル
日付|名前|データ1|データ2|チェック
チェック列の数式(入力シートの日付にマッチする場合1が入ります)
=IF(AND(A2>入力!$A$1,A2<入力!$B$1),1,0)
下にフィル
"入力"シート
A1=集計期間開始の日付を入力 B1=集計期間終了の日付を入力
ここまで出来たら各シートをcheck列で降順ソートします
"結果"シートA1をクリックして、メニューのデータ→統合
集計の方法 合計
統合元範囲で各シートのcheck列が1の行を選択して追加
この時、日付の列を飛ばして名前列からデータ2までを選択します
(5行目まで1が入っていたらBの1列からDの5行までを選択)
各シートを同じ作業で追加
統合の基準 上端行、左端行にチェックでOKを押すと
望みの計算結果が表示されると思います。
自動で処理したい場合は入力シートにボタンを配置して
ボタンに以下のコードを登録して下さい
Sub calc()
Dim i, a, b As Integer
Dim hani() As Variant
For i = 1 To 12 'ワークシートをループ
a = 1
Do While Sheets(i & "月").Cells(a, 1) <> "" '最終行を調べる
a = a + 1
Loop
'降順でソート
Sheets(i & "月").Activate
Range(Cells(1, 1), Cells(a - 1, 5)).Select
Selection.Sort key1:=Range("E2"), order1:=xlDescending
'checkが1の最終行を調べる
b = 2
Do While Sheets(i & "月").Cells(b, 5) <> "1"
b = b + 1
Loop
'統合する範囲を配列に格納
ReDim Preserve hani(i - 1)
hani(i - 1) = Sheets(i & "月").Range(Cells(1, 2), Cells(b, 4)).Address(ReferenceStyle:=xlR1C1, External:=True)
Next
'統合する
Sheets("結果").Range("A1").Consolidate _
Sources:=hani, Function:=xlSum, TopRow:=True, LeftColumn:=True
End Sub
・シート名は半角数字+月として、12月まで作成しないとエラーになります。
ご回答ありがとうございました。
いままで「統合集計の方法」を使ったことが無かったので、勉強になりました。もう少し使い方を勉強してみます。
ありがとうございました。
No.4
- 回答日時:
ANo.2です。
式を入力して確定するときに、Enterでは無くて、Ctrl+Shift+Enterで行いましたか?確定後の式が、中括弧{ }で囲われていますか?
これがうまくいっていないと、#VALUEになると思います。配列数式の解説は、参考URLなどでお調べ下さい。
参考URL:http://home.att.ne.jp/zeta/gen/excel/c01p09.htm, …
No.3
- 回答日時:
わかりやすい方法で
1月シートに2行ほど挿入
A B C D
1 A
2 B
と準備
A1には =DATE(2008,入力!A2,入力!B2)
A2には =DATE(2008,入力!A3,入力!B3)
C1には =SUMPRODUCT((DATE(2008,$A$4:$A$8,$B$4:$B$8)>$A$1)*(DATE(2008,$A$4:$A$8,$B$4:$B$8)<$A$2)*($C1=$C$4:$C$8)*D$4:D$8)
入れて右へフィル、下フィルしてみる。
同様の2行を全てのシートへ挿入、コピィ
結果のシートには1月~12月のシートを合計を串刺しでSUM関数
月ごとにシートを分けた(多分、このままでは年毎にブックが出来る)事が集計を厄介しています。
この際、ひとつのシートに毎月のデータを縦方向にコピィして、今後もひたすら縦方向に入力したほうが良いと思います。
日付も年月日(2008/1/1)で入力しておいたほうが良いでしょう。
試しに、一枚のシートにデータ集めて、結果のシートに入れる関数を考えてみてください。
SUMPRODCUT関数でよいと思いますが、一度で集計できて、年を跨いだ期間も集計できるでしょう。
ご回答いただき有難うございました。
入力されたデータシートが莫大な数の行で存在するので、行数が少ないデータの際に使わせていただきます。
参考になりました。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) エクセルVBA VLOOKUPを使ってのカウント作業 2 2023/02/19 09:03
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) 【VBA】Excelで罫線を引きたい 3 2022/07/14 12:04
- Visual Basic(VBA) 2つの条件に合うセルにデータを転記したい 4 2022/12/02 11:05
- Excel(エクセル) vba シートの並び替え 1 2023/04/19 13:44
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Excel(エクセル) ②Excel 簡単にシートコピーしたら前日の残高と日付を変更させたい→マクロの記録でエラーが出ます 8 2022/07/16 20:40
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) エクセルシートのデータを1列飛ばしで別ブックのシートに貼り付けるマクロが知りたい 2 2023/06/05 22:37
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
エクセルVBAでテキストボックスに入力があった場合のみ、ワークシートに転記したい
Visual Basic(VBA)
-
VBA セルの値と同じ名前のシートにデータを補填するやり方を教えてください エクセルのブックがありま
Excel(エクセル)
-
特定の複数のシートに同じ処理をさせたい
Excel(エクセル)
-
-
4
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
5
Excelで指定した日付から過去の最も近い日付(指定した日付も含む)を下から検索する方法
Visual Basic(VBA)
-
6
Excelにて、ユーザーフォームで、日付けの範囲を指定し、検索しデーターを抽出し 別シートへ転記した
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
クエリを作成しましたが、デー...
-
ACCESS2019 ナビゲーションウィ...
-
Vba Userformを前面に出すについて
-
accessについて(超初心者です)
-
accessの代わりになるもの
-
ms access 2013で、チェックボ...
-
VBA。リストボックスの値を別の...
-
Microsoft365にAccessってあり...
-
Accessレポートのチェックボッ...
-
capeofdragonと申します。 Acce...
-
Accessというソフトで、生年月...
-
access2021 強制終了してしまう
-
access2019の起動が遅い
-
列が255以上のCSVファイルをAcc...
-
Accessで作ったデータベースをw...
-
accessデータを指定したExcel、...
-
アクセス レポートを開いたとき...
-
教えてください! アクセスの書...
-
Accessのスプレッドシートエク...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
access2021 強制終了してしまう
-
実行時エラー3131 FROM 句の構...
-
Vba Userformを前面に出すについて
-
【Access】Dcount関数の複数条...
-
Accessのスプレッドシートエク...
-
ACCESS VBA でのエラー解決の根...
-
Access runtimeでのオプション...
-
Access VBA [リモートサーバー...
-
Accessが強制終了する理由はな...
-
accessデータを指定したExcel、...
-
Accessレポートのチェックボッ...
-
Access IF文でテーブルに存在し...
-
access2019の起動が遅い
-
access2021 更新前に処理をしたい
-
Accessのクエリの結果を、既存...
-
チェックボックスにチェックが...
-
Access で半角スペースと全角ス...
おすすめ情報