Excel Forum Per condividere esperienze su Microsoft Excel

correzzione formula matriciale

  • Messaggi
  • OFFLINE
    maxma62
    Post: 812
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 06/09/2019 21:00
    Salve questa formula matriciale nella colonna B

    =SE.ERRORE(INDICE(TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0;CONFRONTA(0;SE(TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0<>1;CONTA.SE(Grafici!$B$1:Grafici!$B1;TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0));0));"")


    nel foglio "grafici" aggiorna i mesi del foglio "in".
    Nelle colonne seguenti le formule nelle colonne C/D/E/F/G sempre del foglio "grafici"
    aggiorna il conteggio dei mesi dei foglio Si/qu/de/in/pr
    Purtroppo se un mese dei fogli Si/qu/de/pr non è presente nel foglio "in" la formula matricile non si aggiorna.
    La modifica che chiedo è che la formula matriciale aggiorni tutti i mesi presenti in tutti i fogli.
    Spero di essermi spiegato.
    Allego esempio.
    [Modificato da maxma62 06/09/2019 21:08]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 399
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 07/09/2019 16:16
    Buonasera
    io personalmente non ho capito l'obiettivo...prova a spiegare meglio e magari allegare il file con i risultati da ottenere inseriti a mano
  • OFFLINE
    dodo47
    Post: 2.375
    Registrato il: 06/04/2013
    Utente Veteran
    2010
    00 07/09/2019 16:43
    poco chiaro...sembra tu voglia contare quante righe dei singoli mesi ci siano nei vari fogli.

    comunque perchè in col B di grafici non metti semplicemente le date di tutti i mesi dell'anno a partire da gen a dic al posto di quella formula?

    saluti



    [Modificato da dodo47 07/09/2019 16:45]
    Domenico
    Win 10 - Excel 2016
  • OFFLINE
    maxma62
    Post: 812
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 08/09/2019 11:06
    Ciao,
    spero di spiegarmi.
    La formula matriciale in foglio "grafici" celle B2:B25

    =SE.ERRORE(INDICE(TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0;CONFRONTA(0;SE(TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0<>1;CONTA.SE(Grafici!$B$1:Grafici!$B1;TESTO(in!$B$6:$B$1429;"mmm/aaaa")+0));0));"")


    è riferita al foglio "in"
    La modifica che chiedo è che la formula faccia il conteggio per tutti i fogli "si/qu/de/in/pr" in modo che tutti i mesi dei fogli siano presenti.
    [Modificato da maxma62 08/09/2019 11:16]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    dodo47
    Post: 2.378
    Registrato il: 06/04/2013
    Utente Veteran
    2010
    00 08/09/2019 11:26
    Ripeto:
    c'è un motivo per il quale non riporti senza formula tutti i mesi dell'anno a prescindere che esistano nei fogli o meno? Mal che vada ti ritrovi la riga a zero

    saluti



    Domenico
    Win 10 - Excel 2016
  • OFFLINE
    maxma62
    Post: 813
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 08/09/2019 11:31
    Perchè la colonna dei mesi B2:B25 è collegata a un grafico
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    dodo47
    Post: 2.379
    Registrato il: 06/04/2013
    Utente Veteran
    2010
    00 08/09/2019 13:08
    ciao
    a mio avviso è poco probabile che una formula possa fare quello che chiedi.

    In sintesi si tratterebbe di "spazzolare" tutti i fogli alla ricerca di tutte le date (mese-anno) presenti.

    Lo potrebbe fare una macro.

    saluti



    Domenico
    Win 10 - Excel 2016
  • OFFLINE
    maxma62
    Post: 814
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 08/09/2019 19:03
    Questa formula matriciale lavora su 3 colonne A/B/C

    =SE.ERRORE(SE.ERRORE(SE.ERRORE(INDICE($A$2:$A$200;CONFRONTA(0;CONTA.SE($F$1:F1;$A$2:$A$200)+($A$2:$A$200="");0));INDICE($B$2:$B$200;CONFRONTA(0;CONTA.SE($F$1:F1;$B$2:$B$200)+($B$2:$B$200="");0)));INDICE($C$2:$C$200;CONFRONTA(0;CONTA.SE($F$1:F1;$C$2:$C$200)+($C$2:$C$200="");0)));"")
    

    è possibile aggiungere altre 2 colonne D/E?
    Nell'allegato la formula è modificata anche per il controllo su 5 fogli,
    ma non so come aggiingere come per la formula allegata altri 2 fogli

    =SE.ERRORE(SE.ERRORE(SE.ERRORE(INDICE(Foglio1!$B$6:$B$200;CONFRONTA(0;CONTA.SE($G$1:G1;Foglio1!$B$6:$B$200)+(Foglio1!$B$6:$B$200="");0));INDICE(Foglio2!$B$6:$B$200;CONFRONTA(0;CONTA.SE($G$1:G1;Foglio2!$B$6:$B$200)+(Foglio2!$B$6:$B$200="");0)));INDICE(Foglio3!$B$6:$B$200;CONFRONTA(0;CONTA.SE($G$1:G1;Foglio3!$B$6:$B$200)+(Foglio3!$B$6:$B$200="");0)));"")
    [Modificato da maxma62 08/09/2019 19:19]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 400
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 08/09/2019 23:30
    Buonasera
    Se alleghi il file senza formule ma con i risultati da ottenere scritto a mano spiegando come ci arrivi è più facile rispondere
  • OFFLINE
    maxma62
    Post: 815
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 09/09/2019 20:31
    Il file nel post #8
    è composto da 6 fogli

    foglio1/foglio2/foglio3/foglio4/foglio5/mesi

    la formula nella colonna F del foglio mesi
    conta i mesi presenti nelle colonne A/B/C del foglio mesi ma bisogna aggiungere anche le colonne D/F

    la formula nella colonna G del foglio mesi conta i mesi presenti
    nel foglio1/foglio2/foglio3
    ma bisogna aggiungere i fogli4/foglio5

    spero di essermi spiegato
    [Modificato da maxma62 09/09/2019 20:40]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    maxma62
    Post: 816
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 10/09/2019 18:11
    Ciao,
    forse spiegato male o non si può?
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 402
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 10/09/2019 18:39
    Ciao
    Si può fare e te l ho fatta (Mi ha fatto impazzire)...ora sono fuori stasera posto la soluzione
  • OFFLINE
    maxma62
    Post: 817
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 10/09/2019 19:12
    [SM=g27811]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 403
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 10/09/2019 20:04
    Ciao
    premessa:il risultato è comunque una lista univoca e ordinata perchè non scriverle manualmente? comunque
    il quesito è sicuramente molto tosto e ho adattato una soluzione del grande Jos in F2 da attivare con i tasti CTRL+SHIFT+INVIO e trascinare in basso

    =SE(RIGHE($1:1)>MATR.SOMMA.PRODOTTO(( $A$2:$E$49<>"")/CONTA.SE( $A$2:$E$49; $A$2:$E$49&""));"";INDICE(INDICE( $A$2:$E$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1)/COLONNE( $A$2:$E$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1;COLONNE($A$2:$E$49)))));CONFRONTA(PICCOLO(SE(FREQUENZA(SE($A$2:$E$49<>"";CONFRONTA($A$2:$E$49;INDICE( $A$2:$E$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1)/COLONNE( $A$2:$E$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1;COLONNE($A$2:$E$49)))));0));RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49))));CONTA.SE($A$2:$E$49;"<"&INDICE( $A$2:$E$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1)/COLONNE( $A$2:$E$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1;COLONNE($A$2:$E$49)))))));RIGHE($1:1));SE(INDICE( $A$2:$E$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1)/COLONNE( $A$2:$E$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1;COLONNE($A$2:$E$49)))))<>"";CONTA.SE($A$2:$E$49;"<"&INDICE( $A$2:$E$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1)/COLONNE( $A$2:$E$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $A$2:$E$49)*RIGHE( $A$2:$E$49)))-1;COLONNE($A$2:$E$49)))))));0)))

    per quanto riguarda la formula che estrae dai 5 fogli creo una tabella di servizio(eventualmente da nascondere) in K2 del foglio MESI da trascinare a destra e poi in basso

    =SE(INDIRETTO("Foglio"&RIF.COLONNA(A$1)&"!B"&RIF.RIGA($A6))=0;"";INDIRETTO("Foglio"&RIF.COLONNA(A$1)&"!B"&RIF.RIGA($A6)))

    poi in G2 metti la stessa formula di colonna F adattando i riferimenti

    =SE(RIGHE($1:1)>MATR.SOMMA.PRODOTTO(( $K$2:$O$49<>"")/CONTA.SE($K$2:$O$49; $K$2:$O$49&""));"";INDICE(INDICE($K$2:$O$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1)/COLONNE($K$2:$O$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1;COLONNE( $K$2:$O$49)))));CONFRONTA(PICCOLO(SE(FREQUENZA(SE($K$2:$O$49<>"";CONFRONTA( $K$2:$O$49;INDICE( $K$2:$O$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1)/COLONNE( $K$2:$O$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1;COLONNE( $K$2:$O$49)))));0));RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49))));CONTA.SE( $K$2:$O$49;"<"&INDICE( $K$2:$O$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1)/COLONNE( $K$2:$O$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1;COLONNE( $K$2:$O$49)))))));RIGHE($1:1));SE(INDICE( $K$2:$O$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1)/COLONNE( $K$2:$O$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1;COLONNE( $K$2:$O$49)))))<>"";CONTA.SE( $K$2:$O$49;"<"&INDICE( $K$2:$O$49;NUM(SE(1;1+INT((RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1)/COLONNE( $K$2:$O$49))));NUM(SE(1;1+RESTO(RIF.RIGA(INDIRETTO("1:"&COLONNE( $K$2:$O$49)*RIGHE( $K$2:$O$49)))-1;COLONNE( $K$2:$O$49)))))));0)))



    P.S ho messo tutti i riferimenti fino a riga 49 ma li puoi allungare a piacere
    [Modificato da DANILOFIORINI 10/09/2019 20:08]
  • OFFLINE
    maxma62
    Post: 819
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 10/09/2019 20:19
    Ciao danilo, grazie.
    Una formula lunghissima che fa il suo lavoro [SM=g27811]
    Forse è possibile aggiungere alla formula un ordine crescente senza usare i filtri?
    Comunque ti ringrazio, la provo domani in ufficio ma penso funzioni bene.
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    maxma62
    Post: 820
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 10/09/2019 20:38
    Ciao,
    ma qui dove pesca il nome del foglio?

    
    =SE(INDIRETTO("Foglio"&RIF.COLONNA(A$1)&"!B"&RIF.RIGA($A6))=0;"";INDIRETTO("Foglio"&RIF.COLONNA(A$1)&"!B"&RIF.RIGA($A6)))
    
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 404
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 10/09/2019 20:58
    ciao
    la formula già estrae gli univoci in ordine crescente....

    INDIRETTO("Foglio"&RIF.COLONNA(A$1)&"!B"&RIF.RIGA($A6))

    questa parte ad esempio pesca in Foglio1 cella B6
  • OFFLINE
    maxma62
    Post: 821
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 10/09/2019 21:21
    Se io rinomino il foglio1 non funziona più.
    Cosi funziona solo la prima cella poi non cambia il riferimento alla cella resta B6

    =SE(INDIRETTO("Foglio1!B$6")=0;"";INDIRETTO("Foglio1!B$6"))
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa
  • OFFLINE
    DANILOFIORINI
    Post: 405
    Registrato il: 15/01/2016
    Città: ROMA
    Età: 51
    Utente Senior
    2016
    00 10/09/2019 21:28
    Ciao
    io ho fatto le formule in base all'esempio postato...se rinomini il foglio è normale che non funzioni più

    puoi fare le cinque colonne della tabella di sevizio semplicemente cosi

    =tuo foglio!B6

    e trascini in basso l'avevo fatta in quella maniera per fare una formula unica....saluti
  • OFFLINE
    maxma62
    Post: 822
    Registrato il: 28/12/2009
    Città: CITTADELLA
    Età: 62
    Utente Senior
    excel 2007/365
    00 11/09/2019 12:30
    [SM=g27811]
    ____________________________
    versione excel 365 ufficio
    versione excel 2007 casa