BYROW・BYCOL関数で配列を自在に集計！LAMBDA関数と組み合わせた実践活用術

この記事でわかること

  • BYROW・BYCOL関数の基本的な使い方
  • LAMBDA関数との組み合わせ方（必須）
  • 実務でよく使う具体的な使用例
  • よくあるエラーと対処法

BYROW・BCOLとはどんな関数？

ExcelにはSUMやAVERAGEなど、配列（複数のセル範囲）をまとめて集計する関数が多数あります。しかしこれらは「全体をひとつの数値にまとめる」ものがほとんどで、「行ごとに・列ごとに、任意の計算をしたい」となると、SUMIF・ARRAYFORMULA・スピル関数を組み合わせるなど、複雑になりがちでした。

そこで登場したのが BYROW関数 と BYCOL関数 です。

関数処理の方向イメージ
BYROW行ごとに処理各行を横に走って集計
BYCOL列ごとに処理各列を縦に走って集計

どちらも「行・列の単位で、自分が指定した計算を繰り返す」関数です。LAMBDA関数（前回記事参照）と必ずセットで使います。

基本構文

=BYROW(配列, LAMBDA(行変数, 計算式))
=BYCOL(配列, LAMBDA(列変数, 計算式))
  • 配列：処理したいセル範囲
  • LAMBDA(行変数, 計算式)：各行（または各列）に対して適用する処理
    • 行変数（列変数）は任意の名前でOK（慣習的に row や col を使うことが多い）

まずは動かしてみよう：BYROWの基本例

▼ 例①：各営業担当の月次合計を一発で出す

以下のような売上データがあるとします。

A（4月）B（5月）C（6月）
田中12095130
鈴木8511075
佐藤200180210

やりたいこと：各担当者の3ヶ月合計を、別のセルにまとめて出したい。

=BYROW(B2:D4, LAMBDA(row, SUM(row)))

入力するのはこの1つの数式だけ。 スピルによりE2・E3・E4に自動で結果が広がります。

345   ← 田中の合計
270   ← 鈴木の合計
590   ← 佐藤の合計

SUM以外にも AVERAGE(row) や MAX(row) に変えるだけで、同じ構文で平均・最大値も取れます。

▼ 例②：BCOLで月ごとの平均を出す

同じデータで、今度は月ごとの平均を求めます。

=BYCOL(B2:D4, LAMBDA(col, AVERAGE(col)))

結果は横方向にスピルし、B5・C5・D5に各月の平均が表示されます。

135   ← 4月の平均
128.3 ← 5月の平均
138.3 ← 6月の平均

実務での活用例

活用例①〜④では、以下のサンプルデータを使います。営業担当者5名の週次売上（単位：万円） で、「未報告（空白）」や「売上なし（0）」が混在しているイメージです。

サンプルデータ（B1:G6）

B（月）C（火）D（水）E（木）F（金）
2行目　田中12095013085
3行目　鈴木85　1107590
4行目　佐藤2001802100175
5行目　高橋6070　8055
6行目　伊藤1501301401200
  • 鈴木の火曜・高橋の水曜は空白（データ未入力）
  • 田中の水曜・佐藤の木曜・伊藤の金曜は0（売上なし）

結果を出力するセルは以下の通りです。

活用例数式を入力するセル結果の出力先
①空白除き平均H2H2:H6（縦にスピル）
②レンジ（最大－最小）I2I2:I6（縦にスピル）
③100以上の合計J2J2:J6（縦にスピル）
④0以外の件数B7B7:F7（横にスピル）

▼ 活用例①：行ごとに「空白を除いた平均」を出す

空白セルがあるデータでは、通常の AVERAGE だと空白をスキップして計算しますが、0 は数値として計算に含まれてしまいます。「0も除きたい」「確実に空白だけ除きたい」場合は AVERAGEIF を使うのが確実です。

=BYROW(B2:F6, LAMBDA(row, AVERAGEIF(row, "<>", row)))

H2に入力するとH2:H6に結果がスピルします。

86   ← 田中（0を含む5件の平均）
90   ← 鈴木（空白を除く4件の平均）
153  ← 佐藤（0を含む5件の平均）
66.25← 高橋（空白を除く4件の平均）
108  ← 伊藤（0を含む5件の平均）

"<>" は「空白以外」を意味する条件です。空白セルだけを除外し、0は平均の計算に含まれます。

▼ 活用例②：行ごとに「最大値と最小値の差（レンジ）」を出す

各担当者の日々の売上のばらつきを確認したい場合。

=BYROW(B2:F6, LAMBDA(row, MAX(row) - MIN(row)))

I2に入力するとI2:I6に結果がスピルします。

130  ← 田中（最大130－最小0）
35   ← 鈴木（最大110－最小75）
210  ← 佐藤（最大210－最小0）
25   ← 高橋（最大80－最小55）
150  ← 伊藤（最大150－最小0）

従来なら「G列にMAX、H列にMIN、I列で引き算」という3列が必要でしたが、1つの数式で完結します。

注意：空白セルはMIN計算で0として扱われる場合があります。空白を除いてレンジを取りたい場合は MINIFS(row, row, "<>") を使うと安全です。

=BYROW(B2:F6, LAMBDA(row, MAX(row) - MINIFS(row, row, "<>")))

▼ 活用例③：行ごとに「条件に合う値だけ合計」する

各担当者の、100万円以上の売上だけを合計したい場合。

=BYROW(B2:F6, LAMBDA(row, SUMIF(row, ">=100")))

J2に入力するとJ2:J6に結果がスピルします。

335  ← 田中（120＋130＋85）
200  ← 鈴木（200）※110・75・90は100未満
590  ← 佐藤（200＋180＋210）
0    ← 高橋（全て100未満）
540  ← 伊藤（150＋130＋140＋120）

条件を変えれば「50万円未満の合計」「ちょうど0の件数」なども同じ構文で書けます。

▼ 活用例④：BCOLで列ごとに「0以外のデータ件数」を数える

曜日ごとに、実際に売上があった（0でない）担当者が何人いたかを確認します。

=BYCOL(B2:F6, LAMBDA(col, COUNTIF(col, ">0")))

B7に入力するとB7:F7に結果がスピルします。

5    4    3    3    4
↑月  ↑火  ↑水  ↑木  ↑金
  • 月曜：5人全員売上あり
  • 火曜：4人（鈴木が空白）
  • 水曜：3人（田中が0、高橋が空白）
  • 木曜：3人（佐藤が0、鈴木は75なのでカウント）
  • 金曜：4人（伊藤が0）

">0" の条件なので、0と空白はどちらもカウントされません。

BYROW × LAMBDA でできること、できないこと

操作可否補足
SUM・AVERAGE・MAX・MINなどの集計✅最もシンプルな使い方
IF・IFS を使った条件分岐✅結果を1値に絞れば使える
複数の値を返す（配列を返す）❌各行・各列の処理結果は「1つの値」でなければならない
TEXT・CONCATなどの文字列操作✅文字列を1つに結合するなら可

最大のポイント：BYROWとBCOLは、LAMBDAの処理結果が「1行×1列（スカラー値）」でないとエラーになります。
たとえば LAMBDA(row, FILTER(row, row>0)) のように複数の値を返そうとするとエラーが出ます。

よくあるエラーと対処法

● #CALC! エラーが出る

処理結果が複数の値になっている場合に発生します。
→ LAMBDAの中の計算式が「必ず1つの値を返す」形になっているか確認してください。

● #VALUE! エラーが出る

配列に文字列や空白が混在していて、計算できない場合。
→ IFERROR(SUM(row), 0) のようにIFERRORで囲むと回避できます。

=BYROW(B2:D4, LAMBDA(row, IFERROR(SUM(row), 0)))

● 結果が1つしか出ない

スピルを妨げる何かがある場合（既存データ、結合セルなど）。
→ 結果が広がる先のセルが空白か確認してください。

LAMBDA関数との関係を整理しよう

BYROW・BCOLはLAMBDA関数を「引数」として受け取る特殊な関数です。前回の記事で紹介したLAMBDAの「自作関数」とは少し異なる使い方をします。

前回のLAMBDABYROW・BCOLの中のLAMBDA
目的再利用できる関数を定義その場限りの処理を定義
定義方法名前の管理で登録数式内に直接記述
呼び出し関数名で呼び出すBYROW・BCOLが自動で繰り返す

BYROW・BCOLの中のLAMBDAは、「この処理を全行（全列）に対して繰り返してください」という指示書の役割を果たしています。

まとめ

  • BYROW：行ごとに任意の処理を実行、結果を縦方向にスピルして返す
  • BYCOL：列ごとに任意の処理を実行、結果を横方向にスピルして返す
  • 処理内容はLAMBDAで指定し、結果は必ず1つの値を返す必要がある
  • SUM・AVERAGE・MAX・MINなどとの組み合わせが基本で、IFERROR・SUMIFとの組み合わせも強力

従来「補助列を作って計算して最後に消す」という作業が必要だった集計が、1つの数式でスッキリ書けるようになります。LAMBDA関数を学んだ後の「次のステップ」として、ぜひ手を動かしてみてください。

対応バージョンについて

BYROW・BYCOL関数は Microsoft 365（サブスクリプション版） および Excel 2021以降 で使用できます。Excel 2019以前やExcel Onlineの一部バージョンでは使用できないため、職場の環境を確認してから使いましょう。

関連記事

タイトルとURLをコピーしました