Обновить абсолютную ссылку, если указанная ячейка является частью скопированных данных в Excel

Question

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

    | A | B        |
|---|---|----------|
| 1 | 1 | =$A1 + 1 |

B1 будет иметь значение 2.

Теперь, если я скопирую B1 в C1, я хочу, чтобы C1 снова ссылался на A1. Excel сделает это.

    | A | B        | C        |
|---|---|----------|----------|
| 1 | 1 | =$A1 + 1 | =$A1 + 1 |

Но если я скопирую A1:B1 в C1:D1 (таким образом, я скопирую ссылочную ячейку вместе со ссылочными ячейками), Excel все равно будет, согласно абсолютному определению ссылки:

    | A | B        | C | D        |
|---|---|----------|---|----------|
| 1 | 1 | =$A1 + 1 | 1 | =$A1 + 1 |

Но так как я включил A1 (на который ссылаются), я хочу, чтобы D1 ссылался на C1 вместо A1, поэтому:

    | A | B        | C | D        |
|---|---|----------|---|----------|
| 1 | 1 | =$A1 + 1 | 1 | =$C1 + 1 |

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

Основной причиной, по которой я хочу использовать абсолютную ссылку, является сценарий, подобный этому (простейшая форма):

    | A | B         | C         |
|---|---|-----------|-----------|
| 1 |   |         4 |         2 |
|---|---|-----------|-----------|
| 2 | 1 | =$A2 + B1 | =$A2 + C1 |

Идея состоит в том, чтобы добавить значения выше формулы к общему значению $ A2. Вы можете скопировать ячейку B2 в C2, не обновляя формулу вручную; $ A2 останется $ A1, а B1 будет обновлен до C1. Теперь, если я хочу скопировать весь этот блок A1:C2 в другое место, формулы в соотв. вставки из B2 и C2, как и в предыдущих примерах, будут по-прежнему ссылаться на $ A2, а я хочу, чтобы они ссылались на вставленную ячейку из него. Я мог бы избежать этой проблемы, не имея абсолютной ссылки для начала. Но тогда я больше не мог копировать ячейку B2 в C2, так как тогда C2 сказал бы B2 + C1, в то время как я хотел A2 + C1.

Кто-нибудь знает простой и управляемый способ решения этой проблемы?

Заметьте, кстати, что Excel не так уж строг с абсолютными ссылками; если вы вырезаете целые столбцы, он обновит абсолютные ссылки. Если вы вырезаете / вставляете указанную ячейку, она обновит абсолютные ссылки на нее. Я думаю, что вышеупомянутый сценарий также должен быть поддержан ..

Answer 1

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

В лучшем случае у меня есть обходной путь для вас:

1. Скопируйте ячейки, которые вы хотите дублировать (вместе с их исходными данными, как вы описали выше)
2. Вставьте их в другой лист в тех же координатах (A1:C2 в вашем примере), и вы обнаружите, что у вас есть набор скопированных формул, не ссылающихся на исходный лист.
3. Выберите свою дублированную область и перетащите ее в координаты, которые вы хотите, чтобы она обитала на вашем оригинальном листе. Это «обновит» абсолютные ссылки.
4. Скопируйте и вставьте ячейки обратно на исходный лист, и они сохранят свои «обновленные» ссылки.