Правила выполнения практических занятий

Колледж метрополитена

Методические указания

для студентов по проведению

Практических занятий

по

дисциплине

«Информатика и ИКТ»

для студентов всех специальностей

Всех форм обучения

 

 

по теме:

«Электронные таблицы Excel»

 

 

Автор: преподаватель Самара О.П..

ОГЛАВЛЕНИЕ

Введение. 4

Правила выполнения практических занятий. 5

Описание рабочего места студента для выполнения практических занятий. 5

Практическое занятие №1. 6

Тема: Основные понятия Microsoft Excel. Организация расчетов в табличном процессоре Microsoft Excel. 6

Практическое занятие №2. 13

Тема: Ввод формулы. Вставка столбцов. Создание электронной книги. Относительная и абсолютная адресации в MS EXCEL.. 13

Практическое занятие №3. 18

Тема: Базовые информационные технологии. Форматирование ячеек. Связанные таблицы. Расчет промежуточных итогов в таблицах MS EXCEL.. 18

Практическое занятие №4. 23

Тема: Операции копирования, вырезки и вставки. Подбор параметра. Организация обратного расчета. 23

Практическое занятие №5. 29

Тема: Редактирование таблицы.. 29

Практическое занятие №6. 37

Тема: Расчеты в MS EXCEL: Задачи оптимизации (поиск решения) 37

Практическое занятие №7. 43

Тема: Теория относительности адресов. Относительная и абсолютная адресация MS EXCEL 43

Практическое занятие №8. 47

Тема: Именованные блоки. Построение и форматирование диаграмм в MS Excel. 47

Практическое занятие №9. 57

Тема: Работа со списками. Использование функций в расчетах MS EXCEL.. 57

Практическое занятие №10. 65

Тема: Форма для работы со списками. Редактирование таблицы. Фильтрация данных и условное форматирование в MS EXCEL.. 65

Практическое занятие №11. 68

Тема: Агрегирование данных. Связи между файлами и консолидация данных в MS Excel 68

Список рекомендуемой литературы для самостоятельной работы. 72

Введение

Настоящие методические указания по проведению практических занятий предназначены для студентов всех специальностей по дисциплине «Информационные технологии в профессиональной деятельности».

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

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

Практические занятия посвящены углубленному изучению базовых информационных технологий электронных таблиц. Microsoft Excel, используемых в работе специалистами различного профиля (экономистами, финансистами, бухгалтерами… и др.). Электронные таблицы являются идеальной средой для выполнения вычислений различной сложности. В Microsoft Excel включен развитый математический аппарат, специализированные информационные технологии статистического анализа, доступны графические средства представления и анализа данных. Microsoft Excel широко применяется для автоматизации бухгалтерского учета. Документы расчетного характера (разработочные и расчетные таблицы) могут переводиться в электронную форму, сохраняться как шаблоны для многократного использования. Microsoft Excel поддерживает электронный документооборот, обеспечивая создание, описание, поиск и хранение документов сложной структуры, взаимосвязь комплексов расчетных документов..

 

После проведения практических занятий студенты должны:

Знать:

- основные приемы работы с программой Microsoft Excel.

 

уметь:

- вводить информацию в электронные таблицы;

- вводить формулы;

- редактировать формулы;

- пользоваться функциями;

- работать с автофильтром;

- работать с формой;

- применять в работе набор встроенных функций Microsoft Excel;

- решать задачи моделирования и анализа данных;

 

Практическое занятие №1

Тема: Основные понятия Microsoft Excel. Организация расчетов в табличном процессоре Microsoft Excel.

Цель занятия. Изучение информационной технологии исполь­зования встроенных вычислительных функций для финансо­вого анализа.

Порядок работы

1. Запустите редактор электронных таблиц и со­здайте новую электронную книгу. Знак? не ставьте, вместо него далее будем вводить формулы.

Рис. 1.1. Исходные данные для Задания 1.1

 

2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.

3. Для оформления шапки таблицы выделите ячейки на тре­тьей строке A3:D3 и создайте оформление шапки Для этого выполните команду Формат/Ячейки, в открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите Выравнивание текста По горизонтали – По центру и выравнивание текста по вертикали – По середине, после этого нажмите кнопку ОК.

Рис. 1.2. Форматирование ячеек - задание переноса по словам.

4. На третьей строке введите названия колонок таблицы — «Дни недели», «Доход», «Расход», «Финансовый результат», далее запол­ните таблицу исходными данными согласно Заданию 1.1.

 

Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

5. Произведите расчеты в графе «Финансовый результат» по сле­дующей формуле:

Финансовый результат = Доход - Расход,

для этого в ячейке D4 наберите формулу(перед формулой поставьте знак =) = В4-С4.

Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

6. Для ячеек с результатом расчетов задайте формат Деньги с выделением

Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный

Рис. 1.4. Задание формата отрицательных чисел красным цветом.

Рис. 1.5. Выбор функции расчета среднего значения

7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета фун­кции СРЗНАЧ дохода установите курсор в соответствующей ячей­ке для расчета среднего значения (В11), запустите мастер функ­ций (Вставка/Функция/категория — Статистические/СРЗНАЧ) (рис. 1.5). В качестве первого числа выделите группу ячеек с данны­ми для расчета среднего значения — В4:В10.

Аналогично рассчитайте «Среднее значение» расхода.

8. В ячейке D13 выполните расчет общего финансового результа­та (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования () на панели инструментов или функцией СУММ (рис. 1.6). В качестве первого числа выделите группу ячеек с данными для расчета сум­мы — D4:D10.

Рис. 1.6. Задание интервала ячеек при суммировании функцией СУММ

 

9. Проведите форматирование заголовка таблицы. Для этого вы­делите интервал ячеек от А1 до D1, объедините их кнопкой пане­ли инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/вкладка Выравнивание/ отображение — Объе­динение ячеек). Задайте начертание шрифта — полужирное; цвет — по вашему усмотрению.

Конечный вид таблицы приведен на рис. 1.7.

10. Постройте диаграмму (линейчатого типа) изменения фи­нансовых результатов по дням недели с использованием мастера диаграмм.

Для этого выделите интервал ячеек с данными финансового результата и выберите команду Вставка/ Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы — линейча­тая; на втором шаге на вкладке Ряд в окошке Подписи оси X укажите интервал ячеек с днями недели — А4:А10 (рис. 1.8).

Далее введите название диаграммы и подписи осей; дальней­шие шаги построения диаграммы осуществляются автоматичес­ки по подсказкам мастера. Конечный вид диаграммы приведен на рис. 1.9.

11. Произведите фильтрацию значений дохода, превышающих 4000 р.

Краткая справка. В режиме фильтра в таблице видны толь­ко те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты. В этом режиме все операции форматирования, копирования, автозаполнения, и т.д. применяются только к видимым ячейкам листа.

Рис. 1.7. Таблица расчета финансового результата (Задание 1.1)

Рис. 1. 8. Задание Подписи оси X при построении диаграммы.

Рис. 1.9. Конечный вид диаграммы Задания 1.1.

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков Щелк­ните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля Выбе­рите команду для фильтрации — Условие (рис. 1.10)

Рис 1.10. Выбор условия фильтрации

В открывшемся окне Пользовательский авто­фильтр задайте условие «Больше 4000» (рис.1.11.)

Рис. 1.11. Пользовательский автофильтр

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

12. Сохраните созданную электронную книгу в своей папке.

Рис. 1.12. Вид таблицы после фильтрации данных.

 

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 2. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки (рис. 1.13); по ре­зультатам расчета построить круговую диаграмму суммы продаж.

Рис 1.13. Исходные данные для Задания 1.2.

Используйте созданный стиль (Формат/Стиль/Шапка таблиц }. Формулы для расчета:

Сумма = Цена ÏКоличество,

Всего = сумма значении колонки «Сумма».

Краткая справка Для выделения максимального/мини­мального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статис­тические», в качестве первого числа выделите диапазон ячеек зна­чений столбца «Сумма» (ячейки ЕЗ:Е10).

Задание 3. Заполнить ведомость учета брака, произвести рас­четы, выделить минимальную, максимальную и среднюю сум­мы брака, а также средний процент брака; произвести фильтра­цию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 1.14).

Формула для расчета:

Сумма брака = Процент брака Ï Сумма зарплаты.

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/ вкладка Число/ формат — Процентный).

Рис. 1.14 Исходные данные для Задания

Задание 4. Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (ко­личество и сумму); произвести фильтрацию по цене, превышаю­щей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции (рис. 1.15).

Формулы для расчета:

Всего = Безналичные платежи + Наличные платежи;

Выручка от продажи = Цена Ï Всего.

Рис. 1.15. Исходные данные для Задания 1.4.

Содержание отчета

1. Номер практического занятия, тема.

2. Цель занятия.

3. Краткие теоретические сведения.

4. Ответы на контрольные вопросы.

Контрольные вопросы.

1.Перечислите способы перемещения к листам книги EXCEL.

2.Как перейти от одной рабочей книге к другой?

3.Из скольких строк и столбцов состоит лист рабочей книги EXCEL?

Практическое занятие №2

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу (см. рис. 2.1). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. 2.1. Исходные данные для задания 2.1.

Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад х % Премии, в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоми­нанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула

Удержание = Всего начислено х % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5. Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, ми­нимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/Категетория — Статистические функции).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и набе­рите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис. 2.2.

Краткая справка. Каждая рабочая книга Excel может со­держать до 255 рабочих листов. Это позволяет, используя несколь­ко листов, создавать понятные и четко структурированные доку­менты, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользо­ваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окош­ке Создавать копию (рис. 2.3).

Рис. 2.2. Исходный вид таблицы расчета заработной платы за октябрь

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %. Убедитесь, что программа произвела пере­счет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значе­ние доплаты по формуле

Доплата = Оклад х % Доплаты.

Значение доплаты примите равным 5 %.

8. Измените формулу для расчета значений колонки «Всего на­числено»:

Всего начислено = Оклад + Премия + Доплата.

9. Проведите условное фор­матирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 — зеленым цветом шриф­та; меньше 7000 — красным; боль­ше или равно 10000 — синим цве­том шрифта (Формат/Условное форматирование) (рис. 2.4).

10. Проведите сортировку по фа­милиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы — без итогов, выберите меню Данные/Сортировка, сортировать по — Столбец B ) (рис. 2.5).

Рис. 2.4. Условное форматирование данных.

Рис. 2.5. Сортировка данных

11. Поставьте к ячейке D3 комментарии «Премия пропорцио­нальна окладу» (Вставка/Примечание ), при этом в правом верх­нем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис. 2.6.

Рис. 2.6. Конечный вид зарплаты за ноябрь.

12. Защитите лист «Зарплата ноябрь» от изменений (Сервис/За­щита/Защитить лист). Задайте пароль на лист (рис. 2.7), сделайте подтверждение пароля (рис. 2.8).

Рис. 2.7. Защита листа электронной книги.

Рис. 2.8. Подтверждение пароля.

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа ).

13. Сохраните созданную электронную книгу под именем «Зар­плата» в своей папке.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 2. Сделать примечания к двум-трем ячейкам.

Задание 3. Выполнить условное форматирование оклада и пре­мии за ноябрь месяц:

до 2000 р. — желтым цветом заливки;

от 2000 до 10000 р. — зеленым цветом шрифта;

свыше 10000 р. — малиновым цветом заливки, белым цветом шрифта.

Задание 4. Защитить лист зарплаты за октябрь от изменений.

Проверьте защиту. Убедитесь в неизменяемости данных. Сними­те защиту со всех листов электронной книги «Зарплата».

Задание 5. Построить круговую диаграмму начисленной сум­мы к выдаче всех сотрудников за ноябрь месяц.

Содержание отчета

1. Номер практического занятия, тема.
2. Цель занятия.
3. Краткие теоретические сведения.
4. Ответы на контрольные вопросы.

Контрольные вопросы.

1.Как заполнить строку или столбец повторяющимися значениями?

2.Как выполнить автозаполнение арифметической прогрессией?

3.Как автоматически заполнить несколько ячеек подряд в строке или столбце последовательными датами?

Практическое занятие №3

Тема: Базовые информационные технологии. Форматирование ячеек. Связанные таблицы. Расчет промежуточных итогов в таблицах MS EXCEL

Цель занятия. Связывание листов электронной книги. Расчет про­межуточных итогов. Структурирование таблицы.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в ранее файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/Переместить/ Скопировать лист). Не забудьте для копирования поставить галочку в окошке Созда­вать копию.

3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

Рис. 3.1 Ведомость зарплаты за декабрь

4. Измените значение Премии на 46 %, Доплаты — на 8 %. Убеди­тесь, что программа произвела пересчет формул (рис. 3.1).

5. По данным таблицы «Зарплата декабрь» постройте гистог­рамму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 3.2.

6. Перед расчетом итоговых данных за квартал проведите сорти­ровку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь—декабрь.

7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист ). Не забудьте для ко­пирования поставить галочку в окошке Создавать копию.

8. Присвойте скопированному листу название «Итоги за квар­тал». Измените название таблицы на «Ведомость начисления зара­ботной платы за 4 квартал».

Рис.3.2. Гистограмма зарплаты за декабрь.

9. Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 3.3. Для этого удалите в основной таблице (см. рис. 3.1) колонки Оклада и Премии, а также строку 4 с численными значениями % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под ос­новной таблицей. Вставьте пустую третью строку.

10. Вставьте новый столбец «Подразделение» (Вставка/Столбец) между столбцами «Фамилия» и «Всего начислено». Заполните стол­бец «Подразделение» данными по образцу (см. рис. 3.3).

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

Краткая справка. Чтобы вставить в формулу адрес или ди­апазон ячеек с другого листа, следует во время ввода формулы щел­кнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.

В ячейке D5 для расчета квартальных начислений «Всего начис­лено» формула имеет вид:

= 'Зарплата декабрь''!F5 + 'Зарплата ноябрь''!F5 + 'Зарплата октябрь''!E5

Аналогично произведите квартальный расчет «Удержания» и «К выдаче»

Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата» При этом произойдет связывание информации соответствующих ячеек листов электронной книги.

Рис. 3.3. Таблица для расчетов итоговой квартальной заработной платы.

12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержание» и «К вы­даче» достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5(рис.3.4).

Рис. 3.4. Расчет квартального начисления заработной платы, связыванием листав электронной книги.

Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F Ваша электронная таблица примет вид, как на рис.3.4.

13. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям Табли­ца примет вид, как на рис.3.5.

14. Подведите промежуточные итоги по подразделениям, ис­пользуя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (рис.3.6). Задайте параметры подсчета промежуточных итогов:

при каждом изменении в — Подразделение,

операция — Сумма,

добавить итоги по Всего начислено, Удержания, К выдаче

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными»

Примерный вид итоговой таблицы представлен на рис.3.7

15. Изучите полученную структуру и формулы подведения про­межуточных итогов, устанавливая курсор на разные ячейки табли­цы Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

Рис. 3.5. Вид таблицы начисления квартальной платы после сортировки по подразделениям.

Краткая справка. Под структурированием понимается многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.

Рис. 3.6. Окно параметров расчета промежуточных итогов.

 

 

Рис. Итоговый вид таблицы расчета квартальных итогов по зарплате.

16. Сохраните файл «Зарплата» с произведенными изменениями (Файл/Сохранить).

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 2. Исследовать графическое отображение зависимо­стей ячеек друг от друга.

Порядок работы

Скопируйте содержимое листа «Зарплата октябрь» на новый лист Копии присвоите имя «Зависимости» Откройте панель «Зависимости» (Сервис/Зависимости/Панель зависимостей ) (рис.3.8) Изучите назначение инструментов панели, задерживая на них указатель мыши.

Рис. 3.8. Панель зависимостей.

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

Рис. 3.9. Зависимости в таблице расчета зарплаты

Содержание отчета

1. Номер практического занятия, тема.
2. Цель занятия.
3. Краткие теоретические сведения.
4. Ответы на контрольные вопросы.

Контрольные вопросы.

1. Как включить в диаграмму подписи значений для рядов данных?

2. Как изменить размер диаграммы, расположение диаграммы на листе?

3. Как изменить тип диаграммы?

Практическое занятие №4

Порядок работы

1.Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практических работ 2 и 3 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».

3. Осуществите подбор параметра командой Сервис/Подбор параметра (рис. 4.1).

Рис. Задание параметров подбора параметра

Рис. Подтверждение результатов подбора параметра

Рис. 11.3 Подбор значения % Премии для заданной общей суммы заработной платы, равной 250000 р.

В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарп­латы (ячейка G19), на второй строке наберите заданное значение 250000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК.. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК.( рис. 4.2).

Произойдет обратный пересчет % Премии. Результаты подбора (рис. 4.3):

если сумма к выдаче равна 250000 р., то % Премии должен быть 203 %.

Задание 2. Используя режим подбора параметра, определить штатное расписания фирмы

Краткая справка. Известно, что в штате фирмы состоит:

• 6 курьеров;

• 8 младших менеджеров;

•10 менеджеров;

• 3 заведующих отделами;

• 1 главный бухгалтер;

• 1 программист;

• 1 системный аналитик;

• 1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников,фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = А_i*х+В_i, где х — оклад курьера; А_i и В_i — коэффициенты, показывающие:

А_i — во сколько раз превышается значение х;

B_i — на сколько превышается значение х.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel

2. Создайте таблицу штатного расписания фирмы по приведен­ному образцу Введите исходные данные в рабочий лист электронной книги.

3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

Рис. Исходные данные для Задания

4. В столбце D введите формулу для расчета заработной платы по каждой должности.

Например, для ячейки D6 формула расчета имеет следующий вид: =В6 * $D$3+С6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.

В столбце F задайте формулу расчета заработной платы всех ра­ботающих в данной должности. Например, для ячейки F6 формула расчета имеет вид =D6*Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу авто копированием.

В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.

5. Произведите подбор зарплат сотрудников фирмы для суммар­ной заработной платы, равной 100 000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;

в поле Значение наберите искомый результат 100000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000р.

6. Присвойте рабочему листу имя «Штатное расписание 1». Со­храните созданную электронную книгу под именем «Штатное расписание» в своей папке.

Анализ задач показывает, что с помощью MS Excel можно ре­шать линейные уравнения. Задания 4.1 и 4.2 показывают, что поиск значения параметра формулы — это не что иное, как чис­ленное решение уравнений. Другими словами, используя возмож­ности программы MS Excel, можно решать любые уравнения с одной переменной.

Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания определить зара­ботные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Порядок работы

1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно табл. 4.1 (один из пяти вариантов расчетов).

2. Методом подбора параметра последовательно определите зарпла­ты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплат скопируйте в табл.. в виде специальной вставки.

Таблица 1.1

Должность	Вариант 1	Вариант 2	Вариант 3	Вариант 4	Вариант 5
	коэф­фици­ент А	коэф­фици­ент В	коэф­фици­ент А	коэф­фици­ент В	коэф­фици­ент А	коэф­фици­ент В	коэф­фици­ент А	коэф­фици­енте	коэф­фици­ент А	коэф­фици­ент В
Курьер
Младший менеджер	1,2		1,3		1,3		1,4		1,45
Менеджер	2,5		2,6		2,7		2,6		2,5
Зав. отделом			3,1		3,2		3,3		3,1
Главный бухгалтер			4,1		4,2		4,3		4,2
Програм­мист	1,5		1,6		1,7		1,6		1,5
Системный аналитик	3,5		3,6		3,7		3,6		3,5
Ген. Директор			5,2		5,3		5,5		5,4

Таблица 2

Фонд заработной платы
Должность	Зарплата сотрудни­ка	Зарплата сотрудни­ка	Зарплата сотрудни­ка	Зарплата сотрудни­ка	Зарплата сотрудни­ка	Зарплата сотрудни­ка	Зарплата сотрудни­ка
Курьер	?	?	?	?	?	?	?
Младший инженер	?	?	?	?	?	?	?
Менеджер	?	?	?	?	?	?	?
Зав. отделом	?	?	?	?	?	?	?
Главный бухгалтер	?	?	?	?	?	?	?
Програм­мист	?	?	?	?	?	?	?
Системный аналитик	?	?	?	?	?	?	?
Ген. директор	?	?	?	?	?	?	?

Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произ­вести запись в буфер памяти (Правка/Копировать), установить кур­сор в соответствующую ячейку таблицы ответов, задать режим спе­циальной вставки (Правка/Специальная вставка), отметив в каче­стве объекта вставки — значения (Правка/'Специальная вставка/ вставить — значения) (рис.).

Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без даль­нейшей их зависимости от пересчета формул

 

Содержание отчета

1. Номер практического занятия, тема.
2. Цель занятия.
3. Краткие теоретические сведения.
4. Ответы на контрольные вопросы.

Контрольные вопросы.

1. Какие форматы ввода данных используются в EXCEL?

2. Как увеличить или уменьшить разрядность?

3. Виды форматирования текста в EXCEL?

Практическое занятие №5

Содержание отчета

1. Номер практического занятия, тема.
2. Цель занятия.
3. Краткие теоретические сведения.
4. Ответы на контрольные вопросы.

Контрольные вопросы.

 

1. Как добавить лист в электронную таблицу?

2. Как перейти от одной рабочей книге к другой?

3. Из скольких строк и ст