В тази статия ще научим как да групирате дати по броя на дните в Excel.
Сценарий:
Проблемът е, когато имаме набор от полета за дата в обобщената таблица. И потребителят иска да групира полето за дата по определен брой дни вместо години или дискретни дати. Например групиране на внос и износ на продукти на всеки 60 дни в Excel. Или групиране на данни за служители по месеци или седмици в Excel. За подобни проблеми използваме обобщени таблици и опции за групиране. Има още един начин за потребителите, които използват агрегирана функция. Нека разберем тези два процеса един по един.
Групирайте в обобщена таблица в Excel
Изберете данни> Вмъкване> Осева таблица> (Вмъкване на нова обобщена таблица)> Отидете на обобщена таблица, добавете поле за дата към редове> Стойност на датата с десен бутон> Група …> Изберете месеци или дни> Щракнете върху OK, за да потвърдите
Алтернативен начин с агрегираща функция
Добавете начална дата и клетка за увеличаване на всеки интервал от 60. Използвайте обобщената формула на excel, която има множество критерии.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме примерни данни за продажбите и печалбата по държави. Трябва да групираме данните по 60 дни (приблизително 2 месеца).
Сортирайте данните по поле за дата. Изберете таблицата и поставете обобщена таблица за данни. Добавете дата към редовете и продажбите и печалбата към стойностите.
Както можете да видите, полето за дата е във формат година. Но ние искаме да групираме до 60 дни. Така че щракнете с десния бутон върху стойността на датата> Изберете група …
Щракнете върху OK.
Датата е групирана по 60 -дневен интервал заедно със стойността на продажбите и печалбата.
Това е най -лесният начин да групирате дата по месеци. Но ако имате нужда от горните данни, използвайки формулата sumifs, това ще отнеме повече време, но е удобно да използвате функциите ефективно.
Алтернативен начин с помощта на формулата SUMIFS
За същите данни направете нова таблица с имена на началната дата и добавете 59 дни към началната дата, както е показано по -долу.
А в клетката A5 използвайте = B4+1, за да получите следващата дата. Сега копирайте и поставете клетъчната формула В4, докато получи крайната дата. И копирайте поставете клетъчната формула А5 до крайния период от време.
Сега използвайте формулата sumifs
= SUMIFS (диапазон на сумата, колона за дата от таблица, "> =" & A4, колона за дата от таблица, "<=" & B4)
Филтрирайте празната клетка или стойност 0, за да получите същото поле, което получихме с помощта на обобщена таблица.
Докато обобщената таблица ви предоставя различни агрегирани функции като средно, броене, сума, макс, мин, вар, stdev, но функциите на excel AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS и MINIFS, от които да избирате. MAXIFS и MINIFS са функции за актуализиране над Excel 2016 или нов Excel 365.
Ето всички бележки за наблюдение, използващи обяснения метод в Excel
Бележки:
- Предпочитайте или използвайте метода на обобщената таблица, той е лесен и има много функции за избор.
- Excel съхранява дати и час като числа и се използва при изчисляване от функцията. Затова се препоръчва да се използват дати като препратка към клетка или да се използва функцията DATE, вместо да се дава директен аргумент на функцията. Проверете валидирането на стойностите на датата, или функцията връща #VALUE! Грешка.
- Използвайте кавички с формула sumifs за "> =" и "<=".
- MAXIFS и MINIFS нова и актуализирана версия на MIN и MAX с критерии. Или използвайте формулата на масива с MAX и MIN на място за MINIFS и MAXIFS по -стари версии на Excel.
Надявам се, че тази статия за това как да групирате дати по брой дни в Excel е обяснителна. Намерете още статии за изчисляване на стойностите на датите и свързаните с тях формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Обобщени таблици на Excel : Обобщените таблици са един от най -мощните инструменти и този, който познава всички функции на обобщените таблици, може да увеличи производителността му експоненциално. В тази статия ще научим подробно всичко за обобщените таблици.
Пребройте празниците между датите в Excel : бройте неработни дни между двете дадени дати, като използвате функциите DATEDIF и NETWORKDAYS в Excel.
Условно форматиране за обобщена таблица : Условното форматиране в обобщените таблици е същото като условното форматиране при нормални данни. Но трябва да бъдете внимателни, докато условните таблици се форматират, тъй като данните се променят динамично.
Как да конвертирате дата в текст в Excel : В тази статия научихме как да конвертираме текст в дата, но как да конвертирате дата на excel в текст. За да преобразуваме дата на Excel в текст, имаме няколко техники.
Извличане на дни между датите, без да се обръща внимание на годините в Excel : пребройте дните между двете дати, като използвате различни функции и математическа операция в Excel.
Пребройте датите на раждане по месеци в Excel : пребройте броя дати в определен месец, като използвате функцията SUMPRODUCT и MONTH в Excel.
Как да използвате функцията NETWORKDAYS в Excel : Връща работните дни между двете посочени дати, използвайки функцията NETWORKDAYS.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.