エクセル2007 関数

質問

画像の添付ができていなかったため、、再投稿です。
よろしくお願いいたします。

添付の表の上の段のような表があるとしまして、下の表の赤字の部分に上の表のA列のどれかの数字を入れると下にずらっと上の表のデータを参照するようにしたいのですが、タオルの部分は他とセルと仕様が違うのでどのような関数を入れたらいいのか分かりません。

上の表はカラーが複数なければ空欄です。

回答No.5

HohoPapa · ベストアンサー

ありゃ、最大５色まであるんですね。

とすると添付画像のレイアウトで、下のような関数になります。
もはや、これらの計算式は、正気の領域を超えますね。

もし、VBAが扱えるなら、VBAの方がまだ
追いかけやすいコードになるだろうと思います。

B17
=INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0)),2,1)

B18
=INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0)),2,1)

B19
=TEXT(INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0)),4,1),"#")

B20
=TEXT(IF(INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+1,1)="",INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+1,4,1),""),"#")

B21
=TEXT(IF(AND(INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+1,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+2,1)=""),INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+2,4,1),""),"#")

B22
=TEXT(IF(AND(INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+1,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+2,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+3,1)=""),INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+3,4,1),""),"#")

B23
=TEXT(IF(AND(INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+1,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+2,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+3,1)="",INDEX($A$1:$A$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+4,1)=""),INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0))+4,4,1),""),"#")

B24
=TEXT(INDEX($A$1:$E$15,MATCH(1,INDEX(1/($A$1:$A$15=$B$16),0)),5,1),"#")

お礼

うわー...本当におっしゃる通りですね。
詳しい方がそうおっしゃるなら、列がAAどころかB〇まであるので私には無理があると思いました。
こんなに考えていただいてとても感謝しています。

回答No.6

mt2015

作業列を使いましょう。
添付の図の例ではD列に１列挿入し、D2セルに↓
=A2*10+1

D3に↓を入れてD3を下にコピーします。
=IF(A3>0,A3*10+1,D2+1)

参照の欄をH1:I9にしました。
I1セルに番号を手入力
I2セルに　=VLOOKUP(I1,A:F,2,FALSE)
I3セルに　=VLOOKUP(I1,A:F,3,FALSE)
I4セルに　=IFERROR(""&VLOOKUP(I$1*10+ROW(A1),D:E,2,FALSE),"")
I4セルのコピーをI4:I8に貼り付け
I9セルに　=""&VLOOKUP(I1,A:F,6,FALSE)

D列の作業列は非表示にします。

お礼

ありがとうございます！
何度もやり直しておっしゃるようにコピペして入力してみたのですがカラー1～5とその下に表示されませんでした＞＜

回答No.4

HohoPapa

1行/1商品　と　2行/1商品　が混在したデータのため、
とても扱いにくいレイアウトです。

なにか事情があってそうしているのかもしれませんが
可能なら、カラー列を複数持ち、
1行/1商品のレイアウトにすることを強く推奨します。

もし、提示のレイアウトで表示するのであれば
下のような計算式になりましょう。

掲示しておきながらナンナンデスが、
今後のことを考えると、
このような計算式で対応するのは無理があるだろうと思います。
それでもよかったら、参考にしてみてください。

なお、品番と商品名は空欄になることを想定していません。
また、１商品にカラーは最大２つしかない前提です。

B8　
=INDEX($A$1:$E$6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0)),2,1)

B9　
=INDEX($A$1:$E$6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0)),3,1)

B10
=TEXT(INDEX($A$1:$E$6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0)),4,1),"#")

B11
=TEXT(IF(INDEX(A1:A6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0))+1,1)="",INDEX($A$1:$E$6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0))+1,4,1),""),"#")

B12
=TEXT(INDEX($A$1:$E$6,MATCH(1,INDEX(1/($A$1:$A$6=B7),0)),5,1),"#")

お礼

ありがとうございます！

回答No.3

msMike

[No.2補足]へのコメント、
》 …を分けなおす作業をなくしたい
ヤッパリ。だから「参考」とした次第。
お示しのように結合セルを不規則チャンポンにするのは私の最も嫌悪する仕業です。
貴方の希望実現に意欲満々みたいな mt2015さんに期待しましょ！
※昨今稀な鮮明な添付図に敬意を表示します。

お礼

考えていただいたのに使うことができなくて本当に申し訳ありません。
ありがとうございました！

回答No.2

msMike

ご参考
添付図参照
B7: =VLOOKUP(B$6,$2:$4,ROW(A2),FALSE)&""

補足

申し訳ありません、上の段の表の形式で人から受け取って、下の表に自分で直してから仕事がしたいので、D,E列にカラーを分けなおす作業をなくしたいのです。

回答No.1

mt2015

補足願います。
・参照したデータの品番が150になっていますが、151の間違いでしょうか。
・カラーは最大何種類ですか。
・色違いの物がある場合、E列の「カラー有り」は必ず入りますか？別な文言になったり入らない場合もありますか？

お礼

補足の訂正の仕方が分からないのでお礼の欄で失礼します。
カラーは最大でも５つぐらいまでになると思います。

補足

すみません、まだ自動でできなかったので手入力して間違えてしまいました。
おっしゃる通り151です。
E列はカラーありかブランクだけです。