- •Воронеж – 2007
- •Методическое пособие
- •Воронеж – 2007
- •Введение
- •Справочная система Excel
- •Элементы экрана программы Excel
- •Разработка электронных таблиц Создание электронных таблиц
- •Вставка строк и столбцов
- •Форматы вводимых данных
- •Автозаполнение
- •Редактирование данных ячеек
- •Создание примечаний
- •Оформление таблицы
- •Выполнение расчетов
- •Для ввода формулы, содержащей функцию:
- •Адресация ячеек Ссылки на ячейки и диапазоны ячеек При использовании информации ячеек применяют их адреса. Адресацию ячеек принято называть ссылками.
- •Использование имен ячеек и фрагментов таблиц
- •Работа с диаграммами
- •Для изменения порядка отображения рядов данных необходимо:
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Работа с большими таблицами
- •Просмотр таблицы в несколько окон
- •Разбивка таблицы на листы
- •Печать повторяющихся заголовков
- •Операции над рабочими листами
- •Связывание данных листов таблиц на одном рабочем листе
- •Консолидация данных
- •Обработка информации в таблице Работа с базами данных (списками)
- •Сортировка данных в таблице
- •Подведение промежуточных итогов в таблице
- •Анализ данных
- •Диспетчер сценариев
- •Отбор данных из списка (фильтрация)
- •Условия отбора
- •Поиск решения
- •Процесс поиска решения приводится по следующему алгоритму:
- •Кнопка Восстановить служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
- •Сводные таблицы
- •Обмен данными
- •Вставка информации в список Ехсеl из другой программы
- •Вопросы для самопроверки
- •Упражнения по работе
- •Упражнение №2 Расчеты в электронных таблицах. Адресация
- •Упражнение №3 Сортировка и фильтрация данных электронных таблиц
- •Упражнение №4 Построение диаграмм. Форматирование диаграмм
- •Упражнение №5 Подбор параметров и подведение промежуточных итогов
- •Упражнение № 6 Сводные таблицы
- •Упражнение №7 Поиск решения. Анализ деятельности предприятия
- •Выполнение работы
- •Упражнение №8 Поиск решения. Транспортная задача
- •Постановка и решение транспортной задачи
- •Упражнение №9 Итоговое Цель работы
- •Задание на самоподготовку
Отбор данных из списка (фильтрация)
Фильтрация списка позволяет выбрать из него те записи, которые удовлетворяют заданным условиям. Так как таких записей всегда бывает меньше, чем всего в полном списке, то их проще анализировать. Это не значит, что записи списка пропадают, просто они скрываются, после проведения анализа их опять можно открыть и работать с полным списком.
В отличие от сортировки, при фильтрации порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать.
Строки, отобранные при фильтрации в Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.
Для проведения несложной фильтрации используют Автофильтр, который не требует сложных критериев отбора записей. Если необходимо применить сложные критерии отбора, то используют Расширенный фильтр.
Чтобы отобрать строки из списка с использованием одного или двух условий отбора для одного столбца с использованием автофильтра, используется следующая процедура:
Установить курсор в любую ячейку списка, задать команду Данные►Фильтр, а затем выбрать пункт Автофильтр. В названиях столбцов появятся кнопки со стрелками;
Нажать кнопку со стрелкой в том столбце, по значениям которого требуется выбирать данные;
Выбрать любой элемент из раскрывающегося списка;
При использовании пункта Условие можно задавать до двух критериев одного столбца, выбирая из списка операторов и списка значений данного поля те значения, которые необходимы для используемого критерия. В качестве условий может быть как равенство, так и неравенство выбираемых значений. Задаваемые условия могут использовать жесткое правило одновременного выполнения, или более простое правило действия хотя бы одного из них, что определяют использованием операций И или ИЛИ (установить флажки в соответствующих полях открывшегося окна).
Чтобы отобразить строки, удовлетворяющие одному условию отбора, выбирается необходимый оператор сравнения в первом поле под надписью Показать только те строки, значения которых и значение для сравнения справа от него.
Чтобы отобразить строки, удовлетворяющие одновременно двум условиям отбора, вводится оператор и значение для сравнения в первой группе полей, для чего необходимо установить переключатель И, а затем ввести второй оператор и значение для сравнения во второй группе полей.
Чтобы отобразить строки, удовлетворяющие одному из двух условий отбора, вводится оператор и значение для сравнения в первой группе полей, нажимается кнопка Или, а затем вводится второй оператор и значение для сравнения во второй группе полей.
Завершив установку условий, подтвердить условия фильтрации нажатием клавиши Ок;
Список можно еще урезать, если последовательно применить условия отбора записей по другим столбцам;
Для восстановления записей необходимо использовать команду Данные►Фильтр►Показать все записи или в раскрывающемся списке столбца, в котором производилась фильтрация, выбрать пункт Все. Для отмены фильтрации повторно подать команду Данные►Фильтр►Автофильтр.
При фильтрации списка с использованием сложных критериев используется расширенный фильтр. При этом можно использовать несколько условий отбора к одному столбцу, либо несколько условий отбора к нескольким столбцам, либо условия отбора определять как результат выполнения формулы.
При использовании расширенного списка стрелки раскрытия списка автофильтра не отображаются. Вместо этого условия вводятся диапазон условий на листе. Для того чтобы изменить способ фильтрации данных, следует изменить значения в диапазоне условий отбора и выполнить фильтрацию еще раз.
Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк сверху от списка. Эти строки будут использованы в качестве диапазона условий отбора.
Порядок использования расширенного фильтра:
Указать любую ячейку в списке, где необходимо произвести фильтрацию;
Подать команду Данные►Фильтр►Расширенный фильтр.;
Выделить заголовки фильтруемых столбцов списка и нажать кнопку Копировать;
Выделить первую пустую строку диапазона условий отбора и нажать кнопку Вставить. Между диапазоном условий отбора и списком должно находить не менее одной пустой строки;
Ввести в строки под заголовками условий требуемые критерии отбора;
Результат фильтрации может быть оставлен на месте исходной таблицы, для чего в ней скрываются ненужные строки, для чего необходимо установить переключатель Обработка раскрываемого окна Расширенный фильтр в положение Фильтровать список на месте;
Чтобы скопировать отфильтрованные строки в другую область листа, необходимо установить переключатель Обработка в положение Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон, а затем указать верхнюю левую ячейку области вставки;
Ввести в поле Диапазон условий ссылку на диапазон условий отбора, включающий заголовки столбцов;
Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, следует нажать кнопку свертывания диалогового окна;
После установки параметров окна нажать кнопку Ок. выбранные данные будут размещены в указанной для них области.
Если присвоить диапазону имя Критерии, то ссылка на диапазон будет автоматически появляться в поле Диапазон условий. Можно также определить имя База_данных для диапазона фильтруемых данных и имя Извлечь для области вставки результатов, и ссылки на эти диапазоны будут появляться автоматически в полях Исходный диапазон и Поместить результат в диапазон соответственно.
Когда отобранные строки копируются в другое место, можно указать столбцы, включаемые в копию. Перед фильтрованием необходимо скопировать заголовки нужных столбцов в первую строку области вставки результатов, а при фильтровании ввести ссылку на скопированные заголовки столбцов в поле Поместить результат в диапазон. Скопированные строки будут включать только столбцы, заголовки которых были скопированы.