1.3.2. Данные, хранимые в ячейках электронной таблицы.

Типы входных данных

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

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

Пример символьных данных:

Ведомость по начислению премии

Группа № 115

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

Пример числовых данных:

–20

256.28

.251

Формулы – это выражения, состоящие из числовых величин, адресов ячеек и функций, соединенных знаками арифметических операций.

Пример формулы:

А5/(Н8+12).

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

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

Пример функции:

SUM(В2:В6,В9) – функция вычисления суммы значений множества чисел, находящихся в ячейках B2,B3,B4,B5,В6 и В9.

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

– ДД-ММ-ГГ (04-10-98);

– ДД.ММ.ГГ (04.10.98);

– ДД-МММ (04-Янв) и т.д.

Форматирование числовых данных в ячейках

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

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

• Формат с фиксированным количеством десятичных знаков обеспечивает представление чисел в ячейках с заданной точностью, определяемой установленным пользователем количеством десятичных знаков после запятой (десятичной точки). Например, если установлен режим форматирования, включающий два десятичных знака, то вводимое в ячейку число 12345 будет записано как 12345.00, а число 0.12345 – как .12.

• Процентный формат обеспечивает представление введенных данных в форме процентов со знаком % (в соответствии с установленным количеством десятичных знаков). Например, если установлена точность в один десятичный знак, то при вводе 0.123 на экране появится 12.3%, а при вводе 123 – 12300.0%.

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

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

– мантиссы, имеющей один десятичный разряд слева от десятичной точки, и некоторого (определяемого точностью, заданной пользо­вателем) количества десятичных знаков справа от нее;

– порядка числа.

Введенное число 12345 будет записано в ячейке как 1.2345Е +04 (если установленная точность составляет 4 разряда) и как 1.23Е +04 (при точности в 2 разряда). Число 0.0000012 в научном формате будет иметь вид 1.2Е-06.

Форматирование символьных данных в ячейках

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

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

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

• выравнивание по центру ячейки располагает вводимые данные по центру ячейки.

Форматирование данных – выбор формы представления числовых или символьных данных в ячейке, т.е. задание формата ячейки.

Изменение ширины колонки

Отображение числовых данных зависит не только от выбранного формата, но также и от ширины колонки (ячейки), в которой эти данные располагаются. Ширина колонки при текстовом режиме экрана устанавливается в знаках, а при графическом режиме экрана – в независимых единицах. Количество знаков в ячейке зависит от ее ширины, кегля, гарнитуры, а также от конкретного текста. Так, например, не составляет проблемы расположить число 12345 в формате с запятой без дробной части в ячейке шириной в 9 знаков. Однако вы не сможете его расположить там в денежном формате с двумя десятичными знаками, поскольку число $12,345.00 занимает 10 разрядов, превышая тем самым ширину ячейки. В данном случае необходимо изменить используемый формат представления числа либо увеличить ширину колонки.

Отображение данных в ячейках таблицы

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

Числа располагаются в пределах ячейки. Слишком длинное десятичное число округляется до требуемого количества знаков. Если этого недостаточно, число представляется в научном формате.

Если же ширина ячейки и для этого мала, ячейка заполняется знаками * (звездочка) или # (решетка), сигнализирующими о том, что ширина ячейки недостаточна для отображения данных.

Формулы

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

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

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

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

+ сложение,

– вычитание,

* умножение,

/ деление,

^ возведение в степень.

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

Арифметические формулы могут также содержать операторы сравнения: равно (=), не равно (< >), больше (>), меньше (<), не более (<=), не менее (>=). Результатом вычисления арифметической формулы является число.

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

#NOT# – логическое отрицание “НЕ”;

#AND# – логическое “И”;

#OR# – логическое “ИЛИ”.

Логические формулы определяют истинно или ложно выражение. Истинным выражениям присваивается численная величина 1, а ложным – 0. Таким образом, вычисление логической формулы заканчивается получением оценки “Истинно” (1) или “Ложно” (0).

Рассмотрим несколько примеров вычисления арифметических и логических формул по следующим данным:

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

Функции

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

Функции вводят в таблицу в составе формул либо отдельно. В электрон­ных таблицах могут быть представлены следующие виды функций:

• математические;

• статистические;

• текстовые;

• логические;

• финансовые;

• функции даты и времени и др.

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

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

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

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

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

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

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

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

SUM(B5:E5)

SUM(A3:E3,230)

AVERAGE(Список) – статистическая функция определения среднего арифметического значения всех перечисленных в Списке величин.

AVERAGE(5,20,10,5)

AVERAGE(B10:B13,B17)

МАХ(Список) – статистическая функция, результатом которой является максимальное значение в указанном Списке.

МАХ(ВЗ:В8,АЗ:А6)

IF(Условие, Истинно, Ложно) – логическая функция, проверяющая на истинность заданное логическое условие. Если условие выполняется, то результатом функции является значение аргумента “Истинно”. Если условие не выполняется, то результатом функции становится значение аргумента “Ложно”.

IF(B4<100, 100,200) – если ячейка В4 содержит число меньше 100, то функция имеет значение 100, если же это условие не выполняется (т.е. содержимое ячейки В4 больше или равно 100), функции присваива­ется значение 200.

Автоматическое изменение относительных ссылок при копировании и перемещении формул.

Буфер промежуточного хранения

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

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

Относительная и абсолютная адресация

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

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

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.

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

$В$5; $D$12 – полные абсолютные ссылки.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца.

В$5, D$12 – частичная абсолютная ссылка, не меняется номер строки;

$В5, $D12 – частичная абсолютная ссылка, не меняется наименование столбца.

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

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

Правило относительной ориентации ячейки

Формула, где в качестве операндов используются относительные ссылки ячеек, воспринимается системой как шаблон, а ссылки ячеек в таком шаблоне – как средство указания на местоположение ячеек с операндами относительно ячейки с формулой.

Рассмотрим правило относительной ориентации клетки на примере (рис.1.2).

Клетка со ссылкой С2 содержит формулу-шаблон сложения двух чисел, находящихся в ячейках А1 и В4. Эти ссылки являются относительными и отражают ситуацию взаимного расположения исходных данных в ячейках А1 и В4 и результата вычисления по формуле в ячейке С2. По правилу относитель­ной ориентации клеток ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно клетки С2: ссылка А1 указывает на клетку, которая смещена относительно клетки С2 на одну клетку вверх и на две клетки влево; ссылка В4 указывает на клетку, которая смещена относительно клетки С2 на две клетки вниз и одну клетку влево.

Копирование формул

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

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

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

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

На рис. 1.3 мы видим результат копирования формулы, содержащейся в ячейке A3, при использовании относительных, полностью абсолютных и частично абсолютных ссылок. При копировании формулы с использованием относительных ссылок происходит их автоматическая подстройка (рис.1.3а). Результаты копирования с использованием абсолютных ссылок со знаком $ приведены на рис. 1.3б. Как нетрудно заметить, применение абсолютных ссылок запрещает автоматическую настройку адресов и копируемая формула сохраняет свой первоначальный вид. В приведенном на рис. 1.3в примере для запрещения автоматической подстройки адресов используются смешанные ссылки.

Автоматическое изменение ссылок происходит не только при копирова­нии, но и при перемещении формул.

Перемещение формул

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

Пример. На рис. 1.4а мы видим перемещение содержимого отдельной ячейки A3 в ячейку СЗ. В этом случае содержимое исходной ячейки, не изменяясь, перемещается в ячейку назначения, а исходная ячейка остается пустой.

Рис. 1.4б иллюстрирует случай перемещения содержимого трех ячеек A1, A2 и A3. При этом ячейки взаимосвязаны – содержимое третьей ячейки включает в себя содержимое первых двух. После перемещения мы видим, что в результате автоматической подстройки ссылок содержащаяся в ячейке A3 формула изменилась, чтобы отразить произошедшие в электронной таблице изменения (теперь компоненты содержащейся в ячейке СЗ суммы находятся в других ячейках). Так же как и в предыдущем случае диапазон исходных ячеек после выполнения операции перемещения опустел.

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

Рис. 1.4. Перемещение содержимого ячеек: а – одной ячейки;

б – колонки; в – зависимых ячеек

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