[[20120130100936]]　『全く同じシートの名前がある２つのブックから』（ママン）　ページの最後に飛ぶ

『全く同じシートの名前がある２つのブックから』（ママン）
エクセルバージョン：作成は2010ですが、仕事先が2003年ですので2003年対応でお願いします。

色々と調べたのですが回答に繋がらなかった為、知恵をお貸しください。よろしくお願いします。書き方が悪かったらすみません。

●[book1（1金銭出納帳.xls）]に出納帳を作っています。sheetには1～100まで数字で名前をいれようと思っています。（現在は7までしかありません）

 　 ｜　　A　｜　　 B　 ｜　　それと左の様な[メニュー]がシートの左端にあります。
 1　｜H２４年｜ 一 覧 表｜　　A3～A103にはシートに各シートに飛べるようにリンク
 2　｜取　　 引　　先   ｜　　貼ります。(これは他の質問の中からマクロで問題解決
 3　｜　 1　 ｜㈱ああ　 ｜　　してます)
 4　｜　 2　 ｜㈱いい　 ｜　　B3～B103には取引先の名前が入ります。

次に1～100のシートには、H2に一覧表のシート名にあてはまる業者名を自動で入力したいと考えております。
（一覧表のみで業者名の変更を行いたいのです）

7までは手入力ですがとても大変です・・・（現在は[1]のH2には「=メニュー!$B$3」と入れております）

そして、もしも可能なら、0と言うシートをベースにコピーしてシートを増やす予定なのですが自動でシート名をふれないでしょうか？

 - - - - - - - - - - - - - - - - - - - - - - - -
もうひとつお願いします。

●[book2(年間売上表.xls)]と言う中のシートはbook1と全くシート構成で作っています。
ただ違うのは1～100の中身なのですが、表になっていまして、

 　　　A　 ｜　　B　　｜　　C　　｜　　D　　｜　　E　　｜　　F　　｜
 1｜H24年度｜　　　　　　　　　取　引　先　名　　　 　　　　　　　｜
 2｜　月　 ｜売上合計 ｜消 費 税 ｜入 金 額 ｜手 数 料 ｜差引残高 ｜
 3｜　1　　｜　　 　　｜　　 　　｜　　 　　｜　　 　　｜　　 　　｜　
 4｜　2　　｜　　 　　｜　　 　　｜　　 　　｜　　 　　｜　　 　　｜

と、book1に入力した物がこちらに反映されるようにしております。
（当初は一緒のbookでしたが、サイズが肥大になりすぎて重かったので別にしました）

そこで、今関数で、B3には
=SUMPRODUCT(('[1金銭出納帳.xls]1'!$A$7:$A$1000=A3)*('[1金銭出納帳.xls]1'!$B$7:$B$1000=12),('[1金銭出納帳.xls]1'!$K$7:$K$1000))+SUMPRODUCT(('[1金銭出納帳.xls]1'!$A$7:$A$1000=A3)*('[1金銭出納帳.xls]1'!$B$7:$B$1000=13),('[1金銭出納帳.xls]1'!$K$7:$K$1000))

と入っています。２つのｺｰﾄﾞと月によって合計する為です。

C3には＝=SUMPRODUCT(('[1金銭出納帳.xls]1'!$A$7:$A$1000=A3)*('[1金銭出納帳.xls]1'!$B$7:$B$1000=10),('[1金銭出納帳.xls]1'!$K$7:$K$1000))

D4、D5も同様に関数が入っています。

今はシート毎に関数の参照先シートを手入力で変更していってますが、数があるので先が見えなくて大変です。
B3～F3まで自動で参照先のシート名が変更できれば、2月～12月まではコピーすればいいだけなので楽なのですが、こういったことはできないでしょうか？

説明が下手で申し訳ありませんが、どうか力をお貸し下さい。

 なかなか回答が付かないですね。。。
シートが100？とか、SUMPRODUCTがいっぱい（＠＠；ってことで、運用しにくそうな印象だからかもしれません。
かといって一から設計の見直しアドバイスも差し上げられそうにないので、ご希望の動作はこんなの
かな？と呼び水的にアップしてみます。
どなたかがもっといい提案をしてくださるのを祈りつつ。

 book1のシート0のH2に
=INDEX(メニュー!B3:B102,--MID(CELL("filename"),FIND("]",CELL("filename"))+1,3))
と仕込んでおきます。
 標準モジュールに
 　
Sub テスト()
 Dim i As Long
 i = Sheets.Count
 Sheets("0").Copy After:=Sheets(i)
 Sheets(i + 1).Name = i - 1
End Sub

 として、実行すれば１つずつシートがコピーされていくと思います。

 さらに気の進まないbook2ですが、同じシート名を参照するので、INDIRECT関数と上記のCELL関数を組み合わせれば参照出来ると思います。
 '[1金銭出納帳.xls]1'!$A$7:$A$1000　→　
 INDIRECT("'[1金銭出納帳.xls]"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,3)&"'!A7:A1000")
 試していないんで、もしうまく参照出来ない場合は、MIDの前に--とか1*を付け加えてみてください。
(かなれっと)

 あぅ。CELL関数で、対象範囲を指定してないとマズイみたい。
 H2には関数を入れないばーじょん。コードを次に差し替えてください。。。
Sub テスト2()
 Dim i As Long
 i = Sheets.Count
 Sheets("0").Copy After:=Sheets(i)
 With Sheets(i + 1)
    .Name = i - 1
    .Range("H2").Value = Sheets("メニュー").Range("B" & i + 1).Value
 End With
End Sub

 CELL関数ですが、対象範囲の指定を忘れてました；
 CELL("filename")じゃなくてCELL("filename",A1)なんてしておけば大丈夫そうです。
(かなれっと)

 まだ作っている途中みたいだから、まだ方向転換の余地は有りそうかな。。
＞シート毎に関数の参照先シートを手入力で変更していってますが、
という障害の原因は、
＞0と言うシートをベースにコピーしてシートを増やす予定
というポイント。
1金銭出納帳.xlsの１～１００のシートがどんな構成なのかは分かりませんが、
年間売上表.xlsのシートと１対１の関係で参照させたいならば、
＞数があるので先が見えなくて大変
を通過しなきゃいけない。
　
「1金銭出納帳.xls」と先頭に「１」をつけているということは、そのあと、
「2金銭出納帳.xls」以下、１２か月分のブックが控えていそうな気がしたり。
　
どれくらいのデータ量があるのか分かりませんが、
１００シート分のデータをひとつのシートでリスト形式で管理するようには出来ませんか。
　
ひとつのデータ格納場所から必要なデータだけ、その都度参照できるようにすれば
100シートは必要ありません。
100シート有るからハイパーリンクメニューが必要ですが、
たとえばリストから取引先を選択するとその取引先のデータが表示できるように
しておけば、１シートで充足します。
　
年間売上表.xlsの方も同様で、１シートで１００件分のデータをその都度切り替えて
みることが出来れば、１００シートは必要ないように思えます。
　
データの保存場所を決めるのは人間で、どのデータを参照するのかを決めるのも人間。
エクセルは勝手に保存場所を推測はしてくれないので、データの場所は人間が決めなきゃいけない。
保存場所が増えれば、ソレをエクセルに指示する手間も増えるので、
苦労するわけです。
　
関数にしろ、データメニューにしろ、
「ユーザーはデータをリスト形式で用意する」ことを当たり前のようにして作ってあるので、
それに沿ってデータを用意するとかなり楽が出来るはずです。
　
（みやほりん）

＞かなれっと　様
ご返答ありがとうございます！

＞ H2には関数を入れないばーじょん。

 こちらで、マクロ実行すれば、book1のH2の名前が反映されるようになりました！！
 本当にありがとうございます。
 マクロ使いこなせるの本当にすごいです！

そして、book2のほうなのですが、とても使いづらい事がよくわかったため同じブックで管理することにいたしました。

全て解決できました本当にありがとうございました。

＞みやほりん　様

ご指摘ありがとうございます。

 そういった方法があるとは思いませんでした。
 ぜひとも参考させて頂きます。
 ありがとうございました。　
　（ママン）

コメント返信：

[ 一覧(最新更新順) ]

[[20120130100936]] 『全く同じシートの名前がある２つのブックから』（ママン） ページの最後に飛ぶ

[[20120130100936]]　『全く同じシートの名前がある２つのブックから』（ママン）　ページの最後に飛ぶ