В excel сравнение даты

Работа с датами в Excel. Устранение типовых ошибок

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

Что такое даты для Excel?

Дата и Время — это числа для Excel. Целая часть — номер дня, а все что идет после запятой — время. Если перевести в разные форматы, получим следующее:

То есть 12.07.2016 12:50:30 для Excel значение — 42563,5350694,

Где 42563 — это порядковый номер дня с 1 января 1900 года, а часть после запятой — это время.

С датами можно производить вычисления. Например, вычитать или складывать даты.

Даты нужны для группировки ежедневных данных в недельные, месячные и годовые отчеты. Пример группировок различных рекламных каналов по дате:

Основные ошибки с датами и их решение

Перевод разных написаний дат

Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:

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

Дата определяется как текст

Проблемы с датами начинаются, когда мы импортируем данные из других источников. При этом, даты могут выглядеть нормально, но при этом являться текстом. С ними нельзя проводить вычисления, группировки в сводных таблицах, сортировать. Есть 2 решения, которые позволяют сделать это автоматически: 1. Получение значения даты; 2. Умножение текстового значения на единицу.

Получение значения даты

С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:

Умножение текстового значения на единицу

Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:

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

Очистка дат от некорректных символов

Чтобы привести к нужному стандарту, часть дат можно очистить с помощью функции Найти и Заменить. Например, поменять слэши (“/”) на точки:

То же самое можно сделать формулой ПОДСТАВИТЬ

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

Источник: needfordata.ru

Сравнение дат на excel

Я знаю, что это, вероятно, очень просто, но я изо всех сил пытаюсь найти формулу, которая работает:

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

2 Ответа

  1. Выберите даты, которые вы хотите выделить:

  1. Нажмите на HOME , а затем в области меню Стили выберите Условное форматирование

  1. Выберите Новое Правило

  1. Выберите Форматировать только ячейки, которые содержат , и вы можете играть с меньше, чем и больше, чем и поставить формат для каждого.

  1. В конце у вас может быть что-то вроде этого, y используйте ячейку D2 для сравнения:

Вы можете сделать это с помощью простого логического AND (его проще в Excel, если вы умножаете).

Представляют отработанные дни сотрудника в одной строке, по одной ячейке в день. Для года это 365 (366) дней. Если сотрудник здесь, то его ‘1’;

Сделайте то же самое для каждого проекта. Если проект “one”, то его ‘1’.

Затем на третьем листе (например, один лист на проект) вы умножаете сотрудника на проект. Ваши совпадения будут представлены в виде 1 , которые вы можете раскрасить с помощью функций форматирования.

Таким образом, ваша функция для B2 в вашем листе “Персонал для проекта 1” будет:

Вы можете “drag” эту формулу, чтобы иметь его на весь год и весь персонал.

Тогда ваш B2 в вашем листе “Персонал для проекта 2” будет:

и так далее. $3 приводит формулу к “stay” в той же строке, когда вы “drag” формулу.

Вот как это будет выглядеть:

Теперь ваша проблема становится: как я могу сделать мои списки дат генерировать вкладки staff и projs

Похожие вопросы:

Мне нужно сравнить две даты d1= 12-11-01(YY-MM-DD) and d2=2008-05-02(YYYY-MM-DD) сравнение d2 = todays_date Я спросил человека, который дал мне его, почему сравнение дат.

Если я помещу дату 31/12/2013 в A1 и другую дату 1/1/2014 в A2, то формула, как =A1
excel: vba несоответствие типа сравнение дат

Я пытаюсь сделать сравнение между годами из двух дат (в разных столбцах) и датой текущего года. Если год один и тот же, то он должен написать в соответствующей строке в столбце 13 слово ATUAL, если.

Читайте также:  Как в excel выделить повторяющиеся значения

Я пытаюсь сделать сравнение дат в javascript. Через вызов веб-службы я получаю дату в виде 2014-07-02T09:49:49.299Z и из базы данных я получаю данные, такие как2014-07-11 16:01:34 .I нужно сравнить.

у меня путаница с сравнением дат в родном javascript или jquery, может кто-нибудь указать мне, как я могу быстро сделать сравнение с сегодняшней датой в формате ISO-8601

Источник: coderoad.ru

Функции для работы с датами в Excel: примеры использования

Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

  • начальная дата;
  • конечная дата;
  • код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

  • для отображения результата в днях – «d»;
  • в месяцах – «m»;
  • в годах – «y»;
  • в месяцах без учета лет – «ym»;
  • в днях без учета месяцев и лет – «md»;
  • в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

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

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

Источник: exceltable.com

В excel сравнение даты

Крайне часто возникает задача сравнения числовых показателей на временной шкале с целью выяснения тенденций роста/падения показателей и характеристик этих процессов. Если вам интересна эта тема, то давайте об этом поговорим.

Сравнивать числа могут все, на первый взгляд тут нет ничего сложного. Ну, к примеру, доходы вашей компании за апрель были 500 тысяч рублей, а за май 575 тысяч. Соответственно наблюдаем рост на 75 тысяч, то есть на 75/500 = 15%. Куда уж проще! Сразу же все мастрячат табличку, напоминающую эту:

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

Каждый месяц вы будете вручную добавлять 3 колонки

Добавление колонок трудоёмкое (редактируем названия столбцов, проверяем формулы, а с умной таблицей ещё муторнее)

Непонятно, что делать в конце года

Крайне тяжело трансформировать такие таблицы, например, добавить ещё какую-то характеристику процесса

Неудобно строить диаграммы, так как столбцы перемешаны

Читайте также:  Как в excel округлить до целого числа

В общем неудобно – постоянно надо что-то подкручивать и охорашивать, вместо того, чтобы тупо добавить новые данные. Это плата за простоту и быстрый старт 🙂

Рациональный способ

Вот и я попался в эту ментальную ловушку, когда стал анализировать популярность своих статей на этом сайте. Там цифры накопительные, то есть по какой-нибудь статье на 1 марта было 200 просмотров, а на 1 апреля будет 280. То есть цифра идёт нарастающим итогом (приращение в 80 просмотров за март). В продажах не так, но это мало что меняет. Ниже я буду обсуждать случай накопительных данных, а вы легко адаптируете его для данных обычных, если захотите.

Файл примера

Скачать

Исходные данные

Как не трудно догадаться, данные надо разделить. Исходные данные будем хранить в таком виде:

Обратите внимание, что заголовки столбцов представляют из себя даты. Эти даты не обязаны идти через равные промежутки времени. Это определяете вы. Однако заметим, что рост на 100 единиц за февраль не то же самое, что рост на 100 единиц за март. Рост за февраль интенсивнее, так как среднее количество роста за день (скорость роста) получается выше. Мы будем это учитывать.

Модель процесса

Поток числовых показателей обозначим как D1, D2, . Dn. Период времени между снятием показаний D2 и D1 обозначим как P1, между D3 и D2 – как P2 и так далее. С другой стороны Pn – это число, характеризующее длину периода в единицах измерения времени.

Скорость роста

Итак, если я возьму разницу, например, D5-D4 и разделю её на P4, то я узнаю, с какой скоростью изменялся показатель в единицу времени – то есть скорость изменения. Для моей задачи:

Формула для первой строки выглядит так:

Тут Articles – это умная таблица на листе Data. Таблица на листе Speed не требует никакого трудоёмкого обслуживания – просто протяните её вправо на необходимое количество периодов.

Скорость роста в %

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

Вербальная характеристика роста

Если у нас есть шкала, характеризующая скорость,

то мы можем ей воспользоваться так:

Смена уровней

Поскольку мы ввели вербальные уровни, то нам, возможно, будет интересно наблюдать факты изменения этих уровней:

Ускорение

Ну и гурманы могут отслеживать не только скорость изменений, но и ускорение, то есть скорость изменения скорости. Если скорость можно рассчитать для двух периодов, то ускорение можно рассчитать для трёх смежных периодов, так как нужно иметь 2 значения скорости.

Работа с результатами сравнения

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

Источник: perfect-excel.ru

8 способов как сравнить две таблицы в Excel

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Читайте также:  Как в excel преобразовать число в дату

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));””;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

Есть много способов проверить две таблицы на схожесть, но некоторые варианты возможно только с помощью макросов VBA. Макросы для того что бы сравнить две таблицы, унифицирует этот процесс и существенно сокращает затраченное время на подготовку данных. Исходя из решаемой вами задачи и знаний макросов VBA, вы можете создавать любые варианты макросов. Ниже я привел методику, указанную на официальной страничке Microsoft. Вам нужно создать модуль для кода VBA и ввести код:

Источник: topexcel.ru

В excel сравнение даты

445 просмотра

2 ответа

1 Репутация автора

Я знаю, что это, вероятно, очень просто, но я изо всех сил пытаюсь найти формулу, которая работает:

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

Ответы (2)

1 плюс

36 Репутация автора

  1. Выберите даты, которые вы хотите выделить:

  1. Нажмите ДОМОЙ, а затем в области меню Стили , нажмите Условное форматирование.

  1. Выберите новое правило

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

  1. В конце у вас может получиться что-то вроде этого: y используйте ячейку D2 для сравнения:

плюса

6440 Репутация автора

Вы можете сделать это с простой логикой AND (это легче в Excel, если вы умножаете).

Представлять рабочие дни сотрудника в одной строке, одна ячейка в день. В течение года это 365 (366) дней. Если сотрудник здесь, то это «1»;

Сделайте то же самое для каждого проекта. Если проект «один», то это «1».

Затем на третьем листе (например, по одному листу на проект) вы умножаете сотрудника на проект. Ваши совпадения будут представлены как 1 , которые вы можете раскрасить, используя функции форматирования.

Таким образом, ваша функция для B2 вашего ведомости « Персонал для проекта 1 » будет:

Вы можете «перетащить» эту формулу, чтобы она была на весь год и на весь персонал.

Тогда ваш « B2 Лист персонала для проекта 2 » будет:

и так далее. что $3 силы формула «остаться» на той же строке , когда вы «перетаскивания» формула.

Вот как это будет выглядеть:

Теперь ваша проблема становится: как мне сделать мои списки дат генерировать вкладки staff и projs

Источник: issue.life