PDA

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



Pages : [1] 2 3 4 5 6 7 8 9 10

songlon
14-07-06, 09:16 AM
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
06-09-06, 02:37 PM
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
07-09-06, 10:22 PM
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
08-09-06, 08:28 AM
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
08-09-06, 09:09 AM
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
09-09-06, 09:01 AM
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
10-09-06, 05:53 PM
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
04-11-06, 08:53 AM
Đề 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
04-11-06, 09:22 AM
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
04-11-06, 10:38 AM
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
04-11-06, 12:17 PM
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
05-11-06, 10:03 AM
Đâ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
05-11-06, 10:37 AM
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
05-11-06, 11:04 AM
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
06-11-06, 12:37 PM
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
06-11-06, 07:28 PM
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
07-11-06, 07:51 AM
Bạn sử dụng hàm SumIf là được.
Nếu không thì tải file sau nhé!

hunghuongus
07-11-06, 04:07 PM
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;d ata1;2;0);IF(ISNA(VLOOKUP(B4;data2;2;0))=FALSE;VLO OKUP(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
07-11-06, 05:35 PM
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
07-11-06, 05:40 PM
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.

azzura
08-11-06, 09:11 PM
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.

handung107
09-11-06, 07:12 AM
Bạn có thể dùng công thức mảng và hàm SUMPRODUCT.

Bài về hàm Sumproduct

http://www.giaiphapexcel.com/forum/showthread.php?t=59

Và bài này : Tạo các công thức để đếm và tính tổng

http://www.giaiphapexcel.com/forum/showthread.php?t=11

CPH
24-11-06, 09:33 AM
=sumproduct(($b$2:$b$4=c10)*($a$2:$a$4=d10)*($d$2: $d$4))

CPH
25-11-06, 11:10 AM
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ụ

nvson
25-11-06, 11:13 AM
Có chứ. Bạn kết hợp sử dụng hàm Index, Match, Quotient... là được

Đào Việt Cường
25-11-06, 12:51 PM
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!

nvson
25-11-06, 03:33 PM
Nhấn Alt+F11 để vào VBA. Nhấn tiếp Insert/Module rrồi thêm đoạn code dưới đây:


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:


=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.

CPH
27-11-06, 09:23 AM
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!

nvson
27-11-06, 09:29 AM
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!
Mình thấy nó chạy ổn đấy chứ!

CPH
27-11-06, 10:04 AM
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!

CPH
27-11-06, 10:42 AM
Mình thấy nó chạy ổn đấy chứ!
Pác NVSON làm ơn giúp cho chót.
Thanks

CPH
27-11-06, 11:00 AM
Nhấn Alt+F11 để vào VBA. Nhấn tiếp Insert/Module rrồi thêm đoạn code dưới đây:


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:


=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.
À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.

nvson
27-11-06, 12:00 PM
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é.


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 đó.

Đào Việt Cường
27-11-06, 01:45 PM
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?

nvson
27-11-06, 02:37 PM
Thêm 1 đoạn Code nữa cho bạn lựa chọn.


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

CPH
28-11-06, 09:48 AM
-------
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?
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!

CPH
28-11-06, 10:22 AM
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?

CPH
28-11-06, 10:52 AM
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,

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!

nvson
28-11-06, 11:00 AM
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).

ThuNghi
04-12-06, 03:02 PM
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à &

(http://www.giaiphapexcel.com/forum/images/vacation01/buttons/edit.gif)

PhanTuHuong
04-12-06, 03:19 PM
Bạn nên sử dụng Application.WorksheetFunction.Vlookup

Đào Việt Cường
04-12-06, 05:10 PM
Cells(3 + i, 5).Formula = "=VLookup((Cells(3 + i, 2).Value,gianhap,3,0)"

Dear ThuNghi,
-------------
Bạn thử làm thế này xem có dễ nhìn hơn không nhé:


Dim strMaHang As String
strMaHang = Cells(3 + i, 2).Value
Cells(3 + i, 5).Formula = "=VLookup(""" & strMaHang & """, gianhap, 3 , 0)"

nvson
05-12-06, 08:08 AM
Bạn tham khảo thêm code này nữa:


Cells(3 + i, 5).FormulaR1C1 = "=VLOOKUP(RC[-3], gianhap,3,0)"

SA_DQ
05-12-06, 08:39 AM
Bạn tham khảo thêm cho &ui:

Sub GhiVLooKup()
' Range("D4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],ConGiap,2,FALSE)"
End Sub

ThuNghi
05-12-06, 10:44 AM
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

Mr Okebab
11-12-06, 12:27 PM
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)

anhchanghamhoc
20-12-06, 01:29 PM
mo file nay xem nhe!

anhchanghamhoc
20-12-06, 01:40 PM
ban mo ra xem nhe!

Thien
20-12-06, 01:56 PM
mo file xem!

À 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à.

anhchanghamhoc
20-12-06, 02:23 PM
mo ra xem ban nhe!
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

iLike
20-12-06, 06:00 PM
Thử xem bạn, ở đây nè
http://www.bygsoftware.com/Excel/VBA/vlookupx.htm



[/URL]




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
[URL="http://www.bygsoftware.com/examples/zipfiles/vlookupX.zip"]vlookupx.zip (http://www.bygsoftware.com/default.htm)


oooo
SyntaxVLOOKUPX(lookup_value1,column_value1,lookup_ value2,
column_value2,lookup_value3,column_value3,table_ar ray,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 onhttp://support.microsoft.com/support/kb/articles/q59/4/82.asp (http://support.microsoft.com/support/kb/articles/q59/4/82.asp)
oooo
Here's what the output from VLOOKUPX looks like:
http://www.bygsoftware.com/Excel/VBA/vlooku3.gif

nvson
21-12-06, 05:01 PM
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!

Davidsy
25-12-06, 05:37 PM
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!

nvson
25-12-06, 05:47 PM
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!
Bạn sử dụng thêm hàm IsError:
VD:


=IF(ISERROR(VLOOKUP(E9,B5:C9,2,False)),0,VLOOKUP(E 9,B5:C9,2,False))

Mr Okebab
28-12-06, 04:04 PM
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

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 !

lehung99
16-01-07, 02:56 PM
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.

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

tranmanbt
16-01-07, 04:08 PM
+ 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(VAL UE(RIGHT(C5,2)),$E$18:$F$214,2,0))
+ Phần tính tổng dùng hàm SUMIF bình thường.

ngovietct
22-01-07, 09:22 AM
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

ruadangyeu
22-01-07, 10:04 AM
Cái này anh NVSON hướng dẫn rồi mà bạn vào đây
http://www.giaiphapexcel.com/forum/showthread.php?p=7174#post7174

handung107
22-01-07, 11:40 AM
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,B 5:C9,2,False))

vuphuocha
22-01-07, 01:05 PM
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.

Trần Văn Bình
22-01-07, 04:18 PM
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))

handung107
22-01-07, 04:41 PM
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)
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


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))

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

SA_DQ
22-01-07, 07:37 PM
có 2 hàm được sử dụng khử lỗi #N/A là hàm ISNA() và hàm ISERROR()
(ò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?!

vuphuocha
22-01-07, 10:17 PM
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
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.

handung107
22-01-07, 10:38 PM
Đú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

ngovietct
23-01-07, 07:40 AM
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

ngovietct
23-01-07, 07:42 AM
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???

Mr Okebab
23-01-07, 12:16 PM
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!

ngovietct
23-01-07, 01:32 PM
Thanks nhieu nhé
đúng là mình còn yếu quá

vuphuocha
23-01-07, 04:09 PM
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,FALS E)
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,vungd o,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

vuphuocha
23-01-07, 04:15 PM
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!

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!

ngovietct
24-01-07, 08:21 AM
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

nvson
24-01-07, 08:38 AM
Bạn thử dùng hàm sau:


=IF(OR(NOT(ISERROR(MATCH(A1,B1:B10,0))),NOT(ISERRO R(MATCH(A1,C1:C10,0)))),"CV1","CV2")

Mr Okebab
24-01-07, 10:01 AM
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!

Đề 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!

Mr Okebab
24-01-07, 10:55 AM
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ử xem nhé

If(countif(B1:E10;A1)>0;CV1;CV2)
Thân!

vuphuocha
24-01-07, 12:32 PM
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.

Mr Okebab
24-01-07, 12:57 PM
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.

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!

vuphuocha
24-01-07, 03:08 PM
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!
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.

Mr Okebab
25-01-07, 10:44 AM
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.

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))=FA LSE;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!

ngovietct
25-01-07, 11:14 AM
Thanks các bạn!


Cao thủ wa@$@!^%

ngocxit000
01-03-07, 01:42 AM
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!

Đào Việt Cường
01-03-07, 10:24 AM
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)

ThuNghi
01-03-07, 11:21 AM
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

Shinec
02-04-07, 10:19 PM
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.

CPH
02-04-07, 10:25 PM
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)))

chibi
02-04-07, 10:31 PM
Hãy dùng hàm ISNA(), phần help nói rất rõ.

SA_DQ
03-04-07, 06:10 AM
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!)

Mr Okebab
03-04-07, 11:14 AM
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;M ATCH(B7;MaHang;0);0));0)

Thân!

Shinec
03-04-07, 12:18 PM
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 đỡ!

BumBum
16-04-07, 01:04 AM
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 đỡ!

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))

SA_DQ
20-04-07, 04:26 PM
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))
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:

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

/(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!

anhtuan1066
24-04-07, 05:36 PM
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

Đào Việt Cường
30-04-07, 10:02 AM
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:



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

- 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!

anhtuan1066
05-05-07, 05:16 PM
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

imissu18
20-06-07, 06:54 PM
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 (http://www.giaiphapexcel.com/forum/showthread.php?t=944) nha!.

HYen17
21-06-07, 11:12 AM
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
Ư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ẽ!
:=\+

digita
21-06-07, 11:39 AM
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

ThuNghi
21-06-07, 12:12 PM
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.

Mr Okebab
21-06-07, 05:19 PM
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.

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!

HYen17
22-06-07, 11:18 AM
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.

Thu Nghi chủ quan rồi: Hàm mảng trả về không là một kết quả; mà là nhiều kết quả;
Chúng ta thấy có người đã viết hàm mảng tự tạo trả về nhiều kết quả lọc từ 2 sheets đó thôi! Vài lời mong bạn cảm thông cho sự đường đột!

anhtuan1066
22-06-07, 02:32 PM
Cái hay của bảng tính Excel là dường như nó đã đưa ra khá đầy đủ công cụ và hàm đễ mặc sức chúng ta tung hoành. Việc còn lại là do sáng tạo và kinh nghiệm có dc sau thời giản giãi quyết bài tập. Vậy nếu như bạn muốn biết dc mặc mạnh yếu của từng hàm thì ko gì bằng bạn hãy giãi bài tập thật nhiều. Vì dù sao mỗi người nhìn mặt mạnh yếu của từng hàm là theo cách nghĩ riêng, chưa chắc ai giống ai! Vậy bạn còn chần chừ gì nữa mà ko download những bài người ta thắc mắc và đã giãi quyết trên diển đàn này về máy tính rồi tự giãi quyết theo cách của bạn. Tôi dám chắc thời gian tới bạn cũng sẽ giõi như ai!
ANH TUẤN

Nguyễn Duy Tuân
23-06-07, 05:50 PM
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 (http://www.giaiphapexcel.com/forum/showthread.php?t=944) nha!.

Tôi xin nhận xét ngắn thế này.
Ưu điểm: Không chỉ 2 hàm trên mà tất cả các hmà của Excel đều hướng tới một cách dùng đơn giản, dễ áp dụng. Nói là đơn giản thôi chứ cũng phải học mãi mới áp dụng tốt được.

Nhược điểm: Chỉ khi bạn bắt đầu áp dụng nhiều, mức độ ứng dụng cao thì mới thấy cái nhược điểm chung của các hàm Excel.
Hàm VLookup và HLookup có ý nghĩa như nhau, hàm V* tìm theo chiều dọc (trên xuống dưới) tại cột đầu tiên, H* tìm theo chiều ngang (trái qua phải) và trả về giá trị khi tìm ở cột chỉ định khi tìm thấy.
Không chỉ 2 hàm trên mà tất cả các hàm tìm kiếm trong nhóm Lookup của Excel, điều kiện tìm là điều kiện đơn hay tìm đích danh. Tôi không thể tìm một nhân viêc có số ngày công >20 và số năm công tác là n, hay tìm một nhân viên mà có doanh số bán hàng >1 tỷ....

Nếu ai đó học Access rồi thì thấy không như vậy, nhóm hàm D* của nó rất động,...khó học hơn nhưng làm được những yêu cầu phức tạp.

Cón hàm IF giới hạn chỉ có 7 vòng lõ. Đây là một nhược điểm! Nhưng nếu bạn biết áp dụng thì làm được tất.
Xét một ví dụ kiểu "ngớ ngẩn" thế này: Nếu A là "Nam" thì 100, nếu A là "Nữ" thì là 50. Tôi có hai cách làm như thế này:
= IF(A="Nam",100,IF(A="Nữ,50,0)) --> Có 2 Hàm IF lồng nhau.

Như vậy bạn có thể làm = IF(A="Nam",100,0) + IF(A="Nữ",50,0)

Phân tích logic một chút bạn sẽ thấy A chỉ có thể là 1 trong phương án, chứ không bao giờ cùng xảy ra.

Với cách làm trên các hàm IF không bị bao nhau mà vẫn ra đúng kết quả.

Trong trường hợp có n điều kiện, ta có thể phân tích một chút theo hướng trên sẽ Ok.

------------------------------------------------------------------------
Lan man ngoài vấn đề của bài tôi nói thêm về nhược điểm của một số chức năng hay của Excel.
- Tất cả các phương thức lọc - Filter của Excel cũng chỉ lọc được trên một sổ duy nhất chứ không tạo được quan hệ, kết hợp.
- Chức năng PivotTable rất hay nhưng cũng chỉ tạo trên một sổ đơn không có điều kiện gì cả.
...


Tôi nghĩ để đạt được được ưu điểm chính của Excel làm và nhìn thấy ngay kết quả, một bảng tính thông minh và giản đơn thì mọi cái cũng phải nên đơn giản, vì thế MS đã cố tình không tạo thêm sự phức tạp nữa.

Chỉ những ai có như cầu cao về xử lý dữ liệu thì mấy hạn chế nói trên mới đáng để lo nghĩ mà thôi.


.

phananhson
02-08-07, 02:50 PM
Xin hỏi làm cách nào để hàm vlookup trong Excel có thể trả về giá trị trống hoặc #N/A nếu như giá trị dò không có, tôi đã thử và trong mọi trường hợp nếu vlookup dò không có giá trị đều trả về giá trị của ô liền kề trước đó mặc dù value không đúng.

TranThanhPhong
02-08-07, 02:57 PM
Chào bạn,

Do tham số cuối của hàm bạn dùng số 1 nên xảy ra vấn đề như bạn nêu, bạn đổi tham số sang số 0.

Tham số cuối của Vlookup dùng là 1 khi và chỉ khi danh sách tham chiếu đã được sắp xếp thứ tự trước.

TP.

anhtuan1066
02-08-07, 02:59 PM
VLOOKUP(trị dò, mãng, cột dò, kiểu dò)
Với kiểu dò là 0 thì mới cho kết quả chính xác... Tôi e rằng tham số này bạn đang ghi =1 hoặc -1 gì đó (cũng có thể bạn ko ghi gì chăng? Thử thêm tham số này = 0 xem)... Tóm lại, 1 hàm VLOOKUP đầy đũ phải có 4 tham số: Trị dò, mãng, cột dò, kiểu dò
Với kiểu dò =0, nếu VLOOKUP ko tìm dc giá trị thì sẽ cho kết quả =#N/A... Nếu bạn muốn tránh luôn lỗi này thì dùng thêm hàm ISNA
IF(ISNA(VLOOKUP(gì gì đó....)),"",VLOOKUP(gì gì đó...))
Làm thử xem
ANH TUẤN

tsf
02-08-07, 02:59 PM
Chú ý: cứ thích dùng mã bằng kiểu NUMBER thì dễ bị lỗi lắm bạn ạ (Bạn xem help thì biết là tại sao). Bạn nên chọn mã bằng kiểu Text (nếu dùng kiểu số thì bạn phải gõ dấu ' trước số hoặc format kiểu text trước khi nhập liệu).

anhtuan1066
02-08-07, 03:02 PM
Ko cần = TRUE đâu Diễm à... ko ghi gì nghĩa là true rồi...
ANH TUẤN

phananhson
03-08-07, 09:33 AM
Cám ơn cả nhà.
Lý do hàm Vlookup của mình không dùng được là do mình copy dữ liệu ( Bảng giá chứng khoán trực tuyến) từ 02 trang web khác nhau vào excel nhưng chúng lại không có cùng định dạng phần chữ ở Mã chứng khoán nên không dùng Vlookup được. Mình đã thử đổi sang Text, ... nhưng vẫn không được.
VD: A1=ABT B1=ABT tại C1:=A1=B1 ---> FALSE
Giúp mình với.

minhlev
03-08-07, 10:09 AM
Bạn thử tìm trong hai ô đó có khoảng trống không. Nếu có thì xoá toàn bộ khoảng trống đi. Hy vọng là được. Nếu vẫn không được thì bạn up File lên diễn đàn sẽ có người giúp bạn.

anhtuan1066
03-08-07, 10:27 AM
Định dạng chử thì ăn nhằm gì... Miễn nó đừng bỏ dấu tiếng Việt là OK hết... Bây giờ nếu có sai chăng là kiễm tra lại giống như minhlev đã nói...
ANH TUẤN

phananhson
03-08-07, 12:51 PM
Cám ơn minhlev
Đúng như bạn nói, mình thử căn lề trái + phải thì thấy có khoảng trống nên không dùng Vlookup được, bây giờ thì OK rồi.
Thank so much.

anhtuan1066
26-08-07, 09:28 PM
Còn đây là 1 hàm tự tạo, có tác dụng giống VLOOKUP nhưng nó có thễ dò tìm trên tất cả các sheet... Các bạn xem file, cũng rất dể hiểu... Kết quả tìm dc nằm ở Sheet LookupAll
Mến
ANH TUẤN

digita
27-08-07, 08:15 AM
Tôi xin mến tăng anh bạn Anh Tuấn và mọi người khác trong GPE 1 UDF tìm giá trị với nhiều điều kiện.

Syntax:

Mlookup(mảng dò tìm, Mảng 1 = điều kiện 1, Mảng 2 = điều kiện 2, Mảng 3 = điều kiện 3, v.v.)

Tôi đính kèm 1 ví dụ nhỏ để dễ tham khảo. Vậy là ta có thể có cách giải quyết khi hàm lookup không làm được trong trường hợp này.

Mến

ThuNghi
27-08-07, 11:27 AM
Hàm VBA Vlookup trên các sh thấy thế nào, tìm từng sh nếu có 3 sh OK thì chỉ lấy sh đầu tiên.
Còn Mlookup thì có thể thế hàm Excel cũng OK
{=Mlookup(D3:D7,A3:A7=H2,B3:B7=H3,C3:C7=H4)}
Ta có thể dùng
=INDEX($D$3:$D$7,SUMPRODUCT(($A$3:$A$7=$H$2)*($B$3 :$B$7=$H$3)*($C$3:$C$7=$H$4)*ROW($A$3:$A$7))-ROW($A$2),0)

Nguyễn Duy Tuân
27-08-07, 04:15 PM
Thằng này rất hay đấy, liệu có thể biến thành công thức bình thường chứ không phải là công thức mảng được không vậy???

Thân!

Vì các đối số thứ 2 của hàm là biểu thức logic mà vùng = (só sánh) với một giá trị nên bắt buộc phải dùng CT mảng, nếu không lỗi là #VALUE đương nhiên. Hàm này phải dùng cách của CT mảng thì mới có ý nghĩa.

Để làm được hàm này, người làm ra có đẳng cấp cao về lập trình VBA, hiểu rõ cách làm việc của FormulaArray trong Excel.

Mời các bác tham khảo tại đây:
http://www.excelguide.eu/NL_MultipleLookup.html

Mr Okebab
27-08-07, 04:38 PM
Vì các đối số thứ 2 của hàm là biểu thức logic mà vùng = (só sánh) với một giá trị nên bắt buộc phải dùng CT mảng, nếu không lỗi là #VALUE đương nhiên. Hàm này phải dùng cách của CT mảng thì mới có ý nghĩa.

Để làm được hàm này, người làm ra có đẳng cấp cao về lập trình VBA, hiểu rõ cách làm việc của FormulaArray trong Excel.



Em không hiểu lắm (vì đầu óc hơi be be be !!!), nhưng hàm sumproduct cũng dùng các mảng như vậy, chỉ có khác là nó sẽ lấy các giá trị thỏa mãn cộng lại với nhau. Nếu chỉ có 1 giá trị thì sẽ là chính giá trị tìm thấy

Hàm trên cũng tìm như vậy, có cái khác là chỉ tìm 1 giá trị, tuy nhiên giá trị có thể là số hoặc chuỗi

Vậy sao sumproduct đâu có dùng mảng đâu ??

Nguyễn Duy Tuân
27-08-07, 07:35 PM
Theo nguyên tắc, các giá trị tham gia vào biểu thức tính toán phải có cùng kiểu giá trị. A1:A10 > B1 là sai kiểu, A1:A10 là một vùng (Range) còn B1 là một giá trị (Value)-->khác kiểu. Những trường hợp như vậy, Hàm Excel trả về giá trị #VALUE. Nhấn CTRL+SHIFT+ENTER (Công thức mảng) sẽ cho chạy từng dòng một từ A1->A10 và khi đó nó đồng nhất về kiểu giá trị.

Làm một thí nghiệm kiểm tra vùng A3:A7 có ô nào chứa "Smith" không?
1- Gõ công thức sau và ENTER
=OR(A3:A7="Smith")

2- Gõ công thức sau và CTRL+SHIFT+ENTER
=OR(A3:A7="Smith")


Vậy sao sumproduct đâu có dùng mảng đâu ??
Trường hợp này chắc MS có bí quyết riêng rồi, hic. Tớ phải ngâm cứu thêm đã.

digita
28-08-07, 08:59 AM
Không ngờ cái UDF này là đề tài thảo luận khá sôi nổi. Và câu hỏi của anh bạn Okebab rất hay.

Thật tình mà nói khi mới làm quen với XL tôi thấy công thức array là khó hiểu nhất và thường tránh dùng loại này, nếu có thể được.

Khi am tường về cách áp dụng array cho công thức sẽ giúp tận dụng hết cái chức năng tính toán tiềm ẩn trong XL. Giống như bạn chạy xe hơi và bấm nút “turbo overdrive – on” để máy chạy mạnh hơn và tăng giảm ga để xe chạy đầm hơn vậy. Bạn ThuNghi là vua của công thức sumproduct rồi đó.

Theo thiển ý mình thì như vầy. Trong công thức array từng phần tử trong mảng được tính vòng lập từng cái 1. Còn công thức thường thì XL tính theo 1 đơn vị vùng hợp nhất tuy là có nhiều ô (single block of cells). Như thí dụ của anh TuanVNUNI


1- Gõ công thức sau và ENTER
=OR(A3:A7="Smith")

2- Gõ công thức sau và CTRL+SHIFT+ENTER
=OR(A3:A7="Smith")

Thí dụ 1 là công thức thường không cho kết quả tìm trong khi cái thứ 2 là dưới dạng array evaluate từng ô trong mảng A3:A7.

Điều lý thú là khi khi ta gõ hàm sumproduct, mặc dù chi tiết địa chỉ các vùng tính toán và các phần tử vùng đều toàn là dạng array, ta chỉ bấm Enter.

Trở lại câu hỏi của Okebab - có cách nào làm cái UDF này như là 1 công thức bình thường (tức là chỉ gõ Enter thôi). Tôi nghĩ là không. Nếu ai có ý kiến khác thì xin mòi đóng góp để mọi người hiểu thêm về vấn đề nêu ra.

Các bạn có thể vô trang web nầy tham khảo thêm về khái niệm hàm ở dạng array. Dưới trang đó có cho down về cái workbook thí dụ nửa.

http://www.cpearson.com/excel/ArrayFormulas.aspx

connhangheo
29-08-07, 10:27 PM
em không hiểu em làm sai chỗ nào trong file excel bên dưới, mong các bác chỉ giáo. Thanks!

tedaynui
29-08-07, 10:45 PM
Bạn thêm vào công thức như sau
=VLOOKUP(LEFT(B12,2),solieu_th2,2,0)

LikeIt
29-08-07, 10:45 PM
công thức của bạn thiếu số 0 ở sau cùng: =VLOOKUP(LEFT(B17,2),solieu_th2,2,0)

Mến
NHT

connhangheo
29-08-07, 11:15 PM
cảm ơn bạn nhưng vẫn không được bạn ạ, chỉ được ở cột đơn giá vẫn không được, bạn xem lại giúp mình,thanks

tedaynui
29-08-07, 11:51 PM
cảm ơn bạn nhưng vẫn không được bạn ạ, chỉ được ở cột đơn giá vẫn không được, bạn xem lại giúp mình,thanks
Bạn tải file ở bài #2 về xem chưa ?
Cột đơn giá Table_array của bạn không đúng.

TDN

anhphuong
30-08-07, 08:21 AM
Bạn xem cái này nha. Chú ý đến điều kiện lọc của hàm IF và VLOOKUP, nhất là khai báo Table_array của VLOOKUP

cokhimocaybt
30-08-07, 09:06 AM
Vậy là xong có gì đâu mà khó

Nguyễn Xuân Sơn
30-08-07, 09:35 AM
hàm của bạn thiếu số 0 ở sau cùng : viết thế này thì đủ và đúng
: =VLOOKUP(LEFT(B16,2),solieu_th2,2,0)

Bạn xem lại phai gửi kèm nhé!

kiendt1973
30-08-07, 09:52 AM
Em muốn các bác áp dụng vào trường hợp cụ thể giúp em nhé:
Em có sheet đầu là tồn kho của các vật tư theo từng kho (mỗi kho trên 1 cột và có tên kho ở đầu cột làm tiêu đề)
Sheet 2: là tên kho có trong sheet tổng (vd: kho01). Em muốn tạo 1 hàm để chuyển số liệu từ sheet đầu với yêu cầu: lọc theo mã vật tư và trả về giá trị trong cột có tên đầu là kho01 ở sheet tổng. Nếu không có cột kho đó thì trả về giá trị = số 0 vd: ko có kho03 thì số lượng các vật tư trong sheet kho03 sẽ có giá trị 0.
Các bác nghiên cứu giúp em nhé.
thanks

connhangheo
30-08-07, 11:34 AM
thanks các bác rất nhiều
Nhưng có cái là em vẫn ko hiểu thêm số 0 ở cuối và ko có số 0 đó thì khác nhau như thế nào, vì từ trước đến giờ em nghĩ 2 cách trên là như nhau.

em không hiểu tại sao khi em không thêm 0 ở cuối thì có chỗ sai mà có chỗ lại vẫn đúng??? Mong các bác chỉ giáo.

ThuNghi
30-08-07, 11:43 AM
Cái này chỉ cần if(n=0,0,Vlookup(....,n,0)) là OK
Vấn đề n giải quyết n= if(type(match())=16,0,match())

anhtuan1066
30-08-07, 11:49 AM
Thậm chí ko cần 1 hàm nào cả luôn
Sheet Kho1, D3 = tong!D6 rồi kéo fill xuống
Sheet Kho2, D3 = tong!E5 rồi kéo fill xuống
Sheet kho3, D3 = tong!F5 rồi kéo fill xuống
ANH TUẤN

anhtuan1066
30-08-07, 12:38 PM
Sai ở chổ nào? Bạn nói cụ thể đi.. chứ các bạn ở đây ai cũng đã làm ra kết quả, có vấn đề gì đâu...
ANH TUẤN

kiendt1973
30-08-07, 01:02 PM
Thậm chí ko cần 1 hàm nào cả luôn
Sheet Kho1, D3 = tong!D6 rồi kéo fill xuống
Sheet Kho2, D3 = tong!E5 rồi kéo fill xuống
Sheet kho3, D3 = tong!F5 rồi kéo fill xuống
ANH TUẤN

Cách của bạn có 1 vấn đề là nếu trong kho01 vị trí mặt hàng thay đổi thì sẽ ko ổn. mình muốn dùng hàm để còn kiểm tra nếu đúng mặt hàng thì mới điền số lượng vào.

ThuNghi có thể chỉnh giúp mình trực tiếp trên file của mình đc ko? Mình rất không thạo các hàm trong excel (Không phải do mình lười đâu nhé)
Thanks

ThuNghi
30-08-07, 01:53 PM
Bạn xem file nhé, nhớ là công thức tại ô chữ đỏ có khác hơn.
Nếu tên sheet nằm trong danh mục kho thì sẽ có số liệu.

Nguyễn Xuân Sơn
30-08-07, 02:56 PM
À hàm đầy đủ của nó là thế, số 0 là tìm kiếm tuyệt đối, 1 là tương đối ( từ dòng đến dòng kế tiếp), còn nếu chỉ viết như bạn thì phải sắp xếp DL theo TT thì mới đwocj cả.

phamduylong
30-08-07, 05:10 PM
em không hiểu tại sao khi em không thêm 0 ở cuối thì có chỗ sai mà có chỗ lại vẫn đúng??? Mong các bác chỉ giáo.
Giải thích thêm cho bạn đối số cuối cùng của VLOOKUP (hoặc HLOOKUP)

range_lookup =1, cột (dòng) đầu tiên của bảng dò (table_array) phải sắp xếp theo thứ tự từ nhỏ đến lớn (..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE) nếu không kết quả sẽ không đúng vì Excel dò tương đối.
Dữ kiện dò (lookup_value) có thể không có trong cột đầu tiên của bảng dò.

range_lookup =0, cột đầu tiên của bảng dò (table_array) không cần sắp xếp theo thứ tự nhưng Dữ kiện dò (lookup_value) bắt buộc phải có trong cột đầu tiên của bảng dò. Nếu không có sẽ bị lỗi #N/A.

Nếu không khai báo range_lookup, Excel xem như range_lookup=1.

abc12199
09-09-07, 05:59 PM
Chào Các Bạn Tôi Có 1 Bài Tập Về Hàm Vlookup Nhưng Không Biết Làm Thế Nào Mong Các Ban Giúp đỡ

Trần Văn Bình
09-09-07, 08:38 PM
Ý của bạn có phải trong cột mã hành liên kết vừa mã hành vừa tên hàng phải không

anhtuan1066
09-09-07, 09:22 PM
Sai là cái chắc rồi... Lý ra phải là như vầy nè:
G5 =VLOOKUP(LEFT(F5,1),$C$15:$F$17,2,0)&" loại "&RIGHT(F5,1)
H5 =VLOOKUP(LEFT(F5,1),$C$20:$E$22,RIGHT(F5,1)+1,0)
I5 =VLOOKUP(LEFT(F5,1),$C$15:$F$17,4,0)
Chú ý: từ D15:D17 bạn merge cell như thế là ko đúng đâu nhé! Sẽ gây nhiều phiền phức ko đáng có...
Mến
ANH TUẤN

minhlev
09-09-07, 09:57 PM
Công thức ở ô G5 của bạn không ra kết quả là đúng. Vì giá trị RIGHT(F5,3) là dạng text trong khi giá trị ở ô I15 lại là dạng số. Muốn công thức của bạn hoạt động đơn giản thêm *1 vào sau RIGHT(F5,3) thôi. Công thức mới tại ô G5 là:
=VLOOKUP(LEFT(F5,1),C15:F17,2,0)&""&VLOOKUP(RIGHT(F5,3)*1,I15:K16,2,0)

hamy
10-09-07, 08:54 AM
Trong ô G5 bạn nhập công thức:
=VLOOKUP(LEFT(F5,1),$C$15:$F$17,2,0)&" "&VLOOKUP(VALUE(RIGHT(F5,2)),$I$15:$K$16,2,0)
Trong ô H5 bạn nhập:
=IF(RIGHT(F5,1)="1",VLOOKUP(LEFT(F5,1),$C$20:$E$22,2,0),VLOOKUP(LEFT( F5,1),$C$20:$E$22,3,0))
Trong ô I5 bạn nhập:
=VLOOKUP(LEFT(F5,1),$C$15:$F$17,4,0)
Mà bạn đừng nên dùng Merge Cells trong Vùng tìm kiếm (D15:E15), như vậy rất khó để bạn xác định cột trả về.

pce
16-09-07, 11:00 PM
Em muốn hỏi: dùng hàm Vlookup để tìm kiếm gía trị theo 2 biến thì cần kết hợp các hàm khác như thế nào?Hoặc có thể chỉ dùng 1 hàm Vlookup có được không ạ? Ví dụ như tra hệ số k theo l/b và z/b...Cảm ơn các anh nhiều.

ThuNghi
17-09-07, 10:00 AM
dùng hàm Vlookup để tìm kiếm gía trị theo 2 biến thì cần kết hợp các hàm khác như thế nào?
Tôi thấy bạn pce hỏi cũng có lý, nếu không dùng vba, hay Execl2007 thì Vlookup theo 2 biến cũng khó.
Theo tôi dùng if kết hợp offset để thay vùng range. Nếu có vd sẽ cụ thể.

pce
17-09-07, 11:04 AM
Vâng em cảm ơn nhiều!Em đưa ví dụ (co file kèm theo) trong bài này em muốn tìm hệ số ko theo hai biến z/b và l/b.

DOSNET
17-09-07, 11:35 AM
thanks các bác rất nhiều
Nhưng có cái là em vẫn ko hiểu thêm số 0 ở cuối và ko có số 0 đó thì khác nhau như thế nào, vì từ trước đến giờ em nghĩ 2 cách trên là như nhau.
Cách mình tự học chức năng các hàm trong Excel như này, thay vì mình đánh trực tiếp tên công thức trực tiếp vào ô, mình sẽ chọn Insert\Function. Khi bạn chọn hàm cần dùng, hộp thoại Function Agrument hiện ra, với một chút kiến thức Tiếng anh, bạn sẽ rõ hiểu hơn từng chức năng của các phần tử trong hàm. Chúc bạn vui !

SoiBien
17-09-07, 02:51 PM
Chắc bạn chưa biết cách upload. Bạn xem file ví dụ nhé. Áp dụng thủ thuật.
Thân

SoiBien
17-09-07, 09:14 PM
cái lày thì PCE chỉ cần dùng hai hàm Index và match lồng vào nhau thôi, chú ý dữ liệu dạng gì? cần phải thống nhất. Bạn tìm thêm trên DD về hàm Index và Match nhé, đã chỉ dẫn rõ ràng rồi.
Thân.
PS. Đây là link đến bài viết
http://www.giaiphapexcel.com/forum/showthread.php?t=264

man231186
14-10-07, 10:24 PM
ban oi giup minh lam bai nay voi ham Vlookup moi? cam on truoc nhe. Ban dung dung ham if nhe! dang bai ve giai phuong trinh bac hai

SA_DQ
15-10-07, 06:58 AM
ban oi giup minh lam bai nay voi ham Vlookup moi? cam on truoc nhe. Ban dung dung ham if nhe! dang bai ve giai phuong trinh bac hai

Bạn tìm trên diễn đàn với từ khóa: 'phương trình bậc hai'
hay tham khảo ở đây, nếu đúng í thì đến nội quy diễn đàn đọc lại ba lần nữa nha!
http://www.giaiphapexcel.com/forum/showthread.php?t=1265

hatcamthao
15-10-07, 01:20 PM
Mình đang làm báo giá form mẫu cho công ty. Đại loại thê này, khi đánh cole No. ở sheet báo giá, thì các thông số sẽ tự tìm ở 2 sheet data còn lại, để cho ra dữ liệu. Nếu mà 1 sheet thì mình làm ok, nhưng 2 sheet thi làm thế nào nhỉ....
Thank you cac bac/

duongsatdn
15-10-07, 01:37 PM
Nếu mà 1 sheet thì mình làm ok, nhưng 2 sheet thi làm thế nào nhỉ.... thì làm hàm tìm kiếm như bạn nói chứ sao. Gửi file ví dụ lên đi bạn,

hatcamthao
15-10-07, 04:12 PM
Day la vi du cua minh

duongsatdn
15-10-07, 04:35 PM
Với mã K1009 ô C6=VLOOKUP(B6;[Nhap2.xls]Sheet1!$A$1:$B$7;2;0)
Trong đó [Nhap2.xls]Sheet1!$A$1:$B$7 là vùng A1 đến B7 thuộc Sheet1 của File Nhap2.xls
File Nhap2.xls có chứa dữ liệu của bạn đã mở. Vùng dữ liệu nên đặt Name. Khi nhập vùng tìm kiếm thì bạn truy vấn đến file nhap2.
Thân mến!

SoiBien
15-10-07, 04:36 PM
Day la vi du cua minh

Tức là tìm không thấy bên sheet này thì phải nhảy qua sheet kia tìm tiếp. Bạn xem nhé.

Có điều tại sao lại không nối dữ liệu vào chung một sheet hả bạn?

ThuNghi
15-10-07, 04:38 PM
Có phải tìm Data1 không có thì tìm sang data2
Bạn dùng ct sau:
C6:=IF(ISNA(VLOOKUP($B6,data1!$B$6:$D$20,2,0)),"",VLOOKUP($B6,data1!$B$6:$D$20,2,0))&""&IF(ISNA(VLOOKUP($B6,data2!$B$6:$D$20,2,0)),"",VLOOKUP($B6,data2!$B$6:$D$20,2,0))
D6:=VALUE(IF(ISNA(VLOOKUP($B6,data1!$B$6:$D$20,3,0 )),"",VLOOKUP($B6,data1!$B$6:$D$20,3,0))&""&IF(ISNA(VLOOKUP($B6,data2!$B$6:$D$20,3,0)),"",VLOOKUP($B6,data2!$B$6:$D$20,3,0)))

anhtuan1066
15-10-07, 04:54 PM
Đồng ý rằng trong trường hợp này Excel cũng giải quyết dc, nhưng có phải là công thức đã phức tạp đi rất nhiều ko? Tôi đang thắc mắc tại sao bạn ko đưa chung dử liệu vào 1 sheet nhỉ?
Công thức của ThuNghi trong trường hợp này thì dài quá ko cần thiết... Lấy cái của SoiBien, kết hợp thêm 1 cái IF ở trên đầu cùng là ổn rồi...
=IF(B6="","",IF(ISNA(.... )
Mến
ANH TUẤN

kelvin
16-10-07, 10:54 AM
Tôi thường điền nhanh công thức VLOOKUP bằng đoạn VBA sau:
Sub QuyetToanChi5()
Dim sPartFormula As String, sPF2 As String
sPartFormula = "=VLOOKUP($A4,Data,"
sPF2 = ",0)"
Windows("QToanChi_Rp.xls").Activate
Range("L4").FormulaR1C1 = "=RC[-2]*RC[-1]"
Set MyRg = Range("b4:f4,h4:k4,m4")
For Each smallrng In MyRg
With smallrng
.Formula = sPartFormula & .Offset(-2, 0) & sPF2
End With
Next smallrng
End Sub

Bạn nào biết cách dùng vòng lạp tương tự, nhưng khai báo các Range là Array?

hatcamthao
17-10-07, 11:13 AM
Vi minh thuong xuyen phai update du lieu 2 sheet con lai. cho nen khong the cho vao mot sheet,.....

ThuNghi
17-10-07, 11:20 AM
Muốn cho dễ hiểu:
DK1: IF(ISNA(VLOOKUP($B6,data1!$B$6:$D$20,2,0)),"", VLOOKUP($B6,data1!$B$6:$D$20,2,0))
Nếu tìm không thấy trong data1 thì lấy rỗng
DK2: IF(ISNA(VLOO KUP($B6,data2!$B$6:$D$20,2,0)),"",VLOOKUP($B6,data 2!$B$6:$D$20,2,0))
Nếu tìm không thấy trong data2 thì lấy rỗng
Ghép 2 cái lại =DK1&""&DK2
Còn IF(ISNA(...),"" nghĩa là không tìm thấy $B6 thì gán ""

kelvin
17-10-07, 11:23 AM
Gửi các bạn xem thêm các đoạn code, mục đích: vlookup từ Sheet Souce, chép ra giá trị, bỏ bớt các cột lặp lại vừa để dể nhìn, vừa ...đở tốn mực in, file mẫu là 2 file mà tôi đã upload


Option Explicit
Dim smallrng As Range
Dim MyRg As Range
Dim irEnd As Integer 'khai bao bien interger cua row cuoi cua destination extract

Sub QuyetToanChi_RpOpen()
' Macro recorded 15-10-2007 by MR
'D:\PTC\Accounting\BalanceSheet\Report\200709QToan Chi_Rp.xls
'D:\PTC\Accounting\BalanceSheet\Report\QToanChi_Rp .xls
Workbooks.Open Filename:="D:\PTC\Accounting\BalanceSheet\Report\QToanChi_Rp. xls"
End Sub

Sub CloseMe()
ThisWorkbook.Close True
End Sub

Sub Main()
QuyetToanChi_RpOpen
' Tim dong cuoi cua destination extract
With Worksheets("QToanChi")
irEnd = .Range("a4").End(xlDown).Row
End With
Input1RowFormula
FillDown (irEnd)
CopyValue (irEnd)
MyTypeDelete_CellDuplicateInColumn (irEnd)
MyTypeDCellDupC (irEnd)
MyTypeDCellDupC_I (irEnd)
End Sub

'- Buoc 2: la filter tai file data cac so lieu (record) cua C Chi,
' nho phai danh so lai dung thu tu cot (mau do)de dung trong vlookup
'- Buoc 3: copy phan data da filter vao file report quyet toan Chi,
' luu y den ID record, o day copy den cot so 29
' Close file data de nhe may
'Dat vung Data (Name) o Sheet Source, copy ID Record da loc sang sheet QToan Chi
'Kiem tra, copy dong tieu de co chua ca so ID cot giua 2 Sheet Source va Destination
Sub Input1RowFormula()
Dim sPartFormula As String, sPF2 As String
sPartFormula = "=VLOOKUP($A4,Data,"
sPF2 = ",0)"
Windows("QToanChi_Rp.xls").Activate
With Workbooks("QToanChi_Rp.xls").Worksheets("QToanChi")
'.DisplayZeros = False 'record:ActiveWindow.DisplayZeros = False
.Range("L4").FormulaR1C1 = "=RC[-2]*RC[-1]"
Set MyRg = .Range("b4:f4,h4:k4,m4")
For Each smallrng In MyRg

With smallrng
.Formula = sPartFormula & .Offset(-2, 0) & sPF2
End With
Next smallrng
End With
Sheets("QToanChi").Activate
'FillDown (lrEnd) de o day se bao bien chua khai bao
End Sub

Sub FillDown(irEnd As Integer)
' Macro recorded 15-10-2007 by MR
Range("B4:M4").AutoFill Destination:=Range("B4:M" & irEnd), Type:=xlFillDefault
End Sub

Sub CopyValue(irEnd As Integer)
Set MyRg = Range("B4:M" & irEnd)
'Trong case nay ta kg dung them for each van ok
With MyRg
.Value = .Value
End With
Set MyRg = Nothing
End Sub

Sub MyTypeDelete_CellDuplicateInColumn(irEnd As Integer)
Dim j As Integer, i As Integer
'Dung cu phap rngData.Cells(i,k) de chon cell tuong doi trong rngData, va bat dau tu 1
Set MyRg = Range("c4:c" & irEnd)
i = 1
j = i + 1
'Cells(1, 3) = rngData.Rows.Count = number 17
For j = 2 To MyRg.Rows.Count
With MyRg
If .Cells(j, 1) = .Cells(i, 1) Then
.Cells(j, 1) = ""
.Cells(j, 1).Offset(0, -1) = ""
'.Cells(j, 1).Offset(0, 8) = ""
'.Cells(j, 1).Offset(0, 9) = ""
Else
i = j
End If
End With
Next j
Set MyRg = Nothing
End Sub

Sub MyTypeDCellDupC(irEnd As Integer)
'Origine code From nvs GPE
Dim j As Integer, i As Integer
Set MyRg = Range("f4:f" & irEnd)
i = 1
j = i + 1
For j = 2 To MyRg.Rows.Count
With MyRg
If .Cells(j, 1) = .Cells(i, 1) And .Cells(j, 3) = .Cells(i, 3) Then
.Cells(j, 1) = ""
.Cells(j, 2) = ""
.Cells(j, 3) = ""
'.Cells(j, 1).Offset(0, 8) = ""
'.Cells(j, 1).Offset(0, 9) = ""
Else
i = j
End If
End With
Next j
Set MyRg = Nothing
End Sub

Sub MyTypeDCellDupC_I(irEnd As Integer)
Dim j As Integer, i As Integer
'Dung cu phap rngData.Cells(i,k) de chon cell tuong doi trong rngData, va bat dau tu 1
Set MyRg = Range("i4:i" & irEnd)
i = 1
j = i + 1
'Cells(1, 3) = rngData.Rows.Count = number 17
For j = 2 To MyRg.Rows.Count
With MyRg
If .Cells(j, 1) = .Cells(i, 1) Then
.Cells(j, 1) = ""
'.Cells(j, 1).Offset(0, -1) = ""
'.Cells(j, 1).Offset(0, 8) = ""
'.Cells(j, 1).Offset(0, 9) = ""
Else
i = j
End If
End With
Next j
Set MyRg = Nothing
End Sub

Sub NameData()
' NameData Macro
' Macro recorded 15-10-2007 by MR
Workbooks("QToanChi_Rp").Names.Add Name:="Data", RefersToR1C1:="=Source!R4C1:R33C29"
End Sub
Sub NameDelete()
' NameDelete Macro
' Macro recorded 15-10-2007 by MR
Workbooks("QToanChi_Rp").Names("Data").Delete
End Sub

ThuNghi
17-10-07, 11:44 AM
Ta co 1 Range MHList có 20 cột
Trong form thì ta nên gán RowSource là 20 cột để có thể


If Me.MHList.Value <> "" Then
ActiveCell.Value = Me.MHList.Value
ActiveCell.Offset(0, 1).Value = Me.MHList.Column(1)
.........................
ActiveCell.Offset(0, 20).Value = Me.MHList.Column(20)

Hay là chỉ chọn RowSource là 2 cột thôi còn lại dùng Vlookup theo Me.MHList.Value
Xin cám ơn.

phamduylong
17-10-07, 02:37 PM
Không biết câu hỏi của bạn có đúng như tôi nghĩ không.
Sub CopyRange này sẽ copy dữ liệu trong vùng có name là MHList vào nơi mà con trỏ đang chọn.
MHList có số cột, số dòng tùy ý.



Sub CopyRange()
Dim MHL As Range
Set MHL = Range("MHList")
r = ActiveCell.Row
c = ActiveCell.Column
If MHL.Cells(1) <> "" Then
Range(Cells(r, c), Cells(r + MHL.Rows.Count - 1, c + MHL.Columns.Count - 1)).Select
n = 1
For Each mycell In Selection
mycell.Value = MHL.Cells(n)
n = n + 1
Next
End If
End Sub

ThuNghi
17-10-07, 03:16 PM
Range("MHList") này được tạo ra khi chọn tìm theo MaHang, tôi chỉ lấy 2 cột Mả và Tên (nhiều hơn sợ nặng) làm RowSource cho listbox. Chọn xong thì tôi dùng Vlookup(Ma,DMHH,i,0) gán vào sh.
Đang phân 2 công đọan:
- Range("MHList") có 20 cột và còn lại lấy theo Me.MHList.Column(i) hay là vòng lặp.
- Range("MHList") chỉ 2 cột mã và tên, còn lại dùng Vlookup.
Cái nào thì tối ưu hơn.

hatcamthao
17-10-07, 04:17 PM
Thank you cac bac. Neu em lam 3 sheet thi chac cung tuong tu nhu the nhi,....Bac Soibien hoac bac nao co the giup em duoc khong?

ThuNghi
17-10-07, 04:26 PM
Nếu thêm thành 3 sh thì ct theo file SoiBien phải sửa lại.
Tóm lại dùng như sau:
Bạn nhấn Ctr F3 và nhập vào: DKn=IF(ISNA(VLOOKUP($B6,datan!$B$6:$D$20,2,0)),"",VLOOKUP($B6,datan !$B$6:$D$20,2,0))
n=1...,9
Nhớ đặt tên sh là Data1, Data2,...,Data9
=> C6=DK1&""&DK2&"".....&DKn
Sao bạn không đánh có dấu, lỡ reply rồi, nhớ gõ dấu vào nhé. Làm ơn!

phamduylong
17-10-07, 10:46 PM
Anh ThuNghi gởi bài của anh lên để anh em tham khảo

hatcamthao
18-10-07, 10:03 AM
Các bác giúp em dùng hàm vlookup tìm trên 3 sheet được không,.em áp dụng tìm trên 3 sheet mà cứ thấy báo lỗi...không hiểu sao...../
Thank you các bác
Hatcamthao

anhtuan1066
18-10-07, 10:44 AM
Làm vầy nè bạn ơi... Phải đặt name cho công thức gọn lại, nếu ko thì công thức bạn sẽ dài.. 1 Km luôn đấy! Hi.. hi...
ANH TUẤN

ThuNghi
18-10-07, 10:45 AM
Cái file này tôi load của Mr OkeBab, có thêm tham gia của tedaynui.
Trong Sh Nhap nếu dùng Vlookup tại C2, D2 sau đó chuyển sang value thì hay hơn hay là dùng Me.MHList.Column(i).
Bạn xem file nhé!

hatcamthao
18-10-07, 10:49 AM
Cám ơn bác Anh Tuấn rất nhiều,...
Bác giỏi thế này sao không đi dạy học đi nhỉ....

anhtuan1066
18-10-07, 10:52 AM
Cám ơn bác Anh Tuấn rất nhiều,...
Bác giỏi thế này sao không đi dạy học đi nhỉ....
Vì cạnh tranh hòng lại với mấy thầy ở đây như PhanTuHuong, ttphong2007 chẳng hạn... ha... ha...

SoiBien
18-10-07, 11:04 AM
Như vậy là bạn chưa hiểu về hàm if, thế này nhé:
If(đk,"Đúng thì làm cái này","Sai thì làm cái này")

Với 2 sheet bạn sẽ dùng 1 hàm If mà thôi:
If("Nếu không tìm được trong sh1","ta đi tìm trong sh2","vậy thì lấy sh1")

hàm ISNA() nói cho ta biết (true,false) rằng có tìm được hay không? Bạn để ý đi, nếu dùng Vlookup bình thường, nếu tìm không được thì bạn sẽ nhận được kết quả tại ô đó là #N/A, N/A chính là : Not Available. Hàm ISNA chính là IS Not Available.

Bạn dùng hàm If để "nói" cho Excel biết là nếu tìm không được trong sh1 (bằng hàm ISNA và hàm IF), thì qua sh2 tìm

Vậy bạn cũng phải dùng hàm ISNA và hàm IF để "nói" cho Excel biết là nếu tìm không được trong sh2 thì phải tìm trong sh3.

Với công thức ta sẽ diễn đạt thế này:

if(ISNA(sh1),"đúng thì ta tìm tiếp trong sh2","sai thì có nghĩa là tìm được, khỏi tìm tiếp")
____Khi tìm trong sh2 bạn sẽ cũng phải bảo cho nó biết là "nếu tìm không được trong sh2 thì ông Excel làm ơn tìm trong sh3"
____Vậy để nói như trên bằng hàm thì bạn cũng dùng như thế
____IF(ISNA(sh2),"đúng thì ta tìm tiếp trong sh3","sai thì có nghĩa là tìm được, khỏi tìm tiếp")

v.v.v.v

vậy là "nói" với Excel bằng hàm cho công việc tìm trên 3 sh, bạn sẽ phải liên kết lại các điều đã phân tích:
IF(ISNA(sh1),if(ISNA(sh2),"Tìm trong sh3","Tìm trong sh2"),"Tìm trong sh1")

Đôi lời giải thích, hy vọng bạn hiểu rõ hơn về hàm IF và cách dùng nó lồng vào nhau để bảo cho ông Excel hiểu. Hì hì!

ThuNghi
18-10-07, 11:16 AM
Theo tôi dùng cách & dễ hiểu hơn, nếu bạn ấy muốn có 10 data cũng dễ triển khai.

dgvacu
18-10-07, 03:25 PM
Gưỉ hatcamthao hàm vlookup tìm kiêm tất cả dữ liêu có tren máy của bạn

anhtuan1066
18-10-07, 03:39 PM
Có 1 hàm có thể tìm dc dử liệu trên tất cả các sheet nhưng là hàm tự tạo bạn à... Trên diển đàn này tôi đăng bài ấy khá lâu.. hình như nằm trong box "Excel và các công thức" thì phải... tuy nhiên ở đây ta bàn về chuyện những công thức thường phối hợp với nhau xem thử khà năng dc tới đâu... Tất nhiên với 100 sheet mà dùng công thức thường này thì.. hơi tê... Lời khuyên vẫn là bạn nên cho tất cả vào 1 sheet... Đó là cách nhập liệu hiệu quả nhất, có thể dể dàng trích xuất dử liệu mà ko cần nhờ đến hàm tự tạo nào cả!

dgvacu
18-10-07, 03:40 PM
Gửi hatcamthao tôi lần đầu post bài,nên nói ko rõ,hàm vlookup tìm kiếm được tất cả, kể cả trên bât kỳ file .xls tồn tại trên máy bạn.Bạn cứ việc dùng chuột bôi những vùng dữ liệu của bất kỳ sheet nào trên cùng một file .xls, hoặc của một file .xls khác để tham chiếu nhớ chú ý chính xác các thông số tham chiếu khác cho chính xác,hôm nào tôi sẻ gửi chobạn ví dụ cụ thể

haji
19-10-07, 12:28 PM
Hey, trước tiên xin lỗi các anh(chị),vì lần post bài trước Haji đã ko post bài có dấu( vi fạm qui tắc)....vì lúc đó post vội wóa nên quên mất........Xin lỗi nha!!!!

Kỳ này, Haji post bài hỏi tiếp.......( lần này mong là không vi phạm gì nữa..////// ),Mong các anh(chị) giúp em dùm nhé!
Thanks a lot!!!!!//**/

Em có gủi file đính kèm.

Vấn đề thứ hai Haji muốn hỏi có liên quan đến ngày tháng:
Đề bài cho hạn thanh toán là 01/05/03
ngày nộp của cơ quan A là:20/04/03
đề bài yêu cầu ghi vào cột Ghi chú như sau: Nếu ngày nộp sau hạn thanh toán 7 ngày, thi ghi" lưu ý", ngược lại không ghi gì cả
Haji đã làm như thế này but ko ra được kết quả:
=if(ngày nộp-hạn thanh toán>7,"lưu ý",0)
Các anh chị giúp em vấn đề này luôn nhá.
Thanks again!@$@!^%

DOSNET
19-10-07, 12:42 PM
Cột "ngày nộp" đâu hả bạn?
Thử VD này xem

anhtuan1066
19-10-07, 01:10 PM
Giãi thử ko biết đúng ko? Kiểm tra lại nha!
Trong file có mấy chổ bị thiếu, tôi đã thêm vào, chẳng hạn như ko có đơn giá phòng cho loại B!
ANH TUẤN

anhtuan1066
19-10-07, 01:12 PM
Cột "ngày nộp" đâu hả bạn?
Thử VD này xem
Đây chỉ là bài tập thôi mà... đâu cần phải thực tế như thế.. hi.. hi...

anhphuong
19-10-07, 01:16 PM
=IF(NgayNop<=HanThanhToan,"",IF(NgayNop-HanThanhToan<7,"","Luu y"))

minhtriamc
19-10-07, 03:59 PM
Xem lại định dạng sau khi lấy ngày nộp - hạn ngày thanh toán đi bạn, nếu để nó trong hàm value(), mình nghĩ là OK đấy

---------------------
Minh Trí, nick : trantri1311

minhtriamc
19-10-07, 04:04 PM
Update thường xuyên 02 sheet kia thì có sao đâu bạn, cho vào 01 sheet đặt data bên sheet chung = data bên sheet 01, cho nó khoảng 5000 rows đi, rồi đặt tiếp data bên sheet chung = data bên sheet 02

Như vậy update nhiêu chẳng được, excel không khó chỉ khó là do mình sắp xếp data mà thôi

--------------
Minh Tri, nick : trantri1311, URL :www.cafethanhpho.com

Mr Okebab
19-10-07, 04:43 PM
Nếu if nhiều quá thì các bác có thể nghiên cứu thêm hàm CHOOSE.

Thân!

CPH
20-10-07, 08:08 AM
tôi muốn dùng vlookup để lấy giá trị ở cột bên trái của cột cần tham chiếu. Mong các bạn chỉ giáo!

ngocduyen
20-10-07, 09:30 AM
Bạn cho ví dụ cụ thể đi.

MEGAheartno1
20-10-07, 09:41 AM
Bạn có thể đọc kỹ nội dung của hàm vlooup.
Cụ thể như sau
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
Lookup_value: giá trị bạn cần tìm,tra trong bảng ARRAY ( thường bảng array ở phía dưới ). Lookup_value có thể là một giá trị hoặc một tham chiếu. Nếu lookup_value nhỏ hơn giá trị nhỏ nhất ở cột đầu tiên trong bảng array thì hàm VLOOKUP sẽ trả về #N/A
Table_array có hai hay nhiều cột chứa dữ liệu. Sử dụng tham chiếu đến một vùng hay một tên vùng ( còn hay gọi là miền xác định). Giá trị ở cột đầu tiên trong bảng array này sẽ là giá trị mà hàm lookup_value cần tìm. Những giá trị này có thể là một chuỗi (text),số, hay những giá trị logic .
Col_index_num : Cột thứ " mấy " trong bảng bạn cần tra,tìm dữ liệu . Nếu col_index_num :

Nhỏ hơn 1, VLOOKUP báo lỗi là #VALUE! .
Lớn hơn số cột mà bạn có trong bảng báo lỗi #REF! .Range_lookup

CPH
20-10-07, 02:32 PM
Cảm ơn các bạn đã trả lời, nhưng chưa đúng ý tôi muốn hỏi.
ví dụ:
A1=1 B1=a
A2=2 B2=b
A3=3 B3=c
Tôi muốn lookup value tại cột B nếu đúng thì trả về giá trị tại cột A
Cảm ơn các bạn nhiều!

anhtuan1066
20-10-07, 02:45 PM
E rằng ko thể dùng VLOOKUP trong trường hợp như bạn nói... Ko biết có giống thế này ko?
Bạn xem file thử!
ANH TUẤN

Mr Okebab
20-10-07, 02:51 PM
Cảm ơn các bạn đã trả lời, nhưng chưa đúng ý tôi muốn hỏi.
ví dụ:
A1=1 B1=a
A2=2 B2=b
A3=3 B3=c
Tôi muốn lookup value tại cột B nếu đúng thì trả về giá trị tại cột A
Cảm ơn các bạn nhiều!
VD :
C1 = b
D1 = INDEX($A$1:$A$3;MATCH(C1;$B$1:$B$3;0);0)

Thân!

phamduylong
20-10-07, 08:53 PM
Bận quá nên không trả lời nhanh được.
Qua bảng tính load về, tôi có chỉnh lại theo ý mình. Có một số thay đổi:
1. Bạn đã dùng form để chọn và nhập dữ liệu (tức đã dùng VBA) thì nên bỏ bớt công thức cho bảng tính gọn hơn. Trong bài tôi không dùng công thức nào.
2. Listbox HHList cần gán RowSource="mahang" khi form mở, nên name "mahang" chỉ được tạo ra khi chạy VBA, khi kết thúc VBA, xóa luôn name "mahang". Vì thế bảng tính không cần name.
Các bạn góp ý thêm.
ThuNghi hướng dẫn dùng cách tạo CommandButton như trong sheet "Nhap" mà không cho chỉnh, xóa? Cái này tôi lại làm không được.

hatcamthao
21-10-07, 09:31 AM
Thanhs mọi người.
Ở đây là mình muốn sử dụng các hàm kết hợp cho luột. Còn việc tạo data trên một sheet, rồi update trên các sheet kia thì không "prồ" lắm.
Mình đang làm theo cách của bác Anh Tuấn (trong file:Vlookup tìm tren 3 sheet), nhưng trong bảng excel đấy có chỗ mình không hiểu:
Đấy là mục col_index_number: 'baogia'!D$5 (mình không hiểu lắm)
Thanks you các bác

ThuNghi
21-10-07, 10:16 AM
Bạn dùng thử file của tôi và sửa ct tại cột Price như sau:
=IF($B6="","",(IF(ISNA(VLOOKUP($B6,data1!$B$6:$D$20,3,0)),0,VLO OKUP($B6,data1!$B$6:$D$20,3,0)))+(IF(ISNA(VLOOKUP( $B6,data2!$B$6:$D$20,3,0)),0,VLOOKUP($B6,data2!$B$ 6:$D$20,3,0)))+IF(ISNA(VLOOKUP($B6,Data3!$B$6:$D$2 0,3,0)),0,VLOOKUP($B6,Data3!$B$6:$D$20,3,0)))
ie nếu tìm thấy B6 trong Data thì vlookup, nếu không thì lấy 0 =>0+0+x =x

'baogia'!D$5 (mình không hiểu lắm)
'baogia'!D$5 thực ra là có giá trị 3, bạn thử Right Click tại D5, format cell bạn sẽ thấy nó được định dang là "Price".
Và công thức sau
=IF($B6="","",IF(AND(DK_1,DK_2),KQ3,IF(AND(DK_2,DK_3),KQ1,KQ2)) )
Theo tôi, nói chung là khó hiểu khó triển khai.

ThuNghi
22-10-07, 04:39 PM
Cám ơn bác nhiều.

ThuNghi hướng dẫn dùng cách tạo CommandButton như trong sheet "Nhap" mà không cho chỉnh, xóa? Cái này tôi lại làm không được.
Chưa hiểu Bác hỏi gì?

phamduylong
22-10-07, 08:12 PM
Trong sheet NHAP không có protect, nhưng sao click phải để chỉnh sửa cái command button không được?

anhtuan1066
22-10-07, 08:52 PM
Trong sheet NHAP không có protect, nhưng sao click phải để chỉnh sửa cái command button không được?
Oái... cái Command Button này là thuộc thanh Control Toolbox cơ mà.. làm sao mà right click dc chứ... Tất nhiên là phải bấm nút Design Mode thì mới right click dc cơ mà...
Bác Long này... Ko biết có phải hỏi về món này hay là.... ??? hi... hi...

phamduylong
23-10-07, 01:16 PM
Oái... cái Command Button này là thuộc thanh Control Toolbox cơ mà.. làm sao mà right click dc chứ... Tất nhiên là phải bấm nút Design Mode thì mới right click dc cơ mà...
Bác Long này... Ko biết có phải hỏi về món này hay là.... ??? hi... hi...
Phát hiện ra rồi !
Cái Command Button này là thuộc thanh Control Toolbox, muốn right click nó phải Disable Macros.
Còn cái Button của thanh Form thì right click thoải mái.
Thân!

anhphuong
23-10-07, 01:56 PM
Phát hiện ra rồi !
Cái Command Button này là thuộc thanh Control Toolbox, muốn right click nó phải Disable Macros.
Còn cái Button của thanh Form thì right click thoải mái.
Thân!

Cách tốt nhất, bác vào View/Toolbars rồi lôi cái "thằng" Control Toolbox ra, bấm nút Design Mode cái hình eke í là bác dùng thỏai mái. Khi nào cần thử code thì tắt nó đi . Nếu mở file ở chế độ Disable Macros thì khi sửa xong bác không thử được code
Chúc bác vui
Hẹn gặp tại ... Hòa Long nhé, hay bữa nào bác lên Cao Lãnh thì hú một tiếng nhé, nhớ gọi trước kẻo tôi hay đi công tác lắm
Thân

hatcamthao
24-10-07, 09:50 AM
4800
Vlookup() thường là tra suôi. Vậy khi tra ngược thì công thức sẽ ra sao/
Dưới đây là ví t dụ của mình../

ThuNghi
24-10-07, 09:54 AM
Phải dùng Index, match thôi

tedaynui
24-10-07, 10:15 AM
4800
Vlookup() thường là tra suôi. Vậy khi tra ngược thì công thức sẽ ra sao/
Dưới đây là ví t dụ của mình../ Nếu muốn dùng VBA thì bạn có thể tìm và tham khảo bài viết (có file ví dụ) của Mr.Okebab
http://www.giaiphapexcel.com/forum/showthread.php?t=5130

TDN

SA_DQ
24-10-07, 02:18 PM
http://webketoan.vn/forum/showthread.php?t=17263

:-=

anhtuan1066
24-10-07, 08:02 PM
Ha... ha... Hôm nay phát hiện ra 1 chuyện... Thì ra đại ca đâu phải cái gì cũng biết tuốt... Cũng phải có cái chưa biết chứ nhỉ? Thậm chí có những cái chưa biết rất ngây ngô... hô.. hô.. Nên nhân dịp này dc "hướng dẩn" đại ca, cảm thấy "sướng" trong người làm sao
Nói đùa cho vui, đại ca đừng giận!
ANH TUẤN

ThuNghi
25-10-07, 02:25 PM
Gởi Thầy Long
1/ Khi thoát nó báo lỗi
ActiveWorkbook.Names("mahang").Delete
2/ Khi chọn trong HHList.AddItem (Lọc theo ký tự) thì nó không gán theo Array vừa lọc mà nó lấy theo MaHang.

HHList.RowSource = ""
For r = 2 To rc01
If Left(S01.Cells(r, 1), Len(NhomHang)) = NhomHang Then
HHList.AddItem S01.Cells(r, 1)
End If
Next
Thầy xem lại. Và nếu chọn Multi Select thì Nút Ghi hoạt động ra sao.
Cám ơn nhiều.

phamduylong
25-10-07, 09:38 PM
Ha... ha... Hôm nay phát hiện ra 1 chuyện... Thì ra đại ca đâu phải cái gì cũng biết tuốt... Cũng phải có cái chưa biết chứ nhỉ? Thậm chí có những cái chưa biết rất ngây ngô... hô.. hô.. Nên nhân dịp này dc "hướng dẩn" đại ca, cảm thấy "sướng" trong người làm sao
Nói đùa cho vui, đại ca đừng giận!
ANH TUẤN
Ê, tại anhtuan gọi anh là đại ca nên cứ ngỡ cái gì anh cũng biết.
Anh biết những gì anh đã biết. Còn những cái chưa biết thì để cho người khác biết. Gom lại thì nhiều người biết.
Còn nhiều cái chưa biết rất ngây ngô... hô.. hô..


Hẹn gặp tại ... Hòa Long nhé, hay bữa nào bác lên Cao Lãnh thì hú một tiếng nhé, nhớ gọi trước kẻo tôi hay đi công tác lắm
Thân
Hú anphuong bằng cái gì?

Thuyan.acc
26-10-07, 09:29 AM
Em có 1 file trong đó gồm 2 sheet, sheet 1 ở đây của em lấy từ dữ liệu của kế toán và đã được kế toán hạch toán rồi, còn sheet 2 của em là bảng kê bán hàng theo báo cáo thuế hàng tháng mà em lập (Bảng kê 02)

Kính nhờ Anh chị hướng dẫn giúp em sử dụng hàm vlookup dùm, cụ thể như sau :
Ở sheet 2 em muốn kết quả ở cột I, J , K, L (cột em tô màu vàng) truy tìm ở sheet 1 cột b (field name=aaaa) có số chừng từ trùng với số chứng từ của sheet 2 thì đưa ra giá trị sao cho tương ứng ở cột I, J, K, L.

Em đã cài công thức tuy nhiên giá trị đưa ra không như ý em muốn. Do em không hiểu cách sử dụng hàm vlookup rành.Mong anh chỉ chỉ sửa lại dùm em. Ở kết quả của sheet 2 của cột I, J , K, L em muốn kết quả nó tương ứng từng dòng 1 của sheet này. Ngoài hàm vlookup này em kết hợp với hàm if như thế nào nữa.
Chân thành cám ơn anh chị rất nhiều.

DOSNET
26-10-07, 09:55 AM
Em có 1 file trong đó gồm 2 sheet, sheet 1 ở đây của em lấy từ dữ liệu của kế toán và đã được kế toán hạch toán rồi, còn sheet 2 của em là bảng kê bán hàng theo báo cáo thuế hàng tháng mà em lập (Bảng kê 02)

Kính nhờ Anh chị hướng dẫn giúp em sử dụng hàm vlookup dùm, cụ thể như sau :
Ở sheet 2 em muốn kết quả ở cột I, J , K, L (cột em tô màu vàng) truy tìm ở sheet 1 cột b (field name=aaaa) có số chừng từ trùng với số chứng từ của sheet 2 thì đưa ra giá trị sao cho tương ứng ở cột I, J, K, L.

Em đã cài công thức tuy nhiên giá trị đưa ra không như ý em muốn. Do em không hiểu cách sử dụng hàm vlookup rành.Mong anh chỉ chỉ sửa lại dùm em. Ở kết quả của sheet 2 của cột I, J , K, L em muốn kết quả nó tương ứng từng dòng 1 của sheet này. Ngoài hàm vlookup này em kết hợp với hàm if như thế nào nữa.
Chân thành cám ơn anh chị rất nhiều. Bạn xem được chưa nhé

SoiBien
26-10-07, 10:14 AM
[quote=Thuyan.acc]
Ở sheet 2 em muốn kết quả ở cột I, J , K, L (cột em tô màu vàng) truy tìm ở sheet 1 cột b (field name=aaaa) có số chừng từ trùng với số chứng từ của sheet 2 thì đưa ra giá trị sao cho tương ứng ở cột I, J, K, L.
[quote]

Vấn đề của bạn là tìm kiếm theo 2 đk (c/từ & TK) vậy bạn phải sử dụng hàm Sumproduct, bạn có thể tìm hiểu thêm về hàm sumproduct trên Forum này nhé.

Đây là file mình làm cho bạn.
Thân.

DOSNET
26-10-07, 10:33 AM
Cám ơn bác soibien, do không đọc kỹ đầu bài, em tưởng bạn ấy chỉ mắc lỗi do chưa đưa giá trị cột B ở sheet1 về Value, nếu để nguyên cột B như vậy, hàm Vlookup sẽ báo lỗi #REF!

Thuyan.acc
26-10-07, 10:35 AM
Em thành thật cám ơn anh (chị) rất nhiều đã dành ít thời gian để trả lời và dạy cho em. Em sẽ đọc phần hướng dẫn của anh chị chỉ cho em. Nếu có gì chưa rõ em xin hỏi thêm.

Em nhận thấy 2 anh(chị) rất nhiệt tình chỉ cho em thêm hàm =SUMPRODUCT. Đối với em phải nói tất cả những gì anh (chị) chỉ dẫn đều quý báu cả. Em phải học từ đầu và không ngại giấu cái dốt của em.
Trước kia em cài công thức hàm =vlookup do có thể kiểu dữ liệu của 2 sheet không giống nhau nhưng em cũng chưa biết cách để đổi về value nữa. Một phần là dữ liệu của sheet 1 như em trình bày là do data trong chương trình kế toán import ra excel còn sheet 2 là do em lập theo mẫu bảng kê 02 (Hàng hóa, dịch vụ bán ra).

Một lần nữa em xin cám ơn anh(chị) cùng các thành viên trong diễn đàn rất nhiều.
Nhận thấy diễn đàn giaiphapexcel rất hay. Đây nơi giao lưu, trao đổi cùng chia sẽ lẫn nhau. Chúc diễn đàn cùng các anh chị ngày phát triển và luôn đoàn kết

SoiBien
26-10-07, 11:01 AM
Em nhận thấy 2 anh rất nhiệt tình chỉ cho em thêm hàm =SUMPRODUCT...
Em phải học từ đầu
Bạn có thể tìm hiểu thêm về hàm sumproduct tại thread này.
http://www.giaiphapexcel.com/forum/showthread.php?t=59

Thân.

phamduylong
26-10-07, 09:52 PM
Gởi Thầy Long
1/ Khi thoát nó báo lỗi
ActiveWorkbook.Names("mahang").Delete
2/ Khi chọn trong HHList.AddItem (Lọc theo ký tự) thì nó không gán theo Array vừa lọc mà nó lấy theo MaHang.
Thầy xem lại. Và nếu chọn Multi Select thì Nút Ghi hoạt động ra sao.
Cám ơn nhiều.

Đã 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.

anhphuong
27-10-07, 02:28 PM
Hú anphuong bằng cái gì?

Mail : anhphuong1958@yahoo.com
HP : 0913.687.197
Thân !

kelvin
28-10-07, 07:13 AM
"thay đổi nội dung bởi: nvson, 17-10-07 lúc 01:58 PM. Lý do: Cho code vào thẻ CODE ... /CODE] "
Cám ơn nvson, thế là học thêm được vụ thẻ CODE .À quên, thông báo luôn đoạn code trong Sub MyTypeDelete_CellDuplicateInColumn là lấy ý tưởng trên GPE ( hình như cũng của nvson ,và đơn giản đi 1 chút).Trước đây tôi dùng Find() và vòng lặp ( đã ok), nhưng khg rõ ràng, dể hiểu như đoạn code của nvson

nokiano258vn
28-10-07, 12:35 PM
Ở ô C1 tôi có hàm VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0) trả về "Cây nhãn". Tôi muốn sửa trưc tiếp ở ô C1 là "Cây nhãn ở sân trước" có được không?, vì sẽ có cây nhãn ở nhiều chỗ khác nhau. Nghĩa là tôi muốn thêm từ "ở sân trước"

anhtuan1066
28-10-07, 12:41 PM
=VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0)&" ở sân trước"

nokiano258vn
28-10-07, 12:52 PM
Nhưng nếu tối muốn thêm vào giữa "Cây ở sân trước nhãn" thì phải làm sao, nghĩa là tối muốn sửa trực tiếp câu mà hàm Vlookup vừa cho kết quả. VBA có giải quyết được vấn đề này không?

anhtuan1066
28-10-07, 01:12 PM
Cũng ko đến nỗi phải dùng đến VBA đâu... bạn từ từ đặt cột phụ rồi suy luận cũng ra... Nói chung là dùng các hàm tách chuổi... Nếu ko dùng cột phụ thì làm thế này đây:

=LEFT(VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0),FIND(" ",VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0),1)-1)&" ở sân trước "&RIGHT(VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0),LEN(V LOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0))-FIND(" ",VLOOKUP($A6;DGTS2044!$A$4:$E$1500;3;0),1))

nokiano258vn
28-10-07, 01:27 PM
Thực ra vấn đề ở chỗ không phải chỉ có giá trị "cây nhãn" mà có những giá trị dài đến nhiều từ, có thể sửa không nhất thiết là ở giữa, ở đầu hay ở cuối mà sửa ở vị trí bất kỳ, nếu áp dụng hàm của anhtuan thì không thể linh động được.

nokiano258vn
28-10-07, 02:53 PM
Tôi gửi file lên nhờ các bác giúp, tôi muốn khi nhập mã thì cột C6 trả về dạng text để tiện cho việc swar chữa

nokiano258vn
29-10-07, 06:25 PM
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
29-10-07, 06:42 PM
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
30-10-07, 01:27 PM
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
30-10-07, 01:50 PM
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)*(righ t(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
30-10-07, 02:38 PM
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
30-10-07, 02:53 PM
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
30-10-07, 04:01 PM
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
30-10-07, 04:23 PM
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
30-10-07, 04:34 PM
Dùng công thức

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

TERU
30-10-07, 05:13 PM
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
30-10-07, 07:05 PM
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
30-10-07, 08:00 PM
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
31-10-07, 11:49 AM
Đã 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
31-10-07, 11:50 AM
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
31-10-07, 12:04 PM
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
31-10-07, 04:51 PM
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
31-10-07, 07:30 PM
=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(c 4<$f$21,2,if(c4<$f$22,3,4)),0))

TERU
31-10-07, 10:19 PM
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
31-10-07, 10:26 PM
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
31-10-07, 10:29 PM
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

TERU
31-10-07, 10:53 PM
To Mr Okebab: Teru o fải tự sắp xếp số liệu...mà cái này là do đề bài nó sắp sếp như thế!!!( Nó cũng làm Teru crazy từ trưa đến giờ....nhìn ko biết bắt đầu làm từ đâu)
To Tuấn: Yep, đúng như Tuấn nói nếu bảng chính là 2006- bảng dò 2002 thì vô lý...( but đề bài nó thía đấy Tuấn áh!!!)...But Teru đã tự sửa lại toàn bộ là 2006....và đã ra kết quả như mong muốn!!!

còn 1 vấn đề Teru ko giải quyết ra trong bài này đó là tính tổng số lượng các mặt hàng.
Đề bài cho một bảng hai cột: Mã hàng và tổng số lượng.....ví dụ nhé:
A (MÃ HÀNG) B(TỔNG SỐ LƯỢNG)
30 NO83 =sumif(left(a4,4),a30,e4:e9)
31 MO90
32 SA20
But máy báo lỗi là ghi hàm sai.......Teru ko biết sai chỗ nào........Mong các bạn chỉ giúp!
Thanks

anhtuan1066
31-10-07, 10:59 PM
Tại cell C14 bạn gõ công thức:
=SUMIF($B$4:$B$9,B14,$E$4:$E$9)
Rồi kéo fill xuống... Nếu mà bạn dùng LEFT thì có mà chết... lúc đó là phải dùng công thức mãng hoặc hàm SUMPRODUCT.. e rằng bạn chưa học tới
Nói thêm 1 chút: SUMIF( mãng chứa điều kiện, điều kiện, mãng cần cộng)
Với Bắp: Cái này là BT Bắp ơi!
Mến
ANH TUẤN

phamduylong
31-10-07, 11:41 PM
Cám ơn Thầy nhiều lắm.
HHList có thể chuyển thành HHArray(1 to rc01) được không? Chỉ lấy mã hàng thôi..

(1) HHList.AddItem r
(2) HHList.List(HHList.ListCount - 1, 1) = S01.Cells(r, 1)
(3) HHList.List(HHList.ListCount - 1, 2) = S01.Cells(r, 2)
Có thể bỏ dòng (2) hoặc (3) chứ không thể bỏ dòng (1) vì có AddItem xong, mới có thể ghi dữ liệu vào cột 2, 3 của ListBox. Mặc khác (1) ghi stt dòng, không có nó, không biết dữ liệu nguồn nằm ở dòng nào để lấy ghi vào NHAP được.


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.
Không thể sử dụng 2 đối số Myarray1, Myarray2 trong công thức được vì đây là 2 array chứ không phải name.
Câu lệnh này cho kết quả tương đương công thức trên, nhưng chỉ ghi kết quả chứ không ghi công thức.
Range("E1") = WorksheetFunction.SumIf(Range("b1:b10"), Range("D1"), Range("a1:a10"))

ThuNghi
01-11-07, 02:33 PM
Range("E1") = WorksheetFunction.SumIf(Range("b1:b10"), Range("D1"), Range("a1:a10"))
Trong khi Range("b1:b10") là MyArray2 thì làm thế nào hiểu công thức có mảng.
Xin cho 1 giải pháp. Cám ơn!

Thuyan.acc
01-11-07, 05:17 PM
Em muốn khai thác dữ liệu ở 3 sheet, sheet bke, sheet ttoan và sheet code
Ở sheet bke (Bảng kê bán hàng) và sheet ttoan (file dữ liệu của kế toán cung cấp các thông tin thu tiền của khách hàng bằng tiền mặt hoặc tiền gởi)
Ở sheet bke (Bảng kê bán hàng) từ cột O,P,Q,R,S em thiết lập các vùng để theo dõi các phần thanh toán của khách hàng qua sheet ttoan.
Yêu cầu : Truy tìm :
1.- Các số chứng từ của (sheet ttoan : cột E - hoadon) bằng với sheet bke (cột B – Số)
và số tiền của (sheet ttoan : cột H – sotien) bằng với sheet bke số tiền (cột H)
Đưa kết quả “lấy số tiền của sheet ttoan” này đưa vào cột S của sheet bke.
Ở phần này em sử dụng hàm sumproduct theo các anh chị hướng dẫn cho em như những bài trước đây.
=SUMPRODUCT((ttoan!$E$2:$E$219=bke!$B4)*(ttoan!$H$ 2:$H$219=bke!$H4)*ttoan!$H$2:$H$219)

Ở phần này em vướng mắc :
Có khách hàng thanh toán nhiều lần cho 1 hóa đơn so với bảng kê bán hàng của em. Một hóa đơn có khi khách trả 1 phần bằng tiên mặt và 1 phần qua ngân hàng. Ở tình huống này em phải xử lý sao ?
Cụ thể ở sheet ttoan các dòng số liệu được tô màu xanh lá cây.

2.- Ở sheet bke (Bảng kê bán hàng) cột O,P
Cột O – có tên vùng tạm gọi TM (Tiền mặt)
Cột P – có tên vùng tạm gọi CK (Chuyển khoản)
ở sheet bke (Bảng kê bán hàng) này em muốn tham chiếu qua sheet ttoan (cột A – Mã ) cùng với sheet code của (cột D – Viết tắt) để đưa các giá trị tương ứng của sheet code vào sheet bke (Bảng kê bán hàng) cột O hoặc P.

3. Ở sheet bke (Bảng kê bán hàng) cột Q,R. Khi em cài hàm sumproduct thì các kết quả không tìm thấy của (cột Q- ngày) thì cột ngày lại trả về giá trị như 00/01/00
và của (cột R - số chứng từ) lại trả về giá trị 0.
Ở phần này em chưa rõ lắm lý do sao hàm sumproduct lại đưa ra kết quả này.
Có cách nào không thể hiện các giá trị do hàm sumproduct tạo ra không. Vì nếu để như thế thì bảng tính nó đầy cả.

4.- Trong sheet ttoan của kế toán chuyển xuống cho em thì không có cột (E – hóa đơn)
Để làm được các việc nêu trên em mới chèn thêm vào cột E này.
Ở cột D thì là nội dung rất nhiều như : Thu hóa đơn số xxxxx của Ông A gì đó.
Và mỗi nội dung dài ngắn khác nhau. Em phải copy nội dung của cột D vào E rồi lần lượt em dùng F2 (edit) lại lần lượt để trích số hóa đơn đó ra. Anh chị có cách nào để mình trích lọc số hóa đơn chứa trong cột D này sang cột E không ? Ở đây em làm quá thủ công công đoạn này. Có sử dụng thử hàm left, right của E hoặc hàm mid nhưng thấy không thuyết phục lắm.

Em xin cám ơn tất cả các Thầy, Cô cùng các anh chị quan tâm hướng dẫn cho em làm bài này nhằm để phục vụ công tác của em.
Cũng mong các thành viên có quan tâm cùng nghiên cứu sau khi Thầy, Cô cùng các anh chị đúc kết hướng dẫn cho mình.
Kính.
-------------------------------------------------------------
5.- Ở sheet bke (Bảng kê bán hàng) Từ cột I, J, K, L,M,N em cài các công thức như sau :
Ở cột I =IF($E4=0,D4,0) và cột J =IF($E4=0,F4,0)
Ở cột K=IF(AND($E4<=0.05,$E4>0),D4,0) và cột L=IF(AND($E4<=0.05,$E4>0),F4,0)
Ở cột M=IF(AND($E4>=0.1,$E4>0),D4,0) và cột N=IF(AND($E4>=0.1,$E4>0),F4,0)
Mục đích : Trích các số liệu từ cột $D (tiền hàng trước thuế) và cột $F (Thuế GTGT) để đưa sang các cột tương ứng I, J, K, L,M,N sao cho thỏa mãn ở điều kiện của cột E
Anh chị có thể cho ý kiến công thức này em sử dụng vậy ổn chưa hay có cách nào khác ổn định hơn. Xin vui lòng chỉ cho em thêm

TERU
01-11-07, 05:47 PM
Thanks Tuấn....Mới đầu Teru cũng tính làm như vậy , but thấy đề bài nó kẻ ra một bảng riêng ( chỉ gồm hai cột như Teru nói ở trên) và yêu cầu tính tổng số lựong nên Teru o biết làm như thía nào......
ps: cho Teru hỏi:" Với Bắp: Cái này là BT Bắp ơi!"có nghĩa là gì thế Tuấn......Teru o hiểu???
Anyway, thanks Tuấn nhiều nhá
TERU

ThuNghi
02-11-07, 10:52 AM
SoiBien ơi! giúp hộ vấn đề trên.
Cụ thể như sau:
Ta có cột ngày, mã KH và ST
Tạo 1 array thỏa điều kiện ngày = ngày -> lấy số tiền là ArrayST, lấy Mã KH là ArrayKH, lúc này ta muốn dùng sumif thì ???
Nếu dùng cells tạm gán vào thì dùng range. Nhưng ở đây là bỏ qua gán vào Cells.

ThuNghi
02-11-07, 04:17 PM
1.- Các số chứng từ của (sheet ttoan : cột E - hoadon) bằng với sheet bke (cột B – Số)
và số tiền của (sheet ttoan : cột H – sotien) bằng với sheet bke số tiền (cột H)
Đưa kết quả “lấy số tiền của sheet ttoan” này đưa vào cột S của sheet bke.
Ở phần này em sử dụng hàm sumproduct theo các anh chị hướng dẫn cho em như những bài trước đây.
=SUMPRODUCT((ttoan!$E$2:$E$219=bke!$B4)*(ttoan!$H$ 2:$H$219=bke!$H4)*ttoan!$H$2:$H$219)

Ở phần này em vướng mắc :
Có khách hàng thanh toán nhiều lần cho 1 hóa đơn so với bảng kê bán hàng của em. Một hóa đơn có khi khách trả 1 phần bằng tiên mặt và 1 phần qua ngân hàng. Ở tình huống này em phải xử lý sao ?
Cụ thể ở sheet ttoan các dòng số liệu được tô màu xanh lá cây.

Cái này chỉ đúng khi thanh toán 1 lần hết hóa đơn đó.
Vấn đề là Sh BKe phần theo dõi thanh toán bạn muốn tính cái gì,
- Nếu tính tổng số tiền thanh toán cho hđ thì bạn không cần có đoạn này (ttoan!$H$ 2:$H$219=bke!$H4)
Lúc này số CT và ngày CT thanh toán sẽ không lấy được.
- Nếu muốn liệt kê thì sang sh thanhtoan mà xem.
Nói chung là khó gom lại vào 1 sh vừ theo dõi tiến độ thanh toán vừa theo dõi bán hàng.
Mói xem có từng đó, sẽ xem tiếp sau.

aihieulongtoi
02-11-07, 09:20 PM
lam sao ma tao dc bang nhap do hay vay ban co the chi cho minh dc chu
cam on baqn
co chu "nhap hang " do lam sao ma co dc thong tin do vay

ThuNghi
02-11-07, 09:49 PM
Phần 5
Ở cột I =IF($E4=0,D4,0) và cột J =IF($E4=0,F4,0)
Ở cột K=IF(AND($E4<=0.05,$E4>0),D4,0) và cột L=IF(AND($E4<=0.05,$E4>0),F4,0)
Ở cột M=IF(AND($E4>=0.1,$E4>0),D4,0) và cột N=IF(AND($E4>=0.1,$E4>0),F4,0)
VAT thì chỉ có 3 mức: 0, 5, 10 (E4)
Tại sao các cột K,L và M,N không dùng như cột I và J mà phải > và <
Phần trên #6 đã trả lời cho phần 2, 3 rồi.
Còn mục 4, không thấy cái gì chung hết nên phải tách thủ công thôi, tôi cũng đã làm như vậy do phải lấy dữ liệu từ BP khác. Đành phải F2 edit thôi. Còn nếu số HĐ giống như tên trong họ tên thì có thể dùng 1 hàm VBA.