Công thức dò tìm tham chiếu trong chuỗi ký tự cho trước (1 người xem)

Liên hệ QC

Người dùng đang xem chủ đề này

nguyennam1994vnn

Thành viên mới
Tham gia
29/8/18
Bài viết
8
Được thích
4
Chào các bác,
Mình có bài tập như sau: Bảng 1 và Bảng 2. Mình muốn dò ô A3, có chứa một trong điều kiện của D2:D14 thì kết quả trả về tương ứng với E2:E14 ở ô B3.
Mình không thể dùng Vlookup bởi vì nội dung dò khác với điều kiện nên ko ra kết quả, mà không biết sử dụng cách nào. Nhờ các bác hướng dẫn mình.
Chân thành cám ơn các bác.
Giai phap excel.JPG
 

File đính kèm

Bạn nên sửa tiêu đề cụ thể hơn chút. Ví dụ là: "Công thức dò tìm tham chiếu trong chuỗi ký tự cho trước"
B3=LOOKUP(2;1/SEARCH($D$3:$D$14;A3);$E$3:$E$14)
Copy paste các ô còn lại bạn nhé
 
Cám ơn bác rất nhiều.
Mình thấy nhiều công thức có 1/....
Mình không hiểu của bác 1/Search(.) có nghĩa là gì.
Nhờ bác giải thích công thức cụ thể dùm mình và mọi người được hiểu rõ hơn với.
Cám ơn bác!
 
Cám ơn bác rất nhiều.
Mình thấy nhiều công thức có 1/....
Mình không hiểu của bác 1/Search(.) có nghĩa là gì.
Nhờ bác giải thích công thức cụ thể dùm mình và mọi người được hiểu rõ hơn với.
Cám ơn bác!
Giả sử A3 có giá trị = 4
Giả sử vùng từ A16:A19 có chi tiết như sau: A16=3;A17=5;A18=4;A19=10
Giả sử vùng từ B16:B19 có chi tiết như sau: B16=A;B17=B;B18=C;B19=D
Ta làm công thức:
=LOOKUP(2,1/SEARCH($A$16:$A$19,A3),$B$16:$B$19) kết quả là C

Giải thích công thức:
SEARCH($A$16:$A$19,A3) tạo ra một mảng 4 thành phần gồm {phần tử 1, phần tử 2, phần tử 3, phần tử 4}
cụ thể như sau: trong 4 phần tử (A16;A17;A18;A19) mà hàm search dò so với A3 thì có A18 = 4 có trong chuỗi của A3 = 4 (và A18 nằm ở vi trí thứ 3 trong mảng A16;A17;A18;A19) ngược lại các phần tử A16;A17;A19 vì ko khớp chuỗi nào với A3 nên kết quả là #value. ‘#value ở đây hiểu là lỗi tương tự như lỗi #N/A’ Như vậy SEARCH($A$16:$A$19,A3) sẽ tạo ra mảng như sau:=> {#value;#value;3;#value}

Tiếp đó 1/SEARCH($A$16:$A$19,A3) tương đương với {1/#value;1/#value;1/3;1/#value} sẽ tạo ra mảng===> {#value;#value;0.333;#value}

Cuối cùng là tới hàm: LOOKUP(2,………..,………….) sẽ trả về vị trị của giá trị lớn nhất gần với 2 và bỏ qua các lỗi
tức là đi tìm vị trí của giá trị 2 trong mảng {#value;#value;0,3333;#value} . Ở đây là vị trí số 3 trong mảng

===>Từ số 3 này Lookup tiếp tục trả về dòng thứ 3 trong vùng kết quả $B$16:$B$19 ===> kết quả là B18 và = C
Ngoài cách giải thích của mình, bạn có thể tìm kiếm thêm trên GPE để biết thêm thông tin, thêm kiến thức. Chúc bạn học hỏi được nhiều kiến thức, nhiều bài học từ GPE
 

File đính kèm

Lần chỉnh sửa cuối:
Cám ơn sự hướng dẫn nhiệt tình của bác. Thực sự cũng hơi khó hiểu, nhưng mình cũng cố gắng tìm hiểu. Mong được sự giúp đỡ hướng dẫn của bác trong tương lai. Cám ơn bác!!
 
Bác cho mình hỏi, hàm Search(.) ở bên trên tìm kiếm trong mãng, điều kiện cần tìm là cell A3, nó có điểm khác với hàm Search thông thường là tìm một đoạn chuỗi trong một text. Tại sao như vậy ạ??
 
Ý bạn có phải là muốn hỏi hàm search có cú pháp thông thường là SEARCH(find_text,within_text,[start_num]); nhưng ở trong công thức mảng nó được đổi thành SEARCH($A$16:$A$19,A3).Sự khác nhau này, thực ra mình cũng chưa có đọc được ở đâu giải thích chi tiết cả. Nhưng mình có thể ngầm hiểu với nhau sự khác nhau ở đây là SEARCH(find_text,within_text,[start_num]) đơn thuần là tìm ký tự trong chuỗi ký tự, còn SEARCH($A$16:$A$19,A3) là tạo ra mảng trong công thức mảng. Vậy nhé!! Nếu bạn tìm được ở đâu cách giải thích cụ thể hơn về sự khác nhau này thì gửi Link để mình biết thêm chi tiết nhé ;)
 
Lần chỉnh sửa cuối:
Ý bạn có phải là muốn hỏi hàm search có cú pháp thông thường là SEARCH(find_text,within_text,[start_num]); nhưng ở trong công thức mảng nó được đổi thành SEARCH($A$16:$A$19,A3).Sự khác nhau này, thực ra mình cũng chưa có đọc được ở đâu giải thích chi tiết cả. Nhưng mình có thể ngầm hiểu với nhau sự khác nhau ở đây là SEARCH(find_text,within_text,[start_num]) đơn thuần là tìm ký tự trong chuỗi ký tự, còn SEARCH($A$16:$A$19,A3) là tạo ra mảng trong công thức mảng. Vậy nhé!! Nếu bạn tìm được ở đâu cách giải thích cụ thể hơn về sự khác nhau này thì gửi Link để mình biết thêm chi tiết nhé ;)
Không có sự khác nhau khi dùng SEARCH('Mảng find_text',within_text,[start_num]) và công thức thường. Nó vẫn thực thi đúng theo cấu trúc lệnh cho phép, nhưng thay vì là tìm từng lần từng 'chuỗi tìm', thì nó bê 1 lúc 'Mảng chuỗi tìm' vào công thức.

Để tìm hiểu ý nghĩa của việc này ta thực hiện: nhớ rằng trước khi gõ =SEARCH($A$16:$A$19,A3), phải chọn số dòng tương ứng ứng với phần tử Mảng trả về, Vd: chọn trước C3: C6, rồi dán C3=SEARCH($A$16:$A$19,A3) xong nhấn Ctrl+Shift+Enter, tức ý 1 ô (cell) không bao giờ chứa được 1 Mảng (là 1 tập hợp các phần tử) vào cùng 1 ô, mà nó chỉ chứa từng phần tử tương ứng của Mảng, phần tử 1 lưu vào C3, phần tử 2 lưu vào C4, phần tử 3 lưu vào C5, phần tử 4 lưu vào C6. Nếu là công thức thường thì tại C3 ta gõ: =SEARCH($A16,$A$3) xong fill xuống.

Chính nhờ sự vận dụng Mảng thay cho từng ô đơn, mà ta tạo được kết quả cũng là 1 Mảng nhằm cung cấp dữ liệu tiếp tục cho hàm Lookup(2, 'Mảng kết quả từ Search()'....) thực hiện theo yêu cầu của mình.

Thân
 

File đính kèm

Cám ơn bác, mặc dù khó hiểu thật nhưng chấp nhận nó là vậy đi!!
Bạn còn có người giải thích để tìm hiểu công thức, lúc tôi học phải tự mình tìm ra, bạn đã sướng hơn nhiều rồi! :)

Cứ hình dung: thay vì mở từng "hộc bàn" xem có hay không món đồ bạn cần tìm, từ hộc bàn thứ 1, xong đóng nó lại, rồi mở tiếp "hộc bàn thứ 2",...đến "hộc bàn" cuối; bạn chỉ cần mở toang các hộc bàn và nhìn vào 1 lúc, xong chỉ để các hộc bàn nào có chứa món đồ bạn cần tìm thôi. Đơn giản Mảng là vậy.

Thân
 
Giả sử A3 có giá trị = 4
Giả sử vùng từ A16:A19 có chi tiết như sau: A16=3;A17=5;A18=4;A19=10
Giả sử vùng từ B16:B19 có chi tiết như sau: B16=A;B17=B;B18=C;B19=D
Ta làm công thức:
=LOOKUP(2,1/SEARCH($A$16:$A$19,A3),$B$16:$B$19) kết quả là C

Giải thích công thức:
SEARCH($A$16:$A$19,A3) tạo ra một mảng 4 thành phần gồm {phần tử 1, phần tử 2, phần tử 3, phần tử 4}
cụ thể như sau: trong 4 phần tử (A16;A17;A18;A19) mà hàm search dò so với A3 thì có A18 = 4 có trong chuỗi của A3 = 4 (và A18 nằm ở vi trí thứ 3 trong mảng A16;A17;A18;A19) ngược lại các phần tử A16;A17;A19 vì ko khớp chuỗi nào với A3 nên kết quả là #value. ‘#value ở đây hiểu là lỗi tương tự như lỗi #N/A’ Như vậy SEARCH($A$16:$A$19,A3) sẽ tạo ra mảng như sau:=> {#value;#value;3;#value}

Tiếp đó 1/SEARCH($A$16:$A$19,A3) tương đương với {1/#value;1/#value;1/3;1/#value} sẽ tạo ra mảng===> {#value;#value;0.333;#value}

Cuối cùng là tới hàm: LOOKUP(2,………..,………….) sẽ trả về vị trị của giá trị lớn nhất gần với 2 và bỏ qua các lỗi
tức là đi tìm vị trí của giá trị 2 trong mảng {#value;#value;0,3333;#value} . Ở đây là vị trí số 3 trong mảng

===>Từ số 3 này Lookup tiếp tục trả về dòng thứ 3 trong vùng kết quả $B$16:$B$19 ===> kết quả là B18 và = C
Ngoài cách giải thích của mình, bạn có thể tìm kiếm thêm trên GPE để biết thêm thông tin, thêm kiến thức. Chúc bạn học hỏi được nhiều kiến thức, nhiều bài học từ GPE
Công thức của anh



Nguyễn Hồng Quang
hay quá,
Anh ơi cho em hỏi là nếu có 2 ký tự giống nhau thì có cách nào dùng công thức trên để lấy tất cả không hở anh?
 
Yêu cầu của bạn gọi là NỐI CHUỖI THEO ĐIỀU KIỆN, có cả đống trên diễn đàn rồi còn gì.
(Đương nhiên là phải dùng VBA nêu dữ liệu nhiều và phức tạp)
Hi hi..... bác ui bác...... bác viết 1 bài kỷ niệm cho cháu với bác nhé
Cháu cảm ơn bác
 

File đính kèm

File đính kèm

File đính kèm

Bác ui, bác hướng dẫn cho cháu hàm Joinif của bác để làm trường hợp này với bác nhé
Cháu tìm mãi hướng dẫn sử dụng hàm này của bác mà không thấy bác à
Bạn đọc trong công thức thì sẽ thấy mà,
PHP:
Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
PHP:
Function JoinIf(ByVal Delimiter As String, ByVal CriteriaArray, ByVal Criteria, Optional ByVal TargetArray) As String
cú pháp: hàm JoinText(dấu phân cách, đối số động(mảng động))
joinIF(dấu phân cách,vùng điều kiện, điều kiện, vùng kết quả)
 
Lần chỉnh sửa cuối:
Bác ui, bác hướng dẫn cho cháu hàm Joinif của bác để làm trường hợp này với bác nhé
Cháu tìm mãi hướng dẫn sử dụng hàm này của bác mà không thấy bác à
Xem file và hổng hiểu gì ráo
File Excel mà bạn làm gì trong đó vậy? Sao giống vẽ bùa quá
 
Xem file và hổng hiểu gì ráo
File Excel mà bạn làm gì trong đó vậy? Sao giống vẽ bùa quá
Bác ơi, cháu tô màu vào đó để bác rễ nhìn thấy thôi mà
Cháu muốn bác dùng hàm Joinif giống như hàm
=LOOKUP(2;1/(G17:G20=$F$16)/(G22:G25=$F$21)/(G27:G30=$F$26);G32:G35)
Nhưng nhược điểm của hàm Lookup() thì chỉ tìm được trên 1 cột, còn hàm của bác thì tìm được mảng rộng hơn
 
Bác ơi, cháu tô màu vào đó để bác rễ nhìn thấy thôi mà
Cháu muốn bác dùng hàm Joinif giống như hàm
=LOOKUP(2;1/(G17:G20=$F$16)/(G22:G25=$F$21)/(G27:G30=$F$26);G32:G35)
Nhưng nhược điểm của hàm Lookup() thì chỉ tìm được trên 1 cột, còn hàm của bác thì tìm được mảng rộng hơn
Thì chắc là vầy:
Mã:
=JoinIf("; ",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),">0",G32:H35)
Bấm Ctrl + Shift + Enter nhé
 
Bác ơi, cháu tô màu vào đó để bác rễ nhìn thấy thôi mà
Cháu muốn bác dùng hàm Joinif giống như hàm
=LOOKUP(2;1/(G17:G20=$F$16)/(G22:G25=$F$21)/(G27:G30=$F$26);G32:G35)
Nhưng nhược điểm của hàm Lookup() thì chỉ tìm được trên 1 cột, còn hàm của bác thì tìm được mảng rộng hơn
Cháu sử dụng công thức mảng sau:

=JoinText(",",IF(G17:H20=F16,IF(G22:H25=F21,IF(G27:H30=F26,G32:H35,1/0),1/0),1/0))

Cháu nhớ kết thúc bằng Ctrl+Shift+Enter.
 
Thì chắc là vầy:
Mã:
=JoinIf("; ",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),">0",G32:H35)
Bấm Ctrl + Shift + Enter nhé
Cháu cảm ơn bác
Bác ơi sao cháu đưa vào trong code như này không được hở bác?
Sub vd()
Range("L46") = "=JoinIf("",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),">0",G32:H35)"
End Sub
Bài đã được tự động gộp:

Cháu sử dụng công thức mảng sau:

=JoinText(",",IF(G17:H20=F16,IF(G22:H25=F21,IF(G27:H30=F26,G32:H35,1/0),1/0),1/0))

Cháu nhớ kết thúc bằng Ctrl+Shift+Enter.
Cháu cảm ơn bác
 
Cháu cảm ơn bác
Bác ơi sao cháu đưa vào trong code như này không được hở bác?
Sub vd()
Range("L46") = "=JoinIf("",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),">0",G32:H35)"
End Sub
Bài đã được tự động gộp:


Cháu cảm ơn bác
Vầy mới đúng:
Mã:
Sub vd()
  Range("L46").FormulaArray = "=JoinIf("""",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),"">0"",G32:H35)"
End Sub
Bị nó là công thức mảng
 
Vầy mới đúng:
Mã:
Sub vd()
  Range("L46").FormulaArray = "=JoinIf("""",(G17:H20=$F$16)/(G22:H25=$F$21)/(G27:H30=$F$26),"">0"",G32:H35)"
End Sub
Bị nó là công thức mảng
Bác ơi cháu lại làm phiền bác tý tẹo nữa bác nhé
Nếu như ở mảng lấy kết quả nó có các số hoặc ký tự giống nhau thì

- Hàm không liệt kê ra tất cả mà nó chỉ lấy ký tự đại diện thôi bác à (Tức là bỏ các ký tự trùng đi)

Cháu muốn nó liệt kê ra tất cả liệu có được không hở bác?

Cháu cảm ơn bác
 

File đính kèm

Bác ơi cháu lại làm phiền bác tý tẹo nữa bác nhé
Nếu như ở mảng lấy kết quả nó có các số hoặc ký tự giống nhau thì

- Hàm không liệt kê ra tất cả mà nó chỉ lấy ký tự đại diện thôi bác à (Tức là bỏ các ký tự trùng đi)

Cháu muốn nó liệt kê ra tất cả liệu có được không hở bác?

Cháu cảm ơn bác
Giải pháp thì đã có người làm rồi. Ở đây xin lưu ý:
- Nếu muốn nối chuỗi và có loại bỏ trùng thì dùng JoinIF
- Nếu muốn nối chuỗi và lấy toàn bộ thì dùng JoinText
 
Giải pháp thì đã có người làm rồi. Ở đây xin lưu ý:
- Nếu muốn nối chuỗi và có loại bỏ trùng thì dùng JoinIF
- Nếu muốn nối chuỗi và lấy toàn bộ thì dùng JoinText
Bác ơi hàm JoinIF cũng liệt kê được hết theo cách của anh
phuocam
Bác à.
Công thức của bác lợi hại thật bác à.
Nhưng cháu chỉ sợ là công thức mảng thì tốc độ sẽ bị chậm thôi bác à.
 
Bác ơi hàm JoinIF cũng liệt kê được hết theo cách của anh
phuocam
Bác à.
Bạn nhìn kỹ đi, đó là hàm JOINTEXT chứ hổng phải JOINIF
Tôi viết 2 hàm này có mục đích sử dụng khác nhau đấy
-------------------------
Công thức của bác lợi hại thật bác à.
Nhưng cháu chỉ sợ là công thức mảng thì tốc độ sẽ bị chậm thôi bác à.
Mảng đương nhiên là chậm nhưng chúng ta dùng Excel để tính toán là chính đúng không? Vậy không lý nào dùng Excel để nối chuỗi đến vài chục ngàn dòng rồi lại than chậm với nhanh
 
Bạn nhìn kỹ đi, đó là hàm JOINTEXT chứ hổng phải JOINIF
Tôi viết 2 hàm này có mục đích sử dụng khác nhau đấy
-------------------------

Mảng đương nhiên là chậm nhưng chúng ta dùng Excel để tính toán là chính đúng không? Vậy không lý nào dùng Excel để nối chuỗi đến vài chục ngàn dòng rồi lại than chậm với nhanh
Bác
ndu96081631
ui, cho cháu hởi chút xíu
Tại sao hàm JoinText lại cứ báo lỗi như này hở bác?
 

File đính kèm

Bác
ndu96081631
ui, cho cháu hởi chút xíu
Tại sao hàm JoinText lại cứ báo lỗi như này hở bác?
Câu lệnh quá dài, phải cắt bớt mới được:
Mã:
Range("Z190").FormulaArray = "JoinText_DNU("""",IF(('1'!$AA$105:$DRB$112=AA178)*" & _
                                "('1'!$AA$114:$DRB$121=AA179)*('1'!$AA$123:$DRB$130=AA180)*" & _
                                "('1'!$AA$132:$DRB$139=AA181)*('1'!$AA$141:$DRB$148=AA182)*" & _
                                "('1'!$AA$150:$DRB$157=AA183),'1'!$AA$159:$DRB$166,1/0))"
Ah. Sao bạn sửa tên hàm của tôi vậy? Tôi không thích đâu nha. Nói chung của tôi sao cứ để y vậy giùm. Nếu không thích thì đừng xài
Hy vọng bạn để y tên hàm JoinText cho tôi. Cảm ơn!
 
Câu lệnh quá dài, phải cắt bớt mới được:
Mã:
Range("Z190").FormulaArray = "JoinText_DNU("""",IF(('1'!$AA$105:$DRB$112=AA178)*" & _
                                "('1'!$AA$114:$DRB$121=AA179)*('1'!$AA$123:$DRB$130=AA180)*" & _
                                "('1'!$AA$132:$DRB$139=AA181)*('1'!$AA$141:$DRB$148=AA182)*" & _
                                "('1'!$AA$150:$DRB$157=AA183),'1'!$AA$159:$DRB$166,1/0))"
Ah. Sao bạn sửa tên hàm của tôi vậy? Tôi không thích đâu nha. Nói chung của tôi sao cứ để y vậy giùm. Nếu không thích thì đừng xài
Hy vọng bạn để y tên hàm JoinText cho tôi. Cảm ơn!
Hi hi..... Cháu cảm ơn bác
Lúc trước cháu cũng đang định nghĩ như thế
Nhưng cháu chưa làm
Cháu đang thử nghiệm hàm của bác chạy tốc độ có nhanh không?
 
Câu lệnh quá dài, phải cắt bớt mới được:
Mã:
Range("Z190").FormulaArray = "JoinText_DNU("""",IF(('1'!$AA$105:$DRB$112=AA178)*" & _
                                "('1'!$AA$114:$DRB$121=AA179)*('1'!$AA$123:$DRB$130=AA180)*" & _
                                "('1'!$AA$132:$DRB$139=AA181)*('1'!$AA$141:$DRB$148=AA182)*" & _
                                "('1'!$AA$150:$DRB$157=AA183),'1'!$AA$159:$DRB$166,1/0))"
Ah. Sao bạn sửa tên hàm của tôi vậy? Tôi không thích đâu nha. Nói chung của tôi sao cứ để y vậy giùm. Nếu không thích thì đừng xài
Hy vọng bạn để y tên hàm JoinText cho tôi. Cảm ơn!
Bác
ndu96081631
ơi,
Nó không ra kết quả đâu bác à
Hàm bị lỗi rồi bác à.
 
Câu lệnh quá dài, phải cắt bớt mới được:
Mã:
Range("Z190").FormulaArray = "JoinText_DNU("""",IF(('1'!$AA$105:$DRB$112=AA178)*" & _
                                "('1'!$AA$114:$DRB$121=AA179)*('1'!$AA$123:$DRB$130=AA180)*" & _
                                "('1'!$AA$132:$DRB$139=AA181)*('1'!$AA$141:$DRB$148=AA182)*" & _
                                "('1'!$AA$150:$DRB$157=AA183),'1'!$AA$159:$DRB$166,1/0))"
Ah. Sao bạn sửa tên hàm của tôi vậy? Tôi không thích đâu nha. Nói chung của tôi sao cứ để y vậy giùm. Nếu không thích thì đừng xài
Hy vọng bạn để y tên hàm JoinText cho tôi. Cảm ơn!
Đấy là cháu viết tên bác là "NDU" Nhưng cháu viết thành "DNU' đấy bác à.
Bác ơi bác xem lại cho cháu với
Nó vẫn chưa ra kết quả bác à
 
Bạn nên sửa tiêu đề cụ thể hơn chút. Ví dụ là: "Công thức dò tìm tham chiếu trong chuỗi ký tự cho trước"
B3=LOOKUP(2;1/SEARCH($D$3:$D$14;A3);$E$3:$E$14)
Copy paste các ô còn lại bạn nhé
1629079628816.png1629079782876.png
Sao mình copy công thức vào và enter thì nó báo lỗi vầy, nhấn OK thì nó báo hightlight ở số 2 trên thanh công thức. Mình đang dùng excel 2019.
 
thay dấu chấm phẩy bằng dấu phẩy thử, có thể máy bạn để công thức ngăn cách bằng dấu phẩy.
 
Bạn nên sửa tiêu đề cụ thể hơn chút. Ví dụ là: "Công thức dò tìm tham chiếu trong chuỗi ký tự cho trước"
B3=LOOKUP(2;1/SEARCH($D$3:$D$14;A3);$E$3:$E$14)
Copy paste các ô còn lại bạn nhé
Còn trường hợp dò tìm ngược lại:
Mình muốn dùng công thức để tìm customer (bảng bên phải) tham chiếu từ bảng bên trái thì dùng công thức gì ạ?
1664120694511.png
Mong được giải đáp ạ
 

Bài viết mới nhất

Back
Top Bottom