Одним из способов обработки и анализа данных является подведение различных итогов. С помощью команды «Данные\Итоги» можно вычислить итоговые значения и вставить их в список, осуществив обработку данных одним из следующих способов:

• Выбрать одну или несколько групп для автоматического подведения итогов по этим группам;
• Выбрать операцию для подведения итогов;
• Выбрать данные, по которым нужно подвести итоги.

Кроме подведения итогов по одному столбцу, операция автоматического подведения итогов позволяет:

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

При вставке строк итогов Exel автоматически помещает строку общих итогов в конец списка данных.

После выполнения команды «Данные\Итоги» появляется диалоговое окно «Промежуточные итоги» в котором надо установить следующие значения.

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

Из раскрывающегося списка «Операция» выбирается функция для вычисления итогов.

Вычисленные итоги располагаются под столбцом, который выделяется в поле ввода «Добавить итоги по». Можно выбрать более одного столбца.

Если установить флажок «Заменить текущие итоги», то все итоги заменяются новыми.

Если установлен флажок «Итоги под данными», то строки итогов и общих итогов помещаются под соответствующими данными.

Надстройки

Надстройки содержат следующие инструменты для выполнения различных операций:

• Мастер суммирования (создаёт формулы для суммирования выбранных данных таблицы);
• Пакет анализа (содержит функции и интерфейсы для анализа научных и финансовых данных);
• Поиск решения (инструмент для поиска решений уравнений и задач оптимизации).

Эти надстройки вызываются через меню «Сервис\Мастер\Частичная сумма», «Сервис\Анализ данных», «Сервис\Поиск решения».

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

Решение уравнений

Пусть имеется уравнение и надо найти хотя бы один корень с абсолютной погрешностью . Если на отрезке [a,b] выполняется условие , и функция на этом отрезке непрерывна, то отрезок [a,b] называется отрезком локализации корня. Локализовать корень – найти отрезок локализации корня.

Локализовать корень можно, например, табличным методом. Выбирается произвольный отрезок и на нём вычисляются значения функции с каким-либо шагом (как при построении графика). По полученным результатам выбираются два значения аргумента следующие подряд, такие, что для одного значения аргумента функция положительна, а для другого отрицательна. Эти два значения и являются концами отрезка локализации.

Если [a,b] отрезок локализации, то корень находится на этом отрезке и за его приблизительное значение можно принять середину этого отрезка, т.е. корень=(а+b)/2.

Очевидно, чем меньше длина отрезка, тем точнее получается приблизительное значение корня. Есть несколько способов уменьшения длины отрезка локализации. Мы рассмотрим самый простой, который называется метод деления отрезка пополам.

Пусть с=(а+b)/2. Если , то корень находится на отрезке [a,с], который и принимается за новый отрезок локализации. Если , то корень находится на отрезке [с,b]. В результате этих проверок мы получаем новый отрезок локализации, который в два раза меньше чем исходный. Применяя эту процедуру многократно, можно получить значение корня с любой степенью точности, т.к. погрешность определения корня не превышает половины длины отрезка локализации.

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

Затем, вносятся следующие формулы:

В ячейку А3 - =ЕСЛИ(D2<=0;А2;С2)

В ячейку В3 - =ЕСЛИ(D2<=0;С2;В2)

В ячейку С3 - =(А3+В3)/2

В ячейку D3 -

В ячейку Е3 –

=ЕСЛИ(ABS(В3-А3)>$F$2;”продолжаем”;”приехали”).

После этого выделяются ячейки А3…Е3 и автозаполнением протаскиваются вниз до появления в столбце Е сообщения «приехали».

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

Для этого надо в одну ячейку, например, в А2 внести значение аргумента, принадлежащее отрезку локализации, а в другую ячейку, например, В2 внести формулу для вычисления функции по значению аргумента. Сделать активной ячейку, содержащую формулу и выполнить команду «Сервис\Подбор параметра». Появится диалоговое окно, в котором надо заполнить три поля. Одно поле «Установить в ячейке» уже должно содержать адрес ячейки с формулой. В поле «Значение» необходимо поставить 0, т.к. мы ищем корень. В поле «Изменяя значение ячейки» – необходимо указать адрес ячейки, содержащей значение аргумента (в нашем случае адрес А2). После этого щелкнуть по кнопке ОК. После этого в ячейке с аргументом будет находится значение корня.

Точность нахождения корня можно регулировать в меню «Сервис\Параметры\Вычисления» с помощью параметров «Предельное число итераций» и «Относительная погрешность»

Контрольные вопросы

1. Как осуществляется подведение итогов в списке?

2. Как задать вычисление промежуточных итогов?

3. Какие функции можно использовать для вычисления итогов?

4. Что такое надстройки?

5. Что можно сделать с помощью надстройки «Пакет анализа»?

6. Что можно сделать с помощью надстройки «Поиск решения»?

7. Что такое отрезок локализации корня?

8. Что значит локализовать корень?

9. В чём заключается идея решения уравнений методом деления отрезка пополам?

10. Как можно оценить погрешность вычисления корня методом деления отрезка пополам?

11. Как с помощью инструмента «Подбор параметра» найти значение корня?