Мастер функций в программе Microsoft Excel

Microsoft Excel 2007. Использование функций

ID: 16381

Название работы: Microsoft Excel 2007. Использование функций

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

Описание: ПРИВАЛОВА П.А. Методические указания по выполнениюлабораторной работы Microsoft Excel 2007. Использование функций. по дисциплине Информатика для студентов 1 курса дневного отделенияэкономических специальностей 1.Функции в Excel. Мастер функций При проведении расчето

Язык: Русский

Дата добавления: 2013-06-20

Размер файла: 595.5 KB

Работу скачали: 241 чел.

ПРИВАЛОВА П.А.

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

«Microsoft Excel 2007. Использование функций.»

по дисциплине «Информатика»

для студентов 1 курса дневного отделения
экономических специальностей

1.Функции в Excel. Мастер функций

При проведении расчетов в электронных таблицах часто необходимо использовать функции. В пакете Excel функции объединены в категории (группы) по назначению и характеру выполняемых операций:

•  математические;
•  финансовые; 
•  статистические;
•  даты и времени;
•  логические;
•  работа с базой данных;
•  проверки свойств и значений;  … и другие.

Любая функция имеет вид:

ИМЯ (СПИСОК АРГУМЕНТОВ)

ИМЯ- это фиксированный набор символов, выбираемый из списка функций;

СПИСОК АРГУМЕНТОВ (или только один аргумент)- это величины, над которыми функция выполняет операции. Аргументами функции могут быть адреса ячеек, константы, формулы, а также другие функции. В случае, когда аргументом является другая функция, мы имеем дело со вложенной функцией.

Например, запись СУММ(С7:C10;D7:D10) содержит функцию СУММ с двумя аргументами, каждый из которых является диапазоном ячеек, а запись КОРЕНЬ(ABS(А2)) содержит функцию КОРЕНЬ, аргументом которой является функция ABC, у которой в свою очередь аргументом является адрес ячейки А2.

Пакет Excel предоставляет удобный инструмент ввода функций- Мастер функций. Инструмент Мастер функций можно вызвать:

1.  командой Вставить функцию во вкладке Формулы из группы Библиотека функций (Рис.1)

Рис.1 Команда Вставить функцию во вкладке Формулы

1.  командой Вставить функцию в строке формул (Рис.2).

Рис.2 Команда Вставить функцию в строке формул

После вызова Мастера функций появляется диалоговое окно (Рис.3):

Рис.3 Диалоговое окно Мастера функций

В этом окне нужно выбрать категорию функции и в списке ниже необходимую функцию.

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

Рис.4 Окно математической функции КОРЕНЬ

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

Рис.5 Выбор вложенной (внутренней) функции

После ввода аргументов вложенной функции не следует щелкать на кнопке ОК, а нужно активизировать (щелкнуть мышью) имя соответствующей внешней функции в поле ввода строки формул. Т.е. нужно перейти на окно Мастера функций соответствующей внешней функции. Так следует повторять для всех вложенных функций. В формулах может быть до 64 уровней вложения функций.

2.Математические функции

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

2.1.Задание для самостоятельной работы 1

Перейдите на Лист 2 рабочей книги.

Переименуйте Лист 2 рабочей книги в Примеры функций.

Создайте таблицу, представленную на рис. 6 ( !!! Ячейки С4:C7 не заполняйте – в них будут вводиться расчетные формулы.).

Рис.6 Задание для самостоятельной работы 1. Примеры математических функций

1.  В ячейку C4 введите формулу расчета квадратного корня из произведения содержимого ячейки A4  на абсолютное значение (модуль) числа из ячейки B4 (использовать функции КОРЕНЬ и АВS).
2.  В ячейку C5 введите формулу для возведения содержимого ячейки A5  в степень числа, содержащегося в ячейке B5 (использовать функцию СТЕПЕНЬ).
3.  В ячейку C6 введите формулу расчета абсолютного значения целой части разности содержимого ячеек A6 и B6 (использовать функции АВS и ЦЕЛОЕ).
4.  В ячейку С7 введите формулу расчета остатка от деления содержимого ячейки A7 на содержимое ячейки B7 (использовать функцию ОСТАТ).
5.  Сравните результаты с данными, представленными в графе Результат.

2.2.Задание для самостоятельной работы 2

Перейдите на Лист 3 рабочей книги.

Переименуйте Лист 3  рабочей книги в Стаж работы.

Создайте таблицу, представленную на рис. 7 ( !!! Ячейки D3:E5 не заполняйте – в них будут вводиться расчетные формулы.).

Рис.7 Задание для самостоятельной работы 2. Вычисление Стажа работы

В ячейки столбцов Стаж работы введите необходимые для вычисления формулы.  Стаж работы рассчитайте на 1 января 2007 года, эту дату запишите в ячейку В7. При расчете стажа работы используйте функции ЦЕЛОЕ и ОСТАТ из категории Математические функции.

Алгоритм расчета стажа работы состоит из следующих шагов:

1.  От заданной даты (01.01.2007) вычесть дату поступления на работу, тем самым будет найдено количество дней работы.
2.  Разделить найденное количество дней на 365, т.е. на количество дней в году. Целая часть частного от этого деления – это найденные годы стажа работы.
3.  Остаток от деления в п.2 разделить на 30, т.е. на количество дней в месяце. Целая часть частного от этого деления – это найденные месяцы стажа работы

3.Статистические функции

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

3.1.Задание для самостоятельной работы 3

Перейдите на лист  Примеры функций и правее создайте таблицу, представленную на рис. 8 (в графу Результат по- прежнему нужно ввести формулы).

Рис.8 Задание для самостоятельной работы 3 . Примеры статистических функций

1.  В ячейку G4 введите формулу подсчета количества чисел из диапазона E4:F7 (использовать функцию СЧЕТ).
2.  В ячейку G5 введите формулу расчета среднего арифметического  диапазона E4:F7 (использовать функцию СРЗНАЧ).
3.  В ячейку G6 введите формулу поиска максимального значения для данных диапазона F4:F7 (использовать функцию МАКС).
4.  В ячейку G7 введите формулу расчета среднего геометрического диапазона E4:F7 (использовать функцию СРГЕОМ).
5.  Сравните результаты с данными, представленными в графе Результат.

4.1. Описание некоторых логических функций. Примеры

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

Рассмотрим некоторые функции из этой категории на примерах.

Пример 1.

Разместить в ячейке E2 большее из двух значений,  содержащихся в ячейках  C2 и D2 ( предполагается, что эти значения не могут быть равны).

Выполните этот пример на любом свободном рабочем листе. Запишите в ячейки C2 и D2 любые различные числа.

Решение:

Необходимо проанализировать условие C2>D2. Если это условие выполняется, т. е. имеет значение ИСТИНА, то в ячейку E2 нужно записать число из ячейки С2.   Если же условие не выполняется, т.е. его  значение есть ЛОЖЬ, в ячейку E2 нужно записать число из ячейки D2.

Для анализа условий используют функцию ЕСЛИ, которая имеет синтаксис:

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

лог_выражение- это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

значение_если_истина- это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА.

значение_если_ложь- это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ.

Для решения нашего примера в ячейку Е2 введем функцию ЕСЛИ:

Рис.9 Окно функции ЕСЛИ Примера 1. Логические функции.

Следовательно, в ячейке E2 должна быть записана формула:

=ECЛИ(С2>D2;С2;D2)

Рис.10 Решение Примера 1. Логические функции.

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

Пример 2:

Сформировать формулу, определяющую значение ячейки С1 равным:

•  числу из ячейки B1 в четвертой степени, если число в ячейке А1 меньше  или равно нулю;
•  числу из ячейки А1, если в число ячейке А1 больше 0, но меньше 1;
•  числу из ячейки А1, деленное на 3, в других случаях.

Решение:

В качестве значений аргументов функции ЕСЛИ также могут быть функции как  исполняемые, так  и логические. Так, в данном примере при выполнении условия A10;B1>0;C1>0);”разные”;”минус”))

Ниже приведены результаты расчетов по этой формуле для всех вариантов исходных данных (Рис.12).

Рис.12 Результаты решения Примера 3. Сложные условия.

Пример 4.

Перейдите на Лист 4 рабочей книги.

Переименуйте Лист 4 рабочей книги в Работа бригады.

Создайте на этом рабочем листе следующую таблицу (Рис.13):

Рис.13 Таблица исходных данных  для Примера 4

В ячейку А5 введите текст, указанный на рис.13.

Необходимо провести анализ выполнения плана бригады из 3-х человек и сделать следующие заключения:

1.  вся бригада выполнила план;
2.  вся бригада не выполнила план;
3.  не вся бригада выполнила план.

Заключение о работе бригады  записать в ячейку А6.

Столбец D (Факт) заполните самостоятельно для всех случаев, которые необходимо рассмотреть по условию задания.

В ячейку Е2 (% выполнения плана) нужно записать расчетную формулу вида: =D2/C2*100 и скопировать ее приёмом автозаполнения в ячейки Е3:Е4.

Затем, используя в Мастере функций функции из категории Логические функции, проанализируйте процент выполнения плана всей бригады, записав в ячейку А6 следующую формулу:

=ECЛИ(И(E2

Источник: //5fan.ru/wievjob.php?id=16381

Microsoft Excel 2003

на предыдущую  на следующую

5.2. Функции Excel

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

Примечание. Если хорошо известен формат, то функцию можно ввести с клавиатуры. После ввода знака “=”, имени функции и скобки, появляется всплывающая подсказка для ввода аргументов.

Автовычисление итоговых функций

В Excel автоматизирован вызов функций, вычисляющих итоговые значения в диапазонах ячеек с числовыми данными, Такие функции выбирают из раскрывающегося списка кнопки Автосумма в панели инструментов.

Применение функций автовычисления

1 способ:

1. выделить диапазон ячеек с числами;
2. щелкнуть на раскрывающей стрелке кнопки Автосумма и выбрать функцию
3. (для вызова функции суммирования можно не раскрывать список, а щелкнуть на самой кнопке );
4. результат вычисления появится ниже по столбцу (или правее по строке).

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

2 способ:

1. выделить диапазон вместе с пустой ячейкой для помещения результата;
2. щелкнуть на раскрывающей стрелке кнопки Автосумма и выбрать функцию;
3. результат вычисления появится в последней из выделенных ячеек.

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

3 способ

Этот способ позволяет выбрать любой, даже несвязанный диапазон для вычисления:

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

Выделение несмежных диапазонов выполняют при нажатой клавише .

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

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

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

Примечание. Мастер функций можно также вызвать:

• в списке кнопки Автосумма (пункт Другие функции…);
• командой меню ВставкаФункция;
• комбинацией клавиш + .

Диалоговое окно Мастера функций(рисунок 5.4) содержит два списка: раскрывающийся список Категория и список функций. При выборе категории отображается соответствующий список функций.

Рис. 5.4

Категория “10 недавно использовавшихся” постоянно обновляется, в зависимости от того, какие функции использовались в последнее время.

Категория “Полный алфавитный перечень” содержит список всех функций Excel.

При выборе функции в нижней части окна появляется ее краткое описание.

После щелчка на кнопке Ok (или нажатия клавиши ) имя выбранной функции заносится в строку формул вместе со скобками, ограничивающими список аргументов, и одновременно открывается окно Аргументы функции.

Пример такого окна функции показан на рисунке 5.5.

Рис. 5.5

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

Если окно закрывает рабочую область, мешая видеть и выбирать ячейки, его можно сдвинуть или свернуть, щелкнув на кнопке сворачивания соответствующего поля (см. рис. 5.5). При щелчке на такой кнопке окно сворачивается до размера одной строки (рисунок 5.6).

Выделив ячейку (диапазон), окно функции можно восстановить, щелкнув на кнопке разворачивания в конце строки.

–кнопка разворачивания

Рис. 5.6

После ввода аргумента справа от поля указывается его значение.

Выбор недавно использовавшихся функций

1. Активизировать ячейку для помещения функции и ввести с клавиатуры знак “=” (в левой части строки формул отобразится имя последней из использовавшихся функций).

2. Щелкнуть на раскрывающей кнопке рядом с именем функции. Откроется список 10 функций, которыми пользовались в программе в последнее время (рисунок 5.7).

Пункт “Другие функции…” предназначен для вызова Мастера функций.

Рис. 5.7

в начало на предыдущую   на следующую

Источник: //yuschikev.narod.ru/Teoria/Excel2003/Excel5-1.html

Работа с функциями в Excel на примерах

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

Лишь мизерная часть возможностей вычислительных функций включено в данный учебник с уроками по Excel. На простых примерах и далее будем рассматривать практическое применение функций.

Как найти среднее арифметическое число в Excel? Создайте таблицу, так как показано на рисунке:

В ячейках D5 и E5 введем функции, которые помогут получить среднее значение оценок успеваемости по урокам Английского и Математики. Ячейка E4 не имеет значения, поэтому результат будет вычислен из 2 оценок.

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

Функция среднее значение в Excel: =СРЗНАЧ() в ячейке E5 игнорирует текст. Так же она проигнорирует пустую ячейку. Но если в ячейке будет значение 0, то результат естественно измениться.

В Excel еще существует функция =СРЗНАЧА() – среднее значение арифметическое число. Она отличается от предыдущей тем, что:

• =СРЗНАЧ() – пропускает ячейки которые не содержат чисел;
• =СРЗНАЧА() – пропускает только пустые ячейки, а текстовые значения воспринимает как 0.



1. В ячейки D6 и E6 нашей таблицы введите функцию подсчета количества числовых значений. Функция позволит нам узнать количество поставленных оценок.
2. Перейдите в ячейку D6 и выберите инструмент из выпадающего списка: «»-«Сумма»-«Число».
3. На этот рас нам не подходит автоматическое определение диапазона ячеек, поэтому его нужно исправить на D2:D4. После чего жмем Enter.
4. Из D6 в ячейку E6 скопируйте функцию =СЧЕТ() – это функция Excel для подсчета количества не пустых ячеек.

На данном примере хорошо видно, что функция =СЧЕТ() игнорирует ячейки, которые не содержат числа или пусты.

Функция ЕСЛИ в Excel

В ячейки D7 и E7 введем логическую функцию, которая позволит нам проверить, все ли студенты имеют оценки. Пример использования функции ЕСЛИ:

1. Перейдите в ячейку D7 и выберите инструмент: «Формулы»-«Логические»-«ЕСЛИ».
2. Заполняем аргументы функции в диалоговом окне как показано на рисунке и жмем ОК (обратите внимание вторая ссылка $A$4 – абсолютная):
3. Функцию из D7 копируем в E7.

Описание аргументов функции: =ЕСЛИ(). В ячейке A4 у нас количество всех студентов, а в ячейке D6 и E6 – количество оценок. Функция ЕСЛИ() проверяет, совпадают ли показатели D6 и E6 со значением A4. Если совпадают, то получаем ответ ДА, а если нет – ответ НЕТ.

В логическом выражении для удобства применяется 2 типа ссылок: относительная и абсолютная. Это позволяет нам скопировать формулу без ошибок в результатах ее вычисления.

Примечание. Закладка «Формулы» предоставляет доступ только к наиболее часто используемым функциям. Больше можно получить, вызвав окно «Мастер функций» нажав на кнопку «Вставить функцию» вначале строки формул. Или нажать комбинацию клавиш SHIFT+F3.

Как округлять числа в Excel

Функция =ОКРУГЛ() более точна и полезнее чем округление с помощью формата ячеек. В этом легко убедиться на практике.

1. Создайте исходную таблицу так как показано на рисунке:
2. Ячейку B2 отформатируйте так, чтобы были отображены только 2 знака после запятой.

   Это можно сделать с помощью диалогового окна «Формат ячеек» или инструментом расположенном на закладке «»-«Уменьшить разрядность»

3. В столбце C проставьте формулу вычитания 1,25 из значений столбца B: =B-1,25.

Теперь вы знаете, как пользоваться функцией ОКРУГЛ в Excel.

Описание аргументов функции =ОКРУГЛ():

1. Первый аргумент – это ссылка на ячейку значение, которой нужно округлить.
2. Второй аргумент – это количество знаков после запятой, которое нужно оставить после округления.

Внимание! Форматирование ячеек только отображает округление но не изменяет значение, а =ОКРУГЛ() – округляет значение. Поэтому для вычислений и расчетов нужно использовать функцию =ОКРУГЛ(), так как форматирование ячеек приведет к ошибочным значениям в результатах.

Источник: //exceltable.com/funkcii-excel/rabota-s-funkciyami-excel

17 Тема 4_3 Мастер функций

Занятие17

Тема4. Электронные таблицы.

Тема4.2. Мастер функций

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

2. Виды функций.

3. Примеры функций.

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

Встроенный Мастер функций помогаетправильно применять функции на всехэтапах работы и позволя­етза два шага строить и вычислять большинствофункций. Функции вызываются из спискачерез меню Вставка\Функция илинажатием кнопки Щ на стандартной панелиинструментов.

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

Дляконструирования функций предварительномаркируют ячейку, в которой долженпоявиться результат вычислений. За­темщелчком по пиктограмме Мастера функцийсо значком «fx»открывают диалоговое окно Мастера (рис.).

Вокне Выберите функцию перечисленыпредлагаемые Мас­тером функции, ниже— краткое описание активизирован­нойпользователем функции. Если мышью нажатьна кнопку ок, то появится специальноеокно Аргументы функции, в которое вводятнеобходимые значения и нажимают кнопкуок.

Рис.. Экран «Мастера функций»

Функциейв Excelназывается часть формулы, указывающаяна необходимость выполнения стандартныхвычислений

Функциясостоит из имени функции и следующегоза ним списка аргументов, заключенногов круглые скобки

Напримерфункция =СУММ(А1:А4),

гдеА1:А4 – аргумент, а СУММ – это имя функции.

Аргументаминазываются величины, которые используютсядля вычисления значений функций.Например: числа; ссылки на ячейки идиапазоны ячеек; имена; текст; другиефункции; логические значения и др.

Возвращаемымизначенияминазываются величины, которые являютсярезультатом вычисления функций

Списокаргументов функции

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

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

• Внутри скобок аргументы отделяются друг от друга точкой с запятой.

• Аргументы могут быть как константами, так и выражениями. Эти выражения, в свою очередь, могут содержать другие функции.

• Задаваемые аргументы должны иметь допустимые для данного аргумента функции значения.

• Функции, являющиеся аргументом другой функции, называются вложенными.

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

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

Вформуле =А1+В2-3имеется две ссылки. Смысл данной формулысостоит в том, чтобы взять значение,находящееся в области отображенияячейки с адресом А1, прибавить к немузначение, находящееся в областиотображения ячейки с адресом В2, вычестьтри и поместить результат в областьотображения той ячейки, в областисодержимого которой находится этаформула

Видыфункций:

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

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

ФункцииСсылки и массивы позволяютосуществить поиск данных в списках илитаблицах, найти ссылку на ячейку вмассиве. Например, для поиска значе нияв строке таблицы используется функция ГПР .

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

Текстовыефункциипредоставляютпользователю возможность обработкитекста. Например, можно объединитьнесколько строк с помощью функцииСЦЕПИТЬ .

Логическиефункциипредназначены для проверки одного илинескольких условий. Например,функция ЕСЛИ позволяетопределить, выполняется ли указанноеусловие, и возвращает одно значение,если условие истинно, и другое, если оноложно.

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

Исправлениеошибок в функциях

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

1. Опишите возможности Мастера функций.

2. Назовите Виды функций

3. Что такое функция в Excel?

Источник: //StudFiles.net/preview/2087243/