Excel - spørsmål rundt lookup/andre formler (hente riktig info)

[kjakan2005]

Har et spørsmål om hvordan jeg kan bruke Excel på best mulig måte på en enklest mulig måte.

 

Situasjonen er følgende: Jeg får en oversikt over tall fra ulike avdelinger pr.mnd. August-tallene for alle avdelingene kan jeg dumpe i et ark som heter "august-dump". Tilsvarende kan man lage en "juli-dump", "juni-dump" osv. I denne dumpen ligger de samme tallene (feks innteker, utgifter, resultat osv) for de ulike avdelingene (med nummer 1,2,3 osv). Layouten er helt lik for alle avdelinger, først kommer en rad med inntekter for avd1, utgifter for avd1, resultat for avd1, deretter to linjeskift, så inntekter for avd2, utgifter for avd2 osv osv...

 

Tanken er at man i ark1 skal kunne i ark1 velge avdeling (1,2,3 osv) i en dropdown meny og i tillegg velge hvilken måned (juni, juli, august osv) i en annen dropdown meny. Når man feks velger avd1 og august måned skal inntekt, utgift, og resultat tallene hentes fra "august-dump" arket. Tilsvarende skal riktig info hentes hvis man velger avd3 og juni (da hentes tall fra "juni-dump" for avd3). Det betyr at det er to forbehold (conditions) som avgjør hvilket tall som dukker opp i ark1 (hvilken avdeling og måned man velger).

 

Spørsmål: hvordan skal formlene i ark 1 være for å få opp den riktige informasjonen fra de ulike "dump-arkene"? Vet ikke om dette er slå.opp (lookup) funksjoner eller andre funksjoner (har norsk excel forøvrig).

[aklla]

Legger ved ett ark som viser hvordan, ble litt mye her.

Du må fjerne .txt bak da forumet ikke tillater at man laster opp .xls dokumenter.

Excelark.xls.txt

[deaktivert443556]

Et meget grovt utkast til noe som kan fungere...

 

avdelinger.xls.zip

 

Kanskje vil det fungere bedre om du dumper all dataen i samme ark og lager en pivottabell?

[kjakan2005]

Her var det interessante innspill. Filen til Bradbury klarer jeg ikke åpne (hverken som zip fil eller som xls hvis jeg fjerner filbenevnelsen). Er det bare meg eller er det noe galt med filen?

 

Jeg har lastet opp en fil som viser mitt behov. I januar-april arkene ligger det tall og målet er at jeg i ark1 i cellene A2 og B2 skal vise hvilken avdeling og måned det er snakk om og at de riktige tallene skal automatisk komme i cellene B9, B10 og B11...

Excelark, litt endret.zip

[kjakan2005]

Nå fikk jeg opp filen til Bradbury (brukte en annen maskin). Interessant tanke. Jeg ser at filen bruker if-setning i celle E2. Hvis jeg har veldig mange avdelinger, la oss si 20, så vil det være problematisk med en IF-setning. IF-setninger kan vel bare ta 5-6 betingelser (if´s) hvis jeg ikke husker feil...

 

Filen jeg lastet opp viser godt problemstillingen. Denne viser at avdelingene i "dump-filene" er sortert under hverandre (vertikalt) og ikke bortover (horisontalt) som Bradburys eksempel baserer seg på.

 

Kanskje noen som har noen smarte tanker når du ser filen jeg lastet opp og leste problemstillingen over..?

[aklla]

Plutselig vil ikke verken finn.rad eller slå.opp fungere slik den burde

Får opp feil resultater når jeg søker opp med det, selv når jeg bare bruker faste verdier uten formler og variabler...

 

Merkelig problem

[deaktivert443556]

vil det være problematisk med en IF-setning

Tenkte over dette og derfor bruken av kursiv. I tilfellet det kun var noen få avdelinger så holdt jeg det enkelt.

 

En annen mulighet er å bruke avdelingsnummeret og forskyvning (offset). Velger man avdeling 2 da henter man verdien to celler til høyre for en referansecelle. Eksempel vedlagt. (jeg får dessverre ikke pakket ut filen du lastet opp).

 

avdelinger.zip

[kjakan2005]

Spennende forslag. Ser du bruker Indirekte formelen og forskyver svarene. Nå har jeg lastet opp et regneark som gjør at det blir noen endringer:

- i de respektive månedsfilene kommer tallene fra avdelingene under hverandre (vertikalt) og ikke bortover (horisontalt)

- avdelingsnumrene består av både tall og tekst som skissert i vedlegg

- tanken var å få en graf med feks "resultat". Tallene jeg eksempelvis har lagt inn er hardcodet og er tallene jeg ville fått opp hvis man valgte "308 forskning" og "april". Målet er å ha inn formler her. Hvis man feks valgte "308 forskning" og "februar", ville man automatisk fått opp tall for de to første mnd og tomt (0) i de to siste...

 

Jeg har lastet opp en fil som heter 28sept.xls.txt, ta å fjern .txt, så skal filen være brukanes (excel 2003-2007 tror jeg). Har også lastet opp en vanlig zip-fil med samme excel-fil hvis dette foretrekkes (zip filen kan åpnes, mens *.txt-filen må man fjerne .txt)

28sept.zip

28sept.xls.txt

[deaktivert443556]

Jeg har gjort et forsøk, men jeg måtte ty til en hjelpekolonne. Til høyre for avdelingene har jeg nummerert disse. Det hadde jeg sluppet om jeg hadde funnet en måte å se i hvilken rad i kolonne D vi finner igjen verdien fra celle B2.

 

28sept.xls.txt

[kjakan2005]

Har tatt litt tid før jeg har fått tid til å se på denne saken. Nå har det dukket opp enda en utfordring. Jeg har lagt inn litt regnskapsinfo som gjør hele scenariet litt mer realistisk (utdrag fra en kontoplan).

 

Opplegget med å hente tall fra ulike "dump-ark" (januar-april) fungerer egentlig veldig bra så lenge antall linjer for hver avdeling er identisk fra måned til måned og hver antall linjer må være identisk for alle avdelingene. Ting fungerer bra for avd 312 (hvor regnskapsinformasjonen består av 35 rader som også er identisk med det som er i ark1). Problemet er for avd 319 som består av hele 52 rader (se feks i "januar-arket"). Da blir det problemer med de formlene som blir brukt i ark1...

 

Jeg ser to muligheter for å ordne opp i dette:

1) sørge for at alle dump-filene blir 100% identisk mellom avdelingene, og også helt like fra måned til måned. Dette kan antageligvis gjøres ved at alle konti vises for alle avdelinger, noe som betyr at man for avd 312 vil få en større mengde konti hvor det bare står 0.

 

Hvis man klarte å lage noen formler som automatisk klarte å fjerne alle linjer i "ark1" som har 0, så hadde man løst problemet med veldig mange linjer med bare 0 (for avd 312 for eksempel). Eventuelt kunne man lagt et ark til som trakk ut bare den vesentlige informasjonen fra "ark1", dvs bare regnskapsinformasjon med positive/negative tall. Utseende skulle bli likt som A4:B40 i "ark1", men mange av radene ville vært borte (siden det bare står 0 der). Velger man 308 Forskning og Januar, ville feks rad 7,8, 10-14 osv ikke bli vist i det nye arket (siden verdien er 0).

 

2) Bruke andre type formler i ark1 som klarer å ta med all regnskapsinformasjonen fram til neste avdeling begynner. Disse formlene må være fleksible slik at antall linjer kan skifte fra gang til gang. Dette hadde vært den beste løsningen, men jeg innser at det kan være umulig å få noe slikt til med Excel og at man må begynne å programmere litt her...

 

Har lagt opp et regneark som viser hva jeg mener (som forrige gang en vanlig zip fil, og en fil hvor man fjerner *.txt filendelsen)...

4okt.xls.txt

4okt.zip

[kjakan2005]

Ble mye tekst i det siste innlegget, men hovedspørsmålet var vel om man har en liste over feks fylker og antall fotballag i eliteserien og om det er mulig å lage formler som automatisk fjerner rader hvor tallet er 0. Outputen vil da bli samme liste som man begynte med, men de fylkene som ikke har noen lag vil være fjernet fra listen (så da vil antall rader i listen kanskje ha gått ned fra 19 (19 fylker) til kanskje bare 5)...