С помощью Microsoft Excel необходимо решить следующую задачу: магазин компьютерной техники осуществляет продажу товаров определенному списку покупателей. Каждый покупатель имеет скидку на покупки. Необходимо организовать ведение журнала продаж, в котором товар и покупатель выбирается из списка, при этом цена товара и процент скидки определяется автоматически, рассчитать итоговую стоимость покупок для указанного покупателя.
Для выполнения задания необходимо создать 4 таблицы, показанные на рисунке 1.
| Покупатели | ||
| № | Наименование | Скидка, % |
| … | ||
| Товары | ||||||||||
| № | Наименование | Цена | ||||||||
| … | ||||||||||
| Журнал продаж | ||||||||||
| № | Дата | Товар | Цена | Покупатель | Скидка | Количество | Сумма | Сумма со скидкой | ||
| ВПР() | ВПР() | |||||||||
| … | ||||||||||
| Итог | ||||||||||
| Покупатель | ||||||||||
| Общая сумма закупок | СУММЕСЛИ() | |||||||||
Рисунок 1 – Таблицы для решения задачи
Таблицы, показанные на рисунке 1, размещаются на соответствующих листах с наименованиями (рисунок 2): покупатели, товары, продажи, итоги.
Рисунок 2 – Имена листов Excel для решения задачи
Вид таблицы «Товары» представлен на рисунке 3.
Рисунок 3 – Таблица «Товары»
Таблицы Покупатели и Товары необходимо заполнить произвольными значениями. Вид таблицы «Покупатели» представлен на рисунке 4.
Рисунок 4 – Таблица «Покупатели»
В дальнейшем в формулах потребуются диапазоны с перечислением покупателей и товаров, а также наименования покупателей вместе со значениями скидки и названия товаров с их ценами. Этим диапазонам необходимо присвоить соответствующие имена: Покупатели, Товары, Скидки, Цены.
Чтобы присвоить имя диапазону, необходимо выделить его и в поле Имя ячейки внести наименование (см. рисунок 5).
Рисунок 5 – Присвоение имени ячейке
Таблица журнал продаж представлена на рисунке 6.
Рисунок 6 – Таблица «Журнал продаж»
Для заполнения колонок Товар и Покупатель нужно создать соответствующие списки. Для этого необходимо выделить столбец и выбрать пункт меню Данные - Проверка. Заполним появившееся окно, как показано на рисунке 7.
Рисунок 7 – Проверка вводимых значений
В поле Источник указан диапазон ячеек с наименованием Товары.
Для автоматического определения цены используется функция ВПР(). Для этого нужно выбрать первую ячейку из столбца Цены. Затем вызвать Мастер функций, выбрав пункт меню Вставка – Функция, и в перечне найти функцию ВПР. Появившееся окно заполнить, как показано на рисунке 8.
Рисунок 8 – Функция ВПР
Аргументами данной функции являются:
| Искомое_значение | С2 | Наименование товара, для которого определяем цену |
| Таблица | Цены | Диапазон ячеек, содержащий два столбца с наименованиями товаров и их ценами |
| Номер_столбца | Номер столбца с ценами |
Создав функцию для одной ячейки, можно ее скопировать методом автозаполнения во все остальные ячейки столбца.
Аналогичным образом, с помощью функции ВПР заполняется столбец Скидки. Значения столбцов Количеств о и Дата в таблице Журнал продаж указывается произвольно. Для расчета суммы используется формула: Цена*Количество (см. рисунок 9).
Рисунок 9 – Определение суммы покупки
Также с помощью формулы определим Сумму со скидкой – рисунок 10.
Рисунок 10– Определение суммы покупки со скидкой
На листе Итоги в строке Покупатель необходимо создать список аналогично спискам на листе Журнал закупок – рисунок 11.
Рисунок 11 – Создание списка покупателей
Общую сумму можно рассчитать с помощью функции СУММЕСЛИ(). Для этого вызовем мастер функции (Вставка - Функция) и выберем функцию СУММЕСЛИ. Аргументы этой функции показаны в таблице 1.
Таблица 1 – Аргументы функции СУММЕСЛИ
| Диапазон | 'журнал продаж'!E2:E11 | Диапазон ячеек с наименованиями покупателей на листе журнал продаж. |
| Критерий | B1 | Наименование покупателя, для которого определяется сумма |
| Диапазон_суммирования | 'журнал продаж'!I2:I11 | Диапазон ячеек на листе Журнал продаж с суммами с учетом скидок. |
Окно «Аргументы функции» показано на рисунке 12.
Рисунок 12 – Функция СУММЕСЛИ
При выборе другого покупателя, сумма продаж будет автоматически пересчитываться.