• ベストアンサー
  • 困ってます

常に参照もとのセルの書式に従う〔Excel〕

例えば、sheet2のA1セルがsheet1のA1セルを参照していたとします(単にsheet2のA1セルが「=sheet1!A1」となっているだけです)。このときに、sheet2のA1の書式設定を参照元であるsheet1のA1セルの書式に常に一致させたいのですが、何か良い方法はないでしょうか? 具体的にいうと、sheet1のA1セルの表示形式が円単位になったり、千円単位になったり、%表示になったりと変化するのですが、その度にsheet2のA1セルの書式も手作業で変えるのが面倒です。表示単位はその都度カスタマイズすることもあるのでマクロで登録することにも無理があります。 それか、VBAで参照もとの書式の情報を返して、それを参照先セルに反映させることができますでしょうか? できれば、何もせずフルオートで対応できるとありがたいのですが。

共感・応援の気持ちを伝えよう!

  • 回答数8
  • 閲覧数11485
  • ありがとう数8

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.7

No4、No6です。 > いつもは標準モジュールに記述しているのですが、sheetに記述するとどう違うのでしょうか? この回答が抜けてましたね。 Sheetのモジュールに記載したのは、そのSheetに対するイベント(シートが選択された、セルが選択された、入力された、ダブルクリックされた等)を判断するためです。 今回はPrivate Sub Worksheet_Activateですから、Sheet2がアクティブになったというイベントです。 そのイベントが起きて初めて記述したマクロが自動的に作動するのです。これをNo5さんがお書きになったように「イベント・ドリブン型のマクロ」といいます。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

イベント・ドリブン型のマクロ・・・。覚えておきます。 自分で書けるようになるまでにはもう少し時間が必要ですが、いつか挑戦してみます。とりあえず今回はコピーペーストで対応します。 私の周りにもここまでVBAができる人がいればよいのですが・・・ 大変助かりました。

関連するQ&A

  • Excelでのセルの書式設定で、ユーザー定義が反映されない。

    Excelでのセルの書式設定で、ユーザー定義が反映されない。 別シートから参照した数値(@)が入力されると「○○○@○」と表示させたいのですが・・・ たとえばですが、 Sheet1のA1セルに「=IF(Sheet2!A1="","",Sheet2!A1)」という数式を入れてあります。 Sheet1のA1セルの「セルの書式設定」を「ユーザー定義」にして、「"花""子""は"@"歳"」にしてあります。 これでSheet2のA1セルに「3」と入力します。 Sheet1のA1セルに「花子は3歳」と表示されると思っていたのですが、「3」しか表示されません。 何が間違っているのかわかりません。 お知恵を拝借させてください。m(__)m

  • Excelの条件付き書式、直接入力と参照した数値は違う?

    Excelの条件付き書式、直接入力と参照した数値は違う? Sheet1のA1セルの数値を Sheet2のA1セルが「=IF(Sheet1!A1="","",Sheet1!A1)」という数式で参照しています。 数値に、「%以上」という文字列を追加したいので、 Sheet2のA1セルの書式設定を、ユーザー定義で「0"%""以""上"」としています。 Sheet1のA1セルに数値を入力 → Sheet2のA1セルが参照 → ユーザー定義で「○%以上」と表示させる。 ・・・ここまでは簡単にできました。 さらに、数値が表示されたときにセルの色を赤くしようと、 条件付き書式で、 「セルの値が 次の値以上 0」 と設定したのですが、入力が無くても赤いままになってしまいます。 設定を、 「セルの値が 次の値以上 ="0"」とか、 「セルの値が 次の値より大きい 0」とか、 いろいろとやってみましたがどうしてもうまくいきません。 参照ではなく、セルに直接入力するのであればこれでうまくいっていましたので、簡単に出来るものだと思っていたのですが、 これはたぶん、私が根本的なことをわかっていないのだと思います。 ご教授頂けましたら助かります。

  • vlookupの結果があるセルの書式を取得したい

    vlookupの結果があるセルの書式を取得したい =VLOOKUP(検索値,範囲,列番号,検索の型)で値を取ってくることは 出来ますが、その値の入っているセルの書式を取得したい。 Sheet1の セルA1に、検索したい値が入っていて、 セルB2にVLOOKUP関数が入り。 範囲はSheet2のA1:D1000となっていて、 列番号に4と入っていて10行目に 検索値と一致する値が入っていた場合に、D10の値は VLOOKUP関数で持ってこれますが、D10のセル書式を セルB2に反映させたいのですが、どのようにすれば良いでしょうか? VBAでプログラムしないとダメでしょうか?

その他の回答 (7)

  • 回答No.8
  • Wendy02
  • ベストアンサー率57% (3570/6232)

こんにちは。Wendy02です。 罫線やパターンを除く書式コピー >現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。 'シートモジュール Private Sub Worksheet_Activate()  'シートをアクティブにすると書式が設定させる  Dim rng1 As Range  Dim rng2 As Range  Dim ar As Range  Application.ScreenUpdating = False  On Error Resume Next  With Worksheets("Sheet1").UsedRange   On Error Resume Next   Set rng1 = .SpecialCells(xlCellTypeFormulas, 3) '定数-文字,数字   Set rng2 = .SpecialCells(xlCellTypeConstants, 3) '数式-文字,数字   If rng1 Is Nothing And rng2 Is Nothing Then Exit Sub '設定させるものがない   If rng1 Is Nothing Then '定数がない場合    Set rng = rng2   ElseIf rng2 Is Nothing Then '数式がない    Set rng = rng1   Else '両方ともある    Set rng = Union(rng1, rng2)   End If   Set rng1 = Nothing: Set rng2 = Nothing  End With  On Error GoTo 0  For Each ar In rng   ActiveSheet.Range(ar.Address).NumberFormatLocal = ar.NumberFormatLocal  Next  Application.ScreenUpdating = True  Set rng = Nothing End Sub >「Private」とプロシージャの先頭に記述して標準モジュールとしてではなくsheetコードとして入力することでフルオート実行なっているのでしょうか? すでに、merlionXXさんから、ご説明されていますが、標準モジュールは、個別のオブジェクト(シートやブック)を持たない、つまり、[反応を受けるための器]がないので、フルオートには出来ません。個別のオブジェクトを持つと、自動的なマクロ(イベント・ドリブン型)にすることが出来ます。しかし、あくまでも、他から設定されないようにするために、Private というキーワードが必要になります。このキーワードをつけることで、[反応を受けるための器]は、そのシートのみで設定されることになります。 厳密に言うと、もう少し複雑になります。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

あれから、記述内容も検討してみたのですが、段々わかってきました。標準モジュールとシートへの記述の違いもだいたいわかりました。ありがとうございます。 (わざわざ、記述中に「'」をつかって注釈までつけて頂きありがとうございます)

  • 回答No.6

No4です。 では、値と表示形式のみ持ってきますね。 Private Sub Worksheet_Activate() With Sheets("Sheet2").Range("A1") .Value = Sheets("Sheet1").Range("A1").Value .NumberFormatLocal = Sheets("Sheet1").Range("A1").NumberFormatLocal End With End Sub ではいかが?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。即解決です。 この辺まで自由自在にVBAの記述ができるとすごいですね!私の場合、マクロ的な記述は卒業しましたが、ここまで自由自在にはかけません。さすがです!

  • 回答No.5
  • Wendy02
  • ベストアンサー率57% (3570/6232)

こんにちは。 イベント・ドリブン型のマクロです。シート2に設定するなら、シート2のシートをアクティブにすると、シート1側の書式が1瞬でコピーされます。 シート2 のシートタブを右クリックして、「コードの表示」で、出てきた白い画面に以下を貼り付けて、Alt + Q でVisual Basic Editor 画面を締めます。後は、シート1を開き、もう一度、シート2を開けば、設定されます。 Private Sub Worksheet_Activate() 'シートをアクティブにすると書式が設定させる  Dim r As Range  Application.ScreenUpdating = False  Set r = ActiveCell  Worksheets("Sheet1").UsedRange.Copy  ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats  r.Select: Set r = Nothing  Application.CutCopyMode = False  Application.ScreenUpdating = True End Sub これで出来ているかと思います。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

こちらもすごい!です。 一発解決です! 「Private」とプロシージャの先頭に記述して標準モジュールとしてではなくsheetコードとして入力することでフルオート実行なっているのでしょうか?

質問者からの補足

教えていただいた記述ですと、書式情報の全てを取り込んでしまいますが、もし表示形式に関するもののみ(円や%など)の取り込みが可能でしたらアドバイスいただけないでしょうか? 現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。

  • 回答No.4

VBAです。試してみてください。 何もせずフルオートで対応できますよ。 sheet2のタブを右クリックし、コードの表示。 以下をコピペ。 Private Sub Worksheet_Activate() With Application .EnableEvents = False Worksheets("Sheet1").Range("A1").Copy _ Destination:=Worksheets("Sheet2").Range("A1") .EnableEvents = True End With End Sub

共感・感謝の気持ちを伝えよう!

質問者からのお礼

すごいですね!一発解決です。 ありがとうございます。 VBAは使用しているのですが、まだまだ使いこなせていません。いつもは標準モジュールに記述しているのですが、sheetに記述するとどう違うのでしょうか? 標準モジュールに記述するとどのシートでも実行できるというのはわかったのですが、本当の使い分けが良くわかっていません。 今回のようにsheetに記述すると、常にフルオート実行ということなのでしょうか?

質問者からの補足

教えていただいた記述ですと、書式情報の全てを取り込んでしまいますが、もし表示形式に関するもののみ(円や%など)の取り込みが可能でしたらアドバイスいただけないでしょうか? 現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。

  • 回答No.3
  • moon00
  • ベストアンサー率44% (315/712)

#2さんの回答にプラスアルファ。 Sheet2のA1セルの値を他で使いたい、ということであれば、 普通に「=sheet1!A1」としておいて、そのセルの上にさらに#2さんの方法で 図のリンク貼り付けをする、という方法があります。 下のセルが透けて見える場合は、図の書式設定で 「色と線」の「色」を自動にしてみてください。 (でも、結局Sheet2のA1セルの値ってことは、Sheet1のA1セルの値なので、 数式につかうなら、そちらを使えばいい話ですけど。)

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。やはり手作業が必要ですね。

  • 回答No.2

Sheet2のA1セルが表示にしか使われないのであれば、 ・sheet1のA1セルをコピー ・Sheet2のA1セルを選択 ・Shift押しながらメニュー「編集」→「図のリンク貼り付け」 という手はあります。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。

  • 回答No.1

関数では値のみしか参照できないと思います。 書式までをフルオートでということなら、値が変わったときに、マクロでセルごとコピーするという手がありますが、それではダメでしょうか?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます

関連するQ&A

  • エクセルの他シートからの参照方法について

    エクセルファイルで他のシートからデータを参照して反映させたい場合について、操作が分からないことがあるのでどなたか教えて下さい。 説明がうまくできていなかったらすみません。 sheet1のA列にsheet2のB列のデータを反映させたい場合、sheet1のA1に「=sheet2!b1」sheet1のA2に「=sheet2!b2」…というように設定し、以下は設定済みのA列を選択して下にドラッグしていけば連続データとして参照式が反映できると思うのですが、参照したいデータが連続したセルでない場合の方法が分かりません。 たとえば、sheet1のA1にはsheet2のb1、sheet1のA2にはsheet2のb11、sheet1のA3にはsheet2のb21…というように、反映させたいシートのセルは連続しているのですが、その参照元となるシートのセルは一定の間隔を空けて飛んでいる場合、上記と同じようにドラッグしても、セルの値におかしな数値が入ってしまいます。 仕方がないのでひとつひとつ参照先となるセルを手入力している状態ですが、効率が悪いので、ドラッグ等で一気に参照元を指定したいのですが、どのようにしたらいいのでしょうか? よろしくお願い致します。

  • Excelで、「+○,○○○」という表示を、数値として表示させるには?

    Excelで、「+○,○○○」という表示を、数値として表示させるには? 私の質問の書き方が悪かったので、改めて質問をさせて頂きます。 前回、お答えくださった方々には、この場を借りてお詫び申し上げます。 さて、本題ですが、 Sheet1のA1セルに、 Sheet2のA1セルに手入力した、「数字の前に + あるいは - が付く数値」を、桁区切りのカンマ付きで表示させています。 + を表示させるために、参照元のセルの書式を「文字列」にしています。 参照先のSheet1のA1セルの隣のA2セルに入っている数値を、 Sheet1のA1セルが -○,○○○ の場合には赤字で、 Sheet1のA1セルが +○,○○○ の場合には黒字で表示させたくて、条件付き書式で [=$A$1<0]という数式を入れてみたものの、 参照元が「文字列」なので、どうやら参照先も文字列と判断されてしまうらしく、条件付き書式が機能してくれません。(参照元を「標準」等に直せば機能します) +○,○○○という表示を、数値として表示させることができれば、解決すると思うのですが、他に方法があればそれにはこだわりません。 なにか良い案はないでしょうか? お知恵を拝借させてください。

  • excel 参照しているセルに色がついていたら、返すセルにも色もつける

    excelの詳しい方、ぜひ教えてください! Sheet1にあるデータを、計算式によってSheet2に参照させていますが、Sheet1のセルの色までは参照する事ができません。 計算式はいたって簡単で、例えばSheet1のA1を参照している時は、Sheet2のセルに =Sheet1!$A$1 と計算式を入力しています。 条件付き書式や関数も考えたのですが、 ”もし、参照しているセルに色がついていたら、返すセルにも色もつける” をどうすれば出来るのかわかりません。 どうか、わかる方ぜひ教えてください。お願いします!!

  • エクセルのセル参照について

    例えば、Sheet1のA1に「1」、A2に「2」、A3に「3」と入力して、 Sheet2のA1に、「1、2、3」と表示させたいのですが、可能でしょうか? 単に一つのセルを参照するだけなら出来ますが、複数のセルを参照することが出来るのでしょうか? よろしくお願いします。

  • Excelのセル表示形式について

    Sheet1のA1セルに「教えて!goo」と入力。 Sheet2のA1セルに「=Sheet1!A1」と入力。 本来なら、ここでSheet2のA1セルに「教えて!goo」と表示されるはずなのですが、なぜか「=Sheet1!A1」という文字列が表示されてしまいます。 どのように設定すれば、参照元のセル内容を正しく表示することができるのでしょうか?ご教示よろしくお願いいたします。

  • 別シートのセルにある数値、+○,○○○ -○,○○○ を参照し表示させ

    別シートのセルにある数値、+○,○○○ -○,○○○ を参照し表示させたい。 2点ほど質問させてください。 1. Sheet1のA1セルに、 Sheet2のA1セルに手入力した、「数字の前に + あるいは - が付く数値」を、桁区切りのカンマ付きで表示させたいのですが、 関数式「=IF(Sheet2!A1="","",Sheet2!A1)」ではうまくいきません。 ちなみに、- の付いた場合には赤字で表示させたいと思います。 2. Sheet1のA1セルが、上記でうまく表示されたとして、 今度はその隣のA2セルに入っている数値を、 Sheet1のA1セルが -○,○○○ の場合には赤字で、 Sheet1のA1セルが +○,○○○ の場合には黒字で表示させるにはどうしたらよいでしょうか? たぶん「条件付き書式」で出来るような気はするのですが、定義をどうしたらよいものか見当が付きません。 ちなみに、Sheet1のA2セルに入っている数値は、Sheet2の別セルからの参照になっています。 以上ですが、お知恵を拝借させてください。 よろしくお願いいたします。

  • Excel2010でセルの参照について

    こんにちは。 Excel2010のセルにリストを設定しました。 この設定を別のシートのセルにも適用したいのですが、 ・リストの設定を参照して、選択される値は参照先の自由に。 ・参照元のリストの項目が追加されると、参照先でも追加される。 ということを行いたいのですがExcelではどのようにすればよろしいでしょうか。 コピー&ペーストでは、参照元のリストの項目が増えても、参照先では増えません。 =シート名!セル位置で行うと、参照元でセットされた値だけが参照先に表示される。 となり私の期待したものになりません。 上記のようなことは可能でしょうか?

  • EXCELで空白セルを参照したとき、0が表示されてしまう

    EXCELでsheet1、sheet2があり、sheet1にはsheet2の同じ位置のセル内容を参照させたいと思います。 (例:sheet1A1=Sheet2!A1) ところがsheet2の参照元が空白セルだと、sheet1には「0(ゼロ)」が表示されてしまいます。 このような表示がでないようにするには(空白なら空白を参照させるには)、どうしたらいいのでしょうか。 尚、参照元のデータには一部電話番号や住所も入っており、元々0という数字が存在するデータもあります。 どなたかお知恵をお貸しいただければ、と思います。宜しくお願いします。

  • エクセルで、スペースのセルを=で参照するとゼロ0となってしまう

    エクセルでsheet1には、数字やスペースの並びが入っていて、sheet2で、その値を参照すると、参照元がスペースなのに、ゼロという値が出てしまいます。 例 sheet1 ABCDEFGH 12345 67 sheet2 ABCDEFGH 12345067 ここで、A1のセルには、=sheet1!A1 となっています。 書式は標準ですが、何にしてもF1は0となってしまい ます。 どうしたらsheet2のF1をスペースに出来るのでしょうか。

  • エクセル)VBAで他のブックのセル参照

    はじめまして。 エクセルのVBAについて教えてください。 VBAで、 book1のsheet1のセルA1にあるデータを開いていない(アクティブでない)別のbook2のsheet2のセルB1にコピーしたいのですがこんなことってできるのでしょうか? ・・やはり開いていないとダメなんでしょうか? あと、これって"ブック参照"っていうことなんでしょうか?自分がやりたいことが何なのか判らないので上手く調べる事が出来ず難儀しています。 よろしくお願いします。