Лабораторная работа №11

Тема: ФУНКЦИИ ВПР и ГПР, операции с диапазонами

Функция ВПР (вертикальный просмотр) незаменима, когда данные из одной таблицы, содержащей какие-то признаки, нужно перенести в другую таблицу, где содержится тот же характерный признак.

Задание 1: имеются 2 таблицы: таблица количества проданных товаров и таблица цен на эти товары, а для решения задачи требуется рассчитать стоимость всех проданных товаров.

1.        Создать файл MS Excel, сохранить именем lab_2.

2.        Скопировать из Word таблицу 1 Продажа товаров и озаглавить лист, как Продажа.

3.          Скопировать таблицу 2 Цены товаров на лист 2 с тем же размещением на листе и озаглавить лист, как Цены.

Таблица 1

Таблица 2

Порядок работы

Для решения задачи нужно перенести цены проданных товаров из таблицы 2 в пустой столбец "Цена, руб.", создать формулу расчёта стоимости товара, распространить её на все товары и рассчитать полную стоимость проданных товаров, как в рублях, так и в долларах.

1.        Создать в ячейке C3 таблицы 1 формулу для перенесения из таблицы 2 цены товара "Ведро", для чего:

−     установить курсор в ячейку C3 (ячейка результата);

−     щёлкнуть кнопку f(x) и выбрать функцию ВПР из раздела «Ссылки и массивы»;

−     в строке окна «Аргументы функции» создать формулу:

= ВПР(A3;Цена!$A$1:$C$20;3;0)

где      A3 – «Искомое значение» (адрес наименования товара, для которого ищется цена);

Цена!$A$1:$C$20 – «Таблица» (таблица Цены товаров). Для фиксации ценового диапазона необходимо устанавливать знаки доллара;

3 – «Номер столбца» (номер столбца Цена, руб. в таблице Цены товаров);

0 – «Интервальный просмотр» (индекс, указывающий на то, что функция ВПР ищет только точное совпадение). Если точное совпадение не найдено, в ячейку записывается значение ошибки #Н/Д (нет данных).

2.        Проанализировать соответствие полученного результата и щёлкнуть ОК. В ячейку C3 должна быть занесена цена товара «Ведро».

3.        Распространить формулу, используя маркер заполнения, на все строки таблицы 1 и проверить корректность простановки цены товаров. В случае появления символов #Н/Д проверить соответствие названий товаров в обеих таблицах и наличие цен.

4.        Создать в ячейке D3 столбца «Стоимость, руб.» формулу расчёта стоимости проданных вёдер и распространить её с помощью маркера заполнения на все товары.

5.        Создать в ячейке D28 формулу расчёта итоговой стоимости проданных товаров. Она составляет 37509 руб.

6.        Изменить в ячейке B27 слово «Карандаш» на слово «Карандаши». Отметить, что в столбцах этой строки появились символы ошибки #Н/Д, т. к. возникло несоответствие названий в таблицах 1 и 2.

7.        Вернуть правильное название товара, при этом ошибка исчезнет.

8.        Изменить в ячейке F20 слово «Карандаш» на слово «Карандаши».

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

10.    Установить курсор в ячейку C3 и изменить формулу в строке формул следующим образом:

=ЕСЛИОШИБКА(ВПР(A3;$F$2:$H$20;3;0);ЛОЖЬ)

и распространить её на все ячейки столбца C.     В этом случае при имитации ошибки вместо символов ошибки #Н/Д будет возникать слово ЛОЖЬ.

Операции с диапазонами

Для того чтобы умножить или разделить массив чисел на какую-нибудь константу, можно использовать команду Специальная вставка.

Задание 2:

Изменить данные в столбце Стоимость таблицы Продажа согласно курсу доллара.

Порядок работы

1.       Занести в свободную ячейку листа Продажа число, соответствующее курсу доллара, например: 70.

2.      Скопировать ячейку с этим числом.

3.      Выделить в таблице Продажа товаров ячейки со значениями стоимости товаров.

4.      Нажать правую кнопку мыши и выбрать в окне Специальная вставка операцию

«Разделить».

5.       Оценить правильность перевода цены товаров из рублей в доллары и уменьшить количество дробных разрядов до двух знаков.

Аналогичным образом с помощью Специальной вставки в диапазонах осуществляются операции сложения и вычитания.

Работа с функцией ГПР и транспонирование

Задание 3: Транспонировать таблицу Продажа фруктов и рассчитать Стоимость на основе данных таблицы Цены фруктов.

Порядок работы

1.        Скопировать таблицы 3 и 4 на Лист3, назвать его ГПР.

2.        Транспонировать таблицу 3, для чего:

−    выделить таблицу (заголовок Продажа фруктов не выделять);

−    выполнить операцию «Копировать»;

−    выбрать свободную ячейку ниже таблиц, например, А23;

−    нажать правую кнопку мыши –Специальная вставка –Транспонировать.

Таблица 3

Таблица 4

3.        Транспонировать аналогичным образом, расположив ниже, таблицу 4, осуществив вставку в ячейку А29.

Таблица 5

4.                  Применить функцию ГПР (горизонтальный просмотр), список «Ссылки и массивы», создав в ячейке B25 формулу

=ГПР(B23;$A$30:$M$31;2;0),

где

−    B23 – «Искомое значение», т. е. та ячейка, к которой будет найдено соответствие в таблице; $A$30:$M$31;

−    2 – это номер строки таблицы $A$30:$M$31, в которой будет осуществляться поиск соответствия ячейке «Искомое значение»;

−    0 – «Интервальный просмотр» (индекс, указывающий на то, что функция ГПР ищет только точное совпадение).

5.        Скопировать формулу в ячейки С24 – О24.

6.        Рассчитать стоимость продажи каждого проданного фрукта и итоговую стоимость продажи, которая составляет 19161 руб.

7.        Создать ошибку в наименовании какого-либо фрукта таблицы «Цены фруктов» (например, «Кеви») и убедиться, что в таблице

Продажи фруктов возникает несколько символов ошибки (#Н/Д).

Сравнение значений параметров

Задание 4: сравнение данных.

Используя функции ВПР и ГПР, можно обеспечить быстрое сравнение значений в двух таблицах, например, в старом и новом прайсах.

Порядок работы

1.        Создать на Листе 4 таблицу 6 и озаглавить, как «Прайс 2008 г.».

Таблица 6

2.        Создать на следующем листе таблицу 7и озаглавить лист, как «Прайс 2013 г.».

Таблица 7

Примечание. Сортировка в таблицах 6 и 7 не обязательна.

3.        Создать в ячейке D3 таблицы 6 формулу ВПР:

=ВПР(A3;'Прайс 2013 г.'!$A$1:$C$10;3;0)

4.        Проанализировать полученный результат сравнения старой и новой стоимости комплектующих.

Примечание. В строках, где артикулы не совпадают, возникают значения Н/Д.

Задание 5: согласно изученному материалу придумать свой пример применения функций ВПР, ГПР и пример с операцией над диапазоном.