Табличный процессор Microsoft Excel 2007
Использование функций в 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 уровней вложения функций.
Цель работы:
· приобрести и закрепить практические навыки по созданию электронной таблицы с использованием возможностей автозаполнения, автосуммирования и копирования;
· приобрести и закрепить практические навыки по применению функций категории Статистические с использованием Мастера функций.
Теоретический материал
Формулы представляют собой выражения, по которым выполняются вычисления. Формула всегда начинается со знака равно (= ). Формула может включать функции, ссылки, операторы и константы.
Функция – стандартная формула, которая обеспечивает выполнение определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы, особенно если они длинные или сложные.
Ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле. Можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями.
Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.
Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.
Ввод формул с клавиатуры
Формулы можно вводить с использованием клавиатуры и мыши. С использованием клавиатуры вводят операторы (знаки действий), константы (преимущественно числа) и иногда функции. С использованием мыши выделяют ячейки, включаемые в формулу. Адреса ячеек (ссылки) также можно вводить с клавиатуры, обязательно в английской раскладке.
Операторы (знаки действий) вводятся с использованием следующих клавиш:
· сложение – клавиша клавиатуры + (плюс);
· вычитание – клавиша клавиатуры – (минус или дефис);
· умножение – клавиша клавиатуры * (звездочка);
· деление – клавиша клавиатуры / (дробь);
· возведение в степень – клавиша клавиатуры ^ (крышка).
Например, при создании формулы для расчета стоимости товара Баунти в ячейке 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 .
Обращение к Мастеру функций :Вставка Функция… или через Адресное поле во время ввода формулы (после ввода символа "="). На первом шаге выбирают функцию, на втором – вводят аргументы в указанные поля.Если какой-нибудь аргумент содержит дополнительную функцию, повторно обращаются к Мастеру функций только через Адресное поле. Если после ввода аргументов этой вспомогательной функции формула не закончилась, делают в информационном поле щелчок по названию той функции, к которой надо перейти. Если после ввода функции формула не закончилась, делают в информационном поле щелчок после уже набранной части формулы и продолжают её набор. Нажимают <ОК> только после окончания набора всей формулы.
Функция ЕСЛИ() (лаб. раб. СамостЕсли, СложноеЕсли) . Позволяет предусмотреть разные способы расчёта значения этой функции. Выбор нужного варианта осуществляется автоматически в результате проверки условия, введённого в неё и зависящего от данных, введённых на Рабочий лист. Общий вид функции:
ЕСЛИ(Условие;ДействияПриПравильном;ДействияПриНеправильном)
По стандарту проверка первого аргумента должна выработать признак ИСТИНА или ЛОЖЬ.
Примеры условий (логических утверждений ):
D4>T5A5=2 И(X2>=7;F2<=$D$8) ИЛИ(V7=$S$2;K9>=E2;J7<8)
В двух последних случаях сложные логические утверждения можно вводить через вспомогательные функции категории Логические. Примеры – см. практические занятия.
Если при данных, существующих в данный момент во влияющих ячейках, проверка Условия показывает признак ИСТИНА, то используется алгоритм из второго аргумента (ДействияПриПравильном), в противном случае – из третьего (ДействияПриНеправильном).
Если надо предусмотреть три или более варианта расчётов при разных условиях, то в первом аргументе записывают условие, правильное для первого варианта, во втором аргументе записывают этот вариант, в третий аргумент ставят вспомогательную функцию ЕСЛИ() с правильным условием для второго варианта расчётов, в её втором аргументе записывают этот второй вариант и т. д. Вставку ЕСЛИ() в третий аргумент повторяют до тех пор, пока все варианты не будут разобраны. Пример (влияющая ячейка А4):
ЕСЛИ(А4<=10;"плохо";ЕСЛИ(А4<=20;"так себе";Если(A4<=30;"нормально";"превосходно")))
Если в А4 введено число 7, то ЕСЛИ() запишет в свою ячейку " плохо". Если в А4, например, 25, то первая проверка выдаст признак ЛОЖЬ и будет выбран третий аргумент. На него функция автоматически переходит при А4>=7. Функция ЕСЛИ() в нём сделает дополнительную проверку и тоже выдаст признак ЛОЖЬ, поэтому будет задействован её третий аргумент. Его функция ЕСЛИ() сделает ещё одну проверку. На этот раз она выдаст признак ИСТИНА, и будет выбрано слово "Нормально". Другие примеры и приёмы замены проверки одного сложного условия проверкой нескольких простых – см. практические занятия.
Построение диаграмм (лаб.Раб. ТаблицыДиаграммы, Курсовая, Функции массива и имена, Население Европы).
Диаграммы строятся по таблице данных, заранее введённых на рабочий лист.
Первый этап. Построение эскиза диаграммы с помощью Мастера диаграмм . Вызов Мастера диаграмм:Вставка Диаграмма… Далее 4 шага.
Первый – выбор типа диаграммы. Если аргументы – числа или в дальнейшем надо будет строить тренд для прогнозов, то выбирают Точечную, если аргументы – текстовые пояснения, то любую из остальных. Если изображают только один показатель, и важна доля каждого значения в общей сумме, то удобна Круговая (пример: выручка разных отделов магазина), Для нескольких показателей на одной диаграмме удобны График или Гистограммы.
Второй шаг – задают исходные данные. Удобно – на вкладкеРяды . Кнопкой <Удалить> удаляют то, что вставил Мастер самостоятельно, потом <Добавить> – появляется чистый бланк. В полеИмя вводят короткое название показателя, для которого строится диаграмма (можно ввести адрес ячейки, в которую оно введено). В полеЗначения Y (или простоЗначения ) – адрес блока со значениями показателя. В полеЗначения Х (илиПодписи по оси Х илиПодписи категорий ) – координаты блока с аргументами или текстовыми пояснениями к значениям показателя. Если нужно совместить несколько показателей на одной диаграмме, то кнопкой <Добавить> для каждого показателя вызывают новый бланк и заполняют по аналогии с первым.
Третий шаг – выбирают элементы оформления диаграммы (пояснительные заголовки, сетку по осям, положение легенды и т. п.).
Четвёртый шаг – определяют, куда "вклеивать" эскиз диаграммы.
Второй этап. Форматирование диаграммы и исправление параметров, неудачно заданных на первом этапе. Основные действия:
– Изменение положения и размеров всей диаграммы и её отдельных фрагментов: щелчок по полям или по нужному фрагменту, буксировка границы рамки или маркеров на ней.
– Изменение внешнего вида фрагментов: щелчок по нужному фрагменту, затем Формат Выделенный элемент… или щелчок правой кнопкой по нужному фрагменту ипервая команда в Контекстном меню . Можно менять заливку, цвет, тип и толщину линий, шкалу масштаба осей, размер и цвет надписей и т. п.
– Изменение параметров, заданных на первом этапе: щелчок по любому фрагменту диаграммы, вместо меню Данные в строке меню появляется менюДиаграмма . Первые 4 команды – отдельные шаги Мастера диаграмм, можно сделать изменения, не затрагивая остальных шагов работы.
Дополнительные возможности:
– добавление линии тренда (линия, сглаживающая табличные данные) ;
– добавление на диаграмму нового ряда данных при тех же подписях по горизонтальной оси, что и для уже внесённых на диаграмму рядах (вместо вызова команды Диаграмма Исходные данные… ).
Линия тренда. Тренд – это формула (обычно несложная), которая в заданном диапазоне аргументов хорошо совпадает с табличными данными . Создание:
Диаграмма Добавить линию тренда…
На вкладке Тип выбрать по образцам вид тренда.
На вкладке Параметры ввести понятное название для тренда, заказать, если нужно, прогнозы за пределы имеющегося диапазона аргументов, выбрать параметрПоказывать уравнение на диаграмме .
Нажать <ОК>.
Обычно тренд строят по диаграмме типа Точечная. Если тренд строят по гистограмме, то его аргумент – порядковый номер точки данных в таблице .
Коэффициенты тренда, представленные в формуле на диаграмме, округлены до 4-5 цифр. Иногда это приводит к большим погрешностям при прогнозах по формуле тренда. Чтобы проверить, можно ли пользоваться округлёнными коэффициентами, к таблице данных для диаграммы добавляют столбец (строку) с расчётом тренда для каждого из табличных аргументов. Если совпадение с табличными данными плохое, то тренд пересчитывают по методу наименьших квадратов.
Использование Мастера функций
Мастер функций управляет процессом ввода функции. Для активизации Мастера функций выбрать команду Вставка , Функция . Появится диалоговое окно Мастер функций .
Встроенные функции Excel объединены в различные категории согласно типу производимых с их помощью расчетов. В списке Категория отображается набор всех категорий, а в списке Выберите функцию - набор функций выбранной категории в алфавитном порядке. Например, чтобы получить доступ к функции Дата , выделить элемент Дата и время в списке Категория и функцию Дата в списке Выберите Функцию . Нажать кнопку ОК . Указанная функция появится в строке формул. В следующем диалоговом окне ввести необходимые аргументы. Аргументом должна быть ссылка на отдельную ячейку или на группу ячеек, число или другая функция. Некоторые функции имеют один или несколько аргументов, другие функции не имеют их вовсе. В формуле аргументы функции должны быть заключены в скобки и отделены друг от друга точкой с запятой.
Чтобы ввести значение аргумента, необходимо поместить точку вставки в соответствующее поле ввода с помощью мыши. В области диалогового окна над полями ввода появится описание выбранного аргумента. Далее выделить с помощью мыши ячейку или диапазон, который нужно использовать в качестве аргумента функции, или ввести ссылку или имя соответствующего диапазона. Значение аргумента отображается справа от поля ввода.
Когда введены все обязательные аргументы функции, в текстовом поле Значение диалогового окна Мастера функций отображается результат вычисления функции. Нажать кнопку Готово , чтобы ввести формулу в ячейку. Диалоговое окно будет закрыто, а в активной ячейке появится результат вычисления формулы.
Контрольные вопросы
1. Какими способами можно создавать формулы?
2. С каких символов может начинаться ввод формулы?
3. Как увидеть формулу, записанную в ячейку?
4. Как сделать, чтобы в ячейке отображался не результат вычислений по формуле, а сама формула?
5. Как отредактировать формулу?
7. Перечислите типы ссылок. Их назначение.
9. Перечислите виды арифметических операций, используемых в формулах.
10. Перечислите виды операторов, используемых в формулах.
11. Какие текстовые операторы используются в формулах?
12. Как записывается аргумент встроенной функции?
13. Как можно получить информацию о функциях?
14. Сколько вложенных функций может использоваться одновременно в формуле?
15. Назначение кнопки Автосуммирования?
16. Назначение Мастера функций?
17. Перечислите основные Категории функций.
18. В чём различие между функциями СУММ и СУММЕСЛИ?
19. В чём различие между функциями СЧЕТ и СЧЕТЕСЛИ?
20. В каких случаях используются логические функции?
5. Построение диаграмм
Важным элементом при анализе и выводе на печать результатов в Excel являются диаграммы. На каждой диаграмме можно выделить основные элементы (рис. 2).
В зависимости от места расположения и особенностей построения и редактирования различают два типа диаграмм˸ внедренные диаграммы – помещаются на том же рабочем листе, где и данные, по которым они построены, диаграммы на отдельных листах (листах диаграмм).
Использование Мастера функций - понятие и виды. Классификация и особенности категории "Использование Мастера функций" 2015, 2017-2018.
В Excel автоматизирован вызов функций, вычисляющих итоговые значения в диапазонах ячеек с числовыми данными, Такие функции выбирают из раскрывающегося списка кнопки Автосумма в панели инструментов.
Наименование функции в списке |
Имя функции |
Операция |
Суммировать |
Вычисление суммы чисел. |
|
Вычисление среднего арифметического значения. |
||
СЧЁТ() |
Подсчет количества чисел. |
|
Максимум |
Выбор максимального значения. |
|
Выбор минимального значения. |
Применение функций автовычисления
1 способ:A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
Здесь результат |
A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
A |
B |
C |
D |
|
1 |
||||
2 |
||||
3 |
||||
4 |
||||
5 |
||||
6 |
||||
7 |
Если выделить прямоугольный диапазон, включающий пустые ячейки ниже и правее ячеек с числами, то вычисления будут выполнены для каждого столбца и каждой строки.
3 способ Этот способ позволяет выбрать любой, даже несвязанный диапазон для вычисления:A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
СУММ (А2:А5 ) |
Использование Мастера функций
Для вызова Мастера функций предназначена кнопка Вставка функции в строке формул или в панели инструментов. Примечание . Мастер функций можно также вызвать: Диалоговое окно Мастера функций (рисунок 5.4) содержит два списка: раскрывающийся список Категория и список функций . При выборе категории отображается соответствующий список функций.
Категория "10 недавно использовавшихся" постоянно обновляется, в зависимости от того, какие функции использовались в последнее время. Категория "Полный алфавитный перечень" содержит список всех функций Excel. При выборе функции в нижней части окна появляется ее краткое описание.
После щелчка на кнопке Ok (или нажатия клавиши <Enter >) имя выбранной функции заносится в строку формул вместе со скобками, ограничивающими список аргументов, и одновременно открывается окно Аргументы функции .
Пример такого окна функции показан на рисунке 5.5.
- В заголовке окна отображается имя функции.
- В верхней части располагаются поля ввода аргументов. Наименования обязательных аргументов выделены жирным шрифтом.
- Под полями приводится краткое описание функции.
- Ниже описывается назначение аргументов (это описание обновляется при переходе к очередному полю аргумента).
- В нижней части – результат вычисления и кнопки подтверждения и отмены ввода функции.
Выделив ячейку (диапазон), окно функции можно восстановить, щелкнув на кнопке разворачивания в конце строки.
Кнопка разворачивания
После ввода аргумента справа от поля указывается его значение.
