【MOS対策のヒント】数式をコピーしても正しくなるようにする(複合参照)

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る
セル【C17】に「$」をひとつ追加

【MOS対策のヒント】シリーズでは、教室の「MOS対策コース」から、

皆さんが「むずかしい!」と感じるところについて、解説しています。

今回は、「絶対参照」「複合参照」を扱う問題をとりあつかいます。

※エクセルの画像が多く出てきます。スマホでもタップすれば拡大できますが、パソコンやタブレットをお持ちの方は、そちらの方が見やすいと思います。

「数式をコピーしても正しく計算されるようにしてください」

各出版社の模擬試験問題を見ても、多くの場合、

「数式をコピーしても正しく計算されるように修正して下さい」

と出題されています。

「絶対参照を使って」「複合参照を使って」などとは一切書いてない

のではないかと思います。

 

問題

  • セル【C17】の数式を、セル範囲【C17:F22】にコピーしたとき、正しく計算されるように修正してください。
  • 修正したら、実際にセル範囲【C17:F22】にコピーしてください。

※「合計」「平均」のらんには、すでに正しい数式を入力してあります。

複合参照の例題

「数式をコピーしても正しく計算されるように修正して下さい」の例。ひとことも「絶対参照を使って」などとは書いてありません。
(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

本記事で使う例題ファイルのダウンロード

ここから本記事で使う例題ファイルを、下のリンクからダウンロードできます。

ダウンロードして、実際に操作しながら読み進めていただく

正解を見ないと、そもそも「$」をつける、と発想できない……

こんな問題に出くわした多くの受講生の方が、こんな感想を抱いていらっしゃいます。

「正解を見れば、『$』をつけるのだと分かるけれど、

こんな問題文だけ読んで、どうして『$』をつけるって発想できるんだろう??」

大丈夫です。この記事は、そんな方むけの「解き方」の解説ですので、ぜひ最後まで読み進めてみて下さい。

まずは「下方向のコピー」だけを考えてみよう

今回の問題は、「↓下方向、→右方向両方にコピーする」問題ですが、

一気に全部の範囲を考えるのは大変だし、分からなくなります。

ですので、まずはその片方、「↓下方向」のコピーだけを考えることから始めてみましょう。

①入っている数式の意味を考えます。

まずは、今入っている「=C5*C16」という数式の「意味」を考えましょう。

つまり、「一体なにがしたいシートなのか、その意図をくみとる」作業です。

もういちどシートを見てみましょう。

複合参照の例題

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

「=C5*C16」の意味は

  • 「C5」→クッキングコースの、4月の入会者数
  • 「C16」→クッキングコースの料金

なのですから、「=C5*C16」で計算できるのは、

「4月のクッキングコースの売上金額」

だということになります。

ということは、

この式をどわっ!と全体にコピーして、計算できるものは、というと

「すべての月の、すべてのコースの売上金額」

だということになります。

このシートを作った人は、「すべての月の、すべてのコースの売上金額」が計算したいのだ、ということが分かります。

②まずは試しに、一方向だけコピーしてみましょう。

シートの「意図」が分かったところで、こんどは、

「間違っていることを承知で、試しに数式をコピーしてみる」という作業を行います。

「コピーして正しく計算されるように」と出題されているわけですから、

今入っている 「=C5*C16」という式は「コピーしたら正しく計算されない」ってことですよね。

どれどれ、試しに、「どんなふうに正しくないのか」、見てみるとしましょう。

オートフィルを使って、まずは下方向に引っ張ってコピーしてみます。

下方向に間違った式をコピーしてみる

おー、変なことになりましたね。こりゃ大変。確かに間違っているようです!

でも、ここであわてて「戻るボタン」を押さないでください!!  この「ぐちゃぐちゃ」な中に、正解のヒントが隠されているんです!

③今コピーした範囲の、一番下のセルの内容をチェック!

一見ぐちゃぐちゃですが、この状態でコピーした範囲の一番下、【C22】セルをチェックします。

なぜ一番下を見るかというと、途中のセルを見るより間違いを見つけやすく、はっきりするからです。

コピーした範囲の一番下をチェック

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

「=C10*C21」となっていますね。

たぶん、これが間違った式になっちゃってるのだろう、という見当がつきます。

確認したところで、さあ、ここからが大切なところです。

④このセルが「本当はどうなっていて欲しいか?」を考えましょう

このセルには、本当は

「クッキングコースの、9月の売上金額」

が入らなければなりません。

そこでまず、

「クッキングコースの、9月の売上金額」を計算する正しい数式は、どんな式か考えます。

もういちど最初の状態のシートを見て、考えてみましょう。

本当はどんな式であってほしいか考えます。

【C22】セルが、本当はどんな式であってほしいか考えます。
(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

正しい数式は、

「=C10*C16」

ですね。

つまり

  • コピーした結果:「=C10*C21
  •   正しい数式:「=C10*C16

さて、どこを直したら正しくなりますか??

簡単ですね。

「C21」のところが→「C16」になれば、正しい数式になりますが、

さらにいえば、「C」はあっていますから、

「21」のところが→「16」になれば、正しい数式になりますね!

⑤「コピーしたとき『16』になるように」、元の式をなおします

では、「どこが、どうなって欲しいのか」が分かったところで、

一番最初の【C17】セルに戻ります。

「コピーして正しく計算されるように」という問題ですから、コピーされた先の式を一つ一つ直しても意味がありません。

必ず、コピー元の最初の式にもどって修正し、またコピーしてみる、という手順を繰り返す必要があります。

コピーする前の数式をなおす

  • 最初の数式【C17】→「=C5*C16
  • コピー先の数式【C22】→「=C10*C21」(間違い)
  • 本当は→「=C10*C16」になって欲しい!
  • 修正内容: 「『16』が、コピーしても『16』のまま保たれるようにする」

さあ、どのような修正をすればいいでしょうか??

数式の一部がコピーしても変わらないようにするのは「$」ですね!!

数式の一部を、コピーしても変わらないようにするには、変えたくない部分の前に「$」を付ければいいんです。

そう、ここではじめて、

「$」をつければいいじゃん!!!

という発想が出るのです。

ということで、

  • 【C17】セルを修正: 「=C5*C16」→「=C5*C$16

と、修正します。

セル【C17】に「$」をひとつ追加

修正方法は2通りあって、

  • カーソルを「16」の前にもっていって、キーボードから「$」(Shiftを押しながら4)を入力する
  • 「C16」の中にカーソルを置いて、「F4」を2回押す

どちらの方法でも大丈夫です。

⑥再度、下方向にコピーして確認します

【C17】セルが修正できましたので、

もう一度、下方向にコピーして、「本当にコピーして正しく計算できているか」を確認します。

修正後、再度コピーした結果

正しく「クッキングコースの9月の売上金額」が、計算できていますね!

これで、「下方向のコピー」については、「正しく計算できる数式」の完成です。

ちょっと待って! 「右方向のコピー」も必ず確認してください!

ここで、「できた!!」と喜んで、これで解答にしてしまいがちなのですが、

ちょっと待ってください!!

いま確認したのは、下方向のコピーだけです。右方向のコピーについては、まだ確認が終わっていません。

ここまでの作業次第では「×」になってしまう場合もたくさんあります!!

まだまだ気が抜けないのです!

次にいよいよ「右方向のコピー」を考えてみよう

「下方向のコピー」が正しく計算できましたので、

いよいよ今度は、「→右方向のコピー」も正しく修正していきます。

今まで下方向について行った作業を、右方向についてもう一度繰り返せば、必ず正解にたどりつきます。

①まずは試しに、右方向にコピーしてみましょう。

 

さきほどと同じように、こんどは右方向にコピーして、

「どんなふうに正しくないのか」、あるいは「正しいのか」確認していきましょう。

【C17:C22】を選択した状態で、オートフィルを使って、右方向に引っ張ってコピーしてみます。

こんどは横方向にコピーした状態

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

うーん、こんどは何だか、もっともらしい数字が並びましたね。

もしかして正解か?? と思ってしまうのですが、ちょっと待ってください!!

このまま正解になる場合もありますが、再度修正が必要な場合もあります。

②今コピーした範囲の、一番右下のセルの内容をチェック!

この状態でコピーした範囲の一番右下、【F22】セルをチェックします。

コビーした一番右下のセルをチェック

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

「=F10*F$16」となっていますね。

確認したところで、正しいかどうかのチェックにはいりましょう。

③このセルが「本当はどうなっていて欲しいか?」を考えましょう

このセルには、本当は「子供向けコースの、9月の売上金額」が入らなければなりません。

そこでまず、「子供向けコースの、9月の売上金額」を計算する正しい数式は、どんな式か考えます。

コビーした一番右下のセルをチェック

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

正しい数式は、

「=F10*F16」

ですね。

つまり

  • コピーした結果:「=F10*F$16
  •   正しい数式:「=F10*F16

「$」は、コピーしたときだけ影響するもの。計算式としては同じものです。

さて、この結果は正しいですか?それても、修正が必要ですか??

はい、正解だと確認できましたね!! これでやっと正解です!!

④もし間違っていたら、必ず最初のセルに戻って修正

いまの例では、いっぱつで正解にたどりつきましたが、

途中の直し方や、問題によっては、ここでまた正しくない場合もあります。

そのときは、あわてて途中のセルをなおしたりしないで、

必ず、

  • あわてずに一番最初の【C17】セルにもどって修正し、
  • 再び↓下方向、→右方向、両方にコピーする

という手順を踏んでください。

再修正はかならず最初のセルを修正して、再度下、右方向にコピーしてください

(スマホの方はタップすると拡大できます。戻るときは「戻る」ボタンで戻ってください)

この手順をきちんと踏めば、かならず正解できます!

いかがでしたか? 問題によってひとつひとつ、状況が違うため、「決まった解き方」がなかなか見つけにくいのですが、

このように「試しにコピーして、直して、またコピー」という手順をきちんと踏めば、時間はかかっても必ず正解できます。

さらに、この手順でくり返し練習していくことによって、いずれ、このような手順を踏まなくても、正解が考え出せるようになります。

ぜひ、お試しください!

本記事で使ったファイルのダウンロード

本記事で使った例題ファイルを、下のリンクからダウンロードできます。

応用問題:「九九の表」

この記事で紹介した方法の「腕試し」にちょうどよいのが

「九九の表」です。

「九九の表」で複合参照の練習

MOSにはこんな問題は出ませんが、試しに取り組んでみてください! きっと要領がつかめます!

 

キュリオステーション志木店
  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

キュリオステーションなら、MOS(マイクロソフトオフィススペシャリスト)資格対策も格安・安心!

キュリオステーション志木店のMOS対策コースは、【定額制】【時間制】が選べます。

【定額制】コースは、MOS 1科目につき30,000円~(税別)の圧倒的な格安料金。しかも、じっくり復習して時間数がのびても、追加料金は月々3,000円(税別)のみ。

納得いくまでレッスンを受けて、確実に合格をめざすことができます。

【時間制】コースも、1時間1,200円(税別)~の格安料金。入学金などの別途料金もありません。

就職・転職のためにMOSを取りたい!という方、「他教室で習ったけど、落ちてしまった……」という方、ぜひ当教室のMOS講座、体験してみて下さい!

キュリオステーション志木店のMOS対策講座はこちら

SNSでもご購読できます。

コメントを残す

*