Статистические функции Excel считают показатели по набору чисел: =СРЗНАЧ(A2:A9) — среднее, =МЕДИАНА(A2:A9) — медиану, =МОДА.ОДН(A2:A9) — самое частое значение, =СТАНДОТКЛОН.В(A2:A9) — стандартное отклонение, а =СЧЁТЕСЛИ(A2:A9;">20") и =СРЗНАЧЕСЛИ(B2:B9;"чай";C2:C9) — подсчёт и среднее по условию. Ниже — примеры самых популярных функций на одних данных и полный список статистических функций с описаниями.

Статистические функции в 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, до которого функция ещё не поддерживалась и в рамках которого начала поддерживаться. Если поле пустое - функция доступна во всех версиях.

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

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

Статистические функции — частые вопросы

Какие есть статистические функции в Excel?

Самые популярные: СЧЁТ, СЧЁТЕСЛИ, СРЗНАЧ, МЕДИАНА, МОДА, МИН, МАКС, СТАНДОТКЛОН, ДИСП, НАИБОЛЬШИЙ, РАНГ. Полный список — в таблице на этой странице.

Чем среднее отличается от медианы?

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

Что такое ПЕРЕСТ в Excel?

Число перестановок: сколько упорядоченных наборов из k элементов можно составить из n без повторов. С повторами — ПЕРЕСТА.

Чем заменить МАКСЕСЛИ в старом Excel?

Формулой массива =МАКС(ЕСЛИ(A2:A7="кофе";B2:B7)), которая вводится через Ctrl+Shift+Enter.

Что значит .В и .Г в названиях функций?

.В — расчёт по выборке, .Г — по генеральной совокупности. Для данных, которые являются частью большего набора, используйте .В.

Итоги: статистические функции Excel

Надеюсь, на этой странице вы нашли то, что искали. Начните с СРЗНАЧ, МЕДИАНЫ и СЧЁТЕСЛИ — их хватает для большинства задач, а остальные функции из списка пригодятся для глубокого анализа.

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

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

Ниже — все символы, которые в Excel работают не как обычный текст, а как часть синтаксиса. Коротко:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Знак = (равно): начало формулы и сравнение

С символа «=» начинается любая формула: всё, что после него, Excel вычисляет. Если ввести 2+2, в ячейке останется текст, а =2+2 вернёт 4. Формулу можно начать и со знака «+» или «−»: это наследие старых табличных программ, Excel примет ввод и сам добавит «=» в начало.

Внутри формулы тот же знак — оператор сравнения. =A2=B2 возвращает ИСТИНА, если значения равны, и ЛОЖЬ, если нет. Первое «=» начинает формулу, второе сравнивает.

Если формула видна в ячейке как текст и не считается, причин обычно три: у ячейки текстовый формат, перед «=» стоит пробел или апостроф, или включён режим показа формул (Ctrl+` или Формулы → Показать формулы). А если наоборот нужно записать в ячейку текст, начинающийся с «=», смотрите следующий раздел.

Математические знаки в начале ячейки

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

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

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

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

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

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

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

Почему так?

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

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

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

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

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

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

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

Знак $ (доллар): абсолютные и относительные ссылки

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

• A1 — относительная ссылка: при протягивании вниз превращается в A2, A3…, при протягивании вправо — в B1, C1…;
• $A$1 — абсолютная: не меняется никогда;
• $A1 — закреплён столбец, строка меняется;
• A$1 — закреплена строка, столбец меняется.

Знаки $ ставят клавишей F4: курсор на ссылку в строке формул, каждое нажатие переключает A1 → $A$1 → A$1 → $A1. Подробно, с примерами смешанных ссылок, нарастающего итога и ВПР, — в статье абсолютные и относительные ссылки в Excel.

Кавычки в формулах Excel

Двойные кавычки говорят Excel: «это текст, не вычисляй его». Без них слово в формуле считается именем функции, диапазона или ошибкой #ИМЯ?. Что нужно знать:

• Текст в формуле всегда в кавычках: =ЕСЛИ(B2>0;"оплачено";"долг"). Числа и ссылки на ячейки в кавычки не берут.
• Число в кавычках — это текст. ="5"+1 Excel ещё посчитает, а вот сравнение =A2="5" вернёт ЛОЖЬ, если в A2 настоящее число 5.
• Пустые кавычки "" — пустая строка. =A2="" проверяет, пуста ли ячейка, а =ЕСЛИ(B2=0;"";A2/B2) оставляет ячейку визуально пустой вместо ошибки.
• Кавычка внутри текста удваивается. Чтобы получить текст Он сказал "да", пишут ="Он сказал ""да""". Четыре кавычки подряд """" — это текст из одной кавычки.
• Кавычку можно вставить функцией СИМВОЛ(34) — так формула читается проще.
• Условия в функциях пишутся в кавычках: =СЧЁТЕСЛИ(A:A;">100"). Если число в условии берётся из ячейки, кавычки закрывают перед ссылкой: =СЧЁТЕСЛИ(A:A;">"&D1).

Одинарные кавычки (апострофы) в формулах встречаются в одном месте — вокруг имени листа с пробелами: ='Продажи 2026'!B3, об этом в следующем разделе. А типографские кавычки-ёлочки « » для Excel обычный текст без особого смысла — как их поставить и заменить, рассказано в статье кавычки-ёлочки в Excel.

Восклицательный знак: ссылка на другой лист

Восклицательный знак отделяет имя листа от адреса ячейки: =Лист2!B3 — ячейка B3 на листе «Лист2». Его не нужно набирать: начните формулу, щёлкните по ярлыку другого листа и по ячейке, Excel вставит ссылку сам.

• Если в имени листа есть пробел, дефис, скобки или имя начинается с цифры, оно берётся в апострофы: ='Отчёт май'!B3.
• Если в имени листа есть сам апостроф, внутри ссылки он удваивается.
• Диапазон на другом листе: =СУММ(Лист2!B2:B100). Сразу несколько листов подряд — трёхмерная ссылка =СУММ('Январь:Март'!B2) сложит B2 со всех листов от «Январь» до «Март».
• Знак $ ставится после восклицательного знака: Лист2!$B$3.

Все способы сослаться на ячейку, лист или файл, включая гиперссылки, собраны в статье как сделать ссылку в Экселе.

Квадратные скобки в формулах

Квадратные скобки встречаются в Excel в четырёх ролях.

• Имя другой книги. ='[Курсы.xlsx]Лист1'!$B$2 — ячейка B2 на листе «Лист1» файла Курсы.xlsx. Если файл закрыт, Excel покажет перед скобками полный путь к нему.
• Столбцы умной таблицы. В таблице, созданной через Ctrl+T, ссылки пишутся по именам: =СУММ(Продажи[Сумма]) — весь столбец «Сумма» таблицы «Продажи», =[@Цена]*[@Кол-во] — значения из текущей строки. Служебные элементы: [#Заголовки], [#Данные], [#Итоги], [#Все].
• Стиль ссылок R1C1. R[-1]C[2] — ячейка на строку выше и на два столбца правее. Скобки означают относительное смещение; включается стиль в Файл → Параметры → Формулы.
• Числовые форматы. В пользовательском формате в скобках пишут цвет и условие: [Красный] окрашивает число, [>1000] задаёт условие, [ч]:мм показывает больше 24 часов.

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

Разделители в формулах: точка с запятой, двоеточие, пробел

• ; (точка с запятой) разделяет аргументы функции в русской версии Excel: =ЕСЛИ(A2>0;"да";"нет"). В английской версии вместо неё запятая, потому что запятая у нас — десятичный разделитель. Поэтому формула, скопированная с англоязычного сайта, выдаёт ошибку: замените запятые между аргументами на точки с запятой, а названия функций — на русские по таблице функций на русском и английском.
• : (двоеточие) задаёт диапазон: A2:C10 — прямоугольник от A2 до C10, A:A — весь столбец, 2:2 — вся строка.
• Пробел между двумя диапазонами — оператор пересечения: =СУММ(B2:D5 C1:C10) сложит только общие ячейки C2:C5. Случайный пробел в формуле поэтому даёт ошибку #ПУСТО!.
• Несколько диапазонов в одной функции перечисляют через точку с запятой: =СУММ(A2:A10;C2:C10).
• , (запятая) в русской версии — только десятичный разделитель в числах: =A2*1,2.

Операторы и условия: <>, >=, %, ^ и двойное отрицание

В функциях с условием — СЧЁТЕСЛИ, СУММЕСЛИМН и похожих — оператор пишется внутри кавычек вместе со значением: =СЧЁТЕСЛИ(B:B;"<>0") считает ненулевые, =СЧЁТЕСЛИ(A:A;"<>") — непустые ячейки, =СЧЁТЕСЛИ(A:A;"") — пустые. Значение из ячейки приклеивают амперсандом: =СУММЕСЛИ(B:B;">="&E1).

Двойное отрицание «--» — два минуса подряд: первый меняет знак, второй возвращает обратно, а в результате ИСТИНА становится 1, ЛОЖЬ — 0, а текст «15» — числом 15. Так считают ячейки по условию без СЧЁТЕСЛИ:

Порядок вычислений как в математике: сначала степень, потом умножение и деление, потом сложение и вычитание, затем склейка & и в последнюю очередь сравнение. Используйте круглые скобки, если сомневаетесь. Одна особенность: отрицание считается раньше степени, поэтому =-2^2 даёт 4, а не −4.

Символы массивов: #, @ и фигурные скобки

• # после ссылки — весь динамический массив, который «разлился» из ячейки в Excel 365 и 2021: =СЧЁТЗ(C2#). Если массив станет длиннее, ссылка расширится сама.
• @ перед ссылкой или функцией — неявное пересечение: взять одно значение вместо массива. Excel 365 часто сам добавляет @ в формулы из старых файлов, чтобы они считали как раньше. Внутри умной таблицы [@Цена] — значение из текущей строки.
• { } в константах — массив прямо в формуле: =СУММ(СЧЁТЕСЛИ(A:A;{"Анна";"Олег"})) считает оба имени сразу. В русской версии элементы строки разделяют точкой с запятой, а строки — двоеточием: {1;2;3} — по горизонтали, {1:2:3} — по вертикали.
• { } вокруг всей формулы — старая формула массива, введённая через Ctrl+Shift+Enter. Набирать эти скобки вручную нельзя, Excel ставит их сам.

Подробнее о массивах — в статье формулы массива в Excel.

Спецсимволы в регулярных выражениях

В регулярных выражениях — в функциях РЕГТЕСТ, РЕГИЗВЛЕЧЬ, РЕГЗАМЕНИТЬ и в процедурах !SEMTools — у символов . * + ? ^ $ | \ ( ) [ ] { } совсем другое значение, чем в формулах. Например, точка там — любой символ, а $ — конец строки. Этот синтаксис разобран отдельно в статье регулярные выражения в Excel.

Специальные символы Excel: вопросы и ответы

Что означает знак $ в формуле Excel?

Закрепляет столбец, строку или оба в ссылке, чтобы они не менялись при копировании формулы: $A$1, $A1, A$1. Ставится клавишей F4.

Что означает восклицательный знак в формуле?

Отделяет имя листа от адреса ячейки: Лист2!B3 — ячейка B3 на листе «Лист2».

Как вставить кавычки в формулу Excel?

Внутри текста кавычку удваивают: ="Он сказал ""да""". Или используют СИМВОЛ(34).

Что значат квадратные скобки в формуле?

Имя другой книги ([Книга.xlsx]Лист1!A1), столбец умной таблицы (Таблица1[Цена]) или смещение в стиле R1C1.

Почему формула с англоязычного сайта не работает?

В русском Excel аргументы разделяет точка с запятой, а не запятая, и функции называются по-русски.

Как записать в ячейку текст, начинающийся со знака =?

Поставьте перед ним апостроф: '=текст. Апостроф в ячейке не отображается.

Итоги: спецсимволы Excel

Надеюсь, на этой странице вы нашли то, что искали, и теперь знаете, как работают специальные символы в Excel. Чаще всего проблемы вызывают звёздочка и знак вопроса при поиске (их экранируют тильдой), текст, начинающийся с «=» или минуса (ставьте перед ним апостроф), и забытый знак $ при протягивании формулы.

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

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

Найти первую цифру с ПОИСКПОЗ и ЕОШ

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

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

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

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

Позиция первого числа с МИН и ПОИСК

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

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

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

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

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

Разделить текст до и после первого числа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск с конца строки в Excel 365

В Excel 365 появились функции ТЕКСТДО и ТЕКСТПОСЛЕ, и задача стала заметно проще. Их третий аргумент — номер вхождения разделителя, и отрицательный номер означает отсчёт с конца строки. Текст после последнего пробела:

=ТЕКСТПОСЛЕ(A1;" ";-1)

Текст до последнего пробела:

=ТЕКСТДО(A1;" ";-1)

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

=ДЛСТР(A1)-ДЛСТР(ТЕКСТПОСЛЕ(A1;" ";-1))

Если разделителя в ячейке нет, функции вернут ошибку #Н/Д — оберните их в ЕСЛИОШИБКА. В более старых версиях Excel этих функций нет, и тогда выручат формулы из разделов выше.

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

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

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

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

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

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

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