Joran đang so sánh 13 mô hình hỗ trợ tài chính sinh viên khác nhau (theo các tỉnh / vùng lãnh thổ ở Canada). Mỗi mô hình có một bảng tính riêng được cung cấp vào một trang tóm tắt. Joran muốn có thể so sánh mức độ phức tạp của từng bảng tính riêng lẻ (tỉnh / lãnh thổ) một cách cụ thể. Khi sử dụng công cụ ‘Đánh giá công thức’, mỗi bước của một công thức nhất định sẽ được tách biệt. Anh ấy muốn biết liệu có cách nào để đếm số ‘bước’ đánh giá có trong toàn bộ bảng tính hay không. Điều này sẽ cung cấp cho Joran một số đo khách quan có thể so sánh được trên sổ làm việc.

Thật không may, có vẻ như Excel không cung cấp công cụ ‘Đánh giá Công thức’, dưới dạng một đối tượng, trong VBA. Điều đó có nghĩa là để đánh giá một trang tính theo cách này sẽ yêu cầu phát triển một macro để xem qua tất cả các ô, kéo ra các công thức và đếm “cụm từ chức năng” trong mỗi công thức. Điều này có thể trở nên khá phức tạp trong thời gian ngắn.

Mặc dù điều này có thể được thực hiện (sang một bên phức tạp), nó có thể không tạo ra kết quả mong muốn. Ví dụ, hãy xem xét công thức sau:

=A1+A2+A3

Công thức này có hai hoặc ba đánh giá, tùy thuộc vào cách bạn đếm các đánh giá. Bây giờ hãy xem xét công thức sau:

=SUM(A1:A3)

Công thức này có chức năng tương đương với công thức trước đó, nhưng nó chỉ yêu cầu một đánh giá. Không công thức nào phức tạp hơn công thức kia, nhưng nếu bạn chỉ tính các đánh giá, công thức đầu tiên sẽ hiển thị phức tạp gấp hai hoặc ba lần công thức thứ hai. Một số hàm, mặc dù được tính là một phép đánh giá, phức tạp hơn nhiều so với phép cộng đơn giản và điều này cũng bị bỏ qua khi chỉ đếm các cụm từ.

Hơn nữa, nếu công thức của bạn bao gồm bất kỳ loại cấu trúc ra quyết định nào (thường sử dụng hàm IF), thì độ phức tạp của công thức không bị ràng buộc trực tiếp với số lượng cụm từ hoặc đánh giá trong công thức. Lý do là vì những gì Excel thực sự tính toán được xác định tại thời điểm tính toán dựa trên các điều kiện hiện tại tại thời điểm đó.

Việc nhận ra rằng các cụm từ đếm không phải là cách tốt nhất để xác định độ phức tạp của mô hình tự nhiên dẫn đến câu hỏi làm thế nào để người ta có thể xác định. Những gì có thể được kiểm tra trong một trang tính để xác định độ phức tạp một cách khách quan. Không có cách nào dễ dàng để thực hiện một quyết tâm như vậy.

Điều này được giải thích một phần bởi Steve, một người đăng ký ExcelTips, một nhà hóa học phát triển tiến sĩ. Là một phần công việc của mình, anh ấy thiết kế các thí nghiệm và phân tích chúng để tạo ra các mô hình kinh doanh và hậu cần. Đây là nhận xét của anh ấy:

=== [begin Steve's comments on model complexity]===

Một đánh giá đơn giản hơn về độ phức tạp của mô hình, là số thuật ngữ được sử dụng trong mô hình để nhận được kết quả. Khi đánh giá các mô hình, theo kinh nghiệm của tôi, tôi thấy rằng bạn muốn tìm sự cân bằng giữa kết quả đơn giản và kết quả. Người ta thêm các yếu tố cung cấp nội dung thực vào kết quả và loại bỏ những yếu tố không cung cấp.

Phép đo mức độ phù hợp được sử dụng trong các mô hình được gọi là _ hệ số xác định_ (còn được gọi là R ^ 2, R-bình phương). Nó là thước đo mức độ phù hợp của mô hình với dữ liệu thực tế. Nó là tỷ lệ của “tổng bình phương” của hồi quy, có nghĩa là bình phương của giá trị của mô hình so với thực tế cho từng giá trị riêng lẻ, chia cho tổng bình phương tổng, có nghĩa là bình phương của sự khác biệt của mỗi điểm với trung bình của các điểm.

Nhưng khi người ta thêm độ phức tạp (bằng cách thêm các điều khoản bổ sung) thì R ^ 2 sẽ cao hơn khi bậc tự do giảm xuống. Một R ^ 2 đã điều chỉnh là so sánh tốt:

Adjusted R^2 = 1 - (1-R^2) * (n-1) / (n-k-1)

trong đó R ^ 2 được mô tả ở trên, n là số điểm và k là số hạng được sử dụng trong mô hình.

Thông thường, người ta cố gắng đạt được R ^ 2 được điều chỉnh tối đa.

=== [end Steve's comments on model complexity]===

Trừ khi một người thông thạo các phương pháp thống kê và toán học, nếu không thì rất dễ bị lạc vào chi tiết của việc xác định độ phức tạp theo cách này.

Nếu việc đi xuống một con đường mòn như vậy không theo ý muốn của bạn (hoặc sự đào tạo của bạn), thì có một cách khác để xác định độ phức tạp: Mất bao lâu để đi đến kết quả bằng cách sử dụng mỗi mô hình? Nếu bạn đặt trang tính của mỗi mô hình vào sổ làm việc của chính nó, bạn có thể sử dụng macro để đo thời gian thực hiện tính toán lại trên sổ làm việc đó. Vì Excel sử dụng các phương pháp nội bộ giống nhau để thực hiện tính toán lại trên từng sổ làm việc, bạn có thể dễ dàng so sánh thời gian trên từng sổ làm việc. Đối với một chỉ báo chung chung về độ phức tạp, sổ làm việc mất nhiều thời gian nhất để tính toán lại là sổ phức tạp nhất.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13096) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.