教程 (教程是什么意思)

本文目录导航：

• 教程 Excel性能优化方法（四）
• 几种罕用最优化方法
• 教程 Excel性能优化方法（三）

教程 Excel性能优化方法（四）

对一组延续的行或列经常使用 INDEX

还可以从一个查找操作前往多个单元格。

要查找多个延续的列，可以经常使用数组公式中的 INDEX 函数一次性前往多个列（经常使用 0 作为列号）。

还可以经常使用 INDEX 函数一次性前往多个行。

这将从之前的 MATCH 语句创立的存储行前往列 A 到列 J

经常使用子集区域启动多索引查找

在大型上班表中，或许经常须要经常使用多个索引来启动查找，例如查找某个国度/地域的产品量。

为此，可以衔接索引并经常使用衔接的查找值口头查找。

但是，有两个要素会造成其效率低下：

计算查找的子集区域理论更有效（例如，经过查找国度/地域的第一行和最后一行，而后在该子级区域外调找产品）。

思考三维查找选项

除行和列外，若还要查找要经常使用的表，则可以经常使用以下方法，这些方法重点处置如何使 Excel 查找或选用表。

假设要查找的每个表（第三维）都存储为一组已命名结构化表、区域称号或存储为示意区域的文本字符串表，则可以经常使用 CHOOSE 或 INDIRECT 函数。

经常使用 CHOOSE 和区域称号是一种有效方法。

CHOOSE 是无法变函数，但最实用于相对较少的表。

此示例灵活经常使用 TableLookup_Value 来选用要用于查找表的区域称号 ( TableName1, TableName2, ... )。

上方的示例经常使用 INDIRECT 函数和 TableLookup_Value 灵活创立要用于查找表的上班表称号。

这种方法的好处是便捷，能够处置少量表。

由于 INDIRECT 是可变的复线程函数，所以即使没有数据出现变动，查找也是每次计算时计算的复线程。

经常使用此方法速度较慢。

还可以经常使用 VLOOKUP 函数查找要用于表的上班表称号或文本字符串，而后经常使用 INDIRECT 函数将结果文本转换为区域。

另一方法是将一切表聚合到一个大型表中，该表蕴含另一标识各个表的列。

而后可以经常使用前面的示例中所示的多索引查找方法。

经常使用通配符查找

MATCH、VLOOKUP 和 HLOOKUP 函数准许你在按字母顺序齐全婚配的状况下经常使用通配符 ? （任何单个字符）和 *（没有字符或恣意数量的字符）。

有时，可经常使用此方法防止多个婚配项。

优化数组公式和 SUMPRODUCT

数组公式和 SUMPRODUCT 函数配置弱小，但肯定审慎处置。

单个数组公式或许须要屡次计算。

优化数组公式计算速度的主要在于，确保尽或许增加在数组公式中计算的单元格和表白式的数量。

请记住，数组公式与可变公式有点相似：假设它援用的任何一个单元格出现更改、可变或许经过从新计算，则数组公式会计算该公式中的一切单元格并计算口头计算所需的一切虚构单元格。

要优化数组公式的计算速度，请口头以下操作：将数组公式中的表白式和区域援用提取到独自的辅佐列和行中。

这样可以更好地利用 Excel 中的默认从新计算环节。

不要援用完整行，或援用超出须要的行和列。

数组公式自愿计算公式中的一切单元格援用，即使单元格为空或未经常使用。

从 Excel 2007 开局，有 100 万行可用，援用整个列的数组公式的计算速度极端缓慢。

从 Excel 2007 开局，假设可以，请经常使用结构化援用来最大水高山减少数组公式计算的单元格数。

在 Excel 2007 之前的版本中，尽或许经常使用灵活区域称号。

虽然它们可变，但这是值得的，由于它们最大水平增加了区域大小。

留意同时援用行和列的数组公式：这将强迫计算矩形区域。

尽或许经常使用 SUMPRODUCT；它比等效的数组公式速度稍微快一点。

几种罕用最优化方法

学习和上班中遇到的大多疑问都可以建模成一种最优化模型启动求解，比如咱们如今学习的机器学习算法，大部分的机器学习算法的实质都是建设优化模型，经过最优化方法对指标函数（或损失函数）启动优化，从而训练出最好的模型。

经常出现的优化方法(optimization)有梯度降低法、牛顿法和拟牛顿法、共轭梯度法等等。

1. 梯度降低法（Gradient Descent） 梯度降低法是最早最便捷，也是最为罕用的最优化方法。

梯度降低法成功便捷，当指标函数是凸函数时，梯度降低法的解是全局解。

普通状况下，其解不保障是全局最优解，梯度降低法的速度也未必是最快的。

梯度降低法的优化思维是用以后位置负梯度方向作为搜查方向，由于该方向为以后位置的最快降低方向，所以也被称为是”最速降低法“。

最速降低法越接近指标值，步长越小，行进越慢。

梯度降低法的缺陷： （1）接近极小值时收敛速度减慢; （2）直线搜查时或许会发生一些疑问； （3）或许会“之字形”地降低。

在机器学习中，基于基本的梯度降低法开展了两种梯度降低方法，区分为随机梯度降低法和批量梯度降低法。

比如对一个线性回归（Linear Logistics）模型，假定上方的h(x)是要拟合的函数，J()为损失函数，是参数，要迭代求解的值，求解进去了那最终要拟合的函数h()就进去了。

其中m是训练集的样本个数，n是特色的个数。

1）批量梯度降低法（Batch Gradient Descent，BGD） （1）将J()对求偏导，获取每个theta对应的的梯度： (2）由于是要最小化危险函数，所以按每个参数的梯度负方向，来降级每个： （3）从上方公式可以留意到，它获取的是一个全局最优解，但是每迭代一步，都要用到训练集一切的数据，假设m很大，那么可想而知这种方法的迭代速度会相当的慢。

所以，这就引入了另外一种方法——随机梯度降低。

关于批量梯度降低法，样本个数m，x为n维向量，一次性迭代须要把m个样本所有带入计算，迭代一次性计算量为m*n2。

2）随机梯度降低（Stochastic Gradient Descent，SGD） （1）上方的危险函数可以写成如下这种方式，损失函数对应的是训练集中每个样本的粒度，而上方批量梯度降低对应的是一切的训练样本： （2）每个样本的损失函数，对求偏导获取对应梯度，来降级： （3）随机梯度降低是经过每个样原本迭代降级一次性，假设样本量很大的状况（例如几十万），那么或许只用其中几万条或许几千条的样本，就曾经将 迭代到最优解了，对比上方的批量梯度降低，迭代一次性须要用到十几万训练样本，一次性迭代无法能最优，假设迭代10次的话就须要遍历训练样本10次。

但是，SGD随同的一个疑问是噪音较BGD要多，使得SGD并不是每次迭代都向着全体最优化方向。

随机梯度降低每次迭代只经常使用一个样本，迭代一次性计算量为n2，当样本个数m很大的时刻，随机梯度降低迭代一次性的速度要远高于批量梯度降低方法。

两者的相关可以这样了解：随机梯度降低方法以损失很小的一部分准确度和参与肯定数量的迭代次数为代价，换取了总体的优化效率的优化。

参与的迭代次数远远小于样本的数量。

对批量梯度降低法和随机梯度降低法的总结： 批量梯度降低---最小化一切训练样本的损失函数，使得最终求解的是全局的最优解，即求解的参数是使得危险函数最小，但是关于大规容貌本疑问效率低下。

随机梯度降低---最小化每条样本的损失函数，虽然不是每次迭代获取的损失函数都向着全局最优方向， 但是大的全体的方向是向全局最优解的，最终的结果往往是在全局最优解左近，实用于大规模训练样本状况。

2. 牛顿法和拟牛顿法（Newtons method &Quasi-Newton Methods）1）牛顿法（Newtons method） 牛顿法是一种在实数域和双数域上近似求解方程的方法。

方法经常使用函数 f ( x )的泰勒级数的前面几项来寻觅方程 f ( x ) = 0的根。

牛顿法最大的特点就在于它的收敛速度很快。

详细步骤： 首先，选用一个接近函数 f ( x )零点的x0，计算相应的 f ( x 0)和切线斜率 f ( x 0)（这里 f 示意函数 f的导数）。

而后咱们计算穿过点( x 0, f( x 0))并且斜率为 f ( x 0)的直线和 x 轴的交点的 x 坐标，也就是求如下方程的解： 咱们将新求得的点的 x 坐标命名为 x 1，理论 x 1会比 x 0更接近方程 f( x ) = 0的解。

因此咱们如今可以应用 x 1开局下一轮迭代。

迭代公式可化简为如下所示： 曾经证明，假设 f是延续的，并且待求的零点 x 是孤立的，那么在零点 x 周围存在一个区域，只需初始值 x 0位于这个临近区域内，那么牛顿法必然收敛。

并且，假设 f ( x )不为0, 那么牛顿法将具备平方收敛的性能. 粗略的说，这象征着每迭代一次性，牛顿法结果的有效数字将参与一倍。

下图为一个牛顿法口头环节的例子。

由于牛顿法是基于以后位置的切线来确定下一次性的位置，所以牛顿法又被很笼统地称为是切线法。

关于牛顿法和梯度降低法的效率对比： 从实质下来看，牛顿法是二阶收敛，梯度降低是一阶收敛，所以牛顿法就更快。

假设更深刻地说的话，比如你想找一条最短的门路走到一个盆地的最底部，梯度降低法每次只从你以后所处位置选一个坡度最大的方向走一步，牛顿法在选用方向时，不只会思考坡度能否够大，还会思考你走了一步之后，坡度能否会变得更大。

所以，可以说牛顿法比梯度降低法看得更远一点，能更快地走到最底部。

（牛顿法眼光愈延久远，所以少走弯路；相对而言，梯度降低法只思考了部分的最优，没有全局思维。

） 依据wiki上的解释，从几何上说，牛顿法就是用一个二次曲面去拟合你以后所处位置的部分曲面，而梯度降低法是用一个平面去拟合以后的部分曲面，理论状况下，二次曲面的拟合会比平面更好，所以牛顿法选用的降低门路会更合乎实在的最优降低门路。

注：白色的牛顿法的迭代门路，绿色的是梯度降低法的迭代门路。

牛顿法的优缺陷总结： 好处：二阶收敛，收敛速度快； 缺陷：牛顿法是一种迭代算法，每一步都须要求解指标函数的Hessian矩阵的逆矩阵，计算比拟复杂。

2）拟牛顿法（Quasi-Newton Methods） 　拟牛顿法是求解非线性优化疑问最有效的方法之一，于20世纪50年代由美国Argonne国度试验室的物理学家所提进去。

Davidon设计的这种算法在过后看来是非线性优化畛域最具发明性的发明之一。

不久R. Fletcher和M. J. D. Powell证明了这种新的算法远比其余方法极速和牢靠，使得非线性优化这门学科在一夜之间一日千里。

拟牛顿法的实质思维是改善牛顿法每次须要求解复杂的Hessian矩阵的逆矩阵的缺陷，它经常使用正定矩阵来近似Hessian矩阵的逆，从而简化了运算的复杂度。

拟牛顿法和最速降低法一样只需求每一步迭代时知道指标函数的梯度。

经过测量梯度的变动，结构一个指标函数的模型使之足以发生超线性收敛性。

这类方法大大优于最速降低法，尤其关于艰巨的疑问。

另外，由于拟牛顿法不须要二阶导数的消息，所以有时比牛顿法更为有效。

如今，优化软件中蕴含了少量的拟牛顿算法用来处置无解放，解放，和大规模的优化疑问。

详细步骤： 　拟牛顿法的基本思维如下。

首先结构指标函数在以后迭代xk的二次模型： 这里Bk是一个对称正定矩阵，于是咱们取这个二次模型的最优解作为搜查方向，并且获取新的迭代点： 其中咱们要求步长ak 满足Wolfe条件。

这样的迭代与牛顿法相似，区别就在于用近似的Hesse矩阵Bk 替代实在的Hesse矩阵。

所以拟牛顿法最主要的中央就是每一步迭代中矩阵Bk的降级。

如今假定获取一个新的迭代xk+1，并获取一个新的二次模型： 咱们尽或许地利用上一步的消息来选取Bk。

详细地，咱们要求 从而获取 这个公式被称为割线方程。

罕用的拟牛顿法有DFP算法和BFGS算法。

原文链接： Math] 经常出现的几种最优化方法 - Poll的笔记 - 博客园

教程 Excel性能优化方法（三）

经常使用 INDEX 和 MATCH 或 OFFSET 而不是 VLOOKUP

虽然 VLOOKUP 比 MATCH 和 INDEX，或 OFFSET 组合的速度稍快（大概快 5%）、更便捷，并经常使用更少的内存，但 MATCH 和 INDEX 所提供的额外灵敏性理论可以清楚节俭期间。

例如，可以将齐全 MATCH 的结果存储在单元格中，并在几个 INDEX 语句中重用。

INDEX 函数是极速运转的不变函数，它可以放慢从新计算的速度。

OFFSET 函数的运转速度也很快；但它是可变函数，因此有时会清楚参与处置计算链所需的期间。

可轻松将 VLOOKUP 转换为 INDEX 和 MATCH。

以下两个语句前往相反结果：

SORT的运行

由于齐全婚配查找或许很慢，因此可以思考经常使用以下选项来提高性能：

对缺少值的排序数据经常使用两个查找

关于在数行外口头的查找，两个近似婚配清楚快于一个齐全婚配。

（分界点是大概 10-20 行。

）

假设可以对数据排序，但由于不能确定要查找的值能否位于查找范围内而仍无法经常使用近似婚配，则可以经常使用以下公式：

公式第一部分的运作方式是对查找列自身口头近似查找

可以经常使用以下公式审核从查找列获取的结果能否与查找值相反（在这种状况下，你有一个齐全婚配项）：

假设此公式前往“True”，则找到了齐全婚配项，所以可以再次口头近似查找，但这次从列中前往所需的结果。

假设从查找列获取的结果与查找值不婚配，则示意它是缺失值，公式将前往“notexist”。

留意，假设查找的值小于列表中的最小值，则会收到失误。

可以经常使用 IFERROR 来处置此失误，或许向列表参与一个小的测试值。

关于缺少值的未排序数据，经常使用 IFERROR 函数

假设肯定对未排序数据经常使用齐全婚配查找，但是不能确定查找值能否存在，理论肯定处置找不到婚配项时前往的 #N/A。

从 Excel 2007 开局，可以经常使用 IFERROR 函数，该函数既快又便捷。

在早期版本中，一个便捷但较慢的方法是经常使用蕴含两个查找的 IF 函数。

假设经常使用齐全 MATCH 一次性，将结果存储在单元格中，而后在口头 INDEX 之前测试结果，则可以防止双重齐全查找。

假设无法经常使用两个单元格，则经常使用 COUNTIF。

它理论比齐全婚配查找速度快。

经常使用 MATCH 和 INDEX 对多个列启动齐全婚配查找

理论可以屡次重复经常使用存储的齐全 MATCH。

例如，假设要对多个结果列口头齐全查找，则可以经常使用一个 MATCH 和多个 INDEX 语句（而不是多个 VLOOKUP 语句）来节俭期间。

为 MATCH 参与一个额外的列来存储结果 ( stored_row )，并对每个结果列经常使用以下语句：

或许，可以在数组公式中经常使用 VLOOKUP。

（肯定经常使用 Ctrl+-Shift+Enter 输入数组公式。

Excel 将参与 { and }，以显示这是一个数组公式）。