Chia sẻ_ Hàm Glookup_ Tìm kiếm dữ liệu giao nhau giữa cột và hàng

NHG · 22/5/20

Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột

Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Cu Tồ · 22/5/20

tiện đây bác cho hỏi là e có file dữ liệu như sau,muốn dựa vào cột

NHG đã viết:
Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột
View attachment 237776
Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

tiện đây bác cho e hỏi là e có file dữ liệu như sau muốn dựa vào ngày ở hàng ngang xong đó tìm ra cột dữ liệu,rồi tìm trong cột đó có chứa dữ liệu phù hợp điều kiện thứ hai là họ tên để lấy số lượng,bác xem có hướng nào giúp e với file và mô tả ở dưới ah

Bài đã được tự động gộp: 22/5/20

trong file có 2 sheet một shet tổng hợp là nơi nhập dữ liệu để tìm kiếm và một sheet chứ dữ liệu.e muốn là khi nhập tên khách hàng ở sheet tổng hợp thì sẽ dựa vào tên khách hàng và điều kiện là ngày để đến sheet dữ liệu tìm là lấy ra số lượng của khách hàng đó,mong anh chi em trong diễn đàn giúp đỡ
kết quả mong muốn và yêu cầu tìm kiếm

NHG · 22/5/20

cuto24 đã viết:
tiện đây bác cho hỏi là e có file dữ liệu như sau,muốn dựa vào cột

tiện đây bác cho e hỏi là e có file dữ liệu như sau muốn dựa vào ngày ở hàng ngang xong đó tìm ra cột dữ liệu,rồi tìm trong cột đó có chứa dữ liệu phù hợp điều kiện thứ hai là họ tên để lấy số lượng,bác xem có hướng nào giúp e với file và mô tả ở dưới ah

Bài đã được tự động gộp: 22/5/20

trong file có 2 sheet một shet tổng hợp là nơi nhập dữ liệu để tìm kiếm và một sheet chứ dữ liệu.e muốn là khi nhập tên khách hàng ở sheet tổng hợp thì sẽ dựa vào tên khách hàng và điều kiện là ngày để đến sheet dữ liệu tìm là lấy ra số lượng của khách hàng đó,mong anh chi em trong diễn đàn giúp đỡ
kết quả mong muốn và yêu cầu tìm kiếm

Mình ko có nhiều thời gian nên không hỗ trợ đc bạn, nhưng về cách thiết kế cơ sở dữ liệu hiện tại của bạn là chưa phù hợp; dữ liệu người ta thường nhập theo dòng chữ không nhập theo cột. Ví dụ của bạn dữ liệu chỉ cần thiết kế 4 cột:
Số Thứ tự Ngày thực hiện Người thực hiện Số lượng
Như thế sau này mới dễ lập công thức và quản lý dữ liệu

Cu Tồ · 22/5/20

NHG đã viết:
Mình ko có nhiều thời gian nên không hỗ trợ đc bạn, nhưng về cách thiết kế cơ sở dữ liệu hiện tại của bạn là chưa phù hợp; dữ liệu người ta thường nhập theo dòng chữ không nhập theo cột. Ví dụ của bạn dữ liệu chỉ cần thiết kế 4 cột:
Số Thứ tự Ngày thực hiện Người thực hiện Số lượng
Như thế sau này mới dễ lập công thức và quản lý dữ liệu

cảm ơn bác đã góp ý,cái này của e tổng hợp theo ngày,và mỗi ngày thì số ng và danh sách tên trong đó không giống nhau,và dữ liệu mỗi ngày là tầm 3 nghìn dòng ah,nếu để dữ liệu ngày theo dòng thì 1 ngày thế tầm 3 nghìn dòng và tới ngày thứ 2 đã tầm 6 nghìn dòng rồi bác

hocexcel_1991 · 22/5/20

cuto24 đã viết:
tiện đây bác cho hỏi là e có file dữ liệu như sau,muốn dựa vào cột

tiện đây bác cho e hỏi là e có file dữ liệu như sau muốn dựa vào ngày ở hàng ngang xong đó tìm ra cột dữ liệu,rồi tìm trong cột đó có chứa dữ liệu phù hợp điều kiện thứ hai là họ tên để lấy số lượng,bác xem có hướng nào giúp e với file và mô tả ở dưới ah

Bài đã được tự động gộp: 22/5/20

trong file có 2 sheet một shet tổng hợp là nơi nhập dữ liệu để tìm kiếm và một sheet chứ dữ liệu.e muốn là khi nhập tên khách hàng ở sheet tổng hợp thì sẽ dựa vào tên khách hàng và điều kiện là ngày để đến sheet dữ liệu tìm là lấy ra số lượng của khách hàng đó,mong anh chi em trong diễn đàn giúp đỡ
kết quả mong muốn và yêu cầu tìm kiếm

Bạn thử dùng công thức này xem sao

Mã:

=VLOOKUP($F2,OFFSET('số liệu mỗi tháng'!$A$2:$A$3000,,MATCH($E$1,'số liệu mỗi tháng'!$B$1:$BK$1,0),,2),2,0)

SA_DQ · 22/5/20

NHG đã viết:
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột

Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Nếu là mình thì mình sẽ phải tách ra tham biến 'Giá trị cần tìm' thành 2; Như vậy cú pháp hàm sẽ phải là:

GPELookUp(MaNV As String, TenCot As String; BangCantim As Range )

tiniluu123 · 22/5/20

NHG đã viết:
Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột
View attachment 237776
Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Gặp trường hợp này là em Index Match liền vì mới học lỏm được của các anh chị trong GPE, hi. Trước đó nữa thì em phải tạo dòng tiêu đề phụ ra 1 chỗ khác rồi đặt STT cột cho mỗi tiêu đề. Xong vào ô công thức chính dùng vlookup (có hlookup bên trong) . hiiii, ngại ghê. Em chưa biết VBA.

PS: bác đặt tên hàm dài thòn mắc cười vậy. Chắc là để mọi người nhìn vào sẽ biết luôn cấu trúc hàm hén !
Cám ơn bác.

Nguyễn Hồng Lam · 22/5/20

NHG đã viết:
Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột
View attachment 237776
Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Vậy bạn có code VBA của hàm này không? Cho mình với

Bài đã được tự động gộp: 22/5/20

SA_DQ đã viết:
Nếu là mình thì mình sẽ phải tách ra tham biến 'Giá trị cần tìm' thành 2; Như vậy cú pháp hàm sẽ phải là:

GPELookUp(MaNV As String, TenCot As String; BangCantim As Range )

Bạn có code VBA không cho minh với

tiniluu123 · 22/5/20

Nguyễn Hồng Lam đã viết:
Vậy bạn có code VBA của hàm này không? Cho mình với

Bạn thớt NHG có share file mẫu chứa code luôn mà. Bạn down file về rồi mở ra vào VBA mà xem thui. Mình biết cách mở ra xem mà xem không hiểu đây này.

giaiphap · 22/5/20

NHG đã viết:
Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột
View attachment 237776
Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Code bạn viết thế này chạy sẽ rất chậm nếu dữ liệu nhiều. Theo tôi với cái đề này thì dùng công thức là được rồi.

Nguyễn Hồng Lam · 22/5/20

tiniluu123 đã viết:
Bạn thớt NHG có share file mẫu chứa code luôn mà. Bạn down file về rồi mở ra vào VBA mà xem thui. Mình biết cách mở ra xem mà xem không hiểu đây này.

Mình down rồi. Mà chưa biết cách mở vào xem bạn à. Để lấu rồi quên mất

tiniluu123 · 22/5/20

Nguyễn Hồng Lam đã viết:
Mình down rồi. Mà chưa biết cách mở vào xem bạn à. Để lấu rồi quên mất

Ơ, vậy rồi bạn biết viết VBA không?
Bạn xem hình:
Mở file rồi vào thẻ Developer, chọn Visual Basic (hoặc bấm tổ hợp Alt+F11 ra ngay bước này) , chọn tiếp Modules.

Cu Tồ · 22/5/20

hocexcel_1991 đã viết:
Bạn thử dùng công thức này xem sao

Mã:

=VLOOKUP($F2,OFFSET('số liệu mỗi tháng'!$A$2:$A$3000,,MATCH($E$1,'số liệu mỗi tháng'!$B$1:$BK$1,0),,2),2,0)

rất cảm ơn bác,e đã thử và chuẩn như ý e muốn ah,một lần nữa cảm ơn bác rất nhiều

ndu96081631 · 22/5/20

NHG đã viết:
Hi các bạn
Chắc nhiều bạn đã gặp tình huống tìm giá trị giao nhau giữa hàng và cột
View attachment 237776
Có nhiều cách giải quyết, nhưng để tiện trong công việc mình đã tạo ra hàm Glookup để việc tìm kiếm đỡ nhọc hơn
Glookup(Giá trị cần tìm; Bảng cần tìm)
Mình đưa file ví dụ lên để các bạn tham khảo, nếu có thể tối ưu code chạy tốt hơn thì càng tốt

Có khi nào có trường hợp như hình dưới đây không

Lúc ấy nếu tôi muốn kết quả là 33 thì phải làm sao?

NHG · 22/5/20

ndu96081631 đã viết:
Có khi nào có trường hợp như hình dưới đây không

View attachment 237789

Lúc ấy nếu tôi muốn kết quả là 33 thì phải làm sao?

Có bạn ạ, vì nó ghộp mã cột và mã hàng để thành mã tìm kiếm, nên khi đặt mã lúc đầu cố tránh thôi b

Chắc các cao thủ sẽ tìm ra cách bẫy lỗi, nhưng hàm có thể chạy chậm hơn vì phải kiểm tra nhiều điều kiện

Phuocam · 22/5/20

Sao không dùng cặp VLOOKUP + MATCH ? Hahaha ...

VetMini · 22/5/20

phuocam đã viết:
Sao không dùng cặp VLOOKUP + MATCH ? Hahaha ...

Hình như còn giai đoạn phải tách cái chuỗi cần tìm ra thành 2 phần rổi từ đó mới dò.
Thớt chả giải thích hàm nhận tham số gì và trả về gì cho nên tôi lười xem code. (vả lại tôi cũng không thích nói tiếng Tây)

ndu96081631 · 22/5/20

NHG đã viết:
Có bạn ạ, vì nó ghộp mã cột và mã hàng để thành mã tìm kiếm, nên khi đặt mã lúc đầu cố tránh thôi b
Chắc các cao thủ sẽ tìm ra cách bẫy lỗi, nhưng hàm có thể chạy chậm hơn vì phải kiểm tra nhiều điều kiện

"Bẫy lỗi" bằng cách làm giống như bài #16 được không bạn?

SA_DQ · 22/5/20

SA_DQ đã viết:
Nếu là mình thì mình sẽ phải tách ra tham biến 'Giá trị cần tìm' thành 2; Như vậy cú pháp hàm sẽ phải là:
GPELookUp(MaNV As String, TenCot As String; BangCantim As Range )

Bạn có code VBA không cho minh với

4

5

8

9

LMD01

Tiền 3

Mã tên

Họ và tên

Tiền 1

Tiền 2

Tiền 3

Tiền 9

Ghi chú

Ghi chú1

LMD00

Lê Mỹ Dung

1

2

jk

50

Ghi chú 1

5.4

=traBang(K6,M6,B7:I19)

LMD01

Lê T. Mỹ Dung

5

7

5.4

16

Ghi chú 2

LMD02

Lâm Mạnh Dũng

8

6

10

LMD03

Lý Mỹ Dung

50

60

70

Ghi chú 4

LMD04

Lê Võ Mạnh Danh

1

Ghi chú 5

LMD05

Lại Minh Danh

LMD06

Lý Minh Dùng

3

Ghi chú 7

LMD07

Lê Văn Mai Dy

Ghi chú 8

LMD08

Lại Mai Du

LMD09

Lại T. Mỹ Du

13

Hehe

Ghi chú 10

LMD00

Ngô & Ba

NTA50

Nhâm T. An

160

#DIV/0!

300

Ghi chú 13

Nội dung hàm:

Mã:

Function TraBang(MaNV As String, Col As String, CSDL As Range)
Dim J As Long, W As Integer
Dim Arr()

Arr() = CSDL.Value
For J = 1 To UBound(Arr())
    If Arr(J, 1) = MaNV Then
        For W = 2 To UBound(Arr(), 2)
            If Arr(1, W) = Col Then
                TraBang = Arr(J, W)
                Exit Function
            End If
        Next W
    End If
Next J
End Function

Nguyễn Hồng Lam · 22/5/20

SA_DQ đã viết:
4
5
8
9
LMD01 Tiền 3
Mã tên Họ và tên Tiền 1 Tiền 2 Tiền 3 Tiền 9 Ghi chú Ghi chú1
LMD00 Lê Mỹ Dung
1
2
jk
50
Ghi chú 1 5.4 =traBang(K6,M6,B7:I19)
LMD01 Lê T. Mỹ Dung
5
7
5.4
16
Ghi chú 2
LMD02 Lâm Mạnh Dũng
8
6
10
LMD03 Lý Mỹ Dung
50
60
70
Ghi chú 4
LMD04 Lê Võ Mạnh Danh
1
Ghi chú 5
LMD05 Lại Minh Danh
LMD06 Lý Minh Dùng
3
Ghi chú 7
LMD07 Lê Văn Mai Dy Ghi chú 8
LMD08 Lại Mai Du
LMD09 Lại T. Mỹ Du
13
Hehe Ghi chú 10
LMD00 Ngô & Ba
NTA50 Nhâm T. An
160
#DIV/0!
300
Ghi chú 13

Nội dung hàm:

Mã:

Function TraBang(MaNV As String, Col As String, CSDL As Range) Dim J As Long, W As Integer Dim Arr() Arr() = CSDL.Value For J = 1 To UBound(Arr()) If Arr(J, 1) = MaNV Then For W = 2 To UBound(Arr(), 2) If Arr(1, W) = Col Then TraBang = Arr(J, W) Exit Function End If Next W End If Next J End Function

Cám ơn SA_DQ rất nhiều

Chia sẻ_ Hàm Glookup_ Tìm kiếm dữ liệu giao nhau giữa cột và hàng

Thành viên hoạt động

File đính kèm

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

File đính kèm

Thành viên hoạt động

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Bắt đầu học Excel

/(hông là gì!

Có bao giờ bạn tự hỏi "Tôi là ai?"

Thành viên hoạt động

Có bao giờ bạn tự hỏi "Tôi là ai?"

==(^o^)==

Thành viên hoạt động

Có bao giờ bạn tự hỏi "Tôi là ai?"

Tìm đến kiến thức! ꧁༺ Cu Tồ ༻꧂

Huyền thoại GPE

Thành viên hoạt động

Thành viên mới

Ăn cùng góc phố

Huyền thoại GPE

/(hông là gì!

Thành viên hoạt động