[[20040202140946]]　『関数の応用』（なな）　ページの最後に飛ぶ

『関数の応用』（なな）

関数の応用だと思いますが教えて下さい。
sheet2のA1～200くらいまでA1に6桁の№、B2に定価、C3に単価の入っている表があります。
sheet1のA1にsheet2の№の順番がバラバラで入っています。B2にsheet2の№と一致した単価を入れたいです。ここまでは、VLOOKUPを使えばいいのですが、さらに、sheet2の単価が0の場合sheet2の定価×0.7の単価が入るようにし、なおかつ赤色で表したいのですがどうすればいいですか？IFを使えばいいのでしょうか？

 VLOOKUP と If と 条件付き書式　でできそうに思えますが、
 数式が苦手な私には、良く分からないです。

 VBA(マクロ)なら簡単に出来ますが、たぶん数式（関数）でも十分出来るんじゃないかな・・

  (INA)

 VLOOKUPは[VLOOKUP関数(e1tw)]↓を見てください。
http://www.excel.studio-kazu.jp/lib/e1tw/e1tw.html

 色をつける件は[条件付書式(e2qw)]↓を見てください。
http://www.excel.studio-kazu.jp/lib/e2qw/e2qw.html

 （kazu）

 Sheet2のレイアウトは
  | A  | B  | C  |
 1| №1|定価|単価|
 2| №2|定価|単価|
じゃなくて
  | A  |
 1| №1|
 2|定価|
 3|単価|
 4| №2|
 5|定価|
 6|単価|
こんな並びになっているんですか？これだとVLOOKUPでは出来ないように思います。

 ・Sheet2のA列を選択して名前ボックスに名前を入力（仮に 範囲 とします）
 ・Sheet1のA2に下記の式を入力

=IF(OR(A1="",ISERROR(MATCH(A1,範囲,0))),"",IF(INDEX(範囲,MATCH(A1,範囲,0)+2)>0,INDEX(範囲,MATCH(A1,範囲,0)+2),INDEX(範囲,MATCH(A1,範囲,0)+1)*0.7))

 ・A2の条件付き書式を下記のように設定し、書式で赤色を選択
[数式が]　[=INDEX(範囲,MATCH(A1,範囲,0)+2)=0]

 こんな感じで出来るのではないかと思いますが、Sheet2のレイアウトを見直したほうが
良いかもしれないですね。それとも私の考え違いでしょうか？（Yosh!）

あっ、ごめんなさい。Yosh!さんの言うとおり表の説明を間違えました。上記の表が正しいです。。そうしたら、VLOOKUPとIFを使うのですか？教えて下さい。（なな）

 A1～C1に、№、定価、単価だったんですね。それならVLOOKUPとIFで良いです。
基本的には、上記の式でINDEXとMATCHを使用している所をVLOOKUPに置き換えれば良いです。
 ・Sheet2のA～C列を選択して名前ボックスに名前を入力（仮に 範囲 とします）
 ・Sheet1のA2に下記の式を入力

=IF(OR(A1="",ISNA(VLOOKUP(A1,範囲,3,0))),"",IF(VLOOKUP(A1,範囲,3,0)>0,VLOOKUP(A1,範囲,3,0),VLOOKUP(A1,範囲,2,0)*0.7))

 ・A2の条件付き書式を下記のように設定し、書式で赤色を選択
[数式が]　[=VLOOKUP(A1,範囲,3,0)=0]

 kazuさんがリンクしてくれている所も参考にしながら試してみて下さい。（Yosh!）

ありがとうございました。出来ました。ただ、条件付書式で赤色にするのがどうもうまくいきません。ちゃんと式を入れているのに赤色になりません。いくつか赤色になるはずなのに1ヶ所だけ赤色になってくれます。原因がわかりません。（なな）

 表示はちゃんと定価*0.7の値になっているのでしょうか？
 A1,A2を、例えばB1,B2へコピーしたりして使用しているのだと思うのですが、
 B2の条件付き書式の式ではVLOOKUP(B1,範囲,3,0)になっていますか？
 B1でなく、何処かずれたセルを参照したりしていませんか？（Yosh!）

表示はちゃんと定価*0.7の値になります。その値の色を赤色にしたいのですが
できません。（なな）

 VLOOKUPの数式自体はちゃんと動作しているんですね。
 だとしたら、条件付き書式の設定に何か問題があるのだと思います。
 赤色にならないセルを選択して、「書式」－「条件付き書式」で
 条件付き書式の設定が正しく出来ているかを確認してみて下さい。

 単価を表示するセルが、常に№を入力するセルの１行下の同じ列なら、
 A2をコピーして、その他の単価を表示するセルに貼り付けしてみて下さい。
 それでも赤色にならないでしょうか？
（Yosh!）

Yosh!さん、いろいろありがとうございました。赤色にすることが出来ました。
基本的なことを間違えていました。実は、A1の前に列を一行増やしてやっていました。
№は一番左にしなくてはダメですよね。。やっと、表が完成します。ありがとうございました
（なな）

すみません。新たな問題が出てしまいました。
上記の式の中で定価×0.7としていますが、その答えを小数点以下切捨てに
したいのです。後からROUNDDOWNすると条件付書式で赤色にしたところが赤色で
表示されなくなってしまいます。どうする方法がありますか？（なな）

 こんにちは。

 条件付書式を
[数式が] [=VLOOKUP(A1,範囲,3,0)=0]

 数式を

=IF(OR(A1="",ISNA(VLOOKUP(A1,範囲,3,0))),"",IF(VLOOKUP(A1,範囲,3,0)>0,VLOOKUP(A1,範囲,3,0),ROUNDDOWN(VLOOKUP(A1,範囲,2,0)*0.7,0)))

 として私も試してみましたけど、こちらでは条件付書式でちゃんと
赤色になっていますので、ななさんのほうで条件付書式がうまく機能しない
原因がちょっとわからないです。

http://pub.idisk-just.com/fview/JzOFNEcWplMfLN1CpLIxkWg1C26avXiAqqzmLMyBhSNUbL6HW1Ypdyc9f1XdbAak

 ↑とりあえず、こちらに私が使用したテストデータを置いておきますので、
 　ご自身のデータと比較してみてください。（Yosh!）

Yosh!さん、ありがとうございました。分かりやすく教えていただいた
おかげで出来ました！（なな）

コメント返信：

[ 一覧(最新更新順) ]

[[20040202140946]] 『関数の応用』（なな） ページの最後に飛ぶ

[[20040202140946]]　『関数の応用』（なな）　ページの最後に飛ぶ