16381

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

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

Информатика, кибернетика и программирование

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

Русский

2013-06-20

595.5 KB

233 чел.

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

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

«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.Логические функции.

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, в других случаях.

Решение:

В качестве значений аргументов функции ЕСЛИ также могут быть функции как  исполняемые, так  и логические. Так, в данном примере при выполнении условия A1<=0 в ячейку С1 необходимо записать значение функции СТЕПЕНЬ(B1;4). При невыполнении этого условия  нужно проверять условие A1<1 вложенной функцией ЕСЛИ.

Итак, в ячейку C1 необходимо ввести формулу:

=ECЛИ(A1<=0; CТЕПЕНЬ(B1;4); ECЛИ(A1<1; A1; A1/3))

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

Рис.11 Результаты решения Примера 2. Логические функции.

4.1.1.Сложные условия.

Пример 3.

Если и в ячейке А1, и в ячейке B1 и в ячейке C1 записаны положительные числа, то в ячейку D1 записать слово «плюс», если же положительное число записано или в ячейке А1, или в ячейке В1, или в ячейке С1, то в ячейку D1 записать слово «разные», в  противном случае в ячейку D1 записать слово «минус».

Решение:

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

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

Синтаксис функции ИЛИ:

ИЛИ(логическое_значение1; логическое_значение2; ...)

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

Синтаксис функции И:

И(логическое_значение1; логическое_значение2; ...)

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

=ECЛИ(И(A1>0;B1>0;C1>0);"плюс";ECЛИ(ИЛИ(A1>0;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<100;E3<100;E4<100);"все не выполнили";ECЛИ(ИЛИ(E2<100;E3<100;E4<100);"не все не выполнили";"все выполнили "))

Рис.14 Решение Примера 4. Анализ работы бригады.

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

Пример 5.

На рабочем листе Работа бригады в ячейке А8 запишем следующую формулу:

=А7<0

В результате по этой формуле в ячейке А8 будет записано значение ИСТИНА, если в ячейке А7 отрицательное число, в противном случае в ячейке А7 будет записано значение ЛОЖЬ. Анализируя значение в ячейке А8 можем определить, какое число записано в ячейке А7, отрицательное или нет. Для этого, например, в ячейку А10 запишем формулу с использованием функции ЕСЛИ:

Рис.15 Окно функции ЕСЛИ для Примера 5

В результате в ячейке А10 будет записана следующая формула:

=ECЛИ(A8;"отрицательное";"больше или равно нулю")

В этой формуле логическим выражением является адрес (А8), который дает  значение либо ИСТИНА, либо ЛОЖЬ. Ниже приведены результаты расчетов по этой формуле для всех вариантов исходных данных (Рис.16).

Рис.16 Результаты решения Примера 5. Логические функции.

Пример 6.

На свободном рабочем листе в ячейку С1 запишем формулу деления двух чисел, записанных в ячейках А1 и В1:

=А1/В1

Сделаем проверку, является ли значение в ячейке С1 (результат деления) ошибкой. Для этого будем использовать функцию ЕОШИБКА из категории Проверка свойств и значений. Эта функция возвращает значение ИСТИНА, если в вычислениях есть ошибка (например, ДЕЛ/0!-деление на ноль и др.) и ЛОЖЬ, если ошибки нет. Используя функцию ЕОШИБКА  в качестве логического выражения функции ЕСЛИ, запишем в ячейку А2  формулу:

=ECЛИ(ЕОШИБКА(C1);"ошибка";"нет ошибки")

Таким образом, в зависимости от результата операции деления в ячейке А2 соответственно будут записаны слова “ошибка” или “нет ошибки”.

Рис.17 Окно функции ЕСЛИ для Примера 6

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

Рис.18 Результаты решения Примера 6. Логические функции.

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

Создайте на рабочем листе Примеры Функций ещё одну таблицу (Рис.19) (по-прежнему не заполняя последнюю графу, она будет использоваться для сверки результатов):

Рис.19 Задание для самостоятельной работы 4. Логические функции.

  1.  В ячейку С13 ввести большее из значений, содержащихся в соседних слева ячейках.
  2.  В ячейку С14 ввести слово «да», если значение ячейки A14 больше значения ячейки B14. В противном случае ввести слово «нет».
  3.  Создать формулу, записывающую в ячейку С15 слово «да», если ячейка A15 пуста или содержит текст. В противном случае  в ячейку С15 записать слово «нет» (см. ниже Примечание)
  4.  Создать формулу,  определяющую значение ячейки С16 равным:
  5.  -1, если содержимое ячейки B16 меньше 0;
  6.  0, если содержимое ячейки B16 равно 0;
  7.  формулу A16/B16, если содержимое ячейки B16 больше 0.

Сверьте полученные результаты.

Примечание: Для проверки типа значения или ссылки в пакете Excel имеются функции, объединенные в  категорию Проверка свойств и значений. Каждая из этих функций проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ в зависимости от типа.

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

5.Функции из категории Дата и время.

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

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

Переименуйте Лист 5  рабочей книги в Дата и время.

Создайте следующую таблицу (Рис.20):

Рис.20 Задание для самостоятельной работы 5. Функции из категории Дата и время.

В этой таблице в ячейки столбцов B, C, D запишите формулы вычисления дня, месяца и года для дат из ячеек А2:А5. Для этого используйте функции ДЕНЬ, МЕСЯЦ и ГОД из категории  Дата и время.

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

Ниже создайте следующую таблицу (Рис.21):

Рис.21 Задание для самостоятельной работы 6. Функции из категории Дата и время.

В этой таблице в ячейки столбцов B, C, D и E запишите формулы вычисления дня недели, номера квартала, номера декады, номера полугодия, используя по необходимости логическую функцию ЕСЛИ и соответствующие функции из категории  Дата и время.

Задание 7.

В ячейки А15 и А16 ввести текущую дату двумя способами, используя функции СЕГОДНЯ и ТДАТА из категории Дата и время.

Примечание: Функция  ТДАТА требует расширения столбца.

6.Отображение формул на листе Excel

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

  1.  На вкладке ленты Разметка страницы в группе Параметры страницы кнопкой вызова диалогового окна или командой Печатать заголовки вызвать диалоговое окно Параметры страницы. На вкладке Лист этого диалогового окна установить флажок заголовки строк и столбцов.
  2.  На вкладке ленты Формулы в группе Зависимости формул выполнить команду Показать формулы.

7.Вопросы к защите лабораторной работы.

  1.  Какой инструмент в Excel позволяет использовать функции.
  2.  Как вызвать окно Мастер функций в Excel, структура этого окна.
  3.  Вид любой функции.
  4.  Приведите примеры математических, статистических функций.
  5.  Синтаксис логической функции ЕСЛИ.
  6.  Какие значения возвращает логическая функция ЕСЛИ и при каких условиях.
  7.  Синтаксис логической функции И.
  8.  Синтаксис логической функции ИЛИ.
  9.  Какие значения возвращает логические функции И и ИЛИ и при каких условиях.
  10.  Приведите примеры логических функций.
  11.  Приведите примеры функций категории Дата и время.
  12.  Как при печати рабочих листов Excel указать адреса ячеек и расчетные формулы.


 

А также другие работы, которые могут Вас заинтересовать

15818. SQL Server 2005. Программирование на T-SQL 78.5 KB
  SQL Server 2005. Программирование на TSQL Программирование на TSQL Синтаксис и соглашения TSQL. Правила формирования идентификаторов Все объекты в SQL Server имеют имена идентификаторы. Примерами объектов являются таблицы представления хранимые процедуры и т.д. Идентификато
15819. Начало работы с Microsoft SQL Server 2005 187 KB
  Начало работы с Microsoft SQL Server 2005 Утилита SQL Server Management Studio Подавляющую массу задач администрирования SQL Server можно выполнить в графической утилите SQL Server Management Studio. В ней можно создавать базы данных и все ассоциированные с ними объекты таблицы представления ...
15820. Основы Transact SQL: Добавление, изменение и удаление данных 63 KB
  Основы Transact SQL: Добавление изменение и удаление данных. Основы Transact SQL: Добавление изменение и удаление данных в таблицах Запросы рассмотренные ранее были направлены на то чтобы получить данные содержащиеся в существующих таблицах базы данных. Главным ключевым сло...
15821. Основы Transact SQL: Простые выборки данных 241.5 KB
  Основы Transact SQL: Простые выборки данных SQL это аббревиатура выражения Structured Query Language язык структурированных запросов. SQL основывается на реляционной алгебре и специально разработан для взаимодействия с реляционными базами данных. SQL является прежде всего инфор...
15822. Основы Transact SQL: Простые выборки данных 199.5 KB
  Основы Transact SQL: Простые выборки данных Создание вычисляемых полей Конструкция SELECT кроме имен столбцов таблиц может также включать так называемые вычисляемые поля. В отличие от всех выбранных нами ранее столбцов вычисляемых полей на самом деле в таблицах базы дан...
15823. Основы Transact SQL: Сложные (многотабличные запросы) 173.5 KB
  Основы Transact SQL: Сложные многотабличные запросы Основы Transact SQL: Сложные многотабличные запросы В SQL сложные запросы являются комбинацией простых SQLзапросов. Каждый простой запрос в качестве ответа возвращает набор записей таблицу а комбинация простых запросов...
15824. Создание ограничений в SQL Server 2005 416 KB
  Создание ограничений в SQL Server 2005 Создание ограничений Перед тем как начать работать с таблицами следует ограничить вводимые в них данные в целях обеспечения так называемой целостности данных т. е. ограничить возникновение в базе данных некорректных или п
15825. Создание таблиц в SQL Server 2005 376.5 KB
  Создание таблиц в SQL Server 2005 Создание таблиц и ограничений Таблицы представляют собой объекты базы данных используемые непосредственно для хранения всех данных. Одним из самых главных правил организации баз данных является то что в одной таблице должн
15826. Мифологическое знание как разновидность модельных представлений о психической регуляции труда 28.77 KB
  Мифологическое знание как разновидность модельных представлений о психической регуляции труда Для исторической науки в целом реконструкция религиозных представлений является важным критерием уровня духовного развития человеческой общности изучаемого периода иб...