Тема 6.7. Решение системы уравнений и оптимизация функций при помощи средства Поиск решения

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

При решении одного уравнения (В1) с одной переменной (А1) вслед за вызовом этого средства ( Сервис/Поиск решения ) следует в окне Установить целевую ячейку раскрывшегося диалогового окна (рис. 6.10) указать адрес левой части уравнения (В1), щёлкнуть на переключателе Значение и записать в окно нуль, т.к. находится корень уравнения. Затем в окне Изменяя ячейки указать адрес ячейки с переменной (А1) и щёлкнуть на кнопке Выполнить.

Рис. 6.10. Диалоговое окно средства Поиск решения.

Результат работы средства Поиск решения записывается в ячейку А1, а в диалоговом окне можно выбрать какой-либо вариант продолжения.

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

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

Решение системы уравнений ведётся стандартным способом: система превращается в одно уравнение с помощью сложения квадратов левых частей всех уравнений системы. Это результирующее уравнение записывается в ячейку B1, а переменные для случая двух уравнений задаются в ячейках А1, A2, которые указываются в окне Изменяя ячейки. Средство после щелчка на кнопке Выполнить находит первое решение системы и записывает его в ячейки А1 и A2.

Для нахождения второго решения системы следует изменить исходные данные в ячейках А1 и A2 и запустить Поиск решения ещё раз.

Глава 6.8. Создание табличных баз данных. Проверка, фильтрация, сортировка данных. Подведение итогов

С помощью средств для работы с базами данных в Excel облегчается работа как с простыми списками (план на день), так и со сложными, содержащими несколько столбцов с данными.

Информация в базе данных содержит последовательность записей, каждая из которых содержит одинаковый для всех записей набор полей, например информацию о клиентах. Записи нумеруются, поля имеют заголовки, описывающие их содержание, например: первое поле – имя клиента, следующее поле – место его работы, затем – телефон и т.д. Примеры, иллюстрирующие изложенное можно найти в практикуме.

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

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

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

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

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

Команда Данные/Фильтр позволяет выделить из большой базы данных некоторое подмножество, меньшее исходной базы, по заданным условиям отбора. Наиболее простым способом фильтрации является вызов автофильтра (Данные/Фильтр/Автофильтр). После этого в каждой ячейке с подписями в верхней части таблицы (перечень полей) появляется разворачивающийся список (после нажатия на стрелку вниз), из которого можно выбрать те данные, которые нужно выводить на экран. По умолчанию вариант (Все) выводит на экран все записи. Вариант (Первые 10…) позволяет отобрать наименьшие или наибольшие числовые данные. Вариант (Условие…) выводит на экран окно диалога Пользовательский автофильтр в котором можно задавать условия и комбинировать их с помощью логических операторов И и ИЛИ.

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

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

Команда Данные/Сортировка (рис. 6.11) даёт возможность проводить более сложную сортировку: с помощью раскрывающегося списка Сортировать по сортировка ведётся сначала по одному полю (первичная сортировка), затем по спискам Затем по (вторичная) и В последнюю очередь (третичная) по двум другим полям в соответствии с выбранным порядком сортировки для каждого поля. Такое построение сортировки позволяет вводить дополнительные критерии, если после первичной сортировки значения в сортируемом поле совпадают. Имеется также возможность менять параметр сортировки по первому ключу с обычной на дни недели или месяцы (кнопка Параметры).

Рис. 6.11. Диалоговое окно Сортировка данных.

В процессе сортировки порядок следования записей базы данных меняется. Если требуется возвращаться к исходному порядку сортировки, то следует вставить в базу данных столбец с нумерацией записей. Эта нумерация может отражать первоначальное положение записи или дату её создания. Этот столбец удобнее всего сделать первым. При проведении сортировки его следует включать в диапазон для сортировки. Когда понадобится вернуться к исходному расположению, следует указать название этого столбца в списке Сортировать по в окне диалога Сортировка диапазона и выбрать По возрастанию.

В большой по объёму базе данных достаточно сложно найти конкретную запись. Диалоговое окно (рис. 6.12), открываемое командой Данные/Форма, позволяет легко находить требуемые записи, в том числе по указываемым критериям (кнопка Критерии).

Рис. 6.12. Диалоговое окно Форма.

Подведение итогов для табличной базы данных (кроме обычного суммирования по столбцам) выполняется с помощью сводной таблице, рассматриваемой в главе 6.10 (Построение сводных таблиц и диаграмм по ним).