# Excel自动筛选高级应用：让你数据处理效率翻倍

import pandas as pd

# 筛选金额大于1000的记录
df = pd.read_excel('销售数据.xlsx')
result = df[df['金额'] > 1000]

# 且条件：金额>1000 且 地区=北京
result = df[(df['金额'] > 1000) & (df['地区'] == '北京')]

# 或条件：地区为上海或广州
result = df[(df['地区'] == '上海') | (df['地区'] == '广州')]

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

def advanced_filter(source_file, output_file):
    """
    从源数据筛选并输出到新文件
    """
    # 读取源数据
    df = pd.read_excel(source_file, sheet_name='原始数据')
    
    # 高级筛选条件
    conditions = (
        (df['销售额'] >= 10000) &   # 销售额>=1万
        (df['客户等级'].isin(['A', 'B'])) &  # A或B级客户
        (df['日期'] >= '2024-01-01')  # 2024年以后的订单
    )
    
    # 执行筛选
    filtered = df[conditions].copy()
    
    # 添加筛选标记列
    filtered['筛选日期'] = pd.Timestamp.now().strftime('%Y-%m-%d')
    
    # 输出到新Sheet
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        filtered.to_excel(writer, sheet_name='筛选结果', index=False)
        
        # 同时输出统计汇总
        summary = pd.DataFrame({
            '筛选条件': ['销售额>=10000', '客户等级A/B', '日期>=2024'],
            '记录数': [len(filtered)],
            '总销售额': [filtered['销售额'].sum()],
            '平均销售额': [filtered['销售额'].mean().round(2)]
        })
        summary.to_excel(writer, sheet_name='统计汇总', index=False)
    
    return len(filtered)

# 使用
count = advanced_filter('客户数据库.xlsx', '高价值客户.xlsx')
print(f"筛选出 {count} 条高价值客户记录")

import pandas as pd
from datetime import datetime

class ExcelFilter:
    def __init__(self, file_path):
        self.df = pd.read_excel(file_path)
    
    def filter_by_input(self):
        """交互式筛选"""
        print("\n=== Excel高级筛选工具 ===")
        print("支持的字段:", list(self.df.columns))
        
        # 输入筛选条件
        field = input("请输入筛选字段: ")
        if field not in self.df.columns:
            print("字段不存在！")
            return
        
        operator = input("请输入条件 (>, <, =, contains): ")
        value = input("请输入值: ")
        
        # 执行筛选
        if operator == '>':
            result = self.df[self.df[field] > float(value)]
        elif operator == '<':
            result = self.df[self.df[field] < float(value)]
        elif operator == '=':
            result = self.df[self.df[field] == value]
        elif operator == 'contains':
            result = self.df[self.df[field].astype(str).str.contains(value)]
        else:
            print("不支持该操作符")
            return
        
        # 输出结果
        print(f"\n筛选结果: 共 {len(result)} 条记录")
        print(result.head(10))
        
        # 可选择保存
        save = input("是否保存结果? (y/n): ")
        if save.lower() == 'y':
            output = f"筛选结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
            result.to_excel(output, index=False)
            print(f"已保存到: {output}")

# 使用
filter_tool = ExcelFilter('销售数据.xlsx')
filter_tool.filter_by_input()

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font

def highlight_conditions(input_file, output_file):
    """
    自动标记满足条件的行
    """
    df = pd.read_excel(input_file)
    
    # 定义标记规则
    def get_flag(row):
        if row['销售额'] > 50000:
            return '⭐高价值'
        elif row['销售额'] > 20000:
            return '✓达标'
        elif row['销售额'] < 5000:
            return '⚠需关注'
        return ''
    
    df['标记'] = df.apply(get_flag, axis=1)
    
    # 保存到Excel并设置颜色
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='标记结果', index=False)
    
    # 设置高亮颜色
    wb = load_workbook(output_file)
    ws = wb.active
    
    yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
    green_fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
    red_fill = PatternFill(start_color='FFB6C1', end_color='FFB6C1', fill_type='solid')
    
    for row in range(2, ws.max_row + 1):
        flag = ws.cell(row=row, column=ws.max_column).value
        if '高价值' in str(flag):
            ws.cell(row=row, column=ws.max_column).fill = green_fill
        elif '需关注' in str(flag):
            ws.cell(row=row, column=ws.max_column).fill = red_fill
        elif '达标' in str(flag):
            ws.cell(row=row, column=ws.max_column).fill = yellow_fill
    
    wb.save(output_file)
    print(f"标记完成！已保存到: {output_file}")

# 使用
highlight_conditions('员工绩效.xlsx', '员工绩效_标记版.xlsx')

import pandas as pd
import schedule
import time
from datetime import datetime

def daily_filter_report():
    """每日自动生成筛选报告"""
    
    # 读取数据
    df = pd.read_excel('日销售数据.xlsx')
    
    # 生成日报
    today = datetime.now().strftime('%Y-%m-%d')
    today_data = df[df['日期'] == today]
    
    if len(today_data) == 0:
        print(f"今日({today})无销售数据")
        return
    
    # 生成统计
    report = {
        '日期': today,
        '订单数': len(today_data),
        '总销售额': today_data['销售额'].sum(),
        '平均订单额': today_data['销售额'].mean().round(2),
        '最高单笔': today_data['销售额'].max(),
    }
    
    # 保存日报
    report_df = pd.DataFrame([report])
    report_df.to_excel(f'日报_{today}.xlsx', index=False)
    
    print(f"日报已生成: 订单数={report['订单数']}, 销售额={report['总销售额']}")

# 设置定时任务（每天下午6点执行）
schedule.every().day.at("18:00").do(daily_filter_report)

# 运行调度器
while True:
    schedule.run_pending()
    time.sleep(60)