Le formule di matrice di Excel sono super potenti. Puoi sostituire migliaia di formule con una singola formula una volta appreso il trucco Ctrl + Maiusc + Invio. Oggi, una singola formula di matrice esegue 86.000 calcoli.
La triskaidekaphobia è la paura di venerdì 13. Questo argomento non risolverà nulla, ma ti mostrerà una formula assolutamente sorprendente che sostituisce 110.268 formule. Nella vita reale, non devo mai contare quanti venerdì 13 si sono verificati nella mia vita, ma la potenza e la bellezza di questa formula illustrano la potenza di Excel.
Dì che hai un amico che è superstizioso su venerdì 13. Vuoi illustrare quanti venerdì tredici ha vissuto il tuo amico.
Imposta il semplice foglio di lavoro qui sotto, con la data di nascita in B1 e =TODAY()in B2. Quindi una formula selvaggia in B6 valuta ogni giorno che il tuo amico è stato vivo per capire quanti di quei giorni erano venerdì e cadevano il 13 del mese. Per me il numero è 86. Niente di cui aver paura.
A proposito, il 17/02/1965 è davvero il mio compleanno. Ma non voglio che tu mi mandi un biglietto di auguri. Invece, per il mio compleanno, voglio che mi lasci spiegare come funziona quella fantastica formula, un piccolo passo alla volta.
Hai mai utilizzato la funzione INDIRETTO? Quando lo chiedi =INDIRECT("C3"), Excel andrà in C3 e restituirà tutto ciò che si trova in quella cella. Ma INDIRETTO è più potente quando si calcola il riferimento di cella al volo. Potresti impostare una ruota dei premi in cui qualcuno sceglie una lettera tra A e C e poi sceglie un numero compreso tra 1 e 3. Quando concateni le due risposte, avrai un indirizzo di cella e qualunque cosa si trovi a quell'indirizzo di cella è il premio . Sembra che abbia vinto un fotolibro invece del soggiorno in resort.
Sai come Excel memorizza le date? Quando Excel mostra il 17/2/1965, memorizza 23790 nella cella, perché il 17/2/1965 era il 23790 ° giorno del XX secolo. Al centro della formula c'è una concatenazione che unisce la data di inizio, i due punti e la data di fine. Excel non utilizza la data formattata. Invece, utilizza il numero di serie dietro le quinte. Così B3&":"&B4diventa 23790: 42167. Che tu ci creda o no, questo è un riferimento di cella valido. Se desideri sommare tutto nelle righe da 3 a 5, puoi usare =SUM(3:5). Quindi, quando passi 23790: 42167 alla funzione INDIRETTO, punta a tutte le righe.
La prossima cosa che fa la formula killer è chiedere il file ROW(23790:42167). Normalmente, passi una singola cella: =ROW(D17)è 17. Ma in questo caso, stai passando migliaia di celle. Quando chiedi ROW(23790:42167)e finisci la formula con Ctrl + Maiusc + Invio, Excel restituisce effettivamente ogni numero da 23790, 23791, 23792 e così via fino a 42167.
Questo passaggio è il passaggio straordinario. In questo passaggio, si passa da due numeri e si "estrae" un array di 18378 numeri. Ora, dobbiamo fare qualcosa con quella serie di risposte. La cella B9 della figura precedente conta solo quante risposte otteniamo, il che è noioso, ma dimostra che ROW(23790:42167)sta restituendo 18378 risposte.
Semplifichiamo drasticamente la domanda originale in modo da poter vedere cosa sta succedendo. In questo caso troveremo il numero di venerdì di luglio 2015. La formula mostrata sotto in B7 fornisce la risposta corretta in B6.
Al centro della formula c'è ROW(INDIRECT(B3&":"&B4)). Ciò restituirà le 31 date nel luglio 2015. Ma la formula quindi passa quelle 31 date alla WEEKDAY(,2)funzione. Questa funzione restituirà un 1 per lunedì, 5 per venerdì e così via. Quindi la grande domanda è quante di quelle 31 date restituiscono un 5 quando vengono passate alla WEEKDAY(,2)funzione.
È possibile visualizzare la formula calcolata al rallentatore utilizzando il comando Valuta formula nella scheda Formula della barra multifunzione.
Ciò avviene dopo che INDIRETTO converte le date in un riferimento di riga.
Nel passaggio successivo, Excel sta per passare 31 numeri alla funzione WEEKDAY. Ora, nella formula killer, passerebbe 18.378 numeri invece di 31.
Ecco i risultati delle 31 funzioni WEEKDAY. Ricorda, vogliamo contare quanti sono 5.
Il controllo per vedere se l'array precedente è 5 restituisce un intero gruppo di valori True / False. Sono disponibili 5 valori True, uno per ogni venerdì.
Non posso mostrarti cosa succede dopo, ma posso spiegarlo. Excel non può SOMMARE un mucchio di valori True e False. È contro le regole. Ma se moltiplichi quei valori True e False per 1 o se usi la funzione double-negative o N (), converti i valori True in 1 e i valori False in 0. Invia quelli a SUM o SUMPRODUCT e lo farai ottenere il conteggio dei valori True.
Ecco un esempio simile per contare quanti mesi hanno un giorno 13 in essi. È banale pensare a questo: ogni mese ha un tredicesimo, quindi la risposta per un anno intero è meglio essere 12. Excel sta facendo i conti, generando 365 date, inviandole tutte alla funzione DAY () e calcolando quante finiscono il 13 del mese. La risposta, come previsto, è 12.
La figura successiva è un foglio di lavoro che fa tutta la logica la formula del killer mostrata all'inizio di questo argomento. Ho creato una fila per ogni giorno in cui sono stato vivo. Nella colonna B, ottengo il GIORNO () di quella data. Nella colonna C, ottengo il WEEKDAY () della data. Nella colonna D, B è uguale a 13? Nella colonna E, è C = 5? Quindi moltiplico D * E per convertire True / False in 1/0.
Ho nascosto molte righe, ma ti mostro tre giorni casuali nel mezzo che sono sia un venerdì che il 13.
Il totale in F18381 è lo stesso 86 restituito dalla mia formula originale. Un ottimo segno. Ma questo foglio di lavoro ha 110.268 formule. La mia formula killer originale fa tutta la logica di queste 110.268 formule in un'unica formula.
Aspettare. Voglio chiarire. Non c'è niente di magico nella formula originale che diventa intelligente e accorcia la logica. Quella formula originale sta davvero facendo 110.268 passaggi, probabilmente anche di più, perché la formula originale deve calcolare due volte l'array ROW ().
Trova un modo per usarlo ROW(INDIRECT(Date:Date))nella vita reale e inviamelo in una e-mail (pub at dot com). Manderò un premio alle prime 100 persone per rispondere. Probabilmente non è un soggiorno in un resort. Più probabilmente un Big Mac. Ma è così che va con i premi. Ci sono un sacco di Big Mac e non molti soggiorni in resort.
Ho visto per la prima volta questa formula pubblicata sul forum nel 2003 da Ekim. Il merito è stato dato a Harlan Grove. La formula è apparsa anche nel libro di Bob Umlas This Isn't Excel, It's Magic. Mike Delaney, Meni Porat e Tim Sheets hanno tutti suggerito il trucco meno / meno. SUMPRODUCT è stato suggerito da Audrey Lynn e Steven White. Grazie a tutti.
Guarda un video
- Esiste una classe segreta di formule denominate Formule di matrice.
- Una formula di matrice può eseguire migliaia di calcoli intermedi.
- Spesso richiedono di premere Ctrl + Maiusc + Invio, ma non sempre.
- Il miglior libro sulle formule degli array è Ctrl + Maiusc + Invio di Mike Girvin.
- INDIRETTO ti consente di utilizzare la concatenazione per creare qualcosa che assomigli a un riferimento di cella.
- Le date sono ben formattate ma vengono memorizzate come numero di giorni dal 1 gennaio 1900.
- La concatenazione di due date punterà a un intervallo di righe in Excel.
- Chiedendo il
ROW(INDIRECT(Date1:Date2))"pop out" un array di molti numeri consecutivi - Utilizzo della funzione GIORNO.SETTIMANA per capire se una data è venerdì.
- Quanti venerdì ci sono in questo luglio?
- Per vedere una formula calcolata al rallentatore, utilizzare lo strumento Valuta formula
- Quanti tredicesimi si verificano quest'anno?
- Quanti venerdì 13 sono passati tra due date?
- Controlla ogni data per vedere se il WEEKDAY è venerdì
- Controlla ogni data per vedere se il GIORNO è 13
- Moltiplica questi risultati usando SUMPRODUCT
- Usa - per convertire Vero / Falso in 1/0
Trascrizione del video
Impara Excel dal podcast, episodio 2026 - La mia formula preferita in tutto Excel!
Durante il podcast dell'intero libro, fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist!
Va bene, era il trentesimo argomento del libro, eravamo un po 'alla fine della sezione della formula, o nel mezzo della sezione della formula, e ho detto che devo includere la mia formula preferita di tutti i tempi. Questa è solo una formula incredibile, che tu debba contare il numero venerdì 13 o meno, apre un mondo in un'intera area segreta di Excel chiamata Array Formulas! Inserisci una data di inizio, una data di fine e questa formula calcola il numero di venerdì 13 che si sono verificati tra quelle due date. In realtà sta facendo cinque calcoli ogni singolo giorno tra quelle due date, 91895 calcoli + SOMMA, 91896 calcoli che avvengono all'interno di questa piccola formula, va bene. Ora, alla fine di questo episodio, sarai così affascinato dalle formule di matrice. Voglio sottolineare,il mio amico Mike Girvin ha il miglior libro sulle formule degli array chiamato "Ctrl + Maiusc" Invio ", questa è una stampa recente con la copertina blu, una volta era una copertina gialla e verde. Ora, qualunque sia quello che ottieni, è un grande libro, stesso contenuto sia in quello giallo che in quello verde.
Va bene, iniziamo dall'interno di questo, con una formula che potresti non aver sentito chiamata INDIRETTO. INDIRETTO ci permette di concatenare, o in qualche modo costruire un po 'di testo che assomiglia a un riferimento di cella. Va bene, diciamo che qui abbiamo una ruota dei premi e ti ho appena chiesto di scegliere tra A, B e C. Va bene, quindi scegli questo e scegli C, quindi scegli questo e scegli 3, va bene, e il tuo premio è un soggiorno in un resort, perché è ciò che è memorizzato in C3. E la formula qui è concatenata insieme qualunque cosa da C5 e qualunque cosa sia in C6 usando il & e poi passandolo a INDIRETTO. Quindi = INDIRETTO (C5 e C6), in questo caso, è C3, che deve essere un riferimento bilanciato. INDIRETTO dice "Ehi, andremo a C3 e restituiremo la risposta, va bene?" In Lotus 1-2-3 questa era chiamata funzione @@,in Excel lo hanno rinominato in INDIRETTO. Va bene, quindi sei nell'INDIRETTO, ora ecco la cosa incredibile che sta accadendo lì dentro.
Abbiamo due date, in che modo Excel memorizza le date, 17/02/1965, è solo una formattazione. Se siamo andati a vedere il numero effettivo dietro questo, è 23790, il che significa che sono passati 23790 giorni dall'1 / 1/1900 e 42167 giorni dall'1 / 1/1980. Su un Mac sarà dall'1 / 1/1904, quindi le date saranno circa 3000 di sconto. Va bene, è così che Excel lo memorizza, ce lo mostra però, grazie a questo formato numerico come data, ma se concatenassimo insieme B3 e a: e B4, in realtà ci darebbe i numeri memorizzati dietro le quinte. Quindi = B3 & ”:” & B4, e se lo passassimo a INDIRETTO, in realtà punterà a tutte le righe da 23790 a 42167.
Quindi c'è l'INDIRETTO di B6, ho chiesto la RIGA di quello, questo mi darà un sacco di risposte, e per capire quante risposte ho usato contano, va bene. E per far funzionare questo, se premo solo Invio, non funziona, devo tenere premuto Ctrl e Maiusc e premere Invio, e vedi, questo aggiunge la () intorno alla formula qui. Indica a Excel di entrare in modalità super formula, modalità formula matrice e fare tutti i calcoli per tutto ciò che è saltato fuori da quell'array, 18378, va bene. Quindi, questo è un trucco straordinario, indiretto di date1: date2, passalo alla funzione ROW, ed ecco un piccolo esempio.
Quindi vogliamo solo capire quanti venerdì si sono verificati in questo luglio, ecco una data di inizio, ecco una data di fine e per ciascuna di quelle righe chiederò il WEEKDAY. WEEKDAY ci dice che giorno della settimana è, e qui nell'argomento 2 Fridays avrà un valore di 5. Quindi, sto cercando la risposta e sceglieremo questa formula, andiamo su Formule, e Valuta la formula e Valuta la formula è un ottimo modo per vedere una formula calcolata al rallentatore. Quindi c'è B3, il 1 ° luglio, e vedi che cambia in un numero, e poi uniamo i due punti, giusto, c'è il B4, che cambierà in un numero, e ora otteniamo il testo, 42186: 42216. A questo punto, lo passiamo alla RIGA, e quella semplice piccola espressione si trasformerà in 31 valori proprio qui.
Ora, nell'esempio in cui avevo tutto dal 1965 al 2015, sarebbero saltati fuori 86000 valori, giusto, e non vuoi farlo e valutare la formula, perché sarebbe un po 'folle, va bene? Ma puoi vedere cosa sta succedendo qui con il 31, e ora sto passando quei 31 giorni alla funzione WEEKDAY e otteniamo 3-4-5. Quindi 3 significa che era un mercoledì, e poi 4 significa che era un giovedì e poi 5 significa che era un venerdì. Prendi tutti quei 31 valori e vedi se sono = 5 che è un venerdì, e avremo un mucchio di FALSE e TRUE, quindi mercoledì, giovedì, venerdì e poi 7 celle dopo sarebbero le successive TRUE, eccezionale!
Va bene, quindi in questo caso abbiamo 5 TRUE e 26 FALSE, per sommarli, devo convertire FALSE in 0 e TRUE in 1, e un modo molto comune per farlo è usare il - . Va bene, sfortunatamente non ha mostrato la risposta lì, dove abbiamo visto un intero gruppo di 1 e 0, ma in realtà è quello che succede, quindi SUMPRODUCT lo somma e ci porta al 5. Qui sotto, se vogliamo capire quanti del 13 del mese ci sono stati quest'anno, da questa data di inizio a questa data di fine, processo molto simile. Anche se avremo 365, passalo alla funzione GIORNO e controlla per vedere quanti sono 13, va bene. Per l'esempio di riga 92000, sai, stiamo ottenendo il giorno, stiamo ottenendo il giorno della settimana, controllando se il, GIORNO = 13, controllando se il GIORNO DELLA SETTIMANA = FALSO, moltiplicando questo * questo,e solo nei casi in cui è un venerdì 13 ciò finisce come VERO. Il SUMPRODUCT quindi dice "Somma tutti quelli in su", ed è così che otteniamo 86, letteralmente 91895 calcoli + SOMMA, 91896 che accadono all'interno di questa formula, è pazzamente potente! Vai a comprare il libro di Mike, è un libro fantastico, ti aprirà un intero mondo di formule Excel e, in realtà, dovresti semplicemente acquistare entrambi i libri. Acquista il mio libro, acquista il libro di Mike e avrai una fantastica collezione che ti accompagnerà per il resto dell'anno.ti aprirà un intero mondo di formule Excel e in realtà dovresti semplicemente acquistare entrambi i libri. Acquista il mio libro, acquista il libro di Mike e avrai una fantastica collezione che ti accompagnerà per il resto dell'anno.ti aprirà un intero mondo di formule Excel e in realtà dovresti semplicemente acquistare entrambi i libri. Acquista il mio libro, acquista il libro di Mike e avrai una fantastica collezione che ti accompagnerà per il resto dell'anno.
Va bene, quindi ricapitolare: esiste una classe segreta di formule chiamate formule di matrice e la formula di matrice può eseguire migliaia di calcoli intermedi. Di solito richiedono di premere Ctrl + Maiusc + Invio, ma non sempre, e il miglior libro sulle formule di matrice è il libro "Ctrl + Maiusc + Invio" di Mike Girvin. Va bene, quindi INDIRETTO ti consente di utilizzare la concatenazione per creare qualcosa che assomiglia a un riferimento di cella, quindi INDIRETTO va a quel riferimento di cella. La concatenazione di due date con i due punti punterà a un intervallo di righe in Excel, quindi chiedendo la RIGA dell'INDIRETTO di data1: data2 verrà visualizzato un array di molti numeri consecutivi, forse 31, forse 365 o forse 85000. Controlla ogni data per vedere se WEEKDAY = venerdì, controlla ogni giorno per vedere se DAY = 13, moltiplica quelle due matrici di TRUE e FALSE utilizzando SUMPRODUCT. In molti casi noi 'userò - per convertire TRUE / FALSE in 1 e 0 per consentire a SUMPRODUCT di funzionare. È una formula fantastica, non l'ho creata, l'ho trovata sulla bacheca, mentre ci lavoravo, sono tipo "Wow, è davvero fantastico!"
Va bene, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2026.xlsx
