python组合求和-近似值

《excel吧提问-同类商品找出几个和最接近且不能小于目标值的》，正好之前python写过类似组合求和的代码《python组合求和》
可以分别查找大于等于、小于等于、绝对值最近似和值，仅返回最符合条件的组合

from time import time
from itertools import combinations
from openpyxl import load_workbook

def search(find_dict, sum_m, mode='>='):
    """
    对列表中的所有元素，遍历其所有组合，使其和为需查找的值，输出结果以列表嵌套形式
    :param dict find_dict: 字典find_dict，键-单元格地址，值-数值
    :param Number sum_m: 需查找的和值sum_m
    :param str mode: 3种查找模式，>=，<=，<>，即大于等于、小于等于、绝对值与最近和值
    返回结果：列表内嵌套元组[num, (address)]
    """
    start_time = time()
    list_keys = list(find_dict.keys())  # 字典键列表
    result = [None, None]  # 列表，存放sum_c及其地址元组
    for i in range(1, len(list_keys)+1):  # 所有组合个数（2^n - 1）
        combin_list = list(combinations(list_keys, i))  # 迭代器写入列表
        for c in combin_list:
            sum_c = sum(find_dict[k] for k in c)  # 组合键值对求和
            if sum_c == sum_m:  # 等于，直接返回结果
                result[0] = sum_c
                result[1] = c
                end_time = time()
                print('已查找相等组合，用时：%.4f秒' % (end_time - start_time))
                return result
            elif mode == '>=' and sum_c > sum_m:  # 大于
                if result[0] == None or sum_c < result[0]:
                    result[0] = sum_c
                    result[1] = c
            elif mode == '<=' and sum_c < sum_m:  # 小于
                if result[0] == None or sum_c > result[0]:
                    result[0] = sum_c
                    result[1] = c
            elif mode == '<>':  # 绝对值
                if result[0] == None or abs(sum_m - sum_c) < abs(sum_m - result[0]):
                    result[0] = sum_c
                    result[1] = c
    end_time = time()
    print('所有组合都已查找，用时：%.4f秒' % (end_time - start_time))
    return result

def read_excel_search(excel_file):
    """
    读取Excel表数据的待查找find_dict、和sum_m，返回符合条件的地址，写入标记
    :param str excel_file: Excel表格文件路径
    """
    start_time = time()
    wb =  load_workbook(excel_file)  # openpyxl打开文件
    ws = wb['Sheet1']
    nrow = ws.max_row
    for i in range(3, nrow+1):  # 左闭右开
        if ws.cell(i, 1).value != None:
            sum_m = ws.cell(i, 2).value
            find_dict = {}  # 要查找的数据，空字典，键-单元格地址，值-数值
            for j in range(3, nrow+1):
                if ws.cell(i, 1).value == ws.cell(j, 5).value:
                    address = '{!s}-{!s}'.format(ws.cell(j, 7).row, ws.cell(j, 7).column)
                    find_dict[address] = ws.cell(j, 7).value
            ## 调用查找函数，返回符合条件的值；返回结果是列表元组
            result = search(find_dict, sum_m, '<>')
            ## openpyxl写入Excel
            if result[1] != None:
                for res in result[1]:  # 遍历结果，地址元组，写入标记
                    r = res.split('-')
                    ws.cell(int(r[0]), int(r[1])+1).value = '1'
    wb.save(excel_file)  # 保存文件
    end_time = time()
    print('Excel已写入查找结果，累计用时：%.4f秒' % (end_time - start_time))

if __name__ == '__main__':
    xlsx = r'E:\测试\组合求和.xlsx'
    read_excel_search(xlsx)

举例