З місяця до дати - Поради Excel

Як відобразити продажі за місяць до зведеної таблиці. Це епізод дуельного Excel.

Переглянути відео

Метод Білла
Додайте допоміжну комірку з формулою MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Додайте це поле як Slicer, де = True
Бонусна порада: групуйте щоденні дати до років
Додайте розрахунок за межами зведеної таблиці, уникаючи GetPivotData
Підхід Майка:
Перетворіть дані в таблицю, використовуючи Ctrl + T. Це дозволяє додавати більше даних до таблиці та оновлювати формули.
SUMIFS з функціями DATE, MONTH, DAY
Тричі натиснувши клавішу F4, блокується посилання лише на стовпець.
Обережно - якщо перетягнути формулу таблиці вбік, стовпці змінюються. Копіювати та вставити - без проблем
Використання ТЕКСТУ (дата, формат. Гарний фокус з 1, щоб вставити цифру 1 у текст

Стенограма відео

Білл Джелен: Гей, ласкаво просимо назад. Настав час чергового дуельного підкасту Excel. Я Білл Джелен з. До мене приєднається Майк Гірвін з Excel Is Fun.

Це наш епізод 181: Зведена таблиця за місяць до дати.

Ну, привіт, сьогоднішнє запитання - сьогоднішню ідею цього двобою надіслав Майк. Він каже: "Чи можете ви створити звіт за місяць до дати у зведеній таблиці?"

Гаразд, їдемо. Отже, ось що у нас є, у нас є дати на два роки з січня 2016 року аж до 2017 року. Зараз, звичайно, я записую це у квітні, зараз 15 квітня, коли я записую свій фрагмент дуелі. Отже, тут ми маємо зведену таблицю, що відображає Дні зліва, Категорію вгорі та Дохід у центрі зведеній таблиці.

Тепер, щоб створити звіт «За місяць на дату», я збираюся сказати, що додаю сюди новий стовпець помічника до моїх вихідних даних, і це буде перевіряти дві речі. І оскільки я перевіряю дві речі, я буду використовувати функцію І, обидві речі мають бути істинними, щоб це було місяць на сьогодні. І я збираюся використати функцію, яка називається СЬОГОДНІ. СЬОГОДНІ, добре, тому я хочу знати, чи МІСЯЦЬ СЬОГОДНІ ()) = МІСЯЦЬ тієї дати там, у стовпці А. Якщо це правда, якщо це поточний місяць, тобто іншими словами, якщо це квітень, перевірте і подивіться, чи день тієї дати там у А2 <= ДЕНЬ СЬОГОДНІ. Найкрасивіше, що коли ми відкриємо цю книгу завтра чи через тиждень, сьогоднішній день автоматично оновиться, і ми двічі клацнемо, щоб скопіювати це.

Гаразд, зараз ми повинні отримати ці додаткові дані у нашу зведену таблицю, тому я приходжу сюди, зведену таблицю, аналізую, і змінити джерело даних не так складно, просто натисніть ту велику кнопку там і скажіть, що ми хочемо перейти до стовпця D , натисніть OK. Добре, отже, тепер у нас є це додаткове поле, я збираюся вставити слайсер на основі цього поля Month To Date, і я хочу лише побачити, наскільки наш місяць To Date справжній. Тепер, чи потрібен нам цей Зріз таким великим? Ні, ми, мабуть, можемо зробити це двома стовпчиками, і це просто на зразок ненав’язливого там, з правого боку. Отже, зараз у нас є всі дати у 2016 році та всі дати у 2017 році; хоча, було б дуже здорово порівняти їх поруч. Тож я збираюся взяти це поле Дата та проаналізувати. Я збираюся згрупувати поле, збираюся згрупувати його лише до Років. Я неt насправді піклуються про окремі дні. Я просто хочу знати місяць на сьогодні. А де ми зараз? Тож я згрупую це за Роками, і ми закінчимо ці 2 роки там, і я збираюся потім переставити це, поставити ці Роки, щоб перейти, Категорії знизити. І тепер я бачу, де ми були минулого року, а де були цього року. Гаразд, оскільки я зробив групування, мені більше не дозволено створювати обчислюване поле всередині зведеної таблиці. Якби я хотів мати там рік-річну суму, я б клацнув правою кнопкою миші, Видалити загальний підсумок, добре, і тепер ми, отже,% Зміна, ми знаходимося поза зведеною таблицею, що вказує всередині зведеної таблиці . Ми повинні обов’язково вимкнути GetPivotData або просто створити таку формулу: = J4 / I4-1, і це створює формулу, яку ми можемо копіювати без жодних клопотів, наприклад.Добре, Майк, давайте подивимось, що у вас є.

Майк Гірвін: Дякую. Так, я надіслав запитання тому, що зробив це за допомогою формул, і я не міг зрозуміти, як це зробити за допомогою стандартної зведеної таблиці, а потім згадав, як бачив протягом багатьох років, робити купу цікавих відео про допоміжні стовпці та зведені таблиці . Це прекрасна формула і гарне рішення. Отже, як це зробити за допомогою зведеної таблиці, давайте подивимось, як це зробити за допомогою формули.

Зараз я роблю це через два дні після того, як він це зробив. F2 У мене є функція СЬОГОДНІ, яка завжди буде інформацією про дату сьогоднішньої поточної дати, яка буде використана формулами тут, оскільки ми хочемо її оновити. Я також використовував таблицю Excel, і вона називається FSales. Якщо я натиснув Ctrl + стрілка вниз, я бачу, що це 4/14, але я хочу мати можливість додавати останні записи та включати оновлення наших формул, коли ми переходимо до наступного місяця. Ctrl + стрілка вгору. Гаразд, у мене в якості заголовків стовпців є критерії року, категорія - заголовки рядків, а потім дані з місяця та дня надходитимуть із цієї клітинки. Тож я просто збираюся використовувати функцію SUMIFS, оскільки ми додаємо з кількома умовами, діапазон суми - це дохід, ми використаємо цей чудовий фокус для таблиці Excel.Прямо вгорі ми бачимо ту чорну стрілку вниз, BAM! Це ставить у власну назву таблиці, а потім у квадратних дужках ім’я поля, кома. Діапазон критеріїв, нам доведеться використовувати дату двічі, тому я почну з дати. Клацніть, там стовпець дати, кома. Зараз я в квітні, тому мені потрібно створити умову> = до 1 квітня. Тож порівняльні оператори “> =” у подвійних лапках, і я збираюся приєднатися до цього. Тепер я повинен створити формулу дати, яка завжди виглядає тут і створює перше число місяця для цього конкретного року. Тож я збираюся використовувати функцію DATE. Рік, ну у мене є Рік як заголовок стовпця, і я натисну клавішу F4, два рази, щоб зафіксувати рядок, але не стовпець, тому, коли він переміститься сюди, ми перейдемо до 2017, кома, Місяць - я 'm використовуватиме функцію MONTH, щоб отримати місяць від 1 до 12. Тобто, який би місяць не був у цій клітинці, F4, щоб зафіксувати його у всіх напрямках, закрити дужки, а потім кому, 1 це завжди буде 1-е число місяць незалежно від того, який це місяць, закрийте дужки.

Гаразд, так це критерії. Завжди буде> = першого числа місяця, кома, діапазон критеріїв два Я збираюся отримати свій стовпець Дата, кома. Критерії два, ну, це буде <= верхня межа, тому в «<=» та &. Я збираюся обдурити, подивись це. Я просто скопіюю це звідси, оскільки це одне і те ж, Ctrl-C Ctrl-V, крім дня, ми повинні використовувати функцію DAY і завжди отримувати як верхню межу будь-який день із цього конкретного місяця . F4, щоб зафіксувати його у всіх напрямках, закрийте дужки на Дата. Гаразд, так це наші два критерії: кома. Діапазон критеріїв 3, це категорія. Ось вона, кома і є наш заголовок рядка. Отже, нам доведеться F4 два два рази тричі, зафіксувати стовпець, але не рядок, тому, скопіювавши формулу, ми перейдемо до Gizmo та Widget,тісна дужка, і це формула. Перетягніть, двічі клацніть і надішліть його вниз. Я бачу, що там проблеми. Мені краще підійти до останньої комірки по діагоналі найдальше. Потрапив F2. Тепер поведінка за замовчуванням для номенклатури формул таблиці полягає в тому, що коли ви копіюєте формули збоку, фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.Перетягніть, двічі клацніть і надішліть його вниз. Я бачу, що там проблеми. Мені краще підійти до останньої комірки по діагоналі найдальше. Потрапив F2. Тепер поведінка за замовчуванням для номенклатури формул таблиці полягає в тому, що коли ви копіюєте формули збоку, фактичні стовпці переміщаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.Перетягніть, двічі клацніть і відправте вниз. Я бачу, що там проблеми. Мені краще підійти до останньої комірки по діагоналі найдальше. Потрапив F2. Тепер поведінка за замовчуванням для номенклатури формул таблиці полягає в тому, що коли ви копіюєте формули збоку, фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.Мені краще підійти до останньої комірки по діагоналі найдальше. Потрапив F2. Тепер поведінка за замовчуванням для номенклатури формул таблиці полягає в тому, що коли ви копіюєте формули збоку, фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.Мені краще підійти до останньої комірки по діагоналі найдальше. Потрапив F2. Тепер поведінка за замовчуванням для номенклатури формул таблиці полягає в тому, що коли ви копіюєте формули збоку, фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.фактичні стовпці рухаються так, ніби це змішані посилання на комірки. Тепер ми могли б їх заблокувати, але цього разу я цього робити не збираюся. Тепер зауважте, коли ви копіюєте його, це працює нормально, але коли ви копіюєте в ту сторону, коли рухаються фактичні стовпці. Тож стежте за цим, я перейду до Ctrl + C і Ctrl + V, а потім це дозволить уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.m, переходячи до Ctrl + C і Ctrl + V, а потім це дозволяє уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.m, переходячи до Ctrl + C і Ctrl + V, а потім це дозволяє уникнути переміщення стовпців F, коли ви копіюєте його в бік. Двічі клацніть і надішліть. Тепер наша формула% зміни = кінцева сума / початкова сума -1, Ctrl + Enter, двічі клацніть та надішліть її вниз.

Тепер, перш ніж ми випробуємо його, тепер додайте кілька нових записів. Я насправді хочу створити цей ярлик тут, щоб він був динамічним. І те, як я це буду робити, це те, що я кажу = знак, і ми будемо робити формулу Тексту, тому щоразу, коли нам потрібен текст і формула, ви повинні ввести це: “а я збираюся ввести Продажі між, пробіл ”& і тепер мені потрібно витягти ту саму ту саму дату, перше місяця до кінця місяця. Я збираюся використовувати функцію TEXT. Функція TEXT може приймати дати чи порядкові номери, коми та використовувати певне спеціальне форматування чисел у ”. Я завжди хочу бачити трибукве абревіатуру місяця, ммм, я завжди хочу, щоб це було першим. Тепер, якщо я поставлю тут 1, пробіл yyy, це не буде працювати. Хоче побачити, що це дає нам цінність або тому, що це не подобається 1. Але ми 'повторно дозволено вставляти один символ, якщо ми використовуємо косу риску, це форматування нестандартного числа. Під форматуванням користувацьких чисел mm та yy розумітимуться як місяць і рік, і тепер формат Custom Number буде розуміти, щоб вставити число 1. F2, і тепер ми просто перейдемо до: & “-” & TEXT цієї коми, і тепер ми я просто використаю форматування прямого числа: “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.

Добре, ну привіт, я хочу подякувати усім, що завітали. Ми побачимо вас наступного разу для чергового дуельного підкасту Excel від та Excel is Fun.

Завантажити файл

Завантажте зразок файлу тут: Duel181.xlsm