Лабораторная работа №13
Тема: Изучение функции Просмотр
Цель: Изучить назначение и возможности использования функции Просмотр (ВПР, ГПР) для упрощения работы с базами данных в Excel
План:
1. Пример решения задачи анализа данных при помощи сводных таблиц.
2. Задания для самостоятельной работы.
Ход занятия:
Теоретическая часть
Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. В данной работе используется первая форма функции – вектор.
Вектор — это диапазон, который содержит только одну строку или один столбец. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки. Эта форма функции ПРОСМОТР используется, когда требуется указать интервал, в котором находятся искомые значения. Другая форма функции ПРОСМОТР автоматически использует для этой цели первую строку или первый столбец.
Векторная форма
ПРОСМОТР (искомое_значение; просматриваемый_вектор; вектор_результатов)
Искомое_значение — это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор — интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Примечание
Значения в аргументе просматриваемый _ вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.
Вектор _ результатов — интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый _ вектор.
Примечание
1. Если ПРОСМОТР не может найти искомое _ значение, то подходящим считается наибольшее значение в аргументе просматриваемый _ вектор, которое меньше, чем искомое _ значение.
2. Если искомое _ значение меньше, чем наименьшее значение в аргументе просматриваемый _ вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
Задание
Для выполнения работы необходимо создать базу данных, содержащую сведения о товарах и объемах их продаж (таблица 6, таблица 7).
Рассчитать объем продаж за каждый месяц.
Объемы продаж представить в виде таблицы и графика.
Таблицы с данными должны находиться на разных листах файла MS Excel.
Порядок выполнения:
1. Создать Таблицу 6 на Листе 1.
2. Присвоить столбцам Таблицы 6 имена: Номер, Товар, Цена:
а) выделит нужный столбец;
б) ввести в поле Имя требуемое имя столбца (поле Имя находится слева от строки формул);
в) нажать клавишу < Enter >.
3. Присвоить Листу 1 имя Товары:
а) дважды щелкнуть по имени листа 1 (или воспользоваться контекстным меню на ярлыке листа 1) и ввести новое имя – Товары;
б) нажать клавишу < Enter >.
4. Создать Таблицу 7 на Листе 2. Присвоить Листу 2 имя Продажи (см п. 3).
5. Ввести в Т аблицу 7 новыестолбцы: Товар, Цена и Сумма (заголовки столбцов).
6. Ввести в первую строку столбца Товар Таблицы 7 формулу для заполнения наименования проданного товара по номеру товара. Использовать функции ЕСЛИ и ПРОСМОТР:
=ЕСЛИ($В2=””;””;ПРОСМОТР($B2;НОМЕР;ТОВАР)), где
”” – в ячейке пусто, т. е. ячейка не содержит информацию;
НОМЕР и ТОВАР – имена столбцов Таблицы 6;
в ячейке B2 Таблицы 6начинается столбец данных номеров товара.
Примечание
Функция ЕСЛИ обеспечивает проверку содержимого ячейки В2. Если данные там отсутствуют ($B2=””), то ячейка D2 тоже останется незаполненной (””). Если в ячейке В2 содержится номер товара, то произойдет его поиск в диапазоне Номер (ПРОСМОТР($B2;НОМЕР)), а в ячейку D2 внесется значение из диапазона Товар (Товар).
7. Ввести в столбец Цена Таблицы 7 формулу для заполнения цены проданного товара по номеру товара (использовать функции ЕСЛИ и ПРОСМОТР).
=ЕСЛИ($В2=””;””;ПРОСМОТР($B2;НОМЕР;ЦЕНА)), где НОМЕР и ЦЕНА имена столбцов Таблицы 9.
1. Ввести в первую строку столбца Сумма Таблицы 7 формулу для заполнения суммы продаж каждого товара (использовать функцию ЕСЛИ). =ЕСЛИ($С2=””;””;С2*Е2).
2. Скопировать формулы в ячейках D2, E2, F2 (из первой строки данных) вниз до конца Таблицы 2.
3. На листе Итоги вывести итоговые значения по месяцам в Таблице 8 (функция СУММЕСЛИ).
4. Создать на листе Итоги диаграмму реализации товаров за каждый месяц по значениям Таблицы 8.
Таблица 6 Таблица 7
| № | Наименование товара | Цена | Месяц | Номер товара | Количество | |
| Excel для Windows 2000 | ||||||
| IBM PC для пользователя | ||||||
| Windows 2000 | ||||||
| Операционная среда Windows и ее приложения | ||||||
| Word для Windows 2000 | ||||||
| Access для Windows 2000 | ||||||
| Office для Windows 2000 | ||||||
| Internet в подлиннике | ||||||
| Сетевые ресурсы Windows NT | ||||||
| Мультимедиа в примерах |
Таблица 8
| Итого | |
| 1-й месяц | |
| 2-й месяц | |
| 3-й месяц |
Листы файла Excel c выполненной работой
Рисунок 11 – Заполненная таблица 7 на листе ПРОДАЖИ
Рисунок 12 – Таблица 7 с формулами
Рисунок 13 – Таблица 8 с рассчитанными данными на листе ИТОГИ
Рисунок 14 – Формулы таблицы 8 на листе ИТОГИ
Содержание отчета:
Представить выполненные задания и конспект с анализом и выводами по работе.