Excel在財務中的實用技巧.ppt

Excel在財務中的實用技巧 Excel在財務中的實用技巧 一 基本概述二 Excel的常用快捷鍵介紹三 Excel常用公式和函數(shù)的使用四 數(shù)據(jù)透視表的建立和應用 一 基本概述 1 1Excel的初始設(shè)置 Excel選項 精度的設(shè)置 快速訪問欄的設(shè)置保存 選擇性粘貼為數(shù)值 選取可見單元格 1 2 工作簿和工作表的運用 1 在屏幕上同時看到一個工作簿的多個工作表 比如sheet1和sheet2 只需打開該工作簿并且顯示其中一個工作表sheet1 然后 選擇 視圖 新建窗口 單擊新建窗口中的sheet2 然后選擇 窗口 重排窗口 選擇 水平平鋪 選項2 選取工作范圍在工作簿里 如果要選擇幾片不相連的區(qū)域或單元格 可以按住Ctrl鍵 再選擇單個或多個單元格即可選定所需的工作范圍 同樣適用于工作表間的選定 3 拆分和凍結(jié)工作表 1 3數(shù)據(jù)的快速填充及有限性 Ctrl Enter在多個單元格中輸入相同的數(shù)據(jù) 只需要在最后一個單元格輸入數(shù)據(jù) 復制后同時按下Ctrl Enter鍵即可 同樣也適用于某一區(qū)域內(nèi)的相同的公式填充 F4鍵重復上一次操作 例如 要將多個不連續(xù)的單元格改為填充綠色 可以選擇第一個單元格 設(shè)置填充綠色 然后依次選擇其余的單元格 每次按一下F4 1 3數(shù)據(jù)的快速填充及有限性 數(shù)據(jù)的有限性 限制數(shù)值的可輸入位數(shù)對于固定位數(shù)的數(shù)據(jù) 例如訂單號 身份證號 手機號等 可以設(shè)置該行的數(shù)據(jù)有限性 防止輸入的數(shù)字少一位或者多一位 選擇 數(shù)據(jù) 有效性 菜單 打開 數(shù)據(jù)有效性 對話框 在 設(shè)置 選項卡的有效條件 允許 處選擇 文本長度 在出現(xiàn)的 數(shù)據(jù) 下面的方框中輸入數(shù)字 再選擇 出錯警告 選項卡并設(shè)置一個 出錯警告 確定返回 這樣 但輸入的號碼位數(shù)不正確時 系統(tǒng)會給出提示 限定輸入值的范圍 圈出指定數(shù)據(jù)實例描述 想把本公司員工考勤中屬于全勤的員工在Excel表格中用紅圈突出顯示出來 以方便查閱核對 首先 選中表格中考勤天數(shù)所在的單元格 如C2 C62 選擇 數(shù)據(jù) 有效性 菜單 在彈出的 數(shù)據(jù)有效性 對話框中 在 設(shè)置 選項卡的有效條件 允許 處選擇 小數(shù) 在數(shù)據(jù)處選擇 介于 然后在下面分別輸入最小值 1 和最大值 21 5 假設(shè)當月全勤是22天 點擊確定 然后 單擊 圈釋無效數(shù)據(jù) 按鈕 這樣考勤天數(shù)績在21天以上的就被圈上了一個紅圈 起到了突出顯示的作用 二 Excel常用快捷鍵介紹 Shift F11新建空白的工作表 sheet1 SHIFT 空格鍵選定當前單元格所在的整行Ctrl N新建工作簿 book1 Ctrl 1設(shè)置單元格格式Ctrl 2加粗Alt enter一個單元格里輸入兩行數(shù)據(jù) 換行 Alt 分號 定位于可見單元格 在篩選的狀態(tài)下刪除數(shù)據(jù)很方便 Ctrl 減號 刪除某一行或某一列 先選擇后按快捷鍵Ctrl 移動到當前數(shù)據(jù)區(qū)域的邊緣Ctrl X剪切選定的單元格Ctrl Z撤銷上一次操作Ctrl A選定整張工作表 Ctrl PageDown移動到工作簿中的下一張工作表Ctrl PageUp移動到工作簿中的上一張工作表Ctrl shift L篩選 再用一次取消篩選其他常用快捷鍵 Windows D顯示桌面Windows E打開 計算機 Windows F在 計算機 里搜索ALT Tab切換不同窗口 三 Excel常用公式與函數(shù)的使用 1 公式的引用 F4鍵 1 相對引用 2 絕對引用 3 混合引用2 運算符 1 算術(shù)運算符 2 比較運算符 3 文本運算符 4 引用運算符 3 函數(shù)的使用 3 1數(shù)學與三角函數(shù)Sum number1 number2 求和Round number 小數(shù)位數(shù) 將參數(shù)引用的數(shù)舍入到指定的小數(shù)位數(shù)如 round 123 456 1 3 2統(tǒng)計函數(shù)Average number1 number2 求平均值Count value1 value2 返回參數(shù)中數(shù)字的個數(shù)3 3文本函數(shù)Concatenate text1 text2 返回將給出的幾個字符串合并的一個字符串Value text 將以文本形式輸入的數(shù)字轉(zhuǎn)換成數(shù)值 Len text 返回輸入項中的字符個數(shù) 文字或者數(shù)值的長度 3 5查找及引用函數(shù) Vlookup 查找所構(gòu)造的表格中存放的信息Vlookup lookup value table array col index num range lookup lookup value需要以它為索引的數(shù)值table array查找范圍col index num查找值所在列號range lookup查找時是精確匹配還是近似匹配 0 精確匹配 1 近似匹配 3 6財務中常用的公式函數(shù)運用 1 個稅計算公式ROUND MAX A1 3500 0 03 0 1 0 2 0 25 0 3 0 35 0 45 0 105 555 1005 2755 5505 13505 0 2 A1為應發(fā)工資扣除五險一金后的工資余額2 隱藏身份證信息 電話信息具體方式如下 第一步 先將身份證信息輸入到sheet1的單元格A1中 第二步 在sheet2中A1單元格輸入下面公式 隱藏中間四位 CONCATENATE LEFT A1 7 REPT 1 RIGHT A1 7 隱藏前四位 CONCATENATE REPT 1 RIGHT A1 14 隱藏后四位 CONCATENATE LEFT A1 14 REPT 1 第三步 將sheet1隱藏 選中此表點擊右鍵進行操作即可 第四 避免在數(shù)據(jù)清單中出現(xiàn)合并單元格 第五 避免在單元格的開始和末尾輸入空格 第六 盡量避免在一張工作表中建立多個數(shù)據(jù)清單 每張工作表最好僅使用一個數(shù)據(jù)清單 第七 工作表的數(shù)據(jù)清單應與其他數(shù)據(jù)之間至少留出一個空列和一個空行 以便于檢測和選定數(shù)據(jù)清單 在制作數(shù)據(jù)透視表之前 應該按照以上7點來檢查數(shù)據(jù)區(qū)域 如果不滿足上面的要求 需要先進行整理工作表數(shù)據(jù)從而使之規(guī)范