Microsoft Excel (sovint anomenat Excel) és un potent programari de fulls de càlcul dissenyat per emmagatzemar grans conjunts de dades. A més, permet als usuaris organitzar i analitzar dades i fins i tot realitzar càlculs bàsics o complexos sobre les dades enregistrades. Les tasques essencials d'anàlisi de dades d'Excel inclouen principalment calcular o trobar la mitjana (o mitjana aritmètica), la moda i la mediana.
En aquest tutorial, parlem de diversos mètodes o solucions pas a pas sobre com calcular la mitjana a Excel. Abans de parlar dels mètodes, primer entenem breument el concepte de mitjana i els seus beneficis.
Quina és la mitjana en Excel?
A Excel, la mitjana aritmètica no és més que un nombre mitjà que normalment es calcula sumant tots els nombres d'un conjunt de dades donat i després dividint la suma pel nombre total de valors (o recompte) d'elements donats.
Tot i que és fàcil calcular la mitjana aritmètica per a conjunts de dades petits a la vida real, es fa difícil treballar amb conjunts de dades grans. Aquí és on un programa potent de fulls de càlcul com Excel és útil. Pot calcular fàcilment la mitjana de centenars a milers de nombres. A més, és menys propens a errors.
Hi ha molts usos essencials de la mitjana en l'anàlisi de dades, com ara:
- Per comparar dades històriques
- Per destacar els KPI (Key Performance Indicators) i les cotitzacions
- Per trobar el punt mitjà dels punts de dades donats
- Determinar la gestió de projectes i plans de negoci
L'escenari bàsic en què és possible que necessitem calcular la Mitjana a Excel és quan necessitem conèixer les vendes mitjanes d'un article en tot un mes, trimestre, any, etc.
diccionari c#
Com calcular la mitjana a Excel?
Malauradament, no hi ha una funció MEAN definida per calcular o determinar la mitjana aritmètica de les dades. Tanmateix, hi ha un parell de maneres efectives. En general, normalment podem aplicar el concepte matemàtic d'afegir valors i després dividir la seva suma pel nombre de valors dins de les cel·les d'Excel. A més, existeixen funcions com AVERAGE, AVERAGEA, AVERAGEIF i AVERAGEIFS, que també ens ajuden a calcular la Mitjana Aritmètica en diferents casos d'ús.
Per tenir una millor idea de com funcionen aquests conceptes per calcular la mitjana aritmètica, parlem de cadascun d'ells individualment amb un exemple:
Càlcul de la mitjana en Excel: Mètode general
Podem aplicar les funcions SUMA i COUNT en un full d'Excel per utilitzar l'enfocament d'afegir valors i dividir-los pel nombre de valors inclosos.
Per exemple, suposem que hem donat els nombres de l'1 al 5 i hem de calcular la mitjana aritmètica. Matemàticament, primer sumem tots aquests nombres, és a dir, (1+2+3+4+5). Després, dividim la seva SUMA per 5 perquè és el recompte total de nombres donats.
Així, tota la fórmula per calcular la mitjana es converteix en aquesta:
(1+2+3+4+5)/ 5
Això retorna la mitjana aritmètica 3.
A Excel, podem utilitzar la fórmula matemàtica anterior combinant-la amb les funcions SUMA i COUNT de la següent manera:
=SUMA(1,2,3,4,5)/COUNT(1,2,3,4,5)
Aquí, la funció SUMA suma els valors donats, mentre que la funció COUNT determina el nombre de valors donats.
Ara, si registrem aquests cinc nombres a l'interval A1:A5, la fórmula següent a la cel·la A6 calcularà la mitjana aritmètica:
=SUMA(A1:A5)/COUNT(A1:A5)
El resultat (o mitjana) obtingut a la imatge anterior és el mateix que hem calculat a partir de la fórmula matemàtica, és a dir, 3.
Càlcul de la mitjana en Excel: funció MITJANA
Com que la mitjana és una mitjana, la funció MITJANA d'Excel és la forma més eficient de calcular la mitjana aritmètica. És una de les funcions estadístiques més utilitzades per calcular la mitjana d'un interval determinat si no hi ha cap condició o limitació especial. És la funció d'Excel integrada.
Sintaxi
La sintaxi de la funció MITJANA d'Excel és la següent:
=MITJANA(número1, [nombre2], ...)
El número1 és l'argument requerit, és a dir, un argument és obligatori a la funció MITJANA. Tanmateix, podem proporcionar múltiples arguments en diferents formes com números, cel·les, rangs, matrius, constants, etc. Quan calculem la mitjana aritmètica, hauríem de proporcionar almenys dos arguments a la funció MITJANA.
Passos per calcular la mitjana utilitzant la funció MITJANA
Entenem ara el funcionament de la funció MITJANA i calculem la mitjana aritmètica d'un grup de nombres en un full d'Excel utilitzant les dades de mostra.
Suposem que enumerem algunes fruites en un full d'Excel amb els seus preus corresponents. Els noms de fruites s'anoten a la columna A (A2:A9), i els seus preus es troben a la següent columna B (B2:B9). Hem de trobar o calcular el preu mitjà (o la mitjana aritmètica dels preus) a la cel·la B10.
programa d'encapsulació
Hem de realitzar els passos següents per calcular la mitjana aritmètica al nostre full de treball d'exemple:
- En primer lloc, hem de seleccionar o destacar la cel·la per registrar la mitjana aritmètica resultant, és a dir, la cel·la B10.
- A continuació, hem d'inserir el nom de la funció començant amb un signe igual i proporcionar els arguments necessaris a la funció. En el nostre cas, escrivim el signe igual (=) i la funció MITJANA. També podem triar la funció de la llista de funcions suggerides per Excel.
- Després d'introduir el nom de la funció, hem d'introduir el parèntesi inicial i proporcionar el nostre rang efectiu de conjunts de dades. Com que tenim dades a cel·les adjacents o un interval (de B2 a B9), podem passar tot l'interval a la funció de la següent manera:
- Finalment, hem d'introduir el parèntesi de tancament i prémer el botó Entra tecla del teclat per obtenir el resultat efectiu o la mitjana aritmètica per al conjunt de dades donat. La imatge següent mostra la mitjana aritmètica dels preus a les cel·les de B2 a B9:
A la imatge de dalt, hem utilitzat les cel·les adjacents i hem subministrat tota la gamma de conjunts de dades d'una vegada. Tanmateix, també podem calcular la mitjana de les cel·les no adjacents del nostre full d'Excel proporcionant les cel·les individualment.
Suposem que només necessitem calcular la mitjana aritmètica dels preus de només tres fruits: Poma, mango i raïm. En aquest cas, podem passar cada cel·la per separat a la funció de la manera següent:
=MITJANA(B2;B3;B6)
Aquí, B2, B3 i B6 són les cel·les individuals amb els preus de poma, mango i raïm.
Encara que la funció MITJANA es pot utilitzar amb cel·les buides, textos, zeros, valors lògics i fins i tot arguments mixts, ignora les cel·les buides, el text i els valors lògics durant el càlcul. Tanmateix, les cel·les de valor zero s'inclouen a la funció MITJANA.
Càlcul de la mitjana en Excel: funció MITJANA
La funció MITJANA és una versió lleugerament avançada de la funció MITJANA. La funció AVERAGEA també ens ajuda a calcular la mitjana aritmètica per a conjunts de dades determinats i funciona en totes les versions d'Excel. Un dels avantatges de la funció AVERAGEA és que pot incloure valors de text i tipus de dades booleans mentre es calcula la mitjana a Excel. Els valors del text s'avaluen com a zeros, mentre que els valors booleans TRUE i FALSE s'avaluen com a 1 i 0, respectivament.
Sintaxi
La sintaxi de la funció AVERAGEA d'Excel és la següent:
=MITJANA(valor1, [valor2], ...)
Igual que la funció MITJANA, cal el primer argument de la funció MITJANA. Tanmateix, podem utilitzar diversos arguments segons sigui necessari.
La sintaxi de les funcions MITJANA i MITJANA és gairebé semblant; tanmateix, el seu concepte de treball és lleugerament diferent. La funció MITJANA només pot calcular la mitjana aritmètica dels valors numèrics, mentre que la funció MITJANA pot calcular la mateixa per a qualsevol tipus de dades, inclosos el text i els booleans.
Passos per calcular la mitjana mitjançant la funció AVERAGEA
Reprenem els exemples anteriors de conjunts de dades on tenim noms de fruites a la columna A (A2:A9) i els seus preus a la columna B (B2:B9). Tanmateix, aquesta vegada no tenim el preu del raïm (enregistrat com a 'No disponible' a la cel·la B6 corresponent), tal com es mostra a continuació:
forma completa de i d e
Podem executar els passos següents i calcular el valor mitjà mitjançant la funció MITJANA:
- Com el mètode anterior, primer seleccionem la cel·la resultant B10 per registrar la mitjana. A continuació, escrivim el signe igual (=), introduïm o seleccionem la nostra funció MITJANA i passem tot el rang de dades a la funció inclosa entre parèntesis.
- Finalment, hem de prémer el botó Entra clau per obtenir una producció (o mitjana) efectiva.
La imatge de dalt retorna la sortida mitjana de 57,125 amb la funció AVERAGEA.
Si fem servir la funció MITJANA en aquest exemple i calculem la mitjana aritmètica, veiem una gran diferència entre els resultats obtinguts per ambdues funcions. La sortida mitjana de 65,285 apareix amb la funció MITJANA, tal com es mostra a continuació:
Les diferències en la mitjana aritmètica d'aquest exemple tenen sentit perquè el nombre de recompte de divisió és diferent per a ambdues funcions. El recompte de nombres per a la funció MITJANA és 8, ja que també avalua el TEXT com a dades vàlides. Però, per a la funció MITJANA, el nombre de divisió és 7, ja que no inclou les dades de TEXT en el seu càlcul.
En lloc de dades TEXT, si el conjunt de dades conté zero (0), apareixerà el mateix resultat (o mitjanes aritmètiques) per a ambdues funcions (MITJANA i MITJANA) perquè totes dues inclouen zero com a dades vàlides en el càlcul.
Càlcul de la mitjana en Excel: funció AVERAGEIF
La funció MITJANASI és la funció rellevant de la funció MITJANA i ens ajuda a calcular la mitjana aritmètica amb una condició o criteris determinats. Amb la funció AVERAGEIF només podem calcular la Mitjana per a determinats nombres que segueixen els criteris especificats. Excel primer avalua els criteris específics, localitza les cel·les efectives seguint aquests criteris i després calcula la mitjana només per a les cel·les localitzades en conseqüència.
Sintaxi
La sintaxi de la funció AVERAGEIF d'Excel és la següent:
=AVERAGEAIF(interval, criteris, [interval_mitjana])
Aquí, l'interval i els criteris són els arguments necessaris, mentre que l'interval_medium és opcional.
- Un interval conté cel·les que s'han de provar segons els criteris donats.
- Un criteri fa referència a l'argument on podem especificar la condició desitjada a seguir. Pot incloure text, valors numèrics, referència de cel·les, rang i expressió lògica.
- L'interval_mitjana és l'argument on esmentem les cel·les (o un interval) per al qual volem calcular la mitjana. Si no s'especifica aquest argument, Excel utilitza l'interval automàticament.
Passos per calcular la mitjana mitjançant la funció AVERAGEIF
En el mateix exemple, suposem que enumerem algunes fruites diverses vegades amb preus diferents. Aquesta vegada, només hem de calcular la mitjana de qualsevol fruita repetida específica (és a dir, Apple) a la cel·la B11. Per tant, utilitzem la funció AVERAGEIF i apliquem la condició mentre calculem la mitjana. Hem d'executar els passos següents per calcular el preu mitjà d'Apple:
- Primer, seleccionem la cel·la resultant (B11) i inserim la fórmula MITJANASI de la manera següent:
=MITJANASI(A2:B9, 'Poma', B2:B9)
Aquí, A2:B9 representa l'interval efectiu, Apple representa el nostre criteri/condició desitjat i B2:B9 representa l'interval_mitjana.
Aquí, hem subministrat manualment l'interval_medi opcional per incloure només les cel·les amb els preus. En el nostre exemple, si no passem l'interval_medium, la funció utilitzarà l'interval principal (A2:B9) amb les dades de text i provocarà el #DIV! error.
Nota: és essencial tenir en compte que la funció AVERAGEIF ignora les cel·les en blanc en el seu argument average_range. A més, si alguna cel·la especificada als criteris està en blanc, la funció la tracta com una cel·la de valor zero.
Càlcul de la mitjana en Excel: funció AVERAGEIFS
La funció AVERAGEIFS és una versió ampliada o avançada de la funció AVERAGEIF. La funció AVERAGEIF només ens permet aplicar una condició a la vegada. Tanmateix, amb la funció AVERAGEIFS, podem aplicar diversos criteris per ser provats simultàniament. A més, la funció AVERAGEIFS ens permet utilitzar cel·les amb valors booleans TRUE i FALSE, ajudant-nos a provar diversos criteris fàcilment. Això tampoc és possible amb la funció AVERAGEIF.
Sintaxi
emet sql
La sintaxi de la funció AVERAGEIFS d'Excel és la següent:
=AVERAGEAIFS(interval_mitjana, rang_criteri1, criteri1, [interval_criteri2, criteri2], ...)
Aquí, l'interval_medium, el rang_criteri1 i el criteri1 són els arguments necessaris. Tanmateix, podem proporcionar els arguments opcionals basats en els nostres conjunts de dades efectius. A més, criteri1 fa referència a una condició que s'ha de provar al rang1, criteri2 es refereix a una condició que s'ha de provar al rang2 i més.
Passos per calcular la mitjana mitjançant la funció AVERAGEIFS
Considerem les mateixes dades d'exemple que hem utilitzat en el mètode anterior. En el mètode anterior, vam utilitzar la funció AVERAGEIF i vam calcular la Mitjana dels preus de la fruita repetida (Poma). En aquest cas, només teníem una condició.
Ara, suposem que hem de calcular la mitjana dels preus d'Apple, però només incloure aquelles cel·les on els preus són superiors a 40.
Tenim dos criteris diferents en aquest cas, de manera que no es pot utilitzar la funció AVERAGEIF.
Criteri 1: En un rang A2:A9, el criteri inicial és comprovar si hi ha 'Apple' que compleixen tres cel·les (o files)
Criteri 2: En un rang B2:B9, l'altre criteri és comprovar els preus superiors a 40 (>40) que compleixen les dues de cada tres cel·les (o fila) amb una poma de fruita.
Així, si calculem matemàticament la mitjana, utilitzem la fórmula següent:
(95+68)/2
prova de capturar en java
Això retorna el valor mitjà de 81.5. Comprovem si obtenim els mateixos resultats amb la funció AVERAGEIFS a Excel.
Quan necessitem provar condicions a Excel, normalment ens ajudem a l'operador lògic OR. L'operador OR retorna TRUE i FALSE depenent de si la condició donada compleix. Per exemple, suposem que hem de comprovar si la cel·la A2 conté Apple. En aquest cas, podem utilitzar l'operador OR de la següent manera:
=O(A2='Poma')
Inseriu la condició anterior al nostre full d'exemple per provar o localitzar cèl·lules amb la fruita Apple. Per tant, creem una nova columna (C) i escrivim la condició específica a la cel·la C2. Després, copiem la mateixa condició a les cel·les restants de la columna C. Sembla així:
La imatge de dalt mostra que l'operador OR retorna TRUE per a les cel·les corresponents a la fruita Apple, mentre que per a altres, retorna FALSE. A partir d'aquest resultat, ara apliquem la funció AVERAGEIFS i inserim diverses condicions per a les cel·les de valor TRUE de la manera següent:
- Com altres mètodes, primer seleccionem la cel·la resultant (és a dir, B12).
- A continuació, introduïm la fórmula AVERAGEIFS de la manera següent:
=MITJANASI(B2:B9;C2:C9;'VERTADER'; B2:B9;'>40')
Amb la fórmula AVERAGEIFS anterior, només hem inclòs la fruita 'Poma' i preus per sobre de 40. - Finalment, hem de prémer el botó Entra clau per obtenir el resultat de la fórmula. Per al nostre conjunt de dades, la fórmula va retornar la mitjana aritmètica de 81.5, el mateix que el nostre resultat calculat matemàticament.
Casos especials
Tot i que podem tenir diversos escenaris mentre calculem la mitjana aritmètica a Excel, aquí es comenten els dos més habituals. Hem de ser prudents a l'hora de calcular la mitjana aritmètica en aquests casos.
Càlcul de la mitjana incloent zeros
Quan necessitem calcular la mitjana amb els zeros, normalment podem utilitzar la funció MITJANA i proporcionar les cel·les efectives, incloses les cel·les de valor zero. La funció MITJANA inclou els zeros com a nombres vàlids en el seu càlcul.
Al conjunt de dades de mostra següent, apliquem la funció MITJANA per calcular la mitjana de les notes de dos estudiants diferents:
La imatge de dalt mostra els diferents valors mitjans per a tots dos estudiants. Si mirem les notes, la suma de les notes és la mateixa per als dos alumnes. El segon estudiant té zero punts en una assignatura (cel·la de valor zero), mentre que el primer estudiant està absent (cel·la de valor de text) en una assignatura.
Com que la funció MITJANA ignora els valors de text però inclou valors zero, el recompte de divisió varia per als dos estudiants. Per al primer alumne, el nombre de divisió és de 4, mentre que per a un altre alumne és de 5. Per tant, hi ha una diferència en els resultats mitjans. Tanmateix, també podem excloure explícitament valors zero si cal.
Càlcul de la mitjana sense zeros
Quan necessitem calcular la mitjana excloent els zeros, hem d'aprofitar la funció AVERAGEIF d'Excel. Podem aplicar la condició específica en aquesta funció, indicant a Excel que no inclogui les cel·les de valor zero durant el càlcul.
La fórmula AVERAGEIF a continuació es pot utilitzar per excloure valors zero mentre es calcula la mitjana a Excel:
=MITJANASI(cel·la o un interval,'>0')
Si inserim la fórmula anterior per a les nostres dades de mostra, apareixeran els mateixos resultats de valor mitjà per als dos estudiants.
La fórmula AVERAGEIF anterior ignora el text i les cel·les de valor zero. Per tant, els comptes de divisió són els mateixos (és a dir, 4), i també ho és la mitjana aritmètica de les notes dels dos estudiants.
Punts importants a recordar
- És important tenir en compte que qualsevol funció d'Excel, incloses MITJANA, MITJANA, MITJANAIF i MITJANAIFS, pot acceptar un màxim de 255 arguments.
- No es recomana escriure manualment el nom de la funció en una cel·la d'Excel. És millor seleccionar la funció desitjada de la llista de suggeriments per evitar els noms de funcions escrits malament i eliminar errors en els resultats.