Файл: 3146.pdf

Ольга Кулешова 

Центр Компьютерного Обучения 

"Специалист" при МГТУ им. Баумана 

www.specialist.ru 

Москва, 2012 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

2 

Кулешова О.В.

, 

Центр Компьютерного Обучения 

"

Специалист

"

, 

2012 

Microsoft Excel 2010. Анализ и Визуализация данных. Решение практических задач. 

Методическое пособие к курсу: 

"Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных." 

Все  названия  программных  продуктов  являются  зарегистрированными  торговыми  марками 
соответствующих  фирм.  Никакая  часть  настоящего  издания  ни  в  каких  целях  не  может  быть 
воспроизведена  в  какой  бы  то  ни  было  форме  и  какими  бы  то  ни  было  средствами,  будь  то 
электронные и механические, включая фотокопирование и запись на магнитный носитель, если на 
это нет письменного разрешения автора. 

© Центр компьютерного обучения «Специалист» при МГТУ им. Н.Э. Баумана, 2012 г. 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

3 

ОГЛАВЛЕНИЕ: 

Модуль 1.

Применение категории встроенных функций «Ссылки и массивы» и формул 

массивов 

 ................................................................................................................................... 5

Формулы массивов ................................................................................................................................. 5 

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

Изменение формулы массива ........................................................................................................... 6 

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

Двусторонний поиск с использованием функций ПОИСКПОЗ и ИНДЕКС ......................................... 7 

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

Использование функции ДВССЫЛ для обработки данных с одного или нескольких листов ........ 11 

Извлечение данных с использованием функций СТРОКА, СТОЛБЕЦ .............................................. 12 

Транспонирование таблиц .................................................................................................................. 13 

Использование функции ТРАНСП и формулы массива ................................................................. 14 

С использованием функций ДВССЫЛ, АДРЕС, СТРОКА и СТОЛБЕЦ .............................................. 14 

Работа с функцией СМЕЩ .................................................................................................................... 16 

Модуль 2.

Прогнозирование данных ....................................................................................... 17

Выделение тренда ................................................................................................................................ 17 

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

Функции регрессионного анализа .................................................................................................. 18 

Построение линий тренда ................................................................................................................... 20 

Учет сезонности в прогнозах ............................................................................................................... 21 

Модуль 3.

Диаграммы .............................................................................................................. 22

Смешанные диаграммы ....................................................................................................................... 22 

Гистограмма с отображением итогов ................................................................................................. 23 

Проектная диаграмма Ганта ................................................................................................................ 25 

Диаграмма сравнений Торнадо .......................................................................................................... 27 

Каскадная диаграмма Водопад ........................................................................................................... 28 

Создание калибровочной диаграммы Термометр ........................................................................... 29 

Диаграммы с пользовательскими элементами управления ............................................................ 31 

Диаграмма с включением/выключением рядов данных ............................................................. 31 

Диаграмма с выбором значений .................................................................................................... 32 

Диаграмма с зумом и прокруткой .................................................................................................. 34 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

www.specialist.ru 

   Центр Компьютерного обучения «Специалист» 

4 

Модуль 4.

Вариативный анализ "Что Если" и Оптимизация .................................................... 38

Использование инструмента Таблица данных для анализа развития ситуации при 2-х 
переменных .......................................................................................................................................... 38 

Оценка развития ситуации и выбор оптимальной стратегии с помощью Сценариев ................... 39 

Создание сценариев ......................................................................................................................... 39 

Создание отчета ................................................................................................................................ 40 

Решение однокритериальной задачи оптимизации с помощью Подбора параметра.................. 41 

Решение многокритериальных задач оптимизации с использованием надстройки Поиск 
решения ................................................................................................................................................. 42 

Модуль 5.

Кубы данных OLAP для оперативного анализа данных в MS Excel ......................... 44

Введение в кубы данных: что такое кубы OLAP и их назначение ................................................ 44 

Преимущества использования кубов данных OLAP. Сравнение запросов к внешним базам 
данных и кубов OLAP ........................................................................................................................ 44 

Подключение к локальному кубу данных OLAP ............................................................................ 46 

Работа со сводной таблицей OLAP .................................................................................................. 46 

Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных

Центр Компьютерного обучения «Специалист»   

www.specialist.ru 

5 

Модуль 1.

П

РИМЕНЕНИЕ КАТЕГОРИИ ВСТРОЕННЫХ ФУНКЦИЙ 

«С

СЫЛКИ И МАССИВЫ

»

 И ФОРМУЛ МАССИВОВ

Формулы массивов 

Массив – это набор данных, объединенных в группу. Массивы бывают одномерными (строка, 
столбец) или двумерными (таблица или матрица). 

Формула массива – это формула, в которой производится вычисления над одним или 
несколькими наборами данных (аргументами массива), а затем отображающая один или 
несколько результатов. 

Одна формула массива может заменить собой несколько формул, расположенных на листе, 
помогая выполнять сложные расчеты или выполнять вычисления, недоступные при помощи 
других формул. 

Признаком формулы массивы являются фигурные скобки, в которые заключена формула. 

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

Последовательность действий: 

1.

Выделить ячейки для результатов 

2.

Ввести с клавиатуры знак 

=

3.

Написать формулу: 



Выделить 1-й массив данных 



Ввести знак операции: 

+

, 

-

, 

*

, 

/

, 

&



Выделить 2-й массив данных и т.д. 

4.

Завершить формулу нажатием 

Ctrl

+

Shift

+

Enter