Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

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

Попробую в двух словах описать суть статьи: предположим на листе есть формула: =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, 3 539 скачиваний)


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

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

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

    Блин, Человек, да ты Гений!!! Сам Бил Гейтс до этого не до пер, а ты Гений!!! Я очень рад твоему приложению, очень рад, я с института не навижу эти буквы и цифры, а теперь есть нормальные значения ячеек!!! Скажи, от куда ты Родом, надо знать, где живут такие люди!!!

  2. Михаил:

    Ты как прометей - принес свет в мир Екселя!!! Я просто делаю и радуюсь, Я жить теперь хочу!!!
    P.S. Автору - оргомное спасибо!!!

  3. егор:

    Так а где собственно сам код?
    как его получить то ?

  4. Алексей:

    Для Excel'я под Windows можно написать более коротко:


    Dim i As Long
    Dim str, resStr As String
    Dim matchPos As Long

    str = ActiveCell.formula

    Set oRegEx = CreateObject("vbscript.regexp")
    oRegEx.Pattern = "\$*[A-Z]+\$*[0-9]+"
    oRegEx.Global = True
    Set Matches = oRegEx.Execute(str)

    resStr = ""
    For i = 0 To Matches.Count - 1
    matchPos = InStr(str, Matches.Item(i).Value)
    resStr = resStr & Mid(str, 1, matchPos - 1) & Range(Matches.Item(i).Value)
    str = Mid(str, matchPos + Matches.Item(i).Length)
    Next
    MsgBox resStr

    • Попробуйте на вложенном файле. Ссылки на другие листы и книги неправильно обрабатываются - берутся значения текущего листа, а не того, на который ссылка идет. Хорошо видно, если применить Ваш код к ячейке С3.
      Так что код надо дорабатывать.

      • Роман:

        Здравствуйте. Спасибо за код! Данную строку:
        sRez = "'" & sFormStr
        преобразовал в:
        sRez = sFormStr
        Но проблема в том, что в таком виде код не работает с нецелыми числами. То есть в примечание выводит как положено, а в ячейку нет. С целыми числами все работает.
        Подскажите пожалуйста в чем причина?

        • Причина может быть в форматах ячеек, в которые выводится результат. В любом случае код от себя ничего не меняет в числах, а выводит как есть.

          • Роман:

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

  5. Дмитрий:

    Спасибо огромное! От себя добавил в конец кода перед последним End If (где создается примечание) строку, которая задает размер окошка примечания по размеру содержимого (удобно, когда много формул друг под другом идет):
    wsParentSheet.Range(rCell.Address).Comment.Shape.TextFrame.AutoSize = True

  6. Елена:

    Спасибо огромное, Дмитрий! Вы мне очень помогли.

  7. Evgeniy:

    Отличная вещь!
    Автору Спасибо!

  8. Мария:

    Дмитрий!!! Вы ГЕНИЙ!!! Обняла б и расцеловала б при встречи!!!
    Я так долго пытала народ, как это можно сделать, и случайно вышла на ваш сайт. Столько нужного и интересного нашла. И вдруг эта статья... сижу и лыблюсь от счастья! Она мне очень помогла!
    Безмерная благодарность!!!

  9. Вадим:

    Здравствуйте! Подскажите пожалуйста следующий момент. Как сделать так что бы формула была раскрыта именно в конкретной ячейке? Данный код раскрывает формулу в соседней ячейке и закрывает нужные данные, а когда он их закидывает в примечание и при распечатке не видно значение формулы.
    Что я хочу увидеть: у меня есть формула =2+3+5, excel суммирует и выдает значение 10, а мне надо чтобы именно в этой ячейки была раскрыта формула =2+3+5 и она работала с формулами других ячеек (текстовый формат не предлагать)

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2018 Excel для всех   Войти