Condividi:        

cerca_orizz celle dinamiche

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

cerca_orizz celle dinamiche

Postdi dobe3 » 12/08/16 14:21

Ciao a tutti!
ho letto un po' in giro ma ancora no ho trovato la soluzione.

Ho provato a concatenare cerca_vert e cerca_orizz e oanche INDICE e CONFRONTA ma senza esito positivo.

Immagine

Devo pescare i valori a DESTRA di "Totale sito-skligroup" presente in colonna D.
La riga può variare in quanto le righe in SERVIZIO possono aumentare o diminuire ogni giorno.

Inoltre ho il doppio caso di CALTANISSETTA dove devo pescare il TOTALE per 2 SKILLGROUP

Potete aiutarmi??

Grazie!!
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Sponsor
 

Re: cerca_orizz celle dinamiche

Postdi alfrimpa » 12/08/16 14:53

Ciao Dobe3

Ti consiglio di allegare il file; con le immagini non si può far nulla ed inoltre inserisci mnualmente il risultato atteso.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 16/08/16 08:06

L'ho condiviso su onedrive...spero vada bene

https://onedrive.live.com/embed?cid=6EF ... utton=True

le formule sono in L7:L9
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi Anthony47 » 17/08/16 15:03

Devo pescare i valori a DESTRA di "Totale sito-skligroup" presente in colonna D

Ho copiato il foglio pubblicato, ma non ho capito quale dato vorresti ottenere in funzione di cosa; puoi riprovare a descrivere?
Inoltre io non sono riuscito a "scaricare" il file da OneDrive, quindi non so che formula usi e se sono importanti per cercare di rispondere allla tua domanda; prova a usare uno dei server di file sharing descritti qui: viewtopic.php?f=26&t=103893&p=605487#p605487

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 18/08/16 06:57

File disponibile qui :
http://www.filedropper.com/cercavert


non ho necessità nè volontà di usare formule specifiche.
accetto qualsiasi consiglio. ho provato finora con cerca.vert e indice e confronta ma senza risolvere.

sulla parte sinistra del foglio1 c'è la tabella dati, sulla destra ho inserito lo specchietto con la formula cerca.vert che ho utilizzato.
con posizioni fisse ovviamente è semplice.
il problema è che quei dati variano quotidianamente (colonne non cambiano ma righe si)

ho bisogno di prendere i dati di ciascun SITO, per ogni SKILLGROUP.
Nella tabella di origine dati, il nome skillgroup è riportato in due celle in colonna C, una tra l'altro proprio sulla stessa riga dei dati numeri da estrapolare, ad esempio BARI = riga 20

la cosa si complica ancora in certi giorni quando, ad esempio, vedi Caltanissetta che ha 2 skillgroup, questi diventano 3 e duqnue non solo le celle slittano in basso di valori indefiniti, ma c'è anche da aggiungere il nome skillgroup nello specchietto che ho preparato sulla destra.


Spero sia un pò più chiaro ora :)

grazie del tempo dedicatomi!
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi peppo55 » 18/08/16 13:31

Ciao dobe3,

la Tabella dati così impostata, non è molto funzionale, anche perché se devi aggiungere un nuovo Skillgroup, rende difficoltoso calcolare le somme.
Allego file di prova. E' composto da una Tabella dati e una Tabella pivot.

http://www.filedropper.com/provalavoro

Vedi se può andare bene.
peppo

Excel 2010
peppo55
Utente Senior
 
Post: 167
Iscritto il: 30/09/12 13:51

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 18/08/16 13:47

Innanzitutto GRAZIE!

Il problema è che la tabella dati la importo da un file che scarico dalla intranet.
quella è la visualizzazione default.
siccome ogni giorno dovrò scaricare quel file xls , ho creato macro per importare il foglio e poi sul altro sheet voglio impostare le formule per pescare i dati che, come già scritto, possono "oscillare" orizzontalmente.
vedo che hai creato tabella pivot e che hai modificato alcune celle dati riportando i nomi in ogni cella.
si può fare in automatico?
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi Marius44 » 18/08/16 16:50

Ciao dobe3
Penso che questo possa servire. Ti allego il file e inserisco le due macro

https://www.dropbox.com/s/85a0nqcxgj9md2j/consuntivo_macro.xlsm?dl=0

Ecco il codice
Codice: Seleziona tutto
Sub totali()
ur = Cells(Rows.Count, 7).End(xlUp).Row         'assume ultima riga
Range("J7:L" & ur).ClearContents
r = 7
For i = 3 To ur                                                'ciclo da riga 3 a ur
    sito = Cells(i, 2).Text                                  'assume nome colonna B
    skill = Cells(i, 3).Text                                  'assume nome colonna C
    tot = Left(Cells(i, 4), 6)
    tot1 = tot1 + Cells(i, 6).Value                       'tot1 uguale alla somma di colonna F
    For j = i + 1 To ur
        If tot = "Totale" Then
            Cells(r, 10) = sito
            Cells(r, 11) = skill
            Cells(r, 12) = tot1
            tot1 = 0
            r = r + 1
            i = j - 1
            GoTo 1
        ElseIf Cells(j, 2) = "" And Cells(j, 3) = "" Then   'se col.B e col.C sono vuote continua
            tot1 = tot1 + Cells(j, 6).Value
        ElseIf Cells(j, 2) = "" And Cells(j, 3) <> "" Then 'se col.B è vuota e col.C non è vuota
            tot = "Totale"
        ElseIf Cells(j, 2) <> "" Then                     'se col.B non è vuota
            tot1 = 0                                            'azzera entrambi i totali
            i = j - 1
            GoTo 1
        End If
2   Next j
1 Next i
End Sub
Sub cancella()
ur = Cells(Rows.Count, 7).End(xlUp).Row         'assume ultima riga
Range("J7:L" & ur).ClearContents
End Sub


Lascio a te il compito di dichiarare le variabili e mettere Option Explicit all'inizio del VBE

Fai sapere. Ciao,
Mario
Marius44
Utente Senior
 
Post: 655
Iscritto il: 07/09/15 22:00

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 19/08/16 07:35

Sei stato gentilissimo!!
L'ho appena provato.
Se aggiungo o elimino righe o cambio valori, i calcoli sono aggiornati.

Però c'è modo di prendere direttamente i valori nella riga dove è presente sito-skligroup?


Purtroppo
1) skillgroup posono variare di giorno in giorno
2) non è possibile utilizzare la formula somma nè la media per tutte le colonne: alcuni dati (che non ho riportato nel file) sono calcolati con medie in base a valori esterni al file stesso, tipo servizio con incidenza maggiore...)

Dunque usare le formule per calcolare i valori colonna non restituirebbe i dati riportati nel file originale.

Al momento ho creato una macro con ciclo DO WHILE i<30 e chiudo il loop al 29° sito estratto - creo automaticamente un foglio per ogni sito tagliando e incollando il blocco valori così poi ricominciando sul foglio dati, passa per alcune celle vuote in maniera molto veloce e va avanti sito per sito.


la riporto di seguito:
Codice: Seleziona tutto
Sub importa()
' Select cell  *first line of data*.
      Dim I, K, J As Integer
      Dim IniPOScell As Variant
      Dim NomeSito As String
      Dim SITI(16) As String
      Dim Test As Boolean
     
     
       SITI(0) = "Bari"
       SITI(1) = "Cagliari"
       SITI(2) = "Caltanissetta"
       SITI(3) = "Firenze"
       SITI(4) = "Genova"
       SITI(5) = "Napoli"
       SITI(6) = "Palermo"
       SITI(7) = "Perugia"
       SITI(8) = "Pescara"
       SITI(9) = "Pisa"
       SITI(10) = "Reggio Calabria"
       SITI(11) = "Roma"
       SITI(12) = "Siracusa"
       SITI(13) = "Taranto"
       SITI(14) = "Torino"
       SITI(15) = "Trento"
       SITI(16) = "Venezia"
       
    J = 0
    For J = 0 To 16
      On Error Resume Next
      Test = Sheets(SITI(J)).Select
       If Test Then
          Sheets(SITI(J)).Delete
       End If
    Next
   
      Sheets("ONLINE SITO PER PERIODO").Activate
      Range("D5").Select
       I = 1
       K = 0
       Do While I < 30
                If ActiveCell.Value = "Totale sito-skligroup:" Then
                  IniPOScell = ActiveCell.Address
                   Range("A4:I4").Select
                   Selection.Copy
                   Sheets.Add(After:=Sheets(Sheets.Count)).Name = SITI(K)
                   Range("A4:I4").Select
                   ActiveSheet.Paste
                    Sheets("ONLINE SITO PER PERIODO").Activate
                   Range(IniPOScell).Select
                   ActiveCell.EntireRow.Select
                   Selection.Cut
                   Sheets(SITI(K)).Select
                    Range("A5").Select
                   ActiveSheet.Paste
                                               
                   I = I + 1
                   Sheets("ONLINE SITO PER PERIODO").Activate
                   Range(IniPOScell).Select
                   K = K + 1
               End If
               ActiveCell.Offset(1, 0).Select
               
    Loop
      ActiveWorkbook.SaveAs Filename:="C:\Sito\report.xls"
      ActiveWorkbook.Close
End Sub


Chiaramente la mia è molto più complessa e vorrei evitare di far fare tutte queste operazioni (cerca, taglia , crea, incolla etc...).
il foglio dati ha circa 500 rows.
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi Anthony47 » 19/08/16 18:54

Se ho capito bene allora questa macro potrebbe aiutare:
Codice: Seleziona tutto
Sub GetSkGr()
Dim LastR As Long, myArr(), gArr(1 To 1000, 1 To 7), gArrI As Long
Dim I As Long, dRange As Range, rRange As Range
'
Set dRange = Sheets(1).Range("B:J")       '<<< Foglio/ colonne su cui sono presenti i dati di origine
Set rRange = Sheets(2).Range("B2")        '<<< L'inizio della tabella dei risultati
'
With dRange
  Set Last = .Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlFormulas)
End With
If Last Is Nothing Then LastR = 1 Else LastR = Last.Row
rRange.Resize(1000, 7).ClearContents        '****
ReDim myArr(1 To LastR, 1 To 9)
myArr = dRange.Value
For I = 1 To LastR
    If myArr(I, 1) <> "" Then lsito = myArr(I, 1)
    If myArr(I, 2) <> "" Then lgrp = myArr(I, 2)
    If myArr(I, 2) <> "" And myArr(I, 3) <> "" Then
        gArrI = gArrI + 1
        If gArrI > 1 Then If lsito <> gArr(gArrI - 1, 1) Then gArrI = gArrI + 1
        gArr(gArrI, 1) = lsito
        gArr(gArrI, 2) = lgrp
        gArr(gArrI, 3) = myArr(I, 5)
        gArr(gArrI, 4) = myArr(I, 6)
        gArr(gArrI, 5) = myArr(I, 7)
        gArr(gArrI, 6) = myArr(I, 8)
        gArr(gArrI, 7) = myArr(I, 9)
    End If
Next I
rRange.Resize(gArrI + 10, 7) = gArr
End Sub
Le righe marcate <<< sono da personalizzare come da commenti.

La macro crea una tabella con tutti gli skillgroups presenti nella tabella di origine, e la scrive nel range dichiarato all'inizio (rRange).
Il contenuto (non la formattazione) dell'area riservata per la tabella dei risultati (rRange) viene all'inizio CANCELLATO SENZA PREAVVISO dalla macro per una dimensione di 1000 righe * 7 colonne (vedi istruzione marcata ****).

Ciao, fai sapere.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 22/08/16 10:28

Ciao Anthony47,
la tua macro è OTTIMA!
leggera e veloce.
ho già cestinato la mia che taglia e incolla ogni sito in un nuovo foglio. un ciclo Do troppo lungo ed elaborato.

allego il foglio rielaborato.
http://www.filedropper.com/cercavert_1

nello sheet1 c'è il foglio di destinazione formattato e con il bottone macro.
sheet2 è foglio dati di test che avevo condiviso con voi.
sheet3 il foglio dati UFFICIALE così come lo importo ogni giorno.

usando la tua macro, col foglio TEST tutto ok cioè riportat solo la riga TOTALE SKILLGROUP.
col foglio ufficiale invece riporta la prima riga e la riga TOTALE skillgroup

ho provato a DISUNIRE le celle SITO (colonna B) o SKILLGROUP (colonna C) ma il risultato non cambia.

sbaglio qualcosa?

grazie ancora del tempo dedicatomi!!
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi Anthony47 » 22/08/16 16:48

Da viewtopic.php?f=26&t=103911#p605595, Linee guida per pubblicare le vostre domande:
[quote]5) Evitate di pubblicare strutture dati diverse da quelle che in realta' dovete gestire, a meno che non siete certi di riuscire ad adattare autonomamente le risposte che vi verranno fornite[/code]

Sostituisci
If myArr(I, 2) <> "" And myArr(I, 3) <> "" Then
con
Codice: Seleziona tutto
    If myArr(I, 2) <> "" And Left(myArr(I, 3), 12) = "Totale sito-" Then

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 24/08/16 07:06

non so come ringraziarti!
mi scuso per il disguido. in verità, come da titolo, speravo ci fosse la possibilità di usare solo formule =SE(CERCA.VERT ....CONCATENA...INDICE // non c'è proprio modo di usare formule invece di macro?

in tal modo avrei provveduto a riadattarle
siccome gli aiuti sono arrivati subito con VBA, ho provato di volta in volta a lavorarci su ma è troppo avanzato per me :-P

la prossima volta, saprò come chiedere
grazie a tutti.
POST RISOLTO
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12

Re: cerca_orizz celle dinamiche

Postdi Anthony47 » 24/08/16 23:24

Su certe strutture dati disordinate rincitrullire con le formule ha poco senso...
Comunque...
In M5 di ONLINE SITO PER PERIODO metti la formula
Codice: Seleziona tutto
=PICCOLO(SE(SINISTRA($D$5:$D$1000;14)="Totale sito-sk";RIF.RIGA($C$5:$C$1000);"");RIF.RIGA(A1))

In O5 inserisci:
Codice: Seleziona tutto
=SE.ERRORE(INDICE($B$1:$B$1000;MAX(SE(SCARTO($B$1;0;0;M5;1)<>0;RIF.RIGA(SCARTO($B$1;0;0;M5;1));"")));"")

Selezioni poi P5:W5 e inserisci
Codice: Seleziona tutto
=SE.ERRORE(INDICE($C$1:$K$1000;M5;0);"")

NOTA: tutte le suddette formule sono da confermare con Contr-Maiusc-Enter, non il solo Enter

Poi copia la prima riga di formule e copia in M6:M100 (accertati che in colonna M compaia alla fine l'errore #NUM!, che indica non ci sono ulteriori skill-groups)

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: cerca_orizz celle dinamiche

Postdi dobe3 » 25/08/16 09:14

Fantastico!
adesso posso usare queste formule per ricavare colonne anche su e da altri fogli.

G R A Z I E ! ! !
dobe3
Newbie
 
Post: 8
Iscritto il: 12/08/16 14:12


Torna a Applicazioni Office Windows


Topic correlati a "cerca_orizz celle dinamiche":


Chi c’è in linea

Visitano il forum: systemcrack e 45 ospiti