excel表格公式大全

《excel表格公式大全》由會員分享，可在線閱讀，更多相關(guān)《excel表格公式大全（4頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1 查找重復(fù)內(nèi)容公式 IF COUNTIF A A A2 1 重復(fù) 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 從輸入的身份證號碼內(nèi)讓系統(tǒng)自動提取性別 可以輸入以下公式 IF LEN C2 15 IF MOD MID C2 15 1 2 1 男 女 IF MOD MID C2 17 1 2 1 男 女 公式內(nèi)的 C2 代表的是輸入身份證號碼的單元格 1 求和 SUM K2 K56 對 K2 到 K56 這一區(qū)域進(jìn)行求和 2 平均數(shù) AVERAGE K2 K56 對 K2 K56 這一區(qū)域求平均數(shù) 3 排名 RANK K2 K 2 K 56 對 55 名學(xué)生的成績進(jìn)行排名 4 等級 IF K2 85 優(yōu) IF K2 74 良 IF K2 60 及格 不及格 5 學(xué)期總評 K2 0 3 M2 0 3 N2 0 4 假設(shè) K 列 M 列和 N 列分別存放著學(xué)生的 平時總評 期中 期末 三項成績 6 最高分 MAX K2 K56 求 K2 到 K56 區(qū)域 55 名學(xué)生 的最高分 7 最低分 MIN K2 K56 求 K2 到 K56 區(qū)域 55 名學(xué)生 的最低分 8 分?jǐn)?shù)段人數(shù)統(tǒng)計 1 COUNTIF K2 K56 100 求 K2 到 K56 區(qū)域 100 分的人數(shù) 假設(shè)把結(jié)果存放于 K57 單元格 2 COUNTIF K2 K56 95 K57 求 K2 到 K56 區(qū)域 95 99 5 分的人數(shù) 假設(shè)把結(jié)果存放于 K58 單元格 3 COUNTIF K2 K56 90 SUM K57 K58 求 K2 到 K56 區(qū)域 90 94 5 分的人數(shù) 假設(shè)把結(jié)果存放于 K59 單元格 4 COUNTIF K2 K56 85 SUM K57 K59 求 K2 到 K56 區(qū)域 85 89 5 分的人數(shù) 假設(shè)把結(jié)果存放于 K60 單元格 5 COUNTIF K2 K56 70 SUM K57 K60 求 K2 到 K56 區(qū)域 70 84 5 分的人數(shù) 假設(shè)把結(jié)果存放于 K61 單元格 6 COUNTIF K2 K56 60 SUM K57 K61 求 K2 到 K56 區(qū)域 60 69 5 分的人數(shù) 假設(shè)把結(jié)果存放于 K62 單元格 7 COUNTIF K2 K56 60 求 K2 到 K56 區(qū)域 60 分以下的人數(shù) 假設(shè)把結(jié)果存放于 K63 單元格 說明 COUNTIF 函數(shù)也可計算某一區(qū)域男 女生人數(shù) 如 COUNTIF C2 C351 男 求 C2 到 C351 區(qū)域 共 350 人 男性人數(shù) 9 優(yōu)秀率 SUM K57 K60 55 100 10 及格率 SUM K57 K62 55 100 11 標(biāo)準(zhǔn)差 STDEV K2 K56 求 K2 到 K56 區(qū)域 55 人 的成績波動情況 數(shù)值越小 說明該班學(xué)生間的成績差異較小 反之 說明該班存在兩極分化 12 條件求和 SUMIF B2 B56 男 K2 K56 假設(shè) B 列存放學(xué)生的性別 K 列存放學(xué)生的分?jǐn)?shù) 則此函數(shù)返回的結(jié)果表示求該班男生的成績之和 13 多條件求和 SUM IF C3 C322 男 IF G3 G322 1 1 0 假設(shè) C 列 C3 C322 區(qū)域 存放學(xué)生的性別 G 列 G3 G322 區(qū)域 存放學(xué)生所 在班級代碼 1 2 3 4 5 則此函數(shù)返回的結(jié)果表示求一班的男生人數(shù) 這是一個數(shù)組函數(shù) 輸完后要按 Ctrl Shift Enter 組合鍵 產(chǎn)生 不能手工輸入 只能用組合鍵產(chǎn)生 14 根據(jù)出生日期自動計算周歲 TRUNC DAYS360 D3 NOW 360 0 假設(shè) D 列存放學(xué)生的出生日期 E 列輸入該函數(shù)后則產(chǎn)生該生的周歲 15 在 Word 中三個小竅門 連續(xù)輸入三個 可得一條波浪線 連續(xù)輸入三個 可得一條直線 連續(xù)輸入三個 可得一條雙直線 一 excel 中當(dāng)某一單元格符合特定條件 如何在另一單元格顯示特定的顏色比如 A1 1 時 C1 顯示紅色 0 A1 1 時 C1 顯示綠色 A1 條件格式 條件 1 設(shè)為 公式 A1 1 2 點 格式 字體 顏色 點擊紅色后點 確定 條件 2 設(shè)為 公式 AND A1 0 A1 字體 顏色 點擊綠色后點 確定 條件 3 設(shè)為 公式 A1 字體 顏色 點擊黃色后點 確定 4 三個條件設(shè)定好后 點 確定 即出 二 EXCEL 中如何控制每列數(shù)據(jù)的長度并避免重復(fù)錄入 1 用數(shù)據(jù)有效性定義數(shù)據(jù)長度 用鼠標(biāo)選定你要輸入的數(shù)據(jù)范圍 點 數(shù)據(jù) 有效性 設(shè)置 有效性條件 設(shè)成 允許 文本長度 等于 5 具體條件可根據(jù)你的需要改變 還可以定義一些提示信息 出錯警告信息和是否打開中文輸入法等 定義好后點 確定 2 用條件格式避免重復(fù) 選定 A 列 點 格式 條件格式 將條件設(shè)成 公式 COUNTIF A A A1 1 點 格式 字體 顏色 選定紅色后點兩次 確定 這樣設(shè)定好后你輸入數(shù)據(jù)如果長度不對會有提示 如果數(shù)據(jù)重復(fù)字體將會變成紅色 三 在 EXCEL 中如何把 B 列與 A 列不同之處標(biāo)識出來 一 如果是要求 A B 兩列的同一行數(shù)據(jù)相比較 假定第一行為表頭 單擊 A2 單元格 點 格式 條件格式 將條件設(shè)為 單元格數(shù)值 不等于 B2 點 格式 字體 顏色 選中紅色 點兩次 確定 用格式刷將 A2 單元格的條件格式向下復(fù)制 B 列可參照此方法設(shè)置 二 如果是 A 列與 B 列整體比較 即相同數(shù)據(jù)不在同一行 假定第一行為表頭 單擊 A2 單元格 點 格式 條件格式 將條件設(shè)為 公式 COUNTIF B B A2 0 點 格式 字體 顏色 選中紅色 點兩次 確定 用格式刷將 A2 單元格的條件格式向下復(fù)制 B 列可參照此方法設(shè)置 按以上方法設(shè)置后 AB 列均有的數(shù)據(jù)不著色 A 列有 B 列無或者 B 列有 A 列無的數(shù)據(jù)標(biāo)記為紅色字體 四 EXCEL 中怎樣批量地處理按行排序 假定有大量的數(shù)據(jù) 數(shù)值 需要將每一行按從大到小排序 如何操作 由于按行排序與按列排序都是只能有一個主關(guān)鍵字 主關(guān)鍵字相同時才能按次關(guān)鍵字排序 所以 這一問題不能用排序來解決 解決方法如下 1 假定你的數(shù)據(jù)在 A 至 E 列 請在 F1 單元格輸入公式 LARGE A1 E1 COLUMN A1 用填充柄將公式向右向下復(fù)制到相應(yīng)范圍 你原有數(shù)據(jù)將按行從大到小排序出現(xiàn)在 F 至 J 列 如有需要可用 選擇性粘貼 數(shù)值 復(fù)制到其他地方 注 第 1 步的公式可根據(jù)你的實際情況 數(shù)據(jù)范圍 作相應(yīng)的修改 如果要從小到大排序 公式改為 SMALL A1 E1 COLUMN A1 五 巧用函數(shù)組合進(jìn)行多條件的計數(shù)統(tǒng)計 例 第一行為表頭 A 列是 姓名 B 列是 班級 C 列是 語文成績 D 列是 錄取結(jié)果 現(xiàn)在要統(tǒng)計 班級 為 二 語文成績 大于等于 104 錄取結(jié)果 為 重本 的人數(shù) 統(tǒng)計結(jié)果存放在本工作表的其他列 公式如下 SUM IF B2 B9999 二 C2 C9999 104 D2 D9999 重本 1 0 輸入完公式后按 Ctrl Shift Enter 鍵 讓它自動加上數(shù)組公式符號 六 如何判斷單元格里是否包含指定文本 假定對 A1 單元格進(jìn)行判斷有無 指定文本 以下任一公式均可 IF COUNTIF A1 指定文本 1 有 無 IF ISERROR FIND 指定文本 A1 1 無 有 求某一區(qū)域內(nèi)不重復(fù)的數(shù)據(jù)個數(shù) 例如求 A1 A100 范圍內(nèi)不重復(fù)數(shù)據(jù)的個數(shù) 某個數(shù)重復(fù)多次出現(xiàn)只算一個 有兩種計算方法 一是利用數(shù)組公式 SUM 1 COUNTIF A1 A100 A1 A100 輸入完公式后按 Ctrl Shift Enter 鍵 讓它自動加上數(shù)組公式符號 二是利用乘積求和函數(shù) SUMPRODUCT 1 COUNTIF A1 A100 A1 A100 七 一個工作薄中有許多工作表如何快速整理出一個目錄工作表 1 用宏 3 0 取出各工作表的名稱 方法 Ctrl F3 出現(xiàn)自定義名稱對話框 取名為 X 在 引用位置 框中輸入 MID GET WORKBOOK 1 FIND GET WORKBOOK 1 1 100 確定 2 用 HYPERLINK 函數(shù)批量插入連接 方法 在目錄工作表 一般為第一個 sheet 的 A2 單元格輸入公式 HYPERLINK INDEX X ROW A1 INDEX X ROW 將公式向下填充 直到出錯為止 目錄就生成了