Самосознание для ячейки, столбца и строки в функциях листа

где F13 – ячейка, которую вы должны ссылаться:

=CELL("Row",F13) yields 13; its row number =CELL("Col",F13) yields 6; its column number; =SUBSTITUTE(ADDRESS(1,COLUMN(F13)*1,4),"1","") yields F; its column letter 

Для самой ячейки для саморекламы:

 INDIRECT(ADDRESS(ROW(), COLUMN())) 

Чтобы ячейка могла самостоятельно ссылаться на свою колонку:

 INDIRECT(ADDRESS(1,COLUMN()) & ":" & ADDRESS(65536, COLUMN())) 

Чтобы ячейка могла самостоятельно ссылаться на свою строку:

 INDIRECT(ADDRESS(ROW(),1) & ":" & ADDRESS(ROW(),256)) or INDIRECT("A" & ROW() & ":IV" & ROW()) 

Числа указаны за 2003 год и ранее, используйте столбец: XFD и строку: 1048576 для 2007+.

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

Я не вижу необходимости в косвенном, особенно для условного форматирования.

Самый простой способ самонаведения ячейки, строки или столбца – относиться к ней обычно, например, «= A1» в ячейке A1, и сделать ссылку частично или полностью относительной. Например, в условной формуле форматирования для проверки того, есть ли значение в первом столбце строк различных ячеек, введите следующее с выделенным A1 и скопируйте при необходимости. Условное форматирование всегда будет ссылаться на столбец А для строки каждой ячейки:

 = $A1 <> "" 

Для нелетучего решения, как насчет в 2007 году:

 for cell =INDEX($A$1:$XFC$1048576,ROW(),COLUMN()) for column =INDEX($A$1:$XFC$1048576,0,COLUMN()) for row =INDEX($A$1:$XFC$1048576,ROW(),0) 

У меня есть странная ошибка в Excel 2010, где она не будет принимать самую последнюю строку или столбец для этой формулы (строка 1048576 и столбец XFD), поэтому вам может понадобиться ссылаться на эти короткие. Не уверен, что это то же самое для любых других версий, поэтому оцените отзывы и изменения.

и за 2003 год (индекс INDEX стал нестабильным в 97 году):

 for cell =INDEX($A$1:$IV$65536,ROW(),COLUMN()) for column =INDEX($A$1:$IV$65536,0,COLUMN()) for row =INDEX($A$1:$IV$65536,ROW(),0) 

Существует лучший способ, который безопаснее и не замедлит ваше приложение. Как настроен Excel, ячейка может иметь либо значение, либо формулу; формула не может ссылаться на свою собственную ячейку. В противном случае вы получите бесконечный цикл, так как новое значение вызовет другой расчет ….

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

Например:

Столбец A является истинным или ложным, столбец B содержит денежное значение, столбец C содержит следующую формулу:

 =B1 

Теперь, чтобы вычислить, что столбец B будет выделен желтым в условном формате, только если столбец A равен True, а столбец B больше нуля …

 =AND(A1=True,C1>0) 

Затем вы можете скрыть столбец C

В функции UDF для листа VBA вы используете Application.Caller для получения диапазона ячеек (ячеек), которые содержат формулу, называемую UDF.

Текущая колонка вычисляется по:

Способ 1:

= ЛЕВЫЙ (АДРЕС (СТРОКА (), Column (), 4,1), LEN (АДРЕС (СТРОКА (), Column (), 4,1)) – LEN (СТРОКА ()))

Способ 2:

= ЛЕВЫЙ (АДРЕС (СТРОКА (), Column (), 4,1), INT ((колонка () – 1) / 26) + 1)

Моя текущая строка вычисляется по:

= ПРАВЫЙ (АДРЕС (СТРОКА (), Column (), 4,1), LEN (СТРОКА ()))

поэтому косвенная ссылка на Sheet2! My Column, но другая строка, указанная в столбце A моей строки:

Способ 1:

= INDIRECT ( "Лист2!" & LEFT (АДРЕС (СТРОКА (), COLUMN (), 4,1), LEN (АДРЕС (СТРОКА (), COLUMN (), 4,1)) – LEN (СТРОКА ())) & INDIRECT (АДРЕС (СТРОКА (), 1,4,1)))

Способ 2:

= ДВССЫЛ ( "Лист2!" & ЛЕВЫЙ (АДРЕС (СТРОКА (), Column (), 4,1), INT ((колонка () – 1) / 26) +1) & ДВССЫЛ (АДРЕС (СТРОКА (), 1,4 , 1)))

Поэтому, если A6 = 3, а моя строка – 6, а мой Col – C, возвращается содержимое «Sheet2! C3»,

Поэтому, если A7 = 1, а моя строка – 7, а мой Col – D, возвращает содержимое «Sheet2! D1»

Я искал решение этого и использовал косвенный, найденный на этой странице, но я нашел его довольно длинным и неуклюжим для того, что я пытался сделать. После небольшого исследования я нашел более элегантное решение (по моей проблеме) с использованием нотации R1C1 – я думаю, вы не можете смешивать разные стили нотации, не используя VBA.

В зависимости от того, что вы пытаетесь сделать с самоналоженной ячейкой, что-то вроде этого примера должно получить ячейку для ссылки на нее, где ячейка F13:

 Range("F13").FormulaR1C1 = "RC" 

И затем вы можете ссылаться на ячейки в относительных позициях на эту ячейку, например, где ваша ячейка F13, и вам нужно ссылаться на G12.

 Range("F13").FormulaR1C1 = "R[-1]C[1]" 

Вы, по сути, говорите Excel, чтобы найти F13, а затем переместите вниз по 1 строке и вверх по одному столбцу.

Как это вписывалось в мой проект, было применить vlookup в диапазоне, где значение поиска было относительно каждой ячейки в диапазоне, без необходимости отдельно указывать каждую ячейку поиска:

 Sub Code() Dim Range1 As Range Set Range1 = Range("B18:B23") Range1.Locked = False Range1.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],DATABYCODE,2,FALSE),"""")" Range1.Locked = True End Sub 

Мое значение поиска – это ячейка слева от каждой ячейки (столбец -1) в моем диапазоне DIM'd, а DATABYCODE – это именованный диапазон, на который я смотрю.

Надеюсь, что это мало смысла? Подумал, что стоило выбраться в микс как еще один способ подойти к проблеме.

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

 =ROW(A2)-1 

Допускается линейка ячейки A1 (эта формула будет поступать в ячейку A1.

Это позволяет избежать использования косвенных () и index (), но все же работает.