Tabelat e hartës në excel. Si të krahasoni dy kolona në Excel për ndeshjet

23.07.2023 TV (Smart TV)

Artikulli jep përgjigje për pyetjet e mëposhtme:

Si të krahasoni dy tabela në Excel?
Si të krahasoni tabelat komplekse në Excel?
Si të krahasoni tabelat në Excel duke përdorur funksionin VLOOKUP()?
Si të gjeneroni identifikues unik të rreshtit nëse veçantia e tyre përcaktohet fillimisht nga një grup vlerash në disa kolona?
Si të rregulloni vlerat e qelizave në formula kur kopjoni formula?

Kur punon me sasi të mëdha informacioni, përdoruesi mund të përballet me një detyrë të tillë si krahasimi i dy burimeve tabelare të të dhënave. Kur ruani të dhënat në një sistem të unifikuar të kontabilitetit (për shembull, sistemet e bazuara në 1C Enterprise, sistemet që përdorin bazat e të dhënave SQL), aftësitë e integruara në sistem ose DBMS mund të përdoren për të krahasuar të dhënat. Si rregull, për ta bërë këtë, mjafton të përfshini një programues i cili do të shkruajë një pyetje në bazën e të dhënave, ose një mekanizëm raportimi të softuerit. Një përdorues me përvojë që ka aftësinë për të shkruar pyetje 1C ose SQL mund të trajtojë kërkesën.

Problemet fillojnë kur ka një nevojë urgjente për të përfunduar një detyrë të krahasimit të të dhënave dhe punësimi i një programuesi dhe shkrimi i një pyetjeje ose raporti programi mund të tejkalojë afatet e përcaktuara për zgjidhjen e detyrës. Një problem tjetër po aq i zakonshëm është nevoja për të krahasuar informacionin nga burime të ndryshme. Në këtë rast, deklarimi i problemit për programuesin do të tingëllojë si integrimi i dy sistemeve. Zgjidhja e një problemi të tillë do të kërkojë një programues më të kualifikuar dhe gjithashtu do të marrë më shumë kohë sesa zhvillimi në një sistem të vetëm.

Për të zgjidhur këto probleme, teknika ideale është përdorimi i redaktuesit të spreadsheet-it të Microsoft Excel për të krahasuar të dhënat. Sistemet më të zakonshme të menaxhimit dhe kontabilitetit rregullator mbështesin ngarkimin në formatin Excel. Kjo detyrë do të kërkojë vetëm kualifikime të caktuara përdoruesi për të punuar me këtë paketë zyre dhe nuk do të kërkojë aftësi programimi.

Le të shohim zgjidhjen e problemit të krahasimit të tabelave në Excel duke përdorur një shembull. Ne kemi dy tabela që përmbajnë listat e apartamenteve. Ngarkoni burimet - 1C Enterprise (kontabiliteti i ndërtimit) dhe një tabelë Excel (kontabiliteti i shitjeve). Tabelat vendosen në librin e punës Excel, përkatësisht në fletën e parë dhe të dytë.

Detyra jonë është t'i krahasojmë këto lista sipas adresës. Tabela e parë përmban të gjitha apartamentet në pallat. Tabela e dytë përmban vetëm apartamentet e shitura dhe emrin e blerësit. Qëllimi përfundimtar është që në tabelën e parë për çdo apartament (për ato apartamente që janë shitur) të afishohet emri i blerësit. Detyra është e ndërlikuar nga fakti se adresa e banesës në secilën tabelë është një adresë ndërtese dhe përbëhet nga disa fusha: 1) adresa e ndërtesës (shtëpisë), 2) seksioni (hyrja), 3) kati, 4) numri në dysheme (për shembull, nga 1 në 4) .

Për të krahasuar dy tabela Excel, duhet të sigurohemi që në të dyja tabelat çdo rresht të identifikohet nga një fushë, jo nga katër. Ju mund të merrni një fushë të tillë duke kombinuar vlerat e katër fushave të adresës me funksionin Concatenate(). Qëllimi i funksionit Concatenate() është të kombinojë vlera të shumta teksti në një varg. Vlerat në funksion renditen të ndara me simbolin ";". Vlerat mund të jenë ose adresa qelizash ose tekst arbitrar të specifikuar në thonjëza.

Hapi 1. Le të fusim një kolonë boshe "A" në fillim të tabelës së parë dhe të shkruajmë formulën në qelizën e kësaj kolone përballë rreshtit të parë me të dhëna:
=KONCATENATE(B3;"-";C3;"-";D3;"-";E3)
Për lehtësinë e perceptimit vizual, ne kemi vendosur simbolet "-" midis vlerave të qelizave që bashkohen.

Hapi 2. Le të kopjojmë formulën në qelizat e mëposhtme në kolonën A.

Hapi 4. Për të krahasuar tabelat e Excel sipas vlerave, përdorni funksionin VLOOKUP(). Qëllimi i funksionit VLOOKUP() është të kërkojë një vlerë në kolonën më të majtë të tabelës dhe të kthejë vlerën e qelizës që është në kolonën e specifikuar të të njëjtit rresht. Parametri i parë është vlera e dëshiruar. Parametri i dytë është tabela në të cilën do të kërkohet vlera. Parametri i tretë është numri i kolonës nga qeliza e së cilës në rreshtin e gjetur do të kthehet vlera. Parametri i katërt është lloji i kërkimit: false - përputhje e saktë, e vërtetë - përputhje e përafërt. Meqenëse informacioni i daljes duhet të vendoset në tabelën e parë (ishte në të që na duhej të shtonim emrat e klientëve), ne do të shkruajmë formulën në të. Le të krijojmë një formulë në kolonën e lirë në të djathtë të tabelës përballë rreshtit të parë të të dhënave:
=VLOOKUP(A3,Fletë2!$A$3:$F$10,6,FALSE)
Kur kopjoni formulat, Smart Excel ndryshon automatikisht adresimin e qelizës. Në rastin tonë, vlera e kërkuar për çdo rresht do të ndryshojë: A3, A4, etj., por adresa e tabelës në të cilën po kryhet kërkimi duhet të mbetet e pandryshuar. Për ta bërë këtë, ne rregullojmë qelizat në parametrin e adresës së tabelës me simbolet "$". Në vend të "Sheet2!A3:F10" ne bëjmë "Sheet2!$A$3:$F$10".

Le të ketë dy tabela vlerash që kanë të njëjtën përbërje dhe lloje kolonash. Kërkohet krahasimi i këtyre tabelave për të përcaktuar dallimet, në dispozicion mes tyre.

Duke menduar për kushtet e problemit duke përdorur rrethanat më të zakonshme, ne do të përcaktojmë gjithashtu se:

Rendi i ndryshëm i rreshtave të njëjtë në dy tabela nuk i bën tabelat të ndryshme (në problemet ku renditja e rreshtave është e rëndësishme, gjithmonë mund të shtoni një kolonë me numrin e rreshtit për të vërejtur rirregullimin e tyre);
Nuk mund të ketë dy rreshta identikë në një tabelë (dhe nëse ekziston një gjë e tillë, gjithmonë mund të kryeni një konvolucion në të gjitha kolonat, duke numëruar rreshta identikë në kolonën e shtuar - kjo do të thjeshtojë interpretimin e rezultateve të krahasimit).
Tabelat krahasohen duke krahasuar drejtpërdrejt vlerat e elementeve ose referencave të tyre. Nëse elementët e tabelës përmbajnë koleksione, atëherë vetëm referencat për koleksionet krahasohen pa u përpjekur të përcaktohet nëse përmbajtja e tyre është e barabartë.

Përsosja e dytë automatikisht çon në faktin se tabela do të ketë gjithmonë një ose më shumë kolona, vlera (kombinimi i vlerave) në të cilat do të jetë unike dhe mund të shërbejë si një identifikues rreshti. Një kolonë e tillë (bashkësi kolonash) mund të quhet çelës: i thjeshtë në rastin e një kolone ose kompleks në një rast më kompleks. Më mirë akoma, në analogji me regjistrat, quani kolonat e përmendura dimensionet e tabelës, dhe ato të mbetura - burime.

Zgjedhja e kolonave të dimensioneve lejon, kur krahasohen tabelat, të përcaktohet jo vetëm fakti i fshirjes ose shtimit të një rreshti, por edhe fakti i ndryshimit të një rreshti nëse burimet kanë ndryshuar në të njëjtin grup dimensionesh.

Për shembull, kur krahasohen tabelat e vlerave të marra nga bilanci i llogarisë së lëndëve të para dhe furnizimeve, dimensionet do të jenë kolonat që përmbajnë artikullin dhe depon, dhe burimet do të jenë bilancet dhe qarkullimi i llogarisë. Dhe kur krahasohen pjesët tabelare "Produktet", dimensionet do të jenë nomenklatura, karakteristikat dhe seritë, dhe burimet do të jenë të gjitha detajet e tjera të kësaj pjese tabelare. Dhe pastaj, duke krahasuar versionet e pjesëve tabelare, do të jetë e mundur të thuhet se nomenklatura e tillë dhe ajo u hoq ose u shtua, dhe kjo e ajo u ndryshua.

Gjatë vendosjes së problemit, ne përcaktojmë edhe formën për paraqitjen e rezultateve të krahasimit. Kjo është zgjidhja më e pambrojtur ndaj kritikave. Sepse rezultati i konkurrencës së metodave varet nga kjo. Një formë mund të jetë e përshtatshme për një metodë, e dyta për një tjetër, e treta për një të tretë, por praktika, për shkak të shumëllojshmërisë së detyrave dhe situatave, nuk e ndihmon përgjigjen.

Pas shumë hezitimesh, u mor vendimi i mëposhtëm: rezultati i krahasimit të dy tabelave Tabela 0 dhe Tabela 1 duhet të jetë një tabelë “Diferenca” me të njëjtën strukturë me tabelat që krahasohen. "Dallimi" duhet të përmbajë rreshta të ndryshëm të dy tabelave (të fshira, të shtuara, të ndryshuara). Në këtë rast, në kolonën shtesë "Shenja" duhet të ketë një shenjë: 0 - nëse rreshti është në Tabelën 0 dhe 1 - nëse rreshti është në Tabelën 1. Kjo mund të interpretohet si 0 - rresht i fshirë, 1 - i shtuar, ose 0 - rresht para ndryshimit, 1 - pas. Përveç kësaj (vëmendje!), rreshtat me të njëjtat vlera matëse duhet të vendosen njëra poshtë tjetrës, gjë që ofron një mënyrë të përshtatshme për kontrollin vizual për të "lidhur" rreshtat para dhe pas ndryshimit.

Për shembull, nëse krahasoni tabelën "Klasa e 7-të" me tabelën "Klasa e 8-të" duke përdorur metodën e propozuar, duhet të merrni një tabelë "Diferenca".

klasa e 7-të		klasa e 8-të		Diferenca
Artikulli	Gradë	Artikulli	Gradë	Artikulli	Gradë	Shenjë
Duke kënduar	5	Letërsia	5	Duke kënduar	5	0
Letërsia	5	Algjebër	4	Algjebër	5	0
Algjebër	5	Fizika	5	Algjebër	4	1
Fizika	5	Kimia	4	Kimia	4	1

Epo, një gjë e fundit. Jo aq shpesh, por ka ende raste kur krahasohen tabelat e renditura tashmë sipas fushave kryesore. Le t'ia shtojmë problemit këtë kusht në mënyrë që të zgjerojmë grupin e algoritmeve të testuara me një metodë që është përshtatur posaçërisht për këtë rast.

2. Kriteret e vlerësimit dhe metodat e testimit

Kriteri kryesor i vlerësimit është natyrisht zgjedhja e kohës së krahasimit. Një kriter shtesë mund të jetë thjeshtësia e funksionit të krahasimit. Koha e ekzekutimit të krahasimit mund të matet duke përdorur përpunimin e krijuar posaçërisht për këtë qëllim. Thjeshtësia e funksioneve propozohet të vlerësohet subjektivisht.

Përpunimi i krijuar për testim gjeneron një tabelë vlerash me një numër të caktuar rreshtash dhe kolonash dhe një numër të caktuar dimensionesh. Lloji i të dhënave të elementeve zgjidhet nga një listë e kufizuar e llojeve primitive: vargu, numri dhe data, dhe gjatësia e vlerës gjithashtu mund të specifikohet. Vlerat e elementeve të tabelës krijohen në mënyrë të rastësishme. Me ndryshimin e tabelës së parë, formohet e dyta. Numri i ndryshimeve përcaktohet si përqindje e numrit të rreshtave në tabelën e parë nga tre tregues të ndryshëm: përqindja e fshirjeve, ndryshimeve dhe shtesave. Numri i përsëritjeve është specifikuar gjithashtu për të përcaktuar kohën mesatare të funksionimit të metodës. Të gjitha metodat e testuara ekzekutohen njëra pas tjetrës në të njëjtat tabela testimi. Përpunimi i përdorur në testim i bashkëngjitet këtij publikimi në mënyrë që rezultatet të mund të rishikohen në pajisje të tjera dhe në një mjedis tjetër softuerësh.

3. Përshkrim i shkurtër i metodave të krahasuara

Gjithsej shtatë metoda të ndryshme u zgjodhën për testim të detajuar:

3.1. Palos dhe rendit

Thelbi i metodës është kombinimi i tabelave duke shtuar një rresht nga tabela e parë në të dytën në një lak. Pastaj shtohet një kolonë shtesë "Llogaria" për numërimin e mëvonshëm të rreshtave identike. Llogaritja bëhet me konvolucion mbi të gjitha kolonat. Kjo përcakton rreshtat e njëjtë dhe të ndryshëm në tabelën e parë dhe të dytë. Ato rreshta që shfaqen një nga një në tabelën e kombinuar rishkruhen në një tabelë dallimesh, e cila më pas renditet sipas dimensionit në mënyrë që rreshtat para dhe pas ndryshimeve të jenë krah për krah. Këtu është kodi për këtë funksion

Funksioni DifferenceTableValues(Tabela0, Tabela1, Dimensionet) Eksporto të gjitha kolonat = ""; Për çdo kolonë nga Tabela0.Cikli i kolonave Të gjitha kolonat = Të gjitha kolonat + ", " + Emri i kolonës. Cikli i fundit; Të gjitha kolonat = Mesatare (Të gjitha kolonat, 2); Tabela = Tabela1.Copy(); Tabela.Columns.Add("Sign", New TypeDescription("Number")); Tabela.FillValues(1, "Sign"); Për çdo rresht nga Tabela0 Loop FillPropertyValues(Table.Add(), Row) EndLoop; Tabela.Columns.Add("Llogaria"); Tabela.FillValues(1, "Llogaria"); Tabela.Collapse(Të gjitha kolonat, "Sign, Account"); Përgjigje = Tabela.Kopje(Struktura e re("Llogaria", 1), Të gjitha Kolonat + ", Shenja"); Përgjigje.Sort(Dimensionet); Funksioni i fundit i përgjigjes së kthimit

3.2 Mashtrimi, palosja dhe renditja

Ky funksion është një modifikim i lehtë i funksionit të mëparshëm për faktin se shtimi i tabelës së parë në të dytën nuk ndodh në rreshta, por në kolona. Kjo, në një gamë të caktuar kushtesh, përshpejton funksionimin e bashkimit të tavolinës

Funksioni DifferenceTableValues(Tabela0, Tabela1, Dimensionet) Eksporto të gjitha kolonat = ""; Për çdo kolonë nga Tabela0.Cikli i kolonave Të gjitha kolonat = Të gjitha kolonat + ", " + Emri i kolonës. Cikli i fundit; Të gjitha kolonat = Mesatare (Të gjitha kolonat, 2); Tabela = Tabela1.Copy(); Tabela.Columns.Add("Sign", New TypeDescription("Number")); Tabela.FillValues(1, "Sign"); Për е = 1 Sipas tabelës0.Sasia() Tabela e ciklit.Insert(0) Cikli i fundit; Për e = 0 Sipas Tabelës0.Kollonat.Sasia() - 1 Tabela e Ciklit.Kollona e ngarkimit(Tabela0.Shkarkimi i kolonës(е), e) Cikli i fundit; Tabela.Columns.Add("Llogaria"); Tabela.FillValues(1, "Llogaria"); Tabela.Collapse(Të gjitha kolonat, "Sign, Account"); Përgjigje = Tabela.Kopje(Struktura e re("Llogaria", 1), Të gjitha Kolonat + ", Shenja"); Përgjigje.Sort(Dimensionet); Funksioni i fundit i përgjigjes së kthimit

3.3. Bashkohu me indeks

Kjo veçori është ndërtuar mbi një ide të thjeshtë dhe të qartë. Cikli përsëritet nëpër rreshtat e tabelës së parë. Për çdo rresht, tentohet të gjendet rreshti në tabelën e dytë që i përgjigjet sipas vlerës së dimensionit, duke përdorur metodën FindRows. Burimet e rreshtave të gjetur më pas krahasohen për mospërputhje, rreshti i gjetur në tabelën e dytë shënohet me zero, në mënyrë që rreshtat e pashënuar "një" të zgjidhen si të munguara në tabelën e parë. Për ta bërë metodën FindRows të funksionojë shpejt, krijohet një indeks për tabelën e dytë për të gjithë grupin e dimensioneve.

Funksioni DifferenceTableValues (Tabela0, Tabela1, Dimensionet) Zgjedhja e eksportit = Struktura e re (Dimensionet); Burimet = Array i ri; Për ColumnIndex = 0 By Tabela0.Columns.Sasia() - 1 Cikli Nëse NUK Zgjidh.Property(Table0.Columns[ColumnIndex].Name) Pastaj Resources.Add(ColumnIndex) FundIf EndCycle; Tabela1.Columns.Add("Sign", New TypeDescription("Number")); Tabela1.Plotëso Vlerat(1, "Shenja"); NewIndex = Tabela1.Indekset.Add(Dimensions); Diferenca = Tabela1.CopyColumns(); Për çdo rresht0 Nga Tabela0 Loop FillPropertyValues (Zgjedhja, Rreshti0); Rreshtat1 = Tabela1.GjeniRreshtat(Zgjidh); Nëse Rreshtat1.Sasia() = 0 Pastaj FillPropertyValues(Difference.Add(), Rreshti0) Tjetër Rreshti1 = Rreshtat1; Për çdo burim nga burimet Loop If Row0[Burimi]<>Row1[Resource] Pastaj FillPropertyValues(Difference.Add(), Row0); FillPropertyValues(Difference.Add(), Line1); Ndërprit EndIfLoopEnd; Linja 1.Shenja = 0 Cikli FundIfEnd; Për çdo rresht1 Nga Tabela1.FindRows(Strukturë e re("Sign", 1)) Loop FillPropertyValues(Difference.Add(), Row1); Cikli i Fundit; Tabela1.Columns.Delete("Sign"); Tabela1.Indekset.Fshije(NewIndex); Funksionet e Fundit të Diferencës së Rimbursimit

3.4. Lidhja e pajtueshmërisë

Ky funksion është algoritmikisht i njëjtë me atë të mëparshëm, me përjashtim të faktit se në vend të një indeksi të rregullt, përdoret një indeks i bazuar në ndeshje "i bërë në shtëpi". Për ta bërë këtë, së pari kalohet tabela e dytë, si rezultat i së cilës referencat në rreshtat e saj mbahen mend në pemën e kërkimit të ndërtuar në bazë të korrespondencës

Funksioni DifferenceTablesValues_(Tabela0, Tabela1, Rreshti i Dimensionit) Eksporto Tabela1.Columns.Add("Sign", New TypeDescription("Number")); Tabela1.Plotëso Vlerat(1, "Shenja"); DimensionsStructure = Struktura e re (DimensionsString); Dimensionet = Array i ri; Burimet = Array i ri; Për Indeksin = 0 Nga Tabela0.Kollonat.Sasia() - 1 cikli Emri i kolonës = Tabela0.Kollonat[Indeksi].Emri; Nëse DimensionsStructure.Property(ColumnName) Pastaj Dimensions.Add(Index) Else Resources.Add(Index) EndIf FundCycle; DimensionPlus = Dimensionet[Dimensionet.Sasia() - 1]; Dimensionet.Fshije(Dimensionet.Sasia() - 1); HashMap = Ndeshje e re; Për çdo rresht1 Nga Tabela1 Loop Root = HashMap; Për çdo dimension nga Dimension LoopPart kryesore = Rreshti1[Dimensioni]; Dega = Root[Pjesa kryesore]; Nëse Dega = E Papërcaktuar Atëherë Dega = Përputhje e Re; Root[KeyPart] = BranchEndIf; Rrënja = Cikli Fundor i Degës; Pjesa kryesore = Rreshti 1[DimensionPlus]; Root[PartKey] = Rreshti1EndCycle; Dimensionet.Add(DimensionPlus); Diferenca = Tabela1.CopyColumns(); Për çdo rresht0 Nga Tabela0 Loop Root = HashMap; Për çdo dimension nga Dimension LoopPart kryesore = Rreshti0[Dimensioni]; Dega = Root[Pjesa kryesore]; Nëse Branch = E Padefinuar Atëherë FillPropertyValues(Difference.Add(), Rreshti0); Aborti FundIf; Rrënja = Cikli Fundor i Degës; Nëse Dega<>I papërcaktuar atëherë për çdo burim nga burimet Loop If Row0[Burimi]<>Branch[Resource] Pastaj FillPropertyValues(Difference.Add(), Row0); FillPropertyValues(Difference.Add(), Branch); Ndërprit EndIfLoopEnd; Dega.Shenja = 0 Cikli FundIfEnd; Për çdo rresht1 Nga Tabela1.FindRows(Strukturë e re("Sign", 1)) Loop FillPropertyValues(Difference.Add(), Row1); Cikli i Fundit; Tabela1.Columns.Delete("Sign"); Funksionet e Fundit të Diferencës së Rimbursimit

3.5. Bashkimi

Ky funksion supozon se tabelat që krahasohen janë të renditura sipas dimensioneve kryesore. Gjatë funksionimit të tij, rreshtat e dy tabelave lexohen me radhë, krahasuar me njëra-tjetrën në mënyrë që dalja që rezulton të jetë një tabelë e bashkuar, e renditur pa rreshta identike.

Funksioni DifferenceTablesValues_(Tabela0, Tabela1, Rreshti i Dimensionit) Eksporto Tabela1.Columns.Add("Sign", New TypeDescription("Number")); Tabela1.Plotëso Vlerat(1, "Shenja"); Diferenca = Tabela1.CopyColumns(); DimensionsStructure = Struktura e re (DimensionsString); Dimensionet = Array i ri; Burimet = Array i ri; Për Indeksin = 0 Nga Tabela0.Kollonat.Sasia() - 1 cikli Emri i kolonës = Tabela0.Kollonat[Indeksi].Emri; Nëse DimensionsStructure.Property(ColumnName) Pastaj Dimensions.Add(Index) Else Resources.Add(Index) EndIf FundCycle; Krahaso = Vlerat e reja krahasuese; Indeksi1 = Tabela0.Sasia() - 1; Indeksi2 = Tabela1.Sasia() - 1; Rreshti 1 = Tabela0[Indeksi1]; Rreshti 2 = Tabela1[Indeksi2]; Ndërsa cikli i vërtetë për çdo dimension nga cikli i dimensionit Rezultati i krahasimit = Krahaso. Krahaso(Rreshti1[Dimensioni], Rreshti 2[Dimensioni]); Nëse Rezultati i Krahasimit<>0 Pastaj anuloni fundin nëse fundi i ciklit; Nëse Rezultati i Krahasimit = 0 Atëherë për Çdo Burim Nga Resources Loop If Row1[Burimi]<>Line2[Resource] Pastaj FillPropertyValues(Difference.Add(), Line1); FillPropertyValues(Difference.Add(), Line2); Anuloni EndIfLoopEnd; Indeksi1 = Indeksi1 - 1; Indeksi2 = Indeksi2 - 1; Nëse Min (Indeksi 1, Indeksi 2)< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1]; Строка2 = Таблица1[Индекс2]; ИначеЕсли РезультатСравнения >0 Pastaj FillPropertyValues(Difference.Add(), Line1); Indeksi1 = Indeksi1 - 1; Nëse Indeksi 1< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1] Иначе ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Индекс2 = Индекс2 - 1; Если Индекс2 < 0 Тогда Прервать КонецЕсли; Строка2 = Таблица1[Индекс2] КонецЕсли КонецЦикла; Пока Индекс1 >= 0 Rreshti 1 i ciklit = Tabela0[Indeksi1]; FillPropertyValues(Difference.Add(), Line1); Indeksi1 = Indeksi1 - 1 Cikli Fundor; Ndërsa Index2 >= 0 Loop Row2 = Tabela1[Index2]; FillPropertyValues(Difference.Add(), Line2); Indeksi2 = Indeksi2 - 1 Cikli Fundor; Tabela1.Columns.Delete("Sign"); Funksionet e Fundit të Diferencës së Rimbursimit

3.6. Kërkesë - lidhje e plotë

Funksioni bazohet në kalimin e dy tabelave në një pyetje, ku ato bashkohen me vlera të barabarta në dimensione. Një ndërlikim i lehtë shoqërohet me "shpalosjen" e mëvonshme në dy rreshta rreshtash,të ndryshme në burime.

Funksioni LinePart(String, Separator) ExportSeparatorPosition = Find(String, Separator); Nëse SeparatorPosition = 0 Pastaj Response = Array i ri; Përgjigje.Add(String); Përndryshe Përgjigja = StrPart(Avg(Rresht, Pozicioni Ndarës + StrLength(Ndarës)), Separator); Përgjigje.Insert(0, Mesatar(Rresht, 1, Pozicioni ndarës - 1)) FundIf; Ktheje përgjigjen e funksionit të funksionit fundor DifferenceTableValues(Tabela0, Tabela1, Dimensionet) Kërkesa e eksportit = Pyetje e re("SELECT | 0 AS Sign(), T.Field() | PLACE T0 | FROM | &Table0 AS T |; | |///// ///////////////////////////////////////////////////////////////// ///////////////////// /////////////////////////////////////////////////////////////////////// //////////////////////// ///////////////////////////////////////////////// ///////////////////// ) | Shenjat.Shenja |NGA | T0 AS T0 | LIDHJA E PLOTË E T1 AS T1 | NGA (E VËRTETË) | () DHE T0.Fusha = T1.Fusha(), | Shenjat SI Shenja |KU | (() T0.Fusha ËSHTË NULL DHE Shenjat.Shenja = 1 | OSE T1.Fusha ËSHTË NULL DHE Shenjat.Shenja = 0 | () OSE T0.Fusha<>T1.Fusha()) | |PËRNDARJE NGA | ()Fusha"); Struktura e Dimensionit = Strukturë e re(Dimensionet); Seksione = StrParts(Query.Text, "()"); Pyetje.Tekst = Seksione; Për secilën kolonë nga Tabela1. Pyetja e Ciklit të Kolonave. Teksti = Pyetja. Teksti + StrReplace(Seksionet, "Fusha", Emri i Kolonës) Cikli i Fundit; Pyetës.Text = Pyetje.Tekst + Seksione; Për secilën kolonë nga Tabela1. Pyetje e Ciklit të Kolonave.Tekst = Pyetje.Tekst + StrReplace(Seksione, "Fusha", Kolona. Emri) Cikli i fundit; Pyetës.Tekst = Pyetje.Tekst + Seksione; Për secilën kolonë nga Tabela1.Kërkesa e Ciklit të Kolonave.Tekst = Pyetje.Tekst + StrReplace(Seksionet, "Fusha", Emri i Kolonës) Cikli i Fundit; Pyetës.Text = Pyetje. Tekst + Seksione; Për secilin Element Nga Struktura e Dimensioneve Kërkesë Cikli.Tekst = Kërkesë.Tekst + StrReplace(Seksione, "Fusha", Element.Key) Cikli i fundit; Kërkesë.Tekst = Kërkesë.Tekst + Seksione; Kërkesë.Tekst = Kërkesë .Text + StrReplace (Seksionet, "Fusha", Tabela1.Kollonat.Emri); Për secilën kolonë nga Tabela1. Kolonat Loop Nëse JO DimensionStructure.Property(Column.Name) Pastaj Query.Text = Query.Text + StrReplace(Seksionet, " Fusha", Emri i Kolonës) FundIf FundCycle; Query.Text = Query.Text + Seksionet; Query.Text = Query.Text + StrReplace(Seksionet, "Fusha", Dimensionet); Query.SetParameter("Tabela0", Tabela0); Query.SetParameter("Tabela1", Tabela1); Kërkesa e kthimit.Execute().Unload() FundFunction

3.7. Kërkesë - grupim

Ky funksion është ndërtuar mbi të njëjtën ide si funksioni 3.1, i zbatuar vetëm brenda kërkesës

Të gjitha funksionet e paraqitura këtu janë akorduar me kujdes për të arritur performancën maksimale. Duke marrë parasysh përvojën e fituar gjatë akordimit të përbashkët në forumin e funksionit për rastin njëdimensional. Megjithatë, kjo nuk u bë me aq kujdes sa atje, kështu që ndoshta pak më shumë performancë mund të shtrydhej nga disa funksione.

4. Rezultatet e testit

4.1 Efekti i numrit të rreshtave

Le të studiojmë varësinë e kohës së krahasimit nga numri i rreshtave në tabela. Për ta bërë këtë, ne përdorim vlerat e mëposhtme të parametrave të testimit. Numri i rreshtave - 20000, 40000, 60000, 80000, 100000, numri i kolonave - 10, numri i kolonave kryesore - 1, lloji i të dhënave - vargu, gjatësia e vargut - 10, përqindja e fshirjeve, ndryshimeve, shtesave - 5, numri i testit përsëritjet - 2. Marrim varësinë e mëposhtme, e cila është më e përshtatshme për t'u paraqitur në formë grafiku.

Kjo marrëdhënie është pothuajse lineare për shumicën e metodave! Kështu duhet të jetë. Koha e ekzekutimit të metodës FindRows në prani të një indeksi nuk varet nga numri i rreshtave, kështu që një bashkim sipas indeksit kryhet në kohë lineare. E njëjta gjë është e vërtetë kur përdorni ndeshjen dhe bashkimin. Me një bashkim të plotë, pyetja ka shumë të ngjarë të përdor një përputhje hash për të bashkuar tabela me madhësi të barabartë.

Jo-lineariteti i kohës së renditjes për një numër relativisht të vogël rreshtash të ndryshëm devijon pak nga marrëdhënia e drejtpërdrejtë për konvolucionin. Metoda që përdor bashkimin duke kopjuar kolonat është më e keqe - është kjo metodë e kopjimit që paraqet jolinearitet të rëndësishëm përveç jolinearitetit të lehtë të renditjes. Për shkak të kësaj, përfitimi i përdorimit të "mashtrimit" të bashkimit të tabelave me më shumë se 60,000 rreshta humbet.

4.2 Efekti i gjatësisë së vlerës

Tani le të shqyrtojmë varësinë e kohës nga gjatësia e vlerave të vargut. Le të vendosim numrin e rreshtave të barabartë me 50000. Parametrat e mbetur janë të njëjtë si në 4.1. Ne e paraqesim rezultatin në formën e një grafiku me shtylla. Ai tregon më mirë raportin e kohës së funksionimit të metodave të ndryshme dhe ju lejon të identifikoni liderin, i cili në shumicën e rasteve është metoda e konvolucionit.

Mund të shihet se varësia e kohës nga metoda praktikisht nuk ndryshon kur ndryshon gjatësia e vargut. Vetëm koha e ekzekutimit të pyetjes rritet.

Për ta bërë këtë diagram më përshkrues të metodave të pyetjes, ai ndan kohën që duhet për të futur tabelat në një pyetje në dimensione të veçanta. Për këtë qëllim, është krijuar një funksion dummy që kryen vetëm futjen e tabelave në pyetje dhe nuk kryen asnjë punë tjetër. Koha e gjatë që duhet për të futur tabela tregon se është shumë e vështirë për teknologjinë e pyetjeve të konkurrojë me metodat kryesore. Në shumë raste, drejtuesit e kanë përfunduar tashmë punën e tyre në kohën kur të dhënat origjinale janë vetëm në kërkesë.

4.3 Ndikimi i llojeve të të dhënave

Pyetja tjetër interesante është marrëdhënia midis metodave dhe llojeve të të dhënave. Diagrami i mëposhtëm e tregon atë. Këtu numri i rreshtave është gjithashtu 50000, gjatësia e vargut dhe vlerat numerike është 10. Pjesa tjetër është si në 4.1.

Ajo tregon se lloji i të dhënave ka ndikimin më të madh në kohën e metodave të pyetjes. Për numrat, grupimi funksionon më mirë. Dhe hurmat përpunohen shumë mirë.

4.3 Efekti i numrit të kolonave

Një tjetër varësi është varësia e kohës së krahasimit nga numri i kolonave. Diagrami i mëposhtëm e tregon atë. Numri i rreshtave këtu është 50,000, lloji i të dhënave është një varg me gjatësi 10, përqindja e shtesave, shtrembërimeve dhe fshirjeve është 5. Një kolonë kyçe.

Mund të shihet se numri i kolonave nuk e ndryshon shumë shpejtësinë krahasuese të metodave. Ngadalësimi më i madh në performancën e pyetjeve është rritja e numrit të kolonave.

4.4 Efekti i numrit të matjeve

Më interesante është varësia nga numri i kolonave kryesore të paraqitura më poshtë. Numri i rreshtave këtu është 50,000, lloji i të dhënave është një varg me gjatësi 10, përqindja e shtesave, shtrembërimeve dhe fshirjeve është 5. Janë gjithsej 10 kolona.

Mund të shihet se metoda e bazuar në korrespondencë, e cila më parë tregonte rezultate të mira, tani është një e huaj. Fusioni gjithashtu përkeqësohet. Por kërkimi sipas indeksit është përmirësuar - për faktin se ka më pak kolona për t'u krahasuar.

4.5 Ndikimi i dallimeve në madhësinë e tabelave

Tani le t'i kushtojmë vëmendje asimetrisë së metodave 1 - 4 (përbërja dhe bashkimi) në lidhje me madhësitë e tabelave të krahasuara. Të gjitha këto metoda përfitojnë nga zvogëlimi i tabelës së parë! Kjo konfirmohet nga tabela e mëposhtme, e cila tregon kohën që duhet për të krahasuar dy tabela me 50,000 dhe 40,000 rreshta në renditje të ndryshme.

Ekziston një objekt interesant i dukshëm në diagramin e mësipërm. Duke pasur parasysh numrin e rreshtave dhe kolonave, rezulton të jetë më fitimprurëse të shtoni 50 mijë rreshta në një lak në një tabelë me 40 mijë rreshta sesa anasjelltas. Kjo mund të jetë për shkak të veçorive të alokimit të memories për tabelën e vlerave.

4.6 Efekti i numrit të diferencave

Dhe së fundi, le të shqyrtojmë varësinë e kohës së krahasimit nga shkalla e ndryshimit midis tabelave. Mund të shihet se me rritjen e përqindjes së mospërputhjeve, koha e funksionimit të konvolucionit ngadalësohet. Meqenëse jolineariteti i renditjes fillon të luajë një rol.

4.7 Ndikimi i mjedisit të harduerit dhe softuerit

Testet u kryen në platformën 8.3.5.1248 në një laptop VGN-Z51MRG. Varësitë e marra përgjithësisht konfirmohen në pajisje të tjera, por ka edhe disa veçori që ende nuk janë përgjithësuar.

5. Përfundime

5.1. Metoda më e thjeshtë e konvolucionit rezulton të jetë më produktive në shumicën e rasteve. Duhet të përdoret si metodë universale, por jo në raste të veçanta.

5.2 Nëse madhësia është e vogël (deri në 50,000 rreshta), mund të merrni një shpejtësi shtesë për palosjen duke përdorur kopjimin e kolonave kur bashkoni tabelat (metoda 3.2).

5.3 Në rastin e veçantë të një kolone kryesore, një numër i konsiderueshëm diferencash dhe një ndryshim domethënës në madhësitë e tabelës, duhet të përdoret një bashkim përputhësh. E njëjta gjë duhet bërë edhe nëse ka disa tabela kryesore, por krahasimi bëhet me të njëjtën tabelë, për të cilën mund të përgatiteni paraprakisht. "pema e vendimit" bazuar në pajtueshmërinë,përshtatur me karakteristikat e tij.

5.4 Në rastin e veçantë të disa kolonave kryesore me një numër të konsiderueshëm dallimesh dhe tabelat që krahasohen nuk janë të renditura, duhet të përdorni metodën e bashkimit sipas indeksit.

5.5 Për efikasitetin më të madh të metodave 1-4, duhet të zgjidhni rendin e saktë të treguesve të tabelave kur krahasoni.

5.6 Në rastin e veçantë të tabelave të renditura kur ka një numër të konsiderueshëm diferencash, duhet të përdoret bashkimi.

5.7. Në rastin e veçantë të tabelave të mëdha (në varësi të pajisjeve) me madhësi afërsisht të barabartë, të cilat, për më tepër, kanë dallime të konsiderueshme dhe përbëhen nga rreshta të shkurtër dhe një numër jashtëzakonisht i vogël kolonash, është e mundur të përdoren pyetje.

5.8 Nëse tabelat dominohen nga të dhënat numerike, datat, vargjet e mesme dhe të gjata, atëherë pyetjet e krahasimit të tabelave duhet të përdorin grupimin, dhe vetëm për vargjet shumë të shkurtra - një bashkim i plotë.

6. Përfundime të përgjithshme

6.1 Në çdo rast, përpara se të bëni një zgjedhje vendimtare, është më mirë, nëse është e mundur, të krahasohen disa metoda në kushte reale të përdorimit të tyre. Për shembull, duke përdorur përpunimin e bashkangjitur artikullit.

6.2 Marrja në konsideratë e veçorive të të dhënave në tabela lejon optimizimin shtesë të synuar të shumicës së metodave të paraqitura. Ka shumë mundësi për këtë që mbeten jashtë fushës së çështjeve të shqyrtuara.

6.3 Futja e tabelave të vlerave në pyetje mund të marrë kohë të konsiderueshme, gjë që në shumicën e rasteve mohon efektivitetin e përdorimit të tyre në detyrat ku të dhënat merren nga memoria dhe jo nga baza e të dhënave. Përdorimi i pamenduar i pyetjeve në këtë detyrë është një keqkuptim i dëmshëm.

6.4 Koha e funksionimit të metodës FindRows, nëse ka një indeks në kolonat e përfshira në përzgjedhje, nuk varet nga madhësia e tabelës së vlerave. Kështu, vlerësimi i saktë për performancën e metodës së krahasimit të tabelës duke përdorur një bashkim me indeks është O(N).

, të cilët propozuan, zbatuan dhe korrigjuan metodat e tyre për rastin njëdimensional, bënë shumë ndryshime dhe konsiderata të dobishme dhe gjithashtu morën pjesë aktive në të gjitha diskutimet. Falenderime të veçanta për sponsorët e asaj teme dhe - për një pyetje interesante.

Pas instalimit të shtesës, do të keni një skedë të re me komandën e thirrjes së funksionit. Kur klikoni mbi komandën Krahasimi i diapazonit Shfaqet një kuti dialogu për futjen e parametrave.

Kjo makro ju lejon të krahasoni tabela të çdo madhësie dhe me çdo numër kolonash. Krahasimet e tabelave mund të bëhen në një, dy ose tre kolona në të njëjtën kohë.

Kutia e dialogut ndahet në dy pjesë: e majta për tabelën e parë dhe e djathta për të dytën.

Për të krahasuar tabelat, duhet të bëni sa më poshtë:

Specifikoni vargjet e tabelave.
Vendosni një kuti kontrolli (shënues/zog) nën gamën e zgjedhur të tabelave nëse tabela përfshin një kokë (vijë titulli).
Zgjidhni kolonat e tabelave majtas dhe djathtas për krahasim (nëse sferat e tabelave nuk përfshijnë titujt, kolonat do të numërohen).
Specifikoni llojin e krahasimit.
Zgjidhni një opsion për shfaqjen e rezultateve.

Lloji i krahasimit të tabelës

Programi ju lejon të zgjidhni disa lloje të krahasimeve të tabelave:

Gjeni rreshtat nga një tabelë që mungojnë në një tabelë tjetër

Kur zgjidhni këtë lloj krahasimi, programi kërkon rreshta në një tabelë që mungojnë në një tjetër. Nëse përputhni tabelat e bazuara në kolona të shumta, rezultati do të jetë rreshta që kanë një ndryshim në të paktën njërën nga kolonat.

Gjeni vargjet që përputhen

Kur zgjidhni këtë lloj krahasimi, programi gjen rreshta që përputhen në tabelën e parë dhe të dytë. Rreshtat në të cilat vlerat në kolonat e përzgjedhura të krahasimit (1, 2, 3) të një tabele përputhen plotësisht me vlerat e kolonave të tabelës së dytë konsiderohen të përputhen.

Një shembull se si funksionon programi në këtë mënyrë është paraqitur në të djathtë në figurë.

Përputhni tabelat në bazë të përzgjedhjes

Në këtë mënyrë krahasimi, përballë çdo rreshti të tabelës së parë (e zgjedhur si kryesore), kopjohen të dhënat e rreshtit që përputhet me tabelën e dytë. Nëse nuk ka rreshta që përputhen, rreshti përballë tabelës kryesore mbetet bosh.

Krahasimi i tabelave me katër ose më shumë kolona

Nëse ju mungon funksionaliteti i programit dhe duhet të krahasoni tabelat me katër ose më shumë kolona, atëherë mund të dilni nga situata si më poshtë:

Krijoni një kolonë boshe në tabelat tuaja.
Në kolonat e reja duke përdorur formulën = LIDHJE kombinoni kolonat me të cilat dëshironi të krahasoni.

Në këtë mënyrë do të përfundoni me 1 kolonë që përmban vlerat e kolonave të shumta. Epo, ju tashmë e dini se si të krahasoni një kolonë.

1. Deklarata e problemit

Le të ketë dy tabela vlerash që kanë të njëjtën përbërje dhe lloje kolonash. Kërkohet krahasimi i këtyre tabelave për të përcaktuar dallimet, në dispozicion mes tyre.

Duke menduar për kushtet e problemit duke përdorur rrethanat më të zakonshme, ne do të përcaktojmë gjithashtu se:

Rendi i ndryshëm i rreshtave të njëjtë në dy tabela nuk i bën tabelat të ndryshme (në problemet ku renditja e rreshtave është e rëndësishme, gjithmonë mund të shtoni një kolonë me numrin e rreshtit për të vërejtur rirregullimin e tyre);
Nuk mund të ketë dy rreshta identikë në një tabelë (dhe nëse ekziston një gjë e tillë, gjithmonë mund të kryeni një konvolucion në të gjitha kolonat, duke numëruar rreshta identikë në kolonën e shtuar - kjo do të thjeshtojë interpretimin e rezultateve të krahasimit).
Tabelat krahasohen duke krahasuar drejtpërdrejt vlerat e elementeve ose referencave të tyre. Nëse elementët e tabelës përmbajnë koleksione, atëherë vetëm referencat për koleksionet krahasohen pa u përpjekur të përcaktohet nëse përmbajtja e tyre është e barabartë.

Për shembull, nëse krahasoni tabelën "Klasa 7" me tabelën "Klasa 8" duke përdorur metodën e propozuar, duhet të merrni një tabelë "Diferenca".

klasa e 7-të		klasa e 8-të		Diferenca
Artikulli	Gradë	Artikulli	Gradë	Artikulli	Gradë	Shenjë
Duke kënduar	5	Letërsia	5	Duke kënduar	5	0
Letërsia	5	Algjebër	4	Algjebër	5	0
Algjebër	5	Fizika	5	Algjebër	4	1
Fizika	5	Kimia	4	Kimia	4	1

2. Kriteret e vlerësimit dhe metodat e testimit