Excel 2019. Библия пользователя

Об авторах стр. 29

О технических рецензентах стр. 29

Благодарности авторов стр. 30

Введение стр. 31

Для кого эта книга стр. 31

Версии программы стр. 32

Соглашения, принятые в книге стр. 33

Команды Excel стр. 33

Имена файлов и вводимые данные стр. 33

Соглашения относительно действий, выполняемых с помощью мыши стр. 33

Структура книги стр. 35

Как пользоваться книгой стр. 35

Веб-сайт книги стр. 36

Ждем ваших отзывов! стр. 36

Часть I. Вводный курс стр. 37

Глава 1. Знакомство с Excel стр. 39

Обзор возможностей Excel стр. 39

Что нового в Excel 2019 стр. 40

Рабочая книга и рабочий лист стр. 41

Перемещение по рабочему листу стр. 44

Перемещение с использованием клавиатуры стр. 45

Перемещение с использованием мыши стр. 46

Интерфейс пользователя: лента стр. 47

Вкладки ленты стр. 47

Контекстные ленточные вкладки стр. 49

Типы ленточных команд стр. 50

Доступ к командам на ленте с помощью клавиатуры стр. 52

Использование контекстных меню стр. 54

Настройка панели быстрого доступа стр. 55

Работа с диалоговыми окнами стр. 57

Перемещение по элементам диалогового окна стр. 58

Диалоговые окна с вкладками стр. 58

Использование панели задач стр. 59

Создание первого рабочего листа стр. 60

Подготовка к работе стр. 60

Ввод названий месяцев стр. 61

Ввод данных о продажах стр. 61

Форматирование чисел стр. 62

Форматирование таблицы стр. 63

Суммирование значений стр. 63

Создание диаграммы стр. 64

Печать рабочего листа стр. 65

Сохранение рабочей книги стр. 65

Глава 2. Ввод данных на рабочий лист и их редактирование стр. 67

Типы данных Excel стр. 67

Числовые значения стр. 68

Текст стр. 69

Формулы стр. 69

Ввод чисел и текста стр. 70

Ввод числовых значений стр. 70

Ввод текстовых значений стр. 70

Режим ввода данных стр. 71

Ввод значений дат и времени стр. 72

Работа с датами стр. 72

Ввод значений времени стр. 72

Изменение содержимого ячейки стр. 73

Удаление содержимого ячейки стр. 73

Замена содержимого ячейки стр. 74

Редактирование содержимого ячейки стр. 74

Некоторые удобные способы ввода данных стр. 76

Форматирование числовых значений стр. 83

Автоматическое форматирование чисел стр. 85

Форматирование чисел с помощью ленточных команд стр. 85

Форматирование чисел с использованием комбинаций клавиш стр. 86

Форматирование чисел с помощью диалогового окна Формат ячеек стр. 87

Числовые форматы, определяемые пользователем стр. 90

Глава 3. Основные операции с рабочим листом стр. 91

Операции с рабочими листами стр. 91

Работа с окнами рабочих книг Excel стр. 92

Активизация рабочих листов стр. 94

Добавление нового рабочего листа стр. 95

Удаление листа из рабочей книги стр. 96

Изменение названия рабочего листа стр. 96

Изменение цвета ярлыков листов стр. 97

Переупорядочение рабочих листов стр. 97

Сокрытие и отображение рабочего листа стр. 99

Управление внешним видом рабочего листа стр. 100

Масштабирование рабочих листов для улучшения условий просмотра стр. 100

Просмотр листов в нескольких окнах стр. 101

Сравнение рабочих листов стр. 102

Разделение окна рабочего листа на две панели стр. 103

Закрепление областей в окне стр. 103

Отслеживание значений с помощью окна контрольного значения стр. 105

Манипуляции строками и столбцами стр. 106

Вставка строк и столбцов стр. 106

Удаление строк и столбцов стр. 108

Изменение ширины столбцов и высоты строк стр. 108

Сокрытие строк и столбцов стр. 110

Глава 4. Работа с диапазонами и таблицами Excel стр. 113

Ячейки и диапазоны Excel стр. 113

Выделение диапазонов стр. 114

Выделение строк и столбцов целиком стр. 115

Выбор несмежных диапазонов стр. 116

Выделение диапазонов на разных листах стр. 117

Выделение ячеек определенного типа стр. 119

Выделение ячеек с использованием функции поиска стр. 122

Копирование и перемещение диапазонов стр. 124

Копирование с помощью команд на ленте стр. 125

Копирование с помощью команд контекстного меню стр. 126

Копирование с помощью клавиатуры стр. 126

Копирование и перемещение с помощью операции перетаскивания стр. 127

Копирование в соседние ячейки стр. 128

Копирование диапазона ячеек в другие листы стр. 129

Использование буфера обмена Office для вставки стр. 130

Специальная вставка стр. 131

Использование диалогового окна Специальная вставка стр. 133

Именованные ячейки и диапазоны стр. 135

Создание именованных ячеек и диапазонов в рабочих книгах стр. 136

Управление именами стр. 139

Добавление примечаний к ячейкам стр. 141

Форматирование примечаний стр. 142

Изменение контура окна примечания стр. 143

Просмотр примечаний стр. 143

Сокрытие и отображение примечаний стр. 144

Редактирование примечаний стр. 144

Удаление примечаний стр. 144

Работа с таблицами Excel стр. 145

Что такое табличная структура стр. 145

Создание таблиц стр. 147

Добавление данных в таблицу стр. 148

Сортировка и фильтрация таблиц стр. 149

Изменение внешнего вида таблицы стр. 155

Глава 5. Форматирование рабочих листов стр. 159

Средства форматирования Excel стр. 159

Средства форматирования на вкладке ленты Главная стр. 161

Инструменты форматирования на мини-панелях стр. 161

Диалоговое окно Формат ячеек стр. 161

Форматирование рабочих листов стр. 162

Форматирование рабочих листов с использованием различных шрифтов стр. 162

Выравнивание содержимого ячеек стр. 165

Цвета и заливки стр. 170

Границы и линии стр. 171

Условное форматирование стр. 173

Задание условного форматирования стр. 174

Условные форматы с использованием графических элементов стр. 174

Создание правил условного форматирования на основе формул стр. 179

Примеры условного форматирования, основанного на формулах стр. 182

Работа с условными форматами стр. 184

Использование именованных стилей стр. 186

Применение стилей стр. 187

Изменение существующего стиля стр. 188

Создание новых стилей стр. 189

Объединение стилей из разных рабочих книг стр. 190

Стили и шаблоны стр. 190

Темы документов стр. 190

Применение тем стр. 192

Настройка тем стр. 193

Глава 6. Работа с файлами и шаблонами Excel стр. 195

Создание новой рабочей книги стр. 195

Открытие существующей рабочей книги стр. 197

Отбор по типу файла стр. 199

Выбор способа отображения файлов стр. 200

Сохранение рабочих книг стр. 201

Средство Автовосстановление стр. 202

Восстановление версий текущей рабочей книги стр. 203

Восстановление несохраненных результатов работы стр. 204

Настройка режима автосохранения стр. 204

Защита рабочих книг паролем стр. 205

Организация файлов стр. 205

Другие задаваемые свойства рабочих книг стр. 206

Раздел Защита книги стр. 206

Раздел Поиск проблем стр. 207

Параметры управления книгой стр. 207

Параметры просмотра в браузере стр. 207

Раздел Режим ограниченной функциональности стр. 208

Закрытие рабочих книг стр. 208

Как избежать потери данных стр. 208

Работа с шаблонами стр. 209

Краткий обзор шаблонов стр. 209

Использование стандартных шаблонов стр. 213

Использование пользовательских шаблонов рабочих книг стр. 215

Глава 7. Печать результатов работы стр. 217

Быстрая печать документов стр. 217

Режимы просмотра рабочих книг стр. 219

Обычный режим просмотра стр. 219

Режим разметки страницы стр. 221

Страничный режим просмотра стр. 222

Задание параметров печатной страницы стр. 223

Выбор принтера стр. 225

Задание объекта для печати стр. 225

Изменение ориентации печатной страницы стр. 226

Задание формата бумаги стр. 226

Печать нескольких копий отчетов стр. 227

Задание размеров полей стр. 227

Управление разделителями страниц стр. 228

Печать заголовков строк и столбцов стр. 229

Масштабирование печатной страницы стр. 231

Печать сетки рабочего листа стр. 231

Печать заголовков строк и столбцов рабочего листа стр. 231

Использование фонового рисунка стр. 231

Создание колонтитулов стр. 233

Выбор предустановленных колонтитулов стр. 234

Коды элементов колонтитулов стр. 235

Другие параметры колонтитулов стр. 236

Другие темы, связанные с печатью стр. 236

Копирование параметров страниц между рабочими листами стр. 236

Сокрытие ячеек перед печатью стр. 237

Сокрытие объектов перед печатью стр. 237

Создание представлений стр. 239

Создание PDF-файлов стр. 240

Глава 8. Настройка пользовательского интерфейса стр. 241

Настройка панели быстрого доступа стр. 241

Панель быстрого доступа стр. 242

Добавление новых команд на панель быстрого доступа стр. 243

Другие действия с панелью быстрого доступа стр. 246

Настройка ленты стр. 247

Зачем настраивать ленту стр. 247

Что можно настраивать на ленте стр. 247

Как настроить ленту стр. 248

Сброс ленты стр. 250

Часть II. Формулы и функции стр. 251

Глава 9. Основы формул и функций Excel стр. 253

Знакомство с формулами стр. 253

Операторы, используемые в формулах стр. 254

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

Использование в формулах функций стр. 258

Ввод формул в ячейки рабочего листа стр. 261

Ввод формул вручную стр. 262

Ввод формул путем указания стр. 262

Вставка в формулы имен диапазонов стр. 263

Вставка функций в формулы стр. 263

Советы по вводу функций стр. 266

Редактирование формул стр. 266

Использование ссылок в формулах стр. 267

Абсолютные, относительные и смешанные ссылки стр. 268

Изменение типа ссылок стр. 270

Ссылки на ячейки за пределами текущего рабочего листа стр. 270

Использование формул в таблицах стр. 272

Итоговые вычисления в таблицах стр. 272

Использование формул в столбцах стр. 273

Ссылки на табличные данные стр. 275

Исправление ошибок в формулах стр. 276

Циклические ссылки стр. 277

Режимы вычислений стр. 278

Использование имен в формулах стр. 279

Присвоение имен константам стр. 279

Присвоение имен формулам стр. 280

Пересечение диапазонов стр. 281

Замена существующих ссылок именами стр. 282

Советы по работе с формулами стр. 283

Не используйте в формулах константы стр. 283

Строка формул в качестве калькулятора стр. 283

Получение точной копии формулы стр. 283

Преобразование формул в значения стр. 284

Глава 10. Использование формул в математических расчетах стр. 285

Расчет процентов стр. 285

Вычисление процента выполнения задания стр. 286

Вычисление процента отклонения стр. 287

Вычисление процентного отклонения в случае отрицательных значений стр. 288

Вычисление процентного распределения стр. 289

Вычисление нарастающих итогов стр. 290

Увеличение или уменьшение значений на заданный процент стр. 291

Обработка ситуации деления на нуль стр. 292

Округление чисел стр. 293

Округление чисел с помощью формул стр. 293

Округление до целых копеек стр. 294

Округление до заданного количества значащих цифр стр. 295

Подсчет значений в диапазоне стр. 297

Преобразование единиц измерения стр. 298

Глава 11. Формулы для обработки текста стр. 301

Работа с текстовыми значениями стр. 301

Функции работы с текстом стр. 302

Объединение текстовых строк стр. 303

Изменение регистра символов текста стр. 304

Удаление лишних пробелов из строки текста стр. 305

Извлечение фрагментов из текстовой строки стр. 306

Поиск в текстовой строке определенного символа стр. 307

Поиск второго вхождения символа в строке стр. 309

Замена текста стр. 310

Подсчет количества определенных символов в тексте стр. 311

Использование в формуле символа конца строки стр. 312

Удаление из текстовых строк непечатаемых символов стр. 313

Дополнение числовых значений нулями стр. 314

Отображение отформатированных числовых значений в виде текста стр. 314

Отображение числовых значений в текстовом формате Денежный стр. 316

Глава 12. Формулы для работы с датами и временем стр. 317

Как Excel обрабатывает значения дат и времени стр. 317

Даты как порядковые номера стр. 318

Ввод дат стр. 319

Работа с порядковыми номерами, соответствующими времени стр. 320

Ввод значений времени стр. 321

Форматирование значений дат и времени стр. 322

Проблемы, возникающие при работе с датами в Excel стр. 322

Функции обработки дат стр. 324

Отображение текущей даты стр. 324

Вычисление возраста человека стр. 325

Вычисление количества дней между двумя датами стр. 326

Вычисление количества рабочих дней между двумя датами стр. 327

Построение списка бизнес-дней с исключением выходных и праздничных дней стр. 328

Извлечение отдельных частей даты стр. 330

Вычисление количества лет и месяцев между двумя датами стр. 332

Преобразование дат в стандартном формате в юлианский формат стр. 332

Вычисление прошедшей и оставшейся частей года в процентах стр. 334

Определение последнего дня заданного месяца стр. 335

Определение календарного квартала по дате стр. 336

Вычисление финансового квартала по дате стр. 337

Определение финансового месяца по дате стр. 338

Определение даты N-го вхождения определенного дня недели в указанном месяце стр. 339

Определение даты последнего вхождения определенного дня недели в указанном месяце стр. 341

Извлечение отдельных частей из значений времени стр. 342

Вычисление прошедшего времени стр. 342

Округление значений времени стр. 343

Преобразование десятичных значений часов, минут и секунд в значение времени стр. 344

Прибавление к значению времени часов, минут и секунд стр. 345

Глава 13. Формулы с проверкой условий стр. 347

Проверка условий с помощью формул стр. 347

Проверка выполнения простого условия стр. 348

Проверка нескольких условий стр. 349

Использование средств проверки данных для построения условия стр. 350

Проверка совпадения результатов двух условий стр. 352

Проверка удовлетворения первого или второго условия стр. 355

Вычисления с использованием формул с проверкой условий стр. 356

Суммирование всех значений, которые отвечают определенному условию стр. 356

Суммирование значений, которые соответствуют двум и более условиям стр. 360

Суммирование, если значения попадают в заданный диапазон дат стр. 361

Подсчет значений, которые отвечают заданному условию стр. 363

Подсчет количества значений, отвечающих двум и более условиям стр. 364

Вычисление среднего для всех значений, отвечающих определенному условию стр. 366

Вычисление среднего для всех значений, отвечающих двум и более условиям стр. 367

Глава 14. Использование формул для поиска и извлечения данных стр. 369

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

Функции, используемые для поиска и выборки значений стр. 370

Поиск точного совпадения при просмотре левого столбца таблицы стр. 371

Поиск точного совпадения при просмотре любого заданного столбца таблицы стр. 373

Поиск значений по горизонтали стр. 375

Сокрытие ошибок, возвращаемых функциями поиска стр. 376

Поиск ближайшего значения в списке граничных значений шкалы стр. 378

Нахождение ближайшего соответствия с помощью функций ИНДЕКС и ПОИСКПОЗ стр. 380

Поиск значений в нескольких таблицах стр. 382

Поиск значений на основании двух критериев стр. 384

Поиск значения на основе нескольких критериев стр. 386

Поиск последнего значения в столбце стр. 388

Глава 15. Формулы и функции для финансового анализа стр. 391

Выполнение типовых бизнес-расчетов стр. 391

Расчет валовой прибыли и валовой прибыли в процентах стр. 392

Расчет дохода до вычета процентов и налогов и расчет прибыли до вычета процентов, налогов, износа и амортизации стр. 393

Расчет себестоимости реализованных товаров стр. 395

Расчет рентабельности активов стр. 395

Вычисление точки безубыточности стр. 397

Расчет оттока клиентов стр. 399

Расчет пожизненной ценности клиента стр. 401

Расчет текучести кадров стр. 401

Использование финансовых функций Excel стр. 403

Преобразование процентных ставок стр. 403

Калькулятор выплаты кредита стр. 405

Создание графика погашения ссуды с переменной ставкой стр. 409

Расчет амортизации основных средств стр. 411

Расчет текущей стоимости стр. 415

Вычисление чистой приведенной стоимости стр. 418

Расчет внутренней ставки доходности стр. 421

Финансовое прогнозирование стр. 423

Глава 16. Формулы для статистического анализа стр. 427

Работа со взвешенными средними стр. 427

Сглаживание данных с помощью скользящих средних стр. 429

Применение экспоненциального сглаживания к изменчивым данным стр. 432

Использование функций для создания описательной статистики стр. 434

Выборка максимального или минимального значения стр. 434

Выборка N-го максимального или минимального значения стр. 436

Вычисление среднего, медианы и моды стр. 438

Группирование данных в процентили стр. 440

Выявление статистических выбросов с помощью межквартильного диапазона стр. 443

Создание частотных распределений стр. 446

Альтернативный вариант расчетов: без использования функции ЧАСТОТА стр. 447

Глава 17. Использование формул в правилах условного форматирования стр. 449

Выделение ячеек, отвечающих определенному условию стр. 450

Выделение ячеек на основании значения другой ячейки стр. 452

Выделение значений, которые присутствуют в первом списке, но отсутствуют во втором стр. 453

Выделение значений, которые присутствуют одновременно в первом и втором списках стр. 455

Выделение ячеек на основании дат стр. 457

Выделение дней между двумя датами стр. 458

Выделение дат на основе текущей даты стр. 460

Глава 18. Понятие о формулах массивов стр. 463

Введение в формулы массивов стр. 463

Формула массива для диапазона ячеек стр. 464

Формула массива для отдельной ячейки стр. 466

Создание массива констант стр. 467

Размерность массивов стр. 468

Одномерные горизонтальные массивы стр. 469

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

Двухмерные массивы стр. 470

Именованные массивы констант стр. 471

Работа с формулами массивов стр. 472

Ввод формул массивов стр. 472

Выделение диапазона для формулы массива стр. 473

Редактирование формул массивов стр. 473

Расширение и сокращение диапазона, содержащего формулу массива стр. 474

Формулы массивов для диапазонов ячеек стр. 475

Создание массивов на основе значений ячеек диапазона стр. 475

Создание массива констант на основе значений диапазона ячеек стр. 476

Выполнение операций над массивами стр. 476

Применение функций к массивам стр. 477

Транспонирование массивов стр. 477

Генерирование последовательности целых чисел стр. 478

Формулы массивов для отдельных ячеек стр. 480

Подсчет количества символов в диапазоне стр. 480

Суммирование трех наименьших значений диапазона стр. 481

Подсчет количества ячеек в диапазоне, содержащих текст стр. 482

Исключение промежуточных формул стр. 483

Использование массивов вместо ссылок на диапазоны стр. 484

Глава 19. Поиск и исправление ошибок стр. 485

Поиск и исправление ошибок в формулах стр. 485

Несоответствие скобок стр. 486

Ячейки заполнены символами решетки стр. 487

Непустые “пустые” ячейки стр. 488

Лишние символы пробела стр. 488

Формулы, возвращающие значения ошибок стр. 488

Приоритет операторов стр. 493

Что делать, если формулы не вычисляются стр. 493

Точность значений десятичных чисел стр. 494

Ошибки, связанные с “фантомными” внешними ссылками стр. 495

Средства проверки Excel стр. 495

Выделение ячеек определенного типа стр. 495

Просмотр формул стр. 496

Отслеживание связей между ячейками стр. 496

Отслеживание ошибочных значений стр. 498

Циклические ссылки стр. 498

Фоновая проверка ошибок стр. 498

Вычисление формул стр. 500

Средства поиска и замены стр. 500

Поиск информации стр. 501

Замена информации стр. 502

Поиск формата стр. 502

Проверка орфографии стр. 503

Использование автозамены стр. 504

Часть III. Создание диаграмм и другие приемы визуализации стр. 507

Глава 20. Основы построения диаграмм стр. 509

Что такое диаграмма стр. 509

Как Excel строит диаграммы стр. 510

Внедренные диаграммы стр. 512

Диаграмма, расположенная на отдельном листе стр. 512

Элементы диаграмм стр. 514

Ограничения в диаграммах стр. 516

Создание диаграмм стр. 516

Пример создания диаграммы стр. 517

Изменение ориентации строк и столбцов стр. 518

Изменение типа диаграммы стр. 518

Выбор макета диаграммы стр. 520

Выбор стиля диаграммы стр. 520

Добавление и удаление элементов диаграмм стр. 521

Форматирование элементов диаграммы стр. 522

Манипуляции диаграммами стр. 523

Перемещение и изменение размеров диаграммы стр. 523

Перемещение внедренной диаграммы на собственный лист стр. 524

Копирование диаграммы стр. 525

Удаление диаграммы стр. 525

Добавление элементов в диаграмму стр. 525

Перемещение и удаление элементов диаграммы стр. 525

Форматирование элементов диаграммы стр. 526

Копирование форматирования диаграммы стр. 527

Переименование диаграмм стр. 528

Печать диаграмм стр. 528

Типы диаграмм Excel стр. 529

Определение типа диаграммы стр. 529

Гистограммы стр. 531

Линейчатые диаграммы стр. 533

Графики стр. 534

Круговые диаграммы стр. 535

Точечные диаграммы стр. 537

Диаграммы с областями стр. 538

Лепестковые диаграммы стр. 539

Поверхностные диаграммы стр. 540

Пузырьковые диаграммы стр. 541

Биржевые диаграммы стр. 541

Новые типы диаграмм в Excel стр. 542

Гистограммы стр. 542

Диаграммы Парето стр. 543

Каскадные диаграммы стр. 544

Диаграммы типа “ящик с усами” стр. 545

Диаграммы типа “солнечные лучи” стр. 545

Древовидные диаграммы стр. 547

Диаграммы “воронка” стр. 547

Картограммы стр. 548

Глава 21. Расширенные возможности построения диаграмм стр. 551

Выделение элементов диаграммы стр. 552

Выделение с помощью мыши стр. 552

Выделение с помощью клавиатуры стр. 553

Выделение с помощью списка элементов диаграммы стр. 554

Средства Excel для настройки элементов диаграммы стр. 555

Использование панели задач Формат стр. 555

Использование кнопок настройки диаграммы стр. 555

Использование ленты стр. 557

Использование мини-панелей стр. 557

Настройка области диаграммы стр. 557

Настройка области построения диаграммы стр. 559

Заголовки диаграммы стр. 560

Легенда диаграммы стр. 561

Линии сетки стр. 563

Настройка осей стр. 563

Настройка оси значений стр. 564

Настройка оси категорий стр. 568

Работа с рядами данных стр. 570

Удаление или сокрытие ряда данных стр. 571

Добавление нового ряда данных на диаграмму стр. 571

Изменение ряда данных стр. 572

Добавление подписей данных стр. 575

Обработка пропущенных данных стр. 577

Добавление планок погрешностей стр. 578

Добавление линии тренда стр. 579

Построение комбинированных диаграмм стр. 581

Отображение таблицы данных стр. 582

Создание шаблонов диаграмм стр. 583

Глава 22. Графика спарклайнов стр. 585

Типы спарклайнов стр. 586

Создание спарклайнов стр. 587

Настройка спарклайнов стр. 589

Изменение размеров ячеек со спарклайнами стр. 589

Обработка скрытых или отсутствующих данных стр. 590

Смена типа спарклайна стр. 590

Изменение цвета и ширины спарклайна стр. 591

Выделение определенных аспектов данных стр. 591

Настройка масштабирования осей спарклайнов стр. 592

Имитация опорной линии стр. 593

Задание оси дат стр. 594

Автоматическое обновление спарклайнов стр. 596

Отображение спарклайнов для динамического диапазона стр. 596

Глава 23. Работа с настраиваемыми числовыми форматами, изображениями и рисунками стр. 599

Визуализация с помощью числовых форматов стр. 600

Базовые приемы форматирования чисел стр. 600

Создание собственных числовых форматов стр. 602

Использование символов для улучшения отчетов стр. 610

Использование фигур и значков как средств визуализации стр. 613

Вставка фигур стр. 614

Вставка значков в формате SVG стр. 615

Форматирование фигур и значков стр. 616

Использование фигур для повышения качества отчетов стр. 618

Распределение фигур по слоям для экономии места стр. 619

Создание динамических надписей стр. 621

Создание связанных рисунков стр. 622

Использование графики SmartArt и WordArt стр. 624

Вставка графических элементов SmartArt стр. 624

Использование WordArt стр. 625

Работа с другими типами графических объектов стр. 626

Несколько слов о графических файлах стр. 626

Вставка копий экрана стр. 627

Добавление подложки рабочего листа стр. 627

Работа с редактором уравнений стр. 628

Глава 24. Практика создания графических итоговых панелей стр. 629

Подготовительный этап проекта панели итоговой отчетности стр. 630

Определение аудитории и назначения итоговой панели стр. 630

Определение основных показателей, представляемых на панели стр. 631

Каталогизация необходимых источников данных стр. 631

Определение размерности и фильтров итоговой панели стр. 632

Определение необходимых функций детализации стр. 632

Согласование графика обновления данных стр. 633

Разработка модели данных графической итоговой панели стр. 633

Разделение данных, анализа и презентации стр. 634

Начините с выбора правильно структуризированных данных стр. 635

Не превращайте модель данных в базу данных стр. 636

Документирование и организация модели данных стр. 637

Выбор дизайна графической итоговой панели стр. 638

Часть IV. Управление данными и их анализ стр. 643

Глава 25. Импорт и предварительная подготовка данных стр. 645

Импорт данных в Excel стр. 646

Импорт данных из файла стр. 646

Импорт файла или его открытие стр. 648

Импорт текстового файла стр. 649

Копирование и вставка данных стр. 653

Методы предварительной подготовки данных стр. 653

Удаление повторяющихся строк стр. 653

Обнаружение повторяющихся строк стр. 655

Разбиение текста стр. 656

Изменение регистра символов стр. 661

Удаление лишних пробелов стр. 662

Удаление непечатаемых символов стр. 663

Преобразование значений стр. 663

Классификация значений стр. 664

Объединение столбцов стр. 665

Переупорядочение столбцов стр. 666

Перестановка строк в произвольном порядке стр. 667

Выборка имени файла из URL стр. 667

Поиск текста в списке стр. 667

Перевод вертикально расположенных данных в горизонтально расположенные стр. 668

Заполнение пропусков в импортированном отчете стр. 671

Проверка правописания стр. 672

Замена или удаление текста в ячейках стр. 673

Добавление текста в ячейки стр. 674

Решение задачи с завершающим символом “минус” стр. 674

На что следует обратить внимание при подготовке данных стр. 675

Экспортирование данных стр. 675

Экспортирование данных в текстовые файлы стр. 675

Экспорт в файлы других форматов стр. 676

Глава 26. Проверка данных стр. 677

Средства проверки данных стр. 677

Определение критерия проверки стр. 678

Типы проверяемых данных стр. 679

Создание раскрывающегося списка стр. 681

Проверка данных с использованием формул стр. 683

Ссылки на ячейки стр. 683

Примеры формул для проверки данных стр. 685

Ввод только текста стр. 685

Ввод значений, больших, чем в предыдущей ячейке стр. 685

Ввод только уникальных значений стр. 686

Ввод текста, начинающегося с определенного символа стр. 686

Ввод дат по дню недели стр. 687

Ввод значений, не превышающих суммы стр. 687

Создание зависимых списков стр. 688

Использование методов проверки данных без ограничения ввода стр. 688

Вывод сообщения с напоминанием стр. 689

Предложение возможности выбора стр. 689

Глава 27. Структурирование рабочих листов стр. 691

Что такое структура рабочего листа стр. 691

Создание структуры стр. 695

Подготовка данных стр. 695

Автоматическое создание структуры стр. 696

Создание структуры вручную стр. 697

Работа со структурами стр. 698

Отображение уровней стр. 699

Добавление данных к структуре стр. 699

Удаление структуры стр. 699

Настройка символов структуры стр. 700

Сокрытие символов структуры стр. 700

Глава 28. Связывание и консолидация данных стр. 701

Связывание рабочих книг стр. 701

Создание формул с внешними ссылками стр. 702

Синтаксис формул с внешними ссылками стр. 703

Создание формулы со ссылками путем указания стр. 703

Вставка связей стр. 704

Работа с формулами, содержащими внешние ссылки стр. 704

Создание связей с несохраненными рабочими книгами стр. 705

Открытие рабочей книги, содержащей формулы с внешними ссылками стр. 705

Запрос на обновление связей стр. 707

Обновление связей стр. 707

Изменение ссылки стр. 708

Разрыв связей стр. 708

Возможные проблемы при работе с формулами, содержащими внешние ссылки стр. 708

Переименование или перемещение исходной рабочей книги стр. 709

Использование команды Сохранить как стр. 709

Изменения в исходной рабочей книге стр. 709

Использование промежуточных связей стр. 710

Консолидация рабочих листов стр. 711

Консолидация с помощью формул стр. 712

Консолидация с помощью диалогового окна Специальная вставка стр. 713

Консолидация рабочих листов с помощью диалогового окна Консолидация стр. 714

Пример консолидации данных стр. 716

Обновление консолидированных данных стр. 719

Дополнительные сведения о консолидации стр. 719

Глава 29. Понятие о сводных таблицах стр. 721

Что такое сводная таблица стр. 721

Пример сводной таблицы стр. 722

Отбор данных для сводных таблиц стр. 725

Автоматическое создание сводных таблиц стр. 727

Создание сводных таблиц вручную стр. 728

Определение местонахождения данных стр. 729

Определение расположения сводной таблицы стр. 730

Создание макета сводной таблицы стр. 731

Форматирование сводной таблицы стр. 734

Модификация сводной таблицы стр. 736

Другие примеры сводных таблиц стр. 738

Какова ежедневная сумма вкладов в каждом из отделений? стр. 738

На какой день недели приходятся наибольшие вклады по счетам? стр. 739

Сколько счетов было открыто в каждом из отделений и по каждому из типов счетов? стр. 740

Как распределены суммы по счетам? стр. 741

Какие типы счетов чаще всего открывают кассиры? стр. 742

В каком отделении уполномоченные открыли наибольшее количество депозитов для новых клиентов? стр. 742

Что дальше стр. 743

Глава 30. Анализ данных с помощью сводных таблиц стр. 745

Работа с нечисловыми данными стр. 745

Группирование элементов сводной таблицы стр. 747

Пример группирования данных вручную стр. 748

Автоматическое группирование стр. 749

Использование сводных таблиц для создания распределения частот стр. 754

Создание вычисляемого поля и вычисляемого элемента стр. 756

Создание вычисляемого поля стр. 758

Вставка вычисляемого элемента стр. 760

Фильтрация сводных таблиц с помощью срезов стр. 763

Фильтрация сводных таблиц с помощью временной шкалы стр. 765

Ссылки на ячейки сводной таблицы стр. 766

Создание сводных диаграмм стр. 768

Пример построения сводной диаграммы стр. 768

Еще о сводных диаграммах стр. 771

Применение моделей данных стр. 772

Глава 31. Анализ данных с помощью сценариев “что если” стр. 779

Пример анализа “что если” стр. 780

Типы анализа “что если” стр. 781

Проведение анализа “что если” вручную стр. 781

Создание таблиц данных стр. 782

Диспетчер сценариев стр. 789

Глава 32. Анализ данных: подбор параметров и поиск решения стр. 797

Анализ “что если” наоборот стр. 797

Подбор параметра для одной ячейки стр. 798

Пример подбора параметра стр. 798

Подробнее о процедуре подбора параметра стр. 800

Надстройка Поиск решения стр. 801

Характерные задачи поиска решения стр. 801

Простой пример поиска решения стр. 802

Параметры средства Поиск решения стр. 807

Примеры использования процедур поиска решений стр. 809

Решение системы линейных алгебраических уравнений стр. 809

Минимизация транспортных расходов стр. 811

Распределение ресурсов стр. 814

Оптимизация инвестиционного портфеля стр. 816

Глава 33. Надстройка Пакет анализа стр. 819

Обзор возможностей надстройки Пакет анализа стр. 819

Установка надстройки Пакет анализа стр. 820

Использование средств надстройки Пакет анализа стр. 821

Средства надстройки Пакет анализа стр. 822

Дисперсионный анализ стр. 822

Корреляционный анализ стр. 822

Ковариационный анализ стр. 823

Описательная статистика стр. 824

Экспоненциальное сглаживание стр. 824

F-тест (двухвыборочный тест для сравнения дисперсий) стр. 825

Анализ Фурье стр. 825

Гистограмма стр. 825

Скользящее среднее стр. 827

Генерация случайных чисел стр. 828

Ранг и процентиль стр. 829

Регрессия стр. 829

Выборка стр. 830

t-тест стр. 830

Двухвыборочный z-тест для средних стр. 831

Глава 34. Защита вашей работы стр. 833

Типы защиты стр. 833

Защита рабочего листа стр. 834

Заблокированные и незаблокированные ячейки стр. 834

Параметры защиты рабочего листа стр. 836

Назначение разрешений пользователям стр. 837

Защита рабочей книги стр. 838

Защита рабочей книги паролем, необходимым для ее открытия стр. 838

Защита структуры рабочей книги стр. 839

Защита проектов VBA стр. 840

Дополнительные темы стр. 841

Сохранение рабочих книг в формате PDF стр. 841

Финализация рабочей книги стр. 842

Инспектирование рабочей книги стр. 842

Использование цифровой подписи стр. 843

Часть V. Использование инструментов Power Pivot и Power Query стр. 845

Глава 35. Надстройка Power Pivot стр. 847

Внутренняя модель данных Power Pivot стр. 848

Отображение интерфейса надстройки Power Pivot на ленте приложения стр. 848

Связывание таблиц Excel с Power Pivot стр. 849

Загрузка данных из сторонних источников стр. 858

Загрузка данных из реляционных баз данных стр. 858

Загрузка данных из плоских файлов стр. 864

Обновление соединений с внешними источниками данных и управление ими стр. 870

Глава 36. Работа непосредственно с внутренней моделью данных стр. 875

Прямой доступ к внутренней модели данных стр. 875

Управление связями во внутренней модели данных стр. 882

Удаление таблицы из внутренней модели данных стр. 883

Глава 37. Использование формул в надстройке Power Pivot стр. 885

Дополнение данных надстройки Power Pivot вычисляемыми столбцами стр. 886

Создание простого вычисляемого столбца стр. 886

Форматирование вычисляемых столбцов стр. 888

Ссылки на вычисляемые столбцы в других вычислениях стр. 889

Сокрытие промежуточных вычисляемых столбцов стр. 890

Использование языка DAX при создании вычисляемых столбцов стр. 891

Функции языка DAX, используемые для создания вычисляемых столбцов стр. 892

Создание вычисляемых столбцов с использованием функций языка DAX стр. 894

Ссылки на поля в других таблицах стр. 897

Вложение функций стр. 899

Использование вычисляемых мер стр. 900

Редактирование и удаление вычисляемых мер стр. 903

Использование аналитических функций Excel для манипуляции данными сводных таблиц стр. 904

Глава 38. Что такое Power Query стр. 907

Основные концепции Power Query стр. 908

Этапы выполнения запроса стр. 916

Просмотр кода в окне Расширенный редактор стр. 918

Обновление данных в запросах Power Query стр. 918

Управление существующими запросами стр. 919

Действия на уровне столбца стр. 921

Действия на уровне таблицы стр. 924

Получение данных из внешних источников стр. 927

Импорт данных из файлов стр. 928

Импорт данных из систем баз данных стр. 930

Получение данных из других систем данных стр. 932

Управление параметрами источников данных стр. 933

Редактирование параметров источников данных стр. 933

Глава 39. Преобразование данных с использованием Power Query стр. 935

Выполнение общих преобразований стр. 936

Удаление дублирующихся записей стр. 936

Заполнение пустых полей стр. 939

Заполнение пустых строк стр. 940

Объединение столбцов стр. 940

Изменение регистра текстовых значений стр. 942

Поиск и замена текстовых значений стр. 942

Обрезка и очистка текстовых значений стр. 944

Извлечение символов слева, справа и из середины значения стр. 946

Извлечение первых и последних символов стр. 947

Извлечение символов из середины текстового значения стр. 948

Разделение столбцов с использованием маркерных символов стр. 949

Развертывание свернутых столбцов стр. 951

Развертывание других свернутых столбцов стр. 952

Свертывание столбцов стр. 953

Создание пользовательских столбцов стр. 955

Конкатенация исходных столбцов в настраиваемый пользовательский столбец стр. 957

Преобразование типов данных стр. 958

Тонкая настройка пользовательских столбцов с помощью функций стр. 959

Добавление в пользовательские столбцы условной логики стр. 962

Группирование и агрегирование данных стр. 963

Глава 40. Организация совместной работы запросов стр. 967

Повторное использование этапов запроса стр. 967

Использование функции добавления данных стр. 972

Создание необходимых базовых запросов стр. 973

Добавление данных стр. 974

Использование функции объединения запросов стр. 977

Типы объединения данных в средстве Power Query стр. 977

Объединение запросов стр. 978

Глава 41. Повышение эффективности работы с Power Query стр. 983

Повышение эффективности работы с Power Query стр. 983

Быстрое получение информации о запросах стр. 984

Организация запросов в группы стр. 984

Быстрое выделение столбцов в запросах стр. 985

Переименование этапов запроса стр. 986

Быстрое создание справочных таблиц стр. 986

Копирование запросов с целью экономии времени стр. 987

Определение действий при загрузке данных, принимаемых по умолчанию стр. 988

Предотвращение автоматического преобразования типов данных стр. 988

Предотвращение проблем с производительностью в Power Query стр. 990

Используйте представления вместо таблиц стр. 990

Возложите часть преобразований информации на сервер базы данных стр. 990

Апгрейд до 64-разрядной версии Excel стр. 991

Для повышения производительности отключите функцию обеспечения конфиденциальности стр. 992

Отключите функцию распознавания связей стр. 993

Часть VI. Программирование в Excel стр. 995

Глава 42. Введение в язык Visual Basic for Applications стр. 997

Введение в макросы VBA стр. 997

Вкладка Разработчик на ленте приложения стр. 999

Безопасность макросов стр. 999

Сохранение рабочих книг, содержащих макросы стр. 1001

Два типа VBA-макросов стр. 1001

Подпрограммы VBA стр. 1002

Функции VBA стр. 1003

Создание VBA-макросов стр. 1005

Запись макросов VBA стр. 1005

Еще несколько слов о записи макросов VBA стр. 1014

Написание кода VBA стр. 1017

Дополнительные сведения стр. 1027

Глава 43. Функции VBA: создание новых функций рабочего листа стр. 1029

Понятие функций VBA стр. 1029

Простой вводный пример стр. 1030

Создание новой функции стр. 1030

Использование функции на рабочем листе стр. 1031

Анализ новой пользовательской функции стр. 1031

Процедуры-функции стр. 1033

Запуск процедур-функций стр. 1034

Вызов функции из другой процедуры стр. 1034

Использование функций в формулах рабочего листа стр. 1035

Аргументы процедур-функций стр. 1036

Пример функции без аргументов стр. 1036

Пример функции с одним аргументом стр. 1036

Еще один пример функции с одним аргументом стр. 1037

Пример функции с двумя аргументами стр. 1039

Пример функции с аргументами-диапазонами стр. 1039

Простая и полезная функция стр. 1040

Отладка пользовательских функций стр. 1041

Вставка собственных функций в формулы стр. 1042

Дополнительные сведения стр. 1044

Глава 44. Создание экранных форм стр. 1045

Зачем создавать новые диалоговые окна стр. 1045

Альтернатива экранным формам стр. 1047

Использование функции InputBox стр. 1047

Использование функции MsgBox стр. 1047

Создание пользовательских диалоговых окон стр. 1051

Работа с экранными формами стр. 1051

Вставка в экранную форму элементов управления стр. 1052

Изменение свойств элементов управления стр. 1053

Обработка событий стр. 1054

Отображение экранных форм стр. 1055

Пример экранной формы стр. 1055

Создание экранной формы стр. 1055

Тестирование экранной формы стр. 1056

Создание процедур обработки событий стр. 1058

Еще один пример диалогового окна стр. 1059

Создание диалогового окна стр. 1059

Создание процедур обработки событий стр. 1061

Отображение диалогового окна стр. 1062

Тестирование диалогового окна стр. 1063

Назначение макроса кнопке на рабочем листе стр. 1064

Назначение макроса кнопке на панели быстрого доступа стр. 1064

Дополнительная информация о создании диалоговых окон стр. 1065

Добавление “горячих клавиш” стр. 1065

Изменение последовательности обхода стр. 1066

Дополнительные сведения стр. 1066

Глава 45. Элементы управления на рабочем листе стр. 1067

Размещение элементов управления на рабочем листе стр. 1067

Использование элементов управления стр. 1070

Вставка элемента управления стр. 1070

Режим конструктора стр. 1071

Настройка свойств элементов управления стр. 1071

Общие свойства элементов управления стр. 1072

Связывание элементов управления с ячейками рабочего листа стр. 1073

Создание макросов для элементов управления стр. 1074

Обзор элементов управления ActiveX стр. 1075

Флажок стр. 1076

Поле со списком стр. 1076

Кнопка стр. 1077

Изображение стр. 1077

Подпись стр. 1078

Список стр. 1078

Переключатель стр. 1079

Полоса прокрутки стр. 1079

Счетчик стр. 1080

Текстовое поле стр. 1081

Выключатель стр. 1082

Глава 46. Обработка событий в Excel стр. 1083

События Excel стр. 1083

Создание процедуры обработки события стр. 1084

События уровня рабочей книги стр. 1086

Использование события Open стр. 1087

Использование события SheetActivate стр. 1088

Использование события NewSheet стр. 1089

Использование события BeforeSave стр. 1089

Использование события BeforeClose стр. 1090

События рабочего листа стр. 1090

Использование события Change стр. 1091

Отслеживание изменений в заданном диапазоне ячеек стр. 1091

Использование события SelectionChange стр. 1092

Использование события BeforeRightClick стр. 1093

События, не связанные с объектами стр. 1094

Использование события OnTime стр. 1094

Использование события OnKey стр. 1095

Глава 47. Примеры VBA-программ стр. 1097

Работа с диапазонами стр. 1097

Копирование диапазона стр. 1098

Копирование диапазона произвольного размера стр. 1099

Выборка до конца строки или до конца столбца стр. 1100

Выборка всей строки или всего столбца стр. 1101

Перемещение диапазона стр. 1101

Циклические операции над диапазоном стр. 1102

Запрос на ввод значения ячейки стр. 1104

Определение типа выделенного объекта стр. 1105

Выявление несмежных диапазонов стр. 1105

Подсчет количества выделенных ячеек стр. 1106

Работа с книгами Excel стр. 1107

Сохранение всех рабочих книг стр. 1107

Сохранение и закрытие всех рабочих книг стр. 1107

Работа с диаграммами стр. 1108

Изменение типа диаграммы стр. 1108

Изменение свойств диаграммы стр. 1109

Форматирование диаграммы стр. 1109

Советы по ускорению работы VBA-кода стр. 1110

Отключение функции обновления экрана стр. 1110

Отключение предупреждающих сообщений стр. 1110

Упрощение ссылок на объекты стр. 1111

Объявление типов переменных стр. 1111

Глава 48. Создание надстроек Excel стр. 1115

Что такое надстройка стр. 1115

Работа с надстройками стр. 1116

Зачем создавать надстройки стр. 1117

Создание надстройки стр. 1118

Пример создания надстройки стр. 1119

Код в модуле Module1 стр. 1120

Описание экранной формы стр. 1121

Тестирование рабочей книги стр. 1121

Добавление описания стр. 1121

Создание пользовательского интерфейса для макроса надстройки стр. 1122

Защита проекта стр. 1123

Создание надстройки стр. 1123

Инсталляция надстройки стр. 1124

Предметный указатель стр. 1125