Om có hàng trăm bảng tính được đặt tên khác nhau trong cùng một bảng tính. Cấu trúc của mỗi trang tính về cơ bản là giống nhau. Anh ta muốn sao chép một phạm vi cố định (A146: O146) từ mỗi trang tính sang một trang tính mới, hết hàng này đến hàng khác.

Có một số cách bạn có thể thực hiện để làm điều này. Đối với tiền của tôi, một macro đơn giản là đặt cược tốt nhất. Tuy nhiên, trước khi đi vào phương pháp dựa trên macro, có một cách bạn có thể thực hiện bằng cách sử dụng công thức. Một cách khá độc đáo dựa vào việc xác định công thức trong Trình quản lý tên. Làm theo các bước sau để bắt đầu:

  1. Hiển thị tab Công thức của ruy-băng.

  2. Trong nhóm Tên đã xác định, hãy bấm vào công cụ Trình quản lý tên. Excel sẽ hiển thị hộp thoại Trình quản lý tên.

  3. Nhấp vào nút Mới. Excel sẽ hiển thị hộp thoại Tên mới. (Xem Hình 1.)

  4. Trong trường Tên, hãy nhập tên ListSheets (lưu ý rằng đây là một từ đơn, không có khoảng trắng).

  5. Trong trường Tham chiếu đến, hãy nhập công thức sau:

  6. Nhấp vào nút OK để hoàn tất việc tạo dải ô đã đặt tên. Phạm vi mới sẽ xuất hiện trong hộp thoại Trình quản lý tên.

  7. Nhấp vào nút Đóng để loại bỏ hộp thoại Trình quản lý tên.

Với công thức được xác định theo cách này, sau đó bạn có thể chuyển đến trang tính mà bạn muốn kết hợp tất cả các phạm vi đó. (Giả sử tên của trang tính này là “Tóm tắt”.) Tôi thực sự khuyên bạn nên đảm bảo rằng trang tính này là trang tính cuối cùng trong sổ làm việc của bạn. Trên trang tính Tóm tắt, hãy đặt công thức sau vào cột A của bất kỳ hàng nào:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

Sao chép công thức xuống tuy nhiên cần có nhiều hàng để đại diện cho tất cả các trang tính. Nói cách khác, nếu bạn có 25 trang tính (không tính trang tính Tóm tắt), hãy sao chép công thức xuống 24 hàng. Đếm bản gốc, bây giờ bạn sẽ có công thức xuất hiện trong tổng số 25 hàng.

Một lưu ý phụ ở đây: Công thức ListSheets — công thức bạn đã xác định trong Trình quản lý tên — trả về một mảng tên trang tính. Hàm ROWS được sử dụng để xác định phần tử nào của mảng đó được trả về thông qua hàm INDEX. Nếu trang tính Tóm tắt của bạn không phải là trang cuối cùng trong sổ làm việc của bạn, nó có thể dễ dàng được ListSheets trả về và bạn sẽ kết thúc việc lấy các giá trị từ nó. Đây chắc chắn là điều bạn không muốn làm, đó là lý do tại sao tôi đề nghị đảm bảo rằng Tóm tắt là trang tính cuối cùng trong sổ làm việc.

Bây giờ chỉ cần sao chép các công thức từ cột A sang bên phải để bạn có chúng đến hết cột O. Kết quả là bạn sẽ có các giá trị từ A146: O146 trong các ô chứa công thức.

Trước đó tôi đã nói rằng tôi nghĩ cách tiếp cận dựa trên vĩ mô là cách tốt nhất.

Đây là một macro nhỏ ngắn gọn giải thích lý do tại sao lại như vậy.

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

Lưu ý rằng có hai biến (sNewName và sRange) được đặt ở đầu macro. Chúng thể hiện tên bạn muốn sử dụng cho trang tính tóm tắt mới được tạo bởi macro và phạm vi ô bạn muốn sao chép từ mỗi trang tính.

Sau đó, macro làm cho trang tính đầu tiên trong sổ làm việc hoạt động và thêm một trang tính mới được sử dụng để tóm tắt. Trang tính này được gán bất kỳ tên nào bạn đã chỉ định trong biến sNewName. Sau đó macro bước qua một vòng lặp kiểm tra từng trang tính khác. Miễn là trang tính không phải là bảng tóm tắt, thì tên của trang tính được đặt vào cột A của bảng tóm tắt và phạm vi được chỉ định trong biến sRange (A146: O146) được sao chép vào trang tính tóm tắt bắt đầu từ cột B .

Phương pháp tiếp cận vĩ mô nhanh chóng và dễ dàng. Ngoài ra, nếu bạn cần làm lại trang tóm tắt của mình, chỉ cần xóa trang cũ và chạy lại macro.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9812) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.