Trovare un valore da range in una cella

[Londonoffice]

Ciao a tutti,
ecco un nuovo problema che mi sa che si potrebbe risolvere con una semplice formula, ma non riesco a capire come estenderla a dovere.
Anzitutto ho un range:
Col. A: Parola di origine (esempio: Cielo)
Col. B: Parola finale (esempio: Sky)
Mettiamo caso che ho anche un altro foglio con tre colonne:
Col. A: Testo di origine (esempio: Il cielo è blu)
Col. B: Testo di arrivo (esempio: Sky is blue)
In entrambi i fogli sia il 'dizionario' che le frasi contengono numerose voci.
Devo accertarmi che: SE nella frase in A2 è presente UNA QUALSIASI parola/espressione contenuta nella colonna A del dizionario, ALLORA voglio che il valore della formula = alla parola corrispondente a quella trovata nella Col. B del dizionario. ALTRIMENTI c'è un errore! Inoltre, nella frase d'origine ci può essere più di una parola contenuta nel dizionario, nel qual caso sarebbe perfetto se potessi controllare quante e quali parole sono contenute nella frase d'origine e vedere se le corrispondenti parole finali sono contenute tutte nel testo d'arrivo.
Ho pensato prima di tutto a una cosa semplice, senza range. Uso Excel 2007.

Codice: Seleziona tutto

=IF(NOT(ISNUMBER(SEARCH("Parola di origine";A2)));"nessuna parola del dizionario!";IF(ISNUMBER(SEARCH("Parola finale";B2));"corrispondenza OK";"corrispondenza errata!"))

Ora, ciò che mi serve fare è sostituire a "parola d'origine" un intero range che va da A2 ad A500 di Sheet2 (per esempio) e che una volta trovato il valore venga confrontato con il suo analogo nella colonna B (esempio, se il 5° valore del range, Sheet2!A6, viene trovato in Sheet1!A2, allora Sheet2!B6 sia confrontato in Sheet1!B2).
Non so se sono stato molto chiaro, ma se potete aiutarmi mi fate un grande favore.
Inoltre c'è da considerare che nella frase ci può essere più di una parola dal dizionario...

[Flash30005]

Sicuramente per te è chiaro...
a tuo vantaggio hai l'ora tarda nella quale sto leggendo, domattina rileggerò il tutto e speriamo che io sia un po' più sveglio.

Ciao

P.s. non credi sia meglio che inviassi un file con qualche nome-dizionario, frase-testo origine, frase-testo destinazione e due piccoli commenti come esempio?

[Londonoffice]

Ciao Flash!
Eheheh! Scusa, hai ragione!
Considera ad esempio il seguente file dizionario:
COLONNA A
House
Blue
Window

COLONNA B
1) Casa
2) Blu
3) Finestra

---
Frasi esempio:
COLONNA A
1) The house has many windows.
2) Ah, nice!
3) That door is blue.
4) That is a cat.

COLONNA B
1) La casa ha molte finestre.
2) Ah, bello!
3) Quella porta è blu.
4) Quella è una casa.

COLONNA C (risultato delle formule che cercano tutte le parole del dizionario in ogni cella di A e vedono se c'è il corrispondente in B)
1) C'è almeno un errore. [una parola nel dizionario e nella frase in inglese "house", è stata riconosciuta nella frase in italiano, ma un'altra, identificata anche se finisce con la S nella frase in inglese, no perché è al plurale in italiano e non corrisponde esattamente a "finestra" nel dizionario. Desidero comunque un allarme per il controllo manuale, anche se di fatto poi la traduzione è corretta.]
2) Nessuna parola del dizionario è presente in questa frase.
3) Corrispondenza trovata.
4) C'è almeno un errore. [il corrispondente di "house", ovvero "casa" nel dizionario, è presente in italiano ma non nell'originale]
---
Tutto questo almeno per 3 possibili errori in una sola cella di B. Se vedo che ci sono ben 3 errori (ma non importa che il risultato mi segnali quanti sono), beh, è il caso di leggere attentamente la cella in italiano e accertarsi che non ce ne siano altri.
Spero sia un po' più chiaro
(e scusa ma non sono riuscito a trovare il pulsante per allegare!)

[Flash30005]

Non credo che tu possa risolvere con una formula
secondo me occorre una macro (anzi diverse macro collegate tra loro).
Il progetto finale lo vedo abbastanza complesso però ti dò uno spunto per iniziare
Chiama il foglio dei vocaboli "Dizionario" e il foglio delle frasi "Frasi"
Considerando lo schema dei tuoi dati nel foglio frasi da A2 in poi hai le frasi in inglese e in B2 in poi quelle in italiano
puoi utilizzare questa macro per separare ogni parola della frase inglese contenuta nella cella A2 (provvederai in seguito a variare la riga con un for...next di tutte le righe)

Codice: Seleziona tutto

Private Sub TCampoE()
Dim VeEN(20) As String
CellaN = Sheets("Frasi").Range("A2").Value
Csp = 0
For Sp = 1 To Len(CellaN)
If Mid(CellaN, Sp, 1) = " " Then Csp = Csp + 1
Next Sp
Col = 0
For Vs = 1 To Csp + 1
virg = ""
           CampoE = ""
           Do Until virg = " "
           Col = Col + 1
           virg = Mid(CellaN, Col, 1)
           If virg <> " " Then CampoE = CampoE & Mid(CellaN, Col, 1)
            If Col >= Len(CellaN) Then
            CampoE = Mid(CampoE, 1, Len(CampoE) - 1)
            GoTo FineS
            End If
           Loop
FineS:
           VeEN(Vs) = CampoE
Next Vs
For Vs = 1 To Csp + 1
MsgBox VeEN(Vs)
Next Vs
End Sub

Stessa cosa dovrai fare per le frasi in italiano.
Una macro-madre che scansionerà riga per riga richiamerà sia la macro in inglese e poi quella in italiano
lavorando sul vettore VeEN(20) confrontato con le parole del dizionario troverai la stringa corrispondente e quindi il vocabolo in italiano che confronterai con il vettore VeIT(20) della macro in italiano. questo confronto condizionato produrrà ciò che ti occorre (dovrai applicare qualche artefizio per il plurale, secondo me nel dizionario dovresti avere anche le parole al plurale come windows e finestre).
Ricorda di usare la funzione Ucase per le stringhe che metterai a confronto.

Fia sapere
Ciao

P.s. per allegare i file segui questa procedura

[Londonoffice]

Ciao Flash,

Grazie per il tuo input, ma vedo almeno qualche problema.
1: Non ne capisco niente di programmazione, quindi non saprei come proseguire ^^; al massimo riesco a usare e combinare delle formule che ho imparato on the road.
2: Questo sistema che divide le parole sembra essere basato sugli spazi. Questo vuol dire che parole plurali, o celle contenenti una sola parola, o infine l'ultima parola di ogni cella non verranno scansionati, dico bene?
Ora, se riuscissi invece a estendere la formula che ho scritto all'inizio, ma anziché una singola parola tra "" riuscissi a infilarci una cosa tipo OR{rangename} (non mi sembra che esista una sintassi simile, o almeno non sono riuscito a farla funzionare), secondo la quale tutte le parole nel range devono essere cercate e possono essere trovate nella cella scansionata, beh sarei quasi a cavallo. Se poi per ogni cella in cui viene trovato un errore, fosse possibile rifare una scansione a partire dalla parola nel range successiva a quella contenente un errore, potrei verificare se esiste un secondo errore. Già sarebbe perfetto!
Per quanto riguarda i plurali in italiano, non temere, non sono tanti quindi l'importante è che venga identificata la parola in inglese (anche se plurale). In tal senso, fare una ricerca con FIND/SEARCH anziché dividere ogni cella in parole separate da spazi è più efficace per la mia necessità.
Che ne dici, è possibile chiedere alla formula di cercare tutte le parole in un range anziché una singola tra ""?
PS allora avevo capito bene, non è possibile allegare file in questo forum... Vorrà dire che seguirò il tuo consiglio e userò hosting esterni se necessario. Grazie!

[Londonoffice]

Scusa il doppio post, ma per aggiungere una condizione importante alla formula che avevo in mente:
bisognerebbe anche indicizzare il range, in modo che SE viene trovata una parola del dizionario inglese nella frase inglese, viene verificato l'indice che questa ha nel range, e controllato nella frase in italiano se la parola corrispondente nel range italiano allo stesso numero dell'indice di cui sopra è presente in italiano. In questo modo dovrebbe anche essere più veloce.
Inoltre il secondo check della stessa frase dovrebbe avvenire a condizione che sia semplicemente stata riconosciuta una parola del dizionario inglese nella frase inglese, perché se la prima è giusta, la seconda può sempre essere sbagliata...
Due check dovrebbero essere sufficienti ad allarmarmi!

[Flash30005]

Come già detto nel post precedente non conosco formule ovvero funzioni native di excel che possano fare quello che cerchi.
La formula da te postata potrà funzionare solo in un determinato caso, quello che intendo io è, invece, scansionare ogni parola della frase in inglese con la macro postata (creare una seconda macro simile alla prima per l'italiano),
Cercare ogni singola parola nel dizionario e una volta trovata "prendere" la corrispondente in italiano e confrontarla con ogni singola parola costituente la frase. A questo punto hai i seguenti casi
Trovata in inglese = Si
Trovata in italiano = Si
= corretto1
Oppure
Trovata in inglese = Si
Trovata in italiano = No
= errore1 (tipo A)

inoltre per il discorso del plurale si potrebbe pensare di prendere e confrontare anche solo una parte della parola usando la lunghezza della parola stessa: Mid(stringa, 1, len(stringa)-1)
Nel caso di window e windows avresti condizione trovata = Si
In quella italiana invece confrontare anbedue le stringhe troncate di un carattere
If Mid(stringa1, 1, len(stringa1)-1) = Mid(stringa2, 1, len(stringa2)-1) avresti
in caso di finestra e finestre la condizione Trovata = Si (vero)
e successivamente con il controllo della parte troncata, quindi
If Mid(stringa1, len(stringa1)-1, 1) = Mid(stringa2, len(stringa2)-1, 1) = Falso
e questo sarebbe un caso di errore (tipo B)

Ma come accennavo precedentemente la complessità aumenta considerando le tantissime eccezioni che ci sono nei vocaboli delle due lingue in esame.

Lascio ad altri la soluzione attraverso le formule
Ciao

[Anthony47]

Un piccolo esercizio con formule.
Vedi immagine



Uploaded with ImageShack.us
(Per vedere l' immagine completa fare tasto dx, Visualizza immagine)
In col A e K di Foglio2 ci sono le frasi originali
Le stesse sono state copiate in col B e L, queste copie sono state ripulite da simboli (, . !) tramite il comando Trova e sostituisci (ripetuto piu' volte), e poi splittate in parole usando Menu /Testo in colonna. Le colonne per le parole devono essere presenti e libere, per questo ho spostato le frasi in Italiano in col K.
Nell' area in sfondo rosa e' stata impostata una formattazione condizionale, con la condizione=La formula e', con formula =E(B1<>"";CONTA.SE(DICT;B1)>0) e con "Motivo" color Oro
Idem l' area in giallina, ma la formula e' =E(L1<>"";CONTA.SE(DIZ;L1)>0) e Motivo color Giallo.
Sul foglio con le parole e le traduzioni, le colonne A e B sono state nominate rispettivamente DICT e DIZ, per essere usate nelle formule della formattazione condizionale.
Il risultato e' che le parole presenti nei dizionari vengono evidenziate come in figura.

Ma vorrei anche sottoporti questa macro ottenuta al 95% tramite Registra nuova macro, che forse ti potrebbe far cambiare approccio:

Codice: Seleziona tutto

Sub WebQ()
'
Sheets("Foglio3").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://translate.google.it/?js=n&prev=_t&hl=it&ie=UTF-8&layout=2&eotf=1&sl=en&tl=it&text=" & Replace(Sheets("Foglio2").Range("A2").Text, " ", "+") & " &file=#" _
        , Destination:=Range("A1"))
        .Name = _
        "?js=n&prev=_t&hl=it&ie=UTF-8&layout=2&eotf=1&sl=en&tl=it&text=Pippo&file=#"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub


Il risultato e' che su Foglio3 otterrai:


Uploaded with ImageShack.us
La query e' stata lanciata con il contenuto della cella A2 di Foglio2 (l' immagine precedente), guarda cosa ti restituisce nella cella che ho colorato in giallo.

Oppure, prova questa macro; lanciala quando hai una frase in ingese su A2, ti scrivera' qualcosa in B2:

Codice: Seleziona tutto

Sub GoogTrans()
Set ie = CreateObject("InternetExplorer.Application")
With ie
    .Visible = True
    .Navigate "http://translate.google.it/#en|it|"
    Do Until .ReadyState = 4: DoEvents: Loop
       
    Google_Translate_Text = Range("A2").Value
    .Document.forms("text_form").elements("source").Value = Google_Translate_Text
    Application.Wait (Now + TimeValue("0:00:03"))
    .Document.getElementById("text_form").submit
    Application.Wait (Now + TimeValue("0:00:03"))
    Range("B2").Value = .Document.all("result_box").innertext
End With
End Sub


Sono prototipi, ci puoi fare qualche sviluppo interessante?

Ciao

[Londonoffice]

Caro Anthony,

Grazie come sempre per l'interessante spunto.
Ti espongo i problemi che mi sembra di intravedere.
In realtà i testi con cui ho a che fare sono mooolto più complessi: contengono parole inventate e che necessitano di una determinata ortografia oltre che parole con traduzioni poco ortodosse. Inoltre molte delle frasi sono lunghe, e persino divise a capo con il line break (possono essere anche molte righe in una cella). E poi la quantità di dati con cui ho a che fare è dell'ordine di milioni di parole.
Per quanto riguarda le traduzioni di google translate, purtroppo non si rivelerebbero molto utili, per i motivi di cui sopra.
La formattazione condizionale invece è un ottimo approccio. L'unico difetto consisterebbe nella lunghezza delle frasi. Tra l'altro dividere le frasi parola per parola ho l'impressione che potrebbe causare delle imprecisioni: mettiamo caso che una frase in inglese contiene più volte la parola "casa", ma in italiano usiamo dei pronomi: controllare su frasi molto lunghe (ammettiamo pure che usiamo la formula CLEAN per mettere tutto su una riga) che non abbiano problemi in italiano diventa tedioso. Oltretutto, c'è un altro possibile errore: mettiamo che in originale c'è "house" e in italiano "finestra": il Con.For. mi evidenzia in entrambe una parola, ma in italiano c'è chiaramente un errore.
Ora, se la scansione delle celle corrispondenti, come ha proposto Flash, è l'idea più vicina a quella che vorrei realizzare, essa pone il problema della divisione per spazi. Pertanto una cosa come il comando SEARCH mi arrangerebbe alla grande. Anche il fatto di indicizzare un array (per il dizionario) e trovare il corrispettivo valore dell'indice nel dizionario italiano mi garantirebbe precisione.
Se davvero non è possibile chiedere a una formula di cercare "uno qualsiasi dei valori di un array" tramite OR in una cella, dovrò studiare un po' come sviluppare una semplice macro...
Se intanto vi viene qualcosa in mente, si accettano consigli
Grazie ancora e buona giornata!