Prorrateo en excel
No estoy seguro que su nombre sea prorrateo, pero bueno así lo llamo yo.
Existe alguna función o que haga algo más o menos así:
N = (N1 * C1 + N2 * C2 + ... Nn * Cn)/
(N1 + N2 + ... Nn)
(Siendo N y C, cualquiera celda!).
No he encontrado ni podido crear algo parecido.
¿Cómo podría crear una función que pudiese usar desde la hoja?
Existe alguna función o que haga algo más o menos así:
N = (N1 * C1 + N2 * C2 + ... Nn * Cn)/
(N1 + N2 + ... Nn)
(Siendo N y C, cualquiera celda!).
No he encontrado ni podido crear algo parecido.
¿Cómo podría crear una función que pudiese usar desde la hoja?
1 Respuesta
Respuesta de César Mera
1
Pues no se si exista lago similar... en todo caso te hice una función que te hace el cálculo solicitado (lo que entendí, ¿eso es como una serie de yonosequé cierto? Me parece que cuando vi cálculo integral vi algo similar)
La función es la siguiente:
Function Prorrateo(N As Range, C As Range) As Double
Dim i As Integer
Dim Suma As Double
Dim Resultado As Double
Dim Rango1 As Range
Dim Rango2 As Range
Set Rango1 = N
Set Rango2 = C
Var = N.Cells.Count
If Rango1.Cells.Count = Rango2.Cells.Count And _
Rango1.Columns.Count = 1 And _
Rango2.Columns.Count = 1 Then
For i = 1 To Rango1.Cells.Count
Debug.Print
Resultado = Resultado + (Rango1.Cells(i).Value * Rango2.Cells(i).Value)
Suma = Suma + Rango1.Cells(i).Value
Next i
End If
Prorrateo = Resultado / Suma
Set Rango1 = Nothing
Set Rango2 = Nothing
End Function
Pégala en el editor de VBA en un módulo nuevo, eso te permitirá visualizar la función por insertar función y en categoría seleccionas Definidas por el Usuario, la función te va a pedir dos rangos de valores, el primero corresponde a las N y el segundo a las C.
Los rangos deben ser iguales en tamaño y los valores deben estar distribuidos en columnas... prúebalo y me cuentas.
Si no me explico bien o algo es muy complicado envíame tu dirección de mail y te envío un libro que tiene incorporada la función para que la veas funcionando.
De lo contrario te agradeceré finalizar la pregunta con una valoración acorde con la calidad y oportunidad de la respuesta.
La función es la siguiente:
Function Prorrateo(N As Range, C As Range) As Double
Dim i As Integer
Dim Suma As Double
Dim Resultado As Double
Dim Rango1 As Range
Dim Rango2 As Range
Set Rango1 = N
Set Rango2 = C
Var = N.Cells.Count
If Rango1.Cells.Count = Rango2.Cells.Count And _
Rango1.Columns.Count = 1 And _
Rango2.Columns.Count = 1 Then
For i = 1 To Rango1.Cells.Count
Debug.Print
Resultado = Resultado + (Rango1.Cells(i).Value * Rango2.Cells(i).Value)
Suma = Suma + Rango1.Cells(i).Value
Next i
End If
Prorrateo = Resultado / Suma
Set Rango1 = Nothing
Set Rango2 = Nothing
End Function
Pégala en el editor de VBA en un módulo nuevo, eso te permitirá visualizar la función por insertar función y en categoría seleccionas Definidas por el Usuario, la función te va a pedir dos rangos de valores, el primero corresponde a las N y el segundo a las C.
Los rangos deben ser iguales en tamaño y los valores deben estar distribuidos en columnas... prúebalo y me cuentas.
Si no me explico bien o algo es muy complicado envíame tu dirección de mail y te envío un libro que tiene incorporada la función para que la veas funcionando.
De lo contrario te agradeceré finalizar la pregunta con una valoración acorde con la calidad y oportunidad de la respuesta.
Saludos,
Bien con dos rangos, (entiéndase conjunto de celdas continuas), trabaja sin problema... el único detalle es que en mi caso aunque el N y C están en la misma fila (obviamente en diferentes columnas!), no están continuas.
p.e:
Ventas % Ganancia
Frutas 100 25
Carnes 110 37
Pescados 150 55
Básicos 160 66
Y Pedro, lleva Frutas y Pescados, entonces quiero ponderar ambos para conocer el % Ganancia de Pedro.
Pienso que el punto esta en cambiar la definición de la variables del tipo de entrada (Range), pero no se por cual; Debería ser por algo que soporte, rangos y celdas independientes.
Gracias
XD
Bien con dos rangos, (entiéndase conjunto de celdas continuas), trabaja sin problema... el único detalle es que en mi caso aunque el N y C están en la misma fila (obviamente en diferentes columnas!), no están continuas.
p.e:
Ventas % Ganancia
Frutas 100 25
Carnes 110 37
Pescados 150 55
Básicos 160 66
Y Pedro, lleva Frutas y Pescados, entonces quiero ponderar ambos para conocer el % Ganancia de Pedro.
Pienso que el punto esta en cambiar la definición de la variables del tipo de entrada (Range), pero no se por cual; Debería ser por algo que soporte, rangos y celdas independientes.
Gracias
XD
Saludos,
Disculpa que te vuelva a pedir una aclaratoria, pero es que a veces por este medio resulta difícil explicarse.
Te detallo un poco más:
* Todas las filas tienen valores.
* Los valores Nn y Cn, estan en dos columnas (no necesariamente contiguas), pero si en la misma fila.
* Si Nn y Cn, tienen el mismo tamaño (fijate que siempre uso la n).
Pero el problema, es que los N1... Nn, no
Son continuos en celdas (y obviamente también pasa lo mismo con C1... Con).
Por ejemplo:
A B C
1 Frutas 100 25%
2 Verduras 150 30%
3 Carnes 190 45%
4 Pescado 60 30%
Supongamos que Pedro maneja, Frutas y Pescado, entonces la formula del prorateo seria = Prorateo(B1;B4;C1;C4)
Que deberia ser igual a:
Pedro = (B1 * C1 + B2 * C2)/(B1 + B2)
Pero la fórmula da un error!, por que no permite seleccionar celdas "independientes", para cada Rango.
Prove cambiando el tipo de dato a String, y tampoco lo lee... el tipo de dato debe permitir seleccionar celdas independientes y rangos...
(Aunque pienso que es imposible, por que confundiría los parámetros!)
Suerte!
XD
Disculpa que te vuelva a pedir una aclaratoria, pero es que a veces por este medio resulta difícil explicarse.
Te detallo un poco más:
* Todas las filas tienen valores.
* Los valores Nn y Cn, estan en dos columnas (no necesariamente contiguas), pero si en la misma fila.
* Si Nn y Cn, tienen el mismo tamaño (fijate que siempre uso la n).
Pero el problema, es que los N1... Nn, no
Son continuos en celdas (y obviamente también pasa lo mismo con C1... Con).
Por ejemplo:
A B C
1 Frutas 100 25%
2 Verduras 150 30%
3 Carnes 190 45%
4 Pescado 60 30%
Supongamos que Pedro maneja, Frutas y Pescado, entonces la formula del prorateo seria = Prorateo(B1;B4;C1;C4)
Que deberia ser igual a:
Pedro = (B1 * C1 + B2 * C2)/(B1 + B2)
Pero la fórmula da un error!, por que no permite seleccionar celdas "independientes", para cada Rango.
Prove cambiando el tipo de dato a String, y tampoco lo lee... el tipo de dato debe permitir seleccionar celdas independientes y rangos...
(Aunque pienso que es imposible, por que confundiría los parámetros!)
Suerte!
XD
Te cuento que ahí si no entendí, según veo el problema es que no siempre hay valores en todas las casillas... yo lo probé y funciona la condición es que las celdas que no contengan valores deben estar vacías o contener 0, esto no afectará el cálculo para nada pues la suma no se verá afectada y la multiplicación dará 0 para ese item.
A tener en cuenta, es que independientemente de que todas las celdas contengan o no valores el tamaño de los rangos debe ser igual, en cuanto al primer párrafo de la pregunta en donde me dices que los datos se encuentran en filas y no en columnas le hice una pequeña modificación para que funcione en filas y no en columnas, quedaría así:
Function Prorrateo(N As Range, C As Range) As Double
Dim i As Integer
Dim Suma As Double
Dim Resultado As Double
Dim Rango1 As Range
Dim Rango2 As Range
Set Rango1 = N
Set Rango2 = C
If Rango1.Cells.Count = Rango2.Cells.Count And _
Rango1.Rows.Count = 1 And _
Rango2.Rows.Count = 1 Then
For i = 1 To Rango1.Cells.Count
Resultado = Resultado + (Rango1.Cells(i).Value * Rango2.Cells(i).Value)
Suma = Suma + Rango1.Cells(i).Value
Next i
End If
Prorrateo = Resultado / Suma
Set Rango1 = Nothing
Set Rango2 = Nothing
End Function
Me cuentas como te fue con eso.
A tener en cuenta, es que independientemente de que todas las celdas contengan o no valores el tamaño de los rangos debe ser igual, en cuanto al primer párrafo de la pregunta en donde me dices que los datos se encuentran en filas y no en columnas le hice una pequeña modificación para que funcione en filas y no en columnas, quedaría así:
Function Prorrateo(N As Range, C As Range) As Double
Dim i As Integer
Dim Suma As Double
Dim Resultado As Double
Dim Rango1 As Range
Dim Rango2 As Range
Set Rango1 = N
Set Rango2 = C
If Rango1.Cells.Count = Rango2.Cells.Count And _
Rango1.Rows.Count = 1 And _
Rango2.Rows.Count = 1 Then
For i = 1 To Rango1.Cells.Count
Resultado = Resultado + (Rango1.Cells(i).Value * Rango2.Cells(i).Value)
Suma = Suma + Rango1.Cells(i).Value
Next i
End If
Prorrateo = Resultado / Suma
Set Rango1 = Nothing
Set Rango2 = Nothing
End Function
Me cuentas como te fue con eso.
Creo que eso que pides se puede hacer, pero la verdad me parece muy complicado y no se me ocurre como "decirle" a la función que de los (supongamos 10)parámetros corresponden a las N y cuales a las C... bastante complicado, además habría que agregarle bastantes validaciones adicionales para evaluar rangos independientes... hay otra función que se me ocurre pero te la dejo para que la investigues... si ves que es posible y progresas en lgo me escribes en otra pregunta y miramos a ver como lo arreglamos, la función es Multiarea... puedes consultar en la ayuda e línea de VBA.
