2017/3/29
常用日期與時(shí)間運(yùn)算
1详瑞、日期與時(shí)間
計(jì)算結(jié)束時(shí)間
=D4+E4/24/60
計(jì)算持續(xù)時(shí)間
=(E9-D9)*24*60
計(jì)算結(jié)束日期
=D14+E14
計(jì)算間隔天數(shù)
方法1：=E18-D18
方法2：=DATEDIF(D18,E18,"d")
2掂林、推算日期
計(jì)算結(jié)束日期
=DATE(YEAR(B5),MONTH(B5)+4,DAY(B5))
求本月最后一天
方法1：=DATE(YEAR(B13),MONTH(B13)+1,1)-1
方法2：=DATE(YEAR(B13),MONTH(B13)+1,0)
求本月天數(shù)
方法1：=DAY(DATE(YEAR(B21),MONTH(B21)+1,1)-1)
方法2：=DAY(DATE(YEAR(B21),MONTH(B21)+1,0)
3、計(jì)算日期間隔
計(jì)算工齡
=DATEDIF(B5,C5,"y")
計(jì)算日期間隔
=DATEDIF(B13,C13,"y")&"年"&DATEDIF(B13,C13,"ym")&"月"&DATEDIF(B13,C13,"md")&"天"
4坝橡、星期計(jì)算
求第幾周
=WEEKNUM(B3,2)
求周幾
=WEEKDAY(B8,2)
="第"&WEEKNUM(B13,2)&"周第"&WEEKDAY(B13,2)&"天"
2017/3/30
條件格式與公式
1泻帮、多重條件格式設(shè)置，后設(shè)置的會(huì)覆蓋前面設(shè)置的计寇。
小于1000000的顏色標(biāo)記
后設(shè)置的小于2000000的顏色會(huì)覆蓋前面的
正確的應(yīng)該是先設(shè)置小于2000000的锣杂，再設(shè)置小于1000000。
2番宁、對(duì)于約束條件和設(shè)置結(jié)果分別位于2列的元莫，要寫(xiě)公式。
將數(shù)量大于100的日期列標(biāo)記
=D2>100
3蝶押、對(duì)于標(biāo)記多列的踱蠢，注意單元格約束
將數(shù)量大于100的行 標(biāo)記
=$D2>100
4、練習(xí)題
將周末整行標(biāo)記
=(WEEKDAY($A2,2))>5
將未來(lái)15天內(nèi)生日的人都姓名標(biāo)記
=(DATEDIF($C2,TODAY(),"md"))<=15
2017/4/13
簡(jiǎn)單文本函數(shù)
提取綽號(hào)和姓名
綽號(hào):=LEFT(A3,3)
姓名:=MID(A3,4,10)
提取后四位編碼
=RIGHT(E3,4)
提取性別位數(shù)字
=RIGHT(LEFT(B13,17),1)
提取單位
=RIGHT(A2,LENB(A2)-LEN(A2))
提取用戶(hù)名和域名
用戶(hù)名:=LEFT(E2,(FIND("@",E2)-1))
域名:=MID(E2,FIND("@",E2)+1,100)
2017/4/14
數(shù)學(xué)函數(shù)
性別位數(shù)字奇數(shù)男棋电，偶數(shù)女
=IF(MOD(RIGHT(LEFT(B2,17),1),2),"man","woman")
計(jì)算休假天數(shù)朽基，不到0.5舍去，超過(guò)0.5算半天
解法1：=IF(MOD(C2,1)<=0.5,INT(C2),INT(C2)+0.5)
解法2:=INT(C2*2)/2
轉(zhuǎn)置
=INDEX($A:$A,COLUMN()-2)
提取每周第二個(gè)數(shù)值
=INDEX(E:E,(ROW()-4)*5+3)
轉(zhuǎn)置
=INDEX($A:$A,ROW()*3+COLUMN()-10)
反轉(zhuǎn)
先找規(guī)律离陶，再引用
=INDEX($A$2:$C$35,INT((ROW()-2)/3)+1,MOD(ROW()-2,3)+1)
五人分一組
=IF((MOD(ROW()-1,6))=5,"",INDEX(A:A,INT((ROW()-1)/6)*5+MOD(ROW()-1,6)+1))
2017/4/16
LOOKUP函數(shù)與數(shù)組
求銷(xiāo)售區(qū)域的金額
方法1：=SUMIF(A:A,K8,E:E)
方法2（數(shù)組）：=SUMPRODUCT((A2:A22=K8)*(E2:E22))
多條件查找
=LOOKUP(1,0/(($A$2:$A$13=F6)*($B$2:$B$13=G6)),$D$2:$D$13)
第19講-Indirect函數(shù)
用兩種方法取值
index法：=INDEX(E:E,ROW()*5-25)
indirect法：=INDIRECT("e"&ROW()*5-25)
跨表引用
1、確定位置：=A4&"!G2"
2衅檀、引用：=INDIRECT(A4&"!G2")
跨表引用招刨，順序不同
=VLOOKUP("張三",INDIRECT(A4&"!A:H"),7,0)
問(wèn)題:如果員工姓名重復(fù)該如何處理？
=SUMIF(INDIRECT(A4&"!A:A"),"張三",INDIRECT(A4&"!G:G"))
跨表引用2
=VLOOKUP(B$2,INDIRECT($A3&"!A:H"),7,0)
數(shù)據(jù)有效性+求和
1哀军、定義單元格名稱(chēng)
2沉眶、=SUM(INDIRECT(G3))
多重?cái)?shù)據(jù)有效性
1、定義單元格名稱(chēng)
2杉适、=INDIRECT(E2)
3谎倔、去除首行數(shù)據(jù)有效性
2017/4/20
旋風(fēng)圖
要點(diǎn)：主次坐標(biāo)軸、逆序刻度猿推、坐標(biāo)軸自定義格式片习、模糊背景插入剪貼畫(huà)
2017/4/22
動(dòng)態(tài)圖表1
注意if函數(shù)的單元格必須絕對(duì)引用
利用offset函數(shù)自動(dòng)更新數(shù)據(jù)透視表取值范圍
注意必須絕對(duì)引用單元格
步驟1
步驟2
2017/5/6
2017/5/8
甘特圖
要點(diǎn)：條形堆積圖、固定坐標(biāo)軸
動(dòng)態(tài)甘特圖
要點(diǎn)：IF公式區(qū)分完成時(shí)間和未完成時(shí)間蹬叭、滾動(dòng)條日期設(shè)置
=IF($B$12<B2,0,IF($B$12>B2+C2,C2,$B$12-B2))
2017/5/9
注意藕咏！二維函數(shù)用“;”
利用數(shù)組函數(shù)求值
{=SUM(((B2:K2)={"事";"病";"婚"})*{1;2;3})}
數(shù)組引用
先計(jì)算日期位置，再用數(shù)組求和
{=SUM(INDEX(B2:B15,MAX((A2:A15=I4)*(ROW(A2:A15)-1))):INDEX(F2:F15,MAX((A2:A15=J4)*(ROW(A2:A15)-1))))}
列出交易額大于100的店鋪
{=INDEX($A$2:$A$19,MATCH(1,($B$2:$B$19>100)*(COUNTIF($G$1:G1,$A$2:$A$19)<1),0))}
找出不重復(fù)的店鋪
{=INDEX($A$2:$A$23,MATCH(0,COUNTIF($H$1:H1,$A$2:$A$23),0))}
2018/6/29
根據(jù)15或18位身份證提取信息
1秽五、性別
=IF(MOD(MID(A2,15,3),2)=1,"男","女")
2孽查、出生日期
=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00")
3、年齡
=DATEDIF(B2,TODAY(),"y")
4坦喘、星座
=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";21,"水瓶座";50,"雙魚(yú)座";81,"白羊座";112,"金牛座";143,"雙子座";174,"巨蟹座";205,"獅子座";236,"處女座";268,"天秤座";298,"天蝎座";328,"射手座";357,"摩羯座"},2,TRUE)
