LET関数の高度な使い方2 SUBTOTAL関数とCHOOSE関数をまとめる Excel2021 Microsoft 365

LET関数の高度な使用例 Excel2021 Microsoft 365

オプションボタンを数式と連動させながら、さらにLET関数でSUBTOTAL関数とCHOOSE関数をまとめる

LET関数を使い、オプションボタンでSUBTOTAL関数を切り替える数式 10回

LET関数でSUBTOTAL関数を切り替える数式

今回のサンプルではオプションボタンを使っていますが、このオプションボタンを数式と連動させるためにオプションボタンをグループ化しています。
そして選択したオプションボタンのナンバーが表示されるようにしてあります。
グループ化してセルと関連付けるという方法は以下のページで紹介しています


オプションボタンをグループ化し連動させる オプションボタンを数式で連動


SUBTOTAL関数の集計方法をオプションボタンのチェックで切り替える

  1. オプションボタンをクリックすることで合計と最小最大平均を自由に選択できます。 数式はそれに連動するようにして、LET関数を使って作成しましょう。合計をチェックすると選択は市最小を選択すると選択は2と表示されます。

    LET関数の高度な使い方2、タイトル01
  2. オプションボタンを利用して数式を切り替えるために使う関数は、SUBTOTAL関数です。 SUBTOTAL関数(サブトータル関数)はたくさんの集計機能を持っています。
    どの機能を使うかは数字で指定することができます。

    LET関数の高度な使い方2、タイトル02
  3. SUBTOTAL関数の構文
    SUBTOTAL(集計方法, 参照1, 参照2, ...)
    引数の集計方法と機能は下記の表になります。

    集計方法集計機能同等の関数
    1または101平均値を求めるAVERAGE
    2または102数値の個数を求めるCOUNT
    3または103データの個数を求めるCOUNTA
    4または104最大値を求めるMAX
    5または105最小値を求めるMIN
    6または106積を求めるPRODUCT
    7または107不偏標準偏差を求めるSTDEV.S
    8または108標本標準偏差を求めるSTDEV.P
    9または109合計値を求めるSUM
    10または110不偏分散を求めるVAR.S
    11または111標本分散を求めるVAR.P
  4. まず、合計なら集計方法は、9です。最小は5,最大は4,平均は1を数値で指定します。

    LET関数の高度な使い方2、タイトル03
  5. 合計を出す場合は、9と入力、範囲は対象となるD6:D22を指定します。=SUBTOTAL(9,D6:D22)

    LET関数の高度な使い方2、タイトル04
  6. これで合計の回数を取得できますが、1つの式で4通りに切り替えたいのです。 そこでSUBTOTAL関数の集計方法を切り替えるために、CHOOSE関数を使います。

    LET関数の高度な使い方2、タイトル05

LET関数を使わない方法

  1. 以前のLET関数を使わない場合の方法です。
    こちらは、関数の意味とLET関数でどの数式をまとめるのかという理解につながるので、確認しておきましょう。
    CHOOSE関数の構文
    CHOOSE(インデックス, 値1, 値2, ...)
    引数インデックスの後に続く、引数値1,値2,値3,...に対応する何番目の値を選ぶのかを指定してやるという関数です。

    LET関数の高度な使い方2、タイトル06
  2. CHOOSE関数の数式はどこに入力しても良いのですが、見えない位置か、もしくはフォントの色を白に設定しておくと良いでしょう。

    LET関数の高度な使い方2、タイトル07
  3. SUBTOTAL関数の引数、集計方法に、このサンプルではF4セルを指定してやることにします。

    LET関数の高度な使い方2、タイトル08
  4. オプションボタンで集計方法を変更すると、表示される値が変わります。

    LET関数の高度な使い方2、タイトル09

LET関数で2つの式をまとめる

  1. SUBTOTAL関数の数式とCHOOSE関数の数式2つがシートに入力されています。これを1つの数式にまとめましょう。

    LET関数の高度な使い方2、タイトル10
  2. =LET(名前1,式1,名前2,式2,...,計算)
    何度も出てくる、値や式に名前をつけます。まず、範囲D6:D22にrngと名前をつけ、D6:D22を指定してやります。
    続いて、オプションボタンで選択した値の入ったセルは、select と名前をつけ、H2セルを指定します。

    LET関数の高度な使い方2、タイトル10
  3. CHOOSE関数の数式で得た集計方法の番号をagreeとしました。agreeは数式 CHOOSE(select,9,5,4,1) を指定します。
    最後に式、SUBTOTALagree,rng)

    LET関数の高度な使い方2、タイトル10
  4. 2つの数式をすっきりとまとめることができました。

    LET関数の高度な使い方2、タイトル10
  5. 長い数式になるので、 Alt + Enter で改行すると、見やすくなり、内容の理解も容易です。

    LET関数の高度な使い方2、タイトル10