TRIKOVI U EXCELU 146. DEO – Najinteresantnija formula na svetu

Napravite niz datuma od dva datuma

PROBLEM KORISNIKA: U koloni A imam početni datum a u koloni B krajnji. Treba da vidim koliko je dana između ta dva datuma palo u petak 13.

REŠENJE: Ovo rešava jedna od najboljih formula koju sam ikada video. Ta jedna formula zameniće 108.000 formula (Slika 1). Mada ova formula utvrđuje broj petaka 13, ovaj metod možete koristiti svaki put kada treba da uporedite s nečim svaki datum između dva datuma.

Prvo ću vam pokazati formulu pa ću je zatim detaljno objasniti.

Objašnjenje se odnosi na red br. 2. Isti koncept važi i za red br. 3.

1. Zapamtite da se datumi zapravo memorišu kao broj dana koji su prošli od 1. januara 1900. godine. Datum u ćeliji A2 zapravo je memorisan kao 41680.

2. Datum u ćeliji B2 zapravo je memorisan kao 41691.

3. Povežite ta dva broja dvotačkom između njih. Dobićete 41680:41691. To je zapravo ispravna Excel referenca. Ona upućuje na sve redove između 41680 i 41691.

4. Uzmite tekst iz 3. koraka i ubacite ga u funkciju INDIRECT(41680:41691). Pošto je to ispravna Excel referenca, sada upućujete na opseg svih redova od 41680 do 41691.

5. Na referencu iz 4. koraka primenite funkciju ROW(). Sada imate niz brojeva redova:

{41680; 41681; 41682; 41683; 41684; 41685; 41686; 41687; 41688; 41689; 41690; 41691}. Imajte u vidu da je ovo relativno mali niz. U redu br. 3 biće 18.000 brojeva u nizu.

6. Kod preostalih koraka prestanite da o njima razmišljate kao o brojevima i počnite da ih tretirate kao datume.

7. Za svaki broj u nizu iz 5. koraka primenite funkciju WEEKDAY(). Dobićete {2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6}. U ovoj verziji funkcije WEEKDAY ponedeljak je 1 a petak je 5.

8. Proverite da li su rezultati 6. koraka jednaki broju 5 što znači petak. Sada imate niz vrednosti tačno/netačno (True/False):

{False; False; False; True; False; False; False; False; False; False; True; False}.

9. Vratite se na niz iz 5. koraka pa ga stavite u funkciju DAY(). To će dati niz delova datuma koji se odnose samo na dane:

{10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}.

10. Uzmite niz iz 8. koraka pa proverite da li je jednak broju 13.

{False; False; False; True; False; False; False; False; False; False; False; False}.

11. Pomnožite niz iz 7. koraka nizom iz 9. koraka. Ako oba niza imaju TRUE na istom mestu, dobićete broj 1, a u suprotnom broj 0. Dakle, dobijate:

{0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}.

Moglo bi biti korisno da se kalkulacija koja se odvija u Excel-u prikaže na slici (Slika 2).

    Zamislite kalkulaciju za ćeliju C3. Umesto 12 stavki u svakom nizu, ovde ima po 18.000 stavki. Ova jedna formula uzima dve ćelije s datumima i spaja ih u više nizova od po 18.000 stavki.