martes, 8 de febrero de 2011

Cómo obtener coincidencias de una tabla cruzada.

Una consulta que me llegó hace algunos días preguntaba sobre la forma en que se podría obtener listados condicionados o filtrados sobre una tabla cruzada:

...DISPONGO DE UNA TABLA CON VARIAS COLUMNAS (45) Y FILAS (266).
CADA FILA SE REFIERE A UNA CASA DIFERENTE Y CADA COLUMNA A UN DETERMINADO SERVICIO.
LO QUE QUIERO LOGRAR ES DANDO COMO REFERENCIA EL SERVICIO Y SU CONDICION OBTERNER UNA LISTA DE TODAS LAS CASAS QUE CUMPLEN CON ESTAS CONDICIONES. LOS VALORES DENTRO DE TABLA SON REPETIDOS. TE AGREGO UN EJEMPLO RESUMIDO DE LA TABLA .
la busqueda la hago en una hoja distinta a la que esta la table con los datos. Por lo que me es importante conseguirlo con formulas...


Se trata entonces, a partir de una tabla cruzada, obtener un listado de elementos que cumplen con dos condiciones. Veamos dicha tabla:


Nuestro objetivo es lograr un listado de los elementos del rango A3:A7, es decir, del campo 'CASAS' de la tabla, que cumplan unas condiciones definidas por nosotros, de acuerdo a las otras dos variables de la tabla; en este caso según el tipo de consumo (LUZ, GAS, AGUA) y si dicho consumo se encuentra CONECTADO/DESCONECTADO para cada CASA..
Para ello asignaremos nombre a los siguientes rangos(ver) dentro de nuestra hoja de trabajo 'datos:
AGUA =datos!$D$3:$D$7
CASAS =datos!$A$3:$A$7
consumos =datos!$B$2:$D$2
GAS =datos!$C$3:$C$7
LUZ =datos!$B$3:$B$7

En la siguiente etapa definiremos con la Validación de datos los criterios para las celdas G7 y G9; donde para la celda G7 se le asignará una validación tipo lista con los valores del rango consumos; y para la celda G9 una validación tipo lista con los valores CONECTADO / DESCONECTADO.


haz click en la imagen


Los valores que despleguemos en estas dos celdas (G7 y G9 servirán para configurar nuestra fórmula, y poder obtener así el listado de CASAS que cumplen ambos criterios.
Nuestra fórmula deberá ser matricial(Ctrl+Mayusc+Enter), y la desarrollaremos para el rango G12:G16
{=SI(INDIRECTO(G7)=$G$9;CASAS;"")}
con la que conseguimos que detecte para el rango definido en la celda G7 los valores iguales al valor determinado en la celda validada G9, devolviendo en caso de coincidencia el correspondiente del rango CASAS, y blanco en caso contrario.
Para que Excel reconozca el valor de la celda G7 como un rango evaluable se ha de emplear la función INDIRECTO.
El resultado será entonces:


haz click en la imagen


Es decir, sólo los DPTO1, DPTO3 y DPTO4 tienen la LUZ en modo CONECTADO.

18 comentarios:

  1. como se pueden ordenar las filas para eliminar las que quedan vacias

    ResponderEliminar
  2. Hola,
    lo más sencillo sería aplicar un filtro sobre el rango, quitando las vacías...
    Otra solución, habría que probar con algún ordenado por FRECUENCIA o COINCIDIR...
    Un saludo

    ResponderEliminar
  3. HE INTENTADO ESTAS ACCIONES EN 2010, PERO CREO QUE ALGO ANDA MAL, PUES ME DA EL ERROR DE REFERENCIA... QUE CREEN QUE SEA.

    ResponderEliminar
  4. Hola Luis Alonso,
    ¿te has asegurado de ejecutar la formulación de forma matricial???
    Slds

    ResponderEliminar
  5. Hola, estoy buscando como hacer algo que por lo que veo puede estar relacionado con este ejercicio.

    Tengo un negocio donde somos tres personas atendiendo, y cuando hacemos algún gasto lo registramos en una tabla de 3 columnas, con fecha, concepto e importe. Tenemos una tabla diferente por mes, y lo que quisiera hacer es sumar en otra tabla a la que tengo solamente acceso yo como administrador del negocio los valores repetitivos de la tabla a la que tenemos acceso los tres.

    Por ejemplo: todas las semanas pasa un comisionista por el negocio, que nos cobra por sus servicios, así que en la otra tabla donde registro ventas, impuestos, gastos, etc, quisiera que en el cierre del mes de enero, aparezca en una sola celda el gasto de comisionista que se ingresó cuatro veces en ese mes.

    Podrán ayudarme? Mil gracias!

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      podrías aplicar la herramienta Consolidar
      http://excelforo.blogspot.com.es/2009/10/consolidar-herramienta-avanzada-de.html

      De esa manera obtendrás en un solo lugar el acumulado por Concepto... aunque tendrías que modificar el orden de las tablas. las columnas deberán ser: Concepto, Fecha, Importe

      Espero te sirva. Creo es la solución más sencilla.
      Slds

      Eliminar
  6. Hola, Me puede ayudar de como poder pasar desde el OUTLOOK 2010 informacion al EXCEL. Necesito llevar un control de ciertos contactos con la recepcion de correos. Muchas Gracias. GERMAN FLORES.

    ResponderEliminar
    Respuestas
    1. Hola perrokalato,
      lo mejor que puedes hacer es exportar desde Outlook un archivo , podrás hacerlo en distintos formatos (uno de ellos como Excel)... aunque no soy especialista en Outlook, creo que te puede funcionar bien.
      La opción está en Archivo > Abrir > Importar > opción exportar a un archivo

      Espero te sirva.
      Slds

      Eliminar
  7. Tengo en Excel 4 secciones de 5año con sus promedios por alumnos y necesito encontrar el mayor promedio de cada sección y comparar cual de las 4 secciones es el mayor y colocar los 4tros mejores promedios de las secciones y colocarlos en otro cuadro. ejemplo.
    1) 19,55
    2) 19,41
    3) 18,15
    4) 17,94.
    gracias de ante mano por la ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      entiendo que hablamos de obtener los máximos para cada alumno, y después los cuatro mejores promedios.
      La primera parte creo sería aplicar la función MAX sobre esas secciones de cada alumno.
      Para obtener los 4 mejores puedes aplicar la función K.ESIMO.MAYOR sobre los máximos promedios de todos los alumnos, quedándonos con el 1,2,3 y 4
      =K.ESIMO.MAYOR(rango_máx_promedios;1)
      =K.ESIMO.MAYOR(rango_máx_promedios;2)
      =K.ESIMO.MAYOR(rango_máx_promedios;3)
      =K.ESIMO.MAYOR(rango_máx_promedios;4)

      Slds cordiales

      Eliminar
  8. tengo en Excel un cuadro de notas de tres lapsos y necesito darle color rojo cuando en la definitiva de cada notas tenga una aplazada, Como lo hago. Gracias por la ayuda

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      supondré que 'aplazada' es un concepto asociado a las tres notas de un alumno.
      En ese caso seleccionamos aquél rango al que deseemos aplciar ese formato condicional... con cuidado de tener como celda activa alguna de la primera fila de ese rango; por ejemplo, supongamos que el rango es A1:D10, siendo la columna D donde se encuentra la Nota definitiva (y en ella encontraremos alguna 'aplazada'), nuestra celda activa debe ser D1.
      A continaución aplicamos Formato condicional tipo fórmula con:
      =$D1="aplazada"
      y asignas el color/formato que quieras.
      Slds

      Eliminar
    2. muchas gracias por la por las formulas.

      Eliminar
  9. Gracias por la formula.

    ResponderEliminar
  10. Muchas gracias por todas las respuestas que estan en el BLOG las estoy practicando y me ayudan en mucho. Una consulta:
    " como puedo saber con un boton cuantos datos sume e ingrese una sola celda. Ejemplo. en la celda A1= 50+50+50+50 en B1= saldria 04 datos.

    Muchs gracias. !!!

    ResponderEliminar
    Respuestas
    1. Hola, siempre que lo tengas expresado como comentas, es decir, con el signo '+', puedes aplicar esta macro (asociándala a un botón si quieres):
      Sub ConteoSumandos()
      Dim cadena As String, n As Long
      Dim matriz() As String
      cadena = Range("A1").Formula
      matriz = Split(Trim(cadena), "+")
      n = UBound(matriz) + 1
      MsgBox "Número sumandos:= " & n
      End Sub

      Slds

      Eliminar
  11. GRACIAS, q pasa si por error le agrego ++, tambien lo contabiliza como 1 ?... Muchas gracias por su gran apoyo. Conteo de sumandos

    ResponderEliminar
  12. Hola Perrokalato,
    la macro realiza un conteo separando precisamente por el signo +.. asi que entiendo te contará como uno más.. al fin y al cabo es como si sumaras 0.
    De todas formas nunca ma ha pasado tal cosa realziando una suma.
    Para evitarlo, quizá habría que repasar los elementos de la matriz, y cuando estén vacíos, no contarlos como sumandos.
    Prueba con un FOR para recorrer los elementos de la matriz.
    Slds

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.