[Excel] Formiranje svih kombinacija

[Excel] Formiranje svih kombinacija 17.10.2007. u 08:57 · pre 231 mesec #1

Treba mi formula koja izlistava sve kombinacije elemenata u odredjenoj tabeli, ali bez kombinacija medju elementima koji se nalaze u istim kolonama.
Zadatak glasi ovako: Ako kompanija nudi odredjeni broj tipova mesecne pretplate na neku uslugu i s tim da klijent moze da uzme sve tipove koje zeli i da svaki tip moze da uzme najvise odredjeni broj puta mesecno, navesti sve kombinacije koje su mu na raspolaganju.
Ili jos jasnije, primer se moze naci kod tarifnih dodataka mobilnih operatera. Recimo MTS SMS dodaci: MTS nudi cetiri tipa dodataka sms60, sms90, sms300 i sms600. Korisnik moze da uzme bilo koji ili sve dodatke svakog meseca s tim sto svaki dodatak moze da uzme najvise 3 puta u toku meseca. Izracunati na koje sve nacine korisnik moze da iskombinuje dodatke.

Tabela za ovaj primer:
A B C D E
1. tip paketa/broj koriscenja sms60 sms90 sms300 sms600
2. 1. 60 90 300 600
3. 2. 120 180 600 1200
4. 3. 180 270 900 1800

Znaci treba mi formula koja ce izlistavati kombinacije tipa

B2
C3
B2,C2
B4,C3,E3
B2,C4,D4,E3

itd.....

Znaci sve kombinacije 1,2,3 i 4 klase, ali bez kombinacija koje ukljucuju dva ili vise elemenata iz iste kolone, tipa B2,B3,C2.

Tabela.xls13.5k

Re: [Excel] Formiranje svih kombinacija 18.10.2007. u 12:45 · pre 231 mesec #2

Ako sa 0 obeležimo da određeni tip ne učestuvuje u kombinaciji, a sa 1,2 i 3 tip koji učestvuje u kombinaciji i pošto imaš četiri tipa, dobijamo da se problem svodi na generisanje svih četvorocifrenih brojeva koji se mogu sastaviti od cifara 0,1,2,3. Ovo praktično znači generisanje svih brojeva manjih od najmanjeg petocifrenog broja u sistemu sa osnovom 4. Takvih brojeva ima 255 odnosno 256 ako uključimo i 0 (praznu kombinaciju). Jedno rešenje je da pomoću autofil napraviš niz 1,2,...255 pa to konvertuješ u brojeve sa osnovom 4. Pošto koliko je meni poznato ne postoji direktna funcija u Excelu možeš konvertovati u binarni broj, pa svake dve cifre binarnog broja prevesti u cifru ternarnog brojnog sitema, pomoću tekstualnih funkcija
00 -> 0
01 -> 1
10 -> 2
11 -> 3
Dalje cifre koje si dobio služe ti kao ofset da pristupiš vrednostima iz tabele tipova i generisanjeodgovarajućih kombinacija.
Meni je ipak lakše da napišem kod koj generiše kombinacije :

Code:

Sub ListKomb()
' Lista sve kombinacije za zadatu tapelu mogucnosti
' tabela mora biti 4x4
' P.Jovanovic za elitesecurity.org
'
Dim rw As Long
Dim cl As Range
Dim tbl As Range

   ' tabela sa tipovima  - ukljucuje i praznu kombinaciju
   Set tbl = ActiveSheet.Range("B2:E6")
   ' pocetak liste sa kombinacijama
   Set cl = ActiveSheet.Range("A8")
   rw = 0  ' Pocetni red
   For i = 0 To 3            ' Tip 1
      For j = 0 To 3         ' Tip 2
         For k = 0 To 3      ' Tip 3
            For l = 0 To 3   ' Tip 4
               cl.Offset(rw, 0) = tbl.Offset(i, 0).Value
               cl.Offset(rw, 1) = tbl.Offset(j, 1).Value
               cl.Offset(rw, 2) = tbl.Offset(k, 2).Value
               cl.Offset(rw, 3) = tbl.Offset(l, 3).Value
               rw = rw + 1
            Next l
        Next k
      Next j
   Next i

End Sub

U kodu je svaka kombinacija ispisana u četiri susedne ćelije, ali ti možeš prepraviti da ispisuje kako želiš sa zarezima koristeći funkcije TRIM i operator za spajanje teksta &

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

KombTabela.xls49k

Re: [Excel] Formiranje svih kombinacija 18.10.2007. u 12:48 · pre 231 mesec #3

Iskopao sam ti ovaj antikvitet iz zbirke rešenih zadataka u BASIC-u jer računa ovo što tebi treba - kombinacije bez ponavljanja, pa vidi kako možeš da ga iskoristiš. Nisam se preterano udubljivao u tvoj "zadatak", iako si ga prilično detaljno opisao (što je za pohvalu s obzirom na to koliko njih preferira haiku ovde), ali razumećeš ako nemam naviku da za drugog završavam komercijalne poslove; sem ako mi nije poslodavac, razume se. Elem, kod je prilično gadan, napisan je u maniru koji se odavno preporučuje za izbegavanje ali, kako s'ono kaže, poklonjenom kodu koji radi ono što sam ne umem da napišem, ne gleda se u sintaksu. ;)

Code:
' KOMBINACIJE BEZ PONAVLJANJA KLASE bC [BYTE]
' OD UNETIH ELEMENATA BEZ RAZDVAJANJA [STRING]
' A ZA PROIZVOLJAN BROJ KARAKTERA U STRINGU.
' KOMBINACIJE SE STAMPAJU U PRVE DVE KOLONE
' KOJE SE PRETHODNO OBRISU [A:B].
'
Option Explicit

Sub Kombinacije()
Dim bC As Byte
Dim bCif As Byte
Dim bRow As Byte
Dim bL As Byte
Dim bS As Byte
Dim bEl As Byte
Dim strSviEl As String
Dim strC As String
Dim strInp As String
Dim blArr() As Boolean

    strInp = InputBox("K = ", "Kombinacije")
    bC = CByte(strInp)
    strSviEl = InputBox("Elementi: ", "Kombinacije")
    strInp = InputBox("Broj cifara po elementu: ", "Kombinacije")
    bCif = CByte(strInp)
    bEl = Len(strSviEl)
    ReDim blArr(1 To bEl) As Boolean
    Range("A:B").ClearContents
    Range("A1").Value = "Kombinacije klase K = " & bC
    Range("A2").Value = "Od elemenata " & strSviEl & ":"
    bRow = 1
Nod1: bL = 1
Nod2: If blArr(bL) = False Then GoTo Nod3
    blArr(bL) = False
    bL = bL + bCif
    If bL < bEl + 1 Then GoTo Nod2
    Exit Sub
Nod3: blArr(bL) = True
    bS = 0
    For bL = 1 To bEl Step bCif
        If blArr(bL) Then Let bS = bS + 1
    Next bL
    If bS <> bC Then GoTo Nod1
    For bL = 1 To bEl Step bCif
        If blArr(bL) Then Let strC = strC & Mid(strSviEl, bL, bCif)
    Next bL
    Cells(bRow + 3, 1).Value = CStr(bRow) & ")"
    Cells(bRow + 3, 2).Value = "'" & strC
    bRow = bRow + 1
    strC = ""
    GoTo Nod1
End Sub

Ubaci kod u Excel, čiji objekti su upotrebljeni za ispis, da bi video efekte. Programčić je namenjen za opšti slučaj pa računa kombinacije samo za unetu klasu, a ako sam dobro shvatio, tebi je potreban ispis za sve klase počev od 1 pa do tražene (koja je opet, čini mi se, fiksna). To naravno nije teško prilagoditi a ti ćeš, nadam se, umeti sam da završiš i za ostale zahteve (koje nisam sasvim razumeo, btw).

U programu se elementi unose integralno, svi su u jednom stringu, ali je dozvoljena proizvoljna širina (može biti >1) tako bi, eventualno mogao da koristiš kombinacije adresa ćelija. Recimo, najbolje je videti na primeru, ako je klasa = 4, elementi "12345", br.karak. po elem. = 1, dobiješ ovo na izlazu:

Citat:

Kombinacije klase K = 4
Od elemenata 12345:

1) 1234
2) 1235
3) 1245
4) 1345
5) 2345

a ako je klasa = 3, elementi "B2C2B3C4", br.karak. po elem. = 2:

Citat:

Kombinacije klase K = 3
Od elemenata B2C2B3C4:

1) B2C2B3
2) B2C2C4
3) B2B3C4
4) C2B3C4

Naravno, mogao bi programčić da prilagodiš i tako da radi kao makro f-ja pa da joj iz ćelija prosleđuješ potrebne argumente. Izlazni string onda možeš iskoristiti preko f-je INDIRECT(), npr. S tim da bi onda bilo poželjno dodati f-ji još i razne provere ispravnosti i druge kontrole grešaka što sve ovde nedostaje.

Potpis

Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!

Re: [Excel] Formiranje svih kombinacija 18.10.2007. u 18:12 · pre 231 mesec #4

@Jpeca
Izgleda da ovo radi savrseno ono sto mi treba. Hvala!
Ja sam poprilicni pocetnik u excelu i programiranju, tako da cu malo morati da lupam glavu da provalim zasto ovaj kod funkcionise bas kako treba, ali sad bar imam oko cega da lupam glavu. Onako nisam znao odakle da pocnem.

@3okc

Citat:

poklonjenom kodu koji radi ono što sam ne umem da napišem, ne gleda se u sintaksu. ;)

Bas tako :) Meni je najvise i bio potreban princip po kojem se generisu kombinacije. Kad budem sebi potpuno rastumacio kod, lako cu ga posle modifikovati.

Pozdrav.

Re: [Excel] Formiranje svih kombinacija 19.10.2007. u 08:24 · pre 231 mesec #5

Oko koda koji sam ja postavi ne treba previše da lupaš glavu - kod je krajnje jednostavan. Verovatno sam ja bio konfuzan pokušavajući da problem rešim bez koda (uz put nije ni to problem bar u tvom specijalnom slučaju).

Kod funkcioniš kao kilometar sat na autombilu. Svaka for...next petlja pretstavlja jedan prsten (jednu cifru na kilometar satu). Prvo su prva tri prstena sa leve strane fiksirana, a poslednji prsten se okreće. Kad je poslednji prsten okrenuo pun krug prvi do njega se pomeri za jedno mesto, pa se opet poslednji okreće pun krug itd.
Broj elemenata koji se pojavljuju u kombinaciji određuju broj petlji - zato ovaj kod nije univerzalan nego moraš dodavati nove petlje za nove elemente. Broj mogućih pojavljivanja jednog elementa ("cifre") određuje brojač petlje - u tvom slučaju je četiri (0 to 3) - ali se ovo može promeniti za ma koji od elemenata jednostavnom izmenom umesto 3 neki drugi broj.
Na kraju ofset se koristi da umesto cifre 0,1, 2,3 prikaže pravi oblik elementa - npr za kolonu SMS 60: 60,120,180 izvlečeći odgovarajući podatak iz tvoje tablele.

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

1

Re: [Excel] Formiranje svih kombinacija 19.10.2007. u 20:57 · pre 231 mesec #6

Da, to ima smisla.

Forum kao aplikacija na telefonu

Srodne teme