Excel 縦形式の価格表を横形式に変換したい

質問

お世話になります。
縦形式（JANコード一覧形式）の価格表を、自社システムに当てる為に列方向に変更したいです。
同一の品番に複数の色があり、サイズによって価格が違うことがあります。サイズが違っても価格均一の商品もあります。
価格帯が2つまでなら自分でできるのですが、2つ以上だと難しいです。

添付画像左側の（I～N列）ように、同一商品で最安値から順に列方向に次の価格が並んで欲しいです。
一つの品番に対して色が複数あっても殆どがサイズで価格が変わるだけですが、まれに色によって単価が違うこともあって困ってます。その場合のやり方も教えていただけると助かります。

https://toushitsu-off8.com/excel-chouhuku-naraberu/
こちらのサイト様を参考に途中まで作っていますが（添付画像右側）、行き詰っています。なんか枠からずれてる。
F列を、同一品番の中から下代を昇順で番号を振らないといけないんでしょうが、やり方がわかりません。
私が試している方法よりも良いやりかたがあれば教えてください（VBAやマクロは使えません）。どうかお助けください。よろしくお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

SI299792 · ベストアンサー

すみません。返信が遅れました。
品番順に並んでいる前提です。

F2: =F1+(A1<>A2)
G2: =IF(COUNTIFS(A$2:A2,A2,E$2:E2,E2)=1,E2)
（作業列です。目障りなら非表示にして下さい。）

K1以右: 1 2 …（数字にします）
表示形式、ユーザー定義｢"下代"0｣
I2: =IFERROR(INDEX(A:A,MATCH(ROW()-1,F:F,0)),"")
J2: =IFERROR(INDEX(D:D,MATCH(ROW()-1,F:F,0)),"")
K2: =IFERROR(SMALL(OFFSET($G$1,MATCH($I2,$A:$A,0)-1,,COUNTIF($A:$A,$I2)),K$1),"")
K2を右へコピペ。
纏めて下へコピペ。

お礼

こちらの方法でできました！
なかなか実践する時間が取れずに遅くなってしまい申し訳ありませんでした。
何度もご回答いただき解決まで導いてくださり本当に感謝しています！ありがとうございました！

回答No.2

SI299792

放置してすみません。
数式が複雑すぎる上、この数式では件数制限ができるので（9999位）後程修正したいと思います。
現状で何か問題点はありますか❓

今考えられる問題点は「同一商品で最安値から順」を見落としていたので、上から順に並べていることです。（画像でもそうなっているみたいでした）
最安値順に並べる必要はありますか（現データを見る限り小さい順に並んでいるので、最安値が上に来ているみたいですが）並べる必要があるなら、品番が同じなら、同一商品という事でいいでしょうか。

お礼

すみません、改めて明確に、
＞最安値順に並べる必要はありますか
これは必要ありません。

自宅PCがExcel2021なので、新バージョンの方もやってみました。こちらはうまく表示できました。ありがとうございます！魔法のように、表示されていきました、すごいです。
（会社が旧バージョンなものですみません。。）

商品が違うけど同じ品番がふられている商品は、上から順に価格を取得して、その間に一度出てきた価格はスルーして表示していました。旧バージョンもそういう表示になるのかなと思いました。（ご報告です。）

＜元データ（わかりやすく横並び）＞
品番　（品名）上代　下代1　下代2　下代3
N001　商品A　4,200　1,610
N001　商品B　6,000　2,250　2,350
N001　商品C　4,200　1,610　1,810

↓　表示結果

品番　上代　下代1　下代2　下代3
N001　4,200　1,610　2,250　2,350　1,810

補足

再びありがとうございます。
・データの数は多い時には3万（行）近くなります。
・「同一商品で最安値から順」と最初書いたのは、だいたい同じ品番だったらどの色でも金額が同じ（サイズによって金額が違ってくるだけ）なんですが、ごく稀に色が違うと金額が違う場合があるからでした。色の種類も多いので、色分けまでするとデータを絞る意味がなくなってしまうだろうと思い、全ての色含めて「同一商品で最安値から順」という取得方法にすれば、下代の数が多いと「この品番は色によって金額が違う」というのが見つけ出せるかなと思ったんです。
また、廃盤と現行品番を同じ品番にしている場合があり、その場合も価格設定が違います。
この例は稀なので、無視してもらって前回の考え方のままで大丈夫です。

メーカーからもらう元データは、基本的に上から順に小さいサイズから並びます。金額もそれに比例するので上から最安値になります。

・前回教えていただいた「旧バージョンのやり方でつまづいたので、教えてください。
＞K1～K4: 1 2 3 4 数字にします。
　KIに、数字の「1」、
　K2に「2」、K3に「3」、K4に「4」を入力し、

＞表示形式、ユーザー定義｢"下代"0｣
　K1～K4　のユーザー定義を｢"下代"0｣にし、

＞I2: =IFERROR(INDEX(A:A,MATCH(ROW()-1,F:F,0)),"")
J2: =IFERROR(INDEX(D:D,MATCH(ROW()-1,F:F,0)),"")
K2: =IFERROR(INDEX($E:$E,MATCH((ROW()-1)*10000+K$1,$G:$G,0)),"")

このK2に関数を入力する際に、先ほどK2に入力した数字「2」を消して（上書き）この関数を入力するんですか？

品番・上代・下代1まではうまく表示できましたが、下代2・3・4が空白になってうまく表示できません。

大変ご面倒でしょうが、またお時間がある時にでもご回答いただけると助かります。
よろしくお願いいたします。

回答No.1

SI299792

上代も価格は何種類かありますが、それは無視して一番上だけ出せばいいのですか❓

新バージョン（Excel2021,MicroSoft365）
I1: =UNIQUE(A:A)
Ｉ列：表示形式、ユーザー定義｢#｣
J2: =IFERROR(VLOOKUP(I2,A:D,4,0),"")
K2: =TRANSPOSE(UNIQUE(FILTER(E:E,A:A=I2)))
Ｋ～Ｎ列：表示形式、ユーザー定義｢#,###｣
Ｉ列：表示形式、ユーザー定義｢#｣
J2～K2を下へコピペ。

旧バージョン（Excel2019 以前）
F2: =F1+(A1<>A2)
G2: =F2*10000+(A1=A2)*MOD(G1,10000)+(COUNTIFS(A$2:A2,A2,D$2:D2,D2,E$2:E2,E2)=1)
纏めて下へコピペ。（作業列です。目障りなら非表示にして下さい。）
K1～K4: 1 2 3 4 数字にします。
表示形式、ユーザー定義｢"下代"0｣
I2: =IFERROR(INDEX(A:A,MATCH(ROW()-1,F:F,0)),"")
J2: =IFERROR(INDEX(D:D,MATCH(ROW()-1,F:F,0)),"")
K2: =IFERROR(INDEX($E:$E,MATCH((ROW()-1)*10000+K$1,$G:$G,0)),"")
右へコピペ。
纏めて下へコピペ。

画像は旧バージョン。
バージョンは書いて下さい。

お礼

ご回答いただきありがとうございます！
2倍のお手間をお掛けしてすみませんでした。時間が出来次第すぐに、自分で試してみます。また報告させていただきます。

補足

情報不足で失礼しました。

Excel2016 です。
上代は、同一品番の最安値を表示したいです。基本的に、その品番の一番の行が最安値になっていますので、一番上の行を表示で問題ないと思います。