絶対参照をせずにセル参照の値をそのままコピペする

セルのコピー&ペーストをする際に参照先を変えずに貼り付ける方法です。絶対参照($を付ける方法)をすれば参照先を変えずに貼り付けれますが、絶対参照をしたくない場面もあるでしょう。

相対参照と絶対参照

相対参照だとA1のセルをA3にコピペすると、数式が =C3 と勝手に指定したセルが変わります。
絶対参照の場合はどこにコピペしようが =$C$1 のまま変わりません。

相対参照のまま、しかも=C1が勝手にC3にならないようにコピペする方法を4種類紹介致します。

方法① セルの中身を直接コピーする

一つ一つのセルしか出来ませんので、大量にあるセルには対応出来ませんが一番簡単な方法です。

セルの中身を直接選択してコピーする

セル編集モードにするか数式バーから直接セルの数式を選択し、コピーします。
こうすれば、セルのコピーではなく=C1という文字列をコピーしている事になりますので、貼り付け先は当然=C1として貼り付けられます。

メリットは簡単さですが、デメリットはセル1つずつしかコピペ出来ないので大量にある場合は相当手間です。

方法② =(イコール)を置換する

コピペしたいセルが大量にある場合はこちらの方が良いでしょう。
=(イコール)を#(シャープ)に置き換えて、セルの中身を数式ではなく文字列に変えてしまうやり方です。

対象セルを選択して、置換します

置換の方法は検索ウィンドウを出すか、ショートカットキーCtrl+Hで出ます。
http://excel-magic.com/post-97/

検索する文字列に「=」、置換後の文字列に「#」を入れ「すべて置換」します。

=→#へ置換後

そうすると=が#に置換されるため、セルが#C1という文字になり、数式として扱われなくなります。
数式として扱われなければ、このままコピペしてもC1がC3に変わることはありません。
その後、置換を利用して#(シャープ)から=(イコール)へ戻してやれば良いのです。

コピペしても文字列なので当然変わらない
置換で#→=へ戻す

デメリット、というか大きな問題なのが数式内に#(シャープ)が最初の段階で入っている場合です。
=IF(C1=0,”###ゼロ###”,”ゼロ以外”) というような数式の場合、=→#の置換を実行すると #IF(C1#0,”###ゼロ###”,”ゼロ以外”) となります。
その後#→=へ戻してしまうと =IF(C1=0,”===ゼロ===”,”ゼロ以外”) という風に意図しない結果になってしまいます。

ですのでこの方法もケースによっては使えない事もあるでしょう。

方法③ セルを数式表示にしてメモ帳を使いコピペ

あまり知られていないショートカットキーなのですが、
Ctrl + Shift + @
これを使うと数式のセルが数式結果ではなく数式自体を表示するモードへ切り替えられます。

Ctrl + Shift + @ を押すと表示が切り替わる

この状態にしてテキストエディタ(Windows付属のメモ帳でも可)に貼り付けます。
ちなみに数式表示モードにしていても、Excel内だけでコピペをすると相対参照は勝手に変更されてしまいます。そのため一旦別のアプリを経由させます。

メモ帳に貼り付ける

そして今度はメモ帳に貼り付けられた文章を全選択し、コピーします。これで文字列をコピーしたことになります。
これは複数のセルに対応できます。A1セルとA2セルの間は「=C1         =$C$1」と空白スペースに見えますが、タブで空いていますので、Excelに再貼付けの際はちゃんとセルを分けてくれます。

メモ帳の中身を全部コピー

Excelに貼り付けます。するとちゃんと相対参照をせずに=C1で貼り付けられる事が出来ます。
数式表示モードはもう一度 Ctrl + Shift + @ を押せば戻ります。

Excelに貼り付ける

デメリットは一旦別のアプリを通すのがちょっと手間ですね。
ただこの方法が一番確実かなと思います。

方法④ マクロを使う

マクロを使う方法です。コードは改変すればいろいろ使えると思います。

以下のコードを標準モジュールに貼り付けて使います。

使い方はコピーしたい範囲を選択して、「コピー」を実行します。すると目には見えませんが範囲がコピーされます。

そして貼り付けたい箇所をセル選択して「ペースト」を実行します。

Private myRange As Range

Sub コピー()
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set myRange = Selection
End Sub

Sub ペースト()
    If TypeName(Selection) <> "Range" Then Exit Sub
    If myRange Is Nothing Then Exit Sub
    
    Dim i As Integer
    Dim j As Integer
    
    For i = 0 To myRange.Rows.Count - 1
        For j = 0 To myRange.Columns.Count - 1
            Cells(Selection.Row, Selection.Column).Offset(i, j).Formula = myRange(i + 1, j + 1).Formula
        Next j
    Next
End Sub

シート間のコピペは出来ますが、ブック間のコピペは出来ません。ご注意を。

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

印刷 検索 TODAY COLUMN VLOOKUP 置換 STDEV DATE SUM 目標 無料 バーコード 偏差値 条件付き書式 テンプレート ユーザー定義関数 変換 コピペ セル選択 ワイルドカード NA() XLSTART 日付 図形 COUNTIF 作業効率UP HLOOKUP SUMIF グラフ フロー図
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑