関数(数式)を使ってデータを並べ替える:Excel関数の技


スポンサードリンク


並べ替えのキーに重複がない場合 並べ替えのキーに重複がある場合

並べ替えについて  topへ

例1:並べ替えのキーに重複がない場合

SORT関数を使って並べ替える 作業列を使う方法

SORT関数を使って並べ替えます

  1. 合計得点の高い順に得点を取り出します。
    SORT関数の構文:=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
    J3セルに =SORT(B3:H11,7,-1) と入力するだけです。


    関数の引数を利用する場合は
  2. 合計得点の低い順に取り出します
    J3セルに =SORT(B3:H11,7) と入力するだけです。
    並べ替えの順序は昇順なので引数を省略することができます。入力する場合は =SORT(B3:H11,7,1) とします。

作業列を使って並べ替えます

  1. P3セルに =LARGE($H$3:$H$11,ROW(A1)) と入力して、下方向へコピーします。
  2. 元のデータから、合計が一致する行の値をそれぞれ取り出せば完成となります。
    J3セルに =INDEX(B$3:B$11,MATCH($P3,$H$3:$H$11,0)) と入力して、列方向へコピーし、さらに行方向にコピーします。
    INDEX関数で取り出すデータ範囲を指定し、MATCH関数で「合計」が完全一致する行を求めてデータを取り出しています。
    複合参照になっていますので「$」の付いている位置に注意してください。
  1. P3セルに =SMALL($H$3:$H$11,ROW(A1)) と入力して、合計の小さい方から順番に取り出します。
    J3:O11の数式は上記と同じで変更はありません。

例2:並べ替えのキーに重複がある場合

作業列を使う方法 SORT関数を使って並べ替える

SORT関数を使って並べ替えます

  1. 合計得点の高い順に得点を取り出します。
    Microsoft 365ではSORT関数が使えます。
    J3セルに =SORT(B3:H11,7,-1) と入力するだけです。
  2. 合計得点の小さい順に得点を取り出します。
    J3セルに =SORT(B3:H11,7) と入力するだけです。

作業列を使って並べ替えます

  1. 合計得点の高い順に得点を取り出します。
    1. 作業列のI3セルに =H3+B3/100 と入力して、下方向へコピーします。
      B3/100 とデータに重みを付けているのですが、合計の最小変動値「1」を超えないような値になるように1/100にしました。
      「合計」が「1」の差で順位が変動するので、この値にならない数値を加えて同値のデータに軽重の差をつけています。
    2. 書き出す先にも作業列を設けました。
      Q3セルに =LARGE($I$3:$I$11,ROW(A1)) と入力して、下方向へコピーしています。
    3. 元のデータから、作業列の値が一致する行の値をそれぞれ取り出せば完成となります。
      J3セルに =INDEX(B$3:B$11,MATCH($Q3,$I$3:$I$11,0)) と入力して、列方向へコピーし、さらに行方向にコピーします。
      • INDEX関数で取り出すデータ範囲を指定し、MATCH関数で「作業列」が完全一致する行を求めてデータを取り出しています。
  2. 合計得点の小さい順に得点を取り出します。
    1. 作業列のQ3セルに =SMALL($I$3:$I$11,ROW(A1)) と入力して、合計の小さい方から順番に取り出します。
      J3:P11の数式は上記と同じで変更はありません。
      • 得点の合計が同じ場合はNoが小さい方から表示されます。

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数の技 »  関数(数式)を使ってデータを並べ替える

 PageViewCounter
 Counter
 Since2006/2/27