残業を減らす！Officeテクニック

エクセルで作成した数式が複雑過ぎ!? LET関数で整理してみよう

今井孝

2025年9月1日 06:55

担当ごとに集計して「役職順」に並べ替えるには？

窓の杜から
ダウンロード

　日々の業務では、予算や実績のデータを合計したり、件数を数えたりする場面がよくありますよね。必要に応じて、並べ替えや絞り込みをして、さらに関数を使って処理する作業はよくあるワークフローです。

　例えば、以下のように「担当ごとに合計を出して一覧にする」処理は定番。これは、UNIQUE関数とSUMIFS関数、HSTACK関数の組み合わせで簡単に実現できます。

担当ごとに予算を集計する例。セルG2には「=HSTACK(UNIQUE(C2:C11),SUMIFS(E2:E11, C2:C11, UNIQUE(C2:C11)))」と入力してある

　セルG2には以下の数式が入力してあります。UNIQUE関数で一意の「担当」を取り出した配列と、SUMIFS関数で担当ごとの予算の合計値を求めた配列をHSTACK関数で横方向につなげています。

=HSTACK(UNIQUE(C2:C11),SUMIFS(E2:E11, C2:C11, UNIQUE(C2:C11)))

　ところが一歩踏み込んで「部長→課長→係長→一般」のように、この結果を役職の序列順に並べたいとなると、急に数式が複雑になります。SORTBY関数で指定できる並べ替え順は「昇順／降順」なので、任意の順序に並べ替えるには、SWITCH関数を利用することになります。例えば以下のような数式です。

=SORTBY(
  HSTACK(
    INDEX(UNIQUE(C2:D11),,1),
    SUMIFS(E2:E11, C2:C11, INDEX(UNIQUE(C2:D11),,1))
  ),
  SWITCH(INDEX(UNIQUE(C2:D11),,2),"部長",1,"課長",2,"係長",3,"一般",4), 1
)

　読みやすいように折り返して記述しています。役職は複数回出現するので、UNIQUE関数でセルC2～D11から重複のない結果（配列）を取り出して、INDEX関数で列を指定して利用しています。おそらく、この数式を見た時点で嫌になる人がほとんどでしょう。

　最初の数式の結果を「値」として貼り付け直して、ユーザー設定リストを利用する方法を使うのも1つの手ですが、メンテナンスしにくいのも事実です。今回は、このように複雑になってしまう数式をLET関数を用いて、読みやすく整理する方法を紹介します。

LET関数の基本
繰り返し使う値や処理を「変数」として定義する

LET関数の基本

　まずは、LET関数の基本的な動作を見てみましょう。構文は以下の通りです。［名前］［名前値］［計算］の3つの引数が指定されていれば成り立ちます。プログラミングの経験があれば［名前］＝変数名、［計算］＝処理と考えると理解しやすいでしょう。

LET関数の構文。引数は［名前］と［名前値］の組み合わせと［計算］で指定する。［名前］と［名前値］の組み合わせは126組まで指定可能

　例えば、予算をすべて合計してプロジェクト数で割り、平均の予算を求める場合は以下のように記述できます。

=LET(
  count, COUNTA(B2:B11),
  sums, SUM(E2:E11),
  sums / count
)

　単純に「=SUM(E2:E11) / COUNTA(B2:B11)」という数式の結果と同じなので、あまりメリットを感じられませんが、冒頭のような処理には役立ちます。

LET関数を使った記述例。数式バーをドラッグして広げておくと入力しやすくなる

　なお、［名前］はLET関数の中でのみ有効です。「A1」や「B1」といったセル番地はNG、先頭に数字は使えない制約があります。また、Excelの関数名との重複は問題ありませんが、［名前］の重複はできません。

繰り返し使う値や処理を「変数」として定義する

　冒頭の処理をLET関数で書き換えると以下のようになります。UNIQUE関数やINDEX関数を繰り返し利用しているのが、数式を難しくしている要因です。これらの処理をLET関数の［名前値］として定義し、わかりやすい［名前］を付けます。全体の処理であるSORTBY関数は最後に［計算］として指定しています。

=LET(
  pairs, UNIQUE(C2:D11),
  names, INDEX(pairs,,1),
  roles, INDEX(pairs,,2),
  sums,  SUMIFS(E2:E11, C2:C11, names),
  rank,  SWITCH(roles,"部長",1,"課長",2,"係長",3,"一般",4),
  data,  HSTACK(names, sums),
  SORTBY(data, rank, 1)
)

　「names」と「roles」で利用しているINDEX関数は、「pairs」に定義したUNIQUE(C2:D11)の結果、つまりセルC2～D11の一意の配列を対象に、1列目と2列目を取り出しています。「sums」で、SUMIFS関数で担当ごとの合計を求めて、「rank」で役職を数値に変換、「data」で「names」と「sums」をつなげています。

　改行やインデントはなくても動作しますが、数式バーを広げて入力するとわかりやすいでしょう。