使用Python计算VLOOKUPS的列

Question

我正在练习Python，对此我非常陌生，我想我应该把它应用到在excel中做VLOOKUPS时的一个小问题上。我们拥有的数据库非常庞大，>；15000行长，最多和超过GG的列，所以我想要一个简单的方法来检查列的数量是为了查找，而不是突出显示，滞后，或手动计数

我试过下面的代码，在一定程度上是有效的。它允许我检查两个不同的3列代码的值（excel max是XFD）；但是，如果我比较列AA和XFD，会有一个错误，因为在[2]处没有值作为开始，没有数组

在执行for循环之前，如何排除或检查它是否存在

def get_character_distance2(start,end):
endList = list(end)
print(endList)

startList = list(start)
print(startList)

firstDistance = 0
secondDistance = 0
thirdDistance = 0

totalEnd = 0
totalStart = 0

for item in endList[0]:
    firstDistance += (ord(item.upper()) - ord(startList[0])) + 1

for item in endList[1]:
    secondDistance += ((ord(item.upper()) - ord(startList[1])) + 1) * 26

for item in endList[2]:
    thirdDistance += ((ord(item.upper()) - ord(startList[2])) + 1) * 676

totalStart += firstDistance + secondDistance + thirdDistance
print(firstDistance)
print(secondDistance)
print(thirdDistance)
print(totalStart)

对于结果：

get_character_distance2('AAA','ZZZ')

结果（良好）：

['Z', 'Z', 'Z']
['A', 'A', 'A']
26
676
17576
18278

不同的检查：

get_character_distance2('AA','ZZZ')

坏结果：

['Z', 'Z', 'Z']
['A', 'A']
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-185-c330b0ebbf1d> in <module>()
----> 1 get_character_distance2('AA','ZZZ')

<ipython-input-182-a080de45986e> in get_character_distance2(start, end)
 23 
 24     for item in endList[2]:
---> 25         thirdDistance += (ord(item.upper()) - ord(startList[2]))
 26         thirdDistance +=1
 27         thirdDistance *= 676

IndexError: list index out of range

我只想和第一个一样，它添加for循环的值，前提是实际上有一个级别可以计算。如果没有数组值，则可以假定其中的数字为0

Answer 1

我根据Anwarvic关于try和except的建议提出了以下解决方案

def get_character_distance2(start,end):
startList = list(start)
print(startList)

endList = list(end)
print(endList)

firstDistance = 0
secondDistance = 0
thirdDistance = 0

totalEnd = 0
totalStart = 0

for item in endList[0]:
    firstDistance += (ord(item.upper()) - ord(startList[0])) + 1

try:
    for item in endList[1]:
        secondDistance += ((ord(item.upper()) - ord(startList[1])) + 1) * 26
except:
    try:
        for item in endList[1]:
            secondDistance += ((ord(item.upper()) - ord('A')) + 1) * 26
    except:
        secondDistance = 0

try:
    for item in endList[2]:
        thirdDistance += ((ord(item.upper()) - ord(startList[2])) + 1) * 676
except:
    try:
        for item in endList[2]:
            thirdDistance += ((ord(item.upper()) - ord('A')) + 1) * 676
    except:
        thirdDistance = 0

totalStart += firstDistance + secondDistance + thirdDistance
print(firstDistance)
print(secondDistance)
print(thirdDistance)
print(totalStart)

非常感谢