Лабораторная работа № 3. Работа с листами. Функция ЕСЛИ. Логические функции И, ИЛИ

Лабораторная работа 1. Ввод и форматирование данных рабочего листа

1. На первом листе создать таблицу «Менеджеры», содержащую столбцы: Фамилия, имя, отчество менеджера, Оклад, р. Ввести данные о пяти менеджерах (см. пример ниже)

Фамилия, имя, отчество менеджера	Оклад
Иванов
Петров

2. На втором листе создать таблицу «Прайс-лист» со следующими столбцами: Наименование товара, Цена товара, Процент премии менеджеру за продажу. Ввести данные о 10-15 товарах.

Наименование товара	Цена	Процент премии за продажу
Телевизор Samsung		2%
Холодильник Стинол		1,50%

3. На третьем листе создать таблицу «Продажи» со следующими столбцами: Дата продажи, Наименование товара, Фамилия менеджера, Количество товара, Цена товара, Стоимость, Процент премии за продажу, Премия менеджеру за продажу, р.

4. Заполнить первые четыре столбца таблицы данными. При заполнении данной таблицы копируйте наименование товаров и фамилии менеджеров соответственно с первого и второго листов. Для ввода дат продажи воспользуйтесь дополнительным списком дат, который постройте на этом же листе, начиная с 1 октября текущего года до 31 октября через 3 дня. Для этого введите первые две даты в соседние ячейки столбца, выделите эти ячейки и потяните за маркер в правом нижнем углу выделенного диапазона.Копируйте первую дату из данного списка в две первые ячейки столбца «Дата продажи», скопируйте два любых наименования товаров из таблицы «Прайс-лист» и две любые фамилии менеджеров из таблицы «Менеджеры». Аналогичным образом, копируя произвольные данные из списка дат и первых двух таблиц, заполните первые три столбца. Введите данные в столбец «Количество товара. В таблице должно быть не менее 25 строк.

5. Отформатируйте таблицы. Для этого:

· Выделите шапку, вызовите команду Формат/Ячейки, и на вкладке Выравнивание установите флажок «Переносить по словам» и задайте выравнивание по горизонтали - по центру, по вертикали - по верхнему краю.

· Данные столбца «Оклад» отформатируйте с двумя знаками после запятой.(Формат/Ячейки-вкладка Число-Числовой).

· Данные столбцов «Цена» и «Стоимость» отформатируйте в денежном формате.

· Обведите таблицу рамками: внутри таблицы – тонкими одинарными линиями, внешнюю границу и границы шапки – двойными линиями. Используйте команду Формат/Ячейки – Вкладку «Граница» или соответствующий инструмент на панели форматирования

6. Сохраните таблицу.

Лабораторная работа №2. Формулы и простейшие функции.

1. Введите произвольные значения цен для первых пяти строк таблицы продажи.

2. Введите формулу для расчета стоимости в первую ячейку столбца Стоимость (=D2*E2).Скопируйте формулу во все ячейки таблицы.

3. В одну из свободных ячеек таблицы, например, J2 введите процент премии за продажу (1,5%). В первую ячейку столбца «Премия менеджеру» (G2) введите формулу Стоимость*Процент за продажу, т.е.=F2*J2.Скопируйте формулу в следующие четыре ячейки столбца. Убедитесь, что при копировании Вы получили нули. Почему?

Отредактируйте формулу в ячейке G2, изменив ссылку на ячейку J2 на абсолютную($J$2), поставив перед адресом ячейки в формуле курсор и нажав на клавишу F4. Заново скопируйте формулу. Сделайте выводы.

Используя простейшие математические и статистические формулы, в первой строке под таблицей определите общую стоимость товаров и общую сумму премии. В следующих строках определите максимальную и минимальную цены товаров, среднюю стоимость, средний размер премии, количество продаж (См. функции СУММ(), МАКС(),МИН(),СРЗНАЧ(), СЧЕТ()).

Распечатайте таблицу Продажи в числовом и формульном виде. Для перехода в формульный вид выполните Сервис/Параметры/Вид – установить флажок Формулы. В формульном виде установите режим отображения заголовков строк и столбцов (Файл/Параметры страницы – вкладка Лист, флажок Заголовки строк и столбцов или Просмотр печати/Страница).

Лабораторная работа № 3. Работа с листами. Функция ЕСЛИ. Логические функции И, ИЛИ

1. Переименуйте листы (выполните двойной щелчок по имени листа и введите новые названия соответственно Менеджеры, Прайс, Продажи ).

Добавьте три новых листа (Вставка / Лист). Назовите их П1, П2 и П3. Скопируйте заполненную часть таблицы «Продажи». Сгруппируйте новые листы (щелчками по корешкам листов при нажатой клавиши CTRL).

3. Вставьте на сгруппированные листы скопированную часть таблицы.

4. На всех сгруппированных листах вставьте новую колонку «Цена со скидкой» после колонки «Цена».

5. Измените формулу для Стоимости, заменив в ней ссылку на столбец «Цена» ссылкой на столбец «Цена со скидкой».

6. Скопируйте данные таблицы и вставьте их столько раз, чтобы получилось не менее 20 строк.

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

8. Измените даты продаж на листе П1 так, чтобы они начинались с 15 ноября до 31 декабря с интервалом в 4 дня, а на листе П2 с 15 февраля до 1 апреля. Дополните недостающие данные таблицы копией исходных данных этой же таблицы.

9. Определите цену со скидкой на листе П1 и П2 по таким правилам:

· Если дата продажи больше 20 декабря, то цена снижается на 10%, а иначе она равна обычной цене. (Предварительно в одну из свободных ячеек занесите, например в M1, занесите дату 20.12.2011). Формула для вычисления цены со скидкой должна иметь следующий вид:

=ЕСЛИ(A2>$M$1;E2*0,9;E2)

· Если дата продажи попадает в интервал от 1 до 8 марта, то цена снижается на 12%, а иначе она равна обычной цене. Соответствующие даты занесите в ячейки М2 и М3.

=ЕСЛИ(И(A2>=$M$2;A2<=$M$3;E2*88%;E2)

10. На листе П3 удалите все данные под шапкой, а затем скопируйте последовательно все данные с листа П1 и листа П2 (без шапок, разумеется). Удалите формулы из столбца «Цена со скидкой» и напишите универсальную формулу для вычисления цены со скидкой при условии, что скидка в 10 процентов устанавливается при продаже товара в период от 20/12/2011(ячейка М1 )до 31.12.2011 (ячейка М2) и от 1.03.2011(ячейка М3) 8.03.2011 (ячейка М4).

=ЕСЛИ(ИЛИ(И(A5>=$M$3;A5<=$M$4);И(A5>=$M$1;A5<=$M$2));E5*90%;E5)

Распечатайте листы П1-П3 в числовом и формульном виде.

Лабораторная работа № 4. “Совместная обработка таблиц”

1. На листе Продажи удалите данные столбца «Цена товара».

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

ВПР(B2;Прайс!$A$2:$C$6;2;0).

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

3.В первую ячейку столбца Процент премии за продажу введите формулу для поиска нужного значения на листе Прайс в зависимости от наименования изделия

=ВПР(B2;Прайс!$A$2:$C$6;3;0)

4.Определите размер премии в рублях (стоимость*процент премии)

5.После столбца «Стоимость,р» вставьте столбец «Стоимость,$».

6.На свободном пространстве листа Продажи создайте новую таблицу со значениями курса доллара в зависимости от даты. Введите даты, которые входят в созданный Вами диапазон дат в таблице Продажи, но не все, и не точно совпадающие с ними, например, как показано ниже (диапазон ячеек M1:N4). Вы можете разместить эту таблицу в другом месте)

	M	N
	Дата	Курс $
	1.10.2008	25,03
	17.10.2008	25.21
	1.11.2008	25.62

7. Используя функцию ВПР, произведите неточный поиск курса доллара и определите стоимость в долларах по следующей формуле (у Вас могут быть другие адреса ячеек)

=F2/ВПР(A2;$M$1:$N$5;2;1)

Лабораторная работа № 5. «Промежуточные итоги. Сводная таблица»

1. Отсортируйте таблицу Продажи по Наименованию товара и менеджерам (выделите всю таблицу с шапкой, выполните команду Данные/Сортировка-Наименование товара, затем по – Фамилия менеджера).

2. Найдите общую стоимость продаж по каждому товару с рублях и долларах (Выделить всю таблицу с шапкой, выполнить команду Данные /Итоги. В списке «При каждом изменении в» выбрать – Наименование товара), в области «Операция» выбрать «Сумма», в области «Добавить итоги по» проставить флажки в нужных столбцах (см рис.1).

3. Выделите всю таблицу с итогами и выполните команду Данные/Итоги еще раз, выбрав в первом списке фамилии менеджеров. Результат распечатайте.

4. Уберите все итоги (Данные/Итоги/Убрать все). Самостоятельно найдите итоги продаж на каждую дату. Сделайте распечатку.

5. С помощью сводной таблицы найдите сумму премии каждого менеджера Для этого выберите команду Данные /Сводная таблица.

a. В первом окне Мастера установите переключатель «В списке или базе данных Microsoft Excel». Нажмите кнопку «Далее».

b. Во втором окне укажите диапазон ячеек таблицы, включая шапку.

c. На третьем листе установите переключатель «Новый лист» и щелкните на кнопке «Макет».

d. В окне макета в область «Строка» перенесите поле «Фамилия менеджера», в область «Данные» - поле: «Премия менеджера,р», Нажмите на кнопку ОК. Затем на кнопку «Готово».

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

Рис.1

Лабораторная работа № 6. «Фильтрация данных»

1. Используя автофильтр (Данные/Фильтр/Автофильтр), показать:

a. список товаров определенного наименования;

b. список товаров, стоимость продаж которых находится в интервале от … до ….;

c. список товаров, проданных двумя менеджерами.

2. Постройте таблицы условий и выполните фильтрацию с помощью расширенного фильтра для решения следующих задач:

a. В отдельной таблице, содержащей такие же столбцы, как в таблице Продажи, показать строки для товаров трех различных наименований, в которых стоимость лежит в пределах от … до …;

b. В отдельной таблице, содержащей столбцы Дата продажи, наименование товара, показать товары, проданные на три определенные даты.

c. Показать список товаров, стоимость продаж которых выше средней стоимости продаж по всем строкам таблицы.

3. Используя функции базы данных и таблицы условий, построенные ранее, решить следующие задачи:

a. Найти общую стоимость продаж трех различных товаров, в которых стоимость лежит в пределах от … до …(БДСУММ);

b. Найти, сколько было фактов продаж за три определенных даты (БСЧЕТА);

c. Найти максимальную стоимость в рублях для тех товаров, стоимость продаж которых в рублях выше средней стоимости товаров в рублях (ДМАКС).

d. Построить дополнительную таблицу условий и определить максимальную стоимость продаж в долларах для определенного товара (ДМАКС).

e. Найти фамилию менеджера, продавшего определенный товар на наибольшую сумму в долларах (БИЗВЛЕЧЬ).

Лабораторная работа № 7. «Макросы: Создание и выполнение»

1. Добавьте новый лист перед первым, присвойте ему имя «Меню».

2. Создайте макросы для перехода на каждый из рабочих листов. Для этого:

¨ Выполните команду меню Сервис – Макрос - Начать запись.

¨ В окне диалога укажите имя макроса, например, «Продажи» , назначьте клавишу для вызова, используя латинскую букву «p».

¨ Перейдите на лист «Продажи».

¨ Нажмите кнопку «Остановить запись» в панели инструментов «Visual Basic» или выполните команду меню Сервис – Макрос – Остановить запись.

Вернитесь на лист «Меню».

Выполните созданный макрос используя команду меню Сервис - Макрос - Макросы - <Продажи> - Выполнить. Вернитесь на лист «Меню». Выполните созданный макрос еще раз с помощью комбинации клавиш Ctrl - p.

3. Создайте еще один макрос «Товары», включив следующие действия:

¨ переход на рабочий лист Продажи, где формировалась область критериев для фильтрации;

¨ Фильтрацию данных с помощью расширенного фильтра по критерию, созданного в пункте 2a лабораторной работы 6;

4. Назначьте макросам управляющие кнопки. Для этого выполните следующие действия:

¨ Перейдите на лист «Меню» .

¨ Выведите панель инструментов Вид – Панели инструментов - Формы.

¨ Нажмите на элемент «Кнопка» и щелкните в том месте листа, где нужно разместить элемент.

¨ В окне диалога «Назначить макрос » выберите макрос «Продажи», нажмите ОК.

¨ Переименуйте кнопку соответственно действию макроса.

¨ Повторите перечисленные действия для остальных макросов.

Выполните настройку листа, используя команду меню Сервис – Параметры.

В окне диалога, на вкладке Вид сбросьте флажки:

§ «Строка формул»;

§ «Строка Состояния»

§ «Сетка»

§ «Заголовки строк и столбцов»

Лабораторная работа № 8. «Построение диаграммы»

1. На листе «Менеджер» постройте круговую диаграмму, отображающую процентное соотношение оклада каждого менеджера от общей суммы окладов. Для этого выделите таблицу вместе с шапкой, а затем запустите мастер диаграмм кнопкой на стандартной панели инструментов или командой Вставка/Диаграмма.

2. Измените размер диаграммы, отформатируйте заголовок. Сделайте распечатку.

3. Постройте гистограмму на отдельном листе, на которой покажите стоимость продаж в рублях на каждую дату продажи (по таблице Продажи ).

4. Перейдите на лист диаграммы. Добавьте новый ряд данных, например «Стоимость продажи,$» (контекстное меню-Исходные данные-вкладка Ряд-кнопка Добавить, в область Имя рядавставить адрес элемента шапки столбца, в область Значения –диапазон ячеек со значениями столбца). Распечатайте диаграмму.

5. Измените вид диаграммы на объёмную. (Контекстное меню-Тип диаграммы). Выполните поворот так, чтобы наиболее удобно представить данные для восприятия (Контекстное меню –Объемный вид).

6. По результатам сводной таблицы (постройте диаграмму итоговых данных с помощью команды контекстного меню «Сводная диаграмма». Данную диаграмму тоже распечатать.