最近では、インターネットから XML データをインポートするための FILTER.XML 関数の使用について説明しました。これは、実際には、この関数の主な目的です。 しかし、途中で、この関数の別の予想外の美しい使用方法が明らかになりました。それは、スティッキー テキストをセルにすばやく分割するためです。
次のようなデータ列があるとします。
もちろん、便宜上、会社名、市区町村、番地、家屋という別々の列に分割したいと思います。 これは、さまざまな方法で行うことができます。
- 列ごとのテキスト タブから 且つ (データ — テキストから列へ) そして三歩進む テキストパーサー. ただし、明日データが変更された場合は、プロセス全体をもう一度繰り返す必要があります。
- このデータを Power Query に読み込み、そこで分割してからシートにアップロードし直し、データが変更されたときにクエリを更新します (これは既に簡単です)。
- その場で更新する必要がある場合は、非常に複雑な式をいくつか記述して、コンマを検索し、それらの間のテキストを抽出できます。
FILTER.XML 関数を使用してより洗練された方法で実行することもできますが、それとどのような関係があるのでしょうか?
FILTER.XML 関数は、最初の引数として XML コード (特別なタグと属性でマークアップされたテキスト) を受け取り、それをコンポーネントに解析して、必要なデータ フラグメントを抽出します。 XML コードは通常、次のようになります。
XML では、各データ要素をタグで囲む必要があります。 タグは、山かっこで囲まれたテキスト (上記の例ではマネージャー、名前、利益) です。 タグは常に、開始と終了 (先頭にスラッシュが追加されたもの) のペアになります。
FILTER.XML 関数は、必要なすべてのタグの内容 (たとえば、すべてのマネージャーの名前) を簡単に抽出し、(最も重要なこととして) それらを XNUMX つのリストに一度に表示できます。 したがって、私たちのタスクは、ソース テキストにタグを追加し、FILTER.XML 関数によるその後の分析に適した XML コードに変換することです。
例としてリストの最初のアドレスを取り上げると、それを次の構造に変換する必要があります。
グローバルな開始と終了のすべてのテキストタグを呼び出しました t、各要素を囲むタグは s., しかし、他の任意の指定を使用できます – それは問題ではありません.
このコードからインデントと改行を削除すると (ちなみに完全に省略可能であり、わかりやすくするためにのみ追加されます)、これはすべて行になります。
そして、カンマをいくつかのタグに置き換えることで、送信元アドレスから比較的簡単に取得できます 関数を使用する 代替 （代わりの） そしてシンボルで接着 & 開始タグと終了タグの最初と最後に:
結果の範囲を水平方向に拡張するには、標準関数を使用します 輸送 （転置）で、式をラップします。
この設計全体の重要な特徴は、動的配列をサポートする Office 2021 および Office 365 の新しいバージョンでは、入力に特別なジェスチャーは必要なく、入力してクリックするだけであるということです。 入力します – 数式自体が必要な数のセルを占有し、すべてがうまく機能します。 動的配列がまだなかった以前のバージョンでは、数式を入力する前に十分な数の空のセルを最初に選択する必要があり (余白を使用できます)、数式を作成した後、キーボード ショートカットを押します。 Ctrlキー+シフト+入力します配列数式として入力します。
同様のトリックを使用して、テキストを改行で XNUMX つのセルにまとめることができます。
前の例との唯一の違いは、カンマの代わりに、非表示の Alt + Enter 改行文字を置き換えていることです。これは、CHAR 関数を使用してコード 10 で数式に指定できます。
- Excel での改行 (Alt + Enter) の微妙な操作
- Excelでテキストを列ごとに分割する
- テキストを SUBSTITUTE に置き換える