Excel

Excel表格公式大全
excel把负数变成0的最简公式
判断A列数据，正数依旧显示为正数，负数显示为0，一般是这样设置公式
=IF(A1<0,0,A1)
如果再简单一些
=max(a1,0)
公式中参数如果是0，一般都可以省略，所以公式进一步简化为
=max(a1,)
========================================================================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單元格；

（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56區域70～84.5分的人數；假設把結果存放於K61單元格；

（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56區域60～69.5分的人數；假設把結果存放於K62單元格；

（7） =COUNTIF(K2:K56,"<60") ——求K2到K56區域60分以下的人數；假設把結果存放於K63單元格；

說明：COUNTIF函數也可計算某一區域男、女生人數。

如：=COUNTIF(C2:C351,"男") ——求C2到C351區域（共350人）男性人數；

9、優秀率： =SUM(K57:K60)/55*100

10、及格率： =SUM(K57:K62)/55*100

11、標準差： =STDEV(K2:K56) ——求K2到K56區域(55人)的成績波動情況（數值越小，說明該班學生間的成績差異較小，反之，說明該班存在兩極分化）；

12、條件求和： =SUMIF(B2:B56,"男"，K2:K56) ——假設B列存放學生的性別，K列存放學生的分數，則此函數返回的結果表示求該班男生的成績之和；

13、多條件求和： ｛=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))｝ ——假設C列（C3:C322區域）存放學生的性別，G列（G3:G322區域）存放學生所在班級代碼（1、2、3、4、5），則此函數返回的結果表示求一班的男生人數；這是一個數組函數，輸完後要按Ctrl＋Shift＋Enter組合鍵(產生“｛……｝”)。“｛｝”不能手工輸入，只能用組合鍵產生。

14、根據出生日期自動計算周歲：=TRUNC((DAYS360(D3,NOW( )))/360,0)

———假設D列存放學生的出生日期，E列輸入該函數後則產生該生的周歲。

15、在Word中三個小竅門：

①連續輸入三個“~”可得一條波浪線。

②連續輸入三個“-”可得一條直線。

連續輸入三個“=”可得一條雙直線。
===============================================
一、excel中當某一單元格符合特定條件，如何在另一單元格顯示特定的顏色比如：
A1〉1時，C1顯示紅色
0<A1<1時，C1顯示綠色
A1<0時，C1顯示黃色
方法如下：
1、單元擊C1單元格，點“格式”>“條件格式”，條件1設為：
公式=A1=1
2、點“格式”->“字體”->“顏色”，點擊紅色後點“確定”。
條件2設為：
公式=AND(A1>0,A1<1)
3、點“格式”->“字體”->“顏色”，點擊綠色後點“確定”。
條件3設為：
公式=A1<0
點“格式”->“字體”->“顏色”，點擊黃色後點“確定”。
4、三個條件設定好後，點“確定”即出。

二、EXCEL中如何控制每列數據的長度並避免重複錄入
1、用數據有效性定義數據長度。
用鼠標選定你要輸入的數據范圍，點"數據"->"有效性"->"設置"，"有效性條件"設成"允許""文本長度""等於""5"（具體條件可根據你的需要改變）。
還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好後點"確定"。
2、用條件格式避免重複。
選定A列，點"格式"->"條件格式"，將條件設成“公式=COUNTIF($A:$A,$A1)>1”，點"格式"->"字體"->"顏色"，選定紅色後點兩次"確定"。
這樣設定好後你輸入數據如果長度不對會有提示，如果數據重複字體將會變成紅色。

三、在EXCEL中如何把B列與A列不同之處標識出來？
（一）、如果是要求A、B兩列的同一行數據相比較：
假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，將條件設為:
“單元格數值” “不等於”=B2
點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。
用格式刷將A2單元格的條件格式向下複製。
B列可參照此方法設置。

（二）、如果是A列與B列整體比較（即相同數據不在同一行）：
假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，將條件設為:
“公式”=COUNTIF($B:$B,$A2)=0
點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。
用格式刷將A2單元格的條件格式向下複製。
B列可參照此方法設置。
按以上方法設置後，AB列均有的數據不著色，A列有B列無或者B列有A列無的數據標記為紅色字體。

四、EXCEL中怎樣批量地處理按行排序
假定有大量的數據(數值)，需要將每一行按從大到小排序，如何操作？
由於按行排序與按列排序都是只能有一個主關鍵字,主關鍵字相同時才能按次關鍵字排序。所以，這一問題不能用排序來解決。解決方法如下：
1、假定你的數據在A至E列，請在F1單元格輸入公式：
=LARGE($A1:$E1,COLUMN(A1))
用填充柄將公式向右向下複製到相應範圍。
你原有數據將按行從大到小排序出現在F至J列。如有需要可用“選擇性粘貼/數值”複製到其他地方。
注：第1步的公式可根據你的實際情況（數據范圍）作相應的修改。如果要從小到大排序,公式改為:=SMALL($A1:$E1,COLUMN(A1))

五、巧用函數組合進行多條件的計數統計
例：第一行為表頭，A列是“姓名”，B列是“班級”，C列是“語文成績”，D列是“錄取結果”，現在要統計“班級”為“二”，“語文成績”大於等於104，“錄取結果”為“重本”的人數。統計結果存放在本工作表的其他列。
公式如下：
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
輸入完公式後按Ctrl+ Shift+Enter鍵,讓它自動加上數組公式符號"{}"。

六、如何判斷單元格里 ​​是否包含指定文本？
假定對A1單元格進行判斷有無"指定文本",以下任一公式均可:
=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","無")
=IF(ISERROR(FIND("指定文本",A1,1)),"無","有")

求某一區域內不重複的數據個數
例如求A1:A100範圍內不重複數據的個數，某個數重複多次出現只算一個。有兩種計算方法：
一是利用數組公式：
=SUM(1/COUNTIF(A1:A100,A1:A100))
輸入完公式後按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{ }"。
二是利用乘積求和函數：
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

七、一個工作薄中有許多工作表如何快速整理出一個目錄工作表
1、用宏3.0取出各工作表的名稱，方法：
Ctrl+F3出現自定義名稱對話框，取名為X，在“引用位置”框中輸入：
=MID(GET.WORKBOOK(1),FIND( "]",GET.WORKBOOK(1))+1,100)
確定
2、用HYPERLINK函數批量插入連接，方法：
在目錄工作表（一般為第一個sheet）的A2單元格輸入公式：
=HYPERLINK("# '"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
將公式向下填充，直到出錯為止，目錄就生成了。

=====================================================================

1-Excel-給予儲存格號碼來加總(INDIRECT,ADDRESS)
網友問到一個問題：在 Excel 的工作表中，如果只給予儲存格的號碼，如何能計算加總？以下圖為例，如果給予 2 個號碼：22、27，要對照儲存格的位置(B22和B27)，再予以加總(=B22+B27=48+47=95)。



【公式設計與解析】

若要將英文字和數字所組成的文字，轉換成一個儲存格位址，則必須靠 INDIRECT 函數來處理。

儲存格G12：=INDIRECT("B"&D12)+INDIRECT("B"&D13)

其中 INDIRECT("B"&D12)，本例指為儲存格B22，INDIRECT("B"&D13)，本例指為儲存格B27。

或

儲存格G12：=INDIRECT(ADDRESS(D12,2))+INDIRECT(ADDRESS(D13,2))

ADDRESS(D12,2)：利用 ADDRESS 函數指定參數D12為列數，參數 2 為 B 欄，本例為儲存格B22。

複製儲存格G12，貼至儲存格G29。
您或許對這些文章有興趣:

Excel-在間隔的儲存格複製來源為連續儲存格的內容(INDIRECT,ADDRESS)
Excel-取用不連續工作表中相同位置儲存格的計算(INDIRECT,ADDRESS)
Excel-查詢多個工作表中的資料(INDIRECT,ADDRESS)
Excel-將資料欄列互換(轉置)(INDIRECT,ADDRESS,OFFSET)
Excel-個數不固定之加總
Linkwithin

您可能會有興趣的文章
Excel-摘要表格(INDIRECT+ADDRESS+ROW+陣列公式)
Excel-多條件查表應用(陣列應用,INT,ADDRESS,INDIRECT,SMALL)
Excel-製作梯形票價表及票價查詢
Excel-藉由定義名稱轉換表格來摘要資料(陣列公式,OFFSET,INDIRECT)
家中網速升級推薦!好康大加碼

DEC 02 2015
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

本範例是延續前四篇文章的研習講義：
1. Excel-SUMPRODUCT函數範例與說明(研習範例)
2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)
3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
4. Excel-多人多項的金額統計(SUMPRODUCT練習)
這次來練習在 SUMPRODUCT 函數中搭配其他函數達成篩選並統計的功能。參考下圖，列有五種計算一二三年級各項比賽的人數。



【準備工作】
假設報名資料的儲存格範圍是A1:G49。選取儲存格A1:G49，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、班級編碼、班級名稱、大隊男、大隊女、跳繩、拔河。

作法(一)：未使用名稱
儲存格J3：=SUMPRODUCT(($A$2:$A$49=$I3)*(D$2:D$49))
複製儲存格J3，貼至儲存格J3:M5。


作法(二)：使用名稱
儲存格J8：=SUMPRODUCT((年級=$I9)*(INDIRECT(J$8)))
INDIRECT(J$8)：使用 IINDIRECT 函數將儲存格J8的內容轉換為一個『名稱』，而該名稱已定義了儲存格位址。
複製儲存格J8，貼至儲存格J8:M10。


作法(三)：使用『年級』欄位
儲存格J13：=SUMPRODUCT((LEFT(年級,1)=$I15)*(INDIRECT(J$14)))
LEFT(年級,1)：使用 LEFT 函數取出『年級』儲存格陣列中的每一個內容的第一個字元，再和儲存格I15來比對判斷。
複製儲存格J13，貼至儲存格J13:M15。


作法(四)：使用『班級編碼』欄位
儲存格J18：=SUMPRODUCT((LEFT(班級編碼,1)=LEFT($I21,1))*(INDIRECT(J$20)))
複製儲存格J18，貼至儲存格J18:M20。


作法(五)：使用『班級編碼』欄位
儲存格J23：
=SUMPRODUCT((VALUE(LEFT(班級編碼,1))=ROW(1:1))*(INDIRECT(J$2)))
ROW(1:1)=1，當複製後向下貼上時，ROW(1:1)→ROW(2:2)=2→ROW(3:3)=3。
複製儲存格J23，貼至儲存格J23:M25。

======================================================================
2.【Excel】讓儲存格依據條件"自動"變色呢?-格式化條件相信很多人在建立或維護Excel資料時…常常為了凸顯某些特定的資料而使用"顏色"來做分類標示。這是一個很好的方式，也讓你的資料更容易辨識，也顯得更專業些!
(不過，千萬別弄得花花綠綠滴，反而失去了焦點)
不過如果你要凸顯的資料是有特定條件的，例如金額大於10,000元，用綠色來標示；小於10,000元用紅色。你會怎麼做?
一筆一筆看…然後逐筆更改文字格式或儲存格底色嗎?那下次金額更改時，又要手動調整一次?今天我們要上的課，就是教你如何讓Excel自動幫你變更顏色~

一起來讓Excel幫你做事吧!!別讓它閒著了~
〔格式化條件〕

範例：業務員業績若大於等於業績目標，業績欄位更改為綠色，若未達目標，欄位改為紅色。

分析：需要B3~B7的儲存可與B1的目標來做比較，>=B1則更改底色為綠色。

             <B1則更改為紅色。

作法：
《1》我們先在第一筆資料(B3)，設定〔格式化條件〕


點選〔設定格式化條件〕→〔新增規則〕，會開啟以下視窗：

規則類型有非常多的選項，老師最常用的就是最後一項<1>〔使用公式來決定要格式化哪些儲存格〕，因為這一項大概就可以解決95%的問題，而剩下的5%問題…我想，你我要遇到的機率都非常的低~~(基於腦容量永遠都不夠的限制條件，只要學最常需要的即可)

《2》選取後，就會跳出格式化的公式輸入區，點選<2>


在格式化規則裡，輸入 =$B3>=$B$1
這裡要輸入的就是你要改變顏色的條件，複習一下題目：業務員業績($B3)若大於等於(>=)業績目標($B$1)，業績欄位更改為綠色
◎各位有沒注意到，儲存格欄列前老師有放上$這個絕對位置符號。這個很重要，有概念的學員就先試吧!因為這個要說明…實在是又得開一堂概念課了!!這裡就先暫時跳過去吧!
但業績目標，因為每個業務員要比對的目標，都是B1這個欄位，所以請務必打成$B$1喔~
◎另外有沒有人很厲害，注意到條件中有個顏色特別不一樣的…是的，就是那個=，這個是一般人在設定絛件時，很容易常漏掉的。記得"條件前"還要加"="喔!

《3》條件設好就來設定格式囉~




大夥兒看到這裡可以設定的，都可使用喔!包括字型的顏色、大小等，儲存格框線(老師常常用這個請Excel幫我畫框線，因為我太懶了)，儲存格底色。

●●到這裡…我們就已將第一個格式化條件設好了!!給自己鼓勵一下~~
第二個條件：業務員業績($B3)若小於(<)業績目標($B$1)，業績欄位更改為紅色
就當練習實作的功課吧!重覆《1》~《3》的動作，再設定一次!

如果你完成了…千萬別以為結束了喔!!因為目前只設定了B3這一個儲存格的格式化條件…別忘了我們的業績欄位是B3~B7

《4》〔設定格式化的條件〕→〔管理規則〕：我們來針對剛剛設定的條件，把適用的範圍加上去。





在〔套用到〕的設定裡，預設都=$B$3。請直接將它改為=$B$3:$B$7
◎套用到這個功能，是在MSOffice Excel 2007版以後才有的喔!如果是之前的版本，最多只能設3個格式化設定。而且適用的範圍只能用儲存格格式複製過去(這時就考驗你寫條件的絕對相對位置的功力…嘿嘿)

做完這一步…你會發現…耶~~格式自動改變了喔!

=======================================================================

3.ABS 函數
傳回數字的絕對值。 所謂數字的絕對值，就是不含符號的數字。
語法ABS(number)

ABS 函數語法具有下列引數：

Number 必要。 這是要求得絕對值的實數。
範例

複製以下表格，並貼至 Excel 的儲存格 A1 中。 您可能需要選取內含公式的所有儲存格，然後按 F2 和 Enter，才能使公式運作。 您可能會想要將欄位加寬，使工作表更容易閱讀。

資料
-4
公式                 描述                  結果
=ABS(2)          2 的絕對值          2
=ABS(-2)       -2 的絕對值           2
=ABS(A2)      -4 的絕對值          4

請參閱
將數字相減
將數字相乘
計算百分比

========================================================================