Le tabelle pivot vanno bene per tutti: calcolano rapidamente, sono configurate in modo flessibile e, se necessario, il design può essere avvolto con eleganza. Ma c'è anche qualche mosca nell'unguento, in particolare l'impossibilità di creare un riassunto, dove l'area del valore non dovrebbe contenere numeri, ma testo.
Proviamo a aggirare questa limitazione e inventiamo un "paio di stampelle" in una situazione simile.
Supponiamo che la nostra azienda trasporti i suoi prodotti in container in diverse città del Nostro Paese e del Kazakistan. I container vengono spediti non più di una volta al mese. Ogni contenitore ha un numero alfanumerico. Come dato iniziale, c'è una tabella standard che elenca le consegne, da cui è necessario fare una sorta di riepilogo per vedere chiaramente il numero di container inviati a ciascuna città e ogni mese:
Per comodità, rendiamo “intelligente” in anticipo la tabella con i dati iniziali utilizzando il comando Home – Formatta come una tabella (Home — Formatta come tabella) e darle un nome Consegne linguetta Costruttore (Design). In futuro, questo semplificherà la vita, perché. sarà possibile utilizzare il nome della tabella e le sue colonne direttamente nelle formule.
Metodo 1. Il più semplice: usa Power Query
Power Query è uno strumento super potente per caricare e trasformare i dati in Excel. Questo componente aggiuntivo è stato integrato in Excel per impostazione predefinita dal 2016. Se hai Excel 2010 o 2013, puoi scaricarlo e installarlo separatamente (completamente gratuito).
L'intero processo, per chiarezza, l'ho analizzato passo passo nel seguente video:
Se non è possibile utilizzare Power Query, puoi andare in altri modi, tramite una tabella pivot o formule.
Metodo 2. Riepilogo ausiliario
Aggiungiamo un'altra colonna alla nostra tabella originale, dove usando una semplice formula calcoliamo il numero di ogni riga nella tabella:
Ovviamente, -1 è necessario, perché abbiamo un'intestazione di una riga nella nostra tabella. Se la tua tabella non si trova all'inizio del foglio, puoi utilizzare una formula leggermente più complessa, ma universale, che calcola la differenza tra i numeri della riga corrente e l'intestazione della tabella:
Ora, in modo standard, costruiremo una tabella pivot del tipo desiderato in base ai nostri dati, ma nel campo del valore rilasceremo il campo Numero di riga invece di quello che vogliamo contenitore:
Dal momento che non abbiamo più container nella stessa città nello stesso mese, il nostro riepilogo indicherà, infatti, non l'importo, ma i numeri di riga dei container di cui abbiamo bisogno.
Inoltre, puoi disattivare i totali generali e parziali nella scheda Costruttore – Totali generali и subtotali (Design — Totali generali, totali parziali) e nello stesso posto passare il riepilogo a un layout tabella più conveniente con il pulsante Modello di rapporto (Layout del rapporto).
Quindi, siamo già a metà del risultato: abbiamo una tabella in cui, all'intersezione tra città e mese, c'è un numero di riga nella tabella sorgente, dove si trova il codice contenitore di cui abbiamo bisogno.
Ora copiamo il riepilogo (nello stesso foglio o in un altro) e incolliamolo come valori, quindi inseriamo la nostra formula nell'area dei valori, che estrarrà il codice del contenitore dal numero di riga trovato nel riepilogo:
Funzione IF (SE), in questo caso, controlla che la cella successiva del riepilogo non sia vuota. Se vuoto, allora genera una stringa di testo vuota “”, cioè lascia vuota la cella. Se non è vuoto, estrai dalla colonna Contenitore tabella di origine Consegne contenuto della cella per numero di riga utilizzando la funzione INDICE (INDICE).
Forse l'unico punto non molto ovvio qui è la doppia parola Contenitore nella formula. Che strano modo di scrivere:
Forniture[[Contenitore]:[Contenitore]]
… è necessario solo per fare riferimento alla colonna Contenitore era assoluto (come un riferimento con i segni $ per le normali tabelle "non intelligenti") e non è scivolato nelle colonne vicine durante la copia della nostra formula a destra.
In futuro, quando si modificano i dati nella tabella di origine Consegne, dobbiamo ricordarci di aggiornare il nostro riepilogo ausiliario con i numeri di riga facendo clic destro su di esso e scegliendo il comando Aggiorna e salva (Ricaricare).
Metodo 3. Formule
Questo metodo non richiede la creazione di una tabella pivot intermedia e l'aggiornamento manuale, ma utilizza "l'arma pesante" di Excel: la funzione SUMMESLIMN (SOMME). Invece di cercare i numeri di riga in un riepilogo, puoi calcolarli utilizzando questa formula:
Con un po' di ingombro esterno, infatti, questo è un caso d'uso standard per la funzione di somma selettiva SUMMESLIMNA che somma i numeri di riga per la città e il mese indicati. Anche in questo caso, poiché non abbiamo più container nella stessa città nello stesso mese, la nostra funzione, infatti, non fornirà l'importo, ma il numero di riga stesso. E poi la funzione già familiare dal metodo precedente INDICE Puoi anche estrarre i codici contenitore:
Certo, in questo caso, non è più necessario pensare ad aggiornare il riepilogo, ma su tabelle di grandi dimensioni, la funzione SUMMESLI può essere notevolmente lento. Quindi dovrai disattivare l'aggiornamento automatico delle formule o utilizzare il primo metodo: una tabella pivot.
Se l'aspetto del riepilogo non è molto adatto al tuo report, puoi estrarre i numeri di riga da esso nella tabella finale non direttamente, come abbiamo fatto noi, ma utilizzando la funzione OTTIENI.DATI.TABELLA.PIVOT (OTTIENI.DATI.PIVOT.). Come fare questo può essere trovato qui.
- Come creare un report utilizzando una tabella pivot
- Come impostare i calcoli nelle tabelle pivot
- Conteggio selettivo con SUMIFS, COUNTIFS, ecc.