相対参照と絶対参照 付録 2 入力した数式を下のセルへコピーすると、表示がおかし くなるときがあります。下の図 A ではセル C4 に「単価 x 個数」を求める「 = BI * B4 」という数式を入力して、数 式をセル C5 、セル C6 へコピーしています。 セル C5 には「 0 」と表示されています。数式バーを確 認すると「 = B2 * B5 」となっていることがわかります。 「 = BI * B5 」となっていないため、正しく計算できていま せん。 図 A 05 1 2 3 4 6 。 A 500 6 2 月 1 0 2 1 0 日 5 2500 0 2 月 1 0 3 #VALLJ 曰 セル C4 の数式をセル C5 に コピーすると、「 = B2 * B5 」 となってしまう = 日 2 * 日 5 数式をコピーすると、セル C4 に入力した参照先「 BI 」 「 B4 」のセル位置は、 1 つ下のセルとなるセル C5 では 1 つずっ下にずれ、「 B2 」「 B5 」を参照します。このように 数式のセル位置に合わせて、入力したセル番地も変化する 参照方法を相対参照と呼びます。 セル C4 の数式をセル C5 にコピーしても「 = BI * B5 」 となるようにするには「 BI 」が変化しないように入力す る必要があります。参照先のセル番地が変化しないように 固定してしまう参照方法を絶対参照と呼びます。 ここでは、セル C4 の数式を編集して「 BI 」を相対参照 から絶対参照に切り替える手順を説明します。 260

数式をコピーして他の年代も求める セル F4 に「 20 代男性」の平均購入金額が表示されまし たので、数式をコピーして他の年代の平均購入金額も求め てみます。セル F4 を選択し、ハンドルをドラッグしてセ ル F3 とセル F5 ~ F7 へ数式をコピーします。 =AVERAGEIFS($C$3 $C$1 4. $ 3 : $ 14. $ 2 , $ 日 $ 3 $ 日 $ 14. 朝 L = 。町三 0 L 、、上朝 1 来店者記録 平均購人、 . 額 性別齡購入金額 2 3 女 10 代 . 、き 22 3500 4 51 4 300 20 30 代 3 日 755 〕 40 代 6 24 6 0 〔旧 50 は、 435 〕 8 女 1 55 〕 1 6 1 日 33 〔旧 1 0 男 41 2500 55 3 500 20 1 00 43 9 00 1 4 23 1 00 セル F4 を選択して、 ハンドルをドラッグ する =AVERAG 日田($0$3$0$14 , $ 23 : $ 14. $ 2 , $ 日 $ 3 $ 日 $ 14. 平均購入金額 1 0 イは、 3 500 20 代 4 300 30 イ 7 500 40 代 6 80 50 弋以上 4 300 1 500 3 300 2 500 コピーされた数式は、どれも「 20 代男性」の平均購入 金額が表示されたままです。年代別に異なる条件を設定す る論理式は、それぞれ修整する必要があります。本書の COUNTIFS 関数の説明 ( 96 ~ 97 ページ ) でも行いまし たが、コピーされた数式のセルをダブルクリックして、各 年代別に 1 っ 1 つ編集します。修整した結果は、それぞれ 次のようになります。 つ」 1 , 03 4 ・ ( 0 ( 0 〔 0 1 ・ 記 店女男男女女男男 1 ~ り三 3 ( 4 5 6 フ 00 」 ( リ 3900 3900 三 114

コピーした数式はどのようになっているでしようか。た とえばセル D4 を選択して数式バーを確認すると、引数と して指定した端数を切り捨てるセルの位置が「 C4 」にな っていることがわかります ( 図 B) 。つまり、セル D4 の 数式がコピー元のセル位置から 1 っ下に移動した分、引数 で参照先となるセル位置も変化しています。 =ROIJNDDOWN(04,O) D4 N ( 04 , 0 ) x 判引 1 702 日 21 日日 .8 1 91 7 3 901 6 端丿て 520 1 9 日 0 3040 2310 9B0 0.92 0.86 0.72 092 数式のセル位置の変化にあわせて、 参照先となるセル位置も変化する 47 日 1 702 21 日日 1 91 7 901 このように、数式のセル位置の変化にあわせて引数のセ ル位置が変化する参照方法を「相対参照」と呼びます。 これに対して、数式をコピーしても引数のセル位置が変 化しない参照方法を「絶対参照」と呼びます。この「絶対 参照」については、 3 章で使用しますので、その際に説明 します。 ここでの例のように、入力した数式を下向きにコピーす る方法は、 Excel 関数を使用するときの常套手段です。何 度も同じ入力を繰り返さないでも済むことが、なんとなく おわかりいただけるのではないでしようか。この方法は、 本書でもこれ以降何度も登場します。 42

3 章データの集計に役立つ関数 数式をコピーして「女」の来店者数を求める 入力した数式をコピーします。セル F2 を選択し、ハン ドルを下向きにセル F3 までドラッグします。 = COLJNT 旧 $ 23 : $ 214 ′ ) 1 店者記録 店者 2 3 4 5 6 7 8 9 1 0 1 3 1 5 1 0 1 7 1 9 20 1 7 - 5 0 ) 0 4 300 1 5 〔 0 1 2 1 00 25 000 1 5 200 日 05 〕 25 〔 3 3 ( 9 8m 1065 ) 19 日〔旧 LO 0 」 ( 0 4 ( 0 ( 0 に 7 ・つリり」 ( 0 セル F2 を選択して、 ハンドルをドラッグ する 数式がコピーされ、セル F3 に「女」の来店者数が表示 されます。 店者記 = COIJNT 旧 $ 3 ・ $ 1 4 , 田 ) 7 5 5 〕 0 438 ー 15000 1 2 1 00 25 000 1 5 00 2 5CO 3 9 8CC) 1 0 6CK.) 1 9 8 〔 「女」の来店者数が 表示される 89

数式をコピーして他の日の作業時間を表示する 入力した数式をコピーします。セル D4 を選択し、ハン ドルを下向きにセル D6 までドラッグします。 = I 04 く日 4. C4 + 1 一日 4.04 一日 4 ) D4 1 作業時間記録 2 時刻退時第時 3 22 : 00 5 21 ・ 50 6 950 7 8 9 セル D4 を選択して、 ハンドルをドラッグ する 3 ( ま 320 : 1820 セル D5 ~ D6 に、他の日の作業時間が表示されます。 「時刻」の表示形式もコピーされています。 = IF ( 04 く日 4 , 04 + 1 一日 4 , 04 一日 4 ) D4 1 作業時間記録 他の作業時間が表示される 2 時亥退時刻 3 4 22 : 00 3 : 00 5 21 ・ 50 320 9 ・ 50 1 日 20 7 8 9 1 2 5 : 00 5 : 30 : 30 198

2010 年 5 月 1 日は土曜日なので、セル D4 に「休」が 表示されます。これは、「セル C4 が 0 より大きい」とい う条件が満たされないときの結果です。後に数式をコピー するとわかりますが、平日の場合は空欄となります。 D5 年 「休」が表示される 元日 2010 / 1 / 1 成人の日 2010 / 1 / 1 1 建国記念の日 2010 / 2 / 1 1 春分の日 2010 / 3 / 21 振替休日 2010 / 3 / 22 昭和の日 2010 / 4 / 29 憲法記念日 2010 / 5 / 3 みどりの日 2010 / 5 / 4 こどもの日 2010 / 5 / 5 海の日 2010 / 7 / 19 敬老の日 2010 / 9 / 20 秋分の日 2010 / 9 / 23 体育の日 2010 / 10 / 11 文化の日 2010 / 11 / 3 勤労感謝の日 2010 / 1 1 / 23 天皇誕生日 2010 / 12 / 23 数式をコピーする 入力した DATE 関数、 NETWORKDAYS 関数、 IF 関 数の数式をコピーします。セル B4 ~ D4 を選択して、ハ ンドルを下向きにドラッグします。 222

6 章日付と時刻の処理に役立つ関数 数式をコピーして他の有効期限の日付を表示する 入力した数式をコピーします。セル C4 を選択し、ハン ドルを下向きにセル CI 1 までドラッグします。 =EDATE(84,1 ) C4 販促サービス有効期限一覧 3 顧客 ID 晴 . 有勅期阯 . 生 201 0 / 1 / 1 5 ま C12351 0 201 0 / 1 / 30 5 0123511 201 0 / 2 / 5 6 OI 2351 2 201 0 / 2 / 1 0 7 0123513 201 0 / 3 / 5 # 日 01 23514 201 0 / 3 / 1 9 01 2351 5 201 0 / 3 / 1 1 0 01 23516 201 0 / 3 / 21 1 1 CI 2351 7 1 2 1 3 1 4 1 6 17 1 セル C4 を選択して、 ハンドルをドラッグ する セル C5 ~ CI 1 に、他の有効期限の日付が表示されま す。シリアル値で表示されていないため、「日付」の表示 形式もコピーされていることがわかります。 =EDATE(841 ) 04 1 販促サービス有効期限一 2 3 顧客 ID 購入 201 0 / 1 / 1 5 201 0 / 2 / 1 5 4 01 23510 5 。。 012351 1 201 0 / 1 / 30 201 0 / 2 / 2 日 201 0 / 2 / 5 2010 / 3 / 5 6 。 012351 2 201 0 / 3 / 1 0 7 201 0 / 2 / 1 0 012351 3 201 0 / 3 / 5 201 0 / 4 / 5 日 C12351 4 9 。 CI 23515 201 0 / 4 / 1 2 201 0 / 3 / 1 2 201 0 / 4 / 1 日 201 0 / 3 / 1 8 1 0 01 23516 201 0 / 3 / 21 1 1 CI 2351 7 1 2 1 3 14 1 7 他の日付が表示される 191

数式をコピーして他の日の作業時間を表示する 入力した数式をコピーします。セル E7 ~ G7 を選択し、 ハンドルを下向きにセル G9 までドラッグします。 = D7 ー 07 勤務表 氏名 時 ¥ 1 000 時刻退社時刻聞鰔導位旦 日 : 55 1 3 : 3 匪 ¥ 4 500 1 4 ・ 50 ま 日 : 30 1 4 ・ 4 ま 9 : 1 5 数式がコピーされ、すでに「出社時刻」と「退社時刻」 が入力されている日の給与が表示されます。 = D7 ー 07 他の日の給与が表示される 勤務表 氏名 町 , 201 0 年 時給 ¥ 1 000 寰リ 1 2 ~ 07 4 5 : 0 一 7 0> セル E7 ~ G7 を選択して、 ハンドルをドラッグする 時刻、 時刻 日 : 55 1 3 : 30 日 : 30 1 4 : 50 9 : 1 5 14 ・ 45 2 金 3 土 ここで紹介した FLOOR 関数と逆に、基準値で切り上げ るのが CE 旧 NG 関数です。関数の使い方は、 FLOOR 関 数と同じです。ここでの例で CEILING 関数を使うと、 「 4 : 35 」が「 5 」となります。 4 5 ¥ 4 500 ¥ 6 000 5 5 5 5 4 : 35 620 5 30 1 0 206

付録関数の入力に役立つ豆知識 セ榔す ②を AVE RAGE = 日 1 * 日 4 500 0 月 2 -4 一 5 日 4 日 1 ・ 3 望キーを 1 回押す 日 4 日 1 3 = $ 日 $ 1 * 日 4 ④「 $ B $ 1 」となる AVERAGE 0 月 単 500 5 Ente 「 日 4 これで数式をコピーしても「 BI 」が変化しない絶対参 照になります。絶対参照にすると、セル番地を表す列 ( 「 B 」 ) と行 ( 「 1 」 ) の前に「 $ 」が入力されます ( ※ ) 。 セル C4 の数式をコピーすると正しい計算結果が表示さ れます。セル C5 は「 = $ B $ 1 * B5 」となってます ( 図 B)O = $ 日 $ 1 * 日 5 1 単価 500 3 4 2 月 1 0 日 5 5 。 2 月 1 0 日 6 6 2 1 0 3 日 4 日 $ い 05 図 B セル C5 は「 = $ B $ I * B5 」 2500 となる 3000 1 500 セルの参照方法は、下表の 4 種類があります。キーを 押すたびに、参照方法が順番に切り替わります。 AI 数式をコピー / 移動すると、その分だけ数式 相対参照 中のセル番地が変化する 数式をコピー / 移動しても、数式中のセル番 絶対参照 $A$I 地は変化しない 列番地は変化するが、行番地は変化しない 複合参照 ( 行固定 ) A$I 列番地は変化しないが、行番地は変化する 複合参照 ( 列固定 ) $AI ※固定したいほうの番号に「 $ 」を付けます。なお、絶対参 照や複合参照でも、参照先のセルが移動したときは、それに 合わせて変更されます。 261

2 章必ず押さえておきたい基本の関数 数式をコピーする 入力した数式は、他のセルへコピーして使い回すことが できます。セル D3 を選択し、ハンドルを下向 D7 までドラッグします。 D3 =ROlJNDDOWN(C3,O) x 宝気値 ( 端秋切てし 47 宀価割引率宀 47 日 1 702 21 日日 . 匪 1917. 匪 901 .6 芸 0.92 0 日 6 072 0.83 0.92 520 1 980 3040 2310 9 日 0 セル D3 を選択して、 ハンドルをドラックする 、 0 1 2 ~ ( 9 4 、 0 ~ 7 日 すると、数式がコピーされます。他の数値の小数点以下 1 円単位で表示されます。 の端数も切り捨てられ、 =ROUNDDOWN(C3,O) D3 ィーのこ 3L4-- 0 0 ~ -1 亠 8 りて x 判引率 4784 1702 日 21 8 日 8 1 91 7.3 901 .6 宀価割引率宀 0.92 0B6 072 0B3 0.92 520 1 9 日 0 3040 231 0 9B0 1 702 21 88 1 91 7 小数点以下が切り捨てられ I 円単位で表示される