Extract coefficients associated with a chosen label across Excel sheets and check them against a standard value. Create a comparison workbook showing each value, its source sheet, and red highlighting for rows that fail the check.
中文简介正在准备中。当前显示英语简介。
对比Excel多表中的特定系数并对异常值进行颜色标记。
安装前可以查看交给 AI 代理的指令内容。
This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.
Step1 提取不同Sheet中特定维度（如“B1层”）的数值，并进行跨表逻辑对比。
# 定义提取逻辑：定位目标行（如包含'B1'的行）并获取其关联的系数
def extract_target_value(df, target_label='B1', label_col_idx=0, offset_row=1, value_col_idx=2):
"""
在指定列搜索标签，并返回其相对偏移位置的数值
"""
extracted_values = []
for idx, row in df.iterrows():
if str(row.iloc[label_col_idx]).strip() == target_label:
# 提取目标行下方或特定偏移位置的数值
if idx + offset_row < len(df):
val = df.iloc[idx + offset_row].iloc[value_col_idx]
extracted_values.append(val)
return extracted_values
# 分别读取需要对比的Sheet
sheet1_df = pd.read_excel(file_path, sheet_name='Sheet1')
sheet2_df = pd.read_excel(file_path, sheet_name='Sheet2')
# 提取系数（示例：B1层的换算系数）
# 注意：不同Sheet的列索引可能不同，需根据实际结构调整
s1_coeffs = extract_target_value(sheet1_df, target_label='B1', label_col_idx=1, value_col_idx=3)
s2_coeffs = extract_target_value(sheet2_df, target_label='B1', label_col_idx=0, value_col_idx=2)
# 汇总对比数据
comparison_results = []
target_standard = 0.6 # 预设的标准阈值
for val in s1_coeffs:
comparison_results.append({'source': 'Sheet1', 'value': val, 'is_anomaly': val != target_standard})
for val in s2_coeffs:
comparison_results.append({'source': 'Sheet2', 'value': val, 'is_anomaly': val != target_standard})
Step2 生成对比报告，并使用 openpyxl 对异常值（非标准系数）进行红色高亮标记。
from openpyxl import Workbook
from openpyxl.styles import PatternFill
output_path = 'comparison_report.xlsx'
wb = Workbook()
ws = wb.active
ws.title = "Comparison Analysis"
# 写入表头
headers = ['数据来源', '提取数值', '是否符合标准', '状态标记']
ws.append(headers)
# 定义红色填充样式
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
# 遍历结果并写入，同时应用条件格式
for item in comparison_results:
status_text = '正常' if not item['is_anomaly'] else '异常(非0.6)'
row_data = [item['source'], item['value'], '是' if not item['is_anomaly'] else '否', status_text]
ws.append(row_data)
# 如果是异常值，将该行或特定单元格标红
if item['is_anomaly']:
curr_row = ws.max_row
for col_idx in range(1, len(headers) + 1):
ws.cell(row=curr_row, column=col_idx).fill = red_fill
# 保存结果并提供下载
wb.save(output_path)
print(f"Analysis complete. Report saved to: {output_path}")
还没有评价。欢迎分享你的使用感受。
无需登录即可发表。欢迎分享使用感受、提问，或说明在哪些环境下无法运行。
还没有评论。来写第一条吧。
Compare counts and shares for two categories in Excel data. The skill cleans the selected columns, calculates differences and percentages, and produces a summary spreadsheet plus bar and pie charts.
中文简介正在准备中。当前显示英语简介。
Analyze Excel data using custom categories, count distributions, and compare groups with charts. Score text using its length, technical terms, numbers, and selected complex concepts, then visualize difficulty levels and the relationship between text length and scores.
中文简介正在准备中。当前显示英语简介。
Count categories in a chosen data column after removing missing values and placeholder labels. Calculate each category’s share and create high-resolution charts combining counts, percentages, pie charts, and horizontal bars for distribution reports.
中文简介正在准备中。当前显示英语简介。
Clean category and numeric fields in structured data, compare groups with counts and cross-tabulations, and produce charts, Excel results, and a Markdown report. Export visuals as high-resolution PNG and SVG files for use in reports.
中文简介正在准备中。当前显示英语简介。
Filter Excel data using candidate column names, RGB color values, text prefixes, and regular expressions, which match text patterns. Calculate totals and averages, then export results to Excel with highlighted rows and a download link.
中文简介正在准备中。当前显示英语简介。
Filter rows from Excel or previously converted Parquet files, a format designed for efficient data storage. Summarize matching records by group with counts, percentages, and totals, then export the summary to Excel.
中文简介正在准备中。当前显示英语简介。