Информатика : электронные таблицы 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]. 
Перемещение ячейки/блока также можно выполнить многими 
способами: 
а) курсор приблизить к любой границе выделенного блока (при 
этом курсор превратится в стрелку ) и, нажимая и удерживая кнопку мыши, переместить блок на новое место; 
б) выделить блок, перейти в меню Правка, выполнить подпункт 
Вырезать, курсор переместить в ячейку, с который начинается перемещаемый блок, и выполнить подпункт меню Вставить. Вместо 
меню Правка можно использовать нажатие правой кнопки мыши и 
при открытии контекстного меню выбрать соответственно Вырезать 
и Вставить; 
в) выделить блок, курсор приблизить к любой границе выделенного блока (при этом курсор превратится в стрелку ), нажать правую кнопку мыши и начать перемещать блок, при этом откроется