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

[nokiano258vn]

Viết VBA cho hàm Vlookup

Bác nào biết code của hàm Vlookup viết bằng VBA không, hình như tôi đã đọc được ở đâu đó trên diễn đàn nhưng giờ tìm lại không thấy. Tôi cảm ơn nhiều

 

[Mr Okebab]

Bác nào biết code của hàm Vlookup viết bằng VBA không, hình như tôi đã đọc được ở đâu đó trên diễn đàn nhưng giờ tìm lại không thấy. Tôi cảm ơn nhiều

Bạn tham khảo ở đây nhé, nó sẽ tiện lợi hơn Vlookup nhiều.
http://tinyurl.com/2zqcxf
Thân!

 

[Thuyan.acc]

1.-Trong dữ liệu của sheet 1, cột F là cột do kế toán thiết lập và định khoản.
Trong đó có phần định khoản 3387 phân ra chi tiết nhiều như 33870010,33870020,33870030,... Nhưng lúc em viết bài này lên thì em đã thay tất cả 33870010,33870020,33870030 bằng 3387 để khỏi chia ra nhiều cột 3387 ở sheet 2 (Cột L - sheet 2).


​

Qua ứng dụng hàm =sumproduct em có thể kết hợp cùng với hàm right được chứ. Em muốn để nguyên dữ liệu của sheet 1 có các tài khoản 33870010,33870020,33870030,... (không thay 3387 cho sheet 1 như trước đây).
Công thức hàm =sumproduct em sử dụng kết hợp với hàm right như sau :


=SUMPRODUCT(--((Sheet1!$B$2:$B$82)=Sheet2!$B5)*(Sheet1!right($F$2:$F$82,4)=Sheet2!I$4)*Sheet1!$G$2:$G$82) ===> Mày báo sai. Không rõ ở đây em thiếu sót cái gì nhờ anh chị chỉ hộ cho em.

2.- Tìm hiểu về kiểu dữ liệu khác biệt giữa 2 sheet : (Sheet 1 và sheet 2)


​

Ờ sheet 1( cột b có field name = aaaa) : Em muốn nhờ anh chị chỉ cho em thêm làm sao anh chị phân biệt được ngay kiểu dữ liệu ở cột aaaa này khác với cột b của sheet 2 vậy. Vì lúc em sử dụng hàm =MID(A2,2,5) cài cho cột b của sheet 1 đồng thời em sử dụng hàm Vlookup để cài cho sheet 2 bấy giờ kết quả nó cứ báo sai #N/A.Mặc dầu em đã định dạng cột b của 2 sheet này lại (format/cell/number/general) rồi vẫn báo sai.
Anh Phamnhukhang đã biến =value(MID(A2,2,5)) thành ra giá trị trở lại.
Lý do sao phải sử dụng hàm value ở đây. (Lý do mà em muốn Tìm hiểu về kiểu dữ liệu khác biệt giữa 2 sheet là vậy):
Chân thành cám ơn

 

[SoiBien]

1.-Trong dữ liệu của sheet 1, cột F là cột do kế toán thiết lập và định khoản.
Trong đó có phần định khoản 3387 phân ra chi tiết nhiều như 33870010,33870020,33870030,... Nhưng lúc em viết bài này lên thì em đã thay tất cả 33870010,33870020,33870030 bằng 3387 để khỏi chia ra nhiều cột 3387 ở sheet 2 (Cột L - sheet 2).




​

Qua ứng dụng hàm =sumproduct em có thể kết hợp cùng với hàm right được chứ. Em muốn để nguyên dữ liệu của sheet 1 có các tài khoản 33870010,33870020,33870030,... (không thay 3387 cho sheet 1 như trước đây).
Công thức hàm =sumproduct em sử dụng kết hợp với hàm right như sau :



=SUMPRODUCT(--((Sheet1!$B$2:$B$82)=Sheet2!$B5)*(Sheet1!right($F$2:$F$82,4)=Sheet2!I$4)*Sheet1!$G$2:$G$82) ===> Mày báo sai. Không rõ ở đây em thiếu sót cái gì nhờ anh chị chỉ hộ cho em.

bạn thử cái này:
=SUMPRODUCT(((Sheet1!$B$2:$B$82)=Sheet2!$B5)*(right(Sheet1!$F$2:$F$82,4)=Sheet2!I$4)*Sheet1!$G$2:$G$82)

2.- Tìm hiểu về kiểu dữ liệu khác biệt giữa 2 sheet : (Sheet 1 và sheet 2)



Ờ sheet 1( cột b có field name = aaaa) : Em muốn nhờ anh chị chỉ cho em thêm làm sao anh chị phân biệt được ngay kiểu dữ liệu ở cột aaaa này khác với cột b của sheet 2 vậy. Vì lúc em sử dụng hàm =MID(A2,2,5) cài cho cột b của sheet 1 đồng thời em sử dụng hàm Vlookup để cài cho sheet 2 bấy giờ kết quả nó cứ báo sai #N/A.Mặc dầu em đã định dạng cột b của 2 sheet này lại (format/cell/number/general) rồi vẫn báo sai.
Anh Phamnhukhang đã biến =value(MID(A2,2,5)) thành ra giá trị trở lại.
Lý do sao phải sử dụng hàm value ở đây. (Lý do mà em muốn Tìm hiểu về kiểu dữ liệu khác biệt giữa 2 sheet là vậy):

Cái này thì hơi rắc rối, làm riết thì quen, nhiều khi đúng với công thức này, nhưng lại không đúng với công thức kia, với những ô dữ liệu ta nhập vào bằng tay bình thường thì excel có thể tự động chuyển đổi kiểu dữ liệu của ô đó, cho nên nó có thể hiểu là text hoặc number, nhưng khi sử dụng các hàm có kiểu của dữ liệu trả vể được xác định (như hàm Mid, kiểu dữ liệu trả về chắc chắn là string) nên trong trường hợp này dùng format/cell sẽ không thay đổi được kiểu dữ liệu của dữ liệu được trả về của hàm mid, do đó phải dùng hàm Value để đổi kiểu dữ liệu.

 

[chinghia]

các bác tranh luận với nhau quá hả!!!!!excel mà, có nhiều chiêu thức để chúng ta hóa giải bài toán lắm.chỉ cần kết quả trả về đúng và công thức đơn giản, dể hiểu là tốt rồi các bác ạ.Tôi nghỉ trong trường hợp này ta xử dụng hàm ISNA hoặc ISERROR là tốt rồi vì hai hàm này dể xử dụng, dể hiểu cho các bạn mới bắt đầu nữa!!!!!

 

[chinghia]

trường hợp này đơn giản thôi mà.bạn chú ý rằng A01 trong trương hợp này 01 là text bạn nhé vì text kết hợp với number thì excel trả về kiểu dữ liệu là text.công thức của bạn là không sai, nhưng kết quả trả về là #N/A thì hàm này đã sai giá trị dò rồi đấy(lookup_value). bạn hãy chuyển 01,02,03...sang kiểu text nhé.để tránh sai sót trong trương hợp này khi nhập liệu cho 01,02,03... bạn nhập như sau:'01->kết quả bây giờ là 01 và kiểu dữ liệu luôn luôn là tẽt.
chúc thành công!!!!!

 

[tuanh1979]

Mình làm theo bài của các bạn mà không lấy được dữ liệu đúng yêu cầu. Trong form nhap khi kich vao cap nhat sẽ lấy đc tên hàng từ giá trị tham chiếu MSP nhập vảo cột tên hàng trong sheet tonghop. Giá trị mình lấy đc luôn luôn là Tên Hàng

 

[TERU]

hỏi về hàm vlookup!!

hi all

Teru muốn hỏi các bạn là dùng hàm vlookup trong trường hợp này như thế nào để tính ra đơn giá( vì ở đây có chia ra từng thời điểm thì đơn giá khác nhau)

Teru có gửi file đính kèm.......CÁc bạn xem và giúp Teru nha'!!
Thanks all
Im waiting for ur reply

 

[anhtuan1066]

Dùng công thức

=INDEX($B$13:$D$15,MATCH(D3,$A$13:$A$15,1),MATCH(LEFT(B3,1),$B$12:$D$12,0))

 

[TERU]

Hi all!

Xin hỏi có cách giải khác không vì Teru chưa học hàm Match !!
Thanks!

 

[anhtuan1066]

Dễ hiều nhất thì bạn dùng VLOOKUP kết hợp với IF:

=VLOOKUP(D3,$A$12:$D$15,IF(LEFT(B3,1)="B",2,IF(LEFT(B3,1)="D",3,4)),1)

Cẫn thận với công thức dc post lên.. ko hiều sao viết chử LEFT mà nó cứ trở thành LEF T
Khi bạn copy thì nhớ coi chừng vụ này nha!

 

[phamduylong]

Bác nào biết code của hàm Vlookup viết bằng VBA không, hình như tôi đã đọc được ở đâu đó trên diễn đàn nhưng giờ tìm lại không thấy. Tôi cảm ơn nhiều

Trong VBA Excel cho phép sử dụng một số hàm bảng tính như COUNT, COUNT, COUNTBLANK, COUNTIF, ..., trong đó có`VLOOKUP.
Để sử dụng các hàm này, phải khai báo Application.WorksheetFunction.Tên hàm(đối số, ...)
Các đối số sử dụng như là các đối số ngoài bảng tính.
Muốn biết Application.WorksheetFunction có những hàm nào, trong Module nhập Application.WorksheetFunction và dấu chấm ( . ) VBA sẽ hiện 1 ListBox liệt kê các hàm được phép sử dụng.

 

[ThuNghi]

Đã chỉnh lại lỗi 1 và 2 và listbox cho phép Multi Select.
- Chọn Multi Select thì Nút Ghi ghi tất cả các mục chọn vào danh sách.

Cám ơn Thầy nhiều lắm.
Khi em tăng mã hàng thành 1500 record thì chạy hết nổi luôn

Private Sub ....NhomHang()
For r = 2 To rc01
If UCase(Left(S01.Cells(r, 1), Len(NhomHang))) = NhomHang Then
HHList.List(HHList.ListCount - 1, 2) = S01.Cells(r, 2)
End If
Next
End Sub

HHList có thể chuyển thành HHArray(1 to rc01) được không? Chỉ lấy mã hàng thôi.
Em xin hỏi Thầy vấn đề khác như sau

Option Explicit
Public Sub ChRangeToArray()
Dim rngMyRange As Range, aaa As Long, bbb As Range

Dim MyArray1()
Dim MyArray2()
Dim i As Long, j As Long
Set rngMyRange = Range("a1:b10") 'NVSON
For i = 1 To rngMyRange.Rows.Count
If rngMyRange.Cells(i, 2) = "b" Then
j = j + 1
ReDim Preserve MyArray1(1 To j)
MyArray1(j) = rngMyRange.Cells(i, 1)
ReDim Preserve MyArray2(1 To j)
MyArray2(j) = rngMyRange.Cells(i, 2)
End If
Next i
'Range("E1").FormulaR1C1 = "=SUMIF(Myarray2,RC[-1],Myarray1)"
'MsgBox UBound(MyArray2())
aaa = WorksheetFunction.Sum(MyArray1)
MsgBox aaa
End Sub

Theo file kèm, em muốn công thức sau thi hành
Range("E1").FormulaR1C1 = "=SUMIF(Myarray2,RC[-1],Myarray1)"
Vì là aray nên chưa hiểu phải làm thế nào.

 

[TERU]

Hi Tuấn

Teru đã áp dụng hàm mà bạn chỉ thì đã đựoc kết quả như ý muốn....but Teru o hiểu cho lắm:
- thứ nhất là giá trị lookup_value. Teru biết đó là cột thời điểm( but thời điểm của bảng dò đâu có giống với cột ngày của bảng chính đâu ví du: ở bảng dò: là 01/01/2001,01/02/2001.01/03/2001.......còn ở bảng chính là 14/01/2001,.....,27/03/2001
- thứ hai đó là giá trị range_lookup, khi Teru học thì thầy dặn là giá trị này luôn luôn bằng 0, còn ở dây Tuấn làm là 1......
Teru có hai vấn đề thắc mắc...Tuấn giải thích dùm nhé!( mong là bạn hiểu ý Teru muốn hỏi)
Thanks!
Im waiting for ur reply

 

[anhtuan1066]

Tại vì ở đây là dò ko chính xác.. Bạn đễ ý thấy VLOOKUP(tri dò,bảng dò,cột dò, tham số) cái tham số cuối cùng này là 1... Đó là dò ko chính xác đấy! Nó áp dụng cho trường hợp bảng dò đã dc sắp xếp trước tăng dần hay giảm dần và trị dò thì ko = chính xác giá trị trong bảng dò (bảng dò của bạn đang sắp xếp tăng dần)... Vậy thì khi dò tìm, nếu ko tìm dc giá trị trong bảng, nó sẽ tìm cái nào gần nhất... Trong trường hợp này nó sẽ lấy cái gần nhất nhỏ hơn nó
VD: trị dò 10/1/2001 khi dò vào bàng, thì cái gần nhất nhỏ hơn nó chính là 1/1/2001... Đúng ko?
Nói chung CÁC HÀM DÒ TÌM ĐÂU CHỈ LÀ TÌM CHÍNH XÁC

khi Teru học thì thầy dặn là giá trị này luôn luôn bằng 0, còn ở dây Tuấn làm là 1......

Thầy dặn như thế rất có thể là do chương trình học chưa tới... hi.. hi.. Bạn ko tin cứ mở Help của hàm này sẽ thấy, đâu phải chỉ là 0
Còn như thầy ko chấp nhận thì bạn hảy làm ngược lại: HLOOKUP và dò tìm các ký tự B, G.. kết hợp với IF... (nếu ngày <1/2/2001 thì lấy dòng 2, nếu ngày <1/3/2001 thì lấy dòng 3, ngược lại lấy dòng 4)... Đại khái là thế thì sẽ tránh dc cái vụ 0, 1 này...
Công thức này đây sẽ giúp bạn tránh dc thầy la:

=HLOOKUP(LEFT(B3,1),$A$12:$D$15,IF(D3<$A$14,2,IF(D3<$A$15,3,4)),0)

Ha... ha...
ANH TUẤN

 

[TERU]

Hỏi về hàm vlookup(hlookup) tiếp!!!

heya.....lại về vấn đề về hàm vlookup-hlookup...bài này Teru ngồi suy nghĩ hoài but không biết làm thế nào cả?
Mong các bạn giúp dùm
Thanks all
Im waiting for ur reply
PS: Teru post sai ở cột trước thời điểm( bảng dò).....năm 2002 chứ không phải năm 2006

 

[anhtuan1066]

=if(mid(a4,5,1)="n",hlookup(left(a4,4),$g$20:$i$23,if(c4<$f$21,2,if(c4<$f$22,3,4)),0),hlookup(left(a4,4),$j$20:$l$23,if(c4<$f$21,2,if(c4<$f$22,3,4)),0))

 

[TERU]

Hi Tuấn!!
Teru có hai vấn đề thắc mắc đó là:
- Thứ nhất Tuấn có thấy rằng bạn đưa ra cái điều kiện c4<...là vô lý hay ko??? ( mọi giá tri trong cột ngày(bảng chính- 2006) đều lớn hơn các giá trị trong cột trước thời điểm(bảng dò-2002)------> Teru đã note vô bài post là Teru đánh đề bài sai..Tuấn coi kỹ lại nhé!!!!
- Thứ hai là teru muốn hỏi là dòng nhận giá trị là 3,4,5 mới đúng chứ.....chứ sao lại là 2,3,4( vì đơn giá nhập/xuất + mã hàng đả chiếm 2 dòng rồi....????

 

[Mr Okebab]

heya.....lại về vấn đề về hàm vlookup-hlookup...bài này Teru ngồi suy nghĩ hoài but không biết làm thế nào cả?
Mong các bạn giúp dùm
Thanks all
Im waiting for ur reply
PS: Teru post sai ở cột trước thời điểm( bảng dò).....năm 2002 chứ không phải năm 2006

Việc cần thiết không phải là mấy công thức mà là cách bố trí lại số liệu.
Các bố trí như của bạn rất không khoa học và gây khó cho việc làm báo cáo (số liệu và thẩm mỹ)
Cố gắng nhé

Thân!

 

[anhtuan1066]

Gì mà vô lý chứ... đễ ý lại xem... 20/10/2006 thì tất nhiên phải nhỏ hơn 30/10/2006... đúng ko nè... Còn chuyện bạn ghi sai đề thế nào đó thì sữa lại... công thức vẩn trên nguyên tắc đó thôi
Dòng 2,3,4 hay 3,4,5 là tùy vào tôi chọn bảng phụ từ đâu... Tôi đâu có chọn 2 dòng tiêu đề: từ dòng 20 trờ xuống chứ có phải từ dòng 19 đâu nè... Nói chung cái này cũng tùy bạn luôn...
Mà nè... nếu bảng chính là 2006 còn bảng dò là 2002 thì làm sao mà tính dc.. có phải vô lý lắm ko? Vì nếu như thế thì cần quái gì IF nữa... toàn bộ ngày đều > bảng phu ??? Bạn nghĩ sao ? Tôi nghĩ nếu sữa thì sữa toàn bộ thành 2002, còn ko thì đễ nguyên 2006