покупка
Как сохранить постоянную ссылку на ячейку формулы в Excel?
Когда вы используете относительную ссылку на ячейку в формуле, она автоматически настраивается, когда вы используете маркер заполнения, чтобы применить его к другому месту или скопировать и вставить в другие ячейки. См. Снимок экрана ниже.
Во многих случаях вам нужно, чтобы ссылка на ячейку оставалась постоянной, а не корректировалась автоматически. В этом руководстве мы покажем вам, как сохранить постоянную ссылку на ячейку формулы в Excel.
Сохраняйте постоянную ссылку на ячейку формулы с помощью клавиши F4
Чтобы ссылка на ячейку в формуле оставалась постоянной, вам просто нужно добавить символ $ к ссылке на ячейку, нажав клавишу F4. Пожалуйста, сделайте следующее.
1. Выделите ячейку с формулой, которую вы хотите сделать постоянной.
2. На панели формул поместите курсор в ячейку, которую вы хотите сделать постоянной, затем нажмите F4 .
В этом случае я не хочу, чтобы ссылка на ячейку A1 изменялась при перемещении формулы, поэтому я помещаю курсор на A1 в формуле и затем нажимаю F4. Смотрите скриншот:
Затем вы можете увидеть, что ссылка на ячейку A1 в формуле постоянна.
Сохраняйте постоянную ссылку на ячейку формулы всего за несколько кликов
Здесь очень рекомендую Kutools for Excel‘s Преобразовать ссылки утилита. Эта функция помогает легко преобразовать все ссылки на формулы в большом количестве в выбранном диапазоне или нескольких диапазонах в конкретный тип ссылки на формулу. Например, преобразовать относительное в абсолютное, абсолютное в относительное и т. Д.
Скачать Kutools for Excel Сейчас! ( 30 -дневная бесплатная трасса)
Давайте посмотрим, как использовать эту функцию, чтобы легко сохранить постоянную ссылку на ячейку формулы в Excel.
1. После установки Kutools for Excel, Пожалуйста, нажмите Кутулс > Больше > Преобразовать ссылки для активации Преобразование ссылок на формулы функцию.
2. Когда Преобразование ссылок на формулы появится диалоговое окно, настройте его следующим образом.
- Выберите диапазон или несколько диапазонов (удерживайте Ctrl клавиша для выбора нескольких диапазонов один за другим) вы хотите сделать ссылки постоянными;
- Выберите К абсолютному вариант;
- Нажмите OK кнопку.
Затем все относительные ссылки на ячейки в выбранном диапазоне немедленно заменяются на постоянные ссылки.
Если вы хотите получить бесплатную пробную версию ( 30 -день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Как сделать постоянную ссылку на ячейку в excel
Подскажите, пожалуйста, можно ли сделать постоянную ссылку на ячейку запроса Power Query на другом листе?
При изменении данных в запросе у меня слетают формулы на обычном листе и пишет например =Share1.1!#ССЫЛКА! вместо =Share1.1!B2.
Share1.1 это и название листа с запросом, также пробовал через диспетчер имен создавать диапазон таблицы и его прописывать в формуле вместо имени листа. не работает, слетают формулы.
Подскажите, пожалуйста, можно ли сделать постоянную ссылку на ячейку запроса Power Query на другом листе?
При изменении данных в запросе у меня слетают формулы на обычном листе и пишет например =Share1.1!#ССЫЛКА! вместо =Share1.1!B2.
Share1.1 это и название листа с запросом, также пробовал через диспетчер имен создавать диапазон таблицы и его прописывать в формуле вместо имени листа. не работает, слетают формулы. Vladimir92
Сообщение отредактировал Vladimir92 — Четверг, 26.05.2022, 16:04
Сообщение Подскажите, пожалуйста, можно ли сделать постоянную ссылку на ячейку запроса Power Query на другом листе?
При изменении данных в запросе у меня слетают формулы на обычном листе и пишет например =Share1.1!#ССЫЛКА! вместо =Share1.1!B2.
Share1.1 это и название листа с запросом, также пробовал через диспетчер имен создавать диапазон таблицы и его прописывать в формуле вместо имени листа. не работает, слетают формулы. Автор — Vladimir92
Дата добавления — 26.05.2022 в 16:00
Как сделать постоянную ссылку на ячейку в excel
Прошу подсказать, как задать ссылку на переменный номер ячейки.
Задача такая:
На Лист1 — исходные данные.
На Лист2 — формируется бланк отчета.
Каждая строка содержит данные для одного отчета.
На Лист1 всегда в одной и той же ячейке E2 вручную задается № отчета.
Отчет № 1 — формируется из 6-й строки, Отчет № 2 — из 7-й строки, Отчет № 3 — из 8-й и т.д.
В отчет просто вставляются данные ячеек одной строки.
PS Нельзя ли также автоматически сразу после задания номера в ячейке Е2 посылать Лист2 на печать?
Прикрепленные файлы
- пример.xlsx (10.74 КБ)
Пользователь
Сообщений: 8007 Регистрация: 21.12.2012
12.04.2017 15:30:53
| Цитата |
|---|
| На Лист2 — формируется бланк отчета. |
А где пример бланка?
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 15:41:06
Ок, Пример бланка такой
=Лист1!A6
=Лист1!A7
=Лист1!A8
и т.д.
Только вместо A6, A7, A8. должно стоять А(Е2+5)
В этом и заключается мой вопрос: «Как задать переменный номер ячейки?»
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
12.04.2017 15:42:39
Прикрепленные файлы
- СВАТ.xlsx (11.97 КБ)
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 15:50:46
2 Bema
Нет, Вы даете ссылку на постоянную ячейку.
А нужна ссылка на переменную ячейку, где вместо «6»- переменная функция =Е2+5
Чтобы когда Е2=1 ссылка вела на A6
при е2=2 та же ссылка вела на А7
при е2=3 — на А8 и т.д.
Изменено: СВАТ — 12.04.2017 15:52:30
Пользователь
Сообщений: 1028 Регистрация: 08.11.2016
12.04.2017 15:52:54
Не очень понравилась идея с печатью листа 2 при изменении заданной ячейки, добавил отдельную кнопку «Напечатать Лист 2».
Или Вы хотели узнать, как а втором листе вывести нужную строку из первого? (тоже сделано)
Прикрепленные файлы
- пример.xlsm (23.8 КБ)
Изменено: Wiss — 12.04.2017 15:53:21
Я не волшебник, я только учусь.
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
12.04.2017 15:54:54
А Вы на Листе1 в ячейке Е2 значения меняли? Что-то происходит?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
Пользователь
Сообщений: 8007 Регистрация: 21.12.2012
12.04.2017 16:07:04
| Цитата |
|---|
| вопрос: «Как задать переменный номер ячейки?» |
Пишите макрос на изменение ячейки Е2 .
Копируете нужную строку и переносите ее на лист2,
затем печать
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 16:23:10
Bema, Прошу прощения, теперь заметил. Спасибо.
Надеюсь, смогу по аналогии состряпать остальное
Wiss, согласен, идея с печатью была лишняя. Это я погорячился.
Пример ваш открываю, инет лагает. Заранее спасибо.
Kuzmich, копипаста не подходит. даже в виде макроса.
На Лист2 данные иначе должны быть раскиданы по ячейкам, не в одну строку.
Если бы мне была нужна только строка, я бы смог печатать их и с первого листа.
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
12.04.2017 16:26:34
СВАТ, еще такой вариант проверьте.
=ЕСЛИОШИБКА(ДВССЫЛ(«Лист1!A»&Лист1!$E$2+5&»:E»&Лист1!$E$2+5);»»)
В формуле как раз есть то, что Вы хотели
| Цитата |
|---|
| СВАТ написал: переменная функция =Е2+5 |
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
12.04.2017 16:35:51
| Цитата |
|---|
| Чтобы когда Е2=1 ссылка вела на A6 при е2=2 та же ссылка вела на А7 при е2=3 — на А8 и т.д. |
=Индекс(Лист1!A:A;E2+5)
Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 16:44:18
Bema, Wiss
Оба ваших способа работают. (С учетом исправления Wiss +5)
Просто поразительно, респект! Буду выбирать более короткую формулу )
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
12.04.2017 16:47:05
Неправильно. Нужно выбирать ЛУЧШЕ работающую )
А это не всегда самая короткая
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 16:48:58
Wiss, формула =ДВССЫЛ(«Лист1!D»&Лист1!E2+5;ИСТИНА) — уже работает.
Зачем еще оператор ЕСЛИОШИБКА?
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
12.04.2017 17:03:38
| Цитата |
|---|
| vikttur написал: Неправильно. Нужно выбирать ЛУЧШЕ работающую ) |
При прочих равных — выбираем более простой путь. Если только Вы не альпинист из песни Высоцкого. Захотелось пофлудить?
Между прочим, безоценочный способ общения — самый бесконфликтный.
Поэтому прошу избавить от общих философских неконструктивных оценок.
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
12.04.2017 17:11:13
| Цитата |
|---|
| СВАТ написал: безоценочный способ общения — самый бесконфликтный. |
| Цитата |
|---|
| СВАТ написал: Захотелось пофлудить? |
Это ли не оценка?
Не флуд, я Вам даю совет. Между прочим, ценный. И если Вы его не оценили — Ваша проблема.
ДВССЫЛ лучше не применять, если есть замена — функция пересчитывается при любом изменении на листе.
Пользователь
Сообщений: 656 Регистрация: 01.01.1970
12.04.2017 17:18:51
Не производите оценку необходимости данной вам подсказки а просто примите во внимание и никаких конфликтов не будет.
Пользователь
Сообщений: 1028 Регистрация: 08.11.2016
12.04.2017 17:46:14
СВАТ, у меня там нет ЕСЛИОШИБКА. Не стал писать так как там ошибка может быть в 2=х случаях:
1. Кривой диапазон;
2. Ошибка в ячейке из которой берутся данные.
В обоих случаях я хотел бы видеть, что у меня вылезла ошибка, а не заменять её каким — то значением.
Я не волшебник, я только учусь.
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
13.04.2017 08:39:52
| Цитата |
|---|
| vikttur написал: ДВССЫЛ лучше не применять |
Виктор, если не трудно, не могли бы Вы привести пример, где без ДВССЫЛ не обойтись.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
Пользователь
Сообщений: 9 Регистрация: 12.04.2017
13.04.2017 08:47:00
| Цитата |
|---|
| Ігор Гончаренко написал: =Индекс(Лист1!A:A;E2+5) |
Не смог добиться, чтобы эта формула у меня заработала как надо
| Цитата |
|---|
| vikttur написал: ДВССЫЛ лучше не применять, если есть замена — функция пересчитывается при любом изменении на листе. |
Что ж, спасибо за совет. Жаль. Эта формула была мне понятнее. Да,Вы правы — я тоже бываю грешен оценочен, категоричен и недипломатичен.
Вот бы Вас слышал мой шеф — он упрекает меня в чрезмерной вежливости
| Цитата |
|---|
| Wiss написал: СВАТ , у меня там нет ЕСЛИОШИБКА. |
Понятно, значит, это только для отладки. Ок. Отдельное спасибо за кнопку печати.
Правда, я тут слегка передумал — лучше я буду задавать ячейку с «№ Отчета» не на Лист1, а сразу в бланке на Лист2, соответственно сразу видеть и возможные ошибки и печатать в один клик.
Как сделать постоянную ссылку на ячейку в excel
Эта формула вводится в ТаблицеВвода в ячейку и считает СУММУ для каждого критерия $A2 из неё, смотря в ПолеПоиска и суммируя из ПоляСуммы — в ТаблицеПоиска
Реальный пример
Изменено: Jack Famous — 12.10.2021 11:20:44
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Пользователь
Сообщений: 2188 Регистрация: 15.01.2016
12.10.2021 11:01:47
Если «копипастить» —> ссылка не съезжает.
Если протягивать мышкой —> съезжает.
Изменено: Бахтиёр — 12.10.2021 12:21:23
Пользователь
Сообщений: 110 Регистрация: 18.10.2021
09.11.2021 16:13:07
| Цитата |
|---|
| написал: Если «копипастить» —> ссылка не съезжает.Если протягивать мышкой —> съезжает. |
Самый рабочий вариант. Спасибо.
Пользователь
Сообщений: 3 Регистрация: 30.05.2022
30.05.2022 13:21:43
| Цитата |
|---|
| написал: Есть такой недокументированный вариант ссылки: =Таблица1[[Расход]:[Расход]] |
Добрый день, продолжу тему. Рассчитываю на вашу помощь. При применении данного варианта фиксирования диапазона получается следующий результат вычисления формулы:
Сама формула:
=ЕСЛИ(СВОДНАЯ[@[Дох_Расх]:[Дох_Расх]]=»РАСХОД»;СУММЕСЛИ(РЕЕСТР_РАСХ[@[Номер статьи]:[Номер статьи]];СВОДНАЯ[@[Столбец9]:[Столбец9]];РЕЕСТР_РАСХ[декабрь]);СУММЕСЛИ(РЕЕСТР_ДОХ[@[Номер статьи]:[Номер статьи]];СВОДНАЯ[@[Столбец9]:[Столбец9]];РЕЕСТР_ДОХ[декабрь]))
Результат вычислений прикреплен. Результат вычислений неудовлетворительный)
Формула писалась с целью зафиксировать диапазоны таблицы для протягивания.
Исходная формула выглядела так:
=ЕСЛИ([@[Дох_Расх]]=»РАСХОД»;СУММЕСЛИ(РЕЕСТР_РАСХ[Номер статьи];[@Столбец9];РЕЕСТР_РАСХ[декабрь]);СУММЕСЛИ(РЕЕСТР_ДОХ[Номер статьи];[@Столбец9];РЕЕСТР_ДОХ[декабрь]))
Результат ее вычисления прикреплен (вычисление 2)
При протягивании исходной формулы менялись диапазоны, вариантом было только копирование формулы и изменение периода. Таким образом получается нужный вариант. Но не могу поверить, что нельзя получить нужный результат путем протягивания.
Получается при корректировке формулы и фиксировании диапазона РЕЕСТР_ДОХ[@[Номер статьи]:[Номер статьи]], результатом вычисления является ДОХОД!$D$18 (рис. результатывыч), а исходная ФОРМУЛА РЕЕСТР_ДОХ[Номер статьи] смотрит весь диапазон ДОХОД!$D$6:$D$160 (рис. вычисление 2), что и влияет в конечном итоге на корректный результат.
Коллеги помогите докопаться до истины.
Прикрепленные файлы
- Результатвыч.png (21.16 КБ)
- вычисление 2.png (10.83 КБ)