Формулы

Формулы

Разбить лист на ячейки и разрешить пользователям заполнять их – дело нехитрое. Но возможности Excel этим не ограничиваются. Следующая ступень мастерства – связать отдельные клетки с цифрами невидимыми связями с помощью математических формул. А это значит, что при изменении значения одной ключевой клетки автоматически будут пересчитаны значения и во всех ячейках, связанных с ней. Для того чтобы проиллюстрировать эти возможности Excel, попробуем создать формулу, дублирующую встроенный в Excel механизм автозаполнения.

Откроем новый лист и в первую ячейку (А1) внесем цифру 1. Допустим, вы хотите, чтобы число в каждой последующей строке одного столбца было в два раза больше предыдущего. Выделив щелчком вторую клетку столбца с номером А2, отправьте курсор в строку, отмеченную знаком «=» (она находится непосредственно над полем с клеточками и предназначена как раз для записи формул). В строке впишите формулу, устанавливающую зависимость второй клетки от первой. При записи формул необходимо поставить перед ними знак «=», чтобы Excel смог отличить формулу от простой текстовой записи.

=A1*2

После нажатия кнопки ввода во второй клеточке столбца появится число 2. Теперь выделите мышкой клетку, которой мы присвоили формулу, подцепите мышкой правый нижний угол этой клетки и, держа нажатой левую кнопку мыши, потяните ее вниз по столбцу, до тех пор, пока курсор не остановится на клетке с нужным вам номером, например, с номером А64. Теперь, отпустив кнопку мыши, вы присвоите всем выделенным вами ячейкам формулу первоначальной клетки с одной маленькой поправкой: в формуле для каждой новой клетки в столбце будет фигурировать именно ее клетка-предшественница, а не первоначальная клетка А1. Это преобразование Excel выполнит автоматически.

Итак, у вас получился ряд из 64 клеток, в каждой из которых вписано число, вдвое большее числа в предыдущей клетке. Осталось узнать сумму. Как мы помним, это можно сделать, поставив курсор на свободную ячейку под нужным столбцом и нажав кнопку ? (сумма) на Панели инструментов Excel. Программа моментально рассчитает сумму чисел в столбце и выдаст результат. Но этот простой путь нас уже не устраивает – ведь эту же операцию можно проделать с помощью формулы!

Итак, нам нужно узнать сумму значений ячеек в диапазоне с A1 по A64. Формула на этот случай будет такая:

=СУММ(А1:А64)

Знак «:» используется в Excel для сокращенного обозначения интервала ячеек, то есть, «A1:A64» означает «от ячейки A1 до ячейки A64».

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

Правило построения формул

Как видим, любая формула состоит из нескольких основных частей:

? Знак равенства (=), с которого обязательно начинается любая формула. Если знак равенства отсутствует, Excel интерпретирует данные не как формулу, а как обычный ввод в ячейку.

? Значения или ссылки на ячейки, с которыми выполняются расчеты.

? Операторы (простые математические операции, такие как сложение или умножение).

Операторы Excel

Например, простейшая формула имеет вид:

=А1+А2

Эта формула складывает значения ячеек А1 и А2. При создании формул Excel важно помнить о приоритете операторов.

При обработке формулы Excel выполняет операции слева направо в следующем порядке:

? Все операции в круглых скобках.

? Показательные (экспоненциальные) выражения или операторы.

? Остальные операции согласно правилам арифметики.

Ввод формул

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

Чтобы ввести формулу, выполните следующее:

Выделите ячейку, которая должна содержать формулу, и введите знак равенства (=). Это предупреждает Excel о предстоящем вводе формулы.

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

Введите оператор, например, знак плюс (+).

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

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

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

Введенную формулу можно отредактировать в любой момент. Для этого просто выделите ячейку с формулой, щелкните по строке формул и введите нужные изменения. Для выбора новых ячеек и диапазонов можно воспользоваться мышью. Закончив, нажмите клавишу Enter или щелкните по кнопке Enter. Excel выполнит расчеты по формуле и отобразит в ячейке новые результаты. Если вы ввели формулу неверно, с ошибкой, Excel не замедлит известить вас об этом. Рядом с ячейкой, к которой вы привязали ошибочную формулу, появится тревожный восклицательный знак. В отдельных случаях Excel не просто укажет на наличие ошибки, но и расскажет вам о том, что именно вы сделали не так.

Копирование формул

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

Адреса ячеек в разных листах

При работе с Excel мы можем оперировать не только отдельными ячейками или даже таблицами-листами, но и целыми книгами. И вот представьте себе, что перед нами возникает необходимость «связать» несколько различных таблиц в книгу и снабдить их перекрестными ссылками. То есть результат в ячейке одного листа-таблицы будет напрямую зависеть от ячейки другого. Использовать адреса ячеек в формулах мы уже умеем – но только в пределах одного листа. А как же быть в этом случае? Если вам необходимо использовать ссылку на ячейку в другом листе, включите в формулу его имя, поставив между ним и адресом ячейки восклицательный знак.

Абсолютные и относительные адреса ячеек

Для упрощения копирования и перемещения формул в Excel применяется концепция, известная как относительная адресация. Excel интерпретирует адреса ячеек, введенные в формулу, не как ссылку на их постоянное расположение, а как ссылку на их относительное расположение. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Например, если первые 3 ячейки столбца А заполнены следующими данными:

А1: 50

А2: 100

А3: =А1+А2

то при работе с формулой в ячейке А3 Excel понимает ее не как «взять число из ячейки А1 и прибавить его к числу из ячейки А2». Вместо этого система понимает данную формулу как «взять число из ячейки, расположенной в том же столбце, что и текущая, на две строки выше, и прибавить его к числу из ячейки, расположенной...». При копировании данной формулы в другую ячейку, например, в В3, принцип останется тот же: «взять число из ячейки, расположенной в том же столбце, что и текущая, на две строки выше, и прибавить его к...». То есть, при копировании этой формулы из ячейки А3 в ячейку В3 она автоматически преобразится в формулу =В1+В2. Такой тип ссылок избавляет от необходимости снова и снова вводить одинаковые формулы – их можно просто копировать. Однако, в некоторых формулах необходимо сохранять ссылку на точно указанную ячейку, иными словами, ссылка на ячейку не должна зависеть от расположения формулы на листе. Например, в рабочем листе имеется список товаров с их долларовыми ценами, и есть одна ячейка, например А1, в которой записан текущий курс рубль/$. При создании формулы необходимо всегда ссылаться на ячейку А1, и эта ссылка не должна изменяться при копировании формулы. В таком случае следует применять ссылку иного типа – абсолютную ссылку. Абсолютная ссылка остается неизменной при изменении адреса ячейки с формулой. При помощи абсолютных ссылок можно заставить Excel при копировании формул изменять ссылки на столбцы, но сохранять ссылку на строку постоянной, либо изменять ссылки на строки, но сохранять ссылку на столбец постоянной, либо сохранять постоянными ссылки – как на столбец, так и на строку. Чтобы заменить относительную ссылку на абсолютную, введите знак доллара ($) перед той частью ссылки, которая должна стать абсолютной.

Примеры:

$А1 Всегда ссылается на столбец А, ссылка на строку может изменяться.

А$1 Всегда ссылается на строку 1, ссылка на столбец может изменяться.

$А$1 Всегда ссылается на ячейку А1.

Ссылки вида А1 называются относительными, вида $А$1 – абсолютными, вида $А1 или А$1 – смешанными.

Чтобы ввести абсолютную ссылку, выполните следующие действия:

Выделите ячейку для формулы и введите знак равенства (=).

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

Нажмите клавишу F4. Excel немедленно установит знаки доллара перед буквой, обозначающей столбец, и номером строки в ссылке на ячейку. Знак $ можно ввести вручную.

Продолжайте строить формулу вплоть до завершения, нажимая клавишу F4 для вставки Повторно, нажимая клавишу F4, можно циклически переходить от одного типа ссылок к другим. Введите, например, относительную ссылку С6. Теперь нажимайте клавишу F4 – ссылка будет иметь вид: $C$6, C$6, $C6 и С6.

Данный текст является ознакомительным фрагментом.