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

Данная функция является частью надстройки MulTEx

Описание, установка, удаление и обновление Полный список команд и функций MulTEx Часто задаваемые вопросы по MulTEx   Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны -Ячейки -Замена ссылок в формулах на значения ячеек

Команда преобразует ссылки на ячейки в формуле на значения ячеек, на которые ведут эти ссылки. К примеру на листе есть формула: =A1*A14+(5+C13)*C14 /B11
И необходимо понять какие значения скрываются за ссылками на ячейки. Т.е. из приведенной выше формулы надо сделать что-то вроде: =10*5,2+(5+5)*10 /7,8

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

Диапазон с формулами(Лист1!$B$3:$D$16) - указывается диапазон, формулы из которого необходимо преобразовать.
Метод вывода:

• В комментарии к ячейкам с формулами - преобразованная формула будет записана в примечание к ячейке, в которой расположена эта формула.
  
  Примечание создается автоматически внутри команды. Пожалуй самый удобный метод, т.к. и формула остается неизменной, и ячейки рядом. Но, как можно догадаться, если в ячейке до этого уже было какое-либо примечание - оно будет перезаписано. Т.е. старое примечание будет удалено без возможности восстановления.
• В ячейки правее ячеек с формулами - преобразованная формула будет выведена в ячейку, которая расположена правее ячейки с формулой:
  
  С одной стороны это вполне наглядно, но с другой стороны не всегда удобно. Если в ячейках правее есть какие-то значения, то они будут удалены и на их место будет записана расшифровка формулы.
• Заменить ссылки на их значения прямо в формуле - преобразование ссылок на ячейки в значения производится непосредственно внутри формулы. Может пригодиться, если необходимо оставить и формулу и возможность проследить этапы вычислений. В данном случае полностью теряется возможность проследить из каких ячеек взяты те или иные значения, т.к. исходная формула полностью заменяется. При использовании данного метода действия команды по преобразованию формул можно отменить, нажав кнопку возврата значений на панели Excel или сочетание клавиш Ctrl+Z

Выводить значения с точностью как на экране - если установлен, то значения ссылок выводятся так же, как они отображаются в ячейках. К примеру, если в ячейке A14 отображается значение "5,2" это не всегда означает, что само значение ячейки так же "5,2". Если к ячейке применен формат Числовой с количеством знаков после запятой 1, а в ячейке значится "5,159", то это значение тоже будет отображаться как "5,2". Это следует учитывать, применяя данную команду. Если флажок для данной опции не установлен, то в преобразованной формуле будут использованы реальные значения ячеек, несмотря на примененные к ним числовые форматы.

Проверять вложенность формул в ссылках - Если установлен, то при обнаружении внутри формулы ссылок на другие ячейки, которые так же содержат формулы - формулы этих ячеек так же будут разобраны и все ссылки в них заменены на значения. Используется в случаях, если формулы имеют множество связей друг с другом и надо проследить все значения. Так же в поле правее указывается уровень вложенности формул, до которого необходимо просматривать. Если указать 1 - то в значения будут преобразованы только самые первые ссылки, без просмотра внутри них формул. Если необходимо разбирать формулу до самого последнего уровня вложенности - можно указать заранее бОльшее значение вложенности, чем может быть в формуле(например, 999).
Не выводить [значение недоступно] - данная опция необходима в случаях, когда в формуле присутствуют ссылки на другие книги и эти книги закрыты. Получить значение таких ссылок в этом случае невозможно. Поэтому для таких ссылок замена происходит следующим образом: путь к книге будет оставлен без изменений, а имя книги, листа и адрес ячеек будут заменены на [значение недоступно]. Однако, далеко не всегда требуется скрывать источник ссылки. К тому же, если выбран пункт Заменить ссылки на их значения прямо в формуле - текст [значение недоступно] вызовет ошибку формулы. Но если ссылку оставить без изменений, то формула будет работать.

Не выводить [ссылок на другие ячейки нет] - Если установлен, то для ячеек, не содержащих ссылок, будет возвращено значение пусто, а не текст [ссылок на другие ячейки нет]. Пригодится в случае, если применяется метод Заменить ссылки на их значения прямо в формуле, т.к. в этом случае лишний текст может привести к ошибке расчетов формулы.

Не заменять именованные диапазоны на значения - Если установлен, то ссылки на именованные диапазоны внутри формулы не будут заменяться значениями, а имена останутся "как есть". Может пригодится, если именованные диапазоны были созданы специально для удобства разбора формулы. В этом случае замена их на значения может только запутать.

Если в какой-либо из ячеек не будет ссылок на другие ячейки, а просто текстовая формула, то как результат отобразится сама формула. Если при этом пункт Не выводить [ссылок на другие ячейки нет] не отмечен, то за формулой так же будет дописан текст: "[ссылок на другие ячейки нет]".
Если в формуле применяются функции (ВПР, СЧЁТЕСЛИ, МИН, МАКС и т.д.), то их имена будут отображены без искажений (=СУММ(5,2;7,8)+ЦЕЛОЕ(5/11))
Если присутствуют ссылки на ячейки из других листов или книг, то они отображаются как и все остальные - просто значениями. Если в формуле есть ссылка на другую книгу, которая в настоящий момент закрыта - то сам путь к книге будет оставлен без изменений, а имя книги, листа и адрес ячеек будут заменены на [значение недоступно]. Если при этом установлен флажок Не выводить [значение недоступно], то ссылка пропускается и ничем не заменяется.
Если в формулах встречаются ссылки на массивы ячеек (A14:B16) — будут отображены все значения непустых ячеек массива (как и положено массиву в фигурных скобках: {5,2;4:6}. Для русской локализации двоеточием разделяются строки, а точкой-с-запятой - столбцы).

Так же см.:
Вставить значения