キャリカク一宮駅前

こんにちは、キャリカク一宮駅前の利用者「ファーストネームが K」 です。

今回はドル記号 $ でオートフィルを制御する方法を紹介するよ！

電卓スプレッド　履歴と予定
～電卓から始まる編～
(1) はじまり・はじまり～👏
(2) 範囲で計算SUM系関数
(3) オートフィルで魔方陣
(4) ドル記号$で九九表 ← 今回
～自分用業務アプリ編～
(5) CONCATENATEで市場調査
(6) PROPER、SPLIT、TEXTJOINで名刺作成
(7) SUBSTITUTEで定型文作成
(8) XLOOKUPで業務管理

第3回で紹介したオートフィルはとても便利ですが、技術的なことを言えば、オートフィルを可能にしているのが相対参照・絶対参照という「セルの指定の仕方・技術」です。だんだん難しくなってくるので、本題に入る前にお題目だけ伝授しておきます😋–

オートフィルが上手くいかないときは、ドル記号 $ を使って、列か行か、それとも両方を固定してみよう！

用語としては「複合参照」というのもありますが、頭がパンクしそうなので、MOSなどの資格取得を考えている人以外は頭の片隅にでも入れておくだけで大丈夫です。困ったときは上のお題目を思い出してください😉

🔰👩‍🏫記事中の数式入力例をGoogleスプレッドシートで試したい人は、Googleドライブの「マイドライブ」フォルダに移動して [新規作成]→[Googleスプレッドシート] で自分専用のスプレッドシートで試すことができます。

⚠👨‍🏫記事中の数式入力例はGoogleスプレッドシートでしか試していませんが、マイクロソフトExcelでも似たような感じでできるはずです。

ステップ1 九九表をオートフィルで作る

小学校時代を思い出して九九表を作ってみましょう。当時と違って81個全部覚えて書き出すのではなく、スプレッドシートの関数（掛け算「*」）とオートフィルを使って半自動的に書き出してみましょう！

でも、第3回でやったやり方だけでは上手くいきません。ドル記号 $ を上手に使う必要があります。とりあえず次の表を、「九九表」と書かれたセルが A1 の位置に来るようにコピペするか打ち込んでください。

記事の見やすさのために数字が1～4までしか書かれていませんが、適宜9まで埋めてください・・・え？ めんどくさい⁈・・・そんな人こそ関数に向いています😋 だって、めんどうな手間を省くために使うのが関数ですから👍

そこで、SEQUENCE という関数を使って1～9まで入力する手間を省きましょう。だまされたと思って、次の2行2列の4マスだけを「九九表」と書かれたセルが A1 の位置に来るようにコピペするか打ち込んでください（SEQUENCE 関数の詳細は後述します）。

これは、列B が1の段、列C が2の段・・・列J が9の段の九九表になる予定です。「初手」として、「$1\times1$」 の位置にあたるセル B2 に =B$1*$A2 という掛け算の式を入力しておきました。この式はドル記号 $ がなければおなじみの掛け算 =B1*A2 です。しかしオートフィルで九九表を作ろうとすると、このドル記号 $ がないと上手くいきません。

では、セル B2 のフィルハンドルを列方向（縦方向）と行方向（横方向）にドラッグしてオートフィルしてみてください（フィルハンドルとオートフィルについては前回のステップ3を参照してください）。

下の図のように九九表になるはずです（下の図ではちょっと装飾しています）。

ドル記号 $ が何か気になるところですが、まずは自分で作った九九表が正しいかどうか確認してください。81個全部確認するのではなく、次のようなサンプリングテストで確認してはいかがでしょうか。

👍まずは自信のある段（私なら1の段、2の段、5の段）を確認してください。

✌ 9の段は、1の位が9、8、7… と一つずつ減っていき、10の位が1、2、3… と一つずつ増えていくはずなので、それを確認してください。

👌 あとは、例えば「$4\times7$」とそれを逆にした「$7\times4$」の計算結果が同じになっているか、というようなことを数箇所確認してください。

⚠👨‍🏫SEQUENCE 関数は数列（シーケンス）を生成します（MOSの教科書にも載ってるよ）。
・SEQUENCE(9) は縦方向（列方向）に1、2、…、9を生成します。
・SEQUENCE(1,9) は1行9列の数列を、つまり横方向（行方向）に1、2、…、9を生成します。

ステップ2 九九表をちゃんと失敗する

相対参照とか絶対参照とかを、理解するのではなく、使いこなせるようになるための手っ取り早い方法は、ちゃんと失敗してみることです。

ステップ2では、ステップ1と同じような九九表を作ってみるのですが、「初手」の式 =B$1*$A2 をドル記号 $ のない普通の式 =B1*A2 にして、ちゃんと失敗してみましょう。

さっきと同じように「九九表」と書かれたセルが A1 の位置に来るようにコピペするか打ち込んでください。

まずは、セル B2 のフィルハンドルを列方向（縦方向）にドラッグして、1の段をオートフィルしてみましょう。下の図は途中までですが、すでにこれが失敗だとわかります。

何が悪かったのかは、とりあえず、「1×3」のつもりのセル B4 にどんな式がオートフィルされたか確認すればわかります（数式の確認の仕方は第1回のステップ5を参照してください）。

初手で「$1\times1$」を計算するために =B1*A2 を入力したんだから、「$1\times3$」を計算するためには =B1*A4 となってほしいところですが、オートフィルの結果はいちばん上の B1 ではなく一つすぐ上の B3 つまり「$1\times2$」の計算結果を参照してしまっています。

ステップ3 相対参照とオートフィル

ステップ2での失敗は、オートフィル機能があんぽんたんだからではありません。フィルハンドルを列方向（縦方向）に動かせば行の番号が1、2、3… と一つずつ勝手に増えていくからこそのオートフィルなのですからね。

いまの九九表の例では「初手」を間違えたから上手くいきませんでしたが、このようなオートフィルを可能にするようなセルの指定の仕方を相対参照と言います。相対参照というのは要するに、フィルハンドルを列方向（縦方向）に動かせば行番号を一つずつずらしていき、行方向（横方向）に動かせば列番号をA、B、C… と一つずつずらしていくっていう仕組みのことを言っているにすぎません。

どちらかといえば、相対参照は「絶対参照じゃない」っていう意味です。つまり、フィルハンドルを動かしても行番号または列番号が増えない仕組みもあって、それを絶対参照とか複合参照とか言います。ステップ1で上手くいったのは、そんな仕組みを使ったからです。

ここでもう一度、冒頭のお題目を思い出しましょう。

オートフィルが上手くいかないときは、ドル記号 $ を使って、列か行か、それとも両方を固定してみよう！

ステップ1での「初手」の式 =B$1*$A2 の B$1 は、行番号の「1」を固定してオートフィルしてね、っていう意図を表しています。$A2 は、列番号の「A」を固定してオートフィルしてね、っていう意図を表しています。

九九表を作るためのオートフィルは意外と複雑です。もう1回くらい、ちゃんと失敗しておいたほうがよさそうです😋

ステップ4 行番号だけ固定して、ちゃんと失敗する

ステップ2の失敗は、1の段を計算するために B1 のセルを固定したままオートフィルしたい、っていう意図が伝わらなかったことが原因です。

その、1の段の失敗（だけ）をカバーするには、B$1 というふうに行番号「1」だけ固定する意図をオートフィル機能に伝えればいいはずです。

とりあえず、失敗を恐れず😋、下の表を「九九表」と書かれたセルが A1 の位置に来るようにコピペするか打ち込んでください。

セル B2 のフィルハンドルを列方向（縦方向）にドラッグすれば、1の段は上手くいきます（下の図では途中までですが、「$1\times9$」まで全部上手くいきます）。

では、他の段も上手くいくかどうか、行方向（横方向）にオートフィルしてみてください。次の図のように、2の段までならなんとか上手くいきますが、3の段以降は上手くいかないはずです（「$3\times3$」が9ではなく18になってる！）。

ちゃんと失敗した？

ちなみに、列番号「B」と行番号「1」の両方を固定するには $B$1 と書きます。もし「初手」を =$B$1*A2 としてオートフィルするとどうなるでしょうか？（答え：全部の列が1の段になってしまいます）

ステップ5 ファンクションキーF4

ドル記号 $ を使ったオートフィルは、実のところ、失敗して試行錯誤しながら、そしてステップ1でやったようなサンプリングテストでいちいち確認しながらするのが普通だと思います。

だから、ドル記号 $ の入力を手助けしてくれる機能がGoogleスプレッドシートにもExcelにも備わっています。

下の図のように、セルを参照する B1 などの式にカーソルを持っていき、ファンクションキーの「F4」を押すとドル記号 $ を補ってくれます。4回押すと元に戻ります。

ネクストステップ

次回からは「自分用業務アプリ編」と題して、私がキャリカクでの作業でどんなふうにスプレッドシート関数を使っているのかを紹介していくつもりです❣

今後の予定：
・CONCATENATEで市場調査
・PROPER、SPLIT、TEXTJOINで名刺作成
・SUBSTITUTEで定型文作成
・XLOOKUPで業務管理

就労継続支援B型

キャリカク一宮駅前

愛知県一宮市大江3丁目12-22　一観ビル1階

電話番号：0586-71-3577　FAX：0586-71-3587