71687

EXCEL СОЗДАНИЕ БАЗ ДАННЫХ. ФОРМА. ФИЛЬТР. УСЛОВИЯ ОТБОРА. СОРТИРОВКА

Лабораторная работа

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

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

Русский

2014-11-10

130.5 KB

7 чел.

PAGE  7

ЛАБОРАТОРНАЯ РАБОТА № 5

EXCEL СОЗДАНИЕ БАЗ ДАННЫХ. ФОРМА. ФИЛЬТР. УСЛОВИЯ ОТБОРА. СОРТИРОВКА.

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

  1.  ОСНОВНЫЕ ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ:
    1.  База данных.

Одна из задач, для которых часто используется Excel, - это организация баз данных.

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

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

В Excel термины база данных и список часто используются как синонимы.

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

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

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

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

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

Таким образом:

Поле – столбец, содержащий данные определенного типа.

Имя поля - заголовок столбца.

Записи - данные, записанные в строках.

  1.  Форма данных

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

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

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

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

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

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

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

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

  1.  Создание новых записей.

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

Процесс прост: при нажатии на кнопку Добавить высвечивается чистая форма данных (обозначенная как новая запись), которую нужно заполнить. После ввода значения первого поля нажмите клавишу <Tab> для перехода к следующему полю. При нажатии <Enter> добавляется заполненная запись в базу данных.

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

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

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

  1.  Поиск записей

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

В качестве критерия поиска используется либо известная информация, либо символы (*) и (?).

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

  1.  Сортировка базы данных.

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

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

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

При выборе команды Данные/Сортировка открывается окно диалога "Сортировка диапазона", в котором надо указать поля сортировки и определить критерий сортировки.

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

Сортировка по возрастанию предполагает следующий порядок:

- числа

- текст, включая текст с числами

- логические значения

- значения ошибок

- пустые ячейки

При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке от А до Я. Числовые данные упорядочиваются по возрастанию значения от минимального до максимального.

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

Два дополнительных раздела Затем и В последнюю очередь, позволяют определить порядок вторичной сортировки записей, в которой имеются совпадающие значения.

Окно диалога "Сортировка диапазона" содержит кнопку Параметры, в результате нажатия которой открывается окно диалога «Параметры сортировки». С помощью этого окна можно:

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

- сделать сортировку чувствительной к использованию прописных и строчных букв.

- изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева на право).

  1.  Фильтрация данных в списке.

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

Все это упрощает процесс ввода и удаления записей, а также процесс поиска информации.

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

В Microsoft Excel доступны две команды для фильтрации списков:

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

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

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

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

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

Microsoft Excel обозначает отфильтрованные элементы голубым цветом 

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

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

• когда к ячейкам одного столбца необходимо применить три и более условий отбора:

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

Расширенный фильтр может использовать операции И, ИЛИ, а также составлять вычисляемые критерии.

Обе команды вызываются в результате выбора команды Данные/Фильтр.

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

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

1 Создать БАЗУ ДАННЫХ (Исходный диапазон).

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

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

Условия всех столбцов каждой строки соединяются логической операцией «И», а затем все строки соединяются логической операцией «ИЛИ»

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

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

  1.  Проверить правильность автоматического заполнения всех диапазонов и при необходимости откорректировать их.

Примеры сложных условий отбора:

Несколько условий для одного столбца

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

Продавец

Белов

Батурин

Рощин

Одно условие для нескольких столбцов

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

Тип

Продавец

Продажи

Фрукты

Белов

>1 000

Разные условия для разных столбцов

Тип

Продавец

Продажи

Фрукты

Белов

>1 000

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

Один из двух наборов условий для двух столбцов

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

Продавец

Продажи

Белов

>3 000

Батурин

>1 500

Более двух наборов условий для одного столбца

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

Продажи

Продажи

>5 000

<8 000

<500

Условия, создаваемые как результат выполнения формулы

В качестве условия отбора можно использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка столбца условий; либо оставьте условие отбора без заголовка, либо используйте заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.

  1.  ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ.
  2.  Создать список работников предприятия по следующим полям:
  •  № п/п
  •  Фамилия
  •  Имя
  •  Отчество
  •  Год рождения
  •  Улица
  •  Дом
  •  Квартира
  •  Телефон
  •  Специальность
  •  Год приема на работу

  1.  Внести первую запись по всем полям.
  2.  Создать форму данных и внести не менее 10 записей.
  3.  Изучить порядок поиска записей из формы данных (использую справочную систему Excel).
  4.  Отсортировать базу данных по фамилии, по году рождения, по специальности.
  5.  Используя команду Расширенный фильтр сделать выборку по следующему критерию:
    •  работники с одинаковыми отчествами одного года рождения;
    •  работники старше 60 лет;
    •  работники от 20 до 30 лет и работающие более 10 лет.
    •  Выбрать людей родившихся в указанные три года
    •  Выбрать людей, проживающих по указанной улице, с указанным номером дома, указанного года рождения.
    •  Выбрать людей, у которых номер телефона начинается на 44 или проживают на одной улице и в одном доме
    •  Выбрать людей, у которых отчество заканчивается на «-ич» и одного возраста
  6.  Открыть файл Most.xls. Скопировать себе на рабочий лист список на листе1 исходного файла.
  7.  С помощью автофильтра найти:
    •  все мосты, построенные в США;
    •  мосты, построенные с 1960 по 1970 годы типа «Арочный»;
    •  все висячие мосты и вантовые мосты
    •  мосты, длина пролета которых не меньше 1000 м
    •  арочные мосты с длиной пролета более 500 м.
    •  американские висячие мосты

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

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

1 Что такое список Excel. и каковы правила оформления списков?

2  Как упростить работу с базой данных?

3. Как создать форму данных для новой базы?

4 Для чего используется сортировка базы данных?

5 Какие команды используются для фильтрации данных?

6 В каких случаях применяется команда расширенный фильтр?

 


 

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

40045. Знакомство с приложением PowerPoint. Создание слайдов. Вставка в слайды различных объектов 136 KB
  Установить для заголовка размер шрифта – 60 пт цвет – красный. Установить для заголовка тень желтую с помощью кнопки Тень на панели инструментов Рисование. Установить для подзаголовка размер шрифта 40 пт цвет синий тень – голубую. Установить фон слайда белый мрамор с помощью команды Формат Фон вкладка Текстура.
40046. Приложение MICROSOFT INTERNET EXPLORER 107 KB
  Сохраните открытую страничку в своей папке: выполните команду Файл Сохранить как; в окне Сохранение вебстраницы откройте или создайте папку ФамилияГруппа; в поле Имя файла введите его название например Студенческая_жизнь; в поле Тип файла выберите Вебстраница полностью и нажмите кнопку Сохранить. Откройте в программе Проводник свою папку и проанализируйте размер трех созданных в ней файлов. Сохраните найденную информацию в файле Об образовании поместив его в собственную папку...
40047. Расчёт и исследование технологической системы разгрузки космического зеркала менисковой формы с переменной толщиной мениска вдоль радиального направления 14.64 MB
  Исследование особенностей технологической системы разгрузки космического астрономического зеркала с целью обеспечения разгруженного состояния зеркала в процессе его контроля и обеспечению технологических условий с условиями эксплуатации зеркала в космическом пространстве...
40048. Комплекс технических мероприятий по эксплуатации и наладке устройства ЭСПУ электроавтоматики и электропривода согласно исходным данным 1.82 MB
  Целью дипломного проекта является приобретение навыков при эксплуатации электронных систем программного управления и выполнения наладочных операций на каждом этапе наладки электронных узлов, а также обеспечение надёжности работы электронных систем программного управления (ЭСПУ)...
40049. Робота з функціями в мові С 290.5 KB
  Программа прошла тестирование: в результате выполнения тестов программа выдала ожидаемые результаты. Программа – работоспособна.
40050. Робота з багатомірними масивами в мові С 512.5 KB
  Получить новую матрицу путем вычитания всех элементов данной матрицы из ее наибольшего по модулю элемента.
40051. ИНФОРМАЦИОННЫЕ СИСТЕМЫ УПРАВЛЕНИЯ БИЗНЕСОМ 480 KB
  Давыденко ИНФОРМАЦИОННЫЕ СИСТЕМЫ УПРАВЛЕНИЯ БИЗНЕСОМ Методические рекомендации и контрольные задания для студенток заочной формы обучения специальности 1 – 26 02 01 Бизнесадминистрирование Минск 2011 Авторсоставитель: Давыденко Татьяна Дмитриевна старший преподаватель. Информационные системы управления бизнесом: методические рекомендации и контрольные задания для студенток заочной формы обучения специальности 1–26 02 01 Бизнесадминистрирование. Приведена программа курса Информационные системы...
40052. ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ Е.В.Сидиряков 343.5 KB
  Информационная технология (ИТ) — совокупность методов и способов получения, обработки, представления информации, направленных на изменение ее состояния, свойств, формы, содержания и осуществляемых в интересах пользователей на основе производственных и программно-технологических средств, объединенных в технологическую цепочку, обеспечивающую сбор, хранение, обработку, вывод и распространение информации.
40053. СИСТЕМЫ ПРОГРАММИРОВАНИЯ 87.5 KB
  2 Что такое системы программирования их состав. Языки программирования. Машинноориентированные системы программирования.