Excel: перечислите диапазоны, являвшиеся целью КОСВЕННЫХ формул
5 ответов
Вы могли выполнить итерации по всей Рабочей книге с помощью vba (я включал код от @PabloG и @euro-micelli):
Sub iterateOverWorkbook()
For Each i In ThisWorkbook.Worksheets
Set rRng = i.UsedRange
For Each j In rRng
If (Not IsEmpty(j)) Then
If (j.HasFormula) Then
If InStr(oCell.Formula, "INDIRECT") Then
j.Value = Replace(j.Formula, "INDIRECT(D4)", "INDIRECT(C4)")
End If
End If
End If
Next j
Next i
End Sub
Этот пример substitues каждое возникновение "косвенного (D4)" с "косвенным (C4)". Можно легко подкачать функцию замены с чем-то более сложным, если у Вас есть более сложные косвенные функции. Производительность не состоит в том что плохо, даже для больших Рабочих книг.
Q: "Есть ли какой-либо способ определить местоположение всех КОСВЕННЫХ формул, которые в настоящее время относятся к таблицам, которые мы хотим переместить?"
Когда я считал его, Вы хотите посмотреть в аргументах КОСВЕННЫХ для ссылок на сферы интересов. OTTOMH, которые я записал бы VBA для использования синтаксического анализатора регулярного выражения или даже простого INSTR для нахождения КОСВЕННЫМ (чтение передают соответствию), затем ОЦЕНИВАЮТ () строку внутри, чтобы преобразовать его в исполнительный адрес, повториться как требуется для нескольких КОСВЕННЫХ (...) звонит, и выведите формулу и ее перевод в два столбца в листе.
Можно использовать что-то вроде этого в VBA:
Sub ListIndirectRef()
Dim rRng As Range
Dim oSh As Worksheet
Dim oCell As Range
For Each oSh In ThisWorkbook.Worksheets
Set rRng = oSh.UsedRange
For Each oCell In rRng
If InStr(oCell.Formula, "INDIRECT") Then
Debug.Print oCell.Address, oCell.Formula
End If
Next
Next
End Sub
Вместо Отладки. Печать можно добавить код для удовлетворения вкусу
К сожалению, аргументы КОСВЕННЫХ обычно более сложны, чем это. Вот фактическая формула от одного из листов, не наиболее сложная формула, которую мы имеем:
=IF(INDIRECT("'"&$B$5&"'!"&$O5&"1")="","",INDIRECT("'"&$B$5&"'!"&$O5&"1"))
гм, Вы могли записать простой синтаксический анализатор путем игнорирования большинства символов и просто поиска соответствующих частей (в этом примере: "A.. Z", "0.. 9 дюймов и"!": и т.д.), но Вы столкнетесь с проблемами, если аргументами в "косвенном" будут функции.
возможно, более безопасный подход должен был бы распечатать каждое происшествие "косвенных" в третьем листе. Вы могли затем добавить желаемый вывод и записать маленький поиск и заменить программу для обратной записи изменений.
Если Вы "получаете" каждую ячейку в огромной электронной таблице, Вы могли бы закончить тем, что нуждались в чудовищных объемах памяти. Я все еще готов попытаться взять на себя тот риск.
Метод PabloG выбора используемого диапазона является способом пойти (добавил он в мой исходный код). Скорость довольно хороша, особенно если Вы проверяете, содержит ли текущая ячейка формулу. Очевидно, это все зависит от размера Вашей рабочей книги.
Я не уверен, каков этикет SO относительно упоминания продуктов, с которыми связан писатель, но OAK, Operis Analysis Kit, надстройка Excel, может заменить КОСВЕННЫЙ функции по ссылкам на ячейки, которые они разрешают. Затем вы можете использовать инструменты аудита Excel, чтобы определить, какие иждивенцы имеют каждый диапазон.
Вы бы, конечно, сделали это с временной копией книги.
Подробнее на
- http://www.operisanalysiskit.com/oakpruning.htm
- http: //www.operisanalysiskit .com / help / 2007 / index.html? oakconceptpruning.htm
Учитывая возраст этого вопроса, вы, возможно, нашли альтернативное решение или обходной путь.