内容
問題の定式化
入力データとして、シートの XNUMX つに次の形式の売上データを含む複数のテーブルが含まれている Excel ファイルがあります。
ご了承ください：
- サイズが異なり、行と列にさまざまな製品と地域のセットが並べ替えられていないテーブル。
- 表の間に空白行を挿入できます。
- テーブルの数は任意です。
XNUMX つの重要な仮定。 次のことが想定されます。
- 各表の上、最初の列には、表が示している売上高のマネージャーの名前があります (イワノフ、ペトロフ、シドロフなど)。
- すべての表の商品と地域の名前は、大文字と小文字の正確さで同じ方法で書かれています。
最終的な目標は、すべてのテーブルからデータを XNUMX つのフラットな正規化されたテーブルに収集し、その後の分析と要約の作成に便利にすることです。つまり、次のようになります。
ステップ 1. ファイルに接続する
新しい空の Excel ファイルを作成し、タブで選択してみましょう 且つ Command データの取得 – ファイルから – ブックから (データ — ファイルから — ワークブックから). 販売データを含むソース ファイルの場所を指定し、ナビゲーター ウィンドウで必要なシートを選択してボタンをクリックします。 データの変換 (変換データ):
その結果、そこからのすべてのデータを Power Query エディターに読み込む必要があります。
ステップ 2. ゴミを片付ける
自動生成されたステップを削除する 改造型 (タイプ変更) и 昇格したヘッダー (昇格されたヘッダー) フィルターを使用して、空の行と合計のある行を取り除きます ヌル и 合計 最初の列によって。 その結果、次の図が得られます。
ステップ 3. マネージャーの追加
後で売上がどこにあるかを理解するために、テーブルに列を追加する必要があります。各行には、対応する姓があります。 このため：
1. コマンドを使用して行番号付きの補助列を追加しましょう 列の追加 – インデックス列 – 0 から (列を追加 — インデックス列 — 0 から).
2. コマンドで式を含む列を追加します 列の追加 – カスタム列 (列を追加 — カスタム列) そこに次の構造を導入します。
この式のロジックは単純です。最初の列の次のセルの値が「製品」の場合、これは新しいテーブルの先頭に出くわしたことを意味するため、前のセルの値をマネージャーの名前。 それ以外の場合、何も表示しません。つまり、null です。
姓を持つ親セルを取得するには、まず前の手順のテーブルを参照します #「インデックス追加」、次に必要な列の名前を指定します 【コラム1】 角括弧で囲み、その列のセル番号を中括弧で囲みます。 セル番号は、列から取得した現在のセル番号よりも XNUMX つ少なくなります。 インデックスそれぞれ。
3. 空のセルを埋めるために残っています ヌル コマンドを使用した上位セルの名前 変換 - フィル - ダウン (変換 - 塗りつぶし - 下) 不要になったインデックスのある列と、最初の列に姓がある行を削除します。 その結果、次のようになります。
ステップ 4. マネージャーごとに別々のテーブルにグループ化する
次の手順では、各マネージャーの行を個別のテーブルにグループ化します。 これを行うには、[変換] タブで [グループ化] コマンド ([変換] - [グループ化]) を使用し、開いたウィンドウで [マネージャー] 列と操作 [すべての行] (すべての行) を選択して、集計関数を適用せずにデータを収集します。それら（合計、平均など）。 P.):
その結果、マネージャーごとに個別のテーブルが得られます。
ステップ 5: ネストしたテーブルを変換する
結果の列の各セルにあるテーブルを指定します すべてのデータ まともな形で。
まず、各テーブルで不要になった列を削除します マネージャー. また利用します カスタム列 タブ 変換 (変換 — カスタム列) そして次の式：
次に、別の計算列を使用して、各テーブルの最初の行を見出しに上げます。
そして最後に、主な変換を実行します - M 関数を使用して各テーブルを展開します Table.UnpivotOtherColumns:
ヘッダーの領域の名前が新しい列に入り、正規化された表がより狭くなりますが、同時により長くなります。 セルを空にする ヌル 無視されます。
不要な中間列を取り除くと、次のようになります。
手順6 ネストした表の展開
列ヘッダーの二重矢印の付いたボタンを使用して、正規化されたすべてのネストされたテーブルを単一のリストに展開する必要があります。
…そして、ついに私たちが望んでいたものを手に入れました：
コマンドを使用して、結果のテーブルを Excel にエクスポートできます。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…).
- 複数の本から異なるヘッダーを持つ表を作成する
- 特定のフォルダー内のすべてのファイルからデータを収集する
- 本のすべてのシートから XNUMX つのテーブルにデータを収集する