Table Of Content2015
УДК 004.6
ББК 32.973.26-018.2
В49
ВинстонУэйн Л.
В49 Microsoft Excel 2013. Анализ данных и бизнес-моделирование: Пер.сангл.—М.:
Издательство «Русская редакция»; СПб.: «БХВ-Петербург»,2015.—864с.: ил.
ISBN 978-5-7502-0437-3(«Русская редакция»)
ISBN 978-5-9775-3584-7 («БХВ-Петербург»)
Данное практическое руководство ориентировано на сценарии. В нем наглядно демонстри-
руется использование новейших инструментов Excel для интеграции данных из нескольких
таблиц и создания реляционного источника данных в книге Excel. Рассказано о новых тенден-
циях в прогнозной и предписывающей аналитике. Описаны кривые тренда, множественная
регрессия, экспоненциальное сглаживание, расширенные функции Excel, ключевые финансо-
вые, статистические функции, функции даты и времени. Рассмотрено построение наглядных
диаграмм, решение сложных задач оптимизации, имитационное моделирование по методу
Монте-Карло.
Материал книги сопровождается английской версией учебных файлов.
Для опытных пользователей Microsoft Excel
УДК 004.6
ББК 32.973.26-018.2
© 2015, Russian Edition Publishers, Translation BHV-St.Petersburg.
Authorized Russian translation of the English edition of Microsoft Excel 2013: Data Analysis and Business Modeling,
ISBN 978-0-7356-6913-0 © Wayne L. Winston.
This translation is published and sold by permission of O’Reilly Media, Inc., which owns or controls all rights to publish and sell the same.
© 2015, ООО «Издательство «Русская редакция», перевод издательство «БХВ-Петербург».
Авторизованный перевод с английского на русский язык произведения MicrosoftExcel2013:DataAnalysisandBusinessModeling,
ISBN978-0-7356-6913-0 © WayneL.Winston
Этот перевод оригинального издания публикуется и продается с разрешения O’Reilly Media, Inc., которая владеет или распоряжа-
ется всеми правами на его публикацию и продажу.
© 2015, оформление и подготовка к изданию, ООО «Издательство «Русская редакция», издательство «БХВ-Петербург».
Microsoft, а также товарные знаки, перечисленные в списке, расположенном по адресу:
http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx являются товарными знаками или охраняемы-
ми товарными знаками корпорации Microsoft в США и/или других странах. Все другие товарные знаки являются собственностью
соответствующих фирм.
Все названия компаний, организаций и продуктов, а также имена лиц, используемые в примерах, вымышлены и не имеют никакого
отношения к реальным компаниям, организациям, продуктам и лицам.
Уэйн Л. Винстон
Microsoft Excel 2013. Анализ данных и бизнес-моделирование
Перевод с английского языка Наталии Гавриловой
Совместный проект издательства «Русская редакция» и издательства «БХВ-Петербург»
Подписано в печать 04.02.15.
Формат 70 1001/16. Печать офсетная. Усл. печ. л. 69,66.
Тираж 1000 экз.Заказ №
Первая Академическая типография "Наука"
199034, Санкт-Петербург, 9 линия, 12/28
ISBN 978-0-7356-6913-0(англ.)
ISBN 978-5-7502-0437-3(«Русская редакция»)
ISBN 978-5-9775-3584-7(«БХВ-Петербург»)
Оглавление
Об авторе ............................................................................................................................ 1(cid:2)
Введение ............................................................................................................................. 3
Глава 1. Имена диапазонов ............................................................................................. 9(cid:2)
Глава 2. Функции поиска............................................................................................... 21(cid:2)
Глава 3. Функция Индекс .............................................................................................. 29(cid:2)
Глава 4. Функция ПОИСКПОЗ ..................................................................................... 33(cid:2)
Глава 5. Текстовые функции ......................................................................................... 41(cid:2)
Глава 6. Даты и функции даты...................................................................................... 57(cid:2)
Глава 7. Оценка инвестиций по чистой приведенной стоимости ............................. 65(cid:2)
Глава 8. Внутренняя ставка доходности ...................................................................... 73(cid:2)
Глава 9. Еще несколько финансовых функций Excel ................................................. 81(cid:2)
Глава 10. Циклические ссылки ..................................................................................... 95(cid:2)
Глава 11. Функция ЕСЛИ ............................................................................................ 101(cid:2)
Глава 12. Время и функции времени .......................................................................... 121(cid:2)
Глава 13. Команда Специальная вставка .................................................................. 127(cid:2)
Глава 14. Трехмерные формулы ................................................................................. 133(cid:2)
Глава 15. Инструменты проверки зависимостей и надстройка Inquire .................. 137(cid:2)
Глава 16. Анализ чувствительности с помощью таблиц данных ............................ 151(cid:2)
Глава 17. Инструмент Подбор параметра ................................................................ 163(cid:2)
VI Оглавление
Глава 18. Анализ чувствительности с помощью Диспетчера сценариев ............... 169(cid:2)
Глава 19. Функции СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СЧЁТ, СЧЁТЗ
и СЧИТАТЬПУСТОТЫ ................................................................................................. 175(cid:2)
Глава 20. Функции СУММЕСЛИ, СРЗНАЧЕСЛИ, СУММЕСЛИМН
и СРЗНАЧЕСЛИМН ...................................................................................................... 183(cid:2)
Глава 21. Функция СМЕЩ .......................................................................................... 189(cid:2)
Глава 22. Функция ДВССЫЛ ...................................................................................... 203(cid:2)
Глава 23. Условное форматирование ......................................................................... 213(cid:2)
Глава 24. Сортировка в Excel ...................................................................................... 245(cid:2)
Глава 25. Таблицы ........................................................................................................ 253(cid:2)
Глава 26. Счетчики, полосы прокрутки, переключатели, флажки, группы
и поля со списками ........................................................................................................ 267(cid:2)
Глава 27. Революция в аналитике ............................................................................... 281(cid:2)
Глава 28. Введение в оптимизацию с надстройкой Поиск решения ....................... 287(cid:2)
Глава 29. Поиск решения при определении оптимального ассортимента
продукции ...................................................................................................................... 293(cid:2)
Глава 30. Поиск решения при планировании расписания работы сотрудников .... 307(cid:2)
Глава 31. Поиск решения для задач транспортировки и распределения ................ 313(cid:2)
Глава 32. Поиск решения для бюджетирования капиталовложений ...................... 319(cid:2)
Глава 33. Поиск решения при финансовом планировании....................................... 327(cid:2)
Глава 34. Поиск решения при оценке спортивных команд ...................................... 335(cid:2)
Глава 35. Расположение складов по методу ОПГ с несколькими начальными
точками и согласно эволюционному поиску решения .............................................. 341(cid:2)
Глава 36. Штрафы и эволюционный поиск решения ............................................... 351(cid:2)
Глава 37. Задача коммивояжера ................................................................................. 359(cid:2)
Глава 38. Импорт данных из текстового файла или документа .............................. 363(cid:2)
Глава 39. Импорт данных из сети Интернет .............................................................. 369(cid:2)
Глава 40. Проверка достоверности данных ............................................................... 373(cid:2)
Глава 41. Обобщение данных на гистограммах ........................................................ 383(cid:2)
Оглавление VII
Глава 42. Обобщение данных с помощью описательной статистики ..................... 393(cid:2)
Глава 43. Сводные таблицы и срезы для описания данных ..................................... 409(cid:2)
Глава 44. Модель данных ............................................................................................ 457(cid:2)
Глава 45. PowerPivot .................................................................................................... 469(cid:2)
Глава 46. Power View ................................................................................................... 481(cid:2)
Глава 47. Спарклайны .................................................................................................. 495(cid:2)
Глава 48. Обработка данных с помощью статистических функций
для баз данных ............................................................................................................... 501(cid:2)
Глава 49. Фильтрация данных и удаление дубликатов ............................................ 511(cid:2)
Глава 50. Консолидация данных................................................................................. 527(cid:2)
Глава 51. Создание промежуточных итогов .............................................................. 533(cid:2)
Глава 52. Приемы работы с диаграммами ................................................................. 539(cid:2)
Глава 53. Оценка линейных зависимостей ................................................................ 573(cid:2)
Глава 54. Моделирование экспоненциального роста ............................................... 583(cid:2)
Глава 55. Степенная кривая ........................................................................................ 587(cid:2)
Глава 56. Представление зависимостей с помощью корреляции ............................ 595(cid:2)
Глава 57. Введение во множественную регрессию .................................................. 603(cid:2)
Глава 58. Включение качественных факторов во множественную регрессию ..... 611(cid:2)
Глава 59. Моделирование нелинейных характеристик и взаимосвязей ................. 621(cid:2)
Глава 60. Однофакторный дисперсионный анализ ................................................... 629(cid:2)
Глава 61. Рандомизированные блоки и двухфакторный дисперсионный анализ .... 635(cid:2)
Глава 62. Скользящие средние для временных рядов .............................................. 645(cid:2)
Глава 63. Метод Винтерса ........................................................................................... 649(cid:2)
Глава 64. Метод прогнозирования "по отношению к скользящему среднему" ..... 655(cid:2)
Глава 65. Прогноз для особых случаев ...................................................................... 659(cid:2)
Глава 66. Введение в случайные величины ............................................................... 669(cid:2)
Глава 67. Биномиальные, гипергеометрические и отрицательные
биномиальные случайные величины ........................................................................... 675(cid:2)
VIII Оглавление
Глава 68. Пуассоновская и экспоненциальная случайные величины ..................... 685(cid:2)
Глава 69. Нормальная случайная величина ............................................................... 689(cid:2)
Глава 70. Распределение Вейбулла и бета-распределение: моделирование
надежности механизмов и продолжительности работы ............................................ 697(cid:2)
Глава 71. Создание вероятностных высказываний на основе прогнозов ............... 703(cid:2)
Глава 72. Логарифмически нормальная случайная величина в моделировании
курса акции .................................................................................................................... 707(cid:2)
Глава 73. Введение в моделирование по методу Монте-Карло ............................... 711(cid:2)
Глава 74. Вычисление оптимальной цены предложения ......................................... 721(cid:2)
Глава 75. Моделирование цен на акции и распределения средств
между активами ............................................................................................................. 727(cid:2)
Глава 76. Игры и развлечения: моделирование вероятностей для азартных игр
и спортивных соревнований ......................................................................................... 737(cid:2)
Глава 77. Анализ данных с помощью повторной выборки ...................................... 747(cid:2)
Глава 78. Ценообразование опционов ........................................................................ 751(cid:2)
Глава 79. Определение ценности клиента ................................................................. 765(cid:2)
Глава 80. Оптимальный размер заказа в модели управления запасами ................. 771(cid:2)
Глава 81. Построение моделей управления запасами для неопределенного
спроса ............................................................................................................................. 777(cid:2)
Глава 82. Теория массового обслуживания (теория очередей) ............................... 785(cid:2)
Глава 83. Оценка кривой спроса ................................................................................. 793(cid:2)
Глава 84. Ценообразование продуктов с сопутствующими товарами .................... 799(cid:2)
Глава 85. Ценообразование продуктов с помощью субъективно определяемого
спроса ............................................................................................................................. 805(cid:2)
Глава 86. Нелинейное ценообразование .................................................................... 811(cid:2)
Глава 87. Функции и формулы массива ..................................................................... 821(cid:2)
Предметный указатель .................................................................................................. 840(cid:2)
Об авторе
УЭЙН Л. ВИНСТОН (WAYNE L. WINSTON) является почетным профессором
Школы бизнеса Kelley School of Business при Университете Индианы и в настоящее
время приглашенным профессором в Колледже бизнеса Bauer College of Business
при Университете Хьюстона. Он получил свыше 45 наград для преподавателей,
преподавал Excel и моделирование в Excel тысячам бизнес-аналитиков, работаю-
щих в компаниях из списка Fortune 500. Он провел консультации для многих орга-
низаций, включая команды НБА Dallas Mavericks и New York Knicks. Он написал
15 книг по Excel, по методам управления и исследованию операций, в том числе
"Mathletics", в которой описывается применение математических методов в такой
новой и весьма интересной области, как спортивная аналитика.
Введение
Если вы читаете эту книгу, и неважно, работаете ли вы в корпорации из списка
Fortune 500, в небольшой компании, в правительственном учреждении или в не-
коммерческой организации, скорее всего, вы применяете Microsoft Excel в своей
повседневной работе. Возможно, ваша работа заключается в подведении итогов,
составлении отчетов и анализе данных. Она также может быть связана с построе-
нием аналитических моделей, которые позволяют работодателю увеличить при-
быль, снизить издержки или эффективнее управлять операциями.
С 1999 г. я обучил более продуктивному использованию программы Microsoft Excel
тысячи аналитиков в таких компаниях, как 3M, консалтинговая фирма Booz Allen
Hamilton, Bristol-Myers Squibb, Broadcom Cisco Systems, Deloitte Consulting,
Drugstore.com, eBay, Eli Lilly, Ford, General Electric, General Motors, Intel, Microsoft,
Morgan Stanley, NCR, Owens Corning, Pfizer, Proctor & Gamble, PWC, Schlumberger,
Tellabs, армия США, Министерство обороны США и Verizon. Студенты часто го-
ворили мне, что инструменты и методы, с которыми они ознакомились на моих за-
нятиях, позволили им еженедельно экономить рабочее время и предоставили новые
усовершенствованные подходы к анализу важных бизнес-задач.
Описанные в этой книге приемы я использовал для решения многих бизнес-задач
в собственной консалтинговой практике. Например, с помощью Excel я помог
менеджерам баскетбольных команд НБА Dallas Mavericks и New York Knicks соста-
вить мнение о судьях, игроках и оценить расположение игроков на поле. В течение
последних 15 лет я также вел занятия по бизнес-моделированию и анализу данных
в Excel для студентов, обучающихся по программам MBA в Школе бизнеса Kelley
School of Business при Университете Индианы. (Мой преподавательский опыт под-
твержден более чем 45 наградами для преподавателей, включая шесть наград Шко-
лы для преподавателей программ MBA.) Следует также отметить, что 95 процентов
студентов MBA в Университете Индианы выбрали для изучения мой курс модели-
рования в электронных таблицах, который не входил в обязательную программу.
Книга, которую вы держите в руках, представляет собой попытку сделать этот по-
пулярный курс обучения доступным каждому. Далее приведены доводы, согласно
которым, смею надеяться, книга научит вас работать в Excel более эффективно.
(cid:2) Материалы протестированы во время обучения тысяч аналитиков, работающих
в корпорациях из списка Fortune 500 и правительственных учреждениях, в том
числе в армии США.
