Процедура из статьи доступна в !SEMTools Lite и Pro.
Деление в Excel записывается косой чертой: =A2/B2 разделит значение одной ячейки на другую. Повод обычно простой и очень знакомый: поделить счёт на количество, перевести рубли в тысячи для отчёта, посчитать цену за штуку или среднюю выручку на сотрудника. Почти всё здесь решается одной чертой, а сложности начинаются с нулей и остатков. На ноль Excel делить не умеет и пишет #ДЕЛ/0!, это прячут формулой =ЕСЛИОШИБКА(A2/B2;0); весь столбец на одно число делят =A2/$F$1, а разделить столбец на месте без формул поможет процедура деления.
Формула деления в Excel
Знак деления в формулах это прямая косая черта /, двоеточие или ÷ Excel не поймёт. Черта есть на цифровой клавиатуре и на основной, рядом с правым Shift в английской раскладке.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Сумма | Количество | Цена | Без ошибки |
| 2 | 1500 | 12 | 125=A2/B2 | 125=ЕСЛИОШИБКА(A2/B2;0) |
| 3 | 980 | 0 | #ДЕЛ/0!=A3/B3 | 0=ЕСЛИОШИБКА(A3/B3;0) |
| 4 | 1000 | 3 | 333,3333333=A4/B4 | 333,3333333=ЕСЛИОШИБКА(A4/B4;0) |
В третьей строке количество ноль, и простое деление дало ошибку. Столбец D вместо ошибки показывает 0.
Знаки после запятой при делении не теряются: Excel хранит 15 значащих цифр, а показывает столько, сколько помещается в ячейку.
В английском Excel: =A2/B2
Деление на ноль: ошибка #ДЕЛ/0!
Ошибка возникает, когда делитель равен нулю или ячейка с ним пустая. Убрать её можно двумя способами:
=ЕСЛИОШИБКА(A2/B2;0)
Функция ЕСЛИОШИБКА подставит 0 или любой текст вместо любой ошибки. Если нужно скрыть только деление на ноль и видеть остальные ошибки, проверьте делитель явно:
=ЕСЛИ(B2=0;"";A2/B2)
Разделить весь столбец на одно число
Делитель кладут в отдельную ячейку и закрепляют ссылку на неё знаками $ (F4 после выбора ячейки), потом протягивают формулу вниз:
=A2/$F$1
Так удобно переводить суммы в тысячи: в F1 пишем 1000, и весь отчёт в тысячах. Поменяли курс или единицу в F1, пересчитался весь столбец.
Штатный способ без формул и без надстройки называется специальной вставкой. Она разделит на число из буфера обмена прямо в выделенных ячейках:
- Впишите число в любую свободную ячейку и скопируйте её (Ctrl+C).
- Выделите диапазон, который нужно изменить.
- Нажмите Ctrl+Alt+V (или правая кнопка мыши → «Специальная вставка»).
- В блоке «Операция» выберите «разделить», в блоке «Вставить» оставьте «значения», нажмите OK.
Минус способа в том, что формулы в диапазоне Excel не заменит, а допишет к ним операцию, а ячейку с числом потом придётся удалить.
Разделить столбец на месте процедурой
В !SEMTools то же самое делает процедура «ИЗМЕНИТЬ» → «Числа» → «Арифметические операции» → «Разделить». Выделите диапазон, запустите процедуру, в окне «Разделить ячейки столбца на:» введите число и нажмите OK. Чтобы перевести суммы в тысячи, введите 1000.
- Числа меняются на месте, вспомогательный столбец и копирование значений не нужны.
- Текст и пустые ячейки пропускаются, а числа, сохранённые как текст, обрабатываются и становятся настоящими числами.
- Можно выделить несколько столбцов сразу, а в полной версии и несколько несмежных областей через Ctrl.
- Формулы в выделении заменяются результатом, так что столбец с формулами лучше не выделять.
- Последний шаг отменяет кнопка «Отмена последнего действия» в первой группе ленты, обычное Ctrl+Z после макроса не работает.
- При делителе 0 в ячейки записывается слово «ошибка», поэтому ноль в окне вводить не нужно.
Деление с остатком и без остатка: ЧАСТНОЕ и ОСТАТ
Целочисленное деление, то есть деление без остатка, в Excel делает функция ЧАСТНОЕ, а остаток возвращает ОСТАТ. Когда нужна целая часть и остаток отдельно, например сколько полных коробок и сколько штук останется, помогают две функции.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Число | Делитель | Целая часть | Остаток |
| 2 | 17 | 5 | 3=ЧАСТНОЕ(A2;B2) | 2=ОСТАТ(A2;B2) |
| 3 | 125 | 12 | 10=ЧАСТНОЕ(A3;B3) | 5=ОСТАТ(A3;B3) |
| 4 | 135 | 60 | 2=ЧАСТНОЕ(A4;B4) | 15=ОСТАТ(A4;B4) |
Последняя строка переводит 135 минут в 2 часа 15 минут.
ЧАСТНОЕ отбрасывает дробную часть без округления, ОСТАТ возвращает то, что не поделилось.
В английском Excel: =QUOTIENT(A2,B2)
Минуты в часы одной строкой текста:
=ЧАСТНОЕ(A2;60)&" ч "&ОСТАТ(A2;60)&" мин"
Распределить сумму пропорционально
Разделить премию или бюджет между отделами пропорционально их показателям помогает та же косая черта. Доля строки равна её значение, делённое на сумму всех, умноженная на распределяемую сумму:
| A | B | C | |
|---|---|---|---|
| 1 | Менеджер | Продажи | Премия |
| 2 | Анна | 500 | 50000=$E$1*B2/СУММ($B$2:$B$4) |
| 3 | Борис | 300 | 30000=$E$1*B3/СУММ($B$2:$B$4) |
| 4 | Вера | 200 | 20000=$E$1*B4/СУММ($B$2:$B$4) |
В E1 лежит распределяемая сумма 100 000. Ссылки на неё и на диапазон суммы закреплены знаками $, поэтому формулу можно протянуть.
В английском Excel: =$E$1*B2/SUM($B$2:$B$4)
Деление с округлением
Формат ячейки лишь прячет знаки после запятой, а в сумме они всё равно участвуют. Чтобы результат деления был ровно до копеек, округлите его в формуле:
=ОКРУГЛ(A2/B2;2)
Вверх и вниз округляют ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ, все варианты в статье округление в Excel.
Какую долю составляет число
Доля это тоже деление: часть делят на целое и ставят ячейке процентный формат (Ctrl+Shift+%):
=B2/$B$10
Результат 0,25 в процентном формате покажется как 25%. Умножать на 100 не нужно, иначе получится 2500%.
Почему деление в Excel даёт не тот результат
- #ДЕЛ/0!. Делитель ноль или пустой. Проверьте исходные данные или оберните формулу ЕСЛИОШИБКА.
- #ЗНАЧ!. Одно из чисел записано текстом. Переведите его в число, способы в статье числа как текст в числа.
- Вместо результата дата. Ячейка унаследовала формат даты, поставьте «Общий».
- Слишком много знаков после запятой. Уменьшите разрядность кнопкой на вкладке «Главная» или округлите функцией ОКРУГЛ.
- При протягивании делитель уехал. Закрепите ссылку на него знаками $.
Деление в Excel: частые вопросы
Как в экселе разделить одно число на другое?
Формулой =A2/B2: знак деления в Excel это косая черта.
Как разделить весь столбец на одно число?
Формулой =A2/$F$1 с закреплённой ссылкой, специальной вставкой с операцией «разделить» или процедурой «Разделить» на месте.
Как убрать ошибку #ДЕЛ/0!?
Обернуть деление: =ЕСЛИОШИБКА(A2/B2;0) или =ЕСЛИ(B2=0;"";A2/B2).
Как разделить с остатком?
Целую часть даёт =ЧАСТНОЕ(A2;B2), остаток =ОСТАТ(A2;B2).
Что значит #ДЕЛ/0! в Excel?
Формула делит на ноль или на пустую ячейку. Проверьте делитель или оберните деление в ЕСЛИОШИБКА.
Как распределить сумму пропорционально?
Формулой =$E$1*B2/СУММ($B$2:$B$4): сумма из E1 делится между строками по их доле.
Как разделить всю таблицу на 1000?
Скопировать ячейку с числом 1000, выделить таблицу и выбрать «Специальная вставка» → «Разделить», или запустить процедуру «Разделить» с числом 1000.
Перевести отчёт в тысячи, пересчитать цены по курсу, поделить план на число недель: процедура !SEMTools делит числа прямо в выделении и не трогает текст и пустые ячейки.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: деление в Excel
Делят в Excel косой чертой: =A2/B2, а для всего столбца =A2/$F$1. Ноль в делителе гасит ЕСЛИОШИБКА, целую часть и остаток дают ЧАСТНОЕ и ОСТАТ, ровные копейки ОКРУГЛ. Без формул на месте делит специальная вставка или процедура деления. Остальные действия: сложение, вычитание, умножение, корень и степень, обзор в разделе арифметические операции в Excel.