BBS-logg

[RolfCarlsson]

Jag har en databas som är en logg från en BBS. Varje post i databasen innehåller text av varierande längd men med några uppgifter som återkommer i alla poster. Exempel:

Re: Roliga historier 12/4/04 7:03:01 am Tina Testare

 

* Namnen på medlemmarna har jag i en tabell

* Datum och tid är i amerikanskt format (månad/dag/år timme:minut:sekund am/pm)

* Texten före datumet varierar mycket och kan ha olika längd

 

Det jag skulle vilja göra är att till en ny databas (eller till nya fält i samma databas) extrahera informationen så att varje post innehåller enbart tre fält med:

* Namn

* Datum i europeiskt format

* Timme, minut och sekund i europeiskt format

 

Jag har försökt lösa problemet med både Excel och Access men har tyvärr inte tillräckliga kunskaper i något av programmen. Förmodligen finns det en ganska enkel lösning.

 

Kan jag få några tips?

 

/Rolf Carlsson

 

 

 

[MH]

EXCEL

Om din text står i kolumn A så borde du kunna få fram namnet med en formel i stil med:

=HÖGER(A1;LÄNGD(A1)-SÖK(":??:?? ";A1;1)-9)

 

Klockslaget

=TIDVÄRDE(EXTEXT(A1;SÖK(":??:?? ";A1;1)-2;11))

 

*****tillägg******

Obs att du måste ändra visningsformatet i excel (format celler tid) för att det skall se rätt ut.

I verkligheten lagras tiden som "andel av dag", DVS 00:00:00=0 medan kl 12 = 12/24 osv. Om du vill ha ut en textsträng i exakt rätt format så får du använda funktionen "TEXT", TYP:

=TEXT(C1;"tt:mm:ss")

/*****tillägg******

 

 

Datumet orkar jag inte testa. Borde gå om du använder sök på samma sätt som ovan, fast med " ??/". Vet inte vad du kan förutsätta riktigt. Kan "/" förekomma i rubriken?

istället för "TIDVÄRDE" så skulle du kunna testa "=datumvärde". Vette tusan om den klarar av att översätta från Amerikanskt till europeiskt. Annars får du hacka det i bitar och använda "DATUM". Testa och lämna Feedback.

/M

 

Tilägg 2: Ändrade formel 2 PGA hjärnsläpp

[inlägget ändrat 2004-12-08 16:45:21 av MH]

[RolfCarlsson]

Tack för alla tipsen. Det hjälpte mig en bit på väg. När jag har en klar lösning ska jag redovisa den här. Men nu har jag ett anslutande problem.

 

I Excel-hjälpen har jag hittat följande text "Du kan också använda HITTAB om du vill hitta en textsträng inom en annan". Men någon sådan funktion finns inte i min version av Excel (Office 2000 Prefessional). Jag har också förgäves kollat på installations-CDn och på Webben.

 

Om en funktion finns beskriven i hjälptexten borde den väl även finnas för användning - eller ????

 

/Rolf Carlsson

 

 

[MH]

Kollade min dator. HittaB finns inte i "infoga funktion". Men om du skriver in det manuellt så går det! Märkligt, eller?

=HITTAB("t";C2)

Har du någon nytta av HittaB? Verkar lite konstigt om en Loggfil skulle innehålla "dubbel byte tecken". Eller tänker jag helt fel nu?

 

För övrigt finns "B" formler för nästan alla texthanteringsfunktioner, T.ex ExtextB. Använder du det på ett ställe så måste du nog köra det generellt.

 

Hur går det med datumet?

Kan du förutsätta att det inte finns något / i rubriktexten? Då skulle du kunna hitta år

=EXTEXT(A1;SÖK("/";A1;SÖK("/";A1;1)+1)+1;2)

Månad

=RENSA(EXTEXT(A1;SÖK("/";A1;1)-2;2))

Dag

=RENSA(EXTEXT(A1;SÖK("/";A1;1)+1;SÖK("/";A1;SÖK("/";A1;1)+1)-SÖK("/";A1;1)-1))

 

Eller, den nätta lilla formeln:

=DATUM(EXTEXT(A1;SÖK("/";A1;SÖK("/";A1;1)+1)+1;2);EXTEXT(A1;SÖK("/";A1;1)-2;2);EXTEXT(A1;SÖK("/";A1;1)+1;SÖK("/";A1;SÖK("/";A1;1)+1)-SÖK("/";A1;1)-1))

 

Om det blir 1900-tals datum så får du väl plussa på 36525.

 

Eller, förmodligen enklast.

=RENSA(EXTEXT(A1;SÖK("/";A1;1)-2;8))

Kopera som text, sedan kör du "Data->text till kolumner" med / som separator. Efter det är det en baggis att köra "=datum()"

 

[RolfCarlsson]

Kollade min dator. HittaB finns inte i "infoga funktion". Men om du skriver in det manuellt så går det! Märkligt, eller?

=HITTAB("t";C2)

Har du någon nytta av HittaB? Verkar lite konstigt om en Loggfil skulle innehålla "dubbel byte tecken". Eller tänker jag helt fel nu?

 

Nej, texten om HITTAB fanns i beskrivningen av funktionen SÖK. Det där med "dubbel byte tecken" visste jag inte om. Jag får också HITTAB att funka som du beskrev, men det ger också bara textens position i posten. Det var inte det jag ville ha.

 

Jo, jag har faktiskt fått detta med datum att fungera, men det var en ren lyckträff. Jag förstår fortfarande inte varför. Men jag ska återkomma med en utförligt redogörelse för det hela när jag själv fattat vad jag gjort :-)

 

/Rolf Carlsson

 

[RolfCarlsson]

Tack för den "lilla netta formeln". Jag trodde att jag kommit på en enklare lösning men den höll inte för alla varianter av datum.

 

Jag är inte säker på att jag förstått allt, men det verkar som om det funkar nu. Kalkylmodellen som den ser ut nu finns på:

 

http://www.rcdk.net/SNS/Loggtest.xls

 

Under resans gång har ambitionen växt. Det finns två saker kvar som jag inte lyckas hitta en lösning på.

 

/Rolf Carlsson

 

 

[MH]

Kom just på en sak jag borde ha påpekat.

Anledningen till att det blir så krångligt med Datum är ju att du har svenska inställningar i Windows (excel lånar alla inställningar från "nationella alternativ" i kontrollpanelen).

Om du kan ställa om till amerikanskt datum så blir konverteringen mycket enklare. Men å andra sidan, vem vill ha amerikanskt datum i Windows ?(bortsett från några 100 miljoner jänkare...)

 

Laddade ner din fil och tittade lite. Antar att du håller på med "deltagare" nu? Det blir förmodligen mycket enklare om du använder Pivotabeller istället. Det är ett kraftigt underskattat hjälpmedel i Excel.

MEN (ständigt dessa men) Om du vill använda Pivotabeller så är problemet att du har delat upp Datum och Tid. Det gör det väldigt svårt att hitta korrekt sistadatum (eller, datumet är lätt, men inte att knyta rätt klockslag till det datumet).

Du kan skapa en ny variabel där du slår ihop Datum + tid (för en gångs skull precis så enkelt som det låter, =B2+C2). Sen kan du skapa en pivotabell, exempel:

 

Stå i databasen och välj DATA-rapport för pivo... -> Nästa-> (Utöka gärna området så att det ingår fler rader. Då kan du lägga till personer utan att behöva göra om allt) Nästa-> "på nytt kalkylblad" (eller bestäm) Slutför ->

 

1. Sedan släpper du "skrivet av" i radfältet,

2. Släpa en ny "skrivet av" till "dataelement", se till att det blir "antal"

3. Släpa "tidpunkt" (sammanslagningen av datum och tid) till "dataelement", Högerklicka, välj fältinställningar och ändra till MAX

 

Om datafälten kommer i fel ordning så kan du släpa den grå knappen "data" lite försiktigt tills höger tills det blir som du vill.

 

Sen måste du naturligtvis ändra visningsformatet till "datum+tid" Annars blir det ganska ointressant (högerklicka, fältinställningar, tal-> datum...)

 

Det sista du gör är att ändra rubrikerna till något roligare än "max" osv (högerklicka, fältinställningar, ändra i fältet "namn").

 

Pivotabellen kan verka lite jobbig, men alternativet är att göra allt med formler. Jag tror att du blir tvungen att köra med matrisformler och annat otäckt för att få till det och det är inget kul.

 

Om du vill ha olika celler för Datum och klockslag så kan du helt enkelt släpa in en extra "Max tidpunkt" och formatera den ena för att visa datum och den andra för attt visa tid...

 

Puuuh

/M

 

[RolfCarlsson]

Det blir förmodligen mycket enklare om du använder Pivotabeller istället. Det är ett kraftigt underskattat hjälpmedel i Excel.

Jag har faktiskt använt Pivotabeller förut i andra sammanhang. Tänkte inte på att de gick att använda även här. Tack för tipset.

 

Stå i databasen och välj DATA-rapport för pivo... -> Nästa-> (Utöka gärna området så att det ingår fler rader. Då kan du lägga till personer utan att behöva göra om allt) Nästa-> "på nytt kalkylblad" (eller bestäm) Slutför ->

1. Sedan släpper du "skrivet av" i radfältet,

2. Släpa en ny "skrivet av" till "dataelement", se till att det blir "antal"

Där anser min version av Excel att Pivotabellen är klar så jag får aldrig möjlighet att:

 

3. Släpa "tidpunkt" (sammanslagningen av datum och tid) till "dataelement"

Men om jag däremot tar det via Layout-knappen så kan jag få dit båda. Men då hamnar värdena under varandra. Jag lyckas inte få Antal och Senaste bredvid varandra som jag tycker vore snyggare.

 

Den nya versionen finns på samma ställe och under samma namn som den gamla versionen.

 

/Rolf Carlsson

 

 

 

[MH]

Där anser min version av Excel att Pivotabellen är klar så jag får aldrig möjlighet att:

Vi kör samma ver av excel, men jag antar att det är den lilla datoralfen som är framme. Gå till menyn visa-> verktygsfält och välj pivotabell. Visserligen tycker jag att den gamla menyn under Layotknappen är mycket smidigare, men det är ju så väl dold nuförtiden.

 

Men då hamnar värdena under varandra

Vet inte om det finns något kommando. Men det går med musen. Det är bara lite svårt att förklara. Ta tag i den gråa klossen "Data" och släpa den åt höger och släpp (som om du försökte flytta den in i cellen där det står totalt). Du kommer att få lite ledtrådar i form av en blå "spöktabell".

Svårt att skriva en förklaring. Du måste nästan leka dig fram.

 

Hmmm, det måste finnas ett kommando. Ähh, spela roll.

/M

 

[RolfCarlsson]

Det blir förmodligen mycket enklare om du använder Pivotabeller istället.

 

Problem: Min Pivotabell uppdateras inte när jag gär ändringar eller tillägg i den. Jo, jag har berett plats för tillägg.

 

Fundering: Skulle man inte kunna använda databasfunktionerna DANTAL och DMAX i stället?

 

/Rolf Carlsson

 

[Monshi]

Min Pivotabell uppdateras inte när jag gär ändringar eller tillägg i den

Menar du att ändringar du gör i ex.v. datafältm ändrad ordning eller nya fält ex.v., inte slår igenom?

Pivottabeller är nyckfulla och svårfornaterade men väldigt kraftfulla och enkla när man får dem att fungera.

 

Allt du gör med Pivitabeller kan du också göra med formler i Excel, men formlera blir svåröverskådliga och betydligt svårare att ändra i om något skulle ändras i datamängden. Samt du förlorar den dynaik som finns i pivotabellern.

 

Filen du lagt upp verkar inte jag kunna ladde ned, vet därmed inte riktigt vad det är du vill göra eller hur långt du kommit i lösningen.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[RolfCarlsson]

Menar du att ändringar du gör i ex.v. datafältm ändrad ordning eller nya fält ex.v., inte slår igenom?

Just det. Om jag ändrar tex datum eller tid i "originalkolumnen" så ändras värdena i den kolumn där tex tiden räknas fram med din "nätta" funktion. Men det slår inte igenom i Pivotabellen.

 

Ja har kommit på en stor nackdel med Pivotabeller! Man inte får med dem som varit helt aktiva, dvs aldrig skrivit något inlägg. Det är bland det viktigaste jag behöver veta.

 

Jag har kommit på en lösning med databasfunktionen DMAX. Men den är tyvärr ganska klumpig.

 

Den senaste versionen av kalkylen finns på det vanliga stället:

http://www.rcdk.net/SNS/Loggtest.xls

Jag lyckades i alla fall plocka hem den. Det var kanske ett tillfäligt fel på servern.

 

/Rolf Carlsson

 

 

 

 

 

[RolfCarlsson]

Nu har jag en lösning med databasfunktionen DMAX som verkar fungera. Den finns som vanligt på:

http://www.rcdk.net/SNS/Loggtest.xls

 

Nackdelen med databasfunktionerna är att villkoren bara kan skrivas horisontellt. Det är ganska opraktiskt.

 

Fördelen med den här lösningen är att jag tydligt får signal om de inaktiva deltagarna. Det lyckas jag inte få med Pivotabellerna.

 

Det går nog inte att komma så mycket längre nu. Så jag får tacka dem som hjälp mig så mycket. Jag hade inte kommit så här långt utan ert bistånd!

 

/Rolf Carlsson

 

 

[Monshi]

Anledningen till att jag ej fick hem filen var FireFox. Med IE fungerar det.

 

Nåja.

 

Klart att du i Pivottabellen bara får med de aktiva! Det krävs ju att du har en lista på alla som är registerade för att få med dem i statistiken. Nackdelen med databsfunktioner, som du nu skapat dem, är att de ej dynamiskt uppdateras om ny personer tillkommer på listan.

 

Vad gäller Pivottabeller, och alla andra databsfunktioner, är regel ett att man ska organisera grunddatat på ett sådant sätt att önskade värden är lätta att få ut.

 

Min lösning på ditt problem skulle vara att ställa upp en ny tabell med alla registerade användare i. Skapa funktioner i denna tabell som ex.v. hämtar tid för senaste inlägg och antal inlägg ur den tabell du nu har på bladet logg. Något liknande det du gjort på bladet DMax. Skulle dock ställa upp det på andra ledden i en ren datatabell och sedan ur denna datatabel ta datat som presenteras i en tredje tabell, kanske en pivottabell.

 

För du behöver nog en trestegsraket.

1: Ta ut värden ur loggen

2: Identifiera mot användarregister vem som skrivit. (Passa, hitta, letarad eller dylikt. Kärvs nog lite uppfiningsrikedom, som jag gärna bidrar med, för att få ut alla data du önskar)

3: Presentera statistik

 

Tabellen i steg två kommer att innehålla mycket information, men även troligen en hel del inaktuell infomation.

Steg tre kommer att sortera ut den information du önskar. Vem skrev senaste inlägg, vem har skrivit flest inlägg, hurt många inaktiva medlemmar finns det det osv. Statisik som alltså beräknas ur de uppgifter som finns i steg 2.

 

Ok?

 

Just det. Om jag ändrar tex datum eller tid i "originalkolumnen" så ändras värdena i den kolumn där tex tiden räknas fram med din "nätta" funktion. Men det slår inte igenom i Pivotabellen.

Missade detta i mitt svar. Pivotabeller uppdateras inte automatiskt vid förändringar i dataområdet. Uppdateringen måste initieras manuellt eller via lite VBA-kod.

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2004-12-11 14:34:40 av Monshi]

[RolfCarlsson]

Min lösning på ditt problem skulle vara att ställa upp en ny tabell med alla registerade användare i. Skapa funktioner i denna tabell som ex.v. hämtar tid för senaste inlägg och antal inlägg ur den tabell du nu har på bladet logg. Något liknande det du gjort på bladet DMax. Skulle dock ställa upp det på andra ledden i en ren datatabell och sedan ur denna datatabel ta datat som presenteras i en tredje tabell, kanske en pivottabell.

 

Jo, det verkar vettigt. Det har blivit ganska mycket trial-and-error. Jag började med det jag hade och byggde vidare på det. Skulle givetvis ha börjat med det jag ville ha ut. Men det är inte för sent.

 

Återkommer om/när jag räknat ut något som jag vill ha synpunkter på.

 

/Rolf Carlsson

 

 

[MH]

Du kan få det på"vertikalt också. Men då är vi inne i Matrisformlernas mörka värld...

Se Monshis exempel i den här tråden för en skiss:

//eforum.idg.se/viewmsg.asp?EntriesId=589239

 

I ditt fall skulle det bli någonting i stil med

=MAX(OM(Logg!$E$1:$E$100=DMax!A2;Logg!$D$1:$D$100;""))

Varefter du trycker CTRL Shift Enter

Enda nackdelen är att den försöker skriva ut 0:värden om personen är inaktiv. Det kan du enklast trolla bort med hjälp av villkorsstyrd formatering.

/M