VLOOKUP関数を使った別表参照の方法と使い方
- VLOOKUP関数を利用して別表を参照し、計算する式を作成する方法について教えてください。
- 条件に基づいて別の表からデータを抽出するためにVLOOKUP関数を使用できます。
- VLOOKUP関数は、指定した値を検索して条件に一致する値を返すため、プライス表を参照して計算することが可能です。
- ベストアンサー
別表参照の関数
IF関数についての質問です A1を顧客名としB1を受注数量とし、単価を200円とします。 =B1*200 下に挙げる2つのパターンについて 別で作ったプライス表を参照し、計算する式を作りたいのですが 上手くできずに困っています… パターン １ A1＝山田さま or 田中さま @150円 A1＝鈴木さま @180円 A1＝その他顧客 @200円 パターン ２ A1＝山田さま or 田中さま @150円 A1＝鈴木さま @180円 A1＝その他顧客 注文数100個以下 @200円 注文数100個以上 @160円 VLOOKUP？を使用した表を初めて作ってみたいのですが 詳しく教えて頂けませんでしょうか。 お願い致します。
- Excel（エクセル）
- 回答数7
- ありがとう数8
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
>もしよければ別シートバージョンも教えて下さい。 回答に記載の数式では括弧の個数に誤りがありました。 C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1 ↓ C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200)*B1 別シートを参照するときは次のように修正してください。 =IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200)*B1 ↓ =IF(COUNTIF(Sheet2!E:E,A1),VLOOKUP(A1,Sheet2!E:F,2),200)*B1 但し、価格表がSheet2と言うシート名でE列に特定顧客名、F列に単価が入力されているとします。 >ここに登場する(A1,E:F,2)の「2」という数字はどういう意味の2ですか？ VLOOKUP関数は複数列を指定したとき「1番左側の列に検索対象のデータがある」と言う条件になっています。 検索対象の範囲に検索値が見付かった行の左から1番目は検索値と一致した値であり、左から2番目の列の値を返すときはVLOOKUP(A1,E:F,2)のように第3引数で2を指定します。
その他の回答 (6)
- bunjii
- ベストアンサー率43% (3589/8248)
他の回答者への補足からの憶測ですが、パターン2の場合は次のような数式で良いと思います。 =B1*VLOOKUP(IF(COUNTIF(Sheet2!A:A,A1),A1,"その他"),Sheet2!A:C,IF(B1<100,2,3),FALSE) 検索方法のFALSEは省略可能のようです。 COUNTIF関数でA1セルの顧客名がSheet2のA列に存在するときはVLOOKUPの検索値をA1とし、存在しないときは"その他"を検索値に設定します。 また、戻り値をB1が100未満のときSheet2のB列の値を返し、100以上のときC列の値を返すようにできます。 尚、A1セルが空欄のときの処理は省略していますので必要なら次のように修正してください。 =IF(A1="","",B1*VLOOKUP(IF(COUNTIF(Sheet2!A:A,A1),A1,"その他"),Sheet2!A:C,IF(B1<100,2,3),FALSE))
- mshr1962
- ベストアンサー率39% (7418/18948)
>単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200) > の、 ↑2 > という数字はどういう意味ですか？ この場合は、A:B列の2列目（B列）の内容を表示という意味です。 ここが1の場合はA列の内容なので、顧客のセルの内容と同じものが表示されます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
今仮に >A1を顧客名としB1を受注数量 というデータが入力されているシートがSheet1であるものとします。 又、プライス表を作表するシートがSheet2であるものとします。 まず、Sheet2に次の様な表を作成して下さい。 A列 B列 1行目 顧客 単価 2行目 <100 200 3行目 >=100 160 4行目 山田さま 150 5行目 田中さま 150 6行目 鈴木さま 180 その上で、 >A1を顧客名としB1を受注数量 というデータに対する価格を表示させるセル(C1セル？)には次の様な関数を入力して下さい。 =IF(ISNUMBER($B1),$B1*VLOOKUP(IF(COUNTIF(Sheet2!$A:$A,$A1),$A1,IF($B1<100,"<",">=")&100),Sheet2!$A:$B,2,FALSE),"")
- bunjii
- ベストアンサー率43% (3589/8248)
>注文数100個未満 @200円 >注文数100個以上 @160円 >でお願い致します。 貼付画像のような表の構成で検証しました。 パターン １ C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1 IF関数で特定の顧客（山田、田中、鈴木）の単価の対応表に対してVLOOKUP関数で単価の抽出を行います。 特定の顧客に含まれない顧客のとき単価を200とします。 パターン ２ C2=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),VLOOKUP(B1,H:I,2))*B1 パターン １で特定の顧客以外の顧客のときは数量を基準に単価を抽出します。 数量が一致しないときは数量一覧の中から参照の数値より小さい最大値が検出結果になります。
- mshr1962
- ベストアンサー率39% (7418/18948)
プライス表 A＿＿＿B 顧客名 単価 山田さま 150 田中さま 150 鈴木さま 180 と別表があるとして パターン1 単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200) パターン2 単価=MIN(IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200),IF(注文数<100,200,160)) ※パターン2が複雑なのは、顧客が鈴木さまで注文数100個以上の時に、金額の逆転現象が起きないようにするためです。 もしくは プライス表 A＿＿＿B＿＿＿C 顧客名 単価1 単価2 山田さま 150 150 田中さま 150 150 鈴木さま 180 160 その他 200 160 と別表があるとして パターン2 単価=VLOOKUP(IF(COUNTIF(プライス表!$A,顧客),顧客,"その他"),プライス表!$A:$B,IF(注文数<100,2,3),FALSE)
補足
ありがとうございます！ ちなみに 単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200) の、 ↑2 という数字はどういう意味ですか？
- bunjii
- ベストアンサー率43% (3589/8248)
>注文数100個以下 @200円 >注文数100個以上 @160円 正確には矛盾します。 注文数100個のときは両方の条件がマッチします。 注文数100個未満 @200円 注文数100個以上 @160円 または 注文数100個以下 @200円 注文数101個以上 @160円 何方にするかを補足してください。
補足
すいません！ 注文数100個未満 @200円 注文数100個以上 @160円 でお願い致します。 ありがとうございます。
関連するQ&A
- 文字を含んだIF関数
IF関数についての質問です A1を顧客名としB1を受注数量とし、単価を200円とします。 =B1*200 しかし A1が山田様と田中様であった場合、150円で販売 A1が鈴木様であった場合、180円で販売 つまり、特定のお客様だけ金額が変わるというものなのですが 文字を含んだIF関数を教えて頂きたいです。 教えて頂けませんでしょうか。 お願い致します。
- ベストアンサー
- Excel（エクセル）
- VLOOKUP・FALSE…関数
尋ねたいことは単純なのですが、説明の仕方が分からず 作ってある数式をそのまま貼り付けての質問で申し訳ないです！ =IF(B1="","0",B1*VLOOKUP(IF(COUNTIF(【プライス表】!E:E,C1),C1,"その他"),【プライス表】!E:G,IF(B1<100,2,3),FALSE)) B1 受注数 C1 顧客名 別シート【プライス表】には、顧客ごとの売値が表で入っています。 いま、受注数が0～99個と100～の設定しかないのですが 0～49 50～99 100～ に分けたいと思います。 恐らく IF(B1<100,2,3) の部分だと思うのですが プライス表は1列挿入したとして、数式をどのような式にすればいいのでしょうか。 教えて下さい！お願い致します。
- ベストアンサー
- Excel（エクセル）
- Excel(2010)で氏名の頻度の求め方
Excel(2010)で、Ａ列に縦に 山田 鈴木 田中 山田 鈴木 山田 というように氏名の並べてある表があるとします。この表で氏名の頻度が 山田 3 鈴木 2 田中 1 というように表示できる関数または方法があるでしょうか。 この表のように項目が少なければ 山田 鈴木 田中 というセルを作っておいて、VLOOKUP関数で求められますが、実際には約500行ありその中の氏名をすべて手作業で入力するのは大変ですので、重複なくすべての氏名を抜き出すのは実際的ではありません。 以上よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- SQLで違うテーブルの結果を組み合わせたい
SQLでどう結果を返したらいいのかわからないので力を貸してください。 下記の受注表と入荷表があります。 受注表 担当 商品コード 顧客コード ── ──── ───── 田中 A1 001 田中 A1 002 田中 A1 003 山田 A2 020 山田 A1 003 田中 A1 020 入荷表 担当 入荷コード 入荷日 ── ───── ───── 山田 X013 20080701 山田 X013 20080701 田中 X013 20080701 山田 X013 20080630 山田 X013 20080630 田中 X123 20080630 自分の欲しい結果を出すためのSQLは下記のクエリです。 ≪受注表テーブルから≫ SELECT 担当, 顧客コード FROM 受注表 WHERE 担当 = '田中' AND 商品コード = 'A1' AND 顧客コード = '003' ; SELECT 担当, 顧客コード FROM 受注表 WHERE 担当 = '田中' AND 商品コード = 'A1' AND 顧客コード = '020' ; ≪入荷表テーブルから≫ SELECT 担当, 入荷日 FROM 入荷表 WHERE 担当 = '田中' AND 入荷コード = 'X013' AND 入荷日 = '20080701' ; SELECT 担当, 入荷日 FROM 入荷表 WHERE 担当 = '田中' AND 入荷コード = 'X123' AND 入荷日 = '20080630' ; これらで出る結果をただ下記のように横並びに出したいのですがどうしたらよいでしょうか？ 「担当」の項目に関しては複数でますが気にしないで下さい。 ただ全く関係のないテーブル同士の結果を「横並び」表示にしたいだけです。 担当 顧客コード 担当 顧客コード 担当 入荷日 担当 入荷日 ── ───── ── ───── ── ──── ── ───── 田中 003 田中 020 田中 20080701 田中 20080630
- ベストアンサー
- その他(データベース)
- IF関数
A1＝お客さんの名前と B1＝注文数 「鈴木サマ」には単価100円 その他は100注文で単価150円、100以下で単価200円で販売しています。 =IF(A1="鈴木",B1*100,B1*IF(B1>=100,150,200)) この式に、「山田サマ」だったら単価120円という式を足したいのです。 宜しくお願い致します。
- ベストアンサー
- Excel（エクセル）
- EXCEL関数について
関数がわからないので教えてください。 売上表を作成しています 担当者 顧客名 売上 Aさん 田中さん 1,000 Bさん 高橋さん 1,000 Aさん 石井さｎ 1,000 Bさん 鈴木さん 1,000 この表から担当者別に購入顧客数を出したいのですが、そういった関数はありますか？ Aさん 購入顧客数 2人 Bさん 購入顧客数 2人 全体の購入顧客数をだす関数は教えていただいてわかったのですが、 担当者別に顧客数をだす方法はあるのでしょうか？
- ベストアンサー
- その他(業務ソフトウェア)
- 文字列を含んだIF関数
IF関数についての質問です 現在以下のような関数を入れています。 A1とは受注数量です。 A1が100個以下の場合A1×300円 A1が100個以上の場合A1×200円 ↓ =A1*IF(A1>=100,200,300) つまり、受注数量によって金額が変わるというものなのですが B1に顧客名を入れて、B1が○○様だった場合100円で売るという 文字を含んだIF関数も足したいのです。 方法がありましたら教えて頂けませんでしょうか。 お願い致します。
- ベストアンサー
- Excel（エクセル）
- 関数について
二つのシートがあります。 一つのシートには、二つの表があり名前を定義しています。 もう一方のシートでVlookupとindirectを使い検索できるようにしたいのです。 シート(1) A B C D 表1 101 名前 果物 表2 102 名前 野菜 ‥ シート(2) 表1 表2 A B C D E F 101 鈴木 バナナ 101 斉藤 トマト 102 佐藤 リンゴ 102 吉田 ピーマン 103 山口 ミカン 103 三浦 きゅうり 104 山田 マンゴー 104 加藤 大根 シート(2)には名前定義をしています。 名前、表1は=シート名!A1(101)～C4(マンゴー)です。 表2は=シート名!D1(101)～F4(大根)です。 シート(1)のA1、B1に入力をすると自動的にシート(2)の二つの表を切り替えてみにいくようにしたいのです。 シート(1)のC1には、=vlookup(B1,indirect(A1),2,0) D1には、=vlookup(B1,indirect(A1),3,0) と入力していますが、N#Aとなります。 困っています。どうか よろしくお願いします。
- 締切済み
- オフィス系ソフト
- 12年秋 午後 問５ 設問１
本題とは関係ないのですが 問題の中で以下の表現があります ------------------------------------------------------------ 例 SELECT 受注明細表.受注番号, 顧客名, SUM(注文数量*単価) FROM 受注明細表, 顧客表, 商品表, 注文表 WHERE 顧客表.顧客コード = 注文表.顧客コード AND 受注明細表.商品番号 = 商品表.商品番号 AND 受注明細表.受注番号 = 注文表.受注番号 ------------------------------------------------------------- ここで受注明細表.受注番号 = 注文表.受注番号 の比較は なにも絞り込んでいるのでしょうか？ 必要性が理解できません
- ベストアンサー
- 情報処理技術者
- エクセルデータの参照について【関数】
エクセルで別ブックの参照について教えてください。 顧客の売上データなのですが 例えばブック1のセルA1に注文番号、A2に数量、A3に金額が入っています。 それをブック2のセルA2に注文番号、A3に数量、A5に金額が入っていて、3つが一致したらブック2のA6にOK!と表示させたいのですが可能でしょうか・・。 教えてください。宜しくお願い致します。
- 締切済み
- その他MS Office製品
補足
どうもありがとうございます！ 分かり易そうで、やってみようと挑戦したら、価格表を別シートで作っていたので びっくりマークに混乱して、、断念してしまいました。 もしよければ別シートバージョンも教えて下さい。 ちなみに C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1 ここに登場する(A1,E:F,2)の「2」という数字はどういう意味の2ですか？ 教えて下さい。