[[20210121132003]] 『複数シートに同条件でまとめてフィルターをかけた』(まる) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

 

『複数シートに同条件でまとめてフィルターをかけたい』(まる)

いつも大変お世話になっております。

【複数シートに同条件でまとめてフィルターをかけたい】

現在、支店別・売上項目別で合算を出し、横にある「条件指定リスト」で条件を選択すると合算シートの合計も条件に合わせて変わる…というデータを作成しています。

・全支店合計シート(1枚)
・支店別内訳シート(25枚)
支店別シートを基に合計シートを作成しています。

全支店合計シート

  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所在地     東京

というような条件リストを作成しています。
(特に指定がない場合は空白、空白なら全表示)

支店ごとに1枚づつシートがあるので、合計シートに反映させている条件と同じものでフィルターをかけられればと考えております。

支店シートは

   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 >


< 使用 Excel:2017、使用 OS:ウィンドウズ7 >

使用スペックを選択し忘れておりました、申し訳ありません!
(まる) 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行目の合計列は指定条件に対象が無くても表示したままにしたいです。
 ↑つまり、オートフィルタの範囲は項目行も含めると、3〜【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

(もこな2) 2021/01/24(日) 08:40


 もこな2 さん ありがとうございます。
私も考えてはいたのですが。← 多分253行目までは
使用しない。。。憶測の範囲をでません。。。^^;
後日、まる さんにお聞きしないとわかりませんが
いづれにしましても254行目まで情報があれば
合計欄、隠れてしまいますね[一緒にフルター
かかってしまいますね]^^;。
運用面で253行までとしていただくか
合計欄を下げるのが一番簡単かと。。m(_ _)m
合計欄を変動で処理後に設け、全支店合計も
マクロで作ってしまえばOKかも。
(隠居じーさん) 2021/01/24(日) 09:25

 こんにちは ^^
マクロで力技でも。。。^^;出来なくは無いでしょうが
最初からテーブルにして、集計行を255行目になるよう
に範囲を指定しておけばいいみたいですね。←そうなって
いるのかも。。。まる さんにお聞きしてみます。
(*^ ^*)v
(隠居じーさん) 2021/01/24(日) 11:19

隠居じーさん さん>

土日の間にお返事ができず申し訳ありません。
まったくのマクロ初心者なので、すべてをお答えできるか分からないのですが
・支店別シート→シート名は〇〇支店ではなく【北海道】【秋田】【東京】というような地名での登録になってしまっています…(また面倒になってしまい申し訳ありません。)

・支店シートの行はとりあえずで250行くらいまで用意しているだけで、実際使用しているのは多くても100行くらいです。
表に使われているデータ自体は別のブックから持ってきているので、いつデータが増えてもいいように…と用意している感じです。

頂いたレスを見た限りの解釈違いの点を挙げさせていただきました。
実現出来たら楽だな…と思っているだけなので、もし面倒でしたら無視して頂いて構いません(汗)
(まる) 2021/01/25(月) 09:24


 おはようございます ^^
早速のご説明恐縮です。2点教えて下さい
1.支店名がシート名のシートと全支店合計という名前のシート以外に
  シートはありますでしょうか。もしあれば、シート数とシート名
  [差し障りが御座いましたら仮名で、後でそちらで置き換え]で。
2.全支店合計、各、支店のシートは普通の表でしょうか、テーブルになっているのでしょうか
  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


恐縮で御座います。m(_ _)m
(隠居じーさん) 2021/01/25(月) 14:01

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.