Создание информационной системы средствами MS Excel и VBA (стр. 2 из 4)
Рис.6 Сортировка диапазона.
На рисунке 7 показан список, сортированный по четырем полям: Тираж, Наименование книги, Год издания, Издательство
8.Фильтрация данных в списке
В MSExcel списком называется снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа.
Фильтрация списка позволяет находить и отбирать для обработки часть записей в списке, таблице или БД. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям. При этом остальные строки оказываются скрытыми. В MSExcel для фильтрации данных используются команды Автофильтр и Расширенный фильтр. Рассмотрим каждую из них.
Рис.7 Сортировка по четырем полям и более.
Автофильтр.
Автофильтр предоставляет простой доступ к мощным средствам рабочего листа, он используется при задании простых критериев для фильтрации нужной информации. Автофильтр выводит информацию на рабочем листе, при этом записи, не удовлетворяющие критерию, скрыты.
Чтобы включить автофильтр, нужно воспользоваться командой Данные/Фильтр/Автофильтр (рис.8). Excel выведет кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовком столбца. При помощи этих кнопок можно выбрать строки таблицы, которые необходимо вывести на экран.
Рис.8 Применение Автофильтра
При выборе одной из опций раскрывающегося списка: Все, Первые 10 или Условие, на экран могут выводиться все строки списка, часть списка наибольших или наименьших значений, а также элементы списка, удовлетворяющие указанному условию (рис.9).
Рис.9 Список команд для фильтрации.
При выборе из списка команд Первые 10 появляется диалоговое окно (рис.10), в котором следует выбрать количество и "качество" (т.е. наибольшее или наименьшее) отображаемых элементов.
Рис.10 Диалоговое окно команды Первые 10.
С другой стороны команда Условие отображает окно (рис.11), позволяющее ставить логические условия, определяющие режим фильтрации. В частности, в левой части окна следует выбрать требуемое условие (равно, больше, меньше и т.д.), а в правой – либо выбрать значение из списка, либо задать собственное.
Рис.11. Диалоговое окно команды Условие.
Наряду с этим, при выделении какого-либо значения (а не команды) в раскрывшемся списке MSExcel временно скрывает строки, которые не содержат данный элемент. Для возвращения исходного списка следует выбрать команду Данные/Фильтр/Отобразить все или Данные/Фильтр/Автофильтр.
Допустим, мне необходимо отфильтровать список по следующему условию: найти информацию об авторах, книги которых опубликованы позже 1990 г. в издательстве Просвещение и тираж которых больше 10000.
Для этого необходимо выделить строку заголовка списка и применить команду Данные/Фильтр/Автофильтр, как показано на рисунке 7. После этого в строке заголовка возле необходимых столбцов появляются кнопки со стрелками, которые позволяют отфильтровать список по необходимым критериям. На рисунке 12 показан список, отфильтрованный по заданному условию.
Рис.12 Фильтрация БД с помощью Автофильтр.
На рисунке 13 показан список, отфильтрованный по следующему условию: найти информацию о книгах, изданных после 1980г., но раньше 2000, в издательстве Просвещение или Машиностроение, цена которых больше 100 руб., но меньше 200 руб.
Рис.13 Список, отфильтрованный по заданному условию.
Расширенный фильтр.
Расширенный фильтр является более гибким средством отбора записей из БД, чем Автофильтр, он позволяет отыскивать строки с помощью более сложных критериев, по сравнению с пользовательским автофильтром.
Чтобы воспользоваться расширенным фильтром, необходимо выбрать команду меню Данные/Фильтр/Расширенный фильтр (рис.14)
Рис.14 Применение расширенного фильтра.
При фильтрации списка в некоторых случаях используются два типа критериев: множественные и вычисляемые. Множественные критерии применяются, когда нужно определить более двух критериев сравнения для одного столбца. Вычисляемые критерии применяются при использовании результатов вычислений в операциях сравнения.
Команда Расширенный фильтр используется для фильтрации данных интервала критериев. При этом в таблице отображаются только те строки, которые удовлетворяют всем критериям, записанным в интервале критериев (рис.15).
Рис.15 Окно диалога Расширенный фильтр.
Область Обработка содержит переключатель, который может быть установлен в одно из положений: Фильтровать список на месте или Скопировать результат на другое место. При установке переключателя в положение Фильтровать список на месте на экран отображаются только те строки, которые удовлетворяют указанному критерию. При выборе Скопировать результат на другое место отфильтрованные данные копируются на другой рабочий лист или в другую область на том же рабочем листе.
В поле ввода Исходный диапазон указывается интервал, содержащий список, который подлежит фильтрации. В это поле следует вводить ссылки на таблицу или БД.
В поле ввода Диапазон условий указывается интервал ячеек на рабочем листе, который содержит требуемый критерий.
В поле ввода Поместить результат в диапазон указывается интервал ячеек, в который копируются строки, удовлетворяющие критериям. Это поле доступно только в том случае, когда выбран переключатель Скопировать результат на другое место.
Флажок Только уникальные записи предназначен для отображения только неповторяющихся строк.
Допустим, мне нужно найти в БД информациюо книгах, изданных после 1970г. в издательстве Высшая школа, тираж которых больше 20000, а цена больше 100 рублей
Для этого необходимо открыть диалоговое окно расширенного фильтра, ввести в строку Исходный диапазон весь диапазон ячеек БД, а в Диапазон условий - диапазон ячеек, где указаны условия фильтрации. Если есть необходимость копировать отфильтрованный список в другое место, можно воспользоваться функцией "Скопировать результат в другое место" и в строке "Поместить результат в диапазон" указать диапазон ячеек, в который нужно скопировать отфильтрованный список.
На рисунке 15 изображен список, который отфильтрован по выше заданному условию.
Рис.15Фильтрация БД с помощью расширенного фильтра.
9. Функции для анализа списка
Понятие функции в MSExcel.
Функции в MSExcel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые употребляются для вычисления функций, называются аргументами. С другой стороны, значения, возвращаемые функциями в качестве ответа, называются результатами.
Помимо встроенных функций, в вычислениях могут применяться пользовательские функции, которые создаются при помощи средств MSExcel.
Чтобы использовать какую-либо функцию, следует ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться применяемые в формуле символы, называются синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если нарушить правила синтаксиса, то в этом случае MSExcel выдаст сообщение о том, что в формуле имеется ошибка.
Функций можно задавать с помощью диалогового окна "Мастер функций". Оно вызывается командой Вставка/Функция либо нажатием на кнопку Вставка функции на панели Стандартная.
После выбора требуемой функции следует нажать кнопку ОК, что приведет к появлению диалогового окна "Мастер функций". В появившемся диалоговом окне необходимо правильно ввести все необходимые условия и затем нажать на кнопку ОК, тем самым запустить функцию.
Функции для анализа списка.
Функции для анализа списка – это функции, возвращающие информацию об элементах списка, которые удовлетворяют некоторым условиям.
Например, мне нужно подсчитать количество книг, изданных после 1980г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей Для этого я сначала применила расширенный фильтр, тем самым отфильтровала БД по условию, показанному на рис.16.
Рис.16 Применение расширенного фильтра
Чтобы подсчитать количество книг, изданных после 1980 г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей, необходимо открыть диалоговое окно Мастер функций, найти функцию СЧЕТЕСЛИ, ввести в строку Диапазон весь диапазон ячеек отфильтрованного списка, а в строку Критерий – условие счета (рис.17)
Рис.17 Диалоговое окно функции СЧЕТЕСЛИ.
Применив функцию СЧЕТЕСЛИ, я подсчитала количество книг, изданных после 1980г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей (рис.18).
