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

8 Responses

1. hands says:

   06/05/2023 at 1:13 am

   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!

   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

   Reply
2. hands says:

   06/05/2023 at 1:13 am

   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.
   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 ạ??

   Cám ơn bác!!

   Ý 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,); 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,) đơ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é 😉

   Reply
3. hands says:

   06/05/2023 at 1:14 am

   Không có sự khác nhau khi dùng SEARCH('Mảng find_text',within_text,) 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

   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

   Reply
4. hands says:

   06/05/2023 at 1:15 am

   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?

   lấy tất cả? Vậy kết quả cụ thể là = bao nhiêu?

   Bác ơi cháu muốn lấy cả 2 như file cháu đính kèm đây bác à

   LÀM CHƠI!

   Reply
5. hands says:

   06/05/2023 at 1:17 am

   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à,

   Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String

   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ả)

   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

   Thì chắc là vầy:

   =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

   Vầy mới đúng:

   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

   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

   Reply
6. hands says:

   06/05/2023 at 1:17 am

   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.

   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

   =JoinText("; ",IF((G17:H20=$F$16)*(G22:H25=$F$21)*(G27:H30=$F$26),G32:H35,1/0))

   Reply
7. hands says:

   06/05/2023 at 1:21 am

   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á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:

   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?

   Reply
8. hands says:

   06/05/2023 at 1:22 am

   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ì ạ?
   5909
   Mong được giải đáp ạ

   Bạn thử công thức này nha
   =LOOKUP(2,1/SEARCH(E2,$B$2:$B$3),$A$2:$A$3)

   Reply