Tabla de frecuencias en Calc o Excel utilizando “CONTAR.SI”

Aunque la mejor forma de trabajo es utilizar calc o excel sólo para guardar los datos y utilizar programas externos para analizarlos de forma reproducible, en ocasiones es más fácil y rápido (aunque menos seguro) realizar algunos cálculos directamente en la propia hoja de cálculo.

Si se realizan cálculos en la propia hoja de cálculo, lo ideal es hacerlo en una hoja diferente a la que contiene los datos y los datos originales no tocarlos nunca (o lo menos posible, en cuyo caso es mejor copiar la hoja, para no  tocar el original).

En este post yo voy a utilizar LibreOffice Calc, pero el mismo procedimiento debería funcionar en Microsoft Excel.

Dicho esto, el objetivo de este post es crear una tabla de contingencia a partir de una tabla de datos con observaciones (filas) x variables (columnas). Para el ejemplo utilizaré una tabla de datos de aves recopilada por mis alumnos.

Primeras lineas de la hoja “Aves” de los datos recogidos por mis alumnos. Observar que la notación de cada celda es una letra y un número. Por ejemplo la zona del Ave_001 está en la celda E2.

Tabla de contingencia simple

El primer ejemplo será una tabla de contingencia simple en la que vamos a contar el número de aves en “Campus de la UPV”, “Viveros”, “Blasco Ibañez”, “El río” y “Otros”. Para ello lo primero que hacemos es crear una hoja nueva (Hoja>Insertar hoja>poner nombre>aceptar) y en ella poner los nombres de las zonas que queremos utilizar.

Aunque podríamos utilizar la función “FRECUENCIA” como proponen aquí y aquí, vamos a utilizar mejor la función CONTAR.SI, que es mucho más versátil y nos servirá para luego complicar la selección: Insertar>Función>CONTAR.SI.

Asistente de funciones. Seleccionar la función CONTAR.SI

En esta función nos pide dos cosas, el intervalo y los criterios.

El Intervalo va a ser la columna donde están los datos de “zona”:  Aves.E$1:Aves.E$1000    Importante que el último número sea mayor que el número de observaciones (líneas de la tabla de datos).

El Criterio va a ser: A2, Es decir, que coincida con el valor de A2 en la tabla de contingencia, que es “Campus de la UPV”. También se podría escribir el texto directamente aquí.

por lo que la celda B2 quedará así: =CONTAR.SI(Aves.E$2:Aves.E$1000;A2)

Los $ sirven para que no se muevan los valores al arrastrar.

Al dar a aceptar, ya tenemos el primer valor:

Arrastrando la fórmula podemos tener ya hechas hasta “El río”, pero como no hay categoría “Otros”, para esta no nos vale:

Para calcular los otros, primero vamos a calcular la suma con la función CONTARA, que sólo nos pide el intervalo:

Y ya los “Otros serán el valor de la suma (B7) menos la suma de los valores anteriores (B2:B5):

Tabla de contingencia algo más complicada

Si queremos hacer una tabla más complicada, como por ejemplo la tabla de contingencia con los diferentes nichos de las aves de, “Campus de la UPV”, sólo tenemos que utilizar la función CONTAR.SI.CONJUNTO, para poner varios intervalos y criterios. También pueden ser útiles dos funciones muy interesantes:

  • Y()
  • O()

La primera de ellas da un valor verdadero si se cumple todo lo que hay dentro, mientras que la segunda da un valor verdadero si se cumple alguno de los criterios que hay dentro.

Así por ejemplo la fórmula para las aves del “Campus de la UPV”, que se encuentran “Sobre el cesped a la sombra”, será:

                                   =CONTAR.SI.CONJUNTO($Aves.E2:E1000; “Campus de la UPV”; $Aves.H2:H1000; “Sobre el cesped a la sombra” )

Espero que os sea útil. Si es así o encontráis algún error, por favor, comentar.

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.