本記事では、Excel 2021を使用して検証および画像の作成を行っています。
合計といえばSUM関数！ でもそれだけじゃない！
「Excelで合計を出すなら、SUM関数でしょ?」
そう思ったあなた、大正解！
ほとんどの場合、SUM関数が最も簡単で分かりやすい方法です。
でも、ちょっと待ってください！
Excelの世界は奥深いです。
合計を計算する方法はSUM関数だけではありません。
「え、SUM関数以外でどうやって合計するの？しかも、演算子の”+“も使わずに？」
そんな少し変わった挑戦をしてみます！
今回の記事では、普段ならまず使わないであろうテクニックを紹介します。
それは「SUM関数や演算子を使わずに合計を求める」ことです。
ここでは、テクニックを5つ紹介します。
目的は、
様々な関数の使い方や、数式の組み立て方の「引き出し」を増やすこと！
AGGREGATE関数ってこんなこともできるんだ！
AVERAGEとCOUNTの組み合わせ！？
文字列操作や行列計算で合計が出せるなんて！
きっと、そんな発見があるはずです。
もちろん、実務では素直にSUM関数を使うのが一番です（笑）
でも今回は、あえて関数だけでどこまでできるか、その限界に挑んでみましょう！
この関数パズルを通して、あなたのExcel関数への理解がさらに深まるかもしれませんよ！
今回のミッション
現在の状態とゴール
現在の状態: まず、ExcelシートのA列 (A2:A26) に、25個の数値データが入力されている状態です。
目指すゴール: 次に、上記のA2:A26に入力された数値を、Excelの「関数」だけを使って合計します。
そして単一のセルに表示させます。
ルール：関数縛り＆データ変更禁止！
1. 単一数式: 結果は、1つのセルに入力する1つの数式で求めること。
2. 元データ変更禁止: A2:A26の元の数値データは一切変更・加工しないこと。
3. 演算子禁止: 演算子の使用は禁止！ (例: =A2+A3+... や =A2*A3 はNG)
4. Excelバージョン: Excel 2021で利用可能な関数のみを使用。
5. 関数制限: 方法2以降、使用できる関数に厳しい制限が課せられます！
データの準備
まず、新しい空白のExcelシートを用意してください。
次に、合計したい数値データを準備します。（練習なので値は何でも構いません。）
そして、それらの数値をA列の A2セル から A26セル に入力します。
もしくは、
サンプルファイル excel_de_himatsubushi010.xlsx (10KB)をダウンロードします。
さあ、これで準備はできましたか？
SUM関数の呪縛から解き放たれる、合計計算の冒険に出発しましょう！
方法1：制限なし！ …って、あれ？王道のSUM関数！
考えかた
まずは肩慣らしです。
ルール上はどんな関数でもOK！
となれば、やはり合計の王道、SUM関数の登場ですよね。
基本中の基本ですが、ここからスタートしましょう！
数式
合計結果を表示したいセル（例: C2セル）に、以下の数式を入力します。
=SUM(A2:A26)
解説
これは最もシンプルで一般的な合計の求め方です。
具体的には、SUM() のカッコ内に、
合計したいセル範囲 A2:A26 を指定するだけです。
すると、Excelが自動的に範囲内の数値をすべて足し合わせてくれます。
メリット＆デメリット
メリット:
- とにかく簡単で分かりやすい！
- 計算が高速です。
- 誰が見ても一目で合計だと理解できます。
デメリット:
- 特にありません。あえて一つくらい挙げたいのですが…。見つかりません。
方法2：SUM系関数 使用禁止！ AGGREGATE関数で集計！
考えかた
さて、ここからが本番です！
はじめに、使用禁止の関数を確認しましょう。
最初の制限は「SUM系の関数はすべて使用禁止」です。
例えば、SUM、SUMIF、SUMIFS、DSUM、SUMPRODUCT などが該当します。
つまり、「SUM」と名の付く関数は使えません。
そこで登場するのが、集計の万能選手 AGGREGATE関数 です！
【使用禁止関数】
- SUM,SUMIF,SUMIFS,SUMPRODUCT,SUMSQ,SUMX2MY2,SUMX2PY2,SUMXMY2,DSUM,IMSUM,SERIESSUM（以後、まとめてSUM系と書きます）
手順
数式入力
まず、結果を表示したいセル（例: C3セル）に、以下の数式を入力します。
=AGGREGATE(9, 4, A2:A26)
解説
AGGREGATE関数は、様々な種類の集計を行うことができる非常に高機能な関数です。
例えば、合計、平均、個数、最大値、最小値などが可能です。
まず、第1引数の9は「集計方法」を指定します。
具体的には、9は「合計」を表します。
次に、第2引数の4は「オプション」を指定します。
4 は「エラー値を無視する」というオプションです。
他にも非表示の行を無視するなど、様々なオプションがあります。
そして、第3引数のA2:A26は集計対象のセル範囲です。
これにより、A2:A26の範囲にある数値を合計します。
さらに、もし範囲内に #N/A などのエラー値があっても、
それを無視して計算してくれる優れものです。
メリット＆デメリット
メリット:
- SUM関数の強力な代替となります。
- エラー値や非表示行を無視するオプションがあり、実務でも役立つ場面が多いです。
- 合計以外にも様々な集計ができます。
デメリット:
- 引数が多く、SUM関数に比べると少しだけ複雑に見えます。
- どんなオプションがあるか覚える必要があります。
方法3：さらにSUBTOTAL, AGGREGATEも禁止！ 平均と個数から合計を導く！
考えかた
続いて、制限が厳しくなってきました！
SUM系に加え、集計処理系の関数、
つまり、SUBTOTAL関数や先ほど使ったAGGREGATE関数も禁止です。
どうしましょうか…？
ここで発想を転換してみましょう！
数学的に考えれば、合計値 = 平均値 × データの個数 で求めることができますよね？
この関係を利用してみましょう！
【使用禁止関数】
- SUM系
- SUBTOTAL
- AGGREGATE
手順
数式入力
まず、結果を表示したいセル（例: C4セル）に、以下の数式を入力します。
=PRODUCT(AVERAGE(A2:A26), COUNT(A2:A26))
解説
この数式は3つの基本的な関数を組み合わせています。
まず、AVERAGE(A2:A26): AVERAGE関数で A2:A26 の範囲に含まれる数値の平均値を計算します
次に、COUNT(A2:A26): COUNT関数で A2:A26 の範囲に含まれる「数値データ」の個数を数えます
最後に、PRODUCT(..., ...): PRODUCT関数を使って、平均値と個数を掛け合わせます
結果として、「平均 × 個数」が計算され、合計値が求められます。
メリット＆デメリット
メリット:
- 数学的な考え方で合計を導き出せます。
- AVERAGE, COUNT, PRODUCT という、比較的よく使われる基本関数の組み合わせで実現できます。
デメリット:
COUNT関数は数値データのみを数えます。そのため、範囲内に文字列などが含まれていると意図した結果にならない可能性があります。（その場合はCOUNTAを使うなどの工夫が必要ですが、今回は数値のみなのでOK）
方法4：さらにPRODUCT, AVERAGE系, COUNT系も禁止！ 文字列操作で力技！？
考えかた
さあ、いよいよ変態的な領域（？）に足を踏み入れます！
合計、集計、平均、個数、そしてそれらの掛け算（PRODUCT）まで禁止されました。
数値として扱うのが難しいなら…そうだ！
文字の長さで表現すればいいではないか！
ここでは、各数値を「特定の文字の繰り返し回数」と見立てます。
次に、それらを全部つなげた文字列を作ります。
そして、最終的な文字数（＝長さ）を数えることで合計を求める、
というアクロバティックな方法です！
【使用禁止関数】
- SUM系
- SUBTOTAL, AGGREGATE
- PRODUCT
- AVERAGE系 (AVERAGE, AVERAGEA, AVERAGEIF, etc.)
- COUNT系 (COUNT, COUNTA, COUNTBLANK, COUNTIF, etc.)
手順
数式入力
まず、結果を表示したいセル（例: C5セル）に、以下の数式を入力します。
=LEN(CONCAT(REPT(“a”, A2:A26)))
この数式はMicrosoft 365やExcel 2021以降などのスピル機能に対応したバージョンが必要です
解説
これもまた、一見すると何をしているか分かりにくい数式ですね。
しかし、順を追って分解してみましょう。
まず、REPT(“a”, A2:A26) の部分
REPT関数は、指定した文字列を指定回数繰り返す関数です。
ここでは、A2からA26の各セルに入っている数値の回数だけ、”a” という文字を繰り返します。
例えば、セルに 13 と入っていれば “aaaaaaaaaaaaa” (aが13個) という文字列が生成されます。
Excelのスピル機能により、A2:A26の各セルに対してこの処理が行われます。
画像のように [“aが13個の文字列”, “aが37個の文字列”, …] という配列が作られます。
次に、CONCAT(…)
CONCAT関数は、複数の文字列を一つに連結する関数です。
先ほど生成された “a” の文字の塊（の配列）を、すべて繋ぎ合わせます。
結果として、元の数値の合計値とちょうど同じ長さの、ながーい “a” の文字列が出来上がります。
最後に、LEN(…)
LEN関数は、指定した文字列の長さ（文字数）を返す、非常にシンプルな関数です。
ステップ2で生成された、合計値と同じ長さを持つ “a” の文字列の長さを数えます。
これで、目的の合計値が直接求められる、というわけです。
メリット＆デメリット
メリット:
- 関数の全く予想外な使い方と、その動作原理を知ることができます。
- REPT, CONCAT, LEN という、文字列操作系の関数の組み合わせが見られます。
デメリット:
- なぜこれで合計になるのか、直感的に理解するのは難しいです。
- 実用性はゼロ に等しいです。
- 合計する数値が大きい場合やデータ数が多い場合、Excelの動作が極端に遅くなったり、エラーになったりする可能性があります。（Excelの扱える文字列長には上限があるため）
- スピル機能が前提となります。
別解：SEARCH関数を使った方法
ちなみに、文字列の長さを利用する別の方法もあります。
基本的な考え方は同じですが、
最後にLEN関数の代わりにSEARCH関数を使います。
数式は以下のようになります。
=SEARCH(“b“,CONCAT(REPT(“a”,A2:A26))&”b“)-1
これは、まず連結した”a“の文字列の末尾に、目印として”b“を追加します。
次に、SEARCH関数でその”b“の位置（これは合計値+1になります）を見つけます。
最後に1を引いて合計値を求める、という少し回りくどい手順です。
先に紹介したLEN関数を使う方が若干シンプルですね。
方法5：さらにLEN,SEARCH系, CONCAT系, REPTも禁止！ 行列計算でフィニッシュ！
考えかた
ついに最終問題です！
禁止関数のオンパレードです！
文字列操作系の関数も封じられました。
もう打つ手なしか…？
いや、まだExcelには奥の手があります。
それは 行列計算 です！
特定の行列同士の掛け算（行列積）を使うと、うまい具合に合計が計算できるのです！
【使用禁止関数】
- SUM系
- SUBTOTAL, AGGREGATE
- PRODUCT
- AVERAGE系
- COUNT系
- SEARCH系 (SEARCH, FIND, FINDB)
- CONCAT系 (CONCAT, CONCATENATE, TEXTJOIN)
- REPT
手順
数式入力
まず、結果を表示したいセル（例: C6セル）に、以下の数式を入力します。
RANDARRAY関数はExcel 2021以降、MMULT関数は古くからありますが、
組み合わせとしての動作はスピル対応のExcelが望ましい場合があります。
=MMULT(TRANSPOSE(A2:A26), RANDARRAY(ROWS(A2:A26), 1, 1, 1, TRUE))
解説
これもまた難解に見えます。
しかし、一つずつ見ていきましょう。
まず、ROWS(A2:A26): ROWS関数でデータ範囲 A2:A26 の行数を取得
今回は 25 です。
次に、RANDARRAY(ROWS(A2:A26), 1, 1, 1, TRUE)
RANDARRAY関数は、
指定したサイズの乱数の配列を生成する関数です
=RANDARRAY([行数], [列数], [最小値], [最大値], [整数にするか])
しかし、ここでは引数を工夫して「すべてが 1」である配列を作ります。
- ROWS(A2:A26) (つまり 25): 作成する配列の行数
- 1: 作成する配列の列数
- 1: 生成する数値の最小値
- 1: 生成する数値の最大値
- TRUE: 整数を生成する
結果として、25行1列で、すべての要素が 1 である縦長の配列 [[1], [1], …, [1]] がメモリ上に生成されます。
(1が25個縦に並ぶ配列です)
続いて、TRANSPOSE(A2:A26)
TRANSPOSE関数は、
行と列を入れ替える（転置する）関数です。
元のデータ A2:A26 は縦1列 (25行1列) です。
これを横1行 (1行25列) の配列 [13, 37, 97, …, 86] に変換します。
最後に、MMULT(…, …)
MMULT関数は、2つの行列の積を計算する関数です。
ここでは、ステップ3で作成した 1行25列 の行列を使います。
そして、ステップ2で作成した 25行1列 の行列の積を計算します。
行列の積の計算ルールにより、
(1行m列) × (m行1列) の計算結果は (1行1列) となります。
つまり単一の数値になります。
具体的には、以下の計算が行われます。
= (13×1 + 37×1 + 97×1 + … + 86×1)
これにより、見事に合計値が求められるのです！
メリット＆デメリット
メリット:
- 行列計算という、普段あまり使わないかもしれないExcelの強力な機能の一端に触れられます。
- 数式自体は（慣れれば）比較的スッキリして見える…かも？
- MMULT関数は、条件に合うデータの合計（SUMPRODUCT的な使い方）など、応用範囲が広いです。（ただし、難しい）
デメリット:
- 行列計算の知識がないと、全く意味が分からない。
- MMULT関数自体、使える場面や組み合わせる配列の次元（行数・列数）に制約があります。
- そのため、扱いが難しいです。
- RANDARRAY関数が比較的新しい（Excel 2021以降）ため、古いバージョンでは使えません。
まとめ
今回は、「SUM関数や演算子を使わずに合計を求める」という、
少し遊び心のあるテーマに挑戦しました。
SUMやAGGREGATE、AVERAGE&COUNTに加え、
文字列操作や行列計算など、さまざまな方法を紹介しています。
方法4や5のようなトリッキーな数式は全く実務向きではありません。
ですが、関数パズルに取り組むことで思わぬ発見があり、
関数理解や数式を組み立てる力を伸ばすことができます。
実務では、基本に忠実にSUMやAGGREGATEを使うのが安心です。
ときにはExcelの奥深さに触れて、楽しんでみるのもスキルアップにつながるはずです。
今回の挑戦が、みなさんのExcelライフを少しでも豊かにするきっかけになればうれしいです！
