•САНКТ-ПЕТЕРБУРГ• •МОСКВА• •КРАСНОДАР• Э. Г. БУРНАЕВА, С. Н. ЛЕОРА Э. Г. БУРНАЕВА, С. Н. ЛЕОРА ОБРАБОТКА И ПРЕДСТАВЛЕНИЕ ДАННЫХ В MS EXCEL УЧЕБНОЕ ПОСОБИЕ Издание третье, стереотипное •САНКТ-ПЕТЕРБУРГ• •МОСКВА•КРАСНОДАР• 2021 УДК 004 ББК 32.973.26-018.2я73 Б 91 Бурнаева Э.Г.Обработка и представление дан- ных в MSExcel: учебное пособие для вузов/ Э.Г.Бурнаева, С.Н.Леора. — 3-е изд., стер.— Санкт-Петербург: Лань, 2021.— 156с.: ил.— Текст: непосредственный. ISBN 978-5-8114-8473-7 Книга посвящена эффективному использованию инструмен- тов приложения Excel для обработки и анализа многомерных дан- ных с целью их систематизации, выявления характера и струк- туры взаимосвязей. Пройдя путь от структурирования данных до создания простейшей базы данных, на основе которой можно стро- ить различные сводные таблицы для нахождения ответов на по- ставленные вопросы, читатель научится извлекать необходимую информацию и знание из множества данных. Дополнительно к этому, в книге рассматривается одна из наиболее востребованных задач— статистическая обработка данных, полученных в резуль- тате случайного эксперимента. Особое внимание уделено визуа- лизации данных с помощью диаграмм и дополнительных возмож- ностей, предоставляемых в последних версиях Excel. Книга адресована, прежде всего, студентам вузов нематема- тических направлений подготовки и может быть полезна аспи- рантам, инженерам, научным сотрудникам и преподавателям— всем, кто заинтересован в получении мощного и эффективного инструмента для выполнения всевозможных математических опе- раций при помощи компьютера. УДК 004 ББК 32.973.26-018.2я73 Рецензенты: С.М.ЕРМАКОВ— доктор физико-математических наук, профес- сор, зав. кафедрой статистического моделирования Санкт-Петер- бургского государственного университета; Н.К.КРИВУЛИН— доктор физико-математических наук, про- фессор кафедры статистического моделирования Санкт-Петербург- ского государственного университета. Обложка Е. А. ВЛАСОВА © Издательство «Лань», 2021 © Э.Г.Бурнаева, С. Н. Леора, 2021 © Издательство «Лань», © художественное оформление, 2021 ВВЕДЕНИЕ Сразвитием Интернета пользователи получили доступ к огромному объему данных. Сами по себе данные не пред- ставляют большой ценности. Необходимо научиться из- влекать из них нужную, адекватную информацию, а зна- чит, уметь эти данные структурировать, обрабатывать и, наконец, представлять. Самым доступным инструментом для реализации всех этих задач являются электронные таблицы. Excel— про- граммный продукт два в одном, в котором удобно сочета- ются вычислительные и графические возможности. Excel включает в себя элементы баз данных, что по- зволяет хранить структурированные данные, осуществ- лять их сортировку, фильтрацию, выборку данных, удов- летворяющих необходимым критериям. Реализованы здесь и возможности управления много- мерными данными на основе сводных таблиц, являющих- ся в какой-то мере аналогами мобильных таблиц в стати- стических пакетах (например, в SPSS). Надстройка Па- кет анализа позволяет проводить статистический анализ данных. И наконец, Excel как инструмент визуализации может использоваться для создания иллюстраций в стиле инфо- графики, современного направления информационного дизайна. Именно эти продвинутые возможностям являются пред- метом обсуждения в данном пособии. Теоретический и практический материал предназначен для работы в среде 6 Введение приложений Excel 2013, которые открывает новые воз- можности обработки и представления данных. Материал расположен в следующем порядке. Впервой главе рассматриваются таблицы как средство структури- рования и обработки данных. Во второй главе речь идет о базах данных и сводных таблицах как способах работы с многомерными данными в Excel. Втретьей главе рассмат- риваются диаграммы— мощное средство визуализации данных, информации и знаний. И,наконец, в четвертой главе представлены возможности Excel для статистической обработки данных. Данное пособие предназначено для проведения занятий по курсам «Информатика» и «Информационные техноло- гии в науке и образовании» со студентами и аспирантами гуманитарных специальностей, оно может быть также по- лезно всем пользователям, работающим в среде Microsoft Office. Авторы выражают большую благодарность рецензен- там этого издания за их квалифицированные коммента- рии и замечания. ГЛАВА 1 ТАБЛИЦЫ В EXCEL ТЕМА1.1. ОСНОВНЫЕ ПОНЯТИЯ Обработка данных в Excel обычно подразумевает рабо- ту с взаимосвязанной группой данных. Практически каж- дая последующая версия электронных таблиц расширяет возможности управления данными, вводя новые объек- ты. Поэтому необходимо обратить особое внимание на сходство и различие понятий диапазон, список и табли- ца, которые можно рассматривать как развитие различ- ных способов структурирования данных от простого к сложному. 1.1.1. ДИАПАЗОН Под диапазоном во всех версиях Excel понимается группа ячеек (две и более), объединенных обычно некото- рым смысловым значением. Для задания диапазона ис- пользуется двоеточие, например, A1:D2. Диапазон может быть смежным или несмежным. Для его выделения необходимо щелкнуть мышью по верхней левой ячейке диапазона и, не отпуская кнопку, переме- стить указатель в нижнюю правую ячейку диапазона. Вы- деление несмежных диапазонов осуществляется при на- жатой клавише Ctrl. Диапазону ячеек также можно присвоить имя, которое затем используется как ссылка на этот диапазон. Таким 8 Глава 1. Таблицы в Excel образом, на диапазон можно ссылаться по адресу или по имени. Смежный диапазон данных можно считать первым шагом структурирования данных. Такие инструменты, как сортировка, фильтрация, подведение итогов и другие, применимы только к смежным диапазонам, например к A3:G17 (рис.1.1). Рис.1.1 Список в Excel 1.1.2. СПИСОК Во всех версиях Excel вводится понятие списка, осно- вой которого являются смежный диапазон данных, удов- летворяющий следующим требованиям. 1.Список состоит из строк, называемых записями. По строкам описывается только одно событие (данные о кон- кретном человеке, фирме, сделке ит.д.). 2.Столбцы обычно называют полями. Каждый стол- бец должен содержать данные одного типа. 3.Верхняя строка списка, называемая заголовком, содержит имена соответствующих полей, обычно ее фор- мат (шрифт, цвет фона и т.п.) отличается от формата за- писей. Тема 1.1. Основные понятия 9 4.На пересечении строк и столбцов находятся данные. 5.Внутри списка не должно быть полностью пустых строк и столбцов, а также объединенных ячеек. 6.От остальной части рабочего листа список отделяет- ся хотя бы одним пустым столбцом слева и справа и хотя бы одной пустой строкой сверху и снизу. Список можно создать вручную, введя информацию в прямоугольный диапазон с учетом вышеперечисленных требований. Такой список по виду ничем не отличается от обычной таблицы Excel. Над списками можно произво- дить такие действия, как сортировка, фильтрация, под- ведение итогов и т.п. На рисунке1.1 приведен пример списка, который за- нимает диапазон А3:G17. Список имеет заголовок с име- нами полей. Каждая запись содержит информацию о кон- кретном туре. Список отсортирован по полю «Название фирмы». 1.1.3. ТАБЛИЦА ВExcel 2007/2010/2013 под таблицей понимается спи- сок, т.е. взаимосвязанная группа данных, обладающая дополнительными свойствами. По структуре таблица ничем не отличается от списка. Расширились только возможности управления данными, расположенными в таблице. Таблицы в Excel 2007/2010/2013 пользователи назы- вают «умными» таблицами, на их основе можно органи- зовывать и поддерживать личное хранилище данных. Таблица, как объект, создается на основе диапазона пустых ячеек или прямоугольного диапазона данных, с помощью последовательности команд «Вставка»(cid:2) «Таб- лицы»(cid:2) «Таблица». Для создания таблицы сначала нуж- но выделить диапазон (пункт1), а затем выполнить по- следовательность команд (пункт2). 1.Если исходный диапазон данных удовлетворяет требованиям, предъявляемым к спискам, т.е. она не со- держит пустых строк ит.д., тогда для выделения табли- цы достаточно поместить курсор в любую ячейку внутри 10 Глава 1. Таблицы в Excel диапазона. Если же в исходном диапазоне имеются пол- ностью пустые строки или столбцы или вообще нет дан- ных, то этот данный диапазон будет рассматриваться как таблица, и ввод данных в нее будет осуществляться по мере надобности, необходимо выделить весь этот диапазон. 2.Выполнить последовательность команд «Вставка»(cid:2) «Таблицы»(cid:2) «Таблица». Если первая строка выбранно- го диапазона содержит данные, которые являются заго- ловками столбцов, установите флажок «Таблица с заго- ловками». Если такой строки нет, строка с заголовками будет создана автоматически. Заметим, что в таблицах не должно быть объединен- ных ячеек, так как это нарушает их структуру и идеоло- гию: одна строка— одна запись, один столбец— одно поле, если использовать терминологию баз данных. Такие таб- лицы можно использовать как основу для создания про- стейших баз данных в Excel. 1.1.4. ОСОБЕННОСТИ РАБОТЫ С ТАБЛИЦАМИ Рассмотрим новые возможности работы с таблицами, начиная с версии Excel 2007. Создадим таблицу на основе диапазона на рисунке1.1. Способ1. Выделите любую ячейку диапазона А3:G17, выполните последовательность команд «Вставка»(cid:2) «Таб- лицы»(cid:2) «Таблица». Способ2. Для быстрого форматирования диапазона ячеек и преобразования его в таблицу выделите любую ячейку диапазона А3:G17 и выполните последователь- ность команд «Главная»(cid:2) «Стили»(cid:2) «Форматировать как таблицу»(cid:2) «Выбрать стиль». Врезультате произойдет следующее (рис.1.2): 1)будет создана таблица, к которой применен стиль форматирования по умолчанию; 2)в первую строку с заголовками помещены элементы «Фильтра» (стрелочки); 3)появится новая вкладка «Конструктор» («Работа с таблицами»).