Tengo una hoja de cálculo de Excel con dos hojas de cálculo titulada «Ciudades» y «Datos».
Los «Datos» de la página contiene 108264 filas de datos y las columnas de progreso de todo el camino hasta la columna.

Virtud de las Ciudades de la hoja de cálculo, tengo una lista de 210 ciudades de filas B4 a B214. Junto a él (Columna C) se muestra una lista de los condes de códigos utilizados para cada ciudad (es decir, cuántos códigos hizo que la ciudad de usar). El próximo 20 columnas (Columnas D a W) debe mostrar una secuencia de los más utilizados los códigos para cada ciudad (es decir, la más común a menos común). He incluido imágenes de muestra pseudo conjunto de datos para proporcionar una representación gráfica de lo que me estoy refiriendo.

Fórmula de matriz para VBA

Fórmula de matriz para VBA

Si vamos a mirar la Ciudad «1», por ejemplo (fila 4 «Ciudades»), usted se dará cuenta de que tiene un Recuento de 5, y la que más frecuentemente se utiliza el código es de 5, 4, luego 3, luego el 2 y por último 1. Si te refieres a los «Datos» de la imagen, se puede ver la correlación.

Las fórmulas de matriz que he usado para este conjunto de la muestra es la siguiente:

En D4 de «Ciudades»

      {=IFERROR((MODE(IF(ISNUMBER(SEARCH(B4,Data!$B2:$B6)),IF(ISNUMBER(Data!$K2:$AT6),Data!$K2:$AT6)))),"")}

En E4 de «Ciudades»

{=IFERROR(MODE(IFERROR(SMALL(IF(ISNUMBER(SEARCH($B$4, Data!$B2:$B6))*ISNUMBER(1/Data!$K2:$AT6)*ISNA(MATCH(Data!$K2:$AT6,$D4:D4,0)),Data!$K2:$AT6,""),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNT(Data!$K2:$AT6)))),"")),"")}

A continuación, arrastra la fórmula de E4 en adelante, y se cuenta automáticamente la frecuencia de los comúnmente utilizados los códigos basados en los datos de la columna anterior.

El objetivo es este: para cada ciudad se señaló en «Ciudades» de la hoja de cálculo, me gustaría volver a esos 20 más comúnmente utilizados los códigos mediante la búsqueda en las Columnas B y Columnas de K a A partir de los «Datos» de la Hoja de cálculo. Así se vería la ciudad en la Columna B, a continuación, mirar a través de los códigos que se utilizan comúnmente en las Columnas K a A.

Tengo dos fórmulas de matriz que yo uso para esto (es decir, que cuenta con el más usado en el código, que dependiendo del valor en la columna anterior, devuelve el siguiente código de uso frecuente). El problema es que, debido a un gran conjunto de datos, la creación de una fórmula de matriz para cada célula se vuelve mucho tiempo, y se ralentiza la hoja de cálculo de Excel considerablemente.

Así que, esto es lo que he intentado hasta ahora:

  1. Fórmulas de matriz (también refiera por favor a la hoja adjunta)
  2. A Continuación VBAs. Primero devuelve un error en tiempo de Ejecución ‘1004’ no se puede establecer la propiedad FormulaArray de la Gama de la Clase, mientras que el segundo no hace nada.

Cualquier sugerencia o ayuda en cualquiera de acelerar las fórmulas de matriz, o modificar el VBA en consecuencia, sería muy apreciado. Si usted tiene una alternativa de VBA así, que sería apreciado demasiado.

Gracias.

Sub Option1()
     Dim r As Long
     For r = 4 To 214
        Sheet2.Cells(r, 210).FormulaArray = _
        "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C" & CStr(r) & ", Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
    Next r
 End Sub



Sub Option2()

    Sheet1.Range("C4").FormulaArray = _
        "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"

    Sheet1.Range("D4:D214").FillDown

End Sub
InformationsquelleAutor user2722253 | 2014-02-21

1 Comentario

  1. 8

    Primer consejo:

    En la parte final de su VBA fórmulas tiene "":

    ...Data!$K2:$AT108264)))),"")"

    En VBA si desea incluir citas en la fórmula, se debe utilizar doble qoutes: """" lugar "".

    Segundo consejo:

    Hay ninguna necesidad de utilizar el bucle para aplicar la fórmula para cada celda en el rango:

    For r = 4 To 214
        Sheet2.Cells(r, 210).FormulaArray = "=IFERROR(...C4,...)"
    Next r

    Su código sería mucho más rápido si desea utilizar (columna № 210 es HB):

     Sheet2.Range("HB4:HB214").FormulaArray = "=IFERROR(...C4,...)"

    Este enfoque se ajusta automáticamente a todos los parientes/referencias mixtas en la fórmula:

    • en HB4 tendría =IFERROR(...C4,...)
    • en HB5 tendría =IFERROR(...C5,...)
    • en HB214 tendría =IFERROR(...C214,...)

    Por lo tanto, trabajando código sería el siguiente:

    Sheet2.Range("HB4:HB214").FormulaArray = "=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"""")"
    • Gracias. Esto fue muy útil.
    • la Legibilidad de la punta: cuádruple citas son horribles – que se se equivocan cuando se especifica una fórmula compleja y se necesita demasiado tiempo para depurar que. Así que trate de este lugar: Public Const QQ As String * 2 = """" a colocar comillas dobles en su citado en las cadenas y usarlo como strFormula = QQ & "Today= " & QQ & " & DATE()" …Es que, o Chr(34)

Dejar respuesta

Please enter your comment!
Please enter your name here