実践的な Excel スキルの共有: Power Query を使用してフォルダー内のブックを結合する

以前、EXCEL の新機能 Power Query を使ってブック内のワークシートを集計する方法を紹介しましたが、Power Query の機能はそれだけではありません。より高度な結合テクニック: Power Query を使用して、フォルダー内のブックを結合します。

以下に示すように、デスクトップの「Sales」フォルダ内に 4 つの地域の売上データがあります。各ワークブックのタイトル名は一貫していますが、順序は異なる場合があります。各ワークブックの都市列の値はワークブックの名前であり、後でマージ効果を確認するのに便利です。

##操作は次のとおりです。

フォルダー内のファイルを閉じ、新しいワークブックを作成し、[データ]タブをクリックし、 [取得と変換]グループ「新しいクエリ」---「ファイルから」---「フォルダから」。

#フォルダー パスを入力するか、フォルダーの場所を参照して選択し、[OK] をクリックします。

このインターフェイスには、フォルダー内のすべてのワークブックがリストされ、[編集] をクリックします。

Power Query エディター インターフェイスに入りました。上がメニューバー、中がテーブルエリアと最後にワークシートに返されるデータ、右側のクエリ設定ウィンドウにはPower Queryの操作記録が表示されます。

前に紹介したように、「コンテンツ」列の緑色のフォントは、このセルにファイルが含まれていることを意味します。セルをクリックして内容をプレビューします。 (注: セルの内容をプレビューするときは、テキストではなくセルの空白スペースにマウスを置く必要があります。テキストをクリックすると、セル内のファイルが直接開きます

)ファイルはフォルダーから直接開かれます。抽出されたファイルはすべてバイナリ形式であるため、下のプレビュー ウィンドウにバイナリ形式のワークブックが表示されます。

Binary はバイナリ ファイルのままでは表に表示できないので、表形式に変更してから展開する必要があります。データを内部のテーブルに取り込みます。他の不要な列を先に削除してください。

「コンテンツ」列を選択し、「ホーム」タブの「列の管理」グループにある「列の削除」-「他の列の削除」をクリックします。

このようにして、「Content」列を除く他のすべての列が削除されます。

#したがって、バイナリ ファイルを通常のテーブルに変換するには、Power Query の特別なプログラミング言語である M 言語を使用する必要があります。よく使われる機能を紹介します。

[列の追加] オプションの下にある [全般] グループの [カスタム列] をクリックします。

[カスタム列] ウィンドウで、[カスタム列の式] に「=Excel.Workbook([Content],true)」と入力します。ここで、「[コンテンツ]" 右側の利用可能な列で [コンテンツ] をクリックし、右下隅をクリックして挿入できます (注: 数式の大文字と小文字を間違えないでください)。

#数式分析:

#Excel.Workbook

関数: Excel ワークブックからワークシートのレコードを返します。

パラメータ: Excel.Workbook(ワークブックはバイナリとして、オプションの useHeaders は null 許容の論理として、オプションの遅延タイプは null 許容として logical) as table

この関数はテーブルを返します。最初のパラメータ ワークブックはバイナリ形式で、2 番目のパラメータはオプションのパラメータの論理値です。true は、タイトルを使用することを意味します元のテーブルを新しいテーブルとして使用します。タイトル (デフォルトは false) は、元のワークシートのタイトルを新しい列名に置き換えることを意味します。 3 番目のパラメータについては心配する必要はありません。

ここではフォームの元のタイトルを引き続き使用するため、true と入力します。これにより、タイトルの最初の行を改善する後続のステップが省略されます。

新しい列が正常に追加されました。セルの 1 つをプレビューしてください。以下に示されているのはテーブル スタイルのワークブックです。これはテーブルに直接拡張できます。

「データ」列には、テーブル内のデータを含むテーブルがテーブル形式で表示されます。ここでは、この列を抽出するだけです。カスタム列の右上隅にある展開ボタンをクリックし、展開された列「データ」を選択し、「元の列名をプレフィックスとして使用する」のチェックを外します。

#列名は「データ」になります。このとき「データ」でデータをプレビューしますが、その下に表示されるのがテーブル内の元のデータです。次に、以下のすべてのデータを抽出します。

同様に、カスタム列の右上隅にある展開ボタンをクリックし、すべての列を展開することを選択し、[元の列を使用する] チェックボックスをオンにしないでください。プレフィックスとして名前を付けます。」

#このようにして、レイヤーごとにドリルしてワークシート内のデータを取得します。

最後に「コンテンツ」列を削除します。 「コンテンツ」列を選択し、右クリックして削除します。

#最後に、このテーブルをフォームにアップロードするだけです。

「ホーム」タブの「閉じる」グループにある「閉じてアップロード」をクリックします。

#データはワークシートに要約されます。

[都市] 列のフィルター ボタンをクリックすると、4 つのワークブックのデータがすべてテーブルに含まれていることがわかります。

#フォルダー内にもう 1 つワークブックがある場合はどうなりますか?新しいワークブック「Xi'an」をこのフォルダーに配置してみてください。

#統計を作成したテーブルに戻り、データ タブの下の [] をクリックします。 [接続]グループの[すべて更新]。

#city 列には「西安」が追加されており、これはこのデータを表します。新しいワークブックが追加されました。

概要: Power Query はフォルダーを結合します。各ワークシートのタイトルが同じである限り、結合して要約することができます。このメソッドは次のことを行います。フォルダーは気にせず、任意の数のワークブックを結合できます。また、データ変更はすべて 1 回のクリックで更新できます。

Power Query は、EXCEL データ分析のための強力なツールです。独自の M 言語と組み合わせた簡単なグラフィカルな操作と操作レコーダーを通じて、より多くのデータを統一的に操作し、データ処理を迅速に完了するのに役立ちます。そして最適化。さらに、VBA よりも起動が早く、操作も簡単で、グラフィカルな操作でほとんどのニーズを満たすことができます。皆さん、急いで勉強してください！

関連する学習の推奨事項:

Excel チュートリアル

以上が実践的な Excel スキルの共有: Power Query を使用してフォルダー内のブックを結合するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。