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

  • Thread starter Thread starter songlon
  • Ngày gửi Ngày gửi
Liên hệ QC

Closed poll

  • 1

    Votes: 1 100.0%

  • 2

    Votes: 0 0.0%
  • Total voters
    1
  • Poll closed .

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
Status
Không mở trả lời sau này.
S

songlon

Thành viên mới
Tham gia
13/7/06
Bài viết
24
Được thích
137
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
 

File đính kèm

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)

Mã: 
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
 
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.
 
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!
 
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.
 
LearnExcel đã viết:
Cac dieu kien nay deu la AND - dieu kien loc thong dung nhat
Nhấp chuột vào đây để mở rộng...
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)

LearnExcel đã viết:
Cai nay xai khoe hon VBA do.
Nhấp chuột vào đây để mở rộng...
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.
 
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?
 
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é !
 
Lần chỉnh sửa cuối:
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#")
.....
 
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 !
 
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()
 
Đâ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 !
 

File đính kèm

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.
 
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.
Nhấp chuột vào đây để mở rộng...
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á !
 
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 !
 
Bạn sử dụng hàm SumIf là được.
Nếu không thì tải file sau nhé!
 

File đính kèm

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
 
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!
 
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.
 
VLOOKUP dùng 2 tham chiếu, làm sao?

Chào các bạn,
Tôi có một thắc mắc về cách sử dụng hàm VLOOKUP khi cần phải dùng đến cả 2 tham chiếu. Ví dụ:
---- A ---------- B ----------- C ---------
1 Hàng Ngày bán Số lượng
2 Áo 10/11 12
3 Áo 12/11 5
4 Quần 13/11 7
........

Và tôi muốn tra Vlookup trong ngày 10/11, món hàng Áo bán được mấy cái. Ở đây tôi buộc phải dùng một mẹo nhỏ để tra, tôi thêm 1 cột nữa là kết hợp giữa "Hàng" và "Ngày bán":

---- A ---------- B ----------- C --------------- D ----
1 Hàng Ngày bán Mapping Số lượng
2 Áo 10/11 "=A2&Day(B2)" 12
3 Áo 12/11 "=A3&Day(B3)" 5
4 Quần 13/11 "=A4&Day(B4)" 7
........

Sau đó VLOOKUP thì dùng cột mapping này để tra cứu.

Tôi có gửi file đính kèm. Vậy có ai biết cách dùng như thế nào mà không cần phải thêm một cột mapping như thế k, xin chỉ giáo.
Cảm ơn.
 

File đính kèm

Lần chỉnh sửa cuối:
=sumproduct(($b$2:$b$4=c10)*($a$2:$a$4=d10)*($d$2:$d$4))
 
Có cách nào dùng Vlookup để tìm giá trị của cột nhưng khác dòng ko?
Mong các bạn chỉ giáo!

file làm ví dụ
 

File đính kèm

Chỉnh sửa lần cuối bởi điều hành viên:
Có chứ. Bạn kết hợp sử dụng hàm Index, Match, Quotient... là được
 
Dear CPH,
-----------
Mình đã xem file minh hoạ của bạn. Bạn đang dùng hàm VLOOKUP là rất thích hợp. Tuy nhiên để LOOKUP được bạn đang phải dùng cột phụ, hơn nữa giá trị tìm kiếm là diễn giải khoản mục thì rất không hợp lý.
Theo mình với mỗi khoản mục trong bảng Dongiachitiet bạn gán thêm một mã đơn giá. Sau đó trên bảng Duthau, bạn sử dụng mã này. Bây giờ dùng VLOOKUP để lấy ra đơn giá tương ứng dựa vào mã đơn giá hiện hành.
Chúc bạn thành công!
 
Nhấn Alt+F11 để vào VBA. Nhấn tiếp Insert/Module rrồi thêm đoạn code dưới đây:
Mã: 
Option Explicit
Public Function traDGCT(MaHieu, CongViec)
Dim row1 As Integer
Dim baseSheet As Worksheet
Set baseSheet = Sheets("Don gia chi tiet")
row1 = WorksheetFunction.Match(MaHieu, Range("rngMahieu"), 0)
Do
    If baseSheet.Cells(row1, "D") = CongViec Then
        Exit Do
    End If
    row1 = row1 + 1
Loop Until Not IsEmpty(baseSheet.Cells(row1, "B"))
traDGCT = baseSheet.Cells(row1, "I")
End Function
Trở về MS Excel, tại ô F8 trong sheet Du thau bạn đánh công thức:
Mã: 
=traDGCT(B8,C8)
Dựa vào đoạn code trên bạn có thể tính được tổng cộng chi phí "Nhân công, Máy thi công..." mà không cần đến phần mềm Dự toán.
 
Chào các bạn!
Rất cảm ơn các bạn đã tham gia góp ý giúp đỡ tôi giả quyết vấn đề.
Nhưng tôi thấy nếu dùng theo cách của bạn Đào Việt Cường thì có vẻ hơi khó vì nếu tôi cần tra phần Vật liệu, nhân công, máy...
Theo doạn code của bạn NVSON thì có vẻ đáp ứng được yêu cầu nhưng nó lại không chạy.
Bạn NVSON có thể xem giúp lại giúp tôi đc ko?
Thanks alot!
 
CPH đã viết:
Chào các bạn!
.....
Theo doạn code của bạn NVSON thì có vẻ đáp ứng được yêu cầu nhưng nó lại không chạy.
Bạn NVSON có thể xem giúp lại giúp tôi đc ko?
Thanks alot!
Nhấp chuột vào đây để mở rộng...
Mình thấy nó chạy ổn đấy chứ!
 

File đính kèm

Cảm ơn bạn NVSON rất nhiều!
down của bạn về thì ok nhưng tôi copy đoạn code thì lại ko chạy!
 
nvson đã viết:
Nhấn Alt+F11 để vào VBA. Nhấn tiếp Insert/Module rrồi thêm đoạn code dưới đây:
Mã: 
Option Explicit
Public Function traDGCT(MaHieu, CongViec)
Dim row1 As Integer
Dim baseSheet As Worksheet
Set baseSheet = Sheets("Don gia chi tiet")
row1 = WorksheetFunction.Match(MaHieu, Range("rngMahieu"), 0)
Do
    If baseSheet.Cells(row1, "D") = CongViec Then
        Exit Do
    End If
    row1 = row1 + 1
Loop Until Not IsEmpty(baseSheet.Cells(row1, "B"))
traDGCT = baseSheet.Cells(row1, "I")
End Function
Trở về MS Excel, tại ô F8 trong sheet Du thau bạn đánh công thức:
Mã: 
=traDGCT(B8,C8)
Dựa vào đoạn code trên bạn có thể tính được tổng cộng chi phí "Nhân công, Máy thi công..." mà không cần đến phần mềm Dự toán.
Nhấp chuột vào đây để mở rộng...
Àh tôi đã hiểu vì sao doạn code ko chạy,
vì bạn ko hướng dẫn tôi đặt define name rngMaHieu cho cột B sheet dongiachitiet.
Nhưng cách này chỉ có thể áp dụng để tìm đơn giá được thôi vì:
- Đơn giá thì công việc nào cũng có
- Nhưng nếu tìm Nhân công, máy thi công, .... thì không jphải công việc nào cũng có.
Mong bạn chỉ giáo thêm.
 
Mình quên không bảo bạn tạo Name trong sheet Don gia chi tiet.
Bạn thay đổi code này nhé.
Mã: 
Option Explicit
Public Function traDGCT(MaHieu, CongViec)
Dim Tontai As Boolean
Dim row1 As Integer
Dim baseSheet As Worksheet
Set baseSheet = Sheets("Don gia chi tiet")
row1 = WorksheetFunction.Match(MaHieu, Range("rngMahieu"), 0)
Tontai = False
Do
    If LCase(baseSheet.Cells(row1, "D")) = LCase(CongViec) Then
        Tontai = True
        Exit Do
    End If
    row1 = row1 + 1
Loop Until Not IsEmpty(baseSheet.Cells(row1, "B"))
If Tontai Then
    traDGCT = baseSheet.Cells(row1, "I")
Else
    MsgBox "Ban kiem tra lai:  " & MaHieu & "  hoac  " & CongViec
    traDGCT = ""
End If
End Function
Dựa vào hàm trên bạn lập thêm một hàm nữa là có thể tính được tổng cộng của bất kỳ công tác nào đó.
 
Dear all,
-------
Các bạn xem file đính kèm dưới đây. Không biết file có đáp ứng đúng yêu cầu của bạn CHP không?
 

File đính kèm

Thêm 1 đoạn Code nữa cho bạn lựa chọn.
Mã: 
Option Explicit
Public Function traDGCT(MaHieu, CongViec)
Dim i As Integer, row1 As Integer, row2 As Integer
Dim rngVungi As Range, rngTien As Range
Dim baseSheet As Worksheet
Set baseSheet = Sheets("Don gia chi tiet")
row1 = WorksheetFunction.Match(MaHieu, baseSheet.Range("B:B"), 0)
row2 = baseSheet.Cells(row1, "B").End(xlDown).Row - 1
'D la cot thanh phan hao phi
Set rngVungi = baseSheet.Range("D" & row1, "D" & row2)
'I la cot tien, ban co the tra cot don gia cung duoc
Set rngTien = baseSheet.Range("I" & row1, "I" & row2)
i = WorksheetFunction.Match(CongViec, rngVungi, 0)
traDGCT = rngTien.Cells(i, 1)
End Function
 
Đào Việt Cường đã viết:
-------
Các bạn xem file đính kèm dưới đây. Không biết file có đáp ứng đúng yêu cầu của bạn CHP không?
Nhấp chuột vào đây để mở rộng...
To Đào Việt Cường!
Kiểu này hiện nay tôi cũng đang sử dụng, nhưng mất rất nhiều thời gian vì có những gói thầu chúng tôi phải làm hàng trăm đầu việc (cá biệt có gói > 1000 công việc) nếu ngồi link từng công việc thì... )*&^) .
To NVSON!
VBA của bạn rất hay, có thể giải quyết được vấn đề nhưng cái VBA cũ thì ko thể tìm đc những cái TT (tạm tính).
Cái VBA mới thì hình như lại ko chay đc, không biết có thiếu cái jì ko nữa!
 
To NVSON !
Tôi đã làm đc rồi, cảm ơn bạn rất nhiều.
Nếu tôi tìm đơn giá thì tôi dùng cái VBA cũ của ban, nếu tìm vật tư, nhân công, máy..... thì tôi dùng cái VBA mới,
Một lần nữa cảm ơn bạn rất nhiều!

ah` NVSON có thể hướng dẫn tôi chuyển cái VBA thành add-in đc ko?
 
Chỉnh sửa lần cuối bởi điều hành viên:
CPH đã viết:
Nếu tôi tìm đơn giá thì tôi dùng cái VBA cũ của ban, nếu tìm vật tư, nhân công, máy..... thì tôi dùng cái VBA mới,
Nhấp chuột vào đây để mở rộng...
Tôi lại nhầm rồi.
2 cái này tìm giá trị hàng ngang như nhau, ko áp dụng để tìm đơn giá được vì đơn giá phải là ở hàng Giá trị dự toán xây lắp sau thuế.
Nhưng thôi đc rồi để tôi cho thêm 1 cột rồi tra mã từ cột này vậy.
Cảm ơn NVSON rất nhiều!
 
Mình giải quyết vấn để tổng quát, có nghĩa là nó sẽ tìm ô thoả mãn cả hai điều kiện của bạn, trước hết phải thoả mãn mã hiệu, sau đó mới tìm công việc nào đó có trong mã hiệu đó (ở bất kỳ hàng nào).
 
Dùng Vlookup trong VBA

Cells(3 + i, 5).Formula = "=VLookup((Cells(3 + i, 2).Value,gianhap,3,0)"
DG = Cells(3 + i, 5)
tìm trong gianhap lấy côt 3 theo Cells(3 + i, 2).Value (tenhang)
Các bạn chỉ giúp sao nó cứ báo lỗi, thêm hộ mấy dấu " và &

 
Bạn nên sử dụng Application.WorksheetFunction.Vlookup
 
ThuNghi đã viết:
Cells(3 + i, 5).Formula = "=VLookup((Cells(3 + i, 2).Value,gianhap,3,0)"
Nhấp chuột vào đây để mở rộng...
Dear ThuNghi,
-------------
Bạn thử làm thế này xem có dễ nhìn hơn không nhé:
Mã: 
Dim strMaHang As String
strMaHang = Cells(3 + i, 2).Value
Cells(3 + i, 5).Formula = "=VLookup(""" & strMaHang & """, gianhap, 3 , 0)"
 
Bạn tham khảo thêm code này nữa:
Mã: 
Cells(3 + i, 5).FormulaR1C1 = "=VLOOKUP(RC[-3], gianhap,3,0)"
 
Đây là ~ gì excel ghi cho mình:

Bạn tham khảo thêm cho &ui:
Mã: 
Sub GhiVLooKup()
'    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],ConGiap,2,FALSE)"
End Sub
 
Rất cám ơn, PhanTuHuong, Đào Việt Cường và NVSON, nhiều lúc tôi máy móc quá và luôn mong các bạn vẫn nhiệt tình. Tôi hỏi để làm giúp cho bạn BHYT trên GPE
 
Nhân đây cũng nhắc nhở các bạn hay sử dụng các hàm tham chiếu là nguồn tham chiếu phải cùng một kiểu dữ liệu (text hay number). Thậm chí để cho dễ dàng hơn trong tổng hợp thì cũng nên cùng độ dài (nếu là text)

Như VD ở trên, bảng tham chiếu của bạn đã dùng 2 kiểu dữ liệu là Text (M2; M4) và number(64; 74). Trong khi đó hàm Right lại trả về một text; như vậy là khi tham chiếu đến nguồn thì sẽ bị báo lỗi ngay (số 64 khác với ký tự 64)
 
bạn thử tải file này thử xem nhé !

ban mo ra xem nhe!
 

File đính kèm

anhchanghamhoc đã viết:
mo file xem!
Nhấp chuột vào đây để mở rộng...

À Anh Sơn chỉ tính Đĩa mềm 1.2 MB thôi, chưa tính Đĩa mềm 1.4 MB đó mà.
Nếu cộng lại thì có khác chi đâu. chẳng qua Anh sơn đọc đề chưa kỹ đó mà.
 
Thử xem bạn, ở đây nè
http://www.bygsoftware.com/Excel/VBA/vlookupx.htm





VLOOKUPX​


Look up on multiple fields with this alternative to VLOOKUP​

Uses the two worksheet functions INDEX and MATCH within the VBA Evaluate method. Use any columns in any order, from the source table, for the lookup values.​

Purpose
Look up a value based on up to three columns of a table.


Download

oooo
SyntaxVLOOKUPX(lookup_value1,column_value1,lookup_value2,
column_value2,lookup_value3,column_value3,table_array,col_index_num) lookup_value1 is found in column_value1 of the table array lookup_value2 is found in column_value2 of the table array lookup_value3 is found in column_value3 of the table array table_array is the table of information in which data is looked up. Use a reference to a range or a range name col_index_num is the column number in table_array from which the matching value must be returned RemarksCan use one, two or all three arguments (see examples) Based on[FONT=Arial, sans-serif]http://support.microsoft.com/support/kb/articles/q59/4/82.asp[/FONT]
oooo
Here's what the output from VLOOKUPX looks like:
vlooku3.gif



​
 
To anhchanghamhoc:
Thực sự thì mình hay lập trình trên VB. Mình nghĩ sách VB bằng tiếng Việt thì rất nhiều (bạn có thể tìm cuốn Microsoft Visual Basic 6.0 & lập trình cơ sở dữ liệu của tác giả Nguyễn thị Ngọc Mai chủ biên - NXB giáo dục 2000 và rất nhiều tài liệu học tập VB, VBA trên diễn đàn này...). Khi đã hiểu VB rồi thì lập trình trên VBA cũng không khó lắm.
Mình chỉ có mỗi cuốn "Programming Excel with VBA and .NET", hình như trên diễn đàn cũng có quyển này rồi.
Chúc bạn thành công!
 
Các bác chỉ giúp em làm sao để biến các giá trị #N/A (không tìm thấy) thành giá trị 0 ...khi sử dụng Vlookup?
tks!
 
Davidsy đã viết:
Các bác chỉ giúp em làm sao để biến các giá trị #N/A (không tìm thấy) thành giá trị 0 ...khi sử dụng Vlookup?
tks!
Nhấp chuột vào đây để mở rộng...
Bạn sử dụng thêm hàm IsError:
VD:
Mã: 
=IF(ISERROR(VLOOKUP(E9,B5:C9,2,False)),0,VLOOKUP(E9,B5:C9,2,False))
 
anhchanghamhoc đã viết:
a` nhung neu co truong hop 1m6,1m8,1m10 nua thi` sao?
neu lam vay cong thu'c se qua' dai`,minh nghi la` phai lam cho cong thuc to'i u'u ma` thoi
Nhấp chuột vào đây để mở rộng...

bạn xem qua nhé, bạn sẽ không cần phải định dạng theo text nữa, cứ để nó tự nhiên. Text cũng được mà số cũng được. Bạn thêm vào cái mã gì cũng được

Tất nhiên, để chuyên nghiệp hóa hơn và để an toàn hơn, bạn nên cho tất cả mã chuyển sang dạng text (cùng độ dài càng tốt)
VD : 1M2; M64; M74; 1M6; 1M8 . . . . .

Chúc vui vẻ
Thân !
 

File đính kèm

azzura đã viết:
Chào các bạn,
Tôi có một thắc mắc về cách sử dụng hàm VLOOKUP khi cần phải dùng đến cả 2 tham chiếu. Ví dụ:
---- A ---------- B ----------- C ---------
1 Hàng Ngày bán Số lượng
2 Áo 10/11 12
3 Áo 12/11 5
4 Quần 13/11 7
........

Và tôi muốn tra Vlookup trong ngày 10/11, món hàng Áo bán được mấy cái. Ở đây tôi buộc phải dùng một mẹo nhỏ để tra, tôi thêm 1 cột nữa là kết hợp giữa "Hàng" và "Ngày bán":

---- A ---------- B ----------- C --------------- D ----
1 Hàng Ngày bán Mapping Số lượng
2 Áo 10/11 "=A2&Day(B2)" 12
3 Áo 12/11 "=A3&Day(B3)" 5
4 Quần 13/11 "=A4&Day(B4)" 7
........

Sau đó VLOOKUP thì dùng cột mapping này để tra cứu.

Tôi có gửi file đính kèm. Vậy có ai biết cách dùng như thế nào mà không cần phải thêm một cột mapping như thế k, xin chỉ giáo.
Cảm ơn.
Nhấp chuột vào đây để mở rộng...

Chào Bạn ,

Trong trường hợp này không nhất thiết phải dùng thêm hàm DAY() thì công thức sẽ đơn giản hơn


Thanks
 
+ Bạn không cần phải định dạng gì cả, ta dùng hàm IF kết hợp với hàm MID để xét:
=IF(MID(C5,4,1)="M",VLOOKUP(RIGHT(C5,2),$E$18:$F$214,2,0),VLOOKUP(VALUE(RIGHT(C5,2)),$E$18:$F$214,2,0))
+ Phần tính tổng dùng hàm SUMIF bình thường.
 
Lần chỉnh sửa cuối:
Vlookup Báo Lỗi #n/a???

Cho mình hỏi thêm 1 vấn đề về hàm vlookup:

Nếu mình dò tìm 1 đk mà đk này ko có trong bảng cần dò (do chưa cập nhật danh sách kịp) thì nó báo #N/A, nên mình không thể tính tổng được. các bạn có "chiêu" gì để khắc phục ko vậy giúp với!!!!!!


Thanks&&&%$R
 
Bạn có thể sử dụng thêm hàm ISNA :

Thí dụ :

= =IF(ISNA(VLOOKUP(E9,B5:C9,2,False)),0,VLOOKUP(E9,B5:C9,2,False))
 
hoặc bạn sử dụng thêm 1 hàm logic nữa để kiểm tra vi dụ như sử dụng câu lệnh sau
=IF(COUNT(VLOOKUP(giá trị kiểm tra,vùng dò kiểm tra,vùng lấy giá trị)>=1),VLOOKUP(giá trị kiểm tra,vùng dò kiểm tra,vùng lấy giá trị),0)
Như vậy nếu gặp giá trị chưa có thì kết quả luôn bằng 0 và không ảnh hưởng đến phép cộng của bạn.
 
Theo minh nhu ham sau cung duoc= IF(ISBLANK(giá trị kiểm tra);"";ROUND(VLOOKUP(giá trị kiểm tra;vùng kiểm tra;hàng kiẻm tra;0);0))
 
Trong tất cả các cách các bạn bàn luận, đều không đúng.
=IF(COUNT(VLOOKUP(E5,B5:C7,2,0))>=1,VLOOKUP(E5,B5:C7,2,0),0)
Nhấp chuột vào đây để mở rộng...
Hàm này chỉ đúng khi giá trị dò tìm cho ra kết quả số. Trong thí dụ trên,giá trị E5 khi dò tìm trả về kết quả là số tại cột C của mảng B5:C7. Nếu cột C có các kết quả là Text, sẽ trả về số 0

Trần Văn Bình đã viết:
Theo minh nhu ham sau cung duoc= IF(ISBLANK(giá trị kiểm tra);"";ROUND(VLOOKUP(giá trị kiểm tra;vùng kiểm tra;hàng kiẻm tra;0);0))
Nhấp chuột vào đây để mở rộng...

Tại trường hợp này, nếu E5 là trống, không có giá trị, thì kết quả là số 0. Nhưng nếu E5 có giá trị, mà giá trị này không có trong cột B của mảng B5:C7, thì kết quả vẫn là lỗi #N/A.

Do đó, chỉ có 2 hàm được sử dụng khử lỗi #N/A là hàm ISNA() và hàm ISERROR() mà thôi
 
có 2 hàm được sử dụng khử lỗi #N/A là hàm ISNA() và hàm ISERROR()
Nhấp chuột vào đây để mở rộng...
(òn hàm =IF(TYPE(VLOOKUP(...;...;...;...))= 16; "" ; VLOOKUP(...;...;...;...)) có thể xài được?!?
(ác bạn bàn thêm xem nên sao, nếu hàm VLOOKUP() trả về lỗi thì nên thể hiện số 0 hay nên là ""; Mình e rằng ngay bản thân hàm VLOOKUP() cũng trả về giá trị 0 thì sẽ sinh ra việc ngộ nhận hay không?!
 
Lần chỉnh sửa cuối:
handung107 đã viết:
Trong tất cả các cách các bạn bàn luận, đều không đúng.

Hàm này chỉ đúng khi giá trị dò tìm cho ra kết quả số. Trong thí dụ trên,giá trị E5 khi dò tìm trả về kết quả là số tại cột C của mảng B5:C7. Nếu cột C có các kết quả là Text, sẽ trả về số 0



Tại trường hợp này, nếu E5 là trống, không có giá trị, thì kết quả là số 0. Nhưng nếu E5 có giá trị, mà giá trị này không có trong cột B của mảng B5:C7, thì kết quả vẫn là lỗi #N/A.

Do đó, chỉ có 2 hàm được sử dụng khử lỗi #N/A là hàm ISNA() và hàm ISERROR() mà thôi
Nhấp chuột vào đây để mở rộng...
Một bài toán luôn có nhiều cách giải không nhất thiết phải luôn theo 1 cách của thầy giáo?? Trong yêu cầu giải quyết không biết bạn đọc có kỹ không chứ người yêu cầu nêu vấn đề nếu giá trị không có làm xuất hiện lỗi #N/A thì không tính tổng được. Vậy nên không thể nói là bàn luận không đúng được.
 
Đúng, bạn nói rất đúng, nhưng khi nói về khử lỗi #N/A,nếu bạn hướng dẫn cho người mới bắt đầu, thì hàm ISNA() là tổng quát nhất và phù hợp nhất cho mọi trường hợp. Còn mọi người tự khám phá thêm là cách của riêng mỗi người. Dù sao, cách của bạn cũng là một ý kiến cho các bạn khác tham khảo thêm
 
Thanks các bạn. Nhung công thức của mình đã dài rồi mà giờ lai dung hàm IF nữa thì báo lỗi, Ý mình nói là dữ liệu trong vùng tìm thiếu chứ không phải cột tìm thiếu: VD

Vlookup(A1,C2:E10,2,0) . Giá trị A1 ko có trong khối cần tìm C2:E10 nên báo lỗi #N/A --> ko tính tổng được.

Sau đây là công thức thực tế của mình:
=IF($F$4="MIEN BAC",VLOOKUP(A6,MIENBAC,2,0),
IF($F$4="MIEN TRUNG",VLOOKUP(A6,MIENTRUNG,2,0),
IF($F$4="MIEN DONG",VLOOKUP(A6,MIENDONG,2,0),
IF($F$4="HCM",VLOOKUP(A6,HCM,2,0),
IF($F$4="MEKONG 1",VLOOKUP(A6,MK1,2,0),
IF($F$4="MEKONG 2",VLOOKUP(A6,MK2,2,0),0))))))

"MIEN BAC" là text cần so sánh, MIENBAC là khối dữ liệu cần tìm
mình
 
Thanks các bạn. Nhung công thức của mình đã dài rồi mà giờ lai dung hàm IF nữa thì báo lỗi, Ý mình nói là dữ liệu trong vùng tìm thiếu chứ không phải cột tìm thiếu: VD

Vlookup(A1,C2:E10,2,0) . Giá trị A1 ko có trong khối cần tìm C2:E10 nên báo lỗi #N/A --> ko tính tổng được.

Sau đây là công thức thực tế của mình:
=IF($F$4="MIEN BAC",VLOOKUP(A6,MIENBAC,2,0),
IF($F$4="MIEN TRUNG",VLOOKUP(A6,MIENTRUNG,2,0),
IF($F$4="MIEN DONG",VLOOKUP(A6,MIENDONG,2,0),
IF($F$4="HCM",VLOOKUP(A6,HCM,2,0),
IF($F$4="MEKONG 1",VLOOKUP(A6,MK1,2,0),
IF($F$4="MEKONG 2",VLOOKUP(A6,MK2,2,0),0))))))

"MIEN BAC" là text cần so sánh, MIENBAC là khối dữ liệu cần tìm. Giả sử mình qui định cả khối trên là A thì mình đã làm như sau:
If(A>0,A,0) thì nó báo lỗi, ko hiểu tại sao???
 
Mình không nghĩ là bạn cần thiết phải làm một công thức dài như vậy. Toàn bộ các khối dữ liệu cần tìm kiếm bạn có thể cho vào một khối (VD MaVung). Nhu vậy việc quan trọng ở đây là đặt mã cho phù hợp để tránh trùng thôi. VD Miền Bắc bắt đầu từ MB, Miền nam bắt đầu từ MN. . . .
Khi đó công thức của bạn chỉ còn là
=VLOOKUP(A6,MaVung,2,0).

Có thể khi công thức không tìm thấy giá trị thì nó sẽ báo lỗi.

Vậy thì bạn nên sử dụng các PP sau để tránh lỗi :

- Tại ô A6 dùng Validation với List = MaVung để đảm bảo rằng tất các các giá trị của A6 đều lấy từ MaVung
- Dùng PP : Nếu hàm bị lỗi thì trả về giá trị nào đó :
=if(iserror(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP(A6,MaVung,2,0),"Khong co gi tri nay") : Nếu tìm thấy thì sẽ tiến hành bình thường; nếu bị lỗi thì sẽ trả về chuỗi ký tự thông báo trên.
=if(isNA(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP(A6,MaVung,2,0),"Khong co gi tri nay")
Nếu tìm thấy thì sẽ tiến hành bình thường; nếu bị lỗi (không tìm thấy)thì sẽ trả về chuỗi ký tự thông báo trên.

Hai trường hợp bị lỗi có khác nhau :
Vì hàm VLOOKUP bị lỗi ở 3 trường hợp :
1. Số cột trong công thức <1 : trả về #VALUE!
2. Số cột trong công thức > số cột bảng tham chiếu : trả về #REF!
3. Khi không tìm thấy giá trị thỏa mãn : trả về #N/A
Nếu dùng iserror thì loại trừ cả 3 trường hợp lỗi. Còn nếu dùng IsNA thì chỉ loại được trường hợp thứ 3.
. . . . . . . . . .
Mong bạn suy xét kỹ.

Còn công thức của bạn :
if(A>0;A;)) -->> if(iserror(A>0)=false;A;0)

Thân!
 
Lần chỉnh sửa cuối:
Thanks nhieu nhé
đúng là mình còn yếu quá
 
A vậy vấn đề lại khác, bạn thử tìm hiểu theo cách sau:
Sử dụng hàm Vlookup lồng với hàm Indirect trong câu lệnh chính để dò tìm.
Sử dụng hàm ISNA để khử lỗi #N/A (đúng bài bản rồi đấy bác handung107)
Các giá trị để dò tìm đưa vào 1 bảng riêng và bạn nhớ đặt tên để sau này dễ sử dụng (MIEN BAC, MIEN TRUNG, MIEN DONG...)
Các vùng để lấy giá trị đưa vào một bảng riêng và đặt tên luôn nha.
Như vậy câu lệnh chính dò giá trị sẽ như sau:
VLOOKUP(A6,INDIRECT(VLOOKUP(F4,vungdo,2,0)),2,FALSE)
lồng với hàm bắt lỗi ISNA sẽ trở thành hoàn chỉnh như sau:
IF(ISNA(VLOOKUP(A6,INDIRECT(VLOOKUP(F4,vungdo,2,0)),2,FALSE)),0,VLOOKUP(A6,INDIRECT(VLOOKUP(F4,vungdo,2,0)),2,FALSE))
với vungdo là tên bảng chứa các text cần do và các tên bảng khối dữ liệu cần tìm.
như vậy bạn thấy đấy câu lệnh được rút ngắn tối đa và giá trị kiểm tra của bạn là tuỳ thích (muốn bao nhiêu cũng được) chứ không giới hạn 7 như khi bạn sử dụng hàm IF để dò giá trị.
Mình muốn upload lên file Excel có ví dụ nhưng không biết làm cách nào. Nếu bạn cần thì mình sẽ email cho bạn
 
MrHieu đã viết:
Mình không nghĩ là bạn cần thiết phải làm một công thức dài như vậy. Toàn bộ các khối dữ liệu cần tìm kiếm bạn có thể cho vào một khối (VD MaVung). Nhu vậy việc quan trọng ở đây là đặt mã cho phù hợp để tránh trùng thôi. VD Miền Bắc bắt đầu từ MB, Miền nam bắt đầu từ MN. . . .
Khi đó công thức của bạn chỉ còn là
=VLOOKUP(A6,MaVung,2,0).

Có thể khi công thức không tìm thấy giá trị thì nó sẽ báo lỗi.

Vậy thì bạn nên sử dụng các PP sau để tránh lỗi :

- Tại ô A6 dùng Validation với List = MaVung để đảm bảo rằng tất các các giá trị của A6 đều lấy từ MaVung
- Dùng PP : Nếu hàm bị lỗi thì trả về giá trị nào đó :
=if(iserror(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP(A6,MaVung,2,0),"Khong co gi tri nay") : Nếu tìm thấy thì sẽ tiến hành bình thường; nếu bị lỗi thì sẽ trả về chuỗi ký tự thông báo trên.
=if(isNA(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP(A6,MaVung,2,0),"Khong co gi tri nay")
Nếu tìm thấy thì sẽ tiến hành bình thường; nếu bị lỗi (không tìm thấy)thì sẽ trả về chuỗi ký tự thông báo trên.

Hai trường hợp bị lỗi có khác nhau :
Vì hàm VLOOKUP bị lỗi ở 3 trường hợp :
1. Số cột trong công thức <1 : trả về #VALUE!
2. Số cột trong công thức > số cột bảng tham chiếu : trả về #REF!
3. Khi không tìm thấy giá trị thỏa mãn : trả về #N/A
Nếu dùng iserror thì loại trừ cả 3 trường hợp lỗi. Còn nếu dùng IsNA thì chỉ loại được trường hợp thứ 3.
. . . . . . . . . .
Mong bạn suy xét kỹ.

Còn công thức của bạn :
if(A>0;A;)) -->> if(iserror(A>0)=false;A;0)

Thân!
Nhấp chuột vào đây để mở rộng...
theo như mình thì bạn đã bỏ sót 1 giá trị kiểm tra vì như yêu cầu thì kiểm tra giá trị tên vùng, nếu đúng giá trị tên vùng thì lấy giá trị A6 dò tìm trong vùng tên vừa tìm được. Như vậy ở đây bắt buộc dò tìm 2 lần.
Bạn thử kiểm tra xem.
Thân!
 
Morning!

Mình rất cám ơn vì sự giúp đỡ nhiệt tình của các bạn.
Các bạn cho mình hỏi thêm: có cách nào thực hện công việc (CV) sau ko nhé.
=nếu A1 có mặt trong vùng B1:E10 thì thực hiện CV1, ngược lại thì thực hiện CV2.
A1 là text (VD: tên tỉnh...) có trong danh sách list tỉnh của khối B1:E10.
Có hàm nào để so sánh như vậy ko hén.

Thanks&&&%$R
 
Bạn thử dùng hàm sau:
Mã: 
=IF(OR(NOT(ISERROR(MATCH(A1,B1:B10,0))),NOT(ISERROR(MATCH(A1,C1:C10,0)))),"CV1","CV2")
 
vuphuocha đã viết:
theo như mình thì bạn đã bỏ sót 1 giá trị kiểm tra vì như yêu cầu thì kiểm tra giá trị tên vùng, nếu đúng giá trị tên vùng thì lấy giá trị A6 dò tìm trong vùng tên vừa tìm được. Như vậy ở đây bắt buộc dò tìm 2 lần.
Bạn thử kiểm tra xem.
Thân!
Nhấp chuột vào đây để mở rộng...

Đề bài của bạn ngovietct như sau :
Nếu F4 = ……. Thì tìm kiếm giá trị tương ứng của ô A6 trong vùng dò. . . .

Do vùng dò của bạn ngovietct không đồng nhất (MIENBAC; MIẺNTRUNG; MIENNAM. . . ) nên bạn ấy phải sử dụng rất nhiều hàm if (tương ứng với số vùng dò)
Do đó mình đề nghị bạn ngovietct gộp tất cả các vùng dò đó vào một vùng (VD : MAVUNG), khi đó mới áp dụng những công thức như trên mình nêu ra.
Vì mình nghĩ rằng giá trị ở F4 bạn ngovietct lập ra nhằm làm cho hàm if , nghĩa là giá trị A6 lúc nào cũng tồn tại để dò (nếu không có là do chưa cập nhật kịp) . Do đó khi đồng nhất các vùng dò lại thì cột F lại không cần thiết nữa, vì thế có thể xóa cột F đi.

Có thể không đúng ý bạn ngovietct !?!
Thân!
 
ngovietct đã viết:
Morning!

Mình rất cám ơn vì sự giúp đỡ nhiệt tình của các bạn.
Các bạn cho mình hỏi thêm: có cách nào thực hện công việc (CV) sau ko nhé.
=nếu A1 có mặt trong vùng B1:E10 thì thực hiện CV1, ngược lại thì thực hiện CV2.
A1 là text (VD: tên tỉnh...) có trong danh sách list tỉnh của khối B1:E10.
Có hàm nào để so sánh như vậy ko hén.

Thanks&&&%$R
Nhấp chuột vào đây để mở rộng...


Bạn thử xem nhé

If(countif(B1:E10;A1)>0;CV1;CV2)
Thân!
 
Chào MrHieu
Mình vẫn bảo lưu ý kiến củ của mình. Do yêu cầu của ngovietct là lấy giá trị ở A6 dò tìm trong vùng được tìm ra tại giá trị F4. Nên lời giải của bạn là thiếu. Vì theo như mình nhận định, tại mỗi vùng dò ra sau khi kiểm tra F4 đều có các giá trị khác nhau mặc dầu giá trị muốn tìm tại A6 là không đổi.
Vì nếu các giá trị sau khi kiểm tra tại F4 bằng nhau thì chẳng việc gì phải phân ra làm nhiều vùng để kiểm tra chi cho mệt và lúc đó chỉ cần lấy giá trị A6 đi dò trong 1 vùng (vùng này sẽ bao gồm tất cả các giá trị của các vùng còn lại) là được.
Bàn luận lý thuyết thì dài dòng, bạn cứ thử làm ví dụ đi rồi sẽ sáng tỏ liền.
Vd:(mình đang lấy số vùng <7 nhưng không được sử dụng hàm if lồng như yêu cầu của bạn ngovietct)
- có 3 vùng MB,MT,MN với MB có các giá trị AA=1,AB=2,AC=3; MT có các giá trị AA=2,AB=4,AC=6; MN có các giá trị AA=8,AB=16,AC=64.
Giá trị cần tìm là AA với yêu cầu tên vùng là ngẫu nhiên (sửa lại cho đúng ý : chọn trong các giá trị MB,MT,MN...)(giống như yêu cầu của ngovietct).
Vậy thì lời giải của bạn có áp dụng vào đây được không?
Thân chào bạn.
 
Lần chỉnh sửa cuối:
vuphuocha đã viết:
Chào MrHieu
Mình vẫn bảo lưu ý kiến củ của mình. Do yêu cầu của ngovietct là lấy giá trị ở A6 dò tìm trong vùng được tìm ra tại giá trị F4. Nên lời giải của bạn là thiếu. Vì theo như mình nhận định, tại mỗi vùng dò ra sau khi kiểm tra F4 đều có các giá trị khác nhau mặc dầu giá trị muốn tìm tại A6 là không đổi.
Vì nếu các giá trị sau khi kiểm tra tại F4 bằng nhau thì chẳng việc gì phải phân ra làm nhiều vùng để kiểm tra chi cho mệt và lúc đó chỉ cần lấy giá trị A6 đi dò trong 1 vùng (vùng này sẽ bao gồm tất cả các giá trị của các vùng còn lại) là được.
Bàn luận lý thuyết thì dài dòng, bạn cứ thử làm ví dụ đi rồi sẽ sáng tỏ liền.
Vd:(mình đang lấy số vùng <7 nhưng không được sử dụng hàm if lồng như yêu cầu của bạn ngovietct)
- có 3 vùng MB,MT,MN với MB có các giá trị AA=1,AB=2,AC=3; MT có các giá trị AA=2,AB=4,AC=6; MN có các giá trị AA=8,AB=16,AC=64.
Giá trị cần tìm là AA với yêu cầu tên vùng là ngẫu nhiên (giống như yêu cầu của ngovietct).
Vậy thì lời giải của bạn có áp dụng vào đây được không?
Thân chào bạn.
Nhấp chuột vào đây để mở rộng...

Thứ nhất tên vùng không ngẫu nhiên mà phải là : Hoặc MN; hoặc MB; hoặc . . . Tức là tại 1 ô thì hoàn toàn xác định
Thứ hai : Như mình đã nói ở trên :
Toàn bộ các khối dữ liệu cần tìm kiếm bạn có thể cho vào một khối (VD MaVung). Nhu vậy việc quan trọng ở đây là đặt mã cho phù hợp để tránh trùng thôi. VD Miền Bắc bắt đầu từ MB, Miền nam bắt đầu từ MN. . . .

Như vậy các mã sẽ biến thành
Mã………Giá Trị
MBAA….…1
MBAB…….2
MBAC…….3
MTAA….…2
MTAB…….4
MTAC…….6
MNAA….…8
MNAB…….16
MNAC…….64

Khi F4 = MienNam; A6 = AA
Thay vì phải lập công thức dài như vậy thì nếu làm theo cách của mình thì khi đó

A6=MNAA
Khi đó công thức sẽ là :
=if(iserror(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP( A6,MaVung,2,0),"Khong co gi tri nay") = 8.

Như thế ngắn gọn hơn nhiều.

Rất khó để nói rằng mình là đúng hoàn toàn, mình chỉ đưa ra những trường hợp để bạn ngovietct lựa chọn. Bởi thực ra file của bạn ngovietct như thế nào thì chưa biết, chỉ có mấy dòng thông tin chung nên việc hiểu sai đề là bình thường.
Rất cảm ơn bạn.
Thân!
 
MrHieu đã viết:
Thứ nhất tên vùng không ngẫu nhiên mà phải là : Hoặc MN; hoặc MB; hoặc . . . Tức là tại 1 ô thì hoàn toàn xác định
Thứ hai : Như mình đã nói ở trên :
Toàn bộ các khối dữ liệu cần tìm kiếm bạn có thể cho vào một khối (VD MaVung). Nhu vậy việc quan trọng ở đây là đặt mã cho phù hợp để tránh trùng thôi. VD Miền Bắc bắt đầu từ MB, Miền nam bắt đầu từ MN. . . .

Như vậy các mã sẽ biến thành
Mã………Giá Trị
MBAA….…1
MBAB…….2
MBAC…….3
MTAA….…2
MTAB…….4
MTAC…….6
MNAA….…8
MNAB…….16
MNAC…….64

Khi F4 = MienNam; A6 = AA
Thay vì phải lập công thức dài như vậy thì nếu làm theo cách của mình thì khi đó

A6=MNAA
Khi đó công thức sẽ là :
=if(iserror(VLOOKUP(A6,MaVung,2,0))=false,VLOOKUP( A6,MaVung,2,0),"Khong co gi tri nay") = 8.

Như thế ngắn gọn hơn nhiều.

Rất khó để nói rằng mình là đúng hoàn toàn, mình chỉ đưa ra những trường hợp để bạn ngovietct lựa chọn. Bởi thực ra file của bạn ngovietct như thế nào thì chưa biết, chỉ có mấy dòng thông tin chung nên việc hiểu sai đề là bình thường.
Rất cảm ơn bạn.
Thân!
Nhấp chuột vào đây để mở rộng...
Tôi đồng ý với điều chỉnh của bạn về điều thứ nhất. Tôi phải nói rõ nghĩa là tên vùng được chọn ngẫu nhiên từ những tên vùng có sãn.
Tiếp đến phải phân tích kỹ hơn với bạn về đề tài này vì những lý do sau:
1. Giá trị tại ô F4 là một giá trị bất kỳ (được chọn từ các tên vùng hoặc là một giá trị mới).
2. Giá trị tại ô A6 cũng là một giá trị bất kỳ (nhưng lại là giá trị con của các tập hợp tên vùng)
3. Vậy nên không thể đặt tên các mã như bạn thực hiện được (vì ở đây ngovietct đang thực hiện phép kiểm tra chéo để tìm giá trị thích hợp) nhưng bạn lại mặc nhiên qui định thành phép kiểm tra 1 chiều. Và việc gán mã như bạn tại ô A6 là một việc biết trước giá trị tại ô F4. Như vậy chỉ sử dụng cho 1 trường hợp cụ thể mà không mang tính tổng quát cho câu lệnh.
4. Giả sử các vùng có các giá trị từ A1...An (n<=1000) nhận các giá trị x1...xn và có khoảng từ m vùng trở lên không lẽ bạn cũng gán mã như vậy??. Điều này không thực tế trong cuộc sống.
Tóm lại theo tôi bạn vô tỉnh chuyển các giá trị tại các ô F4 và A6 từ các giá trị chưa biết sang các giá trị biết trước để thực hiện câu lệnh cho giá trị này và như vậy mỗi khi giá trị tại ô F4 thay đổi bạn cũng phải thay đổi câu lệnh cho việc dò tìm giá trị A6! Liệu như vậy có nên chăng.
Thân chào bạn.
 
vuphuocha đã viết:
Tôi đồng ý với điều chỉnh của bạn về điều thứ nhất. Tôi phải nói rõ nghĩa là tên vùng được chọn ngẫu nhiên từ những tên vùng có sãn.
Tiếp đến phải phân tích kỹ hơn với bạn về đề tài này vì những lý do sau:
1. Giá trị tại ô F4 là một giá trị bất kỳ (được chọn từ các tên vùng hoặc là một giá trị mới).
2. Giá trị tại ô A6 cũng là một giá trị bất kỳ (nhưng lại là giá trị con của các tập hợp tên vùng)
3. Vậy nên không thể đặt tên các mã như bạn thực hiện được (vì ở đây ngovietct đang thực hiện phép kiểm tra chéo để tìm giá trị thích hợp) nhưng bạn lại mặc nhiên qui định thành phép kiểm tra 1 chiều. Và việc gán mã như bạn tại ô A6 là một việc biết trước giá trị tại ô F4. Như vậy chỉ sử dụng cho 1 trường hợp cụ thể mà không mang tính tổng quát cho câu lệnh.
4. Giả sử các vùng có các giá trị từ A1...An (n<=1000) nhận các giá trị x1...xn và có khoảng từ m vùng trở lên không lẽ bạn cũng gán mã như vậy??. Điều này không thực tế trong cuộc sống.
Tóm lại theo tôi bạn vô tỉnh chuyển các giá trị tại các ô F4 và A6 từ các giá trị chưa biết sang các giá trị biết trước để thực hiện câu lệnh cho giá trị này và như vậy mỗi khi giá trị tại ô F4 thay đổi bạn cũng phải thay đổi câu lệnh cho việc dò tìm giá trị A6! Liệu như vậy có nên chăng.
Thân chào bạn.
Nhấp chuột vào đây để mở rộng...

Mình nghĩ rằng cả hai chúng ta càng ngày càng đi xa vấn đề mà bạn ngovietct đặt ra. Cái mà mình đưa ra cho bạn ngovietct là một cái nhìn mới về phương pháp sử dụng hàm VLOOKUP, nhưng chúng ta lại ngồi tranh luận với nhau về việc có (nên) tồn tại hay không giá trị ô F4. Nếu tồn tại thì cứ để như thế dò hay là tái cấu trúc lại bảng mã nguồn.

VD :
Có 3 vùng : MienNam; MienBac; MienTrung
Các tập con :
F4…………………A6…………………….Mã mới (VD)
MienNam: ………1;2;3;A;B………………MN(1;2;3;A;B) – 2 ký tự đầu tiên là MN
MienBac: ………..1;2;3;C;D………………MB(1;2;3;C;D)
MienTrung:………1;2;3;E;F………………MT(1;2;3;E;F)

Như vậy ta phải có 4 bảng mã nguồn để tham chiếu gồm 1 bảng MaVung; + 3 bảng tập con tương ứng với các mã vùng.
Khi nhập dữ liệu:
Khi F4 = MienNam thì tại ô A6 sẽ nhận các giá trị :1;2;3;A;B
Khi F4 = MienBac thì tại ô A6 sẽ nhận các giá trị :1;2;3;C;D
Khi F4 = MienTrung thì tại ô A6 sẽ nhận các giá trị :1;2;3;E;F

Như vậy các giá trị tại ô F4 và A6 là các giá trị đã biết trước (có biết thì ta mới nhập được chứ). VD như ta muốn lấy giá trị 2 của vùng MienNam -> Chọn : F4 = MienNam ->A6 = 2
Nhưng nếu theo bảng mã mới thì chỉ cần : A6 = MN2

Giả sử bạn có 100 vùng, mình không nghĩ rằng bạn sẽ tạo ra 100 bảng mã nguồn và 100 name tương ứng. Nếu thế thì không có gì để nói nữa. Trong khi đó bạn chỉ cần tạo ra 1 bảng mã duy nhất là được trong đó cần chú ý đến việc đặt mã.


Nếu vẫn cứ phải làm như bạn ngovietct và cần đúng câu cú như trên thì ta chỉ cần sử dụng thêm hàm INDIRECT là xong và sửa lại name MK1 => MEKONG 1; MK2 => MEKONG 2

=if(and(ISERROR(VLOOKUP(A6;INDIRECT($F$4);2;0))=FALSE;OR($F$4="MIEN BAC";$F$4="MIEN TRUNG";$F$4="MIEN DONG";$F$4="HCM";$F$4="MEKONG 1";$F$4="MEKONG 2"));VLOOKUP(A6;INDIRECT($F$4);2;0);0)
Thân!
 
Giúp tớ lập công thức

A B
1 ĐiểmTB Mã điểm
2 ? A01


A B C D
4 Mã điểm Toán Văn Trung
5 A01 10 8 7
.....
=VLOOKUP(B2,$A$4:$D$5,AVERAGE(?????),1)
Thanks!
 
Dear ngocxit000,
-----------------
A_________ B______ C______ D________E_______
4 Mã điểm__ Toán__ Văn___ Trung___ Trung bình
5 A01______ 10____ 8_____ 7_______ =AVERAGE($B5:$D5)
.....
A2=VLOOKUP($B2,$A$4:$E$5,5,0)
 
Có thể vận dụng
A B
1 ĐiểmTB Mã điểm
2 ? A01


A..........B...... C..... D
4 Mã điểm Toán Văn Trung
5 A01..... 10...... 8.... 7
....

A2=SUMPRODUCT(($A$5:$A$14=$B$2)*($B$5:$D$14))/3
 
Giúp em về hàm vlookup!

Phần câu hỏi em đã post trong file attack phần comment, các anh chị biết thì giúp em với, em đang rất cần.
Chúc các anh chị và diễn đàn ngày một đi lên.
 

File đính kèm

Bạn thử xem sao:

=IF(ISERROR(IF($C10>$F$4,VLOOKUP(B10,gia,3,0),VLOOKUP(B10,gia,2,0))),0,IF($C10>$F$4,VLOOKUP(B10,gia,3,0),VLOOKUP(B10,gia,2,0)))
 
Hãy dùng hàm ISNA(), phần help nói rất rõ.
 
To Shinec:
1./ /(hông nên trả về 'O' khi hàm gặp lỗi, vì rằng 'O' cũng có lúc là giá trị đúng mà hàm trả về! ( Mà nên trả về là khoảng trắng, dấu nháy đơn hay đôi hay vài kí tự khác).
2./ Ngoài 2 hàm mà các bạn # đã chỉ ra, mình khuyên bạn nghiên cứu mở rộng với hàm =Type()
VD:
=If(Type(VLOOKUP(LooKupV;CSDL ;iCol; 0)=16 ; "";VLOOKUP(LooKupV; CSDL ;iCol; 0))
(Bạn xem thêm trong fần Help!)
 
Bạn có thể dùng match & index :
=IF(ISERROR(MATCH(B7;MaHang;0))=FALSE;IF(C7>$F$4;INDEX(Gia2;MATCH(B7;MaHang;0);0);INDEX(Gia1;MATCH(B7;MaHang;0);0));0)

Thân!
 
Cái này mình dùng để tính tiền, nếu nó trả về #N/A * number nó cũng ra #N/A nên khi sum nó báo lỗi, trả về 0 thì *number nó cũng về 0, chẳng ảnh hưởng gì. Mình mới tập tành bên excel nên chỉ nghĩ đến đó, thời gian sau mình sẽ học hỏi thêm. Mong các bạn giúp đỡ!
 
Shinec đã viết:
Cái này mình dùng để tính tiền, nếu nó trả về #N/A * number nó cũng ra #N/A nên khi sum nó báo lỗi, trả về 0 thì *number nó cũng về 0, chẳng ảnh hưởng gì. Mình mới tập tành bên excel nên chỉ nghĩ đến đó, thời gian sau mình sẽ học hỏi thêm. Mong các bạn giúp đỡ!
Nhấp chuột vào đây để mở rộng...

Bạn làm như sau:
=IF(ISNA(VLOOKUP(B7,gia,IF(C7>$F$4,3,2),0)),0,VLOOKUP(B7,gia,IF(C7>$F$4,3,2),0))
 
(ó cách không dùng 3 hàm của excel:

Thay vì:
=IF(ISNA(VLOOKUP(B7,gia,IF(C7>$F$4,3,2),0)),0,VLOOKUP(B7,gia,IF(C7>$F$4,3,2),0))
Nhấp chuột vào đây để mở rộng...
hay:
=IF(TYPE(VLOOKUP(B5,C$2:E$28,3,0))=16,"0",VLOOKUP(B5,C$2:E$28,3,0))
ta có thể dùng hàm tự tạo sau:
Mã: 
[b]Function eError(formula As Variant, Show As String)[/b]
    On Error GoTo ErrorHandler
    If IsError(formula) Then eError = Show Else eError = formula
    Exit Function
ErrorHandler:           Resume Next
     
[b]End Function [/b]

/(hi đó cú pháp sẽ là:
=eError(VLOOKUP(B5,C$2:E$28,3,0),0)

(ũng chỉ để tham khảo cho &ui thôi nha!
 
Trời... cần gì phải "Đao to búa lớn" thế... Theo tôi thì: Cell mà bạn đang bị lỗi đang chứa công thức: =IF($C10>$F$4,VLOOKUP(B10,gia,3,0),VLOOKUP(B10,gia,2,0)))... Giờ chỉ cần thêm 1 cái IF nữa là xong: =IF(C10="",0,(IF($C10>$F$4,VLOOKUP(B10,gia,3,0),VLOOKUP(B10,gia,2,0))))
Bạn xem thử có giúp gì ko?
Chúc thành công nha!
ANH TUẤN
 
Dear all,
--------
Anh SQL làm Đao Phủ, các bạn không biết à! Nhưng mà quả Đao của anh í hôm nay không mài nên hơi... cùn thì phải:

Đao Phủ SQL đã viết:
Function eError(formula As Variant, Show As String)
On Error GoTo ErrorHandler
If IsError(formula) Then eError = Show Else eError = formula
Exit Function
ErrorHandler: Resume Next
End Function
Nhấp chuột vào đây để mở rộng...

- Nếu đã là Funtion thì nên khai báo kiểu dữ liệu cho nó, kể cả chưa biết kết quả đó sẽ là kiểu gì (thì khai báo nó As Variant).
Ở đây, sau khi khai đao và ... "chảm" Function eError sẽ trả về 1 chuỗi trên Formula nên có thể xác định eError As String.
- formula (nên đặt tên khác với thuộc tính) là một công thức nên nó có kiểu là một String. Show là giá trị trả về nếu formula IsError nhưng nó có khả năng tính toán được nên khai báo nó ở dạng số (Interger, Long...).
-...
(Hình như sắp lạc đề!)

Túm lại, nếu các bạn muốn làm nghề Đao Phủ giống như anh SQL thì chịu khó gọt rũa cho cái đao của mình từng li từng tí một.
Càng rũa bao nhiêu - càng sắc bấy nhiêu!
 
Có gì mà ko hiểu nhỉ? Công thức bạn ấy về cơ bản là đúng rồi. Chỉ vướng mắc ở 1 chổ là nó chỉ đúng khi cột C có dữ liệu, nếu cột C la rỗng thì công thức sẽ báo lỗi... Vì thế mà tôi thêm 1 hàm IF nữa đặt ở phía trước. Nghĩa là nếu cột C = rỗng thì cho ra kết quả = 0, ngược lại thì làm theo công thức cũ... Thế thôi
ANH TUẤN
 
em cần hỏi một vấn đề nhỏ ah, mong các bạn ai biết xin chỉ giúp.Thanks

Em ko biết hàm Vlookup, Hlookup va hàm IF có Ưu Điểm và hạn chế của mấy hàm đó là gì? Ai biết xin chỉ em, em đang rất cần biết rất gấp ah -\\/. . Cảm ơn nhiều -=.,, ... Tuni

______________

Bạn xem cách đặt tên đề bài tại đây nha!.
 
Chỉnh sửa lần cuối bởi điều hành viên:
imissu18 đã viết:
Em ko biết hàm Vlookup, Hlookup va hàm IF có Ưu Điểm và hạn chế của mấy hàm đó là gì? Ai biết xin chỉ em, em đang rất cần biết rất gấp ah -\\/. . Cảm ơn nhiều -=.,, ... Tuni
Nhấp chuột vào đây để mở rộng...
Ưu điểm thì nhiều vô kể; xài chỗ nào được thì là ưu điểm;
Hạn chế: HLOOPKUP() & VLOOKUP(): chỉ trả về 1 KQuả khi trong CSDL có tới vài KQuả tương tự;
Hàm IF() với excel <2004 thì chỉ được xài 7 IF trong 1 hàm mà thôi; Nhiều hơn bạn phải dùng ~ hàm khác, như CHOOSE() Hay hơn nữa là SWITCH() trong VBA (một khi trên 30 đối số vẫn chưa đủ cho bạn trong hàm CHOOSE() nêu trên!)
Đề nghị mọi người bổ sung cho thêm phần vui vẽ!
:=\+
 
Vâng đúng như bạn HYen17 vừa trình bày.

Mình chỉ xin đóng góp thêm là nếu cần nhiều các boolean test if thì nên xài Case trong VBA thì đơn giản và tha hồ có bao nhiêu if cũng được.

Mến
 
Hàm nào mà không trả về 1 kết quả. Hạn chế của Vlookup, Hlookup là phải tìm từ dòng đầu hay cột đầu (lookup_value). Rất hạn chế.
Còn hàm if thì như trên.
 
ThuNghi đã viết:
Hàm nào mà không trả về 1 kết quả. Hạn chế của Vlookup, Hlookup là phải tìm từ dòng đầu hay cột đầu (lookup_value). Rất hạn chế.
Còn hàm if thì như trên.
Nhấp chuột vào đây để mở rộng...

Mình thì hay dùng Index và Match thay cho Vlookup, mặt mạnh của anh này lại là nhược của anh kia:

Index + Match : Tìm kiếm 2 chiều, cột mã có thể nằm ở bất cứ đâu (phải, trái, giữa), Khi đặt name thì không quan tâm đến cột nằm chỗ nào, mà chỉ quan tâm đến name
VD : = Index(DMHHTen;Match(B1;DMHHMa;0);0)
Tuy nhiên ta lại phải đặt ra rất nhiều name cho từng cột
(Khổ 1 lần, sướng dài dài)
Vlookup : Chỉ cần 1 name duy nhất là toàn bộ bảng, công thức ngắn gọn (đặc biệt trong lập trình), tuy nhiên khi cột mã bao giừo cũng là đầu tiên, khi thay đổi thứ tự cột trong bảng thì công thức tham chiếu (chỉ cột thứ mấy) sẽ bị sai

Thân!
 
Status
Không mở trả lời sau này.

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

R
Các bác có kinh nghiệm excel cho em xin công thức đánh số thứ tự theo ngày tháng này vợi ạ. (5 người xem)
Trả lời
4
Đọc
48
HieuCD
H
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (3 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
3 4 5
Trả lời
436
Đọc
56K
hangiang
H
E
VBA copy ô trong excel khi đang lọc filter
Trả lời
5
Đọc
78
LOGANWover
L
N
Không sử dụng được phím tắt Ctrl + Shift + mũi tên xuống
Trả lời
1
Đọc
3K
aFdangthacmac
A
N
Nhờ giúp Drop down list theo tỉnh xã mới. (1 người xem)
Trả lời
5
Đọc
192
ongke0711
ongke0711
L
  • Question Question
Gõ và chọn tên trong ComboBox hiển thị giá trị trong textbox (2 người xem)
    • Cười
Trả lời
8
Đọc
359
ongke0711
ongke0711
H
Tự động cập nhật dữ liệu từ 2 file word
Trả lời
0
Đọc
31
hoangquochuy
H
befaint
[ Python và ứng dụng ] Công cụ trích xuất hàng loạt hóa đơn điện tử XML vào bảng Excel
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
56
Đọc
9K
Hoale85
H
Nam168
Không mở được file excel (1 người xem)
Trả lời
2
Đọc
57
Nam168
Nam168
V
Quản lý xuất nhập vật tư trong kho
Trả lời
14
Đọc
520
LOGANWover
L
phuongnam366377
Sử dụng COM DLL **không cần đăng ký** với Delphi 12 / C++ Builder
    • Thích
Trả lời
0
Đọc
70
phuongnam366377
phuongnam366377
N
Định mức nguyên vật liệu
Trả lời
49
Đọc
5K
ntan50
N
H
  • Question Question
Dò tìm giá trị từ bảng khác theo cột (1 người xem)
Trả lời
6
Đọc
284
Chả_biết_gì
C
2
  • Question Question
Hỏi cách in phiếu điều tra PCGD-XMC từ file dữ liệu mẫu xuất ra từ hệ thống PCGD-XMC của Bộ GD
    • Thích
Trả lời
26
Đọc
1K
haitien123
H
ongke0711
Truy cập trang GPE chậm
    • Thích
Trả lời
3
Đọc
189
Hai Lúa Miền Tây
Hai Lúa Miền Tây
V
Gộp File Từng File Excel vào 1 File (1 người xem)
Trả lời
16
Đọc
478
ptm0412
ptm0412
Gà Con yêu VBA
Mở Pass file excel phiên bản .xlsx , xlsm, xlsb
Trả lời
10
Đọc
789
Zizi One
Zizi One
I
Phân bổ số lượng xuất kho trong kỳ theo từng mã nguyên vật liệu
Trả lời
6
Đọc
205
IrisQ
I
L
Các bạn giúp mình hoàn thiện công thức hàm sumif nhé
Trả lời
5
Đọc
168
lequang_qn
L
N
Xin file tính khẩu phần ăn cho trường mầm non
Trả lời
5
Đọc
6K
HUONGHCKT
H
Xem thêm…

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

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom