Список клиентов

Название фирмы	Код	Контакт	Город	Улица	Телефон	Скидка
Плата ОАО	2001	Иванова	Челябинск	Торговая,12	28-51-12	0%
Лад ОАО	3102	Петров	Копейск	Первая,12	12-12-13	3%
Старт ОАО	2301	Сидоров	Уфа	Вторая,34	32-14-15	2%
Винт ОАО	2201	Усачев	Иваново	Третья,45	45-56-67	1%

1. Создайте список товаров. Откройте лист 2 и присвойте ему имя ТОВАРЫ. Создайте таблицу (см. табл. 10), задайте в столбце С денежный формат. Введите данные, приведенные в табл. 10. Присвойте столбцам имена: столбцу А – имя Номер, столбцу В – Товар, столбцу С – Цена. Отсортируйте данные по возрастанию по полю Номер.

Таблица 10

Список товаров

Номер	Наименование товара	Цена
101	Компьютер З5-55	999,00р.
201	Принтер лазерный ОХ	1 300,00р.
102	Компьютер Р5-100	1 150,00р.
202	Принтер лазерный ПХ	740,00р.

3. Создайте список заказов. Для этого откройте лист 3, присвойте ему имя ЗАКАЗЫ и создайте таблицу (см. табл. 11).

Таблица 11.

Список заказов

Месяц	Дата	№ зака- за	№ това- ра	Наименова ние товара	Коли - чество	Цена	Код заказчика	Название фирмы	Сумма заказа	Скидка	Упла чено
март	02.03	98-1	102		10		2001
март	12.03	98-2	202		50		2201
май	06.05	98-3	101		30		2301
июнь	12.06	98-4	201		15		3102

4. Задайте в столбцах форматы представления данных: в столбце В формат Дата, в столбцах G, J, L денежный формат, а в столбце К – процентный. Введите данные, которые даны в столбцах А, В, С. D, G, H. В остальные столбцы данные заносятся из таблиц Список клиентов и Список товаров. Для поиска нужной информации в этих таблицах используем функцию ПРОСМОТР и имена столбцов.

5. Для задания в ячейке Е2 наименования товара производим поиск товара из списка товаров по его номеру. Для этого в ячейку Е2 вводим формулу:

ЕСЛИ($D2=“”;””;ПРОСМОТР($D2;Номер; Товар))

Формула означает: Если в ячейке D2 номер товара не указан (ячейка пустая), то Е2 остается пустой (“”); если же в D2 введен номер товара, то выполняется поиск номера товара в столбце Номер в таблице Список товаров и в ячейку Е2 возвращается наименование товара из столбца Товар таблицы Список товаров.

6. Формула для задания цены товара в ячейке G2 выглядит аналогично, но поиск по номеру товара его цены выполняется в столбце Цена таблицы Список товаров.

7. Для поиска названия фирмы и скидки на товар используйте функцию ПРОСМОТР, которая по Коду будет производить поиск в столбцах Фирма и Скидка в таблице Список клиентов на листе Клиенты. Например, для ячейки J2 формула выглядит так:

ЕСЛИ($H2=“”;””;ПРОСМОТР($H2;Код; Фирма))

Ввод формул выполняйте копированием, исправляя соответствующие имена столбцов.

8. В столбце Сумма заказа вычислите общую стоимость заказа без учета скидки, а в столбце Уплачено – сумму, подлежащую к оплате с учетом предоставленной скидки.

9. Присвойте столбцам имена: столбцу В - Дата, С - Заказ, D - Номер1, Е - Товар1, F - Количество, G - Цена1, J - Фирма1, К - Сумма, H - Код1, L- Скидка1, М -Оплата.

10. Закрасьте ячейки, в которые должны быть введены данные, синим цветом, а ячейки с формулами – желтым

Лабораторная работа № 9.

Тема: Автоматизация составления бланка заказа.

Порядок выполнения работы.

Создайте бланк заказа следующего вида

	Заказ №		от
Название фирмы заказчика				Код
Наименование товара				№
Заказываемое количество		ед. по цене			за ед.
Общая стоимость заказа		Скидка (%)
К оплате		Оформил

1. Загрузите таблицы, созданные в работе № 8. Перейдите в четвертый рабочий лист. В ячейку D3 введите текст Заказ №, в ячейку F3 введите слово “от” и уменьшите ширину столбца. В ячейку G3 вставьте дату заказа с помощью формулы:

=ЕСЛИ ($E$3=“”;””;ПРОСМОТР($E$3; Заказ; Дата)).

Проведите линию обрамления в ячейках Е3 и G3. Текст в строке 3 должен иметь полужирное начертание и шрифт размером 14 пунктов.

2. В ячейку С5 введите текст Название фирмы - заказчика, размер шрифта 8 пунктов. Расположите текст по центру ячеек С5-D5. Задайте формулу для вставки названия фирмы:

=ЕСЛИ ($E$3=“”;””;ПРОСМОТР($E$3; Заказ; Фирма1)).

Подчеркните название фирмы и расположите его по центру ячеек Е5, F5, G5. В ячейку H5 введите слово код, в ячейку I5 поместите формулу:

=ЕСЛИ ($E$3=“”;””;ПРОСМОТР($E$3; Заказ; Код1)).

3. В ячейку С7 введите текст Наименование товара, а для ячеек Е7, F7 и G7 примените центрирование и подчеркивание. Ячейка Е7 должна содержать формулу для поиска по номеру заказа наименования товара с аргументами Заказ и Товар1. В ячейку H7 введите символ №, а в ячейку I7 задайте формулу для поиска по номеру заказа номера товара с аргументами Заказ и Номер1. Примените подчеркивание для ячейки I7.

4. Четвертая строка бланка содержит сведения о количестве и цене заказываемого товара. В ячейку С9 введите текст: Заказываемое количество. В ячейку Е9 введите формулу для поиска с аргументами Заказ и Количество. Подчеркните ячейку. В ячейку Е9 введите текст: ед. по цене. Ячейка H9 должна содержать формулу с аргументами Заказ и Цена1. К этой ячейке следует применить подчеркивание и денежный стиль. В ячейку I9 поместите текст: за ед.

5. Формируем пятую строку бланка. В ячейку С11 введите текст Общая стоимость заказа, а в ячейку Е11 формулу с аргументами Заказ и Сумма. Задайте для ячейки обрамление рамкой снизу и денежный формат. В ячейку F11 введите Скидка (%). В ячейку I11 поместите формулу с аргументами Заказ и Скидка1. Задайте обрамление снизу и процентный формат.

6. Заполним последнюю строку бланка. Введите в ячейку С13 текст: К оплате, а в ячейку D13 поместите формулу с аргументами Заказ и Оплата, и вновь задайте параметры форматирования: обрамление рамкой снизу и денежный стиль. В ячейке Е13 введите слово Оформил, выделите ячейки G13, H13, I13 и задайте для них центрирование по столбцам и обрамление рамкой снизу.

7. Поместите в ячейке Е3 номер заказа и проверьте правильность заполнения бланка. Проверьте, чтобы номера заказов в таблице Список заказов были отсортированы по возрастанию.

Лабораторная работа № 10.

Тема: Сводные таблицы Работа является продолжением работы 8.

Задание: Проследите динамику сбыта отдельных товаров по месяцам.

Порядок выполнения работы.

1. Загрузите таблицы, созданные в работе № 8. Перейдите в пятый рабочий лист и присвойте ему имя таблица. На этом листе будет размещена сводная таблица, Вызовите Мастер сводных таблиц и активизируйте опцию Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel.

2. В окне Шаг 2 из 4 укажите диапазон с исходными данными, Для этого переместитесь на лист Заказы и выделите диапазон с таблицей.

3. В окне Шаг 3 из 4 в область страниц поместите поле данных Месяц, а в область строк – поля Наименование товара, Количество и Сумма заказа. Затем определите, по какому полю будет подсчитываться итоговая сумма. Для этого поместите в область данных поле Сумма заказа, после этого в области данных должна появиться кнопка Сумма по полю Сумма заказа. Если ее нет, то щелкните по той кнопке, которая есть в области данных, и из появившегося списка функций выберите функцию Сумма. Чтобы не отображать в таблице промежуточные итоги, дважды щелкните на имени поля Количество. Выберите в появившемся окне переключатель Нет.

4. В окне Шаг 4 из 4 выберите ячейку рабочего листа Таблица, с которой должна начинаться сводная таблица, присвойте таблице имя и нажмите кнопку Готово.

5. Выберите из списка Месяц Январь и получите данные о продажах за этот месяц. Поместите поле Название в область страницы непосредственно под полем Месяц и используйте в качестве фильтра показа данных значения полей Месяц и Название. В результате должна быть построена таблица следующего вида:

Месяц	Март
Наименование товара	Компьютер Р5 100

Сумма по полю Сумма заказа
Количество	Сумма заказа	Всего
10	11 500	11 500
Общий итог		11 500