Пример выполнения практического задания

С помощью Microsoft Excel необходимо решить следующую задачу: магазин компьютерной техники осуществляет продажу товаров определенному списку покупателей. Каждый покупатель имеет скидку на покупки. Необходимо организовать ведение журнала продаж, в котором товар и покупатель выбирается из списка, при этом цена товара и процент скидки определяется автоматически, рассчитать итоговую стоимость покупок для указанного покупателя.

Для выполнения задания необходимо создать 4 таблицы, показанные на рисунке 1.

Рисунок 1 – Таблицы для решения задачи

Таблицы, показанные на рисунке 1, размещаются на соответствующих листах с наименованиями (рисунок 2): покупатели, товары, продажи, итоги.

Рисунок 2 – Имена листов Excel для решения задачи

Вид таблицы «Товары» представлен на рисунке 3.

Рисунок 3 – Таблица «Товары»

Таблицы Покупатели и Товары необходимо заполнить произвольными значениями. Вид таблицы «Покупатели» представлен на рисунке 4.

Рисунок 4 – Таблица «Покупатели»

В дальнейшем в формулах потребуются диапазоны с перечислением покупателей и товаров, а также наименования покупателей вместе со значениями скидки и названия товаров с их ценами. Этим диапазонам необходимо присвоить соответствующие имена: Покупатели, Товары, Скидки, Цены.

Чтобы присвоить имя диапазону, необходимо выделить его и в поле Имя ячейки внести наименование (см. рисунок 5).

Рисунок 5 – Присвоение имени ячейке

Таблица журнал продаж представлена на рисунке 6.

Рисунок 6 – Таблица «Журнал продаж»

Для заполнения колонок Товар и Покупатель нужно создать соответствующие списки. Для этого необходимо выделить столбец и выбрать пункт меню Данные - Проверка. Заполним появившееся окно, как показано на рисунке 7.

Рисунок 7 – Проверка вводимых значений

В поле Источник указан диапазон ячеек с наименованием Товары.

Для автоматического определения цены используется функция ВПР(). Для этого нужно выбрать первую ячейку из столбца Цены. Затем вызвать Мастер функций, выбрав пункт меню Вставка – Функция, и в перечне найти функцию ВПР. Появившееся окно заполнить, как показано на рисунке 8.

Рисунок 8 – Функция ВПР

Аргументами данной функции являются:

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

Аналогичным образом, с помощью функции ВПР заполняется столбец Скидки. Значения столбцов Количество и Дата в таблице Журнал продаж указывается произвольно. Для расчета суммы используется формула: Цена*Количество (см. рисунок 9).

Рисунок 9 – Определение суммы покупки

Также с помощью формулы определим Сумму со скидкой – рисунок 10.

Рисунок 10– Определение суммы покупки со скидкой

На листе Итоги в строке Покупатель необходимо создать список аналогично спискам на листе Журнал закупок – рисунок 11.

Рисунок 11 – Создание списка покупателей

Общую сумму можно рассчитать с помощью функции СУММЕСЛИ(). Для этого вызовем мастер функции (Вставка - Функция) и выберем функцию СУММЕСЛИ. Аргументы этой функции показаны в таблице 1.

Таблица 1 – Аргументы функции СУММЕСЛИ

Окно «Аргументы функции» показано на рисунке 12.

Рисунок 12 – Функция СУММЕСЛИ

При выборе другого покупателя, сумма продаж будет автоматически пересчитываться.