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

แบง่ปนั แนวทางการใช ้Excel ต ั�งแต ่ก-ฮ

1

โดย สมเกยีรต ิชว่ยมาก

หวัขอ้การอบรม• ทบทวนพื�นฐานที�สําคญั• การประยกุตใ์ชฟ้ังกช์นั• การประยกุตใ์ชค้ําสั�งบรหิารจัดการขอ้มลู• การสรา้งกราฟและการประยกุต์• การประยกุตใ์ช ้PivotTable• การพัฒนาระบบงานดว้ย Macro & VBA

2

สว่นที 1: ทบทวนพื�นฐานที สาํคญั

• Short cut key• การป้อนขอ้มลู• การ Format Cells• ผูช้ว่ยในการเลอืกขอ้มลู• ผูช้ว่ยในการวางขอ้มลู

3

Short cut ที�จําเป็น• Name Box ชว่ยเลอืกเซลลท์ี�ตอ้งการ

4

Short cut ที�จําเป็น

• การกระโดดไปยงัทศิทางตา่งๆ

• การเลอืกขอ้มลูตามทศิทาง

5

Ctrl

Ctrl Shift

Short cut ที�จําเป็น

• เลอืกขอ้มลูทั �งหมด

6

Ctrl A

• คดัลอกขอ้มลูCtrl C

• วางขอ้มลูCtrl V

การป้อนตวัเลข

• ป้อนตวัเลข

7

• Format ตวัเลข

การป้อนวนัที�

• ป้อนวนัที�

8

• Format วนัที�

การป้อนขอ้มลูดว้ย Auto Fill

9

Step 1 Step 2

Step 3 Step 4 Step 5

การสรา้งรายการ Auto Fill

10

Step 1 Step 2

Step 3

การป้อนขอ้มลูดว้ย Drop Down

• สรา้งรายการ Dropdown ดว้ยวธิี– พมิพร์ายการลงไปเอง– สรา้งจากรายการที�เตรยีมไวบ้นเซลล ์(ภายในชตีเดยีวกนั)

– สรา้งจากรายการที�เตรยีมไวบ้นเซลล ์(ตา่งชตี)

11

การสรา้ง Drop Down (List)

12

(พมิพร์ายการลงไปเอง)

Step 1 Step 2 Step 3 Step 4

กําพมิพร์ายการลงไป

การสรา้ง Drop Down

13

(สรา้งจากรายการที�เตรยีมไวบ้นเซลล)์

Step 1 Step 2 Step 3 Step 4

Format Cells: อตัโนมัติ

14

Format Cells: กําหนดเอง

15

Format Cells: กําหนดเอง (ตวัเลข)

16

Format Cells: กําหนดเอง (ตวัเลข)

17

Format Cells: กําหนดเอง (วนัที�)

18

Conditional Formatting

19

Home

ขั �นตอนการทํา

20

Step 1 Step 2

ขั �นตอนการแกไ้ข

21


ขั �นตอนการเคลยีร์

22

Step 1 Step 2

การสรา้งเงื�อนไขเอง

23

Step 1 Step 2

Format as Table(จัดรปูแบบตารางใหแ้ลดงูา่ย)

24

Format as Table(ชว่ยงานคํานวณแบบอตัโนมตั)ิ

25

ผูช้ว่ยในการเลอืกขอ้มลู

F5 -> Special

26

ผูช้ว่ยในการวางขอ้มลู

Paste Special

27

สว่นที 2: การประยกุตใ์ชฟ้งักช์นั

• การอา้งองิเซลล์• การตั �งชื�อกลุม่เซลล์• การตั �งสตูรคํานวณ• การคํานวณดว้ยฟังกช์นัสําเร็จรปู• การประยกุตใ์ชฟ้ังกช์นั

28

การอา้งองิเซลล์

1. A12. $A$13. $A14. A$15. SUM(Amount)

29

A. Lock เฉพาะคอลัมน์B. Lock เฉพาะแถวC. Lock เซลล์D. ไม ่Lock เซลล์E. ตั �งชื�อกลุม่เซลล์

การต ั�งชื อกลุม่เซลล์

• วธิกีารตั �งชื�อกลุม่เซลล์• การนําชื�อกลุม่เซลลไ์ปใช ้• การควบคมุจัดการชื�อกลุม่เซลล์

30

วธิกีารต ั�งชื อกลุม่เซลล์

• ตั �งชื�ออตัโนมัติ(ตามหวัตาราง)

• ตั �งชื�อแบบกําหนดเอง

31

ตั �งชื�ออตัโนมัต ิ(ตามหวัตาราง)

32


ตั �งชื�อแบบกําหนดเอง

33


ตั �งชื�อแบบกําหนดเอง: Tips

34


การควบคมุชื�อกลุม่เซลล์

35

การนําชื�อกลุม่เซลลไ์ปใช ้

36

Step 1

Step 2

Step 3

37

ฟงักช์นัสาํเร็จรปูคอื ?

Argument

No Argument

Require

Optional

=ชื�อฟังกช์นั ( )

=ชื�อฟังกช์นั (Ar1, Ar2)

=ชื�อฟังกช์นั (Ar1, [Ar2])

Argument

No Argument

Require

Optional

=ชื�อฟังกช์นั ( )

=ชื�อฟังกช์นั (Ar1, Ar2)

=ชื�อฟังกช์นั (Ar1, [Ar2])[ ]

ฟงักช์นักลุม่ตา่งๆ ที นา่สนใจ

38

F.

Text

Number

Date

Logic

Lookup

Infor-mation

ฟงักช์นักลุม่ Text

• LOWER/UPPER/PROPER• TRIM/LEN• LEFT/RIGHT/MID• TEXT TO COLUMN• CONCATENATE

39

ฟงักช์นัเกลุม่ NUMBER

• ABS• TRUNC/INT• ROUND/ROUNDDOWN/ROUNDUP• CEILING/FLOOR• MOD• MAX/MIN/LARGE/SMALL• SUM/PRODUCT/SUMPRODUCT

40

ฟงักช์นักลุม่ DATE

• TODAY/NOW• YEAR/MONTH/DAY• DATE• DATEDIF

41

ฟงักช์นักลุม่ LOGIC

• IF/AND/OR• SUMIF/SUMIFS• COUNT/COUNTA/COUNTIF/COUNTIFS• AVERAGEIF/AVERAGEIFS• MAX(IF(เงื�อนไข, ผลลัพธ)์)• MIN(IF(เงื�อนไข, ผลลัพธ)์)

42

ฟงักช์นักลุม่ LOOKUP & REFERENCE

• VLOOKUP• HLOOKUP• MATCH• INDEX• ROW/COLUMN• OFFSET• CHOOSE• INDIRECT

43

ฟงักช์นักลุม่ INFORMATION

• ISNUMBER• ISTEXT• ISERROR• ISNA• ISBLANK

44

ตวัอยา่งการประยกุตใ์ชฟ้งักช์นั

45

46

ฟงักช์นักลุม่ Logic (1)• เงื�อนไข

• IF เดี�ยวๆ=IF(เงื�อนไข, จรงิ, เท็จ)

• IF ซอ้น IF=IF(เงื�อนไข 1, คําตอบ 1,IF(เงื�อนไข 2, คําตอบ 2,IF(เงื�อนไข n, คําตอบ n, คําตอบนอกเหนอืจากนั�น)))

47

ฟงักช์นักลุม่ Logic (2)

• IF(OR(เงื�อนไข1, เงื�อนไข2, เงื�อนไขn), จรงิ, เท็จ)

• IF(AND(เงื�อนไข1, เงื�อนไข2, เงื�อนไขn), จรงิ, เท็จ)

48

ฟงักช์นักลุม่ Logic (3)

• COUNTIF(ชว่งขอ้มลู, เงื�อนไข)

• COUNTIFS(ชว่งขอ้มลู, เงื�อนไข, ชว่งขอ้มลู, เงื�อนไข)

49

ฟงักช์นักลุม่ Lookup & Ref• VLOOKUP : ค้นหาข้อมลูที ตรงตามเงื อนไขที กําหนด

=VLOOKUP(Key, Range, Col_Num, 0/False)

• MATCH : ค้นหาลําดบัที ของข้อมลู=MATCH(Key, Range)

• INDEX: ค้นหาข้อมลูด้วยลําดบัที ของข้อมลู=INDEX(Range, Index_Num)

ทดสอบฟงักช์นั VLOOKUP

50

=VLOOKUP(เงื�อนไข, ตาราง, Col_Num, 0)

ทดสอบฟงักช์นั HLOOKUP

51

=HLOOKUP(เงื�อนไข, ตาราง, Row_Num, 0)

ฟงักช์นั VLOOKUP & ISNA & IF

52

=ISNA(VLOOKUP(…))

=IF(ISNA(VLOOKUP(…)) , ไมม่ ี, ม)ี

ทดสอบฟงักช์นั MATCH & INDEX

53

=MATCH(เงื�อนไข, ชว่งขอ้มลู, 0)

=INDEX(ชว่งขอ้มลู, ลําดบัที�)

ตวัอยา่งการประยกุตใ์ชฟ้งักช์นัชว่ยบรหิารจดัการขอ้มลู

54

ตวัอยา่งที� 1

55


56


57

=TEXT(ขอ้มลู, รปูแบบ)


58

โจทยค์อื ตอ้งการคํานําหนา้ชื�อ โดยมเีงื�อนไขวา่1. ถา้ซา้ยมอืหกตวัเป็นนางสาวใหโ้ชวน์างสาว2. ถา้ซา้ยมอืสามตวัเป็นนางใหโ้ชวน์าง3. ถา้ซา้ยมอืสามตวัเป็นนายใหโ้ชวน์าย

ตวัอยา่งที� 5 (1)

59

ตวัอยา่งที� 5(2)

60

=SUBSTITUTE(ขอ้มลู, คน้หาอะไร, แทนที�ดว้ย)

ตวัอยา่งการประยกุตใ์ช้ฟงักช์นั

รว่มกบัคาํส ั ง Conditional Formatting

61


62


63


64

ตวัอยา่งการประยกุตใ์ช้ฟงักช์นั

รว่มกบัคาํส ั ง Data Validation

65

ตวัอยา่งที� 1: ป้องกนัการป้อนขอ้มลูซํ�า

66


67

ตวัอยา่งที� 3: Dropdown ซอ้น Dropdown

68

ตวัอยา่งที� 3 (วธิคีดิ)

69

Using formula auditing

• Trace Precedents• Trace Dependents• Trace Error• Show Formulas• Evaluate Formula• Watch Window• Calculation

70

Trace Precedents

71

Step 1

Step 2

Step 3

Trace Dependents

72

Step 1

Step 2

Step 3

Trace Error

73

Step 1

Step 2

Step 3

Show Formulas

74

Step 1

Step 2

Step 3

Evaluate Formula

75

Step 1

Step 2

Step 3

Watch Window

76

Step 1Step 2

Step 3

Calculation

77

สว่นที 3: การบรหิารจดัการขอ้มลู

• Database Conceptual• Using Advanced Filter• Using Advanced Sort

• Using Data Subtotals/Group & Outline• Selecting visible cell only

78

Database Conceptual

79

Tips & Tricks: Fill Blanks

80

ข ั�นตอน1. F5 -> Special

->Blanks-> OK2. พมิพ ์=3. กดปุ่ ม 4. กดปุ่ ม Ctrl Enter

Using Text to Columns

81

Using Auto Filter + Subtotal Function

82

Using Advanced Filter+Unique record only

83

Using Remove Duplicate

84

Using Advanced Sort(Custom List)

85

Using Data Group & Outline

86

Using Data Subtotals

87

Using Data Subtotals

88


Using Data Consolidate

89

Using Data Consolidate

90

ขั �นตอน1. เลอืก Sheet วา่งๆ2. คลกิปุ่ ม

3. Function: Sum4. Reference:

Table1Table2

Selecting visible cell only

1.1. เลือกข้อมลู

2. F5 -> Special

3. เลือก Visible Cells Only

4. คดัลอก (Ctrl C)

5. วางข้อมลู (Ctrl V)

91

สว่นที 4: การสรา้งกราฟ

92

• แนวคดิเกี�ยวกบัการสรา้งกราฟ• ขั �นตอนการสรา้งกราฟ• เครื�องมอื Chart Tools• การบนัทกึกราฟเป็น Template• ตวัอยา่งการประยกุตใ์ชง้านกราฟ

แนวคดิเกี�ยวกบัการสรา้งกราฟ

93

ขั �นตอนการสรา้งกราฟแบบงา่ยๆ

94

การสรา้งกราฟดว้ย Short cut key

F11 Alt F1

95

เครื�องมอืกราฟ

96

เครื�องมอืกราฟ: Design

97

เครื�องมอืกราฟ: Layout

98

เครื�องมอืกราฟ: Format

99

เครื�องมอืกราฟ: Short cut

100

เครื�องมอืกราฟ: Short cut (ตอ่)

101


102


103

การบนัทกึกราฟเป็น Template

104

ตวัอยา่งการประยกุตใ์ชง้านกราฟ (1)

105


106


107


108


109


110

สว่นที 5: การประยกุตใ์ช ้PivotTable

• แนวคดิเกี�ยวกบั PivotTable• ประโยชนข์อง PivotTable• แหลง่ขอ้มลูของ PivotTable• เครื�องมอื PivotTable Tools• ตวัอยา่งการประยกุตใ์ชง้าน PivotTable

111

PivotTable/PivotChart

112

PivotTable

PivotChart

PivotTable: แนวความคดิ

113

ประโยชนห์ลกัของ PivotTable

114

ผูข้ายมกีี�คน ใครบา้งผูข้ายแตล่ะคน ขายไดย้อดรวมเทา่ไหร่หนังสอืกลุม่ใด มยีอดขายสงูสดุตอ้งการสรปุยอดขายเป็น %ตอ้งการสรปุยอดขายตาม ปี, ไตรมาส, เดอืน

PivotTable: แหลง่ขอ้มลู

115

ขั �นตอนการทํา Pivot: ขอ้มลูภายใน

1. เปิดไฟล ์Excel -> เลอืกขอ้มลู2. คลกิรบิบอน Insert -> PivotTable

116

ขั �นตอนการทํา Pivot: ขอ้มลูภายนอก

1. เปิดโปรแกรม Excel2. คลกิรบิบอน Insert -> PivotTable

117

วธิกีารสรา้ง PivotTable

118

เครื�องมอื PivotTable Tools

119

PivotTable Tools

120

ตวัอยา่งการประยกุตใ์ชง้าน

PivotTable

121

ตอ้งการดชูื�อผูข้ายแบบ Unique

122

ตอ้งการด ูSum of ยอด

123

ตอ้งการด ูSum , Average of ยอด

124

ตอ้งการด ู% of Total ของยอด

125

ขั �นตอนการเลอืก % of Total ของยอด

126

ตอ้งการด ู% of Row

127

ขั �นตอนการเลอืก % of Row

128

ตอ้งการด ู% of Column

129

ขั �นตอนการเลอืก % of Column

130

ตอ้งการด ู% of เกง่กาจ

131

ขั �นตอนการเลอืก % of เกง่กาจ

132

ทดสอบ PivotTable 1

133

อยากทราบวา่ผูข้ายแตล่ะคน ขายไดย้อดรวมเทา่ไหร่


134

อยากทราบวา่ผูข้ายแตล่ะคน ขายหนังสอืกลุม่อะไรบา้ง ไดย้อดรวมเทา่ไหร่


135

ตอ้งการเปลี�ยนฟังกช์นัการคํานวณ


136

ตอ้งการยา้ย/เปลี�ยนมมุมองขอ้มลู


137

ตอ้งการเปลี�ยนการแสดงผลเป็น % of total


138

ตอ้งการดสูรปุเป็น ปี/ไตรมาส


139

ตอ้งการ Group วนัที�เป็น ปี/ไตรมาส


140

ตอ้งการ Group ตวัเลข


141

ตอ้งการดรูายละเอยีดของตวัเลขสรปุ


142

ตอ้งการเปลี�ยน Layout ของ Report


143

ตอ้งการเปลี�ยนรปูแบบของตาราง


144

ตอ้งการแสดงPivotChart รว่มดว้ย

ทดสอบ PivotTable 13เพิ�มฟิลดค์ํานวณ

145

ทดสอบ PivotTable 13เพิ�มฟิลดค์ํานวณ (ตอ่)

146

สว่นที 6::::การพฒันาระบบงานอัตโนมัติด้วย MMMMAAAACCCCRRRROOOO AAAANNNNDDDD VVVVBBBBAAAA

147

เรยีนรู้พ ืนฐานการทาํงานกบั MACRO

� ทาํความรู้จกั Macro

� การเตรยีมความพร้อมก่อนทาํ Macro

� วธิกีารบนัทกึ/ทดสอบ/ใชง้าน Macro

� วธิกีาร Run Macro

� LAB

148148

ทาํความรู้จกักบั MACRO

�Macro คอื......................................................................................................

�Macro คอื......................................................................................................

� ประโยชนข์อง Macro คอื..................................................................................

�Macro เหมาะกบังานที).......................................................................................

� พ ืนฐาน/ความเขา้ใจก่อนไปถงึข ัน Macro

149

ความรู้พ ืนฐาน

� การใชง้าน Function

� การใชง้านคาํส ั)งในชุด Data

� การใชง้าน Pivottable

150

การเตรยีมพร้อมก่อนเริ)มต้นทาํ MACRO

151

การเตรยีมพร้อมก่อนเริ)มต้นทาํ MACRO

� บนัทกึไฟลเ์ป็น Macro File

� เปิดใชง้าน Tab เครื)องมอื Developer

� การกาํหนด Macro Security

� สร้าง Macro ได้ก ี)วธิี

152

บนัทกึไฟลเ์ป็น MACRO FILE

� Office Button -> Save as

� หรอื Office Button -> Excel Options

153

เปิดใชง้านเครื)องมอื DEVELOPER

154

Office Button -> Excel Options

ภาพรวมเครื)องมอื DEVELOPER

155

การกาํหนด MACRO SECURITY เพ ื)อใชง้านคาํส ั)ง MACRO

156

สร้าง MACRO ได้ก ี)วธิี

157

การบนัทกึคาํส ั)งด้วยเครื)องมอื

RECORD MACRO

158

ชนดิของ MACRO

� Absolute � Relative

159

แนวคดิเรื)องการบนัทกึ MACRO

� คดิงาน

� ซกัซ้อม

� บนัทกึ

� ทดสอบ

� นาํไปใชง้าน

160

ข ันตอนบนัทกึ MACRO

1. กดปุ่ม

2. ทาํงานที)ต้องการ


161

เกบ็ MACRO ไว้ท ี)ไหน

162

เกบ็ MACRO ไว้ท ี)ไหน (ต่อ)

�Personal macro Workbook

C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART

�New Workbook

�This Workbook

163

ทดสอบบนัทกึ MACRO 3 ตวั

� Macro 1 เกบ็ไว้ท ี) This Workbook

� Macro 2 เกบ็ไว้ท ี) Personal Macro Workbook

� Macro 3 เกบ็ไว้ท ี) New Workbook

164

ทดสอบการ RUN คาํส ั)ง MACRO

165

ทดสอบการ DELETE คาํส ั)ง MACRO

166

การลบ MACRO ที)อยู่ใน PERSONAL.XLSX

167

เนื�องจาก Personal.xls (เป็นไฟลส์งวนไว ้ใชค้ําสั�ง Macro แบบสว่นรวม) ที�ถกูเปิดอตัโนมัต ิตอนเปิดโปรแกรม Excel !!!แต่จะถกูซอ่นหนา้ตา่งไวอ้ตัโนมัติ

***ดงันั�นหากตอ้งการลบ Macro จะตอ้งยกเลกิการซอ่นกอ่น (Unhide Window) แลว้จงึลบ Macro

***หมายเหต:ุ เมื�อลบ Macro เสร็จแลว้ตอ้งสั�ง ซอ่นหนา้ตา่ง (Hide Window) ดว้ย

ความสมัพนัธ์ระหว่าง MACRO กบั VBA

168

วธิกีาร RUN MACRO

� วธิที ี) 1: Developer -> Macros

� วธิที ี) 2: Shortcut key

� วธิที ี) 3: Button

� วธิที ี) 4: Object บนชตี

� วธิที ี) 5: Quick Access Toolbar

� วธิที ี) 6: Tab Ribbon

169

วธิที ี) 1: คลกิปุ่ม MACROS

170

วธิที ี) 2: SHORTCUT KEY

171

วธิที ี) 3: BUTTON

172

วธิที ี) 4: OBJECT บนชตี

173

วธิที ี) 5: QUICK ACCESS TOOLBAR

174

เริ)มต้นก่อนการเขยีน VBA

� หนา้ต่าง VBA

� การต ังภาษาเพ ื)อใชใ้นการเขยีนโปรแกรม

175

หนา้ต่าง VBA

176

การต ังภาษาเพ ื)อใชใ้นการเขยีนโปรแกรม

177

เขยีนโปรแกรมที)ไหนได้บา้ง

178

กา้วสู่ VBA แบบเตม็ตวั

179

ก้าวสู่ โลกVBA ต้องเรยีนรู้เรื)องอะไรบา้ง

� เรยีนรู้เรื)อง Procedure

� เรยีนรู้เรื)อง OOP

� เรยีนรู้เรื)อง Constant/Variable

� เรยีนรู้เรื)อง Statement Control

� เรยีนรู้เรื)อง Bug/Debug

� เรยีนรู้เรื)อง User Interface

� เรยีนรู้เรื)อง การเขยีนโปรแกรมเช ื)อมต่อขอ้มูลภายนอก

180

LAB: MACRO

181

TEST-MACRO-1.XLSM

คลกิปุ่มบนัทกึ Record Macro ต ังช ื)อ Macro ว่า

MySummary1

1. คลกิเลอืกชตี Database

2. เลอืกคอลมัน ์J (ยอด) แลว้ต ังช ื)อกลุม่เซลลโ์ดยต ังช ื)อตามหวัตาราง

3. คลกิเลอืกชตี Summary

4. คลกิเลอืกเซลล ์B2 แลว่ใส่สูตร =SUM(ยอด) แลว้กดปุ่ม Enter

5. คลกิเลอืกเซลล ์B5 แลว่ใส่สูตร =MAX(ยอด) แลว้กดปุ่ม Enter

คลกิปุ่ม Stop Recording

182

TEST-MACRO-2.XLSM


MyAddColumn1

1. คลกิเลอืกชตี Product

2. คลกิเลอืกเซลล ์ A2 ตามด้วยกดปุ่ม Ctrl Shift

3. ต ังช ื)อว่า MyProduct (พมิพต์รงช่อง Name box) แลว้กดปุ่ม

Enter

4. คลกิเลอืกชตี Order

5. คลกิเลอืกเซลล ์H2 แลว่ใส่สูตร =VLOOKUP(G2,

MyProduct,2,0)

6. คดัลอกสูตรจากเซลล ์H2 จนถงึเซลล ์H11 183

TEST-MACRO-3.XLSM


MyAddColumn2

1. คลกิเลอืกชตี Product


3. ต ังช ื)อว่า MyProduct (พมิพต์รงช่อง Name box) แลว้กดปุ่ม

Enter


5. คลกิเลอืกเซลล ์H2 แลว่ใส่สูตร =VLOOKUP(G2,

MyProduct,2,0)

6. คลกิเลอืกเซลล ์G2 ตามด้วยกดปุ่ม Ctrl Shift 184

TEST-MACRO-3.XLSM (ต่อ)

คลกิปุ่ม Macros -> เลอืก MyAddColumn2 -> Edit

185

TEST-MACRO-4.XLSM


CopytoDatabase



3. กดปุ่ม Ctrl C

4. คลกิเลอืกชตี Database

5. คลกิเลอืกเซลล ์A1 ตามด้วยกดปุ่ม Ctrl

6. คลกิปุ่ม Use Relative Reference


8. กดปุ่ม Ctrl V

Stop Recording

186

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

Education

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