§ 4 Выполнение арифметических операций средствами Excel 2000

Важнейшим преимуществом Excel 2000 перед другими табличными процессорами является очень мощный аппарат для проведения математических и прочих расчётов. Простейшие расчёты - арифметические действия над числами, помещёнными в соответствующие ячейки электронной таблицы. Для выполнения таких действий в ячейку, где должен находиться результат расчёта, записывают знак «=», свидетельствующий о характере выражения, как математической формуле, и адреса ячеек, содержащих исходные данные, соединённые знаками арифметических действий: +, -,*,/ , ^ (возведение в степень). Знаки арифметических действий называются арифметическими операторами. Наряду с арифметическими операторами программа даёт пользователю возможность применять логические операторы: = равенство, < меньше, > больше, <= меньше или равно, >= больше или равно, <> неравно, текстовый оператор & (амперсанд) объединение последовательностей символов. Действия программы после записи формул зависят от установок, записанных в пункте Параметры из меню «Сервис». Обычно вычисления производятся автоматически и их результат вставляется в соответствующую ячейку. При желании пользователь может заменить результаты формулами. Для этого надо дать команду Сервис—› Параметры—›Вид—›Параметры окна, после чего изменить состояние флажка перед словом «Формулы».

Перемещение и копирование формул. Абсолютная и относительная системы адресации ячеек. Одним из параметров, который может автоматически изменяться, является адрес ячейки, т.е. номер столбца и номер строки, на пересечении которых расположена ячейка. Такое изменение может быть полезным при выполнении расчётов с использованием формул. Пусть в ячейки столбца А внесены некоторые исходные величины, а в соответствующие ячейки столбца В требуется внести определенную их часть (например, 20%). Тогда в ячейке В1 записываем =А1*20% или =А1*0,2. Знак «=» является признаком формулы, т.е. даёт возможность отличить обычный текст от математических формул. Далее методами, представленными в таблице 1, распространяем формулу на остальные ячейки диапазона. Необходимые изменения адресов будут произведены автоматически. Аналогичные манипуляции можно производить и со строками. Изменяемые адреса ячеек называются относительными. При обычной записи адреса считаются относительными.

Пример задачи с использованием относительных адресов ячеек и копированием формулы показан в таблице 2 и в таблице 3. Здесь формула =С3-D3 вносится в ячейку Е3 и затем копируется в диапазон E4:E7.

Т аблица 2

Т аблица 3

Однако, бывают случаи, когда адрес ячейки, например С1, при всех манипуляциях должен оставаться неизменным. Такой абсолютный адрес должен иметь знак «$» перед названием столбца и перед номером строки. Например, =A1*$c$1.

Пример использования абсолютных адресов ячеек показан в таблице 4 и в таблице 5.

Здесь выражение =С3*$D$19 вводится в ячейку F3 и затем копируется в диапазон F3:G16.

В ячейку D19 вводится значение курса доллара, которое используется при расчете розничной и оптовой цены в рублях. Чтобы при копировании формулы значение курса доллара не менялось, в формуле используется абсолютный адрес ячейки $D$19 (можно D$19).

Самостоятельно скопируйте таблицу на лист2 и лист3. Переименуйте Листы: Лист1 – на Январь; Лист2 – на Февраль; Лист3 -на Март; Лист4 – на Квартал. Переименовать листы можно с помощью контекстно-зависимого меню (командой «Переименовать», наведя указатель мыши на вкладку Лист и нажав правую кнопку). В феврале и марте измените курс доллара, цены мониторов в таблицах изменятся автоматически. Назовите таблицы: «Цены мониторов за январь», «Цены мониторов за февраль», «Цены мониторов за март». На листе «Квартал» создайте новую таблицу, в которой укажите розничную цену мониторов в рублях за январь, февраль и март (цены возьмите с соответствующих листов, например, =Январь!С3). В последнем столбце рассчитайте среднюю цену каждого монитора за квартал.

Т аблица 4

Таблица 5.

В случае необходимости, одна из координат ячейки может быть абсолютной, а другая - относительной. Например, запись =$А2*В$1свидетельствует об абсолютном характере номера столбца первой ячейки и номера строки второй ячейки. Остальные координаты обоих ячеек относительны. Такая форма записи адреса часто используется при создании таблиц, расчёта значений функций двух аргументов с использованием автозаполнения ячеек.

Пример нахождения значений таблицы умножения с использованием абсолютных адресов ячеек показан в таблице 6 и в таблице 7. Здесь формула =$A3*B$2 записывается в ячейку В3 и затем копируется в диапазон В3:J11. Таким образом, абсолютными делают адреса аргументов, которые в данном примере находятся в столбце А и в строке 2.

Т аблица 6.

Т аблица 7.

Использование осмысленных имён, как одна из форм абсолютной адресации. Excel 2000 дает возможность присваивать выделенным ячейкам или областям осмысленные имена и заменять ими координаты. Присвоить имя выделенной области можно путем нажатия кнопки в области имени с последующей записью в неё требуемого имени. Тот же результат может быть достигнут с использованием команды Вставка—›Имя—› Присвоить. На экране появится таблица диалога, включающая список присвоенных имен, строку ввода и командные кнопки. В строку ввода надо внести имя выделенной области и щёлкнуть мышью по кнопке «Добавить». Для восстановления первоначального положения в той же таблице диалога следует выделить удаляемое имя и щелкнуть мышью по кнопке «Удалить». Если осмысленные имена присвоены многим элементам таблицы, целесообразно вывести все названия и координаты соответствующих им ячеек на какой-либо свободный лист рабочей книги. Вывод перечня имён и вставка нужного имени может осуществляться при помощи команды Вставка—› Имя—› Вставить. После этого на экране появится диалоговое окно с перечнем имён и управляющими кнопками. По имени можно обращаться к ячейкам, расположенным на любом из листов рабочей книги. Здесь пользователь должен щёлкнуть мышью по кнопке «Все имена».

Имеется также возможность присваивать осмысленные имена листам рабочей книги вместо порядковых номеров. Для этого надо дважды щёлкнуть мышью по ярлычку листа в нижней части экрана, после чего в диалоговое окно вписать новое название листа и нажать «Enter». Другой способ переименования листа - щёлкнуть по ярлычку правой кнопкой мыши. Появится контекстное меню, содержащее перечень операций работы с листом (вставка, удаление, переименование, выделение). В нём необходимо выбрать соответствующий пункт и ответить на последующие вопросы, которые будет автоматически задавать программа. Вторая процедура несколько сложнее, однако, более универсальна, чем первая, ибо применима для выполнения каждой операции, поименованной в контекстном меню. При выполнении расчётов осмысленные имена могут быть использованы наравне с координатами. Например, если ячейкам заранее присвоены имена, то возможна такая запись = Цена*Количество.