Probleemid valemite arvutamisel Microsoft Excelis. Töölehe leherežiim Excelis. Teave kommentaarides

Üks Exceli kõige nõutumaid funktsioone on valemitega töötamine. Tänu sellele funktsioonile teostab programm iseseisvalt erinevaid arvutusi tabelites. Kuid mõnikord juhtub, et kasutaja sisestab lahtrisse valemi, kuid see ei täida oma otsest eesmärki - tulemuse arvutamist. Mõelgem välja, millega see võib olla seotud ja kuidas seda probleemi lahendada.

Excelis valemite arvutamisega seotud probleemide põhjused võivad olla täiesti erinevad. Neid võivad põhjustada nii konkreetse raamatu seaded või isegi eraldi lahtrivahemik või mitmesugused süntaksi vead.

Meetod: muutke lahtrite vormingut

Üks levinumaid põhjusi, miks Excel üldse ei arvesta või valemeid ei arvesta, on valesti määratud lahtrite vorming. Kui vahemikus on teksti vormingus, siis selles olevate avaldiste arvutamist ei tehta üldse, see tähendab, et need kuvatakse tavalise tekstina. Muudel juhtudel, kui vorming ei vasta arvutatud andmete olemusele, võidakse lahtris kuvatud tulemus kuvada valesti. Uurime, kuidas seda probleemi lahendada.



Nüüd arvutatakse valem standardses järjekorras koos tulemuse väljundiga määratud lahtris.


Meetod: lülitage režiim "Kuva valemid" välja

Kuid võib -olla põhjus, miks arvutustulemuste asemel kuvate avaldisi, on see, et programm on režiimi lubanud "Kuva valemid".




3. meetod: parandage süntaksiviga

Valemit saab kuvada ka tekstina, kui selle süntaksis tehti vigu, näiteks täht puudus või seda muudeti. Kui sisestasite selle käsitsi, mitte läbi Funktsiooni viisard, siis on see üsna tõenäoline. Väga levinud viga avaldise tekstina kuvamisel on tühiku olemasolu märgi ees «=» .


Sellistel juhtudel peate hoolikalt üle vaatama valesti kuvatud valemite süntaksi ja tegema vajalikud kohandused.

4. meetod: lubage valemi ümberarvutus

Samuti juhtub, et valem näib väärtust kuvavat, kuid kui sellega seotud lahtrid muutuvad, ei muutu see ise, st tulemust ei arvutata ümber. See tähendab, et olete selles raamatus arvutusparameetreid valesti seadistanud.




Kõik selle töövihiku avaldised arvutatakse nüüd automaatselt ümber, kui nendega seotud väärtusi muudetakse.

5. meetod: viga valemis

Kui programm ikkagi arvutab, kuid selle tulemusel kuvatakse tõrge, on tõenäoline, et kasutaja tegi avaldise sisestamisel lihtsalt vea. Ekslikud valemid on need, mille arvutamisel arvutatakse lahtris järgmised väärtused:

  • #NUMBER !;
  • #VÄÄRTUS!;
  • # TÜHI !;
  • # DIV / 0!;
  • # N / A.

Sellisel juhul peate kontrollima, kas avaldisega viidatud lahtrite andmed on õigesti kirjutatud, kas esineb süntaksivigu või kas valem ise sisaldab valesid toiminguid (näiteks jagamine 0 -ga).


Kui funktsioon on keeruline ja suure hulga lingitud lahtritega, on arvutuste jälgimine spetsiaalse tööriista abil lihtsam.




Nagu näete, võivad põhjused, miks Excel valemeid ei arvesta või ei arvuta õigesti, võivad olla täiesti erinevad. Kui arvutamise asemel kuvab kasutaja funktsiooni ise, siis on sel juhul suure tõenäosusega kas lahtr teksti jaoks vormindatud või avaldise vaatamise režiim lubatud. Samuti on võimalik süntaksiviga (näiteks tühiku olemasolu märgi ees «=» ). Kui pärast lingitud lahtrite andmete muutmist tulemust ei värskendata, peate siin vaatama, kuidas raamatu parameetrites automaatne värskendamine on konfigureeritud. Samuti kuvatakse üsna sageli õige tulemuse asemel lahtris viga. Siin peate vaatama kõiki väärtusi, millele funktsioon viitab. Kui viga leitakse, tuleb see kõrvaldada.

Lifehackeri lugejad on sellega juba tuttavad Denis Batjanov kes meiega jagas. Täna räägib Denis, kuidas vältida kõige tavalisemaid probleeme Exceliga, mille me sageli endale loome.

Teen kohe reservatsiooni, et artikkel on mõeldud algajatele Exceli kasutajatele. Kogenud kasutajad on sellel rehal juba korduvalt sütitavalt tantsinud, seega on minu ülesanne kaitsta noori ja kogenematuid "tantsijaid" selle eest.

Te ei anna tabeli veergudele pealkirju

Paljud Exceli tööriistad, nagu sortimine, filtreerimine, nutikad tabelid, pöördtabelid, eeldavad, et teie andmed sisaldavad veerupäiseid. Vastasel juhul ei saa te neid üldse kasutada või nad ei tööta päris õigesti. Veenduge alati, et teie tabelid sisaldaksid veerupäiseid.

Tühjendage tabelites veerud ja read

See on Exceli jaoks segane. Kui tabeli sees on tühi rida või veerg, hakkab see arvama, et teil on kaks tabelit, mitte üks. Peate seda pidevalt parandama. Samuti ärge peitke tabeli sees ridu / veerge, mida te ei vaja, parem on need kustutada.

Ühel lehel on mitu tabelit

Kui need ei ole väikesed tabelid, mis sisaldavad võrdlusväärtusi, siis ei tohiks te seda teha.

Teil on ebamugav töötada rohkem kui ühe lauaga lehel. Näiteks kui üks tabel asub vasakul ja teine ​​paremal, mõjutab ühe laua filtreerimine teist. Kui tabelid asuvad üksteise all, on alade kinnitamist võimatu kasutada ning üks tabelitest peab tabeli kursoriga sellel seismiseks pidevalt otsima ja tegema tarbetuid manipuleerimisi. Kas vajate seda?

Sama tüüpi andmed on kunstlikult paigutatud erinevatesse veergudesse

Väga sageli eelistavad kasutajad, kes teavad Exceli üsna pealiskaudselt, seda tabelivormingut:

Näib, et me seisame silmitsi kahjutu vormiga, mis võimaldab koguda teavet agentide müügi ja nende trahvide kohta. Sellist laua paigutust tajub inimene visuaalselt hästi, kuna see on kompaktne. Uskuge mind, aga tõeline õudusunenägu on püüda sellistest tabelitest andmeid välja võtta ja saada vahekokkuvõtteid (koondteavet).

Fakt on see, et see formaat sisaldab 2 mõõdet: nii et peate otsustama rea ​​üle, läbides filiaali, grupi ja agendi. Kui leiate õige varu, peate otsima vajaliku veeru, kuna neid on siin palju. Ja see "kahemõõtmelisus" raskendab oluliselt sellise tabeli tööd standardsete Exceli tööriistade jaoks - valemid ja pöördtabelid.

Pöördtabeli koostamisel leiad, et andmeid aasta või kvartali kohta pole lihtne hankida, kuna näitajad on jagatud erinevateks väljadeks. Teil pole ühte mugavalt manipuleeritavat müügivälja, kuid seal on 12 eraldi välja. Peate käsitsi looma eraldi arvutatud väljad kvartalite ja aastate kohta, kuigi olgu see kõik ühes veerus, pöördlaud teeks seda sinu eest.

Kui soovite rakendada standardseid summeerimisvalemeid nagu SUMIF, SUMIFS, SUMPRODUCT, leiate ka, et need ei tööta selle tabelipaigutusega tõhusalt.

Teabe jagamine raamatu erinevatel lehtedel "mugavuse huvides"

Teine levinud viga on see, et kui teil on mingi standardne tabelivorming ja vajate nende andmete põhjal analüüsi, levitage see Exceli töövihiku eraldi lehtedele. Näiteks loovad nad sageli iga kuu või aasta kohta eraldi lehed. Selle tulemusel korrutatakse andmeanalüüsi töö hulk tegelikult loodud lehtede arvuga. Ära tee seda. Koguge teave ÜHEL lehel.

Teave kommentaarides

Sageli lisavad kasutajad olulist teavet nad võivad vajada lahtri kommentaari. Pidage meeles, et seda, mis on kommentaarides, saate ainult vaadata (kui leiate selle). Selle lahtrisse tõmbamine on keeruline. Soovitan parem, kui kommentaaride jaoks on eraldi veerg.

Segadus vormindamisega

Kindlasti ei lisa see teie lauale midagi head. See ei meeldi teie arvutustabeleid kasutavatele inimestele. Parimal juhul ei anna nad sellele tähtsust, halvimal juhul - nad arvavad, et te pole ettevõtluses organiseeritud ja lohakas. Püüdke järgmiste poole:

Lahtrite ühendamine

Kasutage lahtrite liitmist ainult siis, kui te ei saa ilma selleta hakkama. Ühendatud lahtrid raskendavad sisenemispiirkondade manipuleerimist. Probleemid tekivad rakkude liigutamisel, lahtrite sisestamisel jne.

Kombineerige tekst ja numbrid ühes lahtris

Valusa mulje tekitab numbrit sisaldav lahter, mille tagaküljel on tekstikonstant "RUB". või "USD" käsitsi. Eriti kui see pole trükitud vorm, vaid tavaline laud. Selliste lahtritega on aritmeetilised toimingud loomulikult võimatud.

Numbrid tekstina lahtris

Vältige arvandmete salvestamist lahtrisse tekstivormingus. Aja jooksul on mõnel sellise veeru lahtril tekstivorming ja mõnel tavaline. Selle tõttu tekivad valemitega probleemid.

Kui teie laud esitatakse LCD -projektori kaudu

Valige kõige kontrastsemad värvi ja tausta kombinatsioonid. Näeb projektori peal hea välja tume taust ja heledad tähed. Kõige kohutavama mulje jätab punane mustal ja vastupidi. See kombinatsioon tundub projektoril äärmiselt madala kontrastsusega - vältige seda.

Leherežiimi tööleht Excelis

See on sama režiim, milles Excel näitab, kuidas leht trükituna leheküljeks saab. Lehe äärised on sinisega esile tõstetud. Ma ei soovita selles režiimis pidevalt töötada, nagu paljud seda teevad, kuna printeridraiver on kaasatud andmete ekraanile kuvamise protsessi ja seda sõltuvalt paljudest põhjustest (näiteks võrguprinter pole praegu saadaval ), on täis renderdamisprotsessi ja valemite ümberarvutamise külmutamist. Töötage nagu tavaliselt.

Veelgi kasulikumat teavet Exceli kohta leiate aadressilt

Piisavalt sageli kolmandate osapoolte programmide andmed laaditakse Excelisse üles nende edasiseks töötlemiseks ja sageli nende andmete edasine kasutamine valemites annab ettearvamatu tulemuse: numbreid ei liideta kokku, kuupäevade vaheliste päevade arvu on võimatu arvutada jne.

Selles artiklis käsitletakse selliste probleemide põhjuseid ja erinevaid viise nende kõrvaldamine

Põhjus üks ... Number salvestati tekstina

Sel juhul näete, et numbrid või kuupäevad on vajutatud lahtri vasakule servale (tekstina) ja reeglina on lahtri vasakus ülanurgas veamarker (roheline kolmnurk) ja silt mis selgitab hiire hõljutamisel, et number salvestatakse tekstina.

Ükski vormingu muutmine numbriliseks, üldiseks või kuupäevaks ei paranda olukorda, kuid kui klõpsate valemiribal (või vajutate klahvi F2) ja seejärel sisestusklahvi, muutub number numbriks ja kuupäev kuupäevaks. Selliste arvude suure hulga puhul on see valik vastuvõetamatu.

Selle probleemi lahendamiseks on mitu võimalust.

  • Veamarkeri ja sildi kasutamine... Kui näete veamarkerit ( roheline kolmnurk) ja märgend, seejärel valige lahtrid, klõpsake sildil ja valige suvand Teisenda arvuks
  • Otsimis- / asendustoimingu kasutamine... Oletame, et tabelis on kümnendkohaga numbreid, mis on salvestatud tekstina. Valige numbritega vahemik - klõpsake Ctrl + h(või leiate vahekaardilt Kodu või menüüst Muuda versioonidele enne 2007. aastat käsk Asenda) - põllul Leia tutvustada , (koma) - väljal Asendatud tutvustame ka , (koma) - Asenda kõik... Seega, asendades koma komaga, simuleerime lahtrite redigeerimist samamoodi F2 - Sisestage


Sarnast toimingut saab teha ka kuupäevadega, ainus erinevus on see, et peate punktist punkti muutma.

Lisaks saavad kolmanda osapoole programmid kümnendkoha eraldajana tähti tühjendada, siis aitab punkti asendamine komaga.

Sarnast asendust saab teha valemiga (vt allpool), kasutades funktsiooni SUBSTITUTE ()

  • Paste Speciali kasutamine... See meetod on mitmekülgsem, kuna see töötab murdarvude, täisarvude ja kuupäevadega. Valige suvaline tühi lahter - täitke käsk Kopeeri- valige probleemide numbritega vahemik - Kleebi spetsiaalselt -- Voltimiseks-- Okei... Seega lisame numbritele (või kuupäevadele) 0, mis ei mõjuta kuidagi nende väärtust, vaid teisendatakse numbrivormingusse


Selle tehnika variatsiooniks võiks olla vahemiku korrutamine 1 -ga

  • Tööriista Tekst veergude järgi kasutamine... Seda tehnikat on mugav kasutada, kui peate ühe veeru teisendama, sest kui veerge on mitu, tuleb samme korrata iga veeru jaoks eraldi. Niisiis, valige veerg, kus on tekstina salvestatud numbrid või kuupäevad, määrake lahtrivorming Kindral(numbrite puhul saate määrata näiteks Numbriline või Finants). Järgmisena täidame käsu Andmed-- Veeru tekst -- Valmis


  • Valemite kasutamine... Kui tabel lubab täiendavaid veerge, saate arvuks teisendamiseks kasutada valemeid. Tekstiväärtuse teisendamiseks arvuks saate kasutada kahekordset miinust, liitmist nulliga, korrutamist ühega, funktsioone VALUE (), SUBSTITUTE (). Saate lugeda üksikasjalikumalt. Pärast teisendamist saab saadud veeru kopeerida ja kleepida väärtustena algandmete asemele


  • Kasutades... Tegelikult saab makroga kasutada kõiki loetletud meetodeid. Kui peate sellist konversiooni sageli tegema, on mõistlik kirjutada makro ja käivitada see vastavalt vajadusele.

Siin on kaks näidet makrodest:

1) korrutage 1 -ga

Alamkonv () Dim c As vahemik iga c valiku korral, kui see on numbriline (c.Value) Siis c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End If Next End Sub

2) tekst veergude kaupa

Alamkonv 1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" Lõpu alam

Põhjus kaks ... Number sisaldab kõrvalisi märke.

Enamasti on need kõrvalised tegelased tühikud. Need võivad asuda nii numbri sees numbrite eraldajana kui ka enne / pärast numbrit. Sel juhul muutub number loomulikult tekstiks.

Pane ära lisaruumid seda saab teha ka operatsiooni abil Otsi / asenda... Väljas Leia sisestage tühik ja väli Asendatud jäta siis tühjaks Asenda kõik... Kui numbris oleks tavalisi tühikuid, piisab nendest toimingutest. Kuid number võib sisaldada niinimetatud murdumatuid tühikuid (sümbol koodiga 160). Selline ruum tuleb kopeerida otse lahtrist ja seejärel väljale kleepida Leia Dialoogikast Otsi / asenda... Või saate põllul Leia vajutage kiirklahvi Alt + 0160(numbrid sisestatakse numbriklaviatuuril).

Tühikuid saab eemaldada ka valemiga. Valikud on järgmised:

Tavaliste ruumide jaoks: = - ASENDUS (B4; ""; "")

Mittepurunevate ruumide jaoks:= - ASENDUS (B4, SÜMBOL (160), "")

Kohe nende ja muude ruumide jaoks: = - ASENDUS (ASENDAJA (B4; SÜMBOL (160); ""); ""; "")

Mõnikord peate soovitud tulemuse saavutamiseks kombineerima loetletud meetodid. Näiteks eemaldage kõigepealt tühikud ja seejärel teisendage lahtrivorming



Soovitatav lugeda