[[20190606114036]]　『数式のコピーについて』（ぺんちゃん）　ページの最後に飛ぶ

『数式のコピーについて』（ぺんちゃん）

SUMIFの数式を別ファイルにコピーしたいときについて、、、

元となるファイル（フォーマット）にSUMIFの数式をいれて合計などをだせるように入れました。※エクセル名はフォーマット最新版

=SUMIF(請求書!A18:A49,F26,請求書!AF18:AO47)

※シート1の合計をシート2にいれるようにしています。

そのフォーマットの数式をコピーして、別ファイルにコピー（コピーしたいファイルにはすでにファイル1、ファイル2というふうに名前を変更しています）をすると、どうしても、元のフォーマットの式になります。

=SUMIF('[請求書_ 　フォーマット最新版.xlsx]請求書'!A18:A49,F26,'[請求書_ 　フォーマット最新版.xlsx]請求書'!AF18:AO47)

単純に［　］の中を消せばいいのかなと思い消すと、壊れた式という風になってしまいます　。

どのようにすれば、貼り付けたときに新しいファイルの式になりますか。
すでに貼り付けたいファイル（多数）には、それぞれ数字が入っているので、貼り付けたときにすぐ合計などがでてほしいなと思っています。

＜使用 Excel：Excel2016、使用 OS：unknown ＞

 []の中を消すのではなく、新しいファイルの名前に変えてあげると動きませんか？

（虎） 2019/06/06(木) 12:13

（虎）さん

ご返信ありがとうございます。［］の中の名前をかえてみると、＃VALUEとなってしまいます。
（ぺんちゃん） 2019/06/06(木) 12:22

 お書きの状況だと単なるコピペだけでは元のブックのリンクを外して貼り付けは難しいかと。
 貼り付けてから、リンク元の変更で自ブックを選択するとか、置換で=を☆とかに変更してから
 貼り付けてまた置換で=に戻すとか…ですかね。
（コナミ） 2019/06/06(木) 12:23

私が良く使う方法としては、以下ですね。

 ・コピー元を範囲選択する。
 ・CTRL+SHIFT+@ を押して、数式表示に切り替える。
 ・CTRL+C で数式をコピー。
 ・コピー先のブックに切り替え、貼り付け先を右クリック。貼り付けのオプションで、「貼り付け先の書式に合わせる」を選択。
 ・コピー元に戻って、CTRL+SHIFT+@ で表示を元に戻す。

数式そのままを張り付けるので、行や列の自動変換は行われない点に注意。
（???） 2019/06/06(木) 13:29

（コナミ）さん
ありがとうございます。無事できました。
★と＝を置き換えたらできました。

（？？？）さん
ご丁寧な説明ありがとうございます。次回参考にさせていただきます。

（ぺんちゃん） 2019/06/06(木) 13:47

 ???さん

 Ctrl+Shift+@で数式を表示するというのは、単に表示を数式が見えるようにしているだけで
 文字列とかになっている訳ではないのでSUMIF等の関数であればその状態でコピペしても
 元ブックとのリンクはついてしまう思うのですが。
 如何でしょうか？
（コナミ） 2019/06/06(木) 13:55

数式表示でも、コピーはRangeオブジェクト扱いで通常と変わらないでしょう。 CTRL+Vで貼ってしまえば、同じ結果になってしまいます。

ただ、数式表示にすることで、見た目の数式を文字列としてコピーするのだと思いますが、「貼り付け先の書式に合わせる」を使った場合、数式が単なる文字列として貼られ、これが数式の形として成立するので自動的に数式変換されるのだと思います。表示を切り替えていない場合、同じ貼り付け方をすると、セルの値が貼られます。

Excel2010ではこれでうまくいくのですが、2013以降がどうなるかまでは試していません。（家では別ブックに式をコピーしたい、って程は使わないので…）
（???） 2019/06/06(木) 14:17

 私は2013を使っているのですが、貼り付けた時に「貼り付け先の書式に合わせる」が
 出ないんですよね。
 もちろん、その貼り付けオプションがあるのは知ってますし、表示される場合は
 私も使うんですが。あまり意識していないのでそれが表示される場合の
 条件がわからないのですけど。
（コナミ） 2019/06/06(木) 14:27

 あ、???さんの話で思いましたがクリップボード経由にすると
 できるのかな。
（コナミ） 2019/06/06(木) 14:29

2010の場合は、右クリックメニューに「形式を選択して貼り付け」の1行上に「貼り付けのオプション」とあって、私の書いた手順通り進めると、アイコンが3つ並びます。（何をコピーしたかで、アイコンの種類と数は変わります。他アプリのテキストコピーだとAだけとか）

ショートカットキーだと、ALT+H　V　M ですね。こっちは使えるでしょうか？
（???） 2019/06/06(木) 14:38

または、他のエディタ等に式を貼ってから範囲コピーし直して、コピー先でCTRL+Vでもできますよ。とにかく、数式をRangeオブジェクトから切り離して、単純文字列にしてしまえば、勝手にブック名付加とかされなくなります。
（???） 2019/06/06(木) 14:41

 >ショートカットキーだと、ALT+H　V　M ですね

 う～、最後のMがないですね。
 私が使う時は多分別アプリからのコピペだったのかな。
（コナミ） 2019/06/06(木) 15:03

Excelのセルコピー仕様（シートのオブジェクト構造？）が、SDI化の際に変わっているのかもですね。エディタ経由すると、ひと手間増えちゃいますか…。まぁ、ブック名またはイコール記号を置換するよりは楽かと思います。
（???） 2019/06/06(木) 15:21

今回の計算式のように、計算式の引数の中に、別のシートを参照する範囲があると、他のファイルにコピーした時に、コピーの元ファイルのファイルを参照する式になります。

計算式の引数に指定している範囲が、他のシートを参照していない場合は、そのままコピー先のシートを参照します。
「=SUMIF(A18:A49,F26,AF18:AO47) 」←の場合は元のファイル名は参照しない。

＞「単純に［　］の中を消せばいいのかなと思い消すと、壊れた式という風になってしまいます　。
［　］の中だけではなく、［　］も消しましたか？
もしくは、コピー先のファイルに「請求書」というシートはありますか？

私の場合は、コピーした後に「検索と置換」で、ファイル名およびシート名の部分を置き換えてしまいます。

=SUMIF('[請求書_ 　フォーマット最新版.xlsx]請求書'!A18:A49,F26,'[請求書_ 　フォーマット最新版.xlsx]請求書'!AF18:AO47)

上記の場合、
１）ファイル名を外す（自分自身のファイルを参照する）だけの場合
「検索する文字列」：[請求書_ 　フォーマット最新版.xlsx]
「置換後の文字列」：空白（何も入力しない）

２）1）+シート名が変わる場合
「検索する文字列」：[請求書_ 　フォーマット最新版.xlsx]請求書
「置換後の文字列」：変更後のシート名

３）コピー先のシートを参照する場合
「検索する文字列」：'[請求書_ 　フォーマット最新版.xlsx]請求書'!
「置換後の文字列」：空白（何も入力しない）

といった感じで置き換えます。

解決しているのでもう見ていないかもしれませんが、ご参考まで。

（う） 2019/06/06(木) 15:30

コナミさん

「貼り付け先の書式に合わせる」は、コピー元とコピー先のExcelが別のプロセスの場合、表示されたと思います。
ただ、Excel2013の場合、普通にファイルをダブルクリックしたり、メニューからExcelを起動しても、すでに起動しているExcelと同じプロセスになるので、別プロセスで起動することはほとんどないのですが。。。。

マクロで、ExcelをCreateObjectした時は、別プロセスになるので、「貼り付け先の書式に合わせる」が表示されました。

以前、気になって調べました。
（う） 2019/06/06(木) 15:51

 うさん、ありがとうございます。
 普段意識せずに使っているのですが、今後はもう少し意識しながら
 操作してみようと思います。

 ぺんちゃんさん、私の疑問の解決にスレッドをお借りしてすみません。

 でも、勉強になる内容もたくさん書いて頂いていると思います。
（コナミ） 2019/06/06(木) 16:13

＞置換で=を☆とかに変更してから
貼り付けてまた置換で=に戻すとか…ですかね。

＞★と＝を置き換えたらできました。
　　↓
＞元のフォーマットの式になります。

になると思いますが私の勘違いでしょうか。

勘違いでしたらすみません。

（のりん） 2019/06/06(木) 18:07

コメント返信：

[ 一覧(最新更新順) ]

[[20190606114036]] 『数式のコピーについて』（ぺんちゃん） ページの最後に飛ぶ

[[20190606114036]]　『数式のコピーについて』（ぺんちゃん）　ページの最後に飛ぶ