Board logo

標題: [發問] 只輸入簡短文字或數字,以名稱或帳號查詢 [打印本頁]

作者: cypd    時間: 2023-1-17 17:39     標題: 只輸入簡短文字或數字,以名稱或帳號查詢

只輸入簡短文字或數字,以名稱或帳號查詢

問題如下:
今因帳號大部分前10碼為相同數字
以模糊查詢方式例如在  Q8儲存格以數字方式輸入帳號   1020
Q9-Q51儲存格範圍顯示含有  1020 之數據全部顯示(若是筆數多較有問題...)
該如何修正公式才能以輸入帳號末 4 碼之原則搜尋帶出相關含有 1020之選項

希望結果是以模糊查詢方式
例如在  Q8儲存格以數字方式(末4碼)或以戶名方式查詢
[attach]35748[/attach]
[attach]35749[/attach]

[attach]35750[/attach]
作者: 准提部林    時間: 2023-1-17 21:33

頭/身/尾 分查
[attach]35753[/attach]
作者: blueskyneter    時間: 2023-1-17 22:47

謝謝提問的..
也謝謝回答的准提部林先進..
學到了..
作者: cypd    時間: 2023-1-19 00:02

回復 2# 准提部林

非常感謝版主 准提部林  @回覆
針對所述問題解答有一疑問??
若是只在原儲存格…Q8  不另設置…S8  的狀態下
是否在原儲存格…Q8能達成輸入簡短文字或數字,以名稱或帳號查詢 ??
作者: Andy2483    時間: 2023-1-19 08:22

本帖最後由 Andy2483 於 2023-1-19 08:30 編輯

回復 4# cypd


    '謝謝前輩發表此主題與範例,謝謝 准提部林前輩指導
'以下是後學建議不變更 准提部林前輩範例原本公式的折衷方案:分拆字串,請前輩試試看

'將下列程式碼放入 "單" 工作表模組裡

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Address = "$Q$8" Then
   '↑如果編輯內容的儲存格位址是 "$Q$8"
      If InStr(.Value, "*") Then
      '↑如果編輯的結果內容裡包含"*"字元
         [S8] = Split(.Value, "*")(1): [Q8] = Split(.Value, "*")(0)
         '↑if條件成立就令[S8]儲存格是 以"*"字元分割[Q8]儲存格後右邊的字串,
         '令[Q8]儲存格是 以"*"字元分割[Q8]儲存格後左邊的字串
         '所以以[Q8]儲存格輸入 "永豐*28" 為例:[S8]="28",[Q8]="永豐",
         '另外以 "永豐*" 為例:[S8] = "" , [Q8]="永豐"

      End If
   End If
End With
End Sub

輸入 "永豐*28" :
[attach]35757[/attach]
結果:
[attach]35758[/attach]

----------------------------------------------------
輸入 "永豐*" :
[attach]35759[/attach]
結果:
[attach]35760[/attach]
作者: 准提部林    時間: 2023-1-19 17:35

回復 4# cypd

試試//
[attach]35761[/attach]
作者: cypd    時間: 2023-1-19 18:53

回復 6# 准提部林

太感謝版主 准提部林  @回覆

實測結果非常完美
簡單由實惠  ^^
作者: cypd    時間: 2023-1-19 18:58

回復 5# Andy2483

感謝  Andy2483  熱心回覆  ^^

將所 vba模組程式碼產生分拆字串
又是另一種分享...獲益良多
作者: Andy2483    時間: 2023-2-18 15:44

本帖最後由 Andy2483 於 2023-2-18 15:46 編輯

回復 6# 准提部林


    謝謝前輩,請前輩再指導

1.先在客戶基本資料表設輔助欄(B欄)
1.1.植入=IF(單!$Q$8="","",IF(ISERR(SEARCH(單!$Q$8,C2&D2&"/")),"",ROW(A2)))    :顯示 空白或列號

1.2.SEARCH(單!$Q$8,C2&D2&"/"): 回傳 字元位置
以 單!$Q$8值字串當要找尋的文字, 用SEARCH()搜尋 C2&D2&"/" 所組成的新字串 回傳 所在的開始位置
單!$Q$8 ="中華*1020/" : 是要找尋的文字,而中間有個 * 萬字元,所以找尋的文字串是: 以 "中華" 開頭, 以 "1020/" 結尾的連續字串的意思
最後的 "/" 是為了確定是字串的結尾字元
如果要找尋的文字不在 C2&D2&"/" 所組成的字串裡! 則會傳回 #VALUE! 錯誤值。

1.3.ISERR(字元位置): 錯誤值判定 回傳1或0
如果 字元位置 回傳  #VALUE! 錯誤值!   錯誤值判定是1
但如果 字元位置是數字! 錯誤值判定是0

1.4.IF(錯誤值判定,"",ROW(A2))) :空白或列號
IF(單!$Q$8="","",空白或列號)
如果 單!$Q$8 是空白!就不必SEARCH(),在儲存格顯示空白
否則就回傳 IF(ISERR(SEARCH()),"",ROW(A2))是空白或列號,顯示在儲存格

2.[Q9]=INDEX(客戶基本資料!C:C,SMALL(客戶基本資料!B:B,ROW(A1)))&"_"&INDEX(客戶基本資料!D:D,SMALL(客戶基本資料!B:B,ROW(A1)))
中華電信股份有限公司_66001020001020

2.1.顯示 回傳戶名&"_" & 回傳帳號

2.2.回傳戶名=INDEX(客戶基本資料!C:C,SMALL(客戶基本資料!B:B,ROW(A1)))        ="中華電信股份有限公司"
ROW(A1)   =1
SMALL(客戶基本資料!B:B,ROW(A1))      =2
回傳 客戶基本資料!B欄第1小的值

INDEX(客戶基本資料!C:C,SMALL(客戶基本資料!B:B,1))
INDEX(客戶基本資料!C:C,2)
回傳 客戶基本資料!C2儲存格的值

2.3.回傳帳號=INDEX(客戶基本資料!D:D,SMALL(客戶基本資料!B:B,ROW(A1)))
ROW(A1)          =1
SMALL(客戶基本資料!B:B,ROW(A1))          =2
回傳 客戶基本資料!B欄第1小的值

INDEX(客戶基本資料!D:D,2)
回傳 客戶基本資料!D2儲存格的值       =  "66001020001020"
作者: cypd    時間: 2023-2-19 01:00

回復 9# Andy2483

非常感謝  Andy2483  熱心回覆  ^^

懂得應用才像學生,臉皮厚點學會更謹慎積極

針對題內公式用心詳細講解每一過程含意...心中感恩
作者: Andy2483    時間: 2023-2-20 14:39

本帖最後由 Andy2483 於 2023-2-20 15:00 編輯

回復 6# 准提部林


    謝謝前輩
學公式很不簡單!懂VBA解法不一定會公式解法,後學駑鈍,學公式希望勤能補拙
以下是後學學習以VBA的方式處理,懇請前輩再指導,謝謝
不用(Application.EnableEvents = False)盡力防堵再觸發,不知什麼原因還是發生再觸發了,哪裡錯了??

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr, V$, N&, A$, i&
With Target
  If InStr(.Address, "$Q$9") Or .Count > 1 Then
      Exit Sub
   End If
   If .Address = "$Q$8" Then
      V = .Value
      Arr = Sheets("客戶基本資料").Range([客戶基本資料!D1], [客戶基本資料!C65536].End(3))
      For i = 2 To UBound(Arr)
         If Arr(i, 1) & Arr(i, 2) & "/" Like "*" & V Then
            A = Split(V, "*")(0)
            A = Format(InStr(Arr(i, 1) & Arr(i, 2) & "/", A), "00|")
            N = N + 1
            Arr(N, 1) = A & Arr(i, 1) & "_" & Arr(i, 2)
         End If
      Next
      Range([Q9], Cells(Rows.Count, "Q").End(3)(2)) = ""
      If N = 0 Then Exit Sub
      With .Item(2, 1).Resize(N, 1)
         .Value = Arr
         If N > 1 Then
            [T:W].EntireColumn.Hidden = False
            .Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
            [T:W].EntireColumn.Hidden = True
         End If
         .Replace "*|", ""
      End With
   End If
End With
End Sub




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)