Excel快捷键与公式.ppt

1、Excel 使用技巧與高級用法,Education PowerPoint-Excel,Learning and progress,第一講,目錄:,第一部分：,1. 快捷鍵,I am here!,目錄,第二部分：,12. 公式應用,第三部分：,19. 高級應用, 高級篩選, 數據透視表, 規划求解/單變量求解, 表單,第四部分：, 宏與VBA(另做教程), Excel与Access結合管理數据,第一部分:快捷鍵,Learning Key!,1,有些是所有Windows軟件通用快捷鍵，如：Ctrl+N;Ctrl+O; Ctrl+S;Ctrl+P;Ctrl+Z;Ctrl+X;Ctrl+C;Ctrl+

2、V;Ctrl+F;Ctrl+H,提示,回目錄,第一部分:快捷鍵,It is so easy!,2,-不帶輔助鍵,回目錄,第一部分:快捷鍵,It is useful!,3,-帶輔助鍵Ctrl,回目錄,第一部分:快捷鍵,Function key!,4,-帶輔助鍵Shift,回目錄,第一部分:快捷鍵,It is very insteresting!,5,回目錄,第一部分:快捷鍵,Just for a while!,6,回目錄,第一部分:快捷鍵,You will be expert!,7,本單元講解所有用于在Excel中完成單元格定位的技巧,結合使用Shift可以輕松選中大范圍數据區域,回目錄,第一部

3、分:快捷鍵,8,Do you like it!,回目錄,第一部分:快捷鍵,9,舉例說明,回目錄,Do it like this!,開啟,第一部分:公式,10,獲取未打開工作簿中數据的方式,(單引號),+,未打開工作簿路徑,+,+,+,表名,+,+,(單引號),+,!,+,單元格定位,F:susanTradeCard-3-4.xlsBody!A1,例如,Get information out of book?!,回目錄,第一部分:公式,11,VLookUp(參數1參數2參數3參數4),參數1(Lookup_value)查詢的依据即為需要查找的內容一般不會是公式 返回值,查詢的依据查詢表中的查詢范

4、圍查詢公式最終的返回,參數2(Table_Array) 查詢表中的查詢范圍必須是連續的選擇范圍,參數3(Col_Num) 查詢結果位于查詢范圍中的列號,參數4(Boolean) 是否精确查詢False精确查找True若沒有找到取 小于Lookup_value的最大值,My function!,回目錄,第一部分:公式,12,Find(參數1參數2參數3),參數1(find_text) 需要查找的字符串,參數2(Within_text) 在該參數字符中查找參數1,參數3(Start_Num) 設置在參數2中開始查找參數1的起始位置,返回結果找到后返回參數1在參數2(從參數3開始)首次出現的位置,F

5、ind(“abcD”,”RstfabcDefg”,2),例如,返回為5,大小寫視同相异,若要使用不區分大小寫的查找使用Search, 參數一致,You will be feel free in this word!,回目錄,第一部分:公式,13,IF(參數1參數2參數3),參數1(Criteria) 判斷條件,參數2(True_String) 條件成立時公式返回該結果,參數3(False_String) 條件不成立時公式返回該結果,IF(1=2,”True”,”False”),例如,返回為False,參數1(Criteria),判斷,Y,N,參數2,參數3,Very good!,回目錄,第一部

6、分:公式,14,Mid(參數1參數2參數3),從參數1的參數2指定的位置開始取參數3個字符返回,Replace(參數1參數2參數3參數4),Substitute(參數1參數2參數3參數4),在參數1中找到參數2第參數4次出現的位置并將其替換為參數3,Len(參數1),參數1的字符長度,Come on!,回目錄,第一部分:公式,15,Concatenate(參數1參數2參數3,.),從參數列中所有參數合并為一個,Text(參數1參數2),將參數1中數据按照參數2中格式顯示,參數2需要用雙引號括起來以表示為字符串其使用方法如格式設置 中自定義欄位,“00000”,例如,返回數据將有5位如果不超過5

7、位其他位用0填充,“YYYY-MMM-DD”,返回日期以年月日順序(1998-Mar-05),You will be winner!,回目錄,第一部分:公式,16,SubTotal(參數1參數2參數3,.),You will be winner!,參數一取值,它是Excel中唯一具有排除隱藏行 能力進行計算的公式 所謂排除隱藏行就是通過自動篩選 或高級篩選將其他記錄隱藏手動 的隱藏不能起作用,回目錄,第一部分:公式,17,通過Shift+F3可以調用公式輔助界面從中可以獲得具体的公式幫助。,友情提示,回目錄,Pay attention!,一個公式例子,Dsum(參考區域計算部分位于區域的列號條

8、件),第一部分:高級應用,18,調用順序如下,圖一,圖二,Is it good for you?,回目錄,第一部分:高級應用,19,表單用來顯示記錄和修改記錄,圖中顯示了選擇區域(未選擇區域時系統 自動确認整個數据區域必須保証區域有標 題),圈住的滾動條用來滾動到下一行,新增按鈕用來增加一行,點擊關閉按鈕后將會對所做動作保存在Excel 表中,作用便利于字段的查看与編輯操作,回目錄,I think I will be expert,第一部分:高級應用,20,調用順序,Advance!,回目錄,第一部分:高級應用,21,高級篩選常用于多于三個條件以上的 篩選以及獲取一列數据中不重复記錄,決定最終

9、篩選結果顯示位置,被篩選的區域可以點右邊的 按鈕后選擇區域,設置條件區域有OR條件与AND 條件設置之分,最終篩選結果去除重复記錄,Yes! I like this!,回目錄,第一部分:高級應用,22,條件的設置,AND條件,兩條件位于同行不同列時運算為AND 運算,OR條件,兩條件位于同列不同行時運算為OR 運算,在同一列的條件必須要有一個條件頭并且條件頭必須与篩選的Title一樣,Professional!,回目錄,第一部分:高級應用,23,舉例,選出英語分數在80与90間中文在60到100間記錄,選出英語等于80或小于60中文大于80記錄,1,2,回目錄,Exercise!,開啟,第一部

10、分:高級應用,24,調用順序,第一步,Wonderful tool,回目錄,第一部分:高級應用,25,I will be invincible!,回目錄,第一部分:高級應用,26,選擇被分析區域,Next! Next! Next!.,回目錄,第一部分:高級應用,27,基本屬性設置,進入版面設計,一般先設置基本屬性,I can use it free,回目錄,第一部分:高級應用,28,定義透視表引用名稱非表名,生成主要欄位總計 一般取消,Config by yourself,回目錄,第一部分:高級應用,29,列字段, 例如 INV.No將排在 第一列訂單 號碼將排在第 二列依此類 推而每一個 I

11、NV NO.下面 所有的訂單號 碼包裝編號 都被分配到該 INV NO下,分析表必須有計算字段,Write for a while here!,回目錄,第一部分:高級應用,30,修改列字段屬性,修改在最終分析表上該字段体現的名稱,My own pivot table,回目錄,第一部分:高級應用,31,資料欄即計算部分的布局是可以調整的,Performance,回目錄,第一部分:高級應用,32,調整方法,左鍵按住,往小計上方的欄方向移動,當看到,到藍色色色塊的對應區域在欄位置時松開鼠標,Thanks very much Microsoft,回目錄,第一部分:高級應用,33,回目錄,Perfect!,第一部分:高級應用,34,規划求解,當目標受一個或多個變量影響(影響方式可以是線性或非線性的) 時要讓目標達到最大或最小或某特定值這一系列變量應該的 取值組成的解,單變量求解,目標受到一個因素的影響要讓目標達到預定值該因素應該 的取值,Lets learn something else!,回目錄,第一部分:高級應用,35,單變量求解,En! Oh!,回目錄,第一部分:高級應用,36,單變量求解,被影響的單元格,影像因素,被影像變量 預期的最終 值,The Micro is good!,回目錄,第一部分:高級應用,37,規划求解決,Usefu