Memoria insuficiente en Excel 2010 para ejecutar fórmulas
Tengo que hacer 2 cálculos con una cantidad excesiva de datos, les explico
1) Tengo la columna Y2:Y999001 que tiene datos concatenados
2) Tengo la columna Z2:Z999001 con fórmulas que cuentan la cantidad de veces que aparecen los números de la columna Y2:Y999001.
Las fórmulas son:
=VALOR(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1&U1&V1&W1&X1)
=CONTAR.SI($Y$2:$Y$999001;Y2)
Estas 2 fórmulas se repiten en las 999001 filas
Necesito que este proceso se realice en forma rápida y el excel lo hace en una gran cantidad de tiempo, ya que no solamente concatena cada fila sino que luego cuenta cuantas veces aparecen los números en las 999001 filas.
Como hago para que excel haga estos cálculos normalmente, hay aluna macro que pueda ayudarme
2 respuestas
.30/09/16
Hola, Diego
Digamos, en principio, que cualquier operación aplicada a casi un millón de líneas será lenta.
Por otra parte, el resultado de la segunda fórmula es redundante en cada ocurrencia, toda vez que cada fórmula indicará cuantas veces se repite esa combinación.
Por lo tanto, será más apropiado y más rápido que trabajes con una tabla dinámica.
Es decir, reemplazá la primera fórmula por la siguiente:
=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1&U1&V1&W1&X1
No parece ser de utilidad convertir a valor lo concatenado. Ahí ya le estás ahorrando un trabajo a MS Excel un millón de veces, casi.
Luego insertá una tabla dinámica que lea esa base.
En el área de diseño de la tabla:
Seleccioná el campo concatenado y colocalo en el área de filas. Eso te dejará una ocurrencia de cada combinación, sin repetidos.
Luego, ponelo de nuevo en el área Campos de Valor con la función CONTAR.
Así, por cada caso tendrás cuantas veces se repite.
Ahorrás, todas las fórmula de contar. Si y tienes un resumen para cada caso único.
Avisame si necesitás más ayuda con esto de las tablas dinámicas.
Un abrazo
Fer
.
:)
Hola! Diego (y Fer)
Veinticuatro millones de datos es... ¡Muchísimo! (Lo mires por donde lo mires).
De todos modos te dejaré una macro que -estimo- te puede brindar el conteo en unos 15 segundos (probada con la décima parte de datos, claro está):
Sub Macro434()
'------------------
'by Mario Rodríguez
'------------------
Dim Mat, Dic, Vec
Dim Q&, i&, R%, j%, iniTime!
iniTime = Timer
Mat = Range("A1:X999001"): Q = UBound(Mat): R = UBound(Mat, 2)
Set Dic = CreateObject("Scripting.Dictionary")
For i = 1 To Q
Vec = ""
For j = 1 To R: Vec = Vec & Mat(i, j): Next
If Vec <> "" Then
If Dic.Exists(Vec) Then Dic(Vec) = 1 + Dic(Vec) Else Dic(Vec) = 1
End If
If (i Mod 100000) = 0 Then DoEvents
Next
Q = Dic.Count
With Range("z1").Resize(Q)
.NumberFormat = "@": .Value = Application.Transpose(Dic.Keys)
End With
Range("aa1").Resize(Q) = Application.Transpose(Dic.Items)
MsgBox "Procesado en " & Format(Timer - iniTime, "0.00 seg.")
End
End SubEspero te sea de utilidad.
Mario R.
.
:)
Gracias Mario
Abrazo.-
Me alegra que te haya sido de utilidad.