[sv-users] Re: [sv-users] BYT.UT / SÖK - funktion!

Den 2 februari 2013 00:19 skrev Jan Öhman <Jan_Ohman@glocalnet.net>:
> Tack för Era svar!
>
> Håller på att göra ett av mina kalkylblad för att underlätta för andra.
> Det är ett prisfält som jag just nu blickar på.
>
> På flik "1" skriver personal in eller kopierar in ett pris i 2st kolumner
> (inköp och försäljningspris).
> Min önskan är inte att fokusera på prisets layout utan på prisets storlek.
>
> Hur priset ser ut vid inmatning, kan se väldigt olika ut. Nedan följer några
> exempel, som jag träffat på vid olika tester.
> 123,50 kr
> 80.74
> 45,50
> 36,45 .-
> 78.50 :-
> Med eller utan "blanksteg" mellan tal och krontecken.
>
> Resultatet kommer på flik "3"
> Med ovanstående invärden skulle jag vilja få ett resultat med följande
> innehåll och format.
> (observera att det är decimal punkt)
> 123.50 eller 123.5
> 80.74
> 45.50 eller 45.5
> 36.45
> 78.50 eller 78.5
>
> dvs.
> 1) Jag vill plocka bort alla olika förekommande krontecken som i ovanstående
> exempel är "kr", ".-"; ":-"
> Att radera tecknet ":-" fungerar nu med formeln  (Bra idé med "ÄRFEL")
> =OM(ÄRFEL(SÖK(":-";'Tabell1'.B3;1));'Tabell1'.B3;BYT.UT('Tabell1'.B3;":-";""))
> Dock blir det en oönskad "nolla" om C6 inte innehåller något. (går säkert
> att lösa om man ytterligare tänker till.)
> Att radera alla olika krontecken blir en ganska komplicerad formel.
>
> 2) Byta ut alla "komma" till "punkt"
> Förslagsvis med formeln
> =OM('Tabell1'.B3<>"";BYT.UT('Tabell1'.B3;",";".");"")
>
> 3) Plocka bort alla ev omgivande blanksteg runt priset.
> Här hade jag tänkt använda mig av funktionen
> =RENSA(C6)
>
> När alla ovanstående formler ska slås ihop, blir det en ganska komplicerad
> formel för att utföra våra önskemål.
> Är det enda sättet att lösa mitt problem?

Så länge du tillåter folk att klistra in priser hur som helst, så blir
det nog så, ja. Ja, du kan ju läsa det med ett makro förstås, som
söker upp alla klant-inmatningar och rättar till dem, så blir allt
annat jobb så mycket enklare.

Men om du nu envisas med att låta folk göra lite som de vill, så
verkar detta gå att åstadkomma med en gigantisk formel. Har testat den
på lite olika värden, men kan ändå inte garantera att den funkar på
allt.

Ett enkelt sätt att skiva långa formler är att först dela upp dem. Så
här gjorde jag:

Testvärden i kolumn A, jag kopierade dina exempel för att verifiera
att åtminstone dessa fungerar.
Sedan gäller det att få formeln så generell som möjligt.
Det första jag gjorde var att leta upp förekomsten av siffror.
B1: =SÖK("[:digit:]+";A1)
Eller =SÖK("[0-9]+";A1)
Eftersom den sista är lite kortare, väljer vi den.
Resultatet blir den position första siffran hittas. Den kommer vi att
använda senare.

Nu vill vi veta var första förekomsten av ett ogiltigt tecken kan
hittas. Ogiltiga tecken är ju alla som inte är siffra, komma eller
punkt.
C1: =SÖK("[^0-9\.,]+";A1;B1+1)
Vi söker efter ICKE-siffra, ICKE-punkt och ICKE-komma i A1, en eller
flera till antalet (+). Vi börjar att söka i tecknet efter första
giltiga siffra, därav ”B1+1”, så om siffra hittades som första tecken
kommer sökningen efter första ogiltiga tecken att börja på position 2.
Detta för att inte hitta oönskade tecken INNAN första giltiga siffra.

Om inga ogiltiga tecken skulle hittas, får vi ett felmeddelande. Det
vill vi inte ha. Vad vi vill ha är istället positionen efter sista
tecknet, eftersom alla tecken verkar vara giltiga:
D1: =LÄNGD(A1)+1

Så om C1 blev felmeddelande, visa istället D1.
E1: =OM(ÄRFEL(C1);D1;C1)

Nu kan vi ringa in de giltiga tecknen:
F1: =EXTEXT(A1;B1;E1-B1)

Om A1 nu var tom, får vi ett felmeddelande, och det vill vi ju inte ha.
G1: =OM(ÄRFEL(F1);"";F1)

Sista steget blir då att byta ut eventuella förekomster av komma mot punkt.
H1: =BYT.UT(G1;",";".")

Nästa steg är nu att ersätta alla dessa småformler med  en enda
formel, så då blir fruktansvärt lång och väldigt långsam, i och med
att många saker, räknas ut väldigt många gånger, vilket är väldigt
onödigt men går inte att komma runt om man inte vill göra ett makro
eller dela upp det på delformler som vi redan gjort här.

Enklast är att gå steg för steg här, baklänges. Vad vi vill ha är en
formel som endast refererar till A1. Således måste vi göra något åt
H1, där referens sker till G1. Byt dörför ut texten G1 mot formeln i
cell G1:
=BYT.UT(OM(ÄRFEL(F1);"";F1);",";".")

Enklast är att kopiera innehållet i G1 med Ctrl+c (utelämna =-tecknet)
och sedan klistra in det över texten G1 i H1, Kontrollera att
innehållet i cellen inte ändrades. Kopiera nedåt för att se att
innehållet inte ändras för övriga testrader heller. Gör denna procedur
för varje förändring, så att vi inte får följdfel som är svåra att
spåra senare… Ju längre formeln blir desto mer motiverat är det att
istället använda Sök/Ersätt för att byta ut exempelvis alla
förekomster av B1 mot B1:s innehåll i sista steget. Då minskar ju
risken att du missar något utbyte.

Okej, inte heller F1 är något vi vill referera till, så vi byter ut
alla förekomster av F1 mot innehållet i F1:
=BYT.UT(OM(ÄRFEL(EXTEXT(A1;B1;E1-B1));"";EXTEXT(A1;B1;E1-B1));",";".")

Forsätt på samma sätt med E1, D1, C1 och B1. Det blir då, steg för steg:
=BYT.UT(OM(ÄRFEL(EXTEXT(A1;B1;OM(ÄRFEL(C1);D1;C1)-B1));"";EXTEXT(A1;B1;OM(ÄRFEL(C1);D1;C1)-B1));",";".")

=BYT.UT(OM(ÄRFEL(EXTEXT(A1;B1;OM(ÄRFEL(C1);LÄNGD(A1)+1;C1)-B1));"";EXTEXT(A1;B1;OM(ÄRFEL(C1);LÄNGD(A1)+1;C1)-B1));",";".")

=BYT.UT(OM(ÄRFEL(EXTEXT(A1;B1;OM(ÄRFEL(SÖK("[^0-9\.,]+";A1;B1+1));LÄNGD(A1)+1;SÖK("[^0-9\.,]+";A1;B1+1))-B1));"";EXTEXT(A1;B1;OM(ÄRFEL(SÖK("[^0-9\.,]+";A1;B1+1));LÄNGD(A1)+1;SÖK("[^0-9\.,]+";A1;B1+1))-B1));",";".")

=BYT.UT(OM(ÄRFEL(EXTEXT(A1;SÖK("[0-9]+";A1);OM(ÄRFEL(SÖK("[^0-9\.,]+";A1;SÖK("[0-9]+";A1)+1));LÄNGD(A1)+1;SÖK("[^0-9\.,]+";A1;SÖK("[0-9]+";A1)+1))-SÖK("[0-9]+";A1)));"";EXTEXT(A1;SÖK("[0-9]+";A1);OM(ÄRFEL(SÖK("[^0-9\.,]+";A1;SÖK("[0-9]+";A1)+1));LÄNGD(A1)+1;SÖK("[^0-9\.,]+";A1;SÖK("[0-9]+";A1)+1))-SÖK("[0-9]+";A1)));",";".")

Radera nu kolumnerna B-G för att verifiera att vi har en formel som
endast refererar till A. Har vi missat något, så kommer detta steg att
visa det tydligt.

Denna absurda formel borde nu kunna lösa ditt problem…

Observera att detta inte är idiotsäkert. Exempelvis har vi inte kollat
hur många punkter och kommatecken som förekommer, så ”85,3.28...147,,”
kommer att godkännas och sväljas med hull och hår. Detta går givetvis
att åtgärda, men tycker vi inte att formeln är tillräckligt lång
redan? Det är mycket enklare att inte utgå från att folk är idioter…

Men egentligen är det ju klart mycket bättre att försöka fixa felet
vid roten istället, det vill säga vid själva inmatningen av data.
Exempelvis kan man kanske göra ett enkelt makro som gör en enkel
Sök/Ersätt i inmatningskolumnen som automatiskt körs när en cell
ändrat värdet eller när användaren klickar på en knapp eller liknande.
Man slipper då ovanstående fruktansvärt långsamma formel. Den kanske
inte känns långsam när den bara förekommer några gånger, men kopierar
man den till några hundra celler kommer man nog att märka det, även på
snabba datorer.


Johnny Rosenberg

> //Jan
>
> Johnny Rosenberg skrev 2013-02-01 17:51:
>
> > Den 1 februari 2013 17:41 skrev Johnny Rosenberg <gurus.knugum@gmail.com>:
> > > Den 1 februari 2013 10:40 skrev Jan Öhman <Jan_Ohman@glocalnet.net>:
> > > > Hej!
> > > >
> > > > Mitt önskemål:
> > > > Jag vill söka och radera det sökta värdet om förekomst av olika
> > > > textkombinationer finns (t.ex. ":-" eller ".-" osv.)
> > > >
> > > > Hade tänkt använda "BYT.UT" för att utföra borttagningen, men kommer
> > > > inte på
> > > > om det går att skriva flera funktioner efter varandra...
> > > > Därför hade jag tänkt börja med OM och SÖK och vid "träff" BYT.UT (på
> > > > något
> > > > sätt)
> > > >
> > > > Men...
> > > > _______________________________________________
> > > > Antag
> > > > i cell B2 finns talet / texten "123" samt
> > > > i cell B3 finns talet / texten "123x".
> > > >
> > > > i cell C2 finns formeln "=SÖK("x";B2;1)" och
> > > > i cell C3 finns formeln "=SÖK("x";B3;1)".
> > > >
> > > > Resultatet för mig blir i C2 "#VÄRDE!" och i C3 "4"
> > > > Jag hade hoppats på att få resultatet "0" i C2, men så blev det inte.
> > > >
> > > >
> > > > Hur löser man detta?
> > >
> > > =OM(ÄRFEL(SÖK("x";B2;1));0;SÖK("x";B2;1))
> > >
> > > Nackdelen är att ”SÖK("x";B2;1)” räknas ut två gånger i värsta fall,
> > > vilket inte är så lyckat om man har tusentals rader av detta slag (det
> > > blir onödigt segt).
> > >
> > >
> > > Men allvarligt talat, varför gör du inte bara en vanlig SÖK/ERSÄTT
> > > (Ctrl+h)?
> > > Är det mer komplicerade saker du vill söka upp och ersätta, kan
> > > reguljära uttryck vara till hjälp.
> > >
> > > Exempelvis, för att ta bort alla förekomster (på ett blad eller i ett
> > > markerat område), välj reguljära uttryck och fyll i fälten enligt
> > > följande:
> > > Sök: (.*)\.-$|(.*):-$
> >
> > Det gick visst att använda | för en del av uttrycket också, inte bara
> > för hela. Då blir det lite kortare:
> > (.*)(\.-|:-)$
> >
> > Okänt antal valfria tecken följt av .- ELLER :- och därefter ett radslut.
> > Vill man specificera att de okända tecknen framför måste vara siffror,
> > finns flera sätt:
> > ([0123456789]*)(\.-|:-)$
> > är samma sak som
> > ([0-9]*)(\.-|:-)$
> > är samma sak som
> > ([:digit:]*)(\.-|:-)$
> >
> > [0-9] betyder ETT tecken från 0 till och med 9. Asterisken (*) efter
> > ändrar antalet till ”0 eller flera”
> > Ett plus kan också användas: [0-9]+ → EN eller flera siffror.
> > Man kan också specificera antalet exakt: [0-9]{3} → Exakt TRE siffror.
> > [0-9]{3,5} → Minst TRE och högst FEM siffror.
> >
> > Som sagt, hjälpen är bra hjälp i detta sammanhang. Enklast är att
> > klicka på hjälp-knappen i SÖK/ERSÄTT-dialogen, därefter klicka på
> > länken ”Reguljära uttryck”.
> >
> >
> > Johnny Rosenberg
> >
> > > Ersätt med: $1
> > >
> > > Om du bara vill jobba i ett område som du redan markerat, välj ”Endast
> > > aktuell markering”. Annars är det hela bladet som gäller.
> > > Klicka ”Sök alla” för att se att du får markering på de celler du
> > > tänkt dig. Ser det rätt ut, klicka ”Ersätt alla”.
> > >
> > > Om du inte kan reguljära uttryck finns det beskrivet i hjälpen. I
> > > detta fall kan jag bidra med en enkel förklaring av ovanstående
> > > sökning:
> > > (.*) – Punkten betyder ”valfritt tecken”, asterisken anger ”noll eller
> > > fler av föregående tecken”.
> > > \. – Eftersom punkt betyder ”valfritt tecken”, måste vi föregå punkten
> > > med ett ”escape-tecken”, ”\”, om vi vill söka efter en punkt.
> > > - – Ett minustecken.
> > > $ – Radslut.
> > > : – Ett vanligt kolon.
> > > | – ”Eller”.
> > >
> > > Så i detta fall:
> > > Sök efter ett okänt antal valfria tecken (”noll eller flera”) följt av
> > > en punkt, ett minustecken och ett radslut ELLER ett okänt antal
> > > valfria tecken följt av ett kolon, ett minustecken och ett radslut.
> > > I ersätt har jag angett ”$1”. Varför då?
> > > Jo, det har att göra med parentesen i sök-fältet. En parentes anger
> > > att det som anges inuti ska kommas ihåg. Första parentesen kan sedan
> > > kallas fram i Ersätt-fältet med $1, den andra med $2 och så vidare. I
> > > detta fall har vi visserligen två parenteser, men egentligen är det ju
> > > bara en eftersom vi har en ELLER emellan. Därför kan ju aldrig BÅDA
> > > parenteserna hittas.
> > >
> > > Fler användbara saker framgår av hjälpen. Exempelvis kanske man vill
> > > veta hur man anger början av en rad, nu när vi vet hur radens slut ($)
> > > anges. Då hittar man snart i tabellen i hjälp tecknet ^.
> > > Observera att samma tecken kan betyda olika saker beroende på var man
> > > skriver dem. Exempelvis kan ^-tecknet också användas som ”ICKE”:
> > > [^acgrt] – valfritt tecken UTOM a, c, g, r eller t.
> > > Ja, det finns mycket kul man kan göra med reguljära uttryck. Synd bara
> > > att begreppet är lite funktionshindrat i LibreOffice. Vissa saker går
> > > helt enkelt inte att göra, men det är ändå värdefullt, det som trots
> > > allt finns. Speciellt när man vant sig vid tankesättet…
> > >
> > >
> > >
> > >
> > >
> > > Johnny Rosenberg
> > >
> > >
> > > > //Jan
> > > >
> > > > --
> > > > For unsubscribe instructions e-mail to: users+help@sv.libreoffice.org
> > > > Problems?
> > > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> > > > List archive: http://listarchives.libreoffice.org/sv/users/
> > > > All messages sent to this list will be publicly archived and cannot be
> > > > deleted
>
>
> --
> For unsubscribe instructions e-mail to: users+help@sv.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/sv/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

-- 
For unsubscribe instructions e-mail to: users+help@sv.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/sv/users/
All messages sent to this list will be publicly archived and cannot be deleted