Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
инеок Александра Борисовна Лекции по дисциплине “Инф. технологии при проектировании текст. изд-ий” стр 18.
РАБОТА С БАЗАМИ ДАННЫХ
Терминология и организация работы с базами данных в EXCEL
Интегрированный пакет Office содержит специальное программное средство, предназначенное для работы с базами данных (СУБД) – ACCESS. Однако обычному пользователю проще иметь дело с базой данных в виде обычной электронной таблицы и не осваивать специальную сложную программу.
EXCEL очень гибкая система, позволяющая достаточно просто организовать хранение информации именно в виде Базы данных. Единые для всего пакета Office форматы позволяют копировать информацию из ACCESS в EXCEL и обратно без потери данных, разработанных форматов, формул и различных настроек. Предоставляемый EXCEL 2007 выбор команд и функций для обработки баз данных не уступает ACCESS.
Ниже предоставлена полезная информация и рекомендации для правильной организации работы с такими данными.
В EXCEL предусмотрена специальная структура для организации базы данных. В версии 2003 такая структура называлась Списком, в версии 2007. такая структура получила название Таблица. Совпадение названий вызывает путаницу. Поэтому в рамках данной лекции упоминание о структуре Таблица будет выделено жирным шрифтом и с большой буквы, а упоминание об обычной таблице в EXCEL как о наборе данных будет с маленькой буквы и обычным шрифтом.
Независимо от версии EXCEL, база данных имеет собственную терминологию.
База данных состоит из строки заголовков (верхняя строка, содержащая название или описание данных) и множества строк с данными. Каждая строка списка содержит одинаковые типы данных для всей базы данных. В каждой ячейке строки заголовков Таблицы находится значок автофильтра или раскрывающего списка в виде серого квадратика с черным треугольником для сортировки и фильтрации данных.
Попадая мышкой по треугольнику можно выбрать нужный режим отображения данных из Таблицы.
Столбцы с данными, включая верхнюю ячейку с названием поля, называются полями, а строки называются записями. Размер Таблицы ограничен размерами одного рабочего листа, т.е. база данных в EXCEL может иметь не более 256 полей и не более 65 535 записей (одна строка отводится под строку с заголовками).
Заголовки (по одному для каждого поля) следует помещать в первую строку Таблицы — в строку заголовков. Если заголовки полей получаются очень длинные, то для этих ячеек следует применить формат с переносом слов в ячейке. Использовать несколько строк заголовков или помещать в нее объединенные ячейки не рекомендуется, так как EXCEL не сможет распознать заголовки, если они расположены в нескольких строках или в нескольких ячейках.
В каждом поле списка должна содержаться однотипная информация. Не нужно смешивать в одном поле информацию разных типов, например, дату и текст. Перед работой следует отформатировать весь столбец сразу, чтобы все ячейки столбца имели один и тот же формат. Более того, чтобы в поле Таблицы вносились только определенные данные, можно задать проверку вводимых данных при помощи команды Данные→Проверка.
Рекомендуется размещать Таблицу на отдельном листе. Не следует занимать столбцы левее и правее Таблицы.
В Таблице не должно быть пустых строк. При проведении операций с Таблицей EXCEL определяет его размеры автоматически и пустая строка для EXCEL означает конец списка. Если пустая строка попадется внутри базы данных, то нижняя часть Таблицы после пустой строки будет потеряна.
Если база данных очень велика (более одного экрана), то следует использовать команду Окно → Закрепить области, чтобы заголовки всегда были видны при прокручивании списка.
EXCEL-2007 выделит весь список синей рамкой. В верхней строке списка в правой стороне каждой ячейке–. В самом низу списка будет расположена пустая строка с синей звездочкой, предназначенная для ввода новых данных.
В большинстве случаев База данных организуется из уже существующего набора данных в EXCEL путем преобразования их в структуру Таблица. Если данные уже существуют в виде электронной таблицы , то их нужно выделить и выбрать команду Вставка→Таблицы →Таблица.
Для перевода в базу данных, нужно выполнить преобразование диапазона ячеек в отдельную структуру.
Для вычисления значений в ячейках можно использовать формулы, использующие значения из других полей той же записи. Если формула использует ячейку, расположенную вне списка, то следует сделать эту ссылку абсолютной, иначе при сортировке или фильтрации списка результаты будут непредсказуемы. Кроме этого, следует использовать специальные формулы для работа с базами данных.
Ниже приведены типовыми операциями с базами данных в EXCEL:
Ввод данных в список.
Данные можно ввести в список тремя способами.
При вводе данных вручную следует перемещаться по таблице и вводить информацию в нужные ячейки с клавиатуры. Чтобы избежать скучного заполнения однообразных данных можно использовать функции автозаполнения а также можно воспользоваться функцией выбора из списка. Для этого следует щелкнуть правой кнопкой мыши по пустой ячейке и выберите из появившегося контекстного меню команду Выбрать из списка. EXCEL выведет список со всеми элементами, находящимися в столбце. Выберите из списка нужный элемент, и он появится в ячейке (набирать при этом ничего не нужно!).
Ввод данных при помощи формы ввода.
Excel предоставляет возможность использования формы для ввода данных. Для этого следует , поместить табличный курсор внутри списка и выполните команду Данные→Форма. Excel определит размер списка и выведет диалоговое окно, в котором будет находиться каждое поле списка. Поля, содержащие формулу, в форме ввода отображаются, но изменить их значения будет невозможно.
Если полей в списке будет больше 32, то команда Данные→Форма не сработает и данные придется вводить вручную непосредственно в ячейки.
Когда появится форма, в ней будет показана первая запись списка. Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в списке. Для того, чтобы ввести новую запись, нужно щелкнуть мышью по кнопке Добавить, таким образом в форме очистятся все поля. Теперь можно вводить новую информацию в соответствующие поля. Следует использовать клавишу <ТаЬ> или <Shift+Tab> для перемещения от одного поля к другому. После щелчка по кнопке Добавить или Закрыть введенные данные появятся в конце списка. Можно также нажать клавишу <Enter>. Если список содержит формулы, то они автоматически появятся в новых записях.
Форму ввода можно использовать не только для ввода данных, но и для их редактирования, Форма позволяет редактировать существующие записи, просматривать их, удалять и выборочно отображать записи по определенному критерию.
Форма ввода содержит несколько кнопок.
Удалить. Удаляет текущую запись.
Вернуть. Отменяет все внесенные в текущую запись изменения. Эта кнопка работает до тех пор, пока вы не щелкнете на кнопке Добавить.
Назад. Осуществляется переход к предыдущей записи списка. Если установлен критерий отбора, то произойдет переход к предыдущей записи, удовлетворяющей данному критерию.
Далее. Осуществляется переход к следующей записи списка. Если установлен критерий отбора, то произойдет переход к следующей записи, удовлетворяющей данному критерию.
Критерии. Позволяет задать условие - критерий, по которому будут отбираться записи. Например, чтобы найти все записи, в которых значение больше 10, нужно ввести >10 в соответствующее поле формы. После этого можно использовать кнопки Назад и Далее, чтобы найти предыдущую или последующую запись, удовлетворяющую заданному критерию.
Закрыть. Закрывает форму и записывает введенные данные (если они были введены) в рабочий лист.
Фильтрация списков
Фильтрация списка - это процесс сокрытия всех строк списка, кроме тех, которые удовлетворяют заданным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. Фильтрация является весьма распространенной и очень полезной операцией. В Excel списки можно фильтровать двумя способами.
Автофильтр используется для фильтрации по простым критериям.
Расширенный фильтр применяется для фильтрации по более сложным или нескольким критериям.
Автоматическая фильтрация
По умолчанию кнопки автоматической фильтрации сразу добавляются в строку заголовка. Кнопки автофильтра выглядят как серые квадратика с черным треугольником острием вниз. Если кнопок автофильтра нет, то их можно выставить. Для этого следует установить табличный курсор на одну из ячеек списка и выполнить команду Данные → Фильтр → Автофильтр. Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков - кнопки автофильтра.
После фильтрации списка в строке состояния появится сообщение о том, сколько строк отобрано. Кроме того, изменится цвет кнопки раскрывающегося списка – он станет не черного, а синего цвета, чтобы напомнить, что список отфильтрован по значениям, содержащимся в этом столбце.
Автоматическая фильтрация имеет ограничения. В раскрывающемся списке появляются только первых 999 различных значений. Если число записей во всем списке превышает указанный предел, то лучше использовать расширенный фильтр.
Кроме всех значений, находящихся в столбце, раскрывающийся список содержит еще пять пунктов.
Все. Отображает все элементы столбца. Используется для отмены фильтрации столбца.
Первые 10. Выбирает заданное количество наибольших или наименьших элементов списка..
Условие. Вызов пользовательского фильтра. Позволяет фильтровать список по нескольким условиям.
Пустые. Фильтрует список, отображая только строки с пустыми ячейками.
Непустые. Фильтрует список, показывая только строки с непустыми ячейками.
Чтобы весь список был виден снова (отмена фильтрации), щелкните по кнопке раскрытия списка и выберите опцию Все, которая расположена первой в раскрывающемся меню или выполните команду
Данные → Фильтр → Отобразить все.
Пользовательский автофильтр
Пользовательский автофильтр является более гибким средством для фильтрации данных. В отличие от автофильтра он позволяет использовать более сложные критерии или несколько критериев для фильтрации данных. Для его вызова нужно выбрать в раскрывающемся списке автофильтра опцию Условие. Ниже перечислены предлагаемые варианты пользовательского автофильтра.
Значения больше или меньше установленного. Например, можно выбрать записи, указывающие на стоимость товара, превышающую 1 000.
Значения в интервале. Например, можно отобрать все записи, указывающие на стоимость товара, превышающие 1 000 и не превышающие 2 000.
Два отдельных значения. Отбор записей, по двум значениям одновременно.
Выборка по шаблону. Можно использовать символы подстановки "*" – любая последовательность символов и "?" – один любой символ, что бы отфильтровать список более гибким способом. Например, чтобы вывести на экран фамилии клиентов, начинающихся с буквы В, следует использовать шаблон В*, а шаблон И*ва выведет на экран фамилии клиентов начинающиеся с буквы И и заканчивающиеся на –ва.
Пользовательский автофильтр имеет ограничения. Например, с его помощью нельзя провести фильтрацию по трем и более значениям. Для выполнения задач такого типа необходимо использовать средство расширенной фильтрации, которое будет рассмотрено далее.
Наложение условия по списку
Если необходимо отфильтровать числовые поля так, чтобы показать на экране заданное количество наибольших или наименьших значений, тогда следует использовать опцию Первые 10.
Название Первые 10 — это просто общепринятый термин, действие же опции не ограничивается поиском только 10 "наибольших" элементов. С его помощью можно выбрать наибольшие или наименьшие элементы из списка, а также указать их количество. Таким образом можно отфильтровать список, оставив в нем нужное количество строк с наибольшими или наименьшими значениями этого поля. В этом диалоговом окне можно также выбрать опцию Элементов списка или % от количества элементов. Например, можно отобрать 5% наибольших элементов поля.
Расширенная фильтрация
Для обработки списка обычно бывает вполне достаточно автофильтра. Расширенная фильтрация предоставляет еще более широкие возможности. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять больше предварительных действий. Расширенный фильтр позволяет выполнить следующее:
Прежде чем использовать средство расширенной фильтрации, необходимо задать диапазон критериев. Обычно для этого используют либо несколько строк ниже списка, либо переносят диапазон с заданными критериями на другой лист. Диапазон критериев для фильтрации списка должен отвечать следующим требованиям:
Хотя можно отвести для диапазона критериев любое место на рабочем листе, желательно не задействовать при этом строки, используемые самим списком. Следует размещать диапазон критериев над или под списком, либо на отдельном листе.
Примеры числовых и текстовых критериев при использовании расширенной фильтрации.
Для сравнения с числом следует использовать операторы сравнения
= равно.
> больше.
>= больше или равно.
< меньше.
<= меньше или равно.
<> не равно.
В текстовых или строковых критериях не различаются прописные и заглавные буквы. Допускается применение специальных символов "*" – любая последовательность символов и "?" – один любой символ.
> К слова, начинающиеся с букв от Л до Я.
<> А все слова, кроме начинающихся с буквы А.
= “Маша” все слова МАША, Маша, маша и т.д.
Ив* все слова, начинающиеся на Ив.
П*ов все слова, начинающиеся с буквы П и заканчивающиеся на ов.
С??с Слова из четырех букв, начинающиеся и заканчивающиеся на букву с.
Сортировка списка
Обычно порядок записей в списке не имеет значения. Но иногда необходимо, чтобы строки имели определенную последовательность. Например, чтобы фамилию и имя нужного клиента легче было найти в списке, записи должны быть расположены в алфавитном порядке по фамилиям и именам клиентов. Иногда необходимо изменить порядок записей в уже выполненной таблице таким образом чтобы числовые значения располагались в списке в последовательности от большей к меньшей или наоборот.
Изменение порядка записей в списке называется сортировкой. Excel — очень гибкая система в отношении методов сортировки данных. Для простейшей сортировки достаточно одного щелчка мышью по значку на панели инструментов.
При сортировке по возрастанию данных EXCEL поддерживает следующие правила для полей разного типа.
Числа сортируются от наименьшего отрицательного к наибольшему положительному.
Дата и время сортируются так же, как и числа.
Текст сортируется по ASCII- кодам от меньшего к большему в следующем алфавитном порядке: 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 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 А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я а б в г д е ё ж з и й к л м н п р с т у ф х ц ч ш щ ъ ы ь э ю я.
По умолчанию во время сортировки строчные и прописные буквы не различаются, однако это можно изменить в параметрах сортировки, поставив галочку в опции Учитывать регистр.
Логические значения. Ложь следует перед истиной (ложь кодируется нулем, истина любым другим числовым значением).
Ошибочные значения. Такие значения, как #знач! Или #Н/Д появляются в их первоначальном порядке и вообще не сортируются.
Пустые ячейки. Всегда появляются последними.
Простая сортировка
Чтобы быстро отсортировать список в порядке возрастания, следует переместить табличный курсор в начало столбца, по которому нужно выполнить сортировку и щелкнуть мышью по кнопке Сортировка по возрастанию, расположенной на панели инструментов. Кнопка Сортировка по убыванию работает аналогично, список сортируется в порядке убывания. В обоих случаях Excel самостоятельно определяет размер списка и сортирует все записи в нем.
Сортировка отфильтрованного списка выполняется только для видимых строк. Если фильтрация отменяется, то список окажется неотсортированным.
Следует быть внимательным во время сортировки списка с формулами. Если в формулах используются значения ячеек, находящихся в тех же строках, то никаких проблем не возникнет. Однако если в формулах используются значения ячеек, находящихся в других строках списка, то после сортировки эти формулы не будут верными, так как адреса в них изменяться. Если формулы в списке связаны с ячейками вне списка, то следует использовать абсолютную адресацию ячеек.
Более сложная сортировка
В отдельных случаях необходимо выполнить сортировку по двум и более столбцам.
Если необходимо выполнить сортировку по нескольким полям, выберите команду Данные→Сортировка — появится диалоговое окно Сортировка диапазона. В раскрывающемся списке следует выбрать нужные поля для сортировки и указать порядок сортировки (по возрастанию или по убыванию). Всего можно провести сортировку по трем полям. Для того, чтобы заголовки полей не влияли на сортировку, нужно выставить переключатель Идентифицировать поля по подписям.
Если результат сортировки оказался неудовлетворительным, то можно отменить сортировку командой Правка → Отменить сортировку или нажатием клавиш <Ctrl+Z>, чтобы вернуть список к первоначальному состоянию.
Если необходимо сортировать больше, чем по трем полям, тогда придется проводить сортировку несколько раз.