| Цитата |
|---|
| Kuzmich написал: =A1&»+»&A2&»+»&A3 |
лучше поздно чем никогда
как бы это в макрос загнать, столько лет прошло а стандартно не решается до сих пор
макрос должен проставить в формуле значек — Амперса́нд — & у краев всех адресов ячеек (край слеваА1край справа), т.е. &А1&, а символы операторы и функции между амперсандами & ( = — ( ) / * и т.д.) взять в кавычки «-«, потому что таблицы как простыни а экспертизе нужен красивый расчет а не голые формулы со снипа и сводная таблица итоговых значений, нужно показать промежуточный красивый расчет.
Пока решения нет приходится вручную клацать ибо код писать руки и мозги не из того места видать выросли =(
Изменено: Антон Золотухин — 11.11.2020 23:31:12
Хитрости »
13 Сентябрь 2012 68143 просмотров
Отобразить в формулах вместо ссылок на ячейки значения ячеек
Попробую в двух словах описать суть статьи: предположим на листе есть формула: =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, 6 639 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
Обычно в ячейках формулы отображаются результаты вычислений в Excel, и скучно щелкать каждую ячейку, чтобы просмотреть ссылку на нее. Здесь я расскажу о трех методах отображения всех ссылок на ячейки вместо значений и наоборот в текущем листе, а также для отображения указанных ссылок на ячейки в виде текста вместо значений и наоборот.
- Показывать всю ссылку на ячейку вместо значения и наоборот в указанном диапазоне
- Показывать всю ссылку на ячейку вместо значения и наоборот в активном листе
- Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Показывать все ссылки на ячейки вместо значений и наоборот с помощью функции Показать формулу
Мы можем быстро показать все ссылки на ячейки вместо вычисленных результатов формул, нажав Формулы > Показать формулу. Смотрите скриншот ниже:
Если вам нужно снова отобразить все рассчитанные результаты в ячейках, нажмите кнопку Показать формулу снова.
Показывать ссылку на ячейку в виде текста вместо значения и наоборот в указанном диапазоне
С другой стороны, некоторые пользователи могут захотеть отобразить указанную ссылку на ячейку в виде текста вместо значений, но не все ссылки на ячейки всех ячеек формулы. Вот рекомендую попробовать Kutools for ExcelАвтора Преобразовать формулу в текст полезности и Преобразовать текст в формулу утилита.
Выделите ячейки формулы, в которых вы хотите отображать ссылки на ячейки в виде текста, а не результатов вычислений, и щелкните Кутулс > Содержание > Преобразовать формулу в текст.
Примечание: Напротив, для отображения вычисленных результатов вместо формул выберите эти формулы и нажмите Кутулс > Содержание > Преобразовать текст в формулу. Смотрите скриншот выше.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Как мы знаем, Формулы > Показать формулы могут отображать/скрывать формулы только на активном листе. Чтобы показать или скрыть все ссылки на ячейки вместо значений или наоборот на всех листах, попробуйте Kutools for Excel’s параметры просмотра функцию.
1, Нажмите Кутулс > Показать / Скрыть> Параметры просмотра. Смотрите скриншот ниже:
2. В открывшемся диалоговом окне «Параметры просмотра» установите флажок Формулы вариант, а затем щелкните Применить ко всем листам кнопки и Ok кнопку последовательно. Смотрите скриншот ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демонстрация: показывать ссылку на ячейку (как текст) вместо значения и наоборот в Excel
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (0)
Оценок пока нет. Оцените первым!
Excel для Microsoft 365 Excel для Интернета Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel на значения или данные, которые нужно использовать в формуле.
Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:
-
данные из одной или нескольких смежных ячеек на листе;
-
данные из разных областей листа;
-
данные на других листах той же книги.
Например:
|
Формула |
Объект ссылки |
Возвращаемое значение |
|---|---|---|
|
=C2 |
Ячейка C2 |
Значение в ячейке C2 |
|
=A1:F4 |
Ячейки A1–F4 |
Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter. Примечание. Эта функция не работает в Excel в Интернете. |
|
=Актив-Пассив |
Ячейки с именами «Актив» и «Пассив» |
Разность значений в ячейках «Актив» и «Пассив» |
|
{=Неделя1+Неделя2} |
Диапазоны ячеек «Неделя1» и «Неделя2» |
Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива |
|
=Лист2!B2 |
Ячейка B2 на листе Лист2 |
Значение в ячейке B2 на листе Лист2 |
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства). -
Выполните одно из следующих действий.
-
Создайте ссылку на одну или несколько ячеек. Чтобы создать ссылку, выделите ячейку или диапазон ячеек на том же листе.
Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
-
Создайте ссылку на определенное имя. Чтобы создать ссылку на определенное имя, выполните одно из указанных ниже действий.
-
Введите имя.
-
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.
-
-
-
Выполните одно из указанных ниже действий.
-
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
-
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
введите = (знак равенства).На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства) и формулу, которую нужно использовать. -
Щелкните ярлычок листа, на который нужно сослаться.
-
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки (‘).
Также можно скопировать и вставить ссылку на ячейку, а затем воспользоваться командой Ссылки на ячейки для создания ссылки на ячейку. Эту команду можно использовать в указанных ниже случаях.
-
Для отображения важных данных в более заметном месте. Предположим, существует книга с множеством листов, на каждом из которых есть ячейка, отображающая итоговые данные по другим ячейкам этого листа. Чтобы выделить такие итоговые ячейки, можно создать ссылки на них с первого листа книги, которые позволят увидеть итоговые данные из всей книги на ее первом листе.
-
Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.
-
Выделите ячейку с данными, ссылку на которую необходимо создать.
-
Нажмите клавиши CTRL+C или перейдите на вкладку Главная и в группе Буфер обмена щелкните Копировать
.
-
Нажмите клавиши CTRL+V или перейдите на вкладку Главная и в группе Буфер обмена щелкните Вставить
.По умолчанию при вставке скопированных данных отображается кнопка Параметры вставки
. -
Нажмите кнопку Параметры вставки, а затем выберите пункт Вставить связь
.
.
.
.
.-
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
-
Выполните одно из указанных ниже действий.
-
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
-
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
-
В строка формул выделите ссылку в формуле и введите новую ссылку
. -
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
-
-
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если после ввода ссылки на ячейку в формулу задается имя для ссылки на ячейку, иногда требуется заменить существующие ссылки на ячейки определенными именами.
-
Выполните одно из указанных ниже действий.
-
Выделите диапазон ячеек, содержащих формулы, в которых необходимо заменить ссылки на ячейки определенными именами.
-
Чтобы заменить ссылки именами во всех формулах листа, выделите одну пустую ячейку.
-
-
На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.
-
Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.
-
Выделите ячейку с формулой.
-
В строке формул строка формул
выделите ссылку, которую нужно изменить. -
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства). -
Выделите ячейку или диапазон ячеек на том же листе. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
-
Выполните одно из указанных ниже действий.
-
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
-
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
-
Щелкните ячейку, в которую нужно ввести формулу.
-
В строка формул
введите = (знак равенства) и формулу, которую нужно использовать. -
Щелкните ярлычок листа, на который нужно сослаться.
-
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки (‘).
-
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
-
Выполните одно из указанных ниже действий.
-
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
-
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
-
В строка формул
выделите ссылку в формуле и введите новую ссылку.
-
-
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
Выделите ячейку с формулой.
-
В строке формул строка формул
выделите ссылку, которую нужно изменить. -
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
На чтение 2 мин. Просмотров 249 Опубликовано 23.05.2021
Например, у вас есть список формул, показанный как = Sheet1! (ячейка) +1 , теперь вы хотите заменить все Sheet1 в формулах с Sheet3 , как показано на скриншоте ниже, как вы можете быстро решить это в Excel?
Перемещение или замена ссылок в формуле Excel
Перемещение или замена ссылок в формуле Excel
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!
Если вы хотите переместить или заменить конкретную ссылку во всех формулах диапазона или листа, вы можете применить Найти и заменить , чтобы решить эту проблему.
1. Выберите диапазон или лист, на котором вы хотите использовать формулы, нажмите клавиши Ctrl + H , чтобы открыть диалоговое окно Найти и заменить . См. Снимок экрана:
2. Нажмите Параметры , чтобы развернуть диалоговое окно, введите ссылку, которую вы хотите найти, в текстовое поле Найти , введите новую ссылку, которую вы используете в Заменить с помощью и выберите Формулы в раскрывающемся списке Искать в . См. Снимок экрана:
3. Нажмите Заменить все , тогда все ссылки в выделенном или активном листе будут заменены новым.
Совет.
1. Если вы хотите переместить ссылки, вам просто нужно ничего не оставлять в текстовом поле Заменить на в текстовом поле Найти и заменить диалоговое окно.
2. Если вы хотите найти или заменить символы или ссылки в нескольких книгах или листах, вы можете применить расширенную функцию Найти и заменить в Kutools for Excel , которая может сделаю вам одолжение. Полная функциональность без ограничений в течение 60 дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.
