Excel数据检索省力小工具（文末附源码）

引言

​ 相信很多人都是用过VLOOKUP函数来检索和处理Excel数据。比如教师查看班级学生成绩表，想单独检索某个科目、某个学生，某一分数段（80~90分数段内的成绩）；或者会计/财务想要统计某个薪资段内的工资等等。因此，Excel数据检索的使用场景和需求都非常迫切和可观。

01、VLOOKUP函数

VLOOKUP函数是Microsoft Excel中常用的查找函数之一，用于在一个指定的数据范围内查找某个特定值，并返回该值所在行或列的相关信息。以下是关于VLOOKUP函数的详细介绍：

语法：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: 要查找的值，即要在数据范围中查找的目标值。
• table_array: 包含要进行查找的数据范围，通常是一个表格或区域。
• col_index_num: 指定要返回的值所在的列号。列号是相对于table_array的起始列的位置。
• [range_lookup]: 可选参数，用于指定查找方式。如果为TRUE（或省略），则进行近似匹配（查找范围内最接近的值）。如果为FALSE，执行精确匹配（仅返回完全匹配的值）。

示例：

=VLOOKUP(A2, B2:E10, 3, FALSE)

在这个例子中，函数将查找单元格A2中的值在B2:E10范围内，返回该值所在行的第3列的内容。FALSE表示进行精确匹配。

注意事项：

1. lookup_value必须位于第一列。
2. 如果查找的值不存在，VLOOKUP将返回错误值#N/A。
3. 在使用VLOOKUP函数时，确保数据范围是有序的，以获得正确的结果。

VLOOKUP函数在处理大量数据和建立关联性时非常有用，例如在表格中查找员工编号并返回相应的姓名或查找产品代码并返回价格等。

02、工具介绍

​ 上图实现了一个使用Python和Tkinter GUI库创建的Excel关键词检索小工具。它可以让用户选择一个Excel文件，并输入要搜索的关键词和要添加的底色。程序将遍历Excel文件中的每个单元格，如果它们包含了关键词，就会将底色设置为用户选择的颜色。最后，程序将Excel文件保存，并提示用户操作完成。

源代码如下：

import openpyxl
from tkinter import *
from tkinter import messagebox, simpledialog, filedialog
from openpyxl.styles import PatternFill

# 创建颜色映射字典
color_mapping = {
    '白色': 'FFFFFFFF',
    '红色': 'FFFF0000',
    '绿色': 'FF00FF00',
    '黄色': 'FFFFFF00',
    '洋红色': 'FFFF00FF',
    '深绿色': 'FF006400',
    '深黄色': 'FF808000',
    '深青色': 'FF008080',
}

# 创建弹出窗口
root = Tk()
root.title("Excel关键词检索小工具")
root.geometry("300x200")
root.resizable(False, False)

# Excel文件路径
excel_path = StringVar()
excel_path.set("未选择文件")


def select_excel_file():
    # 弹出文件选择对话框
    file_path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
    if file_path:
        excel_path.set(file_path)


# 选择Excel文件按钮和文件路径标签
frame = Frame(root)
frame.pack(pady=10)
Button(frame, text="选择Excel文件", command=select_excel_file).pack(side=LEFT, padx=10)
Label(frame, textvariable=excel_path).pack(side=LEFT)

# 关键词输入框和底色选择框放在同一行
input_frame = Frame(root)
input_frame.pack()

# 关键词输入框
Label(input_frame, text="关键词组检索:").pack(side=LEFT)
search_text = Entry(input_frame)
search_text.pack(side=LEFT, padx=5)

# 颜色选择框和提示标签放在同一行
color_frame = Frame(root)
color_frame.pack(pady=5)

# 颜色选择框
Label(color_frame, text="底色：").pack(side=LEFT)
colors = list(color_mapping.keys())
color_var = StringVar()
color_var.set(colors[0])
color_menu = OptionMenu(color_frame, color_var, *colors)
color_menu.pack(side=LEFT, padx=5)

# 提示标签
tip_label = Label(color_frame, text="（默认为白色）")
tip_label.pack(side=LEFT)


def search_and_fill():
    # 获取用户选择的Excel文件路径
    file_path = excel_path.get()
    if not file_path:
        messagebox.showerror("错误", "请选择Excel文件")
        return

    try:
        # 打开Excel文件
        wb = openpyxl.load_workbook(file_path)
        ws = wb.active

        # 获取用户输入的关键词和底色选择
        keyword = search_text.get()

        # 获取用户选择的底色
        color = color_var.get()

        # 检查是否选择了底色，如果未选择，将color_code设置为None
        color_code = color_mapping[color]

        # 遍历每一个单元格，如果其包含关键词，则添加底色
        for row in ws.iter_rows():
            for cell in row:
                if keyword in str(cell.value):
                    if color_code is not None:
                        fill = PatternFill(start_color=color_code, end_color=color_code, fill_type='solid')
                        cell.fill = fill
                    else:
                        cell.fill = None

        # 保存Excel文件
        wb.save(file_path)

        # 提示用户操作完成
        messagebox.showinfo("完成", "单元格颜色已更新")

    except Exception as e:
        messagebox.showerror("错误", str(e))


# 确认按钮
Button(root, text="确定", command=search_and_fill).pack(pady=10)

# 将弹出窗口置于屏幕中心
windowWidth = root.winfo_reqwidth()
windowHeight = root.winfo_reqheight()
positionRight = int(root.winfo_screenwidth() / 2 - windowWidth / 2)
positionDown = int(root.winfo_screenheight() / 2 - windowHeight / 2)
root.geometry("+{}+{}".format(positionRight, positionDown))

# 运行窗口循环
root.mainloop()

03、使用说明

​ 上述示例用户选择了本地桌面的Excel文件，对ARE关键词进行检索，同时选择将检索到的关键词单元格底色设置为想要的颜色，直观地反映意向操作数据，默认色系为白色，同时用户可以将其他底色的单元格设置为白色而实现底色清除的功能。

运行效果如下:

代码功能介绍：

以下是对代码功能的总结：

1. 导入库：
   • 使用import openpyxl导入Openpyxl库，用于处理Excel文件。
   • 使用from tkinter import *导入Tkinter库的所有模块，包括messagebox、simpledialog和filedialog。
2. 颜色映射字典：
   • 创建了一个颜色映射字典color_mapping，将颜色的中文名映射为十六进制表示。
3. 创建主窗口：
   • 使用Tkinter创建了一个主窗口，设置了窗口标题、大小和不可调整大小。
4. 文件选择功能：
   • 创建了一个按钮和标签，用于选择Excel文件。
   • 使用filedialog.askopenfilename弹出文件选择对话框，获取用户选择的Excel文件路径。
5. 关键词和底色输入框：
   • 创建了关键词输入框和底色选择框，用于用户输入关键词和选择底色。
6. 搜索和填充功能：
   • 创建了一个按钮，点击后触发搜索和填充功能。
   • 打开用户选择的Excel文件，遍历每个单元格，查找包含关键词的单元格，然后根据用户选择的底色添加相应的填充。
7. 保存并提示：
   • 保存修改后的Excel文件。
   • 弹出消息框提示用户操作完成或报告错误信息。
8. 窗口居中：
   • 将弹出窗口置于屏幕中心。
9. 窗口循环：
   • 使用root.mainloop()启动Tkinter窗口循环，使窗口保持运行状态。
     件，遍历每个单元格，查找包含关键词的单元格，然后根据用户选择的底色添加相应的填充。
10. 保存并提示：
    • 保存修改后的Excel文件。
    • 弹出消息框提示用户操作完成或报告错误信息。
11. 窗口居中：
    • 将弹出窗口置于屏幕中心。
12. 窗口循环：
    • 使用root.mainloop()启动Tkinter窗口循环，使窗口保持运行状态。
      总体而言，这个小工具允许用户选择一个Excel文件，输入关键词和选择底色，然后在文件中查找包含关键词的单元格并进行底色填充。