Tìm MATCH như thế nào trong file này???

[anhtuan1066]

Câu hỏi tôi có ghi trong file, nhờ các bạn góp ý, đại khái tôi muốn biết vị trí của 1 dử liệu nào đó đang nằm thứ mấy trong danh sách
ANH TUẤN

 

[BNTT]

Trong danh sách của bác không có ngày 01/2/2006.
Có phải ý bác muốn hỏi ngày nhỏ nhất trong danh sách >= 01/2/2006 ?

 

[anhtuan1066]

Ý tôi muốn tìm ngày đầu tiên của tháng 2 trong ds.. Theo như trong file thì nó chính là ngày 5/2/2006, vậy nó đứng vị trí thứ 7 trong ds... Cái quan trong là công thức nào đễ ra dc số 7 này...
Ví dụ tôi thay G6 = 3 thì vị trí tôi cần tìm phải là 12, tức ngày 2/3/2006
ANH TUẤN

 

[BNTT]

Bác dùng thử công thức sau (ở cell I6)

=IF(ISNA(MATCH(DATE($F$6;$G$6;1);DS;1));1;MATCH(DATE($F$6;$G$6;1);DS;1)+1)
​

Kết quả cho là vị trí thứ 7 đếm từ trên xuống của DS (ngày 05/2)
(Em dùng ISNA để đề phòng bác tìm ngày đầu của tháng 1, khi đó nó sẽ chọn vị trí đầu tiên).

 

[anhtuan1066]

He... he... Cũng rất hay... nhưng mà.. hơi dài... ngắn hơn nữa dc ko?
Hic...
------------------------------------
Ah... Ah... vừa kiểm tra thấy có sai nha!... Sai ở những tháng có ngày 1
ANH TUẤN

 

[BNTT]

Tại sao cái chữ DATE ở phần sau của công thức em gửi (#4), nó cứ nhảy cách ra thành DA TE vậy bác? Em lôi về Edit 2 lần rồi, nó vẫn vậy?

He... he... Cũng rất hay... nhưng mà.. hơi dài... ngắn hơn nữa dc ko?
Hic...
ANH TUẤN

Khả năng của em chỉ "dài" nhiêu đó thôi bác à, muốn ngắn hơn... CÁC BÁC KHÁC ƠI!

 

[salam]

Em thử CT của bác BNTT thấy không ổn à, nếu thay đổi ngày ở DS sao CT nó không chạy nhỉ.

 

[anhtuan1066]

Món này lúc trước tôi đã bị vướng, trong danh sách ngày tháng năm dc sếp tăng dần nhưng ko theo trật tự thì thật khó mà lấy MATCH... tôi cũng đã nghĩ đến MATCH có tham số 1.. nhưng vẫn bị sai trong trường hợp có ngày 1 đầu tháng... Còn nếu MATCH với tham số 0 thì lại sai với những tháng thiếu ngày 1...
Xem ra vụ này cũng ko dễ nhai đây!

Em thử CT của bác BNTT thấy không ổn à, nếu thay đổi ngày ở DS sao CT nó không chạy nhỉ.

DS là cố định, bạn ko dc thay đỗi, chỉ thay đổi F6 và G6 thôi... Còn như bạn muốn thay đỗi dử liệu trong DS thì phải tuân thủ quy luật tăng dần của dử liệu nha!
Về công thức của BNTT thì.. gần đúng chứ chưa chính xác
Nhờ các bạn xem lại giùm
ANH TUẤN

 

[BNTT]

... tôi cũng đã nghĩ đến MATCH có tham số 1.. nhưng vẫn bị sai trong trường hợp có ngày 1 đầu tháng... Còn nếu MATCH với tham số 0 thì lại sai với những tháng thiếu ngày 1...

Về công thức của BNTT thì.. gần đúng chứ chưa chính xác

Lại phải dài hơn nữa rồi:

=IF(ISNA(MATCH(DATE($F$6;$G$6;1);DS;1));1;MATCH(DATE($F$6;$G$6;1);DS;1);DS;1)+IF(ISNA(VLOOKUP(DATE($F$6;$G$6;1);DS;1;0));1;0))
​

Cái này chạy đúng với tất cả các ngày trong DS, kể cả ngày 1 (em thử 10 lần rồi).

 

[BNTT]

@ Bác nvson: Bác có thể giải thích thêm lý do gộp bài là sao không ?
(để em không tái phạm nữa)
Cảm ơn bác.

 

[anhtuan1066]

Nếu bạn đã thử rồi thì tôi nghĩ là ổn... Chỉ cần chú ý 2 điểm thôi:
-Ngày đầu tháng ko phải ngày 1
-Ngày đầu tháng là ngày 1
Cám ơn bạn! Nhưng tôi nghĩ sẽ có công thức ngắn hơn đấy! Món này tôi biết chắc là các bạn sẽ gặp rất nhiều khi trich xuất dử liệu của 1 tháng nào đó (vi dụ tính tổng sử dụng trong 1 tháng)... Tất nhiên dùng SUMPRODUCT trên toàn bộ DS thì ra là cái chắc.. nhưng nếu dử liệu có từ 10.000 dòng trở lên thì e rằng sẽ.. treo máy mất.. Thế nên tôi mới nghĩ ra vụ này.. Kết hợp với OFFSET nữa thì xem như ta dc 1 mãng giới hạn chỉ với vài trăm dòng mà thôi.. Như vậy việc tính toán sẽ nhanh hơn rất nhiều lần...
Các bạn thử nghĩ xem!
ANH TUẤN

 

[ThuNghi]

{=MIN(IF(YEAR(DS)=$F$6,IF(MONTH(DS)=$G$6,ROW(DS),"")))}

Cho ra số dòng, còn lại Index thôi. Không biết có đúng?
Thêm ct
=COUNTIF(DS,"<"&DATE(F6,G6,1))+2
Thấy anh Tuấn hỏi sợ quá nghĩ cao quá.

 

[anhtuan1066]

Rất chính xác.. nhưng hàm của ThuNghi vẫn còn phụ thuộc vào ROW.. như trong file này do biết trước dc ROW đầu tiên trong danh sách là 2 nên chỉ cần trừ 1 thì ra dc MATCH... Giã sử ta ko biết trước DS bắt đầu bằng ROW thứ mấy và bắt buộc phải tính dc vị trí thứ tự của dử liệu cần tìm là thứ mấy thì phải làm sao?

 

[BNTT]

...gần đúng chứ chưa chính xác...

Rất chính xác.. nhưng...

Xin lỗi cho em spam một cái.
Phe em mà có 10 người đi nữa chắc cũng không đè nổi bác...

 

[anhtuan1066]

Hồ... hồ... Nghiên cứu mà bạn... Kết quả = TRUE trong mọi trường hợp chính là CHÂN LÝ... đúng ko?
Vã lại tôi thấy vụ này chúng ta cũng thường hay gặp.. hoặc là sẽ gặp trong tương lai... Biết thêm sẽ tốt chứ sao!
Tôi thì đang tổng hợp dử liệu và đang đụng phải vấn đề gần tương tự nên post lên hỏi ý kiến mọi người xem ai có sáng kiến gì hay ko
Hic... Bác cứ "chơi" tôi ko
???
ANH TUẤN

 

[anhtuan1066]

Tôi có 1 giãi pháp như sau: Ở đây đễ ý nếu ta tạo 1 công thức mãng:
=(YEAR(DS)=Sheet1!$F$6)*(MONTH(DS)=Sheet1!$G$6)
Thì dử liệu trong mãng này toàn là số 0 và 1, với những em =1 thì ứng với d/k đúng... Vậy ta sẽ có công thức MATCH như sau:
{=MATCH(1,((YEAR(DS)=$F$6)*(MONTH(DS)=$G$6)),0)}
Nếu đặt =(YEAR(DS)=Sheet1!$F$6)*(MONTH(DS)=Sheet1!$G$6) thành 1 Name, giã sử tên là DK thì lại càng ngon, ta sẽ có vị trí của cell đầu tiên trùng khớp với d/k là:
=MATCH(1,DK,0)
Và tổng số phần tử trùng khớp d/k là:
=SUM(DK)
Khỏi dùng SUMPRODUCT luôn...và nếu ko đặt name thì hàm SUM và MATCH này phải Ctrl + Shift + Enter (ở đây chỉ cần Enter)
Ngắn gọn nhỉ?
Tôi test dử liệu = 20.000 dòng, chạy cực nhanh (tất nhiên là kết hợp với OFFSET đễ tạo 1 mãng động)
Xin hỏi còn cách nào ngắn hơn nữa ko?
ANH TUẤN

 

[ditimdl]

Rất hay, nếu được anhtuan share luôn cái ví dụ để nghiên cú ứng dụng thêm?

 

[anhtuan1066]

Tôi gữi lên cho các bạn dử liệu 20.000 dòng.. Bài toán tính số lượng trong 1 tháng nào đó!
Đây chỉ là ví dụ đơn giản nhất, tất nhiên trong thực tế sẽ phức tạp hơn nhiều, nhưng ta sẽ dùng cách này đễ giới hạn mãng động!
ANH TUÂN

 

[ditimdl]

Thanks anhtuan! Cái này dữ liệu ngày tháng sắp xếp lộn xộn có gây vấn đề gì không?

 

[anhtuan1066]

Thanks anhtuan! Cái này dữ liệu ngày tháng sắp xếp lộn xộn có gây vấn đề gì không?

Ko dc... nhất định phải sắp tăng dần (OFFSET mới chính xác chứ)..
Cái này bạn hảy yên tâm, dù dử liệu lộn xộn thì bạn Sort lại mấy hồi... Tất nhiên vẫn có cách cho trường hợp như bạn nói, nhưng càng làm file chậm thêm, ko khả thi bằng Sort
Mến
ANH TUẤN