Изменение адресов при копировании или перемещении формул

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

Пример 2: Исходная ячейка B2 с формулой =$C$2+$C3+C$4. При копировании ячейки формула изменится следующим образом:

На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Чтобы сослаться на диапазон ячеек (например, на группу смежных ячеек в строке), можно указать через двоеточие адреса начальной (первой) и конечной (последней) ячейки в диапазоне. Например, обозначение А7:Е7 адресует ячейки А, В, С, D, Е в строке 7 (т.е. весь диапазон находится в одной строке), обозначение В3:В6 адресует ячейки 3, 4, 5, 6 в столбце В (т.е. весь диапазон находится в одном столбце) и т.п.

Чтобы ввести ссылку на всю строку или столбец, нужно набрать номер строки или букву столбца дважды и разделить их двоеточием, например, А:А, 2:2.

Для обозначения адреса ячейки с указанием листа используются имя листа и восклицательный знак, например, Лист2!В5, Итоги!В5.

Для обозначения адреса ячейки с указанием книги используются квадратные скобки, например, [Книга1]Лист2!А1.

Диапазоном можно обозначить и прямоугольный блок ячеек. Ссылки (адрес) на блок ячеек состоит из адреса ячейки, находящейся в левом верхнем углу прямоугольного блока ячеек, двоеточия и адреса ячейки, находящейся в правом нижнем углу этого блока. Например, B2:D5 адресует блок, в котором адрес левой верхней ячейки равен B2 и адрес правой нижней ячейки – D5 (см. рис).

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

Типы и формат данных

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

Табличный процессор должен «знать», какого типа данные хранятся в конкретной ячейке таблицы, для того, чтобы правильно интерпретировать ее содержимое. Тип входных данных, содержащихся в каждой ячейке, определяется первым символом, который должен трактоваться не только как часть данных, но и как команда переключения режима.

Первые два типа данных (число и текст) считаются константами (постоянными величинами), то есть представляют собой информацию, которая не изменяется без вашего участия. Введя в ячейку электронной таблицы числа (например, 6; 7890; 3,8) или текст (например, «Налоги», «Начисления»), вы как бы «впечатываете» свои данные в ячейку и видите их (или хотя бы часть этих данных) на экране.

В чем же заключается отличие числа от текста? Есть ли необходимость разделять эти типы данных? Оказывается, есть. Число, в отличие от текста, может участвовать в вычислительных операциях. Так, например, можно число 6 умножить на число 3 и получить результат вычислений. Однако если вы попробуете из текста «Начисления» вычесть текст «Налоги», то получите сообщение об ошибке.

Число

Число в Excel может состоять только из следующих символов: цифры от 0 до 9 (0 1 2 3 4 5 6 7 8 9), знаки плюс (+) и минус (-), круглые скобки (), запятая (,) (используется в качестве десятичного разделителя), косая черта (/) (в рациональных дробях, например ½), знак процента (%), точка (.), знак мантиссы (Е е). Все другие комбинации, состоящий из цифр и нецифровых символов, интерпретируются как текст.

Первыми символами в ячейке с числовыми данными могут быть: число, знак числа (плюс или минус) и левая круглая скобка. Знак плюс, стоящий перед числом, игнорируется. Перед отрицательным числом необходимо ввести знак минус или заключить его в круглые скобки. В качестве десятичного разделителя в России используют запятую.

По умолчанию числа выравниваются в ячейке по правому краю. Это объясняется тем, что при размещении чисел друг под другом (в столбце таблицы) удобно иметь выравнивание по разрядам (единицы под единицами, десятки под десятками и т.д.). При выравнивании чисел в ячейке незначащие («хвостовые») нули в дробной части числа отбрасываются. Например, число 25,10 превратится в 25,1.

Большие и малые (по модулю) числа удобно записывать в экспоненциальной форме, т.е. в виде условного сочетания мантиссы и порядка. Например, число 4890000 можно представить как произведение 4,89х10⁶ (4,89 – мантисса, 6 – порядок) и записать так: 4,89Е+06.

В Excel установлено ограничение для используемых числовых данных – 15 разрядов, т.е. числа хранятся с точностью до 15 знака независимо от количества разрядов, отображаемых на экране. Если число имеет больше 15 знаков, то разряды после 15-го преобразуются в нули.

Текст

Текстом в Excel является любая последовательность, состоящая из цифр, пробелов и нецифровых символов (буквы и специальные символы) – например, следующие записи (987$$78, 100 рублей) обрабатываются как текст. В качестве первого символа для обозначения текстовых данных иногда используется апостроф (`), кавычки (“) или пробел. Текст (текстовые данные, символьные данные) имеет описательный характер и не подвергается каким-либо преобразованиям. Общая длина вводимого в ячейку текста не может превышать 32767 символов. При необходимости можно расположить текст в ячейке в несколько строк. По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).

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

1) следующие ячейки в строке пусты, - тогда визуально текст накроет эти ячейки;

2) в следующих ячейках есть данные, - тогда правая часть текста скроется за этими ячейками.

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

Формулы

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

Например, ввод формулы =А1+А2 означает, что нужно сложить содержимое ячеек А1 и А2 и вывести результат в активной ячейке. По умолчанию Excel вычисляет формулы при их вводе и автоматически пересчитывает формулы при каждом изменении входящих в них исходных данных.

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

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

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

Для выполнения вычислений с помощью формул и функций в Excel имеются четыре вида операторов:

· арифметические операторы (сложение +, вычитание -, умножение *, деление /, процент %, возведение в степень ^) используются для выполнения арифметических операций над операндами. Например, =А1+В3, =400-С4, =А3*G13, =D3/G7, =10%, =C6^2;

· операторы сравнения (равно =, меньше <, больше >, меньше или равно <=, больше или равно >=, не равно <>) используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ). Например, =F5=G5, =F5<G5, =F5>G5, =F5<=G5, =F5>=G5, =F5<>G5;

· операторы ссылок (диапазон: (используется для формирования ссылок на диапазоны ячеек) и объединение; (объединяет несколько ссылок в одну ссылку)) используются для формирования ссылок на диапазоны ячеек или на несколько отдельных ячеек. Например, =СУММ(В5:В10), =СУММ(В5;С6;D7);

· текстовый оператор конкатенации & (амперсанд) применяется для объединения нескольких последовательностей символов в одну строку, т.е. для объединения содержимого ячеек в одну текстовую строку. Например, =А1&В5.

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

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

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

Общее количество символов в формуле не может быть больше 1024. Строка аргумента может содержать не более 255 символов.

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

Excel содержит более 300 встроенных функций, условно разделенных на несколько категорий: Математические, Статистические, Финансовые, Логические, Текстовые и др. В русифицированных версиях Excel имена многих функций записываются на русском языке. Имена функций лучше набирать строчными буквами. После завершения ввода имен правильно введенных функций строчные буквы автоматически преобразуются в прописные, что служит критерием правильности ввода функции.

Математические функции выполняют различные математические операции, например, суммирование чисел (СУММ), вычисление логарифмов (LOG, LN), тригонометрических функций (SIN, COS), преобразование радиан в градусы (ГРАДУСЫ) и т.п.

Статистические функции выполняют операции по вычислению параметров случайных величин или их распределений, представленных множеством чисел, например, среднего значения (СРЗНАЧ), стандартного отклонения (СТАНДОТКЛОН) и т.п.

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

Логические функции (всего 6 функций – ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ) используются для построения логических выражений, результат которых зависит от истинности проверяемого условия. Логические выражения строятся с помощью логических функций и операторов сравнения (равно =, меньше <, больше >, меньше или равно <=, больше или равно >=, не равно <>). Например, =ЕСЛИ(В4<100,100,200) расшифровывается так: если ячейка В4 содержит число меньше 100, то функции присваивается значение 100, если же условие не выполняется (В4 больше или равно 100), то функции присваивается значение 200.

Текстовые функции относятся к функциям, которые не возвращают значение, а выполняют операции над текстовыми строками или последовательностью символов, вычисляя длину строки (ДЛСТР), преобразовывая заглавные буквы в строчные (СТРОЧН), позволяют соединять (сцеплять) две строки (СЦЕПИТЬ) и т.п. К текстовым данным нельзя применять операции умножения и деления.

Для облегчения работы с встроенными функциями используется Мастер функций. Мастер – инструмент, позволяющий выполнить требуемое действие по шагам с уточнением параметров по каждому шагу. Для вызова Мастера функций можно использовать команду горизонтального меню окна Excel «ВСТАВКА» -> «Функция», кнопку «Вставка функции» на панели инструментов и комбинацию клавиш Shift+F3.

Рассмотрим некоторые функции.

Логическая функция ЕСЛИ

Итак, первая функция – это логическая функция ЕСЛИ. Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Эта функция записывается следующим образом:

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь).

· Лог_выражение (логическое выражение) - условие, которое требуется проверить. Что это такое? Это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100— это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае— ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

· Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент— строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

· Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает полученное значение. Например, в ячейке набираем формулу =ЕСЛИ(A2<=100;"Внутри бюджета";"Вне бюджета"). Что это означает? Если число, находящееся в ячейке А2, меньше либо равно числу 100, формула отображает строку «Внутри бюджета». В противном случае — строку «Вне бюджета»

Логическая функция И

Следующая функция – тоже логическая – функция И. Эта функция возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Записывается следующим образом:

И (логическое_значение1; логическое_значение2;...)

· Логическое_значение1, логическое_значение2,... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Например, пусть в ячейке А2 находится число 50, тогда формула с использованием функции И будет такая: =И(1<A2; A2<100) (Проверяем условие: наше число, записанное в ячейке А2, должно быть больше 1 и меньше 100. Т.к. 50 больше 1 и меньше 100, то функция И возвращает значение ИСТИНА). Функцию И можно использовать вместе с функцией ЕСЛИ. Например, пусть в ячейке А2 находится число 50, тогда формула может быть такой: =ЕСЛИ(И(1<A2; A2<100); A2; "Значение вне интервала."). (Если число, находящееся в ячейке А2 больше 1 и меньше 100, то в ячейке, в которой набрана формула, отобразится наше число, если проверяемое условие ложно, то отобразится сообщение «Значение вне интервала»).

Логическая функция ИЛИ

Функция ИЛИ (логическая) – возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Эта функция записывается следующим образом:

ИЛИ (логическое_значение1; логическое_значение2;...)

· Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Текстовая функция ПРАВСИМВ

Текстовая функция ПРАВСИМВ возвращает заданное число последних знаков текстовой строки. Записывается в таком виде:

ПРАВСИМВ (текст;число_знаков)

· Текст — это текстовая строка, содержащая извлекаемые знаки.

· Число_знаков — количество знаков, извлекаемых функцией ПРАВСИМВ. Число_знаков должно быть больше либо равно нулю. Если число_знаков больше длины текста, то функция ПРАВСИМВ возвращает весь текст. Если число_знаков опущено, то предполагается, что оно равно 1.

Пример: в ячейке А2 находится текст «Цена продаж». Формула =ПРАВСИМВ(A2;6) возвращает 6 знаков текста с конца (справа), находящегося в ячейке А2, т.е. слово «продаж».