Автоматическая подстановка данных
Задание 1. Создание выпадающего списка
1. Откройте файл Ex03_1.xlsx.
2. На листе Прайс находится таблица товаров с ценами.
3. Задайте имена диапазонам с названием товара (Товар) и таблице с прайсом (Таблица) (рисунок 1). Имена диапазонов задаются для дальнейшего их использования в формулах и облегчения работы с диапазонами. Имя диапазона можно задать через поле имени ячейки или же в контекстном меню выбрать команду Присвоить имя (разумеется в том или ином случае диапазон ячеек должен быть предварительно выделен, как показано на рисунке 1).
Рисунок 1
4. На листе продажи в столбце Наименование товара создайте выпадающий список и заполните его данными.
Для создания выпадающего списка необходимо выделить ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выбрать на вкладке Данные кнопку Проверка данных (рисунок 2).
Рисунок 2
На первой вкладке Параметры из выпадающего списка Тип данных выбрать вариант Список и ввести в строчку Источник знак равно и имя диапазона с требуемыми данными (т.е. =Товар) (рисунок 3). Нажмите ОК.
Результат создания выпадающего списка представлен на рисунке 4.
Если набор значений в источнике может изменяться, лучше вставлять или удалять данные в середине списка.
Рисунок 3
Рисунок 4
Знакомство с функциями ПОИСКПОЗ и ИНДЕКС
Функции ПОИСКПОЗ и ИНДЕКС в основном применяются для автоматической подстановки данных в таблицу из заданного диапазона.
Синтаксис функций
ПОИСКПОЗ ( искомое_значение, массив, тип_сопоставления)
Массив— это блок, состоящий из одного столбца или одной строки.
Тип_сопоставления—это число -1, 0 или 1.
Если тип_сопоставленияравен 1, то функция ПОИСКПОЗнаходит наибольшее значение, которое меньше, чем искомое_значениеили равно ему. Просматриваемый массивдолжен быть упорядочен по возрастанию.
Если тип_сопоставленияравен 0,то функция ПОИСКПОЗнаходит первое значение, которое в точности равно аргументу искомое_значение.Просматриваемый массивможет быть неупорядоченным.
Если тип_сопоставленияравен -1, то функция ПОИСКПОЗнаходит наименьшее значение, которое больше, чем искомое_значение,или равно ему. Просматриваемый_массивдолжен быть упорядочен по убыванию.
Если тип_сопоставленияопущен, то предполагается, что он равен 1.
Функция ПОИСКПОЗвозвращает позицию искомого значения в массиве, а не само значение.
Функция ИНДЕКС имеет две формы. Мы рассмотрим только одну.
ИНДЕКС( таблица; номер_строки; номер_столбца)
Эта функция выбирает из прямоугольного блока (таблицы) элемент, задаваемый номерами строки и столбца, причем эти номера отсчитываются от левого верхнего элемента блока.
Давайте рассмотрим работу этих функций на конкретных примерах.
Задание 2. Знакомство с функцией ПОИСКПОЗ
С помощью функции ПОИСКПОЗ() определите позицию товара с наименованием «Иогурт» в диапазоне Товар. Для этого:
1. в электронной книге Ex03_1.xlsx перейдите на лист Прайс и установите курсор листа в любую свободную ячейку;
2. введите в эту ячейку формулу =ПОИСКПОЗ("Йогурт";Товар;0), т.е. мы указываем с помощью данной функции что нам необходимо найти элемент «Йогурт» (текстовое значение в функциях всегда указывается в кавычках) в диапазоне Товар, тип сопоставления 0 – точное совпадение;
3. нажмите Enter. В результате вы должны получить число 7 (рисунок 5).
Рисунок 5
Действительно, если посмотреть на диапазон Товар, то позиция йогурта соответствует этой цифре. При этом стоит помнить, что позиция значения в массиве, т.е. в заданном диапазоне, отсчитывается от начала диапазона. Получается, что если мы добавим в начале таблицы с данными новую строку, то значение «Йогурт» сместится на строку с номером 8, но диапазон Товар начинается со значения «Молоко», поэтому с начала диапазона позиция йогурта будет в любом случае соответствовать цифре 7 (рисунок 6).
Рисунок 6
Обратите внимание на то, что в качестве значения для функции ПОИСКПОЗ() можно указывать как само значение, так и имя ячейки, в которой находится это значение. Например, запись =ПОИСКПОЗ(A7;Товар;0) позволит получить аналогичный результат как и при использовании записи =ПОИСКПОЗ("Йогурт";Товар;0).
Задание 3. Знакомство с функцией ИНДЕКС
Напомним, что функция ИНДЕКС() выбирает из прямоугольного блока (таблицы) элемент, задаваемый номерами строки и столбца, причем эти номера отсчитываются от левого верхнего элемента блока.
С помощью функции ИНДЕКС() найдите в диапазоне Товар элемент расположенный на пятой позиции. Для этого:
1. в электронной книге Ex03_1.xlsx на листе Прайс установите курсор листа в любую свободную ячейку;
2. введите в эту ячейку следующую функцию =ИНДЕКС(Товар;5). Нажмите Enter. Результат представлен на рисунке 7.
Рисунок 7
Обратите внимание, что мы указали для функции ИНДЕКС только значения диапазона и номер строки. Номер столбца мы указывать не стали. Этот параметр является не обязательным если мы ищем значение в таблице (диапазоне) состоящей из одного столбца. Если же нам надо найти значение в таблице (диапазоне) состоящем из нескольких столбцов, тогда в формуле, для более точного поиска значения, необходимо указывать номер столбца.
В новой ячейке введите формулу = ИНДЕКС(Таблица;8;2). В результате вы найдете стоимость ветчины (рисунок 8).
Рисунок 8
Автоматическая подстановка данных
При работе с выпадающим списком можно автоматизировать ввод данных в таблице. Например, имеется прайс лист, содержащий названия товаров и их цену. Можно организовать выбор названия товара из списка и автоматическую подстановку цены товара в итоговую таблицу.
Автоматическая подстановка данных из справочной таблицы основывается на комбинированном использовании функций ИНДЕКС и ПОИСКПОЗ.
Задание 4. Комбинирование функций ПОИСКПОЗ и ИНДЕКС
В электронной книге Ex03_1.xlsx на листе Продажи в колонке Цена, используя функции MS Excel, обеспечьте автоматическую подстановку стоимости товара с листа Прайс, в зависимости от выбранного из выпадающего списка значения на листе Продажи.
1. Установите курсор листа в ячейку С2 и введите в нее следующую формулу:
=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)
2. Нажмите Enter. В результате вы получите в ячейке С2 стоимость того товара, наименование которого указано в соседней колонке (рисунок 9). При выборе из выпадающего списка другого наименования товара значение цены будет изменяться соответствующим образом.
Рисунок 9
3. С помощью маркера автозаполнения продублируйте данную формулу до конца таблицы (рисунок 10).
Рисунок 10
Расшифруем данную формулу. Эта формула начинает работать с функции ПОИСКПОЗ, которая позволяет нам найти позицию значения из ячейки B2 на листе Продажи в диапазоне Товар с листа Прайс, и типом точности 0. Например, если воспользоваться списком товаров, который представлен на рисунке 10, то функция ПОИСКПОЗ ищет позицию хлеба, название которого указано в ячейке В2 из колонки Наименование товара в диапазоне Товар на листе Прайс. Эта позиция будет равна 3. Следовательно, в памяти компьютера введенная нами формула
=ИНДЕКС(Таблица; ПОИСКПОЗ(B2; Товар; 0); 2)
примет вид
=ИНДЕКС(Таблица;3; 2)
Параметр «3» – это результат работы функции ПОИСКПОЗ.
Далее вступает в работу функция ИНДЕКС, которая будет искать в диапазоне Таблица на листе Прайс значение, находящееся на пересечении третьей строки и второго столбца. (Напоминаем, что диапазон Таблица состоит из двух столбцов). Этим значением будет стоимость товара, в данном случае хлеба = 27.
Задание 5. Оформление итоговой таблицы
На листе Продажи в колонке Количество введите произвольные значения. Вычислите значения в колонке Итого.
Задание 6. Самостоятельное задание
Откройте файл Кадры.xls. Требуется автоматизировать изменение окладов на листе Кадры.
Например, требуется кого-то из менеджеров перевести и старшие менеджеры или консультанта перевести в грузчики. Требуется проделать эту операцию для четырех сотрудников. Вместе с должностью должен изменяться и оклад.
Создайте новый лист и назовите его Штат.
Скопируйте на лист Штат столбцы Должность и Оклад.
Удалите повторяющихся сотрудников. Для этого:
1. Выделите столбцы должность и оклад.
2. На вкладке Данные щелкните по кнопке Удалить дубликаты.
3. Удалите галочку из столбца Оклад.
4. Щелкните по кнопке Ok.
Для того, чтобы можно было изменять должности, на листе Кадры в колонке должности создайте выпадающий список. При этом потребуется менять и должностной оклад, для этого автоматизируем этот процесс так, чтобы при смене должности Excel сам менял оклад. Для этого используем функции: ПОИСКПОЗ и ИНДЕКС.
Методические указания
1. Выделите на листе Штатдиапазон должностей. Присвойте выделенному диапазону имя должность.
2. Выделите на листе Штатдиапазон окладов. Присвойте выделенному диапазону имя оклад.
3. На листе Сотрудникивстолбце Окладвведите формулу, которая бы искала на листе Штатвдиапазоне должностьпозицию соответствующую должности сотрудника, а затем из диапазона окладлиста Штатвставляла оклад, соответствующий найденной позиции.
4. После ввода формулы убедитесь, что полученный оклад действительно соответствует окладу должности сотрудника с листа Штат.
5. Измените оклад у гл. бухгалтера на листе Штат. Проверьте произошло ли изменение соответствующего оклада на листе Сотрудники.