Xử lý công thức cải thiện tốc độ

[boyxin]

Xin chào các bác

Em có 1 vấn đề, trong file đính kèm. Em đã hoàn thành các công thức lọc tìm theo điều kiện. Khi dữ liệu của em trong sheet Data có khoảng 10.000 dòng thì tốc độ lọc tìm theo điều kiện rất lâu (thay đổi đk lọc tìm là thấy ngay)

Mong các bác tìm giúp giải pháp cải thiện tốc độ giúp em
(sheet "Theodoi" và "Report" copy công thức khoảng 3000 dòng)​

 

[sealand]

Đây cũng là vấn đề mình rất quan tâm và đã đưa lên diễn đàn thỉnh giáo. Theo mình thì trích lọc kiểu liệt kê này thì dùng VBA áp dụng SQL là nhanh nhất.

 

[feelingyes]

Boyxin
Mình xem trong file của bạn, công thức rất chuẩn mực, nhưng có một điều theo mình, tốc độ tính của file còn phụ thuộc vào cột nào mình đặt cố định và cột khác sẽ offset theo
-Như trong file boyxin đặt cột STT làm cột mốc, giả sử ta chọn report tháng 3 thì dữ liệu mảng sẽ ko chỉ phủ riêng cho tháng 3 (thực tế dữ liệu phủ cho cả Tháng 1, 2,3...) do công thức offset từ STT sang.
Bạn ko tin xem lại thử file xem
-Mình nghĩ boyxin nên đổi lại
Cột ngày tháng xếp lên đầu (tạo thêm một name động sẽ offset theo tháng mình chọn)
cột số thứ tự mình dùng làm cột phụ (if( A<>"",Max()+1,"")

Mong được góp ý

 

[boyxin]

Boyxin
Mình xem trong file của bạn, công thức rất chuẩn mực, nhưng có một điều theo mình, tốc độ tính của file còn phụ thuộc vào cột nào mình đặt cố định và cột khác sẽ offset theo
-Như trong file boyxin đặt cột STT làm cột mốc, giả sử ta chọn report tháng 3 thì dữ liệu mảng sẽ ko chỉ phủ riêng cho tháng 3 (thực tế dữ liệu phủ cho cả Tháng 1, 2,3...) do công thức offset từ STT sang.
Bạn ko tin xem lại thử file xem
-Mình nghĩ boyxin nên đổi lại
Cột ngày tháng xếp lên đầu (tạo thêm một name động sẽ offset theo tháng mình chọn)
cột số thứ tự mình dùng làm cột phụ (if( A<>"",Max()+1,"")

Mong được góp ý

Vấn để ở đây là sheet Data:

• Về ngày tháng nhập (mình chủ động) có thể có sẵn theo thứ tự trước -> sau
• Nhưng ngày tháng xuất hàng cho từng bộ phận thì (bị động) nên muốn sắp sếp ngay khi nhập là không khả thi trong cùng 1 bảng <--> mông được góp ý thêm

==>

cột số thứ tự mình dùng làm cột phụ (if( A<>"",Max()+1,"")

mình chưa hiểu ý đồ chỗ này, mông được giải thích cụ thể hơn

 

[vumian]

BoyXin oi, Lọc theo bác là Index và Match, không biết lọc AdvanceFilter của Excel thì theo thuật toán nào ? Bác có thể dùng để so sánh tốc độ với dữ liệu 10 ngàn dòng
Lọc theo AdvanceFilter
http://www.giaiphapexcel.com/forum/showthread.php?t=11163

 

[boyxin]

Boyxin
Mình xem trong file của bạn, công thức rất chuẩn mực, nhưng có một điều theo mình, tốc độ tính của file còn phụ thuộc vào cột nào mình đặt cố định và cột khác sẽ offset theo
-Như trong file boyxin đặt cột STT làm cột mốc, giả sử ta chọn report tháng 3 thì dữ liệu mảng sẽ ko chỉ phủ riêng cho tháng 3 (thực tế dữ liệu phủ cho cả Tháng 1, 2,3...) do công thức offset từ STT sang.
Bạn ko tin xem lại thử file xem
-Mình nghĩ boyxin nên đổi lại
Cột ngày tháng xếp lên đầu (tạo thêm một name động sẽ offset theo tháng mình chọn)
cột số thứ tự mình dùng làm cột phụ (if( A<>"",Max()+1,"")

Mong được góp ý

Đã thay đổi cách OFFSET nhưng vẫn không cải thiện được gì
(dữ liệu ít mà vẫn còn hiện % -> data khoảng 10000 dòng, công thức khoảng 1000 dòng thì ...
không dám nghĩ đến nữa)​

 

[sealand]

Xin chào các bác

Em có 1 vấn đề, trong file đính kèm. Em đã hoàn thành các công thức lọc tìm theo điều kiện. Khi dữ liệu của em trong sheet Data có khoảng 10.000 dòng thì tốc độ lọc tìm theo điều kiện rất lâu (thay đổi đk lọc tìm là thấy ngay)

Mong các bác tìm giúp giải pháp cải thiện tốc độ giúp em

(sheet "Theodoi" và "Report" copy công thức khoảng 3000 dòng)​

Trường hợp của bạn hoàn toàn giống trường hợp của tôi đã đưa ra diễn đàn nhờ giúp đỡ. Không hiểu sao sau 1 thời gian sử dụng file mắc phải tình trạng này, nhiều bạn đã giải đáp nhưng vẫn chưa thoả đáng. Tôi nghĩ không hẳn là do công thức đâu. Bạn cứ xoá trắng toàn bộ công thức trong sheet Report của bạn từ dòng 5 mà xem, cái combo tháng của bạn cũng phải vài giây mới cập nhật được tháng bạn chọn. Trường hợp của tôi còn ngồi đếm % calculate nữa cơ. Mong các cao thủ trên diễn đàn sớm triệt hạ được chứng này thì tốt quá.

 

[feelingyes]

Gửi boyxin
Mình mở file thì cậu đã chỉnh sửa lại công thức, nhưng ý của mình ở đây là sau khi mình chọn tháng 1 chẳng hạn, thì dữ liệu chỉ phủ riêng cho tháng 1 thôi, như vậy công thức sẽ ko phải mất thời gian tính cho các tháng còn lại
-Cậu thử vào trong define name --> trong phần dưới Refer to: --> ví dụ ta đang ở name là D_in --> sau đó cậu để chuột vào khung refer to: cậu sẽ thấy có một đường nét đứt mờ bao quanh vùng tại cột ngày nhập (cột 6)
-> Trong khi ta chọn tháng 1, thì đường chỉ đứt mờ đó vẫn bao phủ 538 dòng (đừng nói là 10.000 dòng) theo mình đó là nguyên nhân làm chậm tốc độ của excell
* Theo mình vì trong Sheet theo dõi, báo cáo. Chủ yếu ta lọc theo điều kiện là tháng thì cậu phải xếp các tháng theo một trật tự, ví dụ ta chọn tháng 3 thì name được tạo ra chỉ phủ quanh trong tháng 3 thôi
-Anh chị nào có ý kiến gì ko?

 

[tien1007]

Em khuyên các bác nên xoa hết công thức ở report rồi dùng một button gán macro la nhe va đơn giản nhất.

 

[feelingyes]

Vpp

Boyxin
Cậu thử xem file của mình, vì mình làm vội, cậu xem lại liệu có thể cải tiến hơn được ko?
-Trong sheet report mình làm vội nên chưa hoàn thành, đây là sheet báo cáo cho từng bộ phận trong tháng (quý). mình sẽ đối chiếu sheet theo dõi và sheet báo cáo với nhau.
Còn ý mình tội gì báo cáo file lưu lấu thế (tớ thấy trong phần report , cell chọn năm, tớ thấy tận năm 2 nghìn bao nhiêu ...
Theo tớ là chỉ lưu theo tháng, hoặc quý là ổn

 

[ndu96081631]

BoyXin oi, Lọc theo bác là Index và Match, không biết lọc AdvanceFilter của Excel thì theo thuật toán nào ? Bác có thể dùng để so sánh tốc độ với dữ liệu 10 ngàn dòng
Lọc theo AdvanceFilter
http://www.giaiphapexcel.com/forum/showthread.php?t=11163

Đương nhiên AF sẽ nhanh hơn nếu không nói là cực nhanh!
Tùy theo việc mà chọn lựa giữa công thức và VBA. Với những kiểu lọc thế nào thì tôi chọn VBA

 

[boyxin]

Nhưng vấn đề ở đây là muốn tìm hiểu nguyên nhân vì sao mà dùng công thức mà

1. Sheet Data: mơí có 533 dòng trong khi số liệu đầy dủ mói có 10 dòng
2. Sheet Theodoi: mới copy công thức được 200 dòng
3. Sheet Report: mới copy công thức được 100 dòng

Đã phải ngồi đếm % ở thanh Status Bar

Tối ưu đến thế này là hết khả năng của boyxin rồi, mong được giúp đỡ

 

[Mr Okebab]

Nhưng vấn đề ở đây là muốn tìm hiểu nguyên nhân vì sao mà dùng công thức thì lại phải ngồi đếm % ở thanh Status Bar để còn phòng tránh cho những bài sau

Tối ưu đến thế này là hết khả năng của boyxin rồi, mong được giúp đỡ

Cái này thì dùng cái nào cũng được, chưa cần đến SQL đâu

Tuy nhiên bạn nên hệ thống lại Sheet Data nhé, mình nhìn cứ như là báo cáo chi tiết ấy
VD :

• NgayThang : Chỉ ngày tháng Nhập hoặc Xuất

• ChungTu : Để phân biệt là Nhập hay Xuất

• BoPhan : Để xác định Bộ phận nhận hàng

• MaHH : Để phân biệt rõ hàng này với hàng khác. Nếu cho chuyên nghiệp hơn, bạn hãy tạo một danh mục hàng hóa, danh mục bộ phận

• TenHangHoa : Không có cũng được, có chẳng qua là do một số người muốn chọn cho chính xác hơn thôi

• SoLuong : Số lượng nhập hoặc xuất

• ĐonGia : Đơn giá Nhập hoặc Xuất

• ThanhTien

Khi bạn tạo ra 1 DATA như thế thì sẽ thuận tiện cho việc giải quyết bài toán = công thức, bằng Advanced Filter hay bằng VBA.
Như vậy với bài toán trên thì vài chục ngàn dòng cũng không đáng kể gì cả.

Thân!

 

[boyxin]

Cái này thì dùng cái nào cũng được, chưa cần đến SQL đâu

Tuy nhiên bạn nên hệ thống lại Sheet Data nhé, mình nhìn cứ như là báo cáo chi tiết ấy
VD :

• NgayThang : Chỉ ngày tháng Nhập hoặc Xuất

• ChungTu : Để phân biệt là Nhập hay Xuất

• BoPhan : Để xác định Bộ phận nhận hàng

• MaHH : Để phân biệt rõ hàng này với hàng khác. Nếu cho chuyên nghiệp hơn, bạn hãy tạo một danh mục hàng hóa, danh mục bộ phận

• TenHangHoa : Không có cũng được, có chẳng qua là do một số người muốn chọn cho chính xác hơn thôi

• SoLuong : Số lượng nhập hoặc xuất

• ĐonGia : Đơn giá Nhập hoặc Xuất

• ThanhTien

Khi bạn tạo ra 1 DATA như thế thì sẽ thuận tiện cho việc giải quyết bài toán = công thức, bằng Advanced Filter hay bằng VBA.
Như vậy với bài toán trên thì vài chục ngàn dòng cũng không đáng kể gì cả.

Thân!

Hi
Boyxin cũng chỉ là người làm giúp thôi (không phải chuyên môn nên có cái khó)

Sheet Data thì đúng là 1 bản chi tiết (kiểu nhật ký xuất, nhập hàng) nhưng không thể in ra để báo cáo
Trong khi đó cần có các bản báo cáo:

1. Theo dõi hàng nhập của công ty trong từng tháng xem hiệu xuất kinh doanh (xuất, nhập, tồn)
2. Theo dõi hàng nhập của công ty từ đầu năm đến cuối 1 tháng nào đó (theo dõi: xuất, nhập, tồn)
   => tạo ra sheet TheoDoi để hoàn thành nhiệm vụ này
3. Thống kê từng tháng số lượng hàng và thời gian đã xuất cho các bộ phận và gửi các bọ phận bản in chi tiết để các bộ phận dùng để kiểm hàng (tác dụng như hoá đơn xuất hàng)
   => Tạo ra Sheet Report để hoàn thành nhiệm vụ này

Trước hết là xin cảm ơn Mr Okebab đã giành thời gian nghiên cứu có ý kiến rất cụ thể, chi tiết
Do quản lý quá nhiều loại mặt hàng mà mỗi loại có thể nhập nhiều lần, mỗi lần nhập thì không phải là sẽ cùng xuất cho các bộ phận cùng ngày (mỗi bộ phận có thể họ sẽ đến lấy vào những ngày khác nhau, số lượng kác nhau)
Vậy Tạo DATA như Mr Okebab xem ra có vẻ hơi khó. Mong được giúp đỡ thêm

 

[Mr Okebab]

Hi
Boyxin cũng chỉ là người làm giúp thôi (không phải chuyên môn nên có cái khó)

Sheet Data thì đúng là 1 bản chi tiết (kiểu nhật ký xuất, nhập hàng) nhưng không thể in ra để báo cáo
Trong khi đó cần có các bản báo cáo:

1. Theo dõi hàng nhập của công ty trong từng tháng xem hiệu xuất kinh doanh (xuất, nhập, tồn)
2. Theo dõi hàng nhập của công ty từ đầu năm đến cuối 1 tháng nào đó (theo dõi: xuất, nhập, tồn)
   => tạo ra sheet TheoDoi để hoàn thành nhiệm vụ này
3. Thống kê từng tháng số lượng hàng và thời gian đã xuất cho các bộ phận và gửi các bọ phận bản in chi tiết để các bộ phận dùng để kiểm hàng (tác dụng như hoá đơn xuất hàng)
   => Tạo ra Sheet Report để hoàn thành nhiệm vụ này

Trước hết là xin cảm ơn Mr Okebab đã giành thời gian nghiên cứu có ý kiến rất cụ thể, chi tiết
Do quản lý quá nhiều loại mặt hàng mà mỗi loại có thể nhập nhiều lần, mỗi lần nhập thì không phải là sẽ cùng xuất cho các bộ phận cùng ngày (mỗi bộ phận có thể họ sẽ đến lấy vào những ngày khác nhau, số lượng kác nhau)
Vậy Tạo DATA như Mr Okebab xem ra có vẻ hơi khó. Mong được giúp đỡ thêm

Chẳng có gì khó khăn cả, xem mình lấy dữ liệu từ DATA của bạn sang PHATSINH của mình nhé.

Mình chỉ là VD thôi, còn việc tùy biến là ở bạn.

Vấn đề còn lại là đặt Name để dùng công thức, hay dùng AF, hay dùng VBA thôi, cái nào cũng được cả.


Thân!

P/S : Mấy cái báo cáo mà bạn nêu ở trên khi đã hoàn thành công việc bố trí dữ liệu thì chỉ còn là chuyện nhỏ.

 

[boyxin]

QLyVPP Ver04

Chẳng có gì khó khăn cả, xem mình lấy dữ liệu từ DATA của bạn sang PHATSINH của mình nhé.
Mình chỉ là VD thôi, còn việc tùy biến là ở bạn.

Thân!
P/S : Mấy cái báo cáo mà bạn nêu ở trên khi đã hoàn thành công việc bố trí dữ liệu thì chỉ còn là chuyện nhỏ.

Một lần nữa cảm ơn Mr Okebab nha

Sau 1 thời gian thai nghén cộng với sự giúp đỡ của các bác, đối chiếu với yêu cầu công việc
boyxin cho ra đời

QLyVPP Ver04.rar có tốc độ xử lý tốt kể cả với máy có cấu hình thấp
Khi có thêm dữ liệu chỉ cần

1. Vào Insert\Name\Define... thay đổi giá trị của Name End (giá trị của name End là số của dòng cuối cùng chứa dữ liệu)
2. Copy thêm công thức (kéo fill xuống) ở 2 sheet theodoi và Report

Rất mong được các bác chỉ giáo thêm

 

[Mr Okebab]

Thêm dữ liệu chỉ cần

1. Vào Insert\Name\Define... thay đổi giá trị của Name End (giá trị của name End là số của dòng cuối cùng chứa dữ liệu)
2. Copy thêm công thức (kéo fill xuống) ở 2 sheet theodoi và Report

Rất mong được các bác chỉ giáo thêm

Hãy biến các Name thành name động khi có dữ liệu thêm vào, đừng bắt người khác phải thay đổi.

VD :
End =COUNTA(Data!$D:$D)

​

Và cố gắng ít cột phụ đi, nhiều khôg phải là điều tốt
Các công thức tổng hợp cần gọn gàng hơn, nhìn .. sợ quá!!

Thân!

 

[boyxin]

End =COUNTA(Data!$D:$D

OK thank

Và cố gắng ít cột phụ đi, nhiều khôg phải là điều tốt

1. Chỉ có 3 cột phụ thôi mà
2. Còn dòng Hide là để lấy thông số cho cột cho offset gọn gàng hơn

Có thể tinh giản hơn như thế nào vậy hả bác?

Các công thức tổng hợp cần gọn gàng hơn, nhìn .. sợ quá!!

1. Sheet theodoi: cột L lắp thêm bẫy lỗi nên như vậy
2. các chỗ khác công thức gọn rồi còn giè

Có thể tinh giản hơn nữa như thế nào vậy hả bác?