Transcript เอกสารอบรม Excel 2010 - สำนักวิทยบริการและเทคโนโลยีสารสนเทศ

27 – 29 เมษายน 2554
โดย สานักวิทยบริการและเทคโนโลยีสารสนเทศ
Microsoft Excel เป็ นโปรแกรมประเภท “ตารางคานวน”
หรือ “Spreadsheet” มักใช้ในการคานวนทางธุรกิจ ซึง่
จะช่วยลดขัน
้ ตอนความยุงยากและลดเวลาการค
านวน และ
่
ยังสามารถสรุปและวิเคระหข
เพือ
่
้ ลในรูปแบบตางๆ
่
์ อมู
ช่วยในการตัดสิ นใจ
สามารถดูคุณลักษณะและคุณสมบัตใิ หมๆของ
Excel
่
version 2010 ไดที
้ ่
http://office.microsoft.com/thth/excel/HA101806958.aspx
สาหรับ version 2010 นั้นไดปรั
่ นหน้าตาตาก
้ บเปลีย
version 2007 เพียงเล็กน้อย โดยเปลีย
่ นปุ่ม Office
Button กลับเป็ น Menu File แตยั
่ งคงรูปแบบ Ribbon
ไว้
2010
2007
Quick Access
Tool Bar
Menu
File
Title Bar
Ribbon
Name
Box
Formula Bar
Scroll Bar
Worksheet
Status
Bar
Sheet
Tabs
View
Buttons
Zoom
Slider
Wookbook (Excel
File)
Wookshee
t1
Wookshee
t2
Wookshee
t3
And more..
Name of Active
Cell
Columns
Active
Cell
Rows
New Worksheet
Button


Auto Complete ช่วยในการป้อนขอมู
้ ลซา้ ๆกัน
Auto Fill ช่วยในการป้อนขอมู
้ เช่น
้ ลทีเ่ ป็ นลาดับขัน
ตัวเลข หรือ เดือน
1.
2.
3.
4.
5.
เลือก Cell เดียว
เลือกกลุม
่ ยูติ
่ Cell ทีอ
่ ดกัน
เลือกกลุม
่ Cell ทีไ่ มติ
่ ดกัน
เลือก Cell ทัง้ หมดใน Column หรือ
Row
เลือก Cell ทัง้ หมดใน Worksheet
1.
2.
3.
4.
การเลือกใช้ Worksheet
การเปลีย
่ นชือ
่ Worksheet
การเพิม
่ Worksheet
การลบ Worksheet
1.
2.
3.
4.
การปรับขนาดความ กวาง/สู
ง ของ Cell
้
การแทรก Cell, Column, Row
การลบ Cell, Column, Row
การซ่อน Column, Row
 Normal
มุมมองปกติ ทีใ่ ช้งาน Excel
 Page Layout
มุมมองเอกสารกอนพิ
มพ ์
่
 Page Break Preview
มุมมองทีช
่ ่ วยจัดหน้าและขอบเขตการพิมพ ์
1.
2.
3.
4.
5.
การผสาน Cell (Merge Cell)
กาหนดรูปแบบขอความและตั
วเลข
้
(Font & Number)
การจัดวางตาแหน่งขอมู
้ ลภายใน Cell
(Alignment)
การตกแตง่ Cell (Cell Styles)
การตกแตงตารางอั
ตโนมัต ิ (Format as
่
Table)
 Merge
& Center
 Merge & Across
 Merge Cell
 Unmerge Cell
าความ
การใส่สูตรคานวนใน Excel มีส่ิ งทีต
่ องท
้
เขาใจดั
งนี้
้
1. กฎเกณฑเกี
่ วกับการคานวน
์ ย
2. ลาดับความสาคัญของเครือ
่ งหมาย
3. เครือ
่ งหมายคานวนทางคณิตศาสตร ์
(Arithmetic Formula)
4. เครือ
่ งหมายการเชือ
่ มขอความ
(Text
้
Formula)
5. เครือ
่ งหมายในการเปรียบเทียบ
(Comparison Formula)
ม
่ ตน
 เมือ
่ ใช้สูตรในการคานวนจะตองเริ
้
้
ดวยเครื
อ
่ งหมาย (=) เสมอ
้
 การคานวนจะขึน
้ อยูกั
่ บลาดับความสาคัญ
ของเครือ
่ งหมาย ถาความส
าคัญเทากั
้
่ น
จะคานวนจากซ้ายไปขวา
 ขอความที
อ
่ ยูในสู
ตรคานวนจะตองใส
้
่
้
่
เครือ
่ งหมาย (“”) ครอบขอความเสมอ
้
เครื่องหมาย
ความหมาย
ตัวอย่างสูตร
+
*
/
%
^
บวก
ลบ
คูณ
หาร
เปอรเซ็
์ นต ์
ยกกาลัง
=100-3*8+105^2
ผลลัพธเท
์ ากั
่ บ
61
ลาดับ
เครื่องหมาย
1
2
3
4
5
()
%
^
* และ /
+ และ -
เครื่องหมาย
ความหมาย
&
เชือ
่ มขอความ
้
เครื่องหมาย
ความหมาย
=
>
<
>=
เทากั
่ บ
มากกวา่
น้อยกวา่
มากกวาหรื
อ
่
เทากั
่ บ
น้อยกวาหรื
อ
่
เทากั
่ บ
ไมเท
่ ากั
่ บ
<=
<>
ตัวอย่างสูตร
=a1>=100
ถา้ a1 มีคา่
มากกวาหรื
อ
่
เทากั
่ บ 100 จะ
ไดผลลั
พธ ์ True
้
แตถ
่ า้ a1 มีคา่
น้อยกวา่ 100
จะไดผลลั
พธ ์
้
False
เครื่องหม ความหมาย
าย
(:) (โค
ลอน)
( ) (เวน้
วรรค)
(,)
ตัวอย่างสูตร
a1:b5
เลือกช่วงขอมู
่ ยูติ
้ ลทีอ
่ ดกัน หมายถึงช่วง Cell ตัง้ แต่
a1 ถึง b5
a1:c1 c1:c3
หมายถึงช่วง Cell ทีซ
่ า้
เลือกเฉพาะขอมู
กันของช่วง Cell ตัง้ แต่
้ ลซา้
(Intersection)
a1 ถึง c1 และ c1 ถึง
c3
ฉนั้นผลลัพธคื
์ อ c1
a1:c1, b5:b10
เลือกช่วงขอมู
หมายถึงเลือกช่วง Cell
้ ลทีไ่ มอยู
่ ่
่ ่ อง
 การป้อนโดยตรงทีช
Cell
 การป้อนบน Formula Bar
 การป้อนสูตรโดยใช้เมาส์
 การแกไขสู
ตรให้เลือก Cell ทีต
่ องการแล
ว
้
้
้
กด F2 หรือ ดับเบิล
้ คลิก
 สามารถคัดลอกจาก
รูปแบบเช่น
Cell ไดหลาย
้
◦ การคัดลอกทุกอยางของ
Cell ทัง้ สูตร
่
ขอมู
้ ล และรูปแบบ
◦ คัดลอกเฉพาะขอมู
้ ล
◦ คัดลอกเฉพาะสูตร
◦ คัดลอกเฉพาะรูปแบบ
 คัดลอกสูตรโดยใช้
Auto Fill
บการใช้สูตรคานวน
การใช้ Function คลายกั
้
ทางคณิตศาสตร ์ โดยมีรป
ู แบบการใช้งานคือ
= function(argument1,
argument2, ...)
ตัวอยางเช
่
่ น =SUM(A1:D10)
การใช้งานสามารถป้อน Function ลงใน
Formula Bar เหมือนกับการป้อนสูตรคานวน
หรือใช้ Function Wizard ช่วย
Function
รูปแบบ
SUM(กลุม
่ Cell)
AVERAGE(กลุม
่
Cell)
COUNT
COUNT(กลุม
่
Cell)
MAX
MAX(กลุม
่ Cell)
MIN
MIN(กลุม
่ Cell)
BAHTTEXT BAHTTEXT(Ce
ll ตัวเลข)
SUM
AVERAGE
การใช้งาน
ผลรวมจากกลุม
่ Cell
ย
่ จากกลุม
คาเฉลี
่ Cell
่
นับจานวน Cell จาก
กลุม
่ Cell
คาสู
่ งสุดจากกลุม
่ Cell
คาต
่ า่ สุดจากกลุม
่ Cell
เปลีย
่ นตัวเลขจาก Cell
เป็ นตัวอักษรแสดง
จากคุณสมบัต ิ AutoFill นั้นจะทาให้การคัดลอกสูตรมีการ
เปลีย
่ นแปลงตาแหน่งของ Cell ทีใ่ ช้อางอิ
ง เช่นจาก
้
b1 ไปเป็ น b2 หรือ b1 ไปเป็ น c1 หากตองการตรึ
ง
้
ตาแหน่ง Cell ทีใ่ ช้อางอิ
งไมให
่ นแปลงเวลาคัดลอก
้
่ ้เปลีย
สูตรทาไดดั
้ งนี้
ให้ใช้เครือ
่ งหมาย $ ในการตรึง Column หรือ Row
เช่น
 $a$1
จะเป็ นการตรึงทัง้ Column(a) และ
Row(1)
 b$6
จะเป็ นการตรึงเฉพาะ Row(6)
 $c3
จะเป็ นการตรึงเฉพาะ Column(c)
ข้อผิดพลาด
####
#VALUE!
#NAME?
#REF!
สาเหตุ
วิธีแก้ไข
ตัวเลขใน Cell ยาวกวา่
ขนาด Column
ขยายความกวาง
้
Column
ใช้สูตรผิดหลักไวยากรณ์
เช่น =a1+a2 โดยที่ a1
หรือ a2 ไมใช
่ ่ ตัวเลข
ในสูตรคานวนมีส่วน
ขอความที
่ Excel ไมรู่ จั
้
้ ก
เช่นพิมพ ์ Function ผิดจาก
sum เป็ น som
ตรวจสอบประเภท
ของขอมู
้ ลใน Cell
ไมพบต
าแหน่ง Cell ที่
่
อางอิ
ง
้
ตรวจสอบตาแหน่งที่
อางอิ
ง
้
ตรวจสอบชือ
่
Function
รูปแบบการใช้ SUMIF
=SUMIF( range, criteria, sum_range )
ตัวอยางการใช
่
้งาน
 ตองการผลรวมของราคาสิ
นค้ายีห
่ ้อใดยีห
่ ้อหนึ่งสามารถ
้
เขียนสูตรไดดั
้ งนี้
=SUMIF( ช่วง Cell ยีห
่ ้อสิ นค้า, “ชือ
่ ยีห
่ ้อ”, ช่วง Cell
ของราคาสิ นค้า )
 ตองการผลรวมของราคาสิ
นค้าทีม
่ รี าคามากกวา่ 10,000
้
บาทสามารถเขียนสูตรไดดั
้ งนี้
=SUMIF( ช่วง Cell ของราคาสิ นค้า, “>10000” )
รูปแบบการใช้ COUNTIF
=COUNTIF( range, criteria )
การใช้งานคลายๆกั
บ SUMIF แตเป็
้
่ นการนับจานวนจึง
ไมจ
argument sum_range เช่น
่ าเป็ นตองมี
้
 ตองการนั
บจานวนของราคาสิ นค้ายีห
่ ้อใดยีห
่ ้อหนึ่ง
้
สามารถเขียนสูตรไดดั
้ งนี้
=COUNTIF( ช่วง Cell ยีห
่ ้อสิ นค้า, “ชือ
่ ยีห
่ ้อ” )
 UPPER()
ใช้แปลง Cell ขอความตั
วอักษรให้เป็ น
้
ตัวพิมพใหญ
่
์
 LOWER()
ใช้แปลง Cell ขอความตั
วอักษรให้เป็ น
้
ตัวพิมพเล็
์ ก
เป็ น Function ทีต
่ รวจสอบเงือ
่ นไขทีต
่ ง้ั ไวว
้ า่ เป็ นจริง
(true) หรือเป็ นเท็จ(false) และสามารถกาหนดตอไปได
่
้
วาถ
่ าเป็
้ นจริงให้ดาเนินการอะไร ถาเป็
้ นเท็จให้ดาเนินการ
อะไร
รูปแบบการใช้ IF
=IF( logical_test, value_if_true,
value_if_false )
ตัวอยางการใช
่
้งาน
าเท
 ถานั
่ บ 50 ถือวาผ
่ ากั
้
้ กศึ กษาไดคะแนนรวมมากกว
่ าน
่
ถาน
้ ้ อยกวา่ 50 เทากั
่ บไมผ
่ าน
่
=IF( cell คะแนนรวม>=50, “ผาน”, “ไมผาน” )
VLOOKUP เป็ นฟังกชั
Column
่
์ นทีใ่ ช้ค้นหาคาจาก
แรกของตารางช่วง cell ทีเ่ ลือก ทีม
่ ค
ี าเดี
่ ยวกันหรือ
ใกลเคี
้ ยง
=VLOOKUP( lookup_value, table_array,
col_index_num, range_lookup )
 HLOOKUP เป็ นฟังกชั
Row แรก
่
์ นทีใ่ ช้ค้นหาคาจาก
ของตารางช่วง Cell ทีเ่ ลือก ทีม
่ ค
ี าเดี
่ ยวกันหรือ
ถ้ใกล
า range_lookup
เป็ น TRUE ค่าใน Column หรือ Row
เคี
้ ยง
=HLOOKUP(
lookup_value,
แรกของ
table_array
จะต้องเรียงลtable_array,
าดับจากน้ อยไปหามาก
row_index_num, range_lookup )


LOOKUP เป็ นฟังกชั
่
่ วง
์ นทีใ่ ช้ค้นหาและส่งคืนคาจากช
ของหนึ่ง Column หรือหนึ่ง Row หรือจาก Array และ
สามารถใช้แทน IF แบบหลายเงือ
่ นไข รูปแบบการใช้
งานดังนี้
=LOOKUP( lookup_value, lookup_vector,
result_vector
)
1.
คา่ lookup_vector
หรือ array จะตอง
้
=LOOKUP(
lookup_value,
array )
เรียงลาดับจากน
้ อยไปหามาก
2. การเขียน value, array เขียนไดดั
้ งนี้
a) { "a","b","c" }, { 1,2,3 }
b) { "a","b","c" ; 1,2,3 }
c) { "a",1; "b",2; "c",3 }
ตัวอยางการใช
่
้งาน VLOOKUP, HLOOKUP และ
LOOKUP
 ตองการราคาของสิ
นค้าเจาะจงรุน
้
่
=VLOOKUP( “ชือ
่ รุน”,
ช่วงตารางขอมู
่
้ ล, ลาดับ
Column ของราคา, FALSE )
 ตองการทราบเงิ
นเดือนปี 2553 ของเจ้าหน้าทีค
่ นหนึ่ง
้
=HLOOKUP( "ปี 2553", ช่วงตารางขอมู
้ ล, ลาดับ
Row ของเจ้าหน้าที,่ FALSE )
 ตองการทราบเงิ
นเดือนปี 2554 ของเจ้าหน้าทีท
่ ม
ี่ รี หัส
้
“s003”
=LOOKUP( “s003”, Column รหัสเจ้าหน้าที,่
Column เงินเดือนปี 2554 )
เมือ
่ เปลีย
่ นช่วง Cell เป็ นรูปแบบ Table แลวจะท
าให้
้
ไดรั
ิ เิ ศษเช่น
้ บคุณสมบัตพ
 การเรียงลาดับ และการกรองขอมู
้ ลสามารถทาได้
สะดวก
 สามารถสราง
Row ผลรวม (Total) อัตโนมัตโิ ดยไม่
้
ตองใส
้
่ สูตรคานวน ซึง่ สามารถเลือกใช้ Function การ
คานวนตางๆเพื
อ
่ หาผลรวม คาเฉลี
ย
่ และอืน
่ ๆได้
่
่
Microsoft Excel 2010 สามารถนาเขาข
้ ลจาก
้ อมู
ภายนอกไดหลายรู
ปแบบดวยกั
นเช่น
้
้
 Microsoft Access
 Website
 From Text (txt, csv)
 And more…
เป็ นการนาขอมู
่ เขามาค
านวน หรือ
้ ลจาก Sheet อืน
้
อาจจะสราง
worksheet หนึ่งทีน
่ าขอมู
้
้ ลจากหลายๆ
sheet เพือ
่ มาหาผลรวม
สามารถใช้ Consolidate Wizard ช่วย หรือป้อนสูตร
เพือ
่ อางอิ
ง Cell จาก sheet อืน
่ ดวยตนเอง
เช่น
้
้
=SUM(‘sheet1’!a1:a7)
Report
Filter
Pivot
Chart
กรองขอมู
้ ลจาก
คาใน
Cell
่
กรองแบบระบุ
เงือ
่ นไข
เมือ
่ การจัดการกับ Excel ทีม
่ ก
ี ารทาบอยๆ
เช่นการ
่
จัดรูปแบบ ซึง่ อาจะจะมีหลายขัน
้ ตอนตัง้ แตปรั
่ บขนาด
ความหนา และแกสี
้ ตอนเหลานี
้ สามารถนาขัน
่ ้มารวม
เป็ น Macro คาสั่ งเดียว จะช่วยให้สะดวกรวดเร็วยิง่ ขึน
้
1. Recording Macro
2. Run Macro
3. Assign Macro
4. Absolute / Relative Macro
5. Save / Delete Macro
การ Shared
Workbook เป็ นการ
อนุ ญาติให้แกไขไฟล
้
์
เดียวไดครั
้ ง้ ละมากกวา่
1 คน สามารถเก็บ
ขอมู
่ นแปลง
้ ลการเปลีย
ยอนหลั
งได้ แตก็
้
่ มี
ขอจ
่
้ ากัดหลายๆอยางที
่
เกิดจากการทางาน
รวมกั
นในไฟลเดี
่
์ ยว
เมือ
่ มีการใช้งานไฟลเดี
นหลายคน เวลาพบ
่
์ ยวกันรวมกั
ปัญหาจะทาให้ยากในการตรวจสอบ Excel จึงมีการ
ติดตามการแกไข
โดยสามารถระบุไดว
้
้ ามี
่ การแกไข
้
เมือ
่ ไหร่ โดยใคร และทีไ่ หน





Picture Effect
Picture Styles
Shape Styles
Adjust
Size & Crop
Select Chart
Type
Resul
t
การจาลองสถานการณสมมติ
เพือ
่ ทาการคานวนผลลัพธ ์
์
ของสถานการณต
เพือ
่ วิเคราะหผลที
แ
่ ตกตางกั
นใน
่
่
์ างๆ
์
แตละสถานณ
การ
่
์
เครือ
่ งมือใช้วิเคราะหว์ าต
คาตั
งจะได้
่ องมี
้
่ วแปรเทาใดจึ
่
เป้าหมายตามทีก
่ าหนดไว้
Q/A