内容
複数のセルのテキストをすばやく XNUMX つに接着し、逆に長いテキスト文字列を解析してコンポーネントに分割する方法については、既に書きました。 次に、もう少し複雑なタスクを見てみましょう。特定の条件が満たされたときに、複数のセルからテキストを接着する方法です。
顧客のデータベースがあり、XNUMX つの会社名がその従業員の複数の異なる電子メールに対応しているとします。 私たちのタスクは、会社名ごとにすべてのアドレスを収集し、それらを (カンマまたはセミコロンで区切って) 連結して、たとえば顧客向けのメーリング リストを作成することです。つまり、次のような出力を取得します。
つまり、条件に応じてテキストを接着 (リンク) するツールが必要です。これは関数の類似物です。 サマーリ (スミフ)、ただしテキスト用。
方法 0. 式
あまりエレガントではありませんが、最も簡単な方法です。 次の行の会社が前の行と異なるかどうかを確認する簡単な数式を作成できます。 変わらない場合は、次のアドレスをカンマで区切って貼り付けます。 異なる場合は、蓄積されたものを「リセット」し、最初からやり直します。
このアプローチの欠点は明らかです。得られた追加の列のすべてのセルから、各会社の最後のセル (黄色) だけが必要です。 リストが大きい場合、それらをすばやく選択するには、関数を使用して別の列を追加する必要があります DLSTR （レン）、蓄積された文字列の長さを確認します。
これで、それらを除外し、さらに使用するために必要なアドレスの接着をコピーできます。
方法 1. XNUMX つの条件による接着のマクロ関数
元のリストが会社別にソートされていない場合、上記の単純な式は機能しませんが、VBA の小さなカスタム関数を使用して簡単に回避できます。 キーボード ショートカットを押して Visual Basic Editor を開きます Altキー+ F11 またはボタンを使用して ビジュアルベーシック タブ 開発者 （デベロッパー）. 開いたウィンドウで、メニューから新しい空のモジュールを挿入します インサート – モジュール そこに関数のテキストをコピーします。
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " 接着は互いに等しくありません - エラーで終了します If SearchRange.Count <> TextRange.Count次に、MergeIf = CVErr(xlErrRef) Exit Function End 'すべてのセルを調べて、条件を確認し、変数 OutText にテキストを収集します。i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i '最後のデリミタなしで結果を表示 MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End関数
ここで Microsoft Excel に戻ると、関数のリスト (ボタン fx 数式バーまたはタブで 数式 – 関数の挿入）関数を見つけることが可能になります MergeIf カテゴリー内 ユーザー定義 （ユーザー定義の）. 関数の引数は次のとおりです。
方法 2. 不正確な条件でテキストを連結する
マクロの 13 行目の最初の文字を置き換えると、 = 近似一致演算子に いいね、その後、初期データと選択基準との不正確な一致によって接着を実行することが可能になります。 たとえば、会社名がさまざまなバリアントで記述できる場合、XNUMX つの関数でそれらすべてをチェックして収集できます。
標準のワイルドカードがサポートされています。
- アスタリスク (*) – 任意の数の任意の文字 (それらの不在を含む) を示します
- 疑問符 (?) – 任意の XNUMX 文字を表します
- ポンド記号 (#) – 任意の 0 桁 (9 ～ XNUMX) を表します
デフォルトでは、Like 演算子は大文字と小文字を区別します。つまり、たとえば、「Orion」と「orion」を別の会社として認識します。 大文字と小文字を区別しないようにするには、Visual Basic エディターでモジュールの最初に行を追加します。 オプション比較テキスト、Like を大文字と小文字を区別しないように切り替えます。
このようにして、条件をチェックするための非常に複雑なマスクを作成できます。次に例を示します。
- ?1##??777RUS – 777 から始まる 1 地域のすべてのナンバー プレートの選択
- LLC* – 名前が LLC で始まるすべての会社
- ##7## – 7 桁目が XNUMX の XNUMX 桁のデジタル コードを持つすべての製品
- ??????? – XNUMX文字のすべての名前など
方法 3. XNUMX つの条件でテキストを貼り付けるマクロ関数
作業では、テキストを複数の条件にリンクする必要がある場合に問題が発生する可能性があります。 たとえば、前の表で都市の列がもう XNUMX つ追加され、特定の会社だけでなく特定の都市に対しても接着を実行する必要があるとします。 この場合、別の範囲チェックを追加して、関数を少し最新化する必要があります。
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimiter = ", " '区切り文字 (スペースや ; などに置き換え可能) e.) '検証範囲と接着範囲が等しくない場合、エラーで終了する If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'すべてのセルを調べ、すべての条件をチェックし、テキストを変数 OutText に収集する For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i ' 最後の区切り文字なしで結果を表示 MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
まったく同じ方法で適用されます – 引数のみをさらに指定する必要があります:
方法 4. Power Query でのグループ化と接着
無料の Power Query アドインを使用すると、VBA でプログラミングしなくても問題を解決できます。 Excel 2010 ～ 2013 の場合は、ここからダウンロードできます。Excel 2016 では、既定で既に組み込まれています。 アクションのシーケンスは次のようになります。
Power Query は通常のテーブルを操作する方法を認識していないため、最初のステップは、テーブルを "スマート" テーブルに変換することです。 これを行うには、それを選択して組み合わせを押します Ctrlキー+T またはタブから選択 ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット). 次に表示されるタブで コンストラクタ （設計） テーブル名を設定できます（私は標準を残しました テーブル1):
テーブルを Power Query アドインに読み込みましょう。 これを行うには、タブで 且つ (Excel 2016 を使用している場合) または [Power Query] タブ (Excel 2010-2013 を使用している場合) をクリックします。 テーブルから (データ — テーブルから):
開いたクエリ エディター ウィンドウで、ヘッダーをクリックして列を選択します。 会社概要 そして上のボタンを押す グループ (グループ化). 新しい列の名前とグループ化の操作のタイプを入力します – すべての行 (すべての行):
[OK] をクリックすると、会社ごとにグループ化された値のミニ テーブルが表示されます。 結果の列のセルの白い背景 (テキストではなく!) を左クリックすると、テーブルの内容がはっきりと見えます。
ここで、関数を使用して、各ミニ テーブルの Address 列の内容をコンマで区切って貼り付けます。 これを行うには、タブで 列を追加 押す カスタム列 (列を追加 — カスタム列) 表示されるウィンドウで、Power Query に組み込まれている M 言語で新しい列の名前と結合式を入力します。
すべての M 関数は大文字と小文字を区別することに注意してください (Excel とは異なります)。 をクリックした後 OK アドレスが貼り付けられた新しい列を取得します。
すでに不要な列を削除する必要があります テーブルアドレス （タイトル右クリック） 列を削除) タブをクリックして、結果をシートにアップロードします。 ホーム — 閉じてダウンロード (ホーム — 閉じてロード):
重要なニュアンス: 以前の方法 (関数) とは異なり、Power Query からのテーブルは自動的に更新されません。 将来、ソース データに変更があった場合は、結果テーブルの任意の場所を右クリックして、コマンドを選択する必要があります。 更新して保存 （更新）.
- 長いテキスト文字列をパーツに分割する方法
- 異なるセルのテキストを XNUMX つに接着するいくつかの方法
- Like 演算子を使用してマスクに対してテキストをテストする