[[20170211160625]]　『複数条件に当てはまったら別シートのセル内容を返』（まよぴよ）　ページの最後に飛ぶ

『複数条件に当てはまったら別シートのセル内容を返したい』（まよぴよ）

シート１で複数の条件を投げて、当てはまればシート２のセル内容を返してシート１のセルに入れたいんですが、出来ますでしょうか？
それに加えて、条件に当てはまらなかったら空白で返したいです
宜しくお願いします

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 こんにちわ。

 何を言ってるのか分からないです？

 >シート１で複数の条件を投げて
 複数の条件を指定するの間違い？

 >当てはまればシート２のセル内容を返してシート１のセルに入れたい
 指定した複数の条件に一致したデータを全て表示すると言う事ですか？

 >条件に当てはまらなかったら空白で返したいです
 これの意味が分かりませんが？
 上記のような事ならフィルターオプションがそのままです。
 調べてみて下さい。

 フィルターオプションがやりたい事と違うなら、シート1とシート2のレイアウトを提示して下さい。

（sy） 2017/02/11(土) 16:32

フィルターオプションでは出来ませんでした

シート１が統計表になってまして
会社名　　色　　大きさ　　個数
　　A R S 1

    A      R       M        1
    A      B       S        2
    B      R       S        1
    B      Y       M        1

シート２が集計まとめ（これに自動で再集計したいんです）
　　　　　　色R（固定です）
　　　　　　S M L
A（会社名） 1 1
B（会社名） 1

シート３
　　　　　　色B（固定です）
　　　　　　S M L
A（会社名） 2
B（会社名）

シート４
　　　　　　色Y（固定です）

               S M L
A（会社名）
B（会社名）      1

伝わりますでしょうか？
宜しくお願いします
（まよぴよ） 2017/02/11(土) 16:54

 あぁ、SUMIFSで条件に合ったサイズの個数を集計したいと言う事ですか。

 色とサイズは固定と思いますが、会社名は固定ですか？
 固定なら簡単で、
 以下のレイアウトとして

 シート1
       A     B     C      D  
 1  会社名  色  大きさ  個数   
 2    A     R     S       1  
 3    A     R     M       1  
 4    A     B     S       2  
 5    B     R     S       1  
 6    B     Y     M       1

 シート2
    A  B  C  D  
 1     R         
 2     S  M  L   
 3  A  1  1  0  
 4  B  1  0  0

 B3 =SUMIFS(シート1!$D:$D,シート1!$A:$A,$A3,シート1!$B:$B,$B$1,シート1!$C:$C,B$2)
 右と下にフィルコピー

 0を表示させたくないなら、セルの書式をユーザー定義で 0;; として下さい。

 結果が文字になってしまっても良いなら以下のようにしても良いですけど。
 =TEXT(SUMIFS(シート1!$D:$D,シート1!$A:$A,$A3,シート1!$B:$B,$B$1,シート1!$C:$C,B$2),"0;;")

（sy） 2017/02/11(土) 17:13

ご返答大変感謝しております。

シート2以降の会社名は固定ですが、統計（シート1）の会社名は固定ではありません

会社名Aから始まることもあれば
会社名Eから始まることもあります

どうでしょうか？
（まよぴよ） 2017/02/11(土) 17:44

追記です
B3 =SUMIFS(シート1!$D:$D,シート1!$A:$A,$A3,シート1!$B:$B,$B$1,シート1!$C:$C,B$2)

これが何をしているか詳しく教えて頂いても大丈夫ですか？
（まよぴよ） 2017/02/11(土) 17:48

 マクロでやるなら、下記サイトの『2つの条件で合計する』のとこが参考になるよ　
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_dictionary.html
（マリオ） 2017/02/11(土) 17:53

 >>シート2以降の会社名は固定ですが、統計（シート1）の会社名は固定ではありません 
 >>どうでしょうか？

 どうなるか、やってみましたか？
 やってみて、もし、結果がおかしかったら質問しましょう。

 >>これが何をしているか詳しく教えて頂いても大丈夫ですか？

 まよぴよさんって　ヘルプを読まないんですか？
 この式が入ったセルを選択して、上のほうの数式バーの左の fx をクリックし、
 出てくる小窓の　この関数のヘルプ　をクリックしてみてください。

（ β） 2017/02/11(土) 17:59

 言いたい事を全てβさんが言ってくれたので、特にありません。

 まず試して、意味もヘルプを見てから、それでも分からなかったら再質問して下さい。

（sy） 2017/02/11(土) 18:08

βさん　syさんすいませんでした

自分のレイアウトに適してやってみたところ
＃＃＃　と表示されて出来ませんでした

それと入力してて思ったんですが、シート１の表で個数が２個となってる場合も
１　と返されてしまうのでしょうか？

宜しくお願いします。
（まよぴよ） 2017/02/11(土) 18:46

 たとえば　セルに 123456 といれたとします。
 このセル幅を、ずずずっと狭くするとどうなりますか？

 ### といった表示に変わりますね。　セル内にある数値を表示するだけのセル幅がないということ、
 逆にいえば、そのセル幅を広げてみればどうなりますか？

 ２つ目の質問に対しては、あえてコメントしません。

（β） 2017/02/11(土) 18:51

 >まよぴよ さん
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_dictionary.html
 を参考に、3つの条件で合計するマクロを作ってみました。

 Sheet1のA列～D列が、下記のようであったとき、
 下に記述しているtestプロシージャをModuleにコピペして
プログラムを実行してみてください。
     |[A]       |[B]|[C]   |[D] 
 [1] |会社名    |色 |大きさ|個数
 [2] |甲株式会社|R  |S     |   1
 [3] |乙株式会社|Y  |M     |   1
 [4] |甲株式会社|R  |M     |   1
 [5] |甲株式会社|R  |M     |   1
 [6] |甲株式会社|B  |S     |   2
 [7] |乙株式会社|R  |S     |   1
 [8] |乙株式会社|Y  |M     |   1
 [9] |乙株式会社|Y  |L     |   2
 [10]|丙株式会社|B  |2L    |  10
 *********************************************************
 Sub test()
    Dim sh As Worksheet, myDic As Object, i As Long
    Dim myKey, myItem, myVal, myVal2, myVal3
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Columns("F:I").ClearContents 'F～I列を消去（値のみ）
    sh.Range("F1:I1").Value = Range("A1:D1").Value 'タイトルを転写
    Set myDic = CreateObject("Scripting.Dictionary")
   ' ---元データを配列に格納
    myVal = sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
   ' ---myDicへデータを格納
         For i = 1 To UBound(myVal, 1)
            myVal2 = myVal(i, 1) & "★" & myVal(i, 2) & "★" & myVal(i, 3)
            If Not myVal2 = "★★" Then
                If Not myDic.exists(myVal2) Then
                    myDic.Add myVal2, myVal(i, 4)
                Else
                    myDic(myVal2) = myDic(myVal2) + myVal(i, 4)
                End If
            End If
        Next
   ' ---Key,Itemの書き出し
    myKey = myDic.keys
    myItem = myDic.items
        For i = 0 To UBound(myKey)
            myVal3 = Split(myKey(i), "★")
            sh.Cells(i + 2, "F").Value = myVal3(0)
            sh.Cells(i + 2, "G").Value = myVal3(1)
            sh.Cells(i + 2, "H").Value = myVal3(2)
            sh.Cells(i + 2, "I").Value = myItem(i)
        Next
   ' ---並べ替え
    sh.Range("F1", sh.Range("I" & Rows.Count).End(xlUp)).Sort _
        Key1:=sh.Range("F2"), Order1:=xlAscending, _
        Key2:=sh.Range("G2"), Order2:=xlAscending, _
        Key3:=sh.Range("H2"), Order2:=xlAscending, _
        Header:=xlGuess
    ' ---セル幅自動調整
    sh.Columns("F:I").Columns.AutoFit
    ' ---後処理
     Set myDic = Nothing
     Set sh = Nothing
 End Sub
 *********************************************************
 プログラム実行後のSheet1のF～I列は、下記のようになります
    |[F]       |[G]|[H]   |[I] 
 [1]|会社名    |色 |大きさ|個数
 [2]|乙株式会社|R  |S     |   1
 [3]|乙株式会社|Y  |L     |   2
 [4]|乙株式会社|Y  |M     |   2
 [5]|甲株式会社|B  |S     |   2
 [6]|甲株式会社|R  |M     |   2
 [7]|甲株式会社|R  |S     |   1
 [8]|丙株式会社|B  |2L    |  10
（マリオ） 2017/02/11(土) 18:52

βさん
伸ばしたら、＃NAME?と出ました
調べてみたところ関数名が間違ってると書かれてましたが、間違えてないはずです。

どうすれば宜しいでしょうか？

マリオさん
ありがとうございます
時間がある時見返して勉強させて頂きます

（まよぴよ） 2017/02/11(土) 19:06

 ＃NAME?　が出るなら100%、式のスペルが間違ってます。
 ＞間違えてないはずです。
 思い込みは問題解決の邪魔にしかなりませんよ。

 2013なら、セルに　=S　と打ったら候補が出てくる筈ですが、それで選べば間違える事はありません。

（sy） 2017/02/11(土) 19:19

 まさかEXCELのバージョンが2013では無くて2003だったとかでは無いでしょうね？

 2003ならSUMIFSは無いので、＃NAME?が表示されます。

（sy） 2017/02/11(土) 19:24

 ＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞ ということですから エクセルは 2013 だと思いますけど
 もしかしたら、会社の環境はそうでも、式を当てはめて検証した自宅の環境は 2003 だったのかもしれませんね。

 新規ブックのシートの、たとえば A1 に　=SUMIFS(E1:E10,F1:F10,"A",G1:G10,"B")
 こんな式を入れるとどうなりますかね？
 （式は手打ちせず、この式をマウスで選択して Ctrl/c、A1 を選択して　値貼り付けで）

（ β） 2017/02/12(日) 10:33

コメント返信：

[ 一覧(最新更新順) ]

[[20170211160625]] 『複数条件に当てはまったら別シートのセル内容を返』（まよぴよ） ページの最後に飛ぶ

[[20170211160625]]　『複数条件に当てはまったら別シートのセル内容を返』（まよぴよ）　ページの最後に飛ぶ