Sheet1に下記のような数量別単価表があります。
品番 数量(個) 単価(円)
001 100-199 500
001 200-299 450
001 300-399 420
001 400-499 360
001 500-599 300
001 600- 220
002 300-599 90
002 600-999 88
002 1000-1499 85
002 1500-1799 81
002 1800-1999 80
002 2000- 79
・・・以下品番500程度まであり、それぞれ数量と単価の設定が変わってきます。この表を元にSheet2のセルに品番と数量を入力すれば、自動的に条件にあう単価を表示させる方法がわからず困っております。お知恵をお貸し下さい。
※品番001の数量「600-」の表記は600個以上はすべて単価220円という意味です。
No.9ベストアンサー
- 回答日時:
#8のWendy02です。
一応、確認を待たずに、ユーザー定義関数のVLOOKUPに似たものを作って完成しています。ただ、少し大きめなのと、最近、私のミスが多くなってきておりますので、動作試験をしばらく繰り返します。
今の段階では、まったく問題は出ていません。イレギュラーなパターンを探して、試しています。
#←ここが問題*
という問題は、こちらで解決させてしまいました。
#変更されたデータと、そうでないベタ・データでは、段違いにベタのほうが難しいです。
これは無視してください。
#今のデータは、以下のようなベタで打ち出したような状態になっているのですか?
なお、ベタのデータが、どのぐらいあるのかは、まだ気になるところです。たぶん、1万行程度は、まったく問題がないと思います。一応、品番は、並べ替えて、同じものが並んでいるという条件です。
なるべく見切り早いうちに、掲示します。お使いになるかは別問題ですが。
お返事が遅くなり申し訳ございませんでした。
せっかく「ユーザー定義関数のVLOOKUPに似たものを作って完成」
までしていただいて申し訳ございませんが、検索していましたら
別の方法が載っておりました。
・数量の最小と最大をB列とC列にわける(単価はD列)
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
として配列数式にする
この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。
No.8
- 回答日時:
こんにちは。
>単価がでずに、#REF!となります。なぜでしょうか?
INDIRECT の中の記述がおかしいのでしょうね。
VLOOKUP(B2,INDIRECT("_"&A2),2)
登録した名前と記述とかあっていないとか?
>全商品で約500品
>これはやはり全品とも品番で名前登録するしかないでしょうか?
元の質問の
>以下品番500程度まであり
というのを私は、読み落としていました。私の方法ではお勧めできません。グループとしては少数に限ります。
名前登録では、メモリ配分が悪くなります。それは、OSとExcelのバージョンに依存しますが、直接、VLOOKUPに影響が出るかと思います。
全面的に、設計じたいを替えたほうがよいです。ちまちまと、規定の関数でやって追いつかないように思います。もう、マクロ処理しかないと私は思います。今、いくつか案は持っています。
それでもよいのでしたら、私のほうで進めます。
それと、以下の確認が必要です。
今のデータは、以下のようなベタで打ち出したような状態になっているのですか?
確実になっているかどうか、それによっても話が違っています。
品番 数量 単価
A222 1-99 1110
A222 100-199 1000
A222 200-299 930
A222 300-499 890
A222 500-799 880
A222 800-900 850 ←ここが問題*
A223 1-999 78
A223 1000-2999 74
A223 3000-4999 70
A223 5000-9999 67
A223 10000-19999 63
A223 20000-20001 59 ←ここが問題*
A224 1-299 300 ←ここが問題*
データの認識としては、その示した部分が、それ以上は、値段が変わらないのなら、
A222 800- |850
A223 20000- | 59
A224 1- |300
(見にくいので、「|」区切り線を入れましたが、つまり、数量は、1- 上限がない、というようになっていることです)
という規則性がほしいですが、500ともなると、それ自体をマクロ処理しないといけないようです。
変更されたデータと、そうでないベタ・データでは、段違いにベタのほうが難しいです。
一応、よかったら確認のレスをつけてください。
No.7
- 回答日時:
#6 の Wendy02です。
読み落としました。
>品番001の数量「600-」の表記は600個以上はすべて単価220円という意味です。
だったら、上限を超えたときの処理はいりませんね。
A222 901 xxx 削除
A223 20002 xxx 削除
A224 300 xxx ←300(円)に換える。
A224 は、数式的には、ダブってしまっていますが、一応、目安のために、そのままで良いと思います。
_A222, _A223 の名前の登録の参照範囲は、狭めてください。
当然、エラー処理は、VlookUp が、IsError にならない限りは、必要ありません。
=IF(ISERROR(C2),"",B2*C2)
ぐらいでよいでしょうね。
No.6
- 回答日時:
こんばんは。
このように書き換えたほうが見やすいですね。
xxx は、単に上限を超えたときのエラー値を出すためのダミーです。
A B C
品番 数量 単価
A222 0 0
1 1110
100 1000
200 930
300 890
500 880
900 850
901 xxx
品番 数量 単価
A223 0 0
1 78
1000 74
3000 70
5000 67
10000 63
20000 59
20002 xxx
品番 数量 単価
A224 0 0
1 300
300 xxx
数が増えたりするいけないので、本当は、それぞれの商品群の間は空けておいたほうが良いかもしれませんね。
次に、品番で名前登録します。しかし、A222 とかいうのは、セル名と同じになりますが、そのまま登録しようとすると、デフォルトで、「_ (アンターバー)」が付けられます。
[挿入]-[名前]-[定義] で、
それぞれを登録していきます。
名前 参照範囲
_A222 Sheet1!$B$2:$C$9
_A223 Sheet1!$B$12:$C$19
_A224 Sheet1!$B$22:$C$24
と、品名の部分は入れないで、数量と単価の部分だけを参照範囲に入れます。
後は、Sheet2のシートに、このように入れれば、
A B C D
品名 数量 単価
A222 150 =VLOOKUP(B2,INDIRECT("_"&A2),2)
単価は出てきます。D列の計算のところで、文字(xxx)が返った時のエラー処理をしてください。上限が超えております。例:=If(IsText(C2),"",B2*C2)
この回答への補足
ごめんなさい、実際に記載いただきましたとおりにやってみましたが、
単価がでずに、#REF!となります。なぜでしょうか?
また、全商品で約500品あるのですが、これはやはり全品とも品番で名前登録するしかないでしょうか?
No.5
- 回答日時:
VLOOKUPを使った表にしたいんです。
(シート1)
A B C D E
1行目 品番 数量 単価 計算式1 計算式2
2行目 A222 100-199 500
3行目 A222 200-299 450
4行目 A222 300-399 420
5行目
(計算式1)=VALUE(RIGHT(A2,3)&LEFT(B2,find("-",B2,1),-1))
(計算式2)=C2
計算式1は品番がA○○○しかないとして計算式を作っていますので、
他にもある場合はA2の値を拾う計算式を考えてください。
(シート2)
A B C D
1行目 品番入力 数量入力 計算式3 計算式4
(計算式3)=VALUE((RIGHT(A1,3)&B1))
(計算式4)=VLOOKUP(C1,Sheet1!$D:$E,2,TRUE)
計算式3は計算式2と同様に、品番がA○○○しか無いとして作っています。 以上
この回答への補足
実際にやってみましたが、Sheet1のD2セルに計算式1を貼り付けようとした所、「この関数に対して、多すぎる引数が入力されています。」とエラー表示されてしまいます。
また、品番はご指摘の通り、A000~Z000まで使用しております。
>A2の値を拾う計算式を考えてください。
こちらは具体的にはどのようにすればいいでしょうか?
初心者で申し訳ございませんが、よろしくお願いいたします。
・数量の最小と最大をB列とC列にわける(単価はD列)
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
として配列数式にする
この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。
No.4
- 回答日時:
参考にならないかも知れないが、私なら次のようにします。
(フォントサイズを小さくして、画面をいっぱいに広げてご覧下さい)Sheet1
A B C D E F G H I J K L M N
1 品番↓ 1 100 200 300 500 800 901 1000 3000 5000 10000 20000 20002
2 A222 1110 1000 930 890 880 850 X X X X X X X
3 A223 78 78 78 78 78 78 78 74 70 67 63 59 X
4 A224 300 300 300 X X X X X X X X X X
5 …
Sheet1 の「数量別単価表」を上のように書き替えます。ただし、データは[No.2回答に対する補足]に示されたものを使用しています。
Sheet2
A B C
1 品番 数量 単価
2 A222 23 1110
3 A222 123 1000
4 A222 234 930
5 A222 345 890
6 A222 567 880
7 A222 890 850
8 A222 900 850
9 A222 1234 X
10 A223 567 78
11 A223 2345 74
12 A223 3456 70
13 A223 7890 67
14 A223 12345 63
15 A223 20001 59
16 A223 23456 X
17 A224 123 300
18 A224 300 X
C2: =HLOOKUP(B2,Sheet1!B$1:N$500,MATCH(A2,Sheet1!A:A,0))
ありがとうございます。
商品数量が約500品あって、その1品ごとに数量の設定(何個から何個までがいくら)が、全くバラバラなのです。
数量欄を全品共通にしてしまうと、Sheet1の表作成に膨大な時間がかかってしまうかも。。
No.3
- 回答日時:
#01です。
すみません。数量の桁は最大5でしたね。以下の式に置き換えて下さい。なお「2000- 」の最大数は入れなくても計算します(式で使っていません)
=INDEX(Sheet1!C1:C13,MATCH(A1&TEXT(B1,"00000"),Sheet1!A1:A13 &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"00000"),1))
やってみたのですが、やはりエラーになってしまいました。
・数量の最小と最大をB列とC列にわける(単価はD列)
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
として配列数式にする
この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。
No.2
- 回答日時:
#01です
>実際には品番はA205のように、ローマ字1文字+3桁の数字としており
>数量は1桁から最大5桁まで商品ごとにあります
式は以下のようになると思います。Shift+Ctrl+Enterで確定です。
=INDEX(Sheet1!C1:C13,MATCH(A1&TEXT(B1,"0000"),Sheet1!A1:A13 &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"0000"),1))
注意事項:各品目の数量は必ず「1-xxx」のように数値は1から始まっていないと、前の品目の単価を引っぱってくることがあります。それをご了承ください
「2000- 」のように上限値が空白なのでそれを関数で処理するにはつらいのです。
本当であればマクロで処理するのがベストだったかもしれません
この回答への補足
品番数量単価
A2221-991110
A222100-1991000
A222200-299930
A222300-499890
A222500-799880
A222800-900850
A2231-99978
A2231000-299974
A2233000-499970
A2235000-999967
A22310000-1999963
A22320000-2000159
A2241-299300
・・・・・・・・・・・・
という具合に数量を1からはじめ、かつ数量に上限を設けてやってみましたが、一度最後の行の数値を検索してしまうと、品番を入力しなおしても、数量を入力しなおしても、最後の行の単価が表示されっぱなしになります。
この数式は仕入れの発注書に単価を明記する際に、利用する予定です。
なので、発注のたびに品番・数量を変更して入力すれば、単価が自動的に表示される仕組みを作りたかったのです。
やはり関数では限界でしょうか?
No.1
- 回答日時:
表が品番、数量で並び替えされている前提で、かつ数量は最大4桁とするとき、
Sheet2のA1に品番(001でも1でも可)、B2に数量を入力して、C1に以下の式をペーストしShift+Ctrl+Enterで確定(配列数式にします)
=INDEX(Sheet1!C1:C13,MATCH(TEXT(A1,"000")&TEXT(B1,"0000"),TEXT(Sheet1!A1:A13,"000") &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"0000"),1))
ただし品番001で数量<99の場合はエラーとなります。また品番003以降があると最後の行のデータを検索します。ですからダミーで品番003、数量0-1、単価0の行を入れて下さい。
この回答への補足
早速ありがとうございます。
教えて頂いたとおりに試してみましたら無事出来ました。
ただ実際には品番はA205のように、ローマ字1文字+3桁の数字としており、このような品番が約500アイテム分ございます。
また数量は1桁から最大5桁まで商品ごとにあります。単価は2桁~4桁までです。
以上の条件でも作動させるには、教えて頂いた数式のどの部分を修正すればいいでしょうか?
度々すみませんがよろしくお願い申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
都道府県穴埋めゲーム
都道府県の名前を1人1つずつ投稿してください。全ての都道府県が出たら締め切ります!
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセル関数 数量により3種の単価設定があり数量を入力すると該当する単価を表示させる方法が知りたい
Excel(エクセル)
-
数量・会社ごとに異なる単価表から、関数を使って抽出したい
会計ソフト・業務用ソフト
-
エクセル 数量顧客ごとに異なる単価について
Excel(エクセル)
-
-
4
エクセルで顧客毎に単価が違う場合の入力
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの警告について
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
Excelでの文字色
-
ワークシートに出現したこの画...
-
EXCELの散布図で日付が1900年に...
-
OFFSET関数を使用したいのです...
-
エクセルでファイルの最終更新...
-
エクセルの文字が途中から消える
-
エクセルデーターから必要な項...
-
Excel 大小比較演算子による「...
-
SUBTOTALは、参照された数字で...
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
Excelについて教えてください。...
-
エクセルVBA 月の中で、月~土...
-
Excelの数字の前に入っている空...
-
Excelの関数について このよう...
-
セルの数を求めたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報