7 pārsteidzoši Excel pielāgoti skaitļu formātu triki (jums tas jāzina)

Excel pielāgoto skaitļu formatēšana ir apģērbs datiem Excel šūnās. Jūs varat tos ģērbt, kā vēlaties. Viss, kas jums nepieciešams, ir mazliet zināšanas par Excel pielāgoto skaitļu formāta darbību.

Izmantojot pielāgotu skaitļu formatējumu programmā Excel, varat mainīt šūnu vērtību parādīšanas veidu, bet tajā pašā laikā saglabājot sākotnējo vērtību neskartu.

Piemēram, es varu likt datuma vērtību parādīt dažādos datuma formātos, bet pamatā esošā vērtība (skaitlis, kas apzīmē datumu) paliek nemainīga.

Šajā apmācībā es apskatīšu visu par pielāgotu skaitļu formātu programmā Excel, kā arī parādīšu dažus patiešām noderīgus piemērus, kurus varat izmantot ikdienas darbā.

Tātad, sāksim!

Excel pielāgota skaitļa formāta konstrukcija

Pirms es jums parādīšu tā izcilību, ļaujiet man īsi paskaidrot, kā programmā Excel darbojas pielāgota skaitļu formatēšana.

Pēc konstrukcijas Excel šūnā var pieņemt šādus četru veidu datus:

• Pozitīvs skaitlis
• negatīvs skaitlis
• 0
• Teksta virknes (tas var ietvert tīras teksta virknes, kā arī burtciparu virknes)

Vēl viens datu veids, ko Excel var pieņemt, ir datumi, taču, tā kā visas datuma un laika vērtības programmā Excel tiek saglabātas kā skaitļi, tās tiks iekļautas kā pozitīvā skaitļa daļa.

Jebkurai Excel darblapas šūnai varat norādīt, kā šūnā jāparāda katrs no šiem datu veidiem.

Un zemāk ir sintakse, kurā varat norādīt šo formātu.

;;;

Ņemiet vērā, ka tos visus atdala ar semikolu (;).

Viss, ko ievadāt šūnā programmā Excel, ietilpst kādā no šīm četrām kategorijām, un tāpēc tam var norādīt pielāgotu formātu.

Ja jūs minat tikai:

• Viens formāts: To piemēro visām četrām sadaļām. Piemēram, ja jūs vienkārši rakstāt Vispārīgi, tas tiks piemērots visām četrām sadaļām.
• Divi formāti: Pirmais tiek piemērots pozitīviem skaitļiem un nullēm, bet otrais tiek piemērots negatīviem skaitļiem. Teksta formāts pēc noklusējuma kļūst par Vispārīgu.
• Trīs formāti: Pirmais tiek piemērots pozitīviem skaitļiem, otrais - negatīvajiem, trešais - nullei, un teksts pazūd, jo tekstam nekas nav norādīts.

Ja vēlaties uzzināt vairāk par pielāgotu numuru formatēšanu, es ļoti ieteiktu Office palīdzības sadaļu.

Kā tas darbojas un kur ievadīt šos formātus, tiks apskatīts vēlāk šajā apmācībā. Pagaidām tikai paturiet prātā, ka tā ir jebkuras šūnas formāta sintakse

Kā pielāgotā skaitļa formāts darbojas programmā Excel

Pēc tam atverat jaunu Excel darbgrāmatu, pēc noklusējuma visām darbgrāmatas darblapu šūnām ir vispārējais formāts, kur:

• Pozitīvi skaitļi tiek parādīti tādi, kādi tie ir (līdzināti pa kreisi)
• Negatīvie skaitļi tiek parādīti ar mīnusa zīmi (un izlīdzināti pa kreisi)
• Nulle tiek parādīts kā 0 (un līdzināts pa kreisi)
• Teksts tiek parādīts tāds (izlīdzināts pa labi)

Ja ievadāt datumu, tas tiek parādīts, pamatojoties uz jūsu reģionālajiem iestatījumiem (formātā DD-MM-GGGG vai MM-DD-GGGG).

Tātad pēc noklusējuma šūnas numura formatējums ir šāds:

Ģenerālis; Ģenerālis; Ģenerālis; Ģenerālis

Ja katrs datu veids ir formatēts kā vispārīgs (un ievērojiet iepriekš minētos noteikumus, pamatojoties uz to, vai tas ir pozitīvs skaitlis, negatīvs skaitlis, 0 vai teksts)

Tagad noklusējuma iestatījums ir kārtībā, bet kā mainīt formātu. Piemēram, ja es vēlos, lai mani negatīvie skaitļi tiktu parādīti sarkanā krāsā, vai es vēlos, lai mani datumi tiktu parādīti noteiktā formātā, kā to izdarīt.

Izmantojot lentes nolaižamo formātu

Dažus no visbiežāk izmantotajiem ciparu formātiem varat atrast cilnes Sākums grupā Skaitlis.

Tā ir nolaižamā izvēlne, kurā tiek parādīti daudzi visbiežāk izmantotie formāti, kurus varat lietot šūnai, vienkārši atlasot šūnu un pēc tam atlasot formātu no šī saraksta.

Piemēram, ja vēlaties pārvērst skaitļus procentos vai konvertēt datumus īsā datuma formātā vai garas formas datuma formātā, varat to viegli izdarīt, izmantojot nolaižamās izvēlnes opcijas.

Dialoglodziņa Format Cells izmantošana

Dialoglodziņi Šūnu formatēšana, kur jūs iegūstat maksimālu elastību, piemērojot formātus šūnai programmā Excel.

Tam jau ir daudz iepriekš sagatavotu formātu, kurus varat izmantot, vai arī, ja vēlaties izveidot savu pielāgoto skaitļu formātu, varat to izdarīt arī.

Tālāk ir norādīts, kā atvērt dialoglodziņu Šūnu formatēšana;

1. Noklikšķiniet uz cilnes Sākums
2. Grupā Cipari noklikšķiniet uz dialoglodziņa palaišanas ikonas (mazā noliektā bultiņa grupas apakšējā labajā stūrī).

Tiks atvērts dialoglodziņš Šūnu formatēšana. Tajā atradīsit visus formātus cilnē Skaitlis (kopā ar iespēju izveidot pielāgotus šūnu formātus)

Varat arī izmantot šo īsinājumtaustiņu, lai atvērtu dialoglodziņu Šūnu formatēšana:

CONTROL + 1 (viss vadības taustiņš un pēc tam nospiediet taustiņu 1).

Ja izmantojat Mac, varat izmantot CMD + 1

Tagad aplūkosim dažus praktiskus piemērus, kuros mēs izveidosim savus pielāgotos skaitļu formātus.

Piemēri pielāgotas skaitļu formatēšanas izmantošanai programmā Excel

Tagad aplūkosim dažus praktiskus piemērus, kuros mēs izveidosim savus pielāgotos skaitļu formātus.

Slēpt šūnas vērtību (vai slēpt visas vērtības šūnu diapazonā)

Dažreiz jums var būt nepieciešams slēpt šūnas saturu (neslēpjot rindu vai kolonnu, kurā ir šūna).

Praktisks gadījums ir tad, kad veidojat Excel informācijas paneli un informācijas panelī izmantojat dažas vērtības, taču nevēlaties, lai lietotāji to redzētu. lai jūs varētu vienkārši slēpt šīs vērtības

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties paslēpt visas skaitliskās vērtības:

Lūk, kā to izdarīt, izmantojot pielāgotu formatējumu programmā Excel:

1. Atlasiet šūnu vai šūnu diapazonu, kuram vēlaties paslēpt šūnas saturu
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

   ;;;

Kā tas darbojas?

Četru dažādu veidu datu formāts ir sadalīts ar semikolu šādā formātā:

;;;

Šajā metodē mēs esam noņēmuši formātu un norādījuši tikai semikolu, kas norāda, ka mēs nevēlamies šūnu formatējumu. Šī ir norāde programmai Excel, lai tā paslēptu visu šūnā esošo saturu.

Ņemiet vērā: lai gan tas slēpj šūnas saturu, tas joprojām pastāv. tādēļ, ja jums ir paslēpti skaitļi, jūs joprojām varat tos izmantot aprēķinos (kas padara to par lielisku triku Excel informācijas paneļiem un pārskatiem).

Parādiet negatīvos skaitļus sarkanā krāsā

Pēc noklusējuma negatīvie skaitļi tiek parādīti ar mīnusa zīmi (vai iekavās dažos reģionālajos iestatījumos).

Varat izmantot pielāgotu skaitļu formatējumu, lai mainītu negatīvo skaitļu krāsu un parādītu tos sarkanā krāsā.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties visas negatīvās vērtības izcelt sarkanā krāsā.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet šūnu diapazonu, kurā negatīvos skaitļus vēlaties parādīt sarkanā krāsā
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

Vispārīgi; [Sarkans]-Vispārīgi

Iepriekš minētais formāts parādīs jūsu šūnas ar negatīvām vērtībām sarkanā krāsā.

Kā tas darbojas?

Ja kā pielāgoto skaitļu formātu norādāt tikai divus formātus, pirmais tiek uzskatīts par pozitīviem skaitļiem un 0, bet otrais - par negatīviem skaitļiem (nākamā vērtība pēc noklusējuma kļūst par vispārējo formātu).

Iepriekš izmantotajā formātā, ko esam izmantojuši, kvadrātiekavās esam norādījuši krāsu, kuru vēlamies izmantot.

Tātad, lai gan negatīvajiem skaitļiem ir vispārējs formāts, tie tiek parādīti sarkanā krāsā ar mīnusa zīmi.

Ja nevēlamos skaitļus iekavās vēlaties apturēt sarkanā krāsā (mīnusa zīmes vietā), varat izmantot tālāk norādīto formātu.

Vispārīgi; [Sarkans] (Vispārīgi)

Pievienojiet tekstu skaitļiem (piemēram, miljoniem/miljardiem)

Viena pārsteidzoša lieta par pielāgotu skaitļu formatēšanu ir tāda, ka skaitlim varat pievienot prefiksu vai sufiksa tekstu, vienlaikus saglabājot šūnā sākotnējo numuru.

Piemēram, “100” var tikt parādīts kā “100 miljoni USD”, bet šūnā joprojām ir sākotnējā vērtība, lai to varētu izmantot aprēķinos.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties parādīt šos skaitļus ar dolāra zīmi un vārdu miljonu priekšā.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet datu kopu, kurā vēlaties cipariem pievienot tekstu
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

Ģenerālis "Miljons"

Ņemiet vērā, ka šis pielāgotais ciparu formāts ietekmēs tikai ciparus. Ja ievadāt tekstu tajā pašā šūnā, tas parādīsies tāds, kāds tas ir.

Kā tas darbojas?

Šajā piemērā vispārējam formātam esmu pievienojis vārdu miljons ar pēdiņām. viss, ko formātam pievienoju pēdiņās, tiek parādīts šūnā.

Es arī pievienoju dolāra zīmi pirms vispārējā formāta, lai visi mani skaitļi arī parādītu dolāra zīmi pirms skaitļa.

Piezīme. Ja jums rodas jautājums, kāpēc dolāra zīme nav pēdiņās, kamēr vārds Miljons ir pēdiņās, tas ir tāpēc, ka Excel atpazīst dolāru kā simbolu, kas bieži tiek izmantots pielāgotu skaitļu formatēšanā, un jums tas nav nepieciešams lai tam pievienotu pēdiņas. Jūs varat turpināt un pievienot dubultās pēdiņas dolāra zīmei, bet, aizverot dialoglodziņu Šūnu formatēšana un atverot to vēlreiz, pamanīsit, ka Excel to ir noņēmis

Slēpt ciparus un tekstu

Izmantojot pielāgotu skaitļu formatējumu, varat novērtēt ne vairāk kā divus nosacījumus, un, pamatojoties uz šiem nosacījumiem, šūnai var piemērot formātu.

Piemēram, pieņemsim, ka jums ir dažu studentu rezultātu saraksts, un vēlaties rādīt tekstu Atļauts vai Neveiksmīgs, pamatojoties uz rezultātu (ja jebkuram rādītājam, kas mazāks par 35, vajadzētu parādīt “Neizdevās”, citādi - “Pāreja”).

Tālāk ir norādītas darbības, lai parādītu rādītājus kā “Pass” vai “Fail”, nevis vērtību, izmantojot pielāgotu skaitļu formātu.

1. Atlasiet datu kopu, kurā ir rādītāji
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

   [<35] "Neizdevās"; "Iziet"

Tālāk ir norādīts, kā jūsu dati izskatīsies pēc iepriekš minētā formāta izmantošanas.

Ņemiet vērā, ka tas nemaina vērtību šūnās. Šūnā joprojām ir skaitliskās vērtības, vienlaikus parādot “Pass” vai “Fail” atkarībā no šūnas vērtības.

Kā tas darbojas?

Šajā piemērā nosacījums ir jānorāda kvadrātiekavās. pēc tam pielāgota skaitļa formatēšana novērtē šūnas vērtību un attiecīgi piemēro formātu.

Tātad, ja vērtība šūnā ir mazāka par 35, tad numurs tiek paslēpts, un tā vietā tiek parādīts teksts “Fail”, pievienojot visus citus gadījumus, tiek parādīts “Pass”.

Līdzīgi, ja vēlaties pārvērst šūnu diapazonu ar 0 un 1 ar TRUE un FALSE, varat izmantot tālāk norādīto formātu.

[= 0] "FALSE"; [= 1] "TRUE"

Atcerieties, ka pielāgoto skaitļu formatēšanā varat izmantot tikai divus nosacījumus. Ja mēģināsit izmantot vairāk nekā divus nosacījumus, tiks parādīts paziņojums, ka tas nevar pieņemt šo formātu.

Slēpt tekstu, bet parādīt ciparus

Dažreiz, lejupielādējot datus no datu bāzēm, jo ​​tās ir tukšas, tiek aizpildīta vērtība, piemēram, Nav pieejama vai NA vai -

Izmantojot pielāgotu skaitļu formatējumu, varat paslēpt visas teksta vērtības, vienlaikus saglabājot numurus tādus, kādi tie ir. tas arī nodrošina, ka jūsu sākotnējie dati ir neskarti (tā kā jūs neizdzēšat teksta vērtību, jūs vienkārši to slēpjat)

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk un kurā vēlaties paslēpt visas teksta vērtības:

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet datu kopu, kurā jums ir dati
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

Vispārīgi; -Vispārīgi; Vispārīgi;

Ņemiet vērā, ka cipariem esmu izmantojis vispārējo formātu. Jūs varat izvēlēties jebkuru formātu (piemēram, 0, 0. #, #0.0%). Ņemiet vērā arī to, ka pirms otrā vispārējā formāta ir negatīva zīme (-), jo tā apzīmē negatīvo skaitļu formātu.

Kā tas darbojas?

Tā kā šajā piemērā mēs vēlējāmies slēpt visas teksta vērtības, mēs vienkārši nenorādījām tā formātu.

Tā kā mēs norādījām formātu pārējiem trim datu tipiem (pozitīvs skaitlis, negatīvs skaitlis un nulle), programma Excel saprata, ka mēs mērķtiecīgi atstājām teksta vērtības formātu tukšu, un tam nevajadzētu parādīt neko, ja šūnai ir teksts virkne.

Rādīt skaitļus procentos (%)

Izmantojot pielāgotu skaitļu formatējumu, varat arī mainīt numurus un parādīt tos procentos.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties pārvērst šos skaitļus, lai parādītu līdzvērtīgu procentuālo vērtību:

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet šūnas, kurās ir skaitļi (kurus vēlaties konvertēt procentos)
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

0%

Tādējādi skaitļi tiks mainīti uz procentiem. Piemēram, tas mainās no 0,11 līdz 11%.

Ja viss, ko vēlaties darīt, ir pārvērst skaitli par tā ekvivalentu procentuālo vērtību, labāks veids, kā to izdarīt, ir izmantot procentu opciju šūnu formatēšanas dialoglodziņā.

Pielāgota formatēšana var būt noderīga, ja vēlaties vairāk kontrolēt procentuālās vērtības parādīšanu.

Piemēram, ja vēlaties parādīt papildu tekstu ar procentiem, visus negatīvos procentus vēlaties parādīt sarkanā krāsā, labāk ir izmantot pielāgotu formatējumu

Parādiet skaitļus citā vienībā (miljonos miljardu vai gramos kā kilogramos)

Ja strādājat ar lielu skaitu, iespējams, vēlēsities tos parādīt citā vienībā. piemēram, ja jums ir dažu uzņēmumu pārdošanas dati, iespējams, vēlēsities parādīt pārdošanas apjomus miljonos vai miljardos (vienlaikus saglabājot sākotnējo pārdošanas vērtību šūnās)

Par laimi, to var viegli izdarīt, izmantojot pielāgotu numuru formatējumu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties šīs pārdošanas vērtības parādīt miljonos.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet šūnas, kurās ir skaitļi
2. Atveriet dialoglodziņu Šūnu formatēšana (īsinājumtaustiņš Control + 1)
3. Cilnē Skaitlis noklikšķiniet uz Pielāgots
4. Laukā Tips ievadiet šādu formātu:

0.0,,

Iepriekš minētais sniegs jums rezultātu, kā parādīts zemāk (vērtības miljonos ar vienu ciparu aiz komata):

Kā tas darbojas?

Iepriekš minētajā formātā nulle pirms komata norāda, ka programmai Excel ir jāparāda viss skaitlis (tieši tā, kā tas ir šūnā), un 0 aiz komata norāda, ka aiz komata ir jāparāda tikai viens nozīmīgs cipars.

Tātad 0,0 vienmēr būtu skaitlis ar vienu ciparu aiz komata.

Pievienojot komatu aiz skaitļa 0,0, tas noskūtīs skaitļa pēdējos trīs ciparus. Piemēram, 123456 kļūs par 123,4 (pēdējie trīs cipari tiek noņemti, un, tā kā aiz komata ir jāparāda 1 cipars, tas parāda 4)

Ja jūs par to padomājat, tas ir līdzīgi skaitļa dalīšanai ar 1000. Tātad, ja vēlaties rādīt skaitli vienībā “miljoni”, jums jāpievieno 2 komati, lai tas noskūtu pēdējos 6 ciparus.

Un, ja arī pēc skaitļa vēlaties parādīt vārdu miljons vai alfabētu M, izmantojiet tālāk norādīto formātu

0.0 ,, "M"