Как узнать какие ячейки ссылаются на данную

Как узнать какие ячейки ссылаются на данную

Проверка есть ли ссылки на ячейки

Мария К:
Прошу прощения если ввела Вас в заблуждение.

Мне необходимо определить существуют ли ячейки, ссылающиеся на указанную.
Т.е. определить участвует ли данная ячейка в каких-либо расчетах в книеге.

Я могу нажать просто на иконку «Зависимые ячейки» и увидеть результат. Но как я понимаю эта операция работает только для одной ячейки, а мне надо проверить весь лист.

Дмитрий Щербаков(The_Prist):
Мария, я выложил выше код. К нему осталось лишь цикл прикрутить. Но делать это нет желания, т.к. Вы даже не написали подходит ли он. Непонятно, что вообще делать надо. Вот получили мы все адреса ссылок. Дальше что? Их может быть 10,20, 50 и т.д. А на какие-то и не одна ячейка будет ссылаться. Если их закрашивать — будет бесполезная цветная каша на листе.

vikttur:
Не знаю, сработает ли:
запомнить данные ячейки, удалить ячейку, пересчитать книгу — если есть ошибка, то и есть ссылки.
Знатоки-кодописцы — идея может жить или в зародыше ее.

Дмитрий Щербаков(The_Prist):
Не, Вить. Пересчет книги ошибку не даст, только если каждую ячейку проверять. Но ошибка внутри формулы может быть и не следствием удаления этой ячейки. В общем работы в таком случае больше, чем в моем коде. Ведь надо понять какие ячейки ссылаются на указанную. Т.е. по сути ты предлагаешь поудалять все ячейки, кроме рабочей, плюс проверок и запоминаний лишних кучу сделать надо будет. А удалив ячейку, которая никак не влияет на другие мы ошибок не выявим.
Хотя может не так понял твою задумку.

P.S. Может у меня что-то поломалось, но я свой код вижу — и он работает. Но, видимо, вижу его только я 🙂 Хотя, если нужно определить на какие ячейки влияет указанная. Тут очень много проблем. Не помню, чтобы из VBA был простой способ это определить. Надо искать.

Дмитрий Щербаков(The_Prist):
Хотя вот, накропал:
Код: (vb)
Sub test()
Dim rAC As Range, li As Long, le As Long, lu As Long
Dim oSp
Set rAC = ActiveCell
GetDependents rAC ‘на какие влияет
GetPrecedents rAC ‘влияющие
End Sub
Function GetDependents(ByVal rCell As Range)
Dim lPresedCnt As Long
lPresedCnt = 1
On Error Resume Next
With rCell
.ShowDependents False

Do
.NavigateArrow False, lPresedCnt, 1
If Err.Number <> 0 Then Exit Do
If Selection.Address(External:=True) <> .Address(External:=True) Then
MsgBox Selection.Address(External:=True)
Else
Exit Do
End If
lPresedCnt = lPresedCnt + 1
Loop
End With
rCell.ShowDependents True
End Function
Function GetPrecedents(ByVal rCell As Range)
Dim lPresedCnt As Long
lPresedCnt = 1
On Error Resume Next
With rCell
.ShowPrecedents False
Do
.NavigateArrow True, lPresedCnt, 1
If Err.Number <> 0 Then Exit Do
If Selection.Address(External:=True) <> .Address(External:=True) Then
MsgBox Selection.Address(External:=True)
Else
Exit Do
End If
lPresedCnt = lPresedCnt + 1
Loop
End With
rCell.ShowPrecedents True
End Function

Как узнать какие ячейки ссылаются на данную

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как узнать какие ячейки ссылаются на данную

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

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

Для того, чтобы автоматически перейти на требуемую ячейку, необходимо:

1. Выбрать ячейку для проверки и перейти в меню ФОРМУЛЫ .
   
2. Нажать на кнопку «Влияющие ячейки»
3. При наличии ссылок на другие листы, мы увидим стрелку с указанием на другой лист.
   
4. Необходимо кликнуть два раза по стрелке и вызвать окно перехода.
   
5. В открывшемся окне , выбираем ссылку на необходимый лист и нажимаем ОК .
   
6. Программа выделит целевую ячейку , влияющую на наши расчеты.

Данный способ также позволяет увидеть все внешние ссылки и проверить все исходные данные по очереди.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:

Excel: Ссылки относительные и абсолютные

Часто при использовании формул в Excel после ввода формулы в одну ячейку необходимо скопировать или распространить ее на блок ячеек.

При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок.

Ссылка в Excel — адрес ячейки или связного диапазона ячеек.

Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.

Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Ссылки в Excel бывают 3-х типов:

• Относительные ссылки (пример: A1);
• Абсолютные ссылки (пример: $A$1);
• Смешанные ссылки (пример: $A1 или A$1).

Относительные ссылки

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

Пример.

В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.

При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7 формула изменяется (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).

Теперь формула в ячейке А7 ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).

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

1. Если формулу не предполагается копировать в другие ячейки.
2. Если формулу необходимо скопировать в идентичные ячейки.

Абсолютные ссылки

Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка. Для этого перед символами ссылки устанавливаются символы «$» (формат записи $А$1).

Абсолютные ссылки в формулах используются в случаях:

1. Необходимости применения в формулах констант.
2. Необходимости фиксации диапазона для проведения расчетов.

Пример.

В диапазоне А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию каждого сотрудника и поместим в диапазоне В1:В5.

Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.

Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Так как в ячейках диапазона С2:С5 нет значений, то в диапазоне В2 : В5 получаем нули.
Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.

• выделите ячейку В1
• в Строке формул поставьте знак «$» перед буквой столбца и адресом строки $С$1. Более быстрый способ — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед или после 1) и нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
• нажмите ENTER

Формула приняла вид « =А1*$С$1».
Маркером заполнения протяните полученную формулу вниз.

Теперь диапазон В2: В5 заполнен значениями премий сотрудников.

Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знаки «$».