Компьютерный анализ. Практикум в среде Microsoft Excel: учебное пособие для СПО

279 Pages·2020·17.292 MB·Russian

by  Журавлев А. Е., Макшанов А. В., Тындыкарь Л. Н.

А. Е. ЖУРАВЛЕВ,
А. В. МАКШАНОВ,
Л. Н. ТЫНДЫКАРЬ
КОМПЬЮТЕРНЫЙ АНАЛИЗ
ПРАКТИКУМ В СРЕДЕ
MICROSOFT EXCEL
Учебное пособие
САНКТ(cid:7)ПЕТЕРБУРГ
МОСКВА
КРАСНОДАР
2020
УДК 004
ББК 32.973я723
Ж91 Журавлев А. Е. Компьютерный анализ. Практикум в среде
Microsoft Excel: учебное пособие для СПО / А. Е. Журавлев,
А. В. Макшанов, Л. Н. Тындыкарь.— Санкт(cid:7)Петербург :
Лань, 2020.— 280с. : ил.— Текст : непосредственный.
ISBN 978(cid:29)5(cid:29)8114(cid:29)5678(cid:29)9
В учебном пособии подробно рассмотрены основные аспекты компью(cid:7)
терного анализа данных с использованием общедоступного инструментария.
Вкачестве основного инструмента предлагается табличный процессор
Microsoft Excel и его блок анализа данных как наиболее популярный
идоступный пакет.
Учебное пособие рекомендуется учащимся всех форм обучения в соот(cid:7)
ветствии с ФГОС 3++ профессионального учебного цикла по специальностям
среднего профессионального образования «Прикладная математика
иинформатика», «Информационные системы» и «Организация и техноло(cid:7)
гия защиты информации» и изучающим курсы, непосредственно связанные
с технологиями анализа данных, например «Компьютерный анализ»,
«Большие данные», «Слияние данных» и т. п. В качестве дополнительной
литературы в рамках тех же дисциплин пособие может быть использовано
студентами, аспирантами и преподавателями в ходе проведения экспери(cid:7)
ментов как вспомогательный инструмент обработки получаемых данных.
УДК 004
ББК 32.973я723
Îáëîæêà
П. И. ПОЛЯКОВА
© Издательство «Лань», 2020
© Коллектив авторов, 2020
© Издательство «Лань»,
художественное оформление, 2020
ВВЕДЕНИЕ
В нашей повседневной жизни, быту, производстве, бизнесе, иной профес-
сиональной деятельности, а также в научных исследованиях мы постоянно
сталкиваемся с событиями и явлениями с неопределенным исходом. Например,
торговец не знает, сколько посетителей придет к нему в магазин, рабочий —
сколько времени ему придется сегодня добираться до работы, бизнесмен — ка-
кой будет завтра или через месяц курс доллара, банкир — вернут или нет взя-
тый у него заем, страховщик — когда и какое ему придется выплачивать стра-
ховое вознаграждение и т. д. При этом нам постоянно приходится принимать в
подобных неопределенных, связанных со многими случайностями ситуациях
свои решения, иногда очень важные. В быту или в несложном бизнесе мы мо-
жем принимать такие решения на основе здравого смысла, интуиции, преды-
дущего опыта. Здесь мы часто можем сделать некий «запас прочности» на дей-
ствие случая: скажем, выходить из дома на десять минут раньше, чтобы уже
почти наверняка не опаздывать на работу.
Однако в более серьезном бизнесе в условиях жесткой конкуренции ре-
шения должны приниматься на основе тщательного анализа имеющейся ин-
формации, быть обоснованными и доказуемыми. Например, вряд ли банк или
совет директоров крупной корпорации примет решение о вложении денег в не-
который проект только потому, что он кому-то «представляется выгодным».
Здесь потребуется тщательный расчет, связанный с прогнозами состояния рын-
ка и рентабельности вложений, оценками возможных рисков и их последствий
и т. д. При этом уже вряд ли возможно делать большой «запас прочности»
на всякий случай, ибо тогда вас опередят конкуренты, умеющие считать лучше
и тем самым принимать более правильные и эффективные решения.
Для решения задач, связанных с анализом данных, при наличии случай-
ных и непредсказуемых воздействий математиками и другими исследователями
(биологами, психологами, экономистами и т. д.) за последние двести лет был
выработан мощный и гибкий арсенал методов, называемых в совокупности ма-
тематической статистикой (а также прикладной статистикой или анализом дан-
ных). Эти методы позволяют выявлять закономерности на фоне случайностей,
делать обоснованные выводы и прогнозы, давать оценки вероятностей их вы-
полнения или невыполнения. Введению в эти методы, а также их общедоступ-
ной инструментальной реализации и посвящено данное учебное пособие,
структурированное в виде практикума.

3
1. ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL

1.1. Цели и задачи
Основные цели выполнения лабораторной работы — изучение базовых
средств автоматизации простых вычислений в соответствии с заданными кри-
териями, знакомство с наиболее востребованным функционалом табличных
процессоров, обзор основных элементов типичной электронной книги, подго-
товка рабочей среды к выполнению последующих лабораторных работ в кон-
тексте анализа данных.
1.2. Обзор интерфейса и функционала среды

Рис. 1.1
Главная вкладка новой книги Microsoft Excel 2016/2019
Для выполнения предложенных работ рекомендуется среда исполнения
Microsoft Excel версии 2016 г. (или 2019, их интерфейс и возможности в рас-
4
сматриваемом контексте практически идентичны). По умолчанию при запуске
среды Microsoft Excel 2016/2019 и создании «пустой книги» появится следую-
щий интерфейс (рис. 1.1).
Этот «ленточный» исходный интерфейс содержит основные элементы
управления средой и электронной книгой, восемь базовых вкладок позволяют
полноценно автоматизировать простые процессы расчета и учета. Основные
компоненты интерфейса Microsoft Excel 2016:
⎯ вкладка «Файл». Основные средства работы с текущей электронной кни-
гой как файлом операционной системы. Возможность открыть/закрыть,
сохранить, распечатать и тому подобное текущий файл;
⎯ вкладка «Главная». Инструменты для работы с ячейками таблицы и их
содержанием. Форматирование объектов таблицы;
⎯ вкладка «Вставка». Средства по интеграции в таблицу различных дополни-
тельных объектов, таких как документы, иллюстрации, ссылки и т. п.;
⎯ вкладка «Разметка страницы». Работа с внутренней структурой книги, на-
стройка параметров страницы («листа»);
⎯ вкладка «Формулы». Редактор функций и формул, работа с математиче-
скими компонентами;
⎯ вкладка «Данные». Взаимодействие с внешними и внутренними объекта-
ми как источниками информации;
⎯ вкладка «Рецензирование». Предоставляет функционал для редакции,
верстки и публикации входящих (не авторских) книг;
⎯ вкладка «Вид». Настройка способа отображения книги. Также средство
управления макросами.
Каждая вкладка («лента») разделена внутри вертикальными разделителя-
ми на смысловые разделы. Название раздела написано в нижней части ленты.
Справа от каждого названия размещена кнопка в виде стрелки, позволяющая
более подробно настроить соответствующий элемент документа.
Нижняя часть книги состоит из списка именованных «листов» книги и стро-
ки состояния. Листы добавляются, используя кнопку «+», а управляются через кон-
текстное меню (рис. 1.2). Строка состояния отображает текущую информационную
сводку по содержанию книги (адресация, сумма, среднее и т. п.), а также элементы
для управления режимом отображения книги и ее масштабом.

Рис. 1.2
Контекстное меню управления листом
5
В обязательном порядке для выполнения контрольного задания слушате-
лю потребуются вкладки «Вставка» (рис. 1.3) и «Формулы» (рис. 1.4).

Рис. 1.3
Вкладка «Вставка»
Вкладка «Вставка» содержит элементы внедрения различных внешних и
внутренних объектов в структуру электронной книги, а также механизмы
управления ими.

Рис. 1.4
Вкладка «Формулы»
Вкладка «Формулы» (рис. 1.4, 1) содержит сгруппированные по темам
(например, «Логические», рис. 1.4, 2) готовые формулы и функции (например,
«ИЛИ», рис. 1.4 3) а также основные способы манипуляции.
Кнопкой «fx» или пунктом контекстного меню «Вставить функцию»
(рис. 1.4, 4) вызывается диалоговое окно (рис. 1.5) с перечнем всех формул и
функций, их описанием и средством быстрого поиска нужной функции.

Рис. 1.5
Диалоговое окно «Вставка функции»
6
В описании указан синтаксис (формальные правила, которым должна
удовлетворять запись вызова конкретной функции, т. е. ее форма записи) в виде
перечня аргументов (элементов функции, разделенных символом «;») и семан-
тика в виде описания. Например:
⎯ «ЕСЛИ» — название функции;
⎯ «ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)» —
синтаксис функции;
⎯ «проверяет, выполняется ли условие, и возвращает одно значение, если
оно выполняется, и другое значение, если нет» — семантика функции,
т. е. ее смысл;
⎯ «лог_выражение» — условие, которое проверяется самой функцией;
⎯ «значение_если_истина» — если условие выполняется, то происходят
действия, описанные здесь, например любая арифметическая или логиче-
ская операция (в том числе с ячейками таблицы), другая функция и т. д.;
⎯ «значение_если_ложь» — аналогично возможные действия при невыпол-
нении условия «лог_выражение».
После выбора функции открывается диалоговое окно с ее аргументами
(рис. 1.6), в котором также указан их синтаксис, семантика и итоговое зна-
чение.

Рис. 1.6
Диалоговое окно «Аргументы функции»
Аргументами часто являются другие функции, в таком случае они назы-
ваются вложенными. Для редактирования уже сделанной функции можно ис-
пользовать как диалоговые окна, так и текстовую запись в поле формулы.
В случае ручного исправления или написания функций следует крайне внима-
тельно проверять место и количество скобок и разделителей.
Таким образом, формула — это полная запись для конкретной ячейки, ее
содержимое, функция — часть формулы, выполняющая конкретные действия с
объектами, аргумент — часть функции, являющаяся набором входящих данных.
7
Для более подробного изучения интерфейса программы имеется возмож-
ность использовать встроенную справку Microsoft Excel 2016 (клавиша «F1»),
всплывающие краткие подсказки или официальный веб-сайт продукта.
Среда Microsoft Excel — это не просто табличный редактор (процессор),
но еще и мощный инструмент для различных математических и статистических
вычислений. В представленной среде имеется огромное число функций, пред-
назначенных для этих задач. Правда, не все эти возможности по умолчанию ак-
тивированы. Именно к таким скрытым функциям относится необходимый в
рамках курса «Компьютерный анализ» набор инструментов «Анализ данных».
Чтобы воспользоваться возможностями, которые предоставляет функция
«Анализ данных», необходимо активировать группу инструментов «Пакет анали-
за», выполнив определенные действия в настройках Microsoft Excel. Алгоритм
этих действий практически одинаков для версий программы 2010, 2013, 2016 и
2019 гг., и имеет лишь незначительные отличия у версии 2007 г.
1.3. Методические указания
В данном разделе представлена методика подготовки рабочей среды к ис-
пользованию в рамках курса «Компьютерный анализ». Приведенные алгорит-
мы, способы и примеры могут незначительно отличаться от доступной среды.
В этом случае корректную методику стоит выработать самостоятельно.
1.3.1. Включение блока «Анализ данных»
Перейдите во вкладку «Файл» (рис. 1.7). Если вы используете версию
Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office
в верхнем левом углу окна.

Рис. 1.7
Вкладка «Файл»
Кликаем по одному из пунктов, представленных в левой части открывше-
гося окна, — «Параметры» (рис. 1.8).
8
Рис. 1.8
Пункт меню «Параметры»
В открывшемся окне параметров Excel переходим в предпоследний в
списке в левой части экрана подраздел «Надстройки» (рис. 1.9).

Рис. 1.9
Подраздел «Надстройки»
В этом подразделе нас интересует нижняя часть окна. Там представлен
параметр «Управление» (рис. 1.10). В представленной выпадающей форме дан-
ного параметра следует выбрать значение «Надстройки Excel» и далее нажать
кнопку «Перейти…» справа от него.
9
Рис. 1.10
Параметр «Управление»
Открывается небольшое окно доступных надстроек (рис. 1.11). Среди
них нужно выбрать пункт «Пакет анализа» и поставить около него галочку. По-
сле этого нажать кнопку «OK», расположенную в самом верху правой части
окошка.

Рис. 1.11
Окно доступных надстроек
10