Программирование на рабочем листе

В современных версиях Excel можно выполнять вычисления, используя формулы, связанные между собой ссылками (адресами ячеек), давать нестандартные имена данным. Разрешается также именовать формулы. Кроме того, в Excel имеется большое количество функций и средства решения сложных вычислительных задач. Все это позволяет реализовывать достаточно сложные алгоритмы. Кроме того, табличный процессор Excel содержит мощные средства программирования на языке VBA (Visual Basic for Application). Но и без использования VBA правильнее говорить о программировании на рабочем листе, чем просто о выполнении вычислений.

В ячейки рабочего листа Excel можно записывать числа, тексты, даты, формулы. Каждая ячейка имеет свой адрес. Адреса ячеек называются также ссылками. Существует два формата адресации ячеек. В одном из них (А1), наиболее часто используемом, адрес состоит из имени столбца (их 256, - А,В,…Z,AB,…HZ. IA,…IV) и номера строки (1,2,…65356). Например, А1 – это адрес ячейки, расположенной на пересечении первой строки и первого столбца. Второй формат – R1C1. Он используется редко. Для переключения между форматами следует выбрать команды Сервис → Параметры, далее – вкладку Общие. Если снят флажок в строке Стиль ссылок R1C1, то в Excel будет использоваться формат адресации А1.

Формула в Excel начинается со знака «=» (равно) и содержит константы, ссылки на ячейки, имена, функции Excel, соединенные знаками арифметических, логических или текстовых операций. В формуле записываются ссылки на ячейки (адреса ячеек), в которых хранятся используемые в этой формуле данные. Пусть в ячейке А1 записано число 2, а в ячейке В1 – число 3. Для вычисления суммы этих чисел поместим в какую-либо пустую ячейку, например С1, формулу: =А1+В1

Завершить ввод формулы можно нажатием клавиши Enter. В ячейке С1 появится результат – число 5 (рис. 1). Если выделить ячейку, в которой записана формула, то эта формула появится в строке формул.

Рис. 1. Вычисление суммы двух чисел

Ссылки, которые не содержат символа $ (доллар), называются относительными. При копировании формул они изменяются. Пусть, например, в ячейку С1 (рис.1) введена формула =А1+В1. После копирования этой формулы в ячейку С2, она приняла вид: =А2+В2. По умолчанию в Excel создаются относительные ссылки. Если адрес содержит знак доллара, то он называется абсолютным адресом или абсолютной ссылкой. Если требуется, чтобы при копировании формулы некоторый адрес оставался бы неизменным, его преобразуют в абсолютный добавлением знаков доллара. Изменить тип ссылки с относительной на абсолютную, можно следующим способом:

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

2. В строке формул установить курсор на первый символ ссылки.

3. Нажать клавишу F4.

4. Завершить ввод, нажав клавишу Enter.

Пример. Для трех значений х, равных 1, 2.5, 4, вычислить функцию В ячейке В2 (рис. 2) записана формула: =A2+$C$2. При копировании этой формулы в ячейки В3 и В4 изменяется только первая ссылка (относительная). Вторая ссылка (абсолютная) остается неизменной.

 

Рис. 2. Использование абсолютной ссылки

 

В Excel можно по своему усмотрению установить разделитель целой и дробной части числа. Для этого следует выбрать команды Сервис → Параметры, затем в открывшемся окне «Параметры» - вкладку Международные (рис.3). Флажок (галочка) в строке Использовать системные разделители означает, что целая часть числа отделяется от дробной запятой.

 

Рис. 3. Замена разделителя

В русскоязычных версиях Excel по умолчанию целая часть числа отделяется от дробной запятой. Если пользователь ошибся и вместо запятых поставил точки, то следует выделить диапазон неправильно записанных чисел, выбрать команду меню Правка, затем, в раскрывающемся списке – строку Заменить. Откроется окно «Найти и заменить»(рис.5). В строке Найти надо указать символ точки (.), а в строке Заменить на – символ запятой, нажать кнопку Заменить все.

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

 

 

Рис.4. Выравнивание текста и чисел

Рис. 5. Замена символа

Рис.6. Форматирование чисел

 

В памяти компьютера числа хранятся с точностью до 15 значащих цифр (разрядов). Отображение числа в ячейке зависит от того, как отформатирована эта ячейка. Желательно всегда форматировать ячейки, в которые будут записываться данные или результаты. Для форматирования выбираем в строке меню команду Формат,в раскрывающемся меню – строку Ячейки. Откроется приведенное на рисунке 6 окно. Если выбран числовой, денежный, финансовый или экспоненциальный формат, можно установить число десятичных разрядов – количество значащих цифр после запятой. Если все цифры числа не размещается в ячейке, то эта ячейка заполняется символами #. В этом случае следует увеличить ширину столбца или уменьшить с помощью команды Формат количество десятичных знаков в числе.

В Excel получающиеся в результате вычислений очень большие или очень маленькие числа записываются в ячейках в экспоненциальном формате. Так, число 125 348 000 000 000 000 состоит из 18 значащих цифр, что превышает допустимыйв Excel предел. В математике это число можно представить в виде: 125 348∙10¹². В Excel числа в экспоненциальном формате записываются подобным образом, но вместо числа 10 и математических символов умножения и возведения в степень используется буква «Е» (или «е»), за нею – показатель степени, в которую возводится число 10. Символ возведения в степень никогда не записывается!

Примеры записи числа в экспоненциальной форме:

125 348 или 125,3487е3 или 0,125348е6

Очень маленькое число

0,000 000 000 000 000 125 (или 0,125∙10^-15) в экспоненциальной форме имеет вид:

0,125е-15

Здесь показатель степени, в которую возводится число 10, отрицателен.

При использовании процентного формата после числа записывается символ процента - %.

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

Дробный формат служит для отображения чисел в виде обыкновенных дробей: 1/3, 2/7, 5/11 и т.д.

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

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

В Excel по умолчанию используется шрифт Arial Cyr. Добавка Cyr означает, что этот шрифт содержит символы кириллицы (буквы русского алфавита). Можно выбрать иной тип шрифта, его размер и начертание на панели форматирования или с помощью команд Формат → Ячейки и вкладки Шрифт (рис.7.).

Рис. 7. Выбор шрифта

На рисунке 8 приведен пример созданной средствами Excel отформатированной таблицы. При создании таблиц часто используется вставка Выравнивание окна Формат ячеек (рис.9.).

 

	Затраты на перевозку одного изделия	Ограничения количества изделий по потребностям фирм
Цех 1	Цех 2	Цех 3
Фирма 1
Фирма 2
Фирма 3
Ограничения по производительности цехов

 

 

Рис.8.Пример таблицы

Для объединения нескольких ячеек в одну следует выделить их, выбрать команды Формат → Ячейки, вкладку Выравнивание и отметив флажокв строке Объединение ячеек, щелкнуть кнопку ОК. Если отметить флажок в строке Переносить по словам, текст в ячейке будет записан в нескольких строчках.

 

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

 

Рис.9.Форматирование и выравнивание

Примеры решения задач