ВНИМАНИЕ

Используя этот способ автосуммирования, необходимо иметь в виду, что во время подсчета суммы используются значения из всех выделенных ячеек, игнорируются только ячейки, содержащие текст. Все остальные данные (типа дата, время, форму­ла) участвуют в вычислении суммы после соответствующего автоматического преоб­разования в числовой формат. Так, например, значение 1/2/1, трактуемое как дата 01.02.2001 года, в числовом формате рассматривается как число 36 923. И если ячейка с такой датой попадет в выделенный фрагмент, то ее содержимое будет включено в общую сумму.

-----------------------------------------------------------

Второй способ состоит в использовании кнопки Автосуммирование панели Стан­дартная. Как правило, итоговые суммы в таблицах помещают справа от строки или под столбцом, которые содержат подлежащие суммированию числа. В программе MS Excel достаточно выделить ячейку, в которую следует поместить результат (то есть ячейку, расположенную справа или под суммируемыми ячейками), и нажать кнопку Автосуммирование. Программа MS Excel попытается самостоятельно определить, какой диапазон ячеек выбран для суммирования. Предполагаемый про­граммой диапазон выделяется пунктирным контуром.

---------------------------------------------------------------------

СОВЕТ

Необходимо внимательно проверить, совпадает ли предлагаемый программой диа­пазон ячеек с тем, который намечен для вычисления суммы.

------------------------------------------------------------------

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

Формулы в MS Excel

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

Отличительным признаком формул в программе MS Excel является наличие знака равенства (=), плюс (+) или минус (-) в начале формулы.

Обычно формулы в качестве аргументов используют значения из других ячеек рабочей таблицы. Рассмотрим, например, формулу=А1+А2. Она интерпретиру­ется как указание MS Excel сложить числовые значения, хранящиеся в ячейках А1 и А2, после чего отобразить результат вычислений в ячейке, заменив находящуюся в ней формулу.

Операции и функции

В формулах допускается использование знаков математических и логических операций» а также операции конкатенации, объединяющей текстовые строки. Если в одном выражении используется несколько операций, то порядок их выполнения диктуется правилами старшинства, аналогичными правилам, действующими в арифметике. Например, действие умножения выполняется раньше, чем действие сложе­ния или вычитания. Так, в формуле=А1+А2*АЗ сначала выполняется умножение содержимого ячеекА2 и A3, а потом выполняется сложение полученного, результата с содержимым ячейкиА1. Если нужно изменить порядок, определяемый старшин­ством операций, то применяются круглые скобки. Как и в арифметике, действия в круглых скобках выполняются в первую очередь. Поэтому в формуле =(А1+А2)*АЗ сначала будет выполнено сложение, а затем умножение. Ниже приведены обозна­чения операций, которые могут использоваться в формулах программы MS Excel (в порядке убывания их приоритета):

1. - (унарный минус или отрицание);

2. % (вычисление процента);

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

4. *, / (умножение и деление);

5. +,- (сложение и вычитание);

6. & (оператор конкатенации);

7. <, <=,>, >=, =,<> (операции сравнения: «меньше», «меньше или равно», «боль­ше», «больше или равно», «равно» и «не равно» соответственно).

Кроме операций в формулах могут использоваться функции. В MS Excel опре­делено более 200 различных функций — математических, статистических, фи­нансовых, для работы с базами данных и ряд других. Командой Вставка > Функ­ция..., а также с помощью кнопки Вставка функцийfx панели инструментов Стандартная или строки формул можно вызвать мастера вставки функций. Работа мастера состоит из двух шагов. Для каждого шага используется отдельное окно. Диалоговое окно первого шага Мастер функций — шаг 1 из 2, в котором перечислены все функции, используется для выбора нужной для вычислений функ­ции, а окно второго шага—для задания ее аргументов.

Чтобы включить функцию в формулу в списке Категория: окна первого шага, нуж­но выбрать строку, соответствующую характеру включаемой функции, а затем в об­новившемся после выбора категории списке Функция: отобрать строку с ее назва­нием. Во время выбора функции в нижнем секторе окна отображаются ее описание и требуемые аргументы. После нажатия кнопки ОК произойдет открытие окна вто­рого шага мастера функций, в котором предоставляется возможность ввести все необходимые для вычисления значения функции аргументы. Аргументы функции записываются в круглых скобках после ее названия. Если аргументов у функции несколько, то они перечисляются через запятую. Например, функция вычисления суммы называетсяСУММ. Если требуется просуммировать числа, находящиеся в ячейкахAl, A2 и A3, то это можно сделать с помощью функцииСУММ(А1,А2,АЗ)с тремя аргументами.

Если аргументом функции или формулы является адрес ячейки, то вместо ввода этого адреса с клавиатуры можно выполнить щелчок клавишей мыши на нужной ячейке — ее адрес автоматически станет аргументом выбранной функции или за­писываемой в ячейку формулы. Кроме того, с помощью кнопки Я, которая распо­ложена в правом конце поля ввода аргументов, можно временно перейти к исход­ной таблице и в ней выделить соответствующие ячейки или их диапазоны.

Некоторые функции, например функция вычисления суммыСУММ, среднего арифметического (она называетсяСРЗНАЧ), наименьшего(МИН) или наиболь­шего (МАХ) значений, могут иметь много аргументов, которые должны быть рас­положены в некоторых ячейках таблицы. В этом случае адреса всех аргументов должны быть указаны в обращении к такой функции. В программе" MS Excel пре­дусмотрена возможность упрощения обращения к функциям с помощью задания так называемогодиапазона ячеек. Диапазон ячеек задается адресами первой и по­следней ячейки диапазона, которые отделены друг от друга двоеточием. Напри­мер, записьВ2:В6 аналогична перечислению совокупности ячеекВ2, ВЗ, В4, В5, В6, а диапазонА1:ВЗ задает прямоугольный фрагмент, он эквивалентен следую­щему списку аргументовA1, A2, A3, В1, В2, ВЗ. Обращаем внимание читателя на то, что обычный список может содержать произвольные, не смежные адреса ячеек, в то время как диапазон всегда включает в список аргументов все его ячейки.

Ошибки в формулах

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

Таблица 2 Сообщения об ошибках в формулах

#ДЕЛ/0! Попытка деления на ноль
#Н/Д! Отсутствуют данные, необходимые для расчетов (возможно, ячейка пуста)
#ИМЯ! Ссылка на несуществующее имя
#ЧИСЛО! Использован недопустимый числовой аргумент
#^ССЫЛКА! Неправильно указан адрес ячейки
#ЗНАЧ! Тип значения не совпадает с типом данных, допустимых для данного аргумента

 

 

Редактирование формул

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

Автопересчет

Каждый раз, когда в ячейки, адреса которых используются в формулах, вносятся изменения, программа MS Excel автоматически пересчитывает значения во всех ячейках таблицы. Если таблица достаточно велика, то такой пересчет может за­метно увеличить время редактирования таблицы. Поэтому, например, во время отладки автоматический пересчет Excel можно отключить. Для этого нужно вы­полнить команду Сервис > Параметры... и на странице Вычисления включить пе­реключатель вручную. Теперь пересчет значений будет производиться только при нажатии клавиши F9.

Относительная и абсолютная адресация

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

ПРИМЕЧАНИЕ ---------------------------------------------------

Различие между этими формами адреса проявляется только при копировании или перемещении ячейки с формулой в другое место рабочей таблицы.

-------------------------------------------------------------------------

Смысл относительного адреса зависит от относительного положения ячейки, в ко­торой находится формула с относительным адресом, и ячейки, на которую пока­зывает этот адрес. Пусть, например, формула, содержащая адресА1, находится в ячейкеA3. Это значит, что речь в формуле идет о ячейке, которая находится через одну ячейку над ячейкой с формулой. И при переносе или копиро­вании формулы этот адрес автоматически изменится таким образом, чтобы в но­вом положении он показывал на ячейку, которая расположена точно так же. Если, например, формула скопирована в ячейку С4, то в ячейке окажется формула, ко­торая вместо адресаА1 содержит адресС2, поскольку ячейка С2 рас­положена относительно ячейки С4 точно так же, как ячейкаА1 расположена отно­сительно ячейкиA3.

Внешним признаком абсолютного адреса является наличие знака $ перед значением координаты в адресе ячейки. Знак $ может стоять в адресе как перед обозначением столбца, так и перед номером строки. Рассмотрим, например, адрес ячейкиAt. Так как знак $ в адрес не входит, то это запись относительного адреса. Таким образом, все рассматривавшиеся выше адреса ячеек были относительными. Возможны следующие варианты включения знака $ в адрес: $А1, А$1 и $А$1. Полностью абсолют­ным является адрес $А$1, он всегда при любом копировании и перемещении останется неизменным и всегда указывает на ячейку, находящуюся на пересечении столбца А и строки 1. В адресах $А1 и А$1 сочетаются абсолютная и относительная адреса­ция. В первом случае ($А1) абсолютная адресация используется для задания столб­ца, а относительная — для строки, а во втором случае (А$1) — наоборот.

-------------------------------------------------------------------