скачать книгу бесплатно
Нажмите кнопку «Критерии». Откроется диалоговое окно формы для ввода различных критериев. Это окно изображено на Рисунок 1.1.2. Щелкните по полю «Наименование региона» и введите критерий. В данном случае в качестве критерия выступает набор символов «Орловская область».
Рисунок 1.1.2. Ввод критерия в форме данных
Нажмите кнопку «Далее». Excel просматривает весь список, начиная с первой записи, и ищет данные, удовлетворяющие введенному критерию. Если Excel находит запись, удовлетворяющую критерию, то она появляется в диалоговом окне. Искомая запись – это восьмая запись из 30 записей списка (Рисунок 1.1.3.). Нажмите кнопку «Далее», чтобы найти следующую запись, удовлетворяющую заданному критерию. Звуковой сигнал указывает на то, что ниже текущей записи не существует такой, которая удовлетворяла бы указанному критерию.
Рисунок 1.1.3. Результаты поиска по заданному критерию
Нажмите кнопку «Назад», для того, чтобы произвести поиск среди записей, предшествующих текущей записи. Звуковой сигнал указывает на то, что выше текущей записи не существует такой, которая удовлетворяла бы указанному критерию.
После поиска пользователь сразу получает возможность доступа к данным. Если хотя бы один раз режим ввода критериев был активизирован, то кнопка «Критерии» превращается в кнопку «Поиск», хотя в работе формы ничего не изменяется. Поскольку эти два режима функционирования формы очень похожи друг на друга по виду окна, то пользователь должен следить за тем, какой режим в данный момент включен.
Кнопка «Очистить» позволяет стереть введенные критерии, а кнопка «Вернуть» позволяет восстановить стертые критерии.
Нажмите кнопку «Закрыть», чтобы закрыть окно формы, либо нажмите кнопку «Правка», чтобы вернуться к правке формы.
В качестве второго примера рассмотрим поиск записей, у которых поле «Наименование экономического района» имеет значение «Центральный регион».
Пример ввода двух критериев поиска
Поиск записей осуществляется по двум критериям, поэтому соответствующие значения введены в три поля ввода. На Рисунок 1.1.4. показан пример ввода трех критериев поиска.
Рисунок 1.1.4. Пример ввода двух критериев поиска
1.2. Организация данных в порядке возрастания или убывания с помощью функции сортировки в MS Excel
Порядок сортировки, используемый по умолчанию
Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки – способ изменения относительного положения данных, основанный на значении или типе данных.
Данные можно сортировать:
– по алфавиту;
– по числу;
– по дате.
Порядок сортировки может быть возрастающим (от 1 до 9, от А до Z) или убывающим (от 9 до 1, от Z до А).
При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст «А100», то после сортировки эта ячейка будет находиться после ячейки, содержащей А1 и перед ячейкой, содержащей А11.
При сортировке по возрастанию в Microsoft Excel используется следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный за исключением пустых ячеек, которые всегда помещаются в конец списка): числа сортируются от наименьшего отрицательного до наибольшего положительного числа.
Текст, в том числе содержащий числа, сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 ' – (пробел)! « # $ % & () *,. /:;? @ [\] ^ _ ` {|} ~ + <=> A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я
Логическое значение ЛОЖЬ предшествует значению ИСТИНА. Все ошибочные значения равны. Пустые ячейки всегда помещаются в конец списка.
Порядок сортировки списков
При сортировке списка меняется порядок следования записей (строки таблицы). По полям записей (столбцы таблицы) сортировка не производится вообще.
Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки.
Совет. Перед сортировкой сохраните исходный файл под другим именем, используя команду «Файл», «Сохранить как». Это позволит в дальнейшем, если данные в результате сортировки случайно окажутся перемешанными, вернуться к исходному порядку данных.
Отмена сортировки
Если Вас не устраивают результаты сортировки, то сразу после сортировки выполните ее отмену любым из способов:
– нажмите кнопку «Отменить» на панели инструментов «Стандартная»;
– выполните команду «Правка», «Отменить сортировку»;
– закройте файл, в котором выполнялась сортировка, без сохранения.
Сортировка строк по возрастанию данных в одном столбце
Отсортируем список в файле «Сбор Зерновых» в алфавитном порядке по столбцу «Наименование региона».
1. Сохраните исходный файл «Сбор Зерновых» под другим именем, например «Сортировка Сбор Зерновых». Щелкните по любой ячейке в сортируемом списке.
2. В меню «Данные» выполните команду «Сортировка». Excel автоматически выделяет весь список и открывает диалоговое окно «Сортировка диапазона» (Рисунок 1.2.1.).
Рисунок 1.2.1. Диалоговое окно «Сортировка диапазона»
3. Обратите внимание на переключатель «Идентифицировать поля по…». Обычно автоматически выбирается правильное положение. Переключатель установлен в положении «подписям (первая строка диапазона)», если список содержит заголовки столбцов. Выберите положение «обозначениям столбцов листа», если заголовков в списке нет.
4. В раскрывающемся списке «Сортировать по» выберите первое поле, по которому следует упорядочить данные (первый ключ сортировки) – «Наименование региона».
Выберите переключатель «По возрастанию» (Рисунок 1.2.2.).
Нажмите кнопку ОК.
Рисунок 1.2.2. Результат заполнения полей диалогового окна «Сортировка диапазона»
Сортировка строк по двум или более столбцам
Отсортируем список в файле «Сбор Зерновых» по столбцам: сначала по столбцу «Наименование экономического района», а затем по столбцу «Пшеница 3 кл.». Для этого: сохраните исходный файл «Сбор Зерновых» под другим именем, например «Сортировка Сбор Зерновых», используя команду «Файл», «Сохранить как». Щелкните по любой ячейке в сортируемом списке.
В меню «Данные» выполните команду «Сортировка». Появится диалоговое окно «Сортировка диапазона». Укажите столбцы сортировки в полях:
«Сортировать по» – «Наименование экономического района»;
«Затем по» – «Пшеница 3 кл.».
Установите для первого и второго ключей сортировки положение
переключателя «По возрастанию».
Нажмите ОК.
Сортировка с помощью панели инструментов
Отсортируем исходный список из файла «Сбор Зерновых» по столбцу «Пшеница 3 кл.» в порядке возрастания.
На панели инструментов «Стандартная» имеются две кнопки, предназначенные для сортировки списка в возрастающем или убывающем порядке. Используя их, можно упорядочить данные по одному полю. Необходимо учитывать, что при такой сортировке используются последние значения параметров (порядок и направление сортировки, учет регистра), которые были установлены в диалоговом окне « Параметры сортировки».
Щелкните по любой ячейке в столбце, который следует использовать в качестве ключа сортировки. В данном задании в качестве ключа сортировки используется столбец «Посевная площадь».
Нажмите кнопку «По возрастанию» на панели инструментов «Стандартная».
Возврат к первоначальному порядку сортировки
Проиндексировав список перед сортировкой, пользователь получает дополнительную возможность для возвращения исходного порядка данных. Для этого щелкните по любой ячейке в столбце индекса и нажмите кнопку «По возрастанию» на панели инструментов «Стандартная».
1.3. Поиск данных с помощью фильтров
Фильтрация данных в Excel – это временное скрытие записей списка, не удовлетворяющих заданному критерию. В Excel существуют следующие средства для фильтрации данных: «Автофильтр», «Пользовательский Автофильтр», «Расширенный фильтр».
Использование «Автофильтра»
Для применения автофильтра выполните следующее.
– Щелкните по любой ячейке списка.
– В меню «Данные» выберите команду «Фильтр», «Автофильтр». В каждой ячейке с заголовками поля списка Excel поместит кнопки управления, с изображением черной стрелки. Таким образом, ячейки с названием полей превращаются в раскрывающиеся списки.
– Нажмите кнопку управления в том поле заголовка, которое следует включить в критерий. На экране появится список критериев.
Совет. Если на экране дисплея в раскрывающемся списке критериев вы не видите полный перечень критериев Автофильтра, то вы можете, например, уменьшить высоту строки, которая содержит заголовки.
Выберите желаемый критерий. Список критериев Автофильтра приведен в таблице 1.3.1.
Таблица 1.3.1.
Если пользователь выбирает критерий «Первые 10», либо «Условие», то следует в соответствующем диалоговом окне выбрать необходимую команду. Результаты работы Автофильтра моментально отображаются на экране. Обратите внимание: кнопки управления, которые участвовали в фильтрации данных, окрашены в голубой цвет.
Все записи, не отвечающие критерию фильтрации, окажутся скрытыми.
Если необходимо включить в текущий критерий фильтрации дополнительно другое поле, то повторите выполнение алгоритма.
Записи, находящиеся на экране (номера строк окрашены в голубой цвет) удовлетворяют условиям, которые были наложены на все поля, участвовавшие в фильтрации исходного списка.
Чтобы исключить из текущего критерия любое поле, для этого необходимо щелкнуть по соответствующей кнопке управления и из раскрывающегося списка выбрать критерий «Все» (частичное снятие критерия).
Чтобы снять критерий целиком, не выходя из режима Автофильтра, выполните команду «Данные», «Фильтр», «Отобразить все».
Чтобы отменить режим Автофильтра, выберите команду «Данные», «Фильтр», «Автофильтр».
Все задания по фильтрации исходного списка, записанного в файле «СборЗерновых», будем выполнять в файле «Фильтр СборЗерновых». Для этого сохраните исходный файл «СборЗерновых» под именем «Фильтр СборЗерновых», используя команду «Файл», «Сохранить как».
Задание 1. Из исходного списка выберите только те записи, которые относятся к Центральному экономическому району.
1. Щелкните по любой ячейке исходного списка.
2. Выполните команду «Данные», «Фильтр», «Автофильтр».
3. Нажмите кнопку управления (черная стрелка) в поле «Наименование экономического района».
4. Из раскрывающегося списка вариантов фильтрации (обратите внимание, что список всех экономических районов, встречающихся в поле «Наименование экономического района», записан в алфавитном порядке) выберите «Центральный».
5. Результаты работы Автофильтра представлены на Рисунок 1.3.1.
Рисунок 1.3.1. Раскрывающийся список вариантов фильтрации
Задание 2. Из списка, полученного в Задание 1, выберите только те области Центрального экономического района, у которых цены на пшеницу 3 класса не превышает 6200.
После выполнения пункта 5 Задания 1 нажмите кнопку управления в поле «Пшеница 3 кл.».
Из раскрывающегося списка вариантов фильтрации выберите «6200».
Результаты работы Автофильтра представлены на Рисунок 1.3.2.
Выполните команду «Данные», «Фильтр», «Отобразить все», чтобы вернуться к исходному списку.
Рисунок 1.3.2. Рабочий лист с результатами работы Автофильтра
Использование команды «Условие» или «Пользовательский Автофильтр»
Применение «Пользовательского Автофильтра» рассмотрим, на примере группировки исходного списка по посевной площади. Первый интервал группировки: менее 6200; второй от 6200 до 6400.
1. Из исходного списка выберите записи, имеющие посевную площадь менее 6200.
2. Убедитесь, что находитесь в режиме фильтрации исходного списка на Листе 1.
3. Нажмите кнопку управления в поле «Пшеница 3 кл.».
4. Из раскрывающегося списка вариантов фильтрации выберите «Условие» (Рисунок 1.3.3.)
Рисунок 1.3.3. Раскрывающийся список вариантов фильтрации
5. Открывается диалоговое окно «Пользовательский автофильтр».
6. Используя раскрывающийся список, выберите опцию «меньше» в левом верхнем углу диалогового окна, а в правом верхнем текстовом поле введите число 6200, либо из раскрывающегося списка значений критерия выберите число 6400, если оно является элементом этого списка (Рисунок 1.3.3.).
Рисунок 1.3.4. Рабочий лист с результатами работы Автофильтра
Результат заполнения диалогового окна «Пользовательский автофильтр» представлен на Рисунок 1.3.4.
