今日を乗り切るExcel研究所

Excel に働かされていませんか

セル書式なしでコピー&ペーストしたい

貼り付けの形式を選択することで、セル書式なしでセルの内容だけ貼り付ける手順を検討し、またその操作をマクロ化してみます。

セルの貼り付けは気を使う

セル範囲を単純にコピー&ペーストすると、その値だけではなく、罫線や文字色等の書式も一緒に貼り付けられます。

貼り付け先でせっかくの罫線などが破壊されてイラッとします。

それを避けるには、貼り付け時に「貼り付けのオプション」か「形式を選択して貼り付け」から、セルの書式を含めないようオプションを選択する必要があります。

貼り付けのオプションには、「書式なし」という単純な選択肢は見当たらないのですが、色々やってみて一番筆者のイメージに近いのは「数式と数値の書式」というオプションのようです。

f:id:shego:20170508183236p:plain:w500

「数式と数値の書式」では、

  • 定数(数値や文字列)はそのまま貼り付けられます
  • 数式は値に展開されず、数式のままです
  • 数式内の相対参照も反映されます
  • 数式の書式(小数点以下の桁数や通貨、日付などの書式)も一緒に貼り付けられます

逆に、数値の書式もコピーせずに貼り付け先に合わせたい場合には、「数式」の方を選択します。

さらに、数式を値(定数)に展開したい場合には「値」や「値と数値の書式」を選びます。

これらの細かい違いを字面から判断するは分かりにくく、ペーストするだけでそんな神経を使いたくもありません。

よく使う「下方向へコピー」(Ctrl + D)では貼り付けの形式を選べないのも中途半端な感じです。

手作業でセル書式なし貼り付けをするには

セル書式なし貼り付けを手早く行う手順を検討します。

貼り付けの形式を選択するタイミングは2回あって、「貼り付けの前」と「貼り付けの後」で選択できます。

貼り付け前」には右クリックで「貼り付けのオプション」メニューか、「形式を選択して貼り付け」ダイアログが使えます。

f:id:shego:20170508175123p:plain:w254

貼り付け後」には右下にアイコンが表示されてCtrlキーで「貼り付けのオプション」メニューを使用できますが、人によっては煩わしくて非表示にしているかもしれません。

f:id:shego:20170508174622p:plain:w245

「数式と数値の書式」形式をキー操作で選択するショートカット(アクセスキー)は、何通りかあります。

  • 貼り付け前
    • Ctrl + Alt + VREnter
    • AltHVOEsc
    • AltESR
    • Shift + F10SOEsc
  • 貼り付け後
    • Ctrl + VCtrlOEsc

「貼り付けのオプション」メニューを抜けるにはEnterではなく、EscTABを押します。

Enterを押してしまうとなぜか通常貼り付けになってしまいますので、気を付ける必要があります。

【手順例】

  1. 任意のセルをコピーします(Ctrl + C)
  2. 貼り付け先に移動し「数式と数値の書式」選択して貼り付けます
    • キー操作(貼り付け前):
      Ctrl + Alt + VREnter
    • キー操作(貼り付け後):
      Ctrl + VCtrlOEsc
  3. セルの内容がセル書式なしで貼り付けられます

練習すれば慣れるかもしれません。

慣れそうになければ、クイックアクセスツールバーに「数式と数値の書式の貼り付け」ボタンを登録したほうが早いでしょう。

ここではその詳細は紹介しませんが、そのコマンドボタンは見つけ辛くて、「リボンにないコマンド」グループの方にありますのでそちらから探してください。

ちなみに、「リボンにないコマンド」のボタンを眺めると、「貼り付け先の書式を適用して貼り付けます]といういかにもそれっぽいボタンもあるのですが、これはセルをコピーするとなぜかグレーアウトして使えませんでした(Excel 2013)。



コピーされているセルを書式なしで貼り付けるマクロ

コピーされたセルを「数式と数値の書式」で貼り付けるマクロ VBA を作成してみました。

このマクロは「マクロの表示」ダイアログからは実行できない(コピーモードが解除されてしまう)ので、ボタンやショートカットに割り付けるなどして実行してください。

マクロにショートカットを割り付けるならCtrl + Shift + V(通常の「貼り付け」(Ctrl + V)にShiftを追加したもの)が分かりやすいでしょう。

ついでに、「下方向へコピー」用と「右方向へコピー」用の同様のマクロも用意しました。

Option Explicit

' Ctrl + Shift + V
sub 貼り付け_数式と数値書式のみ()
    If Application.CutCopyMode = xlCopy Then
        Selection.PasteSpecial xlPasteFormulasAndNumberFormats
    End If
End Sub

' Ctrl + Shift + D
Sub 下方向へコピー_数式と数値書式のみ()
    If TypeName(Selection) = "Range" Then
        fillDownValues Selection
    Else
        Beep
    End If
End Sub

' Ctrl + Shift + R
Sub 右方向へコピー_数式と数値書式のみ()
    If TypeName(Selection) = "Range" Then
        fillRightValues Selection
    Else
        Beep
    End If
End Sub

Private Sub fillDownValues(rng As Range)
    With rng
        If .Rows.Count > 1 Then
            .Rows(1).Copy
            .PasteSpecial xlPasteFormulasAndNumberFormats
        ElseIf .Row > 1 Then
            .Rows(1).Offset(-1, 0).Copy
            .PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    End With
    
    Application.CutCopyMode = False
End Sub

Private Sub fillRightValues(rng As Range)
    With rng
        If .Columns.Count > 1 Then
            .Columns(1).Copy
            .PasteSpecial xlPasteFormulasAndNumberFormats
        ElseIf .Column > 1 Then
            .Columns(1).Offset(0, -1).Copy
            .PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    End With
    
    Application.CutCopyMode = False
End Sub

【使い方1】貼り付け_数式と数値書式のみ

  1. セルやセル範囲を普通にコピーします(Ctrl + C
  2. 貼り付け先を選択し、本マクロを実行します
    • ショートカット例 : Ctrl + Shift + V
  3. 選択セルにコピー内容(値や数式)が数値書式以外の書式なしで貼り付けられます。

【使い方2】下方向へコピー_数式と数値書式のみ

  1. 貼り付け先のセル範囲を選択します。
  2. 「下方向へコピー_数値書式のみ」マクロを実行します。
    • ショートカット例 : Ctrl + Shift + D
  3. 選択セルにコピー内容(値や数式)が数値書式以外の書式なしで貼り付けられます。
    • 選択セル範囲が1行しかなかった場合は、その1行上のセルの内容がコピーされ貼り付けられます。
    • 選択セル範囲が複数行からなる場合は、その1行目のセルの内容がコピーされ残りの全ての行に貼り付けられます。

【使い方3】右方向へコピー_数式と数値書式のみ

  1. 貼り付け先のセル範囲を選択します。
  2. 「右方向へコピー_数値書式のみ」マクロを実行します。
    • ショートカット例 : Ctrl + Shift + R
  3. 選択セルにコピー内容(値や数式)が数値書式以外の書式なしで貼り付けられます。
    • 選択セル範囲が1列しかなかった場合は、その1列左のセルの内容がコピーされ貼り付けられます。
    • 選択セル範囲が複数列からなる場合は、その1列目のセルの内容がコピーされ残りの全ての列に貼り付けられます。

【注意】 貼り付け後の「取り消し」(Undo)はできません

【注意】 「切り取り」(Cut(Ctrl + X))したセルの貼り付けはできません

本記事内容は Windows 10 の Excel 2013 で動作を確認しました。