今日は、日ごろのExcelのレッスンの中から、「簡単なようで、意外と難しい」この操作のお話をしていきたいと思います。
ただ、２か所に同じ数字を入れるだけなのに、あれ!?どうやるんでしたっけ??
例えば次のような請求書を作る場合です。請求明細の金額を合計したセルがあり、その金額と同じものを「ご請求金額」のらんに大きく表示します。
赤丸をつけた2か所は、同じ金額が入りますよね。
請求書の中で、赤丸の2か所に同じ数字が入っています。これをどう作ったらいいでしょう?(画像タップで拡大できます。戻るときは「戻る」ボタンで戻ってください。)
このように、
計算結果と同じ数字を表示するだけなのに、
これが意外と「むずかしい」ところなんです!
「なぁんだ、簡単だよ!?」と思った方は、この記事はすっとばしていただいて結構です(^^;
「あれ、簡単そうだけど、どうやるんだっけな……」と思ったアナタは、ぜひ最後まで読んでみてください! きっとためになりますよ!
同じものをもう一つ入力するのは、「コピー」ではないの??
「同じものをもうひとつ入力する」と聞いて、
ぱっと思いつくのは「コピー」です。
それでは、この請求額のらんを「コピー」「貼り付け」で正しく作れるか、実際に試してみます。
コピーして貼り付けたら、ゼロになっちゃった・・・(画像タップで拡大できます。戻るときは「戻る」ボタンで戻ってください。)
コピーして貼り付けると、なんと「0(ゼロ)」になってしまいました……
コピーしたのに、金額が変わってしまうのはなぜ?
Excelの「コピー→貼り付け」は、普通のコピー・貼り付けとは違う動きをします。それで、コピーすると金額が変わってしまったのです。
それにはきちんとした「ルール」があるんです!!
- 「Excel以外のときのコピーと、何が違うの?」
- 「どういうルールでコピーされているの?」
これがちゃんと分かれば、あなたもExcelの達人にグッと近づきますよ!!
Excelのコピーは、普通のコピーとなにが違うの?
実は「2つのルール」があるんです。
Excelのコピーには、2つのルールがあります。
- ルール1) 見たものそのままでなく、「中身」がコピーされる
- ルール2) 「相対参照」のルールに従ってコピーされる
この2つのルールを詳しく見ていきましょう。
ルール1) 見たものそのままでなく、「中身」がコピーされる
もういちど、さきほどの状況をおさらいします。この画像のようになっていました。
なぜ、コピーして同じものを貼り付けたはずなのに、片方は「42,420」、もう片方は「0」になってしまったのでしょう?
Excelは、セルの「中身」と「見た目」が違うのが特徴
この謎を解くには、それぞれのセルの「中身」を見てみる必要があります。
このように、常に「中身」と「見た目(表示内容)」が区別されています。
この場合は、「42,420」と表示されていますが、「コピー」を押したときは「42,420」ではなく「=SUM(G28:G29)」がコピーされます。
各セルの「中身」に実際何が入っているかは、そのセルをクリックして数式バーを見ないと分からないのです!!
コピーする前に、必ず数式バーを確認しましょう!
ですので、Excelでセルをコピーしたり、または何か操作をするときは、必ず一旦そのセルをクリックして、数式バーに何が出ているか確認しましょう!
「え～～面倒くさい!」
と思われるかもしれませんが……慣れるしかないです(- -;
慣れれば確認が「習慣」になり、自然とマウスでクリックしている自分に気づくと思います(^^;
ルール2) 「相対参照」のルールに従ってコピーされる
では、もう一つのルールについてご説明しましょう。
さきほどコピーしてみた請求書ですが、
こんどは「コピー元」「貼り付け先」両方の「中身」がどうなっているのかを見てみます。
- 請求明細の「合計金額」→「=SUM(G28:G29)」
- 上の「御請求金額」→「SUM(D17:D18)」
「中身」がコピーされるって言ったのに、「中身」すら変わっちゃってるじゃない!!
そうなんです。Excelは「中身」すら、そのままコピーされるわけじゃないんです。
なんだこりゃ! これじゃ全然分からないじゃない!! 理解不能です!! (> <)
………って言っていても始まらないので(^^; 気を取り直して、
「中身」がどう変わるかにもルールがあるんです! というお話をします。
画面イメージで見てみたら、意外と「超カンタン!」なルール、それが「相対参照」
この「中身」が変化するルールを「相対参照」といいます。
言葉で説明するよりも、次の画像を見ていただいた方が分かりやすいと思います。
このように、数式が変わってしまったように見えましたが、
現在地を起点にした、計算の「位置関係」は、まったく変わっていないですね!
このように、数式が表す位置関係がコピーされる。このルールを「相対参照」といいます。
このように図で見ると、とても簡単なルールですね!!
「G28」とか「D17」とか、「A3+A4」とか、とにかく数式の中で他のセルを参照しているものは、すべてこのルールに従って変化します。
なお、この逆で「絶対参照」というのも聞いたことがあると思います。
「G28」と書かずに「$G$28」と書くと、いまのルールとは違う「絶対参照」のルールになり、コピーしても数式そのものが変化せず、完全に同じ場所を指すようになります。
「相対参照」と「絶対参照」について、詳しくは当ブログのこちらの記事をご覧ください。
コピーがだめなら、じゃあ、どうすれば同じ数字が入れられる?
コピーでは同じ金額は入れられなかった!
さて、話を請求書に戻しますと、
最初に見たように、コピーでは2か所に同じ数字は入れられませんでしたね。
では、どうすればこの2か所に同じ金額が入れられるでしょうか?
そのためには、「セル参照」という方法を使います。
「セル参照」を使って、どこでも自由に、同じ数値を表示する
「セル参照」は、とても簡単な方法です。
「御請求金額」の空欄D19セルには、「=G30」とだけ入力しています。たったこれだけです。
(※実際には、「=」を打った後すぐにG30セルをクリックして、確定のEnterを押すだけです。)
拍子抜けするほど簡単ですが、今まで教室でExcel基礎コースのレッスンをやってきて、
これが一回でできた生徒様は一人もいらっしゃいません!!
「セル参照」の使い方、おさらいしましょう!
ということで、Excelを使う方なら、実はたぶん毎日使っている、「セル参照」。
その方法を、ここで「おさらい」しておきたいと思います。
- セルの先頭に「=」をつけて「数式」にします。
- 数式の中では、「セル番号」が、その指しているセルの「見た目」の代わりの役目になります
セルの先頭に「=」をつけて「数式」にします。
Excelは、セルの「中身」の先頭に「=」があると、ないとでは、全く意味が違います。
試しに、いまの請求書の「御請求金額」を、「=」あり、と、「=」なし、の両方で打ったものを比べてみます。
このように、
- 「=」ありだと、「G30セル」という意味になる。
- 「=」なしだと、そのまま「G30」という文字になる。
ということになります。
計算はなくても「=」があれば「数式」となります。
いま、「=G30」という数式には、足し算も引き算も、関数もありませんでした。
こんなふうに、なにも計算がなくても、Excelでは「=」がつけば、すべて「数式」と呼びます。
数式の中では、「セル番号」が、そのセルの「見た目」(計算結果)の代わりになります
この記事の初めの方で、
Excelは、セルの「中身」と「見た目」が違うのが特徴
と書いていたのを思い出してください。
G30セルについて、改めて見てみますと、
- G30セルの「中身」(数式)—「=SUM(G28:G29)」
- G30セルの「見た目」(計算結果)—「42,420」
です。
「セル参照」では、「=G30」と書くと、「見た目」(計算結果)の方、「42,420」の意味になります。
けっして、「中身」(数式)の「=SUM(G28:G29)」という文字が表示されたりはしません。
コピーは「数式」、セル参照は「計算結果」
まとめますと、
- 「コピー」「貼り付け」をおこなうと、元のセルの「数式」が、相対参照のルールにのっとってコピーされる。
- 「セル参照」を行うと、元のセルの「計算結果」が、参照したセルにそのまま表示される。
ということですね!!
コピー先で計算しなおす必要がなければ、「セル参照」でOK!
ということは、別の角度から言えば、
- 新しい場所で、もう一度計算をやりなおす必要があるときは「コピー」「貼り付け」で数式ごともってくる。
- 計算をやり直す必要がない場合は、「セル参照」で計算結果だけもってくればよい
ということになります。
あるセルの内容をコピーするとき、コピー先にある数字を使って再計算する必要があれば、
「コピー」を使って数式そのものをコピーしておかないと、計算が違ってしまいますね。
逆に、今回例に出した、請求書のような場合、
ただ単に同じ金額をもう一つ、表示するだけですので、「セル参照」でよいのです。
いかがでしょうか? 少し、つかめてきましたでしょうか?
Excelのテキストや練習問題をやるときに、ぜひ、ちょっと思い出してみて下さい!
やっているうちに、だんだん区別が身についてくると思います!
オンラインレッスンはどこからでも受講できます
当教室では、新型コロナ対策として【オンラインレッスン】を開始しました。
「接触ゼロ」だ安心してレッスンできるようにしたのですが、同時に、全国どこからでも同じように受講できるようになりました。
下のバナーからオンラインレッスンについて詳しく見ることができます。体験レッスン1時間無料です。
この記事について質問したい方、お近くにパソコン教室がない方、新型コロナ感染を防ぎたい方、ぜひお待ちしています。
コメント
お忙しい中、ご回答ありがとうございます。
ゼロの時に空白をさけるためだったんですね。
ありがとうございます。
お忙し時にお邪魔ばかりしてご迷惑をお掛けしました。
いいえ、コメントありがとうございました。お役に立てて良かったです！
１つ疑問があるのですが
「#,##0」の所ですが
「#,###」ではないのはリ湯があるのでしょうか。
すいません、知識が浅くて、、、、、。
はい、これは、ゼロの時空白にならないようにするためです。
実際は、領収書なのでゼロはあり得ないとは思うんですけれども、そういう理由で普段そこはゼロにするものですから、ゼロにしています。
例えば、免税商品などがあった場合、消費税はゼロがあり得ますが、#,###にしておくと、そこが空白になってしまいます。
ありがとうございます。
うまくいきました。出来ました。
TEST関数を使うのですね！！考えつかなかったです。
流石です！！ご教授ありがとうございました。
助かりました。
いつもお世話になっております。
いつも数字をコピーしてくるときに不便をかんじるのが同一セル内へのコピーです。
例えばですが、領収書のフォーム枠外で 税込み 10,000(=A1)
税別 909(=INT(A1*0.1/1.1)
消費税 9091(=A2-A3)
と計算して、
領収書の１つのセルに
価格 10,000（税別 9091，消費税 909）と記載します。
この時、枠外で計算した数字をコピペして持ってきます。
この領収書フォームが項目別に設定されていればいいのですが、
１つのセル内に「価格 10,000（税別 9091，消費税 909）」と記載されていて、
不便なのですが、
これを領収書のフォームを変更せずに、枠外の計算式から
１つのセル内にもってくる方法はないですか。
セルを結合や領収書のフォーム変更ができないのでこまってます。
ご教授お願い致します。
ご返信遅くなりました。つまり「価格 10,000（税別 9091，消費税 909）」という文字列を生成して、これをコピペできればよい、という状況でしょうか。
セルA2 10000 セルB2 909 セルC2 9091
セルA3
=”価格 “&TEXT(A2,”#,##0”)&”（税別 “&TEXT(C2,”#,##0”)&”，消費税 “&TEXT(B2,”#,##0″&”)”)
とおくと、セルA3に
価格 10,000（税別 9,091，消費税 909)
という文字列が生成されますので、これをこのまま値貼り付けしてはいかがでしょうか?