Tìm MATCH như thế nào trong file này??? (1 người xem)

[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

 

[anhtuan1066]

Hỏi thêm 1 câu dành cho các bạn mới học: Các bạn đễ ý cột A và B có rất nhiều dử liệu! Vậy xin hỏi làm sao tôi tạo dc dử liệu như thế? Chẳng lẽ tự gõ vào? 20.000 dòng mà gõ thì có mà.. cả 1 tháng... hi... hi... Dù chỉ là dử liệu ko có thật, nhưng tất nhiên phải có đễ mà tính chứ, đúng ko?
Các bạn suy nghĩ xem... Đừng ngại đưa ra quan điểm của mình... (xin bật mí là tôi tạo nó chỉ trong 30s)
ANH TUẤN

 

[ditimdl]

Thế thì hơi bất tiện.
Vẫn áp dụng file của anhtuan, mình cần tính tổng từ 20/07/2007 đến 19/08/2007 thì nên áp dụng công thức của anhtuan như thế nào? Mình vẫn dùng sumproduct đơn thuần nên rất chậm, hàng tháng mình cần tính tổng 15nghìn dòng và fill 50col and 500row nên thấy rất chậm.
anhtuan có thể help?

 

[anhtuan1066]

Nếu bạn muốn tính chính xác như thế thì càng dể đấy chứ... Này nhé: Sau khi sort xong dử liệu thì bạn dùng hàm MATCH(20/7/2007,DS,0) ra dc vị trí của nó trong DS... dùng SUMPRODUCT đễ đếm xem có bao nhiêu phần tử từ 20/7/2007 đến 19/8/2007 (nếu mỗi ngày duy nhất 1 dòng thì lấy 19/08/2007-20/07/2007+1.. khỏi SUMPRODUCT luôn)...
Vậy là bạn có dc cell đầu tiên và tổng số cell, thế vào OFFSET là ra ngay thôi mà...
Lưu ý rằng tuy ở đây cũng dùng SUMPRODUCT đễ đếm cell, nhưng sẽ ko làm chậm bảng tính, vì bạn chỉ tính duy nhất có 1 lần, đúng ko?
Còn nếu bạn muốn thật chi tiết thì cho tôi nhìn thấy file của bạn, tôi sẽ gợi ý 1 công thức nhanh nhất (mailto: yeulachet1066@yahoo.com)
ANH TUẤN

 

[ThuNghi]

dùng hàm MATCH(20/7/2007,DS,0) ra dc vị trí của nó trong DS... dùng SUMPRODUCT đễ đếm xem có bao nhiêu phần tử từ 20/7/2007 đến 19/8/2007 (nếu mỗi ngày duy nhất 1 dòng thì lấy 19/08/2007-20/07/2007+1.. khỏi SUMPRODUCT luôn)...

MATCH(20/7/2007,DS,0) nếu trong DS không có ngày 20/07/2007 thì sao, báo N/A.
Sao không dùng:
- Countif(DS,"<"&date(2007,7,20)) +1 => DongDau có dữ liệu thỏa DK
- Countif(DS,"<"&date(2007,8,19+1)) - Countif(DS,"<"&date(2007,7,20)) =>SoDong
To Ditimdl: Lâu lắm mới gặp lại người KrongBuk, OK?
Ví dụ:bạn có
- Cột sl xuất từ ngày 01/01/yy-31/12/yy: SLXuat
- Cột ngày xuất từ ngày 01/01/yy-31/12/yy: NgayXuat (ngày tăng dần)
- Cột Mã hàng:từ ngày 01/01/yy-31/12/yy: Mahang

Thay vì bạn dùng Sumproduct((Mahang=X1)*(NgayXuat>="20/07/07")*((NgayXuat<="19/08/07")*SLXuat)
Như thế thì hơi dài và sợ chạy chậm. Vấn đề này hiện nay tôi cũng không biết nhanh hơn hay chậm hơn khi dùng Sumif.
Bạn dùng: sumif(offset(MaHang,DongDau,0,SoDong,1),X1,offset(SLXuat,DongDau,0,SoDong,1))
offset(MaHang,DongDau,0,SoDong,1) chính là Range MaHang đã giới hạn bởi >= 20/07/yy và <= 19/08/yy
Không có file nên HD chay vậy. Hy vọng giờ bạn đã nắm bắt nhiều hơn.
Chúc thành công. AnhTuan1066 xem HD bạn ấy với nhé, bạn cũ của mình ấy.

 

[ditimdl]

Dear TN!
Lâu ngày quá nhỉ? Hình như trong GPE có mỗi mình làm bên y tế nên vấn đề này ít ai quan tâm đến. Công việc thì từ khi được TN hướng dẫn và mình cũng có bổ sung thêm được 1 ít nhưng vấn đề gặp phải là khi thống kê báo cáo rất chậm. Hôm nay gặp topic anhtuan bàn về mảng động nên có gởi file nhờ anhtuan xem và chỉnh sửa lại. HY vọng có đựoc giải pháp tốt hơn. Dùng offset để tạo mảng động với điều kiện dữ liệu ngày tháng tăng dần nhưng một số file của mình ngày tháng ko tuân thủ như vậy. Nếu phải sort để tính toán thì hơi phiền, có cách nào để khắc phục được vấn đề trên không vậy TN?
CThức TN hướng dẫn dùng cũng ok lém nhưng chỉ thống kê chung chứ không thống kê thuốc theo khoa được. Phải có thêm 1 điều kiện thống kê riêng cho từng khoa nữa.