教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010-2016:データを詰めて別領域に取り出すSMALL関数

 

 

 


 

 

 


 

 

 

データを別領域に取り出す方法としてSMALL関数の使い方を覚えましょう。

=SMALL(配列,○番目)

配列の中で小さい方から数えて○番目を返す

=INDEX(配列,○行,○列)

配列の中で○行と○列が交差するセルの値を返す

★ここではあえてSMALL関数を使いますが、別領域にデータを取り出すにはフィルタオプション(数式も利用可)が最も簡単です。


例:空白セルを詰めて別領域に取り出す

SMALL関数は配列の中で、○番目に小さい数値を返します。

空白以外のセルの行番号(1,3,4,6) をSMALL関数で小さい順に取り出し(1,3,4,6が詰めて表示)それらをINDEX関数の行番号として指定することで値を取り出せます。

 

{=INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW()))}

  1. A列で空白以外の行番号を出す

  2. 1の行番号を1,2,3…の順(行番号を利用。タイトル行がある場合はROW()-1などして順番を調整)で取り出す(行番号を小さい順に取り出し)

  3. 2で取り出した行番号をINDEX関数の○行部分に引き渡し、指定された値を返す
    → INDEX(A:A,1)  INDEX(A:A,3) …

※実際にはエラーが表示されないようIFERROR(数式,"")を頭に付けてください。

{=IFERROR(INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW())),"")}

 

★★応用1:数値を小さい順に抜き出す★★

条件:5以上の数値を小さい順に取り出す(配列数式なので最後は必ずCtrl+Shift+Enter)

{=IFERROR(SMALL(IF(A:A>=5,A:A),ROW(A1)),"")}

 

★★応用2:別領域にデータを取り出す★★

 

D1(ひよこ)を条件に、F列・G列にデータを取り出します。

 

 F列

{=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15=$D$1,ROW($A$1:$A$15)),ROW()-1)),"")}

 

 G列

{=IFERROR(INDEX($B$1:$B$15,SMALL(IF($A$1:$A$15=$D$1,ROW($A$1:$A$15)),ROW()-1)),"")}

 

★フィルタオプションを利用するなら「動物名」「ひよこ」という条件領域を作成するだけで別領域にデータを取り出せます。

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows