Работа с таблицей Excel как с базой данных
Обработка информации в электронных таблицах Excel или списках, основные понятия и требования к спискам, экономико-математические приложения Excel. Решение уравнений и задач оптимизации: подбор параметров, команда "Поиск решения", диспетчер сценариев.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | реферат |
| Язык | русский |
| Дата добавления | 08.11.2010 |
| Размер файла | 704,3 K |
- посмотреть текст работы
- скачать работу можно здесь
- полная информация о работе
- весь список подобных работ
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
«Работа с таблицей Excel как с базой данных»
1. Обработка информации в электронных таблицах Excel или списках. Основные понятия и требования к спискам
2. Экономико-математические приложения Excel
3. Решение уравнений и задач оптимизации
3.1 Подбор параметров
3.2 Команда «Поиск решения»
3.3 Диспетчер сценариев «что-если»
1. Обработка информации в электронных таблицах Excel или списках. Основные понятия и требования к спискам
Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов). Список — это набор строк электронной таблицы со взаимосвязанными однотипными данными постоянного формата. Другими словами список — это плоская база данных, а строки и столбцы списка соответствуют записям и полям в базе данных. Образец списка представлен на рисунке.
К спискам в Excel предъявляются более строгие требования, чем к обычным электронным таблицам. Количество столбцов в списке должно быть постоянным, а количество строк переменным. Это позволяет добавлять, удалять или переставлять строки таблицы или записи списка (базы данных).
Наличие пустых строк и столбцов в списке является недопустимым. Данные в списке должны иметь постоянный формат. Первая строка в списке содержит названия столбцов или имена полей как в базах данных.
К средствам, которые предназначены для обработки и анализа данных в списке относятся команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.
При применении команды сортировка можно отсортировать записи по одному или нескольким полям. С помощью фильтров (Автофильтра и Расширенного фильтра) можно быстро найти (отфильтровать) необходимые данные в списках по одному, двум или нескольким параметрам поиска. Командой Итоги можно упорядочить данные в списках с помощью итоговых значений.
Для добавления новых записей в список, удаления и поиска существующих записей в списках применяется команда Форма. Для проверки данных при вводе используется средство, которое называется проверкой ввода (команда Проверка).
При создании списка необходимо выполнить определенные требования:
· Чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список;
· Формат шрифта заголовков (подписей) столбцов или имен полей в списках должен отличаться от формата шрифта записей. Обычно шрифту заголовкам столбцов назначается полужирный шрифт, а ячейкам для заголовков присваивается текстовый формат;
· Ячейки под заголовками столбцов необходимо отформатировать в соответствии с данными, которые будут вводиться в эти ячейки (например, установить денежный формат, выбрать выравнивание и т.д.);
· Для обеспечения автоматического форматирования введенных данных в список целесообразно активизировать команду "Расширение форматов и формул". Для этого необходимо установить флажок "Расширять форматы и формулы в диапазонах данных" в окне диалога "Параметры" на вкладке "Правка", которое открывается командой "Параметры" в меню Сервис;
· В списке не должно быть пустых записей (строк) и полей (столбцов), даже для отделения имен полей от записей следует использовать границы ячеек, а не пустые строки.
После выполнения подготовительных работ по созданию списка можно переходить к введению данных в список.
2. Экономико-математические приложения Excel
К типичным экономико-математическим приложениям Excel относятся:
· структуризация и первичная логическая обработка данных;
· статистическая обработка данных, анализ и прогнозирование;
· проведение финансово-экономических расчетов;
· решение уравнений и оптимизационных задач.
Структуризация и первичная логическая обработка данных
Списки в Excel являются примером формализованной структуры у исходной информации. Список — это содержащая в рабочем листе Excel таблица, данные в строках которой имеют однородную структуру или данные одного типа. К традиционным задачам первичной или предварительной логической обработки данных относятся сортировка и выборка (фильтрация) по заданному критерию. Статистическая обработка данных, анализ и прогнозирование
Функции, реализующие статистические методы обработки и анализа данных, в Excel реализованы в виде специальных программных средств — надстройки Пакета анализа, которая входит в поставку Microsoft Office и может устанавливаться по желанию пользователей. Установка надстройки Пакет анализа осуществляется так же, как и установка других надстроек с помощью команды Сервис/Надстройка. Далее необходимо установить флажок перед пунктом Пакет анализа и нажать ОК.
После успешной установки надстройки в меню Сервис появится пункт: Анализ данных, а в окне мастера функций становится доступной категория функций — Статистические.
Целью статистического исследования является обнаружение и исследование соотношений между статистическими (экономическими) данными и их использование для изучения, прогнозирования и принятия решений. Фундаментальным понятием статистического анализа являются понятия вероятности и случайной величины. Excel не предназначен для комплексного статистического анализа и обработки данных, но с помощью команд, доступных из окна Анализ данных можно провести:
· описательный стстистический анализ (описательная статистика);
· ранжирование данных (Ранг и персентиль);
· графический анализ (Гистограмма);
· прогнозирование данных (Скользящее среднее. Экспоненциальное сглаживание);
· регрессионный анализ (Регрессия) и т.д.
Статистические функции для регрессионного анализа из категории Статистические в окне мастера функций:
· ЛИНЕЙН(знач.У; знач.Х; константа;стат.) — Определяет параметры линейного тренда для заданного массива;
· ТЕНДЕНЦИЯ(знач.У;знач.Х; новые знач.Х; константа;) — Определяет предсказанные значения в соответствии с линейным трендомдля заданного массива (метод наименьших квадратов) и многие другие.
Проведение финансово-экономических расчетов
Одной из важнейших сфер приложения Excel — это осуществление финансовых рачетов. Финансовые вычисления включают в себя всю совокупность методов и расчетов, используемых при принятии управленческих решений (от элементарных арифметических операций до сложных алгоритмов построения многокритериальных моделей, позволяющих получить оптимальные характеристики коммерческих сделок и т.д.).
В Excel реализованы встроенные и дополнительные финансовые функции. Для применения дополнительных функций необходимо установить надстройку Пакет анализа.
По типу решаемых задач все финансовые функции Excel можно разделить на условные группы:
· функции для анализа инвестиционных проектов;
· функции для анализа ценных бумаг;
· функции для расчета амортизационных платежей;
Применение функций для финансовых расчетов осуществляется с помощью мастера функций (Вставка/Функция, выбрать категорию Финансовые).
3. Решение уравнений и задач оптимизации
Для решения задач оптимизации широкое променение находят различные средства Excel:
· Подбор параметров для нахождения значения, приводящего к требуемому результату.
· Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;
· Диспетчер сценариев для создания и оценки наборов сценариев «что — если» с несколькими вариантами исходных данных.
3.1 Подбор параметров
Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.
Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка
Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:
· формула для расчета;
· пустая ячейка для искомого значения;
· другие величины, которые используются в формуле.
Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel. Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи.
Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления)
Оптимизация с помощью команды Подбор параметров выполняется так:
1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.
3. Введите в текстовое поле Значение число, соответствующее объему продаж — 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.
3.2 Команда «Поиск решения»
Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка — Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами.
Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки лежат итерационные методы.
В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек.
Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:
· Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.
· Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке.
· Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.
Постановка задачи Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которую вводится основная формула.
Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.
После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.
1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».
3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.
3.3 Диспетчер сценариев «что-если»
При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений — при каждом изменении данных предыдущее значение пропадает.
Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что — если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.
Сценарием называется модель «что — если», в которую входят переменные ячейки, связанные одной или несколькими формулами. Перед созданием сценария необходимо спроектировать лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения. Например, может возникнуть потребность в сравнении лучшего и худшего сценариев.
Создание сценариев происходит следующим образом:
· Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
· Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
· Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
· Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
· Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.
Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:
· Выполните команду Сервис/Сценарии. Открывается окно диалога:
· Выберите из списка сценарий для просмотра.
· Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
· Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что — если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.
Создание отчетов по сценарию
Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа.
Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.
Создание отчета по сценарию происходит следующим образом:
· Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
· Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
Подобные документы
Общее понятие об электронных таблицах Excel, использование формул, функций и диаграмм. Принципы обработки информации в электронныхх таблицах, общие требования к спискам. Экономико-математические приложения Excel, решение уравнений и задач оптимизации.
реферат [2,5 M], добавлен 10.11.2010
Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.
курсовая работа [1,1 M], добавлен 27.08.2012
Ознакомление с разнообразными надстройками, входящими в состав Microsoft Excel; особенности их использования. Примеры решения задач линейного программирования с помощью вспомогательных программ "Подбор параметра", "Поиск решения" и "Анализ данных".
реферат [2,5 M], добавлен 25.04.2013
Анализ возможностей текстового редактора Word и электронных таблиц Excel для решения экономических задач. Описание общих формул, математических моделей и финансовых функций Excel, используемых для расчета скорости оборота инвестиций. Анализ результатов.
курсовая работа [64,5 K], добавлен 21.11.2012
Назначение и функциональные возможности табличного процессора Excel, его структура, особенности форматирования и режим работы. Работа с электронной таблицей как с базой данных, типы данных в ее ячейках. Средства для создания печатного документа Excel.
контрольная работа [26,1 K], добавлен 28.10.2009
Изучение и освоение инструментальных средств Excel для управления базами данных. Правила формирования списка на рабочем листе Excel. Простая и многоуровневая сортировка списка. Варианты фильтрации данных в таблице. Вычисляемый критерий и его создание.
лабораторная работа [297,2 K], добавлен 15.11.2010
Понятие и возможности MS Excel. Основные элементы его окна. Возможные ошибки при использовании функций в формулах. Структура электронных таблиц. Анализ данных в Microsoft Excel. Использование сценариев электронных таблиц с их практическим применением.
курсовая работа [304,3 K], добавлен 09.12.2009
Работа с таблицей как с базой данных в excel
Тема 5. ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL
Вопрос 6. Работа с таблицей как с базой данных
Для управления большими массивами данных используются специальные программы, предназначенные для работы с базами данных. В Excel также имеются средства для обработки данных, организованных по аналогичному принципу, – это, так называемые, функции списка. В виде списка можно представить таблицу, строки которой содержат однородную информацию. Список характеризует не содержимое таблицы, а способ ее организации. Отдельные записи (комплекты данных списка) должны быть однородны по строкам и/или столбцам. В таблице не должно быть объединенных ячеек! Однако только однородность данных списка по строкам делает возможным применение функции фильтров (если данные однородны по столбцам, таблицу следует транспонировать). Примером базы данных является список адресов, в котором указаны названия и адреса различных фирм или фамилии людей.
Excel распознает списки автоматически. Поэтому если надо, например, отсортировать данные в списке по определенному критерию, достаточно выделить любую ячейку списка. После активизации функции сортировки Excel автоматически выделит всю область списка.
При автоматическом определении (и выделении) списка признаком конца области списка считается первая пустая строка. Поэтому предварительно следует удалить из списка пустые строки или выделить вручную область, для которой должна быть применена функция списка. Предварительное выделение области необходимо также в том случае, если функция должна быть применена только к определенной части списка. Функции списка нельзя использовать, если выделены несмежные области.
При автоматическом выделении области списка Excel в поисках меток (названий) столбцов сравнивает содержимое первой и второй строк области списка. Если данные в этих строках различаются по типу, Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки будут исключены из обрабатываемой области списка.
Если Excel не обнаружит различий между типами данных в первой и второй строках, то на экране появится сообщение о том, что программа не смогла обнаружить названий для образования меток столбцов. В этом случае программа выдаст запрос, следует ли использовать в качестве меток столбцов данные первой строки выделенной области. Пользователь может согласиться нажатием [ОК] или отменить выполнение операции.
Основными возможностями при работе с базами данных являются:
· организация ввода данных;
· поиск данных по заданному критерию;
Для работы с таблицей как с базой данных в Exce l используется пункт меню Данные. Эта команда корректно работает только с таблицами, в которых названия колонок занимают одну строку. Так, например, для работы с командой Данные таблица Учет движения материалов на складах должна быть преобразована к виду, представленному на рис. 5.14.
Приведем таблицу Учет движения материалов на складах к виду, представленному на рис. 5.14.
1. Создайте копию листа Товары, чтобы легко было вернуться к первоначальному порядку данных. Для этого активизируйте его и выполните команду Правка►Переместить/скопировать лист. В окне Переместить или скопировать установите флажок Создавать копию и нажмите [ОК]. Переименуйте полученный лист Товары(2) на Сортировка.
2. Для преобразования таблицы сделайте следующее:
· выделите строку 2 щелчком мыши на номере строки и удалите ее командой Правка►Удалить. Аналогично удалите итоговую строку.
· Дополните шапку таблицы недостающими заголовками.
5.6.1. Работа с формой
Удобным средством для работы с таблицей является форма (окно формы открывается по команде Данные►Форма. В окне формы (рис.5.15) отображаются все поля одной записи базы данных: слева располагаются названия полей, рядом с ними их значения, доступные для редактирования. Если поле вычисляемое, то оно не доступно для редактирования.
Работа с формой предполагает:
1. Перемещение по полям записи мышью или клавишей [ Tab ];
2. Перемещение по записям с помощью полосы прокрутки и кнопок [Назад] и [Далее];
3. Ввод или просмотр записей. Достоинством ввода информации через форму является автоматическое копирование формул и автоматическая поддержка форматов данных.
4. Удаление и добавление записей с помощью кнопок [Добавить] и [Удалить]. При добавлении новой записи, она всегда добавляется в конец таблицы.
5. Редактирование значений полей в записях.
6. Поиск данных по критерию с помощью кнопки [Критерии], после нажатия которой в появившемся окне в соответствующих полях задаются критерии. Задание критериев позволяет просматривать через окно Формы только те записи, которые удовлетворяют некоторым условиям поиска (критериям).
Перед тем как начать поиск записей по критерию, рекомендуется сделать текущей первую запись базы данных. Просмотр отобранных записей по критерию осуществляется с помощью кнопок [Назад] и [Далее]. При задании критериев поиска можно использовать символы подстановки:
* – для обозначения произвольного количества символов;
? – для обозначения одного символа.
Например, при необходимости выбора всех записей с фамилиями студентов, начинающихся с буквы «К», в качестве критерия поиска следует ввести К*. А при задании критерия К?рсанов, будут выбираться фамилии Кирсанов, Керсанов, Корсанов и т.п.
В критериях при поиске числовых значений можно использовать операторы сравнения: =, , <>, =.
Чтобы задать несколько критериев поиска, следует указать их в различных полях, тогда они объединяются логическим И. Задание нескольких критериев позволяет сузить область поиска.
5.6.2. Сортировка данных
Упорядочение табличных данных в выделенных строках по алфавиту, величине или дате называется сортировкой. Столбец, определяющий порядок данных, называются ключом сортировки. Сортировка производится в убывающем или возрастающем порядке. Для сортировки в Excel используется команда Данные►Сортировка. Можно задавать три уровня сортировки одновременно: за одну сортировку можно выполнить сортировку сначала по первому уровню, потом в полученном списке – по второму, а затем – по третьему (рис. 5.16).
Если сортировка ведется по нескольким ключам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле В последнюю очередь, по.
Рассмотрим порядок сортировки таблицы Учет движения материалов на складах по двум ключам: 1 – номер склада, 2 – код материала.
1. Активизируйте лист Сортировка.
2. Установите курсор в любую ячейку диапазона A 2: F 7 , который необходимо отсортировать, т.е. диапазона таблицы вместе с шапкой.
3. Выберите команду Данные►Сортировка. В окне Сортировка диапазона (рис. 5.16) в раскрывающемся списке Сортировать по выберите поле, по которому следует выполнить сортировку в первую очередь – это № склада. Поставьте переключатель справа в положение – по возрастанию.
4. В поле Затем по выберите второй ключ сортировки – поле Код материала, способ сортировки – по возрастанию. Нажмите кнопку [ОК]. Вид таблицы после выполнения сортировки представлен на рис. 5.17. Обратите внимание, что записи, имеющие одинаковые значения номера склада, выстроились в порядке возрастания кода материала.
5.6.3. Фильтрация данных
Excel дает возможность выборочно работать с данными, удовлетворяющими условиям (критериям) поиска, которые задает пользователь. Процесс выбора данных называется фильтрацией. Фильтрация происходит в пределах предварительно выделенного диапазона или заданной таблицы.
В Excel существует возможность фильтрации данных с помощью Автофильтра и Расширенного фильтра.
С помощью функции автофильтра выбор отдельных записей можно производить непосредственно в самой таблице. При этом для выбора данных можно задавать целый ряд различных критериев. Активизация функции автофильтра происходит путем выбора команды Данные ► Фильтр► Автофильтр . Фильтрация таблицы оставляет на экране для обработки только те записи, которые удовлетворяют критериям, остальные строки становятся скрытыми. Команда вызова Автофильтра помещает кнопки раскрывающихся списков в названия полей, при помощи этих кнопок задаются критерии отбора. Стрелки кнопок раскрывающихся списков тех полей, которые задействованы в критерии, меняют цвет с черного на голубой.
Если задать критерии отбора в нескольких столбцах (полях), то они связываются между собой по принципу логического И.
Автофильтр предоставляет несколько видов фильтрации данных:
1. Выбор записей с заданным значением поля происходит путем выбора в раскрывающемся списке значения поля для поиска точного соответствия.
2. Выбор записей по условию производится командой Условие, в результате чего открывается диалоговое окно Пользовательский автофильтр, где задают критерии с участием одного или двух условий с использованием шаблонов(”?” и “*”) , операций сравнения и логических операций И, ИЛИ.
3. Выбор первых наибольших или наименьших n значений позволяет выполнить команда Первые 10.
4. Команда Все восстанавливает на экране все скрытые фильтром строки таблицы.
Рассмотрим использование автофильтра на примере таблицы Учет движения материалов на складах. Пусть из таблицы необходимо выбрать информацию о материалах со склада №2, у которых остаток на конец месяца больше 50.
1. Создайте копию листа Сортировка и назовите его Автофильтр.
2. Установите курсор в любую ячейку диапазона A 2: F 7 .
3. Выберите команду Данные► Фильтр► Автофильтр.
4. Ячейки с названиями полей превращаются в раскрывающиеся списки.
5. Раскройте список в столбце № склада для включения в критерий.
6. Выберите строку, где номер склада равен 2.
7. Раскройте список столбца Остаток на конец месяца.
8. Выберите команду Условие. В окне Пользовательский автофильтр (рис. 5.18.) в левом верхнем раскрывающемся списке выберите операцию сравнения больше, в правом – введите значение 50 и нажмите [ОК]. На экране останется одна строка, удовлетворяющая условиям отбора. Остальные строки таблицы окажутся скрытыми.
9. Для отмены всех условий отбора надо выполнить команду Данные► Фильтр►Отобразить все.
Чтобы отменить режим Автофильтра, необходимо повторно выбрать команду Данные►Фильтр►Автофильтр.
Использование расширенного фильтра
Расширенный фильтр предоставляет широкие возможности поиска и фильтрации. Он позволяет не только применять операции И, ИЛИ, но и составлять вычисляемые критерии. Кроме того, отфильтрованные данные могут быть скопированы в заданный диапазон рабочего листа.
При работе с расширенным фильтром создаются три области:
1. Исходный диапазон . Область, где хранятся исходные данные. Содержит диапазон, подлежащий фильтрации, т.е. исходную таблицу обязательно вместе с шапкой таблицы.
2. Диапазон условий . Область на рабочем листе, где задаются критерии поиска информации. Диапазон условий применяется для задания условия отбора записей и формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки или строк критериев.
При создании диапазона условий необходимо пользоваться следующими рекомендациями:
· В верхней строке диапазона критериев должны быть расположены имена, в точности, совпадающие с заголовками столбцов исходного диапазона. Можно перечислить все имена, но это необязательно, достаточно указать только те, которые определяют условия отбора. Точное соответствие имен полей исходного диапазона и имен, указанных в диапазоне условий, проще всего обеспечить копированием из самой таблицы.
· Ниже должна располагаться хотя бы одна строка, где задаются условия. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на вычисляемое формулой значение.
· Для объединения критериев с помощью логического И нужно указать задаваемые критерии в одной строке, а для объединения критериев с помощью логического ИЛИ следует представить критерии в разных строках. Следует также учитывать, что в случае необходимости вместе с критерием в ячейку надлежит ввести оператор сравнения. Для обозначения точного соответствия поля записи заданному критерию при задании критериев знак равенства (=) не используется.
3. Выходной диапазон. Область, в которую Excel копирует выбранные из таблицы данные. Этот диапазон должен быть расположен на том же листе, что и исходный. Извлеченную информацию можно поместить на другой лист копированием результата фильтрации. Задание выходного диапазона необязательно, т. к. существует опция “Фильтровать список на месте”. В этом случае фильтрация преобразует исходную таблицу в набор записей, удовлетворяющих условиям выбора.
Диапазон условий и выходной диапазон рекомендуется отделять от исходного диапазона, хотя бы одной пустой строкой (если они будут располагаться ниже или выше исходного диапазона) или одним пустым столбцом (если они будут располагаться левее или правее исходного диапазона). Если же предполагается, что исходный диапазон со временем будет расширяться, то диапазон условий рекомендуется разместить выше списка.
Рассмотрим порядок выбора из таблицы Учет движения материалов на складах с помощью Расширенного фильтра материалов, у которых приход превысил величину 200, а остаток на конец месяца составил меньше чем 15.
1. Создайте копию листа Сортировка и назовите его Расш_фильтр.
2. Сформируйте диапазон условий, для чего скопируйте заголовки полей исходного диапазона, которые будут ключевыми при отборе записей, и заполните строки критериев:
· c копируйте D 2 в A 10 ; F 2 – в B 10 ;
· в A 11 запишите критерий: >200, а в В11 критерий:
3. Сформируйте выходной диапазон, скопировав заголовки полей исходного диапазона, которые необходимо отобразить в выходном диапазоне:
· выделите и скопируйте в буфер диапазон A 2: F 2 ;
· вставьте его содержимое в А13: F 13 .
4. Установите курсор в любую ячейку исходного диапазона.
5. Выберите команду Данные►Фильтр►Расширенный фильтр. В диалоговом окне Расширенный фильтр (рис. 5.19) проделайте следующие действия:
· в группе Обработка выберите переключатель Скопировать результат в другое место. В этом случае исходная таблица останется нетронутой, а отобранные записи будут помещены в выходной диапазон. (При выборе переключателя Фильтровать список на месте не удовлетворяющие критерию записи будут скрыты в исходной таблице);
· установите курсор в поле Исходный диапазон и введите ссылку на диапазон исходной таблицы, включая шапку (A2:F7). Ввод ссылок во всех полях данного диалогового окна лучше всего осуществлять путем выделения указателем мыши нужного диапазона;
· установите курсор в поле Диапазон условий и введите ссылку на диапазон условий (A10:В11);
· так как выбран переключатель Скопировать результат в другое место, перейдите к полю Поместить результат в диапазон и введите ссылку на выходной диапазон (А13: F 20 );
· установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех, удовлетворяющих критерию, одинаковых записей); нажмите кнопку [ОК].
Результаты работы Расширенного фильтра сразу же отобразятся на рабочем листе (рис. 5.20). Причем, если в окне Расширенный фильтр установлен переключатель Фильтровать список на месте, то подобно Автофильтру команда Расширенный фильтр скроет все строки, которые не удовлетворяют фильтру и отметит номера отобранных строк синим цветом, а в строке состояния будет отображено число найденных записей.
Примечание. При каждом выполнении команды Расширенный фильтр Excel просматривает полный список, а не текущее множество ранее отфильтрованных строк. Вследствие этого не обязательно использовать команду Показать все перед изменением фильтра.
Выберите из таблицы Учет движения материалов на складах только данные о материалах, у которых остаток на конец месяца меньше 10 или больше 50.
Как формируются диапазоны условий в этом случае показано на рис.5.21.
5.6.4. Подведение итогов
Часто бывает необходимо посчитать промежуточные и общие итоги в таблице. При этом таблица должна быть отсортирована по столбцам, которые являются группировочными признаками. Команда Данные►Итоги добавляет строки промежуточных итогов для каждой группы элементов. Допускается использование различных функций (например, среднего значения, количества строк или пустых ячеек, стандартное отклонение и т.д.) для вычисления итогов в пределах каждой группы.
В диалоговом окне Промежуточные итоги поле При каждом изменении в используется для указания столбца, по которому следует сгруппировать данные для подведения итогов; поле Операция позволяет использовать различные функции; поле Добавить итоги по: позволяет отметить все поля, по которым будут подводиться итоги. Установка флажка Заменить текущие итоги позволяет заменить в таблице уже существующие итоги. Установленный флажок Конец страницы между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Флажок Итоги под данными дает возможность поместить строки промежуточных и общих итогов под соответствующими данными. Кнопка [Убрать все] позволяет восстановить исходный вид экрана до подведения итогов.
Используя возможности по автоматическому подведению итогов, рассчитаем частные (по каждому складу) и общие итоги в таблице Учет движения материалов на складах.
1. Отсортируйте таблицу по столбцу № склада.
2. Выделите диапазон таблицы вместе с шапкой, т.е. A 2: F 7 .
3. Откройте окно формирования итогов командой Данные►Итоги.
4. Заполните диалоговое окно как показано на рис. 5. 22 и нажмите [ОК].
В результате подведения итогов таблица примет вид, представленный на рис. 5.23.
Кроме итоговых строк программа сформировала структуру (см. слева от таблицы), которая согласована с группировкой данных для вычисления промежуточных и общих итогов. Знак «-» означает, что можно спрятать строки, относящиеся к группе, оставив только промежуточный или общий результат, (щелкнув по кнопке [—]). Кнопки , , также служат для управления отображением структуры. Например, кнопка оставляет в таблице только промежуточные и общие итоги, скрывая содержимое таблицы .
* Теоретический материал по теме 5 составлен ст. преподавателем Т.В. Куратевой, ассистентом Е.П. Холодовой и опубликован в учебно-практическом пособии "Основы информатики и вычислительной техники" ( Дистанционное обучение. – Мн.: БГЭУ, 2005).