メインコンテンツへスキップ

Excelの基準に基づいてテキストを簡単に連結するにはどうすればよいですか?

いくつかの重複を含むID番号の列と名前の列があり、次に、左のスクリーンショットに示すように一意のID番号に基づいて名前を連結し、基準に基づいてテキストをすばやく結合したいとします。 Excelで行いますか?

docは、基準1に基づいてテキストを結合します

ユーザー定義関数を使用して、基準に基づいてテキストを連結します

基準に基づいてテキストをKutoolsforExcelで連結します


テキストを一意のID番号と組み合わせるには、最初に一意の値を抽出してから、ユーザー定義関数を作成して、一意のIDに基づいて名前を組み合わせることができます。

1。 次のデータを例にとると、最初に一意のID番号を抽出する必要があります。この配列式を適用してください。 =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),"")、この数式を空白のセル(たとえば、D2)に入力し、を押します。 Ctrl + Shift + Enter キーを一緒に、スクリーンショットを参照してください:

docは、基準2に基づいてテキストを結合します

先端:上記の式では、 A2:A15 一意の値を抽出するリストデータ範囲です。 D1 抽出結果を出力する列の最初のセルです。

2。 次に、塗りつぶしハンドルを下にドラッグして、空白が表示されるまですべての一意の値を抽出します。スクリーンショットを参照してください。

docは、基準3に基づいてテキストを結合します

3。 このステップでは、を作成する必要があります ユーザー定義関数 一意のID番号に基づいて名前を組み合わせるには、 Alt + F11 キー、そしてそれは開きます アプリケーション向け Microsoft Visual Basic 窓。

4に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュール 窓。

VBAコード:基準に基づいてテキストを連結します

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. 次に、このコードを保存して閉じ、ワークシートに戻って、この数式をセルE2に入力します。 = CONCATENATEIF($ A $ 2:$ A $ 15、D2、$ B $ 2:$ B $ 15、 "、") 、スクリーンショットを参照してください:

docは、基準4に基づいてテキストを結合します

6。 次に、この数式を適用するセルまで塗りつぶしハンドルをドラッグすると、対応するすべての名前がID番号に基づいて結合されます。スクリーンショットを参照してください。

docは、基準5に基づいてテキストを結合します

ヒント:

1.上記の式では、 A2:A15 に基づいて結合する元のデータです。 D2 抽出した一意の値であり、 B2:B15 組み合わせたい名前の列です。

2.ご覧のとおり、コンマで区切られた値を組み合わせました。必要に応じて、数式のコンマ「、」を変更することで、他の文字を使用できます。


あなたが持っている場合 Kutools for Excelそのと 高度な結合行 ユーティリティを使用すると、基準に基づいてテキストベースをすばやく便利に連結できます。

Kutools for Excel : 300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます.

インストールした後 Kutools for Excel、次の手順で実行します。

1。 XNUMXつの列に基づいて、結合するデータ範囲を選択します。

2に設定します。 OK をクリックします。 クツール > マージ&スプリット > 高度な結合行、スクリーンショットを参照してください:

3。 の中に 列に基づいて行を組み合わせる ダイアログボックスで、[ID]列をクリックし、[ 主キー この列を、結合されたデータの基になるキー列にするには、スクリーンショットを参照してください。

docは、基準7に基づいてテキストを結合します

4。 そして、 お名前 値を結合する列をクリックし、 組み合わせる オプションを選択し、結合されたデータに対してXNUMXつのセパレーターを選択します。スクリーンショットを参照してください。

docは、基準8に基づいてテキストを結合します

5。 これらの設定が完了したら、をクリックします OK ダイアログを終了するには、列Bのデータがキー列Aに基づいて結合されています。スクリーンショットを参照してください。

docは、基準9に基づいてテキストを結合します

この機能を使用すると、次の問題ができるだけ早く解決されます。

複数の行をXNUMXつに結合し、Excelで重複を合計する方法は?

今すぐExcel用のKutoolsをダウンロードして無料トライアル!


Kutools for Excel:300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます。 今すぐダウンロードして無料トライアル!

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
This comment was minimized by the moderator on the site
BTW i used the VBA solution
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For I = 1 To CriteriaRange.Count
If CriteriaRange.Cells(I).Value = Condition Then
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations