内容
私の YouTube チャンネルで最も視聴されているビデオの 2 つは、Microsoft Excel のフラッシュ フィルに関するビデオです。 このツールの本質は、何らかの方法でソース データを変換する必要がある場合、取得したい結果を隣の列に入力し始めるだけでよいということです。 いくつかのセルを手動で入力した後 (通常は 3 ～ XNUMX 個で十分です)、Excel は必要な変換のロジックを「理解」し、入力した内容を自動的に続行して、単調な作業をすべて完了します。
効率の真髄。 私たち全員が大好きな魔法の「正しく行う」ボタンですよね？
実際、Power Query にはそのようなツールの類似物があります。 例の列 （例のコラム）. 実際、これは Power Query に組み込まれた小さな人工知能であり、データからすばやく学習して変換することができます。 実際のタスクでどこで役立つかを理解するために、いくつかの実際的なシナリオでその機能を詳しく見てみましょう。
例 1. テキストの接着/切断
従業員に関するデータを含む Excel のような「スマート」テーブルがあるとします。
ボタンを使用して、標準的な方法で Power Query に読み込みます。 テーブル/範囲から タブ 且つ (データ — テーブル/範囲から).
各従業員の姓とイニシャルを含む列を追加する必要があるとします (最初の従業員は Ivanov SV など)。 この問題を解決するには、次の XNUMX つの方法のいずれかを使用できます。
- ソース データの列見出しを右クリックし、コマンドを選択します。 例から列を追加 (例から列を追加);
- データを含む XNUMX つまたは複数の列を選択し、タブで 列の追加 チームを選ぶ 例の列. ここでは、ドロップダウン リストで、すべての列を分析する必要があるか、選択した列のみを分析する必要があるかを指定できます。
次に、すべてが簡単です。右側に表示される列に、目的の結果の例を入力し始めます。Power Query に組み込まれている人工知能は、変換ロジックを理解し、独自にさらに続行しようとします。
ところで、この列の任意のセルに正しいオプションを入力できます。つまり、必ずしもトップダウンで行に入力する必要はありません。 また、タイトルバーのチェックボックスを使用して、後で分析から列を簡単に追加または削除できます。
ウィンドウの上部にある式に注意してください。これは、必要な結果を得るためにスマート Power Query が作成するものです。 ちなみに、これがこのツールとの根本的な違いです。 インスタントフィル エクセルで。 インスタント フィルは「ブラック ボックス」のように機能します。変換のロジックを示すのではなく、既製の結果を提供するだけであり、それを当然のことと見なします。 ここではすべてが透過的であり、データで何が起こっているのかを常に完全に明確に理解できます.
Power Query が「アイデアを思いついた」ことがわかった場合は、安全にボタンを押すことができます。 OK またはキーボードショートカット Ctrlキー+入力します – Power Query によって考案された数式を含むカスタム列が作成されます。 ちなみに、後で手動で作成した通常の列として簡単に編集できます (コマンドを使用して、 列の追加 – カスタム列) ステップ名の右側にある歯車アイコンをクリックします。
例 2: case as in 文
テキストのある列見出しを右クリックしてコマンドを選択した場合 変換 （変身）、次に、レジスタの変更を担当するXNUMXつのコマンドを確認できます。
便利でクールですが、たとえば、このリストには、個人的には常にもう XNUMX つの選択肢がありませんでした。文のように、大文字 (大文字) が各単語の最初の文字ではなく、セルの最初の文字になる場合、および小文字（小文字）で表示されます。
この不足している機能は、人工知能で簡単に実装できます 例の列 – Power Query が同じ精神で続行するには、いくつかのオプションを入力するだけです。
ここで式として、Power Query は一連の関数を使用します。 テキスト.アッパー и テキスト.下位、テキストをそれぞれ大文字と小文字に変換、および関数 テキスト.スタート и Text.Mid – Excel 関数の LEFT と PSTR の類似物で、テキストの左と中央から部分文字列を抽出できます。
例 3. 単語の順列
場合によっては、受信したデータを処理するときに、セル内の単語を特定の順序で並べ替える必要が生じることがあります。 もちろん、セパレーターで列を個別の単語列に分割し、指定された順序で貼り付けることもできます (スペースを追加することを忘れないでください)。ただし、ツールの助けを借りて 例の列 すべてがはるかに簡単になります：
例 4: 数字のみ
もう XNUMX つの非常に重要な作業は、セルの内容から数字 (数字) だけを引き出すことです。 前と同じように、Power Query にデータを読み込んだ後、タブに移動します。 列の追加 – 例の列 プログラムが取得したいものを正確に理解できるように、いくつかのセルを手動で入力します。
ビンゴ！
繰り返しますが、ウィンドウの上部を見て、Query が式を正しく生成したことを確認してください。この場合、関数が含まれています。 文章。 選択するこれは、ご想像のとおり、リストに従ってソース テキストから指定された文字を抽出します。 その後、もちろん、このリストは、必要に応じて数式バーで簡単に編集できます。
例 5: テキストのみ
前の例と同様に、テキストのみを引き出したり、その逆を行うことができます。すべての数字、句読点などを削除します。
この場合、すでに反対の意味を持つ関数 Text.Remove が使用されます。この関数は、指定されたリストに従って元の文字列から文字を削除します。
例 6: 英数字のお粥からのデータの抽出
Power Query は、セル内の英数字のお粥から有用な情報を抽出する必要がある場合など、より困難な場合にも役立ちます。たとえば、銀行取引明細書の支払い目的の説明から口座番号を取得します。
Power Query で生成された変換式は非常に複雑になる可能性があることに注意してください。
読みやすく理解しやすいように、無料のオンライン サービスを使用して、より健全な形式に変換できます。 Power Query フォーマッター:
非常に便利なこと - クリエイターに敬意を表します!
例 7: 日付の変換
ツール 例の列 日付列または日時列にも適用できます。 日付の最初の桁を入力すると、Power Query は有効なすべての変換オプションのリストを表示します。
したがって、元の日付を「年-月-日」などの特殊な形式に簡単に変換できます。
例 8: 分類
ツールを使用する場合 例の列 数値データを含む列では、動作が異なります。 従業員のテスト結果が Power Query に読み込まれ (0 ～ 100 の範囲の条件付きスコア)、次の条件付きグラデーションを使用するとします。
- マスターズ – 90 点以上を獲得した者
- エキスパート – 70 から 90 までのスコア
- ユーザー – 30 から 70 まで
- 初心者 – 30 点未満の人
例の列をリストに追加し、これらのグラデーションを手動で配置し始めると、すぐに Power Query がアイデアを採用し、演算子が互いにネストされた数式を含む列を追加します。 if 必要なものと非常によく似たロジックが実装されます。
繰り返しますが、状況を最後まで押すことはできませんが、 OK 次に、式に既に含まれているしきい値を修正します。この方法の方が高速です。
結論
確かにツール 例の列 は「魔法の薬」ではありません。遅かれ早かれ、Power Query が失敗し、必要な結果を得ることができない、データ内の「集団ファーム」の非標準的な状況または特に無視されたケースが発生します。私たちにとって正しく。 しかし、補助ツールとしては非常に優れています。 さらに、彼が生成した式を研究することで、M 言語の機能に関する知識を広げることができます。これは、将来必ず役立つものです。
- Power Query での正規表現 (RegExp) を使用したテキストの解析
- Power Query でのあいまいテキスト検索
- Microsoft Excel のフラッシュ フィル