背景需求
过了一周
把文本的xlsx下载
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx" # 修改为你的excel完整路径
sheet_name = "Sheet1" # 修改为你的工作表名称
output_img = path+r"\幼儿园家长担心词云.png"
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 20
font = r"C:\Windows\Fonts\simhei.ttf" # windows黑体，解决中文乱码
# ==========================================================
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，取top20
counter = Counter(valid_words)
top20 = counter.most_common(top_n)
print("====出现最多20个关键词【词，次数】====")
for k,v in top20:
print(f"{k}：{v}次")
# 拼接top20文本给词云
cloud_text = " ".join([item[0] for item in top20])
# 4、生成词云
wc = WordCloud(
font_path=font,
width=1400,
height=900,
background_color="white",
max_words=top_n,
collocations=False
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(14,9),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=14, pad=15)
plt.tight_layout()
plt.savefig(output_img, bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：{output_img}")
wb.close()
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx" # 修改为你的excel完整路径
sheet_name = "Sheet1" # 修改为你的工作表名称
output_img = path+r"\幼儿园家长担心词云.png"
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 20
font = r"C:\Windows\Fonts\simhei.ttf" # windows黑体，解决中文乱码
# ==========================================================
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"小朋友","是否","不会","老师","问题","不能","情况","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，取top20
counter = Counter(valid_words)
top20 = counter.most_common(top_n)
print("====出现最多20个关键词【词，次数】====")
for k,v in top20:
print(f"{k}：{v}次")
# 拼接top20文本给词云
cloud_text = " ".join([item[0] for item in top20])
# 4、生成词云
wc = WordCloud(
font_path=font,
width=1400,
height=900,
background_color="white",
max_words=top_n,
collocations=False
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(14,9),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=14, pad=15)
plt.tight_layout()
plt.savefig(output_img, bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：{output_img}")
wb.close()
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx" # 修改为你的excel完整路径
sheet_name = "Sheet1" # 修改为你的工作表名称
output_img = path+r"\幼儿园家长担心词云.png"
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 20
font = r"C:\Windows\Fonts\simhei.ttf" # windows黑体，解决中文乱码
# ==========================================================
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"小朋友","是否","不会","老师","问题","不能","情况","同学","能否","孩子","好好","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，取top20
counter = Counter(valid_words)
top20 = counter.most_common(top_n)
print("====出现最多20个关键词【词，次数】====")
for k,v in top20:
print(f"{k}：{v}次")
# 拼接top20文本给词云
cloud_text = " ".join([item[0] for item in top20])
# 4、生成词云
wc = WordCloud(
font_path=font,
width=1400,
height=900,
background_color="white",
max_words=top_n,
collocations=False
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(14,9),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=14, pad=15)
plt.tight_layout()
plt.savefig(output_img, bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：{output_img}")
wb.close()
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx"
sheet_name = "Sheet1"
output_img = path+r"\幼儿园家长担心词云.png"
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 15 # 减少词数量，从20改为15，减少拥挤
font = r"C:\Windows\Fonts\simhei.ttf"
min_count = 2 # 只保留出现>=2次的词，过滤掉只出现1次的微小干扰词
# ==========================================================
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"小朋友","是否","不会","老师","问题","不能","情况","同学","能否","孩子","好好","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，过滤频次过低词汇，取top
counter = Counter(valid_words)
# 过滤：只保留出现次数 >= min_count 的词
filter_counter = {k:v for k,v in counter.items() if v >= min_count}
top_list = Counter(filter_counter).most_common(top_n)
print("====筛选后关键词【词，次数】====")
for k,v in top_list:
print(f"{k}：{v}次")
cloud_text = " ".join([item[0] for item in top_list])
# 4、生成词云：关键调参，解决拥挤小字干扰
wc = WordCloud(
font_path=font,
width=1800, # 加宽画布
height=1200, # 加高画布
background_color="white",
max_words=top_n,
collocations=False,
margin=40, # ✨词语之间边距，拉开距离，解决文字堆叠
relative_scaling=0.4, # ✨控制大小词字号差距，避免有的词过小
min_font_size=24, # ✨设置最小字号，彻底消除很小的干扰小字
max_font_size=320 # ✨限制最大字号，防止个别词过大挤占空间
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(16,11),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=15, pad=20)
plt.tight_layout()
plt.savefig(output_img, bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：{output_img}")
wb.close()
我发现每次运行代码，生成出来的图的布局都不同
标题文字变大
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx"
sheet_name = "Sheet1"
output_img = path+r"\幼儿园家长担心词云.png"
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 15 # 减少词数量，从20改为15，减少拥挤
font = r"C:\Windows\Fonts\simhei.ttf"
min_count = 2 # 只保留出现>=2次的词，过滤掉只出现1次的微小干扰词
# ==========================================================
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"小朋友","是否","不会","老师","问题","不能","情况","同学","能否","孩子","好好","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，过滤频次过低词汇，取top
counter = Counter(valid_words)
# 过滤：只保留出现次数 >= min_count 的词
filter_counter = {k:v for k,v in counter.items() if v >= min_count}
top_list = Counter(filter_counter).most_common(top_n)
print("====筛选后关键词【词，次数】====")
for k,v in top_list:
print(f"{k}：{v}次")
cloud_text = " ".join([item[0] for item in top_list])
# 4、生成词云：关键调参，解决拥挤小字干扰
wc = WordCloud(
font_path=font,
width=1800, # 加宽画布
height=1200, # 加高画布
background_color="white",
max_words=top_n,
collocations=False,
margin=40, # ✨词语之间边距，拉开距离，解决文字堆叠
relative_scaling=0.4, # ✨控制大小词字号差距，避免有的词过小
min_font_size=24, # ✨设置最小字号，彻底消除很小的干扰小字
max_font_size=320 # ✨限制最大字号，防止个别词过大挤占空间
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(16,11),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=35, pad=20)
plt.tight_layout()
plt.savefig(output_img, bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：{output_img}")
wb.close()
每次生成都不一样，批量生成10张，让领导自己选
最终效果
'''
读取问卷星“文本格式”xlsx表，寻找过滤词，提取前20个出现字数最高的词语
家长担心的5个项目
豆包，阿夏
20260805
'''
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx"
sheet_name = "Sheet1"
output_img = "幼儿园家长担心词云"
all=path+fr'\{output_img}'
os.makedirs(all,exist_ok=True)
title_text = "您的孩子要上幼儿园了，您最担心的是什么？"
# K=11，O=15，读取K~O列
start_col = 11
end_col = 15
top_n = 15 # 减少词数量，从20改为15，减少拥挤
font = r"C:\Windows\Fonts\simhei.ttf"
min_count = 2 # 只保留出现>=2次的词，过滤掉只出现1次的微小干扰词
# ==========================================================
for i in range(10):
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"小朋友","是否","不会","老师","问题","不能","情况","同学","能否","孩子","好好","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","担心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，过滤频次过低词汇，取top
counter = Counter(valid_words)
# 过滤：只保留出现次数 >= min_count 的词
filter_counter = {k:v for k,v in counter.items() if v >= min_count}
top_list = Counter(filter_counter).most_common(top_n)
print("====筛选后关键词【词，次数】====")
for k,v in top_list:
print(f"{k}：{v}次")
cloud_text = " ".join([item[0] for item in top_list])
# 4、生成词云：关键调参，解决拥挤小字干扰
wc = WordCloud(
font_path=font,
width=1800, # 加宽画布
height=1200, # 加高画布
background_color="white",
max_words=top_n,
collocations=False,
margin=40, # ✨词语之间边距，拉开距离，解决文字堆叠
relative_scaling=0.4, # ✨控制大小词字号差距，避免有的词过小
min_font_size=24, # ✨设置最小字号，彻底消除很小的干扰小字
max_font_size=320 # ✨限制最大字号，防止个别词过大挤占空间
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(16,11),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=35, pad=20)
plt.tight_layout()
plt.savefig(all+fr"\{output_img}{i:02}.png", bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：f{output_img}")
wb.close()
同理制作第二问
0、没有添加过滤词，原始状态中，有很多“口语化”填写“是否、有没有”
1、复制一份，改名，使用修改过滤词的列表
2、把代码里面的担心改成关心
3、修改读取数据列数
因为上一个代码已经过滤掉“是否、不会、老师、小朋友”等词语，所以现在只有“有没有”一个过滤词
'''
读取问卷星“文本格式”xlsx表，寻找过滤词，提取前20个出现字数最高的词语
家长关心的5个项目
豆包，阿夏
20260805
'''
from openpyxl import load_workbook
import jieba
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
import os
# ======================配置区，修改这里======================
path=r'C:\Users\mhjg2\Desktop\20250728托小幼儿挂牌'
excel_path = path+r"\新生调查问卷_132_132.xlsx"
sheet_name = "Sheet1"
output_img = "幼儿园家长关心词云"
all=path+fr'\{output_img}'
os.makedirs(all,exist_ok=True)
title_text = "您的孩子要上幼儿园了，您最关心的是什么？"
# K=11，O=15，读取K~O列
start_col = 16
end_col = 20
top_n = 15 # 减少词数量，从20改为15，减少拥挤
font = r"C:\Windows\Fonts\simhei.ttf"
min_count = 2 # 只保留出现>=2次的词，过滤掉只出现1次的微小干扰词
# ==========================================================
for i in range(10):
# 1、读取Excel K‑O列所有单元格文本
wb = load_workbook(excel_path)
ws = wb[sheet_name]
all_sentence = []
for row in ws.iter_rows(min_col=start_col, max_col=end_col, values_only=True):
for cell_val in row:
if cell_val is not None and isinstance(cell_val, str):
txt = str(cell_val).strip()
if len(txt) > 0:
all_sentence.append(txt)
full_text = " ".join(all_sentence)
# 2、jieba分词 + 过滤停用词（单字、无意义虚词）
stop_words = {"有没有","小朋友","是否","不会","老师","问题","不能","情况","同学","能否","孩子","好好","","的","和","与","我","会","有","不","很","比较","一点","也","都","就","还是","希望","怕","关心","什么","对","在","要","是","了","吗"}
words = jieba.lcut(full_text)
valid_words = []
for w in words:
w = w.strip()
if len(w)>=2 and w not in stop_words:
valid_words.append(w)
# 3、统计词频，过滤频次过低词汇，取top
counter = Counter(valid_words)
# 过滤：只保留出现次数 >= min_count 的词
filter_counter = {k:v for k,v in counter.items() if v >= min_count}
top_list = Counter(filter_counter).most_common(top_n)
print("====筛选后关键词【词，次数】====")
for k,v in top_list:
print(f"{k}：{v}次")
cloud_text = " ".join([item[0] for item in top_list])
# 4、生成词云：关键调参，解决拥挤小字干扰
wc = WordCloud(
font_path=font,
width=1800, # 加宽画布
height=1200, # 加高画布
background_color="white",
max_words=top_n,
collocations=False,
margin=40, # ✨词语之间边距，拉开距离，解决文字堆叠
relative_scaling=0.4, # ✨控制大小词字号差距，避免有的词过小
min_font_size=24, # ✨设置最小字号，彻底消除很小的干扰小字
max_font_size=320 # ✨限制最大字号，防止个别词过大挤占空间
)
wc.generate(cloud_text)
# 5、matplotlib添加中文标题并保存图片
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(16,11),dpi=150)
plt.imshow(wc, interpolation="bilinear")
plt.axis("off")
plt.title(title_text, fontsize=35, pad=20)
plt.tight_layout()
plt.savefig(all+fr"\{output_img}{i:02}.png", bbox_inches="tight")
plt.close()
print(f"\n✅词云图已输出：f{output_img}")
wb.close()
发给领导
感悟：
1、问卷星的词云图是固定一张，而且不能去掉“过滤词”
2、Python可以生成人工去掉“过滤词”。并批量生成多个样式，便于选择。
扫一扫
291
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
