Công thức thống kê

[ditimdl]

Trong file đính kèm ở sheet total em có vài vấn đề cần các anh chị trợ giúp.
Ngày tháng thống kê được tính từ ngày... đến ngày...
Ví dụ em cần thống kê tháng 4 thì ngày bắt đầu là 1/4 đến 30/4. Đầu tiên em xác định ngày đầu tiên(1/4) nằm ở vị trí dòng số mấy trong cột ngày và tiếp đến xác định có bao nhiêu dòng thỏa mãn khoảng thời gian 1/4-30/4(Em đã làm được với điều kiện sort ngày tháng tăng dần) nhưng gặp phải vấn đề là ngày đầu tiên ko có trong cột ngày tháng nên công việc xác định dòng đầu tiên và số dòng thõa mãn điều kiện bị sai.
Với cách làm như thế nào thì dữ liệu có khoảng 10.000-14.000 dòng có làm chậm bảng tính quá không?
Việc xác định dòng đầu và số dòng dùng công thức như trên với việc dùng name động thì cách nào xử lý nhanh hơn?
Cảm ơn các bạn và anh chị đã đọc topic.

 

[anhtuan1066]

Bạn thử xem cách làm của tôi nhé!
Hướng làm của bạn đi gần đúng rồi đấy, tuy nhiên công thức khá cồng kềnh và có chổ sai về tính toán... Tôi sửa lại mấy chổ:
1> Sửa lại cell tiêu đề SỐ LƯỢNG
2> Sửa lại công thức xác định vị trí đầu tiên.. Riêng công thức xác định vị trí cuối thì tôi sửa thành TỔNG SỐ DÒNG với mục địch dùng cho OFFSET
3> Dùng Name động để rút gọn công thức và tạo sự mềm dẻo hơn cho việc áp dụng
... Nói lòng vòng.. bạn xem file sẽ rõ
Mến
ANH TUẤN

 

[anhtuan1066]

Đây là file tôi giã lập 60.000 dòng dử liệu, file chạy cực nhanh khi chọn ComboBox...
Bạn tải về xem thử, tôi sẽ xóa file này đi trong vài ngày tới vì.. hao tốn tài nguyên quá.. hi.. hi..
ANH TUẤN

 

[ditimdl]

Trước hết là cảm ơn AT nhiều. Tối nay xem lại file AT gửi rùi mới trao đổi tiếp. Mới mò đến công thức Address và column với row nên tập tành thử
Mới xem qua một số name trong file AT gửi nhưng ko hiểu lắm. AT có thể giải thích mấy name dùng offset và công thức mãng tại vị trí dòng đầu tiên đó?

 

[anhtuan1066]

Tôi có thể nói đại khái thế này: Vì bạn nói dử liệu của bạn rất nhiều nên đễ tăng tốc ta ko nên tính trên toàn bộ dử liệu mà chỉ nên làm cách nào đó giới hạn vùng dử liệu vừa trọn trong tháng
Ví dụ:
1> Nếu bạn chọn tháng 2 năm 2008 thì Name NGAY sẽ giới hạn dử liệu vừa đũ từ ngày 1 đến ngày 29 tháng 2 mà thôi (ko tính hết 60.000 dòng dử liệu)
2> Name SL và THUOC chẳng qua dịch chuyển từ name NGAY sang bên phải với số cột phụ thuộc vào việc cell bạn đang xét nằm ở cột SỐ LƯỢNG 1, SỐ LƯỢNG 2 hay SỐ LƯỢNG 3... Cũng vì muốn nó phải tính toán dc nên tôi cố tình sửa 3 cell E2, F2 và G2 thành số đếm (chú ý nó ko phải là TEXT nhé)
Giờ bạn hãy làm 1 cuộc thí nghiệm như thế này nhé:
1>Chọn tháng năm trong ComboBox..
2> Đặt trỏ chuột tại 1 trong 3 cột E hoặc F hoặc G (ko dc đặt cell chọn sang cột khác)
3> Xong vào menu Insert\Name\Define... chọn vào name NGAY hoặc SL hoặc THUOC.. tiếp theo bạn đặt con trỏ chuột vào khung "Refer to"
Lặp tức bạn sẽ thấy bảng tính "nhảy" sang sheet Data và có 1 đường bao đứt nét quanh vùng nào đó... Xem thật kỹ cell đầu tiên và cell cuối cùng trong vùng này có phải chính là vùng mà ta đang cần tính toán ko?
Bạn có thể thử nghiệm nhiều lần bằng cách lúc đầu đặt con trỏ chuột chọn vào cột E, tiếp theo lần sau lại chọn vào cột F rồi lần sau nữa chọn vào cột G đễ thấy sự thay đổi... Và đây chính là name động.. SUMIF sẽ chỉ tính toán nội trong vùng này chứ ko tính trên 60.000 dòng dử liệu... Đó là thuật toán tăng tốc...
Ái da.. Càng nói càng ngọng... hy vọng bạn vừa thí nghiệm vừa hiểu dc vấn đề.. Thú thật tôi thấy nó rất dể nhưng lại ko biết nói sao cho mọi người hiểu!
-----------------------
Ah... mà bạn đang nói đến công thức mãng tại dòng đầu tiên là công thức nào vậy?
ANH TUẤN

 

[giaosy]

ditimdl ơi, bạn có yêu cầu giống tôi quá, tôi cũng đã nhờ bác anh tuấn giải đáp vấn đề này rồi, bác anh tuấn đã chỉ giúp cho tôi ở chủ đề cộng theo 3 điều kiện bằng một cách hết sức hay.
Tôi cũng đã tìm cách giải quyết nó bằng advance filter những không hiểu sao nó không nhận điều kiện là thời gian trực tiếp mà phải nhận một giá trị gián tiếp. Nếu ditimdl có gì hay hơn thì trao đổi với tôi với nhé.
thank

 

[anhtuan1066]

Về phần AF tôi cũng ko rành lắm.. nhưng bạn có thể thí nghiệm để rút ra kết luận... hảy tự giã lập dử liệu và thí nghiệm... thất bại lại thí nghiệm tiếp, tin chắc sẽ có lúc bạn ngộ ra dc vấn đề...
ANH TUẤN

 

[ditimdl]

To giaosy: Bạn dùng advan để lọc hả? Điều kiện của bạn như thế nào mà phải gõ gián tiếp?
To AT: Tối giờ em ngâm cú cũng hiểu sơ sơ rùi, cái này em chỉ ví dụ nên để em áp dụng vào file thực tế của em. 50 cột thuốc + 50 cột số lượng. HY vọng là tốc độ tính toán khả quan.
Em hỏi AT thêm tí: Offset có 5 thành phần nhưng em thấy có cái anh làm đủ 5 thành fần, có cái 4, 3...Em chưa hiểu lắm, tập cho anh nói ngọng tiếp đó. Help.

 

[anhtuan1066]

Em hỏi AT thêm tí: Offset có 5 thành phần nhưng em thấy có cái anh làm đủ 5 thành fần, có cái 4, 3...Em chưa hiểu lắm, tập cho anh nói ngọng tiếp đó. Help.

Thông thường tham số trong hàm nếu ta ko ghi gì thì sẽ lấy theo mặc định... Ví dụ:
Offset($A$1,5,2) <==> Offset($A$1,5,2,1,1)
Offset($A$1,,,2,3) <==> Offset($A$1,0,0,2,3)
vân vân...
Cũng giống như:
Match($A$1,Vùng) <==> Match($A$1,Vùng,1)
If($A$1,"Đúng","Sai") <==> If($A$1 = TRUE,"Đúng","Sai")
Vài ví dụ nhỏ, chắc là bạn sẽ hiểu!
Mến
ANH TUẤN

 

[babykokoro]

Phím tắt

Các bác cho em hỏi phím tắt để kẻ viền cho tất cả các ô trong excel là thế nào?

Em xin đa tạ các bác nhiều nhiều

 

[anhtuan1066]

Các bác cho em hỏi phím tắt để kẻ viền cho tất cả các ô trong excel là thế nào?

Em xin đa tạ các bác nhiều nhiều

1> Đầu tiên là Ctrl Shift 7 để kẻ đường ngoài biên
2> Tiếp theo Ctrl Shift , để kẻ đường ngang
3> Cuối cùng Ctrl Shift . để kẻ đường dọc
Bạn ko thể bỏ qua bước 1 để chuyển sang bước 2 và 3 dc
vậy muốn làm vắn tắt thì: Ctrl Shift 7 , . 7 (bấm Ctrl + Shift trước, giữ nguyên tay bấm phím, tay kia bấm số 7 .. rồi dấu phẩy.. rồi dấu chấm.. rồi lại số 7)
Muốn xóa hết tất cả các đường viền thì Ctrl Shift -
(Chú y: số và dấu đều ko phải thuộc bàn phím số)
ANH TUẤN

 

[babykokoro]

Các bác ơi em hỏi chút nhé.Em có phải :"Part Data", mỗi lần em update hoặc Vlookup vào thì chữ trong cột Tiếng Anh và Tiếng Việt (em có tô màu xanh trong file đình kém)cứ dính vào nhau. Chúng chẳng chịu tách ra gì cả. Mỗi lần như vậy em toàn phải làm thủ công để tách chúng ra.

Em xin nhờ các cao thủ chỉ giúp em cách làm nhanh hơn được không ạ ( vì bảng của em có tới vài chục nghìn mã cơ)
Thank kiu các bác

Bác Tuấn ơi về vụ kẻ viền cho các ô trong excel, em thấy nếu áp dụng trên bảng trống không có dữ liệu thì Ok nhưng nếu em áp dụng vào bảng đã có số liệu (khi ấn CTRL SHIFT 7,.7 )thì dữ liệu trong dòng đầu tiên sẽ bị copy sang các dòng khác.
Bác xem lại giúp em vụ này nhé.

Thank kiu bác

 

[ditimdl]

To baby...: Bạn muốn hỏi thì mở topic khác nha sao lại xen vào topic người khác vậy trời?
To AT: Ý em muốn hỏi công thức mảng để xác định vị trí đầu đó?
{=MATCH(TRUE,DMDATE>=$M$3,0)}
Em đặt con chuột tại vị trí số lượng 1 trong phần total để xem công thức SL=OFFSET(NGAY,,Total!E$2*2): Anh giải thích dùm em nó dịch chuyển như thế nào mà khi em vào hộp thoại "Refer to" nó lại chọn đúng vị trí cột số lượng của Thuốc 1 vậy?

 

[anhtuan1066]

To baby...: Bạn muốn hỏi thì mở topic khác nha sao lại xen vào topic người khác vậy trời?
To AT: Ý em muốn hỏi công thức mảng để xác định vị trí đầu đó?
{=MATCH(TRUE,DMDATE>=$M$3,0)}
Em đặt con chuột tại vị trí số lượng 1 trong phần total để xem công thức SL=OFFSET(NGAY,,Total!E$2*2): Anh giải thích dùm em nó dịch chuyển như thế nào mà khi em vào hộp thoại "Refer to" nó lại chọn đúng vị trí cột số lượng của Thuốc 1 vậy?

Nói thật bạn đừng cười... Tôi cũng.. chả biết giãi thích thế nào nữa... Nhìn vào dử liệu trong file và tự nhiên có ý tưởng... Còn giãi thích uh? Tôi mà giãi thích xong có khi ngay cả tôi đọc lại còn hỏng hiểu chứ đừng nói người khác...
Hic...
Các bạn khác như Phamnhukhang, ttphong2007, BNTT ....vân... vân thì lại rất có năng khiếu về vụ này... E rằng phải nhờ mấy đại ca này thôi...
ANH TUẤN

 

[ditimdl]

SL=OFFSET(NGAY,,Total!E$2*2) Name này em ko hiểu nó tính như thế nào mà khi vào refer to nó lại chọn đúng vị trí của thuốc 1. E muốn hiểu rõ cách tính của nó như thế nào để em có thể áp dụng vào một số file khác, cấu trúc nó không giống như file này.
Anh nói vậy thì em đành chờ mấy đại ka kia chứ bít sao giờ (

 

[anhtuan1066]

Đầu tiên giã sử rằng bạn đã hiểu dc name NGAY rồi thì dể.. công thức trên sẽ dịch Range NGAY đi 0 dòng và dịch số cột đi đúng bằng kết quả E$2*2.... Nếu E2 = 1 thì E$2*2 = 2... vậy:
OFFSET(NGAY,,Total!E$2*2) sẽ dịch Range NGAY đi 0 dong 2 cột
Công thức trên tôi viết tắt, đúng ra phải là:
OFFSET(NGAY,0,Total!E$2*2)
Ko biết bạn hiểu ko nhỉ?
ANH TUẤN

 

[ditimdl]

Đó đúng là điều em cần hiểu ) Thế mà anh lại bảo ko biết giải thích thế nào.
Còn cái này : {=MATCH(TRUE,DMDATE>=$M$3,0)} anh giải thích nốt luôn cho em luôn đi.
Cảm ơn anh nhiều!

 

[anhtuan1066]

Đó đúng là điều em cần hiểu ) Thế mà anh lại bảo ko biết giải thích thế nào.
Còn cái này : {=MATCH(TRUE,DMDATE>=$M$3,0)} anh giải thích nốt luôn cho em luôn đi.
Cảm ơn anh nhiều!

Vì dử liệu thật trong file khá lớn nên ta khó có thể thí nghiệm trực tiếp dc....

Để hiểu công thức này, bạn hãy giã lập dử liệu khác và làm thí nghiệm như tôi nhé:

1> Trong cột A, từ A1 đến A30 bạn hãy gõ các số tùy ý từ 1 đến 100​

2> Sắp xếp cột A theo thứ tự tăng dần​

3> Giờ tôi muốn tìm xem các số > 10 là những số nằm ở vị trí nào, vậy tôi sẽ thí nghiệm bằng cách quét chọn từ B1 đến B30 và gõ vào thanh Formula công thức mãng: $A$1:$A$30 > 10 (Ctrl + Shift + Enter)​

4> Bạn sẽ nhìn thấy giá trị thu dc có 2 loại TRUE và FALSE​

5> Nhìn vào cột B này bạn có thể đoán rằng để tìm cell đầu tiên thỏa d/k có phải là tìm thằng TRUE đầu tiên ko? Vậy có phải công thức sẽ là MATCH(TRUE,$A$1:$A$30,0) ... (công thức này là mãng nên cũng phải Ctrl + Shift + Enter luôn)​

Bạn có thể đặt câu hỏi tại sao ko dùng công thức MATCH(10,$A$1:$A$30,0) hoặc MATCH(10,$A$1:$A$30,1).. ngắn gọn dể hiểu hơn, đúng ko?... Nhưng nếu lở trong dảy $A$1:$A$30 ko có số 10 nào thì sao? Lúc này sẽ có 2 trường hợp xảy ra:

1> Nếu dùng MATCH(10,$A$1:$A$30,0) (tìm chính xác) thì đương nhiên sẽ cho kết quả #NA nếu như ko tìm thấy số 10​

2> Nếu dùng MATCH(10,$A$1:$A$30,1) (tìm ko chính xác) thì lại có 2 trường hợp con khác xảy ra:

a) Nếu số nhỏ nhất trong dảy cũng lớn hơn số 10 thì kết quả sẽ là #NA​

b) Nếu số 10 là số nằm trong khoảng số nhỏ nhất và lớn nhất của dảy thì kết quả nhận dc sẽ là số nhỏ liền kề với 10 (chẳng hạn là số 9)​

​

Bạn thấy đấy... đây chính là điều mà bạn đang lo ngại: TRONG DẢY KO CÓ SỐ CẦN TIM, RẤT CÓ THỂ SẼ CHO KẾT QUẢ #NA LÀM SAI CÔNG THỨC CHÍNH... và ta giãi quyết nó bằng công thức mãng là tổng quát nhất (dù chưa chắc là hoàn hảo nhất)
Hy vọng bạn hiểu dc những gì tôi vừa giãi thích!
Mến
ANH TUẤN

 

[ditimdl]

Trước hết em cảm ơn về những bài viết của anh rất hay, qua đây em cũng học hỏi thêm nhiều từ những công thức trên.
File Test của anh với 60.000dòng với 3 cột thuốc, 3 cột sl thì chạy khá nhanh(hơi rùng mình xí) nhưng áp dụng vào file thật của em với 14000dòng, 9cột thuốc, 9cột số lượng thì nhìn nó chạy hơi đau lòng. E xóa bớt dữ liệu chỉ còn ~5000dòng, 9cột thuốc, 9 cột số lượng nhưng kết quả cũng ko khả quan mấy.
Theo anh mình có thể cải thiện gì được nữa ko anh?

 

[anhtuan1066]

Tôi đang xem lại file của bạn! Chỉ có thể tối ưu đến mức như file tôi đính kèm mà thôi... Tốc độ chậm là do nhiều công thức quá (đến 57 cột)
Bạn thử nghĩ xem có cách nào giãm bớt số lượng cột này ko? Chẳng hạn muốn trích số lượng nào thì chọn vào Validation Số lượng ấy ???
Tôi đã thí nghiệm, nếu xóa bớt đi cột công thức số lượng thì tốc độ sẽ tăng lên...
---------------
Một điều lưu ý nữa: kiểm tra cẩn thận dử liệu nhập, coi chừng có sai sót (tôi thấy bên phần nhập liệu, ngay cột MÃ có cell ko ghi gì cả ??? )
(File này nặng quá, bạn hãy nhanh chóng tải về, ngày mai tôi xóa nó đi)
Mến
ANH TUẤN