Табличный процессор Microsoft Excel 2007

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

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

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

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

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

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

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

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

4.Логические функции. 7

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

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

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

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

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

6.Печать рабочего листа Excel. 16

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

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

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

* математические;

* финансовые;

* статистические;

* даты и времени;

* логические;

* работа с базой данных;

* проверки свойств и значений; ... и другие.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если в появившемся списке отсутствует требуемая функция, то следует активизировать строку «Другие функции…» и работать далее с диалоговым окном Мастер функций , как описано выше.

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

Функций MS Excel 2010

Для вычислений в редакторе «Microsoft® Excel® 2010» помимо обычных арифметических операторов применяются специальные заранее созданные формулы, которые выполняют вычисления по введенным величинам и в указанном порядке. Данные формулы называются функциями .

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

Выделите ячейку, где будет располагаться функция. Перейдите на вкладку «Формулы» и в группе «Библиотека функций» щелкните по кнопке «Вставить функцию».

В открывшемся диалоговом окне «Мастер функций – шаг первый из двух» в графе «Категории» раскройте список существующих типов функций.

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

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

Откройте список «Десять недавно использовавшихся».

В группе «Выберите функцию» выберите «СУММ». Как вы можете заметить, в нижней части окна отображается описания для каждой из функции. Перейдите к следующему шагу щелчком по кнопке «ОК».

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

Затем выделите поле «Число два» и выберите ячейку из документа, с которой вы хотите сложить ранее отмеченные данные.

Закройте окно кнопкой «ОК».

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

Сложение различных чисел или ячеек с данными является одной из востребованных операций при вычислении формул в «Excel®». Для сложения большого числа ячеек удобней использовать специальную функцию «Автосуммирование». Для того чтобы сразу сложить несколько значений, выделите ячейку, где будут происходить вычисления.

На вкладке «Функция» в группе «Библиотека функций» раскройте список «Автосумма» и выберите команду «Сумма».

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

Таким образом, используя функции редактора «MS Excel® 2010», вы сможете выполнять разнообразные вычисления, что позволит максимально быстро и удобно работать с вычислением данных в электронных таблицах.

(0)

Обзор программы
	1.	Интерфейс программы	4:21	2	81866
	2.	Меню «Файл»	2:28	0	24183
	3.	Панель вкладок	5:03	1	22472
	4.	Меню команд	3:08	0	17164
	5.	Средства просмотра	3:41	0	15545
	6.	Основные настройки программы	2:46	0	16446
Работа с книгами
	7.	Создание, открытие и сохранение рабочей книги	7:06	0	18188
	8.	Работа с книгами	4:20	1	14413
	9.	Ячейки и диапазон ячеек	4:39	1	18364
	10.	Имена ячеек и примечания	5:17	11	18630
	11.	Ввод данных	2:37	0	15012
	12.	Автозаполнение ячеек	1:34	0	17201
	13.	Поиск данных	2:23	0	15321
	14.	Форматирование данных	3:22	0	14317
	15.	Графическое форматирование ячеек	2:47	0	14200
Работа с таблицами
	16.	Таблицы	2:16	0	30963
	17.	Работа с таблицами	4:00	2	34724
	18.	Сортировка данных	2:56	0	18750
	19.	Итоги	2:02	0	16561
	20.	Вычисляемые столбцы	2:02	0	20925
	21.	Фильтрация данных	2:35	0	15539
	22.	Пользовательские фильтры	2:03	0	12313
	23.	Сводная таблица	3:05	0	46371
	24.	Вычисляемые поля	1:52	0	15362
	25.	Консолидация табличных данных	2:30	0	18173
	26.	Оформление таблиц	2:23	0	13133
	27.	Запись макроса	3:00	0	24241
Формулы и функции
	28.	Обзор Формул	2:42	0	26116
	29.	Копирование формул	2:35	0	13181
	30.	Массивы	2:34	0	15464
	31.	Мастер функций	3:59	0	16326
Работа с диаграммами
	32.	Обзор диаграмм	3:16	0	19177
	33.	Форматирование диаграммы	2:22	0	6514
	34.	Настройка осей диаграммы	2:02	0	31139
	35.	Параметры шкалы диаграммы	1:54	0	6570
	36.	Подписи данных	2:35	0	5632
	37.	Таблица данных	2:23	0	5419

Конспект урока

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

Занятие 26. Работа с мастером функций. Цель занятия: Научиться вставлять в формулы функции развить умение по использованию мастера функций. Ход работы: Подготовить ПЭВМ к работе Загрузить программу Microsoft Excel. Ознакомиться с инструкцией к практиче

Занятие 26. Работа с мастером функций.

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

Ход работы :

1. Подготовить ПЭВМ к работе
2. Загрузить программу Microsoft Excel .
3. Ознакомиться с инструкцией к практическому занятию.
4. Выполнить задания 1-3
5. Выполнить практическое задание

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

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

• Как числовое значение (например, 89 или – 5,76),
• Как координату ячейки (это наиболее распространенный вариант),
• Как диапазон ячеек (например, С3: F 3).

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

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

Когда последний аргумент введен, следует набрать правую круглую скобку, чтобы обозначить конец списка аргументов. Затем необходимо щелкнуть на кнопке ВВОД в строке формул, или нажать < Enter >, или клавишу управления курсором – и функция будет вставлена в ячейку, куда Excel занесет результат вычислений.

Безусловно, функцию можно ввести, набрав ее прямо в ячейке. Однако Excel предоставляет на стандартной панели инструментов кнопку Вставка функции . В открывшемся диалоговом окне Мастер функций – шаг 1 указывается нужная функция, затем Excel выводит диалоговое окно Аргументы функции , в котором необходимо ввести аргументы функции.

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

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

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

Упражнение

1. Загрузите программу Excel .
2. Откройте свою рабочую книгу.
3. Сделайте активным лист Ведомость1 с таблицей для расчета среднего балла, установите курсор в любую свободную ячейку
4. Выполните команду Вставка-Функция .
5. Изучите категории функций Математические , Статистические , Дата и время .
6. Получите описание функций СУММ , СРЗНАЧ , МИН , МАКС , СЕГОДНЯ .

1. Откройте свою рабочую книгу, сделайте активным лист Ведомость 1
2. Установите курсор в ячейку I 5 (формула для расчета среднего балла первого учащегося).
3. Вызовите мастер функций.
4. Выберите категорию Статистические, функцию СРЗНАЧ, нажмите О k .
5. В диалоговом окне Аргументы функции появятся поля, куда необходимо вести аргументы – диапазон D 5: G 5 (можно просто перетащить мышь в таблице, выделив нужный диапазон).

После этого в поле Число1 появится адрес ячейки (либо адрес диапазона) и одновременно в окне справа – числовое значение, введенное в эту ячейку, а также внизу диалогового окна после слова Значение отразится итоговое значение функции.

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

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

ЗАДАНИЕ 2 :

На листе Ведомость 1 под расчетной таблицей подведите итоги, используя следующие формулы:

МИН(I5 : I14 )	Минимальный средний балл
МАКС(I5 : I14 )	Максимальный средний балл
СРЗНАЧ(D5 : D14 )	Средняя отметка по информатике
СРЗНАЧ(Стипендия!D5:D14)	Средняя стипендия
Сумм(Стипендия!D 5 :D1 4 )	Суммарная стипендия

Результаты сопроводите пояснительными надписями.

1. На лист Группа скопируйте столбцы №, Фамилия , Имя из таблицы на листе Ведомость1 .
2. В ячейку А1 введите название таблицы Расчет возраста учащихся .
3. Добавьте к таблице столбцы Дата рождения и Возраст в годах .
4. Заполните столбец Дата рождения в соответствии с Таблицей 6 , выданной преподавателем.
5. В ячейку В2 вставьте функцию =СЕГОДНЯ(), которая в качестве результата возвращает текущую дату. В ячейку С2 введите пояснительный текст.
6. Для расчета возраста в годах первого учащегося введите в ячейку формулу =$B$2-F5 .
7. Объясните, для чего в формуле используется абсолютная ссылка на ячейку В2 .
8. Скопируйте формулу на остальные ячейки столбца Возраст в годах .
9. Результат вычисления будет отображаться в формате Дата (11.05.18 – 11 дней, 5 месяцев, 18 лет). Значение возраста необходимо отобразить только в годах. Для этого создадим свой собственный, пользовательский формат :
   • выделите ячейки со значениями возраста
   • выполните команду Формат-Ячейки
   • во вкладке Число в поле Числовые форматы выберите (все форматы)

в поле Тип выберите любой формат даты и удалите ненужные параметры даты, оставив только ГГ:

1. Сохраните изменения в рабочей книге.

ПРАКТИЧЕСКОЕ ЗАДАНИЕ

Вставьте после листа Стипендия лист Функции .

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

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

Вопросы для самопроверки:

1. Что представляет собой функция?
2. Каким образом можно ввести функцию в ячейку?
3. Как записываются аргументы функции?
4. Как вызвать мастера функций?
5. Опишите этапы работы с мастером функций.
6. Приведите примеры категорий функций.
7. Какие функции вы встречали на уроках математики?
8. Какими статистическими функциями вы пользовались сегодня на занятии?

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

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

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

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

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

2. Активизировать на вкладке Формулы команду Вставить функцию либо при помощи кнопки Вставить функцию , которая находится начале строки формул. Они полностью однотипны по своему действию, пользуйтесь, какой будет удобно. В результате отобразится окно Мастер функций – шаг 1 из 2 .

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

3. В открывшемся диалоговом окне выбираем категорию функции (например, Статистические ), и в поле Выберите функцию указываем требуемую функцию (допустим, СРЗНАЧ), после чего нужно нажать кнопку OK .

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

4. В открывшемся диалоговом окне Аргументы функции следует осуществить ввод значений или выражений в поля аргументов выбранной функции. Для функции СРЗНАЧ в поле Число1 нужно указать адрес диапазона ячеек, для которых вычисляется среднее значение. Допустим, это такой диапазон: F4:F13 (редактор, как правило, определяет такие диапазоны самостоятельно или же его можно задать, введя с клавиатуры).

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

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

5. После выполнения всех указанных действий для завершения процесса создания формулы нажать в окне Мастера функций кнопку ОК . При этом готовая формула вставляется в редактируемую ячейку и в ячейке отображается результат ее работы.

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

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

Цель работы:

· приобрести и закрепить практические навыки по созданию электронной таблицы с использованием возможностей автозаполнения, автосуммирования и копирования;

· приобрести и закрепить практические навыки по применению функций категории Статистические с использованием Мастера функций.

Теоретический материал

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

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

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

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

Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.

Ввод формул с клавиатуры

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

Операторы (знаки действий) вводятся с использованием следующих клавиш:

· сложение – клавиша клавиатуры + (плюс);

· вычитание – клавиша клавиатуры – (минус или дефис);

· умножение – клавиша клавиатуры * (звездочка);

· деление – клавиша клавиатуры / (дробь);

· возведение в степень – клавиша клавиатуры ^ (крышка).

Например, при создании формулы для расчета стоимости товара Баунти в ячейке D2 таблицы на рис. 2.1 необходимо выделить ячейку D2 , ввести с клавиатуры знак = В2 , ввести с клавиатуры знак * , щелкнуть левой кнопкой мыши по ячейке С2 .

Рис. 2.1. Ввод формулы с клавиатуры

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

Для подтверждения ввода формулы в ячейку следует нажать клавишу клавиатуры Enter или нажать кнопку Ввод (зеленая галочка) в строке формул.

Создание формул с использованием мастера функций

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

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

Для создания формулы следует выделить ячейку и нажать кнопку Вставка функции в строке формул. Можно также нажать комбинацию клавиш клавиатуры Shift + F3 .

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

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

Рис. 2.2. Выбор функции

Например, для округления числа следует выбрать категорию Математические , а функцию ОКРУГЛ .

Если название нужной функции неизвестно, можно попробовать найти ее по ключевым словам. Для этого после запуска мастера функций в поле Поиск функции диалогового окна Мастер функций: шаг 1 из 2 (рис. 2.3) следует ввести примерное содержание искомой функции и нажать кнопку Найти .

Рис. 2.3. Поиск функции

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

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

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

Рис. 2.4. Задание аргументов функции

Текст, числа и логические выражения в качестве аргументов обычно вводят с клавиатуры.

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

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

Следует иметь в виду, что некоторые функции не имеют аргументов.

По окончании создания функции следует нажать кнопку ОК или клавишу клавиатуры Enter .