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

1. Использование формы. Выделите область таблицы и перейдите в режим формы. Введите данные. Выполните листание записей. Перейдите к 6-й записи и замените адрес Лесная,19,12 на адрес Лесопарковая,12, 33.Добавьте еще одну произвольную запись в имеющийся список.

Таблица 8

№	Фамилия, Имя	Должность	Дата рождения	Адрес	Телефон
	Сидоров Андрей	Кассир	06.12.48	Ленина, 38,4	65-10-02
	Попов Петр	Продавец	23.04.56	Южная,6,41	34-82-19
	Шмидт Анна	Продавец	01.08.60	Лесная,19,12	24-13-08
	Шмидт Николай	Президент	16.05.55	Горная,212,44	35-03-31
	Жуков Петр	Продавец	23.01.66	Солнечная,1,2	12-65-90
	Попова Марина	Координатор	02.03.59	Лесная, 12,45	12-18-92
	Орлов Сергей	Менеджер	05.08.64	Мопра, 12,22	26-66-76
	Исаев Петр	Разведчик	23.02.64	Зеленая, 64,44	14-18-78

Выполните поиск записей по фамилии “Попов” (критерием поиска является фамилия), а затем по имени “Петр” (критерием поиска является шаблон * Петр). Выполните поиск сотрудников старше 30 лет (имеющих дату рождения после 01.01.68).

2. Сортировка данных. Выделите область таблицы и используйте разнообразные виды сортировок: по фамилиям, по датам рождения, по адресам, по должности. После этого установите двухуровневую сортировку по фамилиям и дате рождения.

3. Группировка. Примените группировку данных по должностям.

4. Автофильтр. Установите автофильтр и выберите всех сотрудников, имеющих должность Продавец. Затем выберите данные обо всех продавцах, живущих на Северо-западе или ЧМЗ (телефон начинается с цифр 12 или 41). Результат применения фильтра скопируйте на другое место рабочего листа и дайте таблицам названия.

5. Расширенный фильтр. Перед использованием расширенного фильтра определите область критериев (3 строки правее таблицы) и область результатов поиска (10 -12 строк ниже таблицы). Выполните копирование строки заголовка исходной таблицы в указанные области.

Выберите из таблицы:

· сотрудников, чьи фамилии начинаются на букву П;

· сотрудников, чьи фамилии начинаются на букву П, с датой рождения после 01.01.54;

· сотрудников, профессия которых Президент или Координатор;

· другие варианты выборок по желанию.

Результаты выборок скопируйте на другие листы рабочей книги и дайте им соответствующие имена.

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

Тема: Работа со списками. Функции поиска в списках.

Условие задачи. Вы владелец фирмы, которая торгует компьютерами и комплектующими к ним. Финансовый директор фирмы ведет реестр клиентов, поступивших заказов и список товаров, которые у Вас есть. Необходимо автоматизировать поиск нужного товара, вычисление суммы заказа и составление бланка заказа. Составьте отчет по динамике сбыта товаров по месяцам и за текущий квартал.

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

1. Создайте список клиентов. Откройте лист 1 и присвойте ему имя КЛИЕНТЫ. Введите заголовки столбцов в ячейки A1 – H1 (см. табл. 9) и введите в нее данные, используя команду Данные/Форма. Задайте в столбце H процентный формат. Отсортируйте данные по возрастанию по полю Код. Для связывания таблиц присвойте столбцам следующие имена: столбцу А – имя Фирма, столбцу В – Код, столбцу H – Скидка. Оформите таблицу.

Таблица 9

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

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

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

Таблица 10

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

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

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

Таблица 11.

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

Месяц	Дата	№ зака- за	№ това- ра	Наименова ние товара	Коли - чество	Цена	Код заказчика	Название фирмы	Сумма заказа	Скидка	Упла чено
март	02.03	98-1
март	12.03	98-2
май	06.05	98-3
июнь	12.06	98-4

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.

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

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

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

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

Загрузите таблицы, созданные в работе № 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.

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