Лабораторная работа №8 «Вычисления в excel. Использование функций»

Программа Excel содержит следующие категории стандартных функций:

Финансовые, Дата и время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические, Проверка свойств и значений, Инженерные, Аналитические.

Имена функций в основном записаны на русском языке. Количество аргументов определяется форматом конкретной функции, но не более 255. Количество уровней вложений одной функции в другую не должно быть больше 64.

В этой категории 7 функций (ЕСЛИ, ЕСЛИОШИБКА, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ). Вычисляются логические выражения, возвращающие значения «ИСТИНА» или «ЛОЖЬ», которые в дальнейшем могут использоваться для выполнения других действий или форматирования.

• 
  В качестве значений аргументов «значение_если_истина» и «значение_если_ложь» можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ.
  
• 
  После вычисления аргументов «значение_если_истина» и «значение_если_ложь», функция ЕСЛИ возвращает полученное значение.
  

Функция И

Обычно функция И используется для расширения возможностей других функций, выполняющих логическую проверку. Например, функция ЕСЛИ выполняет логическую проверку и возвращает одно значение, если при проверке получается значение ИСТИНА, и другое значение, если при проверке получается значение ЛОЖЬ. Использование функции И в качестве аргумента лог_выражение функции ЕСЛИ позволяет проверять несколько различных условий вместо одного.

• 
  Логическое_значение1. Обязательный аргумент. Первое проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ.
  
• 
  Логическое_значение2, ... Необязательный аргумент. Дополнительные проверяемые условия, вычисление которых дает значение ИСТИНА или ЛОЖЬ. Условий может быть не более 255.
  

• 
  Аргументы должны давать в результате логические значения (такие как ИСТИНА или ЛОЖЬ) или должны быть массивами (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) или ссылками, содержащими логические значения.
  
• 
  Если аргумент, который является ссылкой или массивом, содержит текст или пустые ячейки, то такие значения игнорируются.
  
• 
  Если указанный интервал не содержит логических значений, функция И возвращает значение ошибки #ЗНАЧ!.
  

В этой категории 18 функций. Они вычисляют и возвращают значения из диапазона, создают гиперссылки, транспонируют таблицы и др.

Перед использованием функции ВПР необходимо выполнить сортировку по графе «должность» в таблице со ставками.

Рекомендуется названия должностей вводить 1 раз, а затем копировать их в соответствующие ячейки
Синтаксис
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение — значение, которое должно быть найдено в первом столбце табличного массива.

Этот аргумент может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Рассчитать, сколько начислено зарплаты по количеству отработанных часов в зависимости от ставки, назначенной в соответствии с должностью исполнителя. Выполнить задание с использованием функций: 1. ЕСЛИ и 2. ВПР. Оба задания сохранить в одном файле на разных листах с соответствующим названием листа.

1. 
   Выделить ячейку для ввода формулы H3 и щелкнуть по пиктограмме f_x
   
2. 
   В открывшемся окне выбрать логическую функцию «ЕСЛИ» и заполнить две строки аргументов
   
3. 
   Для заполнения третьего аргумента необходимо щелкнуть ЛКМ по имени функции «ЕСЛИ» (в строке формул записано слева) – откроется новое окно для ввода второй функции ЕСЛИ, в котором заполняются 2 строки аргументов, а далее снова выполняется щелчок ЛКМ для ввода третьей функции ЕСЛИ. Таким образом, в расчете используются 4 функции «ЕСЛИ» для проверки 4-х должностей, и вычисления нужного значения ставки
   

Лист с данной таблицей назвать по имени функции «ЕСЛИ».

Выполнить заполнение по листам для построения таблицы с использованием функции ВПР (сделать группу из двух листов – выполнить копирование рабочего листа с исходной таблицей – выбрать пиктограмму - выбрать команду «по листам» - заполнить полностью). Удалить из колонки Н функцию ЕСЛИ и вставить функцию ВПР

В этой категории более 50 функций. Вычисляются процентные ставки, ежемесячные отчисления, платежи, доходность, амортизация и др. Все функции этой группы требуют предварительной установки специальной надстройки «Пакета анализа». Для установки этой надстройки выполняется команда: кнопка «Office»  Параметры Excel  Надстройки  кнопка Перейти  в открывшемся окне выбрать Пакет анализа  OK.

Если в качестве аргумента используются даты, то они вводятся с помощью функции ДАТА().

Программа Excel содержит несколько средств анализа данных типа «что-если»:

• 
  «Подбор параметра»
  
• 
  «Таблица подстановки»
  
• 
  «Сценарии»
  
• 
  «Поиск решения»
  

ТП – это диапазон ячеек, в котором отображаются результаты вычислений по определенным формулам вместе с параметрами, которые используются в этих формулах. ТП – это способ быстрого вычисления нескольких версий в рамках одной операции. Существует 2 варианта построения ТП: с одной переменной, влияющей на результат вычислений; с двумя переменными, влияющими на результат вычислений. Функция содержит несколько аргументов, но в качестве изменяемых может использоваться одна (или две) переменные в зависимости от выбранного варианта

1. 
   Ввести в ячейки значения аргументов для выбранной функции ОБЩДОХОД
   
2. 
   Так как по 30-тигодичному займу при ставке 9% годовых производятся ежемесячные выплаты, следует задать значение 9%/12 для аргумента «ставка» и значение 30*12 для аргумента «кол_пер».
   
3. 
   Подготовить таблицу подстановки
   
   1. 
      Ввести значения изменяемой переменной, например, «ставки» в ячейки С14:С19
      
   2. 
      Определить ссылочную ячейку, которая будет использоваться механизмом ТП (вне основной таблицы), например А13
      
   3. 
      Ввести функцию в ячейку D13, используя для аргумента «ставка» ссылочную ячейку А13. Т.к. в А13 ничего не введено, то появляется сообщение об ошибке #ЧИСЛО!
      

4. 
   Выделить диапазон, содержащий функцию и значения, которые нужно подставлять, здесь это С13:D19, и выполнить команду: Данные  анализ «что-если» (пиктограмма )  таблица данных и заполнить в появившемся окне строку «Подставлять значения по строкам в» ссылкой на адрес ячейки А13 (ссылка должна быть абсолютной)  ОК
   
5. 
   Появится результирующая таблица, которая может быть использована для анализа
   

1. 
   Ввести в ячейки значения аргументов для выбранной функции
   
2. 
   Подготовить таблицу подстановки
   
   1. 
      Ввести значения первой изменяемой переменной, например, «ставки» в ячейки С14:С19
      
   2. 
      Ввести значения второй изменяемой переменной, например, «стоимость инвестиций» в ячейки D13:G13
      
   3. 
      Определить ссылочные ячейки, которые будут использоваться механизмом ТП (вне основной таблицы), например А13 – для аргумента «Ставка» и А15 – для аргумента «Стоимость инвестиций»
      
   4. 
      Ввести функцию в ячейку С13, используя для аргумента «ставка» ссылочную ячейку А13, а для аргумента «Стоимость инвестиций» - А15. Т.к. в А13 и А15 ничего не введено, то появляется сообщение об ошибке #ЧИСЛО!
      
3. 
   Выделить диапазон, содержащий функцию и значения, которые нужно подставлять, здесь это С13:D19, и выполнить команду: Данные Þ анализ «что-если» (пиктограмма ) Þ таблица данных и заполнить в появившемся окне строку «Подставлять значения по столбцам в» ссылкой на адрес ячейки А15 (ссылка должна быть абсолютной) , а строку «Подставлять значения по строкам в» ссылкой на адрес ячейки А13 (ссылка должна быть абсолютной) Þ ОК
   
4. 
   Появится результирующая таблица, которая может быть использована для анализа
   

1. 
   Изучить 3 предложенные функции.
   
2. 
   Ввести аргументы и выполнить предлагаемый пример вычисления функций.
   
3. 
   Показать результат преподавателю.
   
4. 
   Реализовать на примере заданных функций инструмент анализа «что-если». Создать таблицы подстановок для изменения одного аргумента каждой функции, а затем для изменения двух аргументов каждой функции.
   
5. 
   Изучить финансовую функцию по заданию преподавателя и выполнить построение таблицы подстановки для нее.