Just nu i M3-nätverket
Gå till innehåll
Nippegubbe

Söka ett tidigare datum i en databas

Rekommendera Poster

Hej alla, jag ska nu ge mig ut på djupt vatten och försöka skapa lite formler i en arbetsbok. Nu har inte jag jobbat i Excel tidigare så mina kunskaper är mycket ytliga. Det är tänkt att jag för en klubbs räkning ska läsa av ett antal mätare som finns på en bränsletank. Alla som får tanka bränsle har egen nyckel som sätt i ett räkneverk för att pumpen ska kunna startas. Det finns då tio olika nycklar så när rätt nyckel sätts i rätt räkneverk debiteras det sedan den nyckelns kontoägare.

Jag ska då läsa av dessa konton/räkneverk en gång i månaden och jag vill då när jag matar in nya uppgifter att en cell kollar med månaden innan vad räkneverket för samma konto stod på den månaden så att jag får fram mätarställning denna månad och mätarställning förra månaden och det ska ju då till generera hur många liter bränsle ska klubben fakturera för X månad.

Men mitt stora problem är att ja inte kan lista ut hur jag jämföra datum och då bara en månad tillbaka inte två. Eller rättare sagt om nu en månad inte har blivit avläst eller om vi bara ska läsa av varannan månad så ska det läsas av. När jag skriver in nyckelnummer så hämtar Excel vilket konto det tillhör. Sånt har jag lyckats lösa, men datum går jag bet på.

Dels så behöver jag få själva databasen rätt från början. Tror det kommer att löna sig på slutet.

 

Förutsättningar:

Vi har tre olika bränsletyper/tankar.

Till varje tank tiotalet nycklar/konton.

Ett huvudräkneverk till varje tank.

 

//Thord

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Som så ofta är en pivotabell lösningen på dina problem om du sköter dina data så fint som du lagt upp dem ovan.

Dvs som i en databas.

Du noterar där enbart händelserna, räknar inget. jag utgår från att mätarställningen är individuell.

Se bifiogat bok med en Pivot-tabell.

 

PivExempel.xlsx

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Ok, jag trodde jag skulle göra pivottabellen långt senare för att göra en lättare läsbar presentation för kassören. Då ska jag tänka om lite.

 

//Thord

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Jag röstar för Monshis lösning. men om du vill göra en formellösning där du hela tiden ser föregående avläsning så kan du få lite verktyg (tabell-formler):

 

Annars kan du testa en formel i den här stilen i "föregående mätarställning"

=PRODUKTSUMMA(MAX([Mätarställning]*([Nyckel]=[@Nyckel])*([Tank]=[@Tank])*([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1))))

Bygger på att din tabell är formaterad som tabell och att jag läst kolumn-namnen rätt (kolumnen tank har jag snott från Monshi). 

 

 

Delar av formlerna:

Sista dagen i föregående månad. 

=SLUTMÅNAD([@[Datum för avläsning]];-1)

 

Villkor för vilka ANDRA rader som har datum som är tidigare eller = detta datum (@-tecknet betyder att det är den här raden du läser på):

([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1))

 

När man sätter ett villkor inom parentes så där så blir värdet av parentesen 1 när det är sant och 0 när det är falsk, så du kan lägga ihop flera villkor genom att multiplicera parenteser. Så fort en parantes är falskt blir hela raden=0

 

Villkor för vilka rader som har datum som är tidigare eller = sista dagen i förra månaden OCH har samma nyckel/tank som denna rad:

([Nyckel]=[@Nyckel])*([Tank]=[@Tank])*([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1))

Det  ger 1 för de rader som uppfyller villkoren och 0 för övriga.

 

Om vi multiplicerar villkorsparenteserna med mätarställningen så är det bara de rader som uppfyller villkoren som får ett värde, övriga blir 0

[Mätarställning]*([Nyckel]=[@Nyckel])*([Tank]=[@Tank])*([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1))

 

Sen är det bara att hämta det högsta värdet med hjälp av MAX

MAX([Mätarställning]*([Nyckel]=[@Nyckel])*([Tank]=[@Tank])*([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1)))

 

Antingen matar du in det som en matrisformel (Ctrl+shift+enter) eller skriver in det i en produktsumma

=PRODUKTSUMMA(MAX([Mätarställning]*([Nyckel]=[@Nyckel])*([Tank]=[@Tank])*([Datum för avläsning]<=SLUTMÅNAD([@[Datum för avläsning]];-1))))

Kräver att alla tank/nyckelkombinationer har fått ett startvärde (före denna månad).

 

Men, nu när du läst så här  långt så röstar jag för Monshis förslag..

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Oj, det blev mycket för en amatör, men jag ska helt klart kika på det.

Men om jag då återgår till pivot tabellen så närjag matade in nya "avläsningar" med nya månader så uppdaterar inte pivottabellen utan den ligger kvar på ursprunget så att säga.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Oj, det blev mycket för en amatör, men jag ska helt klart kika på det.

Men om jag då återgår till pivot tabellen så närjag matade in nya "avläsningar" med nya månader så uppdaterar inte pivottabellen utan den ligger kvar på ursprunget så att säga.

Högerklicka på pivoten och begär uppdatering (fungerar i alla fall om du lagt in dina rådata i excels tabellformat)

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Glömde skriva att det var i Monshis arbetsbok jag provade att lägga till några poster och pivoten uppdaterade inte med dessa poster. När jag då sedan högeklickade och uppdaterade så tappade Excell formatet för datumet.

 

Sen har jag stött på en annan liten tröskel och det är att jag hade tänkt att ha en kolumn där tankad kvantitet står, men när man då börjar att bygga upp database så innebär det att det inte finns någon tidigare avläsning. Jag har då använt =summa för resultatet i kolumnen liter, men skulle vilja ha att om "föregående mätarställning" är noll så ska det stå noll i literkolumnen.

 

 

post-138732-0-90959700-1510596615_thumb.jpg

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Principiellt

OM Föregående mätarställning=0, värdet 0; annars mätarställning-Föregående mätarställning

 

Skrivet för hand, inte kollat mot tabell

=OM([@[Föregående mätarställning]]=0;0;[@[mätarställning]]-[@[Föregående mätarställning]])

 

 

Men nu blev jag lite förvirrad över ditt upplägg. Har varje nyckel ett eget räkneverk, eller måste du räkna ut ut diffen från föregående? Har varje tank ett eget räkneverk?

 

I det här exemplet antar jag att varje tank har sin egen räknare men att nycklarna delar på samma räknare:

Den här formeln är en "bastard" mellan tabellformler och klassisk excelformel. om Mätarställningen står i kolumn E så kan du lägga in formeln i F2:

 

=[@Mätarställning]-OMFEL(PRODUKTSUMMA(MAX((D1:D$2=D2)*(E1:E$2)));0)

Den kommer då att räkna ut hur mycket den här avläsningen skiljer från föregående  avläsning för samma TANK

 

 

Skillnaden mellan förra avläsningen för samma tank och samma nyckel.

=[@Mätarställning]-OMFEL(PRODUKTSUMMA(MAX((D1:D$2=D2)*(B1:B$2=D2)*(E1:E$2)));0)

 

 

Skillnaden mellan förra avläsningen för samma nyckel (oavsettt bränsletyp).

=[@Mätarställning]-OMFEL(PRODUKTSUMMA(MAX((D1:D$2=D2)*(B1:B$2=D2)));0)

 

Se bifogat för ett exempel med summering för sista månaden (obs: mina fejkade data har en massa slump-formler, räknas om genom att trycka på tangenten [F9] (formel-summeringen uppdateras omedelbart medan Pivoten kräver ett högerklick så de visar inte alltid samma sak)

Filen är baserad på Monshis om den känns bekant.

 

formel och PivExempel.xlsx

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Varje bränslesort har en tank och ett huvudräkneverk, första bilden. Sen har kunderna en nyckel ansluten till sitt konto som när den sätts i även ger ström till pumpen på tanken. Och som du ser på andra bilden så har varje nyckel ett räkneverk. Det här är bara ett exempel från en tank. Hoppas det ger lite klarhet i vad det är jag försöker sy ihop och jag har aldrig använt Excel till så här svåra uppgifter tidigare.

 

//Thord

post-138732-0-58974300-1510697788_thumb.jpg

post-138732-0-88991900-1510697803_thumb.jpg

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Ok. Så varje nyckel/tank kombination är en egen serie. Då kan du ju göra ett väldigt enkelt upplägg. 

 

Fråga (#9) "tankad kvantitet": föregående mätarställning (oavsett månad) för SAMMA Tank och nyckel:

=PRODUKTSUMMA(MAX(([Nyckel]=[@Nyckel])*([bränsletyp]=[@bränsletyp])*([Mätarställning]<[@Mätarställning])*([Mätarställning])))

Dvs mätarställningen för samma tank, samma nyckel och där Mätarställningen är mindre än radens värde

 

Sen är det bara att  att ta mätarställning-föregående mätarställning för att få ut tankad volym sedan senast avläsning

=OM([@[Föregående mätarställning]]=0;0;[@Mätarställning]-[@[Föregående mätarställning]])

 

Skapa en extra kolumn som du kallar månad eller liknande:

=TEXT([@[Datum för avläsning]];"ÅÅÅÅ, MMM")

Nu kan du summera tankad volym Utifrån avläsningsmånad. Om du alltid skapar räkningarna när den sista avläsningen för månaden är inmatad så kan du använda avläsningsmånad som förbrukningsmånad.

 

Om du lägger tankens huvudmätare som ett eget nyckelnummer så har du en extern kontroll. Om Tankens månadsförbrukning inte är =  summan av de övriga nycklarnas förbrukning så är någonting fel. 

 

 

Tänkbara problem.

Systemet tar inte hänsyn till när du skickade ut senaste fakturan. Om du t.ex skapade en faktura från avläsningen den 15:e augusti och sedan gjorde extra avläsningar den 20:e augusti, den 5:e och 10:e september så blir det konstigt. Om du baserar räkningen på avläsningarna i September så riskerar du att förlora de tankningar som skett mellan den 15:e och 20:e augusti eftersom den förbrukningen hamnar under augusti (som redan är fakturerad). 

 

Det är inga problem om du alltid baserar faktureringen på den sista avläsningen varje månad. Men alla avläsningar som sker efter månadsräkningen kommer att ställa till det. 

Om du vill ta hänsyn till det måste du nog skapa en hjälptabell där du skriver in datumen för "räkningsavläsningar" och sedan baserar räkningen på skillnaden mellan den sista avläsningen och den senaste "räkningsavläsningen" (eller tidigare).

 

Men som sagt var. Om du gör räkningen när alla avläsningar för månaden är klara så har du inget problem. 

 

 

***ED***

Spelar ingen roll om du skickar räkning per månad, varannan månad eller kvartal. Om du vill använda avläsningsmånad som bas för räkningen så är det bara viktigt att du inte gör någon mer avläsningar den månad som du skapade sista räkningen.

Redigerad av MH_resurrected

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Tack, jag ska försöka göra mig tid för att titta på det där, men innan dess så fick jag lite följdfrågor. Idag så vet jag ännu inte när jag ska ta över den här sysslan så jag försöker förbereda mig så att jag och kassör får det "enkelt". Alla nycklar används inte eller så används de väldigt sällan och de skulle jag vilja slippa mata in om det inte har skett någon förändring på mätarna sedan föregånde läsning. Eller kräver upplägget/formeln att alla nycklar matas in när avläsningen har gjorts? Dumt att redovisa det som det inte har skett någon förändring på. Är det ingen risk att Excel hoppar över en rad och hamnar två avläsningar bak i tiden? Nu är det här även lite årstidsberoende så sommar kommer tankningar vara flera än på vintern.

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Nej. Jag tänkte att du summerar förbrukningen (tankad volym sedan senast avläsning) efter avläsnings-månaden  (förslagsvis med hjälp av Pivottabell som du tänkte från början). Och om det inte finns någon avläsning på nyckel X/tanky under de avläsningsmånader du är intresserad av så får du inga data.

 

"Är det ingen risk att Excel hoppar över en rad och hamnar två avläsningar bak i tiden"

Nej. Om nyckel/tank stämmer och du inte har skrivit in  avläsningen fel (så att den är lägre än tidigare) så kommer formeln för "tankad volym sedan senast avläsning" alltid att jämföra sig ett steg bak i tiden.

 

Problemet är som sagt var att du måste vara säker på att  du har gjort sista avläsningen en månad innan du skapar fakturan.

 

Fråga: Varför matar du inte in Varje tank/nyckel  som en egen kolumn? Det blir visserligen rätt många kolumner datum+27X3 (+3 tank)-kolumner. Men då behöver du bara skriva in datumet till vänster och så för du in värdet för varje nyckel/tank. Det blir betydligt mindre att skriva eftersom tank/nyckel framgår av kolumnrubriken. Du får enklare att se om du matar in helstolligt osv. 

 

Om du vill hålla det riktigt enkelt så bör en avläsning = en fakturaperiod (om du vill slippa krångliga formler). Se bifogat för ett exempel där jag slumpat fram data och du kan ändra ditt fakturadatum för att se hur den beter sig (orkade inte fejka data för tankens huvudmätare så Svinn-funktionen funkar inte).

Jag gillar framför alt att du får bättre koll och formlerna blir enkla.

Bok1.xlsx

Redigerad av MH_resurrected

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Tack för alla "input", jag får mycket att fundera över hur jag vill ha det. Jag har kommit en bit på vägen nu och har skickat ett utkast till kassören för att höra vad han saknar eller vad jag har missat. Så just nu ser det ut så här att jag har en databas som jag då ska peta in avläsningarna på med tiden.

Sedan med Pivottabeller så hämtar jag data (som jag nu tror intresserar kassören) och där då ha kan filtrera ut det han vill ha. Pivottabellerna kommer sedan att få egna flikar då jag har förstått att de kan överlappa varandra när det blir många träffar. Det finns även två listor som jag hämtar data ifrån till databasen.

post-138732-0-64697100-1511253826_thumb.jpg

post-138732-0-48789300-1511253836_thumb.jpg

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Skapa ett konto eller logga in för att kommentera

Du måste vara medlem för att kunna kommentera

Skapa ett konto

Skapa ett nytt konto på vårt forum. Det är lätt!

Registrera ett nytt konto

Logga in

Redan medlem? Logga in här.

Logga in nu



×