|
Удаление тысячных долей |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше
По умолчанию в Microsoft Excel используются системные деления, определенные в региональных параметрах панели управления. Если иногда требуется отобразить числа с различными знаками для тысяч или десятичных чисел, можно временно заменить системные.
-
Выберите Файл > Параметры.
-
На вкладке Дополнительные параметры в окне Параметры правкиразберется с помощью системных разных вариантов.
-
Введите новые знаки в поля Десятичной и Тысячной.
Совет: Если вы хотите снова использовать системные сепараторы, выберите использовать системные.
Примечание: Для емких решений необходимо изменить региональные параметры панели управления.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
У начинающих пользователей программы Excel может возникнуть следующая ситуация:
При вводе чисел в ячейку цифры автоматически преобразуются в десятичные дроби с двумя знаками после запятой. Изменение формата ячеек и другие ухищрения не помогают привести числа к обычному виду.
| Вводим число | Получаем результат |
| 7 | 0,07 |
| 77 | 0,77 |
| 777 | 7,77 |
| 7777 | 77,77 |
| 77777 | 777,77 |
| 777777 | 7777,77 |
| 7777777 | 77777,77 |
| 77777777 | 777777,77 |
Как избавится от такого отображения вводимых в ячейку чисел.
Чтобы исправить ситуацию достаточно выполнить довольно несложную последовательность действий:
После выполнения последовательности действий приведенной в тексте выше цифры будут отображаться в ячейках в том виде, в котором Вы их записываете.
как в Excel Отключить обрезание чисел больших [закрыт]
Хотите улучшить этот вопрос? Обновите вопрос так, чтобы он вписывался в тематику Stack Overflow на русском.
Закрыт 2 года назад .
Чтобы они отображались не так в графике 2E+09 а нормально в десятиричной системе без сокращений
1 ответ 1
Первое, что нужно учитывать: формат значения и формат ячейки — не одно и то же.
То, что видим (2E+09) — экспоненциальная форма записи числа 2000000000 (не только этого, см. ниже). Это же число можно получить:
Число не обрезано, но отображается в другом виде. Значение в нормальном его виде можно посмотреть в строке формул.
Но вот незадача. 2100000000 в экспоненциальной форме будет иметь ту же запись — 2E+09. А все потому, что формат ячейки выставлен Экспоненциальный, да еще и с нулевым количеством знаков в дробной части. Ставим два знака после запятой — получаем 2,10E+09
Excel сравнивает значение с заданными форматами и сам определяет, как его отображать. Иногда «зло шутит». Например, запись 1.02 (не в текстовой ячейке) воспринимается как дата день.месяц.(текущий_год), 11.22 — как дата месяц.день.(текущий_год). При этом Excel меняет не только формат отображения — он еще и значение изменит! Вместо введенного 1.02 получим дату 01.02.2018. Или, если установим формат ячейки Общий, увидим 43132 (количество дней после 01.01.1900)
Формат ячейки мог автоматически измениться на экспоненциальный в случае, когда ширина ячейки была недостаточна для отображения полного числа. Мог подтянуться при копировании данных.
Исправить «обрезанное» просто: задать достаточную ширину ячейки, задать ей формат Общий или Числовой с необходимым количеством знаков после запятой.
Важное примечание. Большие числа все же обрезаются, но это не связано с экспоненциальной формой записи.
Excel имеет ограничение при работе с числовыми данными — не более 15 разрядов. Если в ячейку вписать число 12345678901234567890, получим значение с обнуленными младшими разрядами — 12345678901234500000, от 1,2345678901234567890 останется 1,23456789012345.
Избежать обрезания можно, если установить текстовый формат ячейки. Текстом может храниться и длинное число. Это тоже бывает нужно: банковские счета, инвентарные номера. Т.е. фатически не числа, а текст, состоящий из цифр. Но использовать такие значения в качестве чисел не получится — при первой же попытке младшие разряды обнулятся.
Ради справедливости нужно отметить, что работать можно и с очень большими числами, разрядность которых превышает 15: хранить значения как текст, при использовании делить на допустимые фрагменты. Но это непросто и выходит за рамки вопроса.
Редактор таблиц Microsoft Excel широко применяется для выполнения разного рода вычислений. В зависимости от того, какая именно задача стоит перед пользователем, меняются как условия выполнения задачи, так и требования к получаемому результату. Как известно, выполняя расчёты, очень часто в результате получаются дробные, нецелые значения, что в одних случаях хорошо, а в других, наоборот, неудобно. В этой статье подробно рассмотрим, как округлить или убрать округление чисел в Excel. Давайте разбираться. Поехали!
Для удаления дробных значений применяют специальные формулы
О существовании специальной функции округления в Экселе знают далеко не все пользователи. Случается, что некоторые из них ошибочно принимают формат значений, то есть отображаемое количество знаков после запятой, за округление. Далее в статье разберёмся, что к чему и как всё делается.
Для начала отметим, что функция «Формат числа» применяется в случаях, когда вид числа необходимо сделать более удобным для чтения. Кликните правой кнопкой мыши и выберите в списке пункт «Формат ячеек». На вкладке «Числовой» установите количество видимых знаков в соответствующем поле.
Но в Excel реализована отдельная функция, позволяющая выполнять настоящее округление по математическим правилам. Для этого вам понадобится поработать с полем для формул. Например, вам нужно округлить значение, содержащееся в ячейке с адресом A2 так, чтобы после запятой остался только один знак. В таком случае функция будет иметь такой вид (без кавычек): «=ОКРУГЛ(A2;1)».
Принцип прост и понятен. Вместо адреса ячейки вы можете сразу указать само число. Бывают случаи, когда возникает необходимость округлить до тысяч, миллионов и больше. Например, если нужно сделать из 233123 — 233000. Как же быть в таком случае? Принцип тут такой же, как было описано выше, с той разницей, что цифру, отвечающую за количество разделов, которые необходимо округлить, нужно написать со знаком «-» (минус). Выглядит это так: «=ОКРУГЛ(233123;-3)». В результате вы получите число 233000.
Если требуется округлить число в меньшую либо в большую сторону (без учёта того, к какой стороне ближе), то воспользуйтесь функциями «ОКРУГЛВНИЗ» и «ОКРУГЛВВЕРХ». Вызовите окно «Вставка функции». В пункте «Категория» выберите «Математические» и в списке ниже вы найдёте «ОКРУГЛВНИЗ» и «ОКРУГЛВВЕРХ».
Ещё в Excel реализована очень полезная функция «ОКРУГЛТ». Её идея в том, что она позволяет выполнить округление до требуемого разряда и кратности. Принцип такой же, как и в предыдущих случаях, только вместо количества разделов указывается цифра, на которую будет заканчиваться полученное число.
В последних версиях программы реализованы функции «ОКРВВЕРХ.МАТ» и «ОКРВНИЗ.МАТ». Они могут пригодиться, если нужно принудительно выполнить округление в какую-либо сторону с указанной точностью.
Как вариант, решить проблему можно при помощи функции «ЦЕЛОЕ», принцип которой заключается в том, что дробная часть просто отбрасывается, округляя вниз.
Иногда программа автоматически округляет полученные значения. Отключить это не получится, но исправить ситуацию можно при помощи кнопки «Увеличить разрядность». Кликайте по ней, пока значение не приобретёт нужный вам вид.
Теперь, когда вы разбираетесь, как выполнять округление числа, вам будет проще выполнять ряд задач в программе. Кроме того, эти навыки помогут вам и в решении повседневных вопросов, связанных с расчётами.
Если вы вставите в числовую ячейку длинное число с более, чем 15 разрядов, то Excel округлит его и выдаст нечто совсем непригодное. Например, число 893800399000003570 он покажет в виде 8,938E+17, а если по нему кликнуть, покажет его, как 893800399000003000. Здесь мы видим потерю последних трёх разрядов, что совершенно неприемлемо.
Решить проблему можно, если изменить формат ячейки или колонки с числового на текстовый. Тогда вставляемое число любой разрядности не будет преобразовываться. Также можно вставить его в числовую ячейку, если перед числом использовать знак одинарных кавычек.
Однако при преобразовании таких строчных записей в число Excel снова округляет все разряды больше 15-ти. Поэтому такой способ подходит лишь для представления больших чисел в таблице, а для вычислений они всё равно должны преобразовываться с потерями. Сделать с этим ничего нельзя – Excel так устроен, и 15 разрядов – предельная его точность, с которой можно записывать числа в таблице.
Пишите в комментариях были ли статья полезной для вас и продолжайте совершенствовать навыки работы в Microsoft Excel.
| Цитата |
|---|
| vikttur: Да где Вы видели число с двумя разделителями? Нет таких в природе! |
| Цитата |
|---|
| Evgenyy: Везде с двумя разделителями |
| Цитата |
|---|
| БМВ: Десятичный разделитель только один |
прав только медведь, если докопаться, однако понятно, что vikttur имел ввиду десятичный разделитель целой и дробной части (в таком случае, он тоже прав)
Evgenyy, вы же, говоря, что «везде с 2мя разделителями» не правы, т.к. количество разделителей будет равно количеству разрядов — 1, а значит, зависит от числа, а значит, для, скажем, миллиона и 11 сотых (1 000 000,11 или 1,000,000.11) уже будет равно 3ём: 2 разделителя разрядов + 1 десятичный
В Excel 2 типа разделителей: «десятичный» (разделитель целой и дробной части — именно его обычно имеют ввиду, когда говорят о разделителе в числе) и разрядный (отбивает разряды (обычно по 3 цифры))
Изменено: Jack Famous — 09.11.2020 09:44:22
Иногда вам нужно удалить все цифры после запятой, чтобы преобразовать числа в целые числа. Вы можете подумать, что функция Decrease Decimal может с этим справиться. Фактически, функция «Уменьшение десятичного числа» просто изменяет отображаемое значение числа, например, показывает 2.5 как 3, но фактическое значение по-прежнему равно 2.5, отображаемое в строке формул. Следовательно, как в Excel убрать цифры после запятой? Для вас есть несколько способов.
- Удалите цифры после десятичной дроби с помощью функции INT или TRUNC
- Удалите цифры после десятичной дроби без формулы с помощью утилиты Round
Удалите цифры после десятичной дроби с помощью функции INT или TRUNC
Предположим, у нас есть таблица покупок, как показано ниже, и теперь нам нужно удалить цифры после десятичной дроби для значений в столбце Amount. Функции Excel могут помочь вам легко удалить цифры после десятичной дроби. Вы можете сделать следующее:
Функция INT: В пустой ячейке помимо значения, которое вы удалите цифры после десятичной дроби, введите формулу = ЦЕЛОЕ (E2), и перетащите маркер заливки в нужный диапазон.
Ноты:
(1) В формуле = ЦЕЛОЕ (E2), E2 — это ячейка, в которой вы удалите цифры после запятой.
(2) Функция INT также может сохранять определенные места цифр после запятой. Например, чтобы удалить все цифры, кроме первой после десятичной, можно применить формулу = ЦЕЛОЕ (E2 * 10) / 10.
Функция ОТБР: Помимо значения, которое вы удалите цифры после запятой, введите формулу = TRUNC (E2,0) в пустую ячейку, а затем перетащите маркер заполнения в нужный диапазон.
Ноты:
(1) В формуле = TRUNC (E2,0), E2 — ячейка, в которой вы удалите цифры после десятичной дроби, 0 означает удаление всех цифр после десятичной дроби;
(2) Чтобы удалить все цифры, кроме первой после десятичной, вы можете применить формулу = TRUNC (E2,1);
Функции: Удалить формулы, но сохранить результаты формулы: (1)выделить все ячейки формулы; (2) скопируйте эти ячейки формулы, нажав Ctrl + C ключи одновременно; (3) щелкните правой кнопкой мыши первую ячейку диапазона назначения и выберите Ценности ниже Параметры вставки в контекстном меню.
Кстати, если у вас есть Kutools for Excel установлен, вы можете применить его К фактическому функция для быстрого преобразования всех формул в результаты их формул одним щелчком мыши. Смотрите скриншоты:
Пакетное добавление десятичной точки к существующим числам в столбце / списке без формулы
Как вы знаете, десятичная точка может быть автоматически добавлена с указанными десятичными знаками при вводе чисел с помощью параметров Excel> Дополнительно> Автоматически вставлять десятичную точку. Однако этот параметр повлияет на все листы. Как насчет автоматической вставки десятичных знаков в существующие числа в указанном диапазоне в Excel? Пытаться Kutools for ExcelАвтора Эксплуатация утилита!
Удалите цифры после десятичной дроби без формулы с помощью утилиты Round
Все функции не изменяют исходные значения в Excel, но иногда может потребоваться просто изменить исходные значения. Чтобы удалить цифры после десятичной дроби непосредственно из исходных значений, вы можете применить Круглые полезности Kutools for Excel.
1. Выделите ячейки, в которых вы удалите цифры после запятой, а затем щелкните значок Кутулс > Круглые.
2. В открывшемся диалоговом окне Раунд без формулы введите 0 в поле Десятичное число поле, отметьте Округление и нажмите Ok кнопку.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демо: удаление цифр после десятичной без формулы в Excel
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (13)
Оценок пока нет. Оцените первым!
Skip to content
В статье объясняется использование ОКРУГЛ, ОКРУГЛТ, ОКРУГЛВВЕРХ, ОКРВНИЗ, ЦЕЛОЕ, ОТБР и множества других функций округления в Excel. Также приводятся примеры формул для изменения десятичных чисел до целых или до определенного количества десятичных знаков, до ближайшего 5, 10 или 100, извлечения дробной части, и многое другое.
В некоторых ситуациях, когда вам не нужен совершенно точный ответ, полезно использовать округление чисел. Говоря простым языком, эта операция означает удаление наименее значащих цифр, что упрощает понимание результатов, при этом сохраняя близкое к исходному значение. Другими словами, округление позволяет получить приблизительное значение с желаемой степенью точности.
В повседневной жизни оно обычно используется, чтобы упростить расчет или работу с числами. Например, вы можете воспользоваться им, чтобы сделать длинные десятичные числа короче, чтобы сообщить о результатах сложных вычислений или упростить денежные значения.
Существует множество различных способов округления – до целого, до указанного приращения, до простых дробей и т. д.
- Округление при помощи форматирования
- Функции округления
- ОКРУГЛ (ROUND)
- ОКРУГЛВВЕРХ (ROUNDUP)
- ОКРУГЛВНИЗ (ROUNDDOWN)
- ОКРУГЛТ (MROUND)
- ОКРВНИЗ (FLOOR) и ОКРВНИЗ.МАТ
- ОКРВВЕРХ (CEILING) и ОКРВВЕРХ.МАТ
- ЦЕЛОЕ (INT)
- ОТБР (TRUNC)
- ЧЁТН (EVEN) и НЕЧЁТ (ODD)
- Примеры формул
- Как округлять десятичные дроби
- Округление отрицательных чисел
- Как получить десятичную часть числа?
- Округление десятичной дроби до целого.
- Как округлить до 5 / 10 / 100 / 1000
- Округление времени
Microsoft Excel предоставляет несколько функций для различных типов округления. Ниже вы найдете краткий обзор основных таких функций, а также примеры формул, которые демонстрируют, как использовать их на реальных данных в ваших таблицах.
Округление путем изменения формата ячейки.
Если вы хотите округлить числа в Excel исключительно для целей презентации, вы можете быстро изменить формат ячейки, выполнив следующие действия:
- Выберите ячейки, вид которых нужно изменить.
- Откройте диалоговое окно форматирования, нажав
Ctrl + 1, или же щелкните правой кнопкой мыши ячейку и выберите тот же пункт в контекстном меню. - На вкладке Число, выберите Числовой или Денежный формат, и укажите количество знаков после запятой, которое вы хотите видеть. Предварительный просмотр результата сразу же появится в поле Образец.
- Нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалог.
Важно! Этот метод изменяет формат отображения без изменения фактического значения, хранящегося в ячейке. Если вы ссылаетесь на эту ячейку в любой формуле, во всех расчетах будет использоваться исходное значение. Это может оказаться существенным, когда вы производите вычисления (к примеру, рассчитываете НДС или сумму скидки), а затем считаете итоги. Часто оказывается, что если сложить на калькуляторе значения из таблицы, которые мы распечатали, то сумма не совпадёт с итоговым значением. Это как раз из-за того, что числа, которые мы видим, и реальные данные, с которыми производятся математические операции — это совсем не одно и то же.
Функции округления чисел.
В отличие от форматирования, которое изменяет только отображение, функции округления изменяют фактическое значение в ячейке.
Ниже вы найдете список функций, специально разработанных для выполнения различных типов округления в Excel.
- ОКРУГЛ (ROUND) – до указанного количества цифр.
- ОКРУГЛВВЕРХ (ROUNDUP) – с увеличением до определённого количества знаков.
- ОКРУГЛВНИЗ (ROUNDDOWN) – вниз до нужного количества цифр.
- ОКРУГЛТ (MROUND) – в любую сторону до указанного кратного.
- ОКРВНИЗ (FLOOR) и ОКРВНИЗ.МАТ – с нужной точностью вниз.
- ОКРВВЕРХ (CEILING) и ОКРВВЕРХ.МАТ – с нужной точностью с увеличением.
- ЦЕЛОЕ (INT) – до ближайшего целого.
- ОТБР (TRUNC) – до нужного количества десятичных знаков.
- ЧЁТН (EVEN) – до четного целого.
- НЕЧЁТ (ODD) – до нечетного целого.
ОКРУГЛ.
ОКРУГЛ – это основная функция округления чисел в Excel, которая производит усечение до указанного количества цифр.
Синтаксис: ОКРУГЛ(число; количество_цифр)
Число – это может быть число, ссылка на ячейку с числом, или рассчитанное при помощи формулы значение.
Второй аргумент определяет количество знаков, до которых нужно обрезать сумму. В этом аргументе можно указать положительное или отрицательное значение:
- Если больше 0 – округляется до указанного количества знаков после запятой.
Например, =ОКРУГЛ(57,55; 1) округляет 57,55 до 57,6.
- Если меньше 0, все десятичные разряды удаляются, а число округляется слева от десятичной точки (до десятков, сотен, тысяч и т. д.).
Например, =ОКРУГЛ(57,55; -1) округляет 57,55 до ближайшего, кратного 10, и возвращает 60 в качестве результата.
- Если равно 0, то происходит изменение до целого числа (без десятых).
Например, =ОКРУГЛ(57,55; 0) заменяет 57,55 на 58.
Функция ОКРУГЛ в Excel следует общим математическим правилам, где цифра справа от границы округления определяет, изменяется ли результат вверх или вниз.
Цифра округления – это последняя значащая цифра, которая сохраняется в результате. Она изменяется в зависимости от того, больше или меньше 5 цифра, следующая за ней:
- Если цифра справа от цифры округления равна 0, 1, 2, 3 или 4, цифра не изменяется, и значение считается округленным вниз.
- Если за цифрой следует 5, 6, 7, 8 или 9, она увеличивается на единицу, а результат округляется вверх.
На следующем рисунке показано несколько примеров формулы ОКРУГЛ:
ОКРУГЛВВЕРХ.
Изменяет в сторону увеличения (от 0) до указанного количества цифр.
Синтаксис : ОКРУГЛВВЕРХ(число; количество_цифр)
Второй аргумент работает так же, как и в ОКРУГЛ, но результат всегда рассчитывается в сторону увеличения.
ОКРУГЛВНИЗ.
Она работает противоположно тому, что делает ОКРУГЛВВЕРХ, то есть уменьшает значение.
Второй аргумент работает аналогично предыдущим функциям за исключением того, что результат всегда изменяется в сторону уменьшения.
ОКРУГЛТ.
Округляет заданное значение до указанного кратного.
Синтаксис: ОКРУГЛТ(число; точность)
Например, формула =ОКРУГЛТ(17; 2) изменяет 17 до ближайшего кратного 2 и возвращает 18 в качестве результата.
Изменение последней оставшейся цифры в большую или в меньшую сторону зависит от величины остатка от деления числового аргумента на аргумент «точность»:
- Если остаток больше или равен половине значения аргумента, ОКРУГЛТ изменяет последнюю цифру с увеличением.
- Если остаток меньше половины аргумента, последняя цифра уменьшается.
Функция ОКРУГЛТ удобна, например, при изменении цен до удобного значения (10 копеек или 50 копеек), чтобы облегчить расчёты наличными.
Также это очень полезно, когда речь идет об округлении времени до желаемого интервала. Например, чтобы округлить время до 5 или 10 минут, просто укажите «0:05» или «0:10» в аргументе:
=ОКРУГЛТ(15,51; -0.1) и =ОКРУГЛТ(-15,51; 0.1).
ОКРВНИЗ.
Функция ОКРВНИЗ используется для преобразования в меньшую сторону до ближайшего кратного указанного значения.
Синтаксис : ОКРВНИЗ(число; точность)
Например, =ОКРВНИЗ(12.5; 2) означает округление 12,5 до ближайшего кратного 2, т.е. до 12.
Функция ОКРВНИЗ выполняет действия на основе следующих правил:
- Если число и точность положительны, то число уменьшается как в строках 2 и 6 на скриншоте ниже.
- Когда исходное число больше нуля, а точность – меньше, то выражение возвращает ошибку #ЧИСЛО!, как в строке 3 на скриншоте ниже.
- В случае если число отрицательное, а аргумент больше нуля, округляется вниз, как в строке 4.
- Когда исходное значение и аргумент отрицательны, то округляется в сторону нуля, как в строке 5.
- Если значение является точным кратным аргументу, никакое действие не производится.
Начиная с Excel 2013 функция была улучшена и появилась ОКРВНИЗ.МАТ. Во многом она лишена недостатков и нелогичностей своей предшественницы:
ОКРВНИЗ.МАТ(число; [точность]; [режим])
Если точность не указана, то по умолчанию подразумевается +1 для положительных чисел и -1 – для отрицательных.
Третий параметр определяет, в какую сторону преобразуются отрицательные значения. Ноль – в меньшую, единица – в большую сторону.
=ОКРВНИЗ.МАТ(-17;2;0) дает результат -18.
=ОКРВНИЗ.МАТ(-17;2;1) возвращает -16.
В остальном всё аналогично ОКРВВНИЗ.
Еще одной приятной особенностью является отсутствие ошибки, если число и точность имеют разные знаки.
ОКРВВЕРХ.
Округляет с увеличением до ближайшего кратного. Имеет тот же синтаксис, что и функция ОКРВНИЗ.
Синтаксис : ОКРВВЕРХ(число; точность)
Например, формула =ОКРВВЕРХ(12,5; 2) преобразует 12,5 до ближайшего кратного 2, то есть до 14.
Функция ОКРВВЕРХ работает на основе правил, аналогичных правилам ОКРВНИЗ, за исключением того, что обычно округляется от 0 вверх.
- Если число и точность положительны, то результат увеличивается, как в строках 2 и 6 на скриншоте ниже.
- В случае если округляемая сумма положительна, а кратное — отрицательное, ОКРВВЕРХ возвращает ошибку, как в строке 3.
- Когда число отрицательное, а точность положительная, то действие производится в сторону увеличения, как в строке 4.
- Ежели оба они отрицательны, результат преобразуется до ближайшего большего по абсолютному значению (то есть, в меньшую сторону), как в строке 5.
Учитывая некоторую нелогичность работы этой функции, в Excel 2013 Microsoft ввела функцию ОКРВВЕРХ.МАТ. Синтаксис ее:
ОКРВВЕРХ.МАТ(число; [точность]; [режим])
Второй аргумент не обязателен. При его отсутствии применяется автоматически +1 для положительных и -1 – для отрицательных чисел.
Параметр «режим» определяет направление изменения отрицательной величины: 0 – в большую сторону, 1 – в меньшую. Вы можете это видеть на скриншоте.
ЦЕЛОЕ.
Из всех рассматриваемых в этом руководстве функций она, вероятно, является наиболее простой в использовании, поскольку используется только один аргумент.
Синтаксис : ЦЕЛОЕ(число)
Положительные величины округляются до 0, а отрицательные – от 0. То есть, действие происходит без учёта знака, по модулю. Например, =ЦЕЛОЕ(5.5) возвращает 6, а =ЦЕЛОЕ(-5.5) даёт результат -6.
ОТБР.
Обрезает числовое значение до указанного числа знаков после запятой.
Синтаксис: ОТБР(число; [число_цифр])
Число_цифр – необязательный аргумент, определяющий точность усечения. То есть сколько десятичных разрядов должно в итоге остаться. Если опущен, то число обрезается до целого.
Функция ОТБР придерживается следующих правил:
- Если второй параметр положительный, то результат усекается до указанного количества цифр справа от десятичной точки.
- Когда он отрицательный, он усекается до указанного количества цифр слева от десятичной точки.
- Если равен нулю или вовсе отсутствует, то этом случае функция ОТБР работает аналогично ЦЕЛОЕ, поскольку обе в результате возвращают целые числа. Однако ОТБР просто удаляет дробную часть, а ЦЕЛОЕ округляет до целого.
- Например, =ОТБР(-7,4) возвращает -7, а =ЦЕЛОЕ(-7,4) – -8, потому что это ближайшее меньшее целое.
Вот как это выглядит:
ЧЁТН и НЕЧЁТ.
Здесь всё достаточно просто — округляем указанную сумму до целого в определённую сторону.
НЕЧЁТ(число) — до ближайшего нечетного целого.
ЧЁТН(число) — до четного целого.
- Если аргумент не является числовым, функции возвращают ошибку #ЗНАЧ!.
- Если число отрицательное, оно изменяется в сторону уменьшения, от нуля.
Функции ЧЁТН и НЕЧЁТ могут оказаться полезными при обработке элементов, которые входят в пары.
Например:
=НЕЧЁТ(2,4) возвращает 3
=НЕЧЁТ(-2,4) – получаем -3
=ЧЁТН(2,4) равно 4
=ЧЁТН(-2,4) равно -4
Использование формул округления в Excel.
Как видите, в Excel существует множество функций округления в зависимости от конкретной цели. Надеемся, что эти примеры дадут вам некоторые подсказки о том, как быстро и правильно настроить округление чисел в Excel в соответствии с вашими задачами.
Как округлить десятичные дроби до нужного количества разрядов.
В зависимости от ситуации вы можете округлить десятичные дроби в любую сторону или на основании математических правил округления:
Функция ОКРУГЛВВЕРХ – всегда меняет десятичную дробь в сторону увеличения.
Функция ОКРУГЛВНИЗ – делает то же самое, но назад.
ОКРУГЛ – изменение вверх, если за цифрой округления следует равная или больше 5, в противном случае – вниз.
В качестве примера округлим десятичные значения в столбце A до двух знаков после запятой. В первом аргументе вы вводите ссылку на ячейку, содержащую сумму, а во втором аргументе вы указываете количество десятых, которое вы хотите оставить.
=ОКРУГЛВВЕРХ(A3; 2) – формула округления в Excel до двух знаков вверх.
=ОКРУГЛВНИЗ(A3; 2) – уменьшение, до двух знаков.
=ОКРУГЛ(A3; 2) – изменение по стандартным правилам, в зависимости от того, больше или меньше 5 третий десятичный разряд.
Округление отрицательных чисел.
Когда дело доходит до работы с отрицательными числами, результаты, возвращаемые рассмотренными нами функциями округления Excel, могут показаться совершенно нелогичными 
Когда функция ОКРУГЛВВЕРХ применяется к отрицательным величинам, то считается, что они меняются с увеличением, хотя на самом деле их значение уменьшается. Например, результатом =ОКРУГЛВЕРХ(-0,5; 0) будет -1, как в строке 7 на скриншоте ниже. Просто округляется абсолютное значение, без учёта знака. А потом уже подставляется минус, если он был.
Известно, что функция ОКРУГЛВНИЗ уменьшает, хотя отрицательные числа могут увеличиваться в значении. Например, формула =ОКРУГЛВНИЗ(-0,5; 0) возвращает 0, как в строке 8 ниже. Причина та же: знак «минус» отбрасывается, число округляется в меньшую сторону, а затем вновь добавляется минус. В результате по абсолютной величине результат уменьшился, но с учетом минуса перед ним — по законам математики увеличился.
Итак, логика округления отрицательных чисел очень проста. Каждый раз, когда вы используете функцию ОКРУГЛ или аналогичную ей для отрицательного числа, оно сначала преобразуется в его абсолютное значение (без знака минус), затем выполняется операция округления, и уже после этого отрицательный знак повторно применяется к результату.
При работе с отрицательными величинами рекомендую обратить внимание на функции ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ, о которых мы рассказывали выше. Эта не совсем понятная логика там исправлена.
Как извлечь десятичную часть числа?
Если вы хотите извлечь дробную часть десятичного числа, вы можете использовать функцию ОТБР, чтобы отбросить десятичные разряды, а затем вычесть это целое из исходного числа.
=A3 — ОТБР(A3; 0)
Как показано на скриншоте ниже, формула в столбце B отлично работает как для положительных, так и для отрицательных величин.
При этом, если вы хотите получить абсолютное значение (десятичную часть без знака минус), оберните формулу в функцию ABS:
=ABS(A3-ОТБР(A3;0))
Как округлить десятичную дробь до целого числа.
Как и в случае с десятичными знаками, существует несколько функций для превращения дробного числа в целое.
ОКРУГЛВВЕРХ.
Округляя до большего целого, используйте формулу ОКРУГЛВВЕРХ с параметром, равным 0. Например, =ОКРУГЛВВЕРХ(5,5; 0) десятичное число изменяется с 5,5 до 6.
А вот для отрицательных чисел использовать =ОКРУГЛВВЕРХ(-5.5; 0) и получить в результате -6 мне кажется не совсем правильным.
При округлении отрицательных чисел, чтобы избежать путаницы, используйте
=ОКРВВЕРХ.МАТ(-5.5; -1; 0), что даёт -5. Это математически верно.
ЦЕЛОЕ или ОКРУГЛВНИЗ.
Округляя до целого положительного числа с уменьшением, используйте ЦЕЛОЕ или ОКРУГЛВНИЗ также с параметром 0. Например, обе следующие формулы преобразуют 5,5 в 5:
=ОКРУГЛВНИЗ(5,5; 0)
=ЦЕЛОЕ(5,5)
Однако для отрицательных чисел они дают другие результаты — ЦЕЛОЕ преобразует отрицательные десятичные дроби, уменьшая значение, а ОКРВНИЗ делает результат больше — в сторону 0:
=ОКРУГЛВНИЗ(-5.5; 0) возвращает -5.
=ЦЕЛОЕ(-5.5) возвращает -6.
Более логично использовать –
=ОКРВНИЗ.МАТ(-5.5;-1;0) – дает -6, что соответствует правилам математики.
ОТБР
Чтобы удалить дробную часть без изменения целой части, используйте формулу ОТБР с опущенным вторым аргументом. Например, =ОТБР(5,5) обрезает десятичную часть (,5) и оставляет целую часть (5).
НЕЧЁТ или ЧЁТН
Чтобы изменить десятичную дробь до нечетного целого, используйте функцию НЕЧЁТ:
=НЕЧЁТ(5,5) возвращает 7.
Чтобы изменить десятичную дробь до четного целого, используйте функцию ЧЁТН:
=ЧЁТН(5,5) возвращает 6.
Округлить до 0,5
Microsoft Excel предоставляет 3 функции, которые позволяют округлять числа до ближайшего кратного 0,5. Можете использовать любую из них.
- Если нужно округлить вниз до 0,5, используйте функцию ОКРВНИЗ, например:
=ОКРВНИЗ(A3; 0,5).
- Чтобы изменить вверх до 0,5, примените, функцию ОКРВВЕРХ
=ОКРВВЕРХ(A3; 0,5).
- Округляя просто до 0,5, используйте функцию ОКРУГЛТ. Например, =ОКРУГЛТ(A3; 0,5).
То, как изменится сумма, зависит от остатка от деления её на второй аргумент (0,5). Если остаток равен половине от 0,5 или превышает его, то округляется в большую сторону, в противном случае – в меньшую.
Как видите, функцию ОКРУГЛТ можно использовать только для положительных значений. При применении её к отрицательным числам возвращается ошибка #ЧИСЛО!.
Округлить до 5/10/100/1000
Округление до пяти, десяти, сотен или тысяч выполняется так же, как и до 0,5, рассмотренное в предыдущем примере.
Округлить до 5.
Вы можете использовать одну из следующих формул:
- Для того, чтобы округлить вниз до 5:
=ОКРВНИЗ($A3, 5) - Вверх до 5:
=ОКРВВЕРХ($A3, 5) - В любую сторону до 5:
=ОКРУГЛТ($A3, 5)
Округлить до 10.
Если нужно округлить ваши суммы до десятка, укажите 10 во втором аргументе:
Округлить до 100.
Округление до сотни выполняется таким же образом, с учётом того, что вы ставите 100 во втором аргументе:
Думаю, вы понимаете, что точно так же вы можете округлить число и до тысячи. Для этого просто вновь измените второй аргумент.
Те же методы можно использовать с целью округления чисел до других кратных величин. Например, вы можете округлить цены до 50 копеек (кратно 0,5), или время до одного дня. Кстати, о времени: знаете ли вы, как преобразовать его в ближайший час, 5 или 10 минут? Если нет, то ответы вы найдете в следующем разделе
Округление времени.
Может возникнуть много ситуаций, когда вам нужно округлить значения времени. И опять же, вы можете использовать разные функции в зависимости от вашей цели.
Пример 1. Как округлить время до часа.
Когда время находится в столбце A, вы можете использовать одну из следующих функций для округления времени до часа:
- до часа (вверх или вниз) –
=ОКРУГЛТ($A$2; «1:00»)
=ОКРУГЛТ($A$2; ВРЕМЯ(1;0;0))
=ОКРУГЛ($A$2*24;0)/24
- вперёд до часа –
=ОКРВВЕРХ($A$7; «1:00»)
=ОКРВВЕРХ($A$7; ВРЕМЯ(1;0;0))
=ОКРУГЛВВЕРХ($A$7*24;0)/24
- назад до часа –
=ОКРВНИЗ($A$12; «1:00»)
=ОКРВНИЗ($A$12; ВРЕМЯ(1;0;0))
=ОКРУГЛВНИЗ($A$12*24;0)/24
В формулах ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ вы умножаете значение времени на 24 (количество часов в сутках), чтобы преобразовать время в часы. Затем вы используете одну из функций округления десятичного значения до целого числа, после чего делите на 24, чтобы вернуть рассчитанное значение обратно в формат времени.
Если ваши временные метки включают значения даты, используйте функцию ЦЕЛОЕ и ОТБР. Ведь в Excel дата и время хранятся как обычные числа. Целая часть представляет дни, а дробная часть – время. Нам нужно извлечь только дробную часть, отбросив дни. Например:
=ОКРУГЛТ($A$2, «1:00») — ЦЕЛОЕ(A2)
=ОКРУГЛТ($A$2, ВРЕМЯ(1,0,0)) — ОТБР(A2)
=ОКРУГЛ($A$2*24; 0)/24 — ЦЕЛОЕ(A2)
На скриншоте показаны и другие формулы:
Пример 2. Округление времени до ближайших 5, 10, 15 минут.
Если вы хотите округлить время в таблице Excel до пяти или десяти минут или четверти часа, вы можете использовать те же методы, что и продемонстрированные выше. Но замените «1 час» на желаемое количество минут в формулах.
- Если округлить время в A3 до ближайших 15 минут:
=ОКРУГЛТ($A3; «00:15»)
=ОКРУГЛТ($A3; ВРЕМЯ(0;15;0))
- Чтобы проделать это в большую сторону:
=ОКРВВЕРХ($A3; «00:15»)
=ОКРВВЕРХ($A3; ВРЕМЯ(0;15;0))
- А теперь — в меньшую:
=ОКРВНИЗ($A3; «00:15»)
=ОКРВНИЗ($A3; ВРЕМЯ(0;15;0))
Если вы знаете (или можете рассчитать), какая часть дня состоит из определенного количества минут, тогда у вас не будет проблем с использованием функций ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.
Например, зная, что 15 минут – это 1/96 часть дня, вы можете использовать одну из следующих формул, чтобы округлить время до ближайшей четверти часа.
- до 15 минут: =ОКРУГЛ(A3*96;0)/96
- В большую сторону: =ОКРУГЛВВЕРХ(A3*96;0)/96
- В меньшую сторону =ОКРУГЛВНИЗ(A3*96;0)/96
Мы представили вам множество способов округления в Excel. Надеюсь, теперь вы знаете, как среди всех этих функций выбрать ту, которая лучше всего подходит для ваших нужд.
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как умножить число на процент и прибавить проценты — Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак… 
Как считать проценты в Excel — примеры формул — В этом руководстве вы познакомитесь с быстрым способом расчета процентов в Excel, найдете базовую формулу процента и еще несколько формул для расчета процентного изменения, процента от общей суммы и т.д.… 
Как сделать пользовательский числовой формат в Excel — В этом руководстве объясняются основы форматирования чисел в Excel и предоставляется подробное руководство по созданию настраиваемого пользователем формата. Вы узнаете, как отображать нужное количество десятичных знаков, изменять выравнивание или цвет шрифта,… 
7 способов поменять формат ячеек в Excel — Мы рассмотрим, какие форматы данных используются в Excel. Кроме того, расскажем, как можно быстро изменять внешний вид ячеек самыми различными способами. Когда дело доходит до форматирования ячеек в Excel, большинство… 
Как удалить формат ячеек в Excel — В этом коротком руководстве показано несколько быстрых способов очистки форматирования в Excel и объясняется, как удалить форматы в выбранных ячейках. Самый очевидный способ сделать часть информации более заметной — это… 
Как преобразовать текст в число в Excel — 10 способов. — В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…
Содержание
- 1 Рассмотрим пример того, как преобразовать формат ячеек, чтобы числа отображались в виде тысяч.
- 1.1 Следует выполнить последовательность действий:
- 2 Способ 1. Формула
- 3 Способ 2. Формат ячеек
Предположим, у Вас есть большая таблица, значения в которой выражены в миллионах рублей. Например:
Вам же нужно предоставить эти данные в тысячах рублей. Как сделать так, чтобы не заходить в каждую ячейку и не делить ее на 1000?
Выделяем нужную область и используем для этих целей «Формат ячеек»:
В закладке «Число» выбираем (все форматы) и находим в окошке «Тип» выделенный формат:
Далее в окошке «Тип» ставим курсор после первого нуля перед запятой и делаем пробел. Как видим, число, указанное в образце, преобразилось:
Нажимаем ОК и получаем желаемый результат:
Стоит отметить, что Excel читает такие числа в их первоначальном виде, т.е. в миллионах, что видно в строке формул, поэтому если Вам нужно использовать такие числа в расчетах, не забудьте делить их на 1000, если расчеты производятся в тысячах рублей:
Большинство людей используют программу «Excel» для расчёта и аналитики экономических показателей процессов, происходящих на предприятии, то есть
фактически производят подсчет денег (рублей, гривен, долларов, тенге и т.д.).
Для красивого и понятного отображения денежных величин в программе «Excel» есть специальный финансовый формат, который делит число на разряды и ставит после числа единицу измерения (наименование валюты, в которой произведен расчет).
Присвоить ячейке такой формат можно нажав соответствующую иконку в меню быстрого доступа либо выбрав в настройках формата ячейки финансовый формат.
Этот формат удобен, когда вычисляемые суммы невелики — пяти-шестиразрядные числа (до пяти-шести нулей).
Если расчеты ведутся глобальные, на сотни тысяч или миллионов рублей, например, приведенный выше формат неудобен.
Необходимо, чтобы числа отображались в виде тысяч или миллионов рублей.
Рассмотрим пример того, как преобразовать формат ячеек, чтобы числа отображались в виде тысяч.
Следует выполнить последовательность действий:
1) Выделить нужные ячейки.
2) Кликом правой кнопки по выделению вызвать контекстное меню.
3) Кликнуть по надписи: «Формат ячеек…»
4) В появившемся окне выбрать «Все форматы»
5) Записать в строку «Тип» нужный формат: 0,0 » тыс. руб»
Полученный результат перевода в тысячи
6) Нажать «Ок».
Разберем варианты перевода данных в тысячи, миллионы и другие порядки в Excel для сокращения записей (например, запись вида 123 456 руб. в 123,4 тыс. руб.).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Проблема наглядной визуализации данных всегда остро стоит перед любым пользователем программы Excel — сегодня мы остановимся на аспектах отображения чисел с выделением размерности.
К примеру, при виде числа 123 456 789 не сразу сообразишь что это за число, а уж при виде большой таблицы с подобными числами глаза точно устанут и таблица станет не читаемой.
Проблему с округлением чисел мы можем решить 2 способами:
- Формула. Непосредственное деление содержимого ячейки на требуемый делитель (тысяча, миллион, миллиард и т.д.);
- Формат. Изменения отображения ячейки с помощью настройки формата ячейки.
Начнем с более очевидного и простого варианта.
Способ 1. Формула
Чтобы перевести числа в тысячи, нужно эти числа разделить на тысячу.
Достаточно очевидный факт, тем не менее именно в простоте и заключается суть применения варианта с формулами.
Возьмем таблицу с произвольными числами (продублируем данные в соседний столбец B для более понятного пояснения):
Оставим столбец A неизменным, а работать будем со столбцом B.
Запишем в любую неиспользуемую ячейку число 1 000 (или 1 000 000, в зависимости от порядка, который мы хотим показать).
Копируем данную ячейку, а затем выделяем диапазон ячеек (ячейки B2:B10), где будем менять размерность.
Воспользуемся инструментом Специальная вставка — щелкаем правой кнопкой мыши по выделенному диапазону и выбираем Специальную вставку (либо воспользуемся сочетанием клавиш Ctrl+Alt+V):
В появившемся окошке в разделе Операция выбираем Разделить и щелкаем на ОК, в результате получаем (для наглядности я добавил еще 1 знак после запятой):
Минус данного способа в том, что операцией деления мы меняем содержимое ячеек, что не всегда удобно и возможно.
Теперь перейдем к рассмотрению более сложного, однако более гибкого и красивого варианта.
Способ 2. Формат ячеек
Помимо изменения самих ячеек, мы можем менять и их формат отображения.
К тому же, если перед нами стоит задача, например, перевести рубли в тысячи рублей, то мы сможем это сделать только данным способом (так как предыдущий способ меняет само число, но подпись оставляет неизменной).
Рассмотрим такую же таблицу, что и в примере выше, аналогично выделяем диапазон с данными (B2:B10), щелкаем правой кнопкой мыши и переходим в Формат Ячеек.
Во вкладке Число в разделе Числовые форматы выбираем (все форматы), в правой части окна показывается как будет отображаться ячейка, в моем случае маска отображения «# ##0».
Для того, чтобы сделать число в тысячах добавим 1 пробел в конце записи числа в маске — «# ##0 » (для миллиона 2 подряд пробела и т.д.), нажимаем ОК и получаем аналогичный результат.
Если нужно добавить в запись «тыс. руб.», то в формат маски записываем «# ##0,0 » тыс. руб.»», т.е. ставим пробел именно после записи формата числа (до добавления текстовой записи, а не после):
Скачать файл с примером.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Дата: 28 января 2017 Категория: Excel Поделиться, добавить в закладки или статью
Здравствуйте, друзья. В статье о форматировании данных в Эксель мы рассмотрели, какие форматы существуют и как их применить. Но что, если их недостаточно? Если нам нужно самим, вручную задать, как должны отображаться значения в ячейке? Конечно, есть решение! В этом посте учимся делать пользовательские форматы и значительно упрощать себе жизнь. Представьте, Вы абсолютно точно говорите программе, как отображать данные, а она безотказно выполняет. Это то, к чему мы с Вами стремимся – полный контроль и разумная автоматизация!
Если Вы внимательно читали статью о форматировании чисел, там я рассказывал все способы вызова меню «Формат ячеек». Самый простой из них – выделить ячейки для форматирования и нажать Ctrl+1. На вкладке «Число» Вы увидите все доступные форматы данных. Последней строкой здесь будет «все форматы». Как раз в этом пункте меню, в строке «Тип» записываются пользовательские форматы.
Как это работает? С помощью специальных символов, которые я опишу ниже, мы говорим программе, как отображать данные. Более того, мы можем через точку с запятой задать форматы для:
1. Положительного числа
2. Отрицательного числа
3. Нуля
4. Текста
В зависимости о того, сколько вариантов формата мы зададим, Эксель по-разному их трактует:
1. Задаем один формат – он применится для всех видов значений;
2. Два формата – первый применится для положительных и нулевых значений, второй – для отрицательных;
3. Три формата: первый – для отрицательных, второй – для положительных, третий – для нулевых;
4. Четыре формата: последовательно – положительные, отрицательные, нулевые, текст.
А вот какие служебные символы используются для форматирования данных:
| Символ | Обозначение |
| Общий | Применяется формат «Общий» |
| # | Значащее числовое значение. Незначащие числа не отображаются |
| 0 (ноль) | Значащее число. Несуществующие разряды заменяются нулями слева в старших разрядах |
| ? | Значащее число. Несуществующие разряды заменяются пробелами слева в старших разрядах |
| , | Десятичный разделитель (запятая) |
| Пробел | Разделяет тысячные разряды |
| % | Проценты |
| Повторяет символы, следующие за ним | |
| * | Повторяет символы, следующие за ним до конца ячейки |
| _ (подчеркивание) | Пропуск на ширину следующего за ним символа |
| “” | Вставляет текст, заданный в кавычках |
| @ | Текст |
| Выберите цвет текста из списка: черный, синий, голубой, зеленый, фиолетовый, красный, белый, желтый | |
| n – номер цвета из палитры Excel |
Так же, есть служебные символы форматирования дат:
| Символ | Обозначение |
| ГГ | Год двузначным числом |
| ГГГГ | Год четырехзначным числом |
| М | Номер месяца без первого нуля |
| ММ | Номер месяца с первым нулем |
| МММ | Месяц аббревиатурой |
| ММММ | Месяц (полное название) |
| МММММ | Первая буква месяца |
| Д | День месяца без первого нуля |
| ДД | День месяца с первым нулем |
| ДДД | День недели аббревиатурой |
| ДДДД | День недели (полное название) |
| ч | Час без первого нуля |
| чч | Час с первым нулем |
| м | Минуты без первого нуля |
| мм | Минуты с первым нулем |
| с | Секунды без первого нуля |
| сс | Секунды с первым нулем |
Здесь я привел самые полезные служебные символы, полный перечень можно найти в справке Excel.
Повторюсь, строка с форматом записывается в поле «Тип» меню «Формат ячеек», вкладка «Все форматы»
Комбинируя обычные и служебные символы между собой, можно добиться того вида, который нужен. Давайте посмотрим как это работает на нескольких примерах:
Интересным решением станет условный формат, когда способ форматирования зависит от содержимого ячейки. Это очень похоже на условное форматирование. Например, мне приходилось получать в работу прайс-лист, где в поле «Остатки» было указано не количество, а «много», «мало» и т.д. Отличный вариант, чтобы не дать точных цифр, а лишь подтолкнуть клиента сделать заказ побольше. Вот как это можно реализовать:
Пользовательские форматы – действительно простая тема, но очень мощная и полезная в работе. Напомню, что при установке формата, значение в ячейке не изменяется. Изменяется лишь способ его отображения.
Пользовательский формат сохраняется вместе с рабочей книгой. Чтобы перенести его в другую – переопределите такой же формат и в ней, либо скопируйте в нее одну из отформатированных ячеек.
Проведите пару экспериментов с пользовательскими форматами, и увидите, это может даже быть увлекательным! Часто мои знакомые и ученики пишут огромные форматы один раз, чтобы потом в разы быстрее получать читаемые результаты своих вычислений. Берите на вооружение этот подход и будьте еще на один шаг эффективнее!
Заканчиваю пост об условном форматировании, и обещаю следующую статью – о проверке данных. Для своих постоянных читателей, да и для новичков назову ее обязательной. Читайте и узнаете почему. До новых встреч!
Поделиться, добавить в закладки или статью
Программа Microsoft Excel: округление чисел
Смотрите также Excel».Копируем формулу. Получитсяфункции округления в зададим формуле =ОКРВВЕРХ(C12;10), где запятой будут стоять только умножить цифры,Первый вариант. ячейке A1 на данных в файле, по кнопке справа округлить число 17 ближайшего числа вверх ни к одному листе, кликнуть правой не отображаемых наПрограмма Microsoft Excel работает,В Excel можно так.Excel. Ещё о нескольких число 10 в две цифры, т.д. но и сразуОкругление в Excel до рисунке ниже выглядит или только для от этого значения. до ближайшего четного по модулю;
листу, а ко кнопкой мыши, и
Хранение чисел в памяти Excel
мониторе данным. в том числе, посчитать количество цифр,Третий вариант, смотрите в статье способах читайте в формуле, означает округлитьЕсли поставим 0 округлить их. В целого числа. округленным, но полное определенного диапазона ячеек.Опять открывается окно аргументов применяем функцию ЧЕТН(17).ОКРУГЛВНИЗ – округляет до
всей книги в в появившемся менюС помощью операции округления, и с числовыми символов, слов в. «Округление в Excel». статье «Формула округления до десятков. – после запятой ячейку с суммойКак убрать числа
Округление с помощью кнопок на ленте
число отображается вАвтор: Максим Тютюшев функции. В поле Получаем число 18. ближайшего числа вниз целом, то есть выбрать пункт «Формат Microsoft Excel отбрасывает данными. При выполнении ячейке, количество конкретныхОкруглить в Здесь рассмотрим не в Excel».Округление до сотен в не будет цифр, ставим функцию «ОКРУГЛ». после запятой форматом, строке формул в
Примечание: «Число разрядов» записываемФункцию можно вводить, как по модулю; ко всему файлу.
ячеек». некоторое количество знаков деления или работе букв в тексте,
Округление через формат ячеек
Excel стандартное округление чисел.Есть ещё Excel т.е произойдет Итак, на закладке смотрите в статье верхней части листаМы стараемся как разрядность, до которой
в ячейку, такОКРУГЛТ – округляет число Ставим галочку напротивВ открывшемся окне настроек после запятой. В с дробными числами, например, чтобы проверитьдо половины целого числаПервый вариантфункции округления в Excel:по такой формулеокругление до целого числа Excel «Формулы» в «Число Excel. Формат». и используется в можно оперативнее обеспечивать нам нужно сокращать и в строку с заданной точностью;
Установка точности расчетов
параметра «Задать точность формата ячеек нужно Excel применяется общепринятый программа производит округление. контрольный тест, узнать (0,5).Функция «ЧЕТН» =ОКРВВЕРХ(C15;100), т.д. в Excel
разделе «Библиотека функций» Но здесь есть вычислениях. вас актуальными справочными
дроби. После этого, функций, предварительно выделивОКРВВЕРХ – округляет число как на экране». перейти во вкладку способ округления, когда Это связано, прежде количество по номерам,.Округление в–Другие способы округления. выбираем «Математические», нажимаем один подвох. ЕслиЧтобы остановить округления числа материалами на вашем жмем на кнопку ту ячейку, в с заданной точность
Жмем на кнопку «Число». Если формат число меньше 5 всего, с тем, т.д. Какими способамиНужно округлить числаExcelокругляет число Excel до вверх, до определеннойА если нужно функцию «ОКРУГЛ».
Применение функций
мы убрали все в ячейке, нажмите языке. Эта страница «OK». которой она будет вверх по модулю; «OK», расположенную в данных указан не округляется в меньшую что абсолютно точные произвести такие подсчеты, не до целого, чтобы последняя цифра тех пор, пока цифры, т.д., смотрите
округлить целое числоПоявилось диалоговое окно «Аргументы числа после запятой
- кнопку переведена автоматически, поэтомуКак видим, число округлилось. находиться. Перед каждой
- ОКРВНИЗ – округляет число нижнем левом углу числовой, то нужно
- сторону, а больше дробные числа редко читайте в статье
- числа, а до была «9»
- не получится четное в статье «Округление до десятков, то
- функций».Заполняем так. форматом, то результатУвеличить разрядность
- ее текст может Для того, чтобы
- функцией нужно ставить вниз по модулю окна.
- выбрать именно числовой или равно 5 когда бывают нужны,
«Как посчитать количество целого с половиной.. число Excel в большую ставим «-1» (минусВ строке «Число» может быть некнопки содержать неточности и таким же образом
знак «=». с заданной точностью;Теперь при расчете данных формат, иначе вы – в большую но оперировать громоздким символов в ячейке Например, число 2,3Нам нужно округлить.
сторону». 1). пишем формулу умножения верен. Например, естьна вкладке « грамматические ошибки. Для округлить и всеСуществует и несколько другой
ОТБР – округляет данные будет учитываться отображаемая не сможете регулировать сторону. выражением с несколькими Excel». до числа 2,5 последнюю цифру вФункция «НЕЧЁТ»
Функция «ОКРУГЛВНИЗ»Например, число 123456 (цены на количество). такая таблица.Главная нас важно, чтобы другие данные нужного способ введения функций до целого числа;
величина числа на округление. В центральнойСамым простым способом изменить знаками после запятойЕсть столбец с большим или, число 2,7
числе до цифры–– нужно округлить доВ строке «Число_разрядов»Теперь мы убираем цифры» пока достигается эта статья была столбца, наводим курсор округления. Его особенно
ЧЕТН – округляет данные экране, а не части окна около округление числа — не очень удобно. количеством чисел, введены – до числа 9. Например, числоокругляет число до первогоокругление Excel в меньшую
сотен. Ставим в пишем 2. Это после запятой (форматом количество десятичных разрядов, вам полезна. Просим на нижний правый удобно использовать, когда до ближайшего четного
та, которая хранится надписи «Число десятичных это выделить ячейку Кроме того, существуют вплоть до копейки. 3,5. Чтобы всегда 676 нужно округлить нечетного числа сторону строке «Число_разрядов» цифру значит – 2 ячейки). Получилось так. которые нужно отобразить.
вас уделить пару угол ячейки с есть таблица со
числа; в памяти Excel. знаков» просто указываем или группу ячеек, числа, которые в Мне нужны вплоть была после запятой, до числа 679.., оставляя после запятой -2 (минус 2). знака после запятой.Получилось: 2+3 = 6.По мере увеличения количества секунд и сообщить, округленным значением, жмем значениями, которые нужноНЕЧЕТН – округляет данные Настройку же отображаемого цифрой то число и находясь во принципе точно не до тысяч, т.
цифра «5».
lumpics.ru
Отключение округления чисел
Или, число 562Функция «ОТБР» столько цифр, сколько Получилось такое число Нажимаем «ОК». Получилось Так получилось, потому разрядов формат изменяется помогла ли она на левую кнопку преобразовать в округленные до ближайшего нечетного числа можно провести знаков, которое желаем вкладке «Главная», нажать округляются. Но, в е. чтобы вместо,В ячейке В16 округлить до числа– мы укажем в 123500. так.
что по правилам с вам, с помощью мыши, и протягиваем числа в отдельном числа. любым из двух видеть при округлении. на ленте на то же время,
к примеру, 134 напишем такую формулу. 559.округляет до целого числа, строке диалогового окнаКак ещё можноФормула такая. математики, цифра 5Общий кнопок внизу страницы. её вниз до столбике.Для функций ОКРУГЛ, ОКРУГЛВВЕРХ способов, о которых После этого, выполняем кнопку «Увеличить разрядность» недостаточно точное округление 432 344,42 отображалось =ОКРУГЛТ(A16;1)+0,5
Таблица такая. отбрасывая все цифры «Число_разрядов». округлить в ExcelТеперь копируем эту
после запятой округляетсяна Для удобства также конца таблицы.Для этого, переходим во
и ОКРУГЛВНИЗ следующий мы говорили выше. клик по кнопке или «Уменьшить разрядность». может привести к 134 432, аПолучится так.В ячейке В7 пишем
после запятой.Функция «ОКРВВЕРХ». формулу вниз перетаскиванием. в большую сторону.Число приводим ссылку наПосле этого, все значения
Подробнее
-
вкладку «Формулы». Кликаем
support.office.com
Округление в Excel.
формат ввода: «НаименованиеЕсли же вы хотите «OK». Обе кнопки располагаются грубым ошибкам в рубли и копейкиЧетвертый вариант. такую формулу. =ОКРУГЛ(A7;-1)-1Бывает, нужно–Функция «ОКРУГЛ» Получилось так.
Чем больше цифр
и автоматически настраивается оригинал (на английском
в нужном столбце по копке «Математические». функции (число;число_разрядов). То изменить величину округленияЕсли в предыдущих случаях, в блоке инструментов ситуациях, где требуется были не видны.ОкруглениеКопируем формулу внизокруглить числа в Excelокругление Excel в большую
–Но, если мы ещё после запятой, тем
ширина столбца. языке) . будут округлены. Далее, в открывшемся есть, если вы, при расчете относительно устанавливаемые параметры влияли «Число». При этом, именно точность. К
Со знаками послеExcel по столбцу. Какими
до кратного числа сторону до целогоокругляет число в Excel в эту ячейку точнее считает.Примечание:Числа с десятичными знаками
Как видим, существуют два
списке выбираем нужную
например, хотите округлить одной или нескольких
только на внешнее будет округляться только счастью, в программе запятой все понятно,
в большую сторону способами быстро скопировать. Например, чтобы все числа по правилам математики. с формулой округленияЗдесь оставили две цифры Если ширина столбца не могут округляться на основных способа округлить функцию, например ОКРУГЛ. число 2,56896 до
ячеек, но не отображения данных, а
отображаемое число, но Microsoft Excel имеется а что делать
. формулу по столбцу, числа делились на.Числа до 5 установим формат ячейки
после запятой (форматом
изменяется и в листе. Это происходит, видимое отображение числа:
После этого, открывается окно трех разрядов, то хотите понижать точность при расчетах использовались для вычислений, при возможность самим пользователям с рублями доExcel округляет по смотрите в статье
три без остатка.Формула такая. округляет в меньшую с одной цифрой
ячейки). Получилось так. ячейке отображаются символы если столбец недостаточно
с помощью кнопки аргументов функции. В применяете функцию ОКРУГЛ(2,56896;3). расчетов в целом
более точные показатели необходимости будут задействованы устанавливать, как будут запятой? Вручную перебивать
законам математики – «Копирование в Excel» Как округлить числаПолучилось так. сторону. Числа с после запятой, тоЗдесь мы убрали в
;;##, измените ширину широк для целого на ленте, и поле «Число» можно На выходе получается
для документа, то (до 15 знака), до 15 разрядов округляться числа. не вариант - числа до 5 тут.
до кратного числа,Здесь число по 5 до 10
число округлится до столбце форматом ячеек столбца, дважды щелкнув числа или число
путем изменения параметров ввести число вручную, число 2,569. в этом случае, то сейчас мы чисел.
Скачать последнюю версию слишкой большой массив. в меньшую сторону,Получится так. смотрите в статье формуле округлилось в округляет в большую одной цифры после нули после запятой,
правый край его содержит больше символов формата ячеек. Кроме но если мыДля функций ОКРУГЛТ, ОКРВВЕРХ лучше всего воспользоваться расскажем, как изменитьПри нажатии на кнопку
ExcelПолосатый жираф алик числа больше 5Второй вариант
«Сумма чисел кратных большую сторону, хотя сторону. запятой. а в итоговой заголовка.
после десятичного разделителя, того, можно изменить хотим автоматически округлить и ОКРВНИЗ применяется возможностями, которые предоставляет саму точность расчетов. «Увеличить разрядность», количествоВсе числа, с которыми: Ну, хотя бы
в большую сторону.. 3 в Excel». по правилам математикиФункция «ОКРУГЛВВЕРХ»В желтой ячейке мы
ячейке оставили две
Округление числа
чем позволяет выбранный и округление реально данные всей таблицы, такая формула округления: функция «ОКРУГЛ», иДля этого, переходим во
внесенных знаков после работает программа Microsoft так : =ОКРУГЛЗдесь рассмотрим, какОкругление вКак умножить время
нужно округлить в– установили с помощью цифры после запятой.Часто возникает вопрос формат. рассчитываемых данных. Это тогда кликаем по «Наименование функции(число;точность)». Например,
различные её вариации, вкладку «Файл». Далее,
запятой увеличивается на Excel, делятся на (C1/1000;0). Сделать параллельный настроить такое округлениеExcel на число (деньги), меньшую сторону.
округление Excel в большую формата ячейки одну Получилось так. «Например, если вы введете
также можно сделать кнопке справа от чтобы округлить число а также некоторые перемещаемся в раздел
один. точные и приближенные. с исходным столбец – числа до, чтобы последняя цифра например, часы работыФункция «ОКРВНИЗ» сторону цифру после запятой.Второй вариант.Как округлить число в
число в ячейку двумя способами: изменением окна введения данных. 11 до ближайшего другие функции. «Параметры».При нажатии на кнопку В памяти хранятся
excel-office.ru
Округление Excel в большую сторону.
с такой формулой. 3 в меньшую была «99» на стоимость одного– , оставляя после запятойКак правильно написать вФормула округления в Excel. Excel с форматом
настроек книги вОкно аргументов функции сворачивается.
числа кратного 2, Среди основных функций, которыеОткрывается окно параметров Excel. «Уменьшить разрядность» количество числа до 15
Потом можно скопировать сторону, числа больше. часа, смотрите вокругление Excel в меньшую столько цифр, сколько строке «Число_разрядов»У нас естьправильно, точно?» В
Общий
целом, или путем Теперь нужно кликнуть
вводим функцию ОКРУГЛТ(11;2). регулируют округление, следует В этом окне цифр после запятой разряда, а отображаются полученный столбец и 3 в большую
Нужно округлить числа
статье «Как умножить сторону до целого
мы укажем в . такая таблица. Excel можно, как у всех
применения специальных функций. по самой верхней На выходе получается выделить такие:
переходим в подраздел уменьшается на одну. до того разряда,
перенести значения в сторону. Например, так.
так, чтобы последние время на число
числа строке диалогового окнаЕсли поставим цифруЕсли мы умножим ценуокруглить число точно или
ячеек нового листа, Выбор конкретного способа ячейке столбца, данные число 12.ОКРУГЛ – округляет до «Дополнительно». Ищем блокТакже можно выставить округление который укажет сам исходный (если требуется).В ячейке В28 написали две цифры в
в Excel».. «Число_разрядов».
1, то после
на количество, то
в большую (меньшую) Excel автоматически отобразит зависит от того, которого мы собираемся
Функции ОТБР, ЧЕТН и указанного числа десятичных настроек под названием с помощью настроек пользователь. Но, при Меню — Правка
такую формулу. =ОКРУГЛВВЕРХ(A28-0,3;) числе были «99».Есть много способовФункция «ОКРУГЛТ»Например, запятой будет стоять после запятой будет
сторону, до целого меньше разрядов после
собираетесь ли вы округлить. После того, НЕЧЕТ используют следующий знаков, согласно общепринятым
«При пересчете этой формата ячеек. Для этом, все расчеты — Специальная вставкаЕще способы округленияВ ячейке С7округления в–округление до десятков в одна цифра. стоять несколько цифр. числа, до десятков, десятичного разделителя, чтобы применять подобный вид
excel-office.ru
Как в экселе округлить числа с единиц до тысяч? или с тысяч до миллионов?
как значение занесено формат: «Наименование функции(число)». правилам округления; книги». Настройки в этого, нужно выделить выполняются согласно хранимых — Вставить «Значения». смотрите в статье пишем такую формулу.Excelокругляет число Excel с ExcelЕсли поставим цифруНо с помощью формулы, сотен число поместилось в округления для всех в окно, кликаем
Для того, чтобыОКРУГЛВВЕРХ – округляет до данном бока применяются диапазон ячеек на в памяти, а Наслаждаться результатом. «Формула округления в =ОКРУГЛ(A7;-2)-1.Как применить точностью, которую мыпроизводится по такой 2, то после мы сможем, не, т.д.
ячейку. Число в
Если ячейка содержит значение больше 1000, то часто не требуется указывать точное значение, а достаточно указать число тысяч или миллионов, один-два знака после запятой и соответственно сокращение: тыс. или млн.
Если необходимо, чтобы вводимые числовые значения отображались в формате миллионов и тысяч с нотацией соответственно
млн
. или
тыс
. (например,
551 200
отобразить в виде
551,2 тыс., а 5 567 634
в виде
5,6 млн.
), то для реализации этого существует 2 принципиально разных подхода.
Использование формулы
Очевидным подходом для получения желаемого результата является использование простой формулы и, соответственно, дополнительной ячейки, куда эта формула вводится (см.
файл примера
):
=ЕСЛИ(A1<1000;A1; ЕСЛИ(A1<1000000;ОКРУГЛ(A1/1000;1)& » тыс.»;ОКРУГЛ(A1/1000000;1)& » млн.»))
Результат формулы – текстовое значение. Надо также иметь ввиду, что из-за округления число 999 990,00, например, будет отражено в виде 1000,0 тыс.
В
файле пример
а также создана формула на отрицательных значений.
Пользовательский формат
Если нет желания использовать дополнительную ячейку, то с помощью
пользовательского формата
можно изменить, лишь отображение значения в ячейке, а не само значение.
Пользовательский формат
вводим через диалоговое окно
Формат ячеек
.
-
для вызова окна нажмите
CTRL
+
1
; -
выберите (
все форматы
), -
удалите все из поля
Тип;
-
в поле
Тип
введите
[>1000000]#,0 » млн»;[>1000]#,0 » тыс.»;# ##0,00
Очень важно ввести правильно все пробелы, т.к. пробелы разделяют числовые разряды. Для корректного отображения ниже приведена формула с явным указанием местонахождения пробела (пробел заменен словом
Space
).
[>1000000]#,0
SpaceSpace
» млн»;[>1000]#,0
Space
» тыс.»;# ##0,00
При применении такого пользовательского формата будет произведено округление, т.е. число 1 290 000 будет отображено как 1,3 млн., а 1 240 000 как 1,2 млн.