Использование Функций в Excel Лабораторная Работа

Доступные файлы (1):

Формулы в Excel состоят из значений, функций, адресов ячеек и диапазонов (ссылок) и арифметических операций.

Перед формулой всегда ставится знак = (равенство). Например, чтобы вычислить среднее арифметическое значений, занесенных в ячейки A1, B1 и C1, надо ввести формулу в виде:

• выделить ячейку;
• ввести с клавиатуры знак =;
• ввести формулу либо непосредственно в ячейку, либо в строке формул. Ссылку на ячейку в формуле можно набрать с клавиатуры или выделить эту ячейку;
• для подтверждения окончания ввода надо щелкнуть на кнопке Ввод в строке формул или нажать клавишу [Enter]. При использовании кнопки Ввод ячейка с формулой остается выделенной. После нажатия клавиши [Enter] выделяется ячейка, стоящая ниже;
• отменить ввод можно, либо нажав клавишу [Esc], либо щелкнув на кнопке Отмена в строке формул. Эти действия выполняются до подтверждения ввода. Если формула уже введена, то отменить ее можно, щелкнув на кнопке Отменить на панели инструментов Стандартная.

Задание

1. Откройте рабочую книгу Учебный.xls.
2. Выделите Лист2 и установите на Стандартной панели инструментов масштаб просмотра 75%.
3. Выделите свободную ячейку J5.
4. Введите с клавиатуры формулу: =H5–I5.
5. Нажмите клавишу [Enter]. В ячейке J5 отобразится результат 50.
6. Выделите свободную ячейку J6.
7. Введите с клавиатуры знак = (равенство).
8. Выделите ячейку H6.
9. В строке формул введите с клавиатуры знак – (минус).
10. Выделите ячейку I6.
11. Щелкните на кнопке Ввод в строке формул. В ячейке J6 отобразится результат 130.
12. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

В Excel можно вместо адресных ссылок в формулах задавать заголовки строк и столбцов таблицы. Например, ячейка I5 может иметь адрес: Расход Июль, где Расход – заголовок столбца, Июль – заголовок строки. Для задания заголовков в формулах надо в диалоговом окне Параметры (пункт меню Сервис) на вкладке Вычисления установить флажок допускать названия диапазонов.

Задание

1. Откройте диалоговое окно Параметры и на вкладке Вычисления установите флажок допускать названия диапазонов. Щелкните на кнопке ОК.
2. Выделите ячейку J7.
3. В строке формул введите формулу: =Приход–Расход.
4. Нажмите клавишу [Enter]. В ячейке J7 отобразится результат 230.
5. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

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

Чтобы отредактировать формулу, надо либо дважды щелкнуть на ячейке с формулой, либо отредактировать ее в строке формул.

Задание

1. Выделите ячейку J5 и просмотрите формулу в строке формул. Ее вид: =H5–I5.
2. Выделите ячейку J6 и просмотрите формулу в строке формул. Ее вид: =H6–I6.
3. Выделите ячейку J7 и просмотрите формулу в строке формул. Ее вид: =Приход–Расход.
4. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

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

Задание

1. Выделите ячейки J6 и J7 и очистите их, нажав клавишу [Delete].
2. Выделите ячейку J5.
3. Протащите маркер ячейки J5 на ячейки J6 и J7.
4. Выделите ячейку J6 и просмотрите формулу в строке формул. Ее вид: =H6–I6.
5. Выделите ячейку J7 и просмотрите формулу в строке формул. Ее вид: =H7–I7.
6. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

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

Если при копировании формулы необходимо сохранить все или некоторые ссылки без изменения, то используется абсолютная адресация, а адресные ссылки называются абсолютными ссылками. Для указания абсолютной ссылки перед именем столбца и перед номером строки ставится знак $ (знак доллара). Например, ссылка $I$8 является абсолютной ссылкой.

Иногда при копировании в формулах указываются смешанные ссылки. Например, смешанными ссылками являются:

$A2 – здесь ссылка на столбец A – абсолютная (столбец A не изменяется при копировании), а ссылка на строку 2 – относительная (номера строк будут изменяться).

B$4 – здесь ссылка на столбец B – относительная (адреса столбцов будут изменяться), а ссылка на строку 4 – абсолютная (строка 4 не меняется при копировании).

Задание

1. Выделите ячейку К5 и введите в нее формулу: =I5/$I$8.
2. Протащите маркер ячейки K5 на ячейки K6 и K7, чтобы скопировать формулу в эти ячейки.
3. Выделите ячейку K5 и просмотрите формулу в строке формул. Ее вид: =I5/$I$8.
4. Выделите ячейку K6 и просмотрите формулу в строке формул. Ее вид: =I6/$I$8.
5. Выделите ячейку K7 и просмотрите формулу в строке формул. Ее вид: =I7/$I$8.
6. Выделите диапазон ячеек K5:K7 и установите в нем формат Процентный. Результаты вычислений этих формул отражают процентное отношение от общего расхода (ячейка I8) за соответствующий месяц.
7. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

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

Задание

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

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

Задание

1. Откройте Лист1 рабочей книги Учебный.xls и установите масштаб просмотра 60%.
2. Выделите свободную ячейку H25, в которой будем подсчитывать сумму чисел.
3. Наберите на клавиатуре: =СУММ(
4. Выделите ячейки C5 и C6 с числами 17,5 и –4.
5. Теперь наберите на клавиатуре закрывающуюся скобку ) и нажмите клавишу [Enter]. Результат вычисления в ячейке H25 будет равен 13,5.
6. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

Задание

1. Выделите свободную ячейку H26.
2. Щелкните на кнопке мастера функций f_x (Вставка функции) в строке формул.
3. В диалоговом окне Мастер функций – шаг 1 из 2 из списка Категория: выберите элемент Полный алфавитный перечень, а затем в области Выберите функцию: выберите функцию СЧЕТ (эта функция подсчитывает количество выделенных элементов).

В диалоговом окне щелкните на кнопке свертывания в правой части поля ввода Значение1.

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

Задание

1. Выделите диапазон ячеек с числовыми данными C5:C9 столбца Числа.
2. Щелкните правой кнопкой мыши на свободном месте строки состояния.
3. Из появившегося списка выберите функцию: Максимум.
4. В строке состояния прочтите результат:

Пример 1

1. Выделите на Листе1 свободную ячейку C25.
2. Вызовите мастер функций.
3. В диалоговом окне Мастер функций – шаг 1 из 2 из списка Категория: выберите элемент Математические, а в области Выберите функцию: выберите функцию LOG.

1. На втором шаге в диалоговом окне Аргументы функции в поле ввода Число введите число 2 – первый аргумент функции.

1. В диалоговом окне Аргументы функции в поле ввода Основание введите основание 3 – второй аргумент функции.

1. Щелкните мышью в строке формул и введите: +1. Нажмите клавишу [Enter]. В ячейке C25 прочтите результат:

1. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

Пример 2

1. Выделите ячейку A25.
2. Щелкните в поле Имя и введите имя переменной x. Нажмите клавишу [Enter]. Теперь адресу ячейки A25 присвоено имя x.
3. Аналогично адресу ячейки B25 присвойте имя переменной y.
4. Введите в ячейку x число 4. Нажмите клавишу [].
5. Введите в ячейку y число 3. Нажмите клавишу [].
6. Выделите свободную ячейку C26.
7. Вызовите мастер функций.
8. В диалоговом окне Мастер функций – шаг 1 из 2 из категории Математические выберите функцию ASIN. Щелкните на кнопке ОК.
9. В диалоговом окне Аргументы функции в поле ввода Число наберите формулу: x/(x^2+1).
10. Щелкните мышью в строке формул и введите знак +.
11. В поле Имя откройте список функций. Если функции ATAN нет в списке, то выберите элемент Другие функции… Откроется диалоговое окно Мастер функций – шаг 1 из 2, в котором выберите функцию ATAN. Щелкните на кнопке ОК.
12. В диалоговом окне Аргументы функции в поле ввода Число наберите формулу: y+2. Щелкните на кнопке ОК. В ячейке C26 прочтите результат:

1. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

Пример 3

1. Выделите свободную ячейку C27.
2. Введите с клавиатуры формулу: =КОРЕНЬ(TAN(x)+1). Нажмите клавишу [Enter]. В ячейке C27 прочтите результат:

1. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

Пример 4

1. Выберите команду: СервисПараметры…
2. В диалоговом окне Параметры на вкладке Вычисления установите флажок допускать названия диапазонов. Щелкните на кнопке ОК.

1. В ячейку E25 введите наименование аргумента функции: z.
2. В ячейку F25 введите наименование функции: sin z.

1. В ячейках диапазона E26:E35 создайте последовательность в виде прогрессии: 1, 2, 3, …, 10. Эта прогрессия определяет значения аргумента z.
2. В ячейку F26 введите формулу: =sin(z).
3. Скопируйте с помощью маркера формулу в ячейки диапазона F27:F35.
4. Уменьшите разрядность вычисленных значений функции до трех знаков после запятой.
5. Выделите диапазон ячеек таблицы E25:F35 вместе с заголовком и щелкните на кнопке выравнивания По центру на панели инструментов Форматирование. Результат получен в виде таблицы.

1. Щелкните на кнопке Сохранить на панели инструментов Стандартная.

Решением системы является нахождение корней: kx и ky, т.е. таких значений x и y, которые после подстановки в систему обращают уравнения в тождества.

Из расчетной части рис.1.3 видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются?

Урок информатики по теме: Excel: Построение графиков функций по заданным параметрам

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

Урок информатики по теме: «Excel: Построение графиков функций по заданным параметрам»

Информатика. Базовый курс 2-е издание/Под ред. С.В. Симоновича. — СПб.: Питер, 2004.-640с.:ил.

1. Организационный момент – 3 мин.
2. Проверка домашнего задания –10 мин.
3. Объяснение нового материала –20 мин.
4. Применение полученных знаний –20 мин.
5. Самостоятельная работа. – 20 мин
6. Подведение итогов урока. Домашнее задание – 7 мин.

Проверка готовности учащихся к уроку, отметка отсутствующих, объявление темы и цели урока

Проверка домашнего задания. (фронтальный опрос)

1. Что представляет собой рабочая область программы Excel?
2. Как определяется адрес ячейки?
3. Как изменить ширину столбца, высоту строки?
4. Как ввести формулу в Excel?
5. Что такое маркер заполнения и для чего он нужен?
6. Что такое относительная адресация ячеек?
7. Что такое абсолютная адресация ячеек? Как она задается?
8. Что такое колонтитулы? Как они задаются?
9. Как задать поля печатного документа? Как изменить ориентацию бумаги?
10. Что такое функциональная зависимость у = f(х)? Какая переменная является зависимой, а какая независимой?
11. Как ввести функцию в Excel?
12. Что такое график функции у = f(х)?
13. Как построить диаграмму в Excel?

При объяснении нового материала может быть использован файл Excel с шаблонами задач (Приложение 1), который выводится на экран с помощью проектора

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

Рассмотрим построение графиков функций на примере функции у = sin x.

Вид данного графика хорошо известен вам по урокам математики, попробуем построить его средствами Excel.

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

Задача 1.Построить график функции у = sin x на отрезке [– 2; 2] с шагом h = 0,5.

Таким образом, мы получили таблицу аргументов (х) и значений (у) функции у = sin x на отрезке [-2;2] с шагом h = 0,5 :

6. Следующий шаг. Выделим таблицу и вызовем Мастер диаграмм. На первом шаге выберем во вкладке Нестандартные Гладкие графики.
7. На втором шаге во вкладке Ряд выполним:

В поле Ряд необходимо выделить ряд х и нажать на кнопку “Удалить” (график изменений х нам не нужен. График функции – это график изменения значений у)

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

На самом деле пока это мало похоже на график функции в нашем привычном понимании.

• Вызовем контекстное меню оси ОУ. Затем, выберем пункт Формат оси…. Во вкладке Шкала установим: цена основного деления: 1. Во вкладке Шрифт установим размер шрифта 8пт.
• Вызовем контекстное меню оси ОХ. Выберем пункт Формат оси….

Во вкладке Шкала установим: пересечение с осью ОУ установите номер категории 5 (чтобы ось ОУ пересекала ось ОХ в категории с подписью 0, а это пятая по счету категория).

Во вкладке шрифт установите размер шрифта 8пт. Нажмите на кнопку ОК.

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

Пригласить к проектору студента и сформулировать следующую задачу.

Задача 2. Построить график функции у = х 3 на отрезке [– 3; 3] с шагом h = 0,5.

1. Создать следующую таблицу: Создать таблица значений функции у = f(х).

Таким образом, должна получиться таблица аргументов (х) и значений (у) функции у = х 3 на отрезке [–3;3] с шагом h = 0,5:

7. Выделить таблицу и вызвать мастер диаграмм. На первом шаге выбрать во второй вкладке Гладкие графики.
8. На втором шаге во вкладке Ряд выполнить:

• В поле Ряд выделить ряд х и нажать на кнопку “Удалить” (график изменений х нам не нужен. График функции – это график изменения значений у)
• В поле Подписи оси Х нажать на кнопку . Выделить в таблице ячейки со значениями х и нажать на кнопку . Подписи по горизонтальной оси станут такими, как у нас в таблице.

10. Пример полученного графика:
11. Оформить график.
12. Установить параметры страницы и размеры диаграмм таким образом, что бы все поместилось на одном листе альбомной ориентации.
13. Создать колонтитулы для данного листа (Вид Колонтитулы…):
14. Верхний колонтитул слева: график функции у = x 3

Пример карточки, с задачей в общем виде, выводится на экран с помощью проектора.

1. Построить график функции y=f(x) на отрезке [a;b] с шагом h=c
2. Установить параметры страницы и размеры графика таким образом, что бы все поместилось на одном листе альбомной ориентации.
3. Создать колонтитулы для данного листа (Вид Колонтитулы…):

4. Сохранить в своей папке под именем “Зачетный график”
5. Вывести документ на печать.

После выполнения задания правильность каждого варианта проверяется с помощью проектора.

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

Работа с формулами в эксель практическая работа

6. Следующий шаг. Выделим таблицу и вызовем Мастер диаграмм. На первом шаге выберем во вкладке Нестандартные Гладкие графики.
7. На втором шаге во вкладке Ряд выполним:

Функция подсчета количества значений в Excel

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

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

Эта функция одна из самых известных в работе с Эксель. В ней проверяются числа и/или текст, функции, формулы. Когда значения отвечают заданному условию, появляется запись из поля «значение_если_истина», не отвечают — «значение_если_ложь». Часто используется для распределения выражений на категории, группы.

Анализ данных и их оптимизация в Excel

1. Вычисление обратной матрицы. В ячейки B16:D18 вводим исходную матрицу. Выделить диапазон ячеек G16:I18. Нажать на кнопку fx в строке ввода и выбрать категорию МАТЕМАТИЧЕСКИЕ, функция МОБР (рис. 12)

Поиск решения в Exceel

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

Средство Поиск решения — это надстройка Excel. Для ее подключения следует выполнить команду меню Сервис > Надстройки. В появившемся диалоговом окне Надстройки нужно установить флажок опции Поиск решения.

Характерные особенности задач, для решения которых предназначено данное средство, заключаются в следующем:

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

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

Правильная формулировка ограничений — самая ответственная часть описания модели для поиска решения. Следует особенно внимательно следить за тем, чтобы задавать все объективно существующие ограничения. Неполнота описания ограничений приводит к неправильному решению.

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

Чтобы исключить использование общих более медленных методов для решения линейных задач, следует установить параметр Линейная модель в окне Параметры поиска решения.

Для пояснения принципа работы средства Поиск решения рассмотрим пример, используя данные таблицы на рис. 4.1.

Рис. 4.1 — Таблица Excel для определения количества товаров, приносящих максимальную прибыль

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

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

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

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

общее число производимых товаров за отчетный период должно составлять ровно 1000 шт.;

товар С пользуется наименьшим спросом, поэтому, как показал опыт, удается реализовать товар этого вида не более 140 шт.;

на товары вида A, B, D имеются заказы соответственно на 50, 100 и 200 шт., которые необходимо выполнить.

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

• Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
• Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
• Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
• Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
• Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

Рис 4.3 – Диалоговое отношение Добавление ограничений Excel

После того как найдем оптимальное решение, мы можем выбрать одну из следующих возможностей:

2) восстановить исходные значения в изменяемых ячейках;

4) щелкнуть на кнопке Сохранить сценарий. Сохраненный сценарий может быть использован в средстве Диспетчер сценариев.

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

10 базовых функций Excel для начинающих | Бизнес-школа LABA (ЛАБА)

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