• 検索結果がありません。

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

N/A
N/A
Protected

Academic year: 2021

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

Copied!
21
0
0

読み込み中.... (全文を見る)

全文

(1)

Lookup 関数、Vlookup 関数、Index 関数等で

表からデータを抽出する①

Rev070924

こうすればできる研究所 目次 1. 表から値を抽出する説明 ... 3 2. Lookup 関数①(検査値配列を選択)... 5 3. Lookup 関数②検査値、検査範囲、対応範囲を選択して扶養人数に対応した源泉税額を出力する。 .. 8 4. Lookup 関数(セル範囲指定に名前を使用する) ... 10 5. 定義した名前付範囲名を LookUp 関数等に挿入する ... 11 6. Vlookup 関数 ... 12 7. VLOOKUP 関数で扶養人数に対応した表の値を抽出する... 13 8. 入力規則―リストの設定 ... 14 9. INDIRECT 関数で複数の表を切り替えて使用する(Vlookup 関数を使った場合) ... 15

10. Vlookup 関数、Match 関数、Index 関数の組合せ ... 16

11. Vlookup、Index、Match 関数と入力規則の組合せて操作しやすくする(VBE を使ってみる)... 19

12. Vlookup、入力規則、Index、Match 関数の組合せ。INDIRECT 関数を使って表を切り替える... 20

(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)

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

(4)

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

(5)

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

(6)

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

(7)

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

(8)

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

(9)

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

(10)

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

(11)

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

(12)

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

(13)

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

(14)

9. INDIRECT 関数で複数の表を切り替えて使用する(Vlookup 関数を使った場合) 9.1. シート8を使用します。 源泉税額表200601 と源泉税額表 200404 をセルに設定したリストを選択して切り替えます。 ついでに扶養人数の選択できるようにします。 下図のセル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) と設定します。 9.2. 上記の設定によって扶養人数を変えたり、また源泉税額表を替えたりできます ここは入力する ここはリストで選択する ここに表示されている数値は誤りで す。正しくは1910 です。

(15)

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

(16)

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

(17)

10.3. さらに列位置を計算しておくと INDEX 関数の意味が理解できます。

行位置

列位置 交点の値

(18)

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

(19)

12. Vlookup、入力規則、Index、Match 関数の組合せ。INDIRECT 関数を使って表を切り替える 12.1. シート11を使用します。 INDIRECT関数はセルで指定した範囲名を設定できます。 この機能を使って表の切り替えをすることが出来ます。 セルには入力規則のリスト設定で範囲名を設定しておくことがポイントです 計算結果のセルB34 には INDIRECT 関数を使ってセル C28 の文字列によって源泉税額表を切り 替えるように =INDEX(INDIRECT($C$28),$B$31,$B$33) と設定しています。 つまりINDIRECT 関数は文字列で指定されたセル範囲を返してくれます。 前述しましたがMatch 関数は複数列の表を扱うことが出来ません。 そのため(この例では) $B$31 は参照行の値の二つの表を切り替えて源泉税額表の行方向の位置を求めるため IF 関数を使って

=IF($C$28="源泉税額表 200601",MATCH($B$29,給与額 200601,TRUE),MATCH($B$29,給与額 200404,TRUE)) としています。

同様に$B$33 は「扶養親族等の数」の源泉税額表の列方向の位置を求めるため

=IF($C$28="源泉税額表 200601",MATCH($B$32,扶養数 200601,False),MATCH($B$32,扶養数 200404,False)) としています。

セルB30 の式は

=VLOOKUP($B$29,源泉税額表 200601,1,1) です。これは上のようにした場合は必要ないです。 セルB30 を使うようにするにはセル B31 の式は

=IF($C$28="源泉税額表 200601",MATCH($B$30,給与額 200601,TRUE),MATCH($B$30,給与額 入力規則のリスト設定で

範囲名と同じ名前を設定する

入力する値

入力規則のリスト設定

(20)

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

(21)

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

参照

関連したドキュメント

3.排出水に対する規制

した標準値を表示しておりますが、食材・調理状況より誤差が生じる場合が

これはつまり十進法ではなく、一進法を用いて自然数を表記するということである。とは いえ数が大きくなると見にくくなるので、.. 0, 1,

点から見たときに、 債務者に、 複数債権者の有する債権額を考慮することなく弁済することを可能にしているものとしては、

(注)本報告書に掲載している数値は端数を四捨五入しているため、表中の数値の合計が表に示されている合計

サンプル 入力列 A、B、C、D のいずれかに指定した値「東京」が含まれている場合、「含む判定」フラグに True を

本プログラム受講生が新しい価値観を持つことができ、自身の今後進むべき道の一助になることを心から願って

ダウンロードした書類は、 「MSP ゴシック、11ポイント」で記入で きるようになっています。字数制限がある書類は枠を広げず入力してく