Index
U NIVERSITY OF L EIPZIG
Institute of Production Management and Industrial Information Management
Marschnerstr. 31, 04109 Leipzig, Germany
Phone: [49] / (0)341 / 4941-182, Fax: -125
Report No. 17
The Threshold Waving Algorithm for Job Shop Scheduling
Jukka Siedentopf
?siedentopf@wifa.uni-leipzig.de?
- to appear -
August 1995
Index
1Introduction1
2Threshold waving2
2.1The algorithm2
2.2Parametrization3 3Computational results4 4Comparison7 5Outlook8 References8
Summary
A unidirectional mutation-selection approach for the general job shop scheduling problem is presented. The underlying algorithm uses threshold accepting as an iterative search technique and incorporates a special non-monotonic threshold function (threshold waving). Search is performed in the space of operation sequences and, for the purpose of evaluation, is combined with a simple heuristic transforming operation sequences into schedules. Mutation of solution candidates is based on a critical path analysis and, additionally, on a probabilistically employed local hill-climbing operator. Effectiveness of the approach is demonstrated within a comparison with some well-known heuristic and exact algorithms.
Remark: A slightly shortened and changed version of of this report has been submitted to the Symposium über Operations Research, the 1995 Annual Conference of the DGOR, the GM?R, and the ?GOR.
1Introduction
In the field of combinatorial optimization an increasing number of approaches introducing some kind of stochastic search are recommended as alternatives to somewhat more conven-tional approaches of mathematical programming. A common characteristic of many of these approaches is the principle of generating new solution candidates (configurations) by per-forming some (often stochastic) perturbations (mutations or modifications) of already available ones. Another characteristic is that the selection of configurations permitted to remain in the solution process does not only depend on the quality of the solution represented by the con-sidered configuration, but also on external parameters.
The number of configurations involved in the solution process can serve as a separation crite-rion for the proposed approaches: In multidirectional approaches like genetic algorithms (GA) [e.g. 11] a multitude of configurations is managed in a population. Availability of several dif-ferent configurations is an indispensable requirement for the recombination of new confi-gurations from the building blocks of already available ones. Recombination is the predomi-nating principle for generating configurations in genetic algorithms. Mutation serves for adding new building blocks by implementing small stochastic perturbations in the available building material.
In another class of more simply structured unidirectional approaches, e.g. simulated annealing [e.g. 1] or derivates like threshold accepting [6, 7], only one configuration undergoes a process of mutation. Thus, selection is reduced to a decision whether a new, modified configuration will replace the old one.
Facing complex scheduling problems with approaches using recombination it is a major prob-lem to find a suitable representation of schedules: By means of reducing the search space, the desired structure should be capable of representing schedules effectively, i.e. maximizing the ratio of the number of feasible schedules to the number of representable ones. Simultaneously, in order to exploit the implicit parallelism of multidirectional approaches, the availability of a suitable recombination operator working on the proposed structure is presupposed. Short-comings of the ‘team-work’ of a representation structure and the corresponding recombination operator frequently causes inefficiencies: A lot of time is spent either searching the space of infeasible solutions, e.g. in case of binary representation, or recombining solutions without gaining solution quality. As long as ‘efficient’ combinations of representation structures and recombination operators are missed, unidirectional approaches seem to be more fruitful for the development of fast algorithms for scheduling purposes - despite some discouraging experien-ces made with the ‘logarithmically cooled’ simulated annealing. In the following, such a unidi-rectional approach for job shop scheduling considering the objective of makespan minimization is introduced.
2Threshold waving
2.1The algorithm
A first version of the sketched algorithm [13] has been developed by means of a comparison with a specific genetic algorithm [12]. The algorithm adopts components of the GA, avoiding the originally used binary representation structure which only serves for applicability of the standard recombination operator (crossover). The algorithm uses threshold accepting as a base search strategy very similar to simulated annealing, deviating in particular in the acceptance of new configurations: A modified configuration serves as a working basis for further search if its solution quality is better than or not more than an actual threshold worse than that one of the current configuration. Hence, in contrast to simulated annealing threshold accepting does without probabilities of acceptance and the formulation of a cooling or annealing schedule - a very critical task when applying simulated annealing [e.g. 1] - is replaced by a slightly more simple instruction of a threshold decrease. Additionally, a monotonic and linear decreasing threshold function is used and the threshold is decreased after a fixed number of trials (modifications).
In the resulting threshold accepting algorithm for makespan minimization (TAMM) solution candidates are coded as task or job sequences, entered e.g. in a matrix row by row for the in-dividual machines. An initial solution is created by randomly generating such sequences. In order to generate schedules, the task sequences serve as input for a simple heuristic schedule builder together with the given machine sequences of the jobs and the given operation times. The schedule builder tries to build up a schedule according to the proposed sequences. In cases of mutually blocking sequences (i.e. it is not possible to schedule any operation on any machine), a repair algorithm changes one of the sequences giving priority to an operation cur-rently to be processed on the selected machine while shifting as few other operations as pos-sible.
Significant improvements of the TAMM algorithm have been attained by implementing two extensions: Firstly, the modification operator, generating neighbourhood solutions by exchanging two randomly chosen elements in the operation sequence of a randomly chosen machine, is restricted to successive operations lying on a critical path of the considered sche-dule. Secondly, an operator is added performing local hill-climbing by modifying the task se-quences in a randomly chosen order. Within this operator only modifications resulting in higher solution qualities are accepted. Each sequence is modified as long as any improvement is attained. Because each modification causes a rebuilding of the schedule and, in case of im-proved quality, maybe even of the critical path, this local search operator is very expensive and therefore should be used carefully.
Some first tests of that improved version of TAMM concerned the shape of the threshold function to be used within a given runtime limit, i.e. the combination of a threshold’s start value and the number of trials permitted at each threshold level. The range of possible combi-nations is limited by the given runtime which can be translated into an overall number of trials permitted.
As a result of the considered tests, combinations of high threshold’s start values and low num-bers of trials permitted at each threshold level clearly outperformes reverse combinations. Or, alternatively spoken, compared to the ‘threshold schemes’ originally used in TAMM, the num-ber of trials needed to satisfy a given solution quality’s level can be reduced using high thresh-old’s start values and low number of trials permitted at each threshold level. Clearly, this result expresses only a tendency without claiming general validity. Nevertheless, it led to a third ex-tension of the TAMM algorithm, considering the use of the ‘released’ number of trials in a way that the solution quality is further improved: The rule of a monotonic decreasing threshold is replaced by a threshold waving down and up between an (initial high) upper level and zero with a decreasing amplitude, i.e. an upper level decreasing from ‘wave to wave’. Thus, the new algorithm is called threshold waving (TW). The TW algorithm for job shop scheduling is pre-sented in Figure 1 (for the sake of simplicity it is supposed that the initial value of thresh-old_upper_limit is positive).
2.2Parametrization
In detail, TW can be controlled by adjusting the following parameters (not all explicitly listed in Figure 1): The starting value (thresholdstart) of the threshold’s upper limit (threshold_up-per_limit) as well as the number of trials after which the threshold is reduced or raised (nor-mal_wavefactor).
Sometimes it seems to be advantageous to stretch the search of regions of slow convergence, i.e. of high solution quality levels and low threshold’s upper limits. Therefore, a parameter max_wavefactor is introduced replacing the normal wavefactor when the threshold’s upper limit falls below a value wavefactor_limit.
Considering the modification operator, the number of random operation exchanges performed each time the operator is called is bounded by the value of max_modification_factor. An exact (integer) value is, also randomly, chosen from the interval [1, max_modification_factor].
Finally, due to its high cost, the application of the local search procedure is doubly controlled: Firstly, permanent local search (i.e. follwing each single modification) is permitted only after reaching a threshold’s upper limit of local_search_limit. Secondly, a probability for the appli-cation of local search is given by the value local_search_probability, independent from the threshold or the threshold’s upper limit.
Figure 1:Threshold waving algorithm
3Computational results
Tests are performed by using two well-established data sets of Fisher and Thompson [9]: The processing of 10 jobs on 10 machines (‘10x10-problem’) as well as of 20 jobs on 5 machines (‘20x5-problem’). The optimum makespan values are 930 for 10x10-problem and 1165 for 20x5-problem, respectively. The parameter settings used for TW are listed below in Table 1. The last row indicates the number of repetitions (runs) of TW with the respective parameter setting.
The cited parameter were determined in a trial and error process trying to fit an arbitrarily fixed mean runtime value of 30 seconds for the 10x10-problem. The attained settings resulted in a mean runtime of about 45 seconds for the 20x5-problem and have then been adopted (again by trial and error) to lead to solution qualities as good as possible within the given time frame.
Tab. 1:Parametrization
Computational results are presented in Table 2 and refer to an implementation of TW written in Pascal on a Pentium-based PC (90 MHz). The distributions of the solution qualities for both problems are shown in Figure 2 and Figure 3. The values of the worst solutions obtained are 1034 for the 10x10-problem and 1282 for the 20x5-problem, respectively.
Tab. 2:Computational results
9409609709809901000101095010401030102050100
150200
250
# solutions
Fig. 2:Distribution of solution qualities for the 10x10-problem
1170119012001210122012301240118012701260125050
100
150
200
250
# solutions
300
12801290Fig. 3:
Distribution of solution qualities for the 20x5-problem
makespan
runtime [sec]
20001000
9001500
Fig. 4:Convergence of TW for the 10x10-problem
Remarks : (1) Beside the waving threshold, a very steep threshold function is the most signifi-cant characteristic of TW, appearing in only 1 to 3 trials per actual threshold using the cited parametrization. (2) Note the very short time of 2.8 seconds for detecting the optimum value of 930 for the ‘famous’ 10x10-problem. This value, even if supported by a stroke of luck, il-lustrates the strength of the convergence properties of TW (see Figure 4). (3) It should also be mentioned that the used schedule builder does not produce non-delayed or other active sche-dules (just semi-active ones). Nevertheless, comparisons to the algorithm of Giffler and Thompson [10], producing active schedules, revealed the superiority of the used algorithm,indicated by a significantly better convergence [13]. (4) A test using the parameter settings of the 20x5-problem for solving the 10x10-problem led to a mean solution quality of 966.81, a standard deviation of 14.88, and a mean runtime of 40.03 seconds. Use of parameter settings of the 10x10-problem for solving the 20x5-problem led to a mean solution quality of 1212.55,
a standard deviation of 24.70, and a mean runtime of 43.98 seconds. (5) Neglecting local search, the runtime of TW is mainly determined by the number of trials working as a factor for the runtime(s) of the algorithm(s) for building up schedules and critical paths. Let a be the nor-mal_wavefactor, n the thresholdstart,
b the max_wavefactor, and s the wavefactor_limit. The number of trials is then given by: [a×(n2+1) + (b-a)×(s2+s+1)].
4Comparison
Concluding, the presented results are compared to the results of some other authors using the same ‘problem artefacts’ of Fisher and Thompson:
?the branch&bound algorithm of Barker and McMahon [3](BM)?the branch&bound algorithm of Carlier and Pinson [4] (CP)?both shifting-bottleneck-procedures of Adams, Balas and Zawack [2](SB1, SB2)?two (hybrid) genetic algorithms of Dorndorf and Pesch [5](DP1, DP2)?the genetic algorithm of Fang, Ross and Corn [8](FRC)?the simulated annealing approach of Van Laarhoven, Aarts and Lenstra [14](SA1,...,SA4)?the TAMM algorithm [13](TAMM) Table 3 summarizes solutions qualities and runtimes obtained by the mentioned approaches, a selected parametrization of TAMM from [13] and TW. Runtimes values are rounded off, if necessary, and found optimum values as well as corresponding runtime values are marked by bold types. Because of the close relation to TW, all four parametrizations proposed in [14] are listed for the algorithm SA.
Tab. 3:Comparison of solution qualities and runtimes
Remark: It should be noted that, due to different computer systems serving as bases for imple-mentations and tests of the different approaches, no direct comparability of the stated runtime values is given. The following computer systems are indicated by the authors: BM: Cyber 171, CP: PRIME 2655, SBx: VAX 780/11, DPx: DECstation 3100, FRC: SUN-4, SAx: VAX 785, and TAMM: PC 80486 (66MHz).
5Outlook
With regard to the practical applicability, the effectiveness of an approach is just a necessary, not, however, a sufficient condition. A proof of adaptability and flexibility of TW by applying it to different real-world problems should be the next step creating a versatile algorithm as a useful tool for the development of operational systems. Due to the multitude of introduced parameters, the parametrization of TW is a problem of its own, suffering from the combinato-rial ‘explosion’ of possible parameter combinations. Thus, another requirement for further de-velopments is the implementation of an automated parameter tuning, perhaps exploiting pos-sibilities of self-adaptation.
References
[1]Aarts, E., Korst, J.: Simulated Annealing and Boltzmann Machines, A Stochastic Approach to
Combinatorial Optimization and Neural Computing, Chichester et al. 1989.
[2]Adams, J., Balas, E., Zawack, D.: The Shifting Bottleneck Procedure for Job Shop Scheduling,
Management Science 34 (1988) 3, pp. 391-401.
[3]Barker, J.R., McMahon, G.B.: Scheduling the General Job-Shop, Management Science 31 (1985)
5, pp. 594-598.
[4]Carlier, J., Pinson, E.: An algorithm for solving the job-shop problem, Management Science 35
(1989) 2, pp. 164-176.
[5]Dorndorf, U., Pesch, E.: Evolution Based Learning in a Job Shop Scheduling Environment, Re-
search Memorandum 92-019, University of Limburg, Limburg 1992.
[6]Dueck, G.: New Optimization Heuristics - The Great Deluge Algorithm and the Record-to-Record
Travel, Journal of Computational Physics (1993) 104, pp. 86-92.
[7]Dueck, G., Scheuer, T.: Threshold Accepting: A General Purpose Optimization Algorithm Supe-
rior to Simulated Annealing, Journal of Computational Physics (1990) 90, pp. 161-175.
[8]Fang, H.-L., Ross, P., Corne, D.: A Promising Genetic Algorithm to Job-Shop Scheduling, Re-
scheduling, and Open-Shop Scheduling Problems, in: Forrest, S. (ed.): Proceedings of the Fifth International Conference on Genetic Algorithms, San Mateo 1993, pp. 375-382.
[9]Fisher, H., Thompson, G.L.: Probabilistic Learning Combinations of Local Job-Shop Scheduling
Rules, in: Muth, J.F., Thompson, G.L. (eds.): Industrial Scheduling, Englewood Cliffs 1963, pp.
225-251.
[10]Giffler, B., Thompson, G.L.: Algorithms for Solving Production-Scheduling Problems, Operations
Research 8 (1960), pp. 487-503.
[11]Goldberg, D.E.: Genetic Algorithms in Search, Optimization, and Machine Learning, Reading et
al. 1989.
[12]Nakano, R., Yamada, T.: Conventional Genetic Algorithm for Job Shop Problems, in: Belew, R.,
Booker, L. (eds.): Proceedings of the Fourth International Conference on Genetic Algorithms, San Mateo 1991, pp. 474-479.
[13]Siedentopf, J.: An Efficient Scheduling Algorithm Based upon Threshold Accepting, Report No.
16 of the Institute of Production Management and Industrial Information Management, University
of Leipzig, Leipzig 1995.
[14]Van Laarhoven, P.J.M., Aarts, E.H.L., Lenstra, J.K.: Job Shop Scheduling by Simulated Annea-
ling, Operations Research 40 (1992) 1, pp. 113-125.
U NIVERSITY OF L EIPZIG
Institute of Production Management and Industrial Information Management
No.1:Zelewski, Stephan: Das Konzept technologischer Theorietransformationen - eine Analyse aus produktionswirtschaftlicher Perspektive, Leipzig 1994.
No.2:Siedentopf, Jukka: Anwendung und Beurteilung heuristischer Verbesserungsverfahren für die Maschinenbelegungsplanung - Ein exemplarischer Vergleich zwischen Neuronalen
Netzwerken, Simulated Annealing und genetischen Algorithmen, Leipzig 1994.
No.3:Zelewski, Stephan: Unternehmenskrisen und Konzepte zu ihrer Bew?ltigung, Leipzig 1994.
No.4:Siedentopf, Jukka: Ein effizienter Scheduling-Algorithmus auf Basis des Threshold Accep-ting, Leipzig 1995.
No.5:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 1: Exposition, Leipzig 1995.
No.6:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 2: Bezugsrahmen, Leipzig 1995.
No.7:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 3: Einführung in Stelle/Transition-Netze, Leipzig 1995.
No.8:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 4: Verfeinerungen von Stelle/Transition-Netzen, Leipzig 1995.
No.9:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 5: Einführung in Synthetische Netze, Teilband 5.1: Darstellung des Kern-
konzepts, Leipzig 1995.
No.10:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 5: Einführung in Synthetische Netze, Teilband 5.2: Auswertungsm?glich-
keiten, Leipzig 1995.
No.11:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 6: Erweiterungen von Synthetischen Netzen, Leipzig 1995.
No.12:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 7: Fallstudie, Leipzig 1995.
No.13:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 8: Charakterisierung des Petrinetz-Konzepts, Leipzig 1995.
No.14:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 9: Beurteilung des Petrinetz-Konzepts, Leipzig 1995.
No.15:Zelewski, Stephan: Petrinetzbasierte Modellierung komplexer Produktionssysteme (Projekt PEMOPS), Band 10: Petrinetz-Literatur, Leipzig 1995.
No.16:Siedentopf, Jukka: An Efficient Scheduling Algorithm Based upon Threshold Accepting, Leipzig 1995.
No.17:Siedentopf, Jukka: The Threshold Waving Algorithm for Job Shop Scheduling, Leipzig 1995.
excel 中INDEX和MATCH函数嵌套应用
INDEX和MATCH函数嵌套应用 主讲老师：简单老师 第一部分：INDEX和MATCH函数用法介绍 第一，MATCH函数用法介绍 MATCH函数也是一个查找函数。MATCH 函数会返回匹配值的位置而不是匹配值本身。在使用时，MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。 MATCH函数语法：MATCH(查找值，查找区域，查找模式) 可以通过下图来认识MATCH函数的用法： =MATCH(41,B2:B5,0)，得到结果为4，返回数据区域B2:B5 中41 的位置。 =MATCH(39,B2:B5,1)，得到结果为2，由于此处无正确匹配，所以返回数据区域B2: B5 中(38) 的位置。注：匹配的查找值，MATCH 函数会查找小于或等于(39)的最大值。 =MATCH(40,B2:B5,-1)，得到结果为#N/A，由于数据区域B2:B5 不是按降序排列，所以返回错误值。 第二，INDEX函数用法介绍 INDEX函数的功能就是返回指定单元格区域或数组常量。如果同时使用参数行号和列号，函数INDEX返回行号和列号交叉处的单元格中的值。
INDEX函数语法：INDEX(单元格区域，行号，列号) 可以通过下图来认识INDEX函数的用法： =INDEX(A1:C6,2,3)，意思就是返回A1:C6中行号是2 列号是3 ,即第二行与第三列的交叉处，也就是C2单元格的值，为84。 第二部分：INDEX和MATCH函数应用案例介绍 下图工作表所示的是一个产品的型号和规格的价格明细表。通过这个表的数据，进行一些对应的查询操作。
第一，单击B5单元格下拉按钮，选择型号，然后在B6单元格完成型号所在行号的查询。如下图所示： 随意选择一个型号，比如A0110，然后在B6单元格输入公式：=MATCH($B$5,$D$4: $D$12,0)，得到结果1。 公式解释：用MATCH函数查找B5单元格这个型号在D4:D12区域中对应的位置。其中的0参数可以省略不写。MATCH函数中0代表精确查找，1是模糊查找。 第二，单击B9单元格下拉按钮，选择规格，然后在B10单元格完成规格所在列号的查询。如下图所示： 随意选择一个规格，比如101，然后在B10单元格输入公式：=MATCH(B9,E3:G3,0)，得到结果1。 第三，查询B6和B10单元格所对应的价格。 价格的查询，可以使用index函数完成，输入公式：=INDEX(E4:G12,B6,B10)可以得到结果为78。嵌套上面的match函数，可以将公式改为：=INDEX(E4:G12,MATCH(B5,D4: D12,0),MATCH(B9,E3:G3,0))。大家可以变化C3中的型号来看看结果是否正确。 通过下面工作表的源数据，利用index函数实现行列汇总查询。
什么是HTML文件
什么是HTML文件？ ?HTML的英文全称是Hypertext Marked Language，中文叫做“超文本标记语言”。 ?和一般文本的不同的是，一个HTML文件不仅包含文本内容，还包含一些Tag，中文称“标记”。 ?一个HTML文件的后缀名是.htm或者是.html。 ?用文本编辑器就可以编写HTML文件。 ?这就试写一个HTML文件吧！ ?打开你的Notepad，新建一个文件，然后拷贝以下代码到这个新文件，然后将这个文件存成first.html。
INDEX索引函数语法
名师整理优秀资源 1、index语法 index(区域，第几行，第几列) 返回区域第几行第几列的数值。 参数还是很好理解的。 注意的就是：如果区域为同一行，可以省略第二个参数；同样如果区域为同一列，可以省略第三个参数。 下面看实例： 讲解一下。我们挑INDEX(A1:C6,2,2)单元格，所以公B2行、第2列的值，交叉点即2C6A1意思是返回：这个区域第。姓名式结果是 名师整理优秀资源
一列嘛。/单列区域只需要两个参数就行了，因为只有一行/单行 ；INDEX(B2:B6,2)也就是返回第二行的张三。也就是返回第列列的成绩INDEX(B2:C2,2) 和match配合、2index 函数配合，常用来查询。matchindex函数和index来查询，现在展示vlookup查询张三的成绩。比如下面的例子，我们可以使用和match的配合。 INDEX(C2:C6,MATCH(E2,B2:B6,0)) 名师整理优秀资源 现在分析一下，在函数组合的文章中，我们讲到了函数组合的剖析要从内到外。2。B2在：B6的位置，是先看内部的MATCH(E2,B2:B6,0)，是返回张三了。，就是 30INDEX(C2:C6,2)MATCH(E2,B2:B6,0)那么把替换成2再看：这么复杂的组合有matchindex 肯定有同学会说：我已经会了vlookup，再学和个卵用？其实作用还是很大的，看下面的例子：
根据蓝色区域的信息，查询张三和王五的成绩、性别、班级。． 名师整理优秀资源 如果用vlookup，公式如下： 3个公式才可以。成绩、性别、班级需要手动编辑match配合，一个公式就可以搞定了。如果用index和B8的公式： INDEX($A$1:$D$5,MATCH($A8,$A$1:$A$5,0),MATCH(B$7,$A$1:$D$1,0)) 公式很复杂，我们讲解一下：这个区域被锁定了，公式怎么拖动都不会改变。1、$A$1:$D$5表中的位置。$A8张三在$A$1:$A$5MATCH($A8,$A$1:$A$5,0)2、，是返回。。。、示公式向右拖动不变，向下拖动还是正常的$A9$A10表B$7$A$1:$D$1成绩在中的位置。 MATCH(B$7,$A$1:$D$1,0)3、，是返回。B$7，向下拖动被锁定了一直时D$7、C$7示公式向
html网页编写实例
1、题目：按照题目中所给出的网页截图及说明，写出相同的页面效果。页面包含如下：index.html、footer.html、header.html、insurance.html、rates.html、redbank.html、welcome.html。 主干代码: Index:
ITSORedBank Excel中index和match函数的应用实例
Excel中index和match函数的应用实例 原文出处https://www.wendangku.net/doc/0d4909768.html,/50281/400990 查询函数一直是Excel中常被用到的一种函数，本篇来介绍一下index与match在实际工作中的应用实例。先看一下这个Excel工作簿。要求：将“用户分析”工作表中机房名称列中输入函数，向下拖动使其自动选择对应“号段检索”工作表中备注的机房名称。
其中故障号码为“号段检索”表中起始、结束号段中的码号。因此这里需要利用index 与match函数来完成检索号段归属机房查询工作。 想到了index与match函数了吧，可以先回顾一下。 -------------------------------------INDEX------------------------------------ index函数的意义：返回指定行列交叉处引用的单元格。 公式：=index(reference,row_num,column_num,area_num) reference指的是要检索的范围； row_num指的是指定返回的行序号，如超出指定检索范围，返回错误值#REF!; column_num指的是指定返回的列序号，如超出指定检索范围，返回错误值#REF!; area_num指的是返回该区域中行和列的交叉域。可省略，默认1。如小于1时返回错误值#VALUE! -------------------------------------MATCH------------------------------------ match函数的意义：返回指定方式下查找指定查找值（可以是数字、文本或逻辑值）在查找范围1行或1列的位置。 公式：=match(lookup_value,lookup_array,match_type) lookup_value指指定查找值； lookup_array指的是1行或1列的被查找连续单元格区域。 match_type指的是查找方式，1或省略指查找小于或等于lookup_value的最大值，lookup_array必须为升序排列，否则无法得到正确结果。 0指查找等于lookup_value的第一个数值，如果不是第一个数值则返回#N/A -1指查找大于或等于lookup_value的最小值，lookup_array必须为降序，否则无法得到正确结果。 ------------------------------------------------------------------------------- 那么在这里是用match函数来定位“用户分析”表中故障号码在“号段检索”起始号段或结束号段的所在行序号。 如下图：=MATCH(用户分析!K2,号段检索!B:B,1)。但是为什么检索出来的行号会是错误值呢？
index函数语法说明及应用实例
index函数语法说明及应用实例 INDEX函数INDEX函数是返回表或区域中的值或对值的引用。函数INDEX （）有两种形式：数组形式和引用形式。数组形式通常返回数值或数值数组；引用形式通常返回引用。 返回特定行和列交叉处单元格的引用。如果该引用是由非连续选定区域组成的，则可以选择要用作查找范围的选定区域。 函数语法语法：INDEX（array，row_num，column_num）返回数组中指定的单元格或单元格数组的数值。INDEX（reference，row_num，column_num，area_num）返回引用中指定单元格或单元格区域的引用。 参数：Array为单元格区域或数组常数；Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column_num；Column_num是数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area_num是选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，则INDEX函数使用区域1 实例：如果A1=68、A2=96、A3=90，则公式=INDEX（A1:A3，1，1）返回68。 INDEX函数返回一个值或者对某个值的引用。与其他函数如MATCH函数联合使用，可以构造强大的公式。 什么情况下使用INDEX函数？INDEX函数可以返回一个值或者对某值的引用，因此可以使用该函数来：查找所选月份的销量；获取对指定行、列、区域的引用；基于给定数目创建动态区域；以字母顺序排序文本列。 INDEX函数的语法 INDEX函数有两种语法形式数组和引用。使用数组形式，返回值;使用引用形式，返回引
html代码练习题
1．（多选）网站首页默认的文件名一般有（）。 A．index.htm B．index.html C．index.asp D．default.htm 2.下列关于HTML语言的描述不正确的是（）。 A．HTML文档是文本格式的文件 B．HTML代码中可以嵌入多种网页特效代码 C．不懂HTML语言的人无法制作网站 D．HTML语言是通过一系列特定的标记来标识出相应的意义和作用的 3．为了标识一个HTML 文件，应该使用的标签是（）。 A． B．
C． D． 4．在HTML语言中，表格中行的开始和结束的标签是（）。 A． B．