xls-hashimotoのExcel学

xls-hashimoto mougモーグ 回答集


回答0013　【Office 2021】Excelの「スピル」に対応した関数群を
従来関数で実現するための関数式の組み合わせの例

impressのできるネットに
「【Office 2021】Excelの「スピル」に対応した関数群の使い方。
多数のデータをまとめて処理できる！
Dekiru_Net」
との記事が掲載されました。
記事によるとExcel 2021では、スピルを活用した関数として
以下の6つが追加されたそうです。
・FILTER
・SORT
・SORTBY
・UNIQUE
・SEQUENCE
・RANDARRY
記事の中で、それぞれの機能や使い方が説明されています。

それらの新関数の機能をExcel2019以前のExcelで
VBAを使わずに関数で実現するか一例を紹介します。

FILTER
「FILTER」は条件に一致する行を表示する関数だそうです。
これをExcel2019以前のExcelで
VBAを使わずに関数で実現する一例が
次のExcelファイルです。

FILTER
「FILTER.xlsx」(Excel 18kB)

式は通常の書式では次の様になります。
=IFERROR(INDEX(A:A,ROUND(1/LARGE((OFFSET($C$1,1,,COUNT($C:$C),1)>60)/ROW(OFFSET($C$1,1,,COUNT($C:$C),1)),ROW()-1),0)),"")
これでは目で判読しずらいので、改行と字下げを加えると次の様になります。
=IFERROR(
         INDEX(
               A:A,
               ROUND(
                     1
                     /
                     LARGE(
                           (OFFSET($C$1,1,,COUNT($C:$C),1)>60)
                           /
                           ROW(OFFSET($C$1,1,,COUNT($C:$C),1)),
                           ROW()-1
                          ),
                     0)
              ),
              
         "")
マイクロソフトの「FILTER関数」のサポートページがこちらです。
FILTER 関数を使用すると、定義した条件に基づいてデータの範囲をフィルター処理できます。
このページの計算例に対する計算式も先ほどのExcelファイルに示してあります。

SORT
「SORT」はデータを並べ替えて取り出す関数だそうです。
これをExcel2019以前のExcelで
VBAを使わずに関数で実現する一例が
次のExcelファイルです。

SORT
「SORT.xlsx」(Excel 25kB)

式は通常の書式では次の様になります。
=INDEX(A:A,MATCH(LARGE(OFFSET($C$1,1,,COUNT($C:$C),1),ROW()-1),OFFSET($C$1,1,,COUNT($C:$C),1),0)+1)
これでは目で判読しずらいので、改行と字下げを加えると次の様になります。
=INDEX(
       A:A,
       MATCH(
             LARGE(
                   OFFSET($C$1,1,,COUNT($C:$C),1),
                   ROW()-1
                  ),
             OFFSET($C$1,1,,COUNT($C:$C),1),
             0
            )
       +1
      )
マイクロソフトの「SORT関数」のサポートページがこちらです。
SORT 関数では、範囲または配列の内容を並べ替えます。
このページの計算例に対する計算式も先ほどのExcelファイルに示してあります。

SORTBY
「SORTBY」もデータを並べ替えて取り出す関数だそうです。
これをExcel2019以前のExcelで
VBAを使わずに関数で実現する一例が
次のExcelファイルです。

SORTBY
「SORTBY.xlsx」(Excel 23kB)

式は通常の書式では次の様になります。
=INDEX(A:A,MATCH(SMALL(COUNTIF(OFFSET($B$1,1,,COUNT($C:$C),1),"<="&OFFSET($B$1,1,,COUNT($C:$C),1))*10^5+(1000-OFFSET($C$1,1,,COUNT($C:$C),1)),ROW()-1),COUNTIF(OFFSET($B$1,1,,COUNT($C:$C),1),"<="&OFFSET($B$1,1,,COUNT($C:$C),1))*10^5+(1000-OFFSET($C$1,1,,COUNT($C:$C),1)),0)+1)
これでは目で判読しずらいので、改行と字下げを加えると次の様になります。
=INDEX(
       A:A,
       MATCH(
             SMALL(
                   COUNTIF(
                           OFFSET($B$1,1,,COUNT($C:$C),1),
                           "<="
                           &OFFSET($B$1,1,,COUNT($C:$C),1)
                          )
                   *10^5
                   +(1000-OFFSET($C$1,1,,COUNT($C:$C),1)),
                   ROW()-1
                  ),
             COUNTIF(
                     OFFSET($B$1,1,,COUNT($C:$C),1),
                     "<="
                     &OFFSET($B$1,1,,COUNT($C:$C),1)
                    )
             *10^5
             +(1000-OFFSET($C$1,1,,COUNT($C:$C),1)),
             0
            )
       +1
      )
マイクロソフトの「SORTBY関数」のサポートページがこちらです。
SORTBY 関数は、範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
このページの計算例に対する計算式も先ほどのExcelファイルに示してあります。

UNIQUE
「UNIQUE」は重複するデータをまとめて表示する関数だそうです。
これをExcel2019以前のExcelで
VBAを使わずに関数で実現する一例が
次のExcelファイルです。

UNIQUE
「UNIQUE.xlsx」(Excel 29kB)

式は通常の書式では次の様になります。
=IFERROR(INDEX(OFFSET($B$1,1,,COUNTA($B:$B)-1,1),ROUND(1/LARGE((MATCH(OFFSET($B$1,1,,COUNTA($B:$B)-1,1),OFFSET($B$1,1,,COUNTA($B:$B)-1,1),0)=(ROW(OFFSET($B$1,1,,COUNTA($B:$B)-1,1))-1))/ROW(OFFSET($B$1,1,,COUNTA($B:$B)-1,1)),ROW()-1)-1,0)),"")
これでは目で判読しずらいので、改行と字下げを加えると次の様になります。
=IFERROR(
         INDEX(
               OFFSET($B$1,1,,COUNTA($B:$B)-1,1),
               ROUND(
                     1
                     /
                     LARGE(
                           (
                            MATCH(
                                  OFFSET($B$1,1,,COUNTA($B:$B)-1,1),
                                  OFFSET($B$1,1,,COUNTA($B:$B)-1,1),
                                  0
                                 )
                            =
                            ROW(OFFSET($B$1,,,COUNTA($B:$B)-1,1))
                           )
                           /
                           ROW(OFFSET($B$1,,,COUNTA($B:$B)-1,1)),
                           ROW()-1
                          ),
                     0
                    )
               ),

         ""
        )
マイクロソフトの「UNIQUE関数」のサポートページがこちらです。
UNIQUE 関数は、一覧または範囲内の一意の値の一覧を返します。
このページの計算例に対する計算式も先ほどのExcelファイルに示してあります。

SEQUENCE
「SEQUENCE」は任意の配列を作成できる関数だそうです。
これをExcel2019以前のExcelで
VBAを使わずに関数で実現する一例が
次のExcelファイルです。
ただし、この様な計算をする機会が無かったので的外れの計算式かもしれません。

SEQUENCE
「SEQUENCE.xlsx」(Excel 29kB)

式は通常の書式では次の様になります。
=IF(AND(ROW()=1,COLUMN()=1),1,$A$1+(ROW()-1)*30+(COLUMN()-1)*3)
これでは目で判読しずらいので、改行と字下げを加えると次の様になります。
=IF(
    AND(ROW()=1,COLUMN()=12),
    1,
    
    $L$1+(ROW()-1)*30+(COLUMN()-12)*3
   )
マイクロソフトの「SEQUENCE関数」のサポートページがこちらです。
SEQUENCE 関数では、1、2、3、4 など、配列内の連続した数値の一覧を生成することができます。
このページの計算例に対する計算式も先ほどのExcelファイルに示してあります。


「xls-hashimoto mougモーグ 回答集」に戻る

トップページに戻る

Copyright(C) xls-hashimoto 2011-2023 All Rights Reserved.