jueves, 8 de octubre de 2009

Análisis de datos en Excel.

Nos introduciremos hoy en el mundo del análisis de datos utilizando la Estadística descriptiva, es decir, empleando una parte de la estadística que se dedica a analizar y representar los datos. Es importante tener siempre presente que esta herramienta tiene poder inferencial es mínimo, ya que existe tendencia a generalizar a toda la población las primeras conclusiones obtenidas tras un análisis descriptivo, por ello deberíamos evitar esas generalizaciones.
Con esta herramienta, básicamente, realizaremos un estudio calculando una serie de medidas de tendencia central, para ver en qué medida los datos se agrupan o dispersan en torno a un valor central. Hablamos de conceptos tales como la Media, Moda, Mediana, Error típico, Desviación Estándar, etc.
Para comenzar a trabajar necesitamos en primer lugar una muestra de datos, por ejemplo, una muestra de 30 empleados de una empresa con un total de 200 trabajadores, de los cuales se disponen de datos de sexo y edad:


Una vez disponemos de los datos viene el estudio, si bien podríamos ir calculando con distintas funciones de Excel cada uno de lo estadísticos, optaremos por utilizar el Análisis de datos, y en concreto la opción de Estadística descriptiva; para la versión Excel 2003, desde el Menú Herramientas> Análisis de datos... para la versión Excel 2007, desde el menú Datos > Análisis (cuadro de opciones)>Análisis de datos:


Es necesaria la previa instalación de este complemento!!!.
Seleccionamos , de entre todas la herramientas, la Estadística descriptiva, con lo que se activa la ventana diálogo de la herramienta en concreto:


Los distintos parámetros de este cuadro diálogo son:
  • Rango de entrada: será la referencia de celda correspondiente al rango de datos que deseemos analizar. Al menos contendrá dos o más rangos adyacentes organizados en columnas o filas.

  • Agrupado por: Seleccionaremos 'Filas' o 'Columnas' para indicar si los datos del rango de entrada están organizados en filas o en columnas.

  • Rótulos en la primera fila y rótulos en la primera columna: Si la primera fila del rango de entrada contiene rótulos, activamos la casilla de verificación Rótulos en la primera fila. Si los rótulos están en la primera columna del rango de entrada, la casilla de verificación Rótulos en la primera columna.

  • Rango de salida: Seleccionamos la referencia correspondiente a la celda superior izquierda de la tabla de resultados. Esta herramienta genera dos columnas de información por cada conjunto de datos. La columna de la izquierda contiene los rótulos de estadística y la columna de la derecha contiene las estadísticas. Excel escribirá una tabla de estadísticas de dos columnas por cada columna o fila del rango de entrada, dependiendo de la opción que se haya seleccionado en el cuadro 'Agrupado por'.

  • En una hoja nueva: Si queremos insertar una Hoja nueva en el libro actual y pegar los resultados comenzando por la celda A1 de la nueva hoja de cálculo. tenemos la posibilidad de darle un nombre personalizado a esta nueva hoja de cálculo.

  • En un libro nuevo: Lo mismo que el punto anterior, pero para crear un nuevo libro y pegar los resultados en una hoja nueva del libro creado.

  • Resumen de estadísticas: Quizá el más interesante de todos los parámetros necesarios para configurar esta herramienta; cuando deseemos que Excel genere un campo en la tabla de resultados por cada una de las siguientes variables estadísticas: Media, Error típico (de la media), Mediana, Moda, Desviación estándar, Varianza, Curtosis, Coeficiente de asimetría, Rango, Mínimo, Máximo, Suma, Cuenta, Mayor (#), Menor (#) y Nivel de confianza.

  • Nivel de confianza para la media: Si queremos incluir una fila correspondiente al nivel de confianza de la media en la tabla de resultados. Por ejemplo, un valor de 95 % calculará el nivel de confianza de la media con un nivel de importancia del 5 %.

  • Késimo mayor: Cuando necesitemos incluir una fila correspondiente al valor k-ésimo mayor de cada rango de datos en la tabla de resultados. Si, por ejemplo, escribimos 1, esta fila contendrá el máximo del conjunto de datos.

  • Késimo menor: Cuando necesitemos incluir una fila correspondiente al valor k-ésimo menor de cada rango de datos en la tabla de resultados. Si escribimos 1, esta fila contendrá el mínimo del conjunto de datos.

Aplicamos entonces la herramienta a nuestra muestra de empleados:


Vemos que como rango de entrada hemos seleccionado sólo las columnas con los datos del estudio. Tras Aceptar Excel nos devuelve el siguiente cuadro:


Tan sólo nos queda entonces interpretar los datos extraidos, pero esa labor es más de estadísticos de profesión...
Como siempre Excel es sólo nuestra herramienta, no nos será útil si no sabemos interpretar los resultados.

61 comentarios:

  1. Como se pueden ordenar las columnas? en Excel 2007
    O dicho de otra forma, ordenar una base de datos por columnas y no por filas.

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Mediante la función transponer.

      Ojo, que es matricial.

      Eliminar
  2. Acabo de subir una explicación de cómo realizar ordenaciones de izquierda a derecha:
    http://excelforo.blogspot.com/2010/09/ordenacion-de-izquierda-derecha-en.html

    Saludos

    ResponderEliminar
  3. Vaya ... usto lo que necesitaba era la interpretación de algunos de los valores que lanza excel (ya que, por ejemplo, la fórmula que excel usa para el coeficiente de asimetría no es ni la de Pearson ni la de Fisher) .... :( ... Por lo demás, se ve muy bien la explicación, pero es la parte que ya sabía ..

    ResponderEliminar
  4. Si en lugar de calcular a desviación típica sobre la media de la muestra, quiero calcular la desviación típica sobre "cero", sabéis la fórmula y procedimiento a utilizar que no sea puramente manual?. Gracias por vuestra atención.
    Ángel

    ResponderEliminar
  5. Hola Ángel,
    pues la verdad es que lo poco que recuerdo de estadística y cálculo de probabilidades es que la desviación típica o estándar era la medida de la dispersión de los valores respecto a la media... y ahí me quedé.
    Al menos que yo sepa no existe ninguna función estándar de Excel que haga lo que quieres, pero si me dices cómo se calcula la desviación típica sobre cero, a lo mejor puedo dar con la solución a tu problema.
    Saludos

    ResponderEliminar
  6. POR FAVOR PODREIS AYUDARME, RESULTA QUE NO TENGO LA OPCION ANALISIS DE DATOS EN MI HOJA DE EXEL SABREIS QUE PUEDEO HACER COMO LA PUEDO CARGAR

    ResponderEliminar
  7. Hola,
    para activar este complemento, suponiendo trabajas con Excel 2007, deberás acceder al botón de Office > Opciones de Excel > Complementos
    y en la ventana activa buscas (abajo) Administrar Complementos de Excel > Ir.
    Se abrirá una ventanita con todos los complementos estándar de Excel, sólo marca Herramientas para análisis.
    Slds

    ResponderEliminar
  8. Viendo estos ejemplos me podrían aconsejar como poder hacer para registrar el consumo semanal de un producto. Tengo tres columnas fecha, nombre del producto y cantidad de consumo. Necesito que en otra columna me ponga el total del consumo semanal de un producto me lo podrían explicar ? Gracias

    ResponderEliminar
  9. Muy buenas,
    la verdad es que podrías optar por usar una función SUMAR.SI.CONJUNTO sobre los rangos de fecha y producto para criterios y sumando la cantidad de consumo; pero creo que lo mejor sería aplicar una Tabla dinámica, y aprovechar la herramienta de 'Agrupar' el campo Fecha, por días, meses, etc. O también generando previamente un campo en tu tabla de datos de origen, donde especifiques a qué semana corresponde cada fecha.
    Espero te haya orientado...
    Slds

    ResponderEliminar
  10. Holaaa!!! Explicaste como agregar el analisis de datos en el excel 2007 pero yo tengo 2003 yla verdad que nose cual seria el boton office.. Podrias ayudarmme? lo necesito para un trabajo!! Graiass

    ResponderEliminar
  11. Hola,
    para agregar este complemento desde Excel 2003, lo primero es ir al menú Herramientas > complementos > herramientas para análisis
    esto instalará dicho complemento, con lo que te aparecerá al final del menú Herramientas > Análisis de datos con las mismas herramientas que en Excel 2007.
    Un saludo

    ResponderEliminar
  12. BUENAS TARDES ESTOY HACIENDO UNA PLANTILLA PARA CONTROL DE OBRAS Y QUIERO SABER COMO PUEDO SUMAR EN UNA HOJA SOLO LAS FILAS REFERENTES A UNA OBRA EN CONCRETO YA QUE EN UNA MISMA HOJA INTRODUZCO VARIAS OBRAS A LA VEZ Y QUE ME LO SUME EN OTRA HOJA DONDE TENGO EL RESUMEN DE CADA OBRA

    ResponderEliminar
  13. Hola,
    para poder sumar de manera acumulada por un mismo criterio podrías emplear la función SUMAR.SI o SUMAR.SI.CONJUNTO o incluso una SUMA condicianal matricial.
    Echa un vistazo a
    http://excelforo.blogspot.com/2010/02/sumas-condicionadas-sumarsiconjunto.html
    Espero te sirva.
    Un saludo

    ResponderEliminar
  14. Hola!!!! oye me podrías resolver una duda es que ahorita tengo un trabajo en Excel de estadística pero con datos agrupados que función aplico para poder hacerlo......... espero puedas ayudarme

    ResponderEliminar
  15. Hola,
    habría que ver cómo están agrupados esos datos, y sobre todo cuáles son las necesidades que tienes; ya que depende de que necesites podrás aplicar unas funciones o no.
    Poco más puedo decirte con lo que has comentado.
    Slds

    ResponderEliminar
  16. Hola, cuál sería la interpretación de los datos arrojados por excel aplicando la herramienta estadística?

    ResponderEliminar
  17. Hola Mario,
    supongo que te refieres a la herramienta que encuentras en Análisis de datos > Estadística descriptiva
    Puedes ver un ejemplo sencillo en
    ejemplo
    Como ves no hay mucho que leer, sólo expresa sus significados estadísticos para cada parámetro.
    Slds

    ResponderEliminar
  18. me gustaria tener mas ejemplos
    seria bueno

    ResponderEliminar
    Respuestas
    1. Hola,
      por supuesto puedes proponer un ejemplo de Análisis de datos a solucionar...
      Un cordial saludo

      Eliminar
  19. Hola, tengo una Macbook pro con office 2011, pero cuando quiero buscar los complementos de excel solo aparece el solver, quisiera saber de donde puedo descargar el "análisis de datos".
    Gracias!

    ResponderEliminar
    Respuestas
    1. Lamento no poderte ser de mucha ayuda, ya que nunca he trabajado con Mac...
      pero no creo sea muy diferente del entorno Windows, donde al abrir Excel, buscas las Opciones de Excel > Complementos, desde podrías descargarte cualquiera de los complementos, incluido el de Análisis de datos
      Echa un vistazo a la ayuda de Microsoft
      http://support.microsoft.com/kb/2431349/es
      Poco más puedo decirte.
      Un saludo y suerte

      Eliminar
  20. sabes como hacer un histograma de probabilidad en excel???

    ResponderEliminar
    Respuestas
    1. Hola,
      en esencia no hay ninguna diferencia a la hora de generar un histograma, ya que se trata de representar en columnas una medida por grupos.
      Revisa esta entrada
      http://excelforo.blogspot.com.es/2009/11/histograma-en-excel.html
      Slds

      Eliminar
  21. HOLA: Tengo en la primera columna un listado de trabajadores y en la primera fila 5 tipos de pieza, los datos de interseccion son el tiempo que demora cada obrero por cada tipo de pieza.
    Quiero en un celda aparte yo introdusco un nombre y un tipo de pieza y en una tercera celda me reporte el tiempo donde conciden ambos datos que le di como referencia...como se formula esto...

    ResponderEliminar
    Respuestas
    1. Hola,
      revisa estas entradas del blog, donde se explica el uso de las funciones INDICE y COINCIDIR, que son las que necesitas para llegar a lo que quieres.
      http://excelforo.blogspot.com.es/2009/12/busqueda-de-referencias-cruzadas-en.html
      y
      http://excelforo.blogspot.com.es/2010/03/buscar-en-matrices-la-funcion-indice.html
      Slds

      Eliminar
    2. aplique este vinculo http://excelforo.blogspot.com.es/2009/12/busqueda-de-referencias-cruzadas-en.html y me dio resultado cuando evaluo la formula, pero en la celda no me muestra el resultado de la formula en su lugar me pone 0,00

      Eliminar
    3. Hola,
      asegúrate que la has ejecutado matricialmente, es decir validando la fórmula no con Enter, si no presionando Ctr+Mayusc+Enter.
      Slds

      Eliminar
    4. Un millon 500 mil gracias, me han ayudado muchisimo con la solucion de esta formula, me gustaria registrarme, pero no he encontrado el link,exactamente cuando valide la formula tomo el valor que era, No conocia que se validaban las formulas de esa forma.
      Gracias

      Eliminar
    5. Gracias a ti...
      para registrarte y seguir las entradas del blog a través de RSS, en la barra lateral derecha del blog, en la parte de arriba, puedes ver los link de subscripción (vía Rss y vía mail).
      Un saludo

      Eliminar
  22. Hola:
    quiero hacer una Analisis de varianza de dos factores con varias muestras por grupo y no puedo hacerlo porque no me doy cuenta que debo poner en "fila por muestra".
    La tabla de resultados a analizar esta dividida en tres columnas y dos filas, cada una con 10 datos.

    ResponderEliminar
    Respuestas
    1. Hola...
      ¿puedes enviarme el fichero a excelforo@gmail.com?... no visualizo la tabla de 3 columnas y 2 filas y 10 datos cada columna???
      En cuanto lo reciba le echaré un vistazo...
      Un cordial saludo

      Eliminar
  23. Hola,necesito ayuda:
    ¿Cómo debo ingresar a la hoja de excel los datos de un estudio en el se obtuvieron resultados como negativo o positivo a diferentes pruebas? Ejemplo: son 100 personas a quienes se les hicieron 15 pruebas cutaneas para diferentes alergenos. ¿Como puedo analizar la frecuencia de positividad para tal o cual alergeno?. GRACIAS DE ANTEMANO POR LA AYUDA

    ResponderEliminar
    Respuestas
    1. Hola Angel,
      si necesitas realizar un conteo de esos 1500 datos, y determinar cuántos son positivos o negativos, podrías aplicar la siguiente fúnción al rango de 1500 celdas:
      =CONTAR.SI(rango_datos;">"&0)
      o para los negativos:
      =CONTAR.SI(rango_datos;"<"&0)

      Espero sea lo que necesitabas.
      Un saludo

      Eliminar
    2. OK MUCHAS GRACIAS. Lo intentaré

      Eliminar
  24. buenos dias mi nombre es marcos chan, me gustaria saber como hacer preguntas, y donde puedo subir un archivo la pregunta es sobre macros, muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Marcos,
      las preguntas las puedes realizar bien a través de estos comentarios, o bien, como es tu caso, si necesitas adjuntar un fichero con un ejemplo, enviándomelo al correo:
      excelforo@gmail.com

      Slds

      Eliminar
  25. kika

    Necesito hacer una regresión, pero no cuento con el complemento de Analisis de Datos, ya realice la ejecución pero no me sale ningún dialogo y no aparece en menu la Analisis de Datos lo que si pude hacer fue instalar el SOLVER nada mas.... que podría hacer ? donde encuentro el archivo que me falta?

    ResponderEliminar
    Respuestas
    1. Hola Kika
      Entiendo por lo que dices que ya has ejecutado desde las Opciones de Excel > Complementos > Administrar complementos de Excel > Ir > Herramientas para Análisis.
      Si ha sido así, y tenías en el listado de complementos esta Herramienta par análisis, deberá aparecerte en la ficha Datos > grupo Análisis, junto al complemento Solver.

      Los complementos se preinstalan en el momento de la instalación personalizada del paquete Office; si a tí, no te aparece esa opción de Herramientas para análisis entre los Complementos disponibles, posiblemente sea un problema de la instalación, que seleccionaste NO instalarlo...

      En definitiva, vuelve a instalar Office e indica que quieres instalar estas dos herramientas (para análisis y solver) desde el principio...

      Slds

      Eliminar
  26. Hola!!
    Tengo una duda ante el uso de esta herramienta:

    Cuando uso analítica descriptiva, los resultados son muy parecidos pero no iguales a cuando realizo las fórmulas "manualmente" en excel. Creo que es evidente; pero diré que por supuesto introduzco los datos exactos hasta el ultimo decimal, de hecho seleccionando cada uno de las celdas necesarias al introducir los datos en las fórmulas.

    Saludos y gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      bueno, realmente sólo deberías tener diferencia en el último de los parámetros (Nivel de confianza), ya que todos los demás sí deberían coincidir.
      Hace tiempo escribí al respecto:
      http://excelforo.blogspot.com.es/2009/11/analisis-estadistico-con-funciones-moda.html

      ¿De que diferencias estamos hablando???
      Slds

      Eliminar
  27. Y la interpretación como la hago???? eso es lo que necesito

    ResponderEliminar
    Respuestas
    1. Hola, que tal?!!
      Espero tu también te encuentre bien.

      Excel es sólo la herramienta, eres tu , quien realiza los cálculos, quien debe saber leer o interpretar los resultados.

      Slds cordiales

      Eliminar
  28. Hola, tengo una surface, y en mi hoja de Excel no me aparece análisis de datos... como puedo habilitarlo? Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      debes ir a Complementos, dentro de las Opciones de Excel...
      al final de la ventana hay un botón Complemento de Excel [ir]
      presiona el botón de Ir y en la nueva ventana te listará los complementos de Excel disponible.. elige el de herramientas de análisis

      un cordial saludo

      Eliminar
  29. que es análisis de datos

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      Un placer saludarte igualmente,
      El análisis de datos es el conjunto de herramientas dispuestas en Excel para poder analizar conjuntos de información.
      Tal y como se explica en este post...
      UN cordial saludo

      Eliminar
  30. Hola también tengo una Surface y no me parece en la ventana de opciones de Excel elementos de Excel solo me parece elementos activos, elementos inactivos, elementos relacionados con documentos y complementos de aplicaciones deshabilitadas y ni siquiera se pueden seleccionar por favor ayuda

    ResponderEliminar
    Respuestas
    1. Hola Alan,
      habría que mirar in situ tu aplicación... podría ser que al ejecutar alguna macro se te hayan deshabilitado ciertos botones...

      Yo probaría reinstalando Office, es la solución más directa y segura, cualquier intento de solucionar el problema 'parcheando' seguro que explotaría por algún lado.

      Saludos

      Eliminar
    2. Gracias y como lo reinstalo?

      Eliminar
    3. Alan, No entiendo la pregunta??
      puedes instalarlo como lo hiciste la primera vez; también desde el Administrador de programas de Windows, en la opción de Reparar.
      Obviamente debes tener el paquete de instalación y el Key Product.
      Un saludo

      Eliminar
  31. Hola una pregunta y como aplico eso para datos agrupados en intervalos?

    ResponderEliminar
    Respuestas
    1. Hola Pamela,
      si los datos están agrupados, la herramienta funcionaría igual.. pero obviamente el resultado obtenido correspondería a esos rangos/intervalos de información.
      También deberías partir el intervalo en dos celdas para mejor funcionamiento, por ejemplo en A1: 1-10, entonces dividir el intervalo en A1: 1 y en B1: 10 (opcional)....
      Saludos

      Eliminar
  32. Tengo unos valores numéricos que traslado a otra columna en otra hoja, utilizando la función REPETIR. Se copian correctamente, pero si intento hacer una SUMA de estos, siempre me da 0 (cero). Si quiero obtener el PROMEDIO, sale DIV/0!.
    Me interesa sobre todo obtener el PROMEDIO. ¿Como debería hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola José,que tal estás?
      un placer saludarte igualmente.
      REPETIR devuelve el valor de una celda o una cadena como tipo texto, repetido tantas veces como se indique.
      Es decir, las celdas con un resultado de la función REPETIR son texto, y por tanto no se puede operar sobre ello.

      Lo más simple sería realizar un vínculo entre celdas, en tu segunda hoja marcas:
      =Hoja1!A1
      o sencillamente seleccionas con el ratón la celda origen.
      Un cordial saludo

      Eliminar
    2. Ismael: Genial !

      Lo que me indicas es mucho más sencillo que lo que yo pensaba hacer y funciona de acuerdo con lo que buscaba.

      Muchas gracias por la respuesta y la rapidez de la misma.

      Eliminar
    3. Hola Ismael. Hice todos los pasos para cargar herramientas para analisis, cargo analisis de datos, reviso por todo el excel y no me aparece para utilizarla, busco por todos lados y nadie explica si pasa eso. Si sabes que puede ser te lo agradezco. Saludos y muchas gracias

      Eliminar
    4. Hola,
      debes buscar en la ficha de Datos, en el grupo de la derecha ('Análisis')

      Si no te aparece vuelve a mostrarlo desde los Complementos

      No puedo decirte mucho más...
      Saludos

      Eliminar
  33. Hola, disculpa, tengo incovenientes al ingresar el rango para generar el histograma, estoy trabajando con una base de datos y cundo selecciono una columna me arroja que "El rango de entrada contiene datos no numericos" a pesar que todos son números para mi, lo puedo solucionar de alguna forma? De verdad necesito que funcione.

    ResponderEliminar
    Respuestas
    1. Hola,
      emplea la función VALOR en una columna anexa para convertir a número esos valores almacenados como texto, y luego aplica la herramienta sobre esta última columna
      Espero te funcione
      Saludos

      Eliminar

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