Apolloh 函数和图表原创贴结集 [20060115更新]

apolloh

以下是本人在EXCELHOME的主要的答题案例和技术文章清单。(分为【主题原创】和【回帖原创】两个部分，按时间逆序排列。)

更多文章请见Apollo Huang's Blog


【主题原创】部分


[RANK的三维引用和COUNTIF的四维引用] 2006-01-15
本例中需要一次求出9个学生9次考试成绩中最好的三次成绩和的最优成绩。这是OFFSET多维引用的一个典型引用，其中RANK三维引用对每个学生的9次成绩单独排列大小，再用COUNTIF的四维引用来将排列名次修正成无并列排名。RANK的三维引用和COUNTIF的四维引用都是最新原创的用法。值得一提的是这两个函数的参数在这个例子中都支持隐含交叉计算。
[将三列文本数组转换成一列数组]　2005-06-29
多列文本数组转换成一列文本数组，曾经是公认的难题。此文中提及CHOOSE+LOOKUP的方法已克服这一难题，为解决类似的问题提供一个崭新的思路。

[双条件求和的18种方法--分段数据计算的讨论]　2005-5-23
这是个竞赛题的总结(其中2种方法为原创)。以时间为序列的数据表是我们较为常见的数据表形式。以往针对此类表格的多条件汇总中，我们一般都用数组公式来实现。但随着时间的增长，数据表的数据量会不断增大，数组公式的运算速度问题就会突现出来。此文中提及的SUMIF+OFFSET+MATCH的多单元格数组公式的方法，将庞大的数据量按时间分段计算，大大提高此类运算的计算效率，可以解决速度瓶颈问题。
[浅谈引用函数产生的多维引用及其应用]　2005-5-15
引用函数OFFSET和INDIRECT多维引用是非常实用的技术，它可以使许多原本需要大量辅助区域公式协助才能解决的问题变得轻松易解。此文从对引用的认识出发，带领大家认识什么是一维、二维、以及三维以上的多维引用及其工作原理，并列举了大量的例子来说明COUNTBLANK()、COUNTIF()、D数据库函数()、N()、RANK()、SUBTOTAL()、SUMIF()、T()等函数结合OFFSET/INDIRECT的多维引用的妙用。此文揭开了原来只为少数人所了解，大多数人觉得迷糊的"引用函数产生的多维引用"的神秘面纱，使得多维引用成为EXCELHOME函数爱好者们的常用技术之一。
[交集在并集中的补集问题--数组的合并、追加与置换]　2005-4-13
这是一个竞赛题的总结(其中1种方法为原创)。单列单行数组合并在EXCELHOME论坛上早有讨论，但利用CHOOSE或IF进行多列多行的数据合并、追加以及数组中部分行列的置换还是第一次被提及。这一技术是后来"多列文本数组转换成一列文本数组"的基础。
[多行多列不重复值的新探讨]　2005-4-11
传统的函数筛选不重复值的方法一般使用MATCH()=ROW()的模式，由于MATCH只能在一行或一列中查找，遇到二维区域的情况，一般是先将二维区域转化为一列，然后再用一列求不重值的方法来实现不重复值的筛选。此文中的例子不用MATCH，也不用先将二维区域转化为一列，其使用FREQUENCY+COUNTIF函数，利用COUNTIF获得数据的排序大小值，再利用FREQENCY可操作二维数值数组的特点对数据的排序大小值求唯一值，并取得对应的位序号，从而进一步求得不重复值。
[筛选状态下的分类汇总--多条件筛选不重复值]　2005-4-7
这是个竞赛题的总结(其中1种方法为原创)。涉及三个方面的综合应用，SUBTOTAL+OFFSET的多维引用计算，多条件筛选不重复值的方法，数组为结果的公式在图表中的应用。其中确定了一个简易的多条件筛选不重复值的模式，用IF(ISNUMBER(1/(MATCH(数据,IF(多条件判断,数据),)=记录号)),记录号)来判断符合多条件的记录号。
[如何求最大值的和--数据库函数多维计算、用函数对数据表进行排序]　2005-3-24
这是个竞赛题的总结(其中1种方法为原创)。此文详细阐述了DMAX+OFFSET的多维引用计算和使用函数对数据表进行排序的问题。着重分析在乱序排列的数据表中求各分类项目对应之极值的思路。
[先进先出法Excel能实现吗？--Excel函数实现类似数据库指针功能]　2005-3-17
这是个竞赛题的总结(其中2种方法为原创)。存货计算中的先进先出法在数据库中也是较难处理的。此文通过对先进先出法FIFO的5种解决方案的详细分析，论述了使用Excel函数较为轻松地实现类似数据库指针功能的思路和方法。同时还验证了在OFFSET函数的HEIGHT和WIDTH参数中可以使用负整数。
[如何求这样的余额--关于公式优化和报表结构设计的讨论]　2005-3-3
这是一个竞赛题的总结(其中1种方法为原创)。题目为财务领域常需涉及的应收和预收款计算问题，此题得到了３种答案，但它们在计算效率上存在较大的差异。通过此例，本文试图说明如何对公式进行优化以及如何设计合理的报表结构来提高公式的计算效率。
[餐费分摊问题--关于内存数组的讨论]　2005-2-25
这是个竞赛题的总结(其中1种解法为原创)。内存数组公式（计算结果为数组的公式）在函数高级应用及图表应用中常被提及。此题用十几种方法解决一道多人用餐记录的餐费分摊问题。其中有5种添加辅助区域的方法，3种单个单元格数组公式的方法，5种多单元格数组公式的方法，1种内存数组公式的解法。通过对各种方法的比较分析以及数组测试，阐述了"内存数组公式"这个一个概念及意义，以及多单元格数组公式和"内存数组公式"的区别和联系。
[随机密码生成器函数版]　2005-1-7
一个可以自定义包含字符的随机密码生成器。
[另类的英文名分割法]　2005-1-4
首先使用SUBSTITUTE函数对文本进行分割，然后用EVALUATE宏表函数将其转换成数组，再用INDEX取其中指定位置元素，来最终实现英文名的姓、名的分割的方法，此方法还可以用于文本表示的加法和乘法运算中。
[数量金额变动图]　2005-1-13
一个实用的可以直观体现数量和金额关系的变动图表。
[比较分析图]　2005-1-7
一个实用的可以智能判断差异方向并自动补差的差异分析图表。
[四舍六入并保留指定有效位数]　2004-11-7
数字修约中保留有效性问题，要求数据在四舍六入五单双的情况下同时保留有效位数，本文提供了工作表函数和自定义函数两种解决方案。



【回帖原创】部分


[对列、堆栈、混合排序] 2006-01-06
这个是一个人事问题，可以用在排班上。例子中不同员工分别需要值班1-N次，要求并按照对列、堆栈、对列堆栈混合的方式进行排班。

[一维数组大小调整] 2005-12-24
剔除一维数组中不需要的元素，返回一个剩余元素的数组，并不改变元素的原由排列顺序。

[Sign+Choose在多个正负值方向为条件的判断中的应用]　2005-07-12
SIGN函数常常被大家忽略了，此例中说明了在多个正负值方向为条件的判断中，使用SIGN来简化公式的方法。
[计算实际工作时间]　2005-07-12
这是一个复杂的时间计算问题。在既要排除周末又要指定有效工作时间段如8：30-12：00，13：00-17：00的应用中，如何计算有效的工作时间呢？此例提供了一个解决方案。
[方格网土方简易计算表]　2005-07-11
建筑行业工程预决算中，常常需要计算方格网土方的问题，此应用提供了一个简易的表格，可以轻松地输入和计算土方。
[从明细数据中统计分类项及频率数的图表]　2005-07-08
用函数公式对明细数据进行分类汇总或频率统计产生内存数组结果，作为动态图表中的系列引用的案例。
[复制工作表时实现表名的相对引用]　2005-07-07
在复制工作表时，Excel公式不能实现表间的相对引用，此例利用宏表函数GET.WORKBOOK模拟实现了这一应用需求。
[PERCENTRANK在库存ABC分析中的应用]　2005-07-07
库存ABC分析法是常用的存货分析方法，此例中用PERCENTRANK轻松实现了对所有存货的ABC分类。
[按不重复项目分系列作图]　2005-06-24
在项目无序穿插排列的表中，分离出各项目的数据分别作为图表一个系列引用来作图，直接用内存数组公式实现，不使用辅助区域。

[SUMIF多维引用应用于BOM计算]　2005-5-18
SUMIF+INDIRECT多表多维引用，应用于多个不同产量不同BOM的产品的材料消耗数量的统计。
[求各组最高值的平均值]　2005-4-28
有序排列的各组中如何用一个公式求得各组中最高值的平均值，主要应用SUBTOTAL(4,OFFSET())多维计算的方法来实现。
[相邻的不重复数字]　2005-4-22
求出如348这样一个数字中每个数字的相邻数且彼此不重复的数字，结果为2579。

[任意级次的分级小计]　2005-2-27
此例使用MATCH+OFFSET进行定位，对一个树状排列的分级项目使用一个可复制填充的公式进行分级求和计算。

[指定记录数符合条件的记录]　2005-2-23
用一个常量数组指定各类别的记录数，要求返回各类别指定记录数的最早记录。此例采用数组运算的方法将符合条件的记录通过数组计算的方式进行定位，然后再用查找函数将其筛选出来。
[精确内插值查找]　2005-2-18
使用OFFSET+MATCH对给定条件值对应的最小范围进行定位，然后再用TREND函数返回内插值。
[颜色区分图表中跨年度的数据]　2005-2-17
在以月份为变量的动态图表中，跨年的不同段图表数据使用不同颜色区分。
[X轴不同段对应不同的图表类型]　2005-2-7
此例中X轴分别由以前年度和本年度的各月组成，在以前年度使用柱形图、而本年度的月份数据使用折线图，同时画一条代表控制指标的直线贯穿其中。
[用条形图制作的多个层叠的Y轴]　2005-2-2
我们常见到使用XY散点图来模拟坐标轴的应用方法，此例中采用另一种方法用条形图来模拟多个Y轴，实现同Y值范围段数据可分别绘制在一个层叠的图表的不同层面中进行趋势比较。
[系列变动图表]　2005-1-25
结合窗体控件，用函数和辅助区域制作能够变动系列的图表。这是一张可选择厂商和产品类别为不同视角的纺织品价格走势图。
[Frequency求第N大的唯一值]　2005-1-25
在类似{5,5,6,7,8,9,9,10,10}数组中返回第二大的数值9，用LARGE(DATA,2)返回的是10。这里使用FREQUENCY去除重复值，轻松实现了求第N大的唯一值。
[波士顿矩阵法图]　2005-1-21
以平均毛利率和平均销售额为坐标原点，将各项数据以波士顿矩阵图的方式表达出来。
[不等距等差值和等距不等差值的Y轴-条形图]　2005-1-11
此例中用条形图来模拟不等距等差值标签或等距不等差值标签的Y轴。同理可以使用柱形图来模拟X轴。如果要同时模拟X和Y轴，一般采用XY散点图的方法。
[多表汇总--总表在工作簿中的任意位置]　2005-1-9
使用SUMIF+INDIRECT多维引用计算，进行多表汇总，其中汇总表任意移动不会影响到数据的计算。
[Mmult在多条件筛选中的应用]　2004-12-25
Mmult的一个例子。
[在用分隔符隔开的数据中筛选符合条件的数据]　2004-12-23　
在一个带有分割符的数据中，筛选符合条件的记录数据的特例。
[帐龄计算]　2004-12-23
帐龄计算是财务领域中的常见应用，本例提供了一个简单有效的方法来实现它。

[移动加权平均法]　2004-12-23
在分别记录的入库单和出库单列表中，实现出库单中的移动平均单价的计算。



[ 本帖最后由 apolloh 于 2008-12-6 14:39 编辑 ]

布衣铁剑

T黄大哥

感谢您把自己的学习历程分享给大家.

为了纪念这些帖子和学习您的帖子.

我决定摘香沐浴半个月不再发贴,以表示对您的敬重!

天啊,今天又增加了这么多,凭我的智商恐怕要学3年. ：（

只拣自己用的着滴学习先!

Excel知识无止境,黄大哥的学识更无止境!

[此贴子已经被作者于2005-5-21 20:34:23编辑过]

shuiyuan

这样大家就方便了。谢谢共享

ab007

强烈的支持和感谢！！！

[em24][em26][em25][em27]

wuaza

黄大侠真是厉害，又一位大师级的人物诞生了！

chenjun

汇总得不错！

xuanhou

谢谢楼主费心了.

DING...

lgcmeli

是否文字介绍一下apolloh的在EXCELHOME的成长经历呢？

apolloh

谢谢chenjun老大鼓励！

to lgcmeli：需要什么文字呢，从帖子里是可以找到痕迹的。：）

yigepure

以下是引用apolloh在2005-5-24 8:10:00的发言：

谢谢chenjun老大鼓励！

to lgcmeli：需要什么文字呢，从帖子里是可以找到痕迹的。：）

先收藏起来.慢慢研究.

呵呵.老兄真是厉害.学习中....