Trợ giúp code vba tìm kiếm thay thế hàm Index Match

tuhumg00 · 11/10/19

Chào mọi người mình có bài toán muốn nhờ các bạn trợ giúp code vba :
+Mình muốn điền vào bảng xếp hạng vào ô( màu vàng) , dữ liệu được lấy từ 2 điều kiện cột A,B và bảng xếp hạng được lấy từ cột C
(giá trị được lấy như cột I) (như hình vẽ)
(Mình có sử dụng hàm Index và Match (excel), sumproduct nhưng khi thay một trong các giá trị ở bảng xếp hạng ở (cột C) vào nếu là giá trị 6 thì tìm thấy giá trị còn nếu để 6a thì hàm Index, Match không tìm thấy giá trị.(mình chưa tìm hiểu được nguyên nhân và xử lý).
-----Mong muốn trợ giúp có code VBA điền bảng xếp hạng vào ô màu vàng. mong các bạn giúp đỡ mình với nhé. Thanks cả nhà nhiều.
file đính kèm mình để ở dưới
http://www.mediafire.com/file/flezfoyak6bm6cy/Dien_vao_bang_xep_hang.xlsm/file

phulien1902 · 11/10/19

tuhumg00 đã viết:
Chào mọi người mình có bài toán muốn nhờ các bạn trợ giúp code vba :
+Mình muốn điền vào bảng xếp hạng vào ô( màu vàng) , dữ liệu được lấy từ 2 điều kiện cột A,B và bảng xếp hạng được lấy từ cột C
(giá trị được lấy như cột I) (như hình vẽ)
(Mình có sử dụng hàm Index và Match (excel), sumproduct nhưng khi thay một trong các giá trị ở bảng xếp hạng ở (cột C) vào nếu là giá trị 6 thì tìm thấy giá trị còn nếu để 6a thì hàm Index, Match không tìm thấy giá trị.(mình chưa tìm hiểu được nguyên nhân và xử lý).
-----Mong muốn trợ giúp có code VBA điền bảng xếp hạng vào ô màu vàng. mong các bạn giúp đỡ mình với nhé. Cảm ơn cả nhà nhiều.
file đính kèm mình để ở dưới
http://www.mediafire.com/file/flezfoyak6bm6cy/Dien_vao_bang_xep_hang.xlsm/file

Bạn thử 1 cách

PHP:

Private Sub Worksheet_Change(ByVal target As Range)    
    Dim Rng As Range
    Application.EnableEvents = False
    If Not Intersect(target, [F3:F100]) Is Nothing Then
        Set Rng = Sheet1.[A:A].Find(target, LookAt:=xlWhole)
        If target <> "" Then
            If Rng Is Nothing Then
                target.ClearContents: MsgBox "Khong co ten trong cot A!"
            Else
                target.Offset(, 1) = Rng.Offset(, 1): target.Offset(, 2) = Rng.Offset(, 2)
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

tuhumg00 · 12/10/19

Cảm ơn bạn đã quan tâm bài viết của mình. Nhưng khi mình tìm kiếm nếu trường hợp TH1
+Tran Than son1 khi ở lớp 6A1 thì xếp hạng 3 nhưng khi đánh Trần thanh son1(dòng 2) lớp 7A2 thì vẫn xếp hạng 3(thì nó lại không đúng lắm) mình muốn xếp hạng 4 (học sinh Tran than son len lop 7A2 thì đc xếp hạng 4)(có thể bài trước mình không dẫn chứng trường hợp đó sr bạn).

+ Và Ý của mình là muốn là muốn có marcro tìm kiếm bảng xếp hạng trong bảng tổng hợp và dữ liệu điều kiện ở cột F và cột G điền vào mà bảng xếp hang (ô màu vàng) (có thể thêm tên học sinh và lớp và không thay đổi theo worksheet _change như bạn đã viết, mình muốn như một hàm tìm kiếm với hai điều kiện Tên và Lớp vậy) như trường hợp dưới được viết ở range(A12:H19). Bạn có thể sửa lại và viết lại code được không ạ. Cảm ơn bạn.

CHAOQUAY · 13/10/19

tuhumg00 đã viết:
Cảm ơn bạn đã quan tâm bài viết của mình. Nhưng khi mình tìm kiếm nếu trường hợp TH1
+Tran Than son1 khi ở lớp 6A1 thì xếp hạng 3 nhưng khi đánh Trần thanh son1(dòng 2) lớp 7A2 thì vẫn xếp hạng 3(thì nó lại không đúng lắm) mình muốn xếp hạng 4 (học sinh Tran than son len lop 7A2 thì đc xếp hạng 4)(có thể bài trước mình không dẫn chứng trường hợp đó sr bạn).

+ Và Ý của mình là muốn là muốn có marcro tìm kiếm bảng xếp hạng trong bảng tổng hợp và dữ liệu điều kiện ở cột F và cột G điền vào mà bảng xếp hang (ô màu vàng) (có thể thêm tên học sinh và lớp và không thay đổi theo worksheet _change như bạn đã viết, mình muốn như một hàm tìm kiếm với hai điều kiện Tên và Lớp vậy) như trường hợp dưới được viết ở range(A12:H19). Bạn có thể sửa lại và viết lại code được không ạ. Cảm ơn bạn.

Có trường hợp cùng tên, cùng lớp, khác thứ hạng không bạn?

tuhumg00 · 13/10/19

CHAOQUAY đã viết:
Có trường hợp cùng tên, cùng lớp, khác thứ hạng không bạn?

Trường hợp của Mình áp dụng là : Cùng tên -khác lớp- cùng thứ hạng hoặc khác thứ hạng. ( có thể danh sách đó thêm học sinh "Tran than son1" - lớp 8A1 hoặc 9B1 - thứ hạng 9c hoặc 6a. Bảng TH của mình thì cố định, mình chỉ muốn tìm kiếm như hàm vlookup, index, match. Cảm ơn @CHAOQUAY đã quan tâm.Giúp mình với nhé.

SA_DQ · 13/10/19

Trước tiên ta nên bàn đến thiết kế CSDL của bạn, vì nó đang có vấn đề:

(1): Với CSDL là danh sách HS toàn trường, ta phải xây dựng bộ mã hay số báo danh của HS trong trường; Không bao giờ tổ chức CSDL không có mã duy nhất cho mỗi dòng dữ liệu.

Sẵn đây mình xin gợi ý bộ mã hay xài của mình

Mã	Họ và tên	Mã BP	Chức vụ	GPE.COM
FVC00	Đỗ Văn Công	BX	Xe
FVH00	Đặng Văn Hiếu	BX	CN
BHK00	Bùi Hồng Khải	DG	CN
FBH00	Đào Bá Hưng	DG	CN
LQP00	Lê Quang Phúc	DG	CN
MTT00	Mai Thị Thoa	DG	CN
NBT00	Nguyễn Thị Bích Thuận	DG	TT
NNL00	Nguyễn Ngọc Lâm	KT	NV
NTA00	Nguyễn Lương Trường An	KT	KCS
BVK00	Bùi Văn Khang	Ox	TX
FTV01	Đặng Tuấn Việt	Ox	CN
FTH00	Đào Trọng Hùng	Kh	NV
HHT00	Hồ Hữu Tâm	Kh	TP
LFT00	Lê Đức Thịnh	Kh	KT
PFT00	Phạm Đức Tuân	Kh	KT
PJG00	Phạm Giang	Kh	KT
VHD00	Vũ Hữu Huy	Kh	KT
NHN00	Nguyễn Hùng Nam	BX	KCS
NMH02	Nguyên Mạnh Hòa	GD	TL
NVH01	Nguyễn Việt Hồng	DG	KS

(2) Trong 1 CSDL, 1 trường ta không nên có 2 kiểu dữ liệu, như

Xếp hạng
3
4
5
6a
7
8b
9

Nếu là mình thì sẽ là 1 trong 2 trường hợp sau:

Xếp hạng

Xếp hạng (A)

Xếp hạng (B)

3

===>

30

Hay:

3A

4

===>

40

Hay:

4A

5

===>

50

Hay:

5A

6a

===>

61

Hay:

6B

7

===>

72

Hay:

7D

8b

===>

82

Hay:

8C

9

===>

90

Hay:

9A

Mình muốn nhắn với bạn là CSDL tốt rồi ta mới tính chuyện khác, như lên cung trăng hoặc sao hỏa; Còn bằng ngược lại sẽ có lúc ta xuống âm phủ không chừng!

Chúc bạn ngày cuối tuần vui vẻ & thành công!

VetMini · 13/10/19

SA_DQ đã viết:
Trước tiên ta nên bàn đến thiết kế CSDL của bạn, vì nó đang có vấn đề:
...

Thiết kế lại mất công lắm. Nhờ viết code khoẻ hơn.
Nếu bạn thấy bắt buộc phải thiết kế lại thì bạn viết giùm code thiết kế lại luôn đi.

SA_DQ · 13/10/19

Nếu bạn thấy bắt buộc phải thiết kế lại thì bạn viết giùm code thiết kế lại luôn đi.

Nếu có file thiệt thì mới cố bõ công, còn file giả lập đưa ở bài trên thì viết lại cũng chả đạt mục đích gì!
/(hà, Khà, khà,. . . .

HieuCD · 13/10/19

tuhumg00 đã viết:
Chào mọi người mình có bài toán muốn nhờ các bạn trợ giúp code vba :
+Mình muốn điền vào bảng xếp hạng vào ô( màu vàng) , dữ liệu được lấy từ 2 điều kiện cột A,B và bảng xếp hạng được lấy từ cột C
(giá trị được lấy như cột I) (như hình vẽ)
(Mình có sử dụng hàm Index và Match (excel), sumproduct nhưng khi thay một trong các giá trị ở bảng xếp hạng ở (cột C) vào nếu là giá trị 6 thì tìm thấy giá trị còn nếu để 6a thì hàm Index, Match không tìm thấy giá trị.(mình chưa tìm hiểu được nguyên nhân và xử lý).
-----Mong muốn trợ giúp có code VBA điền bảng xếp hạng vào ô màu vàng. mong các bạn giúp đỡ mình với nhé. Cảm ơn cả nhà nhiều.
file đính kèm mình để ở dưới
http://www.mediafire.com/file/flezfoyak6bm6cy/Dien_vao_bang_xep_hang.xlsm/file

Dùng bộ thu công thức Excel

Mã:

Private Sub Worksheet_Change(ByVal target As Range)
  Dim iR&, eR&
  Application.EnableEvents = False
  If Not Intersect(target, [F3:G100]) Is Nothing Then
    eR = Range("A65000").End(xlUp).Row
    iR = target.Row
    Cells(iR, 8).FormulaR1C1 = "=IFERROR(LOOKUP(2,1/(R3C1:R" & eR & "C1=RC[-2])/(R3C2:R" & eR & "C2=RC[-1]),R3C3:R" & eR & "C3),"""")"
    Cells(iR, 8).Value = Cells(iR, 8).Value
  End If
  Application.EnableEvents = True
End Sub

CHAOQUAY · 13/10/19

tuhumg00 đã viết:
Trường hợp của Mình áp dụng là : Cùng tên -khác lớp- cùng thứ hạng hoặc khác thứ hạng. ( có thể danh sách đó thêm học sinh "Tran than son1" - lớp 8A1 hoặc 9B1 - thứ hạng 9c hoặc 6a. Bảng TH của mình thì cố định, mình chỉ muốn tìm kiếm như hàm vlookup, index, match. Cảm ơn @CHAOQUAY đã quan tâm.Giúp mình với nhé.

Bạn chỉ cần trả lời là Có hoặc là Không, không cần giải thích vì đâu có gì quá phức tạp.

tuhumg00 · 13/10/19

CHAOQUAY đã viết:
Bạn chỉ cần trả lời là Có hoặc là Không, không cần giải thích vì đâu có gì quá phức tạp.

Uhm. Vậy mình trả lời bạn không có trường hợp đấy bạn à.

Bài đã được tự động gộp: 13/10/19

SA_DQ đã viết:
Trước tiên ta nên bàn đến thiết kế CSDL của bạn, vì nó đang có vấn đề:

(1): Với CSDL là danh sách HS toàn trường, ta phải xây dựng bộ mã hay số báo danh của HS trong trường; Không bao giờ tổ chức CSDL không có mã duy nhất cho mỗi dòng dữ liệu.

Sẵn đây mình xin gợi ý bộ mã hay xài của mình

Mã Họ và tên Mã BP Chức vụ
GPE.COM
FVC00 Đỗ Văn Công BX Xe
FVH00 Đặng Văn Hiếu BX CN
BHK00 Bùi Hồng Khải DG CN
FBH00 Đào Bá Hưng DG CN
LQP00 Lê Quang Phúc DG CN
MTT00 Mai Thị Thoa DG CN
NBT00 Nguyễn Thị Bích Thuận DG TT
NNL00 Nguyễn Ngọc Lâm KT NV
NTA00 Nguyễn Lương Trường An KT KCS
BVK00 Bùi Văn Khang Ox TX
FTV01 Đặng Tuấn Việt Ox CN
FTH00 Đào Trọng Hùng Kh NV
HHT00 Hồ Hữu Tâm Kh TP
LFT00 Lê Đức Thịnh Kh KT
PFT00 Phạm Đức Tuân Kh KT
PJG00 Phạm Giang Kh KT
VHD00 Vũ Hữu Huy Kh KT
NHN00 Nguyễn Hùng Nam BX KCS
NMH02 Nguyên Mạnh Hòa GD TL
NVH01 Nguyễn Việt Hồng DG KS

(2) Trong 1 CSDL, 1 trường ta không nên có 2 kiểu dữ liệu, như

Xếp hạng
3
4
5
6a
7
8b
9

Nếu là mình thì sẽ là 1 trong 2 trường hợp sau:

Xếp hạng Xếp hạng (A) Xếp hạng (B)
3 ===> 30 Hay: 3A
4 ===> 40 Hay: 4A
5 ===> 50 Hay: 5A
6a ===> 61 Hay: 6B
7 ===> 72 Hay: 7D
8b ===> 82 Hay: 8C
9 ===> 90 Hay: 9A

Mình muốn nhắn với bạn là CSDL tốt rồi ta mới tính chuyện khác, như lên cung trăng hoặc sao hỏa; Còn bằng ngược lại sẽ có lúc ta xuống âm phủ không chừng!

Chúc bạn ngày cuối tuần vui vẻ & thành công!

Cảm ơn bác @SA_DQ thực ra mục đích của e chỉ nhỏ thôi bác, vì e đưa ra dữ liệu như trường với lớp và tên để các bác diễn đàn hiểu và viết thành code thôi ạ. Vì cái e cần nó giống như một hàm tìm kiếm 2 điều kiện thôi ạ, điều kiện tên học sinh và tên lớp(vào bài toán thực tế của e thì các tên và lớp thành như (lỗ khoan (LK1(tên) - "UD1" ở lớp "3"(xep hang)"), (LK1 - UD2-lớp "3b") (bên ngành địa chất) ) .......vì văn trình bày của e nó kém sợ trình bày thế các bác không hiểu vì thế e trình bày theo kiểu như trên Tên- Lớp- xếp hạng. Chứ không cần phải xây dựng lại hệ thống ạ. thực ra nó chỉ cần tìm kiếm ra ô màu vàng thế thôi bác (như hình vẽ)

Bài đã được tự động gộp: 13/10/19

VetMini đã viết:
Thiết kế lại mất công lắm. Nhờ viết code khoẻ hơn.
Nếu bạn thấy bắt buộc phải thiết kế lại thì bạn viết giùm code thiết kế lại luôn đi.

E chỉ cần đơn giản như hình vẽ bác nhỉ.

tuhumg00 · 13/10/19

HieuCD đã viết:

Dùng bộ thu công thức Excel

Mã:

Private Sub Worksheet_Change(ByVal target As Range)
  Dim iR&, eR&
  Application.EnableEvents = False
  If Not Intersect(target, [F3:G100]) Is Nothing Then
    eR = Range("A65000").End(xlUp).Row
    iR = target.Row
    Cells(iR, 8).FormulaR1C1 = "=IFERROR(LOOKUP(2,1/(R3C1:R" & eR & "C1=RC[-2])/(R3C2:R" & eR & "C2=RC[-1]),R3C3:R" & eR & "C3),"""")"
    Cells(iR, 8).Value = Cells(iR, 8).Value
  End If
  Application.EnableEvents = True
End Sub

Bác có cách nào khi thay đổi số liệu bảng tìm kiếm nó tự nhảy không ạ. code của bác khi e copy một dữ liệu khác từ bảng TH vào thì nó không tự nhảy hết(vi dụ copy 2 dòng ở lớp bảng tổng hợp sang bảng tìm kiếm thì chỉ có dòng 1 là tìm được dữ liệu còn dòng 2 thì phải click vào mới tìm được giữ liệu mới mà phải click chuột vào từng ô. bác hiểu ý e ko ạ.

HieuCD · 13/10/19

tuhumg00 đã viết:
Bác có cách nào khi thay đổi số liệu bảng tìm kiếm nó tự nhảy không ạ. code của bác khi e copy một dữ liệu khác từ bảng TH vào thì nó không tự nhảy hết(vi dụ copy 2 dòng ở lớp bảng tổng hợp sang bảng tìm kiếm thì chỉ có dòng 1 là tìm được dữ liệu còn dòng 2 thì phải click vào mới tìm được giữ liệu mới mà phải click chuột vào từng ô. bác hiểu ý e ko ạ.

thêm vòng for

Mã:

Private Sub Worksheet_Change(ByVal target As Range)
  Dim i&, iR&, eR&, sRow&, Rng As Range
  Set Rng = Intersect(target, [F3:G65000])
  If Not Rng Is Nothing Then
    Application.EnableEvents = False
    eR = Range("A65000").End(xlUp).Row
    sRow = Rng.Rows.Count
    For i = 1 To sRow
      iR = Rng(i, 1).Row
      Cells(iR, 8).FormulaR1C1 = "=IFERROR(LOOKUP(2,1/(R3C1:R" & eR & "C1=RC[-2])/(R3C2:R" & eR & "C2=RC[-1]),R3C3:R" & eR & "C3),"""")"
      Cells(iR, 8).Value = Cells(iR, 8).Value
    Next i
    Application.EnableEvents = True
  End If
End Sub

tuhumg00 · 13/10/19

HieuCD đã viết:

thêm vòng for

Mã:

Private Sub Worksheet_Change(ByVal target As Range)
  Dim i&, iR&, eR&, sRow&, Rng As Range
  Set Rng = Intersect(target, [F3:G65000])
  If Not Rng Is Nothing Then
    Application.EnableEvents = False
    eR = Range("A65000").End(xlUp).Row
    sRow = Rng.Rows.Count
    For i = 1 To sRow
      iR = Rng(i, 1).Row
      Cells(iR, 8).FormulaR1C1 = "=IFERROR(LOOKUP(2,1/(R3C1:R" & eR & "C1=RC[-2])/(R3C2:R" & eR & "C2=RC[-1]),R3C3:R" & eR & "C3),"""")"
      Cells(iR, 8).Value = Cells(iR, 8).Value
    Next i
    Application.EnableEvents = True
  End If
End Sub

Em dùng được rồi bác, cảm ơn bác nhiều nhé.

Trợ giúp code vba tìm kiếm thay thế hàm Index Match

tuhumg00

Thành viên mới

File đính kèm

phulien1902

GPE - My love

tuhumg00

Thành viên mới

CHAOQUAY

Thành viên gắn bó

tuhumg00

Thành viên mới

SA_DQ

/(hông là gì!

VetMini

Ăn cùng góc phố

SA_DQ

/(hông là gì!

HieuCD

Chuyên gia GPE

File đính kèm

CHAOQUAY

Thành viên gắn bó

tuhumg00

Thành viên mới

tuhumg00

Thành viên mới

HieuCD

Chuyên gia GPE

tuhumg00

Thành viên mới

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

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian