ピボット テーブルは誰にとっても良いものです。計算が速く、柔軟に構成でき、必要に応じてデザインをエレガントに組み込むことができます。 しかし、軟膏にはいくつかのハエもあります。特に、値領域に数字ではなくテキストを含める必要がある要約を作成できないことです。
この制限を回避して、同様の状況で「松葉杖のカップル」を考え出してみましょう。
私たちの会社が製品をコンテナで私たちの国とカザフスタンのいくつかの都市に輸送するとします。 コンテナは月に XNUMX 回しか送られません。 各コンテナには英数字の番号が付いています。 初期データとして、配送をリストした標準の表があります。各都市と各月に配送されたコンテナーの数を明確に確認するには、そこから何らかの要約を作成する必要があります。
便宜上、コマンドを使用して事前に初期データ「スマート」でテーブルを作成しましょう ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット) 彼女に名前を付けて 配達 タブ コンストラクタ （設計）. 将来的には、これは生活を簡素化するでしょう。 テーブルの名前とその列を数式で直接使用できます。
方法 1. 最も簡単な – Power Query を使用する
Power Query は、Excel でデータを読み込んで変換するための非常に強力なツールです。 このアドインは、2016 年以降、既定で Excel に組み込まれています。Excel 2010 または 2013 をお持ちの場合は、個別にダウンロードしてインストールできます (完全無料)。
明確にするために、プロセス全体を次のビデオで段階的に分析しました。
Power Query を使用できない場合は、別の方法 (ピボット テーブルまたは数式) を使用できます。
方法 2. 補助要約
元のテーブルにもう XNUMX 列追加してみましょう。単純な数式を使用して、テーブルの各行の数を計算します。
テーブルには 1 行のヘッダーがあるため、明らかに -XNUMX が必要です。 テーブルがシートの先頭にない場合は、現在の行とテーブル ヘッダーの数の差を計算する、もう少し複雑ではあるが普遍的な式を使用できます。
ここで、標準的な方法で、データに基づいて目的のタイプのピボット テーブルを作成しますが、値フィールドではフィールドを削除します 行番号 私たちが望むものの代わりに コンテナ:
同じ月に同じ都市に複数のコンテナがないため、実際には、要約では金額ではなく、必要なコンテナのライン番号が示されます。
さらに、タブで総計と小計をオフにすることができます コンストラクター – 一般的な合計 и 小計 (設計 — 総計、小計) 同じ場所で、ボタンを使用して要約をより便利な表レイアウトに切り替えます レポートのモックアップ (レポートのレイアウト).
したがって、すでに結果の半分に達しています。都市と月の交点に、必要なコンテナー コードが存在するソース テーブルの行番号があるテーブルがあります。
次に、サマリーを (同じシートまたは別のシートに) コピーして、値として貼り付けてから、数式を値領域に入力します。これにより、サマリーにある行番号でコンテナー コードが抽出されます。
演算 IF （IF）、この場合、集計の次のセルが空でないことを確認します。 空の場合、空のテキスト文字列「」を出力します。つまり、セルを空白のままにします。 空でない場合は、列から抽出します コンテナ ソース テーブル 配達 関数を使用した行番号によるセルの内容 INDEX （索引）.
おそらく、ここで唯一明らかではないポイントは、ダブルワードです。 コンテナ 式で。 そのような奇妙な書き方：
用品[[容器]:[容器]]
…列を参照するためにのみ必要です コンテナ (通常の「非スマート」テーブルの $ 記号を使用した参照のように) 絶対的であり、式を右側にコピーするときに隣接する列にスリップしませんでした。
今後、ソーステーブルのデータを変更する場合 配達、それを右クリックしてコマンドを選択して、補助要約を行番号で更新することを忘れないでください 更新して保存 （更新）.
方法 3. 数式
この方法では、中間のピボット テーブルを作成して手動で更新する必要はありませんが、Excel の「強力な武器」である関数を使用します。 SUMMSLIMN （スミフス）. 集計で行番号を調べる代わりに、次の式を使用して計算できます。
実際、これは選択的合計関数の標準的な使用例です。 SUMMSLIMN指定された都市と月の行番号を合計する です。 繰り返しますが、同じ月に同じ都市に複数のコンテナーがないため、関数は実際には金額ではなく、行番号自体を提供します。 そして、前の方法ですでにおなじみの機能 INDEX コンテナ コードを抽出することもできます。
もちろん、この場合、集計の更新について考える必要はなくなりますが、大きなテーブルでは関数 サマーリ 著しく遅くなる可能性があります。 次に、数式の自動更新をオフにするか、最初の方法であるピボット テーブルを使用する必要があります。
要約の外観がレポートにあまり適していない場合は、ここで行ったように直接ではなく、次の関数を使用して、要約から最終テーブルに行番号を抽出できます。 ピボットテーブルデータの取得 (GET.PIVOT.DATA). これを行う方法については、こちらを参照してください。
- ピボット テーブルを使用してレポートを作成する方法
- ピボット テーブルで計算を設定する方法
- SUMIFS、COUNTIFSなどによる選択的カウント。