Excel là ngôn ngữ lập trình gì? Cách viết macro trong Excel bằng ngôn ngữ lập trình VBA
Bạn có biết Excel có công cụ dịch văn bản sang ngôn ngữ khác. Với Mini Translator, bạn có thể dịch một cụm từ, đoạn văn hoặc toàn bộ tệp được viết bằng ngôn ngữ khác. Vật phẩm này sử dụng chức năng chương trình Microsoft Trình dịch, được cài đặt mặc định trong Office Ứng dụng của Microsoft. Trong bài viết hôm nay tôi sẽ mô tả cách sử dụng chức năng này. Ngoài ra, chúng ta sẽ xem xét cách sử dụng các hàm WEBSERVICE và FILTER.XML để triển khai trình dịch Yandex trong Sổ làm việc Excel.
Dịch bằng Microsoft Translator
Vì vậy, bạn có một văn bản cần được dịch sang ngôn ngữ khác. Trong trường hợp của chúng tôi, tôi muốn dịch văn bản nằm trong ô A1:A3 từ tiếng Nga sang tiếng Anh. Chuyển đến tab Ôn tậpđến nhóm Ngôn ngữ, nhấn nút Dịch. Một bảng điều khiển sẽ xuất hiện ở phía bên phải của cuốn sách Những tài liệu tham khảo, trong đó cần chỉ rõ văn bản cần dịch và ngôn ngữ nguồn và ngôn ngữ cần dịch.
Có hai cách để chỉ định văn bản cần dịch. Việc đầu tiên, tầm thường, chúng tôi gõ văn bản vào trường bằng bút. Tìm kiếm, cho biết ngôn ngữ dịch, nhấp vào nút mũi tên ở bên trái của trường, đợi vài giây cho đến khi Microsoft Translator chọn những lời đúng và tận hưởng kết quả của bản dịch trong cùng một bảng.
Tùy chọn thứ hai tiên tiến hơn về mặt công nghệ và giả định rằng văn bản cần dịch đã có trên trang tính. Để thực hiện việc này, hãy giữ phím Alt và nhấp vào ô có văn bản cần dịch trong trường Tìm kiếm tấm Những tài liệu tham khảo bạn sẽ thấy cụm từ từ ô đã chọn và bên dưới là bản dịch của cụm từ đó. Nếu bạn nhấn nút Chèn, nằm dưới bản dịch, Excel sẽ thay thế văn bản gốc trong ô để dịch.
Công cụ này rất dễ sử dụng và đặc biệt hữu ích nếu bạn gặp những từ không quen thuộc trong sổ làm việc Excel.
Triển khai trình dịch Yandex trong Excel
Phương pháp được mô tả ở trên không yêu cầu bất kỳ kỹ năng Excel đặc biệt nào, bạn chỉ cần sử dụng nó một lần và mọi thứ sẽ trở nên rõ ràng. Phương pháp được mô tả dưới đây, phức tạp hơn và thậm chí thú vị hơn, sẽ yêu cầu người dùng một số kỹ năng.
Như vậy, như tôi đã viết ở bài viết, Excel 2013 có công cụ nhập dữ liệu vào định dạng XML, sử dụng hàm . Hàm đầu tiên sử dụng API dịch vụ web để tải dữ liệu từ Internet, hàm thứ hai trả về thông tin cụ thể từ XML đã tải xuống. Để triển khai ý tưởng của chúng tôi trong Excel, chúng tôi sẽ sử dụng trình dịch Yandex, có API riêng. Truy cập trang API dịch Yandex, nơi bạn có thể đọc tất cả tài liệu và quy tắc định dạng kết quả dịch, tìm mục Có được miễn phíAPI-chìa khóa và nhấp vào nó (CHÚ Ý! Để lấy chìa khóa, bạn phải có hồ sơ Yandex).
Tiếp theo, bạn cần điền vào biểu mẫu để lấy khóa API, nơi bạn cần cho biết bạn cần khóa từ dịch vụ nào, hãy đọc Điều khoản sử dụng, đánh dấu vào ô đọc và nhấn nút LấyAPI-chìa khóa. Nó trông như thế này.
Khóa này sẽ được sử dụng làm một trong các đối số bắt buộc trong yêu cầu URL được gửi tới các dịch vụ Yandex. Đối số bắt buộc tiếp theo là văn bản cần dịch và đối số thứ ba là hướng dịch, có thể được chỉ định dưới dạng một cặp mã ngôn ngữ (ví dụ: ru-en) hoặc dưới dạng mã ngôn ngữ đích (ví dụ: ru). Bạn có thể đọc thêm về cú pháp URL yêu cầu trên trang web Yandex.
Do đó, bạn sẽ có hai đối số bắt buộc hầu như không thay đổi - khóa và ngôn ngữ dịch, và văn bản dịch sẽ là một đối số có thể thay đổi.
Một điểm quan trọng mà bạn nên chú ý là trước khi gửi yêu cầu tới Yandex, bạn cần thay đổi mã hóa văn bản yêu cầu sang ngôn ngữ mà dịch vụ web có thể hiểu được. Để thực hiện việc này, bạn cần sử dụng hàm ECODE.URL, hàm này trả về một chuỗi được mã hóa dưới dạng URL. Ví dụ, văn bản Samara sẽ trông giống như %D0%A1%D0%B0%D0%BC%D0%B0%D1%80%D0%B0.
Sau khi thêm một chút chú ý vào tệp của chúng tôi, trình dịch của chúng tôi đã chuyển sang dạng hoàn toàn có thể đọc được.
Viết chương trình máy tínhĐầu tiên bạn cần phải biết một số ngôn ngữ lập trình. Thứ hai, bạn phải cài đặt trên máy tính của mình chương trình đặc biệt, nó sẽ biên dịch mã bạn viết. Thứ ba, bạn sẽ cần phát triển và viết ra giấy thuật toán của chương trình để luôn có mục tiêu rõ ràng trước mắt bạn về những gì chúng ta sẽ hướng tới (ít nhất, đây là điều tôi luôn làm khi viết những bài phức tạp hơn). các chương trình).
Ngôn ngữ lập trình Bất cứ điều gì sẽ làm, nhưng khi làm việc trong Excel và viết chương trình cho nó, tôi khuyên bạn nên sử dụng Ngôn ngữ lập trình dành cho Ứng dụng (VBA), vì Excel đã có trình chỉnh sửa đặc biệt tích hợp sẵn để tạo chương trình trong Excel.
Tôi nghĩ bạn và tôi có thể dễ dàng thành thạo ngôn ngữ lập trình, tôi đã thành công và bạn cũng có thể làm được. Hãy tin tôi, lập trình trong Excel dễ hơn nhiều so với trong một số C++ hoặc java. Bởi vì nó được tạo ra cho tất cả mọi người, bất kể bạn là kỹ sư hay kế toán, VBA được tạo ra cho những người sử dụng Excel. Và nếu bạn đã thành thạo Excel thì bước hợp lý tiếp theo là thành thạo VBA.
Tôi sẽ chỉ cho bạn một ví dụ về cách tạo một chương trình trong VBA.
Chúng tôi không phát minh ra bất kỳ thuật toán nào; thay vào đó, chúng tôi sẽ viết cụm từ sau ““Mã của chúng tôi”.
1. Khởi chạy Excel.
2. Thực hiện lệnh blitz (giữ hai phím) “Alt” và “F11”.
3. Tạo mẫu và lưu nó.
4. Sau đó chúng ta viết ra ba dòng, dòng bắt đầu bằng dấu nháy đơn là một nhận xét. Từ "Sub" biểu thị phần đầu của chương trình, "program()" là tên của chương trình, có thể thay đổi, ví dụ: thành "Macro()".
Cụm từ “End Sub” biểu thị chương trình kết thúc tại đây, thay vì dòng “Mã của chúng tôi”, bạn có thể viết thuật toán của chúng tôi sau này.
Lập trình trong Excel không khó như bạn tưởng, bạn cần phải có một thuật toán rõ ràng, tốt nhất là viết ra giấy, chúng ta sẽ sớm tìm hiểu cách phát triển và một số kiến thức cơ bản về VBA (đầu vào và đầu ra dữ liệu vào chương trình, các điều kiện). và vòng lặp), điều này là đủ để bắt đầu.
Xem thêm trong phần này.
Chào buổi chiều các độc giả thân mến.
Hôm nay tôi muốn chỉ ra cách bạn có thể kết nối các khả năng của ngôn ngữ R và đồng phục công sở MS Excel 2010. Dưới đây tôi sẽ nói về cách bạn có thể mở rộng chức năng của phần mềm tích hợp sẵn ngôn ngữ VBA sử dụng các hàm R và bổ trợ RExcel sẽ giúp tôi điều này. Hướng dẫn cài đặt nó có thể dễ dàng tìm thấy trực tuyến hoặc ngoại tuyến. trang mạng.
Báo cáo vấn đề và cài đặt sơ bộ
Để chúng ta có thể sử dụng hàm R từ VBA phải được mở bằng Excel "Trình soạn thảo Visual Basic" (Alt+F11). Sau này, bạn cần thêm một mô-đun vào dự án RExcelVBAlib, bạn có thể thực hiện việc này bằng cách truy cập Công cụ->Tài liệu tham khảo và đánh dấu vào mục mong muốn.
Mô-đun này chứa lớp giao diện, qua đó xảy ra sự tương tác của các thành phần trong gói của chúng tôi.
Để minh họa, tôi đã lấy dữ liệu ban đầu về báo giá đồng đô la từ trang web Finam trong khoảng thời gian từ 16/12/2011 đến 20/03/2014. Ví dụ: dựa trên chúng, sử dụng R, chúng tôi sẽ xây dựng biểu đồ thay đổi giá mở cửa hàng tháng ( "MỞ").
Cơ bản khi làm việc với rinterface từ RExcel
Đầu tiên, hãy viết một mã trong R để thực hiện tác vụ và lưu nó vào một tệp, ví dụ agg_price.R (chúng ta sẽ cần nó sau).Thư viện(sở thú) agg_price_func<- function(x) { y <- zoo(x$OPEN, as.Date(as.character(x$DATE),"%Y%m%d")) new_y <- aggregate(y, as.yearmon, mean) plot(new_y) return (new_y) }
Hãy cùng tìm hiểu xem đoạn mã này làm gì.
Đầu tiên, chúng ta tải thư viện sở thú mà chúng ta sẽ cần làm việc với chuỗi thời gian.
Sau đó, chúng ta tạo một hàm thực hiện những việc sau:
- Chuyển đổi tập dữ liệu của chúng tôi thành một chuỗi thời gian. Các chỉ mục trong đó sẽ là các giá trị cột "Ngày", được chuyển đổi thành ngày. Các giá trị của các cấp độ của hàng này sẽ bằng cột " MỞ".
- Với dòng sau, chúng tôi tổng hợp dữ liệu của mình theo tháng bằng cách sử dụng hàm tổng hợp. Bước này là cần thiết vì dữ liệu nguồn của chúng tôi chứa dữ liệu hàng ngày nhưng chúng tôi cần chuyển sang dữ liệu hàng tháng.
- hiển thị biểu đồ dựa trên giá trị hàng tháng
- Chúng tôi trả về một mảng có giá trị hàng tháng trên cơ sở biểu đồ được xây dựng.
Có một số cách để làm điều này, sẽ được trình bày dưới đây.
Phương pháp 1. Thực hiện từng dòng lệnh chức năng.
Phương pháp này là dễ hiểu nhất và dài nhất về số dòng mã. Mã thủ tục cho nó như sau:Sub call_r_func() RInterface.PutDataframe "open_price", Range("USD!A1:C535") RInterface.RRun "library(zoo)" RInterface.RRun "price<- zoo(open_price$OPEN, as.Date(as.character(open_price$DATE),""%Y%m%d""))" RInterface.RRun "agg_price <- aggregate(price, as.yearmon, mean)" RInterface.RRun "plot(agg_price)" RInterface.InsertCurrentRPlot Range("OPEN_PRICE!A1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True End Sub
Như bạn có thể thấy trong quy trình này, 3 hàm từ giao diện:
- Đặt khung dữ liệu
- Chèn CurrentRPlot
RRun được sử dụng để thực thi một lệnh từ trình thông dịch R. Là một tham số, nó được truyền chuỗi sẽ được thực thi bởi nó.
Cái cuối cùng trong danh sách, InsertCurrentRPlot, xuất đồ họa R sang một trang MS Excel nhất định. Là tham số đầu tiên, nó được cung cấp cho ô mà biểu đồ sẽ được hiển thị. Tùy chọn chiều rộng Và thang đo chiều caođược sử dụng để chia tỷ lệ biểu đồ đầu ra (trong trường hợp của chúng tôi là 50%). đồ thị gần hơn báo hiệu rằng đồ thị được tạo bằng hàmplot() cần phải được đóng lại.
Sau các chức năng được mô tả ở trên, hoạt động của thủ tục call_r_func() không nên nêu bất kỳ câu hỏi nào. Nói tóm lại, quy trình này chỉ đơn giản là thực thi tập lệnh R được mô tả ở trên từng dòng một.
Ưu điểm của phương pháp này là tất cả mã đều tập trung trong macro VBA , có thể thuận tiện cho các nhiệm vụ nhỏ.
Nhược điểm có thể là việc gỡ lỗi mã R bất tiện.
Cách 2. Sử dụng hàm ngoài để hiển thị đồ thị.
Quy trình của phương pháp này như sau:Cuộc gọi phụ_r_impotr_func_without_print() RInterface.RunRFile "D:/agg_price.R" RInterface.RunRCall "agg_price_func", AsSimpleDF(Range("USD!A1:C535")) RInterface.InsertCurrentRPlot Range("OPEN_PRICE!H1"), widthrescale:= 0,5, tỷ lệ chiều cao:=0,5, đồ thị gần hơn:=True End Sub
Có 3 chức năng mới trong mã này:
- RunRFile
- AsSimpleDF
- ChạyRCall
AsSimpleDF chuyển đổi phạm vi được chọn trong tham số thành loại tập dữ liệu ( khung dữ liệu).
Hàm RunRCall gọi một thủ tục và không trả về kết quả của nó ( trở lại (...) làm ngơ). Tên của thủ tục hoặc mã của nó trong R được truyền dưới dạng tham số đầu tiên. Tất cả các tham số tiếp theo đều chuyển các tham số được xác định trong thủ tục (trong trường hợp của chúng tôi chỉ có một).
Từ mô tả ở trên, có thể thấy rõ rằng quy trình của chúng tôi call_r_impotr_func_without_print() thực thi tập tin bên ngoài đầu tiên agg_price.R. Sau đó nó gọi hàm agg_price_func cho một tập hợp dữ liệu từ một phạm vi nhất định. Và cuối cùng nó hiển thị biểu đồ trên trang tính đã cho.
Ưu điểm của phương pháp này là dễ dàng gỡ lỗi mã trong R , bởi vì nó có thể được viết bằng bất kỳ trình soạn thảo hoặc IDE nào.
Trong số các điểm hạn chế, cần lưu ý rằng ở đầu ra, thay vì một tệp, chúng ta sẽ nhận được 2: xls và r.
Cách 3. Sử dụng hàm ngoài với dữ liệu đầu ra và đồ thị.
Mã thủ tục:Cuộc gọi phụ_r_impotr_func_with_print() RInterface.RunRFile "D:/agg_price.R" RInterface.GetRAApply "agg_price_func", Range("OPEN_PRICE!A19"), AsSimpleDF(Range("USD!A1:C535")) RInterface.InsertCurrentRPlot Range(" OPEN_PRICE!D19"), closegraph:=True End Sub
Quy trình này chỉ có một điểm khác biệt nhỏ so với quy trình trước, đó là thay vì
RunRCall được gọi bởi GetRApply.
Sự khác biệt giữa hàm này là nó có thể trả về một kết quả ( trở lại (...) không bị bỏ qua) và không chỉ thực thi một số mã. Mặc dù đối với những người đã quen với định nghĩa về thủ tục và hàm thì sự khác biệt này là rõ ràng.
Ngoài ra, trong GetRAApply, sau tham số có tên hàm, bạn cũng cần chỉ định phạm vi ô nơi các giá trị do hàm tạo ra sẽ được hiển thị.
Ưu và nhược điểm của phương pháp này cũng giống như phương pháp trước.
Phần kết luận
Sau khi thực hiện các quy trình VBA này, bảng kiểm tra của chúng tôi trông như thế này:Trong bài viết tôi đã cố gắng hiển thị các tùy chọn hữu ích nhất để thực thi mã R từ VBA. Ngoài ra, những ưu điểm và nhược điểm của tất cả các phương pháp này đã được mô tả một cách ngắn gọn. Cũng cần phải nói rằng RExcel có thể hoạt động mà không cần VBA.
Cần lưu ý rằng không phải tất cả các chức năng giao diện đều được hiển thị. giao diện, nhưng tài liệu này sẽ giúp bạn có thể thành thạo các chức năng chưa được mô tả với thời gian tối thiểu.
Tệp có quy trình có thể được lấy từ tôi trên GitHub.
Thẻ: Thêm thẻ
Sau khi cài đặt add-on sẽ xuất hiện tính năng mới = DỊCH(CHỮ;NGÔN NGỮ). Hàm chỉ có 2 đối số:
- CHỮ- Văn bản thực tế cần dịch. Độ dài văn bản tối đa là 10.000 ký tự
- NGÔN NGỮ- Hướng dịch. Có thể đặt theo một trong các cách sau:
- Ở dạng một cặp mã ngôn ngữ (“from which” - “to which”), được phân tách bằng dấu gạch nối. Ví dụ: "en-ru" là viết tắt của dịch từ tiếng Anh sang tiếng Nga.
- Ở dạng mã ngôn ngữ cuối cùng ( ví dụ "ru"). Trong trường hợp này, dịch vụ Yandex.Translation sẽ cố gắng tự động xác định ngôn ngữ nguồn.
Sau khi cài đặt tiện ích bổ sung, chức năng này có thể được sử dụng giống như chức năng tích hợp sẵn. Đây là giao diện của cửa sổ tiêu chuẩn để nhập tham số cho hàm TRANSLATION. Sau đây là các ví dụ khác nhau về cách sử dụng chức năng dịch thuật.
ví dụ 1
Như đã đề cập, biến LANGUAGE có thể được đặt theo hai cách. Cách dễ nhất là chỉ định mã ngôn ngữ đích. Trong trường hợp này, chính Yandex sẽ cố gắng xác định ngôn ngữ nguồn.
Ví dụ 2
Một cách phức tạp hơn là chỉ định rõ ràng ngôn ngữ nguồn và ngôn ngữ bạn cần dịch sang.
So sánh kết quả dịch có và không có chỉ dẫn rõ ràng về nguồn dịch. Họ có vẻ giống hệt tôi. Tốc độ của chức năng cũng không thay đổi.
Chức năng dịch đơn giản hóa
Theo quy định, chúng tôi sử dụng hai ngôn ngữ: tiếng Nga và tiếng Anh. Để giúp cuộc sống của tôi và người dùng dễ dàng hơn, tôi đã tạo thêm 4 loại chức năng:
=Bản dịchRu(CHỮ) - Dịch văn bản từ hầu hết mọi ngôn ngữ sang tiếng Nga bằng dịch vụ Yandex.Translation (không cần chỉ định ngôn ngữ nguồn)
Ví dụVí dụ 3
=DịchEn(CHỮ) - Dịch văn bản từ bất kỳ ngôn ngữ nào sang tiếng Anh bằng dịch vụ Yandex.Translation (không cần chỉ định ngôn ngữ nguồn).
Ví dụVí dụ 4
Bạn chỉ cần chỉ định văn bản bạn muốn dịch. Dịch vụ Yandex.Translation sẽ tự động cố gắng xác định ngôn ngữ nguồn của văn bản.
=Bản dịchEnRu(CHỮ) - Dịch văn bản từ tiếng Anh sang tiếng Nga bằng dịch vụ Yandex.Translation.
Ví dụVí dụ 5
Một chức năng đơn giản hóa để dịch từ tiếng Anh sang tiếng Nga. Bạn chỉ cần chỉ định văn bản bạn muốn dịch.
=Bản dịchRuEn(CHỮ) - Dịch văn bản từ tiếng Nga sang tiếng Anh bằng dịch vụ Yandex.Translation.
Ví dụVí dụ 6
Một chức năng đơn giản hóa để dịch từ tiếng Nga sang tiếng Anh. Bạn chỉ cần chỉ định văn bản bạn muốn dịch.
| Ngôn ngữ | Mã số | |
|---|---|---|
| tiếng Albania | vuông | |
| Tiếng Anh | vi | |
| Ả Rập | ar | |
| tiếng Armenia | hy | |
| Tiếng Azerbaijan | az | |
| Tiếng Belorussia | là | |
| tiếng Bungari | bg | |
| tiếng Bosnia | bs | |
| Tiếng Việt | vi | |
| người Hungary | hu | |
| tiếng Hà Lan | nl | |
| người Hy Lạp | el | |
| tiếng Gruzia | ka | |
| người Đan Mạch | da | |
| tiếng Do Thái | Anh ta | |
| tiếng Indonesia | nhận dạng | |
| người Ý | Nó | |
| tiếng Iceland | là | |
| người Tây Ban Nha | es | |
| tiếng Catalan | ca | |
| người Trung Quốc | zh | |
| Hàn Quốc | ko | |
| tiếng Latvia | lv | |
| tiếng Litva | nó | |
| Mã Lai | bệnh đa xơ cứng | |
| cây nho | tấn | |
| Tiếng Macedonia | mk | |
| tiếng Đức | de | |
| người Na Uy | KHÔNG | |
| Đánh bóng | làm ơn | |
| tiếng Bồ Đào Nha | điểm | |
| người Rumani | ro | |
| tiếng Nga | ru | |
| tiếng Serbia | sr | |
| Tiếng Slovak | sk | |
| tiếng Slovenia | sl | |
| tiếng Thái | quần què | |
| tiếng Thổ Nhĩ Kỳ | tr | |
| tiếng Ukraina | Vương quốc Anh | |
| tiếng Phần Lan | fi | |
| người Pháp | bạn | |
| tiếng Croatia | giờ | |
| tiếng Séc | cs | |
| tiếng Thụy Điển | sv | |
| tiếng Estonia | và | |
| tiếng Nhật | à |
Hạn chế chuyển nhượng
Thật không may, Yandex cho phép bạn dịch miễn phí không quá 1 triệu ký tự mỗi ngày. Do sự tăng trưởng của người dùng, khối lượng này bắt đầu đạt được khá thường xuyên. Vào lúc 00:00 giờ Moscow, hạn chế sẽ được dỡ bỏ và chức năng sẽ hoạt động trở lại. Nếu âm lượng vượt quá, hàm trả về giá trị "Không thể dịch văn bản".
