Методические указания для студентов геолого-географического факультета по курсу ''Информатика''. Электронные таблицы. Работа с таблицей как с базой данных

Министерство образования Российской Федерации Ростовский ордена Трудового Красного Знамени государственный университет

Н.И.Амелина, Л.А.Мачулина

Методические указания для студентов геолого-географического факультета по курсу ИНФОРМАТИКА ЭЛЕКТРОННЫЕ ТАБЛИЦЫ РАБОТА С ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ

Ростов-на-Дону 2001

Печатается по решению кафедры прикладной математики и программирования от 28 декабря 2000 г.

АННОТАЦИЯ Методические указания содержат описание технологии работы со списками - базами данных в Excel и лабораторные работы, позволяющие практически освоить эту технику при организации простейшей базы данных и реализации поиска в ней необходимой информации. Методические указания предназначены для студентов геологогеографического факультета РГУ. Авторы : Н.И.Амелина, Л.А.Мачулина

© Н.И.Амелина, Л.А.Мачулина 2000

3

СОДЕРЖАНИЕ Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

ЗАДАЧА 1. Прогноз погоды на неделю . . . . . . . . . . . . . . . . . . . . . . .

5

Этап 1. Создание структуры списка . . . . . . . . . . . . . . . . . . . . . .

5

Этап 2. Ввод данных в список . . . . . . . . . . . . . . . . . . . . . . . . . . .

7

Этап 3. Редактирование списка . . . . . . . . . . . . . . . . . . . . . . . . . .

9

Этап 4. Сортировка списка . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10

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

12

Этап 6. Формирование итогов . . . . . . . . . . . . . . . . . . . . . . . . . . .

15

Этап 7. Защита данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

21

ЗАДАЧА 2. Страны мира . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

24

ЛИТЕРАТУРА . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

27

ПРИЛОЖЕНИЕ. Сообщения об ошибках . . . . . . . . . . . . . . . . . . . . .

27

4

ВВЕДЕНИЕ Термин база данных можно применить к любой совокупности связанной информации, объединенной вместе по определённому признаку. Основным назначением баз данных является хранение информации и ее быстрый поиск. Электронные таблицы можно использовать для организации работы с небольшими реляционными (табличными) базами данных. В электронные таблицы Excel включен набор средств, позволяющий организовать простейшую базу данных и осуществлять автоматический поиск в ней необходимой информации. В настоящих методических указаниях будут рассмотрены следующие вопросы: – организация ввода, просмотра и редактирования данных; – сортировка и фильтрация данных; – подведение итогов. Введем ряд определений. В Excel каждая таблица базы данных называется списком и должна находиться на отдельном рабочем листе. Каждая из таблиц имеет фиксированную структуру и может состоять из произвольного числа строк. Структура таблицы определяет количество столбцов в ней, их заголовки (имена) и вид информации, представимой в каждом столбце (числа, символы, тексты, даты и т.п.).

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

5

ЗАДАЧА 1. ПРОГНОЗ ПОГОДЫ НА НЕДЕЛЮ Дан прогноз погоды на неделю по городам России: дневная и ночная температуры воздуха и погодные условия. Необходимо – создать список данных о погоде (содержащий, например, сведения о погоде с 20 по 26 октября 2000 г., приведенные в таблице 1); – определить среднесуточную температуру для каждого города; – выполнить сортировку данных списка; – организовать выбор данных из списка по различным критериям; – определить для каждого региона минимальную и максимальную дневную и ночную температуры.

Этап 1. С о з д а н и е с т р у к т у р ы с п и с к а Для создания списка необходимо определить структуру таблицы (число полей списка, их имена и вид информации, представимой в каждом столбцеполе), и ввести данные в таблицу (см. этап 2.). 1. В заглавной строке таблицы (первой или второй строке рабочего листа) введите имена полей. Если в качестве заглавной строки выбрана вторая строка, то введите в ячейки A2: F2 заголовки столбцов:

Регион,

Город,

День,

Среднесуточная температура,

Ночь, Погодные условия.

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

6

Таблица 1 Прогноз погоды с 20 по 26 октября по городам России Регион

Город

День

Ночь

Погодные условия

6

2

осадки

13

4

без осадков

Север

Архангельск

Поволжье

Астрахань

Дальний Восток

Благовещенск

7

-4

в отдельные дни дожди

Дальний Восток

Хабаровск

7

-4

в отдельные дни дожди

Дальний Восток

Владивосток

13

4

в отдельные дни дожди

Поволжье

Волгоград

7

2

в отдельные дни дожди

Север

Вологда

8

1

в отдельные дни дожди

Север

Воркута

1

-4

в отдельные дни дожди

Центр

Курск

11

4

без осадков

Центр

Воронеж

11

4

без осадков

Центр

Липецк

11

4

без осадков

Сибирь

Иркутск

3

-10

в отдельные дни снег

Сибирь

Чита

3

-10

в отдельные дни снег

Сибирь

Улан-Удэ

3

-10

в отдельные дни снег

Северный Кавказ

Краснодар

16

6

в отдельные дни дожди

Северный Кавказ

Ставрополь

16

6

в отдельные дни дожди

Центр

Москва

10

4

в отдельные дни дожди

Север

Мурманск

5

1

осадки

Поволжье

Нижний Новгород

8

1

без осадков

Сибирь

Омск

0

-6

в отдельные дни дожди

Сибирь

Новосибирск

0

-6

в отдельные дни дожди

Сибирь

Барнаул

0

-6

в отдельные дни дожди

Урал

Екатеринбург

4

-3

в отдельные дни дожди

Урал

Пермь

4

-3

в отдельные дни дожди

Северо-Запад

Новгород

9

3

в отдельные дни дожди

Северо-Запад

Псков

9

3

в отдельные дни дожди

Северный Кавказ

Ростов-на-Дону

13

4

без осадков

Поволжье

Самара

8

0

в отдельные дни дожди

Поволжье

Ульяновск

8

0

в отдельные дни дожди

Центр

Тамбов

8

0

в отдельные дни дожди

7

Окончание таблицы 1 Северо-Запад

Санкт-Петербург

10

3

в отдельные дни дожди

Центр

Тверь

10

3

без осадков

Центр

Тула

10

3

без осадков

Центр

Рязань

10

3

без осадков

Сибирь

Томск

-1

-7

в отдельные дни снег

Сибирь

Кемерово

-1

-7

в отдельные дни снег

Поволжье

Чебоксары

7

1

без осадков

2. В следующей строке определите, если нужно, выражения для вычисляемых полей (введите в соответствующие ячейки формулы для необходимых вычислений). В этой строке можно предварительно выполнить форматирование ячеек, чтобы обеспечить необходимый вид отображения информации в таблице. Как формулы, так и форматы будут автоматически копироваться при заполнении таблицы в новую строку. Введите в ячейку E3 формулу для вычисления среднесуточной температуры =(C3 + D3)/2

3. Если список расположен со второй строки, то можно в первой строке рабочего листа поместить название списка. Для этого введите в ячейку A1 или B1 соответствующий текст, например: Прогноз погоды с 20 по 26 октября по городам России

4. Присвойте рабочему листу содержательное имя, например, Погода

России вместо Лист1 и удалите лишние листы. 5. Сохраните текущее состояние таблицы в личной папке под именем prognos (будет создан файл prognos.xls).

Этап 2. В в о д д а н н ы х в с п и с о к Заполнение списка информацией осуществляется командой Данные/ Форма… строки меню Excel.

8

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

ся диалоговое окно формы будет иметь то же название, что и рабочий лист. В окне формы выводятся заголовки столбцов таблицы в качестве названий полей и окна для ввода данных, курсор стоит в первом окне ввода. Для перехода в следующее окно ввода используется клавиша Таb, для возврата в предыдущее – одновременно нажатые клавиши Shift и Tab. Выбрать любой элемент формы можно щелчком левой кнопки мыши на нём. Доступ к вычисляемым полям блокирован. Значения этих полей автоматически заполняются на основе информации, содержащейся в таблице. Кроме полей, форма содержит индикатор номера записи и ряд кнопок справа от полей ввода. Кнопка Добавить вводит новую строку в конец списка. Кнопка Удалить удаляет текущую запись. Кнопка Вернуть (Восстановить) позволяет восстановить исходное содержание записи, отменяя результаты редактирования, если только Вы не перешли к следующей записи или не нажали клавишу Enter. Для “пролистывания” списка предназначены кнопки Назад (Предыдущая запись) и Далее (Следующая запись), можно также использовать полосу про-

крутки. Кнопка Закрыть закрывает форму и возвращает нас в режим работы с таблицей. Для ввода данных в созданную таблицу - список воспользуйтесь режимом ввода с помощью диалогового окна формы: 1. Выделите любую ячейку заголовка таблицы (A2:F2). 2. Выберите пункт меню Данные/ Форма…

9

3. В открывшемся диалоговом окне формы Погода России (по имени рабочего листа) вводите данные о погоде в соответствующие поля ввода, используя информацию из таблицы 1. Для добавления очередной записи в таблицу используйте кнопку Добавить. Записи добавляются в конец таблицы. Доступ к вычисляемому полю

Среднесуточная температура блокирован. 4. После ввода последней записи нажмите кнопку Закрыть.

Этап 3. Р е д а к т и р о в а н и е с п и с к а Добавление новых записей осуществляется в конец таблицы. Для этого, находясь в пределах списка, выполните п.п.2-4 этапа 2. Для внесения исправлений в записи списка также можно использовать режим формы. Нажатие кнопки Критерии обеспечивает переход в режим поиска данных по критериям. В качестве критериев используются как значения полей, так и условия на значения полей. Для текстовых полей может быть задан поиск значений по частичному совпадению. Например, при поиске всех городов Урала в поле Регион указывается только одна буква У . Если необходимо найти записи о городах, в которых дневная температура выше нуля, а ночная не превышает нуля, то в поле День указывается условие > 0, а в поле Ночь – условие <=0. После нажатия клавиши Enter или кнопки Правка с помощью кнопок Назад и Далее можно просмотреть все записи в таблице, удовлетворяющие

заданному набору критериев – совокупности критериев поиска. Предположим, что нужно уточнить прогноз для городов Сибири, увеличив на один градус отрицательную ночную температуру. Для этого : 1. Вызовите окно диалога формы командой Данные / Форма…

10

2. Нажмите кнопку Критерии. При этом поля формы очистятся, а название кнопки Критерии заменится на Правка. 3. В поле Регион укажите первые две буквы названия региона Си, перейдите с помощью клавиши Таb в поле Ночь и задайте условие <0. 4. Нажмите клавишу Enter или кнопку Правка для возврата в режим просмотра и редактирования данных. 5. Теперь в форме отображаются только необходимые записи. Листая их с помощью кнопок Назад и Далее, внесите исправления в поле Ночь. 6. Закройте окно формы, нажав кнопку Закрыть. Для удаления записи из списка сначала ее нужно найти (см. п.п.1-4), а затем удалить, нажав кнопку Удалить.

Этап 4. С о р т и р о в к а с п и с к а Сортировка - упорядочение данных по возрастанию или убыванию. Сортировка записей - строк списка реализуется в Excel в соответствии с содержимым конкретных столбцов командой Данные / Сортировка...

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

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

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

11

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

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

кнопок

(по возрастанию) и

(по убыванию) на панели Стан-

дартная. При этом ключом сортировки является столбец с текущей ячейкой. Так как в таблице приведен прогноз погоды по городам различных регионов России, удобнее работать не с исходным списком (см. таблицу 1), а со списком, в котором записи отсортированы по городам каждого региона. Для этого: 1. Скопируйте рабочий лист Погода России на новый лист. Назовите новый лист, например, Погода по регионам. 2. Выполните команду Данные / Сортировка… 3. В открывшемся диалоговом окне Сортировка диапазона в двух полях ввода задайте или выберите из списка (раскрывающегося щелчком мыши на кнопке-стрелке) ключи сортировки: Регион, а затем Город. Установите порядок сортировки: для регионов – по убыванию, а для городов – по возрастанию.

12

Упражнение 1.1. Отсортируйте список по двум ключам: а) Регион, Среднесуточная температура. Установите для поля Реги-

он порядок сортировки по возрастанию, а для поля Среднесуточная температура – по убыванию; б) Регион, Погодные условия. Упражнение 1.2. Отсортируйте список по трем ключам: а) Регион, День, Ночь ; б) Регион, Ночь, День. Указание к решению. Для выполнения упражнений 1.1, 1.2. четыре раза

скопируйте рабочий лист Погода по регионам. Назовите новые листы, например, Сортировка 1, Сортировка 2, Сортировка 3 и Сортировка 4.

Этап 5. Ф и л ь т р а ц и я д а н н ы х в с п и с к е Фильтрация позволяет находить и отбирать (отсеивать) для обработки часть записей (строк), которые содержат определенное значение или отвечают определенным критериям. Остальные строки при этом скрыты. В Excel для отбора данных списка (реализации запроса к базе данных) используются Автофильтр и Расширенный фильтр. Расширенный фильтр применяется, если в отборе участвуют более двух

критериев (условий поиска), или если при сравнении используются результаты вычислений. Для установки Автофильтра следует, находясь в пределах списка, выполнить команду Данные / Фильтр / Автофильтр

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

13

В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые.

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

их (связкой И – для случая, когда оба условия должны выполняться одновременно, связкой ИЛИ – для случая, когда достаточно выполнения хотя бы одного из условий). Например, для поиска городов, в которых дневная температура будет колебаться от 2 до 5 градусов, используется условие для поля День : больше или равно

2

И меньше или равно

5

В условиях поиска для текстовых полей можно задавать символы шаблона: *

для указания любой последовательности символов,

?

для представления любого одного символа.

Например, для отбора номеров телефонов АТС-22 следует задавать 22-* Для поиска номера телефона, если Вы сомневаетесь в одной из цифр, укажите, например, так: 22-?6-76

14

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

Отмена режима фильтрации (без уничтожения фильтров) реализуется командой Данные / Фильтр / Показать все

Удаление фильтров ( т.е. отключение

Автофильтра ) производится ко-

мандой Данные / Фильтр / Автофильтр

При обработке данных таблицы прогноза погоды может возникнуть необходимость отбора данных, имеющих определенное значение или удовлетворяющих определенным условиям, то есть организации разнообразных запросов. Предположим, что необходимо выделить из таблицы записи о городах Сибири с целью уточнения данных о ночной температуре. Эта задача уже решалась на этапе 3 с использованием диалогового окна формы. Теперь для выбора данных из таблицы можно воспользоваться режимом Автофильтра. Для этого: 1. Перейдите в рабочий лист Погода России (или Погода по регио-

нам). 2. Выполните команду Данные / Фильтр / Автофильтр В ячейках заголовков столбцов появятся кнопки - стрелки списков фильтров. 3. Для отбора городов Сибири нажмите кнопку со стрелкой в поле

Регион и в списке фильтра выберите строку Сибирь. 4. Для наложения условия на значения ночной температуры нажмите кнопку со стрелкой в поле Ночь и выберите строку Условие. 5. В диалоговом окне Пользовательский автофильтр укажите условие <0 и нажмите кнопку OK.

15

После всех этих действий на экран будет выведена часть таблицы прогноза погоды с необходимыми сведениями. 6. Перемещаясь по выведенным записям городов Сибири, внесите исправления в поле Ночь. 7. Отмените режим фильтрации командой Данные / Фильтр / Показать все

Упражнение 1.3. Выделите из таблицы прогноза погоды записи о городах Северного Кавказа. Упражнение 1.4. Выделите из таблицы записи о городах а) Урала и Сибири; б) Сибири и Дальнего Востока. Упражнение 1.5. Выделите из таблицы записи о городах Сибири, в которых дневная температура выше (ниже) нуля. Упражнение 1.6. Выделите из таблицы записи о городах Центра и Северного Кавказа, в которых дневная температура выше +100C, а ночная – выше +50C. Упражнение 1.7. Выделите из таблицы записи о городах отдельных регионов России, в которых выпадение осадков не предвидится. Замечание. После выполнения упражнений отмените режим фильтрации

(см. п. 7) или удалите фильтры командой Данные / Фильтр / Автофильтр

Этап 6. Ф о р м и р о в а н и е и т о г о в Подведение различных итогов – один из способов обработки списка. Общие и промежуточные итоги Для получения итоговой информации в Excel используется команда Данные/Итоги…, которая позволяет применить одну из итоговых функций к

некоторому полю или полям. При этом итоги формируются не только ко все-

16

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

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

группам записей. Команда Данные/Итоги… вызывает диалоговое окно Промежуточные итоги, содержащее три поля ввода.

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

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

17

Кнопка Убрать все диалогового окна позволяет удалить все итоги из текущего списка. Структурирование таблицы Кроме итоговых строк, команда Данные/Итоги… формирует так называемую структуру, которая соответствует группировке данных для вычисления промежуточных и общих итогов.

Структурирование – это средство, которое позволяет управлять сокрытием или выводом на экран частей рабочего листа. Слева от таблицы с итогами располагаются символы структуры – кнопки “+” и “–“. Кнопка “–” (символ сокрытия деталей) позволяет скрыть группу данных. Кнопка “+” (символ показа деталей), которая появляется после сокрытия данных, позволяет их восстановить. Замечание. Сокрытие данных не влияет на итоговые величины.

К символам структуры относятся и кнопки с изображением цифр (“1”, “2”, ...), обозначающие уровни структуры. Если нажать кнопку с номером уровня, то будут скрыты детальные данные этого уровня и всех низших. Сформированную структуру можно скрыть. Для этого следует выполнить команду Сервис/Параметры… и на вкладке Вид снять флажок Символы

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

18 Формат / Строка / Скрыть

или

Формат / Столбец / Скрыть

Отображение скрытых данных выполняется командой Формат / Строка / Показать

или

Формат / Столбец / Показать

Определение диапазона температур Возможности команды Данные/Итоги… можно применить для определения диапазона дневных и ночных температур для регионов России и страны в целом. Для получения итоговой информации выполните следующие шаги: 1. Скопируйте рабочий лист Погода по регионам и присвойте новому листу другое имя, например, Итоги. 2. Для сокрытия столбца Погодные условия выделите любую его ячейку и выполните команду Формат/Столбец /Скрыть

3. Записи таблицы должны быть отсортированы по регионам. Если список не отсортирован, выполните сортировку по полю Регион (этап 4). 4. Выделите одну из ячеек списка и выполните команду Данные /Итоги… 5. В открывшемся диалоговом окне Промежуточные итоги выполните следующие действия: • Раскройте список в поле ввода При каждом изменении в и выберите то поле, по которому группируются записи для получения итогов. Таким полем в поставленной задаче является поле Регион, т.к. все итоги подводятся по регионам. • В поле ввода Операция по умолчанию будет выбрана функция Сум-

ма, замените ее на Максимум. • В поле ввода Добавить итоги по выберите элементы, по которым подводятся итоги: День и Ночь. При помощи полосы прокрутки раскры-

19

вающегося списка пройдите по всем его элементам и проверьте, что другие элементы не помечены. • Оставьте отметки для флажка Заменить текущие итоги и для флажка Итоги под данными. • Нажмите кнопку ОК. 6. На экране появятся строки с промежуточными итогами для каждой группы записей и общий итог. Обратите внимание на кнопки, которые появились слева от таблицы. Кнопки с цифрами 1,2 и 3 обеспечивают степень детализации показа данных в таблице: кнопка 3 – вся таблица с промежуточными итогами и общим итогом; кнопка 2 – только итоги: промежуточные и общий; кнопка 1 – только общий итог. Той же цели служат и кнопки со знаками «+» и «–». Нажмите на каждую из кнопок и посмотрите, как свернулась структурная схема слева. После этого разверните весь список со всеми итогами, нажав кнопку 3. 7. Добавьте в таблицу итоговые строки по определению минимальных значений температур: • Выполните команду Данные/Итоги… • В поле ввода При каждом изменении в оставьте то же самое значение

Регион. • В поле ввода Операция выберите элемент Минимум. • Для сохранения старых промежуточных итогов уберите отметку с флажка Заменить текущие итоги. Это позволит добавлять новые итоговые строки к уже имеющимся. • Нажмите кнопку ОК. На экране появились новые итоговые строки, добавилась кнопка с номером 4 и изменилась структурная схема детализации.

20

8. Можно улучшить внешний вид таблицы, выполнив ее форматирование. Попробуйте воспользоваться Автоформатом. Для этого выполните команду Формат/Автоформат… и выберите один из вариантов форматирования. В окне просмотра для каждого варианта можно увидеть образец формата. Перед форматированием списка выделите одну из ячеек списка, тогда весь список будет выбран автоматически. Чтобы отформатировать только часть списка, ее следует выделить. 9. Чтобы убрать все итоги, выделите любую ячейку списка, выполните команду Данные/Итоги… и в диалоговом окне Промежуточные итоги нажмите кнопку Убрать все. 10. Для отображения скрытых столбцов используйте команду Формат/Столбец /Показать

Упражнение 1.8. Выделите из таблицы на листе Погода по регионам, используя Автофильтр, записи о городах Центра и Северного Кавказа (см. этап 5); скройте столбец Среднесуточная температура и определите диапазон дневных и ночных температур в городах этих двух регионов (см. этап 6.). Результат должен соответствовать таблице 2. Упражнение 1.9. Используя таблицу, полученную в упражнении 1.8, постройте гистограммы дневных и ночных температур для городов Центра. Указание к решению. Выделите диапазон ячеек, содержащий (включая

заголовки) столбцы с названиями городов Центра, дневными и ночными температурами в них. Вызовите Мастер диаграмм, выберите тип диаграммы – гистограмма и выполните все последующие шаги Мастера диаграмм.

21

Таблица 2 Прогноз погоды с 20 по 26 октября ( Центр и Северный Кавказ ) A

B

C

D

F

Регион

Город

День

Ночь

Погодные условия

Центр

Воронеж

11

4

без осадков

Центр

Курск

11

4

без осадков

Центр

Липецк

11

4

без осадков

Центр

Москва

10

4

в отдельные дни дожди

Центр

Рязань

10

3

без осадков

Центр

Тамбов

8

0

в отдельные дни дожди

Центр

Тверь

10

3

без осадков

Центр

Тула

10

3

без осадков

Центр Минимум

8

0

Центр Максимум

11

4

Северный Кавказ

Краснодар

16

6

в отдельные дни дожди

Северный Кавказ

Ростов-на-Дону

13

4

без осадков

Северный Кавказ

Ставрополь

16

6

в отдельные дни дожди

Северный Кавказ Минимум

14

5

Северный Кавказ Максимум

16

6

Общий максимум

16

6

Общий минимум

8

0

Этап 7. З а щ и т а д а н н ы х Защитить данные в Excel можно двумя способами: используя средства запрещения доступа к данным и средства сокрытия данных. Защита книги и листа Защитить рабочую книгу в целом можно с помощью команды Сервис / Защита / Защитить книгу…

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

22

Защита отдельного листа производится командой Сервис / Защита / Защитить лист…

При защите и книги, и листа можно указать пароль, который задается с подтверждением в “секретном” режиме (в виде последовательности звездочек). Снять защиту с рабочего листа можно, выполнив команду Сервис / Защита / Снять защиту листа…

Снятие защиты с рабочей книги осуществляется аналогичной командой Сервис / Защита / Снять защиту книги… Замечание. Если защита была дополнена паролем, то снять защиту мож-

но будет только в том случае, если известен пароль. Защита ячеек рабочего листа Для того чтобы защитить лист, но при этом разрешить изменение содержимого части ячеек, необходимо с этих ячеек снять статус “защищаемая ячейка”, а затем защитить рабочий лист. Для этого нужно выполнить следующие действия: • Снять защиту с листа, если он был защищен. • Выделить все ячейки, содержимое которых может изменяться. • Выполнить команду Формат / Ячейки и во вкладке Защита сбросить флажок Защищаемая ячейка. • Выполнить команду Сервис / Защита / Защитить лист…

Сокрытие рабочего листа Скрыть (отобразить ) рабочий лист можно командой Формат / Лист / Скрыть

( Формат / Лист / Показать )

Сокрытие (отображение) всей книги выполняется командой Окно / Скрыть ( Окно / Показать )

Сокрытие формул

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

23

команды Формат/Ячейки… во вкладке Защита следует установить флажок

Скрыть формулы. Защита данных листа Погода России На листе Погода России можно защитить от изменения заголовок таблицы и заголовки столбцов, столбец Среднесуточная температура, а также скрыть формулы в этом столбце. Для этого: 1. Если лист Погода России был защищен, снимите с него защиту командой

Сервис /Защита /Снять защиту листа…

2. Выделите столбцы A – D и F, содержимое которых может меняться. Выполните команду Формат/Ячейки… и во вкладке Защита сбросьте флажок

Защищаемая ячейка. 3. Выделите столбец E, формулы в котором должны быть скрыты. Выполните команду Формат/Ячейки… и во вкладке Защита установите флажки

Защищаемая ячейка и Скрыть формулы. 4. Выделите строки 1 и 2, которые должны быть защищены. Выполните команду Формат/Ячейки… и во вкладке Защита установите флажок Защи-

щаемая ячейка. 5. Выполните команду Сервис/Защита /Защитить лист…

Упражнение 1.10. Обеспечить защиту данных листа Погода по регио-

нам, полученного на этапе 4.

24

ЗАДАЧА 2. СТРАНЫ МИРА Даны краткие сведения о государствах мира: столица, площадь, население. Необходимо создать список (см., например, таблицу 3), содержащий исходную информацию о странах, и определить для каждой страны плотность населения (чел./кв.км) – отношение численности населения страны к ее площади. Таблица 3 Страны мира Часть света

Страна

Столица

Территория Население (тыс. кв. (тыс. чел.) км)

Европа

Франция

Париж

544

55 282

Европа

Италия

Рим

301

57 074

Америка

США

Вашингтон

9 363

240 468

Азия

Япония

Токио

372

120 030

Азия

Китай

Пекин

9 597

1 055 052

Азия

Индия

Дели

3 288

746 742

Европа

Великобритания Лондон

244

56 488

Европа

Испания

Мадрид

505

38 600

Африка

Египет

Каир

1 001

48 600

Африка

Алжир

Алжир

2 382

21 050

Африка

ЮАР

Претория

1 221

31 010

Америка

Канада

Оттава

9976

25 500

Америка

Мексика

Мехико

1 973

78 524

Америка

Бразилия

Бразилиа

8 512

135 560

Америка

Аргентина

БуэносАйрес

2 777

30 560

Европа

Финляндия

Хельсинки

337

4 911

Азия

Индонезия

Джакарта

2 027

159 434

Азия

Монголия

Улан-Батор

1 567

1 866

Танзания

Дар-эсСалам

945

21 000

Африка

25

Америка

Колумбия

Богота

1 139

28 620

Америка

Куба

Гавана

111

10 152

Для этого задайте заголовок и “шапку” таблицы (заголовки столбцов):

Часть света, Страна, Столица, Территория (тыс.кв.км), Население (тыс.чел.), Плотность населения. Столбец Плотность населения является вычисляемым полем. Причем, если для какой-либо страны не будет указана площадь, то в соответствующей ячейке столбца Плотность населения появится сообщение об ошибке в виде #ДЕЛ/0! Чтобы этого не произошло, воспользуйтесь логической функцией ЕСЛИ. Например, если в ячейках D3 и E3 указаны площадь и население, то функция, определяющая плотность населения, может быть задана так =ЕСЛИ(D3="";"";E3/D3)

Упражнение 2.1. Данные о численности населения даны в таблице на 1984-1986 гг. Скопируйте лист и отредактируйте список, используя диалоговое окно формы, в соответствии с данными последней переписи населения. Упражнение 2.2. Внесите в список, используя диалоговое окно формы, данные о странах, отсутствующих в таблице 3. Упражнение 2.3. Выполните сортировку по следующим полям: а) Территория; б) Население; в) Плотность населения; г) Часть света, Страна; д) Часть света, Территория; е) Часть света, Население; ж) Часть света, Плотность населения.

26

Указание к решению. Для каждого поля установите свой порядок сор-

тировки: по возрастанию или по убыванию. Упражнение 2.4. Выделите, используя Автофильтр, из списка записи о странах: а) одной из частей света; б) с населением свыше 100 млн. чел.; в) с населением менее 50 млн. чел.; г) с населением от 50 до 100 млн. чел.; д) площадью менее 500 тыс.кв.км; е) с максимальной площадью (10 первых); е) с минимальной (максимальной) плотностью населения (5 первых). Упражнение 2.5. Определите, используя команду Итоги, максимальную и минимальную площадь, численность и плотность населения для государств из списка в целом и для каждой части света. Упражнение 2.6. Определите среднюю плотность населения стран Азии, последовательно используя команды Итоги и Автофильтр. Упражнение 2.7. Определите среднюю плотность населения стран Америки, за исключением США, используя команды Итоги и Автофильтр.

27

ЛИТЕРАТУРА 1. Амелина Н.И., Мачулина Л.А., Чердынцева М.И. Практикум по электронным таблицам в экономике. - М.: "Издательство ПРИОР", 2000. 2. Гончаpов А. Excel 7.0 в пpимеpах.- СПб: Питеp, 1996. 3. Долголаптев В.Г. Работа в Excel 7.0 для Windows 95 на пpимеpах. - М: БИHОМ, 1995. 4. Hиколь H., Альбpехт Р. Электpонные таблицы Excel 5.0 для квалифициpованных пользователей. - М: ЭКОМ, 1995. 5. Русанова Я.М., Спивак И.Г. Методические указания по курсу "Информатика" для студентов геолого-географического факультета. Часть 4. Электронные таблицы Excel. - Ростов-на-Дону, УПЛ РГУ, 2001. ПРИЛОЖЕНИЕ. СООБЩЕНИЯ ОБ ОШИБКАХ #####

Размер ячейки недостаточен для размещения числа или результата

#ДЕЛ/0!

Деление на ноль

#ЗНАЧ!

Недопустимый тип аргумента или операнда

#ИМЯ?

Неверное имя функции или области

#Н/Д

Неопределенные данные

#ПУСТО!

Задано пересечение двух областей, не имеющих общих ячеек

#ССЫЛКА! Недопустимая ссылка на ячейку #ЧИСЛО!

Ошибка в вычислениях