Электронные таблицы, функции базы данных и взаимосвязи : создание и форматирование таблиц. Взаимосвязи. Расчеты. Функции. Регрессии

УДК 004.42 
М91 

Р е ц е н з е н т 
ст. преподаватель М.О. Пышняк 

Муратова СЮ. 

М91 
Электронные таблицы, функции, базы данных и взаимосвязи: 

Создание и форматирование таблиц. Взаимосвязи. Расчеты. Функции. Регрессии: Лаб. практикум. - М.: МИСиС, 2003. - 107 с. 

Настоящий лабораторный практикум используется в курсе «Электроппые таблицы, функции, базы данных и взаимосвязи» (раздел «Создание, 
форматирование таблиц. Взаимосвязи. Расчеты. Функции. Регрессии»). 
Практикум рассчитан на 34 часа аудиторных и самостоятельных занятий, что 
составляет половину курса. В практикум включено пять работ, выполнение 
которых позволит приобрести навыки создания и оформления таблиц, проведения расчетов и их отладки. Особое внимание уделяется составлению прогнозов с использованием линейной и экспоненциальной регрессий. 

© Московский государственный институт 
стали и сплавов (Технологический 
университет) (МИСиС), 2003 

ОГЛАВЛЕНИЕ 

Введение 
6 

Лабораторная работа № 1. Окно Excel и его элементы. Ввод 
и редактирование содержимого ячейки. Операции над листами. 
Операции над столбцами и строками. Форматирование таблицы 
7 

1.1. Основные элементы окна Excel 
7 

1.2. Ввод и редактирование данных 
9 

1.2.1. Ввод данных 
9 

1.2.2. Отображаемые и хранимые значения 
9 

1.2.3. Редактирование данных 
10 

1.2.4. Изменение стандартного шрифта 
11 

1.3. Операции над листами 
14 

1.3.1. Вставка листов 
14 

1.3.2. Выделение листов 
14 

1.3.3. Удаление листов 
15 

1.3.4. Переименование листов 
16 

1.3.5. Перемещение/копирование листов 
16 

1.4. Выделение фрагмента таблицы 
17 

1.4.1. Выделение диапазона ячеек 
17 

1.4.2. Выделение строк и столбцов 
18 

1.5. Операции над столбцами и строками 
18 

1.5.1. Настройка ширины столбца 
18 

1.5.2. Настройка высоты строки 
20 

1.5.3. Вставка пустых строк и столбцов 
20 

1.5.4. Удаление строк и столбцов 
21 

1.5.5. Скрытие строк и столбцов 
21 

1.6. Форматирование таблицы 
22 

1.6.1. Основные приемы форматирования таблиц 
23 

1.6.2. Форматирование чисел 
27 

1.6.3. Создание пользовательских форматов 
28 

1.6.4. Форматирование положительных, отрицательных, 
нулевых и текстовых значений 
31 

1.6.5. Включение цвета в форматы 
31 

1.6.6. Условное форматирование 
32 

1.6.7. Копирование форматов 
33 

1.6.8. Удаление форматов 
34 

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

3 

Лабораторная работа № 2. Копирование и перенос фрагмента 
таблицы разными способами. Подготовка таблицы к печати 
38 

2.1. Копирование и перенос фрагмента таблицы 
38 

2.1.1. Копирование и перенос фрагмента таблицы с помощью 
мыши 
38 

2.1.2. Копирование и перенос фрагмента таблицы обычным 
способом 
39 

2.2. Подготовка таблицы к печати 
41 

2.2.1. Параметры страницы. Вкладка Страница 
41 

2.2.2. Параметры страницы. Вкладка Поля 
42 

2.2.3. Параметры страницы. Вкладка Колонтитулы 
43 

2.2.4. Параметры страницы. Вкладка Лист 
48 

2.2.5 Предварительный просмотр таблицы 
51 

2.2.6. Принудительный разрыв страницы 
55 

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

Лабораторная работа №3. Списки 
58 

3.1. Создание и заполнение рядов 
58 

3.2. Перетаскивание маркера заполнения с использованием 
правой кнопки мыши 
60 

3.3. Создание пользовательских списков 
61 

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

Лабораторная работа № 4. Формулы. Абсолютные, относительные 
и смешанные ссылки. Копирование формул. Автосуммирование. 
Автоматические вычисления. Отображение формул на листе. 
Отображение зависимостей 
64 

4.1. Создание формул 
64 

4.2. Адресация ячеек 
64 

4.3. Ввод адреса с помощью мышки 
65 

4.4. Копирование формул с помощью маркера заполнения 
66 

4.5. Относительная, абсолютная и смешанная адресация ячеек ....66 
4.6. Копирование формул обычным способом 
68 

4.7. Автосуммирование 
68 

4.8. Ссылка на другие листы и другие книги 
70 

4.8.1. Ссылка на другие листы 
70 

4.8.2. Ссылка на другие книги 
71 

4.8.3. Открытие зависимых книг 
72 

4.8.4. Переадресация связей 
74 

4.9. Присваивание имен ячейкам и диапазонам 
75 

4.9.1. Определение имен в поле Имя 
75 

4.9.2. Замена ссылок в формулах на имена 
76 

4 

4.9.3. Создание имен из текстовых значений 
76 

4.9.4. Удаление и переопределение имени 
77 

4.10. Автоматические вычисления и вычисления вручную 
78 

4.11. Отладочные средства Excel 
79 

4.11.1. Отображение зависимостей 
80 

4.11.2. Добавление примечаний 
82 

4.11.3. Отображение формул 
83 

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

Лабораторная работа № 5. Функции. Мастер функций. Линейная 
и экспоненциальная регрессия. Использование формул 
при форматировании 
85 

5.1. Синтаксис функций 
85 

5.2. Вставка функций 
87 

5.3. Примеры некоторых функций 
92 

5.4. Перехват ошибок: функции ЕОШ, ЕОШИБКА и ЕПД 
94 

5.5. Использование функций в объемных формулах 
95 

5.6. Линейная и экспоненциальная регрессии 
97 

5.7. Условное форматирование с использованием формул 
100 

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

Библиографический список 
106 

5 

ВВЕДЕНИЕ 

Во многих сферах человеческой деятельности существует 
множество задач, в которых данные представлены в табличной форме. Для автоматизации расчетов в подобных задачах имеется класс 
программных продуктов, которые называются электронными таблицами. Наиболее известной из них является Microsoft Excel, позволяющий создавать, оформлять и редактировать таблицы, осуществлять простые и прогнозные расчеты на основе хранящихся в них 
данных, графически представлять данные с помощью графиков и 
диаграмм, осуществлять структурирование данных, их многомерный 
анализ, автоматизировать часто выполняющиеся операции, обмениваться информацией с другими программами Microsoft Office и многое другое. Целью данного курса и является овладение навыками работы с этой полезной и эффективной программой. 

6 

Лабораторная работа № 1 

ОКНО EXCEL И ЕГО ЭЛЕМЕНТЫ. ВВОД 

И РЕДАКТИРОВАНИЕ СОДЕРЖИМОГО ЯЧЕЙКИ. 

ОПЕРАЦИИ НАД ЛИСТАМИ. ОПЕРАЦИИ 

НАД СТОЛБЦАМИ И СТРОКАМИ. 
ФОРМАТИРОВАНИЕ ТАБЛИЦЫ 

Цель работы: приобретение навыков работы в Microsoft 
Excel по созданию и форматированию текста, а также созданию 
пользовательских форматов. 

1.1. Основные элементы окна Excel 

При запуске Excel на экране компьютера появляется рабочее 
окно подобное показанному на рис. 1.1. 

11Щ Файл 
ПрзЕзка Дид 
ВстзЕзка 
Форм. 
^iaJ2<l| 

-^Линейка прокрутки 
ярлычков листов - выводит 
на экран невидимые ярлычки 

Ярлычки листов - с их 
помощью происходит переход 
от одной таблицы к другой 

Рис. 1.1. Отличительные элементы окна Excel 

7 

Документы, созданные в среде Excel, называются рабочими 
книгами. До тех пор, пока документ не сохранен, он называется Книга № (в начале работы в Excel - Книга 1) и содержит только рабочие 
листы. Рабочую книгу можно рассматривать как файл-папку с листами (подшивку листов), на которых хранятся электронные таблицы. 
При сохранении рабочей книги ей присваивается расширение «xls». 

Имена листов отображаются на ярлычках в нижней части 
окна книги. Для перехода с одного листа на другой следует щелкнуть 
мышкой по соответствующему ярлычку или нажать клавиши 
[Ctrl] + [Page Up] или [Ctrl] + [Page Down]. У активного листа - белый ярлычок, его название выделено жирным шрифтом. 

Электронная таблица состоит из столбцов и строк. Это и есть 
ее рабочее поле. Слева от рабочего поля находится координатный 
столбец (столбец заголовков) с номерами строк, сверху- координатная строка (строка заголовков) с названиями столбцов. Размер 
таблицы фиксированный: она состоит из 65 536 строк и 255 столбцов. 

Пересечение столбца и строки образует ячейку, в которую 
вводятся числа, текст, даты или формулы. Местоположение ячейки 
задается адресом (который еще называется ссылкой), образованным 
из имени столбца и номера строки, на пересечении которых эта 
ячейка находится. Например, адрес самой первой ячейки - А1 (пересечение столбца А с 1-й строкой). 

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

ВНИМАНИЕ! Зачастую то, что вы видите, глядя на таблицу, не совсем 
точно отражает реальное положение вещей. Точную информацию о содержимом ячейки дает сочетание адрес - содержимое ячейки в строке 
формул. Дело в том, что если в какой-то ячейке содержится, например, 
слишком длинный текст, а правая ячейка пуста, то этот текст «залезает» 
на ее территорию', хотя на самом деле он находится в одной ячейке. 
Кроме того, некоторые операции форматирования размещают содержимое ячейки совсем в другом месте. Вышеназванные поля строки формул 
однозначно отвечают на вопрос о содержимом той или иной ячейки. 

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

• Если текст очень длинный, то он может «покрывать» несколько ячеек подряд. 

8 

1.2. Ввод и редактирование данных 

1.2.1. Ввод данных 

Укажите курсором ячейку, в которую следует ввести данные, 
и напечатайте их. Обратите внимание: при вводе или редактировании 
данных появляются кнопки строки формул 1^^ =1, где А- 
кнопка 
отмены, А - кнопка ввода и ^ - кнопка мастера функций. 

Завершение 
ввода осуществляется одним из следующих 
способов: 

1-й-щелчком по кнопке ввода (зеленой галочке). При этом 
курсор ввода остается в той же ячейке; 

2-й-щелчком мышкой по любой другой ячейке (убиваем 
двух зайцев: заканчиваем ввод данных и переходим в нужное место 
таблицы); 

3-й - клавишами передвижения курсора (если вы, например, 
заполняете строку, то ввод данных в ячейку удобно заканчивать нажатием клавиши Ы ) . 

4-й-клавишей 
[Enter]. Обычно при нажатии клавиши 
[Enter] курсор передвигается на одну ячейку вниз. Если вы хотите, 
чтобы этого не происходило, т.е. текущей оставалась та же самая 
ячейка, выполните команду Сервис/Параметры/Правка, а затем 
уберите флажок в поле Переход к другой ячейке после ввода. Обратите внимание, что здесь же можно задать другое направление передвижению курсора. Для этого в поле В направлении выберите 
нужный вам вариант. 

ВНИМАНИЕ! Если вы ввели неверные данные, следует щелкнуть по 
кнопке отмены (красному крестику) или нажать клавишу [Esc]. 

1.2.2. Отображаемые и хранимые значения 

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

При вводе длинного числового значения, если оно не помещается в ячейке. Excel использует либо экспоненциальную форму 
представления числа, либо «забор» из символов #. В первом случае 
точность значения выбирается такой, чтобы число можно было отобразить в ячейке (рис. 1.2). 

9 

Рис. 1.2. Во всех четырех ячейках содержится число 123456789012345 

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

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

Редактировать содержимое ячейки можно несколькими 
способами: 

1-й - дважды щелкните по нужной ячейке и внесите изменения*; 
2-й-укажите курсором ячейку, в которую следует внести 
изменения, и нажмите клавишу [F2] (этот способ удобен, если вам 
необходимо что-нибудь дописать в конце); 

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

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

Для того чтобы удалить данные из ячейки, укажите ячейку и 
нажмите клавишу [Delete]. Эту же операцию можно выполнить с помощью контекстного меню командой Очистить содержимое. 

• Если данный режим недоступен, выполните команду Сервис/Параметры/Правка 
и установите флажок в поле Правка прямо в ячейке. 

10 

1.2.4. Изменение стандартного шрифта 

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

1) выполнить команду Сервис/Параметры/Общие (рис. 1.3); 
2) в поле Стандартный шрифт: выбрать новый шрифт; 
3) в поле Размер: установить необходимый размер шрифта. 

Рис. 1.3. На этой вкладке можно установить новый стандартный шрифт 

Задание: на листе Лист1 напечатайте исходную таблицу Население Земли (рис. 1.4). Сохраните ее в файле с именем Мои табЛИЦЫ.Х18. 

11 

Население Земли (в тысячах) 
Континент или страна 
Европа 
Германия 
Франция 
Югославия 
Африка 
Египет 
Эфиопия 
Сомали 
Азия 
Китай 
Индия 
Пакистан 
Америка 
Бразилия 
США 
Венесуэла 
Австралия/Океания 
Австралия 
Новая Зеландия 
Папуа-Новая Гвинея 

1950 

593407 

68376 
41829 
16346 

224075 

20330 
19573 
2423 

1353743 

554760 
357561 

39513 

331440 

53444 

152271 

5009 
12647 
8219 
1908 
1613 

1960 

666914 

72673 
45684 
18402 
281076 
25922 
24191 

2935 

1639292 
657492 
442346 

49955 

416312 

72595 

180671 

7502 
15842 
10315 
2372 
1920 

1970 

738412 

77717 
50772 
20371 

362788 

33053 
30623 

3668 

2065781 

830675 
554911 

65706 

511607 

95847 

205051 

10604 
19329 
12552 
2820 
2422 

1980 

794420 

78303 
53880 
22299 

481034 

41520 
38750 

5352 

2538398 

996134 
688856 

85299 

613564 
121757 
227757 

15024 
22794 
14695 

3113 
3086 

1990 

841348 

77188 
56173 
23849 

647518 

54059 
46743 

7555 

3052860 
1135496 

853373 
122666 
723976 
162341 
249235 

19736 
26476 
16746 

3379 
4011 

Рис. 1.4. Исходная таблица «Население Земли» 

Методика выполнения задания 

1. На листе Лист1 щелкните мышкой по ячейке А1 и напечатайте текст: Население Земли в тысячах. 

2. Нажмите клавишу [Enter]. 
3. В ячейке А2 напечатайте текст Континент или страна. 
4. Нажмите клавишу передвижения курсора вправо [^]. Текущей стала ячейка В2. 

5. В ячейке В2 напечатайте 195О и нажмите клавишу передвижения курсора вправо [^]. 

6. В ячейке С2 напечатайте 1960 и т.д. до тех пор, пока не 
введете последний год - 19 9 0. 

7. Нажмите клавишу [Enter], а затем клавишу [Ноте], курсор 
переместится в начало 3-й строки (вспомнили? - так же, как и в редакторе Word, клавиша [Ноте] перемещает курсор в начало строки). 

8. Таким же образом напечатайте остальные строки. 

12 

Обратите внимание на реакцию Excel, когда будете набирать 
слово Азия, - после того как вы напечатаете букву А, в ячейке появится слово Африка (рис. 1.5). 

АН 

1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
Л. 

А 

Население 

и X N/ =1 Африка 

1 

Земли 

Континент или стра! 
Европа 
Германия 
Франция 
Югославия 
Африка 
Египет 
Эфиопия 
Сомали 
А|]ИШМШННН1 

в 
С 

^в тысячах) 

1950 

593407 

68376 
41829 
16346 

224075 

20330 
19573 
2423 

1960 

666914 

72673 
45684 
18402 

261076 

25922 
24191 

2935 

Е 

1980 

794420 

78303 
53880 
22299 

481034 

41520 
38750 
5352 

F - п 

1990! 

841348: 

771881 
56173! 
23849! 

647518! 

54059! 
46743! 

7555 

Рис. 1.5. Автозавершение ввода 

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

Режим автозавершения ввода установлен по умолчанию. 
Включить/отключить этот режим можно с помощью команды Сервис/Параметры/Правка/Автозаполнение значений ячеек. 

9. Сохраните рабочую книгу в папке C:\ST. 

13 

1.3. Операции над листами 

Как уже было сказано, книга- это подшивка листов. Помимо 
рабочих листов в книге могут храниться листы диаграмм, модули VBA*, 
листы диалога. Листы можно перемещать, копировать, присваивать им 
имена и переименовывать их. 

По умолчанию новая книга содержит 16 рабочих листов. Для 
того чтобы изменить их количество в новых книгах, необходимо: 

1) вьшолнитъ команду Сервис/Параметры/Общие (см. рис. 1.3); 
2) в поле Листов в новой книге установить требуемое количество листов. 

ЗАМЕЧАНИЕ. Пустые листы занимают дополнительное место на 
диске. Поэтому для экономии места и лучшего обзора содержимого 
файла следует создавать книги с небольшим количеством листов и 
добавлять новые по мере надобности. По этой же причине неиспользуемые листы из книги удаляются. 

Задание для самостоятельной работы: установите в новых 
книгах 3 листа. 

1.3.1. Вставка листов 

Добавить в книгу лист можно двумя способами. 
1-й способ 
1) щелкните правой кнопкой мышки по ярлычку листа, перед которым надо вставить новый лист; 

2) в появившемся контекстном меню выберите команду 
Добавить; 

3) в появившемся диалоговом окне (ДО) Вставка укажите 
тип листа и нажмите кнопку ОК. 

Во 2-м способе вставка осуществляется с помощью команды 
Вставка/Лист. 

1.3.2. Выделение листов 

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

Выделение листов осуществляется щелчками мышки: 

• VBA (Visual Basic for Application) - встроенный в MS Office язык программирования. 

14