【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」
と、修正します。
修正方法は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にはこんな問題は出ませんが、試しに取り組んでみてください! きっと要領がつかめます!
コメント