Excel -kaavat eivät toimi: mahdolliset syyt ja kuinka korjata se!

Jos käytät kaavoja Excelissä, kohtaat ennemmin tai myöhemmin ongelman, jossa Excel -kaavat eivät toimi ollenkaan (tai antavat väärän tuloksen).

Vaikka olisi ollut hienoa, jos kaavojen toimintahäiriöihin olisi ollut vain muutama mahdollinen syy. Valitettavasti on liian monia asioita, jotka voivat mennä pieleen (ja usein menee).

Mutta koska elämme maailmassa, joka noudattaa Pareto -periaatetta, jos tarkistat joitakin yleisiä ongelmia, se todennäköisesti ratkaisee 80% (tai ehkä jopa 90% tai 95% ongelmista, joissa kaavat eivät toimi Excelissä).

Ja tässä artikkelissa korostan niitä yleisiä ongelmia, jotka todennäköisesti aiheuttavat sinun Excel -kaavat eivät toimi.

Aloitetaan siis!

Toiminnon syntaksi on virheellinen

Aloitan huomauttamalla ilmeisen.

Jokaisella Excelin funktiolla on tietty syntaksi - kuten argumenttien määrä, joita se voi ottaa, tai argumenttien tyyppi, jotka se voi hyväksyä.

Monissa tapauksissa Excel -kaavasi eivät toimi tai antavat väärän tuloksen, mikä voi olla väärä argumentti (tai puuttuvat argumentit).

Esimerkiksi VLOOKUP -funktio sisältää kolme pakollista argumenttia ja yhden valinnaisen argumentin.

Jos annat väärän argumentin tai et määritä valinnaista argumenttia (missä sitä tarvitaan kaavan toimimiseksi), se antaa sinulle väärän tuloksen.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko, jossa sinun on tiedettävä tentin 2 pistemäärä (solussa F2).

Jos käytän alla olevaa kaavaa, saan väärän tuloksen, koska käytän väärää arvoa kolmannessa argumentissa (joka pyytää sarakkeen indeksinumeroa).

= HAKU (A2, A2: C6,2, EPÄTOSI)

Tässä tapauksessa kaava laskee (koska se palauttaa arvon), mutta tulos on väärä (kokeen 2 pistemäärän sijasta se antaa pisteen kokeessa 1).

Toinen esimerkki, jossa sinun on oltava varovainen argumenttien suhteen, on käyttää VLOOKUPia likimääräisen vastaavuuden kanssa.

Koska sinun on käytettävä valinnaista argumenttia määrittämään, missä haluat VLOOKUPin vastaavan tarkasti tai likimääräisesti, tämän määrittämättä jättäminen (tai väärän argumentin käyttäminen) voi aiheuttaa ongelmia.

Alla on esimerkki, jossa minulla on joidenkin oppilaiden arvosanat ja haluan poimia pisteitä tentissä 1 oikealla olevasta taulukosta.

Kun käytän alla olevaa VLOOKUP -kaavaa, se antaa minulle virheen joillekin nimille.

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

Tämä tapahtuu, koska en ole määrittänyt viimeistä argumenttia (jota käytetään määrittämään, tehdäänkö tarkka tai likimääräinen vastaavuus). Jos et määritä viimeistä argumenttia, se tekee oletuksena automaattisesti likimääräisen osuman.

Koska meidän täytyi tehdä tarkka vastaavuus tässä tapauksessa, kaava palauttaa virheen joillekin nimille.

Vaikka olen ottanut esimerkin VLOOKUP -toiminnosta, tässä tapauksessa tätä voidaan soveltaa moniin Excel -kaavoihin, joissa on myös valinnaisia ​​argumentteja.

Lue myös: Virheiden tunnistaminen Excel -kaavan virheenkorjauksen avulla

Ylimääräiset tilat aiheuttavat odottamattomia tuloksia

Johtavia, perään jääviä välilyöntejä on vaikea löytää ja ne voivat aiheuttaa ongelmia, kun käytät solua, jossa on näitä kaavoja.

Esimerkiksi, jos alla olevassa esimerkissä yritän hakea Markin pisteet VLOOKUPilla, se antaa minulle #N/A -virheen (ei käytettävissä oleva virhe).

Vaikka näen, että kaava on oikea ja nimi "Merkki" on selvästi olemassa, luettelo on olemassa, mutta en voi nähdä, että solussa, jossa on nimi (solussa D2), on välilyönti.

Excel ei pidä näiden kahden solun sisältöä samanlaisena, ja siksi se pitää sitä epäsopivana haettaessa arvoa VLOOKUP -sovelluksella (tai se voi olla mikä tahansa muu hakukaava).

Voit korjata tämän ongelman poistamalla nämä ylimääräiset välilyönnit.

Voit tehdä tämän jollakin seuraavista tavoista:

1. Puhdista solu ja poista kaikki edessä/takana olevat tilat ennen kuin käytät sitä kaavoissa
2. Käytä TRIM -funktiota kaavan sisällä varmistaaksesi, että kaikki alku-, loppu- ja kaksoisvälit jätetään huomiotta.

Yllä olevassa esimerkissä voit käyttää alla olevaa kaavaa sen varmistamiseksi, että se toimii.

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

Vaikka olen ottanut VLOOKUP -esimerkin, tämä on myös yleinen ongelma, kun työskentelet TEXT -toimintojen kanssa.

Jos esimerkiksi lasken LEN -funktion avulla solun merkkien kokonaismäärän, jos edessä tai takana on välilyöntejä, myös nämä lasketaan ja antavat väärän tuloksen.

Ota pois / Kuinka korjata: Jos mahdollista, puhdista tietosi poistamalla kaikki edessä/takana olevat tai kaksoisvälit ennen kuin käytät niitä kaavoissa. Jos et voi muuttaa alkuperäisiä tietoja, käytä TRIM -toimintoa kaavoissa hoitaaksesi tämän.

Manuaalisen laskennan käyttäminen automaattisen sijasta

Tämä yksi asetus voi saada sinut hulluksi (jos et tiedä, mikä aiheuttaa kaikki ongelmat).

Excelissä on kaksi laskentatilaa - Automaattinen ja Manuaalinen.

Automaattinen tila on oletusarvoisesti käytössä, mikä tarkoittaa, että jos käytän kaavaa tai teen muutoksia olemassa oleviin kaavoihin, se tekee laskelman automaattisesti (ja heti) ja antaa minulle tuloksen.

Tämä on asetus, johon olemme tottuneet.

Automaattisessa asetuksessa Excel laskee kaiken automaattisesti uudelleen aina, kun teet muutoksia laskentataulukkoon (esimerkiksi kirjoitat uuden kaavan jopa johonkin solun tekstiin) (kyllä, kaikki).

Mutta joissakin tapauksissa ihmiset ottavat käyttöön manuaalisen laskenta -asetuksen.

Tämä tehdään enimmäkseen, kun sinulla on raskas Excel -tiedosto, jossa on paljon tietoja ja kaavoja. Tällaisissa tapauksissa et ehkä halua, että Excel laskee kaiken uudelleen, kun teet pieniä muutoksia (koska voi kestää muutamia sekunteja tai jopa minuutteja), ennen kuin tämä uudelleenlaskenta suoritetaan.

Jos otat manuaalisen laskennan käyttöön, Excel ei laske, ellet pakota sitä.

Ja tämä voi saada sinut ajattelemaan, että kaava ei laske.

Tässä tapauksessa sinun tarvitsee vain asettaa lasku takaisin automaattiseksi tai pakottaa uudelleenlaskenta painamalla F9 -näppäintä.

Alla on vaiheet, joilla laskenta muutetaan manuaalisesta automaattiseksi:

1. Napsauta Kaava -välilehteä
2. Napsauta Laskutusasetukset
3. Valitse Automaattinen

Tärkeää: Jos muutat laskutoimitusta manuaalisesta automaattiseksi, sinun on luotava varmuuskopio työkirjasta (siltä varalta, että työkirja roikkuu tai Excel kaatuu)

Ota pois / Kuinka korjata: Jos huomaat, että kaavat eivät anna odotettua tulosta, kokeile jotain yksinkertaista missä tahansa solussa (kuten lisäämällä 1 olemassa olevaan kaavaan. Kun olet tunnistanut ongelman sellaiseksi, jossa laskentatilaa on muutettava, tee voimalaskenta käyttämällä F9.

Poistetaan rivit/sarakkeet/solut, jotka johtavat kohtaan #REF! Virhe

Yksi niistä asioista, joilla voi olla tuhoisa vaikutus nykyisiin Excel -kaavoihisi, on se, kun poistat kaikki laskutoimituksissa käytetyt rivit/sarakkeet.

Jos näin tapahtuu, joskus Excel säätää viitettä itse ja varmistaa, että kaavat toimivat hyvin.

Ja joskus… se ei voi.

Onneksi #REF! virhe soluissa. Tämä on viittausvirhe, joka kertoo, että kaavan viittauksissa on jokin ongelma.

Näytän mitä tarkoitan esimerkin avulla.

Alla olen käyttänyt SUM -kaavaa solujen A2: A6 lisäämiseen.

Jos poistan jonkin näistä soluista/riveistä, SUM -kaava palauttaa #REF! virhe. Näin tapahtuu, koska kun poistin rivin, kaava ei tiedä mihin viitata nyt.

Näet, että kaavan kolmannesta argumentista on tullut #REF! (joka viittasi aiemmin poistamaamme soluun).

Ota pois / Kuinka korjata: Ennen kuin poistat kaavoissa käytettävät tiedot, varmista, ettei poiston jälkeen ole virheitä. On myös suositeltavaa luoda varmuuskopio työstäsi säännöllisesti varmistaaksesi, että sinulla on aina jotain, mihin palata.

Sulkeiden väärä sijoitus (BODMAS)

Kun kaavasi alkavat muuttua suuremmiksi ja monimutkaisemmiksi, on hyvä käyttää sulkeita, jotta voit olla täysin selvillä siitä, mikä osa kuuluu yhteen.

Joissakin tapauksissa sulkeet voivat olla väärässä paikassa, mikä voi antaa sinulle väärän tuloksen tai virheen.

Ja joissakin tapauksissa on suositeltavaa käyttää sulkeita varmistaaksesi, että kaava ymmärtää, mitä on ryhmitettävä ja laskettava ensin.

Oletetaan esimerkiksi, että sinulla on seuraava kaava:

=5+10*50

Yllä olevassa kaavassa tulos on 505, koska Excel tekee ensin kertolaskun ja sitten liitoksen (koska operaattoreilla on etusijajärjestys).

Jos haluat sen ensin lisäävän ja sitten kertovan, sinun on käytettävä alla olevaa kaavaa:

=(5+10)*50

Joissakin tapauksissa etusijajärjestys voi toimia sinulle, mutta on silti suositeltavaa käyttää sulkeita, jotta vältytään sekaannuksilta.

Lisäksi, jos olet kiinnostunut, alla on tärkeysjärjestys eri operaattoreille, joita käytetään usein kaavoissa:

Operaattori	Kuvaus	Ensisijaisuusjärjestys
: (kaksoispiste)	Alue	1
(yksi välilyönti)	Risteys	2
, (pilkku)	liitto	3
-	Kieltäminen (kuten -1)	4
%	Prosenttiosuus	5
^	Eksponointi	6
* ja /	Kertolasku ja jako	7
+ ja -	Lisäys ja vähennys	8
&	Yhdistäminen	9
= =	Vertailu	10

Ota pois / Kuinka korjata: Käytä aina sulkeita välttääksesi sekaannukset, vaikka tiedätkin järjestyksen ja käytät sitä oikein. Suluilla on helpompi tarkistaa kaavoja.

Absoluuttisten/suhteellisten soluviitteiden virheellinen käyttö

Kun kopioit ja liität kaavoja Excelissä, se säätää viittaukset automaattisesti. Joskus tämä on juuri sitä, mitä haluat (enimmäkseen, kun kopioit ja liität kaavoja sarakkeeseen), ja joskus et halua tämän tapahtuvan.

Absoluuttinen viite on, kun korjaat soluviittauksen (tai alueviittauksen) siten, että se ei muutu, kun kaavoja kopioidaan ja liitetään, ja suhteellinen viite muuttuu.

Voit lukea lisää absoluuttisista, suhteellisista ja sekavista viitteistä täältä.

Saatat saada virheellisen tuloksen, jos unohdat muuttaa viittauksen absoluuttiseksi (tai päinvastoin). Tämä tapahtuu minulle melko usein, kun käytän hakukaavoja.

Annan sinulle esimerkin.

Alla on tietojoukko, josta haluan noutaa sarakkeen E nimien kokeen 1 pisteet (yksinkertainen VLOOKUP -käyttötapaus)

Alla on kaava, jota käytän solussa F2 ja kopioin sitten kaikkiin sen alla oleviin soluihin:

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

Kuten näet, tämä kaava antaa joissakin tapauksissa virheen.

Tämä tapahtuu, koska en ole lukinnut taulukkotaulukon argumenttia - se on A2: B6 solussa F2, kun sen olisi pitänyt olla $ A $ 2: $ B $ 6

Kun minulla on nämä dollarimerkit soluviittauksessa ennen rivinumeroa ja sarakkeen aakkosia, pakotan Excelin pitämään nämä soluviittaukset kiinteinä. Joten vaikka kopioin tämän kaavan alas, taulukkotaulukko viittaa edelleen kohtaan A2: B6

Pro -vinkki: Jos haluat muuntaa suhteellisen viittauksen absoluuttiseksi, valitse kyseinen soluviittaus solussa ja paina F4 -näppäintä. Huomaat, että se muuttuu lisäämällä dollarin merkkejä. Voit jatkaa F4 -näppäimen painamista, kunnes saat haluamasi viitteen.

Virheellinen viittaus taulukon / työkirjan nimiin

Kun viittaat muihin arkeihin tai työkirjoihin kaavassa, sinun on noudatettava tiettyä muotoa. Ja jos muoto on väärä, saat virheilmoituksen.

Jos esimerkiksi haluan viitata taulukon 2 soluun A1, viite olisi = Taulukko2! A1 (jossa arkin nimen jälkeen on huutomerkki)

Ja jos taulukon nimessä on useita sanoja (oletetaan, että se on esimerkkitietoja), viittaus olisi = 'Esimerkkitiedot'! A1 (jossa nimi on lainausmerkeissä ja sen jälkeen huutomerkki).

Jos viittaat ulkoiseen työkirjaan (oletetaan, että viittaat työkirjan Esimerkkitaulukko -soluun A1 esimerkkityökirjaan), viite on seuraavanlainen:

= '[Esimerkki työkirjasta.xlsx] Esimerkki -taulukko'! $ A $ 1

Ja jos suljet työkirjan, viittaus muuttuu siten, että se sisältää koko työkirjan polun (kuten alla):

= 'C: \ Users \ sumit \ Desktop \ [Esimerkki työkirja.xlsx] Esimerkki -taulukko'! $ A $ 1

Jos muutat työkirjan tai laskentataulukon nimen, johon kaava viittaa, se antaa sinulle #REF! virhe.

Lue myös: Ulkoisten linkkien ja viitteiden löytäminen Excelistä

Pyöreät viitteet

Pyöreä viittaus on, kun viitataan (suoraan tai epäsuorasti) samaan soluun, jossa kaavaa lasketaan.

Alla on yksinkertainen esimerkki, jossa käytän SUM -kaavaa solussa A4 ja käytän sitä itse laskelmassa.

= SUMMA (A1: A4)

Vaikka Excel näyttää kehotteen, joka kertoo sinulle pyöreästä viittauksesta, se ei tee sitä kaikissa tapauksissa. Ja tämä voi antaa sinulle väärän tuloksen (ilman varoitusta).

Jos epäilet pyöreää viitettä pelissä, voit tarkistaa, missä soluissa se on.

Voit tehdä tämän napsauttamalla Kaava-välilehteä ja napsauttamalla Kaavan tarkastus -ryhmässä avattavaa Virheentarkistus-kuvaketta (pieni alaspäin osoittava nuoli).

Vie hiiri ympyräviittausvaihtoehdon päälle ja se näyttää solun, jossa on pyöreä viittausongelma.

Tekstiksi muotoillut solut

Jos joudut tilanteeseen, jossa heti kun kirjoitat kaavan osumaksi enter, näet kaavan arvon sijasta, on selvää tapaus, jossa solu muotoillaan tekstiksi.

Kun solu on muotoiltu tekstiksi, se pitää kaavaa tekstimerkkijonona ja näyttää sen sellaisenaan.

Se ei pakota sitä laskemaan ja antamaan tulosta.

Ja siihen on helppo korjata.

1. Muuta muoto "Yleiseksi" tekstiksi (se on Numerot -ryhmän Aloitus -välilehdessä)
2. Siirry soluun, jolla on kaava, siirry muokkaustilaan (käytä F2 tai kaksoisnapsauta solua) ja paina Enter

Jos yllä olevat vaiheet eivät ratkaise ongelmaa, toinen asia on tarkistaa, onko solussa alussa heittomerkki. Monet ihmiset lisäävät heittomerkit muuntaakseen kaavat ja numerot tekstiksi.

Jos on heittomerkki, voit yksinkertaisesti poistaa sen.

Teksti muuttuu automaattisesti päivämääriksi

Excelillä on tämä huono tapa muuntaa päivämäärän näköinen todellinen päivämäärä. Jos esimerkiksi kirjoitat 1/1, Excel muuntaa sen kuluvan vuoden 01.1.

Joissakin tapauksissa tämä voi olla juuri sitä, mitä haluat, ja joissakin tapauksissa tämä voi toimia sinua vastaan.

Ja koska Excel tallentaa päivämäärä- ja aika -arvot numeroina, heti kun kirjoitat 1/1, se muuntaa sen numeroksi, joka edustaa kuluvan vuoden 1. tammikuuta. Minun tapauksessani, kun teen tämän, se muuntaa sen numeroksi 43831 (01.1.2020).

Tämä voi sekoittaa kaavasi, jos käytät näitä soluja argumenttina kaavassa.

Kuinka korjata tämä?

Emme myöskään halua, että Excel valitsee muodon automaattisesti meille, joten meidän on määritettävä muoto selkeästi itse.

Alla on vaiheet, joiden avulla voit muuttaa muodon tekstiksi niin, että se ei muunna tekstiä automaattisesti päivämääriksi:

1. Valitse solut/alue, joissa haluat muuttaa muotoa
2. Napsauta Etusivu -välilehteä
3. Napsauta Numero-ryhmässä avattavaa Muoto-valikkoa
4. Napsauta Teksti

Nyt kun kirjoitat jotain valittuihin soluihin, se katsotaan tekstiksi eikä sitä muuteta automaattisesti.

Huomautus: Yllä olevat vaiheet toimivat vain tiedoille, jotka on syötetty muotoilun muuttamisen jälkeen. Se ei muuta tekstiä, joka on muunnettu päivämäärään ennen tämän muotoilumuutoksen tekemistä.

Toinen esimerkki, jossa tämä voi olla todella turhauttavaa, on, kun kirjoitat tekstin/numeron, jossa on nollia. Excel poistaa nämä nollaukset automaattisesti, koska se pitää niitä hyödyttöminä. Jos esimerkiksi syötät soluun 0001, Excel muuttaa sen arvoksi 1. Jos haluat säilyttää nämä nolla, toimi yllä olevien ohjeiden mukaisesti.

Piilotetut rivit/sarakkeet voivat antaa odottamattomia tuloksia

Tämä ei ole tapaus, jossa kaava antaa sinulle väärän tuloksen, vaan väärän kaavan käyttäminen.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluan saada kaikkien sarakkeen C näkyvien solujen summan.

Solussa C12 olen käyttänyt SUM -funktiota saadaksesi kaikkien näiden tietueiden kokonaismyyntiarvon.

Toistaiseksi niin hyvin!

Käytän nyt suodatinta tuote -sarakkeessa näyttääkseni vain tulostimen myynnin tietueet.

Ja tässä on ongelma - solun C12 kaava näyttää edelleen saman tuloksen - eli kaikkien tietueiden summan.

Kuten sanoin, kaava ei anna väärää tulosta. Itse asiassa SUM -toiminto toimii hienosti.

Ongelma on, että olemme käyttäneet väärää kaavaa täällä.

SUM -funktio ei voi ottaa huomioon suodatettuja tietoja ja antaa sinulle kaikkien solujen (piilotetut tai näkyvät) tuloksen. Jos haluat saada vain näkyvien solujen summan/lukumäärän/keskiarvon, käytä VAIHTO- tai AGGREGAATIO -funktioita.

Avain takeaway - Ymmärrä Excelin toiminnon oikea käyttö ja rajoitukset.

Nämä ovat joitain yleisiä syitä, jotka olen nähnyt missä olet Excel -kaavat eivät välttämättä toimi tai antavat odottamattomia tai vääriä tuloksia. Olen varma, että on monia muita syitä siihen, miksi Excel -kaava ei toimi tai päivity.

Jos tiedät jonkin muun syyn (ehkä jotain, joka ärsyttää sinua usein), kerro siitä kommenttiosassa.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä!