[[20100719154733]] 『会社別に抽出したいのですが』(素人) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『会社別に抽出したいのですが』(素人)

宜しくお願い致します

     A      B      C       D
 2   NO    日付  会社名  現場名
 3  123   2/2    山田建設   あ邸
 4  444     2/25    田中建設   い邸
 5  323     2/12    近藤建設   う邸
 6  111     2/8     山田建設   え邸
 7  552     5/12    山田建設   お邸
 8  666     5/23    田中建設   か邸
 ↓
 ↓
これが 152行まで続きます
 C列に 12社 出てくる会社名を、隣の表に 会社別に抽出したいのですが 
 あらかじめ G2.J2.M2.P2.S2.V2.Y2.AB2.AE2.AH2.AK2.AN2
 に 12社の名前を 入れてあります

 

     F      G      H    I     J    K   L   M      N
 2  NO    山田建設 空白   NO  田中建設 空白  NO   近藤建設 空白
 3  123    あ邸   ↓   444   い邸     ↓  323  う邸   ↓
 4  111     え邸   ↓   666   か邸   ↓           ↓
 5  555     お邸   ↓            ↓           ↓
 ↓152行まであります

 と こんな感じで AN2まで 会社名が入ります。それで、
 シートが150枚作ってあって そこから このシートにリンク貼り付け でここに来るようにしたので
 開いたり 上書き保存したりする時に 12秒位かかります。
 なので なるべく重たくならないように 関数を入れたいのですが 
 どこに何と入れたら いいでしょうか?
 すみませんが 教えて下さいますでしょうか 宜しくお願い致します m(__)m


 こんばんは。
おひさしぶりです、コタです。

 今回のご質問は、以前のご質問と関連しているのでしょうか。
(なんとなく、1.のご質問と似てはいますが)
5.[[20100529133559]] 18141 『月ごとで作ったシートを年に1枚にまとめたい』(素人)
4.[[20100523235047]] 16247 『 すいません 又間違えて書いてしまっていました』(素人)
3.[[20100523171805]] 15607 『すいません…折角教えて頂いたのに私が間違えていました』(素人)
2.[[20100516133044]] 11904 『日付を入力したら別シートにその内容を抽出したいです』(素人)
1.[[20100516002008]] 10945 『シート1からシート2へ特定の会社名のいくつかの内容のセルを抽出したいです』(素人)

 いずれにしろ、今現在の数式をご提示しないと、どのように変えればいいかなどの回答は
しづらいと思いますよ。

 (コタ)

(素人)コタさん お久しぶりです。見て頂いて ありがとうございます。(^_^)
 今回のは 以前の5件とは 違うもので 見積書の関係です。なので まだ
 作ったばかりで 数式も入れていないです。A〜Dの3:152 には 上のように
 NO・日付・会社名・現場名 が 並んでいます。
 これを隣の F〜AN の表に 会社別に 抽出したいです。
 すいません 説明が変で すいません m(__)m 宜しくお願い致します

(素人)すいません 追記します m(__)m 
 A〜Dの3:152 には シート150枚から
 来ますが 
 B3は ='1'!$I$6  C3は ='1'!$C$6    D3は   ='1'!$C$12
 B4は ='2'!$I$6  C4は ='2'!$C$6  D4は  ='2'!$C$12
 と 152行まで こんな感じで シート1から シート150 までの特定の
 セルを拾ってきます。 これを 隣に作った 会社別の 12の表に抽出したいです すみません 宜しくお願い致します m(__)m

(素人)何度もすみません又 漏れていましたm(__)m 読みにくくなってすみませんm(__)m
 A3:A152 には 手打ちでNOを 入れています。
 A列にあるNOを      F, I, L, O, R, U, X, AA, AD, AG, AJ, AM    に抽出して、
 D列にある現場名を G, J, M, P, S, V, Y, AB, AE, AH, AK, AN  に抽出したいです

 12の会社名は   G2.J2.M2.P2.S2.V2.Y2.AB2.AE2.AH2.AK2.AN2  に入れました
 大変にすみませんが 宜しくお願い致します m(__)m 

 > 今回のは 以前の5件とは 違うもので 見積書の関係です。なので まだ 作ったばかりで 数式も入れていないです。
そうでしたか。勘違いしちゃいました。ごめんなさい。

 >上書き保存したりする時に 12秒位かかります。
大元の問題はここにありそうですが、とりあえずこれは置いておいて、なるべく最小限の計算になるよう組んでみました。
・以前使っていたOFFSETは、計算量が増えやすいので不使用。
・そのため、F列とG列の数式は、それぞれで入力必要(ほとんど同じだけど)。
・1行目の数式で、各社の件数を1回だけ計算。

  ※「_」セルは未入力または""
   [R/C]  [A]   [B]       [C]     [D]         [E]  [F]       [G]  [H]  [I]       [J]  [K]  [AM]      [AN]  [AO]    [AP]
    [1]     _     _         _       _           _    _         3    _    _         2    _     _         1     _       _
    [2]    NO  日付    会社名  現場名      作業列   NO  山田建設    _   NO  田中建設    _    NO  近藤建設     _  作業列
    [3]   123   2/2  山田建設    あ邸  山田建設-1  123      あ邸    _  444      い邸    _   323      う邸     _       1
    [4]   444  2/25  田中建設    い邸  田中建設-1  111      え邸    _  666      か邸    _     _         _     _       2
    [5]   323  2/12  近藤建設    う邸  近藤建設-1  552      お邸    _    _         _    _     _         _     _       3
    [6]   111   2/8  山田建設    え邸  山田建設-2    _         _    _    _         _    _     _         _     _       4
    [7]   552  5/12  山田建設    お邸  山田建設-3    _         _    _    _         _    _     _         _     _       5
    [8]   666  5/23  田中建設    か邸  田中建設-2    _         _    _    _         _    _     _         _     _       6
    [9]     _     _         _       _           _    _         _    _    _         _    _     _         _     _       7
   [10]     _     _         _       _           _    _         _    _    _         _    _     _         _     _       8
  [151]     _     _         _       _           _    _         _    _    _         _    _     _         _     _     149
  [152]     _     _         _       _           _    _         _    _    _         _    _     _         _     _     150

  E3 =IF(C3="","",C3&"-"&COUNTIF(C$3:C3,C3)) →E3:E152フィルコピー
  G1 =COUNTIF($C$3:$C$152,G$2)
  F3 =IF($AP3>G$1,"",INDEX($A$3:$A$152,MATCH(G$2&"-"&$AP3, $E$3:$E$152,0))) →F3:F152フィルコピー
  G3 =IF($AP3>G$1,"",INDEX($D$3:$D$152,MATCH(G$2&"-"&$AP3, $E$3:$E$152,0))) →G3:G152フィルコピー
  ・表を1ブロック作ったあと、F1:H152を選択して、右側にAOまでフィルコピー
  ・その後、2行目の会社名を各表毎のものに修正

 (コタ)

(素人)コタさん 本当にいつもありがとうございます。又コタさんに解答してもらって本当にうれしい限りです (^_^)
 F3とG3が #N/A と出ます 何か私間違っていますでしょうか?
 E3=IF(C3="","",C3&"-"&COUNTIF(C$3:C3,C3))
 F3=IF($AP3>G$1,"",INDEX($A$3:$A$152,MATCH(G$2&"-"&$AP3, $E$3:$E$152,0)))
 G3=IF($AP3>G$1,"",INDEX($D$3:$D$152,MATCH(G$2&"-"&$AP3, $E$3:$E$152,0)))
 G1=COUNTIF($C$3:$C$152,G$2)

 と入れました 宜しくお願い致します m(__)m

(素人)すいません コタさん APの作業列を見落としていました★できました!!  凄いですね〜〜 ありがとうございました。
 ところで 聞いておきたいことがあるのですが、作業列の所とか 人が消さないようにするために 
 そこの列とか範囲だけ かまえなくするような事は 出来るものでしょうか?
 もし出来るとしたら 以前教えて頂いたのにも 数式とかが入っている所とかに 
 カギ?みたいなブロック?みたいなのを 出来るならしたいのですが
 シート全体でなく 部分範囲だけ かまえなくする というのは 出来るものなのでしょうか? 
 すみません 宜しくお願い致します
 それと もう一つ 
 >上書き保存したりする時に 12秒位かかります。
大元の問題はここにありそうですが

 すみません この意味を教えて欲しいのですが、やはり見積書のシートが150枚
 あるのを ここに集めて来るから 開くのに12秒とか かかるのでしょうか?
 このような やり方は あまりよくないのでしょうか?
 すみません 宜しくお願い致します m(__)m

(素人)追記します それと 今回教えて頂いた シート全体に 鍵?みたいな
 かまえなくするように したいのですが これはシート全体 でいいのですが
 どうやったら出来ますでしょうか? エクセルは2003です
 すみません 何卒宜しくお願い致します m(__)m

 できましたか。よかったです。

 > シート全体でなく 部分範囲だけ かまえなくする というのは 出来るものなのでしょうか
 > シート全体に 鍵?みたいな  かまえなくするように したいのですが
どちらもできますね。

 基本となる機能は、「ツール」-「保護」-「シートの保護」です。
上のコマンドを順に選択していくと、「シートの保護」画面になると思いますので、
そのままOKを押すと、そのシートのセル全てが保護状態(入力を受け付けない)になります。
(誤操作の防止だけなら、パスワードとかは設定しなくてもいいかと)

 次に、部分だけのロックですが、これはセルごとにロックするかどうか、を設定できます。
「書式」-「セル」を選択すると、「セルの書式設定」画面が出ますが、その中の
「保護」タブをクリックしてみてください。

 2つのチェックボックスがあって、デフォルトでは、「ロック」のほうにチェックがはいっています。
つまり、セルの「ロック」にチェックが入っている状態で、上述のシートの保護をかけると、
そのセルが保護状態になるのです。

 なので、特定のセルだけ保護をかけるには、以下のようにします。
(と、ここで手抜きをして、microsoftさんの説明ページを貼り付けておきます)
http://office.microsoft.com/ja-jp/excel-help/HA001054825.aspx 現在参照不可

 ただ、この場合、いったん全セルのロックを外す必要があり、ちょっと面倒なので、
逆に、入力してもよいセルだけロックを外しておくほうが楽ですね。

 ----------

 さて、12秒問題のほうですが、数式(B3は ='1'!$I$6など)を見る限り、ただの参照なので、
時間がかかる原因は、やはりシートの枚数(150枚)でしょうかねえ。

 ちょっと確認ですが、この150枚のシートは、いくつのブックに入っているのでしょうか。
例えば、シート'1'からシート'25'までが1つのブックにあった場合、そのブック内に集計用シートを
1枚作って(今回のシートのA〜D列と全く同じレイアウトで)、そのシートを参照するようにしたら、
参照するシート枚数が減るので、時間が短くなるかもしれませんよ。

 それがだめな場合、データをもってくる方法として、これ以上簡単な数式はありませんから、
マクロでデータ転記しかなさそうです。

 (コタ)

(素人)コタさん お返事ありがとうございますm(__)m
 150枚のシートは同じブックです。その150枚のシートを A〜Dに 集計するように作り、その隣に 今回教えて頂いた会差別の表を作ってあります。
 なので この方法で いくしかないですよね?
 保護の方法 出来ました★
 ありがとうございます m(__)m m(__)m

(素人)あ コタさん すみません 今 セルの部分だけを保護しましたが 
 そのシートが 同じものが150枚あるのですが この場合は 一枚一枚 同じ作業を 繰り返して やっていかないと もしかして駄目でしょうか?
 ロックを設定した一枚目をコピーして 2枚目の同じ作りの シートにコピーしましたが 
 ロックが されないようです。 
 という事は 150枚 設定していかないと 駄目という事になりますでしょうか?
 すみませn 教えて ください m(__)m

(素人)何度もすみません
 以前 [[20100526142441]] でマクロで色が変わるように教えて頂いたシートの
 部分的なセルに ロックをかけましたが 、文字を入れると色が変わるように設定してある セルに 入力すると
 エラーが出て 色が変わらなくなりました 
 実行時エラー’1004’Interiorクラスの Color Indexプロパティ を
 設定できません と出ます。それで「終了」「デバック」「ヘルプ」 と出ています 
 マクロが 入れてあるシートでは セルの保護は 無理でしょうか?
 すみません 宜しくお願い致します


 <12秒問題>
 >  150枚のシートは同じブックです。
書き込みをしたあと、もう一度見直してみて、同じブック内なのかなとも思ったのですが、
やはりそうでしたか。

 そうなると、そのブック内のデータや計算量自体が多いということでしょうから、数式の代わりに
マクロなどを使って転記する、ぐらいまでしないと時間短縮は難しそうですね。
とりあえずこれは後回しにしておきましょうか。

 ----------------------------
 <150枚問題>
 >150枚 設定していかないと 駄目という事になりますでしょうか
 前述のとおり、変更不可にするためには、
 (1) セルの「ロック」にチェックを入れる
 (2) シートの保護をかける
の2段階の設定が必要なわけですが、↑の記述は(2)のほうということでしょうか。
(セルの「ロック」有無は、セルのコピペで反映されますので)

 (2)のことだとして、答えはYesです。
複数枚のシートに、一度にシート保護をかける機能はないようです。
そうはいってもさすがに面倒でしょうから、簡単なマクロを挙げてみました。

 以下のマクロを、以前キリキさんに教えていただいた方法(※)で開いた白紙の部分にコピペして、
保護設定マクロを実行(※※)してみてください。
→そのブックの全てのワークシートに、シートの保護がかかります。
 (念のため、一括解除のほうも挙げておきますので、何かのときにお使いください。)
※ツールバーの【ファイル(F)】の左隣にある【XL】(エクセルのマーク)を右クリック → コードの表示
※※ワークシートに戻って、Alt+F8を押す→「マクロ」画面→「ThisWorkbook.保護設定」を選択して実行

 ○【ThisWorkbook】モジュール
 ↓ここから
 '全シート一括保護設定
Sub 保護設定()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect UserInterfaceOnly:=True
    Next
End Sub

 '全シート一括保護解除
Sub 保護解除()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Unprotect
    Next
End Sub
 ↑ここまで

 ----------------------------
 <色不変問題>
[[20100526142441]] 17967 『セルに入る文字によって セルの色を変えたい』(素人)
 >実行時エラー’1004’Interiorクラスの Color Indexプロパティ を 設定できません と出ます
おや、ほんとですね。
D1のロックを外して、シート保護にしても、↑のエラーになっちゃいますね。

 ネットで探してみたら「excel シート保護 Interiorクラスの Color Indexプロパティ」、周知の問題のようです。
対応策としては、色設定前後だけ保護を解除する、というのがありますが、マクロのほうの変更になって
しまいますので、ちょっとあれですね。

 ということで、こちらのほうのシート保護も、↑のマクロで行ってみてください。
        ws.Protect UserInterfaceOnly:=True
この設定で保護すると、マクロでの変更は受け付けるようになりますので。

 それ以外だと、「シートの保護」画面で、下のブロック(ユーザーに許可する操作)の上から3番目にある、
「セルの書式設定」にチェックを付けてから、OKを押す。という手もあることはあります。
が、ロック有無に関わらず、セルの書式設定はできるようになりますので、あまりよろしくないかもです。

 (コタ)

 大量シートの実験を行ってみました。(長文ですみません)

 ○ブック構成
 ・各ブックの特徴は、以下のとおり
  実験1(150シート、数式、  15,000):1,039KB
  実験2(150シート、データ、15,000): 664KB
  実験3(150シート、数式、   4,500): 665KB
  実験4(  1シート、数式、  15,000): 626KB

 ・[大量シート実験1.xls]ブック
  ├【1】   シート → A1 =集計!A1 →A1:E20フィルコピー
  │ 〜
  ├【150】 シート → 同上
  └【集計】シート → A1:E20に適当な数値データ
  ※1〜150シート内に、20×5×150=15,000の数式設定

 ・[大量シート実験2.xls]ブック
  ├【1】   シート → A1:E20に適当な数値データ
  │ 〜
  ├【150】 シート → 同上
  └【集計】シート → 同上
  ※1〜150シート内に、20×5×150=15,000の数値データ設定

 ・[大量シート実験3.xls]ブック
  ├【1】   シート → A1 =集計!A1 →A1:C10フィルコピー
  │ 〜
  ├【150】 シート → 同上
  └【集計】シート → A1:E20に適当な数値データ
  ※1〜150シート内に、10×3×150=4,500の数式設定

 ・[大量シート実験4.xls]ブック
  ├【1】   シート → A1 =集計!A1 →A1:E3000フィルコピー
  └【集計】シート → A1:E20に適当な数値データ
  ※1シート内に、3,000×5=15,000の数式設定

 ○実験
 ・内容
  実験1〜4ブックについて、セーブにかかる時間を計測
 ・結果
  実験1(150シート、数式、  15,000):約6.6秒
  実験2(150シート、データ、15,000):約5.4秒
  実験3(150シート、数式、   4,500):約5.5秒
  実験4(  1シート、数式、  15,000):約0.5秒

 'セーブ時間計測
Private Declare Function timeGetTime Lib "winmm" () As Long
Sub timelap()
    Dim t As Long
    t = timeGetTime()
    ActiveWorkbook.Save
    Debug.Print "timelap: "; timeGetTime() - t
End Sub

 ○結論
 ・セーブ時間は、数式やデータ量よりも、シート数に依存する部分が大きい
 (ちなみにXP,2003SP2)

 --------------------------------

 あまり大量のシートを使用する機会がなかったので、知りませんでしたが、
今回の結果はなかなか意外でした。

 上で書いた、数式の代わりにマクロなどを使って転記する、という方法も、
これだとあまり効果がないですね。

 あとは、いくつか上で書いたように、何枚かのシートを1つのブックにまとめて
外出しにして、集計シートの入っているブックの総シート数を減らせば、全体の
セーブ時間は減るかもしれませんね。

 サンプルがあるのだから、実験してみましょう。
実験1ブックを5としてコピペして、シートを30枚ずつ外出しします。

 ○ブック構成
 ・各ブックの特徴は、以下のとおり
  実験5 (30シート、数式、3,000):272KB、31シート
  実験5A(30シート、数式、3,000):187KB、30シート
  〜中略〜
  実験5D(30シート、数式、3,000):187KB、30シート

 ・[大量シート実験5.xls]ブック
  ├【121】 シート → A1 =集計!A1 →A1:E20フィルコピー
  │ 〜
  ├【150】 シート → 同上
  └【集計】シート → A1:E20に適当な数値データ
  ※121〜150シート内に、20×5×30=3,000の数式設定

 ・[大量シート実験5A.xls]ブック
  ├【1】  シート → A1 =[大量シート実験5.xls]集計!A1 →A1:E20フィルコピー
  │ 〜
  └【30】 シート → 同上
  ※1〜30シート内に、20×5×30=3,000の数式設定

 〜中略〜

 ・[大量シート実験5D.xls]ブック (178KB)30シート
  ├【91】  シート → A1 =[大量シート実験5.xls]集計!A1 →A1:E20フィルコピー
  │ 〜
  └【120】 シート → 同上
  ※91〜120シート内に、20×5×30=3,000の数式設定

 ○実験
 ・結果
  実験5 (30シート、数式、3,000):約1.5秒
  実験5A(30シート、数式、3,000):約0.5秒
  〜中略〜
  実験5D(30シート、数式、3,000):約0.5秒
  合計              :約3.5秒

 ○結論
 ・ということで、ブックを分割すれば、総セーブ時間でも減ることが分かりました。
 ・とはいえ、分割してもいいかどうかは、素人さんの運用上の問題ですので、
  使い勝手やらいろいろ検討してみてください。

 (コタ)

 案のみで恐縮ですが

 元のデータがある150シートのブックはそのままで
 集計用のブックを作って、そちらのマクロから読みこんで集計されてはどうでしょう?

 それでしたら元のブックは保存の必要も無いですから時間など考慮しなくて大丈夫ですし
 元データが改ざんされる危険もありません。
 また数式は使わないのでそちらの考慮も不要でしょう。

 私は普段集計作業をするときは必ずデータと集計のブックは別にしています。
 参考になれば・・・
 (momo)

(素人)コタさん〜〜凄いですね〜〜〜〜びっくりしました!!!研究結果やはりシート数に関係あるのですね〜〜
 しかし 素晴らしくて 驚きます!!
 それでですが最後に書いて下さった集計を別ブックにする やり方を 教えて頂けますでしょうか? 

 それと 上のマクロですが

 >ということで、こちらのほうのシート保護も、↑のマクロで行ってみてください。
        ws.Protect UserInterfaceOnly:=True
この設定で保護すると、マクロでの変更は受け付けるようになりますので

 ここの意味ですが

 このマクロを シートにつければ いいという事でしょうか?
 理解力が 悪くすみません m(__)m 

'全シート一括保護設定
Sub 保護設定()

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect UserInterfaceOnly:=True
    Next
End Sub

 '全シート一括保護解除
Sub 保護解除()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Unprotect
    Next
End Sub


 >集計を別ブックにする やり方
では、以下の手順でやってみましょう。

 (1) ブックを開く
 (2) 「1」シートを開く
 (3) 「30」シートが見えるまで、左下の右三角ボタンを押していく
 (4) Shiftキーを押したまま、「30」シートのタグを押す
   →「1」〜「30」シートまで全てが選択された状態になる
    ※これを、「作業グループ」状態といい、Excelのタイトルに、[作業グループ]と表示されます。
 (5) 「1」〜「30」シートのどれでもいいので右クリック
   →メニュー画面が出る
 (6) 「移動またはコピー」をクリック
   →「シートの移動またはコピー」画面が出る
 (7) 上のリストボックスから、「(新しいブック)」をクリックして、そのあとOKを押す
   →これで、「1」〜「30」シートまでが、新しいブック(Book1とか)に移動しています。
    移動なので、元々の参照数式もそのまま継続されています。
 (8) 新しいブックに名前を付けて保存
   ※保存先のフォルダが、元のブックと同じかどうか注意のこと。
   →これで、「1」〜「30」シートだけのブックが保存されています。

 あとは、残りのシートについても同様に、複数まとめて移動しては保存を繰り返します。

 --------------------

 > このマクロを シートにつければ いいという事でしょうか?
普通に手作業で保護するとエラーになるので、150枚ブックと同じように、色付けシートのあるブックの
ThisWorkbookにマクロを貼り付けて、マクロで保護してみてください。ということです。
(保護のいらないシートは、あとで手作業で解除してくださいね)

 (コタ)

(素人)コタさん ありがとうございます m(__)m 今
 >※ツールバーの【ファイル(F)】の左隣にある【XL】(エクセルのマーク)を右クリック → コードの表示
 でコードを表示しましたが 白紙ではなく 前 色を付けるために教えて頂いた
 マクロのが 書いてあります。この下から 今回の教えていただいたのを
 コピー貼り付け すればいいでしょうか?
 白紙が出ないです (^_^;) すみません (^_^;)

 >色を付けるために教えて頂いた マクロのが 書いてあります
ああ、すみません。この前のキリキさんのご説明をそのままコピペりましたので。

 >この下から 今回の教えていただいたのを コピー貼り付け すればいいでしょうか
そうです(上でも下でもいいですが^^)。
マクロは1つの(元白紙の)シート上に、いくつでも書けますので。

 (コタ)

(素人)コタさん ありがとうございます。 150枚の方を 移動しました。それで マクロのを入れました。それでですが シート全体でなく 
 特定のセルにロックをかけたいのですが これだと シート全体になってしまうみたいです
 すみません (^_^;)
 1枚目に 特定のセルにロックをかけてから その1枚目のシートを
 150枚 シートのコピーを150枚していって、最後に ツール→シートの保護 を
 押すと 全部の150枚のシートに 特定のセルにロックが かかるという事になりますでしょうか?
 それとも 一枚目のセルのロックを作ってから→ ツール→シートの保護 を
 押してから、その シートごと あと149枚 コピーしていけばいいでしょうか?
 すみません お手数おかけ致します (^_^;)

(素人)コタさん すみません それと別ブックの 色のマクロの付いているシートに 
 この前のコードの下に 上のを貼り付けまして→ワークシートに戻って→書式→特定のセルにロックをかけて→ツール→シートの保護
 をしまして 色の変わるセルの所に文字を打ちましたら やはり 同じエラーが 出ます
 すみません (^_^;) 何が間違っていますでしょうか?


 > 特定のセルにロックをかけたいのですが これだと シート全体になってしまうみたいです
特定のセル以外の「ロック」のチェックを外してないからですね。
上で挙げたマクロは、シート保護をするだけなので、そっちは手作業で行う必要があります。

 〜再掲〜
 次に、部分だけのロックですが、これはセルごとにロックするかどうか、を設定できます。
「書式」-「セル」を選択すると、「セルの書式設定」画面が出ますが、その中の
「保護」タブをクリックしてみてください。

 2つのチェックボックスがあって、デフォルトでは、「ロック」のほうにチェックがはいっています。
つまり、セルの「ロック」にチェックが入っている状態で、上述のシートの保護をかけると、
そのセルが保護状態になるのです。
 〜〜〜〜

 以下の手順でやってみてください。

 (1) ブックを開く
 (2) 「1」シートを開く
 (3) 「30」シートが見えるまで、左下の右三角ボタンを押していく
 (4) Shiftキーを押したまま、「30」シートのタグを押す
   →「1」〜「30」シートまで全てが選択された状態になる
    ※これを、「作業グループ」状態といい、Excelのタイトルに、[作業グループ]と表示されます。

 この時点では、「1」シートが開かれている状態なので、そのまま、
 (4) [全セル選択] ボタン (行 1 の上、列 A の左にあるグレーの四角形) をクリック
   →全てのセルが選択された状態になる
 (5) 「書式」-「セル」 をクリック
   →「セルの書式設定」画面が出る
 (6) 「保護」タブをクリック
   →「ロック」と「表示しない」の2つのチェックボックスが出る
 (7) 「ロック」のチェック ボックスをオフにして、OKを押す
   →全てのセルの「ロック」が外れた状態になる
 (8) 特定のセルだけを範囲選択し、上の(5)(6)を行って、今度は
   「ロック」のチェック ボックスをオンにして、OKを押す
   →特定のセルだけ「ロック」が入った状態になる
    ※この時点ではまだ、シート保護を行っていないので、保護はかかっていない

 ここまでの作業で、「1」〜「30」シートの全てについて、セルのロック設定は終わっています。
(作業グループの状態で、セルの編集などを行うと、全てのシートに反映されるので)

 (9) 「1」〜「30」シートのどれでもいいので右クリック
   →メニュー画面が出る
 (10) 「作業グループ解除」をクリック
   →作業グループの状態が解除されて、1つのシートだけ選択した状態になる

 (11) Alt+F8を押す
   →「マクロ」画面が出る
 (12) 「ThisWorkbook.保護設定」を選択して、実行ボタンを押す
   →全てのシートに保護がかかる

 保護のいらないシートがあるなら、このあと手作業で解除してくださいね。

 -------------------------

 > 150枚 シートのコピーを150枚していって
ところで、150枚と書かれていますが、結局150枚で1ブックにしたのでしょうか。

 30シートずつ分割すると使いづらい、とかであれば仕方ないと思いますが、実験した限りでは、
150枚を1ブックにすると、数式がなくても、セーブなどに時間がかかるかもしれません。。

 (コタ)

 書いている間に、次のご質問がはいってしまいましたね。^^
すぐには思いつかないので、明日考えてみます。ではおやすみなさい。


 失礼しますね。
 私の発言がコタさんと混同されているようですので
 ブックを別にするというのは、たとえば
 新規ブックに以下のようなコードでデータを収集してしまえば
 多くのシートを持つブックに細工しなくても済むのでは?という事です。

  Sub データ収集()
  Const myFile As String = "D:\テストデータ.xls" '←実際の150シートのデータのパスに修正
  Dim dat(1 To 150, 1 To 3) As Variant
  Dim i As Long
  With Workbooks.Open(myFile)
    For i = 1 To 150
      With .Worksheets(CStr(i))
        dat(i, 1) = .Range("I6").Value
        dat(i, 2) = .Range("C6").Value
        dat(i, 3) = .Range("C12").Value
      End With
    Next i
    .Close False
  End With
  ThisWorkbook.Worksheets("Sheet1").Range("B3:D152").Value = dat
  End Sub

 (momo)

 まったくの大きなお世話なんですが、常連(笑)になってこられたので
 出来たらもっと一意のお名前にされてはどうでしょう?

 以下のように、同じお名前の方が多いので、例えば今度また質問されたさいに
 同じ方か分かると作業内容の関連や、
 どの程度エクセルを理解されているか分かりやすいと思うのです。
 (その際は改名の旨を記していただけるといいかな、と)

 ちなみにお名前で全文検索かけると
http://www.excel.studio-kazu.jp/cgi-bin/estindex/estseek2.cgi?phrase=%EF%BC%88%E7%B4%A0%E4%BA%BA%EF%BC%89&perpage=10&attr=&order=&clip=-1&navi=0

 愛着のあるお名前でしたら大変失礼しました。スルーしてください。
 (とおりすがりん)

 >色の変わるセルの所に文字を打ちましたら やはり 同じエラーが 出ます
うーん、こちらではエラーにならないですねえ。
保護設定マクロを使って、シート保護していますか?
と書いておきながら、Protect メソッドのヘルプを読んでいて、まずいことに気が付きました。

 〜ヘルプ抜粋〜
 引数 UserInterfaceOnly に True を設定した Protect メソッドをブックのワークシートに適用した場合、
 保存して閉じた後でもう一度開いたブックに対しては、画面上からもマクロからも変更ができなくなります。
 〜〜〜〜〜〜〜

 つまり、保護設定マクロでシート保護して、その場ではエラーにならなくなっても、保存して
開きなおすとまたエラーが発生してしまう、ということです。。(なぜこんな仕様に。。)

 こうなったらもう、色付けマクロを変更するしかないですね!
ということで、以下のようにしてみました。
・変更点は(2010/7/22追加)と書かれた行とその次行の計4行です。
・判定の部分はすでに独自のものに変更されているでしょうから、今のマクロにこの4行をコピペして追加してください。
・ちなみに、このマクロが起動すれば、自動でシート保護されます。

 '色付けマクロ(シート保護追加)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim colr As Integer
    '↓複数セルをまとめて変更したら終了
    If Target.Count > 1 Then Exit Sub
    '↓D1セル以外の変更なら終了
    If Target.Address <> "$D$1" Then Exit Sub
    '↓D1セルの値によって色を指定
    Select Case Target.Value
        Case "山田建設"
            colr = 3 '赤
        Case "近藤建設"
            colr = 4 '緑
        ' 設定を増やすときは、↓の2行を☆行の上にコピペして、
        ' 会社名と数値を変更する
        Case "伊藤建設"
            colr = 5 '青
        Case ""
            colr = xlNone '色なし
        '☆
        '↓上記以外の入力またはブランクのとき
        Case Else
            colr = 41 '水色
    End Select
    '↓色付け直前にシート保護を解除(2010/7/22追加)
    Sh.Unprotect
    '↓D1セルのパターンを指定値に変更
    Target.Interior.ColorIndex = colr
    '↓開いているシートのタグ色を指定値に変更
    Sh.Tab.ColorIndex = colr
    '↓色付け直後にシート保護を設定(2010/7/22追加)
    Sh.Protect
End Sub

 (コタ)

(素人)とおりすがりんさん momoさん コタさん ありがとうございます。
 そうですね 今度から 名前 ゆき にします。ありがとうございました
 それとmomoさん すみません私の理解不足かと知識不足で 教えてくださったのを入れたら
 デバックという エラーが出てしまいました すみません(^_^;)
 それで 最初に教えて頂いたのを入れたら 150枚のほうは出来ました
 ありがとうございました。30枚にしてみましたが 150枚まとまっていないと
 駄目 と他のものに言われまして…結局150枚にしましたが うまく 教えて
 頂いた通りに出来ました ありがとうございました m(__)m

 それと 色のシートの方は
 同じものが25枚あるのですが
 今までのをコードの表示をして、バックスペースで消していって、
 上の 教えて 頂いたのを貼り付けしました。
 1〜25の特定のセルを 作業グループで 設定して それは出来ました
 次に 右クリック→作業グループの解除→ツール→シートの保護 をしますと
 そのシートしか 保護にならず 残り24枚は 保護になりませんが 24枚なので
 手作業で ツール→シートの保護 を押していけばいいかなと思いますが
 何か 私間違っていますでしょうか?色は 綺麗に 出るようになりました
 もし 間違ってなかったら 手作業でも 出来るので いいかなと思います(^_^)
 ありがとうございます (^_^)

 もう素人ではない、ゆきさんですね。了解です。^^

 >次に 右クリック→作業グループの解除→ツール→シートの保護 をしますと
 色付けマクロ・改を貼り付けできたなら、手作業でのシート保護は不要です。
 なぜかというと、
・ちなみに、このマクロが起動すれば、自動でシート保護されます。
こんな仕組みになっているからです。

 25枚のシートで色付けを終わるころには、同時にシート保護もされているはずですので、
確認してみてください。

 (コタ)

(ゆき) 
 わかりました ありがとうございます
 コタさんは 本当に いつも 判りやすく 丁寧に教えてくださり 
 又そこに優しさがプラスされているので 嬉しいです
 ありがとうございます m(__)m m(__)m m(__)m
 それと もう一つ教えて頂きたいのがあるのですが新規で又質問させてください
 以前[[20100529133559]] 『月ごとで作ったシートを年に1枚にまとめたい』
 で 教えて頂いたものに プラスして 別の見方からの 工務店別シートを
 作って欲しいと 他の人に言われまして 自分でやってみましたが 
 情けないですが やはり駄目でした (^_^;)

 年間別売上表シートから抽出してくる 工務店別シートを教えて頂いたものを
 もう一つ 別バージョンで 見たいので 別バージョンのシートを 作りたいのです
 又 質問の仕方を 考えて新規で 質問させてください m(__)m
 本当に ありがとうございました

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.