内容
参照リストに従って、テキストを数式ですばやく一括して置き換える方法 – 既に整理済みです。 それでは、Power Query で実行してみましょう。
よくあることですが 実行する このタスクは、説明するよりもはるかに簡単です なぜ 動作しますが、両方を実行してみましょう 🙂
したがって、キーボード ショートカットを使用して通常の範囲から作成された XNUMX つの「スマートな」動的テーブルがあります。 Ctrlキー+T またはチーム ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット):
最初のテーブルに電話しました 且つ、XNUMX 番目のテーブル – ディレクトリフィールドを使用して テーブル名 (テーブル名) タブ コンストラクタ （設計）.
タスク: テーブル内のアドレスを置き換えます 且つ 列からのすべての出現 見つけるには ハンドブック 列から対応する正しい相手に 代替. セル内の残りのテキストはそのままにしておく必要があります。
ステップ 1. ディレクトリを Power Query に読み込み、リストに変換する
アクティブ セルを参照テーブルの任意の場所に設定したら、タブをクリックします。 且つ （日にち）またはタブで パワークエリ (古いバージョンの Excel を使用していて、別のタブで Power Query をアドインとしてインストールした場合) ボタン テーブル/範囲から (テーブル/範囲から).
参照テーブルが Power Query クエリ エディターに読み込まれます。
干渉しないように、自動的に追加されたステップ 改造型 (タイプ変更) 右側のパネルで、適用されたステップを安全に削除して、ステップのみを残すことができます ソース （ソース）:
ここで、さらに変換と置換を実行するには、このテーブルをリスト (リスト) に変換する必要があります。
叙情的な逸脱
- 表 複数の行と列で構成される XNUMX 次元配列です。
- 記録 (記録) – XNUMX 次元の配列文字列。たとえば、名前を持つ複数のフィールド要素で構成されます。 [名前=「マーシャ」、性別=「f」、年齢=25]
- リスト – 複数の要素で構成される XNUMX 次元の配列列。たとえば、 {1、2、3、10、42} or { "信仰希望愛" }
私たちの問題を解決するために、主に型に関心があります リスト.
ここでの秘訣は、Power Query のリスト アイテムは、平凡な数字やテキストだけでなく、他のリストやレコードにもなり得るということです。 ディレクトリを変更する必要があるレコード（レコード）で構成される、非常にトリッキーなリスト（リスト）にあります。 Power Query の構文表記 (角括弧内のエントリ、中括弧内のリスト) では、次のようになります。
{
[ 検索 = 「聖。 サンクトペテルブルク」、Replace = 「サンクトペテルブルク」 サンクトペテルブルク」 ] ,
[ 検索 = 「聖。 サンクトペテルブルク」、Replace = 「サンクトペテルブルク」 サンクトペテルブルク」 ] ,
[ 検索 = 「ピーター」、置換 = 「聖。 サンクトペテルブルク」 ] ,
等々
}
このような変換は、Power Query に組み込まれている M 言語の特別な関数を使用して実行されます。 テーブル.ToRecords. 数式バーに直接適用するには、この関数をそこのステップ コードに追加します。 ソース.
そうだった：
後：
Table.ToRecords 関数を追加すると、テーブルの外観が変わり、レコードのリストに変わります。 個々のレコードの内容は、任意の単語の横にあるセルの背景をクリックすると、ビュー ペインの下部に表示されます。 USBレコーディング （でも一言じゃない！）
上記に加えて、作成したリストをキャッシュ (バッファリング) するために、もう XNUMX つのストロークを追加することは理にかなっています。 これにより、Power Query は一度ルックアップ リストをメモリにロードし、後でアクセスして置換するときに再度再計算しないようにします。 これを行うには、数式を別の関数でラップします – リストバッファ:
このようなキャッシングにより、大量の初期データが消去され、速度が非常に顕著に (数倍!) 向上します。
これでハンドブックの作成は完了です。
クリックする必要があります ホーム – 閉じてロード – 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロードへ..)、 選択肢一つを選択してください 接続を作成するだけ (接続のみ作成) そしてエクセルに戻ります。
ステップ 2. データ テーブルのロード
ここではすべてが陳腐です。 以前の参考書と同様に、表の任意の場所に移動し、タブをクリックします 且つ (Comma Separated Values) ボタンをクリックして、各々のジョブ実行の詳細（開始/停止時間、変数値など）のCSVファイルをダウンロードします。 テーブル/範囲から そして私たちのテーブル 且つ Power Query に入ります。 自動的に追加されたステップ 改造型 (タイプ変更) 次のものを削除することもできます。
特別な準備作業は必要なく、最も重要なことに移ります。
ステップ 3. List.Accumulate 関数を使用して置換を実行する
コマンドを使用して、計算列をデータ テーブルに追加しましょう。 列の追加 – カスタム列 (列を追加 — カスタム列): 開いたウィンドウに追加した列の名前を入力します (たとえば、 訂正した住所) と魔法の関数 リスト.累積:
クリックする必要があります OK – そして、置換が行われた列を取得します。
ご了承ください：
- Power Query では大文字と小文字が区別されるため、最後から XNUMX 番目の行に置換はありません。これは、ディレクトリに「SPb」ではなく「SPb」があるためです。
- ソース データに一度に置換する部分文字列が複数ある場合 (たとえば、7 行目で「S-Pb」と「Prospectus」の両方を置換する必要がある場合)、問題は発生しません (以前の方法)。
- ソース テキスト (9 行目) に置換するものが何もない場合、エラーは発生しません (数式による置換とは異なります)。
このようなリクエストの速度は非常にまともです。 たとえば、サイズが 5000 行の初期データのテーブルの場合、このクエリは 3 秒未満で更新されました (ちなみに、バッファリングなしで約 XNUMX 秒です!)。
List.Accumulate 関数のしくみ
原則として、これでこの記事は (私が書き、あなたが読む) 終わりになる可能性があります。 できるようにするだけでなく、「ボンネットの下」でどのように機能するかを理解したい場合は、ウサギの穴にもう少し深く入り込み、すべての一括置換を行った List.Accumulate 関数を処理する必要があります。私たちのために働いてください。
この関数の構文は次のとおりです。
=List.Accumulate(リスト, シード, アキュムレータ)
コラボレー
- リスト 要素を反復処理するリストです。
- シード - 初期状態
- アキュムレータ – リストの次の要素に対して何らかの操作 (数学、テキストなど) を実行し、処理の結果を特殊変数に蓄積する関数。
一般に、Power Query で関数を記述するための構文は次のようになります。
(argument1, argument2, … argumentN) => 引数を持ついくつかのアクション
たとえば、合計関数は次のように表すことができます。
(a, b) => a + b
List.Accumulate の場合、このアキュムレータ関数には XNUMX つの必須引数があります (名前は何でもかまいませんが、通常は 状態 и 現在、この関数の公式ヘルプのように、ここで:
- 状態 – 結果が蓄積される変数 (初期値は上記のものです) シード)
- 現在 – リストからの次の反復値 リスト
たとえば、次の構築のロジックのステップを見てみましょう。
=List.Accumulate({3、2、5}, 10, (状態、現在) => 状態 + 現在)
- 可変値 状態 初期引数と同じに設定されます シードIe 状態 = 10
- リストの最初の要素 (現在 = 3) 変数に追加します 状態 （十）。 我々が得る 状態 = 13.
- リストの XNUMX 番目の要素 (現在 = 2) そして、それを変数の現在の累積値に加算します 状態 （十）。 我々が得る 状態 = 15.
- リストの XNUMX 番目の要素 (現在 = 5) そして、それを変数の現在の累積値に加算します 状態 （十）。 我々が得る 状態 = 20.
最新の累計です 状態 値は List.Accumulate 関数であり、結果として出力されます。
少し空想する場合は、List.Accumulate 関数を使用して、たとえば、Excel 関数の CONCATENATE をシミュレートできます (Power Query では、その類似物が呼び出されます テキスト.結合) 式を使用:
または、最大値を検索することもできます (Power Query で呼び出される Excel の MAX 関数の模倣) リスト最大):
ただし、List.Accumulate の主な機能は、引数として単純なテキストまたは数値リストだけでなく、より複雑なオブジェクト (リストからのリストやレコードからのリストなど) を処理できることです (こんにちは、ディレクトリ!)。
問題で置換を実行した構造をもう一度見てみましょう。
List.Accumulate(ディレクトリ, [住所], (状態、現在) => Text.Replace(状態、現在の[検索]、現在の[置換]) )
ここで実際に何が起こっているのですか？
- 初期値として (シード) 列から最初のぎこちないテキストを取得します [住所] 私たちのテーブル: 199034、サンクトペテルブルク、str。 ベリンガ、d. 1
- 次に、 List.Accumulate はリストの要素を XNUMX つずつ繰り返し処理します – ハンドブック. このリストの各要素は、「何を見つけて何と置き換えるか」、つまりディレクトリの次の行のフィールドのペアで構成されるレコードです。
- アキュムレータ関数は変数に入れます 状態 初期値（先頭アドレス 199034、サンクトペテルブルク、str。 ベリンガ、d. 1) でアキュムレータ関数を実行します。標準の M 関数を使用した置換操作です。 テキスト.置換 (Excel の SUBSTITUTE 関数に似ています)。 その構文は次のとおりです。
Text.Replace( 元のテキスト、探しているもの、置き換えているもの)
そしてここに私達があります：
- 状態 私たちのダーティーアドレスです。 状態 (そこからの行き方 シード)
- 現在の[検索] – フィールド値 見つけるには リストの次の反復エントリから ディレクトリ、変数にある 現在
- 現在の[置換] – フィールド値 代替 リストの次の反復エントリから ディレクトリ横たわっている 現在
したがって、アドレスごとに、ディレクトリ内のすべての行の列挙の完全なサイクルが毎回実行され、[Find] フィールドのテキストが [Replace] フィールドの値に置き換えられます。
あなたがアイデアを得たことを願っています🙂
- 数式を使用してリスト内のテキストを一括置換する
- Power Query の正規表現 (RegExp)