Mitchell có rất nhiều dữ liệu trong một trang tính đại diện cho tất cả mua hàng đặt hàng của công ty ông trong một năm. Dữ liệu được sắp xếp trên cột C, cột này chứa tên của các nhà cung cấp. Mitchell muốn in một trang riêng biệt cho từng nhà cung cấp với tất cả dữ liệu cho các hàng đó. Anh ấy tự hỏi liệu có cách nào để tự động hóa việc in các trang tính dành riêng cho nhà cung cấp hay không.

Như với nhiều thứ trong Excel, có nhiều cách tiếp cận bạn có thể thực hiện cho vấn đề này. Tôi sẽ xem xét bốn cách tiếp cận trong mẹo này. Tất cả bốn cách tiếp cận giả định rằng dữ liệu của bạn được sắp xếp theo cột tên nhà cung cấp (cột C) và bạn có các đầu cột trên mỗi cột dữ liệu của mình (Tên, Ngày, Số PO, Nhà cung cấp, v.v.).

Sử dụng Tổng phụ

Để in trang tính của nhà cung cấp cụ thể bằng tổng phụ, hãy bắt đầu bằng cách chọn một ô trong dữ liệu của bạn. (Một ô trong cột C sẽ là hoàn hảo.) Nếu dữ liệu của bạn không liền kề, bạn có thể cần phải chọn tất cả theo cách thủ công; Tuy nhiên, nếu nó liền kề, thì việc chọn ô đơn phải là đủ. Sau đó, hãy làm theo các bước sau:

  1. Hiển thị tab Dữ liệu của dải băng.

  2. Trong nhóm Dàn ý, bấm vào công cụ Tổng phụ. Excel sẽ hiển thị hộp thoại Tổng phụ. (Xem Hình 1.)

  3. Đảm bảo danh sách thả xuống Tại Mỗi Thay đổi được đặt thành Nhà cung cấp. (Sử dụng tên của cột C.) Điều này cho biết nơi Excel sẽ chèn tổng phụ.

  4. Danh sách thả xuống Hàm sử dụng phải được đặt thành Đếm.

  5. Sử dụng danh sách trong hộp Thêm tổng phụ vào, chọn cột Nhà cung cấp (cột C). Đây là nơi số lượng sẽ được thêm vào.

  6. Đảm bảo rằng hộp kiểm Thay thế Tổng phụ Hiện tại được chọn.

  7. Đảm bảo hộp kiểm Ngắt trang giữa các Nhóm được chọn.

  8. Đảm bảo rằng hộp kiểm Tóm tắt Dưới đây Dữ liệu được chọn.

  9. Nhấp vào OK.

Excel đặt tổng phụ trong trang tính của bạn, nhưng nó cũng phải đặt ngắt trang trước mỗi nhà cung cấp mới. (Điều này là do bước 7, ở trên.) Các dấu ngắt trang có thể không rõ ràng ngay lập tức, nhưng chúng phát huy tác dụng khi bạn in trang tính.

Sau khi được in, những gì bạn nhận được là một trang in cho mỗi nhà cung cấp của bạn. Tổng phụ ngay dưới hàng cuối cùng trên mỗi trang cho biết số lượng đơn đặt hàng được in cho nhà cung cấp cụ thể đó.

Sử dụng dữ liệu đã lọc

Việc lọc dữ liệu của bạn khá dễ dàng và đây là một cách tiếp cận tốt nếu bạn không cần in các loại báo cáo này thường xuyên. Một lần nữa, hãy bắt đầu bằng cách chọn một ô trong dữ liệu của bạn, trừ khi dữ liệu của bạn không liền nhau.

(Trong trường hợp đó, bạn sẽ cần phải chọn tất cả dữ liệu của mình theo cách thủ công.) Sau đó, hãy làm theo các bước sau:

  1. Hiển thị tab Dữ liệu của dải băng.

  2. Nhấp vào công cụ Bộ lọc trong nhóm Sắp xếp & Bộ lọc. Excel sẽ hiển thị các chỉ báo thả xuống của Bộ lọc Tự động bên cạnh mỗi nhãn cột trong hàng 1.

  3. Sử dụng chỉ báo thả xuống cho cột Nhà cung cấp (cột C), chọn tên của nhà cung cấp bạn muốn in. Danh sách của bạn được tự động lọc để chỉ hiển thị các đơn đặt hàng từ nhà cung cấp đó.

  4. In trang như bình thường. Báo cáo được in chỉ nên hiển thị các đơn đặt hàng cho nhà cung cấp mà bạn đã chỉ định trong bước 3.

Nếu bạn muốn in báo cáo cho các nhà cung cấp khác, tất cả những gì bạn cần làm là thay đổi bộ lọc (bước 3) và in lại (bước 4). Khi hoàn tất, bạn có thể xóa bộ lọc bằng cách nhấp lại vào công cụ Bộ lọc trên tab Dữ liệu của dải băng.

Sử dụng PivotTables

Một cách nhanh chóng khác để tạo báo cáo bạn muốn là sử dụng khả năng PivotTable của Excel. Tôi sẽ không đi sâu vào cách tạo PivotTable ở đây, vì điều đó đã được đề cập trong các vấn đề khác của ExcelTips. PivotTable của bạn có thể được thiết lập theo bất kỳ cách nào bạn muốn, nhưng bạn cần đảm bảo rằng trường Nhà cung cấp nằm trong nhóm Bộ lọc của ngăn Trường PivotTable. (Xem Hình 2.)

image

Hình 2. Thiết lập PivotTable của bạn.

Tiếp theo, hiển thị tab Tùy chọn hoặc Phân tích của dải băng, tùy thuộc vào phiên bản Excel của bạn. (Các tab này chỉ hiển thị khi bạn chọn một ô trong PivotTable của mình.) Trong nhóm PivotTable, ở bên trái ruy-băng, hãy bấm vào danh sách thả xuống Tùy chọn và chọn Hiển thị Trang Bộ lọc Báo cáo. (Tùy chọn này chỉ khả dụng nếu bạn đảm bảo rằng trường Nhà cung cấp nằm trong nhóm Bộ lọc, như đã đề cập trước đó.) Excel sẽ hiển thị hộp thoại Hiển thị các Trang Bộ lọc Báo cáo. (Xem Hình 3.)

image

Hình 3. Hộp thoại Show Report Filter Pages.

Chỉ nên có một trường duy nhất được liệt kê trong hộp thoại, trừ khi bạn đã thêm nhiều hơn trường Nhà cung cấp vào nhóm Bộ lọc. Nếu có nhiều hơn một trường được liệt kê, hãy đảm bảo bạn nhấp vào trường Nhà cung cấp. Khi bạn bấm vào OK, Excel sẽ tạo các trang tính PivotTable riêng biệt cho từng nhà cung cấp trong bảng dữ liệu của bạn. Tùy thuộc vào thông tin bạn đã chọn để đưa vào PivotTable, những thông tin này có thể tạo báo cáo tuyệt vời cho nhà cung cấp của bạn. Sau đó, bạn có thể in các trang tính để nhận các báo cáo bạn muốn.

Sử dụng Macro

Có nhiều cách mà bạn có thể thiết lập macro để cung cấp cho bạn dữ liệu bạn muốn. Cá nhân tôi thích một macro sẽ đi qua dữ liệu của bạn và tạo trang tính mới cho từng nhà cung cấp. Đó là những gì macro sau làm — nó biên soạn danh sách các nhà cung cấp từ dữ liệu của bạn và sau đó tạo một trang tính có tên cho từng nhà cung cấp. Sau đó, nó sao chép thông tin từ trang tính gốc sang các trang tính mới được tạo.

Sub CreateVendorSheets()

' To use this macro, select the first cell in     ' the column that contains the vendor names.



Dim sTemp As String     Dim sVendors(99) As String     Dim iVendorCounts(99) As Integer     Dim iVendors As Integer     Dim rVendorRange As Range     Dim c As Range     Dim J As Integer     Dim bFound As Boolean

' Find last row in the worksheet     Set rVendorRange = ActiveSheet.Range(Selection, _       ActiveSheet.Cells(Selection.SpecialCells(xlCellTypeLastCell).Row, _       Selection.Column))



' Collecting all the vendor names in use     iVendors = 0

For Each c In rVendorRange         bFound = False         sTemp = Trim(c)

If sTemp > "" Then             For J = 1 To iVendors                 If sTemp = sVendors(J) Then bFound = True             Next J             If Not bFound Then                 iVendors = iVendors + 1                 sVendors(iVendors) = sTemp                 iVendorCounts(iVendors) = 0             End If         End If     Next c

' Create worksheets     For J = 1 To iVendors         Worksheets.Add After:=Worksheets(Worksheets.Count)

ActiveSheet.Name = sVendors(J)

Next J

' Start copying information     Application.ScreenUpdating = False     For Each c In rVendorRange         sTemp = Trim(c)

If sTemp > "" Then             For J = 1 To iVendors                 If sTemp = sVendors(J) Then                     iVendorCounts(J) = iVendorCounts(J) + 1                     c.EntireRow.Copy Sheets(sVendors(J)). _                       Cells(iVendorCounts(J), 1)

End If             Next J         End If     Next c     Application.ScreenUpdating = True End Sub

Như đã lưu ý ở đầu macro, bạn nên chọn ô dữ liệu đầu tiên trong cột Nhà cung cấp trước khi chạy macro. Khi hoàn thành, bạn sẽ có một trang tính cho mỗi nhà cung cấp, bạn có thể định dạng và in như mong muốn. (Bạn có thể làm cho macro hữu ích hơn nữa bằng cách thêm mã sẽ đưa thông tin tiêu đề cột hoặc thông tin khác vào mỗi trang tính đã tạo.) Khi hoàn tất, bạn sẽ cần xóa trang tính cho các nhà cung cấp đó để lần sau khi bạn chạy macro bạn không gặp phải vấn đề gì.

_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 (13633) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.