Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. 5-е издание

Введение ............................................................................................................19
Новое в этом издании..............................................................................21
Что необходимо знать для работы с книгой.............................................21
Как работать с книгой..............................................................................21
Сопроводительный сайт...........................................................................22
Благодарности.........................................................................................22
Будьте на связи.......................................................................................23
От издательства.......................................................................................23
Глава 1 Основы моделирования в электронных таблицах.........................24
Ответы на вопросы..................................................................................24
Задания...................................................................................................32
Глава 2 Имена диапазонов............................................................................33
Как создать именованный диапазон?.......................................................33
Создание имени диапазона в поле Имя...............................................34
Создание имен с помощью инструмента Создать из выделенного.......35
Создание имен диапазонов с помощью инструмента Присвоить имя...36
Диспетчер имен..................................................................................37
Ответы на вопросы..................................................................................38
Замечания..........................................................................................43
Задания...................................................................................................43
Глава 3 Функции поиска................................................................................44
Синтаксис функций поиска......................................................................44
Синтаксис функции ВПР......................................................................44
Синтаксис функции ГПР......................................................................45
Ответы на вопросы..................................................................................45
Задания...................................................................................................49
Глава 4 Функция Индекс................................................................................52
Синтаксис функции Индекс......................................................................52
Ответы на вопросы..................................................................................52
Задания...................................................................................................54
Глава 5 Функция ПОИСКПОЗ......................................................................... 55
Синтаксис функции ПОИСКПОЗ................................................................ 55
Ответы на вопросы.................................................................................. 57
Задания................................................................................................... 61
Глава 6 Текстовые функции.......................................................................... 62
Синтаксис текстовых функций................................................................. 63
Функция ЛЕВСИМВ.............................................................................. 63
Функция ПРАВСИМВ............................................................................ 63
Функция ПСТР.................................................................................... 63
Функция СЖПРОБЕЛЫ........................................................................ 63
Функция ДЛСТР.................................................................................. 64
Функции НАЙТИ и ПОИСК................................................................... 64
Функция ПОВТОР................................................................................ 64
Функции СЦЕПИТЬ и &........................................................................ 64
Функция ЗАМЕНИТЬ............................................................................ 64
Функция ЗНАЧЕН................................................................................ 65
Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ............................................ 65
Функция СИМВОЛ............................................................................... 65
Функция ПЕЧСИМВ.............................................................................. 65
Функция ПОДСТАВИТЬ........................................................................ 65
Ответы на вопросы.................................................................................. 66
Извлечение данных с помощью Мастера распределения текста
по столбцам........................................................................................ 69
Задания................................................................................................... 74
Глава 7 Даты и функции работы с датами................................................... 77
Ответы на вопросы.................................................................................. 78
Задания................................................................................................... 83
Глава 8 Оценка инвестиций по чистой приведенной стоимости................ 86
Ответы на вопросы.................................................................................. 87
Задания................................................................................................... 91
Глава 9 Внутренняя ставка доходности....................................................... 93
Ответы на вопросы ................................................................................. 94
Задания................................................................................................... 97
Глава 10 Еще несколько финансовых функций Excel................................. 100
Ответы на вопросы................................................................................ 100
Функции ОБЩДОХОД и ОБЩПЛАТ.................................................... 106
Задания................................................................................................. 110
Глава 11 Циклические ссылки...................................................................... 115
Ответы на вопросы................................................................................ 115
Задания................................................................................................. 118
Глава 12 Функция ЕСЛИ................................................................................. 121
Ответы на вопросы................................................................................ 122
Задания................................................................................................. 136
Глава 13 Время и функции времени............................................................. 142
Ответы на вопросы................................................................................ 143
Задания................................................................................................. 147
Глава 14 Команда Специальная вставка...................................................... 148
Ответы на вопросы................................................................................ 148
Задания................................................................................................. 152
Глава 15 Трехмерные формулы и гиперссылки.......................................... 153
Ответы на вопросы................................................................................ 153
Задания................................................................................................. 157
Глава 16 Инструменты проверки зависимостей.......................................... 158
Ответы на вопросы................................................................................ 161
Задания................................................................................................. 165
Глава 17 Анализ чувствительности с помощью таблиц данных................ 166
Ответы на вопросы................................................................................ 167
Задания................................................................................................. 174
Глава 18 Инструмент Подбор параметра...................................................... 178
Ответы на вопросы................................................................................ 179
Задания................................................................................................. 181
Глава 19 Анализ чувствительности с помощью Диспетчера сценариев.... 183
Ответ на вопрос .................................................................................... 183
Замечания........................................................................................ 186
Задания................................................................................................. 187
Глава 20 Функции СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СЧЁТ, СЧЁТЗ
и СЧИТАТЬПУСТОТЫ....................................................................... 188
Ответы на вопросы................................................................................ 190
Замечания........................................................................................ 193
Задания................................................................................................. 194
Глава 21 Функции СУММЕСЛИ, СРЗНАЧЕСЛИ, СУММЕСЛИМН
и СРЗНАЧЕСЛИМН........................................................................... 195
Ответы на вопросы................................................................................ 196
Задания................................................................................................. 199
Глава 22 Функция СМЕЩ............................................................................... 202
Ответы на вопросы................................................................................ 203
Замечания........................................................................................ 213
Задания................................................................................................. 214
Глава 23 Функция ДВССЫЛ........................................................................... 216
Ответы на вопросы................................................................................ 217
Задания................................................................................................. 223
Глава 24 Условное форматирование............................................................ 225
Ответы на вопросы................................................................................ 227
Задания................................................................................................. 251
Глава 25 Сортировка в Excel.......................................................................... 255
Ответы на вопросы................................................................................ 255
Задания................................................................................................. 263
Глава 26 Таблицы........................................................................................... 264
Ответы на вопросы................................................................................ 264
Задания................................................................................................. 278
Глава 27 Счетчики, полосы прокрутки, переключатели, флажки,
группы и поля со списками............................................................ 280
Ответы на вопросы................................................................................ 281
Задания................................................................................................. 291
Глава 28 Революция в аналитике................................................................. 292
Ответы на вопросы................................................................................ 292
Глава 29 Введение в оптимизацию с надстройкой Поиск решения........... 298
Задания................................................................................................. 302
Глава 30 Поиск решения при определении оптимального
ассортимента продукции................................................................ 303
Ответы на вопросы................................................................................ 303
Задания................................................................................................. 313
Глава 31 Поиск решения при планировании расписания работы
сотрудников.................................................................................... 316
Ответ на вопрос..................................................................................... 316
Задания................................................................................................. 319
Глава 32 Поиск решения для задач транспортировки и распределения.. 321
Ответ на вопрос..................................................................................... 321
Задания................................................................................................. 324
Глава 33 Поиск решения для бюджетирования капиталовложений......... 327
Ответ на вопрос..................................................................................... 327
Обработка других ограничений........................................................ 330
Решение задач бинарного и целочисленного программирования...... 332
Задания................................................................................................. 333
Глава 34 Поиск решения при финансовом планировании......................... 335
Ответы на вопросы................................................................................ 336
Задания................................................................................................. 340
Глава 35 Поиск решения при оценке спортивных команд......................... 342
Ответ на вопрос..................................................................................... 343
Почему эта модель не является линейной моделью
поиска решения?.............................................................................. 346
Задания................................................................................................. 346
Глава 36 Расположение складов по методу ОПГ с несколькими
начальными точками и согласно эволюционному поиску
решения........................................................................................... 347
Метод ОПГ с несколькими начальными точками и эволюционный
поиск решения....................................................................................... 347
Как Поиск решения применяется к линейным задачам?.................... 347
Как метод ОПГ применяется к нелинейным оптимизационным
моделям?.......................................................................................... 348
Как метод эволюционного поиска решения применяется
к негладким оптимизационным задачам?.......................................... 351
Ответы на вопросы................................................................................ 351
Задания................................................................................................. 355
Глава 37 Штрафы и эволюционный поиск решения................................... 357
Ответы на вопросы................................................................................ 357
Условное форматирование для выделения рейтинга
сотрудника....................................................................................... 360
Задания................................................................................................. 361
Глава 38 Задача коммивояжера.................................................................... 363
Ответы на вопросы................................................................................ 363
Задания................................................................................................. 366
Глава 39 Импорт данных из текстового файла или документа.................. 367
Ответ на вопрос..................................................................................... 367
Задания................................................................................................. 372
Глава 40 Проверка достоверности данных.................................................. 373
Ответы на вопросы................................................................................ 373
Замечания........................................................................................ 377
Задания................................................................................................. 378
Глава 41 Обобщение данных на гистограммах и диаграммах Парето...... 381
Ответы на вопросы................................................................................ 381
Задания................................................................................................. 392
Глава 42 Обобщение данных с помощью описательной статистики......... 394
Ответы на вопросы................................................................................ 395
Выделение выбросов с помощью условного форматирования........... 401
Задания................................................................................................. 411
Глава 43 Сводные таблицы и срезы для описания данных........................ 414
Ответы на вопросы................................................................................ 415
Замечания по группированию........................................................... 444
Что такое детализация?.................................................................... 446
Задания................................................................................................. 455
Глава 44 Модель данных............................................................................... 459
Ответы на вопросы................................................................................ 459
Задания................................................................................................. 467
Глава 45 PowerPivot....................................................................................... 468
Ответы на вопросы................................................................................ 469
Задания................................................................................................. 477
Глава 46 Power View и 3D Maps..................................................................... 478
Ответы на вопросы................................................................................ 479
Задания................................................................................................. 492
Глава 47 Спарклайны..................................................................................... 493
Ответы на вопросы................................................................................ 493
Задания................................................................................................. 497
Глава 48 Обработка данных с помощью статистических функций
для баз данных............................................................................... 498
Ответы на вопросы................................................................................ 500
Задания................................................................................................. 505
Глава 49 Фильтрация данных и удаление дубликатов............................... 507
Ответы на вопросы................................................................................ 509
Задания................................................................................................. 521
Глава 50 Консолидация данных.................................................................... 522
Ответ на вопрос..................................................................................... 522
Задания................................................................................................. 526
Глава 51 Создание промежуточных итогов................................................. 527
Ответы на вопросы................................................................................ 527
Задания................................................................................................. 531
Глава 52 Приемы работы с диаграммами.................................................... 532
Ответы на вопросы................................................................................ 533
Задания................................................................................................. 567
Глава 53 Оценка линейных зависимостей................................................... 570
Ответы на вопросы................................................................................ 572
Задания................................................................................................. 577
Глава 54 Моделирование экспоненциального роста................................... 579
Ответ на вопрос..................................................................................... 580
Задания................................................................................................. 582
Глава 55 Степенная кривая........................................................................... 583
Ответ на вопрос..................................................................................... 585
Задания................................................................................................. 587
Глава 56 Представление зависимостей с помощью корреляции............... 589
Ответ на вопрос..................................................................................... 591
Заполнение корреляционной матрицы.............................................. 593
Функция КОРРЕЛ.............................................................................. 594
Связь между коэффициентом корреляции и R2................................. 594
Корреляция и регрессия к среднему................................................. 594
Задания................................................................................................. 594
Глава 57 Введение во множественную регрессию...................................... 596
Ответы на вопросы................................................................................ 596
Глава 58 Включение качественных факторов во множественную
регрессию........................................................................................ 603
Ответы на вопросы................................................................................ 603
Глава 59 Моделирование нелинейных характеристик и взаимосвязей.... 614
Ответы на вопросы................................................................................ 614
Задания к главам 57–59......................................................................... 618
Глава 60 Однофакторный дисперсионный анализ...................................... 622
Ответы на вопросы................................................................................ 623
Задания................................................................................................. 627
Глава 61 Рандомизированные блоки и двухфакторный
дисперсионный анализ.................................................................. 628
Ответы на вопросы................................................................................ 629
Задания................................................................................................. 637
Глава 62 Скользящие средние для временных рядов................................. 638
Ответ на вопрос..................................................................................... 638
Задание................................................................................................. 640
Глава 63 Метод Винтерса............................................................................... 641
Характеристики временных рядов.......................................................... 641
Определение параметров...................................................................... 642
Определение начальных параметров для метода Винтерса.................... 642
Вычисление сглаживающих постоянных................................................ 643
Замечания........................................................................................ 645
Задания................................................................................................. 645
Глава 64 Метод прогнозирования «по отношению
к скользящему среднему»............................................................. 646
Ответы на вопросы................................................................................ 646
Задание................................................................................................. 649
Глава 65 Прогноз для особых случаев......................................................... 650
Ответы на вопросы................................................................................ 650
Задания................................................................................................. 658
Глава 66 Введение в теорию вероятностей................................................. 659
Ответы на вопросы................................................................................ 659
Задания................................................................................................. 666
Глава 67 Введение в случайные величины.................................................. 669
Ответы на вопросы................................................................................ 669
Задания................................................................................................. 673
Глава 68 Биномиальные, гипергеометрические и отрицательные
биномиальные случайные величины........................................... 674
Ответы на вопросы................................................................................ 675
Задания................................................................................................. 681
Глава 69 Пуассоновская и экспоненциальная случайные величины........ 683
Ответы на вопросы................................................................................ 683
Задания................................................................................................. 686
Глава 70 Нормальная случайная величина и Z-оценка.............................. 687
Ответы на вопросы................................................................................ 687
Задания................................................................................................. 695
Глава 71 Распределение Вейбулла и бета-распределение:
моделирование надежности механизмов
и продолжительности работы....................................................... 698
Ответы на вопросы................................................................................ 698
Задания................................................................................................. 703
Глава 72 Создание вероятностных высказываний на основе
прогнозов........................................................................................ 704
Ответы на вопросы................................................................................ 705
Задания................................................................................................. 707
Глава 73 Логарифмически нормальная случайная величина
в моделировании курса акций....................................................... 708
Ответы на вопросы................................................................................ 708
Замечания........................................................................................ 711
Задания................................................................................................. 712
Глава 74 Введение в моделирование по методу Монте-Карло.................. 713
Ответы на вопросы................................................................................ 713
Влияние риска на решение............................................................... 720
Доверительный интервал для средней прибыли............................... 720
Задания................................................................................................. 721
Глава 75 Вычисление оптимальной цены предложения............................ 723
Ответы на вопросы................................................................................ 723
Задания................................................................................................. 727
Глава 76 Моделирование цен на акции и распределения средств
между активами.............................................................................. 729
Ответы на вопросы................................................................................ 730
Задания................................................................................................. 737
Глава 77 Развлечения и игры: моделирование вероятностей
для азартных игр и спортивных соревнований........................... 739
Ответы на вопросы................................................................................ 739
Задания................................................................................................. 746
Глава 78 Анализ данных с помощью повторной выборки.......................... 748
Ответ на вопрос..................................................................................... 748
Задания................................................................................................. 751
Глава 79 Ценообразование опционов........................................................... 752
Ответы на вопросы................................................................................ 753
Задания................................................................................................. 764
Глава 80 Определение потребительской ценности..................................... 766
Ответы на вопросы................................................................................ 766
Задания................................................................................................. 770
Глава 81 Оптимальный размер заказа в модели управления запасами.... 772
Ответы на вопросы................................................................................ 772
Задания................................................................................................. 776
Глава 82 Построение моделей управления запасами
для неопределенного спроса......................................................... 777
Ответы на вопросы................................................................................ 778
Невыполненный заказ...................................................................... 778
Упущенные продажи......................................................................... 779
Задания................................................................................................. 783
Глава 83 Теория массового обслуживания (теория очередей).................. 784
Ответы на вопросы................................................................................ 784
Задания................................................................................................. 789
Глава 84 Оценка кривой спроса.................................................................... 791
Ответы на вопросы................................................................................ 791
Задания................................................................................................. 795
Глава 85 Ценообразование продуктов с сопутствующими товарами........ 796
Ответ на вопрос..................................................................................... 796
Задания................................................................................................. 799
Глава 86 Ценообразование продуктов с помощью субъективно
определяемого спроса.................................................................... 801
Ответы на вопросы................................................................................ 801
Задания................................................................................................. 804
Глава 87 Нелинейное ценообразование....................................................... 807
Ответы на вопросы................................................................................ 807
Задания................................................................................................. 815
Глава 88 Формулы массива и функции, возвращающие массив............... 816
Ответы на вопросы................................................................................ 817
Задания................................................................................................. 832
Глава 89 Запись макросов............................................................................. 836
Ответы на вопросы................................................................................ 836
Задания................................................................................................. 846
Об авторе .......................................................................................................... 847
Список вопросов................................................................................................ 848