Microsoft Excel (bieži saukta par Excel) ir jaudīga izklājlapu programmatūra, kas paredzēta lielu datu kopu glabāšanai. Tas arī ļauj lietotājiem organizēt un analizēt datus un pat veikt ierakstīto datu pamata vai sarežģītus aprēķinus. Programmas Excel būtiskie datu analīzes uzdevumi galvenokārt ietver vidējā (vai vidējā aritmētiskā), režīma un mediānas aprēķināšanu vai atrašanu.
struktūras masīvs c valodā
Šajā apmācībā mēs apspriežam dažādas soli pa solim metodes vai risinājumus, kā programmā Excel aprēķināt vidējo. Pirms metožu apspriešanas, vispirms īsi izprotam vidējā jēdzienu un tā priekšrocības.
Kas ir vidējais programmā Excel?
Programmā Excel vidējais aritmētiskais ir tikai vidējais skaitlis, ko parasti aprēķina, saskaitot visus dotās datu kopas skaitļus un pēc tam dalot summu ar kopējo vērtību skaitu (vai skaitu).
Lai gan reālajā dzīvē ir viegli aprēķināt vidējo aritmētisko mazām datu kopām, strādāt ar lielām datu kopām kļūst sarežģīti. Šeit noder tāda jaudīga izklājlapu programma kā Excel. Tas var viegli aprēķināt vidējo no simtiem līdz tūkstošiem skaitļu. Turklāt tas ir mazāk pakļauts kļūdām.
Datu analīzē ir daudz būtisku vidējo lietojumu, piemēram:
- Lai salīdzinātu vēsturiskos datus
- Lai izceltu KPI (galvenos veiktspējas rādītājus) un citātus
- Lai atrastu viduspunktu dotajiem datu punktiem
- Noteikt projektu vadību un biznesa plānus
Pamatscenārijs, kad mums var būt nepieciešams aprēķināt vidējo programmā Excel, ir tad, kad mums ir jānoskaidro preces vidējais pārdošanas apjoms veselā mēnesī, ceturksnī, gadā utt.
Kā aprēķināt vidējo programmā Excel?
Diemžēl nav noteiktas funkcijas MEAN, lai aprēķinātu vai noteiktu datu vidējo aritmētisko. Tomēr ir daži efektīvi veidi. Parasti mēs parasti varam izmantot matemātisko jēdzienu vērtību pievienošanai un pēc tam to summas dalīšanai ar vērtību skaitu Excel šūnās. Turklāt pastāv tādas funkcijas kā AVERAGE, AVERAGEA, AVERAGEIF un AVERAGEIFS, kas arī palīdz mums aprēķināt vidējo aritmētisko dažādos lietošanas gadījumos.
Lai iegūtu labāku priekšstatu par to, kā šie jēdzieni darbojas vidējā aritmētiskā aprēķināšanā, apspriedīsim katru atsevišķi ar piemēru:
Vidējās vērtības aprēķināšana programmā Excel: vispārīgā metode
Mēs varam izmantot funkcijas SUM un COUNT Excel lapā, lai izmantotu pieeju vērtību pievienošanai un dalīšanai ar iekļauto vērtību skaitu.
Piemēram, pieņemsim, ka esam devuši skaitļus no 1 līdz 5 un ir jāaprēķina vidējais aritmētiskais. Matemātiski mēs vispirms saskaitām visus šos skaitļus, t.i., (1+2+3+4+5). Pēc tam mēs dalām viņu SUMMU ar 5, jo tas ir kopējais doto skaitļu skaits.
Tādējādi visa vidējā aprēķināšanas formula kļūst šāda:
(1+2+3+4+5)/ 5
Tas atgriež vidējo aritmētisko 3.
Programmā Excel mēs varam izmantot iepriekš minēto matemātisko formulu, apvienojot to ar funkcijām SUM un COUNT šādā veidā:
=SUM(1,2,3,4,5)/SKAITS(1,2,3,4,5)
Šeit funkcija SUM saskaita dotās vērtības, bet funkcija COUNT nosaka doto vērtību skaitu.
Tagad, ja ierakstīsim šos piecus skaitļus diapazonā A1:A5, tālāk norādītā formula šūnā A6 aprēķinās vidējo aritmētisko:
char + int java
=SUMMA(A1:A5)/SKAITS(A1:A5)
Iepriekš redzamajā attēlā iegūtais rezultāts (vai vidējais) ir tāds pats, kā mēs aprēķinājām no matemātiskās formulas, t.i., 3.
Vidējās vērtības aprēķināšana programmā Excel: AVERAGE funkcija
Tā kā vidējais ir vidējais, Excel funkcija AVERAGE ir visefektīvākais veids, kā aprēķināt vidējo aritmētisko. Tā ir viena no visbiežāk izmantotajām statistikas funkcijām, lai aprēķinātu vidējo vērtību noteiktam diapazonam, ja nav īpašu nosacījumu vai ierobežojumu. Tā ir iebūvēta Excel funkcija.
Sintakse
Programmas Excel funkcijas AVERAGE sintakse ir šāda:
=VIDĒJS(skaitlis1, [skaitlis2], ...)
Skaitlis1 ir obligātais arguments, kas nozīmē, ka viens arguments ir obligāts funkcijā AVERAGE. Tomēr mēs varam sniegt vairākus argumentus dažādās formās, piemēram, skaitļos, šūnās, diapazonos, masīvos, konstantēs utt. Aprēķinot vidējo aritmētisko, funkcijā AVERAGE ir jāiesniedz vismaz divi argumenti.
Darbības, lai aprēķinātu vidējo, izmantojot funkciju AVERAGE
Tagad sapratīsim funkcijas AVERAGE darbību un aprēķināsim vidējo aritmētisko skaitļu grupai Excel lapā, izmantojot parauga datus.
Pieņemsim, ka mēs Excel lapā uzskaitām dažus augļus ar atbilstošām cenām. Augļu nosaukumi ir ierakstīti A ailē (A2:A9), un to cenas ir norādītas nākamajā B ailē (B2:B9). Šūnā B10 ir jāatrod vai jāaprēķina vidējā cena (vai cenu vidējā aritmētiskā).
Lai mūsu parauga darblapā aprēķinātu vidējo aritmētisko, ir jāveic šādas darbības:
- Pirmkārt, mums ir jāatlasa vai jāizceļ šūna, lai reģistrētu iegūto vidējo aritmētisko, t.i., šūnu B10.
- Tālāk mums ir jāievieto funkcijas nosaukums, kas sākas ar vienādības zīmi, un funkcijā jānorāda nepieciešamie argumenti. Mūsu gadījumā mēs ierakstām vienādības zīmi (=) un funkciju AVERAGE. Funkciju varam izvēlēties arī no Excel ieteiktā funkciju saraksta.
- Pēc funkcijas nosaukuma ievadīšanas mums jāievada sākuma iekavas un jāiesniedz mūsu efektīvais datu kopu klāsts. Tā kā mums ir dati blakus šūnās vai diapazonā (no B2 līdz B9), mēs varam nodot visu diapazonu funkcijā šādi:
- Visbeidzot, mums jāievada noslēdzošās iekavas un jānospiež Ievadiet taustiņu uz tastatūras, lai iegūtu efektīvo rezultātu vai vidējo aritmētisko dotajai datu kopai. Zemāk esošajā attēlā parādīts cenu vidējais aritmētiskais rādītājs šūnās no B2 līdz B9:
Iepriekš redzamajā attēlā mēs izmantojām blakus esošās šūnas un vienā reizē piegādājām visu datu kopu klāstu. Tomēr mēs varam arī aprēķināt vidējo vērtību mūsu Excel lapā esošajām šūnām, piegādājot šūnas atsevišķi.
Pieņemsim, ka mums ir jāaprēķina tikai trīs augļu cenu vidējā aritmētiskā vērtība: Ābols, mango un vīnogas. Tādā gadījumā mēs varam nodot katru šūnu atsevišķi funkcijā šādi:
= VIDĒJAIS(B2,B3,B6)
Šeit B2, B3 un B6 ir atsevišķas šūnas ar Apple, Mango un Grapes cenām.
Lai gan funkciju AVERAGE var izmantot ar tukšām šūnām, tekstiem, nullēm, loģiskajām vērtībām un pat jauktiem argumentiem, tā aprēķina laikā ignorē tukšās šūnas, tekstu un loģiskās vērtības. Tomēr nulles vērtības šūnas ir iekļautas funkcijā AVERAGE.
Vidējās vērtības aprēķināšana programmā Excel: AVERAGEA funkcija
Funkcija AVERAGEA ir nedaudz uzlabota funkcijas AVERAGE versija. Funkcija AVERAGEA arī palīdz mums aprēķināt vidējo aritmētisko noteiktām datu kopām un darbojas visās Excel versijās. Viena no funkcijas AVERAGEA priekšrocībām ir tā, ka tajā var iekļaut teksta vērtības un Būla datu tipus, aprēķinot vidējo programmā Excel. Teksta vērtības tiek novērtētas kā nulles, savukārt Būla vērtības TRUE un FALSE tiek novērtētas attiecīgi kā 1 un 0.
Sintakse
Programmas Excel funkcijas AVERAGEA sintakse ir šāda:
=VIDĒJAIS(vērtība1, [vērtība2], ...)
Tāpat kā funkcijai AVERAGE, ir nepieciešams pirmais arguments funkcijā AVERAGEA. Tomēr pēc vajadzības varam izmantot vairākus argumentus.
Funkciju AVERAGE un AVERAGEA sintakse ir gandrīz līdzīga; tomēr to darba koncepcija ir nedaudz atšķirīga. Funkcija AVERAGE var aprēķināt tikai vidējo aritmētisko skaitliskām vērtībām, savukārt funkcija AVERAGEA var aprēķināt to pašu jebkuram datu veidam, tostarp tekstam un Būla vērtībām.
Darbības, lai aprēķinātu vidējo, izmantojot funkciju AVERAGEA
atšķirība starp tīģeri un lauvu
Atkārtosim iepriekšējās datu kopu piemēru piemērus, kur augļu nosaukumi ir A slejā (A2:A9) un to cenas B ailē (B2:B9). Tomēr šoreiz mums nav vīnogu cenas (atbilstošajā šūnā B6 reģistrēta kā “Nav pieejams”), kā parādīts tālāk:
Mēs varam izpildīt tālāk norādītās darbības un aprēķināt vidējo vērtību, izmantojot funkciju AVERAGEA:
- Tāpat kā iepriekšējā metode, mēs vispirms atlasām iegūto šūnu B10, lai reģistrētu vidējo. Pēc tam mēs ierakstām vienādības zīmi (=), ievadām vai atlasām mūsu funkciju AVERAGEA un nododam visu datu diapazonu funkcijai, kas atrodas iekavās.
- Visbeidzot, mums ir jānospiež Ievadiet galvenais, lai iegūtu efektīvu rezultātu (vai vidējo).
Iepriekš redzamais attēls atgriež vidējo izvadi 57,125 ar funkciju AVERAGEA.
Ja šajā piemērā izmantojam funkciju AVERAGE un aprēķinām vidējo aritmētisko, mēs redzam milzīgu atšķirību starp abām funkcijām iegūtajiem rezultātiem. Vidējā izvade 65,285 parādās ar funkciju AVERAGE, kā parādīts zemāk:
Vidējā aritmētiskā atšķirība šajā piemērā ir jēga, jo abām šīm funkcijām dalījumu skaita skaitlis ir atšķirīgs. Skaitļu skaits funkcijai AVERAGEA ir 8, jo tā arī novērtē TEKSTU kā derīgus datus. Bet funkcijai AVERAGE dalījumu skaits ir 7, jo tās aprēķinā nav iekļauti TEKSTA dati.
TEKSTA datu vietā, ja datu kopā ir nulle (0), abām funkcijām (VIDĒJAIS un VIDĒJAIS) tiks parādīts viens un tas pats rezultāts (vai vidējais aritmētiskais), jo abās aprēķinos ir iekļauta nulle kā derīgi dati.
Vidējās vērtības aprēķināšana programmā Excel: funkcija AVERAGEIF
Funkcija AVERAGEIF ir attiecīgā funkcijas AVERAGE funkcija un palīdz mums aprēķināt vidējo aritmētisko ar noteiktu nosacījumu vai kritērijiem. Izmantojot funkciju AVERAGEIF, mēs varam aprēķināt vidējo vērtību tikai noteiktiem skaitļiem, kas atbilst norādītajiem kritērijiem. Programma Excel vispirms novērtē konkrētos kritērijus, pēc šiem kritērijiem atrod efektīvās šūnas un pēc tam attiecīgi aprēķina vidējo vērtību tikai atrastajām šūnām.
Sintakse
Excel funkcijas AVERAGEIF sintakse ir šāda:
=VIDĒJAISAIF(diapazons, kritēriji, [vidējais_diapazons])
Šeit diapazons un kritēriji ir obligātie argumenti, savukārt vidējais_diapazons nav obligāts.
- Diapazons satur šūnas, kas jāpārbauda atbilstoši norādītajiem kritērijiem.
- Kritērijs attiecas uz argumentu, kurā mēs varam norādīt vēlamo nosacījumu, kas jāievēro. Tas var ietvert tekstu, skaitliskās vērtības, šūnu atsauci, diapazonu un loģisko izteiksmi.
- Vidējais_diapazons ir arguments, kurā mēs pieminam šūnas (vai diapazonu), kurām vēlamies aprēķināt vidējo. Ja šis arguments nav norādīts, programma Excel automātiski izmanto diapazonu.
Darbības, lai aprēķinātu vidējo, izmantojot funkciju AVERAGEIF
Pieņemsim, ka tajā pašā piemērā daži augļi ir uzskaitīti vairākas reizes ar atšķirīgām cenām. Šoreiz mums ir jāaprēķina tikai vidējais lielums jebkuram konkrētam atkārtotam auglim (t.i., ābolam) šūnā B11. Tātad, mēs izmantojam funkciju AVERAGEIF un piemērojam nosacījumu, aprēķinot vidējo. Lai aprēķinātu Apple vidējo cenu, mums ir jāveic šādas darbības:
- Pirmkārt, mēs atlasām iegūto šūnu (B11) un ievietojam AVERAGEIF formulu šādā veidā:
= VIDĒJSIF(A2:B9, 'Apple', B2:B9)
Šeit A2:B9 apzīmē efektīvo diapazonu, Apple apzīmē mūsu vēlamos kritērijus/nosacījumus un B2:B9 apzīmē vidējo diapazonu.
Šeit mēs manuāli norādījām izvēles vidējo diapazonu, lai iekļautu tikai šūnas ar cenām. Mūsu piemērā, ja mēs neizturēsim vidējo_diapazonu, funkcija izmantos primāro diapazonu (A2:B9) ar teksta datiem un izraisīs #DIV! kļūda.
Piezīme. Ir svarīgi atzīmēt, ka funkcija AVERAGEIF ignorē tukšās šūnas savā argumentā vidējais_diapazons. Turklāt, ja kāda kritērijos norādītā šūna ir tukša, funkcija to apstrādā kā nulles vērtības šūnu.
Vidējās vērtības aprēķināšana programmā Excel: AVERAGEIFS funkcija
Funkcija AVERAGEIFS ir AVERAGEIF funkcijas paplašināta vai uzlabota versija. Funkcija AVERAGEIF ļauj vienlaikus piemērot tikai vienu nosacījumu. Tomēr, izmantojot funkciju AVERAGEIFS, mēs varam piemērot vairākus kritērijus, kas tiek pārbaudīti vienlaicīgi. Turklāt funkcija AVERAGEIFS ļauj mums izmantot šūnas ar Būla vērtībām TRUE un FALSE, palīdzot mums viegli pārbaudīt vairākus kritērijus. Tas nav iespējams arī ar funkciju AVERAGEIF.
pastnieks
Sintakse
Excel funkcijas AVERAGEIFS sintakse ir šāda:
=VIDĒJAISAIFS(vidējais_diapazons, kritēriji1_diapazons, kritēriji1, [kritēriju2_diapazons2, kritēriji2], ...)
Šeit nepieciešamie argumenti ir vidējais_diapazons, kritēriji1_diapazons1 un kritēriji1. Tomēr mēs varam sniegt izvēles argumentus, pamatojoties uz mūsu efektīvajām datu kopām. Turklāt kritērijs1 attiecas uz nosacījumu, kas jāpārbauda diapazonā1, kritērijs2 attiecas uz nosacījumu, kas jāpārbauda diapazonā2 un citi.
Darbības, lai aprēķinātu vidējo, izmantojot funkciju AVERAGEIFS
Apskatīsim tos pašus datu piemēru piemērus, kurus izmantojām iepriekšējā metodē. Iepriekšējā metodē mēs izmantojām funkciju AVERAGEIF un aprēķinājām atkārtotu augļu (Ābolu) cenu vidējo. Tādā gadījumā mums bija tikai viens nosacījums.
Tagad pieņemsim, ka mums ir jāaprēķina Apple cenu vidējā vērtība, bet jāiekļauj tikai tās šūnas, kurās cenas pārsniedz 40.
Šajā gadījumā mums ir divi dažādi kritēriji, tāpēc funkciju AVERAGEIF nevar izmantot.
1. kritērijs: Diapazonā A2:A9 sākotnējais kritērijs ir pārbaudīt, vai “Apple” atbilst trīs šūnām (vai rindām).
2. kritērijs: Diapazonā B2:B9 citi kritēriji ir pārbaudīt cenas virs 40 (>40), ko apmierina divas no trim šūnām (vai rindā) ar augļu ābolu.
Tādējādi, ja mēs matemātiski aprēķinām vidējo, mēs izmantojam šādu formulu:
izpildīt skripta čaulu
(95+68)/2
Tas atgriež vidējo vērtību 81.5. Pārbaudīsim, vai mēs iegūstam tādus pašus rezultātus, izmantojot Excel funkciju AVERAGEIFS.
Kad mums ir jāpārbauda nosacījumi programmā Excel, mēs parasti izmantojam loģiskā operatora VAI palīdzību. Operators VAI atgriež TRUE un FALSE atkarībā no tā, vai dotais nosacījums atbilst. Piemēram, pieņemsim, ka mums ir jāpārbauda, vai šūnā A2 ir Apple. Šajā gadījumā mēs varam izmantot operatoru VAI šādi:
=VAI(A2='Apple')
Mēs ievietojam iepriekš minēto nosacījumu mūsu parauga lapā, lai pārbaudītu vai atrastu šūnas ar augli Apple. Tātad, mēs izveidojam jaunu kolonnu (C) un ierakstām konkrēto nosacījumu šūnā C2. Pēc tam mēs nokopējam to pašu nosacījumu atlikušajās C kolonnas šūnās. Tas izskatās šādi:
Iepriekš redzamajā attēlā redzams, ka operators VAI atgriež TRUE šūnām, kas atbilst auglim Apple, savukārt citiem operators atgriež FALSE. Pamatojoties uz šo rezultātu, mēs tagad lietojam funkciju AVERAGEIFS un ievietojam vairākus nosacījumus TRUE vērtību šūnām šādā veidā:
- Tāpat kā citas metodes, mēs atkal vispirms atlasām iegūto šūnu (t.i., B12).
- Tālāk mēs ievadām AVERAGEIFS formulu šādā veidā:
=VIDĒJIE IFS(B2:B9,C2:C9,'TRUE',B2:B9,'>40')
Izmantojot iepriekš minēto AVERAGEIFS formulu, mēs iekļāvām tikai augļus 'Ābols' un cenas virs 40. - Visbeidzot, mums ir jānospiež Ievadiet taustiņu, lai iegūtu formulas rezultātu. Mūsu datu kopai formula atgrieza vidējo aritmētisko vērtību 81,5, tāds pats kā mūsu matemātiski aprēķinātais rezultāts.
Īpaši gadījumi
Lai gan, aprēķinot vidējo aritmētisko programmā Excel, mums var būt dažādi scenāriji, šeit ir apskatīti divi visizplatītākie. Šādos gadījumos mums jābūt piesardzīgiem, aprēķinot vidējo aritmētisko.
Aprēķinot vidējo, ieskaitot nulles
Ja mums ir jāaprēķina vidējais ar nullēm, mēs parasti varam izmantot funkciju AVERAGE un nodrošināt efektīvās šūnas, tostarp nulles vērtības šūnas. Funkcija AVERAGE savā aprēķinā iekļauj nulles kā derīgus skaitļus.
Šajā datu kopas paraugā mēs izmantojam funkciju AVERAGE, lai aprēķinātu vidējo divu dažādu skolēnu atzīmēm:
Iepriekš redzamajā attēlā parādītas abu studentu atšķirīgās vidējās vērtības. Ja skatāmies uz atzīmēm, tad atzīmju summa abiem skolēniem ir vienāda. Otrajam studentam vienā priekšmetā ir nulle atzīmju (nulles vērtības šūna), savukārt pirmais students nav klāt (teksta vērtības šūna) vienā priekšmetā.
Tā kā funkcija AVERAGE ignorē teksta vērtības, bet ietver nulles vērtības, dalījumu skaits abiem skolēniem atšķiras. Pirmajam studentam dalījumu skaits ir 4, savukārt citam studentam tas ir 5. Tādējādi ir atšķirība starp vidējo rezultātu. Tomēr, ja nepieciešams, mēs varam arī skaidri izslēgt nulles vērtības.
Aprēķinot vidējo, izņemot nulles
Kad mums ir jāaprēķina vidējais lielums, neskaitot nulles, mums ir jāizmanto Excel funkcijas AVERAGEIF priekšrocības. Šajā funkcijā mēs varam piemērot konkrēto nosacījumu, norādot, ka programmā Excel aprēķina laikā neiekļauj nulles vērtības šūnas.
Tālāk norādīto AVERAGEIF formulu var izmantot, lai izslēgtu nulles vērtības, aprēķinot vidējo programmā Excel:
=VIDĒJSIF(šūna vai diapazons,'>0')
Ja mēs ievietojam iepriekš minēto formulu mūsu izlases datiem, abiem studentiem parādās vienādi vidējās vērtības rezultāti.
Iepriekš minētā AVERAGEIF formula ignorē teksta un nulles vērtības šūnas. Tātad dalījumu skaits ir vienāds (t.i., 4), tāpat arī abu studentu atzīmju vidējais aritmētiskais.
Svarīgi punkti, kas jāatceras
- Ir svarīgi ņemt vērā, ka jebkura funkcija programmā Excel, tostarp AVERAGE, AVERAGEA, AVERAGEIF un AVERAGEIFS, var pieņemt ne vairāk kā 255 argumentus.
- Nav ieteicams manuāli rakstīt funkcijas nosaukumu Excel šūnā. Izvēloties vēlamo funkciju no ieteikumu saraksta, labāk ir izvairīties no nepareizi uzrakstītiem funkciju nosaukumiem un novērst kļūdas rezultātos.