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

ProgramBox

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

Post author:admin
Запись опубликована:31 января, 2023
Post category:Вопросы по программированию

#excel

Вопрос:

С помощью специального символа ‘$’ я могу запретить формуле Excel изменять свою ссылку при копировании самой ячейки (например, ‘$ A $ 1’).
Но существует ли также специальный символ, который не позволяет формуле Excel изменять свою ссылку при перемещении ячейки, на которую ссылается ссылка?

Вот пример:

У меня есть:

Теперь я перемещаю ячейки ‘A1: A3’ вниз на одну строку. Таким образом, формулы в ячейках ‘C1: C3’ меняются:

Но я не хочу, чтобы формулы в ячейках ‘C1: C3’ менялись. Они должны оставаться неизменными:

Ответ №1:

Попробуйте formula:

 =IF(INDEX(A:A,ROW(1:1))=INDEX(B:B,ROW(1:1)),"Same","Not same")

формула всегда работает, независимо от того, какой столбец был удален, кроме столбцов A или B

1. Да, я думаю, что это самое элегантное решение, потому что здесь вы все еще можете видеть столбцы и строки. Большое спасибо.

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

Ответ №2:

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

Вместо этого используйте Index(A:A,1) . Это всегда будет возвращать значение из первой строки столбца A.

 =if(index(A:A,1)=index(B:B,1),"same","not")

Редактировать после комментария: если это не соответствует вашим целям, возможно, вам нужно изменить дизайн вашей электронной таблицы, чтобы положение вещей не менялось постоянно. Похоже, что вы добавляете новые данные в начало списка.

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

1. Да, это работает для моего примера. Но это скорее обходной путь, и он не такой приятный, потому что с помощью ‘INDEX’ вам нужно вводить каждую формулу (ячейки ‘C1: C3’) вручную, потому что вы не можете скопировать формулу, перетащив нижний правый угол ячейки ‘C1’. Гораздо удобнее было бы использовать специальный символ, который предотвращает изменение ссылки на формулу при перемещении ячейки, на которую ссылается ссылка, например, специальный символ «$», который предотвращает изменение ссылки на формулу при копировании самой ячейки. Но я боюсь, что этот специальный символ еще недоступен.

2. Не стесняйтесь вносить это предложение в excel.uservoice.com . Именно так работает Excel.

Ответ №3:

Используйте формулу в C1 :

 =IF(INDEX(A:A,ROW(ZZ1))=INDEX(B:B,ROW(ZZ1)),"Same","Not same")

и скопируйте вниз.

1. Да, это выглядит довольно красиво. Но, как написано ниже, это скорее обходной путь. Гораздо удобнее было бы использовать специальный символ, который предотвращает изменение ссылки на формулу при перемещении ячейки, на которую ссылается ссылка, например, специальный символ «$», который предотвращает изменение ссылки на формулу при копировании самой ячейки. Но я боюсь, что этот специальный символ еще недоступен. Примечание: мы также можем использовать ‘ROW (Z1)’ вместо ‘ROW (ZZ1)’, не так ли?

2. Использование строки (некоторой ячейки) — это больше работы для Excel, чем использование только числа, которое вы хотите использовать. Но что более важно, использование Row(ZZ1) изменится на Row(ZZ2) , если строка вставлена перед строкой 1, поэтому вся концепция использования Index() с фиксированным номером не будет работать, если вы используете строку (адрес ячейки) вместо числа.

3. @Seeky Да, вы можете использовать любой адрес ячейки из первой строки, если к этой ячейке не прикасаться.

4. @teylyn Во-первых, я ответил на вопрос how to prevent an Excel formula from changing its reference when the referenced cell is moved , и формула, которую я предлагаю, делает это. Во-вторых, если вставлена целая строка, пользователь также, вероятно, ожидает, что формула переместится вниз со всеми ее ссылками. Поэтому, пожалуйста, отмените свой понижающий голос.

5. Во-первых, это не мой downvote, поэтому я ничего не могу с этим поделать. Во-вторых, ваш подход будет нарушен, если пользователь также вставит строку выше ZZ1, поэтому он не очень надежный.