Распространенные ошибки при создании формул

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

Рекомендации	Дополнительные сведения
Проверьте согласованность открывающих и закрывающих скобок	Убедитесь, что каждая скобка имеет соответствующую открывающую и закрывающую скобку. При создании формулы Excel отображает вводимые скобки цветом.
Используйте двоеточие для указания диапазона	При указании ссылки на диапазон ячеек в качестве разделителя между первой и последней ячейками диапазона используйте двоеточие (:). Например: A1:A5.
Введите все необходимые аргументы	Некоторые функции имеют обязательные аргументы. Кроме того, убедитесь, что отсутствуют лишние аргументы.
Допускается вложение не более 64 функций	Число уровней вложения функций в пределах одной функции не должно превышать 64.
Имена других листов следует заключать в апострофы	Если формула содержит ссылки на значения ячеек на других листах или в других книгах, а имя другой книги или листа содержит небуквенные символы, его необходимо заключить в одиночные кавычки (').
Включите путь к внешним книгам	Убедитесь, что каждая внешняя ссылка (Внешняя ссылка. Ссылка на ячейку или диапазон ячеек в другой книге Microsoft Excel или ссылка на имя, определенное в другой книге.) содержит имя книги и путь к этой книге.
Вводите числа без форматирования	Не задавайте формат для чисел, вводимых в формулы. Например, даже если требуется ввести значение 1 000 р., введите в формулу 1000

Совет. Чтобы отменить выделение, щелкните любую ячейку листа.

· Во время ввода формул следует руководствоваться предложенными ниже рекомендациями. Каждая формула должна начинаться со знака равенства (=). Если первым символом в ячейке является буква или цифра. Excel интерпретирует данные как текстовое или числовое значение соответственно.

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

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

· Помимо ссылок, в формулы можно явно вводить числовые значения. Предположим, что в ячейку С5 введена стоимость некоторого товара без учета налога на добавленную стоимость (НДС). Для получения окончательной стоимости товара с учетом НДС (составляет 20%) необходимо в другой ячейке ввести формулу =С5*1,2. Для Excel эта формула не что иное, как руководство к действию, а именно: значение ячейки С5 необходимо умножить на 1,2.

· Excel автоматически обновляет результат вычисления формулы сразу после изменения значения в ячейке, на которую в формуле сделана ссылка. Например, если после ввода формулы =В4+В5+В6+В7 вы измените значение в ячейке В5, программа автоматически отобразит обновленный результат в той ячейке, в которой данная формула была введена.

· В формулах можно использовать следующие символы, которые называются арифметическими операторами: + (сложение), - (вычитание), * (умножение), / (деление), Л (возведение в степень), % (процентное отношение).

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

Приоритет операторов

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

1. Все операторы, заключенные в круглые скобки.

2. Возведение в степень.

3. Умножение и деление.

4. Сложение и вычитание.

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

Ввод формул

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

Для начала рассмотрим наиболее надежный и вместе с тем простой способ ввода формулы — с помощью мыши.

1. Активизируйте ячейку, в которой должна находиться формула, а следовательно, отображаться ее результат.

2. Введите с клавиатуры знак равенства (=).

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

4. Введите нужный арифметический оператор. Например, введите знак сложения (+), если к значению выбранной ячейки необходимо прибавить содержимое другой ячейки; либо введите знаки вычитания (-), умножения (*) или деления (/).

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

6. В заключение после ввода формулы нажмите клавишу <Enter>

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

Отображение формул

Время от времени у каждого пользователя программы Excel возникает необходимость в проверке всех формул, введенных на рабочем листе. Но в ячейке с формулой отображается не сама формула, а результат ее вычисления. Специально для таких случаев в Excel предусмотрен режим отображения формул. Чтобы активизировать этот режим, воспользуйтесь комбинацией клавиш <Ctrl+>. В данном режиме Excel не только отображает формулы, но и выделяет ячейки, на которые в этих формулах сделаны ссылки (рис. 13). Чтобы вернуться в обычный режим отображения данных рабочего листа, еще раз нажмите клавиши <Ctrl+>.
Обратите внимание: в обычном режиме работы содержимое текущей ячейки всегда отображается в строке формул, которая расположена под лентой инструментов (в верхней части окна программы).

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

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