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

УДК 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