Anónimo
Comparar vectores y buscar datocercano
Hola. Estoy tratando de encontrar la media y la mediana de unos datos pero esta complicado. Se que con las funciones promedio y mediana las podría sacar, pero el problema es que necesito estos valores solo para aquellas celdas que cumplen unas condiciones de todo el rango.
A ver me explico mejor. La matriz que tengo es algo así:
A B C D
1 1995 2 06 13546
2 2008 4 61 12000
3 2004 6 45 234000
4 1999 1 22 7834
5 2002 3 38 56950
Aparte tengo dos vectores uno en la columna G y otro en la fila 8:
G
1 1999
2 2000
3 2008
4 2002
...
A B C
7
8 45 38 61
Vale. Ahora necesito encontrar la media y la mediana de la columna DE de la matriz, pero solo teniendo en cuenta los valores ubicados en filas que cumplan: 1) el año de la columna A de la matriz coincida con algún año de los que están en el vector de la columna G, y 2) el valor columna C que son sectores coincida con alguno de los sectres que están en el vector fila en la fila 8.
El problema también es que son muchos datos. La matriz es de 5800 filas, el vector columna en G es de 49 filas y el vector fila en 8 es de 13 columnas.
Estoy estresadisima y llevo 2 días tratando de encontrar una solución. El problema es que no se si esto seria más fácil solo en excel o usando una macro, pero igual tampoco se usar macros. :S
Muchas gracias!
PD: Es urgente es para mi tesis!
A ver me explico mejor. La matriz que tengo es algo así:
A B C D
1 1995 2 06 13546
2 2008 4 61 12000
3 2004 6 45 234000
4 1999 1 22 7834
5 2002 3 38 56950
Aparte tengo dos vectores uno en la columna G y otro en la fila 8:
G
1 1999
2 2000
3 2008
4 2002
...
A B C
7
8 45 38 61
Vale. Ahora necesito encontrar la media y la mediana de la columna DE de la matriz, pero solo teniendo en cuenta los valores ubicados en filas que cumplan: 1) el año de la columna A de la matriz coincida con algún año de los que están en el vector de la columna G, y 2) el valor columna C que son sectores coincida con alguno de los sectres que están en el vector fila en la fila 8.
El problema también es que son muchos datos. La matriz es de 5800 filas, el vector columna en G es de 49 filas y el vector fila en 8 es de 13 columnas.
Estoy estresadisima y llevo 2 días tratando de encontrar una solución. El problema es que no se si esto seria más fácil solo en excel o usando una macro, pero igual tampoco se usar macros. :S
Muchas gracias!
PD: Es urgente es para mi tesis!
1 Respuesta
Respuesta de antares18
1
Me parece que hoy es tu día de suerte
Para la media
Public Function media_años_sectores(Columna_calcular_media As Range, Columna_años As Object, Años_a_comparar As Range, Columna_sectores As Object, Sectores_a_comparar As Range) As Double
Dim existe_año As Boolean
Dim existe_sector As Boolean
Dim elementos As Double
Dim sumatorio As Double
Dim col_calc_media As Variant
Dim col_años As Variant
Dim col_sectores As Variant
col_calc_media = Columna_calcular_media.Value
col_años = Columna_años.Value
col_sectores = Columna_sectores.Value
elementos = 0
existe_año = False
existe_sector = False
Dim i As Double
For i = 1 To UBound(col_calc_media, 1)
On Error Resume Next
'Si es un número es que existe en el grupo de datos, si no da un error
' MsgBox (Columna_calcular_media.AddressLocal(False, False))
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_años(i, 1), Años_a_comparar, 0))) Then
If Err = 0 Then ' Si es 0 no hay fallo, o sea existe el numero
existe_año = True
Else
existe_año = False
End If
End If
On Error Resume Next
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_sectores(i, 1), Sectores_a_comparar, 0))) Then
If Err = 0 Then
existe_sector = True
Else
existe_sector = False
End If
End If
If (existe_año And existe_sector) Then
sumatorio = sumatorio + col_calc_media(i, 1)
elementos = elementos + 1
End If
Next i
media_años_sectores = sumatorio / elementos
End Function
Para la mediana
Public Function mediana_años_sectores(Columna_calcular_mediana As Range, Columna_años As Object, Años_a_comparar As Range, Columna_sectores As Object, Sectores_a_comparar As Range) As Double
Dim existe_año As Boolean
Dim existe_sector As Boolean
Dim celdas_cumplen_condicion As String
Dim rango_celdas_cumple_condicion As String
Dim col_calc_mediana As Variant
Dim col_años As Variant
Dim col_sectores As Variant
col_calc_mediana = Columna_calcular_mediana.Value
col_años = Columna_años.Value
col_sectores = Columna_sectores.Value
existe_año = False
existe_sector = False
Dim i As Double
For i = 1 To UBound(col_calc_mediana, 1)
On Error Resume Next
'Si es un número es que existe en el grupo de datos, si no da un error
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_años(i, 1), Años_a_comparar, 0))) Then
If Err = 0 Then ' Si es 0 no hay fallo, o sea existe el numero
existe_año = True
Else
existe_año = False
End If
End If
On Error Resume Next
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_sectores(i, 1), Sectores_a_comparar, 0))) Then
If Err = 0 Then
existe_sector = True
Else
existe_sector = False
End If
End If
If (existe_año And existe_sector) Then
celdas_cumplen_condicion = celdas_cumplen_condicion + Columna_Celda(Columna_calcular_mediana) + CStr(i) + ","
End If
Next i
celdas_cumplen_condicion = Left(celdas_cumplen_condicion, Len(celdas_cumplen_condicion) - 1)
mediana_años_sectores = WorksheetFunction.Median(Range(celdas_cumplen_condicion))
End Function
Para la media
Public Function media_años_sectores(Columna_calcular_media As Range, Columna_años As Object, Años_a_comparar As Range, Columna_sectores As Object, Sectores_a_comparar As Range) As Double
Dim existe_año As Boolean
Dim existe_sector As Boolean
Dim elementos As Double
Dim sumatorio As Double
Dim col_calc_media As Variant
Dim col_años As Variant
Dim col_sectores As Variant
col_calc_media = Columna_calcular_media.Value
col_años = Columna_años.Value
col_sectores = Columna_sectores.Value
elementos = 0
existe_año = False
existe_sector = False
Dim i As Double
For i = 1 To UBound(col_calc_media, 1)
On Error Resume Next
'Si es un número es que existe en el grupo de datos, si no da un error
' MsgBox (Columna_calcular_media.AddressLocal(False, False))
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_años(i, 1), Años_a_comparar, 0))) Then
If Err = 0 Then ' Si es 0 no hay fallo, o sea existe el numero
existe_año = True
Else
existe_año = False
End If
End If
On Error Resume Next
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_sectores(i, 1), Sectores_a_comparar, 0))) Then
If Err = 0 Then
existe_sector = True
Else
existe_sector = False
End If
End If
If (existe_año And existe_sector) Then
sumatorio = sumatorio + col_calc_media(i, 1)
elementos = elementos + 1
End If
Next i
media_años_sectores = sumatorio / elementos
End Function
Para la mediana
Public Function mediana_años_sectores(Columna_calcular_mediana As Range, Columna_años As Object, Años_a_comparar As Range, Columna_sectores As Object, Sectores_a_comparar As Range) As Double
Dim existe_año As Boolean
Dim existe_sector As Boolean
Dim celdas_cumplen_condicion As String
Dim rango_celdas_cumple_condicion As String
Dim col_calc_mediana As Variant
Dim col_años As Variant
Dim col_sectores As Variant
col_calc_mediana = Columna_calcular_mediana.Value
col_años = Columna_años.Value
col_sectores = Columna_sectores.Value
existe_año = False
existe_sector = False
Dim i As Double
For i = 1 To UBound(col_calc_mediana, 1)
On Error Resume Next
'Si es un número es que existe en el grupo de datos, si no da un error
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_años(i, 1), Años_a_comparar, 0))) Then
If Err = 0 Then ' Si es 0 no hay fallo, o sea existe el numero
existe_año = True
Else
existe_año = False
End If
End If
On Error Resume Next
If (WorksheetFunction.IsNumber(WorksheetFunction.Match(col_sectores(i, 1), Sectores_a_comparar, 0))) Then
If Err = 0 Then
existe_sector = True
Else
existe_sector = False
End If
End If
If (existe_año And existe_sector) Then
celdas_cumplen_condicion = celdas_cumplen_condicion + Columna_Celda(Columna_calcular_mediana) + CStr(i) + ","
End If
Next i
celdas_cumplen_condicion = Left(celdas_cumplen_condicion, Len(celdas_cumplen_condicion) - 1)
mediana_años_sectores = WorksheetFunction.Median(Range(celdas_cumplen_condicion))
End Function
Se me olvidaban estas. Las has de poner también
Public Function Columna_Celda(rango) As String
Dim a As String
a = rango.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Columna_Celda = Left$(a, esletra_pos(a))
End Function
Function esletra_pos(Celda) As Integer 'Hasta que posicion hay letras, usado para Rangos
Dim i As Integer
Dim a As String
i = 1
For i = 1 To Len(Celda)
a = LCase(Mid(Celda, i, 1))
If Not (Asc(a) > 96 And Asc(a) < 123) Then
Exit For
End If
esletra_pos = esletra_pos + 1
Next i
End Function
Public Function Columna_Celda(rango) As String
Dim a As String
a = rango.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Columna_Celda = Left$(a, esletra_pos(a))
End Function
Function esletra_pos(Celda) As Integer 'Hasta que posicion hay letras, usado para Rangos
Dim i As Integer
Dim a As String
i = 1
For i = 1 To Len(Celda)
a = LCase(Mid(Celda, i, 1))
If Not (Asc(a) > 96 And Asc(a) < 123) Then
Exit For
End If
esletra_pos = esletra_pos + 1
Next i
End Function
Gracias! Perdón, pero para visual no se nada. ¿Cómo las hago funcionar? Las pego en visual, ¿guardo la macro y luego como las llamo?
Atento, no te pierdas. Esto es para guardarlas.
Has de crear un módulo y meter la fórmula dentro. Si no, no te la va a encontrar.
Suponiendo que no sabes hacer esto. Te explico.
Para crear el módulo, haz lo siguiente.
1 - Aprieta el círculo con el icono del office, lo más arriba a la izquierda.
2 - Le das a opciones de Excel, abajo a la derecha.
3 - Te sale un cuadro de diálogo, está señalado de color naranja Más frecuentes. Señala Personalizar
4 - En la primera lista, arriba te pone Comandos más utilizados. Abre la lista y señala Ficha Programador
5 - Busca Visual Basic y apretás al botón agregar y le das a aceptar
6 - En la barra de tareas que crea office por defecto te saldrá el botón del Visual Basic que has agregado antes.
Por defecto la barra está arriba del todo.
7 - Aprietas el botón de visual Basic.
8 - A la izquierda verás que pone Proyecto - VBAProject y debajo Propiedades - Hoja1
9 - En la parte de Proyecto - VBAProject.
Arriba del todo pone VBAProject(Nombre del libro que usas). A mi me pone VBAProject(Libro1.xlsm)
En esa parte aprietas botón derecho - Insertar - >Módulo
10 - Se te crea una carpeta llamada Módulos y un archivo llamado Módulo1. Haces click en el Módulo1
11 - Pegas la función de arriba aquí, debajo de Option Explicit, el option este da igual, pasa de él
Ya esta
Para poder usarlas
La das al botón de fx y abajo del todo pone Definidas por el usuario y te saldrán las funciones. Es la de media y mediana, las otras son auxiliares.
Los parámetros
1o columna D
2o columna A
3o columna G
4o columna C
5o Fila 8, pero si lo pasas a una columna, no habrá error.
Los mismos parámetros para la mediana
Has de crear un módulo y meter la fórmula dentro. Si no, no te la va a encontrar.
Suponiendo que no sabes hacer esto. Te explico.
Para crear el módulo, haz lo siguiente.
1 - Aprieta el círculo con el icono del office, lo más arriba a la izquierda.
2 - Le das a opciones de Excel, abajo a la derecha.
3 - Te sale un cuadro de diálogo, está señalado de color naranja Más frecuentes. Señala Personalizar
4 - En la primera lista, arriba te pone Comandos más utilizados. Abre la lista y señala Ficha Programador
5 - Busca Visual Basic y apretás al botón agregar y le das a aceptar
6 - En la barra de tareas que crea office por defecto te saldrá el botón del Visual Basic que has agregado antes.
Por defecto la barra está arriba del todo.
7 - Aprietas el botón de visual Basic.
8 - A la izquierda verás que pone Proyecto - VBAProject y debajo Propiedades - Hoja1
9 - En la parte de Proyecto - VBAProject.
Arriba del todo pone VBAProject(Nombre del libro que usas). A mi me pone VBAProject(Libro1.xlsm)
En esa parte aprietas botón derecho - Insertar - >Módulo
10 - Se te crea una carpeta llamada Módulos y un archivo llamado Módulo1. Haces click en el Módulo1
11 - Pegas la función de arriba aquí, debajo de Option Explicit, el option este da igual, pasa de él
Ya esta
Para poder usarlas
La das al botón de fx y abajo del todo pone Definidas por el usuario y te saldrán las funciones. Es la de media y mediana, las otras son auxiliares.
Los parámetros
1o columna D
2o columna A
3o columna G
4o columna C
5o Fila 8, pero si lo pasas a una columna, no habrá error.
Los mismos parámetros para la mediana