Прибыль филиалов условной фирмы

От коммерческой деятельности в условных единицах

			…
	Цех 1	0,5
Введите 9 граф Введите 10 строк	…
	Цех 10	1,3

 

7. Переместите новые данные на столбиковую диаграмму пятью способами (с помощью буфера обмена, контекстное меню, перетаскивание, меню Диаграмма).

8. Поменяйте цвет одного из сегментов столбиковой диаграммы. Уменьшите размер шрифта у заголовков осей и у легенды. Заголовок для оси Z расположите вертикально.

 

 

ЗАДАНИЕ 7

(после завершения задания выполнить один пункт из дополнительного задания)

 

1. Откройте файл ТАВ2. Добавьте новый рабочий лист Лист9. Переместите Лист9 после Лист8. Перейдите на Лист9 и создайте базу данных по учету кадров (Таблица 1).

2. Установите автофильтр и выполните отбор информации в Таблице 1:

2.1. Список сотрудников, ФИО которых начинается с символа “К”

2.2. Список сотрудников, женатых, у которых два и более иждивенцев

2.3. Список сотрудников, которые старше 30 лет

3. Используя исходную таблицу по базе данных, создайте (путем копирования) таблицы для ОБЛАСТИ УСЛОВИЙ и для ЦЕЛЕВОЙ ОБЛАСТИ (расположите их ниже Таблицы 1).

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

4.1. Список сотрудников, ФИО которых начинается с символа “П”

4.2. Список сотрудников-экономистов ИЛИ сотрудников-менеджеров;

4.3. Список сотрудников, у которых стаж работы менее 10 лет;

4.4. Список сотрудников-менеджеров, которые женаты;

4.5. Список сотрудников, у которых количество иждивенцев больше трех.

5. Отсортируйте исходную таблицу одновременно по ФИО (по возрастанию) и по Дате рождения (по убыванию).

Таблица 1

ФИО	Дата рождения	Дата приема на работу	Должность	Семейное положение	Количество иждивенцев
А	Б	В	Г	Д	Е
Петров А.В.	14.02.63	01.11.85	Директор	женат
Носов Е.Н.	01.05.77	25.03.95	Гл.бух-р	женат
Сидоров Г.А.	14.03.73	21.04.91	Зам.дирек.	не женат
Поспелов Т.В.	18.11.67	15.02.88	Экономист	женат
Уварова Г.И.	25.09.69	17.02.93	Уборшица	замужем
Иванов Л.М.	17.05.74	17.02.93	Экономист	не женат
Ковалева А.К.	06.12.65	03.04.87	Бухгалтер	не замужем
Кирова С.Н.	12.07.79	20.04.96	Экономист	замужем
Коновалов М.Н.	03.01.63	26.04.84	Менеджер	женат
Потапов К.Г.	15.08.68	12.06.87	Кассир	не женат
Сидорова Т.А.	19.11.75	14.06.94	Менеджер	не замужем

 

 

ЗАДАНИЕ 8

 

1. Откройте файл ТАВ2. Добавьте три новых рабочий листа Лист10, Лист11, и Лист12. Переместите эти листы после Лист9.

2. Выполните группировку трех новых листов. Введите и заполните следующую таблицу (Таблица 1). Проследите заполнение информацией сразу всех трех листов.

Таблица 1

Распределение занятого населения по формам собственности в _____ году

№№	Страны	Всего	в том числе, в процентах:
п/п		занято в экономике, тысяч человек	государств. предприя-тия и орга- низации	част- ный сек- тор	общест- венные органи-зации, фонды	сов- местные пред- приятия	предприятия со смешанной формой собстствен-ности и другие
А	Б	В	Г	Д	Е	Ж	З
1.	Азербайджан
2.	Армения
3.	Беларусь
4.	Грузия
5.	Казахстан
6.	Кыргызстан
7.	Латвия
8.	Литва
9.	Молдова
10.	Россия
11.	Таджикистан
12.	Туркменистан
13.	Узбекистан
14.	Украина
15.	Эстония

 

Примечание: сумма граф 4, 5, 6, 7, 8 равна 100 (%) по каждой строке.

Значения в строках граф 3, 4 могут повторяться.

 

3. Отмените группировку листов и заполните таблицы данными по “Распределению занятого населения...” за 1997, 1998 и 1999 годы.

4. Добавьте новый лист и скопируйте в него только шаблон таблицы (т.е. названия строк и граф), выделите блок для размещения консолидированных данных (ячейки для данных) и выполните консолидацию таблиц по “Распределению...” за все введенные годы, используя функцию СРЕДНЕЕ (меню Данные).

5. Для ячеек граф 2, 3, 4, 5 постройте СВОДНУЮ ТАБЛИЦУ (меню Данные) и разместите:

· графу 2 в строках СВОДНОЙ ТАБЛИЦЫ;

· графу 3 в строках СВОДНОЙ ТАБЛИЦЫ;

· графу 4 в данных СВОДНОЙ ТАБЛИЦЫ;

· графу 5 в странице СВОДНОЙ ТАБЛИЦЫ.

6. Выполните фильтрацию данных в СВОДНОЙ ТАБЛИЦЕ для разных значений графы 5 (проследите изменения в таблице).

 

 

ЗАДАНИЕ 9

 

 

1. Создайте новую рабочую книгу с именем файла МАКРОС.XLS

2. Перейдите на лист Лист1 и переименуйте его в Меню.

3. Включите запись нового макроса и присвойте ему имя Макрос1.

4. Перейдите на лист Лист2. Остановите запись макроса.

5. Включите запись нового макроса и присвойте ему имя Макрос2.

6. Перейдите на лист Меню. Остановите запись макроса.

7. Создайте на листе Лист2 кнопку «ВЫХОД» и свяжите ее с Макросом2 (включите панель ФОРМЫ).

8. Создайте на листе Меню кнопку «ПЕРЕХОД на Лист2» и свяжите ее с Макросом1.

9. Отработайте с помощью кнопок переход между листами.

10. На листе Меню включите запись нового макроса и присвойте ему имя Макрос3.

11. Выполните переход на пустой лист Лист3. Выделите сразу все ячейки и удалите их. Введите в ячейку В1 текст: Отчет по итогам работы. Установите для этого текста цвет: СИНИЙ, размер: 14. Увеличьте ширину графы В. Остановите запись макроса!

12. Создайте на листе Лист3 кнопку «ТЕСТ» и свяжите ее с макросом Макрос3.

13. Измените в тексте макроса Макрос3 для текста Отчет по итогам работы: цвет на КРАСНЫЙ, размер на 16, ширину графы В на 30 пунктов. Апробируйте изменения с помощью кнопки «ТЕСТ».

Дополнительные задания по Excel

(выполнять следует по одному пункту после сдачи

основного задания преподавателю)

Функции МАТЕМАТИЧЕСКИЕ è к заданию № 4

Таблица 1:

Стоимость	Скидка	Расчет
	0,005			Результат
	5,002
	7,003
	10,001

 

1. Определите ТРЕТЬЕ наибольшее значение в графе Стоимость и занесите его в ячейку Результат.

2. Округлите в графе Расчет значения графы Скидка до ближайшего нечетного целого.

3. Выделите дробную часть от произведения значений графы Стоимость и Скидка; поместите результат в графу Расчет.

 

Функция ЕСЛИ è к заданию № 5

Таблица 1:

Наименование	Стоимость	Срок службы	Поставщик	Дата	Износ
Шкаф			ООО “Label”	01.05.95
Сервант			ЗАО “Week”	05.06.95
Стол			OOO “Live”	23.06.96
Диван			ТОО “Info”	15.07.97

 

1. Заполнить графу “Износ” по формуле: если стоимость больше 1000 и срок службы больше 3 лет, то рассчитать 10% стоимости, иначе если срок службы меньше 3 лет, то рассчитать 30% стоимости, иначе рассчитать 50% стоимости.

 

2. Заполните графу “Износ” по формуле: если год в дате больше 1995 и меньше 1997, то рассчитать 40% стоимости, иначе если срок службы больше 3, то рассчитать 30% стоимости, иначе рассчитать 60% стоимости.

 

3. Заполните графу “Износ” по формуле: если стоимость больше 1000 и меньше 2000, то рассчитать 30% стоимости, иначе если срок службы больше 2, то рассчитать 40% стоимости, иначе рассчитать 50% стоимости.

 

4. Заполните графу “Износ” по формуле: если день в дате больше 10 и месяц в дате меньше 9, и год в дате больше 1995 то рассчитать 10% стоимости, иначе если месяц в дате больше 5, то рассчитать 30% стоимости, иначе рассчитать 20% стоимости.

 

5. Заполните графу “Износ” по формуле: если срок службы больше 2 и меньше 6, то рассчитать 20% стоимости, иначе если стоимость больше 1500, то рассчитать 30% стоимости, иначе рассчитать 50% стоимости.

 

6. Заполните графу “Износ” по формуле: если частное от деления стоимости на срок службы больше 170 и меньше 800, то рассчитать 20% стоимости, иначе если стоимость больше 1000, то рассчитать 30% стоимости, иначе рассчитать 10% стоимости.

 

Функции ПРОСМОТР или ВПР è к заданию № 6

Таблица 1:

Табельный номер	Ставка	Премия, % от ставки	ФИО	Дата зачисления
		50%	Назаров	03.04.85
		55%	Богдан	23.04.96
		60%	Рябов	10.06.80
		50%	Летягин	18.03.83
		40%	Кочнева	24.02.85

 

Таблица 2:

Табельный номер	Обращение	Начислено

 

1. Заполните графу “Обращение” по формуле: если ФИО равна “Кочнева”, то вывести текст “госпожа”, иначе вывести “господин”.

 

2. Заполните графу “Начислено” по формуле: если стаж работы (год из текущей даты минус год из даты зачисления) больше 5 лет, то рассчитать Ставка + Премия (Ставка * (1 + %Премии / 100)), иначе вывести Ставку.

 

3. Заполните графу “Обращение” по формуле: если Премия больше 50%, то вывести “почетный железнодорожник”, иначе - “сотрудник”.

 

4. Заполните графу “Начислено” по формуле: если Ставка больше 300, то рассчитать Ставка + Премия (Ставка * (1 + %премии * 2 / 100)), иначе вывести Ставку.

 

Функции текстовые è к заданию № 7

Таблица 1:

Фирма	Директор	Сфера деятельности	Телефон	Обращение
ОООО Валга	Иванов Иван Петрович	Компьютеры	12-12-3
КБ Торибанк	Сидоров Дмитрий Федорович	Кредиты	245-46-65
ОООО Сизонс	Алексеев Виктор Николаевич	Туризм	45-64-5
КБ Оргбанк	Маслов Леонид Кириллович	Галантерея	54-64-32

 

1. Заполните графу “Обращение” по формуле: если фирма начинается с букв “КБ”, то вывести “А” + Фирма (функции ЛЕВСИМВ(), СЦЕПИТЬ()), иначе вывести Фирму.

 

2. Заполните графу “Обращение” по формуле: если фирма начинается с букв “ОООО”, то убрать первую букву Фирмы (функции ЛЕВСИМВ(), ПСТР(), ДЛСТР()), иначе вывести Фирму.

 

3. Заполните графу “Обращение” по формуле: если длина телефона равна 9, то вывести “(095) ” + Телефон (функции ДЛСТР(), СЦЕПИТЬ()), иначе вывести Телефон.

 

4. Заполните графу “Обращение” по формуле: если сфера деятельности заканчивается на “ы”, то убрать последнюю букву Сферы деятельности (функции ПРАВСИМВ(), ПСТР(), ДЛСТР()), иначе вывести Сферу деятельности.

 

Итоговое задание по курсу (Excel) ( лабораторная работа ).

 

Требования к лабораторной работе

 

1. Разработать самостоятельно предметную область для реализации условной экономической задачи.

Примерные темы:

1. Система учета книжной продукции.

2. Система учета продажи обуви.

3. Система учета медикаментов.

4. Система учета компьютерной техники.

5. Система учета оргтехники.

6. Система учета покупки-продажи квартир.

7. Система учета продажи автомобилей.

8. Система учета продажи собственности за границей.

9. Система учета продажи мебели.

10. Система учета реализации табачной продукции.

11. Система учета реализации винно-водочной продукции.

12. Система учета выполнения ремонта квартир.

13. Система учета закупок обуви за границей.

14. Система учета продажи сотовых телефонов и пейджеров.

15. Система учета товаров на складе хладокомбината.

2. Разработать 2 (две) справочные таблицы (5 граф, 15 строк), включив в них постоянные реквизиты (например, Название, Оптовая цена и т.п.). Упорядочить данные по выбранному полю (например, по № п/п). Названия граф в справочных таблицах не должны повторяться.

3. Разработать 1 (одну) таблицу с исходными данными (10 граф, 15 строк), включив в нее как постоянные, так и переменные реквизиты (например, Кол-во, Дата и т.п.). Добавить в таблицу необходимые поля для связи со справочными таблицами (например, № п/п).

3.1. Включить в одну из граф условный формат для ячеек (не менее 3-х условий).

4. Сформировать 1 (одну) итоговую таблицу (на основе данных исходных и справочных таблиц) с включением в нее:

4.1. Функции ЕСЛИ(), включая сложные функции И(), ИЛИ();

4.2. Функции ПРОСМОТР() или ВПР(), или ГПР();

4.3. Функции СЧЕТ() или СЧЕТЕСЛИ();

4.4. Функции СУММ() или СУММЕСЛИ().

5. Выполнить по итоговой таблице (предварительно рекомендуется создать требуемое количество копий на пустых листах):

5.1. сортировку по возрастанию всех данных по одному из столбцов;

5.2. фильтрацию данных по двум условиям одновременно (И, ИЛИ);

5.3. подведение итогов по кодовым полям с получением промежуточного итога;

5.4. сформировать итоговую таблицу в режиме просмотра формул;

5.5. сформировать сводную таблицу по таблице с итоговыми данными.

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

 

Содержание отчета по лабораторной работе

 

 

1. Введение (преимущества электронных таблиц EXCEL)

2. Постановка задачи (тема, описание структур таблиц, требования к размерам таблиц).

3. Распечатка таблиц с данными (добавить комментарии по каждой таблице).

4. Распечатка таблиц с формулами (добавить комментарии по получению листа с формулами).

5. Описание функций (каждую функцию рассмотреть на примере) и условного формата.

6. Распечатка итоговой таблицы (представить таблицу: 1) после сортировки и подведения итогов; 2) после сортировки и фильтрации по условиям; добавить комментарии).

7. Распечатка сводной таблицы (без фильтра и с фильтром).

8. Распечатка диаграмм (диаграммы строить по итоговой таблице после сортировки, добавить комментарии по названиям осей).

9. Заключение (результаты работы, возможные места применения разработанных таблиц, трудности при выполнении работы, личное мнение по использованию возможностей Microsoft Excel).