<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[dream_py的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/dream_py</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; dream_py]]></copyright><item><title><![CDATA[7.8.用excel进行数据分析]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106772640</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106772640</guid><author>dream_py</author><pubDate>Mon, 15 Jun 2020 22:25:02 +0800</pubDate><description><![CDATA[1.
数据——排序

=MAX(D:D)
=MATCH(J2,D:D,0)
=INDEX(C:D,MATCH(J2,D:D,0),1) 
2.
城市和口味做透视表

4.
=FIND("麻",C2,1)
=FIND("辣",C2,1)
开始——筛选（麻、川）
开始——筛选（辣、川）
5.
把口味、环境、服务都做筛选，然后再把城市复制出来，做城市跟总和的数据透视表
6.
先筛选出上海的，再复制到另一个表格，然后按服务进行降序排列，然后再按类型排列。然后
=IF(B2=B3,E2+1,1)
7.
=COUNT(]]></description><category></category></item><item><title><![CDATA[5.时间序列函数]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106735253</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106735253</guid><author>dream_py</author><pubDate>Sat, 13 Jun 2020 17:22:47 +0800</pubDate><description><![CDATA[]]></description><category></category></item><item><title><![CDATA[4.计算统计函数]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106734845</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106734845</guid><author>dream_py</author><pubDate>Sat, 13 Jun 2020 17:21:21 +0800</pubDate><description><![CDATA[1.
=SUMPRODUCT(R2:R3,S2:S3)
2.
=COUNT(S:S)
3.
=MAX(S2:S11)
=MIN(S2:S11)
4.
=RANK(S5,S2:S5)
5.
=AVERAGEA(R2:R9)
6.
=QUARTILE(R2:R5,0)
7.
=STDEV(S2:S8)
8.
=INT()
=ROUND()
9.
=COUNTIFS(R2:R14,"&gt;10",T2:T14,"毕业生")
=SUMIF(T:T,"=应届毕业生",R:R)




...]]></description><category></category></item><item><title><![CDATA[3.逻辑运算函数]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106698485</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106698485</guid><author>dream_py</author><pubDate>Thu, 11 Jun 2020 22:29:26 +0800</pubDate><description><![CDATA[1.
=IF(Q2&gt;10,"高","低")
=IF(Q2&gt;10,IF(T2="应届毕业生","yes","no"),"低")

2.
=ISNUMBER(T2)



]]></description><category></category></item><item><title><![CDATA[2.关联匹配函数]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106697937</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106697937</guid><author>dream_py</author><pubDate>Thu, 11 Jun 2020 21:59:47 +0800</pubDate><description><![CDATA[1.
=VLOOKUP(B2,Sheet1!A:B,2,FALSE)
2.
=INDEX(Q2:T11,2,2)
=MATCH(6,R2:R12,0)或者=MATCH(R2,R2:R12,0)
=INDEX(S2:T7,MATCH(S2,S2:S7,0),1)
3.
=ROW()
=RIGHT(A1,1)
=RIGHT(A2,1)
再做筛选
4.
=OFFSET(U2,1,1,1,1)


]]></description><category></category></item><item><title><![CDATA[1.excel文本清洗]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106694040</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106694040</guid><author>dream_py</author><pubDate>Thu, 11 Jun 2020 18:54:18 +0800</pubDate><description><![CDATA[1.
=FIND("k",P2,1)
=LEFT(P2,Q2-1)
选择性粘贴为数值
=FIND("-",P2,1)
选择性粘贴为数值
=RIGHT(P2,R2)
2.
=CONCATENATE(S2,"k")
=S2&amp;"K"
3.
=REPLACE(U2,1,2,"k")
4.
=SUBSTITUTE(T2,U2,"")
5.
TRIM()


]]></description><category></category></item><item><title><![CDATA[45、 查询选修了全部课程的学生信息（重点划红线地方）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106633584</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106633584</guid><author>dream_py</author><pubDate>Tue, 09 Jun 2020 08:54:41 +0800</pubDate><description><![CDATA[-- 45、 查询选修了全部课程的学生信息（重点划红线地方）
SELECT s_id,COUNT(c_id) FROM score
GROUP BY s_id HAVING COUNT(c_id)=3

-- 方法二适应性更强
SELECT s_id,COUNT(c_id) FROM score
GROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM course)



...]]></description><category></category></item><item><title><![CDATA[44、检索至少选修两门课程的学生学号（不重要）（自己做出）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106633464</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106633464</guid><author>dream_py</author><pubDate>Tue, 09 Jun 2020 08:45:44 +0800</pubDate><description><![CDATA[-- 44、检索至少选修两门课程的学生学号（不重要）
SELECT s_id,COUNT(DISTINCT c_id) FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id)&gt;=2




]]></description><category></category></item><item><title><![CDATA[43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106633366</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106633366</guid><author>dream_py</author><pubDate>Tue, 09 Jun 2020 08:36:09 +0800</pubDate><description><![CDATA[-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）
SELECT c_id, COUNT( distinct s_id) as num FROM score
GROUP BY c_id HAVING num&gt;5 
ORDER BY num DESC,c_id ASC



...]]></description><category></category></item><item><title><![CDATA[35、查询所有学生的课程及分数情况（重点）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106557598</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106557598</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 20:34:48 +0800</pubDate><description><![CDATA[-- 35、查询所有学生的课程及分数情况（重点）
SELECT st.s_id,st.s_name
,MAX(CASE WHEN co.c_name='语文' then sc.s_score else NULL END) as '语文'
,MAX(CASE WHEN co.c_name='数学' then sc.s_score else NULL END) as '数学'
,MAX(CASE WHEN co.c_name='英语' then sc.s_score else NULL END) as '英语']]></description><category></category></item><item><title><![CDATA[40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106546850</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106546850</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 15:02:22 +0800</pubDate><description><![CDATA[-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

SELECT st.s_name,sc.s_score FROM 
student as st
INNER JOIN 
score as sc  on st.s_id=sc.s_id
INNER JOIN 
course as co on sc.c_id=co.c_id
INNER JOIN 
teacher as te ON te.t_id=co.t_id
WHERE te.t_name='张三'
ORDER BY]]></description><category></category></item><item><title><![CDATA[39、求每门课程的学生人数（不重要）（自己做出）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106539046</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106539046</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 09:46:09 +0800</pubDate><description><![CDATA[-- 39、求每门课程的学生人数（不重要）
SELECT c_id,COUNT(c_id) FROM score
GROUP BY c_id 



]]></description><category></category></item><item><title><![CDATA[38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）(自己做出)]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106538931</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106538931</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 09:41:30 +0800</pubDate><description><![CDATA[-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
SELECT st.s_id,st.s_name,sc.s_score,co.c_id,co.c_name FROM
student as st
INNER JOIN
score as sc on st.s_id=sc.s_id
INNER JOIN
course as co on sc.c_id=co.c_id
WHERE co.c_id='03' AND sc.s_score&gt;80



...]]></description><category></category></item><item><title><![CDATA[37、查询不及格的课程并按课程号从大到小排列(不重点)(自己做出)]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106538670</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106538670</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 09:30:50 +0800</pubDate><description><![CDATA[-- 37、查询不及格的课程并按课程号从大到小排列(不重点)
SELECT * FROM score
WHERE s_score&lt;60
ORDER BY s_id ASC



]]></description><category></category></item><item><title><![CDATA[36、查询课程成绩在70分以上的姓名、课程名称和分数（重点）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106538564</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106538564</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 09:26:59 +0800</pubDate><description><![CDATA[-- 36、查询课程成绩在70分以上的姓名、课程名称和分数（重点）
SELECT st.s_name,co.c_name,sc.s_score FROM 
student as st
INNER JOIN
score as sc on st.s_id=sc.s_id
INNER JOIN 
course as co on sc.c_id=co.c_id
WHERE sc.s_score&gt;70



]]></description><category></category></item><item><title><![CDATA[34、查询课程名称为数学，且分数低于60的学生姓名和分数（不重点)（自己做出）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106537775</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106537775</guid><author>dream_py</author><pubDate>Thu, 04 Jun 2020 08:34:13 +0800</pubDate><description><![CDATA[-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
SELECT st.s_name,st.s_id,co.c_name, sc.s_score FROM 
student as st 
INNER JOIN 
score as sc on st.s_id=sc.s_id
INNER JOIN
course as co on sc.c_id=co.c_id
WHERE co.c_name='数学' and sc.s_score&lt;60



...]]></description><category></category></item><item><title><![CDATA[33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106479503</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106479503</guid><author>dream_py</author><pubDate>Mon, 01 Jun 2020 18:42:16 +0800</pubDate><description><![CDATA[-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
SELECT c_id,AVG(s_score)'mean' FROM score
GROUP BY c_id 
ORDER BY mean ASC,c_id DESC



]]></description><category></category></item><item><title><![CDATA[32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（自己做出)]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106479286</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106479286</guid><author>dream_py</author><pubDate>Mon, 01 Jun 2020 18:32:51 +0800</pubDate><description><![CDATA[-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要)
SELECT st.s_id,st.s_name,AVG(sc.s_score)'mean' FROM 
student as st
INNER JOIN
score as sc on st.s_id=sc.s_id
GROUP BY s_id HAVING mean&gt;=85 




]]></description><category></category></item><item><title><![CDATA[31、查询1990年出生的学生名单（重点year）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106479208</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106479208</guid><author>dream_py</author><pubDate>Mon, 01 Jun 2020 18:26:18 +0800</pubDate><description><![CDATA[-- 31、查询1990年出生的学生名单（重点year）
SELECT * FROM student
WHERE YEAR(s_birth)='1990'




]]></description><category></category></item><item><title><![CDATA[29 、(自己做出）]]></title><link>https://blog.youkuaiyun.com/dream_py/article/details/106430506</link><guid>https://blog.youkuaiyun.com/dream_py/article/details/106430506</guid><author>dream_py</author><pubDate>Fri, 29 May 2020 18:04:09 +0800</pubDate><description><![CDATA[-- 29 查询名字中含有"风"字的学生信息（不重点）
SELECT * FROM student
WHERE s_name like '%风%'



]]></description><category></category></item></channel></rss>