 |
Таблицы подстановки данных
|
|
|
|
Пусть имеется формула, которая зависит от некоторых переменных. Задача состоит в определении результатов формулы при различных значениях этих переменных. Математическая сущность задачи состоит в табулировании функции. Эта задача является обратной к задаче подбора параметра.
Анализ выполняется при помощи таблицы подстановки данных.
Таблица подстановки данных представляет собой блок ячеек, в котором выводятся результаты подстановки различных значений переменных в одну или несколько формул.
Например, рассчитать значения функции:
на отрезке [1;5] с шагом 0,2.
Анализ формулы начинается с подготовки таблицы подстановки:
1. В столбцы А1:А21 ввести значения отрезка [1;5] с шагом 0,2-значения варьируемой переменной.
2. В столбец В1 ввести формулу: =4*А$1+5. Нажать ввод. Выделить диапазон А1:В5. В меню Данны е выбрать команду Таблица подстановки.
3. В появившемся диалоговом окне выбрать По строкам и щелкнуть по ячейке А1. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями для интервала х<2.
4. Аналогично в ячейку В6 ввести формулу: -2*А$6-3*А$6. выделить теперь диапазон А6:В16. В меню Данные выбрать команду Таблица подстановки.
5. В появившемся диалоговом окне выбрать По строкам и щелкнуть по ячейке А6. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями для интервала 
.
6. Теперь в ячейку В17 ввести формулу: -А$17*A$17-6. выделить теперь диапазон А17:В21. В меню Данны е выбрать команду Таблица подстановки.
7. В появившемся диалоговом окне выбрать По строкам и щелкнуть по ячейке А17. Щелкнуть по кнопке ОК. Теперь таблица будет заполнена значениями для интервала x>4.
Работа со списками (базами данных)
Список – определенным образом сформированный на рабочем листе EXCEL массив со столбцами и строками. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов. Каждая запись должна содержать полное описание конкретного элемента. Количество полей в каждом записи – одинаково. Каждое поле в записи является объектом поиска или сортировки.
|
|
|
Создание списка (базы данных)
На листе не следует помещать более одного списка. Некоторые функции обработки списков, например фильтры, не позволяют обрабатывать несколько списков одновременно. В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка. Списков должен быть организован так, чтобы во всех строках в одинаковых столбцах находиться однотипные данные. Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
Для создания списка с помощью формы (маски ввода):
1. Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.
2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные / Форма.
3. В отрывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Tab> – для перехода вниз и < Shift><Tab> – для перехода вверх.
4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.
Для завершения процесса ввода данных нажмите кнопку Закрыть.
Поиск записей в списке
Для того чтобы в большой таблице найти записи, удовлетворяющие некоторому условию:
1. Установите курсор в любую ячейку списка и выберите команду Данные/ Форма.
|
|
|
2. Нажмите кнопку Критерии.
3. Введите критерии поиска а одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести >500000.
4. Нажимайте кнопки Назад и Далее, либо кнопки полосы прокрутки для поиска записей, отвечающих установленному критерию.
Для заданного нового критерия поиска нажмите кнопку Очистить.
Фильтрация данных
Фильтрация - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям отбора, заданным для столбца. Microsoft Excel предоставляет две команды для фильтрации списков:
¨ Автофильтр, включая фильтр по выделенному, для простых условий отбора;
¨ Расширенный фильтр для более сложных условий отбора.
В отличие от сортировки, при фильтрации порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.
Пример. Выбор данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию
Этап 1. Формирование диапазона условий по типу Критерий сравнения
1.Скопируйте все имена полей списка (рис.14) в другую область на том же листе, например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 – имена полей области критерия, J2:O5 – область значений критерия.
Рис. 4.5
2.Сормируйте в области условий отбора Критерий сравнения – о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:
в столбец Номер группы - точное значение 133;
в столбец Код предмета – точное значение п1;
в столбец Оценка – условие - >3.
Этап 2. Фильтрация записей расширенным фильтром.
Произведите фильтрацию записей на том же листе:
· установите курсор в область списка (базы данных);
|
|
|
· выполните команду Данные, Фильтр, Расширенный фильтр;
· в диалоговом окне <<Расширенный фильтр >> с помощью мыши задайте параметры, например:
Скопировать результат в другое место: установить флажок
Исходный диапазон: А1:G17
Диапазон условия: J1:O5
Поместить результат в диапазон:J6
· Нажмите кнопку ОК.
3.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.
Этап 1. Формирование диапазона условий по типу Вычисляемый критерий
Сформируйте в области условий отбора вычисляемый критерий – для каждого преподавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий – л; результат отбора поместите на новый рабочий лист. Для этого:
· В столбец Вид занятия введите точное значение – букву л;
· Переименуйте в области критерия столбец Оценка, например, на имя Оценка1;
· В столбец Оценка 1 введите вычисляемый критерий, например, вида
=G2>CPЗНАЧ($G$2:$G$17),
где G2 – адрес первой клетки с оценкой в исходном списке,
$G$2:$G$17- блок ячеек с оценками,
CPЗНАЧ – функция вычисления среднего значения.
Этап 2. Фильтрация записей с расширенным фильтром.
Произведите фильтрацию записей на новом листе:
· Установите курсор в область списка (базы данных);
· Выполните команду Данные, Фильтр, Расширенный фильтр;
· В диалоговом окне Расширенный фильтр с помощью мыши задайте параметры, например:
Скопировать результат в другое место: установите флажок
Исходный диапазон: А1:G17
Диапазон условия:J1:O5
Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке
· Нажмите кнопку ОК.
Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.
Сводные таблицы
Сводные таблицы применяются для обработки больших списков данных. С ее помощью можно быстро извлечь из больших баз данных необходимую информацию, благодаря ее возможности одновременно выполнять различные операции.
Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц. Перед построением необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры.
|
|
|
1. Установите курсор в любую ячейку списка и выберите команду Данные/Сводная таблица.
2. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных MS Excel.
3. Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.
4. В следующем окне определите, значения каких полей списка будут использоваться в качестве заголовок строк (зона Строка), каких – в качестве заголовков столбцов (зона Столбец) и каких - в качестве данных (зона Данные), по которым следует подвести необходимые итоги. В зону Страница помещается кнопка поля, по которому предполагается фильтровать данные. В каждой зоне может быть несколько кнопок.
5. Далее определяется место, в которое будет помещена разработанная сводная таблица.
6. После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными.
|
|
|
