[ sapet @ 20.04.2021. 07:40 ] @
Pozdrav svima!

problem mi je sledeći:

jedna tabela je cenovnik.

druga tabela ima u jednoj ćeliji više vrsta iz cenovnika odvojenih zarezom.

Kako da saberem sve cene iz cenovnika, na osnovu kriterijumama iz celije.

Primer tabele u prilogu.

Hvala unapred
[ nenandi @ 21.04.2021. 07:27 ] @
Izvoli primer,

mozes i da probas da kriterije odvojis text to colums ali moras da napravis dovoljno mesta,
zatim sa formulom iz primera zameni mesto polja pod navodnicima brojevima celije
[ sapet @ 21.04.2021. 09:05 ] @
hvala nenandi

počeo sam da razmišljam u tom pravcu, kao što si mi i ti predložio
Tvoje sabiranje ću prihvatiti uz adekvatno prilagođavanje.

Mada sam se namučio da nađem rešenje za odvajanje teksta prebrojavajući zareze.
text to colums neću da radim jer će tabela biti velika, uz stalno punjenje novih redova.
Tako da mi treba automatizam koji mi odmah pokazuje cenu što mi je bitno.

Evo primera dokle sam došao.

Treba da skinem još taj jedan zarez i da napravim ograničenja:
- šta ako ima samo jedan, dva ili više od tri kriterijuma
- šta ako nema ni jedan.

Ovakvo trenutno rešenje traži da ima 3 kriterujuma, jer sam prepravio rešenje koje je razdvajalo ime, srednje slovo i prezime.
Ne mogu da nađem neko elegantno rešenje za izdvajanje teksta u kolone, koji je razdvojen zarezima a da me ne ograničava, jer može da bude i više kriterijuma a moraču da ograničim za sada na samo tri što mi mnogo remeti zamisao tabele.

Svaka pomoć je dobrodošla
[ Jpeca @ 21.04.2021. 18:58 ] @
Ako koristiš Office 365 ima funkcija FILTERXML koja omogućava da formiraš dinamički niz od texta razvdvojenog zarezom. Vidi FilterXML. Potrebno je formirati XML string i za to koristiš SUBSTITUTE , sa xml tagovima.
U tvom slučaju nakon što razdvojiš na pojedinačne delove koristiš VLOOKUP da nađeš cene i onda sabereš:

Code:
=SUM(TRANSPOSE(VLOOKUP(FILTERXML(
"<t><s>"&
SUBSTITUTE(
G5,
",",
"</s><s>"
)&"</s></t>",
"//s"
), $B$5:$C$9,2, FALSE)))


Ovo mora da se unese kao array formula Ctrl+Shift+Enter

Za ranije verzije Excela može se jednostavno napraviti i VBA funkcija koja bi ovo odradila.
[ sapet @ 21.04.2021. 20:00 ] @
Hvala Jpeca.

Nažalost, radim u excel 2007, i ne znam da napravim vba funkciju koja bi mi odradila posao.
Pokušavao sam na razne načine ali nisam uspeo.

Ako bi neko imao vremena da mi pomogne i uradi mi takvu funkciju bio bih mu zahvalan

Pozdrav
[ Jpeca @ 22.04.2021. 08:58 ] @
U prilogu su oba rešenja: sa FilterXML i korisnički definisanom funkcijom SplitString.
Ova funkcija ima dva argumenta separator - znak po kojem se vrši razdvajanje i text string u kome se traži sep
Rezultat koji vraća je niz stringova.
Code:
Option Explicit
Public Function SplitString(sep As String, text As String) As Variant
' Function which split provided text by separator sep
' and return an array of strings
'
' P.Jovanovic for elitesecurity.org
'
    Dim start As Integer, pos As Integer
    ' Declare array and dimension
    Dim arr() As String, N As Integer
    Dim Item As String
    N = 0
    start = 1
    pos = InStr(start, text, sep, 1)
    ' Loop through text and find sep
    While pos > 0
        ReDim Preserve arr(N)  ' add emp ty element to array
        Item = Mid(text, start, pos - start)
        arr(N) = Trim(Item)    ' set empty element to text part
        start = pos + 1        ' new position to search for separator
        pos = InStr(start, text, sep, 1)
        N = N + 1
    Wend
    ' Last item
    Item = Mid(text, start, Len(text) - start + 1)
    ReDim Preserve arr(N)
    arr(N) = Trim(Item)
    SplitString = arr
End Function




Dalje ovu funkciju možeš da iskoristiš u formuli
Code:
=SUM(IFNA(VLOOKUP(SplitString(",", G5),$B$5:$C$9,2, FALSE),0))

Rešenje je slično kao za FormatXML - na razdvojene delove koristiš VLOOKUP da nađeš cene i onda sabereš. Jedino sam još ubacio IFNA - da se oni koji nisu nađeni u cenovnik tabeli uzimaju sa 0. Ovo ne treba kao array formula - može običan SUM.

[ sapet @ 22.04.2021. 10:14 ] @
Izvini Jpeca,

nešto ne kapiram baš kako treba.

Ova funkcija SplitString mi vadi samo prvi kriterijum iz celije i predložena formula vadi za nju cenu iz cenovnika.
Na početku sam shvatio da radi kao matrična i da će mi za sve kriterijume odvojene zarezom sabrati cene.

I izbacio sam IFNA funkciju jer je nemam u 2007-ci, koja mi i ne treba jer su svi kriterijumi i izabrani iz cenovnika tako da ne može da se pojavi greška.

Da li sam dobro shvatio?
[ Jpeca @ 22.04.2021. 12:16 ] @
SplitString vraća niz vrednosti koje razdvojene zarezima za "kl, okt, sd" vraća niz {"kl", "okt", "sd"} što možeš da proveriš sa Evaluate formula, pa klik na evaluate - dobićeš rezultat posle izvršavanja SplitString. Makroi moraju da ti budu omogućeni u toj radnoj svesci.
[ sapet @ 22.04.2021. 13:30 ] @
Tako sam i shvatio ali meni ne radi.

evo kako kod mene radi-ne radi

[ Jpeca @ 22.04.2021. 16:22 ] @
Probao sam na starijoj verziji Excela i zaista ne radi. Ubacio sam ti dodatnu VBA funkciju koja zamenjuje vlookup i sum, pa iz nje posvao SplitString. Rešenje nije tako elegantno ali nadam se da radi
Znači sad se zove SumCena (SplitString je sakriveno) sa parametrima
Lista proizvoda
Tabela cenovnik (range, fiksirati)

Code:
=SumCena(G5; $B$5:$C$9)

[ sapet @ 24.04.2021. 07:38 ] @
Jpeca hvala na izdvojenom vremenu i elegantnom rešenju.

SumCena radi na primeru koji smo postavili, ali kada sam hteo da implementiram u moj excel fajl neće da radi.
Probao sam ali nisam uspeo da rešim problem, zašto ne radi.
Uspeo sam da dođem do krajnjeg cilja ali, što bi se reklo, peške. Bitno da radi.

Takođe imam problem sa vba jer mi radi nešto, ali kada zaštitim list onda neće.
Pošto sam sa vama ovde rešavao problem, podeliću i ovaj problem, za koji ću otvoriti novu temu.
Ovaj kod mi ne radi kada zaštitim list, a moram da bih zašttitio neke ćelije.

Code:
 
Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
'code runs on protected sheet
Dim oldVal As String
Dim newVal As String
Dim strSep As String
Dim strType As Long

'add comma and space between items
strSep = ", "

If Target.Count > 1 Then GoTo exitHandler

'checks validation type of target cell
'type 3 is a drop down list
On Error Resume Next
strType = Target.Validation.Type

If Target.Column = 5 And strType = 3 Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  If oldVal = "" Or newVal = "" Then
    Target.Value = newVal
  Else
    Target.Value = oldVal _
      & strSep & newVal
  End If
End If

exitHandler:
  Application.EnableEvents = True
  
  
  
  'otvara tabelu konkretno izabranog pacijenta
  
  If Target.Address = "$B$4" Then
 If Range("B4") = "" Then
  Range("tblKartoni").AutoFilter
  'prikazuje sve zapise
 
 Else
  Range("tblKartoni").AutoFilter Field:=1, Criteria1:=Range("B4"), Criteria2:="=0", Operator:=xlOr
  
  'prikazuje izabranog pacijenta i prazne redove (ciji redni broj je 0 ali se ne vidi zbog
  'slovnog formatiranja koje boji font u belo)
  
 End If
End If

End Sub


Hvala još jednom na pomoći
[ Jpeca @ 24.04.2021. 09:47 ] @
Ne mogu da budem siguruan u čemu je problem jer nisi postavio primer. U principu ako nešto radiš nad protektovanim listom u VBA možeš da uradiš unprotekt - prethodno i protekt na kraju.


Code:
'Unprotect current worksheet
ActiveSheet.Unprotect
...tvoj kod 
' Protect current worksheet
ActiveSheet.Protect 


Ako imaš password dodaj ga kao parametar za unprotect i protect VB protect and unprotect worksheet
[ sapet @ 24.04.2021. 12:36 ] @
Jpece

postavio sam fajl koji sam pravio.
- pacijent - samo jednom se unosi
- karton - svaki pacijent se unosi pri svakoj terapiji (može da ima više terapija i u ceni se sabiraju pojedinačne - sada mi rade sa samo 3 jer mi tvoja funkcija ne radi)
- cenovnik

Ova kombinacija unprotect i protect radi izvrsno i odmah sam je primenio.
Otkrio sam i deo koda koji mi ne radi kada je list zaštićen, ali mi je sada sve jedno.

Žao mi je što tvoju funkciju nisam mogao da primenim jer nije radila. Najverovatnije sam negda ja fulao u primeni.

Hvala Jpece na pomoći

[ Jpeca @ 24.04.2021. 17:15 ] @
Baš si se potrudio! Ja se ne sećam da sam nekad uložio toliko truda da doteram Excel tabelu.
U mom kodu podrazumeva se da cenovnik ima dve kolone i da se cena uzima iz druge- moguće da ti je to bio problem. Sad sam to prepravio da se šalje dodatni parametar koji označava broj kolone iz koje se sumira cena i to primenio na tvom primeru pa pogledaj.
[ 3okc @ 26.04.2021. 07:38 ] @
Možda ova formula odradi posao ali mislim da je VBA bolje rešenje, pogotovo ako ne razumeš princip po kome formula radi.

Code:

=SUMPRODUCT(--MID(C3;SEARCH("/";SUBSTITUTE(","&C3;",";"/";ROW(INDIRECT("1:"&LEN(C3)-LEN(SUBSTITUTE(C3;",";""))+1)))); SEARCH("/";SUBSTITUTE(C3&",";",";"/";ROW(INDIRECT("1:"&LEN(C3)-LEN(SUBSTITUTE(C3;",";""))+1))))-SEARCH("/";SUBSTITUTE(","&C3;",";"/";ROW(INDIRECT("1:"&LEN(C3)-LEN(SUBSTITUTE(C3;",";""))+1))))))


Ukratko, koristim treći parametar SUBSTITUTE da redom menjam pojave simbola kojim hoću da podelim string, ovde koristim "/" ili neki drugi simbol koji "znam" da se neće pojaviti i onda sa SEARCH očitam na kojoj je poziciji. Ovim imam podatke za MID, a širina svake cifre biće razlika ove lokacije kada je očitam sa desne strane, "minus" lokacija sa leve.