検 索 の 型 が FALSE 又 は 0 の 場 合 は 左 端 を 昇 順 に 並 べ 替 えておく 必 要 はありません が 該 当 する 値 がない 場 合 は エラーになります 旅 行 命 令 書 など 名 前 から 職 名 や 住 所 を VLOOKUP 関 数 で 取 得 する 場 合

Transcription

1 VLOOKUP 関 数 VLOOKUP 関 数 は 最 もポピュラーで 使 用 頻 度 の 高 い 検 索 / 行 列 関 数 です 構 文 は 次 のとおりです =VLOOKUP( 検 索 値, 範 囲, 列 番 号,[ 検 索 の 型 ]) 範 囲 の 左 端 の 列 が 検 索 値 と 合 致 する 行 の 列 番 号 のセルの 値 を 返 します =VLOOKUP(C1,F1:I12,3) 下 図 では 左 端 (F 列 )が 5 である 行 の 3 列 目 のセルの 値 を 返 しています VLOOKUP 関 数 は 検 索 の 型 で 異 なる 動 作 をします 検 索 の 型 が 省 略 されている 場 合 ( 上 図 )や TRUE または 1 の 場 合 は 左 端 の 列 が 昇 順 で 並 べ 替 えられていることが 必 要 です この 場 合 検 索 値 が 左 列 の 最 大 値 を 超 えている 場 合 は 最 大 値 の 行 の 値 を 返 します 左 端 の 列 が 昇 順 で 並 べ 替 えられていない 場 合 は 正 しい 値 を 返 さないことがあります 1

2 検 索 の 型 が FALSE 又 は 0 の 場 合 は 左 端 を 昇 順 に 並 べ 替 えておく 必 要 はありません が 該 当 する 値 がない 場 合 は エラーになります 旅 行 命 令 書 など 名 前 から 職 名 や 住 所 を VLOOKUP 関 数 で 取 得 する 場 合 は 必 ず 4 番 目 の 引 数 を FALSE 又 は 0 にします =VLOOKUP($L$13, 職 員 データ,2,FALSE) 学 校 事 務 では =VLOOKUP( 検 索 値, 範 囲, 列 番 号,FALSE) =VLOOKUP( 検 索 値, 範 囲, 列 番 号,0) と 使 うものだと 考 えておいても 良 いかもしれません 2

3 HLOOKUP 関 数 VLOOKUP 関 数 が 範 囲 の 左 端 の 列 を 検 索 するのに 対 し HLOOKUP 関 数 は 範 囲 の 上 端 の 行 を 検 索 します 範 囲 の 上 端 の 行 が 検 索 値 に 合 致 する 列 の 行 番 号 のセルの 値 を 返 します HLOOKUP( 検 索 値, 範 囲, 行 番 号,[ 検 索 の 型 ]) =HLOOKUP(C1,F1:Q3,3) 下 図 では 上 端 (1 行 )が 8 である 列 の 3 行 目 のセルの 値 を 返 しています 検 索 の 型 は VLOOKUP 関 数 と 同 様 です HLOOKUP 関 数 が 使 われる 機 会 は それほどありません というのも 表 はデーターベース 的 ( 左 端 に 排 他 的 な ID を 持 たせる)に 作 ったほうが 利 用 価 値 が 高 いからです 非 常 勤 勤 務 状 況 報 告 書 の 例 下 図 左 の 表 で 時 間 数 を 管 理 しているため HLOOKUP 関 数 で 該 当 月 の 時 数 を 取 得 します =HLOOKUP(A20, 勤 務 データ!$B$1:$C$13,IF(MONTH( 報 告 書!$A$10)<4,MONTH( 報 告 書!$A$10)+ 9,MONTH( 報 告 書!$A$10)-2),0) A10 には 作 成 月 の 初 日 の 日 付 を 入 れています 3

4 CHOOSE 関 数 CHOOSE 関 数 は 値 のリストからインデックス 番 目 の 値 を 取 り 出 します =CHOOSE(インデックス, 値 1,[ 値 2],...) インデックスの 小 数 部 分 は 無 視 されます また インデックスが 1 以 下 の 場 合 や 値 の 個 数 を 超 えると エラー 値 (#VALU E!)になります 値 は 最 大 254 個 の 数 値 セル 範 囲 名 前 数 式 関 数 または 文 字 列 が 指 定 できます セル 範 囲 が 引 数 の 場 合 単 一 のセルならセルの 値 を 返 します 複 数 セルのセル 範 囲 の 場 合 は エラー(#VALUE! 引 数 の 種 類 が 正 しくない)になります が SUM 関 数 などの 引 数 に すれば 正 しく 機 能 します 1 子 2 子 で 会 費 が 異 なる 場 合 など 一 見 IF 関 数 で 処 理 したくなりますが 1 子 2 子 をコード 化 すれば CHOOSE 関 数 で 簡 単 に 取 り 出 すことができます =CHOOSE(B2,300,150,0) 1 子 は 300 円 2 子 は 150 円 3 子 以 降 は 0 円 である 場 合 それぞれを 1,2,3 とコード 化 するだけの 話 です 4

5 MATCH 関 数 MATCH 関 数 は 検 査 範 囲 内 で 検 査 値 を 検 索 し その 相 対 的 な 位 置 を 数 値 で 返 します =MATCH( 検 査 値, 検 査 範 囲,[ 照 合 の 型 ]) 検 査 値 1 または 省 略 検 査 値 以 下 の 最 大 の 値 が 検 索 されます このとき 検 査 範 囲 のデータは 昇 順 に 並 べ 替 えておく 必 要 があり ます 0 検 査 値 に 一 致 する 値 のみが 検 索 の 対 象 となります このとき 検 査 範 囲 を 並 べ 替 えておく 必 要 はありませ ん -1 検 査 値 以 上 の 最 小 の 値 が 検 索 されます このとき 検 査 範 囲 のデータは 降 順 に 並 べ 替 えておく 必 要 があり ます =MATCH(D2,$B$2:$B$14,0) 下 図 では 宮 崎 県 教 育 研 修 センター が 検 査 範 囲 の 5 番 目 であることを 返 しています また 照 合 の 型 が 0 の 場 合 一 致 するものがない 場 合 MATCH 関 数 は エラーを 返 します 次 の 例 は IF 関 数 で MATCH 関 数 がエラーであるかどうかを 判 断 し 一 致 するものがあった 場 合 のみ 該 当 を 表 示 しま す =IF(ISERROR(MATCH(D2,$I$1:$U$1,0)),""," 該 当 ") これは 年 齢 から 指 定 年 齢 であるかどうかを 判 断 する 式 です 年 齢 は DATEDIF 関 数 を 使 用 しています =DATEDIF(C2,DATE(2016,4,1),"y") 5

6 また MATCH 関 数 は INDEX 関 数 の 引 数 とし 動 的 に 値 を 返 す 場 合 にも 使 用 されます 次 の 例 では 会 場 名 の 上 のセルに 市 町 村 名 を 返 します =INDEX($B$2:$C$14,MATCH(D2,$B$2:$B$14,0),2) ただし これは VLOOKUP 関 数 を 使 用 する 方 が 簡 単 です =VLOOKUP($D$2,$B$2:$C$14,2,0) VLOOKUP 関 数 は 2002 以 降 の 関 数 であり 上 で 紹 介 した INDEX 関 数 の 使 用 例 は Excel97 や 2000 では 必 須 でした 従 って 2010 や 2013 といったバージョンの Excel が 多 く 使 われている 現 状 では INDEX 関 数 と MATCH 関 数 の 組 み 合 わせを 使 うことはないでしょう 6

7 INDIRECT 関 数 INDIRECT 関 数 は 参 照 文 字 列 で 示 されるセルやセル 範 囲 の 参 照 を 返 します =INDIRECT( 参 照 文 字 列,[ 参 照 形 式 ]) 参 照 文 字 列 は セルの 参 照 を 表 す 文 字 列 や 定 義 された 名 前 です 参 照 形 式 は 省 略 可 です A1 形 式 でセルを 参 照 している 限 り 必 要 のない 引 数 です 引 数 は セルのアドレスを 表 す 文 字 列 なので 必 ず で 挟 みます =INDIRECT("A5") 上 の 例 は 次 の 式 と 同 じです =A5 これでは わざわざ INDIRECT 関 数 を 使 う 必 要 はありません 実 際 は 動 的 に 参 照 を 変 更 する 場 合 に INDIRECT 関 数 は 使 用 されます もっとも 有 名 なのが 入 力 規 則 のリストを 絞 り 込 む 場 合 です 市 町 村 名 で 会 場 のリストを 絞 り 込 みます まず 市 町 村 ごとに 名 前 を 定 義 します 日 向 市 A2:A10 の 範 囲 延 岡 市 B2:B10 の 範 囲 門 川 町 C2:C10 の 範 囲 宮 崎 市 D2:D9 の 範 囲 F2 に 元 の 値 を=$A$1:$D$1 として 入 力 規 則 のリストを 設 定 します 7

8 G2 に 元 の 値 を 次 の 数 式 として 入 力 規 則 のリストを 設 定 します =INDIRECT($F$2) リストで 市 町 村 名 を 切 り 替 えると 同 じ 定 義 された 名 前 を 持 つ 範 囲 に 参 照 が 切 り 替 わります 宮 崎 市 で 絞 り 込 んだ 場 合 INDIRECT 関 数 は 次 と 同 じ 動 作 をします 8

9 OFFSET 関 数 OFFSET 関 数 は セルまたはセル 範 囲 から 指 定 された 行 数 と 列 数 だけシフトした 位 置 にあるセル 範 囲 の 参 照 を 返 します =OFFSET( 基 準, 行 数, 列 数,[ 高 さ],[ 幅 ]) 高 さと 幅 は 省 略 できます そこで 基 準 と 行 数 列 数 のみを 指 定 してみます =OFFSET(A1,5,3) 下 図 では この 式 は 13 を 返 します =D6 と 同 じです A1 から 下 に +5 右 に +3 のセルということです 下 に +3 右 に +1 のセル B4 は 次 の 式 で 返 します =OFFSET(A1,3,1) これに 高 さ 2 と 幅 2 を 指 定 します =OFFSET(A1,3,1,2,2) 複 数 セルの 参 照 となるため エラーとなります そこで SUM 関 数 の 引 数 にします =SUM(OFFSET(A1,3,1,2,2)) B4 から 高 さ 2 幅 2 のセル 範 囲 B4:C5 の 合 計 が 返 されます OFFSET 関 数 は あまり 使 う 機 会 のない 関 数 の 一 つですが 増 減 するセル 範 囲 の 自 動 取 得 は 必 須 のテクニックです 9

10 OFFSET 関 数 の 定 番 1 入 力 規 則 リストの 元 の 値 を 単 に セル 範 囲 にしておくと データが 増 えた 場 合 は 範 囲 を 変 更 しなければなりません =$A$1:$A$8 OFFSET 関 数 を 使 用 すると 変 動 するセル 範 囲 を 取 得 できるため データの 増 加 に 自 動 的 に 対 応 できます =OFFSET( 基 準, 行 数, 列 数,[ 高 さ],[ 幅 ]) データの 増 加 は [ 高 さ]を COUNTA 関 数 で 取 得 します 行 数 と 列 数 は,, で 省 略 できます =OFFSET(A1,,,COUNTA(A:A),1) ここで A1 や 1 行 目 を 削 除 すると OFFSET 関 数 がエラーになり リストが 機 能 しません これには INDIRECT 関 数 で A1 を 参 照 することで 対 応 できます =OFFSET(INDIRECT("A1"),,,COUNTA(A:A),1) A1 を 削 除 しても エラーになりません また 数 式 で 値 を 返 しているセル 範 囲 では COUNTA 関 数 のみでは 高 さを 取 得 できません =IFERROR(INDIRECT("A" & SMALL(C:C,ROW())),"") このような 場 合 は 数 式 が 設 定 されている 行 数 から COUNTBLANK 関 数 で 空 白 行 数 を 差 し 引 くことで 高 さを 得 ることが できます =OFFSET(E1,,,20-COUNTBLANK(E1:E20),1) 10

11 OFFSET 関 数 の 定 番 2 名 前 の 定 義 OFFSET 関 数 で 可 変 する 名 前 の 定 義 範 囲 を 自 動 で 取 得 します =OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A:$A),5) ここでは A 列 の 数 値 をカウントするため [ 高 さ]は COUNT 関 数 で 取 得 できます 次 の 例 は VLOOKUP 関 数 の 引 数 に 職 員 データ と 定 義 された 名 前 を 使 用 しています =VLOOKUP($A$2, 職 員 データ,COLUMN()) データを 追 加 します 正 しく 反 映 します ただし OFFSET 関 数 を 使 用 した 名 前 は 名 前 ボックスには 表 示 されません 11

12 COLUMN ROW 関 数 COLUMN 関 数 は 単 一 のセルを 参 照 している 場 合 はそのセルの 列 番 号 を セル 範 囲 を 参 照 している 場 合 は 一 番 左 の 列 番 号 を 引 数 が 省 略 されている 場 合 は 関 数 が 入 力 されているセルの 列 番 号 を 返 します =COLUMN([ 範 囲 ]) 下 図 では A2 以 降 同 じ 数 式 が 入 っています A1 の 日 付 を 変 えると 自 動 でそれに 続 く 日 付 を 得 ることができます =$A$1+COLUMN()-1 このように COLUMN 関 数 を 連 続 で 増 加 する 変 数 として 使 うことができます ROW 関 数 は 単 一 のセルを 参 照 している 場 合 はそのセルの 行 番 号 を セル 範 囲 を 参 照 している 場 合 は 一 番 上 の 行 番 号 を 引 数 が 省 略 されている 場 合 は 関 数 が 入 力 されているセルの 行 番 号 を 返 します =ROW([ 範 囲 ]) あまり 使 うことのない 関 数 ですが 同 じ 数 式 をコピーする 場 合 など 増 加 値 をこれらの 関 数 で 置 き 換 えることができます =VLOOKUP($A$2, 職 員 データ,COLUMN()) ROW 関 数 の 定 番 条 件 付 き 書 式 で 1 行 おきに 塗 りつぶします =1 は 奇 数 行 を =0 は 偶 数 行 を 塗 りつぶします =MOD(ROW(),2)=1 5 行 おきに 塗 りつぶします =MOD(ROW(),5)=1 12