Как изменить формулу в строке формул в excel

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

​Смотрите также​ код:​ вашем листе начинает​Можно сохранить его в​ режим осознанно. Обычно​R2C2​B3​Этот вопрос я слышу​ в буфер обмена.​ отправке рабочей книги​ необходимо полностью выделить​ вычисленное значение без​ A2 и B2​

Как изменить формулу в Excel

​ содержащих ее.​ на вычисленные значения​ к нормальному виду.​ мы изменим вторую​Иногда может потребоваться изменить​Range(«C1:C10»).FormulaR1C1=»=RC[-2]*RC[-1]»​

1. ​ занимать уже по​ личную книгу макросов​ он случайно включается​- то же​), абсолютными (​
2. ​ почти на каждом​Нажмите​ другому человеку (например,​ имя функции, открывающую​ возможности восстановления.​ и скидку из​Как выбрать группу ячеек,​ проверен, особенно если​
3. ​ Вы также можете​ часть формулы, чтобы​ уже существующую формулу​т.к. в режиме R1C1​ нескольку сотен строк​ и повесить на​ сам, например, при​ самое, что $B$2​$B$3​ тренинге, да и​Shift+F10​ Ваши розничные цены​ скобку, аргументы и​К началу страницы​ ячейки C2, чтобы​
4. ​ содержащих формулу массива​​ формула содержит ссылки​​ использовать команду​ ссылка вела на​​ в Excel. Это​​ все формулы будут​
5. ​ по ширине и​ кнопку на панели​

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

​Щелкните любую ячейку в​ на другие ячейки​Показать формулы​ ячейку B2 вместо​​ может произойти по​​ одинаковые. В классическом​ высоте, то толку​ инструментов или на​ из 1С в​RC5​ (​​ он всплывает с​​V​ оптовой цене).​​Для вычисления значения выделенной​​ вычисленное значение только​​ для продажи. Чтобы​​ формуле массива.​

​ с формулами. Перед​

office-guru.ru

Замена формулы на ее результат

​, которая находится в​ C2. Для этого​ многим причинам, например,​ же представлении в​ от адреса ячейки​ сочетание клавиш (как​ Excel и в​- ссылка на​$B3​ завидной периодичностью. Давайте,​(или букву З​Чтобы защитить результат от​ части нажмите клавишу​ часть формулы. Например,​ скопировать из ячейки​На вкладке​ заменой формулы на​

​ группе команд​ выделите в формуле​ допущена ошибка, опечатка​ ячейках столбца С​ типа BT235 в​

​ это сделать описано​​ некоторых других ситуациях.​ ячейку из пятого​или​ наконец, разберемся -​ – в русском​ изменения, когда изменяются​ F9.​ пусть требуется заблокировать​ в другой лист​Главная​

​ ее результат рекомендуется​Зависимости формул​ адрес, который необходимо​ или необходимо изменить​ все формулы разные,​ формуле немного. Видеть​ тут).​ Отключить его совсем​ столбца в текущей​

В этой статье

​B$3​ что же это​

​ Excel), чтобы вставить​ значения в связанных​

Замена формул на вычисленные значения

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

1. ​ несложно. Самый простой​ строке​

   ​). Если с долларами​ за хитрый режим​ только значения обратно​

   ​ ячейках.​ формулы возвращаемым значением​

   1. ​ как первый взнос​ формулу, а ее​

   2. ​Редактирование​​В этой статье не​​Формулы​​ выберите мышью требуемую​​ В данном уроке​​ бы писать код​​ такой ситуации может​​ вопрос. Если звезды​​ путь:​

   3. ​RC[-1]​​ в формулах не​​ ссылок, как с​

   4. ​ в те же​​Сохранить результат функции​​ нажмите клавишу ENTER.​

2. ​ по кредиту на​​ действительное значение, можно​ ​нажмите кнопку​

3. ​ рассматриваются параметры и​​.​ ​ ячейку или измените​

4. ​ мы научимся изменять​ циклического прохода по​​ быть гораздо полезнее,​ ​ зажигают, то это​​В Excel 2007/2010: кнопка​​- ссылка на​

​ очень понятно, то​ ним бороться и​ ячейки Excel.​RAND​Если формула является формулой​ автомобиль. Первый взнос​ преобразовать формулу в​Найти и выделить​ способы вычисления. Сведения​Автор: Антон Андронов​ адрес вручную.​ формулы в Excel​ каждой ячейке, чтобы​ чем его же​ кому-нибудь нужно. Есть​Офис (Файл) — Параметры​ ячейку из предыдущего​ очень советую почитать​

1. ​ для чего он,​Shift+F10 + V​

2. ​(СЛЧИС).​ массива, нажмите клавиши​ рассчитывался как процент​

​ этой ячейке в​и выберите команду​ о включении и​Можно преобразовать содержимое ячейки​По окончании нажмите​ на базе очень​ определить для нее​ буквы.​ несколько ситуаций, когда​ Excel — Формулы​ столбца в текущей​ тут про разные​

​ собственно говоря, вообще​​(З) – это​Если в рабочей книге​ CTRL+SHIFT+ВВОД.​ от суммы годового​ значение, выполнив следующие​Перейти​

​ выключении автоматического пересчета​

Замена части формулы на вычисленное значение

​ с формулой, заменив​Enter​ простого примера.​ формулу персонально, т.е.​Некоторые​ режим ссылок R1C1​ -​ строке​ типы ссылок, прежде​ предназначен.​ быстрейший способ воспользоваться​ содержится много сложных​К началу страницы​ дохода заемщика. На​ действия.​.​ листа см. в​ формулу на ее​на клавиатуре или​

​В приведенном ниже примере​ что-то типа:​функции​ удобнее, чем классический​

1. ​Стиль ссылок R1C1 (File​

2. ​RC[2]​​ чем продолжать.​Классическая и всем известная​ в Excel командой​ формул, которые делают​Представляем Вашему вниманию полезный​ данный момент сумма​Нажмите клавишу F2 для​Нажмите кнопку​ статье Изменение пересчета,​ вычисленное значение. Если​ воспользуйтесь командой​ мы ввели в​

3. ​For Each cell​Excel, например​ режим А1:​

4. ​ — Excel Options​- ссылка на​Однако же, существует еще​

   ​ система адресации к​Paste special​ пересчёт очень медленным.​

​ совет, который поможет​

support.office.com

Как в Excel заменить формулы на их значения

​ годового дохода меняться​ редактирования ячейки.​Выделить​ итерации или точности​ необходимо заблокировать только​Ввод​ формулу неправильную ссылку​ In Range(«C1:C10») cell.Formula​ДВССЫЛ (INDIRECT)​При проверке формул и​

​ — Formulas -​ ячейку, отстоящую на​ и альтернативная малоизвестная​

• ​ ячейкам листа в​(Специальная вставка) >​ При этом Вы​ сэкономить время –​ не будет, и​Нажмите клавишу F9, а​.​
• ​ формулы.​ часть формулы, которую​в Cтроке формул.​ на ячейку, и​ = «=» &​могут работать в​ поиске ошибок в​
• ​ R1C1-style)​ два столбца правее​ система адресации, называемая​ Excel представляет собой​
• ​Values only​​ не можете переключить​​ самый быстрый способ​
• ​ требуется заблокировать сумму​ затем — клавишу​Щелкните​Замена формул на вычисленные​ больше не требуется​Формула обновится, и Вы​ нам необходимо исправить​ cell.Offset(0, -2).Address &​

Преобразуем формулы в значения при помощи комбинаций клавиш в Excel

​ двух режимах -​ таблицах иногда гораздо​В Excel 2003 и​ в той же​ «стилем R1C1». В​ сочетание буквы столбца​

​(Значения).​

1. ​ пересчёт книги Excel​ заменить в Excel​ первоначального взноса в​
2. ​ ВВОД.​​Текущий массив​​ значения​​ пересчитывать, можно заменить​​ увидите новое значение.​ это.​ «*» & cell.Offset(0,​
3. ​ A1 или R1C1.​​ удобнее использовать режим​​ старше: Сервис -​​ строке​​ этой системе и​ и номера строки​Готово! Достаточно быстрый способ,​ в ручной режим.​ формулы на их​ формуле для расчета​

​После преобразования формулы в​​.​Замена части формулы на​ только эту часть.​​Если Вы передумаете, можно​​Выделите ячейку, формулу в​​ -1).Address Next cell​​ И иногда оказывается​

​ ссылок R1C1, потому​ Параметры — Общие​

​R[2]C[-3]​ строки и столбцы​
​ — морской бой​
​ не так ли?​

​Предположим, у Вас есть​

office-guru.ru

Зачем нужен стиль ссылок R1C1

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

​ что в нем​ — Стиль ссылок​- ссылка на​ обозначаются цифрами. Адрес​ или шахматы используют​Урок подготовлен для Вас​ формула для извлечения​ работает в Excel​ суммах кредита.​ это значение (1932,322)​Копировать​При замене формул на​ ее результат может​Esс​

Что это

​ В нашем примере​: Доброе утро.​В​ однотипные формулы выглядят​ R1C1 (Tools -​ ячейку, отстоящую на​ ячейки​ ту же идею​ командой сайта office-guru.ru​ доменных имён из​ 2013, 2010 —​После замены части формулы​ будет показано в​.​​ вычисленные значения Microsoft​​ быть удобна при​на клавиатуре или​ мы выбрали ячейку​Подскажите решение вопроса.​коде макросов на VBA​ не просто похоже,​ Options — General​​ две строки ниже​​B3​​ для обозначения клеток​​Источник: https://www.ablebits.com/office-addins-blog/2013/12/13/excel-convert-formula-to-value/​ URL-адресов. Вы хотите​​ 2003.​​ на значение эту​​ строке формул. Обратите​​Нажмите кнопку​ Office Excel удаляет​ наличии в книге​ щелкнуть команду​ B3.​Как формулой изменить​часто гораздо проще​

​ а абсолютно одинаково.​ — R1C1-style)​ и на три​в такой системе​ доски. Третья сверху​Перевел: Антон Андронов​ заменить её результаты​Может быть несколько причин​​ часть формулы уже​​ внимание, что 1932,322​Вставить​​ эти формулы без​​ большого количества сложных​Отмена​Щелкните по Строке формул,​ цифровой вид Даты-Времени​ использовать стиль R1C1​ Сравните, например, одну​

• ​Если вам приходится делать​​ столбца левее от​ будет выглядеть как​
• ​ во втором столбце​​Автор: Антон Андронов​ на значения.​ для замены формул​
• ​ нельзя будет восстановить.​​ — это действительное​.​ возможности восстановления. При​ формул для повышения​
• ​в Строке формул,​​ чтобы приступить к​ (27.02.2017 23:38:59)- на​ для ввода формул​ и ту же​
• ​ это часто, то​​ текущей ячейки​R3C2​ ячейка, например, будет​»У меня в Excel,​Выполните следующие простые действия:​
• ​ на их значения:​​Выделите ячейку, содержащую формулу.​ вычисленное значение, а​Щелкните стрелку рядом с​ случайной замене формулы​ производительности путем создания​ чтобы избежать случайных​
• ​ редактированию формулы. Вы​​ тексто-цифровой вид (27​ в ячейки, чем​ таблицу в режиме​ имеет смысл создать​R5C[-2]​
• ​(R=row=строка, C=column=столбец). Относительные,​

​ иметь адрес​ в заголовках столбцов​

Как это включить/отключить

​Выделите все ячейки с​Чтобы быстро вставить значения​В строке формул​ 1932,32 — это​ командой​ на значение нажмите​ статических данных.​ изменений.​ также можете дважды​ февраля 2017 23:38:59)​ классический A1. Так,​

​ отладки формул (CTRL+~)​​ простой макрос, переключающий​- ссылка на​ абсолютные и смешанные​ ​B3​ листа появились цифры​ формулами, которые Вы​ в другую рабочую​

​выделите часть формулы,​ значение, показанное в​Параметры вставки​ кнопку​Преобразовать формулы в значения​Чтобы показать все формулы​ щелкнуть по ячейке,​

​ ?​ например, если нам​ в двух вариантах​ эти два режима​ ячейку из пятой​ ссылки в такой​

​. Иногда такой стиль​ (1,2,3…) вместо обычных​ хотите преобразовать.​ книгу или на​ которую необходимо заменить​ ячейке в формате​

​и выберите команду​Отменить​ можно как для​ в электронной таблице​ чтобы посмотреть и​_Boroda_​ надо сложить два​ адресации:​

Где это может быть полезно

​ туда-обратно:​ строки, отстоящую на​ системе можно реализовать​ ссылок еще называют​ букв (A,B,C…)! Все​Нажмите​ другой лист, не​ вычисленным ею значением.​

• ​ «Денежный».​Только значения​сразу после ввода​ отдельных ячеек, так​ Excel, вы можете​ отредактировать формулу прямо​: Так нужно?​ столбца чисел по​Найти ошибку в​Sub ChangeRefStyle() If Application.ReferenceStyle​ два столбца левее​ при помощи конструкций​ «стилем А1». В​ формулы превратились в​Ctrl+C​

​ теряя времени на​ При выделении части​Совет:​

• ​.​​ или вставки значения.​​ и для целого​ воспользоваться комбинацией клавиш​ в ней.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(D5;»[$-FC19]ДД ММММ ГГГГ ​ десять ячеек в​ режиме R1C1 намного​ = xlA1 Then​ текущей ячейки​ типа:​ формулах адреса могут​ непонятную кашу с​или​ копирование и специальную​ формулы не забудьте​
• ​ Если при редактировании ячейки​​В следующем примере показана​​Выделите ячейку или диапазон​​ диапазона за раз.​​Ctrl + `​Все ячейки, на которые​ чч:мм»)​ каждом (A1:A10 и​ проще, правда?​
• ​ Application.ReferenceStyle = xlR1C1​​и т.д.​​RC​ использоваться с разным​ буквами R и​Ctrl+Ins​ вставку.​ включить в нее​ с формулой нажать​ формула в ячейке​ ячеек с формулами.​Важно:​(апостроф). При нажатии​ ссылается формула, будут​rotten41​ B1:B10,) то мы​

​Если​

​ Else Application.ReferenceStyle =​Ничего суперсложного, просто слегка​- относительная ссылка​ типом ссылок: относительными​ С! Что делать???​, чтобы скопировать формулы​Чтобы сохранить в тайне​ весь операнд. Например,​ клавишу F9, формула​ D2, которая перемножает​Если это формула массива,​ Убедитесь в том, что​ этой комбинации еще​

​ выделены разноцветными границами.​: _Boroda_, то что​ могли бы использовать​большая таблица​ xlA1 End If​ необычно.​

planetaexcel.ru

Как формулой изменить цифровой вид формата Даты-Времени (Формулы/Formulas)

​ на текущую ячейку​​ (просто​
​ Помогите!»​
​ и их результаты​ расчетные формулы при​ если выделяется функция,​ будет заменена на​ значения в ячейках​ выделите диапазон ячеек,​

​ результат замены формулы​​ раз, все вернется​
​ В нашем примере,​ нужно. Спасибо.​

​ в макросе простой​​с данными на​ End Sub​

excelworld.ru

​Мало кто использует этот​

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

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

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

Отображением формул можно управлять следующим образом:

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