excel中如何通过函数调取符合一个数据库里两个条件_百度知道
提问者采纳
g2公式=INDEX(Sheet2!$B$2:$F$10,MATCH(B2,Sheet2!$A$2:$A$10,0),MATCH(D2,Sheet2!$B$1:$F$1,0))下拉数据范围自己按照实际范围选定
提问者评价
其他类似问题
按默认排序
其他1条回答
=INDEX(Sheet2!$B$2:$F$10,MATCH(B2,Sheet2!$B$2:$B$10,0),MATCH(D2,Sheet2!$B$1:$F$1,0))
excel的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁随笔 - 94&
评论 - 14&
&&&&&&&&&&&
DAVERAGE(databasefieldcriteria)
DatabaseFieldCriteria
DCOUNT(databasefieldcriteria)
DatabaseFieldCriteria
DCOUNTA(databasefieldcriteria)
DatabaseFieldCriteria
DGET(databasefieldcriteria)
DatabaseFieldCriteria
DMAX(databasefieldcriteria)
DatabaseFieldCriteria
DMIN(databasefieldcriteria)
DatabaseFieldCriteria
DPRODUCT(databasefieldcriteria)
DSTDEV(databasefieldcriteria)
DSTDEVP(databasefieldcriteria)
DSUM(databasefieldcriteria)
DVAR(databasefieldcriteria)
DVARP(databasefieldcriteria)
13GETPIVOTDATA
GETPIVOTDATA
GETPIVOTDATA(pivot_tablename)
Data_field()Pivot_tableField1Item1Field2Item2114
DATE(yearmonthday)
yearExcel for Windows1900Excel for Macintosh1904Month12Day day
Excel1900Excel19001111998113579619001135795
1900Excel=DATE(200111)36892
2DATEVaLUE
DATEVaLUE(date_text)
Date_textExcel1900date_text190011999912311904date_text19041199991231date_textDATEVaLUE#value!
date_textDATEVaLUEdate_text
=DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="5" month="3" year="/3/5")36955DATEVaLUE("2-26")36948
DAY(serial_number)
Serial_number"<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/01/30"190035825 1998130DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/1/30")
=DAY("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="27" month="1" year="/1/27")27=DAY(35825)30=DAY(DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="25" month="1" year="/1/25"))25
, 360(3012 )
DAYS360(start_dateend_datemethod)
Start_dateend_datestart_dateend_dateDAYS360::"<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/01/30":1900358251998130(DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/1/30"))
MethodFALSE31303130130 TRUE 31
=DAYS360("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="2" year="/2/1""")1080
(start_date)
EDATE(start_datemonths)
Start_date:"<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/01/30"358251998130DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/1/30")Months start_date
=EDATE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="5" month="3" year="/3/5"2)37016200155=EDATE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="5" month="3" year="/3/5"-6)36774200095
start-date
EOMONTH(start_datemonths)
Start_date:("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/01/30")(190035825)(DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/1/30"))Monthstart_date
=EOMONTH("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="1" year="/01/01"2)369812001331=EOMONTH("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="1" year="/01/01"-6)367382000731
012:00 A.M.2311:00 P.M.
HOUR(serial_number)
Serial_number"6:45 PM"078125 6:45PMTIMEVaLUE("6:45 PM")
=HOUR("3:30:30 PM")15=HOUR(0.5)1212:00:00 AM=HOUR(29747.7)16
MINUTE(serial_number)
Serial_number"6:45 PM" 0.781256:45 PMTIMEVaLUE("6:45 PM")
=MINUTE("15:30:00")30=MINUTE(006)26=MINUTE(TIMEVaLUE("9:45 PM"))45
MONTH(serial_number)
Serial_number:("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/01/30")(199813035825)(DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="1" year="/1/30"))
=MONTH("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="24" month="2" year="/02/24")2=MONTH(35825)1=MONTH(DATEVaLUE("<st1:chsdate w:st="on" isrocdate="False" islunardate="False" day="30" month="6" year="/6/30"))6
DB(costsalvagelifeperiodmonth)
CostSalvageLifePeriodPeriodlifeMonth12
DDB(costsalvagelifeperiodfactor)
CostSalvageLifePeriodPeriodlifeFactorfactor2
FV(ratenperpmtpvtype)
RateNperPmtPvType0101
IPMT(ratepernperpvfvtype)
RatePer1nperNperPvFvfvType01
IRR(valuesguess)
valuesGuess IRR
ISPMT(ratepernperpv)
RatePer1nperNperPvpv
MIRR(valuesfinance_ratereinvest_rate)
valuesFinance_rateReinvest_rate
NPER(ratepmtpvfvtype)
RatePmtPv()FvType01
NPV(ratevalue1value2...)
Ratevalue1value2...129
PMT(ratenperpvfvtype)
RateNperPvFvType10
PPMT(ratepernperpvfvtype)
RatePer1nperNperPvFvType10
PV(ratenperpmtfvtype)
RateNperPmtFvType10
RATE(nperpmtpvfvtypeguess)
NperPmtPvFvType10
SLN(costsalvagelife)
CostSalvageLife
SYD(costsalvagelifeper)
CostSalvageLifePerlife
VDB(costsalvagelifestart_periodend_periodfactorno_switch)
CostSalvageLifeStart_periodEnd_period
CELL(info_typereference)
Info_typeReferenceinfo_type
=CELL("row"A20)20A3TOTALCELL("contents"A3)"TOTAL"
2.ERROR.TYPE
#N/AIFERROR.TYPE()
ERROR.TYPE(error_val)
Error_valerror_val
:ERROR.TYPE:#NULL!1#DIV/0!2#value!3#REF!4#NAME?5#NUM!6#N/A7#N/A
A5=36/0=ERROR.TYPE(A5)2
INFO(type_text)
Type_text"directory""memavail"()"memused""numfile""osversion"()"recalc"()"release"Excel()"system"(Macintosh="mac"Windows="pcdos")"totmem"()
=INFO("osversion")Windows(32-bit)4.90=INFO("memavail")1048576
TRUEFALSEISBLANKTRUEFALSE
ISBLANK(value)ISERR(value)ISERROR(value)ISLOGICAL(value)ISNA(value)ISNONTEXT(value)ISNUMBER(value)ISREF(value)ISTEXT(value)
TRUE:ISBLANKISERR(#N/A)ISERROR(#N/A#value!#REF!#DIV/0!#NUM!#NAME?#NULL!)ISLOGICALISNA#N/AISNONTEXT(TRUE)ISNUMBERISREFISTEXT
:ISvalue"19"19ISNUMBER("19")"19"FALSE
=ISBLANK("")FALSE=ISREF(A5)TRUE(a5)A1:A4AVERAGE(A1:A4)#DIV/0!=IF(ISERROR(AVERAGE(A1:A4))""AVERAGE(A1:A4))
ISEVEN(number)
:numberISEVEN#value!
=ISEVEN(11)FALSE=ISEVEN(6)TRUE
ISODD(number)
=ISODD(19)TRUE=ISODD(14.5)FALSE
valueN:TRUE1FALSE0(#DIV/0!)0
A1"7"A2""A3"TRUE"=N(A1)7=N(A2)0(A2)=N(A3)1(A3TRUE)
#N/A#N/A#N/A#N/A#N/A
=NA("")#N/A
TYPE(value)
valueExcel
value1241664
A1""=TYPE(A1)2=TYPE(2+A1)16
TRUE();FALSE()
AND(logical1logical2)
Logical1logical2130TRUE()FALSE()AND#value!
A1=2A=6=AND(A1A2)FALSEB4=104=IF(AND(1
A1=FALSE()FALSEFALSEExcelFALSE
IF(logical_testvalue_if_truevalue_if_false)
Logical_testTRUEFALSE;value_if_trueLogical_testTRUElogical_testTRUEvalue_if_trueTRUEvalue_if_true;value_if_falseLogical_testFALSElogical_testFALSEvalue_if_falseFALSEvalue_if_false
=IF(C2&=85"A"IF(C2&=70"B"IF(C2&=60"C"IF(C2&60"D"))))IFIFIFIFC2&=85D2A;C2&=85IFIF(C2&=70;
NOT(logical)
LogicalTRUEFALSEFALSENOTTRUE;TRUENOTFALSE
A1=6A2=8=NOT(A1
OR(logical1logical2...)
Logical1logical2...130TRUEFALSEOR#value!
A1=6A2=8=OR(A1+A2&A2A1=A2)TRUE;=OR(A1&A2A1=A2)FALSE
A1=TRUE()TRUETRUEExcelTRUETRUE
ADDRESS(row_numcolumn_numabs_numa1sheet_text)
Row_num;Column_num;Abs_num(1234);A1A1R1C1A1TRUEADDRESSA1;A1FALSEADDRESSR1C1Sheet_textsheet_text
=ADDRESS(1441)D1
AREAS(reference)
=AREAS(a2:b4)1=AREAS((A1:A3A4:A6B4:B7A16:A18))4
CHOOSE(index_numvalue1value2...)
Index_num129129;value1value2...129
=CHOOSE(2"""")=SUM(A1:CHOOSE(3A10A20A30))=SUM(A1:A30)(CHOOSE(3A10A20A30)A30)
COLUMN(reference)
ReferencereferenceCOLUMNreferenceCOLUMNCOLUMNreference
=COLUMN(A3)1=COLUMN(B3:C5)2
COLUMNS(array)
=COLUMNS(B1:C4)2=COLUMNS({54;45})2
HLOOKUP(lookup_valuetable_arrayrow_index_numrange_lookup)
Lookup_value;Table_arrayTable_arrayRow_index_numtable_arrayRange_lookupHLOOKUP
A1:B3342368699236=HLOOKUP(34A1:B31FALSE)34;=HLOOKUP(3{123;"a""b""c";"d""e""f"}2TRUE)c
7.HYPERLINK
Intranet(Internet)
HYPERLINK(link_locationfriendly_name)
Link_locationExeclWordInternetIntranetURL;Friendly_nameFriendly_namelink_location
HYPERLINK("/""")/=HYPERLINK("D:\README.TXT""")DREADME.TXT
INDEX(arrayrow_numcolumn_num)INDEX(referencerow_numcolumn_numarea_num)
Array;Row_numrow_numcolumn_Column_numcolumn_numrow_numReferenceArea_numrow_numcolumn_num12area_numINDEX1
A1=68A2=96A3=90=INDEX(A1:A311)68=INDEX(A1:A3111)68
9.INDIRECT
INDIRECT(ref_texta1)
Ref_textA1R1C1;A1ref_texta1TRUEref_textA1-a1FALSEref_textR1C1-
A1B1B168.75=INDIRECT($A$1)68.75
1():LOOKUP(lookup_valuelookup_vectorresult_vector)
2():LOOKUP(lookup_valuearray)
1():Lookup_valueLOOKUPLookup_valueLookup_vectorLookup_vector
2():Lookup_valueLOOKUPLookup_valueLOOKUPlookup_valuelookup_valueArraylookup_value
:Lookup_vectorLOOKUP
A1=68A2=76A3=85A4=90=LOOKUP(76A1:A4)2=LOOKUP("bump"{"a"1;"b"2;"c"3})2
MATCH(lookup_valuelookup_arraymatch_type)
Lookup_value()Lookup_arrayLookup_array;Match_type-101Excellookup_arraylookup_valuematch_type1MATCHlookup_valuematch_type0MATCHlookup_valuematch_type-1MATCHlookup_value
:MATCHlookup_arraymatch_type0lookup_valuelookup_value(*?)
A1=68A2=76A3=85A4=90=MATCH(90A1:A50)3
OFFSET(referencerowscolsheightwidth)
Reference;Rows()5Rowsreference5()();Cols()5Colsreference5()();HeightHeight;WidthWidth
A1=68A2=76A3=85A4=90=SUM(OFFSET(A1:A22021))177
ROW(reference)
=ROW(A6)6C5=ROW()5
ROWS(array)
=ROWS(A1:A9)9=ROWS({123;456;123})3
RTD(ProgIDservertopic1[topic2]...)
ProgIDCOMProgIDServertopic1topic2...128
16.TRANSPOSE
TRANSPOSE(array)
A1=68A2=76B1=85B2=90{=TRANSPOSE(A1:B1)}C1=56D1=98C2=90D2=87
17.VLOOKUP
VLOOKUPHLOOKUP
VLOOKUP(lookup_valuetable_arraycol_index_numrange_lookup)
Lookup_valueTable_arrayCol_index_numtable_arrayCol_index_num1table_array;col_index_num2table_arrayRange_lookupVLOOKUPTRUElookup_value;range_valueFALSEVLOOKUP#N/A
A1=23A2=45A3=50A4=65=VLOOKUP(50A1:A41TRUE)50
ABS(number)
A1=-16=ABS(A1)16
ACOS(number)
A1=0.5=ACOS(A1)1.(/3600);=ACOS(-0.5)*180/PI()120
ACOSH(number)
=ACOSH(1)0;=ACOSH(10)2.993223
ASIN(number)
A1=-0.5=ASIN(A1)-0.5236(-/6);=ASIN(A1)*180/PI()-300
ASINH(number)
=ASINH(-2.5)-1.64723;=ASINH(10)2.998223
ATAN(number)
number180/PI()
=ATAN(1)0.785398(/4);=ATAN(1)*180/PI()450
XYX(x_numy_num)-(-)
ATAN2(x_numy_num)
X_numXY_numY
=ATAN2(11)0.785398(/4);=ATAN2(-1-1)-2.35619(-3/4);=ATAN2(-1-1)*180/PI()-1350
ATANH(number)
=ATANH(0.5)0.;=ATANH(-0.1)-0.10034
Numbersignificance
CEILING(numbersignificance)
numberSignificance
:number0numberSignificance
A1=3.1416=CEILING(A11)4;=CEILING(-2.5-2)4
COMBIN(numbernumber_chosen)
numbernumber_chosen
:number&0number_chosen&0number&number_chosen
10=COMBIN(102)45
COS(number)
numbernumberPI()/180
A1=1=COS(A1)0.540302;A2=60=COS(A2*PI()/180)0.5
COSH(number)
A1=5A3=6=COSH(A1+A3);C1=60=COSH(COS(C1*PI()/180))1.
13.COUNTIF
COUNTIF(rangecriteria)
range;Criteria(36"&160""")
A1:A5=COUNTIF(A1:A5"")3
14.DEGREES
DEGREES(angle)
=DEGREES(1)57.=DEGREES(PI()/3)60
EVEN(number)
A1=-2.6=EVEN(A1)-4;=EVEN(-4.56+6.87)4
EXP(number)
A1=3=EXP(A1)20.085537e3
1*2*3*...*
FACT(number)
:NumberNumber
A1=3=FACT(A1)6;=FACT(5.5)1*2*3*4*5.5120
18.FACTDOUBLE
FACTDOUBLE(number)
NumberNumber
=FACTDOUBLE(4)8
Numbersignificance
FLOOR(numbersignificance)
NumberSignificance
A1=22.5=FLOOR(A11)22;=FLOOR(-2.5-2)-2
GCD(number1number2...)
Number1number2...129
A1=16A2=28A3=46=GCD(A1:A3)2
INT(number)
A1=16.24A2=-28.389=INT(A1)16=INT(A2)-29
number1number2LCM
LCM(number1number2...)
Number1number2...129
A1=4A2=16A3=8=LCM(A1:A3)16
e(2.04)(LNEXP)
LN(number)
A1=100A2=67=LN(A1+A2)5.;=LN(EXP(3))3;=EXP(LN(4))4
LOG(numberbase)
NumberBase10
A1=8=LOG(A12)3;=LOG(10010)2
LOG10(number)
A1=1000=LOG10(A1)3;=LOG10(10^5)5
26.MDETERM
MDETERM(array)
ArrayArrayA1:C3;{123;456;789};
A1=1A2=2B1=3B2=4=MDETERM(A1:B2)-2
27.MINVERSE
MINVERSE(array)
ArrayA1:C3;{123;456;789};
=MINVERSE({4-1;20}){00.5;-12};=MINVERSE({121;34-1;020}){0.250.25-0.75;000.5;0.75-0.25-0.25}
array1array2
MMULT(array1array2)
Array1array2Array1array2Array1array2
=MMULT({12;23}{34;45})11
MOD(numberdivisor)
NumberDivisor(divisor)
A1=51=MOD(A14)3;=MOD(-101-2)1
MROUND(numbersignificance)
NumberSignificanceNumber
:numberSignificanceSignificanceMROUND
A1=6.6876=MROUND(A14)8
31.MULTINOMIAL
MULTINOMIAL(234)9!/2!*3!*4!
MULTINOMIAL(number1number2...)
Number1number2...Multinomial129
MULTINOMIAL(234)1260
ODD(number)
:number0number
A1=31.5=ODD(A1)33;=ODD(3)3;=ODD(-26.38)27
POWER(numberpower)
NumberPower
:^POWER=5^2=POWER(52)
A1=25.37=POWER(A17);=POWER(45/4)5.656854
35.PRODUCT
PRODUCT(number1number2...)
Number1number2...130
A1=24A2=36A3=80=PRODUCT(A1:A3)69120;=PRODUCT(122639)12168
36.QUOTIENT
QUOTIENT(numeratordenominator)
NumeratorDenominator
A1=86A2=9=QUOTIENT(A1A2)9;=QUOTIENT(-103)3
37.RADIANS
RADIANS(angle)
A1=90=RADIANS(A1)1.57=RADIANS(360)6.28()
:ab=RAND()*(b-a)+a=RAND()F9
=RAND()*100001000
39.RANDBETWEEN
RANDBETWEEN(bottomtop)
BottomRANDBETWEENTopRANDBETWEEN
=RANDBETWEEN(10009999)10009999
ROMAN(numberform)
Numberformform
=ROMAN(4990)CDXCIX;=ROMAN(4991)LDVLIV
ROUND(numbernum_digits)
Number;Num_digitsNumber
:num_digits0;num_digits0;num_digits0
A1=65.25=ROUND(A11)65.3;=ROUND(82.1492)82.15;=ROUND(21.5-1)20
42.ROUNDDOWN
ROUNDDOWN(numbernum_digits)
NumberNum_digits
:ROUNDDOWNROUNDROUNDDOWN
A1=65.251=ROUNDDOWN(A10)65;=ROUNDDOWN(A12)65.25;=ROUNDDOWN(3.141593)3.141;=ROUNDDOWN(-3.141591)-3.1;=ROUNDDOWN(-2)31400
43.ROUNDUP
ROUNDUP(numbernum_digits)
NumberNum_digits
:num_digits0num_digits0
A1=65.251=ROUNDUP(A10)66;=ROUNDUP(A11)66;=ROUNDUP(A12)65.26;=ROUNDUP(-3.141591)-3.2;=ROUNDUP(-2)31500
44.SERIESSUM
SERIESSUM(xnmcoefficients)
XNxMnCoefficientsxCoefficients
:SERIESSUM
A1=65.25=SERIESSUM(A1326)
SIGN(number)
A1=65.25=SIGN(A1)1;=SIGN(6-12)-1;=SIGN(9-9)0
SIN(number)
Number()PI()/180
A1=60=SIN(A1*PI()/180)0.86660
SINH(number)
=SINH(10)=SINH(-6)-201.7131574
SQRT(number)
A1=81=SQRT(A1)9;=SQRT(4+12)6
SQRTPI(number)
:SQRTPInumber&0SQRTPI#NUM!
=SQRTPI(1)1.772454=SQRTPI(2)2.506628
50.SUBTOTAL
SUBTOTAL(function_numref1ref2)
Function_num111(1AVERAGE;2COUNT;3COUNTA;4MAX;5MIN;6PRODUCT;7STDEV;8STDEVP;9SUM;10VAR;11VARP)Ref1ref2129
A1=1A2=2A3=3=SUBTOTAL(9A1:A3)SUMA1:A36
SUM(number1number2...)
Number1number2...130()
A1=1A2=2A3=3=SUM(A1:A3)6;=SUM("3"2TRUE)6"3"3TRUE1
SUMIF(rangecriteriasum_range)
RangeCriteriaSum_range
FB=SUMIF(B1:B1000""F1:F1000)B1:B1000""B1:B1000F1:F1000
53.SUMPRODUCT
SUMPRODUCT(array1array2array3...)
Array1array2array3...230
=SUMPRODUCT({34;86;19}{27;67;53})156
SUMSQ(number1number2...)
Number1number2...130
A1=1A2=2A3=3=SUMSQ(A1:A3)14(12+22+32=14)
55.SUMX2MY2
SUMX2MY2(array_xarray_y)
Array_xArray_y
A1=1A2=2A3=3B1=4B2=5B3=6=SUMX2MY2(A1:A3B1:B3)-63
56.SUMX2PY2
SUMX2PY2(array_xarray_y)
Array_xArray_y
A1=1A2=2A3=3B1=4B2=5B3=6=SUMX2PY2(A1:A3B1:B3)91
57.SUMXMY2
SUMXMY2(array_xarray_y)
Array_xArray_y
A1=1A2=2A3=3B1=4B2=5B3=6=SUMXMY2(A1:A3B1:B3)27
TAN(number)
NumberP1()/180
A1=60=TAN(A1*PI()/180)1.;TAN(1)1.
TANH(number)
A1=60=TANH(A1)1=TANH(0.5)0.462117
TRUNC(numbernum_digits)
NumberNum_digits()
:TRUNCINTINTTRUNC:TRUNC(-4.3)-4INT(-4.3)-5
A1=78.652=TRUNC(A11)78.6=TRUNC(A12)78.65=TRUNC(-8.9632)8.96
AVEDEV(number1number2...)
Number1number2...130
A1=79A2=62A3=45A4=90A5=25=AVEDEV(A1:A5)20.16
AVERAGE(number1number2
Number1number2...130
A1:A510070924782=AVERAGE()78.2
3.AVERAGEA
AVERAGE(TRUEFALSE)
AVERAGEA(value1value2...)
value1value2...130
A1=76A2=85A3=TRUE=AVERAGEA(A1:A3)54(76+85+1/3=54)
4.BETADIST
BETADIST(xalphabetaAB)
X(AB)AlphaBetaAxBx
=BETADIST(281013)0.
betaprobability=BETADIST(x...)BETAINV(probability...)=xbeta
BETAINV(probabilityalphabetaAB)
ProbabilityBetaAlphaBetaAxBx
=BETAINV(0.81013)2
6.BINOMDIST
BINOMDIST106
BINOMDIST(number_strialsprobability_scumulative)
Number_sTrialsProbability_sCumulativecumulativeTRUEBINOMDISTnumber_s;FALSEnumber_s
0.5106=BINOMDIST(6100.5FALSE)0.205078
CHIDIST(xdegrees_freedom)
Xc2Degrees_freedom
=CHIDIST(12)0.
c2probability=CHIDIST(x?)CHIINV(probability?)=x
CHIINV(probabilitydegrees_freedom)
Probabilityc2Degrees_freedom
=CHIINV(0.52)1.
CHITEST(actual_rangeexpected_range)
Actual_rangeExpected_range
A1=1A2=2A3=3B1=4B2=5B3=6=CHITEST(A1:A3B1:B3)0.
10.CONFIDENCE
CONFIDENCE(alphastandard_devsize)
Alpha(100*(1-alpha)%alpha0.0595%)Standard_devSize
4660595%=CONFIDENCE(0.05546)1.44601.44
array1array2
CORREL(array1array2)
Array1Array2
A1=90A2=86A3=65A4=54A5=36B1=89B2=83B3=60B4=50B5=32=CORREL(A1:A5B1:B5)0.AB
COUNT(value1value2...)
value1value2...(130)
A1=90A2=A3=A4=54A5=36=COUNT(A1:A5)3
COUNTA(value1value2...)
:value1value2...130COUNT
A1=6.28A2=3.74=COUNTA(A1:A7)2
14.COUNTBLANK
COUNTBLANK(range)
A1=88A2=55A3=""A4=72A5=""=COUNTBLANK(A1:A5)2
15.COUNTIF
COUNTIF(rangecriteria)
RangeCriteria
COVAR(array1array2)
Array1Array2
A1=3A2=2A3=1B1=3600B2=1500B3=800=COVAR(A1:A3B1:B3)933.3333333
17.CRITBINOM
CRITBINOM(trialsprobability_salpha)
TrialsProbability_sAlpha
=CRITBINOM(100.90.75)10
DEVSQ(number1number2...)
Number1number2...130
A1=90A2=86A3=65A4=54A5=36=DEVSQ(A1:A5)2020.8
19.EXPONDIST
EXPONDIST(xlambdacumulative)
XLambdaCumulativecumulativeTRUEEXPONDIST;cumulativeFALSE
=EXPONDIST(0.210TRUE)0.864665=EXPONDIST(0.210FALSE)1.353353
FDIST(xdegrees_freedom1degrees_freedom2)
XDegrees_freedom1Degrees_freedom2
=FDIST(19089)0.
FFp=FDIST(x)FINV(p)=x
FINV(probabilitydegrees_freedom1degrees_freedom2)
ProbabilityFDegrees_freedom1Degrees_freedom2
=FINV(0.18674)1.
=FISHER(0.55)0.
23.FISHERINV
Fishery=FISHER(x)FISHERINV(y)=x
FISHERINV(y)
=FISHERINV(0.765)0.
24.FORECAST
FORECAST(xknown_ysknown_xs)
XX()Known_ysy=kx+byKnown_xsy=kx+bx
=FORECAST(16{7891115}{2126323642})4.
25.FREQUENCY
FREQUENCY(data_arraybins_array)
Data_arrayBins_arraydata_array
FTEST(array1array2)
Array1Array2
A1=71A2=83A3=76A4=49A5=92A6=88A7=96B1=59B2=70B3=80B4=90B5=89B6=84B7=92=FTEST(A1:A7B1:B7)0.
27.GAMMADIST
GAMMADIST(xalphabetacumulative)
XAlphaBetabeta=1GAMMADISTCumulativecumulativeTRUEGAMMADIST;FALSE
=GAMMADIST(1092FALSE)0.032639=GAMMADIST(1092TRUE)0.068094
28.GAMMAINV
P=GAMMADIST(x...)GAMMAINV(p...)=x
GAMMAINV(probabilityalphabeta)
ProbabilityAlphaBetabeta=1GAMMAINV
=GAMMAINV(0.0582)7.
29.GAMMALN
GAMMALN(x)
=GAMMALN(6)4.
30.GEOMEAN
GEOMEAN(number1number2...)
Number1number2...130
=GEOMEAN(1.21.51.82.32.62.83)2.
xyGROWTHxyGROWTHxy
GROWTH(known_ysknown_xsnew_xsconst)
Known_ysy=b*m^xy;Known_xsy=b*m^xx();New_xsxGROWTHy;Constb1constTRUEbconstFALSEb1my=m^x
32.HARMEAN
HARMEAN(number1number2...)
Number1number2...130
=HARMEAN(668892)80.
33.HYPGEOMDIST
HYPGEOMDIST
HYPGEOMDIST(sample_snumber_samplepopulation_snumber_population)
Sample_sNumber_samplePopulation_sNumber_population
4222206:=HYPGEOMDIST(362042)0.
34.INTERCEPT
INTERCEPT(known_ysknown_xs)
Known_ysKnown_xs
A1=71A2=83A3=76A4=49A5=92A6=88A7=96B1=59B2=70B3=80B4=90B5=89B6=84B7=92=INTERCEPT(A1:A7B1:B7)87.
KURT(number1number2...)
Number1number2...130
A1=71A2=83A3=76A4=49A5=92A6=88A7=96=KURT(A1:A7)-1.
LARGE(arrayk)
B1=59B2=70B3=80B4=90B5=89B6=84B7=92=LARGE(B1B72)90
LINEST(known_ysknown_xsconststats)
Known_ysy=mx+byKnown_xsy=mx+bxConstb0constTRUEbconstFALSEb0my=mxStatsstatsTRUELINESTstatsFALSELINESTmb
A1=71A2=83A3=76A4=49A5=92A6=88A7=96B1=59B2=70B3=80B4=90B5=89B6=84B7=92{=LINEST(A1:A7B1:B7)}-0.-0.-0.-0.-0.-0.-0.
LOGEST(known_ysknown_xsconststats)
Known_ysy=b*m^xyKnown_xsy=b*m^xxConstb1constTRUEb
A1=33100A2=47300A3=69000A4=102000A5=150000A6=220000B1=11B2=12B3=13B4=14B5=15B6=16{=LOGEST(A1:A6B1:B6TRUETRUE)}C1:D5:1.495.30477020.0.0.0.42.0.
xln(x)mean()standard-dev()p=LOGNORMDIST(x...)LOGINV(p...)=x
LOGINV(probabilitymeanstandard_dev)
ProbabilityMeanln(x)Standard_devln(x)
=LOGINV(0.0362.51.5)0.
40.LOGNORMDIST
xln(x)meanstandard_dev
LOGNORMDIST(xmeanstandard_dev)
XMeanln(x)Standard_devln(x)
=LOGNORMDIST(25.51.6)0.
MAX(number1number2...)
Number1number2...130
A1=71A2=83A3=76A4=49A5=92A6=88A7=96=MAX(A1:A7)96
MAX(TRUEFALSE)
MAXA(value1value2...)
value1value2...130
A1:A500.20.50.4TRUE:MAXA(A1:A5)1
MEDIAN(number1number2...)
Number1number2...130
MEDIAN(1112131415)13;MEDIAN(123456)3.534
MIN(number1number2...)
Number1number2...130
A1=71A2=83A3=76A4=49A5=92A6=88A7=96=MIN(A1:A7)49;=MIN(A1:A50-8)-8
MIN(TRUEFALSE)
MINA(value1value2...)
value1value2...130
A1=71A2=83A3=76A4=49A5=92A6=88A7=FALSE=MINA(A1:A7)0
MODE(number1number2...)
Number1number2...130
A1=71A2=83A3=71A4=49A5=92A6=88=MODE(A1:A6)71
47.NEGBINOMDIST
probability_sNEGBINOMDISTnumber_snumber_f
NEGBINOMDIST(number_fnumber_sprobability_s)
Number_fNumber_sProbability_s
100.310=NEGBINOMDIST(40100.3)0.
48.NORMDIST
NORMDIST(xmeanstandard_devcumulative)
XMeanStandard_dev;CumulativecumulativeTRUENORMDIST;FALSE
=NORMDIST(46352.5TRUE)0.
49.NORMSINV
NORMSINV(probability)
Probability
=NORMSINV(0.8)0.
50.NORMSDIST
NORMSDIST(z)
=NORMSDIST(1.5)0.
51.NORMSINV
NORMSINV(probability)
Probability
=NORMSINV(0.)1.(1.5)
52.PEARSON
Pearson()r-1.01.0(-1.01.0)
PEARSON(array1array2)
Array1Array2
A1=71A2=83A3=71A4=49A5=92A6=88B1=69B2=80B3=76B4=40B5=90B6=81=PEARSON(A1:A6B1:B6)0.
53.PERCENTILE
PERCENTILE(arrayk)
A1=71A2=83A3=71A4=49A5=92A6=88=PERCENTILE(A1:A60.8)888088
54.PERCENTRANK
PERCENTRANK(arrayxsignificance)
ArrayXSignificancePERCENTRANK3
A1=71A2=83A3=71A4=49A5=92A6=88=PERCENTRANK(A1:A671)0.271620%
PERMUT(numbernumber_chosen)
NumberNumber_chosen
909(09)=PERMUT(109)3628800
56.POISSON
POISSON(xmeancumulative)
XMeanCumulative
=POISSON(510TRUE)0.=POISSON(312FALSE)0.
PROB(x_rangeprob_rangelower_limitupper_limit)
X_rangexProb_rangex_rangeLower_limitUpper_limit
=PROB({0123}{0.20.30.10.4}2)0.1=PROB({0123}{0.20.30.10.4}13)0.8
58.QUARTILE
QUARTILE(arrayquart)
ArrayQuartqurart01234QUARTILE(25)(50)(75)
A1=78A2=45A3=90A4=12A5=85=QUARTILE(A1:A53)85
RANK(numberreforder)
Number;Ref();Orderorder0orderref
RANK605617(6)
A1=78A2=45A3=90A4=12A5=85=RANK(A1$A$1:$A$5)582104
RSQ(known_ysknown_xs)
Known_ysKnown_xs
=RSQ({22232919382725}{16151917151434})0.
SKEW(number1number2...)
Number1number2...130
=SKEW({22232919382725}{16151917151434})0.
SLOPE(known_ysknown_xs)
Known_ysKnown_xs
=SLOPE({22232919382725}{16151917151434})-0.
SMALL(arrayk)
A1=78A2=45A3=90A4=12A5=85=SMALL(A1:A53)78
64.STANDARDIZE
meanstandard-dev
STANDARDIZE(xmeanstandard_dev)
XMeanStandard_dev
=STANDARDIZE(626010)0.2
STDEV(number1number2...)
Number1number2...130
STDEVSTDEVP(TRUEFALSE)STDEVA
A1=78A2=45A3=90A4=12A5=85=STDEV(A1:A5)33.
STDEV(TRUEFALSE)
STDEVA(value1value2...)
value1value2...130
A1=78A2=45A3=90A4=12A5=85=STDEVA(A1:A5)33.
STDEVP(number1number2...)
Number1number2...130
STDEVP(TRUEFALSE)STDEVPA
STDEVPSTDEVSTDEVSTDEVP
5A1=78A2=45A3=90A4=12A5=85=STDEVP(A1:A5)29.
68.STDEVPA
STDEVP(TRUEFALSE)
STDEVPA(value1value2...)
value1value2...130()
STDEVPASTDEVA
5A1=78A2=45A3=90A4=12A5=85=STDEVP(A1:A5)29.
STEYX(known_ysknown_xs)
Known_ysKnown_xs
=STEYX({22132919181715}{16251117251417})4.
t-()t(x)t()tt
TDIST(xdegrees_freedomtails)
XDegrees_freedomTailstails=1TDISTtails=2TDIST
=TDIST(6021)0.
TINV(probabilitydegrees_freedom)
Probability-tDegrees_freedom
=TINV(0.560)0.
(y)known_ysknown_xs()new_xsy
TREND(known_ysknown_xsnew_xsconst)
Known_ysy=mx+byKnown_xsy=mx+bxNew_xsTRENDyxConstb0
73.TRIMMEAN
TRIMMEAN(arraypercent)
ArrayPercentpercent=0.220422percent=0.13010%3TRIMMEAN
A1=78A2=45A3=90A4=12A5=85=TRIMMEAN(A1:A50.1)62
TTEST(array1array2tailstype)
Array1Array2Tailstails=1TTESTtails=2TTESTTypettype(123)()
=TTEST({345891245}{619321445171}21)0.196016
VAR(number1number2...)
Number1number2...130
5VARA1=78A2=45A3=90A4=12A5=85=VAR(A1:A5)1089.5
VAR(TRUEFALSE)
VARA(value1value2...)
value1value2...130
5VARA1=78A2=45A3=90A4=12A5=85=VARA(A1:A5TRUE)
VARP(number1number2...)
Number1number2...130(TRUEFALSE)
5A1=88A2=55A3=90A4=72A5=85VARP=VARP(A1:A5)214.5
VARP(TRUEFALSE)
VARPA(value1value2...)
value1value2...130
5A1=88A2=55A3=90A4=72A5=85VARPA=VARPA(A1:A5)214.5
79.WEIBULL
WEIBULL(xalphabetacumulative)
XAlphaBetaCumulative
=WEIBULL(9821100TRUE)0.=WEIBULL(581167FALSE)0.
ZTEST(arrayxsigma)
ArrayxXSigma()
=ZTEST({3678654219}4)0.090574
A1=excel=ASC(A1)excel
(MacintoshMacintoshWindowsANSI)
CHAR(number)
Number1255()
=CHAR(56)8=CHAR(36)$
CLEAN(text)
CHAR(7)=CLEAN(CHAR(7)&"text"&CHAR(7))text
CODE(text)
CHAR(65)A=CODE("Alphabet")65
5.CONCATENATE
CONCATENATE(text1text2...)
Text1text2...130
A1=98A2==CONCATENATE(A1A2)98=A1&A2
6.DOLLARRMB
DOLLAR(numberdecimals)RMB(numberdecimals)
Number;DecimalsDecimalsnumberDecimals2
=RMB(2)1586.57=RMB(99.888)99.89
TRUE;FALSEEXACT
EXACT(text1text2)
Text1Text2
A1=A2=A3==EXACT(A1A2)FALSE=EXACT(A1A3)FALSE=EXACT("word""word")TRUE
FIND(within_text)(find_text)within_textfind_text
FIND(find_textwithin_textstart_num)
Find_text;Within_text;Start_numwithin_text1start_num1
A1==FIND(""A11)1
FINDB(within_text)(find_text)within_textfind_textFINDFINDB:
FINDB(find_textwithin_textstart_num)
Find_text;Within_text;Start_numwithin_text1start_num1
A1==FINDB("A11)52
FIXED(numberdecimalsno_commas)
Number;Decimals;No_commasTRUEFIXEDno_commasFALSE
A1==FIXED(A22TRUE)2001.17=FIXED(-1)6830
A1=excel=JIS(a1)EXCEL
12.LEFTLEFTB
LEFT(textnum_chars)LEFTB(textnum_bytes)
Text;Num_chars0Num_bytesLEFTB
A1=LEFT(A12)LEFTB(A12)
13.LENLENB
LEN(text)LENB(text)
A1==LEN(A1)5=LENB(A1)10
LOWER(text)
LOWERLOWERPROPERUPPER
A1=Excel=LOWER(A1)excel
15.MIDMIDB
MIDMIDBMIDB
MID(textstart_numnum_chars)MIDB(textstart_numnum_bytes)
TextStart_numstart_num1;Num_charsMID;Num_bytesMIDB
a1==MID(A132)=MIDB(A132)
16.PHONETIC
(furigana)
PHONETIC(reference)
Referencereferencefuriganareference#N/A
PROPER(text)
A1=excel=PROPER(A1)Excel
18.REPLACEREPLACEB
REPLACEREPLACEBREPLACE
REPLACE(old_textstart_numnum_charsnew_text)REPLACEB(old_textstart_numnum_bytesnew_text)
Old_text;Start_numnew_textold_text;Num_charsREPLACEnew_textold_text;Num_bytesREPLACEnew_textold_text;New_textold_text
A1=A2==REPLACE(A133A2)=REPLACEB(A123A2)
REPT(textnumber_times)
TextNumber_times()
=REPT(""2)
20.RIGHTRIGHTB
RIGHTRIGHTB
RIGHT(textnum_chars)RIGHTB(textnum_bytes)
Text;Num_charsRIGHT0num_charsRIGHTnum_chars1Num_bytes
A1==RIGHT(A12)=RIGHTB(A12)
21.SEARCHSEARCHB
start_numSEARCHSEARCHB
SEARCH(find_textwithin_textstart_num)SEARCHB(find_textwithin_textstart_num)
Find_text?*~Within_textfind_textStart_numwithin_textstart_num1
A1==SEARCH(""A1)3=SEARCHB(""A1)5
22.SUBSTITUTE
new_textold_textSUBSTITUTE;REPLACE
SUBSTITUTE(textold_textnew_textinstance_num)
Text;Old_text;New_textold_text;Instance_numnew_textold_instance_numold_text;new_textTextold_text
A1=A2==SUBSTITUTE(A1""A21)
valuevalueT""()
TEXT(valueformat_text)
value;format_text*
A1==TEXT(A5"###0.00")2986.64
TRIM(text)
A1=FirstQuarterEarnings=TRIM(A1)FirstQuarterEarnings
UPPER(text)
=UPPER("apple")APPLE
value(text)
TextExcelTextvalue#value!
valueExcelvalue
=value("1000")1000;=value("16:48:00")-value("12:00:00")0.2448
28.WIDECHAR
WIDECHAR(text)
=WIDECHAR("apple")apple=WIDECHAR("")
阅读(...) 评论()