Không có Power Pivot? Không thành vấn đề. Hầu hết Power Pivot được tích hợp sẵn trong Excel 2013 và thậm chí nhiều hơn trong Excel 2016. Hôm nay, mẹo của chúng tôi từ Ash là nối các bảng trong một bảng tổng hợp.
Mỗi thứ Tư trong bảy tuần, tôi giới thiệu một trong những mẹo yêu thích của Ash Sharma. Ash là giám đốc sản phẩm của nhóm Excel. Nhóm của anh ấy mang đến cho bạn bảng tổng hợp và nhiều thứ hay ho khác. Ngày nay, tính năng yêu thích của Ash là kết hợp nhiều tập dữ liệu bằng cách sử dụng Mối quan hệ và Mô hình dữ liệu.
Giả sử rằng bộ phận CNTT của bạn cung cấp cho bạn tập dữ liệu được hiển thị trong các cột A: D. Có các lĩnh vực cho khách hàng và thị trường. Bạn cần kết hợp các thị trường nhất định thành các khu vực. Mỗi khách hàng thuộc một lĩnh vực. Khu vực và Khu vực không có trong dữ liệu gốc, nhưng bạn có các bảng tra cứu để cung cấp thông tin này.
Thông thường, bạn sẽ làm phẳng dữ liệu bằng cách sử dụng hàm VLOOKUP để kéo dữ liệu từ bảng màu cam và màu vàng vào bảng màu xanh lam. Nhưng vì trường khóa không ở bên trái của mỗi bảng, bạn sẽ phải chuyển sang INDEX và MATCH, hoặc sắp xếp lại các bảng tra cứu.
Bắt đầu từ Excel 2013, bạn có thể để nguyên các bảng tra cứu và kết hợp chúng trong chính báo cáo bảng tổng hợp.
Để kỹ thuật này hoạt động, cả ba bảng phải được Định dạng dưới dạng Bảng. Chọn một ô trong mỗi tập dữ liệu và chọn Trang chủ, Định dạng dưới dạng Bảng hoặc nhấn Ctrl + T. Ba bảng ban đầu sẽ được gọi là Bảng1, Bảng2 và Bảng3. Tôi sử dụng tab Thiết kế Công cụ Bảng của Ruy-băng và đổi tên từng bảng. Tôi cũng thay đổi màu sắc của mỗi bảng. Trong ví dụ này, bảng màu xanh lam được gọi là Dữ liệu. Bảng màu cam là RegionTable. Bảng màu vàng là SectorTable.
Ghi chú
Một số người sẽ nói với bạn rằng bạn nên sử dụng những cái tên ngổ ngáo như Fact, TblSector và TblRegion. Nếu ai đó làm phiền bạn như thế này, chỉ cần lấy cắp miếng bảo vệ túi của họ và cho họ biết rằng bạn thích tên tiếng Anh hơn.
Để đổi tên bảng, hãy nhập tên mới vào hộp ở bên trái của tab Thiết kế Công cụ Bảng. Tên bảng không được có khoảng trắng.
Khi ba bảng được xác định, hãy chuyển đến tab Dữ liệu và nhấp vào Mối quan hệ.
Trong hộp thoại Quản lý mối quan hệ, bấm Mới. Trong hộp thoại Tạo mối quan hệ, chỉ định rằng trường Khách hàng của bảng Dữ liệu có liên quan đến Trường khách hàng của SectorTable. Bấm OK.
Xác định mối quan hệ mới khác giữa trường Thị trường trong trường Dữ liệu và Bảng khu vực. Sau khi xác định cả hai mối quan hệ, bạn sẽ thấy chúng trong hộp thoại Quản lý mối quan hệ.
Xin chúc mừng: bạn vừa tạo Mô hình Dữ liệu trong sổ làm việc của mình. Đã đến lúc xây dựng bảng tổng hợp.
Chọn ô trống mà bạn muốn bảng tổng hợp của mình xuất hiện. Theo mặc định, hộp thoại Tạo PivotTable sẽ chọn Sử dụng Mô hình Dữ liệu của Sổ làm việc Này. Vị trí bảng tổng hợp sẽ mặc định là ô bạn đã chọn. Bấm OK.
Danh sách Trường Pivot Table sẽ liệt kê tất cả ba bảng. Sử dụng hình tam giác ở bên trái của bảng để mở rộng tên bảng để hiển thị cho bạn các trường.
Mở rộng bảng Dữ liệu. Chọn trường Doanh thu. Nó sẽ tự động di chuyển đến khu vực Giá trị. Mở rộng SectorTable. Chọn trường Khu vực. Nó sẽ di chuyển đến khu vực Hàng. Mở rộng RegionTable. Kéo trường Vùng vào vùng Cột. Bây giờ bạn sẽ có một bảng tổng hợp tóm tắt dữ liệu từ ba bảng.
Ghi chú
Trong mỗi cuốn sách mà tôi đã viết trước đây ngày hôm nay, tôi sử dụng một kỹ thuật khác nhau để xây dựng báo cáo này. Sau khi xác định ba bảng, tôi chọn ô A1 và Chèn, Bảng tổng hợp. Tôi chọn hộp Thêm Dữ liệu Này vào Mô hình Dữ liệu. Trong danh sách Trường Bảng Tổng hợp, hãy chọn Tất cả từ đầu danh sách. Chọn các trường cho báo cáo, sau đó xác định các mối quan hệ sau thực tế. Kỹ thuật được mô tả ở trên có vẻ mượt mà hơn và thực sự liên quan đến một chút kế hoạch trước. Những người sử dụng Option Explicit trong mã VBA của họ chắc chắn sẽ thích phương pháp này.
Các mối quan hệ trong mô hình dữ liệu làm cho Excel giống như Access hoặc SQL Server hơn, nhưng với tất cả những gì tốt đẹp của Excel.
Tôi thích hỏi nhóm Excel về các tính năng yêu thích của họ. Mỗi thứ Tư, tôi sẽ chia sẻ một trong những câu trả lời của họ. Cảm ơn Ash Sharma đã cung cấp ý tưởng này.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Đừng tra cứu nếu bạn đang trong một mối quan hệ"
John Michaloudis
