880

Работа со списками (базами данных) в Excel

Практическая работа

Информатика, кибернетика и программирование

Правила формирования списка. Использование формы данных. Поиск и фильтрация данных. Использование Автофильтра. Вывод на экран записей, данные в которых в этом поле совпадают с выбранным значением. Использование Расширенного фильтра.

Русский

2013-01-06

181 KB

198 чел.

Работа со списками (базами данных) в Excel

  1.  Создание списков

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

Итоги сессии

Факультет

Группа

Фамилия

Год рождения

Средний балл

Стипендия

ФАИС

ПЭ-42

Котин

1987

7,3

98360

ЭФ

Э-11

Короткевич

1990

6,2

89410

ГЭФ

ОП-52

Ласица

1986

9,3

134160

ГЭФ

УП-11

Курачёва

1990

9,1

134160

ФАИС

ПЭ-52

Зайцев

1986

4,9

 

ФАИС

ПЭ-42

Кондраченко

1987

6,4

98360

………..

……….

………………..

……..

……..

……….

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

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

Правила формирования списка:

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

Создание списка.

Первый способ: ввод данных в ячейки таблицы.

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

Второй способ: Использование формы данных.

  •  создать строку заголовков;
    •  выделить любую ячейку строки заголовков;
    •  выполнить команду Данные – Форма. Появится ДО.
    •  заполнить соответствующие поля и щелкнуть по кнопке Добавить для перехода к следующей записи или по кнопке Готово для окончания ввода.

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

  1.  Сортировка списков

Сортировка – это упорядочение списка по значениям одного или нескольких полей.

При сортировке текст упорядочивается по алфавиту от А до Я (по возрастанию) или от Я до А (по убыванию). Числовые поля сортируются в порядке возрастания или убывания значений, даты в хронологическом порядке (от ранних к более поздним) или наоборот.

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

Пример: при сортировке по полям Факультет – Группа – Ср. балл – Фамилия: сначала Ср.балл  – Фамилия, затем Факультет – Группа.

Выполнение сортировки:

  •  выделить любую ячейку списка;
  •  выполнить команду Данные – Сортировка;
  •  указать ключи и порядок сортировки в ДО;
  •  нажать ОК.

Кнопка Параметры дает возможность различать прописные и строчные буквы; выполнять сортировку столбцов; сортировать данные в соответствии с некоторыми данными (Пон – Вт – Ср …).

При выполнении сортировки по одному ключу можно использовать кнопки на ПИ   

    

. Для этого нужно выделить любую ячейку столбца с ключевым полем (не весь столбец!) и щелкнуть по соответствующей кнопке.

  1.  Поиск и фильтрация данных

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

Пример: Найти всех студентов факультета ГЭФ, имеющих ср. балл не менее 7.

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

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

3.1. Использование Автофильтра

При фильтрации записей с помощью Автофильтра необходимо:

  •  выделить любую ячейку списка;
  •  выполнить команду Данные – Фильтр – Автофильтр;

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

Все –

Вывод на экран всех записей (снятие фильтра с этого поля)

Первые 10 –

Вывод на экран заданного числа или % записей с минимальным или максимальным значением поля

Пустые –

Вывод на экран записей с пустым полем

Непустые –

Вывод на экран записей, содержащих в поле какие-либо данные

Точное значение поля  –

Вывод на экран записей, данные в которых в этом поле совпадают с выбранным значением

Условие –

Вывод на экран записей, данные в которых в этом поле удовлетворяют заданному условию

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

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

  •  студенты ЭФ со средним баллом от 6 до 8;
  •  студенты 1989 и  1990 года рождения, не получающие стипендию;
  •  3 лучших студента ГЭФ.

При использовании Автофильтра условия поиска (критерии) в одном поле могут быть связаны логическими операциями "И" и "ИЛИ",  а для разных полей только операцией "И" (для "ИЛИ" используется расширенный фильтр). Все отличники ГЭФ можно, а отличники всех факультетов и все студенты ГЭФ – нельзя.

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

Для снятия фильтра:

  •  по данному полю:

выбрать режим Все в данном поле;

  •  по всем полям:

Данные – Фильтр – Отобразить все;

  •  отмена Автофильтра:

Данные – Фильтр –  Автофильтр.

3.2. Использование Расширенного фильтра

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

  •  сразу поместить результат в новое место вне списка;
  •  задать больше двух условий для одного поля;
  •  соединить условия в разных полях операцией ИЛИ;
  •  использовать формулы со ссылками на ячейки при записи условий.

Р.ф. позволяет использовать два типа критериев:

  •  критерий сравнения;
  •  вычисляемый критерий (использование формул).

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

Применение р.ф. включает два этапа:

  1.  Формирование диапазона условий (создание дополнительной таблицы).
  2.  Фильтрация записей списка.

  1.  Формирование диапазона условий.

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

Правила формирования диапазона условий критерия сравнения:

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

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

  •  все условия, записанные в столбцах одной строки, соединяются логической операцией И (как в Автофильтре);
  •  все условия, записанные в разных строках, соединяются логической операцией ИЛИ (нельзя в Автофильтре).

Примеры формирования диапазона условий

1. Результат помещается в другое место

Отличники ГЭФ и ФАИС

Факультет

Средний балл

ГЭФ

>=9

ФАИС

>=9

2. Условия связаны операцией ИЛИ

Студенты 1 курса и

все студенты, не получающие стипендию

Группа

Стипендия

*1?

 

шаблон с символами * и ?

 

0

точное значение

3. Допустимы более двух значений поля

Студенты МТ-32, ПЭ-42, ПЭ-52 со стипендиями от 90000 до 100000

Группа

Стипендия

Стипендия

МТ-32

>=90000

<=100000

Простое  сравнение

ПЭ-42

>=90000

<=100000

с одной операцией

ПЭ-52

>=90000

<=100000

отношения

  1.  Фильтрация записей.

  •  Выделить любую ячейку списка.
  •  Выполнить команду Данные – Фильтр – Расширенный фильтр, появится ДО Расширенный фильтр.

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

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

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

Отмена Расширенного фильтра:

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

  1.  Автоматическое подведение итогов

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

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

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

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

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

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

Создание промежуточных итогов:

  •  выделить любую ячейку списка;
  •  выполнить команду Данные – Итоги; появится ДО;
  •  выбрать поле, при изменении значения которого подводятся итоги (поле, по которому группируются данные);
  •  выбрать операцию для каждой группы данных;

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

  •  указать поля, к которых будет выполнена выбранная операция (их может быть несколько), для этого установить флажки в списке Добавить итоги по;
  •  для замещения старых итогов новыми установить флажок в поле Заменить текущие итоги.

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

  •  установить или снять флажки для размещения итогов под (по умолчанию) или над группами данных.
  •  нажать ОК.

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

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

Пример:  определить ср. балл и стипендиальный фонд для каждого факультета и группы.

Порядок действий:

  •  сортировка по полям факультет  – группа;
  •  Данные – Итоги
    •  факультет
    •  среднее
    •  ср. балл
    •  заменить текущие итоги
  •  Данные – Итоги  
    •  факультет
    •  сумма
    •  стипендия
    •  текущие итоги не заменять
  •  Данные – Итоги
    •  группа
    •  среднее
    •  ср. балл
    •  текущие итоги не заменять
  •  Данные – Итоги
    •  группа
    •  сумма
    •  стипендия
    •  текущие итоги не заменять

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

  •  уровни структуры - кнопки с номерами уровней 1,2,3 и т.д.;
    •  показ деталей – кнопка  +;
      •  скрытие деталей – кнопка  - ;
      •  полосы уровней структуры.

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

1 – только общий итог;

2 – итоги по факультетам;

3 – итоги по группам и факультетам;

4 – все записи списка и все итоги.

Кнопки + и – используются для того, чтобы показать или скрыть данные для каждой группы.

После изменения уровня детализации можно:

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

Удаление промежуточных итогов:

Выполнить команду: Данные – Итоги  - Убрать все.

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

  1.  Работа со сводными таблицами

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

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

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

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

Создание сводной таблицы.

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

Порядок действий при создании св. таблицы:

  •  выделить любую ячейку списка;
  •  выполнить команду Данные – Сводная таблица, появится ДО Мастера св. таблиц;
  •  Шаг 1:

Указывается на основе каких данных создается св. таблица, по умолчанию на основе списка или базы данных Microsoft Excel.

  •  Шаг 2:

Указывается диапазон данных, на основании которых создается сводная таблица (определяется автоматически, если была выделена ячейка списка).

  •  Шаг 3.

Формируется структура сводной таблицы и указывается место для ее размещения.

Св. таблица не заменяет исходную таблицу, а создается отдельно на том же или новом листе. Лучше размещать св. таблицу на отдельном листе

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

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

Для формирования структуры св. таблицы щелкнуть по кнопке Макет. Появится ДО Мастер сводных таблиц и диаграмм - макет. В этом окне:

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

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

  •  щелкнуть по кнопке ОК.

Для создания таблицы щелкнуть по кнопке Готово.

Появится сводная таблица и ПИ Сводные таблицы.

Обновление данных сводной таблицы.

При изменении данных списка сводная таблица автоматически не изменяется. Для ее обновления необходимо:

  •  выделить любую ячейку сводной таблицы;
  •  выполнить команду Данные – Обновить данные или нажать соответствующую кнопку (!) на ПИ Сводные таблицы.

Изменение структуры сводной таблицы:

выделить любую ячейку сводной таблицы;

вызвать Мастер сводных таблиц;

Данные – Сводная таблица или ПИ Сводная таблица или КМ св. таблицы

изменить параметры св. таблицы.

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

Удаление св. таблицы:

выделить всю сводную таблицу;

выполнить команду Удалить меню Правка или КМ св. таблицы


 

А также другие работы, которые могут Вас заинтересовать

44912. Эпоха Возрождения или Ренессанс 27.65 KB
  В своем основном педагогическом трактате О первоначальном воспитании детей а также в других трудах по вопросам воспитания О благовоспитанности детей Беседы Метод обучения Способ писать письма Роттердамский определил необходимость сочетания античной и христианской традиций при выработке педагогических идеалов а также принцип активности воспитанника врожденные способности могут быть реализованы лишь через напряженный труд Школа Витторино да Фельтре Дом радости Создал светское учебное заведение нового типа дававшее...
44914. Нагрузки и воздействия на трубопровод 15.03 KB
  Поэтому учитывая продолжительность сроков эксплуатации трубопроводов и высокую изменчивость нагрузок и воздействий исходная информация о нагрузках и воздействиях должна быть подготовлена с учетом фактора времени. Для нестандартных условий работы должна быть применена расширенная процедура анализа нагрузок и воздействий на трубопровод и расчета напряженнодеформированного состояния. Для каждой из позиций должна быть реализована специализированная методика анализа нагрузок и воздействий на трубопровод. Перечень и уровень функциональных...
44915. Определитель порядка п, его свойства 14.2 KB
  Определитель n – порядка соответствует квадратной матрице А называется сумма произведение элементов матрицы взятых по одному из каждой строки и каждого столбца причем со знаком если число инверсий в произв. Определитель не меняется при транспонировании. Это означает что определитель матрицы равен определителю транспонированной матрицы матрицы в которой строки заменены соответствующими столбцами. Если одна из строк определителя состоит из нулей то определитель равен нулю.
44916. Canada. Канада 17.07 KB
  Canada is the second largest country in the world. Only Russia has a greater land area. Canada is situated in North America. Canada is slightly larger than the United States, but has only about a tenth as many people. About 28 million people live in Canada.
44917. Форматирование жесткого диска 19.97 KB
  Этот каталог должен существовать в каждой файловой системе поскольку он выполняет служебную роль: при проверке файловой системы командой fsck в этом каталоге собираются потерянные файлы и подкаталоги. Команда fsck Основная функция программы fsck заключается в восстановлении логической непротиворечивости файловой системы созданной в разделе жесткого диска. Формат запуска команды следующий: [root] fsck [опции] [t fstype] [fsoptions] filesystem где fstype тип проверяемой файловой системы а в качестве filesystem можно указать либо имя...
44918. Творчество Кузьмі Сергеевича Петров-Водкина 28.05 KB
  Эротизм в творчестве Некоторые работы Петрова-Водкина были восприняты как содержащие эротизм. Среди картин Петрова-Водкина присутствует немало изображений обнажённых мужчин. В 1910е годы складывается особая художественно-теоретическая система Петрова-Водкина где главную роль играет принцип сферической перспективы который позволяет ему изображая натуру в ракурсах сверху и сбоку передавать ощущение земли как планеты. Картины Петрова-Водкина: Утренний натюрморт; Берег; Девушки на Волге; Купание красного коня; 1918 год в Петрограде; ...
44919. Основные методы и приёмы обучения РЯ в школе 14.41 KB
  Методы обучения: Познавательные методы А Объяснительный: слово учителя лекция Б Эвристический: беседа самостоятельный анализ. Практические методы.
44920. Ударение в русском языке 15.64 KB
  Ударение может стоять на любом слоге и любой части слов зо́лото боло́то молоко́ ; в разных грамматических формах одного и того же слова ударение может переходить с одного слога на другой нога́ но́гу при́нял приняла́; такими приёмами пользовались многие поэты XIX века. Некоторые сложные слова а также слова с приставками анти меж около контр сверх супер экс и др. Побочное ударение обычно по порядку бывает первым ближе к началу слова а основное вторым ближе к концу слова: кля́твопреступле́ние о́колозе́мный...