Excel индекс символа в строке

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​Смотрите также​ не зависимо от​ подстроку заданного размера.​​Для заполнения столбца «Наименование»​​ в Excel».​​Ещё вариант сделать​​ «Вычитание». Если вводим​

Описание

​ что она вернет​​слово дрель, и​​которых составляет 5​​(Изменить списки…)​​А​(СИМВОЛ) возвращает определённый​ПРАВСИМВ, ПРАВБ​ доступна в выпуске​ и возвращает число​    Обязательный. Текст, который требуется​В этой статье описаны​ величины числа.​ Начальная позиция символа​ используем следующую формулу:​Функция ПСТР в Excel​

​ цифру, букву надстрочной​

​ символ амперсанд (&),​​ не одно число,​​ у которых есть​ символов.​Нажмите кнопку​

​. Вы можете ввести​ символ, код которого​Возвращают крайние справа знаки​

​ Excel 2013 и всех​

​ 9. Функция​​ найти.​​ синтаксис формулы и​В Excel подготовка и​ в строке соответствует​=ПСТР(A2;1;НАЙТИ(» «;A2))​ предназначена для выделения​​ или подстрочной. Пишем​​ то Excel понимает,​​ а массив целых​​ перед ним и​Критерий будет вводиться в​Import​А​ соответствует введённому числу,​ текстовой строки.​​ последующих версиях.​​ПОИСК​​Просматриваемый_текст​​ использование функций​ заполнение данных дополнительными​​ числу 1. Если​​Функция НАЙТИ возвращает номер​​ подстроки из строки​​ маленькую букву «о».​ что нужно соединить​ чисел — соответствующих​

​ после него как​​ ячейку​

• ​(Импорт), затем​в ячейку и​ опираясь на таблицу​

• ​ПОИСК, ПОИСКБ​Функция​всегда возвращает номер​    Обязательный. Текст, в котором​ПОИСК​ нулями являются достаточно​ данный аргумент принимает​ позиции символа пробела​ текста, переданной в​ Выделяем её. Нажимаем​ в ячейке два​ номеров строк. Для​ минимум 1 символ.​

​С2​ОК​ сослаться на неё,​ символов Вашего компьютера.​Ищут вхождения одного текстового​

Синтаксис

​Описание​

​ знака, считая от​

​ нужно найти значение​и​

• ​ простым способом в​​ дробное число из​ « » в​

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

• ​и закройте меню​​ или ввести букву​ (Для Windows —​​ значения в другом​​ASC​ начала​

Замечание

• ​ аргумента​​ПОИСКБ​​ применении. Если, например,​​ диапазона допустимых значений,​​ просматриваемой строке, который​ и возвращает требуемое​ из контекстного меню​​Есть видимые символы,​​ Строке формул выражение​​ найденные значения, воспользуемся​​ «дрел?». Вопросительный знак​

• ​ настроек.​​А​​ это​ (без учета регистра).​Для языков с двухбайтовыми​​просматриваемого текста​​искомый_текст​​в Microsoft Excel.​​ каждое значение в​ дробная часть будет​ принимается в качестве​ количество символов начиная​ функцию «Формат ячейки»,​ которые видны в​ =СТРОКА(F8:F18). Выделите формулу​​ формулой массива:​​ является подстановочным знаком.​

• ​Теперь введите любую букву​​в функцию в​​ANSI​ПОДСТАВИТЬ​

• ​ наборами знаков (например,​​, включая символы, которые​​.​Функции​

• ​ поле «ID клиента»​​ усечена;​​ аргумента число_знаков функции​ с заданной позиции.​ ставим галочку у​​ ячейке. Но есть​​ и нажмите​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​

• ​Для реализации этого варианта​​ на листе Excel​​ виде текстовой строки.​, а для Macintosh​Заменяет в текстовой строке​ катакана) преобразует полноширинные​​ пропускаются, если значение​​Начальная_позиция​ПОИСК​ должно иметь по​число_знаков – обязательный аргумент,​ ПСТР. В результате​Один символ в языках​ функции «надстрочный». Нажимаем​​ невидимые символы, их​​F9​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($G$2;Список);СТРОКА(Список)-СТРОКА($A$4);НД());»»);​ поиска требуется функция​ и используйте автозаполнение,​=CODE(«A») или =CODE(B1)​ – кодировка​ старый текст новым.​​ (двухбайтовые) знаки в​​ аргумента​    Необязательный. Номер знака в​И​ 10 чисел, то​​ принимающий значение из​​ расчетов получим:​ с однобайтовой кодировкой​ «ОК».​​ не видно в​​(см. файл примера).​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))​ позволяющая использовать подстановочные​​ чтобы завершить последовательность.​​=КОДСИМВ(«А») или =КОДСИМВ(B1)​Macintosh​Т​​ полуширинные (однобайтовые).​​начальная_позиция​

Примеры

​ аргументе​ПОИСКБ​ для реализации данной​ диапазона неотрицательных чисел,​Для заполнения столбца «Марка»​ соответствует 1 байту.​Или выделяем цифру.​ ячейках, но они​Получим массив последовательных чисел​)​ знаки: используем функцию​Вы можете создать справочную​В данном примере код​

support.office.com

Текстовые функции (справка)

​CHAR​ английского алфавита. Для​(СИМВОЛ) может решить​ его в текст.​

​ ß (БАТ).​​ вставьте их в​Функции​ позицию первой текстовой​ Например, для идентификатора​ качестве этого аргумента​Функция НАЙТИ возвращает позицию​ ПСТРБ (возвращает подстроку​Вставить символ кодом в​ «разрыв строки» в​ например, статью Сложение​ диапазона​

​Возвращает числовой код первого​​ измените ширину столбцов,​и​=ПОИСК(«н»;»принтер»)​ данных.​ часть строки начиная​ начальная_позиция функции ПСТР.​ считаться за два.​ расположена НЕ над​ удалить лишние пробелы​, состоящий из 13​I2​

support.office.com

30 функций Excel за 30 дней: СИМВОЛ (CHAR)

​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))​​ на цифровой клавиатуре).​ любую букву алфавита,​​Функция​ эффективно объединит диапазоны.​ знака в текстовой​​ чтобы видеть все​​НАЙТИБ​Эта функция возвращает​Ниже на рисунке изображено​ с указанной вторым​ Для упрощения, вместо​ Двухбайтовыми языками являются​ буквами вверху клавиатуры,​​ в Excel» тут.​​ ячеек, в Строке​​и выглядит так:​​)​​Запишите числа от 32​​ добавляя​CHAR​СЖПРОБЕЛЫ​ строке.​

​ данные.​.​​4​​ автоматическое дозаполнение недостающего​ аргументом позиции. В​ поиска номера последней​ корейский, японский и​ а — или​Символ​ формул введите выражение​

Функция 08: CHAR (СИМВОЛ)

​ «дрел?». В этом​​Часть формулы ПОИСК($C$2;Список)=1 определяет:​​ до 255 в​64​(СИМВОЛ) имеет вот​Удаляет из текста пробелы.​СЦЕП​Данные​В аргументе​​, так как «н»​​ количества символов нулями​ дробных числах, используемых​​ позиции (например, с​​ китайский.​

Как можно использовать функцию CHAR (СИМВОЛ)?

​ справа от букв,​​Excel​​ =СТРОКА(F8:F18) как формулу​ случае будут выведены​

• ​начинается​ таблицу.​
• ​к заданному номеру.​
• ​ такой синтаксис:​ЮНИСИМВ​
• ​Объединяет текст из нескольких​Выписки​

Синтаксис CHAR (СИМВОЛ)

​искомый_текст​​ является четвертым символом​​ в конец строки​ в качестве данного​

​ помощью функции ДЛСТР)​
​Пример 1. В столбце​

• ​ или на ноутбуке​​можно вставить в​ массива, нажав​ все значения,​ли значение из​В соседнем столбце, используя​=CHAR(B5+64)​CHAR(number)​Возвращает символ Юникод, на​

Ловушки CHAR (СИМВОЛ)

​ диапазонов или строк,​Доход: маржа​​можно использовать подстановочные​​ в слове «принтер».​​ с помощью простой​​ аргумента, дробная часть​ указано число 100,​ таблицы содержатся даты,​ на клавишах букв.​ формулу, и он​CTRL+SHIFT+ENTER​заканчивающиеся​ диапазона​ функцию​=СИМВОЛ(B5+64)​СИМВОЛ(число)​ который ссылается заданное​ но не добавляет​

Пример 1: Добавляем разрыв строки

​маржа​ знаки: вопросительный знак​Можно также находить слова​​ формулы:​​ усекается.​ которое в данном​ записанные в виде​ Например, 1 –​

​ будет выполнять определенную​
​.​

​на слова дрель​A5:A13​CHAR​​Используя инструмент автозаполнения в​​number​ числовое значение.​ разделитель или аргументы​Здесь «босс».​

Пример 2: Находим букву в алфавите

​ (​ в других словах.​​В результате каждый идентификатор​​Функция ПСТРБ имеет схожий​ примере гарантированно превышает​ текстовых строк. Записать​ на клавише с​

​ функцию. Читайте о​Функция СТРОКА() вернула номера​​ или дрели.​​с фразы «дрел?».​(СИМВОЛ), отобразите соответствующие​ Excel Вы можете​​(число) – число​​UNICODE​​ IgnoreEmpty.​​Формула​?​ Например, функция​​ получил необходимое ему​​ синтаксис:​ количество знаков в​

​ отдельно в соседних​
​ буквой О, 2​

​ таких символах в​​ строк, указанных в​​ ​Часть формулы ДЛСТР($C$2)=ДЛСТР(Список)​ этим числовым кодам​ написать два числа​​ от 1 до​​Возвращает число (кодовую страницу),​

​СЦЕПИТЬ​Описание​​) и звездочку (​​=ПОИСК(«base»;»database»)​ количество нулей, чтобы​=ПСТРБ(текст;начальная_позиция;число_байтов)​​ изначальной строке.​​ столбцах номер дня,​

​ – на клавише​
​ статье «Подстановочные знаки​

Пример 3: Перечисляем весь алфавит

​ аргументе «ссылка». Т.к.​Для создания списка, содержащего​ определяет:​ символы.​ и быстро создать​ 255, определяющее символ,​ которая соответствует первому​Объединяет несколько текстовых элементов​Результат​*​​возвращает​​ соответствовать правилу для​Она отличается единственным аргументом:​В результате расчетов получим:​

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

​Чтобы показать символы другого​
​ всю последовательность чисел.​

Создаем пользовательский список

​ который Вы хотите​ символу текста.​ в один.​=ПОИСК(«и»;A2;6)​). Вопросительный знак соответствует​5​

1. ​ дальнейшего запыления поля​число_байтов – обязательный аргумент,​Пример 3. В таблице​ выделенные из представленных​
2. ​Чтобы включить эту​Символы, которые часто​ (13) было больше​ формулой массива:​значения из диапазона​ шрифта, такого как​​ К сожалению, это​​ получить. Символ берется​
3. ​ПРОПИСН​​DBCS​​Позиция первого знака «и»​ любому знаку, звездочка —​​, так как слово​​ «ID клиента» при​ принимающий целые числа​ содержатся данные о​​ дат.​​ числовую клавиатуру, нужно​
4. ​ используются, расположены на​​ числа строк диапазона​​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​A5:A13​​Symbol​​ не работает с​
5. ​ из символьной таблицы,​​Преобразует все буквы текста​​Для языков с двухбайтовыми​
6. ​ в строке ячейки​​ любой последовательности знаков.​​ «base» начинается с​​ импорте таблицы в​​ из диапазона от​ сотрудниках в столбцах​
7. ​Вид исходной таблицы данных:​ нажать на клавишу​ клавиатуре. Смотрите в​F8:F18​

Пример 4: Создаём справочную таблицу символов

​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2)));СТРОКА(Список)-СТРОКА($A$4);НД());»»);​5 символам?​или​​ буквами. Тем не​​ используемой на Вашем​ в прописные.​ наборами знаков (например,​ A2, начиная с​ Если требуется найти​ пятого символа слова​​ базу данных.​​ 1 до N,​ ФИО и дата​

1. ​Для заполнения номера дня​ «Num Lk» или​ статье «Где на​
2. ​(10), то в​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))​​Знак * (умножить) между​​Wingdings​ менее, Вы можете​ компьютере.​

​ЗНАЧЕН​ катакана) преобразует полуширинные​​ шестого знака.​​ вопросительный знак или​​ «database». Можно использовать​​​ где N –​​ рождения. Создать столбец,​​ используем следующую формулу​

​ «Num Lock» (вверху​ клавиатуре кнопка» здесь.​
​ трех ячейках формула​
​)​

​ частями формулы представляет​

office-guru.ru

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки

​, измените шрифт в​ использовать функцию​Так же, как и​Преобразует текстовый аргумент в​ (однобайтовые) знаки в​7​ звездочку, введите перед​ функции​Формула, которая изображена выше​ число байтов в​ в котором будет​ (использовать в качестве​ в правой части​Но специальные символы,​

​ вернула значения ошибки​Часть формулы ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2))) определяет:​ условие И (значение​​ столбце, содержащем функцию​​CHAR​

Задача

​ для функции​ число.​ текстовой строке в​=ПОИСК(A4;A3)​ ним тильду (​ПОИСК​ на рисунке сначала​

А. Найти значения, которые начинаются с критерия и содержат определенное количество символов

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

​ #Н/Д.​совпадают ли последние 5​ должно начинаться с​

​CHAR​(СИМВОЛ), чтобы создать​​CODE​​Важно:​ полноширинные (двухбайтовые).​​Начальная позиция строки «маржа»​​~​и​

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

​Чтобы вывести номера строк​ символов​ дрел и иметь​(СИМВОЛ).​ список всех букв​(КОДСИМВ), результат​ Вычисляемые результаты формул и​РУБЛЬ​ (искомая строка в​).​

​ПОИСКБ​ ячейки A4 ряд​ возвращаемой подстроке.​
​ в формате «Иванов​
​A2:A10 – диапазон ячеек​
​ любой символ кодом​
​ таблице.​

​ в горизонтальном диапазоне​​значений из диапазона​​ такую же длину,​Урок подготовлен для Вас​​ алфавита.​​CHAR​
​ некоторые функции листа​Преобразует число в текст,​​ ячейке A4) в​​Если​​для определения положения​​ символов из 10-ти​

​Примечания:​ – 27».​ с текстовым представлением​ в ячейку, нажимаем​Таблица символов​ используйте формулу =ТРАНСП(СТРОКА(F8:F18)).​A5:A13​ как и критерий,​ командой сайта office-guru.ru​Используйте тот же приём​(СИМВОЛ) может быть​ Excel могут несколько​ используя денежный формат​ строке «Доход: маржа»​

Б. Найти значения, которые начинаются со слова дрель или дрели и содержат как минимум 6 букв

​искомый_текст​​ символа или текстовой​​ нулей и в​Функция ПСТР вернет пустую​Вид исходной таблицы:​ дат, из которых​ клавишу «Alt», удерживаем​​Excel​​ Не забудьте нажать​с фразой «дрел?».​​ т.е. 5 букв).​​Источник: http://blog.contextures.com/archives/2011/01/09/30-excel-functions-in-30-days-08-char/​

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

​ результате получаем новый​​ строку, если в​​Для возврата строки с​ будут выделены номера​​ её нажатой и​​расположена на закладке​CTRL+SHIFT+ENTER​ Критерию также будут​ Критерию также будут​Перевел: Антон Андронов​

В. Найти значения, у которых слово дрель находится в середине строки

​64​​ от операционной системы.​​ под управлением Windows​СОВПАД​ выполняется поиск — A3).​ значение ошибки #ЗНАЧ!.​​ текстовой строке, а​​ идентификатор. Каждый из​ качестве аргумента начальная_позиция​ фамилией и текущим​ дней;​ вводим цифры кода​

​ «Вставка» в разделе​.​ соответствовать значения заканчивающиеся​
​ соответствовать такие несуразные​
​Автор: Антон Андронов​
​к номеру символа,​
​ Коды для таблицы​

​ с архитектурой x86​​Проверяет идентичность двух текстовых​​8​Если аргумент​​ затем вернуть текст​​ них теперь в​ было передано число,​ возрастом используем следующую​1 – номер начальной​ символа. Отпускаем клавишу​

Г. Найти значения, которые заканчиваются на слово дрель или дрели

​ «Текст» кнопка «Символ».​​Записав в правиле Условного​​ на фразы дрела,​ значения как дрел5,​Найдем текстовые значения, удовлетворяющие​ чтобы получить буквы​​ символов ASCII (от​​ или x86-64 и​ значений.​

​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)​

​начальная_позиция​ с помощью функций​ любом случае имеет​
​ превышающее количество символов​
​ формулу:​
​ позиции символа извлекаемой​
​ «Alt». Символ появился​

​Для примера, как​​ форматирования для диапазона​ дрел6 и т.п.​​ дрелМ и т.п.​​ заданному пользователем критерию.​​ алфавита.​ 32 до 126)​ компьютерах под управлением​НАЙТИ, НАЙТИБ​Заменяет слово «маржа» словом​

​опущен, то он​
​ПСТР​ не менее 10-ти​ в исходной строке.​Функция ПСТР возвращает часть​ подстроки (первый символ​ в ячейке.​ можно использовать символы,​

excel2.ru

Функция СТРОКА() в MS EXCEL

​A5:A18​СОВЕТ:​ (если они содержатся​ Критерии заданы с​=CHAR(A2+64)​

Синтаксис функции

​ в большинстве своем​​ Windows RT с​

​Ищет вхождения одного текстового​​ «объем», определяя позицию​ полагается равным 1.​и​

• ​ чисел.​Если в качестве аргумента​ строки до символа​ в исходной строке);​Коды символов​ смотрите, как вставить​формулу =НЕЧЁТ(СТРОКА())=СТРОКА() получим​​О поиске текстовых​​ в списке).​ использованием подстановочных знаков.​

• ​=СИМВОЛ(A2+64)​ соответствуют символам на​ архитектурой ARM. Подробнее​ значения в другом​

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

​ слова «маржа» в​Если аргумент​ПСТРБ​Далее применяется функция ЛЕВСИМВ,​ начальная_позиция было передано​ пробела, позиция которого​2 – номер последней​Excel.​ и использовать символ​ выделение цветом каждой​ значений с учетом​Критерий вводится в ячейку​ Поиск будем осуществлять​Если Вы хотите вводить​​ Вашей клавиатуре. Однако,​​ об этих различиях.​

​ (с учетом регистра).​ ячейке A3 и​начальная_позиция​или заменить его​ которая обрезает каждое​ значение 1, а​ определяется функцией НАЙТИ.​

​ позиции символа извлекаемой​Здесь приведены коды​ «Стрелка», в статье​ второй строки (см.​​ РЕгиСТра читайте в​​E2​ в диапазоне с​ алфавит при помощи​ символы для более​Вчера в марафоне​​ФИКСИРОВАННЫЙ​​ заменяя этот знак​

​не больше 0​ с помощью функций​ исходное значение до​ аргумент число_знаков определен​ Для нахождения возраста​ подстроки.​​ часто используемых символов.​​ «Символ в Excel​ рисунок выше).​ статье Поиск текстовых​и выглядит так:​

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

Использование функции для чрезстрочного выделения

​ или больше, чем​ЗАМЕНИТЬ​​ первых 10-ти чисел​​ числом, которое равно​ сотрудника используется функция​Аналогичным способом выделим номера​В Excel можно​

Формирование списка без повторов

​ для визуализации данных».​Если список значений содержит​​ значений в списках.​​ «дрел??». В этом​ наличии повторов, можно​ импортировать список букв​ 129 до 254)​
​ 30 дней​
​ его в текст​ знаков текстовой строкой​ длина​

excel2.ru

Символ в Excel.

​и​ от начала строки.​ или больше общему​ ДОЛЯГОДА, полученное значение​​ месяца и годы​ ​ установить в ячейке​​Коды символов Excel.​ повторяющиеся значения (диапазон​ Часть3. Поиск с​ случае будут выведены​
​ ожидать, что критерию​ ​ алфавита в пользовательский​​ могут различаться.​мы определяли код​ с заданным числом​ «объем.»​просматриваемого текста​ЗАМЕНИТЬБ​ Для этого во​ числу знаков в​ которой усекается до​ для заполнения соответствующих​ ссылку в виде​Каждый символ имеет​A21:A34​ учетом РЕГИСТРА.​ все значения, в​ будет соответствовать несколько​
​ список.​Чтобы добавить разрыв строки​ символа при помощи​ десятичных знаков.​Доход: объем​, возвращается значение ошибки​. Эти функции показаны​ втором аргументе функции​ исходной строке, функция​ ближайшего меньшего целого,​ столбцов с учетом,​ символа на конкретную​ свой код. Его​), то функция СТРОКА()​Функция СТРОКА(), английский вариант​ которые​ значений. Для их​Скопируйте ячейки с буквами​ в ячейке, можно​
​ функции​ ​ЛЕВСИМВ, ЛЕВБ​​=ПСТР(A3;ПОИСК(» «;A3)+1,4)​ #ЗНАЧ!.​ в примере 1​ ЛЕВСИМВ указано число​ ПСТР вернет всю​ чтобы получить число​ что номер месяца​
​ строку в другой​ можно посмотреть в​ может быть использована​ ROW(), вычисляет номер​начинаются​
​ вывода в отдельный​ алфавита и вставьте​ нажать​CODE​
​Возвращают крайние слева знаки​ ​Возвращает первые четыре знака,​​Аргумент​ данной статьи.​ 10.​
​ строку целиком.​ полных лет. Функция​ начинается с 4-го​ таблице на другом​ таблице символов. Нажимаем​ для формирования списка​ строки, определяемой ссылкой:​
​с текста-критерия (со​
​ диапазон удобно использовать​ их в отдельную​Alt+Enter​(КОДСИМВ), а также​ текстового значения.​ которые следуют за​начальная_позиция​Важно:​Если необходимо сделать так​Если аргумент начальная_позиция был​ ТЕКСТ преобразует полученное​ символа в каждой​ листе. Например, есть​ в таблице символов​ без повторов. Формула​ формула =СТРОКА(B5) вернет​ слова дрел) и​ формулы массива.​ строку как значения.​​. Когда Вы объединяете​ использовали её, чтобы​​ДЛСТР, ДЛИНБ​​ первым пробелом в​можно использовать, чтобы​ ​ чтобы нули были​​ указан числом из​ значение в текстовую​
​ строке, а год​ таблица с общими​ на нужный символ​=ЕСЛИ(ПОИСКПОЗ(A21;$A$21:$A$34;0)=СТРОКА()-СТРОКА($A$20);A21;»»)​
​ 5.​длиной как минимум​Пусть Исходный список значений​Выберите эти ячейки. Здесь​ текстовые строки в​ разоблачить скрытые символы.​Возвращают количество знаков в​ строке «Доход: маржа»​ пропустить определенное количество​Эти функции могут быть​ автоматически добавлены не​
​ диапазона отрицательных чисел​ строку.​ – с 7-го.​ данными. Нам нужно​
​ и в строке​возвращает значение если​
​СТРОКА​6 символов.​
​ (например, перечень инструментов)​
​ главное не перепутать​ формуле, нужно использовать​ В восьмой день​ текстовой строке.​ (ячейка A3).​ знаков. Допустим, что​ доступны не на​ с правой стороны,​ или 0 (нулем),​Для соединения (конкатенации) полученных​ Используем следующие формулы:​ узнать конкретную информацию​ «Код знака» виден​ оно встречается первый​
​(ссылка)​Для создания списка, содержащего​ находится в диапазоне​ и выбрать ячейки​ символ разрыва строки.​ нашего марафона мы​СТРОЧН​
​марж​ функцию​ всех языках.​ а с левой​ функция ПСТР вернет​ строк используются символы​=ПСТР(A2:A10;4;2)​ по какому-то пункту​ код этого символа.​
​ раз.​ ​Ссылка​​ найденные значения, воспользуемся​A5:A13.​
​ именно со значениями,​=C2&CHAR(10)&C3​ изучим злого близнеца​Преобразует все буквы текста​=ПОИСК(«»»»;A5)​ПОИСК​Функция ПОИСКБ отсчитывает по​ (например, так: 0001234567).​ код ошибки #ЗНАЧ!.​ «&». В результате​=ПСТР(A2:A10;7;4)​ (контактные данные по​ Ставим в строке​Кроме цифр и​  — ячейка или интервал​ формулой массива:​См. Файл примера.​ а не с​=C2&СИМВОЛ(10)&C3​ функции​ в строчные.​Позиция первой двойной кавычки​нужно использовать для​ два байта на​ Тогда следует немного​Если аргумент число_знаков задан​
​ вычислений получим:​Вид заполненной таблицы данных:​ человеку, т.д.). Нажимаем​ «из» — «Юникод»,​
​ букв, можно применять​ ячеек, для которых​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​Выведем в отдельный диапазон​ функцией​

excel-office.ru

Пример функция ПСТР для разделения текста на части в Excel

​После того как Вы​CODE​ПСТР, ПСТРБ​ («) в ячейке​ работы с текстовой​ каждый символ, только​ изменить формулу, а​ отрицательным числом, результатом​

Примеры использования функции ПСТР в Excel

​Функция имеет следующую синтаксическую​Таким образом нам удалось​ на ссылку и​ если не срабатывает,​ в Excel определенные​ определяется номер строки.​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($E$2;Список)=1;СТРОКА(Список)-СТРОКА($A$4);НД());»»);​ значения, которые удовлетворяют​CHAR​ создадите формулу, отформатируйте​(КОДСИМВ) – функцию​Возвращают заданное число знаков​ A5.​ строкой «МДС0093.МужскаяОдежда». Чтобы​ если языком по​ вместо функции ЛЕВСИМВ​ выполнения функции ПСТР​ запись:​

Как разделить текст на несколько ячеек по столбцам в Excel?

​ разрезать на части​ Excel переходит в​ то — «Кирилица​ символы. Есть​Если ссылка опущена, то​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))​ критерию, причем критерий​(СИМВОЛ).​ ячейку командой​

​CHAR​

​ из строки текста,​5​ найти первое вхождение​ умолчанию является язык​

​ использовать функцию ПРАВСИМВ:​

• ​ будет код ошибки​=ПСТР(текст;начальная_позиция;число_знаков)​ текст в ячейках​ другую таблицу на​ (дес.). Обязательно проверяйте,​
• ​в​ функция СТРОКА() вернет​)​ задан с использованием​
• ​Нажмите кнопку​Wrap Text​(СИМВОЛ). Возможно, функция​

​ начиная с указанной​=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1)​ «М» в описательной​ с поддержкой БДЦС.​Как видено на рисунке​ #ЗНАЧ!.​Описание аргументов:​ столбца A. Удалось​ другом листе на​ что написано и​

​Excel таблица символов​

​ номер строки в​

​Часть формулы ПОИСК($E$2;Список)=1 определяет:​

​ подстановочных знаков (*,​Office​(Перенести текст), чтобы​CHAR​ позиции.​Возвращает из ячейки A5​ части текстовой строки,​ В противном случае​ на этот раз,​

​Очень часто данные набранные​

Как вырезать часть текста ячейки в Excel?

​текст – обязательный для​ отдельно каждую дату​ строку именно этого​ ставьте нужное в​. Эти символы не​ которую введена сама​начинается​ ?). Рассмотрим различные​(Офис) на Ленте​ увидеть перенос строки​

​(СИМВОЛ) не такой​

​ЧЗНАЧ​ только текст, заключенный​

​ задайте для аргумента​

​ функция ПОИСКБ работает​ используя символ амперсант,​ в электронных таблицах​ заполнения аргумент, принимающий​ разделить на несколько​ человека, т.д. Для​ строках «Шрифт» и​ просто написаны как​

​ формула. Т.е. если​ли значение из​ варианты поиска.​

​ Excel, затем выберите​

​ вместо таинственного символа​ уж и злой​Преобразует текст в число​ в двойные кавычки.​начальная_позиция​ так же, как​ мы добавили по​ Excel используются для​ ссылку на ячейку​ ячеек по столбцам:​ ссылки можно использовать​ «Набор» в таблице​ буквы, а выполняют​ формула =СТРОКА() введена​ диапазона​Для удобства написания формул​Excel Options​ между строками.​

​ близнец, просто с​

Как посчитать возраст по дате рождения в Excel?

​ независимо от языкового​босс​значение 8, чтобы​ функция ПОИСК, и​ 10 нулей слева​ заполнения файлов баз​ с текстом или​ день, месяц и​ любые символы. Подробнее​ символа.​

​ определенную функцию.​

​ в ячейку​A5:A13​ создадим Именованный диапазон​(Параметры Excel). Если​

​Быстро! Какая 19-я буква​ противоположным характером.​ стандарта.​Чтобы просмотреть более подробные​ поиск не выполнялся​ отсчитывает по одному​ к исходному значению​ данных. Файлы такого​ текстовую строку, заключенную​ год.​ о такой функции​Набор символов бывает​Что в​B5​

​с фразы «дрел??».​ Список для диапазона​ у Вас Excel​ алфавита? С функцией​

Особенности использования функции ПСТР в Excel

​Итак, посмотрим подробнее инструкцию​PHONETIC​

​ сведения о функции,​

​ в той части​

• ​ байту на каждый​ ячейки A4. После​ типа часто требуют​ в кавычки, из​​ Excel читайте в​ «​Excel означает символ​, то результат будет​ Критерию также будут​
• ​A5:A13​ 2010, то выберите​CHAR​ к функции​Извлекает фонетические (фуригана) знаки​ щелкните ее название​ текста, которая является​ символ.​ чего обрезали каждый​ соблюдения правил заполнения.​ которой будет извлечена​Пример 2. В столбце​ статье «Гиперссылка в​Надстрочный/подстрочный​. Например, если начинаем​ 5.​
• ​ соответствовать значения начинающиеся​.​Options​(СИМВОЛ) Вы выясните​CHAR​ из текстовой строки.​ в первом столбце.​ серийным номером (в​К языкам, поддерживающим БДЦС,​ идентификатор оставляя только​ Например, чтобы определенные​ подстрока определенной длины​ таблицы хранятся текстовые​ Excel на другой​». Это значит, что​ вводить в ячейку​В аргументе «ссылка» нельзя​ с фраз дрелью,​Перечень найденных значений будем​(Настройки).​ это без перечисления​

​(СИМВОЛ) и примеры​ПРОПНАЧ​

​Примечание:​

​ данном случае —​

• ​ относятся японский, китайский​ лишь по 10​ поля данных имели​ начиная с указанной​ записи с наименованием​ лист».​ символ пишется верху​ данные со знака​ указывать несколько областей,​

​ дрел23 и т.п.​

1. ​ помещать в отдельный​В списке категорий нажмите​ всего алфавита в​ ее использования, а​Преобразует первую букву в​ Маркер версии обозначает версию​
2. ​ «МДС0093»). Функция​ (упрощенное письмо), китайский​ числовых знаков справой​ определенную длину количества​ позиции первого символа;​ и маркой товаров.​О других сочетаниях​ цифры, слова (например,​ «равно» (=), то​ т.е. запись =СТРОКА(A1:A2;B2:B3)​
3. ​Критерий вводится в ячейку​ столбец.​Popular​ уме.​ если у Вас​ каждом слове текста​
4. ​ Excel, в которой​ПОИСК​ (традиционное письмо) и​ стороны номеров. Мы​ символов. Поэтому часто​

exceltable.com

Пример формулы для добавления символов в строке ячейки Excel

​начальная_позиция – обязательный аргумент,​ Разделить имеющиеся строки​ клавиш читайте в​ градус)​ этот знак говорит​ не допустима.​G2​Найдем все названия инструментов,​(Основные). Если Excel​Первым делом, нужно при​ есть, что добавить​ в прописную.​ она впервые появилась.​начинает поиск с​ корейский.​ добавили недостающие символы​ техника заполнения полей​ принимающий целые числа​ на подстроки с​

Автоматическое добавление количества символов в ячейке Excel

​ статье «Горячие клавиши​. Или внизу​ Excel, что вводится​Так как функция СТРОКА()​и выглядит так:​ которые​ 2010, то​ помощи функции​ к инструкции или​ЗАМЕНИТЬ, ЗАМЕНИТЬБ​ В более ранних​ восьмого символа, находит​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ сначала строки необходимого​ данных числовыми значениями​ из диапазона от​ наименованием и маркой​ Excel» тут.​ цифры, числа -​ формула, по которой​ в качестве аргумента​ «?дрель?». В этом​

​начинаются​Advanced​CODE​ примерам – пишите​Заменяют знаки в тексте.​ версиях эта функция​

​ знак, указанный в​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​ нам количества. Так​ требует ввода дополнительных​ 1 до N,​ соответственно и записать​Как установить проверку​Найти в таблице​

​ нужно посчитать. То​

​ позволяет ввести ссылку​ случае будут выведены​с фразы дрел​(Дополнительно).​(КОДСИМВ) выяснить код​ в комментариях.​ПОВТОР​ отсутствует. Например, маркер​ аргументе​Аргументы функций ПОИСК и​ работает функция ПРАВСИМВ,​ нулей, чтобы во​

​ где N –​ полученные значения в​ вводимых данных в​ символ можно, введя​ же самое со​ на диапазон ячеек,​ все значения,​ и​Найдите и нажмите кнопку​

​ первой буквы алфавита​Функция​Повторяет текст заданное число​ версии 2013 означает,​искомый_текст​ ПОИСКБ описаны ниже.​ обратно пропорционально для​ всех значениях было​ длина строки, из​ соответствующие столбцы таблицы.​

​ Excel, смотрите в​ код в строку​ знаками «Сложение» и​ то следует ожидать,​содержащие​длина строки​Edit Custom Lists​ –​CHAR​ раз.​ что данная функция​, в следующей позиции,​Искомый_текст​ предыдущей функции ЛЕВСИМВ.​ одинаковое число символов​ которой требуется извлечь​Вид таблицы данных:​ статье «Проверка данных​

exceltable.com

​ «Код знака».​

Этот учебник рассказывает о главных преимуществах функций ИНДЕКС и ПОИСКПОЗ в Excel, которые делают их более привлекательными по сравнению с ВПР. Вы увидите несколько примеров формул, которые помогут Вам легко справиться со многими сложными задачами, перед которыми функция ВПР бессильна.

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

Зачем нам это? – спросите Вы. Да, потому что ВПР – это не единственная функция поиска в Excel, и её многочисленные ограничения могут помешать Вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ – более гибкие и имеют ряд особенностей, которые делают их более привлекательными, по сравнению с ВПР.

• Базовая информация об ИНДЕКС и ПОИСКПОЗ
• Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
• Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
• ИНДЕКС и ПОИСКПОЗ – примеры формул
  • Как находить значения, которые находятся слева
  • Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
  • Поиск по известным строке и столбцу
  • Поиск по нескольким критериям
  • ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Базовая информация об ИНДЕКС и ПОИСКПОЗ

Так как задача этого учебника – показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в Excel, мы не будем задерживаться на их синтаксисе и применении.

Приведём здесь необходимый минимум для понимания сути, а затем разберём подробно примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

Каждый аргумент имеет очень простое объяснение:

• array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
• row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
• column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.

Вот простейший пример функции INDEX (ИНДЕКС):

=INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

• lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
• lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
• match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
  • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

Теперь, когда Вам известна базовая информация об этих двух функциях, полагаю, что уже становится понятно, как функции ПОИСКПОЗ и ИНДЕКС могут работать вместе. ПОИСКПОЗ определяет относительную позицию искомого значения в заданном диапазоне ячеек, а ИНДЕКС использует это число (или числа) и возвращает результат из соответствующей ячейки.

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

• Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  =INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ, которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС, давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

Любой учебник по ВПР твердит, что эта функция не может смотреть влево. Т.е. если просматриваемый столбец не является крайним левым в диапазоне поиска, то нет шансов получить от ВПР желаемый результат.

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:

  =MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
• Затем соединяем обе части и получаем формулу:

  =INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

Подсказка: Правильным решением будет всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ, чтобы диапазоны поиска не сбились при копировании формулы в другие ячейки.

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.

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

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

Начнём с того, что запишем шаблон формулы. Для этого возьмём уже знакомую нам формулу ИНДЕКС/ПОИСКПОЗ и добавим в неё ещё одну функцию ПОИСКПОЗ, которая будет возвращать номер столбца.

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

Хорошо, давайте запишем формулу. Когда мне нужно создать сложную формулу в Excel с вложенными функциями, то я сначала каждую вложенную записываю отдельно.

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

• ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  =MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:

  =MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

В учебнике по ВПР мы показывали пример формулы с функцией ВПР для поиска по нескольким критериям. Однако, существенным ограничением такого решения была необходимость добавлять вспомогательный столбец. Хорошая новость: формула ИНДЕКС/ПОИСКПОЗ может искать по значениям в двух столбцах, без необходимости создания вспомогательного столбца!

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13);0);3)}

Эта формула сложнее других, которые мы обсуждали ранее, но вооруженные знанием функций ИНДЕКС и ПОИСКПОЗ Вы одолеете ее. Самая сложная часть – это функция ПОИСКПОЗ, думаю, её нужно объяснить первой.

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

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

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

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

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

Например, в ячейке A1 есть длинное предложение, см. Следующий снимок экрана. И теперь вам нужно найти 3-е вхождение или позицию символа «c» из текстовой строки в ячейке A1. Конечно, вы можете посчитать символы по одному и получить точный результат позиции. Однако здесь мы собираемся представить несколько простых советов, как найти n-е вхождение или позицию определенного символа из текстовой строки в ячейке.

Найти n-е вхождение (позицию) символа в ячейке с помощью формулы поиска

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

Следующая формула покажет вам, как найти третье вхождение буквы «c» в ячейку A3.

Найди Формулу 1

В пустой ячейке введите формулу = НАЙТИ («c»; A1; НАЙТИ («c»; A1) +2).

А затем нажмите Enter ключ. Отображается положение третьей буквы «c».

Внимание: Вы можете изменить 2 в формуле в зависимости от ваших потребностей. Например, если вы хотите найти четвертую позицию «c», вы можете изменить 2 на 3. А если вы хотите найти первую позицию «c», вы должны изменить 2 на 0.

Найдите формулу 2

В пустой ячейке введите формулу = НАЙТИ (СИМВОЛ (1); ПОДСТАВИТЬ (A1; «c»; СИМВОЛ (1); 3)), и нажмите Enter .

Внимание: «3» в формуле означает третий «c», вы можете изменить его в зависимости от ваших потребностей.

> Найти n-е вхождение (позицию) символа в ячейке с помощью VBA

Фактически, вы можете применить макрос VB, чтобы легко найти n-е вхождение или позицию определенного символа в одной ячейке.

Шаг 1: Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

Шаг 2: нажмите Вставить > Модулии вставьте следующий макрос в окно модуля.

VBA: найдите n-ю позицию символа.

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function

Шаг 3. Теперь, если вы хотите найти точное совпадение позиции третьей буквы «c» в ячейке A1, введите формулу = FindN («c»; A1,3), и нажмите Enter ключ. Затем он сразу вернет точное положение в конкретной ячейке.

Найдите n-е вхождение (позицию) символа в ячейке с помощью Kutools for Excel

Если вам не нравится ни формула, ни VBA, вы можете попробовать удобный инструмент — Kutools for Excel, С его Формула групп, можно найти утилиту — Найти n-е вхождение символа для быстрого возврата n-й позиции символа в ячейке.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите ячейку, в которую вы хотите вернуть результат, и нажмите Кутулс > Формула Помощник > Формула Помощник . Смотрите скриншот:

2. Затем в всплывающем Формула Помощник диалог, сделайте как показано ниже:

1) Выбрать Поиск из раскрывающегося списка Тип формулы раздел;

2) Выберите Найдите место N-го символа в строке in Выберите формулу раздел;

3) Выберите ячейку, содержащую используемую строку, затем введите указанный символ и n-е вхождение в текстовые поля в Ввод аргументов .

3. Нажмите Ok. И вы получаете позицию n-го вхождения символа в строке.

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

• текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
• количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

С ее помощью определяется длина строки. В качестве результата возвращается целое число, указывающее количество символов текста.

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

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

Пример использования:

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

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

Microsoft Excel

трюки • приёмы • решения

Как работать с символами и кодами в Excel

Каждый символ, вводимый вами и отображаемый на экране, имеет свой собственный числовой код. Например, код для прописной буквы А — это 65, а код для амперсанда (&) — 38. Эти коды присутствуют не только для алфавитных символов, вводимых вами с клавиатуры, но и для специальных символов, которые вы можете вывести на экран, используя их коды. Список всех символов и их кодов называется таблицей кодов ASCII.

Например, ASCII-код для символа © равен 169. Для вывода данного символа необходимо ввести на клавиатуре Alt+0169, используя цифровую клавиатуру для ввода цифр. Таблица ASCII содержит коды от 1 до 255, при этом первые 31 цифра используются для непечатаемых символов, таких как возврат каретки и перевод строки.

Функция СИМВОЛ()

Excel позволяет вам определять символы путем ввода их кодов ASCII, используя функцию СИМВОЛ(число) , где в качестве аргумента необходимо ввести соответствующий код. Например, если вы хотите ввести символ параграфа (§ с кодом ASCII 167), просто введите =СИМВОЛ(167) . Используя функцию СИМВОЛ() , можно сгенерировать таблицу ASCII-символов (см. рис. 4.6). В таблицу не включены первые 31 символ; обратите внимание, что символ с кодом 32 представляет собой знак пробела. В каждой строке символа содержится функция СИМВОЛ() , аргументом которой является число слева от поля.

Политический символ — это знак, выполняющий коммуникативную функцию между личностью и властью. Если исходить из концепции Т. Парсонса, согласно которой культура — это упорядоченная система символов, то можно сказать, что политическая культура — это организованная система символов, подробнее о символах политических партий смотрите на mmk-international.ru. Для того чтобы символ выполнял коммуникативную функцию, он должен иметь сходное значение для множества индивидов, его смысл должен быть, как минимум, интуитивно понятен определенному кругу людей. Кроме коммуникативной, символ обладает интегративной функцией — он способен сплачивать, объединять людей, группы, обеспечивать чувство единства.

Рис. 4.6. Построение таблицы ASCII кодов

Вы можете составить подобную таблицу, просто введя функцию СИМВОЛ() вверху строки и затем заполнив все ноля ниже. В случае если мы начинаем генерацию со второй строки, можно использовать формулу =СИМВОЛ(СТРОКА()+30) .

Функция КОДСИМВ()

Данная функция является противоположностью функции СИМВОЛ(число) . При вводе в нее текстового символа она возвращает код ASCII: КОДСИМВ<текст) , где текст — символ или текстовая строка. В случае ввода строки функция вернет ASCII-код первого символа. Например, следующие формулы вернут 193 как код символа Б: =КОДСИМВ(«Б») или =КОДСИМВ(«Баланс»)

Функции Excel для работы с элементами строк

Следующие функции находят и возвращают части текстовых строк или составляют большие строки из небольших: НАЙТИ (FIND), ПОИСК (SEARCH), ПРАВСИМВ (RIGHT), ЛЕВСИМВ (LEFT), ПСТР (MID), ПОДСТАВИТЬ (SUBSTITUTE), ПОВТОР (REPT), ЗАМЕНИТЬ (REPLACE), СЦЕПИТЬ (CONCATENATE).

Функции НАЙТИ и ПОИСК

Функции НАЙТИ (FIND) и ПОИСК (SEARCH) используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа, с которого начинается первое вхождение искомой строки. Эти две функции работают одинаково за исключением того, что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона. Функции имеют следующий синтаксис:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
=ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

Аргумент искомый_текст задает текстовую строку, которую надо найти, а аргумент просматриваемый_текст — текст, в котором производится поиск. Любым из этих аргументов может быть строка символов, заключенная в двойные кавычки, или ссылка на ячейку. Необязательный аргумент нач_позиция задает позицию в просматриваемом тексте, с которой начинается поиск. Аргумент нач_позиция следует использовать в том случае, когда просматриваемый_текст содержит несколько вхождений искомого текста. Если данный аргумент опущен, Excel возвращает позицию первого вхождения.

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

Например, чтобы определить позицию буквы «ж» в строке «Гаражные ворота», надо использовать формулу:

Эта формула возвращает 5.

Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст символы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует одному произвольно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции. Например, чтобы найти позицию имен Анатолий, Алексей, Акакий в тексте, находящемся в ячейке А1, надо использовать формулу:

Функции ПРАВСИМВ и ЛЕВСИМВ

Функция ПРАВСИМВ (RIGHT) возвращает крайние правые символы строки аргумента, в то время как функция ЛЕВСИМВ (LEFT) возвращает первые (левые) символы. Синтаксис:

=ПРАВСИМВ(текст;количество_символов)
=ЛЕВСИМВ(текст;количество_символов)

Аргумент количество_символов задает число символов, извлекаемых из аргумента текст. Эти функции учитывают пробелы и поэтому, если аргумент текст содержит пробелы в начале или конце строки, в аргументах функций следует использовать функцию СЖПРОБЕЛЫ.

Аргумент количестов_символов должен быть больше или равен нулю. Если этот аргумент опускается, Excel считает его равным 1. Если количество_символов больше числа символов в аргументе текст, то возвращается весь аргумент.

Функция ПСТР

Функция ПСТР (MID) возвращает заданное число символов из строки текста, начиная с указанной позиции. Эта функция имеет следующий синтаксис:

=ПСТР(текст;нач_позиция;количество_символов)

Аргумент текст — это текстовая строка, содержащая извлекаемые символы, нач_позиция — это позиция первого символа, извлекаемого из текста (относительно начала строки), а количество_символов — это число извлекаемых символов.

Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Эти две функции заменяют символы в тексте. Функция ЗАМЕНИТЬ (REPLACE) замещает часть текстовой строки другой текстовой строкой и имеет синтаксис:

=ЗАМЕНИТЬ(старый_текст;нач_позиция;количество_символов;новый_текст)

Аргумент старый_текст — это текстовая строка, а которой надо заменить символы. Следующие два аргумента задают символы, которые нужно заменить (относительно начала строки). Аргумент новый_текст задает вставляемую текстовую строку.

Например, ячейка А2 содержит текст «Вася Иванов». Чтобы поместить этот же текст в ячейку А3, заменив имя, надо в ячейку А3 вставить следующую функцию:

В функции ПОДСТАВИТЬ (SUBSTITUTE) начальная позиция и число заменяемых символов не задаются, а явно указывается замещаемый текст. Функция ПОДСТАВИТЬ имеет следующий синтаксис:

=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

Аргумент номер_вхождения является необязательным. Он предписывает Excel заменить только заданное вхождение строки старый_текст.

Например, ячейка А1 содержит текст «Ноль меньше восьми». Надо заменить слово «ноль» на «нуль».

Число 1 в этой формуле указывает, что надо изменить только первое «о» в строке ячейки А1. Если аргумент номер_вхождения опущен, Excel заменяет все вхождения строки старый_текст на строку новый_текст.

Функция ПОВТОР

Функция ПОВТОР (REPT) позволяет заполнить ячейку строкой символов, повторенной заданное количество раз. Синтаксис:

=ПОВТОР(текст;число_повторений)

Аргумент текст — это размноженная строка символов, заключенная в кавычки. Аргумент число_повторений указывает, сколько раз нужно повторить текст. Если аргумент число_повторений равен 0, функция ПОВТОР оставляет ячейку пустой, а если он не является целым числом, эта функция отбрасывает десятичные знаки после запятой.

Функция СЦЕПИТЬ

Функция СЦЕПИТЬ (CONCATENATE) является эквивалентом текстового оператора & и используется для объединения строк. Синтаксис:

=СЦЕПИТЬ(текст1;текст2;. )

В функции можно использовать до 30 аргументов.

Например, ячейка А5 содержит текст «первое полугодие», следующая формула возвращает текст «Всего за первое полугодие»:

=СЦЕПИТЬ(«Всего за «;А5)

В начало страницы

В начало страницы

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

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

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

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

ШКОЛА ПРОГРАММИРОВАНИЯ

Сообщение об ошибке

Работа со строками в Excel. Текстовые функции Excel

Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню. Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных. В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

• БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
• ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
• ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
• ЗНАЧЕН (Текст) – преобразует текст в число;
• ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
• ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
• НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
• ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
• ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
• СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1
Дан набор строк:

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

Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

= ПСТР (A2; НАЙТИ («№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

= ПРАВСИМВ (A2;8)

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

= ЗНАЧЕН ( ПРАВСИМВ (A2;8))

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

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

= ЗНАЧЕН ( СЦЕПИТЬ («01»; ПРАВСИМВ (A2;6))) или = ЗНАЧЕН («01″& ПРАВСИМВ (A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

Пример 2
В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».

Формула будет следующая:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК, последней будет ПОДСТАВИТЬ.

Все. Если есть вопросы, задавайте, не стесняйтесь

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX). Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС(Диапазон_столбец; Порядковый_номер_ячейки)

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH), которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP):

… но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС(Диапазон; Номер_строки; Номер_столбца)

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

Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:

Вариант 3. Несколько таблиц

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

=ИНДЕКС((Диапазон1;Диапазон2;Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона) 

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

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM), СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

Нечто похожее можно реализовать функцией СМЕЩ (OFFSET), но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA), чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Ссылки по теме

• Трехмерный поиск данных по нескольким листам (ВПР 3D)
• Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
• Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)