Информатика : электронные таблицы Excel

№ 15

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

Кафедра инженерной кибернетики

Е.В. Сигитов

Информатика

Электронные таблицы Excel

Практикум

Рекомендовано редакционноиздательским
советом университета

Москва   Издательский Дом МИСиС
2008

УДК 004.9 
 
С34 

Р е ц е н з е н т  
канд. техн. наук, доц. С.Ю. Муратова 

Сигитов Е.В. 
С34  
Информатика: Электронные таблицы Excel: Практикум. – 
М.: Изд. Дом МИСиС, 2008. – 91 с. 

Практикум посвящен практическому использованию одного из мощных 
современных программных средств – электронной таблице Excel. Он содержит теоретическую часть, включающую основные средства для работы с 
электронной таблицей Excel (общие для различных модификаций), приемы и 
подходы, используемые при решении практических задач, примеры решенных задач, иллюстрирующие их практическое применение в инженерной 
практике. Практикум содержит 7 работ, основу которых составляют содержательные задачи, для решения многих из них применяются численные методы. 
Содержание практикума соответствует программе курса «Информатика». 
Предназначен для студентов всех специальностей, а также для тех, кто 
хочет научиться работать с электронной таблицей Excel. 

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

СОДЕРЖАНИЕ 

Теоретическое введение...........................................................................5 
1. Принципы работы с электронными таблицами. Электронные 
таблицы Excel............................................................................................5 
1.1. Загрузка Excel ................................................................................5 
1.2. Структура окна рабочего листа Excel..........................................5 
1.3. Сохранение рабочей книги ...........................................................8 
1.4. Средства управления Excel...........................................................8 
1.5. Организация данных в Excel ........................................................9 
1.6. Элементарные операции с данными............................................9 
1.7. Операции с рабочими листами...................................................12 
1.8. Виды адресации ...........................................................................13 
1.9. Типы данных................................................................................14 
1.10. Оформление данных..................................................................15 
2. Расчеты в Excel ...................................................................................16 
3. Диаграммы ..........................................................................................19 
3.1. Построение диаграмм..................................................................19 
3.2. Редактирование диаграмм ..........................................................20 
4. База данных.........................................................................................21 
4.1. Работа с базой данных в Excel....................................................21 
4.2. Создание списка...........................................................................21 
4.3. Сортировка списка.......................................................................21 
4.4. Выбор из базы данных элементов списка .................................22 
5. Макросы ..............................................................................................23 
5.1. Основы языка Visual Basic..........................................................23 
5.1.1. Переменные и константы.....................................................23 
5.1.2. Массивы.................................................................................24 
5.1.3. Тип, задаваемый пользователем..........................................24 
5.1.4. Выражение.............................................................................24 
5.1.5. Операторы .............................................................................25 
5.1.6. Функции для работы с диалоговыми окнами.....................26 
5.1.7. Процедуры и функции..........................................................26 
5.1.8. Методы ..................................................................................26 
5.2. Создание макросов (модулей) ....................................................27 
Контрольные вопросы........................................................................28 
Практические занятия ............................................................................30 
Работа 1. Построение таблиц и наглядное представление 
результатов..........................................................................................30 

Работа 2. Решение задач, зависящих от параметра .........................40 
Работа 3. Решение типовых задач.....................................................47 
Работа 4. Финансовые функции в Excel ...........................................58 
Работа 5. База данных в Excel ...........................................................67 
Работа 6. Матричные операции в Excel............................................74 
Работа 7. Создание макросов.............................................................80 
Библиографический список...................................................................90 
 

ТЕОРЕТИЧЕСКОЕ ВВЕДЕНИЕ 

1. ПРИНЦИПЫ РАБОТЫ С ЭЛЕКТРОННЫМИ 
ТАБЛИЦАМИ. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL 

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

1.1. Загрузка Excel 

Для загрузки Microsoft Excel необходимо выполнить следующие 
действия: 
1) запустить Windows; 
2) загрузить Excel, для чего щелкнуть на кнопке «ПУСК» на панели задач Windows и выбрать команду Microsoft Excel либо 
подвести указатель к пиктограмме программы Microsoft Excel и 
дважды нажать левую кнопку мыши. 

1.2. Структура окна рабочего листа Excel 

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

• первая строка (главное меню), в которой приведены названия девяти командных меню. Все функции Excel доступны через это меню. 

 

Рис. 1 

Меню Файл позволяет выполнять команды сохранения файла под 
тем же именем, под другим именем и на выбранное устройство, считывания файла в область рабочего пространства, просмотр и печать 
файла (рабочей книги). 
Меню Правка позволяет выполнять команды, связанные с исправлением текста таблицы, его полным или частичным устранением, переносом в другое место и копированием. 
Меню Вид позволяет придать определенный вид создаваемой таблице, произвести ее разметку, вызвать любые кнопки-пиктограммы из 
панели инструментов, выбрать определенный масштаб.  
Меню Вставка позволяет добавить в таблицу новые строки, 
столбцы и лист, вставить в набранную таблицу графики, рисунок, 
функцию и имя, вызвать любую программу, доступную в операционной системе Windows. 
Меню Формат позволяет выбрать тип и размер шрифта текста в 
ячейках таблицы, установить размер строк и столбцов. 

Меню Сервис позволяет обеспечить автоматическую проверку 
орфографии создаваемого текста таблицы и его соответствие правилам грамматики. 
Меню Данные позволяет создать базу данных редактором Excel, 
их ввод с помощью формы, их сортировку, выбор записей из базы 
данных, удовлетворяющих определенным условиям с помощью таблицы критериев. 
Меню Окно позволяет создавать дополнительные таблицы на рабочем листе и осуществлять их взаимное расположение. 
Меню Справка дает возможность получить разнообразную информацию справочного характера при работе с программами;  
• вторая и третья строки (стандартная панель инструментов и 
линейка форматирования), которых продублированы многие средства управления в форме кнопок-пиктограмм, поскольку выполнять 
функции с помощью меню не всегда удобно и не так быстро. Кнопки-пиктограммы дублируют важнейшие функции Главного меню. 
При настройке Excel могут быть выведены необходимые кнопкипиктограммы из панели инструментов; 
• четвертая строка (строка формул), в которой отражается информация, связанная с набором формулы:  
– поле имени отображает адрес текущей ячейки, в которой набираем формулу и при желании задаем собственное имя этой ячейки. В 
режиме ввода/редактирования данных здесь появляется меню функций (Мастер функций) Excel, из которого можно выбрать нужную 
функцию; 
– кнопка просмотра списка имен служит для быстрого перемещения в любую поименованную область книги, открывая меню, содержащее перечень (если есть) всех таких блоков; 
• поле ввода (собственно строка формул), которое используется 
для отображения ввода самой формулы. Для управления вводом 
здесь доступны три кнопки: X – отказ от ввода (действует аналогично клавише [Esc]), ٧ – кнопка подтверждения ввода (аналогична 
клавише [Enter]), = – редактирование формулы. Кнопка вызывает 
Мастер функций, облегчающий ввод функций; 
• рабочее пространство Excel, которое находится ниже строки 
формул и называется рабочим листом. Листов может быть до 255 (по 
умолчанию 3). Лист обрамлен бордюром, в котором указаны номера 
строк (1, 2, 3, …) и буквенные обозначения столбцов (A, B, C, ...); 

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

1.3. Сохранение рабочей книги 

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

1.4. Средства управления Excel 

В Excel управление данными может выполняться одним из четырех способов: через главное меню, с помощью пиктограмм, с помощью закрепленных клавиш и мышью. 
Главное меню позволяет реализовать все функции Excel. Доступ к 
нему осуществляются клавишами [Alt], или [F10], или мышью. 
Пиктограммы (экранные кнопки) в графической форме отображают важнейшие функции, необходимые пользователю. 
Клавишные комбинации открывают еще более быстрый доступ к 
некоторым из средств обработки данных. Перечислим важнейшие 
клавиши: 
[F1] – вызов справочной системы по работе в Excel (помощника), 
[Esc] – отмена текущего ввода, 
[Home] – переход в начало строки,  
[End] – переход в конец строки, 
[F2] – переход в режим редактирования непосредственно в ячейке, 
[F4] – в режиме ввода/редактирования формулы преобразует адрес из относительного в абсолютный и наоборот, 
[F9] – проводит вычисления на всех рабочих листах, 
[Shift] + [F4] – проводит вычисления только на активном листе, 
[Ctrl] + [F4] – закрывает активное окно, 
[Alt] + [F4] – закрывает Excel. 

Мышь является самым оперативным и самым удобным средством 
перемещения и управления данными. При перемещении мыши экранный курсор (указатель мыши) меняет свою форму в зависимости 
от функций, которые ему доступны в данном месте. Курсор показывает местоположение текущих действий, а вид курсора указывает на 
возможные действия: 
| – курсор ввода данных, 
╬ – курсор выделения блоков таблицы, 
– курсор копирования/перемещения блоков, 
+ – курсор копирования в соседние ячейки, 
◄|► – курсор изменения ширины столбца, 
▲ – курсор изменения высоты строки.  
▼  

1.5. Организация данных в Excel 

Рабочий лист образует рабочее пространство пользователя. Лист 
имеет 256 столбцов и 65 536 строк. Данные, находящиеся в разных 
листах, могут взаимодействовать друг с другом. Для этого необходимо указать перед адресом ячейки адрес листа (разделив их знаком 
!), например Лист4!В5. Листам можно давать произвольные имена. 
Ячейка является элементарной единицей данных в таблице. К 
клетке можно обратиться по ее адресу, который формируется из буквы столбца и номера строки, например D8.  
Блок – прямоугольная область ячеек. Блок в таблице задается адресами левого верхнего и правого нижнего его углов, разделенными 
двоеточием, например B4:F10. Вместо двоеточия можно набирать 
точку (B4.F10) и после нажатия клавиши [Enter] точка автоматически 
превращается в двоеточие. Любой блок или ячейка могут быть поименованы. Одним из способов присвоения имени блоку (с предварительным выделением его) является ввод его имени в поле имени в 
строке формул. Дальше уже можно обращаться к блоку как единому 
целому по имени. 

1.6. Элементарные операции с данными 

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

Выделение ячейки осуществляется с помощью стрелок клавиатуры (, , , ) или щелчком мыши. Адрес и содержимое этой ячейки будет отображаться в строке формул. 
Выделение строки/столбца выполняется, если щелкнуть мышью 
на соответствующем элементе бордюра – номере строки или букве 
столбца. Вся строка/столбец закрашивается в темный цвет, кроме 
самой первой ячейки. 
Выделение всего листа целиком осуществляется щелчком мыши 
на кнопке «выделить все», находящейся на пересечении бордюра 
строк и столбцов. 
Выделение блока (окрашивается темным цветом) можно выполнить различными способами: 
а) установить курсор в ячейке любого из четырех углов блока. Далее, удерживая нажатой клавишу [Shift], с помощью стрелок клавиатуры переместить курсор в ячейку противоположного угла блока; 
б) щелкнуть левой кнопкой мыши ячейку в одном углу блока и, 
удерживая ее нажатой, переместить в ячейку противоположного угла 
блока; 
в) щелкнуть левой кнопкой мыши в одном углу блока, удерживая 
нажатой клавишу [Shift], щелкнуть ячейку противоположного угла 
блока.  
Выделение нескольких блоков, расположенных в несмежных областях листа, может осуществляться теми же способами, что и выделение одного блока, но для выделения следующего блока необходимо нажать клавишу [Ctrl]. 
Перемещение ячейки/блока также можно выполнить многими 
способами: 
а) курсор приблизить к любой границе выделенного блока (при 
этом курсор превратится в стрелку ) и, нажимая и удерживая кнопку мыши, переместить блок на новое место; 
б) выделить блок, перейти в меню Правка, выполнить подпункт 
Вырезать, курсор переместить в ячейку, с который начинается перемещаемый блок, и выполнить подпункт меню Вставить. Вместо 
меню Правка можно использовать нажатие правой кнопки мыши и 
при открытии контекстного меню выбрать соответственно Вырезать 
и Вставить; 
в) выделить блок, курсор приблизить к любой границе выделенного блока (при этом курсор превратится в стрелку ), нажать правую кнопку мыши и начать перемещать блок, при этом откроется 

контекстное меню, затем выполнить пункт Переместить, предварительно переместив курсор в первую ячейку выделенного блока. 
Копирование выполняется теми же способами. При способе «а» 
необходимо держать клавишу [Ctrl], а при способах «б» и «в» вместо 
подпункта Вырезать использовать подпункт Копировать. 
Копирование/перемещение данных на другие листы выполняется 
аналогичным образом, но при нажатой клавише [Alt]. Так, для перемещения выделенного блока нужно отбуксировать его на вкладку 
нужного листа (что повлечет открытие этого листа) и установить на 
выбранное место. Для копирования следует сделать то же самое, но 
еще и при нажатой клавише [Ctrl]. 
Наряду с перечисленными средствами копирования/перемещения 
данных, как правило, во второй строке (стандартная панель) имеются 
пиктограммы, выполняющие эти операции быстрее: 

 – кнопка «Вырезать» удаляет выделенные данные из таблицы 
с сохранением их в буфере обмена; 

 – кнопка «Копировать» копирует выделенные данные из таблицы в буфер обмена; 

 – кнопка «Вставить» извлекает данные из буфера обмена в 
указанное место таблицы. 
Автозаполнение означает множественное копирование, когда содержимое исходной ячейки (блока) за одно действие «размножается» 
сразу в нескольких ячейках. Автозаполнение применяется к числовым, текстовым данным, содержащим числа в начале или конце текста, а также к данным типа дата. Такая операция возможна только 
для смежных ячеек таблицы. Операция автозаполнения заключается 
в следующем: подводим курсор мыши в правый нижний угол выделенной ячейки, курсор превращается в значок + (маркер заполнения). 
Буксируем маркер заполнения в соседние смежные ячейки. 
Рассмотрим различные варианты автозаполнения:  
а) выполняем операцию при нажатой клавише [Ctrl], тогда копируемая числовая константа в каждой следующей ячейке получит единичное приращение. Например, в ячейке находилось число 10. Выполнив операцию автозаполнения вправо (или вниз), получим числа 
11, 12, 13, …, а выполнение операции влево (или вверх) даст числа 9, 
8, 7, ….; 
б) в две смежные ячейки набираем два числа (или две текстовых 
величины), выделяем их и выполняем операцию, получим в следующих 

ячейках числа (тексты), причем каждое следующее число отличается от 
предыдущего на величину шага (равного разности первых двух чисел). 
Например, в ячейках набираем числа 15, 18 (или Группа 1, Группа 2), 
получаем числа 21, 24, 27, …(Группа 3, Группа 4, Группа 5, …); 
в) операция выполняется и для списков, созданных разработчиками Excel. Например, в ячейке набираем Понедельник (или Январь), 
получаем в следующих ячейках Вторник, Среда и т.д. (или Февраль, 
Март и т.д.). 
Очистка освобождает выделенный блок от находящихся в нем 
данных. Для этого достаточно нажать клавишу [Delete]. При этом не 
происходит освобождения ячеек блока от занесенных в них форматов и элементов оформления. Чтобы убрать все это, следует в меню 
Правка выбрать пункт Очистить и в открывшемся вспомогательном 
меню – пункт Все. 
Удаление означает изъятие самого объекта. Можно удалить ячейку, блок, строки, столбцы и т.д. При этом удаленные объекты замещаются соседними. Для удаления можно воспользоваться правой 
кнопкой мыши и в открывшемся контекстном меню выполнить 
пункт Удалить. 
Вставку элементов таблицы (ячейки, блока, строки, столбца) 
можно выполнить различными способами: 
а) удерживая клавишу [Shift], отбуксировать мышью в желаемом 
направлении маркер заполнения ячейки (блока, строки, столбца). После отпускания кнопки мыши в таблицу будет вставлено столько новых объектов, на сколько элементов был отбуксирован курсор мыши; 
б) после выделения объекта (строки, столбца, блока) щелчком 
правой мыши вызвать контекстное меню, из которого выбрать подпункт Вставка; 
в) выделить объект, перейти в меню Вставка и выбрать ячейки → откроется диалоговое окно, в котором нужно указать выбираемый объект 
(ячейки со сдвигом вправо; ячейки со сдвигом вниз; строку; столбец. 

1.7. Операции с рабочими листами 

Вставка/удаление листа может быть осуществлено с помощью 
меню Правка, подпункта Лист или Удалить лист, или щелком правой кнопкой мыши на вкладке листа внизу экрана. В появившемся 
меню выполнить пункты Удалить или Добавить. Последний вызывает окно Вставка, где во вкладке Общие двойным щелчком по пиктограмме «Лист» создается новый лист с очередным номером. При