[[20171228145612]]　『複数のシートから文字で検索して1つのシートに集磨x（やち）　ページの最後に飛ぶ

『複数のシートから文字で検索して1つのシートに集約する票を作りたいです』（やち）

初めて質問しますやちといいます。
過去の中で探したのですがこれだというのがないので質問さしていただきます
私は建設業をしており1つ1つの工事の原価をわかりやすくするために工事原価管理表というExcelデータを作っています。
その中に1月から12月の12個のシートと現場別のシート複数があります。
1月から12月のシートにはその月ごとの材料の仕入れ金額を記入しています。
その金額を記入すれば自動的に現場別の原価管理表に反映できる方法を教えていただきたいです。
例(1月のシート）　
　　A　　　　　B　　　　　C　　　　　D　　　　E
1　　　　　現場A　　　現場B　　　現場C　　　現場D
2仕入先A　　1000　　　500　　　　2000　　　500
3仕入先B　　1500　　　1000　　　　1500　　　200
4仕入先C　　500　　　　500　　　　1000　　　200

このシートの数値（例えば現場Aの仕入先A＝1000）をシート（現場A）に自動で反映したいと考えています。
例（現場Aシート）
　　A　　　　　B　　　　　C　　　　　D　　　　E
1　　　　　　 1月　　　 2月　　　　3月　　　　4月
2仕入先A　　1000
3仕入先B　　1500
4仕入先C　　500

（現場Bシート）
　　A　　　　　B　　　　　C　　　　　D　　　　E
1　　　　　　 1月　　　 2月　　　　3月　　　　4月
2仕入先A　　　500
3仕入先B　　　1000
4仕入先C　　　500

シート現場AのセルB1に1月と記入しセルA2に仕入先Aと記入すると自動的にシート（1月）の中から検索し現場A列の仕入れA行の値（1000）が反映されるようにしたいです。
説明がへたくそで申し訳ありません。
VLOOKUPやINDEXやIFで色々考えたのですが僕には答えが出ませんでした。
何卒よろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 多分全くスマートじゃないですが。
 現場AシートのB2にこの数式を入れて横や下にフィルドラッグしてみてください。
 　
=INDEX(INDIRECT("'"&B$1&"'!1:1000"),MATCH($A2,INDIRECT("'"&B$1&"'!A:A")),MATCH("現場A",INDIRECT("'"&B$1&"'!1:1")))

 これでうまくいくといいんですけど、ダメなときには有識者樣からの回答をお待ち下さいごめんなさい。
 うまくいったときには、「1:1000」や「"現場A"(シート名の指定です)」部分を
 各シートなどに合わせて変更してください。
 　
（わをん） 2017/12/28(木) 17:12

ちまちま説明文書いていたら、わをんさんが先にフォローされておられた・・・・
まぁ同じことですけど、がんばって書いたので投稿しちゃいます。

ご質問のことはちょっと難しいですが、いくつかの関数を組み合わせることで実現可能です。

まず、Mach関数やIndex関数で他シートを参照するには以下のようにすればいいですよね
例１：「１月」シートのA列の何行目に「仕入先A」があるのか調べたい場合
　　　=MATCH("仕入先A",'１月'!A:A,0)
例２：「1月」シートの1行目の何列目に「現場A」があるのか調べたい場合
　　　=MATCH("現場A",'１月'!1:1,0)

このうち、検索値についてセル参照が使えるのはご存じかとおもいますので、例１は、
　　　=MATCH(A2,'１月'!A:A,0)
でも動作しますよね

では例２はどうでしょうか。
「現場A」シートの中のセルに「現場A」って書いてあるところありますか？
これがあるのであれば、上記と同じことができますが、無い場合はどうしましょう？
こんな時に役立つのがCELL関数です。
説明が難しいので端折りますが、以下の数式でシート名が取り出せます。
※一度ブックを保存してから試してください。（ファイル名を使用するので保存したことがないブックではエラーになります）
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

これで、MATCH関数をつかって、取り出したい値がどの行、列にあるかがわかりますよね。

では、データを取り出すにはどうしたらいいでしょうか？
行、列がわかっているわけですから、INDEX関数の出番です。
例：「１月」シートの2行目2列目の値を取り出す（参照する）
=INDEX('１月'!A:E,2,2)
となります。

組み合わせると、「現場Ａ」シートの「Ｂ２」セルに
=INDEX('１月'!$A:$E,MATCH($A2,'１月'!$A:$A,0),MATCH(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1))),'１月'!$1:$1,0))
と入力して、４行目までオートフィルすれば、１月分は完成です。

ここまではＯＫでしょうか？

では、セル範囲「Ｂ２：Ｂ４」を列方向にコピー（フィル）してみましょう。
エラーにはならないとおもいますが、なんか変ですよね。
数式を見てみると、参照先のシートが「１月」のままになっていることがわかるかとおもいます。
これは、しょうが無いのでがんばって手で直しましょう。置換でも使えば多少は楽じゃないでしょうか。
・
・
・
・
・
・
これじゃイヤですか？そんなときに便利な関数が実はあったりします。
その名も「INDIRECT関数」
この関数は与えられた文字列を数式として判断するように変えてくれます。
詳しい説明は大変なので、
http://officetanaka.net/excel/function/function/indirect.htm
このあたりをご確認ください。

INDIRECT関数を使えば、先ほどの数式の、「シート名」のところをうまく入れ替えるようにしてやれば、オートフィルが使えるようになります。
「現場○」シートの１行目に１月、２月．．．って入ってるわけですからこれを活用すれば簡単ですよね。
まぁ、数式が長くなるので、置換でいいや～って場合は、そちらで対応してください。
（もこな２） 2017/12/28(木) 17:44

 今回の直接の回答にはなりませんが「エクセルの学校」という観点からの私の考えです。
 1月から12月までSheetを作るとの事ですが、年が変わればまたSheetをどんどん増やす
 か、新たに次年度のファイルを作るという事になるかと思うのですが、このように
 月別・年別にまとめられたSheetやファイルは見た目には分かりやすくて
 良く整理されているかに見えますが
 実は「Excel君」にとっては非常にやり難い方法この上ないと言えます。
 で、私の提案ですが仕入れシートは1つだけにし、月が変わっても年が変わっても、
 ひたすら一つのSheetに入力し続けて、それをデータベースとします。
 そうしておけば、月別あるいは期間別・現場別・仕入先別集計などは常に一個の
 Sheetから取出せて様々な集計が比較的簡単な関数操作だけで可能になります。
 後々の事を考えた場合このような方法が良いのではと思いました。
 長々と書きましたがこういった考え方もあるという事で、参考にどうぞ。

（SS） 2017/12/28(木) 20:39

コメント返信：

[ 一覧(最新更新順) ]

[[20171228145612]] 『複数のシートから文字で検索して1つのシートに集磨x（やち） ページの最後に飛ぶ

[[20171228145612]]　『複数のシートから文字で検索して1つのシートに集磨x（やち）　ページの最後に飛ぶ