Các câu hỏi về hàm dò tìm dữ liệu (Lookup, Vlookup, Hlookup...)

[songlon]

Các bạn quan tâm đến Excel phần đông đều biết đến hàm VLookUp. Dưới đây songlon gởi đến các bạn để tham khảo.
Vấn đề thuộc về thống kê căn bản.
Giả sử chúng ta đo chiều cao của một vật gì đó và có được kết quả do cân đong đo đếm. Dữ liệu thu thập được ban đầu gọi là dữ liệu thô. Nhiệm vụ của chúng ta là phải phân tổ, phân lớp gì đó cho nó để dễ thống kê vậy mà.
Ở đây chúng ta sử dụng chủ yếu hai hàm là VLOOKUP và COUNTIF.
Dĩ nhiên là kiến thức về thống kê cơ bản nữa.
Các bạn xem thử và góp ý.
Bye

 

[hai2hai]

vCOUNTIF - hybrid of COUNTIF và VLOOKUP

Những hàm này cho phép "count" với nhiều điều kiện (Hàm COUNTIF chuẩn của Excel chỉ cho phép 1 điều kiện thôi) để đếm các bản ghi dựa trên các giá trị của nhiều cột (do việc thêm cột, ghép dữ liệu, sắp xếp/lọc dữ liệu là thường xuyên xảy ra)

Public Function vCOUNTIF(Table_array As Range, Col_index_num1 As Long, Lookup_criteria1 As String, ParamArray additionalColsAndCriteria() As Variant) As Long

    On Error Resume Next
    Dim i As Long, r As Long, j As Integer, s As Integer
    i = 0


    For r = 1 To IIf(Table_array.Rows.Count = 65536, Table_array.Worksheet.UsedRange.Rows.Count, Table_array.Rows.Count)
        j = Application.WorksheetFunction.CountIf(Table_array.Cells(r, Col_index_num1), Lookup_criteria1)


        For s = 0 To UBound(additionalColsAndCriteria) Step 2
            j = j * Application.WorksheetFunction.CountIf(Table_array.Cells(r, additionalColsAndCriteria(s)), additionalColsAndCriteria(s + 1))
        Next s

        i = i + j
    Next r

    vCOUNTIF = i
End Function


Public Function hCOUNTIF(Table_array As Range, Row_index_num1 As Long, Lookup_criteria1 As String, ParamArray additionalRowsAndCriteria() As Variant) As Long

    On Error Resume Next
    Dim i As Long, c As Long, j As Integer, s As Integer
    i = 0


    For c = 1 To IIf(Table_array.Columns.Count = 65536, Table_array.Worksheet.UsedRange.Columns.Count, Table_array.Columns.Count)
        j = Application.WorksheetFunction.CountIf(Table_array.Cells(Row_index_num1, c), Lookup_criteria1)


        For s = 0 To UBound(additionalRowsAndCriteria) Step 2
            j = j * Application.WorksheetFunction.CountIf(Table_array.Cells(additionalRowsAndCriteria(s), c), additionalRowsAndCriteria(s + 1))
        Next s

        i = i + j
    Next r

    hCOUNTIF = i
End Function

 

[LearnExcel]

Theo toi co cach Non-VBA la dung ham SUMPRODUCT do
Vd, sumproduct((Range1 toantu_sosanh dk1)*(Range2 toantu_sosanh dk2)*(Range3 toantu_sosanh dk3))
Co the co toi 30 dieukien
Luu y la kich thuoc cua Range1 to Range_n phai bang nhau.
Cac dieu kien nay deu la AND - dieu kien loc thong dung nhat

Cai nay xai khoe hon VBA do.

 

[hai2hai]

Tôi ko làm Excel nên cũng ko muốn test lại nhưng chả nhẽ SUMPRODUCT lại là Count à? Mọi người làm Excel xem hộ lại cái (1 cái là tỉnh tổng, một cái là đếm mà).

Bạn lần sau chịu khó gõ tiếng Việt nhé, nếu ko bài sẽ bị xóa đấy!

 

[handung107]

Hàm SUMPRODUCT có thể dùng để đếm, Hải à. Dùng hàm SUMPRODUCT hay VBA mỗi cái đều có những ưu điểm và thuận lợi riêng, tùy từng điều kiện cụ thể mà ta quyết định sử dụng VBA hay hàm.

 

[Nguyễn Duy Tuân]

Cac dieu kien nay deu la AND - dieu kien loc thong dung nhat

Tất cả đều có thể kết hợp logic AND, OR đó bạn. Vẫn là do cách thôi.
VD: Tính tổng số tiền phát sinh Nợ TK131 của 2 khách hàng "KH001","KH002"
=SUMPRODUCT(((PS_DVKH="KH001")+(PS_DVKH="KH002"))*(PS_NOTK="131"),PS_TIEN)

Cai nay xai khoe hon VBA do.

Chưa hẳn đâu, nếu CSDL nhỏ (<2000 dòng) thì khó mà so sánh được, nhưng nếu dữ liệu khá lớn thì dùng SUM(IF hay SUMPRODUCT đều chậm (Lý do tôi đã giải thích ở bài khác).
Viết VBA tốt hơn hay không còn tùy vào cách viết, nếu viết theo cách giải tuần tự thì có thể không bằng cách dùng hàm có sẵn...Trước đây các bảng phải tính tổng có nhiều điều kiện tôi đều dùng công thức+hàm, nhưng dữ liệu bây giờ quá lớn (>30,000 dòng) tôi phải viết VBA mới chịu được.

 

[LearnExcel]

Với dữ liệu cỡ chục nghìn dòng trở lên, theo tôi nên dùng ứng dụng cơ sở dữ liệu như Access xử lý rồi xuất lại qua Excel thì chắc khỏe hơn, đúng không?

 

[thinhpcr]

Thắc mắc về hàm RIGHT với VLOOKUP !

Đề bài như sau:

---------A------------------- B
1 -----Mã hàng--------------Tên hàng
2 ------A01------------------?
3 ------A02------------------?
4 ------A03------------------?
5 ------A02------------------?

Dựa vào 2 số cuối của Mã hàng hãy điền tên hàng theo bảng sau:

--------A------------------B
6 -----Mã hàng ----------Tên hàng
7------01-----------------Xe máy
8------02-----------------Ô tô
9------03-----------------Cần cẩu

Em sử dụng hàm như sau:
=vlookup(right(A2,2),$A$7:$B$9,2,0)
Kết quả đều trả về là #N/A. (do 2 số cuối là số)
Nếu thay 2 số cuối của Mã hàng là Chữ chẳng hạn (ABC, ACD,...) thì kết quả trả về đúng.

Em cũng biết nếu là số thì phải kết hợp với hàm Right như: Value(right(A2,2)) thì mới cho kết quả đúng nhưng vấn đề ở đây là trong Mã hàng nếu có xen kẽ 2 số cuối cả chữ lẫn số thì dùng kết hợp như thế nào ?
Dùng Vlookup(right(A2,2)) thì chỉ dùng được với chữ. Còn Vlookup(value(right(A2,2)) thì chỉ dùng được với số. Nếu cả chữ lẫn số thì các công thức trên đều trả về sai.
Vậy mong mọi người giúp cho. Em sắp thi rồi nhanh nhé !

 

[nvson]

Vấn đề ở đây là giải quyết trường hợp số 0 ở đầu thôi.
Trong hàm Vlookup, thay vì dùng hàm Right bạn sử dụng hàm Text rồi mới sử dụng hàm Right:
Bạn sử dụng: right(A2,2)
Đổi thành: TEXT(RIGHT(A2,2),"0#")
.....

 

[thinhpcr]

Không hẳn số 0 ở đầu đâu. Đây là đề bài mình ví dụ thôi còn có thể Mã hàng như sau:
SDM2 ---> Sử dụng hàm VLookup và Right lấy 2 số cuối thì bình thường nhưng
HD64--------
|--> Nếu lấy 2 số cuối để tham chiếu thì đều báo sai
HD74--------

Mình nói là nói trường hợp mã hàng cả 2 số cuối có thể ở dạng chữ (M2) hoặc dạng số (64, 74). Mong các bạn giúp mình !

 

[ThuNghi]

Bạn phải định dạnh cột A là Text, vì 64 or 74, 01 thì text thì Vlookup mới OK
Còn muốn biết text hay o thì dùng hàm Type()

 

[thinhpcr]

Đây là bài tập của mình mong các bạn giúp đỡ giải quyết cho ! XIn cảm ơn !

 

[nvson]

Bạn chọn toàn bộ cột mã hàng ($E$18:$F$21) rồi định dạng là Text. Sau đó đánh lại tên mã hàng (vẫn mã như cũ) là được.

 

[thinhpcr]

Bạn chọn toàn bộ cột mã hàng ($E$18:$F$21) rồi định dạng là Text. Sau đó đánh lại tên mã hàng (vẫn mã như cũ) là được.

Vậy còn bảng tham chiếu có cột mã hàng (01, 02, 03) mình có phải định dạng là Text không ?
NẾu có thể các bạn giải hộ mình bài tâp trên rồi upload lên lại nhé. Mình cảm ơn rất nhiều. Mình sắp thi rồi mà chẳng biết gì cả nguy quá !

 

[nvson]

NẾu có thể các bạn giải hộ mình bài tâp trên rồi upload lên lại nhé

.........................................

 

[thinhpcr]

Bạn nvson có thể giải cho mình cả phần Tính tổng tiền bán được của mỗi mặt hàng được không ? Trong bài tập mà mình upload lên đó ! Cảm ơn bạn nhiều !

 

[nvson]

Bạn sử dụng hàm SumIf là được.
Nếu không thì tải file sau nhé!

 

[hunghuongus]

dùng Vlookup tìm và trừ nợ

Ai biết xin chỉ dùm :
có một danh sách ở 4 sheel data1, data2, data3, data1,nhu nhau gồm mã số, tên, điạ chỉ, số nợ tháng 1, nợ tháng 2, v.v...
ở sheel khác dùng hàm để tìm ra tên , điạ chỉ, nợ tháng 1, nợ thàng , V.VV
=IF(ISNA(VLOOKUP(B4;data1;2;0))=FALSE;VLOOKUP(B4;data1;2;0);IF(ISNA(VLOOKUP(B4;data2;2;0))=FALSE;VLOOKUP(B4;data2;2;0);IF(ISNA(VLOOKUP(B4;data3;2;0))=FALSE;VLOOKUP(B4;data3;2;0);IF(ISNA(VLOOKUP(B4;KK;2;0))=FALSE;VLOOKUP(B4;KK;2;0);" "))))
tất cả đều tốt có một vấn đề chưa giải quyết được là.
tìm lần thứ 1 mã 3600526526 hiện ra đầy đủ theo mong muốn tôi dùng công thức trừ nợ tháng 1.
tìm lần thứ 2 cũng mã 3600526526 trừ tiếp tháng 2 sẽ không có tác dụng nữa,ai có cách nào hay chỉ dùm,
Tóm lại hàm Vlookup chỉ tác dụng lần 1 với các thao các +, -, * , /
còn lần 2 không tác dụng

 

[Nguyễn Duy Tuân]

Bạn làm đúng theo các bước sau:
1- Bôi đen vùng A1:A10 formate dạng Text
2- Gõ lại những ô có giá trị là số (như là 63, 52). Mục đích để Excel hiểu lại đó là giá trị kiểu Text.

Có một bài học mà giữa Word và Excel ngược nhau:
Word: Gõ văn bản xong rồi định dạng
Excel: Định dạng rồi gõ. Excel tự động định nghĩa kiểu giá trị khi ô định dạng là "General" (63 và 53 là 2 con số), bì vậy phải ép kiểu (định dạng) trước khi vào giá trị (khi đó 63 và 53 là 2 chữ số).

Bây giờ mọi cái thật dễ hiểu!

 

[Nguyễn Duy Tuân]

Do bạn đặt các điều kiện trong một hàm IF duy nhất nên nó chie nhận một kết quả mà thôi. Bạn hãy tách ra từng cặp ra rồi + - cho nhau.

Nếu bạn chưa làm được thì gửi mẫu dữ liệu lên tôi giúp.