Težave pri izračunu formul v programu Microsoft Excel. Način strani delovnega lista v Excelu. Informacije v komentarjih

Ena izmed najbolj zahtevanih funkcij Excela je delo s formulami. Zahvaljujoč tej funkciji program samostojno izvaja različne vrste izračunov v tabelah. Včasih pa se zgodi, da uporabnik vnese formulo v celico, vendar ne izpolnjuje svojega neposrednega namena - izračunati rezultat. Ugotovimo, s čim je to lahko povezano in kako rešiti to težavo.

Vzroki za težave pri izračunu formul v Excelu so lahko popolnoma različni. Lahko jih povzročijo nastavitve določene knjige ali celo ločen obseg celic ali različne napake v skladnji.

1. način: spremenite obliko celic

Eden najpogostejših razlogov, zakaj Excel sploh ne izračuna ali ne izračuna formule pravilno, je napačno nastavljena oblika celic. Če ima obseg besedilni obliki, potem izračun izrazov v njem sploh ni izveden, torej so prikazani kot običajno besedilo. V drugih primerih, če oblika ne ustreza bistvu izračunanih podatkov, se lahko rezultat, prikazan v celici, prikaže napačno. Ugotovimo, kako odpraviti to težavo.



Zdaj bo formula izračunana v standardnem vrstnem redu z izidom rezultata v podani celici.


2. način: izklopite način »Pokaži formule«

Morda pa je razlog, da namesto rezultatov izračuna prikažete izraze, ta, da je program omogočil način "Pokaži formule".




3. način: popravite sintaktično napako

Formula se lahko prikaže tudi kot besedilo, če je v njeni sintaksi prišlo do napak, na primer manjka črka ali se je spremenila. Če ste ga vnesli ročno in ne prek Čarovnik za funkcije, potem je to zelo verjetno. Zelo pogosta napaka pri prikazu izraza kot besedila je prisotnost presledka pred znakom «=» .


V takih primerih morate natančno pregledati skladnjo tistih formul, ki so prikazane nepravilno, in ustrezno prilagoditi.

Metoda 4: omogočite preračun formule

Zgodi se tudi, da se zdi, da formula prikazuje vrednost, a ko se celice, povezane z njo, spremenijo, se tudi sama ne spremeni, to pomeni, da se rezultat ne preračuna. To pomeni, da ste napačno konfigurirali računske parametre v tej knjigi.




Vsi izrazi v tem delovnem zvezku bodo zdaj samodejno preračunani vsakič, ko se spremeni katera koli povezana vrednost.

5. metoda: napaka v formuli

Če program še vedno izračuna, vendar zaradi tega pokaže napako, je verjetno, da je uporabnik pri vnosu izraza preprosto naredil napako. Napačne formule so tiste, pri izračunu katerih se v celici pojavijo naslednje vrednosti:

  • #ŠTEVILO !;
  • #VREDNOST!;
  • # PRAZNO !;
  • # DIV / 0!;
  • # N / A

V tem primeru morate preveriti, ali so podatki v celicah, na katere se nanaša izraz, pravilno zapisani, ali obstajajo kakšne sintaktične napake ali formula sama vsebuje napačno dejanje (na primer deljenje z 0).


Če je funkcija zapletena, z velikim številom povezanih celic, je lažje slediti izračunom s posebnim orodjem.




Kot lahko vidite, so lahko razlogi, zakaj Excel ne upošteva ali ne izračuna pravilno formul, popolnoma različni. Če se namesto izračuna uporabniku prikaže sama funkcija, potem je v tem primeru najverjetneje celica oblikovana za besedilo ali pa je vklopljen način pogleda izraza. Možna je tudi sintaktična napaka (na primer prisotnost presledka pred znakom «=» ). Če se po spremembi podatkov v povezanih celicah rezultat ne posodobi, morate videti, kako je samodejna posodobitev konfigurirana v parametrih knjige. Prav tako se precej pogosto namesto pravilnega rezultata v celici prikaže napaka. Tu si morate ogledati vse vrednosti, na katere se funkcija nanaša. Če se odkrije napaka, jo je treba odpraviti.

Bralci Lifehackerja so že seznanjeni Denis Batyanov ki je delil z nami. Danes bo Denis govoril o tem, kako se izogniti najpogostejšim težavam z Excelom, ki si ga pogosto ustvarjamo sami.

Takoj bom rezerviral, da je gradivo za člane namenjeno začetnikom uporabnikom Excela. Izkušeni uporabniki so na tem grablju že večkrat zanemarljivo plesali, zato je moja naloga, da pred tem zaščitim mlade in neizkušene "plesalce".

Naslov stolpcem tabele ne dajete

Številna orodja Excel, kot so razvrščanje, filtriranje, pametne tabele, vrtilne tabele, predvidevajo, da vaši podatki vsebujejo glave stolpcev. V nasprotnem primeru jih sploh ne boste mogli uporabljati ali pa ne bodo delovali pravilno. Vedno se prepričajte, da vaše tabele vsebujejo naslove stolpcev.

Prazni stolpci in vrstice v tabelah

To je za Excel zmedeno. Ko v tabeli naletite na prazno vrstico ali stolpec, se vam zdi, da imate dve tabeli, ne eno. To boste morali nenehno popravljati. Prav tako ne skrivajte vrstic / stolpcev, ki jih ne potrebujete, v tabeli, bolje jih je izbrisati.

Na enem listu je več tabel

Če to niso majhne tabele z referenčnimi vrednostmi, tega ne bi smeli storiti.

Če boste v celoti delali z več kot eno mizo na listu, vam bo neprijetno. Na primer, če je ena miza na levi strani, druga pa na desni, bo filtriranje na eni mizi vplivalo na drugo. Če se mize nahajajo ena pod drugo, potem ni mogoče uporabiti pripenjanja območij, ena od tabel pa bo morala nenehno iskati in izvajati nepotrebne manipulacije, da bo na njej stala s kazalcem mize. Ali ga potrebujete?

Podatki iste vrste so umetno razporejeni v različne stolpce

Zelo pogosto uporabniki, ki poznajo Excel precej površno, dajejo prednost tej obliki tabele:

Zdi se, da imamo pred seboj neškodljiv format za zbiranje informacij o prodaji agentov in njihovih globah. Takšno postavitev mize oseba dobro vizualno zazna, saj je kompaktna. Verjemite pa, da je poskušati izvleči podatke iz tabel in pridobiti vmesne vsote (zbirne informacije) je prava nočna mora.

Dejstvo je, da ta oblika vsebuje 2 dimenziji: tako da se morate odločiti za vrstico, tako da greste skozi podružnico, skupino in zastopnika. Ko najdete pravo zalogo, boste morali poiskati zahtevani stolpec, saj jih je tukaj veliko. In ta "dvodimenzionalnost" močno otežuje delo s takšno tabelo za standardna orodja Excel - formule in vrtilne tabele.

Če sestavite vrtilno tabelo, boste ugotovili, da podatkov po letu ali četrtletju ni mogoče enostavno dobiti, saj so kazalniki razdeljeni na različna polja. Nimate enega prodajnega polja, s katerim je mogoče priročno manipulirati, ampak 12 ločenih polj. Ročno boste morali ustvariti ločena izračunana polja za četrtletja in leta, čeprav naj bo vse v enem stolpcu, vrteča miza bi to naredil namesto tebe.

Če želite uporabiti standardne formule za seštevanje, kot so SUMIF, SUMIFS, SUMPRODUCT, boste tudi ugotovili, da s to postavitvijo tabele ne bodo delovale učinkovito.

Porazdelitev informacij na različne liste knjige "zaradi udobja"

Druga pogosta napaka je, da imate nekakšno standardno obliko tabele in da na podlagi teh podatkov potrebujete analitiko, jo razdelite na ločene liste delovnega zvezka Excel. Na primer, pogosto ustvarijo ločene liste za vsak mesec ali leto. Posledično se količina analize podatkov dejansko pomnoži s številom ustvarjenih listov. Ne počni tega. Zberite podatke na ENEM listu.

Informacije v komentarjih

Uporabniki pogosto dodajajo pomembna informacija jih bodo morda potrebovali v komentarju celice. Ne pozabite, da lahko tisto, kar je v komentarjih, samo pogledate (če ga najdete). To je težko izvleči v celico. Priporočam, da je za komentarje bolje imeti ločen stolpec.

Nered pri oblikovanju

Na vašo mizo zagotovo ne boste dodali nič dobrega. To ljudem, ki uporabljajo vaše preglednice, ni všeč. V najboljšem primeru temu ne bodo pripisali pomena, v najslabšem primeru - mislili bodo, da niste organizirani in nemarni v poslu. Prizadevajte si za naslednje:

Združevanje celic

Združevanje celic uporabite le, če brez tega ne morete. Združene celice zelo otežujejo manipulacijo razponov, v katere prihajajo. Težave nastanejo pri premikanju celic, vstavljanju celic itd.

Združite besedilo in številke v eno celico

Boleč vtis naredi celica s številko, zadaj obložena z besedilno konstanto "RUB". ali "USD" vneseno ročno. Še posebej, če ne gre za tiskano obliko, ampak za običajno mizo. Aritmetične operacije s takšnimi celicami so naravno nemogoče.

Številke kot besedilo v celici

Izogibajte se shranjevanju številskih podatkov v celici v besedilni obliki. Sčasoma bodo nekatere celice v takem stolpcu imele obliko besedila, nekatere pa v običajni obliki. Zaradi tega bodo težave s formulami.

Če bo vaša miza predstavljena z LCD projektorjem

Izberite najbolj kontrastne kombinacije barve in ozadja. Dobro izgleda na projektorju temno ozadje in svetle črke. Najbolj grozen vtis naredi rdeča na črno in obratno. Ta kombinacija na projektorju izgleda izjemno nizkokontrastna - izogibajte se ji.

Delovni list načina strani v Excelu

To je isti način, v katerem Excel pokaže, kako bo list razdeljen na stran, ko je natisnjen. Robovi strani so označeni z modro barvo. Ne priporočam nenehnega dela v tem načinu, kar mnogi počnejo, saj je gonilnik tiskalnika vključen v postopek prikaza podatkov na zaslonu, kar je odvisno od številnih razlogov (na primer omrežni tiskalnik trenutno ni na voljo) ), je polno zamrznitev v procesu upodabljanja in preračunavanja formul. Delajte kot običajno.

Za še več koristnih informacij o Excelu obiščite

Dovolj pogosto podatki iz programov drugih proizvajalcev se prenesejo v Excel za njihovo nadaljnjo obdelavo in pogosto nadaljnja uporaba teh podatkov v formulah daje nepredvidljiv rezultat: številke se ne seštevajo, nemogoče je izračunati število dni med datumi itd.

Ta članek obravnava vzroke za takšne težave in različne poti njihovo odpravo

Prvi razlog ... Številka je shranjena kot besedilo

V tem primeru lahko vidite, da so številke ali datumi pritisnjeni na levi rob celice (kot besedilo) in praviloma v zgornjem levem kotu celice obstaja oznaka napake (zeleni trikotnik) in oznaka to, ko premaknete miško, pojasni, da je številka shranjena kot besedilo.

Nobena sprememba oblike v Numeric, General ali Date ne odpravi situacije, če pa kliknete v vrstico s formulami (ali pritisnete F2) in nato pritisnete Enter, številka postane številka, datum pa datum. Vidite, da je pri velikem številu takih številk nesprejemljiva.

Obstaja več načinov za rešitev tega problema.

  • Uporaba označevalca napak in oznake... Če vidite oznako napake ( zeleni trikotnik) in označite, nato izberite celice, kliknite oznako in izberite možnost Pretvori v številko
  • Uporaba operacije Najdi / Zamenjaj... Recimo, da so v tabeli številke z decimalno vejico, shranjene kot besedilo. Izberite obseg s številkami - kliknite Ctrl + h(ali poiščite na zavihku doma ali na meniju Uredi za različice pred letom 2007 ukaz Zamenjati) - na terenu Najti predstaviti , (vejica) - v polju Zamenjan z uvajamo tudi , (vejica) - Zamenjaj vse... Tako z zamenjavo vejice z vejico na enak način simuliramo urejanje celic F2 - Vnesite


Podobno operacijo je mogoče izvesti z datumi, z edino razliko, ki jo morate spremeniti od točke do točke.

Poleg tega lahko programi tretjih oseb raztovorijo številke s piko kot decimalnim ločevalnikom, potem pa bo pomoč pri zamenjavi pike z vejico.

Podobno zamenjavo lahko izvedemo s formulo (glej spodaj) s funkcijo SUBSTITUTE ()

  • Uporaba posebne paste... Ta metoda je bolj vsestranska, saj deluje z delnimi številkami, celimi števili in datumi. Izberite poljubno prazno celico - izvedite ukaz Kopirati- izberite obseg s številkami težav - Posebno prilepite -- Za zlaganje-- v redu... Tako številkam (ali datumom) dodamo 0, kar na noben način ne vpliva na njihovo vrednost, ampak se pretvori v obliko števila


Različica te tehnike bi lahko bila obseg pomnožen z 1

  • Uporaba orodja Besedilo po stolpcih... Ta tehnika je priročna za uporabo, če morate pretvoriti en stolpec, saj če je več stolpcev, je treba korake ponoviti za vsak stolpec posebej. Torej izberite stolpec s številkami ali datumi, shranjenimi kot besedilo, nastavite obliko celice Splošno(za številke lahko na primer nastavite Številčno ali Finančni). Nato izvedemo ukaz Podatki-- Besedilo stolpca -- Pripravljen


  • Uporaba formul... Če tabela dopušča dodatne stolpce, lahko uporabite formule za pretvorbo v številko. Če želite besedilno vrednost pretvoriti v številko, lahko uporabite dvojni minus, seštevek z ničlo, pomnožitev za eno, funkcije VALUE (), SUBSTITUTE (). Podrobneje si lahko preberete. Po preoblikovanju lahko nastali stolpec kopirate in prilepite kot vrednosti namesto prvotnih podatkov


  • Z uporabo... Pravzaprav lahko katero koli od navedenih metod izvedete z makrom. Če morate tovrstno pretvorbo pogosto izvajati, je smiselno napisati makro in ga po potrebi zagnati.

Tu sta dva primera makrov:

1) pomnožite z 1

Sub conv () Dim c kot obseg za vsak c v izboru Če je številčno (c.Value), potem c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End Če je naslednji End Sub

2) besedilo po stolpcih

Sub conv1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" Končaj podv.

Drugi razlog ... Številka vsebuje tuje znake.

Najpogosteje so ti tuji znaki presledki. Lahko se nahajajo znotraj številke kot ločilo števk in pred / za številko. V tem primeru seveda številka postane besedilo.

Pospravi dodatni prostori se lahko izvede tudi s pomočjo operacije Poišči / Zamenjaj... Na terenu Najti vnesite presledek in polje Zamenjan z potem pustite prazno Zamenjaj vse... Če bi bili v številki navadni presledki, bodo ta dejanja dovolj. Številka pa lahko vsebuje tako imenovane neprekinjene presledke (simbol s kodo 160). Tak prostor bo treba kopirati neposredno iz celice in ga nato prilepiti v polje Najti pogovorno okno Poišči / Zamenjaj... Ali pa lahko na terenu Najti pritisnite bližnjico na tipkovnici Alt + 0160(številke se vnesejo na številski tipkovnici).

Prostore lahko odstranite tudi s formulo. Možnosti so:

Za običajne prostore: = - ZAMENJA (B4; ""; "")

Za neprekinjene prostore:= - ZAMENJA (B4, SIMBOL (160), "")

Takoj za tiste in druge prostore: = - ZAMENJALNIK (ZAMENJALNIK (B4; SIMBOL (160); ""); ""; "")

Včasih morate za dosego želenega rezultata združiti navedene metode. Na primer, najprej odstranite presledke in nato pretvorite obliko celice



Priporočeno branje