Hàm UDF tự tạo thay thế hàm vlookup và sumifs trong Excel

[thinhnx22]

Chào các bạn,
Trong file của mình hiện tại đang dùng nhiều hàm vlookup và sumifs, dữ liệu cơ sở khá lớn khoảng 70-100 nghìn dòng. Không biết trên diễn đàn, đã ai viết hàm vlookup và sumifs bằng UDF tự tạo mà cải thiện tốc độ hơn hai hàm trên của Excel. Ở công ty có bạn chưa biết VBA, nên minh muốn viết qua UDF mà không phải bằng Sub. Các bạn tư vấn cho mình xin link bài đã có. Chân thành cảm ơn.

 

[thinhnx22]

1. Không cái U đê ép nào hơn hàm chính chủ của Microsoft về tốc độ được.
2. Một trăm ngàn dòng mà U đê ép thì cũng rùa bò.

Cảm ơn bạn, mình test cũng thấy như bạn nói nên mới vào diễn đàn xem có giải pháp nào hay không, hay vẫn phải dùng Sub VBA.

 

[SA_DQ]

Ông bà xưa nói: "Có bột mới gột nên hồ"
Vậy bạn mô tả kỹ dữ liệu xem hàm phải làm việc gì đi nào; Hay gởi lên file giả lập đi vậy!

 

[Ba Tê]

Cảm ơn bạn, mình test cũng thấy như bạn nói nên mới vào diễn đàn xem có giải pháp nào hay không, hay vẫn phải dùng Sub VBA.

Phương án có vẻ hợp lý nhất là Sub VBA.

 

[VetMini]

Tôi chả hiểu cách phân biệt của thớt giữa U đê ép và Súp Vê bê a ra sao. Theo kiến thức của tôi thì U đê ép nó vẫn phải dùng vê bê a.

Tạm đoán rằng thớt muốn nói súp là cái mà "bấm nút một phát" nó chạy cái rần và cho kết quả lên trang tính. Và U đê ép là cái hàm dùng thay hàm bảng tính, vì được viết cho trường hợp đặc trù của dữ liệu cho nên nó hiệu quả hơn hàm chính của Excel.

Trên nguyên tắc, nếu người viết hàm thấu hiểu cơ cấu dữ liệu thì có thể viết hàm tổng hợp và tìm kiếm theo kiểu có cache thì nó sẽ nhanh.
Đại khái là code sẽ chứa 1 hay cả 2 phần quan trọng sau đây:
- code tổng hợp một lần, và dùng cho tất cả dòng. Hoặc tổng hợp theo luỹ tiến từng dòng.
- code sắp xếp dữ liệu, hoặc đặt dữ liệu lại theo một cấu trúc dễ cho việc truy vấn hơn.

Trước mắt thì công việc vlookup và sumifs là công việc của Pivot table. Nếu dữ liệu nhiều thì nâng cấp lên 2016 hoặc 365 để sử dụng Data Model và PowerQuery/PowerPivot. U đê ép hay súp chỉ là giải pháp chắp vá tạm bợ.

Chú: Data Model dùng bộ máy sửv dụng cho SQL Server Express để quản lý dữ liệu cho nên vài triệu dòng dữ liệu nó xử lý rất hiệu quả.

 

[tam888]

Chào các bạn,
Trong file của mình hiện tại đang dùng nhiều hàm vlookup và sumifs, dữ liệu cơ sở khá lớn khoảng 70-100 nghìn dòng. Không biết trên diễn đàn, đã ai viết hàm vlookup và sumifs bằng UDF tự tạo mà cải thiện tốc độ hơn hai hàm trên của Excel. Ở công ty có bạn chưa biết VBA, nên minh muốn viết qua UDF mà không phải bằng Sub. Các bạn tư vấn cho mình xin link bài đã có. Chân thành cảm ơn.

Đúng như comment này

1. Không cái U đê ép nào hơn hàm chính chủ của Microsoft về tốc độ được.
2. Một trăm ngàn dòng mà U đê ép thì cũng rùa bò.

Chỉ nhanh hơn khi làm tổng hợp hàng loạt tức là thay thế hàng loạt hàm Vlookup và SumIfs bằng 1 code và tất nhiên là làm Sub thì tốt hơn UDF vì UDF nó luôn liên kết động, và luôn caculate mỗi sự kiện thay đổi dẫn đến là chậm.

Muốn vậy chỉ có post file cần lên thì mọi người mới giúp được còn không thì chỉ phán/chém chung chung thôi

 

[VetMini]

...
Muốn vậy chỉ có post file cần lên thì mọi người mới giúp được còn không thì chỉ phán/chém chung chung thôi

Thớt nói như vậy (xem trích dẫn bên dưới) là chính họ muốn cái "chung chung". Tức là một cái hàm chung chung có thể thay thế VLookup trong hầu hết các trường hợp.
Điểm mà thớt không biết là chính cái tên U đê ép nó đã là để giải quyết trường hợp đặc thù. Bảo đem áp dụng chung chung thì có phải là làm chuyện trái ngược?

.... Ở công ty có bạn chưa biết VBA, nên minh muốn viết qua UDF mà không phải bằng Sub. ...

 

[tam888]

Thớt nói như vậy (xem trích dẫn bên dưới) là chính họ muốn cái "chung chung". Tức là một cái hàm chung chung có thể thay thế VLookup trong hầu hết các trường hợp.
Điểm mà thớt không biết là chính cái tên U đê ép nó đã là để giải quyết trường hợp đặc thù. Bảo đem áp dụng chung chung thì có phải là làm chuyện trái ngược?

Vậy chủ thớt tốt bụng thật, thấy bạn của mình chưa biết VBA nên muốn giúp, và tạo việc làm cho GPE -vậy đúng tiêu chí của nhiều người trên này mong muốn rồi.

 

[SA_DQ]

Ngày xuân dong dài thiệt. Nhưng chủ bài đăng chắc bị lo cho cái Tết rồi, . . . mất tăm!

 

[VetMini]

Vậy chủ thớt tốt bụng thật, thấy bạn của mình chưa biết VBA nên muốn giúp, và tạo việc làm cho GPE -vậy đúng tiêu chí của nhiều người trên này mong muốn rồi.

Muốn giúp thì chỉ chỗ cho ngừoi ta hỏi. Chứ hỏi giùm là muốn lập công, trộ nghề.

Công ty gì mà có cả đống người làm việc với file Excel 70-100 nghìn dòng, lại không có lấy một chuyên viên lọc dữ liệu.
1. công ty keo kiệt quá sức. Không muốn bỏ tiền huấn luyện. Không chịu mua phần mềm chuyên (SQL Server chẳng hạn)
2. công ty hoàn toàn không kiểm soát được dữ liệu của mình. Nhân viên mạnh ai nấy làm.

Ngày xuân dong dài thiệt. Nhưng chủ bài đăng chắc bị lo cho cái Tết rồi, . . . mất tăm!

Còn đang tính làm cách nào đưa một cái file 70-100 nghìn dòng lên đây.

 

[Nguyễn Duy Tuân]

Dữ liệu nguồn 100.000 dòng và chậm thì cần phân tích bạn đang nói chậm về cái gì?
1. Chậm lúc nhập liệu? Thường do người làm Excel thích có cột nhập mã các cột bên phải là các hàm Vlookup để lấy thông tin từ mã. Đây là lý do chạy chậm -> Giải pháp là xóa bỏ Vlookup tìm cách thức khác thay thế mà vẫn đpá ứng có cột thông tin tham chiếu gần như Vlookup, tìm kiếm Google "Nhập liệu nâng cao".

2. Chậm khi làm báo cáo lọc chi tiết hoặc làm tổng hợp với các hàm Excel: Vlookup, SumIFs,... chạy chậm việc này là đương nhiên, cách làm báo cáo của Excel từ khi nó sinh ra là, mỗi ô là một công thức, mỗi công thức lại truy tìm trong một mảng giá trị lớn. Nếu báo cáo có 10 cột, và 10.000 dòng thì bạn sẽ có 100.000 công thức, mỗi công thức truy tìm vào mảng có 5.000 phần tử, bạn hãy nhẩm số phép tính Excel phải làm, chậm sẽ là đương nhiên. Trong trường hợp này bạn có 2 lựa chọn cơ bản: 1: nhờ hoặc thuê người viết Sub - code VBA tạo báo cáo; 2: dùng hàm trả về mảng từ Add-in có sẵn - Tim Google "Báo cáo động" (mất phí mua tool).

 

[tam888]

Muốn giúp thì chỉ chỗ cho ngừoi ta hỏi. Chứ hỏi giùm là muốn lập công, trộ nghề.

Công ty gì mà có cả đống người làm việc với file Excel 70-100 nghìn dòng, lại không có lấy một chuyên viên lọc dữ liệu.
1. công ty keo kiệt quá sức. Không muốn bỏ tiền huấn luyện. Không chịu mua phần mềm chuyên (SQL Server chẳng hạn)
2. công ty hoàn toàn không kiểm soát được dữ liệu của mình. Nhân viên mạnh ai nấy làm.


Còn đang tính làm cách nào đưa một cái file 70-100 nghìn dòng lên đây.

Không muốn làm, cũng không muốn tuyển người, nhưng lại dữ liệu nhiều, đành nhờ GPE

 

[SA_DQ]

Không muốn làm, cũng không muốn tuyển người, nhưng lại dữ liệu nhiều, đành nhờ GPE

Cũng là phương cách tốt, chả ai chê trách; Nhưng lại bí mật cả dữ liệu nữa thì thánh mới giúp được!

Chúc mọi người có mùa xuân tươi vui & đầm ấm!

 

[VetMini]

Cũng là phương cách tốt, chả ai chê trách; Nhưng lại bí mật cả dữ liệu nữa thì thánh mới giúp được!

Chúc mọi người có mùa xuân tươi vui & đầm ấm!

Có thể đối với bạn là phương cách tốt. Nhưng đối với tôi thì không.
Làm việc với mức trăm ngàn dòng dữ liệu là phải có quy cách tổ chức - đó là lý do sanh ra từ "cơ sở dữ liệu".
Nhiều người làm việc với cả đống dữ liệu không tổ chức là không có quy cách.

Xe gắn máy, xe hơi, và xe tải là ba bậc khác nhau. Không cứ ngừoi biết đạp ăm b ray da, gạt số thì tự động nhảy qua lái xe hơi, và cứ biết ôm vô lăng là tự động lái được xe tải.

 

[SA_DQ]

Có thể chưa tốt cho bạn ý, nhưng sẽ tốt cho 1 vài người muốn tiếp cận &/hay thử sức với 1 CSDL mới & đủ lớn (& có thể là lạ lẵm thi vị)
Nhứt là dịp đầu xuân rỗi rãnh vi bất thiện!

 

[thinhnx22]

Dạ, cảm ơn bác Sa, cháu đang chuẩn bị tết nên không có thời gian vào diễn đàn. Cơ sở dữ liệu cũng bình thường thôi ạ. Từ dữ liệu nguồn, dùng vlookup tham chiếu dữ liệu cần lấy, dùng sumifs để tổng hợp dữ liệu nhưng do số đong lớn như anh Tuân đã phân tích nên excel calculated tính toán chậm. Cháu sẽ cố gắng gửi file giả lập. Qua các anh, các bác tư vấn có thể dùng Mysql, sub vba, power pivot, Data model. Cháu sẽ tìm hiểu thêm. Cảm ơn các bác, các anh nhiều kinh nghiệm về excel và vba tư vấn. Chúc mọi người ăn tết vui, nhiều sức khoẻ, thành công trong năm mới.