{"id":14626,"date":"2019-03-25T07:28:00","date_gmt":"2019-03-25T05:28:00","guid":{"rendered":"http:\/\/catalinx.ro\/wordpress\/?p=14626"},"modified":"2019-03-26T14:47:06","modified_gmt":"2019-03-26T14:47:06","slug":"excelar-hardcore","status":"publish","type":"post","link":"https:\/\/catalinx.ro\/wordpress\/?p=14626","title":{"rendered":"Excelar Hardcore"},"content":{"rendered":"
<\/div>\n\n

Un tip cu skillsetul si ocupatiunile mele probabil ca nu s-ar recomanda ca un excelar. Orice tip care poate programa in ceva limbaje ar zice ca excelul e de cacat ca pentru analiza\/prelucrarea de date ar trebui sa folosim alte tooluri mai bune, macar access, MySQL, daca nu matlab\u0103 si alte figuri .<\/p>\n\n\n\n

Din experienta mea, proramatorii urasc excelul si powerpointu’. <\/p>\n\n\n\n

Eu sunt un excelar hardcore. Pentru ca daca e vorba de analiza de date , corectie and sheet <\/em> excelul e cel mai rapid, cel mai flexibil, cel mai vizual si sa imi bag pula in de ce nu exista alte tool-uri la fel de bune . Exista, fac parte din pachete de softuri pentru Business Intelligence sau Business Warehouse. Sau chestii gen Dataiku<\/a>. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Weekendul asta am inregistrat recordul de rulare pentru un excel 26 de ore. Da, mi-am dat seama ca nu rulez optim niste scripturi de calcul de distante Levenshtein pe niste dictionare care duceau la calculul a vreo 350 milioane de distante pentru 70 000 de cuvinte, chestie nu chiar necesara. Pe care am optiizat-o cand s-a terminat scriptul si mi-am dat seama ca am uitat sa fac un cacat de conversie catre upper si toate concluziile mele erau oarecum gresite, cu circa 50% . <\/p>\n\n\n\n

<\/p>\n\n\n\n

Gen – asta e distanta, copiata de pe ceva sourceforge, nu mai stiu exact. <\/p>\n\n\n\n

Iar asta-i contextul .<\/p>\n\n\n

[code language=”vb”]
\nFunction Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long<\/p>\n

Dim i As Long, j As Long
\nDim string1_length As Long
\nDim string2_length As Long
\nDim distance() As Long<\/p>\n

string1_length = Len(string1)
\nstring2_length = Len(string2)
\nReDim distance(string1_length, string2_length)<\/p>\n

For i = 0 To string1_length
\ndistance(i, 0) = i
\nNext<\/p>\n

For j = 0 To string2_length
\ndistance(0, j) = j
\nNext<\/p>\n

For i = 1 To string1_length
\nFor j = 1 To string2_length
\nIf Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
\ndistance(i, j) = distance(i – 1, j – 1)
\nElse
\ndistance(i, j) = Application.WorksheetFunction.Min _
\n(distance(i – 1, j) + 1, _
\ndistance(i, j – 1) + 1, _
\ndistance(i – 1, j – 1) + 1)
\nEnd If
\nNext
\nNext<\/p>\n

Levenshtein = distance(string1_length, string2_length)<\/p>\n

End Function
\n[\/code]<\/p>\n\n\n

[code language=”vb”]
\nFunction BestMatch(region As String) As String<\/p>\n

Dim wb As Workbook
\nDim sstart As String
\nDim send As String<\/p>\n

Set wb = ThisWorkbook<\/p>\n

‘define your sheets
\nDim ws_data As Worksheet
\nDim ws_iso As Worksheet
\nDim ws_found As Worksheet
\nDim vfound As Range
\nDim vsearch As Range
\nDim vdist As Range
\nSet ws_data = wb.Sheets(“DATA”)
\nSet ws_iso = wb.Sheets(“ISO”)
\nSet ws_found = wb.Sheets(“ISOFOUND”)
\nsstart = “”
\nDim lastRow As Long
\nBestMatch = “Not Found”
\nlastRow = ws_iso.Range(“A” & Rows.Count).End(xlUp).Row + 1
\nlastFound = ws_found.Range(“A” & Rows.Count).End(xlUp).Row + 1
\nDim isodict(1 To 32000) As String
\nDim isosearch(1 To 70000) As String
\nDim isofound(1 To 70000) As String
\nDim isofounddist(1 To 70000) As String
\nDim xfound As Boolean
\nFor i = 1 To lastRow<\/p>\n

n isodict(i) = ws_iso.Cells(i, 4)
\nNext i<\/p>\n

For j = 1 To lastFound
\nisosearch(j) = ws_found.Cells(j, 1)
\nisofound(j) = ws_found.Cells(j, 2)
\nisofounddist(j) = ws_found.Cells(j, 3)
\nNext j<\/p>\n

mindist = 10000
\nFor i = 1 To lastRow
\n‘meaninful test check if we already distanced that value
\nFor j = 1 To lastFound
\nIf UCase(region) = UCase(isosearch(j)) Then
\nBestMatch = isofound(j)
\nj = lastFound
\ni = lastRow
\n‘exit this loop and the above one
\nxfound = True
\nEnd If<\/p>\n

Next j
\n‘ means we don’t have an already mapped best match
\n‘ looping through dictionary
\nIf xfound = False Then
\ntestword = isodict(i)
\ndistance = Levenshtein(UCase(region), UCase(testword))
\nIf distance = 0 Then
\nBestMatch = testword
\ni = lastRow<\/p>\n

Else
\nIf distance < mindist Then\n BestMatch = testword\n mindist = distance\n End If\n End If\n End If\n\nNext i\n ' write the value of best match in the found dictionary\n\nEnd Function\n\n[\/code]\n\n<\/p>\n\n\n\n

Tre’ sa ma modernizez.<\/p>\n\n\n\n

Si codul asta apare asa pentru ca sa imi bag pula in “Blocks”<\/p>\n","protected":false},"excerpt":{"rendered":"

Un tip cu skillsetul si ocupatiunile mele probabil ca nu s-ar recomanda ca un excelar. Orice tip care poate programa in ceva limbaje ar zice…<\/p>\n","protected":false},"author":2,"featured_media":14688,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[229],"tags":[3159,3160],"class_list":["post-14626","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tech","tag-excel","tag-scripturi-vba"],"views":3385,"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/catalinx.ro\/wordpress\/wp-content\/uploads\/2019\/03\/excel_icon.png","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4Eq23-3NU","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/14626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14626"}],"version-history":[{"count":6,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/14626\/revisions"}],"predecessor-version":[{"id":14642,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/14626\/revisions\/14642"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/14688"}],"wp:attachment":[{"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/catalinx.ro\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}