Pivot con più fogli/files

[myobo...@gmail.com]

Salve ha tutti.
Avrei la seguente esigenza.
Ho 4 files (con un foglio ciascuno) con struttura delle etichette di colonna esattamente identica.
Vorrei creare un'unica tabella pivot.
Ho provato anche a mettere i singoli fogli dentro un unico file, ma nulla.. non riesco.
Ho girato e rigirato su internet ed ho scoperto che il mio excel2010 ha fatto un passo indietro in tal senso (prima era possibile selezionare un range di più fogli).
Ho provato creando una query con il comando union all, ma con 2 fogli come da esempio http://www.comefarea.it/ufficio/office2010/excel2010/pivot-su-piu-fogli/ funziona perfettamente, ma con tre fogli non mi somma i valori..
Qualcuno può aiutarmi?
grazie molte

[Maurizio Borrelli]

Il giorno giovedì 10 gennaio 2013 16:51:18 UTC+1, myobo...@gmail.com ha scritto:
[...]

> Ho provato creando una query con il comando union all, ma con 2 fogli come da esempio http://www.comefarea.it/ufficio/office2010/excel2010/pivot-su-piu-fogli/ funziona perfettamente, ma con tre fogli non mi somma i valori..

[...]

Ciao myobo...,
Riprova perche' dovrebbe funzionare. Io ho appena fatto un esempio con 4 fogli e non ho avuto problemi. La query era questa:
SELECT `T1`.Campo1 AS CAMPO1, `T1`.Campo2 AS CAMPO2 , `T1`.Campo3 AS CAMPO3
FROM `C:\Archivi\Forums\mpioe\SELECTUNION.xlsx`.`Foglio1$` `T1`
UNION ALL
SELECT `T2`.Campo1, `T2`.Campo2, `T2`.Campo3
FROM `C:\Archivi\Forums\mpioe\SELECTUNION.xlsx`.`Foglio2$` `T2`
UNION ALL
SELECT `T3`.Campo1, `T3`.Campo2, `T3`.Campo3
FROM `C:\Archivi\Forums\mpioe\SELECTUNION.xlsx`.`Foglio3$` `T3`
UNION ALL
SELECT `T4`.Campo1, `T4`.Campo2, `T4`.Campo3
FROM `C:\Archivi\Forums\mpioe\SELECTUNION.xlsx`.`Foglio4$` `T4`
Nota bene: NON usare "SELECT *" e metti gli alias ai nomi dei campi della prima SELECT e non alle successive.
Fa' sapere come va.

[myobo...@gmail.com]

con select * è lo stesso?
cosa sono quegli ultimi T1,3,..? rinomini i fogli così facendo?

[Maurizio Borrelli]

Il giorno giovedì 10 gennaio 2013 17:43:07 UTC+1, myobo...@gmail.com ha scritto:
> con select * è lo stesso?
> cosa sono quegli ultimi T1,3,..? rinomini i fogli così facendo?

Ciao myobo...,
come dicevo: NON usare SELECT *
T1..T4 sono gli alias che assegno alle tabelle (qui fisicamente fogli, ma per sql son tabelle). Preferisco assegnarli io.
--
Ciao!
Maurizio

[myobo...@gmail.com]

dunque...
l'unione funziona...
ma in maniera del tutto anomala.. se invece che "conteggio" seleziono "somma" tutti i valori escono come zero
c'è una spiegazione plausibile?

[Maurizio Borrelli]

Il giorno giovedì 10 gennaio 2013 17:56:13 UTC+1, myobo...@gmail.com ha scritto:
Ciao myobo...,

> dunque...
> l'unione funziona...

E` un buon segno. :)

> ma in maniera del tutto anomala.. se invece che "conteggio" seleziono "somma" tutti i valori escono come zero
> c'è una spiegazione plausibile?

Io scollegherei il foglio dalla query prima di fare la pivot:
1. Dati
2. Connessioni
3. Rimuovi
--
Ciao!
Maurizio

[r]

Ricordo d'aver avuto problemi con il passaggio al 2007 ... Quindi all'inizio quando selezioni d'origine dati non scegliere excel files* ma scegli nuova origine ... Avrai la possibilità di scegliere anche la versione di excel ... Poi ricorda di salvare prima di aggiornare la connessione ... Sono dal cellulare e questo è il secondo messaggio che provo a inviare ... Sperem

[r]

Ecco sono a un pc, dopo <nuova origine dati> devi dare un nome , nel punto 2 scegli Microsoft excel driver (*..xls,*.xlsx, *.xlsm, *.xlsb) poi punto 3 connetti e scegli la versione ...

[r]

intanto con excel 2007 e versioni successive possiamo definire i nomi in modo che siano dinamici con un semplice trucco.

quindi partiamo dall'inizio ...
ipotiziamo 3 fogli (foglio1,foglio2 e foglio3)

con 3 tabelle ... ogni tabella ha 4 campi e le intestazioni siano:
campo1
campo2
campo3
campo4

partiamo dalla tabella in foglio 1 ipotiziamo che la tabella sia nell'intervallo C3:F11 ...
primo passo selezioniamo l'intervallo e Inserisci->Tabella OK
verrà nominata in automatico Tabella1
adesso
Formule->definisci nome assegnamo il nome tbl_1
attenzione!
in riferito a: sarà
=Tabella1[#Tutto] ...
cambiamo con
=Foglio1!$C$3:$F$11
e diamo ok

ripetiamo per le altre 2 tabelle ... in modo d'avere alla fine 3 tabelle Tabella1, Tabella2, Tabella3 (anche se vengono nominate diversamente non importa) e sopretutto avere i nostri 3 nomi:
tbl_1
tbl_2
tbl_3

-----------------------------

scheda Dati-> da altre origini -> da microsoft query
selezionare <nuova origine dati> poi OK
1. assegnamo un nome Excel_2010
2. Microsoft excel driver(*.xls,*.xlsx,*.xlsm,*.xlsb)
3. Connetti-> dovrebbe comparire selezionata la versione corretta -> seleziona cartella di lavoro -> scegliete la cartella
OK
OK
OK

adesso selezionate la origine appena creata ...
defleggare Usa creazione guidata ...
OK
Apparirà la dialog Aggiungi tabelle seleziona tbl_1 -> aggiungi -> chiudi
selezioniamo i 4 campi e trasciniamoli per aggiungerli alla queri ... questi ultimi passi sono utili per vedere poi come vengono scritte le select ... apriamo adesso SQL che riporterà questo:

SELECT tbl_1.campo1, tbl_1.campo2, tbl_1.campo3, tbl_1.campo4
FROM `C:\TEST_SELECT_UNION\SELECTUNION.xlsx`.tbl_1 tbl_1

modifichiamo la queri come qui sotto:

SELECT tbl_1.campo1, tbl_1.campo2, tbl_1.campo3, tbl_1.campo4
FROM `C:\TEST_SELECT_UNION\SELECTUNION.xlsx`.tbl_1 tbl_1
UNION ALL
SELECT tbl_2.campo1, tbl_2.campo2, tbl_2.campo3, tbl_2.campo4
FROM `C:\TEST_SELECT_UNION\SELECTUNION.xlsx`.tbl_2
tbl_2
UNION ALL
SELECT tbl_3.campo1, tbl_3.campo2, tbl_3.campo3, tbl_3.campo4
FROM `C:\TEST_SELECT_UNION\SELECTUNION.xlsx`.tbl_3
tbl_3

possiamo chiudere con la x
selezionare in un rapporto di tabella pivot e dare OK

adesso abbiamo la nostra pivot dinamica che si aggiornerà aggiungendo nuove righe alle tabelle in modo del tutto automatico ad ogni Aggiorna

saluti
r

[r]

Il giorno giovedì 10 gennaio 2013 23:25:43 UTC+1, r ha scritto:

ho scritto 2 volte queri ... perdonatemi non avevo riletto!
:-(
r

[r]

Il giorno giovedì 10 gennaio 2013 23:25:43 UTC+1, r ha scritto:

> con 3 tabelle ... ogni tabella ha 4 campi e le intestazioni siano:

ovviamente non è strettamente necessario che le tabella abbiano stessa identica struttura ... importante è che abbiano almeno i campi (con stessa intestazione) sui quali si vuole unire i dati ...

saluti
r

[Maurizio Borrelli]

Il giorno giovedì 10 gennaio 2013 23:54:28 UTC+1, r ha scritto:
> Il giorno giovedì 10 gennaio 2013 23:25:43 UTC+1, r ha scritto:
> ovviamente non è strettamente necessario che le tabella abbiano stessa identica struttura ... importante è che abbiano almeno i campi (con stessa intestazione) sui quali si vuole unire i dati ...

Ciao r,
l'intestazione dei campi non conta, possono essere anche diverse. Quello che conta e` la posizione e il tipo di dato. Cosi` almeno e` la logica di funzionamento delle query di unione sql. Infatti e` previsto che si usino alias solo nella prima SELECT perche` quelle saranno le intestazioni dei campi della vista risultante, indipendentemente dai nomi dei campi delle SELECT successive.
--
Ciao!
Maurizio

[Maurizio Borrelli]

Il giorno giovedì 10 gennaio 2013 18:01:45 UTC+1, Maurizio Borrelli ha scritto:
> Il giorno giovedì 10 gennaio 2013 17:56:13 UTC+1, myobo...@gmail.com ha scritto:

Ciao myobo...,

Dimenticavo... E` fondamentale, ovviamente, salvare prima di aggiornare.
--
Ciao!
Maurizio

[r]

Il giorno venerdì 11 gennaio 2013 00:52:53 UTC+1, Maurizio Borrelli ha scritto:
> Il giorno giovedì 10 gennaio 2013 23:54:28 UTC+1, r ha scritto: > Il giorno giovedì 10 gennaio 2013 23:25:43 UTC+1, r ha scritto: > ovviamente non è strettamente necessario che le tabella abbiano stessa identica struttura ... importante è che abbiano almeno i campi (con stessa intestazione) sui quali si vuole unire i dati ... Ciao r, l'intestazione dei campi non conta, possono essere anche diverse. Quello che conta e` la posizione e il tipo di dato. Cosi` almeno e` la logica di funzionamento delle query di unione sql. Infatti e` previsto che si usino alias solo nella prima SELECT perche` quelle saranno le intestazioni dei campi della vista risultante, indipendentemente dai nomi dei campi delle SELECT successive. -- Ciao! Maurizio

Ciao Maurizio,
queste:
SELECT tbl_1.colonna1, tbl_1.colonna2, tbl_1.colonna3, tbl_1.colonna4
FROM `C:\test\S.xlsx`.tbl_1 tbl_1
UNION ALL
SELECT tbl_2.colonna1, tbl_2.colonna_pippo, tbl_2.colonna3, tbl_2.colonna4
FROM `C:\test\S.xlsx`.tbl_2 tbl_2

e questa
SELECT tbl_1.colonna1, tbl_1.colonna2, tbl_1.colonna3, tbl_1.colonna4
FROM `C:\test\S.xlsx`.tbl_1 tbl_1
UNION ALL
SELECT tbl_2.colonna1, tbl_2.colonna2, tbl_2.colonna3, tbl_2.colonna4
FROM `C:\test\S.xlsx`.tbl_2 tbl_2

funzionano entrambe ... le due tabelle sono coì intestate:
tbl_1
colonna1
colonna2
colonna3
colonna4

tbl_2
colonna1
colonna_pippo
colonna2
colonna3
colonna4


saluti
r

[myobo...@gmail.com]

Scusate la mia ingnoranza....
innanzi tutto grazie a tutti per le risposte.
Tutto quello da voi indicato mi sembra abbastanza complicato..

ma sei io includo il comando

SELECT *
FROM NomeRangeCell1
UNION ALL
SELECT *
FROM NomeRangeCell2
UNION ALL
SELECT *
FROM NomeRangeCell3

a parte dirmi che non riesce a rappresentare la struttura, sembra che l'unione funzioni...
infatti.. con in valori, con la funzione conteggio, tutto sembra ok.. non appena vario in somma, mi dà zero.. ma perché?!?!

ho provato ad eliminare le vecchie connessioni, ma nulla..

venendo a quanto suggerito da voi, poi, devo prima definire una tabella? l'inghippo è nell'assegnazione di un alias alle colonne anche se sono tutte identiche?

[r]

Il giorno venerdì 11 gennaio 2013 11:24:24 UTC+1, myobo...@gmail.com ha scritto:

la definizione delle tabelle serve a rendere i tuoi intervalli dinamici ... nell'articolo che hai riportato c'è una nota che indica che non è possibile, invece si ... però se a te non interessa avere un modello in cui all'aggiunta di nuovi record la tua pivot si aggiorna automaticamente senza bisogno di ridefinire i nomi o la query ... beh contento tu ...

indispensabile invece è la scelta di <nuova origine dati> nel primo passo della definizione della connessione anzichè selezionare excel files* ... purtroppo se insisti a creare una connessione così utilizzando excel 2007 o 2010 continerai ad avere problemi.

l'inghippo non è nell'alias

le colonne sono unite come dalla query sql in questo esempio:

SELECT tbl_1.colonna1, tbl_1.colonna2, tbl_1.colonna3, tbl_1.colonna4
FROM `C:\test\S.xlsx`.tbl_1 tbl_1
UNION ALL
SELECT tbl_2.colonna1, tbl_2.colonna_pippo, tbl_2.colonna3, tbl_2.colonna4
FROM `C:\test\S.xlsx`.tbl_2 tbl_2

puoi intuire che verranno unite le colonne:
colonna1 di tbl_1 con colonna1 di tbl_2
colonna2 di tbl_1 con colonna_pippo di tbl_2
colonna3 di tbl_1 con colonna3 di tbl_2
colonna4 di tbl_1 con colonna4 di tbl_2

saluti
r

[Maurizio Borrelli]

Il giorno venerdì 11 gennaio 2013 11:00:35 UTC+1, r ha scritto:
> Il giorno venerdì 11 gennaio 2013 00:52:53 UTC+1, Maurizio Borrelli ha scritto:
> > Il giorno giovedì 10 gennaio 2013 23:54:28 UTC+1, r ha scritto: > Il giorno giovedì 10 gennaio 2013 23:25:43 UTC+1, r ha scritto: > ovviamente non è strettamente necessario che le tabella abbiano stessa identica struttura ... importante è che abbiano almeno i campi (con stessa intestazione) sui quali si vuole unire i dati ... Ciao r, l'intestazione dei campi non conta, possono essere anche diverse. Quello che conta e` la posizione e il tipo di dato. Cosi` almeno e` la logica di funzionamento delle query di unione sql. Infatti e` previsto che si usino alias solo nella prima SELECT perche` quelle saranno le intestazioni dei campi della vista risultante, indipendentemente dai nomi dei campi delle SELECT successive. -- Ciao! Maurizio

> queste:
> SELECT tbl_1.colonna1, tbl_1.colonna2, tbl_1.colonna3, tbl_1.colonna4
> FROM `C:\test\S.xlsx`.tbl_1 tbl_1
> UNION ALL
> SELECT tbl_2.colonna1, tbl_2.colonna_pippo, tbl_2.colonna3, tbl_2.colonna4
> FROM `C:\test\S.xlsx`.tbl_2 tbl_2
> e questa
> SELECT tbl_1.colonna1, tbl_1.colonna2, tbl_1.colonna3, tbl_1.colonna4
> FROM `C:\test\S.xlsx`.tbl_1 tbl_1
> UNION ALL
> SELECT tbl_2.colonna1, tbl_2.colonna2, tbl_2.colonna3, tbl_2.colonna4
> FROM `C:\test\S.xlsx`.tbl_2 tbl_2
> funzionano entrambe ... le due tabelle sono coì intestate:
> tbl_1
> colonna1
> colonna2
> colonna3
> colonna4
> tbl_2
> colonna1
> colonna_pippo
> colonna2
> colonna3
> colonna4

Ciao r,
non e` molto diverso da quel che ho scritto io. Riassumendo:
- I nomi dei campi della vista o tabella risultante saranno quelli della prima SELECT. Quindi meglio, dico io, assegnare degli alias coerenti con i nostri propositi. Certo, non e` obbligatorio.
- Quel che conta e`
1. stesso numero di campi in ogni SELECT. Qundi meglio evitare SELECT * che non si sa mai.
2. conta la posizione e il tipo di dato. Ovvero campi con lo stesso nome ma posizione diversa non si allineano da soli. Campi con la stessa posizione ma tipo di dato diverso (esempio stringa invece di numero) possono impedire l'esecuzione della query. (Con fogli di Excel la questione e` meno rigida che con un database).
3. Io non userei UNION ALL ma UNION, cosi` mi risparmio i record duplicati, ma questo e` funzione dello scopo che ci si prefigge.
--
Ciao!
Maurizio

[r]

Il giorno venerdì 11 gennaio 2013 13:14:08 UTC+1, Maurizio Borrelli ha scritto:

non e` molto diverso da quel che ho scritto io. Riassumendo:
- I nomi dei campi della vista o tabella risultante saranno quelli della prima SELECT. Quindi meglio, dico io, assegnare degli alias coerenti con i nostri propositi. Certo, non e` obbligatorio.
- Quel che conta e`
1. stesso numero di campi in ogni SELECT. Qundi meglio evitare SELECT * che non si sa mai.
2. conta la posizione e il tipo di dato. Ovvero campi con lo stesso nome ma posizione diversa non si allineano da soli. Campi con la stessa posizione ma tipo di dato diverso (esempio stringa invece di numero) possono impedire l'esecuzione della query. (Con fogli di Excel la questione e` meno rigida che con un database).
3. Io non userei UNION ALL ma UNION, cosi` mi risparmio i record duplicati, ma questo e` funzione dello scopo che ci si prefigge.
--
Ciao!
Maurizio

Ciao Maurizio,
il punto 2 era ambiguo e mentre io parlavo di posizione delle colonne nelle tabelle excel tu intendevi posizione nella SELECT (che io davo per scontato e per questo non avevo capito)

ok comunque direi che ci siamo ... speriamo che ci sia anche myobo :-)
saluti
r

[Maurizio Borrelli]

Il giorno venerdì 11 gennaio 2013 11:24:24 UTC+1, myobo...@gmail.com ha scritto:

> infatti.. con in valori, con la funzione conteggio, tutto sembra ok.. non appena vario in somma, mi dà zero.. ma perché?!?!

[...]

Ciao myobo...,
Di solito danno somma zero dei valori di testo. Sei sicuro che vengono interpretati come numeri quelli che per te dovrebbero essere tali?
--
Ciao!
Maurizio

[myobo...@gmail.com]

aò, a me non funziona ;(

Ho fatto come segue.

Foglio qualsiasi.
Menu Dati, da altre origini, da microsoft query
seleziono file a caso, tanto poi varierò la query...
Nella query ho inserito:

SELECT Contabilità1.Data, Contabilità1.Dare, Contabilità1.Pos, Contabilità1.Avere
FROM 'c:\2013.Contabilità1.xls'.Contabilità1 Contabilità1
UNION ALL
SELECT Contabilità2.Data, Contabilità2.Dare, Contabilità2.Pos, Contabilità2.Avere
FROM 'c:\2013.Contabilità2.xls'.Contabilità2 Contabilità2

Ora, a parte non capire perché dopo il file e l'unione con il foglio si ripete di nuovo il foglio...
mi dice che è impossibile unire..
cosa sbaglio?

Ovviamente il file si chiama 2013.Contabilità1.xls ed ha il foglio Contabilità1
con le prime colonne che si chiamano Data, Dare, POS, Avere.

grazie per la pazienza..

[myobo...@gmail.com]

Con un po' di stratagemmi mi unisce i fogli (come me li univa all'inzio senza selezionare le singole colonne e nominare le tabelle..)
ma se da conteggio seleziono somma mi dà zero!!!! arggggggggggggggggggggggggg
le colonne rappresentano numeri, se utilizzo per singolo foglio la tabella pivot funziona! perché in unione non funziona?!

[r]

Omg!

[r]

questo che percorso è?
c:\2013.Contabilità2.xls

[myobo...@gmail.com]

perché mi dici che percorso è? è il percorso di uno dei file dove è contenuto il foglio da unire
c'è un errore nella sintassi?

[r]

No scusa, solo che usare il punto nel nome del file è deprecabile. Vuoi inviarmi il file? Così ti preparo la pivot e le connessioni. Sarebbe meglio usare una versione xlsx visto che usi excel 2010.
Saluti
r

[Maurizio Borrelli]

Il giorno venerdì 11 gennaio 2013 16:52:36 UTC+1, Maurizio Borrelli ha scritto:
> Il giorno venerdì 11 gennaio 2013 11:24:24 UTC+1, myobo...@gmail.com ha scritto:
> > infatti.. con in valori, con la funzione conteggio, tutto sembra ok.. non appena vario in somma, mi dà zero.. ma perché?!?!
> [...]

> Di solito danno somma zero dei valori di testo. Sei sicuro che vengono interpretati come numeri quelli che per te dovrebbero essere tali?

Ho visto il file e confermo. Il problema sta proprio nel fatto che i valori numerici vengono interpretati come valori di testo. Questo e` causato dal fatto che nei campi ci sono sia valori numerici che celle vuote e questo, specialmente se le celle vuote sono le prime della tabella, impedisce una corretta interpretazione del tipo di dato del campo. La soluzione e` non far indovinare a sql di che tipo di dato si tratta ma convertire noi, nella query, tutti i valori in valori sicuramente numerici. Per esempio cosi`:

SELECT tbl_1.Data AS Data, 0+tbl_1.Dare AS Dare, 0+tbl_1.Avere AS Avere, 0+tbl_1.POS AS POS
FROM `D:\Percorso\2013_Prova.xlsx`.tbl_1 tbl_1
UNION ALL
SELECT tbl_2.Data, 0+tbl_2.Dare, 0+tbl_2.Avere, 0+tbl_2.POS
FROM `D:\Percorso\2013_Prova.xlsx`.tbl_2 tbl_2
UNION ALL
SELECT tbl_3.Data, 0+tbl_3.Dare, 0+tbl_3.Avere, 0+tbl_3.POS
FROM `D:\Percorso\2013_Prova.xlsx`.tbl_3 tbl_3
UNION ALL
SELECT tbl_4.Data, 0+tbl_4.Dare, 0+tbl_4.Avere, 0+tbl_4.POS
FROM `D:\Percorso\2013_Prova.xlsx`.tbl_4 tbl_4
--
Ciao!
maurizio