Методичка

Создание расчетных электронных таблиц. Табличные процессоры

подборе изменяется один параметр. Команда Сервис → Подбор параметра → указать ячейку с искомым параметром, его значение и изменяющуюся ячейку.

При поиске решения можно изменять много параметров. Для чего необходимо записать математическую постановку задачи, определив целевую функцию и ограничения, подготовить таблицу и выполнить команду Сервис → Решатель → указать адрес целевой ячейки, задать область изменяемых ячеек, ограничения и задать параметры поиска.

Задания и методические указания к выполнению лабораторных работ

Лабораторная работа 9. Рабочая книга расчета заработной платы

Задание. Разработать рабочую книгу расчета заработной платы. Она должна включать следующие рабочие листы: Лист1 – «Об-

щий», Лист2 – «Январь», Лист3 – «Февраль», Лист4 – «Март».

Лист «Общий» содержит таблицы: «Исходные данные» и «Итоговые данные» – информацию об итоговых показателях за рассчитанные месяцы.

Листы «Январь», «Февраль» и «Март» содержат расчетную информацию о заработной плате коллектива сотрудников.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ EXCEL

1.Переименуйте Лист1, присвоив ему имя «Общий». В Контекстном меню (КМ) ярлычка листа выберите команду Переименовать, введите новое имя «Общий».

2. Создайте таблички на листе «Общий», приведенные на рис. 16. Заполните столбец «Месяц» автозаполнением: введите в ячейку «Январь» и потяните за маркер заполнения. Введите числовые данные.

Обрамите таблички и залейте первые строку и столбец серым цветом, используя ленту Главная. При редактировании таблицы используйте инструменты лент Главная, Вставка.

51

Создание расчетных электронных таблиц. Табличные процессоры

3. На листе «Общий» создайте таблицу «Итоговые данные». Заполните столбец «Месяц» автозаполнением. Столбцы 2, 3, 4 в таблице «Итоговые данные» заполняйте как формулы, содержащие ссылки на соответствующие итоговые ячейки из таблиц листов «Январь», «Февраль», «Март» после того как произведете в них расчет.

Рис. 16. Исходные и результирующая таблица на листе «Общий»

4. Перейдите на рабочий Лист2, переименуйте его в «Январь» и выполните на нем расчеты в соответствии с последующими указаниями. Проверьте результаты расчетов по заданию.

Порядок выполнения расчета на рабочем листе «Январь».

Создайте таблицу, приведенную на рис. 17, для этого:

-Столбец «№» заполните автозаполнением как арифметическую прогрессию, используя маркер заполнения.

-Столбцы «Фамилия И.О.», «Разряд», «Отработано дней» заполните вручную в соответствии со значениями, приведенными в таблице.

-Столбцы: «Тарифный коэффициент», «Зарплата», «Зарплата с районным коэффициентом», «Зарплата с начала года», «Налог», «Начислено» заполняются расчетными данными.

-В столбце «Тарифный коэффициент» записана формула: если «Разряд» равен 3, то его «Тарифный коэффициент» нужно взять с листа «Общий» из ячейки F4, если «Разряд» равен 4, то его значение с листа «Общий» из ячейки F5, если равен 5, то из ячейки F6 и т. д. Для реализации такой функции нужно обратится к функции ЕСЛИ.

52

Создание расчетных электронных таблиц. Табличные процессоры

Функция вставляется с ленты «Формула» Вставить функцию (или для данной конкретной формулы выбрать Логические), выбрать категорию Логические (если категория неизвестна, то выберите

Полный алфавитный перечень). Затем выбрать имя функции ЕСЛИ.

Рис.17. Расчетная таблица рабочего листа «Январь»

Функция ЕСЛИ имеет три поля для ввода аргументов, рис.18.

Вновь обратиться к функции ЕСЛИ

Рис.18. Окно задания аргументов функции ЕСЛИ

Поставить курсор в ячейку D4 и спросить:

Если ячейка С4(Разряд) =3, то ячейка D4 (Тарифный коэффициент) = лист «Общий» ячейка F4, а если нет, то снова нужно спросить.

Если ячейка C4 (Разряд) =4, то ячейка D4 (Тарифный коэффициент) = лист «Общий» ячейка F5, а если нет, то снова нужно спросить.

53

Создание расчетных электронных таблиц. Табличные процессоры

Если ячейка C4 (Разряд) =5, то ячейка D4 (Тарифный коэффициент) = лист «Общий» ячейка F6, а если нет, то снова нужно спросить и т.д. в ячейке С4 может быть записано любое число от 3 до 7. Таким образом в поле «Значение_если_ложь» функции ЕСЛИ нужно вновь обращаться к функции ЕСЛИ, всего семь раз.

В итоге в строке формул и в ячейке D4 получим формулу

=ЕСЛИ(C4=3;Общий!$F$4;ЕСЛИ(C4=4;Общий!$F$5;ЕСЛИ(C4=5 ;Общий!$F$6;ЕСЛИ(C4=6;Общий!$F$7;ЕСЛИ(C4=7;Общий!$F$8 ;"ошибка"))))). Скопируйте формулу по ячейкам D4:D7.

-Столбец (F) «Зарплата» = («Тарифный коэффициент» * «Отработано дней» * «Минимальную зарплату»)/«Количество рабочих дней в месяце». Например, в ячейке F4 формула будет иметь вид:

=D4*E4*Общий!$C$4/Общий!$B$4, рис 18, строка формул.

-Столбец (G) «Зарплата с районным коэффициентом»=1,15* Зарплата. В ячейке G4 формула будет иметь вид: =1,15*F4.

-Столбец (Н) «Зарплата с начала года» – это зарплата наращенным итогом с начала года. «Зарплата с начала года» за Январь = «Зарпла-

года» <200000, то «Налог» = 0,13 * «Зарплата с районным коэффициентом», иначе «Налог» = 0,2 * «Зарплата с районным коэффициентом». Для формулы вызовите функцию ЕСЛИ. Формула в ячейке I4

=ЕСЛИ(H4<200000;0,13*G4;0,2* G4).

-Столбец (J) «Начислено» = «Зарплата с районным коэффициентом»

– «Налог». Формула в J3 =H3-I3.

-Скопируйте формулы из интервала ячеек первой строки таблицы F4:J4 в интервалы второй – седьмой строк таблицы, используя мар-

кер заполнения.

-Вычислите итоговые показатели, используя кнопку Автосуммиро-

вание или Мастер функций функция СУММ.

5. Порядок выполнения расчета на рабочем листе «Фев-

раль».

- Скопируйте рабочий лист «Январь» − в контекстном меню ярлыка листа «Январь» команда Переместить или скопировать от-

метьте флажок Создать копию переименуйте копию листа «Январь» в контекстном меню ярлыка этого рабочего листа команда

54

Создание расчетных электронных таблиц. Табличные процессоры

Переименовать в «Февраль» и выполните на нем действия редактирования в соответствии с последующими указаниями.

-Проверьте и измените значения в столбцах «Разряд», «Отработано дней» для каждого работника на листе «Февраль», рис. 19.

-Скорректируйте формулу в столбце «Зарплата», учитывая, что «Количество рабочих дней в месяце» феврале и «Минимальная зарплата» за февраль другие. Например, «Зарплата» в ячейке F4

=D4*E4*Общий!$C$5/Общий!$B$5.

-Скорректируйте формулу в столбце «Зарплата с начала года» = «Зарплата с районным коэффициентом» за январь + «Зарплата с районным коэффициентом» за февраль. Например, «Зарплата с начала года» в ячейке H4 =G4+Январь!G4.

Рис. 19. Расчетная таблица за февраль

-Скопируйте скорректированные формулы вниз по соответствующему столбцу.

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

6.Порядок выполнения расчета на рабочем листе «Март».

-Скопируйте рабочий лист «Февраль», переименуйте копию в «Март» и выполните на нем действия редактирования в соответствии с последующими указаниями. Проверьте и измените значения в поле «Раб. Дни» для каждого работника на листе «Март».

55

Создание расчетных электронных таблиц. Табличные процессоры

-Скорректируйте формулы в столбцах: «Зарплата» и «Зарплата с начала года». Вид формул имеют вид соответственно

=D4*E4*Общий!$C$6/Общий!$B$6 =G4+Январь!G4+Февраль!G4, рис.20, строка формул.

-Скорректируйте данные в столбцах «Разряд», «Отработано дней».

7.Создание итоговой таблицы. Перейдите на лист «Общий»,

поставьте курсор, например, в ячейку В12 – вставьте знак = (равно) – перейдите на лист «Январь» − поставьте курсор в ячейку G11 – нажмите на клавишу Enter. Таким образом заполните все ячейки итоговой таблички на листе «Общий», рис.21. Проверьте правильность своих вычислений.

8.Измените в листах Февраль, Март данные в столбце «Отработано дней». Посмотрите результат в Итоговой таблице. Измените размер «Минимальной зарплаты», посмотрите результат в Итоговой таблице.

Рис. 20. Расчетная таблица за март

Рис. 21. Таблица «Итоговые данные»

56

Создание расчетных электронных таблиц. Табличные процессоры

9.Выведите таблицу «Январь» с формулами. Лента Вид – Фор-

мулы, рис. 22.

10.Исследуйте таблицу Январь с помощью ленты Формула Влияющие ячейки, Зависимые ячейки, Убрать стрелки, Показать формулы, рис. 23.

Рис. 22. Таблица за январь в режиме показа формул

Рис. 23. Влияющие и зависимые ячейки для ячейки F4

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ВЫПОЛНЕНИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ CALC

Большинство действий, связанных с созданием расчетной таблицы в Calc, аналогичны действиям в Excel. Поэтому остановимся на отличиях.

1.Набор формулы, аналогично Excel, начинается со знака = в строке формул. Формула содержит адреса ячеек, которые указываются щелчком мыши по нужной ячейке, константы и функции. Отличие

57

Создание расчетных электронных таблиц. Табличные процессоры

в адресах ячеек: в Calc адрес с листа «Общий» Общий.$C$4 в Excel Общий!$C$4. В Calc знак разделения имени листа и адреса ячейки –

точка, в Excel – восклицательный знак.

2.Вызов Мастера функций: в строке формул значок fx, выбираете категорию, затем функцию, например ЕСЛИ – IF, рис. 24. Задание аргументов осуществляется аналогично функции ЕСЛИ табличного процессора Excel.

Копирование рабочего листа аналогично Excel.

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

Для просмотра связи влияющих и зависимых ячеек команда

Сервис Зависимости Влияющие ячейки, Зависимые ячейки, Удалить все стрелки.

Рис. 24. Мастер функций Calc. Окно функции IF

58

Создание расчетных электронных таблиц. Табличные процессоры

Вывод таблицы с формулами: Сервис – Параметры –

OpenOffice.org Calc – Вид – Показать Формулы, рис. 25.

Рис. 25. Окно настройки параметров вывода таблицы с формулами

Лабораторная работа 10. Расчет материального баланса при обогащении

Задание.

1. Создайте таблицу «1. Исходные данные». Наименование столбцов в «шапке» таблицы выровняйте по центру, текст в ячейках «шапки» разместите в несколько строк. Введите числовые данные в столбец «№ смены» автозаполнением, в столбцы «Массовая доля Cu, %» и «Количество, т/час» как приведено в таблице.

Обрамите таблицу и залейте по приведенному образцу, рис. 26. 2.Скопируйте таблицу «1. Исходные данные» на этот же лист

ниже и назовите копию «2. Результаты расчета».

3. В расчетной таблице выполните вычисления. В ячейки Е17, G17, H17, I17, K17, L17 введите формулы (по правилам Excel), в такой последовательности:

в G17 - «Выход» концентрата 100(см. стр.45);

в Н17 - «Извлечение» концентрата ;

59

Создание расчетных электронных таблиц. Табличные процессоры

вК17 - «Выход» отходов x 100 ;

вL17 «Потери» с отходами х 100 k ;

Рис. 26. Исходная и расчетная таблицы

в E17 «Количество концентрата» Qk Qp k /100;

в I17 «Количество отходов» Q Q Q , где

отх p k

– массовая доля Cu в руде, %, Qp – количество руды, т/ч %; Qk – количество концентрата, т/ч; – массовая доля Cu в концентрате, %; k

– выход концентрата, %; k – извлечение концентрата, %; Qх – количество отходов, т/ч; – массовая доля Cu в отходах, %; x – выход отхо- дов, %; x – потери c отходами, %.

4. Скопируйте формулы из ячеек E17, G17:Н17, I17:L17 в распо-

J26. В ячейки G26:H26, K26:L26 скопируйте формулы из расположенных выше ячеек.

60