Datumu ģenerātors

Problēma: Vajag izveidot pietiekami garu rindu/kolonu ar datumiem, kuros ir tikai darba dienas.

Risinājums: Izmantot IF funkciju savienojumā ar datuma funkcijām.

Realizācija: Šo uzdevumu var atrisināt divējādi- gudri un ļoti gudri.

Pirmais variants ir pat ļoti vienkāršs. Ko vajag izdarīt? Pirmajā šūnā (teiksim šūna B2) ierakstam šodienas/starta datumu.

Ierakstam starta datumu

 Turpmākos datumus iegūsim izmantojot loģisko formulu. Ko mums ir jāzina, lai izveidotu nākamo datumu?

1.           Ja pirmais datums ir pirmdiena, otrdiena, trešdiena, ceturtdiena, tad mēs vienkārši rakstam nākamo datumu, izmantojot formulu =B2+1;

2.           Ja pirmais datums ir piektdiena, tad nākamo datumu ir jāatrod ar formulu =B2+3 (sestdiena-tas ir viens; svētdiena- tas ir divi; pirmdiena- tas ir trīs).

Ja nav skaidrs, pārlasi vēlreiz.

Vienīga problēma varētu būt dienas noteikšana. Kā noteikt kura nedēļas diena iekrīt konkrētajā datumā? Ar šo problēmu tiek galā funkcija WEEKDAY(serial_number;[return_type]). Funkcija strādā ļoti vienkārši: serial_number vietā noradām datumu, return_type vietā raksta 2 (nāksies ticēt uz vārda), rezultātā iegūstam dienas kārtas numuru.

Funkcija WEEKDAY

Funkcijas WEEKDAY rezultats

Tagad var uzrakstīt pilnu funkciju (rakstam šunā C2):

=B2+IF(WEEKDAY(B2;2)=5;3;1)

Gatava formula

 Tagad varam paņemt šūnu „aiz maliņas” un pavilkt.

Neaizmista "pavilkt"

Otrais variants paredz iespēju pārbaudīt svētku dienas, ja tās iekrīt darbadienā. Pieņemsim, ka mums ir pieejama tabuliņa ar visiem svētku dienu datumiem (avots- http://www.vid.lv/lv/gada_kalendars/2012). Šī tabuliņa atrodās darba lapā BRĪVDIENAS.

Svētku dienas 2012 gadā

Kā pārbaudīt vai konkrētais datums iekrīt/neiekrīt vienā no šīm datumiem? Excel piedāvā funkciju NETWORKDAYS(start_date;end_date;[holidays]). Šī funkcija pasaka cik darba dienas ir starp diviem datumiem (start_date un end_date). Pie tam ir iespējams ņemt vērā brīvdienas, kuras iekrīt darbadienās (holidays). Ja starta un beigu datums ir vienādi, tad funkcijas rezultāts var būt vai nu 1 (parasta darba diena), vai 0 (brīvdiena).

Funkcija NETWORKDAYS

Funkcija NETWORKDAYS rezultāts

Salīdzinājumā ar pirmo variantu, mums ir grūtāk noteikt cik daudz ir jāpieskaita. Var būt 1 brīvdiena līdz nākamajai darbdienai, bet var būt 3,4,5,6 dienas. Ir vajadzīga formula, kas var noteikt kad ir nākama tuvāka darbadiena.

Ka to noteiksim? Pirmkārt vajag noteikt tuvākos datumus, es pieņēmu, ka nebūs tādu gadījumu, kad būs 7 brīvdienas pēc kārtas. Tāpēc Excel lapa izveidoju sarakstu ar skaitļiem no 1 līdz 7.

Rinda ar skaitļiem

Tas man ļaus ievadīt masīvu formulu ar kuru es noteikšu 7 tuvākos datumus =B2+$A$1:$G$1. Piespiežot Ctr+Shift+Enter Excel atmiņā izveidos septiņus secīgus datumus. Kur mēs pielietosim šo formulu?

Iezīmēsim intervālu B3:H3, ievadīsim tur funkciju

NETWORKDAYS($A$1:$G$1+B2;$A$1:$G$1+B2;BRĪVDIENAS!$B$5:$B$20) un nospiedīsim Ctrl+Shift+Enter.

Ar NETWORKDAYS uzzinām kādas dienas, nākamajā nedēļā, ir darbadienas (1)

Šie skaitļi parāda cik darba dienas ir nākamajās 7 dienās. Tad ir jāuzzina pēc cik dienām ir tās darba dienas. To mēs izdarīsim iepriekšējais formulai pierakstot klāt reizinājumu-

$A$1:$G$1*NETWORKDAYS($A$1:$G$1+B2;$A$1:$G$1+B2;BRĪVDIENAS!B5:B20)

Ar NETWORKDAYS uzzinām kādas dienas pēc kārtas, nākamajā nedēļā, ir darbadienas (1)

Tagad ir redzam, ka tuvākā darba diena ir pēc vienas dienas (1). Tātad ir katru reizi jānosaka minimālo skaitli, kas nav 0. Šādu funkciju būs jāveido pašiem, jo nav funkcijas MINIF vai līdzīgas.

MIN(IF(NETWORKDAYS($B$1:$H$1+B2;$B$1:$H$1+B2;BRĪVDIENAS!$B$5:$B$20)=0;"d";$B$1:$H$1*NETWORKDAYS($B$1:$H$1+B2;$B$1:$H$1+B2;BRĪVDIENAS!$B$5:$B$20)))

Ar šīs funkcijas palīdzību var noteikt minimālo apgabala vērtību, kas nav 0. Visas 0 tiek aizstātas ar „d” un MIN f-ja tos neredz.

Pieskaitām šīs funkcijas rezultātu iepriekšējam datumam un esam visu izdarījuši:

B2+MIN(IF(NETWORKDAYS($A$1:$G$1+B2;$A$1:$G$1+B2;BRĪVDIENAS!$B$5:$B$20)=0;"d";$A$1:$G$1*NETWORKDAYS($A$1:$G$1+B2;$A$1:$G$1+B2;BRĪVDIENAS!$B$5:$B$20)))

Gatava formula

Neaizmirstam nospiest Ctrl+Shift+Enter un pavilkt uz leju, tik, cik tas ir nepieciešams.