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