Condividi:        

[Excel] Cerca valore (un po' più complicato)

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

[Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 28/11/22 22:44

Ciao a tutti.
Un nuovo quesito dopo qualche anno dall'ultimo.
Ho caricato questo file:
https://filedropper.com/d/s/AAeeJejAbZgn2rBnxecUVm8ZXbr2h4
E ora le spiegazioni.
Ho un elenco dove la colonna A non è altro che un concatena delle tre successive.
Per ulteriore chiarezza, la colonna D indica le quantità e la colonna E il costo unitario relativo.
Ed ora l'aiuto.
Nella cella I28 c'è una dicitura (LimoniAzzurri-25000); nella cella J28 vorrei trovare il costo corrispondente.
Se però si va nell'elenco, si vedrà che ci sono solo
LimoniAzzurri-20000 che possono essere venduti a 1,140
LimoniAzzurri-30000 che possono essere venduti a 1,080
Che formula si può usare affinchè il costo trovato sia quello del quantitativo subito inferiore a quello cercato? Ovvero 20000, considerato che non arrivando a 30000, da 20000 a 29999 devono mantenere il costo relativo a 20000?
Vi ringrazio
V.
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Sponsor
 

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 29/11/22 02:28

Quale versione di Office usi?
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 29/11/22 11:39

Anthony47 ha scritto:Quale versione di Office usi?


Ciao Anthony, la versione Excel fa parte di Office LTSC Professional Plus 2021
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 29/11/22 12:51

Con riferimento all'immagine:
Immagine
(per visualizzare tutta l'immagine: tasto dx, sceglere Visualizza immagine)

Con i dati di partenza, se la stringa da ricercare e' in I2 e sottostanti, ti propongo questa formula facile facile:
Codice: Seleziona tutto
=LET(bTAB;$A$2:$E$100;LFInit;I2;qt;TESTO.SUCCESSIVO(LFInit;"-");Lfor;SOSTITUISCI(LFInit;qt;TESTO(qt;"000000"));uuVal;UNICI(INDICE(bTAB;0;2)&INDICE(bTAB;0;3)&"-000000");UVAL;INDICE(bTAB;0;2)&INDICE(bTAB;0;3)&"-"&TESTO(INDICE(bTAB;0;4);"000000");nwTAB;DATI.ORDINA(STACK.VERT(uuVal;STACK.ORIZ(UVAL;bTAB)));CERCA.VERT(Lfor;nwTAB;6))
La metti in J2 e poi copi verso il basso

Per adattarla:
bTAB;$A$2:$E$100 punta alla tabella di partenza
LFInit;I2 punta al valore da cercare
Tutto il resto non si tocca

Sarebbe alquanto piu' semplice se la tabella non avesse la colonna Univoco...

Noterai, tra i risultati calcolati, un #N/A quando si cerca ad es. LimoniAzzurri-60; questo succede perche' non c'e' nessuna riga che possa definire il prezzo per quella quantita'

Fai sapere...
Editata Posizione formula
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 29/11/22 14:18

Grazie, ma...
Dici: La stringa da ricercare è in I2, e va bene, ma poi sotto dici di mettere anche la formula in I2. Considerato che non è possibile, ho pensato che ti fossi sbagliato e che intendessi J2. L'ho quindi piazzata lì, ma il risultato è #NOME?
Fra l'altro, l'immagine la vedo parzialmente (fino a metà della colonna J) e se la clicco per vederla in maniera più estesa, mi rimanda semplicemente al sito di hosting.
Dov'è l'inghippo?
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 29/11/22 15:06

Per vedere l'immagine intera: tasto dx sull'immagine, scehli Visualizza immagine; ma c'e' poco di piu' da vedere rispetto a quello che si intravede.

Hai fatto bene a inserire in J2 (adesso correggo anche il messaggio originale)

La formula usa istruzioni rilasciate ad Agosto, vediamo se le hai gia' disponibili... In una cella libera scrivi la formula
Codice: Seleziona tutto
=TESTO.SUCCESSIVO(A2;"-")

Se A2 contiene "MeleRosse-5000" allora la formula dovrebbe restituirti "5000"
Se ti da #NOME allora c'e' un problema di aggiornamento, prova a vedere i criteri di aggiornamento della tua versione

Se non ne usciamo vediamo un'altra via
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 29/11/22 16:01

...no, restituisce ancora #NOME, e non sembra questione di aggiornamento perchè la versione è:
Excel 2210 (Build 15726.20202)
quindi, di ottobre 2022...
Per ulteriore informazione, se io inizio a scrivere in una cella
=testo.successivo
oppure
=testo.dopo
oppure
=text.after
Excel non riconosce nessuna di queste formule, perchè non suggerisce alcuna sintassi per proseguire.
...strano...
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 29/11/22 16:18

...trovato!

Il sito Microsoft dice che la funzione che tu hai utilizzato, al momento è disponibile solo con Office Insider Beta
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 29/11/22 23:26

No, alcune delle funzioni usate furono rilasciate al pubblico a Luglio 2022, altre addirittura l'anno scorso; non fanno parte delle prestazioni in Beta test.
Sembra invece che proprio la versione 2021 non ha ne' avra' quelle funzioni:
https://support.microsoft.com/it-it/off ... b837e0463c
https://support.microsoft.com/it-it/off ... d2bfb3910a

Propongo quindi una soluzione basata su una funzione personalizzata, corrispondente a questo codice:
Codice: Seleziona tutto
Function MeleBananArr(ByRef LFor As Range, ByRef FTQC As Range) As Variant
Dim oArr(), w1Arr(), w2Arr(), myK As String, LfVal As String
Dim w2Ind As Long, I As Long, tmpStr As String, J As Long

ReDim w1Arr(1 To FTQC.Rows.Count)
ReDim w2Arr(1 To FTQC.Rows.Count * 2)
For I = 1 To FTQC.Rows.Count
    myK = FTQC.Cells(I, 1) & FTQC.Cells(I, 2)
    If IsError(Application.Match(myK, w1Arr, False)) Then
        w2Ind = w2Ind + 1
        w1Arr(I) = myK
        w2Arr(w2Ind) = FTQC.Cells(I, 1) & FTQC.Cells(I, 2) & "-000000-0"
    End If
    w2Ind = w2Ind + 1
    w2Arr(w2Ind) = FTQC.Cells(I, 1) & FTQC.Cells(I, 2) & "-" & Format(FTQC.Cells(I, 3), "000000") & "-" & FTQC.Cells(I, 4)
Next I
'Bubble sort
For I = 1 To w2Ind - 1
    For J = I + 1 To w2Ind
        If w2Arr(I) > w2Arr(J) Then
            tmpStr = w2Arr(I)
            w2Arr(I) = w2Arr(J)
            w2Arr(J) = tmpStr
        End If
    Next J
Next I
ReDim oArr(1 To LFor.Rows.Count, 1 To 1)
For I = 1 To LFor.Rows.Count
    If LFor.Cells(I, 1) <> "" Then
        ival = Split(LFor.Cells(I, 1), "-", , vbTextCompare)(1)
        LfVal = Replace(LFor.Cells(I, 1), ival, Format(ival, "000000"))
        oArr(I, 1) = CSng(Split(w2Arr(Application.Match(LfVal, w2Arr)), "-", , vbTextCompare)(2))
    Else
        oArr(I, 1) = ""
    End If
Next I
MeleBananArr = oArr
End Function


Inserisci il codice in un "modulo standard" del progetto vba del tuo file.
Poi su Excel avrai a disposizione la funzione MeleBananeArr con questa sintassi:
Codice: Seleziona tutto
MeleBananeArr(IntervalloConLeStringheDaCercare; IntervalloDelleColonneF-T-Q-Costo)

Restituisce un array di risultati comprendente tante celle quanti sono le stringhe da ricercare

Ad esempio:
Immagine

Nell'esempio dell'immagine, la formula inserita in J2 è
Codice: Seleziona tutto
=MeleBananArr(I2:I8;$B$2:$D$28)

La formula si espande automaticamente verso il basso, per quante stringhe da cercare sono state indicate

I "trattini" che vedi nei risultati sono degli "Zero" formattati in "stile separatore"
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 30/11/22 14:42

Grazie Anthony.
Ora ci vorrà un po' per fare tutte le prove. Io, ovviamente avevo semplificato per far capire cosa servisse.
Che la funzione TESTO.DOPO (o succesivo) non fosse rilasciata l'ho letto qui:
https://support.microsoft.com/it-it/off ... e6722e90b4
Cliccando su "disponibilità", sembrerebbe che è solo su beta.
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 30/11/22 16:04

Quelle informazioni si riferiscono a Office 365, un "prodotto" in abbonamento con parecchie prestazioni aggiuntive rispetto alle versioni "Licenza Unica"; e tra l'altro sono superate perche' oramai quella funzione (e molte altre) sono gia' rilasciate al pubblico.
Avevo capito che Office 2021 era allineato a Office 365, ma probabilmente contiene solo le prime novita'; vedi
viewtopic.php?f=26&t=111058
viewtopic.php?f=26&t=112111

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

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Venerdi » 04/12/22 12:52

Ciao,
...non avrei mai immaginato che delle funzioni fossero disponibili su un prodotto e non su un altro, ovviamente a parità di versione di Excel.
Sai se da qualche parte c'è una comparazione dettagliata fra prodotti? Io ne ho viste solo di generiche...
Avatar utente
Venerdi
Utente Junior
 
Post: 63
Iscritto il: 18/03/02 20:05
Località: Roma

Re: [Excel] Cerca valore (un po' più complicato)

Postdi Anthony47 » 04/12/22 20:02

Office 365 e' un abbonamento che da diritto a funzionalita' in evoluzione, e solo alcune di esse sono incluse nelle versioni "licenza perpetua". Addirittura inizialmente fino a Office 2019, nessuna delle nuove prestazioni sviluppate per 365 (vedi ad es viewtopic.php?f=26&t=111058) era stata resa disponibile e sembrava che Microsoft avesse intenzione di riservare al 365 le novità più importanti.
Ora sembra che man mano che escono le nuove versioni esse incorporano le novità già rilasciate; anche se non ho letto nessuna dichiarazione penso che Office 2021 rimarrà come è stato rilasciato (salvo aggiornamenti di sicurezza), mentre 365 evolve con nuove funzioni, che saranno poi (tutte? alcune?) inserite nelle versioni "licenza perpetua (ma a contenuto fisso)" che usciranno.
Qui trovi la cronologia degli aggiornamenti 365 distribuiti, con la possibilità di esaminare le singole novità:
https://learn.microsoft.com/it-it/offic ... ps-by-date

Per l'ambiente business, 365 include una serie di applicazioni (aggiuntive rispetto alle classiche applicazioni Office) orientate alla cooperazione aziendale; vedi https://learn.microsoft.com/it-it/offic ... escription
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "[Excel] Cerca valore (un po' più complicato)":

BTp Valore
Autore: MarioLombardi
Forum: Forum off-topic
Risposte: 2

Chi c’è in linea

Visitano il forum: Nessuno e 57 ospiti