Đặt tên (Name) và ảnh hưởng của nó tới tốc độ tính toán

[hoangdanh282vn]

Chào các bạn.
Bấy lâu nay chúng ta hay dùng Name để đặt tên cho vùng, cho 1 điều kiện logic hay cho một công thức hoàn chỉnh nhằm rút ngắn công thức cũng như đáp ứng yêu cầu giới hạn của các công thức.
Nhưng các bạn có để ý về sự thay đổi tốc độ tính toán của công thức khi gán name hay không.
Theo mình thử nghiệm thì mình thấy rằng :
- Nếu để nguyên công thức không dùng name thì độ độ xử lý nhanh, tuy hơi dài dòng.
- Nếu dùng name để gán cho vùng hay 1 công thức hoàn chỉnh thì tốc độ xử lý chậm hơn tí.
- Nếu dùng Name gán cho 1 biểu thức logic thì tốc độ giảm đi hẳn (với dữ liệu nhiều).
Các bạn hãy cho những xét và giải pháp nhằm mang lại hiệu quả khi áp dụng name trong công thức nha.

 

[anhtuan1066]

Oh... ho... điều đó tất nhiên, nhất là những name động dạng công thức mãng.. Ko khéo xử lý nó còn chậm hơn là dùng cột phụ... he... he... Nói chung sẽ tùy từng dử liệu rồi thí nghệm để ra quyết định chính xác nên dùng cái gì!
"Hiện đại" "hại điện"... Cái gì tiện nghi đều phải trả giá.. đúng ko?
ANH TUẤN

 

[feelingyes]

He he
Mấy cái món này may quá nhờ bác AnhTuan gợi ý giúp nên file của tôi bây giờ chạy veo véo. Hầu như trong dữ liệu của chúng ta thường liên quan đến dữ liệu ngày. Dựa vào đây ta đặt name theo ngày
-VD tại file của em đang làm, để tốc độ xử lý nhanh (trong phần ngày em để là From = 1, To =1
Khi nào có việc cần thì chuyển sang To = 31

 

[hoangdanh282vn]

Mình nói thêm về hàm mảng tí.
Nếu là hàm {Sum()} thì sẽ chậm hơn rất nhiều so với Sumproduct
Trong công thức mảng, nếu thay * bằng , thì tốc độ được cải thiện rất nhiều.
Trong khi thiết kế cấu trúc dữ liệu, chúng ta nên tạo vùng nhập liệu và báo cáo có độ lớn vừa phải, khi cần thì copy và insert copied cells. Như thế thì sẽ làm cho tốc độ nhanh hơn.

 

[anhtuan1066]

Mình nói thêm về hàm mảng tí.
Nếu là hàm {Sum()} thì sẽ chậm hơn rất nhiều so với Sumproduct
Trong công thức mảng, nếu thay * bằng , thì tốc độ được cải thiện rất nhiều.
Trong khi thiết kế cấu trúc dữ liệu, chúng ta nên tạo vùng nhập liệu và báo cáo có độ lớn vừa phải, khi cần thì copy và insert copied cells. Như thế thì sẽ làm cho tốc độ nhanh hơn.

Có thể... vậy tốt nhất đừng xài 2 thằng em ấy.. chuyển sang SUMIF là đẹp luôn... ko nhanh mới lạ!
Nói đúng ra tôi quan tâm nhiều đến cách thiết kế dử liệu hơn là cách xử lý nó... Thiết kế hay sẽ làm cho việc xử lý nhẹ nhàng hơn rất nhiều
ANH TUẤN

 

[tedaynui]

Có thể... vậy tốt nhất đừng xài 2 thằng em ấy.. chuyển sang SUMIF là đẹp luôn... ko nhanh mới lạ!
Nói đúng ra tôi quan tâm nhiều đến cách thiết kế dử liệu hơn là cách xử lý nó... Thiết kế hay sẽ làm cho việc xử lý nhẹ nhàng hơn rất nhiều
ANH TUẤN

Tiện đây anh Tuấn cho em hỏi cái, thường thì em dùng SUMPRODUCT nhưng có cách nào dùng SUMIF thay cho SUMPRODUCT không? dùng để tính tổng hoặc đếm với nhiều điều kiện chẳng hạn (Dùng Excel 2003).
Cám ơn anh nhiều !

Thân

 

[mymichau]

To: Tedaynui
Trong công việc thống kê, thường cộng và đếm với nhiều điều kiện khác nhau ( có thể trên 10 ĐK), em thường dùng hàm Sumif và thấy thỏa được yêu cầu tính toán - tốc độ. Tuy nhiên đều phải dùng cột phụ. Nh AnhTuan1066 nói quan trọng là thiết kế dữ liệu, làm sao cột phụ thỏa mản yêu cầu đặt ra vừa gọn, hợp lý, dễ thay đổi.

 

[anhtuan1066]

Tiện đây anh Tuấn cho em hỏi cái, thường thì em dùng SUMPRODUCT nhưng có cách nào dùng SUMIF thay cho SUMPRODUCT không? dùng để tính tổng hoặc đếm với nhiều điều kiện chẳng hạn (Dùng Excel 2003).
Cám ơn anh nhiều !

Thân

Tôi thường làm theo cách dùng cột phụ hoặc đặt name để loại bỏ điều kiện, khi chỉ còn 1 điều kiện duy nhất thì ta hoàn toàn có thể dùng SUMIF... File hôm trước tôi gữi lên thí nghiệm về tốc độ đó là 1 ví dụ...
Còn 1 cách nữa giúp cho SUMPRODUCT nhanh hơn, đó là làm sao thu gọn vùng dử liệu lại... Ví dụ vùng dử liệu thật là 20.000 dòng, ta sẽ dùng name hoặc cột phụ sao đó để SUMPRODUCT chỉ tính trong vùng nhỏ hơn 20.000 dòng (càng nhỏ càng nhanh)
ANH TUẤN

 

[feelingyes]

Đây là một ví dụ mà AnhTuan đã gợi ý cho tôi
-Có 3 điều kiện (ngày, tên, số lượng) nhưng ta thiết kế dữ liệu sao cho tên và số lượng offset theo ngày
-Sau đó mình dùng công thức sumif bình thường

 

[boong]

Sao máy em báo lỗi dòng này vậy.!

[COLOR=#000000]Private Sub Workbook_Open()[/COLOR] [COLOR=#000000][COLOR=red]Sheets(3).Select[/COLOR] 'Msgbox (" Hello, wellcome new costcontroller. You should follow this guide") 'Msgbox ("In order to save capacity, you should key all Items that's the same kind in  one cell for each outlet in one day") 'Msgbox ("For eg: you collect in HC Bill. There 4 Coke (can) for 04 bill. So you can focus to one cell =1+1+1+1") 'Msgbox ("In order to make Excell caculate faster, you should keep is shortest For eg From X to X -For Eg From 1 To 1") Msgbox ("The happiness will come to you. Let's try") Sheets(3).Select End Sub[/COLOR]

 

[SA_DQ]

Sao máy em báo lỗi dòng này vậy.!

[COLOR=#000000]Private Sub Workbook_Open()[/COLOR]
[COLOR=#000000][COLOR=red]Sheets(3).Select[/COLOR]
'Msgbox (" Hello, wellcome new costcontroller. You should follow this guide")
'Msgbox ("In order to make Excell caculate faster, you should keep is shortest For eg From X to X -For Eg From 1 To 1")
Msgbox ("The happiness will come to you. Let's try")
Sheets(3).Select
End Sub[/COLOR]

Bạn xem lại Sheet(3) thân thương của bạn còn tồn tại hay ai đã cho nó đến miền cực lạc rồi cũng nên!
Bạn mở CS VBE xem thử cái tên cúng cơm của nó còn liệt kê trên danh sách các sheets hay không.
( Lần sau bạn ghi cả dòng báo lỗi lên có phải nhận nhanh câu trả lời tương tự như vầy không(!)


Chúc Vui Vẽ!

 

[tedaynui]

To: Tedaynui
Trong công việc thống kê, thường cộng và đếm với nhiều điều kiện khác nhau ( có thể trên 10 ĐK), em thường dùng hàm Sumif và thấy thỏa được yêu cầu tính toán - tốc độ. Tuy nhiên đều phải dùng cột phụ. Nh AnhTuan1066 nói quan trọng là thiết kế dữ liệu, làm sao cột phụ thỏa mản yêu cầu đặt ra vừa gọn, hợp lý, dễ thay đổi.

Tất nhiên mình không muốn dùng cột phụ. Còn nếu đặt Name thay cho cột phụ thì mình chưa nghĩ ra cách nào cho tối ưu vì mỗi cột mỗi Name thì chắc "1 rừng Name" luôn quá (Hiện tại mình cần thống kê gần 50 cột và 5.000 dòng)

@ To Anh Tuấn
Dưới đây em gởi anh 1 File làm ví dụ. Trước giờ, em vẫn dùng SUMPRODUCT để thống kê nhưng cảm thấy nó làm File chậm hẳn đi nên luôn để File ở chế độ Caculation = Manual để chạy cho nhanh hơn. Nhờ anh Tuấn và các bạn xem thử có thể đặt Name hay dùng công thức nào đó để nó tối ưu hoá nó không (tất nhiên không cột phụ)

Xin cám ơn rất nhiều !
TDN

 

[anhtuan1066]

He... he... Quá dể luôn!
Bài toán này tất nhiên là dùng SUMPRODUCT rồi chứ ko thể SUMIF (vì đếm chứ ko phải tổng)... Tuy nhiên để tăng tốc thì ta dùng name động để giãm nhỏ vùng dử liệu lại... Yêu cầu bắt buộc là phải sort 1 cột nào đó.. Ở đây tôi sort cột LOP!
Thầy xem thử file nhé!
ANH TUẤN

 

[tedaynui]

Cám ơn anh Tuấn rất nhiều. Đúng là tốc độ được cải thiện. Cách đặt Name của anh quá tuyệt vời, em nghĩ mãi không ra. Nhưng anh Tuấn ơi, anh xem cách thống kê theo CÁCH 1 xem, anh cải thiện giúp em với.
Cám ơn anh nhiều nhiều lắm lắm !
Thân
TDN

 

[anhtuan1066]

He... he... Thì cũng hao hao giống cách 2 ấy thôi... vấn đề là phải làm sao cho name động ấy "bắt" đúng vào tên MON... Tôi chưa làm, nhưng biết chắc sẽ ko thoát khỏi phải kết hợp INT, MOD, ROW và COLUMN... Cái vụ này có trong file Calendar của tôi ấy!
Các bạn nghiên cứu xem... (giờ đang bận tay tí...)
ANH TUẤN

 

[anhtuan1066]

Đây! Mới rảnh tay.. làm luôn cho thầy cách 1 nhé (chú ý name TestC1)... Ở đây tôi dùng hàm Column để định vị, vì thế phải hết sức cẩn thận khi di chuyển dử liệu đi nơi khác... Cách để nó luôn luôn chính xác cũng ko phải là làm ko dc, nhưng thuật toán định vị sẽ khó khăn hơn rất nhiều (ko đáng)
ANH TUẤN

 

[tedaynui]

Đây! Mới rảnh tay.. làm luôn cho thầy cách 1 nhé (chú ý name TestC1)... Ở đây tôi dùng hàm Column để định vị, vì thế phải hết sức cẩn thận khi di chuyển dử liệu đi nơi khác... Cách để nó luôn luôn chính xác cũng ko phải là làm ko dc, nhưng thuật toán định vị sẽ khó khăn hơn rất nhiều (ko đáng)
ANH TUẤN

Cám ơn Anh rất nhiều. Dựa trên cách làm của anh, em cải tiến chút : bỏ bớt dòng 5 và có thể copy nhanh công thức. =SUMPRODUCT((TestC1<IF(A$4=0,11,A$4))*(TestC1>=B$4))

Em đã Test trên File thức tế, tốc độ tính toán được cải tiến rất nhiều
Cám ơn Anh nhiều lắm lắm !
Thân
TDN