網頁

2019年1月6日 星期日

電腦軟體乙級術科檢定詳解-題組二-Access資料整合解題

使用Access2016(2013 版無法開啟dbf。如果先安裝Office 365,再安裝 Office 2016Access 2016也無法開啟 dbf),資料庫採用dataset2
查詢有4個:基本資料、89請假、89加班、89業績。
打開 Access/空白資料庫,按 「檔案名稱」右邊的資料夾圖示,瀏覽至您要放置的資料夾。
Access2016
Access2010
輸入檔名:01王小明02.accdb (檔名的意思: 1號王小明 題組二 )
按「建立」鈕。
------------------------------------------------------
註:若直接按「空白資料庫」,是在「文件」資料夾建立資料庫,可選功能表的檔案/另存新檔/將資料庫儲存為Access資料庫」。
輸入檔名,如01王小明02.accdb
------------------------------------------------------
如何匯入dbase資料檔?
要匯入那些資料檔呢? 請看術科題本,不用背。以下為題組二需要的資料檔:
外部資料/新資增料來源/從資料庫/dBASE檔案。
-----------------------------------------------------------------
Access 2010:外部資料/其他/dBASE檔案。
檔案名稱,按右邊的「瀏覽」鈕,再選DEPT.DBF,再按「開啟」鈕。
按「確定」,「關閉」鈕。
同樣方式,開啟  EMPLOYEE.DBFLEAVE.DBFOVERTIME.DBFPRODUCT.DBFSALES.DBF(看術科題本,就知道要匯入那些資料檔)
如何建立「基本資料」查詢

參考題本的資料,四個附件都要有部門名稱,如業務部、研發部、部門名稱。

附件一:部門名稱、員工姓名、職稱、月薪年假天數、未休天數、未休假獎金、備註。

附件二:部門名稱、員工姓名、職稱、月薪、加班時數、加班費、佔月薪月比例。
附件三:部門名稱、員工姓名、職稱、未休假獎金、加班費、年薪資、業績總額、比例。
附件四:部門名稱、月薪資(平均月薪資)、未休假獎金(平均未休假獎金)、加班費(平均加班費)。
四個附件使用的基本欄位有:部門名稱、員工姓名、職稱、月薪、年假天數
------------------------------------------------
說明:年薪資可由 月薪*16.5(題目規定,年終獎金以4.5個月薪資計算) 求出。
未休假獎金要由 未休天數 算出,未休天數 等於年假天數 減掉 已休天數,所以「年假天數」也是屬於基本欄位。
------------------------------------------------
建立「基本資料」查詢,含有上述基本欄位。
結論:直接使用「附件一」前面5個欄位名稱當「基本資料」查詢的5個欄位,後面的「未休天數、未休假獎金、備註」3個欄位,在Excel中處理。
建立/查詢設計
先選「DEPT」,按住 Shift鍵(連續選取),再選「EMPLOYEE。再按「新增」鈕,「關閉」鈕。
以「部門代號」建立二個資料表之間的關聯,方法如下:
按住左邊的「部門代號」,拖曳到右邊的「部門代號」再放下,產生關聯。
註:如果選錯欄位,請選關聯線(線會變粗),按右鍵,選「刪除」。
建立下面的查詢欄位:部門名稱、員工姓名、職稱、月薪、年假天數。(員工姓名就是找 姓名,其它依此類推)
說明:將上面的資料表往下拉大,以顯示所有的欄位名稱,這樣在下面要拉欄位名稱時,比較容易看出,在那個資料表有此欄位名稱。可將游標移到二欄之間,將欄寬拉大,以完整顯示欄位名稱。
按最左上方的 「磁碟」圖示,儲存,查詢名稱改為「基本資料」。
按「查詢工具/設計/ ! (執行)」鈕。
結果有97筆資料 (dataset3也是97筆資料)
游標移到查詢結果的最左上方儲存格,按一下,表示全部選取。按 Ctrl+C(複製)
Excel內,移到A1儲存格,「常用/貼上選項」選 「符合目的格式設定」。
更改「工作表1」名稱 為「基本資料」,再刪除第1列。
結果如下:
最左上方 ,快速存取工具列中「磁碟」的圖示,儲存檔案。(或是 檔案/儲存檔案),檔名:01王小明02.xlsx
根據 術科檢定題本 附件一到附件四,都是要求89年的資料。
附件一:建立89年請假資料的查詢
建立/查詢設計
選「LEAVE(請假主檔) 再按「新增」鈕,「關閉」鈕。
欄位:姓名、天數、年。
欄位「年」,準則輸入「89」,不顯示。
雖然 LEAVE」資料都是89年,沒有其它年的資料。但後面的業績,年要輸入89,題組三~題組五也是要求90年的資料,所以養成在「年」的欄位輸入篩選準則的習慣。但不用出現在報表中,所以不勾選顯示。
按最左上方的 「磁碟」圖示,儲存,查詢名稱改為「89請假」。
按「設計/ ! (執行)」鈕。
結果有83筆資料 (dataset391筆資料)
游標移到查詢結果的最左上方儲存格,按一下,表示全部選取。按 Ctrl+C(複製)
Excel內,按「新工作表」按鈕,以插入新工作表。
移到A1儲存格,直接按  Crtl + V(貼上)
說明:由於從Access複製過來的資料,祇是參考使用,不會再作任何變更,所以直接按 Crtl + V(貼上),祇有題組二的「89資料」是用此方法。其它題組,因為會變更其內容,所以使用「常用/貼上選項」選 「符合目的格式設定」此方法。
更改「工作表1」名稱 89資料」。
結果如下:
附件二:建立89年加班資料的查詢
建立/查詢設計
選「OVERTIME(加班主檔) 再按「新增」鈕,「關閉」鈕。
欄位:姓名、加班時數、年。
欄位「年」,輸入「89」,不顯示。
雖然 OVERTIME」資料都是89年,沒有其它年的資料。但後面的業績,年要輸入89,題組三~題組五也是要求90年的資料,所以養成在「年」的欄位輸入篩選準則的習慣。但不用出現在報表中,所以不勾選顯示。
按最左上方的 「磁碟」圖示,儲存,查詢名稱改為「89加班」。
按「設計/ ! (執行)」鈕。
結果有110筆資料 (dataset3也是110筆資料)
游標移到查詢結果的最左上方儲存格,按一下,表示全部選取。按 Ctrl+C(複製)
切換到 Excel,游標移到 89資料」工作表的D1,直接按  Crtl + V(貼上)
結果如下:
附件三:建立89年業績資料的查詢
題目要求:業績總額 = (該業務員所售出之所有產品數量) × (各產品之單價)之總和
因為 業績 = 數量*單價,數量在 SALES資料表,而單價在 PRODUCT資料表,必須先建立這二個資料表的關聯。
建立/查詢設計
先選「PRODUCT」,按住 Shift鍵(連續選取),再選「SALES。再按「新增」鈕,「關閉」鈕。
以「產品代號」建立二個資料表之間的關聯,方法如下:
按住左邊的「產品代號」,拖曳到右邊的「產品代號」再放下,產生關聯。
建立下面的查詢欄位:業務姓名、業績:數量*單價、交易年。
說明:將上面的資料表往下拉大,以顯示所有欄位名稱,這樣在下面要拉欄位名稱時,比較容易看出,在那個資料表有此欄位名稱。
業績此欄位自己輸入運算式,業績:數量*單價 (或者 業績:單價*數量),結果會變成 :業績:[數量]*[單價]

按最左上方的 「磁碟」圖示,儲存,查詢名稱改為「89業績」。
按「查詢工具/設計/ ! (執行)」鈕。
結果有66筆資料 (dataset3也是66筆資料)
游標移到查詢結果的最左上方儲存格,按一下,表示全部選取。按 Ctrl+C(複製)
切換到 Excel,游標移到 89資料」工作表的G1,直接按  Crtl + V(貼上)
結果如下:
Excel由「基本資料」工作表擴充為「整合」工作表。
附件一到附件三,除了最後一個欄位以外,其它欄位都放在一個工作表中,事先統計出來。會使用到 sumififround函數。

選「基本資料」工作表,按住Ctrl(複製的意思),向右拖曳到最後面,複製出另一工作表,將「基本資料(2)」重新命名為「整合」。
修改「整合」工作表中的欄位名稱,和附件一相同,其欄位名稱為 部門名稱、員工姓名、職稱、月薪。
「姓名」改為「員工姓名」,「現任職稱」改為「職稱」,「目前月薪資」改為「月薪」。
題目要求:
報表中的所有數值均應標示千分位符號,且靠右對齊。
本題組附件一到附件三,都要有千分位,且均為整數
游標移到工作表最左上方的儲存格,按一下以全選工作表。
按右鍵,選「儲存格格式」。
數值,類別選「數值」,勾選「使用千分位」 ,小數位數設為0。按「確定」。
註:請勿直接使用「常用/數值」功能區,「千分位」按鈕來設定千分位。
因為把結果複製到  Word 後,會有問題。以題組六附件一來說明:
使用「表格工具/版面配置/自動調整/自動調整內容」,再使用「自動調整成視窗」,還是無法讓版面正常,欄位名稱的寬度無法調好。
結論:軟乙檢定所有題組,若要設千分位,一律使用按右鍵,選擇「儲存格格式」方式來設定千分位。

全選工作表,游標移到二個欄位中間,連續按二下,以調整最適欄寬,讓資料全部顯現,沒有被遮住。以後要讓資料全部出現,都是一樣的用法。
將附件一到附件三所需的欄位新增到「整合」工作表中
附件一:部門名稱、員工姓名、職稱、月薪、年假天數、未休天數、未休假獎金、備註。
要統計「未休天數」,要另行新增「已休天數」。所以在整合工作表,新增3個欄位 : 已休天數、未休天數、未休假獎金。最後一欄的「備註」在後面解題時再求出。
附件二:部門名稱、員工姓名、職稱、月薪加班時數、加班費、佔月薪月比例。
所以在「整合」工作表,新增2個欄位 : 加班時數、加班費。最後一欄的「佔月薪月比例」在後面解題時再求出。
附件三:部門名稱、員工姓名、職稱、未休假獎金、加班費、年薪資、業績總額、比例。
所以在整合工作表,新增2個欄位 : 年薪資、業績總額。最後一欄的「比例」在後面解題時再求出。
已休天數的求法
F2儲存格輸入「=sumif()」,按插入函數鈕fx
游標移到Range輸入區內,選取「89資料」工作表中的A(姓名)
游標移到Criteria輸入區內,選取「整合」工作表中的B2(姓名)
游標移到Sum_range輸入區內,選取「89資料」工作表中的B(天數)
從「89資料」找姓名(A)為「方重圍」,找到以後,加總其請假天數(B),就得到 方重圍 89年請假的總天數。
加班時數的求法
I2儲存格輸入「=sumif()」,按插入函數鈕fx。方法跟上面類似。
結果:
業績總額的求法
L2儲存格輸入「=sumif()」,按插入函數鈕fx。方法跟上面類似。
結果:
Dataset2dataset31位員工姓名都是方重圍,其加班時數和業績總額都是0在董事長室當顧問真好,不用加班,也不用拚業績。
未休天數的求法
題目要求:未休假天數=(年假天數 - 已休假天數)。其中已休假天數為各種假別之總和,而未休假天數最少為0 天。
G2儲存格輸入函數「=if()」,按插入函數鈕fx
E2,在函數視窗會自動填入 E2,避免手動輸入錯誤。
結果:
未休假獎金的求法
題目要求:未休假獎金=(月薪 ÷ 28 × 未休假天數。金額以整數計算,小數四捨五入。未休假獎金為0 者,不須列印。
在H2儲存格輸入函數「=round()」,按插入函數鈕fx
結果:
加班費的求法
題目要求:加班費=(月薪 ÷ 224 × 1.5 × 全年總加班時數,金額以整數計算,小數四捨五入。無加班費者,不須列印資料。
J2儲存格輸入函數「=round()」,按插入函數鈕fx
結果:
年薪資的求法
題目要求:年終獎金以4.5 個月薪資計算,年薪資=(月薪 × 16.5),金額以整數計算,小數四捨五入。
K2儲存格輸入函數「=round()」,按插入函數鈕fx
結果:
選取 F2L2儲存格,填滿控點往下拉到最後一筆資料。
結果:
全選整張工作表,調整最適欄寬,讓資料不要隱藏(不要有 #### 訊息)
由於附件一的報表不管是使用那一個資料庫(dataset123),林鳳春都是排在員工姓名此欄位的第一位,所以先檢視這筆資料是否正確。
Dataset2的結果:
口訣: 林鳳春 未休假獎金 23179,愛想()一起久。
Dataset3的結果:
口訣:林鳳春 未休假獎金 10852,依玲怕我兒。
最左上方 ,快速存取工具列中「磁碟」的圖示,儲存檔案。

沒有留言 :

張貼留言