Sử dụng Macro trong Excel trên Mac để tiết kiệm thời gian và làm được nhiều việc hơn

Sử dụng Macro trong Excel trên Mac để tiết kiệm thời gian và làm được nhiều việc hơn

Excel trên Mac không phải lúc nào cũng giống như trên Windows. Macro thực sự sẽ không hoạt động trừ khi chúng được tạo riêng cho Mac.





Bắt đầu từ năm 2013, Microsoft đã đưa macro trở lại. Có hai loại macro: loại bạn có thể tạo bằng cách ghi nhanh các thao tác của mình và loại sử dụng VBA để thiết kế các quá trình tự động hóa nâng cao hơn. Với Office 2016, Excel đang sử dụng cùng một cơ sở mã trên tất cả các nền tảng . Thay đổi này sẽ giúp macro hoạt động trên các nền tảng dễ dàng hơn.





Vì vậy, chúng ta hãy xem cách này hiện đang hoạt động trên macOS.





làm cách nào để tắt đèn pin

Bật Macro trong Excel trên Mac

Làm việc với macro trong Excel trên máy Mac của bạn có thể không được bật theo mặc định. Cài đặt này là do macro có thể là vectơ phần mềm độc hại. Cách dễ nhất để biết là xem liệu bạn có Nhà phát triển tab có sẵn trên Ribbon trong Excel. Nếu bạn không nhìn thấy nó, nó rất đơn giản để kích hoạt.

Bấm vào Excel trong thanh menu, sau đó chọn Sở thích trong trình đơn thả xuống. Trong menu, nhấp vào Ruy-băng & Thanh công cụ . Trong danh sách bên phải, Nhà phát triển sẽ ở dưới cùng, nhấp vào hộp kiểm. Cuối cùng, nhấp vào Cứu và bạn sẽ thấy tab Nhà phát triển hiển thị ở cuối Ruy-băng.



Sau khi bạn tạo từng sổ làm việc bằng macro, hãy lưu nó ở định dạng mới .xlsm để sử dụng macro sau khi mở lại tệp. Nếu bạn quên, Excel sẽ nhắc bạn mỗi lần bạn cố gắng lưu. Bạn cũng sẽ cần bật macro mỗi khi mở tệp.

Ghi Macro theo cách thủ công trong Excel trên Mac

Mặc dù bạn có thể mã macro , điều đó có thể không dành cho tất cả mọi người. Nếu bạn chưa sẵn sàng bắt đầu làm việc với VBA, Excel cho phép bạn ghi lại các bước cho macro của mình trong một trang tính hiện có. Nhấp vào tab Nhà phát triển để xem các tùy chọn của bạn.





Bạn đang tìm kiếm tùy chọn thứ ba trong Ribbon, Ghi Macro . Nhấp vào đây và một hộp thoại bật lên cho phép bạn đặt tên cho macro của mình và đặt phím tắt. Bạn có thể xác định phạm vi macro của mình cho Sổ làm việc Hiện tại , đến Sổ làm việc mới , hoặc trong của bạn Sổ làm việc Macro Cá nhân . Sổ làm việc Macro Cá nhân có trong hồ sơ người dùng của bạn và cho phép bạn sử dụng các macro giữa các tệp của bạn.

Sau khi bạn ghi lại các hành động của mình, chúng sẽ có sẵn trên cùng một tab này. Nhấp vào macro sẽ hiển thị các macro đã lưu trong sổ làm việc của bạn. Nhấp vào tên macro của bạn và nhấp vào Chạy để chạy các hành động đã ghi của bạn.





Ví dụ 1: Tổng doanh số hàng ngày và trung bình hàng giờ

Đối với một macro ví dụ, bạn sẽ chạy qua một bảng doanh số hàng ngày, với doanh số bán hàng được chia nhỏ theo tổng số hàng giờ. Macro của bạn sẽ thêm tổng doanh số hàng ngày, sau đó thêm giá trị trung bình vào cột cuối cùng của mỗi khoảng thời gian hàng giờ. Nếu bạn làm việc ở vị trí bán lẻ hoặc bán hàng khác, đây là một trang tính hữu ích để theo dõi doanh thu.

Chúng ta cần thiết lập trang tính đầu tiên. Sử dụng khoảng trống đầu tiên này làm mẫu để sao chép vào tab mới mỗi ngày có thể giúp bạn tiết kiệm thời gian. Trong cột / hàng đầu tiên đặt Giờ / Ngày. Trên đầu trang thêm từ Thứ Hai đến Thứ Sáu.

Sau đó, trong cột đầu tiên, hãy chia nhỏ tổng số giờ từ 8-5. Tôi đã sử dụng thời gian 24 giờ, nhưng bạn có thể sử dụng ký hiệu AM / PM nếu muốn. Trang tính của bạn phải khớp với ảnh chụp màn hình ở trên.

Thêm một tab mới và sao chép mẫu của bạn vào đó. Sau đó điền vào dữ liệu bán hàng của bạn trong ngày. (Nếu bạn không có dữ liệu để điền trang tính này, bạn có thể nhập = RandBetween (10.1000) trong tất cả các ô để tạo dữ liệu giả.) Tiếp theo, nhấp vào Nhà phát triển trong Ribbon.

Sau đó, nhấp vào Ghi Macro . Trong hộp thoại nhập tên là AverageandSum và để nó được lưu trữ trong Sổ làm việc này . Bạn có thể đặt phím tắt nếu muốn. Bạn có thể nhập mô tả nếu bạn cần thêm chi tiết về chức năng của macro. Nhấn OK để bắt đầu thiết lập macro.

Ở cuối danh sách hàng giờ, hãy nhập Tổng hàng ngày . Trong ô bên cạnh, hãy nhập = SUM (B2: B10) . Sau đó, sao chép và dán nó vào phần còn lại của các cột. Sau đó, trong tiêu đề thêm Trung bình sau cột cuối cùng. Sau đó, trong ô tiếp theo xuống, hãy nhập = Trung bình (B2: F2) . Sau đó, dán nó vào các ô ở phần còn lại của cột.

Sau đó nhấp vào Dừng ghi . Macro của bạn hiện có thể sử dụng trên mỗi trang tính mới mà bạn thêm vào sổ làm việc của mình. Sau khi bạn có một trang dữ liệu khác, hãy quay lại Nhà phát triển và bấm vào Macro . Macro của bạn sẽ được đánh dấu, hãy nhấp vào chạy để thêm tổng và trung bình của bạn.

Ví dụ này có thể giúp bạn tiết kiệm một vài bước, nhưng đối với các hành động phức tạp hơn có thể tăng thêm. Nếu bạn thực hiện các thao tác tương tự trên dữ liệu có định dạng giống hệt nhau, hãy sử dụng macro đã ghi.

Macro VBA trong Excel trên Mac

Các macro được ghi lại thủ công trong Excel giúp dữ liệu luôn có cùng kích thước và hình dạng. Nó cũng hữu ích nếu bạn muốn thực hiện các hành động trên toàn bộ trang tính. Bạn có thể sử dụng macro của mình để chứng minh vấn đề.

Thêm giờ và ngày khác vào trang tính và chạy macro. Bạn sẽ thấy macro ghi đè dữ liệu mới của mình. Cách chúng tôi giải quyết vấn đề này là sử dụng mã để làm cho macro năng động hơn bằng cách sử dụng VBA, đây là một phiên bản thu gọn của Visual Basic . Việc triển khai tập trung vào tự động hóa cho Office.

Nó không phải là dễ dàng nhận như Applescript , nhưng tính năng tự động hóa của Office hoàn toàn được xây dựng dựa trên Visual Basic. Vì vậy, khi bạn làm việc với nó ở đây, bạn có thể nhanh chóng xoay vòng và sử dụng nó trong các ứng dụng Office khác. (Nó cũng có thể là một trợ giúp lớn nếu bạn đang gặp khó khăn với PC Windows tại nơi làm việc.)

Khi làm việc với VBA trong Excel, bạn có một Cửa sổ riêng. Ảnh chụp màn hình ở trên là macro được ghi lại của chúng tôi khi nó xuất hiện trong trình chỉnh sửa mã. Chế độ cửa sổ có thể hữu ích để xem mã của bạn khi bạn đang học. Khi macro của bạn bị treo, có các công cụ gỡ lỗi để xem trạng thái của các biến và dữ liệu trang tính của bạn.

Office 2016 hiện đi kèm với trình soạn thảo Visual Basic đầy đủ. Nó cho phép bạn sử dụng Trình duyệt đối tượng và các công cụ gỡ lỗi từng bị giới hạn ở phiên bản Windows. Bạn có thể truy cập Trình duyệt đối tượng bằng cách đi tới Xem> Trình duyệt Đối tượng hoặc chỉ cần nhấn Shift + Command + B . Sau đó, bạn có thể duyệt qua tất cả các lớp, phương thức và thuộc tính có sẵn. Nó rất hữu ích trong việc xây dựng mã trong phần tiếp theo.

Ví dụ 2: Tổng doanh số hàng ngày và trung bình hàng giờ có mã

Trước khi bạn bắt đầu mã hóa macro của mình, hãy bắt đầu bằng cách thêm một nút vào mẫu. Bước này giúp người dùng mới truy cập vào macro của bạn dễ dàng hơn nhiều. Họ có thể nhấp vào một nút để gọi macro thay vì đào sâu vào các tab và menu.

Chuyển trở lại trang mẫu trống mà bạn đã tạo ở bước cuối cùng. Bấm vào Nhà phát triển để quay lại tab. Khi bạn ở trên tab, hãy nhấp vào Cái nút . Tiếp theo, nhấp vào một nơi nào đó trong trang tính trên mẫu để đặt nút. Menu macro xuất hiện, đặt tên cho macro của bạn và nhấp vào Mới .

Cửa sổ Visual Basic sẽ mở ra; bạn sẽ thấy nó được liệt kê là Mô-đun 2 trong trình duyệt dự án. Ngăn mã sẽ có Sub AverageandSumButton () ở trên cùng và một vài dòng ở dưới Kết thúc Sub . Mã của bạn cần phải đi giữa hai mã này, vì nó là phần đầu và phần cuối của macro của bạn.

Bước 1: Khai báo các biến

Để bắt đầu, bạn sẽ cần khai báo tất cả các biến của mình. Chúng nằm trong khối mã bên dưới, nhưng lưu ý về cách chúng được xây dựng. Bạn nên khai báo tất cả các biến bằng cách sử dụng Không có trước tên, và sau đó như với kiểu dữ liệu.

Sub AverageandSumButton()
Dim RowPlaceHolder As Integer
Dim ColumnPlaceHolder As Integer
Dim StringHolder As String
Dim AllCells As Range
Dim TargetCells As Range
Dim AverageTarget As Range
Dim SumTarget As Range

Bây giờ bạn đã có tất cả các biến của mình, bạn cần sử dụng một số biến phạm vi ngay lập tức. Dải ô là các đối tượng chứa các phần của trang tính dưới dạng địa chỉ. Biến Tất cả các ô sẽ được đặt thành tất cả các ô hiện hoạt trên trang tính, bao gồm nhãn cột và hàng. Bạn nhận được điều này bằng cách gọi Bảng hoạt động đối tượng và sau đó là Đã sử dụng bất động sản.

Vấn đề là bạn không muốn các nhãn được bao gồm trong dữ liệu trung bình và tổng. Thay vào đó, bạn sẽ sử dụng một tập hợp con của phạm vi AllCells. Đây sẽ là phạm vi TargetCells. Bạn khai báo phạm vi của nó theo cách thủ công. Địa chỉ bắt đầu của nó sẽ là ô ở hàng thứ hai trong cột thứ hai của phạm vi.

Bạn gọi điều này bằng cách gọi của bạn AllCells phạm vi, sử dụng nó Tế bào lớp để lấy ô cụ thể đó bằng cách sử dụng (2.2) . Để lấy ô cuối cùng trong phạm vi, bạn vẫn sẽ gọi AllCells . Lần này sử dụng SpecialCells phương pháp lấy tài sản xlCellTypeLastCell . Bạn có thể thấy cả hai điều này trong khối mã bên dưới.

Set AllCells = ActiveSheet.UsedRange
Set TargetCells = Range(AllCells.Cells(2, 2), AllCells.SpecialCells(xlCellTypeLastCell))

Bước 2: Đối với mỗi vòng lặp

Hai phần mã tiếp theo là Cho mỗi vòng lặp. Các vòng lặp này đi qua một đối tượng để tác động lên từng tập con của đối tượng đó. Trong trường hợp này, bạn đang thực hiện hai trong số chúng, một cho mỗi hàng và một cho mỗi cột. Vì chúng gần như giống hệt nhau, chỉ có một trong số chúng ở đây; nhưng cả hai đều nằm trong khối mã. Các chi tiết hầu như giống hệt nhau.

Trước khi bắt đầu vòng lặp cho mỗi hàng, bạn cần đặt cột mục tiêu nơi vòng lặp ghi giá trị trung bình của mỗi hàng. Bạn sử dụng ColumnPlaceHolder biến để đặt mục tiêu này. Bạn đặt nó bằng Đếm biến của Tế bào lớp AllCells . Thêm một vào nó để di chuyển nó sang bên phải dữ liệu của bạn bằng cách thêm vào +1 .

Tiếp theo, bạn sẽ bắt đầu vòng lặp bằng cách sử dụng Cho mỗi . Sau đó, bạn muốn tạo một biến cho tập hợp con, trong trường hợp này, subRow . Sau Trong , chúng tôi đặt đối tượng chính mà chúng tôi đang phân tích cú pháp TargetCells . Nối .Rows ở cuối để giới hạn vòng lặp chỉ có mỗi hàng, thay vì mỗi ô trong phạm vi.

Bên trong vòng lặp, bạn sử dụng phương thức ActiveSheet.Cells để đặt một mục tiêu cụ thể trên trang tính. Các tọa độ được thiết lập bằng cách sử dụng subRow.Row để lấy hàng mà vòng lặp hiện đang ở. Sau đó, bạn sử dụng ColumnPlaceHolder cho các tọa độ khác.

Bạn sử dụng điều này cho cả ba bước. Lần đầu tiên bạn thêm vào .giá trị sau dấu ngoặc đơn và đặt bằng WorksheetFunction.Average (subRow) . Điều này sẽ ghi công thức cho giá trị trung bình của hàng vào ô mục tiêu của bạn. Dòng tiếp theo bạn thêm vào .Phong cách và đặt nó bằng 'Tiền tệ' . Bước này khớp với phần còn lại của trang tính của bạn. Trên dòng cuối cùng, bạn nối thêm .Font.Bold và đặt nó bằng Thật . (Lưu ý rằng không có dấu ngoặc kép xung quanh giá trị này, vì nó là giá trị boolean.) Dòng này tô đậm phông chữ để làm cho thông tin tóm tắt nổi bật so với phần còn lại của trang tính.

Cả hai bước đều có trong ví dụ mã bên dưới. Vòng lặp thứ hai hoán đổi các hàng cho các cột và thay đổi công thức thành Tổng . Sử dụng phương pháp này liên kết các tính toán của bạn với định dạng của trang tính hiện tại. Nếu không, nó được liên kết với kích thước tại thời điểm bạn ghi macro. Vì vậy, khi bạn làm việc nhiều ngày hơn hoặc nhiều giờ hơn, chức năng sẽ phát triển cùng với dữ liệu của bạn.

cách cài đặt chương trình âm thanh windows 10
ColumnPlaceHolder = AllCells.Columns.Count + 1
For Each subRow In TargetCells.Rows
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Value = WorksheetFunction.Average(subRow)
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Style = 'Currency'
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Font.Bold = True
Next subRow
RowPlaceHolder = AllCells.Rows.Count + 1
For Each subColumn In TargetCells.Columns
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Value = WorksheetFunction.Sum(subColumn)
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Style = 'Currency'
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Font.Bold = 'True'
Next subColumn

Bước 3: Gắn nhãn cho các tóm tắt của bạn

Tiếp theo, gắn nhãn hàng và cột mới, đặt RowPlaceHolderColumnPlaceHolder lần nữa. Lần dùng đầu tiên AllCells.Row để có được hàng đầu tiên trong phạm vi và sau đó AllCells.Column + 1 để lấy cột cuối cùng. Sau đó, bạn sẽ sử dụng phương pháp tương tự như vòng lặp để đặt giá trị thành 'Doanh số Trung bình' . Bạn cũng sẽ sử dụng cùng một .Font.Bold thuộc tính để in đậm nhãn mới của bạn.

Sau đó đảo ngược nó, đặt chỗ dành sẵn của bạn thành cột đầu tiên và hàng cuối cùng để thêm 'Tổng doanh số' . Bạn cũng muốn in đậm điều này.

Cả hai bước đều nằm trong khối mã bên dưới. Đây là phần cuối của macro được lưu ý bởi Kết thúc Sub . Bây giờ bạn sẽ có toàn bộ macro và có thể nhấp vào nút để chạy nó. Bạn có thể dán tất cả các khối mã này vào bảng excel của mình nếu bạn muốn gian lận, nhưng điều thú vị là ở đâu?

ColumnPlaceHolder = AllCells.Columns.Count + 1
RowPlaceHolder = AllCells.Row
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value = 'Average Sales'
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold = True
ColumnPlaceHolder = AllCells.Column
RowPlaceHolder = AllCells.Rows.Count + 1
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value = 'Total Sales'
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold = True
End Sub

Tiếp theo cho Macro trong Excel trên Mac là gì?

Các macro được ghi lại rất tốt để sử dụng cho sự lặp lại có thể dự đoán được. Ngay cả khi đó là một cái gì đó đơn giản như thay đổi kích thước tất cả các ô và in đậm tiêu đề, những điều này có thể giúp bạn tiết kiệm thời gian. Chỉ cần tránh những sai lầm vĩ mô phổ biến .

Visual Basic mở ra cánh cửa cho người dùng Mac Excel tìm hiểu sâu hơn về tự động hóa Office. Visual Basic theo truyền thống chỉ có sẵn trên Windows. Nó cho phép các macro của bạn thích ứng với dữ liệu một cách linh hoạt, làm cho chúng linh hoạt hơn. Nếu bạn có đủ kiên nhẫn, đây có thể là cánh cửa dẫn đến lập trình nâng cao hơn.

Muốn có thêm thủ thuật bảng tính tiết kiệm thời gian? Tìm hiểu cách tự động đánh dấu dữ liệu cụ thể với định dạng có điều kiện trong Excel và đánh dấu có điều kiện trong Numbers trên Mac.

Đăng lại Đăng lại tiếng riu ríu E-mail 3 cách để kiểm tra xem một email là thật hay giả

Nếu bạn nhận được một email có vẻ hơi khó hiểu, tốt nhất bạn nên kiểm tra tính xác thực của nó. Dưới đây là ba cách để biết một email có phải là thật hay không.

Đọc tiếp
Chủ đề liên quan
  • Mac
  • Năng suất
  • Lập trình
  • Lập trình Visual Basic
  • Microsoft Excel
Giới thiệu về tác giả Michael McConnell(44 bài báo đã xuất bản)

Michael đã không sử dụng máy Mac khi họ đã chết, nhưng anh ấy có thể viết mã trong Applescript. Ông có bằng Khoa học Máy tính và Tiếng Anh; anh ấy đã viết về Mac, iOS và trò chơi điện tử được một lúc rồi; và anh ấy là một con khỉ IT ban ngày trong hơn một thập kỷ, chuyên về viết kịch bản và ảo hóa.

Xem thêm từ Michael McConnell

Theo dõi bản tin của chúng tôi

Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!

Bấm vào đây để đăng ký
Thể LoạI Mac