Elencare dati di una tabella negli incroci riga/colonna di un altro foglio

[essegi75Partecipante essegi75]

Buongiorno, nel file che ho allegato, avrei la necessità riepilogare nella tabella del foglio PROD-CLIENTI, quello che viene inserito nel foglio VENDITE (che potrà avere un numero indefinito di righe, nell'es. 20). In particolare ho la necessità che i codici dei prodotti, presenti nella colonna E del foglio VENDITE, vengano riportati in sequenza uno sotto l'altro (in caso di stessa associazione) nelle relative celle di incrocio dei CLIENTI (max 4 righe) e ripartiti tra i VENDITORI (colonne B-C-D-E) del foglio PROD-CLIENTI. Il foglio Elenchi mi serve solo per dare l'esatta corrispondenza dei nomi CLIENTI e VENDITORI che un domani potrebbero cambiare. Chi mi aiuta? Grazie

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Credo che con sole formule diventa complicato. Penso che bisogna ricorrere a VBA.
I clienti sono sempre solo due per ogni venditore? E la struttura delle tabelle in VENDITE e PROD-CLIENTI è costante?
Inoltre sarebbe più immediato capire con un esempio del risultato finale. Questo è corretto?

[essegi75Partecipante essegi75]

Il risultato è esattamente quello che hai descritto nell'immagine. Vorrei evitare se possibile di utilizzare macro.

[essegi75Partecipante essegi75]

I clienti da associare al codice prodotto sono sempre 2 x ogni riga di venditore, quindi il codice dovrà comparire in due clienti diversi

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

essegi75 ha scritto:

Vorrei evitare se possibile di utilizzare macro.

Allora speriamo che qualche Maestro formulaio si faccia avanti. Io confesso tutti i miei limiti con le formule 🙂

[essegi75Partecipante essegi75]

Non ti sottovalutare    l'altra volta tu mi hai aiutato con la funzione ERRORE e INDICE, la tua formula insieme a quella di D@anilo sono state decisive e funzionavano entrambe, solo che mi sa questa volta la cosa è un po' più complicata. Devo cercare di implementare più funzioni in fogli diversi in modo da creare più automatismi possibili   se è possibile è bene sennò amen

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Non mi sottovaluto, ma conosco poco delle formule di Excel!
Questa è la soluzione VBA che avevo predisposto (e che funziona 🙂 )... se vuoi provarla tanto per curiosità, almeno mi dici se si comporta bene o no:

Option Explicit

Sub find_in_table()
Dim ra As Range
Dim c1 As Range
Dim c As Range
Dim f1 As Range
Dim f2 As Range
Dim i As Long
Dim j As Long
Dim dict As Object
Dim it As Integer
Dim v As Variant

    'Worksheets("PROD-CLIENTI").Select
    Set dict = CreateObject("Scripting.Dictionary")
    
    Set ra = Worksheets("VENDITE").Range("A2:J20")
    
    'prima colonna cliente
    Set c = ra.Offset(, 5).Resize(, 1)
    Set c1 = ra.Offset(, 5).Resize(Application.CountA(c), 1)
    For Each c In c1
        it = it + 1
        'n°: cliente, venditore, cod_prod
        dict.Add (it), Array((c), (c.Offset(, 4)), (c.Offset(, -1)))
    Next
    
    'seconda colonna cliente
    Set c = ra.Offset(, 7).Resize(, 1)
    Set c1 = ra.Offset(, 7).Resize(Application.CountA(c), 1)
    For Each c In c1
        it = it + 1
        'n°: cliente, venditore, cod_prod
        dict.Add (it), Array((c), (c.Offset(, 2)), (c.Offset(, -3)))
    Next
    
    'recupero e inserimento dati
    With Worksheets("PROD-CLIENTI")
        For Each v In dict
            Set f1 = .Columns(1).Find(dict(v)(0), LookIn:=xlValues, LookAt:=xlWhole)
            If Not f1 Is Nothing Then
                Set f2 = .Rows(1).Find(dict(v)(1), LookIn:=xlValues, LookAt:=xlWhole)
                If Not f2 Is Nothing Then
                    i = f1.Row
                    j = f2.Column
                    Do While .Cells(i, j) <> ""
                        i = i + 1
                    Loop
                    .Cells(i, j) = dict(v)(2)
                End If
            End If
        Next
    End With
End Sub

[essegi75Partecipante essegi75]

la provo e vediamo che succede, ma bisogna sempre attivarla con un pulsante o i valori vengono cambiati in automatico?

[gianfranco55Partecipante gianfranco55 Karma: 91 pts]

ciao

modifica la colonna A come il file

altrimento bisogna usare scarto e diventa un papiro

 

fatte le prime 4 celle basta copiare le 4 celle e incollare in basso

mantengono sia le formule che la convalida

la formula

=SE.ERRORE(INDICE(VENDITE!$E$2:$E$20;AGGREGA(15;6;RIF.RIGA($1:$1000)/(VENDITE!$F$2:$H$20=$A2)/(VENDITE!$J$2:$J$20=B$1);CONTA.SE($A$2:$A2;$A2)));"")

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Ottimo isy come sempre 🙂

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

essegi75 ha scritto:

ma bisogna sempre attivarla con un pulsante o i valori vengono cambiati in automatico?

Per quello che vale (solo per curiosità alla fine, dato che la formula appena vista funziona perfettamente), devi attivare le macro (e se vuoi salvarla nel file, deve essere di tipo xlsm cioè con attivazione di macro) e lanciarla direttamente (premi Alt-F8 e la scegli dall'elenco) oppure la associ a un pulsante. I valori non si cambiano in automatico. Per ottenere questa funzionalità bisogna fare qualche piccola modifica e intervenire nel codice dell'evento _Change del foglio interessato.

[D@niloPartecipante D@nilo Karma: 12 pts]

Buongiorno

Lasciando inalterata la struttura in B2 da trascinare a destra e poi in basso

 

=SE.ERRORE(INDICE(VENDITE!$E$2:$E$1000;AGGREGA(15;6;RIF.RIGA($2:$1000)-1/(((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)))*(VENDITE!$J$2:$J$1000=B$1));RESTO(RIF.RIGA($A1)-1;4)+1));"")

[gianfranco55Partecipante gianfranco55 Karma: 91 pts]

ciao

Danilo

non gli insegnare la formula con le celle unite  

poi continuerà a usarle  

ma oramai hai svelato il segreto  

Bella complimenti

 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Io la trovo massimamente oscura ma sono comunque molto stupito e vi ammiro 🙂

[D@niloPartecipante D@nilo Karma: 12 pts]

Ciao

@ vecchio frac

Sembra difficile ma se entri nel meccanismo è di una semplicità disarmante....il problema in questo caso è che l utente usa le celle unite in colonna A per il criterio nome a gruppi di 4....Ora le celle unite sono belle esteticamente ma vanno evitate come le suocere la domenica a pranzo....parti dal cuore

 

=QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1

Restituisce la sequenza di 4 volte 2 4 volte 6 4 volte 10 etc...messo come argomento riga nell indice A2:A1000

Ci da 4 volte il primo testo 4 volte il secondo etc....

Ora verificheremo in quali righe della colonna F dell altro foglio in quali righe viene soddisfatto il criterio ottenendo una sequenza di VERO/FALSO

Uso lo stesso metodo per verificate in colonna H lo stesso criterio 

 

=((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)) 

Quindi ottengo una matrice di 1/0  questa matrice dove verifico in quale riga sono soddisfatti i 2 criteri nome la moltiplichi per la matrice dove viene soddisfatto il 3 criterio

 

=(((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)))*(VENDITE!$J$2:$J$1000=B$1))

Dove ottengo una matrice di 1/0...questa matrice la metto a denominatore della divisione

 

=RIF.RIGA($2:$1000)-1/(((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)))*(VENDITE!$J$2:$J$1000=B$1))

Dove ottengo una matrice di numeri quando è  verificato il criterio è di errori quando non c'è corrispondenza  questa matrice la do in pasto ad AGGREGA e nella fattispecie alla funzione 15 che è  PICCOLO il 6 serve per ignorare gli errori ottenendo così i numeri di riga dove sono soddisfatti i criteri dall alto verso il basso l ultima parte 

 

=RESTO(RIF.RIGA($A1)-1;4)+1 è  il K di piccolo e restituendo la sequenza 1,2,3,4 1,2,3,4.etc andrà  a pescare il primo il secondo il terzo e il quarto record verificato per ogni nominativo

Questi numeri di riga che otteniamo saranno l  argomento riga della funzione indice 

Come già fatto in precedenza visto che non hai la funzione AGGREGA questa è  la versione per il 2007

 

=SE.ERRORE(INDICE(VENDITE!$A$2:$A$1000;PICCOLO(SE((((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)))*(VENDITE!$J$2:$J$1000=B$1));RIF.RIGA($2:$1000)-1;RESTO(RIF.RIGA($A1)-1;4)));"")

 

Matriciale

[D@niloPartecipante D@nilo Karma: 12 pts]

Ciao

scrivevo da cellulare per il 2007 è così

 

 

=SE.ERRORE(INDICE(VENDITE!$E$2:$E$1000;PICCOLO(SE((((VENDITE!$F$2:$F$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1))+(VENDITE!$H$2:$H$1000=INDICE($A$2:$A$1000;QUOZIENTE(RIF.RIGA($A1)-1;4)*4+1)))*(VENDITE!$J$2:$J$1000=B$1));RIF.RIGA($2:$1000)-1);RESTO(RIF.RIGA($A1)-1;4)+1));"")

 

 

Matriciale 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

@danilo
Avevo cominciato ad analizzarla ma mi sono fermato a Quoziente 🙂
Bella la spiegazione e soprattutto semplice il ragionamento! 🙂

D@nilo ha scritto:

Ora le celle unite sono belle esteticamente ma vanno evitate come le suocere la domenica a pranzo

Anche in VBA è così 🙂

Bravi Maestri delle Formule, ne sapete sempre una più del diavolo 😉

(Qui a casa Aggrega mi funziona perché ho la versione 2013, in ufficio sono fermo alla 2007, forse ci aggiornano quest'anno, speriamo)

[essegi75Partecipante essegi75]

Grazie mille a tutti per l'aiuto fornito, la mia conoscenza excel è minimale rispetto alle potenzialità del software

[Autore]

Articoli