Ввод с использованием формул

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

Например: =CУMM(A1:A15)/$C$3+ЦЕНА*(150*КОЛИЧ)

При этом:

· CУMM - имя функции

· А1, А15, $C$3 - ссылки

· А1:А15 - диапазон (массив) ячеек

· ЦЕНА, КОЛИЧ - имена диапазонов ячеек

· 150 – константа

В формулах могут быть использованы арифметические операторы:

· + сложение,

· - вычитание,

· * умножение,

· / деление,

· ^ возведение в степень.

Формулы могут также содержать операторы сравнения:

· равно (=),

· не равно (<>),

· больше (>),

· меньше (<),

· не более (<=),

· не менее (>=)

и оператор объединения текстовых данных $ (конкатенация).

Кроме того, Excel распознает три адресных оператора для указания диапазонов ячеек:

1.Оператор диапазона (двоеточие).

Оператор диапазона можно использовать для указания ссылок на ячейки всего столбца (А:А) или строки (1:1), на ячейки всего рабочего листа (А:IV) или (1:16384).

2.Оператор объединения диапазонов (точка с запятой).

Например, в формуле =CУMM(C5:C7; A3:C3) вычисляется сумма двух несмежных диапазонов.

3.Оператор пересечения диапазонов (пробел).

Например, по формуле =CУMM(A5:B11_ B8:C10) вычисляется сумма содержимого пересекающегося диапазона В8:В10.

 Используемые в формулах ссылки на ячейки могут быть трех типов:

· абсолютные;

· относительные;

· смешанные.

Абсолютная ссылка задает абсолютные координаты ячейки на рабочем листе. При перемещении или копировании формулы из одной ячейки в другие такая ссылка в формуле не изменяется и всегда указывает на одну и ту же ячейку. Для записи абсолютной ссылки в стиле А1 используется знак $, например $A$5.

Относительная ссылка записывается без знака $, например А5, А8. При вычислении формулы такие ссылки, как и абсолютные указывают на координаты ячеек, участвующих в операции. Например, по формуле =A2+B2+C2 так же как и по формуле=$А$2+$B$2+$C$2 осуществляется суммирование содержимого первых трех ячеек второй строки рабочего листа. Отличие заключается в том, что при копировании формул с относительными ссылками в другое место, последние изменяются. Изменения определяются новым положением формулы в таблице. При этом действует следующее правило: относительная ссылка изменяется таким образом, что маршрут (направление движения и расстояние) к адресуемой ячейке относительно нового адреса формулы сохраняется.

Смешанная ссылка - это ссылка, на которой адресации столбца, напримерА$6, $B8 и т.д.

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

Имя диапазона должно удовлетворять следующим требованиям:

· первым символом имени должна быть буква, символ подчеркивания или прямой слеш (/);

· имя не должно превышать 255 символов;

· цифры в именах не должны совпадать с адресами ячеек в стиле «А1».

Присвоить имя диапазону можно двумя способами: через поле имен в строке формул или путем выполнения командыПрисвоить имя ленты Формулы.

Первый способ присвоения можно реализовать следующим образом:

· выделить диапазон ячеек, которому будет присваиваться имя;

· поместить щелчком мыши курсор ввода в поле имени строки формул и заменить указанный здесь адрес ячейки именем диапазона;

· нажать клавишу <Enter>.

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

Для присвоения имени диапазону вторым способом необходимо выполнить следующие действия:

· выделить диапазон ячеек;

· выбрать командуПрисвоить имя ленты Формулы;

· в появившемся диалоговом окне (рис. 17) в строке Имя: ввести имя диапазона и нажать .

Рис.17. Диалоговое окно Создание имени

Формула может вводиться как непосредственно в ячейку, так и в строку формул.

Для ввода формулы в ячейку необходимо:

· выделить нужную ячейку;

· указать в качестве первого символа знак "=";

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

· задать ссылку на нужную ячейку или диапазон ячеек. При этом могут использоваться два способа: ссылка на эти элементы вводится посимвольно с клавиатуры или ввод осуществляется щелчком мыши по нужной ячейке (методом указания). Последний способ предпочтительнее, поскольку ввод ссылок с клавиатуры требует большого времени и сопряжен с ошибками;

· ввести оставшуюся часть формулы. Завершить ввод нажатием клавиши <Enter>. В ячейке при правильном вводе формулы появится результат вычисления. Саму формулу можно увидеть только в строке формул.

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

Действие над ячейками

Кроме ввода данных и редактирования содержимого рабочей книги Excel можно выполнять следующие действия:

· копирование;

· перемещение;

· вставка;

· удаление и очистка содержимого.

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

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

При реализации первого способа сначала необходимо выполнить для выделенных ячеек командыКопировать или Вырезать меню Правка. Эти команды помещают содержимое ячеек в буфер обмена. Затем по командеВставить содержимое буфера вставляется в нужное место таблицы. Эти же команды могут быть выполнены с использованием кнопок  (Копировать), (Вырезать) и  (Вставить) на панели Буфер обмена ленты Главная или с помощью контекстного меню, вызываемого RM (правой клавишей манипулятора мышь). Операция вставки данных для выделенных ячеек из буфера обмена может выполняться неоднократно. При этом «бегущая» штриховая рамка вокруг выделенных ячеек остается видимой. Завершается операция вставки нажатием на клавишу <Enter>, после чего бегущая рамка исчезнет.

Переместить содержимое ячейки с помощью мыши можно следующим образом:

· выделить диапазон ячеек;

· поместить указатель мыши на границу выделенного диапазона (указатель мыши примет форму полой стрелки); 

· для перемещения нажать LM и переместить указатель мыши в нужную порцию рабочего листа.

Для копирования нужно выполнить те же действия при нажатой клавише <Ctrl>. Указатель мыши при этом будет дополнен знаком +.

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

При выполнении операций замещения и копирования содержимое копируемых ячеек замещает значение ячеек по месту вставки.

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

· выделить копируемую ячейку. При этом в правом нижнем углу выделенной ячейки появляется изображение квадратика (рис.18);

Рис.18. Пример копирования формулы перетаскиванием

· подвести указатель мыши к этому квадратику. При этом указатель мыши трансформируется в изображение крестика;

· нажать LM и «тащить» содержимое ячейки в соседние ячейки (например, на рис.4 в нижние ячейки).

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

Очистку проще всего выполнять следующим образом:

· выделить необходимый диапазон;

· нажать клавишу <DELETE>. При этом будет удалено только содержимое ячеек.

Для удаления вместе с содержимым самих ячеек необходимо выполнить для выделенных ячеек командуУдалить панели Ячейки ленты Главная.