У Анны Мари есть ряд ячеек, содержащих формулы, в которых используются относительные ссылки. Она хочет изменить вторую ссылку в каждой формуле с относительной на абсолютную, поэтому ей интересно, есть ли способ сделать это без необходимости редактировать формулу каждой ячейки по отдельности.

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

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

Если ваши данные расположены немного иначе (а не в столбце, как описано), вы можете попробовать использовать «Найти и заменить», чтобы выполнить редактирование за вас. Однако сначала вам нужно выяснить, есть ли согласованный шаблон во второй ссылке в формулах, которые вы хотите изменить. Например, если вы хотите изменить все ссылки на столбец D так, чтобы они были абсолютными, вы можете поступить следующим образом:

  1. Выделите диапазон ячеек, который хотите отредактировать.

  2. Нажмите Ctrl + H, чтобы открыть вкладку «Заменить» в диалоговом окне «Найти и заменить».

  3. Нажмите кнопку «Параметры», если она доступна. (См. Рис. 1.)

  4. В поле «Найти» введите заглавную букву D.

  5. В поле «Заменить на» введите «$ D» (без кавычек).

  6. Установите флажок Учитывать регистр.

  7. Щелкните «Заменить все».

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

(Вы можете обойти эту последнюю потенциальную проблему, выполнив второй поиск и замену, в котором вы найдете $$ D и замените его на $ D.) Этот подход также изменит все ссылки на столбец D, независимо от того, находятся ли они во втором ссылка или нет.

Если вы абсолютно хотите изменить только вторую ссылку в любой формуле, вам нужно создать макрос для выполнения этой работы. Макросу потребуется изучить ячейки, содержащие формулы, выбрать вторую ссылку на ячейку, а затем убедиться, что перед строкой и столбцом в ссылке есть знаки доллара. Однако это легче сказать, чем сделать; сложнее всего выбрать вторую ссылку в формуле. Как можно определить вторую ссылку для макроса из формулы, подобной следующей?

=A23+SUM(B7:B19)/C15

Является ли вторая ссылка B7, B19, B7: B19, или вы полностью пропускаете эту ссылку, потому что технически это параметр для функции, и вместо этого используете C15? Такие определения, как правило, лучше оставить на рассмотрение в каждом конкретном случае, что означает, что вы вернетесь к просмотру и, возможно, редактированию каждой ячейки индивидуально, без помощи макроса.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9897) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.