1. 表 から 値 を 抽 出 する 説 明 1.1. 表 から 値 を 抽 出 するための 関 数 について 説 明 します LOOKUP VLOOKUP HLOOKUP 関 数 は 検 索 値 に 対 応 する 値 を 検 索 値 を 含 む 一 覧 表 から 抽 出 し てくれる 関 数 です

Transcription

1 Lookup 関 数 Vlookup 関 数 Index 関 数 等 で 表 からデータを 抽 出 する1 目 次 Rev こうすればできる 研 究 所 1. 表 から 値 を 抽 出 する 説 明 Lookup 関 数 1( 検 査 値 配 列 を 選 択 ) Lookup 関 数 2 検 査 値 検 査 範 囲 対 応 範 囲 を 選 択 して 扶 養 人 数 に 対 応 した 源 泉 税 額 を 出 力 する Lookup 関 数 (セル 範 囲 指 定 に 名 前 を 使 用 する) 定 義 した 名 前 付 範 囲 名 を LookUp 関 数 等 に 挿 入 する Vlookup 関 数 VLOOKUP 関 数 で 扶 養 人 数 に 対 応 した 表 の 値 を 抽 出 する 入 力 規 則 リストの 設 定 INDIRECT 関 数 で 複 数 の 表 を 切 り 替 えて 使 用 する(Vlookup 関 数 を 使 った 場 合 ) Vlookup 関 数 Match 関 数 Index 関 数 の 組 合 せ Vlookup Index Match 関 数 と 入 力 規 則 の 組 合 せて 操 作 しやすくする(VBE を 使 ってみる) Vlookup 入 力 規 則 Index Match 関 数 の 組 合 せ INDIRECT 関 数 を 使 って 表 を 切 り 替 える Lookup ウイザードを 使 用 する /22

2 1. 表 から 値 を 抽 出 する 説 明 1.1. 表 から 値 を 抽 出 するための 関 数 について 説 明 します LOOKUP VLOOKUP HLOOKUP 関 数 は 検 索 値 に 対 応 する 値 を 検 索 値 を 含 む 一 覧 表 から 抽 出 し てくれる 関 数 です LOOKUP 関 数 は 対 応 する 範 囲 が 1 列 (1 行 )からなる 場 合 に VLOOKUP 関 数 は 対 応 する 範 囲 が 複 数 列 からなる 場 合 に HLOOKUP 関 数 は 対 応 する 範 囲 が 複 数 行 からなる 場 合 に 利 用 します VLOOKUP 関 数 や HLOOKUP 関 数 の 検 索 の 型 は 0 か FALSE を 入 力 すると 検 索 値 と 完 全 に 一 致 する 値 が 返 され 0 以 外 か TRUE 又 は 省 略 すると 検 索 値 が 見 つからない 場 合 に 検 索 値 未 満 で 最 も 大 きい 値 を 返 してくれます (この 場 合 範 囲 の 左 端 列 のデータは 昇 順 に 並 べ 替 えておく 必 要 があ ります) INDEX 関 数 を 使 っても 表 中 の 値 を 抽 出 することができます INDEX 関 数 は MATCH 関 数 と 組 み 合 わせて 使 うと 便 利 です 表 に 名 前 をつけておけば INDIRECT 関 数 を 使 って 名 前 を 指 定 して 表 を 切 り 替 えることもできます 名 前 の 指 定 は 表 の 名 前 と 完 全 に 一 致 する 必 要 がありますのでリストで 選 択 するようにします Lookup ウイザードを 使 うとINDEX 関 数 とMATCH 関 数 を 組 合 せで 使 うことが 簡 単 にできます 1.2. 具 体 的 には 源 泉 税 額 表 を 使 った 事 例 で 説 明 します 源 泉 税 額 表 は 計 算 するシートと 別 のシートにあるものとしますが 一 部 改 変 しています ( 社 会 保 険 料 控 除 後 の 給 与 額 に 制 限 があります ) また 事 例 は 説 明 するために 作 成 したもので 実 務 に 供 したものではありません 3/22

3 1.3. 源 泉 税 額 表 を 用 意 します(シート 名 ) 表 中 のセル 結 合 などは 解 除 しておきます 以 上 列 は 昇 順 で 並 べておきます 1.4. 源 泉 税 額 表 を 用 意 します(シート 名 ) 表 中 のセル 結 合 などは 解 除 しておきます [ 以 上 ] 列 は 昇 順 で 並 べておきます 4/22

4 2. Lookup 関 数 1( 検 査 値 配 列 を 選 択 ) 2.1. シート2Lookup を 使 用 します シートを 選 択 して 下 図 のように 計 算 する 範 囲 ( 給 与 月 額 参 照 行 の 値 )を 設 定 します 給 与 月 額 は 社 会 保 険 料 控 除 後 の 月 額 です 給 与 月 額 の 右 側 のセルに 値 を 入 力 すると 扶 養 人 数 に 応 じて 源 泉 徴 収 額 が 計 算 されるようにしま す ここでは Lookup 関 数 を 使 って 給 与 月 額 に 対 応 する 値 ( 参 照 行 の 値 )を 求 めます 下 図 で 参 照 行 の 値 は 源 泉 税 額 表 で 給 与 月 額 で 参 照 した 行 の 値 です また 範 囲 指 定 は 源 泉 税 額 表 を 参 照 するのにセル 範 囲 (A1:B10 等 )で 指 定 した 場 合 です 名 前 指 定 は 源 泉 税 額 表 につけられた 名 前 ( 源 泉 税 額 表 等 )で 指 定 した 場 合 です 扶 養 人 数 源 泉 徴 収 額 源 泉 税 額 表 の 給 与 月 額 に 対 応 した 区 分 値 2.2. 給 与 月 額 が 入 力 されたら 参 照 行 の 値 が 出 力 されるようにします 値 を 出 力 するセルを 選 択 しておいて Lookup 関 数 を 入 力 して 検 査 値 配 列 を 選 択 し OK しま す こちらを 選 択 したときの 説 明 は 次 節 3.1で 説 明 します 5/22

5 2.3. 下 図 のようになります (F4 を 押 して 絶 対 参 照 にします ) セル B 源 泉 税 額 表 は 説 明 としてシート を 使 いますので 配 列 の 窓 にはシート の 表 の B 列 を 指 定 します シート を 選 択 してデータの 範 囲 を 選 択 します またはキーボードから 入 力 する 他 のシートにある 表 を 指 定 する 場 合 シート 名! 表 のセル 範 囲 とする 6/22

6 2.5. 配 列 の 範 囲 は F4 を 押 して 必 ず 絶 対 参 照 にします これで 一 応 Lookup 関 数 の 使 い 方 説 明 しました 7/22

7 3. Lookup 関 数 2 検 査 値 検 査 範 囲 対 応 範 囲 を 選 択 して 扶 養 人 数 に 対 応 した 源 泉 税 額 を 出 力 する 3.1. Lookup 関 数 を 使 って 扶 養 人 数 に 対 する 源 泉 税 額 を 計 算 します シート 3Lookup を 使 用 します 見 出 し として 源 泉 税 額 表 の 扶 養 人 数 のフィールド 行 を 計 算 するシートにコピーします 3.2. 計 算 結 果 が 入 るセルを 選 択 し Lookup 関 数 を 入 力 します 下 図 で 検 査 値 検 査 範 囲 対 応 範 囲 を 選 択 し OK します 3.3. 扶 養 人 数 0 の 源 泉 税 額 を 計 算 します 出 力 するセルを 選 択 しておいて 1 検 査 値 に 給 与 月 額 のセルを 選 択 (シート 3Lookup) 2 検 査 範 囲 にシート の B 列 のセル 範 囲 3 対 応 範 囲 にシート の 扶 養 人 数 0(D 列 )のセル 範 囲 を 指 定 します 4OK 他 のシートにある 表 を 指 定 する 場 合 シート 名 を で 囲 み! をつけ セル 範 囲 を 絶 対 参 照 で 指 定 する 8/22

8 3.4. 扶 養 人 数 0 の 計 算 結 果 の 表 示 セルを 右 にコピー 貼 り 付 けし 対 応 範 囲 のセル 範 囲 だけを 書 き 換 え ます (または F4 キーを 押 して$D$ D$としておいて 右 にコピーします) 扶 養 人 数 分 繰 り 返 します 0 人 のときは D$5:D$384 1 人 のときは E$5:E$384 以 下 同 様 に 書 き 換 えます 3.5. 書 き 換 えを 行 うのに 編 集 - 検 索 - 置 換 を 使 用 すると 作 業 が 楽 になります たとえば D を E に 書 き 換 えする 場 合 編 集 検 索 で D という 文 字 を 検 索 します 置 換 をクリックし 置 換 する 文 字 列 に E を 指 定 して 置 換 をクリックします D E に 置 換 されます セルを 移 動 して D F の 置 換 を 行 います セルの 数 だけ 繰 り 返 します 9/22

9 4. Lookup 関 数 (セル 範 囲 指 定 に 名 前 を 使 用 する) 4.1. シート 4.3 を 使 用 します セル 範 囲 を 直 接 指 定 する 代 わりにセル 範 囲 に 名 前 を 定 義 しておいて 定 義 した 名 前 を 指 定 する 方 法 です 4.2. 使 用 する 範 囲 に 名 前 をつけておきます ( 挿 入 - 名 前 - 定 義 ) 名 前 の 付 け 方 は 作 業 グループで 統 一 しておくと 理 解 が 早 いと 思 います 下 図 の 例 ではたとえば 源 泉 税 額 はシート のセル 範 囲 $B$5:$L$384 を 表 します (セル 範 囲 の 指 定 は 最 初 は 小 さな 範 囲 たとえば$B$5:$L$10 などと 指 定 し 次 に$L$10 の 部 分 を 実 際 の 範 囲 $L$384 に 書 き 換 える 方 法 をとると 大 きな 範 囲 をはじめから 指 定 するより 楽 にできま す ) 4.3. 一 例 で 説 明 します 下 図 は 扶 養 人 数 0 の 場 合 の 設 定 例 です 1 給 与 額 源 泉 税 額 扶 養 0 という 二 つの 範 囲 名 を 使 用 していることが 分 かります この 場 合 扶 養 人 数 0 であることが 分 かる 10/22

10 5. 定 義 した 名 前 付 範 囲 名 を LookUp 関 数 等 に 挿 入 する 5.1. 一 例 として Lookup 関 数 ( 検 査 値 配 列 )に 挿 入 する 手 順 を 示 します 定 義 した 名 前 範 囲 を 入 力 するには ここに カーソルを 置 いておいて 挿 入 - 名 前 ー 貼 り 付 け をクリックし 表 示 された 名 前 付 範 囲 から 選 択 する /22

11 6. Vlookup 関 数 6.1. シート 5.2Vlookup を 使 用 します Lookup 関 数 と 同 じようですが 複 数 列 を 含 んだ 範 囲 の 中 の 列 番 で 指 定 できます 完 全 一 致 を 求 める 場 合 は False そうでない 場 合 は True 6.2. 下 図 で Lookup 関 数 の 設 定 の 仕 方 と Vlookup 関 数 の 設 定 の 仕 方 が 比 較 できます ここは True=1にする False にするとエラー になる 場 合 がある 12/22

12 7. VLOOKUP 関 数 で 扶 養 人 数 に 対 応 した 表 の 値 を 抽 出 する 7.1. シート 6を 使 用 します 前 項 5.で Vlookup 関 数 で 源 泉 税 額 表 の 一 列 目 の 給 与 月 額 に 対 応 する 値 を 抽 出 したので さらに 扶 養 人 数 0に 対 応 する 税 額 を 抽 出 することにします 値 を 出 力 するセルをクリックしておいて Vlookup 関 数 を 挿 入 します 扶 養 人 数 0に 対 応 する 列 は 3 列 目 なので3を 入 力 検 索 値 をセル$B$2 True とする とする 場 合 は 7.2. 扶 養 人 数 1~7の 値 はセル C8 をコピーして 数 式 を 編 集 します 扶 養 人 数 1に 対 応 する 列 位 置 は 4 番 目 なので 4 とする 13/22

13 8. 入 力 規 則 リストの 設 定 8.1. セルに 扶 養 人 数 のリストを 設 定 して 選 択 できるようにします ( 次 節 以 降 の 準 備 をします) リストを 設 定 するセルをクリックして(メニューのデーター 入 力 規 則 リスト)で 設 定 します このリストの 文 字 列 と 対 照 となる 表 ( 源 泉 税 額 表 )の 文 字 列 は 厳 密 に 一 致 しなくてはなりません ( 別 の 場 所 でも 使 う 場 合 はツールーオプションーユーザ 設 定 リストに 登 録 しておきます) 複 数 の 表 を 使 うときはすべての 表 のリストに 対 応 する 文 字 列 を 一 致 させます リストを 作 成 するときに 下 図 のようにシートのセル 範 囲 で 作 れば 間 違 いがありません キーボードから 入 力 もできます 正 確 な 文 字 列 を 入 力 し, で 区 切 ります 表 の 名 前 をリストに 登 録 し 表 を 切 り 替 えることにも 使 えます 14/22

14 9. INDIRECT 関 数 で 複 数 の 表 を 切 り 替 えて 使 用 する(Vlookup 関 数 を 使 った 場 合 ) 9.1. シート8を 使 用 します 源 泉 税 額 表 と 源 泉 税 額 表 をセルに 設 定 したリストを 選 択 して 切 り 替 えます ついでに 扶 養 人 数 の 選 択 できるようにします 下 図 のセル C38に 源 泉 税 額 表 につけた 名 前 を 設 定 します セル B41 には 扶 養 人 数 に 対 応 したリストを 設 定 します セル B42 には B42 の 値 に 対 応 した 源 泉 税 額 表 の 列 位 置 が 出 力 されるように IF 関 数 を 使 って =IF($B$41="0 人 ",3,IF($B$41="1 人 ",4,IF($B$41="2 人 ",5,IF($B$41="3 人 ",6,IF($B$41="4 人 ",7,IF($B$41="5 人 ",8,IF($B$41="6 人 ",9,10))))))) と 設 定 します この 例 の 場 合 既 存 の 表 を 使 いましたので 0 人 など 数 字 と 文 字 の 間 に 半 角 スペ ースが 2 個 入 っていて 設 定 が 間 違 いやすくなっていました 表 を 修 正 して 0,1, としたり 0 人,1 人 とすると 楽 になると 思 います (IF 関 数 のネストは7まで 使 用 できます If 関 数 よりも Match 関 数 を 使 ったほうがいいとおもい ますがここでは if 関 数 にしました) セル B43 には =VLOOKUP($B$40,INDIRECT($C$38),$B$42,FALSE) と 設 定 します ここに 表 示 されている 数 値 は 誤 りで す 正 しくは 1910 です 9.2. 上 記 の 設 定 によって 扶 養 人 数 を 変 えたり また 源 泉 税 額 表 を 替 えたりできます ここはリストで 選 択 する ここは 入 力 する ここはリストで 選 択 する ここに 表 示 されている 数 値 は 誤 りです 正 しくは 0 となります 15/22

15 10. Vlookup 関 数 Match 関 数 Index 関 数 の 組 合 せ シート9を 使 用 します Match 関 数 と Index 関 数 と 組 み 合 わせて 抽 出 することにします 一 度 に 関 数 を 組 み 合 わせると 式 が 複 雑 になるので 1 Vlookup 関 数 で 給 与 月 額 に 対 応 する 源 泉 税 額 表 の B 列 の 値 を 抽 出 する( 前 出 ) 2 1で 抽 出 した 値 の 表 中 の 行 位 置 を Match 関 数 で 知 る 3 扶 養 人 数 に 対 する 表 中 の 列 位 置 は 目 視 で 指 定 できるのでその 値 を 入 力 する 4 表 中 の 行 位 置 列 位 置 に 対 応 する 値 を Index 関 数 を 使 って 抽 出 し 扶 養 人 数 に 対 応 するセルに 出 力 する という 手 順 で 計 算 します 1 は 前 段 で 説 明 してありますので 省 略 します 2 Match 関 数 で 行 位 置 を 求 めます 行 位 置 を 出 力 するセル(B9)をクリックしておいて Match 関 数 を 挿 入 します 給 与 額 ( 源 泉 税 額 表 の B 列 )をセル B2 の 値 で Match 関 数 を 使 って 検 索 する 場 合 TRUE とします さらに 同 様 にして 列 位 置 を 計 算 しておきます 16/22

16 Index 関 数 を 挿 入 します 出 力 するセル(C8)を 選 択 しておいて 扶 養 人 数 0のときの 値 を 抽 出 します こちらを 使 う 扶 養 人 数 0に 対 応 する 列 位 置 は 源 泉 税 額 表 の 3 番 目 なので 3 を 入 力 する 扶 養 人 数 に 対 応 した 列 位 置 に 書 き 換 える C8 セルをコピー する 17/22

17 10.3. さらに 列 位 置 を 計 算 しておくと INDEX 関 数 の 意 味 が 理 解 できます 列 位 置 行 位 置 交 点 の 値 18/22

18 11. Vlookup Index Match 関 数 と 入 力 規 則 の 組 合 せて 操 作 しやすくする(VBE を 使 ってみる) シート10を 使 用 します 入 力 規 則 のリスト 設 定 でセルに 入 力 する 値 を 選 択 できるように 設 定 できます 扶 養 人 数 をリストから 選 択 し Match 関 数 で 列 位 置 を 計 算 します これらの 機 能 を 組 み 合 わせて 使 って 操 作 しやすく 出 来 ます さらに 表 示 の 切 り 替 えをオプションボタンを 使 って 行 い オプションボタンのバックカラーを 変 化 させるようにしてみました (オプションボタンなどを 使 用 するときは VisualBasicEditor を 使 いコードを 書 きます ( 省 略 )) 表 を 切 り 替 えるため オプションボタンをクリック する リストで 扶 養 人 数 を 選 択 シート を 使 っ た 計 算 結 果 シート を 使 った 計 算 結 果 上 の 図 でセル B22 には 下 の 式 が 設 定 されています =INDEX( 源 泉 徴 収 税 額 表.xls! 源 泉 税 額 表 , MATCH($B$20, 源 泉 徴 収 税 額 表.xls! 給 与 額 ,False), MATCH($B$21,'200601'!$B$5:$L$5,False)) 同 様 にセル C22 には 下 の 式 が 設 定 されています =INDEX( 源 泉 徴 収 税 額 表.xls! 源 泉 税 額 表 , MATCH($C$20,'200404'!$A$9:$A$388,False), MATCH($B$21,'200404'!$A$9:$K$9,False)) Match 関 数 で 上 の 図 の 参 照 行 の 値 に 対 応 する 行 の 位 置 扶 養 人 数 に 対 応 する 列 の 位 置 を 求 め 行 列 の 交 点 の 値 を index 関 数 で 求 めています Match 関 数 は 複 数 列 の 表 を 扱 うことは 出 来 ません VisualBasicEditor では オプションボタンをクリックしたときに 対 応 する 源 泉 徴 収 額 の 表 示 セル(B22,C22)はフォント の 色 を 白 黒 に 変 化 させています つまり 値 がなくなるわけでなく 見 えないようにシートの 色 と 同 じにしたり( 白 ) 違 えたり( 黒 )しています 同 様 に オプションボタンの 色 は 選 択 されたときに 着 色 するように 設 定 しています ( 非 選 択 のときは 白 にしています) 19/22

19 12. Vlookup 入 力 規 則 Index Match 関 数 の 組 合 せ INDIRECT 関 数 を 使 って 表 を 切 り 替 える シート11を 使 用 します INDIRECT 関 数 はセルで 指 定 した 範 囲 名 を 設 定 できます この 機 能 を 使 って 表 の 切 り 替 えをすることが 出 来 ます セルには 入 力 規 則 のリスト 設 定 で 範 囲 名 を 設 定 しておくことがポイントです 入 力 規 則 のリスト 設 定 で 範 囲 名 と 同 じ 名 前 を 設 定 する 入 力 規 則 のリスト 設 定 入 力 する 値 計 算 結 果 計 算 結 果 のセル B34 には INDIRECT 関 数 を 使 ってセル C28 の 文 字 列 によって 源 泉 税 額 表 を 切 り 替 えるように =INDEX(INDIRECT($C$28),$B$31,$B$33) と 設 定 しています つまり INDIRECT 関 数 は 文 字 列 で 指 定 されたセル 範 囲 を 返 してくれます 前 述 しましたが Match 関 数 は 複 数 列 の 表 を 扱 うことが 出 来 ません そのため(この 例 では) $B$31 は 参 照 行 の 値 の 二 つの 表 を 切 り 替 えて 源 泉 税 額 表 の 行 方 向 の 位 置 を 求 めるため IF 関 数 を 使 って =IF($C$28=" 源 泉 税 額 表 ",MATCH($B$29, 給 与 額 ,TRUE),MATCH($B$29, 給 与 額 ,TRUE)) としています 同 様 に$B$33 は 扶 養 親 族 等 の 数 の 源 泉 税 額 表 の 列 方 向 の 位 置 を 求 めるため =IF($C$28=" 源 泉 税 額 表 ",MATCH($B$32, 扶 養 数 ,False),MATCH($B$32, 扶 養 数 ,False)) としています セル B30 の 式 は =VLOOKUP($B$29, 源 泉 税 額 表 ,1,1) です これは 上 のようにした 場 合 は 必 要 ないです セル B30 を 使 うようにするにはセル B31 の 式 は =IF($C$28=" 源 泉 税 額 表 ",MATCH($B$30, 給 与 額 ,TRUE),MATCH($B$30, 給 与 額 ,TRUE)) とします でも 結 果 は 同 じになります (この 場 合 は True,False はどちらでもいいです ) 20/22

20 13.Lookup ウイザードを 使 用 する エクセルメニューの ツール - アドイン で Lookup ウイザードをインストールしておくとINDEX 関 数 とMATCH 関 数 の 組 合 せを 簡 単 に 使 うことができます アドインは 標 準 ではインストールされませんのでOFFICE -CDROMから 追 加 インストールします 呼 び 出 しは ツール - ウイザード - Lookup() でできます 使 用 する 表 の 名 前 を 入 力 する クリックする 21/22

21 セル C16には 下 の 式 が 入 力 されます =INDEX( 源 泉 徴 収 税 額 表.xls! 源 泉 税 額 表 , ,), 源 MATCH(C15, 泉 徴 収 税 額 表.xls! 扶 養 数 ,)) MATCH(C14, 源 泉 徴 収 税 額 表.xls! 給 与 額 セル C14に=$B$14 セル C15に=$B$15 と 入 力 します セル C14を=$B$13 とする 場 合 はセル C16の 式 を 下 のように 修 正 します =INDEX( 源 泉 徴 収 税 額 表.xls! 源 泉 税 額 表 , ,TRUE ), MATCH(C15, 源 泉 徴 収 税 額 表.xls! 扶 養 数 ,)) MATCH(C14, 源 泉 徴 収 税 額 表.xls! 給 与 額 TRUE を 加 える 22/22