Table Of ContentГобарева Я.Л., Городецкая О.Ю., Золотарюк А.В.
ТЕХНОЛОГИЯ ЭКОНОМИЧЕСКИХ РАСЧЕТОВ
СРЕДСТВАМИ EXCEL
«Новейшая желтая» методичка
Отдельные главы (в сокращенном варианте)
Москва, 2006
ОГЛАВЛЕНИЕ
1. ТЕХНОЛОГИЯ ОБРАБОТКИ И АНАЛИЗА ЭКОНОМИЧЕСКОЙ ИНФОРМАЦИИ . . . . . . . . . . . . . 3
1.1. СПИСКИ И БАЗЫ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2. ФИЛЬТРАЦИЯ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.3. ФОРМИРОВАНИЕ ИТОГОВ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
1.4. АНАЛИЗ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
2. ОБРАБОТКА ДАННЫХ С ПОМОЩЬЮ ВСТРОЕННЫХ ФУНКЦИЙ EXCEL . . . . . . . . . . . . . . . . . . . . . 74
2.1. ТЕХНОЛОГИЯ ПРИМЕНЕНИЯ ВСТРОЕННЫХ ФУНКЦИЙ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
3. ТЕХНОЛОГИЯ РЕШЕНИЯ ЭКОНОМИЧЕСКИХ ЗАДАЧ С ПРИМЕНЕНИЕМ ФИНАНСОВЫХ
ФУНКЦИЙ EXCEL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.1. АНАЛИЗ ОПЕРАЦИЙ ПО КРЕДИТАМ И ЗАЙМАМ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
2
1. ТЕХНОЛОГИЯ ОБРАБОТКИ И АНАЛИЗА ЭКОНОМИЧЕ-
СКОЙ ИНФОРМАЦИИ
1.1.Списки и базы данных
1.1.1.Понятие списка и базы данных в Excel
Для анализа и обработки данных в Microsoft Excel имеется специальный
инструментарий, позволяющий структурировать и хранить данные в связанном
виде, а также составлять и получать различные отчеты. При этом таблицы дан-
ных должны быть представлены в виде списка или базы данных.
Списком называются таблицы, содержащие уникальные записи (имена по-
лей) в первой строке.
В терминологии Excel понятия «список» и «база данных» являются синони-
мами.
Строки таблицы называются записями базы данных, а столбцы – полями.
Таким образом, запись представляет собой набор полей одного объекта, а поле
– это определенная категория информации. Первая строка таблицы в этом слу-
чае является названием полей списка.
Например, на рис. 2.7 приведен фрагмент списка о сотрудниках некоторого
предприятия.
3
Рис. 2.7. Окно Excel со списком – сведениями о сотрудниках предприятия
Список содержит информацию о каждом сотруднике: номер личного дела,
ФИО, должность, пол, год рождения, и т.д. – это поля списка. Записью будет
каждая отдельная строка, описывающая конкретного сотрудника. Так, в строке
5 показана запись на сотрудника с личным номером 1265, а в строке 8 – сотруд-
ника с личным номером 1269.
При создании списков следует придерживаться следующих правил:
•
на одном рабочем листе следует размещать только один список;
•
размер списка не может превышать размеры листа Excel, т.е. число
столбцов не может быть более 256, а число строк – 65536;
4
•
список отделяется от других данных, по крайней мере, одной пустой
строкой и одним пустым столбцом. Поскольку пустая строка или стол-
бец указывает на окончание списка, то между заголовками столбцов и
записями не должно быть пустых строк;
•
каждый столбец списка должен содержать однородную информацию;
•
первая строка таблицы должна содержать имена полей списка, описыва-
ющих назначение соответствующего столбца;
•
необходимо избегать пустых строк и столбцов внутри списка.
Над созданными списками Excel можно выполнять следующие операции:
•
осуществлять поиск данных по заданным критериям;
•
выполнять сортировку записей;
•
фильтровать список;
•
подводить промежуточные итоги;
•
создавать итоговые таблицы данных.
1.1.2.Технология создания списков
Создание списка начинается из ввода наименований полей списка (см.
строку 3 рис. 2.7). Заполнение записей списка – ввод данных в их поля (см.
строки 4 – 23 рис. 2.7) осуществляется двумя способами: вручную, как и при
подготовке небольших таблиц, и с использованием специальной встроенной
формы данных.
Форма данных – это наиболее удобный способ для просмотра, изменения,
добавления, удаления записей списка, а также для поиска записей, удовлетворя-
ющих заданным условиям.
Форма данных, используемая при создании списков, а также при их обра-
ботке, вызывается следующими командами:
Установить курсор в пределах списка1 → Данные → Форма
1 Если выполнен только ввод наименований полей списка, для вызова формы следует
выделить эти поля.
5
В результате появляется диалоговое окно формы с пользовательскими тек-
стовыми полями для ввода значений полей списка (рис. 2.8). Имя окна формы
соответствует имени листа, на котором создается список.
Рис. 2.8. Диалоговое окно формы для работы со списками Excel
В левой части формы располагаются заголовки столбцов, т.е. имена полей
списка, и поля ввода и хранения соответствующих значений. Одновременно в
форме может выводиться до 32 полей списка. Справа представлены кнопки
управления списком. Перечень кнопок не фиксирован. Он меняется в зависимо-
сти от ситуации обработки записей. Краткая информация о кнопках представле-
на в табл. 2.1.
В правом верхнем углу формы находится индикатор номера текущей запи-
си (строки таблицы) и количество записей в списке, без учета строки заголовка.
Для ускорения процедуры ввода больших списков там, где это возможно,
например, для создания последовательностей повторяющихся или изменяю-
щихся по определенному правилу рядов, следует воспользоваться автозаполне-
нием. Автозаполнение автоматически включается при перетаскивании маркера
заполнения предварительно выделенных ячеек с исходными данными, содержа-
щими формулы, числа, даты, текст либо текст с числами. Маркер заполне-
ния находится в левом нижнем углу выделенных ячеек. К нему следует акку-
ратно подвести курсор мыши, и как только тот приобретет вид маленького чер-
6
ного крестика, необходимо, нажав и удерживая левую кнопку мыши, выпол-
нить перемещение по листу – вниз, вправо, влево или вверх, формируя требуе-
мую последовательность данных. Двойной щелчок левой кнопкой мыши по
маркеру обеспечивает автозаполнение вниз по указанному правилу до конца та-
блицы (ее нижней строки).
Таблица 2.1.
Назначение кнопок формы работы со списками
Кнопка Назначение
Добавить Открытие пустой формы для добавления новой записи
Удалить Удаление текущей записи из списка
Вернуть Восстановление измененных значений поля записи
Назад Переход к предыдущей записи; возврат из режима задания критерия
Далее Переход к следующей записи
Критерии Переход в режим задания критерия поиска данных в списке
Закрыть Закрытие окна формы
Очистить Удаление данных поля (в режиме задания критерия)
Вернуть Восстановление данных поля (в режиме критерия)
Правка Переход к редактированию содержимого полей записи
При автозаполнении формулы копируются, относительные адреса ячеек в
формулах соответственным образом изменяются. Числовые последовательно-
сти реализуются при задании значений в двух ячейках. Даты (дни недели, дни
месяца, месяцы, годы, время) последовательно изменяются. Текстовые данные
просто копируются, а текст с числами создает ряд с изменением чисел по ука-
занному правилу.
Более широкие возможности автозаполнения открываются с использовани-
ем команд меню. Здесь можно реализовать не только последовательности по
правилам арифметической прогрессии, но и геометрической, в том числе с ука-
занием граничного значения. Схема автозаполнения в этом случае может быть
представлена следующим образом.
Выделить ячейку с начальным значением последовательности →
7
Команда Правка → Заполнить → Прогрессия...1 →
Задать расположение последовательности: по строкам или по столбцам →
Задать значение шага → Выбрать тип последовательности →
Для последовательности Даты выбрать единицы измерения:
день, рабочий день, месяц, год →
Задать предельное значение последовательности → Кнопка ОК
Автозаполнение, реализованное с помощью команд меню, целесообразно
применять, например, при формировании графиков дежурств (работников охра-
ны, специалистов по отделу и т.п.).
Рис. 2.9. Диалоговое окно задания параметров последовательности
Для запрета автозаполнения и реализации только операции копирования
следует при перетаскивании маркера заполнения удерживать клавишу [Ctrl].
Еще одна возможность автоматизации заполнения списков открывается за
счет обеспечения ускоренного ввода требуемых данных, введенных в столбце
выше, только по их первым символам. Возможность достигается, если в окне
Параметры (рис. 2.10) установлен флажок Автозавершение значений ячеек (см.:
Команда Сервис → Параметры… → Вкладка Правка).
Как только ниже в списке будут введены символы, позволяющие однознач-
но идентифицировать значение элемента списка, оно появляется в ячейке. Ав-
томатически дополненные символы выделены. Нажатие клавиши [Enter] пред-
писывает поместить сформированное значение в ячейку. Продолжение набора
символов предполагает ввод в ячейку нового элемента списка.
1 Последующие операции выполняются в окне Прогрессия, изображенном на рис. 2.9.
8
Рис. 2.10. Диалоговое окно задания параметров правки данных
При работе со списками можно задать условия, позволяющие осуществлять
проверку данных, вводимых в ячейки, с целью недопущения ошибок.
Условия для определенного диапазона ячеек, или критерий правильности
ввода, реализуются следующей схемой.
Выделить ячейки1 → Команда Данные → Проверка → Вкладка Параметры2 →
Выбрать из списка тип данных → Задать интервал значений →
Вкладка Сообщение для ввода3 → Задать параметры подсказки →
Вкладка Сообщение об ошибке4 →
Выбрать вид действия при попытке ввода неверных данных →
Ввести текст сообщения об ошибке → ОК
1 Выделять следует как заполненные, так и пустые ячейки столбца или строки, в кото-
рые предполагается ввод однотипных данных.
2 Окно вкладки Параметры показано на рис. 2.11.
3 Окно вкладки Сообщение для ввода показано на рис. 2.12.
4 Окно вкладки Сообщение об ошибке показано на рис. 2.13.
9
Рис. 2.11. Диалоговое окно задания параметров проверки вводимых значений в
ячейки таблицы
Рис. 2.12. Диалоговое окно формирования подсказки, предваряющей ввод данных в
ячейку
10
