& Quot; СООТВЕТСТВИЕ & quot; в Excel

& Quot; СООТВЕТСТВИЕ & quot; в Excel

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

Использование оператора MAP

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

Синтаксис оператора MATCH выглядит следующим образом:

= ПОИСК (search_value; view_array; [mapping_type])

Теперь рассмотрим каждый из этих трех аргументов отдельно.

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

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

"Тип соответствия " показывает точное совпадение, которое вы хотите найти, или является неверным. Этот аргумент может иметь три значения: "1" ,, "0" а также "-1" . Если значение равно "0", оператор ищет только точное совпадение. Если значение "1" указано, при отсутствии точного совпадения MATCH производит ближайший к нему элемент в порядке убывания. Если значение равно "-1" , то в случае, если точное совпадение не найдено, функция производит ближайший к нему элемент в порядке возрастания. Это важно, если поиск не точный, а приблизительный, чтобы сканируемый массив располагался в порядке возрастания (тип сопоставления "1" ) или по убыванию (тип отображения "-1" ).

Аргумент "Соответствующий тип" не является обязательным. Если не требуется, это можно пропустить. В этом случае его значение по умолчанию "1" . Применение аргумента "Тип соответствия" это имеет смысл только при обработке цифровых значений, но не текста.

Если ПОИСКПОЗ не может найти нужный элемент для заданных настроек, оператор отображает ошибку "# N / A" в клетке.

При выполнении поиска оператор не различает регистры символов. Если в массиве есть несколько точных совпадений, то MATCHING показывает позицию первого в ячейке.

Метод 1. Отображение местоположения элемента в диапазоне текстовых данных

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

  1. Выберите ячейку, в которой будет отображаться обработанный результат. Щелкните значок "Вставить функцию" в строку формул.
  2. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  3. Мастер функций запускается. Откройте категорию "Полный алфавитный список" или "Ссылки и массивы" . В списке операторов ищем название "СООТВЕТСТВИЕ" . Найдя и выбрав его, нажмите кнопку "в порядке" внизу окна.
  4. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  5. Окно «Аргументы оператора MAP» включено. Как видите, в этом окне есть три поля в зависимости от количества аргументов. Мы должны их заполнить.

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

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

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

    После того, как вы установили все данные, нажмите кнопку "в порядке" .

  6. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  7. Программа рассчитывает и отображает порядковый номер позиции "Сахар" в выбранном массиве в ячейке, которую мы определили на первом шаге этой инструкции. Номер позиции будет "4" .
Читать также:  Почтовые клиенты для Android

Метод 2: автоматизировать использование оператора MATCH

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

  1. Для удобства листа добавьте два дополнительных поля: "Конкретное значение" а также "Число" . В поле " Присвоенное значение" мы вбиваем имя, которое нужно найти. Теперь пусть будет "Мясо" . В поле "Число" поместите курсор и перейдите в окно аргументов оператора так же, как описано выше.
  2. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  3. В окне для аргументов функции в поле "Значение поиска" указываем адрес ячейки, где написано слово "мясо" . В полях "Сканируемый массив" а также "Тип отображения" указываем те же данные, что и в предыдущем способе — соответственно адрес диапазона и номер "0" . Затем нажмите кнопку "в порядке" .
  4. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  5. После выполнения вышеуказанных действий поле "Число" показывает положение слова "Мясо" в выбранном диапазоне. В этом случае он равен "3" .
  6. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  7. Этот метод хорош тем, что, если мы хотим узнать позицию любого другого имени, нам не придется каждый раз повторно вводить или изменять формулу. В поле "Конкретное значение" довольно легко ввести новое слово для поиска вместо предыдущего. После этого обработка и вывод результата будут выполнены автоматически.
Читать также:  Загрузите последнюю версию Diskeeper

Метод 3: используйте оператор MAP для числовых выражений

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

Задача найти в продаже товар на сумму 400 рублей или максимально близкую к этой сумме в порядке возрастания.

  1. Сначала нам нужно отсортировать элементы в столбце "Количество" в порядке убывания. Выберите этот столбец и перейдите на вкладку "начало" . Щелкните значок "Сортировка и фильтрация" , который находится на баре в блоке "Редактирование" . В появившемся списке выберите "Сортировать от максимума к минимуму" .
  2. & Quot; СООТВЕТСТВИЕ & quot; в Excel

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

    В поле "Значение поиска" мы ведем номер "400" . В поле "Сканируемый массив" укажите координаты колонны "Количество" . В поле "Тип соответствия" установить значение "-1" потому что мы ищем значение, равное или большее, чем то, которое мы ищем. После завершения всех настроек нажмите на кнопку "в порядке" .

  4. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  5. Результат обработки отображается в заданной ячейке. Это позиция "3" . Она отвечает на "Картофель" . Фактически сумма выручки от продажи данного товара приближается к 400 по возрастанию и составляет 450 рублей.

Метод 4: использовать с другими операторами

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

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

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

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

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

  1. Отсортируйте элементы в столбце "Стоимость дохода" в порядке возрастания. Для этого в разделе выберите нужный столбец. "начало" нажмите на значок "Сортировка и фильтрация" а затем щелкните элемент "Сортировать от минимума до максимума" в показанном меню.
  2. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  3. Выберите ячейку в поле "Товар" и вызовите помощник по функциям обычным образом с помощью кнопки "Вставить функцию" .
  4. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  5. В открытом окне мастера функций в категории "Ссылки и массивы" мы ищем имя "ИНДЕКС" , выберите его и нажмите кнопку "в порядке" .
  6. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  7. Затем открывается окно, в котором предлагается выбрать вариант ИНДЕКС: для массива или для ссылки. Нам нужен первый вариант. Поэтому в этом окне оставляем все настройки по умолчанию и нажимаем кнопку "в порядке" .
  8. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  9. Открывает окно аргументов функции ИНДЕКС. В поле "Массив" укажите адрес диапазона, в котором оператор INDEX будет искать название продукта. В нашем случае это столбец "Название продукта" .

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

    Третий аргумент функции ИНДЕКС "Номер столбца" остается пустым. Затем нажмите кнопку "в порядке" .

  10. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  11. Как видите, функция ИНДЕКС, использующая оператор POSITION в выбранной ячейке, отображает имя "Чай" . Фактически, сумма продажи чая (300 руб.) Наиболее близка по убыванию к сумме 350 руб. Всех значений в обработанной массе.
  12. & Quot; СООТВЕТСТВИЕ & quot; в Excel

  13. Если мы изменим число в поле "Примерная сумма выручки" с другой стороны, содержимое поля "Товар" будет пересчитан автоматически.
Читать также:  Ошибка "Некоторые параметры были установлены системным администратором" в Windows 7

& Quot; СООТВЕТСТВИЕ & quot; в Excel

Урок: Функция ИНДЕКС в Excel

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?