35. Табличный процессор. Функциональные возможности табличных процессоров. Технология работы в электронной таблице.

С помощью электронных таблиц (ЭТ) можно решать финансовые, экономические, математические и статистические задачи.

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

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

Другое равноправное название электронной таблицы — табличный процессор.

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

Термины «лист» и «таблица» являются практически синонимами.

Электронная таблица состоит из столбцов и строк. Столбцы чаще всего обозначаются заглавными латинскими буквами (A, B, C, .., AA, AB, AC,..), а строки — арабскими цифрами. Каждое пересечение строки и столбца образует «клетку» таблицы, которая может содержать текст, число или формулу.

Клетка обозначается буквой и цифрой по маркировке столбца и строки, на пересечении которых она находится. Набор координат, определяющих положение ячейки, называют ссылкой, например, С4. Обозначение ячеек похоже на обозначения, используемые в играх шахматы и морской бой. Иногда клетки называют ячейками, а ссылку порой называют адресом.

Заметим, что кроме рассмотренного стиля маркировки ячеек, который получил название А1, существует стиль, при котором вначале указывается порядковый номер строки, а затем порядковый номер столбца. Название этого стиля R1C1.

Формула = B2 + C4, расположенная в ячейке B6 (см. предыдущий рисунок), означает, что нужно взять содержимое ячейки B2, к нему прибавить содержимое ячейки C4 и результат поместить в ячейку B6. Всякое изменение содержимого ячеек B2 или C4 приведет к автоматическому изменению результата в ячейке B6.

Пользователь может задать любой ячейке собственное имя и затем использовать его при расчетах. Например, =B3+ИТОГ. В данном случае одной из ячеек дано имя «ИТОГ». Использование имен ячеек облегчает составление формул и делает их более наглядными и информативными. Например, следующая формула говорит сама за себя:

=ДОХОД-РАСХОД

Формулы позволяют обрабатывать содержимое сразу нескольких ячеек (диапазона ячеек). Например, чтобы просуммировать содержимое ячеек B7, C7, D7, E7, достаточно записать:

=СУММ(B7:E7)

Формулы могут ссылаться на ячейки текущего листа, ячейки листов той же книги или ячейки других книг.

Диапазоном ячеек (также диапазоном ссылок) называются две или более ячейки, расположенные на одном листе.

Синтаксисом формул называется правила записи (структура) элементов, входящих в формулу.

Формулы содержат знак равенства (=), вычисляемые элементы (операнды) и операторы.

Операнды — величины, с которыми оперирует (работает) ЭТ.

Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена и функции. Важно запомнить, что любая формула должна начинаться со знака равенства.

Константы — это величины, которые не изменяются в процессе вычислений, например, число 4 или текст «Прибыль». При записи констант перед ними знак равенства не ставится.

Ссылка — координата ячейки, ее адрес.

Заголовки — ключевые слова, размещенные сверху столбца и слева от строки, с помощью которых описываются данные внутри блока данных. Заголовки можно использовать при ссылке на необходимые данные. В следующей таблице заголовками являются слова: Иванов, Петров, Сидоров, Физика, Химия.

Заголовки в ряде случаев делают обработку информации очень наглядной. Например, чтобы напечатать оценку Петрова по химии достаточно набрать формулу:

=Петров Химия

Пробел между заголовками является оператором пересечения диапазонов, который предписывает формуле вернуть значение из ячейки, находящейся на пересечении строки «Петров» и столбца «Химия».

Создаются заголовки с помощью специальной процедуры, с которой можно ознакомиться по книгам, посвященным программе MS Excel.

Функция — это стандартная подпрограмма с уникальным именем, которая возвращает результат выполнения определенных действий над элементами, выступающими в роли аргументов. Например, функция

=МАКС(A1:A5;B3:B7)

отбирает максимальное число среди чисел двух указанных диапазонов ячеек.

Функции облегчают выполнение стандартных расчетов. Перед функцией ставится знак равенства, а аргументы заключаются в круглые скобки.

Аргументами в функции могут быть числовые значения, текст, ссылки, диапазоны ссылок, имена и вложенные функции.

Операторами обозначаются операции, которые выполняются над операндами. В Microsoft Excel имеется четыре вида операторов: арифметические, текстовые, сравнения, а также адресные операторы.

Арифметические операторы используются для выполнения математических операций над числами (сложение, вычитание, умножение, деление, возведение в степень, вычисление процентов). Результатом выполнения арифметической операции всегда является число.

Текстовой оператор — & (амперсант) позволяет объединить последовательности символов, находящихся в разных ячейках, в одну последовательность.

Операторы сравнения используются для сопоставления двух чисел. К ним относятся операторы: равно, больше, меньше, больше или равно, меньше или равно, неравно. Результатом выполнения операции сравнения являются логические значения ИСТИНА или ЛОЖЬ.

Например, следующая функция позволяет просуммировать числа, значения которых больше четырех:

=СУММЕСЛИ(А1:А7;»>4»)

Адресные операторы — двоеточие, запятая и пробел объединяют диапазоны ячеек. Например, формула =СУММ(А1:А7) говорит о том, что должно быть просуммировано содержимое ячеек А1, А2, …, А7. Формула =МИН(В1:В5,С5:С9) отберет минимальное число из ячеек двух указанных диапазонов. Наконец, пробел в формуле =Иванов Физика позволит вывести содержимое ячейки, находящейся на пересечении этих заголовков.

В настоящее время широкое распространение имеет электронная таблица MS Excel, которая представляет собой как бы скоросшиватель (книгу), состоящую из нескольких листов (таблиц).

Пользовательский интерфейс программы MS Excel показан на предыдущем рисунке.

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

ЭТ обладают элементами искусственного интеллекта.

Режим Автозаполнения позволяет быстро ввести дни недели (от понедельника до воскресенья), причем достаточно набрать на клавиатуре только первый элемент, а остальные будут добавлены автоматически по технологии Drag and Drop. Аналогично можно ввести месяцы (от января до декабря) либо другую заранее подготовленную последовательность элементов (например, фамилии).

Еще одна интересная возможность ЭТ состоит в использовании арифметических и геометрических прогрессий для быстрого заполнения большого числа ячеек.

Например, чтобы ввести порядковые номера студентов, достаточно указать только цифры 1 и 2. Все остальные номера будут введены автоматически. С помощью арифметической прогрессии легко ввести, например, только четные (или нечетные) номера.

За счет геометрической прогрессии просто сформировать такие (и подобные) последовательности чисел: 5; 20; 80; 320 или 1; 2; 4; 8; 16; 32.

ЭТ позволяют «предсказывать» результаты. Например, если известны антропологические параметры трех студентов, то можно попытаться «угадать» параметры четвертого студента, для которого известна только часть параметров. 

Предположим, что рост студента 170 см, а вес неизвестен. Расчет веса можно произвести с помощью функции:

=ПРЕДСКАЗ(170;{52;63;74};{152;163;174})

В результате будет получен ответ: 70 кг.

Расчеты в данном случае ведутся по методу наименьших квадратов, и исходная зависимость заменяется (аппроксимируется) уравнением прямой линии.

В качестве аргументов здесь использованы два массива чисел, которые заключены в фигурные скобки.

ЭТ дают возможность копировать или перемещать содержимое клетки в другие позиции (при этом используется принцип Drag and Drop).

Благодаря технологии OLE (Object Linking and Embedding) имеется возможность встраивать в клетки электронной таблицы объекты, созданные, например, в графическом редакторе. Таким образом, ведомость с расчетом заработной платы может содержать фотографии сотрудников.

Рассмотрим два важных понятия: относительная и абсолютная ссылки.

На следующем рисунке в ячейке С1 записана формула сложения содержимого ячеек А1 и В1. При этом использованы относительные ссылки. Копирование этой формулы в ячейку С2 привело к автоматическому изменению ссылок. Вместо формулы =А1+В1 в ячейке С2 появилась формула =А2+В2. В ячейке С4 записана формула сложения, в которой использованы абсолютные ссылки. Легко заметить, что добавлены знаки долларов. Копирование этой формулы в ячейку С5 не привело к изменению этой формулы.

Понятно, что использование относительных и абсолютных ссылок дает разные результаты. Это иллюстрирует следующий рисунок.

Таким образом, абсолютная ссылка — это использующаяся в формуле ссылка, которая не изменяется при копировании в другую ячейку.

Относительная ссылка — это ссылка, которая изменяется при копировании формулы.

41.База данных.Классификация

База данных (БД) — совокупность данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования данными, которые относятся к определенной предметной области.

Под данными понимается информация, представленная в виде, пригодном для обработки автоматическими средствами при возможном участии человека.

Под предметной областью понимается однородная часть реального мира, которая представляет интерес для конкретного исследования.

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

Главное достоинство электронных БД — возможность быстрого поиска и отбора информации, а также простая генерация (создание) отчета по заданной форме. Например, по номерам зачеток легко определить фамилии студентов или по фамилии писателя составить список его произведений.

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

Администратор базы данных — это лицо, отвечающее за выработку требований к базе данных во время ее проектирования, реализацию БД в процессе создания, эффективное использование и сопровождение БД в процессе эксплуатации. Администратор взаимодействует с конечными пользователями и программистами в процессе проектирования БД, контролирует ее работоспособность, отвечает за реорганизацию и своевременное обновление информации, удаление устаревших данных и за восстановление разрушенных данных, за обеспечение безопасности и целостности данных.

Под безопасностью данных понимают защиту данных от случайного или преднамеренного несанкционированного доступа к ним лиц, не имеющих на это права.

Под целостностью понимается возможность восстановления данных в случае возникновения сбоев в работе. Если БД содержит данные, используемые многими пользователями, то очень важно, чтобы данные и связи между ними не разрушались.

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

В современной технологии использования баз данных предполагается, что создание базы данных, ее поддержка и обеспечение доступа пользователей к ней осуществляется с помощью специального программного обеспечения — систем управления базами данных.

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

Рассмотрим существующие классификации баз данных.

По технологии обработки данных БД подразделяются на централизованные и распределенные.

Централизованная БД хранится в памяти одной ЭВМ.

Распределенная БД состоит из нескольких частей (возможно, пересекающихся или даже дублирующих друг друга), хранящихся на различных ЭВМ вычислительной сети.

По способу установления связей между данными различают реляционные, иерархические и сетевые БД.

Реляционная БД является простейшей и наиболее привычной формой представления данных в виде таблицы. В теории множеств таблице соответствует термин отношение (relation), который и дал название этой БД. Для нее имеется развитый математический аппарат — реляционное исчисление и реляционная алгебра, где определены такие математические операции, как объединение, вычитание, пересечение, соединение и др.

Существенный вклад в разработку БД этого типа сделал американский ученый Е. Кодд (E. Codd).

Достоинством реляционной БД является сравнительная простота инструментальных средств ее поддержки, недостатком — жесткость структуры данных (невозможность, например, задания строк таблицы произвольной длины) и зависимость скорости ее работы от размера базы данных. Для многих операций, определенных в такой БД, может оказаться необходимым просмотр всей БД.

Иерархическая и сетевая БД предполагают наличие связей между данными, имеющими какой-либо общий признак. В иерархической БД такие связи могут быть отражены в виде дерева-графа, где возможны только односторонние связи от старших вершин к младшим. Это ускоряет доступ к необходимой информации, но только если все возможные запросы отражены в структуре дерева. Никакие иные запросы на извлечение информации не будут удовлетворены.

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

Сведения о некоторых СУБД приведены в таблице. 

Рассмотрим основные понятия и компоненты реляционных БД (например, MS Access), которые в настоящее время имеют наибольшее коммерческое использование.

Реляционная БД ориентирована на организацию данных в виде двумерных таблиц-отношений. Каждая таблица представляет собой двумерный массив и обладает следующими свойствами:

 каждый элемент таблицы — это один элемент данных;

 все столбцы в таблице однородные, т. е. все элементы в столбце имеют одинаковые длину и тип (числовой, символьный и т. д.);

каждый столбец имеет уникальное имя;

одинаковые строки в таблице отсутствуют;

порядок следования строк и столбцов может быть произвольным.

Таблица — это набор данных по конкретной теме (предметной области), например, сведения о студентах высшего учебного заведения. Данные в таблице располагаются в столбцах (полях) и строках (записях).

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

Запись — это совокупность логически связанных полей.

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

Итак, для реляционных БД существует несколько равноправных терминов: столбец может называться полем или доменом, а строка — записью или кортежем.

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

Первичный ключ — это одно или несколько полей, однозначно идентифицирующих запись. Если первичный ключ состоит из одного поля, он называется простым, если из нескольких полей — составным ключом.

В приведенных выше таблицах простым первичным ключом является атрибут «Зачетка». В первой таблице можно было попытаться использовать в качестве простого первичного ключа атрибут «Фамилия». Однако не исключена возможность существования однофамильцев среди студентов. В этом случае атрибут «Фамилия» не сможет играть роль ключа, однозначно определяющего каждую запись. В качестве ключей часто используют инвентарные, табельные номера, электронные адреса, номера ICQ, паспортные номера и серии или просто порядковые номера записей.

Вторичный ключ — это такое поле, значение которого может повторяться в нескольких записях, т. е. он не является уникальным. Если по значению первичного ключа может быть найден один-единственный экземпляр записи, то по вторичному ключу — несколько записей.

Одной из основных характеристик БД является набор допустимых типов данных, которые могут содержаться в полях записей. За каждым полем записи строго закреплен конкретный тип данных, определяющий ограниченный набор применимых к нему операций. К типам данных относятся: символьный (текстовый), числовой, булевский (логический), денежный, дата, время, связанный по технологии OLE объект.

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

Для отбора данных из БД, удовлетворяющих определенным условиям, создается запрос. Запрос — это инструкция для отбора нужных сведений из данной БД в соответствии с определенными условиями, которые порой называют критериями.

Большинство СУБД разрешают использовать запросы следующих типов:

запрос-выборка, предназначенный для отбора данных, хранящихся в таблицах, причем этот вид запроса не изменяет эти данные;

запрос-изменение, предназначенный для перемещения данных или их модификации (добавление, удаление, обновление записей);

перекрестный запрос, предназначенный для отображения результатов статистических расчетов (суммы, количества записей, среднего значения), которые группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк;

подчиненный запрос, включающий в себя инструкцию, находящуюся внутри другого запроса на выборку или изменение.

Запрос можно формировать с использованием логических (булевых) операций И (AND), ИЛИ (OR), НЕ (NOT).

В этом случае из БД будут отобраны данные с помощью логической операции ИЛИ и на экране появятся сведения о студентах двух групп — БТ-61 и БТ-62.

Логическая операция И используется для решения следующей задачи. Пусть требуется выбрать из БД фамилии студентов группы БТ-63, сдавших математику с оценкой 5. На следующем рисунке показано, как формируется запрос с использованием логической операции И.

Результаты отбора приведены на следующем рисунке. Рассматриваемые примеры умышленно выбраны простыми, для того чтобы можно было проверить полученный результат даже без использования ЭВМ.

Таблица с результатами запроса может использоваться при дальнейшей обработке данных. В запросе на выборку могут использоваться не только таблицы БД, но и таблицы, полученные ранее в результате запросов.

В запросах можно производить несложные вычисления. Например, для подсчета средних баллов нужно при формировании запроса вначале просуммировать оценки по четырем предметам, а затем результат разделить на четыре.

В СУБД MS Access это делается так. В очередном свободном заголовке поля делается следующая запись:

Результат: =((Математика)+(Физика)+(Информатика)+(Графика))/4

Чтобы осуществить отбор записей из базы данных по фамилии, нужно в качестве условия отбора использовать следующую запись:

Like(Фамилия)

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

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

Нестандартная обработка данных может быть произведена с помощью макросов (последовательности нескольких команд, вызываемых нажатием одной клавиши) либо с помощью программ, написанных на языке Access Visual Basic. Такие программы часто называют модулями.

Для обработки информации в MS Access также используется специализированный язык SQL (Structured Query Language – структурированный язык запросов).

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

Справка.

Современные СУБД позволяют работать с огромными объемами информации. По некоторым оценкам, за последние 15 лет размеры баз данных выросли на два порядка, и процесс этот продолжается. Сегодня стандартными считаются базы данных объемом в 1—10 Гбайт, а некоторые из них перешагнули рубеж 100 Гбайт. По прогнозам специалистов, развитие крупных информационно-поисковых систем и хранилищ данных приведет к созданию БД, вмещающих свыше 10 Тбайт.

42

43

44

45

46

47

48

49

50