Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (66)
Разное (42)
Баги и глюки Excel (4)

Отобразить в формулах вместо ссылок на ячейки значения ячеек

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

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

  • скачать файл, приложенный к статье
  • ознакомиться со статьей Что такое макрос и где его искать?, если еще не знакомы с макросами
  • при необходимости коды из файла перенести в свой файл(перейти к просмотру кодов можно, нажав в файле кнопку "Посмотреть код")

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

  • выделяем ячейки с формулами, которые необходимо "показать";
  • нажимаем кнопку "Показать значения формулы в выделенных ячейках";
  • появится запрос

    • если выбрать "Нет" преобразованная формула будет записана в ячейках, правее каждой из выделенных

      Не всегда удобно, т.к. формулы могут быть в смежных ячейках и подобный подход может затереть данные других ячеек
    • если выбрать "Да", то преобразованная формула будет записана в созданное примечание (примечание создается внутри кода, никаких действий не требует). Полагаю вполне удобно, т.к. данные других ячеек при этом не изменяются, а формулу всегда можно подсмотреть - и ссылки и их значения;

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

Скачать пример

  Tips_All_Get_ValFormulaText.xls (53,0 KiB, 4 866 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 25 комментариев
  1. Сергей:

    не совсем могу разобраться в вашем коде..
    могли бы сказать как "выцепить" массив ссылок на другие ячейки(или массивы) из формулы?

  2. Я бы посоветовал Вам код весь пошагово пройти и проанализировать. Т.к. он почти весь сводится именно к определению адресов. Есть в коде такая строка:
    'определяем все ссылки на другие ячейки
    вот ниже и идет определение. Адреса заносятся в массив avArr. Протрассируйте код и поймете и где и что Вам надо поменять для своей конкретной задачи.

  3. Сергей:

    спасибо

  4. Елена:

    У меня почему-то работает не во всех случаях! В ряде случаев в примечании - вместо значения указывается ссылка на ячейку.
    В чем может быть причина?

  5. Елена, я так вот не могу сказать. У меня работает. что за ссылка, напишите как выглядит в ячейке в оригинале и как после отработки кода.

  6. Формула в ячейке DN5:
    '=DL5+DF5+CZ5+CT5+CN5+CH5+CB5+BV5+BP5+BJ5+BD5+AX5+AR5+AL5+AF5 +Z5+T5+N5+H5'
    Результат работы функции:
    '=0+0+C0.465+C183.86+C0+C0+0+0+0+0+0.0645+0.375+0+0.005+4.474+0.465+183.86+0+0'
    Ошибка возникает уже в тот момент, когда выделяем из формулы подстроку, равную по длине одному из представлений ссылки:
    If Mid(sFormStr, le, Len(asAddr(lu))) = asAddr(lu)
    В этой строке кода в ссылке CH5 неверно "находится" искомая ссылка H5, поэтому далее эта ложно найденная ссылка заменяется на значение:
    sFormStr = Replace(sFormStr, asAddr(lu), avArr(li, 1), 1,1)
    Получается значение C0, вместо настоящего 0. Далее таким же образом получаются неверные значения C0.465 вместо 0, C183.86 вместо 0 и так далее.

  7. ostrov127, спасибо. Попробую этот момент "обыграть" и исправить.

  8. Спасибо огромное...класс!!!! пользуюсь постоянно теперь

  9. bardin:

    Спасибо огромное! Очень помог код! Рассчитывал курсовик в Excel, теперь надо перенести в ворд. Код сильно помог!

  10. Антон:

    Здравствуйте! Подскажите в чём может быть причина. Пользуюсь вашим макросом уже давно, столкнулся с такой проблемой: по формуле получаю допустим значение 59915,07, далее это число применяется в формуле другой, и при отображении значений этой формулы с помощью макроса выводится выражение с числом 59911 а не 59915,07))) в чём может быть причина? преподаватель всю плешь проел)

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

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

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти