Вычисления в электронных таблицах

 

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

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

Правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить в «уме». Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правиль­ности производимых в ней вычислений.

Если в любой ячейки таблицы, набранная последовательность символов начинается со знака «=» (равно), Excel считает, что Вы набрали формулу. За знаком «равно» набирается арифметическое выра­жение, которое состоит из операндов (элементов), соединенных между собой знаками арифметических операций. В качестве операндов в формуле могут использоваться адреса ячеек, числа в явном виде (иначе – лите­ралы) и функции.

Знаками арифметических операций в Excel служат:

(сложение);

(вычитание);

(умножение);

(деление);

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

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

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

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

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

– при копировании влево (вправо) по горизонтали смещение на одну ячейку уменьшает (увеличивает) каждый номер столбца в формуле на единицу;

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

Этим же способом можно копировать в смежные ячейки чис­ла и тексты.

2. Ссылки на ячейки. Формула может содержать ссылки, то есть адреса ячеек, содер­жимое которых используется в вычислениях. Это означает, что результат вычис­ления формулы зависит от числа, находящегося в другой ячейке. Ячейка, содер­жащая формулу, таким образом, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка.

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

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

При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Иногда это служит источником ошибок. Чтобы отменить автоматическое изменение адреса данной ячей­ки, вы можете назначить ей абсолютный адрес. При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для этого необ­ходимо проставить перед номером столбца и (или) перед номе­ром строки знак доллара «$». Например, в адресе $А5 не будет меняться номер столбца, в адресе В$7 – номер строки, а в адре­се $D$12 – ни тот, ни другой номер.

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

Использование стандартных функций

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

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

Если написание формулы начинается с функции, то перед именем функции вводится знак равенства (=).

Работая с функциями, помните:

1) функция, записанная в формуле, как правило, возвращает уникальное значение (арифметическое или логическое);

2) существуют функции, которые не возвращают значение, а выполняют некоторые операции (например, объединяют тексто­вые строки);

3) существуют функции без аргументов (например, функция ПИ() возвращает число p = 3,1415...).

В программе Excel можно использовать свыше 400 функций, которые разделены на категории (тематические группы): математические, финансовые, статистические, текстовые, логические, даты и времени и пр.

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

Использование мастера функций.

Для упрощения ввода функций в Excel предусмотрен специ­альный Мастер функций, который можно вызвать нажатием кнопки f_x на панели инструментов «Стандартная». Мастер функций имеет два окна – два шага.

Шаг 1.

В левом списке первого окна Мастера функций выбирается категория функций (например, «Матема­тические функции»). В правом списке отображаются имена всех функций, входящих в данную катего­рию.

После выбора имени функции и щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список пара­метров.

Шаг 2.

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

Построение диаграмм и графиков

В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изобра­жения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.

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

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

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

Выбор данных. Второй этап работы мастера служит для выбора данных, по кото­рым будет строиться диаграмма. Следует задать диапазон данных и заполнить строки данных на вкладке Ряд.

Оформление диаграммы. Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заголовки);

• отображение и маркировка осей координат (вкладка Оси);

• отображение сетки линий, параллельных осям координат (вкладка Линии сетки);

• описание построенных графиков (вкладка Легенда);

• отображение надписей, соответствующих отдельным элементам данных на гра­фике (вкладка Подписи данных);

• представление данных, использованных при построении графика, в виде таблицы (вкладка Таблица данных).

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсут­ствовать.

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

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

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

Контрольные вопросы

1.Как определяется адрес ячейки электронной таблицы?

2.Форматирование содержимого ячеек?

3.Копирование содержимого ячеек?