Ngôn ngữ tốt nhất cho tự động hóa Excel

Tách mối quan tâm

Một trong những nguyên tắc thiết kế quan trọng nhất trong lập trình là tách biệt các mối quan tâm, đôi khi còn được gọi là mô đun hóa. Điều đó có nghĩa là một bộ chức năng có liên quan nên được chăm sóc bởi một phần độc lập của chương trình để có thể dễ dàng thay thế nó mà không ảnh hưởng đến phần còn lại của ứng dụng. Ở mức cao nhất, một ứng dụng thường được chia thành các lớp sau

• Lớp trình bày

• lớp kinh doanh

• Lớp dữ liệu

Để giải thích các lớp này, hãy xem xét một công cụ chuyển đổi tiền tệ đơn giản như công cụ được hiển thị trong. Bạn sẽ tìm thấy currency_converter. xlsx Excel trong thư mục xl của kho lưu trữ đồng hành

Đây là cách ứng dụng hoạt động. lần lượt nhập Số tiền và Đơn vị tiền tệ vào các ô A4 và B4, và Excel sẽ chuyển đổi số tiền này thành đô la Mỹ trong ô D4. Nhiều ứng dụng bảng tính theo thiết kế như vậy và được các doanh nghiệp sử dụng hàng ngày. Hãy để tôi chia nhỏ ứng dụng thành các lớp của nó

Đây là những gì bạn nhìn thấy và tương tác với, tôi. e. , giao diện người dùng. giá trị của các ô A4, B4 và D4 cùng với nhãn của chúng tạo lớp trình bày của bộ chuyển đổi tiền tệ

Lớp này xử lý logic dành riêng cho ứng dụng. ô D4 xác định cách số tiền được chuyển đổi thành USD. Công thức

If i < 5 Then
    Debug.Print "i is smaller than 5"
ElseIf i <= 10 Then
    Debug.Print "i is between 5 and 10"
Else
    Debug.Print "i is bigger than 10"
End If

Như tên cho thấy, lớp này đảm nhiệm việc truy cập dữ liệu. phần

If i < 5 Then
    Debug.Print "i is smaller than 5"
ElseIf i <= 10 Then
    Debug.Print "i is between 5 and 10"
Else
    Debug.Print "i is bigger than 10"
End If

Lớp dữ liệu truy cập dữ liệu từ bảng tỷ giá hối đoái bắt đầu trong ô F3 và đóng vai trò là cơ sở dữ liệu của ứng dụng nhỏ này. Nếu để ý kỹ, bạn có thể nhận thấy rằng ô D4 xuất hiện ở cả 3 lớp. ứng dụng đơn giản này kết hợp các lớp trình bày, kinh doanh và dữ liệu trong một ô duy nhất

Đây không nhất thiết là một vấn đề đối với công cụ chuyển đổi tiền tệ đơn giản này, nhưng thông thường, những gì bắt đầu khi một tệp Excel nhỏ sẽ sớm biến thành một ứng dụng lớn hơn nhiều. Làm thế nào tình trạng này có thể được cải thiện? . Thông thường, điều này được kết hợp với việc xác định một mã màu nhất định cho mỗi lớp, e. g. , nền xanh lam cho tất cả các ô nhập liệu. Trong , chúng tôi sẽ xây dựng một ứng dụng thực tế dựa trên các lớp này. Excel sẽ là lớp trình bày, trong khi lớp doanh nghiệp và dữ liệu được chuyển sang Python, nơi việc cấu trúc mã của bạn đúng cách sẽ dễ dàng hơn nhiều

Bây giờ bạn đã biết tách biệt các mối quan tâm có nghĩa là gì, hãy cùng tìm hiểu nguyên tắc DRY là gì

nguyên tắc KHÔ

The Pragmatic Programmer của Hunt và Thomas (Pearson Education) đã phổ biến nguyên tắc DRY. đừng lặp lại chính mình. Không có mã trùng lặp có nghĩa là ít dòng mã hơn và ít lỗi hơn, giúp mã dễ bảo trì hơn. Nếu logic nghiệp vụ của bạn nằm trong các công thức ô của bạn, thì thực tế là không thể áp dụng nguyên tắc DRY, vì không có cơ chế nào cho phép bạn sử dụng lại nó trong một sổ làm việc khác. Thật không may, điều này có nghĩa là một cách phổ biến để bắt đầu một dự án Excel mới là sao chép sổ làm việc từ dự án trước đó hoặc từ một mẫu

Nếu bạn viết VBA, đoạn mã có thể tái sử dụng phổ biến nhất là một hàm. Ví dụ: một chức năng cho phép bạn truy cập vào cùng một khối mã từ nhiều macro. Nếu bạn có nhiều chức năng mà bạn luôn sử dụng, bạn có thể muốn chia sẻ chúng giữa các sổ làm việc. Công cụ tiêu chuẩn để chia sẻ mã VBA giữa các sổ làm việc là phần bổ trợ, nhưng phần bổ trợ VBA thiếu cách thức mạnh mẽ để phân phối và cập nhật chúng. Mặc dù Microsoft đã giới thiệu một cửa hàng bổ trợ nội bộ của Excel để giải quyết vấn đề đó, nhưng điều này chỉ hoạt động với các bổ trợ dựa trên JavaScript, vì vậy đây không phải là một tùy chọn cho các lập trình viên VBA. Điều này có nghĩa là việc sử dụng phương pháp sao chép/dán với VBA vẫn còn rất phổ biến. giả sử rằng bạn cần một hàm spline khối trong Excel. Hàm spline bậc ba là một cách để nội suy một đường cong dựa trên một vài điểm đã cho trong một hệ tọa độ và thường được các nhà giao dịch có thu nhập cố định sử dụng để tạo ra một đường cong lãi suất cho tất cả các kỳ hạn dựa trên một số kết hợp kỳ hạn/lãi suất đã biết. Nếu bạn tìm kiếm “Cubic Spline Excel” trên internet, sẽ không mất nhiều thời gian cho đến khi bạn có một trang mã VBA thực hiện những gì bạn muốn. Vấn đề với điều này là thông thường nhất, các chức năng này được viết bởi một người có thể có mục đích tốt nhưng không có tài liệu hoặc thử nghiệm chính thức. Có thể chúng hoạt động với phần lớn đầu vào, nhưng còn một số trường hợp cạnh không phổ biến thì sao? . Ít nhất, đó là những gì bạn sẽ nghe được từ kiểm toán viên nội bộ của mình khi họ tìm ra nguồn gốc của mã

Python giúp dễ dàng phân phối mã bằng cách sử dụng trình quản lý gói, như chúng ta sẽ thấy trong phần cuối của chương này. Tuy nhiên, trước khi chúng ta đến đó, hãy tiếp tục với thử nghiệm, một trong những nền tảng của sự phát triển phần mềm vững chắc

thử nghiệm

Khi bạn yêu cầu một nhà phát triển Excel kiểm tra sổ làm việc của họ, rất có thể họ sẽ thực hiện một số kiểm tra ngẫu nhiên. nhấp vào một nút và xem liệu macro có còn thực hiện những gì nó phải làm hay thay đổi một vài đầu vào và kiểm tra xem đầu ra có hợp lý không. Tuy nhiên, đây là một chiến lược mạo hiểm. Excel giúp dễ dàng đưa ra các lỗi khó phát hiện. Ví dụ: bạn có thể ghi đè lên một công thức bằng giá trị được mã hóa cứng. Hoặc bạn quên điều chỉnh công thức trong một cột ẩn

Khi bạn yêu cầu một nhà phát triển phần mềm chuyên nghiệp kiểm tra mã của họ, họ sẽ viết các bài kiểm tra đơn vị. Như tên gợi ý, đó là một cơ chế để kiểm tra các thành phần riêng lẻ trong chương trình của bạn. Ví dụ: kiểm tra đơn vị đảm bảo rằng một chức năng duy nhất của chương trình hoạt động bình thường. Hầu hết các ngôn ngữ lập trình đều cung cấp cách chạy thử nghiệm đơn vị tự động. Chạy thử nghiệm tự động sẽ tăng đáng kể độ tin cậy của cơ sở mã của bạn và đảm bảo hợp lý rằng bạn sẽ không phá vỡ bất kỳ thứ gì hiện đang hoạt động khi bạn chỉnh sửa mã của mình

Nếu bạn xem công cụ chuyển đổi tiền tệ trong , bạn có thể viết một bài kiểm tra để kiểm tra xem công thức trong ô D4 có trả về đúng 105 USD hay không với các thông tin nhập sau. 100 EUR dưới dạng số tiền và 1. 05 là tỷ giá hối đoái EURUSD. Tại sao điều này giúp? . thay vì nhân số tiền với tỷ giá hối đoái, bạn chia cho nó—xét cho cùng, làm việc với tiền tệ có thể gây nhầm lẫn. Khi bạn chạy thử nghiệm trên, bạn sẽ nhận được lỗi thử nghiệm là 100 EUR / 1. 05 sẽ không dẫn đến 105 USD nữa như kỳ vọng của bài kiểm tra. Như vậy, bạn có thể phát hiện và sửa công thức trước khi bàn giao bảng tính cho người dùng của mình

Gần như tất cả các ngôn ngữ lập trình truyền thống đều cung cấp một hoặc nhiều khung kiểm tra để viết các bài kiểm tra đơn vị mà không cần nỗ lực nhiều—nhưng Excel thì không. May mắn thay, khái niệm về kiểm tra đơn vị đủ đơn giản và bằng cách kết nối Excel với Python, bạn sẽ có quyền truy cập vào các khung kiểm tra đơn vị mạnh mẽ của Python. Mặc dù trình bày sâu hơn về các bài kiểm tra đơn vị nằm ngoài phạm vi của cuốn sách này, nhưng tôi mời bạn xem qua bài đăng trên blog của tôi, trong đó tôi hướng dẫn bạn qua chủ đề với các ví dụ thực tế

Các bài kiểm tra đơn vị thường được thiết lập để chạy tự động khi bạn cam kết mã của mình với hệ thống kiểm soát phiên bản của mình. Phần tiếp theo giải thích hệ thống kiểm soát phiên bản là gì và tại sao chúng khó sử dụng với các tệp Excel

Kiểm soát phiên bản

Một đặc điểm khác của các lập trình viên chuyên nghiệp là họ sử dụng một hệ thống để kiểm soát phiên bản hoặc kiểm soát nguồn. Hệ thống kiểm soát phiên bản (VCS) theo dõi các thay đổi trong mã nguồn của bạn theo thời gian, cho phép bạn xem ai đã thay đổi cái gì, khi nào và tại sao, đồng thời cho phép bạn hoàn nguyên về các phiên bản cũ vào bất kỳ thời điểm nào. Hệ thống kiểm soát phiên bản phổ biến nhất hiện nay là Git. Ban đầu nó được tạo ra để quản lý mã nguồn Linux và kể từ đó đã chinh phục thế giới lập trình—thậm chí Microsoft đã sử dụng Git vào năm 2017 để quản lý mã nguồn Windows. Ngược lại, trong thế giới Excel, hệ thống kiểm soát phiên bản phổ biến nhất cho đến nay có dạng một thư mục nơi các tệp được lưu trữ như thế này

currency_converter_v1.xlsx
currency_converter_v2_2020_04_21.xlsx
currency_converter_final_edits_Bob.xlsx
currency_converter_final_final.xlsx

Nếu, không giống như trong mẫu này, nhà phát triển Excel tuân theo một quy ước nhất định trong tên tệp, thì không có gì sai với điều đó. Nhưng việc lưu giữ lịch sử phiên bản của các tệp cục bộ sẽ khóa bạn khỏi các khía cạnh quan trọng của kiểm soát nguồn dưới dạng cộng tác dễ dàng hơn, đánh giá ngang hàng, quy trình đăng xuất và nhật ký kiểm tra. Và nếu bạn muốn làm cho sổ làm việc của mình an toàn và ổn định hơn, bạn không muốn bỏ lỡ những điều này. Thông thường nhất, các lập trình viên chuyên nghiệp sử dụng Git cùng với nền tảng dựa trên web như GitHub, GitLab, Bitbucket hoặc Azure DevOps. Các nền tảng này cho phép bạn làm việc với cái gọi là yêu cầu kéo hoặc yêu cầu hợp nhất. Chúng cho phép các nhà phát triển chính thức yêu cầu các thay đổi của họ được hợp nhất vào cơ sở mã chính. Yêu cầu kéo cung cấp các thông tin sau

• Ai là tác giả của những thay đổi

• Khi nào những thay đổi được thực hiện

• Mục đích của những thay đổi như được mô tả trong thông báo cam kết là gì

• các chi tiết của những thay đổi như được hiển thị bởi chế độ xem khác là gì, tôi. e. , chế độ xem làm nổi bật các thay đổi bằng màu xanh lá cây đối với mã mới và màu đỏ đối với mã đã xóa

Điều này cho phép đồng nghiệp hoặc trưởng nhóm xem xét các thay đổi và phát hiện những điểm bất thường. Thông thường, một cặp mắt bổ sung sẽ có thể phát hiện ra một hoặc hai trục trặc hoặc đưa ra phản hồi có giá trị cho lập trình viên. Với tất cả những ưu điểm này, tại sao các nhà phát triển Excel lại thích sử dụng hệ thống tệp cục bộ và quy ước đặt tên của riêng họ thay vì một hệ thống chuyên nghiệp như Git?

• Nhiều người dùng Excel chỉ đơn giản là không biết về Git hoặc bỏ cuộc từ rất sớm vì Git có đường cong học tập tương đối dốc

• Git cho phép nhiều người dùng làm việc song song trên các bản sao cục bộ của cùng một tệp. Sau khi tất cả họ hoàn thành công việc của mình, Git thường có thể hợp nhất tất cả các thay đổi lại với nhau mà không cần bất kỳ sự can thiệp thủ công nào. Điều này không hoạt động đối với các tệp Excel. nếu chúng được thay đổi song song trên các bản sao riêng biệt, Git không biết cách hợp nhất những thay đổi này lại thành một tệp

• Ngay cả khi bạn quản lý để giải quyết các vấn đề trước đó, Git đơn giản là không mang lại nhiều giá trị với các tệp Excel như với các tệp văn bản. Git không thể hiển thị các thay đổi giữa các tệp Excel, cản trở quy trình đánh giá ngang hàng thích hợp

Vì tất cả những vấn đề này, công ty của tôi đã đưa ra xltrail, một hệ thống kiểm soát phiên bản dựa trên Git biết cách xử lý các tệp Excel. Nó ẩn đi sự phức tạp của Git để người dùng doanh nghiệp cảm thấy thoải mái khi sử dụng nó và cũng cho phép bạn kết nối với các hệ thống Git bên ngoài, chẳng hạn như trong trường hợp bạn đã theo dõi các tệp của mình bằng GitHub. xltrail theo dõi các thành phần khác nhau của sổ làm việc, bao gồm công thức ô, phạm vi được đặt tên, Truy vấn nguồn và mã VBA, cho phép bạn tận dụng các lợi ích cổ điển của kiểm soát phiên bản bao gồm đánh giá ngang hàng

Một tùy chọn khác giúp kiểm soát phiên bản dễ dàng hơn với Excel là chuyển logic nghiệp vụ của bạn từ Excel sang tệp Python, điều mà chúng tôi sẽ thực hiện trong. Vì các tệp Python rất dễ theo dõi bằng Git, nên bạn sẽ kiểm soát được phần quan trọng nhất của công cụ bảng tính của mình

Mặc dù phần này được gọi là Các phương pháp hay nhất về lập trình, nhưng nó chủ yếu chỉ ra lý do tại sao chúng khó tuân theo Excel hơn so với ngôn ngữ lập trình truyền thống như Python. Trước khi chúng ta chú ý đến Python, tôi muốn giới thiệu ngắn gọn về Power Query và Power Pivot, nỗ lực hiện đại hóa Excel của Microsoft

Power Query và Power Pivot

Với Excel 2010, Microsoft đã giới thiệu một bổ trợ có tên là Power Query. Power Query kết nối với vô số nguồn dữ liệu bao gồm sổ làm việc Excel, tệp CSV và cơ sở dữ liệu SQL. Nó cũng cung cấp các kết nối với các nền tảng như Salesforce và thậm chí có thể được mở rộng để kết nối với các hệ thống không có sẵn. Chức năng cốt lõi của Power Query là xử lý các bộ dữ liệu quá lớn để vừa với một bảng tính. Sau khi tải dữ liệu, bạn có thể thực hiện các bước bổ sung để làm sạch và thao tác với dữ liệu để dữ liệu ở dạng có thể sử dụng được trong Excel. Ví dụ: bạn có thể tách một cột thành hai, hợp nhất hai bảng hoặc lọc và nhóm dữ liệu của mình. Kể từ Excel 2016, Power Query không còn là một phần bổ trợ nữa nhưng có thể được truy cập trực tiếp trên tab ruy-băng Dữ liệu qua nút Lấy dữ liệu. Power Query chỉ khả dụng một phần trên macOS—tuy nhiên, nó đang được phát triển tích cực, do đó, nó sẽ được hỗ trợ đầy đủ trong bản phát hành Excel trong tương lai

Power Pivot song hành với Power Query. về mặt khái niệm, đây là bước thứ hai sau khi thu thập và làm sạch dữ liệu của bạn bằng Power Query. Power Pivot giúp bạn phân tích và trình bày dữ liệu của mình một cách hấp dẫn trực tiếp trong Excel. Hãy nghĩ về nó như một bảng tổng hợp truyền thống, giống như Power Query, có thể xử lý các tập dữ liệu lớn. Power Pivot cho phép bạn xác định các mô hình dữ liệu chính thức với các mối quan hệ và cấu trúc phân cấp, đồng thời, bạn có thể thêm các cột được tính toán thông qua ngôn ngữ công thức DAX. Power Pivot cũng được giới thiệu với Excel 2010 nhưng vẫn là một phần bổ trợ và cho đến nay vẫn chưa khả dụng trên macOS

Nếu bạn thích làm việc với Power Query và Power Pivot và muốn xây dựng bảng điều khiển trên chúng, thì Power BI có thể đáng xem—hãy xem tại sao

điện BI

Power BI là một ứng dụng độc lập được phát hành vào năm 2015. Đó là câu trả lời của Microsoft cho các công cụ kinh doanh thông minh như Tableau hoặc Qlik. Power BI Desktop miễn phí, vì vậy nếu bạn muốn sử dụng nó, hãy truy cập trang chủ Power BI và tải xuống—tuy nhiên, xin lưu ý rằng Power BI Desktop chỉ khả dụng cho Windows. Power BI muốn hiểu ý nghĩa của các tập dữ liệu lớn bằng cách trực quan hóa chúng trong bảng điều khiển tương tác. Về cốt lõi, nó dựa trên chức năng Power Query và Power Pivot giống như Excel. Các gói thương mại cho phép bạn cộng tác và chia sẻ trang tổng quan trực tuyến, nhưng những trang tổng quan này tách biệt với phiên bản dành cho máy tính để bàn. Lý do chính khiến Power BI trở nên thú vị trong bối cảnh của cuốn sách này là nó đã hỗ trợ các tập lệnh Python từ năm 2018. Python có thể được sử dụng cho phần truy vấn cũng như phần trực quan hóa bằng cách sử dụng các thư viện vẽ đồ thị của Python. Đối với tôi, việc sử dụng Python trong Power BI cảm thấy hơi rắc rối, nhưng phần quan trọng ở đây là Microsoft đã nhận ra tầm quan trọng của Python đối với phân tích dữ liệu. Theo đó, rất nhiều hy vọng rằng một ngày nào đó Python cũng sẽ tìm được đường chính thức vào Excel.

Vậy điều gì tuyệt vời ở Python khiến nó trở thành Power BI của Microsoft?