Excel. Единый справочник

509 Pages·2005·68.79 MB·Russian

by  Шитов В.Н.

В. Н. Шитов
EXCEL
ЕДИНЫЙ СПРАВОЧНИК
Москва 2005
УДК 004.67(035)
ББК32.973.2б-018.2я2
Ш64
Шитов, Виктор Николаевич
Ш64 Excel. Единый справочник/В. Н. Шитов. — М.: ГроссМедиа,
2005. - 512 с. - ISBN 5-476-00111-5
Агентство С/Р РГБ
В книге описаны основные методы работы с популярным программным продук-
том Microsoft Excel 2002, предназначенным для работы с электронными таблицами.
В издании приводится огромное число примеров и иллюстраций по работе с электрон-
ными таблицами. Читатель найдет много советов и приемов, которые потом может
применить'в самостоятельной работе. Книга рассчитана на пользователей с различ-
ным уровнем подготовки.
Воспроизведение в полном объеме или фрагментарно любыми способами текстов, опубликованных в
настоящем издании, за исключением случаев, установленных Законом об авторском праве и смеж-
ных правах, допускается только с письменного согласия обладателя исключительных имуществен-
ных прав на произведение. Все права защищены.
ISBN 5-476-00111-5 © ЗАО «ГроссМедиа Ферлаг», 2005
© ООО «ГроссМедиа», 2005
ОГЛАВЛЕНИЕ
ГЛАВА 1. Введение 13
1.1. Интерфейс Microsoft Excel 13
1.2. Инструментальные панели 16
1.2.1. Основные инструментальные панели .16
1.2.2. Включение инструментальных панелей 16
1.2.3. Превращение плавающих панелей в стационарные 18
1.2.4. Удаление инструментальных панелей 18
1.2.5. Добавление новых кнопок на инструментальную панель 19
1.2.6. Удаление кнопок с инструментальной панели 19
1.2.7. Создание пользовательской инструментальной панели 20
1.2.8. Присоединение инструментальной панели к книге 21
1.2.9. Контекстное меню инструментальных кнопок 22
1.2.10. Редактирование изображений на кнопках 25
1.3. Передвижение по ячейкам электронной таблицы 27
1.3.1. Передвижение по рабочему листу с помощью клавиатуры и мыши 27
1.3.2. Изменение порядка перехода к другой ячейке 27
1.3.3. Выделение диапазона ячеек 28
1.3.4. Выделение за пределами рабочего окна 31
1.3.5. Выделение группы диапазонов ячеек 34
1.3.6. Выделение столбца или строки 35
1.3.7. Выделение и перемещение внутри блока ячеек 36
1.3.8. Прочие методы перемещения по рабочему листу 37
1.4. Создание первого рабочего листа 39
1.4.1. Ввод данных 39
1.4.2. Защита данных 45
1.4.3. Пример ввода данных 48
1.4.4. Использование команды Автоформат 52
1.5. Создание первой формулы ." 52
1.6. Пример форматирования ячеек 53
1.7. Сохранение рабочей книги 59
1.8. Печать документа 62
1.9. Закрытие книги и системы 64
ГЛАВА 2. Редактирование книги 65
2.1. Открытие сохраненной книги 65
2.2. Внесение изменений в книгу 66
Excel. Единый справочник
' 2.3. Сообщения об ошибках 72
2.4. Введение в массивы 75
ГЛАВА 3. Операции с рабочим листом 80
3.1. Добавление нового листа, удаление листа, переименование листа 80
3.2. Перемещение и копирование листов в книге 81
3.3. Масштабирование рабочих листов 83
3.4. Книги и окна 84
3.6. Работа с графикой 88
3.6.1. Связывание графики и ячеек 88
3.6.2. Инструментальная панель Рисование 88
3.6.3. Автофигуры 90
3.6.4. Список Другие фигуры 91
3.6.5. Правила рисования на рабочем листе 92
3.6.6. Работа с маркерами 95
3.6.7. Работа с объектами WordArt 95
3.6.8. Построение организационных диаграмм 97
3.6.9. Затенение и объемность объектов 100
3.6.10. Список Действие .....:..;........: 100
3.6.11. Диалоговое окно Формат автофигуры " 102
3.6.12. Импортирование графических файлов 103
3.7. Примечания к ячейкам 107
3.8. Печать рабочих листов ПО
3.8.1. Быстрая печать документа ПО
3.8.2. Диалоговое окно Печать 111
3.8.2.1. Выбор принтера 111
3.8.2.2. Выбор печатаемого диапазона 112
3.8.2.3. Печать нескольких копий 112
3.8.3. Диалоговое окно Параметры страницы 113
3.8.3.1. Задание параметров страницы 113
3.8.3.2. Масштаб печати 114
3.8.3.3. Размер бумаги и качества печати 114
3.8.3.4. Задание размеров полей 115
3.8.3.5. Задание колонтитулов ......' : 115
3.8:3.6. Печать заголовков 117
3.8.3.7. Организация последовательности вывода страниц 117
3.8.3.8. Печать сетки и заголовков строк и столбцов . ..; 118
3.8.3.9. Вставка разрывов страниц и их удаление 118
3.8.3.10. Скрытие конфиденциальной информации 119
3.8.4. Предварительный просмотр книги перед печатью 119
3.8.4.1. Вызов предварительного просмотра ','.' 119
3.8.4.2. Масштабирование просмотра 120
3.8.4.3. Настройка ширины полей и столбцов 121
3.8.4.4. Разметка страниц 122
3.8.4.5. Использование представлений 123
3.8.4.6. Задание области печати •'...• 124
ГЛАВА 4. Форматирование рабочих листов 126
4.1. Инструменты форматирования 126
4.1.1. Инструментальная панель Форматирование 126
4.1.2. Кнопка Формат по образцу 128
4.1.3. Создание пользовательской инструментальной панели 129
4.1.4. Команда Автоформат 130
4.2. Направления форматирования рабочих листов 131
4.2.1. Форматирование шрифта 1 .131
4.2.1.1. Изменение имени шрифта • ; 131
Оглавление
4.2.1.2. Выбор стилей шрифта и выравнивания шрифта 131
4.2.1.3. Дополнительные средства выравнивания 132
4.2.2. Применение границ 133
4.2.3. Применение цвета, узора и заливки 136
4.2.4. Настройка ширины столбца 138
4.2.4.1. Стандартная ширина столбца 138
4.2.4.2. Изменение ширины столбца с помощью мыши 139
4.2.4.3. Использование команды Автоподбор ширины 140
4.2 А А. Точная ручная установка ширины столбца 140
4.2.4.5. Сокрытие и открытие столбцов 141
4.2.5. Настройка высоты строк • 141
4.2.6. Использование стилей 142
4.2.6.1. Понятие стиля 142
4.2.6.2. Основные стили Microsoft Excel 142
4.2.6.3. Создание пользовательского стиля '. 143
4.2.6.4. Изменение стиля 144
4.2.6.5. Применение стилей 145
4.2.6.6. Объединение стилей из разных книг 145
ГЛАВА 5. Использование шаблонов 147
5.1. Назначение шаблонов 147
5.2. Применение шаблонов 147
5.3. Создание новых шаблонов 148
ГЛАВА 6. Применение формул 150
6.1. Назначение формул 150
6.1.1. Операторы формул 150
6.1.2. Приоритеты операторов 151
6.2. Ввод формул • 152
6.3. Ссылки в формулах 152
6.3.1. Виды ссылок 152
6.3.2. Ссылки на ячейки других рабочих листов и книг 153
6.3.3. Циклические ссылки 154
6.3.4. Присвоение формулам имен 156
6.3.5. Получение точной копии формулы 156
6.4. Формулы массивов 157
6.4.1. Формулы массивов для отдельных ячеек 157
6.4.2. Редактирование формулы массива 159
6.4.3. Создание формул массивов, распространяющихся
на несколько ячеек 161
6.5. Применение формул массивов 163
6.5.1. Поиск ошибок с помощью массива .'. 163
6.5.2. Условное суммирование с помощью формул массивов 164
6.5.3. Поиск значений в массивах 165
ГЛАВА 7. Применение функций и анализ данных 167
7.1. Введение в функции 167
7.1.1. Понятие функции 167
7.1.2. Аргументы функций 169
7.1.3. Использование имен в качестве аргументов 170
7.1.4. Ввод функций вручную 171
7.1.5. Ввод функции с помощью Мастера функций 173
7.2. Математические функции 177
7.2.1. Функция СУММ " 177
7.2.2. Функция ОКРУГЛ 179
7.2.3. Функция ПИ •:. 180
7.2.4. Функция КОРЕНЬ 180
8 Excel. Единый справочник
7.2.5. Функция СТЕПЕНЬ .180
7.2.6. Функция ЦЕЛОЕ 181
7.2.7. Функция РИМСКОЕ 181
7.2.8. Функция ПРОИЗВЕЛ 182
7.2.9. Функция ЕЧИСЛО 182
7.2.10. Функция ОСТАТ 183
7.2.11. Функция ФАКТР 183
7.2.12. Функция ABS '. 184
7.3. Тригонометрические функции i 184
7.3.1. Функция SIN 184
7.3.2. Функция ASIN 185
7.3.3. Функция SINH ' 185
7.3.4. Функции ГРАДУСЫ и РАДИАНЫ 185
7.4. Статистические функции и анализ '. 186
7.4.1. Основные встроенные статистические функции 186
7.4.1.1. Функция СРЗНАЧ 186
7.4.1.2. Функция МЕДИАНА 186
7.4.1.3. Функция МОДА 187
7.4.1.4. Функция МАКС 188
7.4.1.5. Функция МИН 188
7.4.1.6. Функция СЧЕТ 188
7.4.1.7. Функция СУММЕСЛИ 189
7.4.1.8. Функция СУММПРОИЗВ 189
7.4.1.9. Функция СУММКВ 190
7.4.2. Прочие статистические функции и инструменты 190
7.4.2.1. Инструмент Описательная статистика 191
7.4.2.2. Инструмент Гистограмма 194
7.4.2.3. Инструмент Ранги персентиль 196
7.4.3. Генерация случайных чисел 198
7.4.3.1. Равномерное распределение 199
7.4.3.2. Нормальное распределение 200
7.4.3.3. Распределение Бернулли .. 202
7.4.3.4. Распределение Пуассона 203
7.4.3.5. Биномиальное распределение 204
7.4.4. Инструмент Выборка 205
7.4.5. Линейная и экспоненциальная регрессия. Инструмент Регрессия . . . 205
7.4.6. Вычисление скользящего среднего 211
7.4.7. Прочие инструменты статистического анализа 212
7.5. Логические функции 217
7.5.1. Функция ЕСЛИ 217
7.5.2. Функция И 219
7.5.3. Функция ИЛИ 220
7.5.4. Функция НЕ 220
7.5.5. Функции ИСТИНА и ЛОЖЬ 221
7.5.6. Прочие логические функции 221
7.6. Финансовые функции и анализ 223
7.6.1. Функции для анализа инвестиций 223
7.6.1.1. Функция ПЗ 223
7.6.1.2. Функция НПЗ 224
7.6.1.3. Функция БЗ 225
7.6.1.4. Функция ППЛАТ 226
7.6.1.5. Функция ПЛПРОЦ 226
7.6.1.6. Функция ОСНПЛАТ 227
7.6.1.7. Функция КПЕР 228
Оглавление
7.6.2. Функции для вычисления скорости оборота 228
7.6.2.1. Функция НОРМА 228
7.6.2.2. Функция ВНДОХ 229
7.6.2.3. Функция МВСД 230
7.6.3. Функции для вычисления амортизации 231
7.6.3.1. Функция AMP 231
7.6.3.2. Функция ДОБ и ДЦОБ 231
7.6.3.3. Функция ПДОБ 232
7.6.3.4. Функция АМГД 233
, 7.6.4. Функции для анализа ценных бумаг 234
7.6.4.1. Функции РУБЛЬ.ДЕС и РУБЛЬ.ДРОБЬ 234
7.6.4.2. Функции НАКОПДОХОД и НАКОПДОХОДПОГАШ 234
7.6.4.3. Функции ИНОРМА и ПОЛУЧЕНО 237
7.6.4.4. Функции ЦЕНА, ЦЕНАСКИДКА, ЦЕНАПОГАШ 238
7.6.4.5. Функции ДОХОД, ДОХОДСКИДКА, ДОХОДПОГАШ 239
7.6.4.6. Функция СКИДКА 241
7.6.4.7. Функции ДНЕЙКУПОНДО, ДНЕЙКУПОН,
ДНЕЙКУПОНПОСЛЕ, ДАТАКУПОНДО,
ДАТАКУПОНПОСЛЕ, ЧИСЛКУПОН 241
7.6.4.8. Функции ЦЕНАПЕРВНЕРЕГ, ЦЕНАПОСЛНЕРЕГ,
ДОХОДПЕРВНЕРЕГ, ДОХОДПОСЛНЕРЕГ 243
7.6.4.9. Функции ДЛИТ, МДЛИТ 245
7.7. Текстовые функции 246
7.7.1. Функция ЗНАЧЕН 246
7.7.2. Функция ТЕКСТ 247
. 7.7.3. Функция РУБЛЬ 247
7.7.4. Функция ФИКСИРОВАННЫЙ 248
7.7.5. Функция СЖПРОБЕЛЫ 248
7.7.6. Функция ДЛСТР 249
7.7.7. Прочие функции для работы с текстом 249
7.7.8. Создание формул с использованием нескольких текстовых функций . 252
7.7.8.1. Подсчет числа заданных символов 252
7.7.8.2. Поиск первого слова в строке 252
7.7.8.3. Подсчет числа слов в строке 253
7.7.8.4. Поиск имени, отчества и фамилии в строке 253
7.8. Инженерные функции 254
7.8.1. Функция ПРЕОБР 254
7.8.2. Функция ПОРОГ 257
7.8.3. Функции БЕССЕЛЬ 258
7.8.4. Функции преобразования оснований исчисления чисел 260
7.8.5. Функции ФОШ и ДФОШ ' 261
7.9. Функции информационной категории 263
7.9.1. Функции ЕТЕКСТ и ЕНЕТЕКСТ , 263
7.9.2. Функции ЕЧИСЛО и ЕССЫЛКА 263
7.9.3. Функции СЧИТАТЬПУСТОТЫ, ТИП, ТИП.ОШИБКИ 264
7.10. Функции для работы с датами и временем 265
7.10.1. Ввод дат и времени 265
7.10.2. Форматирование дат и времени , 268
7.10.3. Арифметические операции с датами и временем 271
7.10.4. Функции для работы с датами и временем 272
7.10.4.1. Функция ДАТА 272
7.10.4.2. Функция СЕГОДНЯ 272
7.10.4.3. Функция ВРЕМЯ 272
7.10.4.4. Функция ТДАТА 273
"JO Excel. Единый справочник
7.10.4.5. Функция ДЕНЬНЕД 273
7.10.4.6. Функции ГОД, МЕСЯЦ, ДЕНЬ . . . 274
7.10.4.7. Функции ЧАС, МИНУТЫ, СЕКУНДЫ .......... 274
7.10.4.8. Функции ДАТАЗНАЧ и ВРЕМЗНАЧ , 275
7.10.4.9. Функция ДНЕЙ36О ,, 275
7.10.4.10. Функция ДОЛЯГОДА 276.
7.10.4.11. Функции ДАТАМЕС и КОНМЕСЯЦА 277
7.10.4.12. Функции РАБДЕНЬ и ЧИСТРАБДНИ . .'.': 277
ГЛАВА 8. Анализ данных . . .. 280
8.1. Работа со списками 280
8.1.1. Понятие списка .:...... 280
8.1.2. Создание списка 280
8.1.3. Заполнение списка с помощью Формы 281
8.1.4. Поиск записи в Форме 281
8.1.5. Выход за пределы списка 283
8.2. Фильтрация списков 283
8.2.1. Понятие фильтрации данных 283
8.2.2. Применение Автофильтра 283
8.2.3. Создание пользовательского автофильтра 285
8.2.4. Использование шаблонов фильтрации 286
8.2.5. Выход из Автофильтра ..:... 287
8.2.6. Копирование отфильтрованных записей 288
8.2.7. Применение Расширенного фильтра 288
8.2.8. Задание критериев отбора :.'. 289
8.2.9. Операторы сравнения в Расширенном фильтре 290
8.2.10. Использование вычисляемых критериев.. 290
8.2.11. Фильтрация с помощью Формы ...,..: 291
8.3. Сортировка списка 292
8.3.1. Понятие сортировки списка 292
8.3.2. Команда Сортировать . . 292
8.3.3. Установка параметров . . : 293
8.3.4. Нестандартные способы сортировки 294
8.3.5. Создание пользовательского нестандартного способа сортировки .... 294
8.3.6. Сортировка части списка 295
8.3.7. Сортировка столбцов. •...•; 297
8.4. Выполнение вычислений в списках 298
8.4.1. Команда Итоги 298
8.4.2. Простой расчет промежуточных итогов 299
8.4.3. Операции при создании промежуточных итогов 301
8.4.4. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ ....... 301
8.4.5. Использование нескольких критериев •....:• 303
8.4.6. Вложенные итоги 303
8.4.7. Свертывание записей итогов 304
8.4.8. Функции для работы с базами данных 305
8.4.9. Функции СУММЕСЛИ и СЧЕТЕСЛИ 306
8.4.10. Использование функций для работы с базами данных 307
8.5. Применение сводных таблиц 307
8.5.1. Основные понятия сводных таблиц 307
8.5.2. Отбор данных для создания сводных таблиц 308
8.5.2.1. Создание сводных таблиц на основе списков или баз
данных Microsoft Excel 309
8.5.2.1.1. Мастер сводных таблиц и диаграмм — шаг 1 из 3 309
8.5.2.1.2. Мастер сводных таблиц и диаграмм — шаг 2 из 3 310
8.5.2.1.3. Мастер сводных таблиц и диаграмм — шаг 3 из 3 310
Оглавление "| "J
8.5.2.2. Параметры сводной таблицы , 311
8.5.2.3. Создание макета сводной таблицы 312
8.5.2.4. Создание макета сводной таблицы с помощью
- инструментальной панели Сводные таблицы 313
8.5.2.5. Создание сводной таблицы на основе внешних баз данных. ... 315
8.5.2.5.1. Мастер сводных таблиц и диаграмм — шаг 1 из 3 315
8.5.2.5.2. Мастер сводных таблиц и диаграмм — шаг 2 из 3 316
8.5.2.5.3. Мастер сводных таблиц и диаграмм — шаг 3 из 3 319
8.5.3. Изменение структуры сводной таблицы 319
8.5.4. Обновление сводной таблицы 320
8.5.5. Форматирование сводной таблицы 320
8.5.6. Создание диаграммы по сводной таблице 322
8.5.7. Вставка вычисляемого поля в сводную таблицу 322
8.6. Анализ «что-если» 323
8.6.1. Таблицы подстановки 323
8.6.1.1. Таблица подстановки с одним входом 323
8.6.1.2. Таблица подстановки с двумя входами 325
8.6.2. Диспетчер сценариев 326
8.6.3. Команда Подбор параметра 331
8.6.4. Поиск решения 333
ГЛАВА 9. Применение баз данных 341
9.1. Использование внешних баз данных 341
9.2. Создание запросов , 357
ГЛАВА 10. Диаграммы и графики .. .. ...I.......... 366
10.1. Типы диаграмм в Microsoft Excel 366
10.1.1. Варианты размещения диаграмм 366
10.1.2. Типы диаграмм • 369
10.1.3. Гистограммы 369
10.1.4. Линейчатые диаграммы 370
10.1.5. Графики .371
10.1.6. Круговые диаграммы : 372
10.1.7. Точечные диаграммы 373
10.1.8. Диаграммы с областями 374
10.1.9. Кольцевые диаграммы .374
10.1.10. Лепестковые диаграммы 375
10.1.11. Поверхностные диаграммы 376
10.1.12. Пузырьковые диаграммы 376
10.1.13. Биржевые диаграммы 377
10.1.14. Цилиндрические, конические и пирамидальные диаграммы 378
10.1.15. Нестандартные диаграммы : 379
10.2. Построение диаграмм 382
10.2.1. Создание диаграммы по умолчанию 382
10.2.2. Создание диаграмм с помощью Мастера диаграмм 383
10.2.2.1. Мастер диаграмм — шаг 1 из 4 : 384
10.2.2.2. Мастер диаграмм — шаг 2 из 4 '. 386
10.2.2.3. Мастер диаграмм — шаг 3 из 4 387
10.2.2.4. Мастер диаграмм — шаг 4 из 4 392
10.2.2.5. Создание диаграмм вручную 392
10.2.2.6. Модификация диаграмм 393
10.3. Настройка диаграмм 393
10.3.1. Формат области диаграммы.
Формат области построения диаграммы 395
10.3.2. Формат легенды 400
10.3.3. Формат оси. Формат названия оси. .. 402