Я пытаюсь сделать условное форматирование, чтобы цвет ячейки изменился, если значение отличается от значения в ячейке слева от него (каждый столбец - месяц, в каждой строке - затраты на определенный объект. хотят легко отслеживать изменения цен в течение нескольких месяцев.)
Я могу сделать это на ячейку и форматировать ее, но я бы хотел, чтобы общая формула применима ко всему листу.
Спасибо!
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Самая короткая наиболее совместимая версия:
=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.
При создании условного форматирования задайте диапазон, к которому он относится к тому, что вы хотите (весь лист), затем введите относительную формулу (удалите знаки $), как если бы вы только форматировали верхний левый угол.
Excel правильно применит форматирование к остальным ячейкам соответственно.
В этом примере, начиная с B1, левая ячейка будет равна A1. Просто используйте это - не требуется расширенная формула.
Если вы ищете что-то более продвинутое, вы можете играть с column(), row() и indirect(...).
Если вы измените ссылку на ячейку для использования нотации R1C1 (вкладка "Инструменты", "Общие" ), вы можете использовать простую нотацию и вставить ее в любую ячейку.
Теперь ваша формула просто:
=RC[-1]
Вместо написания очень длинного:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
Вы можете просто написать:
=OFFSET(*Name of your Cell*,0,-1)
Таким образом, например, вы можете записать в Cell B2:
=OFFSET(B2,0,-1)
для ссылки на ячейку B1
Все еще спасибо Джейсону Янг! Я бы никогда не придумал это решение без вашего ответа!
заполнить ячейку A1 со следующей формулой:
=IF(COLUMN(A1)=1;"";OFFSET(A20;0;-1))&"1"
Затем автоматически расширяется вправо, вы получаете
1| A | B | C | ect ect
2| 1| 11| 111| ect ect
Если смещение выходит за пределы диапазона доступной ячейки, вы получаете #REF! ошибка.
Надеюсь, вам понравится.
Еще проще:
=indirect(address(row(), column() - 1))
OFFSET возвращает ссылку относительно текущей ссылки, поэтому, если косвенное возвращает правильную ссылку, вам она не нужна.
При создании Пользовательской функции я обнаружил, что другие ответы с участием функций OFFSET и INDIRECT не могут быть применены.
Вместо этого вы должны использовать Application.Caller для ссылки на ячейку, в которой используется пользовательская функция (UDF). второй шаг, вы конвертируете индекс столбца в соответствующее имя столбца.
Наконец, вы можете ссылаться на левую ячейку, используя активный рабочий лист Функция диапазона.
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
Вы можете использовать VBA script, который изменяет условное форматирование выделения (возможно, вам придется соответствующим образом скорректировать условие и форматирование):
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
Я наткнулся на эту нить, потому что я хотел всегда ссылаться на "ячейку слева", но CRUCIALLY - энергонезависимым способом (без СЛОЖНЫХ, КОСВЕННЫХ и подобных бедствий). Глядя в Интернет вверх и вниз, нет ответов. (Этот поток на самом деле не дает ответа.) После некоторого поворота я наткнулся на самый удивительный метод, который мне нравится делиться с этим сообществом:
Предположим, что начальное значение 100 в E6. Предположим, что я введу дельта в это значение в F5, скажем 5. Затем мы вычислили значение продолжения (105) в F6 = E6 + F5. Если вы хотите добавить еще один шаг, просто: просто скопируйте столбец F в столбец G и введите новую дельта в G5.
Это то, что мы делаем, периодически. Каждый столбец имеет дату, и эти даты ДОЛЖНЫ БЫТЬ в хронологическом порядке (чтобы помочь с MATCH и т.д.). Часто случается так, что мы забываем ввести шаг. Теперь предположим, что вы хотите вставить столбец между F и G (чтобы догнать ваше упущение) и скопировать F в новый G (чтобы повторно заполнить формулу продолжения). Это НИЧЕГО КОРОТКОГО полного бедствия. Попробуйте - теперь H6 скажет = F6 + H5 и NOT (как мы это абсолютно необходимо) = G6 + H5. (Новый G6 будет правильным.)
Чтобы сделать эту работу, мы можем запутать этот банальный расчет самым удивительным образом: F6 = индекс ($ E6: F6; 1; столбцы ($ E1: F1) -1) + F5. Скопируйте вправо, и вы получите G6 = index ($ E6: G6; 1; столбцы ($ E1: G1) -1) + G5.
Это никогда не должно работать, не так ли? Циркулярная ссылка, понятно! Попробуйте и удивляйтесь. Кажется, что Excel понимает, что хотя диапазон INDEX охватывает ячейку, которую мы пересчитываем, эта ячейка сама не адресуется индексом и, следовательно, не создает циклическую ссылку.
Итак, теперь я дома и сушу. Вставьте столбец между F и G и мы получим именно то, что нам нужно: значение продолжения в старом H будет ссылаться на значение продолжения, которое мы вставили в новый G.
Почему бы просто не использовать:
=ADDRESS(ROW(),COLUMN()-1)
Для тех, кто ищет энергонезависимый ответ, вы можете сделать это, используя функцию INDEX в именованной формуле.
Name Manager (Ctrl + F3)NewScope: выберите Workbook В Refers to: введите формулу:
=INDEX(!A1:!A2, 1)
Нажмите OK и закройте Name Manager
Это говорит Excel, чтобы всегда смотреть на значение непосредственно слева от текущей ячейки, и будет динамически меняться при выборе разных ячеек. Если имя используется само по себе, оно предоставляет значение ячейки, но в диапазоне оно использует ссылку. Отдайте должное этому ответу о ссылках на ячейки для идеи.
Я думаю, что это самый простой ответ.
Используйте "Имя" для ссылки на смещение.
Скажем, вы хотите суммировать столбец (столбец A) полностью, но не включая ячейку, содержащую суммирование (скажем, ячейка A100); сделай это:
(Я предполагаю, что вы используете ссылки A1 при создании Имени; впоследствии можно переключиться на R1C1)
= СУММ (А1: OneCellAbove)
Выберите весь лист и выберите ДОМОЙ> Стили - Условное форматирование, Новое правило..., Используйте формулу, чтобы определить, какие ячейки форматировать, и Форматируйте значения, где эта формула верна:
=A1<>XFD1
Format..., выберите выбор форматирования, OK, OK.