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

Такие функции, как ЕСЛИ, СУММ, СЧЁТ, СРЗНАЧ хорошо известны всем пользователям Excel. Тем не менее, некоторые функции, такие как MOD, WEEKDAY, WORKDAY, CHOOSE, DATEDIF, DELTA, SUBSTITUTE, HYPERLINK, используются редко.

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

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

Ниже приведены некоторые из его применений;

  1. Получить дату самого последнего дня недели
  2. Анализ времени и даты
  3. Выделите целые числа
  4. Выделите каждую N-ю строку
  5. Суммировать каждое N-е значение
  6. Подсчитайте нечетные или четные числа
  7. Фиксировать значения каждый N-й интервал/период

По сути, функция MOD используется для получения остатка от деления.

Syntax = MOD(Number, Divisor)

NB:

  1. MOD всегда возвращает результат с тем же знаком, что и делитель.
  2. MOD вернет #DIV! ошибка, если делитель равен нулю

Например:

  1. =MOD (7,5) результат 2
  2. =MOD(7,2) приводит к 1, потому что 2 входит в 7 трижды, с остатком 1

Скачать рабочий лист, чтобы следовать приведенным ниже примерам

Получить дату самого последнего дня недели

Excel хранит дату в виде последовательных порядковых номеров, чтобы их можно было использовать в вычислениях. Он также присваивает дню недели номер, то есть по умолчанию от 1 = воскресенье до 7 = суббота.

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

=StartDate-MOD(startDate-NumberOfRecentDay,7)

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

=Today()-MOD(Today()-6,7)

Используя приведенный ниже пример, сегодня (07–06–2016) хранится как номер 42528,

MOD(Today()-6,7) =3 т.е. последняя пятница была 3 дня назад

Следовательно, дата пятницы (42 528–3), поскольку даты хранятся в виде последовательных порядковых номеров.

Примечание. Если номер дня недели «StartDate» совпадает с «NumberOfRecentDay», будет возвращено «StartDate».

Как вы можете видеть выше, поскольку сегодня (07–06–2016) — вторник, последний вторник возвращается как (07–06–2016). Чтобы избежать этого сценария, используйте формулу ниже

=Today()-IF(MOD(Today()-6,7)=0,7,MOD(Today()-6,7))

Анализ времени и даты

В то время как Excel хранит даты как серийные номера, он хранит время как дробную часть 24-часового дня. Например, 8:00 хранится как (8/24)=0,33333333.

MOD использует эти числа для;

=MOD(DateTime Value,1)

Как это работает

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

Используя функции TIME и INT, MOD можно использовать для преобразования секунд в минуты.

= TIME(0,INT(seconds/60),MOD(seconds,60)

Как это работает

Функция INT ТОЛЬКО возвращает целую часть десятичного числа. Поэтому INT(2109/60) = 35

MOD(2109,60) возвращает остаток от делителя, т.е. 09

Примечание. Это работает, ТОЛЬКО если время в секундах НЕ больше одного часа.

Если время в секундах превышает 1 час, используйте приведенную ниже формулу: не забудьте отформатировать ячейку «ч:мм:сс».

=TIME(INT(seconds/3600), INT(MOD(seconds,3600)/60), MOD(MOD(seconds,3600),60))

Например, чтобы преобразовать 8710 секунд в 2:25:10.

КАК?

INT(8710/3600) extracts the hours 2
INT(MOD(8710,3600)/60)→ INT(1510/60) → INT(25.1666666666667) extracts the minutes 25
MOD(MOD(8710,3600),60)→ MOD(1510,60) extracts seconds part 10

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

Например, вычисление результатов ночных часов до отрицательных значений, и Excel отобразит (##################)

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

=MOD(EndTime-StartTime,1)

Выделите целые числа

Поскольку =MOD(Number,1) всегда приводит к нулю для целых чисел, мы можем использовать это в пользовательском форматировании, чтобы выделить целые числа.

Таким образом, =MOD(Number,1)=0 всегда будет оцениваться как TRUE для ячеек с целыми числами, иначе FALSE. Форматирование условия использует этот массив TRUE и FALSE для форматирования ячеек.

Как это сделать:

  1. Перейдите в «Пользовательское форматирование» → «Управление правилом» → «Новое правило» → «Использовать формулу», чтобы определить, какую ячейку форматировать.
  2. Добавьте формулу, как показано ниже
  3. Применить формат к нужному разделу

Выделить каждую NthRow

Используйте ту же процедуру, описанную выше, но ниже функцию

= MOD(ROW(startCell),N)=1

Например, в приведенной ниже формуле выделяется каждая третья строка из строки, содержащей ячейку (C2), потому что это строка, которая оценивается как ИСТИНА.

Суммировать каждое N-е значение

Благодаря объединенным возможностям функций СУММПРОИЗВ и СТОЛБЦА, MOD может суммировать каждое N-е значение в столбце.

= SUMPRODUCT(--(MOD(COLUMN(sumRange)-COLUMN(startColumn)+1,N)=0),sumRange)

Как это работает:

{FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}
  • Этот логический массив преобразуется в числовой эквивалент с использованием двойных отрицаний (двойной унарный метод)
{0;1;0;1;0;1;0}
  • СУММПРОИЗВ получает сумму произведения двух массивов
=SUMPRODUCT({0;1;0;1;0;1;0},{260,270,190,370,630,120,620})

Подсчитайте четные или нечетные числа

В этом вычислении MOD также сочетается с СУММПРОИЗВ.

=SUMPRODUCT(--(MOD(countRange),2)=1)→→ For ODD numbers
=SUMPRODUCT(--(MOD(countRange),2)=0)→→ For EVEN numbers

Как это работает:

MOD возвращает остаток 1 для нечетных чисел и 0 для четных чисел.

Фиксировать значения каждый N-й период

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

=IF(MOD(COLUMN(startCell)-OffsetValue,N)=0,FixValue,0)

Примечание:OffsetValue определяется количеством столбцов, в которых ваш столбец startCell находится в столбце A

Другой метод, предложенный в комментариях ниже,

=(MOD(COLUMN(StartCell)-offsetValue ,N)=0)*FixValue

Скачать рабочий лист для просмотра примеров

Вывод:

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

Я также уверен, что приведенный выше список не является исчерпывающим, поэтому Оставьте комментарий и добавьте больше способов использования MOD FUNCTION.

Рекомендуемая ссылка:

http://excelsemipro.com/2010/08/convert-seconds-to-a-time-format-in-excel/

Первоначально опубликовано на crispexcel.com.