Excel関数で違った答えになってしまいます
Excelで商品の管理をしているのですが、
関数を入れても答えが違ってしまいます。
[Sheet1]・・・コード入力シート
(A1)(A2)・・・商品コード
(B1)(B2)・・・数量
[Sheet2]・・・データベースシート
(A列)・・・商品コード
とあり、データベースの価格を
数量により変化させます。
例えば
商品コード(as400)の価格を
数量100個以上なら10円、100個未満だと15円
としたいのです。
作成した関数は
IF(OR(AND('Sheet1'$A$1=A1,'Sheet1'$B$1>=100),AND('Sheet1'$A$2=A1,'Sheet1'$B$2>=100)),10,15)
としました。
ただこれだと、[Sheet1]の1行目は正しく表示されるのですが
2行目になると、数量を1個にしても価格が10円になってしまいます。
どのようにすれば良いか悩んでいます。
よろしくお願いします。
No.1ベストアンサー
- 回答日時:
具体例が無いので誤解しているかもしれませんが、添付の図の様にSheet2のB列に単価を表示したいと言う事ですかね?
添付の図の場合、Sheet2のB1に↓と入れて下にオートフィルしています。
=IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))
No.2
- 回答日時:
全て絶対参照で指定されていますので、当然の事ながら他のセルへその関数をコピペしても常にその結果はシート1のA1がシート2のA1と一致していてシート1のB1が100以上、もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10となります。
ご質問の内容からは、各シートのA列がどのような並びになっているのか判断できません。
もしもシート1とシート2が全く同一の並びになっているようであれば、ANDやORを使うまでも無く、
=IF((Sheet1!B1>=100),10,15)
をシート2のB1に登録した上でB列を下へコピペしてゆけばよろしいかと思いますが、シート1と2ではA列の並びが全く異なっているようであれば、vlookup関数等を使用する必要があると思います。
参考URL:http://www11.plala.or.jp/koma_Excel/contents1/ma …
この回答への補足
すみません、
ANo.2が2つあり、mt2008様に返信してしまいました。
申し訳ございませんでした。
「シート1のA1がシート2のA1と一致していてシート1のB1が100以上、
もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10」
となるはずが、
「シート1のA2がシート2のA1と一致していてシート1のB2が100以下の場合でも10」
となってしまうのです。
よろしくお願いします。
No.3
- 回答日時:
No1です。
画像が上手く添付できなかったので再チャレンジ
この回答への補足
Sheet1はコード入力用のシートですので、行数は増えません。
Sheet2はデータベースとしてのシートです。
Sheet1で入力したコード及び数量を基に
Sheet2より「商品コード」「商品名」「数量」「価格」等を抽出
別シートにその内容を反映させて印刷する。
といった手順で使用しています。
書かれている
「シート1のA1がシート2のA1と一致していてシート1のB1が100以上、
もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10」
となるはずが、
「シート1のA2がシート2のA1と一致していてシート1のB2が100以下の場合でも10」
となってしまうのです。
書き忘れていたのですが、最初私が書いた関数はSheet2の価格の列に書いている関数です。
すみません、
なぜか回答2が2つあり、補足がずれてしまいました。
本当に申し訳ございませんでした。
ひとつお伺いしたいのですが、
=IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))
の関数の後半
10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
の部分の説明をして頂けないでしょうか?
前半部分の意味と、VLOOKUPの意味はわかるのですが・・・
よろしくお願いします。
No.4
- 回答日時:
データベースとなるシート2を整備することが必要でしょう。
A列には商品コードを1行おきに入力します。
例えばas400の名前をA2セルに入力し、B1セルには0と、C1セルには100と入力します。また、B2セルには1個から99個までのときの価格で15と入力します。C2セルには100個以上のときの価格で20と入力します。
このように2行を使って1行目のB列から右の列には数量による価格が変わるその数量を、2行目には商品コードと数量が変わった時の価格を入力します。数量による価格の変動は仮にE列まで使用して設定することにします。
二つ目の商品コードについても3行目と4行目を使って入力します。
以下同様です。
なお、数量による価格の変動が無い場合には1商品当たり2行は使いますが数量を各行は空欄のままにします。
そこでシート1についてですがA列には商品コードをB列には数量を入力するとして、その時の価格をC列に表示させるとしたらC1セルには次の式を入力して下方にオートフィルドラッグコピーします。
=IF(OR(A1="",B1=""),"",IF(INDEX(Sheet2!A:E,MATCH(A1,Sheet2!A:A,0)-1,3)="",INDEX(Sheet2!A:E,MATCH(A1,Sheet2!A:A,0),2),INDEX(Sheet2!A:E,MATCH($A1,Sheet2!A:A,0),MATCH($B1,INDIRECT("Sheet2!A"&MATCH($A1,Sheet2!A:A,0)-1&":E"&MATCH($A1,Sheet2!A:A,0)-1),1))))
これで商品コードあたりで、数量当たりで、その価格を自由に設定でき、シート1ではそれが反映された形で価格が表示されます。
この回答への補足
Sheet2はすでにデータベースとしてほぼ完成しており、
価格列を増やすとその他の列に使用している関数も全て見直しとなります。
現在実務運用しながらなので、できればデータベースの価格列以外を触らずに解決したいと考えております。
ただMATCHとINDEXは忘れていました。
何か利用できるか考えてみます。
No.5
- 回答日時:
ANo.1です。
> =IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))
> の関数の後半
> 10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
> の部分の説明をして頂けないでしょうか?
> 前半部分の意味と、VLOOKUPの意味はわかるのですが・・・
説明不足でしたね。失礼しました。
論理式の結果のTRUE/FALSEがセル式では1/0として扱える事を利用しています。
まず、VLOOKUP(A1,Sheet1!A:B,2,FALSE) で、商品コードに対応した数量を得ています。
この数量が100未満(<100 が成立)なら 5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
の結果は5、100以上(<100が不成立)なら0になります。
この結果に10を足して15あるいは10にしています。
なるほど、すごく良くわかりました。
ご親切にありがとうございました。
出来ればもう一つ、
VLOOKUPを使うとなれば、必ずA列からの記述が必要ですよね?
例えばSheet1の商品コード入力がE列の5行目と6行目にあり
数量入力がF列の5行目と6行目となっていた場合、
(A列や1行目には他の記述がある)
どうすれば良いでしょうか?
あつかましいお願いですが、どうぞよろしくご教授ください。
No.6
- 回答日時:
>例えば商品コード(as400)の価格を数量100個以上なら10円、100個未満だと15円としたいのです。
要するに数量に対して10円にするか15円にするかを設定したいのでしょうか?
だとするなら単純に【D2】セルを使って「=IF(C2>=100,"10円",IF(C2<=100,"15円",""))」という式になりますねぇ・・・?
Sheet1のA列「商品コード」の返し方は名前を定義しての方法でしょうか?
Sheet2のデータからですと「商品コード」しか入力されていないようですが・・・?
取り合えず単純な数式ですので間違っていましたらそのまま通過してください ^^;
他の方の回答にも返事したのですが、
シート1はあくまでも入力用
シート1で入力した値を基に
シート2のデータベースから値を抽出し
別シート(例えばシート3)に結果を出力し印刷
という流れで運用しています。
また商品や価格は変動しますので、上記数式では対応できません。
あくまでもシート2の価格列の数字を変更したいのです。
No.7
- 回答日時:
ANo.1です。
> VLOOKUPを使うとなれば、必ずA列からの記述が必要ですよね?
> 例えばSheet1の商品コード入力がE列の5行目と6行目にあり
> 数量入力がF列の5行目と6行目となっていた場合、
> (A列や1行目には他の記述がある)
> どうすれば良いでしょうか
そんな事はありません。
Vlookupの2つ目の引数はセル範囲を指定します。私が提示したサンプルがA列B列全てを範囲としているに過ぎません。
Sheet1!A:B を Sheet1!E5:F6 に変えれば済む話です。
この回答への補足
数式を入力してみましたが、やはりダメでした。
[データベース設定Sheet2]
500個以上・・・1,110円
500個未満・・・1,140円
[入力Sheet1]
H10・・・商品コード1
H12・・・1(1つめの数量)
H14・・・商品コード2
H16・・・1(2つめの数量)
[計算結果Sheet3]
商品コード1:1個:1,140円
商品コード2:1個:1,110円
となってしまいます。
何故でしょうか?
No.8
- 回答日時:
こんばんは!
横からお邪魔します。
色々良い回答は出ていますので、参考程度で目を通してみてください。
余計なお世話かもしれませんが、商品によって単価が変わる境界数に違いがあるのではないかと思います。
それに対応するための方法です。
↓の画像の右側がSheet2になります。こちらに表を作っておきます。
画像では勝手に商品コードを入れ、
as400は100個が境界・BB250は50個が境界・CD100は50個おきの境界としてみました。
実際必要な表は薄い水色部分だけですが、判りやすくするために
B列(未満)を表示しています。
そして、Sheet1のC2セルに
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet2!$C$8:$E$11,MATCH(B2,Sheet2!$A$8:$A$11,1),MATCH(A2,Sheet2!$C$7:$E$7,0)))
という数式を入れ、オートフィルで下へコピーすると
数量によって単価が変化します。
以上、参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m
>余計なお世話かもしれませんが、商品によって単価が変わる境界数に違いがあるのではないかと思います。
その通りです。
商品によって基準となる「単価」「単価が変わる境界数」がいろいろあります。
最初はINDEXやMATCHを使用する事も考えたのですが、
あまりにも数が多く、一覧表の作成だけで膨大な数になってしまいます。
だから数式で対応出来ればと思い、試行錯誤していました。
言葉足らずを補って頂きまして、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA For Each 〜 複数条件について 3 2022/10/20 20:05
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelはなんで先頭の0を消すん...
-
Excel元に戻す方法を教えてくだ...
-
【Microsoft Office Excel Comp...
-
Excelが固まってしまった。
-
西暦や和暦の表示をyyyymmdd表...
-
Excel 2019 のピボットテーブル...
-
【関数】スペースがいくつ入っ...
-
【Excel】セル内の時間帯が特定...
-
excelの不要な行の削除ができな...
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excelのセルを飛ばして入力する
-
Excel初心者です。 詳しい方、...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
【マクロ】エクセルにかいてあ...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシート クエリ関数 1...
-
エクセルで指定した日付、店舗...
-
Excelのグラフ軸について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報