ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 06.06.2021
Просмотров: 517
Скачиваний: 1
36
6.
Каково назначение макроса?
7.
Отличительные особенности создания макроса.
8.
Что такое гипертекстовый документ?
9.
Как создать оглавление документа?
37
Тема 9
Программные средства решения вычислительных задач
средствами электронных таблиц
Лекции 9-10
Цель: использование табличного процессора Microsoft Excel для
решения вычислительных задач управленческой деятельности
План:
1.
Задачи структуризации и первичной обработки данных.
2.
Средства структуризации и первичной обработки данных:
сортировка; фильтрация; создание итоговых, сводных и консолидированных
таблиц.
3.
Визуализация данных, диаграммы.
4.
Регрессионная модель. Получение регрессионной модели.
5.
Корреляционные зависимости. Корреляционная модель.
6.
Подбор параметра. Поиск решения.
Список рекомендуемой литературы:
1.
Богатов Ф. Г. Практикум по информатике. Word-Excel-Access :
учебное пособие / Ф. Г. Богатов. – М. : Московский университет МВД
России, 2008 . – 264 с.
2.
Информатика : Учебник / под ред . Н. В. Макаровой – М. :
Финансы и статистика, 2005. – 768 с.
3.
Симонович С. В. Информатика. Базовый курс : учебник для вузов
/ С. В. Симонович. – СПб : Питер, 2006. 639 с.
4.
Степанова Е .Е. Информационное обеспечение управленческой
деятельности : учеб. пособие / Е. Е Степанова, Н. В. Хмелевская. – М. :
ФОРУМ: ИНФРА – М., 2002. – 154 с.
1 Задачи структуризации и первичной обработки данных
Электронная таблица – самая
распространенная
и мощная
информационная технология для профессиональной работы с данными. Для
управления работой с электронной таблицей созданы специальные
программные продукты - табличные процессоры. Электронная таблица — это
прикладная программа, предназначенная для обработки структурированных в
виде таблицы данных т.е. это компьютерный эквивалент обычной таблицы,
клетках (ячейках) которой записаны данные различных типов: текста, даты,
формулы, числа.
Особенность электронных таблиц заключается в возможности
применения формул для описания связи между значениями различных ячеек.
Расчет по заданным формулам выполняется автоматически. Изменение
содержимого какой-либо ячейки приводит к пересчету значений всех ячеек,
38
которые с ней связаны формульными отношениями, и тем самым к
обновлению всей таблицы в соответствии с изменившимися данными.
Одним из наиболее распространенных средств работы с документами,
имеющими табличную структуру, является программа Microsoft Excel.
Документ Excel называется рабочей книгой. Рабочая книга
представляет собой набор рабочих листов, каждый из которых имеет
табличную структуру и может содержать одну или несколько таблиц.
Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены
прописными латинскими буквами и, далее, двухбуквенными комбинациями.
Всего рабочий лист может содержать до 256 столбцов, пронумерованных от
A до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536
(максимально допустимый номер строки). Пересечении строк и столбцов
обазуют ячейки.
Ячейки являются минимальными элементами для хранения данных.
Обозначение отдельной ячейки сочетает в себе номера столбца и строки, на
пересечении которых она расположена, например: A1 или DE234.
Обозначение ячейки выполняет функции ее адреса. Адреса ячеек
используются при записи формул, определяющих взаимосвязь между
значениями, расположенными в разных ячейках. На данные, расположенные
в ячейках, можно ссылаться в формулах как на единое целое.
Прямоугольную группу ячеек называют диапазоном или блоком. Диапазон
ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в
противоположных углах прямоугольника, например A1:C15.
2 Средства структуризации и первичной обработки данных:
сортировка;
фильтрация;
создание
итоговых,
сводных
и
консолидированных таблиц
Сортировки данных. Процесс упорядочения строк в таблице
называется сортировкой. При сортировке изменяется исходный порядок
следования строк таблицы или записей базы данных. Чтобы можно было
вернуть исходный порядок записей лучше ввести столбец номеров записей.
Команда Данные - Сортировка устанавливает порядок следования
строк в таблице в соответствии с возрастанием или убыванием значений в
конкретном столбце. Если необходимо отсортировать записи по значениям
только одного столбца, надо установить курсор в этот столбец и
использовать кнопку сортировки на панели инструментов. В случае
многоуровневой сортировки, в окне команды Данные - Сортировка
указываются последовательно заголовки столбцов таблицы. Если таблица
размещена не в начале листа Excel, то перед вызовом команды сортировки
надо выделить таблицу или заголовки ее столбцов. Если в таблице нет строки
заголовков столбцов, то идентифицировать столбцы можно по обозначениям
Excel (A1, B1 и т.д.). Для этого установите переключатель внизу окна
команды в нужную позицию. Если сортировку надо произвести не по
строкам таблицы, а по столбцам, то нажав кнопку Параметры внизу окна
команды откроете возможность указать это в окне параметров.
39
Фильтрация (отбор) данных в списке. Для отбора части записей
таблицы удобно использовать команду Данные
Фильтр. В этой команде
имеется несколько функций.
а) Функция Автофильтр используется тогда, когда надо отобрать
записи по значениям одного из столбцов таблицы..
Если в качестве критерия фильтра выбрать не значение, а режим
Условие, то откроется окно для ввода условия отбора. В этом окне можно
выбором задать любые одно или несколько условий на значения отбираемых
записей столбца. Чтобы отменить автофильтр для таблицы надо повторно
открыть команду Данные - Фильтр - Автофильтр и щелчком убрать галочку в
режиме.
Б) Функция команды фильтрации Данные - Фильтр - Расширенный
Фильтр. Она позволяет связать условия отбора по значениям нескольких
столбцов списка.
Сводная таблица - это инструмент Excel для переупорядочивания
данных уже существующих таблиц или списков. Так как построение сводной
таблицы многошаговый процесс, то помогает ее строить режим Мастер
сводных таблиц. Он открывается из меню Данные. Сводную таблицу можно
в любой момент изменить. Для этого надо на область таблицы поставить
курсор и повторно вызвать Мастер сводных таблиц. При этом откроется
сразу третье окно мастера и нажав кнопку Макет можно открыть окно
структуры и внести в него изменения. Имеется дополнительная панель
инструментов, которая позволяет формировать форму печатного отчета,
показывать детальные и сводные результаты и многое другое.
Консолидацией называется сведение данных из нескольких таблиц в
одну итоговую таблицу. Используется команда Данные Консолидация. В
Excel существует два способа консолидации. По расположению, в этом
случае таблицы-источники должны иметь одинаковые заголовки строк и
столбцов, т.е. должны быть расположены идентично. По категориям, в этом
случае в таблицах-источниках должны иметься одинаковые заголовки строк
и столбцов, но размещены они могут быть по-разному.
3 Визуализация данных. Диаграммы.
Диаграммы
- это графическое представление числовых данных и их
отношений посредством геометрических фигур. Графический способ
облегчает рассмотрение данных, делает их наглядными, выразительными,
обозримыми.
Данные электронной таблицы можно представить в виде диаграмм.
Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п.
Каждому элементу диаграммы соответствует число в таблице. Числа в
таблице и элементы диаграммы связаны между собой таким образом, что при
изменении чисел автоматически изменяется изображение элементов
диаграммы и наоборот.
Основные типы диаграмм любой электронной таблицы (табличного
процессора). Гистограмма (столбцовая диаграмма) представляет собой набор
40
вертикальных столбиков, высота которых определяется значениями данных.
Гистограмма применяется для отображения одного или нескольких рядов
данных и обычно используется для сопоставления числовых данных, а также
для представления изменения данных во времени или по категориям.
Линейчатые диаграммы – это гистограмма, столбики которой располагаются
не вертикально, а горизонтально. График представляется в виде сглаженной
или ломаной линии, соединяющей точки, соответствующие значениям
данных. Для некоторых видов графиков значения данных изображаются в
виде маркеров. Круговые диаграммы используются для представления
одного ряда данных, они демонстрируют соотношение между целым и его
частями и отвечают на вопрос: какую часть целого составляет тот или иной
его компонент. Каждое значение на круговой диаграмме представляется в
виде сектора круга. Угол сектора пропорционален доле представляемого
значения в общей сумме всех значений. Для каждого значения ведется
автоматический пересчет в проценты, которые затем могут быть отмечены на
диаграмме. Точечная диаграмма (XY– точечная) отображает взаимосвязь
между числовыми значениями в нескольких рядах в координатах XY. При
подготовке данных в первом ряду помещаются значения аргумента (ось X),
во втором (и последующих рядах) – значения функции (функций),
соответствующие значениям аргумента из первого ряда данных и
откладываемых по оси Y.
Смешанные (комбинированные) типы диаграмм позволяют построить
диаграмму с двумя осями и расположить на одной диаграмме данные
различных типов. При создании диаграммы смешанного типа возникают две
группы рядов. Одна группа – гистограммы, а другая может быть графиком
или XY – точечной диаграммой.
4 Регрессионная модель. Получение регрессионной модели
Зависимости между величинами могут быть представлены в виде
математической модели, в табличной и графической формах. Зависимость,
представленная в виде формулы, является математической моделью.
Регрессионная модель — это функция, описывающая зависимость
между количественными характеристиками сложных систем. Вид
регрессионной
функции
определяется
путем
подбора
по
экспериментальным данным.
Получение регрессионной модели происходит в два этапа:
1.
подбор вида функции;
2.
вычисление параметров функции.
График регрессионной модели называется трендом. Английское слово
trend можно перевести как общее направление, или тенденция. На графиках
присутствует еще одна величина, полученная в результате построения
трендов. Она обозначена как R
2
. В статистике эта величина называется
коэффициентом детерминированности. (В ЭТ величина достоверности
аппроксимации. Аппроксимация – сглаживание кривой.) Именно она