Bạn có ghét hàm GETPIVOTDATA của Excel không? Tại sao nó xuất hiện? Làm thế nào bạn có thể ngăn chặn nó? GETPIVOTDATA có sử dụng tốt không?
Hầu hết mọi người lần đầu tiên gặp phải GETPIVOTDATA khi họ cố gắng xây dựng công thức bên ngoài bảng tổng hợp sử dụng các số trong bảng tổng hợp. Ví dụ: phần trăm phương sai này sẽ không sao chép xuống các tháng khác do Excel chèn các hàm GETPIVOTDATA.
Excel sẽ chèn GETPIVOTDATA bất cứ khi nào bạn sử dụng chuột hoặc phím mũi tên để trỏ đến một ô bên trong bảng tổng hợp trong khi xây dựng công thức bên ngoài bảng tổng hợp.
Nhân tiện, nếu bạn không muốn hàm GETPIVOTDATA xuất hiện, chỉ cần nhập một công thức chẳng hạn như = D5 / C5-1 mà không cần sử dụng chuột hoặc các phím mũi tên để trỏ đến các ô. Công thức đó sao chép mà không có bất kỳ vấn đề nào.
Đây là tập dữ liệu chứa một số gói mỗi tháng cho mỗi cửa hàng. Ngoài ra còn có doanh số bán hàng thực tế mỗi tháng cho mỗi cửa hàng cho những tháng đã hoàn thành. Mục tiêu của bạn là xây dựng một báo cáo hiển thị thực tế cho những tháng đã hoàn thành và lập kế hoạch cho những tháng tương lai.
Tạo bảng tổng hợp với Store in ROWS. Đặt Tháng và Nhập các CỘT. Bạn nhận được báo cáo được hiển thị bên dưới, với Thực tế tháng 1, Kế hoạch tháng 1 và Kế hoạch + Thực tế tháng 1 hoàn toàn vô nghĩa.
Nếu bạn chọn một ô tháng và đi tới Cài đặt Trường, bạn có thể thay đổi tổng phụ thành Không có.
Điều này sẽ loại bỏ Kế hoạch + Thực tế vô dụng. Nhưng bạn vẫn phải loại bỏ các cột kế hoạch từ tháng Giêng đến tháng Tư. Không có cách nào tốt để thực hiện việc này bên trong bảng xoay.
Vì vậy, quy trình làm việc hàng tháng của bạn trở thành:
- Thêm các thông tin thực tế cho tháng mới vào tập dữ liệu.
- Xây dựng một bảng tổng hợp mới từ đầu.
- Sao chép bảng tổng hợp và dán dưới dạng các giá trị để nó không phải là bảng tổng hợp nữa.
- Xóa các cột mà bạn không cần.
Có một con đường tốt hơn để đi. Hình rất nhỏ sau đây cho thấy một trang tính Excel mới được thêm vào sổ làm việc. Đây là tất cả chỉ là Excel thẳng, không có bảng tổng hợp. Điều kỳ diệu duy nhất là một hàm IF trong hàng 4 chuyển từ Thực tế sang Kế hoạch dựa trên ngày trong ô P1.
Ô đầu tiên cần được điền là tháng 1, Thực tế cho Baybrook. Bấm vào ô đó và nhập dấu bằng. Sử dụng chuột, điều hướng trở lại bảng tổng hợp. Tìm ô cho Thực tế tháng Giêng cho Baybrook. Nhấp vào ô đó và nhấn Enter. Như thường lệ, Excel xây dựng một trong những hàm GETPIVOTDATA phiền phức không thể sao chép được.
Nhưng hôm nay, chúng ta hãy nghiên cứu cú pháp của GETPIVOTDATA.
Đối số đầu tiên bên dưới là trường số "Bán hàng". Đối số thứ hai là ô chứa bảng tổng hợp. Các cặp đối số còn lại là tên trường và giá trị. Bạn có thấy công thức được tạo tự động đã làm gì không? Nó được mã hóa cứng "Baybrook" làm tên của cửa hàng. Đó là lý do tại sao bạn không thể sao chép các công thức GETPIVOTDATA được tạo tự động này. Họ thực sự mã hóa tên khó thành công thức. Mặc dù bạn không thể sao chép các công thức này, nhưng bạn có thể chỉnh sửa chúng. Trong trường hợp này, sẽ tốt hơn nếu bạn chỉnh sửa công thức để trỏ đến ô $ D6.
Đây là công thức sau khi bạn chỉnh sửa nó. Đã qua rồi "Baybrook", "Jan" và "Thực tế". Thay vào đó, bạn đang trỏ đến $ D6, E $ 3, E $ 4.
Sao chép công thức này và sau đó chọn Dán công thức đặc biệt trong tất cả các ô số khác.
Bây giờ đây là quy trình làm việc hàng năm của bạn:
- Xây dựng một bảng tổng hợp xấu xí mà không ai có thể nhìn thấy.
- Thiết lập trang tính báo cáo.
Mỗi tháng, bạn phải:
- Dán các hướng dẫn thực tế mới bên dưới dữ liệu.
- Làm mới bảng tổng hợp xấu xí.
-
Thay đổi ô P1 trên bảng báo cáo để phản ánh tháng mới. Tất cả các số cập nhật.
Thay đổi ô P1
Bạn phải thừa nhận rằng việc sử dụng một báo cáo đơn giản lấy các số từ bảng tổng hợp mang lại cho bạn điều tốt nhất của cả hai thế giới. Bạn có thể tự do định dạng báo cáo theo những cách mà bạn không thể định dạng bảng tổng hợp. Hàng trống cũng được. Bạn có thể có ký hiệu tiền tệ trên hàng đầu tiên và hàng cuối cùng nhưng không được ở giữa. Bạn cũng nhận được gạch chân kép dưới tổng số.
Cảm ơn @iTrainerMX đã đề xuất tính năng này.
Xem video
- GetPivotData xảy ra khi một công thức trỏ vào bên trong bảng tổng hợp
- Trong khi công thức ban đầu là đúng, bạn không thể sao chép công thức
- Hầu hết mọi người ghét getpivotdata và muốn ngăn chặn nó
- Phương pháp 1: Xây dựng công thức mà không cần chuột hoặc phím mũi tên
- Phương pháp 2: Tắt GetPivotData vĩnh viễn bằng menu thả xuống bên cạnh các tùy chọn
- Nhưng có một cách sử dụng cho GetPivotData
- Người quản lý của bạn muốn có một báo cáo với Thực tế các tháng trước và ngân sách cho tương lai
- Quy trình làm việc bình thường sẽ có bạn tạo bảng tổng hợp, chuyển đổi thành giá trị, xóa cột
- Xóa Tổng phụ để ngăn Thực tế tháng 1 + Kế hoạch sử dụng Cài đặt trường
- Thay vào đó, hãy tạo một bảng tổng hợp có dữ liệu "quá nhiều"
- Sử dụng trang tính báo cáo được định dạng độc đáo
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Từ ô dữ liệu đầu tiên trên trang tính, hãy xây dựng công thức bằng chuột
- Cho phép GetPivotData xảy ra
- Kiểm tra cú pháp của GetPivotData (trường trả về, vị trí tổng hợp, cặp)
- Thay đổi giá trị được mã hóa cứng để trỏ đến một ô
- Nhấn F4 ba lần chỉ khóa cột
- Nhấn F4 hai lần chỉ khóa hàng
- Dán các công thức đặc biệt
- Quy trình làm việc vào tháng tới: Thêm dữ liệu, làm mới bảng tổng hợp, thay đổi qua ngày
- Cực kỳ cẩn thận để xem các cửa hàng mới
Bản chuyển biên của video
Học Excel từ podcast, tập 2013 - GetPivotData có thể không hoàn toàn xấu xa!
Tôi sẽ 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 để đăng ký.
Được rồi, trở lại tập 1998, tôi đã nói ngắn gọn về vấn đề GetPivotData này. Nếu chúng tôi tính toán phương sai% và chúng tôi đang ở ngoài bảng Pivot, trỏ vào bên trong và tôi sử dụng chuột hoặc phím mũi tên, thì 2019 / 2018-1. Câu trả lời mà chúng ta sẽ nhận được ở đây là chính xác cho tháng Giêng, nhưng khi chúng tôi nhấp đúp để sao chép nó xuống, công thức không sao chép, chúng tôi nhận được câu trả lời của tháng Giêng. Và khi chúng tôi xem xét nó, chúng tôi đang nhận được GetPivotData, tôi không nhập GetPivotData, tôi chỉ trỏ vào các ô đó và điều này bắt đầu xảy ra trở lại trong Excel 2002 mà không có bất kỳ cảnh báo nào. Và tại thời điểm đó, tôi đã nói rằng cách để tránh điều này là gõ công thức C5 / B5-1, và bạn sẽ nhận được một công thức mà bạn có thể sao chép. Hoặc nếu bạn chỉ ghét GetPivotData, nếu nó "hoàn toàn xấu xa", hãy chuyển đến tab Phân tích, đừng 'nhân tiện, không mở nút Tùy chọn. Quay lại bảng Pivot, chuyển đến tab Phân tích, mở menu thả xuống bên cạnh Tùy chọn, bỏ chọn hộp này, đó là cài đặt chung. Một khi bạn tắt nó đi, nó sẽ tắt vĩnh viễn, được thôi.
Hầu hết các câu hỏi tôi nhận được là "Làm cách nào để tắt GetPivotData?" nhưng thỉnh thoảng tôi sẽ có được một người yêu thích GetPivotData. Và tôi đã ăn trưa với Rob Collie khi anh ấy vẫn còn ở Microsoft, và anh ấy nói “Chà, khách hàng nội bộ của chúng tôi yêu thích GetPivotData.” Tôi đã nói những gì? Không, mọi người đều ghét GetPivotData! ” Rob nói “Bạn nói đúng, ngoài Microsoft, họ hoàn toàn ghét GetPivotData.” Tôi đang nói về các kế toán viên bên trong Microsoft, và sau đó tôi gặp một người hiện đang làm việc cho nhóm Excel, Carlos, và Carlos là một trong những kế toán sử dụng phương pháp này.
Được rồi, đây là những gì chúng ta phải làm. Chúng tôi có báo cáo của mình, một tập dữ liệu ở đây mà hàng tháng, chúng tôi có kế hoạch cho từng cửa hàng và sau đó ở phần cuối, chúng tôi đang tích lũy thực tế. Được rồi, chúng tôi có thực tế cho tháng 1 đến tháng 12, nhưng chỉ có thực tế cho một vài tháng, những tháng đã qua. Và những gì người quản lý của chúng tôi muốn chúng tôi làm là xây dựng một báo cáo với các cửa hàng ở phía bên trái, tất nhiên là chỉ có các cửa hàng ở Texas, để làm cho cuộc sống khó khăn hơn. Và sau đó, chúng ta có các tháng, và nếu chúng ta có một thực tế cho tháng đó, chúng tôi hiển thị thực tế, vì vậy thực tế tháng Giêng, thực tế tháng hai, thực tế tháng ba, thực tế tháng tư. Nhưng sau đó đối với những tháng mà chúng tôi không có thông tin thực tế, chúng tôi sẽ chuyển sang và hiển thị ngân sách, vì vậy hãy lập ngân sách cho đến hết tháng 12 và sau đó tổng cộng mọi thứ, được rồi. Vâng, khi bạn thử và tạo bảng Pivot này,nó không hoạt động.
Vì vậy, chèn PivotTable, Bảng tính mới, bạn đặt Cửa hàng xuống bên trái, bên đó đẹp, đặt Tháng ở trên cùng, đặt Loại ở trên cùng, đặt Bán hàng ở đây, được rồi. Vì vậy, đây là những gì chúng tôi nhận được mà chúng tôi phải bắt đầu làm việc, vì vậy chúng tôi có kế hoạch thực tế tháng 1, kế hoạch tháng 1 và sau đó là kế hoạch cộng thực tế tháng 1 hoàn toàn vô dụng. Sẽ không có ai sử dụng cái này, nhưng tôi có thể loại bỏ các cột màu xám này, điều đó đủ dễ dàng, một số ở đây cho ô này, đi tới Cài đặt Trường và thay đổi Tổng phụ thành Không có. Nhưng hoàn toàn không có cách nào để tôi xóa bỏ kế hoạch tháng Giêng mà cũng sẽ không xóa kế hoạch tháng Tư, tháng Năm, tháng sáu, tháng Bảy, được rồi, không có cách nào để loại bỏ điều này. Vì vậy, tại thời điểm này hàng tháng, tôi bị mắc kẹt khi chọn toàn bộ bảng Pivot, vào Sao chép, rồi Dán, Dán Giá trị. Nó không phải là một bảng Pivot nữa,và sau đó tôi bắt đầu xóa thủ công các cột không xuất hiện trong báo cáo.
Được rồi, đó là phương pháp bình thường, nhưng các nhân viên kế toán tại Microsoft đã thêm một bước vào tháng 1, mất 15 phút và bước này cho phép bảng Pivot này tồn tại mãi mãi, phải không? Tôi gọi đây là bảng Pivot xấu nhất thế giới và các kế toán tại Microsoft chấp nhận rằng đây là bảng Pivot xấu nhất thế giới, nhưng sẽ không có ai nhìn thấy báo cáo này ngoại trừ họ. Những gì họ làm, là họ đến đây để xem một trang tính mới và xây dựng báo cáo mà người quản lý của họ muốn. Được rồi, đây là các cửa hàng ở phía bên tay trái, tôi thậm chí đã nhóm nó thành Houston, Dallas và Other, đó là một báo cáo được định dạng độc đáo. Tôi đã đánh dấu các tổng số, bạn sẽ thấy rằng khi chúng tôi lấy một số con số, sẽ có đơn vị tiền tệ ở hàng đầu tiên, nhưng không có các hàng tiếp theo này, các hàng trống. Ồ, các hàng trống trong bảng Tổng hợp.Và một chút logic nhỏ ở đây, nơi tôi có thể đặt ngày thông qua ô P1 và sau đó tôi có một công thức ở đây phân tích rằng NẾU tháng của ngày thông qua là> cột này, và sau đó đặt từ Thực tế, nếu không đặt từ Kế hoạch, được rồi. Vì vậy, tất cả những gì tôi phải làm là thay đổi điều này qua ngày tháng, và sau đó từ Thực tế chuyển sang lập kế hoạch, Được rồi.
Bây giờ, đây là những gì chúng tôi làm, chúng tôi sẽ cho phép mình trở thành GetPivotData'd, phải không? Tôi không chắc đó là động từ, nhưng chúng tôi sẽ cho phép Microsoft sử dụng GetPivotData. Vì vậy, tôi bắt đầu xây dựng công thức với dấu =, tôi lấy con chuột và tôi sẽ đi tìm Baybrook thực tế của tháng Giêng! Vì vậy, tôi quay lại bảng Pivot xấu xí nhất thế giới, tôi tìm Baybrook, tôi tìm tháng Giêng, tôi tìm thấy thực tế, và tôi nhấp vào Enter, và để họ làm điều đó với tôi, được rồi, chúng ta bắt đầu, bây giờ chúng ta có một công thức GetPivotData. Tôi nhớ cái ngày mà tôi làm việc này, giống như bạn biết đấy, sau khi Rob giải thích cho tôi những gì họ đang làm, và tôi đã quay lại và thử nó. Bây giờ đột nhiên, trong suốt cuộc đời tôi, tôi đã thoát khỏi GetPivotData, tôi chưa bao giờ thực sự chấp nhận GetPivotData. Vì vậy, nó là gì, mục đầu tiên là thứ chúng tôi đang tìm kiếm, ở đó 'sa trường được gọi là Bán hàng, đây là nơi bắt đầu bảng Pivot và nó có thể là bất kỳ ô nào trong bảng Pivot, họ sử dụng phía trên bên trái.
Được rồi, đây là tên trường "Cửa hàng", sau đó họ đã mã hóa cứng "Baybrook", đây là tên trường "Tháng", họ đã mã hóa "Tháng 1", đây là tên trường "Loại", và họ ' đã mã hóa cứng "Thực tế". Đó là lý do tại sao bạn không thể sao chép nó, bởi vì họ đã mã hóa các giá trị. Nhưng các kế toán viên tại Microsoft, Carlos và các đồng nghiệp của anh ấy nhận ra rằng “Ồ, chờ một chút, chúng tôi có từ Baybrook ở đây, chúng tôi có tháng Giêng ở đây, chúng tôi có Thực tế ở đây. Chúng tôi chỉ cần thay đổi công thức này để trỏ đến các ô thực tế trong báo cáo thay vì được mã hóa cứng ”. Được rồi, họ gọi điều này là tham số hóa GetPivotData.
Bỏ từ Baybrook, đến đây và nhấp vào ô D6. Bây giờ, tôi cần phải khóa nó xuống cột, được rồi, vì vậy tôi nhấn phím F4 3 lần, nhận một $ duy nhất trước chữ D, được rồi. Đối với tháng 1, tôi xóa mã cứng tháng Giêng, tôi nhấp vào ô E3, tôi sẽ nhấn F4 hai lần để khóa nó xuống hàng, E $ 3. Nhập Thực tế, xóa từ Thực tế, nhấp vào E4, lại F4 hai lần, được rồi, và tôi nhận được một công thức bây giờ kéo dữ liệu đó trở lại. Tôi sẽ sao chép nó, và sau đó Dán Đặc biệt, chọn Định dạng, alt = "" ESF, thấy F được gạch dưới ở đó, ESF Enter, và sau đó, bây giờ, tôi đã làm xong và tôi sẽ chỉ lặp lại với F4, F4 là làm lại và F4. Được rồi, bây giờ chúng ta có một báo cáo đẹp mắt, có khoảng trống, có định dạng, có gạch chân kế toán duy nhất dưới mỗi phần,ở dưới cùng, nó có gạch chân kế toán kép.
Đúng vậy, bạn không bao giờ nhận được nội dung này trong bảng Pivot, điều đó là không thể, nhưng báo cáo này được điều khiển từ bảng Pivot. Vì vậy, sau đó những gì chúng tôi làm khi chúng tôi nhận được các thực tế của tháng 5, quay lại đây, dán chúng vào, làm mới bảng Pivot xấu xí nhất thế giới và sau đó tại đây trên báo cáo chỉ cần thay đổi ngày thông qua từ 4/30 thành 5/31. Và điều đó làm là khiến công thức này chuyển từ từ Kế hoạch sang Thực tế, sẽ kéo các thực tế từ báo cáo, thay vì kế hoạch, được rồi. Bây giờ, đây là điều - đây là một điều tuyệt vời, phải không? Tôi có thể thấy nơi tôi sẽ làm điều này rất nhiều nếu tôi vẫn làm việc trong lĩnh vực kế toán.
Điều bạn phải thực sự cẩn thận là nếu họ xây dựng một cửa hàng mới, bạn phải biết cách thêm nó vào theo cách thủ công, đúng không, dữ liệu sẽ hiển thị trong bảng Pivot, nhưng bạn sẽ thêm nó theo cách thủ công. Bây giờ cái này là một tập hợp con của tất cả các cửa hàng, nếu nó báo cáo tất cả các cửa hàng, tôi có thể sẽ ra đây, ngoài phạm vi in, có một cái gì đó đã lấy tổng số từ bảng Pivot. Và sau đó tôi sẽ biết, nếu tổng số này không khớp với tổng số từ bảng Pivot, thì đã xảy ra lỗi và có hàm IF ở đây Nói “Này, bạn biết đấy, dữ liệu mới đã được thêm vào, hãy rất cẩn thận. ” Họ có một số loại cơ chế để phát hiện dữ liệu mới ở đó. Nhưng tôi hiểu, đó là một công dụng tuyệt vời. Vì vậy, mặc dù phần lớn thời gian GetPivotData chỉ khiến chúng ta phát điên, nhưng thực sự có thể có một công dụng cho nó. Ổn thỏa,vì vậy đó là mẹo # 21 trên 40 trong cuốn sách, hãy mua sách ngay bây giờ, đặt hàng trực tuyến, nhấp vào chữ “i” ở góc trên bên phải.
Hôm nay tóm tắt dài, được rồi: GetPivotData xảy ra khi một công thức trỏ vào bên trong bảng Pivot, một công thức bên ngoài bảng Pivot trỏ vào bên trong. Mặc dù công thức ban đầu là đúng, nó sẽ không sao chép. Hầu hết mọi người ghét GetPivotData và muốn ngăn chặn nó. Vì vậy, bạn có thể xây dựng một công thức mà không cần chuột hoặc các phím mũi tên, chỉ cần nhập công thức hoặc tắt GetPivotData vĩnh viễn, ah, nhưng có một cách sử dụng, được thôi. Vì vậy, chúng tôi phải xây dựng một báo cáo với thực tế cho tháng trước, ngân sách cho tương lai. Dòng công việc bình thường, tạo bảng Pivot, chuyển đổi thành giá trị, Xóa cột. Có một cách để loại bỏ tổng phụ bằng cách sử dụng Cài đặt trường, loại bỏ kế hoạch cộng thực tế của tháng Giêng đó. Thay vào đó, chúng tôi sẽ tạo một bảng Pivot xấu xí nhất thế giới với quá nhiều dữ liệu.
Xây dựng một bảng tính báo cáo cũ, có định dạng độc đáo và có thể có một chút logic để thay đổi từ Thực tế thành Kế hoạch. Và sau đó từ ô báo cáo đầu tiên, nơi đầu tiên mà các số sẽ có trong báo cáo đó, hãy nhập dấu =, đi đến bảng Pivot và cho phép GetPivotData xảy ra. Chúng tôi kiểm tra cú pháp của GetPivotData, vì vậy đó là trường trả về, Bán hàng, nơi đặt bảng Pivot và sau đó là các cặp tiêu chí, tên trường và giá trị. Chúng ta sẽ xóa giá trị mã hóa cứng và trỏ đến một ô, nhấn F4 3 lần chỉ khóa cột, nhấn F4 2 lần chỉ khóa hàng, sao chép công thức đó, Dán công thức đặc biệt. Tôi đã đưa ra một mẹo bổ sung rằng F4 là một bản làm lại, vì vậy tôi chỉ phải đi tới hộp thoại Dán Đặc biệt một lần, và sau đó cho Dán Công thức Đặc biệt tiếp theo chỉ sử dụng F4. Tháng sau hãy thêm dữ liệu,làm mới bảng Tổng hợp, thay đổi ngày thông qua. Hãy chắc chắn rằng họ không xây dựng bất kỳ cửa hàng mới nào, bạn biết đấy, có một số loại cơ chế, hoặc thủ công hoặc công thức kiểm tra, hãy kiểm tra. Cảm ơn iTrainerMX trên Twitter, người đã đề xuất GetPivotData, cũng là Carlos và Rob từ Microsoft, Rob bây giờ từ Power Pivot Pro. Carlos đã sử dụng cái này, và Rob đã nói với tôi rằng Carlos đã sử dụng nó, tôi đã gặp Carlos sau đó, và anh ấy xác nhận là có, anh ấy là một trong những kế toán đã sử dụng cái này mọi lúc tại Microsoft, được rồi, bạn xem nhé.và Rob đã nói với tôi rằng Carlos đã sử dụng nó, tôi đã gặp Carlos sau đó, và anh ấy xác nhận là có, anh ấy là một trong những kế toán đã sử dụng nó mọi lúc ở Microsoft, được rồi, bạn xem nhé.và Rob đã nói với tôi rằng Carlos đã sử dụng nó, tôi đã gặp Carlos sau đó, và anh ấy xác nhận là có, anh ấy là một trong những kế toán đã sử dụng nó mọi lúc ở Microsoft, được rồi, bạn xem nhé.
Ồ, tôi muốn 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 xuống tệp mẫu tại đây: Podcast2013.xlsx
