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

Н. А. Полковников»






                АНАЛИЗ И ВИЗУАЛИЗАЦИЯ ДАННЫХ В MICROSOFT EXCEL В ПРИМЕРАХ И ЗАДАЧАХ





Практическое пособие















Москва Вологда
« Инфра-Инженерия» 2023

УДК 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

СОДЕРЖАНИЕ

ВВЕДЕНИЕ.........................................................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. СОЗДАНИЕ 31D-КАРТЫ ДЛЯ ВИЗУАЛИЗАЦИИ ГЕОГРАФИЧЕСКИХ ДАННЫХ В MS EXCEL...........................................95
4.1. Добавление данных на 3D-Kapiy..........................97
4.2. Настройки внешнего вида 3D-KapTbi.....................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 г.                  
Рабочая книга                                                        
Excel           .xlsm       Стандартный формат рабочих книг Excel    
с поддержкой                        с поддержкой макросов            
макросов                                                             
Шаблон Excel    .xltx      Шаблон, созданный как основа для рабочих  
                                             книг                    
Шаблон Excel               Шаблон, созданный как основа для рабочих  
с поддержкой    .xltm         книг, включена поддержка макросов      
макросов                                                             
Надстройка               Надстройка Excel, направленная на добавление
Excel           .xlam           дополнительных функциональных        
                                 возможностей и инструментов         

    Первая версия 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 Ke-meny) и Томасом Куртцом (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).





Книга! - Excel

РАЗМЕТКА СТРАНИЦЫ

ЯЧЕЙКИ

Рисунок 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

a)

Рисунок 1.3 - Сравнение применения относительной и абсолютной ссылки: а - относительная ссылка; б - абсолютная ссылка

    -      смешанная - это частично изменяющийся при копировании формулы адрес ячейки, содержащей исходный параметр.
    Смешанные ссылки бывают двух видов:
    -     с фиксацией адреса по столбцу, имеющие вид - $A1,
    -     с фиксацией адреса по строке; имеющие вид - A$1.
    При копировании формул с такими ссылками не изменяется та её часть, перед которой стоит символ $. Если символ $ стоит перед именем столбца, то координата столбца абсолютная, а строки - относительная. Если символ $ стоит перед номером строки, то, напротив, координата столбца относительная, а строки - абсолютная.


    1.4. Закрепление областей окна для блокировки строк и столбцов


    При работе с большими таблицами в MS Excel часто необходимо, чтобы определенная область листа оставалась видимой при прокрутке. Для закрепления заголовков (строк) или столбцов таблицы необходимо на вкладке «Вид» выбрать команду «Закрепить области» (рис. 1.4).


Рисунок 1.4 - Инструмент «Закрепить области»

11

    Для открепления областей необходимо снова нажать «Закрепить области» и выбрать команду «Снять закрепление областей» (рис. 1.5).


Рисунок 1.5 - Инструмент «Снять закрепление областей»


    1.5. Преобразование текста в речь



    В MS Excel рядом с панелью быстрого доступа нажмите кнопку «Настроить панель быстрого доступа» и выберите пункт «Другие команды...» (рис. 1.6).


Рисунок 1.6 - Настройка панели быстрого доступа

    Выбрать «Все команды» и затем добавить команду «Проговорить ячейки» на панель быстрого доступа (рис. 1.7).


12