[[20210223105449]]　『【条件あり】数値のランキングの上位下位を抽出す』（シュガー）　ページの最後に飛ぶ

『【条件あり】数値のランキングの上位下位を抽出する』（シュガー）

お世話になります。

最終的にＤ列で上位及び下位5位までを抽出する以下表のような結果を出したいのですが、条件が多い為、C列とD列でどのような式を組み合わせれば良いのかわかりません。

条件は以下の通りです。

・A列(数値)の順位をC列の式で表示するが、その際、B列(対象)がブランクとA列がエラーのものは除外する。

・D列(上位&下位)に上位5位までと下位5位までを抽出する。

・A列が同値の場合でも、順位は飛ばさずに順位付けしたい。その際、C列で順位を飛ばさずに順位付けできなくても、D列でできれば問題はないとする。

・作業列は追加せず、関数を入れるのはC列、D列のみ。

数値対象順位上位&下位
#DIV/0 ●
#DIV/0 ●
100 ● 1 1
100
85.06 ● 2 2
76.54 ● 3 3
64.39 ● 4 4
64.39
57.17 ● 5 5
40.36 ● 6
37.15 ● 7
29.47 ● 8
10.69 ● 9
10.69 ● 10
5.12
5.05 ● 11
5.05 ● 12
4.98 ● 13
3.84 ● 14
3.03
2.06 ● 15
2.04
1.98 ● 16
1.95 ● 17
0 ● 18 18
0
△0.99 ● 19 19
△0.99 ● 20 20
△4.39 ● 21 21
△11.97 ● 22 22

※△はマイナス

以上、ご教示の程宜しくお願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 サンプルからすると、データは降順になっていると言う前提でいいですか？

 (1) C1セル =IF(AND(ISNUMBER(A1),B1<>""),COUNT(INDEX(A$1:A1/(B$1:B1<>""),0)),"")
 (2) D1セル =IF(C1="","",IF(MEDIAN(C1,6,MAX(C:C)-5)=C1,"",C1))

 それぞれ下にコピー

（半平太） 2021/02/23(火) 12:25

早速にありがとうございます。

サンプルはわかりやすく降順に並べていますが、本来はランダムな配列です。
その場合、教えていただいた式に調整が必要になりますでしょうか？

（シュガー） 2021/02/23(火) 12:40

 ＞サンプルはわかりやすく降順に並べていますが、

 そういう無神経なサンプルを提示する質問にはお答えできません。

 他の回答者のレスをお待ちください。

（半平太） 2021/02/23(火) 12:52

半平太様

折角ご回答いただきましたのに、私の質問内容に問題がありし訳ありませんでした。
（シュガー） 2021/02/23(火) 13:04

半平太さんが戻ってきてくれるまでのツナギで

 A・B列をテーブル設定可能であれば、
 Power Queryで、C・D列に順位をテーブル出力できます。
 数式と違って、ただコピペすれば使えるのではないので
 無視していただいて結構です。

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"数値", type any}, {"対象", type text}}),
    追加されたインデックス = Table.AddIndexColumn(変更された型, "インデックス", 0, 1, Int64.Type),
    変更された型1 = Table.TransformColumnTypes(追加されたインデックス,{{"数値", type number}}),
    置換されたエラー = Table.ReplaceErrorValues(変更された型1, {{"数値", null}}),
    並べ替えられた行 = Table.Sort(置換されたエラー,{{"数値", Order.Descending}}),
    追加された条件列 = Table.AddColumn(並べ替えられた行, "対象2", each if [数値] = null then 0 else if [対象] = null then 0 else 1),
    グループ化された行 = Table.Group(追加された条件列, {"対象2"}, {{"group", each _, type table [数値=nullable number, 対象=nullable text, インデックス=number, 対象2=number]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([group],"index",1)),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "行数", each Table.RowCount([カスタム])),
    追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "上位", each 5),
    追加されたカスタム3 = Table.AddColumn(追加されたカスタム2, "下位", each [行数]-[上位]),
    削除された他の列 = Table.SelectColumns(追加されたカスタム3,{"カスタム", "上位", "下位"}),
    #"展開された カスタム" = Table.ExpandTableColumn(削除された他の列, "カスタム", {"インデックス", "対象2", "index"}, {"インデックス", "対象2", "index"}),
    追加された条件列1 = Table.AddColumn(#"展開された カスタム", "順位", each if [対象2] = 1 then [index] else null),
    追加された条件列2 = Table.AddColumn(追加された条件列1, "上位＆下位", each if [index] <= [上位] then [順位] else if [index] > [下位] then [順位] else null),
    並べ替えられた行1 = Table.Sort(追加された条件列2,{{"インデックス", Order.Ascending}}),
    削除された他の列1 = Table.SelectColumns(並べ替えられた行1,{"順位", "上位＆下位"})
 in
    削除された他の列1

（マナ） 2021/02/23(火) 15:17

マナ様

ご教示いただき有難うございます。
（シュガー） 2021/02/23(火) 16:25

コメント返信：

[ 一覧(最新更新順) ]

[[20210223105449]] 『【条件あり】数値のランキングの上位下位を抽出す』（シュガー） ページの最後に飛ぶ

[[20210223105449]]　『【条件あり】数値のランキングの上位下位を抽出す』（シュガー）　ページの最後に飛ぶ