Табличный процессор Excel в экономических и финансовых расчетах

Табличный процессор Excel в экономических и
финансовых расчетах

2-е издание, исправленное

Н. Дудник Л.
А. Зарубина С.

Национальный Открытый Университет “ИНТУИТ”
2016

2

Табличный процессор Excel в экономических и финансовых расчетах/ Л. Н. Дудник, С. А. Зарубина М.: Национальный Открытый Университет “ИНТУИТ”, 2016

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

(c) ООО “ИНТУИТ.РУ”, 2021-2016
(c) Н. Дудник Л., А. Зарубина С., 2021-2016

3

Создание и редактирование таблиц

Цель работы: научиться создавать и редактировать таблицы в табличном процессоре
Excel. Содержание работы: Создание таблицы в Excel. Ввод данных и формул в
таблицу. Форматирование таблиц. Порядок выполнения работы: Изучить методические
указания. Выполнить задания. Оформить отчет и ответить на контрольные вопросы.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Запуск программы

Запуск процессора Excel 2007 можно осуществить несколькими способами:

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

Элементы экрана

Верхняя строка окна приложения Microsoft Excel называется строкой заголовка (рис.
1.1 рис. 1.1). В ней слева указывается имя программы Microsoft Excel и название
рабочей книги Книга 1 (либо открытого файла).

Строка меню располагается под строкой заголовка. Каждое из меню объединяет набор
команд, имеющих общую функциональную направленность.

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

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

Строка формул располагается под панелями инструментов и демонстрирует адрес
текущей ячейки и ее содержимое. Эта строка разделена по вертикали на три секции. В
левой секции высвечивается адрес активной ячейки или присвоенное ей имя. В правой
секции строки формул отображается содержимое (данные или формула) активной
ячейки, если они введены.

4

Рабочий экран Excel разделен по вертикали на столбцы, а по горизонтали на строки.
Столбцам присваиваются имена, соответствующие буквам латинского алфавита, либо
числам (в зависимости от выбранного стиля с помощью Главное офисное меню >
Параметры Excel > Формулы), а именами строк являются только числа. Области имен
столбцов и строк располагаются в верхней (столбцы) и в левой (строки) части и
называются заголовками. Пользуясь Excel, можно создать таблицы размером до 256
столбцов (название последнего – IV) и 65536 строк.

Рис. 1.1.  Экран MS Excel

Пересечение строк и столбцов образует клетки, называемые ячейками таблицы. Все
ячейки имеют адреса. Адрес любой ячейки состоит из имени столбца и номера строки,
на пересечении которых она расположена, например, А20, BE6, IA300.

Активная ячейка выделяется жирным контуром (рамкой), называемым табличным
курсором. Именно в активную ячейку осуществляется ввод данных.

На экране Excel размещаются две полосы: вертикальная – у правого края экрана и
горизонтальная – у нижнего края. С помощью этих полос удобно осуществлять
быстрое перемещение по таблице.

Расположенная в нижней части экрана строка состояния содержит ряд полезных при
создании таблицы сведений. На этой строке выводится информация о выбранной
команде или выполняемой операции. Правая часть строки состояния показывает
значение суммы числовых данных в активной ячейке и состояние включения цифровой

5

клавиатуры (клавиши Num Lock>).

Ввод данных в таблицу

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

Закончить ввод данных в текущей строке можно нажатием:

клавиши <Enter> – данные зафиксируются в текущей ячейке, и выделением
переместится на одну ячейку вниз.
кнопки с “галочкой” на строке формул – данные зафиксируются в текущей ячейке,
и выделение останется в этой же ячейке;
любой клавиши со стрелкой – данные зафиксируются в текущей ячейке, и
выделение переместится в ячейку в направлении, указанном стрелкой;
кнопки с крестом на строке формул или клавиши <Esc> – ввод данных будет
отменен.

По окончании ввода текстовые данные выравниваются по левому краю ячейки,
числовые – по правому (по умолчанию). Если выравнивание требуется изменить,
нужно воспользоваться командой Главная > Выравнивание или Формат Ячейки,
вкладка Выравнивание (контекстное меню). При вводе нецелочисленных данных
десятичные знаки определяются с помощью запятой. Если следует изменить
десятичный разделитель на точку, надо последовательно выполнить настройки с
помощью Панели управления.

Ввод формул

Ввод формулы обязательно должен начинаться со знака равенства “=” и завершаться
клавишей <Enter>.

В составе формул могут быть числа, функции, ссылки на адреса и имена ячеек,
операторы сложения (+), вычитания (-), умножения (*), деления (/), круглые скобки для
задания приоритетности операций, а также текст заключенный в кавычки. В Excel
используют логические операторы сравнения, результатом выполнения которых
является логическое значение (Истина или Ложь).

Таблица 1.1. Основные арифметические знаки

и операторы сравнения

Арифметические знаки Операторы сравнения
+ сложение
= равно

- вычитание
<> не равно

6

/ деление
> больше

* умножение
< меньше

^ возведение в степень >= больше или равно
% процент
<= меньше или равно

Когда в одной формуле объединяются несколько операций, операторы выполняются в
следующем порядке:

процент (%);
возведение в степень (^);
умножение и деление (*, /);
сложение и вычитание (+, -);
операторы сравнения (=, <>, > ,<, >=, <=).

Операторы с одинаковым приоритетом выполняется слева направо.

Изменить порядок вычисления можно, объединяя выражения в формуле круглыми
скобками: выражения в круглых скобках выполняются в первую очередь.

Адреса ячеек в формулы следует помещать с помощью указания мышью на
соответствующую ячейку (диапазон ячеек).

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

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

Организация ссылок

При перемещении или копировании формулы адрес в указанной ссылке изменяется,
ориентируясь на ту позицию, в которую переносится формула. Такие ссылки носят
название относительных ссылок (рис. 1.2. рис. 1.2).

Рис. 1.2.  Пример относительной ссылки

Для ввода в формулу значения из фиксированной ячейки (адрес которой при
копировании или перемещении формулы остается неизменным) используются
абсолютные ссылки (рис. 1.3 рис. 1.3). При их обозначении в написании адреса ячейки

7

добавляется знак доллара. (Например, $A$20, $IA200).

В случае изменения только одного значения адреса и фиксации другого используются
смешанные ссылки (рис. 1.4 рис. 1.4). Знаком $ фиксируется только имя столбца
(например, $А9) или имя строки (например, E$6). Для ввода смешанных и абсолютных
ссылок используется клавиша <F4> (курсор в этом случае помещается либо внутрь
ссылки, либо после нее).

Рис. 1.3.  Пример абсолютной ссылки

Рис. 1.4.  Пример смешанной ссылки

Автоматическое вычисление сумм

При необходимости суммирования большого количества данных, расположенных в
смежных ячейках, может оказаться более удобным воспользоваться встроенной
функцией автосуммирования, а не писать длинную формулу с перечислением всех
слагаемых. Для этого следует щелкнуть мышью по кнопке Автосумма стандартной
панели – при этом в строке формул появится написанная функция суммирования с
указанием предлагаемого диапазона ячеек, данные которого подлежат суммированию.
Если окажется, что диапазон указан правильно, то необходимо нажать клавишу
<Enter>. В противном случае сначала надо ввести исправления в указанный диапазон
(щелкнуть для этого по строке формул и ввести новые адреса ячеек), а затем нажать
клавишу <Enter>.

Для задания функции автосуммирования сразу нескольких ячеек можно выделить их, а
затем нажать кнопку Автосумма.

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

8

Редактирование табличных документов

Перемещение по таблице

При перемещении по таблице (изменение адреса активной ячейки) можно
использовать:

четыре клавиши со стрелками, обеспечивающие перемещение во всех четырех
направлениях;
клавиши <PgUp> и <PgDn>, осуществляющие поэкранное перемещение
выделения соответственно вверх и вниз по таблице;
клавиша <Home>, позволяющая перемещать выделение на начало текущей строки;
клавиши <Ctrl><Home> приводят к перемещению выделения в ячейку A1, а
нажатие клавиш <Ctrl><End> вызывает перемещения выделения в последнюю
заполненную ячейку таблицы;
щелчок указателя мыши в соответствующей позиции экрана приводит к
перемещению выделения в данную ячейку;
для перемещения по таблице можно использовать полосы прокрутки (как это было
описано ранее);
можно быстро перейти в любую ячейку таблицы, если нажать клавишу <F5> или
задать команду Главная > Редактирование> Найти и выделить > Перейти и ввести
адрес этой ячейки или имя диапазона в поле Ссылка.

Выделение фрагментов таблицы

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

Выделение произвольного непрерывного фрагмента: установить курсор на начало (или
конец) выделяемого фрагмента. Далее либо протянуть по всей выделяемой области
мышью (при нажатой левой кнопке), либо нажать клавишу <Shift> и, удерживая ее
нажатой, с помощью клавиш перемещения курсора выделить требуемую область.

Выделение всей строки: щелкнуть мышью по номеру строки или установить подсветку
в любой ячейке данной строки и нажать клавиши <Shift><Пробел>,

Выделение всей колонки: щелкнуть мышью на имени столбца или выделить любую
ячейку данного столбца и нажать клавиши <Ctrl><Пробел>.

Выделение всей таблицы: щелкнуть мышью по левому углу бордюра (по кнопке
расположенной между заголовками строк и столбцов) и нажать клавиши <Ctrl><Shift>
<Пробел>.

Выделение несмежных областей: возможно двумя способами:

9

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

Редактирование табличных данных

Под редактированием табличных данных мы будем понимать: замену одних данных в
ячейке на другие, удаление данных из ячейки, перемещение их в другую позицию,
вставку строк и столбцов в таблицу и удаление строк и столбцов из таблицы.

Для замены одних данных другими достаточно установить курсор в эту ячейку и
ввести новые данные. Для редактирования данных в ячейке надо прежде всего
установить курсор в нужную ячейку. Далее можно:

щелкнуть два раза мышью в ячейке или нажать клавишу <F2>. Редактирование в
этом случае осуществляется в самой ячейке;
щелкнуть по строке формул и редактировать данные в этой строке.

Для удаления символов можно использовать клавиши <Delete> и <Backspace>, а для
управления режимами Вставка / Замещение клавишу <Insert>. По завершению
редактирования нажать клавишу <Enter> или кнопку с галочкой в строке формул.

Для удаления содержимого ячейки (или нескольких выделенных ячеек) выделить
очищаемую область или установить курсор на очищаемой от данных ячейке, нажать
клавишу <Delete> или выполнить команду Очистить содержимое (контекстного меню).

Для перемещения данных в другую позицию выделить их. Далее можно:

задать команду Главная 
 Вырезать панели инструментов из группы Буфер

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

Для вставки строки в таблицу установить курсор в любую ячейку строки, перед,
которой будет осуществляться вставка, и задать команду Главная 
 Вставить 

Строки на лист.

Для вставки столбца в таблицу установить курсор в любую ячейку столбца, перед,
которой будет осуществляться вставка, и задать команду Главная 
 Вставить 

Столбцы на лист.

Для удаления строк или столбцов из таблицы установить курсор в любую ячейку,
принадлежащую строке или столбцу, которые надо удалить, и задать команду Главная 

10