Cách hiển thị doanh số bán hàng từ đầu tháng đến nay trong bảng tổng hợp. Đây là một tập Dueling Excel.
Xem video
- Phương pháp của Bill
- Thêm ô trợ giúp với công thức MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))- Thêm trường đó làm Slicer trong đó = True
- Mẹo thưởng: Nhóm Ngày hàng ngày lên đến Năm
- Thêm phép tính bên ngoài bảng tổng hợp trong khi tránh GetPivotData
- Cách tiếp cận của Mike:
- Chuyển dữ liệu thành bảng bằng Ctrl + T. Điều này cho phép nhiều dữ liệu hơn được thêm vào bảng và cập nhật công thức.
- SUMIFS với các hàm DATE, MONTH, DAY
- Nhấn F4 ba lần sẽ khóa tham chiếu đến chỉ cột.
- Chú ý - nếu bạn kéo công thức Bảng sang ngang, các cột sẽ thay đổi. Sao chép & Dán - không có vấn đề gì
- Sử dụng TEXT (ngày tháng, định dạng. Mẹo hay với 1 để chèn số 1 vào văn bản
Bản ghi video
Bill Jelen: Này, chào mừng trở lại. Đã đến lúc cho một Dueling Excel Podcast khác. Tôi là Bill Jelen đến từ. Tôi sẽ được tham gia bởi Mike Girvin từ Excel Is Fun.
Đây là Tập 181 của chúng tôi: Bảng tổng hợp từ tháng đến nay.
Chà, này, câu hỏi của ngày hôm nay - ý tưởng hôm nay cho trận đấu này được gửi bởi Mike. Anh ấy nói, "Bạn có thể tạo báo cáo Tháng đến nay trong bảng tổng hợp không?"
Được rồi, đi thôi. Vì vậy, đây là những gì chúng ta có, chúng ta có khoảng thời gian hai năm kể từ tháng 1 năm 2016 cho đến năm 2017. Tất nhiên bây giờ tôi đang ghi âm điều này vào tháng 4, bây giờ là ngày 15 tháng 4 khi tôi thu âm phần đấu của mình. Và ở đây, chúng tôi có một bảng tổng hợp hiển thị Ngày ở bên trái, Danh mục ở trên cùng và Doanh thu ở trung tâm của bảng tổng hợp.
Bây giờ, để tạo báo cáo Tháng cho đến nay, điều tôi sẽ làm là tôi sẽ nói rằng tôi sẽ thêm cột trợ giúp mới ở đây vào dữ liệu ban đầu của mình và điều đó sẽ kiểm tra hai điều. Và bởi vì tôi đang kiểm tra hai điều mà tôi sẽ sử dụng hàm AND, nên cả hai điều này đều phải Đúng để nó là Tháng đến nay. Và tôi sẽ sử dụng một hàm ở đây có tên là TODAY. TODAY, được rồi, vì vậy tôi muốn biết liệu THÁNG CỦA NGÀY HÔM NAY ()) = với THÁNG của ngày đó ở Cột A. Nếu đúng, nếu đó là tháng hiện tại, hay nói cách khác nếu đó là tháng Tư, thì kiểm tra và xem liệu ngày của ngày đó trong A2 có phải là <= NGÀY HÔM NAY không. Điều tuyệt vời là khi chúng ta mở sổ làm việc này vào ngày mai hoặc một tuần kể từ bây giờ, ngày hôm nay sẽ tự động cập nhật và chúng ta nhấp đúp để sao chép nó xuống.
Được rồi, bây giờ chúng ta phải lấy thêm dữ liệu này vào bảng tổng hợp của mình, vì vậy tôi đến đây Bảng tổng hợp, Phân tích và không khó để thay đổi nguồn dữ liệu, chỉ cần nhấp vào nút lớn ở đó và nói rằng chúng tôi muốn chuyển đến Cột D , bấm OK. Được rồi, bây giờ chúng ta có thêm trường đó, tôi sẽ Chèn một bộ cắt dựa trên trường Tháng đến ngày đó và tôi chỉ muốn xem Tháng đến ngày của chúng ta đúng như thế nào. Bây giờ, chúng ta có cần Slice lớn như vậy không? Không, chúng ta có thể làm cho nó thành hai cột và chỉ cần để nó không phô trương ở phía bên tay phải. Vì vậy, bây giờ những gì chúng ta có là tất cả các ngày trong năm 2016 và tất cả các ngày trong năm 2017; mặc dù, sẽ thực sự tuyệt vời nếu so sánh chúng cạnh nhau. Vì vậy, tôi sẽ lấy trường Ngày đó và Phân tích. Tôi sẽ Nhóm trường, tôi sẽ nhóm nó lên chỉ Năm. Tôi khôngt thực sự quan tâm đến những ngày cá nhân. Tôi chỉ muốn biết Tháng đến Ngày. Bây giờ, chúng ta đang ở đâu? Vì vậy, tôi sẽ nhóm nó lên thành Năm và chúng tôi sẽ kết thúc với 2 năm này ở đó và sau đó tôi sẽ sắp xếp lại điều này, đặt những Năm đã qua, Danh mục giảm xuống. Và bây giờ tôi thấy chúng ta đã ở đâu vào năm ngoái và chúng ta đã ở đâu trong năm nay. Được rồi, vì tôi đã nhóm xong nên tôi không còn được phép tạo trường được tính toán bên trong bảng tổng hợp. Nếu tôi muốn có một số tiền hàng năm ở đó, tôi sẽ nhấp chuột phải, Xóa Grand Total, được rồi, và bây giờ chúng ta đang, vì vậy,% Thay đổi, chúng ta đang ở bên ngoài bảng tổng hợp trỏ vào bên trong bảng tổng hợp . Chúng ta phải đảm bảo tắt GetPivotData hoặc chỉ xây dựng một công thức như sau: = J4 / I4-1 và điều đó tạo ra một công thức mà chúng ta có thể sao chép xuống mà không gặp bất kỳ phức tạp nào, giống như vậy.Được rồi, Mike, hãy xem bạn có gì.
Mike Girvin: Cảm ơn ,. Có, tôi đã gửi câu hỏi đến vì tôi đã làm điều đó với các công thức và tôi không thể tìm ra cách thực hiện điều đó với bảng tổng hợp chuẩn và sau đó tôi nhớ đã xem qua nhiều năm, thực hiện một loạt video thú vị về các cột trợ giúp và bảng tổng hợp . Đó là một công thức đẹp và một giải pháp đẹp. Vậy đó là cách thực hiện với bảng xoay, chúng ta cùng đi xem cách thực hiện với công thức nhé.
Bây giờ, tôi đang làm việc này hai ngày sau khi anh ấy làm. F2 Tôi có hàm TODAY luôn là thông tin ngày tháng cho ngày hiện tại hôm nay sẽ được sử dụng bởi các công thức ở đây vì chúng tôi muốn nó cập nhật. Tôi cũng đã sử dụng một bảng Excel và nó có tên là FSales. Nếu tôi Ctrl + Mũi tên Xuống, tôi thấy đó là 14/4 nhưng tôi muốn có thể thêm các bản ghi mới nhất và bao gồm cập nhật công thức của chúng tôi khi chúng tôi chuyển sang tháng tiếp theo. Ctrl + Mũi tên Lên. Được rồi, tôi có Tiêu chí năm làm tiêu đề cột, Danh mục làm tiêu đề hàng, sau đó chi tiết về tháng và ngày sẽ đến từ ô đó. Vì vậy, tôi chỉ đơn giản là sẽ sử dụng hàm SUMIFS vì chúng tôi đang thêm với nhiều điều kiện, phạm vi tổng đây là doanh thu, chúng tôi sẽ sử dụng thủ thuật tuyệt vời đó cho một bảng Excel.Ngay trên cùng, chúng ta thấy mũi tên chỉ xuống màu đen đó, BAM! Điều đó đặt vào tên bảng thích hợp và sau đó trong dấu ngoặc vuông là tên trường, dấu phẩy. Phạm vi tiêu chí, chúng ta sẽ phải sử dụng Ngày hai lần, vì vậy tôi sẽ bắt đầu với Ngày. Nhấp vào, có Cột Ngày, dấu phẩy. Bây giờ tôi đang ở trong tháng Tư, vì vậy tôi cần tạo điều kiện> = đến ngày 1 tháng Tư. Vì vậy, các toán tử so sánh “> =” trong dấu ngoặc kép và tôi sẽ nối nó. Bây giờ tôi phải tạo một số công thức ngày luôn xuất hiện ở đây và tạo ngày đầu tiên của tháng cho năm cụ thể này. Vì vậy, tôi sẽ sử dụng hàm DATE. Năm, tôi có Năm đúng là tiêu đề cột và tôi sẽ nhấn phím F4 một, hai lần để khóa hàng nhưng không phải cột, vì vậy khi nó di chuyển qua đây, chúng tôi sẽ chuyển sang 2017, dấu phẩy, Tháng - Tôim sẽ sử dụng hàm MONTH để lấy số tháng từ 1 đến 12. Tức là bất kỳ tháng nào có trong ô đó, F4 để khóa nó theo mọi hướng, đóng dấu ngoặc đơn và sau đó là dấu phẩy, 1 nó sẽ luôn là số 1 tháng bất kể tháng này là tháng nào, đóng ngoặc đơn.
Được rồi, đó là tiêu chí. Nó sẽ luôn là> = đầu tiên của tháng, dấu phẩy, tiêu chí phạm vi thứ hai Tôi sẽ lấy cột Ngày của mình, dấu phẩy. Tiêu chí hai, tốt, đây sẽ là <= giới hạn trên, vì vậy trong “<=” và &. Tôi sẽ gian lận, xem này. Tôi chỉ sẽ sao chép điều này từ trên đây vì nó giống nhau, Ctrl-C Ctrl-V ngoại trừ Ngày, chúng ta phải sử dụng hàm DAY và luôn lấy làm giới hạn trên của chúng ta bất kể ngày nào từ tháng cụ thể này là . F4 để khóa nó theo mọi hướng, đóng ngoặc vào Ngày. Được rồi, đó là hai tiêu chí của chúng tôi: dấu phẩy. Phạm vi tiêu chí 3, đó là Danh mục. Đây rồi, dấu phẩy và có tiêu đề hàng của chúng ta. Vì vậy, cái này chúng ta phải F4 một hai ba lần, khóa cột chứ không khóa hàng để khi chúng tôi sao chép công thức xuống, chúng tôi sẽ chuyển sang Gizmo và Widget,đóng ngoặc và đó là công thức. Kéo qua, nhấp đúp và gửi nó xuống. Tôi có thể thấy có rắc rối. Tốt hơn là tôi nên đến ô cuối cùng theo đường chéo xa nhất. Nhấn F2. Bây giờ hành vi mặc định cho Danh pháp Công thức Bảng là khi bạn sao chép công thức sang một bên, các cột thực tế sẽ di chuyển như thể chúng là các tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.Kéo qua, nhấp đúp và gửi nó xuống. Tôi có thể thấy có rắc rối. Tốt hơn là tôi nên đến ô cuối cùng theo đường chéo xa nhất. Nhấn F2. Bây giờ hành vi mặc định cho Danh pháp Công thức Bảng là khi bạn sao chép công thức sang một bên, các cột thực tế sẽ di chuyển như thể chúng là các tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.Kéo qua, nhấp đúp và gửi nó xuống. Tôi có thể thấy có rắc rối. Tốt hơn là tôi nên đến ô cuối cùng theo đường chéo xa nhất. Nhấn F2. Bây giờ hành vi mặc định cho Danh pháp công thức bảng là khi bạn sao chép công thức sang một bên, các cột thực tế sẽ di chuyển như thể chúng là tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.Tốt hơn là tôi nên đến ô cuối cùng theo đường chéo xa nhất. Nhấn F2. Bây giờ hành vi mặc định cho Danh pháp Công thức Bảng là khi bạn sao chép công thức sang một bên, các cột thực tế sẽ di chuyển như thể chúng là các tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.Tốt hơn là tôi nên đến ô cuối cùng theo đường chéo xa nhất. Nhấn F2. Bây giờ hành vi mặc định cho Danh pháp công thức bảng là khi bạn sao chép công thức sang một bên, các cột thực tế sẽ di chuyển như thể chúng là tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.các cột thực sự di chuyển như thể chúng là các tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.các cột thực sự di chuyển như thể chúng là các tham chiếu ô hỗn hợp. Bây giờ chúng tôi có thể khóa chúng nhưng tôi sẽ không làm điều đó lần này. Bây giờ hãy chú ý khi bạn sao chép nó xuống, nó hoạt động tốt nhưng khi bạn sao chép sang một bên thì đó là lúc các cột thực sự di chuyển. Vì vậy, hãy xem này, tôi sẽ Ctrl + C và Ctrl + V và sau đó điều đó sẽ tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.m đi đến Ctrl + C và Ctrl + V và sau đó điều đó tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.m đi đến Ctrl + C và Ctrl + V và sau đó điều đó tránh F để các cột di chuyển khi bạn sao chép nó sang một bên. Nhấp đúp và gửi nó xuống. Bây giờ công thức% Thay đổi của chúng tôi = số tiền cuối cùng / số tiền đầu tiên -1, Ctrl + Enter, nhấp đúp và gửi nó xuống.
Bây giờ, trước khi chúng ta kiểm tra nó, bây giờ hãy thêm một số bản ghi mới. Tôi thực sự muốn tạo nhãn này ở đây để nó động. Và cách tôi sẽ làm điều đó là tôi sẽ nói = ký và chúng tôi sẽ thực hiện một công thức Văn bản, vì vậy bất cứ lúc nào chúng tôi muốn văn bản và một công thức, bạn phải đặt nó vào: “và tôi sẽ nhập Bán hàng giữa, khoảng trắng ”& và bây giờ tôi cần trích xuất từ ngày duy nhất đó ở đó, ngày đầu tiên của tháng đến cuối tháng. Tôi sẽ sử dụng hàm TEXT. Hàm TEXT có thể lấy một số ngày tháng hoặc số sê-ri, dấu phẩy và sử dụng một số định dạng số tùy chỉnh trong ”. Tôi luôn muốn nhìn thấy ba chữ cái viết tắt của tháng, mmm, tôi luôn muốn nó là chữ cái đầu tiên. Bây giờ nếu tôi đặt 1 ở đây, dấu cách dấu phẩy yyy, điều đó sẽ không hoạt động. Muốn thấy điều đó mang lại cho chúng ta một giá trị hoặc vì nó không thích điều đó 1. Nhưng chúng ta 'được phép chèn một ký tự nếu chúng tôi sử dụng dấu gạch chéo lên, đó là ở định dạng Số tùy chỉnh. Mm và yy sẽ được định dạng Số tùy chỉnh hiểu là tháng và năm và giờ đây, định dạng Số tùy chỉnh sẽ hiểu để chèn số 1. F2 và bây giờ chúng ta chỉ cần đi đến: & “-” & TEXT của dấu phẩy đó và bây giờ chúng ta Sẽ chỉ sử dụng định dạng số thẳng: “mmm spaceD, yyy”) Ctrl + Enter.
Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.
That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.
Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.
And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.
Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.
Được rồi, tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một Podcast Dueling Excel khác và Excel Is Fun.
Tải tập tin
Tải file mẫu tại đây: Duel181.xlsm
