如何在电子表格中查找最优参数组合并计算累计搜索量
可行解决方案
方案1:透视表+笛卡尔积组合生成(无代码需求,适合小体量数据集)
- 第一步:先整理原始数据表结构,确保每行对应1个关键词,固定包含6列:关键词文本、C1分类、C2分类、C3分类、C4分类、月均搜索量
- 第二步:提取各分类唯一值:分别导出C1到C4四个分类下的所有不重复参数值,每个分类的唯一值单独存储为一列
- 第三步:生成全量参数组合(笛卡尔积):
Excel操作:用
Power Query的「添加自定义列」功能,依次对4个分类的唯一值列表做交叉联接,自动生成所有合法参数组合
Google Sheets操作:用TOCOL(MAKEARRAY(COUNTA(C1列表),COUNTA(C2列表),LAMBDA(a,b,INDEX(C1列表,a)&"|"&INDEX(C2列表,b))))嵌套函数逐层生成4个分类的组合,最后用TEXTSPLIT拆分回4个独立列 - 第四步:统计累计搜索量:在生成的参数组合表新增「累计搜索量」列,用
SUMIFS函数匹配原始数据中同时满足4个分类参数的所有行,求和对应月均搜索量,公式示例:=SUMIFS(原始数据!F:F,原始数据!B:B,A2,原始数据!C:C,B2,原始数据!D:D,C2,原始数据!E:E,D2)
其中A2到D2是当前行的4个分类参数值,原始数据!F:F是月均搜索量所在列 - 第五步:筛选最优组合:按累计搜索量降序排列所有组合,即可得到搜索热度最高的参数组合。
方案2:Python批量处理(适合10万行以上的大数据集)
- 第一步:导入依赖库并读取原始数据集,代码示例:
import pandas as pd import itertools # 替换为你的本地数据集文件路径 df = pd.read_excel("本地数据集文件路径.xlsx", sheet_name="原始数据") - 第二步:提取各分类的唯一值列表:
c1_unique = df["C1"].dropna().unique().tolist() c2_unique = df["C2"].dropna().unique().tolist() c3_unique = df["C3"].dropna().unique().tolist() c4_unique = df["C4"].dropna().unique().tolist() - 第三步:生成所有参数的全量组合:
# 生成笛卡尔积全量组合 all_combinations = list(itertools.product(c1_unique, c2_unique, c3_unique, c4_unique)) # 转换为DataFrame格式 comb_df = pd.DataFrame(all_combinations, columns=["C1", "C2", "C3", "C4"]) - 第四步:匹配统计每个组合的累计搜索量:
# 先对原始数据按C1-C4分组求和,得到已有组合的总搜索量 group_df = df.groupby(["C1", "C2", "C3", "C4"])["月均搜索量"].sum().reset_index(name="累计搜索量") # 把总搜索量映射到全量组合表,无匹配的组合搜索量记为0 result_df = pd.merge(comb_df, group_df, on=["C1", "C2", "C3", "C4"], how="left").fillna(0) - 第五步:导出结果到指定标签页:
# 导出结果到「预期结果」标签页,已有同名标签会自动覆盖 with pd.ExcelWriter("本地数据集文件路径.xlsx", mode="a", if_sheet_exists="replace") as writer: result_df.to_excel(writer, sheet_name="预期结果", index=False)
注意事项
- 若部分关键词没有某一类参数(比如没有价格属性),提取唯一值时可以添加空值占位,避免漏算不含该类参数的组合
- 若参数组合量级过大(超过10万组),可以先过滤掉月均搜索量低于指定阈值的关键词对应的参数,再生成组合减少计算量
内容的提问来源于stack exchange,提问作者Thomas1331
相关产品推荐
相关产品推荐

