Mô hình Dữ liệu Excel (Power Pivot) cho phép bạn kết nối một tập dữ liệu thực tế chi tiết lớn với ngân sách cấp cao nhất bằng cách sử dụng các bảng nối.
Ngân sách được thực hiện ở cấp cao nhất - doanh thu theo dòng sản phẩm theo khu vực theo tháng. Thực tế tích lũy chậm dần theo thời gian - hóa đơn theo hóa đơn, chi tiết đơn hàng theo chi tiết đơn hàng. So sánh tệp Ngân sách nhỏ với dữ liệu Thực tế đồ sộ mãi mãi là một khó khăn. Tôi thích thủ thuật này của Rob Collie, hay còn gọi là PowerPivotPro.com.
Để thiết lập ví dụ, bạn có một bảng ngân sách 54 hàng: một hàng mỗi tháng cho mỗi vùng trên mỗi sản phẩm.
Tệp hóa đơn ở cấp độ chi tiết: 422 hàng cho đến nay trong năm nay.
Không có hàm VLOOKUP nào trên thế giới cho phép bạn khớp hai tập dữ liệu này. Tuy nhiên, nhờ Power Pivot (hay còn gọi là Mô hình Dữ liệu trong Excel 2013+), điều này trở nên dễ dàng.
Bạn cần tạo các bảng nhỏ nhỏ mà tôi gọi là “bộ nối” để liên kết hai tập dữ liệu lớn hơn. Trong trường hợp của tôi, Sản phẩm, Khu vực và Ngày là điểm chung giữa hai bảng. Bảng Sản phẩm là một bảng bốn ô nhỏ. Ditto cho Vùng. Tạo mỗi bảng trong số đó bằng cách sao chép dữ liệu từ một bảng và sử dụng Xóa bản sao.
Bảng lịch bên phải thực sự khó tạo hơn. Dữ liệu ngân sách có một hàng mỗi tháng, luôn rơi vào cuối tháng. Dữ liệu hóa đơn hiển thị ngày hàng ngày, thường là các ngày trong tuần. Vì vậy, tôi phải sao chép trường Ngày từ cả hai tập dữ liệu vào một cột duy nhất và sau đó loại bỏ các bản sao để đảm bảo rằng tất cả các ngày đều được đại diện. Sau đó, tôi đã sử dụng =TEXT(J4,"YYYY-MM")để tạo cột Tháng từ các ngày hàng ngày.
Nếu bạn không có bổ trợ Power Pivot đầy đủ, bạn cần tạo một bảng tổng hợp từ bảng Ngân sách và chọn hộp Thêm Dữ liệu Này vào Mô hình Dữ liệu.
Như đã thảo luận trong mẹo trước, khi bạn thêm các trường vào bảng tổng hợp, bạn sẽ phải xác định sáu mối quan hệ. Trong khi bạn có thể thực hiện điều này với sáu lần truy cập vào hộp thoại Tạo mối quan hệ, tôi đã kích hoạt bổ trợ Power Pivot của mình và sử dụng dạng xem sơ đồ để xác định sáu mối quan hệ.
Đây là chìa khóa để thực hiện tất cả công việc này: Bạn có thể tự do sử dụng các trường số từ Ngân sách và từ Thực tế. Nhưng nếu bạn muốn hiển thị Khu vực, Sản phẩm hoặc Tháng trong bảng tổng hợp, chúng phải đến từ các bảng nối!
Đây là một bảng tổng hợp với dữ liệu đến từ năm bảng. Cột A đến từ bộ nối Vùng. Hàng 2 đến từ trình kết hợp Lịch. Máy cắt sản phẩm là từ bộ ghép nối Sản phẩm. Số Ngân sách lấy từ bảng Ngân sách và số Thực tế lấy từ bảng Hóa đơn.
Điều này hoạt động bởi vì các bảng nối áp dụng bộ lọc cho bảng Ngân sách và Thực tế. Đó là một kỹ thuật đẹp và cho thấy rằng Power Pivot không chỉ dành cho dữ liệu lớn.
Xem video
- Bạn có một tập dữ liệu ngân sách từ trên xuống nhỏ
- Bạn muốn so sánh với tập dữ liệu thực tế từ dưới lên
- Các hướng dẫn thực tế có thể đến từ sổ đăng ký hóa đơn
- Mô hình dữ liệu sẽ cho phép bạn so sánh các tập dữ liệu có kích thước khác nhau này
- Đặt cả hai tập dữ liệu thành một bảng Ctrl + T
- Đối với mỗi trường văn bản mà bạn muốn báo cáo, hãy tạo một bảng nối
- Sao chép các giá trị và xóa các bản sao
- Đối với ngày, bạn có thể bao gồm ngày từ cả hai bảng và chuyển đổi thành cuối tháng
- Đặt các bộ nối là bảng Ctrl + T
- Tùy chọn nhưng hữu ích để đặt tên cho tất cả năm bảng
- Tạo bảng tổng hợp từ Ngân sách và chọn Mô hình Dữ liệu
- Tạo bảng tổng hợp bằng Ngân sách và Thực tế từ các bảng gốc
- Tất cả các trường khác phải đến từ các bảng nối
- Thêm máy thái theo sản phẩm
- Tạo ba mối quan hệ từ Ngân sách đến Người tham gia
- Tạo ba mối quan hệ từ Thực tế đến Tham gia
- Ngày mai: cách xây dựng mối quan hệ dễ dàng hơn với Power Pivot và Công thức DAX
Bản ghi video
Học Excel từ podcast, tập 2016 - Ngân sách từ trên xuống so với Thực tế từ dưới lên!
Này, tôi đang podcasting toàn bộ cuốn sách này, hãy nhấp vào chữ "i" ở góc trên bên phải và theo dõi danh sách phát.
Này, tôi sẽ ngắt lời đây, đây là Bill Jelen từ 15 phút nữa. Bây giờ tôi nhận ra đây là một podcast cực kỳ dài và bạn bị cám dỗ chỉ cần nhấp chuột ngay qua nó, nhưng hãy để tôi chỉ cho bạn đoạn ngắn này. Nếu bạn đang sử dụng Excel 2013 và bạn đã từng có một bảng ngân sách nhỏ và một bảng thực tế lớn, và bạn cần phải ánh xạ chúng lại với nhau, thì đây là một khả năng mới tuyệt vời mà chúng tôi có trong Excel 2013, mà không nhiều người giải thích , và bạn có thể không biết về nó. Nếu đây là bạn, bạn đang ở năm 2013, và bạn cần lập bản đồ hai tập dữ liệu này, hãy dành thời gian, có thể hôm nay, có thể ngày mai, có thể thêm nó vào danh sách theo dõi, rất đáng giá, đó là một kỹ thuật tuyệt vời.
Được rồi, đây là những gì chúng tôi có, ở bên trái chúng tôi có ngân sách, ngân sách này, được thực hiện ở cấp cao nhất, từ trên xuống, bên phải cho từng dòng sản phẩm, cho từng khu vực, cho mỗi tháng, có một ngân sách . Không có nhiều bản ghi ở đây, số lượng là 55, ở phía bên phải, chúng tôi đang cố gắng so sánh điều này với thực tế. Các thực tế đến từ sổ đăng ký hóa đơn, vì vậy chúng tôi có Khu vực, Sản phẩm và Doanh thu, nhưng chúng là các hóa đơn riêng lẻ, nhiều dữ liệu hơn ở đây, chúng tôi đã làm được nửa năm và tôi đã có 423 bản ghi. Được rồi, vậy bạn làm cách nào để liên kết các số 55 này với 423? Có thể khó thực hiện với hàm VLOOKUP, trước tiên bạn phải tóm tắt lại, nhưng rất may trong Excel 2013, mô hình dữ liệu làm cho việc này thực sự rất dễ dàng. Những gì chúng ta cần để cho phép chiếc bàn khổng lồ to lớn này giao tiếp với chiếc bàn nhỏ bé này là những người trung gian, tôi gọi chúng là những bộ ghép nối.Các bảng nhỏ, Sản phẩm, Khu vực và Lịch, chúng ta sẽ kết hợp ngân sách cho ba bảng này, chúng ta sẽ tham gia thực tế vào ba bảng này và thật kỳ diệu là bảng Pivot sẽ hoạt động. Được rồi, đây là cách chúng tôi thực hiện điều đó.
Trước hết, tôi cần tạo các bộ nối, vì vậy tôi lấy trường Sản phẩm này từ cột A, và tôi sao chép nó sang cột F, rồi đến Dữ liệu, Loại bỏ các bản sao, nhấp vào OK, và chúng ta còn lại với một bảng nhỏ nhỏ, 1 đề mục 3 hàng. Tương tự đối với Vùng, lấy các vùng, Ctrl + C, chuyển đến cột G, Dán, Loại bỏ Bản sao, bấm OK, 3 hàng 1 tiêu đề, được rồi. Bây giờ đối với các ngày, các ngày không giống nhau, đây là những ngày kết thúc tháng, chúng thực sự được lưu trữ dưới dạng ngày kết thúc tháng và đây là những ngày trong tuần. Tôi sẽ lấy cả hai danh sách, Ctrl + C danh sách thứ hai và dán nó vào đây, Ctrl + V, sau đó tôi sẽ lấy danh sách ngắn hơn, sao chép nó và dán nó xuống bên dưới, được rồi. Và thực sự khó chịu rằng, mặc dù chúng được lưu trữ dưới dạng ngày tháng, chúng xuất hiện dưới dạng tháng và Xóa các bản sao sẽ không thấy chúng giống nhau.Vì vậy, trước khi sử dụng Remove Duplicates, tôi phải thay đổi nó thành một ngày ngắn. Chọn dữ liệu đó, Dữ liệu, Xóa bản sao, bấm OK, sau đó sắp xếp một chút ở đây để nó hoạt động.
Được rồi, bây giờ tôi không muốn báo cáo theo ngày hàng ngày, vì vậy tôi sẽ thêm một cột ở đây, một cột tra cứu cho biết Tháng và giá trị này sẽ bằng EOMONTH vào ngày đó, 0, sẽ giúp chúng ta cuối tháng. Nó sẽ định dạng đó là ngày ngắn, và sao chép nó xuống, được thôi. Bây giờ, chúng ta cần làm cho mỗi cái này thành một bảng Ctrl + T, vì vậy từ đây Ctrl + T, Bảng của tôi có tiêu đề, đẹp. Những cái nhỏ, nó không nhận ra đó là những tiêu đề ở trên đó, vì vậy chúng tôi phải đảm bảo đánh dấu kiểm đó và Ctrl + T, được rồi, và họ gọi các bảng này là Table1, Table2, Table3, những cái tên thực sự nhàm chán đúng không? Vì vậy, tôi sẽ đổi tên chúng và gọi nó là BudTable, ProdTable, RegTable, CalTable của tôi và sau đó là ActTable, được rồi.
Chúng tôi bắt đầu từ bảng đầu tiên và nhân tiện chúng tôi sẽ không sử dụng PowerPivot ngày hôm nay, chúng tôi sẽ thực hiện tất cả điều này với mô hình dữ liệu. Vì vậy, Excel 2013 hoặc mới hơn, bạn có Chèn này, PivotTable, chúng tôi sẽ chọn hộp cho “Thêm dữ liệu này vào Mô hình Dữ liệu”, nhấp vào OK và chúng tôi nhận được danh sách trường của mình với nút Tất cả kỳ diệu, điều đó cho phép tôi chọn từ tất cả năm bảng trong sổ làm việc, Thực tế, Ngân sách, Lịch, Sản phẩm, Khu vực. Được rồi, vì vậy các con số sẽ đến từ bảng Ngân sách, tôi sẽ đặt ngân sách vào đó và từ bảng Thực tế, tôi sẽ đưa thực tế vào đó, nhưng sau đó đây là điều cho phần còn lại của bảng Tổng hợp. Bất kỳ trường văn bản nào khác mà chúng ta sẽ đặt trong vùng hàng hoặc vùng cột hoặc dưới dạng bộ cắt, chúng phải đến từ các bộ nối, chúng phải đến từ các bảng đó giữa các bảng.
Được rồi, vì vậy từ bảng Lịch, chúng tôi sẽ lấy trường Tháng đó và đặt nó ở trên cùng, chúng tôi sẽ bỏ qua các mối quan hệ khác ngay bây giờ. Tôi sẽ tạo ra các mối quan hệ, nhưng tôi muốn tạo tất cả chúng cùng một lúc. Và bảng Vùng, hãy đặt các vùng xuống bên cạnh. Tôi có thể đặt các sản phẩm xuống một bên, nhưng thực sự tôi sẽ sử dụng bảng Sản phẩm như một bộ cắt, vì vậy Phân tích, Chèn Bộ cắt, một lần nữa bạn phải chuyển đến Tất cả nếu bạn chưa sử dụng bảng Sản phẩm. Vì vậy, hãy chuyển đến Tất cả, và bạn sẽ thấy rằng Sản phẩm có sẵn để tạo dưới dạng máy cắt từ các sản phẩm, như vậy. Được rồi, tại thời điểm này chúng tôi chưa tạo mối quan hệ nên tất cả những con số này đều sai. Và các mối quan hệ chúng ta phải tạo ra, chúng ta phải tạo 3 bảng từ bảng ngân sách nhỏ này, một bảng cho sản phẩm, một cho các vùng, một cho lịch,đó là 3 mối quan hệ. Và sau đó chúng ta phải tạo mối quan hệ từ bảng Thực tế đến vùng Sản phẩm trong Lịch, như vậy tổng cộng là 6 bảng. Và có, điều này chắc chắn sẽ dễ dàng hơn nếu chúng ta có PowerPivot, nhưng chúng ta không có hoặc giả sử chúng ta không có.
Và vì vậy tôi sẽ sử dụng cách cổ điển, cuộc đối thoại Tạo ở đây, nơi chúng ta có bảng Ngân sách ở bên trái và chúng ta sẽ sử dụng trường Khu vực và liên kết nó với bảng Khu vực, trường Khu vực . Được rồi, 1/6 đã được tạo. Tôi sẽ chọn Tạo, một lần nữa từ bảng Ngân sách, chúng tôi chuyển đến Sản phẩm, sau đó liên kết bảng đó với bảng Sản phẩm, với Sản phẩm, bấm OK. Từ bảng Ngân sách, trường Ngày, chúng ta chuyển đến bảng Lịch và trường Số phận, nhấp vào OK, chúng ta đã đến được nửa chặng đường rồi. Từ bảng Thực tế, chúng tôi chuyển đến Khu vực, đến bảng Khu vực, nhấp vào OK, từ bảng Thực tế đến Sản phẩm và từ bảng Thực tế đến Lịch. Tôi thực sự sẽ lấy Giá trị và làm cho nó đi xuống một bên, được thôi. Thiết kế, Bố cục Báo cáo, Hiển thị trong Biểu mẫu Bảng để có chế độ xem mà tôi thích, Lặp lại Tất cả các Nhãn Mục, được rồi,điều này là hoàn toàn tuyệt vời! Bây giờ chúng ta có một bảng nhỏ bé nhỏ này, một số bản ghi có 50 trong bảng hàng trăm bản ghi này và chúng ta đã tạo một bảng Pivot duy nhất nhờ Mô hình Dữ liệu. Đối với mỗi nơi chúng ta có thể thấy Ngân sách, chúng ta có thể thấy Doanh thu, được chia nhỏ theo Khu vực, chia nhỏ theo Tháng và có thể chia nhỏ theo Sản phẩm.
Bây giờ khái niệm này đến với tôi từ Rob Collie, người điều hành Power Pivot Pro, và Rob đã tạo ra rất nhiều cuốn sách trên mạng, cuốn mới nhất của anh ấy là “Power Pivot và Power BI”. Tôi nghĩ cái này thực sự có trong cuốn sách “Power Pivot Alchemy”, đó là cuốn mà tôi đã thấy cái này và tôi đã nói “Chà cái này, mặc dù tôi không có hàng triệu hàng để báo cáo qua Power Pivot, đây là một trong đó sẽ đã tạo ra sự khác biệt LỚN trong cuộc đời tôi, có hai tập dữ liệu có kích thước không khớp và cần phải báo cáo từ cả hai. ” Chà ví dụ này và nhiều ví dụ khác có trong cuốn sách này, cuối cùng tôi sẽ nhận được toàn bộ podcast của cuốn sách, có vẻ như sẽ mất hai tháng rưỡi. Nhưng bạn có thể nhận toàn bộ cuốn sách ngay hôm nay, đồng thời, đến đó, mua cuốn sách, $ 10 cho sách điện tử, $ 25 cho sách in và bạn có thể có tất cả các mẹo đó cùng một lúc.
Được rồi, một tập thực sự dài ở đây: chúng tôi có một Ngân sách nhỏ từ trên xuống và từ dưới lên Thực tế, chúng có kích thước khác nhau, nhưng sử dụng mô hình dữ liệu trong Excel 2013… Và nhân tiện, nếu bạn đang ở năm 2010, bạn có thể , về lý thuyết, hãy thực hiện việc này bằng cách tải bổ trợ Power Pivot và thực hiện tất cả các bước này trở lại vào năm 2010. Đặt cả hai tập dữ liệu thành một bảng Ctrl + T, sau đó nối các bảng của bạn cho bất kỳ thứ gì bạn muốn báo cáo, trong nhãn hàng hoặc nhãn cột hoặc các bộ cắt, vì vậy hãy sao chép các giá trị đó và Xóa các phần trùng lặp cho các ngày. Tôi thực sự đã lấy các giá trị từ cả hai bảng, bởi vì có một số giá trị duy nhất trong mỗi bảng và sau đó tôi sử dụng EOMONTH để ra khỏi đó, làm cho các bảng nối đó trở thành bảng được điều khiển. Nó là tùy chọn, nhưng tôi đặt tên cho tất cả 5 bảng, vì dễ dàng hơn khi bạn thiết lập các mối quan hệ đó, thay vì được gọi là Table1,Bảng2, Bảng3.
Và do đó, hãy bắt đầu từ bảng Ngân sách, Chèn, PivotTable, chọn hộp cho Mô hình Dữ liệu, rồi tạo bảng Pivot bằng Ngân sách và Thực tế. Mọi thứ khác đều đến từ các bảng nối, vì vậy Vùng và Tháng trong vùng hàng và cột, các bộ cắt đến từ bảng Sản phẩm. Và sau đó chúng tôi phải tạo 3 mối quan hệ từ Ngân sách đến các bộ kết hợp, 3 mối quan hệ từ Thực tế đến các bộ kết hợp và chúng ta có một bảng Pivot tuyệt vời. Bây giờ, ngày mai chúng ta sẽ xem xét việc sử dụng tab Power Pivot và tạo một số phép tính bổ sung. Vì vậy, tất cả những điều này đều có thể thực hiện được, đó là khi chúng tôi muốn chèn một trường được tính toán, đó là khi bạn phải trả thêm $ 2 một tháng để có được phiên bản Office 365 Pro Plus.
Xin chào, cảm ơn Rob Collie từ Power Pivot Pro về mẹo này và cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp lại bạn lần sau với một netcast khác từ!
Tải tập tin
Tải file mẫu tại đây: Podcast2016.xlsx
