0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро и легко заполнить дату по неделям в Excel?

Как сделать сводную таблицу, сгруппировать временной ряд?

сводная таблицаСводная таблица в Excel — это мощнейший инструмент для анализа данных, который поможет вам быстро:

  • Подготовка данных для отчетов;
  • Расчет различных показателей;
  • Агрегирование данных;
  • Фильтрация и анализ интересующих вас показателей.

Это также экономит вам много времени.

В этой статье вы узнаете, как это сделать:

  • Как выполнить перекрестную табуляцию?
  • Как использовать перекрестную табуляцию для группировки временных рядов и оценки данных в динамике по годам, кварталам, месяцам и дням?
  • Как рассчитать прогнозы с помощью электронной таблицы и Forecast4AC PRO?

Для начала научимся делать сводные таблицы.

Чтобы составить сводную таблицу, нам нужно скомпоновать данные в простую таблицу. Каждый столбец должен содержать 1 анализируемый параметр. Например, у нас есть 3 колонки:

  • Дата
  • Продукт
  • Цены в рублях

В каждой строке 3 параметра связаны между собой, т.е. 01.02.2010 товар 1 был продан за 422 656 рублей.

сводная таблица в Excel

После подготовки данных для сводной таблицы установите курсор в первый столбец в первую ячейку простой таблицы, затем перейдите в меню «Вставка» и нажмите «Сводная таблица».

создание сводной таблицы+как сделать сводную таблицу

Появится диалоговое окно, в котором

  • Можно сразу же нажать кнопку «OK», и таблица pivot будет выведена на отдельный лист. Вы также можете настроить параметры вывода данных сводной таблицы:
  1. Диапазон данных для вывода в сводную таблицу
  2. Куда выводить сводную таблицу (на новый рабочий лист или на существующий рабочий лист (если выбран вывод на существующий рабочий лист, укажите ячейку, в которой будет размещена сводная таблица).

excel сводные таблицы

После нажатия кнопки «OK» сводная таблица появится на новом листе. Лист называется «Сводка».

  • В правой части листа отображаются поля и области, с которыми можно работать. Вы можете перетащить поля в эти области, и они появятся в сводной таблице листа.
  • Левая сторона листа представляет собой сводную таблицу.

сводные таблицы скачать

Для этого щелкните левой кнопкой мыши на поле «Товары» и перетащите его в «Название строк», поле «Продажи в рублях». — в «Значения» в сводной таблице. Таким образом, мы получим общую сумму продаж:

Читайте так же:
Как быстро создать вафельную диаграмму в Excel?

сводный таблицы

Группировка и фильтрация временных рядов в сводной таблице

Теперь нам нужно добавить соответствующие поля в сводную таблицу, если мы хотим анализировать и сравнивать продажи товаров по годам, кварталам, месяцам и дням.

Вам нужно вставить три пустых столбца после даты на листе «Данные». Выберите столбец «Товары» и нажмите «Вставить».

Убедитесь, что вновь добавленные столбцы находятся в диапазоне существующей таблицы, тогда мы сможем обновить сводку, а не переделывать все заново.

Вставленные столбцы называются «Год», «Месяц» и «Год-Месяц».

Для каждого из этих столбцов мы добавляем соответствующую формулу для получения интересующего нас параметра времени:

  • В колонке «Год» добавьте формулу =Год (относительно даты)
  • В колонке «Месяц» добавьте формулу =Месяц (относительно даты)
  • В колонке «Год-Месяц» добавьте формулу =CREATE(ссылка на год;» » «; ссылка на месяц)

Мы получаем 3 колонки с годом, месяцем и годом и месяцем:

сводные таблицы 2007

Чтобы обновить сводную таблицу, щелкните меню в верхней части сводной таблицы, щелкните правой кнопкой мыши, а затем выберите Обновить. После обновления таблицы простых данных мы видим, что в ней появились новые поля, такие как Год, Месяц и Год к месяцу:

excel сводные таблицы

Теперь давайте проанализируем продажи по годам.

Для этого перетащите поле «Год» в «имя столбца» сводной таблицы. В таблице ниже показаны продажи продуктов по годам:

работа со сводными таблицами

Теперь мы хотим спуститься еще ниже, на уровень месяца, и проанализировать продажи по годам и месяцам. Для этого перетащите поле «месяц» под годом в поле «имя столбца»:

пример сводной таблицы

Мы можем просмотреть динамику месяцев по годам, переместив месяцы в область сводки «Название строк», а затем просмотреть сводную таблицу, как показано на рисунке ниже:

сводные таблицы +в microsoft excel

Ниже приводится сводная таблица, которая показывает:

  • Продажи каждого продукта в целом за весь год (строка с названием продукта);
  • Наиболее подробные продажи каждого продукта по каждому месяцу в динамике за 4 года.
Читайте так же:
Как в Excel заполнить пустые ячейки тире?

Следующая задача, мы хотим убрать из анализа продажи за какой-то месяц (например, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.
Для этого в область сводной «Фильтр отчета» перетащим «Год — месяц»

создание сводных таблиц +в excel

Нажмите на фильтр, который появится над обзором, и установите флажок «Выделить больше элементов». Затем снимите флажок 2012 10 в списке годов и номеров месяцев и нажмите OK.

эксель сводные таблицы

Таким образом, можно добавлять новые параметры изменения времени и анализировать интересующие нас периоды времени так, как мы хотим. В сводной таблице рассчитываются значения для полей и фильтров, которые были установлены и добавлены в качестве интересующих полей.

Расчет проноза с помощью сводной таблицы и Forecast4AC PRO

Используйте сводную таблицу для создания продаж по товарам, годам и месяцам. Включите вычисления для исключения итогов.

Вы можете отключить итоги в сводной таблице, нажав на кнопку Remove Total рядом с колонкой Total. Итог из сводной таблицы исчезнет.

работа со сводными таблицами

Если используется Forecast4AC PRO, введите 1 января 2009 года как начальную дату.

отчет сводной таблицы

и нажимаем кнопку «График Модель прогноза» в меню Forecast4AC PRO

сводные таблицы скачать

Вы получите расчет прогноза на 12 месяцев и красивый график с анализом модели прогноза (тенденция, сезонность и закономерность) в сравнении с фактическими данными. Forecast4AC PRO может рассчитать для вас прогнозы, сезонные коэффициенты, показатели тренда и многое другое, а также построить графики на основе данных, представленных в сводной таблице.

создание сводной таблицы

С помощью электронной таблицы Excel вы можете быстро рассчитывать и генерировать показатели и данные в интересующем вас формате.

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачать бесплатные приложения для прогнозирования и бизнес-аналитики:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Читайте так же:
Как быстро проверить, пустой ли лист в Excel?

Испытайте возможности платных решений:

  • Novo Forecast PRO — Прогнозирование в Excel для больших наборов данных.

Получите 10 советов по повышению точности прогноза на 90% и более.

Как быстро и легко заполнить дату по неделям в Excel?

Предположим, вы хотите добавить две недели к сроку, установленному в календаре проекта, и определить новую дату окончания, или выяснить из списка задач проекта, сколько времени потребуется для выполнения одной задачи. Сложение или вычитание дней на основе даты можно выполнить с помощью простой формулы или использовать функции рабочего листа, предназначенные для работы с датами.

Сложить или вычесть дни из даты

Предположим, что счет-фактура должен быть выставлен во вторую пятницу каждого месяца. Вам нужно перевести деньги на свой счет так, чтобы они поступили за 15 календарных дней до этой даты, поэтому вам нужно вычесть 15 дней из срока платежа. В следующем примере мы узнаем, как складывать и вычитать даты, вводя положительные или отрицательные числа.

Сложить или вычесть дни из даты можно с помощью =A2+B2, где A2 — это дата, а B2 — количество дней, которые нужно прибавить или вычесть.

Даты конвергенции должны быть указаны в колонке А.

Введите количество дней для добавления или вычитания из колонки B. Вы можете ввести отрицательное число, чтобы вычесть дни из начальной даты, и положительное число, чтобы добавить их к дате.

Скопируйте =A2+B2 в ячейку C2.

Сложить или вычесть месяцы из даты с помощью функции ДАТАМЕСТ

Используйте функцию ДАТА для быстрого добавления или вычитания месяцев из даты.

Чтобы вычесть или прибавить количество месяцев, DATAMEST требует два аргумента: начальную дату и количество месяцев. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Таким образом, =TIME(«15.09.19»;-5) возвращает 15.04.19.

Используйте эту возможность, чтобы прибавить к дате месяцы или вычесть их из нее. В данном случае =ДАТАМЕСЯ(A2;B2), где A2 является датой, а B2 — количество месяцев, которые нужно прибавить или вычесть.

В этом примере начальные даты могут быть введены в колонку А.

Читайте так же:
Как быстро перенести группу строк в столбец в Excel?

Количество месяцев для добавления или вычитания вводится в столбце В. Если вы хотите вычесть месяц, введите знак минус (-) перед числом (например, -1).

В ячейке C2 введите =TIME(A2;B2) и при необходимости скопируйте его.

В зависимости от формата ячеек, содержащих введенную формулу, результаты могут отображаться в Excel в виде порядковых номеров. Например, 8-февраля 2019 года может отображаться как «43504».

В Excel даты хранятся как порядковые номера, поэтому их можно использовать в вычислениях. Порядковый номер по умолчанию — 1 января 1900 г. — 1 и 1 января 2010 г. — 40179 — это на 40 178 дней позже, потому что он от 1 января 1900 г.

Если результаты отображаются в виде числа, выделите нужные ячейки и выполните следующие действия:

Нажмите CTRL+1, чтобы открыть диалоговое окно «Формат ячеек» и перейдите на вкладку «Числа».

В категории нажмите на «Дата», выберите нужный формат даты и нажмите на «OK». Значения в ячейках должны отображаться в виде дат.

Сложить или вычесть годы из даты

В этом примере мы прибавляем и вычитаем годы от начальной даты, используя следующую формулу:

=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))

Сложить или вычесть годы из начальной даты с помощью =ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))

Вот как действует формула:

В ячейке A2 функция ГОД ищет дату и возвращает 2019 год. Затем ячейка B2 увеличивается на три года, в результате чего получается 2022 год.

Функции MONTH и DATE возвращают только исходные значения ячейки A2, но они нужны функции DATE.

И наконец, функция DATE объединяет эти три значения в дату на 3 года вперед — 08.02.22.

Сложить или вычесть из даты сочетание дней, месяцев и лет

В этом примере мы добавляем и вычитаем годы, месяцы и дни из начальной даты, используя следующую формулу:

=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2)+C2;ДЕНЬ(A2)+D2)

С помощью функции ДАТА можно прибавить к дате годы, месяцы или дни или вычесть их из нее.

Как действует формула :

Функция YEAR находит дату в ячейке A2 и возвращает 2019 год. Затем она добавляет 1 год из ячейки B2, в результате чего получается 2020 год.

Читайте так же:
Как активировать ячейку для редактирования одним щелчком мыши в Excel?

Функция MONTH возвращает 6, а затем добавляет 7 в ячейку C2. Поскольку 6 + 7 = 13, это интересно. 1 год и 1 месяц. Функция распознает это и автоматически добавляет еще один год к результату, усекая его с 2020 до 2021.

Функция DAY возвращает 8 и прибавляет к нему 15. Эта формула работает так же, как и часть формулы «MONTH», если вы объедините количество дней в месяце.

Затем создается дата, которая находится на 1 год, 7 месяцев и 15 дней в будущем, — 23.01.21.

Используйте эти формулы или функции для расчета дат, например, при добавлении двух недель к графику проекта или для расчета времени, которое потребуется для выполнения задачи.

Предположим, что расчетный цикл счета составляет 30 дней, и вы хотите, чтобы средства поступили на счет за 15 дней до даты вычета в марте 2013 года. Вот как можно работать с датами с помощью формулы или функции.

В ячейке A1 введите 08.02.2013.

В ячейке B1 запишите =A1-15.

Введите =A1+30 в ячейку С1.

В ячейке D1 введите =С1-15.

Расчет даты

Добавление месяцев к дате

Здесь используется функция DATE, и вам понадобится начальная дата и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:

Использование формулы ДАТАМЕС для добавления месяцев к дате

Введите 24.10.2013 в ячейку А1.

В ячейку B1 введите =TIME(A1;16).

Ячейку B1 можно использовать для форматирования результатов в виде дат. Щелкните стрелку рядом с кнопкой Числовой формат>Даты.

Вычитание месяцев из даты

Для вычитания месяцев из даты используется та же функция DATAMATE.

Введите дату в ячейку A1 и в ячейку B1 и формулу =DATAMES(15.04.2013;-5).

Вычитание месяцев из даты

Это значение начальной даты, заключенное в инвертированную запятую.

Чтобы добиться того же результата, можно либо обратиться к ячейке со значением даты, либо использовать формулу =DATAMES(A1;-5)

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector