Амелина Н. - РАБОТА С ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ
Ростовский ордена Трудового Красного Знамени государственный университет
ВВЕДЕНИЕ
Термин база данных можно применить к любой совокупности связанной информации, объединенной вместе по определённому признаку. Основным назначением баз данных является хранение информации и ее быстрый поиск.
Электронные таблицы можно использовать для организации работы с небольшими реляционными (табличными) базами данных.
В электронные таблицы Excel включен набор средств, позволяющий организовать простейшую базу данных и осуществлять автоматический поиск в ней необходимой информации. В настоящих методических указаниях будут рассмотрены следующие вопросы:
- организация ввода, просмотра и редактирования данных;
- сортировка и фильтрация данных;
- подведение итогов.
Введем ряд определений.
В Excel каждая таблица базы данных называется списком и должна находиться на отдельном рабочем листе.
Каждая из таблиц имеет фиксированную структуру и может состоять из произвольного числа строк.
Структура таблицы определяет количество столбцов в ней, их заголовки (имена) и вид информации, представимой в каждом столбце (числа, символы, тексты, даты и т.п.).
Список - последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа.
Строка списка называется записью, столбец списка - полем.
Поля списка имеют имена (метки или заголовки). Имена полей (названия столбцов) занимают только одну верхнюю строку таблицы-списка.
Над записями списка можно выполнять различные операции обработки, команды вызова которых содержатся в меню Данные.
ЗАДАЧА 1. ПРОГНОЗ ПОГОДЫ НА НЕДЕЛЮ
Дан прогноз погоды на неделю по городам России: дневная и ночная температуры воздуха и погодные условия.
Необходимо
- создать список данных о погоде (содержащий, например, сведения о погоде с 20 по 26 октября 2000 г., приведенные в таблице 1);
- определить среднесуточную температуру для каждого города;
- выполнить сортировку данных списка;
- организовать выбор данных из списка по различным критериям;
- определить для каждого региона минимальную и максимальную дневную и ночную температуры.
Этап 1. Создание структуры списка
Для создания списка необходимо определить структуру таблицы (число полей списка, их имена и вид информации, представимой в каждом столбцеполе), и ввести данные в таблицу (см. этап 2.).
1. В заглавной строке таблицы (первой или второй строке рабочего листа) введите имена полей.
Если в качестве заглавной строки выбрана вторая строка, то введите в ячейки A2: F2 заголовки столбцов:
Регион, Город, День, Ночь,
Среднесуточная температура, Погодные условия.
Перед вводом текстов заголовков (или после ввода) отформатируйте диапазон ячеек A2: F2, выравнивая заголовки по центру по вертикали и по горизонтали. Не забудьте включить режим переноса по словам, что необходимо для длинных заголовков.
Отрегулируйте ширину столбцов в соответствии с введенными заголовками столбцов и предполагаемым содержимым столбцов (см. таблицу 1).
Обведите строку заголовков рамками.
| Таблица 1 Прогноз погоды с 20 по 26 октября по городам России |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Окончание таблицы 1 | |||||||||||||||||||||||||||||||||||
|
Введите в ячейку E3 формулу для вычисления среднесуточной температуры
=(C3 + D3)/2
3. Если список расположен со второй строки, то можно в первой строке рабочего листа поместить название списка. Для этого введите в ячейку A1 или B1 соответствующий текст, например:
Прогноз погоды с 20 по 26 октября по городам России
4. Присвойте рабочему листу содержательное имя, например, Погода России вместо Листі и удалите лишние листы.
5. Сохраните текущее состояние таблицы в личной папке под именем prognos (будет создан файл prognos.xls).
Этап 2. Вводданных в список
Заполнение списка информацией осуществляется командой Данные/ Форма... строки меню Excel.
Форма - способ отображения данных, когда на экране располагаются поля только одной записи-строки.
Для работы с данными в режиме формы нужно выбрать пункт меню Данные/Форма..., предварительно выделив любую ячейку списка. Открывшееся диалоговое окно формы будет иметь то же название, что и рабочий лист.
В окне формы выводятся заголовки столбцов таблицы в качестве названий полей и окна для ввода данных, курсор стоит в первом окне ввода. Для перехода в следующее окно ввода используется клавиша Tab, для возврата в предыдущее - одновременно нажатые клавиши Shift и Tab.
Выбрать любой элемент формы можно щелчком левой кнопки мыши на нём.
Доступ к вычисляемым полям блокирован. Значения этих полей автоматически заполняются на основе информации, содержащейся в таблице.
Кроме полей, форма содержит индикатор номера записи и ряд кнопок справа от полей ввода.
Кнопка Добавить вводит новую строку в конец списка.
Кнопка Удалить удаляет текущую запись.
Кнопка Вернуть (Восстановить) позволяет восстановить исходное содержание записи, отменяя результаты редактирования, если только Вы не перешли к следующей записи или не нажали клавишу Enter.
Для "пролистывания" списка предназначены кнопки Назад (Предыдущая запись) и Далее (Следующая запись), можно также использовать полосу прокрутки.
Кнопка Закрыть закрывает форму и возвращает нас в режим работы с таблицей.
Для ввода данных в созданную таблицу - список воспользуйтесь режимом ввода с помощью диалогового окна формы:
1. Выделите любую ячейку заголовка таблицы (A2:F2).
2. Выберите пункт меню Данные/ Форма.
3. В открывшемся диалоговом окне формы Погода России (по имени рабочего листа) вводите данные о погоде в соответствующие поля ввода, используя информацию из таблицы 1. Для добавления очередной записи в таблицу используйте кнопку Добавить.
Записи добавляются в конец таблицы. Доступ к вычисляемому полю Среднесуточная температура блокирован.
4. После ввода последней записи нажмите кнопку Закрыть.
Этап 3. Редактирование списка
Добавление новых записей осуществляется в конец таблицы. Для этого, находясь в пределах списка, выполните п.п.2-4 этапа 2.
Для внесения исправлений в записи списка также можно использовать режим формы.
Нажатие кнопки Критерии обеспечивает переход в режим поиска данных по критериям. В качестве критериев используются как значения полей, так и условия на значения полей.
Для текстовых полей может быть задан поиск значений по частичному совпадению. Например, при поиске всех городов Урала в поле Регион указывается только одна буква У .
Если необходимо найти записи о городах, в которых дневная температура выше нуля, а ночная не превышает нуля, то в поле День указывается условие 0, а в поле Ночь - условие =0.
После нажатия клавиши Enter или кнопки Правка с помощью кнопок Назад и Далее можно просмотреть все записи в таблице, удовлетворяющие заданному набору критериев - совокупности критериев поиска.
Предположим, что нужно уточнить прогноз для городов Сибири, увеличив на один градус отрицательную ночную температуру.
Для этого :
1. Вызовите окно диалога формы командой Данные / Форма...
2. Нажмите кнопку Критерии. При этом поля формы очистятся, а название кнопки Критерии заменится на Правка.
3. В поле Регион укажите первые две буквы названия региона Си, перейдите с помощью клавиши Tab в поле Ночь и задайте условие 0.
4. Нажмите клавишу Enter или кнопку Правка для возврата в режим просмотра и редактирования данных.
5. Теперь в форме отображаются только необходимые записи. Листая их с помощью кнопок Назад и Далее, внесите исправления в поле Ночь.
6. Закройте окно формы, нажав кнопку Закрыть.
Для удаления записи из списка сначала ее нужно найти (см. п.п.1-4), а затем удалить, нажав кнопку Удалить.
Этап 4. Сортировка списка
Сортировка - упорядочение данных по возрастанию или убыванию.
Сортировка записей - строк списка реализуется в Excel в соответствии с содержимым конкретных столбцов командой Данные / Сортировка...
При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке, числа располагаются по возрастанию значений от минимального к максимальному, даты - в хронологическом порядке. Сортировка по убыванию происходит в обратном порядке.
Пустые ячейки всегда располагаются в конце списка.
В Excel можно реализовать многоуровневую сортировку. В трех строках ввода диалогового окна Сортировка диапазона последовательно задаются ключи сортировки - имена полей (столбцов), по которым сортируются данные.
Порядок сортировки для каждого поля устанавливается переключателями по возрастанию или по убыванию.
На каждом из следующих уровней сортировка применяется к группам записей (строк), имеющих одинаковые значения ключа для предыдущего уровня сортировки. Например, если первый уровень задает сортировку по регионам, а второй - по городам, то сначала записи будут отсортированы по регионам в алфавитном порядке, а в пределах одного региона - по городам также в алфавитном порядке, если порядок сортировки задан по возрастанию.
Если на очередном уровне все записи имеют различные значения ключа сортировки, то организация следующих уровней сортировки становится бессмысленной.
Замечание. При сортировке меняется порядок следования записей.
Если возникает необходимость часто возвращаться к первоначальной последовательности строк списка, то следует добавить в таблицу еще один столбец с порядковым номером записи и выполнять сортировку по этому полю.
Замечание. С
Так как в таблице приведен прогноз погоды по городам различных регионов России, удобнее работать не с исходным списком (см. таблицу 1), а со списком, в котором записи отсортированы по городам каждого региона.
Для этого:
1. Скопируйте рабочий лист Погода России на новый лист. Назовите новый лист, например, Погода по регионам.
2. Выполните команду Данные / Сортировка...
3. В открывшемся диалоговом окне Сортировка диапазона в двух полях ввода задайте или выберите из списка (раскрывающегося щелчком мыши на кнопке-стрелке) ключи сортировки: Регион, а затем Город. Установите порядок сортировки: для регионов - по убыванию, а для городов - по возрастанию.
Упражнение 1.1. Отсортируйте список по двум ключам:
а) Регион, Среднесуточная температура. Установите для поля Регион порядок сортировки по возрастанию, а для поля Среднесуточная температура - по убыванию;
б) Регион, Погодные условия.
Упражнение 1.2. Отсортируйте список по трем ключам:
а) Регион, День, Ночь ;
б) Регион, Ночь, День.
Указание к решению. Для выполнения упражнений 1.1, 1.2. четыре раза скопируйте рабочий лист Погода по регионам. Назовите новые листы, например, Сортировка 1, Сортировка 2, Сортировка 3 и Сортировка 4.
Этап 5. Фильтрация данных в списке
Фильтрация позволяет находить и отбирать (отсеивать) для обработки часть записей (строк), которые содержат определенное значение или отвечают определенным критериям. Остальные строки при этом скрыты.
В Excel для отбора данных списка (реализации запроса к базе данных) используются Автофильтр и Расширенный фильтр.
Расширенный фильтр применяется, если в отборе участвуют более двух критериев (условий поиска), или если при сравнении используются результаты вычислений.
Для установки Автофильтра следует, находясь в пределах списка, выполнить команду
Данные / Фильтр / Автофильтр
Метки (заголовки) столбцов преобразуются в раскрывающиеся списки (кнопки - стрелки), в которых можно задавать нужные критерии для поиска
данных.
В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые.
При выборе конкретного значения или опций Все, Пустые (Непустые) на экран выводятся соответствующие записи списка.
Опция Первые 10 предоставляет возможность выбрать необходимое число (по умолчанию 10) записей, содержащих наибольшие или наименьшие значения в поле фильтра. Предварительно список должен быть отсортирован по этому полю.
Опция Условие позволяет в диалоговом окне Пользовательский автофильтр указать для одного столбца один или два критерия отбора, объединив их (связкой И - для случая, когда оба условия должны выполняться одновременно, связкой ИЛИ - для случая, когда достаточно выполнения хотя бы одного из условий).
Например, для поиска городов, в которых дневная температура будет колебаться от 2 до 5 градусов, используется условие для поля День : больше или равно 2 И
меньше или равно 5
В условиях поиска для текстовых полей можно задавать символы шаблона:
* для указания любой последовательности символов,
? для представления любого одного символа.
Например, для отбора номеров телефонов АТС-22 следует задавать 22-*
Для поиска номера телефона, если Вы сомневаетесь в одной из цифр, укажите, например, так:
22-?6-76
Сброс одного из фильтров осуществляется выбором опции Все раскрывающегося списка этого фильтра.
Отмена режима фильтрации (без уничтожения фильтров) реализуется командой
Данные / Фильтр / Показать все
Удаление фильтров ( т.е. отключение Автофильтра ) производится командой
Данные / Фильтр / Автофильтр
При обработке данных таблицы прогноза погоды может возникнуть необходимость отбора данных, имеющих определенное значение или удовлетворяющих определенным условиям, то есть организации разнообразных запросов.
Предположим, что необходимо выделить из таблицы записи о городах Сибири с целью уточнения данных о ночной температуре.
Эта задача уже решалась на этапе 3 с использованием диалогового окна формы. Теперь для выбора данных из таблицы можно воспользоваться режимом Автофильтра. Для этого:
1. Перейдите в рабочий лист Погода России (или Погода по регионам).
2. Выполните команду Данные / Фильтр / Автофильтр
В ячейках заголовков столбцов появятся кнопки - стрелки списков фильтров.
3. Для отбора городов Сибири нажмите кнопку со стрелкой в поле Регион и в списке фильтра выберите строку Сибирь.
4. Для наложения условия на значения ночной температуры нажмите кнопку со стрелкой в поле Ночь и выберите строку Условие.
5. В диалоговом окне Пользовательский автофильтр укажите условие 0 и нажмите кнопку OK.
После всех этих действий на экран будет выведена часть таблицы прогноза погоды с необходимыми сведениями.
6. Перемещаясь по выведенным записям городов Сибири, внесите исправления в поле Ночь.
7. Отмените режим фильтрации командой
Данные / Фильтр / Показать все
Упражнение 1.3. Выделите из таблицы прогноза погоды записи о городах Северного Кавказа.
Упражнение 1.4. Выделите из таблицы записи о городах
а) Урала и Сибири;
б) Сибири и Дальнего Востока.
Упражнение 1.5. Выделите из таблицы записи о городах Сибири, в которых дневная температура выше (ниже) нуля.
Упражнение 1.6. Выделите из таблицы записи о городах Центра и Северного Кавказа, в которых дневная температура выше +100C, а ночная - выше +50C.
Упражнение 1.7. Выделите из таблицы записи о городах отдельных регионов России, в которых выпадение осадков не предвидится.
Замечание. После выполнения упражнений отмените режим фильтрации (см. п. 7) или удалите фильтры командой
Данные / Фильтр / Автофильтр
Этап 6. Формирование итогов
Подведение различных итогов - один из способов обработки списка.
О б щ и е и п р о м е ж у т о ч н ы е и т о г и
Для получения итоговой информации в Excel используется команда Данные/Итоги..., которая позволяет применить одну из итоговых функций к некоторому полю или полям. При этом итоги формируются не только ко всему списку (общие итоги), но и к отдельным группам записей, имеющих одинаковые значения по одному из полей (промежуточные итоги).
Перед выполнением команды Итоги список должен быть отсортирован по тому полю, по которому предусматривается группирование записей.
После сортировки можно подвести итоги по списку в целом и итоги по группам записей.
Команда Данные/Итоги... вызывает диалоговое окно Промежуточные итоги, содержащее три поля ввода.
В поле ввода При каждом изменении в указывается (выбирается из раскрывающегося списка) то поле, по которому группируются записи для получения промежуточных итогов.
В списке поля ввода Операция выбирается функция, вычисляющая итоги. Для подведения итогов по числовым полям используются следующие функции: Сумма (по умолчанию), Среднее, Максимум, Минимум, Произведение, Кол-во чисел и функции, вычисляющие отклонение от среднего.
Для подведения итогов по нечисловым полям используется по умолчанию операция Кол-во значений, вычисляющая количество записей в группе.
В поле ввода Добавить итоги по выделяются (левой кнопкой мыши) столбцы, по которым подводятся итоги.
Диалоговое окно Промежуточные итоги содержит три флажка.
Флажок Заменить текущие итоги разрешает обновление данных. Для сохранения текущих итогов и добавления новых следует снять этот флажок.
Установка флажка Конец страницы между группами приводит к вставке разделителя страниц перед каждой группой данных. Вследствие этого каждая группа с промежуточными итогами будет распечатываться на отдельной странице.
При установке флажка Итоги под данными строки промежуточных итогов и общих итогов помещаются под соответствующими данными, а при снятии флажка - над ними.
Кнопка Убрать все диалогового окна позволяет удалить все итоги из текущего списка.
С т р у к т у р и р о в а н и е т а б л и ц ы
Кроме итоговых строк, команда Данные/Итоги... формирует так называемую структуру, которая соответствует группировке данных для вычисления промежуточных и общих итогов.
Структурирование - это средство, которое позволяет управлять сокрытием или выводом на экран частей рабочего листа.
Слева от таблицы с итогами располагаются символы структуры -кнопки "+" и "-".
Кнопка "-" (символ сокрытия деталей) позволяет скрыть группу данных.
Кнопка "+" (символ показа деталей), которая появляется после сокрытия данных, позволяет их восстановить.
Замечание. Сокрытие данных не влияет на итоговые величины.
К символам структуры относятся и кнопки с изображением цифр ("1", "2", ...), обозначающие уровни структуры. Если нажать кнопку с номером уровня, то будут скрыты детальные данные этого уровня и всех низших.
Сформированную структуру можно скрыть. Для этого следует выполнить команду Сервис/Параметры. и на вкладке Вид снять флажок Символы структуры.
Структуру можно удалить, выполнив команду Данные/Структура. Удаление структуры не приведет к удалению итоговых строк.
С о к р ы т и е с т р о к и с т о л б ц о в
В Excel существует возможность скрыть любые строки и столбцы таблицы (в том числе и несмежные). Для этого достаточно выделить принадлежащие им ячейки и выполнить команду
Формат / Строка / Скрыть или Формат / Столбец / Скрыть
Отображение скрытых данных выполняется командой Формат / Строка / Показать или Формат / Столбец / Показать
О п р е д е л е н и е д и а п а з о н а т е м п е р а т у р
Возможности команды Данные/Итоги... можно применить для определения диапазона дневных и ночных температур для регионов России и страны в целом.
Для получения итоговой информации выполните следующие шаги:
1. Скопируйте рабочий лист Погода по регионам и присвойте новому листу другое имя, например, Итоги.
2. Для сокрытия столбца Погодные условия выделите любую его ячейку и выполните команду
Формат/Столбец /Скрыть
3. Записи таблицы должны быть отсортированы по регионам. Если список не отсортирован, выполните сортировку по полю Регион (этап 4).
4. Выделите одну из ячеек списка и выполните команду Данные /Итоги.
5. В открывшемся диалоговом окне Промежуточные итоги выполните следующие действия:
- Раскройте список в поле ввода При каждом изменении в и выберите то поле, по которому группируются записи для получения итогов. Таким полем в поставленной задаче является поле Регион, т.к. все итоги подводятся по регионам.
- В поле ввода Операция по умолчанию будет выбрана функция Сумма, замените ее на Максимум.
- В поле ввода Добавить итоги по выберите элементы, по которым подводятся итоги: День и Ночь. При помощи полосы прокрутки раскрывающегося списка пройдите по всем его элементам и проверьте, что другие элементы не помечены.
- Оставьте отметки для флажка Заменить текущие итоги и для флажка Итоги под данными.
- Нажмите кнопку ОК.
6. На экране появятся строки с промежуточными итогами для каждой группы записей и общий итог. Обратите внимание на кнопки, которые появились слева от таблицы.
Кнопки с цифрами 1,2 и 3 обеспечивают степень детализации показа данных в таблице: кнопка 3 - вся таблица с промежуточными итогами и общим итогом; кнопка 2 - только итоги: промежуточные и общий; кнопка 1 - только общий итог. Той же цели служат и кнопки со знаками + и -. Нажмите на каждую из кнопок и посмотрите, как свернулась структурная схема слева. После этого разверните весь список со всеми итогами, нажав кнопку 3.
7. Добавьте в таблицу итоговые строки по определению минимальных значений температур:
- Выполните команду Данные/Итоги...
- В поле ввода При каждом изменении в оставьте то же самое значение Регион.
- В поле ввода Операция выберите элемент Минимум.
- Для сохранения старых промежуточных итогов уберите отметку с флажка Заменить текущие итоги. Это позволит добавлять новые итоговые строки к уже имеющимся.
- Нажмите кнопку ОК. На экране появились новые итоговые строки, добавилась кнопка с номером 4 и изменилась структурная схема детализации.
8. Можно улучшить внешний вид таблицы, выполнив ее форматирование. Попробуйте воспользоваться Автоформатом. Для этого выполните команду Формат/Автоформат... и выберите один из вариантов форматирования. В окне просмотра для каждого варианта можно увидеть образец формата.
Перед форматированием списка выделите одну из ячеек списка, тогда весь список будет выбран автоматически. Чтобы отформатировать только часть списка, ее следует выделить.
9. Чтобы убрать все итоги, выделите любую ячейку списка, выполните команду Данные/Итоги. и в диалоговом окне Промежуточные итоги нажмите кнопку Убрать все.
10. Для отображения скрытых столбцов используйте команду Формат/Столбец /Показать
