Hjelp med Excel formler

[hamil]

Heisann!

 

Prøver å lære meg litt excel-formler, samtidig som jeg prøver å gjøre livet litt lettere for arbeidskollegaene mine (og meg selv..)

 

Men skjønner ikke helt hvordan jeg skal komme videre fra der jeg nå står... Dette er problemstillingen:

 

* Jeg har et timeskjema (utformet av arbeidsgiver, og ufravikelig i oppførsel). På dette skjemaet skal min arbeidstid føres opp med et kryss for hver halve time, og en O for hver overtids-halvtime. Alt dette blir summert rikitig i det arket.

* Jeg har laget en egen "hovedside" der alle relevante statiske informasjoner fylles inn, som navn, arbeidsperiode, arbeidsplass, arbeidstid for hver dag, med tre forkjellige muligheter for registreringer med tanke på pauser og hvile (i formatet tt:mm-tt:mm, tt:mm-tt:mm, tt:mm-tt:mm).

 

Det jeg nå gjerne skulle hatt litt hjelp med, er hvordan jeg skal få importert denne informasjonen fra hovedsiden min og over i timeslisten, slik at om jeg fyller i denne informasjonen:

 

Arbeidsperiode 1:

Fra: 12.12.07 (Celle H21)

Til: 03.01.08 (Celle K21)

 

Arbeidstid 1:

Fra: 08:00 (Celle C12)

Til: 12:00 (Celle D12)

Arbeidstid 2:

Fra: 13:00 (Celle E12)

Til: 17:00 (Celle F12)

Arbeidstid 3:

Fra: 18:00 (Celle G12)

Til: 21:30 (Celle H12)

 

Så vil jeg gjerne at all denne arbeidstiden skal dukke opp som x-er i "1. timeliste"...... "1. timeliste" består av 31 linjer (en for hver dag) og 48 kolonner (en for hver halvtime).

 

 

Om det er noen som har innspill eller spørsmål, så tar jeg gledelig imot de, hadde vært greit å fått til dette..

[hamil]

He he, sikkert jeg som bare er totalt ubrukelig i å forklare meg... Prøver å forklare det på en annen måte..

 

Jeg skal da som sagt prøve å få til en timeliste.. I "Ark 1" skal alle skrive inn Navnet sitt, arbeidsplass, i hvilket tidsrom de har arbeidet (fra dato til dato) og den normale arbeidstiden (fra klokken til klokken x 3, så man også har mulighet til å trekke ut opptil 3 pauser i døgnet). I "Ark 2", ønsker jeg at opplysningene fra "Ark 1" skal bli overført til, det har jeg klart, men har trøbbel med datoene og selve arbeidstiden...

 

I "Ark 2" kommer hver dato på en linje, og arbeidstiden skal fylles inn med x i hver kolonne. På den måten skal de timene man har ført inn som arbeidstid på "Ark 1" dukek opp som kryss her. Hver kolonne tilsvarer en halvtime med jobbing, fra klokken 0000 i den første kolonnen til 2400 i den siste.

 

Legger ved et skjermbilde som viser noe av oppsettet på det siste arket.

 

Om det er noe som er uklart, eller om noen har lyst til å se selve regnearket, sender jeg det gjerne avgårde eller svarer litt mer utfyllende..

[Harald Staff]

Litt forvirret ennå.

Brukeren skal skrive inn dato og tider, og så skal dette bli kryss ? Eller skal de krysse av så det blir dato - tid ?

Og så må du fortelle hvilken Excelversjon / Språkversjon du bruker.

 

Beste hilsen Harald

[hamil]

Hei!

 

Godt nyttår, og takk for svar.

 

Jeg bruker engelsk versjon av Excel 2003, SP2.

 

Siden man skal inn med så mange dager, og siden det er lettere å skrive et klokkeslett enn en mass x'er, er hoved intensjonen at man på første siden skal kun behøve å skrive inn ens arbeidstid med tall. Disse skal da konverteres til tilsvarende x'er som ens arbeidstid * 2 (en x for hver halvtime) på "Ark 2".

 

Eksempelvis:

Jeg har jobbet fra dato: 01.01.2008

Til dato: 29.01.2008 (dette fører jeg inn på "Ark 1"

Arbeidstiden min i denne perioden har vært:

Arbeidstid 1: 0800-1200 (føres inn på "Ark 1")

Arbeidstid 2: 1300-1700 (føres inn på "Ark 1")

Arbeidstid 3: 1730-2100 (føres inn på "Ark 1")

 

All denne informasjonen vil jeg at skal komme frem på "Ark 2" i de korresponderende radene og kolonnene. Arbeidstiden må bli til x i de rutene der man har jobbet, første arbeidsdag (01.01.08) må havne på riktig linje og man må et riktig antall linjer med arbeidstid (1 for hver dag, altså 29 linjer i dette tilfellet).

 

Klarte jeg å forklare intensjonene bedre nå?

 

Takk for hjelpen!

[Harald Staff]

Absolutt.

Da regner jeg med at dagene (1,2,3,... ) står i A-kolonnen (altså tall, ikke datoer) og at klokka 0 begynner i kolonne E. Sett denne i celle E4 -fjern linjeskift hvis denne nettsiden lager noen:

 

=IF($A4<DAY(Sheet1!$H$21);"";IF($A4>DAY(Sheet1!$H$22);"";IF(OR(AND((COLUMN()-5)>=(Sheet1!$C$12*48);(COLUMN()-5)<=(Sheet1!$D$12*48));AND((COLUMN()-5)>=(Sheet1!$E$12*48);(COLUMN()-5)<=(Sheet1!$F$12*48));AND((COLUMN()-5)>=(Sheet1!$G$12*48);(COLUMN()-5)<=(Sheet1!$H$12*48)));"X";"")))

 

Fyll ned og mot høyre, så burde du være i nærheten av noe.

 

HTH. Beste hilsen Harald

[hamil]

Hei!

 

Takk for svar!! Det så ut til å gjøre susen!!

 

Alle datoene, fra 1-31 står i A-kolonnen. Fra A8 og nedover. Halvtime-rutene er fra C8-AX8.

 

Får det ikke helt til å stemme, vet ikke helt hvorfor, men det ser ut som om alle får en halvtime jobb gratis.. He he he.. Meget mulig det er noe feil med det jeg har gjort?

 

De får automatisk en halvtime med jobbing mellom 0100 og 0130 (for dag 1-4 i celle E8-E11)...

Jeg har måttet tilpasse formelen til mitt regneark, og har følgende formel inne:

=IF($A8<DAY(info!$H$21);"";IF($A8>DAY(info!$K$21);"";IF(OR(AND((COLUMN()-5)>=(info!$C$12*48);(COLUMN()-5)<=(info!$D$12*48));AND((COLUMN()-5)>=(info!$E$12*48);(COLUMN()-5)<=(info!$F$12*48));AND((COLUMN()-5)>=(info!$G$12*48);(COLUMN()-5)<=(info!$H$12*48)));"X";"")))

 

A8 er "Date" 1

H21 er 01.01.08

K21 er 04.01.08

C12 er blank

D12 er blank

E12 er blank

F12 er blank

G12 er blank og

H12 er blank

 

Alikevel kommer det da altså opp en halvtime jobb (x) mellom Date "1" og "4" ?

 

Og om det er noen som har noen gode linker på hvor man kan lese seg opp på Excel, hadde jeg blitt evig takknemlig. Ser ut som om det blir endel jobbing med Excel fremover...

 

Og tusen hjertlig takk for hjelpen Harald, utrolig takknemlig for den tiden du har tatt deg med dette!!

 

Mvh

Lasse

[Harald Staff]

Det er et mysterium at blank er større enn null når man regner tid. Jeg har sett det før og ikke forsket særlig på det. Men nå var det ikke noe blanke celler i utgangspunktet ditt, så sånne problemer kalles implementering. Skal se på det og det andre ved anledning -akkurat nå må jeg rigge huset til en større tiåring-bursdag.

 

Beste hilsen Harald

[Harald Staff]

Hei igjen

 

Du må legge inn et "er ikke blank"-kriterium i alle AND-parentesene. Denne er ikke testet men skal være i nærheten:

 

=IF($A8<DAY(info!$H$21);"";IF($A8>DAY(info!$K$21);"";IF(OR(AND((COLUMN()-5)>=(info!$C$12*48);(COLUMN()-5)<=(info!$D$12*48);info!$C$12<>"");AND((COLUMN()-5)>=(info!$E$12*48);(COLUMN()-5)<=(info!$F$12*48);info!$E$12<>"");AND((COLUMN()-5)>=(info!$G$12*48);(COLUMN()-5)<=(info!$H$12*48);info!$G$12<>""));"X";"")))

 

Den beste Excelbloggen heter Daily Dose Of Excel : http://www.dailydoseofexcel.com/

fra medium til avansert er Chip Pearsons side fin: http://www.cpearson.com/excel/mainpage.aspx

ellers finner du mye fint av basic ting hvis du googler Excel Tutorial http://www.google.com/search?hl=en&q=excel+tutorial

 

HTH. beste hilsen Harald

[hamil]

Heisann!

 

Tusen takk for hjelpen til nå! Har vært litt vekk fra jobb nå, men endelig tilbake nå, og får prøvd det litt. Håper feiringen gikk bra!

 

Jeg får fortsatt ikke regnearket til å fungere som forventet, selv med ditt nye forslag til formel. Om jeg i "info" arket setter arbeidstidens begynnelse til 07:00, blir den ikke ført fra før etter klokken 09:00 på regnearket. Den ene halve timen med "gratis-jobb" henger fortsatt der også... Timene blir ført på riktig dato, men altså ikke på rett time, ei heller med korrekt antall timer....

[Harald Staff]

Solved.txt

Laster opp løsningen. Døp om til .xls før du åpner.

[hamil]

Heisann!

 

Takker for kjapt svar!

 

Sitter og stirrer meg helt blind på formelen din, og kan ikke se at jeg har gjort det noe annerledes... Laste det opp som .txt fil var en god ide.. Her har jeg lastet opp mitt regneark, om du hadde hatt lyst til å se hva jeg eventuelt gjør galt? Takknemlig for tilbakemelding...

timeliste_test.txt

[hamil]

Heisann!

 

Om det hadde vært noen trente "excel-øyne" som hadde hatt anledning til å se hvorfor løsningen ikke fungerer i mitt regneark (lastet opp i forrige innlegg og gitt navn som en txt fil), så hadde jeg blitt veldig takknemlig! Virker jo som om det faktisk går ann å få til dette..

 

Mange takk for forslag/forsøk til løsning!

 

Mvh

Hamil

[Harald Staff]

Du fikk en løsning av meg. Virker den ikke ?

[hamil]

Hei,

 

Joda, løsningen din fungerer helt topp, når jeg er i ditt regneark.. Når jeg prøver å få den til å fungere i min timeliste, får jeg samme feil som jeg nevnte tidligere i tråden, arbeidstiden starter på feil tidspunkt, og en halvtime gratis arbeidstid....

[Harald Staff]

To ting her, siterer meg selv:

 

og at klokka 0 begynner i kolonne E

da kan du ikke bare begynne i C uten å gjøre noen forandringer

 

Du må legge inn et "er ikke blank"-kriterium i alle AND-parentesene

du har ikke med leddene som sier sånt som ;info!$C$12<>""

Endret 13. februar 2008 av Harald Staff

[hamil]

To ting her, siterer meg selv:

 

og at klokka 0 begynner i kolonne E

da kan du ikke bare begynne i C uten å gjøre noen forandringer

 

Du må legge inn et "er ikke blank"-kriterium i alle AND-parentesene

du har ikke med leddene som sier sånt som ;info!$C$12<>""

 

 

Hei igjen, og takk for at du er så tålmodig med meg.. Jeg har i den øverste halvdelen av regnearket med denne "er ikke blank" funksjonen. Den nederste halvdelen er med den opprinnelige formelen du foreslo, slik at jeg har hatt den å lese på også, for å prøve å tyde forskjellene, noe jeg foreløpig ikke helt har fått grepet om....

 

Når jeg leser den formelen jeg har i mitt regneark (øverste del) nå, ser den slik ut:

=IF($A18<DAY(info!$H$21);"";IF($A18>DAY(info!$K$21);"";IF(OR(AND((COLUMN()-5)>=(info!$C$12*48);(COLUMN()-5)<=(info!$D$12*48);info!$C$12<>"");AND((COLUMN()-5)>=(info!$E$12*48);(COLUMN()-5)<=(info!$F$12*48);info!$E$12<>"");AND((COLUMN()-5)>=(info!$G$12*48);(COLUMN()-5)<=(info!$H$12*48);info!$G$12<>""));"X";"")))

 

Av dette prøver jeg nå å finne de forskjellige henvisningene, for å tilpasse det til min timeliste. Jeg ser ikke helt hvor jeg har tatt feil av C og E... Etter min forstand leser jeg formelen slik:

 

=IF($A18<DAY(info!$H$21);"";

Om informasjonen i A18 er mindre enn informasjonen fra arket info H21, skal ruten være blank.

 

IF($A18>DAY(info!$K$21);"";

Om informasjonen i A18 er større enn informasjonen fra arket info K21, skal ruten være blank.

 

IF(OR(AND((COLUMN()-5)>=(info!$C$12*48);

Arbeidstidsstart, listet i arket info C12, ganges opp med 48, for å få antall halvtimer.

 

(COLUMN()-5)<=(info!$D$12*48);

Arbeidstidsstop, listet i arket info D12, ganges opp med 48, for å få antall halvtimer.

 

info!$C$12<>"");

Ingen aning om hva denne linjen gjør, og hvilken info den henter fra arket info...

 

AND((COLUMN()-5)>=(info!$E$12*48);

¨

Arbeidstidsstart, listet i arket info E12, ganges opp med 48, for å få antall halvtimer.

 

(COLUMN()-5)<=(info!$F$12*48);

Arbeidstidsstop, listet i arket info F12, ganges opp med 48, for å få antall halvtimer.

 

info!$E$12<>"");

 

AND((COLUMN()-5)>=(info!$G$12*48);

Arbeidstidsstart, listet i arket info G12, ganges opp med 48, for å få antall halvtimer.

 

(COLUMN()-5)<=(info!$H$12*48);

Arbeidstidsstop, listet i arket info H12, ganges opp med 48, for å få antall halvtimer.

 

info!$G$12<>""));

 

"X";

Setter inn alle X'ene

 

"")))

Eller blanke felt, lukker hele formelen.

 

Aner ikke hvor jeg skal gjøre endringene i formelen, for å få alle x'ene til å begynne i kolonne C istedenfor i kolonne C på timelisten.....

[Harald Staff]

Du er på sporet.

(COLUMN()-5)<=(info!$H$12*48);

E er kolonne 5. C er kolonne 3, og dette skal tilsvare klokken 0. Du må derfor endre absolutt alle COLUMN()-5 til COLUMN()-3 hvis du må begynne i C.

 

info!$C$12<>"");

Denne sjekker om C12 er fylt inn eller ikke. Det er der den mystiske halvtimen fanges opp, sjekken er nødvendig.

 

HTH. Beste hilsen Harald

[hamil]

Aha du, da skjønte jeg litt mer ja..

 

Men uansett, nå stemmer alt det der, men den mystiske halvtimen dukker stadig opp, både i ditt løsningsforslag, og i mitt regneark nå. Når jeg endelig fikk til riktig kolonnen nå, kunne jeg ikke skjønne hvorfor det ikke fungerte for meg i mitt regneark enda... Tok derfor og telte antall kryss i løsningsforslaget, og i regnearket mitt. Selvom løsningsforslaget så korrekt ut med første øyekast, gir den også en gratis halvtime for hvert tidsrom som føres opp...

 

I forslaget ditt, kjører du med tre forskjellige tidsrom, og de kryssene som nå genereres, skulle tilsvare antall arbeidstimer * 2, men det skjer ikke.... For hvert tidsrom blir x'ene multiplisert med 2, og tillagt en... Feks skal det jo da i tidsrommet 00:00 til 02:00 kun bli 4 kryss, men det blir 5 i regnearkene, både i ditt løsningsforslag, og i min timeliste... Det samme skjer i de andre tidsrommene jeg setter opp også...

 

Dette er formelen jeg bruker nå:

=IF($A10<DAY(info!$H$12);"";IF($A10>DAY(info!$H$13);"";IF(OR(AND((COLUMN()-3)>=(info!$C$3*48);(COLUMN()-3)<=(info!$D$3*48);info!$C$3<>"");AND((COLUMN()-3)>=(info!$E$3*48);(COLUMN()-3)<=(info!$F$3*48);info!$E$3<>"");AND((COLUMN()-3)>=(info!$G$3*48);(COLUMN()-3)<=(info!$H$3*48);info!$G$3<>""));"X";"")))

Prøvde å bygge om førstesiden litt, derfor andre henvisninger nå...

[Harald Staff]

Disse "andre henvisninger" tilfører masse merarbeid for de som prøver å hjelpe deg, særlig hvis de må gjette hva som er endret. Så nå mistet du meg.

Det er et gyllent prinsipp jeg maser om hver gang jeg underviser: FØRST få det til å virke, SÅ pynte etter at absolutt alt virker. Du pynter og flytter underveis. Alt blir vanskeligere da.

 

Beste hilsen Harald

[hamil]

Hei,

 

Ingen problemer, har jo alltid liggende en kopi av timelisten før jeg begynte å kludre med den. Uansett, feilen med den mystiske halvtimen består, samme hva jeg gjør. Her er formelen for timelisten før jeg begynte å pynte på forsiden.

 

Når jeg jobber med den ukludrede timelisten, og legger inn formelen for henting av timene:

=IF($A19<DAY(info!$H$21);"";IF($A19>DAY(info!$K$21);"";IF(OR(AND((COLUMN()-3)>=(info!$C$12*48);(COLUMN()-3)<=(info!$D$12*48);info!$C$12<>"");AND((COLUMN()-3)>=(info!$E$12*48);(COLUMN()-3)<=(info!$F$12*48);info!$E$12<>"");AND((COLUMN()-3)>=(info!$G$12*48);(COLUMN()-3)<=(info!$H$12*48);info!$G$12<>""));"X";"")))

Der 5 er byttet ut med 3, slik at den begynner i kolonne C, kommer det automatisk opp en halvtime ekstra for hver periode med arbeidstimer. For eksempel, legger jeg inn arbeidstid 1 = 07:00-09:00, kommer det opp 5 kryss i timelisten, fra 07:00-09:30. Det samme skjer om jeg legger inn flere arbeidsperioder, det blir lagt til et kryss for hver arbeidsperiode....