SurDA

这次五一小长假，一个冲动在老家南昌订了套三居的房子。

房子不是很大，算是小户型。建筑面积96.62平，套内面积约81平。五一期间享房价86折，去楼盘看了两次，晚上在网上看了相关情况，第二天就付订金了。哈哈，估计我是【最快买房者】，仅一天就确定了。

回到上海后就开始研究贷款的事情，时间比较紧，13号前就要回去签购房合同了。

贷款主要看利率、和据自身情况制定月还款额、预计贷款金额等，虽然目前网上有很多在线【房贷计算器】，但本人还是比较偏爱Excel.所以今天分享一款【Excel 版个人住房贷款计算器】。

Read More…

今天2012年4月2日,清明小长假中.距离上一次更新博文差不多三个月了,主要是工作太忙.哈哈.

如常签看博客户评论,有位Aleo仁兄留下非常之诚恳的问题.如下:

能否请教啊，我是一个工作簿里多个表，表一的B列有关键字，比如ID号之类的，而表2则是对这些ID号的详细说明，两个表ID号的排列顺序是不一样的，我要怎么做到，当我点击表一B列中的任何一个ID号时，就能跳到表2有相同ID号的那个单元格上去呢？我原本用的是vlookup，但是因为表格中数据很多，用了这个公式后，运行的非常非常慢。请指点指点啊！！

补充说下，表的大致格式如下：表1，A列是顺序号，B列是ID号，表2的D列是对应的ID号，数值是和表一的一样，唯独顺序不一样。我想点击表一的B列里任何一个ID号时，可以转到表2中D列有相同ID号的那行。如何做到链接，最好是批量的，因为我有几万行数据，一个一个添加要人命哦

此篇博文,surda主要解决仁兄的问题——如何使【Excel超链接至目标单元所在行】？

Read More…

记得前面有提到运用复选框、滚动条制作Excel动态图表，今天Surda再分享一例运用下拉框制作Excel动态图表。

问题：为何要制作动态图表？

答案：有时候我们想运用图表展示大量数据，会发现图表很乱，根本没有表现力。那动态图表在这个时候就大有用途了。

我们先来看一个实例。

公司想用图表展示2011年度，各营业部计划业绩达情况。常规的图表如下，你看图表能得出什么结论？不够一目了然吧。

Read More…

在一些老外制的图表中时常会看到一些特殊的图表,形如下图.老外称之为Square pie chart,中文暂且叫做方块百分比图.

此种图表好处是可以直观的展示百分比数值,其实Excel很容易实现此等"伪图表",就像前文介绍的

Read More…

Excel要用颜色点缀,但是也不能使用太多的颜色.
在文档中使用太多的色彩，不仅会造成读者注意力的分散，还会混淆要传达的信息点。为了避免这样的结果，您必须限制使用的色彩数量，巧妙地运用色彩以突出想要强调的部分。

那如何配色才能使效果更佳？

从John Walkenbach牛人那发现一款Excel配色评分程序，分享示人。以供后效！

Read More…

Excel 计算连续相同数据出现的频次?标题应怎么理解?且听Surda慢慢道事……

上周一一哥们通过Blog知道我QQ,并加为好友.发消息求助一问题:"有一列数据,形如{1;1;1;2;1;2;2;1;1},求最大连续相同数据出现的频次?"

如本例中" 1 "连续出" 3 "次,他要的答案是 " 3 " .

后来聊天才知道,这哥们是玩福彩"快乐8",大小单双,找找看最大相连会达到多少,以了解最坏的情况?

计算结果及公式如下:

连续出现频次最多的相同数据:

公式1=INDEX(A:A,MODE.MULT(IFERROR(LOOKUP(ROW(2:26),IF(A2:A25<>A3:A26,ROW(3:26))),1)))

公式2=INDEX(A:A,MODE(LOOKUP(ROW(2:25),IF((A1:A24<>A2:A25)+(ROW(1:24)=1),ROW(2:25)))))

以上两公式皆为数组公式,以CTRL+SHIFT+ENTER 结束公式输入.

Read More…

记得前面分享过一篇Excel去重复项,也就是取不重复值.本文再分享如何统计不重复值的个数.继续延用上文实例.

如上图:

A1:A15为原始数据区域,B,C,D,E列按各自己要求从原数据区域中获取了不重复值,那如何统计不重复值的个数呢?这里给出三种常规解决方案.

一,=SUM(1/COUNTIF(A1:A15,A1:A15))  数组公式

析:COUNTIF(A1:A15,A1:A15)得到原数据区域数据出现次数形成的数组｛2,2,2,2,3,2,3,2,1,1,1,1,2,3,2｝

1/{2,2,2,2,3,2,3,2,1,1,1,1,2,3,2},2个重复的,每个占1/2,求和为1.使重复值只计算一次!

二,=COUNT(1/FREQUENCY(A1:A15,A1:A15))

析:利用Frequency(数据,数据)统计数值的分布频率{2;0;2;2;2;3;0;0;1;1;1;1;0;0},第一次出现的数字位置返回数字出现个数,而第2次出现就返回0,因此只要统计非0的个数即可.故此公式也可改写为:

=SUM(N(FREQUENCY(A1:A15,A1:A15)>0))

三,=SUM(N(MATCH(A1:A15,A1:A15,0)=ROW(A1:A15)))

析:Match精确查找每个数据在数据区第一次出现的位置{1;2;3;4;5;6;5;6;9;10;11;12;4;5;4},并与其对应的行号位置比较,因为只有第一次出现的位置才会一致,所以统计的是不重复个数.

今天再分享一例 Excel 取单元格连续数字 公式 .

此公式不受数字起始位置限制,无论是在开头,中间或结尾均可自动取的单元格中的数字.公式如下:

=LOOKUP(9E+307,–MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

CTRL+SHIFT+SHIFT 数组公式

公式解读:

1. 起关键作用的是蓝色部分公式,其功效为定位数字开始位数,我称其为"样板公式".
2. LOOKUP(9E+307,…… 遍历使用数组生效
3. 9E+307 为 Excel 认为的最大值

Read More…

今天分享一个 Excel 从身份证提取出生年月日的小公式.

首先我们很清楚的知道身份证的构成有两种:一种是18位(6+8+4),中间8位是出生年月日,第二种是15位(6+6+3),中间6位是出生年月日.

OK,小二,上公式.

B2=IF(LEN(A2)=18,MID(A2,7,8),19&MID(A2,7,6))

注:此公式通用期至2016年.

因 360 与 QQ 事件,在卸载360+QQ过程中造成了本本的一点愉快.是乎于上周六为本本换了个心脏,Office 2010 亦无法再次激活,缘因 MAK 激活码已使用完.

好在当初激活安装Office 2010备份了启动信息,还原即可无需再次激活.以后重装多少次系统也不怕.现将方法分享如下:

一.备份OFFICE 2010 启动信息

成功激活Office 2010 后,分别备份

• C:\Program Files\Common Files\Microsoft Shared\OFFICE14\Office Setup Controller\pkeyconfig-office.xrm-ms
• C:\ProgramData\Microsoft\OfficeSoftwareProtectionPlatform\tokens.dat

Read More…

最近经常需要判断手机号是否为上海用户,为此特地从Access数据库中查询出来,生成Excel版的.(文末附下载)

目前网络上鲜有此等离线手机号段数据库.不敢私用,共享出来,以供后效!

相关说明:

• 数据库包含上海移动、上海联通、上海电信三大运营商号码归属
• 号段以6位存储（知道手机前六位，归属地也就知道了）
• 原始Access为6月份数据,部分电信号段归属可能有缺失(Surda已手工增加部分）
• 文件名:"上海手机号段.xls",Excel表名:"查询1"

Read More…

例行查阅Google/Reader博友的博文,[睿智小超人的博客]在Word 2010中输入=lorem(),太神奇引起Surda注意.

Quoto:

“打开Word 2010,输入=lorem(),回车,马上会出现一段你可能永远也看不懂的文字.=lorem()还有参数呢，例如可以输入=lorem(3,4),至于为什么会这样，那就要问微软了.”

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar ultricies, purus lectus malesuada libero, sit amet commodo magna eros quis urna.

Nunc viverra imperdiet enim. Fusce est. Vivamus a tellus.

Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Proin pharetra nonummy pede. Mauris et orci.

Read More…