Корректировка формулы введенной в ячейку программы excel

Перейти к содержанию

1. строка
   формул в Microsoft Excel …

1)
отображает наименование открытого
листа

2)
воспроизводит имя обрабатываемого
файла

3)
показывает адрес ячейки, обозначенной
курсором и данные в ней

4)
содержит служебную информацию о таблице

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

Правильный
ответ: 3.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 336.

1. СТРОКА
   ФОРМУЛ В ОКНЕ ПРОГРАММЫ EXCEL, КАК ПРАВИЛО,
   РАЗМЕЩАЕТСЯ …

1)
в верхней части, ниже стандартной панели
инструментов

2)
справа, рядом с полосой прокрутки

3)
внизу, над строкой состояния

4)
слева, рядом с границей окна

5)
в произвольном положении как отдельное
окно

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

Правильный
ответ: 1.

Литература:
Симонович С.В. Информатика. Базовый
курс: Учебник для вузов. — 3-е изд. Стандарт
третьего поколения. — СПб.: Питер, 2011. —
640с.: ил. – стр. 338.

1. СТРОКА
   ФОРМУЛ ПРОГРАММЫ EXCEL СЛУЖИТ ДЛЯ …

1)
ввода команд, управления программой

2)
ввода и редактирования текстовых данных,
чисел и формул в ячейках таблицы

3)
сохранения таблицы в файле электронного
документа

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

Правильный
ответ: 2.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. СТРОКА
   ФОРМУЛ ПРОГРАММЫ EXCEL ПОКАЗЫВАЕТ:

1)
адрес ячейки, обозначенной табличным
курсором

2)
имя обрабатываемого файла

3)
наименование открытого листа

4)
данные и формулы, расположенные в
активной ячейке

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

Правильный
ответ: 1, 4.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. Ввод
   формулы в ячейку таблицы EXCEL начинается
   с …

1)
знака «=» (знака равенства)

2)
указания адреса ячейки

3)
выполнения команды «Формат»

4)
сохранения документа

Решение:
в программе
Excel ввод формулы в ячейку таблицы
начинается с «=» (знака равенства).

Правильный
ответ: 1.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 336.

1. ФОРМУЛА
   ВЫЧИСЛЕНИЯ В ПРОГРАММЕ EXCEL всЕгда
   НАЧИНАЕТСЯ С СИМВОЛА …

1)
любого алфавитного знака

2)
«’» (апостроф)

3)
«~» (тильда)

4)
«=» (равно)

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

Правильный
ответ: 4.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. КОРРЕКТИРОВКА
   ФОРМУЛЫ, ВВЕДЕННОЙ В ЯЧЕЙКУ ПРОГРАММЫ
   EXCEL:  

1)
не возможна

2)
выполняется в ячейке таблицы

3)
происходит в отдельном диалоговом окне

4)
выполняется в строке формул 

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

Правильный
ответ: 2, 4.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. в
   программе Excel Адрес ячейки $A$1 является…

1)
смешанным

2)
абсолютным

3)
пользовательским

4)
относительным

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

Правильный
ответ: 2.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 339.

1. ИЗМЕНЕНИЕ
   ТИПА АДРЕСА ЯЧЕЙКИ В1 В ФОРМУЛЕ
   (СМ. РИСУНОК) НА АБСОЛЮТНЫЙ ВЫПОЛНЯЕТСЯ
   С ПОМОЩЬЮ …

1)
ввода с клавиатуры  #В#1

2)
ввода с клавиатуры  $В$1

3)
ввода с клавиатуры &В&1

4)
ввода с клавиатуры &В$1

Решение:
строка
формул, которая расположена в верхней
части окна ниже панели инструментов,
предназначенная для ввода и редактирования
в ячейках таблицы текстовых данных,
чисел и формул. Данные, внесенные в
активную ячейку, дополнительно
отображаются в этой строке. Изменение
типа адреса ячейки B1
в формуле (см.рисунок) на абсолютный
выполняется в строке формул с помощью
ввода с клавиатуры  $В$1.

Правильный
ответ: 2.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. ФОРМУЛА
   ВЫЧИСЛЕНИЙ В ЯЧЕЙКЕ ТАБЛИЦЫ EXCEL МОЖЕТ
   ВКЛЮЧАТЬ …

1)
знак «=» (равно), а затем переменные
(адреса ячеек), константы и функции

2)
только адреса ячеек, из которых извлекается
число для вычислений

3)
текстовое описание расчета

4)
только функции вычислений

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

Правильный
ответ: 1.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. ВВОД
   ФОРМУЛЫ ВЫЧИСЛЕНИЙ В ЯЧЕЙКУ ТАБЛИЦЫ
   EXCEL ВЫПОЛНЯЕТСЯ СЛЕДУЮЩИМИ СПОСОБАМИ:

1)
набрать на клавиатуре знак «=» (равно),
а затем ввести расчетную часть формулы

2)
ввести текст описания расчета и указать
адреса ячеек, используемых в вычислениях

3)
выполнить команду «Вставить функцию»
и выбрать способ вычисления из «Мастера
функций»

Решение:
формула вычислений в ячейке таблицы
Excel может быть вставлена набором с
клавиатуры знака равно (=), а затем вводом
адресов ячеек, констант и функций.
Дополнительным способом использования
формул является применение «Мастера
функций», который позволяет выбрать
требуемый алгоритм вычислений из списка
функций.

Правильный
ответ: 1, 3.

Литература:
Сабанов В.И. Медицинская информатика и
автоматизированные системы управления
в здравоохранении. Учебно-методическое
пособие к практическим занятиям / В.И.
Сабанов, А.Н. Голубев, Е.Р. Комина. –
Волгоград: Изд-во ВолГМУ, 2006.– стр. 55.

1. в
   программе excel вычисление суммы значений
   в диапазоне ячеек выполняется функцией
   …

1)
СУММ()

2)
МАКС()

3)
ДИСП()

4)
СЧЕТ()

Решение:
в программе
Excel сумма значений в диапазоне ячеек
может быть вычислена функцией СУММ().

Правильный
ответ: 1.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 345.

1. в
   программе excel
   Выбор функции из списка выполняется
   с помощью…

1)
создания новой книги

2)
мастера функций

3)
надстроек

4)
справочной системы

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

Правильный
ответ: 2.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 341.

1. В
   Представленном фрагменте электронной
   таблицы EXCEL
   при использовании указанной формулы
   (см. рисуноК) Значение в ячейке B3 будет
   равно…

1)
5

2)
12

3)
7

4)
3

Решение:
в программе
Excel функция МАКС служит для вычисления
максимального значения в диапазоне
ячеек, указанных в скобках и перечисленных
через точку с запятой, поэтому в ячейке
B3
будет вычислено значение 7.

Правильный
ответ: 3.

Литература: Симонович
С.В. Информатика. Базовый курс: Учебник
для вузов. — 3-е изд. Стандарт третьего
поколения. — СПб.: Питер, 2011. — 640с.: ил. –
стр. 345.

1. если
   формула вычисления в программе Excel
   содержит ошибку, то сообщение о ней
   отображается в …

1)
строке формул

2)
панели инструментов

3)
ярлыке листа

4)
ячейке таблицы

Решение:
в случае
ввода в ячейку таблицы формулы с ошибкой,
программа Excel сообщит о ней в ячейке
таблицы.

Правильный
ответ: 4.

Литература: Степанов
А.Н. Информатика: Учебник для вузов. 6-е
изд.- СПб.: Питер, 2010. – 720с.: ил. – стр.
537.

1. в
   программе EXCEL
   Причиной появления ошибки #ЗНАЧ! ПРИ
   вводе формулы является …

1)
использование недопустимого типа
данных, расположенных в ячейке, адрес
которой указан в формуле

2)
числовое значение не умещается по ширине
ячейки

3)
в формуле указан несуществующий или
неправильный адрес ячейки

4)
формула содержит вычисления включающие
деление на ноль

Решение:
если при вводе формулы в программе Excel
в ячейке отображается ошибка
#ЗНАЧ!, это
указывает на использование недопустимого
типа данных в формуле, например:
используется адрес текстовой ячейки.
Для исправления ошибки требуется
изменение формулы или корректировка
данных в ячейке, адрес которой используется
в формуле.

Правильный
ответ: 1.

Литература: Степанов
А.Н. Информатика: Учебник для вузов. 6-е
изд.- СПб.: Питер, 2010. – 720с.: ил. – стр.
538.

1. в
   программе EXCEL
   ПричиноЙ появления в ячейке знаков
   решетки (####) является …

1)
использование недопустимого типа
данных, расположенных в ячейке, адрес
которой указан в формуле

2)
числовое значение не умещается по ширине
ячейки

3)
в формуле указан несуществующий или
неправильный адрес ячейки

4)
формула содержит вычисления включающие
деление на ноль

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

Правильный
ответ: 2.

Литература: Степанов
А.Н. Информатика: Учебник для вузов. 6-е
изд.- СПб.: Питер, 2010. – 720с.: ил. – стр.
538.

1. в
   программе EXCEL
   Причиной появления ошибки #ИМЯ? является
   …

1)
использование недопустимого типа
данных, расположенных в ячейке, адрес
которой указан в формуле

2)
числовое значение не умещается по ширине
ячейки

3)
в формуле указан несуществующий или
неправильный адрес ячейки

4)
формула содержит вычисления включающие
деление на ноль

Решение:
если при вводе формулы в программе Excel
в ячейке отображается ошибка
#ИМЯ?, это
указывает на использование несуществующего
или неправильно указанного адреса
ячейки. Для исправления ошибки необходимо
проверить правильность обозначения
адресов ячеек в формуле.

Правильный
ответ: 3.

Литература: Степанов
А.Н. Информатика: Учебник для вузов. 6-е
изд.- СПб.: Питер, 2010. – 720с.: ил. – стр.
538.

1. в
   программе EXCEL
   Причиной появления ошибки #ДЕЛ/0!
   является …

1)
использование недопустимого типа
данных, расположенных в ячейке, адрес
которой указан в формуле

2)
числовое значение не умещается по ширине
ячейки

3)
в формуле указан несуществующий или
неправильный адрес ячейки

4)
формула содержит вычисления включающие
деление на ноль

Решение:
если при вводе формулы в программе Excel
в ячейке отображается ошибка
#ДЕЛ/0, это
указывает на попытку деления на ноль.
Для исправления ошибки необходимо
проверить правильность формулы и
содержимое адресов ячеек, используемых
в ней.

Правильный
ответ: 4.

Литература: Степанов
А.Н. Информатика: Учебник для вузов. 6-е
изд.- СПб.: Питер, 2010. – 720с.: ил. – стр.
538.

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

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

Огромная производительность Excel позволяет моментально обрабатывать очень сложные математические выражения. На практике обычно используется лишь малая часть доступного математического аппарата. В качестве примера возможностей Excel по экстремальным вычислениям в одной ячейке обратите внимание на статью «Сумма прописью без программирования», размещенную на нашем сайте.

Ограничения на сложность формул стали еще менее строгими при изменении формата файла Excel на xlsx (версия 2007 и более поздние):

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

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

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

В Excel имеется несколько возможностей для написания математического выражения:

• Мастер функций
• Редактор формул непосредственно в ячейке
• Редактор формул в отдельной области главного окна

У каждого метода есть свои достоинства и недостатки. Разберем их в контексте создания сложных формул.

Мастер функций

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

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

Редактор формул в ячейке

Параметры Excel по умолчанию установлены таким образом, что при вводе формулы с клавиатуры (нажатии кнопки «=») редактирование выражения осуществляется непосредственно в активной ячейке:

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

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

Область редактора формул

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

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

Автозавершение названия функции

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

Автозавершение названия проще всего производить через клавиши вверх, вниз, выбор через клавишу Tab. Можно для этих же целей воспользоваться мышью, но это заведомо менее удобный способ – переключение между вводом с клавиатуры и мышью занимает некоторое время.

Выбор диапазонов

Если в качестве параметров функций используются ячейки или диапазоны ячеек, то в большинстве случаев для их выбора проще использовать выделение мышью непосредственно в области рабочего листа. Это очень удобный и наглядный способ работы, и осваивается он довольно быстро. Другое дело, что многие пользователи после этого остаются в уверенности, что только так и можно указывать параметры функции. Это не так. Возможность выделения при помощи мыши всего лишь интерфейсное средство, облегчающее ввод с клавиатуры. То есть, если ввести выражение «A1:A1000» – это будет равносильно выделению мышью тысячи ячеек первого столбца. Очевидно, что это не очень просто – требуется прокрутка видимой области выделения (скроллинг) с точным поиском тысячной строки. В некоторых случаях просто ввести адрес с клавиатуры даже быстрее: в примере набирается «a1:a1000» (регистр не важен), это 8 нажатий клавиш.

Еще одним способом является смешанный тип ввода, когда сначала выделяется начало диапазона при помощи мыши, а правильный размер вводитсья с клавиатуры. В предыдущем примере, можно выделить A1:A10 (десять видимых ячеек), потом сразу же ввести с клавиатуры «00» — получится искомый диапазон A1:A1000. Выражение будет работать верно, но скроллинг при помощи мыши при этом не требуется.

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

• При работе в A1-адресации явным указателем признака абсолютной адресации является символ «$» (доллар) ввод с клавиатуры «$a$1:$a$1000».
• При работе в R1C1-адресации абсолютные ссылки указываются без использования квадратных скобок – ввод с клавиатуры «R1C1:R1C1000».
• Выделение диапазона, а затем последовательное нажатие клавиши F4 для подбора нужного типа адресации. В примере адрес будет меняться следующим образом:

1. $A$1:$A$1000
2. A$1:A$1000
3. $A1:$A1000
4. A1:A1000 (исходное состояние)

Способ с использованием F4 на практике оказывается обычно быстрее для ввода абсолютных ссылок, закрепленных и по горизонтали, и по вертикали. Для ввода смешанных ссылок на диапазон проще указать мышью нужную область, а затем расставить символы «$» в нужные позиции выражения.

Структурные выражения

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

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

Теперь обратите внимание ту же формулу, но записанную в многострочном варианте:

Очевидно, что второй вариант более понятен по смыслу выполняемых расчетов.

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

Также в качестве разделителя внутри выражений можно использовать разное количество пробелов на разных уровнях скобок – этот прием также немного облегчает восприятие сложных выражений.

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

 

=ЕСЛИ($BD11=0;
  "";
  СУММ(
    ЕСЛИ(Доходы!$I$6:$I$503=$BD11;
        ЕСЛИ(Доходы!$H$6:$H$503=C$7;
          Доходы!$K$6:$K$503;
          0
        );
        0
    )
  )
+
  ЕСЛИ($BG110;
    0;
    СУММ(
      ЕСЛИ($BF12:$BF$59=$BD11;
        D12:D$59;
        0
      )
    )
  )
)
 

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

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

Существует способ написания комментариев внутри формулы. Для этого используется любая функция, преобразующая текст в число, например: ДЛСТР, КОДСИМВ; затем это число прямо в формуле умножается на ноль, а результат прибавляется в конец формулы.

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

Отладка формул

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

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

Разложение формулы на составляющие

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

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

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

Есть противопоказания к применению этого метода.

• Если формула содержит функции вычисляемой адресации (СМЕЩ, СТРОКА, ЧИСЛСТОЛБ, ДВССЫЛ), то применять метод разложения с использованием других ячеек некорректно. Надо четко отслеживать изменения в адресах при перемещении составляющих формулы в другие ячейки, что не всегда удобно.
• Если в сложной формуле используется обработка массивов, то надо четко понимать, какие промежуточные ячейки требуют такой обработки (вводятся через Ctrl+Shift+Enter), а какие нет. При обратной сборке формулы это также надо учитывать.

Вычисления внутри формулы

Это, пожалуй, самый полезный способ отладки формул. Excel позволяет проводить вычисления выражений внутри сложной формулы. Для этого в редакторе формул выделяется блок текста, который должен быть вычислен, и нажимается клавиша F9 – в результате выделенный блок будет заменен на рассчитанное значение. Можно выделить другой блок, и повторить расчет, не покидая редактор формул.

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

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

Влияющие значения

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

Отобразить влияющие ячейки можно несколькими способами.

Активизация редактора формул

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

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

Использование кнопок зависимостей формулы

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

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

Ссылки на влияющие ячейки, вызывающие ошибки в текущей формуле выделяются красным цветом стрелки.

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

Неприятным недостатком этого метода отладки формул является сброс возможности отмены операций после любого нажатия на кнопку графических зависимостей (не работает Ctrl+z).

Принудительное выделение влияющих ячеек

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

Для проведения операции в Excel 2007/2010 выберите на ленте «Главная Найти и выделить Выделение групп ячеек»; в открывшемся диалоге отметьте пункт «Влияющие ячейки» (только верхнего или всех уровней) и нажмите «Ок».

Можно использовать последовательную комбинацию клавиш (русская раскладка): Ctrl+п, Alt+в, в, Enter (работает и в предыдущих версиях Excel). Если Windows настроен по умолчанию в английскую раскладку клавиатуры (см. региональные настройки), то для этих же целей доступна комбинация клавиш Ctrl+[ (Ctrl+] для зависимых ячеек).

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

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