แบ่งปันแนวทางการประยุกต์ใช้ Excel...

แบงปนการประยกตใช Excel ต�งแต ก-ฮ: สมเกยรต ชวยมาก

1

หลกการคานวณใน Microsoft Excel

การคานวณใน Excel

เปนท�ทราบกนดวาโปรแกรม Microsoft Excel มความสามารถทางดานการคานวณสง ซ�งสามารถนาไปประยกตใชงานกบทกสาขาอาชพไดเปนอยางด ดงน6นกอนท�จะคานวณคาตางๆ จงควรทราบหลกการ ท�ไปท�มาของการคานวณเสยกอนเพ�อท�จะสามารถใชงานโปรแกรมไดอยางถกตอง รวดเรว

การคานวณใน Excel สามารถทาได 2 รปแบบใหญๆ คอ 1. การคานวณโดยใชสตรคณตศาสตร (Formula) เชน เคร�องหมาย + - เชน

= 2+1 ผลลพธไดเทากบ 3 2. การคานวณโดยใชฟงกชนสาเรจรป (Function) เชน SUM, COUNT, AVERAGE

=SUM(2,1) ผลลพธไดเทากบ 3 ในการคานวณสามารถใช ฟงกชนรวมกบเคร�องหมายทางคณตศาสตรกได เชน =SUM(1,1)+1

ผลลพธท�ไดเทากบ 3

ตวดาเนนการทางดานคานวณ (Operator)

ตวดาเนนการทางดานคานวณนบวาเปนจดหลก ท�ควรทาความเขาใจใหถกตองกอนท�จะคานวณ เพราะถาเกดไมเขาใจตรงน6 แลว อาจจะทาใหการคานวณเกดความผดพลาดข6นได โดยไมรตว

ตวดาเนนการทางดานการคานวณสามารถแบงไดเปน 3 ประเภท ดวยกนคอ 1. ตวดาเนนการคณตศาสตร (Arithmetic Operator) 2. ตวดาเนนการเปรยบเทยบ (Comparison Operator) 3. ตวดาเนนการเช�อมขอความ (Concatenation Operator)

ตวดาเนนการคณตศาสตร (Arithmetic Operator)

ตวดาเนนการทางคณตศาสตร หรอ Arithmetic Operator เปนตวดาเนนการหลกท�ใชในการคานวณประกอบดวยเคร�องหมาย ( ) ^ % * / + -

ลาดบความสาคญของเคร2องหมายคานวณ

( ) เคร�องหมายวงเลบความสาคญลาดบ 1 ^ เคร�องหมายยกกาลงความสาคญลาดบ 2

% เคร�องหมายรอยละความสาคญลาดบ 3 * / เคร�องหมาย คณ หาร ความสาคญลาดบ 4 + - เคร�องหมาย บวก ลบ ความสาคญลาดบ 5


2

ตวอยางเชน =(1+2)*5 โปรแกรมจะคานวณผลบวก 2 กบ 1 ภายในวงเลบกอน หลงจากน6นนาผลลพธท�ไดจากการบวกไปคณกบ 5 เพราะฉะน6นคาตอบท�ไดคอ 15

ตวดาเนนการเปรยบเทยบ (Comparison Operator)

ตวดาเนนการเปรยบเทยบ หรอ Comparison Operator เปนตวดาเนนการท�ใหผลลพธออกมาเปนคา ตรรกะ (Logical) ซ� งผลลพธท�เกดจากการเปรยบเทยบเปนไปได 2 คาดวยกนคอ True (จรง) กบ False (เทจ) ผลลพธท6ง 2 สามารถนาไปคานวณได

ตวดาเนนการเปรยบเทยบท�ใชสาหรบการคานวณมดงตอไปน6

• = แทนเคร�องหมายเทากบ

• > แทนเคร�องหมายมากกวา

• < แทนเคร�องหมายนอยกวา

• = แทนเคร�องหมายมากกวาเทากบ

• < = แทนเคร�องหมายนอยกวาเทากบ

• < > แทนเคร�องหมายไมเทากบ เชน = 1>2 ผลลพธท�ไดคอ False เพราะ 1 ตองนอยกวา 2 = 2>1 ผลลพธท�ไดคอ True เพราะ 2 มากกวา 1

ตวดาเนนการเช2อมขอความ (Concatenate Operator)

ในโปรแกรม Microsoft Excel มตวดาการอกชนดหน�งท�สามารถเช�อมขอความเขาดวยกนได ตวดาเนนการท�วาน6 คอ เคร�องหมาย & (Ampersand) ซ� งการใชงานน6นหากตองการนาขอความใดมาเช�อมตอกนกใหค�นดวยเคร�อง & ดงตวอยางเชน

= Somkiat & Chuaymak ผลลพธท�ไดคอ SomkiatChuaymak =Somkiat & “ “ & Chuaymak ผลลพธท�ไดคอ Somkiat Chuaymak = “(“ & Somkiat & “ “ & Chuaymak &”)” ผลลพธท�ไดคอ (Somkiat Chuaymak)

วธการคานวณหาผลลพธ

1. คานวณผลลพธแคเซลลเดยว แลวคดลอกสตรไปยงเซลลผลลพธอ�นๆ โดยการคดลอกสตรสามารถทาได 2 วธคอ

• เล�อนเมาสไปยงตาแหนงดานลางขวาของเซลลตนฉบบท�เกดจากการคานวณจนปรากฏเคร�องหมายบวก (Fill Handle) ใหคลกเมาสซายคางไวแลวลากเพ�อคดลอกสตรไปยงเซลลผลลพธอ�นๆ


3

• เล�อนเมาสไปดานลางขวาจนปรากฏเคร�องหมายบวก (Fill Handle) ใหดบเบลคลกเมาสซาย สตรจะถกคดลอกไปยงเซลลผลลพธอ�นๆ โดยอตโนมต

2. คานวณโดยเลอกเซลลผลลพธท6งหมดกอน แลวปอนสตร เม�อตองการผลลพธใหกดปม <Ctrl> <Enter>

3. การคานวณแบบ Array (ชดขอมล) โดยผลลพธน6นเกดจากการกดปม <Ctrl> <Shift> <Enter> หลกการการคานวณแบบ Array

• ขอมลท�นามาคานวณตองมากกวา 1 ชวง (Array)

• ขอมลแตละชวงตองมจานวนเซลลเทากน

• กอนคานวณใหเลอกเซลลผลลพธท6งหมดกอน

• เม�อตองการผลลพธใหกดปม <Ctrl> <Shift> <Enter>

• ผลลพธจากการคานวณจะมเคร�องหมายปกกาครอมดงรป

• ไมสามารถลบเซลลผลลพธเซลลใดเซลลหน�งได หากตองการลบตองลบทเดยวท6งชวง Array


4

การอางองตาแหนงเซลล

หลกการคานวณใน Microsoft Excel จะคานวณผลลพธแคเซลลเดยว หลงจากน6นจงคดลอกสตรไปยงเซลลผลลพธอ�นๆ ทานเคยสงเกตหรอไมวาในการคดลอกสตรน6นตาแหนงเซลล เปล�ยนหรอไมเปล�ยนอยางไร เพราะฉะน6นในหวขอน6 ผเขยนขอกลาวถงรายละเอยดเก�ยวกบการอางองเซลลในการคานวณท�ควรทราบดงตอไปน6

การอางองแบบสมพนธ (Relative References)

การอางองแบบสมพนธเปนการอางองท�ใชบอยท�สดในการคานวณ โดยเม�อคดลอกสตรไปยงเซลลอ�นๆ ตาแหนงเซลลในสตรจะไหลตามเซลลท�คดลอกไปในลกษณะท�เรยกวา “สมพนธ” เชน ตองการคานวณท�เซลล C1 โดยพมพสตร =A1+B1 เม�อคดลอกสตรมาท� C2 ตาแหนงเซลลอางอง จะเปล�ยนเปน =A2+B2 โดยอตโนมต เปนตน ตวอยางการอางองแบบสมพนธ

1. คานวณหาผลบวก ของคาท� 1 กบคาท� 2

2. คดลอกสตร สงเกตดท�เซลลผลลพธ จะเกดการอางองแบบสมพนธ


5

การอางองแบบสมบรณ (Absolute References)

การอางองแบบสมบรณเปนการอางองแบบ Lock ตาแหนงเซลล โดยเม�อคดลอกสตร คาตาแหนงเซลลในสตรจะถก Lock ไว ไมใหไหลตามเซลล

หลกการกมอยวาใหพมพเคร�องหมาย $ ไวหนาเซลลท�ตองการ Lock ตาแหนง โดยคยลดในการเปล�ยนสตรจากคาสมพนธเปนคาสมบรณ คอ <F4> ตวอยางเชน ตองการ Lock เซลล A1 ใหทาดงน6

1. พมพ A1 2. ลากคลม A1 3. กดปม <F4> 1 คร6 ง 4. จะไดผลลพธ $A$1 (หมายถง Lock ท6งแถวและคอลมน ความหมายคอ Lock เซลล

น�นเอง) ตวอยางการอางองแบบสมบรณ

1. คานวณหาผลบวก ของคาท� 1 กบคาท� 2 โดยคาท� 2 มเพยงเซลลเดยวคอ B3 โดยตองการนาไปบวกกบคาท� 1 ทกๆ คา ดงน6นจงตอง Lock ตาแหนง เซลล $B$3 ไว

2. คดลอกสตรไปยงเซลลท�ตองการผลลพธ สงเกตดท�เซลลผลลพธ จะเกดการอางองแบบ

สมบรณ (เซลลท�มเคร�องหมาย $ จะไมมการเปล�ยนแปลงแตอยางใด)


6

การอางองแบบผสม (Relative & Absolute References)

การอางองแบบผสม เปนการอางองท�รวมการอางองแบบ Relative และ Absolute เขาดวยกน เพ�อตองการ Lock เฉพาะแถว หรอ เฉพาะคอลมน ท�ตองการเชน

1. A$1 หมายถง Lock แถวท� 1 (Absolute) ไว แตไมได Lock คอลมน A 2. $A1 หมายถง Lock คอลมน A (Absolute) ไว แตไมได Lock แถวท� 1

ตวอยางการอางองแบบผสม

ตองการคานวณหาคาสวนลดท� 10 % และ 20 % โดยมขอแมวาใหคานวณโดยปอนสตรแคเซลลเดยว หลงจากน6นจงคดลอกสตรไปยงเซลลผลลพธท�ตองการท6งหมด สามารถใชหลกการอางองเซลลแบบผสมตามข6นตอนดงน6

1. ตองการคานวณหาผลลพธ ราคา คณดวย สวนลด โดยท�ตองการคานวณสวนลด 10% และ 20% ของราคาจรง

2. หลกการปอนสตรการอางองแบบผสมใหสงเกตดงน6

• ถาขอมลท�นามาคานวณวางในแนวต6งให Lock คอลมน เชน ในตวอยางขอมลราคาอยในแนวต6งจง Lock เฉพาะคอลมน A ($A3) สวนแถวไมตอง Lock

• ถาขอมลท�นามาคานวณวางในแนวนอนให Lock แถว เชน ในตวอยางขอมลสวนลดอยในแนวนอนจง Lock เฉพาะแถวท� 2 (B$2)สวนคอลมนไมตอง Lock

3. คดลอกสตรไปยงเซลลอ�นๆ สงเกตดท�เซลลผลลพธ จะเกดการอางองแบบผสม


7

การตEงช2อเซลล/กลมเซลล (Range Name)

ในการอางองเซลลสาหรบการคานวณโดยใชช�อเซลลน6นๆ โดยตรง เชน A1, B1, C1 ซ�งตอนท�ผเขยนเพ�งเร�มศกษาโปรแกรม Microsoft Excel น6นบางคร6 งรสกงงๆ เหมอนกน เพราะการอางองเซลลแบบใชตาแหนงเซลลไมเปนการส�อความหมายน�นเอง ดงน6นการต6งช�อกลมเซลลสามารถชวยแกปญหาน6ไดเปนอยางด

การต6งช�อกลมเซลลเปรยบเทยบไดกบการ Lock ตาแหนงเซลล (Absolute) ซ�งขอดของการต6งช�อกลมเซลล ผเขยนขอสรปเปนขอๆ ดงน6 คอ

1. ส�อความหมายไดด ชวยใหการสรางสตรคานวณทาไดงายข6น 2. สามารถเรยกใชงานโดยพมพช�อไดเลย 3. มประโยชนสาหรบการนาไปใชสาหรบ การส�งพมพ การสรางรายการ การสรางตารางสรป

สาระสาคญ (PivotTable) และมประโยชนสาหรบนาไปใชเขยนโปรแกรม VBA (Visual Basic for Application)

การตEงช2อกลมเซลลแบบท2 1 (ตEงช2อตามหวตาราง)

1. เลอกกลมเซลลท�ตองการ รวมท6งหวตาราง ดวย

2. เลอกแทบ Formulas -> Create From Selection

• Top row: คลกเลอกเพ�อนาหวตารางดานบนสดเปนช�อ Range Name ซ�งในตารางตวอยางน6หวตารางอยดานบนสด

• Left Column: คลกเลอกเพ�อนาหวตารางดานซายสดเปนช�อ Range Name


8

• Bottom row: คลกเลอกเพ�อนาหวตารางดานลางสดเปนช�อ Range Name

• Right column: คลกเลอกเพ�อนาหวตารางดานขวาสดเปนช�อ Range Name

• คลกปม OK

3. ทดสอบการใชงานโดยคลกเมาสท�ชอง Name Box แลวเลอก Test โปรแกรมจะไปเลอกชวงเซลลอตโนมต

การตEงช2อกลมเซลลแบบท2 2 (แบบกาหนดเอง)

1. เลอกเฉพาะชวงขอมลท�จะนาไปใชงาน เชน เลอกชวงขอมลท�เปนคะแนนในคอลมน Test ต6งแต B2 ถง B7

2. เลอกแทบ Formulas -> Define Name หรอ คลกท�ชอง Name Box แลวต6งช�อกลมเซลล โดยมกฎในการต6งช�อดงน6

• ควรใชขอความภาษาองกฤษ ข6นตนคาดวยตวพมพใหญตามหลงดวยตวพมพเลก

• หามใชเคร�องหมายใดๆ ยกเวนเคร�องหมายขดลาง (Under Score)

• หามต6งช�อซ6ากน

ในตวอยางน6 ต6งช�อกลมเซลลวา Test

3. เม�อต6งช�อเสรจแลวใหกดปม <Enter> เปนอนเสรจพธ


9

4. ทดสอบการใชงานโดยคลกเมาสท�ชอง Name Box แลวเลอก Test โปรแกรมจะไปเลอกชวงเซลลอตโนมต

การเรยกใชช2อกลมเซลล

วธท�1: พมพเอง

วธท�2: ใชตวชวย

การลบช2อกลมเซลล

เลอกแทบ Formulas -> Name Manager


10

การคานวณดวยฟงกชน (Function)

จดเดนของโปรแกรม Microsoft Excel คอ เร�องการคานวณดวยฟงกชนสาเรจรป ซ�งฟงกชนสาเรจรปเปนการคานวณท�โปรแกรม Microsoft Excel เตรยมไวใหเพ�อใชสาหรบงานคานวณเฉพาะดาน งานท�มความสลบซบซอน เชน ฟงกชนทางดานคณตศาสตร ฟงกชนทางดานการเงน ฟงกชนทางดานสถต เปนตน ซ�งฟงกชนตางๆ ท�โปรแกรมเตรยมไวใหใชงานมมากกวา 300 ฟงกชน และการเรยนรการใชงานฟงกชนน6นไมยากจนเกนไป โดยพยามยามทาความเขาใจหลกการใชงาน เม�อมความเขาใจแลว สามารถนาฟงกชนไปประยกตใชงานไดอยางงายดาย

ทาความเขาใจกอนใชงานฟงกชน

1. ฟงกชนเปนการคานวณแบบสาเรจรปท�โปรแกรมเตรยมไวใหลวงหนา 2. ฟงกชนจะเกบไวเปนหมวดหม สาหรบงานเฉพาะดานตางๆ

3. การใชงานฟงกชนทาได 2 วธคอ

• ใช Function Wizard โดยคลกท�ปม หรอเลอกแทบ Formulas วธน6ผใชสามารถเลอกฟงกชนท�ตองการไดอยางสะดวกโดยไมตองพมพเอง การใช Function Wizard เหมาะกบการใชงานท�ไมมการซอนฟงกชน

• พมพช�อฟงกชนดวยตนเอง วธน6ผใชตองจาช�อฟงกชนท�ตองการใชงานใหได เพราะถาเกดพมพช�อฟงกชนผดจะปรากฏผลลพธเปน #NAME? ขอดของการพมพช�อฟงกชนดวยตนเองคอ เหมาะกบการใชฟงกชนซอนฟงกชน หรอการประยกตใชฟงกชนตางๆ เขาดวยกน

4. การใชงานฟงกชนโดยการพมพช�อฟงกชน ใหพมพเทากบกอน ตามดวยช�อฟงกชน ตามดวยวงเลบเปด แลวตามดวยคาท�นามาคานวณซ�งตองอยในเคร�องหมายวงเลบเสมอ เชน

= SUM(2,3) ผลลพธท�ไดคอ 5


11

5. สวนขยายฟงกชน (Argument) คอ คาสาหรบนามาใชเพ�อใหไดผลลพธจากการคานวณ ซ�งคา Argument หมายถงคาตางๆ ท�นามาคานวณเพ�อใหไดผลลพธตามท�ตองการ เชน ตวเลข ขอความ ตาแหนงเซลล คาตรรกะ ฟงกชนซอนฟงกชน เปนตน บางฟงกชนอาจจะมหรอไมม Argument กข6นอยกบรปแบบของฟงกชนน6นๆ

6. ถาหากฟงกชนใดม Argument มากกวา 1 Argument ระหวาง Argument น6นๆ จะก6นดวยเคร�องหมาย คอมมา (,) เชน ฟงกชน IF ม 3 Argument ดงน6 =IF(Argument1, Argument2,Argument3)

รปแบบของฟงกชน (Function)

ฟงกชนแบงเปน 2 รปแบบใหญๆ คอ 1. ฟงกชนท�ไมตองการ Argument มรปแบบดงน6

รปแบบ = Function Name( ) ตวอยางเชน ฟงกชน NOW(), TODAY() เปนตน

2. ฟงกชนท�ตองการ Argument มรปแบบดงน6

รปแบบ = Function Name(Argument) ตวอยางเชน ฟงกชน SUM(number), COUNT(number), AVERAGE(number), MAX(number), MIN(number) เปนตน

ทาความเขาใจสวนขยายในฟงกชน (Argument)

1. Argument ท�บงคบตองใสคา ถาไมใสคาจะไมสามารถคานวณหาผลลพธได 2. Argument ท�ไมบงคบใหใสคา หรอไมใสคากได กจะไดผลลพธเชนกน Argument ชนดน6

จะอยในเคร�องหมายกามป [ ] ตวอยางท� 1 การใชฟงกชน SUM โดยการพมพช�อฟงกชน

• Argument Number1 บงคบตองใส

• Argument Number2 ใสหรอไมใสกได

ตวอยางท� 2 การใชฟงกชน SUM โดยการเรยกใชจากฟงกชน Wizard

• Argument Number1 บงคบตองใส (แสดงขอความเปนตวหนา)

• Argument Number2 ใสหรอไมใสกได (แสดงเปนขอความธรรมดา)


12

ฟงกชนท2นยมใชงาน

เน�องจากฟงกชนมอยมากมายกวา 300 ฟงกชน โดยแบงเปนหมวดหม และการใชงานแตละฟงกชนน6นกมความแตกตางกนออกไป แตหลกการใชงานจะมลกษณะคลายๆ กน จะตางกนกคอสวนท�เปน Argument ของฟงกชนน�นเอง เพราะฉะน6นในหลกสตร Intermediate Excel ผเขยนขอยกตวอยางฟงกชนตางๆ ท�นยมใชงานสาหรบทกสาขาอาชพดงตอไปน6

ช2อฟงกชน หนาท2

IF ฟงกชนแบบมเง�อนไข SUMIF หาผลรวมตวเลขแบบมเง�อนไข COUNT นบจานวนท�เปนตวเลข, วนท�, เวลา COUNTIF นบจานวนแบบมเง�อนไข COUNTA นบจานวนท�เปนตวเลข, วนท�, เวลา และ ขอความ COUNTBLANK นบจานวนเซลลวาง AVERAGE หาคาเฉล�ย AVERAGEIF หาคาเฉล�ยแบบมเง�อนไข MAX หาคาสงสด MIN หาคาต�าสด LARGE หาคามากท�สดโดยระบเปนลาดบ SMALL หาคานอยท�สดโดยระบเปนลาดบ PRODUCT หาคาผลคณ SUMPRODUCT หาคาผลรวมของผลคณ ROUND ปดเศษตวเลข INT ปดเศษตวเลข TRUNC ปดเศษตวเลข LOWER เปล�ยนตวอกษรภาษาองกฤษเปนพมพเลกท6งหมด UPPER เปล�ยนตวอกษรภาษาองกฤษเปนพมพใหญท6งหมด PROPER กาหนดใหอกษรตวแรกของคาเปนตวพมพใหญ LOOKUP คนหาขอมลในตารางฐานขอมล


13

ตวอยางการใชงานฟงกชน

• =SUM(A1, B1) หาผลรวมคาท�อยใน A1 กบ B1

• =SUM(A1:A10) หาผลรวมของคาท�อยใน A1 ถง A10

• =SUM(A3:A8, B2:F4) หาผลรวมของคาท�อยในเซลล A3 ถง A8 กบผลรวมของคาในเซลล B2 ถง F4

• =AVERAGE(A1:A10) หาคาเฉล�ยของคาท�อยในเซลล A1 ถง A10

• =MAX(A1:A10) หาคาสงสดของคาท�อยในเซลล A1 ถง A10

• =MIN(A1:A10) หาคาต�าสดของคาท�อยในเซลล A1 ถง A10

จดกลมฟงกชนใหม

1. Number เชน � ABS/MOD/INT/TRUNCT/ROUND/ROUNDUP/ROUNDDOWN/CEILING/

FLOOR 2. Text เชน

� LOWER/UPPER/PROPER/LEN/LEFT/RIGHT/MID/TRIM/CONCAT 3. Date/Time เชน

� TODAY/NOW/YEAR/MONTH/DAY/DATE/DATEDIF/HOUR/MINUTE 4. Logic เชน

� IF/AND/OR/NOT/COUNTIF/SUMIF/COUNTIFS/SUMIFS 5. Lookup & Reference เชน

� VLOOKUP/HLOOKUP/INDEX/MATCH/INDIRECT/CHOOSE/OFFSET 6. Information เชน

� ISTEXT/ISNUMBER/ISERROR/ISNA/ISLOGIC


14

ฟงกชนเก2ยวกบตวเลข ROUND, ROUNDUP,

ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, MOD

ฟงกชน ABS (คาบวก)

รปแบบ = ABS (ตวเลข) เชน = ABS(-10) คาตอบท�ไดคอ 10

ฟงกชน MOD (หาจานวนเศษท2เหลอจากการหาร)

รปแบบ = MOD (ตวเลข, ตวหาร) เชน = MOD (13, 12) คาตอบท�ไดคอ 1

ฟงกชน INT (ตดเอาเฉพาะตวเลขจานวนเตม)

รปแบบ = INT (ตวเลข) เชน = INT (97.21) คาตอบท�ไดคอ 97

ฟงกชน TRUNC (ตดเอาตวเลขตามตาแหนงท2ระบ)

รปแบบ = TRUNC (ตวเลข, ตดก�ตาแหนง) เชน = TRUNC (97.219,2) คาตอบท�ไดคอ 97.21


15

ฟงกชน ROUND (ปดตามหลก >=5 ปดขEน <5 ปดลง)

รปแบบ = ROUND (ตวเลข, ก�ตาแหนง) เชน = ROUND(12.345,2) คาตอบท�ไดคอ 12.35

ฟงกชน ROUNDUP (ปดขEน)

รปแบบ = ROUNDUP (ตวเลข, ก�ตาแหนง) เชน = ROUNDUP(12.345,2) คาตอบท�ไดคอ 12.35

ฟงกชน ROUNDDOWN (ปดลง)

รปแบบ = ROUNDDOWN (ตวเลข, ก�ตาแหนง) เชน = ROUNDDOWN(12.345,2) คาตอบท�ไดคอ 12.34

ฟงกชน CEILING (ปดขEนใหลงทายดวยตวเลขท2ตองการ)

รปแบบ = CEILING (ตวเลข, ลงทายดวยอะไร) เชน = CEILING(97,10)-1 คาตอบท�ไดคอ 99

ฟงกชน FLOOR (ปดลงใหลงทายดวยตวเลขท2ตองการ)

รปแบบ = FLOOR (ตวเลข, ลงทายดวยอะไร) เชน = FLOOR(97,10)-1 คาตอบท�ไดคอ 89


16

ฟงกชนเก2ยวกบขอความ เชน LOWER, UPPER, PROPER,

RIGHT, LEFT, TEXT TO COLUMN, CONCATENATE

ฟงกชนรวมขอความ (CONCATENATE)

รปแบบ = ขอความ & ขอความ2

ฟงกชนแบงขอความ (Text to Column)

1. เลอกขอมล

2. เลอกแทบ Data -> Text to Columns…

• Delimited

• Fixed with

ฟงกชน LOWER (ตวพมพเลก)

รปแบบ = LOWER (ขอความ) เชน = LOWER (“Somkiat” ) คาตอบท�ไดคอ somkiat

ฟงกชน UPPER (ตวพมพใหญ)

รปแบบ = UPPER (ขอความ) เชน = UPPER (“Somkiat” ) คาตอบท�ไดคอ SOMKIAT

ฟงกชน PROPER (ขEนตนคาดวยตวพมพใหญ)

รปแบบ = PROPER (ขอความ) เชน = PROPER (“somkiat” ) คาตอบท�ไดคอ Somkiat


17

ฟงกชน LEN (นบจานวนอกขระ)

รปแบบ = LEN (ขอความ) เชน = LEN (“Somkiat” ) คาตอบท�ไดคอ 7

ฟงกชน TRIM (ใหเหลอ 1 เคาะวรรค)

รปแบบ = TRIM (ขอความ) เชน = TRIM (“Somkiat Chuaymak” ) คาตอบท�ไดคอ Somkiat Chuaymak

ฟงกชน LEFT (ตองการขอความตามจานวนท2ระบ นบจากซาย)

รปแบบ =LEFT (ขอความ, ก�ตว) เชน = LEFT (“Somkiat” , 3) คาตอบท�ไดคอ Som

ฟงกชน RIGHT (ตองการขอความตามจานวนท2ระบ นบจากขวา)

รปแบบ =RIGHT (ขอความ, ก�ตว) เชน = RIGHT (“Somkiat” , 3) คาตอบท�ไดคอ iat

ฟงกชน MID (ตองการขอความตามจานวนท2ระบ นบจากตาแหนงท2ระบ)

รปแบบ =MID (ขอความ, ตาแหนงเร�มตน, ก�ตว) เชน = MID (“Somkiat” ,4, 2) คาตอบท�ไดคอ ki


18

ฟงกชนเก2ยวกบวนท2 Date, Year, Month, Day, DateDif

ฟงกชน YEAR (แสดงป)

รปแบบ = YEAR (วนท�)

ฟงกชน MONTH (แสดงเดอน)

รปแบบ = MONTH (วนท�)

ฟงกชน DAY (แสดงวน)

รปแบบ = DAY (วนท�)

ฟงกชน DATE (แปลงขอมลใหเปนวนท2 ป, เดอน, วน)

รปแบบ = DATE (ป, เดอน, วน)

ฟงกชน DATEDIF (คานวณอายแบบ ป, เดอนวน)

รปแบบ = DATEDIF (วนเร�มตน, วนส9นสด, ผลลพธ)

• ผลลพธ: ป ใหใส “Y”

• ผลลพธ: เดอน ใหใส “YM”

• ผลลพธ: วน ใหใส “MD”


19

รปแบบของ IF, OR, AND, COUNTIF, SUMIF

ฟงกชน IF 2 คาตอบ

รปแบบ = IF (เง�อนไข, จรง, เทจ)

ฟงกชน IF มากกวา 2 คาตอบ

รปแบบ = IF (เง�อนไข1, คาตอบ1,

IF (เง�อนไข2, คาตอบ2,

IF (เง�อนไข3, คาตอบ3,

IF (เง�อนไขn, คาตอบn, [นอกเหนอจากน9น]

ฟงกชน OR

รปแบบ = OR (เง�อนไข1, เง�อนไข2,เง�อนไขn)

ฟงกชน AND

รปแบบ = AND (เง�อนไข1, เง�อนไข2,เง�อนไขn)

เชน A มากกวาเทากบ 5 และ B มากกวาเทากบ 50


20

ฟงกชน IF ซอน OR

รปแบบ = IF (OR (เง�อนไข1, เง�อนไข2 ,...), จรง, เทจ)

ฟงกชน IF ซอน AND

รปแบบ = IF (AND (เง�อนไข1, เง�อนไข2 ,...), จรง, เทจ)

ฟงกชน COUNTIF

รปแบบ = COUNTIF (ชวงขอมล, เง�อนไขท�มคาอยในชวงขอมล)

ฟงกชน SUMIF

รปแบบ = SUMIF (ชวงขอมล, เง�อนไขท�มคาอยในชวงขอมล, ชวงรวม)

ฟงกชน AVERAGEIF

รปแบบ = SUMIF /COUNTIF


21

ฟงกชนคนหาขอมล VLOOKUP, MATCH, INDEX

ฟงกชน VLOOKUP

รปแบบ = VLOOKUP (เง�อนไข, ตาราง, ลาดบท�คอลมนของผลลพธ, 1 หรอ 0)

ฟงกชน HLOOKUP

รปแบบ = HLOOKUP (เง�อนไข, ตาราง, ลาดบท�แถวของผลลพธ, 1 หรอ 0)

ฟงกชน ISNA

รปแบบ = ISNA (คา)

รปแบบ = IF (ISNA (VLOOKUP(…)), จรง, เทจ)

ฟงกชน MATCH

รปแบบ = MATCH (เง�อนไข, ชวงขอมล, 0)

ฟงกชน INDEX

รปแบบ = INDEX (ชวงขอมล, ลาดบท�)


22

ตวอยางการใชฟงกชน

ฟงกชน IF

ฟงกชน IF เปนฟงกชนท�ใชสาหรบการคานวณแบบมเง�อนไข ซ�งเปนฟงกชนท�ถกนามาใชงานมากท�สดกวาได เพราะฟงกชน IF สามารถนาไปประยกตใชรวมกบฟงกชนอ�นๆไดเปนอยางด ยกตวอยางเชน ตองการแบงเกณฑคะแนนสอบโดย ถาคะแนนมากกวาหรอเทากบ 50 ใหผาน ถาคะแนนนอยกวา 50 ใหตก เปนตน

รปแบบฟงกชน IF

ตวอยางการใชงานฟงกชน IF ตองการคานวณแบบมเง�อนไขดงน6 คอ คะแนน Test มากกวาหรอเทากบ 50 ใหผาน (Pass) แตถา

คะแนน Test นอยกวา 50 ใหตก (Fail) 1. คลกเมาสท�เซลล C2 พมพ =IF(Test>=50,"Pass", "Fail") โดยหลกการพมพ

• ถาเปนขอความ ขอความน6นตองอยภายในเคร�องหมายคาพด

• ถาเปนช�อ เซลล กลมเซลล ตวเลข คาตรรกะ (True, False) ไมตองมเคร�องหมายคาพด

2. เม�อปอนสตรเสรจแลวกดปม <Enter>

=IF (เง2อนไข, คาตอบเม2อเง2อนไขเปนจรง, คาตอบเม2อเง2อนไขเปนเทจ)


23

3. ปรากฏผลลพธ ดงรป

4. คดลอกสตรไปยงเซลลผลลพธทกเซลล


24

ฟงกชน IF ซอน IF

เม�อตวอยางท�ผานมาหากทานสงเกตจะพบวาการใชฟงกชน IF 1 ช6น สามารถคานวณหาผลลพธไดแค 2 เง�อนไข คอ ไมจรงกเทจ หากทานตองการคานวณหาผลลพธมากกวา 2 เง�อนไขจาเปนตองนาฟงกชน IF มาซอนกน โดยมหลกการพจารณาดงน6

เชน

• ถากาหนด 2 เง�อนไข จานวนช6นของ IF เทากบ 1 ช6น หรอ 1 IF

• ถากาหนด 5 เง�อนไข จานวนช6นของ IF เทากบ 4 ช6น หรอ 4 IF

• กาหนดเง�อนไขสดทายเปนคา False รปแบบของการใช IF ซอน IF

ตวอยางการใชงานฟงกชน IF ซอน IF

โจทย ตองการคานวณเกรด (Grade) โดยมเง�อนไข 5 เง�อนไขดงน6 1. ถาคะแนน (Test) มากกวาหรอเทากบ 80 ไดเกรด A 2. ถาคะแนน (Test) มากกวาหรอเทากบ 70 ไดเกรด B 3. ถาคะแนน (Test) มากกวาหรอเทากบ 60 ไดเกรด C 4. ถาคะแนน (Test) มากกวาหรอเทากบ 50 ไดเกรด D 5. ถาคะแนน (Test) นอยกวา 50 ไดเกรด F

= IF1(เง2อนไข 1, จรง 1, IF2(เง2อนไข 2, จรง 2, IF…n(เง2อนไข...n, จรง…n, เง2อนไขสดทายเปนเทจ)))

จานวนชEนของ IF = จานวนเง2อนไข – 1


25

ในตวอยางน6กาหนดเง�อนไข 5 เง�อนไข เพราะฉะน6นจานวนช6นของ IF เทากบ 4 ช6น (4 IF) และเง�อนไขสดทาย (ถาคะแนน (Test) นอยกวา 50 ไดเกรด F) ใสหลงสดคอ เปนคา False น�นเอง สามารถตความหมายเง�อนไขและมาพมพเปนสตรไดดงน6 คอ

=IF(Test>=80, "A", IF(Test>=70, "B", IF(Test>=60, "C", IF(Test>=50, "D", "F"))))

ฟงกชน MAXIF/MINIF

ในหวขอน6บางทานอาจจะสงสยวาฟงกชน MAXIF/MINIF มดวยหรอ อนท�จรงแลวฟงกชน MAXIF/MINIF ไมมหรอกครบ แตเปนการประยกตใชฟงกชน MAX/MIN รวมกบฟงกชน IF น�นเอง (ฟงกชน MAX/MIN ซอนอยในฟงกชน IF)

ตวอยางการใชงาน กาหนดเง�อนไขดงน6

1. ถาหากวาคะแนน (Test) มากท�สดใหปรากฏคาวา MAX 2. ถาหากวาคะแนน (Test) นอยท�สดใหปรากฏคาวา MIN 3. ถาไมใชคา MAX / MIN ใหปรากฏคาวาง

วธทา กาหนดเง�อนไข 3 เง�อนไข แสดงวา ใช IF 2 ช6น (2 IF)

1. ปอนสตรตามลาดบเง�อนไข 2. เง�อนไขสดทายเปนคา False (ในท�น6คอ ถาไมใชคา MAX / MIN ใหปรากฏคาวาง) 3. พมพสตรในเซลล E2 ตามลาดบเง�อนไขดงน6

=IF(MAX(Test)=B2, "MAX", IF(MIN(Test)=B2, "MIN", " "))


26

• IF(MAX(Test)=B2,"MAX" ถาคาในเซลล B2 ในชวงคะแนน Test เปนคา MAX ใหปรากฏคาวา MAX

• IF(MIN(Test)=B2,"MIN", ถาคาในเซลล B2 ในชวงคะแนน Test เปนคา MIN ใหปรากฏคาวา MIN

• " " ถานอกเหนอจากน6นเปนคาวาง

ฟงกชน COUNT

ฟงกชน COUNT เปนฟงกชนท�ใชสาหรบนบจานวนขอมลท�เปนตวเลข, วนท�, เวลา ซ�งมรปแบบการใชงานดงน6

ฟงกชน COUNTA

ฟงกชน COUNTA เปนฟงกชนท�ใชสาหรบนบจานวนขอมลท�เปนขอความ, ตวเลข, วนท�, เวลา คาตรรกะ และคาผดพลาด ซ�งมรปแบบการใชงานดงน6

ฟงกชน COUNTBLANK

ฟงกชน COUNTBLANK เปนฟงกชนท�ใชสาหรบนบจานวนเซลลท�เปนคาวาง ซ�งมรปแบบการใชงานดงน6

=COUNT(ชวงขอมล)

=COUNTBLANK(ชวงขอมล)

=COUNTA(ชวงขอมล)


27

ตวอยางการใชงาน 1. ต6งช�อกลมเซลลท�ตองการนาไปคานวณวา DataRange

2. ปอนสตรการคานวณดงรป

3. ผลลพธจากการใชฟงกชน COUNT ในรปแบบตางๆ

• COUNT เฉพาะขอมลท�เปนตวเลขและวนท�ไดเทากบ 5

• COUNTA เฉพาะขอมลท�เปนตวเลข วนท� และขอความไดเทากบ 8

• COUNTBLANK เฉพาะเซลลท�มคาวางไดเทากบ 4


28

ฟงกชน COUNTIF

ฟงกชน COUNTIF เปนฟงกชนท�ใชสาหรบนบแบบมเง�อนไข สามารถใชไดกบขอมลท�เปนท6งขอความ และขอมลตวเลข

รปแบบฟงกชน COUNTIF

ตวอยางการใชงาน

โจทย ตองการนบจานวนคนท�สอบผาน (Pass) วามจานวนก�คน วธทา

1. ต6งช�อกลมเซลลชวง C2 ถง C7 วา PassFail 2. คลกท�เซลลผลลพธ ในท�น6 คอเซลล C9 3. พมพ =COUNTIF(PassFail,"Pass") 4. กดปม <Enter> จะไดผลลพธ คนท�สอบผานเทากบ 5 คน

=COUNTIF (ชวงขอมล , เง2อนไข)


29

ฟงกชน SUMIF

ฟงกชน SUMIF เปนฟงกชนท�ใชสาหรบรวมขอมลท�เปนตวเลขแบบมเง�อนไข สามารถใชไดกบขอมลท�เปนตวเลข และ วนท�เวลา รปแบบฟงกชน COUNTIF


โจทย ตองการรวม Units เฉพาะ Category ท�ช�อ Coffee วธทา

1. เลอกชวงขอมลท6งหมด ต6งแต A1 ถง D9 2. เลอกแทบ Formula -> Create from Selection เลอก ต6งช�อกลมเซลลตามหวตาราง Top row

3. คลกท�เซลลผลลพธ ในท�น6 คอเซลล D11 4. พมพ =SUMIF(Category, "Coffee" , Units) 5. กดปม <Enter> ไดผลลพธการรวม Units เฉพาะ Category Coffee เทากบ 35

=SUMIF (ชวงขอมล , เง2อนไขในชวงขอมล , ชวงท2ตองการรวมตามเง2อนไข)


30

ฟงกชน AVERAGEIF

ฟงกชน AVERAGEIF เปนฟงกชนท�ใชสาหรบหาคาเฉล�ยแบบมเง�อนไข รปแบบฟงกชน AVERAGEIF


1. เลอกชวงขอมลท6งหมด ต6งแต D1 ถง D6 2. เลอกแทบ Insert, Names, Create… เลอก ต6งช�อกลมเซลลตามหวตาราง Top row

3. คลกท�เซลล D8 เพ�อหาคาผลรวมคาท�มากกวา 20

• พมพ =SUMIF(MyRange, ">20" , MyRange) กดปม <Enter> ไดผลลพธเทากบ 306.68

• เง�อนไขท�เปนเคร�องหมายเปรยบเทยบตองพมพภายในเคร�องหมายคาพด

4. คลกท�เซลล D9 เพ�อนบจานวนคาท�มากกวา 20

• พมพ =COUNTIF(MyRange,">20") กดปม <Enter> ไดผลลพธเทากบ 3

• เง�อนไขท�เปนเคร�องหมายเปรยบเทยบตองพมพภายในเคร�องหมายคาพด

5. คลกท�เซลล D10 เพ�อหาคาเฉล�ยของตวเลขท�มคามากกวา 20

• พมพ =D8/D9

=SUMIF/COUNTIF


31

ฟงกชนปดเศษ

ฟงกชนสาหรบปดเศษท�นยมใชงานม 3 ฟงกชนดวยกนคอ ROUND, TRUNC, INT ซ�งแตละฟงกชนใชงานอยางไร และมขอแตกตางกนหรอไมน6น ลองตดตามดตอไปไดเลยครบ

ฟงกชน ROUND

ฟงกชน ROUND มหนาท�ปดเศษตวเลขใหมตาแหนงทศนยมตามท�ระบ ROUND เปนฟงกชนท�คอนขางเหมาะสมท�สดสาหรบการปดเศษจานวนเงนท�เก�ยวของกบบญชและการเงน ซ�งหลกการปดเศษจะใชหลกการดงน6

• ตาแหนงท�ตองการปดเศษน6น จะพจารณาจากตวเลขหลงตาแหนงท�ตองการปดเศษ

• ถาตวเลขนอยกวา 5 จะปดลง

• ถาตวเลขมากกวาหรอเทากบ 5 จะปดข6น รปแบบฟงกชน ROUND

หลกการกาหนด จานวนตาแหนงทศนยมท�ตองการปดเศษ

1. ถาเทากบ 0 หรอไมใส ตวเลขจะถกปดเปนจานวนเตมท�ใกลท�สด (ไมมทศนยม)

• เชน =ROUND(199.38) คาตอบท�ไดคอ 199

2. ถามากกวา 0 ตวเลขทศนยมจะถกปดเศษเทากบจานวนหลกของตาแหนงทศนยมตามตวเลขท�ระบ (เล�อนไปทางขวาของจดทศนยม)

• เชน =ROUND(199.38,1) คาตอบท�ไดคอ 199.4

3. ถานอยกวา 0 ตวเลขจะถกตดไปทางซายของจดทศนยมตามตวเลขท�ระบ

• เชน =ROUND(199.38, -1) คาตอบท�ไดคอ 200

4. ถาตองการปดข6นอยางเดยวใหใชฟงกชน ROUNDUP

• เชน =ROUNDUP(199.38) คาตอบท�ไดคอ 200

• เชน =ROUNDUP(199.38,1) คาตอบท�ไดคอ 199.4

• เชน =ROUNDUP(199.38, -1) คาตอบท�ไดคอ 200

5. ถาตองการปดลงอยางเดยวใหใชฟงกชน ROUNDDOWN

• เชน =ROUNDDOWN(199.38) คาตอบท�ไดคอ 199

• เชน =ROUNDUP(199.38,1) คาตอบท�ไดคอ 199.3

• เชน =ROUNDUP(199.38, -1) คาตอบท�ไดคอ 190

=ROUND (ตวเลขท2ตองการปดเศษ, จานวนตาแหนงทศนยมท2ตองการปดเศษ)


32

ฟงกชน INT

INT เปนฟงกชนท�ทาหนาท�ตดเศษทศนยมท6ง ใหเหลอเฉพาะตวเลขจานวนเตม รปแบบฟงกชน INT


• เชน =INT(199.99) คาตอบท�ไดคอ 199

• เชน =INT(199.11) คาตอบท�ไดคอ 199

• เชน =INT(199) คาตอบท�ไดคอ 199

ฟงกชน TRUNC

TRUNC เปนฟงกชนท�ทาหนาท�คลายๆ กบฟงกชน INT ตางกนตรงท� ฟงกชน INT ตดเศษทศนยมท6ง ใหเหลอเฉพาะตวเลขจานวนเตม สวนฟงกชน TRUNC สามารถกาหนดจานวนตดเศษทศนยมได รปแบบฟงกชน TRUNC


เชน =TRUNC(199.99) คาตอบท2ไดคอ 199

เชน =TRUNC(199.99, 1) คาตอบท2ไดคอ 199.9

เชน =INT(199, -1) คาตอบท2ไดคอ 190

= TRUNC (ตวเลข, จานวนตาแหนงการปดเศษ)

= INT (ตวเลข)


33

ฟงกชนคนหาขอมล

ฟงกชนคนหาขอมลเปนฟงกชนในกลม LOOKUP ใชกบการคนหาขอมลในตารางฐานขอมล ซ�งฟงกชนท�นยมใชงานคอ VLOOKUP (คนหาคาจากตารางขอมลในแนวต6ง), HLOOKUP(คนหาคาจากตารางขอมลในแนวนอน), INDEX (คนหาคาตามดชนท�ระบ) เปนตน ลกษณะการทางานของฟงกชนประเภทน6 คอ กาหนดคาๆ หน�ง ใหไปคนหาคาในอกตารางหน�ง ถาเจอขอมลท�ตองการจะสงกลบคาน6นออกมา และถาไมเจอคาท�ตองการจะสงกลบคาผดพลาด #NA ออกมาเพ�อแจงใหทราบวาไมเจอขอมลท�ตองการคนหา

ฟงกชน VLOOKUP

VLOOKUP เปนฟงกชนท�นยมใชงานมากท�สดอกฟงกชนหน�ง ซ� งใชในการคนหาขอมลในตารางฐานขอมลตามแนวต6ง โดยดจากคอลมนแรกของตาราง และเม�อเจอคาท�ตองการ VLOOKUP จะสงกลบคาน6นออกมา และถาไมเจอคาท�ตองการจะสงกลบคาผดพลาด #NA ออกมาเพ�อแจงใหทราบวาไมเจอขอมลท�ตองการคนหา

รปแบบของฟงกชน

• lookup_value คาท�ตองการนาไปคนหา ในคอลมนแรกของ table_array (โดยการกาหนดคา lookup_value น6นจะตองสมพนธกบคาในคอลมนแรกของ table_array

• table_array ตารางท�เกบขอมล (ตารางฐานขอมล)

• col_index_num หมายเลขคอลมนใน table_array ท�ตรงกบคาผลลพธท�ตองการ

• range_lookup คาตรรกะ (True หรอ 1 หรอละไว, False หรอ 0)

=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)

Lookup_value

Table_array

Column_index_num


34

การกาหนดคา True หรอ False ในตาแหนง range_lookup นบไดวามความสาคญเปนอยางมาก ถาหากไมเขาใจอยางถกตองแลวอาจจะทาใหผลลพธเกดความผดพลาดได ดงน6นจงควรทาความเขาใจกบคา 2 คาน6 กอนดงน6

ความหมายของ True (หรอละไว) ม 3 ความหมายคอ

1. ขอมลในคอลมนแรกของตาราง table_array ตองมการจดเรยงจากนอยไปมาก ถาขอมลไมถกจดเรยงจะสงกลบคา Error (#N/A) ทนทโดยไมสนใจวาจะเจอขอมลหรอไม ดรปประกอบ เชนตองการคนหาเงนเดอนของ Bancha แต table_array ยงไมไดจดเรยงคอลมน NAME ผลลพธจงออกมาเปนคา #N/A ตวอยางเชน ในเซลลผลลพธ B11 พมพ = VLOOKUP(A11,A2:C7,2,True) คาตอบท�ไดเปน #N/A เพราะตารางขอมล Column Name ยงไมถกเรยงจากนอยไปมาก

2. ถาฟลด Name มการจดเรยงขอมลจากนอยไปมากจงจะไดผลลพธท�ตองการ


35

3. กรณท�มการจดเรยงขอมลแลว ถาไมเจอคาท�ตรงกนจรงๆ ฟงกชน VLOOKUP จะเปล�ยนใจมองหาคาท�นอยกวา หรอคาท�ใกลเคยงกบคาท�นาไปคนหา ออกมาเปนผลลพธแทน

• ถาเจอคาท�นอยกวาจะสงกลบผลลพธของคาท�นอยกวาหรอคาท�ใกลเคยงออกมา ในตวอยางตองการกาเงนเดอนของ Tanarat แตไมมขอมลของ Tanarat VLOOKUP จงไปนาคาเงนเดอนของ Suchin มาใหแทน

• และถาไมเจอคาท�นอยกวากบคาท�ตองการคนหา ฟงกชน VLOOKUP จะสงกลบคา Error (#N/A)


36

ความหมายของ False ม 3 ความหมายคอ 1. นาไปใชเม�อขอมลในคอลมนแรกของตาราง table_array ไมมการจดเรยงขอมลจากนอยไปมาก 2. ถาเจอคาท�ตองการ ฟงกชน VLOOKUP จะสงกลบคาน6นเปนผลลพธ

ตวอยางเชน ในเซลลผลลพธ B11 พมพ = VLOOKUP(A11,A2:C7,2,False) เม�อคาท�นาไปสบคนมอยในตาราง VLOOKUP จะสงกลบขอมลมาใหอยางถกตอง

3. ถาไมเจอคาท�ตรงกนจรงๆ จะสงกลบคาผดพลาด (#N/A) เปนผลลพธ ตวอยางเชน ในเซลลผลลพธ B11 พมพ = VLOOKUP(A11,A2:C7,2,False) เม�อคาท�นาไปสบคนไมมอยในตาราง VLOOKUP จะสงกลบ Error #N/A โดยไมลงเล


37

ตวอยางการใชงานฟงกชน VLOOKUP

ตวอยางการใชฟงกชน VLOOKUP เพ�อตองการตรวจสอบรายการสนคาวามรายการสนคาตามขอมลท�นาไปตรวจสอบหรอไม สามารถชวยใหเราทราบวาขอมลท�นาไปตรวจสอบเปนขอมลเกา หรอ ขอมลใหม

• ถาเปนขอมลเกา กจะปรากฏรายการสนคาน6นๆ เชน ในตวอยางน6 RAM กบ CPU เปนสนคาเกา หรอ ยงมสนคาอย

• ถาเปนขอมลใหม กจะปรากฏ Error #N/A เพราะไมม รายการสนคาน6นๆ เชน ในตวอยางน6 CDROM กบ HARDDISK เปนสนคาใหม หรอไมมรายการสนคา น�นเอง

วธการคานวณ

1. คลกในเซลลผลลพธ C2 2. พมพ =VLOOKUP(B2,$A$2:$A$5,1,False) 3. หรอพมพ =VLOOKUP(B2,$A$2:$A$5,1,0)

• B2 เปนคาท�นาไปคนหาขอมลในตารางขอมล

• $A$2:$A$5 เปนตารางขอมล (ตองใสสตรแบบ Absolute เพ�อ Lock ตาแหนงชวงตารางขอมลไวไมใหไหลตามเซลลในกรณท�มการคดลอกสตร)

• 1 หมายถง หมายเลขคอลมนเพ�อให VLOOKUP คนหาขอมลและสงผลลพธกลบมา

• False หรอ 0 หมายถง ถามขอมลจะสงกลบขอมลท�ถกตอง และถาไมมขอมลกจะสงกลบ #N/A เพ�อใหทราบวาไมเจอขอมลท�ตองการ

4. เม�อพมพสตรเสรจแลว ใหกดปม <Enter> เพ�อดผลลพธ 5. คดลอกสตรไปยงเซลลผลลพธทกเซลล


38

ฟงกชน ISERROR

ISERROR เปนฟงกชนท�ใชสาหรบตรวจสอบคาผดพลาด (Error) ตางๆ ไดทกคาผดพลาด (Error)เชน #N/A, #VALUE, #NAME?, #DIV0 เปนตน รปแบบของฟงกชน ISERROR

ความหมายของฟงกชน ISERROR

• ถาคาท�ตองการตรวจสอบเปนคา Error จะสงกลบคา TRUE เปนคาตอบ

• ถาคาท�ตองการตรวจสอบไมใชเปนคา Error จะสงกลบคา FALSE เปนคาตอบ

• คาท�ตองการตรวจสอบ อาจจะเปนการอางองตาแหนงเซลล, คาคงท� หรอ เปนฟงกชนกได


จากตวอยางท�ผานมาสามารถใชฟงกชน ISERROR ตรวจผลลพธในคอลมน C เพ�อเรยนรการใชงานฟงกชน ISERROR ดงรป

= ISERROR (คาท2ตองการตรวจสอบ)


39

ฟงกชน IFISERROR

เม�อทาความเขาใจฟงกชน ISERROR อยางถกตองแลว ในหวขอน6จะเปนการประยกตใชงานฟงกชน ISERROR โดยนาฟงกชน IF เขามาตรวจสอบเง�อนไข (ถาทานใดยงสบสนการใชฟงกชน IF ใหยอนกลบไปทบทวนการใชงานฟงกชน IF เสยกอนนะครบ) รปแบบฟงกชน IFISERROR

ความหมายของฟงกชน IFISERROR

• ฟงกชน ISERROR (คาท2ตองการตรวจสอบ) กาหนดเปนเง�อนไขของฟงกชน IF

• ถาเง�อนไขเปนจรงหรอเปนคาผดพลาด (Error) ผลลพธของเง�อนไขท�เปนจรงสามารถกาหนดเปนคาตวเลข คาตรรกะ (Logical) ขอความ สตร หรอฟงกชนกได

• ถาเง�อนไขเปนเทจหรอเปนคาอ�นท�ไมใชคาผดพลาด (Error) ผลลพธของเง�อนไขท�เปนเทจสามารถกาหนดเปนคาตวเลข คาตรรกะ (Logical) ขอความ สตร หรอฟงกชนกได


จากตวอยางท�ผานมาสามารถใชฟงกชน IFISERROR ตรวจสอบผลลพธจากการใชฟงกชน VLOOKUP และกาหนดเง�อนไขดงน6

• ถา VLOOKUP สงกลบเปนคา ERROR #N/A ใหปรากฏคาวา ขอมลใหม

• ถา VLOOKUP สงกลบเปนคาผลลพธท2คนหาเจอใหปรากฏคาวา ขอมลเกา

= IF (ISERROR(คาท2ตองการตรวจสอบ) , เง2อนไขเปนจรง, เง2อนไขเปนเทจ )


40

ฟงกชนเก2ยวกบขอความ

ฟงกชน LEN

LEN เปนฟงกชนท�ทาหนาท� นบจานวนอกขระ (รวมถงชองวาง ตวเลข สระ วรรณยกต และเคร�องหมายตางๆ) ใหผลลพธออกมาเปน คาตวเลข

รปแบบฟงกชน LEN


ฟงกชน UPPER

UPPER เปนฟงกชนท�ทาหนาท� กาหนดตวอกษรภาษาองกฤษใหเปนตวพมพใหญท6งหมดทกตวอกษร รปแบบฟงกชน UPPER


= UPPER (ขอความภาษาองกฤษ)

= LEN (จานวนอกขระท2ตองการนบ)


41

ฟงกชน LOWER

LOWER เปนฟงกชนท�ทาหนาท� กาหนดตวอกษรภาษาองกฤษใหเปนตวพมพเลกท6งหมดทกตวอกษร รปแบบฟงกชน LOWER


ฟงกชน PROPER

PROPER เปนฟงกชนท�ทาหนาท� กาหนดตวอกษรภาษาองกฤษตวแรกของคาใหเปนตวพมพใหญสวนตวอกษรท�ไมใชตวแรกใหเปนตวพมพเลก รปแบบฟงกชน PROPER


= PROPER (ขอความภาษาองกฤษ)

= LOWER (ขอความภาษาองกฤษ)


42

ฟงกชน LEFT

LEFT เปนฟงกชนท�ทาหนาท� สงกลบอกขระของคาโดยนบลาดบจากซายไปขวาตามจานวนของตาแหนงท�ระบ รปแบบฟงกชน LEFT


สามารถนาฟงกชน PROPER มาประยกตรวมกบฟงกชน LEFT ไดดงน6

= LEFT (ขอความภาษาองกฤษ , จานวนตาแหนงท2ระบ)


43

ฟงกชน CONCATENATE

CONCATENATE (&) เปนฟงกชนท�ใชสาหรบเช�อมขอความเขาดวยกน ซ�งการใชงานฟงกชนน6สวนใหญนยมพมพตว & (Ampersand) เพ�อเช�อมขอความกบขอความเขาดวยกน รปแบบฟงกชน CONCATENATE

หรอรปแบบฟงกชน CONCATENATE (พมพเคร�องหมาย &)


การเช�อมขอความดวยขอความ สวนใหญนยมพมพเคร�องหมาย & เปนตวเช�อม ซ�งหลกการใชงาน จานวนเคร�องหมาย & จะเทากบ จานวนขอความท�ตองการนามาเช�อมลบดวย 1 เชน ถาขอความท�ตองการนามาเช�อมตอเขาดวยกนม 2 ขอความ (หรอขอความจาก 2 คอลมน) จะใชเคร�องหมาย & เทากบ 1 ตว ดงตวอยาง

หรอ

= ขอความ1 & ขอความ2 & ขอความ…

= CONCATENATE (ขอความ 1 , ขอความ 2 , ขอความ… )


44

Data Text to Column

Text to Column เปนฟงกชนท�ใชสาหรบแบงขอความเปนคอลมน สวนมากจะใชในกรณท�ตองการนาไฟล text จากภายนอกเขามา เชน ไฟล text ท� Export ออกมาจากฐานขอมล และตองการนามาวเคราะหหรอสรปผลในโปรแกรม Microsoft Excel

ตวอยางไฟล text ท� Export ออกมาจากฐานขอมลเปดกบโปรแกรม Notepad

ขEนตอนการใชงาน Text to Column

1. เตรยมไฟล text ท�ตองการนาเขามาใชใน Microsoft Excel 2. เลอกแทบ File, Open 3. เลอกไฟล text ท�เตรยมไว แลวคลกปม Open

• Delimited: แบงขอความเปนคอลมนโดยใชตวแบง (ในตวอยางน6 เลอก Delimited เพราะใชคอมมาเปนตวแบง)

• Fixed width: แบงขอความเปนคอลมนดวยตนเอง


45

4. รายการ Delimiters คลกเลอก Comma (ใชคอมมาเปนตวแบง)

• รายการ Delimiters: เลอกชนดของตวแบง � Tab แบงโดยเคาะ Tab � Semicolon แบงโดยเคร�องหมาย : � Comma แบงโดยเคร�องหมาย , � Space แบงโดยการเคาะ Spacebar � Other แบงโดยเคร�องหมายอ�นๆ นอกเหนอจากท�กาหนดเชน เคร�องหมาย -

• รายการ Data Preview: ตวอยางการแบงขอความเปนคอลมน

5. คลกปม Finish จะไดผลลพธดงรป


46

Data Validation

การตรวจสอบการปอนขอมล หรอ Data Validation เปนฟงกชนท�ชวยในการตรวจสอบขอมลท�ผใชปอนลงไปวาถกตองตามเง�อนไขท�กาหนดไวหรอไม เชน ตองการใหผใชปอนเฉพาะขอมลท�เปนตวเลขเทาน6น หากผใชปอนตวเลขลงไปกใหยอมรบขอมล แตถาผใชปอนขอมลท�เปนขอความลงไปโปรแกรมจะไมยอมรบขอความท�ปอนลงไป เปนตน

การสรางกฎในการยอมรบขอมลท�ผใชปอนสามารถทาไดดงข6นตอนตอไปน6 1. เลอกชวงเซลลท�ตองการ 2. เลอกแทบ Data -> Data Validation… 3. ปรากฏกรอบโตตอบ Data Validation 4. บตรรายการ Settings: กาหนดเง�อนไขการยอมรบขอมล

Validation criteria Allow: เง�อนไขในการยอมรบขอมล

• Any value: ยอมรบทกๆ คา

• Whole number: ยอมรบเฉพาะตวเลขจานวนเตม

• Decimal: ยอมรบท6งตวเลขจานวนเตม และทศนยม

• List: สรางรายการใหผใชเลอก (Dropdown List)

• Date: ยอมรบเฉพาะขอมลท�เปนวนท�

• Time: ยอมรบเฉพาะขอมลท�เปนเวลา

• Text length: กาหนดความยาวของขอความท�ยอมรบ

• Custom: ยอมรบขอมลท�เปนสตร


47

5. บตรรายการ Input Message (ขอความแนะนาการปอนขอมล)

• Title: พมพขอความแสดงบนแถบช�อเร�อง

• Input Message: ขอความแนะนาการปอนขอมล

6. บตรรายการ Error Alert (ขอความแจงเตอนกรณปอนขอมลไมตรงตามเง�อนไข)

• Title: ขอความแสดงบนแถบช�อเร�อง

• Error message: ขอความแจงเตอน

• Style: ลกษณะรปไอคอน


48

สรางรายการดวย Dropdown List

ประโยชน

• ปองกนการปอนขอมลผดพลาด

• ประหยดเวลาไมตองพมพ ข6นตอนการสราง

1. สรางรายการท�ตองการนาไปทาเปน Drop Down List รอไวกอน 2. เลอกชวงเซลลท�ตองการสราง Drop Down List 3. เลอกแทบ Data, Validation แลวกาหนดคาดงรป

4. คลกปม OK 5. ทดสอบการใชงาน


49

Data Validation แบบ Custom

ประโยชน

• สรางเง�อนการปอนขอมลโดยใชสตร ข6นตอนการสราง

1. เลอกชวงเซลลท�ตองการ 2. เลอกแทบ Data, Validation แลวกาหนดคาดงรป

• Allow: เลอก Custom

• Formula: ปอนสตรหรอฟงกชนท�ตองการ � เชน กาหนดใหยอมรบขอมลท�เปนตวเลข

= ISNUMBER(ชวงเซลล) � เชน กาหนดใหยอมรบขอมลท�เปนตวอกษร

= ISTEXT(ชวงเซลล)


50

Conditional Formatting

การจดรปแบบขอมลดวยเง�อนไข หรอ Conditional Formatting เปนฟงกชนท�นาสนใจซ�งเหมาะกบการนาไปใชในกรณท�มขอมลจานวนมาก เชน ยอดขายมากกวา 1,000,000 ใหมสแดง ยอดการเทากบ 1,000,000 ใหมสน6าเงน และยอดขายต�ากวา 1,000,000 ใหมสเขยว เปนตน จดท�นาสนใจของ Conditional Formatting คอ เม�อมการเปล�ยนขอมล เชนจาก 800,000 ซ�งมสเขยว เม�อเปล�ยนเปน 2,000,000 ตวเลขจะเปล�ยนเปนสแดงโดยอตโนมต น�คอจดเดนของ Conditional Formatting

วธการเรยกใช: Home -> Conditional Formatting

New Rule = สรางเง2อนไขใหมดวยตนเอง

Manage Rule = จดการกบเง2อนไข/แกไขเง2อนไข

ClearRules = คนสสภาพเดม


51

การวเคราะหขอมลดวย What-If Analysis

ในโปรแกรม Microsoft Excel ไดเตรยมเคร�องมอสาหรบใชในการวเคราะหขอมลไว 3 เคร�องมอดวยกนคอ

1. การวเคราะหหาเปาหมาย (Goal Seek) 2. การวเคราะหขอมลเชงตาราง (Data Table) 3. การวเคราะหสถานการณสมมต (Scenarios)

การวเคราะหหาเปาหมาย (Goal Seek)

การวเคราะหคนหาเปาหมาย (Goal Seek) สามารถนาไปวเคราะหเก�ยวกบตวเลขท�ตองการ เม�อตวเลขอกคาหน�งเปล�ยนไป เชน ผเขยนอยากจะมเงนฝากในธนาคารสบลานบาท แตไมทราบวาจะตองฝากเงนเปนจานวนเทาไหร ถาเจอโจทยอยางน6ผเขยนสามารถใช Goal Seek มาวเคราะหเปาหมายวาจะตองฝากเงนเทาไหรไดอยางรวดเรวและมความแมนยาสง

ตวอยางการวเคราะหหาเปาหมาย

ตวอยาง ซ�อสนคามา 100 บาท ขายไป 200 บาท คานวณหากาไรไดเทากบ 100 บาท อยากทราบวาถาตองการกาไร 150 บาท จะตองขายสนคาในราคาเทาไหร

วธทา 1. เลอกเซลลท�เกดจากการคานวณ ในท�น6คอเซลล B3 2. เลอกแทบ Tools, Goal Seek… 3. ปรากฏกรอบโตตอบ Goal Seek ดงรป


52

• Set cell: เลอกเซลลท�เกดจากการคานวณ ในท�น6 คอเซลล B3 (กาไร)

• To value: ตองการเปล�ยนคาใน Set cell (กาไร) เปน 120 บาท

• By changing cell: เลอกเซลลท�ตองการวเคราะหหาเปาหมาย ในท�น6 คออยากทราบวาจะตองขายในราคาเทาไหรจงจะไดกาไร 120 บาท

4. เม�อกาหนดคาเสรจแลวคลกปม OK 5. จะไดผลลพธดงรป คอ ถาตองการกาไร 120 บาท จะตองขายสนคาในราคา 220 บาท


53

การวเคราะหขอมลเชงตาราง (Data Table)

การวเคราะหขอมลเชงตารางสามารถวเคราะหได 2 ทางดวยกนคอ 1. การวเคราะหทางเดยว เปนการวเคราะหในลกษณะตวแปรท�นามาวเคราะหอยทางแนวต6ง

(Column) หรอ แนวนอน (Row) อยางใดอยางหน�ง 2. การวเคราะห 2 ทาง เปนการวเคราะหในลกษณะตวแปรท�นามาวเคราะหอยทางแนวต6ง (Column)

และแนวนอน (Row)

ตวอยางการวเคราะหขอมลเชงตารางแบบทางเดยว

1. กาหนดขอมลตนฉบบในท�น6 คอเซลล C3, C4 2. คานวณท�เซลล C5 โดยนา C3 หารดวย C4 ไดผลลพธเทากบ 50 3. กาหนดตวแปรท�นาไปวเคราะห ในท�น6 คอจานวนคน โดยนาตวแปรไปวางในแนวต6ง 4. เลอกชวงขอมลท�ตองการนาไปวเคราะห ดงรป


54

5. เลอกแทบ Data, Table…

Column input cell: เลอกเซลลจานวนคนท�เปนตนฉบบในท�น6 คอเซลล C4

6. คลกปม OK 7. ผลลพธท�ไดจะเปนดงรป


55

ตวอยางการวเคราะหขอมลเชงตารางแบบ 2 ทาง

1. กาหนดขอมลตนฉบบในท�น6 คอเซลล C3, C4 2. คานวณท�เซลล C5 โดยนา C3 หารดวย C4 ไดผลลพธเทากบ 50 3. กาหนดตวแปรท�นาไปวเคราะห ในท�น6 คอจานวนคนวางในแนวต6ง (Column) จานวนเงนวางใน

แนวนอน (Row) 4. เลอกชวงขอมลท�ตองการนาไปวเคราะห ดงรป

5. เลอกแทบ Data, Table…


56

6. ปรากฏกรอบโตตอบ Table กาหนดคาดงรป

Column input cell: เลอกเซลลจานวนคนท�เปนตนฉบบในท�น6 คอเซลล C5 Row input cell: เลอกเซลลจานวนเงนท�เปนตนฉบบในท�น6คอเซลล C4

7. คลกปม OK 8. ผลลพธท�ไดจะเปนดงรป


57

การวเคราะหสถานการณสมมต (Scenarios)

การวเคราะหสถานการณสมมต (Scenarios) เปนการวเคราะหตวแปรในลกษณะคลายๆ กบการวเคราะหขอมลเชงตารางแตมความแตกตางตรงท� Scenarios จะวเคราะหไดผลลพธออกมาในรปแบบรายงาน (Report)

ตวอยาง ตองการคานวณหาคางวดในการผอนรถเปน 3 Case ดงน6 Case ท� 1 อยากทราบคางวดท� อตราดอกเบ6ย 3% จานวน 36 งวด Case ท� 2 อยากทราบคางวดท� อตราดอกเบ6ย 5 % จานวน 24 งวด Case ท� 3 อยากทราบคางวดท� อตราดอกเบ6ย 7 % จานวน 12 งวด

ข6นตอนการวเคราะหแบบสถานการณสมมต (Scenarios) 1. คานวณคางวดในเซลล B4 2. เลอกแทบ Tools, Scenarios… 3. ปรากฏกรอบโตตอบ Scenarios ดงรป

4. Scenario name: ต6งช�อสถานการณสมมตคร6งละ 1 Case เชน Case ท� 1 5. Changing cells: เซลลท�ตองการวเคราะห ในท�น6 คอ อตราดอกเบ6ย กบ จานวนงวด


58

6. คลกปม OK 7. กาหนดคาท�ตองการวเคราะหใน Case ท� 1

8. คลกปม Add 9. ต6งช�อ Scenario name: Case ท� 2 10. คลกปม OK 11. กาหนดคาท�ตองการวเคราะหใน Case ท� 2

12. คลกปม Add 13. ต6งช�อ Scenario name: Case ท� 3 14. คลกปม OK 15. กาหนดคาท�ตองการวเคราะหใน Case ท� 3

16. คลกปม Add 17. คลกปม Cancel 18. คลกปม Summary 19. ปรากฏกรอบโตตอบ Scenario Summary


59

• Report type: เลอก Scenario Summary

• Result cells: เลอกเซลลท�ตองการผลลพธ ในท�น6 คอ คางวด (B4)

20. คลกปม OK จะไดผลลพธ Scenario Report ดงรป


60

การวเคราะหขอมลดวย Analysis ToolPak

1. File -> Excel Options -> Add-Ins -> Go… -> เลอก Add-Ins ท�ตองการดงรป

2. การใชงานใหคลกไปท�แทบ Data ดงรป


61

กราฟ (Chart)

โปรแกรม Microsoft Excel เปนโปรแกรมท�มความสามารถอนหลากหลายไมวาจะเปนการคานวณ การบรหารขอมล การวเคราะหขอมล หรอแมแตการสรปขอมลดวยกราฟ Microsoft Excel กสามารถทาไดดทเดยว ไมเฉพาะสรปขอมลในรปแบบกราฟเทาน6น โปรแกรม Microsoft Excel ยงสามารถวเคราะหขอมลในรปแบบกราฟไดอกดวย

เน�องจากกราฟมรปแบบท�หลากหลาย และแตละรปแบบกข6นอยกบขอมลดวย ดงน6นกอนท�จะสรางกราฟจงควรทาความเขาใจกอนวากราฟมก�ชนด แตละชนดเหมาะกบขอมลประเภทใด และสวนประกอบของกราฟมอะไรบาง ดงท�ผเขยนจะบอกกลาวในลาดบตอจากน6

สวนประกอบของกราฟ

• ช�อกราฟ (Chart Title) คอช�อหรอหวเร�องของกราฟ

• แกน (Axis) คอ เสนแนวอางอง ถาเปนกราฟ 2 มต จะมแกน X ใชแสดงหมวดหรอชนดของขอมลในแนวนอน กบ Y ใชแสดงคาของขอมล(แนวต6ง) สวนกราฟ 3 มตจะเพ�มคาแกนลก (Z) เปนคาของขอมลอกคาหน�ง

• ช�อแกน (Axis Title) คอ คาอธบายแกน

• เสนกรด (GridLine) คอ เสนท�ต6งฉากกบแกนตางๆ เพ�อดขอมลไดสะดวกข6น

• ชดขอมล (Data Series) คอ กลมตวเลขท�เปนคาของส�งเดยวกน

• Legend คอ คาอธบาย เพ�อใหทราบวาสและลวดลายแตละแบบในกราฟใชทาขอมลอะไร


62

ชนดของกราฟ

กราฟแทงแนวต6ง (Column) เปนกราฟท�นยมใชงานมากท�สด เพราะสามารถเปรยบเทยบใหเหนความแตกตางของขอมลไดอยางชดเจนและงายตอการทาความเขาใจ

กราฟแทงแนวนอน (Bar) เปรยบเทยบใหเหนความแตกตางของขอมลทางดานเวลา, ระยะทาง

กราฟเสน (Line) แสดงคาของขอมลและแนวโนมภายในชวงระยะเวลาหน�ง

กราฟวงกลม (Pie) แสดงความสมพนธระหวางขอมลแตละคากบผลรวมของขอมลท6งหมด

กราฟ XY (XY Scatter) แสดงความสมพนธระหวางตวเลข 2 กลม เพ�อการวเคราะหแบบเชงเสน

กราฟพ6นท� (Area) คลายกราฟเสน แตจะแสดงใหเหนผลรวมและแนวโนมผลรวมของขอมลท6งหมด

กราฟรปโดนท (Doughnut) คลายกราฟวงกลมแตซบซอนกวา เพราะสามารถเปรยบเทยบขอมลต6งแต 2 ชดไดดวย

กราฟเรดาร (Radar) แสดงความสมพนธระหวางขอมลแตละชดในรปของขอบเขตพ6นท� ท�ขอมลชดน6นๆ ครอบครองโดยขอมลแตละชดมจดศนยกลางรวมกน


63

กราฟพ6นผว (Surface) ใชหาจดสมดลยของผลลพธจากตวแปร 2 ตว โดยตวหน�งอยทางแกน X และตวหน�งอยทางแกน Y พ6นผวในกราฟท�มสเดยวกน คอจดท�มความสมพนธระหวางตวแปรท6ง 2 ใหผลลพธออกมาเทาหรอใกลเคยงกน

กราฟฟองสบ (Bubble) คลายกบกราฟ XY (Scatter) แตมตวแปรไดเพยง 3 ตว โดยตวท� 3 จะแสดงดวยขนาดของฟองสบ

กราฟหน (Stock) เรยกอกอยางวากราฟ High-Low-Close เปนกราฟท�ใชแสดงความเปล�ยนแปลงของขอมลใดๆ ท�ประกอบไปดวยคาสงสด ต�าสดและคาอ�นๆ อกคาหน�ง ณ แตละจดของเวลาภายในชวงเวลาหน�งโดยมากมกใชกบราคาหนเพราะสามารถแสดงราคาสงสดต�าสด และราคาปดไดด

กราฟแทงรปทรงกระบอก (Cylinder) เหมอนกราฟแทง แตแสดงคาของขอมลเปนรปทรงกระบอกแทน

กราฟแทงรปทรงกรวย (Cone) เหมอนกราฟแทง แตแสดงคาของขอมลเปนรปทรงกรวยแทน

กราฟแทงรปปรามด (Pyramid) เหมอนกราฟแทง แตแสดงคาของขอมลเปนรปทรงปรามดแทน


64

ขEนตอนการสรางกราฟ

เคร2องมอ Chart Tools


65

เคร2องมอในกลม Design

เคร2องมอในกลม Layout

เคร2องมอในกลม Format

วธการเพ2มแกน Y ท2สอง (แกน Y สองแกน)

1. คลกขวาบนแทงกราฟ 2. เลอก Format Data Series 3. เลอก Secondary Axis


66

วธการเพ2มขอมลท2แกน X (Horizontal Axis Label)

1. คลกขวาบนรปกราฟ 2. เลอก Select Data 3. คลกปม Edit แลวเลอกขอมลตามตองการ


67

ฐานขอมล (Database)

ฐานขอมล (Database) คออะไร

ฐานขอมลหมายถง ขอมลตนฉบบท�ยงไมผานกระบวนการใดๆ เชน รายช�อสมครสมาชก ขอมลสนคาขอมลพนกงาน โดยฐานขอมลท�วไปจะมช�อเรยกสวนประกอบตางๆ เฉพาะดงน6

• หวตาราง (Column) เรยกวา ฟลด (Field)

• ขอมลแตละแถว (Row) เรยกวา Record

ทาความเขาใจฐานขอมล

• ฐานขอมลเปนการจดเกบขอมลท�สะดวกในการสบคน และสรปผลขอมล

• ในฐานขอมลเรยกรายการในแตละคอลมนวา Field เรยกรายการในแตละแถววา Record

• ฐานขอมลมหวตารางไดแค 1 แถว เรยกหวตารางวา “Header Row”

• การจดเกบขอมลตองจดเกบแบบตอเน�องหามมแถววาง ขอมลตองตอเน�องกนตลอด

ฐานขอมลท2ดควรเปนอยางไร

1. หวตาราง หรอ ฟลดตองมแคแถวเดยว 2. ควรแบงขอมลท�ตองการสรางฟลดใหมขนาดเลกท�สด เชน ตองการเกบขอมลพนกงาน ควร

ออกแบบฐานขอมลดงน6 คอ ฟลดคานาหนาช�อ, ฟลดช�อ, ฟลดนามสกล, ฟลดทอย, ฟลดเบอรโทรศพท

3. ขอมลแถวแรกตองอยตดกบหวตาราง 4. ควรจดเกบขอมลแบบตอเน�องหามมการเวนแถว 5. ไมควรจดรปแบบเซลลของขอมล เพราะจะทาใหฐานขอมลมขนาดใหญ

หวตาราง หรอ ฟลด (Field)

ขอมล 1 Record


68

ขอดของการจดเกบขอมลในรปแบบฐานขอมล

ถาจดเกบขอมลในรปแบบฐานขอมล เม�อนาขอมลไปใชงานโปรแกรมจะเลอกชวงเซลลขอมลใหโดยอตโนมต โดยท�นาเมาสไปคลกในเซลลใดกได แตเซลลน6นตองอยในชวงขอมลของฐานขอมล

1. การทาตารางสรปสาระสาคญ (Pivot Table) 2. การกรองขอมล (Filter) 3. การจดเรยงขอมล (Sorting) 4. การทารายงานผลรวมยอย (Sub Total)


69

Pivot Table

Pivot Table คอ อะไร

ตารางสรปสาระสาคญ หรอ ท�เรยกกนตดปากวา Pivot Table เปนฟงกชนท�ผใชงานโปรแกรม Microsoft Excel ทกคนจะตองรจกเปนอยางด เพราะสามารถสรปผลขอมล หรอวเคราะหขอมลไดอยางรวดเรว มความยดหยนสง และท�สาคญคอใชงานงาย

ความสามารถของ Pivot Table

1. สรปขอมลเปนตารางในรปแบบอตโนมต 2. สรปขอมลเปนกราฟไดอยางรวดเรว 3. สรปขอมลจากหลายๆ Worksheet ได (Consolidate) 4. สรปขอมลจากฐานขอมลได (Get External Data) 5. นาคณสมบต Filter, Sort, Subtotal, Consolidate, Group, Formula Calculate Filed และอ�นๆ ท�

นาสนใจมาใหเลอกใชงานอกมากมาย

แนวคดของ Pivot Table


70

ขEนตอนการทา Pivot Table

1. คลกแทบ Insert -> PivotTable

2. เลอกขอมลท�ตองการ (Key Information)

3. ดรายงาน PivotTable


71

เคร2องมอ PivotTable Tools

เคร2องมอ Options

เคร2องมอ Design


72

5.

การกรองขอมลดวย AutoFilter

การกรองขอมล หรอ Filter เปนฟงกชนหลกของโปรแกรมฐานขอมลเพราะ Filter ชวยใหทราบขอมลท�ตองการไดอยางรวดเรว เชน ตองการดขอมลเฉพาะ Sate ท�ช�อวา WA โปรแกรมกจะกรองขอมลออกมาไดอยางรวดเรวพรอมท6งแจงใหผใชทราบดวยวาขอมลท�กรองน6นมท6งหมดก� Record

ข6นตอนการกรองขอมลดวย AutoFilter สามารถทาไดดงน6 1. คลกเมาสเซลลใดกได (อยในชวงขอมล) 2. เลอกแทบ Data, Filter


73

การกรองขอมลดวย Advanced Filter

การกรองขอมลแบบ Advanced Filter ตองกาหนดพ6นท� 2 สวนดวยกนคอ 1. Data Range ชวงขอมลตนฉบบ 2. Criteria Range ชวงสาหรบกาหนดเง�อนไข

• การกาหนดเง�อนไขท�อยแถวเดยวกน จะหมายถง “และ (And)”

• การกาหนดเง�อนไขท�อยคนละแถวกน จะหมายถง “หรอ (Or)”

ข6นตอนการใช Advanced Filter

1. คลกเมาสเซลลใดกไดในชวงขอมลตนฉบบ 2. เลอกแทบ Data, Filter, Advanced Filter ปรากฏกรอบโตตอบ Advanced Filter ดงรป

• List range: ชวงขอมลตนฉบบ

• Criteria: ชวงเง�อนไข

• Copy to: ชวงผลลพธ

• Unique record only: หาก Record ใดซ6ากนจะคดลอกไปแค 1 Record


74

การจดเรยงขอมล (Sorting)

เม�อตองการนาขอมลตนฉบบมาเปนสารสนเทศ มความจาเปนท�จะตองจดเรยงขอมล เพ�อจดขอมลเปนหมวดหม และเพ�อความเปนระเบยบเรยบรอย

การจดเรยงขอมลใน Microsoft Excel สามารถทาได 4 วธคอ

1. จดเรยงขอมลโดยใชป มเคร�องมอ

• Sort Ascending : เรยงขอมลจากนอยไปมาก (0-9, A-Z, ก-ฮ)

• Sort Descending : เรยงขอมลจากนอยไปมาก (9-0, Z-A, ฮ-ก)

2. จดเรยงขอมลโดยใชแทบ Data, Sort…


75

การจดกลมและผลรวมยอย (Group & Sub Totals)

Sub Total เปนการนาความสามารถในการจดเรยงขอมล มาผสมผสานกบความสามารถในการคานวณดวยฟงกชนตางๆ เชน Sum, Count, Average ซ�งชวยใหการสรปขอมลทาไดอยางสะดวกรวดเรวโดย Microsoft Excel จะคานวณใหโดยอตโนมต อกท6ง Microsoft Excel ยงแถม Outline มาใหเพ�อสะดวกในการเลอกดขอมล การหาผลรวมยอยมข6นตอนดงน6

1. คลกเลอกเซลลใดกไดในชวงขอมล 2. เลอกแทบ Data, SubTotals… 3. ปรากฏกรอบโตตอบ Subtotals ดงรป

• At each change in: เลอกฟลดตารางท�ตองการจดกลม (การจดเรยง)

• Use function: เลอกฟงกชนท�ตองการใชในการคานวณ

• Add Subtotal to: เลอกฟลดท�ตองการคานวณ

• Replace current subtotals: แทนท� Subtotals ท�เคยทาไวกอนหนาน6

• Page break between groups: ตเสนแบงกลมขอมล

• Summary below data: คานวณผลลพธไวดานลางของกลมขอมล

• Remove All: ลบ Subtotals ท�เคยทาไวท 6งหมด


76

การรวมขอมล (Consolidate)

Consolidate เปนการนาขอมลจากหลายๆ ตารางมาคานวณเพ�อสรปขอมล ซ�งแหลงของตารางขอมลท�จะนามาคานวณเพ�อหาผลสรป สามารถนามาจาก Worksheet เดยวกน ตาง Worksheet หรอตาง Workbook กได และฟงกชนท�นามาคานวณกเปนฟงกชนท�นยมใชงานหลายฟงกชน นอกจากน6นในตารางสรปโปรแกรมยงแถม Outline มาใหอกดวย

การรวมชดขอมล (Data Consolidate) เปนฟงกชนสาหรบรวมหรอสรปขอมลหลายๆ ตารางเขาดวยกนอยางรวดเรว โดยตารางแตละตารางตองเปนรปแบบเดยวกน

Consolidate = Table1+Table2+Table3+Table4

Table1 Table3

Table2

Table4


77

F5 -> Special ชวยเลอกขอมล

การเตมขอมลในเซลลวาง

1. เลอกขอมล 2. F5 -> Special -> Blanks 3. = 4. ลกศรข6น 5. Ctrl Enter

การคดลอกเฉพาะขอมลท2มองเหน

1. เลอกขอมล 2. F5 -> Special -> Visible cells only 3. คดลอกขอมล (Ctrl C) 4. วางขอมล (Ctrl V)


78


79

การใชงานอ2นๆ

การตดตามสตร

การตดตามสตร (Formula Auditing) เปนเคร�องมอท� Microsoft Excel เตรยมไวสาหรบวตถประสงคเพ�อตดตามสตร ตดตามความผดพลาด ตดตามท�ไปท�มาของสตร แสดงขอคดเหน (Comment) ซ� งเคร�องมอท�ใชสาหรบตดตามสตรสามารถเรยกใชไดจากแทบคาส�ง

Tools, Formula Auditing, Show Formula Auditing Toolbar

1. เคร�องมอ Error Checking ใชสาหรบตรวจสอบความผดพลาดของสตร

2. เคร�องมอ Trace Precedents ใชสาหรบตรวจสอบตรวจสอบวาสงคามาจากท�ใดบาง 3. เคร�องมอ Remove President Arrow ใชสาหรบลบลกศร Trace Presidents

4. เคร�องมอ Trace Dependents ใชสาหรบตรวจสอบวาสงไปใหใครบาง 5. เคร�องมอ Remove Dependent Arrow ใชสาหรบลบลกศร Trace Dependents

6. เคร�องมอ Remove All Arrow ใชสาหรบลบเสนลกศรท6งหมด 7. เคร�องมอ Trace Error ใชสาหรบหาท�ไปท�มาของคาความผดพลาด 8. เคร�องมอ Comment ใชสาหรบเพ�มขอคดเหน

9. เคร�องมอ Show watch Windows ใชสาหรบแสดงคาท�อยในเซลล

10. เคร�องมอ Evaluate Formula ใชสาหรบดสรปสตร


80

Trace Dependents

1. คลกเซลล B2

2. คลกปม Trace Dependents คร6 งท�1 จะปรากฏเสนลกศรสน6าเงนแสดงใหทราบวาคาในเซลลปจจบนท�เลอกอย สงคาไปใหเซลลใดบาง

3. คลกปม Trace Dependents คร6 งท� 2 จะปรากฏเสนลกศรแสดงใหทราบวาสงคาตอเน�องไปท�เซลลใดอกบาง

4. คลกปม Trace Dependents คร6 งท� 3 จะปรากฏเสนลกศรแสดงใหทราบวาสงคาตอเน�องไปท�เซลลใดอกบาง


81

Remove Dependent Arrows

1. คลกเซลล B2 2. คลกปม Remove Dependent Arrows คร6 งท�1 เปนการลบเสนลกศรสน6าเงน Step

ท� 1

3. คลกปม Remove Dependent Arrows คร6 งท� 2 เปนการลบเสนลกศรสน6าเงน Step

ท� 2

4. คลกปม Remove Dependent Arrows คร6 งท� 3 เปนการลบเสนลกศรสน6าเงน Step ท� 1


82

Trace Precedents

1. คลกเซลล E10

2. คลกปม Trace Precedents คร6 งท�1 จะปรากฏเสนลกศรสน6าเงน Step ท� 1 แสดงใหทราบวาคาในเซลลปจจบนท�เลอกอย สงมาจากเซลลใดบาง

3. คลกปม Trace Precedents คร6 งท� 2 จะปรากฏเสนลกศรสน6าเงน Step ท� 2 แสดงใหทราบวา เซลลใน Step ท� 1 รบคามาจากเซลลใดบาง

4. คลกปม Trace Precedents คร6 งท� 3 จะปรากฏเสนลกศรสน6าเงน Step ท� 3 แสดงใหทราบวา เซลลใน Step ท� 2 รบคามาจากเซลลใดบาง


83

Remove Precedent Arrows

1. คลกเซลล E10 2. คลกปม Remove Precedent Arrows คร6 งท�1 เสนลกศรสน6าเงน Step ท� 3 จะถกลบ

ออกไป

3. คลกปม Remove Precedent Arrows คร6 งท� 2 เสนลกศรสน6าเงน Step ท� 2 จะถกลบ

ออกไป

4. คลกปม Remove Precedent Arrows คร6 งท� 3 เสนลกศรสน6าเงน Step ท� 1 จะถกลบออกไป


84

Trace Error

ตวอยาง จากรปสมมตวามการปอนขอความลงไปในเซลล B5 จะเกดขอความแสดงคาผดพลาด #VALUE! ในเซลล E5, E10, E11 ผเขยนสามารถตรวจสอบท�ไปท�มาของคาความผดพลาดไดดงน6

1. คลกเลอกเซลลท�แสดงคาผดพลาด #VALUE! เซลลใดกได เชน ผเขยนเลอกเซลล E5 2. คลกปมเคร�องมอ Trace Error 3. ปรากฏเสนลกศรสแดง และสน6าเงน ดงรป

• เสนลกศรสน6าเงน แสดงท�มาของคาความผดพลาด

• เสนลกศรสแดง แสดงความผดพลาดท�ท�เกดตอเน�องกนไป

4. คลกปม Remove All Arrows เพ�อลบเสนลกศรท6งหมดในคร6 งเดยว


85

Formula Auditing Mode

ในบางคร6 งหากตองการแสดงสตรท�อยในเซลล เพ�อแกไขสตร หรอเพ�อส�งพมพสตรเกบไวใชงานในกรณท�ไฟลเกดเสยข6นมา จะไดสรางสตรข6นมาใหมไดอยางถกตองเหมอนเดม

การส�งใหแสดงสตรทาไดตามข6นตอนดงน6 1. เลอกแทบคาส�ง Tools, Formula Auditing, Formula Auditing Mode 2. แสดงผลลพธดงรป

Comment

หมายเหต หรอ Comment ใชสาหรบแสดงขอมลท�มความสาคญหรอเปนจดเดน เชน ยอดขายสงสด ยอดขายต�าสด ตามตวอยาง

1. คลกเลอกเซลลท�ตองการแทรก Comment เชน เซลล C6 (ตวเลขแสดงยอดขายสงสด) 2. คลกปม Comment 3. พมพขอความคดเหนตามตองการ

4. หากใน Worksheet มขอคดเหนมากกวา 1 สามารถเลอกแทบคาส�ง View, Comment เพ�อแสดงขอคดเหนท6งหมดในคร6 งเดยว


86

การปองกนขอมลใน Excel

Microsoft Excel 2000/2002/2003 ไดตระเตรยมฟงกชนสาหรบการปองกนขอมลในกรณท�เปนเอกสารลบ หรอเอกสารท�ยอมใหผใชปอนขอมลในเฉพาะชวงเซลลท�กาหนด สาหรบบทความน6กระผมขอเสนอวธการ และรปแบบการปองกนขอมลใน Microsoft Excel ดงตอไปน6

การปองกนขอมลใน Excel แบงเปน 4 ระดบคอ

1. การปองกนระดบไฟล 2. การปองกนระดบ WorkBook 3. การปองกนระดบ WorkSheet 4. การปองกนระดบ Cell

การปองกนระดบไฟล

การปองกนระดบไฟล เปนการปองกนตอนเปดไฟล โดยหากไมทราบ Password กไมสามารถเปดไฟล หรอแกไขไฟลได ข6นตอนการปองกนระดบไฟลทาไดดงน6

1. เปดไฟลท�ตองการ 2. เลอกแทบ File -> Save As… 3. คลกปม Tools เลอกแทบยอย General Options… 4. ปรากฏหนาตาง Save Options ดงรป

� Always create backup: กาหนดให Excel สรางไฟล backup � Password to open: รหสปองกนการเปดไฟล � Password to modify: รหสปองกนการแกไข, ปองกนการบนทกทบไฟลเดม


87

5. หากตองการยกเลก Password กสามารถทาไดเชนเดยวกบข6นตอนท� 1-4 แลวลบ Password ท6งไป

การปองกนระดบ WorkBook

การปองกนระดบ WorkBook เปนการปองกนส�งท�อยใน WorkBook เชน หามเปล�ยนช�อ WorkSheet หามยอหรอขยายหนาตาง ข6นตอนการปองกนระดบ WorkBook ทาไดดงน6

1. เปดไฟลท�ตองการ 2. เลอกแทบ Tools -> Protection -> Protect WorkBook…. 3. ใส Password ตามตองการ


88

การปองกนระดบ WorkSheet

การปองกนระดบ WorkSheet เปนการปองกนส�งท�อยใน WorkSheet เชน หามแกไขเซลล, หามแทรกแถวหรอคอลมน สามารถทาไดตามข6นตอนตอไปน6

1. เปดไฟลท�ตองการ 2. เลอกแทบ Tools -> Protection -> Protect WorkSheet…

การปองกนระดบ Cell

หากตองการใหผใชงานกรอกขอมลเฉพาะบางเซลล (ยกเลกการ Lock เฉพาะบางเซลล) สามารถทาไดดงตอไปน6

1. เปดไฟลท�ตองการ 2. เลอกเซลลท�ไมตองการ Lock (ยอมใหผใชกรอก หรอแกไขขอมลได) 3. เลอกแทบ Format, Cells… แลวเลอกบตรรายการ Protection กาหนดคาดงรป


89

4. เลอกแทบ Tools -> Protection -> Protect WorkSheet…


90

การ Share Workbook

โปรแกรม Microsoft Excel ไดเตรยมเคร�องมอไวสาหรบตดตามการเปล�ยนแปลง การแกไขไฟล การเพ�มขอมล การลบขอมล ซ�งคนท�เปนเจาของไฟล หรอเจาของ Workbook น6นๆ สามารถยอมรบ หรอไมยอมรบการเปล�ยนแปลงกได อกท6งยงสามารถดสรปประวตการเปล�ยนแปลงท�เกดข6น ทาใหการใชงานมความยดหยน ในกรณท�ตองการใหผอ�นใชงาน Workbook รวมกนได ซ�งการใชงานในลกษณะน6จะเรยกวาการ Share Workbook

การเปดการ Share Workbook

ตวอยาง กอนท�จะ Share Workbook ผเขยนปอนขอมลไวดงรป เม�อตองการ Share Workbook ผเขยนสามารถทาไดดงน6

1. เลอกแทบ Tools, Share Workbook… 2. ปรากฏกรอบโตตอบดงรป

3. บตรรายการ Editing

• เลอก Allow changes … เพ�อเปดการ Share Workbook ยอมใหผใชคนอ�นเขามาแกไขขอมลได

• หากตองการยกเลกการ Share Workbook ใหคลกยกเลกการเลอกท� Allow changes…

4. บตรรายการ Advanced


91

• Keep change history for…days: กาหนดใหเกบประวตการเปล�ยนแปลงตามจานวนวนท�ระบ

• Don’t keep change history: กาหนดใหไมมการเกบประวตการเปล�ยนแปลง

5. เม�อกาหนดคาเสรจแลว คลกปม OK 6. จะปรากฏคาวา Share ตรง Title bar เพ�อแจงใหทราบวา กาหนดให Workbook น6

สามารถใชงานรวมกนได

เปล2ยนแปลง/แกไข Workbook

ข6นตอนน6 เปนการเปล�ยนแปลง/แกไข Workbook ดงรป

• ในเซลล B2 ปอนเลข 10





92

การตดตามการเปล2ยนแปลง (Track Changes)

การตดตามการเปล�ยนแปลงสามารถทาได 2 ลกษณะดวยกนคอ 1. Accept or Reject Changes: ยอมรบหรอปฏเสธการเปล�ยนแปลง 2. Highlight Changes: เนนเซลลท�มการเปล�ยนแปลง

Accept or Reject Changes

1. เลอกแทบ Tools, Track Changes, Accept or Reject Changes 2. ปรากฏกรอบโตตอบ ใหเลอกการเปล�ยนแปลงท�เกดข6นท6งหมด ดงรป

3. คลกปม OK 4. ปรากฏกรอบโตตอบ Accept or Reject Changes ดงรป

• Accept All: ยอมรบการเปล�ยนแปลงท6งหมด

• Reject All: ปฏเสธการเปล�ยนแปลงท6งหมด

• Accept: ยอมรบการเปล�ยนแปลงคร6 งละ 1 เซลล

• Reject: ปฏเสธการเปล�ยนแปลงคร6 งละ 1 เซลล

5. ตวอยางน6สมมตวาผเขยนกดปม Reject All ผลลพธท�ไดเปนดงรป


93

Highlight Changes

1. เลอกแทบ Tools, Track Changes, Highlight Changes 2. ปรากฏกรอบโตตอบ Highlight Changes ดงรป

• Highlight changes on screen: เนนการเปล�ยนแปลงบนหนาจอ

• List changes on a new sheet: แสดงประวตการเปล�ยนแปลงในชตใหม

3. คลกปม OK ปรากฏผลลพธ เซลลท�มการเปล�ยนแปลงจะถกเนนดงรป

4. เม�อผเขยนนาเมาสไปคลกท�เซลล B2 จะปรากฏ Comment โดยอตโนมต เพ�อบอกใหทราบวามการเปล�ยนแปลงแกไขขอมลในเซลลอยางไรบาง


94

Form Toolbars

Form เปนสวนท�ทาหนาท�ตดตอกบผใช (User Inter Face) เพ�อใหผใชงานมความสะดวกในการปอนขอมล เลอกขอมล สรปผลขอมล ฯลฯ

ในโปรแกรม Microsoft Excel ไดเตรยมเคร�องมอ Form สาหรบการออกแบบหนาตาการใชงานท�นบไดวามความยดหยน เพราะสามารถสราง Form รวมกบการคานวณไดอยางลงตวและรวดเรว เชน Form สาหรบการขาย/ส�งซ6อสนคา Form สรปขอมล เปนตน

แนะนาเคร2องมอ Form

กอนการออกแบบ Form ใน Program Microsoft Excel มความจาเปนทราบวาเคร�องมอแตละตวน6นทาหนาท�อะไร และใชงานอยางไร ข6นแรกใหเรยกเคร�องมอ Form ออกมากอนโดยเลอกแทบ View, Toolbars, Form

Label ใชสาหรบสรางขอความปายช�อ

• Edit Box ใชสาหรบแสดงผลขอความหรอตวเลข

• Group Box ใชสาหรบสรางกลม Check Box หรอ Option Button

• Button ใชสาหรบสรางปมกดเพ�อส�งใหไปรนโปรแกรมหรอ Macro

• Check Box ใชสาหรบสรางตวเลอก

• Option Button ใชสาหรบสรางตวเลอก

• List Box ใชสาหรบสรางรายการใหผใชคลกเลอก

• Combo Box ใชสาหรบสรางรายการใหผใชคลกเลอกในลกษณะ Dropdown List

• Scroll Bar ใชสาหรบใหผใชคลกเลอกในลกษณะแถบเล�อน

• Spinner ใชสาหรบใหผใชคลกเลอกในลกษณะไมมแถบเล�อน

โหมดการใชงานของเคร2องมอ Form

การใชงานเคร�องมอ Form สามารถทาไดในลกษณะการวาดรป น�นคอ คลกเลอกเคร�องมอท�ตองการ แลวนาไปวาดตรงตาแหนงท�ตองการ ซ�งหลงจากวาดเสรจแลว เคร�องมอ Form จะมโหมดการใชงาน 2 โหมดดวยกนคอ

1. โหมดออกแบบ (Design ) ใหคลกเมาสขวา 2. โหมดทางาน (Run) ใหคลกเมาสซาย


95

การออกแบบเคร2องมอ Form

เคร�องมอ Form มคณสมบตเฉพาะคอ ในการออกแบบการใชงานเคร�องมอ Form จะคนคาออกมาท� Cell Link ในรปแบบของ ตวเลข คาตรรกะ ซ�งเราสามารถนาคาท�ไดไปสรางการคานวณเพ�อใหไดแบบ Form ท�มความยดหยน ซ�งผเขยนขออธบายพรอมตวอยางดงตอไปน6

การออกแบบ Check Box

1. คลกเลอกเคร�องมอ Check Box 2. นาไปวาดตรงตาแหนงท�ตองการ และต6งช�อ Check Box ตามตองการดงรป

3. คลกขวาท� Check Box เลอก Format Control… 4. เลอกบตรรายการ Control แลวกาหนดคาดงรป

• Value: กาหนดคาเร�มตนวาเลอก หรอไมเลอก

• Cell link: กาหนด Cell link เพ�อนาคาไปคานวณตอ

• คลกปม OK 5. ผลลพธท�ไดจากการออกแบบใน Cell link ของ Check Box จะคนคาออกมาเปน True, False ตาม

สถานการณเลอก หรอไมเลอก ดงรป

6. นาคาท�ไดจาก Cell link ไปคานวณตอดงตวอยาง เชน ถาคาในเซลล B2 เปน True ใหแสดงผลลพธวา “เปนสมาชก” ถาคาในเซลล B2 เปน False ใหแสดงผลลพธวา “ไมเปนสมาชก” โดยใชฟงกชน IF เขามาตรวจสอบดงน6 =IF(B2=TRUE,"เปนสมาชก","ไมเปนสมาชก") ผลลพธท�ไดเปนดงรป


96

การออกแบบ Option Button

1. คลกเลอกเคร�องมอ Option Button 2. นาไปวาดตรงตาแหนงท�ตองการ และต6งช�อใหกบ Option Button ตามตองการ ดงรป

3. คลกขวาท� Option Button ตวใดตวหน�งกได เลอก Format Control… 4. เลอกบตรรายการ Control แลวกาหนดคาดงรป

• Value: กาหนดคาเร�มตนวาเลอก หรอไมเลอก

• Cell link: กาหนด Cell link เพ�อนาคาไปคานวณตอ

• คลกปม OK

5. ผลลพธท�ไดจากการออกแบบใน Cell link ของ Option Button จะคนคาออกมาเปนตวเลข 1หรอ 2 ตามลาดบ ดงรป

6. นาคาท�ไดจาก Cell link ไปคานวณตอดงตวอยาง เชน ถาคาในเซลล B2 เทากบ 1 ใหแสดงผลลพธวา “สมาชก” ถาคาในเซลล B2 เทากบ 2 ใหแสดงผลลพธวา “ไมใชสมาชก” โดยใชฟงกชน IF เขามาตรวจสอบดงน6 =IF(B2=1,"สมาชก","ไมใชสมาชก") ผลลพธท�ไดเปนดงรป


97

การออกแบบ Combo Box

1. สรางรายการท�ตองการนาไปใชทาเปน Dropdown List

2. คลกเลอกเคร�องมอ Combo Box 3. นาไปวาดตรงตาแหนงท�ตองการ

4. คลกขวาท� Combo Box ท�วาดไว แลวเลอกแทบยอย Format Control… 5. เลอกบตรรายการ Control แลวกาหนดคาดงรป

• Input range: เลอกชวงเซลลท�ตองการนาไปสรางรายการ

• Cell link: เลอกเซลล Link โดยในการเลอกขอมลจากกรายการตว Combo Box จะสงกลบคาตวเลขตามลาดบ (จากบนลงลาง โดยรายการท�อยบนสดจะใหคาเปนลาดบเลขท� 1)

• Drop down lines: กาหนดรายการขอมลท�ตองการใหปรากฏตอนคลกเลอกท� Combo Box

6. ผลลพธท�ไดจากการออกแบบใน Cell link ของ Combo Box จะสงกลบคาตวเลขตามลาดบ (จากบนลงลาง โดยรายการท�อยบนสดจะใหคาเปนลาดบเลขท� 1)


98

7. นาคาท�ไดจาก Cell link ไปคานวณตอดงตวอยาง เชน ถาคาในเซลล B1 เทากบ 1 ใหแสดงผลลพธ

วา “มกราคม” ถาคาในเซลล B1 เทากบ 2 ใหแสดงผลลพธวา “กมภาพนธ” ถาคาในเซลล B1 เทากบ 3 ใหแสดงผลลพธวา “มนาคม” โดยใชฟงกชน INDEX เขามาชวย โดยรปแบบของฟงกชน INDEX คอ =INDEX(ชวงขอมลท6งหมด, เซลล Link) ดงน6นในตวอยางน6สามารถใชฟงกชน INDEX เขามาชวยดงตวอยางน6 ท�เซลล C1 พมพ = INDEX(A2:A4, B1) ผลลพธท�ไดจะเปนดงรป

การออกแบบ Scroll Bar

กอนการสรางแบบ Form โดยใช Scroll Bar ผเขยนขอกลาวถงคณสมบตของ Scroll Bar ส6นๆ วา Scroll Bar น6นใชสาหรบกาหนดคาของชวงตวเลขเพ�อใหผใชงานคลกเลอกไดอยางสะดวก ซ�งการออกแบบ Scroll Bar สามารถทาไดดงน6

1. คลกเลอกเคร�องมอ Scroll Bar 2. นาไปวาดตรงตาแหนงท�ตองการ (สามารถวาดในแนวนอน หรอแนวต6งกได)

3. คลกขวาท� Scroll Bar ท�วาดไว แลวเลอกแทบยอย Format Control…

การออกแบบดวยเคร�องมอ List Box จะมลกษณะเชนเดยวกบเคร�องมอ Combo Box คอ

ไวสาหรบเลอกรายการ ซ�งขอแตกตางของเคร�องมอท6ง 2 คอ

• List Box: ขอมลสาหรบใหผใชเลอกจะแสดงใหเหนมากกวา 1 รายการ

• Combo Box: ขอมลสาหรบใหผใชเลอกจะแสดงใหแค 1 รายการ


99

4. เลอกบตรรายการ Control แลวกาหนดคาดงรป

• Current value: กาหนดคาตวเลขเร�มตน 0

• Minimum value: คาตวเลขนอยท�สด 0

• Maximum value: คาตวเลขมากท�สด 100

• Incremental change: เม�อคลกท� หรอ จะเพ�มคา/ลดคาคร6 งละ 1

• Page change: เม�อนาเมาสไปคลกบนพ6นท�ของ Scroll Bar จะเพ�มคาคร6งละ 10

• Cell link: เซลลท�แสดงผลลพธจากการเลอก Scroll Bar

5. ผลลพธท�ไดจากการออกแบบใน Cell link ของ Scroll Bar จะสงกลบคาของตวเลขตามชวงท�กาหนด

6. สามารถนาคาท�ไดจาก Cell link ไปคานวณตอเพ�อใหแสดงคาเปน % กสามารถทาได ดงรป

การออกแบบดวยเคร�องมอ Scroll Bar จะมลกษณะเชนเดยวกบเคร�องมอ Spinner คอ

ไวสาหรบกาหนดชวงตวเลข ซ�งขอแตกตางของเคร�องมอท6ง 2 คอ

• Scroll Bar: ม Page change

• Spinner: ไมม Page change


100

ตวอยางการประยกตใชเคร2องมอ Form

อธบายงานท2ตองการ

• สรปผลเลอกดเดอนท�สนใจ

• ใช Combo Box เลอกดขอมลแตละเดอน

• ใชฟงกชน INDEX แสดงขอมลจากเดอนท�เลอก

• นาขอมลท�เลอกไปสรางกราฟ ขEนตอนการสราง

1. เรยกเคร�องมอ Form ข6นมา (เลอกแทบ View, Toolbars, Forms) 2. เลอกเคร�องมอ Combo Box แลวนามาวาดลงบนชต 3. คลกขวาท� Combo Box เลอก Format Control กาหนดคาตามรป เสรจแลวคลกปม OK

4. คลกเซลล B2 แลวพมพ = INDEX(B5:B16,$A$2 เสรจแลว Copy สตรไปยงเซลล C2 และ D2 ตามลาดบ

5. นาขอมลในขอ 4 ไปสรางกราฟ

คานา

โปรแกรมในชด Microsoft Office แตละโปรแกรมกมความสามารถเพาะดานท�แตกตางกนออกไป

เชน Microsoft Word เกงทางดานประมวลผลคา Microsoft PowerPoint เกงทางดานงานนาเสนอ Microsoft Access เกงทางดานฐานขอมล และ Microsoft Excel เกงทางดานการคานวณ

โปรแกรม Microsoft Excel นบไดวาเปนโปรแกรมท�มความสามารถอนหลากหลายไมวาจะเปนความสามารถทางดานการคานวณ การวเคราะหขอมล การสรางฟอรม อกท6งยงม Macro (VBA) เพ�อสราง Application บน Microsoft Excel ไดอยางงายดายอกดวย

เน6อหาในหนงสอเลมน6 จะเปนการแนะนาการใชงาน Microsoft Excel ระดบกลาง (Intermediate Excel) โดยผเขยนใชโปรแกรม Microsoft Excel XP/2003 อธบายพรอมยกตวอยางแบบ Step by Step ประกอบดวยเน6อหา การคานวณดวยฟงกชน การบรหารขอมล การวเคราะหขอมล การปองกนขอมล การตดตามสตร และการใชเคร�องมอฟอรม ซ�งทานสามารถทาความเขาใจเน6อหาตางๆ ไดในเวลาอนรวดเรว และสามารถนาไปใชงานจรงไดอยางแนนอน

ผเขยนหวงวาเน6อหาตางๆ ท�ผเขยนนามาเสนอคงเปนประโยชนและเปนแนวทางสาหรบนาไปประยกตใชกบงานของทานไดเปนอยางด และหากเน6อหามขอผดพลาดหรอมความบกพรองประการใด ผเขยนขอนอมรบไวเพ�อปรบปรงตอไป

(ผเขยนขอขอบพระคณ อาจารยบญเลศ อรณพบลย, อาจารยวโรจน ทวชยนกลกจ ผซ� งใหความรแกผเขยน และใหคาปรกษาท�ดตลอดมา)

สมเกยรต ชวยมาก 1 กรกฎาคม 2554

สารบญ

หลกการคานวณใน Microsoft Excel ............................................................................................................ 1

การคานวณใน Excel ................................................................................................................................ 1

ตวดาเนนการทางดานคานวณ (Operator) ............................................................................................ 1

ตวดาเนนการคณตศาสตร (Arithmetic Operator) ........................................................................... 1

ลาดบความสาคญของเคร�องหมายคานวณ ............................................................................ 1

ตวดาเนนการเปรยบเทยบ (Comparison Operator) ......................................................................... 2

ตวดาเนนการเช�อมขอความ (Concatenate Operator) ..................................................................... 2

วธการคานวณหาผลลพธ ................................................................................................................. 2

การอางองตาแหนงเซลล ........................................................................................................................ 4

การอางองแบบสมพนธ (Relative References) ............................................................................... 4

การอางองแบบสมบรณ (Absolute References) .............................................................................. 5

การอางองแบบผสม (Relative & Absolute References) .................................................................. 6

การต6งช�อเซลล/กลมเซลล (Range Name) ............................................................................................ 7

การต6งช�อกลมเซลลแบบท� 1 (ต6งช�อตามหวตาราง) .................................................................... 7

การต6งช�อกลมเซลลแบบท� 2 (แบบกาหนดเอง) .......................................................................... 8

การเรยกใชช�อกลมเซลล .................................................................................................................. 9

การลบช�อกลมเซลล .......................................................................................................................... 9

การคานวณดวยฟงกชน (Function) ..................................................................................................... 10

ทาความเขาใจกอนใชงานฟงกชน ......................................................................................... 10

รปแบบของฟงกชน (Function) ...................................................................................................... 11

ทาความเขาใจสวนขยายในฟงกชน (Argument) ........................................................................ 11

ฟงกชนท�นยมใชงาน ................................................................................................................................... 12

ตวอยางการใชงานฟงกชน ............................................................................................................ 13

จดกลมฟงกชนใหม ............................................................................................................................... 13

ฟงกชนเก�ยวกบตวเลข ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, MOD ................................................................................................................................................................ 14

ฟงกชน ABS (คาบวก) ........................................................................................................................... 14

ฟงกชน MOD (หาจานวนเศษท�เหลอจากการหาร) ......................................................................... 14

ฟงกชน INT (ตดเอาเฉพาะตวเลขจานวนเตม) ................................................................................. 14

ฟงกชน TRUNC (ตดเอาตวเลขตามตาแหนงท�ระบ) ....................................................................... 14

ฟงกชน ROUND (ปดตามหลก >=5 ปดข6น <5 ปดลง) ..................................................................... 15

ฟงกชน ROUNDUP (ปดข6น) ................................................................................................................ 15

ฟงกชน ROUNDDOWN (ปดลง) .......................................................................................................... 15

ฟงกชน CEILING (ปดข6นใหลงทายดวยตวเลขท�ตองการ) ............................................................ 15

ฟงกชน FLOOR (ปดลงใหลงทายดวยตวเลขท�ตองการ) ............................................................... 15

ฟงกชนเก�ยวกบขอความ เชน LOWER, UPPER, PROPER, RIGHT, LEFT, TEXT TO COLUMN, CONCATENATE ........................................................................................................................................... 16

ฟงกชนรวมขอความ (CONCATENATE) ........................................................................................... 16

ฟงกชนแบงขอความ (Text to Column) ............................................................................................... 16

ฟงกชน LOWER (ตวพมพเลก) ............................................................................................................ 16

ฟงกชน UPPER (ตวพมพใหญ) ........................................................................................................... 16

ฟงกชน PROPER (ข6นตนคาดวยตวพมพใหญ) ................................................................................ 16

ฟงกชน LEN (นบจานวนอกขระ) ....................................................................................................... 17

ฟงกชน TRIM (ใหเหลอ 1 เคาะวรรค) ............................................................................................... 17

ฟงกชน LEFT (ตองการขอความตามจานวนท�ระบ นบจากซาย) .............................................. 17

ฟงกชน RIGHT (ตองการขอความตามจานวนท�ระบ นบจากขวา) .............................................. 17

ฟงกชน MID (ตองการขอความตามจานวนท�ระบ นบจากตาแหนงท�ระบ) .............................. 17

ฟงกชนเก�ยวกบวนท� Date, Year, Month, Day, DateDif ............................................................................ 18

ฟงกชน YEAR (แสดงป) ....................................................................................................................... 18

ฟงกชน MONTH (แสดงเดอน) ............................................................................................................ 18

ฟงกชน DAY (แสดงวน) ...................................................................................................................... 18

ฟงกชน DATE (แปลงขอมลใหเปนวนท� ป, เดอน, วน) ................................................................. 18

ฟงกชน DATEDIF (คานวณอายแบบ ป, เดอนวน) .......................................................................... 18

รปแบบของ IF, OR, AND, COUNTIF, SUMIF .......................................................................................... 19

ฟงกชน IF 2 คาตอบ ............................................................................................................................. 19

ฟงกชน IF มากกวา 2 คาตอบ ............................................................................................................. 19

ฟงกชน OR .............................................................................................................................................. 19

ฟงกชน AND ........................................................................................................................................... 19

ฟงกชน IF ซอน OR ............................................................................................................................... 20

ฟงกชน IF ซอน AND ............................................................................................................................ 20

ฟงกชน COUNTIF .................................................................................................................................. 20

ฟงกชน SUMIF ....................................................................................................................................... 20

ฟงกชน AVERAGEIF ............................................................................................................................. 20

ฟงกชนคนหาขอมล VLOOKUP, MATCH, INDEX ................................................................................. 21

ฟงกชน VLOOKUP ................................................................................................................................ 21

ฟงกชน HLOOKUP ................................................................................................................................ 21

ฟงกชน ISNA .......................................................................................................................................... 21

ฟงกชน MATCH ..................................................................................................................................... 21

ฟงกชน INDEX ....................................................................................................................................... 21

ตวอยางการใชฟงกชน .......................................................................................................................... 22

ฟงกชน IF ................................................................................................................................................ 22

รปแบบฟงกชน IF ..................................................................................................................... 22

ฟงกชน IF ซอน IF ................................................................................................................................. 24

ฟงกชน MAXIF/MINIF .......................................................................................................................... 25

ฟงกชน COUNT ...................................................................................................................................... 26

ฟงกชน COUNTA ................................................................................................................................... 26

ฟงกชน COUNTBLANK ........................................................................................................................ 26

ฟงกชน COUNTIF .................................................................................................................................. 28

ฟงกชน SUMIF ................................................................................................................................. 29

ฟงกชน AVERAGEIF ....................................................................................................................... 30

ฟงกชนปดเศษ ........................................................................................................................................ 31

ฟงกชน ROUND ............................................................................................................................... 31

ฟงกชน INT ....................................................................................................................................... 32

ฟงกชน TRUNC ................................................................................................................................ 32

ฟงกชนคนหาขอมล .............................................................................................................................. 33

ฟงกชน VLOOKUP .......................................................................................................................... 33

ตวอยางการใชงานฟงกชน VLOOKUP ........................................................................................ 37

ฟงกชน ISERROR ............................................................................................................................ 38

ฟงกชน IFISERROR ......................................................................................................................... 39

ฟงกชนเก�ยวกบขอความ ...................................................................................................................... 40

ฟงกชน LEN ...................................................................................................................................... 40

ฟงกชน UPPER ................................................................................................................................. 40

ฟงกชน LOWER ............................................................................................................................... 41

ฟงกชน PROPER .............................................................................................................................. 41

ฟงกชน LEFT .................................................................................................................................... 42

ฟงกชน CONCATENATE ...................................................................................................................... 43

Data Text to Column ................................................................................................................................. 44

ข6นตอนการใชงาน Text to Column ................................................................................................ 44

Data Validation ............................................................................................................................................... 46

สรางรายการดวย Dropdown List .................................................................................................... 48

Data Validation แบบ Custom ........................................................................................................... 49

Conditional Formatting ................................................................................................................................... 50

การวเคราะหขอมลดวย What-If Analysis ................................................................................................. 51

การวเคราะหหาเปาหมาย (Goal Seek) ................................................................................................ 51

ตวอยางการวเคราะหหาเปาหมาย ................................................................................................ 51

การวเคราะหขอมลเชงตาราง (Data Table) ......................................................................................... 53

ตวอยางการวเคราะหขอมลเชงตารางแบบทางเดยว ................................................................ 53

ตวอยางการวเคราะหขอมลเชงตารางแบบ 2 ทาง ..................................................................... 55

การวเคราะหสถานการณสมมต (Scenarios) ...................................................................................... 57

การวเคราะหขอมลดวย Analysis ToolPak ................................................................................................ 60

กราฟ (Chart) .................................................................................................................................................. 61

สวนประกอบของกราฟ ....................................................................................................................... 61

ชนดของกราฟ ........................................................................................................................................ 62

ข6นตอนการสรางกราฟ ......................................................................................................................... 64

เคร�องมอ Chart Tools .............................................................................................................................. 64

เคร�องมอในกลม Design ................................................................................................................. 65

เคร�องมอในกลม Layout ................................................................................................................. 65

เคร�องมอในกลม Format ................................................................................................................. 65

วธการเพ�มแกน Y ท�สอง (แกน Y สองแกน) .............................................................................. 65

วธการเพ�มขอมลท�แกน X (Horizontal Axis Label) ...................................................................... 66

ฐานขอมล (Database) ................................................................................................................................... 67

ฐานขอมล (Database) คออะไร ............................................................................................................. 67

ทาความเขาใจฐานขอมล ................................................................................................................ 67

ฐานขอมลท�ดควรเปนอยางไร............................................................................................................. 67

ขอดของการจดเกบขอมลในรปแบบฐานขอมล ....................................................................... 68

Pivot Table ...................................................................................................................................................... 69

Pivot Table คอ อะไร .............................................................................................................................. 69

ความสามารถของ Pivot Table .............................................................................................................. 69

แนวคดของ Pivot Table ......................................................................................................................... 69

ข6นตอนการทา Pivot Table .................................................................................................................... 70

เคร�องมอ PivotTable Tools ..................................................................................................................... 71

เคร�องมอ Options .............................................................................................................................. 71

เคร�องมอ Design ............................................................................................................................... 71

การกรองขอมลดวย AutoFilter ............................................................................................................. 72

การกรองขอมลดวย Advanced Filter ................................................................................................... 73

การจดเรยงขอมล (Sorting) ................................................................................................................... 74

การจดกลมและผลรวมยอย (Group & Sub Totals) ............................................................................ 75

การรวมขอมล (Consolidate) .................................................................................................................. 76

F5 -> Special ชวยเลอกขอมล ...................................................................................................................... 77

การใชงานอ�นๆ ............................................................................................................................................. 79

การตดตามสตร ............................................................................................................................................ 79

Trace Dependents ........................................................................................................................... 80

Remove Dependent Arrows ............................................................................................................. 81

Trace Precedents ............................................................................................................................. 82

Remove Precedent Arrows ............................................................................................................... 83

Trace Error ......................................................................................................................................... 84

Formula Auditing Mode ........................................................................................................................... 85

Formula Auditing Mode ........................................................................................................................... 85

Comment ............................................................................................................................................ 85

การปองกนขอมลใน Excel ......................................................................................................................... 86

การปองกนขอมลใน Excel แบงเปน 4 ระดบคอ ............................................................................. 86

การปองกนระดบไฟล .......................................................................................................................... 86

การปองกนระดบไฟล เปนการปองกนตอนเปดไฟล โดยหากไมทราบ Password กไมสามารถเปดไฟล หรอแกไขไฟลได ข6นตอนการปองกนระดบไฟลทาไดดงน6 ............................................. 86

การปองกนระดบ WorkBook ................................................................................................................ 87

การปองกนระดบ WorkBook เปนการปองกนส�งท�อยใน WorkBook เชน หามเปล�ยนช�อ WorkSheet หามยอหรอขยายหนาตาง ข6นตอนการปองกนระดบ WorkBook ทาไดดงน6 ................ 87

การปองกนระดบ WorkSheet................................................................................................................ 88

การปองกนระดบ WorkSheet เปนการปองกนส�งท�อยใน WorkSheet เชน หามแกไขเซลล, หามแทรกแถวหรอคอลมน สามารถทาไดตามข6นตอนตอไปน6 ................................................................ 88

การปองกนระดบ Cell ........................................................................................................................... 88

หากตองการใหผใชงานกรอกขอมลเฉพาะบางเซลล (ยกเลกการ Lock เฉพาะบางเซลล) สามารถทาไดดงตอไปน6 ........................................................................................................................................... 88

การ Share Workbook ..................................................................................................................................... 90

การเปดการ Share Workbook ................................................................................................................. 90

เปล�ยนแปลง/แกไข Workbook ............................................................................................................. 91

การตดตามการเปล�ยนแปลง (Track Changes) ................................................................................... 92

Accept or Reject Changes ................................................................................................................... 92

Highlight Changes .............................................................................................................................. 93

Form Toolbars ................................................................................................................................................. 94

แนะนาเคร�องมอ Form .......................................................................................................................... 94

โหมดการใชงานของเคร�องมอ Form ................................................................................................. 94

การออกแบบเคร�องมอ Form ................................................................................................................ 95

การออกแบบ Check Box ................................................................................................................. 95

การออกแบบ Option Button ............................................................................................................ 96

การออกแบบ Combo Box ................................................................................................................ 97

การออกแบบ Scroll Bar ................................................................................................................... 98

ตวอยางการประยกตใชเคร�องมอ Form ............................................................................................. 99

ตวอยางการประยกตใชเคร�องมอ Form ........................................................................................... 100

สมเกยรต ชวยมาก

แบ่งปันแนวทางการประยุกต์ใช้ Excel...

Education

Transcript of แบ่งปันแนวทางการประยุกต์ใช้ Excel...