12.5. электронные таблицы

12.5. электронные таблицы

Методические рекомендации по изучению темы

Изучаемые вопросы:

ª Области применения электронных таблиц (ЭТ).

ª Среда табличного процессора (ТП).

ª Режимы работы ТП.

ª Система команд.

ª Данные в ячейках ЭТ.

ª Методы адресации в ЭТ.

Области применения электронных таблиц. Появление ЭТ исторически совпадает с началом распространения персональных компьютеров. Первая программа для работы с ЭТ — табличный процессор, была создана в 1979 г., предназначалась для компьютеров типа Apple II и называлась VisiCalc. В 1982 г. появляется знаменитый табличный процессор Lotus 1-2-3, предназначенный для IBM PC. Lotus объединял в себе вычислительные возможности ЭТ, деловую графику и функции реляционной СУБД. Популярность табличных процессоров росла очень быстро. Появлялись новые программные продукты этого класса: Multiplan, Quattro Pro, SuperCalc и др. Одним из самых популярных табличных процессоров сегодня является MS Excel, входящий в состав пакета Microsoft Office.

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

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

2. Математическое моделирование. Использование математических формул в ЭТ позволяет представить взаимосвязь между различными параметрами некоторой реальной системы. Основное свойство ЭТ — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента: подбор параметров, прогноз поведения моделируемой системы, анализ зависимостей, планирование. Дополнительные удобства для моделирования дает возможность графического представления данных.

3. Использование электронной таблицы в качестве базы данных. Конечно, по сравнению с СУБД электронные таблицы имеют меньшие возможности в этой области. Однако некоторые операции манипулирования данными, свойственные реляционным СУБД, в них реализованы. Это поиск информации по заданным условиям и сортировка информации.

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

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

На пересечении строк и столбцов образуются ячейки (клетки), каждая из которых имеет свое обозначение (имя, адрес), состоящее из имени столбца и номера строки: А1, С5, АВ356 и т.п. На экране дисплея видна не вся электронная таблица (документ), а только ее часть (рис. 12.4).

Рис. 12.4. Структура электронной таблицы

Документ в полном объеме хранится в оперативной памяти, а экран можно считать «окном», через которое пользователь имеет возможность просматривать его. Кроме основного раздела памяти, где хранится электронная таблица, используются еще дополнительные разделы:

• буфер для хранения копируемых фрагментов таблицы;

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

У разных табличных процессоров может различаться расположение на экране рабочего поля и панели диалога. На рис. 12.5 отображена среда табличного процессора SuperCalc, работающего в операционной системе MS-DOS.

Рис. 12.5. Среда табличного процессора SuperCalc

На панели диалога меню команд постоянно не отражается и вызывается нажатием клавиши «» — слеш. Для табличных процессоров, работающих в ОС Windows (в том числе Excel), свойственно наличие панели инструментов, линеек прокрутки. На рис. 12.6 показана характерная структура среды для таких ТП.

Рис. 12.6. Среда табличного процессора Excel

Строка ввода предназначена для отражения вводимых в текущую ячейку данных. В этой строке можно просмотреть и отредактировать хранимую в ячейке формулу; в самой ячейке пользователь видит результат вычисления по формуле.

Главное меню содержит основные команды управления электронной таблицей; представляет собой иерархическую систему команд. Команды, вызывающие открытие подменю, можно назвать режимами. Исполняемые команды инициируют определенные действия над электронной таблицей.

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

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

Основные режимы работы. Режим готовности. В этом режиме происходит выбор текущей ячейки или выделение блока ячеек.

Режим ввода данных. Происходит посимвольный ввод данных с клавиатуры в текущую ячейку.

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

Командный режим. Режим выбора и исполнения команд из иерархической системы меню. После выполнения команды происходит возврат к режиму готовности.

Кроме перечисленных основных режимов работы ТП, можно говорить о режимах отображения таблицы и режимах управления вычислениями.

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

Режим управления вычислениями. Табличный процессор производит вычисления по формулам, сканируя таблицу в определенном порядке. Такое сканирование всегда начинается с клетки А1. Порядок вычислений может быть установлен по строкам или по столбцам. Некоторые ТП позволяют устанавливать этот порядок по желанию пользователя.

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

Система команд. Команды ТП организованы в иерархическую систему, верхним уровнем которой является главное меню. Кроме того, выполнение команд может инициироваться через панель инструментов, контекстное меню, «горячие клавиши».

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

Команды форматирования позволяют изменять внешний вид таблицы, ее оформление. К элементам формата относятся:

• направления выравнивания данных относительно границ ячейки;

• высота строки и ширина столбца;

• тип, начертание и размер шрифта;

• формат представления чисел (обычный, экспоненциальный, разрядность);

• вид разлиновки таблицы;

• цвет фона и пр.

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

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

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

Чтобы организовать поиск и извлечение данных, необходимо задать:

• входной блок, т.е. диапазон ячеек, в котором хранятся данные (записи и поля); важное требование: все строки в этом блоке должны быть однородны;

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

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

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

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

• команды описания диаграмм (задают данные, которые будут выведены в графическом виде, задают тип диаграмм и т.д.);

• команды вывода диаграмм.

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

Здесь мы снова встречаемся с такими свойствами данных, как ,jHn и структура, знакомыми из темы о базах данных. Кроме того, тоявляются новые темы в разговоре о данных. Это понятия константы и переменной, понятие арифметического и логического выражения, понятие адресации.

Содержимым ячейки электронной таблицы может быть фор-лула или текст. Частным случаем формулы является числовая константа или переменная, более общим — арифметическое или тогическое выражение.

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

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

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

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

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

Запись числовой константы с фиксированной точкой предполагает, что число содержит целую и дробную часть, разделенные десятичной точкой. Например, числовая константа —3,1415 записывается как —3.1415 (в ТП Excel может употребляться запятая). При записи числовой константы в экспоненциальной форме сначала записывается мантисса, затем — латинская буква Е (прописная или строчная), после нее — порядок. Мантисса может быть записана как целая константа или константа с фиксированной точкой, а порядок — только как целая двузначная константа. Числовая константа в экспоненциальной форме трактуется, как мантисса, умноженная на 10 в степени, равной порядку. Например, числа 0,0001 и 1 000 000 могут быть записаны следующим образом: IE —4 или 0.le —3 и 1е6 или 1Е+6.

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

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

Выражения составляются из констант, переменных, знаков операций, функций, круглых скобок. Примеры выражений: 2.5*(G5+G2) SQRT(B4Ù2-4*B3*B5) SUM(C10:C20) Здесь символ «*» — знак умножения; «Ù» — возведение в степень. В ТП SuperCalc SQRT — функция вычисления квадратного корня; SUM — функция суммирования. В русифицированных версиях Excel функция квадратного корня — КОРЕНЬ, функция суммирования — СУММ. Порядок вычисления выражений происходит в соответствии с приоритетами выполнения арифметических операций, с расстановкой скобок. Приобретение учениками навыков записи формул требует практических упражнений. Задачи такого рода имеются в пособии [3].

Логические выражения (логические формулы) строятся с помощью операций отношения (<, >, =, <=, >=, <>) и логических операций (логическое «И», логическое «ИЛИ», логическое отрицание «НЕ»). Результатом вычисления логического выражения являются логические величины «истина» или «ложь». Об особенностях использования логических выражений в электронных таблицах рассказано в подразделе 8.4 настоящего пособия.

Адресация. Следует обратить внимание учеников на определенную родственность структуры электронной таблицы и оперативной памяти ЭВМ. В обоих случаях используется принцип адресации для хранения и поиска информации. Разница состоит в том, что в ОЗУ наименьшей адресуемой единицей является байт, а в таблице — клетка (ячейка).

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

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

Обычно режим относительной адресации работает в таблице по умолчанию. Абсолютная адресация применяется к отдельным ссылкам на ячейки в формулах. Для этого используется символ «замораживания» адреса — «$». С помощью этого символа можно «заморозить» как весь адрес, например $В$2, так и отдельные его части, например $В2, В$2.

Рекомендации по организации практической работы

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

• осуществлять перемещение табличного курсора; устанавливать курсор в нужную ячейку;

• вводить данные: числа, тексты, формулы;

• редактировать данные в ячейках;

• копировать информацию в ячейках;

• вставлять и удалять строки и столбцы.

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

• все символы в формуле записываются в одну строчку;

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

• используются круглые скобки для влияния на последовательность выполнения операций;

• учитываются приоритеты операций, расположенные в таком порядке: Л — возведение в степень; *, / — умножение и деление; +, — — сложение и вычитание;

• приоритет стандартных функций выше арифметических операций; аргумент записывается в круглых скобках после имени функции;

• последовательно записанные операции одинакового старшинства выполняются в порядке записи, т. е. слева направо (возведение в степень — справа налево).

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

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

Например, дано математическое выражение:

Нужно записать формулу для электронной таблицы. Во-первых, переменным величинам в этой формуле нужно поставить в соответствие адреса ячеек. Например так: х — А1, у — В1. Теперь записать формулу:

Для обоснования правильности формулы следует пронумеровать операции в порядке их выполнения так, как это сделано в примере.

Пример обратной задачи. Дана формула для электронной таблицы:

Нужно записать соответствующее математическое выражение. От адресов ячеек перейдем к математическим переменным: С5 — х, В5 — у, А5 — z, B2 — k. Следующим шагом нужно, как это делали раньше, указать последовательность вычислений:

Теперь становится ясно, что эта формула соответствует следующему математическому выражению:

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

При переносе этой формулы в любую другую ячейку любым способом (копированием, вследствие вставки или удаления фрагментов) сохранится сформулированный выше смысл формулы. Вследствие этого изменятся ссылки на ячейки. Например, при копировании формулы из ячейки С1 в ячейку С2 формула примет вид: А2 + В2. При копировании в ячейку F6 примет вид: F4 + F5.

Для закрепления понимания принципа относительной адресации следует выполнить несколько заданий. Условия предлагаемых

задач должны быть следующего типа: дан фрагмент электронной таблицы (например такой, как приведен выше). Какие формулы занесутся в ячейки блока D1:F1, если в них скопировать формулу из ячейки С2? Результат выполнения этого задания в режиме отображения формул и в режиме отображения значений будет следующим (табл. 12.5):

 Таблица 12.5

Здесь заливкой отмечены ячейки, в которые произведено копирование.

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

При знакомстве с приемом «замораживания» адресов ячеек в формулах полезно выполнить аналогичные упражнения. Например, в той же таблице в ячейке С1 записана формула: А$1+$В$1. Какой вид примет формула, если ее скопировать в блок Dl :F1 и в блок С2: F2? Результаты решения этой задачи будут следующими (табл. 12.6):

Таблица 12.6

Основные типы расчетных задач, которые учащиеся должны научиться решать на электронных таблицах:

1) получение несложных расчетных ведомостей;

2) статистическая обработка числовых таблиц;

3) построение диаграмм по табличным данным;

4) сортировка таблицы по значениям параметра (столбца);

5) табулирование функций.

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

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

Теперь можно переходить к построению таблицы. Следует сразу же сформулировать ученикам некоторые правила оформления таблицы. Во-первых, у таблицы обязательно должен быть заголовок. Строки и столбцы таблицы должны быть поименованы. Постепенно учитель должен познакомить учеников с основными средствами форматирования таблицы для получения оформленного документа. Начальная часть таблицы будет выглядеть так (предполагается, что цена 1кВт-ч равна 20 коп., а плата подсчитывается в руб., табл. 12.7):

Таблица 12.7

Во время работы с табличным процессором обязательно нужно использовать прием копирования формул. Формулы в ячейках С4, D4 являются исходными. Все формулы, расположенные ниже, получены путем копирования. Из этого примера ученикам станет ясен практический смысл принципа относительной адресации: он позволяет быстро создавать большие таблицы без переписывания формул.

Разбор этой задачи приводит к идее использования абсолютного (замороженного) адреса. Удобно цену 1 кВт-ч хранить в отдельной ячейке (например, в В16), а в ячейке D4 записать формулу С4*$В$16. При копировании формулы абсолютный адрес меняться не будет. Теперь, если произойдет изменение цены 1 кВт-ч, то будет достаточно внести изменения лишь в одну ячейку В16.

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

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

Для построения диаграммы пользователь должен указать ее тип и сообщить табличному процессору, из каких блоков таблицы нужно выбирать всю необходимую информацию. Достаточно подробно основные типы диаграмм описаны в [3], даны рекомендации по их применению.

Большие возможности придает использование в электронной таблице условной и логических функций. Таблица без использования условной функции реализует в себе линейный вычислительный алгоритм. Использование условной функции вносит в таблицу структуру ветвления. Потребность в ветвлении появляется при усложнении условия задачи. Например, если бы правило оплаты за электроэнергию звучало так: за первые 100 кВт-ч нужно платить по 20 коп.; за каждый 1 кВт-ч, израсходованный выше этого, нужно платить по 30 коп. Тогда расчетная формула в ячейке D4 выглядела бы так:

IF(C4<=100, С4*0.2, 20+(С4 100)*0.3)

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

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

вычисленной с шагом 0,2 начиная от значения х = 0, строится следующим образом (табл. 12.8):

Таблица 12.8

Если нужно изменить начальное значение X в таблице, то достаточно исправить ячейку A3, а для изменения шага нужно изменить содержимое ячейки В1. Формулы записываются по правилам, принятым в используемом табличном процессоре.

Применение электронных таблиц для решения задач математического моделирования описывается в подразделе 1.0.4.