1. Home »
2. エクセル関数の技 »
3. 関数(数式)を使ってデータを並べ替える

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

作成:2013年9月2日,更新更新:2020年11月13日

並べ替えについて topへ

• データリストを並べ替える機能として[並べ替え]があります。
  このページでは関数(数式)を使ってデータを並べ替える方法について書いてみます。
• 数式を入力する必要がありますので、元のデータとは別のセルに書き出すことになります。
  [並べ替え]機能は同じデータリスト内でデータの並び替えが実行できるところが大きく異なる点になります。
• 並び順が分かる列(キー)があれば簡単にできます。

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

• 下表のデータを合計得点の高い順に並べ替えます。
  
  B C D E F G H
  2 No 氏名 国語 算数 理科 社会 合計
  3 1 岡田 70 65 71 77 283
  4 2 会田 55 61 66 48 230
  5 3 河合 82 77 83 88 330
  6 4 近藤 72 78 74 73 297
  7 5 井上 61 55 52 65 233
  8 6 木下 86 98 94 87 365
  9 7 植田 68 78 75 66 287
  10 8 佐々木 81 67 71 86 305
  11 9 桑田 62 64 61 69 256

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

• Microsoft 365 ではSORT関数が使えるようになりました。
  SORT関数の詳細な使い方は SORT関数でデータを並べ替える:Excel関数 をご覧ください。
• SORT関数を使うと作業列を使わずに、容易にデータを並べ替えることができます。

1. 合計得点の高い順に得点を取り出します。
   SORT関数の構文:=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
   J3セルに =SORT(B3:H11,7,-1) と入力するだけです。
   
   
   関数の引数を利用する場合は
   • 配列(並べ替える対象)は B3:B11
     並べ替えのインデックスは(合計の列は左から7番目なので)、7
     並べ替えの順序は降順なので、-1
     並べ替えの基準は行で並べ替えるので省略できます。入力したいときはFALSEとします。
     
2. 合計得点の低い順に取り出します
   J3セルに =SORT(B3:H11,7) と入力するだけです。
   並べ替えの順序は昇順なので引数を省略することができます。入力する場合は =SORT(B3:H11,7,1) とします。
   

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

• Microsoft365よりも前のバージョンのExcelでは関数を使ってデータを並べ替えるには、作業列を使う方法が考えられます。

• 合計得点の高い順に得点を取り出します。

1. P3セルに =LARGE($H3ドル:$H11,ドルROW(A1)) と入力して、下方向へコピーします。
   • LARGE関数で大きい値から順に取り出します。LARGE関数の引数の順位には1,2,3・・・と変化するようにしたいのでROW(A1)としています。
2. 元のデータから、合計が一致する行の値をそれぞれ取り出せば完成となります。
   J3セルに =INDEX(B3ドル:B11,ドルMATCH($P3,$H3ドル:$H11,0ドル)) と入力して、列方向へコピーし、さらに行方向にコピーします。
   INDEX関数で取り出すデータ範囲を指定し、MATCH関数で「合計」が完全一致する行を求めてデータを取り出しています。
   複合参照になっていますので「$」の付いている位置に注意してください。

• 得点の低い順に取り出すには?

1. P3セルに =SMALL($H3ドル:$H11,ドルROW(A1)) と入力して、合計の小さい方から順番に取り出します。
   J3:O11の数式は上記と同じで変更はありません。
   

(追記) (追記ここまで)

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

• 例1では「合計」が重複していなかったので、わりと簡単にデータを取り出すことができました。
  「合計」に重複する値がある時にはちょっと小細工が必要になります。
• 作業列を利用してみます。作業列には得点の「合計」と行番号を1/100した値を加えたものを書き出すことにしました。
  得点の合計が同じ場合はNoが大きい方を上にします。(得点は降順、Noは降順の条件とします)
• 例1とは赤文字の部分が異なっています。
  
  B C D E F G H
  2 No 氏名 国語 算数 理科 社会 合計
  3 1 岡田 70 65 71 77 283
  4 2 会田 55 61 66 48 230
  5 3 河合 82 77 83 88 330
  6 4 近藤 72 78 74 73 297
  7 5 井上 61 55 52 65 233
  8 6 木下 86 98 94 87 365
  9 7 植田 68 78 75 76 297
  10 8 佐々木 81 92 71 86 330
  11 9 桑田 62 64 61 69 256

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

1. 合計得点の高い順に得点を取り出します。
   Microsoft 365ではSORT関数が使えます。
   J3セルに =SORT(B3:H11,7,-1) と入力するだけです。
   
   • もし、元の配列B3:H11がNo順に並んでいなかったりするときは、下図のように同点でもNo順になりません。
     (同点のNoが3,8 と 7,4 と並んでいます)
     
     ↓
     =SORT(SORT(B3:H11,1,1),7,-1) と、No順に並べ替えたデータSORT(B3:H11,1,1)を、合計得点の降順に並べ替えます。
     (同点のNoが3,8 と 4,7 と並んでいます)
     
2. 合計得点の小さい順に得点を取り出します。
   J3セルに =SORT(B3:H11,7) と入力するだけです。
   
   • 同点の場合、Noの降順に並べ替えたい場合は、先にNoの降順に並べ替えてから、得点の昇順で並べ替えます。
     J3セルは =SORT(SORT(B3:H11,1,-1),7) としています。
     

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

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

スポンサードリンク
(追記) (追記ここまで)

---

(追記) (追記ここまで)

---

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

PageViewCounter
Counter
Since2006/2/27