Funcións estatísticas de Excel para calcular medias: Titorial con exemplos, segunda parte

Excel ofrece unha ampla gama de funcións estatísticas que realizan cálculos desde a media básica, a mediana e a moda ata distribucións estatísticas máis complexas e probas de probabilidade.

Neste artigo afondaremos nas funcións estatísticas de Excel para calcular a media.

Teña en conta que algunhas funcións estatísticas foron introducidas nas versións recentes de Excel e, polo tanto, non están dispoñibles nas versións anteriores.

Funcións para calcular medias

AVERAGE

A función AVERAGE é unha das funcións estatísticas de Excel. A función devolve a media dos valores numéricos introducidos na función. En palabras simples, engade todos os valores especificados na función, logo divídeos polo reconto e devolve o resultado.

sintaxe

= AVERAGE(number1,number2,…)

argumentos

• numero1 : O primeiro número que quere utilizar para calcular a media.
• [numero2] : O segundo número que quere usar para facer a media.

exemplo

Para ver como funciona a función AVERAGE vexamos un exemplo:

No primeiro exemplo inserimos os argumentos directamente na función.

No segundo exemplo, fixemos referencia a un intervalo que contén números. Podes referirte á cela sen límites usando un intervalo continuo e se queres referirte a un intervalo dinámico podes usar unha táboa para iso.

Pode referirse á cela sen límites usando un intervalo continuo e se quere referirse a un intervalo dinámico pode usar unha táboa.

No terceiro exemplo referímonos a un intervalo no que as celas teñen formato como valores de texto. Neste caso, pode converter eses números de texto en números reais para calcular a media.

No cuarto exemplo temos un apóstrofo antes de cada valor en cada cela e, polo tanto, ignorado pola función.

AVERAGEA

A función AVERAGEA de Excel aparece na categoría Funcións estatísticas de Microsoft Excel. Devolve a media dos números especificados en función, pero a diferenza AVERAGE, trata os valores booleanos e os números con formato de texto.

sintaxe

=AVERAGEA(valore1,valore2,…)

argumentos

• value1 : un valor que é un número, un valor lóxico ou un número almacenado como texto.
• [valore2] : un valor que é un número, un valor lóxico ou un número almacenado como texto.

exemplo

Para entender a función AVERAGEA necesitamos ver un exemplo:

O valor devolto pola función é 10,17 que é "(0+0+1+10+20+30)/6".

AVERAGEIF

A función AVERAGEIF de Excel aparece na categoría Funcións estatísticas de Microsoft Excel. Devolve a media dos números que cumpren varias condicións especificadas . 

sintaxe

= AVERAGEIF( range, criteria, [average_range] )

Temas

• range:  unha matriz de valores (ou un rango de celas que conteñen valores) para probar cos criterios proporcionados.
• criteria:  A condición a probar con cada un dos valores do intervalo proporcionado.
• [average_range]:  Unha matriz opcional de valores numéricos (ou celas que conteñan números) que se deberían promediar se o valor correspondente no intervalo cumpre os criterios proporcionados.

Se o tema [average_range] omítese, calcúlase a media para os valores no intervalo inicial proporcionado.

Os criterios proporcionados poden ser:

un valor numérico (incluíndo números enteiros, decimais, datas, horas e valores lóxicos) (por exemplo, 10, 01/01/2008, TRUE)
O
unha cadea de texto (por exemplo, "Texto", "Xoves"): DEBE proporcionarse entre comiñas
O
unha expresión (por exemplo, ">12", "<>0") - DEBE proporcionarse entre comiñas.
Tamén teña en conta que a función AVERAGEIF Excel non distingue entre maiúsculas e minúsculas. Así, por exemplo, as cadeas de texto "TEXT"E"text” será avaliado como igual.

exemplo

Para entender a función AVERAGEIF temos que probalo nun exemplo.

As células A16-A20 da seguinte folla de cálculo mostran cinco exemplos da función AVERAGEIF de Excel.

Para cada chamada de función AVERAGEIF de Excel, o tema range (para ser probado contra criteria) é o intervalo de celas A1-A14 e o tema [average_range] (que contén os valores que se deben promediar) é o intervalo de celas B1-B14.

Teña en conta que, nas celas A16, A18 e A20 da folla de cálculo anterior, o valor de texto "Xoves" e as expresións ">2" e "<>TRUE” están entre comiñas. Isto é esencial para todos os textos ou expresións.

AVERAGEIFS

A función AVERAGEIFS de Excel aparece na categoría Funcións estatísticas de Microsoft Excel. Devolve a media dos números que cumpren varias condicións especificadas . A diferenza AVERAGEIF, pode establecer varias condicións e calcular a media só para os números que cumpran todas as condicións.

sintaxe

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Temas

• average_range:  Unha matriz de valores numéricos (ou celas que conteñan números) que se deben promediar.
• criteria_range1, [criteria_range2], …: matrices de valores (ou intervalos de celas que conteñen valores) para probar uns contra outros criteria1, criteria2, … (As matrices criteria_range subministrados deben ter todos a mesma lonxitude).
• criteria1, [criteria2], …: As condicións a probar con respecto aos valores en criteria_range1, [criteria_range2], …

exemplo

Agora vexamos un exemplo da función AVERAGEIFS:

No seguinte exemplo, usamos a función AVERAGEIFS para calcular a cantidade media vendida polo vendedor “Pietro” e para o produto “B”. Introducimos os criterios directamente na función e temos dúas entradas da venda do produto B de Peter.

No seguinte exemplo, usamos AVERAGEIFS cun asterisco para calcular o prezo medio da froita cuxa cantidade sexa superior a 20 unidades e teña B no nome.

Nos datos a continuación, temos dous froitos que cumpren estes criterios.

MEDIAN

A función MEDIAN Excel devolve a mediana estatística (o valor medio) dunha lista de números proporcionados.

sintaxe

= MEDIAN( number1, [number2], ... )

Temas

Os argumentos numéricos son un conxunto de un ou máis valores numéricos (ou matrices de valores numéricos), para os que quere calcular a mediana

Teña en conta que:

• Se hai un número par de valores no conxunto de datos dado, devólvese a media dos dous valores medios;
• Se unha matriz proporcionada contén celas en branco, texto ou valores lóxicos, estes valores ignóranse ao calcular a mediana.
• Nas versións actuais de Excel (Excel 2007 e posteriores), pode proporcionar ata 255 argumentos numéricos á función Mediana, pero en Excel 2003 a función só pode aceptar ata 30 argumentos numéricos. Non obstante, cada un dos argumentos numéricos pode ser unha matriz de moitos valores.

exemplo

A seguinte folla de cálculo mostra tres exemplos da función Median:

Considere que, nos exemplos anteriores:

• O exemplo na cela B2 recibe un número par de valores e, polo tanto, a mediana calcúlase como a media dos dous valores medios, 8 e 9;
• O exemplo na cela B3 inclúe a cela baleira A8. Esta cela ignórase ao calcular a mediana.

Para máis detalles sobre a función MEDIAN de Excel, consulte o Sitio web de Microsoft Office .

MODE

A función MODE de Excel devolve o MODE estatística (o valor máis frecuente) dunha lista de números proporcionados. Se hai 2 ou máis valores recorrentes nos datos proporcionados, a función devolve o valor máis baixo entre eles

sintaxe

= MODE( number1, [number2], ... )

Temas

son un conxunto de un ou máis valores numéricos (ou matrices de valores numéricos), para os que quere calcular o MODE estatísticas.

Nota:

• Nas versións actuais de Excel (Excel 2007 e posteriores), pode proporcionar ata 255 argumentos numéricos á función MODE, pero en Excel 2003 a función só pode aceptar ata 30 argumentos numéricos.
• A función ignora o texto e os valores lóxicos dentro dunha matriz de números proporcionada Mode.

Exemplos de funcións MODE

Exemplo 1

A seguinte folla de cálculo mostra a función MODE Excel, usado para calcular MODE estatísticas do conxunto de valores nas celas A1-A6.

Exemplo 2

A seguinte folla de cálculo mostra a función MODE, usado para calcular o MODE estatísticas do conxunto de valores nas celas A1-A10.

Teña en conta que neste caso hai dous mode nos datos.

No caso anterior, onde os datos da columna A da folla de cálculo anterior teñen dous MODE estatísticas (3 e 4), a función MODE devolve o menor destes dous valores.

Para máis detalles e exemplos da función MODE de Excel, consulte o Sitio web de Microsoft Office .

MODE.SNGL

A función MODE.SNGL de Excel devolve o MODE estatística (o valor máis frecuente) dunha lista de números proporcionados. Se hai 2 ou máis valores recorrentes nos datos proporcionados, a función devolve o valor máis baixo entre eles.

A función Mode.Sngl é novo en Excel 2010 e, polo tanto, non está dispoñible en versións anteriores de Excel. Non obstante, a función é simplemente unha versión renomeada da función MODE dispoñible en versións anteriores de Excel.

sintaxe

= MODE.SNGL( number1, [number2], ... )

Temas

son un conxunto de un ou máis valores numéricos (ou matrices de valores numéricos), para os que quere calcular o MODE.SNGL estatísticas.

Exemplos de funcións MODE.SNGL

Exemplo 1

A seguinte folla de cálculo mostra a función MODE.SNGL Excel, usado para calcular o MODO estatístico do conxunto de valores nas celas A1-A6.

Exemplo 2

A seguinte folla de cálculo mostra a función MODE.SNGL, usado para calcular o modo estatístico do conxunto de valores nas celas A1-A10.

Teña en conta que neste caso hai dous mode nos datos.

No caso anterior, onde os datos da columna A da folla de cálculo anterior teñen dous MODE estatísticas (3 e 4), a función MODE.SNGL devolve o menor destes dous valores.

Para máis detalles e exemplos da función MODE.SNGL de Excel, consulte o Sitio web de Microsoft Office .

GEOMEAN

A media xeométrica é unha medida da media que indica o valor típico dun conxunto de números. Esta medida só se pode usar para valores positivos.

A media xeométrica dun conxunto de valores, y ₁ E ₂ ,..., alí _n calcúlase coa fórmula:

Teña en conta que a media xeométrica é sempre menor ou igual á media aritmética.

A función Geomean Excel calcula a media xeométrica dun determinado conxunto de valores.

sintaxe

= GEOMEAN( number1, [number2], ... )

Temas

un ou máis valores numéricos positivos (ou matrices de valores numéricos), para os que quere calcular a media xeométrica.

Nas versións actuais de Excel (Excel 2007 e posteriores), a función pode aceptar ata 255 argumentos numéricos, pero en Excel 2003 a función só pode aceptar ata 30 argumentos numéricos. Non obstante, cada argumento pode ser unha matriz de valores ou un rango de celas, cada unha das cales pode conter moitos valores.

exemplo

A célula B1 da folla de cálculo mostra un exemplo sinxelo da función geomean en Excel, usado para calcular a media xeométrica dos valores nas celas A1-A5.

Neste exemplo, a función Geomean devolve o valor 1.622671112 .

HARMEAN

A media harmónica é unha medida da media calculada como o recíproco da media aritmética dos recíprocos. Isto só se pode calcular para valores positivos.

A media harmónica dun conxunto de valores, y1, y2, ..., yn vén dada pola fórmula:

a media harmónica é sempre menor ou igual á media xeométrica e a media xeométrica é sempre menor ou igual á media aritmética.

A función Harmean Excel calcula a media harmónica dun determinado conxunto de valores.

sintaxe

= HARMEAN( number1, [number2], ... )

Temas

un ou máis valores numéricos positivos (ou matrices de valores numéricos), para os que quere calcular a media harmónica.

Nas versións actuais de Excel (Excel 2007 e posteriores), a función pode aceptar ata 255 argumentos numéricos, pero en Excel 2003 a función só pode aceptar ata 30 argumentos numéricos. Non obstante, cada argumento pode ser unha matriz de valores ou un rango de celas, cada unha das cales pode conter moitos valores.

exemplo

A cela B1 da folla de cálculo da dereita mostra un exemplo sinxelo da función Harmean en Excel, usado para calcular a media harmónica dos valores nas celas A1-A5.

Neste exemplo, a función Harmean devolve o valor 1.229508197.

TRIMMEAN

A función TRIMMEAN (tamén coñecida como media recortada) é unha medida da media que indica a tendencia central dun conxunto de valores.

A media recortada calcúlase descartando algúns valores nos extremos do intervalo de valores, antes de calcular a media aritmética dos valores restantes. Isto evita que a media calculada sexa distorsionada por valores extremos (tamén coñecidos como valores atípicos, tecnicamente outliers).

sintaxe

= TRIMMEAN( array, percent )

Temas

• orde – Unha matriz de valores numéricos para os que quere calcular a media truncada.
• por cento - A porcentaxe de valores dos que queres eliminararray provedor.

Teña en conta que o valor porcentual especificado é a porcentaxe total dos valores a excluír do cálculo. Esta porcentaxe divídese por dous para obter o número de valores eliminados de cada extremo do intervalo.

Tamén hai que ter en conta que cando Excel calcula cantos valores borrados doarray dos valores proporcionados, a porcentaxe calculada redondea á baixa ao múltiplo máis próximo de 2. Por exemplo, se quere calcular a media recortada de a array de 10 valores, polo tanto:

• Unha porcentaxe do 15 % corresponde a 1,5 valores, que se redondearán a 0 (é dicir, non se descartará ningún valor doarray antes de calcular a media);
• Unha porcentaxe do 20 % corresponde a 2 valores, polo que descartarase 1 valor de cada extremo do intervalo antes de promediar os valores restantes;
• Unha porcentaxe do 25 % corresponde a 2,5 valores, que se redondearán a 2 cara abaixo (é dicir, descartarase 1 valor de cada extremo do intervalo antes de facer a media dos valores restantes).

exemplo

As células B1-B3 na folla de cálculo a continuación móstranse 3 exemplos da función trimmean en Excel, todo usado para calcular a media recortada dos valores nas celas A1-A10, para diferentes valores porcentuais.

Teña en conta que, na cela B1 da folla de cálculo anterior, o argumento porcentual indicado é do 15%. Xa que noarray sempre que haxa 10 valores, o número de valores a ignorar é 1,5 redondeado ao múltiplo máis próximo de 2, que é cero.

Funcións para o cálculo de permutacións

PERMUT

O número de permutacións para un determinado número de obxectos é o número de combinacións en calquera orde posible.

As permutacións difiren das combinacións en que, para unha permutación, a orde dos obxectos importa, pero nunha combinación a orde non importa.

O número de posibles permutacións vén dado pola fórmula:

pomba k é o número de obxectos escollidos e n é o número de obxectos posibles.

A función de Excel Permut calcula o número de permutacións dun número especificado de obxectos a partir dun conxunto de obxectos.

sintaxe

= PERMUT( number, number_chosen )

Temas

• number: número total de elementos dispoñibles
• number_chosen: O número de obxectos en cada permutación (é dicir, o número de obxectos seleccionados do conxunto)

Teña en conta que se algún dos argumentos se dá como valores decimais, a función truncará a números enteiros. Permut.

exemplo

Na seguinte folla de cálculo, o Excel Permut úsase para calcular o número de permutacións de seis obxectos, seleccionados entre conxuntos de diferentes tamaños:

PERMUTATIONA

Funcións de Excel Intercambio e Permutationa calculan o número de permutacións dunha selección de obxectos dun conxunto.

Non obstante, as dúas funcións difiren en que o función permut non conta repeticións mentres que a función Permutationa conta repeticións.

Por exemplo, nun conxunto de 3 obxectos, a , b , c , cantas permutacións hai de 2 obxectos?

• La función permut devolve o resultado 6 (permutacións: ab , ac , ba , bc , ca , cb );
• A función Permutationa devolve o resultado 9 (permutacións: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

A función de Excel Permutationa calcula o número de permutacións dun número especificado de obxectos a partir dun conxunto de obxectos.

sintaxe

= PERMUTATIONA( number, number_chosen )

Temas

• number: O número total de obxectos do conxunto (debe ser ≥ 0).
• number_chosen: O número de obxectos seleccionados do conxunto (debe ser ≥ 0).

Teña en conta que se algún dos argumentos se dá como valores decimais, a función truncará a números enteiros. PERMUTATIONA.

exemplo

Na seguinte folla de cálculo, o Excel PERMUTATIONA úsase para calcular o número de permutacións de seis obxectos, seleccionados entre conxuntos de diferentes tamaños:

Funcións para calcular intervalos de confianza

CONFIDENCE

En Excel 2010, a función CONFIDENCE substituíuse pola función Confidence.Norm.

Aínda que foi substituído, as versións actuais de Excel aínda teñen a función Confidence (almacenados na lista de funcións de compatibilidade), para permitir a compatibilidade con versións anteriores de Excel.

Con todo, a función Confidence é posible que non estea dispoñible en futuras versións de Excel, polo que recomendamos usar a función Confidence.Norm, se é posible.

A función Confidence Excel usa unha distribución normal para calcular un valor de confianza que se pode usar para construír o intervalo de confianza para unha media poboacional, unha probabilidade determinada e un tamaño da mostra. Suponse que se coñece a desviación estándar da poboación.

sintaxe

= CONFIDENCE( alpha, standard_dev, size )

Temas

• alfa: O nivel de significación (= 1 – nivel de confianza). (Por exemplo, un nivel de significación de 0,05 equivale a un nivel de confianza do 95 %).
• standard_dev: a desviación típica da poboación.
• size: O tamaño da mostra poboacional.

Para calcular o intervalo de confianza para unha media poboacional, o valor de confianza devolto debe engadirse e restarlle á media mostral. Quere dicir que. para a media mostral x:

Confidence Interval =   x   ±   CONFIDENCE

exemplo

Na seguinte folla de cálculo, a función de confianza de Excel úsase para calcular o intervalo de confianza cunha significación de 0,05 (é dicir, un nivel de confianza do 95%) para a media dunha mostra de 100 estaturas de homes. A media mostral é de 1,8 metros e a desviación estándar é de 0,07 metros.

A función anterior devolve un valor de confianza de 0,013719748

Polo tanto, o intervalo de confianza é 1,8 ± 0,013719748, o que equivale ao intervalo entre 1,786280252 e 1,813719748

CONFIDENCE.NORM

En estatística, o intervalo de confianza é o rango dentro do que é probable que caia un parámetro de poboación, para unha probabilidade dada.

Por exemplo. Para unha poboación determinada e unha probabilidade do 95 %, o intervalo de confianza é o intervalo no que un parámetro de poboación ten un 95 % de probabilidades de caer.

Nótese que a precisión do intervalo de confianza depende de que a poboación teña unha distribución normal.

A función Confidence.Norm Excel usa unha distribución normal para calcular un valor de confianza que se pode usar para construír o intervalo de confianza para unha media poboacional, unha probabilidade determinada e un tamaño da mostra. Suponse que se coñece a desviación estándar da poboación.

sintaxe

= CONFIDENCE.NORM( alpha, standard_dev, size )

Temas

• alfa: O nivel de significación (= 1 – nivel de confianza). (Por exemplo, un nivel de significación de 0,05 equivale a un nivel de confianza do 95 %).
• standard_dev: a desviación típica da poboación.
• size: O tamaño da mostra poboacional.

Para calcular o intervalo de confianza para unha media poboacional, o valor de confianza devolto debe engadirse e restarlle á media mostral. Quere dicir que. para a media mostral x:

Confidence Interval =   x   ±   CONFIDENCE

exemplo

Na seguinte folla de cálculo, a función de confianza de Excel úsase para calcular o intervalo de confianza cunha significación de 0,05 (é dicir, un nivel de confianza do 95%) para a media dunha mostra de 100 estaturas de homes. A media mostral é de 1,8 metros e a desviación estándar é de 0,07 metros.

A función anterior devolve un valor de confianza de 0,013719748

Polo tanto, o intervalo de confianza é 1,8 ± 0,013719748, o que equivale ao intervalo entre 1,786280252 e 1,813719748

Ercole Palmeri