將單元格設(shè)置為有“凸出”的效果或“凹進(jìn)去”的效果
用條件格式=mod(row(),2)=mod(column(),2)
方法是設(shè)定單元格的邊框
3樓的辦法不錯(cuò)，但是要一個(gè)格一個(gè)格地設(shè)定，數(shù)據(jù)多了很麻煩
2樓的格式里設(shè)公式能不能搞成隔一行ao隔一行tu的形式呢？
格式—自動(dòng)套用格式里就有。
湊個(gè)熱鬧。邊框用黑白的就可以了
看來(lái)還是用條件格式更方便些！
用黑白雙線邊框是最簡(jiǎn)單的辦法
在Excel中設(shè)計(jì)彩色數(shù)字
用戶在使用Excel處理數(shù)據(jù)時(shí)，經(jīng)常需要將某些數(shù)據(jù)以特殊的形式顯示出來(lái)，這樣可以起到醒目的作用，使瀏覽者一目了然。如在某用戶的Excel單元格中有“月工資”一欄，需要小于500的顯示為綠色，大于500的顯示為紅色，則可以采用以下的方法來(lái)操作：選中需要進(jìn)行彩色設(shè)置的單元格區(qū)域，選擇“格式”→“單元格”，在彈出的對(duì)話框中單擊“數(shù)字”選項(xiàng)卡。然后選擇“分類(lèi)”列表中的“自定義”選項(xiàng)，在“類(lèi)型”框中輸入“［綠色］［＜500；［紅色］［＞＝500］”，最后單擊“確定”按鈕即可。
小提示
除了紅色和綠色外，用戶還可以使用六種顏色，它們分別是黑色、青色、藍(lán)色、洋紅、白色和黃色。另外，“［＞＝120］”是條件設(shè)置，用戶可用的條件運(yùn)算符有：“＞”、“＜”、“＞＝”、“＜＝”、“＝”、“＜＞”。當(dāng)有多個(gè)條件設(shè)置時(shí)，各條件設(shè)置以分號(hào)“；”作為間隔。
定義名稱(chēng)的妙處
名稱(chēng)的定義是EXCEL的一基礎(chǔ)的技能，可是，如果你掌握了，它將給你帶來(lái)非常實(shí)惠的妙處！
1. 如何定義名稱(chēng)
插入-名稱(chēng)-定義
2. 定義名稱(chēng)
建議使用簡(jiǎn)單易記的名稱(chēng)，不可使用類(lèi)似A1…的名稱(chēng)，因?yàn)樗鼤?huì)和單元格的引用混淆。還有很多無(wú)效的名稱(chēng)，系統(tǒng)會(huì)自動(dòng)提示你。
引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。
在引用工作表單元格或者公式的時(shí)候，絕對(duì)引用和相對(duì)引用是有很大區(qū)別的，注意體會(huì)他們的區(qū)別 –和在工作表中直接使用公式時(shí)的引用道理是一樣的。
3. 定義名稱(chēng)的妙處1 – 減少輸入的工作量
如果你在一個(gè)文檔中要輸入很多相同的文本，建議使用名稱(chēng)。例如：定義DATA = “I LOVE YOU， EXCEL！”，你在任何單元格中輸入“=DATA”，都會(huì)顯示“ILOVE YOU， EXCEL！”
4. 定義名稱(chēng)的妙處2 – 在一個(gè)公式中出現(xiàn)多次相同的字段
例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),””,IF(A1>B1,A1/B1,A1))，這里你就可以將IF(A1>B1,A1/B1,A1)定義成名稱(chēng)“A_B”，你的公式便簡(jiǎn)化為=IF(ISERROR(A_B),””,A_B)
5. 定義名稱(chēng)的妙處3 – 超出某些公式的嵌套
例如IF函數(shù)的嵌套最多為七重，這時(shí)定義為多個(gè)名稱(chēng)就可以解決問(wèn)題了。也許有人要說(shuō)，使用輔助單元格也可以。當(dāng)然可以，不過(guò)輔助單元格要防止被無(wú)意間被刪除。
6. 定義名稱(chēng)的妙處4 – 字符數(shù)超過(guò)一個(gè)單元格允許的最大量
名稱(chēng)的引用位置中的字符最大允許量也是有限制的，你可以分割為兩個(gè)或多個(gè)名稱(chēng)。同上所述，輔助單元格也可以解決此問(wèn)題，不過(guò)不如名稱(chēng)方便。
7. 定義名稱(chēng)的妙處5 – 某些EXCEL函數(shù)只能在名稱(chēng)中使用
例如由公式計(jì)算結(jié)果的函數(shù)，在A1中輸入’=1+2+3，然后定義名稱(chēng) RESULT = EVALUATE(Sheet1!$A1)，最后你在B1中寫(xiě)入=RESULT，B1就會(huì)顯示6了。
還有GET.CELL函數(shù)也只能在名稱(chēng)中使用，請(qǐng)參考相關(guān)資料。
8. 定義名稱(chēng)的妙處6 – 圖片的自動(dòng)更新連接
例如你想要在一周內(nèi)每天有不同的圖片出現(xiàn)在你的文檔中，具體做法是：
8.1 找7張圖片分別放在SHEET1A1至A7單元格中，調(diào)整單元格和圖片大小，使之恰好合適
8.2 定義名稱(chēng)MYPIC =OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)
8.3 控件工具箱 –文字框，在編輯欄中將EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。
這里如果不使用名稱(chēng)，應(yīng)該是不行的。
此外，名稱(chēng)和其他，例如數(shù)據(jù)有效性的聯(lián)合使用，會(huì)有更多意想不到的結(jié)果。
零值問(wèn)題
在工作表中隱藏所有零值
在Excel默認(rèn)情況下，零值將顯示為0，這個(gè)值是一個(gè)比較特殊的數(shù)值。如果工作表中包含了大量的零值，會(huì)使整個(gè)工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇“工具”→“選項(xiàng)”，打開(kāi)“選項(xiàng)”對(duì)話框，單擊“視圖”標(biāo)簽，在“窗口選項(xiàng)”里把“零值”復(fù)選框前面的對(duì)號(hào)去掉，單擊“確定”按鈕。此時(shí)，可以看到原來(lái)顯示有0的單元格全部變成了空白單元格。
小提示
若要在單元格里重新顯示0，用上述方法把“零值”復(fù)選框前面的打上對(duì)號(hào)即可。
隱藏部分零值
有些時(shí)候可能需要有選擇地隱藏部分零值，使隱藏的零值只會(huì)出現(xiàn)在編輯欄或正在編輯的單元格中，而不會(huì)被打印，這時(shí)候就要通過(guò)設(shè)置自定義數(shù)字格式來(lái)實(shí)現(xiàn)：先按住Ctrl鍵用鼠標(biāo)左鍵一一選定需要隱藏零值的單元格，然后選擇“格式”→“單元格”，在“單元格格式”對(duì)話框選擇“數(shù)字”選項(xiàng)卡，在“分類(lèi)”列表框中選擇“自定義”選項(xiàng)，然后在右邊的“類(lèi)型”文本框中輸入“0；_0；；@”，單擊“確定”按鈕。
要將隱藏的零值重新顯示出來(lái)，可選定單元格，然后在“單元格格式”對(duì)話框的“數(shù)字”選項(xiàng)卡中，單擊“分類(lèi)”列表中的“常規(guī)”選項(xiàng)，這樣就可以應(yīng)用默認(rèn)的格式，隱藏的零值就會(huì)顯示出來(lái)。
條件隱藏零值
利用條件格式也可以實(shí)現(xiàn)有選擇地隱藏部分零值：首先選中包含零值的單元格，選擇“格式”→“條件格式”，在“條件1”的第一個(gè)框中選擇“單元格數(shù)值”，第二個(gè)框中選擇“等于”，在第三個(gè)框中輸入0，然后單擊“格式”按鈕，設(shè)置“字體”的顏色為“白色”即可。
如果要顯示出隱藏的零值，請(qǐng)先選中隱藏零值的單元格，然后選擇“格式”菜單中“條件格式”，單擊“刪除”按鈕，在彈出的“選定要?jiǎng)h除的條件”對(duì)話框中選擇“條件1”即可。
使用公式將零值顯示為空白
還可以使用IF函數(shù)來(lái)判斷單元格是否為零值，如果是的話就返回空白單元格，例如公式“=IF(A2-A3=0，""，A2-A3)”，如果A2等于A3，那么它們相減的值為零，則返回一個(gè)空白單元格；如果A2不等于A3，則返回它們相減的差值。
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【匯總計(jì)算與統(tǒng)計(jì)】
個(gè)調(diào)稅公式
=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}
-{0,0,25,125,375,1375,3375,6375,10375,15375})
{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}為稅率
{0,0,25,125,375,1375,3375,6375,10375,15375} 為稅收扣除數(shù)
上列公式的簡(jiǎn)化式 :
=MAX(應(yīng)納稅所得額*0.05*{1,2,3,4,5,6,7,8,9}
-25*{0,1,5,15,55,135,255,415,615},0)
算物價(jià)的函數(shù)
物價(jià)的那個(gè)三七作五，二舍八入的尾數(shù)處理，做一個(gè)函數(shù)。就是小數(shù)點(diǎn)后面第二位如果是1，2的就舍掉，如果是3，4，5，6，7的都變?yōu)?，如果是8，9的小數(shù)點(diǎn)第一位加1，第二位就變?yōu)?。比如價(jià)格是3.32、3.31，作尾數(shù)處理就是3.3；價(jià)格是3.33、3.34、3.36、3.37，做尾數(shù)處理就是3.35；價(jià)格是3.38、3.39，做尾數(shù)處理就是3.4。
=CEILING(A1-0.02,0.05)
都是二位小數(shù) B2=ROUND(2*A2,1)/2
超過(guò)二位小數(shù) B2=ROUND(2*ROUNDDOWN(A2,2),1)/2
要求在給定的應(yīng)收日期、滯納金率、當(dāng)前日期（自動(dòng)取）的基礎(chǔ)上自動(dòng)計(jì)算出應(yīng)收滯納金。
解答：=(DATEDIF(應(yīng)收日期,NOW(),"d"))*滯納金率（每天）*應(yīng)收金額
淘汰率
題目如下：這個(gè)工廠有1000人，今天抽出十人來(lái)做調(diào)查，這十人一天的產(chǎn)量分別為101 102 105 106 98 95 96 104 110 103（A3-A12）。
1000人當(dāng)中淘汰率為5%，以這十人為標(biāo)準(zhǔn)那么這1000人他們的生產(chǎn)應(yīng)該為多少才不會(huì)被淘汰，看看函數(shù)的幫助就知道了呀，返回?cái)?shù)組K百分比值點(diǎn)，你要1000人淘汰5人就是5/1000=0.5%=0.005，就是你以這10個(gè)抽樣調(diào)查的數(shù)據(jù)為基準(zhǔn)，只要產(chǎn)量達(dá)到這個(gè)數(shù)就不會(huì)被淘汰了。（95.45）
公式=PERCENTILE(A3:A12,G1)
應(yīng)用公積金的一個(gè)函數(shù)
我公司職工公積金比例為26％ 也就是個(gè)人和單位各13％，給公積金投繳人員制作了一個(gè)函數(shù)。直接用基數(shù)乘以比例 基數(shù)*比例＝投繳額， 對(duì)于投繳額的要求是：取最接近“投繳額”的偶數(shù)。
我制作的函數(shù)是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))”
注：A1=基數(shù) B1＝投繳比例
也可以改成這樣
=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)
或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))
如何利用公式將數(shù)值轉(zhuǎn)為百分比格式
如用公式將1.289675顯示為128.97%，不是用格式來(lái)達(dá)到的。
公式＝ROUND(B1*100,1)&"%"
比高得分公式
=RANK(B4,$B$4:$B$26,1)
自動(dòng)評(píng)定獎(jiǎng)級(jí)
=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等獎(jiǎng)","二等獎(jiǎng)","三等獎(jiǎng)","紀(jì)念獎(jiǎng)","紀(jì)念獎(jiǎng)"})
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
對(duì)帶有單位的數(shù)據(jù)如何進(jìn)行求和
在數(shù)據(jù)后必須加入單位，到最后還要統(tǒng)計(jì)總和，請(qǐng)問(wèn)該如何自動(dòng)求和？（例如：A1：2KG，A2：6KG.....，在最后一行自動(dòng)計(jì)算出總KG數(shù)）。
=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”
對(duì)a列動(dòng)態(tài)求和
可以隨著a列數(shù)據(jù)的增加，在“b1”單元格=sum(x)對(duì)a列動(dòng)態(tài)求和。
=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))
動(dòng)態(tài)求和公式
自A列A1單元格到當(dāng)前行前面一行的單元格求和。
=SUM(INDIRECT("A1:A"&ROW()-1))
列的跳躍求和
若有20列（只有一行），需沒(méi)間隔3列求和，該公式如何做？
假設(shè)a1至t1為數(shù)據(jù)（共有20列），在任意單元格中輸入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))
按ctrl＋shift＋enter結(jié)束即可求出每隔三行之和。
跳行設(shè)置：如有12行，需每隔3行求和
=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))
有規(guī)律的隔行求和
要求就是在計(jì)劃、實(shí)際、差異三項(xiàng)中對(duì)后面的12個(gè)月求和。
=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)
=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)
=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)
也可以拖動(dòng)填充，插入行、列也不影響計(jì)算結(jié)果。
如何實(shí)現(xiàn)奇數(shù)行或偶數(shù)行求和
假設(shè)數(shù)據(jù)在A1:A100
奇數(shù)行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)
偶數(shù)行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))
奇數(shù)行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))
偶數(shù)行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))
單數(shù)行求和
隔行求和用什么函數(shù)，即：A1+A3+A5+A7+A9…公式如何用。
{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}
{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}
統(tǒng)計(jì)F4到F62的偶數(shù)單元格合計(jì)數(shù)值 。｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝
隔行求和公式設(shè)置
均為數(shù)組公式：
=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))
=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))
=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))
=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)
隔列將相同項(xiàng)目進(jìn)行求和
隔列將出勤日和工資分別進(jìn)行求和
數(shù)組公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))
或；
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)
隔行或隔列加總
隔2列加總
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)
隔2欄加總
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
請(qǐng)問(wèn)如何在一百行內(nèi)做隔行相加
數(shù)組公式
A1+A3+……+A99 單
=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))
A2+A4+……+A100 雙
=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))
如何將間隔一定的列的數(shù)據(jù)相加呢
碰到100多列的數(shù)據(jù)將間隔一定的數(shù)據(jù)用手工相加太煩了，也容易出錯(cuò)。如果需要相加的數(shù)據(jù)均有相同的名稱(chēng)（字段），可以用Sumif()來(lái)求解，如果沒(méi)有，就需要用數(shù)組公式來(lái)解決了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)} 1、4、7……行相加。
隔列求和（A、B列）
=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C) （統(tǒng)計(jì)A、B、C列）
隔列求和的公式
品種及日期
1月1日
1月2日
1月3日
1月4日
1月5日
余額
進(jìn)
出
進(jìn)
出
進(jìn)
出
進(jìn)
出
進(jìn)
出
A
1
1
2
5
3
2
7
9
8
1
3
=SUMIF($B$2:$K$2,"進(jìn)",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3)
=SUM(SUMIF(B$2:K$2,{"進(jìn)","出"},B3:K3)*{1,-1})
隔列求和
類(lèi)別
成品代碼
單價(jià)
安貞
北辰
長(zhǎng)安
長(zhǎng)春
合計(jì)
庫(kù)存
銷(xiāo)售
庫(kù)存
銷(xiāo)售
庫(kù)存
銷(xiāo)售
庫(kù)存
銷(xiāo)售
庫(kù)存
銷(xiāo)售
皮帶
V19201
270.00
1
2
1
2
1
2
1
2
庫(kù)存合計(jì)=SUMIF($D$3:$BS$3,"庫(kù)存",$D$4:$BT$4),
銷(xiāo)售合計(jì)=SUMIF($D$3:$BS$3,"銷(xiāo)售",$D$4:$BT$4)
=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)
=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)
關(guān)于隔行、隔列求和的問(wèn)題
隔2列加總
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)
隔2行加總
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
均為數(shù)組公式。
EXCEL中求兩列的對(duì)應(yīng)元素乘積之和
如:a1*b1+a2*b2+b3*b3...的和
=SUM(A1:A3*B1:B3)(數(shù)組公式)
=SUMPRODUCT(A1:A10,B1:B10)
計(jì)算900~1000之間的數(shù)值之和
sumif函數(shù)的計(jì)算格式為:=sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大于1000的數(shù)值的和,但如果想計(jì)算900~1000之間的數(shù)值之和,應(yīng)該如何編寫(xiě)。
請(qǐng)參考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或{=SUM((900<1000)*A1:A20)}
2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")
雙條件求和
1、 求一班女生的個(gè)數(shù) :
=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))
2、求一班成績(jī)的和 :
=SUMIF(A2:A9,1,C2:C9)"
3、求一班男生成績(jī)的和 :
=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9)"
如何實(shí)現(xiàn)這樣的條件求和
求型號(hào)中含BC但不含ABC的量:
A
B
C
型號(hào)
數(shù)量
1
CRVABC12
100
2
CVABC13
102
3
CVBC12
104
4
CNVBC13
106
=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$B$2:$B$12)
=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)
A1:A10數(shù)字顯為文本格式時(shí)，如何求和
=SUMPRODUCT(A1:A10)
求和
所有本范例所使用的數(shù)據(jù)都為引用以下綠色區(qū)域，并定義為對(duì)應(yīng)的標(biāo)題。
Name
Sex
Age
Position
Salary
張無(wú)忌
男
26
主角
10000
韋小寶
男
16
主角
13000
滅絕
女
55
配角
3000
周芷若
女
22
主角
8000
鰲拜
男
62
普通演員
2000
儀琳
女
18
配角
5000
岳靈珊
女
19
配角
4500
令狐沖
男
27
主角
15000
性空
男
88
普通演員
2200
東方不敗
不詳
45
主角
9000
A 求所有演員工資總額
71700 =SUM($G$7:$G$16)
簡(jiǎn)單求和
B 求男演員工資總額
42200 =SUMIF($D$7:$D$16,"男",$G$7:$G$16)
單條件求和.1
C 求年齡在20歲以下的演員工資
22500 =SUMIF($E$7:$E$16,"<20",$G$7:$G$16)
單條件求和.2
D 求主角和配角的工資(不是普通演員)
67500 =SUMIF($F$7:$F$16,"*角",$G$7:$G$16)
單條件求和.3
E 求20歲以下女演員工資
9500 {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)}
多條件求和-同時(shí)滿足條件
F 求男性或主角的工資
59200 {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))}
多條件求和-只須滿足條件之一
G 求男性非主角或主角非男性的工資(即除男主角外的男性和主角)
g.1 21200 {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))}
g.2 21200 {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))}
多條件求和-只滿足條件之一而不能同時(shí)滿足
H 啊~~~你不知道什么是數(shù)組函數(shù)啊，可是你有時(shí)候也要用多條件求和？
不要緊，教你用另外的方法：SUBTOTAL
求20歲以下女演員工資
71700 =SUBTOTAL(9,$G$7:$G$16)
現(xiàn)在你看到的還不是最后結(jié)果，請(qǐng)按如下操作
1、把數(shù)據(jù)區(qū)域設(shè)置成可篩選
2、把SEX篩選成"=女", 把年齡篩選成<20
3、你再看上面的公式結(jié)果…
分享
