Статистические, финансовые, логические функции. Сортировка, модификация БД, организация простейших запросов. Автофильтр, Расширенный фильтр. Подведение промежуточных итогов

 

Цель работы - Привить у студентов навыки создания и обработки баз данных в Microsoft Excel.

Краткие теоретические сведения

 

База данных - это средство, использующееся для хранения, организации и поиска информациии. (Например, обычная телефонная книга - это тоже база данных). В современных «компьютерных» базах данных информация обычно содержится во многих таблицах, определенным образом связанных между собой. Таблицы в таких базах данных состоят из записей(строк) и полей(столбцов). Простые базы данных, состоящие из одной таблицы, можно создавать средствами Excel.

Использоване списка в качестве базы данных

Списком называется набор строк таблицы, содержащий связанные данные -например, перечень телефонов клиентов. В Microsoft Excel в качестве базы данных можно использовать список. При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных.

Столбцы списков становятся полями базы данных.

Заголовки столбцов становятся именами полей базы данных.

Каждая строка списка преобразуется в запись данных.

Требования к базе данных (списку) на листе Excel.

В Microsoft Excel имеется набор функций, облегчающих обработку и анализ данных в списке. При этом список должен отвечать следующим требованиям:

На листе не следует помещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно.

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

Заголовки столбцов должны находиться в первой строке списка. Они используются при подведении итогов, поиске и сортировке данных.

Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.

Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.

Не следует помещать пустую строку между заголовками и первой строкой данных.

Желательно также придерживаться следующих советов:

Формат заголовков столбцов должен отличаться от формата, присвоенного строкам данных.

Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

Сортировка данных в списке.

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст " A100", то после сортировки она будет находиться после ячейки, содержащей " A1" и перед ячейкой, содержащей " A11."

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» — следующими и «Высокий» — последними. (При помощи Сервис, Параметры, Списки можно создать собственный порядок сортировки).

Для сортировки списков в одном столбце следует использовать кнопки «По возрастанию» и «По убыванию».

Для сортировки списка по двум или более столбцам необходимо:

1 Указать любую ячейку в сортируемом списке.

2 Выбрать Данные, Сортировка.

3 Указать столбцы сортировки в полях Сортировать по и Затем по.

Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям «Отдел», «Должность», «Фамилия», «Имя» и «Отчество», выберите сначала «Имя» в поле Сортировать по, «Отчество» в поле Затем по и отсортируйте список. Затем выберите «Отдел» в поле Сортировать по, «Должность» в поле Затем по, «Фамилия» в поле В последнюю очередь, по и отсортируйте список.

4 Выбрать другие параметры сортировки и нажать кнопку OK.

5 Повторить шаги 2 - 4 для следующих более значимых столбцов.

Автофильтр. Для применения автофильтра необходимо:

Щелкнуть любую ячейку внутри списка.

Выбрать Данные, Фильтр, Автофильтр. В ячейках с именами полей списка должны появиться кнопки со стрелками вниз.

Чтобы отфильтровать строки, содержащие определенное значение, надо нажать кнопку со стрелкой в столбце, в котором содержатся искомые данные.

Выбрать значение в списке.

Повторить шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах.

Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения, надо нажать кнопку со стрелкой, а затем выбрать пункт Условие. После этого на экране появится диалоговое окно «Пользовательский автофильтр». В этом окне необходимо будет сделать следующее:

В верхнем левом поле ввода щелкнуть кнопку со стрелкой вниз и выбрать один из следующих операторов сравнения: «равно», «не равно», «больше», «больше или равно», «меньше», «меньше или равно», «начинается с», «не начинается с», «заканчивается на», «не заканчивается», «содержит», «не содержит».

В правом верхнем поле ввода ввести соответствующее значение.

Если необходимо отобрать строки, удовлетворяющие одновременно двум условиям отбора, то надо щелкнуть флажок «И», а затем сформировать второе условие в нижней части окна.

Если необходимо отобрать строки, удовлетворяющие одному из двух условий отбора, то надо щелкнуть флажок «ИЛИ», а затем сформировать второе условие в нижней части окна.

Расширенный фильтр.

Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. Удобно также иметь не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора.

1 Скопируйте из списка заголовки фильтруемых столбцов.

2 Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.

3 Введите в строки под заголовками условий требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка.

4 Укажите ячейку в списке.

5 Выберите пункт Фильтр в меню Данные, а затем — команду Расширенный фильтр.

6 Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение Скопировать результаты в другое место, перейдите в поле Поместить результат в диапазон, а затем укажите верхнюю левую ячейку области вставки.

7 Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, нажмите кнопку свертывания диалогового окна: .

Совет. Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон

Примеры условий отбора расширенного фильтра

В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение.

⇐ Предыдущая12345678910Следующая ⇒

Поделиться: