Filtrar y sumar con condiciones en Excel
Tengo un problema con lo siguiente:
Estoy haciendo una tabla de facturación en la cual meto por columnas: números de clientes, importes y pagada o no pagada. De esa forma quiero controlar, según filtrado por número de clientes, el importe total de facturas pagadas y no pagadas. El problema es que tengo que utilizar la función subtotales para poder sumar solo lo filtrado, pero esta no me da la opción de ponerle condiciones (que me sume independientemente las pagadas o las no pagadas). No se si será mejor pasarlo a una hola e introduciéndole el código del cliente que me saque un listado del mismo, y allí efectuar el cálculo, sin utilizar la función subtotales.
Estoy haciendo una tabla de facturación en la cual meto por columnas: números de clientes, importes y pagada o no pagada. De esa forma quiero controlar, según filtrado por número de clientes, el importe total de facturas pagadas y no pagadas. El problema es que tengo que utilizar la función subtotales para poder sumar solo lo filtrado, pero esta no me da la opción de ponerle condiciones (que me sume independientemente las pagadas o las no pagadas). No se si será mejor pasarlo a una hola e introduciéndole el código del cliente que me saque un listado del mismo, y allí efectuar el cálculo, sin utilizar la función subtotales.
1 respuesta
Respuesta de javigo
1
Yo tampoco he encontrado la forma de hacer una suma condicional que no considere las celdas ocultas, habrá que buscar otro sistema.
Creo que no hace falta que lleves los datos a otra hoja, lo que puedes hacer es crear una fórmula que sume los importes en función del código de cliente y del estado de las facturas (pagadas/no pagadas).
He utilizado una fórmula matricial para esto. Te pongo un ejemplo simplón de cómo se hace para que luego lo puedas replicar en tu tabla.
Empezando por A1 ponemos los rótulos (nº cliente, importe, pagadas/no pagadas)
He puesto una tabla con 8 números de clientes, sus respectivos importes y en la columna Pagada/No pagada una "p" o una "n". El rango final de la tabla sería A1:C8
En B10 ponemos la fórmula del subtotal que funciona según los filtros:
=SUBTOTALES(109;B2:B8)
Ahora en A11 se pone el número de cliente que quiero sumar y en C11 el estado de las facturas (P o N)
En B11 escribes la siguiente fórmula:
=SUMA(SI(($C$2:$C$8=C11)*($A$2:$A$8=A11);$B$2:$B$8;0))
Pero para que funcione como una fórmula matricial, cuando la escribas en vez de ENTER, pulsa a la vez, SHITFT+CONTROL+ENTER
Si se hace bien, ahora la fórmula se muestra entre llaves {XXX}
Con esta fórmula puedes seleccionar el número de cliente y el estado de sus facturas y te genera la suma.
Bueno, espero que esto te ayude, si necesitas alguna aclaración no dudes en continuar la pregunta.
Creo que no hace falta que lleves los datos a otra hoja, lo que puedes hacer es crear una fórmula que sume los importes en función del código de cliente y del estado de las facturas (pagadas/no pagadas).
He utilizado una fórmula matricial para esto. Te pongo un ejemplo simplón de cómo se hace para que luego lo puedas replicar en tu tabla.
Empezando por A1 ponemos los rótulos (nº cliente, importe, pagadas/no pagadas)
He puesto una tabla con 8 números de clientes, sus respectivos importes y en la columna Pagada/No pagada una "p" o una "n". El rango final de la tabla sería A1:C8
En B10 ponemos la fórmula del subtotal que funciona según los filtros:
=SUBTOTALES(109;B2:B8)
Ahora en A11 se pone el número de cliente que quiero sumar y en C11 el estado de las facturas (P o N)
En B11 escribes la siguiente fórmula:
=SUMA(SI(($C$2:$C$8=C11)*($A$2:$A$8=A11);$B$2:$B$8;0))
Pero para que funcione como una fórmula matricial, cuando la escribas en vez de ENTER, pulsa a la vez, SHITFT+CONTROL+ENTER
Si se hace bien, ahora la fórmula se muestra entre llaves {XXX}
Con esta fórmula puedes seleccionar el número de cliente y el estado de sus facturas y te genera la suma.
Bueno, espero que esto te ayude, si necesitas alguna aclaración no dudes en continuar la pregunta.
Muchas gracias por todo, me ha servido de gran utilidad. Una pregunta más, se podría, ¿metiendo el número de cliente que me mostrase un listado con todas las facturas de dicho cliente? Muchas gracias por todo
Es posible simular una búsqueda para que te muestre un listado, voy a tratar de explicarlo paso a paso sobre el ejemplo anterior (la tabla que va desde A1 hasta C8).
Suponemos que pones el número de cliente en A14
En A15 pones la siguiente fórmula:
=COINCIDIR($A$14;$A$2:$A$8;0)
en B15:
=SI(ESERROR(A15);"";INDICE(B$2:B$8;$A15))
y en C15:
=SI(ESERROR(B15);"";INDICE(C$2:C$8;$A15))
La primera fórmula busca el nº de cliente deseado dentro del rango A2:A8 y devuelve la posición que ocupa el primer resultado.
Las otras dos fórmulas utilizan esa posición para ofrecer el importe y la situación del pago. Tienen un control para que en caso de que no se encuentre nada se muestren en blanco.
Ahora viene la parte más enrevesada. Supongamos que estamos buscando el cliente nº3 y la fórmula anterior indica que la primera vez que se encuentra en la lista está en la posición 2. ¿Cómo buscar las siguientes veces que aparezca?
Lo que hay que hacer es utilizar la función DESREF de forma que en vez de considerar el rango completo (A2:A8) tome el rango desde la posición 3 hasta el final. De este modo no se tiene en cuenta el primer resultado (posición 2).
La fórmula para eso se pondría en A16:
=COINCIDIR($A$14;DESREF($A$2:$A$8;$A15;;CONTAR($A$2:$A$8)-$A15);0)+A15
Lo de sumarle A15 al final es para que devuelva la posición que ocupa el segundo resultado "cliente nº3" en el rango completo.
Ahora viene la parte fácil. Sólo hay que copiar hacia abajo la fórmula de A15 y las de B14 y C14 hasta llenar la tabla que va a formar el listado. Yo la he creado de 7 elementos para probar el caso en que todos los clientes tienen el mismo número.
Resumiendo, la fórmula de A14 busca el primer resultado de la lista (el cliente nº3 que aparece por primera vez). La fórmula de A15 hace una nueva búsqueda justo después de ese primer resultado, la de A16 busca a partir del segundo resultado y así sucesivamente.
UUUUFFFFF! Esto es complicado de explicar, espero que haya quedado claro. Ya me dirás.
Suponemos que pones el número de cliente en A14
En A15 pones la siguiente fórmula:
=COINCIDIR($A$14;$A$2:$A$8;0)
en B15:
=SI(ESERROR(A15);"";INDICE(B$2:B$8;$A15))
y en C15:
=SI(ESERROR(B15);"";INDICE(C$2:C$8;$A15))
La primera fórmula busca el nº de cliente deseado dentro del rango A2:A8 y devuelve la posición que ocupa el primer resultado.
Las otras dos fórmulas utilizan esa posición para ofrecer el importe y la situación del pago. Tienen un control para que en caso de que no se encuentre nada se muestren en blanco.
Ahora viene la parte más enrevesada. Supongamos que estamos buscando el cliente nº3 y la fórmula anterior indica que la primera vez que se encuentra en la lista está en la posición 2. ¿Cómo buscar las siguientes veces que aparezca?
Lo que hay que hacer es utilizar la función DESREF de forma que en vez de considerar el rango completo (A2:A8) tome el rango desde la posición 3 hasta el final. De este modo no se tiene en cuenta el primer resultado (posición 2).
La fórmula para eso se pondría en A16:
=COINCIDIR($A$14;DESREF($A$2:$A$8;$A15;;CONTAR($A$2:$A$8)-$A15);0)+A15
Lo de sumarle A15 al final es para que devuelva la posición que ocupa el segundo resultado "cliente nº3" en el rango completo.
Ahora viene la parte fácil. Sólo hay que copiar hacia abajo la fórmula de A15 y las de B14 y C14 hasta llenar la tabla que va a formar el listado. Yo la he creado de 7 elementos para probar el caso en que todos los clientes tienen el mismo número.
Resumiendo, la fórmula de A14 busca el primer resultado de la lista (el cliente nº3 que aparece por primera vez). La fórmula de A15 hace una nueva búsqueda justo después de ese primer resultado, la de A16 busca a partir del segundo resultado y así sucesivamente.
UUUUFFFFF! Esto es complicado de explicar, espero que haya quedado claro. Ya me dirás.
