Раздел 6. Программное обеспечение персонального компьютера. Обработка данных средствами электронных таблиц Microsoft Excel.

Тема 10. Основные понятияэлектронных таблиц. Интерфейсэлектронных таблиц (ЭТ) Microsoft Excel. Содержание ЭТ. Формулы. Ссылки в формулах. Применение ЭТ для расчетов. Использование функций в формулах. Мастер функций. Связанные таблицы. Редактирование и форматирование данных и таблиц. Условное форматирование. Средства анализа данных. Построение и редактирование диаграмм. Сортировка и фильтрация. Промежуточные итоги. Сводные таблицы и диаграммы. Консолидация данных. Подбор параметра. Анализ данных. Поиск решения.

Методические указания

Электронные таблицы или табличный процессор Microsoft Excel – средство создания и ведения различных электронных документов. ЭТ MS Excel дают возможность обрабатывать и анализировать табличные данные, автоматизировать итоговые вычисления, предоставляют обширный набор встроенных функций, которые производят различные типы вычислений, позволяют решать задачи путем подбора значений параметров, поиска оптимальных значений параметров, позволяют строить диаграммы и графики по имеющимся данным.

При изучении этого раздела следует ознакомиться с рабочим окном MS Excel, его настройкой. Интерфейс MS Excel построен по аналогии с интерфейсом MS Word. Окно содержит следующие основные элементы управления: панель быстрого запуска, лента с расположенными на ней вкладками, строка формул, рабочее поле окна книги с тремя поименованными на ярлычках рабочими листами, строка состояния.

Панель быстрого доступа содержит кнопки часто выполняемых операций (Сохранить, Отменить, Вернуть). Состав панели быстрого доступа можно изменить путем ее настройки (справа от панели выбрать соответствующую списочную кнопку). Для быстрого выполнения часто используемых команд при работе с документом в панель быстрого доступа можно, например, добавить кнопки Создать, Открыть, Просмотр и печать. Каждая вкладка связана с видом выполняемого действия и содержит определенный набор функций. На вкладке имеется несколько групп с логически упорядоченными по общему назначению командами (инструментами, кнопками). Например, вкладка Главная состоит из групп: Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки, Редактирование. В правом нижнем углу некоторых групп имеется небольшая диагональная стрелка – это кнопка вызова диалогового окна, где отображены дополнительные возможности этой группы.

В строке формул отражается адрес текущей ячейки, ее содержимое. Через строку формул удобно осуществлять редактирование содержимого ячейки. Рабочий лист состоит из строк и столбцов. Столбцы (16384) имеют имена: А, B, …, AB, AC, …, XFD. Строки (1048576) нумеруются с 1. На пересечении столбцов и строк образуются ячейки. Обозначение ячейки выполняет функцию адреса или ссылки на ячейку (например, А1), который может быть использован при записи формул. Группу ячеек прямоугольной формы называют диапазон, для обозначения диапазона используют двоеточие (например, А1:D10). Диапазон выделяется графически, либо с клавиатуры. Для перемещения между листами используются ярлычки листов (Лист 1, Лист 2 и т.д.). Их можно переименовывать, копировать, перемещать, удалять.

В строке состояния указывается режим, в котором находится активная ячейка (Готово, Ввод, Правка), режим просмотра книги, масштаб. Для управления настройками вида окна книги следует ознакомиться с командами Файл – Параметры, а также вкладкой Вид – группы Режимы просмотра книги, Показать, Масштаб. Следует обратить внимание на режимы просмотра книги. По умолчанию книга отображается в режиме Обычный. Все файлы ЭТ сохраняются в формате .xlsx.

В ячейки листа можно вводить числа, формулы, текст, даты. Запись формул в ЭТ начинается со знака равенства. Формула – совокупность значений, ссылок на другие ячейки, функций Excel, математических или логических действий. Ссылки указывают на ячейку или их диапазон. С помощью ссылок можно использовать данные, находящиеся в разных частях листа, на других листах, в других книгах.

Следует различать относительные, абсолютные и смешанные ссылки. По умолчанию в новых формулах используется относительная ссылка (например, А1). При копировании формулы такая ссылка автоматически изменяется (например, адрес А1 в формуле изменяется на А2 при копировании вдоль строк, а при копировании вдоль столбцов адрес А1 изменяется на В1). Абсолютная ссылка ячейки в формуле, например,$A$1, всегда ссылается на ячейку, расположенную в определенном месте. При копировании формулы абсолютная ссылка не изменяется. Из относительной ссылки (относительного адреса) можно сделать абсолютную ссылку (абсолютный адрес) нажатием на функциональную клавишу F4 при вводе формулы или ее редактировании. Смешанная ссылка содержит абсолютный столбец и относительную строку или наоборот. При последовательных нажатиях клавиши F4 адрес ячейки А1 будет записываться как А1, $А$1, А$1, $А1, где последние два адреса являются смешанными.

Копирование и перемещение содержимого ячеек осуществляется графически или через буфер обмена. Вычисления в таблице автоматизированы за счет копирования первой введенной расчетной формулы в смежный диапазон клеток. Такое копирование осуществляется с помощью автозаполнения, путем перетаскивания за маркер заполнения (правый нижний угол рамки исходной клетки). При изменении ячеек с исходными данными происходит автоматический пересчет во всех ячейках таблицы, где имеется ссылка на эти данные.

Стандартные функции MS Excel используются в формулах. После имени каждой функции в скобках задаются аргументы, используемые функцией. Если функция не использует аргументов, то за ее именем следуют пустые скобки. Вызов Мастера функций осуществляется с помощью вкладки Формулы – Вставить функцию или кнопки Вставить функцию в строке формул. Мастер функций позволяет в диалоговом режиме просмотреть списки всех функций, распределенных по категориям, выбрать любую из них и задать ее аргументы. Во вкладке Главная – группа Редактирование расположен инструмент Сумма, позволяющий быстро произвести вычисление суммы, среднего, количества, максимального, минимального значений диапазонов клеток, а также вызвать Мастер функции (Другие функции).

В ЭТ можно работать одновременно с несколькими таблицами, расположенными на рабочих листах (страницах), которые объединяются в рабочие книги. Использование рабочих листов, расположенных в одной книге, то есть в одном файле, облегчает работу с несколькими таблицами, связанными по смыслу или общими данными, и помогает проводить последующий анализ данных. Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения клеток из нескольких разных таблиц на одном рабочем листе. Изменение содержимого клетки на одном листе (листе-источнике) рабочей книги приводит к изменению связанных с ней клеток в листах-приемниках. Этот принцип отличает связывание листов от простого копирования содержимого клеток из одного листа в другой. Через команду контекстного меню Специальная вставка – Вставить связь осуществляется копирование данных с листа на лист со связью.

Редактирование данных в таблице осуществляется через строку формул или непосредственно в клетке после двойного щелчка на ней. MS Excel позволяет форматировать как таблицу, так и табличные данные. Можно изменять ширину столбцов и высоту строк, для чего необходимо перетащить границу между заголовками столбцов и строк или воспользоваться вкладкой Главная – Ячейки – Формат. В таблицу можно добавлять строки, столбцы, удалять отдельные элементы таблицы (Главная – Ячейки). Таблицу следует обрамить линиями (Главная – Шрифт – и далее выбрать вид границы). В группе Выравнивание вкладки Главная расположены команды для выравнивания данных в таблице, объединения ячеек, переноса текста для расположения его в несколько строк. По умолчанию значение в ячейке отображается в формате Общий. Для изменения формата отображения данных используются команды вкладки Главная – Число или диалоговое окно Формат ячеек. С помощью этих команд можно выбрать нужный формат (числовой, денежный, финансовый, процентный, формат даты, задать число знаков после запятой и др.). Условное форматирование позволяет выделить ячейки с важной информацией, а также улучшить восприятие табличных данных (Главная – Стили).

В MS Excel можно создавать диаграммы профессионального вида. Диаграммы обеспечивают наглядность данных, облегчают их восприятие и интерпретацию, являются средством анализа и сравнения данных. При создании диаграммы на основе выделенных на рабочем листе ячеек MS Excel использует значения величин с рабочего листа и представляет их на диаграмме в виде элементов, которые могут быть изображены полосами, линиями, столбцами, секторами, точками и в иной форме. Группы данных, отражающих содержимое ячеек одной строки или столбца на рабочем листе, составляют ряд данных. Диаграммы создаются с помощью вкладки Вставка – Диаграммы, далее выбирается тип диаграммы. Диаграммы можно редактировать и форматировать (вкладка Работа с диаграммами – Конструктор (Макет, Формат)).

Для работы с таблицами, которые содержат большое количество данных, используют сортировку и фильтрацию. Сортировку можно выполнять по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или наоборот), по датам и времени (от старых к новым или наоборот) в нескольких столбцах. Для сортировки выбирается вкладка Главная - Редактирование – Сортировка и фильтр (или вкладка Данные - Сортировка и фильтр – Сортировка). Фильтры бывают двух типов: обычный (автофильтр) и расширенный. В отфильтрованных данных отображаются строки, соответствующие заданным условиям, а ненужные строки могут быть временно скрыты. Для применения обычного фильтра выбирается вкладка Главная - Редактирование – Сортировка и фильтр – Фильтр (или вкладка Данные - Сортировка и фильтр – Фильтр). В названиях полей таблицы появятся списочные кнопки, что позволит настроить параметры фильтра. С помощью расширенного фильтра можно задавать более сложные критерии фильтрации, помещать результаты отбора данных на другое место. Для использования расширенного фильтра необходимо создать на свободном месте таблицу с критериями отбора, содержащую названия нужных полей таблицы, а под ними указать условия отбора (критерии фильтрации). Далее скопировать и вставить на свободное место «шапку» исходной таблицы. Сделав исходную таблицу активной, выбрать вкладку Данные - Сортировка и фильтр – Дополнительно. В соответствующих полях задать исходный диапазон для фильтрации, диапазон условий, диапазон, куда поместить результат, а также включить режим, обеспечивающий размещение результата в новой таблице.

При помощи вкладки Данные – группа Структура – Промежуточный итог можно автоматически подсчитать промежуточные и общие итоги в списке на основе данных таблицы. Следует предварительно выполнить сортировку по столбцу, который формирует группу. При подведении итогов выбирается столбец для подсчета итогов, функция для вычисления, выбираются столбцы, по которым подводятся итоги.

Для удобного просмотра больших таблиц, получения промежуточных и общих итогов, анализа объединенных в различных разрезах данных используются сводные таблицы. Для создания сводной таблицы используется вкладка Вставка -группа Таблицы - Сводная таблица - Сводная таблица. После задания источника данных для отчета сводной таблицы и указания, что таблица будет, например, помещена на новый лист, следует выбрать путем перемещения поля для названия столбцов, строк, значений. В сводных таблицах можно изменять представление данных, например, переместить поле из области названия строк в область названия столбцов. Для поля значений можно использовать различные итоговые функции. Отчеты сводных диаграмм обеспечивают графическое представление данных.

Консолидация данных – процедура получения итогов для данных, расположенных в несмежных диапазонах. Диапазоны ячеек могут располагаться как на одном рабочем листе, так и на разных, а также в различных книгах. Консолидация данных позволяет объединить несколько однотипных таблиц в одну. Для проведения консолидации используется вкладка Данные – группа Работа с данными – Консолидация. Далее выбирается функция, используемая для обработки данных. В поле Ссылка задается исходная область консолидируемых данных, затем кнопка Добавить, этот шаг повторяется для всех консолидируемых областей. Для автоматического обновления итоговой таблицы при изменении источников данных включить переключатель Создавать связи с исходными данными. При установлении связи можно наблюдать символы структуры, позволяющие видеть данные с большими или меньшими подробностями.

Инструмент Подбор параметра используется, если результат, который необходимо получить при вычислении формулы, известен, но не ясно, какое исходное значение данных требуется для его получения (вкладка Данные – группа Работа с данными – Анализ «что если» - Подбор параметра). В поле Установить в ячейке необходимо ввести ссылку на ячейку, содержащую формулу, параметры которой подбираются, а в поле Значение – само значение. В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать.

Программа Поиск решения является инструментом оптимизации, с ее помощью можно найти оптимальное или заданное значение некоторой ячейки путем подбора значений нескольких ячеек, удовлетворив нескольким граничным условиям. Средство Поиск решения является надстройкой MS Excel, которую нужно загрузить через команду Файл – Параметры – Надстройки – Управление – Надстройки Excel – Перейти – Поиск решения. После загрузки на вкладке Данные в группе Анализ становится доступна команда Поиск решения. В окне Поиск решения в поле Целевая ячейка задается ячейка, для которой находится максимальное, минимальное или заданное значение. В поле Изменяемые ячейки задаются адреса изменяемых ячеек, связанных с целевой. В поле Ограничения вводятся накладываемые ограничения. Кнопка Выполнить инициирует процесс решения, после чего появится сообщение о том, что решение найдено или нет.

Литература: [1, гл.12]; [2, гл. 12-16]; [3, гл.17]; [5].

Вопросы для самопроверки

1. Как называется документ в MS Excel? Структура окна.

2. Какие типы данных используют в электронных таблицах? Как изменить формат данных?

3. Как обозначается адрес ячейки, диапазон ячеек?

4. Каково назначение маркера заполнения?

5. Какие типы ссылок используются при записи формул в MS Excel?

6. Как создаются и редактируются диаграммы?

7. Что такое автозаполнение в MS Excel?

8. Что такое форматирование таблицы и табличных данных?

9. Функции и их использование при работе с таблицами.

10. Сортировка данных в таблицах.

11. Способы фильтрации данных при работе с таблицами.

12. Сводные таблицы, назначение.

13. Когда применяется консолидация данных?

14. Анализ данных в MS Excel.

Раздел 7. Программное обеспечение персонального компьютера. Базы данных (БД). Системы управления базами данных (СУБД). Проектирование базы данных с помощью СУБД Microsoft Access.

Тема 11. Основные понятия баз данных. Система управления базами данных MS Access. Свойства полей базы данных. Типы данных. Безопасность базы данных. Основные объекты базы данных. Средства создания основных объектов базы данных. Таблицы. Создание, редактирование структуры таблиц. Связи и обеспечение целостности БД. Схема данных. Создание форм для наполнения таблиц данными. Запросы. Создание запросов. Виды запросов. Запросы на выборку. Итоговые запросы. Запросы на модификацию БД. Отчеты. Создание и редактирование отчетов. Кнопочная форма.

Методические указания

База данных – организационная структура, предназначенная для хранения информации. Система управления базами данных представляет собой комплекс программных средств, предназначенных для создания структуры новой базы, наполнения её содержимым, редактирования содержимого, отбора отображаемых данных в соответствии с заданным критерием, упорядочивания данных, оформление данных с последующей выдачей на устройство вывода или передачи по каналам связи. К основным требованиям, предъявляемым к СУБД, относят непротиворечивость данных, актуальность хранимых данных, многоаспектное использование, возможность модификации системы, надежность, скорость доступа.

При проектировании БД сначала разрабатывается концептуальная модель БД, в которой на естественном языке описываются объекты предметной области и их взаимосвязи, т.е. выделяется и описывается информация, представляемая в БД. Эта модель является основой для построения логической модели БД, которая отражает информационное содержание и является основой для всех пользователей информационной системы. Физическая (внутренняя) модель БД содержит информацию обо всех объектах БД.

Каждая конкретная СУБД работает с определенной моделью данных, определяющей способ взаимосвязи данных – в виде иерархического дерева, сложной сетевой структуры или связанных таблиц. Большинство СУБД использует табличную модель данных, называемую реляционной. MS Access является реляционной базой данных, представляющей собой множество взаимосвязанных двумерных таблиц – отношений, содержащих сведения об одной сущности предметной области. Для создания таблиц, соответствующих реляционной модели данных, используется процесс нормализации – удаления из таблиц повторяющихся данных путем их переноса в новые таблицы, записи которых не содержат повторяющихся значений.

Основной объект БД – таблицы. Структуру таблиц БД образуют поля и записи. Структура таблицы определяет методы занесения данных и хранение их в базе. БД должна иметь хотя бы одну таблицу, в случае нескольких – между ними устанавливается межтабличная связь по ключевому полю. Следует различать первичный(уникальный) ключ,который однозначно идентифицирует запись и внешнийключ-поле, не являющееся первичным в данной таблице, но дублирующее поле, которое служит первичным ключом в другой таблице и используется для связи этих таблиц. Связываемые поля должны иметь одинаковый тип данных. При создании межтабличных связей способом корректного ввода данных во вторичной таблице является включение режима Обеспечение целостности данных. Это означает, что данные, содержащиеся в поле первичного ключа, используемом в межтабличной связи, должны совпадать с данными в таком же поле вторичной таблицы. Наиболее используемыми типами отношений при установке межтабличных связей являются: один-ко-многим, один-к-одному и другие.

К основным объектам БД относят таблицы, запросы, формы, отчеты, макросы и модули. Таблицы – это основные объекты БД, хранят все данные базы, структуру базы (поля, их типы, свойства). Запросы – специальные средства для отбора и анализа данных, служат для извлечения данных из таблиц, предоставления их в удобном для пользователя виде. С помощью запросов выполняются операции по отбору данных, сортировке, фильтрации. Запросы позволяют преобразовать данные по заданному алгоритму, создавать новые таблицы, выполнять автоматическое наполнение таблиц данными, импортируемыми из других источников, выполнять простейшие вычисления в таблицах и т.д. Формы – это специальные средства для ввода данных без доступа к самим таблицам и их отображение с использованием специальных средств оформления. Основной смысл форм – предоставить пользователю возможность заполнять только те поля, которые ему заполнять положено. В форме можно размещать специальные элементы управления – кнопки, раскрывающиеся списки, переключатели и т.д. Отчеты предназначены для вывода данных на печать. В них предусмотрены специальные меры для группирования, сортировки выводимых данных, для вывода специальных элементов оформления, характерных для печатных документов (номера страниц, колонтитулы и т. д.). В отчетах можно производить вычисления. Макросы и модули предназначены для автоматизации повторяющихся операций при работе с БД и для создания новых функций путем программирования. В БД макросы состоят из последовательности внутренних команд СУБД и являются одним из средств автоматизации работы с базой. Модули создаются средствами внешнего языка программирования (Visual Basic for Application). С помощью модулей разработчик базы может заложить в нее нестандартные функциональные возможности, удовлетворяющие специфические требования заказчика, повысить быстродействие системы управления, уровень защищенности БД.

БД MS Access предоставляет различные средства создания основных объектов БД. Ручные средства – разработка объектов в режиме Конструктор (например, разработка таблиц, запросов). Автоматизированные средства – разработка объектов с помощью программ – мастеров (например, разработка форм, отчетов). Автоматические средства – средства ускоренной разработки простейших объектов (например, разработка форм, отчетов). Для создания основных объектов БД следует выбрать вкладку Создание, затем соответствующую группу (Таблицы, Запросы, Формы, Отчеты). Затем следует выбрать режим создания (Конструктор таблиц, Конструктор запросов, Мастер форм, Мастер отчетов).

При проектировании БД сначала создается структура таблиц БД и устанавливаются межтабличные связи (вкладка Работа с базами данных – Отношения - Схема данных). Затем создаются формы для наполнения таблиц записями. Для таблиц, связанных отношением один-ко многим, удобно создавать формы с подчиненной формой, дающие возможность работать с данными из нескольких таблиц. Следующим этапом является проектирование основных типов запросов (на выборку, на выборку с параметром, итоговые запросы, запросы с вычисляемыми полями, перекрестные запросы, запросы на модификацию БД). Запросы на модификацию БД позволяют создавать новые таблицы на базе существующих, обновлять, удалять, добавлять записи. На базе созданных запросов и таблиц можно создавать отчеты, позволяющие оформлять соответствующим образом данные, хранимые в запросах и таблицах, и предоставлять их конечному пользователю. Отчет позволит представить информацию на печать в желаемом формате. В отчете удобно группировать и сортировать данные. Особый вид формы - кнопочная форма, обеспечивает быстрый доступ к созданным ранее формам и отчетам для управления данными. Диспетчер кнопочных форм позволяет создать форму, состоящую из нескольких страниц, на которых располагаются кнопки, сгруппированные по смыслу. Редактирование всех созданных объектов осуществляется в режиме конструктора.

Литература: [1, гл.13]; [6]; [9].

Вопросы для самопроверки

1. Что такое «предметная область»?

2. Что такое база данных, система управления базами данных?

3. Что такое «модель данных»?

4. Какие основные объекты базы данных Вам известны? Их назначение.

5. Что означает понятие «структура таблиц» базы данных?

6. Зачем нужно ключевое поле?

7. Что такое «первичный ключ» и «внешний ключ»?

8. Какие типы межтабличных связей Вы знаете?

9. Способы создания основных объектов БД.

10. Запросы и их назначение. Виды запросов.