戻る  □VBAのサロン  □ 使用方法  □ 新着記事  □ 新規に質問する!  □ トピック一覧  □ 検索  □ 過去ログ
[ 親トピックをトピックトップへ ]
このトピックに書きこむ
Name/
E-Mail/

└>このツリーのレス記事をメールで受信しますか? YES/ NO/
Title/
URL/
Comment/ 通常モード->  図表モード-> (←の場合適当に改行を入れて下さい)
タグが使用できます。例 ⇒ <font color="blue">文字</font>
解決!!ありがとうございました! 解決       保留中です・・・ 保留        迷宮入りorほかあたって見ます・・ 迷宮入       すいませ〜ん。誰か〜! Help! ←※回答者専用
解決したらチェック       保留(コードテスト中など・・)         解決不可orレスが全く付かなくてほかのサイトに行くときなど・・
削除キー/
(半角8文字以内) 

このトピック参照回数 :
Re[22]: フィルターをかけたあとの合計

    [133479] Re[22]: フィルターをかけたあとの合計-

    解決!!ありがとうございました! / 記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/17(14:14))
    □U R L/


      皆様ありがとうございました。
      自分なりに必死にやっておりましたが、
      どうしても出来ずお力を貸していただいたおかげで完成しました。

      本当にありがとうございました!




    [133478] Re[21]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ んなっと -(2022/12/15(12:27))
    □U R L/
      作業列を追加していいなら、D1に「可視」と入力して
      
      D2
      =IF(SUBTOTAL(3,[@大分類]),ROW()-ROW(テーブル1[#見出し]),0)
      
                    ↓追加
           A    B   C   D 
       1 大分類 小分類 個数 可視 
       2   A りんご   1   1 
       3   B りんご   2   2 
       4   B ばなな   3   3 
       5   A ばなな   4   4 
       6   A みかん   5   5 
       7   A りんご   6   6 
       8   B ばなな   7   7 
      
       ・・・・・・・・・・・
      
      20     小分類 個数    
      21     りんご   9    
      22     ばなな  14    
      23     みかん   5    
      
      B21
      =IFERROR(INDEX(テーブル1[小分類],AGGREGATE(15,6,1*TEXT(MATCH(テーブル1[小分類],テーブル1[小分類]&T(0/テーブル1[可視]),0),"[="&テーブル1[可視]&"]0;"),ROW(B1))),"")
      下方向・↓
      C21
      =IF(B21="","",SUMIFS(テーブル1[個数],テーブル1[小分類],B21,テーブル1[可視],">0"))
      下方向・↓




    [133477] Re[20]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ んなっと -(2022/12/15(11:58))
    □U R L/
      こんな方法も。
      
           A    B   C 
       1 大分類 小分類 個数 
       2   A りんご   1 
       3   B りんご   2 
       4   B ばなな   3 
       5   A ばなな   4 
       6   A みかん   5 
       7   A りんご   6 
       8   B ばなな   7 
      
        ・・・・・・・
      
      20     小分類 個数 
      21     りんご   9 
      22     ばなな  14 
      23     みかん   5 
      
      B21
      =IFERROR(INDEX(テーブル1[小分類],AGGREGATE(15,6,1*TEXT(MATCH(テーブル1[小分類],テーブル1[小分類]&T(0/SUBTOTAL(3,OFFSET(テーブル1[小分類],ROW(テーブル1)-ROW(テーブル1[#見出し])-1,,1))),0),"[="&ROW(テーブル1)-ROW(テーブル1[#見出し])&"]0;;;"),ROW(B1))),"")
      下方向・↓
      C21
      =IF(B21="","",SUMPRODUCT(テーブル1[個数]*(テーブル1[小分類]=B21)*SUBTOTAL(3,OFFSET(テーブル1[個数],ROW(テーブル1)-ROW(テーブル1[#見出し])-1,,1))))
      下方向・↓
      
      
      
      
      →大分類でフィルター
      
           A    B   C 
       1 大分類▼ 小分類 個数 
       2   A りんご   1 
       5   A ばなな   4 
       6   A みかん   5 
       7   A りんご   6 
      
        ・・・・・・・
      
      20     小分類 個数 
      21     りんご   7 
      22     ばなな   4 
      23     みかん   5 




    [133471] Re[19]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(23:06))
    □U R L/
      目的のブックのコピーを作ってテストしてください。
      
      目的ブックの目的シートの「シート見出し」を右クリックする。
      「コードの表示(V)」を選ぶと画面中央に白いエリアが表れます。(VBE画面です)
      その白いエリアに下記マクロをコピぺして、Alt+F11でエクセルに戻って下さい
      
      ※前回と違って、F5を押さないし、VBAコードも消さないように。
      
      集計が必要になったら、B5セル(規格と書いてあるセル)を右クリックするだけです。
      
      なお、フィルターが掛かっていても、いなくても、右クリックした時点において、
      目に見えるデータ行を全て集計するように作ってあります。(その方が便利なハズ)
      
      ’コピペするVBA
      
      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
          If Target(1, 1).Address(0, 0) = "B5" Then
              Cancel = True
              Call Extract
          End If
      End Sub
      
      Private Sub Extract()
          Dim rNew As Range, aCell As Range, nextTopCell As Range
          Dim dicT As Object, ky, OutItem, firstCounter, i As Long
          Dim itmAry(1 To 3), temp
          
          Set dicT = CreateObject("Scripting.Dictionary")
          
          Application.ScreenUpdating = False
          
          With Me.ListObjects("テーブル1")
              
              With .DataBodyRange.Columns(2)
                  Set nextTopCell = .Cells(.Cells.Count).Offset(5)
                  nextTopCell.Resize(10000, 3).Clear
              End With
              
              With .Range.Columns("B:D").Resize(, 3).SpecialCells(xlCellTypeVisible)
                  .Copy nextTopCell
                  
                  Set rNew = Range(nextTopCell, Cells(Rows.Count, "B").End(xlUp))
              End With
          End With
          
          Me.Sort.SortFields.Clear
          Me.Sort.SortFields.Add Key:=nextTopCell, SortOn:=xlSortOnValues, Order:=xlAscending
          
          With Me.Sort
              .SetRange rNew.Resize(, 3)
              .Header = xlYes
              .Apply
          End With
          
          For Each aCell In rNew
              If aCell.Value <> "" And aCell.Value <> "規格" Then
                  ky = aCell.Value & "♪" & aCell(1, 2).Value
                  If dicT.exists(ky) Then
                      temp = dicT(ky)
                      
                      temp(3) = temp(3) + aCell(1, 3).Value
                      dicT(ky) = temp
                  Else
                      itmAry(1) = aCell(1, 1).Value
                      itmAry(2) = aCell(1, 2).Value
                      itmAry(3) = aCell(1, 3).Value
                      dicT(ky) = itmAry
                  End If
                  
              End If
          Next
          
          OutItem = dicT.Items
          firstCounter = OutItem(0)(1)
          
          For i = 1 To dicT.Count - 1
              If OutItem(i)(1) = firstCounter Then
                  OutItem(i)(1) = Empty
              Else
                  firstCounter = OutItem(i)(1)
              End If
          Next
          
          rNew.Offset(1).Resize(, 3).Clear
          rNew.Offset(1).Resize(dicT.Count, 3) = Application.Transpose(Application.Transpose(OutItem))
          Application.Goto nextTopCell
          
          Application.ScreenUpdating = True
      End Sub


      この記事にはVBAのコードが含まれています。

      緑の太文字→注釈
      茶色の太文字→条件分岐
      赤の太文字→ループ
      青の太文字→その他




    [133469] Re[18]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(17:10))
    □U R L/
      >VBAはさっきの先ほどのコードを入れてやることですよね。
      
      まだ、具体的に考えていません。
      
      いずれにしても、さっきと違ってVBAは消さない方式になるので、
      ブックの拡張子は.xlsmになります。
      
      それが、会社の方針で問題ないことが必須条件です。
      問題なければ、今日中にはアップする予定です。(明日確認してください)
      
      もしその前に、何方かから数式案が提示されれば、それでやってください。
      




    [133468] Re[17]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(16:55))
    □U R L/


      何度もありがとうございました。
      確かに0と表示されています。なぜなのでしょうか・・・。

      自分で考えていた以上に難しいことだったのだと思っています。

      VBAはさっきの先ほどのコードを入れてやることですよね。
      今後も同じ資料から作成することが続くと思いますので、
      数式でなくても全く問題はありません。

      先ほどまでお伝えした内容で作成は可能なのでしょうか?
      お手数おかけして申し訳ありません。





    [133467] Re[16]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(16:49))
    □U R L/
       エラーじゃなく、0ですか。
       ちょっと分からないですねぇ・・こちらでは正常に出ています。
       
       この問題は、数式でやるのは面倒なところがありまして、これ以上はちょっと対応しにくいです。
       
       VBAじゃダメですか? それなら対応します。
       
       あくまでも数式案と言う事であれば、他の回答者のレスを待ってみてください。
       
       あと、普通にピボットテーブルで集計して、必要な数字だけ眼でチェックする、と言う選択肢も
       あるかも知れないなぁと思っていますけど。




    [133466] Re[15]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(15:51))
    □U R L/


      ありがとうございます。
      今いただいた数式を入れてみました。

      B291のセルは正しく表示されたのですが、C291とD291は0のままで
      何も表示されませんでした。

      何度もお手数おかけして申し訳ありません。




    [133465] Re[14]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(15:27))
    □U R L/
      (1) A291セル =INDEX(テーブル1[名前],MATCH(B291,テーブル1[規格],0))
      (2) B291セル =IF(SUBTOTAL(3,テーブル1[規格])=COUNTA(テーブル1[規格]),"フィルタで絞ってください",LOOKUP(1,0/SUBTOTAL(3,INDIRECT("B"&ROW(テーブル1[規格]))),テーブル1[規格]))
      
      (3) C291セル =IFERROR(INDEX(テーブル1[サイズ],AGGREGATE(15,6,(ROW(テーブル1[サイズ])-5)/(MATCH(B$291&テーブル1[サイズ],テーブル1[規格]&テーブル1[サイズ],0)=ROW(テーブル1[サイズ])-5),ROWS(B$290:B290))),"")
      (4) D291セル =IF(C291="","",SUMIFS(テーブル1[個数],テーブル1[規格],B$291,テーブル1[サイズ],C291))
      
      上記(3)、(4)の数式を下方へコピー(多目に)
      




    [133464] Re[13]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(14:28))
    □U R L/

      > 現在のテーブルはどうなっているんですか?
      >
      > 1.テーブル名
      >
      > 2.テーブルタイトル行→1行目ですね?
      >
      > 3.テーブルの最下行の行番号
      >
      > 4.テーブルの列数(サンプルと同じ3列なんですか?)
      >   タイトル名はサンプル通りですか?(大分類、小分類、個数)
      >
      1.テーブル1

      2. 5行目

      3. 283行
       
      4.名前 規格(大分類) サイズ(小分類) 個数 単位の5項目


      となっています。




    [133463] Re[12]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(14:09))
    □U R L/
      現在のテーブルはどうなっているんですか?
      
      1.テーブル名
      
      2.テーブルタイトル行→1行目ですね?
      
      3.テーブルの最下行の行番号
      
      4.テーブルの列数(サンプルと同じ3列なんですか?)
        タイトル名はサンプル通りですか?(大分類、小分類、個数)
      




    [133462] Re[11]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(13:58))
    □U R L/


      上記試してみました。
      まさにこれがやりたかったことです。
      ありがとうございます。

      自分に当てはめてやろうとしたいのですが、
      ばなな・りんご・みかんの小分類は300項目ほどありました。
      そこをテーブルの項目の部分にしたらよいのでしょうか?




    [133461] Re[10]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(13:25))
    □U R L/
      >今どうしてもできないので、↑と同じものを別のシートに作ってみましたが、
      新規ブックを作成して、Sheet1のシート見出しを右クリックする。
      
      「コードの表示(V)」を選ぶと画面中央に白いエリアが表れます。(VBE画面です)
      
      その白いエリアに下記マクロをコピぺし、F5キーを押下してください
      
      ※以上で全て自動で出来上がりますので、
       「Ctrl+Z」でコードを消去し、Alt+F11でエクセルに戻って下さい
      
      Private Sub onlyOnce()
      
          With Sheets(1)
           Rem 生データのセルをまとめて処理
          .Range("A1,A12").Value = "大分類"
          .Range("B1,B12").Value = "小分類"
          .Range("C1,C12").Value = "個数"
          .Range("A2,A5:A7").Value = "A"
          .Range("B2:B3,B7").Value = "りんご"
          .Range("C2").Value = 1
          .Range("A3:A4,A8").Value = "B"
          .Range("C3").Value = 1000
          .Range("B4:B5,B8").Value = "ばなな"
          .Range("C4").Value = 2000
          .Range("C5").Value = 2
          .Range("B6").Value = "みかん"
          .Range("C6").Value = 5
          .Range("C7").Value = 3
          .Range("C8").Value = 16
      
           Worksheets(1).ListObjects.Add(xlSrcRange, Range("$A$1:$C$8"), , xlYes).Name = "テーブル1"
           Rem 数式セルをまとめて処理
           .Range("A13").FormulaR1C1Local = "=IF(SUBTOTAL(3,テーブル1[大分類])=COUNTA(テーブル1[大分類]),""フィルタで絞ってください"",LOOKUP(1,0/SUBTOTAL(3,INDIRECT(""Sheet1!A""&ROW(テーブル1[大分類]))),テーブル1[大分類]))"
           .Range("B13:B16").FormulaR1C1Local = "=IFERROR(INDEX(テーブル1[小分類],AGGREGATE(15,6,(ROW(テーブル1[大分類])-1)/(MATCH(R13C[-1]&テーブル1[小分類],テーブル1[大分類]&テーブル1[小分類],0)=ROW(テーブル1[大分類])-1),ROWS(R12C[-1]:R[-1]C[-1]))),"""")"
           .Range("C13:C16").FormulaR1C1Local = "=IF(RC[-1]="""","""",SUMIFS(テーブル1[個数],テーブル1[大分類],R13C[-2],テーブル1[小分類],RC[-1]))"
          End With
      End Sub
      
      シートが出来たら、フィルターで「A」か「B」に絞ってください。
      絞らない内は、A13セルに「フィルタで絞ってください」と言う表示が出ます。


      この記事にはVBAのコードが含まれています。

      緑の太文字→注釈
      茶色の太文字→条件分岐
      赤の太文字→ループ
      青の太文字→その他




    [133460] Re[9]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(13:07))
    □U R L/


      ありがとうございます。
      まずテーブル名は変更しました。

      その後、Aの大分類の一番最終行より少し下に(1)の数式を入れました。
      [大分類]1[小分類]はそれぞれの名称に変更しました。
      (1)の"フィルタで絞ってください"←こちらは何をすれば良いのでしょうか?

      今どうしてもできないので、↑と同じものを別のシートに作ってみましたが、
      私の作り方が悪いのかどうしても何もできません。
      結果が#N/Aとなってしまいます。
      何度も申し訳ありません。




    [133459] Re[8]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(12:33))
    □U R L/
      テーブル名を「テーブル1」とします。
      
      (1) A13セル =IF(SUBTOTAL(3,テーブル1[大分類])=COUNTA(テーブル1[大分類]),"フィルタで絞ってください",LOOKUP(1,0/SUBTOTAL(3,INDIRECT("A"&ROW(テーブル1[大分類]))),テーブル1[大分類]))
      (2) B13セル =IFERROR(INDEX(テーブル1[小分類],AGGREGATE(15,6,(ROW(テーブル1[大分類])-1)/(MATCH(A$13&テーブル1[小分類],テーブル1[大分類]&テーブル1[小分類],0)=ROW(テーブル1[大分類])-1),ROWS(A$12:A12))),"")
      (3) C13セル =IF(B13="","",SUMIFS(テーブル1[個数],テーブル1[大分類],A$13,テーブル1[小分類],B13))
      
      上記(2)、(3)の数式を下にコピー
      
      <結果図>
      行  ___A___  ___B___  __C__
       1  大分類   小分類   個数 
       2  A       りんご       1
       5  A       ばなな       2
       6  A       みかん       5
       7  A       りんご       3
       9                         
      10                         
      11                         
      12  大分類   小分類   個数 
      13  A       りんご       4
      14           ばなな       2
      15           みかん       5
      16                         
      




    [133458] Re[7]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(11:48))
    □U R L/

      > >大分類を選ぶ度に個数が変わる為、どのように合計欄を用意したら良いかもわかりません。
      >
      > 仮りにですが、いつも3種類と決まっていたら、何処に合計欄を用意するお積りですか?
      >

      小分類の種類毎の数値の合計は
      可能であれば、同じシートのテーブル外が理想かと思っています。







    [133457] Re[6]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(11:34))
    □U R L/
      >大分類を選ぶ度に個数が変わる為、どのように合計欄を用意したら良いかもわかりません。
      
      仮りにですが、いつも3種類と決まっていたら、何処に合計欄を用意するお積りですか?
      




    [133456] Re[5]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(09:39))
    □U R L/

      ズレてしまっていたので再送します。

      ___A___ ___B___ _C_ _D_ _
      1 大分類 小分類
      2 A りんご 1
      5 A ばなな 2
      6 A みかん 5
      7 A りんご 3

      ↑の場合の結果は
      行 ___A___ ___B___ _C_ _D_ _
      1 大分類 小分類
      2 A りんご 1
      5 A ばなな 2
      6 A みかん 5
      7 A りんご 3

      大分類 A  りんご 4
            ばなな 2
            みかん 5

      ↑の場合はこんな感じになるのが理想です。

      小分類の種類の個数がC欄にあり、別の欄にそれぞれの合計が出したいです。
      (テーブルの枠には今合計欄はありません。)

      大分類1つに対して小分類の数が1種類〜数十種類あるため、
      大分類を選ぶ度に個数が変わる為、どのように合計欄を用意したら良いかもわかりません。

      よろしくお願いいたします。




    [133455] Re[4]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(09:20))
    □U R L/


      ありがとうございます。
      説明不足で申し訳ございません。

      行 ___A___ ___B___ _C_ _D_ _
      1 大分類 小分類
      2 A りんご 1
      5 A ばなな 2
      6 A みかん 5
      7 A りんご 3

      小分類の種類の個数がC欄にあり、別の欄にそれぞれの合計が出したいです。
      テーブルの枠には今合計欄はありません。

      大分類1つに対して小分類の数が1種類〜数十種類あるため、
      大分類を選ぶ度に個数が変わる為、どのように合計欄を用意したら良いかもわかりません。

      よろしくお願いいたします。




    [133454] Re[3]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(09:13))
    □U R L/
       >りんご・ばなな・みかんそれぞれの合計
       
       「それぞれの合計」とは具体的にどこにどんな数値を出すんですか?
       
       行  ___A___  ___B___  _C_  _D_  _
        1  大分類   小分類              
        2  A       りんご              
        5  A       ばなな              
        6  A       みかん              
        7  A       りんご              
       
       




    [133453] Re[2]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(09:00))
    □U R L/


      ありがとうございます。
      EXCELは2013
      テーブルを使って一番上の項目がフィルターになっています。


      A りんご
      B りんご
      B ばなな
      A ばなな
      A みかん
      A りんご
      B ばなな

      ↓大分類でAを選ぶと
      A りんご
      A ばなな
      A みかん
      A りんご

      になるのですが、この時にりんご・ばなな・みかんそれぞれの合計が出したいと思っています。
      拙い説明で申し訳ございません。
      よろしくお願いいたします。





    [133452] Re[1]: フィルターをかけたあとの合計-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2022/12/14(08:48))
    □U R L/
      「フィルター」とは、とんなフィルターですか?(オートフィルター?)
      
      「フィルター後の結果」と言うものを提示いただけませんか?
      ※大分類2種類、小分類3種類程度のサンプルで結構です。
       「それぞれの合計」とやらも一緒に記入してください。
      
      エクセルのバージョンは何ですか?
      ※複数ある場合は、一番古いバージョンを申告してください。
      




    [133450] フィルターをかけたあとの合計-

    ■親トピック/記事引用/メール受信=OFF■

    □投稿者/ akane -(2022/12/14(08:08))
    □U R L/


      はじめまして。

      フィルター後の結果をそれぞれ合計する方法を教えてください。

      大分類を選ぶと小分類が1種類〜数十種類出てきます。

      小分類全ての合計ではなく、それぞれの合計を出したいです。
      小分類の種類が数百種類のため、どのような式にしたら良いのか分かりません。

      どうぞよろしくお願いいたします。



このトピック内容の全ページ数 / [0]

- Child Forum -
Edit:ゆう-G