[[20110221181113]]　『自動で色番号を出るように登録したい』（りさ）　ページの最後に飛ぶ

『自動で色番号を出るように登録したい』（りさ）
エクセルで　
Ａ1のセルに　品番を入れるた場合に　その品番で必要な色番号が　B1 C1 D1 E1 F1 G1 のセルに　自動的に出るように　設定をすることはできますか？
品番によって　必要な色番号の数はバラバラです
（例：　Ａ　という品番は　5色　　Ｂという品番は　7色　という感じです）

他のシート等で　事前にデーター登録をしておけば可能ですか？

 他のシートに参照表を作成しておき、VLOOKUP等で呼び出ししては
 いかがでしょうか？
 色番号というのがどのようなものかわかりませんが、具体例を示していただけると
 もう少し詳細にアドバイスできるかと思います。

 (Yujin)

返信ありがとうございます。Ａ1　のセルに　品番：ＡＡＡＡＡＡ　　Ｂ1のセルに　カラー：　黒　Ｃ1：白　Ｄ1：黄色　Ｅ１：赤　Ｆ1：緑　　
という形で　　品番を入力したら　Ｂ1以降のセルに　自動でカラーが出るようにしたいのですが
できるんでしょうか？
品番によって　カラーの数は異なってきます。

参照表を作成しておいた場合でも
品番を入力して　計算式を入れてあげないと　カラーは出てきませんよね？

 入力するシートと、品番と色の定義のあるシートのそれぞれのセル構成を
 詳細に説明してはどうでしょうか。
 その方が具体的な回答が得られると思います。

 また、カラーといっているのは色名だけで実際の色を表示したいわけではないです
 よね？そのあたりも、一言説明があると良いかと思います。

 文字だけであれば式でできると思いますし、マクロでも対応できます。
 色の最大色が決まっているのであれば、その分だけ式を入れておけば、空白は
 空白として出るだけなので、VLOOKUP だけでいけるとは思いますが。
 (Mook)

品番の定義：最大10桁の数字とアルファベットの組み合わせ　　色の定義：　ＣＡ　ＢＫ　など　アルファベットで表記（最大25色展開で　カラーの文字数は最大5文字）　になります。

定義のシートに　は　品番とカラーと定価を記入しておく。
　
Ａ１のセルには　品番　Ａ２のセルに定価　　Ｂ1　Ｃ1のセル等にカラーが出てくるようにしたいです。

品番を入力した際に　展開している色や定価が自動的にエクセルに出てくれば　いちいちカラー等を手入力する手間がはぶけていいな　と思いました。

なので　Ｂ2のセルには　数字が入ります。

品番　　　　　カラー

○○○○　　　ＣＡ　ＢＫ　ＲＥ　ＭＵＳ

￥3500　　　　　1　　　2　　5　　　6　　　　

こういう形にしたいです。
説明不足ですいません。。。。

何か　いい方法を教えて下さい。

 (1)品番と色を定義した範囲を選択して、名前（仮に「色表」）をつけます。
 (2)品番を入力する欄がA2、色表示をする範囲がB2からZ2 だとすると
    B2=IF($A2="","",VLOOKUP($A2,色表,COLUMN(B2),FALSE))
    として、C2からZ2までコピー。
 でどうでしょうか。
 名前で定義する範囲の列数、式の列数より大きくしておかないと参照エラーになる
 ので十分大きな範囲（25列？）で定義してください。
 (Mook)

 イマイチわかってない部分もあるのでちょっとまとめたいと思います。
 まず、参照表のシートですが、以下のような作りでいいですか？

 [参照表]シート
	A	B	C	D	E	F	G
 1	品番	定価	色1	色2	色3	色4	
 2	AAAAA	3500	ＣＡ	ＢＫ	ＲＥ	ＭＵＳ	
 3	BBBBB	4000	ＭＵＳ	ＢＬ			
 4	CCCCC	5000	ＣＡ	ＢＫ	ＭＵＳ		
 5

 次にデータを入力・表示するシートですが、以下のようでいいですか？

 [データ表示]シート
	A	B	C	D	E	F	G
 1	品番	色1	色2	色3	色4	
 2	AAAAA	ＣＡ	ＢＫ	ＲＥ	ＭＵＳ	
 3	\3500	
 4		
 5

 で、求めたいのはデータ表示シートのA2セルに品番を入力すればA3,B2～E2に
 結果が表示されるようになればいいですか？
 また、B3～E3セルには数字が入るそうですが、これは手入力？それとも参照する？
 私がよく理解できていないだけですので、違っていれば指摘してください。

 (Yujin)

ありがとうございます。
とてもわかりやすですね。
ここに書いてある通りです。
Ｂ3～Ｅ３は　手入力になります。
宜しくお願い致します。

 B2=IF(OR(VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0))),"",VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0))

 右方向にフィルコピー。

 A3=IF(ISNA(VLOOKUP(A2,参照表!$A$2:$F$5,2,0)),"",VLOOKUP(A2,参照表!$A$2:$F$5,2,0))

 一応エラー処理しています。
 範囲はご自分の表に合わせて変更してください。
 Mookさんご提案の名前の定義はオススメです。

 (Yujin)

Yujinさんの方法で　A2に品番を入力すると　Ｂ2から右側はカラー　Ａ３には　定価が表示されるように
できましたが　Ａ４に計算式をコピーしても　データーを拾ってきてくれません。。。。
何が間違っているんですかね？

 A3の式には絶対参照をかけていませんので・・。
 A4セルにコピーすると =IF(ISNA(VLOOKUP(A3,参照表!$A$:$F$5,2,0)),"",VLOOKUP…
　                           　　　　 ^^^^
 という風に^^^の部分が変わっていると思います。

 A3=IF(ISNA(VLOOKUP($A$2,参照表!$A$2:$F$5,2,0)),"",VLOOKUP($A$2,参照表!$A$2:$F$5,2,0))
                    ^^^^                                   ^^^^
 絶対参照をかけることでこの式はどこにコピーしてもA2セルの値を基に検索をかけに行きます。

 この際ですから絶対参照と相対参照を覚えておかれるといいですよ。
 http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.html

 (Yujin)

すいません。間違えました。
Ｂ2の数式を10行位下方向にコピーして
Ａ４に品番を入れても　Ｂ4に正しいカラーが反映されないんです。

Ａ2は　参照表に記入している　どの品番を入力しても　きちんと反映してくれます。

 ええと、同じことなんですが、
 B2=IF(OR(VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0))),"",VLOOKUP($A$2,参照表!$A$2:$F$5,COLUMN(C1),0))

を

 B2=IF(OR(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0))),"",VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0))

 としてからコピーしてみてください。
 式の中の　VLOOKUP($A$2,参…　を　VLOOKUP($A2,参…　に変えています。
　　　　　　　　　^^^^^^                 ^^^^^

 例えば B1セルに「=A1」と入れて、B1セルをC2セルにコピーするとC2セルの式は「=B2」と
 なっていますよね。これは相対参照といいまして、「一つ左のセルを参照せよ」という意味
 になっています。
 また、列行の記号の前に「$」を入れることで絶対参照となります。
 =A1を=$A1とすれば列が固定され、=A$1とすれば行が固定されます。=$A$1とすれば列行共に
 固定され、どこにコピーしても参照範囲は変わりません。

 (Yujin)

ありがとうございます。
今まで＄を使って固定して使用した事もあったのですが
式が難しすぎて　パニってしまいました。
やっとできました。

ただ　もう1点教えていただきたいのですが
今　この数式を当て込んだエクセルを作りましたが
品番を入れてない場合　数式が入っているセルに　#N/A　が表示されたままになってしまいます。
これを見えないようにするには　どうしたらいいですか？

何度もすいませんが　教えて下さい。
宜しくお願い致します。
　

ＶＬＯＯＫＵＰを使用する際には　どうしても　でてしまうものなんでしょうか？

 =IF(品番セル="","",今の式)

 衝突しました。。

 IF($A2="","",今の式)
 として未入力の場合の処理を追加してはいかがでしょうか。

 (Yujin)

今　
B2=IF(OR(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0))),"",VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0))
この状態で計算式を組んでいます。
これの　どこにいれたらいいんでしょうか？

すいません　何度も何度も・・・・

 ええと（汗）
 そのままですよ。
 =IF($A2="","",IF(OR(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0))),"",VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)))

 がんばってくださいね！

 (Yujin)

 ちょっと済みませんが。。。。
 ＞OR(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,ISNA(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)))
 こうなっていると、ISNA(・・・)の所の意味が無くなってしまいませんか？
 ISNAでTRUE になっても、その前の VLOOKUP(・・・)=0 が #N/A に成ってしまうので。。。

 (HANA)

 HANAさまご指摘ありがとうございます。

 この場合に関してなんですけど、#N/Aが返ってくるのは検索値が無かった場合（当然ですが・・）
 に対してのISNAです。もうひとつのVLOOKUP(･･･)=0は検索値に対して色数が少なかったときの
 見栄えのためだけに入れていたんです。

 例えば、色1・色2はあるが色3・色4に何も無かった場合、色3・色4は0が返ってくるため、
 それを良くしようとしたものです。

 (Yujin)

 そういった事で作ってあるのは分かっていますが、機能してませんよね？
 =OR(#N/A=0,ISNA(#N/A)) の結果は 「TRUE」ではなく「#N/A」なので。

 　　そもそもこれが機能していたら、品番を入れていなくても
 　　#N/A なんて表示されないはず。

 VLOOKUP関数の戻り値が
 0だった時か、#N/A だった時に「""」を返そうと思って作成された式ですよね？
 ・・・と読んだのが違うのかな。。。？

 (HANA)

 HANAさま

 おはようございます。
 先ほど検証しました。ほんとですね。機能してませんでした。
 よく考えないとダメですね・・（反省）

 B2=IF(ISNA(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)),"",IF(VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)=0,"",VLOOKUP($A2,参照表!$A$2:$F$5,COLUMN(C1),0)))

 というふうにネストしました。

 (Yujin)

Yujin様　HANA様　色々ありがとうございました。
できました～。
かなり難しい計算式で　なんとな～く意味はわかりますが
自分で作るというレベルには程遠いので。。。。。。
大変勉強になり　助かりました。

ありがとうございます。

コメント返信：

[ 一覧(最新更新順) ]

[[20110221181113]] 『自動で色番号を出るように登録したい』（りさ） ページの最後に飛ぶ

[[20110221181113]]　『自動で色番号を出るように登録したい』（りさ）　ページの最後に飛ぶ