КулЛиб - Классная библиотека! Скачать книги бесплатно
Всего книг - 706123 томов
Объем библиотеки - 1347 Гб.
Всего авторов - 272721
Пользователей - 124653

Новое на форуме

Новое в блогах

Впечатления

a3flex про Невзоров: Искусство оскорблять (Публицистика)

Да, тварь редкостная.

Рейтинг: +1 ( 1 за, 0 против).
DXBCKT про Гончарова: Крылья Руси (Героическая фантастика)

Обычно я стараюсь никогда не «копировать» одних впечатлений сразу о нескольких томах, однако в отношении части четвертой (и пятой) это похоже единственно правильное решение))

По сути — что четвертая, что пятая часть, это некий «финал пьесы», в котором слелись как многочисленные дворцовые интриги (тайны, заговоры, перевороты и пр), так и вся «геополитика» в целом...

В остальном же — единственная возможная претензия (субъективная

  подробнее ...

Рейтинг: 0 ( 0 за, 0 против).
medicus про Федотов: Ну, привет, медведь! (Попаданцы)

По аннотации сложилось впечатление, что это очередная писанина про аристократа, написанная рукой дегенерата.

cit anno: "...офигевшая в край родня [...] не будь я барон Буровин!".

Барон. "Офигевшая" родня. Не охамевшая, не обнаглевшая, не осмелевшая, не распустившаяся... Они же там, поди, имения, фабрики и миллионы делят, а не полторашку "Жигулёвского" на кухне "хрущёвки". Но хочется, хочется глянуть внутрь, вдруг всё не так плохо.

Итак: главный

  подробнее ...

Рейтинг: 0 ( 0 за, 0 против).
Dima1988 про Турчинов: Казка про Добромола (Юмористическая проза)

А продовження буде ?

Рейтинг: -1 ( 0 за, 1 против).
Colourban про Невзоров: Искусство оскорблять (Публицистика)

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

  подробнее ...

Рейтинг: +2 ( 3 за, 1 против).

Excel 2010: Готовые ответы и полезные приемы профессиональной работы [Коллектив авторов] (pdf) читать онлайн

Книга в формате pdf! Изображения и текст могут не отображаться!


 [Настройки текста]  [Cбросить фильтры]

Серогодский В. В., Рогозин А.В., Козлов Д. А.,
Прокди Р. Г. и др.

EXCEL 2010
ГОТОВЫЕ ОТВЕТЫ
И ПОЛЕЗНЫЕ ПРИЕМЫ
ПРОФЕССИОНАЛЬНОЙ РАБОТЫ

КНИГА + 7 ОБУЧАЮЩИХ КУРСОВ НА DVD

Наука и Техника
Санкт-Петербург
2013

Серогодский В. В., Рогозин А.В., Козлов Д. А., Дружинин А. Ю., Прокди Р. Г. и др.
EXCEL 2010. ГОТОВЫЕ ОТВЕТЫ И ПОЛЕЗНЫЕ ПРИЕМЫ ПРОФЕССИОНАЛЬНОЙ
РАБОТЫ. КНИГА + 7 ОБУЧАЮЩИХ КУРСОВ НА DVD — СПб.: Наука и Техника, 2013. —

352 с.: ил., + DVD (7 обучающих курсов)

Серия «Просто о сложном»
С этой книгой вы научитесь грамотно пользоваться профессиональными
возможностями Excel 2010, сможете существенно повысить эффективность
своей работы и научитесь быстро решать самые различные задачи. Особое
внимание уделено таким важным вопросам, как проведение расчетов (от решения
алгебраических уравнений до использования спец. функций Excel и применения
мегаформул), оформление данных (построение всевозможных графиков и
диаграмм), использование сводных таблиц, особенности проведения анализа и
выявления различных закономерностей в данных.
Книга рассчитана на самый широкий круг читателей. Ведь все пользуются Excel’ем,
но в большинстве случаев делают это неумело или попросту не знают, как что-то
сделать в нем. Книга написана простым и доступным языком. Приводится огромное
количество наглядных примеров.

На прилагаемом DVD размещены 7 обучающих курсов по трюкам и
спец. приемам, практике проведения разного рода вычислений и
анализу данных в Excel 2010

Контактные телефоны издательства:
(812) 567 70 25, (812) 567 70 26, (044) 516 38 66
Официальный сайт: www.nit.com.ru
© Прокди Р. Г., 2013

© Наука и техника (оригинал-макет), 2013

СОДЕРЖАНИЕ
ЧАСТЬ I. ВЫЧИСЛЕНИЯ, ФОРМУЛЫ И ФУНКЦИИ EXCEL
ГЛАВА 1. СОЗДАНИЕ БАЗОВЫХ ФОРМУЛ...................................................14
1.1. ОГРАНИЧЕНИЯ ДЛЯ ФОРМУЛ В EXCEL 2010 ............................................................... 15
1.2. ВВОД И РЕДАКТИРОВАНИЕ ФОРМУЛ ........................................................................ 16
1.3. ИСПОЛЬЗОВАНИЕ ОСНОВНЫХ ОПЕРАЦИЙ В ФОРМУЛАХ.............................................. 17

1.3.1. Арифметические формулы ......................................................... 17
1.3.2. Операторы сравнения ................................................................ 17
1.3.3. Использование текстовых формул .............................................. 18
1.3.4. Операторы ссылок ...................................................................... 18
1.4. НЕСКОЛЬКО СЛОВ О ПОРЯДКЕ ВЫПОЛНЕНИЯ ОПЕРАТОРОВ В ФОРМУЛЕ ....................... 19

1.4.1. Управление порядком вычисления формул ................................. 20
1.5. КОНТРОЛЬ ВЫЧИСЛЕНИЙ НА ЛИСТЕ ......................................................................... 22
1.6. КОПИРОВАНИЕ И ПЕРЕМЕЩЕНИЕ ФОРМУЛ ............................................................... 23

1.6.1. Относительный формат ссылок (относительные адреса ячеек)... 24
1.6.2. Абсолютный формат ссылок (абсолютные адреса ячеек) ............ 25
1.6.3. Копирование формулы без изменения при относительном
формате ссылки......................................................................... 26
1.7. ПОКАЗ ФОРМУЛЫ НА ЛИСТЕ ................................................................................... 27
1.8. ПРЕОБРАЗОВАНИЕ ФОРМУЛЫ В ЗНАЧЕНИЕ ............................................................... 27
1.9. СОЗДАНИЕ ИМЕН ДЛЯ КОНСТАНТ, ДИАПАЗОНОВ И ФОРМУЛ ........................................ 28

1.9.1. Работа с именами в формулах: вставка имен в формулы ............ 29
1.9.2. Применение имен к формулам ................................................... 30
1.9.3. Игнорирование типа ссылки ....................................................... 31
1.10. РАБОТА СО ССЫЛКАМИ В ФОРМУЛАХ ..................................................................... 32

1.10.1. О внешних связях...................................................................... 33
1.10.2. Обновление ссылок .................................................................. 34
33
3

Содержание
1.10.3. Изменение источника связи...................................................... 35
1.11. СОЗДАНИЕ ФОРМУЛ, ИСПОЛЬЗУЮЩИХ ДАННЫЕ С РАЗНЫХ ЛИСТОВ ......................... 36
1.12. ОТОБРАЖЕНИЕ СВЯЗАННЫХ ЯЧЕЕК ........................................................................ 38
1.13. РАСШИРЕННЫЕ ВОЗМОЖНОСТИ ПО АВТОЗАПОЛНЕНИЮ — КАК АВТОМАТИЗИРОВАТЬ
ВВОД ДАННЫХ В ЯЧЕЙКИ ............................................................................... 40

1.13.1. Стандартные последовательности ............................................ 40
1.13.2. Последовательности пользователя ........................................... 42
1.13.3. Прогрессии............................................................................... 43
1.13.4. Дублирование данных ............................................................... 45
1.13.5. Дополнительные возможности автозаполнения ........................ 45

ГЛАВА 2. ФОРМАТИРОВАНИЕ ЧИСЕЛ, ВРЕМЕН И ДАТ ...................................47
2.1. ФОРМАТЫ ОТОБРАЖЕНИЯ ЧИСЕЛ ............................................................................ 48
2.2. ИЗМЕНЕНИЕ ФОРМАТА ОТОБРАЖЕНИЯ ЧИСЕЛ ........................................................... 50
2.3. СОЗДАНИЕ СОБСТВЕННЫХ ЧИСЛОВЫХ ФОРМАТОВ .................................................... 52
2.4. ИСПОЛЬЗОВАНИЕ УСЛОВИЙ ................................................................................... 53
2.5. УСЛОВНОЕ ФОРМАТИРОВАНИЕ И ЕГО ПРИМЕНЕНИЕ (КАК СДЕЛАТЬ ТАК,
ЧТОБЫ ОФОРМЛЕНИЕ ЯЧЕЙКИ АВТОМАТИЧЕСКИ МЕНЯЛОСЬ
В ЗАВИСИМОСТИ ОТ ЕЕ СОДЕРЖИМОГО) ........................................................ 54

ГЛАВА 3. СОЗДАНИЕ ПРОДВИНУТЫХ ФОРМУЛ. ПОЛЕЗНЫЕ ПРИЕМЫ
ПО РАБОТЕ С ФОРМУЛАМИ ........................................................59
3.1. РАБОТА С МАССИВАМИ........................................................................................... 60

3.1.1. Использование формул, работающих с массивами .................... 60
3.1.2. О работе формул-массивов ........................................................ 62
3.1.3. Сложные формулы-массивы ....................................................... 62
3.1.4. Использование констант в массивах ........................................... 63
3.2. ИСПОЛЬЗОВАНИЕ ИТЕРАЦИЙ И ЦИКЛИЧЕСКИХ ССЫЛОК ............................................. 64
3.3. ОБЪЕДИНЕНИЕ ДАННЫХ ИЗ НЕСКОЛЬКИХ КНИГ ......................................................... 67

3.3.1. Объединение по позициям ......................................................... 67
3.3.2. Объединение по категориям ....................................................... 70
3.4. ПРОВЕРКА ПРАВИЛЬНОСТИ ВВОДИМЫХ В ЯЧЕЙКУ ДАННЫХ ........................................ 72

4 4

Содержание
3.5. О ТИПАХ ОШИБОК В ФОРМУЛАХ .............................................................................. 74
3.6. ИСПРАВЛЕНИЕ ДРУГИХ ОШИБОК В EXCEL ................................................................. 75

3.6.1. Отсутствующие или лишние круглые скобки ............................... 75
3.6.2. Обработка ошибок с помощью функции ЕОШИБКА() .................. 75

ГЛАВА 4. ПОЛЕЗНЫЕ ФУНКЦИИ EXCEL 2010 ...............................................77
4.1. СТРУКТУРА ФУНКЦИЙ............................................................................................. 78
4.2. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В ФОРМУЛАХ ................................................................ 80
4.3. ВВОД ФУНКЦИЙ С ПОМОЩЬЮ МАСТЕРА ................................................................... 82
4.4. ФУНКЦИИ ДЛЯ РАБОТЫ С ТЕКСТОМ В EXCEL .............................................................. 84

4.4.1. Работа с символами и кодами..................................................... 85
Функция СИМВОЛ() ................................................................... 86
Функция КОДСИМВ() ................................................................. 87
4.4.2. Функции для конвертации текста ................................................ 87
Функция СТРОЧН() ..................................................................... 87
Функция ПРОПИСН() .................................................................. 87
Функция ПРОПНАЧ() .................................................................. 88
4.4.3. Форматирование текста ............................................................. 88
Функция РУБЛЬ() ....................................................................... 88
Функция ФИКСИРОВАННЫЙ() ................................................... 89
Функция ТЕКСТ() ........................................................................ 90
4.4.4. Операции с текстом .................................................................... 90
Функция СЖПРОБЕЛЫ()............................................................. 90
Функция ПЕЧСИМВ() .................................................................. 91
Функция ПОВТОР() .................................................................... 91
4.4.5. Получение подстроки ................................................................. 93
Функция ЛЕВСИМВ().................................................................. 94
Функция ПРАВСИМВ()................................................................ 94
Функция ПСТР() ......................................................................... 94
Преобразование текста к правильному формату заглавных букв 95
Формулы для конвертирования даты ......................................... 96
4.4.6. Поиск подстроки......................................................................... 96
Функции ПОИСК() и НАЙТИ()...................................................... 96
Выделение имени и фамилии из строки ..................................... 97
Выделение имени, фамилии и отчества ..................................... 98
55
5

Содержание
4.4.7. Вырезание одной строки из другой ............................................ 99
Функция ЗАМЕНИТЬ() ................................................................ 99
Функция ПОДСТАВИТЬ() .......................................................... 100
Удаление символа из строки .................................................... 100
Удаление двух различных символов из строки ......................... 101
4.5. ЛОГИЧЕСКИЕ ФУНКЦИИ В EXCEL .............................................................................101

4.5.1. Использование функции ЕСЛИ() ............................................... 102
Простейший выбор .................................................................. 102
Обработка ложного результата ................................................ 104
Предотвращение деления на ноль ........................................... 105
4.5.2. Проведение множественных логических проверок ................... 105
Использование ЕСЛИ() внутри другой функции ЕСЛИ() ............ 105
Расчет бонуса с продаж ........................................................... 106
Функция И() ............................................................................. 107
Разделение значений по категориям ....................................... 108
Функция ИЛИ() ......................................................................... 108
4.5.3. Применение условия к диапазону ............................................. 110
Операции с ячейками, удовлетворяющими некоторому
условию ................................................................................... 111
Проверка на присутствие значения в списке ............................ 112
Подсчет числа копий значения в диапазоне ............................. 113
Определение места значения в списке .................................... 114
4.6. ПОЛУЧЕНИЕ ДАННЫХ С ПОМОЩЬЮ ИНФОРМАЦИОННЫХ ФУНКЦИЙ .............................116

4.6.1. Функция ЯЧЕЙКА() .................................................................... 117
4.6.2. Функция ТИП.ОШИБКИ() .......................................................... 118
4.6.3. Функция ИНФОРМ().................................................................. 119
4.6.4. Функции Е…() ........................................................................... 120
Подсчет количества пустых ячеек в диапазоне ......................... 121
Проверка диапазона на количество нечисловых значений ....... 121
Подсчет количества ошибок в диапазоне ................................. 122
Игнорирование ошибок при работе с диапазоном ................... 122
4.7. ФУНКЦИИ ССЫЛКИ И АВТОПОДСТАНОВКИ В EXCEL ....................................................123

4.7.1. Функция ВЫБОР() ..................................................................... 124
Определение дня недели ......................................................... 125
Определение взвешенного результата опроса ......................... 125
6 6

Содержание
Интеграция функции ВЫБОР() с группой переключателей
листа ....................................................................................... 125
4.7.2. Просмотр и поиск значений в таблицах..................................... 126
Функция ВПР() ......................................................................... 126
Функция ГПР().......................................................................... 127
Расчет скидки для покупателя с использованием функций
просмотра ............................................................................... 128
Использование функций ВПР() и ГПР() с различными
таблицами ............................................................................... 129
Поиск точных значений: просмотр таблицы счетов клиентов .... 129
4.7.3. Продвинутые операции поиска ................................................. 129
Функции ПОИСКПОЗ() и ИНДЕКС() .......................................... 130
Создание поиска по строке и столбцу ...................................... 132
Поиск по множеству строк ....................................................... 132
4.8. РАБОТА С ФУНКЦИЯМИ ДАТЫ И ВРЕМЕНИ ................................................................133

4.8.1. Как Excel работает с датой и временем ..................................... 133
4.8.2. Ввод даты и времени ................................................................ 134
4.8.3. Excel и годы из двух цифр ......................................................... 135
4.8.4. Работа с функциями даты в Excel .............................................. 136
Функция СЕГОДНЯ()................................................................. 137
Функция ДАТА() ........................................................................ 138
Функция ДАТАЗНАЧ()................................................................ 138
Возврат частей даты ................................................................ 139
Функция ДЕНЬНЕД() ................................................................ 139
Функция НОМНЕДЕЛИ() ........................................................... 140
Возврат даты, отстоящей на N лет, месяцев
или дней от текущей даты ........................................................ 140
Функция РАБДЕНЬ() ................................................................. 141
Проблема при добавлении месяцев к дате ............................... 141
Функция ДАТАМЕС() ................................................................. 142
Функция КОНМЕСЯЦА() ........................................................... 142
Возврат даты N-го вхождения дня недели в месяце ................. 143
Вычисление разницы между двумя датами .............................. 145
Вычисление возраста человека................................................ 145
Функция РАЗНДАТ() ................................................................. 146
Функция ЧИСТРАБДНИ() .......................................................... 147
Функция ДНЕЙ360 ................................................................... 147
Функция ДОЛЯГОДА() .............................................................. 147
77
7

Содержание
4.8.5. Работа с функциями времени в Excel ........................................ 148
Функция ТДАТА() ...................................................................... 149
Функция ВРЕМЯ() .................................................................... 149
Функция ВРЕМЗНАЧ() .............................................................. 150
Получение частей времени ...................................................... 150
Возврат времени, отстоящего от текущего на N часов,
минут или секунд..................................................................... 150
Подсчет разницы между двумя временами .............................. 151
Подытоживая работу со временем ........................................... 152
4.9. РАБОТА С МАТЕМАТИЧЕСКИМИ ФУНКЦИЯМИ ............................................................152

4.9.1. Функции округления в Excel ...................................................... 155
Функция ОКРУГЛ().................................................................... 155
Функция ОКРУГЛТ() .................................................................. 156
Функции ОКРУГЛВНИЗ() и ОКРУГЛВВЕРХ() .............................. 157
Функции ОКРВВЕРХ() и ОКРВНИЗ() ......................................... 157
Определение того, на какой квартал приходится
указанная дата ......................................................................... 158
Функции ЧЁТН() и НЕЧЁТ() ....................................................... 158
Функции ЦЕЛОЕ() и ОТБР() ...................................................... 159
Использование округления для предотвращения ошибок
вычисления .............................................................................. 159
Установка фиксированных частей цен ...................................... 160
4.9.2. Суммирование значений .......................................................... 161
Функция СУММ() ...................................................................... 161
Подсчет общей суммы ............................................................. 161
Сложение только положительных или отрицательных чисел
диапазона ................................................................................ 161
Функция ОСТАТ() ...................................................................... 162
Улучшенная формула для подсчета разницы между двумя
временами ............................................................................... 163
Сумма всех n-х строк ............................................................... 163
Определение високосности года ............................................. 164
Создание затенения для строк ................................................. 164
4.9.3. Генерация случайных величин ................................................... 166
Функция СЛЧИС() .................................................................... 167
Генерация случайного n-значного числа................................... 167
Генерация случайных символов................................................ 168
Сортировка значений в случайном порядке ............................. 168
8 8

Содержание
Функция СЛУЧМЕЖДУ() ........................................................... 170
4.10. РАБОТА СО СТАТИСТИЧЕСКИМИ ФУНКЦИЯМИ .........................................................170

4.10.1. Подсчет количества с помощью функции СЧЁТ() ..................... 172
4.10.2. Подсчет средних значений ...................................................... 172
Функция СРЗНАЧ() ................................................................... 173
Функция МЕДИАНА() ................................................................ 173
Функция МОДА() ...................................................................... 174
Подсчет взвешенного среднего ............................................... 174
4.10.3. Вычисление крайних значений ................................................ 176
Функции МАКС() и МИН() ......................................................... 176
Функции НАИБОЛЬШИЙ() и НАИМЕНЬШИЙ() .......................... 177
Проведение вычислений для верхних K значений .................... 177
4.10.4. Нахождение меры отклонений ................................................ 178
Вычисление диапазона ............................................................ 178
Подсчет дисперсии с помощью функции ДИСП() ..................... 179
Расчет стандартного отклонения функцией СТАНДОТКЛОН()... 180
Функция ЧАСТОТА() ................................................................. 180
4.11. ФИНАНСОВЫЕ РАСЧЕТЫ ......................................................................................182

4.11.1. Расчет суммы платежа ............................................................ 182
Расчет кредита с использованием функции ПЛТ() .................... 182
«Шаровой» кредит ................................................................... 183
Расчет стоимости кредита ....................................................... 184
4.11.2. Вычисление погашения основной суммы и процентов ............ 184
4.11.3. Вычисление общей стоимости погашения основной части
кредита и процентов ................................................................ 185
4.11.4. Подсчет процентной ставки .................................................... 187
4.11.5. Расчет возможной суммы кредита .......................................... 188
4.11.6. Расчет процентов ................................................................... 189
О сложном проценте ................................................................ 189
Номинальный и эффективный процент .................................... 189
Переход от номинальной ставки к эффективной и наоборот .... 190
4.11.7. Расчет будущей суммы ........................................................... 191
Расчет будущей суммы после одиночного инвестирования...... 191
Расчет будущей суммы серии депозитов ................................. 193
4.11.8. Расчет требуемых условий инвестирования ............................ 193
99
9

Содержание
Вычисление процентной ставки ............................................... 193
Расчет количества периодов .................................................... 195
Расчет требуемого периодического взноса ............................. 196
Расчет требуемого начального взноса ..................................... 196
Расчет будущей суммы при изменяющейся процентной ставке 197
4.11.9. Расчет инфляции .................................................................... 199
Введение инфляции в расчет ................................................... 199
Расчет текущей стоимости с помощью функции ПС() ............... 200

ЧАСТЬ II. ПОСТРОЕНИЕ ДИАГРАММ. АНАЛИЗ ДАННЫХ С
ПОМОЩЬЮ ДИАГРАММ И ГРАФИКОВ
ГЛАВА 5. ПОСТРОЕНИЕ ГРАФИКОВ И ДИАГРАММ В EXCEL 2010 ................... 203
5.1. БАЗОВЫЕ ВОЗМОЖНОСТИ ....................................................................................204

5.1.1. Первый график в Excel 2010 ...................................................... 205
5.1.2. Выбор несмежных данных ........................................................ 207
5.1.3. Быстрое создание диаграммы .................................................. 208
5.1.4. Перемещение диаграммы ........................................................ 209
5.1.5. Выбор способа отображения данных ........................................ 210
Группировка данных................................................................. 211
Изменение порядка строк и столбцов ...................................... 211
5.2. МАКЕТЫ И СТИЛИ ДИАГРАММ. ВКЛАДКА «МАКЕТ» .....................................................211

5.2.1. Раздел «Подписи» ..................................................................... 214
Название диаграммы ............................................................... 214
Названия осей ......................................................................... 217
Легенда ................................................................................... 219
Подписи данных ....................................................................... 220
Таблица данных........................................................................ 221
5.2.2. Раздел «Оси», кнопка «Оси» ...................................................... 222
Горизонтальная ось .................................................................. 222
Параметры горизонтальной оси ............................................... 222
Параметры вертикальной оси .................................................. 226
5.2.3. Раздел «Оси», кнопка «Сетка» ................................................... 227
5.2.4. Раздел «Фон» ............................................................................ 228
Область построения................................................................. 228
1010

Содержание
Стенка диаграммы ................................................................... 228
Основание диаграммы ............................................................. 229
Поворот объемной фигуры ...................................................... 229
5.2.5. Раздел «Анализ» ....................................................................... 229
Линия тренда ........................................................................... 229
Линии ...................................................................................... 232
Полосы повышения/понижения ............................................... 233
Планки погрешности ................................................................ 233
5.3. МАКЕТЫ И СТИЛИ ДИАГРАММ. ВКЛАДКА ФОРМАТ .....................................................235

5.3.1. Панель Стили фигур.................................................................. 236
5.3.2. Панель Стили WordArt ............................................................... 238
5.3.3. Панели “Упорядочить” и “Размер”............................................. 240

ГЛАВА 6. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ ДИАГРАММ И ГРАФИКОВ ............. 243
6.1. ПРИМЕР №1. АНАЛИЗ СПРОСА ...............................................................................244

6.1.1. Решение №1. Использование гистограммы .............................. 245
6.1.2. Решение №2. Использование линейчатой диаграммы .............. 245
6.1.3. Решение №3. Использование круговой диаграммы .................. 246
6.1.4. Итоги ........................................................................................ 247
6.2. ПРИМЕР №2. СРАВНЕНИЕ ДАННЫХ ПО ВЫБОРКАМ ....................................................248

6.2.1. Решение №1. Использование гистограммы с группировкой по
видам продукции ..................................................................... 248
6.2.2. Решение №2. Использование гистограммы с накоплением ...... 250
6.2.3. Итоги ....................................................................................... 251
6.3. ПРИМЕР №3. ВЫЯВЛЕНИЕ ОПРЕДЕЛЕННЫХ ДАННЫХ .................................................251

6.3.1. Решение №1. Использование круговой диаграммы .................. 252
6.3.2. Решение №2. Использование контрастного цвета .................... 253
6.3.3. Итоги ........................................................................................ 254
6.4. ПРИМЕР №4. СРАВНЕНИЕ ДАННЫХ В ГРАФИЧЕСКОМ ВИДЕ .........................................255

6.4.1. Решение .................................................................................. 255
6.5. ПРИМЕР №5. АНАЛИЗ ДОЛЕЙ .................................................................................257

6.5.1. Решение ................................................................................... 259

11
11
11

Содержание
6.6. ПРИМЕР №6. ОПРЕДЕЛЕНИЕ ДОЛЕЙ МАЛЫХ КОМПАНИЙ ............................................260
6.7. ПРИМЕР №7. АНАЛИЗ СВЯЗИ МЕЖДУ ДВУМЯ ФАКТОРАМИ .........................................264
6.8. ПРИМЕР №8. АНАЛИЗ ДОПОЛНИТЕЛЬНОГО РЯДА ДАННЫХ .........................................266
6.9. ПРИМЕР №9. ВЫЯВЛЕНИЕ ЗАКОНА РАСПРЕДЕЛЕНИЯ ДАННЫХ ....................................267
6.10. ПРИМЕР №10. ПОСТРОЕНИЕ ГРАФИКА ФУНКЦИИ.....................................................269
6.11. ПРИМЕР №11. АНАЛИЗ ГРУПП ПОТРЕБИТЕЛЕЙ ........................................................273
6.12. ПРИМЕР №12. ОТОБРАЖЕНИЕ ДВУХ ВИДОВ ПОКАЗАТЕЛЕЙ .......................................275
6.13. ПРИМЕР №13. АНАЛИЗ ДОЛИ ДОХОДА ПРЕДПРИЯТИЯ .............................................278
6.14. ПРИМЕР №14. АНАЛИЗ ДАННЫХ ПО ТРЕМ ПАРАМЕТРАМ ...........................................281
6.15. ПРИМЕР №15. АНАЛИЗ ДАННЫХ ПО НЕСКОЛЬКИМ ПАРАМЕТРАМ ...............................284
6.16. ПРИМЕР №16. АНАЛИЗ РАСПРЕДЕЛЕНИЯ ТЕМПЕРАТУРЫ ..........................................286
6.17. ВЫВОДЫ ...........................................................................................................286

ВЫВОДЫ.................................................................................................. 287
6.18. ПОСТРОЕНИЕ ГРАФИКА ФУНКЦИИ ДВУХ ПЕРЕМЕННЫХ ............................................288

ЧАСТЬ III. СВОДНЫЕ ТАБЛИЦЫ. АНАЛИЗ
И ОРГАНИЗАЦИЯ ДАННЫХ С ПОМОЩЬЮ
СВОДНЫХ ТАБЛИЦ
ГЛАВА 7. АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ ...................... 290
7.1. ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ С ПОМОЩЬЮ МАСТЕРА ...........................................291

7.1.1. Шаг №1. Выбор источника данных ............................................ 292
7.1.2. Шаг №2. Выбор диапазона данных ........................................... 293
7.1.3. Шаг №3. Вставка сводной таблицы ........................................... 294
7.2. ПОСТРОЕНИЕ СВОДНОЙ ТАБЛИЦЫ С ПОМОЩЬЮ КОМАНДЫ ВКЛАДКИ «ВСТАВКА» .........294
7.3. ПРИМЕР ПОСТРОЕНИЯ СВОДНОЙ ТАБЛИЦЫ .............................................................294
7.4. НАСТРОЙКА СВОДНОЙ ТАБЛИЦЫ ............................................................................296

7.4.1. Настройка параметров поля ..................................................... 299
7.5. ВКЛАДКИ ДЛЯ РАБОТЫ СО СВОДНЫМИ ТАБЛИЦАМИ .................................................299

1212
12

Содержание
7.5.1. Вкладка «Параметры» ............................................................... 299
Панель «Сводная таблица» ....................................................... 300
Панель «Активное поле» ........................................................... 300
Панель «Группировать» ............................................................ 301
Панель «Сортировка» ............................................................... 301
Панель «Данные» ..................................................................... 302
Панель «Действия»................................................................... 302
Панель «Сервис» ...................................................................... 302
Панель «Показать или скрыть» ................................................. 303
7.5.2. Вкладка «Конструктор» ............................................................. 303
Панель «Макет» ........................................................................ 303
Панель «Параметры стилей сводной таблицы» ......................... 305
Панель «Стили сводной таблицы» ............................................ 305
7.5.3. Параметры сводной таблицы .................................................... 307
Вкладка «Разметка и формат» .................................................. 307
Вкладка «Итоги и фильтры» ...................................................... 308
Вкладка «Вывод» ...................................................................... 308
Вкладка «Печать» ..................................................................... 309
Вкладка «Данные» .................................................................... 309
7.6. СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ ИЗ НЕСКОЛЬКИХ ДИАПАЗОНОВ .....................................310

7.6.1. Создание сводной таблицы на основе нескольких диапазонов . 310
7.6.2. Объединение исходных данных вручную ................................... 312
7.7. ГРУППИРОВКА ДАННЫХ В СВОДНОЙ ТАБЛИЦЕ ..........................................................314

7.7.1. Развертывание данных в сводной таблице................................ 316
7.7.2. Группировка числовых данных по интервалам .......................... 319

ГЛАВА 8. СВОДНЫЕ ДИАГРАММЫ .......................................................... 325
8.1. ПОСТРОЕНИЕ СВОДНОЙ ДИАГРАММЫ НА ОСНОВЕ ТАБЛИЦЫ С ИСХОДНЫМИ
ДАННЫМИ ..................................................................................................326
8.2. ПОСТРОЕНИЕ СВОДНОЙ ДИАГРАММЫ НА ОСНОВЕ СВОДНОЙ ТАБЛИЦЫ ........................328

ГЛАВА 9. СТАТИСТИЧЕСКИЕ РАСЧЕТЫ В EXCEL ......................................... 334

13
13
13

ЧАСТЬ I.
ВЫЧИСЛЕНИЯ, ФОРМУЛЫ И ФУНКЦИИ
EXCEL 2010

ГЛАВА 1.
СОЗДАНИЕ БАЗОВЫХ ФОРМУЛ

1414

Все формулы в Excel имеют следующую базовую структуру: они начинаются со знака равно (=), следом идут операнды - значения, ссылки на ячейки,
диапазоны и их имена, названия функций – и все это может быть разделено
операторами – символами, которые сочетают операнды определенным образом, такими как знак плюса (+) или знак больше (>).
ЗАМЕЧАНИЕ.
Ничего страшного, если вы будете использовать пробелы между операторами
или операндами. Напротив, это повысит читаемость документа. Вы также можете
перенести часть формулы на другую строку, используя сочетание клавиш «Alt» +
«Enter».

1.1. ÎÃÐÀÍÈ×ÅÍÈß ÄËß ÔÎÐÌÓË Â EXCEL 2010
Несмотря на то что маловероятно превышение этих ограничений, неплохо
быть всегда в курсе и знать возможные пределы. Все ограничения представлены в следующей таблице:
Таблица 1.1. Ограничения формул в Excel 2010
Объект

Новый максимум

Старое ограничение (до
Excel 2007)

Столбцы

16 384

1 024

Строки

1 048 576

65 536

Длина функции (символов)

8 192

1 024

Аргументы функции

255

30

15
15
15

Часть I. Вычисления, формулы, функции

Объект
Уровень вложения функций
Ссылки

Новый максимум

Старое ограничение (до
Excel 2007)

64

7

Неограниченно

65 335

Столбцы сводной таблицы

16 384

255

Строки сводной таблицы

1 048 576

65 536

Поля в сводной таблице

16 384

255

Уникальных элементов
в сводной таблице

1 048 576

3268

1.2. ÂÂÎÄ È ÐÅÄÀÊÒÈÐÎÂÀÍÈÅ ÔÎÐÌÓË
Ввод формулы на рабочий лист достаточно прост и прямолинеен:
1. Выберите ячейку для ввода формулы.
2. Введите знак равенства (=) для указания Excel о том, что вы будете
вводить формулу.
3. Введите все операнды и операторы формулы.
4. Нажмите «Enter» для подтверждения формулы.
При этом в Excel имеется три механизма ввода, в соответствии с которыми
программа интерпретирует ваши действия:

1616



Когда вы вводите знак равенства (=) для начала формулы, Excel переходит в режим ввода, который используется вами для ввода операторов и операндов формулы.



Если вы нажмете на любую из клавиш навигации клавиатуры (это
все клавиши со стрелками, а также «Page Up», «Page Down», «Home»
и «End») или если вы кликнете мышью на любую другую ячейку,
Excel переходит в режим указания. В данном режиме вы выбираете
ячейки или диапазоны как операнды. Excel возвращается в режим
ввода, когда вы введете с клавиатуры любой символ.



При нажатии на «F2» Excel переходит в режим редактирования. В
данном режиме вы можете производить изменения в формуле. К
примеру, вы можете использовать клавиши «» и «» клавиатуры
для перемещения по различным частям формулы для удаления или

Глава 1. Создание базовых формул

вставки символов. Также в этот режим вы можете войти, дважды
кликнув на ячейке с формулой или просто внутри формулы. Нажмите «F2» для возврата в режим ввода.
ПРИМЕЧАНИЕ.
Вы можете увидеть, в каком режиме находитесь, просто посмотрев в левом нижнем углу программы на строку состояния.

1.3. ÈÑÏÎËÜÇÎÂÀÍÈÅ ÎÑÍÎÂÍÛÕ ÎÏÅÐÀÖÈÉ Â ÔÎÐÌÓËÀÕ

1.3.1. АРИФМЕТИЧЕСКИЕ ФОРМУЛЫ
Арифметические формулы являются наиболее общим видом формул. Они
работают с числами, адресами ячеек и возвращают результат в зависимости
от использованного математического оператора.
Таблица 1.2. Арифметические операторы
Оператор

Название оператора

Пример

+

Сложение

=10+5

Результат
15

-

Вычитание

=10-5

5

-

Отрицание

=-10

-10

*

Умножение

=10*5

50

/

Деление

=10/5

2

%

Взятие процентов

=10%

0.1

^

Возведение в степень

=10^5

100000

1.3.2. ОПЕРАТОРЫ СРАВНЕНИЯ
Формулы сравнения — это формулы, которые позволяют сравнить два (или
более) числа, строки текста, содержимое ячеек или результаты функций.
Если результат сравнения положительный, результат выполнения формулы
будет представлен в виде логического выражения ИСТИНА (что эквивалентно любому ненулевому значению). Если результат сравнения ложный,
формула возвращает логическое значение ЛОЖЬ (что эквивалентно 0).
Таблица 1.3. Операторы сравнения
Оператор

Название оператора

Пример

Результат

=

Равно

=10=5

ЛОЖЬ

>

Больше

=10>5

ИСТИНА

<

Меньше

=10=

Больше или равно

=”a”>=”b”

ЛОЖЬ

17
17
17

Часть I. Вычисления, формулы, функции
Оператор

Название оператора

Пример

Результат

=”b”

ИСТИНА



Не равно

=”a””b”

ИСТИНА

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

1.3.3. ИСПОЛЬЗОВАНИЕ ТЕКСТОВЫХ ФОРМУЛ
Текстовые формулы представляют собой формулы, возвращающие
текстовые строки в результате своей работы. Текстовые формулы
используют оператор & (амперсанд) для работы с текстовыми ячейками,
текстовыми строками, заключенными в кавычки, и результатами работы
других текстовых формул.
Одним из способов работы с текстовыми формулами является связывание
(объединение) строк текста. Например, если вы введете в поле формулы
ячейки =”soft ”&”ware”, Excel выведет software. При этом значки
цитаты и амперсанд не выводятся в итоговый результат. Также вы можете
использовать оператор & для связывания ячеек, содержащих текст.
Например, если ячейка A1 содержит текст Hello, а ячейка А2 текст World!, то
ввод формулы =A1&” my “&A2 вернет результат Hello my World!

1.3.4. ОПЕРАТОРЫ ССЫЛОК
Операторы ссылок позволяют создавать диапазоны ячеек из отдельных
ячеек или других диапазонов:
Таблица 1.4. Операторы ссылок
Оператор

Название оператора

Описание

: (двоеточие)

Диапазон

Создает диапазон из двух ссылок
на ячейки (например, A1:B6)

Пересечение

Создает диапазон из пересечений
двух диапазонов (например, A1:
B6 B4:C7)

Объединение

Создает диапазон как объединение двух диапазонов (например,
A1:B6 ; B4:C7)

(пробел)

; (точка с запятой)

1818

Глава 1. Создание базовых формул

1.4. ÍÅÑÊÎËÜÊÎ ÑËΠΠÏÎÐßÄÊÅ ÂÛÏÎËÍÅÍÈß ÎÏÅÐÀÒÎÐÎÂ
 ÔÎÐÌÓËÅ
Конечно, чаще всего используются простые формулы, содержащие всего два значения и оператор. Но на практике большинство формул, которые вам предстоит использовать, могут содержать гораздо большее число
переменных и операторов. При этом критически важным становится порядок использования операторов в таких формулах. Простой пример, иллюстрирующий важность порядка вычислений, — это формула =3+5^2. Если
вы просто вычислите формулу слева направо, результатом станет число 64
(3+5 равно 8, при возведении числа 8 в степень 2 получается 64). Однако
если сначала выполнить возведение в степень, результатом будет число 28
(5 в степени 2 равно 25, после чего 25+3 равно 28). Как показывает этот простой пример, даже несложная формула может возвращать несколько ответов в зависимости от порядка вычисления операторов.
Для того чтобы решить эту проблему, Microsoft Excel вычисляет формулу,
отталкиваясь от заранее определенного порядка. Этот порядок позволяет
программе вычислять формулы, просчитывая, какие части должны выполняться в первую очередь, какие далее за ней и так далее.
Таблица 1.5. Порядок вычисления операторов
Оператор

Выполняемая операция

Порядок вычисления

:

Диапазон

1-й



Пересечение

2-й

,

Объединение

3-й

-

Отрицание

4-й

%

Взятие процента

5-й

^

Возведение в степень

6-й

*и/

Умножение и деление

7-й

+и-

Сложение и вычитание

8-й

&

Сцепление

9-й

= > < >= 1000]”Ошибка! Число >1000”;[1000 используется для проверки. Допустим, вы
добавите данную формулу в ячейку B1. Если логическое выражение является истинным (оно является таковым, когда число в А1 больше 1000), функция возвращает значение «много», и именно это вы увидите в ячейке В1 (в
случае если А1 меньше 1000, вы увидите значение ЛОЖЬ).
102
102

Глава 4. Полезные функции Excel 2010

Другое простое применение функции — проверка для вывода предупреждений. Например, если у вас имеется книга с результатами увеличения продаж
различных товаров в процентах. Будет полезно отметить определенным образом товары с уменьшившимися продажами. Базовая формула для такой
отметки представляет собой следующее:
=ЕСЛИ(ячейка < 0; отметка)

Здесь ячейка – адрес ячейки для проверки, а отметка – определенного
рода текст для вывода на экран для указания на отрицательное значение.
Вот пример:
=ЕСЛИ(B1 < 0; “10000. Если же при этом
А1 меньше или равно 10000, возвращается значение «много». Если же при
самой первой проверке число А1 будет меньше 1000, выведется значение
«мало».
Обратите внимание, что с таким же успехом вы можете запустить вторую
проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции ЕСЛИ()). Вот небольшой пример, возвращающий
значение «очень мало», когда число в А1 меньше 100:
=ЕСЛИ(A1>1000; “много”; ЕСЛИ(A10; ЕСЛИ(E3>0.1; 10000; 1000); 0)

Если значение в E3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он
10%, и в зависимости от этого выдается 1 000 или 10 000.
Рис. 4.17 показывает пример работы формулы.
106
106

Глава 4. Полезные функции Excel 2010

Рис. 4.17. Пример расчета бонуса с продаж

ФУНКЦИЯ И()
Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только
когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или
оба условия), премия не выплачивается. В булевой логике это называется
логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.
В Excel выражения логического И обрабатываются с помощью функции
И():
И (логическое_значение1; логическое_значение2; ...)

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


Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.



Если один или более аргументов возвращают ЛОЖЬ (или 0), И()
возвращает ЛОЖЬ.

Чаще всего И() применяется внутри функции ЕСЛИ(). В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по
107
107
107

Часть I. Вычисления, формулы, функции

своей ветке значение_если_истина. Если одно или более из выражений в
И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.
Вот небольшой пример:
=ЕСЛИ(И(C2 > 0; B2 > 0); 1000; “нет бонуса”)

Если значение в В2 будет больше нуля и значение в С2 будет больше нуля,
формула вернет 1000, в противном случае выведется строка «нет бонуса».

РАЗДЕЛЕНИЕ ЗНАЧЕНИЙ ПО КАТЕГОРИЯМ
Полезным применением функции И() является разделение по категориям в
зависимости от значения. Например, у вас имеется таблица с результатами
какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34, 35-49,
50-64, 65 и более. Предполагая, что возраст респондента находится в ячейке
В9, следующие аргументы функции И() проводят логическую проверку на
принадлежность возраста диапазону:
=И(B9 >= 18; B9 = 18; B9 =35;B9=50;B9=65; C9; “”)

ФУНКЦИЯ ИЛИ()
Существуют ситуации, когда вам необходимо решение, только когда одно
из условий принимает положительное значение (ИСТИНА). Например, вы
можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В
булевой логике это называется логическим ИЛИ.
Такие условия проверяются в Excel с помощью функции ИЛИ():
ИЛИ (логическое_значение1; логическое_значение2;...)

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

Глава 4. Полезные функции Excel 2010

Рис. 4.18. Разделение значений по категориям

Результат работы ИЛИ() зависит от следующих условий:


Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.



Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.

Так же как и И(), чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ(). В таком случае, когда один из аргументов внутри ИЛИ() вернет
ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина.
Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по
ветке значение_если_ложь. Вот небольшой пример:
=ЕСЛИ(ИЛИ(C2 > 0; B2 > 0); 1000; “нет бонуса”)

В случае когда в одной из ячеек (С2 или В2) будет положительное число,
функция вернет 1000. Только когда оба значения будут отрицательны (или
равны нулю), функция вернет строку «нет бонуса».
109
109
109

Часть I. Вычисления, формулы, функции

4.5.3. ПРИМЕНЕНИЕ УСЛОВИЯ К ДИАПАЗОНУ
Использование логического И() в качестве аргумента функции ЕСЛИ() целесообразно, только когда применяется три или четыре аргумента. После
чего формула становится слишком запутанной. Если вы постоянно применяете одинаковые проверки для различных ячеек, лучшим решением будет
применение диапазона и задание формулы в качестве массива.
Например, вы хотите сложить значения в ячейках B3:B7, но только если все
они содержат значения больше 0. Вот формула-массив для такого случая:
{=ЕСЛИ(И(B3:B7>0); СУММ(B3:B7); “”)}

(не забывайте, что вводить внешние фигурные скобки не следует, для введения формулы-массива применяется сочетание клавиш Ctrl+Shift+Enter).
Это полезно применять на листах, где, возможно, еще находятся не все данные и вы не хотите вычислять итоговую цифру, пока они не будут заполнены. Данный пример показан на рис. 4.19.
Здесь формула-массив в ячейке B8 аналогична рассмотренной выше. Формула в B16 не возвращает результат, поскольку ячейка B14 на данный момент пустая.

Рис. 4.19. Применение условия к диапазону с помощью функции-массива

110
110

Глава 4. Полезные функции Excel 2010

ОПЕРАЦИИ С ЯЧЕЙКАМИ, УДОВЛЕТВОРЯЮЩИМИ НЕКОТОРОМУ УСЛОВИЮ
Рассмотрим сценарий, когда вы хотите применить действие к диапазону, но
только к тем ячейкам, которые удовлетворяют заданному условию. Например, чтобы происходило суммирование только положительных чисел. Для
того чтобы сделать это, вы должны вынести операцию за пределы функции
ЕСЛИ(). Например, вот формула для суммирования только положительных
чисел определенного диапазона:
=СУММ(ЕСЛИ(B3:B7>0; B3:B7;0))

В данном случае функция ЕСЛИ() возвращает массив адресов ячеек с положительными значениями.
Более сложным примером является таблица с данными о затратах в различные годы. Было бы очень полезно упорядочить данные значения и выдать
сумму затрат за каждый год. На рис. 4.20 показан лист с решением такой
задачи.
Даты будущих расходов находятся в столбце В, а суммы расходов — в столбце С. Годы расходования присутствуют в столбце F. Для вычисления общей
суммы расходов на определенный год используется следующая формуламассив:

Рис. 4.20. Пример операции над ячейками, удовлетворяющими заданному условию

111
111
111

Часть I. Вычисления, формулы, функции

{ =СУММ(ЕСЛИ(ГОД( $B$4 : $B$23 ) = F4; $C$4 : $C$23; 0))}

Функция ЕСЛИ() сравнивает значение ГОД для каждой ячейки диапазона
$B$4 : $B$23. В случае если он равен текущему году в ячейке F4, возвращается соответствующее значение в колонке С, иначе возвращается 0. Функция СУММ() просто складывает возвратившиеся значения.

ПРОВЕРКА НА ПРИСУТСТВИЕ ЗНАЧЕНИЯ В СПИСКЕ
Предположим, у вас имеется список постоянных покупателей. С помощью
логических функций вы можете узнать, присутствует ли некоторый покупатель в этом списке.
Общая формула на проверку наличия значения в списке выглядит так:
{=ИЛИ(значение = диапазон)}

Здесь
значение – величина, которую вы ищете,
диапазон – диапазон ячеек для поиска.
Рис. 4.21 демонстрирует данную формулу в действии. В ячейку В1 вносится
адрес покупателя, а в ячейке В2 отображается логическое значение – имеется ли покупатель в списке.

Рис. 4.21. Поиск в списке клиентов

112
112

Глава 4. Полезные функции Excel 2010

Вот формула для ячейки В2:
{=ИЛИ(B1 = B9:B13)}

Данная формула-массив проверяет все ячейки диапазона В9:В13 и определяет, есть ли среди них равные по значению ячейке В1. Если будет хотя бы
одно совпадение, формула вернет значение ИСТИНА, что означает, что покупатель есть в списке.
Подсказка. Существует также формула, аналогичная приведенной выше, но
построенная на логическом И():
=И(B1 B9:B13)

Но в данном случае она проверяет, нет ли покупателя в списке. Если значение в В1 не совпадет ни с одной из ячеек диапазона, вернется значение ИСТИНА, что означает, что покупателя нет в списке.

ПОДСЧЕТ ЧИСЛА КОПИЙ ЗНАЧЕНИЯ В ДИАПАЗОНЕ
Теперь вы знаете, как посмотреть, есть ли значение в списке, но что если вам
нужно знать, сколько именно раз оно туда входит? Следующая формула решает данную задачу:
{=СУММ(ЕСЛИ(значение = диапазон; 1; 0))

Опять же,
значение – искомая величина,
диапазон – ячейки для поиска.
Функция И() сравнивает значение с ячейками из диапазона. Совпадающие
значения возвращают 1, остальные 0. Функция СУММ() складывает все возвратившиеся значения.
Вот формула для подсчета количества покупок:
{=СУММ(ЕСЛИ(B1= B9:B17; 1; 0))}

В действии формула представлена на рис. 4.22.
ПРИМЕЧАНИЕ.
С помощью данной техники можно сравнивать не только одно значение со значениями диапазона, но и два диапазона между собой (они должны содержать одинаковое
число строк и столбцов). Для этого используйте следующую базовую формулу:

{=СУММ(ЕСЛИ(Диапазон1 = Диапазон2; 1; 0))}

При этом происходит поочередная проверка соответствующих ячеек двух
диапазонов. Сумма показывает количество совпадений в ячейках диапазонов.
113
113
113

Часть I. Вычисления, формулы, функции

Рис. 4.22. Подсчет количества вхождений в диапазон

ОПРЕДЕЛЕНИЕ МЕСТА ЗНАЧЕНИЯ В СПИСКЕ
Что, если вы хотите знать не только, сколько раз значение появляется в
списке, но и конкретно где оно там находится? Вы можете сделать это, проведя проверку с помощью функции И() и возвращая номер строки, где присутствует вхождение:
=ЕСЛИ(значение = диапазон; СТРОКА(диапазон); “”)

Когда значение совпадет со значением одной из ячеек диапазона, функция
ЕСЛИ() использует функцию СТРОКА() для возврата значения. В противном
случае вернется пустой текст.
Для возврата номера строки можно использовать функции МИН() и МАКС(),
которые возвращают минимальное и максимальное значения из всего числа
результатов. Вот их применение:


Для возврата строки самого первого вхождения применяется функция
{=МИН(ЕСЛИ(значение = диапазон; СТРОКА(диапазон); “”))}

114
114

Глава 4. Полезные функции Excel 2010



Для
го

возврата
вхождения

строки
самого
применяется

последнефункция

{=МАКС(ЕСЛИ(значение = диапазон; СТРОКА(диапазон); “”))}

Вот конкретные формулы для нахождения данных значений из предыдущего примера:
{=МИН(ЕСЛИ(B1=B9:B17; СТРОКА(B9:B17); “”))}
{=МАКС(ЕСЛИ(B1=B9:B17; СТРОКА(B9:B17); “”))}

На рис. 4.23 показан конечный результат.
В ячейках B4 и B5 находятся результаты вычисления первой и последней
строки вхождения значения из В1.
Примечание. Также возможно получение непосредственно адреса первой
или последней ячейки вхождения. Для этого используйте функцию АДРЕС(), которая возвращает абсолютный адрес из столбца и строки:

Рис. 4.23. Определение места значения в списке

{ =АДРЕС(МИН(ЕСЛИ(B1=B9:B17; СТРОКА(B9:B17); “”)); СТОЛБЕЦ(B9:
B17))}
{ =АДРЕС(МАКС(ЕСЛИ(B1=B9:B17; СТРОКА(B9:B17); “”));СТОЛБЕЦ(B9:
B17))}
115
115
115

Часть I. Вычисления, формулы, функции

4.6. ÏÎËÓ×ÅÍÈÅ ÄÀÍÍÛÕ Ñ ÏÎÌÎÙÜÞ ÈÍÔÎÐÌÀÖÈÎÍÍÛÕ
ÔÓÍÊÖÈÉ
Информационные функции в Excel (см. табл. 4.3) возвращают различные
данные, касающиеся ячеек, листов и функций.
Таблица 4.3. Информационные функции Excel
Функция и ее синтаксис

Краткое описание работы

ЯЧЕЙКА (тип_информации;

Возвращает информацию о форматировании, положении или содержимом левой верхней ячейки в
ссылке

ссылка)

ТИП.ОШИБКИ (значение_ошибки)

Возвращает номер, соответствующий одному из
возможных значений ошибок в Microsoft Excel, или
значение ошибки #Н/Д, если ошибка отсутствует

ИНФОРМ (тип_информации)

Возвращает информацию о текущей операционной
среде

ЕПУСТО (значение)

Возвращает ИСТИНА, если значение ссылается на
пустую ячейку

ЕОШ (значение)

Возвращает ИСТИНА, если значение содержит любую ошибку, кроме #Н/Д

ЕОШИБКА (значение)

Возвращает ИСТИНА, если значение содержит любую ошибку

ЕЛОГИЧ (значение)

Возвращает ИСТИНА, если аргумент ссылается на
логическое значение

ЕНД (значение)

Возвращает ИСТИНА, если значение является
ошибкой #Н/Д (данные недоступны)

ЕНЕТЕКСТ (значение)

Возвращает ИСТИНА, если значение не является
текстом

ЕЧИСЛО (значение)

Возвращает ИСТИНА, если значение является числом

ЕССЫЛКА (значение)

Возвращает ИСТИНА, если значение ссылается на
ссылку

ЕТЕКСТ (значение)

Возвращает ИСТИНА, если значение является текстом или ссылается на текст

ЕЧЁТН (число)

Возвращает ИСТИНА, если значение является четным числом

ЕНЕЧЁТ (число)

Возвращает ИСТИНА, если значение является нечетным числом

Ч (значение)

Возвращает значение, преобразованное в число

ТИП (значение)

Возвращает тип значения

116
116

Глава 4. Полезные функции Excel 2010

4.6.1. ФУНКЦИЯ ЯЧЕЙКА()
ЯЧЕЙКА() – одна из полезнейших информационных функций. Ее работа за-

ключается в возврате информации о какой-либо ячейке:
ЯЧЕЙКА (тип_информации; ссылка)

где
тип_информации – строка, указывающая на необходимую вам
информацию,
ссылка – ссылка на ячейку для получения информации (по умолчанию сюда подставляется ячейка, в которой находится данная
функция ЯЧЕЙКА(); в случае указания диапазона будет дана информация по верхней левой ячейке диапазона).
Табл. 4.4 показывает варианты строк для аргумента тип_информации
Таблица 4.4. Типы первого аргумента функции ЯЧЕЙКА()
Значение аргумента тип_
информации

Что возвращает функция ЯЧЕЙКА()

“адрес”

Абсолютный адрес ячейки в виде текста

“защита”

0, если ячейка не заблокирована, в противном случае 1

“имяфайла”

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

“префикс”

ЯЧЕЙКА() вернет

Левый край



Центр

^

Правый край



Заполнение

/

“скобки”

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

“содержимое”

Содержимое ячейки

“столбец”

Номер столбца ячейки

“строка”

Номер строки ячейки
Возвращаемый символ служит для определения типа данных в ячейке:

“тип”

Тип ячейки

ЯЧЕЙКА() вернет

Текстовый

l

Пустой
Все остальные

b
v

117
117
117

Часть I. Вычисления, формулы, функции
Возвращается символ в зависимости от типа встроенного Excel-формата, применяемого в ячейке:
Встроенный формат

“формат”

ЯЧЕЙКА() вернет

Общий

“G”

0

“F0”

# ##0

“,0”

0,00

“F2”

# ##0,00

“,2”

$# ##0_);($# ##0)

“C0”

$# ##0_);[Красный]($# ##0)

“C0-”

$# ##0,00_);($# ##0,00)

“C2”

$# ##0,00_);[Красный]($# ##0,00)

“C2-”

0%

“P0”

0,00%

“P2”

0,00E+00

“S2”

# ?/? или # ??/??

“G”

д.М.гг или дд.ММ.гг Ч:мм или дд.ММ.гг

“D4”

Д МММ ГГ или ДД МММ ГГ

“D1”

д.м, или дд.ммм, или Д МММ

“D2”

ммм.гг, ммм.гггг, МММ ГГ или МММ ГГГГ

“D3”

дд.мм

“D5”

ч:мм AM/PM

“D7”

ч:мм:сс AM/PM

“D6”

ч:мм

“D9”

ч:мм:сс

“D8”

“цвет”

Вернется 1 в случае, если формат ячейки использует цвета для указания отрицательных значений; 0 в противном случае

“ширина”

Ширина ячейки, округленная до ближайшего целого числа

4.6.2. ФУНКЦИЯ ТИП.ОШИБКИ()
ТИП.ОШИБКИ() возвращает значение в зависимости от возвращаемой Excel

ошибки:
ТИП.ОШИБКИ (значение_ошибки)

где
значение_ошибки – ссылка на ячейку, которую вы хотите проверить на наличие ошибок. Возможные результаты см. в табл. 4.5.
118
118

Глава 4. Полезные функции Excel 2010

Таблица 4.5. Коды, возвращаемые функцией ТИП.ОШИБКИ()
Значение_ошибки

ТИП.ОШИБКИ() вернет

#ПУСТО!

1

#ДЕЛ/0!

2

#ЗНАЧ!

3

#ССЫЛ!

4

#ИМЯ?

5

#ЧИСЛО!

6

#Н/Д

7

Любое другое

#Н/Д

Чаще всего ТИП.ОШИБКИ() используется для перехвата ошибки и вывода на
экран более дружественного сообщения. Вы можете сделать это, используя
функцию ЕСЛИ(), проверяя, что вернет ТИП.ОШИБКИ(). Если результат будет
меньше или равен 7, ячейка содержит ошибку. Поскольку ТИП.ОШИБКИ() возвращает значения от 1 до 7, вы можете применить функцию ВЫБОР() для показа конкретного сообщения, зависящего от типа ошибки.
Вот формула для показа различных сообщений, зависящих от типа ошибки:
=ЕСЛИ(ТИП.ОШИБКИ(A1) СЕГОДНЯ())

ФУНКЦИЯ РАЗНДАТ()
Очень интересная функция, которая почему-то потерялась в справках и пояснениях Excel при русификации. Но, тем не менее, при правильном вводе
аргументов она продолжает работать. Надеемся, что эта проблема будет устранена в дальнейших версиях.
Данная функция вычисляет разницу между двумя вводимыми датами на
основе заданного аргумента:
РАЗНДАТ (нач_дата; кон_дата; тип_результата)

Рассмотрим возможные значения в аргументе тип_результата:
Таблица 4.12. Способы вычисления результата функцией РАЗНДАТ()
“y”

Количество лет между нач_дата и кон_дата

“m”

Количество месяцев между нач_дата и кон_дата

“d”

Количество дней между нач_дата и кон_дата

“md”

Разница дней между нач_дата и кон_дата (месяцы и годы не включаются в расчет)

“ym”

Разница месяцев между нач_дата и кон_дата (дни и годы не включаются в расчет)

“yd”

Разница дней между нач_дата и кон_дата (годы не включаются в расчет)

Например, вот формула для вычисления количества дней до следующего
Нового года:
=РАЗНДАТ(СЕГОДНЯ(); ДАТА(ГОД(СЕГОДНЯ()); 12; 31); “d”)
Также очень легко вычислить возраст человека с помощью данной функции.
Все расчеты существенно упрощаются:
=РАЗНДАТ (Дата_рождения; СЕГОДНЯ(); “y”)
146
146

Глава 4. Полезные функции Excel 2010

ФУНКЦИЯ ЧИСТРАБДНИ()
При предыдущем расчете разницы дней Excel включает в анализ выходные
и праздничные дни. Данная функция создана для случаев, когда требуется
обратное:
ЧИСТРАБДНИ (нач_дата; кон_дата; праздники)

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

ФУНКЦИЯ ДНЕЙ360
Данная функция возвращает разницу между двумя датами, рассчитанную на
основе 360-дневного года (то есть год состоит из 12 периодов по 30 дней):
ДНЕЙ360 (нач_дата; кон_дата; метод)

где
нач_дата – начальная дата,
кон_дата – конечная дата,
метод – число, показывающее способ вычисления:


1 — если нач_дата или кон_дата приходятся на 31-е число месяца, происходит автоматическое преобразование к 30-му числу,



0 – если кон_дата приходится на 31-е число, а нач_дата – менее чем на
30-е кон_дата преобразовывается к 1-му числу следующего месяца.

ФУНКЦИЯ ДОЛЯГОДА()
Часто требуется подсчет доли года, приходящейся на определенный период
времени. Например, если сотрудник увольняется через 3 месяца, вам может
потребоваться оплата четверти его годовой заработной платы. При этом может помочь функция ДОЛЯГОДА(), которая учитывает множество факторов:
147
147
147

Часть I. Вычисления, формулы, функции

ДОЛЯГОДА (нач_дата; кон_дата; базис)

где
нач_дата – начальная дата,
кон_дата – конечная дата,
базис – число, показывающее, на основе чего будут производиться
вычисления:


0 – на основе 360-дневного года (12 периодов по 30 месяцев);



1 – используется фактическое число дней в году и фактическое число
дней в каждом месяце;



2 – используется 360-дневный год, но фактическое число дней в месяцах;



3 — используется 365-дневный год, но фактическое число дней в месяцах;



4 – если нач_дата или кон_дата приходится на 31-е число месяца, происходит автоматическое преобразование к 30-му числу.

4.8.5. РАБОТА С ФУНКЦИЯМИ ВРЕМЕНИ В EXCEL
Таблица 4.13. Функции времени Excel
Функция и ее синтаксис

Краткое описание работы

ЧАС (время_в_числ_формате)

Возвращает час, соответствующий заданному времени. Час определяется как целое
число в интервале от 0 до 23

МИНУТЫ (время_в_числ_формате)

Возвращает минуты, соответствующие аргументу время_в_числовом_формате. Минуты определяются как целое число в интервале от 0 до 59

СЕКУНДЫ (время_в_числ_формате)

Возвращает секунды, соответствующие
аргументу
время_в_числовом_формате.
Секунды определяются как целое число в
интервале от 0 до 59

ВРЕМЯ (часы; минуты; секунды)

Возвращает десятичное число, представляющее определенное время

ВРЕМЗНАЧ (время_как_текст)

Возвращает время в числовом формате для
времени, заданного текстовой строкой

ТДАТА ( )

Возвращает целое число, представляющее
текущие дату и время

148
148

Глава 4. Полезные функции Excel 2010

ФУНКЦИЯ ТДАТА()
Когда вам необходимо использовать текущую дату и время в формуле или
функции в качестве аргумента, просто используйте функцию ТДАТА(), которая не требует никаких аргументов:
ТДАТА()

При этом возвращаются текущие дата и время в числовом формате. Например, если сейчас 31 декабря 2007 года, полдень, функция ТДАТА() вернет
следующий результат:
39447.5
Если вы хотите выделить компоненту времени из полученного результата,
необходимо просто вычесть текущую дату следующим образом:
=ТДАТА() – СЕГОДНЯ()

Помните, что ТДАТА() является динамической функцией. Всякий раз при
пересчете она будет давать новый результат.

ФУНКЦИЯ ВРЕМЯ()
Время как таковое состоит из трех частей: часы, минуты и секунды. Часто
бывает, что на листе генерируются одна или несколько таких частей и их
необходимо соединить в единое время. Это осуществимо с помощью функции ВРЕМЯ():
ВРЕМЯ(часы; минуты; секунды)

где
часы – компонента часов в составе времени (число от 0 до 23),
минуты – компонента минут в составе времени (число от 0 до 59),
секунды - компонента секунд в составе времени (число от 0 до 59).
Например, данное выражение возвращает время 2:12:55 после полудня в
числовом формате:
=ВРЕМЯ (14; 12; 55)

Аналогично функции ДАТА() функция ВРЕМЯ() корректирует неправильное время, введенное в качестве аргументов, например
=ВРЕМЯ (14; 60; 55)

вернет значение, соответствующее 3:00:55 после полудня.
149
149
149

Часть I. Вычисления, формулы, функции

ФУНКЦИЯ ВРЕМЗНАЧ()
Если у вас есть время в виде строки, вы можете преобразовать его в числовой формат с помощью данной функции:
ВРЕМЗНАЧ (время_как_текст)

где
время_как_текст – время в виде строки.
Например, вы можете привести к числу время “3:44:15 PM”:
=ВРЕМЗНАЧ(“3:44:15 PM”)

ПОЛУЧЕНИЕ ЧАСТЕЙ ВРЕМЕНИ
Три части времени – часы, минуты и секунды – могут быть получены индивидуально с помощью следующих функций: ЧАС(), МИНУТЫ() и СЕКУНДЫ().
Рассмотрим их подробнее.
Функция ЧАС() возвращает компоненту часов в составе времени (число от
0 до 23):
ЧАС (время_в_числ_формате)
Например, следующее выражение возвращает число 12:
=ЧАС(0.5)

Функция МИНУТЫ() возвращает компоненту минут в составе времени (число от 0 до 59):
МИНУТЫ (время_в_числ_формате)
Например, если сейчас 15:46, следующее выражение возвращает число 46:
=ЧАС(ТДАТА())

Функция СЕКУНДЫ() возвращает компоненту секунд в составе времени
(число от 0 до 59):
СЕКУНДЫ (время_в_числ_формате)
Например, следующее выражение возвращает число 30:
=ЧАС(“2:55:30 PM”)

ВОЗВРАТ ВРЕМЕНИ, ОТСТОЯЩЕГО ОТ ТЕКУЩЕГО НА N ЧАСОВ, МИНУТ ИЛИ СЕКУНД
Функция ВРЕМЯ(), как было сказано выше, автоматически устраняет не до
конца верно введенные данные. Этим фактом можно воспользоваться для
выполнения задачи.
150
150

Глава 4. Полезные функции Excel 2010

Например, следующее выражение возвращает время, отстоящее на 12 часов
от текущего:
=ВРЕМЯ(ЧАС(ТДАТА()) + 12; МИНУТЫ(ТДАТА()); СЕКУНДЫ(ТДАТА()))

Функция ВРЕМЯ() не позволяет вам просто прибавлять часы, минуты или
секунды для получения времени. Например, выражение
=ТДАТА() + 1

всего-навсего прибавит один день к текущим дате и времени.
Если вам необходимо добавить часы, минуты и секунды ко времени, вы
должны выразить их как долю (часть) дня. Например, так как в дне 24 часа,
1 час представляет собой 1/24 дня. Точно так же, поскольку в часе 60 минут, каждая минута составляет 1/60 часа, и выражение будет выглядеть как
1/24/60. Табл. 4.14 содержит все выражения для операции прибавления ко
времени n часов, минут или секунд.
Таблица 4.14. Формулы для определения времени, сдвинутого относительно
текущего
Операция

Выражение

Пример

Выражение для примера

Прибавление n часов

n*(1/24)

+6 часов

ТДАТА()+6*(1/24)

Прибавление n минут

n*(1/24/60)

-32 минуты

ТДАТА()-32*(1/24/60)

Прибавление n секунд

n*(1/24/60/60)

+11 секунд

ТДАТА()+11*(1/24/60/60)

ПОДСЧЕТ РАЗНИЦЫ МЕЖДУ ДВУМЯ ВРЕМЕНАМИ
Excel представляет время как часть дня (число от 0 до 1), поэтому ничего не
может помешать вам просто работать с временем как с числом и использовать формулу:
=Конечное_время – Начальное_время

Это выражение будет работать отлично, до тех пор пока конечное_время будет больше чем начальное (такие имена использованы, чтобы вы не забывали вычитать только более раннее время из более позднего).
Однако когда конечное_время находится где-то после полуночи следующего дня, велик шанс, что начальное_время окажется больше. Например, если
человек работает с 11 вечера до 7 утра, выражение 7:00 АМ – 11:00 РМ вызовет недопустимое отрицательное значение времени (в ячейке будут показаны заполняющие знаки #).
Для уверенности в том, что ошибки не произойдет, воспользуйтесь следующим обобщенным выражением:
151
151
151

Часть I. Вычисления, формулы, функции

ЕСЛИ(Кон_время < Нач_время; 1 + Кон_время – Нач_время; Кон_
время – Нач_время)

Функция ЕСЛИ() проверит выражение и, в случае необходимости, использует единицу для устранения ошибки.

ПОДЫТОЖИВАЯ РАБОТУ СО ВРЕМЕНЕМ
Когда вы работаете со значениями, содержащими время, в Excel, вы должны опасаться двух фундаментально различных интерпретаций выражения
«прибавление одного времени к другому»:


Сложение значений времени для получения будущего времени. Как
вы видели из предыдущего раздела, прибавление часов, минут или
секунд ко времени приносит в результате будущее время. Например, если текущее время 11:00 PM (23:00), прибавление двух часов
даст в результате 1:00 AM.



Сложение значений времени для получения суммарного значения.
В данном контексте временные значения складываются для получения общего количества часов, минут или секунд. Это полезно, когда
вы хотите знать, сколько времени в сумме отработал ваш сотрудник.
В таком случае, если рассматривать тот же пример, прибавление к
23 часам двух часов даст в результате 25 часов.

Проблема заключается в том, что стандартная интерпретация Excel воспринимает все сложения времени как получение будущего времени. Таким
образом, когда ячейка А1 содержит 23:00, а А2 – 2:00, следующая формула
вернет значение 1:00:
=А1 + А2

Значение 25:00 будет храниться в ячейке как положено, однако Excel проводит автоматическое преобразование для получения «правильного» времени 1:00. Если вы хотите увидеть 25:00:00, необходимо применить следующий формат для ячейки:
[ч]:мм:сс

4.9. ÐÀÁÎÒÀ Ñ ÌÀÒÅÌÀÒÈ×ÅÑÊÈÌÈ ÔÓÍÊÖÈßÌÈ
Таблица 4.15. Основные математические функции Excel
Функция и ее синтаксис

Краткое описание работы

ABS (число)

Возвращает модуль (абсолютную величину)
числа

152
152

Глава 4. Полезные функции Excel 2010

ОКРВВЕРХ (число; точность)

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

ЧИСЛКОМБ (число; число_выбранных)

Возвращает количество комбинаций для заданного числа элементов

ЧЁТН (число)

Возвращает число, округленное до ближайшего
четного целого

EXP (число)

Возвращает число «e», возведенное в указанную степень

ФАКТР (число)

Возвращает факториал числа

ОКРВНИЗ (число; точность)

Округляет число до кратного заданной точности
с недостатком

НОД (число1; число2; ...)

Возвращает наибольший общий делитель двух
или более целых чисел

ЦЕЛОЕ (число)

Округляет число до ближайшего меньшего целого

НОК (число1; число2; ...)

Возвращает наименьшее общее кратное целых
чисел

LN (число)

Возвращает натуральный логарифм числа

LOG (число; основание)

Возвращает логарифм числа по заданному основанию

LOG10 (число)

Возвращает десятичный логарифм числа

МОПРЕД (массив)

Возвращает определитель матрицы

МОБР (массив)

Возвращает обратную матрицу для матрицы

МУМНОЖ (массив1; массив2)

Возвращает произведение матриц

ОСТАТ (число; делитель)

Возвращает остаток от деления числа на делитель

ОКРУГЛТ (число; точность)

Возвращает число, округленное с нужной точностью

МУЛЬТИНОМ (число1; число2; ...)

Возвращает отношение факториала суммы значений к произведению факториалов

НЕЧЁТ (число)

Возвращает число, округленное до ближайшего
нечетного целого

ПИ ( )

Возвращает математическую константу «пи» с
точностью до 15 цифр

СТЕПЕНЬ (число; степень)

Возвращает результат возведения числа в степень

ПРОИЗВЕД (число1; число2; ...)

Перемножает числа, заданные в качестве аргументов, и возвращает их произведение

ЧАСТНОЕ
тель)

Возвращает целую часть результата деления с
остатком

(числитель;

знамена-

153
153
153

Часть I. Вычисления, формулы, функции

Возвращает равномерно распределенное случайное вещественное число от 0 до 1

СЛЧИС ( )
СЛУЧМЕЖДУ (нижн_граница;
верхн_граница)

Возвращает случайное целое число, находящееся в диапазоне между двумя заданными числами

РИМСКОЕ (число; форма)

Преобразует арабское число в римское (в текстовом формате)

ОКРУГЛ (число; число_разрядов)

Округляет число до указанного количества десятичных разрядов

ОКРУГЛВНИЗ (число; число_разрядов)

Округляет число до ближайшего меньшего по
модулю значения

ОКРУГЛВВЕРХ (число; число_разрядов)

Округляет число до ближайшего большего по
модулю значения

РЯД.СУММ (x; n; m; коэффициенты)

Возвращает сумму степенного ряда

ЗНАК (число)

Определяет знак числа

КОРЕНЬ (число)

Возвращает неотрицательное значение квадратного корня

КОРЕНЬПИ (число)

Возвращает квадратный корень из значения выражения (число * ПИ)

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (номер_функции; ссылка1; ссылка2;...)

Возвращает промежуточный итог в список или
базу данных

СУММ (число1; число2; ...)

Суммирует все введенные аргументы

СУММЕСЛИ (диапазон; условия;
диапазон_суммирования)

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

СУММПРОИЗВ (массив1;
массив3; ...)

Перемножает соответствующие элементы заданных массивов

массив2;

СУММКВ (число1; число2; ...)

Возвращает сумму квадратов аргументов

СУММКВРАЗН (массив_x; массив_y)

Возвращает сумму квадратов разностей соответствующих значений в двух массивах

СУММРАЗНКВ (массив_x; массив_y)

Возвращает сумму разностей квадратов соответствующих значений в двух массивах

СУММСУММКВ (массив_x; массив_y)

Возвращает сумму сумм квадратов соответствующих элементов двух массивов

ОТБР (число; число_разрядов)

Усекает число до целого, отбрасывая дробную
часть

Все тригонометрические функции с краткими описаниями указаны в
табл. 4.16. Вот некоторые замечания, которые необходимо учитывать при
их использовании:
154
154

Глава 4. Полезные функции Excel 2010



В каждой функции аргумент число является углом в радианах.



Если необходимый вам угол представлен в градусах, вы можете
преобразовать его в радианы, умножив на коэффициент ПИ()/180.
Или же используйте функцию РАДИАНЫ (угол), которая преобразует градусы в радианы.



Тригонометрические функции возвращают значения в радианах.
Если вам нужен результат в градусах, умножьте результат на коэффициент 180/ПИ(). Или же используйте функцию ГРАДУСЫ
(угол).

Таблица 4.16. Тригонометрические функции Excel
Функция и ее синтаксис

Краткое описание работы

ACOS (число)

Возвращает арккосинус числа (число должно быть от -1
до 1)

ACOSH (число)

Возвращает гиперболический арккосинус числа (число
должно быть больше или равно 1)

ASIN (число)

Возвращает арксинус числа (число должно быть от -1 до 1)

ASINH (число)

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

ATAN (число)

Возвращает арктангенс числа

ATANH (число)

Возвращает гиперболический арктангенс числа (число
должно быть от -1 до 1)

ATAN2 (x; y)

Возвращает арктангенс для заданных координат x и y

COS (число)

Возвращает косинус заданного угла

COSH (число)

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

ГРАДУСЫ (угол)

Преобразует угол из радианов в градусы

SIN (число)

Возвращает синус заданного угла

SINH (число)

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

TAN (число)

Возвращает тангенс заданного угла

TANH (число)

Возвращает гиперболический тангенс числа

4.9.1. ФУНКЦИИ ОКРУГЛЕНИЯ В EXCEL
Округление необходимо во многих ситуациях. Данный раздел поможет вам
разобраться, какие именно из них необходимо применять в том или ином
случае.

ФУНКЦИЯ ОКРУГЛ()
Для округления чаще всего применяется данная функция:
155
155
155

Часть I. Вычисления, формулы, функции

ОКРУГЛ (число; число_разрядов)

где
число – число для округления,
число_разрядов – число, отвечающее за порядок округления:


>0 – округлить число к этому числу знаков после запятой,



0 – округлить число до ближайшего целого числа,



0 – округлить число к этому числу знаков после запятой,



0 – округлить число до ближайшего целого числа,



0 – отбросить все, кроме указанного числа разрядов после запятой,



0 – отбросить все после запятой (используется по умолчанию),



Основная
горизонтальная ось 
a) Нет. Горизонтальная ось отображаться не будет;
b) Слева направо. Отображать подписи к оси в порядке возрастания
(слева направо);
c) Без подписей. Отображать горизонтальную ось без подписей;
d) Справа налево. Отображать подписи к оси в порядке убывания
(справа налево);
e) Дополнительные параметры основной горизонтальной оси. Откроется окно параметров горизонтальной оси;

ПАРАМЕТРЫ ГОРИЗОНТАЛЬНОЙ ОСИ
На рис. 5.25 показано окно настроек горизонтальной оси.
222
222

Глава 5. Построение графиков и диаграмм в Excel 2010

Рис. 5.25. Окно настроек основной горизонтальной оси в Excel 2010

Здесь, как вы видите, почти все разделы нами рассмотрены, за исключением
раздела Параметры оси, также мы рассмотрим часть настроек раздела Тип
линии – Параметры стрелки, так как при предыдущем рассмотрении они
были недоступны.
В разделе Параметры оси вы можете задать интервал между делениями,
указав необходимое число в соответствующем поле (таким образом, вы
сможете указать, через какое количество интервалов между делениями ставить линию отметки интервала). Пример вида горизонтальной оси при значении данного параметра 1,2 и 3 вы можете видеть на рис. 5.26.
223
223
223

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 5.26. Вид горизонтальной оси диаграмм при различных значениях интервала
между делениями

Далее выберите интервал между подписями. Здесь предусмотрено два варианта:


Автовыбор. Интервал будет выбран автоматически в зависимости
от типа и объема данных, по которым строится график/диаграмма.



Единица измерения интервала. Укажите, с каким шагом отображать числа на оси. Например, у вас есть последовательность чисел:
1,2,3,4,5,6,7 и т.д. Если вы зададите для данного параметра значение 1, то числа будут отображаться в том же порядке, если укажете
значение 2, то в следующем порядке – 1,3,5,7 и т.д., если значение
параметра 3, то будут отображаться только следующие числа – 1,4,7
и т.д.

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

Рис. 5.27. Диаграмма с прямым и обратным порядком категорий в Excel 2010

224
224

Глава 5. Построение графиков и диаграмм в Excel 2010

Далее вы можете указать Тип оси. Здесь предусмотрено три варианта:


Автовыбор на основе данных. Тип оси будет выбран автоматически, в зависимости от типа данных, по которым строится график/
диаграмма.



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



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

Для параметра Основные укажите один из четырех вариантов прорисовки
линий разметки интервала:
a) Нет. Линии разметки интервалов на оси прорисовываться не будут.
b) Внутрь. Линии разметки интервалов на оси будут прорисованы
только над осью.
c) Наружу. Линии разметки интервалов на оси буду прорисованы
только под осью.
d) Пересекают ось. Линии разметки будут пересекать ось, на рис. 5.27
линии разметки прорисованы именно пересекающими ось.
Аналогичные варианты вы можете выбрать для Промежуточных линий
разметки.
Далее укажите, как будут расположены подписи к оси (параметр Подписи оси):
a) Рядом с осью. Подписи буду расположены под осью, на расстоянии,
указанном в параметре Расстояние от подписи до оси.
b) Вверху. Подписи будут расположены над графиком, на расстоянии
от него, заданном параметром Расстояние от подписи до оси.
c) Внизу. Различий между этим вариантом и вариантом Рядом с осью
вы не заметите.
d) Нет. Подписи к горизонтальной оси отображаться не будут.
225
225
225

Часть II. Анализ данных с помощью диаграмм и графиков

В разделе настроек оси Вертикальная ось пересекает вы можете указать
точку пересечения вертикальной оси с горизонтальной (т.е. в какой точке
по номеру вертикальная ось пересечет горизонтальную):


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



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



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

Для настройки Положение оси вы можете выбрать – по делениям и между
делениями, пример обоих случаев положения оси вы можете видеть на рис.
5.28.

Рис. 5.28. Положение оси по делениям и между делениями в Excel

Теперь обратимся к разделу настроек Тип линии. Как вы можете видеть,
теперь в данном разделе настроек стали активны настройки Параметры
стрелки. Здесь вы можете выбрать, как будет указываться направление на
оси. Выберите тип начала и окончания оси (см. рисунок 5.29), а также начальный и конечный размер (см. рисунок 5.30).

Рис. 5.29. Варианты начала и
окончания оси в Excel 2010

Рис. 5.30. Варианты начального
и конечного размеров оси в Excel
2010

ПАРАМЕТРЫ ВЕРТИКАЛЬНОЙ ОСИ
Для настройки параметров вертикальной оси выберите Оси  Основная
вертикальная ось 
a) Нет. Вертикальная ось отображаться не будет;
226
226

Глава 5. Построение графиков и диаграмм в Excel 2010

b) По умолчанию. В данном случае подписи к вертикальной оси будут
расположены в установленном по умолчанию порядке – в зависимости от типа и размерности данных;
c) Тысячи. Значения подписей к вертикальной оси будут отображены
в тысячах;
d) Миллионы. Значения подписей к вертикальной оси будут отображены в миллионах;
e) Миллиарды. Значения подписей к вертикальной оси будут отображены в миллиардах;
f) Логарифмическая шкала. Значения на оси будут отображены в логарифмическом масштабе по основанию 10;
g) Дополнительные параметры основной вертикальной оси. Отличие
от настроек, рассмотренных для горизонтальной оси, следующее:
вам будет необходимо указать минимальное и максимальное значения, цену основных и промежуточных делений; так же, как и для горизонтальной оси, вы сможете указать обратный порядок значений,
поставив галочку напротив соответствующего пункта настроек; вы
можете включить параметр логарифмическая шкала, поставив соответствующую галочку, также при включении данного параметра
вы сможете указать основание логарифмической шкалы (по умолчанию оно равно 10, параметр — Основная); вы сможете выбрать
цену деления (нет – значение по умолчанию, сотни, тысячи, 10000,
100000, миллионы, 10000000, 100000000, миллиарды, триллионы),
также, при выборе варианта, отличного от нет, вы сможете указать
– отображать или нет выбранную цену деления рядом с вертикальной осью (для того чтобы включить отображение выбранной единицы, поставьте галочку напротив Отображать на диаграмме); так
же, как и для вертикальной оси, вы сможете указать, как отображать
основные и промежуточные линии разметки оси, а также как отображать подписи к оси и место, где горизонтальная ось пересечет вертикальную.

5.2.3. РАЗДЕЛ «ОСИ», КНОПКА «СЕТКА»
По умолчанию при построении графика/диаграммы линии сетки не прорисовываются. Но вам может потребоваться показать линии сетки для вертикальной и/или горизонтальной оси.
Для включения линий сетки горизонтальной/вертикальной оси выберите
Сетка  Горизонтальные/вертикальные линии сетки по основной оси 
227
227
227

Часть II. Анализ данных с помощью диаграмм и графиков

a) Нет. Не отображать линии сетки;
b) Основные линии сетки. Будут отображены линии сетки для основных делений (по подписям к оси);
c) Промежуточные линии сетки. Будут отображены линии сетки для
промежуточных делений;
d) Основные и промежуточные линии сетки. Показывать линии сетки
для основных и промежуточных делений;
e) Дополнительные параметры линий сетки на основной горизонтальной/вертикальной оси. Как для горизонтальной, так и для вертикальной оси, откроется одинаковое окно настроек с уже известными нам разделами настроек – Цвет линии, Тип линии и Тень.

5.2.4. РАЗДЕЛ «ФОН»
В данном разделе настроек вы сможете настроить фон для графиков/диаграмм – Область построения (настройка параметров области построения;
доступно только для двумерных графиков/диаграмм), стенка диаграммы,
основание диаграммы, поворот объемной фигуры (последние три настройки доступны только для объемных типов графиков и диаграмм).

ОБЛАСТЬ ПОСТРОЕНИЯ
Для того чтобы настроить область построения, выберите Область построения 
a) Нет. Не отображать область построения;
b) Показать область построения. Название говорит само за себя;
c) Дополнительные параметры области построения. Открыть окно
настроек области построения. В данном окне вы сможете настроить
такие параметры как Заливка, Цвет границы, Стили границ, Тень и
Формат объемной фигуры (данные настройки рассмотрены нами
ранее);

СТЕНКА ДИАГРАММЫ
Для объемной фигуры вы можете включить и настроить стенку диаграммы.
Параметры настройки аналогичны параметрам для Области построения.
228
228

Глава 5. Построение графиков и диаграмм в Excel 2010

ОСНОВАНИЕ ДИАГРАММЫ
Для объемной фигуры вы можете включить и настроить основание диаграммы. Параметры настройки аналогичны параметрам для Области построения.

ПОВОРОТ ОБЪЕМНОЙ ФИГУРЫ
Откроется окно, в котором вы сможете повернуть объемную фигуру под
нужным углом. В зависимости от типа графика/диаграммы вы сможете настроить ряд параметров:


поворот по осям X, Y и/или Z (укажите нужный угол поворота);



перспектива (вы сможете изменить угол, под которым будете “смотреть на фигуру” или расстояние от точки просмотра до основания
фигуры – в зависимости от типа диаграммы, для включения этой
опции снимите галочку напротив пункта Оси под прямым углом);



масштаб диаграммы (вы сможете указать глубину и высоту в % от
базовых параметров, для включения возможности настройки высоты снимите галочку напротив пункта Автомасштабирование);



если на диаграмме присутствует текст, вы сможете оставить его
плоским или сделать объемным (для этого снимите галочку напротив Оставить текст плоским);



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

5.2.5. РАЗДЕЛ «АНАЛИЗ»
В зависимости от типа графика/диаграммы, вы сможете применить различные инструменты для анализа данных диаграммы. В разделе Анализ
вы сможете выбрать инструмент и тип инструмента (в зависимости от типа
диаграммы вам будет доступен определенный набор инструментов или не
будет доступно ни одного инструмента). В Excel, вы сможете выбрать один
из четырех инструментов для анализа данных.

ЛИНИЯ ТРЕНДА
С помощью данного инструмента вы сможете добавлять линию (или линии), показывающую, например, среднее увеличение/уменьшение по дан229
229
229

Часть II. Анализ данных с помощью диаграмм и графиков

ным графика/диаграмм или экспоненциальную зависимость. Рассмотрим
типы линии тренда.


Линейное приближение. Построит на графике линию (прямую),
показывающую среднее значение по всем данным, представленным
на графике/диаграмме;



Экспоненциальное приближение. Построит на графике линию
(экспоненциальную), показывающую среднее значение по всем данным, представленным на графике/диаграмме;



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



Линейная фильтрация. Будет построена ломаная линия, начиная с
заданной точки данных (минимальное значение параметра равно 2,
максимальное — n-1, где n – число точек данных на графике/диаграмме), которая будет показывать среднее значение данных на графике/диаграмме;
ПРИМЕЧАНИЕ.
Каждый раз, когда вы будете выбирать тип линии тренда, текущая линия не будет
изменяться, а будет добавляться новая; при выборе варианта Нет в списке возможных линий тренда, будет удалена последняя из добавленных линий тренда.

ПРИМЕЧАНИЕ.
В области легенды диаграммы будут отображаться пояснения (подписи) для каждой линии тренда, присутствующей на графике/диаграмме.

Для каждой линии тренда можно указать отдельные настройки. Для того
чтобы открыть окно настроек линии тренда, выберите Анализ  Линия
тренда Дополнительные параметры линии тренда. Откроется окно, в
котором вы сможете настроить параметры линии тренда – рисунок 5.31.
Как вы можете видеть, в данном окне настройки разделены на четыре
группы, нас интересует только первая – Параметры линии тренда
(остальные рассмотрены нами ранее). Здесь вы сможете добавить два типа
линий тренда, ранее нами не рассмотренных, так как в основном списке
линий тренда они не указаны:


230
230

Степенная. Добавляет степенную линию тренда, отображающую
динамику изменения данных на графике/диаграмме;

Глава 5. Построение графиков и диаграмм в Excel 2010

Рис. 5.31. Окно настройки параметров линии тренда в Excel 2010



Полиномиальная. Добавляет полиномиальную линию тренда, для
данного типа линии тренда вам будет необходимо указать степень
полинома (минимум 2, максимум n-1, где n — число точек данных
на графике/диаграмме).

Также вы сможете указать название текущей линии тренда: автоматическое (формируется следующим образом: ()) или другое (вам будет
необходимо указать имя линии тренда).
Для текущей линии тренда, вы сможете указать параметры прогноза – вперед и назад на заданное число периодов. Для экспоненциальной, линейной
и/или полиномиальной линий тренда вы сможете указать точку пересечения линии с осью Y, для этого поставьте галочку напротив Пересечение
кривой с осью Y в точке и укажите необходимую точку. Если вы хотите показать на диаграмме уравнение, описывающее текущую линию тренда, пос231
231
231

Часть II. Анализ данных с помощью диаграмм и графиков

тавьте галочку напротив пункта Показывать уравнение на диаграмме. Также вы можете показать на графике величину достоверности аппроксимации
R^2, поставив галочку напротив пункта Поместить на диаграмме величину
достоверности аппроксимации (R^2). Чем ближе значение R^2 к единице,
тем надежнее аппроксимированы данные, представленные на диаграмме.

ЛИНИИ
Здесь вы сможете включить линии проекции (они будут проходить через
опорные точки графика/диаграммы под прямым углом к горизонтальной
оси) для графика/диаграммы. Для включения и настройки линий проекции
выберите Анализ  Линии 
a) Нет. Не показывать линии проекции;
b) Линии проекции. Показать линии проекции;
c) Коридор колебания. Покажет коридор колебания для данных на
графике/диаграмме.
На рис. 5.32 вы можете видеть пример графика с линиями проекции и
коридором колебания.

Рис. 5.32. Линии проекции (вверху) и коридор колебания (внизу)

232
232

Глава 5. Построение графиков и диаграмм в Excel 2010

ПОЛОСЫ ПОВЫШЕНИЯ/ПОНИЖЕНИЯ
Полосы повышения/понижения по своей функции схожи с коридором
колебания, только представляют они собой не линии, а объемные фигуры.
Добавить их к графику довольно просто: выберите Анализ  Полосы
повышения понижения  Полосы повышения понижения (выберите
вариант Нет, чтобы убрать полосы повышения/понижения). Для вызова
окна настроек полос повышения/понижения, выполните Анализ  Полосы
повышения понижения  Дополнительные параметры полос повышения
понижения. В данном окне все настройки аналогичны рассмотренным нами
ранее, поэтому мы не будем их обсуждать.
Пример диаграммы с полосами повышения/понижения вы можете видеть
на рис. 5.33.

Рис. 5.33. Диаграмма с полосами повышения/понижения в Excel 2010

ПЛАНКИ ПОГРЕШНОСТИ
С помощью планок погрешности вы сможете наглядно показать на диаграмме погрешность или возможное отклонение при отображении (или для
отображенных) на графике/диаграмме данных. Щелкните по кнопке Планки погрешности и в появившемся списке выберите нужный тип планок погрешности, которые будут добавлены на график/диаграмму:
233
233
233

Часть II. Анализ данных с помощью диаграмм и графиков

a) Нет. Планки погрешности на графикеотображены не будут.
b) Планки погрешности со стандартными ошибками. Для выделенного ряда диаграммы будут отображены планки погрешности со стандартными значениями ошибок.
c) Планки с относительными ошибками. Для выделенного ряда диаграммы будут отображены планки погрешности с пятипроцентными значениями ошибок.
d) Планки погрешности со стандартными отклонениями. Для выделенного ряда диаграммы будут отображены планки погрешности со
стандартными значениями отклонений.
Также вы сможете открыть окно настроек планок погрешности. Для этого
выберите Анализ  Планки погрешности  Дополнительные параметры
планок погрешности. Окно настроек планок погрешности вы можете видеть
на рис. 5.34.

Рис. 5.34. Окно настроек планок погрешности в Excel

234
234

Глава 5. Построение графиков и диаграмм в Excel 2010

В данном окне настройки разделены на четыре группы. Нас будет
интересовать только первая из них – Вертикальные планки погрешности.
В данной группе настроек вы сможете выбрать направление планок
погрешности – все, минус и плюс (в окне настроек планок погрешности
содержаться изображения каждого из указанных вариантов); конечный
стиль – без точки и точка (для данной настройки также содержаться
поясняющие изображения); а также сможете выбрать величину погрешности
и задать само значение данной величины.
Как вы могли убедиться – все рассмотренные средства анализа данных,
представленные на вкладке Макет, очень полезны, вам остается только
сделать выбор относительно их использования. На данной панели осталась
неисследованной одна кнопка Свойства, нажав ее, вы сможете задать имя для
диаграммы. На этом рассмотрение вкладки Макет для работы с графиками/
диаграммами можно закончить.

5.3. ÌÀÊÅÒÛ È ÑÒÈËÈ ÄÈÀÃÐÀÌÌ. ÂÊËÀÄÊÀ ÔÎÐÌÀÒ
На данной вкладке расположены инструменты для форматирования отдельных элементов диаграммы. Для форматирования элемента вам будет
необходимо проделать следующее: выбрать необходимый элемент диаграммы, щелкнув по нему левой кнопкой мыши; зайти на вкладку Формат; выбрать необходимый для форматирования инструмент (или настройку) и задать нужные параметры.
Как вы убедитесь в дальнейшем, большинство инструментов и настроек,
доступных на вкладке Формат, доступны в окнах настройки диаграммы,
которые мы рассмотрели ранее. Преимущество обращения к данным настройкам из вкладки формат – удобство (все настройки сгруппированы по
категориям на панелях вкладки Формат). Итак, все настройки сгруппированы в пяти вкладках – Текущий фрагмент, Стили фигур, Стили WordArt,
Упорядочить и Размер.
На панели Текущий фрагмент расположены всего две кнопки – Формат
выделенного фрагмента и Восстановить форматирование стиля. Как вы
могли заметить, данная панель присутствует также и на вкладке Макет.
Напомним, что нажав кнопку Формат выделенного фрагмента, вы откроете окно настроек, выделенного вами, элемента диаграммы, а нажав кнопку
Восстановить форматирование стиля, вы сможете вернуть все настройки,
выделенного элемента, к настройкам по умолчанию. Теперь подробно рассмотрим оставшиеся четыре панели.
235
235
235

Часть II. Анализ данных с помощью диаграмм и графиков

5.3.1. ПАНЕЛЬ СТИЛИ ФИГУР
На данной панели вы можете выбрать макет для оформления элемента и
настроить параметры оформления. Самым большим элементом на данной
панели является инструмент для выбора макета стиля. В зависимости от
элемента диаграммы, выбранного в данный момент, вы сможете выбрать
стиль задающий параметры отображения текста (или надписей) в элементе
и стиль заливки – рисунок 5.35 (сверху). Если выделена таблица данных,
вы сможете выбрать толщину и цвет линии для границы таблицы – рисунок
5.35 (снизу).

Рис. 5.35. Выбор стиля фигуры
диаграммы в Excel 2010

Рис. 5.36. Окно выбора цвета в Excel 2010

Как вы видите, в правой части данного элемента расположены три кнопки.
Кнопки
и
предназначены для перемещения вверх и вниз (соответственно) по списку стилей. Если вы хотите просмотреть весь список стилей
и выбрать нужный, нажмите кнопку . Стоит отметить, что при наведении
курсора мыши на иконку, обозначающую тот или иной стиль, вы сразу увидите (на диаграмме), какие изменения произойдут с выбранным элементом.
Для того чтобы применить заливку к выделенному объекту диаграммы, нажмите кнопку Заливка фигуры. Появится список, в котором вы сможете
236
236

Глава 5. Построение графиков и диаграмм в Excel 2010

выбрать нужный цвет заливки. Если нужного цвета в списке вы не найдете,
нажмите Другие цвета заливки, появится окно, показанное на рис. 5.36, в
котором вы сможете выбрать любой цвет доступный на вашем компьютере
(количество цветов зависит от конфигурации вашего компьютера).
На вкладке Обычные данного окна выберите нужный цвет и укажите нужный уровень прозрачности, либо зайдите во вкладку Спектр, где вы сможете выбрать цвет, щелкнув левой кнопкой в поле цвета, либо выберите
цветовую модель (RGB или HLS). Также в данной вкладке вы можете указать уровень прозрачности. При выборе цвета с помощью цветовой модели,
укажите числовые значения для красного, зеленого и синего составляющих
цвета (RGB) или уровни оттенка, насыщенности и яркости (HLS). По окончании выбора цвета нажмите кнопку ОК.
Если в качестве заливки вы хотите указать рисунок, выберите пункт Заливка фигуры  Рисунок, и выберите необходимый файл рисунка с вашего
жесткого диска.
Если вы хотите применить градиентную заливку к выделенному фрагменту,
выберите Заливка фигуры  Градиентная 
a) Выберите нужный шаблон для градиентной заливки.
b) Другие градиентные заливки – откроется окно настроек выбранного элемента диаграммы, в котором вы сможете указать нужный тип
градиентной заливки (при этом в окне настроек будет выбран тип
заливки, соответствующий текущему типу заливки выбранного элемента).
Для того чтобы указать заливку выбранного элемента диаграммы, выберите
Заливка фигуры  Текстура
a) Выберите из списка нужную текстуру.
b) Другие текстуры — откроется окно настроек, выбранного элемента
диаграммы, в котором вы сможете указать нужную текстуру (при
этом в окне настроек будет выбран тип заливки, соответствующий
текущему типу заливки выбранного элемента).
Для того чтобы выбрать цвет и параметры контура фигуры диаграммы, нажмите кнопку Контур фигуры. В появившемся списке вы сможете выбрать
цвет контура (аналогично тому, как вы это делали для Заливки фигуры).
Чтобы указать толщину линии, выберите Контур фигуры  Толщина, далее выберите нужную толщину фигуры. Для того чтобы выбрать тип линии
(штриха), выберите Контур фигуры  Штрихи, далее выберите нужный
тип штриха. Если вы хотите открыть окно дополнительных настроек кон237
237
237

Часть II. Анализ данных с помощью диаграмм и графиков

тура фигуры, выберите Контур фигуры  Толщина (или Штрихи)  Другие линии. Если в качестве форматируемого элемента выбрана одна из осей
графика/диаграммы, вы можете указать тип стрелки, для этого выберите
Контур фигуры  Стрелки 
a) Выберите нужный тип стрелки.
b) Другие стрелки – откроется окно настройки, выбранного элемента
диаграммы, в котором вы сможете указать все необходимые параметры стрелки.
И наконец, вы сможете указать эффекты для выбранного элемента диаграммы (для открытия списка эффектов нажмите кнопку Эффекты для фигур).
Далее выберите Заготовка (для создания объемной фигуры), Тень (вы сможете выбрать определенный тип тени для фигуры), Отражение, Свечение,
Сглаживание (для сглаживания краев фигуры), Рельеф (рельеф для объемной фигуры) и Поворот объемной фигуры. Если вы захотите открыть
окно настроек для выбранной фигуры, чтобы указать более детально настройки выбранного эффекта, выберите (далее приведена общая для всех
типов эффектов схема) Эффекты для фигур  [название эффекта]  в
конце списка будет находиться кнопка для открытия окна настроек.
Обратите внимание на маленькую кнопку в правом нижнем углу панели

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

5.3.2. ПАНЕЛЬ СТИЛИ WORDART
На данной панели вы сможете выбрать и настроить стили для текста, отображаемого внутри выбранного элемента диаграммы. Самым большим инструментом на данной панели является инструмент для выбора макета стиля
текста – рисунок 5.37.

Рис. 5.37. Панель выбора стиля текста в Excel 2010

Перемещаться по списку стилей текста вы можете с помощью кнопок
(вверх) и
(вниз). Для того чтобы открыть полный список стилей текста
и выбрать нужный, нажмите . При наведении курсора мыши на иконку,
238
238

Глава 5. Построение графиков и диаграмм в Excel 2010

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


Тень (выберите тип тени, которую будет “отбрасывать” текст);



Отражение (вы сможете создать “отражение” для текста, выбрав
его макет);



Свечение (укажите макет для создания эффекта свечения текста);



Рельеф (если текст представляет собой объемную фигуру, вы можете выбрать макет рельефа для него);



Поворот объемной фигуры (выберите макет расположения объемного текста “в пространстве”, чтобы расположить его под нужным
углом);



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

Для некоторых эффекторов (Тень, Рельеф, Поворот объемной фигуры)
в конце списка макетов эффектов вы сможете открыть окно настроек для
более детальной настройки, текущего примененного эффекта. При выборе
эффекта из категории Свечение вы сможете в конце списка эффектов перейти к списку цветов, где сможете указать нужный цвет свечения.
Если вы добавили на график/диаграмму фрагмент WordArt из вкладки
Вставить, то на вкладке Формат вместо панели Текущий фрагмент будет
находиться панель Вставить фигуры, показанная на рис. 5.38.

Рис. 5.38. Панель “Вставить фигуры”

239
239
239

Часть II. Анализ данных с помощью диаграмм и графиков

Используя знакомые нам кнопки ,
и
для навигации по списку шаблонов фигур, вы сможете выбрать и добавить к графику/диаграмме нужную фигуру. Если вы хотите изменить выбранную фигуру выберите
 Изменить фигуру, далее выберите из списка новый шаблон фигуры. Вы
можете преобразовать фигуру в полилинию (фигура будет состоять не из
одной неразрывной линии, а из отрезков), для этого выберите
 Преобразовать в полилинию.
После этого вы сможете изменить фигуру по собственному желанию, перемещая точки соединения отрезков (из которых строится линия), для этого
выберите
 Начать изменение узлов. После этого вы увидите, что
на фигуре появится ряд черных маркеров, перемещая (для перемещения
маркера нажмите левую кнопку мыши, когда курсор будет над маркером, и,
удерживая левую кнопку мыши, переместите маркер в нужное место) которые вы сможете изменить фигуру по своему усмотрению. Также при выборе того или иного маркера (маркер будет выбран либо после перемещения,
либо после того, как вы щелкнете по нему левой кнопкой мыши) рядом с
ним появятся два белых маркера, перемещая которые вы сможете изменить
отрезки (сделать их закругленными), на пересечении которых стоит черный маркер.
Для того чтобы вставить надпись (объект WordArt) с панели Вставить фигуры, нажмите кнопку
и щелкните по месту на диаграмме, куда хотите вставить надпись.

5.3.3. ПАНЕЛИ “УПОРЯДОЧИТЬ” И “РАЗМЕР”
На рис. 5.39 показаны панели Упорядочить и Размер. На панели
Упорядочить (для диаграмм) большинство кнопок будут неактивны, а те,
что доступны, вам не понадобятся при построении графиков/диаграмм и
анализе данных с помощью них.

Рис. 5.39. Панели “Упорядочить” и “Размер”

На панели Размер вы можете указать размер диаграммы. Если вы хотите
открыть окно настроек размера диаграммы, нажмите
. Окно настроек
размеров области диаграммы показано на рис. 5.40.
240
240

Глава 5. Построение графиков и диаграмм в Excel 2010

Рис. 5.40. Окно настроек размера диаграммы

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

241
241
241

242
242

ГЛАВА 6.
АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ
ДИАГРАММ И ГРАФИКОВ

243
243
243

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

6.1. ÏÐÈÌÅÐ ¹1. ÀÍÀËÈÇ ÑÏÐÎÑÀ
Пусть у вас есть собственное предприятие по производству мебели, выпускающее несколько видов продукции – шкафы (трех видов), столы (пяти
видов) и стулья (двух видов). У вас есть таблица, в которой приведены данные об объемах продаж за первый квартал текущего года (рис. 6.1) и вы, на
основе этих данных, хотите проанализировать данные, чтобы выяснить ка-

Рис. 6.1. Таблица с данными из условия примера №1

244
244

Глава 6. Анализ данных с помощью диаграмм и графиков

кие виды продукции пользуются наибольшим спросом и принять решение
об увеличении/уменьшении объемов производства.

6.1.1. РЕШЕНИЕ №1. ИСПОЛЬЗОВАНИЕ ГИСТОГРАММЫ
Решение состоит в том, чтобы представить данные по всем видам продукции с помощью гистограммы - рисунок 6.2.

Рис. 6.2. Данные о продажах, представленные с помощью гистограммы

6.1.2. РЕШЕНИЕ №2. ИСПОЛЬЗОВАНИЕ ЛИНЕЙЧАТОЙ ДИАГРАММЫ
Вторая альтернатива — представить данные по всем видам продукции с помощью линейчатой диаграммы (она похожа на гистограмму, только столбцы расположены горизонтально, а не вертикально) с таблицей данных – рисунок 6.3.
Как вы можете видеть, в этом случае данные на диаграмме расположены
снизу вверх. Чтобы исправить это, выберите отображение данных в обратном порядке, но в этом случае в обратном порядке будут отображаться и
данные в таблице.
245
245
245

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.3. Линейчатая диаграмма с таблицей данных

6.1.3. РЕШЕНИЕ №3. ИСПОЛЬЗОВАНИЕ КРУГОВОЙ ДИАГРАММЫ
Третье решение заключается в представлении данных в виде круговой диаграммы, чтобы увидеть, какую часть от общего объема выпущенной продукции составляет объем продаж каждого из видов продукции – рисунок 6.4.

Рис. 6.4. Круговая диаграмма, отображающая объемы продаж

246
246

Глава 6. Анализ данных с помощью диаграмм и графиков
ПРИМЕЧАНИЕ:
На рис. 6.4 вы можете видеть, что от подписей данных до элементов, к которым
относятся подписи, идут линии, с помощью которых вы легко можете проследить
– какая подпись к какому элементу относится. Эти линии рисуются в Excel автоматически, когда вы удаляете подпись от элемента, к которому она принадлежит.
Чтобы переместить подпись, наведите на нее курсор мыши, зажмите левую кнопку
мыши и, не отпуская ее, переместите подпись в нужное место.

СОВЕТ:
В последнее время многие люди применяют круговые диаграммы для того, чтобы сравнить несопоставимые данные (например, цены на автомобили и цены на
одежду, или объемы продаж продуктов и бытовой техники, и т.п.). Поэтому будьте
внимательны при построении диаграмм. От качества построения графиков и диаграмм будет зависеть качество (точность) анализа ваших данных.

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

Рис. 6.5. Рабочий лист Excel 2010 с несколькими диаграммами

6.1.4. ИТОГИ
Используя диаграммы, полученные в решениях 1 и 2, вы можете проанализировать, какой из видов продукции пользуется наибольшим спросом, а
какой - наименьшим. С помощью диаграммы, построенной в решении 3, вы
сможете увидеть, какую часть от общего объема продаж составляет объем
продажи, каждого из видов продукции. Вот несколько случаев, когда полез247
247
247

Часть II. Анализ данных с помощью диаграмм и графиков

но использовать круговую диаграмму: когда вы хотите оценить значимость
той или иной доли данных из общего объема; когда вы хотите посмотреть,
какую часть от общего времени работы занимает определенная деятельность; когда вы хотите увидеть относительный объем/долю чего-либо в общей массе рассматриваемых данных, и т.д., и т.п.

6.2. ÏÐÈÌÅÐ ¹2. ÑÐÀÂÍÅÍÈÅ ÄÀÍÍÛÕ ÏÎ ÂÛÁÎÐÊÀÌ
Пусть у вас есть оптовый склад, на котором вы храните следующие виды
продукции: Мясо, Рыба, Птица, и Овощи. У вас есть данные о прибыли, полученной от продажи каждого из видов продукции за четыре года (с 2004-го
по 2007-ой включительно) – рисунок 6.6. Если какие-то клетки в таблице
пусты, значит, в этом году вы не продавали ту или иную продукцию. Так,
начиная с 2006-го, со склада прекратилась продажа рыбы и началась продажа овощей.

Рис. 6.6. Таблица с данными из условия примера №2

Вам необходимо сравнить данные о продажах по каждому виду продукции
по годам.

6.2.1. РЕШЕНИЕ №1. ИСПОЛЬЗОВАНИЕ ГИСТОГРАММЫ
С ГРУППИРОВКОЙ ПО ВИДАМ ПРОДУКЦИИ

Вы можете построить гистограмму, у которой на горизонтальной оси будут
указанны виды продукции и по каждому виду продукции на гистограмме
будут отображен объем за каждый год – рисунок 6.7.
Для того чтобы построить такую диаграмму выполните следующие действия:
248
248

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.7. Гистограмма с данными, сгруппированными по видам продукции

1) Наберите таблицу, показанную на рис. 6.6 (или аналогичную ей для
вашего случая);
2) Выберите Вставка  Гистограмма (панель Диаграмма), далее выберите самую первую гистограмму из списка;
3) Щелкните правой кнопкой мыши по области построения диаграммы и в появившемся контекстном меню выберите вариант Выбрать
данные;
4) В появившемся окне нажмите кнопку Добавить в разделе Элементы легенды (ряда);
5) В появившемся окне выбора данных нажмите кнопку
(напротив поля Имя ряда) и, щелкнув левой кнопкой мыши по клетке со
;
значение года (заголовок столбца), нажмите
6) Теперь в том же окне нажмите
(напротив поля Значения) и выделите все данные в столбце, заголовок которого вы выбрали на предыдущем шаге. После этого нажмите
и кнопку ОК;
7) Повторяйте шаги 4 – 6, пока все столбцы не будут выбраны, после
чего переходите к шагу 8.
8) Нажмите кнопку Изменить в разделе Подписи горизонтальной оси
(категории), нажмите
, выделите столбец с названиями видов
продукции, нажмите
, после чего нажмите кнопку ОК;
249
249
249

Часть II. Анализ данных с помощью диаграмм и графиков

9) В окне Выбор источника данных нажмите кнопку ОК;
10) По своему усмотрению удалите легенду, добавьте подписи к данным
и другие необходимые вам для анализа элементы диаграммы.

6.2.2. РЕШЕНИЕ №2. ИСПОЛЬЗОВАНИЕ ГИСТОГРАММЫ С НАКОПЛЕНИЕМ
Вы можете построить гистограмму, в которой данные по каждому виду
продукции будут располагаться всего в одном столбце (по одному столбцу
на каждый вид продукции). Сами столбцы будут окрашены в несколько
цветов (в нашем случае число цветов будет равно числу лет, в течение
которых продавалась, рассматриваемая продукция) – рисунок 6.8, таким
образом, вы сэкономите много места на диаграмме. Этот способ подойдет вам
в том случае, когда у вас много наименований продукции, и при построении
гистограммы она не будет помещаться в видимой области рабочего листа.

Рис. 6.8. Гистограмма с накоплением в Excel 2010

Для того чтобы построить такую диаграмму, выберите Вставка 
Гистограмма (панель Диаграмма)  Гистограмма с накоплением (на рис.
6.9 в списке гистограмм выделены гистограммы с накоплением). Для
того чтобы выбрать данные для построения диаграммы, воспользуйтесь
способом, описанным в решении №1 к данному примеру.
250
250

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.9. Список гистограмм (выделены гистограммы с накоплением) в Excel 2010

ПРИМЕЧАНИЕ.
При применении решения №2 вы можете столкнуться со следующей проблемой
– сравниваемые данные будут немного отличаться друг от друга, и на диаграмме
вы не сможете увидеть разницы между ними. Поэтому используйте гистограмму
с накоплением, когда вы точно знаете, что сравниваемые данные в достаточной
степени отличаются друг от друга.

6.2.3. ИТОГИ
С помощью диаграмм, аналогичных построенным в примерах 1 и 2, вы сможете установить разность ваших данных в определенные периоды времени.

6.3. ÏÐÈÌÅÐ ¹3. ÂÛßÂËÅÍÈÅ ÎÏÐÅÄÅËÅÍÍÛÕ ÄÀÍÍÛÕ
Допустим, у вас есть данные о товарах (по группам) в процентах от общего объема продаж, и вам необходимо выделить (визуально) на диаграмме
объем продаж определенной продукции. Рассмотрим следующую таблицу
– рисунок 6.10 и предположим, что вам необходимо выделить объем продаж продуктов питания.

251
251
251

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.10. Таблица с данными из условия примера №3

6.3.1. РЕШЕНИЕ №1. ИСПОЛЬЗОВАНИЕ КРУГОВОЙ ДИАГРАММЫ
Решение состоит в том, чтобы построить круговую диаграмму и “отделить”
(на диаграмме) участок, отображающий объем продаж продуктов питания
– рисунок 6.11.

Рис. 6.11. Круговая диаграмма с “отделенным” сектором

Для того чтобы построить данную диаграмму, выполните следующее:
1) Наберите таблицу, показанную на рис. 6.10;
2) Выполните Вставка  Круговая (панель Диаграмма) -> Объемная круговая (в списке объемных круговых диаграмм она расположена первой);
3) Щелкните правой кнопкой мыши по области построения диаграммы, чтобы вызвать контекстное меню, и в появившемся списке выберите Выбрать данные;
4) В появившемся окне нажмите кнопку Добавить в разделе Элементы легенды (ряда);
5) В появившемся окне заполните поле Имя ряда. Затем нажмите
кнопку
в поле Значения и выделите строку, где указанны зна252
252

Глава 6. Анализ данных с помощью диаграмм и графиков

чения объемов продаж в процентах (вам необходимо выделить сами
цифры), после чего, нажмите кнопку
и кнопку ОК;
6) Нажмите кнопку Изменить в разделе Подписи горизонтальной оси
, выделите строку с наименованиями то(категории), нажмите
варов, нажмите
, после чего нажмите кнопку ОК;
7) В окне Выбор источника данных нажмите кнопку ОК;
8) Добавьте подписи данных, включив в них имена категорий и линии
выноски (если линии выноски включены не будут, то при удалении
подписей данных от диаграммы не будут построены линии, указывающие, какая подпись к какому элементу принадлежит);
9) Теперь щелкните левой кнопкой мыши непосредственно по самому кругу, отображающему данные – выделятся все элементы круга.
После этого щелкните левой кнопкой мыши по элементу, который
хотите выделить – вы можете видеть, что выделенным остался только нужный вам элемент (если вы случайно выбрали не тот элемент
— щелкните левой кнопкой мыши по нужному элементу, чтобы выбрать его). Теперь вам необходимо “оторвать” выбранный элемент
от всех остальных элементов. Для этого щелкните и удержите над
ним левую кнопку мыши. Теперь, не отпуская левую кнопку мыши,
“перетащите” элемент в нужное место и отпустите кнопку;
10) По необходимости удалите и/или добавьте различные элементы
диаграммы и поверните круг с данными под нужный вам угол (для
того чтобы получить диаграмму подобную той, что показана на рис.
6.11, удалите легенду и поверните диаграмму – значение поворота
по оси Y должно составлять 20 градусов).
ПРИМЕЧАНИЕ.
При необходимости вы можете отделить от круговой диаграммы несколько элементов. Если вы хотите построить круговую диаграмму, на которой все элементы
будут отделены друг от друга, при построении выберите один из типов разделенных круговых диаграмм.

6.3.2. РЕШЕНИЕ №2. ИСПОЛЬЗОВАНИЕ КОНТРАСТНОГО ЦВЕТА
Вот еще один способ привлечь внимание наблюдателя к нужному элементу
диаграммы – выбрать для этого элемента цвет, контрастирующий со всеми
остальными элементами диаграммы. На рис. 6.12 показан пример, когда все
элементы окрашены в белый цвет, и только элемент, который необходимо
выделить, окрашен в черный цвет.
253
253
253

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.12. Круговая диаграмма с выделенным при помощи
контрастного цвета элементом

Для того чтобы построить диаграмму, показанную на рис. 6.12, выполните
следующее:
1) Наберите таблицу, показанную на рис. 6.10;
2) Выберите Вставка  Круговая (панель Диаграмма)  Круговая
(в списке круговых диаграмм она расположена первой);
3) Выполните шаги 3 – 8 предыдущего решения;
4) Теперь щелкните по кругу на диаграмме, чтобы выделить все его
элементы. Зайдите на вкладку Формат. Выберите Заливка фигуры,
далее выберите белый цвет. Теперь вы можете видеть, что вся фигура стала белого цвета. Выберите Контур фигуры, далее выберите
черный цвет. Теперь “прорисовалась” граница круга. Теперь щелкните левой кнопкой мыши по элементу, который хотите выделить и
выберите Заливка фигуры, далее выберите черный цвет;
5) Удалите легенду.

6.3.3. ИТОГИ
Теперь вы умеете создавать круговые диаграммы с выделенным ключевым
элементом. При анализе данных это позволит вам привлечь внимание к
конкретной области ваших данных.

254
254

Глава 6. Анализ данных с помощью диаграмм и графиков

6.4. ÏÐÈÌÅÐ ¹4. ÑÐÀÂÍÅÍÈÅ ÄÀÍÍÛÕ Â ÃÐÀÔÈ×ÅÑÊÎÌ ÂÈÄÅ
Снова обратимся к ситуации, описанной в примере №2. Теперь задача стоит
та же, но вам необходимо построить кольцевую диаграмму, чтобы сравнить
данные о прибыли от продажи (в процентах от прибыли за год) мяса, рыбы
и птицы за 2004 и 2005 год – рисунок 6.13.

Рис. 6.13. Кольцевая диаграмма в Excel 2010

6.4.1. РЕШЕНИЕ
Рассмотрим (по шагам), как построить такую диаграмму.
1) Наберите таблицу из примера №2 – рисунок 6.14;
2) Выберите Вставка  Другие диаграммы (на панели Диаграммы) 
Кольцевая (выберите первый в списке кольцевых диаграмм тип);
3) Щелкните правой кнопкой мыши по области построения диаграммы, чтобы вызвать контекстное меню, и в появившемся списке выберите Выбрать данные;
4) В появившемся окне, нажмите кнопку Добавить в разделе Элементы легенды (ряда);
255
255
255

Часть II. Анализ данных с помощью диаграмм и графиков

5) В появившемся окне выбора данных нажмите кнопку
(напротив поля Имя ряда) и, щелкнув левой кнопкой мыши по клетке со
;
значение года (заголовок столбца), нажмите
6) Теперь в том же окне нажмите
(напротив поля Значения) и выделите все данные в столбце, заголовок которого вы выбрали на
предыдущем шаге. После этого нажмите
и кнопку ОК;
7) Повторяйте шаги 4 – 6, пока не выберете все столбцы, после чего переходите к шагу 8.
8) Нажмите кнопку Изменить в разделе Подписи горизонтальной оси
(категории), нажмите
, выделите столбец с названиями видов
продукции, нажмите

, после чего нажмите кнопку ОК;

9) В окне Выбор источника данных нажмите кнопку ОК;

Рис. 6.14. Окно настройки кольцевой диаграммы в Excel 2010

256
256

Глава 6. Анализ данных с помощью диаграмм и графиков

10) Расположите легенду в верхней части диаграммы, добавьте подписи
данных (исключите из подписей значения и включите доли);
11) Выделите ряды данных на диаграмме (должны быль выделены
только кольца) и на вкладке Формат (или Макет) нажмите кнопку
Формат выделенного фрагмента; вы увидите окно настройки кольцевой диаграммы (рис. 6.14).
В разделе настроек Параметры ряда данного окна вы можете настроить
следующие параметры:


Угол поворота первого сектора. С помощью ползунка (или указывая
числовое значение угла поворота) поверните кольца диаграммы под
нужным вам углом;



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



Диаметр отверстия, в % от общего диаметра. С помощью данного параметра вы можете изменять размер отверстия внутри кольцевой диаграммы (чем больше размер отверстия, тем меньше размер колец диаграммы и наоборот). Минимальное значение – 10% (размер отверстия
будет минимален), максимальное – 90% (размер отверстия будет максимален, зато сами кольца будут почти незаметны). Значения данного
параметра, так же как и предыдущие, задаются с помощью ползунка или
посредством указания конкретного числового значения (в процентах).

6.5. ÏÐÈÌÅÐ ¹5. ÀÍÀËÈÇ ÄÎËÅÉ
У вас есть большой объем данных о доле различных компаний на рынке
(пример – рисунок 6.15). Вам необходимо проанализировать, какие фирмы
обладают наибольшей долей (в деньгах) продаж на рынке.
Если теперь построить круговую диаграмму, вы не сможете даже увидеть
долей, отражающих суммарный объем продаж мелких фирм (с небольшой
долей денег на рынке) – рисунок 6.16.
257
257
257

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.15. Таблица с данными из условия примера №5

Рис. 6.16. Круговая диаграмма, на которой нельзя увидеть общие
данные мелких компаний

258
258

Глава 6. Анализ данных с помощью диаграмм и графиков

6.5.1. РЕШЕНИЕ
Как вы можете видеть, наибольшим объемом денежных средств на рынке
обладают три компании – нефтяная, газовая и компания по добыче драгоценных металлов. Остальные компании не столь значимы на рынке, поэтому объем денежных средств, которым они обладают по отдельности, вам не
важен. В таком случае удобнее всего объединить (просуммировать) объем
денежных средств всех незначимых компаний – рисунок 6.17.

Рис. 6.17. Таблица с объединенными данными о мелких компаниях

Если теперь построить диаграмму, то все будет видно, и при этом (что самое
главное), вы сможете спокойно увидеть долю больших компаний в сравнении с мелкими, которые теперь объединены в отдельную группу (см. рисунок 6.18).
259
259
259

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.18. Круговая диаграмма, построенная по таблице с объединенными данными
о мелких компаниях

6.6. ÏÐÈÌÅÐ ¹6. ÎÏÐÅÄÅËÅÍÈÅ ÄÎËÅÉ ÌÀËÛÕ ÊÎÌÏÀÍÈÉ
Воспользуемся примером, очень похожим на предыдущий (немного изменим таблицу с начальными условиями – рисунок 6.19). Теперь вам необходимо кроме определения доли больших компаний на рынке, проследить
долю малых компаний среди всех малых компаний рынка.
Как вы уже могли убедиться – проследить долю мелких компаний на рынке
с помощью обычной круговой диаграммы практически невозможно. Поэтому в данном примере мы построим вторичную круговую диаграмму (в такой
диаграмме строится основная круговая диаграмма, а затем для одной из долей диаграммы строится еще одна круговая диаграмма, отображающая детали выбранной доли первой диаграммы). Вторичную круговую диаграмму
для нашего случая вы можете видеть на рис. 6.19.

6.6.1. РЕШЕНИЕ
Теперь поговорим о том, как строить вторичные круговые диаграммы, а также об их настройках. Начнем с построения:
1) Наберите таблицу, показанную на рис. 6.19;
260
260

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.19. Вторичная круговая диаграмма в Excel 2010 и таблица
с данными к примеру №6

2) Выберите Вставка  Круговая (на панели Диаграммы)  Вторичная круговая (она расположена третьей в списке круговых диаграмм);
3) Щелкните правой кнопкой мыши по области построения диаграммы, чтобы вызвать контекстное меню, и в появившемся списке выберите Выбрать данные;
4) В появившемся окне нажмите кнопку Добавить в разделе Элементы легенды (ряда);
(напротив поля
5) В появившемся окне выбора данных нажмите
Значения) и выделите все данные в столбце, заголовок которого вы
выбрали на предыдущем шаге. После этого нажмите
и кнопку
ОК;
6) Нажмите кнопку Изменить в разделе Подписи горизонтальной оси
(категории), нажмите
, выделите столбец с названиями компаний, нажмите
, после чего нажмите кнопку ОК;
261
261
261

Часть II. Анализ данных с помощью диаграмм и графиков

7) В окне Выбор источника данных нажмите кнопку ОК;
8) Добавьте подписи данных. Удалите из подписей значения, добавьте
доли и имена категорий;
9) Щелкните левой кнопкой мыши по диаграмме так, чтобы выделить сами круги. После этого щелкните по одному из кругов правой
кнопкой мыши, чтобы вызвать контекстное меню. В появившемся
меню выберите Формат ряда данных, вы увидите окно настроек. В
поле Разделить ряд выберите Положение и в поле Вторая область
содержит первую укажите значение 6. В окне настроек нажмите
кнопку Закрыть

Теперь поговорим о настройках параметров ряда для вторичной круговой
диаграммы.

262
262



Параметр Разделить ряд задает, как будут разделены данные
для построения основного и вторичного круга. Данный параметр может принимать четыре значения – Положение, Значение, Процент и Другое. Выбрав значение Положение, вам будет необходимо указать, начиная с какого элемента по номеру
(снизу) разделить данные. Все элементы, начиная с элемента
под указанным номером (снизу), попадут во вторичный круг,
выше указанного элемента – в первичный. Выберите Значение,
если хотите во вторичный круг поместить значения, меньшие
указанного вами (Вторая область содержит значения меньше
и укажите нужное число). Если вы хотите во вторичный круг
поместить значения, которые меньше указанного значения процентов от общего объема, в качестве значения параметра Разделить ряд выберите Процент и укажите необходимое значение в
процентах. Вы также можете выбрать параметр Другое, в этом
случае вы сможете выбрать любую точку на диаграмме (кроме
точки Другой) и указать, к какому кругу он будет относиться
– это удобно, когда вам нужно во вторичную диаграмму поместить несмежные значения из таблицы (появится параметр Точка
относится к, выбрав значение которого вы отнесете выбранную
точку к тому или иному кругу).



Параметр Разрезанная круговая диаграмма. С эти параметром
мы уже знакомы – с его помощью вы можете “разрезать” области построения (круги) на отдельные элементы.

Глава 6. Анализ данных с помощью диаграмм и графиков



С помощью параметра Боковой зазор вы можете указать расстояние от первого до второго круга. Значение указывается в процентах (от 0 до 500). Данным параметром вы можете управлять с
помощью ползунка или вводя конкретное числовое значение.



Параметр Размер второй области построения. По умолчанию
первая область построения больше второй, но вы можете сделать
их равными или сделать так, чтобы вторая область построения
была больше первой. Значения данного параметра изменяются в
процентах и изменяются от 0 до 200. Если вы укажете значение
100 – обе области построения будут одинаковых размеров, если
меньше 100 – первая область построения будет больше второй,
если больше 100 – вторая область построения будет больше, чем
первая.

ПРИМЕЧАНИЕ.
Стоит отметить, что какой бы вариант вы ни выбрали – в первичной области построения, кроме всех точек, указанных вами, будет еще одна – Другой. Эта точка добавляется автоматически (ее значение равно сумме значений всех точек во второй
области построения).

В качестве альтернативы вторичной круговой диаграмме вы можете выбрать вторичную гистограмму – рисунок 6.20.

Рис. 6.20. Вторичная гистограмма в Excel 2010

263
263
263

Часть II. Анализ данных с помощью диаграмм и графиков

Стоит отметить случаи, когда лучше использовать тот или иной тип диаграммы:
1) Используйте обычную круговую диаграмму, когда вам необходимо
проанализировать от 2 до 5 значений;
2) Вторичную круговую диаграмму следует использовать, когда вы
анализируете от 6 до 10 значений;
3) Вторичную гистограмму следует использовать, когда вы сравниваете от 6 до 15 значений;
4) Используйте кольцевую диаграмму, когда хотите сравнить данные
за 2 периода (например, данные о продажах за текущий и предыдущий год); в остальных случаях (когда периодов больше двух) лучше
использовать одну из гистограмм с накоплением.

6.7. ÏÐÈÌÅÐ ¹7. ÀÍÀËÈÇ ÑÂßÇÈ ÌÅÆÄÓ ÄÂÓÌß ÔÀÊÒÎÐÀÌÈ
У вас есть данные о количестве автомобилей в населенных пунктах. Данные о количестве автомобилей привязаны к удаленности населенных пунктов от вашего регионального центра – рис. 6.21. Значение 0 в первой строчке означает, что в ней содержатся данные о количестве автомобилей в региональном центре.

Рис. 6.21. Таблица с данными из условия примера №7

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

Глава 6. Анализ данных с помощью диаграмм и графиков

оси будут отложены значения количества автомобилей в этих населенных
пунктах – рисунок 6.22.

Рис. 6.22. Точечная диаграмма в Excel 2010

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

6.7.1. РЕШЕНИЕ
Рассмотрим по шагам как построить такую диаграмму:
1) Наберите таблицу, показанную на рис. 6.21;
2) Выделите область A1:B10 таблицы. Для этого щелкните и зажмите левую кнопку мыши на ячейке A1, переместите курсор мыши в
ячейку B10 и отпустите левую кнопку мыши;
3) Войдите на вкладку Вставка и выберите Точечная (на панели Диаграммы)  Точечная с маркерами (она первая в списке);
4) Щелкните по легенде, чтобы выделить ее, и нажмите клавишу Delete, чтобы удалить ее;
5) Щелкните по полю названия диаграммы, чтобы выделить его, и нажмите клавишу Delete, чтобы удалить его;
265
265
265

Часть II. Анализ данных с помощью диаграмм и графиков

6) Уменьшите размер области построения диаграммы так, чтобы освободилось место для подписей к осям диаграммы;
7) Войдите на вкладку Вставка и добавьте на диаграмму две надписи (одну около горизонтальной оси, другую около вертикальной).
Для этого нажмите кнопку Надпись (в правом верхнем углу панели Вставка) и щелкните левой кнопкой мыши в нужном месте
диаграммы. После этого введите необходимые названия осей и при
необходимости сделайте шрифт полужирным (Ctrl + B для включения/выключения режима написания текста полужирным шрифтом).
Итак, теперь вы научились строить точечные диаграммы. В следующем
примере мы построим точечную диаграмму с двумя рядами данных.

6.8. ÏÐÈÌÅÐ ¹8. ÀÍÀËÈÇ ÄÎÏÎËÍÈÒÅËÜÍÎÃÎ ÐßÄÀ ÄÀÍÍÛÕ
Допустим, у вас есть предприятие занимающееся заготовкой леса. У вас
есть два варианта – заготавливать на делянке круглый лес, затем продавать
его на месте (т.е. покупатель сам заботится о транспортировке и переработке леса), либо переработать лес на сортименты и доставить их непосредственно к покупателю. У вас есть данные о шести делянках – ваши затраты
для первого и второго случаев (рис. 6.23). Вам необходимо проанализировать – насколько сильно будут отличаться затраты при реализации первого
и второго вариантов заготовки и реализации леса.

Рис. 6.23. Таблица с данными из условия примера №8

Если теперь построить по имеющимся данным точечную диаграмму, то
можно сразу увидеть разницу в затратах при реализации первого и второго
вариантов заготовки и реализации леса – рисунок 6.24.
266
266

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.24. Точечная диаграмма с двумя рядами данных в Excel 2010

6.8.1. РЕШЕНИЕ
Рассмотрим как построить такую диаграмму.
1) Наберите таблицу, показанную на рис. 6.23;
2) Выделите область A1:C7 таблицы. Для этого щелкните и зажмите левую кнопку мыши на ячейке A1, переместите курсор мыши в
ячейку C7 и отпустите левую кнопку мыши;
3) Войдите на вкладку Вставка и выберите Точечная (на панели Диаграммы)  Точечная с маркерами (она первая в списке);
4) Войдите на вкладку Макет и выберите Легенда  Добавить легенду сверху.
Строя подобные диаграммы для двух, трех (и более) рядов данных, вы
сможете проанализировать, вокруг какого значения сосредоточены данные в каждом ряду. Если же вы знаете, что данные в каждом ряду имеют
большой разброс (внутри ряда), воспользуйтесь для анализа другим типом
диаграмм(ы).

6.9. ÏÐÈÌÅÐ ¹9. ÂÛßÂËÅÍÈÅ ÇÀÊÎÍÀ ÐÀÑÏÐÅÄÅËÅÍÈß
ÄÀÍÍÛÕ
Обратимся к примеру №7. Теперь помимо того, что вам необходимо установить зависимость между числом автомобилей и удаленностью от регионального цента, вам необходимо установить (приблизительно) по какому
267
267
267

Часть II. Анализ данных с помощью диаграмм и графиков

закону распределены данные и насколько приближены рассматриваемые
данные к этому закону.

6.9.1. РЕШЕНИЕ
Для того чтобы решить поставленную задачу, выполните построение диаграммы, как это описано в примере №7, или, если у вас сохранился файл с
решением к примеру №7, откройте готовую диаграмму и проделайте следующие шаги:
1) Войдите на вкладку Макет и выполните Анализ  Линия тренда
 Экспоненциальное приближение (если внимательно посмотреть
на диаграмму, показанную на рис. 6.22, то несложно заметить,что
данные на диаграмме близки по распределению к экспоненциальному
закону );
2) Теперь щелкните левой кнопкой мыши по только что добавленной
линии тренда и нажмите кнопку Формат выделенного фрагмента;
3) В появившемся окне настроек поставьте галочки напротив
Показывать уравнение на диаграмме и напротив Поместить на
диаграмме величину доверенности аппроксимации R^2, и нажмите
кнопку Закрыть.
Теперь вы можете посмотреть, по какой функции (предположительно) распределены ваши данные (y=[функция]). А вот оценить,
насколько близко (к предполагаемой функции) расположены ваши
данные, вы сможете с помощью значения R2. R2 может принимать
значения от нуля до единицы. Чем ближе значение R2 к нулю, тем
менее степень распределения данных по предполагаемому закону;
чем ближе значение R2 к единице, тем больше предполагаемый закон распределения данных подходит для описания реального закона
распределения. Если значение R2 равно нулю, то реальное распределение данных не соответствует предполагаемому их распределению
(в большинстве случаев будет означать, что данные распределены
хаотично/случайно). Если значение R2 равно единице, значит, реальный закон распределения совпадает с предполагаемым.
ПРИМЕЧАНИЕ.
О точечных диаграмм следует также сказать, что вы можете поменять вид одного
из маркеров (точки) на графике. Это будет полезно, когда весь ряд значений расположен вдоль одного числа (или функции) и только одно (или малое число точек
ряда) число отклоняется от конкретного значения (функции). Для того чтобы поменять вид маркера, вам необходимо сделать следующее.

268
268

Глава 6. Анализ данных с помощью диаграмм и графиков

1) Щелкните левой кнопкой мыши по маркеру, который хотите изменить – выделятся все маркеры, поэтому щелкните по нему еще раз
– теперь выделится только нужный вам маркер;
2) На вкладке Макет (или Формат) нажмите кнопку Формат выделенного фрагмента – откроется окно настройки маркера;
3) Зайдите в раздел настроек Параметры маркера и для единственного параметра выберите значение Встроенный (значение Авто – маркер будет таким же, как и все остальные, значение Нет – маркер не
будет отображаться на диаграмме). Укажите необходимый тип и
размер маркера;
4) При необходимости зайдите в раздел настроек Заливка маркера
и укажите тип и цвет заливки. Как вы можете заметить, в данном
разделе появился новый параметр – Разноцветные точки. Если вы
поставите галочку напротив его названия, все маркеры на диаграмме
будут разного типа и разных цветов (если маркеров будет слишком
много – типы маркеров будут повторяться, но для каждого повторения типа будет выбран новый цвет заливки маркера).

6.10. ÏÐÈÌÅÐ ¹10. ÏÎÑÒÐÎÅÍÈÅ ÃÐÀÔÈÊÀ ÔÓÍÊÖÈÈ
Иногда вам может потребоваться построить график некоторой заданной
функции. Разберем случай, когда вам необходимо построить график функции f(x)=sin(x) на интервале [-1; 3].

6.10.1. РЕШЕНИЕ
Для начала заполните таблицу с данными функции – рисунок 6.25.
Как вы можете видеть, в первом столбце расположены значения X, во
втором – функции f(x)=sin(x). Для начала разберем, как заполнить такую
таблицу.
1) В ячейку A1 введите значение -1, в ячейку A2 введите значение -0,9.
Теперь выделите ячейки A1:A2. Для этого щелкните и зажмите левую кнопку мыши на ячейке A1, переместите курсор мыши в ячейку
A2 и отпустите левую кнопку мыши. Как вы можете видеть, в правом
нижнем углу области выделения есть маленький квадратный маркер. Наведите курсор мыши на этот маркер, нажмите и удерживайте
левую кнопку мыши. Теперь, продолжая удерживать левую кнопку
мыши, перемещайте маркер вниз – рядом с ним будут появляться
269
269
269

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.25. Фрагмент таблицы с данными из условия примера №10

значения (-0.8; -0.7; -0.6 и т.д.); ведите маркер вниз до тех пор, пока
рядом с ним не появится значение 3, после этого отпустите левую
кнопку мыши;
2) Теперь щелкните левой кнопкой мыши по ячейке B1 и введите в ней
формулу, для этого наберите: =sin(A1). После этого нажмите клавишу Enter;
3) Щелкните левой кнопкой мыши по ячейке B1. Аналогично тому,
как это описано в пункте 1, переместите маркер, расположенный в
правом нижнем углу области выделения, в клетку B41 (как раз напротив значения 3 в столбце A);
ПРИМЕЧАНИЕ.
В пункте 1 описан метод заполнения столбца, в котором будут храниться значения
X. Для заполнения данных были выбраны значения -1 и -0.9. Так, получилось, что
значения по оси X берутся с шагом 0.1. Если вы хотите задать другой шаг, вам необходимо в клетку A2 ввести значение, отстоящее от значения в клетке A1 на величину шага (-1 и -0.8 – шаг 0.2; -1 и -0.5 – шаг 0.5; -1 и 0.999 – шаг 0.001 и т.д.).

Теперь, когда таблица заполнена, построим по имеющимся данным график
функции f(x)=sin(x) – рисунок 6.26.
Разберем, как построить такой график.
1) Выберите Вставка  График (на вкладке Диаграммы)  График с
маркерами;
270
270

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.26. График функции f(x)=sin(x) на интервале [-1; 3] в Excel 2010

2) Щелкните правой кнопкой мыши по области построения диаграммы (графика) и в появившемся меню выберите Выбрать данные.
Если в появившемся окне в списке Элементы легенды (ряда) есть
ряд или ряды, удалите их. Нажмите кнопку Добавить напротив заголовка Элементы легенды (ряда). Нажмите кнопку
напротив
поля Значения. Выделите столбец B1:B41 и нажмите кнопку
и
затем кнопку ОК. Теперь в окне Выбор источника данных нажмите кнопку Изменить напротив пункта Подписи горизонтальной
оси (категории). Нажмите кнопку
и выделите столбец A1:A41,
после чего нажмите
. В окне Выбор источника данных нажмите
кнопку ОК;
3) Щелкните левой кнопкой мыши по легенде, чтобы выделить ее, и
удалите ее (клавиша Delete);
4) Теперь щелкните по одной из горизонтальных линий сетки, чтобы
выделить все линии, и удалите их (клавиша Delete);
5) Щелкните левой кнопкой мыши по горизонтальной оси, чтобы
выделить ее. Зайдите на вкладку Макет (или Формат) и нажмите
кнопку Формат выделенного фрагмента. В появившемся окне настроек зайдите в раздел Тип линии и выберите в качестве типа окончания стрелку. Зайдите в раздел Параметры оси и для настройки
вертикальная ось пересекает выберите в категории с номером и
укажите значение 11 (это делается для того, чтобы вертикальная
ось пересекала горизонтальную в точке 0, а в нашем ряду данных 0
стоит на 11-м месте). В окне настроек нажмите кнопку Закрыть;
271
271
271

Часть II. Анализ данных с помощью диаграмм и графиков

6) Щелкните левой кнопкой мыши по вертикальной оси, чтобы выделить ее, и нажмите кнопку Формат выделенного фрагмента. В появившемся окне настроек зайдите в раздел Тип линии и в качестве
типа окончания выберите стрелку. В окне настроек нажмите кнопку
Закрыть;
7) Уменьшите размер диаграммы так, чтобы осталось место для подписей к осям и функции;
8) Зайдите на вкладку Вставка и нажмите кнопку Надпись. Щелкните
по области построения графика (диаграммы) в месте, куда хотите
вставить надпись, и укажите необходимую подпись. Таким образом
вставьте названия осей и подпись к функции.
ПРИМЕЧАНИЕ.
Для построения графиков функций вы можете выбрать следующие типы диаграммы – Точечная с прямыми отрезками и Точечная с гладкими кривыми и маркерами. На рис. 6.27 показаны оба типа диаграмм для функции f(x)=sin(x) на отрезке [-1; 3] (точки для построения графика взяты с шагом 0.5).

Рис. 6.27. График функции f(x)=sin(x) на отрезке [-1; 3]

272
272

Глава 6. Анализ данных с помощью диаграмм и графиков

6.11. ÏÐÈÌÅÐ ¹11. ÀÍÀËÈÇ ÃÐÓÏÏ ÏÎÒÐÅÁÈÒÅËÅÉ
В данном примере мы рассмотрим случай, когда вам необходимо сравнить
данные о потреблении нескольких видов продукции детьми и взрослыми.
Посмотрите на рис. 6.28. Данные в таблице указаны в процентах. Как вы
можете видеть, в столбце, где представлены данные о потреблении товаров
взрослыми, все значения отрицательные. Это сделано для построения диаграммы, рассматриваемой в данном примере.

Рис. 6.28. Таблица с данными из условия примера №11

Итак, вам необходимо сравнить данные о доле потребления некоторых продуктов детьми и взрослыми. Для данного случая можно построить диаграмму, показанную на рис. 6.29.

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

273
273
273

Часть II. Анализ данных с помощью диаграмм и графиков

Представив данные таким образом, вам будет очень удобно оценить – какие
продукты больше потребляют дети, какие – взрослые. Например, взглянув
на диаграмму, можно сразу сказать, что дети потребляют сладостей больше, чем взрослые, а вот алкогольную продукцию (в нашем примере – пиво)
вообще не потребляют. Также вы можете заметить, что подписи на горизонтальной оси, обозначающие процент потребления продуктов взрослыми, имеют отрицательное значение (это правильно, так как в таблице эти
значения отрицательны). О том, как сделать так, чтобы отрицательные значения показывались без знака “минус”, мы поговорим чуть позже. А сейчас
рассмотрим, как построить данную диаграмму.

6.11.1. РЕШЕНИЕ
1) Наберите таблицу, показанную на рис. 6.28;
2) Выберите Вставка  Линейчатая (на вкладке Диаграммы)  Линейчатая с накоплением;
3) Щелкните правой кнопкой мыши по диаграмме, чтобы вызвать контекстное меню, в появившемся меню выберите Выбрать данные. В
открывшемся окне нажмите кнопку Добавить. В открывшемся окне
нажмите
напротив поля Значение выделите все значения в
столбце Дети, нажмите кнопку

. Нажмите кнопку

напротив

поля Имя ряда, щелкните левой кнопкой мыши по ячейке с названием столбца, нажмите кнопку
, нажмите кнопку ОК;
4) В окне Выбор источника данных нажмите кнопку Добавить. В открывшемся окне нажмите
напротив поля Значение, выделите все значения в столбце Взрослые, нажмите кнопку
. Нажмите кнопку
напротив поля Имя ряда, щелкните левой кнопкой
мыши по ячейке с названием столбца, нажмите кнопку
, нажмите кнопку ОК;
5) Нажмите кнопку Изменить напротив Подписи к горизонтальной
оси. Нажмите кнопку
и выделите названия продуктов в столбце
Продукты, нажмите кнопку
и кнопку ОК. В окне Выбор источника данных нажмите кнопку Закрыть.
6) Зайдите на вкладку Макет. Выполните Легенда  Добавить легенду сверху;
7) Щелкните по горизонтальной оси, чтобы выделить ее. Нажмите Ctrl
+ 1, чтобы открыть окно настроек. В разделе настроек Параметры
оси для параметра минимальное значение выберите вариант фик274
274

Глава 6. Анализ данных с помощью диаграмм и графиков

сированное и укажите значение -100. Для параметра Вертикальная
ось пересекает укажите вариант Значение оси и введите значение
-100, нажмите кнопку Закрыть.
Теперь у вас должна получиться диаграмма похожая на ту, что показана на
рис. 6.29. Теперь осталось сделать так чтобы на горизонтальной оси слева и справа от нуля стояли положительные числа. Для этого вам необходимо войти в окно настроек горизонтальной оси (смотрите шаг №7 из решения данного примера). В разделе Числа настроек для параметра Числовые форматы укажите значение Все форматы, в поле Код формата укажите 0;0 и нажмите кнопку Добавить. После чего выберите только что добавленное значение из списка Тип и нажмите кнопку Закрыть. Теперь, вы можете убедиться, что на горизонтальной оси справа и слева от нуля стоят положительные числа. Казалось бы, показывать на оси отрицательные числа
без знака минус не имеет смысла, но данный пример наглядно показывает,
что бывают случаи, когда это необходимо. Также стоит отметить, что после
добавления типа 0;0 числовым форматам вам не надо будет добавлять его
каждый раз, когда вам понадобится выбрать его. Теперь заданный вами тип
формата чисел будет в списке постоянно (до тех пор, пора пока вы не удалите Microsoft Office).

6.12. ÏÐÈÌÅÐ ¹12. ÎÒÎÁÐÀÆÅÍÈÅ ÄÂÓÕ ÂÈÄÎÂ ÏÎÊÀÇÀÒÅËÅÉ
Рассмотрим, теперь, пример, когда у вас есть данные о суммарной стоимости
товаров (по списку) и реальной стоимости, за которую они были проданы
(имеется в виду, что на покупку товаров некоторым людям/организациям
предоставляется скидка) – рисунок 6.30.

Рис. 6.30. Таблица с данными из условия примера №12

275
275
275

Часть II. Анализ данных с помощью диаграмм и графиков

Вам необходимо вычислить процент скидки (средний, так как для разных
людей/организаций величина скидки разная) и показать на диаграмме
величину скидки и реальную стоимость от продажи товара – рисунок 6.31.

Рис. 6.31. Диаграмма величины скидки на товары и стоимости, по которой
они были проданы

6.12.1. РЕШЕНИЕ
Для того чтобы построить такую диаграмму, как показано на рис. 6.31, вам
будет необходимо построить две диаграммы, которые будут выглядеть как
одна “двойная” диаграмма. Рассмотрим, как построить такую диаграмму.
Для начала необходимо заполнить таблицу. В таблице, показанной на рис.
6.30, величина скидки уже вычислена. Заполните таблицу в таблице столбцы Наименование, Стоимость без скидки, Получено при реализации, а в
столбце Скидка заполните только заголовок. Теперь щелкните левой кнопкой мыши по ячейке D2 и впишите в нее следующую формулу: =((B2-C2)/
B2)*100, после чего нажмите клавишу Enter. Теперь снова щелкните левой
кнопкой мыши по ячейке D2, чтобы выделить ее. Удерживая левую кнопку
мыши, переместите маркер, который расположен в правом нижнем углу области выделения, в ячейку D10. Теперь притупим к построению “двойной”
диаграммы.
1) Зайдите на вкладку Вставка и выберите Линейчатая (на панели
Диаграммы)  Линейчатая с накоплением;
2) Щелкните правой кнопкой мыши по области построения диаграммы
и в появившемся контекстном меню выберите Выбрать данные;
3) В окне Выбор источника данных нажмите кнопку Добавить. Нажмите кнопку
напротив поля Значение и выделите столбец со
значением скидок на товар. Нажмите кнопку
276
276

и кнопку ОК.

Глава 6. Анализ данных с помощью диаграмм и графиков

4) В окне Выбор источника данных нажмите кнопку Изменить в разделе Подписи горизонтальной оси (категории), нажмите кнопку
, выделите столбец с наименованиями товаров, нажмите кнопку
и кнопку ОК. В окне Выбор источника данных нажмите кнопку Закрыть;
5) Щелкните по легенде и нажмите клавишу Delete, чтобы удалить ее;
6) Щелкните по области построения диаграммы так, чтобы выделилась
сама диаграмма, а не вся область диаграммы, и с помощью верхнего
среднего маркера изменения размера диаграммы “сожмите” ее;
7) В правом верхнем углу диаграммы добавьте подпись – “Скидка, %”.
Для этого, войдите на вкладку Вставка, нажмите кнопку Надпись,
щелкните левой кнопкой мыши в правом верхнем углу диаграммы,
чтобы вставить надпись, и наберите текст подписи;
8) Щелкните по краю диаграммы, чтобы выделить всю область диаграммы. Зайдите на вкладку Макет (или Формат) и нажмите кнопку Формат выделенного фрагмента;
9) В окне Формат области диаграммы, зайдите в раздел Цвет границы
настроек, выберите вариант Нет линии и нажмите кнопку Закрыть;
10) Теперь создадим копию построенной диаграммы и сделаем это так,
чтобы обе диаграммы находились рядом друг с другом и на одном
и том же уровне. Для этого наведите курсор мыши на диаграмму
и зажмите левую кнопку мыши, затем (не отпуская левую кнопку
мыши) нажмите и удерживайте Ctrl + Shift. После чего, не отпуская
всех зажатых кнопок, ведите курсор мыши влево, пока правый край
копии нашей диаграммы не совпадет с левым краем оригинала диаграммы. Теперь отпустите левую кнопку мыши, а потом сочетание
Ctrl + Shift (как вы могли заметить, когда вы зажимаете Ctrl + Shift,
создается копия выделенного объекта и, при перемещении, копия
может двигаться только по горизонтали, таким образом, объекты
– исходный и копия, остаются на одном уровне);
11) Щелкните правой кнопкой мыши по левой диаграмме и в появившемся списке выберите Выбрать данные. В окне Выбор источника
данных нажмите кнопку Изменить в разделе Элементы легенды
(ряда). Нажмите кнопку
напротив поля Значение, выделите
значения столбца Получено при реализации, нажмите кнопку
и кнопку ОК;
277
277
277

Часть II. Анализ данных с помощью диаграмм и графиков

12) В окне Выбор источника данных нажмите кнопку ОК;
13) Щелкните левой кнопкой мыши по области подписей к вертикальной оси и нажмите клавишу Delete;
14) Переместите подпись к диаграмме в левый верхний угол и поменяйте ее текст на “Получено при реализации”;
15) Щелкните левой кнопкой мыши по горизонтальной оси, чтобы выделить ее. Нажмите Ctrl + 1, чтобы открыть окно настроек. В разделе Параметры оси поставьте галочку напротив обратный порядок
значений и нажмите кнопку Закрыть.

6.13. ÏÐÈÌÅÐ ¹13. ÀÍÀËÈÇ ÄÎËÈ ÄÎÕÎÄÀ ÏÐÅÄÏÐÈßÒÈß
У вас есть данные о доходах некоторого крупного предприятия и о
доходах всей промышленности, частью которой является рассматриваемое
предприятие. Вам необходимо сравнить данные о доходах от продажи трех
видов продукции вашего предприятия с доходами от продажи (той же
продукции) всей промышленности. Предприятие сбывает продукцию в
Евросоюзе, России и Китае – рисунок 6.32.

Рис. 6.32. Таблица с данными из условия примера №13

6.13.1. РЕШЕНИЕ
В данном случае очень удобно построить “двойную” диаграмму, на которой
будут изображены линейчатые диаграммы, отображающие данные предприятия и всей промышленности, чтобы можно было легко сравнить их
– рисунок 6.33.
278
278

Глава 6. Анализ данных с помощью диаграмм и графиков

Рис. 6.33. “Двойная” диаграмма в Excel 2010

Теперь давайте рассмотрим, как построить данную диаграмму. В предыдущем примере мы уже строили похожую диаграмму, но т.к. такой способ построения очень интересен, и при этом, к сожалению, понимается не сразу,
— рассмотрим еще раз, как это сделать. Итак, приступим:
1) Наберите таблицу, показанную на рис. 6.32;
2) Выделите данные A1:D5 в таблице. Для этого наведите курсор на
ячейку A1 и зажмите левую кнопку мыши. Затем, не отпуская левую
кнопку мыши, переместите курсор в клетку D5 и отпустите кнопку;
3) Выберите Вставка  Линейчатая (на вкладке Диаграммы) Линейчатая с группировкой (она самая первая в списке);
4) Щелкните по диаграмме, чтобы выбрать ее;
5) Зайдите на вкладку Макет и выберите Легенда  Добавить легенду сверху;
6) Выберите Оси  Основная вертикальная ось  Дополнительные
параметры основной вертикальной оси и в появившемся окне настроек поставьте галочку напротив пункта Обратный порядок категорий, затем закройте окно настроек;
7) Щелкните левой кнопкой мыши по диаграмме так, чтобы выбрать
всю диаграмму. После этого щелкните правой кнопкой мыши по
диаграмме, чтобы вызвать контекстное меню. В контекстном меню
выберите Формат области диаграммы. В появившемся окне, зайдите в раздел настроек Цвет границы и выберите вариант Нет линий;
279
279
279

Часть II. Анализ данных с помощью диаграмм и графиков

8) Теперь создадим копию построенной диаграммы и сделаем это так,
чтобы обе диаграммы находились рядом друг с другом и на одном
и том же уровне. Для этого наведите курсор мыши на диаграмму
и зажмите левую кнопку мыши, затем (не отпуская левую кнопку
мыши) нажмите и удерживайте Ctrl + Shift. После чего, не отпуская
всех зажатых кнопок, ведите курсор мыши влево, пока правый край
копии нашей диаграммы не совпадет с левым краем оригинала диаграммы. Теперь отпустите левую кнопку мыши, а потом сочетание
Ctrl + Shift;
9) Теперь щелкните по левой диаграмме правой кнопкой мыши и в появившемся меню выберите пункт Выбрать данные. В появившемся
напротив Диапазон данных для диаграмокне нажмите кнопку
мы, выделите область A7:D11 и нажмите
ника данных, нажмите кнопку ОК;

. В окне Выбор источ-

10) Щелкните по области подписей к вертикальной оси левой диаграммы и нажмите клавишу Delete, чтобы удалить подписи;
11) Удалите легенду с области левой диаграммы;
12) Как вы видите, область построения диаграммы (левой) увеличилась
в соответствии с размерами области диаграммы. Для того чтобы изменить размер области построения левой диаграммы в соответствии
с размером правой диаграммы, щелкните левой кнопкой мыши по
области построения левой диаграммы и, перемещая правый верхний маркер изменения размера, уменьшите размер области построения левой диаграммы;
13) На вкладке Макет выберите Оси  Основная горизонтальная
ось  Дополнительные параметры основной горизонтальной оси
(должна быть выбрана левая диаграмма, либо один из ее компонентов). В появившемся окне, в разделе Параметры оси поставьте галочку напротив пункта Обратный порядок значений и нажмите
кнопку Закрыть;
14) Зайдите на вкладку Вставка, нажмите кнопку Надпись и щелкните
левой кнопкой мыши в левом углу левой диаграммы. Введите необходимую подпись к диаграмме;
15) Щелкните левой кнопкой мыши по правой диаграмме, чтобы выделить ее. Зайдите на вкладку Макет и выберите Оси  Основная
горизонтальная ось  Дополнительные параметры основной горизонтальной оси. В появившемся окне настроек зайдите в раздел
Параметры оси. Для параметра максимальное значение выберите
280
280

Глава 6. Анализ данных с помощью диаграмм и графиков

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

6.14. ÏÐÈÌÅÐ ¹14. ÀÍÀËÈÇ ÄÀÍÍÛÕ ÏÎ ÒÐÅÌ ÏÀÐÀÌÅÒÐÀÌ
Разберем пример, когда вам необходимо оценить ту или иную отрасль по
трем параметрам. Например, продажа автомобилей – будем оценивать три
параметра: год выпуска автомобиля, его пробег и цену. Вам необходимо оценить, как изменяется цена автомобиля в зависимости от года выпуска и пробега. Посмотрите на рисунок 6.34.

Рис. 6.34. Таблица с данными из условия примера №14

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

Часть II. Анализ данных с помощью диаграмм и графиков

Рис. 6.35. Пузырьковая диаграмма в Excel 2010

гом. Также можно сказать и о том, что более новый автомобиль стоит столько же, сколько и старый (за счет того, что у более нового автомобиля пробег больше).

6.14.1. РЕШЕНИЕ
Итак, разберем, как построить пузырьковую диаграмму.
1) Наберите таблицу, показанную на рис. 6.34;
2) Войдите на вкладку Вставка и выберите Другие диаграммы (на панели Диаграммы)  Объемная пузырьковая (она вторая в списке
пузырьковых диаграмм);
3) Щелкните по области диаграммы правой кнопкой мыши, чтобы вызвать контекстное меню, и щелкните по строчке Выбрать данные;
4) В открывшемся окне, нажмите кнопку Добавить (Элементы легенды (ряда));
5) В окне выбор источника данных, нажмите кнопку
напротив
пункта Значения X, выделите столбец Пробег (C5:C11), нажмите
кнопку
и нажмите кнопку ОК
282
282

Глава 6. Анализ данных с помощью диаграмм и графиков

6) Нажмите кнопку
напротив пункта Значения Y, выделите столбец Год выпуска (B5:B11), нажмите кнопку
и нажмите кнопку
ОК;
7) Нажмите кнопку
напротив пункта Размеры пузырьков, выделите столбец Цена (B5:B11), нажмите кнопку
и нажмите кнопку
ОК ;
8) Щелкните левой кнопкой мыши по легенде, чтобы выделить ее и
удалите ее (клавиша Delete);
9) Щелкните левой кнопкой мыши по горизонтальной оси, чтобы выделить ее и нажмите Ctrl + 1, чтобы открыть окно настроек. Для
параметра Вертикальная ось пересекает выберите Значение оси и
укажите число -200000. В окне настроек нажмите кнопку Закрыть;
10) Щелкните левой кнопкой мыши по одному из пузырьков, чтобы
выделить все пузырьки, и нажмите Ctrl + 1, чтобы открыть окно
настроек. Зайдите в раздел Формат объемной фигуры настроек и
выберите понравившийся вам макет объемной фигуры. В окне настроек нажмите кнопку Закрыть;
11) Зайдите на вкладку Макет и выберите Подписи данных  В центре. Щелкните по одной из подписей к пузырькам и нажмите Ctrl + 1,
чтобы открыть окно настроек. В окне настроек в разделе Параметры
подписи снимите галочку напротив пункта Значения Y и поставьте
галочку напротив пункта Размеры пузырьков. В окне настроек нажмите кнопку Закрыть;
12) Увеличьте размер области диаграммы;
13) Переместите от центра пузырьков подписи так, чтобы они были рядом с ними (сделайте это для тех пузырьков, размер которых меньше размера подписи);
14) Уменьшите область построения диаграммы так, чтобы осталось место для подписей к осям;
15) Вставьте подписи к осям. Для этого зайдите на вкладку Вставка, нажмите кнопку Вставить и щелкните левой кнопкой мыши по диаграмме в том месте, где хотите расположить подпись. После этого
укажите саму подпись.
С помощью пузырьковой диаграммы вы также можете указать, например,
объемы продаж некоторой продукции в регионах, в зависимости от широты и
долготы. В этом случае в качестве фона можно выбрать изображение с картой
283
283
283

Часть II. Анализ данных с помощью диаграмм и графиков

местности и построить пузырьковую диаграмму. В итоге у вас получится
карта, на которой пузырьками будут отмечены объемы продаж в том или
ином регионе; главное — правильно подобрать фоновое изображение .

6.15. ÏÐÈÌÅÐ ¹15. ÀÍÀËÈÇ ÄÀÍÍÛÕ ÏÎ ÍÅÑÊÎËÜÊÈÌ
ÏÀÐÀÌÅÒÐÀÌ
В данном примере мы рассмотрим довольно странный тип диаграмм –
лепестковые. Такие диаграммы подойдут для оценки одной отрасли или
предприятия по нескольким параметрам. Пусть у вас есть предприятие,
оцениваемое по нескольким параметрам (параметры оцениваются по
десятибалльной шкале) – рисунок 6.36.

Рис. 6.36. Таблица с данными из условия примера №15

Если построить лепестковую диаграмму, получиться диаграмма, показанная
на рис. 6.37.

Рис. 6.37. Лепестковая диаграмма в Excel 2010

284
284

Глава 6. Анализ данных с помощью диаграмм и графиков

6.15.1. РЕШЕНИЕ
Рассмотрим по шагам, как построить лепестковую диаграмму для данного
примера.
1) Наберите таблицу, показанную на рис. 6.36;
2) Выделите область A3:E4;
3) Зайдите на вкладку Вставка и выберите Другие диаграммы (на панели Диаграммы)  Лепестковая;
4) Щелкните правой кнопкой мыши по диаграмме и в появившемся
меню выберите пункт Выбрать данные;
5) Нажмите кнопку Изменить (Элементы легенды (рядя)). Нажмите кнопку

напротив поля Имя ряда, щелкните левой кнопкой

мыши по ячейке A1, нажмите кнопку

и нажмите кнопку ОК;

6) Увеличьте область диаграммы так, чтобы на диаграмме можно было
увидеть значения. При необходимости добавьте подписи данных со
значениями оценок (если по диаграмме нельзя точно определить
значения оценок).
С помощью данного типа диаграммы довольно сложно провести анализ
предприятия. Данная диаграмма рассмотрена в качестве очередного
примера, показывающего возможности Excel 2010 в области построения
диаграмм. Для анализа данных из данного примера лучше использовать
круговую диаграмму.

Рис. 6.38. Таблица с данными из условия примера №16

285
285
285

Часть II. Анализ данных с помощью диаграмм и графиков

6.16. ÏÐÈÌÅÐ ¹16. ÀÍÀËÈÇ ÐÀÑÏÐÅÄÅËÅÍÈß ÒÅÌÏÅÐÀÒÓÐÛ
И, напоследок, разберем пример построения поверхности в Excel 2010.
Возьмем пример из физики. Пусть у вас есть таблица распределения
температур внутри плоской прямоугольной пластины – рисунок 6.38.
Вам необходимо построить диаграмму, показывающую распределение
температур внутри пластины – рисунок 6.39.

Рис. 6.39. Диаграмма распределения температур внутри
пластины

6.16.1. РЕШЕНИЕ
Рассмотрим, как построить поверхность в Excel 2010.
1) Наберите таблицу, показанную на рис. 6.38;
2) Выделите область A1:K9;
3) Зайдите на вкладку Вставка и выберите Другие диаграммы (на панели Диаграммы)  Поверхность;
4) Зайдите на вкладку Макет и выберите Название диаграммы  Над
диаграммой. Введите название – “Распределение температуры”.
Стоит отметить, что данный тип диаграмм очень хорошо подходит для
построения графиков функций двух переменных.

6.17. ÂÛÂÎÄÛ
Подведем краткий итог рассмотренного материала. Excel 2010 обладает
потрясающими по функциональности и простоте функциями построения
286
286

Выводы

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


Гистограммы и линейчатые диаграммы подойдут для анализа в тех
случаях, когда вы сравниваете деятельность одного предприятия по
нескольким параметрам за 2, 3 или 4 периода;



В случае, когда вы оцениваете отрасль или предприятие по нескольким параметрам, вы можете построить круговую диаграмму;



Если вы хотите показать деятельность предприятия как часть всей
отрасли, вам подойдет вторичная круговая диаграмма или вторичная гистограмма;



Если вы сравниваете предприятие по нескольким параметрам со
всей отраслью, вы можете построить двойную диаграмму, т.е. фактически построить две диаграммы, которые будут выглядеть, как одна;



Для анализа данных по трем параметрам вам подойдет пузырьковая
диаграмма;



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

И еще несколько советов непосредственно по построению диаграмм:

287
287
287

Часть II. Анализ данных с помощью диаграмм и графиков



Для каждого случая, когда вам необходимо проанализировать данные, стройте несколько типов графиков/диаграмм. Это позволит
вам взглянуть на оцениваемый объект с нескольких сторон;



Для каждого графика/диаграммы экспериментируйте с настройками, пока не добьетесь желаемого результата. В некоторых случаях ручная настройка дает гораздо лучший результат, чем в случае, когда диаграмма/график построена с использованием настроек
по умолчанию (или настроек, выставляемых автоматически) Excel
2010.

6.18. ÏÎÑÒÐÎÅÍÈÅ ÃÐÀÔÈÊÀ ÔÓÍÊÖÈÈ ÄÂÓÕ ÏÅÐÅÌÅÍÍÛÕ
Чуть ранее мы научились строить график функции одной переменной. В
построении графика функции двух переменных также нет ничего сложного.
Для этого нужно создать таблицу сродни таблицы умножения. Сверху
горизонтально и слева вертикально должны располагаться, соответственно,
подставляемые значения одного и другого парамтеров, а на пересечении —
значение функции (см. рис. 6.40). Затем необходимо выделить весь массив
вычисленных значений функции и построить диаграмму типа Поверхность
(см. п. 6.16).

Рис. 6.40. Построение графика функции двух переменных

288
288

ЧАСТЬ III.
СВОДНЫЕ ТАБЛИЦЫ.
АНАЛИЗ И ОРГАНИЗАЦИЯ ДАННЫХ
С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

289
289
289

ГЛАВА 7.
АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ
СВОДНЫХ ТАБЛИЦ

290
290

Очень часто вам может понадобиться проанализировать большой объем
данных, представленных в виде таблиц. В “чистом” виде анализировать
большие объемы данных довольно сложно, а иногда и практически невозможно. В таких случаях возникает необходимость представить имеющиеся
данные в более простом (упорядоченном или сгруппированном по определенному признаку) виде. В Excel данные можно представить в виде сводных таблиц.
Если говорить о сущности сводных таблиц, то они являются отчетом, построенным на основе имеющихся табличных данных. Для того чтобы вы
лучше поняли, что собой представляют сводные таблицы, приведем простой пример. Допустим, у вас есть данные о ряде предприятий, работающих
в разных регионах, за несколько лет. Данные о предприятиях включают в
себя данные о количестве произведенной и проданной продукции. Построив сводную таблицу, вы сможете посмотреть сумму произведенной и/или
проданной продукции по всем регионам за каждый год, сумму произведенной и/или проданной продукции за все время работы предприятий во всех
регионах (или по каждому региону в отдельности), сумму произведенной
и/или проданной продукции во всех регионах всеми предприятиями за год
(или за все время работы) и т.д.

7.1. ÏÎÑÒÐÎÅÍÈÅ ÑÂÎÄÍÛÕ ÒÀÁËÈÖ Ñ ÏÎÌÎÙÜÞ ÌÀÑÒÅÐÀ
Теперь поговорим о том, как построить сводную таблицу. Для этого есть два
варианта: создание сводной таблицы с вкладки Вставка и с помощью мастера. Сначала разберем второй вариант. Для того чтобы приступить к рабо291
291
291

Часть III. Сводные таблицы

те с мастером создания сводных таблиц, вам необходимо сначала добавить
кнопку вызова мастера на панель быстрого запуска. Для этого вам необходимо проделать ряд шагов.
1) Щелкните левой кнопкой мыши по кнопке Файл;
2) В появившемся окне нажмите кнопку Параметры;
3) Зайдите в раздел Настройка. Для параметра Выбрать команды из
укажите вариант Все команды. В списке (ниже строки параметра
Выбрать команды из) отыщите команду Мастер сводных таблиц и
диаграмм. Щелкните по ней левой кнопкой мыши и нажмите кнопку Добавить>>;
4) В окне Параметры Excel нажмите кнопку ОК.
Теперь на панели быстрого запуска появится кнопка, нажав на которую вы
сможете вызвать мастер построения сводных таблиц и диаграмм —

.

Теперь рассмотрим работу мастера создания сводных таблиц и диаграмм.
Щелкните по кнопке
, чтобы открыть мастер создания сводных таблиц
и диаграмм. Работа мастера происходит в три шага, рассмотрим каждый из
них.

7.1.1. ШАГ №1. ВЫБОР ИСТОЧНИКА ДАННЫХ
На первом шаге работы мастера вам предстоит указать источник данных
для построения сводной таблицы или диаграммы (параметра Создать таблицу на основе данных, находящихся) и, непосредственно, что будем строить – сводную таблицу или диаграмму. Для первого параметра есть три варианта.


в списке или базе данных Microsoft Office Excel. В данном случае
сводная таблица будет строиться на основе таблицы, находящейся
на отрытом рабочем листе Excel;



во внешнем источнике данных. В качестве источника данных будет
использоваться внешний источник – например, таблицы из базы
данных Microsoft SQL Server;



в нескольких диапазонах консолидации. В этом случае источником для построения сводной таблицы будет являться ряд таблиц
или списков, расположенных в нескольких рабочих листах Excel;



в другой сводной таблице или диаграмме. Этот вариант будет доступен только в том случае, когда у вас уже построена одна (или несколько) сводная таблица.

292
292

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

В качестве значения параметра Вид создаваемого отчета вы можете выбрать из двух вариантов – сводная таблица (будет построена только сводная таблица) или сводная диаграмма (со сводной таблицей) (сводные диаграммы строятся только на основе сводных таблиц).
По окончании выбора необходимых параметров нажмите кнопку Далее>.

7.1.2. ШАГ №2. ВЫБОР ДИАПАЗОНА ДАННЫХ
На втором шаге вам предстоит указать диапазон исходных данных. В зависимости от того, какой вариант вы указали на предыдущем шаге, вид окна
шага №2 будет изменяться.


Если вы выбрали вариант в списке или базе данных Microsoft Office Excel:
Если вы хотите указать диапазон данных, расположенных на открытом рабочем листе, нажмите кнопку
, выделите необходимый
. Если вы хотите указать внешний
диапазон и нажмите кнопку
источник данных (в данном случае может выступать и файл), нажмите кнопку Обзор и выберите необходимый источник.



Если на первом шаге вы указали вариант во внешнем источнике
данных:
Нажмите кнопку Получить данные. Выберите необходимый источник данных и нажмите кнопку ОК. После этого укажите путь к источнику данных и нажмите ОК. Откроется окно программы Microsoft Query, где вы сможете сформировать запрос на выборку данных
из таблицы (о том, как работать с программой Microsoft Query, мы
поговорим позже). После этого в программе Microsoft Query выполните Файл  Вернуть данные в Microsoft Office Excel, чтобы
вернуться в окно мастера создания сводных таблиц.



Если вы указали вариант в нескольких диапазонах консолидации:
Выберите один из двух вариантов – Создать одно поле страницы и
Создать поля страницы (вы сможете создать несколько полей, но не
более четырех).
ПРИМЕЧАНИЕ.
Полем будем называть одну из ячеек сводной таблицы, в которой будут храниться
несколько значений. При работе со сводной таблицей вы сможете выбрать одно
из значений ключевого поля и, в зависимости от выбранного варианта, данные в
сводной таблице будут обновлены.

293
293
293

Часть III. Сводные таблицы



Если вы указали вариант в другой сводной таблице или диаграмме:
В этом случае вам предстоит указать одну из сводных таблиц (все
доступные сводные таблицы будут перечислены в списке).
По окончании выбора необходимых параметров нажмите кнопку
Далее>, чтобы перейти к шагу №3.

7.1.3. ШАГ №3. ВСТАВКА СВОДНОЙ ТАБЛИЦЫ
На третьем шаге вам следует указать место, куда будет вставлена сводная
таблица. Вы можете выбрать из двух вариантов – новый лист (сводная
таблица будет создана на новом рабочем листе) и существующий лист (в
этом случае вам следует указать ячейку, начиная с которой будет вставлена
сводная таблица – для этого достаточно щелкнуть по нужной ячейке левой
кнопкой мыши).
После выполнения всех шагов мастера вам предстоит произвести настройку сводной таблицы, но об этом мы поговорим позже. Сейчас рассмотрим
второй вариант построения сводных таблиц.

7.2. ÏÎÑÒÐÎÅÍÈÅ ÑÂÎÄÍÎÉ ÒÀÁËÈÖÛ Ñ ÏÎÌÎÙÜÞ
ÊÎÌÀÍÄÛ ÂÊËÀÄÊÈ “ÂÑÒÀÂÊÀ”
Зайдите на вкладку Вставка и выберите Сводная таблица (на панели Таблицы)  Сводная таблица. Вы увидите окно, в котором сможете указать
параметры для построения сводных таблиц. Вам следует указать, какие данные будут взяты для построения сводной таблицы – Выбрать таблицу или
диапазон (в этом случае вам предстоит указать диапазон рабочего листа
или таблицу) или Использовать внешний источник данных (в этом случае нажмите кнопку Выбрать подключение и выберите необходимый вариант).
Также вам предстоит указать место, куда будет помещена сводная таблица
– На новый лист или На существующий лист. После указания всех необходимых параметров нажмите кнопку ОК. Далее вам предстоит настроить
сводную таблицу.

7.3. ÏÐÈÌÅÐ ÏÎÑÒÐÎÅÍÈß ÑÂÎÄÍÎÉ ÒÀÁËÈÖÛ
Теперь взгляните на рисунок 7.1. На нем показана таблица с данными о
294
294

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

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

производстве двух видов продукции двумя заводами за пять месяцев.
На основе этой таблицы мы рассмотрим примеры построения сводных
таблиц. Рассмотрим два варианта построения сводной таблицы – с помощью
мастера построения сводных таблиц и диаграмм и с вкладки Вставка.
1. Построение с помощью мастера
1.1.Наберите таблицу, показанную на рис. 7.1;
1.2.Щелкните левой кнопкой мыши по кнопке
мастер создания сводных таблиц и диаграмм;

, чтобы открыть

1.3.На первом шаге создания сводной таблицы выберите вариант в
списке или базе данных Microsoft Office Excel, для источника
данных и вариант сводная таблица для типа создаваемого отчета. Нажмите кнопку Далее>, чтобы перейти ко второму создания сводной таблицы;
1.4.В окне выбора источника данных (Шаг №2) нажмите кнопку

, выделите необходимую таблицу и нажмите кнопку
295
295
295

Часть III. Сводные таблицы

(для нашего примера необходимый диапазон данных – A1:F21).
Если нужная исходная таблица находится в другом документе,
нажмите кнопку Обзор и выберите необходимый документ. По
окончании выбора нажмите кнопку Далее>;
1.5.На третьем шаге укажите, где поместить сводную таблицу – на
новом листе или в существующем листе (в этом случае укажите
ячейку, в которую будет помещена сводная таблица, так чтобы
она не мешала просмотру имеющихся у вас данных). По окончании выбора нажмите кнопку Готово, чтобы завершить процесс
создания сводной таблицы, закрыть окно мастера создания сводных таблиц и диаграмм и приступить к настройке сводной таблицы;
2. Построение сводной таблицы с вкладки Вставка
2.1.Наберите таблицу, показанную на рис. 7.1;
2.2.Войдите на вкладку Вставка. Выберите Сводная таблица (на панели Таблицы)  Сводная таблица, откроется окно Создание
сводной таблицы;
2.3.Укажите вариант Выбрать таблицу или диапазон, укажите диапазон A1:F21 и укажите место расположения диаграммы;
2.4.В окне Создание сводной таблицы нажмите кнопку Готово, чтобы завершить процесс создания сводной таблицы, закрыть окно
параметров создания сводной таблицы и приступить к настройке
таблицы.

7.4. ÍÀÑÒÐÎÉÊÀ ÑÂÎÄÍÎÉ ÒÀÁËÈÖÛ
После окончания создания сводной таблицы с помощью мастера или с
вкладки Вставка вам предстоит настроить сводную таблицу. Посмотрите
на рисунок 7.2, на нем вы можете видеть, что после создания сводной таблицы в окне Excel 2010 появилась новая панель – Список полей сводной таблицы и область, куда будет помещена сводная таблица. Итак,рассмотрим
панель Список полей сводной таблицы.


296
296

Выберите поля для добавления в отчет. Здесь расположен список
полей исходной таблицы. Для того чтобы добавить/убрать поле в
сводную таблицу, поставьте/снимите галочку напротив названия
необходимого поля. Также вы можете перетащить поля данного
списка в один из четырех описанных ниже списков;

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

Рис. 7.2. Панель форматирования сводной таблицы в Excel 2010



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



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



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



Значения. Сюда стоит помещать поля, значения которых будут
отображаться на пересечении строк и столбцов сводной таблицы. В
поле Значения вы можете указать, как отображать данные, т.е. ука297
297
297

Часть III. Сводные таблицы

зать формулу для их отображения – сумма, среднее, количество и
т.д.
Итак, закончим построение сводной таблицы из нашего примера и заодно
разберемся с настройками. Еще раз взгляните на рисунок 7.2. В списке доступных для добавления в сводную таблицу полей присутствуют все четыре поля исходной таблицы.
Выполним ряд шагов для завершения построения нашей сводной таблицы.
1) Перетащите поле месяц в список Названия столбцов, таким образом, данные о производстве будут отображаться по месяцам (также
будет выведен результат за весь период);
2) Перетащите поле товар в список Названия строк, на пересечении
названий товаров и месяцев будут выведены данные по каждому месяцу;
3) Перетащите поле Изготовлено в список Значения, ведь нас интересует именно количество произведенного товара;
4) Перетащите поле Завод производитель в список Фильтр отчета.
В зависимости от выбранного завода(ов) будут показаны данные о
производстве продукции.
Теперь у вас должна получиться таблица, похожая на ту, что показана на
рис. 7.3.
Теперь поговорим об изменении параметров сводной таблицы. Как вы можете видеть, во всех четырех списках с полями, по которым формируется
сводная таблица, значения похожи на кнопки и выделены желтым цветом.
Действительно, если щелкнуть по одному из значений, появится раскрывающийся список, в котором вы сможете поменять порядок записей (если
в списке несколько записей), переместить запись в другой список, удалить
запись из списка или открыть окно параметров поля. Разберемся с параметрами поля поподробнее.

Рис. 7.3. Сводная таблица в Excel 2010

298
298

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

7.4.1. НАСТРОЙКА ПАРАМЕТРОВ ПОЛЯ
Для вызова окна параметров поля сводной таблицы щелкните в одном из четырех списков по записи, которую хотите форматировать, и в появившемся
меню выберите параметры поля. Настройки в данном окне располагаются
в двух вкладках.
Для полей, находящихся в списках Названия столбцов, Названия строк
и Фильтр отчета, настройки параметров поля одинаковы. Рассмотрим эти
настройки. Во вкладке Промежуточные итоги и фильтры вы можете настроить параметры фильтрации данных – автоматически (параметры фильтрации будут выбраны автоматически), нет (фильтрация данных производиться не будет) и другие (в этом случае вы можете указать конкретный
параметр фильтрации – сумма, количество и т.д.). На вкладке Разметка и
печать вы сможете указать параметры для формирования макета таблицы,
включить отображение пустой строки после подписей, включить отображение пустых элементов и, при необходимости, включить добавление разрыва
страницы после каждого элемента. Также в окне Параметры поля вы сможете изменить имя параметра, указав необходимое значение в поле Пользовательское имя.
Теперь рассмотрим настройки поля для значений. В первой вкладке Операции вы сможете указать, как будут вычисляться поля значений таблицы
– вам достаточно выбрать нужный вариант из списка. Во второй вкладке
Дополнительные вычисления вы сможете указать тип дополнительных вычислений и в зависимости от выбранного варианта указать поле и конкретный элемент поля для вычислений. Нажав кнопку Числовой формат, вы
откроете окно, в котором сможете выбрать числовой формат отображения
данных в сводной таблице.

7.5. ÂÊËÀÄÊÈ ÄËß ÐÀÁÎÒÛ ÑÎ ÑÂÎÄÍÛÌÈ ÒÀÁËÈÖÀÌÈ
Как и при работе с графиками и диаграммами, в Excel 2010 при работе со
сводными таблицами появляется группа вкладок – Работа со сводными таблицами. В эту группу входят две вкладки – Параметры и Конструктор.
Для того чтобы сделать эти вкладки активными, вам необходимо сделать
активной сводную таблицу. Для этого достаточно щелкнуть левой кнопкой
мыши по одному из элементов сводной таблицы.

7.5.1. ВКЛАДКА «ПАРАМЕТРЫ»
На этой вкладке расположены элементы, позволяющие настроить параметры сводной таблицы – порядок сортировки и группировки данных, пара299
299
299

Часть III. Сводные таблицы

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

ПАНЕЛЬ «СВОДНАЯ ТАБЛИЦА»
На этой панели расположено поле Имя, в котором вы можете указать имя
сводной таблицы, и кнопка Параметры, нажав которую вы откроете окно
настроек сводной таблицы (Настройки окна Параметры сводной таблицы
мы рассмотрим ниже).

ПАНЕЛЬ «АКТИВНОЕ ПОЛЕ»
Здесь вы можете видеть поле Активное поле, в котором вы можете указать
имя для выбранного поля (при выборе имени учтите, что нельзя указывать в качестве имени поля имя другого поля). Нажав кнопку Параметры
поля, вы откроете окно настроек поля (данные настройки мы рассмотрели
выше). Также на данной панели расположены две кнопки
(Развернуть
все поля) и
(Свернуть все поля). Нажав кнопку
, вы откроете окно,
в котором сможете выбрать поле, по которому хотите развернуть данные.
Возьмем наш пример. Щелкните левой кнопкой мыши по ячейке Товар1 и
нажмите кнопку
. В открывшемся окне выберите Завод-производитель и
нажмите кнопку ОК. Вы можете видеть, что в сводной таблице все данные
о товарах распределены по заводам-изготовителям – рис. 7.4. Аналогично
вы можете развернуть данные и по другим полям. Чтобы отменить развертывание, нажмите Ctrl + Z. Для того чтобы свернуть развернутые данные,
нажмите кнопку
.

Рис. 7.4. Сводная таблица с развернутыми по полю
“Завод-производитель” данными

300
300

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

ПАНЕЛЬ «ГРУППИРОВАТЬ»
На панели Группировать расположены кнопки, предназначенные для
группировки данных в сводной таблице. Рассмотрим пример для нашего
случая. Выделите ячейки столбца с наименованием товара (для нашего
случая это ячейки A5:A6) и нажмите кнопку Группировать по выделенному.
Как вы можете видеть, Товар1 и Товар2 теперь сгруппированы (Группа1 ).
Вы можете свернуть сгруппированные данные, нажав кнопку “-” напротив
названия группы.
Если вы хотите развернуть данные из свернутой группы, нажмите кнопку “+”
напротив названия группы. Таким образом, вы можете сгруппировать данные
об однотипных товарах. Если вы хотите удалить группу, щелкните левой
кнопкой мыши по ячейке с ее названием и нажмите кнопку Разгруппировать.
Если в заголовках строк или столбцов у вас стоят числовые значения, вы
можете сгруппировать данные по ним. Щелкните левой кнопкой мыши
по ячейке с числовым значением (напомню, что эта ячейка должна быть
названием строки или столбца; таким же образом вы сможете группировать
даты) и нажмите кнопку Группировать по полю. В появившемся окне
укажите начальное (начиная с) и конечное (по) значения для группировки,
также укажите шаг для группировки (с шагом). Нажмите кнопку ОК, чтобы
закрыть окно параметров группировки.

ПАНЕЛЬ «СОРТИРОВКА»
На панели Сортировка расположены кнопки для сортировки данных. Перед
тем, как сортировать данные, выделите участок в таблице, данные из которого хотите сортировать. Нажмите кнопку
, чтобы сортировать данные
в алфавитном порядке (для чисел и дат в порядке возрастания), нажмите
, чтобы сортировать данные в обратном порядке (в порядке убывания).
Если вы хотите указать параметры для сортировки, нажмите кнопку Сортировка, вы увидите окно Сортировка. Здесь вы можете выбрать из трех
вариантов: вручную (при данном варианте вы можете перетаскивать элементы в таблице), по возрастанию и по убыванию (для этих двух вариантов вам будет необходимо указать поле, по которому будет производиться
сортировка). Если вы хотите указать дополнительные параметры сортировки, нажмите кнопку Дополнительно, чтобы открыть окно дополнительных
параметров. В этом окне вы сможете указать ключ для сортировки (для этого снимите галочку напротив пункта Автоматическая сортировка при каждом обновлении отчета) и порядок сортировки. Указав все необходимые
параметры сортировки, нажмите кнопку ОК.
301
301
301

Часть III. Сводные таблицы

ПАНЕЛЬ “ДАННЫЕ”
На панели Данные расположены кнопки для управления данными в
сводной таблице. Нажав кнопку Обновить, вы сможете обновить данные в сводной таблице. Нажав на стрелку внизу кнопки Обновить, вы
откроете раскрывающийся список, в котором вы сможете указать, обновлять всю таблицу или выделенную область, также вы сможете показать состояние процесса обновления (состояние обновления) и отменить обновление (отменить обновление). Если для построения сводной
таблицы используются данные из внешнего источника, вы сможете открыть окно параметров подключения (свойства подключения).
Нажав кнопку Изменить источник данных, вы откроете окно, в котором
сможете изменить источник данных (окно изменения источника данных
аналогично окну выбора данных) для построения сводной таблицы.

ПАНЕЛЬ «ДЕЙСТВИЯ»
На панели Действия расположены три кнопки, с помощью которых вы сможете выполнить ряд действий над сводными таблицами.Нажмите кнопку
Очистить, чтобы очистить данные в сводной таблице, вы увидите список,
в котором вам предстоит выбрать из двух вариантов – очистить все (будет
очищена вся сводная таблица) и очистить фильтры (будут сброшены настройки фильтров сводной таблицы).
Нажмите кнопку Выбрать, чтобы открыть список возможных вариантов
выбора данных в сводной таблице – Всю сводную таблицу (будет выбрана вся сводная таблица), Заголовки и значения (будут выбраны заголовки
строк и столбцов, а также значения на пересечении строк и столбцов), Значения и Заголовки (вы сможете выбрать только заголовки или значения).
Нажав кнопку Переместить, вы сможете переместить сводную таблицу в
нужное место (на новый лист или на существующий лист, в этом случае
вам следует указать конкретную ячейку на нужном листе).

ПАНЕЛЬ «СЕРВИС»
На панели Сервис расположены три кнопки – Сводная диаграмма, Формулы и Средства OLAP. Нажмите кнопку Сводная диаграмма, чтобы построить сводную диаграмму (о построении сводных диаграмм мы поговорим чуть ниже). Нажав кнопку Формулы, вы сможете добавить вычисляемые поля и объекты к сводной таблице. Также вы сможете задать порядок вычислений и вывести формулы, используемые для вычисления данных в сводной таблице. Рассмотрим, как добавить поля к сводной таблице. Выберите Формулы  Вычисляемое поле, откроется окно Встав302
302

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

ка вычисляемого поля. Укажите имя добавляемого поля. В поле Формула вам необходимо указать формулу для вычисления значения поля – при
вводе формулы вы можете добавить поля сводной таблицы (выберите
нужное поле в списке Поля и нажмите кнопку Добавить поле) в формулу для вычислений. По окончании ввода формулы нажмите кнопку ОК.
Средства OLAP. Эти средства используются для построения “быстрых” запросов к базам данных. Материал, посвященный средствам OLAP, слишком
велик и выходит за рамки данной книги, поэтому мы не будем его рассматривать.

ПАНЕЛЬ «ПОКАЗАТЬ ИЛИ СКРЫТЬ»
На панели Показать или скрыть расположены три кнопки, с помощью которых вы сможете показать/скрыть Список полей сводной таблицы, Кнопки (вы сможете показать/скрыть кнопки “+” или “-” для групп и списков) и
Заголовки полей.

7.5.2. ВКЛАДКА «КОНСТРУКТОР»
На данной вкладке расположены инструменты, позволяющие вам настроить внешний вид и структуру сводной таблицы. Все настройки, доступные
с данной панели, разделены на группы, каждая группа размещена на соответствующей панели.

ПАНЕЛЬ «МАКЕТ»
На данной панели расположены инструменты (вызываются нажатием соответствующей кнопки), позволяющие настроить внешний вид и содержание
сводной таблицы. Если вы хотите показать промежуточные итоги по данным сводной таблицы, выберите Промежуточные итоги 
a) Не показывать промежуточные суммы. Промежуточные итоги
отображаться не будут;
b) Показывать все промежуточные итоги в нижней части группы.
После данных каждой группы будет показана строка (или столбец,
если вы сгруппировали данные по столбцам), в которой будет приведена сумма (итог) по всем данным группы. Таким образом вы сможете вычислить промежуточные суммы по вашим данным (например, суммарный доход по каждой группе товаров);
303
303
303

Часть III. Сводные таблицы

c) Показывать все промежуточные итоги в заголовке группы. Как и в
предыдущем варианте, для каждой группы будет вычислена сумма
по всем полям; разница состоит в том, что значения сумм по каждой
группе будут расположены не в отдельной строке, а в заголовке каждой группы.
Вы можете настроить Общие итоги, это итоги по данным всех строк/столбцов таблицы. Здесь возможны четыре варианта. Для настройки общих итогов выберите Общие итоги 
a) Отключить для строк и столбцов. Общие итоги отображаться не будут;
b) Включить для строк и столбцов. Общие итоги будут показаны для
строк и столбцов;
c) Включить только для строк. Общие итоги будут показаны только
для строк;
d) Включить только для столбцов. Общие итоги будут показаны только для столбцов.
Стоит отметить, что значения итогов для строк и столбцов зависят
от формулы (или выбранного стандартного варианта) вычисления
данных в сводной таблице.
Если вы хотите настроить Макет отчета (имеется в виду макет сводной таблицы), выберите Макет отчета 
a) Показывать в сжатой форме. Данный параметр выбран по умолчанию. Представляется, что он наиболее удобен для построения сводной таблицы;
b) Показывать в форме структуры. Отличие от предыдущего варианта состоит в том, что данные по группам (наименования в каждой из
групп) будут расположены в соседних, а не в тех же столбцах/строках таблицы;
c) Показывать в табличной форме. То же самое, что и предыдущий
вариант, только добавлены итоговые строки/столбцы по каждой из
групп.
Также вы можете добавить/удалить пустые строки после каждой группы,
для этого выберите Пустые строки  Вставить (или Удалить) пустую
строку после каждого элемента;
304
304

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

ПАНЕЛЬ «ПАРАМЕТРЫ СТИЛЕЙ СВОДНОЙ ТАБЛИЦЫ»
На данной панели вы можете включить/выключить ряд параметров отображения сводной таблицы. Для включения/выключения параметра поставьте/уберите галочку напротив его названия.


Заголовки строк – заголовки строк будут помечены темным цветом;



Заголовки столбцов – заголовки столбцов будут помечены темным
цветом;



Чередующиеся строки – четные и нечетные строки будут отличаться цветом;



Чередующиеся столбцы – четные и нечетные столбцы будут отличаться цветом.

ПАНЕЛЬ «СТИЛИ СВОДНОЙ ТАБЛИЦЫ»
На данной панели вы сможете выбрать один из макетов для построения
сводной таблицы. Стиль сводной таблицы определяет цвет области данных, цвет заголовков строк и столбцов, цвет итоговых строк и столбцов. Вы
можете выбрать один из стандартных стилей сводной таблицы или создать
напротив списка
свой собственный стиль. Для этого нажмите кнопку
стилей и, в самом низу появившегося меню, выберите Создать стиль сводной таблицы. Вы увидите окно, показанное на рис. 7.5.

Рис. 7.5. Окно создания стиля сводной таблицы

305
305
305

Часть III. Сводные таблицы

Для начала укажите имя создаваемого стиля – поле Имя. Затем выберите
из списка Элемент таблицы элемент, который хотите настроить (вы можете
выбрать как конкретный элемент, так и все элементы таблицы) и нажмите
кнопку Формат, чтобы открыть окно настройки выбранного элемента.
В данном окне вы можете выбрать и настроить шрифт для данных, которые
будут отображены в настраиваемом элементе, настроить границу (выбрать
линии границы, тип линий границы, толщину и цвет линий границы), указать заливку элемента. По окончании настройки выбранного элемента нажмите кнопку ОК.
В окне Создание экспресс-стиля сводной таблицы вы также можете указать, что создаваемый вами стиль будет стилем сводных таблиц текущего
документа по умолчанию. Для этого поставьте галочку напротив пункта Назначить используемым по умолчанию экспресс-стилем сводной таблицы
для данного документа. По окончании настройки всех элементов таблицы
нажмите кнопку ОК. Теперь созданный вами стиль будет располагаться в
списке стилей Пользовательские. Таким образом вы сможете создать стиль
сводной таблицы по своему вкусу. Это позволит вам представить ваши данные в более удобном для вас виде.

Рис. 7.6. Окно настройки параметров сводной таблицы

306
306

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

7.5.3. ПАРАМЕТРЫ СВОДНОЙ ТАБЛИЦЫ
Сейчас мы поговорим о настройках сводной таблицы. Для того чтобы вызвать окно настроек сводной таблицы, зайдите на вкладку Параметры и на
панели Сводная таблица нажмите кнопку Параметры, вы увидите окно, показанное на рис. 7.6.
Как вы можете видеть, все настройки в данном окне разделены на группы
(каждая группа находится в своей вкладке). Лишь одна настройка является
общей – имя сводной таблицы, его вы можете указать в поле Имя. Рассмотрим настройки по вкладкам. Описания части настроек вы не найдете здесь
– мы опишем лишь те, что пригодятся вам при построении сводных таблиц
для анализа ваших данных.

ВКЛАДКА «РАЗМЕТКА И ФОРМАТ»
На данной вкладке расположены настройки внешнего вида сводной таблицы
и формата отображения данных. Как вы могли заметить, слишком длинные
подписи к заголовкам строк и столбцов занимают две и более ячейки. Если
вы хотите, чтобы ячейки с подписями автоматически объединялись и текст
подписи выравнивался по центру, поставьте галочку напротив Объединить
и выровнять по центру ячейки с подписями. Если вы группировали названия строк, вы могли заметить, что названия строк в группе смещены вправо
от названия группы. Вы можете указать величину отступа подписей строк в
группе, задав необходимое значение параметра Отступ для названия строк
в сжатой форме.
Иногда при вычислении данных, отображаемых в сводной таблице, возникают ошибки (например, при вычислении производится деление на ноль).
Вы можете указать, какое значение отображать в ячейках, значение которых вычислить не удалось. Для этого поставьте галочку напротив пункта
Для ошибок отображать и в поле напротив укажите необходимое значение.
Аналогичным образом вы можете указать значение, которое будет отображаться в пустых ячейках. Поставьте галочку напротив пункта Для пустых
ячеек отображать и в поле напротив укажите необходимое значение.
При обновлении данных в таблице значения некоторых ячеек могут стать
больше/меньше, поэтому изменится длина значения (в символах). Если вы
хотите, чтобы ширина столбцов при обновлении данных изменялась в соответствии с длиной данных в столбце, поставьте галочку напротив пункта
Автоматически изменять ширину столбцов при обновлении. Если вы хотите сохранить заданное вами форматирование ячеек при обновлении данных
в таблице, установите галочку напротив пункта Сохранять форматирование ячеек при обновлении.
307
307
307

Часть III. Сводные таблицы

ВКЛАДКА «ИТОГИ И ФИЛЬТРЫ»
В данном разделе настроек вы сможете настроить параметры фильтрации и
отображения итогов в сводной таблице. В разделе Общие итоги вы можете
включить/выключить отображение общих итогов для строк/столбцов, для
этого поставьте/уберите галочку напротив пункта Показывать общие итоги для строк/Показывать общие итоги для столбцов. В разделе Фильтры
вы можете включить/отключить отфильтрованные элементы при вычислении итоговых значений, для этого поставьте/снимите галочку напротив
пункта Разрешить несколько фильтров для поля (включение данного параметра позволит отобразить скрытые при фильтрации данные). Если вы
не хотите использовать пользовательские списки при сортировке данных,
снимите флажок напротив пункта Использовать списки при сортировке
(как утверждают на официальном сайте Microsoft, при анализе больших
объемов данных с помощью сводных таблиц это позволит существенно повысить производительность сортировки данных).

ВКЛАДКА «ВЫВОД»
В данном разделе вы можете настроить параметры отображения сводной
таблицы (эти параметры будут учитываться не только при отображении, но
и при печати таблицы). Стоит сразу отметить, что часть настроек в данном
разделе будет недоступна (они доступны только при использовании OLAP),
поэтому мы не будем их рассматривать. Для списков групп вы можете
включить/отключить кнопки “+” и “-”, для этого поставьте/снимите галочку
напротив пункта Показывать кнопки развертывания и свертывания.
Как и во многих других приложениях, в Excel есть всплывающие подсказки
(они появляются при наведении курсора мыши на объект окна). Для
того чтобы отключить эти подсказки, снимите галочку напротив пункта
Показывать контекстные всплывающие подсказки . В данном случае
вы отключите всплывающие подсказки только для сводных таблиц. В
подсказках к сводным таблицам отображаются значения полей и подписи
к заголовкам строк и столбцов.
Если вы хотите скрыть названия полей (подписи в верхней части сводной
таблицы) и кнопки раскрытия списков для фильтров, снимите галочку
напротив пункта Показывать названия полей и списки фильтров.
Если вы хотите включить возможность перетаскивать поля из сводной
таблицы в отчет, поставьте галочку напротив пункта Классический макет
сводной таблицы.
Как вы могли заметить, при построении сводной таблицы поля упорядочены
308
308

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

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

ВКЛАДКА «ПЕЧАТЬ»
Здесь вы можете настроить параметры печати. В данном разделе вы можете
включить/выключить три параметра печати, поставив/убрав галочку напротив названия соответствующего параметра:


Печатать кнопки развертывания и свертывания, если они отображаются в сводной таблице. Включите данный параметр, если хотите, чтобы вместе со сводной таблицей были напечатаны кнопки
развертывания/свертывания списков. По умолчанию данная опция
отключена.



Повторять названия строк на каждой странице печати. Эта опция
определяет, будут ли отображаться названия строк (повторяющиеся) на каждой распечатанной таблице. По умолчанию данный параметр включен.



Печатать заголовки. Если вы хотите, чтобы на каждой странице печатались заголовки к строкам и столбцам, а также значения строк
и столбцов, включите данную опцию. По умолчанию данная опция
отключена.

ВКЛАДКА «ДАННЫЕ»
В данном разделе настроек вы можете включить/отключить параметры сохранения и отображения данных сводной таблицы:


Сохранять исходные данные вместе с файлом. Если вы строите
сводную таблицу на основе данных из внешнего источника, вы можете сохранить рабочую книгу вместе с исходными данными (из
внешнего источника). Для этого включите данную опцию. Это полезно, когда вы планируете перенести файл рабочей книги на другой компьютер, где недоступен источник, по которому вы строили
сводную таблицу.



Разрешить отображение деталей. Данный параметр определяет,
будет ли включена детализация данных от источника данных. По
умолчанию данный параметр включен.
309
309
309

Часть III. Сводные таблицы



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



Число элементов, сохраняемых для каждого поля. Этот параметр
определяет, какое число элементов сохранять (для каждого поля)
при изменении данных в источнике. У данного параметра три значения – Авто (число сохраняемых элементов будет определено автоматически), Нет (для полей не будет сохранено ни одного элемента)
и Максимум (для каждого поля будет сохранено максимальное количество элементов, как утверждают на сайте Microsoft, максимальное число сохраняемых элементов равно 1048576).

7.6. ÑÎÇÄÀÍÈÅ ÑÂÎÄÍÎÉ ÒÀÁËÈÖÛ ÈÇ ÍÅÑÊÎËÜÊÈÕ
ÄÈÀÏÀÇÎÍÎÂ
Иногда вам может потребоваться создать сводную таблицу на основе данных, расположенных в разных диапазонах или на разных листах. В этом
случае есть два способа:
1. Вручную объединить данные и на их основе построить сводную таблицу.
2. Построить сводную таблицу на основе нескольких диапазонов.

7.6.1. СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ НА ОСНОВЕ НЕСКОЛЬКИХ
ДИАПАЗОНОВ

Начнем со второго варианта.
Взгляните на рис. 7.7. Здесь показаны две таблицы о производстве товаров
на двух заводах. В первой таблице данные за первые пять месяцев одного
года, во второй – за последующие пять первых месяцев следующего года.
Структура обеих таблиц одинакова.
ПРИМЕЧАНИЕ.
Стоит отметить, что для построения сводной таблицы, рассматриваемой в данном
примере, исходные таблицы должны иметь одинаковую структуру.

Итак, построим сводную таблицу на основе данных из двух таблиц. Сразу
стоит сказать, что цель построения данной таблицы – получение быстрого
310
310

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

Рис. 7.7. Таблицы с исходными данными

суммарного результата о производстве каждого товара за оба года.
1) Наберите исходные таблицы, показанные на рис. 7.7;
2) Нажмите кнопку
таблиц;

, чтобы запустить мастер создания сводных

3) В первом окне мастера (Шаг №1) в качестве источника данных укажите вариант в нескольких диапазонах консолидации, для параметра Вид создаваемого отчета оставьте значение сводная таблица
и нажмите кнопку Далее >;
4) Во втором окне мастера (Шаг №2) оставьте вариант Создать одно
поле страницы, нажмите кнопку Далее >;
5) Теперь вам следует указать необходимые диапазоны исходных дан, выделите первую таблицу
ных (Шаг №2б). Нажмите кнопку
(диапазон C1:D21), нажмите кнопку
, нажмите кнопку Добавить (Если вы хотите указать таблицу(или таблицы) из другого документа, нажмите кнопку Обзор и укажите документ Excel с нужной вам таблицей). Аналогичным образом выделите и добавьте диапазон данных второй таблицы таблицу (диапазон H1:I21). Если
вы хотите удалить диапазон, выделите его в списке диапазонов и нажмите кнопку Удалить. Стоит также отметить, что в практике использования Excel 2010 вы можете создавать сводную таблицу на
основе неограниченного числа исходных таблиц (главное – не забывайте, что все сводные таблицы должны иметь одинаковую структу311
311
311

Часть III. Сводные таблицы

ру). По окончании добавления диапазонов с исходными данными
нажмите кнопку Далее >;
6) На третьем шаге выберите место, куда будет помещена сводная таблица. Укажите место на существующем листе или укажите вариант
новый лист и нажмите кнопку Готово.
На рис. 7.8 показана сводная таблица, которая должна была получиться у
вас, если вы все сделали правильно.

Рис. 7.8. Сводная таблица, построенная на основе двух исходных таблиц

Сводные таблицы, построенные на основе нескольких диапазонов, подходят
для быстрого получения суммарных результатов по нескольким исходным
таблицам. Для более полного анализа данных вам следует вручную объединить исходные таблицы. Рассмотрим и этот вариант.

7.6.2. ОБЪЕДИНЕНИЕ ИСХОДНЫХ ДАННЫХ ВРУЧНУЮ
1) Наберите исходные таблицы, показанные на рис. 7.7;
2) Щелкните левой кнопкой мыши по первому столбцу (столбец A),
чтобы вызвать контекстное меню, и выберите “Вставить…”.В появившемся окне выберите Столбец и нажмите кнопку ОК;
3) В заголовке вставленного столбца напишите Год и заполните столбец значением 2006;
4) Выделите диапазон G2:J21 (вторая таблица без заголовков столбцов), щелкните по выделенному фрагменту правой кнопкой мыши
и в появившемся меню выберите Вырезать. Теперь щелкните правой кнопкой мыши по ячейке B22 и в появившемся меню выберите
Вставить;
5) Заполните диапазон A22:A41 значение 2007 (значение года для данных из второй таблицы);
312
312

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

6) Удалите все неиспользуемые ячейки. В нашем случае это подписи
ко второй таблице (диапазон G1:J1);
7) Нажмите кнопку
таблиц;

, чтобы запустить мастер создания сводных

8) В первом окне мастера создания сводных таблиц (Шаг №1) оставьте
все настройки без изменений и нажмите кнопку Далее >;
9) Во втором окне мастера создания сводных таблиц (Шаг№2) укажите диапазон с данными исходной таблицы (диапазон A1:E41) и нажмите кнопку Далее >;
10) В третьем окне мастера создания сводных таблиц (Шаг №3) укажите место расположения сводной таблицы (на существующем или
новом листе) и нажмите кнопку Готово.
Теперь необходимо настроить сводную таблицу, рассмотрим, как это сделать для нашего случая. Мы сформируем сводную таблицу, отличную от
той, что строили в самом начале. Это позволит вам на примере убедиться,
как вы сможете изменить подход к анализу одних и тех же данных с помощью сводных таблиц, меняя структуру самих исходных таблиц.
1) К списку Названия строк добавьте поле Год;
2) К списку Названия строк добавьте поле Завод производитель;

Рис. 7.9. Сводная таблица, построенная на основе объединенных вручную данных

313
313
313

Часть III. Сводные таблицы

3) К списку Названия строк добавьте поле товар;
4) К списку Названия столбцов добавьте поле месяц;
5) К списку Значения добавьте поле Изготовлено.
Если вы все сделали правильно, у вас должна получиться сводная таблица,
показанная на рис. 7.9.
Подводя итог, стоит напомнить, что сводные таблицы, построенные на основе нескольких диапазонов данных (исходные данные располагаются в
нескольких диапазонах консолидации), подходят для анализа суммарного
значения по двум и более исходным таблицам. Если вы хотите получить
подробную информацию по каждой таблице (в таблице могут содержаться
данные за год, данные конкретного предприятия и т.д.), а также суммарные
значения по всем исходным таблицам, перед построением сводной таблицы, объедините исходные данные вручную.

7.7. ÃÐÓÏÏÈÐÎÂÊÀ ÄÀÍÍÛÕ Â ÑÂÎÄÍÎÉ ÒÀÁËÈÖÅ
Теперь мы более подробно поговорим о группировке данных. В реальной
практике использования Excel 2010 вам может понадобиться анализировать большие объемы данных. И даже после построения сводной таблицы,
данных для анализа будет слишком много. В этом случае воспользуйтесь
возможностью группировки данных в сводной таблице. Для рассмотрения
данного примера обратимся к сводной таблице из предыдущего примера –
рисунок 7.9. Для начала изменим структуру сводной таблицы:
1) Очистите все списки (фильтр отчета, названия строк и столбцов,
значения), формирующие сводную таблицу;
2) В список Названия столбцов поместите поле Завод производитель;
3) В список Названия столбцов поместите поле товар;
4) В список Названия строк поместите поле Год;
5) В список Названия строк поместите поле месяц;
6) В список Значения поместите поле Изготовлено;
7) Зайдите на вкладку Конструктор (группа вкладок Работа со сводными таблицами) и выберите Промежуточные итоги (панель Макет)  Не показывать промежуточные суммы.
У вас должна получиться сводная таблица, показанная на рис. 7.10.
314
314

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

Рис. 7.10. Исходная сводная таблица

Как вы можете видеть, данные уже отчасти сгруппированы – в строках по
годам, в столбцах – по заводам-производителям. Если бы мы рассматривали данные за весь год, то в данном случае отображались бы данные по
каждому месяцу, что занимало бы очень много места. Данные по месяцам
можно было сгруппировать по кварталам и по полугодиям. В нашем случае
у нас имеются данные за первые пять месяцев каждого года. Сгруппируем
их по временам года – зима и весна:
1) Для данных 2006-го года выделите ячейки январь и февраль;
2) Зайдите на вкладку Параметры (группа вкладок Работа со сводными таблицами) и на панели Группировать нажмите кнопку Группировать по выделенному. Как вы можете видеть, данные сгруппировались не только для 2006-го, но и для 2007-го года, также данные по
всем остальным месяцам находятся в группах (на каждый месяц по
группе);
3) Выделите ячейки март, апрель и май (вместе с названиями групп) и
нажмите кнопку Группировать по выделенному панели Группировать. Теперь данные по каждому году сгруппированы по временам
года;
4) Теперь щелкните левой кнопкой мыши по ячейке Группа1, нажмите
клавишу F2, введите название для группы – Зима;
315
315
315

Часть III. Сводные таблицы

5) Аналогичным образом переименуйте название группы весенних месяцев.
У вас должна получиться сводная таблица, показанная на рис. 7.11.

Рис. 7.11. Сводная таблица с данными, сгруппированными по временам года

Теперь вы можете свертывать/развертывать данные по каждой группе
(учтите, что, свернув/развернув данные по группе за один год, вы свернете/развернете данные за все годы) и по всем группам. Для того, чтобы сверна панели Активное поле
нуть данные во всех группах, нажмите кнопку
вкладки Параметры (группа вкладок Работа со сводными таблицами).

7.7.1. РАЗВЕРТЫВАНИЕ ДАННЫХ В СВОДНОЙ ТАБЛИЦЕ
Наравне с группировкой ваших данных, вам может потребоваться развернуть данные для детального их анализа. Рассмотрим, как это можно сделать.
Обратимся к сводной таблице, показанной на рис. 7.11. Дважды щелкните
левой кнопкой мыши по ячейке январь в таблице (год не имеет значения),
вы увидите окно, показанное на рис. 7.12.
316
316

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

Рис. 7.12. Окно выбора поля для развертывания данных в Excel 2010

Выберите поле товар и нажмите кнопку ОК. Вы увидите, что сводная таблица стала такой, как показано на рис. 7.13.

Рис. 7.13. Сводная таблица с данными, развернутыми по полю товар

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

Часть III. Сводные таблицы
ПРИМЕЧАНИЕ.
Описанную выше операцию по развертыванию данных по месяцам можно выполнить другим способом. Достаточно из списка Названия столбцов перетащить
поле товар в конец списка Названия строк.

Стоит отметить, что вышеописанным способом вы можете развернуть данные не только в строках, но и в столбцах. Для этого дважды щелкните левой
кнопкой мыши по заголовку строки/столбца и в появившемся окне выберите поле, данные по которому хотите развернуть. Если вы дважды щелкнете левой кнопкой мыши по ячейке со значением, то вы получите таблицу, отображающую данные, по которым было вычислено интересующее вас
значение. Если в таблице, показанной на рис. 7.11, дважды щелкнуть левой
кнопкой мыши по ячейке со значением 7500, полученным на пересечении
строки январь (2006 год) и столбца товар1 (Завод1), вы увидите таблицу,
показанную на рис. 7.14.

Рис. 7.14. Таблица с исходными данными для вычисления значения в ячейке

Если щелкнуть по ячейке со значением 33768 – общий итог за январь 2006го года, вы увидите исходную таблицу с гораздо большим количеством исходных данных – рисунок 7.15.
Как вы могли убедиться, двойной щелчок по ячейке со значением сводной
таблицы приводит к формированию таблицы, структура которой аналогична структуре таблицы, по которой была построена ваша сводная таблица.
Если вы дважды щелкнете левой кнопкой мыши по ячейке с итоговым значением по всей таблице (ячейка в правом нижнем углу сводной таблицы),
вы получите таблицу, идентичную исходной, для построения вашей сводной таблицы.

Рис. 7.15. Таблица с исходными данными для вычисления итогового
значения за январь 2006-го года

318
318

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

7.7.2. ГРУППИРОВКА ЧИСЛОВЫХ ДАННЫХ ПО ИНТЕРВАЛАМ
О возможности группировки данных по интервалам мы говорили выше, но
конкретного примера не рассмотрели. Возможность группировки данных
по интервалам может оказаться очень полезной при анализе данных.
Взгляните на рисунок 7.16 . Здесь показан фрагмент таблицы с данными о
колебаниях курса валюты за июнь и июль по дням.

Рис. 7.16. Фрагмент таблицы, отображающей колебания курса валюты
за июнь и июль

Если теперь построить сводную таблицу, в список Названия столбцов
поместить поле Месяц, в список Названия строк поместить поле Число, а
в список значения поместить поле Отклонение и в качестве операции
вычисления значения задать среднее, вы получите сводную таблицу,
фрагмент которой показан на рис. 7.17.
319
319
319

Часть III. Сводные таблицы

Теперь вы можете сгруппировать данные в таблице по датам (интервалам
дат). Рассмотрим, как это сделать.
1) Щелкните левой кнопкой мыши по одной из ячеек поля Число сводной таблицы. Зайдите на вкладку Параметры (группа вкладок Работа со сводными таблицами). Взгляните на панель Группировать
– кнопка Группировать по полю стала активной. Нажмите ее;
2) В окне Группирование (рисунок 7.18) для параметра “начиная с”
укажите значение 1, для параметра “по” укажите значение 31, для
параметра “с шагом” укажите значение 5 (в зависимости от конкретного случая указывайте наиболее подходящее для вас значение
данного параметра) и нажмите кнопку ОК.
У вас должна получиться сводная таблица, показанная на рис. 7.19.
Группировка по полю подойдет для анализа данных в тех случаях, когда
вам необходимо получить итоговое значение за определенный период.
Кроме числовых значений, вы можете группировать даты. Взгляните на
рисунок 7.20.

Рис. 7.17. Фрагмент сводной таблицы по данным о колебаниях курса валюты

320
320

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

Рис. 7.18. Окно Группирование в Excel 2010

Рис. 7.19. Сводная таблица с данными, сгруппированными по числам месяца

321
321
321

Часть III. Сводные таблицы

На рис. 7.20 показана часть таблицы с данными о балансе фирмы. Как вы
можете видеть, периодичность обновления данных о балансе – неделя. Если
построить сводную таблицу по данным таблицы, показанной на рис. 7.20,
она не будет отличаться от исходной – рисунок 7.21.

Рис. 7.20. Фрагмент таблицы с данными о балансе фирмы

Зато теперь вы можете сгруппировать данные по датам. Для этого щелкните
левой кнопкой мыши по одной из ячеек столбца с датами и на панели
Группировать вкладки Параметры (группа вкладок Работа со сводными
таблицами) и нажмите кнопку Группировать по полю. Укажите период,
в течение которого вы хотите сгруппировать данные, и выберите шаг для
группировки – рисунок 7.22.
Если теперь в качестве значения операции для вычисления поля из списка
Значения указать среднее, вы получите сводную таблицу, показанную на
рис. 7.23.
Для того чтобы разгруппировать данные, щелкните левой кнопкой мыши
по одной из ячеек столбца с датами (сгруппированными) и на панели
Группировать вкладки Параметры (группа вкладок Работа со сводными
таблицами) нажмите кнопку Разгруппировать.
ПРИМЕЧАНИЕ.
При указании периода (шага) для группировки дат вы можете указать несколько
периодов, например квартал и месяц. Так данные будут сгруппированы по кварталам и месяцам – рисунок 7.24.

322
322

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

Рис. 7.21. Сводная таблица с данными о
балансе фирмы

Рис. 7.23. Сводная таблица со
средними значениями данных о
балансе, сгруппированных по датам с
шагом месяц

Рис. 7.22. Окно параметров
группировки дат

Рис. 7.24. Данные о балансе фирмы,
сгруппированные по кварталам и по
месяцам

323
323
323

324
324

ГЛАВА 8.
СВОДНЫЕ ДИАГРАММЫ

325
325
325

Конечно, сводные таблицы позволяют эффективно анализировать данные,
но еще лучше, когда есть возможность визуализировать данные с помощью
графиков и диаграмм. В Excel 2010, как и в предыдущих версиях Excel, реализована возможность построения сводных диаграмм. Стоит отметить, что
сводные диаграммы могут быть созданы только на основе сводных таблиц.
Если в мастере построения сводных таблиц вы указали вариант сводная диаграмма, то будет создана сводная таблица, которая будет являться источником данных для сводной диаграммы. При построении сводных диаграмм
вы можете выбрать вид диаграммы так же, как вы делали это при построении обычных диаграмм (список видов сводных диаграмм такой же, как и
для обычных диаграмм).

8.1. ÏÎÑÒÐÎÅÍÈÅ ÑÂÎÄÍÎÉ ÄÈÀÃÐÀÌÌÛ ÍÀ ÎÑÍÎÂÅ
ÒÀÁËÈÖÛ Ñ ÈÑÕÎÄÍÛÌÈ ÄÀÍÍÛÌÈ
Для начала рассмотрим построение сводной диаграммы на основе обычной
таблицы, содержащей исходные данные. Для примеравозьмем таблицу, показанную на рис. 7.1.
1) Нажмите кнопку
грамм;

, чтобы открыть мастер сводных таблиц и диа-

2) В первом окне мастера (Шаг №1) укажите вариант сводная диаграмма (со сводной таблицей) для параметра Вид создаваемого отчета и
нажмите кнопку Далее >;
3) Во втором окне мастера (Шаг №2) нажмите кнопку
, выделите
область исходной таблицы с данными, нажмите кнопку
и кнопку Далее >;
326
326

Глава 8. Сводные диаграммы

4) В третьем окне мастера (Шаг №3) оставьте вариант новый лист для
параметра “Поместить таблицу в” и нажмите кнопку Готово.
Вы увидите, что будет создан новый рабочий лист Excel, в котором будет
присутствовать область построения сводной таблицы, а также область построения сводной диаграммы – рисунок 8.1.
Для построения сводной таблицы вам необходимо заполнить те же списки
полей, что и при построении сводной таблицы. Распределите поля из списка доступных полей исходной таблицы следующим образом:
1) Список Поля осей (категории) – поля Завод производитель и Товар;
2) Список Поля легенды (ряды) – поле Месяц;
3) Список Значения – поле Изготовлено.
У вас должны получиться сводная таблица и сводная диаграмма, которые
показаны на рис. 8.2.

Рис. 8.1. Рабочий лист Excel с областями построения сводной таблицы
и сводной диаграммы

327
327
327

Часть III. Сводные таблицы

Рис. 8.2. Сводная диаграмма и сводная таблица на основе таблицы с рис. 7.1

Так же, как и при построении сводной таблицы, для построения сводной
диаграммы вычисляется сумма по данным исходной таблицы. Если вы хотите изменить операцию вычисления значений сводной диаграммы, щелкните левой кнопкой мыши по строке Сумма по полю Изготовлено списка
Значения, в появившемся меню выберите Параметры полей значений и в
открывшемся окне на вкладке Операция выберите необходимый вариант.
Настройка внешнего вида сводной диаграммы аналогична настройке обычной диаграммы. Если вы хотите изменить тип сводной диаграммы, щелкните левой кнопкой мыши по области диаграммы и в появившемся контекстном меню выберите Изменить тип диаграммы, в появившемся окне
выберите нужный тип диаграммы.

8.2. ÏÎÑÒÐÎÅÍÈÅ ÑÂÎÄÍÎÉ ÄÈÀÃÐÀÌÌÛ ÍÀ ÎÑÍÎÂÅ
ÑÂÎÄÍÎÉ ÒÀÁËÈÖÛ
Вы можете построить сводную диаграмму на основе уже имеющейся сводной таблицы. Для примера возьмем сводную таблицу, показанную на рис.
328
328

Глава 8. Сводные диаграммы

7.9. Активируйте вкладку Параметры (группа вкладок Работа со сводными таблицами) и на панели Сервис нажмите кнопку Сводная диаграмма. В
появившемся окне выберите тип диаграммы и нажмите кнопку ОК. Если в
качестве типа диаграммы вы выбрали гистограмму, у вас должна получиться диаграмма, показанная на рис. 8.3.

Рис. 8.3. Сводная диаграмма, построенная на основе сводной таблицы с рис. 7.9

8.3. ÂÛÁÎÐ ÄÀÍÍÛÕ ÄËß ÏÎÑÒÐÎÅÍÈß ÑÂÎÄÍÛÕ ÒÀÁËÈÖ
È ÄÈÀÃÐÀÌÌ Ñ ÏÎÌÎÙÜÞ ÏÐÎÃÐÀÌÌÛ
MICROSOFT QUERY
При построении сводных таблиц и диаграмм на основе данных из внешнего
источника исходная таблица формируется в программе Microsoft Query.
Microsoft Query откроется после того, как вы укажете необходимые
настройки соединения с внешним источником данных. Рассмотрим пример
построения сводной таблицы на основе данных из MS SQL Server.
1) Откройте мастер построения сводных таблиц и диаграмм, нажав
кнопку
;
2) В первом окне мастера (Шаг №1) для источника данных укажите вариант во внешнем источнике данных и нажмите кнопку Далее >;
3) Во втором окне мастера (Шаг №2) нажмите кнопку Получить данные, вы увидите окно Выбор источника данных, показанное на рис.
8.4. В данном окне вы можете выбрать источник из списка или, если
329
329
329

Часть III. Сводные таблицы

нужного вам источника нет, добавить источник данных. Так как в
списке нет подключений к MS SQL Server, выберите вариант и нажмите кнопку ОК;

Рис. 8.4. Окно “Выбор источника данных”

4) В окне Создание нового источника данных (рисунок 8.5) укажите имя источника данных (вы можете указать любое имя, которое
хотите присвоить источнику), в качестве драйвера требуемой базы
данных укажите SQL Server и нажмите кнопку Связь;
5) В окне Вход в сервер SQL укажите сервер, на котором располагается
нужная база данных. Если для входа на сервер не требуется имя
учетной записи и пароль, оставьте галочку напротив пункта
Использовать доверительное соединение, в противном случае

Рис. 8.5. Окно “Создание нового источника данных”

330
330

Глава 8. Сводные диаграммы

снимите галочку и укажите имя и пароль для входа на сервер.
Нажмите кнопку Параметры, в списке База данных выберите
нужную базу данных и нажмите кнопку ОК. В окне Создание
нового источника данных нажмите кнопку ОК;
6) Теперь в списке доступных источников данных выберите созданное
вами соединение и нажмите кнопку ОК. Появится окно Создание
запроса: выбор столбцов – рисунок 8.6;

Рис. 8.6. Окно Создание запроса: выбор столбцов

7) Нажмите кнопку Параметры и для параметра Owner укажите значение dbo (таким образом, будут отображены таблицы и представления, созданные пользователем). Нажмите кнопку ОК. Теперь вы
можете добавить необходимые таблицы или поля таблиц базы данных, выбирая нужные в списке и нажимая кнопку “>”. После выбора всех необходимых таблиц или полей таблиц нажмите кнопку
Далее, откроется окно программы Microsoft Query – рисунок 8.7.
С помощью программы Microsoft Query вы можете выполнить SQL-запрос
на выборку данных из базы данных. Для этого нажмите кнопку SQL на панели инструментов Microsoft Query. Также данные можно выбрать средствами Microsoft Query.
В качестве примера была взята база данных, состоящая из трех таблиц: Данные сотрудника (в этой таблице содержатся данные о сотрудниках), Должность (в этой таблице хранятся названия должностей и данные о зарплате сотрудника, занимающего определенную должность) и Должности_работников (в этой таблице хранятся данные о том, какую должность занимает сотрудник).
331
331
331

Часть III. Сводные таблицы

Рис. 8.7. Окно программы Microsoft Query

Рассмотрим основные инструменты Microsoft Query для формирования исходной таблицы.
1) Вы можете добавить таблицу из базы данных, выбрав Таблица 

Добавить таблицу, после чего в списке вам будет необходимо в
списке Table выбрать нужную таблицу и нажать кнопку Добавить;
2) Для того, чтобы указать связи между таблицами, выполните Таблица
 Объединение, после чего вам следует указать поля двух таблиц,
по которым они будут связаны, и тип связи, после чего нажмите
кнопку Добавить;
3) Чтобы добавить поле из какой-либо таблицы в итоговую таблицу,
дважды щелкните левой кнопкой мыши по нужному полю. Поля в
итоговой таблице будут располагаться в том порядке, в котором вы
их добавляли. Чтобы удалить поле из итоговой таблицы, щелкните
левой кнопкой мыши по заголовку таблицы и нажмите кнопку
Delete.
332
332

Глава 8. Сводные диаграммы

После того, как вы укажете все необходимые связи между таблицами и добавите все нужные поля, будет сформирована итоговая таблица. Итоговая
таблица для рассматриваемого случая показана на рис. 8.8.

Рис. 8.8. Итоговая таблица с данными о должностях сотрудников

Чтобы вернуть данные из Microsoft Query в Excel, выберите Файл  Вернуть данные в Microsoft Office Excel, после этого окно Microsoft Query
закроется, и вы сможете построить сводную таблицу (или диаграмму) на
основе данных, полученных из внешнего источника. Сводная таблица, построенная на основе данных из итоговой таблицы, сформированной в Microsoft Query, показана на рис. 8.9.
Здесь мы рассмотрели наиболее общие возможности Microsoft Query, так
как в большинстве случаев пользователи строят сводные таблицы на основе таблиц Excel. Более полную информацию о работе с программой вы можете найти в справке Microsoft Query. Чтобы вызвать справку в окне Microsoft Query выберите Справка  Вызов справки. Также информацию о
Microsoft Query вы можете почерпнуть в интернете по адресу http://office.
microsoft.com/ru-ru/excel/HA100996641049.aspx

Рис. 8.9. Сводная таблица на основе данных из внешнего источника

333
333
333

ГЛАВА 9.
СТАТИСТИЧЕСКИЕ РАСЧЕТЫ
В EXCEL 2010

334
334

Статистические расчеты в Excel 2010 выполняются с помощью набора специализированных статистических функций и надстройки Пакета анализа. Процесс
построения формулы в Excel с использованием статистических функций аналогичен использованию любой другой функции. Работа с Пакетом анализа данных требует предварительной настройки и изучения режимов работы.

9.1. ÏÀÊÅÒ ÀÍÀËÈÇÀ ÄÀÍÍÛÕ
Модуль Пакета анализа входит в набор стандартной конфигурации Excel 2010
и предназначен для выполнения основных операций статистического анализа.
Результаты, полученные с помощью Пакета анализа данных, автоматически не
обновляются при внесении изменений в исходные данные. Для получения корректного результата необходимо повторно вызвать команды данного модуля.
Перед началом работы с Пакетом анализа данных необходимо убедиться в его
наличии:
1.

В уже открытой или вновь созданной книге Excel откройте вкладку Файл,
и выберите пункт Параметры.

Рис. 9.1. Активные настройки Excel

335
335
335

Часть IV. Статистика

2.

Выберите команду Надстройки. Проверьте наличие Пакета анализа в перечне Активных надстроек приложения (рис. 9.1). В случае отсутствия
Пакета анализа выполните действия, описанные в пп.3-4.

3.

В поле Управление выберите Надстройки Excel и нажмите на кнопку
.

4.

В открывшемся окне Доступные надстройки (рис. 9.2) установите флажок Пакет анализа и нажмите кнопку
. Если надстройка Пакета
анализа отсутствует в списке, нажмите кнопку
, чтобы найти ее.

Рис. 9.2. Доступные настройки

Установленный Пакет анализа доступен на вкладке Данные в группе Анализ
(пункт Анализ данных).
К основным инструментам Пакета анализа относятся:
1.

Однофакторный дисперсионный анализ.

2.

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

3.

Двухфакторный дисперсионный анализ без повторений.

4.

Корреляция.

5.

Ковариация.

6.

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

336
336

Глава 9. Статистические расчеты в Excel 2010

7.

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

8.

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

9.

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

10. Гистограмма.
11. Скользящее среднее.
12. Генерация случайных чисел.
13. Ранг и персентиль.
14. Регрессия.
15. Выборка.
16. Парный двухвыборочный тест для t-средних.
17. Двухвыборочный t-тест с одинаковыми дисперсиями.
18. Двухвыборочный t-тест с различными дисперсиями.
19. Двухвыборочный z-тест для средних.

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

Рис. 9.3. Выбор инструмента анализа

337
337
337

Часть IV. Статистика

Диалоговое окно режима предназначено для выбора параметров работы. Часть параметров является универсальной, т.е. относится ко всем режимам. Среди них:


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



Метки. Если первая строка или столбец входных данных содержат заголовок, то флажок необходимо установить в активное состояние.



Группирование. Переключатель определяет расположение входных данных (диапазон по строкам или по столбцам).



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

Остальные параметры относятся к конкретным режимам и будут рассмотрены далее.

9.2. ÌÅÒÎÄÛ ÎÏÈÑÀÒÅËÜÍÎÉ ÑÒÀÒÈÑÒÈÊÈ
Область математической статистики принято условно разделять на две подобласти: описательную и аналитическую статистики. Описательная статистика базируется на методах описания статистических данных, представления их в форме таблиц, распределений, гистограмм и т. п. К самым востребованным с практической точки зрения показателям описательной статистики относятся средняя
арифметическая, медиана, дисперсия и стандартное отклонение.
Анализ данных в рамках описательной статистики можно проводить как с помощью специализированных статистических функций, так и с помощью режимов
Пакета анализа данных, Гистограмма, Выборка.
В режиме Описательная статистика (рис. 9.4.) проводится статистический
расчет по основным показателям положения, разброса и асимметрии выборочной совокупности.
В данном режиме кроме универсальных параметров, рассмотренных ранее,
заполняются:


Итоговая статистика. Флажок устанавливается в активное состояние при необходимости получить в результате расчета набор стандартных показателей:
1. Средняя арифметическая выборки;
2. Средняя ошибка выборки;
3. Медиана;
4. Мода;

338
338

Глава 9. Статистические расчеты в Excel 2010

Рис. 9.4. Описательная статистика



5. Оценка стандартного отклонения по выборке;
6. Оценка дисперсии по выборке;
7. Оценка эксцесса по выборке;
8. Оценка коэффициента асимметрии по выборке;
9. Размах вариации выборки;
10. Максимальный и минимальный элементы выборки;
11. Сумма элементов выборки;
12. Количество элементов выборки;
13. K-наибольший и К-наименьший элемент выборки;
14. Предельная ошибка выборки.
Уровень надежности. Флажок устанавливается в активное состояние для расчета значения предельной ошибки выборки при заданном уровне надежности.



K-наименьший. Флажок устанавливается в активное состояние, если требуется получить значение для k-наименьшего значения элемента выборки. При k=1
определяется минимальное значение элемента выборки.



K-наибольший. Флажок устанавливается в активное состояние, если требуется получить значение для k-наибольшего значения элемента выборки. При k=1
определяется максимальное значение элемента выборки.

Среди основных стандартных функций Excel, относящихся к режиму Описательная статистика, можно выделить: СРЗНАЧ, СРЗНАЧА, УРЕЗСРЕДНЕЕ, СРГАРМ, СРГЕОМ, МЕДИАНА, МОДА, КВАРТИЛЬ, ДИСП и др.
339
339
339

Часть IV. Статистика

ПРИМЕР 1. СОГЛАСНО ДАННЫМ, ПРЕДСТАВЛЕННЫМ ФЕДЕРАЛЬНОЙ СЛУЖБЫ ГОСУДАРСТВЕННОЙ СТАТИСТИКИ СТОИМОСТЬ МИНИМАЛЬНОГО НАБОРА ПРОДУКТОВ
ПИТАНИЯ, ВХОДЯЩИХ В ПОТРЕБИТЕЛЬСКУЮ КОРЗИНУ, ПО НЕКОТОРЫМ РЕГИОНАМ
РОССИИ НА СЕНТЯБРЬ 2010 БЫЛА УСТАНОВЛЕНА В РАЗМЕРЕ (РУБ.):












Центральный федеральный округ - 2416,96
Северо-Западный федеральный округ - 2708
Южный федеральный округ- 2355,74
Северо-Кавказский федеральный округ-2410,08
Приволжский федеральный округ - 2207,66
Уральский федеральный округ - 2517,06
Ханты-Мансийский автономный округ- 2925,82
Ямало-Ненецкий автономный округ- 3436,23
Сибирский федеральный округ-2324,98
Дальневосточный федеральный округ - 3281,67

Требуется рассчитать основные показатели описательной статистики и на
основе полученных результатов сделать соответствующие выводы.

РЕШЕНИЕ.
1.

Представим исходные данные в виде таблицы, где столбцами являются
соответствующие показатели. Наименование регионов введите в диапазон А1:А10, стоимость продуктов питания — в диапазон В1:В10. В результате получится таблица, представленная на рис. 9.5.

Рис. 9.5. Исходная таблица данных

340
340

Глава 9. Статистические расчеты в Excel 2010

2.

3.

Далее необходимо провести элементарную статистическую обработку.
Для этого, нажмите на пункт меню Данные, выберите команду Анализ
данных в разделе Анализ. Затем в появившемся списке Инструменты
анализа выберите строку Описательная статистика.
В появившемся диалоговом окне (рис. 9.6) в рабочем поле Входной интервал укажите входной диапазон — В1:В10. Установите флажок Группирование в значение по столбцам. Активировав переключателем рабочее поле Выходной интервал, укажите выходной диапазон — ячейку А12. Установите флажок в поле Итоговая статистика. Уровень надежности задайте равным 95%. Нажмите кнопку
.

Рисунок 2.3. Диалоговое окно Описательная статистика

В результате анализа получим в диапазоне В12:В27 наименования
основных показателей описательной статистики и их значения А12:А27
(рис. 9.6)

На основании полученных результатов можно сделать предположение, что средняя стоимость минимального набора продуктов питания в целом по всем регионам России в сентябре 2010 г. находилась в пределах от 2355,561руб. до
2961,313 руб.

341
341
341

Часть IV. Статистика

Рис. 9.7. Результат работы инструмента Описательная статистика

Данный вывод был сформулирован на основании следующих выкладок. Доверительный интервал находится по формуле 1:
(1)
где


– средняя арифметическая выборки (показатель среднее на рис. 9.7),



– предельная ошибка выборки (показатель уровня надежности в 95% на рис.
2.4.),

Следовательно, левая граница доверительного интервала равна 2355,561, а правая граница доверительного интервала составит 2961,313.
Коэффициент ковариации рассчитывается по формуле 2 и равен 16%.
(2)
где
– коэффициент ковариации,



– стандартное отклонение по выборке (показатель стандартного отклонения
на рис. 2.4.),
Коэффициент ковариации значительно меньше 50%, что свидетельствует о небольших колебаниях признака в исследованной выборочной совокупности.
342
342

Глава 9. Статистические расчеты в Excel 2010

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

ПРИМЕР 2. ТЕСТЫ ДЛЯ ПРОВЕРКИ ЗНАНИЙ ДОЛЖНЫ БЫТЬ СБАЛАНСИРОВАНЫ ПО
УРОВНЮ СЛОЖНОСТИ, ТРУДНЫЕ ВОПРОСЫ СЛЕДУЕТ КОМПЕНСИРОВАТЬ ЛЕГКИМИ.
ДЛЯ ОЦЕНКИ ПРИГОДНОСТИ ТЕСТА РЕКОМЕНДУЕТСЯ ПРОВОДИТЬ МНОГОКРАТНУЮ
СТАТИСТИЧЕСКУЮ ОБРАБОТКУ СЫРОГО БАЛЛА, НАБРАННОГО В РЕЗУЛЬТАТЕ ТЕСТИРОВАНИЯ. ЗА КАЖДЫЙ ВЕРНЫЙ ОТВЕТ НА ВОПРОС ПРИСВАИВАЕТСЯ ЕДИНИЧНЫЙ
БАЛЛ, НЕПРАВИЛЬНЫЙ ОТВЕТ ИЛИ ОТСУТСТВИЕ ОТВЕТА – НОЛЬ БАЛЛОВ.
На рис. 9.8. приведены обработанные по дихотомической шкале результаты тестирования. Необходимо ответить на вопрос, является ли данный тест
пригодным для тестирования.

Рис. 9.8. Оценка по дихотомической шкале

РЕШЕНИЕ
1.

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

2.

Высчитаем индивидуальный балл испытуемого. Выделите
ячейку L3. Откройте вкладку Формулы и выберите функцию
343
343
343

Часть IV. Статистика

Рис. 9.9. Матрица в Excel

Автосумма. Задайте интервал суммирования B3:K3. Нажмите Enter. Теперь выделите ячейку L3 с высчитанной суммой и
потяните за нижнюю правую границу ячейки до L9. Нажмите
Enter. В ячейках L3:L9 хранятся индивидуальные баллы каждого испытуемого (рис. 9.10).

Рис. 9.10. Матрица с подсчетом итоговых баллов

344
344

Глава 9. Статистические расчеты в Excel 2010

3.

Далее необходимо вычислить среднее значение – СРЗНАЧ
(L3:L19), моду - МОДА.ОДН (L3:L9), медиану – МЕДИАНА (L3:L9), дисперсию - ДИСП.В (L3:L9), стандартное отклонение - СТАНДОТКЛОН.В (L3:L9), асимметрию – СКОС
(L3:L9) и эксцесс – ЭКСЦЕСС (L3:L9). Входной интервал будут определять индивидуальные баллы (диапазон L3:L9). Найденные значения числовых характеристик приведены на рис.
9.11.

Рисунок 2.8. Найденные числовые значения

Рис. 9.11. Найденные числовые значения

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

345
345
345

Часть IV. Статистика

Исходя из вышесказанного, можно сделать вывод, что тест требует доработки.
Следует или снизить уровень сложности отдельных вопросов или добавить легкие вопросы в тест.
Режим Гистограмма определяет частоту (выборочную и интегральную) вхождения данных в заданные интервалы. Полученные данные отображаются в таблицах и гистограммах.
Диалоговое окно режима показано на рис. 9.12.

Рис. 9.12. Режим Гистограмма

Рассмотрим параметры данного режима:


Интервал карманов. Необязательный параметр задает интервалы –
карманы. Значения вводятся или диапазоном ячеек или набором значений, определяющих границы интервалов. Данные указываются в возрастающем порядке. При отсутствии значения данного параметра интервалы создаются автоматически, а распределение будет равномерным между максимальным и минимальным значением входных данных.



Интегральный процент. Флажок в активном состоянии обеспечивает
отображение интегральных процентных отношений на гистограмме.



Вывод графика. Флажок в активном состоянии задает построение
встроенной диаграммы на листе с выходным интервалом.

346
346

Глава 9. Статистические расчеты в Excel 2010

К режиму Гистограмма относятся следующие статистические функции:
ЧАСТОТА, СЧЕТ, СЧЕТЗ.

ПРИМЕР. ПОСТРОИТЬ ЭМПИРИЧЕСКОЕ РАСПРЕДЕЛЕНИЕ СТОИМОСТИ ПРОЕЗДА В
РУБЛЯХ В РАЗНЫХ ГОРОДАХ РОССИИ ПО СОСТОЯНИЮ НА СЕНТЯБРЬ 2010 ГОДА.
ДАННЫЕ ПРЕДСТАВЛЕНЫ СЛЕДУЮЩЕЙ ВЫБОРКОЙ:
10
20
15
22
10
8
9
8
12
11
10
11
14
10

13
11
12
9
8
13
9
12
8
9
8
14
15
15

15
12
13
15
25
12
13
20
13
10
12
12
20
9

12
25
8
20
10
14
25
13
12
13
20
9
14
12

10
10
10
12
12
11
10
10
14
25
13
9
8
20

РЕШЕНИЕ.
1.

В ячейку А1 введите слово Стоимость, а в ячейки А2:Е15 — значения стоимости проезда.

2.

Выберите режим Гистограмма пакета Анализа данных. Для этого, нажмите на пункт меню Данные, выберите команду Анализ
данных в разделе Анализ. Затем в появившемся списке Инструменты анализа выделите строку Гистограмма и нажмите на кнопку
.
3. Заполните параметры окна Гистограмма: входной диапазон задайте как (А2:Е15); выходной диапазон — ссылку на левую верхнюю
ячейку выходного диапазона (H1). Переведите в активное состояние флажки Интегральный процент и Вывод графика. После
этого нажмите кнопку
.
Результат, полученный после обработки данных в режиме Гистограмма, показан на рис. 9.13.
347
347
347

Часть IV. Статистика

Рис. 9.13. Результат анализа в режиме Гистограмма

В режиме Выборка формируется выборка из указанного входного интервала,
т.е. определяется группа элементов, выбранная из совокупности заданных элементов.
В диалоговом окне данного режима (рис. 9.14) задается:


Метод выборки. Переключатель определяет один из возможных
вариантов построения выборки – Периодический или Случайный. Периодическая выборка формируется на основе периода, за-

Рис. 9.14. Режим выборка

348
348

Глава 9. Статистические расчеты в Excel 2010

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

ПРИМЕР 1. ПЕРИОДИЧЕСКАЯ ВЫБОРКА ШИРОКО ПРИМЕНЯЕТСЯ ДЛЯ КОНТРОЛЯ
КАЧЕСТВА ПРОДУКЦИИ НА ПРОИЗВОДСТВЕ И В ТОЧКАХ СБЫТА. ДЛЯ ПРОВЕРКИ СООТВЕТСТВИЯ ТОВАРА ПРЕДЪЯВЛЕННЫМ К НЕМУ ТРЕБОВАНИЯМ ПО СТАНДАРТИЗАЦИИ И БЕЗОПАСНОСТИ, ИЗ ОБЩЕЙ ПАРТИИ ПРОДУКЦИИ МОЖЕТ БЫТЬ ИЗВЛЕЧЕН
КАЖДЫЙ N-ЫЙ ТОВАР. ДАННАЯ ВЫБОРКА НОСИТ НАЗВАНИЕ МЕХАНИЧЕСКОЙ И ХАРАКТЕРИЗУЕТСЯ ОБЪЕМОМ ВЫБОРКИ (ОБЪЕМ ПРОДУКЦИИ, ИЗЫМАЕМОЙ ДЛЯ ПРОВЕДЕНИЯ ЭКСПЕРТИЗЫ) ОТНОСИТЕЛЬНОГО ОБЩЕГО ОБЪЕМА ГЕНЕРАЛЬНОЙ СОВОКУПНОСТИ (ОБЪЕМ ВСЕЙ ПРОИЗВЕДЕННОЙ ИЛИ РЕАЛИЗУЕМОЙ ПАРТИИ). ТАК,
ЕСЛИ НЕОБХОДИМО ПРОИЗВЕСТИ 5% ВЫБОРКУ, ТО ОТБИРАЕТСЯ КАЖДЫЙ 20-ЫЙ
ТОВАР, ДЛЯ 10% ВЫБОРКИ – КАЖДЫЙ 10-ЫЙ.
Предположим, что необходимо произвести 20% выборку представленного ниже
ассортимента детских игрушек:
Код

Наименование

20507

Снеговик

35769

Волк-модник

78000

Олень

43689

Динозавр

88864

Погремушка «Зайка»

92347

Лунтик

65658

Жираф

47047

Котенок Гав муз.

92365

Фунтик муз.

12470

Ленивец

35768

Медведь Энди муз.

78007

Бабка-Ёжка муз.

16945

Пчелка с платочком муз.

84668

Медвежонок мал.муз.

13213

Зайчик Баффи

349
349
349

Часть IV. Статистика

РЕШЕНИЕ.
1.

Представим исходные данные в виде таблицы, где столбцами являются код и наименование игрушек. Введите код товара в диапазон А2:А16,
а наименование — в диапазон В2:В16. В результате получится таблица,
представленная на рис. 9.15.

2.

Далее необходимо построить периодическую выборку с периодом 5.
Для этого нажмите на пункт меню Данные, выберите команду Анализ
данных в разделе Анализ. Затем в появившемся списке Инструмен-

Рис. 9.15. Исходная таблица данных

ты анализа выберите строку Выборка и нажмите на кнопку
3.

.

В появившемся диалоговом окне (рис. 9.16) в рабочем поле Входной
интервал укажите входной диапазон — А2:А16. Установите переключатель Метод выборки в значение Периодический. В поле Период введите 5. Укажите начало выходного интервала. Нажмите кнопку
.

В результате были отобраны три игрушки с кодами 88864 (Погремушка «Зайка»), 12470 (Ленивец), 13213 (Зайчик Баффи).

350
350

Глава 9. Статистические расчеты в Excel 2010

Рис. 9.16. Диалоговое окно Выборка

351
351
351

Группа подготовки издания:
Зав. редакцией компьютерной литературы: М. В. Финков
Редактор: М. А. Финкова
Корректор: А. В. Громова

ООО «Наука и Техника»
Лицензия №000350 от 23 декабря 1999 года.
198097, г. Санкт-Петербург, ул. Маршала Говорова, д. 29.
Подписано в печать 08.10.2012. Формат 70х100 1/16.
Бумага газетная. Печать офсетная. Объем 22 п. л.
Тираж 2000. Заказ

Отпечатано в ГП ПО «Псковская областная типография»
180004, г. Псков, ул. Ротная, 34.

352
352