Thực đơn
trang chủTập tin và thư mục

Các giải pháp Excel được tạo sẵn với macro. Làm cách nào để chèn macro tạo sẵn vào sổ làm việc? Macro trong Excel

Kế tiếp ví dụ đơn giản Macro Excel minh họa một số tính năng và kỹ thuật được mô tả trong hướng dẫn Excel VBA.

Macro Excel: Ví dụ 1

Ban đầu thủ tục này phụđược đưa ra làm ví dụ về việc sử dụng nhận xét trong mã VBA. Tuy nhiên, tại đây bạn cũng có thể thấy các biến được khai báo như thế nào, các tham chiếu hoạt động như thế nào Ô Excel, sử dụng vòng lặp Vì, điều hành có điều kiện Nếu như và hiển thị một cửa sổ tin nhắn.

"Thủ tục Sub tìm kiếm một ô chứa chuỗi được chỉ định "trong phạm vi ô A1:A100 của trang tính hiện hoạt Sub Find_String(sFindText As String) Dim i As Integer "Một số nguyên kiểu Integer, được sử dụng trong vòng lặp For Dim iRowNumber As Integer "Một số nguyên kiểu Integer để lưu kết quả iRowNumber = 0 "Xem qua từng ô A1:A100 cho đến khi tìm thấy chuỗi sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then " Nếu tìm thấy kết quả khớp với chuỗi đã chỉ định " hãy lưu số dòng hiện tại và chúng tôi rời đi từ Vòng lặp For iRowNumber = i Thoát để kết thúc nếu tiếp theo i "Chúng tôi thông báo cho người dùng trong cửa sổ bật lên xem hàng được yêu cầu có được tìm thấy hay không" Nếu chuỗi đã chođã tìm thấy, cho biết kết quả khớp được tìm thấy ở ô nào Nếu iRowNumber = 0 Then MsgBox "Không tìm thấy hàng " & sFindText & "" Khác MsgBox "Đã tìm thấy hàng " & sFindText & " trong ô A" & iRowNumber End If End Sub

Macro Excel: Ví dụ 2

Thủ tục tiếp theo phụ– ví dụ về việc sử dụng vòng lặp Làm trong khi. Bạn cũng có thể xem cách khai báo các biến, làm việc với các tham chiếu ô Excel và sử dụng câu lệnh có điều kiện. Nếu như.

"Thủ tục Sub xuất ra các số Fibonacci không vượt quá 1000 Sub Fibonacci() Dim i As Integer "Bộ đếm để chỉ vị trí của một phần tử trong chuỗi Dim iFib As Integer "Lưu trữ giá trị hiện tại của chuỗi Dim iFib_Next As Integer "Lưu trữ giá trị hiện tại của chuỗi Dim iFib_Next As Integer giá trị tiếp theo của chuỗi Dim iStep As Integer "Lưu trữ kích thước của số gia tiếp theo" Khởi tạo các biến i và iFib_Next i = 1 iFib_Next = 0 "Vòng lặp Do While sẽ được thực thi cho đến khi giá trị của" số Fibonacci hiện tại vượt quá 1000 Do While iFib_Tiếp theo< 1000 If i = 1 Then "Một trường hợp đặc biệtđối với phần tử đầu tiên của chuỗi iStep = 1 iFib = 0 Khác "Lưu kích thước của bước tăng tiếp theo trước khi ghi đè" giá trị hiện tại của chuỗi iStep = iFib iFib = iFib_Next End Nếu "Xuất số Fibonacci hiện tại vào cột A của bảng tính hoạt động "trong hàng có chỉ mục i Cells(i, 1).Value = iFib "Tính số Fibonacci tiếp theo và tăng chỉ số vị trí của phần tử lên 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macro Excel: Ví dụ 3

Thủ tục này phụ quét các ô của một cột MỘT trang tính đang hoạt động cho đến khi gặp một ô trống. Các giá trị được ghi vào một mảng. Macro Excel đơn giản này cho thấy cách làm việc với mảng động và cũng sử dụng một vòng lặp Làm cho đến khi. TRONG trong ví dụ này chúng ta sẽ không thực hiện bất kỳ hành động nào với mảng, mặc dù trong thực tế lập trình thực tế, sau khi dữ liệu được ghi vào mảng, những hành động như vậy thường được thực hiện trên chúng.

"Thủ tục Sub lưu trữ các giá trị ô của cột A của trang tính đang hoạt động trong mảng Sub GetCellValues() Dim iRow As Integer "Lưu trữ số của hàng hiện tại Dim dCellValues() As Double "Một mảng để lưu trữ các giá trị ô ​​iRow = 1 ReDim dCellValues(1 To 10) "Thực hiện Vòng lặp cho đến khi lặp tuần tự qua các ô của cột A của trang tính đang hoạt động" và trích xuất các giá trị của chúng thành một mảng cho đến khi gặp một ô trống Thực hiện cho đến khi IsEmpty(Cells( iRow, 1)) "Kiểm tra xem mảng dCellValues ​​​​có đủ kích thước không" Nếu không, hãy tăng kích thước mảng lên 10 bằng cách sử dụng ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macro Excel: Ví dụ 4

Trong ví dụ này, thủ tục phụđọc giá trị từ một cột MỘT bảng tính Trang 2 và làm điều đó với họ các phép tính toán học. Kết quả được nhập vào các ô cột MỘT trên bảng tính đang hoạt động. Macro này thể hiện việc sử dụng các đối tượng Excel. Đặc biệt, việc kháng cáo được thực hiện theo thủ tục phụđến đối tượng Cột và hiển thị cách đối tượng này được truy cập thông qua đối tượng Bảng tính. Nó cũng cho thấy rằng khi truy cập vào một ô hoặc một phạm vi ô trên trang tính đang hoạt động, không cần thiết phải chỉ định tên của trang tính này khi viết liên kết.

"Thủ tục Sub, sử dụng vòng lặp, đọc các giá trị trong cột A của bảng tính Sheet2," thực hiện các phép tính số học với từng giá trị và ghi kết quả vào "cột A của bảng tính đang hoạt động (Trang tính 1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Gán cho biến Col cột A của bảng tính Sheet 2 Đặt Col = Sheets("Sheet2").Columns("A") i = 1 "Sử dụng vòng lặp, chúng ta đọc các giá trị ​​của các ô của cột Col cho đến khi "cho đến khi gặp ô trống Thực hiện Until IsEmpty(Col.Cells(i)) "Thực hiện các phép tính số học trên giá trị của ô hiện tại dVal = Col.Cells(i).Value * 3 - 1" Lệnh tiếp theo ghi kết quả vào cột A của bảng tính đang hoạt động "Không cần chỉ ra tên trang tính trong liên kết, vì đây là trang tính đang hoạt động. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Macro Excel: Ví dụ 5

Macro này hiển thị một ví dụ về mã VBA theo dõi sự kiện Excel. Sự kiện mà macro được đính kèm xảy ra mỗi khi một ô hoặc phạm vi ô được chọn trên trang tính. Trong trường hợp của chúng tôi, khi chọn một ô B1, trên màn hình xuất hiện cửa sổ thông báo.

"Mã này hiển thị hộp thông báo nếu ô B1 được chọn trên bảng tính hiện tại. Bảng tính phụ riêng tư_SelectionChange(ByVal Target As Range) "Kiểm tra xem ô B1 có được chọn hay không Nếu Target.Count = 1 Và Target.Row = 1 Và Target.Column = 2 Sau đó "Nếu ô B1 được chọn, hãy thực hiện hành động được yêu cầu MsgBox "Bạn đã chọn ô B1" End If End Sub

Macro Excel: Ví dụ 6

Thủ tục này minh họa việc sử dụng các toán tử Có lỗi Và Bản tóm tắtđể xử lý lỗi. Mã này cũng hiển thị một ví dụ về mở và đọc dữ liệu từ một tệp.

"Thủ tục Sub gán các đối số Val1 và Val2 các giá trị của ô A1 và B1" từ sổ làm việc Data.xlsx nằm trong thư mục C:\Documents and Setting Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Sổ làm việc có lỗi GoTo ErrorHandling " Đang mở sách bài tập với dữ liệu Đặt DataWorkbook = Workbooks.Open("C:\Documents and Setting\Data") "Gán các biến giá trị Val1 và Val2 ​​từ sổ làm việc đã cho Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets( "Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Nếu không tìm thấy tệp, người dùng sẽ được nhắc đặt tệp họ đang tìm" vào thư mục mong muốn và sau đó tiếp tục thực thi macro MsgBox "Không tìm thấy tệp Data.xlsx!" & _ "Vui lòng thêm sổ làm việc vào thư mục C:\Documents and Cài đặt và nhấp vào OK" Tiếp tục End Sub

Ngay cả khi bạn không biết gì về VBA và macro, bạn sẽ có cảm hứng để tìm hiểu thêm sau khi đọc một số thủ thuật và thủ thuật trong phần này. Nếu bạn mới bắt đầu làm việc với macro, bạn có thể tìm thấy thông tin hữu ích tại đây.

. .

Trong các bài viết trước, bạn đã biết Excel có thể quản lý người khác như thế nào Ứng dụng của Microsoft chẳng hạn như Word và Outlook. Nhưng đồng thời, các ứng dụng khác cũng có thể được sử dụng để quản lý Excel. Ví dụ: bạn đã viết macro cho Word hoặc Access để tạo bảng Excel, điền dữ liệu vào bảng rồi lưu. Excel không nhất thiết phải […]

. .

Bạn có thể sử dụng công nghệ này để quản lý của Microsoft Outlook và gửi email trực tiếp từ bảng tính của bạn hoặc sao chép các mục trong sổ ghi chép của bạn. Tất nhiên, các tính năng của Excel là như vậy e-mail bảng sẽ được gửi, nhưng phương pháp này chỉ cho phép bạn gửi một phần của bảng. Để mã này hoạt động, bạn cần phải có […]

. .

Phương pháp này có thể rất hữu ích, ví dụ nếu bạn có tài liệu chuẩn với các bảng chứa đầy macro dữ liệu từ các bảng Excel. Bạn có thể chạy macro và dữ liệu sẽ được chuyển sang các bảng trong tài liệu Word. Gần đây tôi có nhiệm vụ viết chương trình điền báo cáo về SLA (Thỏa thuận cấp độ dịch vụ). Báo cáo […]

. .

Macro thường được sử dụng để tự động hóa hoạt động của ứng dụng. Bất kỳ macro nào cũng là một chuỗi hành động được ghi lại dưới một tên cụ thể. Nếu khi làm việc với Microsoft Excel cần phải thực hiện cùng một chuỗi thao tác nhiều lần (ví dụ: định dạng phức tạp của ô hiện tại hoặc thêm dòng mới bằng cách điền công thức vào một số ô của nó), sau đó bạn có thể viết ra những hành động này và […]

Excel có khả năng mạnh mẽ nhưng hiếm khi được sử dụng để tạo các chuỗi hành động tự động bằng cách sử dụng macro. Macro là giải pháp lý tưởng nếu bạn đang xử lý cùng một loại tác vụ được lặp lại nhiều lần. Ví dụ: xử lý dữ liệu hoặc định dạng tài liệu bằng mẫu được chuẩn hóa. Đồng thời, bạn không cần kiến ​​thức về ngôn ngữ lập trình.

Bạn đã tò mò về macro là gì và nó hoạt động như thế nào chưa? Sau đó, hãy mạnh dạn tiếp tục - sau đó chúng tôi sẽ cùng bạn thực hiện toàn bộ quá trình tạo macro từng bước.

Macro là gì?

Macro trong Microsoft Office (vâng, chức năng này hoạt động giống nhau trong nhiều ứng dụng gói Microsoft Văn phòng) là Mã chương trình bằng ngôn ngữ lập trình (VBA), được lưu bên trong tài liệu. Để làm rõ hơn, một tài liệu Microsoft Office có thể được so sánh với trang HTML, thì macro là một dạng tương tự của Javascript. Những gì Javascript có thể làm với dữ liệu HTML trên một trang web rất giống với những gì macro có thể làm với dữ liệu trong tài liệu Microsoft Văn phòng.

Macro có thể thực hiện hầu hết mọi hành động mà bạn có thể muốn trong tài liệu. Dưới đây là một số trong số đó (một phần rất nhỏ):

Tạo Macro - Ví dụ thực tế

Ví dụ: hãy tận dụng tối đa tập tin thông thường CSV. Cái này bảng đơn giản 10x20, chứa đầy các số từ 0 đến 100 với tiêu đề cho cột và hàng. Nhiệm vụ của chúng tôi là biến tập dữ liệu này thành một bảng được định dạng có thể trình bày được và tạo tổng số trong mỗi hàng.

Như đã đề cập, macro là mã được viết bằng ngôn ngữ lập trình VBA. Nhưng trong Excel, bạn có thể tạo chương trình mà không cần viết dòng mã nào, đó là điều chúng ta sẽ làm ngay bây giờ.

Để tạo macro, hãy mở Xem(Xem) > Macro(Macro) > Ghi macro(Ghi macro...)

Đặt tên cho macro của bạn (không có dấu cách) và nhấp vào ĐƯỢC RỒI.

Kể từ thời điểm này, TẤT CẢ hành động của bạn với tài liệu đều được ghi lại: thay đổi ô, cuộn qua bảng, thậm chí thay đổi kích thước cửa sổ.

Excel báo hiệu rằng chế độ ghi macro được bật ở hai nơi. Trước hết, trên menu Macro(Macro) - thay vì một dòng Ghi macro dòng (Ghi macro...) xuất hiện Dừng ghi(Dừng ghi âm).

Thứ hai, ở góc dưới bên trái cửa sổ Excel. Biểu tượng Dừng lại(hình vuông nhỏ) cho biết chế độ ghi macro đã được bật. Nhấp vào nó sẽ ngừng ghi. Ngược lại, khi chưa bật chế độ ghi, tại đây sẽ có biểu tượng kích hoạt ghi macro. Nhấp vào nó sẽ cho kết quả tương tự như cho phép ghi qua menu.

Bây giờ chế độ ghi macro đã được bật, hãy bắt tay vào nhiệm vụ của chúng ta. Trước hết, hãy thêm tiêu đề cho dữ liệu tóm tắt.

  • =SUM(B2:K2) hoặc =SUM(B2:K2)
  • =TRUNG BÌNH(B2:K2) hoặc =TRUNG BÌNH(B2:K2)
  • =MIN(B2:K2) hoặc =MIN(B2:K2)
  • =MAX(B2:K2) hoặc =MAX(B2:K2)
  • = TRUNG BÌNH(B2:K2) hoặc = TRUNG BÌNH(B2:K2)

Bây giờ, hãy chọn các ô có công thức và sao chép chúng vào tất cả các hàng trong bảng của chúng tôi bằng cách kéo tay cầm tự động điền.

Sau khi hoàn thành bước này, tổng số tương ứng sẽ xuất hiện ở mỗi hàng.

Tương ứng:

  • =SUM(L2:L21) hoặc =SUM(L2:L21)
  • =TRUNG BÌNH(B2:K21) hoặc =TRUNG BÌNH(B2:K21)– để tính giá trị này cần lấy chính xác dữ liệu bảng gốc. Nếu bạn lấy giá trị trung bình của các giá trị trung bình cho từng hàng riêng lẻ thì kết quả sẽ khác.
  • =MIN(N2:N21) hoặc =MIN(N2:N21)
  • =MAX(O2:O21) hoặc =MAX(O2:O21)
  • = TRUNG BÌNH(B2:K21) hoặc = TRUNG BÌNH(B2:K21)– chúng tôi tính toán bằng cách sử dụng dữ liệu bảng gốc vì lý do đã nêu ở trên.

Bây giờ chúng ta đã hoàn tất việc tính toán, hãy chuyển sang phần định dạng. Đầu tiên chúng ta hãy thiết lập định dạng hiển thị dữ liệu giống nhau cho tất cả các ô. Chọn tất cả các ô trên trang tính bằng phím tắt Ctrl+A, hoặc bấm vào biểu tượng Chọn tất cả, nằm ở giao điểm của tiêu đề hàng và cột. Sau đó nhấn vào Kiểu dấu phẩy Tab (Định dạng được phân cách) Trang chủ(Trang chủ).

  • Kiểu chữ đậm.
  • Căn chỉnh trung tâm.
  • Đổ đầy màu sắc.

Và cuối cùng, hãy thiết lập định dạng của tổng giá trị.

Cuối cùng nó sẽ trông như thế này:

Nếu bạn hài lòng với mọi thứ, hãy dừng ghi macro.

Chúc mừng! Bạn vừa tự mình ghi lại macro Excel đầu tiên của mình.

Để sử dụng cửa sổ bật lên vĩ mô này, chúng ta cần lưu tài liệu excelở định dạng hỗ trợ macro. Trước tiên, chúng ta cần xóa tất cả dữ liệu khỏi bảng mà chúng ta đã tạo, tức là. biến nó thành một mẫu trống. Thực tế là trong tương lai, khi làm việc với mẫu này, chúng tôi sẽ nhập dữ liệu mới nhất và phù hợp nhất vào đó.

Để xóa toàn bộ ô dữ liệu nhấn chuột phải vào biểu tượng Chọn tất cả, nằm ở giao điểm của tiêu đề hàng và cột và từ danh mục chọn mục Xóa bỏ(Xóa bỏ).

Bây giờ trang tính của chúng tôi đã bị xóa hoàn toàn tất cả dữ liệu, trong khi macro vẫn được ghi lại. Chúng ta cần lưu cuốn sách dưới dạng Mẫu Excel kích hoạt macro, có phần mở rộng XLTM.

Tâm điểm! Nếu bạn lưu tệp có phần mở rộng XLTX, thì macro sẽ không hoạt động trong đó. Nhân tiện, bạn có thể lưu sổ làm việc dưới dạng mẫu Excel 97-2003, có định dạng XLT, nó cũng hỗ trợ macro.

Sau khi lưu mẫu, bạn có thể đóng Excel một cách an toàn.

Chạy Macro trong Excel

Trước khi tiết lộ tất cả các khả năng của macro bạn đã tạo, tôi nghĩ bạn nên chú ý đến một số điểm quan trọng về macro nói chung:

  • Macro có thể có hại.
  • Đọc lại đoạn trước.

Mã VBA rất mạnh mẽ. Đặc biệt, nó có thể thực hiện các thao tác trên các tệp bên ngoài tài liệu hiện tại. Ví dụ: macro có thể xóa hoặc thay đổi bất kỳ tệp nào trong một thư mục Tai liệu của tôi. Vì lý do này, chỉ chạy và cho phép macro từ các nguồn bạn tin cậy.

Để chạy macro định dạng dữ liệu của chúng tôi, hãy mở tệp mẫu mà chúng tôi đã tạo trong phần đầu tiên của hướng dẫn này. Nếu bạn có cài đặt tiêu chuẩn security, thì khi bạn mở một tệp, một cảnh báo sẽ xuất hiện ở đầu bảng cho biết rằng macro đang chạy bị tắt và một nút để kích hoạt việc thực thi chúng. Vì chúng tôi tự tạo mẫu và tin tưởng vào chính mình nên chúng tôi nhấn nút Bật nội dung(Gồm nội dung).

Bước tiếp theo là nhập tập dữ liệu cập nhật mới nhất từ ​​tệp CSV(chúng tôi đã tạo macro của mình dựa trên một tệp như vậy).

Khi bạn nhập dữ liệu từ tệp CSV, Excel có thể yêu cầu bạn định cấu hình một số cài đặt để truyền đúng dữ liệu vào một bảng.

Khi quá trình nhập hoàn tất, hãy chuyển đến menu Macro tab (Macro) Xem(Xem) và chọn lệnh Xem macro(Macro).

Trong hộp thoại mở ra, chúng ta sẽ thấy một dòng có tên macro của chúng ta Định dạngDữ liệu. Chọn nó và nhấp vào Chạy(Chạy).

Khi macro bắt đầu chạy, bạn sẽ thấy con trỏ bảng nhảy từ ô này sang ô khác. Sau vài giây, các thao tác tương tự sẽ được thực hiện với dữ liệu như khi ghi macro. Khi mọi thứ đã sẵn sàng, bảng sẽ trông giống như bảng gốc mà chúng ta đã định dạng thủ công, chỉ khác dữ liệu trong các ô.

Chúng ta hãy xem xét kỹ hơn: Macro hoạt động như thế nào?

Như đã được đề cập nhiều lần, macro là mã chương trình trong ngôn ngữ lập trình Ngôn ngữ lập trình cho các ứng dụng(VBA). Khi bạn bật chế độ ghi macro, Excel thực sự ghi lại mọi hành động bạn thực hiện dưới dạng hướng dẫn trên ngôn ngữ VBA. Nói một cách đơn giản, Excel viết mã cho bạn.

Để xem mã chương trình này, bạn cần vào menu Macro tab (Macro) Xem(Xem) bấm vào Xem macro(Macro) và trong hộp thoại mở ra, hãy nhấp vào Biên tập(Thay đổi).

Một cửa sổ sẽ mở ra Visual Basic cho ứng dụng, trong đó chúng ta sẽ thấy mã chương trình của macro mà chúng ta đã ghi. Có, bạn đã hiểu chính xác, tại đây bạn có thể thay đổi mã này và thậm chí tạo macro mới. Các hành động mà chúng ta đã thực hiện với bảng trong bài học này có thể được viết ra bằng cách sử dụng ghi âm tự động macro trong Excel. Nhưng các macro phức tạp hơn, với trình tự hành động và logic được tinh chỉnh, yêu cầu lập trình thủ công.

Hãy thêm một bước nữa vào nhiệm vụ của chúng ta...

Hãy tưởng tượng rằng tập tin gốc với dữ liệu dữ liệu.csvđược tạo tự động bởi một số quy trình và luôn được lưu trên đĩa ở cùng một nơi. Ví dụ, C:\Dữ liệu\data.csv– đường dẫn tới file chứa dữ liệu đã cập nhật. Quá trình mở tệp này và nhập dữ liệu từ nó cũng có thể được ghi lại trong macro:

  1. Mở tệp mẫu mà chúng tôi đã lưu macro - Định dạngDữ liệu.
  2. Tạo một macro mới có tên Tải dữ liệu.
  3. Trong khi ghi macro Tải dữ liệu nhập dữ liệu từ tập tin dữ liệu.csv- như chúng ta đã làm ở phần trước của bài học.
  4. Khi quá trình nhập hoàn tất, hãy dừng ghi macro.
  5. Xóa tất cả dữ liệu khỏi ô.
  6. Lưu tệp dưới dạng mẫu Excel có hỗ trợ macro (phần mở rộng XLTM).

Do đó, bằng cách chạy mẫu này, bạn có quyền truy cập vào hai macro - một macro tải dữ liệu, macro còn lại định dạng dữ liệu.

Nếu bạn muốn bắt đầu lập trình, bạn có thể kết hợp hành động của hai macro này thành một - chỉ bằng cách sao chép mã từ Tải dữ liệuđến đầu mã Định dạngDữ liệu.

Hướng dẫn sử dụng chứa macro về các chủ đề sau:
Chạy macro để tìm kiếm ô.
Chạy macro khi mở sổ làm việc.
Chạy macro khi bạn nhập “2” vào ô.
Chạy macro khi bạn nhấn Enter.
Thêm tab “Phần bổ trợ” (Định dạng ô) của riêng bạn vào bảng điều khiển.
Làm việc với các tệp (tức là trao đổi dữ liệu với TXT, RTF, XLS, v.v.).
Kiểm tra sự hiện diện của tệp tại đường dẫn đã chỉ định.
Tìm kiếm tập tin cần thiết.
Tự động xóa tập tin.
Văn bản miễn phí trên thanh trạng thái.
Khôi phục thanh trạng thái.
Dòng leo trên thanh trạng thái.

Nhanh chóng thay đổi tiêu đề cửa sổ.
Thay đổi tiêu đề cửa sổ (ẩn tên file).
Trở lại tiêu đề ban đầu.
Những gì đang mở vào lúc này.
Làm việc với các tập tin văn bản.
Viết và đọc tập tin văn bản.
Xử lý nhiều tập tin văn bản.
Xác định cuối dòng trong tệp văn bản.
Sao chép từ tệp văn bản sang Excel.

Sao chép nội dung vào một tập tin văn bản.
Xuất dữ liệu sang txt.
Xuất dữ liệu sang html.
Nhập dữ liệu yêu cầu nhiều hơn 256 cột.
Sự sáng tạo bản sao lưu các tập tin có giá trị.
Đếm số lần một file được mở.
Xuất đường dẫn tệp đến ô hiện hoạt.
Sao chép nội dung tập tin RTF trong Excel.
Sao chép dữ liệu từ một sổ làm việc đã đóng.
Trích xuất dữ liệu từ một tập tin đã đóng.
Tìm kiếm một từ trong tập tin.
Tạo một tệp văn bản và nhập văn bản vào tệp.
Tạo một tệp văn bản và nhập văn bản (kết thúc phát hiện tệp).
Sự sáng tạo Tài liệu Word dựa trên bảng excel.
Các lệnh tạo và xóa thư mục.
Lấy thư mục hiện tại.
Thay đổi thư mục.

Xem tất cả các tập tin trong một thư mục.
Xem tất cả các tập tin trong một thư mục.
Đang làm việc Khu vực Microsoft Excel.
Sách bài tập.
Số lượng tên sổ làm việc.
Bảo vệ sổ làm việc.
Cấm in sách.
Mở một cuốn sách (hoặc tập tin văn bản).
Mở sách và thêm văn bản vào ô A1.
Có bao nhiêu cuốn sách được mở?
Đóng tất cả các cuốn sách.
Chỉ đóng một sổ làm việc khi một điều kiện được đáp ứng.
Lưu sổ làm việc với tên đại diện cho ngày hiện tại.
Sổ làm việc có được lưu không?
Tạo một sổ làm việc với một trang tính.
Tạo một cuốn sách.
Loại bỏ những tên không cần thiết.
Sao chép nhanh một sổ làm việc.
Sắp xếp tờ.
Tìm giá trị lớn nhất trên tất cả các trang tính của sổ làm việc.
Bảng tính.
Kiểm tra xem bảng tính có được bảo vệ hay không
Danh sách các tờ được sắp xếp.
Tạo nên lá mới.
Tạo một trang tính mới.
Loại bỏ các tờ dựa trên ngày.
Sao chép một tờ trong một cuốn sách.
Sao chép một trang tính vào sách mới(tạo).
Di chuyển một tờ trong một cuốn sách.
Di chuyển nhiều trang tính sang một sổ làm việc mới.
Thay thế một tập tin hiện có.
"Lật" cuốn sách.
Chèn đầu trang và chân trang với tên của sổ làm việc, trang tính và ngày hiện tại.
Trang tính có tồn tại không?
Trang tính có tồn tại không?
Hiển thị số lượng trang tính trong sổ làm việc đang hoạt động.
Hiển thị số trang tính trong sổ làm việc đang hoạt động dưới dạng siêu liên kết.
Hiển thị lần lượt tên các sheet đang hoạt động.
Hiển thị tên và số trang của cuốn sách hiện tại.
Làm cho tờ giấy trở nên vô hình.
Có bao nhiêu trang trên tất cả các tờ?
Ô và phạm vi (cột và hàng).
Sao chép hàng sang trang tính khác.
Sao chép các cột sang một trang tính khác.
Đếm số ô chứa các giá trị được chỉ định.
Đếm số ô trong một phạm vi chứa các giá trị được chỉ định.
Đếm số lượng ô hiển thị trong một phạm vi.
Xác định số lượng ô trong một phạm vi và tổng giá trị của chúng.
Đếm số lượng tế bào.
Tự động tính toán lại dữ liệu bảng khi giá trị của nó thay đổi.
Nhập dữ liệu vào ô.
Nhập dữ liệu bằng công thức.
Đầu vào dữ liệu nối tiếp.
Nhập dữ liệu văn bản vào ô.
Hiển thị tên sách, sheet và số sheet trong ô.
Loại bỏ các dòng trống.
Loại bỏ các dòng trống.
Loại bỏ các dòng trống.
Xóa một hàng theo điều kiện.
Gỡ bỏ hàng ẩn.
Xóa các hàng bị ẩn hoặc có chiều cao bằng 0 đã sử dụng.
Loại bỏ các bản sao bằng cách sử dụng mặt nạ.

Chọn một phạm vi phía trên ô hiện tại.
Chọn một ô và đặt một số ở đó.
Làm nổi bật các giá trị âm.
Chọn một phạm vi và sử dụng địa chỉ tuyệt đối.

Chọn các ô theo khoảng thời gian.
Lựa chọn nhiều phạm vi.
Di chuyển qua các tế bào.
Tìm ô trống gần nhất trong một cột.
Tìm giá trị lớn nhất.
Tìm kiếm và thay thế theo mẫu.
Tìm kiếm một giá trị và hiển thị kết quả trong một cửa sổ riêng.

Tìm kiếm với việc đánh dấu dữ liệu tìm thấy.
Tìm kiếm theo điều kiện trong một phạm vi.
Tìm ô không trống cuối cùng trong một phạm vi.
Tìm ô không trống cuối cùng trong một cột.
Tìm ô không trống cuối cùng trong một hàng.
Tìm kiếm ô có màu xanh trong phạm vi.
Tìm giá trị âm trong một phạm vi và đánh dấu nó bằng màu xanh lam.
Tìm sự hiện diện của một giá trị trong một cột.
Tìm kết quả phù hợp trong một phạm vi.
Tìm kiếm một ô trong một phạm vi.
Tìm kiếm một ô trong một phạm vi.
Tìm giá trị gần đúng trong một phạm vi.
Tìm phần đầu và phần cuối của một phạm vi chứa dữ liệu.
Tìm phần đầu của dữ liệu.
Tự động thay thế các giá trị
Điền nhanh một phạm vi (mảng).
Điền thông qua khoảng (mảng).
Điền vào phạm vi được chỉ định (mảng).
Điền vào phạm vi (mảng).
Tính tổng các giá trị đầu tiên của phạm vi.
Vị trí trong một tế bào của một chiếc đồng hồ điện tử.
"Báo thức".
Thiết kế mặt trên và giới hạn dưới phạm vi.
Địa chỉ ô đang hoạt động.
Tọa độ của ô đang hoạt động.
Công thức tế bào hoạt động
Lấy công thức từ một ô.
Kiểu dữ liệu di động.
Xuất ra địa chỉ của phần cuối của phạm vi.
Lấy thông tin về phạm vi đã chọn.
Lấy từ có ký tự thứ 13 trong ô.
Tạo một danh sách có thể chỉnh sửa (bảng).
Kiểm tra giá trị trống.
Giao điểm ô.
Nhân phạm vi đã chọn với.
Đồng thời nhân tất cả dữ liệu trong một phạm vi.
Chia phạm vi thành.
Bình phương từng ô trong phạm vi.
Chỉ tóm tắt dữ liệu từ các ô hiển thị.
Tổng các ô có giá trị số.
Khi tính tổng, con trỏ nằm trong phạm vi.

Lãi suất tích lũy tùy theo số tiền.
Lãi suất tích lũy tùy theo số tiền.
Ví dụ tóm tắt về tính toán hoa hồng.
Chuyển động dọc theo phạm vi.
Bù trừ từ ô đã chọn.
Lặp lại qua các ô dọc theo cột.
Tạo một phạm vi điền.
Chọn một tham số ô.
Phân chia phạm vi.
Hợp nhất dữ liệu phạm vi.
Hợp nhất dữ liệu phạm vi.
Tìm ra cột hoặc hàng tối đa.
Giới hạn các giá trị phạm vi có thể.
Kiểm tra tốc độ của phạm vi đọc và viết.
Mở MsgBox khi một ô được chọn.
Ẩn một hàng.
Ẩn nhiều hàng.
Ẩn một cột.
Ẩn nhiều cột
Ẩn một hàng theo tên ô.
Ẩn nhiều hàng theo địa chỉ ô.
Ẩn một cột theo tên ô.
Ẩn nhiều cột theo địa chỉ ô.
Tế bào nhấp nháy.
Làm việc với ghi chú.
Hiển thị tất cả các ghi chú trong bảng tính.
Chức năng trích xuất bình luận.
Danh sách ghi chú cho các tờ được bảo vệ.

Danh sách ghi chú trong một danh sách riêng biệt.
Danh sách ghi chú trong một danh sách riêng biệt.

Đếm số nốt nhạc.
Đếm ghi chú.
Chọn các ô có nhận xét.
Hiển thị tất cả các ghi chú.
Thay đổi màu của ghi chú.
Thêm ghi chú.
Thêm ghi chú vào một phạm vi dựa trên điều kiện.
Di chuyển nhận xét đến một ô và quay lại.

Chuyển giá trị từ một ô sang nhận xét.
Các tab tùy chỉnh trên ribbon.
Bổ sung thanh công cụ.
Thêm một nút vào thanh công cụ.
Bảng điều khiển một nút.
Bảng điều khiển có hai nút.
Tạo một bảng điều khiển ở bên phải.

Sự sáng tạo thực đơn tùy chỉnh(lựa chọn 1).
Tạo menu tùy chỉnh (tùy chọn 2).
Tạo menu tùy chỉnh (tùy chọn 3).
Tạo menu tùy chỉnh (tùy chọn 4).
Tạo menu tùy chỉnh (tùy chọn 5).
Tạo menu tùy chỉnh (tùy chọn 6).
Tạo danh sách các mục menu chính của Excel.
Tạo danh sách các mục menu ngữ cảnh.
Hiển thị thanh công cụ theo một điều kiện nhất định.
Ẩn và hiển thị thanh công cụ.
Tạo chú giải công cụ cho các nút của tôi.
Tạo một menu dựa trên dữ liệu bảng tính.
Tạo một menu ngữ cảnh.
Chặn menu ngữ cảnh.
Thêm lệnh vào menu Công cụ.
Thêm lệnh vào menu Xem.
Tạo bảng danh sách.
Một phim hoạt hình có sự tham gia của một trợ lý.
Thêm văn bản, tiêu đề, nút và biểu tượng vào trợ lý.
Tùy chọn trợ lý mới.
Sử dụng trợ lý để chọn màu tô.
CỬA SỔ ĐỐI THOẠI.
Chức năng INPUTBOX (thông qua giá trị đầu vào).
Xem trước cuộc gọi.
Thiết lập mục nhập dữ liệu trong hộp thoại.

Mở hộp thoại (Mở tệp).
Mở hộp thoại (In).
Các hộp thoại khác.
Gọi trình duyệt từ Excel.
Hộp thoại nhập dữ liệu.
Hộp thoại cài đặt phông chữ.
Giá trị mặc định.
Định dạng văn bản. Những cái bàn. BIÊN GIỚI VÀ ĐIỀN.
Hiển thị danh sách các phông chữ có sẵn.
Chọn tất cả các số từ văn bản.
Chỉ viết hoa ở đầu văn bản.
Đếm số lần lặp lại của văn bản tìm kiếm.
Chọn một phần tử tùy ý từ văn bản.
Hiển thị văn bản ngược.
Văn bản tiếng Anh được viết hoa.
Khởi chạy bảng ký hiệu từ Excel.
thông tin về người dùng, máy tính, máy in, v.v.
Nhận tên người dùng.
Giám sát đầu ra độ phân giải.
Nhận thông tin về máy in bạn đang sử dụng.
Xem thông tin về ổ đĩa máy tính của bạn.
NGƯỜI DÙNG.
SƠ ĐỒ.
Xây dựng biểu đồ bằng macro.
Lưu sơ đồ vào một tập tin riêng biệt.
Xây dựng và xóa biểu đồ chỉ bằng một nút bấm.
Hiển thị danh sách sơ đồ trong một cửa sổ riêng.
Áp dụng bảng màu ngẫu nhiên.
Hiệu ứng trong suốt của biểu đồ.
Xây dựng biểu đồ dựa trên dữ liệu từ nhiều bảng tính.
Tạo chú thích cho dữ liệu biểu đồ.
CHƯƠNG TRÌNH KHÁC NHAU.
Chương trình soạn câu đố ô chữ.
Tạo bìa DVD.
Trò chơi "Bãi mìn".
Trò chơi "Đoán con vật".
Tính toán dựa trên các ô có màu nhất định.
CHỨC NĂNG VÀ MACRO KHÁC.
Gọi các phím chức năng.
Tính toán trung bình số học.
Biến số thành “tiền”.
Tìm kiếm thứ Hai gần nhất.
Đếm số năm trọn vẹn.
Tính toán bình quân gia quyền.
Chuyển đổi số tháng thành tên của nó.
Sử dụng liên kết tương đối
Chuyển đổi bảng Excel sang định dạng HTML.
Trình tạo số ngẫu nhiên.
Số ngẫu nhiên - dựa trên phạm vi.
Áp dụng một hàm mà không cần nhập nó vào ô.
Đếm các đối tượng được đặt tên
Bật bộ lọc tự động bằng macro.
Tạo một đường leo.
Tạo một hình ảnh đang chạy.
Xoay tự động định hình.
Gọi bảng màu.
Tạo một máy tính.
Sự suy giảm của họ, tên và bảo trợ.
NGÀY VÀ GIỜ.
Ngày và giờ đầu ra.
Ngày và giờ đầu ra.
Lấy ngày hệ thống.
Truy xuất ngày và giờ.
Chức năng DateFull. Phiên bản MS Office được sử dụng không được chỉ định.

Nếu bạn không mạnh về lập trình nhưng đồng thời cũng cần phải thực hiện một sốmột nhiệm vụ vượt xa tính năng tiêu chuẩn và các lệnh MS Excel, bạn có thể tìm kiếm giải pháp trên Internet. Giải pháp rất có thể sẽ ở dạng mã VBA mà bạn cần sao chép và dán vào sổ làm việc của mình, sau đó bằng cách nào đó làm cho mã này phù hợp với bạn, trong bài viết này tôi sẽ cho bạn biết cách thực hiện điều này.

Chúng ta hãy xem hai ví dụ:

1. Tìm và sử dụng lệnh

Ví dụ: chúng ta cần một lệnh chèn số “1” vào các ô đã chọn. Chúng tôi khởi chạy công cụ tìm kiếm, nhập cụm từ tìm kiếm, nhận kết quả, bắt đầu duyệt, tìm mã giống như thế này:

Sub Insertion1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

Chúng tôi đánh dấu mã này(không đánh số dòng, bắt đầu bằng từ phụ) và hãy nhấn Ctrl+C. Đi tới sổ làm việc MS Excel của bạn và nhấn phím tắt Alt+F11, cửa sổ soạn thảo VBA sẽ mở ra:

Trong cửa sổ bên trái "Project - VBA Project", chúng tôi chọn (nhấp chuột) sổ làm việc mà chúng tôi cần chèn macro vào, ví dụ: "VBAProject (Book2)":

Trong mục menu "Chèn", chọn "Mô-đun":

Trong cửa sổ bên trái "Dự án - Dự án VBA" bạn sẽ thấy thư mục mới"Mô-đun" và trong đó một đối tượng mới "Mô-đun1":

Di chuyển con trỏ sang trường nhập “Lớn” bên phải và nhấp vào Ctrl+V, macro đã sao chép sẽ được dán vào mô-đun:

Macro trong MS Excel có thể được chèn vào những vị trí sau:

  • Trong Mô-đun, họ thường chèn mã macro, mã này sẽ được khởi chạy khi người dùng nhấn nút (ví dụ như trong trường hợp của chúng tôi) hoặc mã chức năng (công thức);
  • Trong Trang tính, mã macro thường được chèn vào, việc khởi chạy mã này sẽ tự động diễn ra tùy thuộc vào hành động của người dùng hoặc thay đổi dữ liệu trong trang tính (dữ liệu đã thay đổi, macro được thực thi);
  • Mã macro thường được chèn vào Sổ làm việc, mã này sẽ được khởi chạy tự động tùy thuộc vào các hành động được thực hiện trên sổ làm việc (tệp). Ví dụ: macro chạy khi sổ làm việc được mở hoặc đóng hoặc khi sổ làm việc được lưu;
  • Macro cũng có thể là một phần của biểu mẫu người dùng.

Thông thường, người đăng mã sẽ chỉ định vị trí cần chèn mã vào mô-đun, trang tính hoặc sổ làm việc.

Để chèn mã vào Trang tính, hãy chọn trang tính thích hợp trong cửa sổ bên trái của trình soạn thảo VBA, nhấp đúp vào trang tính đó bằng nút chuột trái, di chuyển con trỏ đến trường nhập bên phải và dán mã.

Để chèn mã vào Sách, chọn "Sách này":


Hãy cùng luyện tập. Dán mã được đăng bên dưới vào "Sheet1".

Bảng tính phụ riêng tư_Change(Mục tiêu ByVal dưới dạng phạm vi) Khi có lỗi Chuyển đến a Nếu mục tiêu = 2 Sau đó VBA.MsgBox("Ô " & Target.Address & " = 2") Kết thúc Nếu a: Thoát Sub End Sub

Macro này hiển thị thông báo thông tin nếu bạn nhập “2” vào bất kỳ ô trang tính nào.

Quay lại bảng tính, vào “Sheet1” và nhập số “2” vào ô “A1” và nhấp vào Đi vào, sau đó bạn sẽ thấy thông báo sau:

Nếu bạn thấy thông báo này thì bạn đã làm mọi thứ đúng. Nếu không được thì bạn đã chèn sai mã ở đâu đó, hãy thử lại.

Khi dán mã, bạn cần cẩn thận nơi dán mã. Điều này có thể được thực hiện bằng cách xem những gì được viết trong tiêu đề của cửa sổ soạn thảo VBA:

2. Tìm và sử dụng hàm

Chúng tôi tìm thấy trên Internet mã VBA của một hàm đếm, ví dụ: số từ trong một ô:

Hàm công cộng Số từ trong ô(Ô dưới dạng phạm vi) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") Số từ trong ô = UBound(q) + 1 End Chức năng

Sao chép mã và nhấn tổ hợp phím Alt+F11, trình soạn thảo VBA sẽ mở:


Thêm vào mô-đun mới Dán mã đã sao chép vào sách của bạn và vào mô-đun này:


Đóng trình soạn thảo VBA và đi tới sổ làm việc của bạn. Tạo một trang tính mới (tùy chọn) trong một ô A1 nhập dòng chữ “mẹ đã rửa khung”. Chúng ta đi đến ô mà chúng ta muốn nhận kết quả (số từ), trong menu “Công thức”, nhấp vào nút “Chèn hàm”:

Trong cửa sổ "Chèn hàm" mở ra, trong trường "Danh mục", chọn "Người dùng xác định"

Trên danh sách chức năng có sẵn chọn "Số từ trong ô", nhấp vào "OK":

Nhập các đối số cần thiết và nhấp vào "OK":

Chúng tôi nhận được kết quả:

Quan trọng:

Nếu bạn không lưu sổ làm việc mà bạn đã chèn macro dưới dạng “Sổ làm việc hỗ trợ macro”, tất cả các mô-đun có macro sẽ bị xóa và bạn sẽ phải thực hiện lại tất cả công việc này.

Nếu khi chèn macro vào mô-đun, bạn thấy các dấu hiệu màu đỏ thay vì một số văn bản " ???????? "

Điều này có nghĩa là bạn gặp sự cố về mã hóa, xảy ra khi sao chép văn bản Cyrillic từ một số trình duyệt. Để khắc phục sự cố này, hãy thử dán mã đã sao chép vào tờ giấy trắng MS Excel dưới dạng "Văn bản trong mã hóa Unicode". Để thực hiện việc này, hãy chuyển đến sổ làm việc MS Excel, chọn hoặc tạo một trang tính trống, đi đến ô "A1" và nhấn phím tắt Ctrl+Alt+V. Một menu sẽ xuất hiện " Chèn đặc biệt", chọn "Văn bản Unicode" và nhấp vào "OK".

Mã phải được dán vào bảng tính không có dấu chấm hỏi:

Sau đó, sao chép lại mã đã dán vào trang tính và dán vào mô-đun.

Nếu bạn không thấy cửa sổ "Project - VBA Project" trong trình soạn thảo VBA, hãy chuyển đến tab menu "Xem" và chọn "Project Explorer" từ danh sách hoặc nhấn phím tắt Ctrl+R: