[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数シートに同条件でまとめてフィルターをかけたい』（まる）
いつも大変お世話になっております。
【複数シートに同条件でまとめてフィルターをかけたい】
現在、支店別・売上項目別で合算を出し、横にある「条件指定リスト」で条件を選択すると合算シートの合計も条件に合わせて変わる…というデータを作成しています。
・全支店合計シート(１枚)
・支店別内訳シート(２５枚)
支店別シートを基に合計シートを作成しています。
全支店合計シート
A B C D E F
2 支店名 雑貨 文具 化粧品 食品 合計
3 北海道 100 0 0 50 150
4 秋田 100 0 100 0 200
5 東京 0 100 0 100 200
〜
28 合計 200 100 100 150 550
上記のようなデータの横に
H I
2 条 件 指 定 リ ス ト
3取引年 2019年
4取引先名 ○○(株)
5営業担当者 山田
6所在地 東京
というような条件リストを作成しています。
(特に指定がない場合は空白、空白なら全表示)
支店ごとに１枚づつシートがあるので、合計シートに反映させている条件と同じものでフィルターをかけられればと考えております。
支店シートは
A B C D E F G H I
1 北海道(支店名)
2 (空白列)
3 ナンバー 取引年 取引先名 営業担当者 所在地 雑貨 文具 化粧品 食品 合計
4 no1 2019年 ××(株) 山田 東京 100 0 0 50 150
〜
255 合計 100 0 0 50 150
上記のような作りになっています。
ナンバー・売上商品の指定はなしで問題ありません。
欲を言うと、255行目の合計列は指定条件に対象が無くても表示したままにしたいです。
何か表自体に不都合があれば変更可能ですので、解決策を教えていただけますとありがたいです。
よろしくお願い致します。
＜ 使用 Excel：unknown、使用 OS：unknown ＞
使用スペックを選択し忘れておりました、申し訳ありません！
（まる） 2021/01/21(木) 14:00
こんにちは ＾＾ なにやら 数が多くて。。。とても大変そぉですね ＾＾； 複数、複雑条件で抽出するにはデーターのフイルター詳細 とか、ピボットテーブル ← あまり使った事が無いので 情報の形態によって、使えるかよくわからないのですが、とても べんりそぉです。 等をお使いになると、簡単、便利かと、気が付いた点だけで 済みませんが。北海道とか他の支店の中でも東京の山田ご担当様 とお取引が有ると言う事なのですね。←いやちょっと気になった もので。。。すみません。m(__)m （隠居じーさん） 2021/01/21(木) 14:35
コメントありがとうございます。
まったく同じ形のシート23枚なのに、何回も同じ条件でフィルターをかけるのが手間…という悩みなので、ピボットだと表にフィルターかけてる状態とあまり変わらないかもしれないです…申し訳ありません。
（北海道なのに東京の営業がいるのは、東京本社で商談→支店で取引という流れです；）
（まる） 2021/01/22(金) 12:56
（マナ） 2021/01/22(金) 15:02
コメントありがとうございます。
仰る通り自分で1からマクロを書いたことがありません…
いつもやりたいことが複雑すぎて、ネットでフォーマットを探して参照セルやシート名を変更するくらいでしか対応できていないのですが、今回はネットでも見つけられなかったためこちらで質問させていただきました。
（まる） 2021/01/22(金) 15:33
（隠居じーさん） 2021/01/22(金) 16:16
Sub test()
Dim ws As Worksheet
Dim tbl As ListObject
For Each ws In Worksheets
If ws.Name = "全支店合計" Then
Set tbl = ws.ListObjects(1)
tbl.AutoFilter.ShowAllData
tbl.Range.AutoFilter tbl.ListColumns("取引年").Index, "2020年"
End If
Next
End Sub
（マナ） 2021/01/22(金) 16:26
それです！
いつもこちらで質問する時に情報が少なすぎることが多々あったのですが、今回は逆に多すぎたかもしれません…
・合計シートI列の条件で、支店シートB〜E列をフィルターで反映させたい
・可能であれば、どんな条件(全部空白等)でも支店シートの合計列は表示させたい…
・条件に一致するデータがない支店シートは変更なし
上記のようにできたら嬉しいな…という感じです
（まる） 2021/01/22(金) 16:33
あ。。。マナ さん すみません。。。 非力ですが頑張ってみます。＾＾； ← 土日は暇してますし ダメでしたら両手上げます。。。(￣▽￣)。。。(#^.^#)。m(__)m まる さん すみません。いまテスト環境整えています。御猶予を。 m(__)m （隠居じーさん） 2021/01/22(金) 16:49
数式までありがとうございます！
テストしてみたのですが、
Set tbl = ws.ListObjects(1)
エラーが起きてしまいデバッグで確認してみたところ
上記が黄色くなりました。
正直どの文字列が何を示していてどうなっているのか…というくらいマクロについて無知なので
直し方も分からず・・・・・
何から何まで大変申し訳ありません；；
隠居じーさん さん＞
色々お手間お掛けして申し訳ありません。
完全に弊社の都合なのですが、金曜日がノー残業デーで17：30で退社してしまう為
お返事が月曜日になってしまうかと思われます…！
（まる） 2021/01/22(金) 16:55
＞＞金曜日がノー残業デーで17：30で退社、ご案内の件 了解いたしました。マナさんのコードはテーブル使用 が前程の様です。テーブルではない事が解っただけでも とても参考になります。では後日また。。。m(_ _)m （隠居じーさん） 2021/01/22(金) 17:06
シート名 全支店合計
|[A] |[B] |[C] |[D] |[E] |[F] |[G]|[H] |[I]
[1]| | | | | | | | |
[2]|支店名|雑貨 |文具 |化粧品 |食品 |合計 | |条 件 指 定 リ ス ト|
[3]|1支店 |1,265,000|1,270,000|1,308,000|1,230,000|5,073,000| |取引年 |2019年
[4]|2支店 |1,282,000|1,205,000|1,212,000|1,328,000|5,027,000| |取引先名 |AAA
[5]|3支店 |1,298,000|1,212,000|1,258,000|1,245,000|5,013,000| |営業担当者 |担当1
[6]|4支店 |1,241,000|1,268,000|1,292,000|1,186,000|4,987,000| |所在地 |海
[7]|5支店 |1,249,000|1,226,000|1,249,000|1,211,000|4,935,000| | |
[8]|6支店 |1,170,000|1,291,000|1,216,000|1,233,000|4,910,000| | |
↓
[28]合計 |31,358,000|31,350,000|31,360,000|31,313,000|125,381,000
支店シート名 1支店
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J]
[1]|1支店 | | | | | | | | |
[2]| | | | | | | | | |
[3]|ナンバー|取引年|取引先名|営業担当者|所在地|雑貨 |文具 |化粧品|食品 |合計
[4]| 10001|2019年|AAA |担当1 |海 |1,000|2,000| 2,000|6,000|11,000
[5]| 10002|2019年|FFF |担当6 |池 |1,000|4,000| 7,000|2,000|14,000
[6]| 10003|2019年|EEE |担当5 |湾 |1,000|8,000| 4,000|9,000|22,000
[7]| 10004|2019年|DDD |担当4 |山 |8,000|6,000| 9,000|4,000|27,000
↓
|[A] |[B] |[C]|[D] |[E]|[F] |[G] |[H] |[I] |[J]
[251]|10248|2020年|BBB|担当2|川 | 4,000| 5,000| 3,000| 3,000| 15,000
[252]|10249|2020年|AAA|担当1|海 | 5,000| 9,000| 1,000| 1,000| 16,000
[253]|10250|2020年|EEE|担当5|湾 | 8,000| 5,000| 8,000| 6,000| 27,000
[254]| | | | | | | | | |
[255]|合計 | | | | |1,265,000|1,270,000|1,308,000|1,230,000|5,073,000
[256]| | | | | | | | | |
2支店、3支店。。。続く25支店まで。 の様なフォーマットが前程です。相違点が有りましたら 教えて頂くと、対応可能です。多分。。。＾＾； （隠居じーさん） 2021/01/23(土) 11:12
詳細が不明な箇所が有りますので修正は必要かと思います。
また、きっと、もっとスマートな方法が有ると思いますので
私も一応、作ってみるという事ですので、A＾＾； ← 半分
手が上がってるかも ＾＾； 別案等御座いましたら
回答してあげて下さいませ。エラー処理 ← ありません。
たとえば 全支店合計 シートが存在しないとか(◎_◎;)
これも勝手にお名前つけてますので、実物にご変更を。！
最終的に簡単なエラー処理も設置しておくといいですね。
週明けのレスをお待ちいたします。。。m(__)m
'**********************************************************
'* Function tChk で使用している項目名を実際の項目名と相違
'* する場合は実際の物に変更してください。
'* A255の値もご確認ください。
'* If wS.Name Like "*支店*" Then の "*支店*" は
'* 各、支店シートの名前が[支店]（[]は含まない）を含まない
'* 場合は共通して存在する文字列に置き換えて下さい。
'* 共通文字列が無い場合はシート名マスタを作成するとか別途
'* 工夫が必要ですのでお知らせください。
'**********************************************************
Option Explicit
Sub OneInstanceMain()
Dim i As Long
Dim j As Long
Dim wS As Worksheet
Dim cM1() As Variant
Dim cM2() As Variant
Dim r As Range
Dim var As Variant
Dim eRrStr As String
Dim errTx As String
errTx = "以下のシートは処理出来ませんでした。" & Chr(13)
'条件、フイールド、各、配列作成
With Worksheets("全支店合計")
Set r = .Cells(2, 8).CurrentRegion.Offset(1, 1)
End With
ReDim cM1(0), cM2(0)
For Each var In r.Resize(r.Rows.Count - 1, 1)
If var <> "" Then
ReDim Preserve cM1(i), cM2(i)
cM1(i) = var.Value
cM2(i) = j + 2
i = i + 1
End If
j = j + 1
Next
'全、支店シート､フイルタ処理
For Each wS In Worksheets
If wS.Name Like "*支店*" And wS.Name <> "全支店合計" Then
With wS
If tChk(.Range("A3:J3"), .Range("A255")) Then
.Range("A3").AutoFilter
If .FilterMode = False Then
.Range("A3").AutoFilter
End If
If UBound(cM1) <> 0 Or cM1(0) <> Empty Then
For i = LBound(cM1) To UBound(cM1)
.Range("A3").AutoFilter cM2(i), cM1(i)
Next
End If
Else
eRrStr = eRrStr & wS.Name & ","
End If
End With
End If
Next
Erase cM1, cM2
If eRrStr = "" Then
MsgBox "全件処理終了"
Else
MsgBox errTx & Replace(eRrStr, ",", Chr(13))
End If
End Sub
Private Function tChk(ByVal r As Range, ByVal rr As Range) As Boolean
Dim i As Long
Dim n As Long
Dim 項目名() As Variant
Dim var As Variant
項目名 = Array("ナンバー", "取引年", "取引先名", "営業担当者", _
"所在地", "雑貨", "文具", "化粧品", "食品", "合計")
For Each var In r
If var.Value = 項目名(i) Then
n = n + 1
End If
i = i + 1
Next
If n = 10 And rr = "合計" Then tChk = True
End Function
（隠居じーさん） 2021/01/23(土) 12:56
＞フィィルターをかけられれば
↑オートフィルタのことを言っている
＞欲を言うと、255行目の合計列は指定条件に対象が無くても表示したままにしたいです。
↑つまり、オートフィルタの範囲は項目行も含めると、３〜【254】行目でよい
ということなら、↓みたいな感じでよくないですか？
Sub さんぷる()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
If SH.Name <> "全支店合計" Then
SH.AutoFilterMode = False
With SH.Range("A3:J254")
.AutoFilter Field:=2, Criteria1:=ThisWorkbook.Worksheets("全支店合計").Range("I3").Value
.AutoFilter Field:=3, Criteria1:=ThisWorkbook.Worksheets("全支店合計").Range("I4").Value
.AutoFilter Field:=4, Criteria1:=ThisWorkbook.Worksheets("全支店合計").Range("I5").Value
.AutoFilter Field:=5, Criteria1:=ThisWorkbook.Worksheets("全支店合計").Range("I6").Value
End With
End If
Next SH
End Sub
（もこな２） 2021/01/24(日) 08:40
もこな２ さん ありがとうございます。 私も考えてはいたのですが。← 多分253行目までは 使用しない。。。憶測の範囲をでません。。。＾＾； 後日、まる さんにお聞きしないとわかりませんが いづれにしましても254行目まで情報があれば 合計欄、隠れてしまいますね[一緒にフルター かかってしまいますね］＾＾；。 運用面で２５３行までとしていただくか 合計欄を下げるのが一番簡単かと。。m(_ _)m 合計欄を変動で処理後に設け、全支店合計も マクロで作ってしまえばＯＫかも。 （隠居じーさん） 2021/01/24(日) 09:25
こんにちは ＾＾ マクロで力技でも。。。＾＾；出来なくは無いでしょうが 最初からテーブルにして、集計行を２５５行目になるよう に範囲を指定しておけばいいみたいですね。←そうなって いるのかも。。。まる さんにお聞きしてみます。 (*^ ^*)v （隠居じーさん） 2021/01/24(日) 11:19
土日の間にお返事ができず申し訳ありません。
まったくのマクロ初心者なので、すべてをお答えできるか分からないのですが
・支店別シート→シート名は〇〇支店ではなく【北海道】【秋田】【東京】というような地名での登録になってしまっています…(また面倒になってしまい申し訳ありません。)
・支店シートの行はとりあえずで250行くらいまで用意しているだけで、実際使用しているのは多くても100行くらいです。
表に使われているデータ自体は別のブックから持ってきているので、いつデータが増えてもいいように…と用意している感じです。
頂いたレスを見た限りの解釈違いの点を挙げさせていただきました。
実現出来たら楽だな…と思っているだけなので、もし面倒でしたら無視して頂いて構いません(汗)
（まる） 2021/01/25(月) 09:24
おはようございます ＾＾ 早速のご説明恐縮です。２点教えて下さい １．支店名がシート名のシートと全支店合計という名前のシート以外に シートはありますでしょうか。もしあれば、シート数とシート名 [差し障りが御座いましたら仮名で、後でそちらで置き換え]で。 ２．全支店合計、各、支店のシートは普通の表でしょうか、テーブルになっているのでしょうか m(__)m （隠居じーさん） 2021/01/25(月) 09:46
1.合計シート、支店別シートの2種類以外にシートはありません。
(支店別シートは使用している行、列すべて同じです。)
2.シートは全て普通(罫線を引いただけ)の表になっております。
（まる） 2021/01/25(月) 09:52
あ、はい。。。それでしたら
'全、支店シート､フイルタ処理
For Each wS In Worksheets
If wS.Name Like "*支店*" And wS.Name <> "全支店合計" Then
の箇所の If wS.Name Like "*支店*" And wS.Name <> "全支店合計" Then を If wS.Name <> "全支店合計" Then に、変更後、実行してみてください。フイルターかけるだけですので 何も無いとは思いますが、バックアップは必ず取ってからお試しを。＾＾； "全支店合計" も、実物のお名前にしてくださいね。 m(_ _)m （隠居じーさん） 2021/01/25(月) 10:17
会議の為お返事遅くなってしまいました、申し訳ありません。
コードをはりつけてみたところ、やりたかったことができました！
合計列も隠れないですし、求めてたことが完璧にできて感動しています…
他の集計データにもとても役立つと思うので、頑張って読み解いていこうと思います！
色々とお手間をお掛けしてしまい大変申し訳ありませんでした
また、お付き合いいただきありがとうございました。
（まる） 2021/01/25(月) 13:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.