Các macro hữu ích cho word và excel. Tạo macro trong Microsoft Excel. Cách tạo hàm tùy chỉnh trong VBA

Làm việc với sách

Macro 1. Tạo mới sách bài tập từ đầu
Macro 2. Lưu sổ làm việc khi thay đổi một ô/phạm vi cụ thể
Macro 3. Lưu sổ làm việc trước khi đóng
Macro 4. Bảo vệ bảng tính trong sổ làm việc trước khi đóng
Macro 5. Bỏ bảo vệ sheet khi mở file Excel
Macro 6. Mở sổ làm việc trên trang tính mong muốn
Macro 7. Mở một sổ làm việc cụ thể do người dùng xác định
Macro 8. Xác định xem sách có đang mở không
Macro 9. Xác định xem sách có tồn tại trong một thư mục hay không
Macro 10. Cập nhật tất cả kết nối trong sổ làm việc đang mở
Macro 11. Đóng tất cả sách cùng một lúc
Macro 12. Mở tất cả các bảng tính trong một thư mục
Macro 13. In tất cả sách trong một thư mục
Macro 14. Không được phép đóng sách cho đến khi ô được lấp đầy
Macro 15. Tạo bản sao lưu cuốn sách hiện tại với ngày hôm nay

Làm việc với trang tính

Macro 16. Thêm một bảng tính mới và đặt tên
Macro 17. Xóa tất cả các trang trừ trang đang hoạt động
Macro 18. Ẩn mọi thứ ngoại trừ bảng tính đang hoạt động
Macro 19. Hiển thị tất cả các sheet của bảng tính
Macro 20. Di chuyển bảng tính
Macro 21. Sắp xếp các sheet theo tên
Macro 22. Nhóm các trang tính theo màu nhãn
Macro 23. Sao chép một trang tính vào sổ làm việc mới
Macro 24. Tạo một bảng tính mới cho mỗi sheet
Macro 25. In tờ
Macro 26. Bảo vệ tất cả các trang tính
Macro 27. Bỏ bảo vệ tất cả các trang tính
Macro 28. Tạo mục lục
Macro 29: Bấm đúp để phóng to bảng tính
Macro 30. Chọn cột hàng hiện hoạt

Lựa chọn và thay đổi phạm vi

Macro 31. Chọn và định dạng một phạm vi
Macro 32. Tạo và chọn các phạm vi được đặt tên
Macro 33. Đếm số lượng ô
Macro 34. Lựa chọn và định dạng phạm vi
Macro 35. Chèn dòng trống trong phạm vi
Macro 36. Hiển thị tất cả dòng ẩn và cột
Macro 37. Xóa dòng trống
Macro 38. Xóa cột trống
Macro 39. Chọn và định dạng tất cả các công thức trong sổ làm việc
Macro 40: Tìm và chọn hàng hoặc cột trống đầu tiên
Macro 41. Áp dụng màu tô bổ sung
Macro 42. Sắp xếp phạm vi bằng cách nhấp đúp
Macro 43. Giới hạn phạm vi cuộn trong một khu vực cụ thể
Macro 44. Tự động thiết lập vùng in của sheet

Làm việc với dữ liệu

Macro 45. Sao chép và dán một phạm vi
Macro 46: Chuyển đổi tất cả các công thức trong một phạm vi thành giá trị
Macro 47. Chuyển đổi giá trị văn bản sang số
Macro 48. Chuyển dấu gạch ngang thành dấu trừ
Macro 49. Xóa khoảng trống thừa từ tất cả các ô trong phạm vi
Macro 50. Cắt bỏ 5 ký tự bên trái trong mỗi ô của dãy
Macro 51. Thêm số 0 còn thiếu vào ô
Macro 52. Thay thế các ô trống bằng 0
Macro 53. Thêm văn bản vào đầu hoặc cuối ô
Macro 54. Tạo macro chuyển đổi dữ liệu
Macro 55. Xóa dữ liệu (ký tự không in được)
Macro 56. Chọn trùng lặp trong vùng dữ liệu
Macro 57. Ẩn dòng lặp
Macro 58. Ẩn có chọn lọc các mũi tên của bộ lọc tự động
Macro 59. Sao chép các hàng đã lọc vào sổ làm việc mới
Macro 60. Tạo sheet mới cho từng phần tử trong AutoFilter
Macro 61. Hiển thị các cột được lọc trên thanh trạng thái

Làm việc với Bảng Pivot

Macro 62: Tạo khả năng tương thích ngược bảng tổng hợp
Macro 63. Cập nhật tất cả các bảng trụ trong một cuốn sách
Macro 64. Tạo một “kho” tất cả các bảng tổng hợp của cuốn sách
Macro 65. Tạo tất cả các bảng tổng hợp bằng cùng một bộ đệm dữ liệu
Macro 66. Ẩn tất cả các tổng phụ trong bảng tổng hợp
Macro 67. Thay đổi tên dữ liệu của tất cả các trường tóm tắt
Macro 68. Tổng hợp bắt buộc cho tất cả dữ liệu tóm tắt
Macro 69: Áp dụng định dạng số cho tất cả các mục dữ liệu
Macro 70. Sắp xếp các trường tóm tắt theo thứ tự bảng chữ cái
Macro 71. Áp dụng cách sắp xếp tùy chỉnh cho các mục dữ liệu
Macro 72: Đưa biện pháp bảo vệ lên bảng trụ
Macro 73: Áp dụng các ràng buộc trường Pivot
Vĩ mô 74. Tự động loại bỏ tờ với các chi tiết tóm tắt
Macro 75: In PivotTable cho từng mục bộ lọc
Macro 76. Tạo một file mới cho mỗi phần tử bộ lọc
Macro 77. Chuẩn bị vùng dữ liệu cho bảng trụ

Làm việc với biểu đồ và đồ thị

Macro 78. Thay đổi kích thước biểu đồ trên trang tính
Macro 79. Liên kết biểu đồ với một phạm vi cụ thể
Macro 80: Tạo tập hợp sơ đồ rời rạc
Macro 81: In tất cả biểu đồ trên bảng tính
Macro 82. Đánh dấu giá trị tốt nhất và xấu nhất trên
Macro 83. Màu giống nhau cho các giá trị trên các biểu đồ khác nhau
Macro 84. Khớp màu của biểu đồ với màu của phạm vi

Gửi email từ Excel

Macro 85. Gửi sách đang hoạt động qua thư (đính kèm)
Macro 86: Gửi một loạt giá trị dưới dạng tệp đính kèm
Macro 87. Gửi một tờ dưới dạng tệp đính kèm
Macro 88. Gửi email có liên kết tới tệp của chúng tôi
Macro 89: Gửi email thêm địa chỉ vào danh sách liên hệ của chúng tôi
Macro 90. Lưu tất cả tệp đính kèm vào một thư mục riêng
Macro 91. Lưu các tệp đính kèm cụ thể vào một thư mục

Khả năng tương tác với các ứng dụng Office khác

Macro 92. Chạy yêu cầu truy cập từ Excel



Macro 96. Nén cơ sở Tiếp cận thông tin từ Excel
Macro 97. Đang gửi Dữ liệu Excel vào một tài liệu Word
Macro 98. Thực hiện sáp nhập với tài liệu Word
Macro 99: Gửi dữ liệu Excel tới bản trình bày PowerPoint
Macro 100. Đang gửi Biểu đồ Excel trong bài thuyết trình PowerPoint
Macro 101: Chuyển đổi sổ làm việc thành bản trình bày PowerPoint

Khả năng tương tác với các ứng dụng Office khác
học cách làm việc với Word, Access và PowerPoint
Macro 92. Chạy yêu cầu truy cập từ Excel
Macro 93. Chạy macro Access từ Excel
Macro 94. Mở báo cáo Access từ Excel
Macro 95. Mở biểu mẫu truy cập từ Excel

Các đối tượng như macro trong Excel sẽ giúp bạn sắp xếp hợp lý quy trình làm việc trong Excel.

Chúng ta hãy xem xét kỹ hơn tất cả các tính năng làm việc với các đối tượng này trong gói phần mềm MS Office.

Nhờ sử dụng macro, mọi ô trong tài liệu của bạn đều có thể được tự động hóa. Điều này xảy ra bằng cách cho phép người dùng ghi lại tất cả các hành động khi chúng được tạo.

Macro là gì và tại sao chúng cần thiết?

Bạn có thể làm việc với macro trong bất kỳ chương trình MS Office nào. Trước hết, chúng cần thiết để tổ chức thành thạo công việc của người dùng trong chương trình.

Chúng cần thiết để không thực hiện cùng một loại nhiệm vụ và hành động vài chục lần.

Việc tạo và sử dụng chúng sẽ giúp tiết kiệm đáng kể thời gian và tối đa hóa khả năng tự động hóa của robot trong chương trình.

Quan trọng! Nhờ macro đã tạo, bạn có thể hoàn thành công việc ngay lập tức, trong khi thực hiện thủ công sẽ mất rất nhiều thời gian.

Phần thân của nó về cơ bản được tạo thành từ các hướng dẫn cho chương trình biết phải làm gì nếu người dùng chọn cái này hay cái kia.

Bạn cũng có thể gặp khái niệm macro trong Word, nhưng trong Excel nó có một số ưu điểm:

• Đầu tiên, nó kết hợp tất cả các hướng dẫn thành một tập lệnh thực thi hoàn chỉnh, cho phép bạn tối ưu hóa tải chương trình và bắt đầu hoạt động nhanh hơn;
• Bạn có thể gọi nó bằng một phím trên thanh công cụ hoặc sử dụng tổ hợp phím đặc biệt. Điều này sẽ cho phép người dùng không nhìn lên bàn phím khi làm việc;

Chúng tôi lưu ý thêm hai bài viết có thểbạn sẽ cần:

Tạo macro riêng trong Excel 2010, 2007, 2013

Chúng ta cùng xem kỹ hơn các ví dụ về tạo trong Excel 2007, 2013:

• Mở tài liệu bạn đang làm việc và tài liệu bạn muốn tạo macro. Nhân tiện, mỗi ô mà một hành động được thực hiện phải được xử lý;
• Hiển thị tab nhà phát triển trong ribbon. Để thực hiện việc này, hãy mở mục menu “Tệp” và mở các tùy chọn như trong hình;

• Sau đó chọn cài đặt dải băng và thêm cửa sổ nhà phát triển vào danh sách các cửa sổ chính, như trong hình bên dưới;

• Bây giờ bạn có thể tiến hành trực tiếp để tạo macro người dùng.
  Sau khi tạo, mỗi ô sẽ được tự động hóa - điều này có nghĩa là bất kỳ ô nào trong tài liệu người dùng sẽ thực hiện cùng loại hành động mà người dùng chỉ định;
• Tìm trong tab nhà phát triển chìa khóa đặc biệtđể tạo ra. Vị trí của nó được hiển thị trong hình bên dưới;

• Cắm chìa khóa. Một cửa sổ tạo sẽ xuất hiện trong đó bạn cần chỉ định tên và tổ hợp phím để bật nó. Bạn cũng có thể thêm mô tả ngắn về cách hoạt động của macro.
  Điều này phải được thực hiện nếu bạn có quá nhiều trong số chúng, để không bị nhầm lẫn;

• Tiếp theo, nhấp vào OK. Cửa sổ sẽ đóng lại và quá trình ghi sẽ bắt đầu. Để dừng ghi, bấm phím tương ứng trên bảng điều khiển;

• Bây giờ hãy bắt đầu thực hiện các hành động sẽ được ghi lại trong macro. Mỗi ô có thể chứa đầy dữ liệu nhất định.
  Bạn cũng có thể chỉ làm việc với một ô, sau khi ghi và kích hoạt macro, ô tương tự sẽ được ghi theo thuật toán đã chỉ định;
• Đừng quên nhấn nút dừng ghi dữ liệu. Sau khi hoàn thành tất cả các bước trên, nó sẽ được ghi lại và lưu trong chương trình.

Cách kích hoạt và làm việc với macro trong Excel

Để thực hiện việc này, hãy làm theo hướng dẫn bên dưới:

• Trên tab nhà phát triển, tìm nút có tên Macro. Nhấn vào nó;

• Chọn macro bạn cần từ danh sách và nhấp vào nút “Chạy”;

• Bạn cũng có thể chạy macro cần thiết bằng phím tắt đã được người dùng chỉ định ở giai đoạn đầu tạo macro;
• Sau khi nhấp vào nút thực thi, tất cả các hành động được thực hiện trong quá trình ghi sẽ được thực hiện lại.

Macro thuận tiện nhất để sử dụng khi tế bào cụ thể cần sao chép nhiều lần.

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ừ bảng excel. Bạn có thể chạy macro và dữ liệu sẽ được chuyển đến các bảng trong Tài liệu văn bản. 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à […]

Macro để đánh dấu ô A1 trên mỗi trang tính trong sổ làm việc đang hoạt động. Điều này cũng khiến màn hình chuyển động.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Chọn ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Chọn Trang tiếp theo(1) .Select Application.ScreenUpdating = True End Sub

Macro để sao chép trang tính hiện tại một số lần nhất định. Hữu ích cho việc kiểm tra một số macro - đã thực hiện các chỉnh sửa, kiểm tra chúng trên bản sao dữ liệu. Chúng tôi đã hết bản sao—chạy lại macro

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Nhập số bản sao của trang tính hiện tại") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Sao chép" & j Tiếp theo j Application.ScreenUpdating = True End Sub

Tạo trang tính với tiêu đề từ một phạm vi được chỉ định trên trang tính

Sub CreateFromList() Làm mờ ô làm phạm vi cho mỗi ô trong các trang lựa chọn.Thêm sau:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Ô tiếp theo End Sub

Markros vì gửi thư chậm trễ. Macro được sửa đổi từ cuốn sách Lập trình VBA chuyên nghiệp của John Walkenbach

Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Tiếp tục lại với OutMail .To = " [email được bảo vệ]" .Subject = "Báo cáo bán hàng" .Attachments.Add "C:\Test.txt" .Body = "Văn bản email" .DeferredDeliveryTime = Thay thế(Ngày, ".", "/") & " 11:00:00 " .send ".Display để tạo một lá thư và mở nó End With On Error GoTo 0 Set OutMail = Không dọn dẹp: Set OutApp = Nothing End Sub

Macro mục lục được sửa đổi một chút từ Nikolai Pavlov.
Nếu trang “Mục lục” đã tồn tại trong sách thì macro sẽ nhắc bạn xóa trang đó. Nếu không, hãy tạo một trang “Mục lục” và chèn các liên kết có tên trang tính

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook For Each Workbook Trong ActiveWorkbook.Worksheets Nếu Worksheet.Name = "Table of Contents" Then Answer = MsgBox("Sổ làm việc có một trang có tên Mục lục. Xóa nó?", vbYesNo) Nếu Trả lời = vbNo Sau đó Thoát Sub Nếu Trả lời = vbYes Then Application.DisplayAlerts = Sai Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array) (1)).Chọn Trang tính.Thêm Trang tính(1).Name = "Mục lục" Với ActiveWorkbook cho mỗi trang tính Trong ActiveWorkbook.Worksheets Nếu sheet.Name<>"Mục lục" Sau đó Đặt ô = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add neo:=cell, Địa chỉ:="", SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = sheet.Name End Nếu trang tiếp theo kết thúc bằng hàng("1:1").Delete Application.ScreenUpdating = True End Sub

Sắp xếp các trang tính từ Trình hướng dẫn VBA. Macro cũng sắp xếp tờ ẩn. Sẽ không hoạt động nếu cuốn sách có cấu trúc được bảo vệ

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " ghi nhớ trạng thái hiển thị của từng trang tính và thực hiện mọi thứ hiển thị cho mỗi iSht trong ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sắp xếp các trang hiển thị For i = 1 To .Sheets.Count - 1 For j = i + 1 Đến .Sheets.Count Nếu UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " khôi phục trạng thái ban đầu khả năng hiển thị của từng trang tính Đối với mỗi iSht trong ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Ứng dụng tiếp theo.EnableEvents = True: Application.ScreenUpdating = True End Sub

Nhập cột "Field1" và "Field2" từ trang tính "Sheet1" tập tin Excel“C:\Manager.xls” thông qua kết nối ADODB và chèn nội dung bắt đầu từ ô A1 của trang tính hiện tại

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 của bộ Microsoft Office) - đây 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ỏ):

• Áp dụng kiểu và định dạng.
• Thực hiện hoạt động khác nhau với dữ liệu số và văn bản.
• Sử dụng nguồn lực bên ngoài dữ liệu (tệp cơ sở dữ liệu, tài liệu văn bản vân vân.)
• Tạo một tài liệu mới.
• Thực hiện tất cả các bước trên theo bất kỳ sự kết hợp nào.

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.

Khi hành động này được hoàn thành, 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 macro đã tạo, 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 tất cả các ô dữ liệu, hãy nhấp vào click chuột phải chuột theo 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ừ menu ngữ cảnh, hãy 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.