内容
問題の定式化
ピボット テーブルは、Excel で最も優れたツールの XNUMX つです。 しかし、残念なことに、これまでのところ、Excel のどのバージョンも、たとえば異なるシートや異なるテーブルにあるいくつかの初期データ範囲の要約を作成するような単純で必要なことをその場で実行することはできません。
始める前に、いくつかの点を明確にしましょう。 アプリオリに、私たちのデータでは次の条件が満たされていると思います。
- テーブルには、任意のデータを含む任意の数の行を含めることができますが、ヘッダーは同じである必要があります。
- ソース テーブルを含むシートに余分なデータがあってはなりません。 XNUMX つのシート – XNUMX つのテーブル。 制御するには、キーボード ショートカットを使用することをお勧めします Ctrlキー+終わり、ワークシートで最後に使用されたセルに移動します。 理想的には、これはデータ テーブルの最後のセルにする必要があります。 クリックすると Ctrlキー+終わり テーブルの右側または下にある空のセルが強調表示されます。これらの空の列をテーブルの右側またはテーブルの下の行に削除し、ファイルを保存します。
方法 1: Power Query を使用してピボットのテーブルを作成する
Excel の 2010 バージョンから、任意のデータを収集して変換し、ピボット テーブルを作成するためのソースとして提供できる無料の Power Query アドインがあります。 このアドインを使用して問題を解決することは、まったく難しくありません。
まず、Excel で新しい空のファイルを作成しましょう。このファイルでアセンブリが行われ、ピボット テーブルが作成されます。
次にタブで 且つ (Excel 2016 以降をお持ちの場合) またはタブで パワークエリ (Excel 2010-2013 を使用している場合) コマンドを選択します クエリの作成 – ファイルから – Excel (データの取得 — ファイルから — Excel) 収集するテーブルを含むソース ファイルを指定します。
表示されるウィンドウで、任意のシートを選択し (どのシートでもかまいません)、下のボタンを押します。 変更する （編集）:
Excel の上に Power Query クエリ エディター ウィンドウが開きます。 パネルのウィンドウの右側 リクエストパラメータ 最初のステップを除いて、自動的に作成されたすべてのステップを削除します – ソース （ソース）:
これで、すべてのシートの一般的なリストが表示されます。 ファイルにデータ シートに加えて他のサイド シートがある場合、このステップでは、情報をロードする必要があるシートのみを選択し、テーブル ヘッダーのフィルターを使用して他のすべてのシートを除外します。
列を除くすべての列を削除します 且つ列見出しを右クリックし、 他の列を削除する （削除する その他の列):
次に、列の上部にある二重矢印 (チェックボックス 元の列名をプレフィックスとして使用する オフにすることができます):
すべてを正しく行った場合、この時点で、収集されたすべてのテーブルの内容が上下に表示されます。
ボタンを使用して、最初の行をテーブル ヘッダーに上げる必要があります。 最初の行をヘッダーとして使用する (最初の行をヘッダーとして使用) タブ ホーム （ホーム） フィルターを使用してデータから重複するテーブル ヘッダーを削除します。
コマンドで行ったすべてを保存します 閉じてロード – 閉じてロード… (クローズ & ロード — クローズ & ロード…) タブ ホーム （ホーム）をクリックし、開いたウィンドウでオプションを選択します 接続のみ (接続のみ):
すべての。 要約を作成するだけです。 これを行うには、タブに移動します 挿入 – ピボットテーブル (挿入 — ピボット テーブル)、オプションを選択します 外部データ ソースを使用する (外部データソースを使用)そして、ボタンをクリックして 接続を選択します、私たちの要求。 ピボットのさらなる作成と構成は、必要なフィールドを行、列、および値の領域にドラッグすることにより、完全に標準的な方法で行われます。
将来ソース データが変更されるか、ストア シートがいくつか追加された場合は、コマンドを使用してクエリとサマリーを更新するだけで十分です。 すべて更新 タブ 且つ (データ — すべて更新).
方法 2. マクロで UNION SQL コマンドを使用してテーブルを結合します
この問題に対する別の解決策は、次のマクロで表されます。このマクロは、コマンドを使用してピボット テーブルのデータ セット (キャッシュ) を作成します。 UNITY SQL クエリ言語。 このコマンドは、配列で指定されたすべてのテーブルを結合します シート名 本のシートを XNUMX つのデータ テーブルにまとめます。 つまり、異なるシートから XNUMX つのシートに範囲を物理的にコピーして貼り付けるのではなく、コンピューターの RAM で同じことを行います。 次に、マクロは指定された名前で新しいシートを追加します (変数 結果シート名) 収集されたキャッシュに基づいて本格的な (!) 要約を作成します。
マクロを使用するには、タブの Visual Basic ボタンを使用します。 開発者 （デベロッパー） またはキーボードショートカット 他の+F11. 次に、メニューから新しい空のモジュールを挿入します インサート – モジュール そこに次のコードをコピーします。
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant '結果のピボットが表示されるシート名 ResultSheetName = "Pivot" 'シートの配列ソース テーブルの名前 SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'SheetsNames のシートからテーブルのキャッシュを形成します。 ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.1; Data Source=", _ .FullName, ";Extended Properties=""Excel 1;" ""), vbNullString ) End With ' 結果のピボット テーブルを表示するシートを再作成します On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t。 Name = ResultSheetName '生成されたキャッシュの概要をこのシートに表示 Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A4.0") Set objPivotCache = Nothing Range("A8.0").Select End With End Sub 完成したマクロは、キーボード ショートカットで実行できます。 他の+F8 またはタブの [マクロ] ボタン 開発者 (開発者 — マクロ).
このアプローチの短所：
- キャッシュにはソース テーブルへの接続がないため、データは更新されません。 ソース データを変更した場合は、マクロを再度実行し、集計を再度作成する必要があります。
- 枚数を変更する場合は、マクロコード（配列）の編集が必要です。 シート名).
しかし、最終的には、さまざまなシートの複数の範囲に基づいて構築された本格的なピボット テーブルが得られます。
ほら！
テクニカルノート： マクロの実行時に「プロバイダーが登録されていません」などのエラーが発生した場合は、64 ビット バージョンの Excel を使用しているか、不完全なバージョンの Office がインストールされている (Access がない) 可能性があります。 この状況を修正するには、マクロ コード内のフラグメントを次のように置き換えます。
Provider = Microsoft.Jet.OLEDB.4.0;
以下に送って下さい：
プロバイダー = Microsoft.ACE.OLEDB.12.0;
また、Microsoft Web サイトの Access から無料のデータ処理エンジンをダウンロードしてインストールします – Microsoft Access Database Engine 2010 Redistributable
方法 3: 古いバージョンの Excel からピボットテーブル ウィザードを統合する
この方法は少し時代遅れですが、言及する価値があります。 正式には、2003 年までのすべてのバージョンで、ピボットテーブル ウィザードに「複数の連結範囲のピボットを作成する」オプションがありました。 ただし、残念ながら、このように作成されたレポートは、実際の本格的な要約の哀れな形にすぎず、従来のピボット テーブルの「チップ」の多くをサポートしていません。
このようなピボットでは、フィールド リストに列見出しがなく、柔軟な構造設定がなく、使用される関数のセットが制限されており、一般に、これらすべてがピボット テーブルとあまり似ていません。 おそらくそれが、2007 年以降、Microsoft がピボット テーブル レポートを作成する際の標準ダイアログからこの機能を削除した理由です。 現在、この機能はカスタム ボタンからのみ利用できます ピボットテーブル ウィザード(ピボット テーブル ウィザード)、必要に応じて、クイックアクセスツールバーに追加できます ファイル – オプション – カスタマイズ クイック アクセス ツールバー – すべてのコマンド (ファイル — オプション — クイック アクセス ツールバーのカスタマイズ — すべてのコマンド):
追加されたボタンをクリックした後、ウィザードの最初のステップで適切なオプションを選択する必要があります。
次に、次のウィンドウで、各範囲を順番に選択し、一般リストに追加します。
繰り返しますが、これは完全な要約ではありませんので、あまり期待しないでください。 このオプションは、非常に単純な場合にのみお勧めできます。
- ピボットテーブルを使用したレポートの作成
- ピボットテーブルで計算を設定する
- マクロとは何か、使い方、VBA コードのコピー先など。
- 複数シートからXNUMXシートへのデータ収集（PLEXアドオン）