Как изменить отображение ячеек в формуле excel

Перейти к содержимому

Как изменить стиль ссылок R1C1 на обычные A1 в программе Excel

Адреса ячеек в программе «Excel» могут отображаться в двух разных форматах:

1) Самый популярный и для большинства пользователей наиболее удобный формат адреса ячеек — это буквы латинского алфавита с цифрами, где названия столбцов обозначаются буквами, а названия строк цифрами. И, соответственно, ячейки имеют адреса по названию пересечения столбца и строки.

2) Второй формат — относительные ссылки, менее популярен и востребован. Этот формат имеет вид R1C1. Такой формат отображения показывает адреса ячеек используемых в формуле относительно выбранной ячейки, в которую записывается формула. Многие пользователи, увидев такой формат отображения адресов ячеек, впадают ступор и пытаются перевести вид адресов в привычный буквенно цифровой вид.

Как перевести формат адресов ячеек R1C1 (RC) в обычный вид: A1; B1; C1 и т.д.

Для изменения вида адресов следует выполнить следующую последовательность действий:

• войти во вкладку «Файл»;
• выбрать меню «Параметры»;
• далее выбрать вкладку «Формулы»;
• во вкладке «Формулы» убрать «галочку» (флажок) напротив параметра «Стиль ссылок R1C1»;
• нажать кнопку «Ok».

После выполнения указанной последовательности действий адреса ячеек приобретут привычный вид : A1; B1; C1 и т.д.

Стили ссылок А1 и R1C1, или почему в Excel столбцы вместо букв обозначены цифрами?

При работе с электронными таблицами Excel возникают ситуации, когда при открытии книги столбцы вместо привычных латинских букв имеют в качестве заголовков числа. Происходит это из-за того, что в Excel используется два вида ссылок: А1 и R1C1. Рассмотрим оба стиля и выясним, чем они отличаются, и как отключить стиль R1C1.

Адрес ячейки

Название стиля R1С1 происходит от двух английских слов R— row (строка), C — column (столбец).

В стиле А1 ячейка имеет адрес A1, где А — столбец, а 1 — номер строки. В стиле R1C1 — адрес R1C1, где R1 указывает номер строки, а C1 — номер столбца. Т.е. ячейке С2 одного стиля будет соответствовать ячейка R2C3 (вторая строка, третий столбец).

Чем же отличается использование этих стилей при вычислениях? Рассмотрим следующий пример. Дана таблица, состоящая из трех числовых значений. Нужно посчитать сумму этих ячеек.

Сравним формулы, которые получаются при работе с таблицей в A1 и R1C1-стилях.

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

В формулу A1-стиля входят адреса ячеек, значения которых складываются: B3, B4 и C3.

В R1C1-стиле сумма значений ячеек, соответствующих ячейкам примера с A1-стилем, имеет другой вид. В формулу не входят адреса ячеек. Каждое слагаемое указывает, на сколько строк и сколько столбцов происходит смещение ссылки относительно ячейки, в которую вводится формула. Если число в скобках положительно, то ссылка смещается вправо или вниз, если отрицательно — влево или вверх. Все зависит от того, после чего стоит это число: R — смещение строки, C — смещение столбца. Если числа нет, то нет и смещения.

Рассмотрим детально каждое слагаемое:

• R[-1]C[-1] — ссылка на ячейку, которая расположена на одну строку выше (R — строка, [-1] — смещение вверх) и на один столбец левее (C — столбец, [-1] — смещение на 1 столбец влево). Т.к. формула вводится в ячейку R4C3 (или С4 для A1-стиля), то с учетом смещения мы получим ячейку R3C2. Эта ячейка соответствует ячейке B3 A1-стиля.
• RC[-1] — ссылка расположена в той же строке, что и формула (нет смещения строки), столбец смещен влево на один (-1). Это ячейка B4A1-стиля.
• R[-1]C — ссылка смещена на одну строку вверх (-1) и находится в том же столбце. Это ячейка C3A1-стиля.

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

Относительные, абсолютные и смешанные ссылки

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

• Относительные ссылки при копировании формулы меняются. В A1-стиле относительные ссылки записываются как G3, D5 и т.д. В R1C1-стиле— R[4]C[2], R[6]C[-1] и т.д. Числа, указывающие на смещение, заключаются в квадратные скобки. RC — ссылка на текущую ячейку (смещение равно нулю).
• Абсолютные ссылки при копировании не изменяются. В A1-стиле абсолютные ссылки записываются как $G$3, $D$5 и т.д. В R1C1-стиле— R4C2, R6C1 и т.д. Числа, указывающие на смещение, не заключаются в квадратные скобки.
• Смешанные ссылки — ссылки вида $G3, D$5 и т.д. При копировании не меняется только часть ссылки, стоящая после знака $. В R1C1-стиле— R4C[2], R[6]C5 и т.д.

Как включить или отключить R1C1-стиль в Excel

Если Вы используете Excel 2003, то выберите Сервис — Параметры — вкладка Общие. Для включения стиля R1C1 устанавливаем флажок Стиль ссылок R1C1. Для выключения — флажок снимаем.

Для более поздних версий программы (Excel 2007, 2010 и далее) нажимаем кнопку Офис (или Файл) — Параметры Excel — Формулы — устанавливаем флажок Стиль ссылок R1C1. Для выключения — флажок снимаем.

R1C1-стиль удобен при работе с большими таблицами для сравнения формул в ячейках и поиска ошибок.

Кратко об авторе:

Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Как в Excel 2007/2010 изменить стиль ссылок?

Как сделать чтобы R[-1]C[-1 был A1?

Не каждый пользователь Экселя при своей работе использует формулы — особенно те, что требуют ссылок на ячейки электронной таблицы. Поэтому для большинства даже не возникает вопроса о виде ссылок на ячейки, которые используются: тем более, что по умолчанию ячейка, обычно, обозначается одной (или несколькими) буквой и цифрой (числом) — А1, С8 и т.п. Вот так:

Но если вдруг (по какой-то причине — например, коллега «подправил») обозначение ячеек в таблице изменилось и они отображаются только цифрами и по строкам и по столбцам (а в ссылках это будет выглядеть, как R1C1) — вот в таком виде:

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

НЕ ВОЛНУЙТЕСЬ, Я СЕЙЧАС ВСЕ ОБЪЯСНЮ!

Следите через RSS

Подписаться на RSS

Задаем вопросы

Комментарии на сайте

Что за странные адреса вида R1C1 в Excel?

Недавно вел корпоративный тренинг по Microsoft Office Excel 2007 в одной конторе и столкнулся с пренеприятнейшим сюрпризом.
Внешний вид таблицы Excel был непривычен — вместо хорошо знакомых латинских букв для обозначения заголовков столбцов в верхней строке стояли тоже цифры.
Хозяйка компьютера не могла взять в толк, что это случилось с ее родным компьютером, и отчего Excel «взбунтовался».
А на самом деле — все просто. Дело в том, что есть два вида представления ссылок в Microsoft Excel:

* Классический;
* Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Включить стиль ссылок R1C1 можно в Office 2003 включив в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»

В Office 2007 надо нажать на кнопку «Office», после чего выбрать «Параметры Excel» —> закладка «Формулы» —> «Стиль ссылок R1C1»

Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.

Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).

НАПРИМЕР. Пусть в ячейке D3 нужно получить произведение чисел, находящихся в ячейках А2(второй ряд, первая колонка) и B1 (первый ряд, вторая колонка). Это может быть записано одним из следующих способов:

Стили ссылок R1C1 и A1 в Excel

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Адресация к ячейкам листа в Excel является одним из основополагающих аспектов работы в программе, и большинство пользователей программы работают со стилем ячеек вида A1, в котором столбцы задаются буквами, а строки числами (в данном случае A — литера столбца, а 1 — номер строки, соответственно, ячейка — пересечение столбца A и строки 1).
Например, ячейка D5 стоит на пересечении столбца D и строки 5, ячейка F8 на пересечении столбца F и строки 8 и т.д.
Вдобавок в Excel стиль A1 используется по умолчанию, поэтому все привыкли работать именно с ним.

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

Однако иногда встречается использование другого стиля — R1C1, где и столбцы и строки задаются числами (R1 означает строку 1, где R — Row (строка в переводе с английского), а C1 означает столбец 1, где C — Column (столбец в переводе с английского), соответственно, ячейка — пересечение строки 1 и столбца 1).

Как включить или отключить стиль ссылок R1C1 в Excel?

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

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

Особенности и отличия стилей адресации A1 и R1C1

В первую очередь, при работе с ячейками обратите внимание, что для стиля R1C1 в адресе сначала идет строка, а потом столбец, а для A1 все наоборот — сначала столбец, а потом строка.
Например, ячейка $H$4 будет записана как R4C8 (а не как R8C4), поэтому будьте внимательнее при ручном вводе формул.

Еще одно отличие между A1 и R1C1 — внешний вид окна программы Excel, в котором по-разному обозначаются столбцы на рабочем листе (A, B, C для стиля A1 и 1, 2, 3, … для стиля R1C1) и имя ячейки:

Как известно, в Excel есть 3 типа ссылок (тут можно почитать подробнее): относительные (А1), абсолютные ($А$1) и смешанные ($А1 и А$1), где знак доллара ($) служит закреплением номера строки или столбца.
В случае со стилем R1C1 также можно использовать любой тип ссылки, но принцип их составления будет несколько другим:

• RC. Относительная ссылка на текущую ячейку;
• R1C1. Абсолютная ссылка на ячейку на пересечении строки 1 и столбца 1 (аналог $A$1);
• RC2. Ссылка на ячейку из 2 столбца текущей строки;
• R3C. Ссылка на ячейку из 3 строки текущего столбца;
• RC[4]. Ссылка на ячейку на 4 столбца правее текущей ячейки;
• R[-5]C. Ссылка на ячейку на 5 строк левее текущей ячейки;
• R6C[7]. Ссылка на ячейку из 6 строки и на 7 столбцов правее текущей ячейки;
• и т.д.

В общем и целом, получается, что аналогом закрепления строки или столбца (символа $) для стиля R1C1 является использование чисел после символа строки или столбца (т.е. после букв R или C).

Применение квадратных скобок позволяет сделать относительное смещение относительно ячейки, в которой введена формула (к примеру, R[-2]C делает смещение на 2 строки вниз, RC[2] — смещение на 2 столбца вправо и т.д.). Таким образом, смещение вниз или вправо обозначается положительными числами, влево или вверх — отрицательными.

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

Преимущества стиля ссылок R1C1

Можно выделить 2 основных преимущества, при которых использовать R1C1 предпочтительнее, чем A1: при проверке формул (поиске ошибок) и в макросах.

Рассмотрим пример: возьмем простую таблицу, в которой проверим на корректность расчета формулы в последнем столбце:

Видите ошибку?

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

Давайте теперь посмотрим эту же таблицу со стилем ячеек R1C1:

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

Аналогичный прием можно применить и в случае с работой с ячейками при создании макросов.
В случае, если нам нужно прописать для диапазона ячеек формулы произведения двух столбцов (как в примере выше), то для стиля R1C1 все формулы будут записываться абсолютно одинаково (=RC[-2]*RC[-1]), поэтому в коде макроса можно сразу прописать формулу для всех ячеек диапазона. Для стиля A1, к сожалению, такой способ не подойдет, так как записи всех формул отличаются друг от друга и каждую нужно будет прописывать отдельно, что несколько усложняет задачу.

R1C1 в функциях Excel

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

Однако в Excel есть функции, в которых возможно применение обоих стилей адресации вне зависимости от установленного режима в настройках. В частности, функции ДВССЫЛ (INDIRECT в английской версии) и АДРЕС (ADDRESS в английской версии) могут работать в обоих режимах.

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по использованию и преимуществам разных стилей ссылок — пишите в комментариях.

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

При построении экономических моделей, обработке больших объемов данных, статистических расчетах и других задачах, решаемых при помощи 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.