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