Excelar Hardcore

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ă si alte figuri .

Din experienta mea, proramatorii urasc excelul si powerpointu’.

Eu sunt un excelar hardcore. Pentru ca daca e vorba de analiza de date , corectie and sheet 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.

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% .

Gen – asta e distanta, copiata de pe ceva sourceforge, nu mai stiu exact.

Iar asta-i contextul .

[code language=”vb”]
Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long

Dim i As Long, j As Long
Dim string1_length As Long
Dim string2_length As Long
Dim distance() As Long

string1_length = Len(string1)
string2_length = Len(string2)
ReDim distance(string1_length, string2_length)

For i = 0 To string1_length
distance(i, 0) = i
Next

For j = 0 To string2_length
distance(0, j) = j
Next

For i = 1 To string1_length
For j = 1 To string2_length
If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
distance(i, j) = distance(i – 1, j – 1)
Else
distance(i, j) = Application.WorksheetFunction.Min _
(distance(i – 1, j) + 1, _
distance(i, j – 1) + 1, _
distance(i – 1, j – 1) + 1)
End If
Next
Next

Levenshtein = distance(string1_length, string2_length)

End Function
[/code]

[code language=”vb”]
Function BestMatch(region As String) As String

Dim wb As Workbook
Dim sstart As String
Dim send As String

Set wb = ThisWorkbook

‘define your sheets
Dim ws_data As Worksheet
Dim ws_iso As Worksheet
Dim ws_found As Worksheet
Dim vfound As Range
Dim vsearch As Range
Dim vdist As Range
Set ws_data = wb.Sheets(“DATA”)
Set ws_iso = wb.Sheets(“ISO”)
Set ws_found = wb.Sheets(“ISOFOUND”)
sstart = “”
Dim lastRow As Long
BestMatch = “Not Found”
lastRow = ws_iso.Range(“A” & Rows.Count).End(xlUp).Row + 1
lastFound = ws_found.Range(“A” & Rows.Count).End(xlUp).Row + 1
Dim isodict(1 To 32000) As String
Dim isosearch(1 To 70000) As String
Dim isofound(1 To 70000) As String
Dim isofounddist(1 To 70000) As String
Dim xfound As Boolean
For i = 1 To lastRow

n isodict(i) = ws_iso.Cells(i, 4)
Next i

For j = 1 To lastFound
isosearch(j) = ws_found.Cells(j, 1)
isofound(j) = ws_found.Cells(j, 2)
isofounddist(j) = ws_found.Cells(j, 3)
Next j

mindist = 10000
For i = 1 To lastRow
‘meaninful test check if we already distanced that value
For j = 1 To lastFound
If UCase(region) = UCase(isosearch(j)) Then
BestMatch = isofound(j)
j = lastFound
i = lastRow
‘exit this loop and the above one
xfound = True
End If

Next j
‘ means we don’t have an already mapped best match
‘ looping through dictionary
If xfound = False Then
testword = isodict(i)
distance = Levenshtein(UCase(region), UCase(testword))
If distance = 0 Then
BestMatch = testword
i = lastRow

Else
If distance < mindist Then BestMatch = testword mindist = distance End If End If End If Next i ' write the value of best match in the found dictionary End Function [/code]

Tre’ sa ma modernizez.

Si codul asta apare asa pentru ca sa imi bag pula in “Blocks”