【PAT】 A1055 The Worlds Richest (25分)

本文介绍了一个算法,用于从全球富豪名单中筛选出特定年龄段内的最富有人群。算法首先按财富值、年龄和姓名字典序进行排序,然后遍历数组输出符合条件的人选。输入包括总人数、查询次数、个人详细信息及查询条件,输出则是按要求排序后的富豪名单。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

题目链接

1055 The World’s Richest (25分)

Forbes magazine publishes every year its list of billionaires based on the annual ranking of the world’s wealthiest people. Now you are supposed to simulate this job, but concentrate only on the people in a certain range of ages. That is, given the net worths of N people, you must find the M richest people in a given range of their ages.

Input Specification:

Each input file contains one test case. For each case, the first line contains 2 positive integers: N (≤10 ​5 ​​ ) - the total number of people, and K (≤10 ​3 ​​ ) - the number of queries. Then N lines follow, each contains the name (string of no more than 8 characters without space), age (integer in (0, 200]), and the net worth (integer in [−10 ​6 ​​ ,10 ​6 ​​ ]) of a person. Finally there are K lines of queries, each contains three positive integers: M (≤100) - the maximum number of outputs, and [Amin, Amax] which are the range of ages. All the numbers in a line are separated by a space.

Output Specification:

For each query, first print in a line Case #X: where X is the query number starting from 1. Then output the M richest people with their ages in the range [Amin, Amax]. Each person’s information occupies a line, in the format

Name Age Net_Worth

The outputs must be in non-increasing order of the net worths. In case there are equal worths, it must be in non-decreasing order of the ages. If both worths and ages are the same, then the output must be in non-decreasing alphabetical order of the names. It is guaranteed that there is no two persons share all the same of the three pieces of information. In case no one is found, output None.

Sample Input:

12 4
Zoe_Bill 35 2333
Bob_Volk 24 5888
Anny_Cin 95 999999
Williams 30 -22
Cindy 76 76000
Alice 18 88888
Joe_Mike 32 3222
Michael 5 300000
Rosemary 40 5888
Dobby 24 5888
Billy 24 5888
Nobody 5 0
4 15 45
4 30 35
4 5 95
1 45 50

Sample Output:

Case #1:
Alice 18 88888
Billy 24 5888
Bob_Volk 24 5888
Dobby 24 5888
Case #2:
Joe_Mike 32 3222
Zoe_Bill 35 2333
Williams 30 -22
Case #3:
Anny_Cin 95 999999
Michael 5 300000
Alice 18 88888
Cindy 76 76000
Case #4:

None

思路与总结:

  1. 先按要求1.财富值由大到小 2年龄有小到大 3名字字典序有小到大;写好cmp()sort()排好
  2. 遍历结构体数组,把符合年龄要求的按序输出即可

AC代码

#include<cstdio>
#include<cstring>
#include<algorithm>
using namespace std;
int const maxn = 100010;
struct node
{
	char name[10];
	int age;
	int worth;
}man[maxn];

bool cmp(node A, node B){
	if(A.worth!=B.worth) return A.worth>B.worth;
	else if(A.age!=B.age) return A.age<B.age;
	else return strcmp(A.name, B.name)<0 ;
}
int n,k;

int main()
{
	scanf("%d%d",&n,&k);
	for(int i=0; i<n; i++){
		scanf("%s %d %d",&man[i].name, &man[i].age, &man[i].worth);
	}
	sort(man, man+n, cmp);
    
	for(int i=1; i<=k; i++){
		int m, Amin, Amax, cnt = 0;
        scanf("%d%d%d",&m,&Amin,&Amax);
		printf("Case #%d:\n",i );
		for(int j=0; j<n; j++){
			if(man[j].age>=Amin && man[j].age<=Amax){
				printf("%s %d %d\n",man[j].name, man[j].age, man[j].worth );
				cnt++;
			}
			if(cnt == m) break;
		}
		if(cnt == 0) printf("None\n");

	}
    return 0;
}
<think>我们遇到了一个AttributeError:'DefinedNameDict' object has no attribute 'append' 在openpyxl中,`wb.defined_names` 是一个字典-like的对象(DefinedNameDict),它没有`append`方法。 实际上,我们应该使用`wb.defined_names.add(defined_name)`来添加一个DefinedName对象。 因此,我们需要将: wb.defined_names.append(named_range) 改为: wb.defined_names.add(named_range) 但是,请注意,在openpyxl 3.1及以上版本中,添加命名范围的正确方式如下: 另外,我们还需要注意,在创建DefinedName对象时,attr_text的格式应该是工作表和范围的组合,例如:'Sheet1!$A$1:$B$10' 让我们修改代码:</think>### 错误析 报错信息 `AttributeError: 'DefinedNameDict' object has no attribute 'append'` 表明在 openpyxl 中,`wb.defined_names` 是一个字典对象而非列表,因此不能使用 `append()` 方法。正确的做法是使用字典的赋值语法添加命名范围。 ### 修复方案 使用 `wb.defined_names[name] = DefinedName(...)` 语法替代 `append()` 方法: ```python # 修改前(错误) wb.defined_names.append(named_range) # 修改后(正确) wb.defined_names['RatingTable'] = named_range ``` ### 完整修复代码 ```python import pandas as pd import openpyxl from openpyxl.utils import get_column_letter from openpyxl.styles import PatternFill from openpyxl.workbook.defined_name import DefinedName # 添加导入 def add_esg_formulas(input_path, output_path): # 加载工作簿和工作表 wb = openpyxl.load_workbook(input_path) ws = wb.active # 添加新列标题 new_columns = [ "基础ESG得", "行业系数", "动态进步", "供应链", "垄断矫正", "数据异常扣", "总", "MSCI转换", "晨星转换", "标普转换", "华证转换", "中证转换", "Wind转换", "Wind评级提升", "减排技术研发投入率★", "Tier1供应商合规率★", "碳强度年降幅★", "赫芬达尔指数★", "平台佣金率★", "社会议题投入占比★" ] start_col = ws.max_column + 1 for i, col_name in enumerate(new_columns): col_letter = get_column_letter(start_col + i) ws[f"{col_letter}1"] = col_name # 为需要补充数据的列添加黄色背景 if "★" in col_name: for row in range(2, ws.max_row + 1): ws[f"{col_letter}{row}"].fill = PatternFill( start_color="FFFF00", end_color="FFFF00", fill_type="solid" ) # 设置公式 for row in range(3, ws.max_row + 1): # 从第3行开始(数据行) # 字母评级转换公式 letter_rating_formula = ( f'IF(ISBLANK(B{row}), "", ' f'IF(B{row}="AAA",9,' f'IF(B{row}="AA",8,' f'IF(B{row}="A",7,' f'IF(B{row}="BBB",6,' f'IF(B{row}="BB",5,' f'IF(B{row}="B",4,' f'IF(B{row}="CCC",3,3)))))))' ) # 晨星评转换公式 morningstar_formula = ( f'IF(ISBLANK(E{row}), "", ' f'IF(E{row}>=40,9,' f'IF(E{row}>=30,7,' f'IF(E{row}>=20,6,' f'IF(E{row}>=10,5,3))))' ) # 基础ESG得公式 base_esg_formula = ( f'=((IFERROR({get_column_letter(start_col+6)}{row},0)+' f'IFERROR({get_column_letter(start_col+7)}{row},0)+' f'IFERROR({get_column_letter(start_col+8)}{row},0)+' f'IFERROR({get_column_letter(start_col+9)}{row},0)+' f'IFERROR({get_column_letter(start_col+10)}{row},0)+' f'IFERROR({get_column_letter(start_col+11)}{row},0))/' f'MAX(1,COUNT({get_column_letter(start_col+6)}{row},' f'{get_column_letter(start_col+7)}{row},' f'{get_column_letter(start_col+8)}{row},' f'{get_column_letter(start_col+9)}{row},' f'{get_column_letter(start_col+10)}{row},' f'{get_column_letter(start_col+11)}{row})))*0.4' ) # 行业系数公式 industry_formula = ( f'=IF(OR(T{row}="能源类",T{row}="工业类"),' f'IF({get_column_letter(start_col+15)}{row}>=' f'IF(T{row}="能源类",0.08,0.05),1.2,0.9),' f'IF(OR(T{row}="科技类",T{row}="消费类"),' f'IF({get_column_letter(start_col+18)}{row}>=' f'IF(T{row}="科技类",0.025,0.018),1.1,1.0),1.0))' ) # Wind评级提升公式 wind_improve_formula = ( f'=((IF(AND(NOT(ISBLANK(S{row})),NOT(ISBLANK(R{row}))),' f'MAX(VLOOKUP(R{row},RatingTable,2,0)-VLOOKUP(S{row},RatingTable,2,0),0),0)+' f'IF(AND(NOT(ISBLANK(R{row})),NOT(ISBLANK(Q{row}))),' f'MAX(VLOOKUP(Q{row},RatingTable,2,0)-VLOOKUP(R{row},RatingTable,2,0),0),0))/3)*10' ) # 动态进步公式 progress_formula = ( f'={get_column_letter(start_col+12)}{row}+' f'MIN(10,({get_column_letter(start_col+13)}{row}/1.8)*10)+' f'MIN(10,({get_column_letter(start_col+14)}{row}/0.7)*10)' ) # 垄断矫正公式 monopoly_formula = ( f'=IF(AND(OR(T{row}="金融类",T{row}="能源类"),U{row}="国企"),' f'-5*{get_column_letter(start_col+16)}{row},' f'IF(AND(OR(T{row}="科技类",T{row}="消费类"),' f'IF({get_column_letter(start_col+17)}{row}>0.1,-3*{get_column_letter(start_col+17)}{row},0),0))' ) # 数据异常扣公式 penalty_formula = ( f'=IF(ABS(' f'AVERAGE({get_column_letter(start_col+6)}{row},{get_column_letter(start_col+8)}{row})' f'-AVERAGE({get_column_letter(start_col+9)}{row},{get_column_letter(start_col+10)}{row},{get_column_letter(start_col+11)}{row})' f')>=2,-3,0)' ) # 总公式 total_formula = ( f'=({get_column_letter(start_col)}{row}*{get_column_letter(start_col+1)}{row})' f'+{get_column_letter(start_col+2)}{row}' f'+{get_column_letter(start_col+3)}{row}' f'+{get_column_letter(start_col+4)}{row}' f'+{get_column_letter(start_col+5)}{row}' ) # 写入公式 ws[f"{get_column_letter(start_col+6)}{row}"] = letter_rating_formula # MSCI转换 ws[f"{get_column_letter(start_col+7)}{row}"] = morningstar_formula # 晨星转换 for col_offset in [8, 9, 10, 11]: # 标普/华证/中证/Wind转换 ws[f"{get_column_letter(start_col+col_offset)}{row}"] = letter_rating_formula.replace("B{row}", get_column_letter(2+col_offset-8)+str(row)) ws[f"{get_column_letter(start_col)}{row}"] = base_esg_formula # 基础ESG得 ws[f"{get_column_letter(start_col+1)}{row}"] = industry_formula # 行业系数 ws[f"{get_column_letter(start_col+12)}{row}"] = wind_improve_formula # Wind评级提升 ws[f"{get_column_letter(start_col+2)}{row}"] = progress_formula # 动态进步 ws[f"{get_column_letter(start_col+4)}{row}"] = monopoly_formula # 垄断矫正 ws[f"{get_column_letter(start_col+5)}{row}"] = penalty_formula # 数据异常扣 ws[f"{get_column_letter(start_col+6)}{row}"] = total_formula # 总 # 创建评级转换表 ws["A1000"] = "评级转换表" ratings = ["AAA", "AA", "A", "BBB", "BB", "B", "CCC"] scores = [9, 8, 7, 6, 5, 4, 3] for i, (rating, score) in enumerate(zip(ratings, scores), start=1001): ws[f"A{i}"] = rating ws[f"B{i}"] = score # 定义名称"RatingTable"引用这个区域(使用字典赋值语法) if "RatingTable" not in wb.defined_names: # 创建 DefinedName 对象 named_range = DefinedName( name='RatingTable', attr_text=f"'{ws.title}'!$A$1001:$B${1000+len(ratings)}" ) # 使用字典赋值语法添加命名范围 wb.defined_names['RatingTable'] = named_range # 添加说明文本 ws["A1050"] = "★需要手动补充的数据项:" ws["A1051"] = "1. 减排技术研发投入率 = (自主研发减碳技术投入/总营收)" ws["A1052"] = "2. Tier1供应商ESG合规率 = 接入区块链碳管理平台的供应商比例" ws["A1053"] = "3. 碳强度年降幅 = (上年碳排放强度 - 本年碳排放强度)/上年碳排放强度" ws["A1054"] = "4. 赫芬达尔指数(HHI) = Σ(企业市场份额)^2 (金融/能源类国企填写)" ws["A1055"] = "5. 平台商户佣金率 (科技/消费类填写)" ws["A1056"] = "6. 社会议题投入占比 = 数据隐私/安全投入/总营收" # 保存工作簿 wb.save(output_path) # 执行函数 input_file = "D:/2.xlsx" output_file = "D:/3.xlsx" add_esg_formulas(input_file, output_file) ``` ### 关键修改说明 1. **添加必要的导入**: ```python from openpyxl.workbook.defined_name import DefinedName ``` 2. **修复命名范围创建方式**: ```python # 错误方式(导致AttributeError) wb.defined_names.append(named_range) # 正确方式(使用字典赋值语法) wb.defined_names['RatingTable'] = named_range ``` 3. **完整工作流程**: - 检查命名范围是否已存在:`if "RatingTable" not in wb.defined_names:` - 创建 `DefinedName` 对象 - 使用字典赋值语法添加命名范围 - 保留所有其他功能不变 ### 验证说明 此修复解决了以下问题: 1. 解决了 `AttributeError: 'DefinedNameDict' object has no attribute 'append'` 错误 2. 使用正确的API创建命名范围 3. 确保所有公式中的 `VLOOKUP(R{row},RatingTable,2,0)` 能正确引用评级转换表 4. 保持脚本所有原始功能完整 脚本现在应该能正常运行,创建包含复杂ESG计算逻辑的Excel文件,用户只需在黄色标记的单元格补充数据即可自动计算总
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值