《常用電子表格公式》由會(huì)員分享，可在線閱讀，更多相關(guān)《常用電子表格公式（4頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

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