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



Дата27.04.2016
Размер99.8 Kb.


Шаталова Л.М.

Лабораторная работа №8

«Вычисления в EXCEL. Использование функций»

Цель работы – изучить возможности стандартных функций программы Excel и приобрести практические навыки использования механизма ввода аргументов функций на примере логических функций, функций ссылок и массивов, а также некоторых финансовых функций.
Программа Excel имеет более трехсот встроенных функций, которые можно использовать как отдельно, так и в составе формулы. Любая функция – это заранее созданная формула, выполняющая определенные операции. Для ввода функций используются элементы управления раздела библиотеки функций вкладки ФОРМУЛЫ ЛЕНТЫ. При выборе пункта «вставить функцию» (Shift + F3) появляется окно «Мастера функций» шаг 1, в котором выполняется выбор конкретной функции (здесь имеется возможность получить справку по выбранной функции). После нажатия клавиши Ok появляется окно шага 2, в котором заполняются строки с аргументами. Справа при этом отображаются введенные данные, а в конце – результат. На этом этапе можно выполнить «работу над ошибками ввода». Функция имеет следующий формат:
<Имя функции> (Аргумент 1; Аргумент 2;…; Аргумент N),
где “;” –разделитель. Разделитель «точка с запятой» определяется Windows .

Аргумент – это: 1)Число или текст, 2)ссылка на адрес ячейки, 3)Диапазон ячеек, 4)Арифметическое выражение (например, А7/А10*35), 5)Другая функция

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

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

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


ЧАСТЬ 1. ЛОГИЧЕСКИЕ ФУНКЦИИ

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


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

Синтаксис

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

Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА.

Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ.

Замечания

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

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

Функция И


Возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.

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


Синтаксис

И (логическое_значение1, [логическое_значение2], ...)
Функция И имеет аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.), указанные ниже.

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

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

Замечания

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

  • Если аргумент, который является ссылкой или массивом, содержит текст или пустые ячейки, то такие значения игнорируются.

  • Если указанный интервал не содержит логических значений, функция И возвращает значение ошибки #ЗНАЧ!.


ЧАСТЬ 2 . Функции «ССЫЛКИ И МАССИВЫ»

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


Функция ВПР
Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.

Внимание!!!

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

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

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



Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т.д. Если значение аргумента «номер_столбца» меньше 1, то возвращается ошибка #ЗНАЧ!, больше, чем число столбцов в таблице #ССЫЛ!
Задание

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



Вариант 1

Функция (ЕСЛИ)

1. Создать две таблицы по образцу, приведенному ниже










2. Ввести формулу в колонку "Итого начислено" (в ячейку Н3 и т.д.), используя функцию ЕСЛИ.




Необходимо реализовать условие, проверяющее должность: если результат







проверки "истина", то записывается формула с применением значения "Ставка";







если результат проверки "ложь", то выполняется проверка для следующей должности

Для ячейки H3 начало формулы имеет вид: =ЕСЛИ (F3=$A$3;G3*$B$3;ЕСЛИ(F3=$A$4;G3*$B$4;ЕСЛИ....)




Ввод формулы выполнять с использованием окна ввода "Аргументы функции"







3. После получения результата в колонке "Итого начислено" добавить итоговую сумму "Всего"




Общая сумма равна 222750 (цифра дана для сравнения результата)




Технология ввода вложенной формулы ЕСЛИ:

    1. Выделить ячейку для ввода формулы H3 и щелкнуть по пиктограмме fx

    2. В открывшемся окне выбрать логическую функцию «ЕСЛИ» и заполнить две строки аргументов

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


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



Вариант 2

Функция ВПР

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




ЧАСТЬ 3 . Финансовые функции. Инструменты анализа «что-если»

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

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

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



  • «Подбор параметра»

  • «Таблица подстановки»

  • «Сценарии»

  • «Поиск решения»

В данной работе предлагается освоить средство «Таблица подстановки»

ТАБЛИЦА ПОДСТАНОВКИ (ТП)

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


Технология построения ТП с одной переменной


  1. Ввести в ячейки значения аргументов для выбранной функции ОБЩДОХОД

  2. Так как по 30-тигодичному займу при ставке 9% годовых производятся ежемесячные выплаты, следует задать значение 9%/12 для аргумента «ставка» и значение 30*12 для аргумента «кол_пер».

  3. Подготовить таблицу подстановки

    1. Ввести значения изменяемой переменной, например, «ставки» в ячейки С14:С19

    2. Определить ссылочную ячейку, которая будет использоваться механизмом ТП (вне основной таблицы), например А13

    3. Ввести функцию в ячейку D13, используя для аргумента «ставка» ссылочную ячейку А13. Т.к. в А13 ничего не введено, то появляется сообщение об ошибке #ЧИСЛО!




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

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


Технология построения ТП с двумя переменными


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

  2. Подготовить таблицу подстановки

    1. Ввести значения первой изменяемой переменной, например, «ставки» в ячейки С14:С19

    2. Ввести значения второй изменяемой переменной, например, «стоимость инвестиций» в ячейки D13:G13

    3. Определить ссылочные ячейки, которые будут использоваться механизмом ТП (вне основной таблицы), например А13 – для аргумента «Ставка» и А15 – для аргумента «Стоимость инвестиций»

    4. Ввести функцию в ячейку С13, используя для аргумента «ставка» ссылочную ячейку А13, а для аргумента «Стоимость инвестиций» - А15. Т.к. в А13 и А15 ничего не введено, то появляется сообщение об ошибке #ЧИСЛО!

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

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



=ОБЩДОХОД(A13/12;C5*12;A15;13;24;0)



Задание

  1. Изучить 3 предложенные функции.

  2. Ввести аргументы и выполнить предлагаемый пример вычисления функций.

  3. Показать результат преподавателю.

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

  5. Изучить финансовую функцию по заданию преподавателя и выполнить построение таблицы подстановки для нее.




C:\Documents and Settings\Admin\Рабочий стол\2011_2012\excel\лаб_функ.docx


База данных защищена авторским правом ©psihdocs.ru 2016
обратиться к администрации

    Главная страница