ifempty

    Урок 3. Сочетание функций ИНДЕКС и ПОИСКПОЗ (продвинутый ВПР) (2)

    Введение

    Функции ИНДЕКС  и ПОИСКПОЗ это функции для работы с массивами.

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

    Функция ИНДЕКС

    Скачайте файл Функция ИНДЕКС - пример по ссылке выше. Внутри даны три таблицы. Таблица1 - содержит список городов. В таблице2 с помощью функции ИНДЕКС  в ячейку F3 автоматичсеки подставляется город в случае когда вы меняете номер строки и номер столбца (см. рисунок 1):

     индекс и поискпоз рис1

    Рисунок 1. Пример работы функции ИНДЕКС

    Обратите внимание, что Номер столбца всегда остается -  2. Так как города в Таблице1 находятся во втором столбце. Если в Таблице2 в графе Номер столбца поставить 1, то вместо города функция будет возвращать номер по порядку, который находится в первой графе таблицы 1 (см. рисунок 2). 

     индекс и поискпоз рис2

    Рисунок 2. Пример работы функции ИНДЕКС

    Вызов функции ИНДЕКС

     Очистите ячейку F3 и вызовите функцию ИНДЕКС: вкладка Формулы, Ссылки и массивы - Индекс. 

    Далее выберите перый список аргументов, как показано на риснуке 3:

     индекс и поискпоз рис3

    Рисунок 3. Выбор списка аргументов функции ИНДЕКС

    Далее нужно заполнить аргументы функции, как показано на рисунке 4:

     индекс и поискпоз рис4

    Рисунок 4. Заполнение аргументов функции ИНДЕКС

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

     индекс и поискпоз рис5

    Рисунок 5. Окно аргументов функции ИНДЕКС без указания Номера столбца

    При измененении Номера строки меняется и значение города:

     индекс и поискпоз рис6

    Рисунок 6. Пример работы функции ИНДЕКС

    Функция ПОИСКПОЗ

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

     индекс и поискпоз рис7

    Рисунок 7. Пример использования функции ПОИСКПОЗ

    Вызов функции ПОИСКПОЗ

    Стерите данные в ячейке Е3  Таблицы 2 и вызовите в нее функцию ПОИСКПОЗ  - вкладка Формулы - Ссылки и массивы - ПОИСКПОЗ.

    Заполните аргументы функции, как показано на рисунке 8:

     индекс и поискпоз рис8

    Рисунок 8. Заполнение аргументов функции ПОИСКПОЗ для строк

    После заполнени аргументов нажмите ОК

    Теперь также вызовем функцию ПОИСКПОЗ  в Таблице4. Для этого очистите ячейку Е17  вызовите в нее функцию. Аргументы функции заполните как показано на рисунке 9:

     индекс и поискпоз рис9

    Рисунок 9. Заполнение аргументов функции ПОИСКПОЗ для столбцов

     После заполнения аргументов нажмите на кнопку ОК.

    Обратите внимание, что поле Просматриваемые массив - это либо строка (как это было в таблице 2), либо столбец (как это было в таблице4).

    Сочетание функций ИНДЕКС и ПОИСКПОЗ

    Исходя из предыдущих примеров можно сделать выводы:

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

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

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

     На листе даны две таблицы Таблица 1 и Таблица 2. В Таблице 1 находится список сотрудников, в Таблице 2 даны табелльные номера но не заполнены прочие графы. 

    Задача:

    Нужно заполнить Таблицу2 данными из Таблицы1.

    Решение задачи:

    Становимся в ячейку С19 и вызываем функцию ИНДЕКС, при этом выбираем первый список аргументов (см. рисунок 10):

     индекс и поискпоз рис10

    Рисунок 10. Вызов функции ИНДЕКС

    Далее нужно заполнить аргументы функции ИНДЕКС. В качестве массива нужно полностью выбрать Таблицу 1. Внутрь полей Номер строки и Номер столбца нужно положить функции ПОИСКПОЗ (см. рисунок 11):

      индекс и поискпоз рис11

    Рисунок 11. Вложение функции ПОИСКПОЗ внутрь функции ИНДЕКС

    На рисунке 12 показано окно аргументов функции ИНДЕКС с вложеными функцями ПОИСКПОЗ:

     индекс и поискпоз рис12

    Рисунок 12. Аргументы функции ИНДЕКС с вложенными функциями ПОИСКПОЗ

    В отдельности аргументы функции ПОИСКПОЗ представлены на рисунках 13 и 14:

     индекс и поискпоз рис13

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

     индекс и поискпоз рис14

    Рисунок 14. Аргументы функции ПОИСКПОЗ для поиска номера столбца

    Обратите внимание, что в функциях ПОИСКПОЗ в поле Искомое значение находится смешанная ссылка. Для поиска номера строки  - смешанная ссылка с фиксацией столбца $B19 (рисунок 13). Для поиска номера столбца  - смешанная ссылка с фиксацией строки С$18 (рисунок 14).

    все массивы (диапазоны) зафиксированы с помощью абсолютных ссылок.

     

     

     

     

     

     

     

     


    У вас недостаточно прав