Месяц до даты - Советы по Excel

Как показать продажи за месяц до даты в сводной таблице. Это эпизод дуэлей Excel.

Смотреть видео

Метод Билла
Добавьте вспомогательную ячейку с формулой MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Добавьте это поле как слайсер, где = True
Бонусный совет: групповые ежедневные свидания до нескольких лет
Добавьте вычисление вне сводной таблицы, избегая GetPivotData
Подход Майка:
Превратите данные в таблицу с помощью Ctrl + T. Это позволяет добавлять в таблицу больше данных и обновлять формулы.
СУММЕСЛИМН с функциями ДАТА, МЕСЯЦ, ДЕНЬ
Нажатие F4 три раза блокирует ссылку только на столбец.
Будьте осторожны - если вы перетащите формулу таблицы в сторону, столбцы изменятся. Копировать и вставить - без проблем
Использование ТЕКСТ (дата, формат. Хороший трюк с 1 для вставки числа 1 в текст

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

Билл Джелен: Привет, с возвращением. Пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из. Ко мне присоединится Майк Гирвин из Excel Is Fun.

Это наш Эпизод 181: Сводная таблица за месяц до даты.

Что ж, привет, сегодняшний вопрос - сегодняшняя идея этой дуэли принадлежит Майку. Он говорит: «Можно ли создать отчет за месяц до даты в сводной таблице?»

Хорошо, поехали. Итак, вот что у нас есть: у нас есть свидания на два года с января 2016 года до 2017 года. Сейчас, конечно, я записываю это в апреле, сейчас 15 апреля, когда я записываю свой отрывок из дуэли. Итак, здесь у нас есть сводная таблица, показывающая дни с левой стороны, категорию вверху и доход в центре сводной таблицы.

Теперь, чтобы создать отчет за месяц до даты, я собираюсь сказать, что добавлю сюда новый вспомогательный столбец к моим исходным данным и проверим две вещи. И поскольку я проверяю две вещи, я собираюсь использовать функцию И, обе они должны быть Истинны, чтобы быть Месяцем до даты. И я собираюсь использовать здесь функцию под названием СЕГОДНЯ. СЕГОДНЯ, хорошо, поэтому я хочу знать, соответствует ли МЕСЯЦ СЕГОДНЯ ()) МЕСЯЦУ этой даты в столбце A. Если это правда, если это текущий месяц, другими словами, если это апрель, то проверьте и посмотрите, является ли день этой даты там, в A2, <= DAY of TODAY. Прекрасно то, что когда мы откроем эту книгу завтра или через неделю, сегодняшний день автоматически обновится, и мы дважды щелкнем, чтобы скопировать его.

Хорошо, теперь мы должны поместить эти дополнительные данные в нашу сводную таблицу, поэтому я перехожу сюда: Сводная таблица, Анализ, и нетрудно изменить источник данных, просто нажмите эту большую кнопку там и скажите, что мы хотим перейти к столбцу D. , щелкните ОК. Хорошо, теперь у нас есть это дополнительное поле, я собираюсь вставить слайсер на основе этого поля «Месяц до даты», и я хочу только увидеть, насколько верно наш «Месяц до даты». Теперь нужно, чтобы этот Slice был таким большим? Нет, мы, вероятно, можем сделать его двумя столбцами и просто сделать его ненавязчивым с правой стороны. Итак, теперь у нас есть все даты в 2016 году и все даты в 2017 году; хотя было бы здорово сравнить их бок о бок. Итак, я возьму это поле даты и проанализирую. Я собираюсь сгруппировать поле, я собираюсь сгруппировать его только по годам. Я неНа самом деле меня волнуют отдельные дни. Я просто хочу знать месяц до даты. Итак, где мы? Итак, я сгруппирую это по годам, и у нас будут эти 2 года, и я собираюсь затем переставить это, поместить эти годы, чтобы перейти, категории, чтобы понизить. А теперь я вижу, где мы были в прошлом году и где были в этом году. Хорошо, поскольку я выполнил группировку, мне больше не разрешено создавать вычисляемые поля внутри сводной таблицы. Если бы я хотел получить там годовую сумму, я бы щелкнул правой кнопкой мыши, Удалить общую сумму, хорошо, и теперь мы, итак,% изменение, мы за пределами сводной таблицы, указывающей внутрь сводной таблицы . Мы должны либо выключить GetPivotData, либо просто создать формулу вроде этой: = J4 / I4-1, и это создает формулу, которую мы можем скопировать без каких-либо проблем, например.Хорошо, Майк, давай посмотрим, что у тебя есть.

Майк Гирвин: Спасибо. Да, я отправил вопрос, потому что я сделал это с помощью формул, и я не мог понять, как это сделать с помощью стандартной сводной таблицы, а затем я вспомнил, как за эти годы я видел, сделал кучу классных видеороликов о вспомогательных столбцах и сводных таблицах . Это красивая формула и прекрасное решение. Вот как это сделать с помощью сводной таблицы, давайте посмотрим, как это сделать с помощью формулы.

Я делаю это через два дня после того, как он это сделал. F2 У меня есть функция СЕГОДНЯ, которая всегда будет информацией о текущей текущей дате, которая будет использоваться формулами здесь, потому что мы хотим, чтобы она обновлялась. Я также использовал таблицу Excel под названием FSales. Если я Ctrl + стрелка вниз, я увижу 4/14, но я хочу иметь возможность добавлять последние записи и включать обновление наших формул, когда мы перейдем к следующему месяцу. Ctrl + стрелка вверх. Хорошо, у меня есть критерии года в качестве заголовков столбцов, категория в качестве заголовков строк, а затем сведения для месяца и дня будут поступать из этой ячейки. Поэтому я просто собираюсь использовать функцию СУММЕСЛИМН, поскольку мы добавляем с несколькими условиями, диапазон сумм здесь и есть доход, мы собираемся использовать этот отличный трюк для таблицы Excel.Справа вверху мы видим черную стрелку, направленную вниз: БАМ! Это помещает правильное имя таблицы, а затем в квадратных скобках имя поля, запятую. Диапазон критериев, нам придется использовать Date дважды, поэтому я начну с Date. Щелкните, там столбец даты, запятая. Сейчас я в апреле, поэтому мне нужно создать условие> = до 1 апреля. Итак, операторы сравнения «> =» заключены в двойные кавычки, и я присоединяюсь к ним. Теперь мне нужно создать некоторую формулу даты, которая всегда выглядит здесь и создает первое число месяца для этого конкретного года. Итак, я собираюсь использовать функцию ДАТА. Год, ну, у меня год прямо в заголовке столбца, и я собираюсь нажать клавишу F4 один, два раза, чтобы заблокировать строку, но не столбец, поэтому, когда он переместится сюда, мы перейдем к 2017 году, запятая, Месяц - I 'm собираюсь использовать функцию МЕСЯЦ, чтобы получить номер месяца от 1 до 12. То есть, какой бы месяц ни был в этой ячейке, F4, чтобы заблокировать его во всех направлениях, закрывающие круглые скобки, а затем запятую, 1 он всегда будет первым из числа месяц независимо от того, какой это месяц, закрывающие скобки.

Хорошо, это критерии. Это всегда будет> = первое число месяца, запятая, диапазон критериев два. Я собираюсь получить столбец даты, запятая. Критерий два, ну, это будет <= верхний предел, поэтому в «<=» и &. Я собираюсь схитрить, посмотрите это. Я просто скопирую это отсюда, поскольку это то же самое, Ctrl-C Ctrl-V, за исключением дня, мы должны использовать функцию ДЕНЬ и всегда получать в качестве верхнего предела независимо от того, какой день из этого конкретного месяца . F4, чтобы заблокировать его во всех направлениях, закройте круглые скобки на Дата. Хорошо, это наш критерий два: запятая. Диапазон критериев 3, это Категория. Вот она, запятая и заголовок нашей строки. Таким образом, мы должны нажать F4 один два три раза, заблокировать столбец, но не строку, поэтому, когда мы скопируем формулу вниз, мы перейдем к Gizmo и Widget,закрывающая скобка, и это формула. Перетащите, дважды щелкните и отправьте вниз. Я вижу проблемы. Я лучше подойду к последней камере по диагонали дальше всех. Нажмите F2. Теперь поведение по умолчанию для Номенклатуры формул таблицы - когда вы копируете формулы в сторону, фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.Перетащите, дважды щелкните и отправьте вниз. Я вижу проблемы. Я лучше подойду к последней по диагонали камере. Нажмите F2. Теперь поведение по умолчанию для Номенклатуры формул таблицы - когда вы копируете формулы в сторону, фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.Перетащите, дважды щелкните и отправьте вниз. Я вижу проблемы. Я лучше подойду к последней по диагонали камере. Нажмите F2. Теперь поведение по умолчанию для Номенклатуры формул таблицы - когда вы копируете формулы в сторону, фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.Я лучше подойду к последней по диагонали камере. Нажмите F2. Теперь поведение по умолчанию для Номенклатуры формул таблицы - когда вы копируете формулы в сторону, фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.Я лучше подойду к последней по диагонали камере. Нажмите F2. Теперь поведение по умолчанию для Номенклатуры формул таблицы - когда вы копируете формулы в сторону, фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.фактические столбцы перемещаются, как если бы они были ссылками на смешанные ячейки. Теперь мы можем заблокировать их, но на этот раз я не буду этого делать. Теперь обратите внимание, когда вы копируете его вниз, он работает нормально, но когда вы копируете в ту сторону, где перемещаются фактические столбцы. Так что следите за этим, я собираюсь использовать Ctrl + C и Ctrl + V, а затем это предотвращает перемещение F для столбцов, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.m переходит к Ctrl + C и Ctrl + V, а затем это позволяет избежать перемещения F в столбцы, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.m переходит к Ctrl + C и Ctrl + V, а затем это позволяет избежать перемещения F в столбцы, когда вы копируете его в сторону. Дважды щелкните и отправьте его вниз. Теперь наша формула% Change = конечная сумма / начальная сумма -1, Ctrl + Enter, двойной щелчок и отправка вниз.

Now, before we go test it, now add some new records. I actually want to create this label up here so it's dynamic. And the way I'm going to do that is I'm going to say = sign and we're going to do a Text formula so anytime we want text and a formula, you have to put it in: “ and I'm going to type Sales Between, space”& and now I need to extract from that single date there, the first of the month to the end of the month. I'm going to use the TEXT function. The TEXT function can take a number dates or serial numbers, comma and use some custom number formatting in ” . I always want to see three-letter abbreviation for the month, mmm, I always want it as the first. Now if I put a 1 here, comma space yyy, that won't work. Wants to see that that gives us a value or because it doesn't like that 1. But we're allowed to insert a single character if we use forward slash, that's in Custom Number formatting. The mm and the yy will be understood by Custom Number formatting as month and year and now Custom Number format will understand to insert the number 1. F2 and now we're simply going to: &“ - ”&TEXT of that comma and now we’ll just use straight number formatting: “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.

Хорошо, эй, я хочу поблагодарить всех за то, что заглянули. Увидимся в следующий раз на еще одном подкасте Dueling Excel от и Excel Is Fun.

Скачать файл

Загрузите образец файла здесь: Duel181.xlsm