Работа со списком в excel как с базой данных

С
технической точки зрения списки
представляю собой обычные таблицы, для
которых в программе MS
Excel имеются дополнительные операции
обработки.

Списком
Excel называется таблица, оформление
которой отвечает следующим требованиям:

1)
список состоит из строк, называемых
записями;

2)
столбцы списка, называемые полями,
должны содержать однородную (однотипную)
информацию;

3)
верхняя строка списка, называемая
заголовком, должна содержать метки
(имена) соответствующих полей, и ее
формат (шрифт, цвет фона и т.п.) должен
отличаться от формата записей;

4)
внутри списка не должно быть пустых
строк и столбцов, которыми список
отделяется от остальной части рабочего
листа;

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

Первый,
наиболее важный шаг при создании базы
данных — это разработка хорошо продуманной
структуры (организации хранения данных),
которая и определяет возможности будущей
обработки информации. Структура списка
определяется структурой одинаково
организованных записей, его составляющих.

Под
структурой записи принято понимать
совокупность ее полей (их имена, типы,
назначение). Пусть, например, мы хотим
создать список, содержащий предметы,
которые входят в расписание Башкирского
института физической культуры.

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

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

Глава 2 работа со списками и базами данных в среде ms excel

2.1. Создание списков и базы данных

Список
или база данных рабочего листа – это
упорядоченный набор данных, обладающий
следующими свойствами:

—
данные располагаются в столбцах;

—
каждый столбец имеет однородный тип
данных;

—
каждый столбец имеет уникальное имя;

—
первая строка списка – строка заголовков
столбцов списка.

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

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

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

При
создании списка Excel процесс распознавания
и изменения содержимого списка можно
упростить за счет средств списка и
визуальных элементов, разработанных
для улучшения этих средств.

Рисунок
1-Список преподавателей.

Если
вы вводите постоянно одни и те же
значения, то раскрывающийся список
может здорово облегчить Вам жизнь.

Первый
и самый простой путь — это использовать
встроенную функцию. Если вы вводите
одни и те же значения в столбик и в
следующей пустой ячейке Вам нужно ввести
что то, что было введено выше то можно
использовать стандартную функцию Excel:
встать на ячейку и нажать на сочетание
клавиш (Alt
+стрелочка вниз) .Excel
автоматически сформирует, создаст и
развернет весь список элементов, которые
встречались выше по столбцу.

Минусы
этого способа очевидны: если Вы хотите
использовать что, то, что еще не вводилось
или хотите ввести данные в одну ячейку
ниже то раскрывающейся список нажимая
(Alt+стрелочка
вниз) нам не поможет.

Хотя
в простых случаях это хорошо работает.

Второй
способ заключается в использовании
инструмента «проверка данных», предположим
у нас есть отдельный Лист 1, Лист2, Лист3
и Лист4, в котором мы хотим сделать
открывающийся список с элементами из
предыдущих Лист1,Лист 2,Лист 3.Для начала
Лист1 нужно назвать, для этого нужно
навести курсор мыши на слово Лист внизу
и при помощи правой кнопки мыши выбираем
строчку «переименовать» в нашем примере
переименовываем по названием «научные
дисциплины». После ввода обязательно
нажать кнопку(Enter).Так
же мы переименовываем и оставшиеся
Листы. В нашем работе Лист 2 будет
«преподаватели», Лист 3 назовем «корпус,
аудитория», а Лист 4 в котором мы и будем
создавать выпадающий список «М21»

Так
же очень удобно использовать в складке
«формулы» так называемый «диспетчер
имен», который появился с начала 2007-ой
версии MS
Excel.

Рисунок
2-Диспетчер имен

Рисунок
3-Создание имени

Рисунок
4-Диспетчер имен (2)

«Диспетчер
имен» наглядно отображает список всех
использованных именованных диапазонов,
здесь же эти диапазоны можно
подкорректировать, удалить или создать
новые.

Теперь,
когда выделенному диапазону Лист
1,Лист2,Лист3,Лист4 присвоены имя можно
создать выпадающий список.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

1. Вручную через «точку-с-запятой» в поле «Источник».



2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.



3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».



2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:



3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.



2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.



4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub
 

5. Сохраняем, установив тип файла «с поддержкой макросов».



6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

1. Создадим первый выпадающий список, куда войдут названия диапазонов.



2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.



3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.



Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(0, 1)) = 0 Then
            Target.Offset(0, 1) = Target
        Else
            Target.End(xlToRight).Offset(0, 1) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
        Else
            Target.End(xlDown).Offset(1, 0) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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


Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
        If Len(oldval) <> 0 And oldval <> newVal Then
            Target = Target & "," & newVal
        Else
            Target = newVal
        End If
        If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub


Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).



2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.



3. Жмем «Свойства» – открывается перечень настроек.



4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

Скачать пример выпадающего списка

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

Раздел 4. Технологии создания и преобразования

информационных объектов

Тема 4.3. Возможности динамических (электронных)
таблиц

Лабораторная работа №20.  «Использование
списка Excel в качестве базы данных»

Цель  работы: освоить основные приемы  анализа списков
 в  табличном процессоре  Excel с использованием правил
сортировки и фильтрации данных.

Методические указания

Информационный материал:

1.    
Изучите теоретический
материал по теме: «Возможности динамических
(электронных) таблиц.»

2.    
Семакин И.Г., Хеннер Е.К.
Информатика. Учебник 10-11 Кл. – М., 2010.

3.    
Колмыкова, И. А. Кумскова Информатика: Учебное пособие для студ.
сред. проф. образования / Е.А.. – М.: Издательский центр «Академия», 2011.

Оборудование:

ПК, инструкционная карта, компьютер,
прикладная программа Microsoft Excel.

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

База данных – это информационная модель, позволяющая в
упорядоченном виде хранить данные о группе объектов, обладающих одинаковым
набором свойств.

В Microsoft Excel в качестве базы данных можно
использовать список.

Список — это способ представления
данных, при котором данные в таблице взаимосвязаны и структура таблицы
определяется заранее.

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

Если  список (таблица)  Excel считается
базой данных, то:

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

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

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

Все действия со списками (базой данных) выполняет команда главного меню
Данные.(Рисунок 1)

Правила создания
списков в EXCEL:

·      
Строка заголовков столбцов
(верхняя строка списка) должна быть заполнена именами полей;

·      
Каждая запись должна
размещаться в отдельной строке;

·      
Первую запись необходимо
разместить в строке, следующей непосредственно за строкой заголовков;

·      
Следует избегать пустых
строк между записями;

·      
В столбце под заголовкам
содержатся однотипные данные

Для упорядочения и анализа данных в списке применяется
команда «Сортировка». «Сортировка» позволяет переставлять
записи в определенном порядке на основании значений одного или нескольких
столбцов или переставлять столбцы на основании значений  стр.

Сортировка – это расстановка элементов в заданном
порядке

Существуют три типа сортировки:

·      
в возрастающем порядке

·      
в убывающем порядке

·      
в пользовательском порядке
(многоуровневая сортировка)

  
    Сортировка осуществляется в Excel  на
вкладке  «Данные»((Рисунок 2)..
 

Сортировка списка по возрастанию означает упорядочение списка в
порядке: от 0 до 9, пробелы, символы, буквы от А до Z или от А до Я, а по
убыванию — в обратном порядке.Пользовательский порядок сортировки задается
пользователем в окне диалога «Сортировка»  на вкладке
«Данные «.

Многоуровневая
сортировка — это пользовательская сортировка сразу по
нескольким столбцам.

Например:   Расставить фамилии по алфавиту, а людей с
одинаковыми фамилиями расставить в алфавитном порядке (Рисунок 3,4)

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

В
отличие от сортировки  данные
при фильтрации не переупорядочиваются, а лишь скрываются те
записи, которые не отвечают заданным критериям выборки. Режим фильтрации
находится в Excel на вкладке Данные -Фильтр  (Рисунок
5).

Фильтрация данных
может выполняться двумя способами: с помощью обычного фильтра
(автофильтра) или расширенного фильтра.

·      
Автофильтр используется для фильтрации по простым
критериям.

·      
Расширенный фильтр применяется для фильтрации по более
сложным критериям.

В столбцах списка
появятся кнопки со стрелочками, нажав на которые можно настроить параметры
фильтра. Поля, по которым установлен фильтр, отображаются со значком воронки.
Если подвести указатель мыши к такой воронке, то будет показано условие
фильтрации (Рисунок 6,7 ).

		Рисунок 7.  Результат фильтрации

Задание 

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

Технология
работы:

Задание 1.
 Перенесите таблицу в Excel и расставьте список по алфавиту.

Рассчитайте
 максимальную глубину.

 Для  выполнения работы воспользуйтесь меню Данные-Сортировка.

Задание 2.  Перенести
таблицу в Excel, расставить список по алфавиту.

Оформить  шапку таблицы. Список студентов пересортируйте в
алфавитном порядке по фамилиям студентов и городу,

Выделите
столбцы с данными Фамилия и Город ( с  заголовками столбцов ),  Выберите кнопку
Данные- Сортировка –Настраиваемая сортировка (Рисунок 8):

Добавьте
два уровня сортировки (Рисунок 9) по фамилии и городу.

Задание 3    Перенесите
базу по автомобилям в Excel.

Используя 
автофильтр  найдите:

Все записи об
автомобиле ВАЗ 2109 (только эта модель). Результат скопировать на пустой лист и
упорядочить.

Выделите заголовки
столбцов и нажмите  кнопку Данные-Фильтр.

Выберите Настраиваемый
фильтр – Пользовательские настройки,

 Выберите
пользовательский автофильтр и установите параметры, согласно условию задачи.
(Рисунок 10)

Задание 4.   Выполните самостоятельно:    

·      
Найдите все записи об
автомобиле ВАЗ 2109 (все модификации). Результат скопировать на пустой лист и
упорядочить.

·      
Извлеките из базы данных
все записи об автомобилях с ценой менее 5000$

·      
Результат вместе с
заголовками столбцов скопировать на пустой лист  и упорядочить по полям Модель
(по возрастанию), Цена (по убыванию).

3. Изучите методику выполнения
работы и запишите основные определения

4.  Ответьте
на вопросы в письменном виде.

1.  
В каком случае список Excel
 может считается базой данных?

2.  
Каковы правила создания
списков?  

      3. Какие инструменты 
в Excel используются для анализа данных?

      4. Что такое
сортировка?

      5. Какие типы
сортировки бывают

      6. Чем
сортировка отличается от фильтрации?

      7.  Какая
бывает фильтрация и как осуществляется фильтрация в Excel?

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Понятие о списке (базе данных Excel)

Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel (рис. 5.1
рис.
5.1) и используют соответствующую терминологию:

• строка списка – запись базы данных;
• столбец списка – поле базы данных.

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

Внимание!

1. Список содержит фиксированное количество полей (столбцов), определяющих структуру записи базы данных (строки).
2. Верхняя строка списка содержит имена полей (названия столбцов).
3. Имя поля может состоять из нескольких слов любого алфавита. Обязательное требование – размещение в одной ячейке.

Список (база данных Excel) – электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.

Для размещения имени поля списка в одной ячейке (рис.5.1
рис.
5.1) необходимо:

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

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

Для того чтобы электронная таблица воспринималась системой как список, необходимо соблюдать описанные выше правила и перед выполнением операций обработки установить курсор внутри этой таблицы. В этом случае при вводе команды обработки из меню Данные весь список (имена полей и записи, см. рис.5.1) будет выделены темным цветом. Когда список сформирован неверно или нужно работать с частью области списка, область списка надо выделить вручную с помощью мыши.

Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом. Данные в электронную таблицу можно включить не только путем импорта из «чужой» базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query, вызываемой по команде Данные, Внешние данные. Создать запрос. Результат запроса возвращается в электронную таблицу в виде списка.

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

Сортировка данных является базовой операцией любой таблицы и выполняется командой Данные, Сортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных. Сортировка осуществляется на том же листе.

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

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона (рис. 5.2
рис.
5.2, а) параметром Сортировать по.

Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.

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

При наличии заголовков столбцов (имен полей) их следует исключить из области, подлежащей сортировке, установкой флажка переключателя Идентифицировать поля по подписям.

Кнопка <Параметры> выводит диалоговое окно «Параметры сортировки», в котором задаются дополнительные установки сортировки (рис. 5.2 б
рис.
5.2): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки – обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощью Сервис, Параметры, вкладка Списки.

Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные > Фильтр.

Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные> Форма.

Автофильтрация

Команда Данные, Фильтр, Автофильтр для каждого столбца строит список значений, который используется для задания условий фильтрации (рис. 5.3
рис.
5.3). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

• все – выбираются все записи без ограничений;
• первые 10 – данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 5.4
  рис.
  5.4) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
• значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;
• условие – выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 5.5
  рис.
  5.5).

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

Каждая часть условия включает:

• оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
• значение, которое может выбираться из списка или содержать шаблонные символы *,?.

Пример. Для Кода предмета можно сформировать условия:

• >=п* – отобрать все записи, которые содержат код предмета, начинающийся с буквы п;
• >= п1 И <=п2 – отобрать все записи, которые содержат коды предметов п1 и п2;
• <>п1 – отобрать все записи, которые не содержат кода предмета п1.

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

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

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

• критерий сравнения;
• вычисляемый критерий.

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

Важной особенностью этого режима является необходимость формирования заранее, до выполнения самой команды фильтрации, специального блока (области) для задания сложных поисковых условий, называемых областью критерия (диапазоном условия).

Технология использования расширенного фильтра состоит из двух этапов:

• этап 1 – формирование области критериев поиска;
• этап 2 – фильтрация записей списка.

Этап 1. Формирование диапазона условий для расширенного фильтра. Область критериев поиска содержит строку имен столбцов и произвольное число строк для задания поисковых условий.

Рекомендуется скопировать первую строку с именами полей из области списка в область, где будет формироваться критерий отбора записей (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из диапазона условий можно удалить.

Критерий сравнения формируется при соблюдении следующих требований:

• состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;
• имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;
• ниже имен столбцов располагаются критерии сравнения типа:
• точного значения;
• значения, формируемого с помощью операторов отношения;
• шаблона значения, включающего символы * и (или) ?.

Правила формирования множественного критерия:

1. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И .
2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример1. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:

1-й способ. Множественный критерий сравнения- все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка- оператор сравнения со значением константы.

2-й способ. Множественный критерий сравнения – все условия (точные значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения – условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ.

Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (числа, текст, дата, логическая константа), операторов отношения.

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример2. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

В области критериев присутствуют столбцы с заголовками: Номер группы, Оценка1.

Последовательность действий по созданию данного вычисляемого критерия:

• присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
• установить курсор в ячейку ввода;
• вызвать Мастер функций — команда Вставка, Функция, выбор категории — Логические и выбор функции — ИЛИ;
• ввод параметров функции ИЛИ:

Логическое1: G2<=CP3HAЧ($G$2:$G$17) (при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши <F4>)

Логическое2: G2=5

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

либо в комбинированном виде:

Этап 2. Фильтрация записей расширенным фильтром

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 5.
рис.
5.6).

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

Исходный диапазон и диапазон условий включают все строки, в том числе и строку наименования столбцов. Если предполагается копирование результата в другое место, указывается левая верхняя ячейка области. Переключатель Только уникальные записи позволяет исключить дублирование записей.

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

Для снятия действия условий фильтрации выполняется команда Данные, Фильтр, Отобразить все.

Фильтрация с помощью формы данных

Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 5.7
рис.
5.7).

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

Добавление кнопки «Форма» на панель быстрого доступа

Щелкните стрелку, расположенную рядом с панелью быстрого доступа, и выберите пункт Другие команды.

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

В списке выберите кнопку Форма Кнопка «Форма» и нажмите кнопку Добавить.

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей – названия столбцов списка.

Для просмотра записей используется полоса прокрутки либо кнопки <Назад> или <Далее>, выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки <Вернуть> можно отменить.

Для создания новой записи нажимается кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши, либо клавиша <Таb>.

При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>. Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка <Критерии>, форма очищается для ввода условий поиска в полях формы с помощью кнопки <0чистить>, а название кнопки <Kритерии> заменяется на название <Правка>. После ввода критериев сравнения нажимаются кнопки <Назад> или <Далее> для просмотра отфильтрованных записей в нужном направлении. При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка <Правка>, для выхода из формы – кнопка <3акрыть>.

ЗАДАНИЕ

1. Создать в Excel cписок согласно варианту задания (не менее 10 записей), в правом столбце ввести формулы, содержащие ссылки на ячейки для расчетов соответствующих показателей.
2. Отсортировать данные списка по трём полям (поля выбираются самостоятельно).
3. Выполнить фильтрацию данных списка тремя способами:
   • с помощью автофильтра,
   • с помощью расширенного фильтра по критерию сравнения,
   • с помощью расширенного фильтра по вычисляемому критерию, который задать самостоятельно, (см. пример 2).

Варианты задания (№ варианта – №компьютера в аудитории)

1 вариант.

2 вариант.

3 вариант.

4 вариант.

5 вариант.

6 вариант.

7 вариант.

8 вариант.

9 вариант.

10 вариант.

11 вариант.

12 вариант.

13 вариант.

14 вариант.

15 вариант.

16 вариант.

17 вариант.

18 вариант.

19 вариант.

20 вариант.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что называют списками (базами данных) Excel?
2. Назовите структурные элементы списка Excel.
3. Какие основные правила необходимо соблюдать при создании списка?
4. Как исключить заголовки полей из области сортировки?
5. Какие уровни сортировки предусмотрены в программе Excel, и как осуществляется сортировка данных в списке?
6. Что такое множественный критерий, назовите правила формирования множественного критерия?
7. Как формируется диапазон условий для расширенного фильтра?
8. Объясните, что понимают под вычисляемым критерием, и какие основные правила используются при формировании вычисляемого критерия?
9. Как с помощью экранной формы задаются критерии сравнения при отборе данных?

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше