複数シートで○印をつけたセルの文字列を集約シート上から順に自動コピーしたいです

お知恵をお貸しください。

複数シート　「ａ」「ｂ」「ｃ」があります。
下のようにそれぞれに表があり、プルダウンで「○」を付けられるようにしています。


＜ａシート＞
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　D　　　　Ｅ
１　　注文　　　品目　　　数量　　　備考
２　　　　　　　もも　　　４
３　　○　　　　くり　　　１０　　　燻蒸済
４　　○　　　　うめ　　　８

＜ｂシート＞
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　D　　　　Ｅ
１　　注文　　　品目　　　数量　　　備考
２　　○　　　　あじ　　　１　　　　至急
３　　　　　　　さば　　　１２　　　
４　　　　　　　たこ　　　９

それを「集計」シートに集計したいと思っています。
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　D　　　　Ｅ
１　　注文　　　品目　　　数量　　　備考
２　　○　　　　くり　　　１０　　　燻蒸済
３　　○　　　　うめ　　　８
４　　○　　　　あじ　　　１　　　　至急

　○がつく数は日によって違います。
　一応自前で考えたのは

　ａ～ｃシートのＥ列を作業列とし、
　ａシートは
　　E2=if(A2="○",ROW(),"")
　ｂシートは
　　E2=if(A2="○",100+ROW(),"")
　ｃシートは
　　E2=if(A2="○",200+ROW(),"")

　として、数字を昇順に入れました。

　そのうえで、集計シートに
　　a2=IFERROR(INDEX(a!A:A,SMALL(a!$A:$A,ROW(a1))),"")　と入力し、それぞれのセルにコピーしました。
　　ここで行き詰りました。
　　このままでは、ａシートしか参照しません。
　　ｂ～ｃシートも参照するにはどうすればよいでしょうか。

　　違う方法があればそれでも構いません。
　　
　　どうぞよろしくお願いします。

No.3 ベストアンサー 回答者： ママチャリ 回答日時： 2016/10/29 07:53

koron02さんが考えた数式を見るとａ､ｂ､ｃの各シートの最大行数を100行程度と想定しているように見えます。

もし、それで良いのであれば、各シートに作業列を作るのではなく、いっそのこと、作業シートを作ってしまった方が楽ではないでしょうか。
具体的には、作業シートの1~100行にａシートからリンク貼り付け、101~200行にｂシートからリンク貼り付けといった具合です。集計シートから作業シートを参照すれば、koron02さんが考えた数式の応用で対応可能だと思います。

この回答へのお礼

お礼が遅くなり大変申し訳ありませんでした。
おっしゃるとおり、１００行以下ですので、作業シートを作ってやってみたところ、空白行が発生せず、うまくいきました。
関数の式も比較的シンプルなりました。
感謝いたします。

お礼日時：2016/11/22 12:49

No.2 回答者： tatsu99 回答日時： 2016/10/27 07:09

vbaでやってみました。

以下のマクロを標準モジュールへ登録し、実行してください。
---------------------------------------------
Option Explicit
Dim sh1 As Worksheet '集計シート
Dim rowsh1 As Long '集計シートの行
'各シートを集計シートにまとめる
Public Sub Macro1()
Dim sheets As Variant
Dim i As Long
Dim rowMax As Long
Dim row As Long
sheets = Array("a", "b", "c")
Set sh1 = Worksheets("集計")
rowMax = sh1.Cells(Rows.Count, 2).End(xlUp).row '最終行番号取得
rowsh1 = 2
'各シート分繰り返し
For i = 0 To UBound(sheets)
'1シートの集計
Shukei (sheets(i))
Next
'以前の行を削除
For row = rowsh1 To rowMax
sh1.Range(Cells(row, 1), Cells(row, 4)).Value = ""
Next
MsgBox ("コピー完了")
End Sub

'1シート単位の処理
Public Sub Shukei(ByVal sheet As String)
Dim row As Long
Dim rowMax As Long
Dim shw As Worksheet
Set shw = Worksheets(sheet)
rowMax = shw.Cells(Rows.Count, 2).End(xlUp).row '最終行番号取得
'2行から最終行まで繰り返し
For row = 2 To rowMax
If shw.Cells(row, 1).Value = "○" Then
'A列が○なら、A～D列を集計シートへコピー
sh1.Cells(rowsh1, 1).Value = shw.Cells(row, 1).Value
sh1.Cells(rowsh1, 2).Value = shw.Cells(row, 2).Value
sh1.Cells(rowsh1, 3).Value = shw.Cells(row, 3).Value
sh1.Cells(rowsh1, 4).Value = shw.Cells(row, 4).Value
rowsh1 = rowsh1 + 1
End If
Next
End Sub
-------------------------------------------------------
Macro1を実行してください。
シート:a,b,cから注文が○のものをシート：集計にまとめます。
シート名は必ず"集計","a","b","c"としてください。そのシートがないとエラーになります。

この回答へのお礼

お礼が遅くなり大変申し訳ありませんでした。
マクロは使う相手によって無効としていたり、使わないようにしているので難しいです。
しかし、たしかにこちらの方がスマートですね。
ありがとうございました。

お礼日時：2016/11/22 12:49

No.1 回答者： tom04 回答日時： 2016/10/26 23:16

こんばんは！

VBAの方が簡単にできそうですが、せっかく関数での方法をお考えのようですので・・・

まず、各シートの作業列（E列）の数式はすべて
=If(A2="○",ROW(),"")
のようにしておきます。
シート毎に100をプラスしてしまうとMATCH関数を使用しなくてはならなくなり、
数式がやたらと長くなってしまうため。

そうした上での一例です。
「集約」SheetのA2セルに
=IFERROR(IF(COUNT(a!$E:$E)>=ROW(A1),INDEX(a!A:A,SMALL(a!$E:$E,ROW(A1))),IF(COUNT(a!$E:$E,b!$E:$E)>=ROW(A1),INDEX(b!A:A,SMALL(b!$E:$E,ROW(A1)-COUNT(a!$E:$E))),INDEX('c'!A:A,SMALL('c'!$E:$E,ROW(A1)-COUNT(a!$E:$E,b!$E:$E))))),"")

という数式を入れフィルハンドルで列・行方向にコピーしてみてください。

※　空白セルが返る場合、「0」が表示されてしまいます。
数式で空白セルを表示しない方法もありますが、この処理まで数式でやってしまうと
もっと長い数式になってしまいますので
Excelのオプションから「ゼロ値のセルにゼロを表示する」のチェックを外す方法が良いと思います。

※　数量で「0」が存在するときは、その「0」も表示されませんので
やはりダラダラと数式で処理するしかないと思います。

以上のコトを考慮すると
やはりVBAが手っ取り早そうです。m(_ _)m

この回答へのお礼

お礼が遅くなり大変申し訳ありませんでした。
ヒントをいただき、ありがとうございます。

お礼日時：2016/11/22 12:49