000818.ru

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

Как найти самую раннюю или самую последнюю дату на основе критериев в Excel?

Получите самые ранние и самые свежие даты проекта

Этот пример демонстрирует, как получить самую раннюю и самую позднюю даты, связанные с проектом. Как показано в примере, формулы в H5 и I5 следующие:

Где «данные» — это электронная таблица Excel, как показано на рисунке, а названия проектов в колонке G соответствуют этим колонкам B.

Примечание: MINIFS и MAXIFS доступны только в Excel 365 и Excel 2019. В других версиях Excel можно использовать простую матричную формулу, подобную описанной ниже.

Введение

Этот процесс предназначен для поиска самых ранних и самых поздних дат, связанных с данным проектом. Из столбцов «Начало» и «Конец» берутся самые ранние даты.

Существует большая вероятность того, что вы захотите использовать такие функции поиска, как VLOOKUP, XLOOKUP или INDEX и MATCH. Однако, поскольку каждый проект имеет более одной записи, а записи не всегда можно отсортировать по дате, это становится сложной задачей.

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

Объяснение

Функция MINIFS возвращает наименьшее числовое значение, отвечающее заданным критериям, а функция MAXIFS — наибольшее числовое значение, отвечающее заданным критериям.

Как и SCATESLIMN и SUMMESLIMN, эти функции используют «пары» интервал/критерий для применения условий. Для обеих формул достаточно одного условия: название проекта должно совпадать с названием в столбце G:

Для самой ранней даты начала работы мы используем:

В приведенном ниже примере MINIFS возвращает минимальное значение в столбце Start, где проект — «Omega» (из ячейки G5). Даты в Excel — это просто числа, поэтому самая ранняя дата соответствует минимальной дате.

Читайте так же:
Как не перекрывать пузырьки в пузырьковой диаграмме в Excel?

Чтобы получить последний срок, мы используем :

M AXIFS возвращает максимальное значение в столбце «End», где «Omega» — это проект. Как указано выше, максимальное значение совпадает с последней датой.

Альтернатива формулы массива

Если MINIFS и MAXIFS недоступны, вы можете использовать простые матричные формулы на основе функций MIN и MAX для получения того же результата. Для самой ранней даты начала :

По состоянию на последнюю дату реализации :

Примечание: Обе формулы являются табличными формулами и должны быть введены с помощью Ctrl + Shift + Enter в Excel 2019 или более ранней версии. В Excel 365 формулы можно вводить обычным способом, поскольку матричные формулы являются родными.

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

Если G5 — «Omega», то IF возвращает дату окончания. В противном случае IF возвращает FALSE. Поскольку мы проверяем сразу все имена проектов в таблице, результатом будет массив значений, как показано ниже:

Большие серийные номера относятся к датам Excel для проекта Omega. Ни одно из других значений не является Omega, поэтому они FALSE. MIN и MAX запрограммированы так, чтобы игнорировать логические значения TRUE и FALSE и работать только с остальными значениями. Функция MIN возвращает наименьшую дату, а функция MAX — наибольшую.

Способ 1. Неделя содержит первый четверг года

Начнем с самого популярного варианта — подсчета количества недель в русском календаре.

Как мы уже выяснили, в России первой неделя должна содержать первый четверг года.
Для расчета можно воспользоваться следующей формулой:

Формула номера недели по дате

Алгоритм вычислений :

  • К вычисленной дате прибавляем 3 дня (напомню, что дата в Excel хранится в числовом виде) и вычитаем номер дня недели (1 для понедельника, 2 для вторника и т.д.); этим шагом мы увеличим вычисленную дату для понедельника, вторника и среды и уменьшим для остальных дней (четверг, пятница, суббота и воскресенье), что позволит нам включить первый четверг года в первую неделю.
  • Далее берем остаток от деления на 365,25 (средняя продолжительность года с учетом високосного года); этой операцией мы получим количество дней текущего года для вычисленной даты.
  • Разделите полученный результат на 7; переведите дни в недели.
  • Прибавьте 1 и возьмите целую часть числа.
Читайте так же:
Как найти общие значения в 3 столбцах в Excel?

Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):

НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.

  • Дата(обязательный аргумент) — код даты-времени.

Функция будет работать с теми же датами, что и в предыдущем примере:

Функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)

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

Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel

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

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

  1. Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
  2. В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
  3. В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
Читайте так же:
Как найти первое, последнее или n-е вхождение символа в Excel?

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

Многие не видят будущего без существования Microsoft Excel

У этой программы большое будущее, и дело не только в ее функциональности. Кроме того, крайне важно учитывать особенности человеческой психики и то, как формируются ментальные модели. То есть многим пользователям удобно работать с тем, что у них уже есть под рукой, даже если появляются более удобные инструменты. В таких вопросах существует инерция мышления — люди склонны отдавать предпочтение тому, что они хорошо знают. Большинство людей предпочли бы «Никомахову этику» Аристотеля философским размышлениям его современников, если бы вы положили их рядом с классикой.

Цикл продолжается из поколения в поколение. Используя труды Аристотеля в качестве примера, все больше и больше авторов создают новые произведения, используя хорошо известные труды. Они надеются, что их труды будут жить так же долго, как труды Аристотеля, но в конечном итоге он не исчезнет из истории. То же самое можно сказать и о Microsoft Excel.

Excel — величайшая компьютерная программа. Она никогда не умрёт, и вот почему

Не следует исключать и так называемый «эффект Линди». Согласно этой теории, если разработка или технология существует уже давно, то и просуществовать она может как минимум столько же. В данном случае речь идет о дополнительных 36 годах жизни Excel, и каждый год этот срок будет пропорционально увеличиваться. Продукт продолжает создавать сетевой эффект: одни пишут для него дополнения, другие используют его как основу для своих разработок. Да, именно так создаются традиции. Microsoft Excel остается одним из них, и менять это просто нет необходимости.

Удалить время из даты с помощью текста в столбцы

В большинстве случаев текст в столбцы используется для разделения текста в ячейке.

Читайте так же:
Как округлить диапазон ячеек в Excel?

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

Предположим, у вас есть набор данных, подобный приведенному ниже:

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

  • Выберите ячейки, из которых вы хотите удалить время
  • Перейдите на вкладку «Данные».
  • В группе «Инструменты для работы с данными» нажмите «Текст в столбцы».
  • В мастере преобразования текста в столбец выполните следующие действия:
  • Шаг 1 из 3. Выберите параметр «С разделителями»
  • Шаг 2 из 3. Выберите пробел в качестве разделителя.
  • Шаг 3 из 3. В разделе «Предварительный просмотр данных» щелкните столбец, содержащий значения времени, а затем выберите параметр «Не импортировать столбец (пропустить)». Сохраняйте ячейку назначения той же, где у вас есть данные
  • Нажмите Готово

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

Хотя время все равно будет отображаться с каждой датой (например, 12:00). Это происходит потому, что формат ячейки все еще заставляет указывать время,

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

  • Перейдите на вкладку «Главная»
  • В группе «Номер» щелкните раскрывающийся список.
  • Выберите Краткую дату (или Длинную дату) — в зависимости от того, как вы хотите отображать дату.

С помощью Text to Columns вы также можете получить результирующие данные в отдельных ячейках (сохранив исходные данные как есть). Шаг 3 мастера преобразования текста в столбцы требует указать целевую ячейку.

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

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

Расчет без игнорирования нулевых значений

Итого в Excel нужно вычислить среднее значение по столбцам с учетом нулевых параметров. В этом случае необходимо использовать опции C AVERAGE и IF. В этом случае сам запрос может выглядеть как =C AVERAGEIF(A2:A7;»<>0″).

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

Мне интересно узнать, помогли ли вам приведенные выше рекомендации, и знаете ли вы другие методы, которые можно использовать для решения этой задачи.

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