Как Найти Адрес Ячейки по Условию в Excel Vba • Функция чстрок

ПОИСКПОЗ в Excel

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

Назначение ПОИСКПОЗ

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

На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.

Правильное написание

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

ПОИСКПОЗ

Итак, финальный вариант формулы выглядит следующим образом:

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

Типы сопоставления

Рассмотрим подробнее разницу между несколькими типами сопоставления:

  • 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
  • 0 – первое значение в диапазоне, которое равно искомому;
  • -1 – наименьшее значение относительно искомого.

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

Ввод команды в Excel

Теперь разберемся, как вводится формула на практике. Первый вариант добавления – это отдельное меню со списком функций:

Ввод команды в Excel

Ссылки и массивы

Поля для ввода данных

Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.

Редактирование формулы

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

Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

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

Простой поиск

Выделение массива

Тип сопоставления

Ввод результата

Смена значения

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

Поиск по датам

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

Результат

Поиск по двум критериям

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

Поиск по двум критериям

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

Простая таблица

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

Поиск нужной ячейки

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Поиск точного значения

Для вычисления позиции необходимо воспользоваться комбинацией [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]Enter[/knopka]. Так вы запустите выполнение функции в массиве.

Выполнение функции

Использование функции ПОИСКПОЗ через массив возможно не только по двум признакам. После указания каждого параметра необходимо ставить знак & и запускать поиск только указанной комбинацией клавиш.

Взаимодействие с другими операторами

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

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

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

ИНДЕКС

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

Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:

ПОИСКПОЗ по маркам

Жмем на [knopka]Enter[/knopka] и видим результат в соответствующей ячейке. Проверяем по нашей таблице.

Правильный результат

Теперь можно менять значения в нашей таблице справа и получать новые значения:

Подмена значений

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

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

Видеоинструкция

Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.

Заключение

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

[expert_bq id=»1570″]- Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА — Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:
Как Найти Адрес Ячейки по Условию в Excel • Адрес закольцовки

Работа с объектом Range — Макросы и программы VBA — Excel — Каталог статей — Perfect Excel

  • 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
  • 0 – первое значение в диапазоне, которое равно искомому;
  • -1 – наименьшее значение относительно искомого.

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

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: