Excelで「数式をそのまま別の箇所にコピーしたい」ということ、ありますよね。
じつはExcelにそのような機能は用意されていません。
…が、Excel2013以降でしたら、ひと工夫することで可能なんです。
この表では、A1～B2セルに「1」「2」「3」「4」と入力してあります。
そしてC3～D4セルには「=A1*10」「=B1*10」「=A2*10」「=B2*10」と数式が入力してあります。
ではここで、C3～D4セルの数式をそのまま使いたいと思い、F3～G4セルにコピペしたらどうなるでしょうか。
…すべて「０」になってしまいました。
これは数式をコピーすると、行や列がずれた分だけ、数式も自動でずらされるためです。
F3～G4セルに入力された式は、「=D1*10」「=E1*10」「=D2*10」「=E2*10」となってしまいました。
D1～E2セルには何も入力されていない＝0とみなされるため、0×10＝0 となってしまったんですね。
そこでひと工夫、次の手順をふみます。
- コピーしたい数式をFORMULATEXT関数で参照する
- FORMULATEXT関数の結を値貼り付けする
- 「=」を「=」に置換する
言葉だけだとわかりづらいので、例をふまえて説明しますね。
Contents
Excelで数式をそのままコピペする方法（Excel2013以降)
コピーしたい数式をFORMULATEXT関数で参照する
F3セルに、「=FORMULATEXT(C3)」と入力し、さらにF3～G4セルにコピーします。
すると、次のような状態になります。
FORMULATEXT関数は、そのセルに入力されている数式を文字列として表示します。
C3セルには「=A1*10」と入力されているので、それがそのまま出力されたんですね。
しかしこのままでは、実際に入力されているのはFORMULATEXT関数です。
そこで、値貼り付けを利用します。
FORMULATEXT関数の結果を値貼り付けする
F3～G4セルをコピーして、上書きするように値貼り付けしましょう。
値貼り付けの方法は、コピー→右クリック→「形式を選択して貼り付け」→「値」にチェック→OK です。
すると、次のような状態になります。
見た目はまったくかわりませんが、数式が変わりました。
F3セルの数式をみると、「=FORMULATEXT(C3)」から、「=A1*10」となっています。
しかしこのままだと、「=A1*10」という式が、文字列として入力されてしまっています。
つまり見た目は数式でも、数式とみなされていない状態です。
ここで仕上げに置換機能をつかいます。
値貼り付けした際に式の先頭に「'」が入力されている場合、削除する方法
ここで値貼り付けした式に「'」が入力されている場合、「'」を削除する必要があります。
「'」が入力されていない場合、この段落は読み飛ばして、次の段落へ進んでください。
「'」を削除するには、以下の手順を行ってください。
- どのセルでも良いので、「０」を入力する
- 「０」を入力したセルをCtrl+Cでコピーする
- 「'」が入力されたセルを選択
- 右クリック→形式を選択して貼り付け
- 「演算」欄の「加算」を選択し、OKを押す
どのセルでも良いので「０」を入力し、コピーする
「'」が入力された式を選択する
右クリックし、「形式を選択して貼り付け」を選択
「加算」を選択し、「OK」を押す
すると式の「'」が削除されます。
「=」を「=」に置換する
F3～G4セルを範囲指定して、「=」を「=」に置換しましょう。
意味がないように見えますが、うまいこと変換されます。
すると次のような結果になります。
置換を行うことによって、文字列だったものが数式とみなされました。
無事C3～D4セルの数式が、形を変えることなくF3～G4セルにコピーされました。
FORMULATEXT関数はExcel2013以降のみ使用可能ですが、覚えておくといざというとき便利です。
数式をコピペしても変化がおこらないようにする方法
そもそも数式を、コピペしても変化がおこらないようにしておくことが可能です。
『絶対参照』という方法をつかいます。
もともとは「=A1*10」という数式だったものを、「=$A1*10」と変更しました。
おなじように、D3セルは「=$B1*10」、C4セルは「=$A2*10」、D4セルは「=$B2*10」と変更しています。
そしてC3～D4セルを、F3～G4セルにコピーしてみましょう。
『$』をつけるだけで、値も式も変化することなく、そのままコピーすることができました。
固定したい列のアルファベットや行の数字の前に『$』をつけると、コピーしてもその行や列を固定することができます。
これを絶対参照といいます。
対して、$をつけない列や行をコピーすると相対参照されます。
これらの違いは以下のとおりです。
- 相対参照…コピーするとセルがずれた分だけ数式もずらされる
- 絶対参照…数式をそのままコピーする
今回、もともとC3セルには「=A1*10」という式が入力されていました。
これをF3セルに入力すると、C列→F列にコピーすることになるため、3列分ずれることになります。
そのため、コピー先の数式もA→D列にずれ、「=D1*10」となってしまったんですね。
しかし『$』をつけたコピーでは絶対参照となるため、式がずらされることがありません。
「=$A1」とすると列が固定となりますが、行は固定となりません。
列も行も固定してコピーしたい場合は、「=$A$1」とします。
コピーしてもずらされたくない列のアルファベット、または行の数字の前には『$』をつける、と覚えておきましょう。
絶対参照、相対参照、複合参照について、より詳しく書いた記事はこちら。
数式をそのままコピー、貼り付けする方法【Excel2013以降】まとめ
- Excel2013以降では、次の手順をふむことで式をそのままコピーできる。
- コピーしたい数式をFORMULATEXT関数で参照する
- FORMULATEXT関数の結を値貼り付けする
- 「=」を「=」に置換する
- 『$』を列のアルファベットや行の数字の前につけることで、コピーしても列や行を固定することができる
- 行列がコピー後も固定されている状態を絶対参照、そうでない場合を相対参照という
数式をそのままコピーする方法を紹介させていただきました。
いずれもいざというとき役に立つので、ぜひご活用ください。
