Как в Excel настроить проверку данных, в которых число кратно 100
В этом учебном материале по Excel мы рассмотрим примеры того, как настроить проверку данных, в которых число кратно 100.
Основная формула
Описание
Чтобы разрешить только числа, кратные 100, вы можете использовать проверку данных с помощью настраиваемой формулы на основе функции ОСТАТ. В показанном примере проверка данных, примененная к диапазону C5:C10:
Пояснение
Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки. Когда пользовательская формула возвращает ИСТИНА, проверка проходит, и вводные данные принимаются. Когда формула возвращает ЛОЖЬ, проверка не выполняется и ввод отклоняется.
В этом случае функция ОСТАТ используется для выполнения операции по модулю, которая возвращает остаток после деления. Формула, используемая для проверки ввода:
Значение в C5 — 500. Функция ОСТАТ делит 500 на 100 и получает 5, а остаток равен нулю. Поскольку 0 = 0, правило возвращает ИСТИНА, и проверка данных проходит:
Округление чисел до целого или кратного в Excel
Частый пример — ваша компания хочет закупить акции, но они продаются пачками, допустим, по 5 штук. Вы хотите купить 123 акции, но так как они продаются пачками по 5 штук вы купите только 120 акций. До этого числа вам и необходимо округлить после расчетов.
И да, вы можете сделать это вручную, но корректнее будет делать с помощью функций Excel, так как если вы столкнетесь с большим объемом данных, вручную это делать будет не вариант.
И конечно же, в Excel существуют даже несколько функций, с помощью которых можно выполнить эту задачу.
Округление в большую сторону до целого числа
Допустим, у вас есть такая табличка:
И столбик «FTE Needed» нам нужно округлить в большую сторону.
Как это сделать?
В этой функции используется 2 аргумента, первый само число которое нужно округлить, второй кратность.
Так как нам нужно именно целое число мы прописали во втором аргументе «0».
Также можно округлить и в меньшую сторону с помощью функции:
Округлить до числа кратного 0.5
Допустим, табличка у нас та же, но теперь нам нужно округлить так, чтобы число было кратно 0.5.
Как это сделать?
Первый аргумент функции число, которое нужно округлить, а второй — кратность.
Также, тоже самое можно сделать с помощью функции ОКРВВЕРХ.
Округлить до числа кратного 5
Теперь будем использовать функцию ОКРУГЛТ.
Допустим, у нас есть такая табличка:
В этой формуле аргументы те же, что и в прошлых.
Особенность этой функции в том, что она округляет число в ту сторону, которая ближе всего (большую или меньшую). Выбрать в этом случае вы не можете.
Округлить в большую сторону до числа кратного 5
А сейчас рассмотрим как сделать так, чтобы число было округлено в большую сторону, но при этом оставалось кратно 5.
Мы будем использовать функцию ОКРВВЕРХ.
Округлить в меньшую сторону до числа кратного 5
В этом случае будем использовать функцию ОКРВНИЗ.МАТ.
Округлить в большую сторону до числа кратного 10
Допустим у нас та же табличка:
Но теперь нам нужно округлить в большую сторону до числа кратного 10.
Я хотел подловить вас, конечно же, многие заметили что эта формула округляет в ближайшую сторону. То есть 161 будет округлено до 160.
А если вы все-таки хотите округлить в большую сторону, нужно использовать ОКРВВЕРХ или ОКРВВЕРХ.МАТ.
Это будет выглядеть так:
При выполнении обеих функций — результат будет один.
Если же вам нужно округлить в меньшую сторону, формулы будут такие:
Если вы хотите «вживую» увидеть разницу между ОКРУГЛТ и ОКРВВЕРХ/ОКРВНИЗ, я показал её на картинке ниже:
Обратите внимание, иногда результаты совпадают, но далеко не всегда. Почему? Я рассказал об этом в статье.
Надеюсь, вам помогла данная статья.
Если у вас есть свои способы округления, пожалуйста, поделитесь ими в комментариях.
Как проверить кратность в excel
В общем, конкретное значение в диапазоне Excel может быть для вас легким, но в некоторых случаях вам может потребоваться выделить все кратные определенного числа, как показано на скриншоте ниже. Как вы можете быстро решить эту проблему?
Выделение кратных определенного числа с помощью условного форматирования
На самом деле ， вы можете использовать формулу в условном форматировании, чтобы выделить кратные определенного числа.
1. Введите конкретное число, кратные которому вы выделите в ячейке, как показано на скриншоте ниже:
2. Затем выберите диапазон ячеек и щелкните Главная > Условное форматирование > Новинки Правило. Смотрите скриншот:
3. Затем в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила list и введите эту формулу = MOD (A1, $ F $ 1) = 0 в Формат значений, где эта формула истинна текстовое поле и щелкните Формат для Формат ячеек диалога под Заполнять и выберите один цвет, который хотите использовать для выделения. Смотрите скриншот:
В формуле A1 — это первая ячейка выбранной ячейки, а F1 — конкретное число.
4. Нажмите OK > OK. Затем все числа, кратные конкретному числу, были залиты цветом.
Функции округления в Excel
В Excel часто используют округление чисел. В данной заметке рассмотрим разные типы округления, которые встречаются на практике.
Некоторые новички допускают ошибку, пытаясь произвести округление с помощью формата числа.
Формат никак не влияет на точность числа в ячейке. Поэтому не стоит надеяться на формат, когда нужно реальное округление.
Функция ОКРУГЛ в Excel
Для обычного округления числа по математическим правилам существует функция ОКРУГЛ, которая имеет следующий синтаксис:
ОКРУГЛ(число или ссылка на округляемое число; количество оставляемых знаков).
число или ссылка на округляемое число – исходное число для округления;
количество оставляемых знаков – количество знаков после запятой.
Например, формула округления в Excel до двух знаков после запятой выглядит так.
Чтобы получить формулу округления в Excel до целого числа, во втором аргументе нужно поставить 0.
Встречаются и более сложные ситуации. Как, например, округлить до тысяч (три последние цифры являются нулями)? Многие поступают так. Делят число на 1 000, округляют до целого и затем снова умножают на 1 000. Это не лучший способ.
Функция ОКРУГЛ умеет округлять не только после запятой, но и до запятой, т.е. до тысяч, миллионов и т.д. Например, чтобы число 123456789 округлить до тысяч и получить 123457000, точность указывается с минусом.
На этом формулы округления в Эксель не заканчиваются.
ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ
В Excel есть формулы округления в большую и меньшую сторону независимо от последних цифр числа. Например, расчетные цены округляют вверх, чтобы не уменьшить прибыль; возраст человека округляют вниз до целого, чтобы узнать полное количество лет. Короче, для этих целей придуманы функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.
Данные функции имеют такие же параметры, как и ОКРУГЛ (ссылка на число и количество знаков до или после запятой).
ОКРУГЛТ
Довольно полезная функция ОКРУГЛТ. С ее помощью можно добиться округления не только до нужного разряда (слева или справа от запятой), но и вообще до нужной точности (кратности). К примеру, нужно рассчитать заказ изделий в штуках, но так, чтобы он был равен целому количеству ящиков. Если в ящике 6 шт, то заказываемое количество должно быть кратно шести. Или другой пример. Нужно, чтобы расчетная цена заканчивалась либо на 00, либо на 50, то есть имела кратность 50 рублей. Функция ОКРУГЛТ легко решает такие задачи.
Очень, очень правильная функция. Позволяет избежать 3-х промежуточных расчетов (разделить на кратность, округлить до целого и снова умножить на кратность, как я сам делал, пока не узнал про эту формулу).
ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ
Округление с заданной точностью также можно производить принудительно вверх или вниз с помощью ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ. Например, при расчете заказа, когда никак нельзя заказать меньше расчетного объема, но при этом нужно и кратность сохранить. То есть одна «лишняя» штука требует заказа дополнительно целого ящика. Аргументы функции те же.
Это были основные формулы округления в Excel. Но есть еще парочку, которые используются реже.
Функция ЦЕЛОЕ в Excel отбрасывает дробную часть и таким образом округляет вниз до ближайшего целого. Будет полезна при расчете целого количество лет между датами (возраст человека).
Функции ЧЁТН и НЕЧЁТ округляют числа до ближайшего четного или нечетного значения соответственно.
Видеоуроки ниже показываю основные приемы округления, а также некоторые трюки.