Как сделать график продаж в excel по месяцам датам и дням недели
В Excel есть 3 основных способа определить день недели по дате.
1. Способ первый - функция ДЕНЬНЕД (WEEKDAY)
Функция ДЕНЬНЕД возвращает порядковый номер дня недели (например, для понедельника можно получить значение "1", т.к. это первый день недели, но есть нюансы).
Синтаксис функции состоит из двух аргументов:
= ДЕНЬНЕД(дата_в_числовом_формате;[тип])
- дата_в_числовом_формате. В эксель даты хранятся в виде чисел. Например, 3 июля 2020 года имеет порядковый номер 44015.
- тип. Необязательный аргумент, определяющий, какой день недели будет считаться первым. Например, если указать тип 1 (или не указать), то эксель будет считать, что первый день недели - это воскресение и придаст ему значение 1, а последний - суббота (придает ей значение 7). Полагаю, что для вас, как и для меня, наиболее удобным будет тип 2 - в этом случае отсчет пойдет с понедельника (1) до воскресения (7).
При желании можно выбрать другой тип из подсказок Excel так, чтобы отсчет начинался с любого удобного для вас дня недели.
Мы упомянули, что даты хранятся в виде чисел. Такой подход даёт огромный плюс - даты могут участвовать в расчетах. Например, к дате можно прибавить 14, как к обычному числу, что будет означать, что мы прибавляем 14 дней.
Есть и некоторое неудобство - для работы через мастер функций приходится использовать функцию ДАТА(DATE) - нельзя просто ввести "03.07.2020". Смотрим пример на картинке ниже:
Обратите внимание, что, конечно, гораздо удобнее в качестве первого аргумента указывать ссылку на ячейку с датой (если ячейка уже отформатирована, как дата, то не придется дополнительно использовать функцию ДАТА):
2. Способ второй - форматирование
Имеем ячейки или диапазон с датами, которые представлены, как число.месяц.год (например, 03.07.2020). Задача, сделать так, чтобы в названии даты сразу указывался день недели. Например, Пт 03.07.2020
Для этого необходимо сделать следующее:
- Выделяем диапазон с указанными датами, к которым нужно применить форматирование
- Нажимаем Ctrl+1 (либо клик правой кнопкой мыши >> "Формат ячеек" в выпавшем меню)
- Выбираем в меню пункт ( все форматы) и в строке Тип указываем ДДД ДД.ММ.ГГГГ. Нажать ОК
Код ДДД в примере выше - это код отображения дня недели в сокращенном виде (Пн. Вс). Ниже представлены другие коды, комбинируя которые можно собрать для себя удобный формат даты:
- М - месяц в виде чисел от 1 до 12
- ММ - месяц в виде чисел 01 до 12
- МММ - месяц в виде "янв", . "дек"
- ММММ - месяц в виде "январь", . "декабрь"
- МММММ - месяц в виде первой буквы месяца
- Д - день в виде чисел от 1 до 31
- ДД - день в виде чисел от 01 до 31
- ДДД - день в виде "Пн", . "Вс"
- ДДДД - день в виде "понедельник", . "воскресенье"
- ГГ - год в виде чисел от 00 до 99
- ГГГГ - год в виде чисел от 1900 до 9999
3. Способ третий - функция ТЕКСТ (TEXT)
Функция ТЕКСТ (TEXT) применяется для преобразования числового значения в текстовый формат и отображения его в специальном формате. Поскольку, как мы уже узнали, даты в Excel хранятся в виде чисел, то это идеальный подопытный.
=ТЕКСТ(значение, которое нужно отформатировать; “код формата, который требуется применить”)
В нашем случае, форматируемое значение - это дата, а код формата - это один из вариантов, представленных в пункте выше.
Для примера воспользуемся кодом "ДДДД", чтобы отобразить день недели в виде "понедельник", . "воскресение":
На этом всё. Подписывайтесь, рассказывайте, если у вас возникают трудности - попробуем вместе разобраться, что не так. Любые другие вопросы по Excel также приветствуются - разберемся вместе.
При работе в Excel табличных данных часто не достаточно для наглядности информации. Для повышения информативности ваших данных советуем использовать графики и диаграммы в Эксель. В этой статье рассмотрим пример того, как построить график в Excel по данным таблицы.
Спасибо, файл успешно отправлен на указанный адрес почты. Проверьте папки "Рассылки", "Реклама" или "Спам".
Видеоурок
Как построить простой график в Excel
Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:
На основе этих данных нам нужно нарисовать график. Для этого нам потребуется:
Как построить график в Excel на основе данных таблицы с двумя осями
Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:
Для добавления данных курса Евро на наш график необходимо сделать следующее:
Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:
Ниже мы рассмотрим как улучшить информативность полученных графиков.
Как добавить название в график Эксель
На примерах выше мы строили графики курсов Доллара и Евро, без заголовка сложно понять про что он и к чему относится. Чтобы решить эту проблему нам нужно:
Как подписать оси в графике Excel
Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:
Как добавить подписи данных на графике Excel
Ваш график может стать еще более информативным с помощью подписи отображаемых данных.
На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:
Система отобразила курс Доллара на линии графика, но наглядность данных это не улучшило, так как значения сливаются с графиком. Для настройки отображения подписи данных потребуется сделать следующие шаги:
- Кликните правой кнопкой мыши на любом значении линии графика. В всплывающем окне выберите пункт «Формат подписей данных»:
В этом меню вы сможете настроить положение подписи, а также из чего будет состоять подпись данных: из имени ряда, категории, значения и.т.д.
Помимо настроек расположения, в том же меню вы сможете настроить размер подписей, эффекты, заливку и.т.д:
Еженедельный график продаж - очень часто бывает полезным в отчетах Excel при объединении сразу двух графиков с разными таймфреймами. Например, чтобы на одном графике отображались ежедневные и еженедельные показатели одновременно. Это позволит выполнить технический анализ продаж с использованием нескольких таймфреймов за один и тот же период времени (например, за месяц). Как же красиво визуализировать данные в таком случае, чтобы не получилась «каша-мешанка»?
Еженедельный и ежедневный таймфрейм на одном графике в Excel
Перед тем как сделать еженедельный график продаж в Excel сначала подготовим входящие данные. Для примера возьмем статистику продаж (в штуках) по 5-ти торговым агентам за Март месяц 2020-го года и разместим их показатели на отдельном листе «Данные»:
Получилась таблица в диапазоне B2:H33. Но для качественного составления еженедельного графика продаж в Excel нам потребуется составить еще одну интерактивную таблицу. Разместим ее на листе «График»:
Название заголовков столбцов и строк таблицы вполне информативны и не требуют разъяснений, за исключением последнего столбца «Низ Линии». Он не содержит никаких формул (в отличии от столбца «ИТОГО»), а только лишь нулевые значения, которые нам потребуются в еженедельном графике продаж.
Как упоминалось выше, данная табличка будет интерактивной и при взаимодействиях с пользователем будет изменять свои значения по условию. Пользователь укажет порядковый номер недели в месяце, а таблица автоматически заполнится соответственными значениями выбрав их из статистики продаж на листе «Данные».
Автозаполнение ячеек при выборки данных по нескольким условиям
Соответственно еженедельный график продаж также получится динамическим. Для взаимодействия таблицы с пользователем будет использовать только лишь один элемент управления – выпадающий список. С помощью него пользователь отчета получит возможность указывать порядковый номер недели в Марте месяце. Чтобы создать выпадающий список перейдите курсором Excel в ячейку G1 и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»:
В появившемся диалоговом окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выбираем опцию «Список». В поле ввода «Источник:» через точку с запятой указываем порядок чисел 1-5. Так как в каждом месяце (за исключением февраля не високосного года) всего по 5 неполных недель.
Теперь необходимо решить самую сложную задачу – это выборка значений. Для автоматического заполнения каждой ячейки интерактивной таблицы необходимо делать выборку из таблицы входящих данных на отдельном листе сразу по 3-м условиям:
- Необходимо получить значение из определенного порядкового номера недели месяца.
- Для определенного торгового агента.
- В определенный день недели.
Более того задачу еще усложняет тот факт, что две взаимодействующих таблицы по-разному транспонированы. Кроме того, для простоты усвоения материала из данного урока не хотелось бы прибегать к сложным формулам массива, а лишь воспользоваться простой и читабельной функцией ВПР для выборки значений из таблицы по нескольким условиям. Ведь вся сила в простоте!
Но есть изящное решение, которое часто применяется при работе с базами данных – это генерация логически обоснованного кода ID для каждой строки входящих статистических данных. Для этого заполним специальной формулой дополнительный столбец в диапазоне ячеек A3:A33 на листе «Данные»:
В чем логика сгенерированных ID кодов для каждой строки? Логический ID код состоит из двухзначных чисел:
- Первое число это порядковый номер недели в месяце для каждой даты в таблице.
- Второе число это порядковый номер дня недели для каждой даты.
Формула для генерации логического ID кода состоит одновременно из двух формул, возвращаемые значения которых сцепляются знаком амперсант - «&»:
- Формула в первой части (до символа &) возвращает номер недели месяца для даты: =ОТБР(ДЕНЬ(B3)/7)+1.
- Во второй части функция: =ДЕНЬНЕД(B3;2) с числом 2 во втором своем аргументе, благодаря которому указывается понедельник как первый день недели (европейский формат).
Таким образом мы объединяем два условия в одно и теперь для выборки значений из таблицы входящих данных нам потребуется только 2 условия которые мы будем использовать в функции ВПР.
Более того, обратите внимание на то, что первый столбец значений в просматриваемой таблице уже отсортирован по возрастанию, а значит не возникнет никаких проблем при обработке таблицы функцией ВПР. В целом это самое простое идеальное решение. Простота залог надежности!
Теперь нам нужно заполнить формулой диапазон ячеек B3:F8 в интерактивной таблице на листе «График»:
В основе формулы лежит функция ВПР, а за работу с условиями отвечает функция ПОИСКПОЗ, которая возвращает порядковые номера: дней недель и столбцов. В первом аргументе функции ВПР объединены 2 входящих параметра для образования одного условия – порядковый номер недели в месяце и номер дня недели (определяет по наименованию: понедельник-1, вторник-2 и т.д.). А в третьем аргументе функции ВПР используется функция ПОИСКПОЗ для определения порядкового номера столбца таблицы входящих данных из которого будут получены значения в соответствии с порядковым номером имени торгового агента. Все гениальное в простом!
Все входящие данные подготовлены и обработаны, сразу переходим непосредственно к их визуализации еженедельным графиком продаж.
Как сделать еженедельный и ежедневный график два в одном?
Чтобы сделать еженедельный график продаж в Excel выполните несколько рядов последовательных действий:
- На первом листе выделите диапазон ячеек A2:H8 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Гистограмма»:
- В дополнительном меню щелкните по переключателю: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные»-«Строка/столбец»:
- В этом же меню воспользуйтесь инструментом: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы». Там же следует выбрать тип – «Комбинированная», а для двух последних рядов: «ИТОГО» и «Низ Линии» указываем тип – «График»:
- Добавляем новый элемент нажав на плюс и из появившегося меню «ЭЛЕМЕНТЫ ДИАГРАММЫ» отмечаем галочкой опцию «Полосы повышения и понижения»:
- Делаем двойной щелчок по зеленой линии ряда «ИТОГО» и изменяем параметр: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» на 30%:
- Делаем двойной щелчок левой кнопкой мышки по любой серой полосе и настраиваем цвет: «Формат полосы понижения»-«ПАРАМЕТРЫ ПОЛОС ПОНИЖЕНИЯ»-«Заливка»-«Градиентная заливка» и там настраиваем цвета и положения «Точки градиента». При том для правой точки должен быть установлен не только белый цвет, а также «Прозрачность»-100%. И в этом же разделе параметров в списке опций «ГРАНИЦЫ» отмечаем опцию «Нет линий».
- Снова делаем двойной щелчок по зеленой линии ряда «ИТОГО» чтобы его предварительно выделить, а затем нажав на кнопку плюс добавляем новые элементы: «Подписи данных»-«Сверху»:
- Одним щелчком по любой подписям данных мы выделяем их все, а далее правой кнопкой пышки по подписи вызываем контекстное меню из которого выбираем опцию «Изменить формы меток данных»-«Прямоугольная выноска»:
- Пока не снимая выделения с подписей выбираем опцию из дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Сильный эффект – Синий, Акцент 5»-«Заливка фигуры»-«Цвет заливки»-«Лиловый»:
- Периодически делаем двойной щелчок для выделения и нажимаем клавишу Delete на клавиатуре для удаления элементов легенды графика: ИТОГО и Низ Линии. А затем периодически делаем двойной щелчок для выделения и изменяем параметры линейных графиков ИТОГО и Низ, чтобы скрыть их линии (но не удалить из): «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЛИНИЯ»-«Нет линий»:
- Выделяем фоновую сетку графика и снова нажимаем на кнопку добавления элементов – плюс «+». В появившемся контекстом меню открываем выпадающее меню с опции «Сетка» и отмечаем там все опции галочками:
- Двойным щелчком левой кнопкой мышки по вертикальным полоскам сетки выделяем их и одновременно вызываем таким образом меню, где изменяем параметры: «Формат основных линий сетки»-«ПАРАМЕТРЫ ОСНОВНЫХ ЛИНИЙ СЕТКИ»-«Линия»-«Сплошная линия», а также «Цвет» - черный.
- Щелкаем левой кнопкой мышки по «Название диаграммы» далее в строке формул вводим знак равно «=» и кликаем по ячейке A1, а затем подтверждаем нажатием клавишей Enter на клавиатуре, чтобы привязать название графика к значению ячейки A1:
Шаблон еженедельного графика продаж с двумя динамически синхронизированными таймфреймами – ГОТОВ! При изменении значения в ячейке G1 с помощью выпадающего списка пользователь переключает порядковый номер недель в месяце на графике. В результате синхронно изменяются значения таблицы и показатели столбцов двух таймфермов (недельный и ежедневный) на графике. Перерасчеты в интерактивной таблице выполняются автоматически, а данные на графике изменяются динамически – соответственно.
Визуализация данных, прекрасный инструмент из арсенала Excel.
Сделать график в Excel просто, главное знать последовательность.
Если вы первый раз столкнулись с такой задачей, статья для вас. Уже сталкивались? Дочитайте до конца, всегда есть вариант найти для себя новые фишки.
И так, для того чтобы построить график, нужен некоторый набор (числовых) данных. Это первое и главное условие для решения задачи.
Отлично, у нас есть данные, по которым мы хотим построить график продаж по месяцам, например:
Способ расположение данных не играет значения, можно и так:
Далее, мы для себя должны определиться, как визуально представить данные. От этого зависит тип графика или диаграммы, которых существует множество, подходящего под конкретную задачу.
Для примера, построим простой график, в виде линии, всем знакомый со школы.
Как построить простой график в Excel
Для этого, выделите данные и на вкладке Вставка (Диаграммы) , нажмите на пиктограмму подходящей для вас диаграммы или выберите Рекомендуемые диаграммы :
Чтобы быстро построить график, на текущем листе, выберите данные и нажмите Alt+F1 , на отдельном F11 . По умолчанию построится гистограмма, далее тип диаграммы вы можете поменять на вкладке Конструктор или Вставка .
Вернемся к примеру. При выборе типа графика, Excel мгновенно строит и вставляет на лист, заданную нами визуализацию:
Собственно, график готов:
Дальше, можно украсить график: настроить цвет, толщину линии, размер текста и расположение информации на осях графика, добавить легенды, вывести метки с данными и т.д.
Быстро настроить красоту, можно на вкладке Конструктор , которая станет активна, если выделить график:
Для форматирования и настройки отдельных элементов графика, можно вызвать окно (появится справа), двойным щелчком левой кнопки мыши по нужному элементу:
Или на вкладке Формат :
Диаграммы и графики Excel — динамические, т.е. связаны с данными рабочего листа книги. При изменении данных, диаграмма автоматически обновится в соответствии с новыми значениями, поэтому графические панели всегда отображают текущее состояние исходных данных.
Большим плюсом является, то что можно всегда поменять тип диаграммы, добавить специальные атрибуты форматирования, дополнить новым рядом данных, изменить диапазоны, на которые ссылаются существующие ряды данных.
Как сделать диаграмму в Excel по нескольким диапазонам
Добавим данных в предыдущий пример, для построения диаграммы по нескольким рядам и нажмем Alt+F1 :
Если вам нужен календарь на листе Microsoft Excel, то у вас есть много разных способов - от кропотливого вбивания дат вручную до подключения всплывающих календарей из различных надстроек и макросов. Еще один вариант - реализовать универсальный календарь на любую дату с помощью всего одной (правда, весьма страшной с непривычки) формулы массива.
Чтобы ее использовать создайте на листе заготовку вот такого вида:
Дата в ячейке B2 может быть любой, тут важен только месяц и год. В ячейках диапазона B3:H3 могут быть названия дней недели в любом подходящем формате.
Теперь выделите диапазон B4:H9 и введите туда вот такую формулу:
В английском варианте это будет:
Затем нажмите сочетание Ctrl+Shift+Enter, чтобы ввести эту формулу как формулу массива. Все выделенные ячейки должны заполнится датами заданного в B2 месяца:
Останется только навести внешний блеск, добавив форматирование и скрыв день в заголовке B2 и месяц и год в остальных ячейках с помощью окна Формат ячеек (Ctrl+1):
Теперь, меняя дату в ячейке B2, мы будем получать по нашей формуле корректный календарь на любой выбранный месяц любого года. Практически, вечный календарь ;)
Ссылки по теме
зачем МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) ? достаточно ведь просто МЕСЯЦ(B2)
чуть короче, но по тому же принципу:
Как всегда, пришел МСН и все улучшил!
"Он придет и молча исправит все. " (с) БГ
Потому что у Уокенбаха ТАК
Кстати, да - только что проверил
Вопрос Николаю и МСН. Формулы элегантны и календарь выглядит красиво, но когда я пытаюсь подсветить текущую неделю в условном форматировании, подсветка срабатывает по американскому варианту и выделяет предыдущее воскресенье и текущую шестидневку или, если сегодня воскресенье, текущее воскресенье и грядущую шестидневку. Есть ли способ заставить условное форматирование воспринимать отечественный формат дней недели?
Ребята, а как сделать календарь на несколько лет, например, с 2005 по 2010 гг, чтобы я выбрала февраль 2007 года (месяц и год из выпадающего списка) и мне выдало календарь именно за этот месяц этого года. Года менять у меня получилось, а вот при выборе месяца, календарь не меняется.
Изящно
календарь в одну формулу, это круто. у меня так не получилось бы. тут как-то можно прикрепить файл excel для вопроса?
на прошлой неделе просто загорелся excel'ем, когда посмотрел ролики в интернете (и даже подписался на planetaexcel на youtube), был честно поражен тем сколько всего можно сделать (в универе как-то не так было интересно и понятно). на этой волне решил помочь кадровику и создал "табель рабочего времени". выставляешь дату начала месяца и он разлиновывает/выделяет выходные дни, также создал колонку праздники и он тоже учитывает их. но вот столкнулся с проблемой, а как быть когда праздник выпадает на выходной, он же куда-то переносится. но это еще можно отследить. а вот когда в выходной работаем, его выделять не нужно. короче проблема производственного календаря. возможно на все года это делать нет смысла, но создать доп колонку дней исключений определенного года или т.п. можете подсказать в моём варианте это как-то можно реализовать? сложно объяснить алгоритм, хотелось бы показать сам файл, но не знаю как прикрепить. (сразу оговорюсь, файл .xslm содержит макрос, который прячет/показывает не нужные столбцы из таблицы, которые возникают из-за не одинакового количества дней в месяцах).
Доброго времени суток Николай! Для начало хотелось бы выразить свою благодарность за ответ на мой вопрос и сказать большое спасибо за сайт и возможность черпать необходимую информацию с Вашего сайта!
И вопрос: можно ли создать календарь где в каждой ячейке с датой находилась бы информация т.е таблица excel с проделанной работой или шаблоны с этой таблицей? Для удобства дальнейшего их архивирования и для востоновления сценариев проделанной работы по мере необходимости. Думаю было бы очень удобно в разворачиваемом окне календаря иметь необходимую для работы таблицу.Зарание спасибо!
Здравствуйте! У меня есть вопрос, как нужно написать формулу имея дату начала месяца, чтобы в верхних ячейках прописались все даты месяца(1янв, 2янв и т.д.), а в ячейках под датами соответствующие дни недели? Благодарю за ответ
Добрый день, Николай. Огромное спасибо за сайт. Прошу помочь с очень банальной вещью. У меня смещенный график работы, поэтому чтоб ыто-то планировать нужно считать дни. Хотелось бы автоматизировать этот процесс в экселе. Мой график работы: 4 дня подряд работаю утром (выделить каким-то цветом), потом 2 дня отдыхаю . Далее 4 дня работаю днем (выделить другим цветом), потом 2 дня отдыхаю . и снова 4 - утро, 2 выходных, 4 - днем, 2 выходных, такой цикл. Помогите наложить этот цикл на календарь в цветах. Заранее благодарю.
Здравствуйте, я тоже ищу возможность автоматической окраски выходных дней календаря. строю график из месяца в месяц, хотелось бы его автоматизировать. Заранее благодарен за наводку
Читайте также: