Округление чисел до целого или кратного в Excel
Частый пример — ваша компания хочет закупить акции, но они продаются пачками, допустим, по 5 штук. Вы хотите купить 123 акции, но так как они продаются пачками по 5 штук вы купите только 120 акций. До этого числа вам и необходимо округлить после расчетов.
И да, вы можете сделать это вручную, но корректнее будет делать с помощью функций Excel, так как если вы столкнетесь с большим объемом данных, вручную это делать будет не вариант.
И конечно же, в Excel существуют даже несколько функций, с помощью которых можно выполнить эту задачу.
Округление в большую сторону до целого числа
Допустим, у вас есть такая табличка:
И столбик «FTE Needed» нам нужно округлить в большую сторону.
Как это сделать?
В этой функции используется 2 аргумента, первый само число которое нужно округлить, второй кратность.
Так как нам нужно именно целое число мы прописали во втором аргументе «0».
Также можно округлить и в меньшую сторону с помощью функции:
Округлить до числа кратного 0.5
Допустим, табличка у нас та же, но теперь нам нужно округлить так, чтобы число было кратно 0.5.
Как это сделать?
Первый аргумент функции число, которое нужно округлить, а второй — кратность.
Также, тоже самое можно сделать с помощью функции ОКРВВЕРХ.
Округлить до числа кратного 5
Теперь будем использовать функцию ОКРУГЛТ.
Допустим, у нас есть такая табличка:
В этой формуле аргументы те же, что и в прошлых.
Особенность этой функции в том, что она округляет число в ту сторону, которая ближе всего (большую или меньшую). Выбрать в этом случае вы не можете.
Округлить в большую сторону до числа кратного 5
А сейчас рассмотрим как сделать так, чтобы число было округлено в большую сторону, но при этом оставалось кратно 5.
Мы будем использовать функцию ОКРВВЕРХ.
Округлить в меньшую сторону до числа кратного 5
В этом случае будем использовать функцию ОКРВНИЗ.МАТ.
Округлить в большую сторону до числа кратного 10
Допустим у нас та же табличка:
Но теперь нам нужно округлить в большую сторону до числа кратного 10.
Я хотел подловить вас, конечно же, многие заметили что эта формула округляет в ближайшую сторону. То есть 161 будет округлено до 160.
А если вы все-таки хотите округлить в большую сторону, нужно использовать ОКРВВЕРХ или ОКРВВЕРХ.МАТ.
Это будет выглядеть так:
При выполнении обеих функций — результат будет один.
Если же вам нужно округлить в меньшую сторону, формулы будут такие:
Если вы хотите «вживую» увидеть разницу между ОКРУГЛТ и ОКРВВЕРХ/ОКРВНИЗ, я показал её на картинке ниже:
Обратите внимание, иногда результаты совпадают, но далеко не всегда. Почему? Я рассказал об этом в статье.
Надеюсь, вам помогла данная статья.
Если у вас есть свои способы округления, пожалуйста, поделитесь ими в комментариях.
Условное форматирование кратных чисел определенного значения
Чтобы выделить числа, кратные другому числу с условным форматированием, вы можете использовать простую формулу, основанную на функции ОСТАТ. В показанном примере формула, используемая для выделения кратных значений 9, равна:
Примечание: формула вводится относительно «активной ячейки» в выборе, в этом случае ячейка B4.
Как работает формула
Условное форматирование рассчитывается для каждой ячейки в диапазоне по отношению к верхней левой ячейке в выборе. В этом случае формула использует функцию ОСТАТ для проверки остальной части деления значения в каждой ячейке со значением в ячейке E2, которое равно 9. Когда остаток равен нулю, мы знаем, что это значение является четным кратным числу 9, поэтому формула проверяет результат ОСТАТ на ноль.
Когда ОСТАТ возвращает ноль, выражение возвращает ИСТИНА и применяется условное форматирование. Когда ОСТАТ возвращает любой другой результат, выражение возвращает ЛОЖЬ условное форматирование не применяется.
Примечание E2 вводится как абсолютный адрес ($E$2), чтобы исключить изменение ссылки, поскольку правило условного форматирования оценивается по выбору B4:G11.
Как выделить в Excel числа, кратные определенному числу?
Для выдачи конкретного значения в диапазоне Excel может быть легко, но в некоторых случаях вам может потребоваться выделить все кратные определенного числа, как показано на скриншоте ниже, как можно быстро решить эту проблему?
Выделите кратные число с условным форматированием
Выделение кратных определенного числа с помощью условного форматирования
На самом деле ， вы можете использовать формулу в условном форматировании для выделения кратных определенного числа.
1. Введите конкретное число, кратные которому вы выделите в ячейке, как показано на скриншоте ниже:
2. Затем выберите диапазон ячеек и нажмите Главная > Условное форматирование > Создать Правило . См. Снимок экрана:
3. Затем в диалоговом окне Новое правило форматирования выберите Использовать формулу, чтобы определить, какие ячейки нужно форматировать в Выберите тип правила и введите эту формулу = MOD (A1, $ F $ 1) = 0 в текстовое поле Формат значений, где эта формула истинна , и нажмите Формат . чтобы открыть диалоговое окно Формат ячеек , на вкладке Заливка и выберите один цвет, который вы хотите использовать для выделения. См. Снимок экрана:
В формуле A1 – первая ячейка выбранного ячейка, а F1 – конкретное число.
4. Нажмите OK > OK . Затем все числа, кратные определенному числу, были залиты цветом.
Как разделить значения в ячейке excel на 1000
Для этого, в любой ячейке рядом с таблицей пишем 1000, заходим в эту ячейку и копируем ее. Выделяем весь диапазон с числами, которые нужно разделить на 1000 и вызываем «Специальную вставку», нажав на правую клавишу мыши. Выбираем «разделить»: и нажимаем ОК.
идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка ; выбираем Разделить в разделе Операция ; нажимаем ОК .
Как заставить excel отображать определенное количество значимых цифр?
- в любой пустой ячейке вводим 1000 (например, в D2 ) и нажимаем ENTER ;
- копируем в Буфер обмена значение ячейки D2 ;
- выделяем диапазон с ценами ( В2:В10 );
- идем в меню Главная/ Буфер обмена/ Вставить/ Специальная вставка;
- выбираем Разделить в разделе Операция;
- нажимаем ОК.
-
В таблице столбики «B» и «C» несут информацию об общих продажах за тот или иной месяц. Расчет будем производить в столбике «D», поэтому ему нужно задать процентный формат.
Округление числа с помощью формата ячеек
Формат ячеек позволит выбрать необходимое количество цифр, которое должно отображаться в числе после запятой.
Округлять будем число 23,5168 в ячейке А1. Кликаем по нему правой кнопкой мыши и из контекстного меню выбираем «Формат ячеек».
Дальше, на вкладке «Число» из списка «Числовые форматы» выберите «Числовой». В поле «Число десятичных знаков» поставьте нужное значение. Нажмите «ОК».
Количество знаков после запятой уменьшилось до 2-х. Как видите, округление числа происходит по математическим законам: если отбрасываемая цифра меньше пяти – число округляется в меньшую сторону, если больше пяти или равно – в большую.
Значение в ячейке изменилось только визуально. В строке формул по-прежнему стоит число 23,5168, и именно оно будет браться для любых расчетов, в которых будет указана ячейка А1.
Чтобы уменьшить или увеличить количество знаков после запятой, можно использовать и следующий способ. На вкладке «Главная» в группе «Число» Вы найдете две кнопочки «Увеличить разрядность» и «Уменьшить разрядность».
Выделим ячейку А1 и нажмем один раз на кнопку «Уменьшить разрядность». Получим вот такой результат.
Как посчитать процент в Excel
Выделите ячейку, формулу в которой необходимо изменить. … Щелкните по Строке формул, чтобы приступить к редактированию формулы. Вы также можете дважды щелкнуть по ячейке, чтобы посмотреть и отредактировать формулу прямо в ней. Все ячейки, на которые ссылается формула, будут выделены разноцветными границами.
Как правильно считать
Только что мы научились считать простейшим способом. Конечно, таким образом можно вычислить и более сложные вещи. Главное, не забывать ставить скобки, где нужно. Например: =((375*230)+(1263-455))/(120*33)
Но, как правило, этот способ «счета» используется не так часто. Существует более продвинутый вариант.
В ней нужно посчитать сумму в соответствующем столбике. Получается, нужно умножить количество на цену по каждой позиции. Проще говоря, умножить значения в столбике B на значения в столбике C.
Начну с первой позиции «Сыр». Щелкаю в ячейке D2 и печатаю знак равно.
Затем нажимаю на ячейку B2, так как нужно ее значение умножить на C2.
И, наконец, нажимаю кнопку Enter на клавиатуре. Все! В ячейке D2 получился нужный результат.
Щелкнув по этой ячейке (D2) и посмотрев в строку формул, можно увидеть, как получилось данное значение.
Почему именно так считать правильно?! Дело в том, что когда мы вычисляем этим способом, Excel считает не те цифры, которые введены в ячейки, а содержимое ячеек.
Объясню на примере этой же таблицы. Сейчас в ячейке B2 введено число 213. Удаляю его, печатаю другое число и нажимаю Enter.
Результат изменился. Это произошло из-за того, что поменялось значение в B2. Ведь формула у нас следующая: =B2*C2
Это означает, что программа Microsoft Excel умножает содержимое ячейки B2 на содержимое ячейки C2, каким бы оно не было. Выводы делайте сами 
Попробуйте составить такую же таблицу и вычислить сумму в оставшихся ячейках (D3, D4, D5).
В формулу можно подставить адреса ячеек, чтобы значения чисел для вычисления процентов было взято из них. Например, в ячейку C1 ввести, предварительно установив процентный формат, формулу =B1/A1*100% . Таким образом вычислим сколько процентов составляет число из ячейки B1 от числа в ячейке A1.
Как посчитать в Excel (эксель)
Для того, чтобы подсчитать количество ячеек, которые имеют значения больше, чем конкретное число, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы rng представляет собой диапазон ячеек, содержащих числа, и X представляет собой порог, выше которого вы хотите посчитать.
Как в Экселе указать диапазон чисел в формуле?
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
- в поле Имя введите: Месяц;
- в поле Область выберите лист Книга;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$ B$5;;;1;СЧЁТЗ(лист1!$ B$5:$I$5))
- нажмите ОК.
Итак, чтобы найти, сколько целых чисел расположено между положительными целыми числами n и m (m>n), надо из большего числа вычесть 1 и ещё меньшее число: k=m-1-n. Пример.
Округление в Excel – инструкция
- Воспользуемся функцией COUNTIF (СЧЁТЕСЛИ). Для примера подсчитаем количество чисел 5 в диапазоне A1:A6, используя следующую формулу: .
- Чтобы подсчитать уникальные значения, добавим функцию SUM (СУММ), 1/, и заменим 5 на A1:A6. .
- Закончим ввод формулы нажатием Ctrl+Shift+Enter.
обратите внимание, что отображаемые значения будут округлены до существенных цифр, и должны использоваться только для отображения/вывода. Если вы выполняете дальнейшие вычисления, используйте исходное значение в A1, чтобы избежать распространения незначительных ошибок.