ОГЛАВЛЕНИЕ
ПРЕДИСЛОВИЕ 3
ГЛАВА 1. ЗНАКОМСТВО С ЭЛЕКТРОННОЙ ТАБЛИЦЕЙ
MS EXCEL 5
1.1. ЗАПУСК MS EXCEL 5
1.2. РАБОЧАЯ ОБЛАСТЬ MS EXCEL 5
Строка меню 6
Панели инструментов 7
Строка формул 7
Окно книги 8
Строка состояния 10
1.3. ПОЛУЧЕНИЕ СПРАВОЧНОЙ ИНФОРМАЦИИ 11
1.4. РАБОТА С ФАЙЛАМИ 12
1.5. ПЕРЕМЕЩЕНИЕ ПО ЛИСТУ И ВЫДЕЛЕНИЕ ЯЧЕЕК 12
1.6. РАЗМЕР СТОЛБЦОВ И СТРОК, ЕГО ИЗМЕНЕНИЕ 14
1.7. СОЗДАНИЕ ТАБЛИЦ 15
Ввод текста заголовка таблицы 15
Форматирование текста заголовка таблицы 15
Установка шрифта 16
Выравнивание и направление содержимого ячейки 17
Обрамление ячеек 17
Заполнение фона ячеек 18
Пример создания таблицы 18
1.8. ВЫХОД ИЗ MS EXCEL 20
Контрольные вопросы 20
Задание 21
ГЛАВА 2. МАТЕМАТИЧЕСКИЕ ФОРМУЛЫ И ССЫЛКИ 28
2.1. ФОРМУЛЫ В EXCEL 28
Функции MS Excel. Арифметические и тригонометрические
функции 28
Ввод функций 33
2.2. ССЫЛКИ В EXCEL 34
Ссылки в пределах рабочего листа 34
Ссылки в стиле А1 34
Ссылки в стиле R1C1 35
2.3. ОТЛАДКА ФОРМУЛ 36
Режим отображения формул 37
Трассировка ссылок и зависимостей 37
Ошибочные значения 37
2.4. ОЧИСТКА, ВСТАВКА И УДАЛЕНИЕ НА РАБОЧЕМ ЛИСТЕ 38
Очистка содержимого ячеек 38
Удаление ячеек, строк и столбцов 39
Вставка ячеек, строк и столбцов 40
2.5. КОПИРОВАНИЕ И ПЕРЕМЕЩЕНИЕ ДАННЫХ И ФОРМУЛ 41
Перемещение содержимого ячейки 41
Перемещение с помощью буксировки 41
Перемещение с помощью команд 42
Копирование содержимого ячеек 43
Использование маркера заполнения 43
Копирование с помощью буксировки и команд 44
Контрольные вопросы 44
Задание 1 45
Задание 2 45
Задание 3 46
ГЛАВА 3. АДРЕСАЦИЯ С ПОЛЬЗОВАТЕЛЬСКИМ
ИМЕНОВАНИЕМ 49
3.1. ССЫЛКИ С ПОЛЬЗОВАТЕЛЬСКИМ ИМЕНОВАНИЕМ 49
Адресация столбцов и строк таблиц по заголовкам 49
Адресация ячеек пересечением диапазонов 50
Адресация ячеек по именам 51
Определение имен на уровне книги 51
Определение имен на уровне листа 52
Именованные константы и формулы 53
3.2. ССЫЛКА НА ДРУГИЕ ЛИСТЫ 53
3.3. ВСТРОЕННЫЕ СТАТИСТИЧЕСКИЕ ФУНКЦИИ 55
3.4. РАБОТА С ОКНАМИ КНИГ 55
Создание новой книги 56
Открытие книги 56
Открытие недавно открывавшегося файла 57
Автоматическое открытие файлов при запуске Excel 57
Перемещение между открытыми книгами 57
Размещение окон на экране 58
Свертывание и развертывание окна книги 59
Перемещение окна книги и изменение его размера 59
Сохранение и закрытие книг 60
Сохранение файла рабочего пространства 60
3.5. РАБОТА С ЛИСТАМИ 60
Вставка листа 61
Удаление листа 61
Переименование листа 62
Копирование листа 62
Перемещение листа 63
Группировка листов для редактирования, форматирования и реорганизации 64
Контрольные вопросы 66
Задание 66
ГЛАВА 4. ФОРМАТИРОВАНИЕ ЧИСЛЕННЫХ ДАННЫХ 70
4.1. ВВОД ДАННЫХ 70
Числовые значения 70
Ввод чисел 70
Специальные символы Excel 71
Ввод текста 72
4.2. ФОРМАТИРОВАНИЕ ОТОБРАЖАЕМЫХ ДАННЫХ 72
Форматирование чисел 72
Создание пользовательских числовых форматов 76
Символы форматирования 76
Форматирование положительных, отрицательных, нулевых и текстовых значений 78
Включение цвета в форматы 79
Условное форматирование 79
Стандартные средства условного форматирования 79
Условное форматирование по значению 80
Условное форматирование по результатам вычисления
формулы 81
Применение условий в пользовательских форматах 81
Скрытый числовой формат 82
Контрольные вопросы 82
Задание 82
Предметные области (варианты заданий) 85
Критерии условного форматирования (варианты заданий) 88
ГЛАВА 5. ДИАГРАММЫ 95
5.1. ДИАГРАММЫ: ОСНОВНЫЕ ПОНЯТИЯ И ТЕРМИНЫ 95
5.2. ТИПЫ ДИАГРАММ 97
5.3. ПРОЦЕДУРА СОЗДАНИЯ ДИАГРАММЫ С ПОМОЩЬЮ
МАСТЕРА ДИАГРАММ 103
5.4. НАСТРОЙКА ДИАГРАММЫ 106
Форматирование и изменение шкал осей 107
Выбор типа, цвета и толщины линии. Задание расположения делений шкалы и их подписей 107
Изменение шкалы оси 109
Вывод и форматирование сетки 112
Форматирование рядов и маркеров данных 112
Форматирование рядов и маркеров данных 112
Форматирование вторичной круговой
и кольцевой диаграмм 119
Изменение ориентации исходных данных 120
Отделение секторов круга и кольца 120
Смешивание нескольких типов диаграмм 120
Форматирование текстовых элементов 120
Форматирование фоновых областей 121
Создание пользовательского типа диаграмм 123
5.5. РАБОТА С ДАННЫМИ ДИАГРАММЫ 123
Добавление, замена и удаление данных 123
Изменение исходных значений с помощью маркеров данных 125
Контрольные вопросы 125
Варианты заданий 127
Задание 1 128
Задание 2 136
ГЛАВА 6. РАБОТА СО СПИСКАМИ ИЛИ С БАЗАМИ ДАННЫХ 148
6.1. СПИСОК. СОСТАВЛЯЮЩИЕ СПИСКА 148
Диапазон базы данных 149
Диапазон критериев 149
Диапазон для извлечения 149
6.2. ВВОД ДАННЫХ В СПИСОК ИЛИ В БАЗУ ДАННЫХ 150
Ввод имен полей 150
Ввод данных 150
Использование формы данных 150
Непосредственный ввод данных 151
6.3. СОРТИРОВКА ДАННЫХ 151
Сортировка по возрастанию-убыванию 151
Сортировка в особом порядке 151
Сортировка по четырем и более полям 153
6.4. ПОИСК, ФИЛЬТРАЦИЯ И РЕДАКТИРОВАНИЕ В СПИСКАХ
И В БАЗЕ ДАННЫХ 154
Критерии поиска 154
Использование формы данных 154
Автофильтр 155
Расширенный фильтр 157
Задание условий с использованием логической
операции ИЛИ 158
Задание условий с использованием логической операции И 159
Задание условий с одновременным использованием
логических операций И и ИЛИ 161
Задание текстовых условий 161
Использование вычисляемых условий 162
6.5. АНАЛИЗ СПИСКА С ПОМОЩЬЮ ПОДВЕДЕНИЯ
ПРОМЕЖУТОЧНЫХ ИТОГОВ 163
6.6. ФУНКЦИИ ДЛЯ АНАЛИЗА СПИСКА 166
Функции СЧЕТЕСЛИ и СУММЕСЛИ 166
Функции баз данных 167
6.7. ПРОВЕРКА ВВОДИМЫХ ЗНАЧЕНИЙ 169
Задание типа данных и допустимых значений 169
Сообщение для ввода 171
Задание сообщения об ошибке 171
Контрольные вопросы 172
Варианты заданий 174
ГЛАВА 7. АНАЛИЗ «ЧТО-ЕСЛИ» 183
7.1. АНАЛИЗ «ЧТО-ЕСЛИ» 183
7.2. ПОДБОР ПАРАМЕТРА 183
7.3. ПОИСК РЕШЕНИЯ 187
Формулировка задачи 187
Элементы диалогового окна «Поиск решения» 188
Параметры поиска решения 190
Примеры задач 191
Транспортная задача 191
Решение системы нелинейных уравнений 194
Контрольные вопросы 196
Варианты заданий 196
Задание 1. Найти корни уравнения 196
Задание 2. Найти все решения системы нелинейных
уравнений 197
Задание 3 198
ГЛАВА 8. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL 206
8.1. ФУНКЦИИ ДЛЯ АНАЛИЗА ИНВЕСТИЦИЙ 206
Функция ПЗ 206
Функция НП3 208
Функция БЗ 209
Функция ППЛАТ 210
Функция ПЛПРОЦ 211
Функция ОСНПЛАТ 211
Функция КПЕР 212
Функция БЗРАСПИС 212
Функция ЧИСТНЗ 213
Функция ОБЩПЛАТ 214
Функция ОБЩДОХОД 215
8.2. ФУНКЦИИ ДЛЯ ВЫЧИСЛЕНИЯ СКОРОСТИ ОБОРОТА 216
Функция НОРМА 216
Функция ВНДОХ 217
Функция МВСД 218
Функция ЧИСТВНДОХ 218
8.3. ФУНКЦИИ ДЛЯ ВЫЧИСЛЕНИЯ АМОРТИЗАЦИИ 219
Функция АМР 219
Функция ДДОБ 220
Функция ДОБ 221
Функция ПДОБ 222
Функция АМГД 223
Контрольные вопросы 224
Варианты заданий 225
ГЛАВА 9. ПЕЧАТЬ ЛИСТОВ 230
9.1. ОКНО ДИАЛОГА «ПЕЧАТЬ» 230
Определение области печати 230
Печать нескольких копий 231
9.2. ОКНО ДИАЛОГА «ПАРАМЕТРЫ СТРАНИЦЫ» 231
Вкладка «Страница» 232
Вкладка «Поля» 233
Вкладка «Колонтитулы» 234
Вкладка «Лист» 235
9.3. ПРИМЕЧАНИЯ 237
9.4. НАСТРОЙКА РАЗРЫВОВ СТРАНИЦ 238
9.5. ПРЕДВАРИТЕЛЬНЫЙ ПРОСМОТР 239
Контрольные вопросы 240
Варианты заданий 240
ГЛАВА 10. РАБОТА С МАССИВАМИ 243
Контрольные вопросы 247
Варианты заданий 247
ЛИТЕРАТУРА 249
ПРЕДИСЛОВИЕ
Электронные таблицы Excel являются мощным средством для выполнения расчетов различного характера и одновременно простым в использовании. Мы надеемся, что предлагаемое учебное пособие поможет в приобретении навыков работы с табличным процессором Excel. В книге рассмотрены возможности Excel для решения научно-технических, экономических и прикладных задач таких, как анализ табличных данных и построение диаграмм различных типов, составление статистических сводок и комплексных финансовых калькуляций, планирование и распределение ресурсов по сложным формулам с использованием различных функций.
Книга написана с учетом известных литературных источников по данной тематике. Материал книги был успешно использован в учебном процессе Рязанской государственной радиотехнической академии.
Книга состоит из восьми глав.
В гл. 1 описаны основные элементы рабочей области электронной таблицы Excel. Кроме того, рассмотрены принципы построения пользовательских таблиц.
Гл. 2 посвящена построению сложных математических формул с использованием встроенных функций Excel. Пояснено назначение абсолютных, относительных и смешанных типов ссылок, а также стилей ссылок A1 и R1C1.
Гл. 3 содержит материал, связанный с созданием и использованием именованных ячеек. Пояснено назначение имен на уровне книги и имен на уровне листа.
Гл. 4 посвящена созданию и использованию пользовательских числовых форматов. Рассматриваются правила использования условного форматирования по значению и по формуле.
В гл. 5 описываются возможности Excel в области построения высококачественных диаграмм. Вводятся основные понятия и элементы диаграмм. Рассмотрены вопросы, связанные с настройкой уже готовых диаграмм.
В гл. 6 показано, как в Excel производится работа со списками и базами данных (БД). Подробно описаны способы поиска и фильтрации данных с использованием формы данных, автофильтра и расширенного фильтра, а также способы сортировки данных и подведения промежуточных итогов. Рассмотрены функции для работы с БД.
Гл. 7 посвящена средствам анализа «что-если» Подбор параметра и Поиск решения. Здесь показано, как с помощью этих средств можно решать задачи линейного программирования, уравнения, системы уравнений.
В гл. 8 подробно описаны встроенные финансовые функции. С использованием этих функций можно решать следующие задачи: расчет ипотечной ссуды, оценка выгодности сделки, вычисление основных платежей и платы по процентам по ссуде с фиксированной годовой процентной ставкой, расчет срока погашения долга с постоянной годовой процентной ставкой, расчет амортизационных отчислений.
Каждая глава завершается вариантами заданий по соответствующей теме, что позволяет закрепить изученный материал.