Tin học ứng dụng

b¶ng tiªu chuÈn t×m kiÕm VÝ dô: VÉn c¬ së d÷ liÖu trªn. Nh­ng b©y giê muèn ®Õm xem cã bao nhiªu hé trÎ cã sè liÖu møc thu nhËp (nÕu bá trèng hoÆc lµ ch÷ ë môc thu nhËp th× kh«ng ®­îc ®Õm) th× viÕt vµo mét « trèng nµo ®ã trªn trang tÝnh nh­ sau: = DCOUNT(THUNHAP, C1, B16:B17) SÏ ®­îc kÕt qu¶ lµ 4 3. §Õm sè b¶n ghi tho¶ m·n tiªu chuÈn – DCOUNTA Quy c¸ch lÖnh: DCOUNTA(Database, Field, Criteria) Trong ®ã: Database: lµ ®Þa chØ hoÆc tªn CSDL Field lµ ®Þa chØ « chøa tªn tr­êng x¸c ®Þnh cã (hay kh«ng) nghÜa cña b¶n ghi Criteria: Lµ ®Þa chØ hoÆc tªn B¶ng tiªu chuÈn 4. LÊy gi¸ trÞ cña b¶n ghi tho¶ m·n tiªu chuÈn – DGET Trong qu¶n trÞ c¬ së d÷ liÖu ng­êi ta th­êng ph¶i t×m l¹i vµ lÊy ra gi¸ trÞ cña c¸c tr­êng cña mét b¶n ghi tho¶ m·n ®iÒu kiÖn nµo ®ã. Ch¼ng h¹n cho biÕt sè phiÕu ®iÒu tra h·y cho biÕt hä vµ tªn chñ hé, hoÆc møc thu nhËp, hoÆc nghÒ nghiÖp §Ó thùc hiÖn nh÷ng yªu cÇu nh­ vËy cã thÓ sö dông hµm DGET cña CSDL theo quy c¸ch: =DGET(Database, Field, Criteria) Trong ®ã: Database lµ ®Þa chØ hoÆc tªn CSDL Field lµ ®Þa chØ « chøa tªn tr­êng chØ ®Þnh cña tr­êng b¶n ghi sÏ lÊy gi¸ trÞ Criteria lµ ®Þa chØ hoÆc tªn b¶ng tiªu chuÈn ViÕt =DGET(THUNHAP, B1, C16:C17) vµo mét « nµo ®ã th× ®­îc nghÒ nghiÖp lµ Kinh doanh Chó ý: LÖnh DGET th­êng chØ dïng cho viÖc t×m kiÕm duy nhÊt, nghÜa lµ cã vµ chØ cã duy nhÊt mét b¶n ghi tho¶ m·n mét tiªu chuÈn t×m kiÕm. NÕu cã nhiªu h¬n mét b¶n ghi th× DGET sÏ ®­a ra th«ng b¸o lçi #NUM! vµ nÕu kh«ng cã b¶n ghi nµo tho¶ m·n th× m¸y sÏ th«ng b¸o #VALUE! VÝ dô: DGET (thunhap, C1, B16:B17) sÏ b»ng #NUM! v× cã nhiÒu chñ hé trÎ. Tøc lµ nhiÒu h¬n mét b¶n ghi tho¶ m·n tiªu chuÈn. ViÕt 200000 vµo « C17 th× th«ng b¸o cña lÖnh trªn lµ #VALUE! V× kh«ng cã møc thu nhËp nµo b»ng 200000 5. Hµm tÝnh gi¸ trÞ lín nhÊt cña mét tr­êng CSDL Hµm nµy gióp ng­êi sö dông t×m ®­îc gi¸ trÞ lín nhÊt trong mét cét (tr­êng) cña c¸c b¶n ghi tho¶ m·n tiªu chuÈn t×m kiÕm Quy c¸ch viÕt: =DMAX(Database,Field, Criteria) Trong ®ã: Database lµ ®Þa chØ hoÆc tªn c¬ së d÷ liÖu Field lµ ®Þa chØ « chøa tªn tr­êng t×m gi¸ trÞ max Criteria lµ ®Þa chØ hoÆc tªn b¶ng tiªu chuÈn VÝ dô: Muèn t×m møc thu nhËp lín nhÊt trong c¸c hé gia ®×nh trÎ ta viÕt vµo mét « trèng hµm =DMAX(Thunhap, C1, B16:B17) kÕt qu¶ lµ 1200000. 6. Hµm t×m gi¸ trÞ nhá nhÊt cña mét tr­êng CSDL Hµm nµy trî gióp t×m gi¸ trÞ nhá nhÊt cña mét tr­êng cña b¶n ghi tho¶ m·n tiªu chuÈn t×m kiÕm. C¸c ®iÒu kiÖn vµ c¸ch thøc t×m gièng nh­ hµm DMAX tr×nh bµy trªn. Quy c¸ch: =DMIN(Database, Field, Criteria) VÝ dô: DMIN(thunhap, C1, B16:B17) sÏ cho møc thu nhËp thÊp nhÊt cña c¸c hé gia ®×nh trÎ b»ng 500000 7 Hµm tÝnh ®é lÖnh chuÈn trªn mét tr­êng – DSTDEV Khi muèn tÝnh ®é lÖch chuÈn cña mét d·y gi¸ trÞ cã thÓ dïng hµm thèng kª tÝnh ®é lÖch chuÈn b×nh th­êng dïng STDEV. NÕu nh÷ng gi¸ trÞ ®ã n»m r¶i r¸c trªn c¸c dßng quan s¸t mµ nh÷ng dßng ®ã tháa m·n mét tiªu chuÈn t×m kiÕm nµo ®ã th× nªn dïng hµm tÝnh ®é lÖch chuÈn dùa trªn mét mÉu dïng c¸ch dïng c¸c sè trong cét tr­êng cña c¸c b¶n ghi trong mét c¬ së d÷ liÖu tháa m·n tiªu chuÈn t×m kiÕm theo quy c¸ch: =DTSDEV (Database, Field, Criteria) Trong ®ã: Database lµ ®Þa chØ hoÆc tªn CSDL Field lµ ®Þa chØ « chøa tªn tr­êng quan träng nªu trªn Criteria lµ ®Þa chØ hoÆc tªn B¶ng tiªu chuÈn Chó ý: Khi muèn tÝnh ®é lÖch chuÈn dùa trªn tæng thÓ th× sö dông hµm DSTDEVP (Database, Field. Criteria) 8. Hµm tÝnh tæng cét tr­êng cã lùa chän SUM Hµm nµy gióp ng­êi sö dông tÝnh tæng gi¸ trÞ cña mét cét tr­êng thuéc c¸c b¶n ghi tháa m·n tiªu chuÈn t×m kiÕm. Cßn gäi lµ c«ng cã lùa chän Quy c¸ch viÕt: = DSUM(Database, Field, Criteria) Database lµ ®Þa chØ hoÆc tªn CSDL Field lµ chØ ®Þnh tªn tr­êng cÇn tÝnh tæng Criteria lµ ®Þa chØ hoÆc tªn B¶ng tiªu chuÈn 9. Hµm tÝnh ph­¬ng sai mÉu c¬ së d÷ liÖu – DVAR §Ó tÝnh ph­¬ng sai trªn mét mÉu (Sample) b»ng c¸ch dïng c¸c sè trong mét cét tr­êng cña c¸c b¶n ghi tháa m·n mét tiªu chuÈn t×m kiÕm th× dïng hµm nµy theo Quy t¾c viÕt: = DVAR(Database, Field, Criteria) Trong ®ã: Database lµ ®Þa chØ hoÆc tªn c¬ së d÷ liÖu Field lµ chØ ®Þnh tªn tr­êng cÇn tÝnh ph­¬ng sai Criteria lµ ®Þa chØ hoÆc tªn B¶ng tiªu chuÈn 10. Sö dông hµm tÝnh tæng con Subtotal §Ó t¹o c¸c dßng tæng con trong mét c¬ së d÷ liÖu hay trong mét danh s¸ch ng­êi ta chñ yÕu dïng lÖnh SUBTOTALS nh­ ®· tr×nh bµy ë môc trªn. Tuy nhiªn ®Ó hiÖu chØnh c¸c dßng tæng con th× ng­êi ta l¹i hay dïng hµm SUBTOTAL. Hµm nµy cã quy c¸ch: =SUBTOTAL(Function_num, Ref) Trong ®ã: Function_num lµ sè hiÖu cña hµm dïng tÝnh c¸c dßng tæng kÕt con. Ref lµ tham chiÕu tíi vïng muèn tÝnh tæng con L­u ý: NÕu trong vïng tham chiÕu cña mét hµm Subtotal l¹i chøa mét Subtotal kh¸c (lµ hiÖn t­îng c¸c Subtotal læng nhau) th× Excel bá qua c¸c dßng Subtotal bªn trong ®Ó khái tÝnh to¸n hai lÇn cïng mét gi¸ trÞ. §èi víi viÖc läc danh s¸ch th× hµm Subtotal bá qua nh÷ng dßng d÷ liÖu bÞ che dÊu Chó ý: Chóng ta cã thÓ viÕt trùc tiÕp tªn hµm vµo « cña B¶ng tÝnh hoÆc chän tªn hµm tõ nhãm hµm Database. III. LËp b¶ng c©n ®èi tæng hîp tõ c¬ së d÷ liÖu Trong tÝnh to¸n kinh tÕ hoÆc qu¶n trÞ kinh doanh ng­êi ta cÇn ph¶i lËp nh÷ng b¶ng tæng hîp thÓ hiÖn sù quan hÖ t­¬ng t¸c gi÷a c¸c tr­êng víi nhau. Trªn c¸c b¶ng nh­ vËy ®ßi hái ph¶i cã sù c©n ®èi vÒ nh÷ng d÷ liÖu tæng hîp khi ta muèn xem xÐt theo mÆt nµy hay mÆt kh¸c. VÝ dô cho sè liÖu b¸n hµng cña mét c«ng ty th­¬ng m¹i nh­ sau: Ten hang So luong Don gia Thanh tien Ngay ban Nguoi ban Ti vi 8 3200000 25600000 15/5/99 Le Ha Tu lanh 8 12000000 96000000 15/5/99 Pham Anh Radio 7 500000 3500000 15/5/99 Le Ha Dieu hoa 5 1500000 7500000 16/5/99 Phan Hoang Hut bui 5 8000000 40000000 16/5/99 Le Duy Radio 4 500000 2000000 16/5/99 Tran Cuong Dieu hoa 3 1500000 4500000 16/5/99 Nguyen Nam Tu lanh 3 12000000 36000000 16/5/99 Le cung Ti vi 3 3100000 9300000 16/5/99 Kim Anh Tu lanh 2 12000000 24000000 17/5/99 Hoang Ha Radio 2 500000 1000000 17/5/99 Pham Linh Dieu hoa 2 1500000 3000000 17/5/99 Huy Tu lanh 1 1200000 1200000 17/5/99 Mai Han Ti vi 8 3200000 25600000 17/5/99 Le Thuy Tõ b¶ng b¸o c¸o trªn ng­êi ta muèn cã b¸o c¸o tæng hîp ®èi víi tong ng­êi b¸n hµng ®Ó biÕt mçi ngµy , mçi mÆt hµng b¸n ®­îc bao nhiªu tiÒn vµ tæng céng tiÒn b¸n hµng ®­îc theo ngµy, theo mÆt hµng theo mÉu sau: Nguoi ban Le Ha Tong hop thanh tien Ten hang Ngay ban Hut bui Radio Tu lanh Tong cong 15/5/99 0 3500000 0 3500000 17/5/99 3200000 3000000 6000000 107000000 Tong cong 3200000 6500000 6000000 110500000 B¶ng c©n ®èi tæng hîp nh­ vËy cßn ®­îc gäi lµ b¶ng tæng hîp t­¬ng t¸c (Pivot Table). ViÖc t¹o ra nh÷ng b¶ng tæng hîp t­¬ng t¸c nh­ vËy tõ mét c¬ së d÷ liÖu lµ rÊt dÔ dµng b»ng c¸ch sö dông lÖnh Pivot table.. cña Excel. 1. C¸c thµnh tè trªn mét b¶ng c©n ®èi tæng hîp Trªn mét b¶ng c©n ®èi tæng hîp cã nh÷ng yÕu tè c¬ b¶n sau: Tr­êng ph©n trang b¸o c¸o (page fields) lµ tªn tr­êng cña c¬ së d÷ liÖu ®­îc chän ®Ó øng víi mçi gi¸ trÞ cña nã sÏ cã mét trang b¸o c¸o c©n ®èi tæng hîp cña nh­ng tr­êng kh¸c. VÝ dô: NÕu chän ng­êi b¸n lµm tr­êng ph©n trang th× cø mçi tªn ng­êi b¸n cã mÆt trong tr­êng “Ng­êi b¸n” cña c¬ së d÷ liÖu sÏ ®­îc lËp mét b¶ng b¸o c¸o vµ tªn ng­êi b¸n ®ã sÏ ®­îc ghi ë gãc tr¸i trªn cña trang b¸o c¸o. Tr­êng dßng b¸o c¸o (Pivot Table Rows) lµ tªn tr­êng ®­îc chän mµ øng víi gi¸ trÞ cña nã sÏ cã mét dßng trªn trang b¸o c¸o vµ gi¸ trÞ cña nã sÏ ®­îc viÕt vµo « ®Çu cña dßng b¸o c¸o. VÝ dô: NÕu chän “ Ngµy b¸n” lµm tr­êng dßng b¸o c¸o th× c¸c ngµy b¸n kh¸c nhau cña tr­êng “ Ngµy b¸n” cña c¬ së d÷ liÖu sÏ cã mÆt trªn c¸c dßng b¸o c¸o, mçi gi¸ trÞ ngµy th¸ng chØ xuÊt hiÖn mét lÇn Tr­êng cét b¸o c¸o (Pivot Table Column) lµ tªn tr­êng ®­îc dïng ®Ó x¸c ®Þnh c¸c cét trªn b¸o c¸o VÝ dô: NÕu chän tr­êng tªn hµng lµm tr­êng cét b¸o c¸o th× cã bao nhiªu tªn hµng cã mÆt trong cét tr­êng tªn hµng cña cét c¬ së d÷ liÖu sÏ cã bÊy nhiªu cét víi tªn hµng ë ®Çu cét trong b¸o c¸o. Néi dung « b¸o c¸o (Items) lµ tªn tr­êng sè cÇn ®­îc tæng hîp vµo « b¸o c¸o VÝ dô: Chän “Thµnh tiÒn” lµm néi dung « b¸o c¸o th× trong « b¸o c¸o sÏ lµ tæng thµnh tiÒn theo ng­êi b¸n, theo ngµy b¸n vµ theo mÆt hµng. Tæng theo hµng vµ tæng theo cét cña b¸o c¸o (Grand total) lµ cét ®­îc tÝnh vµ ghi æ bªn ph¶i nhÊt vµ hµng cuèi cïng cña mçi b¸o c¸o. 2. C¸ch sö dông lÖnh lËp b¸o c¸o Pivot Table ViÖc sö dông lÖnh Pivot table ®Ó lËp b¸o c¸o c©n ®èi tæng hîp sÏ ®­îc thùc hiÖn theo quy tr×nh sau: B­íc 1: KÝch ho¹t môc Data trªn thùc ®¬n chÝnh. KÝch ho¹t Pivot table Excel ®­a ra hép tho¹i: Chän Microsoft Excel List ë Database råi sang b­íc 2 B­íc 2: NhÊn nót next -> ta cã hép tho¹i Khai b¸o ®Þa chØ vïng c¬ së d÷ liÖu vµo hép s¸ng Range råi sang b­íc 3 B­íc 3: NhÊn nót Next M¸y hiÖn hép tho¹i khai b¸o c¸c yÕu tè chÝnh cÊu thµnh lªn b¶ng c©n ®èi tæng hîp nh­ sau: H·y chän tªn tr­êng trang b¸o c¸o tõ danh s¸ch c¸c tªn tr­êng cña c¬ së d÷ liÖu vµ dïng chute kÐo nã ®Æt vµo « s¸ng Page. H·y chän tªn tr­êng dßng b¸o c¸o råi dïng chuét kÐo nã råi ®Æt vµo « s¸ng Row vµ chän tªn tr­êng cét b¸o c¸o råi dïng chuét kÐo nã vµo « s¸ng Column H·y chän néi dung « b¸o c¸o råi dïng chuét kÐo nã ®Æt vµo hép s¸ng Data sau ®ã sang b­íc 4. B­íc 4: NhÊn Next Ghi ®Þa chØ « ®Çu tiªn ®Ó ghi kÕt qu¶ lËp b¸o c¸o vµo hép s¸ng Pivot table Starting cell. NÕu kh«ng ghi Excel sÏ tù ®éng ®­a ra b¸o c¸o trªn mét trang chÝnh (Sheet míi) §¸nh dÊu vµo c¸c « t­¬ng øng ®Ó x¸c ®Þnh cã tÝnh tæng theo cét, tæng theo hµng, l­u tr÷ c¬ së d÷ liÖu cïng víi c¸c b¶ng c©n ®èi tæng hîp VÝ dô: Víi dè liÖu b¸n hµng trªn ta chän: Tr­êng trang b¸o c¸o lµ “Ng­êi b¸n” Tr­êng dßng b¸o c¸o lµ “Ngµy b¸n” Tr­êng cét b¸o c¸o lµ “Thµnh tiÒn” Cã chän c¸c dßng tæng hµng vµ tæng cét Ta sÏ ®­îc tËp b¸o c¸o c©n ®èi nh­ sau: (h×nh) Chó ý: Khi xem b¸o c¸o nµy (còng t­¬ng tù cho c¸c b¸o c¸o c©n ®èi kh¸c), ng­êi sö dông cã thÓ nhÊn vµo nót hiÖn danh s¸ch bªn c¹nh tªn ng­êi b¸n ®Ó hiÖn ra nh÷ng tªn ng­êi b¸n kh¸c vµ chän xem b¸o c¸o øng víi tªn ng­êi b¸n míi.