エクセル入門PIVOTBY関数（縦軸と横軸でグループ化して集計）

PIVOTBY関数は、行（縦）と列（横）でグループ化し指定された関数によって値を集計します。行（縦）と列（横）の2軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベル、複数の列グループレベルに対応しています。

行並べ替え順序に を指定しています。並べ替えを行フィールドのみで行う場合は、複数の列を指定することが出来ます。 この場合は、並べ替え順序の数値をベクトル（配列）で指定します。 並べ替え順序の数値は、行フィールドと値で通し番号です。 B列 → 1 C列 → 2 D列 → 3 降順/逆順の場合は数値をマイナスで指定します。 ※シートの列位置とは関係なく、あくまで「行フィールド」の中での順番です。※行フィールドと値の両方を指定して並べ替えることはできません。

列並べ替え順序に -1 を指定しています。 並べ替え順序の数値は、行フィールドと値で通し番号です。 A列 → 1 D列 → 2 降順/逆順の場合は数値をマイナスで指定します。 ※列（横）で並べ替えることはあまりないと思います。

・FILTER関数の書式 ・FILTER関数使用例のサンプルデータ ・FILTER関数の基本 ・空白セルを0ではなく空白にする場合 ・複数条件のフィルター ・関数を使ってフィルター ・横（列）でフィルター ・表示する列を選択する ・FILTER関数の結果を他の関数で使う ・スピルによって新しく追加された関数

行フィールド・値と同じ行数のブール値（TRUE/FALSE）の配列を指定します。 B1:B21="分類A" これは、21個のTRUE/FALSEの縦配列です。 この配列が FALSE の行は対象外として除外されます。

B1:B21&C1:C21 分類と品名を文字列結合しています。 元のデータを編集してPIVOTBY関数に入れる事が出来ます。 この時に使用できる関数に特に制限ありません。 当然ですが、VLOOKUPやMATCH+INDEX等の関数も使えます。 ただし、上記の例ではCONCAT関数を使う事は出来ません。

CONCAT関数は、複数の範囲や文字列からのテキストを結合します。ただし、区切り記号は指定できません。CONCAT関数の書式 CONCAT(テキスト1,[テキスト2],…) テキスト1 必須です。結合するテキスト項目。

・LAMBDA関数の構文 ・LAMBDA関数をセルで使う場合の基本 ・LAMBDA関数の「数式の検証」について ・LAMBDA関数をセルで使う場合の使用例 ・パラメーターの省略について ・LAMBDA関数を名前定義に登録 ・再帰関数の作成 ・LET関数内でLAMBDA関数を使用する ・LAMBDA関数にLAMBDA関数を渡す ・LAMBDA関数のネストと変数のスコープ（適用範囲） ・遅延評価によりLAMBDA関数オブジェクト（関数値）を返すことができる関数 ・LAMBDAヘルパー関数について

SUM、AVERAGE、MEDIAN、COUNT、COUNTA、、、 これらは、LAMBDA関数の記述を縮小簡略化した書き方です。 従って、もともとのLAMBDA関数で書き直すことが出来ます。 =GROUPBY(. イータ縮小関数) ↓ =GROUPBY(. LAMBDA(x,イータ縮小関数(x)))

上記では、「合計」という関数を作成してそれを使っています。 独自に作製する関数名をイータ縮小ラムダ関数と同じにすると、その名前で上書きされます。 誤解の元になるので、このような使い方は避けた方が良いでしょう。

function 引数に指定する関数が2つの引数（例: PERCENTOF やカスタムの LAMBDA 関数）を必要とする場合に、2番目の引数に渡される集計値の基準を指定します。 これにより、割合や比率の計算において、どの合計値に対する割合を出すのかを制御できます。

• 0: 列合計 (既定値)
  • 特徴: 各集計値が、その値が存在する列全体の合計に対する割合として計算されます。
  • 挙動: 例えば、各商品の売上を、その商品が含まれる「月の売上合計」に対する割合として表示したい場合に利用します。列のグループ化が行われている場合、最も内側の列グループの合計が基準となります。
  • 使用例: 四半期ごとの売上データをピボットする際、「各月の製品別売上」を「その月の総売上」に対する割合で表示するケースなど。
  • 特徴: 各集計値が、その値が存在する行全体の合計に対する割合として計算されます。
  • 挙動: 例えば、各商品の売上を、その商品が含まれる「地域の売上合計」に対する割合として表示したい場合に利用します。行のグループ化が行われている場合、最も内側の行グループの合計が基準となります。
  • 使用例: 各地域の製品別売上データをピボットする際、「各地域での特定製品の売上」を「その地域全体の製品売上」に対する割合で表示するケースなど。
  • 特徴: 各集計値が、ピボットテーブル全体の**総計（グランドトータル）**に対する割合として計算されます。
  • 挙動: 全体の中で各データがどの程度の割合を占めるかを一目で把握したい場合に有効です。列や行の小計は考慮されず、常に全体の合計が基準となります。
  • 使用例: 全期間・全製品・全地域を通じた「各単一セル（例: 特定の月・特定の製品）の売上」が「総売上」の何パーセントに当たるかを知りたい場合など。
  • 特徴: 各集計値が、その値が存在する上位のグループ化された親列の合計に対する割合として計算されます。
  • 挙動: 複数レベルで列がグループ化されている場合に、一つ上の階層のグループ合計を基準として割合を計算します。
  • 使用例: 「年」の下に「月」がグループ化されている場合、「各月の売上」を「その年の売上合計」に対する割合として表示するケースなど。
  • この relative_to 引数は、function 引数に指定する関数が2つの引数（subset と totalset）を必要とする場合にのみ影響を与えます。 例えば、SUM や AVERAGE のような単一引数で完結する関数を指定した場合は、この引数は無視されます。
  • function 引数にカスタムのラムダ関数 (LAMBDA 関数) を指定して割合を計算する場合、以下のようなパターンに従って関数を定義する必要があります。 LAMBDA(subset, totalset, SUM(subset) / SUM(totalset)) ここで、subset は現在のセルが含むデータの範囲を、totalset は relative_to で指定された基準（列合計、行合計など）となるデータの範囲を指します。
  relative_to 引数の使用例 架空の「地域別の製品販売データ」を使って、relative_to がどのように集計結果に影響するかを見ていきます。 function に PERCENTOF を使って割合を計算する例を示します。 サンプルデータ 以下の販売データを使用します。これをExcelのシート（例: A1:D13）に貼り付けてください。 地域 製品 月または、 売上 関東 A 1月 100 関東 B 1月 150 関東 A 2月 120 関西 A 1月 80 関西 B 1月 110 関西 C 1月 90 東北 A 1月 50 東北 B 2月 70 関東 B 2月 180 関西 A 2月 90 東北 C 2月 60 関東 C 1月 70 基本となる数式（合計表示あり） 以下の数式は、行に「製品」、列に「月」を配置し、各行・列の合計を表示します。relative_to 以外の引数はこのベースで進めます。 =PIVOTBY(B1:B13, C1:C13, D1:D13, PERCENTOF, TRUE, 1, 1, 1, 1, , [relative_toの値])

  TRUE (フィールドヘッダー): ヘッダーを表示 1 (row_total_depth): 行の合計（製品ごとの合計）を表示 1 (row_sort_order): 製品名を昇順に並べ替え 1 (col_total_depth): 列の合計（月ごとの合計）を表示 1 (col_sort_order): 月を昇順に並べ替え

  行フィールド・列フィールドを複数にした場合の例は、行・列の指定を変更しています。 relative_to = 0 : 列合計に対する割合（画像と同じ状態） =PIVOTBY(B1:B13, C1:C13, D1:D13, PERCENTOF, TRUE, 1, 1, 1, 1, , 0) ※%表示は、セルの表示形式で設定しています。

  各セル（データポイント）の値が、そのセルが含まれるピボットされた「列」の合計に対する割合として計算されます。 各月の列の最後に表示される「合計」行が常に100%になります。 これは、その月の売上合計を基準として、各製品がどれだけの割合を占めるかを示しています。 各月の「合計」行の右側にある総計の列（I列）の数字は、その製品の全売上が、データ全体の総売上に対してどれくらいかを教えてくれます。

  relative_to = 1 : 行合計に対する割合 =PIVOTBY(B1:B13, C1:C13, D1:D13, PERCENTOF, TRUE, 1, 1, 1, 1, , 1) ※%表示は、セルの表示形式で設定しています。

  セル（データポイント）の値が、そのセルが含まれるピボットされた「行」の合計に対する割合として計算されます。 各製品の行の最後に表示される「合計」列が常に100%になります。 これは、各製品の全期間の売上を基準として、各月がどれだけの割合を占めるかを示しています。

  relative_to = 2 : 総計に対する割合 =PIVOTBY(B1:B13, C1:C13, D1:D13, PERCENTOF, TRUE, 1, 1, 1, 1, , 2) ※%表示は、セルの表示形式で設定しています。

  各セル（データポイント）の値が、ピボットテーブル全体の「総計」（グランドトータル）に対する割合として計算されます。 どのセルも全体の売上合計を基準とした割合になります。 最終的な「合計の合計」セルが100%になります。これは、個々のセルや小計が、データ全体のどれくらいの割合を占めるかを把握するのに役立ちます。

  relative_to = 3 : 親列の合計に対する割合（複数列フィールドが必要） このオプションは、col_fields に複数の列（例: 年, 月）が指定され、列に階層がある場合に有効です。 数式例: (サンプルデータを拡張し、列に「年」を追加した場合) =PIVOTBY(B1:B13, HSTACK(A1:A13,C1:C13), D1:D13, PERCENTOF, TRUE, 1, 1, 2, 1, , 3) ※%表示は、セルの表示形式で設定しています。 ※ピボットは、数値比較しやすいように追加表示したものです。

  各地域（親列）内において、各月の売上が、その「地域」における「各製品の全月の売上合計」に対する割合として計算されます。 この設定は、階層化された列を持つピボットテーブルにおいて、各親カテゴリ（例: 地域）の範囲内で、各行の要素（例: 製品）が月ごとにどのように分布しているかを詳しく分析したい場合に非常に有効です。 親カテゴリ内の各要素（製品）の合計を基準とすることで、より具体的な内訳を把握できます。

  relative_to = 4 : 親行の合計に対する割合（複数行フィールドが必要） このオプションは、row_fields に複数の列（例: 地域, 製品）が指定され、行に階層がある場合に有効です。 =PIVOTBY(A1:B13, C1:C13, D1:D13, PERCENTOF, TRUE, 2, 1, 1, 1, , 4) ※%表示は、セルの表示形式で設定しています。 ※ピボットは、数値比較しやすいように追加表示したものです。

  各値が、その値を含む上位の親行の合計に対する割合として計算されます。 各親行（例: 地域）の合計が 100.0% になります。 例えば、「関東地域」の「製品A」の「1月」のセルは、関東地域の1月の売上合計に対する製品Aの1月の売上の割合を示します。 結果の解釈: これは、親グループ（例: 地域）の内訳として、子グループ（例: 製品）がどれくらいの割合を占めるかを視覚的に把握するのに非常に有効です。

  同じテーマ「エクセル入門」の記事 新着記事 NEW ・・・新着記事一覧を見る アクセスランキング ・・・ ランキング一覧を見る このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

  記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。 掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。 本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。 This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.