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

[DOSNET]

Hi,
Có cao thủ nào giúp mình không? Chẳng lẽ mình bó tay, các bạn cũng bó tay luôn???

Tôi chịu rồi vì đọc mãi vẫn không hiểu cách tính vì ko có nghiệp vụ KToán, nhờ bác Thunghi hoặc bác Anhtuan1066 thôi !

 

[lypt]

Đọc mãi mới hiểu được một chút

Bạn xem thử file này.

 

[boyxin]

Mình xin giải thích lại cách phân tiền như sau, mời các bạn xem file tháng 3:
-- Số tiền ở các cột trị giá được hiểu vừa là số tiền nợ vừa là số tiền có của khách hàng (KH). Sau khi lấy khoản thanh toán trừ đi các khoản phát sinh của KH thì số tiền còn dư lại trở thành khoản "có" của KH, còn không thì là khoản "nợ".

-- Khoản tiền ở ô Ô6 chính là khoản nợ của KH sau lấy khoản nợ ơ ô G6 trừ đi khoản "có" còn lại trong tháng 2. Hai dòng ngày 28 & 29/2 có nghĩa là KH thanh toán xong số tiền phát sinh trong tháng 2, phải dời qua tháng 3 tiếp tục thanh toán.

Vd: chúng ta bắt đầu phân tiền từ tháng mới như sau:
-- Các bạn thấy ngày 3/3 KH thanh toán 350tr, lẽ ra phải lấy 350tr này trừ đi ô Ô6 (Ô6 là số tiền còn nợ của KH khi lấy khoản nợ ở ô G6 trừ đi khoản có còn lại của tháng 2) sẽ ra M7. Nhưng nếu lấy 350tr trừ đi khoản nợ ở ô Ô6 thì số tiền còn lại (số tiền "có") của KH vẫn còn > số tiền phát sinh là 163,231.25 ở G7 (có nghĩa là chưa thanh toán hết số tiền 350tr của KH). Như vậy phải tiếp tục phân tiền bằng cách đem số tiền ở G7 qua M7

-- Vì số tiền chưa phân hết nên ngày 3/3 đem xuống L9 và tiếp tục phân. Trị giá ở M9=I11 - O6 - M7 (=8,743.00) < G9 (có nghĩa là số tiền thanh toán 350tr của KH kể như đã phân xong.

-- Tiếp tục phân qua ngày 4/3. Ngày 4/3 KH thanh toán 200tr. Lẽ ra O9=G9-M9 nhưng vì G9-M9 > 200tr nên phải đưa khoản I12 lên O9 (có nghĩa là hai khoản M9 và O9 là hai khoản có của KH), còn khoản G9 là khoản nợ . Vậy Q9=G9-M9-O9 (Q9 bây giờ trở thành khoản nợ của KH)

-- Vì ngày 7/3 KH mới thanh toán nên P9 là ngày 7/3. KH thanh toán khoản I15 thì M10=I15-Q9. Ta không đem khoản G10 qua M10 vì I15-Q9 < G10, như vậy khoản I15 kể như đã phân xong và M10 bây giờ là khoản "có" của KH. Tiếp tục phân như thế cho đến hết các khoản thanh toán có nghĩa là I42=0 thì mới đúng.

-- Bây giờ thì các bạn đã rõ, mặc dù các ô ở cột trị giá có công thức nhưng phải nhập bằng tay mỗi ngày, mình muốn cài công thức chung để khi nhập khoản thanh toán vào thì số tiền ở các ô của cột trị giá tự động phân luôn. Mong các bạn giúp mình vì việc nhập như thế này rất mất thời gian, có rất nhiều khách hàng như vậy thì không thể nào nhập xuể được, các cao thủ ra tay giúp mình nhé. Cám ơn các cao thủ rất nhiều!!!

Xin lưu ý: ngày nào có phát sinh thì mới phân tiền nhé các bạn!

T. Anh

Theo mình hiểuđang nói đến File THANG 03-2008, Sheet K SON)

Tính đến một ngày bất kỳ ở cột A thì

1. Nợ đầu ngày (cột H) = Nợ tồn tính đến ngày hôm trước (Cột J)
2. Nợ tồn cuối ngày (Cột J) = Nợ đầu ngày (Cột H) + Phát sinh thêm trong ngày (Cột G) - Thanh toán trong ngày (Cột I)

Ví dụ: Tính đến ngày 11/3/2008

1. H19 = J18 = 853,709.70
2. J19 = H19 + G19 - I19 = 853,709.70 + 183,239.93 - 408,185.80 = 628,763.83

Vậy Tính đến cuối ngày 11/3/2008 thì KH có số Nợ tồn là 628,763.83 (đúng với số liệu trong File đính kèm)

1. Ngày L13 = 11/3/2008 thì giá trị M13 được tính thế nào?
2. Sao trong File đính kèm lại tính M13 = G13 (G13 là Phát sinh của ngày 5/3/2008) ?

 

[trantuonganh2008]

Cảm ơn bạn Lypt nhiều vì đã công phu viết marco cho bài này, nhưng cũng chưa đáp ứng được yêu cầu của mình. Mình muốn khi xoá đi các khoản thanh toán thì các khoản trị giá ở cột trị giá cũng "đi theo" luôn chứ không phải "trơ trơ" như bạn đã làm. Yêu cầu là khi nhập thanh toán vô là các ô cột trị giá tự động hiện ra mà không có "miễn cưỡng" nào. Nhập thanh toán ngày nào thì phân tiền ứng với ngày đó. Mong bạn cố gắng giúp mình nhé! Trả lời bạn Boyxin: Như mình đã giải thích ở trên thì bạn sẽ thấy O12 là khoản nợ chứ ko phải khoản có. Ngày 11/3 KH thanh toán khoản I19=408,185,800, thông thường thì M13=I19-O12, nhưng như vậy thì M13 vẫn còn > G13 (điều này có nghĩa là khoản I19 chưa phân xong hết) nên buộc phải đưa G13 qua =M13. Tương tự bạn thấy G14=M14 là như vậy đó. Qua dòng tiếp theo thì khác, bạn ko thể đem G15=M15 được vì I19-O12-M13-M14 lúc này < G15 nên buộc M15=I19-O12-M13-M14. Có lẽ bạn đã hiểu rồi nhé, cám ơn vì đã nhiệt tình giúp đỡ!!!

 

[boyxin]

Theo mình hiểuđang nói đến File THANG 03-2008, Sheet K SON)

Tính đến một ngày bất kỳ ở cột A thì

1. Nợ đầu ngày (cột H) = Nợ tồn tính đến ngày hôm trước (Cột J)
2. Nợ tồn cuối ngày (Cột J) = Nợ đầu ngày (Cột H) + Phát sinh thêm trong ngày (Cột G) - Thanh toán trong ngày (Cột I)

Ví dụ: Tính đến ngày 11/3/2008

1. H19 = J18 = 853,709.70
2. J19 = H19 + G19 - I19 = 853,709.70 + 183,239.93 - 408,185.80 = 628,763.83

Vậy Tính đến cuối ngày 11/3/2008 thì KH có số Nợ tồn là 628,763.83 (đúng với số liệu trong File đính kèm)

1. Ngày L13 = 11/3/2008 thì giá trị M13 được tính thế nào?
2. Sao trong File đính kèm lại tính M13 = G13 (G13 là Phát sinh của ngày 5/3/2008) ?

 

[boyxin]

Xin chào các bạn,
Nhờ các bạn giúp mình giải quyết vấn đề này, mình có gửi file cho các bạn để tham khảo.
Đó là cách phân tiền sao cho mỗi khi nhập khoản thanh toán vào là flie tự động phân tiền, không cần phải phân tiền bằng cách đánh tay nữa.
Cách phân tiền như sau, mong các bạn hiểu khi mình giải thích:
- Khoảng tiền cần tự động hiển thị (khi nhập vào khoảng thanh toán nào đó) nằm ở cột trị giá.
- Cột trị giá cũng có nghĩa là cột phát sinh của khách hàng, phân tới khi nào số tiền thanh toán (sau khi trừ đi các khoản ở cột trị giá) < số tiền phát sinh thì thôi.
- Ngày nào không có phát sinh thì ngày đó không phân tiền.
- Phân tiền khi cuối cột thanh toán = 0 & cột Pt Date checking toàn chữ ok là đúng.

Cám ơn các bạn nhiều nhiều!!!

Bạn kiểm tra lại kết quả nhé
Xem File, đoán mò làm đại, không biết kết quả đúng hay sai

Khi lập sheet mới thì J4 = Tồn của Sheet trước
​

 

[trantuonganh2008]

Tuyệt vời, cám ơn bạn nhiều lắm!!! Nhưng còn một số điều mình muốn nhờ bạn giúp luôn: ---- Bạn làm giúp công thức ở các ô ở cột ngày luôn nhé, mình muốn nó hiện lên giống như cột trị giá luôn. Tức là chỉ nhập khoản thanh toán thôi là các ô ở cột ngày & trị giá tự động hiện lên luôn. Bạn nên cài công thức ở tất cả các ô ở 2 cột này vì khoản thanh toán đâu biết trước được, file mình gửi là đã biết khoản thanh toán trước, còn khi làm việc thì đâu có biết ngày nào KH sẽ thanh toán cho mình đâu. ---- Bạn giải thích giúp những công thức mà bạn sử dụng nhé để mình còn cài lại cho những bảng tiếp theo vì mỗi tháng mình phải làm báo cáo như vậy & cũng rất nhiều KH như thế. Cám ơn nhiều lắm!!! Xin đa tạ!!!

 

[boyxin]

Tuyệt vời, cám ơn bạn nhiều lắm!!! Nhưng còn một số điều mình muốn nhờ bạn giúp luôn:
---- Bạn làm giúp công thức ở các ô ở cột ngày luôn nhé, khi làm việc thì đâu có biết ngày nào KH sẽ thanh toán cho mình đâu.

bạn cũng phải nói rõ yêu cầu về thời hạn thanh toán thì mới biết đường mà lập công thức
Cụ thể: khi nào thì điền 3 lần xác định giá trị KH, khi nào thì chỉ có 1 lần xác định giá trị KH, ngày xác định giá trị KH so với thời hạn thanh toán (ở cột K) thì ngày xác định giá trị KH cần điền công thức có quan hệ ràng buộc gì không?? vv....

bạn bố trí dữ liệu kiểu này, nhìn vào hoa cả mắt, không theo quy luật nên rất khó lập công thức

 

[trantuonganh2008]

Hi Boyxin, Thời hạn xác định thanh toán của KH là cột "ngày đến hạn" nhưng xin nói rõ là ngày thanh toán của KH thì ko biết ngày nào. Tức là KH muốn thanh toán ngày nào thì tùy. Nếu thanh toán chậm thì bị phạt được tính ở cột "thu hồi", còn thanh toán trước ngày đến hạn thì được thưởng và được tính ở cột "hỗ trợ". Nhìn công thức ở 2 cột này bạn cũng thấy rõ. Còn việc xác định giá trị của KH 1 lần hay 2 lần v.v... thì tùy thuộc vào khoản thanh toán của KH. Nếu như KH thanh toán số tiền mà sau khi trừ đi cột "trị giá" < số tiền ngày phát sinh thì số tiền này coi như đã phân xong, ngược lại thì phải đem số tiền phát sinh qua cột trị giá để phân tiền tiếp (có nghĩa là phải phân số tiền KH thanh toán cho tới hết, có nghĩa là số dư của phần thanh toán luôn luôn < số tiền phát sinh, nếu phần dư của tiền thanh toán vẫn còn > số tiền phát sinh thì tiếp tục phân tiền. Còn nữa------ Nếu số tiền thanh toán của KH ít (tức là khi bạn lấy số tiền thanh toán+số tiền ở cột giá trị vẫn < số tiền phát sinh thì buộc phải đem số tiền thanh toán lên ô ở cột trị giá và ngày ghi ở cột "ngày" là ngày thanh toán - ngày ghi ở cột "ngày" đơn giản là ngày thanh toán) . Sau đó lấy số tiền phát sinh - số tiền ở cột trị giá - số tiền thanh toán (- số tiền thanh toán của ngày tiếp theo nếu như sau khi cộng các khoản nói trên vẫn còn < số tiền phát sinh). Chính vì thế mà bạn thấy có nhiều cột "ngày" & "trị giá" là lý do đó. Bây giờ chắc bạn đã rõ, mong bạn giúp mình nhé! Cám ơn bạn!!!

 

[boyxin]

Hi Boyxin,
Thời hạn xác định thanh toán của KH là cột "ngày đến hạn" nhưng xin nói rõ là ngày thanh toán của KH thì ko biết ngày nào. Tức là KH muốn thanh toán ngày nào thì tùy. Nếu thanh toán chậm thì bị phạt được tính ở cột "thu hồi", còn thanh toán trước ngày đến hạn thì được thưởng và được tính ở cột "hỗ trợ". Nhìn công thức ở 2 cột này bạn cũng thấy rõ.

Còn việc xác định giá trị của KH 1 lần hay 2 lần v.v... thì tùy thuộc vào khoản thanh toán của KH. Nếu như KH thanh toán số tiền mà sau khi trừ đi cột "trị giá" < số tiền ngày phát sinh thì số tiền này coi như đã phân xong, ngược lại thì phải đem số tiền phát sinh qua cột trị giá để phân tiền tiếp (có nghĩa là phải phân số tiền KH thanh toán cho tới hết, có nghĩa là số dư của phần thanh toán luôn luôn < số tiền phát sinh, nếu phần dư của tiền thanh toán vẫn còn > số tiền phát sinh thì tiếp tục phân tiền. Còn nữa------ Nếu số tiền thanh toán của KH ít (tức là khi bạn lấy số tiền thanh toán+số tiền ở cột giá trị vẫn < số tiền phát sinh thì buộc phải đem số tiền thanh toán lên ô ở cột trị giá và ngày ghi ở cột "ngày" là ngày thanh toán - ngày ghi ở cột "ngày" đơn giản là ngày thanh toán) . Sau đó lấy số tiền phát sinh - số tiền ở cột trị giá - số tiền thanh toán (- số tiền thanh toán của ngày tiếp theo nếu như sau khi cộng các khoản nói trên vẫn còn < số tiền phát sinh). Chính vì thế mà bạn thấy có nhiều cột "ngày" & "trị giá" là lý do đó. Bây giờ chắc bạn đã rõ, mong bạn giúp mình nhé! Cám ơn bạn!!!

Tại sao:

1. Trong bảng theo dõi tháng 3 lại có từ ngày 28/2
2. Khi KH thanh toán lần đầu tiên trong tháng là ngày 3/3 lại được dùng để so sánh với phát sinh của ngày 28/2
3. Vậy: nếu KH thanh toán lần đầu tiên trong tháng là ngày 13/3 thì số tiền TT này được dùng để so sánh với số phát sinh thêm của ngày nào?

Theo mình: cuối mỗi ngày đề có theo dõi phần Tồn cuối ngày, Lấy số tồn này so với tổng số thanh toán kể từ ngày hôm sau đến 7 ngày sau để tính xem KH đó được hỗ trợ trả trước hay là chịu nộp phạt thu hồi thì tính toán sẽ đơn giản hơn nhiều

 

[nhattan1311]

Làm sao dùng hàm Sumif lồng hàm Vlookup

Mình muốn tính Sum unđerpai tính theo từng tháng phải dựa vào bảng tính thứ 1 ở sheet Obversation và sheet Filled_Jos.
Dữ liệu bảng 1 ở sheet Observation được copy từ sheet Filled_jobs

 

[huuthang_bd]

Mình muốn tính Sum unđerpai tính theo từng tháng phải dựa vào bảng tính thứ 1 ở sheet Obversation và sheet Filled_Jos.
Dữ liệu bảng 1 ở sheet Observation được copy từ sheet Filled_jobs

Không biết bảng tính thứ 1 ở sheet Obversation bạn dùng để làm gì nhưng bạn thử dùng công thức sau tại ô B140 và fill xuống.

=SUMIF(Filled_Jobs!$D$4:$D$138,Observations!A140,Filled_Jobs!$K$4:$K$138)

 

[trantuonganh2008]

Chào bạn, 2 dòng đầu tiên của tháng 2 mà bạn thấy là số nợ tồn còn lại của tháng 2 mà kh chưa thanh toán xong, phải đem qua tháng 3 thanh toán tiếp. Bạn thấy số tiền ở cột trị giá được phân sau hết của ngày tháng 2 sau khi phân xong, kh vẫn còn nợ, tức là ngày thanh toán cuối cùng của kh không đủ trả ứng với số tiền phát sinh của họ. Còn việc thanh toán đầu tiên của kh ngày nào thì mình chỉ việc phân tiền ngày thanh toán thôi. Chẳng hạn bạn hỏi nếu ngày đầu tiên kh thanh toán là ngày 13 thì bạn cứ việc phân tiền ngày 13, tức là ghi ngày 13 ở đầu ngang với ngày 28/2 và phân tiền bình thường. Vậy bạn nhé!!!

 

[lehoangtam]

Vlookup 2 điều kiện

Thân gửi các bạn!

Tôi có dữ liệu chứng khoán được xếp theo ngày và mã chứng khoán.

Tôi muốn chọn dữ liệu vừa theo mã chứng khoán, vừa theo ngày.

Tôi đã thử nhiều cách mà chưa được.

Mong các bạn giúp đỡ.

 

[NguyenNgocSon]

Vlookup 2 điều kiện

Mình đã làm giúp bạn xử lí vấn đề đó.
Mình chọn ra 1 số nội dung làm rồi,bạn có thể tham khảo
Nếu cần liên hệ mail: ngocson.gtvt@gmail.com
Hi vọng các thành viên giúp đỡ nhau cho GPE ngày càng phát triển!!!

 

[huuthang_bd]

Mình đã làm giúp bạn xử lí vấn đề đó.
Mình chọn ra 1 số nội dung làm rồi,bạn có thể tham khảo
Nếu cần liên hệ mail: ngocson.gtvt@gmail.com
Hi vọng các thành viên giúp đỡ nhau cho GPE ngày càng phát triển!!!

Mình xin góp một vài ý kiến sau khi xem file bạn làm:
1. Dữ liệu gốc là dữ liệu lấy từ nguồn khác nếu phải thiết kế lại thì rất mất thời gian.
2. Làm theo cách của bạn thì nếu dữ liệu có n ngày thì bạn phải lập n name dulieu.

Thân gửi các bạn!

Tôi có dữ liệu chứng khoán được xếp theo ngày và mã chứng khoán.

Tôi muốn chọn dữ liệu vừa theo mã chứng khoán, vừa theo ngày.

Tôi đã thử nhiều cách mà chưa được.

Mong các bạn giúp đỡ.

Bạn xem file đính kèm xem vừa ý chưa nhé.

 

[boyxin]

Chào bạn,
2 dòng đầu tiên của tháng 2 mà bạn thấy là số nợ tồn còn lại của tháng 2 mà kh chưa thanh toán xong, phải đem qua tháng 3 thanh toán tiếp. Bạn thấy số tiền ở cột trị giá được phân sau hết của ngày tháng 2 sau khi phân xong, kh vẫn còn nợ, tức là ngày thanh toán cuối cùng của kh không đủ trả ứng với số tiền phát sinh của họ.

Còn việc thanh toán đầu tiên của kh ngày nào thì mình chỉ việc phân tiền ngày thanh toán thôi. Chẳng hạn bạn hỏi nếu ngày đầu tiên kh thanh toán là ngày 13 thì bạn cứ việc phân tiền ngày 13, tức là ghi ngày 13 ở đầu ngang với ngày 28/2 và phân tiền bình thường. Vậy bạn nhé!!!

Công thức chắc là khó xài trong trường hợp bạn muốn thế này

Có giải pháp dùng tạm trong File đính kèm
(Trong đó mượn code của lypt và chỉnh sửa tý xíu


​

 

[lehoangtam]

Chưa làm được

Tôi đã làm thử, không biết sai chỗ nào mà chưa ra đúng kết quả.

Mong các bạn chỉ giúp.

Thanks.

 

[ThuNghi]

Tôi đã làm thử, không biết sai chỗ nào mà chưa ra đúng kết quả.

Mong các bạn chỉ giúp.

Thanks.

Có phải bạn muốn lấy chỉ số Open theo ngày và theo mã...
File của bạn dùng Excel 2007, sao bạn không thử hàm SumIfs, còn Excel 2003 thì dùng sumsproduct.
Chưa xem hết ct của bạn nhưng ở Data không có ngày 18/11/2008 =?NA
Với lại nếu lấy thống tin theo MaCP theo ngày thì không cần công thức cột ngày làm gì. Chỉ cần Index, Match là liệt kê ra ngay, cao cấp hơn là dùng AdFi.

 

[huuthang_bd]

Tôi đã làm thử, không biết sai chỗ nào mà chưa ra đúng kết quả.

Mong các bạn chỉ giúp.

Thanks.

Bạn chuyển sang file Excel 2003 rồi up lại file được ko?