God ettermiddag, kjære leser!

I artikkelen ønsker jeg å vurdere spørsmålet om hvordan og på hvilke måter det er mulig å skille teksten i en celle som tilfeldigvis sitter sammen! Hvis du ofte jobber med importerte data i Excel, støter du med jevne mellomrom på problemer som utlastede data, feil dataformat, fastlåste ord eller verdier og mye mer. I dette tilfellet gir Excel flere alternativer for å normalisere data, og hver av dem har sine egne fordeler og ulemper.

Du kan analysere den sammenslåtte teksten til de nødvendige komponentene ved å bruke:

Veiviser for tekstanalyse

La oss se på den enkleste måten å dele tekst i en celle, ikke etter essens, men etter utførelse. Dette kan for eksempel tydelig demonstreres ved å bruke ditt fulle navn i én celle, som for enkelhets skyld må deles inn i separate kolonner.

For å fullføre oppgaven, kall opp "Tekstveiviser (parsing)"-dialogboksen og separer teksten i 3 trinn:

1. Først må du velge dataene som skal deles, neste trinn er på "Data"-fanen i delen "Arbeid med data", klikk på "Tekst etter kolonner"-ikonet og i veiviserdialogboksen som åpnes, spesifiser formatet på arbeidsteksten. Vi velger 2 typer formater:

• Generell– gjør ikke endringer i dataene, og etterlater dem i sin opprinnelige form, som vil være det optimale valget i de fleste tilfeller;
• Tekst – dette formatet, er hovedsakelig nødvendig for kolonner med numeriske verdier, som programmet nødvendigvis må tolke som tekst. (Dette er for eksempel tall med tusenskille eller et plastkortnummer);
• Dato– dette formatet brukes for kolonner med datoer, formatet på selve datoen kan velges i rullegardinlisten.

Hvis du bruker tegn som ikke ligner standardene som er tildelt i de regionale innstillingene, kan du bruke "Flere detaljer"-knappen for å gjenkjenne dem riktig.

Koble fra tekst ved hjelp av formler

For denne metoden trenger vi mulighetene for å kombinere SEARCH- og PSTR-funksjonene. Med hjelp vil vi se etter alle mellomrom som finnes mellom ord (for eksempel mellom etternavn, fornavn og patronym). Deretter velger vi det nødvendige antallet tegn for ytterligere separasjon.

Og hvis det med de to første ordene er klart hva og hvordan man skal skille, så skillet for siste ord nei, noe som betyr at du må spesifisere betinget som et argument et stort nummer av tegn, som "antall_tegn"-argumentet for PSTR-funksjonen, for eksempel 100, 200 eller mer.

La oss nå se på dannelsen av en formel for å skille tekst i en celle trinn for trinn:

• Først må vi finne to mellomrom som skiller ordene våre, for å finne det første rommet trenger vi formelen: =SØK(" ";B2;1), og for det andre: =SØK(" ";B2;C2+ 1) ;
• For det andre bestemmer vi hvor mange tegn som må utheves i linjen. Siden vi allerede har bestemt posisjonene til separatoren, vil vi ha ett tegn mindre å skille. Dette betyr at vi vil bruke PSTR-funksjonen til å fjerne ord fra cellen ved å bruke resultatet av den forrige formelen som argumentet "antall tegn". For å bestemme det første ordet (Etternavn), trenger du formelen: =PSTR(B2,1,SØK(" ";B2,1)), for å bestemme den andre verdien (Navn): =PSTR(B2,SØK(" ",B2,1) +1;SØK(" ";B2;SØK(" ";B2;1)+1) -SØK(" ";B2;1)), og la oss nå bestemme den siste verdien (Mellomnavn ): =PSTR(B2;SØK( " ";B2;SØK(" ";B2;1)+1)+1;100).

Som et resultat delte vi det fulle navnet i tre ord, noe som vil tillate oss å jobbe med dem effektivt.

Hvis du deler verdien i en celle i to deler, leter du bare etter ett mellomrom (eller annen skilletegn), men for å dele mer enn 4 ord, legg til de nødvendige skilletegnene i formelen.

Trekke ut ord ved hjelp av makroerVBA

La oss se på to måter å dele tekst i en celle:

1. Vi trekker ut enkeltord med skilletegn;
2. Del teksten uten mellomrom.

Metode nummer 1.

Siden du er interessert i automatisk tekstdeling, må du skrive en god funksjon i VBA og implementere den i arbeidsbok. For å begynne, gå til "Utvikler"-fanen og velg "Visual Basic" eller ring denne funksjonen ved å bruke Alt+F11. (mer detaljer i artikkelen "").

Vi skaper ny modul I "Sett inn"-menyen velger du elementet "Modul" og overfører koden nedenfor til den:

Funksjon Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) Hvis n > 0 Og n - 1<= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function

Funksjon Delstreng (Txt, Delimiter, n) Som streng Dim x Som variant x = Splitt(Txt, skilletegn) Hvis n > 0 og n - 1<= UBound (x ) Then Delstreng = x(n - 1) Ellers Understreng = "" Slutt om Avslutt funksjon

Denne funksjonen har nå dukket opp i funksjonsveiviseren under Brukerdefinert. Syntaksen til den opprettede funksjonen er veldig enkel og ser slik ut:

SUBSTRING (_Txt_;_Delimeter_;_n_), hvor:

• – angi cellen teksten der vi skal dele;
• – dette er et tegn som fungerer som skilletegn (komma, bindestrek, mellomrom osv.);
• – sett tallet i rekkefølgen til fragmentet som vi skal trekke ut.

Eksempel:

La oss se på hvordan det er mulig å trekke ut tekst basert på ulike skilletegn. For enkelhets skyld og bedre automatisering, ikke glem betydningen av formelen.
Metode nr. 2.

Noen ganger er det alternativer når du, når du laster ned data fra andre kilder, kan få en stor tekst fra ord limt sammen (for eksempel Orlov Stepan Fedorovich), slik tekst må skilles med mellomrom i dens bestanddeler.

En liten funksjon vil hjelpe med dette, som automatisk kan sette inn et "mellomrom" før alle store bokstaver i teksten. Bruk Visual Basic, lag en ny modul som beskrevet i den første metoden og legg til følgende kode til den:

Funksjon CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Som "" Og Mid(Txt, i + 1, 1) Som "" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End-funksjon

Funksjon CutWords (Txt As Range ) Som streng Dim ut $ Hvis Len(Txt) = 0, avslutter du funksjonen Ut = Midt(Txt, 1, 1) For i = 2 til Len (Txt) Hvis Mid (Txt , i , 1 ) Liker "" Og Mid (Txt , i + 1 , 1 ) Liker "" Da

Divisjon er en av de fire vanligste aritmetiske operasjonene. Det er sjelden komplekse beregninger som kan klare seg uten. Excel har et bredt spekter av funksjonalitet for å bruke denne aritmetiske operasjonen. La oss finne ut hvordan du kan utføre divisjon i Excel.

I Microsoft Excel kan divisjon gjøres både ved hjelp av formler og ved hjelp av funksjoner. Utbytte og divisor er tall og celleadresser.

Metode 1: Del et tall med et tall

Et Excel-ark kan brukes som en slags kalkulator, bare å dele ett tall med et annet. Divisjonstegnet er en skråstrek (omvendt skråstrek) - «/» .

Etter dette vil Excel beregne formelen og vise beregningsresultatet i den angitte cellen.

Hvis en beregning utføres med flere tegn, utføres rekkefølgen av deres utførelse av programmet i henhold til matematikkens lover. Det vil si at først og fremst utføres divisjon og multiplikasjon, og først deretter addisjon og subtraksjon.

Som du vet er divisjon med 0 en feil handling. Derfor, når du prøver å gjøre en lignende beregning i Excel, vil resultatet vises i cellen "#DIV/0!".

Metode 2: Dele celleinnhold

Du kan også dele data i celler i Excel.

Du kan også kombinere ved å bruke både celleadresser og statiske tall som utbytte eller divisor.

Metode 3: Del kolonne etter kolonne

Beregning i tabeller krever ofte at verdiene til en kolonne deles med dataene i den andre kolonnen. Selvfølgelig kan du dele verdien av hver celle på samme måte som angitt ovenfor, men denne prosedyren kan gjøres mye raskere.

Som du kan se, etter denne handlingen vil prosedyren for å dele en kolonne med den andre være fullstendig fullført, og resultatet vises i en egen kolonne. Faktum er at fyllmarkøren brukes til å kopiere formelen inn i de nedre cellene. Men med tanke på det faktum at alle koblinger som standard er relative og ikke absolutte, endres celleadressene i formelen i forhold til de opprinnelige koordinatene når du beveger deg nedover. Og det er akkurat dette vi trenger for en konkret sak.

Metode 4: Del en kolonne med en konstant

Det er tider når du trenger å dele en kolonne med det samme konstante tallet, og vise summen av divisjonen i en egen kolonne.

Som du kan se, ble også delingen denne gangen utført korrekt. I dette tilfellet, når du kopierte data med en fyllmarkør, forble koblingene igjen relative. Utbytteadressen for hver linje ble automatisk endret. Men divisor i dette tilfellet er et konstant tall, noe som betyr at relativitetsegenskapen ikke gjelder for den. Dermed delte vi innholdet i kolonnecellene med en konstant.

Metode 5: Dele en kolonne etter celle

Men hva skal du gjøre hvis du trenger å dele en kolonne inn i innholdet i én celle. Tross alt, i henhold til prinsippet om relativitet av referanser, vil koordinatene til utbyttet og divisoren skifte. Vi må gjøre adressen til cellen med divisor fast.

Etter dette er resultatet for hele kolonnen klart. Som du kan se, ble kolonnen i dette tilfellet delt inn i en celle med en fast adresse.

Metode 6: PRIVAT funksjon

Divisjon i Excel kan også utføres ved hjelp av en spesiell funksjon kalt PRIVAT. Det særegne ved denne funksjonen er at den deler seg, men uten en rest. Det vil si at når du bruker denne divisjonsmetoden, vil resultatet alltid være et heltall. I dette tilfellet utføres avrunding ikke i henhold til allment aksepterte matematiske regler til nærmeste heltall, men til et mindre i absolutt verdi. Det vil si at funksjonen vil runde tallet 5,8 ikke til 6, men til 5.

La oss se bruken av denne funksjonen ved å bruke et eksempel.

Etter disse trinnene funksjonen PRIVAT behandler dataene og produserer en respons i cellen som ble spesifisert i det første trinnet i denne delingsmetoden.

Denne funksjonen kan også legges inn manuelt uten å bruke veiviseren. Syntaksen ser slik ut:

KVANTIER(teller;nevner)

Som du kan se, er hovedmetoden for deling i Microsoft Office-programmet bruken av formler. Divisjonssymbolet i dem er en skråstrek - «/» . Samtidig kan du for visse formål bruke funksjonen i delingsprosessen PRIVAT. Men du må ta hensyn til at når du beregner på denne måten, oppnås forskjellen uten en rest, som et heltall. I dette tilfellet utføres avrunding ikke i henhold til generelt aksepterte normer, men til et mindre heltall i absolutt verdi.

Det er ofte nødvendig å optimalisere datastrukturen etter import til Excel. Noen forskjellige verdier faller inn i samme celle, og danner en hel rad som én verdi. Spørsmålet oppstår: hvordan dele en rad i celler i Excel. Programmet har forskjellige søkefunksjoner: noen søker etter celler, andre søker etter innholdet i cellene. Når alt kommer til alt, er det også et vanlig behov for Excel-brukere å søke etter en tekststreng i en celle. Vi vil bruke dem til å skille linjer.

Hvordan dele tekst i to Excel-celler

La oss si at data ble importert til et Excel-ark fra et annet program. På grunn av inkompatibel datastruktur under import, ble noen verdier fra forskjellige kategorier lagt inn i én celle. Det er nødvendig å skille heltalls numeriske verdier fra denne cellen. Et eksempel på slike feilimporterte data er vist i figuren nedenfor:

Først, la oss definere et mønster som vi kan bestemme at data er fra forskjellige kategorier, til tross for at de er i samme rad. I vårt tilfelle er vi kun interessert i tallene som står utenfor hakeparentesene. Hvordan kan du raskt velge heltall fra strenger og plassere dem i separate celler? En effektiv løsning er en fleksibel formel basert på tekstfunksjoner.

I celle B3 skriver du inn følgende formel:

Kopier nå denne formelen langs hele kolonnen:

Velge tall fra strenger i separate celler.



Beskrivelse av formelen for å dele tekst inn i celler:

PSTR-funksjonen returnerer en tekstverdi som inneholder et visst antall tegn i en streng. Funksjonsargumenter:

1. Det første argumentet er en referanse til cellen med kildeteksten.
2. Det andre argumentet er plasseringen til det første tegnet der den delte strengen skal begynne.
3. Det siste argumentet er antall tegn den delte strengen skal inneholde.

Alt er klart med det første argumentet til PSTR - dette er en lenke til celle A3. Vi beregner det andre argumentet ved å bruke funksjonen FINN("]";A3)+2. Den returnerer det neste tegnnummeret i den første avsluttende hakeparentesen i strengen. Og til dette tallet legger vi til et annet nummer 2, siden vi trenger tegnnummeret etter mellomrommet bak hakeparentesen. I det siste argumentet beregner funksjonen hvor mange tegn den delte strengen vil inneholde etter splitting, tatt i betraktning plasseringen av hakeparentesen.

Merk! At i vårt eksempel har alle original- og delte linjer forskjellig lengde og forskjellig antall tegn. Derfor kalte vi denne formelen fleksibel i begynnelsen av artikkelen. Det er egnet for alle forhold når du løser slike problemer. Det som gir den fleksibilitet er en kompleks kombinasjon av FINN-funksjoner. Brukeren av formelen trenger bare å bestemme mønsteret og angi dem i funksjonsparametrene: disse vil være firkantede parenteser eller andre skilletegn. Dette kan for eksempel være mellomrom hvis du skal dele en linje i ord osv.

I dette eksemplet tar FINN-funksjonen sitt andre argument for å bestemme posisjonen i forhold til den første avsluttende parentesen. Og i det tredje argumentet beregner den samme funksjonen posisjonen til teksten vi trenger på linjen i forhold til den andre åpne hakeparentesen. Beregningen i det tredje argumentet er mer kompleks og innebærer å trekke en større tekstlengde fra en mindre. Og for å ta hensyn til 2 mellomrom til, må du trekke fra tallet 3. Som et resultat får vi riktig antall tegn i den delte linjen. Ved å bruke en så fleksibel formel kan du velge forskjellige lengder på delt tekst fra forskjellige lengder på kildestrenger.

Ringer kommandoen:
-gruppe Celler/områder -Områder -Delt opp etter rader/kolonner

Denne kommandoen deler dataene i alle valgte celler i deler ved å bruke det angitte tegnet/tegnene som skilletegn. Hovedfunksjonen er at, i motsetning til standard Excel-kommandoen "Del opp i kolonner", legger denne kommandoen inn resultatet av å dele data i rader eller kolonner i én rad/kolonne, og ikke hver del i sin egen kolonne. I tillegg, i standard Excel-kommandoen er det ingen måte å spesifisere flere tegn - bare ett.

Rekkevidde av verdier- indikerer celleområdet hvis data må deles. Kan enten være en enkelt kolonne/rad eller et område som består av flere rader og flere kolonner.
Avgrensning- symbolet er indikert (eller gruppe med tegn), som vil bli brukt som en separator. Hvis skillet ikke er spesifisert, vises en melding som informerer deg om dette. Meldingen vil be deg om å bruke standardskilletegn (Rom) . Ved avslag vil du kunne spesifisere nødvendig skilletegn. Tidligere innlagte data vil ikke gå tapt.

Plasseringsresultat:

Resultatskilletegn:
Bare tilgjengelig hvis én celle er spesifisert som resultatutdata. Standard skilletegn er linjeskift. Du kan spesifisere et hvilket som helst tegn eller gruppe med tegn som skilletegn.

Transponer- Hvis Rekkevidde av verdier er en kolonne, vil dataene etter behandlingen bli plassert i én rad. Hvis Rekkevidde av verdier- rad, så vil dataene bli plassert i kolonnen. Ikke tilgjengelig når resultatet vises i én celle.

Et regneark i Microsoft Excel er i seg selv en stor tabell som består av celler som er tildelt en bestemt adresse (et tall som tilsvarer en rad og en bokstav som tilsvarer en kolonne). På denne adressen kan du få tilgang til dataene, det vil si å bruke dem i formler. Følgelig vil det ikke fungere å dele en celle direkte, som vi sier i Word. Fordi adressen A5 ikke blir A/1-5 og A/2-5. Så hva kan gjøres?

Hvis du ennå ikke har opprettet en tabell og dataene ikke er lagt inn, må du først tenke gjennom alt godt, og deretter slå sammen bestemte celler, slik at de for eksempel bare er atskilt i overskriften eller i en rad.

Men la oss fortelle deg i denne artikkelen hva du skal gjøre hvis bordet er klart. La oss finne ut hvordan du deler en celle i Excel i to eller flere horisontalt og vertikalt, så vel som diagonalt.

For eksempel vil vi ha et bord som dette. I den må du også angi produsenten etter navnet.

Siden nye kolonner legges til til venstre for det gjeldende utvalget, velger jeg en hvilken som helst verdi i kolonne C og setter inn kolonner på fanen Hjem.

Så, etter navnet, dukket det opp en annen vertikal kolonne, som jeg vil kalle Produsent.

Siden frukt kan bringes fra forskjellige land, må du lage flere motsatte av hver og en i den tilføyde kolonnen fra en celle.

La oss starte med Apple. Velg blokken rett under dette navnet, høyreklikk og velg "Sett inn" fra kontekstmenyen.

Merk "linjen" med en markør i boksen og klikk "OK".

En ekstra linje kommer på stedet vi trenger. På samme måte legges det til flere linjer hvis det er flere produsenter.

La oss nå slå sammen cellene i de kolonnene der dataene ikke endres. I eksemplet er disse Antall, Navn og Antall. Først må du velge dem, klikk deretter på "Slå sammen" -knappen og velg "Celler".

Du kan lese om ulike måter å kombinere celler i Excel i den tilsvarende artikkelen.

Fyll ut feltene Produsent og Pris med data.

Som du kan se, ved å legge til rader og slå sammen visse celler, oppnådde vi at vi i ønsket kolonne klarte å dele dem horisontalt i flere rader. Dermed har Apple flere forskjellige produsenter og forskjellige priser.

Du kan gjøre det samme med Pear. Vi legger til rader, kombinerer hvor Number og Name er, og bare to rader i Quantity-kolonnen (siden verdien vil være den samme). Så legger vi inn dataene og får en slags delt celle i feltet Produsent og Pris.

Vær oppmerksom på at i sammenslåtte blokker må du angi adressen øverst til venstre i formelen. For eksempel, fra D2:D3 blir det D2, og fra B4:B6 blir det B4.

La oss nå se på hvordan du deler en celle i 2 eller flere vertikalt. Dette er nødvendig hvis du vil legge til flere undernavn til kolonnenavnet, og først da fylle dem med data.

La oss for eksempel dele kolonnen Antall. Fra den andre raden, velg hvilken som helst udelt celle, det kan være C2 eller E2. Ring opp kontekstmenyen og sett inn en ekstra linje.

Bruk den aktuelle knappen, kombiner D1 og E1, og plasser verdien i midten.

På denne snedige måten delte vi cellen i kolonnen under ordet Mengde og ga dem navnene Importert og Solgt. Der solgt, har jeg fortsatt slått sammen celler med en verdi, hvis den ikke passer, trenger jeg bare å kansellere den ved å velge riktig element.

Som et resultat mottok vi en tabell der cellene på de riktige stedene er delt inn i flere rader eller kolonner. Og vi gjorde dette ikke ved å dele, men ved å legge til horisontale og vertikale rader, etterfulgt av å kombinere noen blokker.

For å dele diagonalt, velg cellen, åpne kontekstmenyen ved å høyreklikke og klikk "Formater".

Den valgte blokken vil bli delt.

Du kan gjøre det annerledes. Øverst åpner du "Sett inn", klikker på "Illustrasjoner" - "Former" og velger en rett linje fra dem.

I ønsket celle tegner du den diagonalt fra ett hjørne til et annet. Hvis det, som i eksempelet, er feil farge, så på fanen "Tegneverktøy" velg ønsket enten fra ferdige stiler, eller ved å klikke på "Shape Outline".

blåtann