viernes, 2 de octubre de 2009

La función JERARQUIA: la ordenación de valores.

Un usuario escribió:

La cuestión que deseo plantear es la siguiente: necesito construir una función o algo así que me devuelva de un rango de celdas la posición (posición y contenido de la celda sería lo ideal) de la celda/s que cumplan una determinada condición (o condiciones, si tal cosa es posible). En la lengua de andar por casa que por ejemplo, a partir de un rango de celdas pueda sacar una lista con las posiciones de las celdas donde haya datos que sean menores que uno dado (o iguales o diferentes o otra condición que se desee imponer) y cuales son los valores que corresponden a cada celda de la lista obtenida.

.
Partiremos, para intentar solucionar esta cuestión, del siguiente listado de valores:


Nuestra condición a aplicar sobre los valores será escoger los que estén entre 100 y 350:
=SI(Y(valor>=100;valor<=350);valor;"")
con lo que obtenemos una nueva lista sólo con los valores que cumplen nuestra restricción.


Ahora los ordenaremos, para lo cual utilizaremos la función de Excel JERARQUIA.
=JERARQUIA(número; referencia; [orden])
Debemos tener cuidado con esta función, ya que si tuvieramos valores repetidos dentro de nuestro rango de estudio la función JERARQUIA asigna la misma jerarquía a los números duplicados, por lo que para poder resolver este inconveniente, incluiremos una nueva función
=CONTAR.SI(rango; criterio)
con esta función conseguiremos determinar qué valores están repetidos en nuestro rango de datos, y cuantas veces.
Tenemos, por tanto, en este segundo paso, las siguientes funciones:
=JERARQUIA(valor;rango_valores_restringidos;1)
de donde obtenemos en qué posición se encuentra cada valor respecto a los valores que cumplen la condición inicial como si nuestro listado se ordenara en forma ascendente;
y la función
=CONTAR.SI($C$2:$C2;valor)
nos fijamos que el rango determinado en el primer argumento de la función es móvil en cuanto a las filas, es decir, según copiamos la función el rango va creciendo. hemos conseguido con esto saber fila a fila cuantas veces se ha repetido un valor.


Por último, uniendo en una sola función lo anteriormente expuesto, tenemos el siguiente orden:


la función final que hemos logrado es:
=SI($C2="";CONTAR($C$2:$C$11)+1;JERARQUIA($C2;$C$2:$C$11;1)+CONTAR.SI($C$2:$C2;C2)-1)
hemos incluido nuestra función dentro de un condicional SI para eliminar los errores de aquellos valores que no cumplían la condición inicial (valores entre 100 y 350).
Sólo nos queda obtener el resultado final:


Adjunto fichero para su análisis.

Jerarquia
Jerarquia.xls
Hosted by eSnips

18 comentarios:

  1. Gracias por la förmula concatenar, ustedes son de gran ayuda.

    ResponderEliminar
  2. como hago para pasar los dato que estan en una hoja de excel a word?.tengo en la hoja de word un formato de titulo y necesito pasar los datos que estan en excel,ejemplo.
    ( word ) :(dato en excel)
    nombre del plan: dato en excel
    codigo de la mencion: dato en excel
    nombre y apellido: dato en excel
    cédula identidad: dato excel
    día: dato excel
    mes: dato excel
    año: dato excel

    ResponderEliminar
  3. Hola,
    tal cual lo planteas es más un tema de Word...
    aunque hace mucho que no lo uso, supongo seguirá existiendo la opción de 'Combinar correspondencia' en Word, lo que te permite enlazar con bases de datos (también con hojas de cálculo).
    En Word 2007 existe unmenú exclusivo con esta herramienta...
    Espero haber comprendido correctamente tu cuestión.
    Slds

    ResponderEliminar
  4. Necesito la ayuda de los expertos.tengo una hoja de excel que tiene los datos
    CI ;Aellido;Nombre;Lug Nac,Ent.Fed,Sexo,D,M,A
    V19999659,CARRILLO,WENDY,CARACAS,DF,F,27,6,1992 E89675464,SALAZAR,JOSE,COLOMBIA,EX,M,12,10,1994
    V20768804,MORENO,MARIA,PETARE,MI,M,15,12,1992
    V21283081,HIDALGO,MARIA,CARACAS,DF,F,24,11,1993

    y necesito una formula que cada vez que encuentre un EXTRANJERO(EX) lo coloque de ultimo.ejemp.
    V19999659,CARRILLO,WENDY,CARACAS,DF,F,27,6,1992
    V20768804,MORENO,MARIA,PETARE,MI,M,15,12,1992
    V21283081,HIDALGO,MARIA,CARACAS,DF,F,24,11,1993
    E89675464,SALAZAR,JOSE,COLOMBIA,EX,M,12,10,1994

    ResponderEliminar
  5. Hola,
    se me ocurre ordenar la base de datos por el campo CI en orden inverso, esto es de Z a A (suponiendo que todos los CI empiecen por V...).
    Otra forma es añadir a la base de datos una columna auxiliar con la fórmula:
    =SI('Ent.Fed'="EX";100;0)
    lo que devolverá un valor de 100 a todos los extranjeros y 0 a los nacionales; sobre ese campo ajecutar una ordenación de menor a mayor...
    Otras formas serían más complejas, o requerirían de una macro.
    Slds

    ResponderEliminar
  6. Si me pueden ayudar.Tengo en una Hoja de excel los datos de sexo,dia,mes,año de alumnos y necesito una formula que me calcule la edad y el sexo de cada estudiante de manera que cuando se agrego una lista esta lo calcule. ejep.
    F,12,8,1998
    F,28,3,1998
    M,18,9,1997
    M,5,11,1998
    .
    .
    .
    Sexo,Año
    F ,12
    F ,12
    M ,14

    ResponderEliminar
  7. Hola!!,
    bueno, suponiendo que columna A:'Sexo', B: "día", C: "mes" y D: "año",
    podríamos obtener la edad (sólo en años) con la fórmula:
    =ENTERO(CONVERTIR(HOY()-FECHA(B1;C1;D1);"day";"yr"))
    para cada registro...
    espero te sirva
    Un saludo

    ResponderEliminar
  8. gracias por la formula pero no meda el resultado esperado.

    ResponderEliminar
  9. Puedes enviarme un ejemplo, detallando exactament lo que quieres, a:
    excelforo@gmail.com
    y le echaré un vistazo.
    Un saludo

    ResponderEliminar
  10. la formula esta excelente el problema era que se coloco dia, mes y año ;26,09,1994 y daba el resultado errado pero era.año,mes y dia
    1994,09,26 y me da el resultado correcto.
    muchas gracias.

    ResponderEliminar
  11. Mil disculpas!!
    a veces construyo las fórmulas de cabeza, y olvido el orden de los argumentos de las funciones.
    me alegro que te funcionara finalmente.
    Un saludo

    ResponderEliminar
  12. Muy interesante el blog...yo quisiera saber la formula aplicar en generar todas las combinaciones posibles desde el numero 1 al 49 sin repeticion(loteria)., entre 6,7,8...hasta un maximo de 18,20 numeros.Gracias de antemano y disculparme el no saber mucho aun de excel..Gracias

    ResponderEliminar
    Respuestas
    1. Hola dagusa55, he visto tu pregunta y aunque hace casi un año de la misma, si quieres escribe tu correo y te enviare una Macro que fue un Aporte de un Foro, la verdad que alucinaras de las cosas que puede hacer y lo rápido que se ejecuta, incluso se pudiera emplear en las distintas Loterías, ya que te puede sacar combinaciones de 2, 3, 4, 5, 6 y mas números.

      Saludos
      Lázaro.

      Eliminar
  13. Hola Dagusa55,
    la verdad es que tu pregunta no es fácil de contestar, de hecho, no existe ninguna fórmula estándar en Excel que sea capaz de generar lo que quieres... al menos, hasta donde yo sé, lo que quieres necesitaría de una programación avanzada en VBA (que yo no alcanzo).
    Mucha suerte con tu búsqueda y lamento no poder ayudarte
    :-(

    ResponderEliminar
  14. Hola,

    Tengo una base de datos que contiene contratos por determinado valor. Mi complicación es que debo jerarquizar estos valores tomando en cuenta el número de proveedor y el año del mismo por lo tanto me gustaria obtener una tabla similar a la siguiente:

    Proveedor Año Valor de Contrato Jerarquia
    100 2013 789 3
    200 2013 153 1
    100 2013 654 2
    200 2013 876 4
    200 2013 487 3
    100 2013 987 4
    100 2013 321 1
    200 2013 212 2

    Espero me puedas asesorar, mil gracias!

    Edgar Ureña
    edgar.urena@sas-automotive.com

    ResponderEliminar
    Respuestas
    1. Hola Edgar,
      antes de pensar una fórmula que devuelva lo que quieres...
      has pensado sencillamente en aplicar una Ordenación personalizada sobre los tres campos:
      Primer nivel 'Proveedor' de menor a mayor
      Segundo nivel 'Año'
      Tercer nivel 'Valor contrato'
      y una vez en el orden correcto asignarle la correlación de Jerarquía??

      El asunto mediante fórmulas puedes ser bastante complicado, ya que entiendo que el número de proveedores y años puede ser alto, lo que implicaría muchas condiciones, ejecutadas en forma matricial.

      Espero te sirva.
      Slds

      Eliminar
  15. hola una consulta...

    tengo una lista de alumnos que debo ordenarlas por calificacion... ejemplo:

    Carlos 20
    Maria 20
    Luis 18
    Manuel 19

    esto deberia salir...

    Carlos 1
    Maria 1
    Luis 2
    Manuel 3


    pero me sale



    Carlos 1
    Maria 1
    Luis 3
    Manuel 4


    donde esta el 2do puesto... como lo podria corregir?

    la formula que empleo es:

    =JERARQUIA.EQV(b2,$b$2:$b$5,0)

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      el truco es añadir a la fórmula un CONTAR.SI:
      =JERARQUIA.EQV(B1;$B$1:$B$4;0)+CONTAR.SI($B$1:B1;B1)-1

      Echa un vistazo a
      http://excelforo.blogspot.com.es/2012/09/aleatorios-sin-repeticion-en-excel-sin.html
      hay una explicación que te puede servir.
      Slds

      Eliminar