Термины база данных и список в excel используются как синонимы

Термины база данных и список в excel используются как синонимы

Название работы: EXCEL СОЗДАНИЕ БАЗ ДАННЫХ. ФОРМА. ФИЛЬТР. УСЛОВИЯ ОТБОРА. СОРТИРОВКА

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

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

Дата добавления: 2014-11-10

Размер файла: 130.5 KB

Работу скачали: 8 чел.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Форматирование символов в Excel

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

      Форматирование ячеек

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

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

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

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

      Для изменения первоначального форматирования ячеек можно использовать:

      · кнопки панелей инструментов Стандартная и Форматирования;

      · команду меню окна ФОРМАТ ® Ячейки;

      · команду контекстного меню Формат ячеек.

      Команды меню окна ФОРМАТ ®Ячейки и команда контекстного меню Формат ячеек выводят на экран диалоговое окно Формат ячеек. Диалоговое окно Формат ячеек содержит шесть вкладок, с помощью которых можно определить все параметры ячейки или выделенного диапазона ячеек: число, выравнивание, шрифт, граница, вид, защита.

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

      На вкладке Выравнивание задаются параметры расположения текста в ячейке: по горизонтали, по вертикали, ориентация (поворот текста, расположение текста в ячейке по вертикали).

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

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

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

      На вкладке Защита можно скрыть отображение введенных в ячейку формул, а также установить защиту ячейки – запретить изменение помещенных в нее данных. При этом нужно иметь в виду, что ячейки, для которых установлены эти параметры, могут быть блокированы или скрыты только после установки защиты всего Рабочего листа (данной таблицы) или всей Книги. Эта защита (установка паролей на доступ и их отмена) выполняется с помощью команды СЕРВИС ® Защита.

      Анализ и обработка данных электронной таблицы

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

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

      Excel предоставляет разнообразные способы для автоматического обобщения и анализа данных:

      1. автоматические вычисления;

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

      3. средства автоматического подведения общих и промежуточных итогов;

      4. возможность создания и использования структуры таблицы;

      5. средства для консолидации (обобщения) данных;

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

      7. средства условного анализа (анализа «что, если») (подбор параметра, надстройка Поиск решения, сценарии, таблицы подстановки);

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

      Ограничимся рассмотрением лишь двух способов для автоматического обобщения и анализа данных (п.2, п.3).

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

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

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

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

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

      · все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

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

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

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

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

      · добавлять, изменять и удалять записи;

      · осуществлять фильтрацию (и выборку) данных с помощью Автофильтра и Расширенного фильтра;

      · подводить общие и промежуточные итоги и т.д.

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

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

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

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

      · числа сортируются от наименьшего отрицательного до наибольшего положительного числа;

      · текст сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 ‘ – (пробел) ! “ # $ & ( ) * , . / : ; ? @ [ \ ] ^ _ `

      + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я;

      · логическое выражение ЛОЖЬ предшествует значению ИСТИНА;

      · все ошибочные значения равны;

      · пустые ячейки всегда помещаются в конец списка.

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

      Сортировка данных в электронных таблицах производится с помощью команд меню ДАННЫЕ ® Сортировка. В диалоговом окне Сортировка диапазона указывают требуемые параметры – тип и порядок сортировки. Порядок сортировки записей можно указывать по возрастанию или убыванию значений одного, двух или трех столбцов (ключей сортировки). По умолчанию список сортируется по строкам. Можно задать режим сортировки по столбцам. Этот режим задается в диалоговом окне Параметры сортировки, которое открывается при щелчке по кнопке Параметры. В этом же окне можно задать собственный (пользовательский) порядок сортировки и установить переключатель для учета регистра.

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

      Фильтрация (выборка) данных из списка

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

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

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

      Ссылка на основную публикацию