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



Содержание



Об авторе 1
Благодарности 2
Введение 3
Какие задачи можно решать в программе Excel 4
Новый облик программы Excel 2007 6
Лента 6
Использование ленты с помощью клавиатуры 7
Меню Office 10
Инструментальная Панель быстрого доступа 11
Новые возможности в программе Excel 2007 12
Об этой книге 13
Краткое содержание 13
Об этих стрелках 14
О сочетаниях клавиш 18
О щелчках кнопкой мыши 18
Примеры 18
О Web-сайте MissingManuals.com 18
Safari Enabled 19
Часть I. Рабочие таблицы 21
Глава 1. Создание и навигация таблиц 23
Создание основной таблицы 23
Создание новой рабочей книги 24
Добавление заголовков столбцов 25
Добавление данных 28
Редактирование данных 29
Навигация в Excel 31
Вкладки на ленте 33
Строка формул 35
Строка состояния 36
Параметры Excel 39
Сохранение файлов 41
Формат файлов Excel 2007 41
Сохранение таблиц в старых форматах 43
Сохранение таблицы в формате PDF 46
Сохранение таблиц с паролем 48
Восстановление 50
Открытие файлов 51
Еще об открытии файлов 53
Открытие нескольких таблиц 53
Глава 2. Добавление информации в таблицу 57
Добавление данных разных типов 57
Управление типами данных 59
Быстрые способы добавления данных 64
Автозавершение 64
Автозамена 65
Автозаполнение 66
Автоподбор ширины столбцов 69
Команды Отменить и Вернуть 70
Глава 3. Перемещение данных в таблице 74
Выделение ячеек 74
Выделение непрерывных областей 74
Создание несмежных выделений 78
Автоматическое выделение ваших данных 78
Выделение с помощью клавиатуры 79
Перемещение ячеек 81
Простые операции вырезания и вставки и копирования и вставки 81
Выполнение операций вырезания и вставки и копирования и вставки перетаскиванием 84
Буфер обмена 84
Специальная вставка 87
Добавление или перемещение столбцов или строк 89
Вставка столбцов 90
Вставка строк 90
Вставка скопированных или вырезанных ячеек 90
Удаление столбцов и строк 92
Глава 4. Управление листами и книгами 93
Листы и книги 93
Добавление, удаление и сокрытие листов 95
Присвоение имен и изменение порядка листов 97
Группировка листов 99
Перемещение листов из одной книги в другую 101
Команда Найти и заменить 103
Базовая команда Найти 103
Найти все 104
Расширенные возможности поиска 105
Поиск форматированных ячеек 107
Поиск и замена значений 109
Проверка орфографии 111
Параметры проверки орфографии 114
Глава 5. Форматирование ячеек 117
Форматирование значений ячеек 117
Форматирование чисел 120
Форматирование даты и времени 126
Дополнительные форматы для специальных чисел 128
Пользовательские форматы 129
Форматирование оформления ячейки 137
Выравнивание и ориентация 137
Шрифты и цвет 140
Границы и заливки 145
Рисование границ вручную 148
Глава 6. Приемы тонкого форматирования таблиц 152
Формат по образцу 152
Стили и темы 153
Пользовательские стили 155
Изменение стилей 157
Перемещение стилей между книгами 158
Темы: наборы стилей 159
Изменение тем 162
Условное форматирование 165
Основы условного форматирования 165
Выделение специфических значений 166
Гистограммы 170
Цветовые шкалы 172
Наборы значков 172
Тонкая настройка правила форматирования 175
Использование нескольких правил 180
Глава 7. Просмотр и печать таблиц 184
Управление отображением информации 184
Масштабирование 184
Одновременный просмотр удаленных частей таблицы 187
Закрепление столбцов или строк 190
Сокрытие данных 192
Сохранение установок просмотра 194
Одновременный просмотр нескольких рабочих книг 196
Печать 198
Как напечатать файл Excel 198
Быстрая печать 201
Предварительный просмотр распечаток 201
Создание верхних и нижних колонтитулов 204
Настройка параметров печати 206
Управление разбиением на страницы 210
Разрывы страниц 210
Масштабирование 211
Страничный режим: взгляд с высоты птичьего полета на вашу таблицу 212
Часть II. Формулы и функции 215
Глава 8. Построение простых формул 217
Создание простой формулы 217
Приоритет операций в Excel 219
Ссылки на ячейки 221
Как Excel форматирует ячейки, которые содержат ссылки на другие ячейки 222
Функции 223
Ошибки формул 229
Логические операторы 231
Ускорители создания формул 234
Создание формул методом "Наведи и нажми" 234
Редактирование формул методом "Наведи и нажми" 235
Вкладка Формулы 235
Использование кнопки Вставить функцию 237
Копирование формул 241
Абсолютные ссылки на ячейки 244
Смешанные ссылки 246
Ссылки на другие листы и книги 247
Обновление формул, которые ссылаются на другие книги 248
Глава 9. Математические и статистические функции 252
Округление чисел 252
Округление чисел: ОКРУГЛ(), ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ() 253
Еще функции округления: ОКРУГЛТ(), ОКРВВЕРХ() и ОКРВНИЗ() 254
Отбрасывание дробной части: ЦЕЛОЕ() и ОТБР() 255
Округление до четных или нечетных значений: ЧЁТН() и НЕЧЁТ() 257
Группировка чисел 257
Суммирование чисел: СУММ() 258
Подсчет числа элементов в списке: СЧЁТ(), СЧЁТЗ() и СЧИТАТЬПУСТОТЫ() 259
Определение максимального и минимального значений: МАКС() и МИН() 260
Ранжирование чисел: НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ() и РАНГ() 261
Поиск среднего и медианы: СРЗНАЧ() и МЕДИАНА() 262
Поиск часто встречающихся чисел в списке: РЕЖИМ() 264
Расширенные функции ранжирования: ПЕРСЕНТИЛЬ() и ПРОЦЕНТРАНГ() 264
Определение категории, к которой принадлежит число: ЧАСТОТА() 266
Математические функции общего назначения 268
Произведения, факториалы, степени и квадратные корни: ПРОИЗВЕД(), ФАКТР(), СТЕПЕНЬ() и КОРЕНЬ() 268
Деление: ЧАСТНОЕ() и ОСТАТ() 269
Абсолютное значение и определение знака числа: ABS() и ЗНАК() 270
Генерация случайных чисел: СЛЧИС() и СЛУЧМЕЖДУ() 270
Наибольший и наименьший общий знаменатель: НОД() и НОК() 273
Вычисление комбинаций и перестановок: ЧИСЛКОМБ() и ПЕРЕСТ() 273
Тригонометрические и расширенные математические функции 274
Расширенные статистические функции 277
Глава 10. Финансовые функции 282
Мир финансов 282
Финансовые функции 283
Будущая стоимость: БС() 283
Приведенная стоимость: ПС() 287
Вычисление количества необходимых платежей: ПЛТ(), ОСПЛТ(), ПРПЛТ() 288
Вычисление времени, которое понадобится на выплату займа или достижение цели инвестиции: КПЕР() 291
Вычисление процентной ставки, необходимой для достижения будущей стоимости: СТАВКА() 291
Чистая приведенная стоимость и внутренняя ставка доходности: ЧПС() и ВСД() 292
Амортизация 296
Другие финансовые функции 299
Глава 11. Работа с датами, временем и текстом 301
Работа с текстом 301
Объединение строк текста: СЦЕПИТЬ() 302
Копирование части текстовой строки: ЛЕВСИМВ(), ПСТР() и ПРАВСИМВ() 302
Подсчет символов в строке: ДЛСТР(), НАЙТИ() и ПОИСК() 303
Изменение регистра символов: ПРОПИСН(), СТРОЧН() и ПРОПНАЧ() 305
Удаление нежелательных пробелов и непечатаемых символов: СЖПРОБЕЛЫ() и ПЕЧСИМВ() 306
Замена одной последовательности символов другой: ПОДСТАВИТЬ() 306
Преобразование текста в числа и обратно: ТЕКСТ(), ЗНАЧЕН(), ФИКСИРОВАННЫЙ() и РУБЛЬ() 307
Другие текстовые функции 309
Работа с датами и временем 310
Осуществление математических операций с датами и временем 311
Использование дат и времени с обычными функциями 311
Функции даты и времени 312
Вставка текущих даты и времени: СЕГОДНЯ() и ТДАТА() 312
Даты и время в вычислениях: ДАТА() и ВРЕМЯ() 313
Дополнительные вычисления с датами: ДЕНЬ(), МЕСЯЦ() и ГОД() 315
Дополнительные вычисления со временем: ЧАС(), МИНУТЫ(), СЕКУНДЫ() 316
Определение дня недели: ДЕНЬНЕД() 317
Вычисление разности дат: РАЗНДАТ() 317
Преобразование даты и времени в число: ДАТАЗНАЧ() и ВРЕМЗНАЧ() 319
Вычисление количества дней между двумя датами: ДНЕЙ360() 320
Вычисление будущих дат: ДАТАМЕС() 321
Вычисление доли года между двумя датами: ДОЛЯГОДА() 321
Определение последнего дня любого месяца: КОНМЕСЯЦА() 322
Вычисление количества рабочих дней: ЧИСТРАБДНИ() 323
Вычисление даты, на которую попадает день в будущем: РАБДЕНЬ() 323
Определение номера недели, на которую попадает указанная дата: НОМНЕДЕЛИ() 324
Глава 12. Поисковые, справочные и информационные функции 325
Базовые поисковые функции 326
Вертикальный поиск: ВПР() 327
Горизонтальный поиск: ГПР() 329
Расширенные поисковые функции 330
Поиск позиции ячейки в диапазоне: ПОИСКПОЗ() 330
Извлечение значения из ячейки: ИНДЕКС() 331
Осуществление "левого поиска" 332
Осуществление двойного поиска 333
Мастер подстановок 334
Перемещение ссылок на ячейки: СМЕЩ() 337
Другие ссылочные и поисковые функции 338
Работа со ссылками на ячейки, сохраненными как текст: ДВССЫЛ() и АДРЕС() 338
Превращение строк в столбцы и наоборот: ТРАНСП() 341
Информационные функции 342
Группа функций "Есть": проверка значения в ячейке 342
Получения типа данных значения или типа ошибки: ТИП() и ТИП.ОШИБКИ() 344
Сбор информации о вашем компьютере и ячейках вашей таблицы: ИНФОРМ() и ЯЧЕЙКА() 345
Обучающий пример: быстрое получение счетов из каталога продукции 346
Глава 13. Создание сложных формул и выявление ошибок 349
Условия в формулах 349
Построение условных формул: ЕСЛИ() 349
Подсчет только указанных ячеек: СЧЁТЕСЛИ() 351
Суммирование только указанных ячеек: СУММЕСЛИ() 353
Подсчет и суммирование со множественными критериями: СЧЁТЕСЛИМН() и СУММЕСЛИМН() 355
Описательные имена для ссылок на ячейки 357
Создание и использование именованного диапазона 358
Расширенное создание именованных диапазонов 359
Присвоение имен формулам и константам 361
Управление именованными диапазонами 362
Автоматическое создание именованных диапазонов 364
Применение имен к существующим формулам 366
Таблицы данных с переменными 368
Создание таблицы данных с одной переменной 368
Создание таблицы данных с двумя переменными 371
Управление пересчетом 372
Устранение ошибок в формулах 373
Пошаговое вычисление 374
Трассировка влияющих и зависимых ячеек 376
Проверка наличия ошибок 380
Часть III. Организация рабочих таблиц 383
Глава 14. Таблицы: улучшенное управление списками 385
Основы таблиц 385
Создание таблицы 386
Форматирование таблицы 389
Редактирование таблицы 392
Выделение частей таблицы 395
Сортировка и фильтрация таблицы 396
Применение простого порядка сортировки 397
Сортировка с несколькими критериями 398
Сортировка по цвету 402
Фильтрация со списком значений 402
Создание расширенных фильтров 404
Работа с повторяющимися строками 406
Выделение повторяющихся строк 406
Автоматическое удаление повторяющихся записей 408
Осуществление вычислений в таблице 409
Динамические вычисления 411
Имена столбцов 412
Имена таблиц 413
Строка итогов 414
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() 415
Функции базы данных 416
Глава 15. Группировка и структурирование данных 420
Основы группировки данных 420
Создание группы 421
Вложенные группы 425
Подсчет итогов 428
Комбинирование данных из нескольких таблиц 429
Способы экономии времени при группировке 432
Автоматическое структурирование 432
Автоматическое создание промежуточных итогов 433
Глава 16. Шаблоны 437
Основы шаблонов 437
Создание новой книги на основе шаблона 438
Загрузка шаблонов. Метод 1: диалоговое окно Создание книги 440
Загрузка шаблонов. Метод 2: Web-сайт Office Online 442
Создание шаблонов 446
Основы пользовательских шаблонов 446
Построение пользовательского шаблона 449
Разделение шаблонов с другими пользователями 451
Часть IV. Диаграммы и графика 455
Глава 17. Создание базовых диаграмм 457
Основы диаграмм 458
Встроенные и автономные диаграммы 459
Создание диаграммы с помощью ленты 460
Вкладки ленты с инструментами диаграмм 461
Основные действия с диаграммами 462
Перемещение и изменение размеров диаграммы 462
Создание автономной диаграммы 465
Редактирование или добавление данных диаграммы 466
Изменение типа диаграммы 468
Печать диаграмм 468
Примеры построения диаграмм 469
Диаграммы с несколькими рядами чисел 469
Управление подписями на оси x 471
Данные, которые используют масштаб даты или времени 473
Диаграмма по несмежным диапазонам данных 476
Изменение порядка вывода рядов данных 478
Изменение способа отображения пустых значений 478
Типы диаграмм 479
Гистограммы 479
Линейчатые диаграммы 481
Графики 482
Круговые диаграммы 483
Диаграммы с областями 484
Точечные диаграммы 485
Биржевые диаграммы 487
Диаграммы поверхности 488
Кольцевые диаграммы 489
Пузырьковые диаграммы 490
Лепестковые диаграммы 491
Глава 18. Форматирование и настройка диаграмм 492
Стили и макеты диаграмм 492
Стили диаграмм 492
Макеты диаграмм 494
Добавление элементов диаграммы 495
Добавление заголовков 496
Добавление легенды 497
Добавление подписей данных к рядам 498
Добавление индивидуальных подписей данных 500
Добавление таблицы данных 503
Выделение элементов диаграммы 503
Форматирование элементов диаграммы 505
Цвет фона 505
Необычные заливки 507
Необычные границы и линии 510
Форматирование рядов данных и точек данных 512
Повторное использование ваших любимых диаграмм с помощью шаблонов 515
Улучшение ваших диаграмм 516
Управление масштабом диаграммы 516
Добавление линии тренда 520
Добавление полос погрешностей к научным данным 522
Форматирование объемных диаграмм 524
Изменение форм столбцов объемной гистограммы 526
Усложненные приемы построения диаграмм 527
Вырезание секторов на круговой диаграмме 527
Группировка секторов на круговой диаграмме 528
Зазоры, ширины и перекрытия на гистограмме 529
Создание смешанных диаграмм 531
Глава 19. Вставка графических изображений 534
Добавление рисунков на рабочий лист 535
Вставка рисунка 535
Позиционирование и изменение размера рисунка 538
Настройка рисунка 541
Сжатие рисунков 543
Обрезка и изменение формы рисунка 545
Границы рисунков, эффекты и стили 547
Библиотека клипов Excel 549
Рисование фигур 552
Рисование фигуры 554
Вставка текста в фигуру 556
Выделение и настройка расположения фигур 558
Соединение фигур 561
Рисунки SmartArt 563
Часть V. Расширенный анализ данных 567
Глава 20. Сценарии и подбор параметров 569
Использование сценариев 569
Создание нового сценария 570
Управление сценариями 573
Создание итогового отчета 574
Использование подбора параметра 576
Подбор параметра со сложными уравнениями 579
Поиск решения 581
Основы работы поиска решения 582
Определение проблемы для поиска решения 583
Расширенное применение поиска решения 587
Сохранение моделей поиска решения 588
Настройка поиска решения 590
Глава 21. Сводные таблицы 592
Возвращаясь к таблицам промежуточных итогов 592
Жизнь без сводных таблиц 593
Жизнь со сводными таблицами 595
Построение сводных таблиц 596
Подготовка сводной таблицы 598
Области сводной таблицы 600
Создание сводной таблицы 601
Форматирование сводной таблицы 603
Настройка сводной таблицы 605
Многоуровневые сводные таблицы 606
Сокрытие и отображение подробностей 608
Тонкая настройка вычислений сводных таблиц 610
Изменение типа вычисления 611
Добавление вычисляемого поля 613
Фильтрация сводной таблицы 614
Фильтрация отчета 615
Фильтрация групп 617
Сводные диаграммы 619
Создание сводной диаграммы 620
Манипулирование сводной диаграммой 621
Часть VI. Обмен данными 623
Глава 22. Защита ваших книг 625
Введение в меры безопасности Excel 625
Проверка данных 626
Параметры 627
Сообщение для ввода 628
Сообщение об ошибке 629
Проверка данных с формулами и ссылками на ячейки 632
Проверка данных со списками 634
Защищаемые ячейки и ячейки со скрытыми формулами 635
Защита листа 636
Защита всей книги 640
Защита диапазонов ячеек (с дополнительными паролями) 641
Выдача разрешений определенным пользователям Windows на редактирование диапазона 643
Глава 23. Совместная разработка электронных таблиц 646
Подготовка вашей книги 646
Свойства документа 649
Распространение документа 651
Добавление примечаний 653
Вставка примечания 654
Отображение и сокрытие примечаний 654
Тонкая настройка примечаний 655
Рецензирование примечаний 657
Печать примечаний 657
Отслеживание исправлений 658
Включение отслеживания исправлений 659
Знакомство с журналом изменений 661
Выделение исправлений 662
Просмотр журнала изменений 664
Принятие и отклонение исправлений 665
Объединение нескольких редакций в одну книгу 667
Совместное использование вашей книги 669
Работа нескольких пользователей с неразделенной книгой 670
Включение совместного использования книги 671
Совместное использование книги в действии 673
Глава 24. Работа с базами данных и XML-файлами 675
Excel и базы данных 675
Подключение к базе данных Access 676
Обновление данных 679
Безопасность источника данных 681
Подключение к базе данных SQL Server 683
Повторное использование вашего подключения к базе данных 687
Введение в XML 689
Что же такое, на самом деле, XML? 689
Три правила XML 690
XML-файлы и схемы 693
Excel и XML 694
Сопоставление для простого документа 695
Импорт и экспорт XML 700
Сопоставление списков 701
Использование преимуществ сопоставления XML 703
Глава 25. Обмен данными с другими программами 706
Обмен информацией в Windows 706
Внедрение и связывание объектов 707
Экспортирование диаграмм из Excel 709
Редактирование связанного объекта 712
Редактирование внедренного объекта 713
Импортирование объектов в Excel 715
Перенос данных 716
Экспортирование таблиц данных 717
Импортирование таблиц данных 718
Импортирование текстовых файлов 718
Глава 26. Работа с Web 724
Публикация листов в Web 724
Сохранение HTML-файла 725
Выполнение Web-запросов 730
Ограничения Web-запросов 730
Создание Web-запроса 731
Панель Справочные материалы — альтернатива Web-запросам 734
Использование гиперссылок 735
Добавление гиперссылки на Web-страницу или документ 736
Добавление гиперссылки на лист 738
Самостоятельное создание гиперссылки: ГИПЕРССЫЛКА() 739
Часть VII. Программирование в Excel 741
Глава 27. Автоматизация задач с помощью макросов 743
Введение в макросы 743
Книги с макросами и без макросов 744
Макрорекордер 745
Относительная и абсолютная записи 746
Где "живут" макросы 747
Запись макроса 748
Выполнение макроса 751
Безопасность макросов 753
Центр управления безопасностью 755
Настройка надежного расположения 757
Практическое создание макросов 759
Вставка заголовка 760
Чересстрочное форматирование 761
Комбинированная задача 762
Вставка макроса на Панель быстрого доступа 763
Присоединение макроса к кнопке внутри листа 765
Глава 28. Программирование электронных таблиц с помощью VBA 767
Редактор Visual Basic 767
Окно проекта 768
Модули и макросы 770
Поиск и перемещение макросов 771
Отладка макроса 772
Введение в макрокод 774
Анатомия макроса 775
Введение в объекты 776
Использование свойств и методов 776
Поиск объектов 778
Знакомство с языком VBA 779
Ввод текста в текущую ячейку 780
Переход к другим ячейкам 781
Редактирование определенных ячеек 781
Форматирование ячеек 782
Использование переменных 783
Принятие решений 785
Повторение действий в цикле 786
Создание пользовательских функций 789
Приложение. Настройка Панели быстрого доступа 793
Панель быстрого доступа 793
Добавление кнопок 795
Настройка отдельных книг 797
Предметный указатель 799