問題の定式化
ほとんどの Excel ユーザーが遅かれ早かれ直面する非常に標準的な状況の XNUMX つに対する美しいソリューションを見てみましょう。大量のファイルからデータをすばやく自動的に収集して XNUMX つの最終的なテーブルにする必要があります。
次のフォルダーがあるとします。このフォルダーには、支部都市のデータを含むいくつかのファイルが含まれています。
ファイルの数は重要ではなく、将来変更される可能性があります。 各ファイルには次の名前のシートがあります 営業データ テーブルの場所:
もちろん、テーブルの行数 (順序) は異なりますが、列のセットはどこでも標準です。
タスク: すべてのファイルから XNUMX つのブックにデータを収集し、都市ファイルまたはテーブルの行を追加または削除するときに、その後の自動更新を行います。 最終的に統合されたテーブルに従って、任意のレポート、ピボット テーブル、フィルター ソート データなどを作成できます。主なことは、収集できることです。
武器を選択します
ソリューションには、最新バージョンの Excel 2016 (必要な機能は既定で既に組み込まれています)、または無料のアドインがインストールされた以前のバージョンの Excel 2010-2013 が必要です。 パワークエリ Microsoft から (ここからダウンロードしてください)。 Power Query は、データを外部から Excel に読み込み、データを取り出して処理するための非常に柔軟で非常に強力なツールです。 Power Query は、テキスト ファイルから SQL、さらには Facebook まで、既存のほぼすべてのデータ ソースをサポートしています 🙂
Excel 2013 または 2016 をお持ちでない場合は、これ以上読むことはできません (冗談です)。 古いバージョンの Excel では、このようなタスクは、Visual Basic でマクロをプログラミングするか (初心者にとっては非常に困難)、単調な手動コピー (時間がかかり、エラーが発生する) によってのみ達成できます。
ステップ 1. XNUMX つのファイルをサンプルとしてインポートする
まず、例として XNUMX つのブックからデータをインポートして、Excel が「アイデアを拾う」ようにします。 これを行うには、新しい空のワークブックを作成して…
- Excel 2016 をお持ちの場合は、タブを開きます 且つ その後 クエリの作成 – ファイルから – ブックから (データ - 新しいクエリ - ファイルから - Excel から)
- Power Query アドインがインストールされた Excel 2010-2013 がある場合は、タブを開きます パワークエリ そしてそれを選択します ファイルから – 本から (ファイルから — Excel から)
次に、開いたウィンドウで、レポートを含むフォルダーに移動し、都市ファイルのいずれかを選択します (それらはすべて典型的なものであるため、どれでもかまいません)。 数秒後、ナビゲーター ウィンドウが表示され、左側で必要なシート (Sales) を選択する必要があり、その内容が右側に表示されます。
このウィンドウの右下隅にあるボタンをクリックすると、 ダウンロード （負荷）の場合、テーブルはすぐに元の形式でシートにインポートされます。 単一のファイルの場合はこれで問題ありませんが、そのようなファイルを多数ロードする必要があるため、少し違った方法でボタンをクリックします。 補正 （編集）. その後、本のデータを含む Power Query クエリ エディターが別のウィンドウに表示されます。
これは、テーブルを必要なビューに「仕上げる」ことができる非常に強力なツールです。 すべての機能を表面的に説明するだけでも約 XNUMX ページかかりますが、非常に簡単に説明すると、このウィンドウを使用して次のことができます。
- 不要なデータ、空の行、エラーのある行を除外します
- XNUMX つ以上の列でデータを並べ替える
- 繰り返しを取り除く
- 付箋テキストを列ごとに分割します (区切り文字、文字数など)。
- テキストを整理する (余分なスペースを削除する、大文字と小文字を正しくするなど)
- あらゆる可能な方法でデータ型を変換します (テキストのような数値を通常の数値に、またはその逆に変換します)
- テーブルを転置 (回転) し、XNUMX 次元のクロス テーブルをフラットなものに拡張する
- テーブルに列を追加し、Power Query に組み込まれている M 言語を使用して数式と関数を使用します。
- ...
たとえば、後でピボット テーブル レポートを簡単に作成できるように、月のテキスト名を含む列をテーブルに追加してみましょう。 これを行うには、列見出しを右クリックします dateコマンドを選択します 重複する列 (重複列)をクリックし、表示される重複列のヘッダーを右クリックして、[コマンド] を選択します。 変換 – 月 – 月の名前:
各行の月のテキスト名で新しい列を形成する必要があります。 列見出しをダブルクリックすると、名前を変更できます。 日付のコピー より快適に 月、例えば。
一部の列で、プログラムがデータ型を正しく認識していない場合は、各列の左側にあるフォーマット アイコンをクリックしてください。
簡単なフィルターを使用して、エラーや空の行、不要なマネージャーや顧客を含む行を除外できます。
さらに、実行されたすべての変換は右側のパネルで固定され、いつでもロールバック (クロス) したり、パラメーターを変更 (ギア) したりできます。
軽くてエレガントですね。
ステップ 2. リクエストを関数に変換しましょう
インポートされた各書籍に対して行われたすべてのデータ変換を後で繰り返すには、作成したリクエストを関数に変換する必要があります。この関数は、すべてのファイルに適用されます。 これを行うには、実際には非常に簡単です。
クエリ エディターで、[表示] タブに移動し、ボタンをクリックします。 高度なエディタ (表示 — 詳細エディター). 以前のすべてのアクションが M 言語のコード形式で記述されるウィンドウが開くはずです。 この例でインポートしたファイルへのパスは、コードにハードコーディングされていることに注意してください。
次に、いくつかの調整を行います。
それらの意味は単純です: 最初の行 (ファイルパス)=> プロシージャを引数を持つ関数に変換します ファイルパス、および以下では、固定パスをこの変数の値に変更します。
全て。 クリック 終了 そしてこれを見るべきです：
データが消えてしまったことを恐れないでください - 実際、すべて問題ありません。すべてがこのように見えるはずです 🙂 カスタム関数の作成に成功しました。ここでは、データのインポートと処理のアルゴリズム全体が特定のファイルに関連付けられることなく記憶されます。 . よりわかりやすい名前を付ける必要があります（たとえば、 データの取得) フィールドの右側のパネルに First Name そしてあなたは刈り取ることができます ホーム — 閉じてダウンロード (ホーム — 閉じてロード). この例でインポートしたファイルへのパスは、コードにハードコーディングされていることに注意してください。 メインの Microsoft Excel ウィンドウに戻りますが、関数への接続が作成されたパネルが右側に表示されます。
ステップ 3. すべてのファイルを収集する
最も難しい部分はすべて後ろにあり、快適で簡単な部分は残ります。 タブに移動 データ – クエリの作成 – ファイルから – フォルダーから (データ — 新しいクエリ — ファイルから — フォルダから) または、Excel 2010-2013 を使用している場合は、タブと同様に パワークエリ. 表示されるウィンドウで、すべてのソース都市ファイルが配置されているフォルダーを指定し、 OK. 次のステップでは、このフォルダー (およびそのサブフォルダー) にあるすべての Excel ファイルと、それぞれの詳細が一覧表示されるウィンドウが開きます。
クリック 変更する （編集） そして再び、おなじみのクエリ エディタ ウィンドウに入ります。
ここで、作成した関数を使用してテーブルに別の列を追加する必要があります。これにより、各ファイルからデータが「プル」されます。 これを行うには、タブに移動します 列の追加 – カスタム列 (列の追加 — カスタム列の追加) 表示されるウィンドウで、関数を入力します データの取得、各ファイルへのフル パスを引数として指定します。
をクリックした後 OK 作成された列が右側のテーブルに追加されます。
次に、不要な列をすべて削除しましょう (Excel の場合と同様に、マウスの右ボタンを使用して – 削除します)、追加された列とファイル名の列のみを残します。これは、この名前 (より正確には都市) が各行の合計データに含まれていると便利だからです。
そして今、「すごい瞬間」 – 関数で追加された列の右上隅にある独自の矢印が付いたアイコンをクリックします。
…チェックを外す 元の列名をプレフィックスとして使用する (元の列名をプレフィックスとして使用)をクリックし OK. そして、関数は各ファイルからデータを読み込んで処理し、記録されたアルゴリズムに従い、すべてを共通テーブルに収集します。
完全に美しくするために、ファイル名の最初の列から .xlsx 拡張子を削除することもできます。これは、標準的な「何もない」に置き換える (列ヘッダーを右クリックする) ことによります。 代替）、この列の名前を 市町村. また、日付の列のデータ形式も修正します。
全て！ クリック ホーム – 閉じてロード (ホーム — クローズ & ロード). すべての都市のクエリによって収集されたすべてのデータは、現在の Excel シートに「スマート テーブル」形式でアップロードされます。
作成された接続とアセンブリ関数を個別に保存する必要はありません。通常の方法で現在のファイルと一緒に保存されます。
将来、フォルダー (都市の追加または削除) またはファイル (行数の変更) に変更があった場合は、テーブルまたは右側のパネルのクエリを直接右クリックして、指図 更新して保存 （更新） – Power Query は、数秒ですべてのデータを再び「再構築」します。
PS
修正。 2017 年 XNUMX 月の更新の後、Power Query は Excel ブックを単独で収集する方法を学習しました。つまり、別の関数を作成する必要がなくなりました。これは自動的に行われます。 したがって、この記事の XNUMX 番目のステップは不要になり、プロセス全体が大幅に簡素化されます。
- 選択する リクエストの作成 – ファイルから – フォルダーから – フォルダーを選択 – OK
- ファイルのリストが表示されたら、 を押します。 変更する
- クエリ エディター ウィンドウで、バイナリ列を二重矢印で展開し、各ファイルから取得するシート名を選択します。
そしてそれだけです！ 歌！
- ピボット テーブルの作成に適したフラットなクロス集計への再設計
- Power View でのアニメーション バブル チャートの作成
- 異なる Excel ファイルのシートを XNUMX つにまとめるマクロ