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

1. Как Excel определяет, что вводится формула, а не текст?

31. Как ввести текст в ячейку рабочего листа?

32. Каковы способы перемещения содержимого ячеек?

33. Каковы способы копирования содержимого ячеек?

34. Каковы способы тиражирования содержимого ячеек?

35. Каковы способы редактирования содержимого ячеек?

36. Как добавить строку?

37. Как добавить столбец?

38. Каковы способы выделения несмежных ячеек, диапазонов?

39. В чем отличие относительной адресации от абсолютной?

Лабораторная работа № 2. Редактирование и создание сложных формул.

Цели работы: редактирование формул, сортировка табличных данных, применение Мастера функций, функция ЕСЛИ.

Мастер функций

1. Откройте таблицу Задача1.xlsx. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

40. Рассмотрим способ создания формул с помощью Мастера функций. Процессор электронных таблиц поддерживает несколько сотен функций. В предыдущей работе вы уже ознакомились с функцией СУММ. Запомнить все функции невозможно, да в этом и нет необходимости. Специальное инструментальное средство, называемоеМастером функций,позволяет в диалоговом режиме легко найти нужную функцию и сконструировать на ее основе формулу. Для создания формулы с помощьюМастера функцийнадо установить курсор в результирующую ячейку и выполнить командуФормулы / Библиотека формул / Вставить функциюили нажать кнопку Вставка функции слева от строки формул. В появившемся окне (рис.6) в списке сверху следует выбрать нужную категорию функций илиПолный алфавитный перечень, а затем в списке снизу щелкнуть по нужной функции и проверить ее назначение в нижней части окна, после чего следует нажать кнопкуОК. В следующем окне требуется ввести адрес ячеек, по которым будет вычисляться выбранная функция. Можно ввести их вручную с клавиатуры, а можно, отодвинув окно, выделить нужные ячейки. Затем нужно нажатьОКв окне илиEnterна клавиатуре.

Рисунок 6 - Окно Мастера формул

Использование функции если

41. Измените алгоритм расчета подоходного налога (G5) с учетом прогрессивной шкалы налогообложения (F5). Теперь пусть налог вычисляется по двум заданным формулам в зависимости от величины налогооблагаемой базы, условие расчета звучит так: «Если налогооблагаемая база меньше или равна 30 000 рублей, то принимается ставка 12% от НОБ, в противном случае налог равен – 3 600 плюс 15% от суммы, которая превышает 30 000 руб». В таком случае расчет налога должен использовать функцию ЕСЛИ:

ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь).

Формула ЕСЛИ имеет три составные части (рис.7), которые отделены друг от друга обязательным знаком «  ;  » (см. на строку формул):

Рисунок 7 - Функция ЕСЛИ

I-я часть: «логическое_выражение» - это условие задачи.

значение НОБ< = 30 000 руб.

II-я часть:содержит формулу расчета налога при условии, что I-я часть имеет значение ИСТИНА,

значение НОБ* ставку налога 12%

а III-я частьсодержит формулу расчета налога при условии, что I-я часть имеет значение ЛОЖЬ.

(значение НОБ – 30 000) * ставку налога 15% + 3 600

42. Установите 1-2 сотрудникам заработную плату более 30 000. Проверьте правильность расчета налога. Верните исходное значение зарплаты сотрудников.

43. В 1999 были установлены новые ставки подоходного налога, см. таблицу 1.

		Таблица 1
Размер совокупного облагаемого дохода	Ставка налога в федеральный бюджет	Ставка налога в бюджеты субъектов РФ
до 30 000	3%	9%
от 30 001 до 60 000	3%	2700+ 12% с суммы > 30 000
от 60 001 до 90 000	3%	6300 + 17% с суммы > 60 000
от 90 001 до 150 000	3%	11400 + 22% с суммы > 90 000
от 15 001 до 300 000	3%	24600 + 32% с суммы > 150 000
от 300 001 и выше	3%	72600 + 42% с суммы >300 000

Измените формулу расчета налога в основной таблице. Используйте функцию ЕСЛИ несколько раз, вкладывая их друг в друга.В синтаксисе (правило написания формулы) функции ЕСЛИ есть уточнение «До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов «значение_если_истина» и «значение_если_ложь», чтобы конструировать более сложные проверки». Если возникнут трудности, ограничьтесь 3 строчками таблицы. Налог будет высчитываться следующим образом:

ЕСЛИ значение НОБ <= 30000,

то значение Налог = значение НОБ * (3% + 9%),

иначе ЕСЛИ значение НОБ <= 60000,

то значение Налога = 2700 + (НОБ - 30000) * 12% + 3% * НОБ и т.д.

44. Поставьте курсор в ячейку расчета налога для первого сотрудника. Нажмите кнопку Вставка функциислева от строки формул. В диалоговом окне выберите логическую функциюЕСЛИ. Затем в диалоговом окнеЕСЛИв первом поле следует создать условие задачи (значение НОБ<=30000), во втором поле - следует создать формулу расчета налога, если условие верно (Налог = значение НОБ*(3%+9%)), в третьем - если условие неверно. Для этого в третьем поле поставьте курсор и нажмите кнопкуПанели Форматирование, выберите функциюЕСЛИдля проверки следующего условия значение значение НОБ<= 60000, и т.д.

45. Вставьте перед столбцом Фамилияновую графуТаб.номери заполните (автоматически) ее значениями: 100, 101,102 и т.д. Для этого, установите табельный номер первого сотрудника равным 100. Растиражируйте ячейку А5 на весь диапазон А5:А13, щелкните на появившейся в правом нижнему углу от диапазона кнопке, и выберите пунктЗаполнить.

46. Сохраните исправленную таблицу под именем Задача2.xls(командаФайл / Сохранить как…).

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

48. Установите курсор на ячейку рядом с Минимальное, выполните команду вкладкаФормулы / раздел Библиотека формул / кнопка Вставить функциюили нажмите кнопку Вставка функции, выберите категориюстатистические функции, затем в списке функций найдите функциюМИН. Когда на экране будет запрос аргументов, задайте диапазон или запишите имя диапазона. В диапазон входят только значения зарплат сотрудников, строкаИтогодля подсчета минимального значения не включается в диапазон.

49. Запишите формулы для вычисления значений других статистических показателей.

50. Растиражируйте полученные формулы вправо во все остальные столбцы. В результате всех выполненных действий таблица приобретет вид, показанный на рис.8.

Рисунок 8 - Итоговая таблица

51. Проведите с таблицей несколько экспериментов. Меняйте некоторые значения заработной платы и наблюдайте за изменением статистических величин.

52. Запишите Ваше задание в рабочую папку под именем Задача2.xls.