39 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция остаток от деления excel

Функция остаток от деления excel

В ячейках могут быть записаны данные различных типов: числа, строки, логические значения, даты и другие. Для изменения типа данных или для настройки параметров отображения данных используется меню Формат — Ячейки.

Обычно тип данных распознаётся автоматические. Но если хочется ввести несколько цифр в ячейку, чтобы типом ячейки была строка, нужно в начале записать один символ одинарной кавычки. Это нужно сделать также если хочется, чтобы в ячейке была строка, начинающаяся с символа “=”, то есть чтобы введённый текст не интерпретировался, как формула.

Если в начале содержимого ячейки ввести символ “=”, то содержимое ячейки считается формулой. В формулах могут использоваться адреса других ячеек, константы, функции, операции. В русскоязычном приложении в числовых константах для отделения дробной части используется запятая. Текстовые строки в формулах нужно заключать в двойные кавычки.

Метод 1: делим число на число

Простейший метод деления в Excel – это набор в строке формул арифметического выражения, как в калькуляторе, начиная со знака “=”. При этом, для обозначения деления используется так называемый знак “слэш”, или /. Рассмотрим на практическом примере.

  1. Кликаем по любой незанятой ячейке и начинаем писать выражение со знака “=” (выражение также можно набрать в строке формул, предварительно выбрав свободную ячейку):
    • при помощи клавиатуры вводим делимое число, знак “/” и затем – делитель.

Если в арифметическом выражении помимо деления используются другие операции, то их порядок определяется общими правилами арифметики:

  • сначала выполняется деление и умножение, затем – сложение и вычитание;
  • наибольший приоритет имеют операции, заключенные в скобки.

Одно из базовых математических правил – на ноль делить нельзя. Если попробовать вычислить подобное выражение в Excel, то в ячейке отобразится предупреждение о некорректном делении “#ДЕЛ/0!”.

Ошибка при делении в Эксель

Деление на ноль в Excel

Деление на ноль в Экселе приводит к появлению ошибки #ДЕЛ/0!. Появляется она в результате деления числителя на ноль или на пустую ячейку.

Ошибка в Экселе при делении на ноль

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

Функции ОТБР и ЦЕЛОЕ схожи в том отношении, что обе возвращают целые числа. Функция ОТБР отбрасывает дробную часть числа, а функция ЦЕЛОЕ округляет число в меньшую сторону до ближайшего целого в зависимости от значения дробной части числа. Функции ЦЕЛОЕ и ОТБР различаются только в работе с отрицательными числами: функция ОТБР(-4,3) возвращает значение -4, а функция ЦЕЛОЕ(-4,3) возвращает значение -5, поскольку -5 меньше, чем -4,3.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Усекает число 8,9 и возвращает целую часть (8).

Усекает отрицательное число и возвращает целую часть (-8).

Усекает число между 0 и 1, возвращая целую часть (0).

  • ABS() — эта функция возвращает абсолютное значение переданного ей числа (то же число, но без знака). Например, ABS(3) и ABS(-3) вернут одно и то же значение 3.
  • Int() , Fix() и Round() позволяют по разному округлять числа:
  • Int() возвращает ближайшее меньшее целое;
  • Fix() отбрасывает дробную часть;
  • Round() округляет до указанного количества знаков после запятой.

Однако Round может вернуть не совсем ожидаемый результат. Если мы выполним такую строку кода

MsgBox Round(2.505, 2)

то результатом будет 2,5, хотя предполагалось получить 2,51. Поэтому порой для округления лучше использовать Format :

Dim lRundNum As Long, lMinNum As Long, lMaxNum As Long lMinNum = 1: lMaxNum = 100 Randomize lRundNum = Int(lMinNum + (Rnd() * lMaxNum)) MsgBox lRundNum

Функции ОТБР и ЦЕЛОЕ схожи в том отношении, что обе возвращают целые числа. Функция ОТБР отбрасывает дробную часть числа, а функция ЦЕЛОЕ округляет число в меньшую сторону до ближайшего целого в зависимости от значения дробной части числа. Функции ЦЕЛОЕ и ОТБР различаются только в работе с отрицательными числами: функция ОТБР(-4,3) возвращает значение -4, а функция ЦЕЛОЕ(-4,3) возвращает значение -5, поскольку -5 меньше, чем -4,3.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функции деления в DAX: DIVIDE, QUOTIENT и MOD для Power BI и Power Pivot

до конца распродажи осталось:

Курс DAX - это просто

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI (Power Pivot) защититься от ошибки деления на 0. А также, о том, как из общей суммы секунд вычислить соответствующее число часов, минут и секунд? А если говорить конкретнее, то разберем функции деления в DAX: DIVIDE (деление на ноль), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления).

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

Да, и еще один момент, до 29 октября 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 29 октября 2021 г.): узнать подробнее

До конца распродажи осталось:

DAX функция DIVIDE в Power BI и Power Pivot

DIVIDE () — производит деление с обработкой ошибки «деление на 0». Обработка ошибки заключается в выводе альтернативного результата в случае возникновения ситуации деления на ноль.

Где, альтернатива — (необязательный параметр) значение, которое нужно вывести в случае ошибки деления на ноль (0). По умолчанию выводится пустое значение BLANK ().

Пример формулы на основе DAX функции DIVIDE.

В Power BI Desktop имеется исходная таблица «Реклама», содержащая по каждой дате затраты на рекламу и прибыль, полученную от продаж с этой рекламы:

Исходная таблица в Power BI

Задача — создать в Power BI меру расчета коэффициента ROI (окупаемости затрат на рекламу). Данный коэффициент рассчитывается как сумма всей прибыли деленная на сумму всех затрат.

Сумму значений мы можем рассчитать при помощи DAX функции SUM.

В итоге, формула расчета ROI будет такой:

То есть, мы сложили всю прибыль, сложили все затраты и затем разделили сумму прибыли на сумму затрат.

Вроде бы, все хорошо, но, если мы вынесем эту меру в отчеты Power BI и посмотрим ROI по дням, то в визуализации в одной из строк будет отображаться непонятное слово «Бесконечность»:

Ошибка деления на 0 в Power BI

А все дело в том, что у нас произошла ошибка деления на ноль — прибыль 18000 была разделена на затраты, равные 0. И Power BI вместо этой ошибки вывела значение «Бесконечность».

Для того, чтобы исправить эту ситуацию, в формуле расчета ROI вместо обычного деления нужно использовать рассматриваемую DAX функцию DIVIDE. Она позволит нам произвести деление и обработать все ошибки, возникающие при делении на о. И вместо значения «Бесконечность» вывести то значение, которое нам нужно, например, пустое значение BLANK ().

В итоге, формула расчета ROI на основе функции DIVIDE будет такая:

Где, в первом параметре функции DIVIDE мы указали сумму прибыли, которую нужно разделить, во втором параметре — сумму затрат, на которую делится сумма прибыли. Третий параметр указывать не стали, так как по умолчанию он равен функции BLANK (), что нам и нужно.

В результате, визуализация в Power BI теперь работает правильно:

Результат работы формулы в Power BI на основе DAX функции DIVIDE

Используйте функцию деления DIVIDE всегда, когда имеется хоть малейший потенциал значения нуля в делителе Вашей формулы.

DAX функция QUOTIENT в Power BI и Power Pivot

QUOTIENT () — выполняет деление чисел, входящих в параметры функции и возвращает целочисленную часть от деления.

Пример формулы на основе DAX функции QUOTIENT.

В Power BI имеется исходная таблица «Общая Длительность Звонков», содержащая в себе информацию по общей сумме секунд всех разговоров менеджеров:

Исходная таблица

Требуется рассчитать это количество в целых часах.

Для этого, общее количество секунд нужно разделить на количество секунд в часе (3600). Но, в результате этого деления получится дробное число, а нам нужна только целая часть результата деления. В этой ситуации нам поможет функция QUOTIENT.

В итоге, формула расчета общей длительности звонков в целых часах будет такой:

И в отчете Power BI по каждому менеджеру эта мера выведет количество целых часов, которые затратили менеджеры на звонки:

Результат работы формулы в Power BI на основе DAX функции QUOTIENT

DAX функция MOD в Power BI и Power Pivot

MOD () — возвращает остаток от деления со знаком делителя.

В качестве разбора формулы на основе DAX функции MOD продолжим рассматривать прошлый пример с расчетом длительности разговора менеджеров по телефону в целых часах на основе общей суммы в секундах.

Разделив общее количество секунд на 3600 при помощи QUOTIENT, мы получили целую часть от деления. Но, также, при этом делении мы можем получить и остаток от этого целочисленного деления (в нашем случае, это оставшееся количество секунд за вычетом целых часов из общего количества секунд).

И сделать это можно функцией MOD:

Давайте проверим эту формулу в Power BI:

Результат работы формулы в Power BI на основе DAX функции MOD

Действительно, если мы рассмотрим менеджера Петров из визуализации выше, то 16784 (общее количество секунд) — 14400 секунд (4 часа * 3600) = 2384 (остаток секунд). И функция MOD нам также вывела данное значение 2384.

Теперь, это получившееся значение (остаток секунд) можно еще раз разделить функцией QUOTIENT на 60 и мы получим целое количество минут из этого остатка секунд:

В Power BI вычисление этой формулы будет таким:

Совместная работа DAX функций QUOTIENT и MOD в Power BI

Давайте проверим все вычисления на примере менеджера Петрова: общее количество секунд у него 16784, а 4 часа и 39 минут, это 16740 секунд (60*(4*60+39). Все правильно 16740 входит в общее количество секунд 16784.

Теперь, осталось вычислить окончательный остаток в секундах:

И в Power BI все это будет выглядеть так:

Итоговая визуализация в Power BI на основе функций QUOTIENT и MOD

Напоследок, осталось навести некий «косметический дизайн» — совместим часы, минуты и секунды в единое значение «часы : минуты : секунды», для этого, воспользуемся оператором объединения в языке DAX — & и текстом с двоеточием («:»):

Итоговая визуализация, демонстрирующая совместную работу DAX функций QUOTIENT и MOD в Power BI будет такая:

Итоговая визуализация, демонстрирующая совместную работу DAX функций QUOTIENT и MOD в Power BI

Таким образом, общее количество секунд, затраченное менеджером на звонки мы превратили в соответствующее отображение в часах, минутах и секундах.

Давайте проверим все вычисления, так сказать, на калькуляторе, на примере менеджера Сидоров:

Длительность звонков = 5:9:29, то есть — 5 часов, 9 минут и 29 секунд, что равно 18569 секунд (5*3600+9*60+29). А это, в свою очередь, равно исходной сумме секунд по менеджеру Сидоров (18569 секунд).

На этом, с разбором DAX функций DIVIDE (деление на ноль «0»), QUOTIENT (целочисленное деление) и MOD (остаток от целочисленного деления) в Power BI и Power Pivot, все.

Также, напоминаю Вам, что до 29 октября 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 29 октября 2021 г.): узнать подробнее

До конца распродажи осталось:

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Таблица деления в «Эксель»

Построим таблицу в «Эксель», в ячейки А2:А6 поместим числа, которые будем делить (3, 7, 9, 12, 15), а в ячейки В1:Е1 – числа, НА которые будем выполнять деление (1, 2, 3, 4).

Чтобы составить таблицу деления, важно вспомнить про абсолютные и относительные ссылки в «Эксель». Формула деление в Excel при составлении такой таблицы предполагает использование смешанных ссылок. Так, чтобы адреса ячеек при копировании не смещались, надо закрепить в делимом адресе номер столбца, а в делителе – номер строки. Таким образом, введите в ячейку В2 формулу «=$A2/B$1» и протяните ее до конца строки? а потом и на всю таблицу. Таблица заполнится данными. Встаньте теперь на любую ячейку, допустим, на D5. Формула ее будет следующая: «=$A5/D$1». Все сделали правильно.

голоса
Рейтинг статьи
Читайте так же:
Как перенести данные из внутренней памяти на SD-карту (файлы и приложения)
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector