[[20041021135357]]　『男女別の順位一覧表』（Amaru）　ページの最後に飛ぶ

『男女別の順位一覧表』（Amaru）

 下記のような男女混在している商品別の売上表があります。
 そのデーターには男女合計の総合順位はあるのですが、別表として商品別の男女別の売上TOP3を表示したいのです。
 いろいろ試してみたのですが、関数を使ってすることは可能でしょうか？よろしくお願い致します。（WindowsXP　Excel2002）

 　　A　　　B　　C　　　　D　　　　E　　　F　　　　G　　　　H　
 1　氏名　性別　商品A　総合順位　商品B　総合順位　商品C　総合順位
 2　田中　　男　　　10　　　1　　　　2　　　　5　　　1　　　　6
 3　鈴木　　女　　　3　　　 5　　 　10　　　　1　　　2　　　　5
 4　林　　　男　　　8　　 　2　　　  4　　　　3  　　10　　　 1
 5　佐藤　　女　　　6　　　 4　　　　8　　　　2　　　5　　　　2

 別表
 　　　　　商品A
 　　順位　氏名　個数　総合順位
 男　1位　田中　　10　　　　1
 男　2位　林　　　 8　　　　2
 女　1位　鈴木　　 3　　　　5
 女　2位　佐藤　　 6　　　　4　→の商品別です。

 配列数式をつかってできると思うのですが・・・
 あまり詳しくは無いので
http://hp.vector.co.jp/authors/VA014071/tips/hairetsu.html
 ここを参考にしてみてください。

 ひとまかせの(Null)

 ばたばたしてて遅くなりました。
売上表をシート1、別表をシート2として考えています。
   A     B         C        D        E
 1 商品A 	Sheet1最終行：	5	
 2  	順位	氏名	個数	総合順位
 3 男	1	田中	10	1
 4 男	2	林	8	2
 5 男	3	#NUM!	#NUM!	#NUM!
 6 女	1	佐藤	6	4
 7 女	2	鈴木	3	5
 8 女	3	#NUM!	#NUM!	#NUM!
A1には、売上表と同じ商品名を入力。『Sheet1最終行：』の有無はご自由に。
2行目には、項目を入力。　A3:B8には、手入力で男女の1,2,3位をセットします。
 D1セルで売上表のデータ範囲（行数）を出します。（>>範囲に変動があると思いましたので）
セルに何も入力されていない個数＝行数として考えています。IF関数で空白にしている場合もカウントされます。
=COUNTA(Sheet1!A:A)
 D列で男女の各順位の個数を求めます。
=LARGE(IF(INDIRECT("Sheet1!$B$1:$B$"&$D$1)=$A3,INDIRECT(ADDRESS(1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE),,,"Sheet1")&":"&ADDRESS($D$1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE)))),$B3)
Ctrl,Shift,Enterキーを一緒に打鍵して、配列数式として確定してください。
 C列で上記の個数および性別を元に氏名を求めます。
=INDEX(INDIRECT("Sheet1!$A$1:$A$"&$D$1),MATCH($A3&$D3,INDIRECT("Sheet1!$B$1:$B$"&$D$1)&INDIRECT(ADDRESS(1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE),,,"Sheet1")&":"&ADDRESS($D$1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE))),FALSE))
これも同様に配列数式として確定してください。
 E列では、性別（A列）・氏名（C列）を元に、値を探します。
=SUMPRODUCT(($A4=INDIRECT("Sheet1!$B$1:$B$"&$D$1))*($C4=INDIRECT("Sheet1!$A$1:$A$"&$D$1)),INDIRECT(ADDRESS(1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE)+1,,,"Sheet1")&":"&ADDRESS($D$1,MATCH($A$1,Sheet1!$A$1:$H$1,FALSE)+1)))
範囲が変動するだろうと考えてINDIRECT関数を使い長くなっています。
固定範囲ならば、該当する範囲に変更してください。また、D1セルも不要となります。
該当する順位が存在しない場合は、エラー値#NUM!が返ります。
また、同個数が存在する場合には、対応させていません。　（考えるのが面倒だったのでゴメン！）
データ量によっては、大変重たくなります。
ご質問の意図に合っているか『？』ですが、こんな感じです。(sin)

 sin様、Null様ありがとうございます！早速試してみましたが、完璧です！
 丁寧に細かく数式の意味まで教えていただいたので、自分なりに確認しつつ
 他にも利用できればと思います。
 数式って奥が深いですね。本当に有難うございました。（Amaru）

 お役に立てたようで一安心です。
ついでと言っては何ですが、同個数が存在する場合に、対応させたものもアップしておきます。
ここでは、上の行に有るものを上位と考えています。条件によっては、計算式が変わります。
シート1に作業列を設けます。
 J2セルにシート2のA1セルで指定した商品の個数の存在セル範囲を表示させます。
=ADDRESS(1,MATCH(Sheet2!$A$1,$A$1:$H$1,FALSE))&":"&ADDRESS(Sheet2!$D$1,MATCH(Sheet2!$A$1,$A$1:$H$1,FALSE))
 J3セルにシート2のA1セルで指定した商品の個数が存在するセルの列ナンバーを表示させます。
（A列＝１、B列＝２、C列＝３、・・・）
=MATCH(Sheet2!$A$1,$A$1:$H$1,FALSE)
 K1セルには『該当商品仮個数』とでも項目を入力し、
K2セル以下に順位付けの為の仮個数を表示させます。K2セルに入力後下方コピー。
=IF($A2="","",(INDIRECT(ADDRESS(ROW(),$J$3))&"."&100-COUNTIF(INDIRECT(ADDRESS(1,$J$3)&":"&ADDRESS(ROW(),$J$3)),INDIRECT(ADDRESS(ROW(),$J$3))))*1)
男女合わせた任意の商品全ての中で、各同個数に対し、それぞれ100個までは対応します。
それ以上が予想される場合は、『100』の部分を『1000』にすれば、1000個まで対応できます。
現状の個数が5個の場合で、1個目が5.99、2個目が5.98、3個目が5.97の様になります。
 シート2は、前出と同様にD1セルに入力し、
C3,D3,E3セルに下記の式を入力し、全て配列数式として確定してください。そして下方コピー。
 C3　　　　　　　　　　　　　　　　 ~~~~~~~~~~~~~~~~~
=INDEX(INDIRECT("Sheet1!$A$1:$A"&$D$1),MATCH(LARGE(IF(INDIRECT("Sheet1!$B$1:$B$"&$D$1)=$A3,INDIRECT("Sheet1!$K$1:$K$"&$D$1)),$B3),INDIRECT("Sheet1!$K$1:$K$"&$D$1),FALSE))
 D3
=INDEX(INDIRECT("Sheet1!"&Sheet1!$J$2),MATCH(LARGE(IF(INDIRECT("Sheet1!$B$1:$B$"&$D$1)=$A3,INDIRECT("Sheet1!$K$1:$K$"&$D$1)),$B3),INDIRECT("Sheet1!$K$1:$K$"&$D$1),FALSE))
 E3
=INDEX(OFFSET(INDIRECT("Sheet1!"&Sheet1!$J$2),,1),MATCH(LARGE(IF(INDIRECT("Sheet1!$B$1:$B$"&$D$1)=$A3,INDIRECT("Sheet1!$K$1:$K$"&$D$1)),$B3),INDIRECT("Sheet1!$K$1:$K$"&$D$1),FALSE))
前回の式と似たような感じです。E3は、OFFSET関数で一列右に代えてます。
以上です。順位付けの条件等ありましたら、分かる範囲であればご協力します。(sin)

 sin様、重ね重ねありがとうございます！今回は同個数の物は無かったのですが、
今後他にも利用させていただきたいと思いますので、いろいろ触ってみたいと思います。
 とっても勉強になります。いろいろ有難うございました。（Amaru）

コメント返信：

[ 一覧(最新更新順) ]

[[20041021135357]] 『男女別の順位一覧表』（Amaru） ページの最後に飛ぶ

[[20041021135357]]　『男女別の順位一覧表』（Amaru）　ページの最後に飛ぶ