ftcrawler / excel Goto Github PK
View Code? Open in Web Editor NEWsome skills for Excel
some skills for Excel
最近在学习回归分析,准备把过去几年的数据捣鼓出来做做分析。想到之前做月报、年报也都是用excel临时写的公式,这些公式兼容性还不好,有新数据进来经常需要大动。于是想着如何利用复杂一点的公式做一个长期的模板,方便预测和检验工作。
公司的数据都是在数据库里跑出来的,有时候因为日期不是自然月,数据量大不好跑太多数据等原因,我们经常需要一个月一个月数据跑。而且每个月并不是每个渠道都有数据,有可能以前的渠道现在没了,或者现在又增加了新的渠道。这就导致我们得到的数据源大多是下面这样的:
而我们希望针对成单数逐月排列进行分析,比如下面这样:
要实现上面那种格式,我们可能需要这样两个步骤:
1.取A列/E列/I列...的城市去重,作为第一列;
2.取各城市各月的成单数排在相应的位置。
第一步,
首先需要复制所有数据,选择“选择性粘贴”-“转置”,然后筛选,选出“城市”,清空月份列(数据前面需保留列空白列),再复制,“选择性粘贴”-“转置”,得到下图:
利用透视表向导,ALT+D、P(按ALT+D,松开D再按P)快捷键或“插入”选项卡选择数据透视表和数据透视图向导(如果没有,在文件-选项-自定义功能区-不在功能区的命令找到并添加到任意选项卡中即可)调出“数据透视表和数据透视图向导”,如下图:
选择“多重合并计算数据区域”——“下一步”——“创建单页字段”——“下一步”——选定区域——“下一步”——“新工作表”——完成
得到透视表,取消所有勾选,将“值”放在“行”内即可得到所有城市:
查看源数据可以知道,可以用vlookup函数,但如果月份数据过多,就得一次又一次地改公式,繁琐又可能手误。
一步到位的方式只是将vlookup的查找范围每右移一个单元格,整个区域右移3个单元格,这就是OFFSET函数的功能了。
公式为:B2=IFERROR(VLOOKUP($A2,OFFSET(Sheet15!A:C,,3*COLUMN()-2 * 3),3,0),0)
(sheet15是原始数据源表)
再将公式往右往下拖动填充即可。
后续更新新的月份数据,
只需要匹配查找是否有新的城市,有则添加在下面;
再在右边新增新月份列,拖动公式即可。
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.