Анализ и визуализация данных в Microsoft Excel в примерах и задачах

 
 
 
 
 
 
 
Н. А. Полковникова 
 
 
 
 
 
АНАЛИЗ И ВИЗУАЛИЗАЦИЯ ДАННЫХ 
В MICROSOFT EXCEL 
В ПРИМЕРАХ И ЗАДАЧАХ 
 
 
Практическое пособие 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Москва    Вологда 
«Инфра-Инженерия» 
2023 
1 

УДК 004 
ББК 32.81 
П51 
 
 
 
Рецензент: 
д. т. н., доцент ФГБОУ ВО «Государственный морской университет  
имени адмирала Ф. Ф. Ушакова» Попов А. Н. 
 
 
 
 
Полковникова, Н. А.  
П51  
Анализ и визуализация данных в Microsoft Excel в примерах и задачах : практическое пособие / Н. А. Полковникова. – Москва ; Вологда :  
Инфра-Инженерия, 2023. – 172 с. : ил., табл. 
ISBN 978-5-9729-1485-2 
 
Изложена работа с формулами, встроенными функциями, таблицами, диаграммами, визуализацией данных на географических картах, импорт данных из различных 
источников и Интернет-ресурсов, а также разработка макросов на языке программирования VBA. Темы упорядочены от простых по основам работы до более сложных, связанных с разработкой программных приложений с графическим интерфейсом. Стиль изложения материала основан на пошаговом описании действий с рисунками (скриншотами) полученных результатов на каждом этапе выполнения.  
Пособие позволяет освоить работу в программе Microsoft Excel и может быть использовано как для самостоятельного изучения, так и для проведения лабораторных, 
практических работ и научно-технических вычислений. 
 
УДК 004 
ББК 32.81 
 
 
 
 
 
 
 
 
 
 
 
ISBN 978-5-9729-1485-2 
© Полковникова Н. А., 2023 
 
© Издательство «Инфра-Инженерия», 2023 
 
© Оформление. Издательство «Инфра-Инженерия», 2023 
2 

СОДЕРЖАНИЕ 
ВВЕДЕНИЕ .................................................................................................................. 5 
1. ИЗУЧЕНИЕ ИНТЕРФЕЙСА MS EXCEL ............................................................. 8 
1.1. Основные сведения о табличном редакторе MS Excel 
..................................... 8 
1.2. Ввод формул ......................................................................................................... 9 
1.3. Виды адресных ссылок ...................................................................................... 10 
1.4. Закрепление областей окна для блокировки строк и столбцов 
..................... 11 
1.5. Преобразование текста в речь 
........................................................................... 12 
1.6. Сочетания «горячих» клавиш ........................................................................... 13 
1.7. Именованные диапазоны ................................................................................... 14 
1.8. Автоматическое заполнение ячеек листа данными ........................................ 15 
1.9. Диагностика ошибок в формулах MS Excel .................................................... 16 
2. РАБОТА С ФУНКЦИЯМИ И ФОРМУЛАМИ В MS EXCEL .......................... 17 
2.1. Работа с формулами ........................................................................................... 17 
2.2. Статистические расчёты .................................................................................... 19 
2.3. Конкатенация строк и столбцов 
........................................................................ 20 
2.4. Работа с логическими выражениями 
................................................................ 22 
2.5. Установка фильтров для таблицы .................................................................... 27 
2.6. Условное форматирование ................................................................................ 29 
2.7. Использование функции «вертикальный просмотр» ..................................... 31 
2.8. Использование функции «вертикальный просмотр» с интервальным 
соответствием ............................................................................................................ 35 
2.9. Использование функции «горизонтальный просмотр» 
.................................. 36 
2.10. Инструмент «Проверка данных» .................................................................... 38 
2.11. Текстовые функции 
.......................................................................................... 39 
2.12. Инструмент «Текст по столбцам» .................................................................. 41 
3. ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ В MS EXCEL 
.......... 44 
3.1. Построение точечной диаграммы с графиком тренда 
.................................... 47 
3.2. Построение графиков математических и тригонометрических функций 
.... 53 
3.3. Построение гистограммы .................................................................................. 55 
3.4. Построение гистограммы на отдельном листе 
................................................ 58 
3.5. Построение гистограммы вида «объемная линейчатая с группировкой» 
.... 59 
3.6. Построение гистограммы с динамикой темпов роста .................................... 60 
3.7. Построение линейного графика с маркерами ................................................. 62 
3.8. Построение круговой диаграммы 
..................................................................... 64 
3.9. Построение диаграммы Ганта 
........................................................................... 65 
3.10. Построение лепестковой диаграммы ............................................................. 69 
3.11. Построение смешанной (комбинированной) диаграммы ............................ 71 
3.12. Построение пузырьковой диаграммы ............................................................ 74 
3.13. Создание инфографики 
.................................................................................... 78 
3.14. Построение диаграммы с областями и накоплением ................................... 82 
3.15. Анализ временных рядов и прогнозирование в MS Excel ........................... 83 
3.16. Решение транспортной задачи ........................................................................ 89 
 
3 

4. СОЗДАНИЕ 3D-КАРТЫ ДЛЯ ВИЗУАЛИЗАЦИИ ГЕОГРАФИЧЕСКИХ 
ДАННЫХ В MS EXCEL 
........................................................................................... 95 
4.1. Добавление данных на 3D-карту ...................................................................... 97 
4.2. Настройки внешнего вида 3D-карты 
.............................................................. 100 
4.3. Слои карты ........................................................................................................ 105 
4.4. Создание обзора................................................................................................ 107 
5. СОЗДАНИЕ МАКРОСОВ В MS EXCEL 
.......................................................... 111 
5.1. История языка программирования VBA 
........................................................ 111 
5.2. Создание макросов на языке программирования VBA 
................................ 113 
5.3. Типы данных языка программирования VBA 
............................................... 113 
5.4. Объявление переменных ................................................................................. 115 
5.5. Редактор VBA ................................................................................................... 115 
5.6. Пользовательская форма «UserForm» ............................................................ 117 
5.7. Процедуры и функции ..................................................................................... 119 
5.8. Создание информационных сообщений ........................................................ 119 
5.9. Условные операторы 
........................................................................................ 124 
5.10. Игра «Угадай число» с синтезом речи ......................................................... 126 
5.11. Анимация текста в ячейках MS Excel .......................................................... 127 
5.12. Создание макроса для работы с ячейками 
................................................... 131 
5.13. Создание диаграммы с макросами ............................................................... 131 
5.14. Создание пользовательских функций .......................................................... 135 
5.15. Создание приложения с графическим интерфейсом 
.................................. 136 
5.16. Создание приложения «Депозитный калькулятор» с графическим 
интерфейсом ............................................................................................................ 138 
6. ИМПОРТ И ЭКСПОРТ ТАБЛИЦ ИЗ БАЗЫ ДАННЫХ ................................. 143 
6.1. Получение данных из MS Excel 
...................................................................... 143 
6.2. Получение и загрузка данных из Интернета ................................................. 148 
6.3. Сводные таблицы ............................................................................................. 150 
7. ПРОСЛУШИВАНИЕ ИНТЕРНЕТ-РАДИОСТАНЦИЙ В MS EXCEL ......... 152 
8. ПРОСМОТР ВИДЕО С YOUTUBE В MS EXCEL .......................................... 157 
9. МАКРОС ДЛЯ ПОЛУЧЕНИЯ КУРСА ВАЛЮТ С САЙТА ЦБ РФ 
.............. 161 
Задание для самостоятельной работы ................................................................... 165 
Контрольные вопросы 
............................................................................................. 167 
БИБЛИОГРАФИЧЕСКИЙ СПИСОК 
.................................................................... 169 
 
4 

ВВЕДЕНИЕ 
 
Microsoft Excel (далее MS Excel) – одна из самых популярных программ для 
работы с электронными таблицами, а также функциональный инструмент визуализации и анализа данных. Установить MS Excel можно как для Windows и  
Mac-OS, так и для мобильных операционных систем (Android, iOS) через Google 
Play Market и Apple App Store. Программа MS Excel входит в состав Microsoft 
Office и поддерживает следующие основные форматы файлов (табл. 1). 
 
Таблица 1 
Основные форматы файлов MS Excel 
Формат 
Расширение 
Назначение 
.xls 
Стандартный формат рабочих книг Excel  
до 2007 г. 
Рабочая книга 
Excel 97-2003 
.xlsx 
Стандартный формат рабочих книг Excel  
c 2007 г. 
.xlsm 
Стандартный формат рабочих книг Excel  
с поддержкой макросов 
Рабочая книга 
Excel  
с поддержкой 
макросов 
Шаблон Excel 
.xltx 
Шаблон, созданный как основа для рабочих 
книг 
Шаблон Excel  
с поддержкой 
макросов 
.xltm 
Шаблон, созданный как основа для рабочих 
книг, включена поддержка макросов 
Надстройка  
Excel 
.xlam 
Надстройка Excel, направленная на добавление 
дополнительных функциональных  
возможностей и инструментов 
 
Первая версия MS Excel предназначалась для Mac и была выпущена в  
1985 году, а первая версия для Windows была выпущена в ноябре 1987 года. 
Название программы «Excel» c английского языка переводится как «превосходить, отличаться, выделяться, быть лучше». Поскольку на тот момент интерфейс был революционным среди подобных программ, т. к. активно использовал 
мышь, меню, кнопки и т. д. (рис. 1). 
 
5 

 
 
Рисунок 1 – Интерфейс MS Excel 1987 года 
 
Однако другая компания к 1985 году уже выпускала совсем другую программу под названием «Excel». В результате судебной тяжбы корпорация 
«Microsoft» была обязана использовать название «Microsoft Excel» во всех своих 
официальных пресс-релизах и юридических документах. Однако со временем 
«Microsoft» окончательно устранила эту проблему, приобретя товарный знак 
другой программы. 
Начиная с 1993 года, в состав Excel входит язык программирования Visual 
Basic for Applications (VBA), позволяющий автоматизировать задачи Excel. 
Название языка VBA означает «Visual Basic для прикладных программ». С помощью макрорекордера (MacroRecorder) на языке VBA можно создать программный код, повторяющий действия пользователя, и, таким образом, автоматизировать действия при работе с документами. Макрорекордер позволяет создавать программный код приложения или его отдельных частей автоматически, без 
использования программирования. Для программиста макрорекордер также полезен, поскольку даёт возможность автоматически разрабатывать фрагменты 
кода. Это позволяет увеличить скорость разработки и уменьшить время отладки. 
Язык VBA является результатом пересечения двух ветвей развития информационных технологий: языка программирования Basic и макроязыков текстовых редакторов, программ работы с электронными таблицами и других приложений. 
Язык программирования Basic был создан в 1964 г. профессорами математического факультета Дартмутского колледжа (США) – Джоном Кемени (John Kemeny) и Томасом Куртцом (Thomas Kurtz) для обучения студентов навыкам программирования. Название языка является аббревиатурой от «Beginner’s Allpurpose Symbolic Instructional Code» (многоцелевой язык символьных команд для 
начинающих).  
6 

В 1975 г. основатели фирмы Microsoft Билл Гейтс и Пол Аллен создали новую версию Basic для первых компьютеров «Альтаир» (MITS Altairs). Позже эта 
версия стала одним из самых популярных языков программирования в мире.  
С появлением персональных компьютеров IBM PC язык Basic стал стандартом в 
программировании. В начале 1990-х гг. вышла операционная система Microsoft 
Windows, которая использовала новый графический интерфейс пользователя 
(GUI), а в 1991 г. вышла первая версия IDE Microsoft Visual Basic (VB). Основной 
задачей для этого языка было представить простой инструмент разработки в новой операционной системе Windows.  
В 1994 году Microsoft выпустила Visual Basic for Applications (VBA). 
Именно в это время, после включения VBA в состав MS Office, Basic стал одним 
из основных стандартов программирования для Windows. В отличие от VB, язык 
VBA не позволяет создать .exe файл и не работает без пакета MS Office. Проект 
не существует вне документа и не находится вне его. VBA является встроенным 
языком программирования приложений MS Office, его можно применить в Excel, 
Access, Word, PowerPoint. Особенностью VBA является то, что при создании 
приложения за основу берётся документ: нельзя создать приложения независимо 
от документа (рабочей книги в Excel, документа в Word). VBA – это объектноориентированный язык программирования высокого уровня, являющийся одним 
из диалектов очень популярного языка программирования Visual Basic. Язык 
VBA поддерживает использование (но не создание) DLL от ActiveX и элементы 
объектно-ориентированного программирования. VBA – это набор средств для 
создания собственных программ и для автоматизации имеющихся приложений 
под запросы пользователя. Язык VBA имеет графическую инструментальную 
среду, позволяющую создавать экранные формы и управляющие элементы. С его 
помощью можно создавать свои собственные функции для Excel, вызываемые 
мастером функций, разрабатывать макросы, создавать собственные меню и многое другое. 
 
7 

1. ИЗУЧЕНИЕ ИНТЕРФЕЙСА MS EXCEL 
 
1.1. Основные сведения о табличном редакторе  
MS Excel 
 
MS Excel – это табличный редактор (процессор), т. е. программа, которая 
позволяет работать с данными в табличном формате. Максимальное количество 
строк на одном листе составляет 1 048 576, а максимальное количество столбцов 16 384. Строки обозначаются числами: 1, 2, 3, ..., 1048576. Столбцы обозначаются латинскими буквами: A, B, C, ..., Z, AA, AB, AC, ..., AZ, BA, BB, BC, ..., 
BZ, ..., XFD. 
Файлы, созданные в MS Excel, называются «рабочая книга» («Workbook»). 
Рабочая книга состоит из листов («Worksheets»): «Лист1», «Лист2» и т. д. Листы 
можно добавлять, удалять, переименовывать, копировать, перемещать и изменять цвет ярлыка листа.  
Минимальный элемент электронных таблиц – ячейка. Из ячеек состоит  
рабочее поле электронных таблиц, ячейки складываются в строки и столбцы 
(рис. 1.1).  
 
 
 
Рисунок 1.1 – Элементы листа электронной таблицы MS Excel 
 
8 

У каждой ячейки есть уникальное имя или адрес, который составляется из 
заголовка столбца и заголовка строки (например, B7, C2).  
Важным является определение диапазона ячеек. Диапазоном ячеек может 
быть любая выделенная область смежных или несмежных ячеек, а также одна 
ячейка. Например, это может быть строка, часть строки, разбросанные ячейки 
или блок ячеек. 
По умолчанию столбцы нумеруются буквами латинского алфавита, а 
строки – цифрами. Если необходимо, чтобы нумерация строк и столбцов отображалась только в виде цифр, необходимо зайти «Файл» ՜ «Параметры» ՜ 
«Формулы» и установить флажок «Стиль ссылок R1C1» и нажать «ОК» (рис. 1.2).  
 
 
 
Рисунок 1.2 – Установка параметра «Стиль ссылок R1C1» 
 
1.2. Ввод формул 
 
Ввод любой формулы в MS Excel начинается со знака равно =. Завершение 
ввода формулы завершается клавишей Enter. Выделите ячейку, в которую необходимо поместить формулу. Введите знак равно (=) для активизации строки формул 
и наберите формулу. Формула может включать в себя числа, знаки арифметических 
операций, скобки, адресные ссылки на ячейки, данные из которых используются 
для расчетов, математические и специальные функции, используемые в расчёте. 
Знаки, которые можно использовать в формуле, представлены в табл. 1.1. 
 
Таблица 1.1 
Основные знаки для ввода формул 
Знак 
Назначение 
+ 
сложение 
- 
вычитание 
/ 
деление 
9 

Окончание таблицы 1.1 
Знак 
Назначение 
* 
умножение 
^ 
возведение в степень 
( ) 
скобки 
% 
взятие процентов 
& 
знак амперсанд, объединение содержимого из разных ячеек  
в одну текстовую строку 
: 
используется для обозначения диапазона ячеек  
(например, B2:D5 – обозначает блок ячеек B2, B3, B4, B5, C2, C3, 
C4, C5, D2, D3, D4, D5) 
, 
оператор, объединяющий параметры в формуле 
> 
условие «больше» 
< 
условие «меньше» 
= 
условие «равно» 
>= 
условие «больше или равно» 
<= 
условие «меньше или равно» 
<> 
условие «не равно» 
 
В формулах, включающих элементы логических суждений, могут использоваться элементы текста, которые должны быть заключены в кавычки. Чтобы 
вставить ссылки на данные в ячейках в формулу, щёлкните по ячейке левой кнопкой мыши (адрес ячейки запишется в формулу). 
  
1.3. Виды адресных ссылок 
 
Адресные ссылки, в зависимости от характера применения данных в расчётных формулах, могут быть трёх видов: 
– относительная – это изменяющийся при копировании формулы адрес 
ячейки, содержащий исходный параметр, используемый в формуле. В строке 
формул относительная адресная ссылка имеет вид – A15, D27; 
– абсолютная – это не изменяющийся при копировании формулы адрес 
ячейки, содержащий исходный параметр в формуле. Для указания абсолютной 
адресации вводится символ $. Абсолютная ссылка на ячейку A1 записывается в 
виде $A$1, т. е. символом $ указывается, что в ссылке ни имя столбца, ни номер 
строки при копировании или перемещении изменяться не будут. Таким образом, 
при использовании относительной ссылки, ссылка на ячейку смещается при протягивании формулы (рис. 1.3а), а при использовании абсолютной ссылки адрес 
ячейки при протягивании не изменяется (рис. 1.3б). 
 
10