** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Otros de Microsoft: Windows y Office > Excel
  Mensajes nuevos Mensajes nuevos RSS - Sumar una columna
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoSumar una columna

 Responder Responder
Autor
Mensaje
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Tema: Sumar una columna
    Enviado: 01/Noviembre/2018 a las 23:18
El título parece sencillo pero ahora os cuento:

En un Rango llamado Clasificador que tiene dos columnas tengo lo siguiente

en la primera números naturales del 1 al 10 pero DESORDENADOS. En la segunda columna, texto, unas filas "Ing" (Ingresos) y otras "Gas" (Gastos) Es decir, a unos números les corresponden ingresos y a otros gastos. Es decir: números en la primera, texto "GAS" ó "ING" en la segunda.

Hasta aquí fácil, sigo:

En otro sitio tengo otro rango llamado Presupuesto con dos columnas

En la primera columna, ALGUNAS FILAS contienen números (Los números de la primera columna de Clasificador) las demás filas de esa columna están vacías

En la segunda columna tengo las importes correspondientes a esos números, unos corresponden a gastos y otros a ingresos. 

Ahora viene mi problema

En una celda, debajo de la columna de las importes del presupuesto, quiero sumar esa columna pero solamente los gastos, o los ingresos, dependiendo de una celda auxiliar donde pondré "GAS" ó "ING" 

Para ello, necesitaré una función que me diga si los números de la primera columna de Presupuestos se corresponden con GAS Ó ING, dependiendo de cual sea el valor de esos números en la segunda columna del Clasificador. Ahí interviene buscarV, ó desref y coincidir.. hay errores ya que la primera columna tiene celdas vacías.

Luego, comparar esos resultados con la casilla auxiliar, para que me devuelva, verdadero, falso etc y después, que los valores verdaderos me devuelvan los importes de la segunda columna de presupuestos y los sume. He editado totalmente esta entrada porque ni yo mismo la entendía, espero que ahora esté mejor explicado. Un saludo.









Editado por Antonalo - 02/Noviembre/2018 a las 12:34
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 12:52

No puedo decirte si está más claro que 'antes' (no veo como estaba), pero para que esté más claro , selecciona el rango de datos > pégalo aquí > indica que rango es (como mi ejemplo).


Entiendo un escenario como este (rango A1:E15):


Nums ing/gas Nums ing/gas
4 ing 4 467
10 ing 10 260
9 gas 9 734
2 ing 2 970
7 gas 7 547
7 gas 7 792
2 ing 2 845
   
   
   
   
ing 2542
gas 2073



Fórmulas:

[E14]   =SUMAR.SI($B$2:$B$12;D14;$E$2:$E$12)

[E15]   =SUMAR.SI($B$2:$B$12;D15;$E$2:$E$12)



Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 13:50
Veo que  has comprendido la cuestión, hago algunas observaciones:

En el primer rango (Clasificador) no hay números repetidos. Los conceptos representados por números, son únicos, cada uno con su número, eso si, unos son de gas y otros de ing. (por el 7 repetido)

En el segundo rango, hay 10 filas vacías entre cada número ( Para introducir subconceptos en columnas posteriores)

EL problema me viene porque en la fórmula

=SUMAR.SI($B$2:$B$12;D14;$E$2:$E$12), supone que el rango b2:b12 tiene las mismas filas que e2:e12 y que los valores están en el mismo orden. En Clasificador, los valores 1-10 están juntos pero desordenados . En presupuestos, los valores 1-10 sí  están ordenados pero separados por diez filas.  




Editado por Antonalo - 02/Noviembre/2018 a las 14:08
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 14:20
3 GAS
1 ING
4 GAS
2 ING
1 nomina 1000
 
2 varios 200
 
3 alimentos 500
 
4 alquiler 400
 
ING 1200
GAS 900

Este es un ejemplo muy simplificado donde se ve que los dos rangos tienen diferente altura. ING son el segundo y cuarto valor en Clasificador pero no quiero que sumen la segunda y cuarta fila porque en presupuestos, no están en esa posición.

Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 14:45
El problema es querer ingresar los datos como quieres verlos, los datos deben ingresarse como se generan (con fecha, con...) y luego en el informe los tendrás como quieres verlos, por ejemplo una Tabla dinámica.

Sigues sin indicar donde están tus datos.

Según veo en tu último mensaje, me parece entender que 1 (nómina) siempre será 'nómina' y por tanto un ingreso. ¿1 y 2 siempre serán ingreso, 3 y 4 siempre serán gasto (esto resolvería)?


Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 15:08
Ten en cuenta que las respuestas para los ejemplos muy simplificados, no suelen funcionar en el modelo real. si la lista de 'clasificador' ocupa 1,2,3,...10 muéstrala completa, y si la distribución de 'Presupuesto' no es como la indicas muestra un ejemplo inventado (pero fiel) donde estén presentes las columnas que intervienen en el cálculo (número e importe), e indica que rango real ocupa cada una de las dos (p.e. J20:J80 y N20:N80).


Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 16:55
El Rango Clasificador es muy fácil:
NTIPO
TIPOS
CLASES
3 ING DEUDAS
4 GAS GESTIONES
5 GAS IMPUESTOS
1 ING INMOBILIARIOS
6 GAS INMUEBLES
7 GAS LOCAL
8 GAS MATERIAL
13 GAS OTRO2
14 GAS OTRO3
9 GAS PERSONAL
2 ING PRESTACIONES
10 GAS REPRESENTACION
11 GAS SERVICIOS
12 GAS TRANSPORTE

Este Rango se llama BDCLASIFICADOR==DESREF(CLASIFICADOR!$A$1;1;0;CONTARA(CLASIFICADOR!$A:$A)-1;CONTARA(CLASIFICADOR!$1:$1)) Los concptos están en construccion, son irrelevantes pero es dinámico porque puede cambiar. El rango que podría servirnos es =CLASIFICADOR!$A$2:$B$15

El Rango de presupuestos, tiene en la primera columna, los números 1 al 14, separados por 10 filas luego cada concepto ocupa 11 filas, son 14 conceptos, no voy a pegaros 154 filas, digamos que el rango que nos ocupa es PRESUPUESTOS! a2:c155

(no se porqué no puedo pegar  una captura de pantalla)

la segunda columna de presupuestos, tiene los nombres asociados a los números de la columna A que los importo mediante la funcion:=BUSCARV(PRESUPUESTO!$A24;BDCLASIFICADOR;3;FALSO) que en este caso , me dice el concepto asociado al número de la fila 24 de la columna A que busca en la tercera columna del rango BDCLASIFICADOR

LA TERCERA COLUMNA (y siguientes 11 ) contiene los importes correspondientes a los subconceptos y una columna para los 12 meses. Es un presupuesto anual. LA FILA QUE CONTIENE EL NUMERO DE LA COLUMNA A contiene la suma de los importes de los subconceptos asociados al número y a ese correspondiente concepto. Por eso hay 10 filas entre los conceptos generales, para introducir 10 posibles subconceptos  En la columna A tiene por tanto un número cada 11 filas. 

PERO al Final, debajo de cada columna, quiero tener dos celdas, una para GASTOS totales y otra para Ingrsos totales en esa columna ( de ese mes) que mire en la columna A, que vea que numero es, que compruebe en BDCLASIFICADOR si es GAS o ING, y que sume los importes de las filas que llevan el numerito y que se correspondan con GAS ó ING.

En definitiva, es asignar a cada concepto un número y un tipo (gas o ing) y luego, desde otro sitio, apuntar solo los números y que la hoja de calculo resuelva el resto pidiendole que sume ingresos o gastos

El modelo simplificado que he puesto, si lo pudiera resolver, me lo resolvería todo. ya lo adaptaría al modelo real, de hecho, cuando quiero hacer algo difícil, siempre lo hago en una hoja aparte simplificando todo lo que puedo.


Editado por Antonalo - 02/Noviembre/2018 a las 17:01
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 02/Noviembre/2018 a las 17:31
Si te digo la verdad, Ansalval, esto es pura cabezonería, ganas de ver si se puede, porque no me costaría mucho, de la misma manera que en la columna B he importado el nombre del concepto, insertar otra columna e importar si es gasto o ingreso, con lo cual ya no tendría que mirarlo en clasificador y todo se resolvería con un simple sumar.si(RangoInsertado;"ING";RangoSuma)

 El tema es que eso me supondría revisar muchas cosas que podrían alterarse con la insercion de una nueva columna, pero no sería mucho problema, el problema es que me he empeñado en resolver el problema. Y eso me ha costado todo el día de ayer y un dolor de cabeza esta mañanaDead

También estoy diseñando por primera vez, una función definida por mi (VBA), donde tendría como argumento la celda auxiliar que le indica si es gasto o ingreso. En realidad ya está, solo que con el modelo simplificado. En cuanto lo haga real lo pego. Joer, solo por leer todo esto, ya mereces todo mi agradecimiento Ansalval.


Editado por Antonalo - 02/Noviembre/2018 a las 17:33
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 03/Noviembre/2018 a las 02:31

«... no me costaría mucho, ... insertar otra columna e importar si es gasto o ingreso, con lo cual ya no tendría que mirarlo en clasificador y ...»


Sin duda eso es lo ideal (te evitas fórmulas complejas y largas), también es buena idea una función de usuario (UDF), aunque yo prefiero la fórmula por rápida y efectiva, una macro que lea ciento y pico filas tampoco es demasiado .


De todos modos, conociendo ya los rangos reales, cuando tenga un ratito intentaré encontrar una solución a tu consulta inicial.


Yo te comprendo en lo de «cabezonería», pues lo poco que he podido aprender ha sido en gran medida porque me he asignado retos y me he puesto «cabezón»'  hasta resolverlos. Como digo abajo en la firma; se aprende mucho con las consultas de los usuarios (aunque a veces en algunas no me apetece participar Wacko).


«... solo por leer todo esto, ya mereces todo mi agradecimiento...»  LOL


Pues si te soy sincero, prefiero leer una consulta algo larga pero con datos suficientes que me permitan 'ver' como es el modelo de datos, que otra  cortita, pero que no aporta nada. A veces, no solo no sé como tienen los datos y donde, sino que no sé ni lo que pretenden conseguir/resolver. Es que mi «bola de cristal» últimamente está en época de «vacas flacas».


Lo dicho; si resuelvo tu consulta inicial te hago llegar la solución (y aprendemos los dos).  Wink



Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 03/Noviembre/2018 a las 10:07
Muchas Gracias AnSalVal, de mientras y sin renunciar a encontrar esa función, que haría en un paso lo que se puede hacer en dos perfectamente, es decir, creando otra columna donde diga si lo números de la primera columna y misma fila pertenecen a GAS o ING, y serían importados con un sencillo

=BUSCARV(A2;BDCLASIFICADOR;2) Puesto en la fila 2 y en todas las filas que contengan un número en la columna A de la Hoja PRESUPUESTOS. Esto me devolvería sin problemas "GAS" ó "ING" y ahora sí podría usar
=SUMAR.SI(RangoRecienInsertado;Celda_de_comparación;RangoSuma)

Y como digo, sin renunciar a que algo me haga todo en un paso, sin tener que insertar otra fila, he conseguido (ayer todo el día otra vez) una función definida en VBA que por fin funciona. La voy a pegar por si quieres echar un vistazo, espero que esté bien explicada.

Public Function SumaImportesMes(Tipo As String, Rimporte As Range)
Dim Nfp As Integer 'numero de fila del Rango BDPRES en la hoja PRESUPUESTOS (la de 15o y pico filas)
Dim Rnp As Range 'Rango de numeros de BDPRES. Es la primera columna del rango BDPRES
Dim Rclas As Range 'Rango Clasificador 2 columnas, en la hoja CLASIFICADOR, que define los números, si son gastos o ingresos y el nombre del concepto, lo que te he pegado antes.

Dim Np As Integer 'valores contenidos en la primera columna  de BDPRES, los números que definen los conceptos, o vacío
Dim Pc As Integer 'la posicion en el Clasificador del número Np
Dim temporal As Double
Set Rclas = Worksheets("CLASIFICADOR").Range("BDCLASIFICADOR").Resize(, 2) 'Define el Rango clasificador
Set Rnp = Worksheets("PRESUPUESTO").Range("PresColNConcepto") ' Define el rango de números del presupuesto (PresupuestosColumnaNumeroConcepto, abreviado PresColNConcepto), donde están los números

temporal = 0 ' inicia el valor

For Nfp = 1 To Rnp.Rows.Count 'va a recorrer la columna de numeros de BDPRES, PresColNConcepto

    Np = Rnp.Cells(Nfp, 1).Value 'toma el valor de la primera columna de BDPRES, en la fila Nfp

    If Np <> 0 Then 'comprueba que la celda de esa columna no esté vacia, para que no nos de error la busqueda posterior

        Pc = WorksheetFunction.Match(Np, Rclas.Resize(, 1), 0) ' busca la posicion de ese número en la primera columna del clasificador

        If Rclas.Cells(Pc, 2) = Tipo Then 'Comprueba si ese número tiene el tipo exigido en el parámetro Tipo exigido al definir la funcion y es la celda de comparación
            temporal = temporal + Rimporte.Cells(Nfp, 1) 'va sumando el número que encuentra en la columna importe de cada (rango Rimporte, exigido en la funcion como parámetro)
        End If

    End If

Next

SumaImportesMes = temporal

End Function

Me queda por decir que Rimporte es el Rango Columna de cada mes en el rango BDPRES, es un rango con referencia absoluta de fila y relativa de columna, para que la fórmula se pueda arrastrar.

Y creo, que de momento, puedo empezar a disfrutar de este puente, desayunar y tocar un rato la guitarra. Ya me dirás. Dicho sea de paso, el simple hecho de explicar a alguien el problema, exige ordenar las ideas y eso suele acercarnos a la solución.







Editado por Antonalo - 03/Noviembre/2018 a las 15:54
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 03/Noviembre/2018 a las 13:30

Si,  hay una solución sencilla (aunque tal vez no te guste2), tan fácil como:


1.- Fórmulas para C.

Gastos:    [C157]   =SUMAR.SI(A2:A155;">=4";C2:C155)

Ingresos: [C158]   =SUMAR.SI(A2:A155;"<=3";C2:C155)


2.- Aquí lo  que 'puede que no te guste' debido a...

«... Los conceptos están en construcción, son irrelevantes pero es dinámico porque puede cambiar.»


   Si te fijas en tu lista de «Tipos», puedes ver que los ingresos están juntos (1, 2 y 3) y los gastos también ( 4, 5,...14).  De ahí que en mis fórmulas  tenga  ">=4"  y  "<=3".


El tema es que la estructura de los tipos mantenga ese criterio, incluso si ordenas la lista resultará más intuitiva. Podrías hacer que  los ingresos sean 1 a 5 ( en previsión), y los gastos de 6 en adelante, y modificar las fórmulas de acuerdo a los nuevos parámetros (">=6"  y  "<=5").


---------------------------------------


Yo tenía (y tengo) en mente hacer una UDF para este caso, pero aún no lo he intentado. Ahora veré tu código.



Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
Antonalo Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 06/Noviembre/2009
Localización: España
Estado: Sin conexión
Puntos: 374
Enlace directo a este mensaje Enviado: 03/Noviembre/2018 a las 14:37
ya pensé en tu solución y la descarté demasiado pronto. Ahora, pensándolo mejor, no está nada mal, es sencilla y no me priva de ninguna ventaja. Como tú dices, puedo poner en el clasificador conceptos de ingresos, por ej. del 1 al 10, y en la hoja presupuestos usar solo aquellos que me interesen. la función contaría como ing solo los del 1 al 10 y listo. En el clasificador, los conceptos no van a estar ordenados por el numero sino por su nombre, o por su tipo y luego nombre, ya veré. Es para las listas deplegables en la validacion de datos de otra hoja MOVIMIENTOS. De todas formas, cualquier orden en el clasificador, no alteraría para nada los resultados en presupuestos.

Lo positivo es que después de todo esto, he aprendido a hacer mediante VBA una función que me va bastante bien. Tu idea queda, y es muy probable que la use cuando haga cosas similares porque es muy sencilla y, por eso, muy eficaz. 

Ya me dirás del código lo que te parece y cerrarás el tema cundo quieras. Muchas gracias AnSalval.




Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5381
Enlace directo a este mensaje Enviado: 03/Noviembre/2018 a las 21:27

Tu código recorre el rango fila a fila, yo prefiero recorrer importe a importe.


De todos modos como utilizas nombres definidos y yo no tengo definidos los rangos con nombre me da error, tendré que retomarlo cuando tenga un poco más de tiempo, para intentar reproducir tu modelo y sus datos.


Te paso mi UDF, la sintaxis en celda es  =SumaImp_2(3;"gas")  -> 3 es el número de columna de los importes (C), y representa C2:C155, aunque solo recorre las celdas con importe:


Public Function SumaImp_2(col&, Tipo$) As Double

  Dim fila&, t1&, t2&, dep&

  Application.Volatile

'Topes para los tipos "GAS" e "ING".

  t1 = 0 - 3 * (LCase(Tipo) = "gas")

  t2 = 4 - 11 * (LCase(Tipo) = "gas")

'Primera fila con importe.

  fila = Cells(1, col).End(xlDown).Row

'Si no hay importes: mensaje de error y finaliza.

  If fila > 155 Then

    MsgBox "No existen importes en la columna " & UCase(Chr(col + 64)) & ".", vbCritical, "  ASV - Error en datos."

    Exit Function

  End If

'Bucle sumatorio de importes.

  Do While fila < 155

    If Cells(fila, 1).Value > t1 And Cells(fila, 1).Value < t2 Then

'Si cumple el criterio suma el importe.

      dep = dep + Cells(fila, col).Value

    End If

'Fila del importe siguiente.

    fila = Cells(fila, col).End(xlDown).Row

  Loop

  SumaImp_2 = dep

End Function



Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable