Сколько символов в тексте word, excel, google docs (2020)

Попробуйте попрактиковаться

Ниже приведены некоторые примеры того, как работает функция ДЛСТР.

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

Же еще этих мягких французских булок.

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

Подсчет символов в одной ячейке

Щелкните ячейку B2.

Введите формулу =ДЛСТР(a2).

Формула подсчитывает символы в ячейке A2, 27 «Итого», которая содержит все пробелы и период в конце предложения.

Примечание: ДЛСТР подсчитывает пробелы после последнего символа.

Подсчет символов в нескольких ячейках

Щелкните ячейку B2.

Клавиши Ctrl + C, чтобы скопировать ячейку B2, а затем выделите ячейки B3 и B4 и нажмите клавиши Ctrl + V, чтобы вставить формулу в ячейки B3: B4.

Эта формула будет скопирована в ячейки B3 и B4, а функция подсчитает символы в каждой ячейке (20 27 и 45).

Подсчет общего количества символов

В книге с примерами щелкните ячейку В6.

Введите в ячейке формулу =СУММ(ДЛСТР(A2);ДЛСТР(A3);ДЛСТР(A4)) и нажмите клавишу ВВОД.

Так подсчитывается количество символов в каждой из трех ячеек, а суммируются в (92).

Как зафиксировать ячейку в формуле в таблицах Excel – вариант №1

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

В результате ссылка будет зафиксирована с помощью $ (знака доллара). Например, если у вас в формуле было значение С3, то после того, как вы проведете вышеописанную процедуру, ссылка обязана стать такого формата — $С$3.

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

Количество знаков в столбце или строке

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

Чтобы растянуть результат на другие строки столбца наводим указатель мыши на правый нижний угол ячейки с функцией “ДЛСТР”, как только он сменит вид на небольшой плюсик, зажав левую кнопку мыши тянем его вниз до той строки, для которой мы хотим получить аналогичный результат.
Как мы можем заметить, благодаря этому нехитрому действию нам удалось в считанные секунды получить данные по количеству символов для каждой ячейки выбранного столбца.

Ранее мы упоминали, что в качестве аргумента функции “ДЛСТР” можно указать только адрес одной конкретной ячейки. Поэтому, чтобы определить суммарное количество знаков во всех ячейках столбца нужно воспользоваться одним из способов подсчета суммы значений. Подробнее ознакомиться со всеми методами можно в нашей статье – “Как посчитать в Экселе сумму столбца”.

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

Выделяем ячейки столбца. Далее нажимаем кнопку “Сумма” в разделе инструментов “Редактирование” (вкладка “Главная”).
Результат в виде суммарного количества символов во всех ячейках столбца отобразится в ячейке сразу под выделенным диапазоном.

Работа со строками в 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 «);» «;»_»)

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

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

Как посчитать вхождения текста или подстроки в ячейку?

Если вы хотите подсчитать, сколько раз определенная комбинация символов (например, определенный текст или подстрока) появляется в данной ячейке (например «A2» или «М5»), разделите количество определенных символов, возвращаемых приведенными выше формулами, на длину подстроки.

С учетом регистра:

Без учета регистра:

Где B2 — запись, содержащая всю текстовую строку, а C1 – тот текст (подстроку), который вы хотите подсчитать.

Как эта формула подсчитывает количество вхождений определенного текста в ячейку?

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

Результатом этой операции является количество символов, содержащихся во всех вхождениях целевого слова, которое в этом примере равно 4 (2 вхождения «A2», по 2 в каждом).

Наконец, указанное выше число делится на длину искомого текста. Другими словами, вы делите количество символов, содержащихся во всех вхождениях целевого слова, на число знаков, содержащихся в этом слове. В этом примере 4 делится на 2, и в результате мы получаем 2.

Как в ворде в таблице посчитать сумму чисел в столбце или строке

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

Расчет суммы в ячейках таблицы

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

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

Появится диалоговое окно вставки формулы, в котором уже будет прописана формула вычисления суммы в столбце «=SUM(ABOVE)».

Окно вставки формулы суммы в word

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

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

Иначе подсчет будет выполнен до первой пустой ячейки таблицы

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

Вычисления суммы в горизонтальных ячейках (столбец Всего) проводится аналогично, но формулу необходимо изменить в диалоговом окне. Для подсчета суммы чисел слева от ячейки с результатом необходимо написать формулу «=SUM(LEFT)». Если числа расположены справа – «=SUM(RIGHT)»

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

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

Обновление результатов расчета по формулам

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

вычисленные результаты некоторых формул;
результаты в выбранной таблице.

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

Обновление результатов всех формул в таблице Выделите всю таблицу, содержащую результаты формул, которые необходимо обновить, и нажмите клавишу F9.

Кстати, кроме суммы можно вычислить произведение, среднее значение и количество числовых значений. Для этого используем соответствующие функции =PRODUCT(LEFT), =AVERAGE(RIGHT), =COUNT(LEFT). В скобках в качестве параметра указано направление расчета относительно ячейки с формулой. Этот параметр можно изменить, так же, как и у функции вычисления суммы смотрите рисунок выше.

Текстовый процессор в первую очередь предназначен для написания текстов, поэтому если в таблице требуется проводить большие объемы вычислений, то необходимо использовать программу Microsoft Excel или подобную ей, например, Calc из бесплатного пакета LibreOffice.

Как быстро сложить столбец

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

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

Теперь вычислить сумму в столбце или строке будет проще простого. Установите курсор внизу столбца под числами и нажмите кнопку Сумма. Теперь она есть на панели быстрого доступа. Результат появиться сразу.

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

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

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

Дополнительная информация:

Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в х пару слов.Если ответа не нашли, укажите что искали.

Подсчет определенных букв в диапазоне без учета регистра.

Вы уже знаете, что ПОДСТАВИТЬ — функция, чувствительная к регистру. Поэтому наша формула для подсчета также чувствительна к регистру.

Чтобы игнорировать регистр, следуйте подходам, продемонстрированным в предыдущем примере.

Используйте функции ПРОПИСН и СТРОЧН, введите прописную либо заглавную букву и укажите ссылку на нее:

На скриншоте ниже показана последняя формула в действии:

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

Чтобы подсчитать, сколько раз сочетание «А2» в точном виде встречается в наших данных, запишем выражение:

Если не нужно учитывать регистр букв, то тогда —

То есть, мы берем формулы, использованные нами для отдельной ячейки, меняем в них ссылку на диапазон данных и обрабатываем затем при помощи СУММПРОИЗВ.

Вы можете использовать функцию СУММ, но в формуле массива, как мы также уже рассматривали ранее.

Вот как вы можете подсчитывать символы в Excel с помощью функции ДЛСТР. Если вы хотите знать, как считать слова, а не отдельные знаки, вы найдете несколько полезных формул в нашей следующей статье, следите за обновлениями!

Возможно, вам будут также полезны:

Функция СЧЁТЕСЛИ

В этом курсе:

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

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

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Подсчет количества определенных символов в ячейке excel

Функция =СЧЁТСИМВЛ(ТЕКСТ, СИМВОЛ) имеет два обязательных аргумента:

ТЕКСТ — Строка, в которой необходимо посчитать количество символов.
ШАБЛОН — Символ, который необходимо посчитать.

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

Теперь к практике. Далее привожу множество примеров использования данной функции.

Пример 6

Посчитать количество всех символов в строке за исключением пробелов.

Excel позволяет пользователям достичь правильного решения разными путями. Данная формула не исключение. Заменить данную функцию можно с помощью двух других: ДЛСТР и ПОДСТАВИТЬ. Чтобы определить количество заданного символа сначала определите длину всего текста функцией ДЛСТР, а затем отнимите от этого значения длину этого же текста, только с предварительно удаленными символами (которые вы ищите): =ДЛСТР(B13)-ДЛСТР(ПОДСТАВИТЬ(B13;» «;»»)). В файле для скачивания приведены конкретные примеры.

Условное форматирование по части текста в ячейке Excel

У нас имеется данные для анализа производимой продукции. Нам нужно автоматически выделить все изделия, которые были выпущены в 2006 году. К сожалению, в таблице недостает еще одного столбца содержащего значения года производства каждого изделия. Но видно, что в фрагменте идентификатора (ID) изделия указывается год производства. В таком случае нам нужно выполнить поиск по части текста в Excel. А потом мы будем использовать условное форматирование с текстовыми функциями в формуле. Благодаря этому молниеносно реализуем решение задачи.

Пример таблицы производимой продукции:

Чтобы на основе идентификатора выделить изделия 2006-го года выпуска выполним шаги следующих действий:

Выделите диапазон ячеек A2:A10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
Выберите: «Использовать формулу для определения форматируемых ячеек».
Чтобы выполнить поиск части текста в ячейке Excel, введите формулу: =ПСТР(A2;5;4)=»2006″
Нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.

Экспонированные цветом изделия 2006-го года выпуска:

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

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

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

Но как из ячейки извлечь часть текста в Excel? Обратим внимание на функцию =ПСТР() в формуле правила. Данная функция возвращает часть строки, взятой с каждой ячейки столбца A, что указано в первом аргументе

Ссылка в первом аргументе должна быть относительной, так как формула применяется к каждой ячейке столбца A. Во втором аргументе функции указывается номер символа исходного текста, с которого должен начаться отрезок строки. В третьем аргументе указывается количество символов, которые нужно взять после определенного (во втором аргументе) символа исходного текста. В результате функция =ПСТР() возвращает только часть текста длинной 4 символа взятого начиная с 5-ой буквы в каждой ячейки из столбца А. Это «2005» или «2006». После функции стоит оператор сравнения к значению строки «2006». Если ячейка содержит такую часть текста значит ей будет присвоен новый формат.

Аналогичным способом можно использовать и другие текстовые функции в условном форматировании. Например, с помощью функции =ПРАВСИМВ() мы можем экспонировать цветом определенную группу товаров из партии C. Для этого нужно использовать формулу:

Здесь все просто функция позволяет выбрать часть текста из ячейки Excel, начиная с правой стороны исходного текста. Количество необходимых символов указано во втором аргументе функции ПРАВСИМВ. После чего все что возвращает функция сравнивается с значением строки «C».

Во всех выше описанных примерах очень легко применять текстовые функции в условном форматировании так как длина строк в исходных данных одинаковая. Но что, если у нас в исходных данных разного типа индикаторы с разной длинной символов, а нам все еще нужно выделять 2006-й год или группу «C».

В решении данной задачи нам поможет дополнительная текстовая функция в формуле =НАЙТИ(). В первом случаи формула будет выглядеть так:

Для удобного выделения идентификаторов с разной длинной текстовой строки товаров из группы «C» используем такую формулу:

Функция =НАЙТИ() выполняет поиск фрагмента текста в ячейке Excel. Потом возвращает номер символа, в котором был найдет текст, который задан в первом аргументе. Во втором аргументе указываем где искать текст. А третий аргумент – это номер позиции с какого символа вести поиск в исходном тексте. Третий аргумент позволяет нам смещаться по строке. Например, если в идентификаторе 2 раза используется символ «C». В таком случае третий аргумент пользователь задает в зависимости от ситуации.

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

Как вставить формулу с дробью

Разберем пошаговое создание формулы, содержащей простую дробь.

Вставляем уравнение (смотрите скрин выше), а точнее поле для ввода новой формулы. Далее все этапы отображены на нижнем рисунке.

Вкладка Работа с уравнениями. Режим Конструктор. Выбираем шаблон простой дроби в секции Дробь. Получаем шаблон.
Кликаем в пунктирную область и вводим числитель дроби.
Кликаем в знаменатель и выбираем шаблон скобок в секции Скобка.
Затем вписываем содержимое знаменателя.
Выделяем знаменатель и в шаблонах индексов выбираем Верхний индекс. Указываем степень знаменателя.
Кликаем позади дроби и делаем несколько интервалов. Снова идем в шаблоны скобок. Выбираем круглые скобки.
Вписываем содержимое. Знаки сравнений и принадлежности берем в группе Символы на панели инструментов Конструктор.

Замена текста ячейки надписью

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

Дважды щелкните необходимую ячейку, выделите весь текст и нажмите клавиши CTRL+X.

Щелкните другую ячейку, чтобы отменить выделение.

При необходимости измените высоту строки.

На вкладке Вставить щелкните Надпись.

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

Щелкните надпись, чтобы выделить ее, а затем нажмите клавиши CTRL+V для вставки скопированного текста.

Выделите весь текст надписи, щелкните его правой кнопкой мыши и в контекстном меню выберите пункт Абзац.

Выберите подходящие междустрочные интервалы.

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

Примечание: Надпись располагается не внутри ячейки, а поверх нее. Если строка с надписью переместится из-за изменений, внесенных на листе выше, сама надпись останется на прежнем месте. Чтобы переместить надпись, наведите указатель на ее край, щелкните и, удерживая кнопку мыши, перетащите ее на новое место.

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

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

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

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

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

INDEX(array,row_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,)ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

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

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