Создание таблицы подстановки данных

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

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

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

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

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

• Ввести список значений, которые следует подставлять в ячейку ввода, в отдельный столбец или в отдельную строку.

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

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

• Выбрать команду Таблица подстановки в меню Данные. Появится окно Таблица подстановки.

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

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

• Щелкнуть кнопку ОК.

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

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

1-ый способ:

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

• Выбрать команду Очистить в меню Правка. Появится подменю.

• Выбрать команду Содержимое.

2-ой способ:

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

• Нажать на клавиатуре клавишу Delete.

Создание таблицы подстановки данных с двумя переменными

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

• Ввести в ячейку формулу, которая ссылается на две ячейки ввода.

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

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

• Выбрать команду Таблица подстановки в меню Данные. Появится окно Таблица подстановки.

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

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

• Щелкнуть кнопку ОК.

Обработка данных

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

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

Формы базы данных

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

Для работы с формой базы данных необходимо вызвать окно формы. Для этого нужно:

• Выделить одну ячейку списка.

• Выбрать в меню Данные команду Форма. Появится диалоговое окно, имеющее заголовок, совпадающий с именем текущего рабочего листа/

Для просмотра базы данных при помощи формы можно использовать линейку прокрутки или кнопки:

Далее — для перехода к следующей записи базы данных;

Назад — для перехода к предыдущей записи.

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

Фильтрация данных

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

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

Для фильтрации списка при помощи автофильтра нужно:

• Выделить одну из ячеек списка.

• Выбрать в меню Данные команду Фильтр. Появится подменю/

В подменю выбрать пункт Автофильтр. Рядом с этим пунктом появится

галочка.

Рядом с каждой меткой столбца будет помещена кнопка раскрывающегося списка/

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

• Выбрать один из элементов:

(Все) — для отображения всех элементов данного столбца; (Пустые) — для вывода отфильтрованного списка, содержащего

в данном столбце только пустые ячейки;

(Первые 10...)— для вывода ячеек с любым непустым значением. Одно из значений данного поля - для вывода списка, данный столбец которого содержит только выбранное значение; (Условие...) — для задания пользовательского критерия фильтрации. В последнем случае появится диалоговое окно Пользовательский автофильтр.

• Если выбран элемент Условие, выполнить в диалоговом окне Пользовательский автофильтр следующие действия:

— выбрать в левом верхнем раскрывающемся списке оператор сравнения для задания критерия фильтрации;

— ввести с клавиатуры или выбрать в правом верхнем раскрывающемся списке значение, которое нужно использовать вместе с оператором сравнения (можно использовать символы шаблона: ? - любой символ, * -любое количество произвольных символов);

— для определения двух критериев выбрать один из флажков опции:

И — для объединения двух критериев в фильтре;

ИЛИ — для использования двух различных критериев;

— задать второй критерий фильтрации в нижних раскрывающихся списках, определив второй оператор сравнения и значение, которое нужно использовать вместе с этим оператором;

— щелкнуть кнопку ОК.

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

• Задать при необходимости критерии фильтрации по другим столбцам.