Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ  

ОФИСНЫЙ

     Бывают домовые, а я - ОФИСНЫЙ!

     Облегчаю трудовые будни!

  Главная | Карьера | Трудовое законодательство
В помощь коммерсанту | Уроки по Excel
Путешествия | Личные финансы | Непознанное | Загадки,ребусы,анекдоты
Красота и здоровье | Oriflame | Притчи,легенды | Мир женщины  




Loading...





EXCEL ДЛЯ "ЧАЙНИКОВ" И НЕ ТОЛЬКО


ВСЕ УРОКИ


Поиск по сайту:
Пользовательский поиск

Назад

Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ




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

Пример телефонного справочника в Excel

Для создания такого справочника мы используем две функции Excel:

  • ИНДЕКС
  • ПОИСКПОЗ


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

Дает возможность выбрать значение нужной ячейки в массиве данных.

Например, у нас есть список людей с телефонами:

Мы хотим, чтобы в выбранной ячейке появлялось значение, которое находится в определенном месте массива (выбранного диапазона), т.е. мы задаем координаты ячейки: номер строки и столбца.

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

Выбираем в качестве массива все заполненные ячейки, кроме шапки и выбираем номер столбца (2) и строки (2) диапазона, который хотим вывести в выделенную ячейку:

Получаем:

В строке состояния (обведена синим) мы видим, что сначала указан диапазон (массив), в котором происходит поиск значения, а дальше следуют координаты искомого значения:



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

дает возможность найти номер строки, в которой находится искомое значение:

Как видим «Смирнов» находится в 3-ей строке массива, что и выдает функция «ПОИСКПОЗ».

Для вывода функции выбираем ячейку, где будет отражаться номер строки, вызываем функцию «ПОИСКПОЗ» через функции (обведено красным) :

Выбираем в качестве искомого значения ячейку, где будет задаваться фамилия человека, которого мы хотим найти в списке, в качестве массива – ячейки со всеми фамилиями. Тип сопоставления поставим «0».

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



А теперь совместим функцию «ИНДЕКС» с функцией «ПОИСКПОЗ».

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

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

Получаем:

При смене фамилии в ячейке «Искомое» результат поиска будет меняться. При этом регистр значения не имеет. Если написать фамилию с маленькой буквы, функция все равно найдет ее в массиве данных.



Прилагаю пример телефонного справочника в Excel.

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




Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой "эксель" по адресу: ngt@inbox.ru


Назад    Вверх страницы





Как визуально разделить число на разряды


Как округлить значение


Как сделать скидку


Как посчитать наценку и маржу


Как сделать наценку на число


Как посчитать пеню


Как подготовить прайс-лист


Как рассчитать цену с учетом заданной маржи


Как создать базу данных


Как посчитать в базе данных количество клиентов по каналам продаж







Как составить список студентов, не допущенных к экзамену по данным о сдаче зачетов


Как вычленить клиентов, сумма задолженности которых больше судебных издержек


Поиск и замена. Как сделать массовую замену значений


Как установить автофильтр


Сортировка подобных значений. Автофильтр


Сортировка значений по заданным параметрам. Автофильтр


Сортировка данных по убыванию и возрастанию


Как вставить отфильтрованные значения на новый лист


Как добавить формулу. Перемножаем числа


Как добавить формулу. Наценка и маржа







Как сложить значения с помощью автосуммы


Как одну дату вычесть из другой, чтобы получить количество дней


Как скопировать формулу


Как убрать формулы из всех ячеек листа, оставив значения


Как сделать формулу с помощью абсолютной (ссылающейся все время на одну ячейку) ссылки


Как устранить причины, по которым Excel отказывается считать







Рейтинг@Mail.ru   2011-2016   Перепечатка материалов возможна лишь при указании прямой индексируемой гиперссылки