Авторитарная ссылка синтаксиса диапазона Excel

Ответ 1

Я не знаю, есть ли список форматов, потому что на самом деле есть только формат 2: A1 и формат R1C1. Фактически вы не указываете формат диапазона, когда используете A1: C3, потому что: на самом деле это оператор диапазона.

Варианты $A $1, $A1, A $1 - это только те варианты, которые определяют, что при копировании ссылки координата перед $не должна меняться, но в противном случае координата может измениться относительно копии.

R1C1 является более сложным, поскольку он позволяет относительность. R3C2 является абсолютной ссылкой на Row 3, Column 2 (B3 в нотации A1), а использование фигурных скобок [] в ссылке R1C1 указывает, что оно относится к текущей ячейке R [-2] C [1] из ячейки R3C2 даст R1C3 (C1).

Почти забыл. Также может быть ссылка на ячейки на другом листе. 'Лист2'! IV256

Я использую

(((\w*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+) 

как регулярное выражение для идентификации ссылок на ячейки в формате A1

Ответ 2

Проблема заключается в том, что существует множество операторов (Range, Intersect, implicit intersect и т.д.), функции (INDEX, OFFSET, CHOOSE, INDIRECT + пользовательские функции), Определенные имена и структурированные ссылки в таблице и т.д., которые могут быть de-referenced для предоставления действительной ссылки диапазона. Таким образом, чтобы выполнить полную работу, нужно разбор произвольных формул Excel. И это также зависит от версии Excel.

Если все, что вы хотите сделать, это работать со стандартными явными ссылками диапазона, обычный трюк состоит в том, чтобы преобразовать строку в нотацию R1C1 и работать с ней. Синтаксис ссылок R1C1 достаточно хорошо описан в документации Excel.

Существует также описание BNF формул Excel, доступных где-то, но я потерял ссылку.

Ответ 3

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

Если строки/столбцы вставлены в Excel live (в отличие от взлома книги в другом приложении), любые ссылки автоматически обновляются на новый адрес.

Ответ 4

Это похоже на забавное упражнение. Я собираюсь перечислять как можно больше, и, надеюсь, другие люди могут указать на те, о которых я забыл/пропустил/не знал.

Разнообразное руководство к тому, что вы просите, и источник для любого, кто ищет дополнительную документацию: http://www.excelfunctions.net/Excel-Reference-Styles.html

Я собираюсь начать с 5 широких категорий - A1, R1C1, Таблицы, Формулы и ссылки VBA. Я буду игнорировать другие программы, которые могут взаимодействовать с Excel на данный момент (хотя я мог бы добавить в Python, если это официально добавлено)

Все примеры будут использовать стиль ссылки A1, поскольку они более интуитивно понятны для неопытного пользователя.

A1 Тип ссылки:

A1 указывает нам координаты данной ячейки. Алфавитно-цифровая часть сообщает нам, в каком столбце мы находимся, а числовая часть сообщает нам, в какой строке мы находимся.

Ссылки на ячейки:

A1 - базовая ссылка на ячейку. Ссылки на ячейку A1, и при перетаскивании формул через столбцы и строки это изменится. Например, если ваша формула находится в C1, и вы переместите ее на D2, теперь она будет ссылаться на B2.

Хорошее использование: сравнение двух листов друг с другом. ='Sheet1'!A1='Sheet2'!A1 будет сравнивать данные в листе 1 с листом 2 и давать true/false, если они совпадают или нет. Формулу можно легко сдвинуть сбоку и вверх/вниз для дополнительных сравнений.

$A1 - блокирует столбец. Когда вы перетаскиваете формулы, строка изменится, но столбец не будет. Полезно, если вы всегда хотите ссылаться на один и тот же столбец в формуле. Например, если ваша формула находится в C1, и вы переместите ее на D2, теперь она будет ссылаться на $A2.

Полезный пример: формулы в вспомогательных столбцах. A1 = $C1*2 будет аккуратно скользить вниз без каких-либо проблем, и если вы переместите его в столбец B, он будет продолжать ссылаться на C.

A$1 - Это блокирует строку. Когда вы перетаскиваете формулы, столбец изменится, но строка не будет. Полезно, если вы всегда хотите ссылаться на одну и ту же строку в формуле. Например, если ваша формула находится в C1, и вы переместите ее на D2, теперь она будет ссылаться на B $1.

$A$1 - Это блокирует ссылку на ячейку. Независимо от того, как вы перетаскиваете формулы вокруг, он будет продолжать ссылаться на ячейку A1. Например, если ваша формула находится в C1, и вы переместите ее на D2, теперь она будет ссылаться на $A $1.

Полезный пример: постоянный множитель для всех чисел.

Ссылки по диапазонам:

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

A1 Литература разбивается следующим образом:

Alphanumberic часть - какой столбец мы находимся Числовая часть. В какой строке мы находимся $ - Блокировка части сразу после

A1:B2 - ссылается на сетку ячеек с A1, находящуюся в верхнем левом углу, а B2 - в правом нижнем углу. Обе части формулы будут скользить при перемещении. Обычно рекомендуется не иметь разблокированных ссылок в формулах, применяемых для нескольких ячеек, поскольку диапазон ссылок также будет перемещаться. Например, если у вас есть = Sum (A1: B2) в C1, и вы переместите его на D2, он преобразуется в = Sum (B2: C3).

Разблокированные ссылки диапазона чаще всего вызывают проблемы в формулах vlookup, где диапазон ссылок заканчивается изменением, когда люди сдвигают формулу вниз.

$A$1:$B$2 - ссылается на сетку ячеек с A1, находящуюся в верхнем левом углу, а B2 - в правом нижнем углу. Эта ссылка на диапазон не изменится, даже если она перемещена. Например, если у вас есть = Sum ($ A $1: $B $2) в C1, и вы переместите его на D2, он преобразуется в = Сумма ($ A $1: $B $2)

$A$1:A2 - Это блокирует первую ячейку, но оставляет вторую часть ссылки гибкой. Это очень полезно, если вы хотите увидеть "Все, что произошло до сих пор". Например, если вы нумеруете список, в сочетании с =countifs ( "Какой экземпляр появления является этим?" ) Например, если у вас есть = Sum ($ A $1: A2) в C1, и вы переместите его на D2, он преобразуется в = Сумма ($ A $1: B3)

Подобные эффекты можно использовать с $A$1:B1, идущими горизонтально.

A:A - Это дает весь столбец A. Так как он разблокирован, он будет скользить. Полезно для захвата всего в данной колонке. Например, если у вас есть = Sum (A: A) в C1, и вы переместите его на D2, он преобразуется в = Sum (B: B)

$A:A - Это дает весь столбец A. Это будет расширяться, когда вы переходите, чтобы захватить больше столбцов. Я не могу придумать для этого немедленное практическое применение. Например, если у вас есть = Sum ($ A: A) в C1, и вы переместите его на D2, он преобразуется в = Sum ($ A: B)

$A:$A - Это дает весь столбец A. Это заблокировано для столбца A, даже если вы перемещаете формулы вокруг. Например, если у вас есть = Sum ($ A: $A) в C1, и вы переместите его на D2, он останется как = Сумма ($ A: $A)

Менее известны строки:

1:1 - Вся первая строка. Разблокирована. Например, если у вас есть = Sum (1:1) в C2, и вы переместите его на D3, он преобразуется в = Sum (2: 2)

$1:1 - вся первая строка. Частично заблокирован. Например, если у вас есть = Sum ($ 1:1) в C2, и вы переместите его на D3, он преобразуется в = Sum ($ 1: 2)

$1:$1 - Вся первая строка. Полностью заблокирован. Например, если у вас есть = Sum ($ 1: $1) в C2, и вы переместите его в D3, он останется как = Сумма ($ 1: $1)

Ссылки R1C1

R1C1 является скорее эталонным стилем, а не стилем координат. Это может быть чрезвычайно полезно, так как ваши формулы во всех ваших ячейках выглядят одинаково и значительно облегчают ввод скользящих формул в VBA.

R1C2 разбивается как таковой:

R - Строки 1 - строка 1 ИЛИ [1] - Мы на 1 строку ниже нашей текущей ячейки C - Столбцы 1 - столбец 1 ИЛИ [1] - У нас 1 столбец из нашей текущей ячейки

Другими словами, если у вас нет скобок, вы ссылаетесь на соответствующую ячейку или столбец. Если у вас есть скобки, у вас есть относительная ссылка.

Ссылки на ячейки:

R3C7 - абсолютная ссылка - 3-я строка, 7-я колонка. Это будет то же самое, что сказать $G$3 в стиле A1, как описано выше.

R[3]C7 является частично абсолютной, частично относительной ссылкой. Это требует "Дайте мне ячейку на 3 строки вниз от текущей ячейки, в столбце 7" (Которая является столбцом G) Если у меня есть это в ячейке A1, я буду ссылаться на ячейку G4. Если я переведу его на ячейку B2, я буду ссылаться на ячейку G5.

R[2]C[-2] - полная относительная ссылка. Вы также можете ссылаться на более ранние столбцы или строки, как указано знаком -. Если бы у меня была эта формула в ячейке C1, это было бы ссылкой на ячейку A3. Если я переведу формулу на D2, теперь я буду ссылаться на ячейку B4.

Ссылки на диапазон:

Ссылки на таблицы

Таблицы упрощают и интуитивно делают ссылки на другие ячейки и диапазоны. Они разбиваются следующим образом:

TableName [ColumnName]

Ссылки на формулы

Ссылки VBA

Преобразование VBA из письма в колонку

Что-то, возникающее несколько раз, это изменение номеров столбцов на буквы столбцов. Я нашел этот очень полезный фрагмент кода (где-то в Stack Overflow, в настоящее время у меня нет ссылки), чтобы конвертировать номера столбцов в столбцы для удобства использования в VBA.

Function Col_Letter(lngCol As Long) As String
  'Converts a number (usually generated from an application.match function) to a letter. For example 1 turns into A, 5 turns into E, etc.
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function  

********* В стадии строительства **********