Формула Excel для ссылки «ЯВЛЯЕТСЯ ВЛЕВО»

Я пытаюсь сделать условное форматирование так, чтобы цвет ячейки изменился, если значение отличается от значения в ячейке слева от него (каждый столбец - месяц, в каждой строке указаны расходы на определенный объект. Я хочу отслеживать легко меняет цены за месяцы.)

Я могу сделать это для каждой ячейки и перетащить форматирование, но я хотел бы, чтобы общая формула применялась ко всей рабочей таблице.

Спасибо!

3.02.2009 14:20:34
... и в случае, если кто-то ищет это ... чтобы =INDIRECT("RC[1]",0)
prograhammer 18.09.2014 21:08:55
Это очень хороший вопрос. К сожалению, я не уверен, правильно ли я понимаю, что оригинальный постер хотел, чтобы его ячейки менялись без необходимости выполнять условное форматирование, а затем вставлять средство рисования формата в каждую ячейку. Допустим, у нас месяцы в строках, а в бананах бананы, яблоки и апельсины. В матрице указаны цены. Как мы сможем сделать что-то, что бы автоматически ЦВЕТА КЛЕТКИ КРАСНОГО, если бы произошло изменение цены с предыдущего месяца на текущий месяц? Если есть также автоматический способ закрасить клетки зеленым, если цена идет вниз, и красным, если
Bryan 29.01.2015 18:33:32
14 ОТВЕТОВ
РЕШЕНИЕ
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
88
29.04.2009 13:46:44
Использование FIVE редко используемых функций, требующих проверки, вместо простой относительной ссылки A1 или RC [-1]? Это как путешествие из Бруклина в Бронкс с остановкой в ​​Уолла Уолла, штат Вашингтон!
John Machin 9.07.2009 00:51:50
Почему бы не это вместо этого = НЕПРЯМОЙ (АДРЕС (ROW (), КОЛОННА () - 1))
Prometheus 2.02.2018 18:44:20
У «Косвенного» есть очень много проблем, включая (но не ограничиваясь этим) то, что он (1) однопоточный и (2) изменчивый (возможно, каскадное принуждение всей вашей книги пересчитывать при каждом изменении. Не используйте его, если производительность имеет значение) .
John Joseph 30.08.2018 21:05:34

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

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

В этом примере, начиная с B1, левая ячейка будет A1. Просто используйте это - никакая продвинутая формула не требуется.


Если вы ищете что - то более продвинутый, вы можете играть с column(), row()и indirect(...).

11
3.02.2009 14:27:01

Вы можете использовать сценарий VBA, который изменяет условное форматирование выделения (возможно, вам придется соответствующим образом изменить условие и форматирование):

For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" & i.Offset(0, -1).Address
With i.FormatConditions(1).Font
    .Bold = True
End With
Next i
0
3.02.2009 14:37:18

Если вы измените ссылку на ячейку, чтобы использовать нотацию R1C1 (Инструменты | Параметры, вкладка Общие), вы можете использовать простую нотацию и вставить ее в любую ячейку.

Теперь ваша формула просто:

=RC[-1]
7
8.07.2009 23:12:54

Самая короткая наиболее совместимая версия:

=INDIRECT("RC[-1]",0)

«RC [-1]» означает один столбец слева. «R [1] C [-1]» - слева внизу.

Второй параметр 0 означает, что первый параметр интерпретируется с использованием записи R1C1.

Другие варианты:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Слишком долго, на мой взгляд. Но полезно, если относительное значение является динамическим / полученным из другой ячейки. например:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)

Самый простой вариант:

= RC[-1]

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

74
10.10.2011 14:07:32

заполните ячейку А1 следующей формулой:

 =IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"

Затем автоматически расширяется вправо, вы получаете

 1|  A  |  B  |  C  |  ect ect
 2|    1|   11|  111|  ect ect

Если смещение выходит за пределы диапазона доступной ячейки, вы получите #REF! ошибка.

Надеюсь, ты повеселишься.

1
2.08.2012 14:42:41

Еще проще:

=indirect(address(row(), column() - 1))

OFFSET возвращает ссылку относительно текущей ссылки, поэтому, если косвенная возвращает правильную ссылку, она вам не нужна.

1
19.03.2014 16:07:05
Никогда не делай этого. НЕПРЯМОЙ ломает дерево зависимостей - это катастрофа.
Ollie2893 5.01.2017 12:01:50

Вместо того, чтобы писать очень долго:

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)

Вы можете просто написать:

=OFFSET(*Name of your Cell*,0,-1)

Так, например, вы можете написать в ячейку B2 :

=OFFSET(B2,0,-1)

сослаться на ячейку B1

Все еще спасибо Джейсон Янг !! Я бы никогда не придумал это решение без твоего ответа!

4
2.05.2015 20:20:24
Это не полезно. Если вы уже знаете, что ваша ячейка B2, вы можете просто написать B1 для ячейки слева и вообще не использовать смещения. Дело в том, чтобы сделать что-то совершенно общее.
Matthew Read 13.03.2017 02:17:58
Это очень удобно, если вы знаете, с какой ячейки вы начинаете, и только смещение, которое вы хотите сделать, является динамическим, например, = OFFSET (B2,0, A2). В каком контексте я его использовал.
Sam Fed 30.07.2018 16:00:31
Это ответ, который я искал! Я хотел, чтобы кто-то мог выбрать одну ячейку, которая представляет целую строку, и чтобы все автоматически менялось, чтобы использовать параметры в этой строке. Спасибо!
B T 31.05.2019 03:39:09

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

Вместо этого вы должны использовать Application.Callerссылку на ячейку, в которой была использована пользовательская функция (UDF). На втором шаге вы конвертируете индекс столбца в имя соответствующего столбца.
Наконец, вы можете ссылаться на левую ячейку, используя функцию Range активной таблицы .

Function my_user_defined_function(argument1, argument2)
    ' Way to convert a column number to its name copied from StackOverflow
    ' http://stackoverflow.com/a/10107264
    ' Answer by Siddarth Rout (http://stackoverflow.com/users/1140579/siddharth-rout)
    ' License (if applicable due to the small amount of code): CC BY-SA 3.0
    colName = Split(Cells(, (Application.Caller(1).Column - 1)).Address, "$")(1)

    rowNumber = Application.Caller(1).Row

    left_cell_value = ActiveSheet.Range(colName & rowNumber).Value

    ' Now do something with left_cell_value
1
9.07.2015 13:42:28

Я наткнулся на этот поток, потому что я хотел всегда ссылаться на «ячейку слева», но КРАСИЛЬНО энергонезависимым способом (без смещения, косвенного и аналогичных бедствий). Просматривая веб вверх и вниз, ответов нет. (Эта ветка на самом деле также не дает ответа.) После некоторых раздумий я наткнулся на самый удивительный метод, которым я хотел бы поделиться с этим сообществом:

Предположим, что начальное значение 100 в E6. Предположим, я ввожу дельту этого значения в F5, скажем 5. Затем мы вычислим значение продолжения (105) в F6 = E6 + F5. Если вы хотите добавить еще один шаг, просто: просто скопируйте столбец F в столбец G и введите новую дельту в G5.

Это то, что мы делаем, периодически. У каждого столбца есть дата, и эти даты ДОЛЖНЫ БЫТЬ в хронологическом порядке (чтобы помочь с соответствием и т. Д.) Время от времени случается так, что мы забываем сделать шаг. Теперь предположим, что вы хотите вставить столбец между F и G (чтобы наверстать упущенное) и скопировать F в новый G (чтобы заполнить формулу продолжения). Это НИЧЕГО КОРОТКОГО от полной катастрофы. Попробуйте - H6 теперь скажет = F6 + H5, а НЕ (как нам абсолютно необходимо) = G6 + H5. (Новый G6 будет правильным.)

Чтобы сделать эту работу, мы можем запутать этот банальный расчет самым удивительным образом F6 = index ($ E6: F6; 1; столбцы ($ E1: F1) -1) + F5. Скопируйте правильно, и вы получите G6 = индекс ($ E6: G6; 1; столбцы ($ E1: G1) -1) + G5.

Это никогда не должно работать, верно? Циркуляр, ясно! Попробуйте и удивитесь. В Excel, похоже, понимают, что, хотя диапазон INDEX охватывает ячейку, которую мы пересчитываем, сама эта ячейка не рассматривается INDEX и, следовательно, НЕ создает циклическую ссылку.

Так что теперь я дома и сухо. Вставьте столбец между F и G, и мы получим именно то, что нам нужно: значение продолжения в старом H будет ссылаться на значение продолжения, которое мы вставили в новый G.

0
5.01.2017 11:59:35

Почему бы просто не использовать:

=ADDRESS(ROW(),COLUMN()-1)
0
12.04.2017 15:30:03

Сделайте именованную формулу "LeftCell"

Для тех, кто ищет энергонезависимый ответ, вы можете сделать это, используя функцию INDEX в именованной формуле.

  1. Выберите ячейку A2
  2. Открыть Name Manager(Ctrl + F3)
  3. щелчок New
  4. Назовите его «LeftCell» (или как вы предпочитаете)
  5. Для Scope:выберитеWorkbook
  6. В Refers to:, введите формулу:

    =INDEX(!A1:!A2, 1)

  7. Нажмите OKи закройтеName Manager

Это говорит Excel, чтобы всегда смотреть на значение непосредственно слева от текущей ячейки, и будет динамически меняться при выборе разных ячеек. Если имя используется само по себе, оно предоставляет значение ячейки, но в диапазоне оно использует ссылку. Отдайте должное этому ответу о ссылках на идеи.

0
6.11.2018 00:15:28

Я думаю, что это самый простой ответ.

Используйте «Имя» для ссылки на смещение.

Скажем, вы хотите суммировать столбец (столбец A) полностью, но не включая ячейку, содержащую суммирование (скажем, ячейка A100); сделай это:

(Я предполагаю, что вы используете ссылки A1 при создании имени; впоследствии можно переключиться на R1C1)

  1. Нажмите в любом месте листа не в верхнем ряду - скажем, ячейка D9
  2. Определите именованный диапазон, называемый, скажем, «OneCellAbove», но замените поле «RefersTo» на «= D8» (без кавычек)
  3. Теперь в Cell A100 вы можете использовать формулу
    = СУММ (А1: OneCellAbove)
0
13.05.2019 14:37:00

Выберите весь лист и выберите ДОМОЙ> Стили - Условное форматирование, Новое правило ..., Используйте формулу, чтобы определить, какие ячейки форматировать, и Форматируйте значения, где эта формула верна :

=A1<>XFD1

Format...Выберите выбор форматирования OK, OK.

0
29.06.2019 10:30:11