МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ РАБОТЫ
С помощью MS Excel можно создавать и обрабатывать базы данных (списки).Список - это совокупность строк листа, содержащих однотипные данные. Список может использоваться в качестве базы данных, в которой записи соответствуют строкам списка, а поля - столбцам. Например, если базой данных считать телефонный справочник, то полями записи будут фамилии, номера телефонов и адреса абонентов.
База данных (список) MS Excel
Для использования в Microsoft Excel списка в качестве базы данных не требуется каких-либо подготовительных действий. При выполнении характерных для баз данных операций, таких как поиск, сортировка или подведение итогов, список автоматически обрабатывается как база данных.
MS Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям:
1. Список обязательно должен содержать строку заголовков.
2. В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
3. В списке не должно быть пустых строк.
4. Рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой или одним пустым столбцом. В противном случае можно получить, например, сотрудника с фамилией Итого.
На рабочем листе выделяют следующие области:
1. Диапазон данных – область, где хранятся данные списка. Данные, связанные друг с другом, записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля.
2. Диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации. В диапазоне критериев указываются имена полей и отводится область для записи условий отбора.
3. Диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список.
Основные приемы работы со списком
MS Excel обладает мощными средствами для работы со списками:
1. Пополнение списка с помощью формы.
2. Сортировка списка.
3. Фильтрация списка.
4. Подведение промежуточных итогов.
5. Создание итоговой сводной таблицы на основе данных списка.
Для того чтобы воспользоваться любым из этих инструментов, нужно установить курсор на одну из ячеек списка.
Правила ведения списка
Размер и расположение списка
· Не создавать более одного списка на листе, поскольку некоторые средства, такие как фильтрация, обрабатывают один текущий список.
· Оставлять, по крайней мере, один пустой столбец и одну пустую строку между списком и другими данными листа. Это необходимо для автоматической идентификации списка при сортировке, фильтрации и подведении итогов.
· Не размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.
Заголовки столбцов
· Создавать заголовки столбцов в первой строке списка. Далее они будут использоваться в отчетах и для поиска и организации данных.
· Чтобы выделить заголовки столбцов необходимо использовать средства форматирования.
Содержимое строк и столбцов
· Все ячейки столбца должны содержать однотипные данные.
· Не вводить пробелы перед содержимым ячейки, поскольку они влияют на порядок сортировки и результаты выполнения операции поиска.
· Использовать один формат для всех ячеек столбца.
Сортировка списков
Изменение порядка строк в списке называется сортировкой. Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки в процессе сортировки переупорядочиваются в соответствии с заданным порядком сортировки.
Чтобы отсортировать список по одному полю необходимо:
1. Установить курсор в тот столбец таблицы, по которому нужно выполнить сортировку.
2. Нажать кнопку Сортировка по возрастанию 
или Сортировка по убыванию 
на панели инструментов Стандартная
Если необходимо выполнить сортировку по двум или нескольким столбцам:
1. Выполнить команду Данные – Сортировка.
2. В появившемся диалоговом окне Сортировка диапазона(рис. 2) в поле Сортировать по нужно выбрать название столбца и указать порядок сортировки.
3. То же самое необходимо выполнить для второго и третьего полей.
4. Нажать кнопку ОК.
Задание параметров сортировки
диапазона по нескольким полям
Результат выполнения сортировки по полям, может выглядеть так, как показано на рис.
Результат сортировки по нескольким полям
Чтобы задать пользовательский порядок сортировки необходимо:
1. Выполнить команду Сервис – Параметры – вкладка Списки. В поле Элементы списка нужно ввести точное наименование типа товаров. Например, необходимо упорядочить информацию по месяцам в следующем порядке: Январь, Февраль, Март.
2. Нажать кнопку Добавить. Элементы списка будут включены в окно Списки.
Если нужно будет удалить ошибочные списки, то выделите их и нажмите на кнопку Удалить.
Задание элементов списка для организации пользовательского порядка сортировки
3. Выполнить команду Данные – Сортировка. В окне Сортировка диапазона нажмите кнопку Параметры…. Раскрыть список окна Сортировка по первому ключуи выбрать нужную последовательность (рис. 5).
Установка параметров сортировки для
организации пользовательского порядка сортировки
4. Флажок Учитывать регистр можно не устанавливать (рис. 6). В этом случае сортировка будет менее строгая по отношению к тексту.
Учет регистра при организации
пользовательского порядка сортировки
Результат выполнения пользовательской сортировки, указанной на рис. может выглядеть так, как показанониже.
Результат пользовательской сортировки
Фильтрация списков
Фильтруя список, можно отображать только те строки, которые удовлетворяют заданным условиям отбора. Например, список покупок, сделанных покупателями, можно отфильтровать так, что на экран будут выведены имена только тех покупателей, которые совершили покупки больше чем на 500 рублей. В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр.
Автофильтр
В автофильтре условия отбора задаются с помощью кнопок со стрелками, расположенными в заголовках столбцов списка. Применять команду Автофильтр необходимо для того, чтобы быстро отфильтровать данные с одним или двумя условиями, накладываемыми на ячейки отдельного столбца.
Правила фильтрации с помощью Автофильтра:
1. Указать любую ячейку в фильтруемом списке.
2. В пункте меню Данные выберите пункт Фильтр, затем команду Автофильтр.При этом в строке с именами столбцов устанавливаются кнопки скрытых списков 
, содержащие команды и список значений данного поля. С помощью этого меню можно отобрать записи с заданным значением поля.
3. Чтобы отфильтровать список по одному из значений, встречающихся в столбце, выберите требуемое значение из развернувшегося набора.
Меню Автофильтра
4. Чтобы отфильтровать список по двум или более значениям, встречающимся в столбце, или с использованием операторов сравнения, необходимо выбрать строку Условие... из развернувшегося набора.
В диалоговом окне Пользовательский автофильтр можно, указывая, операторы больше, меньше, равно и т.д. ввести два критерия на значения. Если эти условия должны выполняться одновременно, то в диалоговом окне выбирается переключатель И. если хотя бы одно из условий, то ИЛИ.
Если автофильтр применяется к текстовому полю, то в качестве логической функции, связывающей условия, всегда нужно выбирать ИЛИ. Для полей числового типа или дат необходимо использовать следующее:
1. Меньше И больше, когда интересует область между двумя числами или датами.
2. Больше ИЛИ меньше в том случае, если интересует область вне интервала, заданного двумя числами или датами.
Диалоговое окно Пользовательский автофильтр
5. Используя автофильтр, можно также выбрать несколько наибольших или наименьших значений. Для этого следует в меню Автофильтра выбрать пункт Первые 10…. В диалоговом окне Наложение условия по списку в первом поле со счетчиком необходимо выбрать количество записей, а в поле справа выбрать – наибольших или наименьших.
Диалоговое окно Наложение условия по списку
Удаление автофильтра включает в себя:
1. Отмена фильтрации столбца:
· Нажать в столбце кнопку со стрелкой , затем выбрать из развернувшегося списка команду Все.
2. Отмена фильтрации списка:
· В пункте менюДанные выбрать пункт Фильтр, затем команду Показать все.
3. Удаление автофильтра из списка:
· В пункте меню Данные выбрать пунктФильтр, затем командуАвтофильтр.
Пример 1. В исходной базе данных, используя Автофильтр, показать записи:
1) о товаре за февраль месяц.
2) о проданном товаре в январе в количестве от 10 до 42 шт.
Для выполнения задания необходимо установить Автофильтр на таблицу с помощью команды Данные – Фильтр – Автофильтр.
Чтобы отобрать информацию о товаре за февраль месяц необходимо в меню автофильтра выбрать требуемое значение: Февраль.
Пример использования Автофильтра
Результат выполнения фильтрации может выглядеть как показано на рис.
Результат фильтрации с использованием
меню Автофильтра
Чтобы отобрать информацию о проданном товаре в январе в количестве от 10 до 42 шт. необходимо провести фильтрацию в два этапа. Первый этап включает в себя отбор информации о проданном товаре в январе месяце, результат которого представлен на рис.
Результат отбора информации
о проданном товаре в январе
На втором этапе в меню автофильтра поля Продано выбрать пункт Условие…
Пример использования пользовательского
автофильтра
В диалоговом окне Пользовательский автофильтр необходимо задать условие: от 10 до 42 шт.
Пример задания условий в пользовательском
автофильтре
Результат выполнения отбора с использованием пользовательского автофильтра представлен на рис.
Результат использования пользовательского
Автофильтра
Расширенный фильтр
В расширенном фильтре условия отбора вводятся в диапазон условий на листе книги. Применять команду Расширенный фильтрнужно, чтобы отфильтровать данные со сложным критерием, например, вычисляемым условием либо с тремя или более условиями, накладываемыми на ячейки отдельного столбца.
Правила фильтрации с помощью расширенного фильтра:
1. Вставить несколько строк выше списка.
2. В пустую строку ввести или скопировать заголовки фильтруемых столбцов, на которые будут наложены условия.
3. В нижележащие строки ввести условия отбора. Между диапазоном условий и фильтруемым списком следует оставить, по крайней мере, одну пустую строку.
4. Ниже списка, отступив строку необходимо скопировать имена столбцов, которые нужно вывести.
5. Указать ячейку в фильтруемом списке.
6. В пункте меню Данные выбрать пункт Фильтр, затем команду Расширенныйфильтр.
7. В диалоговом окне установите переключатель Обработка в положение Фильтроватьсписокнаместе, чтобы скрыть ненужные строки.
Диалоговое окно Расширенный фильтр
с обработкой Фильтровать список на месте
Чтобы результат фильтрации поместить в другое место, необходимо в диалоговом окне Расширенный фильтр выбрать Скопировать результат в другое место, указать поле Поместить результат в диапазон, затем верхнюю левую ячейку области вставки для вывода всех полей списка.
Диалоговое окно Расширенный фильтр
с обработкой Скопировать результат в другое место
Если вывести нужно только некоторые поля списка, необходимо указать имена полей для вывода, приготовленные ранее (пункт 4).
8. Ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.
Условия отбора расширенного фильтра:
Чтобы наложить условия отбора, выполняемые одновременно на несколько столбцов, необходимо ввести условия в ячейки, расположенные в одной строке диапазона условий.
Если ввести условия в ячейки, расположенные в разных строках диапазона условий, то отобранные записи будут удовлетворять хотя бы одному из них.
Чтобы задать для отдельного столбца три или более условия отбора, необходимо ввести их в ячейки, расположенные в смежных строках.
В условии фильтрации можно использовать возвращаемое формулой значение. При создании условия отбора с помощью формулы нужно оставить пустой ячейку заголовка столбца условий.
Формула, используемая для создания условия отбора, должна использовать относительные ссылки на соответствующие поля первой записи списка. Все остальные ссылки в формуле должны быть абсолютными. Например, условие отбора =F7 > СРЗНАЧ($E$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
Правила ввода условий отбора:
1. Необходимо использовать операторы сравнения, числа, текст и шаблоны как при настройке автофильтра.
2. Чтобы отобрать строки, содержащие ячейки с заданным значением, необходимо ввести требуемые число, дату, текстовую или логическую константу в ячейку, ниже заголовка столбца диапазона условий. Например, чтобы отобрать строки, имеющие индекс отделения связи равный 109462, следует ввести в диапазон условий число 109462 ниже заголовка «Отделение связи».
| Отделение связи |
Создание критерия выборки
в расширенном фильтре (одно условие)
3. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения необходимо ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения Количество больше 1000 необходимо ввести условие отбора >1000 ниже заголовка Количество, имеющие Цену меньше 40, введите <40 ниже заголовка Цена.
| F | G | H |
| Количество | Цена | |
| >1000 | <40 |
Создание критерия выборки в расширенном
фильтре (два условия)
Таким образом, поиск с помощью расширенного фильтра предполагает следующее:
1. Подготовить диапазон критериев для расширенного фильтра:
– первая строка должна содержать заголовки полей, по которым будет производиться отбор (точное соответствие заголовкам полей списка).
– условия критерия записываются в пустые строки под подготовительной строкой заголовка.
2. Поместить указатель в список (или выделить весь список).
3. Выполнить команду Данные – Фильтр – Расширенный фильтр.
4. В диалоговом окне Расширенный фильтрзадать необходимые параметры.
5. Нажать на кнопку ОК.
Пример 2. В исходной базе данных, используя Расширенный фильтр, показать записи о проданном товаре в январе в количестве от 10 до 42 шт.
Прежде чем воспользоваться расширенным фильтром необходимо подготовить диапазон критериев (условие отбора), как показано на рис.
Подготовка условий отбора
Далее необходимо выполнить команду Данные – Фильтр – Расширенный фильтр. В результате появится диалоговое окно Расширенный фильтр, в котором необходимо указать параметры: Обработка, Исходный диапазон, Диапазон условий, Поместить результат в диапазон.
Пример использования
расширенного фильтра
Результат выполнения отбора с использованием расширенного фильтра представлен на рис.
Результат выполнения расширенного фильтра
Анализ данных
MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся:
· Обработка списка с помощью различных формул и функций.
· Построение диаграмм и использование карт MS Excel.
· Проверка данных рабочих листов и рабочих книг на наличие ошибок.
· Структуризация рабочих листов.
· Автоматическое подведение итогов.
· Консолидация данных.
· Сводные таблицы.
Специальные средства анализа выборочных записей и данных – подбор параметра, поиск решения и др.