aiuto formula please

Hai un problema con Excel?

Nuova discussione

aiuto formula please
di kaleido (utente non iscritto) data: 04/05/2016 10:37:41
salve,

avrei bisogno di una formula nel file che allego, che in base alle volte che il cliente visita una sede mi dica se paga con sconto o no:

1 - le prime 3 volte che visita ogni sede paga prezzo pieno (quindi la formula deve scrivere SI nella cella gialla nel foglio 2)
2 - se visita 2 volte la sede di mialno e 2 volte la sede di roma paga sempre prezzo pieno, mentre a partire della quarta visita (sempre tenendo presente le singole sedi e non il totale di visite nelle sedi),la formula nella cella gialla nel foglio 2 scrive NO)

Potreste aiutarmi please?
di Mister_x (utente non iscritto) data: 04/05/2016 13:06:15
ciao

migliorabile
comunque io in foglio2 B3 attualmente metterei la seguente formula

=SE(O(MATR.SOMMA.PRODOTTO((Foglio1!C2:C9=Foglio2!F2)*(Foglio1!B2:B9="ROMA"))>2;MATR.SOMMA.PRODOTTO((Foglio1!C2:C9=Foglio2!F2)*(Foglio1!B2:B9="MILANO"))>2);"NO";"SI")

ciao
di kaleido (utente non iscritto) data: 04/05/2016 14:41:23
mister x non sembra funzionare...

nella casella F1 del foglio2 quando scrivo il codice 1014, la formula in D3 dovrebbe scrivere SI (perchè il cliente ha visitato la sede di ROMA 3 volte e fino a 3 paga), mentre se scrivo il codice 1299 mi esce scritto correttamente NO.

Ricordo che la formula in base a B2 del foglio2 (pero tenendo conto della diversa sede, deve scrivere SI fino a 3 visite alla stessa sede e NO da 4 in poi) la sede che la formula deve prendere come riferimento è quella che corrisponde al codice immesso in F1 nella colonna SEDE del foglio1.

help please
di kaleido (utente non iscritto) data: 04/05/2016 14:59:47
praticamente parte dal cliente e dalla sede che corrisponde al codice immesso in F1 e conta tutte le volte che il cliente ha visitato la sede che corrisponde al codice immesso in F1. Fino a 3 scrive SI e da 4 in poi scrive NO.
di kaleido (utente non iscritto) data: 04/05/2016 15:09:32
ovviamente conterá dalla visita corrispondente del codice (compresa) all'indietro e, prendendo in considerazione la sede corrispondente al codice, conterà le volte e fino a 3 volte scriverà SI e dalla quarta in poi NO.

Capisco che la cosa è un pò complicata e spero che sono riuscito a rendere al meglio l¡idea.
di Mister_x (utente non iscritto) data: 04/05/2016 18:17:44
ciao

basta aggiungere il controllo anche a F1 per la colonna A:A

=SE(O(MATR.SOMMA.PRODOTTO((Foglio1!A2:A9=Foglio2!F1)*(Foglio1!C2:C9=Foglio2!F2)*(Foglio1!B2:B9="ROMA"))>3;MATR.SOMMA.PRODOTTO((Foglio1!A2:A9=Foglio2!F1)*(Foglio1!C2:C9=Foglio2!F2)*(Foglio1!B2:B9="MILANO"))>3);"NO";"SI")

ciao
di kaleido (utente non iscritto) data: 04/05/2016 20:46:49
continua a non funzionare bene...
di Mister_x (utente non iscritto) data: 05/05/2016 00:00:37
ciao

con i due dati che hai messo nel fai postato, con la password inserita in vba dove non capisco cosa succede al variare di una cella
il nominativo e' solamente uno
le citta solamente 2
la terza opzione una per sorte
sul file che io ho a disposizione fa il suo lavoro

adesso spiega cosa succede sul tuo da come penso ai a disposizione molteplici dati , e come penso le colonne e i fogli non sono uguali a quello postato
se ti vuio trovare un SI al Posto di un NO , io ho interpretato cosi la cosa di ritorno basta che in fondo alla formula inverti la posizione del SI e del NO

ultima cosa , se vogliamo tagliare la testa al toro, metti un file con piu' fattori e piu' nominativi e togli le Sub() o almeno togli la password al VBA, e nella cella con i dati che inserisci a mano non con formule cosa ti vuoi trovare

ciao
di kaleido (utente non iscritto) data: 05/05/2016 08:44:46
salve mister x,

allego nuovo file dove dovrebbe risultare piu chiaro il concetto con l'utilizzo delle celle colorate. ho sbloccato anche il vba.

fammi sapere se posso aiutare in altro.

grazie
di kaleido (utente non iscritto) data: 05/05/2016 08:54:31
se per esempio si immette il codice 2016-00016 che corrisponde al cliente bianchi ed alla sede milano, la formula conta 4 volte e scrive no
invece se si immette il codice 2016-00012 che corrisponde sempre al cliente bianchi pero questa volta alla sede roma, la formula conta 3 volte e scrive si

e cosi via. in base al codice immesso in f1 nel foglio 2 e la sede ed il cliente ad esso collegate (che sarebbero nella tabella del foglio 1), la formula conta quante volte siano presenti fino alla riga che inizia con il codice nel foglio 1 e conta ed in base al numero di volte scrive si o no.
di Mister_x (utente non iscritto) data: 05/05/2016 16:08:40
ciao

ho scaricato 30 minuti fa e dopo uno sguardo cosi veloce ho valutato di creare una colonna di appoggio in foglio1 , la quale la potrai nascondera
ho dovuto creare un miop file con i tuoi dati in quanto il tuo mi dava problemi
comunque rapida spiegazione in foglio1 colonna I riga 2 ho inserito la seguente formula
=MATR.SOMMA.PRODOTTO(($B$2:B2=B2)*($C$2:C2=C2)) e trascinata fino alloccorrenza
in foglio2 cella B2
=INDIRETTO("foglio1!"&INDIRIZZO(CONFRONTA(F1;Foglio1!A1:A17;0);9))
B3=SE(B2>3;"NO";"SI")
ho aggiunto altre formule per inquadramento ai dati
F2=CERCA.VERT(F1;Foglio1!A2:C17;3;FALSO)
F3=INDIRETTO("foglio1!"&INDIRIZZO(CONFRONTA(F1;Foglio1!A1:A17;0);2))

PS attenzione che excel non digerisce molto le celle unite ( evitatele se dovete inserire formule o fare calcoli)
allego il mio file di prova

ciao
PS io ho excel in Italiano
di Kaleido (utente non iscritto) data: 05/05/2016 20:57:38
Adesso sembra funzionare alla perfezione.
Solo mi chiedo se è possibile fare a meno della colonna d'appoggio..

di Mister_x (utente non iscritto) data: 06/05/2016 01:12:38
ciao

forse si potra anche fare con le formule , ma nel mio caso la praticita' del VBA mi e' più famigliare

Sub da inserire nel modulo del foglio2, lasciando invariate le altre formule nelle varie celle
questa allla variazione della cella F1 fa il ricalcolo riportando il valore in B2
attualmente per mia comodita' lo riporta in H2 vedi nota basta modificare

riallego il file con la Sub() inserita dove attualmente ti riporta in B2 ancora il valore con la colonna di appoggio e in H2 il valore calcolato con la sub() inserita

ciao

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
On Error Resume Next
Dim i As Long
Dim C_volte As Long
C_volte = 0
Dim SH1 As String
SH1 = "Foglio1"
For i = 2 To Sheets(SH1).Cells(Rows.Count, "A").End(xlUp).Row
 If Sheets(SH1).Cells(i, "A") <> Range("F1") Then
   If Sheets(SH1).Cells(i, "B") = Range("F3") And Sheets(SH1).Cells(i, "C") = Range("F2") Then
   C_volte = C_volte + 1
   End If
 Else
   C_volte = C_volte + 1
   Exit For
 End If
Next i
Range("H2") = C_volte '' mettere il Range a B2
End Sub

di Mister_x (utente non iscritto) data: 06/05/2016 01:42:16
ciao

altro tipo di Sub() in Foglio2 dove in questo caso non servono le varie funzioni inserite in
F2:F3:B2:B3
ma al variare di F1 questa riporta i vari dati

ciao

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
On Error Resume Next
Dim i As Long, Nriga As Long
Dim C_volte As Long
C_volte = 0
Dim SH1 As String
SH1 = "Foglio1"
Dim Cliente As String, Citta As String
For i = 2 To Sheets(SH1).Cells(Rows.Count, "A").End(xlUp).Row
 If Sheets(SH1).Cells(i, "A") = Range("F1") Then
  Nriga = i
  Cliente = Sheets(SH1).Cells(i, "C")
  Citta = Sheets(SH1).Cells(i, "B")
 End If
Next i
For i = 2 To Nriga
 If Sheets(SH1).Cells(i, "C") = Cliente And Sheets(SH1).Cells(i, "B") = Citta Then
  C_volte = C_volte + 1
 End If
Next i
Range("B2") = C_volte
Range("F2") = Cliente
Range("F3") = Citta
Range("B3") = "SI"
If C_volte > 3 Then Range("B3") = "NO"
End Sub

di cromagno data: 06/05/2016 01:43:59
Ciao a tutti,
e complimenti a Mister_x (che saluto) per le soluzioni

@Kaleido
Se non vuoi la colonna d'appoggio, nella cella B2 del "Foglio2" potresti usare:

=MATR.SOMMA.PRODOTTO((Lista_Escalas[CLIENTE]=Foglio2!F2)*(Lista_Escalas[SEDE]=Foglio2!F3)*((DESTRA(Lista_Escalas[CODICE];5)*1)<=(DESTRA(Foglio2!F1;5)*1)))

Ti riallego il file ("Carte3_Sub - unica")...

P.S.
naturalmente i codici in colonna A del Foglio1 devono essere in ordine crescente.
di kaleido (utente non iscritto) data: 06/05/2016 11:42:11
grazie mille mister x. ho utilizzato la tua ultima macro. Ora solamente devo sproteggere e riproteggere il foglio per far si che la macro funzioni.

grazie ancora

di kaleido (utente non iscritto) data: 09/05/2016 09:50:10
adesso la macro è così però non riesco a ri-proteggere il foglio una volta eseguita.

Potreste aiutarmi please?

Grazie

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Gastos").Unprotect Password:="123"
If Not Intersect(Target, Range("F1")) Is Nothing Then
On Error Resume Next
    Select Case Len(Target.Value)
    Case Is = 1
    Target.Value = Year(Date) & "0000" & Target.Value
    Case Is = 2
        Target.Value = Year(Date) & "000" & Target.Value
    Case Is = 3
        Target.Value = Year(Date) & "00" & Target.Value
    Case Is = 4
        Target.Value = Year(Date) & "0" & Target.Value
    Case Is = 5
        Target.Value = Year(Date) & "" & Target.Value
    End Select
End If
If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
On Error Resume Next
Dim i As Long, Nriga As Long
Dim C_volte As Long
C_volte = 0
Dim SH1 As String
SH1 = "Escalas"
Dim Barco As String, Puerto As String
For i = 2 To Sheets(SH1).Cells(Rows.Count, "A").End(xlUp).Row
 If Sheets(SH1).Cells(i, "A") = Range("F1") Then
  Nriga = i
  Barco = Sheets(SH1).Cells(i, "C")
  Puerto = Sheets(SH1).Cells(i, "B")
 End If
Next i
For i = 2 To Nriga
 If Sheets(SH1).Cells(i, "C") = Barco And Sheets(SH1).Cells(i, "B") = Puerto Then
  C_volte = C_volte + 1
 End If
Next i
Range("C62") = "SI"
If C_volte > 3 Then Range("C62") = "NO"
Sheets("Gastos").Protect Password:="123"
End Sub

di Mister_x (utente non iscritto) data: 09/05/2016 10:44:12
ciao

la sub() in sostanza non ha bisogno di 2 controlli , ma si riduce in questo modo

ciao

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub ''controllo scrittura F1 non protetta
On Error Resume Next
''''''''''''''' indicizzazione delle variabili  '''''''''''''
Dim i As Long, Nriga As Long
Dim C_volte As Long
C_volte = 0
Dim SH1 As String
SH1 = "Escalas"
Dim Barco As String, Puerto As String
''''''''''''''''''Inizia il Lavoro ''''''''''''''''''''
Sheets("Gastos").Unprotect Password:="123"  ''Toglie protezione foglio
    Select Case Len(Target.Value)            ''Controllo immissione in F1
    Case Is = 1
    Target.Value = Year(Date) & "0000" & Target.Value
    Case Is = 2
        Target.Value = Year(Date) & "000" & Target.Value
    Case Is = 3
        Target.Value = Year(Date) & "00" & Target.Value
    Case Is = 4
        Target.Value = Year(Date) & "0" & Target.Value
    Case Is = 5
        Target.Value = Year(Date) & "" & Target.Value
    End Select  '' Fine controllo
'' ricerca dei dati per il calcolo
For i = 2 To Sheets(SH1).Cells(Rows.Count, "A").End(xlUp).Row
 If Sheets(SH1).Cells(i, "A") = Range("F1") Then
  Nriga = i
  Barco = Sheets(SH1).Cells(i, "C")
  Puerto = Sheets(SH1).Cells(i, "B")
 End If
Next i
For i = 2 To Nriga
 If Sheets(SH1).Cells(i, "C") = Barco And Sheets(SH1).Cells(i, "B") = Puerto Then
  C_volte = C_volte + 1
 End If
Next i
Range("C62") = "SI"
If C_volte > 3 Then Range("C62") = "NO"
''''''' fine lavoro
Sheets("Gastos").Protect Password:="123"  ''attivazione della pass nel foglio
End Sub

di kaleido (utente non iscritto) data: 09/05/2016 14:06:27
Grazie mille mister x. adesso tutto funziona alla perfezione.

Saluti

Vuoi Approfondire?