Structural safety criteria for blasting vibration based on wavelet packet energy spectra
Zhong Guosheng a ,b ,*,Li Jiang a ,Zhao Kui a
a School of Resources &Environmental Engineering,Jiangxi University of Science &Technology,Ganzhou 341000,China b
School of Civil &Transportation,South China University of Technology,Guangzhou 510641,China
a r t i c l e i n f o
Article history:
Received 24May 2010Accepted 2July 2010Keywords:
Blasting vibration
Wavelet packet transform Response energy Safety criteria
a b s t r a c t
Given multi-resolution decomposition of wavelet packet transforms,wavelet packet frequency band energy has been deduced from different bands of blasting vibration signals.Our deduction re ?ects the total effect of all three key elements (intensity,frequency and duration of vibration)of blasting vibration.We considered and discuss the dynamic response of structures and the effect of inherent characteristics of controlled structures to blasting vibration.Frequency band response coef ?cients for controlled structures by blasting vibration have been obtained.We established multi-factor blasting vibration safety criteria,referred to as response energy criteria.These criteria re ?ect the total effect of intensity,frequency and duration of vibration and the inherent characteristics (natural frequency and damping ratio)of dynamic responses from controlled structures themselves.Feasibility and reliability of the criteria are validated by an example.
Copyright ó2011,China University of Mining &Technology.All rights reserved.
1.Introduction
When we assess safety in blasting,we should ?rst of all establish safety criteria,which re ?ect factors impacting on seismic blasting.Practice shows that in many cases,a single particle velocity as the only indicator to measure seismic intensity of blasting does not re ?ect accurately the actual breakage that occurs in structures,because the frequency of seismic waves,duration of vibration and other factors were not considered [1e 3].Research shows that,damage to structures caused by blasting vibration is the result from a variety of factors,such as intensity,frequency and duration of vibration as well as the characteristics of a dynamic response of the controlled structures themselves [4,5].However,current safety standards have neither considered the duration of blasting vibra-tion nor the inherent properties of the controlled structures (i.e.,natural frequency and damping ratio).It is inevitable that there are some defects and shortcomings in carrying out safety measures.In order to advance new safety criteria to re ?ect all the factors involved in blasting vibration,should be put on the agenda as soon as possible [6,7].
Wavelet packet techniques have been used to analyze blasting vibration signals.Based on wavelet packet energy spectra and the structure of dynamic responses,new safety criteria have been
established,which re ?ect the total effect of the intensity,frequency and duration of vibration and the characteristics of dynamic responses of the controlled structures themselves.It is of theoret-ical and practical importance to assess accurately and rationally the seismic effect of blasting by these criteria.The feasibility and reliability of these criteria are validated by live data of blasting vibration.
2.Wavelet packet energy spectrum analysis of blasting vibration
2.1.Characteristics of wavelet packet analysis
Wavelet analysis decomposes signals into two parts:low-frequency and https://www.doczj.com/doc/ad691721.html,rmation lost in the low-frequency part could be obtained from the high-frequency part.In turn,the low-frequency part is decomposed into two parts and this decomposition can be continued to a number of deeper levels.From the structure of wavelet decomposition it can be seen that the frequency resolution of the wavelet transform reduces with increased frequency.Wavelet packet analysis (WPD)is different.It decomposes signals not only into low-frequency signals,but also into a high-frequency part.WPD can adaptively select the appropriate frequency spectrum to match the signal spectrum from the analysis of the signal characteristics and requirements,it is relatively re ?ned method.Many publications dwell on its
*Corresponding author.Tel.:t867978312259.E-mail address:zgs1001@https://www.doczj.com/doc/ad691721.html, (Z.
Guosheng).
Contents lists available at ScienceDirect
Mining Science and Technology (China)
journal ho mep age:
https://www.doczj.com/doc/ad691721.html,/locate/mstc
1674-5264/$e see front matter Copyright ó2011,China University of Mining &Technology.All rights reserved.doi:10.1016/j.mstc.2010.12.016
Mining Science and Technology (China)21(2011)35e 40
mathematical methodology and numerical calculations [8,9].They cannot all be discussed here for lack of space.2.2.Wavelet packet analysis of blasting vibration
The level of decomposition is determined by the signals them-selves and specialized instrumentation is used for acquiring blast-ing vibration data.The equipment used in our test was an IDTS 3850blasting vibration recorder,manufactured by the Chengdu VIDTS Dynamic Instrument Co.Ltd.Its minimum operating frequency is 1Hz.The main vibration frequency of blasting varia-tion is generally below 200Hz.According to sampling theory,the signal sampling frequency is set to 2000Hz,in which case its Nyquist frequency is 1000Hz [10].As a result,given the algorithm of wavelet packet analysis using binary-scale transformation,signals can be decomposed into seven levels,corresponding to the lowest band,i.e.,0e 7.8125Hz.Frequency bands of reconstructed signals at all levels of decomposed blasting vibration signals by wavelet packet,are shown in Table 1.2.3.Wavelet packet energy spectrum analysis of blasting vibration signals
When we decompose signal s (t ),using a wavelet packet,to level i ,we can obtain the 2i sub-band,i.e.,s (t )can be denoted as follows.
s et T?
X
2i
à1j ?0
f i ;j àt j á
?f i ;0et 0Ttf i ;1et 1Tt/tf i ;2i à1 t 2i à1
j ?0;1;2;/;2i à1
(1)
where f i ;j et j Tis the reconstructed signal at node (i ,j ).If the lowest
and maximum frequency of s (t )are 0and u m ,then the frequency width at the i th level is u m =2i .
According to the Parseval theorem [9]in spectrum analysis,we can obtain the wavelet packet energy spectrum of signal s (t ):
E i ;j àt j á?
Z
T
j f i ;j àt j á
j 2dt ?X m k ?1
j x j ;k j 2
(2)
where x j ;k (j ?0;1;2;/;2i à1;k ?1;2;/;m ;m denotes the number of discrete sampling points of seismic blasting)is the discrete point amplitude of the reconstructed signal f i ;j et j T.E i ;j et j Tis the frequency band energy when the signal decomposes to the i th level at the j th node.
The total energy of s (t )is obtained from Equation (2):
E ?
X
2i
à1j ?0
E i ;j àt j
á
(3)
The ratio,accounting for the energy of each band to total energy is
P i ;j
?E i ;j àt j á?100%
(4)
Equations.(1)and (2)show that the blasting vibration signals,decomposed by the wavelet packet into different frequency components,re ?ect the effect of the frequency of blasting vibration,as well as the band energy,while simultaneously indicating blast-ing vibration intensity and duration.As a result,the wavelet packet band energy obtained on this basis is able to re ?ect the effect of three elements of blasting vibration (vibration intensity,frequency and duration).
3.Criteria of security response energy of seismic blasting 3.1.Analysis of construction response to seismic blasting
Seismic blasting waves can be seen from the synthesis of a series of sine waves,i.e.:
A et T?
X n i ?1
A i sin eu i t t4i T(5)
where A i ,u i and 4i denote amplitude,frequency and initial phase of the i th sine wave respectively.
In order to simplify the calculation,the structure of the system is usually assumed to have a single degree of freedom,according to the principle of the balance of power [11]:
€x t2xu 0_x tu 20x ?
F i
m
sin u i t (6)
where u 0??????????
k =m p and x 0?c =2m u 0are the natural frequency and damping ratio of a structure.
Given the seismic blasting loading component force F i sin u i t ,the theory [12]of structural dynamics indicates that the magni ?-cation factor b ,when compared to corresponding displacement x,is the maximum construction displacement and calculated as:
b ?1
????????????????????????????????????????????????????????????à1àu 2=u 20á2t4x 20$àu 2=u 20
áq (7)
where u is the motivation frequency of seismic blasting;u 0and x 0are the natural frequency and damping ratios of a structure.
This shows that the magni ?cation factor b ,the response coef-?cient to blasting vibration of a structure,is a non-dimensional physical quantity.Equation (7)tells us that response factor of a controlled structure is closely related to its own natural frequency and damping ratio.When the motivation frequency of blasting vibration is close to the natural frequency and the damping ratio of the construction small,it can be concluded that the greater the vibration response factor,the greater the vibration response,but on the other hand,there will be less vibration response factor.As
Table 1
Range for frequency bands of reconstructed blasting vibration signals by wavelet packet analysis (Hz).Level S i ;0
S i ;1
S i ;2
.
S i ;j à1
S i ;j
10e 500500e 100020e 250250e 500500e 750750e 100030e 125125e 250250e 375.750e 875875e 100040e 62.562.5e 125125e 187.5.875e 937.5937.5e 100050e 31.2531.25e 62.562.5e 93.75.937.5e 968.75968.75e 100060e 15.62515.625e 31.2531.25e 46.875.968.75e 984.375984.375e 10007
0e 7.8125
7.8125e 15.625
15.625e 23.4375
.984.375e 992.1875
992.1875e 1000
Note:s ij denotes reconstructed signals at the i th level for the j th wavelet packet decomposition coef ?cient.i ?1;2;/;7;j ?0;1;2;/;2j à1.
Z.Guosheng et al./Mining Science and Technology (China)21(2011)35e 40
36
a result and given the vibration response factor of a structure,we can obtain its frequency band response coef?cient.
3.2.Determination of the frequency band response
coef?cient of a structure
Assuming u to be the motivation frequency,the natural frequency and damping ratio of a structure are u0and x0, respectively.According to Equation(7),the frequency band response coef?cient of a structure,by wavelet packet analysis,is as follows:
e i;j?1
u m
Z u i;j
u i;jà1
1
??????????????????????????????????????????????????????
à
1àu2=u20
á2
t4x20u2=u20
q d u(8)
where e i;j is response coef?cient when seismic blasts are decom-posed by wavelet packet to the i th level and j th node;u i;j and u i;jà1 are,respectively,the upper and lower limits of the frequency.
3.3.Criterion of response energy
Vibration energy is an important consequence from surface vibration,caused by the damage to a building from an explosion. Since blasting vibration is a broadband signal of non-stationary random signals,we used wavelet packet technology to analyze seismic speed signals in time and frequency domains and calcu-lated the energy spectrum of the wavelet packet.Given the various frequency impacts on a structure,the wavelet packet energy is multiplied by corresponding response factors of the band.By summing these products we can obtain the total response energy (expressed by E R),called the criterion of response energy,i.e.,the response energy E R is:
E R?X
2ià1
j?0
E i;j
à
t j
á
$e i;j(9)
where E i;jet jTdenotes band energy and e i;j the response coef?cient
of a seismic signal component at the i th level,for the j th node.
The wavelet packet band energy is able to re?ect the intensity,
frequency and duration of seismic blasting.The frequency band
response coef?cient re?ects the response level of inherent prop-
erties of a structure(natural frequency and damping ratio).As
a result,the criterion of the response energy established on this
basis will be able to consider seismic intensity,frequency and
duration,as well as the dynamic response characteristics of
a structure and describe accurately and comprehensively the level
of the response.
4.Examples in engineering
4.1.Test of seismic blasting
In order to validate the veracity and practical application of the
energy response criterion of seismic blasting,we tested it at an iron
ore mine.We speci?cally selected a similar type structure as our
test object.Before blasting,the structures were intact.A number of
investigators suggested that the use of particle vibration speed as
a standard for scale and to describe vibration intensity is more
appropriate for the removal of impacts on soil than using
displacement and acceleration[13,14].Therefore,we choose the
speed of particle vibration as the monitoring object in this test.The
results of this test are displayed in Fig.1and Table2.
4.2.Actual safety criteria of blasting vibration speed
and frequency in China
Table3presents criteria for several protected objects from
Blasting Safety Regulations(GB6722-2003)[15](referred to as the
Regulations).
Fig.1.Monitoring blasting vibration velocity as a function of time.
Table2
Result of blasting vibration monitoring.
Number Construction Structure PPV/(cm/s)Main
frequency/Hz Status after blasting
1House A Brick&stone1.6392 6.8359Small chinks 2House B Brick&stone1.98821.4844No destruction 3House C Brick&stone2.894830.2734No destruction 4Public house Brick&stone4.150447.8516No destruction Table3
Blasting vibration safety criteria established in China,2003.
Sort of protected object Speed within safe permissibility/(cm/s)
<10Hz10e50Hz50e100Hz
Caves,adobe and stone0.5e1.00.7e1.2 1.1e1.5 Brick,no aseismatic big block 2.0e2.5 2.3e2.8 2.7e3.0 Reinforced concrete 3.0e4.0 3.5e4.5 4.2e5.0 Ancient Buildings0.1e0.30.2e0.40.3e0.5 Tunnel of water conservancy
projects
7.0e15.0
Traf?c tunnel10.0e20.0
Mine laneway15.0e30.0
Z.Guosheng et al./Mining Science and Technology(China)21(2011)35e4037
(1)In our test,the results of the second and third observation
points are all within the prescribed limits of the Regulations.Buildings at these two points were not damaged,because they met the important criteria of the Regulations,established on the base of a considerable amount of investigation and statistics.
(2)Although the measurements at the ?rst observation points
met the criteria of the Regulations.House A was damaged all the same.However,measurements at the fourth set of observation points exceeded the values of the Regulations and this public building was not damaged.We conclude that there are some technical engineering inaccuracies in the Regulations.
(3)Although the Regulations suf ?ciently consider the impact on
structures by frequency of blasting vibration,it ignores the effect of the duration of the vibration and the inherent char-acteristics (natural frequency and damping ratio)of structures.So,what happened was inevitable.
4.3.Evaluating safety response energy criteria of blasting vibration 4.3.1.Wavelet packet energy spectrum analysis
The choice of the basis in wavelet analysis is an important problem,because analyses using a different bases but the same signal will produce different outcomes [8,13].One study indicated that db7and sym8are compactly supported,smooth and
symmetrically similar [13].They are the best wavelet bases to analyze non-stationary random signals.We analyzed blasting vibration signal 1into 7level wavelet packet decompositions with sym8,as shown in Fig.2.According to Equations (1)e (4),the wavelet packet energy spectrum,the band energy and its distri-bution are available (see Fig.2and Table 4).
The following conclusions are drawn from the analysis of the blasting vibration signals wavelet packet energy spectra:
(1)Fig.2and Table 4show that the four signals between 0and
203.125Hz account for 95.3044%,95.6787%,96.1285%and 97.3513%of total energy respectively.The energy of the distribution of blasting vibration is concentrated in 0e 200Hz range,so energy larger than 200Hz was ignored.
(2)In Table 4,energy of four signals in the 0e 62.5Hz range
account for 75.9462%,78.8273%,83.1498%and 84.2925%of total energy respectively.This implies that the dominant energy is mainly distributed in the main vibration
frequency,
Fig.2.Energy spectra of wavelet package for blasting vibration signals.
Table 4
Wavelet packet frequency band energy and distribution of blasting vibration signals.Frequency band/Hz
Singal-1Singal-2Singal-3Singal-4E /(cm/s)2
P /%E /(cm/s)2P /%E /(cm/s)2P /%E /(cm/s)2P /%0e 7.8125
0.0086471021.197000.00438530 4.906800.0055592 2.390400.00198080.611297.8125e 15.62500.0053600013.140000.0098769011.051000.0148910 6.403100.02314007.1412015.6250e 23.43750.00247600 6.069800.010*******.084000.0108570 4.668600.051063015.7580023.4375e 31.25000.0059694014.633000.0123280013.794000.084446036.31200.041590012.8350031.2500e 39.06250.00147520 3.616400.00327540 3.664900.0095638 4.112400.0073848 2.2790039.0625e 46.87500.00143000 3.505400.0113450012.694000.0121820 5.238300.045932014.1750046.8750e 54.68750.00148720 3.645600.0132580014.835000.023856010.25800.060725018.7400054.6875e 62.50000.0041362010.139000.00518140 5.797600.032017013.76700.041324012.7530062.5000e 70.31250.00056380 1.382100.000447960.501230.0024172 1.039400.00146310.4515270.3125e 78.12500.00046083 1.129700.000831280.930140.00189130.813260.00156060.4816278.1250e 85.93757.5791e-0050.185790.00132580 1.483500.00158770.682690.0047236 1.4577085.9375e 93.75000.000255440.626180.00111810 1.251100.00116730.501960.0044929 1.3865093.7500e 101.56250.00174290 4.272500.00317310 3.550500.0025757 1.107500.0063110 1.94760.
.
.
.
.
.
.
.
.
203.1250e 1000.00000.00191550 4.695600.00386200 4.321300.0090037 3.871500.0085829 2.64870P
0.04079300
100.00000
0.08937200
100.00000
0.2325600
100.00000
0.3240400
100.00000
Table 5
Dynamic response parameters of controlled structures.Construction Structure Floor Natural frequency u 0/Hz Damping ratio x 0House A Brick &stone 1 6.60.041House B Brick &stone 1 5.60.044House C
Brick &stone 2 4.50.043Public house
Brick
&stone
1
5.2
0.045
Z.Guosheng et al./Mining Science and Technology (China)21(2011)35e 40
38
divided into several sub-frequency bands.As a result,wavelet packet band energy can accurately describe the effect of frequency on blasting vibration.
4.3.2.Calculation of response energy
The response energy of four controlled buildings in our test can be estimated by the following steps:
(1)Obtain the dynamic response parameters of the four buildings
by impulse and vibration analysis(see Table5).
(2)Apply Equations.(8)and(9),wavelet packet frequency band
energy from Table4and the dynamic response parameters from Table5to obtain response parameters and response energy,as shown in Table6.
4.3.3.Analyzing criteria perpendicular of blast vibration response energy
Analyzing the four buildings in Table6,we conclude the following:
1)According to the actual speed-frequency safety criteria,house A
should not while the public building should have been damaged.However,given our criteria,the response energy of house A(0.028928)is larger than that of the public building
(0.019507),i.e.,house A suffered more serious damage than the
public building.It can be said that the response energy criteria are better than those of speed-frequency for describing the effect on controlled buildings.
2)Table6shows that the largest response energy(0.028928)was
from house A,much larger than that of house B(0.019507), house C(0.019283)and the public building(0.019507).
Considering that all four buildings are masonry structures,only the response energy of house A was larger than that of the threshold of masonry destruction,con?rmed by the results.
Therefore,the results from our method are completely consistent with the actual monitoring results.
5.Conclusions
Based on wavelet packet energy spectrum analysis and dynamics response characteristics,we established safety criter-ia e response energy criteria,which take intensity,frequency and duration into account.It is validated by an engineering application. Several conclusions are drawn:
1)The current“Blasting Safety Regulations”take fully into
account the vibration frequency impacting on structures,but ignore the inherent properties of structural material(natural frequency and damping ratio).As a result,the current speed-frequency criteria are imperfect.
2)Our response energy criteria re?ect intensity,frequency and
duration of blasting vibration,as well as dynamic response characteristics and therefore can depict their effect more accurately and comprehensively.However,for the determina-tion of destruction thresholds of various types of buildings,
a large amount of blasting vibration data still needs to be
obtained.
3)The establishment of safety response energy criteria of blasting
vibration requires a large amount of blasting vibration data of various industrial sectors.With our investigation we have laid
a theoretical and technical foundation for the establishment of
a complete and scienti?c set of safety criteria. Acknowledgments
Financial support for this work,provided by the National Natural Science Foundation of China(No.51064009),the National 11th Five-Year Science&Technology Program of China (No.2008BAB32B03),the Natural Science Foundation of Jiangxi Province(No.2009GQC0036)and the Youth Science Foundation of Education Department of Jiangxi Province(No.GJJ09515),is grate-fully acknowledged.
References
[1]Yang SQ,Liao XK,Liu BC.Default of the judging standard of blasting vibration
safety abstracts.Explosion and Shock Waves2001;21(3):223e8[in Chinese].
[2]Wang XG,Yu YL.On several problems of safety criterion for blasting vibration.
Engineering Blasting2001;7(2):88e92[in Chinese].
[3]Khandelwal M,Kankar PK,Harsha SP.Evaluation and prediction of blast
induced ground vibration using support vector machine.Mining Science and Technology2010;20(1):64e70.
[4]Li ZK,Wang AM,Li SC.Analysis for a triggered collapse in construction of an
underground powerhouse.Chinese Journal of Geotechnical Engineering 1997;19(5):74e9[in Chinese].
[5]Zhong GS,Xu GY,Xiong ZM.Application research of the energy analysis
method for blasting seismic signals based on wavelet transform.Explosion and Shock Waves2006;26(3):222e7[in Chinese].
[6]Li HN,Wang BQ,Lin G.Studies on some problems on explosion earthquake
responses.Explosion and Shock Waves1996;16(1):61e7[in Chinese].
Table6
Frequency response coef?cients and response energy for controlled structures.
Frequency band(Hz)House A House B House C Public house
E7;j(cm/s)2e7;j E7;j(cm/s)2e7;j E7;j(cm/s)2e7;j E7;j(cm/s)2e7;j
0e7.81250.00864710 2.83090000.00438530 2.589300000.0055592 2.234000000.0019808 2.45440000 7.8125e15.62500.005360000.65934000.009876900.375810000.01489100.207140000.02314000.30334000 15.6250e23.43750.002476000.13610000.010800000.094152000.01085700.058721000.05106300.08007800 23.4375e31.25000.005969400.06333800.012328000.044782000.08444600.028454000.04159000.03837000 31.2500e39.06250.001475200.03702400.003275400.026377000.00956380.016872000.00738480.02266000 39.0625e46.87500.001430000.02437400.011345000.017427000.01218200.011184000.04593200.01499000 46.8750e54.68750.001487200.01728800.013258000.012386000.02385600.007962600.06072500.01066100 54.6875e62.50000.004136200.01290900.005181400.009260200.03201700.005960000.04132400.00797430 62.5000e70.31250.000563800.01001200.000447960.007187500.00241720.004629400.00146310.00619120 70.3125e78.12500.000460830.00799330.000831280.005741700.00189130.003700000.00156060.00494680 78.1250e85.93757.5791e-0050.00653040.001325800.004692800.00158770.003025300.00472360.00404380 85.9375e93.75000.000255440.00543610.001118100.003907600.00116730.002519800.00449290.00336750 93.7500e101.56250.001742900.00459590.003173100.003304500.00257570.002131300.00631100.00284800 .........
203.1250e1000.00000.000548860.00119090.000285050.000857070.00144660.000553260.00243750.00073892 E R(cm/s)20.028928000.017179000.01928300.0195070
Z.Guosheng et al./Mining Science and Technology(China)21(2011)35e4039
[7]Chen M,Lu WB,Yi CP,Tang H.Research on safety standard for rock
anchor beam under blasting vibration in large underground powerhouse.
Chinese Journal of Rock Mechanics and Engineering2006;25(3):499e504[in Chinese].
[8]Mallat S.A theory for multiresolution signal decomposition:the wavelet
representation.IEEE Transactions Pattern Anal Machine Intelligence1989;11
(7):674e93.
[9]Hu CH,Zhang JB,Xia J,Zhang W.System analysis and design based on matlab-
wavelet analysis.Xi’an:Xidian University Press;1999[in Chinese].
[10]Zou YP,Li X.Signal transform and Processing.Wuhan:Huazhong University of
Science and Technology Press;1993[in Chinese].[11]Zhou DL,Shao GY.Modern testing technology and signal analysis.Xuzhou:
China University of Mining and Technology Press;2005[in Chinese].
[12]Yu ZD.Basic structural dynamics.Shanghai:Tongji University Press;1987[in
Chinese].
[13]Zhong GS.Applied fundamental research of blasting vibration analysis based
on wavelet transform[Ph.D.dissertation]:Changsha:Central South Univer-sity;2006[In Chinese].
[14]Yan HH,Li XJ,Qu YD,Ou YX.Fine analysis of blasting vibration velocity
testing.Rock and Soil Mechanics2007;28(10):2091e4[In Chinese].
[15]Standardization Administration of P.R.China.Blasting safety Regulation
(GB6722e2003).Beijing:Standards Press of China;2003[in Chinese].
Z.Guosheng et al./Mining Science and Technology(China)21(2011)35e40 40
Excel函数公式大全工作中最常用Excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 ? 2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数.
? 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 ? 2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
? 四、求和公式 1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 ? 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
? 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 ? 4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符*
VF常用函数列表数值函数： 1.绝对值和符号函数 格式：ABS（<数值表达式>） SIGN(<数值表达式>) 例如：ABS(-5)=5,ABS(4)=4,SIGN(8)=1,SIGN(-3)=-1,SIGN(0)=0 2.求平方根表达式 格式：SQRT(<数值表达式>) 例如：SQRT(16)=4,它与开二分之一次方等同。 3.圆周率函数 格式：PI() 4.求整数函数 格式：INT(<数值表达式>)返回数值表达式的整数部分 CEILING(<数值表达式>)返回大于或等于表达式的最小整数FLOOR(<数值表达式>)返回小于或等于表达式的最大整数 例如： INT(5.8)=5.8,INT(-7.8)=-7,CEILING(6.4)=7,CEILING(-5.9)=-5 FLOOR(9.9)=9 5.四舍五入函数 格式：ROUND(<数值表达式1>，<数值表达式2>) 功能：返回制定表达式在制定位置四舍五入的结果 例如：
ROUND(345.345,2)=345.35,ROUND(345.345,1)=345.3,ROUND(345.345,0)=345，ROUND(345.345,-1)=350 6.求余函数 格式:MOD(<数值表达式1>,<数值表达式2>) 例如： MOD(10,3)=1 MOD(10,-3)=-2 MOD(-10,3)=2 MOD(-10,-3)=-1 求余数的规律：1.首先按照两数的绝对值求余 2.表达式1的绝对值大于表达式2的绝对值，则余数为表达式1的值 3.余数取表达式1的正负号 4.若两数异好号，余数在加上表达式2的值为最终的结果 7. 求最大值和最小值函数 MAX(数值表达式列表) MIN (数值表达式列表) 例如：MAX(2,3,5)=5 MAX(…2?,?12?,?05?)=2 MAX(…汽车?,?飞机?,?轮船?) 字符串比较的规律： 字符串比较首先比较第一个字母,如果有结果那就不用在进行比较了。如果相等在进行第二个字母的比较，以次类推。 字符函数 1.求字符串长度函数 格式：LEN(<字符表达式>) 功能：返回制定字符表达式的长度，即所包含的字符个数。函数值为数值型 例如：X=“中文Visual FoxPro6.0” 则LEN(X)=20 2.大小写转换函数
excel常用函数及使用方法 一、数字处理 （一）取绝对值：=ABS(数字) （二）数字取整：=INT(数字) （三）数字四舍五入：=ROUND(数字,小数位数) 二、判断公式 （一）把公式返回的错误值显示为空： 1、公式：C2=IFERROR(A2/B2,"") 2、说明：如果是错误值则显示为空，否则正常显示。 （二）IF的多条件判断 1、公式：C2=IF(AND(A2<500,B2="未到期"),"补款","") 2、说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 （一）统计两表重复 1、公式:B2=COUNTIF(Sheet15!A:A,A2) 2、说明：如果返回值大于0说明在另一个表中存在，0则不存在。 （二）统计年龄在30~40之间的员工个数 公式=FREQUENCY(D2:D8,{40,29} （三）统计不重复的总人数 1、公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 2、说明：用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
（四）按多条件统计平均值 =AVERAGEIFS(D:D,B:B,"财务",C:C,"大专") （五）中国式排名公式 =SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9))) 四、求和公式 （一）隔列求和 1、公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3) 或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 2、说明：如果标题行没有规则用第2个公式 （二）单条件求和 1、公式：F2=SUMIF(A:A,E2,C:C) 2、说明：SUMIF函数的基本用法 （三）单条件模糊求和 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 （四）多条求模糊求和 1、公式：=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 2、说明：在sumifs中可以使用通配符* （五）多表相同位置求和 1、公式：=SUM(Sheet1:Sheet19!B2) 2、说明：在表中间删除或添加表后，公式结果会自动更新。
数据库函数： DA VERAGE 返回选择的数据库条目的平均值 DCOUNT 计算数据库中包含数字的单元格个数 DCOUNTA 计算数据库中的非空单元格 DGET 从数据库提取符合指定条件的单个记录 DMAX 返回选择的数据库条目的最大值 DMIN 返回选择的数据库条目的最小值 DPRODUCT 将数据库中符合条件的记录的特定字段中的值相乘DSTDEV 基于选择的数据库条目的样本估算标准偏差DSTDEVP 基于选择的数据库条目的总体计算标准偏差 DSUM 将数据库中符合条件的记录的字段列中的数字相加 DV AR 基于选择的数据库条目的样本估算方差 DV ARP 基于选择的数据库条目的样本总体计算方差GETPIVOTDATA 返回存储在数据透视表中的数据 日期与时间函数 DA TE 返回特定日期的序列号 DA TEV ALUE 将文本格式的日期转换为序列号 DAY 将序列号转换为月的日期 DAYS360 计算基于一年 360 天的两个日期间的天数 EDATE 返回用于表示开始日期之前或之后月数的日期的序列号EOMONTH 返回指定个数月之前或之后的月的末日的序列号HOUR 将序列号转换为小时 MINUTE 将序列号转换为分钟 MONTH 将序列号转换为月 NETWORKDAYS 返回两个日期之间的所有工作日个数 NOW 返回当前日期和时间的序列号 SECOND 将序列号转换为秒 TIME 返回特定时间的序列号 TIMEV ALUE 将文本格式的时间转换为序列号 TODAY 返回今天日期的序列号 WEEKDAY 将序列号转换为一星期的某天 WEEKNUM 将序列号转换为代表该星期为一年中的第几周的数字WORKDAY 返回指定个数工作日之前或之后日期的序列号
excel常用函数公式介绍 excel常用函数公式介绍1：MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数，它是众数值，可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高，这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项，之后可以直接搜索MODE函数，找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了，我们将所要求的范围输入进Number1选项里面，或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2：IF函数应用 1IF函数常用于对一些数据的进行划分比较，例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170，对于170以及170之上的在备注标明为合格，其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数，在里面直接搜索IF函数后打开 4打开IF函数后，我们先将条件填写在第一个填写栏中， D3>=170，之后在下面的当条件满足时为合格，不满足是则为不合格 5接着点击确定就可以得到备注了，这里因为身高不到170，所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了
excel常用函数公式介绍3：RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中，之后我们打开函数参数 4找到RANK函数后，我们因为选项的数字在D3单元格所以我们就填写D3就可了，之后在范围栏中选定好，这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了，这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。
QBE (Query By Example) Criteria cri = session.createCriteria(Student.class); cri.add(Example.create(s)); //s是一个Student对象 list cri.list(); 实质：创建一个模版，比如我有一个表serial有一个giftortoy字段，我设置serial.setgifttoy("2"), 则这个表中的所有的giftortoy为2的数据都会出来 2: QBC (Query By Criteria) 主要有Criteria,Criterion,Oder,Restrictions类组成 session = this.getSession(); Criteria cri = session.createCriteria(JdItemSerialnumber.class); Criterion cron = Restrictions.like("customer",name); cri.add(cron); list = cri.list(); ============================== 比较运算符 HQL运算符QBC运算符含义 = Restrictions.eq() 等于 <> Restrictions.not(Exprission.eq()) 不等于 > Restrictions.gt() 大于 >= Restrictions.ge() 大于等于 < Restrictions.lt() 小于 <= Restrictions.le() 小于等于 is null Restrictions.isnull() 等于空值 is not null Restrictions.isNotNull() 非空值 like Restrictions.like() 字符串模式匹配 and Restrictions.and() 逻辑与 and Restrictions.conjunction() 逻辑与 or Restrictions.or() 逻辑或 or Restrictions.disjunction() 逻辑或 not Restrictions.not() 逻辑非 in(列表) Restrictions.in() 等于列表中的某一个值 ont in(列表) Restrictions.not(Restrictions.in())不等于列表中任意一个值 between x and y Restrictions.between() 闭区间xy中的任意值 not between x and y Restrictions.not(Restrictions..between()) 小于值X或者大于值y 3: HQL String hql = "select https://www.doczj.com/doc/ad691721.html, ,avg(s.age) from Student s group by https://www.doczj.com/doc/ad691721.html,"; Query query = session.createQuery(hql); list = query.list(); .... 4: 本地SQL查询 session = sessionFactory.openSession(); tran = session.beginTransaction();
Excel常用函数介绍及常用功能 Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。 工程 工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。 财务 财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。财务函数中常见的参数： 未来值 (fv)--在所有付款发生后的投资或贷款的价值。 期间数 (nper)--投资的总支付期间数。 付款 (pmt)--对于一项投资或贷款的定期支付数额。 现值 (pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率 (rate)--投资或贷款的利率或贴现率。 类型 (type)--付款期间内进行支付的间隔，如在月初或月末。 信息 可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为 IS 的工作表函数，在单元格满足条件时返回 TRUE。例如，如果单元格包含一个偶数值，ISEVEN 工作表函数返回 TRUE。如果需要确定某个单元格区域中是否存在空白单元格，可以使用 COUNTBLANK 工作表函数对单元格区域中的空白单元格进行计数，或者使用 ISBLANK 工作表函数确定区域中的某个单元格是否为空。 数据库
当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000 且小于 2,500 的行或记录的总数。Microsoft Excel 共有 12 个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为 D 函数，每个函数均有三个相同的参数：database、field 和 criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database 为工作表上包含数据清单的区域，参数 field 为需要汇总的列的标志，参数 criteria 为工作表上包含指定条件的区域。 逻辑函数 使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用 IF 函数确定条件为真还是假，并由此返回不同的数值。 统计函数 统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和 y 轴截距，或构成直线的实际点数值。 文本函数 通过文本函数，可以在公式中处理文字串。例如，可以改变大小写或确定文字串的长度。可以将日期插入文字串或连接在文字串上。下面的公式为一个示例，借以说明如何使用函数 TODAY 和函数 TEXT 来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yy"的格式表示。 =TEXT(TODAY(),"dd-mm-yy") 查询和引用 当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用 VLOOKUP 工作表函数。如果需要确定数据清单中数值的位置，可以使用 MATCH 工作表函数。 数学和三角 通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。 日期与时间
Professional Studies Presentation Evaluation Criteria The numbers in the top of the boxes are points in a continuum. For example, you can assign 20 points for Organization. As long as you do not give more points than suggested in the leftmost box, the score will range between 0 and 100 when you add up the numbers. Organization (20%) 20 Consistently clear, concise, well organized. Points were easy to follow because of the organization. Transitions between sections smooth and coordinated. 15Usually clear, concise, well organized. Most of the presentation was easy to follow. Transitions between sections usually coordinated. 10Not always clear or concise. Organization was adequate, but weak. Occasionally wandered and was sometimes difficult to follow. Transitions between sections weak. 5Often unclear and disorganized, rambled too much. The presentation was confusing and difficult to follow. Transitions between sections awkward. Topic Knowledge (20%) 20Displayed an excellent grasp of the material. Demonstrated excellent mastery of content, application and implications. Excellent research depth. 15Displayed a general grasp of the material. Demonstrated good mastery of content, application and implications. Good research depth. 10Displayed some grasp of the material. Demonstrated adequate mastery of content, application and implications. Research not very deep. 5Displayed a poor grasp of the material. Demonstrated a superficial handling of content, application and implications. Little depth of research. Creativity (10%) 10Very creative and original. Imaginative design and use of materials. Novel handouts, visual aids, or methods. 8 Exhibited some originality and creativity. 5 Routine treatment, minimal thought given to originality or creativity. 3Lacked creativity. Very ordinary and mundane. Visual Aids (15%) 15Simple, clear, easy to interpret, easy to read. Well coordinated with content, well designed, used very effectively. Excellent example of how to prepare and use good visual aids 11Usually clear, easy to interpret, easy to read. Generally well coordinated with content, design was okay, generally used effectively. Demonstrated some understanding of how to use visual aids. 8 Marginally acceptable, too complex, crowded, difficult to read or interpret. Adequate coordination with content. Used only adequately. Showed little understanding of how to prepare and use visual aids. 4Poor quality visual aids (or none), hard to read, technically inaccurate, poorly constructed. Poor coordination with content. Used poorly. The presenter did not seem to know how to prepare or use visual aids effectively. Summary (15%) 15Clear, concise, major points emphasized, clear recommendations, strong conclusion or call for action. 11Referred to main points, recommendations weak or missing, weak conclusion or call for action. 8Vague mention of major points, no recommendations, weak conclusion, weak or no call for action. 4No summary, no recommendations, no conclusions, no call for action. Stage Presence (20%) 20 Excellent stage presence. Confident, used notes well, at ease, excellent gestures, good audience attention, good eye contact. 15Good stage presence. Fairly confident, used notes fairly well, good gestures, acceptable audience attention and eye contact. 10 Adequate stage presence. Read parts, fumbled with notes, several distracting mannerisms, minimal gestures, minimal eye contact, too many um=s. 5Poor stage presence. Unprepared, awkward, shuffled papers, poor eye contact, lots of um=s, turned from audience to read overheads, shuffled feet, fidgeted. Poor gestures. TOTAL POINTS COMMENTS:
EXCEL常用函数介绍 公式是单个或多个函数的结合运用。 AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期 DATEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。条件计算INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。截取数据 LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置 MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算 MOD 求出两数相除的余数。数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。日期计算 NOW 给出当前系统日期和时间。显示日期时间 OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。数据排序 RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符。字符截取SUBTOTAL 返回列表或数据库中的分类汇总。分类汇总 SUM 求出一组数值的和。数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式数值文本转换 TODAY 给出系统日期显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。文本数值转换 VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值条件定位 WEEKDAY 给出指定日期的对应的星期数。星期计算
C语言常用函数 2009-11-07 22:53 1、字符处理函数 本类别函数用于对单个字符进行处理，包括字符的类别测试和字符的大小写转换头文件 ctype.h int isalpha(int ch) 若ch是字母('A'-'Z','a'-'z')返回非0值,否则返回0 int isalnum(int ch) 若ch是字母('A'-'Z','a'-'z')或数字('0'-'9'),返回非0值,否则返回0 int isascii(int ch) 若ch是字符(ASCII码中的0-127)返回非0值,否则返回0 int iscntrl(int ch) 若ch是作废字符(0x7F)或普通控制字符(0x00-0x1F),返回非0值,否则返回0 int isdigit(int ch) 若ch是数字('0'-'9')返回非0值,否则返回0 int isgraph(int ch) 若ch是可打印字符(不含空格)(0x21-0x7E)返回非0值,否则返回0 int islower(int ch) 若ch是小写字母('a'-'z')返回非0值,否则返回0 int isprint(int ch) 若ch是可打印字符(含空格)(0x20-0x7E)返回非0值,否则返回0 int ispunct(int ch) 若ch是标点字符(0x00-0x1F)返回非0值,否则返回0 int isspace(int ch) 若ch是空格(' '),水平制表符('\t'),回车符('\r'), 走纸换行('\f'),垂直制表符('\v'),换行符('\n'), 返回非0值,否则返回0 int isupper(int ch) 若ch是大写字母('A'-'Z')返回非0值,否则返回0 int isxdigit(int ch) 若ch是16进制数('0'-'9','A'-'F','a'-'f')返回非0值, 否则返回0 int tolower(int ch) 若ch是大写字母('A'-'Z')返回相应的小写字母('a'-'z') int toupper(int ch) 若ch是小写字母('a'-'z')返回相应的大写字母('A'-'Z') 2、数学函数 本分类给出了各种数学计算函数
Design Criteria Building optimization must be followed by design criteria; these design criteria are given by the corresponding code. With the aim to determine the optimization criteria I personally have chosen the IBC 2013 Code with the corresponding related codes, such as ACI 318 and ASCE-7, concrete and load codes. Firstly we should determine the basic requirements for a high-rise building with shear wall structure and deep foundation as a foundation system, being this kind of building the objective of the study in situ. ?Building use and occupancy: (CHAPTER 3)Residential group R-2, residential occupancies containing sleeping units or more than two dwelling units where the occupants are primarily permanent in nature, including: Apartment houses, Boarding houses (non-transient), Convents, Dormitories, Fraternities and sororities, Hotels (non-transient), I Live/work units, Monasteries, Motels (non-transient), Vacation timeshare properties. ?Requirements based on occupancy:(CHAPTER 4) 1. For buildings not greater than 420 feet (128 m) in building height, the fire-resistance rating of the building elements in Type IA construction shall be permitted to be reduced to the minimum fire-resistance ratings for the building elements in Type IB. 2. In other than Group F-1, M and S-1 occupancies, the fire-resistance rating of the building elements in Type IB construction shall be permitted to be reduced to the fire-resistance ratings in Type IIA. 3. Bond strength:
数据库常用函数
一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份和还原 备份：exp dsscount/sa@dsscount owner=dsscount file=C:\dsscount_data_backup\dsscount.dmp log=C:\dsscount_data_backup\outputa.log 还原：imp dsscount/sa@dsscount file=C:\dsscount_data_backup\dsscount.dmp full=y ignore=y log=C:\dsscount_data_backup\dsscount.log statistics=none 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) CREATE TABLE ceshi(id INT not null identity(1,1) PRIMARY KEY,NAME VARCHAR(50),age INT) id为主键，不为空，自增长 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围
Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
criteria范文 我抄袭作业的原因是因为我想早点写完作业早点玩，谁都想玩，但是假如为了玩就抄 作业的话太不值了，现在玩只能玩一会，假如学习不好，以后找不着工作，没钱吃饭，还 谈的上玩吗我抄袭作业抱有一种作业是做给老师看的这种想法，其实根本不是，我们不 做作业，老师就不用批改作业，反而更轻松。所以，作业是为自己做的，我这么想辜负了 老师的一片好心，所以，我向老师表现出深深地歉意! One point is clear that different issues have different objective criteria. For example, criteria of price talking will include factors of cost, market situation, depreciation, price competition and other necessary factors. In other negotiations, exper ts’ opinions, international conventions and norms and legal documents will all serve as objective criteria. 为解决本人衣服无人洗，饭菜无人做，花钱无节制，生活无压力的现状，本人新引进 一招商项目，将于2020年02月20(26)日17时30分举行盛大的庆典活动，请亲朋好友准时参加! In the Sino—US negotiation on China’s accession into WTO, the two parties disputed over China’s developing country status. US took the position that China should be treated as a developed country. To back US stance, American negotiators cited China’s growing exports and large foreign reserve holdings. They a rgued that in developing countries China’s sophisticated technology in launching and retrieving satellites had no parallel. One American negotiator even compared the situation in China with that in India and some African countries. He said when he opened the door of a family in a poorest area randomly chosen by the Chinese government and asked the people if they had their breakfast, he was told they did, and he went on asking if lunch and supper were guaranteed, the answer was yes. However he had a very different story in some African countries and even in some areas in India. People there had little food for breakfast, not mention lunch and supper. The two countries insisted on their own standards and it was hard to bridge the discrepancy. Here the focus is which criterion to apply to for resolving the dispute. In fact there is a ready criterion provided by the World Bank, which is measured by per capita GNP. According to the World Bank’s standard, countries whose per capita GNP below $785 (1996) are the poorest countries. China’s per capita GNP in 1997 was $750, which is among the poorest countries. 甲方聘用乙方的月薪为_____元(含养老、医疗、住房公积金)。试用期满后，并经考 核合格，可根据平等协商的原则，签订正式劳动合同。