РОССИЙСКОЙ ФЕДЕРАЦИИ»
Департамент анализа данных, принятия решений и финансовых технологий
Т.Л. Фомичева, М.Б. Хрипунова
Математические операции, вычисление значений функций, подбор параметра под заданное значение.
( Excel )
Учебно-методические рекомендации для проведения
семинара №2 «Excel как калькулятор» по дисциплине
«Компьютерный практикум»
Для бакалавров направления 38.03.01 «Экономика»
Электронное издание
Москва 2017
Математические операции, вычисление значений функций, подбор параметра под заданное значение ( Excel )
Семинарское занятие «Excel как калькулятор» - второе занятие в курсе дисциплины «Компьютерный практикум». Предполагается, что студенты владеют начальными навыками работы c Microsoft Excel .
Методический материал к занятию и самостоятельной работе содержит настоящие рекомендации, методичку для самостоятельной работы, Excel- рабочий файл. Методические рекомендации включают два основных раздела: теоретический и практический. Планируется, что при выполнении домашнего задания студенты будут использовать учебник Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк., учеб. пособие "Бизнес-аналитика средствами Excel" – 3-е изд., расш. и доп.-М.: Вузовский учебник, 2017.
Теоретический раздел
Ввод данных и формул в ячейки рабочего листа
Выражения и операции Excel
Excel является универсальным вычислителем, выполняющим операции над данными и использующим принцип программного управления. Для записи программы вычислений служат выражения.
Выражение – это указание на выполнение какого-либо действия. Выражения в Excel служат для записи формул, для записи условий фильтрации. Выражение включает хотя бы один из таких элементов, как оператор, литерал, адресную ссылку, идентификатор или функцию. Литералы, идентификаторы, адресные ссылки и функции, соединенные операторами, называются операндами.
Оператор в выражении указывает, какое действие должно быть выполнено над операндами. Различают арифметические операторы, операторы сравнения, операторы фильтрации, операторы конкатенации, операторы идентификации и адресные операторы. Перечень операторов и их назначение приведен в следующей таблице.
Таблица 1.1. Операторы Excel
| Оператор | Вып олняемое действие | Пример |
А рифметические операторы
Типы данных Excel
Тип данных определяет формат данных хранимых в памяти и способ их отображения в ячейках рабочего листа. Тип данных для активной ячейки или выделенного фрагмента устанавливается следующим образом:
• по команде ГЛАВНАЯ/ЧИСЛО и в окне: выбрать нужный тип;
• щелчком правой кнопкой мыши на активной ячейке или выделенного диапазона ячеек и из контекстного меню выбрать команду Формат ячеек и далее во вкладке Число выбрать нужный тип.
Типы данных Excel приведены в следующей таблице.
Таблица 1.2.
| Тип данных | Назначение | Пример |
| Числовой | Служит для представления чисел с фиксированной запятой | 451,12 -9267,123 |
| Денежный | Числовой формат, применяемый для отображения денежных величин | 451,12р. 9267,12р. |
| Финансовый | Применяется для выравнивания денежных величин по разделителю целой и дробной части. | |
| Дата | Применяется для отображения дат | 16.4 16.4.02 апрель 02 |
| Время | Применяется для отображения времени | 14:20 4.5.02 12:35 |
| Процентный | Значение ячеек умножается на 100 и сопровождается символом процента | 12% |
| Дробный | Применяется для отображения чисел в | 3/4 |
| виде рабочий дроби | 4/7 | |
| Экспоненциальный | Применяется для отображения чисел с плавающей запятой | 1,23Е-01, здесь 1,23 – значащая часть (мантисса) Е- основание счисления (10) -01- порядок число может быть представлено в виде 1,23*10-1 |
| Текстовый | Применяется для отображения текстовой информации. Вне зависимости от содержания обрабатываются как строки | |
| Дополнительный | Текстовый, использующий различные | 123-4567 |
| шаблоны для ввода специфических | 456500 | |
| данных (номер телефона, почтовый | ||
| индекс) | ||
| Общий | Служит для отображения текстовых и числовых значений произвольного типа |
Ввод данных
Любая ячейка таблицы может быть заполнена данными. Для ввода данных в ячейку надо активизировать эту ячейку (ячейка выделяется жирным контуром) и начать ввод. Вводимые символы сразу появляются в текущей ячейке и в строке формул.
По окончании ввода данных в текущую ячейку может быть выполнено одно из следующих действий:
ü нажата клавиша <Enter> - данные зафиксируются в текущей ячейке, и выделение переместится на одну ячейку вниз;
ü нажата кнопка с «галочкой» на строке формул - данные зафиксируются в текущей ячейке, и выделение останется в этой же ячейке;
ü нажата любая клавиша со стрелкой - данные зафиксируются в текущей ячейке, и выделение переместится в ячейку в направлении, указанном стрелкой;
ü нажата кнопка с крестиком на строке формул или нажата клавиша <Esc> -ввод данных будет отменен.
Для ввода одинаковых данных в диапазон ячеек, следует сначала выделить нужный диапазон ячеек, затем ввести необходимые данные и завершить ввод нажатием комбинации клавиш <Ctrl><Shift><Enter>.
По умолчанию по окончании ввода текстовые данные выравниваются по левому краю ячейки, числовые - по правому. Если выравнивание требуется изменить, нужно воспользоваться командой ГЛАВНАЯ/ВЫРАВНИВАНИЕ, или щелкнуть правой кнопкой мыши на активную ячейку или выделенный диапазон ячеек и из контекстного меню выбрать команду Формат ячеек и далее во вкладке Выравнивание выбрать нужный тип выравнивания.
Ввод формул
Ввод формулы обязательно должен начинаться со знака равенства (=).
В составе формул могут быть числа, функции, ссылки на адреса или имена ячеек, операторы (см. Таблицу 1.1), круглые скобки для задания приоритетности операций, логические функции, а также текст, заключенный в кавычки. Например, =В12+А2*4 или =А1&” “&В1 (результатом выполнения этой формулы будет объединение значений ячеек А1 и В1, разделенных пробелом. Допустим, в А1 содержится имя, а в В1 – фамилия. Результатом вычисления формулы будет текст, содержащий имя и фамилию).
Формулу, также как и данные можно вводить сразу в несколько ячеек. Для этого следует сначала выделить нужный диапазон ячеек, затем ввести необходимое выражение и завершить ввод нажатием комбинации клавиш <Ctrl><Shift><Enter>.
После ввода формулы в ячейке появляется вычисленный результат, а сама формула отображается в строке формул. Если необходимо (в ходе выверки таблицы) отобразить в ячейках таблицы именно формулы расчета, а не результаты, то следует задать команду ФОРМУЛЫ / ЗАВИСИМОСТИ ФОРМУЛ/ПОКАЗАТЬ ФОРМУЛЫ.
Если результат вычисления формулы или преобразования формата окажется длиннее ширины столбца, в ячейке появляются символы #######. Для получения числового изображения следует увеличить ширину столбца.
Организация ссылок
Адреса ячеек в формулы могут быть помещены с помощью указания мышью на соответствующую ячейку (диапазон ячеек). Вид адресной ссылки на ячейку, содержащую значение операнда, зависит от выбранного формата адресации.
При перемещении или копировании формулы адрес в указанной ссылке изменяется, ориентируясь на ту позицию, в которую переносится формула. Такие ссылки носят название относительных ссылок.
Для ввода в формулу значения из фиксированной ячейки (адрес которой при копировании или перемещении формулы остается неизменным) используются абсолютные ссылки. При их обозначении в написании адреса ячейки добавляется знак доллара (например, $A$20, $IA$200).
В случае изменения только одного значения адреса и фиксации другого используются смешанные ссылки (Знаком $ фиксируется только имя столбца (например, $A9) или номер строки (например, E$6)). Для ввода смешанных и абсолютных ссылок используется клавиша F4 (курсор в этом случае помещается либо внутрь ссылки, либо после нее).
Автозаполнение
Во многих задачах может потребоваться заполнить некоторый диапазон ячеек арифметической последовательностью чисел или дат. Для автоматического создания таких последовательностей можно воспользоваться одним из следующих способов:
• ввести данные в первые две ячейки ряда и выделить их. Далее протянуть маркер заполнения (маленький черный квадрат, расположенный в нижнем правом углу выделенной области) по всему ряду. После того, как мышь будет отпущена, ряд заполнится данными;
• ввести данные в первую ячейку ряда. Протянуть маркер заполнения по всему ряду, удерживая при этом нажатой клавишу <Ctrl>. Образуемая при этом последовательность чисел будет всегда иметь приращение 1;
• ввести данные в первую ячейку ряда. Выделить все ячейки, которые должны быть заполнены данными. Задать команду ГЛАВНАЯ/ РЕДАКТИРОВАНИЕ, нажать кнопку ЗАПОЛНИТЬ и выбрать параметр ПРОГРЕССИЯ. Далее задать тип заполняемого ряда (как правило, тип определяется автоматически), в поле ШАГ указать приращение.
Для создания собственного Списка автозаполнения для ввода данных можно воспользоваться одним из следующих способов:
• Задать команду ФАЙЛ/ПАРАМЕТРЫ вкладка ДОПОЛНИТЕЛЬНО раздел ОБЩИЕ далее щелкаете командную кнопку ИЗМЕНИТЬ СПИСКИ. В окне ЭЛЕМЕНТЫ СПИСКА ввести элементы этого списка, отделяя их друг от друга нажатием клавиши <Enter>. Нажать кнопку ДОБАВИТЬ.
• Ввести список в диапазон ячеек. Выделить полученный диапазон. Задать команду ФАЙЛ/ПАРАМЕТРЫ вкладка ДОПОЛНИТЕЛЬНО раздел ОБЩИЕ далее щелкаете командную кнопку ИЗМЕНИТЬ СПИСКИ. Убедиться, что в поле ИМПОРТ СПИСКА ИЗ ЯЧЕЕК указан верный диапазон. Нажать кнопку ИМПОРТ.
Для применения Списка можно использовать как всю последовательность элементов списка сразу, так и отдельные элементы. Для этого нужно установить курсор в первую ячейку заполняемого диапазона, ввести первый элемент списка, протянуть маркер заполнения по всему ряду.
Пример.
Найти предел функции
Подбор параметра
Реализация различных экономических и финансовых проектов и задач, зачастую требует решения проблемы подбора одного параметра так, чтобы другой параметр принял требуемое значение. То есть, если известен целевой показатель вычисления формулы, но не известны входные значения, позволяющие получить его, то в MS Excel используется инструмент Подбор параметра. Этот инструмент является средством решения задач анализа данных «что если», когда путем перебора одного значения достигается необходимое значение исследуемой функции (критерии оптимальности).
Итак, для того, чтобы определить значение, удовлетворяющее требуемому значению критерия оптимальности, необходимо вывести инструмент Подбор параметра в списке команд «Анализ «что-если»» в группе команд Работа с данными ленты ДАННЫЕ (рис.5).
Рис.5. Работа с данными (Анализ «что если»)
Для того, чтобы применить инструмент Подбор параметра (рис. 6) необходимо настроить соответствующие поля диалогового окна:
1. Установить в ячейке — ссылка на ячейку с исследуемой формулой;
2. Значение — планируемое значение, в виде частного экстремума;
3. Изменяя значение ячейки — ссылка на ячейку с подбираемым параметром;
4. Нажать ОК.
Рис. 6. Окно Подбор параметра
Пример 1 . Требуется определить, каким должен быть курс евро, чтобы имея в наличии 3678 рублей, купить 90 евро.
Решение (уравнение для упомянутой задачи имеет вид х * 90 = 3678, где х — искомый курс доллара, для которого в MS Excel создадим модель):
1. В ячейке А2 должно быть подобрано значение курса доллара (предварительно ячейка пустая).
2. В ячейку B2 вводим формулу: =А2 * 90, предварительный итог которой 0.
3. Запустив инструмент Подбор параметра, необходимо в поле Установить в ячейке указать ссылку на ячейку B2, в поле Значение ввести 3678, а в поле Изменяя значение ячейки указать ссылку на ячейку А2 (рис. 7) (ответ: 40,866667).
Рис. 7. Результат решения примера 1
Пример 2. Определить, под какую процентную ставку необходимо сделать вклад в банк в сумме 500000 рублей, с ежемесячным начислением процентов, чтобы за 4 года накопить на счету 1000000 рублей? Модель задачи представлена на рисунке 8.
С помощью встроенной финансовой функции (БС), позволяющей вычислять будущую стоимость, определим ее значение при условных 10% (744 677,05 р.).
Рис. 8. Расчет процентной ставки
Решение:
1. Установите курсор в ячейку B6, где расположена функция (формула) и запустите инструмент Подбор параметра.
2. В поле Установить в ячейке, должна быть ссылка на ячейку B6.
3. В поле Значение введите 1000000.
4. В поле Изменяя значение ячейки установите ссылку на ячейку В3 (рис9) (ответ: 17,45%).
Рис. 9. Подбор параметра р асчета процентной ставки
В результате получим искомое значение процентной ставки 17,45% (рис. 10).
Рис. 10. Расчет процентной ставки после подбора параметра
Итак, инструмент Подбор параметра, подбирает конкретный показатель, при котором требуемый результат достигает определенного значения.
Практический раздел
Темы заданий.
1. Математические операции в Excel с числами
2. Математические операции в Excel со значениями в ячейках
3. Формулы в Excel вручную
4. Формулы в Excel с помощью функций Excel
5. Вычисление значений математических функций в Excel
6. Вычисление сумм последовательностей
7. Вычисление пределов последовательностей
8. Подбор параметра под заданное значение в Excel
9. Задачи для самостоятельного решения
Указания студентам
· Выполните с 1 по 5 задания в рабочем файле, с точностью до двух знаков после запятой.
· Значения запишите в числовом и экспоненциальном форматах.
· Занесите соответствующие результаты вычислений в сводную таблицу ответов, находящуюся в конце рабочего файла.
· Номера заданий с 6 по 10 рекомендуются в качестве домашнего задания.
Задание1. Математические операции в Excel
Произвести математические операции с помощью Excel .
1. 135+243
2. 135*243
3. 135-243
4. 135/243
5. 
6. 
7. 
8. 
9. 
10. -35/28,5
Задание2.
Произвести математические операции с помощью Excel , по заданным значениям 
1. 
2. 
3. 
4. 
5. 
6. 
7. 
8. 
9. 
10. 
Задание 3. Формулы в Excel
1. Задать в Excel формулу вручную и найти значение выражения при 
2. Задать в Excel формулу вручную и найти значение выражения при 
3. Задать в Excel формулу вручную и найти значение выражения при 
4. Задать в Excel формулу вручную и найти значение выражения при 
5. Задать в Excel формулу вручную и найти значение выражения при x=0.0002543
6. Задать в Excel формулу вручную и найти значение выражения при x=0.0002543
7. Задать в Excel формулу вручную и найти значение выражения при x=0.0002543
8. Задать в Excel формулу вручную и найти значение выражения при x=678
9. Задать в Excel формулу вручную и найти значение выражения при x=-15,25
10. Задать в Excel формулу вручную и найти значение выражения при x=0.00025
Задание 4.
Используя функции Excel, задать формулы для вычисления следующих выражений и вычислить их
1. При x=180 рад.
2. При x=32
3. При х=0,990077
4. При х=0,990077
5. При x=0.0002543
6. При x=0
7. При x=2853,006
8. При x=2853,006
9. При x=2853,006
10. При x=2853,006
Если в результате компьютер выдает «ошибку», пояснить ее происхождение и указать как нужно изменить значение x.
Задание 5. Вычисление значений функций в Excel
Вычислите значений функции y(x)=k*f(x) для всех значений переменной х на отрезке [a;b] с шагом c при заданном k, где f(x) из задания 5
| I ( номер варианта) | k | a | b | с |
| 1 | 2 | 1 | 2 | 0,1 |
| 2 | 4 | 2 | 4 | 0,2 |
| 3 | 5 | 3 | 4 | 0,1 |
| 4 | 3 | 4 | 6 | 0,2 |
| 5 | 6 | 5 | 6 | 0,1 |
| 6 | 8 | 6 | 8 | 0,2 |
| 7 | 2 | 7 | 8 | 0,1 |
| 8 | 3 | 8 | 10 | 0,2 |
| 9 | 1 | 9 | 10 | 0,1 |
| 10 | 7 | 10 | 12 | 0,2 |
Задание 6.
1. Найти сумму двадцати первых членов числовой последовательности
2. Найти сумму первых 9 членов числовой последовательности
3. Найти сумму 30 первых членов числовой последовательности
4. Найти сумму с 10 по 15 членов числовой последовательности
5. Найти сумму 30 первых членов числовой последовательности
6. Найти сумму 30 первых членов числовой последовательности
7. Найти сумму 30 первых членов числовой последовательности
8. Найти сумму 30 первых членов числовой последовательности
9. Найти сумму 30 первых членов числовой последовательности
10. Найти сумму 30 первых членов числовой последовательности
Задание 7.
1. Найти приближенное значение предела числовой последовательности
2. Найти приближенное значение предела числовой последовательности
3. Найти приближенное значение предела числовой последовательности
4. Найти приближенное значение предела числовой последовательности
5. Найти приближенное значение предела числовой последовательности
6. Найти приближенное значение предела числовой последовательности
7. Найти приближенное значение предела числовой последовательности
8. Найти приближенное значение предела числовой последовательности
9. Найти приближенное значение предела числовой последовательности
10. Найти приближенное значение предела числовой последовательности
Пояснение заданию 7. Предположим, что в ячейке А30 расположено значение n, которое стремится к бесконечности. В ячейке В30 вводим формулу для вычисления n–го члена. Далее в ячейке А30 запишем достаточно большое число, например, 10^100. В ячейке В30 появиться примерное значение предела.
Задание 8. Подбор параметра под заданное значение в Excel
1. Известно, что длина окружности первого круга составляет 100, а площадь второго круга составляет 1000. С помощью инструмента Подбор параметра определить во сколько раз радиус первого круга отличается от радиуса второго. Ответ дать с двумя знаками после запятой.
2. Дана формула линейной функции: y = b + ax. Известно, что а=10, b =20. Протабулировать функцию на интервале значений x от 1 до 10 с шагом 1. С помощью инструмента Подбор параметра, изменяя значение b определить, чему равен y в точке x=10, если в точке x =6 значение y =100.
3. Площадь первого круга составляет 1500, площадь второго круга составляет 100. С помощью инструмента Подбор параметра определить во сколько раз радиус первого круга отличается от радиуса второго.
4. Дана формула линейной функции: y =2 b - ax . Известно, что а=25, b =10. Протабулировать функцию на интервале значений x от -3 до 5 с шагом 0,5. С помощью инструмента Подбор параметра, изменяя значение b определить, чему равен y в точке x =5, если в точке x =1 значение y =-10.
5. Найти решение уравнения 2,84x^2-14,7=0. Ответ дать с двумя знаками после запятой.
6. Найти решение уравнения x^2-11,7x+3=0. Ответ дать с двумя знаками после запятой.
7. Дана формула линейной функции: y=a – bx+3 . Известно, что а=10, b=20. Протабулировать функцию на интервале значений x от -2 до +2 с шагом 0,2. С помощью инструмента Подбор параметра, изменяя значение b определить, чему равен y в точке x=2, если в точке x=0,2 значение y=15.
8. Найти решение уравнения x^2-8,2x+6=0. Ответ дать с двумя знаками после запятой.
9. Известно, что площадь первого прямоугольника (a 1* b 1) равна 135, а площадь второго прямоугольника (a 2* b 2) равна 195. С помощью инструмента Подбор параметра определить во сколько раз отличается сторона а1 от стороны а2, если стороны b 1 и b 2 равны по 3,75. Ответ дать с двумя знаками после запятой.
10. Известно, что площадь первого прямоугольного треугольника (a 1* b 1/2) равна 156, а площадь второго прямоугольника (a 2* b 2/2) равна 185. С помощью инструмента Подбор параметра определить во сколько раз отличается сторона а1 от стороны а2, если стороны b 1 и b 2 равны по 4,15. Ответ дать с двумя знаками после запятой.
11. Найти корни уравнения cos(x)+sin(x)=0 на отрезке [-2,5;2,5]. В ответе записать большее значение. Построить график.
12. 
Найти корни уравнения на отрезке [-1,5;2,5]. Построить график
9. Задачи для самостоятельного решения
1. С помощью финансовых функций определить, какая сумма будет накоплена при следующих условиях: начальное значение вклада (Пс) – 8000 долларов, срок вклада (Кпер) – 18 месяцев, годовая процентная ставка (Ставка) – 11%. Дополнительные вложения и изъятия не производятся. Проценты начисляются ежемесячно. Ответ дать с двумя знаками после запятой
2. С помощью финансовых функций определить, каким должно быть начальное значение вклада при следующих условиях: срок вклада (Кпер) – 12 месяцев, будущее значение вклада (Бс) – 9600 долларов, годовая процентная ставка (Ставка) – 13%. Дополнительные вложения и изъятия не производятся. Проценты начисляются ежемесячно. Ответ дать с двумя знаками после запятой.
3. С помощью финансовых функций определить, на какой срок нужно вложить средства при следующих условиях: начальное значение вклада (Пс) – 8000 долларов, будущее значение вклада (Бс) – 10000 долларов, годовая процентная ставка (Ставка) – 11%. В конце каждого периода (тип 0) производится доплата (Плт) 100 долларов. Проценты начисляются ежемесячно. Ответ дать с двумя знаками после запятой.
4. С помощью финансовых функций определить, какую сумму нужно ежемесячно докладывать при следующих условиях: начальное значение вклада (Пс) – 90000 рублей, будущее значение вклада (Бс) – 160000 рублей, годовая процентная ставка (Ставка) – 9%, срок вклада (Кпер) – 15 месяцев. Доплата производится в конце каждого периода (тип 0). Проценты начисляются ежемесячно.
5. Определить, какой должна быть годовая процентная ставка при следующих условиях: начальное значение вклада (Пс) – 100000 рублей, срок вклада (Кпер) – 5 лет, будущее значение вклада (Бс) – 180000 рублей. В конце каждого периода (тип 0) производится снятие средств (Плт) 500 рублей. Проценты начисляются ежемесячно. Ответ записать в процентном формате с двумя десятичными знаками после запятой (например, 7,38%).
6. Определить, какая сумма будет накоплена при следующих условиях: начальное значение вклада (Пс) – 9000 долларов, срок вклада (Кпер) - 16 месяцев, годовая процентная ставка (Ставка) – 9%. Дополнительные вложения и изъятия не производятся. Проценты начисляются ежемесячно. Ответ дать с двумя знаками после запятой.
РОССИЙСКОЙ ФЕДЕРАЦИИ»
Департамент анализа данных, принятия решений и финансовых технологий
Дата: 2018-12-28, просмотров: 965.
