Giorno lavorativo non regolamentato del numero di fine settimana di formula di Excel. Definizione delle festività nella pagella

Per determinare i giorni liberi (sabato e domenica) utilizzare la funzione DENNED, che imposta il numero del giorno della settimana. Inserisci questa funzione nella cella C28:

DENNED (C9)

Nella cella successiva - C29 - inserisci la funzione SE:

IF (C28 = 7; TRUE; IF (C28 = 1; TRUE; FALSE))

La funzione IF opera secondo lo schema descritto di seguito:

  • Il giorno della settimana è definito nella cella C28 un sabato?
  • Se sì, il valore restituito è VERO.
  • Altrimenti, vai al prossimo IF.
  • Se il giorno della settimana è domenica, il valore restituito è VERO.
  • Altrimenti, restituisce FALSE.

Contabilità fine settimana e festività

Nella fase successiva, dovremo creare una formula che, in base a una delle condizioni (una festività, un giorno libero o una festività, in coincidenza con una vacanza), restituisca il valore VERO. Intendiamo posizionare questa formula nella cella di NWD.

Per risolvere il problema, usiamo la funzione logica OR. Questa funzione restituisce il valore TRUE se almeno uno dei suoi argomenti ha il valore TRUE e il valore è FALSE se tutti gli argomenti hanno il valore FALSE.

La funzione OR ha la seguente sintassi:

OR (logical_value1; logical_value2; ...)

Qui logic_value1, logical_value2, ... è da 1 a 30 condizioni verificabili che possono avere il valore TRUE o il valore FALSE.

Fig. 7.12.

Nel primo argomento, dobbiamo specificare l'indirizzo della cella C29, che definisce il giorno della settimana, e nel secondo argomento, l'indirizzo della cella C27, dove sono impostate le date delle vacanze.

La formula nella cella SZO dovrebbe essere:

OR (C29; C27)

Esegui funzioni di nidificazione:

  • La formula dalla cella C25 è racchiusa nella formula dalla cella C26: = UNM (CDF (C9; DateFlash; 1; FALSE))
  • - La formula risultante verrà incorporata nella formula dalla cella C27: = NOT (UNM (CDF (C9; DateFlash; 1; FALSE))))
  • La formula dalla cella C28 viene inserita nella formula dalla cella C29: = IF (DENNED (C9) = 7 / TRUE; IF (DENNED (C9) = 1; TRUE; FALSE))
  • La formula risultante è incorporabile nella formula di cellula SZO: OR = (IF (WEEKDAY (C9) = 7 ;: TRUE; IF (WEEKDAY (C9) = 1, VERO, FALSO)); C27)

Trasferimento nel fine settimana

In pratica, ci sono casi di trasferimenti nel fine settimana. Nel nostro rapporto, la reazione a tali trasferimenti non è fornita. Ora risolviamo questo difetto. Le date dei giorni che, a causa del trasferimento, sono diventate operaie, verranno inserite nell'area G22: G24 del foglio Vacanze. Assegnare il nome Data Transfer a quest'area (Fig. 7.13).

Le date dei giorni che, a causa del trasferimento, sono diventate nei fine settimana, verranno aggiunte all'elenco delle festività, che si trova nell'intervallo A15: G16 del foglio Festività (Fig. 7.5).

Ora nella pagella, abbiamo bisogno di una formula che determinerà se l'output è stato posticipato. Per ottenerlo, perfezioneremo la formula dalla cella C27 ​​della carta.


Fig. 7.13.

Copialo dalla barra della formula, incollalo nella cella C31, sostituisci il nome Sì-Preferito con il nome Data Transfer ed elimina la funzione NOT.

La formula risultante nella cella C31 dovrebbe avere la seguente forma:

UNM (CDF (C9; Trasferimento dati; 1; FALSE))

La formula restituisce il valore FALSE se trova la data tra quelle trasferite o il valore TRUE se non lo fa. Pertanto, questa cella conterrà il valore FALSE se il giorno a causa del trasferimento è diventato un giorno lavorativo. Se la cella contiene il valore TRUE, lo stato del giorno non è cambiato.

Ora dobbiamo analizzare i valori delle celle С3О e С31. Il primo contiene un valore booleano che indica se il giorno è un fine settimana o una vacanza. Il valore logico nella seconda cella riflette il fatto che lo stato del giorno è cambiato (cioè, non è diventato dovuto alle vacanze del fine settimana)

La formula per l'analisi dovrebbe funzionare in questo modo. Un giorno è una festività se il suo stato non è cambiato (C31 contiene il valore VERO) ed è festivo, sabato o domenica (la CHIAMATA contiene il valore VERO). In tutti gli altri casi, il giorno funzionerà.

È possibile creare tale formula utilizzando la funzione I. Restituisce il valore TRUE, se tutti gli argomenti sono TRUE e il valore è FALSE, se almeno un argomento è FALSE. Questa funzione appartiene alla categoria Logical e ha la seguente sintassi:

E (logical_value1; logical_value2; ...)

Qui valore_logico1, valore_logico2, ... sono da I a 30 condizioni controllate, che possono avere il valore VERO o il valore FALSE.

Immettere la seguente funzione nella cella C32:

E (C30; C31)

Le formule delle celle СЗ e С31 dovrebbero essere racchiuse in questa funzione. Quanto segue mostra come appare la formula risultante nella cella C32:

AND (OR (IF (DENNED (C9) = 7; TRUE; IF (DBNNED (C9) = 1; TRUE; FALSE)); C27); UNM (RRF (A9; DateTransfer; 1; FALSE))))

Copia le formule dalle celle C27 e C32 alle celle C10 e C11.

Buon pomeriggio, caro lettore!

Oggi voglio semplificare la vita e il lavoro di quegli specialisti i cui compiti includono lavorare con le date, anche se l'articolo sarà utile a molti. Ci sono molti problemi con le date, ma nell'articolo esaminerò e ti mostrerò come calcolare i giorni lavorativi in ​​Excel. Questa funzione è molto utile e vitale per il calcolo degli stipendi e dei timer, specialisti del personale. Penso che i vantaggi del calcolo automatico delle date ti saranno molto utili.

Per iniziare, è necessario sapere che Excel vede qualsiasi data come un valore numerico ordinario che inizia con il numero 1 e corrisponde alla data 1 gennaio 1900. Ad esempio, il 1 gennaio 2018 sarà uguale al numero 43101 e la differenza tra le date sarà 43.100 giorni. In considerazione di ciò, possiamo facilmente manipolare le date, così come i numeri, e questo rende il nostro lavoro molto più semplice.


Questo è un esempio molto semplice e chiaro, in cui è necessario sottrarre la data di inizio dall'ultima data e convertire il valore risultante in un formato numerico. Come puoi vedere, questo può essere fatto senza l'aiuto di funzioni, ma questo non funzionerà per noi per ottenere giorni lavorativi.

  Calcola i giorni lavorativi in ​​Excel tra due date

Quindi, dal momento che il numero di giorni di calendario non ci soddisfa, ma abbiamo bisogno di giorni di lavoro, usiamo la funzione CHRISTMAND per risolvere questo compito.

La sintassi di questa funzione è abbastanza semplice e diretta:

=   CLEANERS (_st_date_; _con_date_; _ [holidays] _), dove:



  Un semplice esempio:

Abbiamo le date per l'inizio del periodo in A2: A5 e le date per la fine del periodo - B2: B5. Usando la nostra funzione, otteniamo l'intervallo di cui abbiamo bisogno nei giorni lavorativi.   Esempio complicato:

Complicare il nostro compito aggiungendo alcune festività nella gamma E2: E5. Quindi la nostra formula riporta i giorni lavorativi e riduce al minimo le festività che cadono nel periodo sottratto.

  Ti consiglio di fissare l'intervallo con le festività, in modo che quando copi la formula non darebbe un errore e fornisse il risultato corretto.

E su questo ho tutto! Spero davvero che tutto quanto sopra sia chiaro per te. Sarei molto grato per i commenti lasciati, in quanto questo è un indicatore di leggibilità e ispira a scrivere nuovi articoli! Condividi con gli amici, leggi e piace!

Questo articolo descrive la sintassi della formula e l'uso della funzione. GIORNO.LAVORATIVO   in Microsoft Excel.

descrizione

Restituisce un numero che rappresenta una data distanziata in avanti o indietro da una data di inizio per un numero specificato di giorni lavorativi. I giorni lavorativi non sono considerati giorni festivi e giorni definiti come giorni festivi. La funzione PAYMENT viene utilizzata per escludere i fine settimana oi giorni festivi quando si calcolano le date di pagamento, le date di consegna previste o il numero di giorni effettivi lavorati.

  Consiglio:   Per calcolare il numero che rappresenta la data distanziata in avanti o indietro di un numero specificato di giorni lavorativi, utilizzando i parametri che determinano il numero di giorni liberi in una settimana e in quali giorni sono, utilizzare la funzione WORK.D.ME.

sintassi

PAGAMENTO (data_iniziale; numero_giorni; [festività])

Gli argomenti per la funzione BROADDAY sono descritti di seguito.

    start_date   Richiesto. Data di inizio

    num_days   Richiesto. Il numero di giorni prima o dopo la data di inizio che non è un fine settimana o una vacanza. Un valore positivo per l'argomento numero di giorni indica una data futura, un valore negativo indica una data nel passato.

    ferie   Opzionale. Un elenco di una o più date, ad esempio le festività pubbliche, da escludere dal calendario di lavoro. L'elenco può essere un intervallo di celle contenenti date o una costante di matrice contenente numeri che rappresentano le date.

  È importante:   Le date devono essere inserite utilizzando la funzione DATA o come risultato del calcolo di altre formule e funzioni. Ad esempio, per indicare la data del 23 maggio 2008, utilizzare l'espressione DATE (2008,5,23). Inserire date come testo può causare problemi.

osservazioni

    In Microsoft Excel, le date vengono memorizzate come numeri consecutivi, consentendo di utilizzarle nei calcoli. Per impostazione predefinita, la data 1 gennaio 1900 è il numero 1 e il 1 gennaio 2008 è il numero 39.448, poiché l'intervallo tra loro è 39.448 giorni.

    Se il valore di uno qualsiasi degli argomenti non è una data valida, la funzione WORK restituisce il valore dell'errore #VALORE!.

    Se la somma degli argomenti "start_date" e "number_days" non è una data valida, la funzione BROKEN restituisce il valore di errore #NUM !.

    Se il valore del numero di giorni non è un numero intero, viene troncato.

esempio

Copia i dati di esempio dalla tabella seguente e incollali nella cella A1 del nuovo foglio di Excel. Per visualizzare i risultati delle formule, selezionali e premi il tasto F2 e poi il tasto Invio. Se necessario, modifica la larghezza delle colonne per vedere tutti i dati.

Questo è un capitolo del libro di Bill Gelena.

Compito: calcola quanti giorni lavorativi scendono tra due date. La funzione di Excel ChISTRABDNI affronta perfettamente l'attività, se hai una settimana lavorativa di 5 giorni con sabato e domenica come fine settimana. Questa nota ti mostrerà come calcolare la settimana lavorativa di qualsiasi durata e qualsiasi serie di fine settimana.

Circa. Baguzina.Va notato che, a partire dalla versione 2010 in Excel, è apparso una funzione più flessibile di CAYOMER DAY.MEJD, che consente di calcolare per qualsiasi durata della settimana lavorativa e qualsiasi (ma permanente) serie di giorni di riposo. Questa funzione verrà descritta nella seconda parte della nota. Tuttavia, l'approccio di Bill Jelena rimane interessante di per sé.

I CLIENTI calcolano il numero di giorni lavorativi tra due date, comprese le date di inizio e di fine. È necessario specificare una data precedente come primo argomento, una data successiva come secondo argomento e definire ulteriormente l'array di giorni festivi come terzo argomento (Figura 1). La formula nelle celle è C3: = CLEANERS (A3; B3; $ E $ 2: $ E $ 11). 17/17/09 deduzione 10.02.09 fornisce 8 giorni di calendario, meno due giorni di ferie e una festività. Totale 5 giorni lavorativi. Tuttavia, se si è "abbastanza fortunati" da lavorare dal lunedì al sabato, la formula standard non affronterà il compito.

Scarica la nota in formato o, esempi in formato

Innanzitutto, determina l'intervallo e chiamalo Festività. Per fare ciò, seleziona l'intervallo insieme al titolo e passa attraverso il menu FORMULA –> Crea da selezionato   (Fig. 2). Assicurati che l'opzione sia selezionata. nella riga sopra. Fai clic su OK nella finestra. Creazione di un nome da un intervallo selezionato.


Dopo aver determinato l'intervallo, immettere nuovamente la formula in C3. Ora apparirà così: = ACQUISTI (A3; B3; festività)

Passiamo ora al calcolo del numero di giorni lavorativi con una settimana lavorativa di 6 giorni (figura 3). Formula in C3: = SUMPRODUCT (- - (COUNCILS (Holidays; LINE (DFSSYL (A3 & ":" & B3))) = 0); - - (DENNYD (LINE (FDSSYL (A3 & ":" & B3)); 2)<7)). Хотя эта формула имеет дело с массивами, она в конечном счете возвращает одно значение, так что вам не нужно использовать Ctrl+Shift+Enter для ввода этой формулы.


Considerare il lavoro della formula in modo più dettagliato. Per tutte le date che vanno dalla data di inizio alla data di fine (inclusa), la formula controlla due cose. Innanzitutto, non è una data una vacanza? In secondo luogo, quali sono le date nell'intervallo di date della domenica.

È necessario un modo rapido per confrontare ogni data, iniziando con A3 e terminando con B3 con un elenco di giorni festivi. In questo esempio (nella cella C3), hai solo 8 giorni, ma nella riga 5 hai più di 300 giorni. La formula utilizza il fatto che le date sono memorizzate in Excel come numeri seriali. Sebbene la cella A3 venga visualizzata il 20 gennaio 2009, Excel archivia effettivamente la data come valore numerico di 39854.

La prima funzione di FOSSL genera una matrice di tutte le date nell'intervallo. Gli argomenti all'interno del DFSBL concatenano una data (memorizzata come un numero) dalla cella A3 con una data da B3, separata da due punti. Il risultato intermedio del calcolo può essere visto nella finestra Calcolo della formula   (Fig. 4). Frammento FOSSIL (A3 & ":" & B3) trasformato in $ 39,854: $ 39,861.


Fig. 4. Il lavoro di un frammento della formula FOSSOL (A3 & ":" & B3)

Di solito, l'argomento DFSE è un intervallo di tipo "A2: IU2". Tuttavia, se avete mai usato riferimento a un intervallo dalla prima colonna (A) per l'ultima colonna, si vedrà che il = SUM (2: 2) è equivalente a = SOMMA (A2: IV2) in Excel 2003, e = SUM (A2: XFD2 ) in Excel 2007. Pertanto, è possibile fare riferimento a intere linee. Il frammento LINE ($ 39854: $ 39861) restituisce un array di otto numeri (39854; 39855; ...; 39861) (Fig. 5).


Ora puoi confrontare questo array di otto numeri con un intervallo di date di vacanza. Frammento COUNTIF (Holidays; LINE (INDIRETTO (A3 & ":" & B3))) diventare un COUNTIF ((39814: 39832: 39860: 39958: 39997: 40063: 40098: 40128: 40143: 40172), (39854: 39855: 39.856: 39857: 39858: 39859: 39860: 39861)). La formula calcola quante volte ogni festività (il primo argomento della funzione COUNTERS) coincide con uno dei valori dell'intervallo di date (il secondo argomento, vedi Fig. 6).


La funzione COUNTERS restituisce 1 se la festività è nell'intervallo di date e 0 se non lo è (figura 7). Dato che non sei interessato alle date delle festività, ma ai lavoratori, stai confrontando il risultato della funzione COUNTIF con zero. (0; 0; 0; 0; 0; 0; 1; 0; 0) = 0 restituisce (TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; VERO;). converte una matrice di valori logici in numerica: (1; 1; 1; 1; 1; 1; 0; 1; 1).


Fig. 8 illustra le fasi di calcolo appena descritte. La colonna E è il valore restituito dalla stringa STRING (DFL (A3 & ":" & B3)). Colonna F - CONTI (festività; (39854; 39855; ...; 39861)) - restituisce 0 se la data nella colonna E non corrisponde a nessuna delle festività e 1 se corrisponde. Ad esempio, la data all'E3, non corrisponde a una delle feste, COUNTIF in F3 restituisce 0. Allo stesso tempo, a F8 delle cellule, è la data - 39860 - trovato nella lista delle date di vacanza davvero COUNTIF in F8 restituisce 1.


Nella colonna G, viene controllato se il risultato del COUNTIFIER è uguale a uno. Se sì, restituisce VERO, altrimenti - FALSO. La colonna H introduce la formula -G, che converte ciascun valore di TRUE in 1 e FALSE in 0. In Fig. 3, le celle H2: H9 sono i risultati virtuali della prima metà della formula, che trova le date, non le festività.

La seconda metà della formula utilizza la funzione DENNED per trovare date non di domenica. La funzione DENNED restituisce il numero ordinale del giorno della settimana e ha due argomenti, il secondo dei quali determina quale giorno della settimana considerare come il primo (Fig. 9).

Nella fig. 8 nella colonna K presenta il risultato virtuale, che restituisce la funzione DENNED con il secondo argomento del frame 2 (Monday = 1). Nella colonna L, il valore DENNED viene confrontato con il numero 7. Il giorno della settimana restituisce VERO, Domenica - FALSO. Nella colonna M, i valori logici vengono convertiti in valori numerici. Infine, nella colonna N vengono moltiplicati i valori delle colonne H e M. Nella colonna N, il valore 1 corrisponde al giorno lavorativo e il valore 0 indica che il giorno è festivo e / o domenica. L'azione nella colonna N corrisponde alla moltiplicazione degli array, che sono il primo e il secondo argomento della funzione SUMPRODUCT (Figura 10). Quando gli elementi corrispondenti di entrambi gli array contengono 1, abbiamo un giorno lavorativo. Quando il valore del primo array o il secondo (o entrambi) è 0, il risultato è 0. Il risultato finale è la somma di zero e uno. Nel nostro caso, il valore nella cella C3 è 6.


Se hai una settimana lavorativa di 7 giorni, devi solo escludere le festività. La formula è semplificata: = SUMPRODUCT (- - (COUNCILS (Holidays; LINE (FAIR) (A3 & ":" & B3)) = 0)).

Il problema diventa più difficile se i giorni liberi sono disposti in modo arbitrario. Ad esempio, un dipendente lavora part-time il lunedì, il mercoledì e il venerdì. Ora quella parte della formula che ha controllato le domeniche dovrebbe controllare 3 specifici giorni della settimana. Si noti che return_type 2 della funzione DENNED non restituisce mai 0. È possibile utilizzarlo come primo argomento della funzione SELECT per indicare quali giorni stanno funzionando. Utilizzando un frammento della formula = SELEZIONA (DENNED ( alcune date, 2), 1,0,1,0,1,0,0) otteniamo 1 per lunedì, mercoledì e venerdì e 0 per i restanti giorni della settimana (Fig. 11).


Poiché la funzione di scelta non è di solito restituisce un array, è necessario immettere la seguente formula utilizzando la scorciatoia da tastiera Ctrl + Maiusc + Ingresso: (= SUMPRODUCT (- - (COUNTIF (Holidays; ROW (INDIRETTO (A3 & ":" & B3))) = 0); - - (SELEZIONA (DENNED (STRING (YEARS (A3 & ":" & B3)); 2); 1; 0; 1; 0; 1; 0; 0))))

Riepilogo: ti viene presentato il concetto di creare una vasta gamma di soli due valori. Ad esempio, = ROW (INDIRETTO ( "1: 10000")) crea un array di 10 000 valori da 1 a 10 000. È possibile utilizzare questo concetto per testare una vasta gamma di date specificando un punto di inizio e di fine.

A partire dalla versione 2010, Excel ha una funzione più flessibile denominata CUSTOMBDAY.MEZhD, che consente di calcolare per qualsiasi durata della settimana lavorativa e qualsiasi (ma permanente) serie di giorni di riposo (Fig. 12). Nella cella C2, la formula ha la forma: = CLEANING.MEURABLE (A2; B2; "0101011"; Holidays).

Sintassi della funzione: NETWORK MEMBER (start_date, end_date; [holiday]; [holidays])

argomento produzione   è facoltativo. Indica quali giorni della settimana sono festivi e non sono inclusi nel numero di giorni lavorativi tra la data di inizio e quella di fine. Il valore può essere impostato dal numero del giorno (17 valori in totale, per ulteriori dettagli, consultare la Guida di Excel) o da una stringa che determina quali giorni sono giorni festivi.

I valori stringa per i giorni della settimana includono sette caratteri, ognuno dei quali rappresenta il giorno della settimana (a partire dal lunedì). Il valore 1 rappresenta i giorni non lavorativi e 0 rappresenta i giorni lavorativi. È possibile utilizzare solo 1 e 0 caratteri in una riga. Se il valore 1111111 viene sempre restituito, la stringa deve essere quotata. Nel nostro caso, il termine "0101011" significa che i lavoratori sono lunedì, mercoledì e venerdì.

 

Potrebbe essere utile leggere: