データをExcelシート形式に抽出する設計ソフトウェアがあります。出力は2列に分割され、各列には1000行以上あります。このデータを利用するには、2つの列の両方から最大5つの最高値にデータを要約する必要があります。したがって、これは、1つの列とそれに対応する値の最大値であることを意味するのではなく、列1の2番目に大きい値と列2の4番目に大きい値を意味する場合があります。
たとえば(出力データの一部を引用した場合):
ここで選択する必要がある値は次のとおりです。
それを達成するための可能な方法があれば、それは素晴らしいでしょう
ありがとう..
サンプルファイル:http://goo.gl/UIEFEv
サンプルファイル2:http://goo.gl/VSvuVf
これが公式の解決策です。20行を使用し、各列の上位5つを含む行を抽出しました。必要な数の行に拡張できます。
A1:B20のデータを使用して、D1でこの式を使用し、CTRL+ SHIFT+で確認しENTER、E1にコピーして両方の列にコピーします。
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
注:一部の行には両方の列の上位5つの値が含まれているため、抽出される行は8つだけです。より明確に説明するために、列Aと列Bに強調表示を追加しました
以下のスクリーンショットを参照してください
編集:
以下のコメントから、その列の最大値を含む行と、両方の列の合計が最大の行の組み合わせが必要なようです。
In the original formula there are two conditions joined with "+", i.e.
($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)
The "+" gives you an "OR"
type functionality, e.g. in this case rows are included if individual values are in the top 5 in that particular column. You can add other conditions, so if you want to also add any rows which are in the top 5 considering the total of both columns then you can add another "clause", i.e.
($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5))
....and including that in the complete formula you get this version:
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5))+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
You could refine that further by using combinations of +
and *
(for AND
), e.g. for the new condition you might only want to include rows with a total in the top 5 if one of the single values is in the top 10 for that column...
Explanation:
The above part shows how you can use +
for the OR
conditions. In the formula if those conditions are TRUE
then the IF
function returns the "relative row number" of the range (using ROW(A$1:A$20)-ROW(A$1)+1
).
SMALL function then extracts the kth smallest value, k being defined by ROWS(D$1:D1) which starts at 1 in D1 (or E1) and increments by 1 each row.
INDEX
function then takes the actual value from that row.
修飾行が不足すると、SMALL
関数は#NUM!
エラーを返しますが、IFERROR
ここでは空白に変換されます
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加