От месец до дата - Съвети за Excel

Как да покажете продажбите от месец до дата в обобщена таблица. Това е дуел на Excel епизод.

Гледам видео

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

Видео транскрипция

Бил Джелен: Хей, добре дошла. Време е за поредния дуелинг подкаст на Excel. Аз съм Бил Джелен от. Към мен ще се присъедини Майк Гирвин от Excel Is Fun.

Това е нашият епизод 181: Обобщена таблица от месец до дата.

Е, хей, днешният въпрос - днешната идея за този дуел е изпратена от Майк. Той казва: „Можете ли да създадете отчет от месец до дата в обобщена таблица?“

Добре, да тръгваме. И така, ето какво имаме, имаме дати от две години от януари 2016 г. чак до 2017 г. Сега, разбира се, записвам това през април, сега е 15 април, когато записвам парчето си от дуела. И така, тук имаме обобщена таблица, показваща Дни отляво, Категория в горната част и Приходи в сърцето на обобщената таблица.

Сега, за да създам отчет от месец до дата, това, което ще направя, е да кажа, че ще добавя нова помощна колона тук към първоначалните си данни и това ще провери за две неща. И тъй като проверявам за две неща, ще използвам функцията И, и двете неща трябва да са верни, за да бъде месец до дата. И ще използвам функция тук, наречена ДНЕС. ДНЕС, добре, така че искам да знам дали МЕСЕЦЪТ ДНЕС ()) е = до МЕСЕЦА на тази дата там в Колона А. Ако това е вярно, ако е текущият месец, така че с други думи, ако е април, тогава проверете и вижте дали денят на тази дата там в А2 е <= ДЕН от ДНЕС. Красивото е, че когато отворим тази работна книга утре или след седмица, денят днес ще се актуализира автоматично и щракнем двукратно, за да копираме това.

Добре сега, трябва да вземем тези допълнителни данни в нашата обобщена таблица, така че аз идвам тук Обобщена таблица, анализирам и не е толкова трудно да се промени източникът на данни, просто щракнете върху този голям бутон там и кажете, че искаме да преминем към колона D , щракнете върху OK. Добре, така че сега имаме това допълнително поле, аз ще вмъкна слайсър въз основа на това поле от месец до дата и искам само да видя как нашият месец до дата е истина. Сега, имаме ли нужда този парче да е толкова голям? Не, вероятно можем да го направим две колони и просто да го направим ненатрапчив от дясната страна. И така, сега имаме всички дати през 2016 г. и всички дати през 2017 г .; макар че би било много готино да ги сравнявате един до друг. Така че ще взема това поле за дата и ще анализирам. Отивам да групирам полето, ще го групирам до само години. Аз неНе се интересуват от отделните дни. Просто искам да знам месец до дата. Сега къде сме? Така че ще го групирам до Години и ще свършим с тези 2 години там и след това ще пренаредя това, ще сложа тези Години да преминат, Категориите да намалят. И сега виждам къде бяхме миналата година и къде бяхме тази година. Добре сега, тъй като съм направил групирането, вече не ми е позволено да създавам изчислено поле вътре в осевата таблица. Ако исках да имам сума за година за тази година, щях да щракнете с десния бутон, Премахване на общата сума, добре, и сега сме, така че,% Промяна, ние сме извън обобщена таблица, сочеща вътре в обобщената таблица . Трябва да се уверим, че или изключваме GetPivotData, или просто изграждаме формула по следния начин: = J4 / I4-1 и това създава формула, която можем да копираме без никакви неприятности като тази.Добре, Майк, да видим какво имаш.

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

Сега правя това два дни след като той го направи. F2 Имам функцията ДНЕС, която винаги ще бъде информацията за датата за днешната текуща дата, която ще се използва от формулите тук, защото искаме да се актуализира. Използвал съм и таблица на Excel и се казва FSales. Ако Ctrl + стрелка надолу, виждам, че е 4/14, но искам да мога да добавя най-новите записи и да включим актуализацията на нашите формули, когато преминем към следващия месец. Ctrl + стрелка нагоре. Добре, аз имам критерии за година като заглавки на колони, категория като заглавки на редове и след това данните за месец и ден ще идват от тази клетка. Така че просто ще използвам функцията SUMIFS, тъй като добавяме с множество условия, обхватът на сумата тук е приходите, ще използваме този страхотен трик за таблица в Excel.Точно отгоре виждаме онази черна стрелка надолу, BAM! Това поставя в правилното име на таблицата и след това в квадратни скоби името на полето, запетая. Обхват на критериите, ще трябва да използваме Date два пъти, така че ще започна с Date. Щракнете, има колона с дата, запетая. Сега съм през април, така че трябва да създам условието> = до 1 април. Така че сравнителните оператори “> =” в двойни кавички и аз ще се присъединя към него. Сега трябва да създам някаква формула за дата, която винаги изглежда тук и създава първото от месеца за тази конкретна година. Така че ще използвам функцията DATE. Година, добре, имам Година точно като заглавка на колоната и ще натисна клавиша F4, два пъти, за да заключа реда, но не и колоната, така че когато се премести тук, ще преминем към 2017, запетая, Месец - азm ще използвам функцията MONTH, за да получа месеца от 1 до 12. Това е, който и месец да е в тази клетка, F4 да го заключи във всички посоки, да затвори скоби и след това запетая, 1 винаги ще е 1-ви от месец, без значение какъв е този месец, затворете скоби.

Добре, това са критериите. Винаги ще бъде> = първото от месеца, запетая, критерии диапазон две Ще получа моята колона Дата, запетая. Критерии два, добре, това ще бъде <= горната граница, така че в „<=“ и &. Ще изневеря, гледайте това. Просто ще копирам това от тук, тъй като това е едно и също нещо, Ctrl-C Ctrl-V с изключение на Деня, ние трябва да използваме функцията ДЕН и винаги да получаваме като наша горна граница независимо от деня от този конкретен месец . 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 може да приема числови дати или серийни номера, запетая и да използва някакво персонализирано форматиране на числа в ”. Винаги искам да видя трибуквено съкращение за месеца, mmm, винаги го искам като първо. Сега, ако сложа 1 тук, интервал запетая ггг, това няма да работи. Иска да види, че това ни дава стойност или защото това не му харесва 1. Но ние "Позволено е да вмъкнете един символ, ако използваме наклонена черта, това е във форматиране на персонализирано число. Mm и yy ще бъдат разбрани от форматирането на персонализирано число като месец и година и сега форматът на персонализирания номер ще разбере да вмъкнете числото 1. F2 и сега просто ще: & “-” & TEXT на тази запетая и сега ние просто ще използвам правилно форматиране на числа: “mmm spaceD, yyy”) Ctrl + Enter.

Сега нека просто, преди да добавим някои данни, просто да променим това. Преструвайки се, че днес се показва: 15.3.2017 г. точно така, всички формули се актуализират и нашата текстова формула също е Ctrl + Z. Сега, нека слезем до дъното на набора от данни, Ctrl + стрелка надолу Искам да добавя един нов запис. Аз съм в последната клетка на набора от данни, натиснах Tab, за да добавя нов запис към нашия набор от данни. Просто ще копирам този запис тук, Ctrl + стрелка нагоре и там ще видим разликата. Ако искахме да проверим тези стойности на формулата спрямо тези, които направиха: = относителна препратка към клетка = щракване върху листа, ще щракнем точно в I4. Можем да видим нашата формула там, Ctrl + Enter. Всъщност ще го влача надолу. Ctrl + Enter току-що попълва всичко, което бях подчертал. И разбира се, FALSE FALSE.Ами познайте какво? = тази сума точно там -, щракнете върху Ctrl + стрелка надолу, Ctrl + Backspace, така че ще извадя това, само за да проверя и достатъчно сигурно, че е точното количество, което можем да погледнем назад там.

Това е малко забавно с някои IFS и някои изчисления на датата, ДНЕС и дори някои забавни текстови формули. Добре, върни се към.

Бил Джелен: Добре, Майк, това е страхотно. Така че, за да го приключи, Майк взе данните и ги превърна в таблица, използвайки Ctrl + T, която позволява да се добавят повече данни към таблицата и формулите ще се актуализират, създават онази страхотна малка формула със SUMIFS, DATE, MONTH и DAY функции. Не забравяйте да натиснете F4 три пъти, заключва препратката само към колоната, макар че внимавайте, ако плъзнете формула на таблицата странично с помощта на манипулатора за попълване, колоните се променят, но Copy and Paste облекчава този проблем. Никога не съм го познавал.

И тогава хубав трик там, като използвате заглавието с формата на датата на текста и че 1, за да вмъкнете число 1 в текста, във всеки символ. Бих позволил да вмъкна нещо, така че може да се наложи да направите нещо като C O O L, за да вкарате цяла дума там, но ще работи.

Добре, методът ми използваше обобщена таблица, добавих помощна колона с формула MONTH TO DATE тази, която използва = AND проверявайки дали MONTH и DAY съвпадат. Добавете това поле като Slicer, задайте Slicer = True. И тогава съвет за бонус: Групирайте дневни дати до години и след това добавете изчисление извън обобщената таблица, като същевременно избягвате GetPivotData. И ми е интересно, все още не знам как Майк го направи с формулата си. Той успя да използва мишката, за да посочи това, равно на нещо, то е в моята обобщена таблица и не получи GetPivotData. Може, може би той го е изключил.

Добре, добре, хей, искам да благодаря на всички, че се отбиха. Ще се видим следващия път за поредния дуел на Excel Podcast от и Excel е забавно.

Свали файл

Изтеглете примерния файл тук: Duel181.xlsm