Порядок выполнения работы. 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.

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