Tìm giao nhau giữa hàng và cột (1 người xem)
- Thread starter dat21288
- Ngày gửi
-
- Thẻ
- hàm dò tìm
Người dùng đang xem chủ đề này
- Tham gia
- 25/9/09
- Bài viết
- 5,729
- Được thích
- 8,859
- Giới tính
- Nam
- Nghề nghiệp
- Giáo viên
Giả sử chữ d2 nằm ở ô I16, số 12 nằm ở ô J16, bạn dùng tạm công thức này nhé:
=INDEX($E$4:$M$13,SUM(IFERROR(MATCH(I16,$B$4:$B$13,0),0),IFERROR(MATCH(I16,$C$4:$C$13,0),0),IFERROR(MATCH(I16,$D$4:$D$13,0),0)),SUM(IFERROR(MATCH(J16,$E$2:$M$2,0),0),IFERROR(MATCH(J16,$E$3:$M$3,0),0)))
hoangtuaotrang_hp_vn
Thành viên tích cực
- Tham gia
- 17/5/09
- Bài viết
- 1,019
- Được thích
- 899
trường hợp, một trong 2 điều kiện bỏ trống (I16 hoặc J16 trống) thì ko còn đúng nữa bác ah
- Tham gia
- 25/9/09
- Bài viết
- 5,729
- Được thích
- 8,859
- Giới tính
- Nam
- Nghề nghiệp
- Giáo viên
Thế thì có lẽ phải thêm một hàm IFERROR ngoài cùng nữa nhỉtrường hợp, một trong 2 điều kiện bỏ trống (I16 hoặc J16 trống) thì ko còn đúng nữa bác ah
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,975
Bài này mà dùng VBA thì xoẹt cái là xong!
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,642
- Được thích
- 12,515
- Giới tính
- Nam
Góp vui thêm hai cách bằng công thức Mảng:
Mã:
=IFERROR(INDEX(E4:M13,MATCH(1,INDEX(COUNTIF(OFFSET(B3:D3,ROW(1:10),),G16),),),MATCH(1,INDEX(COUNTIF(OFFSET(D2:D3,,ROW(1:9)),H16),),)),"")
Hoặc:
=IFERROR(INDIRECT(TEXT(INDEX(MAX((B4:D13=G16)*ROW(B4:B13)*10^3)+MAX((E2:M3=H16)*COLUMN(E2:M2)),),"R000C000"),),"")Thân
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Cũng muốn "xoẹt cái" nhưng không xong. Phải "xoẹt" nhiều cái hơi bị "đau".
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,975
Em thì làm khác: Dùng Find Method. Em viết 2 hàm:
Mã:
Function FindRow(ByVal FindValue, ByVal FindRange As Range, Optional ByVal MatchCase As Boolean = False)
Dim rngFind As Range
FindRow = CVErr(xlErrNA)
Set rngFind = FindRange.Find(FindValue, , xlValues, xlWhole, , , MatchCase)
If Not rngFind Is Nothing Then FindRow = rngFind.Row
End Function
Function FindCol(ByVal FindValue, ByVal FindRange As Range, Optional ByVal MatchCase As Boolean = False)
Dim rngFind As Range
FindCol = CVErr(xlErrNA)
Set rngFind = FindRange.Find(FindValue, , xlValues, xlWhole, , , MatchCase)
If Not rngFind Is Nothing Then FindCol = rngFind.Column
End Function
Mã:
=INDEX(A1:M13,FindRow(I16,B4:D13),FindCol(J16,E2:M3))
Lần chỉnh sửa cuối:
Bạn tham khảo.
Mình tách hàm từng cột phụ để dễ debug. Bạn có thể gom hàm cho gọn.
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Đọc code của ndu96081631 mới thấy mình phải thêm cái "Option Compare Text".
Bài đã được tự động gộp:
Làm "quái gì được"!
Cột có 3 cột, hàng có 2 hàng. Nếu tìm không có thì ra cái gì?
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,642
- Được thích
- 12,515
- Giới tính
- Nam
Thử:
Mã:
I16=IFERROR(INDIRECT(TEXT(INDEX(MAX((B4:D13=G16)*ROW(B4:B13)*10^3)+MATCH(H16,INDEX(OFFSET($A$1:$M$1,INDEX(MAX((B4:D13=G16)*COLUMN($A:$C)),)-1,),),),),"R000C000"),),"")Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,642
- Được thích
- 12,515
- Giới tính
- Nam
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Lúc tôi viết bài #12 thì không thấy file.
Thanks bác!
Bài đã được tự động gộp:
Hay quá. Cảm ơn bác nhiều!
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,975
Nếu là tôi, trong trường hợp bài toán trở nên quá phức tạp thì tôi sẽ xem lại cách bố trí dữ liệu chứ không cố làm ra kết quả bằng mọi cách
Một CSDL đơn giản sẽ khiến cho mọi yêu cầu tính toán trở nên đơn giản và chính xác. Việc mở rộng dữ liệu cũng không ảnh hướng đến thuật toán tìm kiếm. Bằng ngược lại, dữ liệu bố trí phức tạp thì mọi thay đổi sau này sẽ phá vỡ toàn bộ tính toán trước đó
Tôi nói vậy là vì bắt đầu cảm thấy dữ liệu của bạn nó... dị dị rồi đấy
Dạ đúng là nó hơi dị. Nhưng e đã tóm gọn lại rồi đấy ạ. Hết sức mến phục anh!
Lúc đầu e tính chỉ làm bằng công thức thủ công. nhưng sau khi tham khảo file của bác Ba tê gạo cội e thấy thích cách giải quyết bằng VBA quá, mặc dù chưa học VBA nhưng đọc vô cũng hiểu phần nào. Nếu được bác xử lý công thức còn lại bằng VBA giúp e với. e xin cảm ơn.
be_09
Biên Hòa, Đồng Nai
- Tham gia
- 9/4/11
- Bài viết
- 9,972
- Được thích
- 9,883
- Nghề nghiệp
- Công chức
Góp ý cho bạn:
1/ Bạn nên đưa File có tiêu đề đầy đủ và dữ liệu giả định giống với thực tế.
2/ Theo tôi thì nên có sheet Danh mục và dùng sheet này để tra thì sẽ dễ dàng hơn.
3/ Hoặc đưa ra quy trình và nêu cái cần làm. Có thể các thành viên sẽ có giải pháp khác tốt hơn là chạy theo ý tưởng của bạn.
dazkangel
<New Horizons>
- Tham gia
- 28/2/17
- Bài viết
- 2,882
- Được thích
- 4,048
- Giới tính
- Nam
Vầy có được không nhỉ:
Mã:
=IFERROR(INDEX(E4:M13,AGGREGATE(14,6,ROW(A1:A10)/(B4:D13="d2"),1),AGGREGATE(14,6,COLUMN(A1:I1)/(E2:M3=12),1)),"")
Lần chỉnh sửa cuối:
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Có lẽ theo bạn bài này nữa là hoa mắt rồi.
cảm ơn bác rất nhiều. e nhìn vô thấy hoa mắt thật. không biết có đọc hiểu được code của bác ko nữa
Mộng Thường 2001
Thành viên tiêu biểu
- Tham gia
- 28/11/17
- Bài viết
- 612
- Được thích
- 1,624
Cũng còn dài hơn cái này:
Mã:
=IFERROR(INDEX(E4:M13,MAX(INDEX(("d2"=B4:D13)*ROW(1:10),)),MAX(INDEX((E2:M3=12)*COLUMN($A:$I),))),"")
Chúc em ngày vui.
ps: không biết sao, sau khi trả lời cho anh @yeudoi bên bài: https://www.giaiphapexcel.com/diendan/threads/tìm-tổng-chuỗi-con-trong-chuỗi-lớn.136754/page-3#post-874319, tự dưng @Phan Thế Hiệp "bị ngọng" luôn, vì không còn hiện ba nút phía dưới: "Báo cáo", "Thích" và "Trả lời". Chỉ có thể xem mà không "mần ăn" gì được, đành phải sai "đứa nhỏ" lại ra.
Khà khà khà.
dazkangel
<New Horizons>
- Tham gia
- 28/2/17
- Bài viết
- 2,882
- Được thích
- 4,048
- Giới tính
- Nam
Vâng thế thì chịuCũng còn dài hơn cái này:
Nhưng mà chủ thớt "khoái" VBA rồi em ơi! làm bằng thừa.Mã:=IFERROR(INDEX(E4:M13,MAX(INDEX(("d2"=B4:D13)*ROW(1:10),)),MAX(INDEX((E2:M3=12)*COLUMN($A:$I),))),"")
Chúc em ngày vui.
ps: không biết sao, sau khi trả lời cho anh @yeudoi bên bài: https://www.giaiphapexcel.com/diendan/threads/tìm-tổng-chuỗi-con-trong-chuỗi-lớn.136754/page-3#post-874319, tự dưng @Phan Thế Hiệp "bị ngọng" luôn, vì không còn hiện ba nút phía dưới: "Báo cáo", "Thích" và "Trả lời". Chỉ có thể xem mà không "mần ăn" gì được, đành phải sai "đứa nhỏ" lại ra.
Khà khà khà.
Cũng không hẳn là e không thích công thức thủ công. Nhưng e vẫn thắc mắc điều này:Cũng còn dài hơn cái này:
Nhưng mà chủ thớt "khoái" VBA rồi em ơi! làm bằng thừa.Mã:=IFERROR(INDEX(E4:M13,MAX(INDEX(("d2"=B4:D13)*ROW(1:10),)),MAX(INDEX((E2:M3=12)*COLUMN($A:$I),))),"")
Chúc em ngày vui.
ps: không biết sao, sau khi trả lời cho anh @yeudoi bên bài: https://www.giaiphapexcel.com/diendan/threads/tìm-tổng-chuỗi-con-trong-chuỗi-lớn.136754/page-3#post-874319, tự dưng @Phan Thế Hiệp "bị ngọng" luôn, vì không còn hiện ba nút phía dưới: "Báo cáo", "Thích" và "Trả lời". Chỉ có thể xem mà không "mần ăn" gì được, đành phải sai "đứa nhỏ" lại ra.
Khà khà khà.
Cũng bài excel y như này (tìm đối chiếu tận 5 dòng x 5 cột). cách đây khoảng 6 năm về trước. Với hàm cơ bản như IF, Vlookup, Hlookup, and, or, match, index... nói chung là hàm cơ bản học từ Tin A, trên excel 2003, e lập công thức và ra chính xác kết quả. Tuy công thức hơi dài tí, rơi tầm 3 dòng. Nhưng bây giờ không thể tập trung nghĩ, nhớ và làm lại được. Chắc càng lớn càng ngu đi. Bác nào giải đáp giúp e!
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,642
- Được thích
- 12,515
- Giới tính
- Nam
Tôi thì nhận định ngược với bạn, vì bằng chứng trên diễn đàn GPE này thì "gừng càng già càng cay", đa số những anh em mà thành viên khác gọi là "thầy" toàn trên 58-60 tuổi, thậm chí có bậc trưởng thượng lên hàng >70 tuổi, những vị này có những bài viết vừa rộng vừa thâm sâu để đời cho thành viên khác học tập. Tôi thì bước qua hàng 5 vài ba năm, nên còn phải lượm mót các vị ấy nhiều lắm.
Suy ra rằng, học tập và rèn luyện không có hạn định tuổi tác, chỉ có phân biệt giữa người chịu khó bỏ công nỗ lực tìm tòi hay người chỉ biết 1 là 1 mà không chịu cầu tiến thôi, bạn à!
Thân.
be_09
Biên Hòa, Đồng Nai
- Tham gia
- 9/4/11
- Bài viết
- 9,972
- Được thích
- 9,883
- Nghề nghiệp
- Công chức
Mấy em trẻ trẻ bây giờ lười quá không chịu học nghiên cứu sâu Mới ba mấy, bốn mươi mà gọi là lớn và cho mình càng lớn càng ngu. đã vậy vào diễn đàn hỏi chẳng có đầu, có đuôi đôi khi đưa File mà cũng chẳng có tiêu đề, dữ liệu tgiả định thì rối rắm, không giống với File thực tế đang sử dụng nên gây rất nhiều khó khăn cho người trợ giúp (vì khi đọc có hiểu, có biết mới giúp được), còn đọc mà không hiểu thì chào thua.
Quá trình học của tôi là thế này (nêu lên để các em trẻ phấn đấu):
1/ Năm 2005 (lúc này đã U50), tôi chẳng biết gõ bàn phím là gì. Tất cả mọi thứ liên quan đến máy tính tôi chỉ nêu vấn đề cần làm rồi bắt mấy đứa nhân viên hợp đồng làm tất, tôi chỉ đọc lại và nói mấy em sửa lại theo ý.
Lúc đó, cơ quan kiểm điểm quá nên tôi bắt đầu mò học Word, Excel từ máy cơ quan, có hôm học đến 9 - 10 giờ đêm mới về. Không học cơ quan cũng kiểm điểm, mà học quá mức cũng bị kiểm điểm.
2/ Vì những lẽ trên, năm 2007 tôi mua máy tính tự học tại nhà (nên tôi chẳng có bằng A, B ...gì cả). Đến năm 2011 bắt đầu tham gia diễn đàn, được 1 tháng thì bị khóa Nick, lý do: Mới tham gia nên đăng bài lung tung không đúng Box đôi khi cùng chủ đề tìm bài cũ không thấy (lúc đó chưa biết đọc lại bài cũ) nên mở lại nhiều Topic.
3/ Đến năm 2012, tôi mới vào lại diễn đàn và tham gia liên tục cho đến nay.
4/ Từ khi tham gia diễn đàn tôi càng tiến bộ là do đọc nhiều bài viết, xem cách các thành viên giải đáp, đồng thời tải File về ngâm cứu, và tìm lời giải riêng cho mình.
Tóm lại:
Mấy em 30 - 40 thì chưa gọi là già, là lớn đâu.
Có chịu khó thì từ chỗ chưa biết sẽ khá lên và từ từ sẽ giỏi, có cố gắng thì ắt sẽ thành công.
Lần chỉnh sửa cuối:
Xin lỗi các bác vì e viết gây hiểu nhầm. Ý e chỉ nói bản thân e. Ngày trước e làm được (làm theo ct của e) sau bao lâu ko đụng đến giờ không làm được nên e cảm thấy khó chịu với chính bản thân.
Công thức các bác ở đây đưa ra có nhiều hàm e chưa hiểu. Nhưng đọc thì e thấy các bác xử lý rất gọn, logic. Chứng tỏ hiểu sâu hơn e rất nhiều. E cũng rất hướng tiến bộ nên cũng đang nghiên cứu lại công thức của các bác chỉ giáo ở trên. E cũng nghiên cứu cả VBA mặc dù chưa học bao giờ.
E rất cảm ơn mọi người rất nhiệt tình giúp đỡ. E cũng muốn đưa luôn file cho các bác giải 1 lần cho nhanh. Nhưng do đặc thù công việc, không thể lộ file được. File gốc phức tạp hơn khoảng 1,5 - 2 lần. E xem rút gọn up lên và về lại sửa lại để áp dụng.
Có thể e bị ảnh hưởng bởi công việc, áp lực hoặc nguyên nhân khách quan nên ngày càng "ngu đi". Chắc e bị stress
Kết luận: e viết comment trên vì xem có ai dùng công thức thủ công cơ bản khác mà làm ra đáp án, có thể công thức dài hơn, sử dụng các hàm e nêu trên, e chỉ nhớ lại được vậy... Giúp e gợi nhớ lại công thức ngày trước. Ngoài các công thức, VBA các bác giúp đỡ đã ra kết quả chính xác
E xin chân thành cảm ơn!
Bài viết mới nhất
- Question
- Question
- Question
