经常会被问到，“数据分析需要学习什么技能？”，“针对实际的业务场景，如何使用数据分析工具去分析？”基于此作者总结数据分析面试常用的问题，面试内容包括技能应用篇：EXCEL、SQL、Python、BI工具等，业务思维篇：常用的数据分析方法与业务思维等。

其中大部分问题点，没有绝对标准答案，所有问题点都是为了解决问题，大家如果有更好的问题答案，也可以提出，对于其中的问题也可以提出，共同解答，欢迎点赞、转发、评论，下面一起来学习。

本文讲解内容：Excel、SQL、Python面试必备

适用范围：多种数据分析实用技巧

Excel篇

1、Excel将多行数据变为一列

Excel将多行数据放到一列中，如下包含1月到12月共计12列数据，有5行数据，将以下多列数据聚合到一列中。

框选数据，点击数据选项卡下的从表格。

在创建表选项下点击确定。

进入Powerquery界面，选择第一列数据，在转换选项卡下选择逆透视列中的逆透视其他列选项卡。

即可将多列数据聚合到一列，从而形成多行数据。

在开始选项卡下点击关闭并上载功能。

如下即为数据转化的前后对比，将多列数据聚合为一列数据。

2、Excel将一行数据拆分为多行

Excel将一行数据拆分为多行，如下有一行包含多个城市的数据。

选择数据，点击数据选项卡下的从表格。

在创建表选项下点击确定。

选择数据，在开始选项卡下点击拆分列按分隔符。

因为示例数据使用点分隔，所以选择自定义分隔符，拆分位置为每次出现分隔符时，拆分为行，点击确定。

点击关闭并上载即可将数据结果加载至Excel表格中。

如下即为数据转化的前后对比，将一行数据拆分为多行。

3、VLOOKUP高级用法

1）跨表查找

如下是原始数据，使用VLOOKUP跨表查询功能。

使用VLOOKUP+COLUMN函数实现多列同时跨表查询。

=VLOOKUP($A3,Q1!$A$1:$D$9,COLUMN(B:B),0)

2）跨多表查找

INDIRECT函数用于返回由文本字符串指定的引用。

=VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,0)

3）跨多表查找（格式不一致）

这里不仅使用INDIRECT函数，而且使用MATCH函数来返回"销量"在单元格区域中的位置。

=VLOOKUP($A2,INDIRECT(B$1&"!A:G"),MATCH("销量",INDIRECT(B$1&"!1:1"),0),0)

SQL篇

1、每天的日活数及新用户占比

如下是用户行为日志表，求每天的日活数及新用户占比。

由于跨天都记为该用户活跃过，所以使用union建立一个用户活跃日期表。

select uid,date(in_time) as dt from tb_user_log union select uid,date(out_time) as dt from tb_user_log

union 可以自动去重，此时我们得到了一个包括跨天的用户活跃日期表，然后使用窗口函数，算出每个用户的首登日期。

select uid,dt,min(dt) over (partition by uid) as first_in from ( select uid,date(in_time) as dt from tb_user_log union select uid,date(out_time) as dt from tb_user_log ) t1

由此便得到一个有用户活跃日期dt和用户首登日期first_in的表，将此表命名为temp，接下来使用此表查询，根据日期分组，计算每日的所有有记录的用户作为每日活跃用户，而如果用户活跃日期dt和用户首登日期first_in相等则记为新用户，使用sum累加。

select dt, count(distinct uid) as dau, round(sum(if(dt=first_in,1,0))/count(distinct uid),2) as uv_new_ratio from temp group by dt order by dt

完整代码如下：

with temp as ( select uid,dt,min(dt) over (partition by uid) as first_in from ( select uid,date(in_time) as dt from tb_user_log union select uid,date(out_time) as dt from tb_user_log ) t1 ) select dt, count(distinct uid) as dau, round(sum(if(dt=first_in,1,0))/count(distinct uid),2) as uv_new_ratio from temp group by dt order by dt

2、2021年11月每天新用户的次日留存率

如下是用户行为日志表，求2021年11月每天新用户的次日留存率。

用组合查询将in_time和out_time视为同一时间列，并用union去重，接着使用窗口函数lead()获得每个用户下次登录日期，row_number()获得每个用户本次登录的排序，仅保留新用户首次登录日期的记录，按11月的日期分类，计算留存率。

select dt, round(sum(if(datediff(next_dt,dt)=1,1,0))/count(distinct uid),2) uv_left_rate from (select uid, dt, lead(dt,1) over(partition by uid order by dt) next_dt, row_number() over(partition by uid order by dt) dt_rank from (select uid, date(in_time) dt from tb_user_log union select uid, date(out_time) dt from tb_user_log) m ) n where dt_rank = 1 and date_format(dt,%Y-%m) = 2021-11 group by dt order by dt

Python篇

1、列表推导式

具体地Python列表推导式可以分为以下几种：一般形式、带if的列表解析式、带if...else的列表解析式。

1）一般形式

[expression for i in iterable]

依次返回列表numbers中的内容。

#一般形式 numbers = [1,2,3,4,5,6] [print(i) for i in numbers]

2）带if的列表解析式

[expression for i in iterable if...]

返回列表numbers中大于4的内容。

#带if的列表解析式 numbers = [1,2,3,4,5,6] [i for i in numbers if i >= 4]

3）带if...else的列表解析式

[expression if...else... for i in iterable]

返回列表numbers中数字的奇偶性。

#带if...else的列表解析式 numbers = [1,2,3,4,5,6] ["偶数" if i % 2 == 0 else "奇数" for i in numbers]

2、map、apply、applymap用法

首先构建一个数据集。

import pandas as pd import numpy as np dates = pd.date_range(20230301, periods=6) df = pd.DataFrame(np.random.randn(6,4), index=dates, columns=list(ABCD)) df#增加一列数据 df=df.assign(tag=(df[D]>0).map({True:H,False:L})) df

1）map函数

map将数据逐个当作参数传入字典或者函数中，返回映射之后的值；最后映射结果组成新的Series。

#使用lambda函数，A列乘以10 df[A].map(lambda x:x*10)#tag列，把H换成大于0，L换成小于0 df[tag].map({H:▲0,L:▼0})

使用自定义函数，这里map只接受一个参数，即传入的x。

#使用函数实现tag列，把H换成大于0，L换成小于0 def HL_map(x): if x == H: return ▲0 elif x == L: return ▼0 df[tag].map(HL_map)

2）apply函数

相较于map，apply工作原理类似，区别是能够传入更加复杂的参数，接受多个参数。

#函数累计 df.apply(np.cumsum)df.apply(lambda x: x.max() - x.min())#函数求极差#A列乘以100除以2 def data_apply(x,multiple,bias): return x * multiple / bias df[A].apply(data_apply,args=(100,2))

对DataFrame而言，apply中的参数axis=1表示对行遍历，axis=0表示对列遍历，对于传入apply的行（或列）数据，以Series格式传入指定函数，返回对应结果。

def DATA_B_apply(x): if (x[B]>0)&(x[tag]==H): return B_H elif (x[B]<0)&(x[tag]==L): return B_L else: return x[B] pass df.apply(DATA_B_apply,axis=1)

3）applymap函数

对DataFrame中的每个单元格执行指定函数的操作。

#同乘100 df.iloc[:,0:4].applymap(lambda x : x*100)#保留两位小数 df.iloc[:,0:4].applymap(lambda x : %.2f % x)

三年互联网数据分析经验，擅长Excel、SQL、Python、PowerBI数据处理工具，数据可视化、商业数据分析技能，统计学、机器学习知识，持续创作数据分析内容，点赞关注，不迷路。