Auditportal.ru

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

Как найти дату предыдущего воскресенья в Excel?

Трюк №38. Трюки с возможностями даты и времени в Excel

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

По умолчанию в Excel используется система дат 1900. Это означает, что числовое значение даты 1 января 1900 года равно 1, даты 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями и позволяют использовать даты в вычислениях. Формат времени очень похож, но Excel рассматривает время как десятичную дробь, где 1 — это 24:00 или 00:00. Числовое значение 18:00 (в русском варианте) равно 0,75, так как соответствует трем четвертям 24 часов.

Чтобы найти числовое значение даты или времени, отформатируйте ячейку с этим значением с помощью общего формата. Например, дата и время (английская версия) 3/July/2002 3/July/2002 3:00:00 PM имеет числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

Используя функцию SUM или просто знак плюс, вы можете сложить времена. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне A1 :A5, если эти ячейки содержат действительные значения времени. Однако, если Excel не были даны специальные инструкции, он не будет суммировать время, превышающее 24 часа. Это происходит потому, что когда значение времени превышает 24 часа (текущее значение равно 1), оно перескакивает на новый день, и отсчет начинается заново. Если вы хотите заставить Excel не переходить на новый день через каждые 24 часа, вы можете использовать формат ячеек 37:30:55 или пользовательский формат [h]:MM:cc([h]:mm:ss).

Аналогичное форматирование можно использовать для получения общего количества минут или секунд. Чтобы получить общее количество минут, когда время 24:00, отформатируйте ячейку как [m] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [s] ([s]), и вы получите 86400.

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

Вычисление времени и даты

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и базовой информацией, гораздо легче манипулировать временем и датами. Давайте рассмотрим следующие примеры и посмотрим, что они означают (при условии, что время записано в ячейке A1). Если у вас есть число 5.50 и вы хотите получить 5.30 утра или 5.30 ночи, используйте следующую формулу: =А1/24. Необходимо указать соответствующий формат.

Если время должно выглядеть как 17:30 или 17:30, используйте формулу: =(А1/24)+0.5 .

Чтобы получить обратное значение, то есть десятичное время настоящего времени, используйте формулу =А1*24 .

Если ячейка содержит текущую дату и время (например, 22.01.03 15:36), а вам нужна только дата, используйте формулу: =INT(A1) , в русской версии Excel =CEEL(A1) .

Чтобы получить только время: =A1-INT(A1) , в русской версии Excel =A1-INT(A1) или =MOO(A1;1) , в русской версии Excel =OCTAT(A1;1) . Опять же, необходимо соответствующее форматирование.

Чтобы найти разницу между двумя датами, используйте формулу =DATEDIF(A1;A2; «d»), где A1 — более ранняя дата. Получите количество дней между двумя датами. В качестве результата можно также указать «m» или «y», что означает месяцы или годы. (Функция DATEDIF фактически не документирована в Excel 97 и является функцией Lotus 123).

Если предыдущая дата или время неизвестны, вам помогут функции MIN и MAX. Например, чтобы убедиться в правильности результата, можно использовать такую функцию: =DATEDIF(MIN(Al;A2); MAX(Al,A2), «d») В русской версии Excel: =DATEDIF(MIN(Al;A2);MAKC(A1;A2); «d»).

При работе со временем необходимо также учитывать время начала и время окончания. Например, время начала — 20:50 в ячейке A1, а время окончания — 9:50 в ячейке A2. Если вычесть время начала из времени окончания ( =A2-A1 ), то в ответе получится ######, поскольку Excel по умолчанию не работает с отрицательными значениями времени. Подробнее о том, как работать с отрицательными значениями времени, смотрите «Совет #74. Отображение отрицательных значений времени».

Читайте так же:
Как использовать ИНДЕКС и ПОИСКПОЗ вместе в Excel

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2) , в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1) , в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1) .

Вы также можете использовать Excel для добавления любого количества дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+значение1;MONTH(A1)+значение2;DAY(A1)+значение3). В русской версии Excel это =DATE(YEAR(A1)+значение1;,MONTH(A1)+значение2;DAY(A1)+значение3).

Добавить один месяц к дате в ячейке A1 можно с помощью формулы =DATE(ГОД(A1);МЕСЯЦ(A1)+1;ДЕНЬ(AD), или эквивалентно в русской версии =DATE(ГОД(A1);МЕСЯЦ(A1)+1;ДЕНЬ(A1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak. Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием. Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

Иногда в таблицах с импортированными данными (или данными, введенными неправильно) даты и время отображаются как текст, а не как настоящие числа. Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду Главная → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

Чтобы исправить эту ошибку, сначала скопируйте любую пустую ячейку, затем выделите столбец и отформатируйте его, выбрав любой формат даты или времени. Не снимая выделение столбца, выберите команду Главная → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время. Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Читайте так же:
Как назвать листы на основе значений ячеек (из списка) в Excel?

Ошибка даты?

Excel ошибочно полагает, что 1900 год был високосным (добавим, что это был последний год 19-го века, а не первый год 20-го). Это означает, что Excel считает, что 29 февраля 1900 года был день, но это не так! Компания Microsoft утверждает, что сделала это намеренно.

Excel: определить день недели по дате

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

Перелистывание сотен страниц календаря в поисках нужного числа может быть утомительным. Программа Excel может решить эту проблему легко, всего за несколько секунд. Как вы это делаете? Рассмотрим, как определить день недели в Excel.

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

1. определите день недели, используя форматирование данных.

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

Excel: определить день недели по дате

Формат ячейки можно изменить, выделив ячейку, в которую введены данные, и вызвав контекстное меню — «Форматировать ячейки».

Excel: определить день недели по дате

Выберите вкладку «Число», затем нажмите «Все форматы», затем введите параметр форматирования «DDDD». Операция будет применена.

Excel: определить день недели по дате

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

Excel: определить день недели по дате

Если вы введете, например, «DDD», вместо полного названия дня недели отобразится только его сокращение.

Excel: определить день недели по дате

Excel: определить день недели по дате

Для определения дня недели можно использовать функцию «ТЕКСТ».

Первый метод не может заставить данные появиться в определенной ячейке или диапазоне данных. Для этого необходимо использовать функцию «ТЕКСТ».

Давайте введем ту же дату, 9 апреля 2017 года, и вызовем «Мастер функций» для вставки нашего утверждения в следующую ячейку

Excel: определить день недели по дате

В мастере функций задайте категорию — выберите «Текст» и оператор «TEXT».

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

Excel: определить день недели по дате

В окно аргументов функции можно ввести только два аргумента: «значение» и «формат».

Введите адрес, по которому вы ввели исходные данные; в «Формат» — формат дня недели, например, «dddd» для отображения полного названия дня или «dddd» для сокращенного, как в предыдущем способе.

Затем можно применять функцию.

Excel: определить день недели по дате

С помощью оператора «TEXT» в выбранной ячейке отображается день недели, определенный исходными данными.

Excel: определить день недели по дате

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

Excel: определить день недели по дате

3. определите день недели с помощью функции «DAYNED» (ДЕНЬ недели).

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

Функция «DAYNED», в отличие от предыдущих методов, позволяет вывести на экран номер дня любой искомой даты, указав лишь, с какого дня ведется нумерация — с понедельника или воскресенья.

Давайте посмотрим на это с практической точки зрения. Укажите дату, для которой нужно определить день недели, и вставьте функцию с помощью «Мастера функций».

Excel: определить день недели по дате

Выберите категорию «Дата и время», а затем оператор «Dayened». Его функция выглядит следующим образом: «=DAYNED(date_in_numeric_format;[type])».

Приведем аргументы для функции «DAYNED» — вместо даты укажем адрес ячейки, содержащей дату, а в «тип» запишем число от 1 до 3 со следующими:

• «1» — нумерация начинается с воскресенья с порядковым номером «1»;
• «2» — нумерация начинается с понедельника с порядковым номером «1»;
• «3» — нумерация начинается с понедельника с порядковым номером «0».

Excel: определить день недели по дате

Как видите, число «3» было отправлено в определенную ячейку, которая, согласно введенному аргументу «тип» «2», соответствует окружающей среде.

Excel: определить день недели по дате

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

Excel: определить день недели по дате

Как видите, определить день недели по введенной дате легко и занимает всего несколько секунд, а сам метод остается на усмотрение пользователя.

Excel: Условное форматирование (часть 2)

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

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

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

Формат

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

Рассмотрим 2 примера.

1. Выделение цветом всей строки

Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.

1.Выделите таблицу, к которой будет применено условное форматирование

Выберите Формат — Условное форматирование в меню

3. Когда появится новое окно, установите условие:

  • Выберите — «формула»
  • введите формулу =$B2>5000

4. Выберите нужный формат для выделения строк

Формат

Формат

Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.

Формат

Сложным моментом является знак доллара ($) перед буквой столбца в адресе — он блокирует столбец, оставляя ссылку на строку не отмеченной — значения берутся из столбца в формуле, по очереди из каждой последующей строки.

2. Проверка дат и сроков

Поскольку даты в Excel — это одинаковые числа (один день = 1), вы можете легко использовать условное форматирование для проверки сроков платежей или заданий.

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

1. Выделите таблицу, к которой будет применено условное форматирование

Выберите в меню Формат — Условное форматирование

3. Задайте условие 1:

  • выберите — «формула»
  • введите формулу =B2<$D$1

4. Нажмите «А также >>» для ввода второго условия

5 Задайте условие 2:

  • выберите — «формула»
  • введите формулу =(B2-$D$1) < 7 , (разница между датами меньше 7 дней)
  • выберите необходимый формат

Формат

Обратите внимание, что в формуле должен быть фиксированный адрес ячейки с текущей датой, т.е. $ до и после буквы столбца (в нашем примере это $D$).

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