複数条件の番号付けについて

質問

「メーカー」「商品」「販売可否」のある行に対し、以下の条件付きで番号を1から振りたいと考えています。

条件1: メーカー及び商品が重複した行があれば、既に振られた番号にする
条件2: 販売可否が空白の場合は無視する
条件3: 同じメーカーが連続するように番号を振る
条件4: 元の表は並び替えなどの加工は出来ない

一応、添付画像のような形で実現は出来ています。
以下のような式を使っています。
(1)G4:G21 =IF(COUNTIF(C$3:C3,C4)=0,MAX(G$3:G3)+1,VLOOKUP(C4,C$3:G3,5,FALSE))
(2)I4:I21 =IF(COUNTIF(D$3:D3,D4)=0,MAX(I$3:I3)+1,VLOOKUP(D4,D$3:I3,6,FALSE))
(3)K4:K21 =IF(E4<>"",G4*1000+I4,"")
(4)M4:M21 =INT(IF($K4<>"",SUMPRODUCT(($K$4:$K$21<>"")*($K$4:$K$21<=$K4)/COUNTIF($K$4:$K$21,$K$4:$K$21)),0))
(4)で算出される値を得るために、(1)(2)(3)の行を使っています。

ちなみにこの(4)の値を使って、B24:D37の表を作っています。
※画像では省略していますが、メーカー・商品ごとの台数・金額合計の表になります。
この表で使っている式は以下の通りとなります。
(5)B26:B37 =IF(MAX(B$25:B25)+1>MAX($M4:$M21),"",B25+1)
(6)C25:C37 =IFERROR(INDEX($C$4:$E$21,MATCH($B25,$M$4:$M$21,0),1),"")
(7)D25:D37 =IFERROR(INDEX($C$4:$E$21,MATCH($B25,$M$4:$M$21,0),2),"")

質問としては、(4)を求める為に4列使っているものを減らせられないか、理想は1列ですがせめて2列に纏められないかと思っております。
何卒、宜しくお願い致します。

回答No.1

Chiquilin · ベストアンサー

数式で手間と処理時間を掛けてやる必要がないと思います。

■G4セル
=IF(AND(E4="可",COUNTIFS(C$4:C4,C4,D$4:D4,D4,E$4:E4,"可")=1),MATCH(C4,C$4:C4,0)*1000+ROW(),"")
G21セルまでコピー

■C24セル
=IFERROR(INDEX(C:C,MOD(SMALL($G$4:$G$21,$B24),1000)),"")
縦横方向にコピー

外部データの取り込みでやるなら 範囲を名前定義しておいて 定義した範囲を接続
既存の接続データから指定して プロパティの接続文字列を

SELECT Distinct メーカー, 商品
FROM 範囲
WHERE 販売可否 ="可"
AND メーカー Is Not Null

でおしまい。

というかこんな手間をかけなくても ピボットテーブルだけでも済みます。

お礼

ご案内頂いた式を少し改良させて頂く事で、自己解決する事が出来ました。
=INT(IF($G4<>"",SUMPRODUCT(($G$3:$G$21<>"")*($G$3:$G$21<=$G4)/COUNTIF($G$3:$G$21,$G$3:$G$21)),0))

大変助かりました。
有難うございました！

補足

ご回答有難うございます！
仰る通りでして、実際にはかなりの行数になっている為、ACCESSにしてしまった方が楽なのですが、不特定多数の者が入力したり、閲覧したりする関係と、そもそもフォームがエクセル縛りという事から、どうにかエクセルでクリアする必要がありました。また集計シートも決まっている為、ピボットテーブルも使えないのです…

ちなみにですが、G4セルの=IF(AND(E4="可",COUNTIFS(C$4:C4,C4,D$4:D4,D4,E$4:E4,"可")=1),MATCH(C4,C$4:C4,0)*1000+ROW(),"")を入力した場合、販売可否が可であっても、上の行に同メーカー及び同商品が存在する場合、ブランク表示となります。
これをブランクではなく、上の行の同メーカー及び同商品の時の算出した値をそのまま参照するようにしたいのですが、どのようにすれば良いでしょうか？