Den 2 februari 2013 12:21 skrev Johnny Rosenberg <[email protected]>: > Den 2 februari 2013 00:19 skrev Jan Öhman <[email protected]>: >> 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)));",";".")
Observera också att denna långa formel är i stort sett helt omöjlig att felsöka i, eller utveckla om man skulle vilja tillföra något nytt i den. Det blir till att börja från början i så fall. Johnny Rosenberg > > 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 <[email protected]>: >>>> >>>> Den 1 februari 2013 10:40 skrev Jan Öhman <[email protected]>: >>>>> >>>>> 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: [email protected] >>>>> 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: [email protected] >> 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: [email protected] 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
