valores - Excel: rangos de listas dirigidos por fórmulas INDIRECTAS

saber si un numero esta dentro de un rango excel

Question

Tenemos algunos libros de trabajo de Excel muy grandes (docenas de pestañas, más de un MB cada uno, cálculos muy complejos) con muchas docenas, quizás cientos de fórmulas que usan la temida función INDIRECTO. Estas fórmulas se distribuyen en todo el libro de trabajo y se dirigen a varias tablas de datos para buscar valores.

Ahora necesito mover los rangos de datos que están dirigidos por estas fórmulas a una ubicación diferente en el mismo libro de trabajo.

(La razón no es particularmente relevante, pero es interesante por sí misma. Necesitamos ejecutar estas cosas en Excel Calculation Services y el éxito de la latencia de cargar cada una de las tablas más bien grandes de una en una demostró ser inaceptablemente alto. tablas en un rango contiguo para que podamos cargarlas todas de una vez).

¿Hay alguna manera de localizar todas las fórmulas INDIRECTAS que actualmente se refieren a las tablas que queremos mover?

No necesito hacer esto en línea. Con gusto tomaré algo que demore 4 horas en funcionar, siempre que sea confiable.

Tenga en cuenta que los métodos .Precedent, .Dependent, etc. solo rastrean fórmulas directas.

(Además, volver a escribir las hojas de cálculo en lo que no sea una opción para nosotros).

¡Gracias!

Answer 1

Desafortunadamente, los argumentos de INDIRECT suelen ser más complejos que eso. Aquí hay una fórmula real de una de las hojas, no la fórmula más compleja que tenemos:

=IF(INDIRECT("''"&$B$5&"''!"&$O5&"1")="","",INDIRECT("''"&$B$5&"''!"&$O5&"1"))

hm, podrías escribir un analizador simple ignorando la mayoría de los personajes y simplemente buscando las partes relevantes (en este ejemplo: "A..Z", "0..9" y "!:" etc.) pero lo harás se encuentra con problemas si los argumentos en "indirecto" son funciones.

tal vez el enfoque más seguro sería imprimir cada ocurrencia de "indirecta" en una tercera hoja. a continuación, puede agregar el resultado deseado y escribir un pequeño programa de búsqueda y reemplazo para volver a escribir los cambios.

Si "obtienes" cada celda en una hoja de cálculo enorme, es posible que termines necesitando monstruosas cantidades de memoria. Todavía estoy dispuesto a intentarlo y correr ese riesgo.

El método de PabloG de seleccionar el rango utilizado es el camino a seguir (lo agregó a mi código original). La velocidad es bastante buena, especialmente si comprueba si la celda actual contiene una fórmula. Obviamente, todo esto depende del tamaño de su libro de trabajo.

Answer 2

Podrías iterar sobre todo el libro utilizando vba (he incluido el código de @PabloG y @ 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

Este ejemplo sustituye cada aparición de "indirecta (D4)" por "indirecta (C4)". Puede cambiar fácilmente la función de reemplazo por algo más sofisticado, si tiene funciones indirectas más complicadas. El rendimiento no es tan malo, incluso para libros de trabajo más grandes.

Answer 3

Puede usar algo como esto en 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

En lugar de Debug.Print puede agregar el código que se ajuste a su gusto

Answer 4

P: "¿Hay alguna manera de localizar todas las fórmulas INDIRECTAS que actualmente se refieren a las tablas que queremos mover?"

A medida que lo leo, quiere mirar dentro de los argumentos de INDIRECT para referencias a áreas de interés. OTTOMH Escribiría VBA para usar un analizador de expresiones regulares, o incluso un INSTR simple para encontrar INDIRECTO (leer hacia adelante a la correspondencia), luego EVALUAR () la cadena dentro para convertirla a la dirección real, repetir como se requiera para INDIRECTO múltiple (...) llama y volca la fórmula y su traducción a dos columnas en una hoja.

Answer 5

No estoy seguro de cuál es la etiqueta de SO con respecto a la mención de productos con los que está conectado el escritor, pero OAK, el Operis Analysis Kit, un complemento de Excel, puede reemplazar las funciones INDIRECTAS por las referencias de celda que resuelven. Luego puede usar las herramientas de auditoría de Excel para determinar qué dependientes tiene cada rango.

Por supuesto, haría esto en una copia temporal del libro de trabajo.

Mas en

• http://www.operisanalysiskit.com/oakpruning.htm
• http://www.operisanalysiskit.com/help/2007/index.html?oakconceptpruning.htm

Dada la edad de esta pregunta, es muy posible que haya encontrado una solución alternativa o alternativa.