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

4.

3.

2.

1.

Защита данных

Работа со списками

Анализ данных

Итоговые таблицы и консолидация данных

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

Пример использование абсолютных ссылок

MS Excel для решении математических и экономических задач.

Лекция_9

Фамилии	Зарплата	Премия	Пенсионные отчисл.	Налог
		0,25	0,1	0,12
Иванов		=$B3*C$2	=$B3*D$2	=$B3*E$2
Петров		=$B4*C$2	=$B4*D$2	=$B4*E$2
Сергеев		=$B5*C$2	=$B5*D$2	=$B5*E$2
Тихонов		=$B6*C$2	=$B6*D$2	=$B6*E$2

(Посмотреть, есть ли в Лекции_6 про отображение формул в таблице)

Если созданная таблица не может полностью разместиться на экране, необходимо зафиксировать некоторые области. Для выполнения подобной операции следует установить курсор в ячейку, выше и левее которой строки и столбцы должны быть зафиксированы и выбрать команду Окно \ Закрепить области. Для отмены фиксации – Окно \ Снять закрепление областей. Для разбивки документа на страницы вручную выбирается команда Вставка \ Разрыв страницы.

Для вставки встроенной функции в Еxcel используется Мастер функций, который вызывается кнопкой.

Рассмотрим группу статистических функций:

· СЧИТАТЬПУСТОТЫ(ДИАПАЗОН) – считает количество пустых ячеек в диапазоне, н-ер: С1:С5

· СРЗНАЧ(ДИАПАЗОН) – возвращает среднее значение для чисел указанного диапазона

· МАКС(ДИАПАЗОН), МИН(ДИАПАЗОН) – возвращает максимальное и минимальное значение из диапазона соответственно. Диапазон может содержать и несвязную группу ячеек, н-ер: (С2:С6;Е4:Е8)

· РАНГ(АДРЕС ЯЧЕЙКИ; ДИАПАЗОН) – возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки в указанном диапазоне

· Для прогнозирования используются три функции ПРЕДСКАЗ(Х;Известные значения Y; Известные значения Х) – вычисляет значения для точки Х по существующим значениям, на основании линейной регрессии; РОСТ(Известные значения Y;Известные значения Х;Новые значения Х;КОНСТ) – расчитывает прогнозируемый экспотенциальный рост на основании имеющихся данных; ТЕНДЕНЦИЯ(Известные значения Y;Известные значения Х;Новое значение Х;КОНСТ) – выполняется линейная аппроксимация

Группа математические:

· ABS – модуль числа

· SIN, COS, LN, LOG10 – синус, косинус, натуральный логарифм, десятичный логарифм

Следующие три функции служат для решения систем уравнений типа:

a₁₁x₁+a₁₂*x₂+…+a_1n*x_n=b_n

a₂₁x₁+a₂₂*x₂+…+a_2n*x_n=b_n

…

a_n1x₁+a_n2*x₂+…+a_nn*x_n=b_n

· МОПРЕД – определитель матрицы, для нахождения поместите курсор в свободную ячейку и вызовите встроенную функцию, результат появится в выделенной ячейке

· МОБР – нахождение обратной матрицы, для нахождения выделите диапазон такого же размера, как и исходная матрица, вызовите функцию, далее нажмите F2, затем Shift+Ctrl+Enter

· МУМНОЖ – произведение матриц, выделяем диапазон ячеек размерности исходных матриц, вызвать встроенную функцию, далее нажмите F2, затем Shift+Ctrl+Enter

Из группы функций даты и времени: СЕГОДНЯ() – выдает текущую дату.

Из группы логические – функция ЕСЛИ. В появившемся окне в первой строке задаем условие, н-ер А1>0, во второй строке значение, если ИСТИНА, в третьей строке значение если ЛОЖЬ,

н-ер: если стаж работы < 5 лет,

то б/л=50%*оклад,

иначеб/л=100%*оклад

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

Из группы ссылки и массивы – функция ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ; ДИАПАЗОН) – выбирает искомое значение из диапазона, который представляет собой блок, состоящий из двух колонок. Функция ищет искомое значение в первой колонке и выбирает соответствующее значение из второй колонки. Если искомого значение нет в первой колонке, то функция выбирает ближайшее к искомому меньшее значение.

Группа финансовые:

· КПЕР(СТАВКА;ПЛАТЕЖ;НЗ;БЗ;ТИП) - для определения срока платежа

· НОРМА(КПЕР;ВЫПЛАТА;НЗ;БЗ;ТИП;НАЧПРИБЛ) – определяет значение процентной ставки за один расчетный период

· ПЗ(СТАВКА;КПЕР;ПЛАТА;НЗ;ТИП) – для расчета текущей стоимости как единой суммы вклада, так и будущих финансовых периодических платежей

· БЗ(СТАВКА;КПЕР;ПЛАТА;НЗ;ТИП) – для расчета будущей стоимости периодических постоянных платежей и единной суммы вклада или займа на основе постоянной процентной ставки

· ППЛАТ(НОРМА;КПЕР;НЗ;БС;ТИП) – может быть использована для анализа всевозможных ссуд.

СТАВКА – процентная ставка за период

ПЛАТЕЖ – это выплата, производимая в каждый период

НЗ – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента

БЗ – это будущая стоимость или баланс наличности, который должен быть достигнут после последней выплаты

ТИП – это число 0 и 1, обозначающие, когда должна производиться выплата – в начале периода или в конце периода.

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

отсортировать таблицу по столбцу, содержащему данные, по которым надо подвести итоги

· установить курсор в любую ячейку данного столбца

· задать команду Данные \ Итоги

· в поле При каждом изменении в указать столбец с группами, по которым надо подводить итоги

· в поле Использовать функцию указать СУММА

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

Второи способ подведения итогов – консолидация данных. Это итоговые таблицы, содержащие суммарные данные за по нескольким таблицам, расположенным на разных листах. Например, можно получить итоговую таблицу за год, имея листы с данными за 12 месяцев. Консолидация бывает двух типов: по расположению – это когда исходные таблицы на разных листах имеют совершенно одинаковый вид и консолидация по категориям – когда таблицы содержат одинаковые имена-категории и выполняются выбором команды: Данные \ Консолидация, предварительно выделив диапазон на итоговом листе для суммарных значений в первом случае и выделив левую верхнюю ячейку во втором случае. Для консолидации по категориям в окне Консолидация в поле функция указать СУММА, в окне Использовать в качестве имен указать значения левого столбца – если ваши имена расположены в столбце или верхней строки в противном случае. В поле ссылка выделять диапазоны поочередно в каждой таблице вместе с именами-категориями!

Первый тип задач – подбор параметра. Математическая суть задачи состоит в решении уравнения f(x)=a, где х – искомый параметр, а а – требуемый результат функции.

Необходимо выполнить следующие действия:

· выделить ячейку, содержащую формулу, для которой нужно найти определенное решение

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

· в меню сервис выбрать команду Подбор параметра

· в поле Значение ввести значение, которое нужно получить, т.е. а

· в поле Изменяя ячейку указать на ячейку, содержащую х

· нажать ОК.

Второй тип задач - это таблицы подстановки данных. Это задача обратна предыдущей. Анализ проводится для функции с двумя переменными или для нескольких функций с одной переменной и выполняется следующей последовательностью операций:

· в левый столбец блока, начиная со второй ячейки вводятся значения переменной х

· в любую свободную ячейку вводится какое-нибудь значение для переменной х

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

· выделить диапазон со столбцом переменных и формулами и выбрать команду Данные \ Таблица подстановки

· в поле подставлять значения по строкам ввести адрес свободной ячейки, содержащей х

В случае функции от двух переменных таблица выглядит следующим образом:

	формула	Y1	Y2
Х	Х1
Y	Х2
Другие параметры	Х3

(Пример с листа практического задания).

Используется для решения задач линейного программирования. ЛП – это метод математического моделирования, разработанный для оптимизации использования ограниченних ресурсов и относится к разделу математики "Исследование операций". Задача ЛП, как и любая другая задача ИО, включает три основных элемента:

1. Переменные, которые следует определить

2. Целевая функция, подлежащая оптимизации

3. Ограничения, которым должны удовлетворять переменные.

Допустим необходимо оптимизировать некоторую функцию, которая зависит от нескольких переменных и имеет некоторые ограничения. Для решения задачи необходимо ввести в некоторую ячейку формулу, определяющую целевую функцию, записать в отдельных ячейках все условия задачи в числах и формулах, выделить ячейки для переменных и ввести туда какие-либо значения, удовлетворяющие ограничениям (допустимое решение) и вызвать Поиск решения (пункт Горизонтального меню Сервис), где указать Целевую ячейку - ячейку, содержащую формулу, зависящую от переменных, Ограничения – условия, накладываемые на переменные и Изменяемые ячейки – ячейки, содержащие значения переменных.

(Задача о столах и стульях)

Дата добавления: 2014-01-07