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

Завершив изучение этой темы, вы должны уметь:

1. Описывать назначения объектов: рабочая книга, рабочий лист, ячейка; называть типы ссылок и их стили; перечислять категории символов, вводимых в ячейки.

2. Называть компоненты формул; объяснить преимущества использования в формулах ссылок, а не значений.

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

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

5. Составлять формулы, адекватные условиям предъявляемых задач.

6. Демонстрировать умения вводить формулы с разными типами ссылок.

7. Видоизменять данные рабочего листа с помощью средств числового и стилевого форматирования.

8. Исследовать и обосновывать полученные результаты решения задач.

Краткие теоретические сведения

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

Наиболее популярные табличные процессоры – MS Excel, Lotus 1-2-3, Quatro Pro, Calc.

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

Рабочая книга в MS Excel представляет собой файл (с расширением .XLS), используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов.

Рис. 1. Аспекты манипулирования данными в табличном процессоре

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

Рис. 2. Основные объекты MS Excel

Ячейка или группа ячеек (диапазон) на листе однозначно определяется ссылкой – набором координат, который включает указатель столбца и строки. MS Excel поддерживает два типа стилей ссылок А1 и R1C1 (рис. 3).

	A	B	C				1	2	3
1						1
2						2
3						3
С2 – ссылка на заштрихованную ячейку						R2C3 – ссылка на заштрихованную ячейку
а) стиль А1						б) стиль R1C1

Рис. 3. Стили ссылок в MS Excel

По умолчанию MS Excel использует стиль ссылок A1, определяющий столбцы комбинациями букв (от A до IV, максимум 256 столбцов), а строки – номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов.

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

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

• операторы (они приведены в таблице 1);

• ссылки на ячейки или группы ячеек;

• константы (значения или надписи);

• функции и их аргументы;

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

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

Таблица 1

Операторы формул MS Excel

Оператор		Значение	Приоритет
Операторы ссылки объединяют диапазоны ячеек для осуществления вычислений
: (двоеточие)		оператор диапазона, который ссылается на все ячейки между границами диапазона включительно	I
(пробел)		оператор пересечения, который ссылается на общие ячейки диапазонов	II
; (точка с запятой)		оператор объединения, который ссылается на объединение ячеек диапазонов	III
Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом выполнения арифметической операции всегда является число
– (минус)		унарный минус	IV
% (процент)		вычисление процента	V
^ (крышка)		возведение в степень	VI
* (звездочка), / (слэш)		умножение, деление	VII
+ (плюс), – (минус)		сложение, вычитание	VIII
Текстовый оператор используется для обозначения операции объединения последовательностей символов в единую последовательность
& (амперсанд)		объединение (конкатенация) текстовых последовательностей в одну	IX
Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ
=, >, >=, < , <=, <>	равно, больше, больше или равно, меньше, меньше или равно, не равно		X

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

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

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

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

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

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

	A	B	C	D	E	F	G
1
2		=A2*$B$1
3		=A3*$B$1			=E1*E2	=F1*F2	=G1*G2
4		=A4*$B$1
5					=$C5*E$4	=$C5*F$4	=$C5*G$4
6					=$C6*E$4	=$C6*F$4	=$C6*G$4
7					=$C7*E$4	=$C7*F$4	=$C7*G$4

Рис. 4. Примеры изменения формул при копировании

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

Рис. 5. Структура строки формул

В MS Excel существует два типа форматирования – стилевое и числовое.

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

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