エクセル関数について再質問

質問

再度質問します。よろしくお願いします。ちなみに、エクセル2007です。
セルA1に10、B1に11、C1に12、D1に13、E1に100、A2に14、B2に15、C2に16、D2に17、E2に200、同様にA3に18、A4に22、E3に300、E4に400と、値が入っています。（この値は、その都度変わります）、そして、セルA10に＝A1、B10に＝B1、C10に＝C1、D10に＝D1、E10に＝E1、・・・・　　　　　　D13に＝D4,E13に＝E4と入力しています。
このとき、１０,１１,１２,１3行のE10,E11,E12,E13の値を優先キーとして、降順でセルA20～に１０～１３行の値だけを自動的に移したいのですが。
今度は分かってもらえましたかね？・・・・・・・
よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

回答No.1とは別の方法として、作業列を使わずに済む、次の様な方法もあります。

　まず、A20セルに次の関数を入力して下さい。

=IF(ROWS($20:20)>COUNT($E$10:$E$13),"",IF(INDEX($A$10:$E$13,SUMPRODUCT(ISREF($E$10:$E$13)*(COUNTIF(OFFSET($E$10,,,ROW($E$10:$E$13)-ROW($E$10)+1),LARGE($E$10:$E$13,ROWS($20:20)))<ROWS($20:20)-COUNTIF($E$10:$E$13,">"&LARGE($E$10:$E$13,ROWS($20:20)))))+1,COLUMNS($A:A))="","",INDEX($A$10:$E$13,SUMPRODUCT(ISREF($E$10:$E$13)*(COUNTIF(OFFSET($E$10,,,ROW($E$10:$E$13)-ROW($E$10)+1),LARGE($E$10:$E$13,ROWS($20:20)))<ROWS($20:20)-COUNTIF($E$10:$E$13,">"&LARGE($E$10:$E$13,ROWS($20:20)))))+1,COLUMNS($A:A))))

　そして、A20セルをコピーして、A20～E23の範囲に貼り付けて下さい。

　以上です。

　尚、上記の関数の中で使用されているSUMPRODUCT関数は、繰り返し処理を行う関数であるため、行数が多くなりますと処理が重くなるという短所があります。
　１０～１３行の場合、その中に含まれている行数は4行しかありませんから、SUMPRODUCT関数を使用しても処理が重くなりはしませんが、もし、処理の対象とする行範囲が2000行以上にもなる様な場合には、SUMPRODUCT関数では処理に時間が掛かり過ぎる様になってきますから、注意して下さい。

お礼

私には、大変でしたがなんとか出来ました。
見事に並び替えできるものですね。感動しました。
これで、寝ることができます。
ありがとうございました、・・・・・おやすみなさい！！！！

回答No.1

kagakusuki

何故、1行目～4行目に入力されているデータと同一のデータを、態々10行目～13行目に表示させてから、並べ替えの元データとして扱うのか解りませんが、取り敢えずその事は横に置いておいておきます。
　御質問文内の例の様に、E列に入力されている値は全て数値データであるとしますと、次の様な方法となります。

　まず、使用していない列の中の適当な列(ここでは仮にG列とします)を作業列として使用するものとし、その列の10行目のセル(G10セル)に次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($E:$E,ROW())),RANK(INDEX($E:$E,ROW()),$E$10:$E$13,0)*10000000+ROW(),"")

　次に、G10セルをコピーして、G11～G13の範囲に貼り付けて下さい。
　次に、A20セルに次の関数を入力して下さい。

=IF(ROWS($20:20)>COUNT($G$10:$G$13),"",IF(INDEX($A:$E,MOD(SMALL($G$10:$G$13,ROWS($20:20)),10000000),COLUMNS($A:A))="","",INDEX($A:$E,MOD(SMALL($G$10:$G$13,ROWS($20:20)),10000000),COLUMNS($A:A))))

　そして、A20セルをコピーして、A20～E23の範囲に貼り付けて下さい。

　以上です。