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

Министерство образования Российской Федерации РОСТОВСКИЙ ОРДЕНА ТРУДОВОГО КРАСНОГО ЗНАМЕНИ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

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

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

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

Печатается

по

решению

кафедры

прикладной

математики

и

программирования от 31 мая 2001 г.

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

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

3

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

4

1. Структура документа Excel . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

2. Объекты рабочего листа . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5

3. Выделение объектов электронной таблицы . . . . . . . . . . . . . .

6

4. Вычисления в таблице . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7

5. Относительные и абсолютные ссылки . . . . . . . . . . . . . . . . . .

8

6. Копирование формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

9

7. Сохранение и открытие документа Excel . . . . . . . . . . . . . . . .

10

ЗАДАЧА 1. Регионы России . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

11

Этап 1. Создание документа . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

12

Этап 2. Определение структуры таблицы . . . . . . . . . . . . . . . . . .

15

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

17

Этап 4. Выполнение расчетов в таблице . . . . . . . . . . . . . . . . . . .

19

Этап 5. Форматирование таблицы . . . . . . . . . . . . . . . . . . . . . . . .

24

ЗАДАЧА 2. Лесные ресурсы России . . . . . . . . . . . . . . . . . . . . . . . . . .

25

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

27

Этап 2. Выполнение расчетов в таблице . . . . . . . . . . . . . . . . . . .

28

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

31

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

31

4

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

цессорами или электронными таблицами. При работе с табличным процессором таблица данных изображается на экране, и можно организовать просмотр любой ее части. Для производства расчетов в нужные места таблицы вносятся формулы. При изменении данных осуществляется автоматический пересчет формул, связанных с изменяемыми данными. Графические средства электронных таблиц позволяют представлять данные таблицы в виде графиков и диаграмм [2]. 1. СТРУКТУРА ДОКУМЕНТА EXCEL Табличный процессор Excel – это программа, работающая под управлением операционной системы Windows и, следовательно, являющаяся ее

приложением. Файл, обрабатываемый любым приложением, называется документом. Документ, создаваемый и обрабатываемый приложением Excel, называется книгой, или рабочей книгой. Рабочие книги сохраняются как файлы с расширением .xls. При запуске Excel открывается новый документ с именем Книга1. Новая рабочая книга стандартно содержит определенное число рабочих листов, задаваемое параметрами настройки (для Excel 97 – три листа) с именами

Лист1, Лист2, . . . Имена листов рабочей книги показываются в виде ярлычков в нижней части окна. Активный лист выделен, его содержимое вид-

5

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

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

Стандартная и Форматирование. Убрать или добавить панели инструментов можно, выбрав в строке меню пункт Вид и в раскрывшемся меню строку Панели инструментов. Затем в открывшемся списке панелей щелчком мыши

следует поставить или убрать отметку перед именем требуемой панели. 2. ОБЪЕКТЫ РАБОЧЕГО ЛИСТА Рабочий лист Excel разделен на столбцы и строки. Заголовки столбцов – имена A, B, C, …, Z, AA, AB, …, AZ, BA, BB, …, BZ, …, IV (всего – 256). Заголовки строк – числа 1, 2, 3, … (до 16384 и более). Перемещение по строкам и столбцам таблицы выполняется с помощью горизонтальной и вертикальной полос прокрутки или клавишами управления курсором. Пересечение столбца и строки – ячейка. Ячейка имеет адрес, который складывается из имени столбца и номера строки, например: A1, C5, F12. Адрес ячейки иначе называется ссылкой на ячейку. Активная ячейка (выделенный жирной рамкой прямоугольник) доступна для ввода и редактирования

6

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

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

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

Текст – это любая последовательность символов. Признаком формулы является символ « = », с которого она начинается.

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

Формат включает в себя форму представления (например, процентный или денежный), тип и размер шрифта, начертание и т.д. 3. ВЫДЕЛЕНИЕ ОБЪЕКТОВ ЭЛЕКТРОННОЙ ТАБЛИЦЫ При работе с электронной таблицей все действия выполняются над выделенным объектом. Таким объектом может быть одна ячейка или совокупность ячеек. Совокупность ячеек – это строка, столбец, диапазон строк или столбцов, а также блок (прямоугольный фрагмент таблицы) смежных или несмежных ячеек. Выделение объекта электронной таблицы выполняется следующим образом: • ячейки – щелчком левой кнопкой мыши на ячейке, • строки – щелчком левой кнопкой мыши на заголовке (номере) строки, • столбца – щелчком левой кнопкой мыши на заголовке столбца,

7

• диапазона строк, столбцов, блока смежных ячеек – протягиванием мыши при нажатой левой кнопке или с помощью клавиш управления курсором при нажатой клавише Shift, • блока несмежных ячеек – каждый несмежный диапазон выделяется при нажатой клавише Ctrl, • всего листа – щелчком левой кнопкой мыши на прямоугольнике, находящимся на пересечении заголовков строк и столбцов таблицы. Над выделенным объектом таблицы можно выполнять разнообразные действия: форматировать его, перемещать, удалять, копировать в буфер, использовать для построения диаграмм и т.п. 4. ВЫЧИСЛЕНИЯ В ТАБЛИЦЕ Вычисления в электронных таблицах выполняются с помощью формул.

Формула – это выражение, состоящее из операндов, соединенных знаками операций. Формула в Excel начинается с символа « = ».

Операндами могут быть – числа ( целые и вещественные, в том числе и в экспоненциальной форме, например, 5.6E-4, что равно 0.00056), – адреса ячеек, – функции ( математические, статистические, функции даты и времени, финансовые и другие ), – выражения в круглых скобках ( арифметические или логические ). В арифметических выражениях используются знаки арифметических операций: + - сложение, - - вычитание, * - умножение, / - деление, % - процент, ^ - возведение в степень.

8

В логических выражениях используются знаки операций сравнения: = < > <= >= <>

- равно, - меньше, - больше, - меньше или равно, - больше или равно, - не равно.

Если в ячейке записана формула, то в ней виден результат вычислений, а сама формула отображается в строке формул, если сделать эту ячейку активной. Если значение формулы не может быть вычислено, то в ячейке появится сообщение об ошибке, например: #ДЕЛ/0! ( деление на 0 ) или #ИМЯ? ( опечатка в формуле ). Если вычисленное значение не помещается в видимую часть ячейки, то выдается сообщение об ошибке в виде ###### . Перечень сообщений об ошибках табличного процессора Excel приведен в приложении. 5. ОТНОСИТЕЛЬНЫЕ И АБСОЛЮТНЫЕ ССЫЛКИ Одним из возможных операндов формулы являются адреса ячеек –

ссылки. Ссылки бывают относительные и абсолютные. Относительная ссылка – это обычный адрес ячейки, например: B2, C3, F5. Если скопировать формулу с относительной ссылкой, то относительные ссылки в ней изменятся в соответствии с новым местоположением ячейки, а именно, в относительных адресах происходит смещение на величину переноса. Таким образом, относительные ссылки при копировании формулы изменяются. Однако иногда бывает необходимо, чтобы адрес ячейки в копируемой формуле не изменялся, т.е. необходимо зафиксировать его. Для того чтобы управлять изменением ссылок при копировании, вводится понятие абсолют-

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

9

$C5, C$5, $C$5. При копировании будут изменяться только те атрибуты адреса, перед которыми не стоит символ “ $ ”. Ссылка типа $C$5 при копировании остается неизменной. Существует простой способ получения любого из вариантов абсолютной ссылки. Для этого используется функциональная клавиша F4, нажатие на которую приводит к автоматическому изменению адресации, если ссылка на ячейку находится в строке формул. Например, в строку формул поместили ссылку на ячейку C5. Если после этого нажать клавишу F4, то ссылка примет вид $C$5, если еще раз нажать на клавишу F4, то ссылка будет иметь вид C$5, еще одно нажатие приведет к виду $C5, если нажать клавишу F4 еще один раз, то снова получим ссылку C5. 6. КОПИРОВАНИЕ ФОРМУЛ Довольно часто необходимо выполнять расчеты по одной и той же формуле для совокупности исходных данных, расположенных в диапазоне (блоке) ячеек. В этом случае достаточно набрать формулу в первой ячейке диапазона для результатов и скопировать ее на весь диапазон. Копирование формулы из одной ячейки на весь диапазон можно выполнить различными способами. Приведем один из способов копирования формулы на диапазон ячеек. • Введите формулу в первую ячейку диапазона. • Выделите ячейку с формулой и подведите курсор мыши к маркеру

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

10

7. СОХРАНЕНИЕ И ОТКРЫТИЕ ДОКУМЕНТА EXCEL После создания новой рабочей книги рекомендуется сохранить ее, дав сохраняемому документу уникальное имя. В процессе работы с документом рекомендуется регулярно сохранять его во избежание потери информации. Сохраненный документ в дальнейшем можно использовать для проведения новых расчетов или редактирования. Сохранение новой рабочей книги Excel выполняется в стандартном диалоговом

окне

Файл/Сохранить

Windows,

которое

вызывается

командами

или Файл/Сохранить как… или нажатием на кнопку

Сохранить панели инструментов Стандартная.

Если папка, в которую необходимо выполнить сохранение, является текущей, т.е. ее имя отображается в окне списка Папка, то в поле ввода Имя файла введите имя, которое вы хотите присвоить файлу, и нажмите кнопку Сохранить. Excel сохранит файл, дав ему расширение .xls, и имя файла появит-

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

– Переход на один уровень вверх (в некоторых случаях может

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

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

11

ЗАДАЧА 1. РЕГИОНЫ РОССИИ Экономический район (регион) – это хозяйственная территория, выделяющаяся внутри страны экономико-географическим положением, специализацией и структурой производства, природными и людскими ресурсами. Макрорегионы (зоны) – это крупные экономические зоны с характерными природными и экономическими условиями развития производительных сил, зависящими от сочетания и концентрации природных ресурсов, исторических факторов хозяйственного становления и регионального распределения населения. Выделяются два макрорегиона – европейская (западная) и азиатская (восточная) зоны России. Их демографические и природные ресурсы существенно различны. Определения зон как западной и восточной используются для описания природных ресурсов и экономических процессов, а как европейская и азиатская они называются при описании демографических процессов. На территории европейской зоны сконцентрировано более 78% населения России, здесь производится 73% товарной продукции промышленности и 76% продукции сельского хозяйства. В тоже время основные природные ресурсы сосредоточены в восточной зоне страны, например, более 86% разведанных запасов топливных ресурсов. В таблице 1 приведены данные о территории и численности населения по отдельным экономическим районам европейской и азиатской частей России на 1 января 1996 года [7]. Необходимо создать электронную таблицу, которую сначала следует заполнить данными из таблицы 1, а затем на основе этих данных определить: – плотность населения в каждом экономическом районе и в целом по России; – значения площади каждого экономического района в процентном отношении к территории России (удельный вес района в общероссийской территории);

12

– значение численности населения каждого экономического района в процентном отношении к общей численности населения России. Вид и значения результатов должны соответствовать таблице 2. Таблица 1 Территория и население регионов России

Азиатская часть

Россия

Европейская часть

Экономический район Северный

Территория (тыс.кв.км)

Население (тыс.чел.)

1466,3

5947

196,5

8093

485

30005

Волго-Вятский

263,4

8473

Центрально-Черноземный

167,7

7879

Поволжский

536,4

16896

Северо-Кавказский

355,1

17670

Уральский

824

20488

Калининградская область

15,1

926

Западно-Сибирский

2428,4

15139

Восточно-Сибирский

4120,6

9166

Дальневосточный

6215,9

7625

Северо-Западный Центральный

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

Этап 1. С о з д а н и е д о к у м е н т а На этом этапе предстоит создать новый документ Excel, дать ему имя и сохранить в личной папке. Личная папка студента должна быть предварительно создана. При запуске Excel открывается новый документ, содержащий, как правило, не один рабочий лист. Для решения данной задачи достаточно одного рабочего листа, поэтому вам предстоит удалить все листы, кроме одного; дать

13

ему содержательное имя и сохранить документ в личной папке, дав ему уникальное имя. Таблица 2 Территория и население регионов России Экономический район Россия

Террито- Населе- Плотность Террито- Населерия ние населения рия (в %) ние (в %) (тыс.кв.км) (тыс.чел.) (чел./кв.км) 17074,4

148307

Европейская часть

4309,5

116377

27,0

25,24%

78,47%

Северный

1466,3

5947

4,1

8,59%

4,01%

196,5

8093

41,2

1,15%

5,46%

485

30005

61,9

2,84%

20,23%

Волго-Вятский

263,4

8473

32,2

1,54%

5,71%

Центрально-Черноземный

167,7

7879

47,0

0,98%

5,31%

Поволжский

536,4

16896

31,5

3,14%

11,39%

Северо-Кавказский

355,1

17670

49,8

2,08%

11,91%

Уральский

824

20488

24,9

4,83%

13,81%

Калининградская область

15,1

926

61,3

0,09%

0,62%

12764,9

31930

2,5

74,76%

21,53%

Западно-Сибирский

2428,4

15139

6,2

14,22%

10,21%

Восточно-Сибирский

4120,6

9166

2,2

24,13%

6,18%

Дальневосточный

6215,9

7625

1,2

36,40%

5,14%

Северо-Западный Центральный

Азиатская часть

8,7 100,00%

100,00%

1. Запустите табличный процессор Excel. Изучите объекты окна Excel и инструменты, с помощью которых вам предстоит работать. Это, прежде всего, команды, вызываемые из строки меню, и команды, вызываемые кнопками на панелях инструментов. 2. Удалите все листы рабочей книги, кроме первого листа с именем

Лист1. Для этого: • Щелкните левой кнопкой мыши на ярлычке листа с именем Лист2. • Нажмите клавишу Shift и, не отпуская её, щелкните левой кнопкой мыши на ярлычке последнего листа. Отпустите клавишу Shift – листы со второго до последнего будут выделены. • Не выводя курсор мыши из зоны ярлычков, нажмите правую кнопку мыши, вызывая контекстное меню.

14

• Выберите в контекстном меню пункт Удалить. • В появившемся диалоговом окне нажмите кнопку ОК. 3. Переименуйте оставшийся рабочий лист. Для этого: • Установите курсор мыши на ярлычке листа Лист1, нажмите правую кнопку мыши для вызова контекстного меню. • Выберите в контекстном меню пункт Переименовать, имя листа выделится инверсным цветом. • Наберите на клавиатуре новое имя листа, например, Регионы России. Нажмите клавишу Enter. 4. Сохраните рабочую книгу в личной папке. Для этого: • Установите курсор мыши на пункт Файл строки меню и щелкните левой кнопкой. • В открывшемся меню выберите пункт Сохранить (при первом сохранении) или Сохранить как …(при сохранении книги под новым именем). Откроется диалоговое окно сохранения документа, в котором в качестве текущей папки предлагается папка Мои документы. Но для студентов рекомендуется сохранять файлы в личных папках, вложенных в папку группы. • Поэтому сначала перейдите в папку группы. Нужный диск и папку группы можно выбрать с помощью списка Папка (нажав на кнопкустрелку), а также дважды щелкая мышью на изображении папок в окне под списком Папка. • Выделите папку группы, двойным щелчком левой кнопки мыши раскройте её, будут показаны личные папки студентов. Выделите свою папку и раскройте её двойным щелчком мыши. В н и м а н и е! Если вы забыли предварительно создать личную папку, это можно сделать в окне сохранения документа, нажав кнопку

.

15

• В поле ввода Имя файла наберите имя сохраняемого документа, например, region и нажмите кнопку Сохранить (будет создан файл region.xls).

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

Сохранить

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

Файл/Сохранить. Сохранение происходит быстро и иногда почти незаметно.

Этап 2. О п р е д е л е н и е с т р у к т у р ы т а б л и ц ы Определить структуру таблицы – значит определить количество столбцов таблицы, их заголовки и вид информации, размещаемой в каждом столбце таблицы. В рассматриваемой задаче объектом анализа является экономический район России. Нас интересуют некоторые характеристики (свойства) этого объекта. Такими свойствами являются: – название экономического района, – территория района (тыс.кв.км), – численность населения района (тыс.чел.), – плотность населения (чел./кв.км) – площадь района в % к общей площади, – численность населения в % к общей численности населения. Перечисленные свойства и будут выступать в качестве заголовков столбцов создаваемой таблицы. Строки таблицы будут соответствовать значениям свойств конкретных экономических районов. Сразу заметим, что значения таких свойств, как название района, территория и население (в абсолютных единицах) являются известными и приведе-

16

ны в таблице 1. А значения таких свойств, как плотность населения, территория (в %) и население (в %) необходимо вычислить и получить те же значения, что и в таблице 2. Кроме того, обратите внимание на то, что в таблице 2 по сравнению с таблицей 1 появились новые результирующие строки для районов европейской и азиатской частей и в целом по России. Таким образом, количество и содержание столбцов таблицы определено. Собственно заголовки столбцов таблицы следует разместить во второй строке рабочего листа, а в первой строке поместите заголовок всей таблицы, но сделайте это после того, как таблица будет построена и заполнена данными (см. этап 5). Для формирования заголовков столбцов таблицы выполните следующие действия. 1. Введите заголовок первого столбца в ячейку A2. Для этого: • Щелчком левой кнопки мыши выделите ячейку A2. • Наберите на клавиатуре текст Экономический район. Завершите ввод одним из способов: нажатием клавиши Enter, щелчком на кнопке с зеленой галочкой в строке формул или щелчком мыши на любой ячейке. (Более подробно особенности ввода и редактирования данных будут описаны на следующем этапе). • Введенный текст как бы захватил и ячейку B2, но это пока в ячейку B2 ничего не введено. Если ячейка B2 не будет пустой, то в ячейке A2 будет видна только часть текста, ограниченная шириной столбца. Поэтому следует увеличить ширину столбца. Для этого переведите курсор мыши в зону заголовков столбцов и установите его на границу между столбцами A и B так, чтобы он принял вид двунаправленной стрелки. Нажмите левую кнопку мыши и потяните курсор, перемещая границу и увеличивая ширину столбца до нужного размера. Отпустите кнопку мыши.

17

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

5. Сохраните текущее состояние документа.

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

18

кнопка Enter (зеленая галочка) – подтверждение ввода, при этом по завершении ввода активной остается та же самая ячейка. Пока ячейка находится в режиме ввода, некоторые кнопки и команды меню становятся недоступными. Завершить ввод можно нажатием на кнопку Enter

в строке формул или другим возможным способом: нажать клавишу

Enter или щелкнуть мышью на другой ячейке, или с помощью клавиш управ-

ления курсором перейти на другую ячейку. Для редактирования данных в ячейке необходимо выделить ее и нажать на клавиатуре клавишу F2 или установить в выделенной ячейке курсор мыши (он должен иметь вид толстого белого крестика) и дважды нажать левую кнопку мыши. Еще один способ – выделить ячейку и щелчком мыши установить курсор в строке формул. В любом из этих случаев устанавливается режим Правки, о чем свидетельствует сообщение в строке состояния. На этом этапе необходимо заполнить электронную таблицу исходными данными из таблицы 1. Для этого: 1. Введите в ячейку A3 текст Россия. 2. Введите в ячейку A4 текст Европейская часть. 3. Введите в ячейки A5:A13 названия экономических районов европейской части России из таблицы 1. Завершать ввод в ячейку удобнее всего нажатием клавиши Enter, так как в этом случае активной становится ячейка в следующей строке этого же столбца (это обусловлено стандартными параметрами настройки). 4. Введите в ячейку A14 текст Азиатская часть. 5. Введите в ячейки A15:A17 названия экономических районов азиатской части России из таблицы 1. 6. При необходимости увеличьте ширину столбца A. 7. Введите в ячейки B5:B13 значения территории районов европейской части России из таблицы 1 (десятичным разделителем целой и дробной части

19

является запятая). Обратите внимание на то, что все числа прижаты к правой границе поля – это стандартная установка для числовых данных. 8. Введите в ячейки B15:B17 значения территории районов азиатской части России из таблицы 1. 9. Введите в ячейки C5:C13 значения численности населения районов европейской части, а в ячейки C15:C17 значения численности населения азиатской части – данные из таблицы 1. 10. Сохраните текущее состояние документа. В н и м а н и е! Не вводите значения в ячейки B3, B4, B14 и в ячейки C3, C4, C14, так как их содержимое является результатом вычислений, которые предстоит выполнить на следующем этапе.

Этап 4. В ы п о л н е н и е р а с ч е т о в в т а б л и ц е Для организации вычислений в таблице используются формулы. Формула начинается со знака « = ». Начнем вычисления в таблице с того, что вычислим суммарные значения территории и численности населения отдельно для европейской и азиатской частей, а затем, взяв их сумму, получим соответствующие значения в целом по России. Для некоторых наиболее часто встречающихся действий на панелях инструментов имеются кнопки. В частности, для суммирования диапазона ячеек предусмотрена кнопка Автосумма

на панели инструментов

Стандартная. 1. Для вычисления площади европейской части России выполните следующие действия: • Выделите ячейку B4, в которой необходимо получить сумму. • Нажмите кнопку

на

панели

Стандартная. В строке формул и в ячейке появится выражение

инструментов

20 =СУММ()

При этом текстовый курсор будет находиться внутри круглых скобок. • Установите курсор мыши (широкий белый крестик) на первую ячейку диапазона, участвующего в суммировании – это ячейка B5. • Нажмите левую кнопку мыши и протяните её вниз до ячейки B13 включительно. Диапазон B5:B13 будет обрамлен бегущей рамкой, а в ячейке B4 и в строке формул появится формула =СУММ(B5:B13)

• Завершите ввод одним из возможных способов, например, нажав клавишу Enter. В ячейке B4 появится числовое значение. 2. Для вычисления площади азиатской части России выполните действия, аналогичные описанным выше: • Выделите ячейку B14, в которой необходимо получить сумму. • Нажмите кнопку

на

панели

инструментов

Стандартная. В строке формул и в ячейке появится выражение, содержащее функцию СУММ, но, в отличие от предыдущего применения, в круглых скобках уже содержится диапазон, который система устанавливает по умолчанию. В ячейке этот диапазон выделен цветом, а в таблице обрамлен мерцающей рамкой. Но это не тот диапазон, который необходим в данном случае. • Выделите нужный диапазон. Для этого установите курсор мыши на ячейку B15 – первую ячейку диапазона, участвующего в суммировании. • Нажмите левую кнопку мыши и протяните её вниз, выделяя диапазон до ячейки B17 включительно. Диапазон B15:B17 будет обрамлен бегущей рамкой, а в ячейке B14 и в строке формул появится формула =СУММ(B15:B17)

• Завершите ввод. В ячейке B14 появится числовое значение.

21

3. Для вычисления площади России необходимо сложить площадь европейской части и площадь азиатской части. Для этого в ячейку B3 необходимо поместить формулу =B4+B14

но не спешите её набирать, а прочитайте ниже как это можно сделать наиболее эффективным способом: • Щелчком мыши выделите ячейку B3. • Наберите на клавиатуре признак формулы – символ « = ». • Щелкните мышью на ячейке B4 – ссылка на ячейку B4 будет добавлена в формулу. • Наберите на клавиатуре символ « + ». • Щелкните мышью на ячейке B14 – ссылка B14 будет добавлена в формулу, которая примет вид =B4+B14

• Завершите ввод. В ячейке B3 появится числовое значение. 4. Аналогичным образом (см. п.п. 1-3 этого этапа) вычислите в ячейках C4, C14 и C3 численность населения в европейской части, в азиатской части и в целом по России соответственно. Вычисление плотности населения Плотность населения вычисляется как отношение численности населения к площади и измеряется в количестве человек на квадратный км (чел./кв.км). В рассматриваемом примере численность населения задается в тысячах человек, а площадь – в тысячах кв.км. При взятии отношения тысячи сокращаются, и результат будет выражен в нужных единицах. Для всего столбца, в котором вычисляется плотность населения необходимо в каждой строке выполнить одно и то же действие, а именно, численность населения разделить на площадь. Однако нет необходимости набирать соответствующую формулу для вычисления в каждой строке таблицы, так как

22

в Excel предусмотрен механизм, позволяющий копировать содержимое ячейки или ячеек на диапазон. Для вычисления плотности населения выполните следующие действия: 1. Введите в ячейку D3 формулу =C3/D3

набирая на клавиатуре знаки операций, а ссылки на ячейки получая щелчком мыши. Завершите ввод. 2. Скопируйте формулу из ячейки D3 на диапазон D4:D17. Для этого: • Выделите ячейку D3. • Подведите курсор мыши к маркеру заполнения (маленькому черному квадратику в правом нижнем углу ячейки), добейтесь того, чтобы курсор принял вид тонкого черного крестика. • Нажмите левую кнопку мыши и протяните курсор до конца диапазона, на который копируется формула, выделяя его (в данном случае протяните курсор мыши до ячейки D17); отпустите кнопку мыши. В ячейках появятся значения, а диапазон заполнится формулами. • Выделите ячейку D4, чтобы посмотреть, как видоизменилась формула при копировании, формула показывается в строке формул и имеет вид =C4/B4

3. Диапазон D3:D17 заполнится числовыми значениями, для которых система по умолчанию устанавливает числовой формат общий, т.е. показывает столько цифр дробной части, сколько получилось при вычислении, возможно округляя младшие разряды, чтобы число вписалось в ширину столбца. Измените формат диапазона D3:D17 так, чтобы дробная часть числа содержала одну цифру. Для этого: • Выделите диапазон D3:D17. • Выполните команду Формат/Ячейки…, в появившемся диалоговом окне Формат ячеек выберите вкладку Число (если она не выбрана). • В окне Числовые форматы выберите формат Числовой.

23

• В поле ввода Число десятичных знаков введите или с помощью стрелок счетчика установите значение 1. Нажмите ОК. Удельный вес района в общероссийской территории Для определения удельного веса необходимо для каждого экономического района найти отношение площади района к площади России и результат умножить на 100, или, что то же самое, установить процентный формат. При решении этой задачи также следует ввести формулу для вычисления в одну ячейку, а затем скопировать её на необходимый диапазон. Но делителем во всех формулах диапазона является одно и то же значение – площадь России (содержимое ячейки B3) и нужно позаботиться, чтобы при копировании в формуле делителем всегда оставалась ячейка B3. Для этого её нужно записать как абсолютную ссылку, т.е. в виде $B$3 (см. раздел 5): 1. Введите в ячейку E4 формулу =B4/$B$3

Для этого: • Выделите ячейку E4. • Наберите на клавиатуре символ « = ». • Щелкните мышью на ячейке B4, ссылка на неё попадет в формулу. • Наберите на клавиатуре символ « / ». • Щелкните мышью на ячейке B3, ссылка на неё также попадёт в формулу, а теперь нажмите функциональную клавишу F4, и ссылка B3 превратится в $B$3. Завершите ввод. 2. Выделите ячейку E4 и скопируйте ее на диапазон E5:E17, используя маркер заполнения (описанным выше способом). Диапазон E4:E17 заполнится числовыми значениями в общем формате. 3. Для установления процентного формата с двумя цифрами дробной части выполните следующие действия: • Выделите диапазон E4:E17.

24

• Нажмите кнопку

– Процентный формат на панели инстру-ментов

Форматирование. Значения диапазона будут представлены в процентном формате и округлены до целых чисел. • Для того, чтобы вывести две цифры дробной части (диапазон попрежнему выделен) нажмите два раза на кнопку

–

Увеличить разрядность на панели Форматирование.

4. Выделите ячейку E4 и скопируйте её на ячейку E3, в которой должно получиться 100,00%. Аналогичным образом заполните расчетными формулами диапазон ячеек F3:F17, что позволит получить значения численности населения в экономических районах в процентном отношении к общей численности.

Этап 5. Ф о р м а т и р о в а н и е т а б л и ц ы Форматирование данных в таблице – это способ более наглядного представления данных, обеспечивающий их лучшее восприятие. По умолчанию все объекты рабочего листа представляются в определенном формате. Например, в ячейке, содержащей числовые данные, значения прижимаются к правой границе ячейки, а текстовые данные прижимаются к левой границе ячейки. Форматирование выделенного объекта может быть выполнено с использованием кнопок панели инструментов Форматирование или команд меню. 1. Введите в ячейку A1 заголовок таблицы Территория и население регио-

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

– Объединить и поместить в

центре. Можно изменить размер шрифта заголовка и его начертание.

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

25

ды Формат/Ячейки… Рекомендуется сначала провести все внутренние рамки и линии, а затем – внешние, при этом можно изменить тип и толщину линий. 3. Можно изменить шрифт, его размер и начертание, а также цвет фона и цвет шрифта отдельных элементов таблицы, нажав соответствующие кнопки панели Форматирование.

26

ЗАДАЧА 2. ЛЕСНЫЕ РЕСУРСЫ РОССИИ По состоянию на 1 января 1995 г. площадь, покрытая лесами в Российской Федерации, занимала 771109 тыс. га, что составляло 45,2% от всей территории России. Общий запас древесины на то же время был равен 81644 млн. куб. м., что соответствовало примерно одной пятой части всех мировых запасов древесины. В таблице 3 приведены данные о площадях лесных массивов и запасах древесины по отдельным экономическим районам западной и восточной зон Российской Федерации на 1 января 1995 года [7]. Таблица 3 Площадь лесного пространства и общий запас древесины

Западная зона

тыс. га

Общий запас древесины, млн. куб. м.

Северный

76048

7599

Северо-Западный

10388

1625

Центральный

20328

3042

Волго-Вятский

13309

1787

Центрально-Черноземный

1469

183

Поволжский

4773

573

Северо-Кавказский

3664

579

35753

4850

266

39

90095

10974

Восточно-Сибирский

234464

29135

Дальневосточный

280552

21258

Уральский Калининградская область Восточная зона

Россия

Экономический район

Площадь,

Западно-Сибирский

27

Необходимо создать электронную таблицу, которую сначала следует заполнить данными из таблицы 3, а затем на основе этих данных определить: – какой процент к территории экономического района, зоны и России в целом составляет площадь, покрытая лесом (удельный вес лесного пространства); – запас древесины на душу населения в каждом экономическом районе, зоне и в целом по России; Вид и значения результатов должны соответствовать таблице 4. Таблица 4 Площадь лесного пространства и общий запас древесины Экономический район

%к Общий запас Запас древесины Площадь, террито- древесины, на душу населетыс. га рии млн. куб. м. ния, куб. м./чел.

Россия

771109

45,2

81644

550,5

Западная зона

165998

38,5

20 277

174,2

Северный

76048

51,9

7599

1277,8

Северо-Западный

10388

52,9

1625

200,8

Центральный

20328

41,9

3042

101,4

Волго-Вятский

13309

50,5

1787

210,9

Центрально-Черноземный

1469

8,8

183

23,2

Поволжский

4773

8,9

573

33,9

Северо-Кавказский

3664

10,3

579

32,8

35753

43,4

4850

236,7

266

17,6

39

42,1

605111

47,4

61367

1921,9

Западно-Сибирский

90095

37,1

10974

724,9

Восточно-Сибирский

234464

56,9

29135

3178,6

Дальневосточный

280552

45,1

21258

2787,9

Уральский Калининградская область Восточная зона

В данной задаче в качестве единицы измерения площади используется

гектар (га) – метрическая мера земельной площади, равная 10000 кв. м.

28

Справедливы следующие соотношения: 1 га = 10000 кв. м. 1 кв. км = 1000000 кв. м. = 100 га 1 тыс. кв. км = 100 тыс. га Этап 1. В в о д и с х о д н ы х д а н н ы х Электронную таблицу для решения задачи 2 будем создавать на новом рабочем листе книги в файле region.xls, созданном в задаче 1. 1. Откройте файл region.xls, содержащий рабочий лист Регионы России с решением задачи 1. 2. Добавьте новый рабочий лист и присвойте ему имя Леса России или

Лесные ресурсы России. Для этого: • Установите курсор мыши на ярлычок листа Регионы России, нажмите правую кнопку мыши для вызова контекстного меню; • Выберите пункт Добавить…, в открывшемся диалоговом окне Вставка выберите тип добавляемого объекта – Лист и нажмите кнопку ОК; • Добавится новый рабочий лист со стандартным названием Лист2; • Переименуйте его (см. стр. 14 п.3.); • Новый лист будет вставлен перед имеющимся, его можно переместить с помощью мыши и сделать вторым листом в книге. 3. Введите в ячейку A1 заголовок таблицы Площадь лесного пространства и общий запас древесины 4. Введите в ячейки A2:Е2 заголовки столбцов таблицы (см. таблицу 4), не забудьте установить режим переноса по словам (см. стр. 17 п.2.). 5. Измените ширину столбца A. 6. В диапазоне ячеек A3:A17 введите названия строк таблицы 4. 7. Заполните диапазон ячеек B5:B13 и B15:B17 числами из таблицы 3, соответствующими значениям площади лесов в экономических районах.

29

8. Заполните диапазон ячеек D5:D13 и D15:D17 числами из таблицы 3, соответствующими запасам древесины в экономических районах. В н и м а н и е! Не вводите значения в ячейки B3, B4, B14 и в ячейки D3, D4, D14, так как их содержимое является результатом вычислений, которые предстоит выполнить на следующем этапе. 9. Сохраните текущее состояние таблицы. Этап 2. В ы ч и с л е н и я в т а б л и ц е Сначала необходимо вычислить суммарные значения площади лесов и общего запаса древесины отдельно для западной (ячейки B4 и D4 соответственно) и восточной (ячейки B14 и D14) зон России, а затем, взяв их суммы, получить соответствующие значения в целом по России (ячейки B3 и D3). Выполните вычисления такие же, как в задаче 1 (стр. 19-21 п.п. 1-4). Удельный вес лесного пространства Так как площадь лесов дана в тыс. га, а территория – в тыс. кв. км, то при вычислении процента эти величины необходимо привести к одной и той же единице измерения, используя соотношение 1 тыс. кв. км = 100 тыс. га Формула для вычисления удельного веса лесного пространства (в про-

центах к территории) имеет вид: Удельный вес =

=

Площадь леса (тыс. га) Площадь территории (тыс.кв.км) ⋅100

⋅ 100 =

Площадь леса (тыс. га) Площадь территории (тыс.кв.км)

Числитель в этой формуле содержит значения с текущего листа Леса

России, а знаменатель содержит значения территорий с листа Регионы России этой же рабочей книги (см. задачу 1). В Excel cсылка на ячейку другого листа должна уточняться именем листа, который записывается перед адресом ячейки, и отделяется от нее восклицательным знаком. Если имя листа содержит пробелы, то оно записывается в

30

апострофах. Например, ссылка на ячейку другого листа может иметь вид Лист1!B3 или ‘Регионы России’!B3. Но ссылки на ячейки другого листа также

как и ссылки на ячейки текущего листа, как правило, не набираются на клавиатуре, а получаются щелчком мыши на ячейке. Для получения удельного веса лесного пространства (в процентах к территории) выполните следующие действия: 1. Введите в ячейку C3 расчетную формулу. Для этого: • Выделите ячейку C3. • Наберите на клавиатуре символ « = ». • Щелкните мышью на ячейке B3 – ссылка на неё попадет в формулу. • Наберите на клавиатуре символ « / ». • Щелкните мышью на ярлычке листа Регионы России. • Щелкните мышью на ячейке B3 листа Регионы России – ссылка на неё попадёт в формулу; нажмите клавишу Enter. • Активным снова станет лист Леса России. Выделите ячейку C3. • В строке формул появится формула, получившаяся в ячейке C3 =B3/’Регионы России’!B3

2. Скопируйте ячейку C3 на диапазон C4:C17, используя маркер заполнения. Диапазон C3:C17 заполнится числовыми значениями в общем формате. 3. Измените формат для диапазона C3:C17, оставив одну цифру дробной части (см. стр. 22 п.3.) Сравните полученные результаты с таблицей 4. Вычисление запаса древесины на душу населения Так как общий запас древесины задается в млн.куб.м, население – в тыс.чел., а запас древесины на душу населения необходимо вычислять в куб.м/чел., то формула будет иметь вид Запас на душу населения =

=

Общий запас древесины (куб.м) Население (чел.)

=

Общий запас древесины (млн.куб.м) ⋅ 1000 Население (тыс.чел.)

31

В этой формуле также как и при вычислении удельного веса лесного пространства (в процентах к территории), числитель содержит значения с текущего листа Леса России, а знаменатель содержит значения численности населения с листа Регионы России этой же рабочей книги (см. задачу 1). Для вычисления запаса древесины на душу населения выполните следующие действия: 1. Введите в ячейку E3 расчетную формулу =D3*1000/’Регионы России’!C3

(набирать так же, как формулу для вычисление процента к территории). 2. Скопируйте ячейку E3 на диапазон E4:E17. 3. Измените формат для диапазона E4:E17, оставив одну цифру дробной части (см. задача 1, стр. 22). Сравните полученные результаты с таблицей 4. 4. Выполните форматирование таблицы. 5. Сохраните текущее состояние таблицы. Упражнение 2.1. Добавьте в таблицу 4 еще один столбец для вычисления запаса древесины на единицу площади леса в каждом экономическом районе, зоне и в целом по России. Выполните вычисления. У к а з а н и е. Запас древесины на единицу площади леса определяется как отношение общего запаса древесины к площади леса. Используются данные с листа Леса России. Упражнение 2.2. Добавьте в таблицу 4 еще один столбец для вычисления запаса древесины на единицу территории в каждом экономическом районе, зоне и в целом по России. Выполните вычисления. У к а з а н и е. Запас древесины на единицу территории определяется как отношение общего запаса древесины (данные с текущего листа Леса Рос-

сии) к территории (данные с листа Регионы России).

32

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

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

#ДЕЛ/0!

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

#ЗНАЧ!

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

#ИМЯ?

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

#Н/Д

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

#ПУСТО!

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

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

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