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

С помощью средств анализа «что если» в Microsoft Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения всех возможных результатов.

Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.

Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

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

Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0).

Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

Рассмотрим пример использования функции ПЛТ в Exceel.

Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых.

Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)

Нажав на клавишу Enter , мы получаем величину ежемесячных выплат по ссуде, которая составит -1350 руб. Рис.1.2

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

Рис. 1.3 — Пример расчета Excel, в котором исходные данные в отдельные ячейки

При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.

Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.

[expert_bq id=»1570″]В ячейке А11 введите текст Статистические данные выделите ячейки A11 B11 и щелкните на панели инструментов кнопку Объединить и поместить в центре. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Эта формула будет вычислять всегда правильное количество полных лет человека, т.к. для вычисления используется функция СЕГОДНЯ, которая в каждый конкретный момент времени использует текущую дату. (Таблица была составлена 01.10.2015, при использовании этого примера позже 5.12.2015 будут другие данные в столбце С)

Эксель практические работы – Практические работы в программе EXCEL — Таловская средняя школа

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

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

Встроенные функции Excel — лабораторная работа

2. Научиться использовать встроенные функции для решения конкретных задач.

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

Данные — Сортировка — Сортировать по № группы, затем по Фамилия, в последнюю очередь по Имя — ОК

3. Создали поле Возраст (после Даты рождения) — Вставка — Столбец. Считаем возраст студентов:

Полученный результат представляем в формате Год — Формат ячейки — выбираем нужный формат (ГГ) — ОК.

4. Определяем самого молодого студента с помощью мастера функций: =МИН(E3:E11)

5. Добавляем к списку с данными о студентах столбец «Стипендия» — Вставка — Столбец.

6. Назначаем дифференцированную стипендию: если средний балл студента равен 5, повышенная стипендия, (50% от 600 руб.), средний балл от 4 до 5 и все экзамены сданы без троек — стипендия назначается в размере 600 руб., остальным студентам стипендия не назначается:

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

а. Задаем критерий: копируем заголовки таблицы Ср. балл и № группы, в ячейке под ср. баллом условие >4.5. Выбираю функцию БСЧЕТ, задаем базу данных, поле, критерий — ОК.

b. Задаем критерий: копируем заголовки таблицы № группы, в ячейке под № группы условие — 5433. Выбираем функцию ДРСРЗНАЧ, задаем базу данных, поле, критерий — ОК.

с. Задаем критерий: копируем заголовки таблицы № группы, Математика, История, Информатика; под предметами вводим оценки — 4. Выбираем функцию БСЧЕТ, задаем базу данных, поле, критерий — ОК.

d. Задаем критерий: копируем заголовки таблицы Математика, История, Информатика и № группы, в ячейках под Математика, История, Информатика условие 5, а под № группы — 5433. Выбираем функцию БСЧЕТ, задаем базу данных, поле, критерий — ОК.

е. Задаем критерий: копируем заголовки таблицы Стипендия и Средний балл, Количество отличников. Выбираем функцию БДСУММ, задаем базу данных, поле, критерий — ОК.

f. Задаем критерий: копируем заголовки таблицы Дата рождения два раза. Под ними пишем интервал от 01.01.1987 до 31.12.1987. В ячейке

Количество студентов вводим функцию БСЧЕТ, задаем базу данных, поле, критерий — ОК.

9. Выполняем задания, используя форму данных:

а. Чтобы просмотреть данные о студентах, фамилия которых начинается с буквы А:

Меню — Данные — Форма — Критерии — вводим в ячейку Фамилия — А* — Далее — просматриваем данные.

b. Чтобы просмотреть данные о студентах, получающих стипендию в размере 600 руб.:

Данные — Форма — Критерии — вводим в ячейку Стипендия — 600 — Далее — просматриваем данные.

c. Чтобы просмотреть данные о студентах, имеющих средний балл >4:

Данные — Форма — Критерии — вводим в ячейку Ср. балл условие — >4 — Далее — просматриваем данные.

10.Выполняем задания, используя фильтрацию данных:

а. Чтобы вывести на экран о студентах, получающих повышенную стипендию, выполняю следующие операции:

Задаем критерий — копируем заголовки столбцов Стипендия и №группы, в ячейке под стипендией вводим — 900 — Меню — Данные — Фильтр — Расширенный фильтр — задаем диапазон условий — ОК.

[expert_bq id=»1570″]Как и любая другая электронная таблица, MS Excel предназначен прежде всего для автоматизации расчётов, которые обычно производят на листке бумаги или с помощью калькулятора. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Функции в Excel в значительной степени облегчают проведение расчётов и взаимодействие с электронными таблицами. Наиболее часто применяется функция суммирования значений ячеек. Напомним, что она имеет название СУММ, а в качестве аргументов служат диапазоны суммируемых чисел.

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

Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.

#2. СЧЁТ

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

Следующая формула возвращает количество ячеек в диапазоне A1:E4, которые содержат числа.

Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.

=СЧЁТЗ(значение1;[значение2];…) — считает количество непустых значений в перечне аргументов.

=СЧИТАТЬПУСТОТЫ(диапазон) — считает количество пустых значений в указанном диапазоне.

[expert_bq id=»1570″]Предположим, что в таблице выше нам необходимо каждому товару присвоить категорию по цене высокая стоимость и низкая стоимость. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] искомое_значение — значение, которое необходимо найти в столбце с данными. Аргументы бывают числовыми и текстовыми. Искомое значение должно находиться в крайнем левом столбце диапазона ячеек указанной таблицы.

#9. СЖПРОБЕЛЫ

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