エクセル実践塾 > エクセル実践塾2013

プランに応じた規定料金を求めたい

 

MATCH関数を利用すれば、プラン別の料金をVLOOKUP関数で求めることができます

VLOOKUP関数を使うと、指定したセルの値に応じた料金を求めることができます。

その際に用いる列番号は固定値ですが、MATCH関数を利用することで、変動させることが可能です。

MATCH関数を使うと、指定範囲内で検索した項目の相対的な位置が○番目という値でわかります。これに適切な数値を加えて、VLOOKUP関数の列番号として用います。

Excel 2013/2010/2007

 

@作例では、「Kg」の値を、「料金表」という名のセル範囲の1列目で検索し、該当行の3列目の値を求めるVLOOKUP関数が設定されています

Aプランごとの料金を記した料金表にしてから、「数式」タブの「名前の管理」で「料金表」のセル範囲を修正します

「料金表」のセル範囲は、VLOOKUP関数による表引きで使用しています。名前で定義されているセル範囲を修正すれば、表引きで使用するセル範囲も変わります。

B一覧から「料金表」を選んで、「参照範囲」の変更を行います

Cセル範囲をドラッグで指定し直します

D「閉じる」をクリックします

E「はい」をクリックして、セル範囲名の修正を完了します

FVLOOKUP関数式の列番号の指定位置に、MATCH関数を挿入します

G検査値として、入力されているプラン名を指定します

H半角のカンマを入力してから、F3キーで「プラン」を、検査範囲として指定します

「プラン」は、ドロップダウンリストの設定で使用したセル範囲名です。料金表のプラン名が格納されています。

I「照合の種類」には、完全一致の「0」を指定します

J閉じ括弧でMATCH関数式を完成させて、得られた値に2を加える、「+2」を追加します

2を加えることで、1列目の「プランB」のときには、VLOOKUP関数の列番号が3になるようにします。同様に、2列目の「プランA」のときには4に、3列目の「プランS」のときには5になります。

Kたとえば、「プランA」のときには、MATCH関数で2番目が得られ、それに2を加えた4がVLOOKUP関数の列番号となります

※テーブル化されている表の場合は、数式の修正は、同列の他のセルに自動的に適用されます。

 

関連する他のページ

積み上げ横棒グラフの帯に、「万円」と付けた値を表示したい
積み上げ横棒グラフの帯には、データラベルとして、任意の形式の値を表示することができます

現在の時刻をすばやく入力したい
Ctrl+:(コロン)で入力できます

郵便番号や会員番号、日付の表示形式を整えたい
ユーザー定義すれば、ハイフン付きの表示や、表示桁位置の固定などを、データの修正なしに行えます

あとから見てもわかりやすいVLOOKUP関数式にしたい
「範囲」として指定するセル範囲に、あらかじめ適当な名前を付けておけば、VLOOKUP関数式でもそれを使えるのでわかりやすくなります

郵便番号から、都道府県名と、それ以降の住所を、別々に入力したい
郵政事業株式会社のWebで公開されている郵便番号データを使えば、郵便番号から都道府県名などを表引きできます

商品番号を入力すると、自動的に商品名以外の情報も表示されるようにしたい
商品名などを表示するセルが、表引きの対象リストと同じ並びになっていれば、商品名のVLOOKUP関数式をコピーして使えます

表を簡単に見やすくしたい
Excel 2013/2010/2007なら、表をテーブル化すれば、簡単に見やすくできるとともに、表の拡張なども容易に行えるようにできます

表の見栄えを良くしたい
「テーブルとして書式設定」を使って、表をテーブルに変えるのが簡単です

テーブル化した表を使って表引きしたい
あらかじめ適当なテーブル名をつけておけば、その名前で表引きを行えます

データ行の増減を簡単に行いたい
テーブル化した表なら、データ行の増減にも柔軟に対応できます

都道府県名をドロップダウンリストで入力したい
都道府県が入力されているセル範囲を、「データの入力規則」のリストとして設定すれば、ドロップダウンリストで選べるようになります

プルダウンリストからデータを選んで入力できるようにしたい
あらかじめそのデータを別の場所に用意しておけば、その範囲を指定することで、プルダウンリストの一覧から選んで入力できるようになります

勤務者の入力を効率的に行いたい
勤務者のリストがあるなら、そのリストから選択入力することができます

項目をリストから選択入力したい
「データの入力規則」を使えば、指定した項目をリストから選択入力できるようになります

A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します

特定の値のセルの個数を知りたい
オートフィルタ機能を使えば、特定の値のセルだけを抽出すると同時に、その個数を確認できます

赤い数値を除いて表示したい
色フィルタを使えば、色の付いていない数値だけを絞り込んで表示できます

青色のデータだけを抽出したい
オートフィルタを有効にすれば、色フィルタで特定色のデータだけを抽出できます

データの入力されていないセルの数を知りたい
COUNTIF関数で、「検索条件」として「""」を指定すれば、データの入力されていないセルの数がわかります

COUNTIF関数で比較演算子を使いたい
COUNTIF関数では、比較演算子を文字列として扱います

一覧に含まれるデータが入力されたときに、条件付き書式で強調する
COUNTIF関数を使った条件式で、条件付き書式を設定します

指定色で塗りつぶされているセルの個数を求めたい
塗りつぶし色を数値で表すユーザー定義関数を作れば、数式で求めることができます

30代と40代の回答者の数を求めたい
COUNTIF関数式を2つ使えば、2つの条件のいずれかに合致するセルの数を調べることができます(OR条件)

COUNTIF関数を使って、祝祭日や休業日のリストと、日付を照合する
あらかじめ祝祭日や休業日のリストを用意し、COUNTIF関数で日付との照合を行います

特定の値以上のセルの個数を常に確認したい
COUNTIF関数を使えば、条件に合致するセルの個数を表示できます

別シートの一覧に含まれる商品名が入力されたときに、そのセルを任意の色で塗りつぶして強調したい
COUNTIF関数を使った条件式で、条件付き書式を設定します

特定の範囲にあるセルの個数を常に確認したい
COUNTIF関数式を組み合わせる方法があります

祝祭日を黄色で塗りつぶす
祝祭日の年月日を別表として用意し、COUNTIF関数で日付との照合を行い、それを塗りつぶしの条件とします