Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Таблицы подстановки данных




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

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

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

Например, рассчитать значения функции:

на отрезке [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. После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными.

 

Поделиться:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...