54
Microsoft Excel ครั้งที่ 2
Microsoft Excel ครงท 2
Outline
• การค านวนโดยใชสตร • การค านวนโดยใชฟงกชน
• ขอผดพลาดทเกดจากการค านวณ
• การตรงแนวและการวางแบบพเศษ
• การสรางกราฟ
2
3
การค านวณใน Microsoft Excel
1. การค านวณโดยใชสตร (Formula)
2. การค านวณโดยใชฟงกชน(Function)
4
ฟงกชน คอ สตรส าเรจรปทโปรแกรม
Microsoft Excel สรางไวใชส าหรบการค านวณ
เพอชวยใหผใชท างานไดสะดวกและรวดเรว
การค านวณโดยใชฟงกชน
5
โครงสรางของฟงกชน
ฟงกชนมสวนประกอบ 3 สวน คอ
1. ชอของฟงกชน เชน SUM, AVERAGE, SQRT
2. วงเลบ ( ) ในทกฟงกชนตองมวงเลบตอทาย
เชน SUM ( ), AVERAGE( ), SQRT( )
6
3. อารกวเมนต คอ สวนของขอมลทฟงกชนจะน าไปใชในการประมวลผล
แตละฟงกชนจะตองการอารกวเมนตทแตกตางกน
เชน SUM(A1:A20) , ROUND(A1,0)
โครงสรางของฟงกชน (ตอ)
7
ตวอยางฟงกชน
ฟงกชนทางการเงน (Financial)
ฟงกชนแบบตรรก (Logical)
ฟงกชนขอความ (Text)
ฟงกชนเกยวกบวนทและเวลา (Date & Time)
ฟงกชนทางคณตศาสตรและตรโกณมต (Math & Trig)
ฟงกชนทางสถต (Statistics)
8
ตวอยางการใชงานฟงกชน
9
ฟงกชน IF
ใหคาผลลพธตามเงอนไขวาเปนจรง หรอเทจ รปแบบ IF(เงอนไข,ผลลพธทไดถาเปนจรง,ผลลพธทไดถาเปนเทจ) ตวอยาง
=IF(b5=10, “TRUE”, “FALSE”) =IF(A1>A3, “0”, “1”) =IF(A1>A3, 0, 1)
10
ฟงกชน AND
ตรวจสอบวาทกอารกวเมนตเปนจรงหรอไม เปนจรงเมอทกเงอนไขเปนจรง รปแบบ AND(เงอนไข1, เงอนไข2,…) ตวอยาง
=AND(B5=10, A3 =5) =AND(A1>A3,A1<A4,A1>=10)
11
ฟงกชน OR
ตรวจสอบวามอารกวเมนตทเปนจรงบางหรอไม เปนจรงเมอมเงอนไขเปนจรงอยางนอย 1 เงอนไข รปแบบ OR(เงอนไข1, เงอนไข2,…) ตวอยาง
=OR(B5=10, A3 =5) =OR(A1>A3,A1<A4,A1>=10)
12
ฟงกชน IF , AND , OR
=IF(b5=10, “TRUE”, “FALSE”)
=IF(AND(b5=10 , c5>=2000) , “TRUE”, “FALSE”)
=IF(A1>A3, 0, 1)
=IF(AND(A1>A3 , B1>B3) , C1*2 , C1*5)
=IF(d4=‘f’, “หญง”, “ชาย”)
=IF(OR(d4=‘f’ , d4=‘F’) , “หญง”, “ชาย”)
13
ฟงกชน COUNT
นบจ านวนเซลลในชวงทมตวเลข รปแบบ COUNT(ชวงของขอมล) ตวอยาง
=COUNT(A1:A5) =COUNT(A1,A3)
14
ฟงกชน ROUND
• ปดเศษจ านวนใหเปนจ านวนทมต าแหนงทศนยมตามก าหนด รปแบบ ROUND (ขอมล,จ านวนหลกทศนยม) ตวอยาง
=ROUND(12.1234 , 2) =ROUND(12.1289,2) =ROUND(A1,3)
15
ฟงกชน ROUNDUP
• ปดเศษจ านวนขน รปแบบ ROUNDUP (ขอมล,จ านวนหลกทศนยม) ตวอยาง
=ROUNDUP(12.1234 , 2) =ROUNDUP(12.1289,2) =ROUNDUP(A1,3)
16
ฟงกชน ROUNDDOWN
• ปดเศษจ านวนลง รปแบบ ROUNDDOWN (ขอมล,จ านวนหลกทศนยม) ตวอยาง
=ROUNDDOWN(12.1234 , 2) =ROUNDDOWN(12.1289,2) =ROUNDDOWN(A1,3)
17
ขอผดพลาดทเกดจากการค านวณ
#### เกดจากตวเลขในเซลลยาวกวาขนาดของเซลล #VALUE! เกดจากการใชสตรผดไวยากรณ เชน น าตวเลขไปบวกกบตวอกษร #NAME? สตรไมสามารถบอกไดวาคออะไร เชน A1+แมว โดยค าวาแมวไมไดเกยวของใน Sheet นน
18
#N/A เกดขนเมอกรอกตวแปรผดประเภท เชน ฟงกชนตองการขอมลเซลลเดยว แตเราใสไปหลาย ๆ เซลล #REF! เกดขนเมอโปรแกรมไมสามารถคนหา ต าแหนงอางองเซลลทใชสตรได มกพบใน การอางองเซลลขาม Sheet หรอสมดงาน
ขอผดพลาดทเกดจากการค านวณ (ตอ)
19
#NULL เกดขนกรณทก าหนดพนทเซลลสองเซลล โดยไมม สวนใดตอกน แตลมแยกเซลล ดวยเครองหมาย เชน SUM(A1:B2 C2:D5) ทถกตองเขยนเปน SUM(A1:B2,C2:D5)
ขอผดพลาดทเกดจากการค านวณ (ตอ)
20
IF() = ใหคาผลลพธตามเงอนไขวาเปนจรง
หรอเทจ
COUNTIF() = การนบคาเมอเปนไปตามเงอนไขท
ก าหนด
ฟงกชนทางตรรกศาสตร
21
ตวอยางการใชงานฟงกชน
22
ฟงกชน IF
ใหคาผลลพธตามเงอนไขวาเปนจรง หรอเทจ รปแบบ IF(เงอนไข,ผลลพธทไดถาเปนจรง,ผลลพธทไดถาเปนเทจ) ตวอยาง
=IF(b5=10, “TRUE”, “FALSE”) =IF(A1>A3, “0”, “1”) =IF(A1>A3, 0, 1)
23
ฟงกชน IF แบบ Nested if
ในการทดสอบเงอนไขนนสามารถทดสอบเงอนไขไดมากกวา 1 เงอนไขได ซงเรยกวธการนวา Nested if รปแบบ IF(เงอนไข,ผลลพธทไดถาเปนจรง, IF (เงอนไข, ผลลพธทไดถาเปนจรง, IF(เงอนไข , ผลลพธทไดถาเปนจรง, ผลลพธทไดถาเปนเทจ)))
24
ฟงกชน IF แบบ Nested if
ตวอยางเชน =IF(OR((A2="m"),( A2= "M")), "ชาย",( IF(OR((A2="f" ),( A2="F")) , "หญง", "ไมระบ"))) ผลลพธการท างาน
25
ฟงกชน IF แบบ Nested if
ตวอยางเชน =IF(E3="m","ชาย",IF(E3="f","หญง"," ไมระบ")) ผลลพธการท างาน
26
ฟงกชน IF แบบ Nested if
จากตวอยาง สตรท 1 =IF(OR((A2="m"),( A2= "M")), "ชาย",( IF(OR((A2="f" ),( A2="F")) , "หญง", "ไมระบ"))) สตรท 2 =IF(E3="m","ชาย",IF(E3="f","หญง"," ไมระบ")) ผลลพธการท างาน ของสตรท 1 และสตรท 2 จะใหผลลพธทเหมอนกนเนองจาก Excel 2010 ไดมการพฒนาความสามารถเพมขนมา
27
ตวอยางฟงกชน IF แบบ Nested if
ตวอยางการค านวณอตราดอกเบย ถาตองการค านวณดอกเบยแบบอตรากาวหนา ตามระยะเวลาในอตราดงทก าหนด ระยะเวลา นอยกวาหรอเทากบ 3 เดอน คดอตรา 1% นอยกวาหรอเทากบ 6 เดอน คดอตรา 1.5 % มากกวา 6 คดอตรา 1.85 %
28
ตวอยางฟงกชน IF แบบ Nested if
สตรการค านวณอตราดอกเบย คอ =IF(F12<=3,G12*0.01,IF(F12<=6,G12*0.015,G12*0.0185))
29
ตวอยางการใชฟงกชน SUM รวมกบ IF แบบ Nested if
รวมเงนพเศษตามเงอนไข =SUM(IF(A2=“f”,1000,500),IF(B2>=65,600,IF(B2>=60,500,0)))
30
ฟงกชน COUNTIF
นบจ านวนเซลลทมขอมลตรงกบเงอนไขทก าหนด รปแบบ COUNTIF(ชวงของขอมล, “เงอนไขตรวจสอบ”) ตวอยาง
=COUNTIF(A1:A5, “>=25”) =COUNTIF(A1:A3, “สทธสาร”)
31
ฟงกชน COUNTIF
ตวอยางการใชฟงกชน COUNTIF ถาตองการนบจ านวนผชายทงหมด เราสามารถเขยนสตรใน cell F8 ไดดงน =COUNTIF(E$3:E$7,"m")
32
ฟงกชน COUNTIF
ตวอยางการใชฟงกชน COUNTIF =COUNTIF(E$3:E$7,"m")
=COUNTIF(E$3:E$7,“f")
33
ฟงกชน COUNTIF
ตวอยางท 2 ฟงกชน COUNTIF ถาตองการนบจ านวนคนทไดดอกเบยตามอตราตางๆ สามารถเขยนสตรค านวณไดดงน
34
ฟงกชน COUNTIF
ตวอยางท 2 ฟงกชน COUNTIF
35
ฟงกชน COUNTIF
ตวอยางท 2 ฟงกชน COUNTIF
36
ฟงกชน COUNTIF
ตวอยางท 2 ฟงกชน COUNTIF
37
ฟงกชน SUMIF
รวมคาเมอเซลลทระบมขอมลตรงกบเงอนไขทก าหนด รปแบบ SUMIF(ชวงขอมลตรวจสอบ, “เงอนไขตรวจสอบ”,[ชวงขอมลทน ามารวม]) ตวอยาง
=SUMIF(A1:A5, “>=1000”) =SUMIF(C1:C5, “=f”,A1:A5)
38
ตวอยางการใชฟงกชน SUMIF
รวมเงนพเศษทไดมากกวา 500 =SUMIF(E2:E5, “>500”)
39
ตวอยางการใชฟงกชน SUMIF
รวมอายของพนง.ผหญง =SUMIF(A2:A5, “=f”,B2:B5)
40
ตวอยางการใชฟงกชน SUMIF
หาอายเฉลยของพนง.ผหญง =SUMIF(A2:A5, “=f”,B2:B5)/COUNTIF(A2:A5, “=f”)
การวางแบบพเศษ (Paste Special)
41
การวางแบบพเศษสามารถเลอกไดวาตองการเพยงผลลพธในรปแบบใดบางทจะวางมายงต าแหนงทตองการ
• ทงหมด (All) : คดลอกคณสมบตจากตนฉบบมาทงหมด • สตร (Formulas) : คดลอกเฉพาะสตร • คา (Values) : คดลอกเฉพาะคาของขอมลโดยไมคดลอกสตรมาดวย • รปแบบ (Format) : คดลอกเฉพาะรปแบบเซลล
• เพอตรงแถว หรอคอลมนใหแสดงผลคงทเพองายตอการท างานกบขอมลจ านวนมาก ท าได 3 แบบคอ
• ตรงแนว (Freeze Panes) คอ การตรงแนวแถวบน และคอลมนทางซายมอของเซลทเลอกไว
• ตรงแถวบนสด (Freeze Top Row) คอ การตรงแถวบนสด แถวท 1 ของ Worksheet
• ตรงคอลมนแรก (Freeze Fisrt Column) คอ การตรงคอลมนแรก หรอคอลมน A ของ Worksheet
42
การตรง Worksheet (Freeze Panes)
การตรง worksheet
43
1. เลอกแทบมมมอง (View) 2. เลอกเครองมอตรงแนว (Freeze Panes)
ยกเลกการตรง worksheet
44
1. เลอกแทบมมมอง (View) 2. เลอกเครองมอตรงแนว (Freeze Panes) 3. เลอกยกเลกตรงแนว (Unfreeze Panes)
45
การจดแตงตวเลข, วนท, เวลา
• เลอกค าสงจาก Home tab Number
เลอกรปแบบตวเลข
ใส , เพอคนตวเลขหลกพน
เพม/ลด ต าแหนงทศนยม
46
การจดแตงตวเลข, วนท, เวลา (ตอ)
• หรอ คลกขวาทเซลลทตองการ เลอกจดรปแบบเซลล (Format cells)
กลมของรปแบบ
รปแบบของตวเลข ในกรณทเปนคาลบ
จ านวนจดทศนยม
47
การจดต าแหนงขอมลในเซลล
เลอกค าสงจากแทบ Home (หนาแรก) > กลมการจดแนว
1. เลอนตวชต าแหนงเซลลไปยงต าแหนงเซลลทตองการจดแตง
2.
ตดขอความ
กดเลอกเพอจดขอความใหอยในแนวทแยงหรอแนวอนๆ
48
การจดต าแหนงขอมลในเซลล (ตอ)
ต าแหนงการจดวางขอมลในแนวนอน - ซาย/กลาง/ขวา ต าแหนงการจดวางขอมลในแนวตง - บน/กลาง/ลาง
ทศของการวางขอมล แบบนอน หรอ แบบเฉยง หรอ แบบตง
49
ขยาย-ลดความกวางของคอลมน/แถว
• กดปมของเมาสคางไว แลวลากเมาสโดย • ดงเมาสออก จะเปนการขยายออก
• ดงเมาสเขา จะเปนการลดความกวาง
50
สรางแผนภม
1. ก าหนดชวงขอมลทตองการสรางกราฟ (กรณเลอกขอมลบางสวนไมตอเนองกนมาท ากราฟ ใหกด ctrl+ป มซายบนเมาส)
2. เลอกเมนค าสง แทรก (Insert)
3. เลอกรปแบบ
กราฟทตองการ
51
สรางแผนภม
52
การแกไขกราฟ
• คลกทกราฟ > เลอกแทบ Design (ออกแบบ) • เลอกลกษณะแผนภม (Chart Styles) เพอก าหนดรปราง ส ลกษณะของกราฟ
53
เลอกขอมลทตองการน ามา ก าหนดเปนแกน X, Y ดผลจากภาพตวอยาง
การแกไขกราฟ
• คลกทกราฟ > เลอกแทบ Design (ออกแบบ) • เลอกสลบแถว/คอลมน (Switch Row/Column)
54
การแกไขกราฟ
• เลอกเคาโครงแผนภม (Chart Layouts) เพอก าหนดรายละเอยดในการแสดงขอมล เชน แสดงชอแผนภม ชอแกน X ชอแกน Y
