【Excel関数】人事担当者必見 Excel関数はこう使う！ 第1回 VLOOKUP関数
その1：4番目の引数 [検索方法] の活用法 9月分の保険料から厚生年金保険料の保険料率が変更されました。 日本年金機構でも、平成26年9月からの標準報酬月額表が、PDFとExcelの両方で公開されています。 例えばこの標準報酬月額表のような「～以上～未満」の形式も、VLOOKUP関数を用いて、給与から標準報酬月額や保険料額を求めることができるのです。 まずは標準報酬月額表のExcelをダウンロードします。 これがExcelの標準報酬月額表です。 今回は、試しに同じファイルの別のシートに、標準報酬月額を算出する数式を作成してみます。 準備として、1か所だけ手を加えます。 E10のセルが空白ですが、値として0を入力します。 今回使うVLOOKUP関数では、このE列を参照するのですが、この列の「～以上～未満」の「～以上」の部分が空白だとエラーが出てしまうため、このような処理が必要になります。 また今回は便宜上、シート名を「標準報酬月額表」に変更しています。 シートを1つ追加しました。 今回はB3のセルに給与額、隣のC3のセルに厚生年金保険料の折半額を表示させます。 C3のセルに以下の数式を入力しました。 VLOOKUP関数を使うときは、最後は省略するか、0を入力するか、FALSEを入力するかが多いのですが、省略、0、FALSEにすると「完全一致する値を検索」するモードになるのです。 一方、1やTRUEを入力すると「近似値を含めて検索」するモードになるため、標準報酬月額を算出するのに適している、という訳です。 ただし、VLOOKUP関数で検索する範囲の一番左の列（今回の場合はE列）が、昇順になっている必要があります。 昇順になっていなければ、正しい結果が計算できなくなります。 試しに、100,999／101,000／101,001の3つの値を入力してみました。 値が合致していますよね。 これで、4番目の引数 [検索方法] の活用法は終了です。 近似値検索を使うことで、VLOOKUP関数の活用できる幅が広がりましたね！ その2：MATCH関数との組み合わせ MATCH関数とは、どのような関数でしょうか。 MATCH関数 MATCH 検査値, 検査範囲, [照合の種類] 使いどころが難しいMATCH関数ですが、私はVLOOKUP関数と組み合わせて使うことが多いです。 MATCH関数は、「[検査値]を[検査範囲]から探してきて、何番目にあったかを数値で結果を返す」関数になります。 ポイントは「数値で結果を返す」ところです。 VLOOKUP関数では、[列番号]の部分に使うことで、VLOOKUPを多用することになってもオートフィルで計算式を入力することができるようになります。 例えば以下のような名簿や、給与データから必要な情報を抜き出したい場合、項目が大量にあると、それだけ列番号を変えなければならず、非常に手間になります。 この名簿を、例えば以下のフォーマットのように必要な情報のみ抜き出す際に、VLOOKUP関数とMATCH関数を組み合わせた計算式を使うと、非常にうまくいきます。 MATCH関数を使うと以下のようになります。 つまり、VLOOKUP関数の列番号を求める数え方とまったく同じになるのです。 後はこの式をそのまますべてのセルにコピーすれば、完了となります。 これができれば、項目が多くても、コピー＆ペーストで手間はほとんどかかりません。 私は特に、給与のデータを前月と比較するときにこれを使っています。 少し難しいですが、覚えると業務効率はぐっと高まります。 また、業務に必要なITスキルを鍛えるITリテラシー研修や、業務フローを整え見える化するなどの人事業務改善提案の事例のような、SRのさまざまな事例はこちらからご覧になることができます。
次の
エクセルで給料支払時の源泉所得税額を計算する
＜目次＞• 応用編： （別記事） VLOOKUP関数の使い方を解説！ まず、VLOOKUP関数とは何か？を知って頂くために、簡単なサンプルをご覧ください。 このサンプルでは、「商品検索」の「No」に商品Noが入力されると、「商品マスタ」内でそのNoを検索し、そのNoに該当する商品の「商品名」と「単価」を取り出して表示します。 つまり、VLOOKUP関数は、データを検索し、そのデータに該当した行の指定列からデータを取り出す関数なのです。 それでは、VLOOKUP関数が入力されているセルのうち、商品名を表示するセルC16の内容を詳しく見てみましょう。 （）の中の引数をひとつずつ解説します。 1番目の引数（今回の場合は「B16」）には、 検索したいデータ、もしくは、そのデータを入力するセルを指定します。 サンプルでは、「商品検索」の「No」のセルが指定されています。 入力されている関数 指定されたセルの位置 2番目の引数：検索する表の範囲 2番目の引数（今回の場合は「B3:G12」）には、 検索の対象となる表の範囲を指定します。 サンプルでは、「商品マスタ」のセル範囲が指定されています。 なお、表見出しのセルを含めて指定する必要はありません。 入力されている関数 指定されたセル範囲 検索するのは「左端列」 さて、ここで重要なポイントを押さえておく必要があります。 1番目の引数に「検索するデータ」、2番目の引数に「検索する表の範囲」を指定したわけですが、「表の中のどのセル範囲を検索するのか」について指定していません。 実は、VLOOKUP関数は、指定した表の「左端の列」を縦方向（行単位）に検索します。 「左端ではなく、3列目で検索したい」といったように、検索するセル範囲を指定することはできません。 3番目の引数：列番号（列位置） 3番目の引数（今回の場合は「3」）には、 取り出したいデータの列位置を指定します。 サンプルでは、「商品マスタ」の左から3列目（商品名の列位置）が指定されています。 ここで注意したいのは、列位置は、2番目の引数で指定した表の「左端から何列目か」を指定する点です。 入力されている関数 指定された列位置 4番目の引数：検索方法（検索の型） 4番目の引数（今回の場合は「FALSE」）には、「検索したいデータが表の左端列で見つからなかった場合にどうするか」という 検索方法 について設定します。 ここでは、FALSEを指定しています。 この引数の詳しい内容については後述します。 関数は「結果を表示するセル」に入力 関数の基本の復習になりますが、関数は「結果を表示するセル」に入力します。 従って、VLOOKUP関数は、取り出したデータを表示したいセルに入力します。 サンプルでは、取り出した商品名を表示するセルC16に入力されています。 VLOOKUP関数の押さえておくべきポイント VLOOKUP関数の押さえておくべきポイントは次のとおりです。 検索は、指定した表の「左端列」で行われます。 取り出したいデータ位置は、指定した表の「左端から何列目か」で指定します。 VLOOKUP関数は「検索結果を表示したいセル」に入力します。 これらのポイントを踏まえて、再度、サンプルの仕組みを確認してみましょう。 セルB16に「0005」を入力すると、商品マスタの左端列で縦方向に検索され、「0005」が入力されている行が検索されます。 そして、その行の左端（表の左端）から3列目のデータ「表計算ソフト」が取り出されて、セルC16に表示されます。 なお、セルD16には、次のような数式が入力されています。 「単価」を表示させたいので、3番目の引数【列位置】に「5」が指定されています。 VLOOKUP関数の仕組みが見えてきたでしょうか？ 続いて、補足したいポイントをいくつか解説します。 そこで、以前、「」の「IF関数を活用しよう！」で紹介したように、IF関数を使って数式を以下のように書き換えます。 これで、検索データが空白でもエラーは表示されません。 その理由は、「」の「」で紹介したように、このサンプルでは、商品Noを「4」といった数値ではなく、「0004」といった文字列として扱っているからです。 このように、検索データを入力するセルの 書式設定は、検索対象となる表の左端列の書式に合わせておいた方が良い場合があるので注意しましょう。 補足ポイントその3：引数「検索の型（検索方法）」について 4つ目の引数「検索の型（検索方法）」は、「検索したいデータが表の左端列で見つからなかった場合にどうするか」という検索方法について設定する引数でしたね。 この引数には、「FALSE」か「TRUE」を設定します。 FALSEの代わりに「0」、TRUEの代わりに「1」を使用して設定することもできますが、一般的ではないため、ここでは、FALSEとTRUEを使用して解説します。 TRUEを指定、または、この引数の指定を省略すると、データが見つからなかった場合、 そのデータを超えない最大値を検索します。 ただし、表の左端列で昇順に並べ替えておく必要があります。 例えば、「50,000」を検索しても見つからなかった場合、「50,000」を超えない最大値が検索されるので、下図の場合、「45,000」が検索されます。 予算内で収まる単価かどうかを調べたい場合などに利用できますね。 検索したいNoに「0011」を入力したとします。 もし、4番目の引数が省略、もしくはTRUEに設定されていると、「0011」がないので、この値を超えない最大値「0010」のデータが取り出されます。 これだと、商品No「0011」の商品は「イス」であると勘違いしてしまう可能性があります。 このサンプルでは、商品No「0011」の商品が登録されていないのなら、「登録されていない」ということがわかるように「FALSE」を設定する必要があるでしょう。 つまり、 「完全に一致するデータだけを検索したい」場合は「FALSE」に設定するということです。 さらに、私は「省略した場合はTUREが設定される」という点にも注意が必要と考えます。 意識的に省略するのなら良いのですが、うっかり設定を忘れてしまった、といった場合、エラーが表示されないので、しばらく気付かないかもしれません。 用途にもよりますが、商品リストから商品を検索する、といったような場合は、常にFALSEに設定する、という意識を持つと良いと思います。 なお、今回は、VLOOKUP関数を使って1つの表からデータを取り出す方法を紹介しましたが、「 」では、2つの表が作成されていることを想定して、条件によって取り出す表を切り替える、という定番テクニックを紹介します。 また、「」では、関数を使用して条件を満たす複数のデータを表から取り出すテクニックを紹介しています。 これらも、ぜひご覧ください！ 【関連記事】•
次の
Excelで所得税を計算するには、IF関数よりもVLOOKUP関数のTRUE
所得税の税率のしくみ 所得税は、その所得によって税率がかわります。 稼いでいる人ほど、税金が高くなるのです。 ただ、よくいわれているように、「稼ぎの半分を持って行かれる！」というのは間違っています。 こちらの記事を参考にしていただければ。 【関連記事】年収1,000万円の場合の税金は約15％。 年収と所得の違い、税率の考え方、誤解されやすい税金のしくみ EX-IT 所得税は次の表で計算されます（平成27年分以降の場合）。 【関連記事】年収1,000万円の場合の税金は約15％。 年収と所得の違い、税率の考え方、誤解されやすい税金のしくみ EX-IT この計算を、Excelでやってみます。 Excelで所得税を計算するなら、VLOOKUP関数 VLOOKUP関数のしくみ VLOOKUP関数は、V（Vertical。 垂直に）＋LOOKUP（探す）機能がある関数です。 基本的なしくみはこちらを参考にしていただければ。 【関連記事】最重要関数の1つVLOOKUP関数のしくみ EX-IT 今回の場合、所得が500万円なら、500万円のときの税率を探して計算できます。 ただし、この表からは探せません。 探すのは、あくまで「5,000,000」という数字だからです。 そこで、表をこのように作ります。 こういった数式を入れます。 この20％と427,500をVLOOKUP関数で探してきます。 ［20％］は、G3（5,000,000）を、表（A14からC20の範囲）から探し、2列目を表示するので、 VLOOKUP（G3、A14:C20、2、TRUE） です。 ［TRUE］にするのは、探している数値（この場合、5,000,000）の数値に近いものを探し出すためです。 （指定しない場合もTRUEになります） ［FALSE］だと、完全に一致しているものを探し出します。 5,000,000の場合、3,300,000を超えていて、6,950,000は超えないので、3,300,000の行の［20％］を探し出してくるというしくみです。 「近いもの」（近似値）というのは、「探している値を超えていないもので最大の値」と意味します。 ［427,500］も同様です。 では、表の数値と一致する3,300,000のときはどうなるのでしょう。 VLOOKUPの結果も232,500円です。 会社員の場合のExcel所得税計算 会社員の場合は、給料（賞与含む）の額面金額（給与明細の支給金額の合計。 交通費は除く）から、給与所得控除という経費を引きます。 この給与所得控除の計算に使うのもVLOOKUP関数です。 このように計算します。 給与収入500万円から給与所得控除154万円を引いたものが、346万円。 さらに、社会保険料（健康保険、年金など）、生命保険、扶養、基礎（誰にでもある38万円の控除）の合計（所得控除113万円）を引いたものが、課税所得（233万円）です。 この課税所得に、前述のVLOOKUP関数を使えば、所得税（13万5,500円）を計算できます。 なお、給与所得控除は、今後変更されていくので注意しましょう。 要は、ちょっとずつ増税されているのです。 平成28年分からは、年収1,200万円以上の人、平成29年分からは、年収1,000万円以上の人が増税となります。 VLOOKUP関数の対象範囲も変更しなければいけません。 （自動的に変更できるしくみもありますが、今回は入れていません） 個人事業主（フリーランス）の場合のExcel所得税計算 個人事業主（フリーランス）の場合は、まずは、事業の収入、つまり売上高（500万円）から経費（300万円）を引き利益を出します。 さらに、青色申告の場合は、最大65万円の控除（控除後がマイナスになるなら、0になるまでしか引けない）をひいたものが事業所得（135万円）です。 その後、所得控除（113万円）を引き課税所得（233万円）を計算したら、VLOOKUP関数で所得税（1万1,000円）を計算できます。 VLOOKUPのTRUEは、他の条件分けにも使えますし、 【関連記事】IF関数よりもかんたんなVLOOKUP関数で条件を判定する方法 EX-IT 毎月の源泉所得税計算にも使えます。 【関連記事】ひとり社長のExcel給与計算。 源泉所得税はVLOOKUP関数で計算 EX-IT サンプルはnoteで販売中です。 【編集後記】 昨日は、午前中、ソフマップにいろいろ引き取りに来てもらいました。 ディスプレイ2台、プリンタ、プロジェクタースクリーンです。 スッキリしました。 買取でいい値段がつくといいのですが、まあ引き取ってくれるだけでもありがたいです。 午後からは、個別コンサルティング。 個人事業主の方のfreeeの設定、確定申告準備をメインに。 夕方に友人とお茶して、夜は、俺の揚子江に行ってから、レスパスでやせる護身術。
次の