Эксперт

Сергей

Задать вопрос

Мы готовы помочь Вам.

Упражнение 1. Основные приемы работы с таблицами Excel

1) Сохранить файл под именем «Упражнение1.xls» в своей рабочей папке. В клетке A1 записать заголовок «Ведомость выдачи зарплаты за февраль», в клетке A2 – «Фамилия», B2 – «Оклад», C2 – «Аванс», D2 – «Налог», E2 – «На руки». Заполните таблицу заданного вида.
Фамилия Оклад Аванс Налог На руки
Кивин 5000
Чигин 8000
Сотов 2000
Ситов 6000
Пырьев 3000
Чагин 4000
Перов 7000
Котов 9000
Сычев 10000
Петров 11000

2) В ячейку А30 ввести текст «Минимальный размер оплаты тру-да», а в ячейку В30 ввести действующий норматив минимального размера оплаты труда, например, 600.
3) Выполнить расчет незаполненных граф. Расчет производить сле-дующим образом:
a) аванс составляет 40% от оклада;
b) подоходный налог составляет 13% от разницы между окла-дом и минимальным размером оплаты труда;
c) последняя графа равна разнице между окладом и всеми удержаниями.
4) Задать для ячеек с числовой информацией формат Денежный. В случае необходимости увеличить ширину столбцов.
5) Вставить новый столбец перед столбцом с фамилиями «Номер п/п». Используя автозаполнение, автоматически пронумеровать фамилии.
6) Отсортировать таблицу по фамилиям.
7) Используя фильтрацию, определить фамилии сотрудников, име-ющих оклад менее 6000, более 6000, оклад в пределах от 5000 до 10000.
8) Снять фильтрацию. Осуществить и проверить действие закрепле-ния областей таблицы (закрепить шапку и боковик таблицы). Снят0ь за-крепление.
9) В клетку А13 записать слово «Итого». Выполнить расчет итого-вой строки, воспользовавшись для этого кнопкой «Автосумма».
10) В клетку А14 записать слово «Среднее значение», слово «Максимальное значение» — в клетку А15, слово «Минимальное  значе-ние» – в клетку А16. Просмотреть зависимости формул (влияющие и за-висимые ячейки).Убрать все зависимые стрелки. Выполнить соответству-ющие расчеты для столбцов «Оклад» и «На руки».
11) Выполнить условное форматирование в столбцах «Оклад» и «На руки», для чего необходимо разбить весь диапазон значений ячеек на три диапазона: от 0 до 5000, от 5000 до 10000 и свыше 10000, и назна-чить каждому диапазону желтый, оранжевый и зеленый цвета соответ-ственно. Показать работу преподавателю.
12) Перед столбцом «Оклад» вставить два столбца: «Дата по-ступления на работу» и «Стаж». Столбец «Дата поступления на рабо-ту» заполнить произвольно, а столбец «Стаж» рассчитать по зависимости:
=(СЕГОДНЯ()-«Дата поступления на работу»)/365,25.
13) Для столбца «Оклад» установить проверку данных с по-мощью меню Данные\Проверка, исключив возможность вводить числа окладов менее, например, 2000. Проверить срабатывемость проверки, вво-дя данные иного диапазона.
14) Перед столбцом «Налог » вставить столбец «Премия». Данные в этот столбец занести по условию:
если стаж больше 10 лет, то «Премия»= «Оклад»*«Стаж»/50.
15) Отредактировать формулы в столбцах «Налог» и «На ру-ки» с учетом добавлений к окладу в виде премии.
16) Вставить два столбца после фамилии сотрудника и записать туда имя и отчество сотрудника. Расчертить таблицу, как показано на ри-сунке.
17) Задать для текста ячеек шапки таблицы формат Полужир-ный, Перенос по словам, Выравнивание по центру (по горизонтали и по вертикали).
18) Заголовок таблицы «Ведомость выдачи зарплаты» распо-ложить по центру всей таблицы.
19) Подготовить документ к печати: ввести в верхний колонти-тул свою фамилию, а в нижний – вставить текущую дату.
20) Вставить, если его нет,  дополнительный лист «К выдаче».
Номер Фамилия И. О. На руки Подпись

В него скопировать заголовок исходной таблицы и заполнить ее шапку так, как указано на рисунке.
Столбец «Фамилия И. О.» заполнить по формуле

=Лист1!B3 &» «& ЛЕВСИМВ (Лист1!C3;1) &».»&» «& ЛЕВСИМВ (Лист1!D3;1) &».».

Столбец «На руки» заполнить по формуле =Лист1!I3, скопировав его значение из первого листа.
Ведомость выдачи заработной платы за январь.
Номер Фамилия И. О. На руки Подпись
1Кивин К. К.4350
2Чигин С. С.6960
3Сотов П. С.1740
4Ситов П. С.5220
5Пырьев П. С.2610
6Чагин П. С.3480
7Перов П. С.6090
8Котов П. С.7830
9Сычев П. С.8700
10Петров П. С.9570
Итого56550

21) Проверить полученный результат в режиме Предваритель-ного просмотра.
22) Сохранить документ и выйти из Excel.

Упражнение 2

Создать новую книгу и ввести указанную таблицу:
• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф расположение по центру столбца; переопределить ширину (Номер п/п) и (Показатели);
• названия граф (Номер п/п) и (Итого за год) ввести в две ячейки;
• названия графы (Показатели) расположить в две ячейки сверху вниз, значение ячейки (Год) отцентрировать по кварталам.
1. Расчертить таблицы, как указано на рисунке.
2. Автоматически пронумеровать все показатели в столбце А, задать для чисел столбца А выравнивание по центру столбца.
3. Выполнить все необходимые расчеты:
• данные по строке «Валовая прибыль» рассчитать как разность между торговыми доходами и торговыми расходами;
• данные по строке «Общие затраты» получить как сумму трех предыдущих строк;
• данные по строке «Производственная прибыль» получить как разность между валовой прибылью и общими затратами;
• данные по строке «Удельная валовая прибыль» получить как результат деления производственной прибыли на торговые доходы;
• данные в колонке «Итого за год» получить суммированием квар-тальных данных.
4. Задать для строки «Удельная валовая прибыль» Процентный формат, а для всех остальных строк – Формат с разделителями.
5. На строке 1 вести заголовок, задав для него более крупный жир-ный шрифт и расположение по центру всей таблицы.
6. Зафиксировать титулы таблицы (верхнюю и боковую шапки).  Перемещением по таблице отобразить на экране данные трех столбцов: Но-мер п/п, Показатели, Итого за год. Снять закрепление областей.
7. По данным Торговых расходов фирмы за первые три квартала построить разрезанную круговую диаграмму. Добавить в диаграмму данные по 4 кварталу.
8. По данным Валовой прибыли построить гистограмму. Оформить заголовки и легенды диаграмм, расположить диаграммы рядом под таблицей.
9. Изменить числовое значение за 4 квартал по Торговым расхо-дам фирмы. Проследить зависимость графических данных в диаграммах от числовых в таблице.
10. На отдельном листе построить нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу Итого за год).

Структура доходов и расходов фирмы
Но-мер п/п Показатели Год Итого за год
1 кв. 2 кв. 3 кв. 4 кв.
1 Продано единиц 3592 4390 3192 4789
2 Торговые доходы 143662 175587 127700 191549
3 Торговые расходы 89789 109742 79812 119712
4 Валовая прибыль
5 Расходы на зарплату 8000 8000 9000 9000
6 Расходы на рекламу 10000 10000 10000 10000
7 Накладные расходы фирмы 21549 26338 19155 28732
8 Общие затраты
9 Производственная прибыль
10 Удельная валовая прибыль

Упражнение 3.

1. Создать новую книгу и ввести указанную таблицу, начиная с тре-тьей строки:
• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф расположение по центру столбца;
• текст «Объем страховых сделок» расположить по центру четырех столбцов;
• названия граф «№ п/п», «Комиссионное вознаграждение» рас-положить в две ячейки по вертикали.
• для граф «№ п/п», «за 3 квартал», «Комиссионное вознаграж-дение»- задать перенос по словам;
• названия граф «июль», «август», «сентябрь» ввести автозапол-нение.
2. Ввести фамилии, после этого переопределить ширину столбца В, сделав его равным максимальной длине фамилии.
3. Перед заполнением названий итоговых показателей внизу указать для соответствующих им ячеек перенос по словам. Возможно, несколько пе-реопределить ширину столбца В. Ввести числовые данные в столбцы D, E и F. Автоматически пронумеровать все фамилии в столбце А.
4. Диапазонам ячеек со значениями данных за ИЮЛЬ, АВГУСТ, СЕНТЯБРЬ определить соответствующие названиям месяцев имена.
5. Сделать сортировку всей таблицы по фамилиям (без столбца А).
6. Вычислить для каждого страхового агента объем сделок за 3-й квартал как сумму сделок за июль, август и сентябрь.
7. Рассчитать сводные показатели (расположенные в нижней части таблицы) по соответствующим функциям.
8. Ввести тарификационную таблицу, задав для столбца С процент-ный формат. Рассчитать данные в столбце G, воспользовавшись функцией ПРОСМОТР и тарификационной таблицей.
9. Ввести текст примечаний в ячейки с фамилиями агентов, имею-щих максимальный и минимальный объем сделок за третий квартал.
10. Справа от графы Комиссионное вознаграждение рассчитать ранг каждого  страхового агента по комиссионному вознаграждению и процентную норму ранга.
11. Разграфить таблицу, написать заголовок по центру таблицы более крупным шрифтом.

Отчет о деятельности страховых агентов
N п/п Фамилия Объем страховых сделок Комисси-онное воз-награжде-ние
за III квар. июль август сентябрь
1 Антонов 780 370 410
2 Антонов 965 420 75 470
3 Борисов 780 360 420
4 Владов 790 410 380
5 Громов 470 50 420
6 Егоров 990 360 290 340
7 Иванов 910 400 120 390
8 Орлов 650 290 360
9 Смирнов 840 350 210 280
10 Федоров 720 250 200 270
Всего
Ср. объем сделок
Макс. объем
Мин. объем
Колич. не отчитав-шихся

Тарификацион-ная таблица
200 8%
300 10%
400 12%
500 15%
600 18%
800 20%
1000 25%

Упражнение 4. Консолидация данных

1. В новой рабочей книге переименуйте Лист 1 в 1 квартал. Аналогично переименуйте листы (2) в 2 квартал, (3) в 3 квартал, (4) в 4 квартал, лист 5 в Итого За год.
2. Выделите листы 1 квартал, 2 квартал, 3 квартал, 4 квар-тал, введите таблицу:
Автосалон «Счастливого пути»
Марка Количество
Мерседес 190 47
Москвич 22
Опель 17
Пежо 201 24
Волга 29
Мицубиси 45
Форд 47
БМВ 520 63
Рено Сафран 22
Ауди 100 44
3. Измените данные  в таблицах за 1, 2, 3, 4 кварталы.
4. Выполните консолидацию данных для чего:
Скопируйте шапку таблицы «Счастливого пути» в лист Итого За Год в ячейку А2. Щелкните по ячейке A2.
Выполните команду меню Данные / Консолидация.
В поле Функция выберите Сумма.
Установите курсор в поле Ссылка, перейдите на лист  1 Квартал, выделите диапазон ячеек, содержащих только названия машин и их количе-ство, щелкните по кнопке Добавить  в окне Консолидация.
Аналогичные действия выполните для листов 2, 3 и 4 квартал. Об-ратите внимание, что при переходе к новому листу в поле Ссылка уже будет находиться указанный диапазон.
Проверьте в поле Список диапазонов наличие всех 4-х диапазонов.
Уставите флажки Использовать в качестве имен значения левого столбца и Создать связи с исходными данными. Щелкните по кнопке ОК.
— Перейдите на лист Итого за год.
— Измените цифры на листе 1 квартал и проверьте изменения на листе Итого за Год.