【办公类106-02】20260805问卷星新生家长调查问卷（deep seek制作python词云图

背景需求

过了一周

把文本的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可以生成人工去掉“过滤词”。并批量生成多个样式，便于选择。