Bazaprogram.ru

Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция match в excel на русском

ПОИСКПОЗ (функция ПОИСКПОЗ)

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

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.

Синтаксис

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

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

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

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

Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

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

Функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА.

Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.

Функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z — A, . 2, 1, 0, -1, -2, . и т. д.

Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ(«б»; <"а»;»б»;»в «>;0) возвращает 2 — относительную позицию буквы «б» в массиве <"а";"б";"в">.

Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.

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

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

Пример

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

5 вариантов использования функции ИНДЕКС (INDEX)

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

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

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

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

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

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

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

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

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

Читать еще:  Формула рабдень в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

30 функций Excel за 30 дней: ПОИСКПОЗ (MATCH)

Вчера в марафоне 30 функций Excel за 30 дней мы находили текстовые строки при помощи функции SEARCH (ПОИСК), а также использовали IFERROR (ЕСЛИОШИБКА) и ISNUMBER (ЕЧИСЛО) в ситуациях, когда функция выдаёт ошибку.

В 19-й день нашего марафона мы займёмся изучением функции MATCH (ПОИСКПОЗ). Она ищет значение в массиве и, если значение найдено, возвращает его позицию.

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

Функция 19: MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) возвращает позицию значения в массиве или ошибку #N/A (#Н/Д), если оно не найдено. Массив может быть, как сортированный, так и не сортированный. Функция MATCH (ПОИСКПОЗ) не чувствительна к регистру.

Как можно использовать функцию MATCH (ПОИСКПОЗ)?

Функция MATCH (ПОИСКПОЗ) возвращает позицию элемента в массиве, и этот результат может быть использован другими функциями, такими как INDEX (ИНДЕКС) или VLOOKUP (ВПР). Например:

  • Найти положение элемента в несортированном списке.
  • Использовать вместе с CHOOSE (ВЫБОР), чтобы перевести успеваемость учащихся в буквенную систему оценок.
  • Использовать вместе с VLOOKUP (ВПР) для гибкого выбора столбца.
  • Использовать вместе с INDEX (ИНДЕКС), чтобы найти ближайшее значение.

Синтаксис MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) имеет следующий синтаксис:

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

  • lookup_value (искомое_значение) – может быть текстом, числом или логическим значением.
  • lookup_array (просматриваемый_массив) – массив или ссылка на массив (смежные ячейки в одном столбце или в одной строке).
  • match_type (тип_сопоставления) – может принимать три значения: -1, или 1. Если аргумент пропущен, это равносильно 1.

Ловушки MATCH (ПОИСКПОЗ)

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

Пример 1: Находим элемент в несортированном списке

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

Читать еще:  Mid excel русский

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

В качестве аргумента lookup_array (просматриваемый_массив) можно использовать массив констант. В следующем примере искомый месяц введен в ячейку D5, а названия месяцев подставлены в качестве второго аргумента функции MATCH (ПОИСКПОЗ) в виде массива констант. Если в ячейке D5 ввести более поздний месяц, например, Oct (октябрь), то результатом функции будет #N/A (#Н/Д).

Пример 2: Изменяем оценки учащихся c процентов на буквы

Вы можете преобразовать оценки учащихся в буквенную систему, используя функцию MATCH (ПОИСКПОЗ) так же, как Вы делали это с VLOOKUP (ВПР). В этом примере функция использована в сочетании с CHOOSE (ВЫБОР), которая и возвращает нужную нам оценку. Аргумент match_type (тип_сопоставления) принимаем равным -1, поскольку баллы в таблице отсортированы в порядке убывания.

Когда аргумент match_type (тип_сопоставления) равен -1, результатом будет наименьшее значение, которое больше искомого или эквивалентное ему. В нашем примере искомое значение равно 54. Поскольку такого значения нет в списке баллов, то возвращается элемент, соответствующий значению 60. Так как 60 стоит на четвёртом месте списка, то результатом функции CHOOSE (ВЫБОР) будет значение, которое находится на 4-й позиции, т.е. ячейка C6, в которой находится оценка D.

Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)

Чтобы придать больше гибкости функции VLOOKUP (ВПР), Вы можете использовать MATCH (ПОИСКПОЗ) для поиска номера столбца, а не жестко вписывать его значение в функцию. В следующем примере пользователи могут выбрать регион в ячейке H1, это искомое значение для VLOOKUP (ВПР). Далее, они могут выбрать месяц в ячейке H2, и функция MATCH (ПОИСКПОЗ) возвратит номер столбца, соответствующий этому месяцу.

Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)

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

  1. Функция ABS возвращает модуль разницы между каждым угаданным и правильным числами.
  2. Функция MIN (МИН) находит наименьшую из разниц.
  3. Функция MATCH (ПОИСКПОЗ) находит адрес наименьшей разницы в списке разниц. Если в списке есть несколько совпадающих значений, то возвращено будет первое.
  4. Функция INDEX (ИНДЕКС) возвращает имя, соответствующее этой позиции, из списка имен.

Функции Excel

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

В следующей статье представлен список наиболее важных и полезных функций Excel.

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

Математические функции

ФункцияОписание
СУММ (число1; число2; …)

SUM

Вычисление суммы чисел в заданных ячейках.ПРОИЗВЕД (число1; число2; …)

PRODUCT

Вычисление произведения чисел в заданных ячейках.КОРЕНЬ (число)

SQRT

Вычисление квадратного корня из неотрицательного числа.СТЕПЕНЬ (число; степень)

POWER

Возведение числа в указанную степень.ABS (число)

ABS

Вычисление модуля числа.ОКРУГЛ (число; число разрядов)

ROUND

Округление числа до указанного числа разрядов.ОКРУГЛВВЕРХ (число; число разрядов)

ROUNDUP

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

ROUNDDOWN

Округление числа в меньшую по модулю сторону до указанного числа разрядов.ЦЕЛОЕ (число)

INT

Округление числа до ближайшего меньшего целого.ОСТАТ (число; делитель)

MOD

Остаток от деления.СУММЕСЛИ (диапазон; критерий; диапазон суммирования)

SUMIF

Вычисление суммы значений ячеек, удовлетворяющих заданному критерию. Если диапазон суммирования не задан, – суммируются значения, указанные в поле «диапазон».

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

ФункцияОписание
МАКС (число1; число2; …)

MAX

Нахождение наибольшего значения среди чисел в заданных ячейках.МИН (число1; число2; …)

MIN

Нахождение наименьшего значения среди чисел в заданных ячейках.СРЗНАЧ (число1; число2; …)

AVERAGE

Нахождение среднего арифметического чисел в заданных ячейках.СЧЕТ (значение1; значение2; …)

COUNT

Подсчет количества чисел в заданных ячейках.СЧЕТЗ (значение1; значение2; …)

COUNTA

Подсчет количества заполненных ячеек в указанном диапазоне.СЧЕТЕСЛИ (диапазон; критерий)

COUNTIF

Подсчет количества ячеек, удовлетворяющих заданному критерию.

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

ФункцияОписание
СЦЕПИТЬ (строка1; строка2; …)

CONCATENATE

Соединение нескольких текстовых строк в одну.ЛЕВСИМВ (строка; количество символов)

LEFT

Отделяет указанное количество символов с начала строки.ПРАВСИМВ (строка; количество символов)

RIGHT

Отделяет указанное количество символов с конца строки.ДЛСТР (строка)

LEN

Подсчитывает количество символов в текстовой строке.ПРОПИСН (строка)

UPPER

Преобразует символы строки в прописные (заглавные).ПРОПНАЧ (строка)

PROPER

Преобразует первую букву строки в прописную (заглавную), а остальные символы – в строчные.СТРОЧН (строка)

LOWER

Преобразует символы строки в строчные.ПОИСК (подстрока; строка;
начальная позиция)

SEARCH

Ищет позицию первого вхождения подстроки в строку, начиная с указанного символа без учета регистра.НАЙТИ (подстрока; строка;
начальная позиция)

FIND

Ищет позицию первого вхождения подстроки в строку, начиная с указанного символа с учетом регистра.ПСТР (строка; начальная позиция; количество символов)

MID

Выделяет из строки подстроку, состоящую из заданного числа символов, начиная с указанной позиции.ПОДСТАВИТЬ (строка; подстрока 1; подстрока 2; номер вхождения)

SUBSTITUTE

Заменяет в строке указанное вхождение подстроки 1 на подстроку 2.ЗАМЕНИТЬ (строка; начальная позиция; количество символов; подстрока)

REPLACE

Заменяет в строке указанное количество символов, начиная с указанной позиции на подстроку.СЖПРОБЕЛЫ (строка)

TRIM

Удаляет из строки лишние пробелы.КОДСИМВ (строка)

CODE

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

Логические функции

ФункцияОписание
ЕСЛИ (условие; значение1; значение2)

IF

В зависимости от условия вычисляется:
значение1, если условие истинно
значение2, если условие ложно.И (условие1; условие2; …)

AND

В зависимости от истинности условий:
ИСТИНА, если все условия истинны
ЛОЖЬ, если хотя бы одно из условий ложно.ИЛИ (условие1; условие2; …)

OR

В зависимости от истинности условий:
ИСТИНА, если все условия истинны
ЛОЖЬ, если хотя бы одно из условий ложно.ЕСЛИОШИБКА (выражение; значение если ошибка)

IFERROR

Если в результате вычисления значения выражения получается ОШИБКА, то выдает значение если ошибка, иначе вычисляется значение выражения.

Функции даты и времени

ФункцияОписание
СЕГОДНЯ ()

TODAY

Текущая дата.МЕСЯЦ (дата)

MONTH

Показывает номер месяца указанной даты.ГОД (дата)

YEAR

Показывает год указанной даты.ДЕНЬНЕД (дата; тип)

WEEKDAY

Показывает номер дня недели по указанной дате.
Тип – вариант нумерации дней:
1 – начинается с воскресенья (Вс = 1);
2 – начинается с понедельника (Пн = 1);
3 – начинается с воскресенья (Вс = 0).

Функции категории ссылки и массивы

ФункцияОписание
ВПР (искомое значение; таблица; порядковый номер столбца; интервальный просмотр)

VLOOKUP

Ищет указанное значение в первом столбце таблицы и выдает значение из указанного столбца той же строки таблицы.ГПР (искомое значение; таблица; порядковый номер строки; интервальный просмотр)

HLOOKUP

Ищет указанное значение в первой строке таблицы и выдает значение из указанной строки того же столбца таблицы.ПРОСМОТР (значение; вектор поиска; вектор результата)

LOOKUP

Ищет указанное значение в столбце таблицы (вектор поиска) и выдает значение, расположенное в той же строке в другом столбце таблицы (вектор результата).СТРОКА (ссылка)

ROW

Определяет номер строки, указанной ссылкой.СТОЛБЕЦ (ссылка)

COLUMN

Определяет номер столбца, указанного ссылкой.ИНДЕКС (таблица; номер строки; номер столбца)

INDEX

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

MATCH

Определяет позицию искомого значения в указанном диапазоне.

Функции категории проверка свойств и значений

ФункцияОписание
ЕОШИБКА (значение)

ISERROR

Выдает логическое значение ИСТИНА, если в ячейке в результате вычислений получено значение Ошибка.ЕНД (значение)

ISNA

Выдает логическое значение ИСТИНА, если значение в ячейке #Н/Д (#N/A).

Хотите узнать какие функции есть в Excel и научиться правильно применять их в своей работе? Приходите на Тренинг Excel для продвинутых пользователей.

Ссылка на основную публикацию
Adblock
detector