Статистические функции Excel

Статистические функции в Microsoft Excel представляют собой мощный инструментарий для анализа данных. Они помогают вычислять статистические величины, такие как средние значения, медиану, стандартное отклонение и множество других.

Эти функции разнообразны и могут использоваться в различных областях, от финансового анализа до научных исследований. Одни подходят для базовых статистических расчетов, другие предназначены для более сложного статистического анализа.

ТОП популярных статистических функций

Ниже 20 самых популярных статистических функций, согласно статистике поисковых запросов пользователей рунета:

Функция СЧЁТ (COUNT): Считает количество ячеек с числовыми значениями в диапазоне.
Функция СЧЁТЕСЛИ (COUNTIF): Считает количество ячеек, соответствующих заданному условию.
Функция СРЗНАЧ (AVERAGE): Позволяет вычислить среднее арифметическое заданного набора чисел. Одна из самых часто используемых функций для анализа данных.
Функции МАКС и МИН (MAX, MIN): Определяют максимальное и минимальное значение в наборе данных соответственно.
Функция СЧЁТЕСЛИМН (COUNTIFS): Считает количество ячеек, соответствующих нескольким условиям.
Функция МЕДИАНА (MEDIAN): Определяет медиану заданного ряда чисел, что полезно для понимания “центра” набора данных.
Функция ПРЕДСКАЗ (FORECAST): Предсказывает будущие значения на основе линейного тренда.
Функция ЛИНЕЙН (CORREL): Определяет степень линейной корреляции между двумя наборами данных.
Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ (LARGE, SMALL): Определяют k-ое по величине и k-ое по малости значения в наборе данных.
Функция СРЗНАЧЕСЛИ (AVERAGEIF): Вычисляет среднее арифметическое для ячеек, соответствующих заданному условию.
Функция СЧЁТЗ (COUNTA): Считает количество непустых ячеек в диапазоне, независимо от их содержимого.
Функция МОДА (MODE): Находит значение, которое встречается чаще всего в наборе данных, полезно для анализа трендов.
Функция СТАНДОТКЛОН (STDEV): Рассчитывает стандартное отклонение, что важно для понимания вариативности данных.
Функция ЧАСТОТА (FREQUENCY): Вычисляет распределение частот значений в наборе данных.
Функция ДИСП (VAR): Помогает вычислить дисперсию числового набора данных, что критично для статистического анализа.
Функция ФИ (PHI): Возвращает значение функции плотности для стандартного нормального распределения.
Функция ЭКСЦЕСС (KURT): Возвращает эксцесс (меру остроты распределения) выбранных аргументов.
Функция ОТРЕЗОК (INTERCEPT): Возвращает точку пересечения прямой линии с осью Y.
Функция МАКСЕСЛИ (MAXIFS): Определяет максимальное значение среди ячеек, соответствующих одному или нескольким условиям.
Функция КОРРЕЛ (CORREL): Рассчитывает коэффициент корреляции между двумя наборами данных.

Полный список статистических функций

Полный список статистических функций по алфавиту с кратким описанием вы найдёте ниже. Все они доступны, если у вас последняя версия Microsoft Office Excel.

Столбец “Версия” обозначает релиз MS Excel, до которого функция ещё не поддерживалась и в рамках которого начала поддерживаться. Если поле пустое – функция доступна во всех версиях.

Функция	Версия	Что делает
`Функция СРОТКЛ`		Возвращает среднее арифметическое отклонений указанных значений от их среднего арифметического.
`Функция СРЗНАЧ`		Возвращает среднее арифметическое аргументов.
`Функция СРЗНАЧА`		Возвращает среднее арифметическое аргументов, включая числа, текст и логические значения.
`Функция СРЗНАЧЕСЛИ`		Возвращает среднее арифметическое значений, которые удовлетворяют условию.
`Функция СРЗНАЧЕСЛИМН`		Возвращает среднее арифметическое значений, которые удовлетворяют нескольким условиям.
`Функция БЕТА.РАСП`	2010	Возвращает интегральную функцию бета-распределения.
`Функция БЕТА.ОБР`	2010	Возвращает обратную интегральную функцию указанного бета-распределения.
`Функция БИНОМ.РАСП`	2010	Возвращает отдельное значение вероятности биномиального распределения.
`Функция БИНОМ.РАСП.ДИАП`	2013	Возвращает вероятность пробного результата с помощью биномиального распределения.
`Функция БИНОМ.ОБР`	2010	Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.
`Функция ХИ2.РАСП`	2010	Возвращает интегральную функцию плотности бета-вероятности.
`Функция ХИ2.РАСП.ПХ`	2010	Возвращает одностороннюю вероятность распределения хи-квадрат.
`Функция ХИ2.ОБР`	2010	Возвращает интегральную функцию плотности бета-вероятности.
`Функция ХИ2.ОБР.ПХ`	2010	Возвращает обратное значение односторонней вероятности распределения хи-квадрат.
`Функция ХИ2.ТЕСТ`	2010	Возвращает тест на независимость.
`Функция ДОВЕРИТ.НОРМ`	2010	Возвращает доверительный интервал для среднего значения по генеральной совокупности.
`Функция ДОВЕРИТ.СТЬЮДЕНТ`	2010	Возвращает доверительный интервал для среднего генеральной совокупности, используя t-распределение Стьюдента.
`Функция КОРРЕЛ`		Возвращает коэффициент корреляции между двумя множествами данных.
`Функция СЧЁТ`		Подсчитывает количество чисел в списке аргументов.
`Функция СЧЁТЗ`		Подсчитывает количество значений в списке аргументов.
`Функция СЧИТАТЬПУСТОТЫ`		Подсчитывает количество пустых ячеек в диапазоне.
`Функция СЧЁТЕСЛИ`		Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.
`Функция СЧЁТЕСЛИМН`		Подсчитывает количество ячеек внутри диапазона, удовлетворяющих нескольким условиям.
`Функция КОВАРИАЦИЯ.Г`	2010	Возвращает ковариацию, среднее произведений парных отклонений.
`Функция КОВАРИАЦИЯ.В`	2010	Возвращает ковариацию выборки — среднее попарных произведений отклонений для всех точек данных в двух наборах данных.
`Функция КВАДРОТКЛ`		Возвращает сумму квадратов отклонений.
`Функция ЭКСП.РАСП`	2010	Возвращает экспоненциальное распределение.
`Функция F.РАСП`	2010	Возвращает F-распределение вероятности.
`Функция F.РАСП.ПХ`	2010	Возвращает F-распределение вероятности.
`Функция F.ОБР`	2010	Возвращает обратное значение для F-распределения вероятности.
`Функция F.ОБР.ПХ`	2010	Возвращает обратное значение для F-распределения вероятности.
`Функция F.ТЕСТ`	2010	Возвращает результат F-теста.
`Функция ФИШЕР`		Возвращает преобразование Фишера.
`Функция ФИШЕРОБР`		Возвращает обратное преобразование Фишера.
`Функция ПРЕДСКАЗ`		Возвращает значение линейного тренда. В Excel 2016 эта функция заменена на ПРЕДСКАЗ.ЛИНЕЙН из нового набора функций прогнозирования. Однако она по-прежнему доступна для совместимости с предыдущими версиями.
`Функция ПРЕДСКАЗ.ETS`	2016	Возвращает будущее значение на основе существующих (ретроспективных) данных с использованием версии AAA алгоритма экспоненциального сглаживания (ETS).
`Функция ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ`	2016	Возвращает доверительный интервал для прогнозной величины на указанную дату.
`Функция ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ`	2016	Возвращает длину повторяющегося фрагмента, обнаруженного программой Excel в заданном временном ряду.
`Функция ПРЕДСКАЗ.ETS.СТАТ`	2016	Возвращает статистическое значение, являющееся результатом прогнозирования временного ряда.
`Функция ПРЕДСКАЗ.ЛИНЕЙН`	2016	Возвращает будущее значение на основе существующих значений.
`Функция ЧАСТОТА`		Возвращает распределение частот в виде вертикального массива.
`Функция ГАММА`	2013	Возвращает значение функции гамма
`Функция ГАММА.РАСП`	2010	Возвращает гамма-распределение.
`Функция ГАММА.ОБР`	2010	Возвращает обратное значение интегрального гамма-распределения.
`Функция ГАММАНЛОГ`		Возвращает натуральный логарифм гамма-функции, Γ(x).
`Функция ГАММАНЛОГ.ТОЧН`	2010	Возвращает натуральный логарифм гамма-функции, Γ(x).
`Функция ГАУСС`	2013	Возвращает значение на 0,5 меньше стандартного нормального распределения.
`Функция СРГЕОМ`		Возвращает среднее геометрическое.
`Функция РОСТ`		Возвращает значения в соответствии с экспоненциальным трендом.
`Функция СРГАРМ`		Возвращает среднее гармоническое.
`Функция ГИПЕРГЕОМ.РАСП`		Возвращает гипергеометрическое распределение.
`Функция ОТРЕЗОК`		Возвращает отрезок, отсекаемый на оси линией линейной регрессии.
`Функция ЭКСЦЕСС`		Возвращает эксцесс множества данных.
`Функция НАИБОЛЬШИЙ`		Возвращает k-ое наибольшее значение в множестве данных.
`Функция ЛИНЕЙН`		Возвращает параметры линейного тренда.
`Функция ЛГРФПРИБЛ`		Возвращает параметры экспоненциального тренда.
`Функция ЛОГНОРМ.РАСП`	2010	Возвращает интегральное логарифмическое нормальное распределение.
`Функция ЛОГНОРМ.ОБР`	2010	Возвращает обратное значение интегрального логарифмического нормального распределения.
`Функция МАКС`		Возвращает наибольшее значение в списке аргументов.
`Функция МАКСА`		Возвращает наибольшее значение в списке аргументов, включая числа, текст и логические значения.
`Функция МАКСЕСЛИ`	2016	Возвращает максимальное значение из заданных определенными условиями или критериями ячеек.
`Функция МЕДИАНА`		Возвращает медиану заданных чисел.
`Функция МИН`		Возвращает наименьшее значение в списке аргументов.
`Функция МИНЕСЛИ`	2016	Возвращает минимальное значение из заданных определенными условиями или критериями ячеек.
`Функция МИНА`		Возвращает наименьшее значение в списке аргументов, включая числа, текст и логические значения.
`Функция МОДА.НСК`	2010	Возвращает вертикальный массив наиболее часто встречающихся или повторяющихся значений в массиве или диапазоне данных.
`Функция МОДА.ОДН`	2010	Возвращает значение моды набора данных.
`Функция ОТРБИНОМ.РАСП`	2010	Возвращает отрицательное биномиальное распределение.
`Функция НОРМ.РАСП`	2010	Возвращает нормальное интегральное распределение.
`Функция НОРМ.ОБР`	2010	Возвращает обратное значение нормального интегрального распределения.
`Функция НОРМ.СТ.РАСП`	2010	Возвращает стандартное нормальное интегральное распределение.
`Функция НОРМ.СТ.ОБР`	2010	Возвращает обратное значение стандартного нормального интегрального распределения.
`Функция ПИРСОН`		Возвращает коэффициент корреляции Пирсона.
`Функция ПРОЦЕНТИЛЬ.ИСКЛ`	2010	Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (не включая эти числа).
`Функция ПРОЦЕНТИЛЬ.ВКЛ`	2010	Возвращает k-ю процентиль для значений диапазона.
`Функция ПРОЦЕНТРАНГ.ИСКЛ`	2010	Возвращает ранг значения в наборе данных как процентную долю набора (от 0 до 1, исключая границы).
`Функция ПРОЦЕНТРАНГ.ВКЛ`	2010	Возвращает процентную норму значения в наборе данных.
`Функция ПЕРЕСТ`		Возвращает количество перестановок для заданного числа объектов.
`Функция ПЕРЕСТА`	2013	Возвращает количество перестановок для заданного числа объектов (с повторами), которые можно выбрать из общего числа объектов.
`Функция ФИ`	2013	Возвращает значение функции плотности для стандартного нормального распределения.
`Функция ПУАССОН.РАСП`	2010	Возвращает распределение Пуассона.
`Функция ВЕРОЯТНОСТЬ`		Возвращает вероятность того, что значение из диапазона находится внутри заданных пределов.
`Функция КВАРТИЛЬ.ИСКЛ`	2010	Возвращает квартиль набора данных на основе значений процентили из диапазона от 0 до 1, исключая границы.
`Функция КВАРТИЛЬ.ВКЛ`	2010	Возвращает квартиль набора данных.
`Функция РАНГ.СР`	2010	Возвращает ранг числа в списке чисел.
`Функция РАНГ.РВ`	2010	Возвращает ранг числа в списке чисел.
`Функция КВПИРСОН`		Возвращает квадрат коэффициента корреляции Пирсона.
`Функция СКОС`		Возвращает асимметрию распределения.
`Функция СКОС.Г`	2013	Возвращает асимметрию распределения на основе заполнения: характеристика степени асимметрии распределения относительно его среднего.
`Функция НАКЛОН`		Возвращает наклон линии линейной регрессии.
`Функция НАИМЕНЬШИЙ`		Возвращает k-ое наименьшее значение в множестве данных.
`Функция НОРМАЛИЗАЦИЯ`		Возвращает нормализованное значение.
`Функция СТАНДОТКЛОН.Г`	2010	Вычисляет стандартное отклонение по генеральной совокупности.
`Функция СТАНДОТКЛОН.В`	2010	Оценивает стандартное отклонение по выборке.
`Функция СТАНДОТКЛОНА`		Оценивает стандартное отклонение по выборке, включая числа, текст и логические значения.
`Функция СТАНДОТКЛОНПА`		Вычисляет стандартное отклонение по генеральной совокупности, включая числа, текст и логические значения.
`Функция СТОШYX`		Возвращает стандартную ошибку предсказанных значений y для каждого значения x в регрессии.
`Функция СТЬЮДРАСП`	2010	Возвращает процентные точки (вероятность) для t-распределения Стьюдента.
`Функция СТЬЮДЕНТ.РАСП.2Х`	2010	Возвращает процентные точки (вероятность) для t-распределения Стьюдента.
`Функция СТЬЮДЕНТ.РАСП.ПХ`	2010	Возвращает t-распределение Стьюдента.
`Функция СТЬЮДЕНТ.ОБР`	2010	Возвращает значение t для t-распределения Стьюдента как функцию вероятности и степеней свободы.
`Функция СТЬЮДЕНТ.ОБР.2Х`	2010	Возвращает обратное t-распределение Стьюдента.
`Функция СТЬЮДЕНТ.ТЕСТ`	2010	Возвращает вероятность, соответствующую проверке по критерию Стьюдента.
`Функция ТЕНДЕНЦИЯ`		Возвращает значения в соответствии с линейным трендом.
`Функция УРЕЗСРЕДНЕЕ`		Возвращает среднее внутренности множества данных.
`Функция ДИСП.Г`	2010	Вычисляет дисперсию по генеральной совокупности.
`Функция ДИСП.В`	2010	Оценивает дисперсию по выборке.
`Функция ДИСПА`		Оценивает дисперсию по выборке, включая числа, текст и логические значения.
`Функция ДИСПРА`		Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения.
`Функция ВЕЙБУЛЛ.РАСП`	2010	Возвращает распределение Вейбулла.
`Функция Z.ТЕСТ`	2010	Возвращает одностороннее значение вероятности z-теста.

Статистические функции Excel широко применяются в самых разных областях: от бизнес-анализа и учета до инженерии и научных исследований. Они позволяют быстро анализировать большие объемы данных и принимать обоснованные решения на основе такого анализа.

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

ПИ

Это статья о функции Excel. Если вам нужен значок, см. ЮНИСИМВ.

Синтаксис
Примеры формул с функцией ПИ

Функция ПИ в Excel - формула и её значение

Раздел функций	Математические
Название и описание на английском	PI

Эта функция возвращает число Пи с точностью в 14 знаков после запятой. Функция не требует ввода аргументов, она отдает значение сама по себе. Похожие функции без аргументов – СЛЧИС, СЕГОДНЯ, ТДАТА.

Число Пи иррациональное, и нет простых способов его рассчитать, потому и существует подобная функция.

Синтаксис

=ПИ()

Примеры формул с функцией ПИ

Число используется в расчете площади и объема фигур, основанных на окружностях и сферах.

Площадь круга – формула Excel

Площадь круга A через радиус r вычисляется по формуле:

A = πr^2

Допустим, радиус находится в ячейке A2, тогда формула площади будет выглядеть так:

=ПИ()*A2^2

Длина окружности – формула Excel

C = 2πr

Если радиус окружности находится в ячейке A2, то формула будет:

=2*ПИ()*A2

Площадь кольца – формула Excel

A = π(R^2 - r^2)

(где R — внешний радиус а r — внутренний). Разместим радиусы в ячейках A2 и B2, формула:

=2*ПИ()*(A2^2-B2^2)

Объём сферы (шара) – формула Excel

Для расчёта объёма сферы нужен только её радиус, а геометрическая формула выглядит так:

V = (4/3)πr^3

Введите нужный радиус в ячейку A2 и используйте любую другую ячейку для расчёта объема сферы (шара) следующей Excel-формулой:

=4/3*ПИ()*A2^3

Площадь поверхности сферы (шара) – формула Excel

Математическая формула расчета площади поверхности сферы выглядит так:

A = 4πr^2

А Excel-формула будет такой (r в ячейке A2):

=4*ПИ()*A2^2

Объём цилиндра – формула Excel

V = πr^2h

Разместите радиус в ячейке A2 и высоту в ячейке B2 и используйте формулу ниже:

=ПИ()*A2^2*B2

Площадь поверхности цилиндра – формула Excel

A = 2πrh + 2πr^2

Здесь r — радиус, а h — высота цилиндра. Соответствующие им ячейки: A2 и B2.

=2*ПИ()*A2*B2+2*ПИ()*A2^2

Объём конуса – формула Excel

V = (1/3)πr^2h

где r — радиус основания, а h — высота конуса.

В формуле ниже это A2 и B2 соответственно:

=1/3*ПИ()*A2^2*B2

Площадь поверхности конуса – формула Excel

A = πr(l + r)

где r — радиус основания, а l — длина образующей конуса. Допустим, радиус находится в ячейке A2, а длина образующей конуса — в ячейке B2. Тогда формула будет выглядеть так:

=ПИ()*A2*(A2+B2)

Площадь круга, вписанного в треугольник, по длинам трёх его сторон

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

Непростая, но решаемая поэтапно геометрическая задача. Ниже её алгоритм:

Сначала вычисляется полупериметр треугольника (сумма длин сторон, поделённая напополам):

s = (a + b + c)/2

Затем используется формула Герона, чтобы найти площадь А треугольника через полупериметр и длины сторон:

A = √(s(s-a)(s-b)(s-c))

Радиус r вписанной окружности равен отношению площади треугольника к его полупериметру:

r = A/s

Наконец, зная радиус круга, используем формулу площади круга:

А = πr ^ 2

Если собрать все это вместе, формула площади круга, вписанного в треугольник со сторонами, имеющими длины a, b и c, выглядит следующим образом:

А = π((a + b + c)/2((a + b + c)/2-a)((a + b + c)/2-b)((a + b + c)/2-c))

Представим, что длины сторон треугольника a, b и c находятся в ячейках A2, B2 и C2 соответственно:

Круг, вписанный в треугольник с известными длинами сторон — Как найти площадь круга, вписанного в треугольник, зная только длины его сторон

Тогда итоговая формула будет:

=ПИ()*(A2+B2+C2)/2*((A2+B2+C2)/2-A2)*((A2+B2+C2)/2-B2)*((A2+B2+C2)/2-C2)/((A2+B2+C2)/2)^2

Понравилась статья? Поддержите её автора, купите надстройку для Excel !SEMTools, она поможет вам сэкономить уйму времени при работе в Excel

Скачать !SEMTools

ОКРУГЛВНИЗ

Синтаксис и аргументы функции
Пример формулы с функцией ОКРУГЛВНИЗ
Процедура округления вниз в Excel

При округлении в Excel зачастую нужно округлить числа вниз – до целого или нескольких знаков относительно десятичной запятой.

функция ОКРУГЛВНИЗ, примеры использования

Раздел функций	Математические
Название и описание на английском	ROUNDDOWN
Похожие функции	ОКРУГЛ, ОКРУГЛВВЕРХ
Похожие процедуры в !SEMTools	Округлить числа в столбце

Функция ОКРУГЛВНИЗ используется для того, чтобы округлять числа всегда “вниз”. Её отличие от ОКРУГЛ в том, что вне зависимости от знака после округляемого разряда, округляемая часть числа всегда остается прежней.

Синтаксис и аргументы функции

=ОКРУГЛВНИЗ(округляемое вниз число; до скольких разрядов округлить)

=ОКРУГЛВНИЗ(округляемое вниз число;до скольких разрядов округлить)

Когда второй аргумент равен нулю, производится округление до целых. Если аргумент сделать отрицательным (-1, -2, -3…), то происходит округление числа до десятков, сотен, тысяч и т.д.

Пример формулы с функцией ОКРУГЛВНИЗ

Формула ниже округляет вниз значение ячейки A1 до двух знаков после десятичной запятой.

=ОКРУГЛВНИЗ(A1;2)

Процедура округления вниз в Excel

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

В Excel нет такой возможности, поэтому я решил добавить её в свою надстройку для Excel. Можно выбирать либо вбивать самому число знаков перед и после запятой. Смотрите, как это работает:

Хотите так же быстро округлять числа в Excel? !SEMTools поможет сэкономить время и решить сотни похожих задач в пару кликов!

Скачать бесплатно

ОКРУГЛВВЕРХ

Синтаксис и аргументы функции
Примеры
Процедура округления вверх в Excel

Раздел функций	Математические
Название и описание на английском	ROUNDUP
Похожие функции	ОКРУГЛ, ОКРУГЛВНИЗ
Похожие процедуры !SEMTools	Округлить числа в столбце

Округление в Excel – одна из важнейших процедур при работе с числовыми данными.

Иногда нужно округлять числа в одну сторону, например, вверх, и ровно для этого существует функция ОКРУГЛВВЕРХ. Её отличие от функции ОКРУГЛ в том, что вне зависимости от дробной части, округляемая часть числа увеличивается на единицу.

Целые числа при округлении до целых сохраняют то же значение.

Синтаксис и аргументы функции

=ОКРУГЛВВЕРХ(округляемое вверх число;до скольких разрядов округлить)

Второй аргумент может быть равен нулю, в таких случаях число округляется до целых. Если же он равен -1, -2, -3 и далее, то округление происходит, до десятков, сотен, тысяч и т.д.

Примеры

Формула ниже округляет число в ячейке A1 вверх до целых:

=ОКРУГЛВВЕРХ(A1;0)

Ещё несколько примеров:

функция ОКРУГЛВВЕРХ в Excel, примеры применения — функция ОКРУГЛВВЕРХ

Процедура округления вверх в Excel

В Excel нет такой возможности, поэтому я решил добавить её в свою надстройку для Excel. Она позволяет округлять до целых, одной и двух знаков после запятой в один клик. А также дает возможность выбирать число разрядов самостоятельно. Смотрите, как это работает:

Скачать бесплатно

ОКРУГЛ

Синтаксис и аргументы функции ОКРУГЛ
ОКРУГЛ – примеры формул
Процедура округления в Excel

Раздел функций	Математические
Название и описание на английском	ROUND
Похожие функции	ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ
Похожие процедуры в !SEMTools	Округлить числа в столбце

ОКРУГЛ – это функция Excel, которая используется в формулах для того, чтобы округлять числа по стандартным математическим правилам.

Если знак после округляемого разряда равен 5,6,7,8,9, то происходит округление вверх (к разряду прибавляется единица), а если 0,1,2,3 или 4 – идет округление вниз (число остается прежним). Вся часть после указанного разряда становится нулями.

Синтаксис и аргументы функции ОКРУГЛ

=ОКРУГЛ(округляемое число;до скольких разрядов округлить)

Если второй аргумент равен нулю, число округляется до целых. Если -1, -2, -3 и далее, то, соответственно, до десятков, сотен, тысяч и т.д.

ОКРУГЛ – примеры формул

Формула ниже округляет всем известное иррациональное число π до трёх знаков после запятой, для этого в ней также используется функция ПИ.

=ОКРУГЛ(ПИ();3)

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

функция округл в Excel, примеры использования — Использование функции ОКРУГЛ с числом π (пи)

Процедура округления в Excel

В Excel нет такой возможности, поэтому я решил добавить её в свою надстройку для Excel. Смотрите, как это работает:

Скачать бесплатно

ВПР

Как работает ВПР. Для чайников
Как сделать ВПР — понятная пошаговая инструкция
ВПР по вхождению подстроки
Не работает ВПР — ошибки и причины
- ВПР выдает ошибку #Н/Д
- ВПР не подтягивает значение, хотя искомое в таблице есть

Функцией ВПР (по-английски VLOOKUP) пользоваться довольно просто. Есть десятки примеров и даже пошаговых инструкций. Однако, новичкам в Excel непросто даётся понимание того, как работают формулы с функцией ВПР.

В этой статье я попробую максимально понятно описать, как работает ВПР простыми словами и научить вас её использовать.

Как работает ВПР. Для чайников

Расшифровка названия функции уже дает половину ответа на вопрос “Как работает ВПР?”. Ведь она расшифровывается как Вертикальный ПРосмотр. Потому что находит искомое, просматривая данные в таблице вертикально.

Синтаксис функции ВПР

Синтаксис функции ВПР выглядит следующим образом:

=ВПР(что_найти;таблица;номер_столбца;интервальный_просмотр)

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

Синтаксис ВПР подробно — Расшифровка синтаксиса ВПР

Когда я думаю о функции, я вспоминаю знакомую всем телепередачу “Что? Где? Когда?”.

Первые два аргумента — как раз “Что?” и “Где?”, и идут именно в этом порядке. Первый аргумент – искомое значение, иначе говоря, что мы ищем. Второй аргумент — диапазон, таблица, простыми словами — где мы ищем. Третий аргумент числовой, и обозначает, когда мы должны остановиться, считая столбцы в таблице слева направо от найденного в первом столбце таблицы значения.

А четвертый аргумент (“интервальный просмотр”) вроде бы даже не обязательный, но на поверку очень важен. Ведь от его значения зависит, как будет работать функция. Жаль, вопроса “Как?” в телепередаче не было. :)

Интервальный просмотр — 0 или 1, ЛОЖЬ или ИСТИНА

Скажу заранее. Если нужно просто найти искомое значение и вернуть то, что напротив — используйте в этом параметре 0 (или ЛОЖЬ, но 0 напечатать проще) и не читайте дальше.

Если же хотите узнать главный секрет функции ВПР, то дальше информация для вас.

Тот, кто перевел последний параметр range lookup с английского как “интервальный просмотр”, испортил жизнь многим новичкам в Excel.

Потому что там нет ничего интервального, все вполне себе сплошное. А корректно перевести параметр следовало бы “способ просмотра диапазона“.

Также справочный текст говорит следующее:

Функция ВПР, аргумент "интервальный просмотр" — “Приблизительное” и “точное” совпадение в описании четвертого параметра ВПР

ЛОЖЬ или 0 обозначает точное совпадение;
ИСТИНА, 1 или эквивалентный им пропуск параметра (т.к. это значение по умолчанию) обозначает приблизительное совпадение

И вот с этим приблизительным совпадением сразу возникает ряд вопросов. Что значит приблизительное? Насколько? Каким алгоритмом считается “приблизительность”? Зачем сортировать? И почему по возрастанию?

Тайна четвертого параметра ВПР

Корректно и глубоко тема разницы алгоритмов поиска ВПР и ПОИСКПОЗ в рунете практически не раскрыта. Между тем, примечательный факт в том, что:

Когда четвертый параметр ЛОЖЬ или 0 — используется линейный поиск;
Когда он ИСТИНА, 1 или не указан явно, но используется по умолчанию — используется бинарный поиск.

В чем разница?

Линейный поиск — это когда Excel находит искомое значение, пробегая сверху вниз по одной строке. Это совершенно не оптимально, и именно поэтому с большими таблицами ВПР работает очень медленно!

Бинарный же поиск в Excel позволяет находить данные практически мгновенно, так как выполняет четыре основных шага:

Длина диапазона данных делится пополам и позиция чтения перемещается в середину.
Найденное значение (пусть n) сравнивается с тем, которое мы ищем (пусть m).
Если m > n, то берется вторая часть массива, если m < n — первая часть.
Далее шаги 1-3 повторяются на выбранной части диапазона.

Простыми словами, это похоже на поиск по словарю. Открываем его посередине, смотрим, в какой половине нужное нам значение. В первой? Тогда открываем первую часть посередине, и так продолжаем делить напополам, пока не находим искомое.

В сравнении с линейным поиском, на целиком заполненном столбце Excel (1048576 строк, или 2 в 20 степени) бинарный будет в ~52.000 раз быстрее! (1048576 поделить на 20).

Но есть несколько моментов, которые нужно знать для работы с ВПР в этом режиме:

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

=ЕСЛИ(ВПР(искомое;диапазон;1;1)<>искомое;"";ВПР(искомое;диапазон;n;1))

Так будет выглядеть формула, которая будет возвращать пустоту, если искомое не найдено, при этом будет оставаться столь же быстрой (практически мгновенной даже при работе с сотнями тысяч строк!).

Важный момент – в Excel 2019 и выше даже при использовании параметра 0 используется оптимальный быстрый алгоритм, поэтому большого прироста скорости в последних версиях такая формула не даст (зато даст в Excel 2016 и всех версий что раньше). Вероятнее всего, в Microsoft решили, что пришло время упростить жизнь пользователям, которые не хотят во всём этом разбираться :)

Как сделать ВПР — понятная пошаговая инструкция

Как сделать правильно формулу ВПР, чтобы найти несколько значений одной таблицы в другой и найти совпадения, или для совпадающих данных вернуть данные напротив?

Ниже пошаговая инструкция по использованию функции ВПР с полезными лайфхаками и картинками. Итак:

Если читали выше, я упоминал, как просто запомнить синтаксис функции ВПР. 3 вопроса в указанном порядке. Что? Где? Когда? И потом уже “Как?”

1. Что?

Что будет искать формула? Как правило, здесь указывается адрес ячейки, в которой находится искомое значение.

2. Где?

Где будет искать формула значение из первого шага? Обычно тут указывается диапазон ячеек, состоящий из нескольких столбцов. Лайфхак: выделяйте столбцы целиком (например, B:D), а не диапазон ($B$2:$D$250). Это быстрее и не требует закреплять диапазоны (а закреплять их нужно, иначе при протягивании формулы вниз адрес будет меняться). Выделить столбцы можно, проведя по буквам столбцов левой кнопкой мыши (как показывает стрелка на скриншоте).

Функция ВПР, аргумент "Таблица" (диапазон)

3. Когда?

Когда остановиться, считая слева направо для выбора возвращаемого формулой значения? В данном случае нам нужна сумма, а это второй столбец таблицы.

4. Как?

Как искать? О разнице способов поиска ВПР читайте выше: Интервальный просмотр – 0 или 1, ЛОЖЬ или ИСТИНА? Обычно выбирается линейный поиск, так что тут вводим “0”.

И на всякий случай, пройдем все шаги подряд и посмотрим, как это выглядит на практике.

Функция ВПР в Excel пошагово — Ввод формулы с функцией ВПР – пошаговая простейшая инструкция.

ВПР по вхождению подстроки

Помимо поиска по точному совпадению, ВПР умеет искать данные и с неточным совпадением, т.к. поддерживает подстановочные символы (? и *). Важно, что их учёт возможен только в режиме линейного поиска (четвертый параметр 0 или ЛОЖЬ). Ниже пример с использованием знака вопроса, обозначающего один любой символ:

ВПР с неточным совпадением — Использование ВПР с подстановочными символами для поиска по неточному совпадению

Не работает ВПР — ошибки и причины

Функция ВПР иногда ведет себя непредсказуемо, например, не подтягивает значения, возвращает ошибки там, где не должна, или возвращает неожиданные значения. Рассмотрим, почему ВПР не срабатывает так, как должен.

ВПР выдает ошибку #Н/Д

Формулы с ВПР выдают ошибку “#Н/Д”, когда функция не находит искомое значение в указанной таблице. Следует понимать, что поиск производится исключительно по первому столбцу, а также — что если не закрепить выбранный диапазон поиска, при протягивании формулы будет смещаться и он, что в 99% случаев будет приводить к ошибкам.

Хотите избавиться от ошибки, когда искомое не найдено? Почитайте, как сделать это с помощью функции ЕСЛИОШИБКА в соответствующей статье. А я лишь приведу формулы оттуда:

Текст “ошибка”, если ошибка:

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")

Или “пусто”, если ошибка (ячейка будет пустой):

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

ВПР не подтягивает значение, хотя искомое в таблице есть

Иногда такое бывает — вы видите, что в таблице есть данные, которые вы ищете, но ВПР будто не срабатывает. Не находит данные. Абсолютно всегда проблема не в ВПР, а в самих данных, и они действительно не совпадают. Самые распространенные проблемы, когда визуально в ячейках одинаковы, но на поверку — нет:

В таблице искомых значений или в диапазоне поиска присутствуют лишние пробелы, которые нужно удалить.
Вместо обычных пробелов в ячейках используются неразрывные пробелы, визуально между ними нет никакой разницы, но программа её видит.
В данных присутствуют переносы строк, символы табуляции, возврат каретки и другие виды “невидимых” символов
Вместо кириллицы используется латиница или наоборот, для букв с идентичным написанием (для компьютера русская А и английская A – разные символы!). Тогда нужно заменить кириллицу на похожую латиницу (или наоборот).

Рекомендация в таких случаях следующая. Попробуйте сравнить ячейки, которые видите как одинаковые, отдельно (Например, скопируйте их в ячейки A1 и A2 на другой лист). Сначала сравните целиком (=A1=A2, должно вернуть “ИСТИНА”), если конструкция возвращает “ЛОЖЬ”, сравните их уже посимвольно (первый символ с первым, второй со вторым и т.д.), здесь вам поможет функция ПСТР.

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

Скачать бесплатно

Как найти, заменить и удалить “звездочки” в Excel

У пользователей Excel при обработке текстовых данных часто возникает задача удалить символы пунктуации. Обычно это делают процедурой найти и заменить. Универсальная процедура позволяет найти, заменить, а если заменять на “пустоту”, то и удалить ненужные символы из ячеек.

Однако при попытке удалить “звездочку” (знак “*”) происходит примерно следующее:

Что происходит, если удалить звёздочки неправильно — Неудачная попытка удалить “звёздочки” приводит к удалению всех данных.

В чем же дело? А в том, что “звёздочка” относится к разряду подстановочных символов, которым необходимо экранирование, чтобы они воспринимались как обычные. Подробнее про них можно почитать в соответствующей статье (Подстановочные символы в Excel). Кстати, к ним же относится и вопросительный знак.

Так как же удалить “звёздочку”, каким символом ее нужно экранировать? Ответ – символом “Тильда” (он обычно под клавишей Esc). Ниже успешный пример удаления:

Как удалить звёздочки в Excel — Успешное удаление “звёздочек” в Excel

Пожалуй, как найти звёздочку или как её заменить на что-то другое, вы уже догадались. :)

Понравилась статья? Читайте другие на этом же сайте!

Можно также поддержать автора материально, купив его разработку – полезную надстройку для Excel !SEMTools, которая позволит в пару кликов осуществлять множество массовых операций и ускорить работу в Excel. И вам полезно, и автору приятно!

Все специальные символы в Excel – исчерпывающее руководство

Подстановочные символы в Excel
Математические символы
Знак ' (апостроф) в Excel
Символ & (амперсанд) в Excel

Говоря о специальных символах в Excel, каждый мыслит по-своему. Синоним слова “специальные” – особенные. Разные группы символов имеют разные особенности, на основе которых пользователи зовут их специальными. В этой статье я попробую перечислить все такие спецсимволы.

Если вас интересуют различные значки для вставки, вам нужна Excel-функция СИМВОЛ, а ещё больше различных значков позволит вставить функция ЮНИСИМВ.

Функция ЮНИСИМВ - примеры специальных значков

Подстановочные символы в Excel

Звездочка (*) и знак вопроса среди всех спецсимволов, используемых в Excel, вызывают, пожалуй, наибольшее количество вопросов у начинающих пользователей. Однако являются очень полезными. Они являются подстановочными, иными словами, подставляются в строку вместо других символов, лишь обозначая их неким образом.

Каким образом? Все просто:

Знак вопроса обозначает один любой символ, абсолютно любой, будь то цифра, буква, пробел или что-то более экзотическое, как неразрывный пробел или знак табуляции.
Звездочка (*) обозначает любое количество любых символов, включая их отсутствие.

Использование спецсимволов при поиске

Подстановочные спецсимволы наиболее полезны при использовании штатной процедуры Excel “Найти и заменить“. Вот некоторые примеры:

Находим с помощью подстановочных символов все числа, состоящие из одного знака — Находим все числа, состоящие из одного знака

Обратите внимание на использование галочки “ячейка целиком”. Именно благодаря ей поиск происходит не по содержимому внутри ячейки, а иначе, искомое сравнивается целиком со всей ячейкой.

Находим с помощью подстановочных символов и выделяем двузначные числа, заканчивающиеся на 7 — Находим и выделяем двузначные числа, заканчивающиеся на 7

Находим с помощью подстановочных символов и выделяем все ячейки, начинающиеся на 7 — Находим и выделяем все ячейки, начинающиеся на 7

Как найти символ * (звездочку) и вопросительный знак

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

Разумеется, в Microsoft это предусмотрели и это можно сделать, поставив перед ними знак “~” именуемый Тильда и находящийся обычно в верхнем левом углу клавиатуры.

Забавно, что и сам знак Тильда тоже нужно экранировать им же самим, чтобы его найти, иначе Excel его не найдет.

поиск спецсимволов в Excel — Как найти знак вопроса, звездочку и знак Тильда в Excel

Использование при фильтрации и расширенным фильтром

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

Подстановочные символы в формулах

Помимо процедур, подстановочные символы можно использовать и в некоторых формулах. К таким относятся

СЧЁТЕСЛИ и похожие функции подсчета ячеек по условию – СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН, СРЗНАЧЕСЛИ
ПОИСК
ВПР и похожие на него функции поиска – ГПР, ПОИСКПОЗ, ПРОСМОТРХ

Использование подстановочных символов с функцией СЧЁТЕСЛИ

Математические символы

Наверняка большинство из вас сталкивались с тем, что, если текстовая ячейка начинается со знаков “плюс”, “равенство” или “минус”, Excel обычно отдает ошибку #ИМЯ.

Как знак равенства, минус и плюс вставить в начале ячейки? Дело в том, что равенство – незаменимый оператор формул Excel, а плюс и минус – математические операторы, которые нужно использовать только с числовыми значениями.

Чтобы эти символы воспринимались как текст, перед ними должен быть любой другой символ. Еще один вариант – ставить перед ними символ “‘” (апострОф). Это еще один специальный символ Excel, который меняет значение ячейки на текстовое. С его помощью вы явно сообщаете Excel, что вводимое значение – просто текст и не должно восприниматься никак иначе. Собственно, Excel предлагает этот вариант при ручном вводе:

Апостроф как спецсимвол в Excel — Ошибка и рекомендации Excel при ручном вводе в ячейку знака минус, плюс или равенства с текстом после них, не распознаваемым как формула

Знак ‘ (апостроф) в Excel

Апостроф уже был упомянут в этой статье чуть выше, где помогал в начале ячейки использовать знак равенства без превращения её в формулу.

А все дело в том, что, будучи вставленным в начало ячейки, он несет важную информацию для Excel о том, что данные в ячейке – строго текстовые, и их не нужно воспринимать никак иначе.

Любопытный факт, что длина содержимого ячейки в некоторых случаях может измениться.

Апостроф и форматы ячеек Excel — Использование апострофа для форматирования дат, времени и процентов как текст.

Почему так?

Дело в том, что Excel часто автоматически обрабатывает вводимое значение ячейки и превращает его в число в визуально аналогично выглядящем формате. Например, “50%” будет фактически 0,5, а даты будут восприняты как целые числа. Подробнее об этом можно прочитать в статье про функцию ТЕКСТ

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

Символ & (амперсанд) в Excel

Амперсанд в Excel при использовании в формулах является оператором конкатенации и нужен как более лаконичная замена функции СЦЕПИТЬ. Ниже две формулы, делающие абсолютно одно и то же действие – объединяющие ячейку A1, пробел и ячейку B1:

=СЦЕПИТЬ(A1;" ";B1)
=A1&" "&B1

Очевидно, вторая формула короче, к тому же не использует вложенность (не имеет скобок), что иногда бывает полезно при составлении сложных формул.

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

Связанные статьи:
Как найти определенные символы в ячейках Excel
Как удалить лишние символы из всех ячеек
Как добавить символы в каждую ячейку диапазона

Поиск позиции первого числа в ячейке

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

Вариант 1

{=ПОИСКПОЗ(ЛОЖЬ;ЕОШ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)-1);0)}

Формула поиска позиции первой цифры в ячейке

Как она это делает? Из массива вычитается единица (это может быть любое число), далее полученный массив ошибок и валидных значений обрабатывается функцией ЕОШ. Ошибки вычисления (там, где были нечисловые символы) вернут ИСТИНА, цифровые символы – ЛОЖЬ.

Функция ПОИСКПОЗ возвращает позицию первой цифры через поиск первого значения ЛОЖЬ.

Как и все формулы массива в Excel, данная формула вводится сочетанием клавиш Ctrl + Shift + Enter.

Вариант 2

Есть и вот такая вариация формулы массива, использующая функции МИН, ПОИСК, СИМВОЛ и ЕСЛИОШИБКА:

{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));A1);""))}

Её принцип несколько иной –

каждый из 10 цифровых символов ищется внутри строки функцией ПОИСК, возвращающей в успешных случаях его позицию, а в неуспешных – ошибку
массив результатов обрабатывается функцией ЕСЛИОШИБКА и вместо ошибок вставляет в массив пустоты, чтобы из-за ошибок не прерывалась работа функции МИН
результирующий массив обрабатывает функция МИН и возвращает минимальную из позиций – это и есть позиция первой цифры в ячейке

Функция более требовательна к ресурсам компьютера (строковые функции “дороже” математических), поэтому рекомендуется первый вариант.

Применение формул

Наиболее понятное применение – это когда нужно отделить в ячейке текст от цифр или удалить текст до первой цифры.

Поиск с конца строки, справа налево в Excel

Обычная составная формула
Формулы массива для поиска символа с конца строки
- С помощью МАКС
- С помощью ПОИСКПОЗ

Поиск позиции последнего вхождения значения внутри ячейки Excel – довольно сложная задача.

Тем не менее, иногда ее необходимо решить, например, чтобы удалить или заменить это вхождение.

Так а в чем же проблема?

Все дело в том, что функции поиска позиции ПОИСК и НАЙТИ ищут только с начала ячейки, и у них нет параметра переключения на поиск с конца.

Процедура Найти и Заменить также не подойдет. Она ищет не с конца строки, а просто находит (и заменяет) все вхождения.

Ниже я покажу пару способов, как осуществить поиск с конца строки.

Обычная составная формула

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

Формула ниже ищет пробел с конца ячейки A1:

=ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))

Как она работает:

Фрагмент, обозначенный красным цветом, вычисляет количество пробелов в ячейке. Подробнее можно почитать в описании функции ПОДСТАВИТЬ.
Это количество является аргументом еще одной подстановки, где заменяется лишь последний пробел (выделено жирным)
Вместо пробела в его последнюю позицию вставляется достаточно редкий символ. В данном случае это иероглиф, который создается функцией ЮНИСИМВ. Но можно и прописать символ вручную. В формуле ниже это обратная косая черта:

=ПОИСК("\";ПОДСТАВИТЬ(A1;" ";"\";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))

Функция ПОИСК находит позицию этого символа. Поскольку он встречается в строке всего один раз и стоит на месте последнего пробела, это и позволяет нам сказать, что поиск произведен справа налево.

Если вам необходимо найти с конца строки какой-то другой символ или текстовый фрагмент, замените пробелы в этой формуле на него.

поиск с конца строки - формула — Примеры использования составной формулы для поиска с конца строки

Формулы массива для поиска символа с конца строки

Поиск слева направо с помощью функции ПОДСТАВИТЬ, описанный выше, имеет пару недостатков.

Первый – регистрозависимость этой функции, но это легко учесть, обернув нужные фрагменты функциями изменения регистра.

А второй уже серьезнее – формула не очень универсальна, т.к. использует замену на символ, который может оказаться в строке, и тогда она выдаст неверное значение.

От обоих проблем избавят формулы массива. Они обе создают массивы значений, внутри которых функция выбирает последнее.

С помощью МАКС

Формула ниже находит позицию символа “а” в любом регистре.

={МАКС((ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)="а")*СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1))))}

ВАЖНО:
Это формула массива!
Она вводится без фигурных скобок.
Но не клавишей Enter а сочетанием: 
Ctrl + Shift + Enter
После этого фигурные скобки появятся сами.
Если ввести формулу обычным способом, она не сработает.

Механика ее работы пошагово:

Функция ДЛСТР измеряет длину ячейки в символах
ДВССЫЛ создает из текстового представления длины ссылку на диапазон строк с 1 по строку, равную этой длине
Функция СТРОКА возвращает массив чисел, соответствующих этим длинам, соответственно, {1;2;3;…”длина строки”}
Функция ПСТР, обрабатывая этот массив, возвращает для каждого числа символ, стоящий на этой позиции в строке
Текстовое сравнение с символом “а” возвращает булевый массив (значения ИСТИНА или ЛОЖЬ)
Этот массив умножается на повторно созданный массив чисел (пункты 1:3). ЛОЖЬ эквивалентна нулю, а ИСТИНА – единице, поэтому для всех символов, не равных “а”, в результирующем числовом массиве будут нули, а для равных – их позиции
Функция МАКС возвращает наибольшее число в этом массиве.

С помощью ПОИСКПОЗ

Чуть более хитрая механика делает формулу короче, вместе с тем существенно быстрее, а задействует функцию ПОИСКПОЗ:

{=ПОИСКПОЗ(2;1/(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)="а");1)}

ВАЖНО:
Это формула массива!
Она вводится без фигурных скобок.
Но не клавишей Enter а сочетанием: 
Ctrl + Shift + Enter
После этого фигурные скобки появятся сами.
Если ввести формулу обычным способом, она не сработает.

Здесь алгоритм такой:

Как и в предыдущем варианте, с помощью тех же функций ДЛСТР, СТРОКА, ДВССЫЛ, ПСТР и текстового сравнения создается булевый массив;
Но на этом этапе единица делится на него. ЛОЖЬ эквивалентна нулю и выдается ошибка деления на ноль. ИСТИНА возвращает единицу.
Функция ПОИСКПОЗ с последним параметром “1” при поиске 2 (на месте 2 может быть любое число больше 1) возвращает позицию последнего наибольшего числа, меньшее, чем 2. Т.е. последней единицы, которой и соответствует последний найденный в строке символ.

Смотрите также по теме:

Формулы массива в Excel

Удалить последнее слово в ячейке

Найти и заменить первую букву в ячейке на заглавную

Предыдущая статья о формулах массива:

Учимся формулам массива 3/4:

Извлечь текст до первой цифры в ячейке

Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
Сотни инструментов надстройки для Excel !SEMTools помогут вам упростить их решение и сэкономят ваше время!

Скачать !SEMTools