VBA uparivanje članova beskonačnog broja nizova

VBA uparivanje članova beskonačnog broja nizova 21.03.2019. u 20:44 · pre 92 meseca #1

Imam jedan problem koji umem da resim ali bih zeleo neko programerski zrelije resenje.
Naime. Imam 3 sheet-a. U svakom sheet-u se nalaze neki podaci a jedini bitan podatak je u koloni "Tender", koji predstavlja zavodni broj tendera.
Potrebno je pronaći brojeve tendera koji se ponavljaju kako u istom sheet-u, tako i u različitim sheet-ovima.

Problem je lako rešiv na način kako sam ga ja započeo u prilogu.
Medjutim shvatio sam da imam 2 problema:
1. na ovaj način ne mogu pronaći parnjake u jednom rangu već samo parnjake izmedju 2 razlicita ranga (parnjaci u rangu 1 i rangu2, rangu1 i rangu3, rangu 2 i rangu 3) pa pretraživanje ranga ne mogu obaviti putem for each funcije
2. u slucaju dodavanja novog niza (novog sheet-a u excelu), moram bitnije menjati program a verujem da postoji elekgantnije rešenje.

Ja bi sada to uradio na sledeći način:
1. bih proverio kroz for funkciju da li postoje parnjaci u rangu banka1
2. bih proverio kroz for funkciju da li postoje parnjaci u rangu banka2
3. bih proverio kroz for funkciju da li postoje parnjaci u rangu banka3
(dakle to bi bile 3 if fukncije jedna za drugom)

4. bih proverio preko for each funkcije da li postoje parnjaci izmedju banka1 i banka2 po metodi koja se nalazi u fajlu u prilogu
5. bih proverio preko for each funkcije da li postoje parnjaci izmedju banka1 i banka3 po metodi koja se nalazi u fajlu u prilogu
6. bih proverio preko for each funkcije da li postoje parnjaci izmedju banka2 i banka3 po metodi koja se nalazi u fajlu u prilogu
(i ovo bih uradio pomoću 3 for each funkcije jedna za drugom)

Program bi korektno radio ali mi izgleda ružno jer jer kada se bude radilo sa 10 banaka umesto sa 3, program ce biti nepregledan.

Da li neko ima ideju kako to da uradim na elegantan način, gde bih samo u slučaju dodavanja nove banke, uneo u program samo novi rang koji proverava (banka4, banka5, banka6...)?
U suštini problem se svodi na uparivanje beskonačnog broja nizova, pri kojem se uporedjuje svaki član niza sa članovima istog niza kao i sa članovima ostalih nizova kako bi se pronašli parnjaci. Nizovi nemaju jednak broj članova.

Test1.xlsm18.07k

Re: VBA uparivanje članova beskonačnog broja nizova 21.03.2019. u 21:44 · pre 92 meseca #2

Problem je što ne razmišljaš kao programer.

U prilogu je rešenje.
Razmišljaš u okviru IZVEŠTAJA umesto da razmišljaš o PODACIMA.

Podaci su relacije. U tvom slučaju relacija je:

{BANKA} sa određenim imenom je izdala {GARANCIJU} za {TENDER}

Dakle, treba da napravi[ TABELU (koja odgovara relaciji gore) i koja će kao atribute (kolone) imati BANKU, GARANCIJU i TENDER

Za ovakvu tabelu se kaže da je u prvoj normalnoj formi (ako te interesuje šta su to normalne forme, proguglaj pojmove 1NF, BCNF Boyce-Codd normal form).

Kada si raščistio šta su podaci, lako ćeš da napraviš IZVEŠTAJE tako da je jedan list jedna banka, a ne da radiš naopako.

Test2.xlsm24.67k

Re: VBA uparivanje članova beskonačnog broja nizova 21.03.2019. u 22:28 · pre 92 meseca #3

To je nemoguće odraditi na ovaj način.
Stvarna tabela je mnogo kompleksnija, sa mnogo različitih obračuna i makroa, te iz objektivnih razloga ne može sve da bude u jednom sheet-u. Inače, tu kontrolu i vršim na taj način što podatke iz svih sheet-ova kopiram u jedan pa preko pivota tražim count po broju tendera i gde je broj veći od 1 pogledam ručno o čemu se radi.

Pošto to oduzima puno vremena, smislih da napravim makro koji će isčitati sve brojeve tendera u svakom sheet-u i brojeve tendera koji se ponavljaju obojitu u određenu boju i u komentaru ubaciti sve adrese na kojima ih je našao da bi korisnik mogao ručno da proveri o čemu se radi (pošto postoje slučajevi kada se isti broj tendera ponavlja više puta, ali je bitmo da se skrene pažnja samo na njih a ne da se pregledaju stotine unosa).

Kažem, mogu to da rešim na divlji način i radiće, ali pretpostavljam da vi programeri možete da smislite bolji način koji bi mogao da pregleda članove neograničenog broja nizova.
Ja bar to kao neškolovan za informatiku na taj način razumem. Čak mi je posle, kada sam već napisao prvi post palo na pamet da se tu možda radi o dvodimenzionalnom nizu (ako se to tako zove), s tim što me malo buni to što svaki niz nema jednam broj članova pa bih morao da se zadubim kako to rešiti.

Podaci iz svakog sheet-a su jedan niz pa dobijemo tabelarnu formu niza, npr ako imam 5 banaka i ako najveći niz ima 100 članova to bi bio dvodimenzionalni niz 5*100.
I opet pretpostavljam da se lako kreira petlja koja uporedjuje svaki član sa svakim članom tog niza, ali možda grešim.

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 00:16 · pre 92 meseca #4

Ne treba ti matrica već niz nizova.

Umesto da koristiš banka1, banka2, ... bankaN varijable,
napravi niz banka od N elemenata i u njega stavi podatke od banke1 do bankeN

Posle toga napravi dve ugneždene petlje A i B tako da:
A ide od 0 do N-2
B ide od A+1 do N-1

Unutar B petlje stavi još dve petlje i uradi poređenje kao što i sada radiš,
s' tim da u njima ideš kroz banka[A] i banka[B]

Kad naiđeš na duplikat, onda nakalemi na prvo polje adresu drugog polja a i kontra,
ofarbaj ih, itd....

Naravno, postoje i mnogo lepša rešenja npr.
korištenjem kombinacije Dictionary-ja i Collection-a.

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 07:11 · pre 92 meseca #5

Mislim da si u pravu, da je to najelegantnije resenje.

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 09:02 · pre 92 meseca #6

Takodje mogu da se naprave klase i da se posle instance istih (tj. objekti) dodaju u kolekciju.

Tu ce i For Each onda isto doci do izrazaja.

Znaci klasa za banka (i kolekcija banaka). Onda klasa transakcija/tender (i kolekcija tendera). Svaka banka moze imate kolekciju tendera.

Takodje treba isto razumeti ali i prihvatiti savet sto je @djoka_l rekao da se ne ponavljam.

Sustina je u normalizaciji a opet ako je nemate u direktnom izvornom obliku onda istu mozete napraviti u momentu pred pocetak procesa koji hocete da napravite - znaci da se izvrsi svodjenje jednog oblika u potrebni oblik.

Sto se tice VBA i rada sa klasama evo sa web malo informativno:

www.cpearson.com/excel/classes.aspx

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 11:31 · pre 92 meseca #7

Dao mi je i @djoka_l ideju, mogao bih pomoću VBA da generišem novi sheet u koji će se spojiti podaci iz svih sheet-ova i onda da automatski generiše pivot iz tih podataka, mada mislim da ću primeniti onaj metod da nemam više nizova već jedan niz u kome su mi skupljeni podaci iz svih sheet-ova. To mi deluje kao najbrže rešenje za ovo što mi je potrebno.

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 12:39 · pre 92 meseca #8

to moze da se uradi pomocu nizova ali moze i pomocu klasa i kolekcije (objekata) klasa isto.
na primer, podaci se pokupe sa svih sheetova i ubace u kolekciju klasa i nako tkz. uvoza podataka se onda odradi sta treba i posle obradjeni podaci vrate na postojeci i novi sheet.

sa klasama je podrobnije opisivanje podataka i lakse gledano iz ljudsko ugla.

takodje isto moze recimo da se namerno kroz kod ide na gresku prilikom dodavanja objekta u kolekciju kada se koristi jedinstveni kljuc u kolekciji za svaki zapis koji se dodaje i da pritom zbog duplikata kada dodje do greske ista se ignorise... i tako dobije jedinstvena lista.
recimo key: 'Garancija 1 T-12546'

dole evo primer gde se koristi jedna klasa i nesto koda.
ovo je samo primer koji se moze prilagoditi da bude neogranicen broj banaka tako sto bi se deo oko provere uslova ispravio i odradio na drugi nacin.

Kod za 'Class Modules'
Name: cZapis

Code:

Private mBroj As String
Private mTender As String
Private mBanka As String
Private mB1 As Long
Private mB2 As Long
Private mB3 As Long
Public Property Let Broj(ByVal vData As String)
    mBroj = vData
End Property
Public Property Get Broj() As String
    Broj = mBroj
End Property
Public Property Let Tender(ByVal vData As String)
    mTender = vData
End Property
Public Property Get Tender() As String
    Tender = mTender
End Property
Public Property Let Banka(ByVal vData As String)
    mBanka = vData
End Property
Public Property Get Banka() As String
    Banka = mBanka
End Property
Public Function GetKey() As String
    GetKey = mBroj & " " & mTender
End Function
Public Property Let B1(ByVal vData As Long)
    mB1 = vData
End Property
Public Property Get B1() As Long
    B1 = mB1
End Property
Public Property Let B2(ByVal vData As Long)
    mB2 = vData
End Property
Public Property Get B2() As Long
    B2 = mB2
End Property
Public Property Let B3(ByVal vData As Long)
    mB3 = vData
End Property
Public Property Get B3() As Long
    B3 = mB3
End Property
Public Function GrandTotal() As Long
    GrandTotal = mB1 + mB2 + mB3
End Function

Primer koda koji:
- kreira listu svih zapisa iz INF sheet-a
- kreira potom jedinstvenu listu tendera i
- potom kreira posle statistiku po bankama

Code:

Private Sub CreateList(ByRef ThisSheet As Excel.Worksheet, _
                       ByRef ThisList As Collection, _
                       Optional ByVal iStartFromRow As Long = 2, _
                       Optional IsUniqueList As Boolean = False)

    Dim i As Long
    Dim iTotalRows As Long
    Dim sKey As String
    Dim xItem As cZapis

    ' Get total rows in sheet
    iTotalRows = ThisSheet.Cells(ThisSheet.Rows.Count, 1).End(xlUp).Row

    ' Go over rows in sheet
    For i = iStartFromRow To iTotalRows

        ' Create new instance of object
        Set xItem = New cZapis

        ' Assign values from sheet to class object
        With xItem
            .Broj = Cells(i, 1)
            .Tender = Cells(i, 2)
            .Banka = Cells(i, 3)
            sKey = .GetKey
        End With

        ' If we are not creating unique list then
        If IsUniqueList = False Then

            ' Add item to collection
            ThisList.Add xItem

        ' If we are creating unique list then
        Else

            On Error Resume Next

            ' Add item to collection with unique key
            ThisList.Add xItem, sKey

            ' If there was any error (guess item with same key already exists) then
            If Err.Number <> 0 Then

                ' Notification to user
                Debug.Print "Item with same key already exists.", sKey

                Err.Clear

            End If

        End If

    Next

    ' Notification about total number of items in list
    Debug.Print "Total items in list: " & ThisList.Count

End Sub

Public Sub LetsDoit()

    Dim xData As Collection
    Dim xList As Collection
    Dim xItem As cZapis
    Dim sKey As String

    Set xList = New Collection
    Set xData = New Collection

    ' INF - Sheet4
    CreateList Sheet4, xData

    ' Create unique list
    CreateList Sheet4, xList, , True

    ' Update counters for each bank in unique tender list
    For Each xItem In xData

        With xItem

            ' Get unique tender key
            sKey = .GetKey

            ' Update counters if bank value match to bellow values
            Select Case .Banka

                Case "B1": xList.Item(sKey).B1 = xList.Item(sKey).B1 + 1

                Case "B2": xList.Item(sKey).B2 = xList.Item(sKey).B2 + 1

                Case "B3": xList.Item(sKey).B3 = xList.Item(sKey).B3 + 1

            End Select

        End With

    Next

    ' Dump list to immediate window for review - this can be used to dump data in to (new) worksheet
    For Each xItem In xList

        With xItem

            Debug.Print .Broj, .Tender, .B1, .B2, .B3, .GrandTotal

        End With

    Next

    ' Free memory resource
    Set xItem = Nothing
    Set xData = Nothing
    Set xList = Nothing

End Sub

Dole u nastavku uzorak koji se dobija u immediate prozoru u VBA

Code:

Total items in list: 49
Item with same key already exists.        Garancija 1 T-12546
Total items in list: 48
Garancija 1   T-12546        1             0             1             2
Garancija 2   T-12547        1             0             0             1
Garancija 3   T-12548        1             0             0             1
Garancija 4   T-12549        1             0             0             1
Garancija 5   T-12550        1             0             0             1
Garancija 6   T-12551        1             0             0             1
Garancija 7   T-12552        1             0             0             1
Garancija 8   T-12553        1             0             0             1
Garancija 9   T-12554        1             0             0             1
Garancija 10  T-12555        1             0             0             1
Garancija 11  T-12556        1             0             0             1
Garancija 12  T-12557        1             0             0             1
Garancija 13  T-12558        1             0             0             1
Garancija 14  T-12559        1             0             0             1
Garancija 15  T-12560        1             0             0             1
Garancija 16  T-12561        1             0             0             1
Garancija 17  T-12562        1             0             0             1
Garancija 18  T-12563        1             0             0             1
Garancija 19  T-12564        1             0             0             1
Garancija 20  T-12565        1             0             0             1
Garancija 21  T-12566        1             0             0             1
Garancija 1   T-52154        0             1             0             1
Garancija 2   T-52155        0             1             0             1
Garancija 3   T-52156        0             1             0             1
Garancija 4   T-52157        0             1             0             1
Garancija 5   T-52158        0             1             0             1
Garancija 6   T-12554        0             1             0             1
Garancija 7   T-52160        0             1             0             1
Garancija 8   T-52161        0             1             0             1
Garancija 9   T-52162        0             1             0             1
Garancija 10  T-52163        0             1             0             1
Garancija 11  T-52164        0             1             0             1
Garancija 12  T-52165        0             1             0             1
Garancija 2   T-51245        0             0             1             1
Garancija 3   T-51246        0             0             1             1
Garancija 4   T-51247        0             0             1             1
Garancija 5   T-51248        0             0             1             1
Garancija 6   T-51249        0             0             1             1
Garancija 7   T-51250        0             0             1             1
Garancija 8   T-51251        0             0             1             1
Garancija 9   T-51252        0             0             1             1
Garancija 10  T-51253        0             0             1             1
Garancija 11  T-51254        0             0             1             1
Garancija 12  T-51255        0             0             1             1
Garancija 13  T-51256        0             0             1             1
Garancija 14  T-51257        0             0             1             1
Garancija 15  T-51258        0             0             1             1
Garancija 16  T-51259        0             0             1             1

Re: VBA uparivanje članova beskonačnog broja nizova 22.03.2019. u 15:14 · pre 92 meseca #9

Hvala za sorce, nisam još pogledao kako radi, ali sam ga ubacio u moj tesni primer sa prvog posta i odrađuje posao tako da ću verovatno njega modifikovati za finalno rešenje.

Forum kao aplikacija na telefonu

Srodne teme