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

[Venerdi]

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.

[Anthony47]

Quale versione di Office usi?

[Venerdi]

Quale versione di Office usi?

Ciao Anthony, la versione Excel fa parte di Office LTSC Professional Plus 2021

[Anthony47]

Con riferimento all'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

[Venerdi]

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?

[Anthony47]

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

[Venerdi]

...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...

[Venerdi]

...trovato!

Il sito Microsoft dice che la funzione che tu hai utilizzato, al momento è disponibile solo con Office Insider Beta

[Anthony47]

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:


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"

[Venerdi]

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.

[Anthony47]

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

[Venerdi]

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...

[Anthony47]

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