Stock Index Futures Trading and Volatility in International Equity
The Journal of Futures Markets,Vol.20,No.7,661–685(2000)?2000by John Wiley &Sons,Inc.
S tock I ndex F utures T rading and V olatility in I nternational E quity M arkets
HUSEYIN GULEN STEWART MAYHEW
This article examines stock market volatility before and after the in-troduction of equity-index futures trading in twenty-?ve countries,using various models that account for asynchronous data,conditional heteroskedasticity,asymmetric volatility responses,and the joint dy-namics of each country’s index with the world-market portfolio.We found that futures trading is related to an increase in conditional volatility in the United States and Japan,but in nearly every other country,we found either no signi?cant effect or a volatility-dampen-ing effect.This result appears to be robust to model speci?cation and is corroborated by further analysis of the relationship between vola-tility,trading volume,and open interest in stock futures.An increase in conditional covariance between country-speci?c and world returns
The authors would like to thank Mike Cooper,Jin-Chuan Duan,Robert Engle,Andrew Karolyi,Ken Kroner,Alexandra MacKay,John McConnell,Ken Ratliff,Joshua Rosenberg,and two anonymous referees.Earlier versions of this paper were presented at Virginia Tech,the Spring 1998Chicago Board of Trade Research Symposium,the Istanbul Stock Exchange,Purdue University,the 1999meetings of the American Finance Association and 1999INFORMS meetings in Cincinnati.S.Mayhew would like to thank the Center for International Business Education and Research (CIBER)for funding this project.
Received October,1999;Accepted February,2000
I
Huseyin Gulen is a Ph.D.candidate in Finance at Krannert Graduate School of Management at Purdue University in West Lafayette,Indiana.
I
Stewart Mayhew is an Assistant Professor in the Department of Banking and Finance at Terry College of Business at the University of Georgia in Athens,Georgia.
662Gulen and Mayhew
at the time of futures listing is also documented.?2000John Wiley
&Sons,Inc.Jrl Fut Mark20:661–685,2000
INTRODUCTION
The world’s?rst stock index futures contract was the Value Line contract,
introduced by the Kansas City Board of Trade on February24,1982.
Today,stock index futures and options trade in markets all over the world,
with new contracts launched nearly every year.Table1reports launch
dates for thirty nations that introduced stock index futures between1982
and January,1998.In addition,plans are underway for exchange-listed
index futures in many other nations,including India,Indonesia,Czech
Republic,Slovakia,Turkey,and others.
As exchange-traded stock index futures and other derivatives become more pervasive in the world’s?nancial markets,it is increasingly impor-
tant to understand the effect of derivatives trading on the underlying
markets.The previous literature on the effects of stock index futures
trading has focused primarily on developed markets,and it is unclear to
what extent these results are applicable to less-developed markets.More-
over,the existing research has come to con?icting conclusions regarding
the effect of futures trading on volatility.While some authors have found
that volatility appears to increase with the introduction of futures,others
have found no signi?cant effect,and still others have found that volatility
decreases.1
In this paper,we examine the time series properties of stock indexes in twenty-?ve countries in order to investigate the impact of stock index
futures listing and subsequent trading activity on the volatility structure
of the underlying cash market.We examined this issue in two ways.First,
we tested for structural changes at the time of futures listing by compar-
ing properties of the returns series before and after listing.Second,we
tested whether volatility in the post-listing period is related to futures
market volume and open interest.The results of both tests showed that
futures trading is associated with increased volatility in the United States
and Japan,but this was not the case in virtually every one of the other
twenty-three countries.In some countries,there is no robust,signi?cant
effect,and in many others,futures trading is associated with lower
volatility.
Many theories have been advanced elsewhere for how the introduc-tion of futures might impact the volatility of the underlying market.As
1For a detailed summary of this literature,see surveys by Hodges(1992),Damodaran and Subrah-
manyam(1992),Sutcliffe(1997),and Mayhew(1999).
Stock Index Futures Trading663 pointed out by Hodges(1992),Mayhew(1999),and others,most of these
theories predicted that volatility can increase or decrease with the intro-
duction of futures depending on the underlying assumptions,or depend-
ing on the parameter values used in the models.Due to the large number
of competing theoretical models with overlapping and ambiguous predic-
tions,we are reluctant to interpret our results as favoring any particular
model.Perhaps futures markets in?uence cash markets through multiple,
TABLE I
Launch Dates for Index Futures Contracts
Country Underlying Index Launch Date
United States Value Line24February1982
S&P50021April1982
Australia All Ordinaries16February1983
United Kingdom FT-SE1003May1984
Canada TSE30016January1984
Brazil BOVESPA14February1986
Hong Kong Hang Seng6May1986
Japan(SIMEX)Nikkei2253September1986
(Osaka)OSE509June1987
(Osaka)Nikkei2253September1988
(Tokyo)Topix3September1988
New Zealand Barclay Share January1987
Sweden OMX3April1987
Finland FOX2May1988
Netherlands AEX24October1988
France CAC409November1988
Denmark KFX7December1989
South Africa All Share30April1990
Switzerland SMI9November1990
Germany DAX23November1990
Chile IPSA December1990
Spain IBEX3514January1992
Austria ATX7August1992
Norway OBX4September1992
Belgium BEL2029October1993
Italy MIB3028November1994
Hungary BSI31March1995
Israel Maof2527October1995
Malaysia KLCI15December1995
Korea KOSPI2003May1996
Portugal PSI-2020June1996
Russia RTS March1997
Venezuela IBC5September1997
Poland WIG2016January1998
Greece FTSE/ASE-2027August1999
Initial trading dates for various Index Futures contracts.Sources:information published by the individual exchanges,tele-
phone conversations with exchange of?cials,and Futures Industry Association Fact Book.It should be noted that the trading
of Japanese stock index futures initiated in Singapore.
664Gulen and Mayhew
offsetting channels.Perhaps futures play a more-important stabilizing
role in markets that lack alternative stabilization mechanisms.
Our paper contributes to the existing literature in several ways.To the best of our knowledge,it is the most comprehensive examination to
date on the impact of stock index futures on cash markets.We examined
a much broader cross section of international futures introductions than
any prior study,and this study is the?rst to examine the impact of futures
markets in emerging economies.Examining the existing literature,as
summarized by Mayhew(1999),one is left with the impression that the
introduction of stock index futures is equally likely to be associated with
increasing or decreasing volatility.In examining the results from twenty-
?ve different countries,we saw a pattern emerging—in most cases,fu-
tures markets are associated with decreasing volatility.
In addition,we believe that this paper improves on the methodology used in prior studies.Like Lee and Ohk(1992)and Antoniou,Holmes,
and Priestly(1998),we used a framework that allows for generalized
autoregressive conditional heteroskedasticity(GARCH),but(wherever
possible)we used considerably larger sample periods.This is important
given the dif?culty of obtaining reliable GARCH estimates in small sam-
ples.2Moreover,rather than selecting a GARCH speci?cation ad hoc,we
performed various speci?cation tests to determine the appropriate model,
and then tested the robustness of our results using several alternative
GARCH models.In addition,we examined the properties of excess re-
turns over the world-market index,which enabled us to avoid attributing
worldwide price movements,such as the crash of October1987,to the
listing of futures in the local market.
To our knowledge,this also is the?rst paper to examine the rela-tionship between volatility and futures trading volume and open interest
for a large cross section of markets.We found that some,but not all,of
the results reported in the literature are robust across countries.In ex-
amining this issue,we also generalized the methodology of Bessembinder
and Seguin(1992)to a GARCH-based framework.
Finally,we believe this to be the?rst paper to examine the joint dynamics of country-speci?c and world-market returns for a cross section
of countries using a multivariate GARCH framework.This framework
allowed us to test whether the introduction of futures impacted the con-
ditional covariance between country and world returns,a measure of the
country’s integration with world markets.We found that futures markets
2Engle and Mezrich(1995)suggested using at least eight years of daily data for proper GARCH
estimation.
Stock Index Futures Trading665 appeared to help countries become more integrated with the world
market.
The basic approach of our analysis is as follows.First,we examined the impact of futures introduction on volatility using a modi?cation of
the Generalized Auto-Regressive Conditional Heteroskedasticity model
suggested by Glosten,Jagannathan,and Rundle(GJR-GARCH;1993).
To test for the impact of futures trading,we incorporated a multiplicative
dummy variable in the conditional variance equation.We checked the
robustness of our results using various alternative speci?cations.Next,
using a technique similar to the one employed by Bessembinder and Se-
guin(1992),we decomposed the trading volume and open-interest time
series into permanent and temporary components,and then we tested
how these components affected volatility by inserting them into the con-
ditional volatility equation.Finally,we analyzed the joint dynamics of
each country with world-market portfolios using the bivariate GARCH
speci?cation advanced by Engle and Kroner(1995),commonly known as
the BEKK model.3This richer framework allowed us to more carefully
control for movements in global markets.It also allowed us to test
whether the conditional covariance between a country’s return and the
world-market return changed with futures listing.
The remainder of this paper proceeds as follows.The next section describes the data we used in our analysis.We then describe the univar-
iate GJR-GARCH framework we used to analyze the data,verifying that
conditional heteroskedasticity is present in the returns in all twenty-?ve
countries,and we tested whether volatility is higher or lower after the
introduction of futures trading.We also discuss the robustness of the
results to model speci?cation.We follow up by examining whether con-
ditional volatility is related to the temporary and permanent components
of open interest and trading volume.We then present our analysis of the
joint dynamics of country-speci?c and world returns using the BEKK
bivariate model.
DATA
Daily stock-market-index data were obtained from Datastream4for
twenty-?ve of the thirty-one nations listed in Table I.Russia,Venezuela,
Poland,and Greece,which listed futures after July1996,were excluded
because,in our judgment,there was insuf?cient data in the post-event
3The acronym refers to Baba,Engle,Kraft,and Kroner,the original developers of the model.
4Datastream International,Inc.
666Gulen and Mayhew
period to draw any meaningful conclusions.Brazil and New Zealand were
excluded due to lack of data.
For twenty countries,time-series data were obtained for the stock index underlying the?rst equity futures contract listed in the respective
country.For the United States,we used data on the more popular S&P
500Index instead of the Value Line Index.In some cases,very little data
existed for the underlying index prior to the futures listing date,often
because the index was designed speci?cally to underlie the futures con-
tract and didn’t exist very long prior to the introduction of the futures.
Given the high correlations typically observed between different indices
on the same market,we did not believe this to be a major problem.To
illustrate,for Norway,we used data on the Oslo Stock Exchange(OSE)
General stock index instead of the Oslo Bors Index(OBX)due to the lack
of data on the OBX index prior to the listing date.Over a recent subsam-
ple for which data are available on both indices,we calculated a corre-
lation of.96between them.Likewise,in Finland,we used the Helsinki
Stock Exchange General Index(HEX)instead of the Finnish Options
Index(FOX),and in the UK and Italy,we used market indexes calculated
by Datastream due to insuf?cient daily data in the pre-event samples.5
In Japan,we used the?rst introduction of Nikkei225futures on the
Singapore International Monetary Exchange(SIMEX)as our event date.
Daily data were obtained on Datastream’s World Market Index from January2,1973through December31,1997.For each country,we used
all the stock-index data available on Datastream between1973and1997.
In most cases,data were only available for part of this period.The time
periods covered by our index data for each country,along with the number
of daily observations in the pre-and post-event subsamples,are reported
in Table II.
In addition,we were able to collect daily contract volume and open-interest data for seventeen of the countries in our sample.In most cases,
these data were obtained from Datastream.Data from the Canadian mar-
ket were provided by the Toronto Stock Exchange.
VOLATILITY EFFECTS OF FUTURES LISTING
Empirical Framework for Univariate Modeling
We begin our analysis by modeling the time series of excess country re-
turns net of the world-market portfolio as a univariate GARCH process.
5In the case of the UK,weekly data are available for a large window prior to futures listing,but we
felt that,in order to make the result comparable to the other countries,we should stick to daily data.
Stock Index Futures Trading667
TABLE II
Data Periods
Country Data Period Obs.Pre-Obs.Post-
Australia2January1980–31December199********
Austria20November1987–31December199711621334
Belgium2January1990–31December199********
Canada2January1973–31December199727403516
Chile2January1987–31December199********
Denmark10December1979–31December199724762037
Finland2January1987–31December199********
France9July1987–31December199********
Germany21November1977–31December199732151771
Japan4January1980–31December199720982298
Hong Kong2January1973–31December199732632888
Hungary2January1991–31December199********
Israel2January1992–31December1997928527
Italy2January1973–31December199********
Korea3January1990–31December199********
Malaysia2January1980–31December199********
Netherlands3January1983–31December199714022313
Norway3January1983–31December199724181333
Portugal1January1993–31December1997853376
South Africa10April1985–31December199711361891
Spain6January1987–31December199712381501
Sweden2January1986–31December199********
Switzerland1July1988–31December199********
United Kingdom2January1973–31December199728713485
United States2January1973–31December199723403967
Description of the data period used for each country,including the number of daily return observations before and after
stock index futures listing.
This framework is parsimonious,which allowed us to capture many of
the salient features of the data,and to partially account for movements
in the world market in a model with relatively few https://www.wendangku.net/doc/4b14736951.html,ter,we
would estimate a multivariate GARCH model that would allow us a richer
model of the joint dynamics of country-speci?c and world-market returns.
Following Pagan and Schwert(1990)and Engle and Ng(1993),the ?rst step in our univariate GARCH analysis was to remove from the time
series any predictability associated with lagged returns or day-of-the-week
effects.For each country,the following regression was estimated:
5
R?R?a?a R?a DAY?u(1)
t Wt01Wt?1?j j t
j?2
where R t is the daily return on the country’s stock index and R Wt is the
daily return on the World Market Index on day t,R Wt
is the lagged
?1
668Gulen and Mayhew
return on the World Market Index,and DAY j are day-of-the-week dum-
mies for Tuesday through Friday.
We used the excess return relative to the World Market Index as our dependent variable and the lagged World Market Index return as an in-
dependent variable in an effort to remove the effect of worldwide price
movements on volatility.6It should be noted that because of differences
in time zones,different markets line up differently with the world-market
return.This makes it dif?cult to compare directly the coef?cients of the
?rst-stage regression.For example,if the U.S.market is in?uenced by
Asian markets,this will be re?ected through the contemporaneous mar-
ket return on the left-hand side of the regression equation.On the other
hand,if the Asian markets are in?uenced by the US,this will be re?ected
through the lagged market portfolio.
Regression results are reported in Table III.It should be noted that the coef?cients on the day-of-the-week dummies in this model,which is
based on excess returns relative to the world market,should not be com-
pared directly to day-of-the-week dummies based on models of raw
returns.
To correct for any remaining predictability,and to correct for spuri-ous autocorrelation induced by nonsynchronous trading,7we performed
the usual autocorrelation adjustment:
5
u?b?b u?e.(2)
t0?j t?j t
j?1
Table IV reports parameter estimates for this equation.Following Engle
and Ng(1993),we reported Ljung–Box test statistics for twelfth-order
serial correlation both in the residuals and their squares.The Ljung–Box
statistics reported for the residual levels revealed that the regression
model removes serial correlation in the stock-return series in most of the
countries.At the5%(1%)signi?cance level,there is no serial autocor-
relation left in9(5)of25countries,and in several other cases,the test
statistic is only marginally signi?cant.This suggests that the adjustment
procedure removed the predictable part of the return series for most of
the countries.The Ljung–Box test statistics for the squared residuals are
highly signi?cant in all cases,which is consistent with the existence of
6In separate tests not reported here,we included contemporaneous world returns on the right-hand
side,allowing each country to have its own beta with respect to the world portfolio.These results,
which are available on request,are similar to those reported here.We elected to use the current
formulation because when contemporaneous variables are included in the?rst-stage regression,the
GARCH volatility equation cannot be interpreted strictly as a conditional volatility.
7See Scholes and Williams(1977),Lo and MacKinlay(1988),Nelson(1991).
Stock Index Futures Trading669
TABLE III
Coef?cients from the First-Stage Regression
Tuesday Wednesday Thursday Friday
Country Intercept R Wt
?1
Australia0.03710.3758*?0.1204*?0.0890?0.0508?0.0243
(0.03)(0.02)(0.05)(0.05)(0.05)(0.05)
Austria0.03980.1876*?0.0824?0.0563?0.0206?0.0156
(0.05)(0.04)(0.08)(0.08)(0.08)(0.08)
Belgium?0.0324?0.0988*0.02770.05150.08220.0423
(0.04)(0.03)(0.06)(0.06)(0.06)(0.06)
Canada?0.0328?0.0252*0.02420.01900.04070.0617*
(0.02)(0.01)(0.03)(0.03)(0.03)(0.03)
Chile?0.0204?0.0920*0.06180.15280.13660.3528*
(0.07)(0.04)(0.09)(0.09)(0.09)(0.09)
Denmark0.0774*0.0316?0.0854?0.0986?0.0349?0.0680
(0.04)(0.02)(0.05)(0.05)(0.05)(0.05)
Finland0.01710.0571?0.0725?0.03060.04800.0167
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
France?0.1388*?0.0669*0.1907*0.1727*0.1973*0.1479*
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
Germany0.00180.0070?0.0074?0.01060.0078?0.0341
(0.03)(0.02)(0.05)(0.05)(0.05)(0.05)
Hong Kong?0.1182*0.3306*0.08930.2151*0.06380.2121*
(0.05)(0.03)(0.08)(0.08)(0.08)(0.08)
Hungary0.09400.3063*?0.05190.0740?0.1148?0.0072
(0.08)(0.06)(0.11)(0.11)(0.11)(0.11)
Israel?0.02610.09770.13600.08910.1029?0.0052
(0.10)(0.08)(0.14)(0.14)(0.14)(0.14)
Italy?0.0345?0.0395?0.02550.01260.1258*0.1427*
(0.04)(0.02)(0.05)(0.05)(0.05)(0.05)
Japan?0.02320.1249*?0.03520.15180.0221?0.0263
(0.03)(0.02)(0.05)(0.05)(0.05)(0.05)
Korea?0.11000.0689?0.01340.1928?0.02940.0696
(0.09)(0.06)(1.12)(1.12)(1.12)(1.12)
Malaysia?0.1248*0.1102*?0.00550.1473*0.1684*0.1975*
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
Netherlands?0.0325?0.0676*0.08860.0894?0.00070.0701
(0.04)(0.02)(0.06)(0.06)(0.06)(0.06)
Norway?0.00540.1130*?0.0183?0.01030.04220.1054
(0.04)(0.03)(0.06)(0.06)(0.06)(0.06)
Portugal0.00260.08220.00760.07840.06650.0306
(0.05)(0.04)(0.08)(0.08)(0.08)(0.08)
South Africa?0.03480.02380.00830.1459*0.0765?0.013
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
Spain0.1297*0.1270*?0.1283*?0.2286*?0.1434*?0.1060
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
Sweden?0.01590.0663*0.02280.02450.06670.0637
(0.05)(0.03)(0.07)(0.07)(0.07)(0.07)
Switzerland?0.0123?0.03590.00780.09920.07050.0484
(0.04)(0.03)(0.06)(0.06)(0.06)(0.06)
670Gulen and Mayhew
TABLE III(Continued)
Coef?cients from the First-Stage Regression
Country Intercept R Wt
?1
Tuesday Wednesday Thursday Friday
United Kingdom?0.0709*0.02280.1513*0.05890.06200.1269*
(0.03)(0.02)(0.04)(0.04)(0.04)(0.04)
United States0.0319?0.1647*?0.0027?0.0430?0.0387?0.0350
(0.02)(0.01)(0.03)(0.03)(0.03)(0.03)
Results from the?rst-stage regression of country-speci?c returns on lagged world-market index and day-of-the-week dum-
mies.The model is
5
R?R?a?a R?a DAY?u,
t Wt01Wt?1?j j t
j?2
where R
t is the daily return on the country’s stock index,R
Wt
is the daily return on the World Market Index on day t,R
Wt?1
is
the lagged return on the World Market Index,and DAY
j
are day-of-the-week dummies for Tuesday through Friday.Standard errors are shown in parentheses.An asterisk indicates statistical signi?cance at the5%level.
time-varying volatility of index returns in all countries.We took this as evidence that some type of GARCH speci?cation is necessary to properly model index returns in all countries.
Using{e t}as our new return series,we proceeded to test for the effect of futures introduction on the conditional volatility of the spot market using various GARCH speci?cations.
Volatility Effect of Futures Introduction
Having demonstrated the need to account for conditional heteroskedas-ticity in returns,we now address the issue of futures listing using a GARCH model.In GARCH modeling,the residuals e t arising from esti-mating the autoregression equation[eq.(2)]are assumed to be distrib-uted N(0,h t),or alternatively e t??t,where?t has a conditional
h
?t
distribution that is N(0,1),and the conditional volatility h t depends on the GARCH speci?cation.
In order to determine which GARCH speci?cation we should use in our analysis,we conducted extensive tests to see which form of the con-ditional volatility equation best seemed to model the returns data.The results of these tests are not reported here,but nearly are identical to those reported in Gulen and Mayhew(1999),where we used a slightly different formulation for the?rst-stage regression.The main focus of this analysis was to determine whether we should use the symmetric GARCH model of Bollerslev(1986),in which positive and negative shocks of equal magnitude have the same effect on subsequent volatility,or a model
Stock Index Futures Trading671
TABLE IV
Coef?cients from the Residual Autoregression
Country Constant b1b2b3b4b5LBQ(12) (Levels)
LBQ(12)
(Squares)
Australia0.0001*0.1012?0.01340.0253?0.01810.02328.4630.27**
(0.01)(0.01)(0.01)(0.01)(0.01)(0.01)**
Austria?0.00010.2276*?0.0210?0.0246?0.01250.031020.6**438.65**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Belgium0.00000.02740.066*0.0399?0.0026?0.0358 6.9271.55**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Canada0.00000.0808*?0.00230.01000.02720.01047.9639.94**
(0.01)(0.01)(0.01)(0.01)(0.01)(0.01)**
Chile?0.00010.1981*?0.0534*?0.0394*0.0598*?0.00507.1176.466**
(0.03)(0.02)(0.02)(0.02)(0.02)(0.02)**
Denmark?0.00000.0947*?0.0037?0.0017?0.0402*0.00517.441.96**
(0.02)(0.01)(0.01)(0.01)(0.01)(0.01)**
Finland0.00010.1764*0.00010.00520.01530.022010.2340.722**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
France0.0000?0.0797*0.0533*?0.00590.00160.014015.5*917.54**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Germany0.0000?0.0307*?0.00010.0200?0.01640.0141 4.8438.591**
(0.02)(0.01)(0.01)(0.01)(0.01)(0.01)**
Hkong?0.00000.0432*?0.02370.0601*?0.0093?0.018318.9**1083.33**
(0.02)(0.01)(0.01)(0.01)(0.01)(0.01)**
Hungary0.00030.1301*0.0531*0.0070?0.01130.014133.8**727.973**
(0.04)(0.02)(0.02)(0.02)(0.02)(0.02)**
Israel?0.0001?0.0174?0.0365?0.02070.0117?0.0581*16.3*223.239**
(0.04)(0.03)(0.03)(0.03)(0.03)(0.03)**
Italy0.00000.1592*?0.0717*0.0392*0.0013?0.001012.92050.29**
(0.02)(0.01)(0.01)(0.01)(0.01)(0.01)**
Japan?0.0000?0.0792*?0.0668*0.0229?0.0021?0.00618.1801.76**
(0.01)(0.02)(0.02)(0.02)(0.02)(0.02)**
Korea0.0004?0.0021?0.0422?0.0219?0.0372?0.0528*11.91009.59**
(0.04)(0.02)(0.02)(0.02)(0.02)(0.02)**
Malaysia0.00000.1209*0.0070?0.01140.00650.010510.91352.07**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Netherlands0.0001?0.0971*0.02740.0257?0.01660.0453*21.4**780.3**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Norway0.00000.1182*?0.0068?0.0129?0.0466*0.0373*14.9*501.91**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Portugal?0.00000.1516*0.0490?0.0522?0.0074?0.012713.8154.161**
(0.02)(0.03)(0.03)(0.03)(0.03)(0.03)**
South Africa?0.00000.0834*?0.02520.0018?0.0218?0.03089.638.1064**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Spain?0.00010.0915*0.0149?0.0540*0.0364?0.018618.4*782.843**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Sweden0.00020.0555*0.0252?0.01350.01060.017811.6715.95**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
Switzerland0.0000?0.02000.0089?0.04040.0052?0.02487.256.3926**
(0.02)(0.02)(0.02)(0.02)(0.02)(0.02)**
672Gulen and Mayhew
TABLE IV(Continued)
Coef?cients from the Residual Autoregression
Country Constant b1b2b3b4b5LBQ(12) (Levels)
LBQ(12)
(Squares)
United Kingdom?0.00000.0761*0.01070.01010.004?0.009913.54791.4**
(0.01)(0.01)(0.01)(0.01)(0.01)(0.01)**
United States?0.0000?0.1303*?0.0076?0.0187?0.0513*?0.009720.2**1252.86**
(0.01)(0.01)(0.01)(0.01)(0.01)(0.01)**
Estimated parameters of the residual autoregression
5
u?b?b u?e.
t0?j t?j t
j?1
where u
t
is the residual from regression1.Ljung-Box statistics testing for12th order serial autocorrelation in e and e2are also reported.Standard errors are shown in parentheses.*indicates statistical signi?cance at the5%level.**indicates statistical signi?cance at the1%level.
where positive and negative shocks can have different effects.We tested the symmetric model and three alternative asymmetric models,including the asymmetric GARCH model(GJR-GARCH;Glosten,Jagannathan,& Runkle,1993),the nonlinear GARCH model(NGARCH;Engle&Ng, 1993),and the exponential GARCH model(EGARCH;Nelson,1991).
Speci?cation tests indicated that these asymmetric models?t the data better then the symmetric GARCH model,with the GJR-GARCH per-forming marginally better than the others.Therefore,we based our main analysis on the GJR-GARCH model.
In this model,the conditional volatility equation takes the form:
22
h????h??e??max(0,?e).
t01t?12t?13t?1 In order to estimate the impact of futures introduction,we interacted the GJR-GARCH conditional-volatility equation with a multiplicative dummy,as follows:
22 h?(1??D)[???h??e??max(0,?e)],
t M t01t?12t?13t?1 where D t takes on a value of0prior to futures introduction and a value of1after futures introduction.A signi?cant negative-parameter estimate for?M would indicate an decrease in the volatility associated with futures introduction.8
8In estimating this model,proper inequality constraints were placed on the parameters of the con-ditional variance equations to ensure the stationarity of the GARCH models and the nonnegativity of the conditional variances.Constrained maximum-likelihood estimates of these parameters were computed using the BHHH algorithm.
Stock Index Futures Trading673 Results are reported in Table V.De?ning statistical signi?cance at the5%level,we found that out of25countries,the coef?cient?M is
positive and signi?cant only for the United States and Japan,indicating
an increase in conditional volatility associated with futures introduction
in these countries.On the other hand,?M is signi?cantly negative for
Australia,Austria,Belgium,Chile,Denmark,France,Germany,Hong
Kong,Israel,Italy,Malaysia,Netherlands,Norway,South Africa,Swit-
zerland,and the United Kingdom,a total of16countries.There is no
signi?cant effect in the remaining seven countries.The experience in the
United States and Japan appears to be the exception,not the rule.
At this point,we would like to interject an important caveat.Since we have not analyzed the data for countries with rapidly maturing?nan-
cial institutions that did not list futures,it is possible that this decreasing
volatility re?ects other developments that tend to coincide with the in-
troduction of stock index futures.
Robustness Checks and Additional Tests
This section summarizes various other speci?cations we tested,but did
not report here because the results are substantively similar to those in
Table V.The results of all these tests are available upon request.
Another approach to analyzing the effect of futures introduction on volatility is to put an additive dummy variable into the GARCH equation:
22
h????h??e??max(0,?e)??D.
t01t?12t?13t?1A t
We repeated our analysis using this additive dummy speci?cation for the
GJR-GARCH model.In addition,we examined the standard
GARCH(1,1)model of Bollerslev(1986),the nonlinear NGARCH,and
the exponential EGARCH.In some speci?cations,the volatility increase
in Japan lost its signi?cance,and in some speci?cations,other countries,
including Canada,Hungary,and Korea,exhibit signi?cant increases in
conditional volatility.By and large,however,all of these speci?cations
yielded results supporting the same conclusion:outside of the United
States and Japan,volatility tends to decrease with futures listing,or at
least has remained unchanged.
In our analysis,we accounted for movements in the world index simply by estimating the dynamics of excess returns of the country index
relative to the world market.Implicitly,this assumes that the b of each
country’s return with respect to the world is1.In other results,reported
in an earlier version of this paper,we also estimated a model where each
674Gulen and Mayhew
TABLE V
Effect of Futures Introduction on GJR-GARCH Volatility
Country?0?1?2?3?M
Australia0.1311*0.7463*0.1910*?0.1028*?0.0443*
(0.016)(0.022)(0.009)(0.015)(0.013)
Austria0.0969*0.7511*0.1991*?0.0041?0.0264*
(0.012)(0.018)(0.013)(0.021)(0.011)
Belgium0.0450*0.8577*0.0864*?0.0112?0.0308*
(0.012)(0.027)(0.017)(0.020)(0.011)
Canada0.0153*0.8423*0.1405*?0.0261*0.0012
(0.002)(0.007)(0.008)(0.009)(0.004)
Chile0.2407*0.7074*0.2625*?0.0337?0.0837*
(0.032)(0.021)(0.021)(0.020)(0.013)
Denmark0.0512*0.9242*0.0586*?0.0284*?0.0362*
(0.007)(0.007)(0.006)(0.006)(0.006)
Finland0.0744*0.8446*0.0812*0.01450.0112
(0.011)(0.018)(0.011)(0.012)(0.010)
France0.0579*0.8856*0.0629*0.0544*?0.0272*
(0.014)(0.016)(0.012)(0.018)(0.009)
Germany0.0307*0.8989*0.0668*0.0253*?0.0075*
(0.004)(0.007)(0.008)(0.009)(0.003)
Hong Kong0.0870*0.8231*0.1189*0.1142*?0.0206*
(0.007)(0.005)(0.009)(0.010)(0.004)
Hungary0.3701*0.5299*0.3692*?0.1810*0.0427
(0.032)(0.028)(0.024)(0.029)(0.023)
Israel0.1748*0.7909*0.1315*0.0617*?0.0432*
(0.034)(0.023)(0.021)(0.025)(0.015)
Italy0.0529*0.9020*0.0749*?0.0130?0.0094*
(0.007)(0.008)(0.007)(0.007)(0.004)
Japan0.0309*0.8725*0.0664*0.0450*0.0127*
(0.004)(0.010)(0.005)(0.009)(0.004)
Korea0.1154*0.8351*0.0799*0.0900*0.0149
(0.025)(0.021)(0.014)(0.022)(0.012)
Malaysia0.0817*0.8225*0.1191*0.0437*?0.0211*
(0.009)(0.009)(0.007)(0.010)(0.009)
Netherlands0.1216*0.8071*0.0771*0.0931*?0.0836*
(0.013)(0.015)(0.012)(0.013)(0.010)
Norway0.0524*0.8674*0.0975*0.0120?0.0371*
(0.005)(0.005)(0.006)(0.011)(0.005)
Portugal0.3137*0.2647*0.2987*0.0129?0.0115
(0.050)(0.084)(0.031)(0.048)(0.048)
South Africa0.2772*0.7237*0.3579*?0.2170*?0.1817*
(0.028)(0.019)(0.028)(0.035)(0.013)
Spain0.0702*0.8157*0.1448*?0.0366*?0.0012
(0.010)(0.016)(0.013)(0.015)(0.007)
Sweden0.0495*0.8988*0.0499*0.0492*?0.0113
(0.009)(0.011)(0.008)(0.011)(0.008)
Switzerland0.2491*0.6765*0.1098*0.0390?0.1255*
(0.058)(0.060)(0.021)(0.023)(0.029)
Stock Index Futures Trading 675
TABLE V (Continued)
Effect of Futures Introduction on GJR-GARCH Volatility
Country
?0
?1
?2
?3
?M
United Kingdom 0.0110*0.9370*0.0474*0.0130?0.0096*(0.002)(0.005)(0.005)(0.007)(0.003)United States
0.0046*0.9204*0.0446*0.0309*0.0133*(0.001)
(0.005)
(0.005)
(0.005)
(0.002)
Constrained Maximum Likelihood parameter estimates are reported for the GJR-GARCH model with a multiplicativedummy:
22
h ?(1??D )[???h ??e ??max(0,?e )],
t M t 01t ?12t ?13t ?1where e t is the residual from autoregression 2,and ?M is a dummy variable equal to zero before and one after the futures
introduction.Standard errors are shown in parentheses.An asterisk indicates statistical signi?cance at the 5%level.
country had its own b .The results of this speci?cation are similar to those reported here.
In order to investigate whether the mismatching between time zones materially affects our results,we reestimated the model for the Asian countries in our sample using only lagged world-market returns,and this had no impact on the signs or signi?cance of any of our tests.Also,we estimated our model using weekly instead of daily data.Here,despite the dif?culty in estimating good GARCH parameters on weekly data,we still found results in line with those reported above.
Some authors,such as Chan and Karolyi (1991)and Lee and Ohk (1992),have tested for more-general structural changes in the GARCH equation at the time of futures listing by interacting a dummy variable separately for each term in the conditional volatility equation.By exam-ining these coef?cients,one can measure whether there is a change in the speed with which volatility shocks dissipate.We also estimated such a model for each country in our sample.Although some of the coef?cients on the individual dummy variables were statistically signi?cant,no clear pattern emerged across countries.
THE EFFECT OF FUTURES TRADING ACTIVITY
In the previous section,we tested whether there appears to be any struc-tural change in the underlying market at the time of futures introduction.In this section,we will test whether there appears to be a relationship,after the futures have been listed,between the level of future trading ac-tivity and the volatility of the underlying index.
676Gulen and Mayhew
Our approach is based on that of Bessembinder and Seguin(1992).
Using an autoregressive integrated moving average(ARIMA)model,they
decomposed the time series of futures trading volume and open interest
into expected and unexpected components.Bursts of trading activity
stimulated by unexpected price changes should be picked up in the un-
expected component,while the expected component should re?ect the
“background”level of futures trading.They found that market volatility
was positively related to the unexpected components of volume and open
interest,re?ecting the positive effect of volatility on volume,but that
market volatility was negatively related to the expected component,sug-
gesting an underlying stabilizing in?uence.
We followed a similar procedure using futures market trading volume and open-interest data from17of our25countries for which volume and
open interest data were available.First,we analyzed the volume and open-
interest time series from each country to select an ARIMA model that
appeared to?t the data reasonably well.Restricting our attention to mod-
els with?ve or less autoregressive lags and?ve or less moving average
lags,we selected,on the basis of the autocorrelation structure,a different
model for each time series.We then used these models to decompose
each time series into expected and unexpected components,and then
inserted them as additional explanatory variables in the GJR-GARCH
conditional volatility equation:
22
h????h??e??max(0,?e)
t01t?12t?13t?1
??ExpVol??UnexpVol??ExpOI??UnexpOI,
4567
where ExpVol and UnexpVol are the expected and unexpected compo-
nents of volume,and ExpOI and UnexpOI are the expected and unex-
pected components of open interest.
Estimation results are reported in Table VI.Of the17countries an-alyzed,the coef?cient?7on the unexpected component of open interest
is negative in all17,and statistically signi?cant in eight.The coef?cient
?6on the expected component of open interest is positive and signi?cant
only in Japan,and it is positive but not signi?cant in the United States.
The coef?cient is negative in the remaining15countries,signi?cantly so
in seven.Note that these results very closely correspond to those reported
in our earlier analysis.We interpreted this as additional evidence that it
is,in fact,futures trading,not spuriously correlated factors,that drives
the results.
With respect to the unexpected component of futures trading vol-ume,we found,like Bessembinder and Seguin(1992),that it has a posi-
Stock Index Futures Trading 677
TABLE VI
Effect of Futures Trading Activity on Volatility
Country Vol|OI
?0
?1
?2
?3
?4?5?6?7
Australia 0.13420.7087*0.1745*?0.0941*0.03460.4671*?0.0508?0.06379104|105(0.166)(0.026)(0.009)(0.016)(0.046)(0.059)(0.042)(0.163)Austria 0.2032*0.8200*0.0963*?0.0100?0.6353* 2.0146*?0.0812?0.7615*104|105(0.027)(0.033)(0.022)(0.026)(0.182)(0.272)(0.069)(0.204)Belgium 0.0547*0.8425*0.0575*0.0064?0.00770.3832?0.0133?0.4163104|104(0.022)(0.048)(0.019)(0.028)(0.124)(0.266)(0.014)(0.217)Canada 0.0263*0.8455*0.1432*?0.0767*?0.00930.0491*?0.0009?0.2244*103|104(0.004)(0.015)(0.015)(0.014)(0.008)(0.015)(0.005)(0.057)Denmark 0.2727*0.3764*0.2096*?0.0904* 1.9812* 3.8229*?0.2113*?0.3571104|104(0.047)(0.064)(0.036)(0.044)(0.525)(0.327)(0.076)(0.829)France 0.2950*0.5485*0.0781*0.04790.7452 3.0331*?0.3960?2.6735105|106(0.053)(0.070)(0.025)(0.035)(0.391)(0.334)(0.289)(2.838)Germany 0.3404*0.4334*0.03300.0899 1.0981* 4.2413*?0.1279*?0.2444105|105
(0.050)(0.072)(0.029)(0.046)(0.289)(0.364)(0.040)(0.390)Hong Kong 0.4442*0.4131*0.1507*0.1310* 2.6692*11.2339*?0.5390?5.5596*105|105(0.055)(0.056)(0.033)(0.049)(1.202)(0.817)(0.449)(1.484)Italy 0.5237*0.3059*0.2601*?0.1106 1.82698.5268*?0.6310?5.899*105|105(0.151)(0.130)(0.081)(0.099)(1.542)(1.126)(0.9690)(2.053)Japan 0.0121*0.9111*0.0217*0.0979*?0.00300.4131*0.0757*?0.4698104|106
(0.003)(0.010)(0.007)(0.013)(0.010)(0.098)(0.025)(1.201)Netherlands 0.0488*0.9156*0.0474*0.0022?0.01220.2918*?0.0972*?0.7806*104|105(0.010)(0.016)(0.013)(0.013)(0.026)(0.077)(0.044)(0.388)Norway 0.0403*0.8644*0.0965*?0.00260.06500.1827*?0.1504*?0.5672103|104(0.010)(0.019)(0.015)(0.025)(0.062)(0.085)(0.068)(0.515)Spain 0.2958*0.6943*0.04840.0993*0.3125 4.8436*?0.3706*?0.9556105|105(0.051)(0.071)(0.025)(0.037)(0.506)(0.502)(0.160)(0.764)Sweden 0.00090.8883*0.00320.05350.12230.2158?0.0172?0.1134104|105
(0.072)(0.059)(0.027)(0.038)(0.140)(0.167)(0.127)(0.366)Switzerland 0.2207*0.7317*0.03570.0523?0.00240.8582*?0.4029*?2.3471*104|105
(0.036)(0.082)(0.021)(0.030)(0.097)(0.085)(0.175)(0.132)United Kingdom 0.0393*0.9036*0.0450*0.0164?0.0103*0.1071*?0.0081?0.3577*104|105
(0.007)(0.016)(0.010)(0.011)(0.005)(0.018)(0.005)(0.115)United States 0.0052*0.9193*0.0554*0.0231*0.00070.2241*0.0083?0.7909*105|106
(0.002)
(0.005)
(0.007)
(0.007)
(0.003)(0.017)(0.005)(0.168)
Estimated coef?cients from a GJR-GARCH model with expected and unexpected components of futures trading activity
variables:
22
h ????h ??e ??max(0,?e )t 01t ?12t ?13t ?1??ExpVol ??UnexpVol ??ExpOI ??UnexpOI
4567For computational reasons,the volume and open interest series are standardized to have a mean between zero and one.Scaling units are reported below the country name.An asterisk indicates statistical signi?cance at the 5%level.
678Gulen and Mayhew
tive effect on volatility.This is what we would expect to see if exogenous
volatility events cause high trading volume.The expected component of
futures volume,on the other hand,had no robust signi?cant effect on
volatility—there is a signi?cant positive effect in Denmark,Germany,and
Hong Kong,a signi?cant negative effect in Austria and the UK,and no
signi?cant effect in the other12countries.
It should be noted that we made no effort to model the seasonality in volume and open-interest data that was associated with the rolling over
of contracts prior to monthly expiration dates.Thus,in our ARIMA de-
composition,we may be erroneously classifying some predictable trading
volume as unpredictable.An interesting topic for future research would
be to investigate whether accounting for this seasonality has a signi?cant
impact on the decomposition of permanent and temporary components
of trading activity.
MODELING THE JOINT DYNAMICS OF
COUNTRY AND WORLD VOLATILITY
The univariate models we employed above do not allow for time-varying
conditional covariance between the country and world returns.If the
conditional covariance changes systematically with the introduction of
stock index futures,then our previous results may be biased.
In this section,we will address this problem by estimating the joint dynamics of each country’s return with the world-market return in a mul-
tivariate GARCH framework that allows for time-varying conditional co-
variance.Because we wished to capture the dynamic interaction between
world-market volatility,country-speci?c volatility,and conditional covar-
iance,we used the BEKK speci?cation of Engle and Kroner(1995).9
Unlike certain other well-known multivariate GARCH models,the BEKK
model allows conditional variances and covariances to in?uence each
other.10
For each country i,we estimated the following bivariate process:
55
R?a?a R?b DAY?e
i,t0?j i,t?j?k k i,t
j?1k?2
55
R?w?w R?d DAY?e
w,t0?j w,t?j?k k w,t
j?1k?2
where the error terms are multivariate normal:
9This model also has been used by Karolyi(1995)to model the joint dynamics of stock returns in
Canada and the United States.
10For a comparison of BEKK and other multivariate GARCH models,see Kroner and Ng(1998).
Stock Index Futures Trading 679
e |F ?N (0,H )
t t ?1t with conditional covariance matrix
H ?C ?C ?A ?e e ?A ?G ?H G ?U D .
t t ?1t ?1t ?1t In the mean equations,R i,t represents the log country-index return,
R w,t is the contemporaneous log world-index return,and the variables DAY k are day-of-the-week dummies for Tuesday through Friday.In the conditional variance equations,the coef?cient matrix C represents a ma-trix of constants,A represents a matrix of ARCH coef?cients,G repre-sents a matrix of GARCH coef?cients,and U represents a matrix of dummy coef?cients.Matrices H ,C ,G ,and U are symmetric.Our main purpose in using the multivariate GARCH model was to better correct for the effect of world-market movements,not to test whether futures listing in individual countries in?uenced world-market volatility.Thus,we did not include a dummy variable for futures listing in the conditional-variance equation for world returns.That is,d 22of U is set to 0.
In summary,
2
?a a e e e a a 11121,t ?1
1,t ?12,t ?11112
H ?C ?C ?
2t ?????
?
a a e e e a a 21222,t ?11,t ?12,t ?1
2122
?g g g g d d 111211121112
?
H ?D t ?1t
?
?????
g g g g d 0
1222122212The interpretation of the dummy coef?cient in a country’s conditional-variance equation is analogous to the dummy in the univariate GARCH
equations analyzed above—a negative coef?cient indicates that the intro-duction of futures corresponds to a volatility decrease.By including the dummy variable in the equation governing the conditional covariance of a country’s return with the world-market return,we attempted to test whether futures introduction has any impact on the extent to which the country’s stock market is integrated into the world market.
Maximum-likelihood estimates of these parameters are reported in Table VII.In the interest of space,we have reported only those coef?-cients that we feel are most relevant to the issue at hand.Examining the coef?cients on the futures introduction dummy variable in the country-speci?c-conditional-volatility equation,we found a signi?cant volatility increase in four countries (Germany,Japan,Hungary,and Spain)and a signi?cant decrease in 12countries.Note that under this speci?cation,the volatility effect for the United States is still positive but is no longer
680Gulen and Mayhew
TABLE VII
Joint Dynamics of Country and World Returns
Country c11c12a11a12a21g11g12d11d12
Australia0.3173*0.03390.0101*?0.01320.7291*0.2779*0.1461*?0.0937*0.0009
(0.0284)(0.0183)(0.0083)(0.0077)(0.0151)(0.0251)(0.0543)(0.0194)(0.0179)
Austria0.0041*0.00140.2367*0.0113*0.01420.9446*0.9140*0.00170.0010
(0.0013)(0.0017)(0.0060)(0.0045)(0.0156)(0.0026)(0.0061)(0.0015)(0.0014)
Belgium0.0574*?0.00140.2852*0.0253*0.2637*0.7452*0.6992*0.00250.0132*
(0.0079)(0.0038)(0.0204)(0.0101)(0.0110)(0.0221)(0.0192)(0.0058)(0.0046)
Canada0.0235*0.01620.3470*0.0192*0.00540.8420*0.8412*?0.0040*?0.0052*
(0.0021)(0.0015)(0.0067)(0.0054)(0.0063)(0.0056)(0.0059)(0.0014)(0.0010)
Chile0.1718*?0.00840.4538*0.0270*0.01790.3724*0.0449?0.0719*0.0688*
(0.0205)(0.0268)(0.0127)(0.0052)(0.0299)(0.0123)(0.0472)(0.0160)(0.0290)
Denmark0.45768?0.01120.3240*0.00550.2547*0.4579*0.2963*?0.2076*0.0618*
(0.0334)(0.0086)(0.0157)(0.0073)(0.0177)(0.0379)(0.0543)(0.0153)(0.0115)
Finland0.0305*0.00690.4030*0.00070.0890*0.7907*0.6112*0.0283*0.0089
(0.0051)(0.0081)(0.0140)(0.0070)(0.0110)(0.0104)(0.0306)(0.0046)(0.0084)
France0.0930*?0.00090.2979*0.0315*0.00440.8623*0.7881*?0.02470.0246*
(0.0233)(0.0081)(0.0173)(0.0083)(0.0203)(0.0145)(0.0146)(0.0177)(0.0080)
Germany0.0358*0.0143*0.3297*0.0408*0.01770.8585*0.6734*0.0198*0.0298*
(0.0036)(0.0036)(0.0102)(0.0059)(0.0114)(0.0083)(0.0217)(0.0030)(0.0043)
Hong Kong0.0706*0.00210.4153*0.0104*0.1460*0.8149*0.7488*?0.00460.0201*
(0.0072)(0.0031)(0.0082)(0.0019)(0.0119)(0.0050)(0.0190)(0.0071)(0.0040)
Hungary0.1650*?0.0099*0.5453*0.0109*0.3808*0.5829*0.8104*0.0553*0.0181*
(0.0161)(0.0038)(0.0193)(0.0029)(0.0221)(0.0183)(0.0257)(0.0234)(0.0059)
Israel0.1294*0.0799*0.3963*?0.0213*?0.1335*0.8059*0.0130?0.02070.0387
(0.0312)(0.0315)(0.0231)(0.0049)(0.0593)(0.0222)(0.3260)(0.0228)(0.0362)
Italy0.0486*0.0058*0.2558*?0.00240.01270.9021*0.8668*0.00230.0058*
(0.0043)(0.0015)(0.0080)(0.0030)(0.0100)(0.0060)(0.0116)(0.0055)(0.0029)
Japan0.0181*?0.0047*0.3014*0.00520.1747*0.8525*0.8792*0.0317*0.0145*
(0.0022)(0.0011)(0.0096)(0.0029)(0.0069)(0.0063)(0.0047)(0.0035)(0.0012)
Korea0.1169*0.0567*0.3488*?0.0603*0.1249*0.8262*?0.02840.0482?0.0062
(0.0193)(0.0200)(0.0186)(0.0050)(0.0416)(0.0163)(0.1988)(0.0307)(0.0437)
Malaysia0.1102*0.0112*0.3904*0.0191*0.0989*0.7853*0.6658*?0.0388*0.0135
(0.0071)(0.0045)(0.0108)(0.0041)(0.0122)(0.0104)(0.0284)(0.0075)(0.0075)
Netherlands0.0944*0.00180.2619*0.0262*0.0977*0.8674*0.8139*?0.0538*0.0066*
(0.0081)(0.0035)(0.0094)(0.0050)(0.0139)(0.0077)(0.0097)(0.0053)(0.0029)
Norway0.1132*0.0473*0.4149*0.01120.1566*0.7381*0.3568*?0.0486*0.0125
(0.0077)(0.0027)(0.0115)(0.0074)(0.0095)(0.0115)(0.0366)(0.0060)(0.0084)
Portugal0.0247*?0.00400.4738*0.05310.1618*0.7403*0.3218*?0.00330.0425*
(0.0043)(0.0061)(0.0190)(0.0162)(0.0233)(0.0161)(0.1013)(0.0059)(0.0135)
South Africa0.9573*0.1467*0.3228*?0.0585*0.2147*0.6498*0.8169*?0.8027*?0.1239*
(0.0946)(0.0212)(0.0204)(0.0019)(0.0212)(0.0306)(0.0262)(0.0786)(0.0176)
Spain0.0407*0.0073*0.3049*0.0164*?0.0292*0.8744*0.8396*0.0089*0.0042
(0.0045)(0.0024)(0.0129)(0.0065)(0.0060)(0.0109)(0.0111)(0.0037)(0.0028)
Sweden0.0953*?0.01740.2828*0.0180*0.0874*0.8629*0.7726*?0.0300*0.0314*
(0.0177)(0.0095)(0.0137)(0.0068)(0.0176)(0.0113)(0.0127)(0.0145)(0.0098)
Switzerland0.2279*?0.01000.2863*0.02000.3394*0.6520*0.6596*?0.0667*0.0239*
(0.0240)(0.0090)(0.0229)(0.0105)(0.0176)(0.0288)(0.0172)(0.0117)(0.0093)
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函数实现行列汇总查询。
Excel函数公式完整版
EXCEL函数公式大全(完整) 函数说明 CALL调用动态链接库或代码源中的过程 EUROCONVERT用于将数字转换为欧元形式，将数字由欧元形式转换为欧元成员国货币形式，或利用欧元作为中间货币将数字由某一欧元成员国货币转化为另一欧元成员国 货币形式（三角转换关系） GETPIVOTDATA返回存储在数据透视表中的数据 REGISTER.ID返回已注册过的指定动态链接库(DLL) 或代码源的注册号 SQL.REQUEST连接到一个外部的数据源并从工作表中运行查询，然后将查询结果以数组的形式返回，无需进行宏编程 ?数学和三角函数 ?统计函数 ?文本函数 加载宏和自动化函数 多维数据集函数 函数说明 CUBEKPIMEMBER返回重要性能指标(KPI) 名称、属性和度量，并显示单元格中的名 称和属性。KPI 是一项用于监视单位业绩的可量化的指标，如每月 总利润或每季度雇员调整。 CUBEMEMBER返回多维数据集层次结构中的成员或元组。用于验证多维数据集内 是否存在成员或元组。 CUBEMEMBERPROPERTY返回多维数据集内成员属性的值。用于验证多维数据集内是否存在 某个成员名并返回此成员的指定属性。 CUBERANKEDMEMBER返回集合中的第n 个或排在一定名次的成员。用于返回集合中的一 个或多个元素，如业绩排在前几名的销售人员或前10 名学生。 CUBESET通过向服务器上的多维数据集发送集合表达式来定义一组经过计算 的成员或元组（这会创建该集合），然后将该集合返回到Microsoft Office Excel。 CUBESETCOUNT返回集合中的项数。 CUBEVALUE返回多维数据集内的汇总值。
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示公式向
建设工程经济计算公式汇总
一级建造师《建设工程经济》计算公式汇总 1、等额支付系列的终值、现值、资金回收和偿债基金计算 等额支付系列现金流量序列是连续的，且数额相等，即： ） ，，，，常数（n t A A t 321 ①终值计算（即已知A 求F ） i i A F n 11 ）（ ②现值计算（即已知A 求P ） n n n i i i A i F P ）（）（） （ 1111 ③资金回收计算（已知P 求A ） 111 n n i i i P A ）（） （ ④偿债基金计算（已知F 求A ） 1 1 n i i F A ）（ 2、有效利率的计算 包括计息周期有效利率和年有效利率两种情况。 （2）年有效利率，即年实际利率。 年初资金P ，名义利率为r ，一年内计息m 次，则计息周期利率为 m r i 。根据一次支付终值公式可得该年的本利和F ，即： m m r P F 1 根据利息的定义可得该年的利息I 为： 111m m m r P P m r P I 再根据利率的定义可得该年的实际利率，即有效利率i eFF 为： 11i eff m m r P I 3、财务净现值 t c t n t i CO CI FNPV 10 式中 FNPV ——财务净现值； （CI-CO ）t ——第t 年的净现金流量（应注意“＋”、“-”号）； i c ——基准收益率； n ——方案计算期。 4、财务内部收益率（FIRR ——Financial lnternaI Rate oF Return ） 其实质就是使投资方案在计算期内各年净现金流量的现值累计等于零时的折现率。其数学表达式为：
t t n t FIRR CO CI FIRR FNPV 10 式中 FIRR ——财务内部收益率。 5、投资收益率指标的计算 是投资方案达到设计生产能力后一个正常生产年份的年净收益总额（不是年销售收入）与方案投资总额（包括建设投资、建设期贷款利息、流动资金等）的比率： %100 I A R 式中 R ——投资收益率； A ——年净收益额或年平均净收益额； I ——总投资 6、总投资收益率 总投资收益率（ROI ）表示总投资的盈利水平 %100 TI EBIT ROI 式中 EBIT-----技术方案正常年份的年息税前利润或运营期内平均息税前利润； TI------技术方案总投资包括建设投资、建设期利息和全部流动资金。 7、资本金净利润率（ROE ） 技术方案资本金净利润率（ROE ）表示技术方案盈利水平 %100 EC NP ROE 式中 NP----技术方案正常年份的年净利润或运营期内年平均净利润， 净利润＝利润总额-所得税 EC----技术方案资本金 8、静态投资回收期 ·当项目建成投产后各年的净收益（即净现金流量）均相同时，静态投资回收期计算： A I P t 式中 I ——总投资； A ——每年的净收益。 ·当项目建成投产后各年的净收益不相同时，静态投资回收期计算： 流量 出现正值年份的净现金的绝对值 上一年累计净现金流量现正值的年份数累计净现金流量开始出 1－ t P 9、借款偿还期 余额 盈余当年可用于还款的盈余当年应偿还借款额 的年份数借款偿还开始出现盈余 1－d P 10、利息备付率 利息备付率＝息税前利润/计入总成本费用的应付利息。 式中：息税前利润——即利润总额与计入总成本费用的利息费用之和（不含折旧、摊销费 11、偿债备付率 偿债备付率＝（息税前利润加折旧和摊销-企业所得税）/应还本付息的金额 式中：应还本付息的资金——包括当期还贷款本金额及计入总成本费用的全部利息； 息税前利润加折旧和摊销-企业所得税＝净利润+折旧+摊销+利息 12、总成本 C ＝C F ＋C u ×Q C ：总成本；C F ：固定成本；C u ：单位产品变动成本；Q ：产销量 量本利模型
Excel中index和match函数的应用实例
Excel中index和match函数的应用实例 原文出处https://www.wendangku.net/doc/4b14736951.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)。但是为什么检索出来的行号会是错误值呢？
电子表格常用函数公式
电子表格常用函数公式 1、自动排序函数： =RANK(第1数坐标，$第1数纵坐标$横坐标：$最后数纵坐标$横坐标，升降序号1降0升) 例如：=RANK(X3,$X$3:$X$155,0) 说明：从X3 到X 155自动排序 2、多位数中间取部分连续数值： =MID（该多位数所在位置坐标，所取多位数的第一个数字的排列位数，所取数值的总个数） 例如：612730************在B4坐标位置，取中间出生年月日，共8位数 =MID(B4，7，8) =19820711 说明：B4指该数据的位置坐标，7指从第7位开始取值，8指一共取8个数字 3、若在所取的数值中间添加其他字样， 例如：612730************在B4坐标位置，取中间出生年、月、日，要求****年**月**日格式 =MID(B4，7，4)&〝年〞&MID(B4，11，2) &〝月〞& MID(B4，13，2) &〝月〞&
=1982年07月11日 说明：B4指该数据的位置坐标，7、11指开始取值的第一位数排序号，4、2指所取数值个数，引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板：首先利用Word制作一个奖状模板并保存为“奖状.doc”，将其中班级、姓名、获奖类别先空出，确保打印输出后的格式与奖状纸相符（如图1所示）。 第二步用Excel建立获奖数据库：在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”，格式如图2所示。 第三步关联数据库与奖状：打开“奖状.doc”，依次选择视图→工具栏→邮件合并，在新出现的工具栏中选择“打开数据源”，并选择“奖状数据.xls”，打开后选择相应的工作簿，默认为sheet1，并按确定。将鼠标定位到需要插入班级的地方，单击“插入域”，在弹出的对话框中选择“班级”，并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印：选择“查看合并数据”，然后用前后箭头就可以浏览合并数据后的效果，选择“合并到新文档”可以生成一个包含所有奖状的Word文档，这时就可以批量打印了。
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函数有两种语法形式数组和引用。使用数组形式，返回值;使用引用形式，返回引
建筑工程工程量计算公式
、平整场地：建筑物场地厚度在±30cm 以内的挖、填、运、找平。 1、平整场地计算规则 (1)清单规则：按设计图示尺寸以建筑物首层面积计算。 (2 )定额规则：按设计图示尺寸以建筑物外墙外边线每边各加2 米以平方米面积计算。 2、平整场地计算公式 S= (A+4 ) X ( B+4 ) =S 底+2L 外+16 式中：S———平整场地工程量；A———建筑物长度方向外墙外边线长度；B———建筑物宽度方向外墙外边线长度；S 底———建筑物底层建筑面积；L 外———建筑物外墙外边线周长。 该公式适用于任何由矩形组成的建筑物或构筑物的场地平整工程量计算。 二、基础土方开挖计算 开挖土方计算规则 ( 1 )、清单规则：挖基础土方按设计图示尺寸以基础垫层底面积乘挖土深度计算。 ( 2)、定额规则：人工或机械挖土方的体积应按槽底面积乘以挖土深度计算。槽底面积应以槽底的长乘以槽底的宽，槽底长和宽是指基础底宽外加工作面，当需要放坡时，应将放坡的土方量合并于总土方量中。 2 、开挖土方计算公式： (1) 、清单计算挖土方的体积：土方体积=挖土方的底面积X挖土深度。 (2) --------------------------------------------------------------------------------------------- 、定额规则：基槽开挖：V= (A+2C+X H) HXL。式中：V --------------------------------------------------------- 基槽土方量；A ----------- 槽底宽度；C———工作面宽度；H———基槽深度；L———基槽长度。. 其中外墙基槽长度以外墙中心线计算，内墙基槽长度以内墙净长计算，交接重合出不予 扣除。 基坑体积；A—基坑开挖：V=1/6H[A X B+a X b+(A+a) x(B+b)+a xb]。式中：V 基坑上口长度；B———基坑上口宽度；a———基坑底面长度；b———基坑底面宽度。
EXCEL表格函数公式大全
Excel常用函数公式及技巧搜集（常用的） 【身份证信息？提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式） =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可） =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日")))) 显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd） =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") （最简单公式） =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从身份证号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”) ,TODAY(),”Y”),) =DATEDIF（A1，TODAY（），“Y”） （以上公式会判断是否已过生日而自动增减一岁） =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-VALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄
excel中INDEX函数如何使用
excel中INDEX函数如何使用 INDEX用于返回表格或区域中的数值或对数值的引用。 函数INDEX() 有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。 （1）INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。Array为单元格区域或数组常数。Row_num为数组中某行的行序号，函数从该行返回数值。Column_num为数组中某列的列序号，函数从该列返回数值。需注意的是Row_num 和column_num 必须指向array 中的某一单元格，否则，函数INDEX 返回错误值#REF!。（2）INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区域的引用。 Reference为对一个或多个单元格区域的引用。 Row_num为引用中某行的行序号，函数从该行返回一个引用。 Column_num为引用中某列的列序号，函数从该列返回一个引用。 需注意的是Row_num、column_num 和area_num 必须指向reference 中的单元格；否则，函数INDEX 返回错误值#REF!。如果省略row_num 和column_num，函数INDEX 返回由area_num 所指定的区域。 1. INDEX函数的用途是返回列表或数组中的指定值。 公式：INDEX(ARRAY,ROW-NUM,CLUMN-NUM) 例：返回A1：C10区域，第五行，第二列的值！ =INDEX（A1:C10，5，2） 2. INDEX数组函数：返回多个条件下指定的数值. 例：显示条件为D2:D13=D4、并且F2:F13=F4情况下，E列中对应的值什么？ ={INDEX（A1:F13,SUM(IF((D2:D13=D4)*(F2:F13=F4),ROW(E2:E13))),COLUMN(E2))} 或={INDEX（A1:F13,SMALL(IF((D2:D13=D4)*(F2:F13=F4),ROW(E2:E13))),COLUMN(E2))} 公式输入完成后，同时按下CTRL+SHIFT+ENTER，公式会自动出现大括号，表明使用数组函数。 3、INDEX函数与MATCH函数联用： 3.1MATCH函数是返回在指定方式下与指定数值匹配的数组中元素的相应位置。 公式：MATCH(LOOKUP_VALUE,LOOKUP_ARRAY,MA TCH_TYPE) LOOKUP_VALUE为在数据表中查找的数值 LOOKUP_ARRAY为包括要查找的连续单元格区域 MATCH-TYPE为数字1，0，-1 1表示查找小于或等于LOOKUP_value的最大值，区域需升序排列 -1表示查找大于或等于LOOKUP_VALUE的最小值，区域需降序排列 0表示查找等于LOOKUP_VALUE的第一个值，不需排列 例： A B C 1 吉林长久66666 双桥双胎 2 吉林安顺99999 单桥双胎 求：66666在B列的第几行？ =MATCH("66666"，B1:B2，0）返回“1”。 求“66666”对应的公司是什么？ =INDEX(A:A，MATCH("66666"，B1:B2,0),column(A1)) 公式输入后同时按下CTRL+SHIFT+ENTER 返回“吉林长久”。
INDEX函数
INDEX函数(有数组和引用两种用法。) 贡献者：843211日期：2008-07-22 阅读：14577 相关标签：et2007> 公式> 函数> 函数类型> 查找与引用函数> INDEX 返回表或区域中的值或值的引用。函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。 INDEX(array,Row_num,column_num) 返回数组中指定单元格或单元格数组的数值。INDEX(reference,Row_num,column_num,area_num) 返回引用中指定单元格区域的引用。语法1（数组） 语法1（数组） 贡献者：wendy日期：2009-03-17 阅读：5269 相关标签：et2007> 公式> 函数> 函数类型> 查找与引用函数> INDEX1 返回数据清单或数组中的元素值，此元素由行序号和列序号的索引值给定。 语法 INDEX(array,Row_num,column_num) Array 为单元格区域或数组常量。 ?如果数组只包含一行或一列，则相对应的参数Row_num 或column_num 为可选。 ?如果数组有多行和多列，但只使用Row_num 或column_num，函数INDEX 返回数组中的整行或整列，且返回值也为数组。 Row_num 数组中某行的行序号，函数从该行返回数值。如果省略Row_num，则必须有column_num。 Column_num 数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有Row_num。 说明
?如果同时使用Row_num 和column_num，函数INDEX 返回Row_num 和column_num 交叉处的单元格的数值。 ?如果将Row_num 或column_num 设置为0，函数INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将INDEX 函数以数组公式（数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号({ }) 中。按Ctrl+Shift+Enter 可以输入数组公式。）形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按Ctrl+Shift+Enter。 ? Row_num 和column_num 必须指向array 中的某一单元格；否则，函数INDEX 返回错误值#REF!。 示例 如果您将示例复制到空白工作表中，可能会更易于理解该示例。 示例一： A B 1 数据数据 2 苹果柠檬 3 香蕉梨 公式说明（结果） =INDEX(A2:B3,2,2) 返回单元格区域的第二行和第二列交叉处的值（梨） =INDEX(A2:B3,2,1) 返回单元格区域的第二行和第一列交叉处的值（香蕉） 示例二： A B 1公式说明（结果） 2=INDEX({1,2;3,4},1,2) 返回数组常量中第一行、第二列的值(2) 提示 示例中的公式必须以数组公式的形式输入。在将示例复制到空白工作表中后，请选中以公式单元格开始的单元格区域A2:A3，按F2，再按Ctrl+Shift+Enter。如果公式不是以数组公
建设项目工程经济计算公式汇总
.. 一级建造师《建设工程经济》计算公式汇总 1、等额支付系列的终值、现值、资金回收和偿债基金计算 等额支付系列现金流量序列是连续的，且数额相等，即： ） ，，，，常数（n t A A t 321①终值计算（即已知 A 求F ） i i A F n 1 1 ）（②现值计算（即已知 A 求P ） n n n i i i A i F P ） （）（） （1111 ③资金回收计算（已知 P 求A ） 1 1 1 n n i i i P A ）（）（④偿债基金计算（已知 F 求A ） 1 1 n i i F A ） （2、有效利率的计算 包括计息周期有效利率和年有效利率两种情况。（2）年有效利率，即年实际利率。年初资金P ，名义利率为r ，一年内计息m 次，则计息周期利率为 m r i 。根据一次支付终值公式可得该年的 本利和F ，即： m m r P F 1 根据利息的定义可得该年的利息I 为： 1 1 1 m m m r P P m r P I 再根据利率的定义可得该年的实际利率，即有效利率 i eFF 为： 1 1 i eff m m r P I 3、财务净现值 t c t n t i CO CI FNPV 1 式中 FNPV ——财务净现值； （CI-CO ）t ——第t 年的净现金流量（应注意“＋” 、“-”号）； i c ——基准收益率；n ——方案计算期。
.. 4、财务内部收益率（FIRR ——Financial lnternaI Rate oF Return ） 其实质就是使投资方案在计算期内各年净现金流量的现值累计等于零时的折现率 。其数学表达式为： t t n t FIRR CO CI FIRR FNPV 10 式中 FIRR ——财务内部收益率。 5、投资收益率指标的计算 是投资方案达到设计生产能力后一个正常生产年份的年净收益总额（ 不是年销售收入）与方案投资总额（包括 建设投资、建设期贷款利息、流动资金等） 的比率： % 100I A R 式中 R ——投资收益率； A ——年净收益额或年平均净收益额；I ——总投资 6、总投资收益率 总投资收益率（ROI ）表示总投资的盈利水平 % 100TI EBIT ROI 式中 EBIT-----技术方案正常年份的年息税前利润或运营期内平均息税前利润； TI------技术方案总投资包括建设投资、建设期利息和全部流动资金。7、资本金净利润率（ ROE ） 技术方案资本金净利润率（ ROE ）表示技术方案盈利水平 % 100EC NP ROE 式中 NP----技术方案正常年份的年净利润或运营期内年平均净利润，净利润＝利润总额-所得税 EC----技术方案资本金 8、静态投资回收期 ·当项目建成投产后各年的净收益（即净现金流量）均相同时，静态投资回收期计算： A I P t 式中 I ——总投资；A ——每年的净收益。 ·当项目建成投产后各年的净收益不相同时，静态投资回收期计算： 流量 出现正值年份的净现金 的绝对值 上一年累计净现金流量 现正值的年份数 累计净现金流量开始出 1 －t P 9、借款偿还期 余额 盈余当年可用于还款的 盈余当年应偿还借款额的年份数 借款偿还开始出现盈余 1 －d P 10、利息备付率 利息备付率＝息税前利润 /计入总成本费用的应付利息。 式中：息税前利润——即利润总额与计入总成本费用的利息费用之和（不含折旧、摊销费 11、偿债备付率 偿债备付率＝（息税前利润加折旧和摊销-企业所得税）/应还本付息的金额 式中：应还本付息的资金——包括当期还贷款本金额及计入总成本费用的全部利息； 息税前利润加折旧和摊销 -企业所得税＝净利润 +折旧+摊销+利息
excel表格常用的函数公式
e x c e l表格常用的函数公 式 Prepared on 22 November 2020
1、如何一次性去掉诸多超链接 选中所有的超链接，按住Ctrl+c再按Enter键，就取消的所有的超链。 2、如何在每行的下面空一行 如A1列有内容，我们需要在B1、C2单元格输入1，选中周边四格 ，然后向下拉，填充序列，然后在选取定位条件，选中空值，最后点击插入行，就行了。 3、删除一列的后缀
若A1为此，在B1单元格输入=LEFT（A1，LEN （A1）-4），然后下拉填充公式。 删除前缀则相反RIGHT 4、把多个单元格串成一句 运用=CONCATENATE（“A1”，“B2”，“C2”），比如A1,B1单元格分别是8，个，我们可在C1单元格输入=CONCATENATE("我有",A1,B1,"苹果")，随即C1单元格显示我有8个苹果。 5、数据分类汇总后按需排序 在数据分类汇总后，我们选择左侧2，把数据折叠起来，然后选中你按需排序规则的那行，点击排序即可。 6、分类汇总后，只复制汇总的项 在把分类汇总后的数据折叠后（只显示分类汇总项），然后选中这些，定位——可见单元格——复制——黏贴即可。 7、【Vlookup函数】查找制定目标的相对应数值 公式：B13=VLOOKUP(A13,$B$2:$D$8,3,0) A13是所需要的值对应的属性（姓名）；$B$2:$D$8是指查找的范围从B2开始一直到D8的区间范围内；3是指查找范围的第三列，即查找值所在的列；0表示精确查找或者也可填写false。 8、【sumif函数】在一定条件下求和 G2=sumif（D2：D8，”>=95”）
index函数
index函数编辑词条 INDEX函数是返回表或区域中的值或对值的引用。函数INDEX()有两种形式:数组形式和引用形式。数组形式通常返回数值或数值数组;引用形式通常返回引用。 基本信息 ?外文名称 INDEX函数 ?参数 Array为单元格区域或数组常数 ?形式 数组形式和引用形式 ?应用领域 计算机 ?引用 返回表或区域中的值或对值 目录1函数语法 2数组形式 3引用形式 4说明 5示例 1 函数语法 2 数组形式 3 引用形式 4 说明 5 示例 回到顶部意见反馈 QQ空间新浪微博腾讯微博百度贴吧人人豆瓣 函数语法折叠编辑本段 语法: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 的第一个参数是数组常量，请使用数组形式。 INDEX(array,row_num,column_num) Array是一个单元格区域或数组常量。 · 如果数组中只包含一行或一列，则可以不使用相应的 row_num 或 column_num 参数。 · 如果数组中包含多个行和列，但只使用了 row_num 或 column_num，INDEX 将返回数组中整行或整列的数组。 Row_num用于选择要从中返回值的数组中的行。如果省略 row_num，则需要使用column_num。 Column_num用于选择要从中返回值的数组中的列。如果省略 column_num，则需要使用 row_num。 说明 1、如果同时使用了 row_num 和 column_num 参数，INDEX 将返回 row_num 和column_num 交叉处单元格中的值。 2、如果将 row_num 或 column_num 设置为 0(零)，INDEX 将分别返回整列或整行的值数组。要将返回的值用作数组，请在行的水平单元格区域和列的垂直单元格区域以数组公式 (数组公式:数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号({ }) 中。按 Ctrl+Shift+Enter 可以输入数组公式。)的形式输入 INDEX 函数。要输入数组公式，请按 Ctrl+Shift+Enter。 3、Row_num 和 column_num 必须指向数组中的某个单元格;否则，INDEX 将返回#REF! 错误值
Excel表格函数公式大全
E x c e l表格函数公式大全-标准化文件发布号：（9456-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII
目录按顺序整理，便于打印学习 EXCEL函数大全 (3) 1.数据库和清单管理函数 (3) 2.日期和时间函数 (3) 3.DDE 和外部函数 (4) 4.工程函数 (4) Excel2003常用函数 (6) 5.ABS函数 (6) 6.AND (7) 7.AVERAGE (7) 8.CELL (8) 9.CHOOSE (8) 10.COLUMN 函数 (9) 11.CONCATENATE函数 (9) 12.COUNT (10) 13.COUNTA (10) 14.COUNTIF (10) 15.DATEDIF函数 (11) 16.DATE函数 (11) 17.DAY函数 (12) 18.DCOUNT函数 (12) 19.FIND (13) 20.FREQUENCY函数 (13) 21.IF (13) 22.INDEX (14) 23.INT (15) 24.ISERROR函数 (16) 25.ISEVEN (16) 26.ISODD (17) https://www.wendangku.net/doc/4b14736951.html,RGE (17) 28.LEFT或LEFTB (17) 29.LEN或LENB (18) 30.LOOKUP (18) 31.MATCH (19) 32.MAX (20) 33.MIN (21) 34.MEDIAN (21) 35.MID或MIDB (22) 36.MOD函数 (22) 37.MONTH函数 (23) 38.NOW (23) 39.OR (24) 40.RAND (24) 41.RANK函数 (25) 42.RIGHT或RIGHTB (25) 43.ROUND (26) 44.SUBTOTAL函数 (26) 45.SUM (27) 46.SUMIF (27) 47.TEXT (28) 48.TODAY (29) 49.VALUE (29) 50.VLOOKUP (30) 51.WEEKDAY函数 (31) 关于EXCEL中函数COUNT的用法 (31)
index函数和match函数
MATCH函数有两方面的功能，两种操作都返回一个位置值。 一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。 二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配. 语法结构为：MATCH(lookup_value,lookup_array,match_type) lookup_value为要搜索的值。 lookup_array：要查找的区域(必须是一行或一列)。 match_type：匹配形式，有0、1和－1三种选择："0"表示一个准确的搜索。"1"表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。"－1"表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回＃N/A。 比如：有一列数据：A1:A7 分别时A B C D E F G ，C1的值为F 这个时候D1输入=match(c1,a1:a7,0) 返回值就是6("F"在A1:A7中的行号） 这是基本的原理 经常和INDEX（）函数一起使用！ 比如：和上表一样，B1:B7为10 20 30 40 50 60 70 =index(B1:B7,MATCH(C1,A1:A7,0),0)就能返回“F”对应的B列的值60 INDEX可以实现反向查找，比VLOOKUP用法更加灵活 INDEX用于返回表格或区域中的数值或对数值的引用。 函数INDEX() 有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。 （1）INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。Array为单元格区域或数组常数。Row_num为数组中某行的行序号，函数从该行返回数值。Column_num为数组中某列的列序号，函数从该列返回数值。需注意的是Row_num 和column_num 必须指向array 中的某一单元格，否则，函数INDEX 返回错误值#REF!。（2）INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区