Exceli valemid ei tööta: võimalikud põhjused ja kuidas seda parandada!

Kui töötate Excelis valemitega, tekib varem või hiljem probleem, kus Exceli valemid ei tööta üldse (või annavad vale tulemuse).

Kuigi see oleks olnud suurepärane, kui valemite talitlushäireid oleks olnud vaid mõned võimalikud põhjused. Kahjuks on liiga palju asju, mis võivad valesti minna (ja sageli ka).

Aga kuna me elame maailmas, mis järgib Pareto põhimõtet, siis kui te otsite mõningaid tavalisi probleeme, lahendab see tõenäoliselt 80% (või võib -olla isegi 90% või 95% probleemidest, kus valemid Excelis ei tööta).

Ja selles artiklis toon välja need levinumad probleemid, mis on tõenäoliselt teie põhjuseks Exceli valemid ei tööta.

Nii et alustame!

Funktsiooni vale süntaks

Alustuseks toon välja ilmselge.

Igal Exceli funktsioonil on spetsiifiline süntaks - näiteks argumentide arv, mida see võib vastu võtta, või argumentide tüüp, mida ta saab vastu võtta.

Paljudel juhtudel võib põhjus, miks teie Exceli valemid ei tööta või annavad vale tulemuse, vale argument (või puuduvad argumendid).

Näiteks funktsioon VLOOKUP võtab kolm kohustuslikku argumenti ja ühe valikulise argumendi.

Kui esitate vale argumendi või ei määra valikulist argumenti (kus see on valemi toimimiseks vajalik), annab see teile vale tulemuse.

Oletame näiteks, et teil on allpool näidatud andmekogum, kus peate teadma eksami 2 (lahtris F2) punkti Mark tulemust.

Kui kasutan allolevat valemit, saan vale tulemuse, kuna kasutan kolmandas argumendis valet väärtust (üks, mis küsib veeruindeksi numbrit).

= VLOOKUP (A2, A2: C6,2, FALSE)

Sel juhul arvutab valem (kuna tagastab väärtuse), kuid tulemus on vale (eksami 2 skoori asemel annab see eksamil 1).

Teine näide, kus peate argumentide suhtes olema ettevaatlik, on VLOOKUPi kasutamine ligikaudse vastega.

Kuna peate valikulise argumendi abil määrama, kuhu soovite, et VLOOKUP teeks täpse või ligikaudse vaste, võib selle määramata jätmine (või vale argumendi kasutamine) probleeme tekitada.

Allpool on näide, kus mul on mõne õpilase hindeandmed ja soovin paremal tabelis olevate õpilaste jaoks eksamil 1 hindeid välja võtta.

Kui kasutan allolevat VLOOKUP valemit, annab see mõne nime puhul vea.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

See juhtub, kuna ma pole viimast argumenti täpsustanud (seda kasutatakse täpse või ligikaudse vaste määramiseks). Kui te viimast argumenti ei määra, teeb see vaikimisi automaatselt ligikaudse vaste.

Kuna meil oli sel juhul vaja teha täpne vaste, tagastab valem mõne nime puhul vea.

Kuigi olen võtnud eeskuju funktsioonist VLOOKUP, saab antud juhul seda rakendada paljude Exceli valemite puhul, millel on ka valikulised argumendid.

Loe ka: Tuvastage vead Exceli valemi silumise abil

Täiendavad tühikud, mis põhjustavad ootamatuid tulemusi

Juhtivaid ja lõplikke tühikuid on raske teada saada ja need võivad põhjustada probleeme, kui kasutate lahtrit, mille valemid on olemas.

Näiteks kui proovin allolevas näites Mark'i skoori toomiseks kasutada VLOOKUP -i, annab see mulle vea #N/A (viga pole saadaval).

Kuigi ma näen, et valem on õige ja nimi „Mark” on selgelt olemas, on see nimekiri olemas, kuid ma ei näe seda, et nimega lahtris (lahtris D2) on tühik.

Excel ei pea nende kahe lahtri sisu samaks ja seetõttu peab seda VLOOKUP abil väärtuse toomisel (või võib see olla mõni muu otsinguvalem) mittevastavaks.

Selle probleemi lahendamiseks peate need täiendavad tühimärgid eemaldama.

Seda saate teha, kasutades mõnda järgmistest meetoditest.

1. Puhastage lahter ja eemaldage kõik esi-/lõppruumid enne selle kasutamist valemites
2. Kasutage valemi funktsiooni TRIM, et veenduda, et kõiki esi-/lõpp-/topeltruume eiratakse.

Ülaltoodud näites saate selle toimimise tagamiseks kasutada allolevat valemit.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Kuigi olen võtnud VLOOKUP näite, on see ka tavaline probleem TEXT -funktsioonidega töötamisel.

Näiteks kui kasutan funktsiooni LEN, et loendada lahtris olevate tähemärkide koguarvu, kui ees või lõpus on tühikuid, loetakse need ka kokku ja need annavad vale tulemuse.

Kaasa võtta / Kuidas parandada: Kui võimalik, puhastage oma andmed, eemaldades enne nende kasutamist valemites kõik esi-/lõpp- ja topeltvahed. Kui te ei saa algandmeid muuta, kasutage selle eest hoolitsemiseks valemites funktsiooni TRIM.

Käsitsi arvutamise kasutamine automaatse asemel

See üks seade võib teid hulluks ajada (kui te ei tea, et see põhjustab kõiki probleeme).

Excelil on kaks arvutusrežiimi - Automaatne ja Käsitsi.

Vaikimisi on automaatrežiim lubatud, mis tähendab, et kui kasutan valemit või muudan olemasolevaid valemeid, teeb see automaatselt (ja koheselt) arvutuse ja annab mulle tulemuse.

See on seade, millega me kõik oleme harjunud.

Automaatseade korral arvutab Excel automaatselt kõik töölehe muudatused (näiteks uue valemi sisestamine isegi mõne lahtri teksti juurde) (jah, kõike).

Kuid mõnel juhul lubavad inimesed käsitsi arvutamise seadeid.

Seda tehakse enamasti siis, kui teil on raske Exceli fail, kus on palju andmeid ja valemeid. Sellistel juhtudel ei pruugi te soovida, et Excel väikeste muudatuste tegemisel kõik ümber arvutaks (kuna selle ümberarvutuse lõpuleviimiseks võib kuluda mõni sekund või isegi minut).

Kui lubate käsitsi arvutamise, ei arvuta Excel, kui te seda ei sunni.

Ja see võib panna teid mõtlema, et teie valem ei arvutata.

Sel juhul piisab, kui seada arvutused tagasi automaatseks või sundida uuesti arvutama, vajutades klahvi F9.

Allpool on toodud sammud arvutuse muutmiseks käsitsi automaatseks:

1. Klõpsake vahekaarti Valem
2. Klõpsake Arvutusvalikud
3. Valige Automaatne

Tähtis: kui muudate arvutust käsitsi automaatseks, on hea mõte luua oma töövihikust varukoopia (juhuks, kui see paneb teie töövihiku hanguma või muudab Exceli krahhi)

Kaasa võtta / Kuidas parandada: Kui märkate, et teie valemid ei anna oodatud tulemust, proovige suvalises lahtris midagi lihtsat (nt lisage olemasolevale valemile 1. Kui olete tuvastanud probleemi, mille puhul arvutusrežiimi tuleb muuta, tehke jõuarvutus kasutades F9.

#REF -i viivate ridade/veergude/lahtrite kustutamine! Viga

Üks asi, mis võib teie Excelis olemasolevatele valemitele laastavalt mõjuda, on see, kui kustutate arvutustes kasutatud rea/veeru.

Kui see juhtub, kohandab Excel mõnikord viidet ise ja veendub, et valemid töötavad hästi.

Ja vahel… see ei saa.

Õnneks on üks selge märge, et kui valemid lahtrite/ridade/veergude kustutamisel katki lähevad, on #REF! viga rakkudes. See on viiteviga, mis ütleb teile, et valemis olevate viidetega on probleeme.

Näitan näite abil, mida ma mõtlen.

Allpool olen kasutanud lahtrite A2: A6 lisamiseks SUM valemit.

Kui ma kustutan mõne neist lahtritest/ridadest, tagastab SUM valem #REF! viga. See juhtub seetõttu, et kui ma rea ​​kustutasin, ei tea valem, millele nüüd viidata.

Näete, et valemi kolmas argument on saanud #REF! (mis varem viitas lahtrile, mille me kustutasime).

Kaasa võtta / Kuidas parandada: Enne valemites kasutatavate andmete kustutamist veenduge, et pärast kustutamist pole vigu. Samuti on soovitatav luua oma tööst regulaarselt varukoopia, et veenduda, et teil on alati midagi, millele tagasi tulla.

Sulgude vale paigutus (BODMAS)

Kuna teie valemid hakkavad muutuma suuremaks ja keerukamaks, on hea mõte kasutada sulgudes selgust, milline osa kokku kuulub.

Mõnel juhul võib sulg olla vales kohas, mis võib anda teile vale tulemuse või vea.

Ja mõnel juhul on soovitatav kasutada sulgusid, et veenduda, et valem mõistab kõigepealt seda, mida tuleb rühmitada ja arvutada.

Oletame näiteks, et teil on järgmine valem:

=5+10*50

Ülaltoodud valemis on tulemus 505, kuna Excel teeb kõigepealt korrutamise ja seejärel liitmise (kuna operaatorite osas on eelisjärjekord).

Kui soovite, et kõigepealt teeks liitmise ja seejärel korrutamise, peate kasutama järgmist valemit:

=(5+10)*50

Mõnel juhul võib paremusjärjestus teie jaoks sobida, kuid segaduste vältimiseks on siiski soovitatav kasutada sulgusid.

Lisaks, kui olete huvitatud, on allpool toodud valemites sageli kasutatavate erinevate operaatorite järjekord:

Operaator	Kirjeldus	Eelisjärjekord
: (koolon)	Vahemik	1
(üks tühik)	Ristmik	2
, (koma)	liit	3
-	Eitus (nagu -1)	4
%	Protsent	5
^	Eksponentimine	6
* ja /	Korrutamine ja jagamine	7
+ ja -	Liitmine ja lahutamine	8
&	Ühendamine	9
= =	Võrdlus	10

Kaasa võtta / Kuidas parandada: Segaduste vältimiseks kasutage alati sulgusid, isegi kui teate paremusjärjestust ja kasutate seda õigesti. Sulgude olemasolu muudab valemite auditeerimise lihtsamaks.

Absoluutsete/suhteliste lahtriviidete vale kasutamine

Excelis valemite kopeerimisel ja kleepimisel kohandab see automaatselt viiteid. Mõnikord on see just see, mida soovite (enamasti siis, kui kleepite valemid veergu allapoole) ja mõnikord ei soovi, et see juhtuks.

Absoluutne viide on siis, kui parandate lahtri viite (või vahemiku viite) nii, et see ei muutuks valemite kopeerimisel ja kleepimisel, ning suhteline viide muutub.

Siit saate lugeda absoluutsete, suhteliste ja segatud viidete kohta.

Kui unustate viite absoluutseks muuta (või vastupidi), võite saada vale tulemuse. See juhtub minuga üsna sageli, kui kasutan otsinguvalemeid.

Näitan teile näidet.

Allpool on mul andmestik, kust soovin tuua eksamil 1 skoori veerus E olevate nimede kohta (lihtne VLOOKUPi kasutamise juhtum)

Allpool on valem, mida kasutan lahtris F2 ja seejärel kopeerin kõikidesse lahtritesse selle all:

= VLOOKUP (E2, A2: B6,2,0)

Nagu näete, annab see valem mõnel juhul vea.

See juhtub seetõttu, et ma pole tabelimassiivi argumenti lukustanud - see on A2: B6 lahtris F2, kuigi see oleks pidanud olema $ A $ 2: $ B $ 6

Kui need dollarimärgid on lahtri viites reanumbri ja veeru tähestiku ees, sunnin Excelit neid lahtriviiteid fikseerituna hoidma. Seega, isegi kui ma selle valemi alla kopeerin, viitab tabelimassiiv jätkuvalt viitele A2: B6

Pro näpunäide: Suhtelise viite teisendamiseks absoluutseks, valige lahtrist see lahtri viide ja vajutage klahvi F4. Märkasite, et see muutub dollarimärkide lisamisega. Võite jätkata F4 vajutamist, kuni saate soovitud viite.

Vale viide lehe / töövihiku nimedele

Kui viidate valemis teistele lehtedele või töövihikutele, peate järgima kindlat vormingut. Ja kui vorming on vale, saate vea.

Näiteks kui ma tahan viidata lehe 2 lahtrile A1, oleks see viide = Leht2! A1 (kus lehe nime järel on hüüumärk)

Ja kui lehe nimes on mitu sõna (oletame, et see on näiteandmed), oleks viide = 'Näiteandmed'! A1 (kus nimi on ümbritsetud jutumärkidega, millele järgneb hüüumärk).

Kui viitate välisele töövihikule (oletame, et viitate lahtrile A1 töövihiku „Näidisleht” nimega „Näidisvihik”), on viide järgmine:

= '[Näide töövihikust.xlsx] Näidisleht'! $ A $ 1

Ja kui töövihiku sulgete, muutub viide, mis hõlmab kogu töövihiku teed (nagu allpool näidatud):

= 'C: \ Users \ sumit \ Desktop \ [Näidisvihik.xlsx] Näidisleht'! $ A $ 1

Kui muudate lõpuks töövihiku või töölehe nime, millele valem viitab, annab see teile #REF! viga.

Loe ka: Kuidas leida väliseid linke ja viiteid Excelist

Ringkirjandus

Ümmargune viide on see, kui viitate (otseselt või kaudselt) samale lahtrile, kus valemit arvutatakse.

Allpool on lihtne näide, kus kasutan lahtris A4 SUM valemit, kasutades seda arvutamisel ise.

= SUM (A1: A4)

Kuigi Excel näitab teile viipa, mis annab teile ümmarguse viite kohta teada, ei tee see seda igal juhul. Ja see võib anda teile vale tulemuse (ilma hoiatuseta).

Kui kahtlustate mängus ümmargust viidet, saate kontrollida, millistel lahtritel see on.

Selleks klõpsake vahekaarti Valem ja rühmas „Valemi auditeerimine” klõpsake rippmenüüd Vigade kontrollimine (väike allapoole suunatud nool).

Hõljutage kursorit ümmarguse viite valiku kohal ja see näitab teile lahtrit, millel on ümmarguse viite probleem.

Tekstina vormindatud lahtrid

Kui leiate olukorra, kus niipea kui sisestate valemi sisestusnuppu, näete väärtuse asemel valemit, on selge, et lahter vormistatakse tekstiks.

Kui lahter vormindatakse tekstiks, käsitleb see valemit tekstistringina ja kuvab selle sellisena, nagu see on.

See ei sunni teda arvutama ja tulemust andma.

Ja sellel on lihtne lahendus.

1. Muutke vorminguks „Tekst” vorminguks „Üldine” (see asub grupi Numbrid vahekaardil Avaleht)
2. Minge lahtrisse, millel on valem, minge redigeerimisrežiimi (kasutage klahvi F2 või topeltklõpsake lahtrit) ja vajutage sisestusklahvi

Kui ülaltoodud sammud probleemi ei lahenda, tuleb veel kontrollida, kas lahtril on alguses apostroof. Paljud inimesed lisavad valemite ja numbrite tekstiks teisendamiseks apostroofi.

Kui on olemas apostroof, saate selle lihtsalt eemaldada.

Tekst muutub automaatselt kuupäevadeks

Excelil on see halb harjumus teisendada kuupäevana näiv tegelikuks kuupäevaks. Näiteks kui sisestate 1/1, teisendab Excel selle jooksva aasta 1. jaanuariks.

Mõnel juhul võib see olla täpselt see, mida soovite, ja mõnel juhul võib see teie vastu töötada.

Ja kuna Excel salvestab kuupäeva ja kellaaja väärtused arvudena, teisendab see kohe, kui sisestate 1/1, selle numbriks, mis tähistab jooksva aasta 1. jaanuari. Minu puhul teisendab see selle tegemisel numbriks 43831 (01.01.2020).

See võib teie valemitega segi ajada, kui kasutate neid lahtreid argumendina valemis.

Kuidas seda parandada?

Jällegi, me ei taha, et Excel valiks meie jaoks automaatselt vormingu, seega peame vormingu ise selgelt määratlema.

Allpool on toodud sammud vormingu muutmiseks tekstiks, nii et see ei muuda teksti automaatselt kuupäevadeks.

1. Valige lahtrid/vahemik, kus soovite vormingut muuta
2. Klõpsake vahekaarti Avaleht
3. Klõpsake rühmas Number rippmenüüd Vorming
4. Klõpsake nuppu Tekst

Nüüd, kui sisestate valitud lahtritesse midagi, loetakse seda tekstiks ja seda ei muudeta automaatselt.

Märkus. Ülaltoodud toimingud toimivad ainult pärast vormingu muutmist sisestatud andmete puhul. See ei muuda ühtegi teksti, mis on enne selle vorminduse muutmist kuupäevaks teisendatud.

Teine näide, kus see võib olla väga masendav, on see, kui sisestate teksti/numbri, mille ees on nullid. Excel eemaldab need eesmised nullid automaatselt, kuna peab neid kasutuks. Näiteks kui sisestate lahtrisse 0001, muudab Excel selle väärtuseks 1. Kui soovite need esimesed nullid säilitada, tehke ülaltoodud samme.

Varjatud read/veerud võivad anda ootamatuid tulemusi

See ei ole vale valem, mis annab teile vale tulemuse, vaid vale valemi kasutamine.

Oletame näiteks, et teil on allpool näidatud andmekogum ja ma tahan saada veeru C kõigi nähtavate lahtrite summa.

Lahtris C12 olen kasutanud funktsiooni SUM, et saada kõigi nende kirjete müügi koguväärtus.

Siiamaani on kõik korras!

Nüüd rakendan üksuste veerule filtri, et kuvada ainult printerimüügi kirjed.

Ja siin on probleem - lahtris C12 olev valem näitab endiselt sama tulemust - st kõigi kirjete summat.

Nagu ma ütlesin, ei anna valem vale tulemust. Tegelikult töötab SUM -funktsioon suurepäraselt.

Küsimus on selles, et oleme siin kasutanud vale valemit.

Funktsioon SUM ei arvesta filtreeritud andmeid ja annab teile kõigi lahtrite (peidetud või nähtavad) tulemuse. Kui soovite saada ainult nähtavate lahtrite summat/arvu/keskmist, kasutage funktsioone SUBTOTAL või AGGREGATE.

Võtme kaasavõtmine - Mõista Exceli funktsiooni õiget kasutamist ja piiranguid.

Need on mõned levinumad põhjused, mida olen näinud, kus teie Exceli valemid ei pruugi töötada või annavad ootamatuid või valesid tulemusi. Olen kindel, et Exceli valemi mittetöötamiseks või värskendamiseks on palju rohkem põhjuseid.

Kui teate mõnda muud põhjust (võib -olla midagi, mis teid sageli ärritab), andke mulle sellest kommentaaride jaotises teada.

Loodan, et leidsite selle õpetuse kasulikuks!