Ref-sgain.ru

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

Как создать динамическую диаграмму между двумя датами (на основе дат) в Excel?

Как в Excel вставить текущую дату и время, как найти количество дней между датами (в т.ч. рабочих)

excel-segodnyaДоброго дня!

В некоторых случаях при работе с документами Excel действительно необходимо ставить текущую дату: например, вы часто составляете отчеты на одном и том же бланке (глаз со временем «пачкается», и они могут перепутаться); или, скажем, у вас есть дюжина или около того задач, и вы каждый день просматриваете их, чтобы узнать, как продвигаются дела и сколько дней осталось до их выполнения.

Для решения всех этих проблем в Excel существуют специальные формулы. Собственно, это и есть тема сегодняшней статьи.

Примечание: скриншоты в статье сделаны из Excel 2019 (то же самое касается 2016, 2013).

Дополнения !

Если с вашими документами иногда работают посторонние люди, рекомендую некоторые ячейки защищать от изменения — https://ocomp.info/zashhita-yacheek-v-excel.html

Использование дат в программе Excel

ШАГ 1: вставьте текущую дату

Используйте функцию «TDATA()», чтобы поместить текущую дату и время в ячейку (просто скопируйте функцию в любую ячейку Excel без кавычек, конечно). См. пример ниже.

Формула "ТДАТА()"

В качестве альтернативы используйте формулу «TODAY()». Как и предыдущий метод, она не устанавливает текущее время по умолчанию.

Формула "СЕГОДНЯ()"

S TEP 2: Сколько всего дней находится между двумя датами

Это ещё один популярный вопрос.

Общее количество дней между двумя датами можно найти по формуле » =DAY(B2;A2)». (где B2 и A2 — дата окончания и дата начала соответственно). Если вы знаете значение в днях, то, конечно, легко узнать значение в годах (просто разделите число на 365).

Ко-во дней посчитано

Количество учтенных суток

Кстати, если у вас есть ячейки в формате даты, вы можете сделать это еще проще: вы можете просто вычесть одну дату из другой (как если бы это были числа). А если в одной из ячеек есть время (как в моем примере) — Excel пересчитает до ближайшей десятой.

Второй вариант

S TEP 3: определить количество рабочих дней между датами

Может быть интересно определить не просто количество дней, а сколько дней у нас будет выходных, а сколько включенных.

В этом случае может помочь формула » =CHRABDNY(A2;B2) » (где A2 и B2 — начальная и конечная дата соответственно).

51 рабочий день

51 Рабочие дни

Кстати, если вам нужно выделить ячейки красно-зеленым цветом (чтобы сразу было видно строки, на которых, например, стоят сроки — рекомендую использовать инструмент условного форматирования в разделе Главная). Пример.

Правила выделения ячеек

Правила распределения ячеек

Она выглядит достаточно наглядно (например, задачи, до выполнения которых осталось менее 4 дней, выделены красным цветом). Удобно!)

Текущее время и дату можно обновить, нажав клавиши Ctrl+R.

Изучаем MySQL: работа с датами и временем

В данной статье мы рассмотрим основы работы с датой и временем в MySQL.

Формат даты и времени

Формат даты MySQL поддерживает несколько форматов даты и времени. Они могут быть определены следующим образом:

Читайте так же:
Как скопировать строки, если столбец содержит определенный текст / значение в Excel?

DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.

Создание полей даты и времени

Таблица, содержащая типы данных DATE и DATETIME, создается так же, как и другие столбцы. Например, мы можем создать новую таблицу под названием Orders, содержащую столбцы для Order Number, Ordered Item, Order Date и Delivery Date:

Столбец ORDER_DATE — это поле типа MySQL DATE TIME , в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, поэтому мы записываем только дату.

Форматы даты и времени

Наиболее часто используемым разделителем для дат является тире ( — ), а для времени — двоеточие ( : ). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.

В приведенных ниже примерах все форматы корректны:

Функции даты и времени

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

ФункцияОписание
ADDDATE()Добавляет дату.
ADDTIME()Добавляет время.
CONVERT_TZ()Конвертирует из одного часового пояса в другой.
CURDATE()Возвращает текущую дату.
CURTIME()Возвращает текущее системное время.
DATE_ADD()Добавляет одну дату к другой.
MySQL DATE FORMAT ()Задает указанный формат даты.
DATE()Извлекает часть даты из даты или выражения дата-время.
DATEDIFF()Вычитает одну дату из другой.
DAYNAME()Возвращает день недели.
DAYOFMONTH()Возвращает день месяца (1-31).
DAYOFWEEK()Возвращает индекс дня недели из аргумента.
DAYOFYEAR()Возвращает день года (1-366).
EXTRACTИзвлекает часть даты.
FROM_DAYS()Преобразует номер дня в дату.
FROM_UNIXTIME()Задает формат даты в формате UNIX.
MySQL DATE_SUB ()Вычитает одну дату из другой.
HOUR()Извлекает час.
LAST_DAYВозвращает последний день месяца для аргумента.
MAKEDATE()Создает дату из года и дня года.
MAKETIME ()Возвращает значение времени.
MICROSECOND()Возвращает миллисекунды из аргумента.
MINUTE()Возвращает минуты из аргумента.
MONTH()Возвращает месяц из переданной даты.
MONTHNAME()Возвращает название месяца.
NOW()Возвращает текущую дату и время.
PERIOD_ADD()Добавляет интервал к месяцу-году.
PERIOD_DIFF()Возвращает количество месяцев между двумя периодами.
QUARTER()Возвращает четверть часа из переданной даты в качестве аргумента.
SEC_TO_TIME()Конвертирует секунды в формат ‘ЧЧ:MM:СС’.
SECOND()Возвращает секунду (0-59).
MySQL STR TO DATE ()Преобразует строку в дату.
SUBTIME()Вычитает время.
SYSDATE()Возвращает время, в которое была выполнена функция.
TIME_FORMAT()Задает формат времени.
TIME_TO_SEC()Возвращает аргумент, преобразованный в секунды.
TIME()Выбирает часть времени из выражения, передаваемого в качестве аргумента.
TIMEDIFF()Вычитает время.
TIMESTAMP()С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов.
TIMESTAMPADD()Добавляет интервал к дате-времени.
TIMESTAMPDIFF()Вычитает интервал из даты — времени.
TO_DAYS()Возвращает аргумент даты, преобразованный в дни.
UNIX_TIMESTAMP()Извлекает дату-время в формате UNIX в формат, принимаемый MySQL.
UTC_DATE()Возвращает текущую дату по универсальному времени (UTC).
UTC_TIME()Возвращает текущее время по универсальному времени (UTC).
UTC_TIMESTAMP()Возвращает текущую дату-время по универсальному времени (UTC).
WEEK()Возвращает номер недели.
WEEKDAY()Возвращает индекс дня недели.
WEEKOFYEAR()Возвращает календарную неделю даты (1-53).
YEAR()Возвращает год.
YEARWEEK()Возвращает год и неделю.
Читайте так же:
Как скопировать выпадающий список Excel в документ Word?

Вы можете экспериментировать с этими функциями формата даты MySQL, не вводя никаких данных в таблицу. Например:

Вы можете объединить несколько функций в одном запросе (например, для определения дня недели):

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

Давайте посмотрим, как значения дат вводятся в таблицу MySQL. Чтобы продемонстрировать это, мы продолжим использовать таблицу команд, которую мы создали в начале этой статьи.

Мы начнем с добавления новой строки заказа. Значение поля order_no будет автоматически увеличиваться на 1, так что нам остается вставить значения order_item , дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, поэтому мы можем использовать функцию NOW() , чтобы внести в строку текущую дату и время.

Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя функцию MySQL DATE ADD() , которая принимает в качестве аргументов дату начала ( в нашем случае NOW () ) и INTERVAL ( в нашем случае 14 дней ). Например:

Запрос создает заказ на указанный товар с указанием даты, времени и даты доставки через две недели после этого:

Также можно заказать товар с датой доставки в течение двух месяцев:

Извлечение данных по дате и времени

Наша база данных позволяет нам фильтровать извлеченные данные по дате и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:

Аналогично, мы можем использовать BETWEEN , чтобы выбрать товары, которые должны быть доставлены между двумя указанными датами. Например

Заключение

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

Диаграммы в Excel

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

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

  • Вставка и построение;
  • Элементы диаграммы;
  • Стили;
  • Типы диаграмм:
    • График;
    • Гистограмма;
    • Круговая диаграмма;
    • Диаграмма области;
    • Точка;
    • Область;
    • Переход;
    • Лепесток.

    Вставка и построение

    Например, мы будем использовать таблицу доходов и расходов за год для построения простого графика:

    янв.13фев.13мар.13апр.13май.13июн.13июл.13авг.13сен.13окт.13ноя.13дек.13
    Выручка150 598р.140 232р.158 983р.170 339р.190 168р.210 203р.208 902р.219 266р.225 474р.230 926р.245 388р.260 350р.
    Затраты45 179р.46 276р.54 054р.59 618р.68 460р.77 775р.79 382р.85 513р.89 062р.92 370р.110 424р.130 175р.

    Какой бы тип диаграммы ни использовался, будь то гистограмма, диаграмма площадей и т.д., основной принцип один и тот же. На вкладке «Вставка» в Excel выберите раздел «Диаграммы» и нажмите на нужный значок.

    Пиктограмма вставки графика

    Выделите пустую область, которая была создана для отображения нескольких вкладок. Один из них называется ‘Constructor’ и содержит область ‘Data’, которая содержит элемент ‘Select Data’. При нажатии на него открывается окно выбора источника:

    Выбор источника данных для диаграммы

    Обратите внимание на первое поле «Диапазон данных для графика:». С его помощью можно быстро создать график, но приложение не всегда может понять, как именно пользователь хочет его видеть. Итак, давайте рассмотрим простой способ добавления линий и осей.

    Вы можете добавить элементы легенды в поле «Элементы легенды», нажав кнопку «Добавить». Появится форма «Изменить строку», на которой необходимо задать ссылку на название строки (не обязательно) и значения. Вы можете указать все значения вручную.

    Добавления ряда данных диаграммы

    Если после ввода соответствующей информации нажать «OK», на диаграмме появится новый ряд. Из нашей таблицы мы добавим еще один элемент легенды.

    Замените автоматически добавленные подписи на горизонтальной оси. В окне выбора данных есть область категорий, а в ней кнопка «Изменить». Пожалуйста, нажмите ее и добавьте в форму ссылку на этот диапазон подписей:

    Изменение подписей горизонтальной оси

    Чтобы узнать, что нужно делать, чтобы спастись

    Пример построенного графика в Excel 2007

    Элементы диаграммы

    Диаграмма по умолчанию состоит из следующих элементов:

    • Линии данных — имеют основное значение, так как используются для визуализации данных;
    • Легенда — содержит названия линий и пример их расположения;
    • Оси — шкала с некоторым значением промежуточных делений;
    • Область построения — является фоном линий данных;
    • Линии координатной сетки.

    Помимо вышеперечисленных пунктов, могут быть и другие, например:

    • Заголовки диаграмм;
    • Проекционные линии — линии, выходящие из серии данных по горизонтальной оси;
    • Линия тренда;
    • Обработка данных — числовое значение для точки данных в серии;
    • И другие элементы, которые используются нечасто.

    Объекты диаграммы

    Изменение стиля

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

    Часто имеющихся шаблонов достаточно, но если вы хотите большего, вам нужно выбрать свой собственный стиль. Это можно сделать, щелкнув правой кнопкой мыши на изменяемом объекте графика, выбрав в меню «format element_name» и изменив параметры через диалоговое окно.

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

    С помощью экспресс-макетов приложения, которые находятся на той же вкладке, вы можете быстро перестроить структуру.

    Выбор экспресс макета диаграммы

    Как и в случае со стилями, каждый элемент может быть добавлен или удален по отдельности. В Excel 2007 для этого есть дополнительная вкладка Design, но в Excel 2013 эта функциональность перенесена на ленту Designer, в область Chart Layouts.

    Добавление элементов к макету диаграммы

    Типы диаграмм

    График

    Идеально подходить для отображения изменения объекта во времени и определения тенденций.
    Пример отображения динамики затрат и общей выручки компании за год:

    Пример диаграммы типа График

    Гистограмма

    Хорошо подходит для сравнения нескольких объектов и изменения их отношения со временем.
    Пример сравнения показателя эффективности двух отделов поквартально:

    Пример гистограммы в Excel

    Круговая

    Предназначения для сравнения пропорций объектов. Не может отображать динамику.
    Пример доли продаж каждой категории товаров от общей реализации:

    Пример круговой диаграммы

    Диаграмма с областями

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

    Предположим, есть необходимость показать загруженность отдела продаж и штатное расписание. Для этого данные о мощности и загруженности сотрудников были приведены к единой шкале.

    Поскольку нас больше интересует потенциал, эта линия представлена первой. На диаграмме ниже показано, что с 11 утра до 4 утра служба не справляется с потоком клиентов.

    Пример диаграммы с областями

    Точечная

    Представляет собой систему координат, в которой положение каждой точки задается значениями на горизонтальной (X) и вертикальной (Y) осях. Если значение (Y) объекта зависит от определенного параметра (X), то он хорошо подходит.

    В следующем примере показаны тригонометрические функции:

    Точечная диаграмма

    Поверхность

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

    Пример диаграммы типа поверхность

    Биржевая

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

    Эти графики обычно показывают коридор колебаний (пиковые и впадины) и конечное значение в определенные периоды времени.

    Пример биржевой диаграмма

    Лепестковая

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

    На приведенной ниже диаграмме эти 3 организации сравниваются по 4 направлениям: доступность, цены, качество продукции, ориентация на клиента. Вы можете видеть, что компания X превосходит в первой и последней областях, компания Y — в качестве продукции, а компания Z имеет лучшие цены.

    О компании X также можно сказать, что она является лидером, поскольку площадь ее номера на диаграмме наибольшая.

    Пример лепестковой диаграммы

    Смешанный тип диаграмм

    Excel позволяет объединить несколько типов в одной диаграмме. В качестве примера объединим тип графика и тип гистограммы.

    Первоначально все ряды строятся с использованием одного представления, затем оно меняется для каждого ряда отдельно. Щелкните правой кнопкой мыши на выбранной строке и выберите «Изменить тип диаграммы для строки…». из списка выберите «Гистограмма».

    Изменения типа диаграммы для отдельного ряда данных

    Иногда, из-за сильных различий в значениях рядов графика, невозможно использовать одну шкалу. Но можно добавить альтернативную шкалу. Перейдите в меню «Формат рядов данных.» и в разделе «Параметры рядов» переместите флажок на пункт «По вспомогательной оси».

    Добавление вспомогательной оси

    Теперь схема выглядит так:

    Пример диаграммы со смешанным типом

    Тренд Excel

    Каждый ряд графика может быть настроен на свой тренд. Они необходимы для определения основного направления (тренда). Однако в каждом конкретном случае должна использоваться своя модель.

    Выделите линию данных, для которой вы хотите установить тренд, и щелкните правой кнопкой мыши. В появившемся меню выберите «Добавить линию тренда…».

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

    • Экспоненциальный тренд. Если значения по вертикальной оси (Y) возрастают с каждым изменением по горизонтальной оси (X).
    • Линейный тренд используется, если значения по Y имеют приблизительно одинаковые изменения для каждого значения по X.
    • Логарифмический. Если изменение по оси Y замедляется с каждым изменениям по оси X.
    • Полиномиальный тренд применяется, если изменения по Y происходят как в сторону увеличения, так в уменьшения. Т.е. данные описывают цикл. Хорошо подходит для анализа большого набора данных. Степень тренда выбирается в зависимости от количества пиков циклов:
      • 2 класс: пик, т.е. половина цикла;
      • 3 класс: полный цикл;
      • 4 класс: полтора цикла;
      • , и т.д.

      Линейная фильтрация. Не применимо к прогнозам. Используется для сглаживания изменений в Y. Вычисляет среднее изменение между точками. Если параметр точки в настройке тренда установлен на 2, то среднее значение рассчитывается между соседними значениями на оси X, если 3, то через одно, 4 через — два и т.д.

      Сводная диаграмма

      Она обладает всеми преимуществами традиционных графиков и электронных таблиц без необходимости их создания.

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

      • Выберите сводную таблицу;
      • Перейдите на вкладку Анализ (в Excel 2007 — на вкладку Параметры);
      • Нажмите на значок Сводная диаграмма в группе Служебная.

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

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

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