Hur skapar jag en sammanställning där radantalet varierar beroende på exportens storlek?

[Prinsviggo]

Hej, jag har ett problem som jag ska försöka beskriva så enkelt som möjligt.

 

 

Från ett externt program som samlar GPS-data exporteras rapporter till en Excel-mall (som resulterar i en arbetsbok som sparas i en mapp). Mallen är alltså skräddarsydd i Excel och arbetar tillsammans med det externa programmet. Posterna/Raderna kan givetvis variera beroende på rapportens storlek.

 

Nu till frågan, eftersom jag vill sammanställa det viktigaste på eget kalkylblad måste jag hitta ett sätt att ta hänsyn till rapportens storlek/radantalet, hur gör jag det? Jag vill alltså att vid varje export från det externa programmet, där massor av data samlas på kalkylblad1, kunna göra beräkningar på kalkylblad2 genom att helt enkelt länka till kalkylblad1. Jag ska nämna att allt summeras på den sista raden i rapporten och att en tom rad, som döljs, lämnas ovan summeringen. Jag har lekt med tanken att summera celler i de 200 första raderna (tillräckligt för att vara säker på att alla rader kommer att tas med i beräkningarna) och sedan dividera med två eftersom en sådan summa kommer att bli dubbelt så stor (det blir ju summan av alla celler inklusive den cell där summering av alla cellerna redan skett)

 

 

Sammanfattning

Hur ser jag till att data från ett helt område kommer med då källområdet varierar?

 

 

all the best

/Staffan

[Shape_sthlm]

Hej Staffan

 

Till att börja med så vill jag bara säga att det är olämpligt att ha två helt olika frågor i samma tråd, det kommer ge problem så snart medlemmar börjar svara.

 

Med det sagt så ska jag ge dig lite vägledning.

 

1. Med hjälp av av färdiga funktioner i Excel så kan du hantera dynamiska områden, dvs områden som varierar. Funktionen FÖRSKJUTNING kan hjälpa dig att hitta olika celler utifrån en bestämd startreferens. Om du kombinerar detta med funktionen ANTALV så kan du ganska enkelt hantera det du försöka åstadkomma.

 

Kolla t.ex här

http://office.microsoft.com/sv-se/excel-help/skapa-ett-dynamiskt-namngivet-omradet-i-ett-kalkylblad-HA001126115.aspx

 

Om du vill ha mer exakt vägledning så får du ladda upp en exempelfil med din data.

 

Om du känner dig bekäm med programmering så kan du använda dig av t.ex Range("A1").CurrentRegion.Rows.Count för att identifiera antal rader m.m.

 

Läs mer på http://msdn.microsoft.com/en-us/library/aa214248%28v=office.11%29.aspx

 

 

2. Detta gör du enklast med VBA där du helt enkelt hämtar information från dina arbetsböcker. T.ex lägger du alla i en mapp och skriver en kod som .tex summerar data från flik 2 i dessa filer. Är det nåt sånt du tänkt dig?

 

 

/M

[Prinsviggo]

Hej Staffan

 

Till att börja med så vill jag bara säga att det är olämpligt att ha två helt olika frågor i samma tråd, det kommer ge problem så snart medlemmar börjar svara.

 

Med det sagt så ska jag ge dig lite vägledning.

 

1. Med hjälp av av färdiga funktioner i Excel så kan du hantera dynamiska områden, dvs områden som varierar. Funktionen FÖRSKJUTNING kan hjälpa dig att hitta olika celler utifrån en bestämd startreferens. Om du kombinerar detta med funktionen ANTALV så kan du ganska enkelt hantera det du försöka åstadkomma.

 

Kolla t.ex här

http://office.micros...A001126115.aspx

 

Om du vill ha mer exakt vägledning så får du ladda upp en exempelfil med din data.

 

Om du känner dig bekäm med programmering så kan du använda dig av t.ex Range("A1").CurrentRegion.Rows.Count för att identifiera antal rader m.m.

 

Läs mer på http://msdn.microsof...fice.11%29.aspx

 

 

2. Detta gör du enklast med VBA där du helt enkelt hämtar information från dina arbetsböcker. T.ex lägger du alla i en mapp och skriver en kod som .tex summerar data från flik 2 i dessa filer. Är det nåt sånt du tänkt dig?

 

 

/M

 

 

 

Tackar så länge, jag ska justera frågeställningen vid tillfälle. Jag ska testa med det du föreslagit. Mitt problem är att jag inte kan VBA. Trist för det verkar lösa en hel del problem. Jag laddar nog upp en exempelfil här snart.

 

Vänligen

/Staffan

 

 

[Shape_sthlm]

Vi kan ju alltid hjälpa dig med VBA så att det fungerar, problemet är ju att det kan vara ganska statiskt och det finns alltid en risk när man hanterar och loopar igenom många filer (varje fil är ju en potentiell felkälla), så det är ju en avvägning du får göra.

 

/M

[Prinsviggo]

Vi kan ju alltid hjälpa dig med VBA så att det fungerar, problemet är ju att det kan vara ganska statiskt och det finns alltid en risk när man hanterar och loopar igenom många filer (varje fil är ju en potentiell felkälla), så det är ju en avvägning du får göra.

 

/M

 

OK, här kommer en exempelfil Finns en del kommentarer i boken

 

 

Flyttar mitt andra problem till en egen tråd nu på en gång.

 

VH

Staffan

Exempel.xlsx

[Prinsviggo]

Vi kan ju alltid hjälpa dig med VBA så att det fungerar, problemet är ju att det kan vara ganska statiskt och det finns alltid en risk när man hanterar och loopar igenom många filer (varje fil är ju en potentiell felkälla), så det är ju en avvägning du får göra.

 

/M

 

De exporter som förekommer är baserade på samma mall och varierar andast i storlek nedåt (radantalet). Jag tror att det inte ska vålla några bekymmer. För mig handlar det först om att hitta ett bra sätt att hantera dynamiska områden. När det problemet är löst ska jag lösa nästa, jag postade det i en seoparat tråd. Om du vill kika så finns en exempelfil. Om det kan ordnas med funktioner kan jag nog lösa det själv. VBA:t kan jag inte lösa själv.

 

VH

Staffan

[Monshi]

Dynamiska områden? Du har Excel 2007. Använd tabellformatet!

Borde vara möjligt. Annars får du leka med Förskjutning och bygga namngivna referenser med den formeln. Tabellformatet är dock så mycket enklare.

 

Du måste dock se till att varje kolumn har en unik rubrik för att det ska fungera, dvs slå ihop dina celler i rubrikraden. Vill du ha djupare beskrivning av ett område kolumner får dessa ligga utanför tabellen.

som det nu är är tabellen tvetydig med flera kolumner med samma namn.

 

 

ed: Som alltid - testa på en liten tabell och se hur det fungerar och se om det är en väg du önskar gå.

[Prinsviggo]

Dynamiska områden? Du har Excel 2007. Använd tabellformatet!

Borde vara möjligt. Annars får du leka med Förskjutning och bygga namngivna referenser med den formeln. Tabellformatet är dock så mycket enklare.

 

Du måste dock se till att varje kolumn har en unik rubrik för att det ska fungera, dvs slå ihop dina celler i rubrikraden. Vill du ha djupare beskrivning av ett område kolumner får dessa ligga utanför tabellen.

som det nu är är tabellen tvetydig med flera kolumner med samma namn.

 

 

ed: Som alltid - testa på en liten tabell och se hur det fungerar och se om det är en väg du önskar gå.

 

 

En liten fråga, menar du med "Tabell" ett register/databas där man måste ha tydliga fältnamn/kolumnrubriker?

 

I sådant fall, hur kan jag skapa dynamiska områden enkelt i med hjälp av detta? Rapporten (dvs. det jag bifogade) är ju resultatet av en export från ett externt program. Det jag vill hålla dynamiskt är min sammanställning på eget kalkylblad i samma bok. Alla formler jag använder idag bygger på en exempelrapport med 16 poster, nästa export kanske har 25, 47 eller 100 poster. Kan jag komma åt problemet med formler är det bra men går det endast med VBA så kanske jag ska börja med att göras klart hela sammanställningen på eget kalkylblad först för att sedan lösa problemet med dynamiska områden.

 

Är jag på rätt spår?

 

VH

Staffan

 

 

[Shape_sthlm]

En liten fråga, menar du med "Tabell" ett register/databas där man måste ha tydliga fältnamn/kolumnrubriker?

 

I sådant fall, hur kan jag skapa dynamiska områden enkelt i med hjälp av detta?

 

 

Tabellen i sig blir automatiskt ett eget dynamiskt (levande) område.

 

För att skapa en tabell kan du använda dig av en färdig lista eller dataområde, se bifogat.

 

1. Markera en cell i dataområdet och klicka på "Formatera som tabell" (bild 1)

2. Området identifieras oftast automatiskt, om inte annat så får du själv markera (se bild 2)

3. Din tabell är klar. Byt gärna namn uppe i rutan till vänster (bild 3), med ett unikt namn kan du hålla reda på dina tabeller enklare om du har flera.

 

Varje gång du sedan vill hänvisa till detta område/tabeller så kan du anropa "MinTabell". (se bild 4). Mkt smidigt och du behöver aldrig ändra något mer oavsett hur mkt tabellen växer. Resultatet av detta lilla exemplet (bild 5)

 

Läs mer om hur du refererar till tabeller här :

http://office.microsoft.com/sv-se/excel-help/anvanda-strukturerade-referenser-med-excel-tabeller-HA010155686.aspx

 

Bild 6 och 7 visar hur man kan referera/hämta data i en tabell med hjälp av tydliga rubriker på kolumnerna.

 

 

 

/M

[Monshi]

Ah, ok, det är en export från ett annat program, dvs hur exakt tabellen ser ut kan du inte påverka.

 

 

Men några postulat:

• Tabellen ser likadan ut varje gång, dvs kolumnerna är i samma ordning
• Den börjar i samma cell varje gång
• Det finns värden på varje rad i en av kolumnerna
  eller
• Det finns en given text på den sista raden i tabellen

om dessa de två första och ett av de två sista uppfylls kan vi ganska enkelt, med formler, skapa det du önskar.

 

Om det finns värden på varje rad då tar vi två formler och samordnar dem, vi säger att din tabell ligger där den ligger i ditt exempel och inga andra värden än tabellen finns på detta blad. Vi vet med andra ord att första värdet finns på rad 13 men inte var sista värdet finns. Vi vet även att tabellen sträcker sig över 160 kolumner.

 

Då kan vi skriva denna nätta formel i ett namn (inte på bladet, i ett namn som du skapar i Namnhanteraren)

=FÖRSKJUTNING(Uppföljningsrapport!$A$1;12;0;ANTALV(Uppföljningsrapport!$A:$A)-3;160)

-3 eftersom det finns tre rader med skräpvärden vi inte är intresserade av. Definiera namnet och klicka i formelfältet i namnhanterande och du ser vilket område som det refereras till.

 

Jag valde enbart dataområdet, rubrikerna är inte unik och inte intressanta därmed.

 

Hur får du ut data ur detta då?

Säg att du döpt namnet till tbData.

 

Du vill summera data över kolumnen TotalTid vilken du vet är kolumn nummer fem inom tbData:

=SUMMA(INDEX(tbData;;5))

 

är det ungefär som du tänkt dig? Stämmer mina punkter ovan in på dina data, din tabell?

 

 

Shape - vi kompletterar varandra bra tycker jag!

[Prinsviggo]

Det ser ut som jag fått väldigt bra hjälp i de två senaste posterna, hinner inte kika på det nu men jag lovar att återkomma med feed-back. Tusen tack så länge!

 

Vh

Staffan