Для чего нужна функция отбр
Функция ОТБР позволяет отбросить дробную часть в Excel
Функция ОТБР в Excel используется для отбрасывания дробной части числового значения, переданного в качестве первого аргумента, до определенного количества знаков после запятой, которые указаны в качестве второго аргумента, и возвращает усеченное целое число.
Как найти частное от деления с помощью функции ОТБР в Excel
Операция отбрасывания дробной части отличается от округления чисел, тем, что итоговое значение не увеличивается или уменьшается в соответствии с правилами округления чисел. Например, при округлении числа 10,9 до целого будет возвращено число 11. Функция ОТБР вернет число 10.
Пример 1. В столбце таблицы записаны некоторые числа. Найти частное от деления каждого числа на 5. Если результатом является дробное число, отбросить дробную его до части до целого числа.
Вид таблицы данных:
Для нахождения искомых значений используем следующую формулу массива:
С помощью функции ЕСЛИ выполняется проверка наличия остатка от деления (функция ОСТАТ). Если остаток от деления больше нуля, отбрасывается дробная часть с помощью функции ОТБР, иначе – просто выполняется деление числа на 5.
В результате вычисления формулы мы нашли частное от деления в Excel для исходных чисел, разделенных на 5.
Как преобразовать числа десятичной дроби в дату в Excel
Пример 2. В таблице имеется столбец с дробными числами (с разным количеством знаков после запятой). Это закодированная информация, из которой нам нужно раскодировать дату – число дня и месяца для 2020-го года по следующему алгоритму:
Вид таблицы данных:
Для расчета значений используем следующую формулу:
Для получения данных требуемого формата из кодов используем функцию ДАТА. Чтобы вычислить значение номера месяца с помощью функции ОТБР отбрасываем дробную часть оставляя целое число – номер месяца. Для получения номера дня нам следует вычесть от исходного числа его целое число. Таким образом получим дробную часть, для которой следует переместить запятую вправо на 2 знака с помощью умножением дробной части на 100.
Результат вычислений для всех ячеек:
Как видно, некоторые ячейки содержат код ошибки поскольку 54 и 33 превышают допустимое число дней в месяце. Или 13 и 114 превышают количество месяцев в году. Обработаем это исключение с помощью формулы с проверкой на ошибки:
12;(A2-ОТБР(A2))*100>ДЕНЬ(КОНМЕСЯЦА(ДАТА(2020;ОТБР(A2);1);0)));»нет даты в коде»;ДАТА(2020;ОТБР(A2);(A2-ОТБР(A2))*100))’ >
С помощью функции ИЛИ в первом аргументе ЕСЛИ проверяем значения при двух условиях:
Определим значения с помощью новой формулы и запишем в соседнем столбце:
Особенности использования функции ОТБР в Excel
Функция имеет следующую синтаксическую запись:
=ОТБР( число; [число_разрядов])
Описание аргументов функции:
excel отбросить целую часть
Извлечем из числа 5,324 дробную часть, т.е. 0,324, а также его целую часть.
Если делитель равен 1, то функция ОСТАТ() возвращает дробную часть положительного числа (см. файл примера ).
Формула =ОСТАТ(3,56;1) вернет 0,56.
Универсальная формула для выделения дробной части числа, находящегося в ячейке А1:
=ОСТАТ(A1;ЕСЛИ(A1
Функция ОТБР в Excel используется для отбрасывания дробной части числового значения, переданного в качестве первого аргумента, до определенного количества знаков после запятой, которые указаны в качестве второго аргумента, и возвращает усеченное целое число.
Как найти частное от деления с помощью функции ОТБР в Excel
Операция отбрасывания дробной части отличается от округления чисел, тем, что итоговое значение не увеличивается или уменьшается в соответствии с правилами округления чисел. Например, при округлении числа 10,9 до целого будет возвращено число 11. Функция ОТБР вернет число 10.
Пример 1. В столбце таблицы записаны некоторые числа. Найти частное от деления каждого числа на 5. Если результатом является дробное число, отбросить дробную его до части до целого числа.
Вид таблицы данных:
Для нахождения искомых значений используем следующую формулу массива:
С помощью функции ЕСЛИ выполняется проверка наличия остатка от деления (функция ОСТАТ). Если остаток от деления больше нуля, отбрасывается дробная часть с помощью функции ОТБР, иначе – просто выполняется деление числа на 5.
В результате вычисления формулы мы нашли частное от деления в Excel для исходных чисел, разделенных на 5.
Как преобразовать числа десятичной дроби в дату в Excel
Пример 2. В таблице имеется столбец с дробными числами (с разным количеством знаков после запятой). Это закодированная информация, из которой нам нужно раскодировать дату – число дня и месяца для 2020-го года по следующему алгоритму:
Вид таблицы данных:
Для расчета значений используем следующую формулу:
Для получения данных требуемого формата из кодов используем функцию ДАТА. Чтобы вычислить значение номера месяца с помощью функции ОТБР отбрасываем дробную часть оставляя целое число – номер месяца. Для получения номера дня нам следует вычесть от исходного числа его целое число. Таким образом получим дробную часть, для которой следует переместить запятую вправо на 2 знака с помощью умножением дробной части на 100.
Результат вычислений для всех ячеек:
Как видно, некоторые ячейки содержат код ошибки поскольку 54 и 33 превышают допустимое число дней в месяце. Или 13 и 114 превышают количество месяцев в году. Обработаем это исключение с помощью формулы с проверкой на ошибки:
12;(A2-ОТБР(A2))*100>ДЕНЬ(КОНМЕСЯЦА(ДАТА(2020;ОТБР(A2);1);0)));»нет даты в коде»;ДАТА(2020;ОТБР(A2);(A2-ОТБР(A2))*100))’ >
С помощью функции ИЛИ в первом аргументе ЕСЛИ проверяем значения при двух условиях:
Определим значения с помощью новой формулы и запишем в соседнем столбце:
Особенности использования функции ОТБР в Excel
Функция имеет следующую синтаксическую запись:
=ОТБР( число; [число_разрядов])
Описание аргументов функции:
В этой статье описаны синтаксис формулы и использование функции ОТБР в Microsoft Excel.
Описание
Усекает число до целого, отбрасывая дробную часть.
Функция ОТБР в Excel
Функция ОТБР в Excel предназначена для работы с дробными числами. Как можно догадаться из названия, пусть и сокращённого (что в Excel не редкость), формула ОТБР отбрасывает дробную часть числа полностью или частично, оставляя на выходе целое число или урезанную дробь.
Рассматриваемая функция ОТБР может пригодиться в тех случаях, когда нужно выполнить не округление, а именно усечение дробного числа, оставив «хвост» нужной длины, в том числе полностью обрезав ненужную часть после запятой. Таким образом, можно получать как усечённые дроби, так и целые числа.
Рассмотрим работу данной функции и её использование на примерах.
Синтаксис функции ОТБР
Поскольку мы можем задавать не просто число для усечения, но также и длину «хвоста» дроби, то вполне очевидно, что функция ОТБР должна принимать два аргумента. Первый аргумент обязательный, а вот второй можно не указывать, если требуется получить просто целое число (точнее — целую часть дроби).
Обобщённый синтаксис выглядит следующим образом:
ОТБР(число; [число_разрядов])
Обратите внимание, что все аргументы, которые в описании функций пишутся в квадратных скобках, являются необязательными. В данном случае если Вы не укажете второй аргумент, то он принимается по умолчанию равным нулю, что даст на выходе целое число.
Ещё раз повторим назначение обоих аргументов:
При усечении положительных и отрицательных чисел функция ОТБР ведёт себя одинаково, то есть результат операции от знака числа не зависит.
Примеры использования функции ОТБР
Для примера скопируйте выражение «=ОТБР(10,545; 0)» (без кавычек) в любую ячейку Excel. В результате мы получим число 10, то есть дробная часть будет полностью отброшена в соответствии со значением второго аргумента (ноль знаков после запятой). Если у второго аргумента ноль заменить, например, на 2, то в результате получим число 10,54 (при обычном округлении мы получили бы число 10,55).
Другие примеры есть в прикреплённом файле Excel, который Вы можете скачать после статьи. Также эти примеры разобраны на видео вместе с описанием формулы отбрасывания дробной части.
Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.
- Справка по функции ОТБР в Excel.pdf
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
- Функция ОТБР в Excel (примеры).zip
Математические функции Excel
Здесь рассмотрены наиболее часто используемые математические функции Excel (краткая справка). Дополнительную информацию о функциях можно найти в окне диалога мастера функций, а также в справочной системе Excel. Кроме того, множество математических функций включено в надстройку «Пакет анализа».
Функция СУММ (SUM)
Функция СУММ (SUM) суммирует множество чисел. Эта функция имеет следующий синтаксис:
=СУММ(числа)
Аргумент числа может включать до 30 элементов, каждый из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую или возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения. Аргументы не обязательно должны образовывать непрерывные диапазоны ячеек. Например, чтобы получить сумму чисел в ячейках А2, В10 и в ячейках от С5 до К12, введите каждую ссылку как отдельный аргумент:
=СУММ(А2;В10;С5:К12)
Функции ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ
Функция ОКРУГЛ (ROUND) округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис:
=ОКРУГЛ(число;количество_цифр)
Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количство_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх).
Функции ОКРУГЛВНИЗ (ROUNDDOWN) и ОКРУГЛВВЕРХ (ROUNDUP) имеют такой же синтаксис, как и функция ОКРУГЛ. Они округляют значения вниз (с недостатком) или вверх (с избытком).
Функции ЧЁТН и НЕЧЁТ
Для выполнения операций округления можно использовать функции ЧЁТН (EVEN) и НЕЧЁТ (ODD). Функция ЧЁТН округляет число вверх до ближайшего четного целого числа. Функция НЕЧЁТ округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. Функции имеют следующий синтаксис:
=ЧЁТН(число)
=НЕЧЁТ(число)
Функции ОКРВНИЗ, ОКРВВЕРХ
Функции ОКРВНИЗ (FLOOR) и ОКРВВЕРХ (CEILING) тоже можно использовать для выполнения операций округления. Функция ОКРВНИЗ округляет число вниз до ближайшего кратного для заданного множителя, а функция ОКРВВЕРХ округляет число вверх до ближайшего кратного для заданного множителя. Эти функции имеют следующий синтаксис:
=ОКРВНИЗ(число;множитель)
=ОКРВВЕРХ(число;множитель)
Значения число и множитель должны быть числовыми и иметь один и тот же знак. Если они имеют различные знаки, то будет выдана ошибка.
Функции ЦЕЛОЕ и ОТБР
Функция ЦЕЛОЕ (INT) округляет число вниз до ближайшего целого и имеет следующий синтаксис:
=ЦЕЛОЕ(число)
Эта формула возвратит значение 10, как и следующая:
Функция ОТБР (TRUNC) отбрасывает все цифры справа от десятичной запятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Функция имеет следующий синтаксис:
=ОТБР(число;количество_цифр)
Если второй аргумент опущен, он принимается равным нулю. Следующая формула возвращает значение 25:
Функции СЛЧИС и СЛУЧМЕЖДУ
Функция СЛЧИС (RAND) генерирует случайные числа, равномерно распределенные между 0 и 1, и имеет следующий синтаксис:
=СЛЧИС()
Функция СЛЧИС является одной из функций EXCEL, которые не имеют аргументов. Как и для всех функций, у которых отсутствуют аргументы, после имени функции необходимо вводить круглые скобки.
Значение функции СЛЧИС изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции СЛЧИС изменяется каждый раз при воде данных в этом листе.
Функция СЛУЧМЕЖДУ (RANDBETWEEN), которая доступна, если установлена надстройка «Пакет анализа», предоставляет больше возможностей, чем СЛЧИС. Для функции СЛУЧМЕЖДУ можно задать интервал генерируемых случайных целочисленных значений.
=СЛУЧМЕЖДУ(начало;конец)
Аргумент начало задает наименьшее число, которое может возвратить любое целое число от 111 до 529 (включая и оба эти значения):
Функция ПРОИЗВЕД
Функция ПРОИЗВЕД (PRODUCT) перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис:
=ПРОИЗВЕД(число1;число2. )
Эта функция может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.
Функция ОСТАТ
Функция ОСТАТ (MOD) возвращает остаток от деления и имеет следующий синтаксис:
=ОСТАТ(число;делитель)
Если число меньше чем делитель, то значение функции равно аргументу число. Например, следующая функция возвратит число 25:
Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.
Функция КОРЕНЬ
Функция КОРЕНЬ (SQRT) возвращает положительный квадратный корень из числа и имеет следующий синтаксис:
=КОРЕНЬ(число)
Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 4:
Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.
Функция ЧИСЛОКОМБ
Функция ЧИСЛОКОМБ (COMBIN) определяет количество возможных комбинаций или групп для заданного числа элементов. Эта функция имеет следующий синтаксис:
=ЧИСЛОКОМБ(число;число_выбранных)
Результат будет равен 252. Т.е., может быть образовано 252 команды.
Функция ЕЧИСЛО
Функция ЕЧИСЛО (ISNUMBER) определяет, является ли значение числом, и имеет следующий синтаксис:
=ЕЧИСЛО(значение)
Пусть вы хотите узнать, является ли значение в ячейке А1 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А1 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ:
Функция LOG
Функция LOG возвращает логарифм положительного числа по заданному основанию. Синтаксис:
=LOG(число;основание)
Если аргумент основание не указан, то Excel примет его равным 10.
Функция LN
Функция LN возвращает натуральный логарифм положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис:
=LN(число)
Функция EXP
Функция EXP вычисляет значение константы e, возведенной в заданную степень. Эта функция имеет следующий синтаксис:
EXP(число)
Функция EXP является обратной по отношению к LN. Например, пусть ячейка А2 содержит формулу:
Тогда следующая формула возвращает значение 10:
Функция ПИ
Функция ПИ (PI) возвращает значение константы пи с точностью до 14 десятичных знаков. Синтаксис:
Функция РАДИАНЫ и ГРАДУСЫ
Тригонометрические функции используют углы, выраженные в радианах, а не в градусах. Измерение углов в радианах основывается на константе пи и при этом 180 градусов равны пи радиан. Excel предоставляет две функции, РАДИАНЫ (RADIANS) и ГРАДУСЫ (DEGREES), чтобы облегчить работу с тригонометрическими функциями.
Вы можете преобразовать радианы в градусы, используя функцию ГРАДУСЫ. Синтаксис:
=ГРАДУСЫ(угол)
=РАДИАНЫ(угол)
В то же время следующая формула возвращает значение 3,14159:
Функция SIN
Функция SIN возвращает синус угла и имеет следующий синтаксис:
=SIN(число)
Функция COS
Функция COS возвращает косинус угла и имеет следующий синтаксис:
=COS(число)
Функция TAN
Функция TAN возвращает тангенс угла и имеет следующий синтаксис:
=TAN(число)
Математические функции Excel
В данной статье будет рассмотрена та часть математических функций, которая наиболее часто применяется в решении различных задач. С полным перечнем можно ознакомиться на вкладке «Формулы» => выпадающий список «Математические»:
Какие функции затронет статья:
Функции, связанные с округлением
Функция ОКРУГЛ
Осуществляет стандартное округление, а именно округляет число до ближайшего разряда с указанной точностью.
Синтаксис: =ОКРУГЛ(число; число_разрядов), где
Аргумент может также принимать отрицательные числа:
=ОКРУГЛ(5,45;0) – формула возвращает значение 5.
=ОКРУГЛ(5,45;1) – формула возвращает значение 5,5.
=ОКРУГЛ(5,45;3) – не меняет число, т.к. указанное число разрядов превышает его точность.
=ОКРУГЛ(5,45;-1) – формула возвращает значение 10.
Функция ОТБР
Отбрасывает дробную часть числа. Отличие от предыдущей функции заключается в том, что число по факту не округляется, а только усекается до указанного разряда.
Синтаксис: =ОТБР(число; [число_разрядов]), где
=ОТБР(5,45;0) – формула возвращает значение 5.
=ОТБР(5,85;0) – так же возвращает значение 5.
=ОТБР(5,45;1) – возвращает значение 5,4.
=ОТБР(5,45;3) – формула не меняет число, т.к. указанное количество разрядов превышает его точность.
Функция ОКРУГЛВВЕРХ
Производит округление до ближайшего большего по модулю числа с указанной точностью.
Синтаксис: =ОКРУГЛВВЕРХ(число; число_разрядов), где
Аргумент может также принимать отрицательные числа:
Функция ОКРУГЛВНИЗ
Аналогична предыдущей функции, за исключением того, то округляет число в меньшую сторону по модулю с указанной точностью.
Функция ОКРУГЛТ
Округляет число до ближайшего кратного числу, заданного вторым аргументом.
Синтаксис: =ОКРУГЛТ(число; точность), где
Знаки двух аргументов должны совпадать, иначе функция вернем ошибку.
=ОКРУГЛТ(5,45;0) – формула возвращает значение 0.
=ОКРУГЛТ(5,45; 1,45) – возвращает значение 5,8, т.к. 5,8/1,45=4 и это ближе, чем 7,25/1,45=5.
=ОКРУГЛТ(5,45;3) – формула возвращает значение 6, т.к. 6/3=2, ближе, чем 3/3=1.
Функция ОКРВВЕРХ.МАТ
Появилась в Microsoft Excel 2013. Она округляет число до ближайшего большего кратного числу, заданного вторым аргументом.
Синтаксис: =ОКРВВЕРХ.МАТ(число; [точность]; [режим]), где
Функция ОКРВНИЗ.МАТ
Появилась в Microsoft Excel 2013. Она округляет число до ближайшего меньшего кратного числу, заданного вторым аргументом.
Синтаксис: =ОКРВНИЗ.МАТ(число; [точность]; [режим]), где
Обращаем внимание на то, что третьи аргументы функций ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ, не смотря на то, что очень похожи, все же отличаются, т.к. имеют противоположный эффект. Для избавления от путаницы можно прибегать к следующей ассоциации:
Функция ЦЕЛОЕ
Округляет число до целого в меньшую сторону.
Синтаксис: =ЦЕЛОЕ(число), где число – обязательный аргумент, принимающий числовое значение либо ссылку на ячейку с числовым значением.
Функция ЧЁТН
Округляет число до ближайшего большего по модулю четного числа.
Синтаксис: =ЧЁТН(число), где число – обязательный аргумент. Принимает числовое значение либо ссылку на ячейку, содержащую число.
Функция НЕЧЁТ
Аналогична функции ЧЁТН за исключением того, что числа округляются до нечетных.
Суммирование и условное суммирование
Функция СУММ
Суммирует свои аргументы. Максимальное число аргументов 255.
Если функция ссылается на ячейку, диапазон ячеек или массив, содержащий текстовые либо логические значения, то такие значения игнорируются. Если какой-либо аргумент принимает константу (значение введенное руками), содержащую текстовое значение, то такой аргумент возвращает ошибку, в результате чего вся формула вернет ошибку.
Если же в качестве аргумента функции принимается константа с логическим значением, то ЛОЖЬ приравнивается к нулю, а ИСТИНА к единице.
Синтаксис: =СУММ(число1; [число2]; …), где
Функция СУММПРОИЗВ
Производит суммирование произведений массивов либо диапазонов.
Если аргументы принимают диапазоны либо массивы, содержащие текстовые или логические значения, то такие значения игнорируются.
Если в аргументе явно задать логическое либо текстовое значение или ссылку на одну ячейку, содержащую такое значение, то вся формула вернет ошибку.
Синтаксис: =СУММПРОИЗВ(массив1; [массив2]; …), где
Все аргументы функции должны иметь одинаковую размерность, т.е. если один аргумент ссылается на диапазон с 5 ячейками, то и остальные аргументы должны иметь по 5 элементов. Также должны использоваться диапазоны и массивы одних типов, т.е. горизонтальные и вертикальные массивы и диапазоны либо двумерные и одномерные массивы не могут использоваться одновременно в данной функции, иначе она возвратит ошибку. Чтобы лучше понять этот абзац, ознакомьтесь со статьей «Массивы Excel».
Функция СУММЕСЛИ
Возможно, одна из самых полезных функций, по мнению office-menu. Она производит суммирование элементов, которые соответствуют заданным условиям.
Синтаксис: =СУММЕСЛИ(диапазон_условия; критерий;[диапазон_суммирования]), где
В первом случае, необходимо произвести суммирование по B1:B5, если элемент из A1:A5 больше нуля. Возвращаемое значение 4, так как текстовый элемент A3 игнорируется.
Теперь изменим условие и найдем сумму, если элементы для условия больше или равняются «а». По условиям сортировки все числа являются меньшими любым буквам, поэтому результат должен быть 5. Но так как в условии задано сравнение с текстовой строкой, то все числовые значения отбрасываются. Чтобы они учитывались, их необходимо перевести в текстовый формат. Также можно использовать массивы, для лучшего контроля перевода чисел в текст – <=СУММ(ЕСЛИ(ТЕКСТ(A1:A5;0)
Функция СУММЕСЛИМН
Выполняет те же действия, что и СУММЕСЛИ, но может проверять различные условия по нескольким диапазонам.
Также в данной функции не происходит никаких подмен диапазонов, поэтому все заданные в функции диапазоны должны быть равны по размерности и иметь один тип, т.е. только горизонтальные либо только вертикальные.
Необходимо узнать сумму ячеек, удовлетворяющих условиям:
Таким образом, по первому критерию подходят 3 ячейки, по второму 4, но ячеек, которые подходят под оба условия две – C3 и C4. Поэтому формула вернет значение 2.
Функции, связанные с возведением в степень и извлечением корня
Функция КОРЕНЬ
Извлекает квадратный корень из числа.
Синтаксис: =КОРЕНЬ(число), где аргумент число – является числом, либо ссылкой на ячейку с числовым значением.
=КОРЕНЬ(4) – функция вернет значение 2.
Если возникает необходимость извлечь из числа корень со степенью больше 2, данное число необходимо возвести в степень 1/(показатель корня). Например, для извлечения кубического корня из числа 27 необходимо применить следующую формулу: =27^(1/3) – результат 3.
Функция СУММКВРАЗН
Производит суммирование возведенных в квадрат разностей между элементами двух диапазонов либо массивов.
Синтаксис: =СУММКВРАЗН(диапазон1; диапазон2), где первый и второй аргументы являются обязательными и содержать ссылки на диапазоны либо массивы с числовыми значениями. Текстовые и логические значения игнорируются.
Вертикальные и горизонтальные диапазоны и массивы в данной функции не различаются, но должны иметь одинаковую размерность.
=СУММКВРАЗН(<1;2>;<0;4>) – функция вернет значение 5. Альтернативное решение =(1-0)^2+(2-4)^2.
Функция СУММКВ
Воспроизводит числа, заданные ее аргументами, в квадрат, после чего их суммирует.
Синтаксис: =СУММКВ(число1; [число2]), где число1 … число255, число, либо ссылки на ячейки и диапазоны, содержащие числовые значения. Максимальное число аргументов 255, минимальное 1. Все текстовые и логические значения игнорируются, за исключением случаев, когда они заданы явно. В последнем случае текстовые значения возвращают ошибку, логические 1 для ИСТИНА, 0 для ЛОЖЬ.
=СУММКВ(2;2) – функция вернет значение 8.
=СУММКВ(2;ИСТИНА) – возвращает значение 5, так как ИСТИНА приравнивается к единице.
В данном примере текстовое значение игнорируется, так как оно задано через ссылку на диапазон.
Функция СУММСУММКВ
Возводит все элементы указанных диапазонов либо массивов в квадрат, суммирует их пары, затем выводит общую сумму.
Синтаксис: =СУММСУММКВ(диапазон1; диапазон2), где аргументы являются числами, либо ссылками на диапазоны или массивы.
Функция при обычных условиях возвращает точно такой же результат, как и функция СУММКВ. Но если в качестве элемента одного из аргументов будет указано текстовое или логическое значение, то проигнорирована будет вся пара элементов, а не только сам элемент.
Рассмотрим применение функции СУММСУММКВ и СУММКВ к одним и тем же данным.
В первом случае функции возвращают один и тот же результат:
Во втором случае функции вернут разные результаты из-за несущественных различий в алгоритме вычисления (части выделенные красным игнорируются, т.к. возвращают ошибку):
Функция СУММРАЗНКВ
Аналогична во всем функции СУММСУММКВ за исключение того, что для пар соответствующих элементов находится не сумма, а их разница.
Синтаксис: =СУММРАЗНКВ(диапазон1; диапазон2), где аргументы являются числами, либо ссылками на диапазоны или массивы.
Функции случайных чисел и возможных комбинаций
Функция СЛЧИС
Возвращает случайно сгенерированное число в пределах: >=0 и
Функция СЛУЧМЕЖДУ
Возвращает случайно сгенерированное целое число в пределах указанных границ. При использовании нескольких таких функций, возвращаемые значения могут повторяться.
Синтаксис: =СЛУЧМЕЖДУ(нижняя_граница; верхняя_граница), где аргументы являются числами, либо ссылками на ячейки, содержащие числа. Все аргументы обязательны, и представляют собой минимальное и максимальное возможные значения соответственно. Аргументы могут быть равны друг другу, но минимальная граница не может быть больше максимальной.
Значение возвращаемое функцией меняется каждый раз, когда происходит изменение книги.
Если вдруг возникнет необходимость возвращать дробные числа, то это можно сделать с использованием функции СЛЧИС по следующей формуле:
В следующем примере возвращаются 5000 произвольных значений, лежащих в диапазоне от 10 до 100. В дополнительно приведенной таблице можно посмотреть минимальные и максимальные возвращенные значения. Также для части формулы используется округление. Оно использовано для того, чтобы увеличить вероятность возврата крайних значений диапазона.
Функция ЧИСЛКОМБ
Возвращает возможное число уникальных комбинаций для определенного количества элементов из общего набора элементов.
Синтаксис: =ЧИСЛКОМБ(размер_набора; колво_элементов), где
Все аргументы должны содержать целые положительные числа.
Имеется набор из 4 элементов – ABCD. Из него необходимо составить уникальные комбинации по 2 элемента, при условии что в комбинации элементы не повторяются и их расположение не имеет значения, т.е. пары AB и BA являются равнозначными.
=ЧИСЛКОМБ(4;2) – возвращаемый результат 6:
Функция ФАКТР
Возвращает факториал числа, что соответствует числу возможных вариаций упорядочивания элементов группы.
Синтаксис: =ФАКТР(число), где число – обязательный аргумент, являющийся числом либо ссылкой на ячейку, содержащую числовое значение.
Имеется набор из 3 элементов – ABC, который можно упорядочить 6 разными способами:
Используем функцию, чтобы подтвердить данное количество: =ФАКТР(3) – формула возвращает значение 6.
Функции, связанные с делением
Функция ЧАСТНОЕ
Выполняет самое простое деление.
Синтаксис: =ЧАСТНОЕ(делимое; делитель), где все аргументы являются обязательными и должны представляться числами.
=ЧАСТНОЕ(8;4) – возвращаемое значение 2.
Можно воспользоваться альтернативой функции: =8/2.
Функция ОСТАТ
Возвращает остаток от деления двух чисел.
Синтаксис: =ОСТАТ(делимое; делитель), где все аргументы являются обязательными и должны иметь числовое значение.
Знак остатка всегда совпадает со знаком делителя.
Сама функция из-за алгоритма своего вычисления выдает результат обработки чисел с разными знаками, который возможно Вы от нее ожидать не будете. Подробнее:
Функция НОД
Вычисляет наибольший общий делитель для всех аргументов, на который они делятся без остатка. Наибольший делитель всегда целое число.
=НОД(число1; [число2]; …). Максимальное число аргументов 255, минимальное 1. Аргументы являются числами, ссылками на ячейки или диапазонами ячеек, которые содержат числа. Значения аргументов должны быть всегда положительными числами.
=НОД(8;4) – результат выполнения 4.
=НОД(6;4) – результат выполнения 2.
Функция НОК
Вычисляет наименьшее общее кратное для всех аргументов.
Синтаксис и описание аргументов аналогичны функции НОД.
=НОК(8;4) – результат выполнения 8.
=НОК(6;4) – результат выполнения 12.
Преобразование чисел
Функция ABS
Возвращает модуль числа.
=ABS(число), где число обязательный аргумент, являющийся числом либо ссылкой на ячейку, содержащую число.
=ABS(-4) – результат 4.
Функция РИМСКОЕ
Преобразует число в строку, представляющую римское число.
Синтаксис: =РИМСКОЕ(число; [формат]), где
=РИМСКОЕ(999;0) – результат «CMXCIX»;
=РИМСКОЕ(999;1) – результат «LMVLIV»;
=РИМСКОЕ(999;2) – возвращает «XMIX»;
=РИМСКОЕ(999;3) – результат «VMIV»;
=РИМСКОЕ(999;4) – результат «IM»;
=РИМСКОЕ(999;ИСТИНА) – результат «CMXCIX»;
=РИМСКОЕ(999;ЛОЖЬ) – результат «IM».
Иные функции
Функция ЗНАК
Проверяет знак числа и возвращает значение:
Синтаксис: =ЗНАК(число), где число – обязательный аргумент, являющийся числом либо ссылкой на ячейку, содержащую числовое значение.
Функция ПИ
Возвращает значение числа пи, округленное до 14 знаков после запятой – 3,14159265358979.
Синтаксис: =ПИ().
Функция ПРОИЗВЕД
Вычисляет произведение всех своих аргументов. Максимальное число аргументов 255.
Если функция ссылается на ячейку, диапазон ячеек или массив, содержащий текстовые либо логические значения, то такие значения игнорируются. Если какой-либо аргумент явно принимает текстовое значение, то он вызывает ошибку. Если же аргумент явно принимает логическое значением, то ЛОЖЬ приравнивается к нулю, а ИСТИНА к единице.
Синтаксис: =ПРОИЗВЕД(число1; [число2]; …), где
В данном примере видно, что текстовые и логические значения никак не влияют на конечный результат формулы.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
Данная функция предназначена для работы со структурой промежуточных итогов. Ознакомиться с применением такой структуры можно в соответствующей статье категории Уверенное использование Excel на нашем сайте.
Во время задания подобной структуры, рассматриваемая функция создается автоматически. Смысл ее использования таков, что она игнорирует значения в ячейках, высчитанные с использованием промежуточных итогов. Рассмотрим синтаксис и пример использования.
Соотношение номера функции с конкретной функцией:
Если к описанным номерам прибавить 100 (т.е. вместо 1 указать 101 и т.д.), то они все равно будут указывать на те же функции. Но отличие заключается в том, что во втором варианте, при скрытие строк, те ячейки, указанные в ссылках, которые будут находится в скрытых строках, участвовать в подсчете не будут.
Используем структуру промежуточных итогов, которую мы применяли в одноименной статье. Добавим к ней средний результат по всем агентам за каждый квартал. Для того, чтобы корректно применить функцию СРЗНАЧ для имеющихся значений, нам пришлось бы указать 3 отдельных диапазона, чтобы не принимать в расчет промежуточные значение. Это не составить проблем, если данных не много, но если таблица большая, то выделять каждый диапазон будет проблематично. В данной ситуации лучше применить функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, т.к она проигнорирует все ненужные ячейки. Обратите внимание на изображение. Разница очевидна, что второй пример использовать гораздо удобнее при одинаковых результатах функций. Также можно не беспокоиться о добавлении в будущем других строк с итогами.
Теперь продемонстрируем различия в использовании режимов функций. В качестве примера используем только что созданную формулу и изменим значение первого аргумента на 101 вместо 1. Возвращаемый результат не измениться до тех пор, пока мы не скроем строки структуры.
Информация продаж по Агенту1 во втором случае не учитывается.