7 hämmästyttävää Excelin mukautetun numeromuodon temppua (sinun on tiedettävä)

Excelin mukautetun numeron muotoilu on Excel -solujen tietojen vaatetus. Voit pukea nämä haluamallasi tavalla. Tarvitset vain vähän tietoa Excelin mukautetun numeromuodon toiminnasta.

Mukautetulla lukumuotoilulla Excelissä voit muuttaa tapaa, jolla solujen arvot näkyvät, mutta pitää alkuperäisen arvon ennallaan.

Voin esimerkiksi asettaa päivämääräarvon näkymään eri päivämäärämuodoissa, kun taas taustalla oleva arvo (päivämäärää edustava luku) pysyisi samana.

Tässä opetusohjelmassa käsitän kaiken mukautetusta numeromuodosta Excelissä ja näytän myös sinulle todella hyödyllisiä esimerkkejä, joita voit käyttää päivittäisessä työssäsi.

Aloitetaan siis!

Excelin mukautetun numeromuodon rakenne

Ennen kuin näytän sen mahtavuuden, haluan selittää lyhyesti, kuinka mukautettu numeroiden muotoilu toimii Excelissä.

Suunnittelun mukaan Excel voi hyväksyä seuraavan tyyppiset tiedot solussa:

• Positiivinen luku
• negatiivinen luku
• 0
• Tekstimerkkijonot (tämä voi sisältää puhtaita tekstimerkkijonoja sekä aakkosnumeerisia merkkijonoja)

Toinen tietotyyppi, jonka Excel voi hyväksyä, on päivämäärät, mutta koska kaikki päivämäärä- ja aika -arvot tallennetaan Excelissä numeroina, ne katetaan osana positiivista lukua.

Excel -laskentataulukon mille tahansa solulle voit määrittää, miten kukin näistä tietotyypeistä näytetään solussa.

Ja alla on syntaksi, jossa voit määrittää tämän muodon.

;;;

Huomaa, että kaikki nämä erotetaan puolipisteillä (;).

Kaikki, mitä kirjoitat Excelin soluun, kuuluisi jompaankumpaan näistä neljästä luokasta, joten sille voidaan määrittää oma muoto.

Jos mainitset vain:

• Yksi muoto: Sitä sovelletaan kaikkiin neljään osaan. Jos esimerkiksi kirjoitat vain Yleistä, sitä sovelletaan kaikkiin neljään osaan.
• Kaksi muotoa: Ensimmäinen koskee positiivisia numeroita ja nollia ja toinen negatiivisia numeroita. Tekstimuodosta tulee oletuksena Yleinen.
• Kolme muotoa: Ensimmäistä käytetään positiivisiin numeroihin, toista negatiivisiin numeroihin, kolmatta nollaan ja teksti katoaa, koska tekstille ei ole määritetty mitään.

Jos haluat lisätietoja mukautetusta numeroiden muotoilusta, suosittelen lämpimästi Office -osiota.

Miten tämä toimii ja mihin nämä muodot syötetään, käsitellään myöhemmin tässä opetusohjelmassa. Muista toistaiseksi, että tämä on minkä tahansa solun muodon syntaksi

Kuinka mukautettu numeromuoto toimii Excelissä

Sitten avaat uuden Excel -työkirjan, oletusarvoisesti kaikki työkirjan laskentataulukoiden solut ovat yleisessä muodossa, jossa:

• Positiiviset luvut näytetään sellaisenaan (tasattu vasemmalle)
• Negatiiviset luvut näytetään miinusmerkillä (ja kohdistettu vasemmalle)
• Nolla näkyy 0 (ja tasattu vasemmalle)
• Teksti näkyy sellaisenaan (tasattu oikealle)

Jos syötät päivämäärän, se näkyy alueellisten asetusten perusteella (muodossa PP-KK-VVVV tai KK-PP-VVVV).

Joten oletuksena solun numeromuoto on:

Yleinen; Yleinen; Yleinen; Yleinen

Jos jokainen tietotyyppi on muotoiltu yleiseksi (ja noudata yllä mainitsemiani sääntöjä sen perusteella, onko se positiivinen luku, negatiivinen luku, 0 vai teksti)

Oletusasetus on nyt kunnossa, mutta miten muutat muotoa. Jos esimerkiksi haluan negatiivisten numeroiden näkyvän punaisena tai haluan, että päivämääräni näkyvät tietyssä muodossa, miten teen sen.

Muoto -pudotusvalikon käyttäminen valintanauhassa

Löydät joitain yleisesti käytettyjä numeromuotoja Numero -ryhmän Aloitus -välilehdestä.

Se on pudotusvalikko, joka näyttää monia yleisesti käytettyjä muotoja, joita voit käyttää soluun valitsemalla solun ja valitsemalla muodon tästä luettelosta.

Jos haluat esimerkiksi muuntaa numerot prosentteina tai muuntaa päivämäärät lyhyeksi päivämäärän muotoksi tai pitkämuotoiseksi päivämäärän muotoksi, voit tehdä sen helposti käyttämällä avattavan valikon vaihtoehtoja.

Format Cells -valintaikkunan käyttäminen

Muotoile solut -valintaikkunat, joissa saat maksimaalisen joustavuuden, kun haluat käyttää muotoja solussa Excelissä.

Siinä on jo paljon valmiita muotoja, joita voit käyttää, tai jos haluat luoda oman mukautetun numeromuodon, voit tehdä sen myös.

Näin avaat Muotoile solut -valintaikkunan;

1. Napsauta Aloitus -välilehteä
2. Napsauta Numero-ryhmässä valintaikkunan käynnistyskuvaketta (pieni kallistettu nuoli ryhmän oikeassa alakulmassa).

Tämä avaa Solujen muotoilu -valintaikkunan. Sieltä löydät kaikki muodot Numero -välilehdeltä (sekä mahdollisuuden luoda mukautettuja solumuotoja)

Voit myös avata solujen muotoilu -valintaikkunan käyttämällä seuraavaa pikanäppäintä:

CONTROL + 1 (kaikki Control -näppäimet ja paina sitten 1 -näppäintä).

Jos käytät Macia, voit käyttää CMD + 1

Otetaan nyt pari käytännön esimerkkiä, joissa luomme omat mukautetut numeromuodot.

Esimerkkejä mukautetun numeron muotoilun käyttämisestä Excelissä

Otetaan nyt pari käytännön esimerkkiä, joissa luomme omat mukautetut numeromuodot.

Piilota solun arvo (tai piilota kaikki arvot solualueella)

Joskus saatat joutua piilottamaan solun sisällön (piilottamatta solua sisältävää riviä tai saraketta).

Käytännön tapaus on tämä, kun luot Excel -koontinäyttöä ja koontinäytössä on pari arvoa, mutta et halua käyttäjien näkevän sitä. joten voit yksinkertaisesti piilottaa nämä arvot

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat piilottaa kaikki numeeriset arvot:

Voit tehdä tämän käyttämällä mukautettua muotoilua Excelissä:

1. Valitse solu tai solualue, jonka solun sisällön haluat piilottaa
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

   ;;;

Miten tämä toimii?

Neljän erityyppisen tietotyypin muoto on jaettu puolipisteillä seuraavassa muodossa:

;;;

Tässä tekniikassa olemme poistaneet muodon ja määrittäneet vain puolipisteen, mikä osoittaa, ettemme halua solujen muotoilua. Tämä on osoitus siitä, että Excel voi piilottaa solussa olevan sisällön.

Huomaa, että vaikka tämä piilottaa solun sisällön, se on edelleen olemassa. joten jos sinulla on piilotettuja numeroita, voit silti käyttää niitä laskelmissa (mikä tekee tästä loistavan tempun Excel -hallintapaneeleille ja -raporteille).

Näytä negatiiviset numerot punaisena

Negatiiviset luvut näkyvät oletuksena miinusmerkillä (tai suluissa joissakin alueellisissa asetuksissa).

Muokattujen numeroiden muotoilun avulla voit muuttaa negatiivisten numeroiden väriä ja näyttää ne punaisina.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat korostaa kaikki negatiiviset arvot punaisella värillä.

Alla on vaiheet tämän tekemiseen:

1. Valitse solualue, jossa haluat näyttää negatiiviset luvut punaisella
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

Yleiset; [Punainen] -Yleistä

Yllä oleva muoto näyttää solut, joissa on negatiivisia arvoja, punaisena.

Miten tämä toimii?

Kun määrität vain kaksi muotoa mukautetuksi numeromuodoksi, ensimmäinen otetaan huomioon positiivisina numeroina ja 0 ja toinen negatiivisissa luvuissa (seuraava arvo on oletusarvoisesti yleinen muoto).

Edellä käyttämässämme muodossa olemme määrittäneet hakasulkeissa värin, jonka haluamme muodon käyttävän.

Joten vaikka negatiiviset numerot ovat yleisessä muodossa, ne näkyvät punaisena miinusmerkillä.

Jos haluat negatiiviset numerot pysähtyä punaisella suluissa (miinusmerkin sijasta), voit käyttää alla olevaa muotoa:

Yleinen; [Punainen] (Yleinen)

Lisää tekstiä numeroihin (kuten miljoonia/miljardeja)

Yksi hämmästyttävä asia mukautetussa numeron muotoilussa on, että voit lisätä numeroon etuliitteen tai jälkiliitteen, mutta säilyttää silti alkuperäisen numeron solussa.

Esimerkiksi "100" voi näkyä "100 miljoonan dollarin" arvona, mutta minulla on silti alkuperäinen arvo solussa, jotta voin käyttää sitä laskelmissa.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat näyttää nämä numerot dollarimerkillä ja sen edessä olevan miljoonan sanan.

Alla on vaiheet tämän tekemiseen:

1. Valitse tietojoukko, johon haluat lisätä tekstin numeroihin
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

$ Yleinen "Miljoona"

Huomaa, että tämä mukautettu numeromuoto vaikuttaa vain numeroihin. Jos kirjoitat tekstiä samaan soluun, se näkyy sellaisenaan.

Miten tämä toimii?

Tässä esimerkissä olen lisännyt yleiseen muotoon sanan miljoona kaksoislainausmerkeissä. kaikki, mitä lisään lainausmerkkeihin muotoon, näkyy solussa.

Lisäsin myös dollarimerkin yleisen muodon eteen, jotta kaikki numeroni osoittavat myös dollarin merkin ennen numeroa.

Huomautus: Jos mietit, miksi dollarimerkki ei ole lainausmerkeissä, kun sana Million on lainausmerkeissä, se johtuu siitä, että Excel tunnistaa dollarin symboliksi, jota käytetään usein mukautettujen numeroiden muotoilussa, eikä vaadi sinua lisätä siihen lainausmerkit. Voit jatkaa lainausmerkkien lisäämistä dollarimerkkiin, mutta kun suljet muotoilusolut -valintaikkunan ja avaat sen uudelleen, huomaat, että Excel on poistanut sen

Naamioi numerot ja teksti

Mukautetulla numeroiden muotoilulla voit arvioida enintään kahta ehtoa ja näiden ehtojen perusteella voit käyttää muotoa soluun.

Oletetaan esimerkiksi, että sinulla on luettelo joidenkin oppilaiden pisteistä ja haluat näyttää tekstin Hyväksytty tai Epäonnistunut pisteiden perusteella (jos alle 35 pisteen pitäisi näyttää "Epäonnistunut", muuten sen pitäisi näyttää "Hyväksytty".

Alla on vaiheet, joissa tulokset näytetään hyväksyttävänä tai epäonnistuneena arvon sijasta käyttämällä mukautettua numeromuotoa:

1. Valitse tietojoukko, jossa sinulla on tulokset
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

   [<35] "Epäonnistunut"; "Hyväksytty"

Alla näet, miltä tietosi näyttäisivät, kun olet käyttänyt yllä olevaa muotoa.

Huomaa, että se ei muuta solujen arvoa. Solun pisteet ovat edelleen numeerisia arvoja, kun ne näyttävät solun arvosta riippuen "Pass" tai "Fail".

Miten tämä toimii?

Tässä esimerkissä ehto on määritettävä hakasulkeissa. mukautettu numeromuotoilu arvioi sitten solun arvon ja käyttää muotoa sen mukaisesti.

Joten jos solun arvo on pienempi kuin 35, numero on piilotettu ja sen sijaan näytetään teksti "Epäonnistunut", joka lisää kaikki muut tapaukset, "Pass".

Samoin, jos haluat muuntaa 0- ja 1 -solualueen TOSIIN ja EPÄTOSIIN, voit käyttää alla olevaa muotoa:

[= 0] "EPÄTOSI"; [= 1] "TOSI"

Muista, että voit käyttää vain kahta ehtoa mukautetussa numeroiden muotoilussa. Jos yrität käyttää useampaa kuin kahta ehtoa, niin kehote ilmoittaa sinulle, että se ei voi hyväksyä kyseistä muotoa.

Piilota teksti, mutta näytä numerot

Joskus, kun lataat tietoja tietokannoista, koska tyhjiä on, ne täytetään arvoilla, kuten Ei saatavilla tai Ei käytettävissä tai -

Mukautetulla numeroiden muotoilulla voit piilottaa kaikki tekstiarvot pitäen numerot ennallaan. tämä varmistaa myös, että alkuperäiset tiedot ovat ehjät (koska et poista tekstiarvoa, piilotat sen vain)

Oletetaan, että sinulla on alla näkyvä tietojoukko, johon haluat piilottaa kaikki tekstiarvot:

Alla on vaiheet tämän tekemiseen:

1. Valitse tietojoukko, jossa sinulla on tiedot
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

Yleinen; -Yleistä; Yleinen;

Huomaa, että olen käyttänyt numeroille yleistä muotoa. Voit muotoilla minkä tahansa haluamasi muodon (kuten 0, 0. #, #0.0%). Huomaa myös, että ennen toista yleistä muotoa on negatiivinen merkki (-), koska se edustaa negatiivisten numeroiden muotoa.

Miten tämä toimii?

Tässä esimerkissä, koska halusimme piilottaa kaikki tekstiarvot, emme yksinkertaisesti määrittäneet sen muotoa.

Ja koska määritimme muodon muille kolmelle tietotyypille (positiivinen luku, negatiivinen luku ja nolla), Excel ymmärsi, että olemme jättäneet tarkoituksellisesti tekstiarvon muodon tyhjäksi, eikä sen pitäisi näyttää mitään, jos solussa on teksti merkkijono.

Näytä numerot prosentteina (%)

Mukautetulla numeroiden muotoilulla voit myös muuttaa numeroita ja näyttää ne prosentteina.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat muuntaa nämä luvut näyttämään vastaavan prosenttiosuuden:

Alla on vaiheet tämän tekemiseen:

1. Valitse solut, joissa on numerot (jotka haluat muuntaa prosentteina)
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

0%

Tämä muuttaa luvut prosentteiksi. Esimerkiksi se muuttuu 0,11: stä 11%: iin.

Jos kaikki mitä haluat tehdä, on muuntaa luku vastaavaksi prosenttiarvoksi, parempi tapa tehdä tämä on käyttää prosenttiasetusta solujen muotoilu -valintaikkunassa.

Mukautetusta muotoilusta voi olla hyötyä silloin, kun haluat enemmän hallita sitä, miten haluat näyttää prosentuaalisen arvon.

Jos haluat esimerkiksi näyttää lisätekstiä prosentteina, kaikki negatiiviset prosenttiosuudet punaisena, on parempi käyttää mukautettua muotoilua

Näytä numerot eri yksiköinä (miljoonia miljardeina tai grammoina kilogrammoina)

Jos käytät suuria määriä, saatat haluta näyttää ne eri yksikössä. jos sinulla on esimerkiksi joidenkin yritysten myyntitiedot, voit halutessasi näyttää myynnin miljoonina tai miljardeina (säilyttäen kuitenkin alkuperäisen myyntiarvon soluissa)

Onneksi voit tehdä sen helposti mukautetulla numeroiden muotoilulla.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat näyttää nämä myyntiarvot miljoonina.

Alla on vaiheet tämän tekemiseen:

1. Valitse solut, joissa on numerot
2. Avaa Muotoile solut -valintaikkuna (pikanäppäin Control + 1)
3. Napsauta Numero -välilehdessä Mukautettu
4. Kirjoita Tyyppi -kenttään seuraava muoto:

0.0,,

Yllä oleva antaa sinulle alla olevan tuloksen (arvot miljoonina yhden numeron desimaalin jälkeen):

Miten tämä toimii?

Yllä olevassa muodossa nolla ennen desimaalia osoittaa, että Excelin pitäisi näyttää koko luku (täsmälleen sellaisena kuin se on solussa), ja 0 desimaalin jälkeen osoittaa, että sen pitäisi näyttää vain yksi merkittävä numero desimaalin jälkeen.

Joten 0,0 olisi aina numero, jossa on yksi numero desimaalipilkun jälkeen.

Kun lisäät pilkun 0,0: n jälkeen, se ajelee numeron viimeisen kolmen numeron. Esimerkiksi numerosta 123456 tulee 123,4 (kolme viimeistä numeroa poistetaan, ja koska sen on näytettävä yksi numero desimaalipilkun jälkeen, se näyttää 4)

Jos ajattelet sitä, se on samanlainen kuin luvun jakaminen 1000: lla. Joten jos haluat näyttää luvun miljoonien yksikköinä, sinun on lisättävä 2 pilkkua, jotta se ajaisi pois viimeiset 6 numeroa.

Ja jos haluat myös näyttää sanan miljoona tai aakkoset M numeron jälkeen, käytä alla olevaa muotoa

0,0 ,, "M"