ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

ЭЛЕКТРОННЫЕ ТАБЛИЦЫ

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

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

В 1982 году появляется знаменитый табличный процессор Lotus 1-2-3, предназначенный для IBM PC.

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

Позднее на рынке появились многочисленные продукты этого класса - SuperCalc, Microsoft MultiPlan, Quattro Pro, Lotus 1-2-3, Microsoft Excel,

OpenOffice.org Calc, таблицы AppleWorks и gnumeric, минималистический

78

Spread32. Наибольшее распространение в настоящее время в нашей стране получили электронные таблицы Microsoft Ехсеl. Вместе с тем программа OpenOffice.org Calc ни чем функционально не уступает Microsoft Ехсеl. Офисный пакет OpenOffice.org может свободно устанавливаться и использоваться в школах, офисах, вузах, домашних компьютерах, государственных, бюджетных и коммерческих организациях и учреждениях России и стран СНГ согласно

GNU General Public License.

Самым старшим в иерархии (родительским) объектом обработки ЭТ является Книга (Workbook). Книга состоит из рабочих Листов (Worksheets) следующим по иерархии (дочерним) объектом. Книга может содержать от 1 до 255 рабочих листов по выбору пользователя. Файл автоматически сохраняется с именем КнигаN.xls Лист ЭТ состоит из 232=65536 строк и 28=256 столбцов (в версии 2007 года соответственно 220=1048576 строк и 214=16384 столбцов). Основным элементом обработки является объект Ячейка (Cells) самый младший и конечный в иерархии объект. В ячейку можно вводить числа, текст, даты и формулы и назначать формат отображения этих данных. Каждая ячейка имеет свой адрес, например А1, В2. Ячейки в таблице могут быть объединены в диа-

пазон (Range).

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

– B2:C6; A1:A6.

Формально диапазон из одной ячейки аналогичен самой ячейке. Таким образом, диапазон является обобщением объекта ячейка.

Если диапазон содержит числовые величины, то по нему может быть вычислена сумма, найдено среднее, минимальное или максимальное значение и т.д.

Ячейки в ЭТ могут содержать числа, значения даты, символьные и строковые величины, логические величины, формулы. Текст и числа рассматриваются как константы. Запись формулы в ЭТ начинается со знака «=». Формулы ЭТ содержат данные, ссылки на ячейки, арифметические операции, функции и скобки. Формулы автоматически пересчитывают свои значения, при любом изменении исходных данных. Автоматический расчет формул можно заменить на ручной режим (Сервис-Параметры-Вычисления), в котором вся рабочая книга будет пересчитываться по нажатию клавиши F9.

79

Арифметический оператор				Значение		Пример			В ячейке
	+ (плюс)			Сложение		=3+3			6
	– (минус)			Вычитание			=3–1		2
				Отрицание			–1		-1
	* (звездочка)			Умножение		=3*3			9
	/ (косая черта)			Деление			=3/3		1
	% (знак процента)			Процент		20%			20%(0.2)
	^ (знак «крышка»)			Возведение в степень			=3^2		9

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

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

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

пировании вниз формулы с относительными ссылками из ячейки D2 в ячейку D3 она автоматически изменяется с =В2*С2 на =В3*С3.

Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки E3 =D3/$A$1 в ячейку E4 ссылка на ячейку А1 остается прежней ==D4/$A$1, а изменится лишь относительный адрес в числителе формулы.

80

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

Для ссылок на ячейки другого листа используется адресация с указанием имени листа Лист1! $А$1. Возможно осуществлять ссылки на ячейки другой

книги,	тогда	адрес	ячейки	запишется	следующим	образом
[Книга1] Лист1! $А$1

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

Тестовые задания Задание 1

При копировании содержимого ячейки C3		1.	=A1+$B1+C$1+$D$1
в ячейку E6 в ячейке E6 была получена		2.	=A1+B1+C1+D1
формула =C4+$B4+E$1+$D$1. В ячейке		3.	=A1+$B1+C$1
C3 была записана формула…		4.	A1+$B1+C$1+$D$1

81

Задание 2

Указатель мы-	1.	заполнении ячеек по закономерности (автозаполнении)
ши в MS Excel	2.	изменении ширины столбца
имеет вид	3.	выделении блока ячеек
при …	4.	выборе значения из раскрывающегося списка

Задание 3

Дан фрагмент электронной таблицы.

	Формула из ячейки A4 копируется в	1.	СРЗНАЧ
	B4:C4. По данным блока A4:C4 по-	2.	МАКС
	строена лепестковая диаграмма. В A4
		3.	МИН
	вместо «?» используется функция
		4.	СУММ

Задание 4

Торговый агент получает премию в зависимости от объема заключенной сделки по следующей схеме: если объем сделки до 3000, то в размере 5%; если объем больше 3000, но мень-

ше 10000 – 7%; свыше 10000 – 10%. Формула в ячейке C2 должна иметь вид…

1.=ЕСЛИ(B2<3000;B2*5%;ЕСЛИ(B2<10000;B2*7%;B2*10%))

2.=ЕСЛИ(B2<3000;B2*5%;B2<10000;B2*7%;B2*10%)

3.=ЕСЛИ(B2<3000;B2*5%;B2*7%;B2*10%)

4.=ЕСЛИ(B2*5%;B2*7%;B2*10%)

Задание 5

	Дан фрагмент электронной таблицы.				Таблица приобретет вид
					после выполнения команды …
	1.	промежуточные итоги 2.		условное форматирование
	3.	сортировка 4.	расширенный фильтр
				82