回傳搜索欄位內容

巨集

777lu 2022-01-31 11:57:12 ‧ 2680 瀏覽

分享至

請各位大神幫忙
在"收貨地址"內，搜尋"需查找內容"的資料，並回傳被搜尋到的內容

需查找的內容字數不一、位置不一，也有可能一個地址有兩個"需查找內容"在內(可二擇一顯示)
例如行2.行6，各有關鍵字在"需查找內容"內
有找到則回傳被"需查找內容"搜索到的欄位
沒有則顯示"-"

請問這樣有辦法做到嗎？還是回傳欄位只能單純顯示"有找到"、"無找到"

電腦版本是使用office2016，附google試算表
https://docs.google.com/spreadsheets/d/1VVgOl6O-S8N2sdb7uhlTfMwlkT0rKpH2mKcT8vB65bo/edit#gid=0

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 8 級 ‧ 2022-02-01 12:54:49

最佳解答

VBA :

Private Sub CommandButton1_Click()
For i = 2 To Range("B2").End(xlDown).Row
  Cells(i, 3) = ""
  For k = 2 To Range("E2").End(xlDown).Row
    If RTrim(Cells(i, 2)) Like "*" & RTrim(Cells(k, 5)) & "*" Then
      If RTrim(Cells(i, 3)) <> "" Then
        Cells(i, 3) = RTrim(Cells(i, 3)) & ","
      End If
      Cells(i, 3) = RTrim(Cells(i, 3)) + RTrim(Cells(k, 5))
    End If
  Next k
  If Cells(i, 3) = "" Then
    Cells(i, 3) = "-"
  End If
Next i
End Sub

回應 2
分享
檢舉

777lu iT邦新手 5 級 ‧ 2022-02-02 15:51:11 檢舉

好讚喔！我成功了！！！謝謝rogeryao大師
如果我需要分成不同工作表sheet1(地址+回傳結果)和sheet2(需查找內容)的話，公式要怎麼修改呢？我自己試都失敗QQ

rogeryao iT邦超人 8 級 ‧ 2022-02-02 16:20:34 檢舉

Private Sub CommandButton1_Click()
For i = 2 To Worksheets("工作表1").Range("B2").End(xlDown).Row
  Worksheets("工作表1").Cells(i, 3) = ""
  For k = 2 To Worksheets("工作表2").Range("A2").End(xlDown).Row
    If RTrim(Worksheets("工作表1").Cells(i, 2)) Like "*" & RTrim(Worksheets("工作表2").Cells(k, 1)) & "*" Then
      If RTrim(Worksheets("工作表1").Cells(i, 3)) <> "" Then
        Worksheets("工作表1").Cells(i, 3) = RTrim(Worksheets("工作表1").Cells(i, 3)) & ","
      End If
      Worksheets("工作表1").Cells(i, 3) = RTrim(Worksheets("工作表1").Cells(i, 3)) + RTrim(Worksheets("工作表2").Cells(k, 1))
    End If
  Next k
  If Worksheets("工作表1").Cells(i, 3) = "" Then
    Worksheets("工作表1").Cells(i, 3) = "-"
  End If
Next i
'
MsgBox "搜尋完成"
Worksheets("工作表1").Activate
End Sub

登入發表回應

ccenjor

iT邦高手 1 級 ‧ 2022-01-31 16:56:57

我的做法比較笨，先在D欄建一輔助欄。
D2:
=IFERROR(MATCH(""&E2&"",B:B,0),"")
再將公式複製到D3:D8，主要是抓取那筆記錄包含E欄關鍵字。

C2:
=IFERROR(VLOOKUP(ROW(),$D$2:$E$10,2,0),"-")
再將公式複製到C3:C10，主要是顯示包含的關鍵字。

這只能針對每個關鍵字只有一筆符合記錄，每筆記錄只能符合一個關鍵字。

回應 1
分享
檢舉

777lu iT邦新手 5 級 ‧ 2022-02-01 03:06:52 檢舉

我希望是excel公式自己幫我找出"需查找內容"有沒有相符合的資料，而不是我自己把已知有的欄位輸入回傳XD

登入發表回應

planetoid

iT邦新手 1 級 ‧ 2022-02-01 00:21:51

我習慣資料逐一橫列處理，所以先將要「查找內容」轉置貼到欄位位置。

(1) 輔助格 (淡紫色網底的儲存格) 如果地址包含關鍵字，則回傳關鍵字。

C3 儲存格公式：如果 $B3 (地址) 儲存格包含 C$2 關鍵字，則回傳 C$2 關鍵字，若無則回傳空值。為什麼使用 COUNT 函數請看這篇文章 Excel 布林搜尋多個關鍵字。

=IF(COUNT(SEARCH(C$2, $B3)), C$2, "")

下圖淡紫色網底的儲存格，可以看到如果地址包含查找內容，就會回傳

(2) 輔助格 (深紫色網底的儲存格) 將輔助格 (淡紫色) 可能有多個符合的關鍵字，用逗號黏在一起，如果沒有內容則回傳 - 符號。

I3 儲存格公式：使用 Office 365 或 Google 試算表才支援的 TEXTJOIN 函數，將 C3:H3 欄位值，用逗號黏在一起。如果比較舊版 Excel 則要自行改寫成 C3&","&D3 ...。再使用 LEN 判斷黏在一起的欄位值文字長度是否是 0，如果是 0 ，則回傳 - 符號。

=IF(LEN(TEXTJOIN(",", 1, C3:H3))>0, TEXTJOIN(",", 1, C3:H3), "-")

可自行線上操作的連結

回應 2
分享
檢舉

777lu iT邦新手 5 級 ‧ 2022-02-01 02:34:55 檢舉

請問如果"需查找內容"內的資料多達上百條，還適合將資料逐一橫列處理嗎？雖然得到了回傳的搜尋結果，中間可以用隱藏忽略，但似乎不利於將來"需查找內容"的資料更新＆新增。

planetoid iT邦新手 1 級 ‧ 2022-02-01 07:14:24 檢舉

「回傳搜尋結果」從 I 欄往前移到 C 欄，日後新增的關鍵字就比較方便往最右邊放。

C3 欄位格公式中「需查找內容」儲存格範圍，雖然還沒有到 Z 欄，可以用 D3:Z3 預留比較彈性的範圍

=IF(LEN(TEXTJOIN(",", 1, D3:Z3))>0, TEXTJOIN(",", 1, D3:Z3), "-")

上百條還不會到 Excel 的規格及限制工作表上的總列數和欄數限制：1,048,576 列乘以 16,384 欄。但是如果日後資料真得太多，我不會使用 Excel 太複雜的操作以及速度會變慢兩個原因，而改用資料庫。

可自行線上操作的連結「工作表2」

登入發表回應

我要發表回答

立即登入回答

參賽組數

1123 組

團體組數

52 組

累計文章數

23096 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js

IT邦幫忙