Организация базы данных Excel

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

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

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

Структура базы данных Excel

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

Основным понятием базы данных является запись. Запись, в свою очередь, делится на поля. Одни и те же поля из различных записей предназначены для данных одного типа. На рабочем листе каждая запись располагается на отдельной строке, а для каждого поля отводится определенный столбец. Столбцам присваиваются уникальные имена полей (заголовки), которые заносятся в первую строку списка, называемую строкой заголовков. Excel использует заголовки при сортировке и извлечении данных и составлении отчетов. Например, база данных, представленная в табл. 7.2, состоит из пяти полей и четырнадцати записей. Верхняя строка — строка заголовка.

Таблица 7.2. База данных Товары

Товарная группа

Виды товара

Наименование товара

Единицы

измерения

Стоимость,

руб.

Кондитерские

Конфеты

Белочка

кг

48

Кондитерские

Конфеты

Чародейка

кг

48

Кондитерские

Конфеты

Буревестник

кг

32

Кондитерские

Конфеты

Ласточка

кг

34

Кондитерские

Торт

Весна

шт.

65

Кондитерские

Торт

Подарочный

шт.

56

Кондитерские

Торт

Полет

шт.

70

Мясные

Колбасы

Краковская

кг

36

Мясные

Колбасы

Московская

кг

45

Мясные

Колбасы

Сервилат

кг

60

Мясные

Колбасы

Таллинская

кг

30

Мясные

Консервы

Тушена говяжья 425 г

шт.

17,67

Мясные

Консервы

Тушена говяжья 500 г

шт.

15,45

Мясные

Консервы

Тушенка свиная 325 г

шт.

18

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

Создание базы данных

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

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

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

  • 1. Задать строку заголовков и выделить ячейку внутри нее.
  • 2. Выбрать пункт меню Данные —» Форма.
  • 3. На экране отобразится форма данных (рис. 7.12) — окно, содержащее поля ввода. Названия полей ввода соответствуют именам полей, определенным в строке заголовков. [1]
Окно формы данных

Рис. 7.12. Окно формы данных

5. В окне Формы справа от полей ввода отображается номер текущей записи и общее число записей базы данных. Ниже расположены кнопки управления списком:

Добавить — создает новую запись;

Удалить — удаляет выбранную запись;

Вернуть — отменяет внесенные в запись изменения;

Назад — вызывает предыдущую запись;

Далее — вызывает следующую запись;

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

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

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

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

  • 1. Выделить ячейку внутри базы данных.
  • 2. Выбрать пункт меню Данные —» Сортировка.
  • 3. В диалоговом окне Сортировка диапазона (рис. 7.13) установить следующие параметры:

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

JLjxj

Сортировка диапазона

'Иды товара

Сортировать по

по возрастанию по убыванию

Затем по

(* по возрастанию С по убыванию

В последнюю очередь, по

по возрастанию ^ по убыванию Идентифицировать диапазон данных по

подписям (первая строка диапазона) обозначениям столбцов листа

Параметры...

ОК

Отмена

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

Порядок сортировки — переключатели По возрастанию и По убыванию, расположенные рядом с каждым ключом;

Идентифицировать поля по — указать, включает ли выделенный диапазон заголовки столбцов;

Параметры — позволяет задать особый порядок сортировки, например по дням недели.

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

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

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

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

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

В критериях на основе сравнения можно использовать различные операции сравнения (=, <, >, >= (меньше или равно), <> (неравно)). Например, чтобы найти товары дороже 40 рублей, в поле Стоимость записать условие >40. Перечисленные операции можно использовать не только с числовыми, но и с символьными аргументами. Например, критерий =Т* позволяет найти все значения из поля, начинающиеся на букву «Т».

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

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

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

  • 1. Выделить ячейку внутри базы данных.
  • 2. Выбрать пункт меню Данные —> Фильтр —> Автофильтр.
  • 3. В правой части заголовка каждого поля (рис. 7.14) отобразится кнопка раскрывающегося списка, с помощью команд которого устанавливаются критерии отбора.
  • 4. Чтобы отменить предыдущий фильтр, используется команда Все раскрывающегося списка.
  • 5. Чтобы отключить автофильтр, используется пункт меню Данные —» Фильтр —»Автофильтр.
  • 6. Расширенный фильтр лишь немного сложнее Автофильтра, но позволяет накладывать более сложные условия отбора, которые могут даже включать вычисляемые критерии.

Проведение итоговых расчетов

  • 7. Excel позволяет получить предварительные результаты, если требуется объединить данные в отдельные группы. Назначение промежуточных итогов состоит в обобщении данных. Используя команду Итоги, не требуется писать формулы. Excel автоматически создает необходимые математические выражения, вставляет строки промежуточных итогов и структурирует данные. В результате информацию легко обрабатывать. Чтобы правильно подвести промежуточные итоги, требуется сгруппировать данные (отсортировать) по ключевому столбцу, на основании которого будут подводиться итоги.
  • 1. Выделить любую ячейку в списке.
  • 2. Выбрать пункт меню Данные —» Итоги.

3. В диалоговом окне Промежуточные итоги (рис. 7.15) установить следующие параметры:

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

в списке Операция выбрать операцию, выполняемую над данными;

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

4. Чтобы отменить расчет итогов, следует воспользоваться командой Данные —> Итоги и нажать кнопку Убрать все.

Основные особенности работы в Excel 2007

Новая версия табличного редактора обладает рядом существенных преимуществ по сравнению с Excel 2003. Первым отличием является серьезное изменение размеров рабочего листа. Максимальное количество строк на листе составляет 1 048 575, а столбцов — 16 384. Внешне экраны очень похожи, за исключением того, что в новой версии табличного редактора вместо меню и панелей инструментов на экране присутствует структурные элементы в форме линеек, как и в текстовом редакторе (рис. 7.16).

d=T'

С*

Книга! - Microsoft Excel

HJ й

fyu0 1-,

—Главная Вставка

Разметка ар Формулы Данные Рецензирові

1 1 1

Вид Разработчиї Надаройки

1® _ я X

J ча

Вставить

J

Calibri

- |ll -|]

— = = 3і Общий

: а

j*3 Baa вить -

Z * ffT-

Ж К

ti »* »

— — —

000

j* Удалить -

Sb ^

H H A A 1

=- -=- -= '

J4 -II

<2» - Д -і

IW IW

«-,0 ,00

1 ,00 +,0

Стили

?

jp Формат -

&

Буфер обмена Ь

Шрифт Г*

Выравнивание ^ Число

Г5

Ячейки

Редактирован...

А1

?

*

Is

А

В

C

D E

F

G

Н 1

j

і г

Рис. 7.16. Линейка Главная редактора Excel 2007

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

линейке Вид или переключение по ярлыку ВЭ'Ш Э в панели просмотра строки состояния (справа внизу экрана). Такой режим очень удобен для контроля печати при подшивке документов в папки.

Режим

разметки

страницы

Условное

формати

рование

Кнопка добавления

Переключатель

листов Режим разметка страницы

Рис. 7.17. Режим разметки страниц, линейка Вид, пример условного форматирования содержимого клеток в Excel 2007

В предыдущих версиях все команды для форматирования элементов таблицы располагались в меню Формат. В Excel 2007 эти команды располагаются на линейке Главная, что позволяет производить данную операцию без обращения к диалоговому окну, практически все закладки этого окна представлены на линейке в отдельных группах: Шрифт, Выравнивание, Число. Здесь присутствуют кнопки для выравнивания содержимого ячеек как по горизонтали, так и по вертикали, для изменения угла, под которым расположен текст в ячейке, для настройки формата выводимого значения (общий, процентный, денежный), а также кнопка для изменения разрядности (см. рис. 7.16).

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

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

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

При обработке больших объемов информации часто востребованной является функция сортировки и фильтрации. Эти функции имеют две точки доступа: линейки Главная и Данные. При обращении к этим функциям редактор анализирует тип данных в рассматриваемых диапазонах и автоматически предлагает контекстно-ориентированные варианты команд. Например, при сортировке текстовых данных в качестве порядка сортировки предлагаются два варианта от А до Я и от Я до А, если данные числовые — по возрастанию и по убыванию, а если данные типа дата/время, то — от старых к новым и наоборот. Операция сортировка теперь имеет больше вариантов настройки и допускает добавление более трех уровней и удаление (рис. 7.18), возможна сортировка по цвету. Контекстная ориентация команд используется и при фильтрации (числовые, текстовые фильтры и фильтры по дате).

Окно настройки сортировки в Excel 2007

Рис. 7.18. Окно настройки сортировки в Excel 2007

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

Большое значение для обработки данных имеет линейка Данные, на которой сосредоточены функции баз данных: возможность подключения импортируемой базы, обновление ранее подключенной, фильтрация, подведение итогов. Однако в новой версии отсутствует на линейках стандартная форма ввода записей в базу данных (см. рис. 7.12). Команду, выполняющую эту функцию, можно вывести на панель быстрого доступа следующим образом: Кнопка Office —> кнопка Параметры Excel —> закладка Настройка —> из группы Все команда с помощью кнопки Добавить переместить ярлык Форма в окно Настройка панели быстрого доступа —»ОК.

Кроме того, на линейке Данные организуется доступ в дополнительно подключаемым надстройкам Анализ данных и Поиск решения. Ранее эти надстройки подключались через меню Сервис. Впрочем, через это же меню осуществлялась проверка орфографии и настройка параметров. Теперь большинство этих инструментов вынесено на линейке Рецензирование, а параметры программы можно настроить, щелкнув на кнопке Office и нажав кнопку Параметры Excel. Для подключения Пакета анализа и инструмента для решения оптимизационных задач используется закладка Надстройки, а для параметров закладки Основные, Дополнительные, Правописание, Сохранение, Формулы и проч. Кнопки, предназначенные для создания групп и структур, в Excel 2007 располагаются в группе Структура на линейке Данные. Кнопка Сводная таблица находится в новой версии программы на вкладке Вставка. При работе со сводной таблицей автоматически появляются дополнительные линейки Конструктор, отвечающий за стилевое оформление таблиц, и Параметры, отражающие команды обработки данных, создания сводной диаграммы, работы со структурой и настройки расчетных параметров .

Еще одна интересная функция Microsoft Office Excel 2007 — удаление повторяющихся записей. Чтобы удалить повторяющиеся записи, следует щелкнуть на любом месте в таблице и на линейке Данные в группе Работа с данными нажать кнопку Удалить дубликаты. На экране появится диалоговое окно Удалить дубликаты, в котором следует нажать кнопку OK. Excel просканирует все записи и при наличии повторяющихся удалит их. В этой же группе находятся кнопки Консолидация и Текст по столбцам.

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

Контрольные вопросы

  • 1. Опишите функциональные возможности табличного редактора Excel.
  • 2. Какие отличительные элементы содержит окно редактора Excel? Каково их назначение?
  • 3. Что такое книга Excel? Какое расширение имеет соответствующий файл?
  • 4. Каким образом осуществляются операции Открыть, Создать, Сохранить книгу Excel?
  • 5. Что такое лист Excel?
  • 6. Каким образом можно разбить лист на печатные страницы?
  • 7. Как установить параметры печатной страницы в Excel.
  • 8. Назовите различные способы работы с листами книги Excel.
  • 9. Назовите основные элементы структуры таблицы Excel.
  • 10. Какими способами можно выделять различные элементы таблицы?
  • 11. Что такое маркер автозаполнения и каково его назначение?
  • 12. Как осуществляется ввод и редактирование информации в ячейках таблиц Excel?
  • 13. С помощью каких средств можно автоматизировать ввод данных в ячейки таблицы Excel?
  • 14. Опишите различные способы размещения и форматирования данных в ячейках таблиц Excel.
  • 15. Опишите различные способы редактирования структуры таблиц Excel.
  • 16. Что такое адрес ячейки в Excel и где он используется? Как записывается адрес диапазона ячеек?
  • 17. Сформулируйте общие правила написания формул в Excel.
  • 18. Назовите различные способы копирования формул.
  • 19. Обоснуйте целесообразность использования средства Специальная вставка.
  • 20. Опишите алгоритм использования мастера функций редактора Excel.
  • 21. Каким символом разделяются аргументы в функциях редактора Excel?
  • 22. Назовите основные элементы диаграммы редактора Excel.
  • 23. Какие типы диаграмм можно создавать в Excel?
  • 24. Опишите алгоритм создания диаграммы в Excel.
  • 25. Опишите способы редактирования диаграммы в Excel.

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