IZBORNIK HOME FORUM ACCESS 2003 EXCEL 2003 WORD 2003  .
   
   
HOME
FORUM Win Tips&Tricks
   
KAKO INSTALIRATI
WINDOWS XP ?
Kako instalirati Win XP sa USB STICKA
Kako instalirati WINDOWS 7 ?
Naučite za 15 minuta raditi u Windows XP
Naučite Internet Explorer i Outlook Express
za 15 minuta
Kako kreirati BOOT CD za instalaciju Win95
   
MS OFFICE 2003
MS OFFICE 2007
   
   
HOME NETWORK
tutorijal za mreže
Network Windows 7 - XP
   
ZANIMLJIVI LINKOVI
BROJEVNI SUSTAVI
(DEC, OKT, BIN, HEX )
CMD - Command Prompt
CISCO - CCNA tutoriali
VLSM and SUBNETTING
   
Tutorijali za phpBB forum
JAVASCRIPT
VISUAL BASIC 6.0
AUTOCAD 2007
 

Adsense sponzor



MICROSOFT EXCEL 2007 - Kako zbrojiti vrijednosti između dva datuma uz uvjet - Zbroj vrijednosti između datuma

Kako zbrojiti 10 zadnjih dodanih ili promijenjenih vrijednosti u stupcu
(Copy last 10 added or changed value )

Search This Web Site



Kopiranje i zbrajanje 5 ili 10 zadnjih dodanih ili promijenjenih vrijednosti
Copy and SUM last ten changed data (value)

Općenito o zbrajanju brojeva u Excelu pogledajte na linku ZBRAJANJE U EXCELU

Ako imate potrebu zbrojiti ili kopirati zadnje unesene (promijenjene) vrijednosti u ćelijama tablice podataka tada možete iskoristiti kombinaciju formula i dodatnih stupaca. Uzmimo situaciju kao na slici ispod u kojoj imamo u stupcu "A" nekakve vrijednosti (brojeve) koje po potrebi mijenjamo. Ovi brojevi mogu biti i financijski iznosi (novac). Želimo automatski nakon promjene neke vrijednosti (broja) da nam se sortira zadnjih 10 vrijednosti u nekom drugom stupcu, da ne kažem kopiraju i sortiraju od zadnje prema najstarijoj vrijednosti.

Dakle, u stupcu "A" (ovdje sam već izvršio obradu problema pa mi je stupac A sada stupac C u rasponu C2:C50) vršimo unos (upis ili promjenu) vrijednosti (value) a u stupcu "B" (ovdje sam već izvršio obradu problema pa mi je stupac B sada stupac E)

Copy and SUM last ten changed data (value)

Vjerujem da se ovaj problem može riješiti i na drugi način, ali evo jedan koji možda djeluje kompliciran ali nije. Sve suvišne stupce možemo sakriti da se ne vide (kao što je u ovom primjeru na slici iznad). Problem je ipak riješen.

Problem krećemo rješavati tako što ćemo ispred stupca "A" dodati još dva stupca (kolone)

Kako kopirati i sortirati 10 zadnjih dodanih ili izmijenjenih vrijednosti
Copy and sort last ten changed data (value)

Copy and sort  last ten changed data (value)

Na slici iznad uočite slijedeće:

- U stupcu A imamo datum i vrijeme trenutne promjene podatka ili vrijednosti u stupcu C koji dobijemo preko makronarebe
- U stupcu B imamo poredak datuma i vremena iz stupca A. Ovaj poredak se odnosi na redne brojeve unosa podataka. Tako će prvi uneseni podatak imati broj 1 a zadnji uneseni podatak imati broj 49.
- Raspon podataka u stupcu C je C2:C50 (49 redova tj. ćelija). Ovo su osnovni podaci koje unosimo u ćelije ili mijenjamo
- U stupac D upišimo silazni redoslijed od 49 pa na niže do broja 40. (ako trebate više podataka tada napravite veći raspon)
- U stupcu E izdvajamo 10 zadnjih vremenski upisanih podataka i na kraju ih zbrajamo.

Adsense sponzor



1. Rješenje za stupac A je makronaredba.

Kliknite desnu tipku miša na naziv Sheet1 => View Code => i u polje za unos macro code kopirajte ovu makronaredbu.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
'stupac 3 tj. stupac C u koji se upisuju ili mijenjaju podaci
If Target.Column <>
3 Then Exit Sub
'stupac u koji se automatski vrši upis datuma i vremena nakon promjene podataka u stupcu C i pritiska na tipku Enter
Target.Offset(0,
-2) = Date & " " & Time
End Sub

2. Rješenje za stupac B je formula (ćelija B2).
Ovu ARRAY formulu završavate sa Ctrl+Shift+Enter. Kopirajte je prema dolje

=SUM(1*(A2>$A$2:$A$50))+1+IF(ROW(A2)-ROW($A$2)=0;0;SUM(1*(A2=OFFSET($A$2;0;0;INDEX(ROW(A2)-ROW($A$2)+1;1)-1;1))))

3. Rješenje za stupac E je formula (ćelija E2). Kopirajte je prema dolje
U ovoj formuli uočite da je uzet raspon podataka B2:C50. Dakle obuhvaća dva stupca i obavezno apsolutne adrese raspona podataka.

=VLOOKUP(D2;$B$2:$C$50;2;FALSE)

Ovim smo završili sve potrebne radnje, sakrijmo stupce A, B i D.



Pojašnjenje:

Prilikom upisa podatka u stupcu E ili promjene vrijednosti makronaredba automatski u stupac A postavlja datum i vrijeme za izmijenjenu vrijednost u istom redu. Ovaj podatak je trajan i Excel ga pamti u momentu pritiska na tipku Enter. Pretpostavka je da nećete u jednoj sekundi promijeniti ili upisati dva ili više podataka. Formula u stupcu B vrši brojevni poredak podataka iz stupca A. Dakle automatski nakon upisa u stupcu C i promjene vremena u stupcu A mijenja se i podatak u stupcu B.

U stupcu E funkcija VLOOKUP pronalazi pripadajuću (financijsku) vrijednost u stupcu C za broj poretka iz stupca D.

Također pogledajte tutorijal => Kako automatski kopirati ćeliju nakon pritiska na tipku Enter


Zbroj 10 zadnjih vrijednosti u stupcu
Sum last Nth value in column

Ovdje opet imamo dva načina zbroja deset zadnjih vrijednosti (ćelija) u kojima se nalazi pozitivna vrijednost.
U prvom slučaju formula ignorira NULU (0) kao vrijednost.
Naš cilj je zbrojiti zadnjih deset ćelija u stupcu B koje sadrže vrijednost veću od nule (0).

PRVI NAČIN:

Ovo je osnovna formula koju dupliramo deset puta =VLOOKUP(LARGE($A$2:$A$30;1);$A$2:$B$30;2;FALSE)
Large funkcija u ovoj formuli uzima najveću vrijednost po redu koju joj zadamo brojem (crveno u formuli) a Vlookup na osnovu Large rezultata pronalazi podatak u stupcu A za dotični rezultat funkcije Large i vraća podatak iz stupca B za pripadajući red. Pogledajte sintaxu funkcije VLOOKUP i LARGE.

Ispred stupca u kojem se nalaze vrijednosti dodajmo POMOĆNI stupac u kojem ćemo kreirati uzlazno brojevno stanje svih redova po redoslijedu za vrijednosti iz stupca B. U ćeliju A2 upišimo formulu =IF(B2>0;1;"") a u ćeliju A3 upišimo formulu
 =IF(B3>0;A2+1;"") i kopirajmo je do kraja. Sada imamo redne brojeve za sve vrijednosti koje su veće od NULE u stupcu B.

Kako zbrojiti 10 zadnjih ćelija u kojima se nalazi vrijednost u stupcu Dakle u ćeliji u kojoj želimo zbroj zadnjih deset vrijednosti (u ovom slučaju to je D28) duplirajmo 10 puta funkciju Vlookup u kombinaciji sa SUM funkcijom.

Ta formula izgleda ovako:

=SUM(VLOOKUP(LARGE($A$2:$A$30;1);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;2);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;3);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;4);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;5);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;6);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;7);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;8);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;9);$A$2:$B$30;2;FALSE);VLOOKUP(LARGE($A$2:$A$30;10);$A$2:$B$30;2;FALSE))

Jeste predugačka ali formula rješava problem pojavljivanja NULE (0) u stupcu B.

U koliko vam treba manje ćelija za zbroj jednostavno duplirajte Vlookup onoliko puta koliko trebate.

DRUGI NAČIN:

Problem sa slike iznad možemo riješiti i bez pomoćnog stupca ali tada je problematična pojava NULE kao broja u nekoj od ćelija.

Ova formula slična je prethodnoj a osnovna formula je =OFFSET(B2;MATCH(E1+306;B2:B30;1)-1;0) koju dupliramo 10 puta.
Match funkcija u ovom slučaju pretražuje range i vraća broj popunjenih redova a Offset funkcija vrši pomak za broj redova odozdo prema gore (crveni broj u formuli) i vraća podatak (vrijednost) iz ćelije dotičnog reda. Dakle zadnju popunjenu ćeliju određuje crveni broj. Drugu ododzdo određuje broj -2, treću -3 itd itd...
Jednostavnije rečeno umanjuje ukupan broj redova za rezultat funkcije Match i pronalazi dotičnu vrijednost. Isprobajte "Evaluate Formula" na njoj i vidjet ćete o čemu se radi.

Ova formula također može riješiti problem zbroja zadnjih deset ćelija koje sadrže pozitivnu vrijednost (a da nije nula). Uočite da je ovdje korištena funkcija OFFSET i MATCH za razliku od gornje formule gdje je korištena funkcija VLOOKUP i LARGE.
Ovdje je još jedna razlika, ako koristimo Vlookup tada trebamo pomoćni stupac a ako koristimo Offset tada ne trebamo pomoćni stupac.
Nemojte da vas u formuli zbunjuje E1 jer to nije ćelija iako formula pokazuje na nju.
Taj E1+306 možemo zamijeniti i sa 9,99999999999999E+306

=SUM(OFFSET(B2;MATCH(E1+306;B2:B30;1)-1;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-2;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-3;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-4;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-5;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-6;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-7;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-8;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-9;0);OFFSET(B2;MATCH(E1+306;B2:B30;1)-10;0))

Ako želite zbrojiti samo 10 zadnjih vrijednosti u stupcu B bez obzira na poredak tada iskoristite ovu formulu. Broj 9 u formuli je promjenjiv a odnosi se na zadnjih 10 ćelija, pa je tako za zadnjih 7 ćelija ovaj broj 6 itd. Dakle broj posljednjih ćelija koje želimo zbrojiti minus 1. U ovom slučaju ne treba nam pomoćni stupac
NAPOMENA: U ovom drugom načinu u ćelijama stupca B ne smije biti vrijednost NULA (0). Ako su vrijednosti rezultat neke formule tada pomoću IF funkcije treba dobiti praznu ćeliju umjesto nule.

=SUM(INDEX(B:B;MATCH(9,99999999999999E+307;B:B;1)):INDEX(B:B;MATCH(9,99999999999999E+307;B:B;1)-9;0))


Na jednom mjestu popis svih tema vezanih za kopiranje (copy) u Excelu: Tutorijali vezani za radnje kopiranja u Excelu

©- 2006 - 2020 - IvanC  - Sva prava pridržana.  ic.ims.hr