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

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

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

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

Для того чтобы изменить тип ссылки — относительную сделать абсолютной (или наоборот), переместите курсор на любой адрес ячейки, используемый в формуле, и нажмите клавишу <F4>. В результате перед буквой столбца и номером строки (в адресе ячейки) появится символ доллара ($). В принципе символ доллара можно вводить и вручную, но в большинстве случаев использование клавиши <F4> значительно упрощает эту процедуру.

Помимо относительных и абсолютных, в Excel предусмотрены смешанные ссылки. Суть ссылки такого типа напрямую связана с ее названием. В этом случае абсолютную ссылку создают только на столбец, а на строку делают относительную ссылку (или наоборот). После копирования формулы со смешанной ссылкой «нению подлежит либо только буква столбца, либо только номер строки. Например, $В4 (буква столбца не меняется во время копирования формулы) или В$4 (во время копирования формулы номер строки остается неизменным).

Для того чтобы изменить тип только что введенной ссылки, несколько раз нажмите клавишу <F4> (либо введите в нужной части адреса ячейки символ доллара ($) вручную).

Сообщения об ошибках

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

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

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

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

· Зависимые ячейки. Показывает ячейку с формулой, в которой сделана ссылка на текущую ячейку.

· Источник ошибки. Указывает на ячейки, значения которых могут служить вероятной причиной появления ошибки в формуле.

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

1. Активизируйте ячейку с «проблемной» формулой (либо одну из ячеек, на которую в данной формуле или функции сделана ссылка).

2. Перейдите во вкладку <>bФормулы ленты инструментов.

3. Щелкните на кнопке Зависимости формул. На экране появится палитра с дополнительными инструментами.

4. Щелкните на кнопках Влияющие ячейки, Зависимые ячейки В результате на экране появятся стрелки, указывающие на ячейки, на которые сделаны ссылки в формуле.

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

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

7. Чтобы после устранения ошибок убрать все стрелки, которые использовались в процессе проверки, щелкните на кнопке Зависимости формул и в дополнительной панели — на кнопке Убрать стрелки.

Автовычисления

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

Ввод данных

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

Общие правила ввода данных

1. Выделите ячейку.
2. Введите данные с клавиатуры непосредственно в ячейку или в строку формул.
3. Подтвердите ввод. Подтвердить ввод можно одним из трех способов: нажать клавишу Enter или Tab; нажать кнопку Ввод (галочка) в строке формул; выделить любую другую ячейку на листе (нельзя использовать при вводе формул).

При вводе неправильного символа его можно удалить. Для удаления символа слева от текстового курсора надо нажать клавишу Back Space.

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

Для отказа от ввода данных в ячейку следует нажать клавишу Esc или кнопку Отмена (крестик) в строке формул

В ячейке может находиться до 32767 символов. Исключение составляют формулы. Длина записи для формулы – 8192 символа.

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

При вводе текста нельзя расставлять переносы в словах с использованием клавиши дефис (-). Это может затруднить последующую работу с данными (сортировки, выборки и т. п.).

Числовые данные – это целые или вещественные числа. Дробная часть должна отделятся от целой части запятой. Удобнее всего набирать числа на малой цифровой клавиатуре (при включенном индикаторе Num Lock) – точка этой части клавиатуры соответствует десятичной запятой. Числа автоматически выравниваются по правому краю ячейки.

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

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

Наибольшее число, которое можно ввести в ячейку составляет 9,99999999999999*10³⁰⁷. Точность представления чисел – 15 разрядов (значащих цифр).

Можно вводить числа с простыми дробями. При вводе с клавиатуры простую дробь от целой части числа отделяют пробелом. В строке формул простая дробь отображается как десятичная .Для удобства представления больших чисел группы разрядов при вводе можно отделять пробелами. Например, число 12345678 можно ввести как 12 345 678. В строке формул при этом число будет отображаться без пробелов между разрядами

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

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

Числовые данные имеют несколько форматов, в том числе и форматы Дата и Время.

Microsoft Excel воспринимает даты начиная с 1 января 1900 года. Даты до 1 января 1900 года воспринимаются как текст. Наибольшая возможная дата – 31 декабря 9999 года.

Произвольную дату следует вводить в таком порядке: число месяца, месяц, год. В качестве разделителей можно использовать точку (.), дефис (-), дробь (/). При этом все данные вводятся в числовом виде. Точка в конце не ставится. Например, для ввода даты 12 августа 1918 года с клавиатуры в ячейку следует ввести: 12.8.1918 или 12-8-1918 или 12/8/1918.

При вводе года можно ограничиться двумя цифрами, если речь идет о датах с 1 января 1930 года по 31 декабря 2029 года. Например, для ввода даты 12 апреля 1961 года с клавиатуры в ячейку достаточно ввести:12.4.61 или 12-4-61 или 12/4/61.

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

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

Текущую дату можно ввести комбинацией клавиш Ctrl + Shift + 4.

Независимо от способа ввода и последующего форматирования дата в строке формул всегда отображается в полном формате: ДД.ММ.ГГГГ.

Чтобы ввести текущее время, можно нажать комбинацию клавиш Ctrl + Shift + 6.

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

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

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

Замена содержимого

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

Следует иметь в виду, что при вводе некоторых типов числовых данных (даты, время, числа с разделителями разрядов, проценты и др.) автоматически устанавливается формат данных в ячейке. Ввод новых числовых данных вместо существовавших может привести к их неправильному отображению. Так, если в ячейке была ранее введена дата, то после ввода обычного числа Microsoft Excel преобразует его в дату. Например, число 178 будет отображено как дата 26.06.1900. В этом случае следует очистить формат ячейки.

Редактирование содержимого

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

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

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