Копирование и перемещение формул
После ввода формулы в ячейку, ее можно скопировать или перенести в другую ячейку или интервал ячеек.
При этом следует иметь в виду следующее:
§ при перемещении формулы на новое место ссылки в формуле не изменяются;
§ при копировании формулы абсолютные ссылки не изменяются, относительные ссылки изменяются, настраиваясь на новое местоположение формулы, в смешанной ссылке, в общем случае, изменяется тот компонент ссылки, перед которым нет символа доллара.
Для перемещения и копирования формул, естественно, могут быть использованы описанные выше методыперемещения и копирования ячеек и интервалов ячеек.
Следует заметить, что для формул операция копирования является весьма эффективной (в смысле трудозатрат на решение задачи), если для ее реализации использовать средство Автозаполнитель. Ниже в указаниях к лабораторным работам будет показано применение этого средства для копирования формул, применяемого с целью решения задач.
Ошибки в формулах
В том случае, когда формула не может быть вычислена, Excel выводит в ячейке значение ошибки. Ниже представлены ошибки и возможные причины их возникновения.
Код ошибки "########"
Возможные причины и способы их устранения:
1. Ошибка появляется в том случае, когда вводимое числовое значение или результат выполнения формулы не помещаются в ячейке.
Для ее устранения необходимо увеличить ширину столбца или изменить формат числа ячейки.
2. ·Ошибка может появиться при определении числа дней между двумя датами, а также при определении количества часов между двумя временными промежутками. Например, формула, содержащая разность двух дат выработала отрицательное значение, а так как формат ячейки, в которой содержится формула, имеет формат даты (устанавливается в этом случае автоматически), то Excel не может отобразить результат в виде даты.
Для ее устранения необходимо проверить правильность ввода соответствующей формулы, а также выбрать числовой формат ячейки отличный от формата даты или времени.
Код ошибки "#ДЕЛ/0"
Эта ошибка возникает в случае, когда в формуле делается попытка деления на нуль.
Возможные причины и способы их устранения:
1. Ошибка появляется в том случае, когда в формуле в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение (если операнд является пустой ячейкой, то ее содержимое интерпретируется как нуль).
Для ее устранения необходимо изменить ссылку или введите ненулевое значение в ячейку, используемую в качестве делителя. Кроме того, в качестве делителя можно ввести значение #Н/Д. В этом случае ошибка #ДЕЛ/0! сменится на #Н/Д, указывающая, что значение делителя не определено.
2. В формуле содержится явное деление на ноль (например, =5/(4-4); В1/(А1-А1) и т. д.).
Для ее устранения необходимо проверить правильность подготовки формулы.
Код ошибки "#Н/Д"
Обозначение этой ошибки является сокращением термина "Неопределенные Данные". Этот вид ошибки помогает предотвратить использование ссылки на пустую ячейку. Если ячейки должны содержать данные, но в настоящий момент они отсутствуют, то рекомендуется ввести в эти ячейки значение #Н/Д. Формулы, на них ссылающиеся, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления.
Возможные причины и способы их устранения:
1. Для функций ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР задан недопустимый аргумент — "искомое_значение".
Для ее устранения требуется задать правильно аргумент "искомое_значение", например, значение или ссылку, но не диапазон ссылок.
2. Используются функции ВПР или ГПР, просматривающие значение в несортированной таблице. По умолчанию для функций просмотра таблиц сведения должны располагаться в возрастающем порядке.
Функции ВПР и ГПР содержат аргумент "интервальный_просмотр", позволяющий искать определенное значение в несортированной таблице, который в этом случае должен иметь значение ЛОЖЬ.
3. В формуле массива используется аргумент не соответствующий диапазону, в который она введена. Например, формула массива введена в интервал C1:C15, а в самой формуле имеется ссылка на интервал A1:A10. В этом случае в интервале ячеек C11:C15 отобразиться ошибка #Н/Д.
Чтобы исправить эту ошибку, необходимо уменьшить диапазон формулы до интервала C1:C10 или увеличить диапазон, на который ссылается формула, до интервала A1:A15.
4. Не определены все аргументов встроенной или пользовательской функции.
Требуется задать все необходимые аргументы функции.
Код ошибки "#ИМЯ?"
Эта ошибка появляется тогда, когда Excel не может распознать имя, используемое в формуле.
Возможные причины и способы их устранения:
1. Используемое имя было удалено или не было определено.
Для ее устранения необходимо определить соответствующее имя.
2. Имеется ошибка в написании имени.
Требуется исправить имя, для чего необходимо выделите имя в строке формул, а затем осуществить последовательность действий: меню Вставка Þ команда ИмяÞ параметр Вставить. На экране появится диалоговое окно Вставка имени. Выберите нужное имя и нажмите кнопку OK.
3. Имеется ошибка в написании имени функции.
Требуется вставить в формулу правильное имя функции при помощи панели формул.
4. В формулу введен текст, не заключенный в двойные кавычки. Excel пытается интерпретировать как имя.
Требуется заключить текст формулы в двойные кавычки. Например, правильное объединение текстового фрагмента "Итого:" со значением ячейки А15задается следующей формулой:="Итого: "&А15.
5. В ссылке на интервал ячеек пропущен знак ":" (двоеточие).
Требуется исправить формулу так, чтобы во всех ссылках на интервал ячеек использовался знак двоеточия (:).
Код ошибки "#ПУСТО!"
Эта ошибка появляется тогда, когда задано пересечение двух областей, не имеющих в действительности общих ячеек.
Возможные причины и способы их устранения:
1. Использован ошибочный оператор пересечения диапазонов или ошибочная ссылка на ячейку.
Для создания ссылки на две непересекающиеся области, используется оператор объединения, обозначаемый запятой ",". Например, формула должна суммировать два интервала ячеек В1:В10и D1:D10, т.е. должна иметь вид =(СУММ(В1:В10,D1:D10)).Однако, если вместо запятой ошибочно введен пробел, то Excel будет пытаться суммировать общие ячейки указанных интервалов, которые на самом деле их не имеют. Для устранения ошибки требуется проверить правильность ввода ссылок на интервалы ячеек.
Код ошибки "#ЧИСЛО!"
Эта ошибка появляется тогда, когда возникают проблемы при использовании чисел в формуле или функции.
Возможные причины и способы их устранения:
1. В функции с аргументом, требующим числовой тип данных, используется другой тип данных.
Требуется проверить правильность использования типов данных для аргументов функции.
2. Использована функция, при вычислении которой применяется итерация, которая не сходится и, поэтому результат не может быть получен. Например, финансовые функции ВНДОХ использует метод итераций для вычисления внутренней нормы дохода инвестициям. Начиная со значения, задаваемого параметром "прогноз" (начальное приближение), функция ВНДОХ выполняет циклические вычисления, пока не получит результат с точностью 0,00001 процента. Если функция ВНДОХ не может получить результат после 20 попыток, то возвращается значение ошибки #ЧИСЛО!
Для устранения этой ошибки необходимо попробовать использовать другое начальное приближение для этой функции.
3. Введена формула, возвращающая числовое значение, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.
Требуется изменить формулу так, чтобы в результате ее вычисления получалось число, попадающее в следующий диапазон: -1 10307 и 1 10307.
4. Значения параметров введенной формулы не входят в область ее определения. Например, формула содержит корень четной степени и значения параметров таковы, что под корнем образуется отрицательное число. Аналогичная ошибка может возникать при использовании в формуле логарифмических, обратных тригонометрических функций, имеющих определенную область определения.
Для устранения подобных ошибок правильным будет включение данной формулы в качестве аргумента в функцию ЕСЛИ. Например, формула имеет вид: =КОРЕНЬ(А1-В1). В том случае, если значение, находящееся в ячейке В1 превосходит значение, введенное в ячейку А1, возникает ошибка "#ЧИСЛО!". Построим формулу с использованием функции ЕСЛИ:
=ЕСЛИ(А1-В1>0; КОРЕНЬ(А1-В1);"Н/О"), где Н/О означает, что формула для данного сочетания аргументов не определена.
Код ошибки "#ССЫЛКА!"
Эта ошибка возникает тогда, когда используется недопустимая ссылка на ячейку.
Возможные причины и способы их устранения:
1.В формуле имеются ссылки на ячейки, которые были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.
Требуется измените формулу или восстановите прежнее содержимое ячеек с помощью кнопки Отменить панели инструментов Стандартная.
2.Используется макрос, вызывающий макрофункцию, аргументы которой ссылаются на недопустимые ячейки или диапазоны ячеек. Например, если макрос вызывает функцию из ячейки, расположенной выше первой строки, то отобразится ошибка #ССЫЛКА!, поскольку такая ячейка не существует.
Требуется проверить аргументы функции.
3.Использована удаленная ссылка на не активное в настоящий момент приложение или на недоступный раздел DDE (Dynamic Data Exchange).
Требуется запустить нужное приложение или проверить используется ли правильный раздел DDE.
Код ошибки "#ЗНАЧ!"
Эта ошибка возникает тогда, когда используется недопустимый тип аргумента или операнда, а также, если средствами автоисправления формулы не представляется возможным исправить формулу.
Возможные причины и способы их устранения:
1. Вместо числового или логического (ИСТИНА или ЛОЖЬ) значения введен текст, и Excel не может преобразовать его к нужному типу данных.
Требуется проверить правильность задания типов операндов или аргументов в функции или формуле, а также значения ячеек, на которые ссылается формула. Например, ячейка A6 содержит число, а в ячейке A7 содержит текст "Всего", тогда формула =A5+A6 вернет ошибку #ЗНАЧ!. Чтобы сложить несколько значений из интервала ячеек, среди которых имеются как числовые, так и текстовые значения и при этом, не затрудняя себя выбором ячеек с числовыми значениями, рекомендуется использовать в формуле функцию СУММ (функция СУММ игнорирует текстовые значения).
2. После ввода или редактирования формулы массива нажимается клавиша ENTER.
Для редактирования формулы массива требуется указать ячейку или интервал ячеек, содержащих формулу массива, нажатье клавишу F2, затем отредактировать формулу и нажать сочетание клавиш Ctrl+Shift+Enter.
3. Ссылка, формула или функция указана как массив констант.
Требуется проверить, что ссылка, формула или функция не используются как массив констант.
4. Для оператора или функции, требующей одного значения, возвращается интервал.
Требуется осуществить необходимые изменения, чтобы интервала возвращалось одно значение или изменить интервал таким образом, чтобы в него попадала строка или столбец с формулой.
5. Использована неправильная матрица в одной из матричных функций листа.
Требуется убедиться в правильном указании размерности матрицы.
6. Используется макрос, вызывающий макрофункцию, которая возвращает в определенных случаях значение #ЗНАЧ!.
Требуется найти описание используемой функции и проверить условия возврата значения #ЗНАЧ!.
Лабораторный практикум
Лабораторная работа №8