excel 2007 常用技巧整理
一、 基本操作
1. excel初始化: 左上角介面office 按鈕-excel選項,進行excel的初始化啟動引數設定,包括新工作薄的格式設定,最近檔案列表,使用者名稱和預設位置等;
2. xlstart資料夾:excel選項-高階-啟動時自動開啟此目錄中的所有檔案;
3. xlt檔案:儲存為.xlt檔案,可作為模板使用;
4. xlw檔案:效果同xlstart資料夾;
5. 為工作簿**:定位-物件;檢測資料有效性
6. 自動修復:檔案-開啟-對話方塊中選擇「開啟並修復」;recovery for excel
7. shift在選擇行與列的使用:選擇非連續區域(shift+f8);選定整行和整列(shift);反向選擇剩餘的行列(ctrl+shift+方向)
8. ctrl+y和ctrl+z:撤銷和重複;
9. 快速切換工作表:右鍵單擊導航按鈕;ctrl+pageup;
10. 選擇特殊區域-「定位」:f5或者開始-查詢下選單;
1) 定位條件:批註、常量、公式、空值、物件等;
2) 應用1-填充空白單元格:定位-空值,輸入0,按ctrl+enter(同時輸入),空白單元格被0填充;
11. 隱藏秘密資料:
1) ctrl+9,隱藏單元格所在行;ctrl+0,隱藏單元格所在列;
2) 單元格格式設定為「;;;」(自定義中)
3) 將單元格字型顏色設定為同背景顏色一致;
12. 插入:
1) 插入多個單元格:除常規方法外,可以選定目標單元格後,按住shift,滑鼠在右下角變成分割箭頭時,向下拖動,拖動距離即為插入單元格數量;
2) 插入行列:ctrl+shift+=;
3) 隔行插入:先插入乙個輔助列,在輔助列輸入1後自動填充,之後在輔助列空白行輸入1.5後自動填充相同數量,按輔助列公升序排列;最後刪除輔助列;
4) 非空白行插入空行:道理同上,計算過程加入乙個if函式鎖定非空白行即可;
13. 限定工作表的適用範圍:通過設定scrollarea屬性,右鍵工作表標籤,在快捷選單中選擇「檢視**」,進入vba編輯器後在屬性視窗scrollarea中輸入指定區域(屬性視窗的visible屬性可以隱藏和取消隱藏工作表)
14. 篩選後複製:1)多次篩選後複製;2)查詢-定位條件-可見單元格;3)alt+分號鍵;
二、 資料處理和分析
1. 多視窗協同作業:
1) 檢視—拆分視窗;
2) 檢視—新建視窗;
2. 自動填充:excel選項—常用—編輯自定義列表,可以設定自己想要的序列;
3. 輸入分數:整數-空格-分子-斜槓-分母;
4. excel語音校驗:工具-語音-文字到語音工具欄(似乎只有英文,讀數字可以)
5. 超連結:
1) 取消超連結:ctrl+z;用空白單元格選擇性貼上;
2) 提取超連結位址:需用vba編輯器(p102)
6. 特殊字元:對號-按住alt鍵,輸入41420;錯號-按住alt鍵,輸入41409;平方-按住alt鍵,輸入178;立方-按住alt鍵,輸入179;
7. 自動更正:選項-校對-自動更正選項。很大程度上可以實現自動替換;
8. 換行符:alt+10
9. 選擇性貼上:運算&&轉置(x/y軸轉換)
10. 攝影功能:貼上--貼上為**鏈結;
11. 自定義單元格式:零值不顯示;放縮數值;顯示百分比;顯示分數;
12. 斜線表頭:單表頭(文字框、上下標、中分);多表頭(文字框)
13. 名稱:「有名字的公式」(替代、輸入和計算)
1) 動態名稱:=offset(sheet1!$c$4,,,counta(sheet1!$c:$c)-1)
2) 貼上名稱列表與快速刪除所有名稱(vba)
14. 條件格式:
1) 西洋棋棋盤式:=mod(row()+column(),2)
2) 奇偶行:=mod(row(),2)
3) 重複值:=countif(a1:a$2,a1)>1
4) 生日提醒:=abs(date(year(today()),month($b2),day(#b2))-today())<7
5) 匹配目標值:
a) =match(b3,$b$1:$h$1,0)
b) =countif($b$1:$h$2,b3)
c) =hlookup(b3,$b$1:$h$1,1,0)
15. 使用條件格式比較不同區域的數值:
1) =or(exact(a2,b$2:b$11))=false
2) =isna(match(a2,b$2:b$11,))
3) =isna(vlookup(a2,b$2:b$11,1,))
4) =not(or(a2=b$2:b$11))
16. 資料有效性:設定下拉列表和輸入資訊,出錯警告等;
1) 切換資料有效性的**:=offset(list.1,,a1-1);
2) 某種特徵:and函式限制;
3) 限制重複值:=countif($a$2:$b$11,a2)=1;
4) 負數庫存:=b2<=vlookup(a2,$a$1;$b$11,2,0)
5) 序時輸入:=n(a2)>=n(a1)或=max($a$2:$a2)
17. 在同一頁上列印不連續的區域:參見攝影功能;
18. 同時頁面設定:ctrl選定工作表;
19. 排序和篩選:
1) 自定義序列、筆劃序列、按行、按字元、按顏色、隨機
2) 排序字母和數字組合:
=left(a1,1)&right(「000」&right(a1,len(a1)-1),3)
3) 返回排序前的**:輔助列
20. 分類彙總和資料透視:
1) 複製分類彙總結果:定位條件-可見單元格-ctrl+c;
2) 多欄位分類彙總:先排序,再按順序分類彙總;
3) 多層次的資料顯示:資料-組和分級顯示-自動建立/組合;
4) 組合資料透視表內的日期項:組合及顯示明細資料;
5) 在資料透視表中加入計算項:資料透視表選項-公式-計算項;
三、 函式
1. f9鍵:公式中部分運算的計算結果;
2. 數值運算中t=1,f=0;邏輯判斷中,0=f,非0=t;
(一) 資訊處理
1. 遮蔽公式返回的錯誤值:=if(iserror(原公式),「」,原公式);
2. cell函式:=cell(info_type,reference),例如format,filename和contents,分別返回格式,檔名和內容;
(二) 文字處理
1. 英文本母大小寫轉換:=upper/lower/proper;
2. 字元和ansi字元**轉換:=code/char;
3. 半形全形轉換:=asc/widechar;
4. 填充字元:=right(rept(0,10)&a1,10)將a列數字轉換為10為**;
5. 清除字元:=clean/trim 垃圾字元/空格;
6. 替換字元:=substitute/replace;
7. 查詢字元:=search/find;支援萬用字元/支援大小寫;
8. 計算字元出現次數:單元格內:=len(a1)-len(subtitute(a1,」好」,」」);單元格外:=countif;
9. 提取字串:=left/right/mid;
10. 格式化數值:=text
(三) 日期和時間計算
1. =datedif:」y」」m」」d」」md」」ym」」yd」;
2. 月末最後一天:=date(year(now()),month(now())+1,0);
3. 上月末最後一天:=today()-day(now());
4. 計算星期幾:=weekday(a2,2);=mod(a2-2,7)+1;
(四) 數學與三角運算
1. =round/roundup/rounddown;
2. 小數值捨入:=ceiling/floor;向上捨入、向下捨入
3. 數值取整:=int/trunc;
4. 判斷奇偶數:=isodd/iseven;
(五) 統計求和
1. 資料上下限:=min(100,max(80,a3))
2. =count/countif:條件函式;
3. =sumif(a:a,g2,d:d)根據唯一關鍵字返回數值;
4. =subtotal 1-9;
5. =trimmean(b2:j2,2/counta(b2:j2)) 去掉極值後求平均值;
6. =frequency:對資料進行分段統計;去除重複語法;
(六) 查詢和引用
1. (七) 圖表通用技巧:
1. 去除分類軸上的空白日期:圖表選項-座標軸-分類;
2. 折線圖中的空值:工具—選項—圖表—空單元格的顯示:1)空值不顯示;2)零值顯示;3)內插值替換;
3. 圖表自動適應視窗:圖表—位置—作為新工作表插入;
4. 隱藏接近於0的資料標籤:格式—資料標誌—數字—分類—自定義—型別:[<0.01]」」; 0%
5. 工作表透明:圖表區格式—邊框無,區域顏色無;
8. 顯示較小資料:座標軸格式—對數刻度;復合餅圖;
Excel常用技巧
一 規範高效的工作方式 1.複製工作表,右鍵單擊工作表標籤,移動或複製選單 2.插入或刪除多行 先插入或刪除一行,然後重複按f4鍵 f4鍵功能是重複上一次操作 定位您要插入的位置,向下選擇您要插入的行數,然後選擇插入行命令,或使用f4鍵 3.查詢工作表 右鍵單擊excel視窗左下角工作表表查詢前頭,...
及EXCEL常用使用技巧選萃
word技巧 一 文字錄入及排版 1 word中取消文字錄入過程中的自動編號 可執行選單 工具 自動更正 命令,在開啟的 自動更正 對話方塊中,切換到 鍵入時自動套用格式 選項卡,然後取消選擇 自動編號列表 核取方塊中的複選標誌,然後單擊 確定 按鈕完成即可。2 在word中設定上下標註 首先選中需...
EXCEL常用函式使用技巧及其應用
excel中幾個常用函式 在統計工作中的應用 1.目的 了解函式的基本語法和在統計工作中的應用,提高統計工作的電子化程度 提高工作效率 提高工作質量 2.函式的種類 2.1查詢與引用函式vlookup 表示式 sheet1的單元格c1 vlookup a1,sheet2a sheet2c,3,fal...