Microsoft Excel - @@ Home - KKU Web Hosting 2007 Adv Manual for KKU... · Review Microsoft Excel...

1 | P a g e

Microsoft Excel Advanced Level

สารบญ

บทท 1: การค านวณและการใชฟงกชน .................................................................................... 1

Review Microsoft Excel ..................................................................................................... 1

เทคนคการเรยกใชโปรแกรม ................................................................................................ 1

สรปคณสมบตใหม ของ Microsoft Excel 2007 .................................................................... 2

การซอน/แสดง สวนประกอบยอยตางๆของ Status Bar ....................................................... 4

รจกกบมมมองตางๆ ใน Microsoft Excel 2007 ................................................................... 4

การตกแตงขอมลในเซล ....................................................................................................... 6

การระบายสชองเซล ............................................................................................................ 6

เพมเวรกชท (Worksheet) แผนใหม ..................................................................................... 7

การเกบบนทกสมดงานเปนชอไฟลอนหรอไดรฟอน .............................................................. 7

การใสสตรค านวณปกต ...................................................................................................... 11

ล าดบความส าคญของเครองหมายทางการค านวณ ............................................................. 12

การสรางสตรอยางงายพรอมคาคงท ................................................................................... 12

การค านวณหาผลรวม ........................................................................................................ 13

การสรางสตรดวยฟงกชนทใชงานบอย ............................................................................... 13

เทคนคการค านวณแบบไมตองเขยนสตร ............................................................................ 14

Workshop1 ....................................................................................................................... 15

Workshop2 ....................................................................................................................... 16

การใช IF function ............................................................................................................. 18

ฟงกชนการคนหาและอางอง (Lookup and Reference) ..................................................... 19

Workshop-Vlookup .......................................................................................................... 20

การใช SUMIF ................................................................................................................... 21

การใช COUNTIF .............................................................................................................. 21

แปลงอกษรใหเปนตวพมพใหญ .......................................................................................... 22

Workshop 4 ..................................................................................................................... 22

บทท2. การท างานกบขอมล .................................................................................................. 25

การจดรปแบบขอมลในแผนงาน ......................................................................................... 25

การก าหนด Custom Formatting ....................................................................................... 26

การจดรปแบบแบบมเงอนไข Conditional Formatting ........................................................ 28

การเปลยนเงอนไขหรอรปแบบทก าหนด ............................................................................ 30

การลบรปแบบขอมลทก าหนดเงอนไข ................................................................................ 31

การคนหาเซลลทมการจดรปแบบตามเงอนไข .................................................................... 31

สรปเรองการปองกนขอมล Protection ............................................................................... 34

บทท 3. Working with Data Analysis ................................................................................... 39

รจกกบฐานขอมลใน Microsoft Excel................................................................................. 39

Data Validation ................................................................................................................ 42

Pivot Table....................................................................................................................... 45

การปรบแตงกราฟ ............................................................................................................. 48

การจดกรองและจดเรยงขอมล ............................................................................................ 49

การกรองขอมลแบบอตโนมต (AutoFilter) .......................................................................... 52

Workshop5 ....................................................................................................................... 58

บทท 4. การสราง Macro ...................................................................................................... 60

Recording Macro ............................................................................................................. 60

Running Macro ................................................................................................................ 64

Macro Button ................................................................................................................... 64

Workshop-Macro ............................................................................................................. 65

บทท 5. Collaborating with others ....................................................................................... 66

การใชง านไฟลรวมกน (Shared Workbook) ..................................................................... 66

ขอจ ากดทควรทราบ ภายหลงการ share file ...................................................................... 67

Tracking Changes: การตดตามการแกไข ......................................................................... 68

Excel 2007 New Feature .................................................................................................... 72

แบบฝกหดทายบท ................................................................................................................ 83

Excel Short Cut ................................................................................................................... 85

[email protected] Page | 1

บทท 1: การค านวณและการใชฟงกชน

Review Microsoft Excel ส าหรบบทนจะเปนการแนะน าการเรมตนใชงานโปรแกรม การสรางสมดงาน รวมทงเครองมอใชงานท

ส าคญ เพอเปนพนฐานการใชงานส าหรบงานสมดงานตางๆ

เทคนคการเรยกใชโปรแกรม เพอใหท าการเปดโปรแกรมไดเรวขน ใหคลกปม Start พมพชอโปรแกรมลงในชอง Search เชน

Excel แลวกดปม Enter โปรแกรมทเรยกใชกจะถกเปดขนทนท

สวนประกอบหนาตางโปรแกรม

Formula Bar (แถบสตร)

Ribbon (รบบอน) แทบ (Tab)

พนทใชงาน Work sheet

แถบเครองมอ Quick Access Tool bar


Office Button เปนปมค าสงเกยวกบการท างานของแฟมงาน เชน New, Open, Save, Save

As, Print, Close, ฯลฯ

Quick Access Toolbar เปนแถบเครองมอใหเรยกใชงานไดอยางรวดเรว ผใชสามารถเพม

ปมค าสงทใชงานบอยๆ ไวในแถบเครองมอนได

Title bar แถบแสดงชอโปรแกรมและชอไฟลปจจบนทเปดใชงานอย

แถบ Ribbon เปนแถบทรวบรวมค าสงตางๆ ของเมนหรอทลบาร เพอใหผใชเลอกใชงานงาย

ขน

Formula bar แถบแสดงสตรค านวณหรอขอมลทพมพ

Active cell ต าแหนงปจจบนทก าลงท างานอย

Worksheet พนทใชงานของโปรแกรม

Status bar แถบแสดงสถานะการท างานปจจบนบนหนาจอ

View bar แถบแสดงมมมองสมดงานในแบบตางๆ

สรปคณสมบตใหม ของ Microsoft Excel 2007 เมนค าสงรปแบบใหม เรยกวา Ribbon เปนการจดวางปมค าสงแยกเปนกลมตามการใชงานตางๆ โดย

แบงออกเปน 7 แทบ ไดแก Home, Insert, Page Layout, Formulas, Data, Review, View


เพมขดความสามารถดานตางๆ เพอใชในการจดการกบขอมลไดมากขน ดงน

1. ใช File Format แบบใหมทผสมผสาน XML กบ การบบอดขอมลแบบ Zip ซงท าใหไดขนาดแฟมทเลกลงเมอเทยบกบแฟมในเวอรชนกอนๆ

2. สรางสตรไดงายขนกวาเดม ดวยคณสมบตของ Formula AutoComplete 3. เพมฟงกชนใหม ส าหรบชวยในงานท าผลสรปขอมล ดงน

ขดความสามารถดานตางๆ Excel 2003 Excel 2007 จ านวนคอลมนใน 1 Sheet 256 16,384 จ านวนแถวใน 1 Sheet 65,536 1,048,576 จ านวนสทใชไดทงหมดในแฟมงาน 56 ส 4,300 ลานส เงอนไขของ Condition Format ทใชไดใน 1 ชองเซล

สงสด 3 เงอนไข ไมจ ากด (ขนกบหนวยความจ า)

ความสามารถในการเรยงล าดบขอมลใน 1 ครง สงสด 3 ระดบชน

สงสด 64 ระดบชน

จ านวนตวอกษรสงสดทบนทกไดใน 1 ชองเซล 1,024 ตวอกษร 32,768 ตวอกษร สรางสตรทซอนกนได มากสด 7 ระดบชน 64 ระดบชน จ านวน Argument ของฟงกชน มไดมากสด 30 255

จ านวนคอลมนของ Pivot Table มไดมากสด 255 คอลมน 16,384 คอลมน

ชอฟงกชน การใชงาน AVERAGEIF ใชค านวณหาคาเฉลยแบบ 1 เงอนไข AVERAGEIFS ใชค านวณหาคาเฉลยแบบหลายเงอนไข SUMIFS ใชค านวณหาผลรวมแบบหลายเงอนไข COUNTIFS ใชนบแบบหลายเงอนไข IFERROR ใชแกไขผลลพธจากสตรทแสดงขอผดพลาด


4. ปรบปรงคณสมบตดาน Graphic สามารถปรบแตงรปภาพ หรอสรางรปวาดตางๆไดสวยงาม รวดเรวและงายขนรวมถง Chart ทมรปแบบใหมๆ ทสรางใหอยางสวยงามและปรบแตงไดงายกวาเดม

การซอน/แสดง สวนประกอบยอยตางๆของ Status Bar 1. คลกเมาสปมขวา ลงบน Status Bar 2. คลกเลอกชอของสวนประกอบทตองการเพมใน Status Bar โดยจะเหนเครองหมายถกแสดงท

ดานหนาชอนน หากตองการยกเลกสวนประกอบนนออก ใหคลกทชอนนจนเครองหมายถกดานหนาหายไป

รจกกบมมมองตางๆ ใน Microsoft Excel 2007 Microsoft Excel 2007 เพมรปแบบมมมองใหมเพอสะดวกกบการตรวจสอบเอกสารกอนพมพ และ

สามารถท างานในสมดงานไดพรอมกน โดยแตละมมมองจะมรปแบบ และการใชงานทแตกตางกน

ดงน

2

1


ลกษณะมมมองของ Excel แบงเปน 3 ลกษณะดวยกน คอ

1. มมมอง Normal เปนมมมองทใชดานการค านวณหรอการใชงานทวไป

2. มมมอง Page Layout View ใชตรวจสอบเอกสารกอนการพมพ แทนการใชค าสง Print Preview เดม

จะใชรวมกบแทบ Page Layout ในการจดหนาสมดงาน

3. มมมอง PageBreak Preview

ชวยในการจดหนา

สมดงานกอนการ

พมพ สะดวกในการ

ก าหนดขอบเขต

การพมพ


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

โดยมเครองมอดงตอไปน

การระบายสชองเซล

1.ใชเมาสลากคลมพนท

ทตองการระบายส

คลกเลอกรายการส

3

2.คลกแทบ Home

และคลก ปม Cell Styles


เพมเวรกชท (Worksheet) แผนใหม 1. คลกปม Insert Worksheet

Tip and Trick : สามารถใชคยบอรด

โดยกดปม Shift + F11

การตรวจสอบความเขากนไดของสมดงานตางเวอรชน (Run Compatibility

Checker) หากตองการน าสมดงานทสรางโดย Microsoft Excel 2007 ไปเปดใชโดย Microsoft Excel 97-2003

นน ควรตรวจสอบรปแบบตางๆ ทก าหนดในสมดงานวา มรายการใดบางทไมสามารถแกไขได

มขนตอนดงน

1. คลกปม Office Button 2. เลอก Prepare 3. คลกรายการ Run Compatibility Checker


4. ดรายงานขอมลรายละเอยด วาสวนใดบางใชงานไมได จากกรอบทแสดงขนมา และหากตองการไปยงต าแหนงทมปญหานนใหคลกท Find

5. หากตองการจบการใชงานใหคลกปม OK

1

2

3


TIPS AND TRICK

การเลอกบนทกเปน Version 97 -2003

จะปรากฏหนาตาง Microsoft Office Excel Compatibility Checker โดยอตโนมต

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

Save บน Quick Access Toolbar จะท าการบนทกลงแฟมงานเดมทนท ถาตองการ Save เปนแฟม

งานอนหรอไดรฟอน มข นตอนดงน

1. คลกปม Office Button 2. เลอกค าสง Save As 3. เลอก รปแบบการบนทก


4. จะปรากฏไดอะลอกบอกซของ Save As ใหก าหนดรายละเอยดตามตองการ

ปดไฟลสมดงาน

หากตองการเลกใชงาน หรอจบการท างานกบสมดงานใดๆ มข นตอนการปดไฟลสมดงานดงน

1. คลกปม Office Button 2. คลกปม Exit Excel

1

2

3

เปนการ Save ไฟล2007 นามสกล .xlsx

บนทกไฟลเพอใชกบ Excel 97-2003 ได

ความสามารถใหมในการ Save ไฟลเปน PDF

เปนการเปดไดอะลอกบอกซของ Save As ให

เลอก Save As Type ในรปแบบอนๆ


การใสสตรค านวณปกต

1. เลอกเซลลทตองการแสดงผลลพธ จากตวอยางน ต าแหนงเซลลอยท D4 2. พมพเครองหมาย = แลวตามดวยต าแหนงเซลล เชน =D2*D3 เสรจแลวกดปม Enter 3. ทเซลล D4 จะแสดงผลลพธ และท Formula bar จะแสดงสตรค านวณทก าหนดไว

Formula bar

1

2


Tips and Trick เทคนคการเลอกต าแหนงเซลลในสตรค านวณ พมพเครองหมาย = แลวคลกเลอกเซลลทตองการใช

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

เครองหมายทใช ความหมาย ( ) วงเลบ

^ ยกก าลง * , / คณ , หาร + , - บวก , ลบ

ความส าคญของเครองหมายทางการค านวณ จะไลจากบนลงลาง บนสดจะส าคญสงสด และลางสดจะส าคญต าสด ในสตรค านวณสตรหนง ถาเครองหมายใดส าคญกวากจะท าการค านวณเครองหมายนนกอนแลวไลตามล าดบไปเรอยๆ

การสรางสตรอยางงายพรอมคาคงท ในสตรค านวณบางครงตองการอางองคาต าแหนงเซลลเดยว เวลา copy สตร ไมตองการใหเปลยนแปลง เรยกวา การอางองแบบ Absolute ในตวอยางนตองการค านวณหาผลตางระหวางคาใชจาย (B3) กบคาเฉลย (C16) มขนตอนดงน 1. เลอกเซลลทตองการแสดงผลลพธ จากตวอยางน ต าแหนงเซลลอยท C3 2. ใสสตรค านวณ =B3-$C$16 เสรจแลวกดปม Enter


3. ท าการ copy สตรค านวณลงมา จะเหนวาต าแหนง C16 จะเปนคาคงทตลอด ไมมการเปลยนแปลง

Tips and Trick เทคนคการอางองต าแหนงเซลลในสตรค านวณ

การอางองสตรค านวณปกต เรยกวา การอางองแบบ Relative ซงต าแหนงเซลลจะเปลยนแปลงทกคา เวลา copy สตรค านวณ ตามตวอยางน

การอางองสตรค านวณแบบสมบรณ เรยกวา การอางองแบบ Absolute จะเปนการตรงคาใดคา

หนงเปนคาคงท ไมใหเปลยนแปลงเวลา copy สตรค านวณ ตามตวอยางน รปแบบของการใช Absolute =$column$row (เครองหมาย $ ในสตรค านวณ เรยกวาเครองหมาย string)

ขอผดพลาดทวไปเมอสรางสตร #VALUE! หมายถง ในสตรค านวณปกตมการอางองต าแหนงเซลลทเปนตวอกษร #NAME? หมายถง ชอฟงกชนทใชพมพผด

การค านวณหาผลรวม ถาตองการหาผลรวมของตวเลขหลายๆ ต าแหนง ใหใชฟงกชนของการหาผลรวมเขามาชวย มข นตอนดงน 1. เลอกเซลลทตองการแสดงผลลพธ จากตวอยางน ต าแหนงเซลลอยท H4 2. ทแทบ Home 3. จะปรากฏสตรค านวณ =SUM(B4:G4) ใหดชวงขอมลตวเลขทจะค านวณวาถกตองหรอไม

สงเกตจากเสนประวงรอบๆ ขอมล ถาถกตองแลวกดปม Enter

การสรางสตรดวยฟงกชนทใชงานบอย โปรแกรม Excel จะท าการแบงชดของสตรค านวณตามประเภทการใชงาน ถาตองการใชสตรค านวณประเภทใด คลกเลอกปมนนไดเลย หรอคลกปม Insert Function กได สตรค านวณทน ามายกตวอยาง


จะเปนสตรทใชงานบอยๆ มข นตอนดงน 1. เลอกเซลลทตองการแสดงผลลพธ จากตวอยางน ต าแหนงเซลลอยท C13 2. ทแทบ Formula จะแสดงประเภทของสตรค านวณใหเลอกใช ในทนคลกปมลกศรลงของ AutoSum

3. จะปรากฏสตรค านวณทใชงานบอยๆ ใหเลอก Average สตรการหาคาเฉลย Count Numbers สตรการนบจ านวนขอมล Max สตรการหาคาสงสด Min สตรการหาคาต าสด More Functions สตรอนๆ

เทคนคการค านวณแบบไมตองเขยนสตร ดผลการค านวณงาย ๆ โดยไมตองใสสตร (AutoCalculate)

1. คลกขวาทแถบแสดงสถานะ (Status Bar) ทอยดานลางของหนาตางโปรแกรม Excel

2. เลอกรายการสตรทตองการจากเมนลดดงภาพ

การใชงาน AutoCalculate

1. คลกเมาสลากคลมเซลทตองการเทานน

2. ผลลพธจากการค านวณตามโจทยททานก าหนดไวจะปรากฏใหเหนบนแถบสถานะ (Status Bar)

ทนท


Workshop1 เปดไฟล function.xlsx ท าการค านวณดงน เซล D3:D7 ใช if function แสดงผลสอบ คอ ผาน หรอ ไมผาน เซล D9:D16 ใช function ทเหมาะสม

ผลลพธทแสดงบนแถบ Status Bar


Workshop2 จากแบบฟอรม Quotation ทต าแหนง A21 ตองการใหแสดงขอความอานจ านวนเงนตามตวเลข ในชอง F21


เตรยมงาน 1. เปดไฟล Form.xlsx

ขนตอน 1. คลกเซล A21 2. พมพ = b 3. ปรากฏ เครองมอ complete formular เพอชวยในการพมพฟงกชน

4. ดบเบลคลกท BAHTTEXT


5. ปรากฏ ชอฟงกชนดงกลาวในชอง A21

6. คลกเซล F21 7. กดปม Enter

การใช IF function

=if(เงอนไข, จรง, เทจ)

สามารถซอนฟงกชนไดถง 64 ฟงกชน

ตวอยาง


ฟงกชนการคนหาและอางอง (Lookup and Reference) ฟงกชนการคนหาขอมลเปนกลมฟงกชนทเหมาะสมทสด ส าหรบการท างานทางดานฐานขอมล

โดยเฉพาะอยางยงงานทตองมการคยรายการซ าๆ กนดวยรหสเปนจ านวนมาก แลวตองการ คนหา

ขอมลทคยลงไป

ฟงกชนการคนหาขอมลเปนกลมฟงกชนทเหมาะสมทสด ส าหรบการท างานทางดานฐานขอมล โดยเฉพาะอยางยงงานทตองมการคยรายการซ าๆ

กนดวยรหสเปนจ านวนมาก แลวตองการ คนหาขอมลทคยลงไป

VLOOKUP

Lookup_value เปนคาทตองการคนหาในคอลมนแรกของตาราง

Table-array เปนตารางของขอความ ตวเลข หรอคาตรรกะ ทมขอมลทคณตองการ

Col_index_num เปนหมายเลขคอลมนใน Table _array ซงมคาภายในทตรงกนทจะสงกลบ โดยนบคอลมนซายสดในตารางเปนคอลมนท 1

Range_lookup เปนคาตรรกะ ซงถาตองการคนหาคาทใกลเคยงทสดในคอลมนซายสด (ขอมลตองเรยงล าดบจากนอยไปหามาก)

HLOOKUP

Lookup_value เปนคาทตองการคนหาในแถวแรกของตาราง

Table-array เปนตารางของขอความ ตวเลข หรอคาตรรกะ ทมขอมลทคณตองการ

Row_index_num เปนหมายเลขแถวใน Table _array ซงมคาภายในทตรงกนทจะสงกลบ โดยนบแถวแรกสดในตารางเปนแถวท 1

Range_lookup เปนคาตรรกะ ซงถาตองการคนหาคาทใกลเคยงทสดในแถวบนสด(ขอมลตองเรยงล าดบจากนอยไปหามาก)

ฟงกชนการคนหาและอางอง (Lookup and Reference)


Workshop-Vlookup ใชฟงกชน Vlookup เพอหาขอมลจากในตาราง แลวน ามาแสดงในแบบฟอรมใบเสรจ จากตาราง Product.xlsx เกบรายการสนคาทงหมดของบรษท

ตารางดานลางเปนแบบฟอรมใบเสนอราคา ทต าแหนง A10 เมอพมพรหสนคา เชน TB1 จะปรากฏรายการสนคา และ หนวยนบ พรอมกบ ราคาตอหนวยใหอตโนมต


เตรยมงาน 2. เปดไฟล Product.xlsx 3. เปดไฟล Form.xlsx

ขนตอน 1. คลกเซล B10 2. พมพ =Vlookup ปรากฏชอฟงกชน Vlookup() 3. ท าการดบเบลคลกทฟงกชน Vlookup() ทปรากฏ ฟงกชนดงกลาวจะปรากฏในเซล B10

ดงน =VLOOKUP(A10,………\[Products_ans.xlsx]Products'!$B$1:$J$46,2) 4. กดปม Enter เพอแสดงคา 5. ใชฟงกชนเดยวกนน ทต าแหนง C10 และ E10 ดงน C10 =VLOOKUP(A10,……..\[Products_ans.xlsx]Products'!$B$1:$J$46,5) E10 =VLOOKUP(A10,……..\[Products_ans.xlsx]Products'!$B$1:$J$46,4)

การใช SUMIF

SUMIF function: แสดงผล รวมตวเลขภายใน range ทระบเมอเงอนไขทก าหนดเปนจรง

รปแบบ =SUMIF( range, criteria, sum_range)

range คอชวงของ cells ทตองการน ามาเปนเงอนไข ดวยการเปรยบเทยบ

criteria คอเงอนไข ในรปแบบของการเปรยบเทยบ ซงสามารถก าหนดไดเพยง เงอนไขเดยวเทานน หากก าหนดโดยตรงภายใน function ตองก าหนดภายในเครองหมายค าพด (“ “)

sum_range คอชวงของ cells ทตองการค านวณผลรวม หากไมมการก าหนด จะน าเอา range มาท าการ ค านวณแทน

การใช COUNTIF

COUNTIF function: นบจ านวน cells ทมคาภายในเปนคาใดๆ โดยตรงตามเงอนไขทก าหนด

รปแบบ =COUNTIF( range, criteria)


range คอ range หรอชวงของ cells ทตองการนบ

criteria คอ เงอนไข ในรปแบบของการเปรยบเทยบ ซงสามารถก าหนดไดเพยง เงอนไขเดยวเทานน หากก าหนดโดยตรงภายใน function ตองก าหนดภายในเครองหมายค าพด (“ “)

แปลงอกษรใหเปนตวพมพใหญ

รปแบบ =upper()

เปดไฟล NorthwindDB

เพม column ใหม

ท าการแปลงชอทไดท าจากแบบฝกหดทแลว ใหเปนตวพมพใหญ

Workshop 4 เปดไฟล Workshop4.xlsx ใชฟงกชนทเกยวของ ค านวณเซลตางๆ ของแบบฟอรม ใบสงสนคา/ใบก ากบภาษ


ฟงกชนทเกยวของ

ใชฟงกชนและสตรค านวณ ตามรายการดงน

1. G11:G15 ใชฟงกชน Vlookup() แสดงรายการตาม CODE ในเซล F11:F15

2. H11:H15 ใชฟงกชน Vlookup() แสดงราคาตอหนวยตาม CODE ในเซล F11:F15

3. I11:I15 สรางสตรหาราคารวม โดยคดจาก จ านวน คณ ราคารวม

4. I24 ใชฟงกชน Sum() รวมเงน เซล I11:I15

5. I25 สรางสตร ภาษมลคาเพม โดยคดจาก I24 คณ 7%

6. I26 สรางสตร รวมทงสน โดยคดจาก I24 บวก I25

7. E25 ใชฟงกชน Bahttext() แสดงอกษรเลขในเซล I26

8. E29 ใชฟงกชน IF() แสดงขอความ โดยมเงอนไขดงน

8.1. ถาราคารวมทงสน ในเซล I26 ตงแต 7000 ขนไป จะแสดงขอความ "บรการจดสง"

8.2. ถาราคารวมทงสน ในเซล I26 ไมถง 7000 ขนไป จะแสดงขอความ "ไมมบรการจดสง"

if( ) การก าหนดเงอนไขในการแสดงผล

vlookup( ) คนหาคาในคอลมนซายสดของขอมล แลวสงกลบคาในแถวเดยวกนจากคอลมนทก าหนด

sum( ) หาผลรวม

bahttext( ) แปลงจ านวนเงนใหเปนตวอกษรภาษาไทย


เฉลย


บทท2. การท างานกบขอมล

การจดรปแบบขอมลในแผนงาน เมอพมพขอมลเสรจแลวกท าการตกแตงของมลใหสวยงาม มข นตอนดงน 1. เลอกชวงขอมลทตองการ 2. แถบ Ribbon ชอ Home คลกปมเครองมอบนทลบาร

หรอจดรปแบบขอมลโดยการใชเมน มข นตอนดงน 1. เลอกชวงขอมลทตองการ 2. คลกขวาเลอกค าสง Format Cells

Number จดรปแบบตวเลข Alignment การจดวางขอความ Font ชนดของตวอกษร Border ก าหนดเสนขอบ Fill ก าหนดสพนและลาย Protection ปองกนการแกไขขอมล

3. ก าหนดรายละเอยดเสรจแลวคลกปม OK

ใชตกแตง

ตวอกษร

การจดวาง

ขอความ

จดรปแบบตวเลข


การก าหนด Custom Formatting การใช Format ทก าหนดเอง บน Field Number หรอ Date สามารถก าหนดไดโดย

1. เลอกขอมล และคลกแทบ Home

2. ในกลม Number คลกไดอะลอกบอกซ

3. เลอก Tab Number และ เลอก Custom

4. ก าหนด Format ทตองการ ในชองใต Type:

ก าหนดรปแบบตวเลข สามารถใชสญลกษณ # หรอเลข 0


เชน [Blue]#,##0.00; [Red](#,##0.00) หมายถง คาบวก จะแสดง , เมอมตวเลขหลกพนขนไป

ถาไมมตวเลขใดๆใน cell หรอ range จะแสดง 0.00

คาทงหมดจะแสดงดวยตวอกษรสแดง

คาลบ จะแสดง , เมอมตวเลขหลกพนขนไป ถาไมมตวเลขใดๆใน cell หรอ range จะแสดง 0.00 คาทงหมดจะแสดงอยภายในวงเลบเลก และมตวอกษรเปนสเปนแดง

Tips and Trick เทคนคการก าหนดจ านวน sheet ทตองการใชงาน คลกปม Office button เลอกปม จะปรากฎไดอะลอกบอกซ Excel Options ทแทบ Popular คลกเลอกจ านวน sheet จากค าสง ในทนก าหนดเปน 5 sheet เสรจแลวคลกปม OK จะมผลตงแตแฟมงานใหมเปนตนไป


การจดรปแบบแบบมเงอนไข Conditional Formatting ใชในการปรบแตงขอมลตามเงอนไขทต งไว มขนตอนดงน 1. เลอกชวงขอมลทตองการจดรปแบบ 2. แถบ Ribbon ชอ Home หวขอ Styles คลกปม

จะปรากฏรายการใหเลอกใช

3. ตวอยาง จากตาราง ขอมลดานลางใช Data Bars เนนขอมลตวเลข

Highlight เซลลทตรงตามเงอนไขทก าหนด

แสดงขอมลตวเลข 10 อนดบสงสดหรอต าสด

แสดงขอมลเปนกลมเดยวกนแบบกราฟแทง

แสดงขอมลเปนกลมเดยวกนดวยสตางๆ

แสดงขอมลเปนกลมเดยวกนดวยสญลกษณไอคอน

สรางเงอนไขเอง

ลบเงอนไขทไมตองการใชงาน

จดการกบเงอนไขทก าหนด


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


การเปลยนเงอนไขหรอรปแบบทก าหนด Icon Set ทไดเลอกไว สามารถสรางเงอนไขใหตรงกบความตองการได มขนตอนดงน 1. เลอกชวงขอมลเดมทท าการจดรปแบบไว 2. แถบ Ribbon ชอ Home หวขอ Styles คลกปม 3. เลอกค าสง Manage Rules

4. คลกปม Edit Ruleจะปรากฏไดอะลอกบอกซ Edit Formatting Rule ใหแกไขรายละเอยด 5. คลกปม OK

คาเงอนไข รปแบบ

เงอนไข


การลบรปแบบขอมลทก าหนดเงอนไข ชวงขอมลทมการจดรปแบบตามเงอนไขทต งไว เมอไมไดใชงานแลว คณสามารถท าการยกเลกเงอนไขนนได โดยมขนตอนดงน 1. เลอกชวงขอมลเดมทท าการจดรปแบบไว 2. แถบ Ribbon ชอ Home หวขอ Styles คลกปม 3. เลอกค าสง Clear Rules

ในทนเลอกค าสง Clear Rules from Selected Cells

การคนหาเซลลทมการจดรปแบบตามเงอนไข 1. แถบ Ribbon ชอ Home หวขอ Editing คลกปม 2. เลอกค าสง Conditional Formatting

จะแสดงชวงขอมลทมการก าหนดเงอนไขใหทนท Workshop5 จากไฟล Product.xls ใหท าการใสสญลกษณทคอลมน H รายการ Unit in Stock เพอใหเหนชดเจนวา

รายการใดควรสงซอ โดยมเงอนไข ดงน

จ านวนคงเหลอมากกวาเทากบ 50 ใหใชสญลกษณ

จ านวนคงเหลอนอยกวา 50 และมากกวาเทากบ 10 ใหใชสญลกษณ

จ านวนคงเหลอนอยกวา 10 ใหใชสญลกษณ หมายถงจ านวนคงเหลอนอย ตองสงซอเพม

จากนนท ารายงานสรป เฉพาะขอมลรายการสนคา ทตองสงซอ


ขนการเตรยมการ

เปดไฟล product.xlsx

ขนตอน

1. คลกเลอกทชอคอลมน H (รายการ Unit in stock)

2. คลกแทบ Home และคลกปม

3. คลกเลอกแบบ Icon Sets เลอนเมาไปตามลกศรดานขวา เลอกแบบ 3 Symbols (Circled)

4. คลกปม และคลก Mange Rules

5. ปรากฏหนาตาง Conditional Formatting Rules Manager

6. คลกปม Edit Rules...


7. บรเวณ Icon เปลยนคาตางๆ ตามภาพดานลาง

คลกเลอก Type = Number

ใหพมพตวเลขในชอง Value 50 และ 10 ตามล าดบ

8. คลกปม OK


การตกแตงดวย Format as Table 1. เลอกพนทขอมลทตองการ 2. คลกแทบ Insert 3. คลกปม Table

4. คลกปม OK

5. พนทขอมลจะถกเปลยนใหกลายเปนตารางทมคณสมบตพเศษ โดยปรบแตงคณสมบตไดจาก Table Tools ซงประกอบดวยแทบ Design ดงรป


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

แถวผลรวมอตโนมต ดงน

1. คลกเลอกตาราง 2. คลกแทบ Design 3. คลกแสดงเครองหมายถกทชองดานหนา Total Row 4. แถวผลรวมจะถกสรางขนมาใหอตโนมต

Tip and Trick

หากตองการเปลยนจากผลรวมเปน ผลสรปอน สามารถเปลยนไดโดยคลกเลอกชองเซลของ

ตวเลขผลรวม แลวคลกปมลกศรดานขางชองเซล เพอเลอกฟงกชนอนตามตองการ

คลกเลอก

ฟงกชน


การแปลงตารางแบบพเศษใหกลบมาเปนขอมลธรรมดา 1. คลกเลอกตาราง 2. คลกแทบ Design 3. คลกเลอกค าสง Convert to Range

4. คลกปม Yes

สรปเรองการปองกนขอมล Protection 1.ปองกนการเปดสมดงาน แบบใสรหส

1.1 คลกปม Office Button Prepare Encrypt Document


ปรากฎหนาตาง ใสพมพรหส และคลกปม OK

2.ปองการเขยนขอมลบน sheet (แทบ ReviewProtect Sheet)


2.1ปองกนการแกไขทง sheet

2.2ใหเขยนบาง cell แทบ (ReviewAllow User to Edit Range)

2.3ซอนสตร (คลกเมาสขวาทเซลนน Format cell แทบ Protection Hidden)

3.ปองกนการแกไข workbook เชนการลบ เปลยนชอ

(แทบ Review ProtectionProtect Workbook )

2.1 2.2

3


บทท 3. Working with Data Analysis

รจกกบฐานขอมลใน Microsoft Excel Excel มความสามารถในการจดการกบฐานขอมล ท าใหสะดวกในการจดเกบ คนหา แกไข/ลบ

ขอมล ตลอดจนน าขอมลมาประมวลในลกษณะของรายงานแบบตางๆ ได

Import from another program: การน าเขาขอมลจากภายนอก

การจดเกบขอมลใน Excel นอกจากจะท าการกรอกขอมลลงไปตรงๆ แลว Excel ยงสามารถน าขอมล

รปแบบอนทมอยน าเขามาใชได รปแบบไฟลท Excel สามารถน าเขาขอมลจากภายนอกได เชน ไฟล

จากโปรแกรม Microsoft Excel ไฟลอนๆ (*.xlsx) , ไฟลขอความหรอ Text files (*.txt) , ไฟลจาก

โปรแกรม Microsoft Access (*.mdb)

มขนตอนการน าเขาขอมล ดงน

1. เลอกต าแหนงเซลทตองการใหน าขอมลจากภายนอกมาวาง

2. คลกแทบ Data

3. คลกปม Text to Columns

4. หนาตาง Select Data Source ใหเลอกรปแบบของไฟลขอมล และไฟลขอมลทตองการน าเขา

แลว Click ปม Open


5. หนาตาง Text Import Wizard - Step 1 of 3 ตองก าหนดคาในสวนตางๆ แลวคลกNext

- Original data type จะมคาใหเลอก 2 คา คอ Delimited กรณทไฟลขอมลมการแบงขอความ

ดวยสญลกษณ และ Fixed width กรณทไฟลขอมลใชการเวนวรรคดวยชองวางแบงขอความ ตวอยาง

นใหเลอก Fixed width

เลอกไฟลทตองการ

น าเขา

เลอกรปแบบของไฟลเปน Text

files


- Start import at row ใหเลอกวาจะน าเขาขอมลตงแตแถวทเทาใด ตวอยางนใหเลอกน าเขา

ตงแตแถวท 1

- File origin เปนการก าหนดภาษาของขอมลทจะน าเขา ตวอยางนใหเลอกภาษาไทย คอ 874 :

Thai (Windows)

6. หนาตาง Text Import Wizard : Step 2 of 3 Excel จะท าการแบงเสน Column ใหอตโนมต

โดยดจากขอมลในไฟลทจะน าเขาเปนหลก ตวอยางนใหปม Next ไดเลย


7. หนาตาง Text Import Wizard : Step 3 of 3 เปนการก าหนดรปแบบของเซลขอมลวาแตขอมล

ในแตละ Column จะเปนรปแบบ General, Text, Date, หรอจะเลอกไมน าเขา Column นนโดย

การเลอก Do not import column (skip) ซงปกต Excel จะก าหนดใหเปน General ทก

Column ตวอยางนใหกด Finish ไดเลย

8. หนาตาง Import Data ใหก าหนดวาจะน าขอมลวางไวทเซลใดในชททปรากฎอย (Existing

worksheet) หรอใหน าไปวางในชททสรางใหม (New worksheet) ตวอยางนใหเลอกชทท

ปรากฎอย และเลอกวางทเซล A1 เสรจสนข นตอนการน าเขาขอมลจากภายนอก จะได

ตารางขอมลสนคา 6 Column 143 Rows

Data Validation

การปองกนการปอนขอมลทไมถกตองในแผนงาน


เพอใหการกรอกขอมลมความถกตองมากยงขน คณควรจะก าหนดกฏขอบงคบในการปอนขอมล มข นตอนดงน 1. เลอกชวงขอมลทตองการพมพ 2. ทแทบ Data หวขอ Data Tools คลกปม 3. จะปรากฏไดอะลอกบอกซ Data Validation ทหวขอ Settings ใหก าหนดเงอนไข ในทนก าหนดวา

ขอมลตวเลขทปอนจะตองอยในชวงตงแต 8000-40000 เทานน

4. คลกหวขอ Input Message ระบขอความทตองการแสดงเมอต าแหนงเซลลอยในชวงนน เปนการบอกให user ทราบวาใหท าอะไร

เลอกวาขอมลทปอนเปน

ชนดไหน

เลอกเครองหมายในการ

เปรยบเทยบ

ก าหนดชวงตวเลขเรมตน

ก าหนดชวงตวเลขสดทาย


5. 6. คลกหวขอ Error Alert เพอก าหนดวา ถา user ปอนขอมลไมตรงตามกฎขอบงคบ ใหแสดง

Message และไอคอนเตอน เสรจแลวคลกปม OK

ถาตองการยกเลกเงอนไขทก าหนด คลกปม

Tips and Trick เทคนคการเลอกแบบเพอแสดงไดอะลอกบอกซเตอน จากค าสง Data Validation


Pivot Table

ขนตอนการสราง Pivot Table

จากขอมลในตาราง ถาตองการท า report โดยสรปยอด actual แยกในแตละ Region เพอเปรยบเทยบวาทใดขายไดมลคามาสด

1. Chart

2. ปรากฏแทบชทใหม ใหท าการลาก fied ทตองการทราบสงในชองแถบดานขวาดงภาพดานลาง

จะไดขอมลทตองการ พรอมกราฟ


การแสดงขอมล ในรป แบบ Group

จากขอมลในตาราง ถาตองการท า report โดยสรปยอด actual แยกในแตละ Region เพอเปรยบเทยบวาทใดขายไดมลคามาสด

1. วางเคอรเซอร ในตาราง 2. แทบ Insert ค าสง Pivot table เลอก Pivot Chart

3. ปรากฏแทบชทใหม ใหท าการลาก fied ทตองการทราบสงในชองแถบดานขวาดงภาพดานลาง

จะไดขอมลทตองการ พรอมกราฟ


การปรบแตงกราฟ


การจดกรองและจดเรยงขอมล การจดเรยงล าดบขอมลแบบเงอนไขเดยว สามารถท าไดดวยการ Click เลอกเซลขอมลใน

Column ทตองการ แลวเลอกเครองมอบนแทบ Data Sort Ascending หรอ Sort

Descending เพอจดเรยงล าดบขอมลจากนอยไปมาก หรอมากไปนอยตามล าดบ

การ Sort ขอมลฟลดเดยว

เปนการเรยงล าดบขอมลภายในคอลมน หรอแถว

เมอคณน างานฐานขอมลมาสรางในโปรแกรม Excel สวนใหญหลงจากสรางงานเรยบรอยแลว สงทจะ

ท าตอไป คอ การจดเรยงขอมล มข นตอนดงน

1. คลกต าแหนงเซลในหวขอทตองการจดเรยง จากตวอยางน เลอกเซลใดกไดในหวขอ Salary 2. ทแทบ Data หวขอ Sort & Filter คลกปมบนทลบาร เพอเลอกเกณฑการจดเรยง

Sort Smallest to Largest จดเรยงจากนอยไปมาก (AZ)

Sort Largest to Smallest จดเรยงจากมากไปนอย (ZA)

การ Sort ขอมลซอนกนหลายชน

ต าแหนงเซลอยในงาน Database 1. คลกปม เพอเปดไดอะลอกบอกซ Sort ก าหนดรายละเอยดเพมเตม


2. ทชอง Sort by เลอกหวขอหลกในการจดเรยง

ทชอง Order เลอกเกณฑการจดเรยง 3. 4. คลกปม เพอเพมหวขอรอง, หวขอท 3, 4, 5 ในการจดเรยง เสรจแลวคลก

ปม OK

การ Sort ขอมลตามสพนและสตวอกษร Sort by Color

กอนท าการเรยงล าดบตองท าการ format รปแบบอกษรใหเปนสตางๆ กอน

1. คลกวางเซลในตารางขอมล 2. คลกแทบ Data 3. คลกปม Filter

ปรากฏสญลกษณ

เครองกรองในตาราง

4. คลกปมกรอง 5. เลอกรายการ

Sort by Color

เลอกสน าเงน


การกรองขอมลแบบอตโนมต (AutoFilter) การท างานกบฐานขอมล อกเรองหนงทใชงานบอย นอกจากและส าคญนนคอการคนหาขอมลหรอ

การกรองขอมลเพอตอบสนองความตองการในการน าขอมลมาวเคราะห ซงมรปแบบการท างานดงน

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

เปนการเลอกขอมลจากรายการโดยตรง ซงมรปแบบการใชงานใหมเพอใหผใชสามารถเลอกคาท

ตองการไดมากกวา 1 คา มข นตอนดงน

1.ต าแหนงเซลอยในงาน Database

2.ทแทบ Data หวขอ Sort & Filter คลกปม จะปรากฏลกศรลงทกหวขอ

3.คลก

ปมลกศรลงของหวขอทจะคนหา เลอกขอมลทตองการ ในทนคลกหวขอ Department เลอก

Engineering

4. เสรจแลวคลกปม OK


สงเกตแถบ Status bar ดานลาง จะแสดงจ านวน record ทคนหาได

การยกเลกขอมลทท าการกรอง (Filter)

1. ต าแหนงเซลอยในงาน Database 2. ทแทบ Data หวขอ Sort & Filter คลกปม บนทลบาร

การก าหนดเงอนไขในการกรองขอมล (Advanced Filter)

การกรองขอมลทกลาวมาแลวเปนการกรองขอมลแบบงายๆ แตมการกรองขอมลอกแบบหนงเปนการ

กรองขอมลทซบซอนขน นนคอสามารถก าหนดเงอนไขเพอกรองขอมลทตองการได

การก าหนดเงอนไขอยางงาย

การก าหนดเงอนไขดวยวธน ผลขอมลทไดจะแทนทคาขอมลเดม มข นตอนดงน


เมอคลกปม เครองกรอง แลวคลกปมลกศรลงของหวขอทจะคนหา เลอกค าสง Text Filters หรอ

Number Filters (ขนอยกบขอมลในหวขอทเลอก) จะเปดค าสงใหเลอก

การก าหนดเงอนไขแบบระบต าแหนงใหมใหกบผล

วธนสามารถเลอกแสดงผลในต าแหนงอนได มข นตอนดงน

1.ท าการคดลอกขอมลและหวขอทใชเปนเงอนไขในการคนหา จากตวอยางน หวขอคอ Department

และขอมลคอ Admin. copy ไปวางไวท ต าแหนงเซล K1-K2

2.ต าแหนงเซลอยในงาน database


3.ทแทบ Data หวขอ Sort & Filter เลอกปม จะปรากฏไดอะลอกบอกซ

Advanced Filter ใหก าหนดรายละเอยด

4.หลงจากเลอกรายละเอยดตางๆ เสรจแลว คลกปม OK

จะปรากฏผลลพธดงรป และแสดงสรปทแถบ Status bar ดานลาง วามคนทอยในแผนก

Admin. 14 คน

ในจ านวนพนกงานทงหมด 114 คน

แสดงผลลพธทไดในงาน database นน

เลย

ชวงขอมลของงาน database ทงหมดท

ใชในการกรอง

เลย

เลอกต าแหนงเซลลทใชเปนเงอนไข คอ

K1-K2

เลย

แสดงเฉพาะขอมลทไมซ ากน

เลย


การกรองคาทไมซ ากนหรอการเอาคาทซ ากนออก

ในงาน Database เมอท าการปอนขอมลจ านวนมากๆ อาจมบาง record ทพมพซ ากน คณสามารถเอา

คาทซ ากนออกได มข นตอนดงน

1. ต าแหนงเซลอยในงาน database 2. ทแทบ Data คลกปม จะปรากฏไดอะลอกบอกซ Remove Duplicates

3. คลกเลอกหวขอฟลดทใหตรวจสอบขอมลซ า ในทนเลอกฟลด Emp ID 4. เสรจแลวคลกปม OK 5. จะปรากฏไดอะลอกบอกซแสดงจ านวน record ทซ ากน คลกปม OK

การกรองขอมลตามสพนและสตวอกษร Filter by Color กอนท าการกรองตองท าการ format รปแบบอกษรใหเปนสตางๆ กอน

1. คลกวางเซลในตารางขอมล 2. คลกแทบ Data 3. คลกปม Filter

ปรากฏลญลกษณเครองกรองในตาราง


4. คลกปมเครองกรอง 5. เลอกรายการ Filter by Color 6. เลอกสแดง

จากตาราง Product.xlsx เกบรายการสนคาทงหมดของบรษท ตองการแสดงขอมลของรายการสนคา ทม category เปนสแดง และเรยงล าดง List Price จากมากไปนอย

เตรยมงาน เปดไฟล Product.xlsx

ขนตอน 1. ต าแหนงเซลลอยในงานตาราง 2. ทแทบ Data หวขอ Sort & Filter คลกปม จะปรากฏลกศรลงทกหวขอ


3. คลกปมลกศรลงของหวขอ Category 4. เลอก Filter by Color และคลกสแดง

5. คลกต าแหนงเซลลหวขอ List Price 6. ทแทบ Data หวขอ Sort & Filter คลก ปม

เพอจดเรยงแบบ Sort Largest to Smallest เปนการจดเรยงตวเลขจากมากไปนอย (ZA)

Workshop5 เปดไฟล Workshop-Filter.xlsx 1. ชท Sort ท าการเรยงล าดบขอมลดงน

1.1. ชท Sort1 เรยงขอมลในคอลมน E “ภมภาค” จากนอย ไป มาก

1.2. ชท Sort 2 เรยงขอมลในคอลมน E “ภมภาค” จากนอย ไป มาก และในแตละภมภาคเดยวกน

ใหเรยงตวเลขในคอลมน F “เปาการขาย” จากมาก ไป นอย


1.3. ชท Sort3เรยงขอมลในคอลมน D “ประเภทการขาย” โดยใหประเภทการขายสแดง อยดานบนสด

2. ชท Filter การกรองขอมลโดยมเงอนไขดงน 2.1. คอลมน D “ประเภทการขาย” แสดงเฉพาะรายการสแดง 2.2. คอลมน G “ยอดขายจรง” แสดงเฉพาะ ตวเลขทมคามากกวา100,000


บทท 4. การสราง Macro

Recording Macro งานทมการท าบอยๆ เชนการจดรปแบบทซ าๆ การสรางกราฟ หรอแมแตการใชค าสงบาง ค าสงบอยๆ

ถาสามารถรวบรวมการท างานในทกๆขนตอนทเกยวของไวภายใตค าสงเดยวได กจะ ท าใหงานเปนไป

อยางรวดเรวและอตโนมต ชวยประหยดเวลาในการท างานมากขนอกดวย

Macro คอการบนทกการกระท าตางๆบน Program Excel เปรยบเทยบไดกบการบนทก เสยง

ลงบนเครองบนทกเสยงหรอ การบนทกภาพเคลอนไหวตางๆลงบน Videotape โดยหลงจาก ท าการ

บนทกเรยบรอยแลว สามารถน าสวนทบนทกไวเหลาน ออกมาแสดงหรอใชงานไดอก ตามความ

ตองการ

การบนทก Macro จะบนทกการท างาน หรอการกระท าไป พรอมกบการท างานจรง ตาม

ขนตอนตางๆ ทตองการ หลงจากนนจะสามารถเรยกการบนทกเหลานน กลบมาท างาน ซ าไดอก

หลายๆครง

ขนตอนในการ Record Macro

1 . แทบ View Macro Record New Macro

2. บนหนาตาง Record Macro ก าหนด..

2.1 Macro Name: ชอ Macro

2.2 Shortcut key: การเรยกใชดวย ctrl + ตวอกษร


2.3 Store Macro in: การจดเกบ ค าสง

2.3.1 Personal Macro Workbook

2.3.2 New Workbook

2.3.3 This Workbook

2.4 Description: ค าอธบาย

2.5 Click OK

Macro Name:

1. ตองไมมการเวนวรรค หากตองการเวนวรรค ใหใช underscore “_” แทน

2. ตองไมก าหนดซ ากบ macro name ทเคยก าหนดไว ถามการก าหนดชอซ า จะปรากฏ

message box เตอน โดย click ปม Yes จะเปน replace ค าสงเดม

click ปม No จะเปน การยอนกลบไปก าหนดชอใหม

3. ตองไมเกน 64 ตวอกษร

Shortcut key:

1. ชวยใหการเรยกใช ค าสง จาก Macro งายขน โดยการกด Ctrl รวมกบ การกดตวอกษรท

ก าหนดไว ถาก าหนดดวยตวอกษรพมพใหญจะปรากฏ เปน Ctrl+Shift+ตวอกษร นนหมายถง

เมอม การเรยกใช Macro ตองกด Ctrl พรอมดวยการ กด Shift และตวอกษร จงจะไดค าสงนนมา

ท างาน

2. ไมอนญาตใหมการก าหนด Shortcut key ซ า กบทเคยก าหนดไปแลว หากก าหนดซ า จะ

ปรากฏ Message เตอนใหก าหนดใหม

3. ถามการ ก าหนด Shortcut key ซ ากบ Shortcut key ของระบบ เชน Ctrl + c

หรอ Ctrl + v จะมผลท าให Shortcut key ของระบบเหลานน ไมสามารถท างานได


4. Shortcut key นนจะก าหนดหรอ ไมกได

Store Macro in:

การก าหนดทเกบ บนทกค าสง Macro สามารถก าหนดการจดเกบได 3 ทคอ..

Personal Macro Workbook

เกบบนทก Macroไวใน file ทชอ "Personal.xls" ซงจะท าใหสามารถเรยกใช Macroได

ตลอดเวลา ไมวาก าลงท างานอย บน file ใดกตาม

Personal.xls น Microsoft Excel จะเปนผสรางให เมอมการสราง Macro ในครงแรกและ เลอก

ทเกบเปน Personal Macro Workbook โดยจะถกเกบไวใน folder ทชอ "XLSTART" ซงเปน folder

ทถกสรางขนพรอมกบการ Install Program

เมอมการเปดเขาส Program Microsoft Excel เมอใดกตาม file Personal.xls กจะถกเปดขน

มาทนท และจะถกซอนโดยอตโนมต

ถาตองการแสดง Personal.xls

New Workbook :

เกบบนทก Macro ไวท file ใหม ซงจะตองท าการ save และเมอตองการน าค าสง มาใชตอง

open file น

This Workbook:

เกบค าสงไวท file ทสรางค าสงน โดยตรง


Description :

ค าอธบายทตองการ ก าหนดหรอไมกได ค าอธบายจะชวยให ทราบการท างานของค าสงได

3. หลงจาก OK แลว จะปรากฏ Toolbar ทชอ “Stop Recording “ ทมปมค าสงในการหยด การ

record และการ

บนทกกจะเรม โดยการท างานในแตละขนตอน จะถกบนทกตามล าดบการท างาน

Relative Reference

การอางองต าแหนงผลลพธเมอมการเรยกใช Macro แบงไดเปน 2 ลกษณะคอ..

1. Absolute การอางองต าแหนงคงท หมายถงเมอมการเรยกใช Macro เมอใดจะมการแสดง

ผลลพธลงบนต าแหนงเดมทกครง เชน ระบต าแหนงท A2 ไวเมอมการเรมตนบนทก Macro

หลงจากนนเมอมการเรยกใช Macro จะแสดงผลลพธ ณ. ต าแหนง A2 เสมอ

การก าหนดลกษณะ Absolute น ไมตอง Click เลอก ปม Relative Reference แตกอนจะเรม

ท าการบนทกขนตอนการท างาน ใหเลอกต าแหนงทตองการกอนเปนล าดบแรก

2. Relative การอางองต าแหนงแบบสมพนธ หมายถงเมอมการเรยกใช Macro เมอใดจะม

การแสดงผลลพธ ณ. ต าแหนงปจจบน เสมอ ซงต าแหนงปจจบนนน อาจไมใชต าแหนงเดยวกนใน

ทกๆครง ทมการเรยกใช Macro

Stop Recording เมอตองการ

หยดบนทกให Click ทปมน แตถาไม

ปรากฏ Toolbar น


การก าหนดลกษณะ Relative น ให Click เลอกปม Relative Reference กอนจะเรมท าการ

บนทกขนตอนการท างาน

Running Macro เมอท าการบนทก Macro ไวเรยบรอยแลว สามารถเรยกใช ค าสงการท างานทบนทกไวใน Macro ได

ดวยวธเบองตนคอ ใช Shortcut key ทก าหนดไว

Macro Button แสดงเครองมอในการสรางปม

1. คลกปม Office Button

2. คลก Excel Options และ เลอก

3. คลกปม Insert

4. คลก Button ท าการวาดปมทตองการ จะปรากฏหนาตาง Assign Macro

5. คลกเลอกรายการ macro ทสราง และคลกปม OK


Workshop-Macro เปดไฟลชอ Workshop-Macro.xlsx


บทท 5. Collaborating with others

ปกต การท างานกบ file บน Excel นนมกถกจ ากดดวย การท างานทละ 1 user ซงท าให file ทตองใชรวมกน หลายๆ users ในเวลาเดยวกนท าไดยาก เนองจาก หาก user ท1 ก าลงใชงานใน file นนๆอย user รายอนจะไมสามารถเขาท างานพรอมกนได หรอหากเขาท างานไดกจะเปนการท างานแบบ read only

ปจจบน Excel ไดพฒนาให users หลายๆราย สามารถ open หรอ update file เดยวกน พรอมกนได ดวยการ Share Workbook

การใชง านไฟลรวมกน (Shared Workbook) วธการก าหนด..

1. แทบ Review Share Workbook

2. บน Editing tab ใสเครองหมาย เพอ อนญาตให แกไขพรอมกน ไดมากกวา 1 user

3. Click Advanced tab เพอก าหนดเงอนไขในการตดตามการแกไข Track changes


Keep change history for

ก าหนดจ านวนวนทตองการบนทกการแกไข

Don’t keep change history ไมมการบนทก

Update changes

When file is saved หลงการ save file

Autometically every ปรบปรงอตโนมตโดยก าหนดเวลาเปนนาท

Save my changes and see other’s change

save และแสดงการแกไขของ user อนดวย

Just see other user’s change

แสดงการแกไขของผอน

Conflicting changes between users

Ask me which changes win ใหม message ถามถงการยอมรบในการแกไขวาเปน

ของ user ใด

The change being save win การแกไขของ user ทก าลงถก save จะไดรบการ

ยอมรบ

4. Click OK จะปรากฏ message ใหท าการ save file

5. Click OK จะปรากฏ การ Shared ใหทราบ บน Titlebar

ขอจ ากดทควรทราบ ภายหลงการ share file 1. ไมอนญาต ให Insert หรอ Delete cells ยกเวน Insert หรอ Delete rows และ columns

2. ไมอนญาต ให Delete Worksheet

3. ไมอนญาต ให Insert หรอ Edit charts, pictures, objects หรอ hyperlinks


4. ไมอนญาต ให ใชเครองในการวาดบน Drawing Toolbar

5. ไมอนญาต ให ใชค าสง Group and Outline

6. ไมอนญาต ให ใชค าสง Subtotals

7. ไมอนญาต ให ใชค าสง Validation

8. ไมอนญาต ให ก าหนด Password ใหกบ Worksheet หากมการก าหนด ไวกอนทจะมการ

share file กจะไมสามารถยกเลก หรอแกไขได

Tracking Changes: การตดตามการแกไข เมอมการ share file ใหสามารถเขาถง และแกไขไดพรอมกน หลายๆ users ยอมจะตองมขอจ ากด

และมกเผชญกบปญหา ในเรองขอมลบน file นนๆ Excel สามารถแบงเบาปญหาเหลานได ดวยค าสง

ในการตดตามการแกไข โดยบนทกการแกไขเกบไว เมอตองการคนหาการแกไขกจะสามารถแสดงการ

บนทกนนได 2 วธดงน

1. แสดงดวย comment ซงมลกษณะเปน สามเหลยมสน าเงน บนดานซายของ cellหลงการ แกไขและ save file แลวจะปรากฏ สญลกษณ ของ comment เมอเลอน mouse ชลงบน cell ทมสญลกษณ จะปรากฎขอความทแสดงถง user ทไดท าการแกไข, วนทเวลาทไดแกไข comment จะแสดงใหเหนเพยงครงเดยวเทานน คอการเปลยนแปลงลาสด หลงจากนนเมอปด file ลง comment จะไมปรากฏอก จนกวาจะมการเปลยนแปลงครงใหมเกดขน

2. แสดงดวย History ซงเกบบนทกไวชวคราวบน Worksheet นนๆ ดวยวธการก าหนดดงน


แทบ Review Track Changes Highlight Changes

When: ใหท าการตดตาม บนทกเมอใด

Who: ใหท าการตดตาม บนทก วาใคร เปลยนแปลง

Where: ใหท าการตดตาม บนทก cell หรอ range ใด ถาไมก าหนด หมายถง ทงหมด

Highlight changes on screen แสดงการเปลยนแปลงใหเหนทนท เปน comment

List changes on a new sheet แสดงรายการเปลยนแปลงบน sheet ใหม

ถาตองการเปลยนรปหรอยกเลกรปภาพพนหลงของเวรกชตกใชค าสง ( รปแบบ -> แผนงาน -> ลบ

พนหลง )


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

ดงน

ไฮเปอรลงค

ไฮเปอรลงค หรอ Link คอการสางจดเชอมโยงเอกสารจากจดหนงไปยงอกจดหนง หรอไปยงเอกสาร

อน โดยใช ขอความ รปภาพ ปมกด หรอเมน เปนตวน าทางไปยงเอกสารดงกลาว ซงเนอหาขอมลอาจ

เกยวของกนหรอไมเกยวของกนกได

องคประกอบของไฮเปอรลงค

ตนทาง ( Source Anchor ) หมายถงตวทเลอนเมาสไปชแลวพอยเตอรจะเปลยนเปนรปมอ และเมอ

คลก เอกสารปลายทางกจะถกเรยกขนมา

ปลายทาง ( Destination Anchor ) คอ URL ทอางถงเอกสาร หรอแหลงขอมลในอนเทอรเนต

ใน Excel สามารถ ลงคไดหลายแบบเชน

การลงคระหวางไฟลหรอเวบเพจ ( Existing File or Web Page )

การลงคภายในเอกสารเดยวกน ( Place in this Document )

การลงคไปยงเอกสารใหม ( Create New Document )

การลงคไปยงทอยอเมล (E- Mail Address )


การลงคระหวางไฟล

1. เลอกขอความหรอรปภาพทจะสรางเปนลงค

2. คลกปม Insert Hyperlink ( แทรกการเชอมโยงหลายมต )

3. คลกเลอก Existing File or Web Page ( แฟมหรอเวบเพจทมอย )

4. คลกเลอก Current Folder ( โฟลเดอรปจจบน ) เพอเลอกจากโฟลเดอรเดยวกบโฟลเดอรตนทาง

หรอเลอกโฟลเดอรอนในชอง Look in: ( มองหาใน )

5.เลอกชอไฟลทตองการ ใหเปนไฟลปลายทาง

6. ขอความทเปนไฮเปอรลงคทสรางแลวปกตจะมขดเสนใตอย เมอเลอนเมาสไปทขอความลงคจะเหน

กรอบขอความเลกๆสเหลอง คอยบอกต าแหนงวาขอความนเชอมโยงไวกบไฟลใด การทดสอบลงคใน

Excel ท าไดโดยเลอนเมาสไปคลกทลงคนน จะปรากฏเอกสารทอางถงแสดงขนมาในวนโดวของ

โปรแกรมบราวเซอร

หรอเลอกค าสง File -> Web Page Preview


Excel 2007 New Feature


แบบฝกหดทายบท ระยะเวลา 1 ชม

1. การค านวณ

การใช Function แบบก าหนดเงอนไข

การใช Pivot Table

Function ในกลม Lookup and Reference

จงใชแทบชท cal ค านวณหา เงนทจายจรง ในชอง E2:E9 โดยคดจาก ราคาสนคา หก เปอรเซนต สวนลด

จงใชแทบชท function หาเงนเดอนชอง C5:C12 โดยเงนเดอนเรมตน เงนเดอนเรมตน กรณมเกยรตนยม = ฿8,000 เงนเดอนเรมตน กรณไมมเกรตนยม =฿10,000

จากแทบชท Region Sales ใหท ารายงานในรปแบบ Pivot Table ตามตวอยางดานลางลงในแทบชท Pivot

Row Labels Sum of Actual

East 703000

Midwest 385000

South 425000

West 380000

Grand Total 1893000

แทบชท vlookup ในชอง E5 จงใช Vlookup คนหายอดรวมการขาย Actual ของ East จากตารางในแทบชท Pivot

2. การเรยงล าดบขอมล จากแทบชท filter จงกรองขอมลในตาราง โดยเรยง region จากมากไปนอย


และในแตละ region ใหเรยง Target จากมากไปนอยเชนกน

4. Validation : การตรวจสอบความถกตองในการกรอกขอมล

ใชตาราง validation ท าการใสเงอนไข ดงตอไปน

คณตองท าการเกบขอมลลกคาทส งซอสนคาในแตละวน ดงนนการบนทกขอมลของลกคาอาจเกดความผดพลาด คณจงวางแผนทจะใช Data Validation ในการตรวจสอบการปอนขอมล โดยมขอมล และเงอนไขของขอมลดงน

- ชอสนคา คณตองการเลอกรายชอสนคาจาก List รายการ แทนการพมพ เพอปองกนการพมพผด และเพมความรวดเรว โดยมรายการสนคาดงน

1. เครองถายเอกสาร 2. เครอง Fax 3. เครองคอมพวเตอร 4. เครองโทรศพท 5. เครองคดเลข

- ชอลกคา ก าหนดใหปอนคาไดเปนตวหนงสอความยาวไมเกน 100 ตวอกษร

- จ านวนสนคาทส งซอ ก าหนดใหปอนคาเปนตวเลขเทานนโดยจ านวนในการสงซอ ตองไมเทากบ 0

- วนทส งซอสนคา ตองปอนคาเปนวนทปจจบนเทานน

- ชอผรบใบสงสนคา ก าหนดใหเปนตวหนงสอความยาวไมเกน 75 ตวอกษร

5. Working with Macro สรางปม Macro ใหท างานอะไรกไดทคณคดวาชวยในการท างานกบ Excel ไดสะดวกขน


Excel Short Cut คยลด การใชงาน

Ctrl + Down Arrow Key เลอนลงไปยงชองเซลทอยแถวลางสด

Ctrl + Up Arrow Key เลอนขนไปยงชองเซลทอยแถวบนสด

Ctrl + Right Arrow Key เลอนไปยงชองเซลทอยในคอลมนขวาสด

Ctrl + Left Arrow Key เลอนไปยงชองเซลทอยในคอลมนซายสด

Ctrl + Home เลอนไปยงชองเซล A1

Ctrl + Shift + Down Arrow Key

เลอกชวงของเซลตงแต Active Cell ไปถงชองเซลทอยแถวลางสด

Ctrl + Shift + Up Arrow Key เลอกชวงของเซลตงแต Active Cell ไปถงชองเซลทอยแถวบนสด

Ctrl + Shift + Right Arrow Key

เลอกชวงของเซลตงแต Active Cell ไปถงชองเซลทอยคอลมนขวาสด

Ctrl + Shift + Left Arrow Key เลอกชวงของเซลตงแต Active Cell ไปถงชองเซลทอยคอลมนซายสด

Ctrl + * เลอกชวงเซลในพนทซงมขอมลอยตดตอกนทงหมด

Alt + ; เลอกเฉพาะชองเซลทมองเหนเทานน (Visible Cells Only)

F5 เรยกกรอบค าสง Goto

Ctrl + F3 จดการเกยวกบ Range Name เชน สรางใหม หรอ ลบทง

Ctrl + Shift + F3 สราง Range Name จากปายชอขอมล

F3 เรยกใช Range Name

Ctrl + ; แทรกวนทปจจบน


Ctrl + Shift + ; แทรกเวลาปจจบน

Ctrl + D คดลอกขอมลจากเซลทอยดานบน

Ctrl + Enter คดลอกขอมลจาก Active Cell ไปใสในชองเซลทงหมดทถกเลอกอย

Alt + Down Arrow Key กรอกขอมลจาก list

Alt + Enter ตดขอมลทอยในชองเซลใหขนบรรทดใหม แตยงอยในชองเซลเดม

F11 สรางแผนภมจากขอมลทเลอก ลงบน Sheet ใหม

Shift + F11 แทรก Sheet ใหม

Ctrl + 1 (แปนแถวบน) เรยกกรอบค าสง Format Cells

Ctrl + 8 (แปนแถวบน) สราง Out Line อตโนมต

Ctrl + Shift + ~ ลางเฉพาะรปแบบตวเลขออกจากชองเซล

Ctrl + 9 (แปนแถวบน) ซอนแถวทเลอกอย

Ctrl + Shift + 9 (แปนแถวบน)

เลกซอนแถวในบรเวณทเลอกครอมอย

Ctrl + 0 (แปนแถวบน) ซอนคอลมนทเลอกอย

Ctrl + Shift +0 (แปนแถวบน) เลกซอนคอลมนในบรเวณทเลอกครอมอย

Microsoft Excel - @@ Home - KKU Web Hosting 2007 Adv Manual for KKU... · Review Microsoft Excel...

Documents

Transcript of Microsoft Excel - @@ Home - KKU Web Hosting 2007 Adv Manual for KKU... · Review Microsoft Excel...