Tabella riepilogativa costi

[danibi60]

Buonasera a tutti, come state?

mi rifaccio vivo con una richiesta che, a differenza delle scorse, è a mio parere un po' meno complessa. (o almeno lo spero)

Ho costruito (qui allegata) una tabella riepilogativa (esteticamente non mi appaga, sono onesto) che vorrei poi mutuare per i prossimi mesi sino al 31.12, nella quale inserirò una serie di costi. Il data entry di tali dati sarà interamente manuale e non frutto di estrazioni da gestionali.

in calce a questa tabella ho costruito un mini report che ne consuntiva (con tanto di medie) i risultati.

Ora: questi sarebbero i miei desideri:

1) Si potrebbe graficamente, a Vostro parere, renderla un poi' più gradevole e quindi anche funzionale? se cerco di stampare un report simile mi serve un lenzuolo. Mi è capitato di vedere reportistiche dove attraverso dei menu a tendina si inserivano tutti i dati che confluivano poi in un report, ma non so da dove si comincia.

2) Se questo non fosse possibile, lavorare su questo e creare un foglio che riepiloghi progressivamente i vari mesi nel quale far confluire tutte le formule e i dati ( si può fare questo con una macro oppure è necessario copiare tutte le formule?)

Mi fermo qui e attendo un vostro come sempre saggio consiglio, i quesiti e eventualmente il da farsi.

(nel file: In giallo le aree del data entry manuale)

https://www.dropbox.com/s/xufx2wu3qamxs ... .xlsx?dl=0

Grazie
Daniby

ps. come si fa a togliere "#DIV/0!" dale formule che restano senza risultato??

[Anthony47]

Se la richiesta e' complessa o meno lo devi valutare tu in funzione delle tue competenze; e l'estetica deve essere l'ultima cosa da risolvere dopo aver ottenuto i dati che servono.

Comunque secondo me tu devi fare una tabella analitica in cui registri i movimenti man mano che succedono: Data, Operatore, C, R, Q.li, Valore
Separatamente su questi dati crei un report, tramite una tabella pivot oppure tramite formule.

Ad esempio, in Foglio1 del file allegato ho inserito una tabella dati contenente i campi che (mi pare) devi popolare; per comodita' ho inserito anche una colonna Mese, che mi riporta direttamente il mese relativo alla colonna Data (non sarebbe necessaria, ma e' una semplificazione che non costa tanto); l'ho chiamata TabellaOrigine

Se mentre popoli i dati ti sposti nella tabella usando la freccia a destra (o il tab), quando arrivi sulla colonna "MESE" (che contiene la formula per il calcolo del mese) automaticamente la selezione si sposta alla colonna DATA della riga successiva; se in questo modo arrivi sulla fine tabella allora automaticamente viene aggiunta una nuova riga alla tabella e si puo' continuare l'input dei dati.
Questo e' ottenuto tramite la macro Worksheet_SelectionChange di Foglio1

Nota: sara' quindi impossibile modificare il contenuto dell'ultima colonna della tabella; se si vuole effettivamente cambiarne il contenuto, es per modificare la formula in essa contenuta, bisogna impostare prima la "modalita' Progettazione" sul tab "Sviluppo" del menu.

Visto che usi Office 365 la tua versione di Excel offre strumenti di filtro avanzati, applicabili a tabelle dati, tabelle pivot, grafici; possono essere usati per rendere piu' interattivi i risultati. Si tratta del Filtro Dati e Filtro Sequenza Temporale.
Per le caratteristiche e l'uso del Filtro Dati ("Slicer", in inglese) in una Pivot Table, vedi https://support.office.com/en-us/articl ... fafe79fa8f


Su questa tabella ho costruito una tabella Pivot. Per i primi suggerimenti sulle pivot tables: https://support.office.com/en-us/articl ... 9134456576

Alla pivot ho agganciato (a scopo dimostrativo) due "Filtri dati" e un filtro "Sequenza temporale"
Nella "Sequenza temporale" il timing puo' essere impostato con granularita' Giorno, oppure Mese, oppure Trimestre oppure Anno.
Vedi tu come questi filtri modificano la tabella pivot.

Sempre a scopo dimostrativo ho inserito un filtro anche sulla Tabella dati; semplificando, questo filtro agisce come quello dell'intestazione colonna; ha una diversa interfaccia, e puo' essere agganciato a piu' tabelle contemporaneamente. Tabelle pivot e grafici in genere elaborano solo dati non nascosti, nell'intervallo di origine.

Per inserire un filtro nella tabella dati: seleziona una cella della tabella, poi Strumenti tabella, gruppo Strumenti, usa Inserisci filtro dati.

Per inserire un filtro nella tabella pivot: seleziona una cella della pivot, poi Strumenti tabella piuvot, tab Analizza, gruppo Filtra; scegli un filtro.


Sempre a scopo dimostrativo, nello stesso foglio1 da K37 in avanti ho creato una tabella riassuntiva, alternativa alla pivot, che attinge ai dati direttamente dalla Tabella dati.

Una seconda tabella, in K44 e successivi, attinge dalla tabella dati ma usando solo i dati filtrati della tabella e crea una tabella riepilogativa per fornitore (elenco in colonna K), tipologia di servizio (elenco in colonne L:O) e mese (inserito nella cella evidenziata in Giallo)


Con riferimento a queste tabelle, le formule sono:
in L38

Codice: Seleziona tutto

=SOMMA.PIÙ.SE(TabellaOrigine[C];TabellaOrigine[[Operatore]:[Operatore]];$K38;TabellaOrigine[[Mese]:[Mese]];$L$36)


Poi va copiata nelle celle adiacenti (M-N-O) "tirandola" dal piccolo quadratino visibile (quando la cella e' selezionata) in basso a dx:


Se invece si usasse il normale Contr-C e Contr-V allora i riferimenti alle colonne della tabella rimarrebbero fissi (alla colonna [C]) e le formule dovrebbero essere modificate manualmente per far riferimento alle colonne [R], [Qli], [Valore] di TabellaOrigine

Si noti che l'uso di TabellaOrigine[C] nella formula consente di indirizzare l'intera colonna [C] della tabella, qualsiasi sia la sua altezza (numero di righe).


La formula in L45 e' piu' complessa, volendo indirizzare solo le righe della tabella dati che siano visibili:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(TabellaOrigine[[Operatore]:[Operatore]]=$K45);--(TabellaOrigine[[Mese]:[Mese]]=$L$43);SUBTOTALE(109;SCARTO(TabellaOrigine[C];RIF.RIGA(TabellaOrigine[C])-MIN(RIF.RIGA(TabellaOrigine[C]));0;1;1)))

Va poi copiata con lo stesso metodo descritto prima

Insomma secondo me devi liberarti dal vincolo di scrivere le informazioni nelle righe della data e nelle colonne del fornitore di servizio (come si fa con le schede cartacee) e passare a una registrazione analitica; da questa tabella analitica ti organizzi i dati usando tabella pivot e suoi accessori oppure una tabella calcolata con formule piu' o meno convenzionali.

Il file di cui parlo e' scaricabile qui: https://www.dropbox.com/s/9a1flbpqnl0os ... .xlsm?dl=0

Ciao

[danibi60]

Anthony, come sempre le Tue spiegazioni sono entusiasmanti, e non solo per la completezza, ma per il sempre innovativo punto di vista che mi proponi nell'analizzare i miei preistorici elaborati.

Non c'è dubbio che quanto mi proponi sia indiscutibilmente più funzionale, ma è pur vero che non so dove mettere le mani...
Le tabelle pivot le conosco ma le tue sono "avanti" io così non le ho mai sviluppate.

Ho visto i due link dai quali poter apprender come sviluppare al meglio il lavoro (soprattutto quella: "Per le caratteristiche e l'uso del Filtro Dati ("Slicer", in inglese) in una Pivot Table, vedi https://support.office.com/en-us/articl ... fafe79fa8f
", peccato che io l'inglese non lo comprenda se non le più basiche forme d'utilizzo maccheronico.

Devo rileggere attentamente le tue spiegazioni e fare dei tentativi per capire come ampliare le tabelle affinché si possano inserire tutti i dati che mi servono.

Sta pur certo che Ti scriverò ancora e ti invierò i miei, spero, positivi sviluppi.

Grazie come sempre,

Daniby

[Anthony47]

Degli articoli linkati esiste anche una versione tradotta automaticamente in Italiano:
https://support.office.com/it-it/articl ... t-IT&ad=IT
https://support.office.com/it-it/articl ... t-IT&ad=IT

Purtroppo spesso la traduzione e' deludente; valuta tu se e' meglio affidarsi all'Italiano di Microsoft o al tuo Inglese...

[danibi60]

Ciao Anthony, andiamo per ordine:

1) è probabile che le mie richieste di supporto e di chiarimenti relativi a questi file siano parecchie (vista la complessità e l'innovativo punto di vista nell'elaborazione della/e tabelle: "secondo me devi liberarti dal vincolo di scrivere le informazioni nelle righe della data e nelle colonne del fornitore di servizio (come si fa con le schede cartacee) e passare a una registrazione analitica" nda ) e non vorrei creare innumerevoli pagine di consultazione. scrivo sempre qui o è preferibile una conversazione "privata"??

2) nel frattempo inizio con alcune richieste: La tabella che hai creato in Foglio 1 (quella nella quale dovrei fare il dataentry) è stata da me sbianchettata e, nella stessa, ho aggiunto un campo denominato "Società". Pe4ccato che ora il campo "mese" non si compili più in automatico come invece nella tua originale accadeva, cosa ho combinato? Tieni presente che ho seguito le istruzioni per inserire il campo.

3) nonostante abbia sbianchettao la tabella, come mai nella pivot posta a fianco (K3:O9) risultano ancore visibili dei dati??

4) si possono far migrare le "varie pivottine" create a latere della tabella di inserimento, in un altro foglio senza per questo creare danni e facendo si che i dati si mantengano corretti?

5) hai creato nel tuo file originale due piccole pivot( denominate entrambe "Operatore"), come mai una ha elabora tre campi (denominati A-B-C) e l'altra nulla? come si aggiungo in campi in quelle pivot?

Mi fermo qui per il momento, ma ho molti altri quesiti… meglio andare per step.

Grazie infinite,

Danibi

[Anthony47]

Il mio file era una dimostrazione di quel che si puo' fare partendo da una registrazione sequenziale di dati.

Inserendo una colonna nella tabella, a me la colonna Mese continua a calcolare (tramite la formula =SE([@Data]<>"";MESE([@Data]);"") contenuta nel file originale) il mese della data.
Non e' che "sbianchettando" hai cancellato anche la formula?


La tabella pivot non si aggiorna automaticamente al cambiamento dei dati di origine; devi procedere con Aggiorna.
Avevo posizionato la tabella pivot nello stesso foglio dei dati per poter confrontare al volo la situazione dei dati in "tabella dati" con i risultati della pivot, anche alla luce dei filtri applicati (sequenza temporale e filtro dati).
Secondo me devi partire con un file nuovo, separando il foglio "dati" da quello dei report (che siano fatte tramite formule o tramite pivot).

Uno degli aricoli che ho linkato spiega come costruire le tabelle pivot; per vedere come ho distribuito i campi dovrebbe bastare selezionare una cella della pivot, che fa comparire la finestra Campi Tabella pivot; se invece non compare, allora Menu /Strumenti tabella pivot /Analizza; gruppo Mosta, seleziona Elenco campi.

Se vuoi portare le tabelle calcolate con formule su altro foglio: seleziona tutta la tabella, Contr-x (taglia), seleziona la destinazione, Enter (incolla)
In queste tabelle, A, B e C sono le voci che ho usato nel campo Operatore della tabella dati; la mia tabella aveva solo 3 operatori (trasportatori?), se tu ne hai di piu' in quella colonna li metterai tutti e copierai le formule quanto basta.

Spero c siano sufficienti spunti

Continuiamo qui, pero' per favore chiedi solo cose che non trovi o che non siano chiare nell' l'help di Excel.

Ciao

[danibi60]

Perdona Anthony,

non Ti avevo allegato il file che ho modificato e per il quale Ti ho posto i quesiti di cui sopra... ora lo vedi e potrai eventualmente capire quanto Ti ho chiesto con maggior chiarezza.
Per inciso avevo sbianchettato la tua formula e qui la trovi ripristinata

Cosa intendi quando scrivi : "Secondo me devi partire con un file nuovo, separando il foglio "dati" da quello dei report (che siano fatte tramite formule o tramite pivot)."

Grazie

https://www.dropbox.com/s/lv70fpzae9nck ... .xlsm?dl=0

[Anthony47]

I suggerimenti che avevo dati sono perfettamente allineati col file che hai allegato.

Rimodulo il suggerimento
-preparati il foglio in cui farai i data entry
-dove vuoi tu, ma meglio se su uno o piu' fogli diversi da quello usato per i data entri, creati i report; puoi usare le tabelle pivot (con i suoi ammenicoli quali il filtro dati o il filtro sequenza temporale) oppure tabelle create con formule.

Ciao

[danibi60]

Ciao Anthony

ho preparato (usando come traccia i Tuoi suggerimenti) un file con inseriti anche dei menù a tendina.

Ho riscontrato però alcune difficoltà che di seguito (almeno le prime) Ti elenco

Andiamo per ordine:

Foglio1 (a parte che se lo rinomino le macro non funzionano più, come mai?), ho inserito due menù a tendina per facilitare il data entri delle società e dei nominativi degli autisti.

Primo quesito: Visto che alle società sono abbinati solo alcuni autisti, è possibile, nel momento in cui seleziono il nominativo della società "Ahmed Arslan" cella C2, vedere poi in cella D2 solo gli autisti a lei abbinati?

Secondo quesito: nel foglio "statistiche" ho copiato e creato alcune piccole pivot e un riepilogo dei dati inseriti in foglio1, peccato che le pivot non siano dinamiche e in funzione da come si sposta la visibilità dei dati posti in A2-E12m spesso queste si sovrappongono, è possibile bloccare o farle migrare in un funzione della visibilità

Terzo quesito: si possono creare pivot che generano medie, esempio: la società "tal dei tali" ha tre autisti che trasportano un certo numero di quintali giornaliero, e un'altra che ha quattro autisti che ne trasporta meno , è possibile generare delle medie individuando i vari delta?

Infine, in foglio 5 come mail le due tabelle che avevi creato non funzionano più? dove ho fatto il disastro?

https://www.dropbox.com/s/qdi4dpbiuwt5f ... .xlsm?dl=0

Grazie,
Danibi

[Anthony47]

Per quanto riguarda la "Convalida", tu hai bisogno di creare degli elenchi di convalida "dinamici"; cerca su google "Convalida dinamica Excel site:pc-facile.com" (senza gli apici) e troverai numerosi esempi

Guarda l'esempio che ti ho creato in Foglio2:
-l'elenco delle societa' e' in orizzontale (I1:N1), e a quelle celle ho assegnato il "Nome" SOCIET
-in verticale ci sono i relativi nominativi
A ogni gruppo in verticale ho assegnato il "Nome" della societa'; non puoi usare lo Spazio, quindi il Nome assegnalo eliminando gli spazi

Poi in Foglio1-C2 ho impostato la Convalida da Elencoi e come origine ho indicato =SOCIET
In D2 invece la conalida, sempre da Elenco, sara' fatta con al formula =INDIRETTO(SOSTITUISCI($C2;" ";""))

Tra i vari esempi che troverai con la ricerca detta prima ti segnalo questa: viewtopic.php?f=26&t=97657#p560784
Ha il vantaggio che gli elenchi in verticale possono allungarsi /accorciarsi in modo dinamico.


Le pivot, grazie alla flessibilita' con cui possono essere filtrate, per natura si allungano e si allargano; probabilmente e' questo il motivo che quando ne crei una per default Excel propone di inserirle in un foglio a sé stante...

Quanto ai campi delle Pivot, se vai su foglio Statistiche e selezioni mettiamo A5, comparirà la finestra Campi Tabella Pivot. Nell'area Somma Valori, troverai il campo "Somma di R"; ma se ci clicchi sopra e scegli "Impostazione campo valori" avrai a disposizione altre opzioni, quali Conteggio, ... Media, ..., Dev. Standard, ..., Varianza, ... (e lo stesso campo puo' essere inserito piu' volte con opzioni diverse; es per calcolare la Media a la Varianza)

Come pure e' possibile inserire dei "Campi calcolati", con le formule che scegli tu (tra le opzioni disponibili, )
Devi pero' leggere gli articoli Microsoft che spiegano le cose.

La tabelle calcolate in Foglio5 funzionano regolarmente, infatti (vedi ad esempio seconda tabella), l'autista "a" nel mese "2" ha accumulato (registrazioni in Foglio1) C=0, R=0, Qli=0
Se i tuoi autisti non si chiamano a, b, c allora devi aggiornare gli elenchi...

Il file di esempio con le modifiche che ho detto e' reperibile qui: https://www.dropbox.com/s/vspvzib2rjvzn ... .xlsm?dl=0

Quale e' la macro che non funziona piu' se cambi nome ai fogli? Se parli della Worksheet_SelectionChange, al suo interno ci sono dei nomi fogli che devi modificare.

Ciao

[danibi60]

Anthony,
ho provato e riprovato nel creare appunto un menu a tendina dinamico, ma niente da fare no so dove sbaglio e nonostante gli aiuti in internet non capisco dove sta il mio errore, eppure ho copiato le tue indicazioni, ma la tendina "autisti" non seleziona nulla...come mai??

sono un incapace!!!

Ho persin provato a copiare la formula che indica il sito che mi hai linkato (viewtopic.php?f=26&t=97657#p560784) ma anche con quella nisba...


https://www.dropbox.com/s/xf4aiq2pyg7hr ... .xlsm?dl=0

[Anthony47]

Devi essere piu' disciplinato:

Guarda l'esempio che ti ho creato in Foglio2:
-l'elenco delle societa' e' in orizzontale (I1:N1), e a quelle celle ho assegnato il "Nome" SOCIET
-in verticale ci sono i relativi nominativi
A ogni gruppo in verticale ho assegnato il "Nome" della societa'; non puoi usare lo Spazio, quindi il Nome assegnalo eliminando gli spazi

Ebbene, mentre hai creato correttamente il "nome" SOCIET, non hai per nulla creato i "nomi" in verticale, con gli autisti delle singole Societa'

[danibi60]

Ciao Anthony,

ho smanettato in maniera "disciplinata" e ho scoperto dove si annidava il mio errore: e ho risolto!

Come potrai notare in foglio1 la tabella con i filtri dati sistemati e vi ho portato, in quanto a mio parere più utili le due tabelle.

Quel che però non mi piace e non riesco a sistemare sta nel foglio "Statistiche".

Infatti, se in foglio1 inserisco ritiri con date in mesi successivi la pivot si allarga tantissimo evidenziando una pessima visibilità e una consultazione non chiara; nonché - nel caso decidessi di stamparla - la necessità di un lenzuolo. Come posso ovviare a questo?

In foglio 5 invece ho inserito in entrambe sia il nome degli autisti sia delle società (modificando la Tua formula…) che ne pensi?

Si potrebbe sviluppare altro che possa tornarmi utile ? ad esempio (sempre in foglio 5) due ulteriori specchietti che misurino solo le performance o della singola società o del singolo autista si potrebbero creare?

Grazie,

Daniby

https://www.dropbox.com/s/xf4aiq2pyg7hr ... .xlsm?dl=0

[Anthony47]

se in foglio1 inserisco ritiri con date in mesi successivi la pivot si allarga tantissimo evidenziando una pessima visibilità e una consultazione non chiara; nonché - nel caso decidessi di stamparla - la necessità di un lenzuolo. Come posso ovviare a questo?

La risposta e' in quello che hai gia' cominciato a fare nella pivot: posizionare i mesi in verticale, e non "anche" in orizzontale. Oppure puoi mettere in verticale i campi Somma R, Somma C, Somma Qli e rimettere i 12 mesi in orizzontale.
E se sono ancora troppe righe o troppe colonne puoi usare i filtri (sui dati o sui tempi) per restringere sull'area di interesse

In foglio 5 invece ho inserito in entrambe sia il nome degli autisti sia delle società (modificando la Tua formula…) che ne pensi?

Non ho elementi per giudicare le informazioni che servono per valutare il tuo business; se i dati che ottieni sono di tuo interesse allora va tutto bene
Tieni presente che anche queste informazioni le puoi ottenere col metodo della tabella pivot, da tenere in considerazione soprattutto per la facilita' con cui si possono applicare filtri per selezionare le informazioni e per la dinamicita' con cui si inseriscono nuove voci (mentre nel caso delle tabelle con formule l'elenco delle Societa' e degli Autisti lo devi aggiornare manualmente)

Si potrebbe sviluppare altro che possa tornarmi utile ? ad esempio (sempre in foglio 5) due ulteriori specchietti che misurino solo le performance o della singola società o del singolo autista si potrebbero creare?

Tecnicamente e' assolutamente possibile usando qualsiasi dei due metodi che hai visto (tabella pivot; tabella con formule)

Infine un warning: le tabelle pivot hanno la tendenza ad accantonare grandi quantita' di dati su cui la creazione dei risultati poi si appoggia; se la dimensione del file diventasse un problema allora si puo' togliere la spunta (nelle Opzioni della tabella pivot, tab Dati) alla voce "Salva dati di origine con il file" e metterla alla voce "Aggiorna all'apertura del file"

Ciao

[danibi60]

Anthony ciao,

Le tue indicazioni rispecchiano quanto immaginavo e ti ringrazio per questo.

Seppur stia cercando di reindirizzare la pivot con visibilità diverse e più funzionali (e sembra vi sia riuscito) brancolo invece nel buio per "infilare" sempre nella pivot dei calcoli (medie in % dei singoli autisti e/o società d'appartenenza)... mah...

Ciao
Daniby

[Anthony47]

Le pivot sono aggeggi terribilmente utili, ma richiedono un po' di pratica...
Per info specifiche sull'aggiunta di campi calcolati:
https://support.office.com/it-it/articl ... 185e59da77

Ciao

[danibi60]

Grazie,

consulterò subito,

Danibi