CONTENT 1 1. การนาขอ้ มลู เข้าสู่โปรแกรม Excel (import data) 3 2. การจดั เรยี ง กรองข้อมลู และกรองข้อมูลขนั้ สูง (sort & filter) 8 3. การปรับแตง่ number format 9 4. การต้ังชอ่ื cell และใชช้ ่อื ในสูตรต่างๆ (define names) 11 5. ใชง้ านอยา่ งโปร กับ 10 Functions โคตรมปี ระโยชน์ 11 a. Cell References: Absolute vs. Relative 11 b. IF + AND + OR + NOT 13 c. IFS (2016) 13 d. IFERROR 14 e. COUNTA + COUNTIF/ COUNTIFS 14 f. SUMIF/ SUMIFS 15 g. AVERAGEIF/ AVERAGEIFS 15 h. MAX + MIN + LARGE + SMALL 16 i. VLOOKUP (HLOOKUP) 17 j. MATCH 17 k. INDEX 19 6. ไฮไลท์ขอ้ มลู ดว้ ย conditional formatting 21 7. การเปลยี่ น themes, colors, fonts ของ workbook 22 8. การลบข้อมลู ซา้ (Remove Duplicates) 23 9. การใช้งาน group + ungroup + subtotal 25 10. การสรา้ งตาราง (insert table)
11. การใช้งาน PivotTable เบื้องต้น 27 12. การใช้งาน PivotChart เบ้ืองต้น 29 13. เทคนคิ การสร้าง basic dashboard ขั้นเริ่มต้น 31 14. การกาหนดกฏเพื่อปอ้ งกนั การใสข่ อ้ มลู ผดิ (data Validation) 32 15. คานวณค่าทเี่ ราตอ้ งการดว้ ย what-if analysis 33 33 a. Goal Seek 34 b. Data Table 35 16. วเิ คราะหผ์ ลสถติ ิดว้ ย Analysis ToolPak 36 a. Sampling 38 b. Independent T-Test 40 c. Correlation Matrix 43 d. Linear Regression 47 17. การสรา้ ง form ดว้ ย developer tools 49 18. การปกป้อง worksheet ของเราดว้ ยพาสเวดิ
Chapter 1 การนาขอ้ มลู เขา้ สู่โปรแกรม Excel (import data) บทแรก มาเรียนการนาข้อมูลเข้าสู่โปรแกรม Excel กนั กอ่ นฮะ วิธงี ่ายทสี่ ุดคือ copy แลว้ paste หรอื ให้เราไปที่แทบ๊ Data > Get External Data > From Text ได้เลย กรณนี ีค้ ือเราจะนาเขา้ ข้อมลู จากไฟล์ Excel, CSV, txt อน่ื ๆ Excel จะปอ๊ ปอัพหน้าตา่ งใหม่ให้เรา browse หาไฟลท์ ่ีเราตอ้ งการ import ได้เลย เมื่อเราเจอไฟลท์ ่ีตอ้ งการแลว้ ใหเ้ รากด Import ได้เลยครับ Excel จะแสดง หน้าต่างข้างล่างข้นึ มา ใหเ้ ราเลอื กดงั นี้ • ปกติเราจะเลือกแบบ Delimited สาหรบั ไฟล์ขอ้ มูลแบบ text ท่สี ่วนใหญ่ ใช้ comma “,” ในการแบ่งคอลั่ม (separate columns) • ถ้าแถวทหี่ นงึ่ ของขอ้ มลู เราเป็นชอื่ ตัวแปรในแตล่ ะคอลม่ั ใหเ้ ราตก๊ิ ช่อง My data has headers ดว้ ยนะครบั เสรจ็ แล้วกด next ต่อได้เลย 1
หนา้ ถัดมา ดา้ นซ้ายบนของหน้าตา่ งจะมี Delimiters ใหเ้ ราเลือกได้ 5 แบบ วิธกี าร เลือกก็งา่ ยๆ ให้เราดทู ี่ Data preview ในชอ่ งด้านล่างได้เลย จะเหน็ ตอนน้ี ไฟลข์ อ้ มูลของเราใช้ comma “,” ในการแบ่งคอลม่ั อยู่ (ซ่งึ ปกตคิ อมม่าถือเป็นค่า เรมิ่ ตน้ สาหรับไฟล์ text ทั่วไป โดยเฉพาะไฟล์ CSV ท่ีเราใชก้ นั เยอะมาก) ใหเ้ ราตก๊ิ เลือก comma ตรง delimiters ได้เลยครบั แลว้ กด Finish Excel จะถามต่อวา่ เราจะเอาข้อมลู ไป วางไว้ตรงไหน สามารถเลอื กวางได้ท่ี Existing worksheet หรอื จะไปวางท่ี New worksheet เลยกไ็ ด้ เสรจ็ แลว้ กด OK ไดเ้ ลยครับ 2
Chapter 2 การจดั เรยี ง กรองขอ้ มูล และกรองขอ้ มลู ขั้นสูง (sort & filter) พอเรา import data เป็นแล้ว มาลองดวู ิธกี ารกรอง (filter) และจัดเรยี งข้อมูล (sort) กันดีกว่าครบั เริม่ จากการ Filter กอ่ น โดยเอาเม้าส์ไปคลิ๊กทีข่ ้อมลู ที่เรา import เข้ามาแลว้ ตรงไหนกไ็ ด้ แลว้ ไปทแ่ี ทบ๊ Data แลว้ กดท่ไี อคอน Filter เราจะเหน็ ปุม่ หวั ลกู ศรโผลข่ ึ้นมาที่แถวทห่ี นึง่ ท่เี ป็นชอ่ื ตวั แปรของเราทุกคอลมั่ เลย ดงั แสดงในรูปด้านล่าง ถ้าเรากดท่หี วั ลูกศร เราสามารถเลือกฟลิ เตอรข์ อ้ มูลเราได้ ง่ายๆเลยครบั ถดั มาเรามาลองจดั เรยี งขอ้ มูล Sort Data กันบ้างฮะ ไปทีแ่ ท๊บ Data เหมอื นเดิม เลย แลว้ กดท่ไี อคอน เพ่ือเรียงขอ้ มูลจากนอ้ ยไปมาก (แบบ Ascending) หรอื กด เพ่ือเรียงข้อมูลจากมากไปน้อย (แบบ Descending) แต่ถ้าอยากจะ Sort ข้อมูลแบบละเอียดและ Sort หลายๆเลเวล (หลายตัวแปร พร้อมกัน) ให้เรากดทไ่ี อคอน custom sort ไดเ้ ลยครับ Excel จะป๊อปอัพ หน้าตา่ งใหม่ขึ้นมา และ Excel จะไฮไลท์ขอ้ มูลทัง้ หมดของเราโดยอัตโนมตั ิ ถา้ แถว ท่ีหนึ่งเป็นช่อื ตัวแปร (ซ่ึงส่ วนใหญ่เป็นแบบนั้นอยู่แล้ว) ให้เราต๊กิ เลือก My data has headers ดว้ ยนะครับ 3
• กดปมุ่ Add Level เพ่ือเพิ่มเลเยอร์ในการจดั เรยี งขอ้ มลู โดย Level บนสุดจะสาคัญมากท่ีสุดและถกู เรยี งกอ่ น • ตรง Sort On เราสามารถเลือกได้ 4 แบบวา่ จะเรียงตาม Values (ค่า เริม่ ตน้ ), สีของเซลล์, สีของฟอ้ น หรอื วา่ ไอคอนในคอลั่มน้นั ๆกไ็ ด้ • ตรง Order เราสามารถเรยี งข้อมลู ในคอลั่มน้ันตามตวั อักษรได้ทัง้ แบบ ascending และ descending order หรือถ้าข้อมลู ในคอล่ัมนนั้ เป็นตัวเลข เรากส็ ามารถเรียงคา่ จากมากไปนอ้ ย หรือจากนอ้ ยไปมากไดเ้ ช่นกนั “ ถา้ เราอยากจะ Sort Columns เรยี งคอล่มั จากซ้ายไปขวา หรอื จาก ขวาไปซา้ ย ให้เรากดที่ป่มุ Options แล้วเลอื ก Sort left to right 4
มาลองดู Advanced Filter กนั บ้างฮะ อนั นเ้ี ปน็ เทคนิคข้ันสูงขน้ึ มานิดแตม่ ีประโยชนม์ ากๆ สมมตเิ รามีข้อมลู อยใู่ นสาม คอลั่ม A1:C20 โดยทแ่ี ถวท่หี น่ึงเปน็ ช่ือคอล่มั Fruit, Country, Price (ภาพ ซา้ ยมือ) ใหเ้ รากดคลิ๊กขวาทีแ่ ถวท่หี น่ึง แลว้ กด Insert เพื่อเพ่ิม blank row ซัก 4 แถวดา้ นบนข้อมลู ชดุ นี้ ดังภาพที่สองทางดา้ นขวามอื นะครับ ตอนนีเ้ ซลล์เปล่าๆ A1:C3 เราจะเขยี นกฏทีใ่ ชใ้ นการ Filter ขอ้ มลู ครับ และน่ีคอื ตัวอยา่ งของการทา Advanced Filter (รูปลา่ ง) เราเขียนกฏ/ฟลิ เตอรข์ ้ึนมาสอง ข้อครับ ข้อแรกอย่ใู นแถวท่ีสอง เราอยากได้ Apple ท่ีมาจากประเทศ USA ส่วน กฏ/ฟลิ เตอร์ของท่ีสองจะอยใู่ นแถวท่สี าม เราอยากได้ Grape ท่ีมาจากประเทศ Australia ราคามากกว่า $20 ขนึ้ ไป โดยท่ี Logic ของการเขยี นฟลิ เตอรน์ ้คี อื ฉันอยากฟลิ เตอร์ (Apple AND USA) OR (Grape AND Australia AND >20) โดย Excel จะเข้าใจว่าฟลิ เตอร์ท่ีอยบู่ น 5
แถวเดยี วกนั จะใช้ logic วา่ AND แตถ่ า้ อยูค่ นละแถว (เชน่ แถว 2 vs. แถว 3) Excel จะใช้ logic ว่า OR นะครับ ซึ่งการฟลิ เตอร์ขอ้ มลู ปกติจะทาโลจิคแบบ OR ไม่ได้ เมื่อเราเขยี นฟลิ เตอรเ์ สรจ็ แล้ว ให้เราเอาเมา้ ส์คลกิ ที่ตารางข้อมูลทเี่ ซลล์ A5 (จรงิ ๆ จะกดตรงเซลล์ไหนกไ็ ด้) แล้วไปท่ีแท๊บ Data > Advanced ตามรปู ด้านล่าง Excel จะเดง้ หน้าตา่ งใหม่ขึ้นมาแบบนี้ครบั List range จะคลุมข้อมูลของเรา ท้งั หมดที่อยู่ในเซลล์ A5:C24 ส่วน Criteria range ใหเ้ ราลากคลมุ Filter ทีเ่ รา เขียนมาตะกี้ ในเซลล์ A1:C3 ตามรปู ด้านล่าง เสรจ็ แลว้ กด OK ได้เลยครับ ลองดูผลลพั ธข์ อง Advanced Filter ไดใ้ นหน้าถัดไปเลย 6
Well Done! เสร็จเรียบรอ้ ย งานดมี ากๆ ตอนน้คี ณุ กรองข้อมูลขนั้ สูงเปน็ แลว้ ครบั ด้วยการใช้ Advanced Filter และความเข้าใจเร่ือง logic AND และ OR ถา้ อยากจะ Clear Filter กลบั ไปเหมอื นตอนแรก ให้เราไปท่ีแท๊บ Data แลว้ กด Clear นะครับ 7
Chapter 3 การปรบั แต่ง number format เราสามารถปรบั ฟอแมทของขอ้ มูลใน Excel ได้หลายแบบมากๆ ไม่วา่ จะเป็น number, currency, accounting, date ฯลฯ โดยไปทแ่ี ทบ๊ Home แล้วเอาเมา้ ส์ ไปคลิ๊กตรงลูกศรเล็กๆ (มมุ ขวาล่าง) ตรง Numbers ตามรปู ด้านล่าง หรือกดปุ่ม shortcut CTRL+1 เพ่ือเปดิ หน้าตา่ ง Format Cells Excel จะป๊อปอัพหนา้ ต่างใหมข่ ้ึนมาใหเ้ ราปรบั แต่ง Format Cells แบบละเอยี ด เราสามารถปรบั Alignment, Font, Border, Fill, Protection ไดเ้ ช่นกัน 8
Chapter 4 การต้งั ช่อื cell และใชช้ ื่อในสูตรตา่ งๆ (define names) รู้หมอื ไร่? เราสามารถตงั้ ช่ือ Cell ใน Excel ได้ด้วยนะ แล้วก็เอาช่ือเซลลพ์ วกน้นั ไป ใชเ้ ขยี น Formulas ตอ่ ไดเ้ ลย การตั้งชื่อเซลลท์ าได้ง่ายมากๆ ดูตวั อย่างข้างล่าง ครับ สมมติเรามเี งนิ เดอื นปี 2017 ของพนักงาน 5 คน ในชอ่ ง A2:A6 และเรา วางแผนปรับเงินเดอื นใหพ้ นกั งานทุกคนปีหน้า +10% ในช่อง D2 เราจะลองตงั้ ช่ือเซลล์ D2 วา่ “Increase2018” นะครบั ให้เราเอาเมา้ ส์ไปคล๊ิกที่ เซลล์ D2 แลว้ พิมพ์คาว่า “Increase2018” ในกล่องด้านซา้ ยมอื ไดเ้ ลย คราวนเ้ี รามาลองเขยี นสูตรกันบ้างครบั ในเซลล์ B2 พิมพ์วา่ =A2+A2*Increase2018 ได้เลยครับ แลว้ ลากสูตรลงไปถึงเซลล์ B6 9
ขอ้ ดีของการใช้ Name ในการเขียนสูตรคือเวลาเราอ่าน มนั จะเข้าใจงา่ ยมากๆเลย แทนที่จะเขียนสูตรปกตแิ บบน้ี =A2+A2*$D$2 แทนคา่ $D$2 ดว้ ย “Increase2018” จะ make sense กว่าเยอะเลย เสร็จแล้วครับ และนค่ี อื การตั้งช่ือ Cell ง่ายๆใน Excel และใชช้ ่ือเหล่านนั้ เพ่ือเขียน Formulas ต่างๆต่อไปนะครับ ถ้าอยากจัดการชือ่ ทงั้ หมดใน workbook ของเรา ใหเ้ ราเข้าไปท่แี ท๊บ Formula > Name Manager ตามรูปด้านลา่ งไดเ้ ลยครับ เรา สามารถ add, edit, delete ชอ่ื ตา่ งๆได้ง่ายๆ “ หลกั การตง้ั ชอ่ื มสี องข้อหลกั ๆ คอื หา้ มขึ้นต้นดว้ ยตัวเลข และหา้ มมี ชอ่ งว่าง (space) ในชอ่ื ท่เี ราต้ัง 10
Chapter 5 ใช้งานอย่างโปร กบั 10 Functions โคตรมปี ระโยชน์ มาถึงการเขียนสูตรและฟงั ช่ันต่างๆในโปรแกรม Excel กันแล้วครบั อยา่ งแรกทท่ี กุ คนต้องรคู้ อื การใช้ปุ่ม F4 ใหค้ ลอ่ งเพ่ืออ้างองิ cell ท่ีเราใชใ้ นการเขียนสูตร โดยใน Excel จะแบง่ การอา้ งอิงเซลล์ (reference) ออกเป็น 2 แบบ คือ 1. Absolute reference (กดปุ่ม F4 เพื่อใส่ $ หนา้ ช่ือเซลล)์ 2. Relative reference ถา้ เรากดปมุ่ F4 หลายๆครั้ง • 1 คร้งั เราจะได้ $A$1 แบบนี้เรียก Absolute คอื ล็อคเซลล์ A1 ค้างไวเ้ ลย ในสูตร (ลอ็ คทง้ั คอล่ัม A และลอ็ คแถวที่ 1) • 2 ครง้ั เราจะได้ A$1 แบบน้คี ือการลอ็ คแถวท่ี 1 แตค่ อล่มั เปล่ยี นได้ • 3 คร้ัง เราจะได้ $A1 แบบนคี้ อื การลอ็ คคอลัม่ A แตแ่ ถวเปลี่ยนได้ • 4 ครงั้ เราจะกลับไปสู่คา่ ปกติ A1 เฉยๆ ไม่มกี ารลอ็ คเซลลใ์ ดๆ เม่ือเข้าใจเรือ่ งการทา reference ใน Excel แลว้ เรามาหดั ใช้ฟงั ชัน่ ทส่ี าคญั ใน Excel กนั ดีกวา่ คู่มอื น้ีสอนจดั เต็มกับ TOP 10+ FUNCTIONS ท่ถี ้าใชเ้ ปน็ แลว้ คณุ จะทางานใน Excel ไดร้ วดเร็วข้ึนแน่นอน 1. IF ฟงั ช่นั ทน่ี า่ จะใชเ้ ยอะสุดแล้วครบั ใน Excel เราใช้ IF เพื่อสรา้ งเงือ่ นไข โดย syntax ของ IF เป็นแบบนฮ้ี ะ =IF(condition, if TRUE, if FALSE) 11
ในตัวอยา่ งเมือ่ ตะก้ี เราเขียนสูตรในช่อง E2 วา่ =IF(D2>30, TRUE, FALSE) ถ้า เกิดอายุของตัวละคนมากกว่า 30 ปีขน้ึ ไป ให้ Excel รีเทินคา่ TRUE กลบั มา แต่ถา้ เงอ่ื นไขไมเ่ ปน็ จรงิ (หรอื อายตุ า่ กวา่ หรอื เทา่ กับ 30 ป)ี ให้รเี ทินคา่ FALSE แทน “ สาหรับคา่ TRUE หรอื FALSE ในตวั อย่างด้านบน สามารถเปล่ียนได้ นะครบั ใหเ้ ปน็ คาพู ดกไ็ ด้ แตต่ อ้ งอยู่ในเคร่อื งหมาย “ “ ตวั อย่างเช่น =IF(D2>30, “Age greater than 30 years”, “Age lower or equal to 30 years”) เราสามารถใช้ AND, OR, NOT เพ่ือสร้าง LOGIC ได้ดว้ ยใน IF มาถึงการเขยี น Logic ใน Excel ทเ่ี ราใช้กนั บอ่ ยๆมีแคส่ ามตวั คอื AND, OR, และ NOT ปกตเิ ราจะใช้ฟงั ชน่ั พวกนีใ้ น IF statements ลองดตู วั อยา่ งดา้ นลา่ งครบั เราใช้ =IF(AND(Occupation = ”Super Hero”, Age >= 40), TRUE, FALSE) เพ่ือสรา้ งเงอ่ื นไขสองข้อที่ตอ้ งเกดิ ข้ึนพรอ้ มกัน column F ถึงจะแสดงค่า TRUE 1. Occupation ต้องเป็น Super Hero 2. AND และ Age ตอ้ งมากกว่าหรอื เทา่ กบั 40 ปี ผลทีไ่ ดค้ ือ colunm F จะแสดงคา่ TRUE เฉพาะช่อง F2 และ F4 เท่านัน้ ครับ 12
2. IFS (2016) ถา้ เราอยากจะเขยี น IF ซ้อน IF ตอ้ งทายังไงดี? ถา้ เราต้องมหี ลาย IF ในสูตรเดียว แบบนเี้ รียกวา่ Nested IFs ครับ ซ่ึง Excel เวอรช์ น่ั 2016 มฟี งั ชั่นใหม่ IFS ท่ี ชว่ ยใหก้ ารเขียน Nested IFs สะดวกขึ้นมาก ตัวอยา่ งดา้ นลา่ งเลยครบั พิมพ์สูตร ในช่อง E2 ว่า =IFS(Age<=30, “Young”, Age<=40, “Old”, Age<=50, “Mature”) เราเพิ่งเขียนสูตรที่มีท้ังหมด 3 เงื่อนไขด้วยฟงั ชัน่ IFS งา่ ยๆเลยฮะ ถา้ เป็น Excel version ตา่ กว่า 2016 เราสามารถเขยี น Nested IF ดว้ ยฟงั ชั่น IF ปกติกไ็ ดค้ รบั พิมพ์แบบน้ไี ด้เลย ในช่อง E2 =IF(Age<=30, “Young”, IF(Age<=40, “Old”, “Mature”)) 3. IFERROR เคยพิมพ์สูตรใน Excel แล้วเจอขอ้ ความ Error หรือเปล่าครับ? เช่อื ว่าทกุ คนตอ้ ง เคยเจอมาเหมือนกนั ถา้ เราใช้ IFERROR ไปครอบสูตรทข่ี น้ึ Error นั้น เราสามารถ ซ่อนคา่ Error หรอื แกไ้ ขข้อความนัน้ ไดไ้ ม่ยากเลย ดตู วั อย่างด้านลา่ งฮะ สมมติเรา พิมพ์สูตรแลว้ ขึ้น Error ว่า #DIV/0! เราสามารถพิมพ์สูตร IFERROR() ทบั ลงไป ได้เลยแบบนีค้ รบั =IFERROR( เซลลท์ ี่ขนึ้ error, “”) เพื่อแทนที่ขอ้ ความ error เหล่าน้นั ดว้ ย blank cell หรอื จะเปน็ ข้อความว่า “it’s error” แบบน้กี ็ได้ครับ 13
4. COUNTA + COUNTIF + COUNTIFS ถ้าอยากนบั cell ท่มี ขี อ้ มลู ท้ังหมดให้เราใช้ COUNTA แต่ถ้าอยากนับคา่ แบบมี เง่ือนไข เช่น คอล่ัม C มี Super Hero ก่ีคน? ใหเ้ ราใช้ COUNTIF แต่ถา้ อยากจะ นบั หลายๆคอลั่มพร้อมกัน เชน่ ในประเทศอังกฤษมีนักฟุตบอลกคี่ น? ใหเ้ ราใช้ COUNTIFS (มี S ต่อทา้ ยดว้ ย) G1 = 13 นบั จานวนเซลล์ทม่ี ีขอ้ มูลทงั้ หมดในคอลัม่ B G2 = 4 นบั จานวน Super Hero ทัง้ หมดในคอล่ัม C G3 = 2 นับจานวนนกั Football ท้ังหมดทอ่ี ย่ใู นประเทศ United Kingdom 5. SUMIF + SUMIFS ถา้ ทกุ คนใช้ COUNTIF / COUNTIFS เปน็ แล้ว การใช้ SUMIF (SUMIFS) ก็เปน็ เร่อื งงา่ ยๆแล้วฮะ โดย syntax ของ SUMIF คลา้ ยๆกบั ของ COUNIFS เลย แต่ เพ่ิมมาอีกหน่ีง argument คอื คอลั่มทอ่ี ยากให้มนั หาผลรวมใหเ้ รา ตวั อยา่ งครบั G5 = 142 หาผลรวมของอายใุ นคอลั่ม D ถ้าเกิดเป็นคนท่ีมาจากประเทศองั กฤษ G6 = 43 หาผลรวมของอายุ Professor ทม่ี าจากประเทศองั กฤษ 14
6. AVERAGEIF + AVERAGEIFS ใช้เหมอื นกบั SUMIF / SUMIFS ทุกอย่างเลยแตเ่ ปลย่ี นจากการหาผลรวมเปน็ การ หาคา่ เฉลย่ี แทนครบั ตวั อย่างดา้ นลา่ งเลย ในเซลล์ G8 และ G9 ตามลาดับ 7. MAX + MIN + LARGE + SMALL • MAX ใชแ้ สดงคา่ มากทส่ี ดุ ของขอ้ มลู เรา • MIN ใชแ้ สดงค่าตา่ ทสี่ ดุ ของขอ้ มูลเรา • LARGE ใช้แสดงค่ามากทีส่ ดุ ของขอ้ มลู เรา (เลือกตาแหนง่ ได้เองเลย) • SMALL ใชแ้ สดงคา่ ต่าท่สี ดุ ของข้อมูลเรา (เลอื กตาแหน่งได้เองเลย) ลองดตู ัวอยา่ งในเซลล์ G11:G14 ดา้ นล่างครับ เราเรยี งขอ้ มลู จากสูงไปต่าในคอล่มั D เพื่อความง่ายในการตรวจสอบสูตรรอบนนี้ ะครบั สาหรับ MAX/ MIN ไม่นา่ มี ปัญหาอะไร ส่วน LARGE ท่ีใช้ในเซลล์ G13 = LARGE(D:D, 3) คือดงึ คา่ มากสุดตัว ท่ีสามในคอลั่ม D ออกมาเท่ากับ 43 ส่วนในช่อง G14 = SMALL(D:D, 2) คอื ดึงค่าต่าสุดตัวท่ีสองในคอลม่ั D ออกมา เท่ากับ 21 (ดูจากขา้ งล่างขน้ึ บน) 15
8. VLOOKUP + HLOOKUP VLOOKUP เป็นฟงั ช่ันที่ใชเ้ ยอะรองจาก IF เลยก็ไดค้ รบั ใชใ้ นการ lookup คา่ ที่เรา ตอ้ งการจากตารางหรอื ฐานขอ้ มลู อื่นๆ โดยท่ี VLOOKUP จะไปดงึ คา่ จาก column ทีเ่ ราต้องการครบั (เพราะว่า V ย่อมาจาก Vertical หรือแนวตัง้ น่ันเอง) ลองดู ตวั อยา่ งการใชง้ านด้านลา่ งครับ เราจะให้ Excel ไปตามหา Yaiba ให้เรา แลว้ ดงึ คา่ Country, Occupation และ Age ของไยบะ ออกมาให้เราครบั G3 = Japan ไยบะมาจากญ่ปี ุน่ =VLOOKUP(G1, Table4, 2, FALSE) G4 = Samurai เปล่ยี นจากคอล่ัม 2 เปน็ 3 =VLOOKUP(G1, Table4, 3, FALSE) G5 = 26 แคเ่ ปลี่ยนจากคอล่มั 3 เป็น 4 =VLOOKUP(G1, Table4, 4, FALSE) อธิบายสูตร VLOOKUP จะมที ง้ั หมด 4 arguments โดยท่ี • Argument ที่ 1 คือค่าท่เี ราต้องการไปหา ในตวั อยา่ งคือ “Yaiba” • Argument ท่ี 2 คือตารางที่เราจะให้ Excel ไปดึงคา่ ให้เรา • Argument ที่ 3 คือคอลม่ั ในตารางน้ัน • Argument ที่ 4 ปกตเิ ราใช้ FALSE เพื่อบอก Excel ให้ดึงคา่ ที่เป็น Exact Match เท่านน้ั (คอื ช่อื Yaiba ตอ้ งตรงกนั 100% ถงึ จะดงึ ค่าออกมาได)้ ถา้ ใช้ VLOOKUP เปน็ แล้ว HLOOKUP ก็ใช้เหมือนเดมิ เลยครับ เพียงแตต่ อนนี้ เปล่ยี นจากการดงึ ค่าแนวตง้ั เปน็ ดึงค่าแนวนอนแทน (เพราะว่า H ย่อมาจาก Horizontal) แต่ฟงั ชัน่ นใี้ ช้ไมค่ ่อยบอ่ ยเลย เพราะ database ส่วนใหญไ่ ม่ได้ ออกแบบมาใหเ้ หมาะสาหรับการใช้ HLOOKUP สาหรับเพ่ือนๆท่สี นใจ กดเขา้ มาดู รายละเอียดของฟงั ชัน่ นีไ้ ด้ทล่ี ิ้งนเี้ ลยครับ https://goo.gl/cjZO8V 16
มาถงึ สองฟังชัน่ สุดทา้ ยแลว้ อนั นี้ Advanced ข้นึ มาหนอ่ ย ตอ่ ยอดมาจากฟงั ช่นั VLOOKUP เรามาเรยี นการใช้งาน MATCH คกู่ ับ INDEX เพื่อดึงคา่ ทกุ อย่างทเี่ ราต้องการกนั ดกี วา่ สองสูตรน้ียดื หยุ่นกว่าการใช้ VLOOKUP มาก เพราะว่าข้อจากดั ของ VLOOKUP คือการดงึ ค่าในตารางท่ีมี คอล่ัมอยทู่ างขวามอื ของ Lookup Value เท่านัน้ แตจ่ ะทายงั ไงถ้าเกดิ คา่ ท่ีเรา อยากดงึ ออกมาอยู่ทางซ้ายมือของ Lookup Value? ลองดวู ิธีทาขา้ งล่างเลยฮะ 9. MATCH เราใช้ MATCH เพ่ือระบุตาแหนง่ ของ Lookup Value ใน column (หรือ row นนั้ ๆ) ดูตวั อย่างการใช้ในรปู ด้านล่างได้เลยครบั ตัวอย่างน้เี ราจะหาตาแหน่งของ Yaiba และ Iron Man ในเซลล์ B2:B13 H2 = 8 แปลวา่ Yaiba อยใู่ นตาแหน่งที่ 8 ในเซลล์ B2:B13 H5 = 4 แปลว่า Iron Man อยู่ในตาแหนง่ ท่ี 4 ในเซลล์ B2:B13 มาลองดู syntax ของฟงั ชัน่ MATCH กนั นดิ นึงฮะ มนั จะมีอยู่ 3 Arguments • Argument ท่ี 1 คอื Lookup value ทีอ่ ยากให้ Excel ไปหา • Argument ที่ 2 คอื Column หรอื Row ทีใ่ ห้ Excel ไปหา • Argument ที่ 3 ปกตเิ ราจะใส่เลข 0 เพ่ือให้ Excel หาคาแบบ Exact Match หลกั การเดยี วกบั การใส่คา่ FALSE ในฟงั ชั่น VLOOKUP เลย 10. INDEX ส่วน INDEX ใช้เพื่อดงึ คา่ หลงั จากเรารตู้ าแหน่งของข้อมูลทเี่ ราอยากจะดึงแล้ว สมมติเราอยากจะดงึ ID (ในคอล่มั A) ของทัง้ Yaiba และ Iron Man 17
กรณนี ้ีคือเราจะดึง ID ของตวั ละครที่อยใู่ นคอลั่ม A ทางซา้ ยมอื ของชอื่ ตัวละคร VLOOKUP จะไม่สามารถใช้ไดใ้ นกรณีนคี้ รบั ต้องเปลีย่ นมาใช้ MATCH + INDEX แทน ผลลพั ธ์อยูใ่ นรปู ดา้ นบนแลว้ ครบั H8 = “K08” พิมพ์สูตร =INDEX(Table5, 8, 1) เพ่ือดงึ ไอดขี อง Yaiba H9 = “K04” พิมพ์สูตร =INDEX(Table5, 4, 1) เพ่ือดึงไอดขี อง Iron Man มาดู syntax ของ INDEX กันหนอ่ ยฮะ มอี ยู่ 3 Arguments อกี แลว้ • Argument ที่ 1 คือ ตาราง ที่อยากให้ Excel ไปดึงค่า • Argument ท่ี 2 คอื row index (ทีเ่ ราได้มาจากฟงั ชน่ั MATCH) • Argument ท่ี 3 คอื column index คอลม่ั ทีเ่ ทา่ ไร เราสามารถพิมพ์สูตรเต็มของทั้ง INDEX + MATCH พรอ้ มกนั ได้แบบนคี้ รับ H8 = INDEX(A2:E13, MATCH(“Yaiba”, B2:B13, 0), 1) โดยท่ี A2:E13 (หรอื table 5) คอื ตารางทีเ่ ราอยากไปหาค่า ส่วนฟงั ชน่ั MATCH(“Yaiba”, B2:B13, 0) จะ return ค่า 8 กลับมา คือบอกวา่ ไยบะอยู่ในแถว ที่ 8 ของเซลล์ B2:B13 และ argument สุดท้ายคือเลข 1 ซ่งึ ก็คือคอลัม่ ทีห่ น่งึ ใน ตาราง A2:E13 น่นั เอง ถ้าอยากจะดงึ อายุของ Yaiba ก็แก้ไขสูตรแค่นดิ หนอ่ ยดว้ ยการเปล่ียน column index ในฟงั ชั่น INDEX ไดเ้ ลย =INDEX(A2:E13, MATCH(“Yaiba”, B2:B13, 0), 5) 18
Chapter 6 ไฮไลทข์ ้อมูลดว้ ย conditional formatting บางทกี ารทมี่ ีข้อมูลตัวเลขเยอะๆมันกด็ ยู าก วิธที จี่ ะช่วยใหเ้ รานาเสนอข้อมูลได้ดขี ้ึน คอื การปรบั แต่ง Cells ดว้ ยการใส่สี ใส่ไอคอน ทา heat map ฯลฯ เราตอ้ งทางาน art ใน Excel บา้ ง และเครอ่ื งมอื ที่เหมาะสาหรบั งานนคี้ อื Conditional Formatting นน่ั เองฮะ ไปท่แี ท๊บ Data > Conditional Formatting จะเห็นวา่ เราสามารถไฮไลท์เซลล์ได้หลายวิธีเลย มาลองใส่สีในเซลล์ C2:C20 ดว้ ย Top/Bottom Rules > Above Average กนั ดกี วา่ ครับ ในรูปตวั อยา่ งดา้ นล่าง คา่ เฉลย่ี ของเซลล์ C1:C20 อยทู่ ่ีเซลล์ E2 = $29 ถ้าตัวเลขในเซลล์ C1:C20 มากกว่า 29 (Above Average) เราจะใส่ฟอแมทสีเขยี ว ดังแสดงในรูปด้านซ้ายมอื ครบั ถ้าอยากจะ แกไ้ ขพวกเงื่อนไข >29 หรือแกไ้ ขสี formatting ต่างๆให้เราไปท่ี Conditional Formatting > Manage Rules 19
Excel จะป๊อปอัพหนา้ ต่างใหมข่ น้ึ มาใหเ้ รากด Edit Rule ได้เลยครบั โดยเรา สามารถเปลีย่ นเงือ่ นไขไดห้ ลายอยา่ ง รวมถงึ เปล่ยี นสี Format ตา่ งๆได้ ตัวอยา่ ง ด้านลา่ งเราเลือกใหมเ่ ป็น “Use a formula to determine which cells to format” แลว้ พิมพ์ C2<30 ลงไปในชอ่ งสูตร แล้วฟอแมทสีใหมใ่ ห้เปน็ สีฟา้ อ่อน กด OK เพื่อ apply ฟอแมทใหม่ในเซลล์ C1:C20 จะไดผ้ ลตามรูปขวามอื ดา้ นล่าง ถา้ เราอยากจะลบ Rules ทั้งหมดทงิ้ ใหเ้ รากลบั ไปที่ Conditional Formatting > Clear Rules > Clear Rule From Selected Cells เลย (หรอื จะลบท้ัง Workbook เลยกไ็ ดน้ ะครับ) ลองเล่นเทคนคิ อ่นื ๆเพิ่มไดน้ ะครับ ง่ายๆไม่อยากเลย อย่าง Color Scales ทีใ่ ช้ หลักการ Heat Map กไ็ ลส่ ีคา่ ตา่ งๆได้สวยมาก เราแนะนาให้ลอง 20
Chapter 7 การเปล่ียน themes, colors, fonts ของ workbook ไมช่ อบสีเริ่มต้นของ Excel? ไม่มปี ญั หาครับ เราสามารถแก้ไข และสรา้ ง Theme สีทเี่ ราชอบเองไดเ้ ลย ไปที่ Page Layout > Theme ทางด้านซ้ายมอื สุดได้เลย (จะเปลย่ี นสีหรือฟอ้ นก็ทาได้นะ) เราสามารถปรับแต่งสีและฟอ้ น แลว้ กด Save Theme ไว้ใชใ้ นอนาคตกไ็ ดค้ รบั ลองเขา้ ไปที่ Page Layout > Colors > Customize Colors ปรับเปลยี่ นสีท่ี ตอ้ งการ ด้านขวาจะมพี รวี ิวใหเ้ ราดดู ้วย ตง้ั ชื่อ Theme แลว้ กด Save ได้เลยครบั 21
Chapter 8 การลบขอ้ มูลซา้ (Remove Duplicates) การตรวจสอบข้อมลู ว่ามีขอ้ มลู ซ้าหรอื เปลา่ ก็เปน็ เรอ่ื งทเ่ี ราควรเช็คทุกครงั้ เลย Excel สามารถเช็คให้เราไดง้ ่ายมากๆ โดยลากคลมุ ข้อมลู ของเรา (หรอื column ทเี่ ราจะตรวจสอบ duplicates) ไปที่ Conditional Formatting > Highlight Cells Rules > Duplicate Values แล้วกด OK ไดเ้ ลย ในตัวอยา่ งด้านบนเราอยากจะหา Duplicates (ช่อื ซา้ ) ใน column A การทเ่ี ราใช้ conditional formatting จะทาใหเ้ ราเห็นชื่อซา้ ได้ง่ายขนึ้ มากๆ ดงั รูปดา้ นล่าง ถา้ เราอยากจะลบ Duplicates เหล่าน้ี ให้ เราไปที่ Data > Remove Duplicates แลว้ เชค็ เฉพาะคอลมั่ Name เทา่ น้นั (นีค่ ือ เราบอก Excel ให้ตรวจสอบคาซา้ เฉพาะ คอล่ัมช่อื ตัวละคร) เสร็จแล้วใหเ้ รากด OK ไดเ้ ลย และ Excel จะบอกว่ามันลบออกไปก่ี case และตอนน้ี มีเหลืออยใู่ น data ของเรากี่ case โดย Excel จะลบ duplicates ดา้ นล่างท้ิงไป และเกบ็ ตวั บนไว้ (ลบแถวท่ี 10/11 ท้ิง) 22
Chapter 9 การใชง้ าน group + ungroup + subtotal บทนี้จะสอนการใชง้ านการ Group column หรือ row (รวมถึง Ungroup) และ การใช้งาน Subtotal เบอื้ งต้นครับ เครอื่ งมอื ท้ังหมดในบทนีอ้ ยใู่ นแทบ๊ Data ถ้าเกิดเราอยากจะ Group คอลมั่ C และ D เขา้ ด้วยกนั ใหเ้ ราลากคลมุ คอลัม่ C และ D กอ่ นแลว้ ไปทีแ่ ท๊บ Data > Group เราจะไดเ้ ครื่องหมาย โผลข่ ้นึ มา ด้านบน เรามาสามารถกดที่ปุม่ + นเ้ี พ่ือซ่อนหรือขยายคอลัม่ C และ D ได้ ถ้าเราอยากจะ Ungroup ก็ลากครอบคอลม่ั นั้นไว้ แลว้ ไปกด Ungroup ได้เลยครบั ง่ายๆ ถัดไปมาลองดกู ารใช้งาน Subtotal บา้ งครับ “ Subtotal ไม่สามารถใชไ้ ด้กบั ขอ้ มลู ทเี่ ราเกบ็ ในรปู ของ Table และ ก่อนใช้งาน Subtotal เราต้อง Sort เรียงขอ้ มลู กอ่ น 23
เรยี งข้อมลู ก่อนทา Subtotal นะครบั ตอนนี้เรา Sort ขอ้ มูลใน Column C เรยี บรอ้ ยแล้วครบั เรียงตามชือ่ อาชพี แบบ Descending (เรยี งจากตัวอักษร Z ข้ึนก่อน) เสรจ็ แล้วไปท่ี Data > Subtotal จะมหี นา้ ต่างใหมโ่ ผล่ขึน้ มาดังรูปด้านล่าง ใหเ้ ราต้ังคา่ ตามน้ไี ด้เลยครบั ▪ At each change in ตัง้ ค่าเปน็ Occupation ▪ Use function ใช้เป็นค่าเฉลี่ย (Average) ▪ Add subtotal to ตก๊ิ ชอ่ งอายุ (Age) ▪ เสรจ็ แลว้ กด OK ไดเ้ ลยครบั จะไดผ้ ลลพั ธต์ ามรูปด้านล่าง ตะกค้ี ือเราบอก Excel วา่ ทกุ ครั้งท่ีมีการขึน้ อาชพี ใหม่ จาก Super Hero เปน็ Samurai … ให้เราคานวณค่าเฉล่ียของอายุ และแทรกแถว Average ข้นึ มา ครับ Excel จะแทรกแถวใหม่ ทุกคร้ังท่ีอาชีพเปลย่ี น และมี Grand Average = 32.25 ใน ชอ่ งลา่ งสุด (D19) 24
Chapter 10 การสรา้ งตาราง (insert table) เคา้ วา่ กันวา่ รูปแบบการเกบ็ ขอ้ มูลทดี่ ที ีส่ ุดใน Excel คือการเกบ็ ข้อมูลให้อยู่ในรูป ของตารางแบบ Table Format เอาเมา้ ส์คลก๊ิ ทขี่ อ้ มูลของเรา แล้วไปที่แท๊บ Data > Insert > Table หรอื จะกดปุ่ม shortcut บนคีบอร์ดเลยกไ็ ด้ CTRL+T พรอ้ ม กันเพ่ือฟอแมทข้อมูลให้อยใู่ นรปู ของตาราง ถ้าแถวทห่ี น่ึงของขอ้ มลู เป็นชอ่ื ตัวแปร ใหเ้ ราติ๊กชอ่ ง My data has headers ดว้ ยนะครบั พอเราเอาเมา้ ส์ไปคลก๊ิ ที่ตารางของเรา จะมแี ทบ๊ ใหม่โผล่ข้นึ มาช่อื วา่ Design ซงึ่ เรา สามารถปรบั แตง่ หนา้ ตาของ Table ใหด้ ูดี สวยข้นึ ไดง้ ่ายๆเลย • เราสามารถตัง้ ชื่อตารางของไดท้ ี่ช่อง Table Name (ทางด้านซ้ายมือสุด ของแทบ๊ Design และเราควรต้ังชอื่ ทกุ ครง้ั ) • ถ้าอยากเปล่ยี นตารางกลับไปเปน็ Range ปกติใหก้ ด Convert to Range • เราสามารถกด Insert Slicer เพ่ือใชใ้ นการกรองข้อมลู เร็วโคตรๆ 25
• ขอ้ ดีของการใช้ Table คือเราสามารถพิมพ์สตู รโดยอ้างอิงชอื่ คอลั่มใน Table นนั้ ๆไดเ้ ลยอย่างเชน่ =[Experience]*[Bonus] โดยชื่อคอลม่ั ตอ้ ง อย่ใู นเคร่อื งหมาย [ ] • เวลาเราเพิ่ม new column หรือวา่ new row ตัว Table จะอัพเดทพวก ฟอแมทให้เราอัตโนมัติเลย • เราสามารถปรบั format ของ Table เราได้ท่ี Table Styles • เราสามารถสรุปผลขอ้ มูลใน Table ของเราด้วย PivotTable งา่ ยๆเพียง กดปมุ่ Summarize with PivotTable • เมื่อใช้คู่กบั PivotTable จะมีประสิทธภิ าพมากย่ิงข้นึ การอพั เดทขอ้ มลู ทงั้ หมดเป็นไปอยา่ งอตั โนมัติ ไมต่ ้องกลวั ว่าตารางหรอื ชารท์ ของเราจะไม่ อพั เดทข้อมูล “ การเก็บขอ้ มลู ในรปู แบบ Table ถอื ว่ามปี ระสิทธภิ าพที่สุดแล้วใน โปรแกรม Excel เพราะมนั งา่ ยตอ่ การดแู ล แกไ้ ข และอัพเดท 26
Chapter 11 การใช้งาน PivotTable เบอ้ื งตน้ พอเราเกบ็ ขอ้ มลู ในรูปแบบของ Table เรียบร้อยแลว้ ขน้ั ตอนถัดไปคือการวเิ คราะห์ ขอ้ มลู ของเราอย่างมีประสิทธิภาพดว้ ย PivotTable สุดยอด Tool ใน Excel เอา เมา้ ส์ไปคล๊ิกท่ี Table ของเรา แลว้ กดท่ีแท๊บ Design > Summarize with PivotTable จะมีหน้าตา่ งใหม่โผลข่ ึ้นตามรูปดา้ นลา่ ง ลากคลุมข้อมลู ของเรา (ปกติ Excel จะ select ขอ้ มลู ให้เราอัตโนมัติอยแู่ ลว้ ) แลว้ กด OK ได้เลย Excel จะสรา้ งช้ีตใหม่ขึ้นมาให้เรา และมี หน้าตา่ งดา้ นซ้ายโผล่มา โดยจะมีทงั้ หมด 5 ชอ่ ง ช่องบนสุดคือตวั แปรทง้ั หมดที่มีใน Table ของเรา ไดแ้ ก่ Name, Country, Occupation, Age ส่วนอีกส่ีชอ่ งที่เหลือคือ • Filters เลือกตวั แปรมาเป็นฟลิ เตอร์ • Columns เลอื กตวั แปรมาเป็นหัวตาราง • Rows เลอื กตวั แปรมาเปน็ row ข้างๆ • Values ชอ่ งนี้หา้ มวา่ ง ต้องดงึ ตัวแปร มาใส่เสมอ เพื่อบอก Excel ว่าเราจะหาคา่ อะไร? 27
“ จริงๆแล้ว PivotTable คอื การสรา้ งตาราง Crosstabs นัน่ เอง เอา ตัวแปรสองตวั มาครอสกนั Column x Row ลองเลือกตวั แปรไปใสใ่ นช่องต่างดงั นี้ครบั ▪ Filter = Country ▪ Columns = Occupation ▪ Row = Name ▪ Values = Count of Age (เอาเม้าส์ไปคล๊กิ ที่ตัวแปรน้ใี นชอ่ ง Value แล้ว เลือก Value Field Settings แล้วเปลย่ี นจาก Sum ใหเ้ ป็น Count ไดเ้ ลย) เราจะไดผ้ ลออกมาตามรูปด้านล่างนะครบั ข้อมูลของเรามีทง้ั หมด 12 คน ซึ่งดูได้ จากช่อง G17 ขอ้ มลู ของเรามีนักแสดงอย่ทู ้ังหมด 2 คน (B17) นกั ฟุ ตบอลมีอยู่ ทงั้ หมด 3 คน (C17) ฯลฯ ส่วน Filter จะอยู่ที่ช่อง B1 เราสามารถฟลิ เตอรป์ ระเทศให้ตารางน้ีแสดงเฉพาะ ประเทศอเมรกิ าอยา่ งเดียวก็ไดเ้ หมือนกัน และนี้คอื การสรา้ งตาราง PivotTable เบื้องตน้ ครับ แคเ่ ลอื กตัวแปรไปใส่ในช่องตา่ งๆ ก็สามารถทา Report เบอ้ื งต้นได้ งา่ ยๆเลย (ลองปรับ Format ของตาราง Pivot ได้ทแี่ ท๊บ Design ดา้ นบน) 28
Chapter 12 การใช้งาน PivotChart เบื้องต้น PivotChart เป็นส่วนหน่งึ ของ PivotTable ครับ ในตวั อยา่ งดา้ นลา่ งเรายา้ ย Country ไปอย่ทู ี่ Row ส่วนชอ่ ง Value เปน็ Count of Age ปกติครับ (แค่นบั จานวนคนในแต่ละประเทศ) พอเราสร้างตาราง Pivot ทต่ี ้องการเสรจ็ แล้ว ใหเ้ ราไป ท่แี ท๊บ Analyze > PivotChart แลว้ เลอื กชารท์ ที่เราต้องการได้เลย อนั น้ีเราใช้ เปน็ Clustered Column Chart ไดเ้ ลยครับ กด OK เราก็จะได้ PivotChart สวยๆตามรปู ด้านลา่ งเลยครับ สามารถปรบั แต่ง ชารท์ ของเราไดต้ ามปกติเลย คลกิ๊ ทช่ี าร์ทแล้วเลอื กแทบ๊ Design ด้านบน 29
ทีเด็ดอยู่ตรงน้ีครบั เราสามารถสร้าง Filter รปู แบบใหมท่ ่เี รียกวา่ Slicer เพ่ือ กรองข้อมลู แบบเรว็ ๆไดเ้ ลย ใหเ้ ราเอาเมา้ ส์คลก๊ิ ที่ชารท์ ของเรา แลว้ เลอื กแท๊บ Analyze ดา้ นบน แลว้ เลอื ก Insert Slicer ได้เลยครบั Excel จะป๊อปอพั หนา้ ตา่ งใหม่ขึ้นมา ให้เราเลอื ก Occupation มาเป็น Slicer ของเราได้เลย กลอ่ ง Occupation ในรูปด้านลา่ งคอื Slicer นน่ั เองฮะ เราสามารถกดเลือก ฟลิ เตอร์อาชีพไดเ้ ลย ตาราง PivotTable กบั PivotChart ของเราจะเปล่ียนตาม ฟลิ เตอรท์ เี่ ราเลอื กโดยอัตโิ นมัติ จบแล้วครับ Chapter 11 กับ Chapter 12 อยากให้คุณได้ลองใชง้ าน PivotTable และ PivotChart เบ้อื งต้นครับ ทั้งสองตัวน้เี ป็นเครอื่ งมือสาคัญที่เราจะใช้สรา้ ง Dashboard ในบทถัดไป 30
Chapter 13 เทคนิคการสรา้ ง basic dashboard ขนั้ เร่ิมต้น ตอนนท้ี ุกคนน่าจะใช้งาน PivotTable กับ PivotChart เบอื้ งต้นไดแ้ ลว้ ครับ เรา สามารถสร้าง Basic Dashboard หนา้ ตาแบบรูปดา้ นล่างไดไ้ ม่ยากเลย เทคนิคการสร้าง Dashboard 1. คิดกอ่ นว่าเราอยากนาเสนอขอ้ มูลอะไรบา้ ง Template ควรเป็นแบบไหน 2. เมื่อคดิ เสร็จแล้ว ก็เริ่มท่ีสร้าง PivotTable 3. แล้วสร้าง PivotChart 4. เรยี กใช้งาน Slicer เพื่อกรองข้อมลู 5. ยา้ ย Chart ของเราไปอย่ทู ห่ี นา้ Dashboard ทเ่ี ราต้องการ 6. ถ้าอยากสรา้ ง Chart ใหม่ ใหท้ าวนลูปขอ้ 2-5 อีกครง้ั 7. เช่ือมทกุ ตารางและชารท์ เขา้ ด้วยกนั ด้วย Report Connection ขั้นตอนที่ 7 ให้เราเอาเม้าส์คล๊ิกท่ี Slicer ของเราแลว้ กดไปท่ี Options ด้านบน เลือกไปที่ Report Connection แล้วก็ทาการเช่ือม Slicer ของเราเขา้ กับทุก ตาราง PivotTable และ PivotChart ได้เลยครับ คราวน้ีเวลาเรากดเลอื ก ฟลิ เตอรท์ ี่ Slicer ของเรา กราฟทง้ั หมดจะเปลย่ี นพรอ้ มกันหมดเลย 31
Chapter 14 การกาหนดกฏเพ่ือป้องกนั การใส่ข้อมลู ผดิ (data Validation) Data Validation ใชเ้ พ่ือป้องกนั การคยี ข์ อ้ มลู ผิด เช่น ต้องเปน็ เลขจานวนเตม็ เท่านน้ั หา้ มติดลบ หรอื ทาเป็น drop down list ฯลฯ โดยเราสามารถตง้ั เงอ่ื นไข ไดท้ ง้ั หมด 7 แบบ ใหเ้ ราไปทีแ่ ทบ๊ Data > Data Validation Excel จะแสดงหนา้ ต่างใหม่ขน้ึ มาแบบนี้ ตรงแทบ๊ Settings > Allow เราเลือก กฏท่เี ราตอ้ งการไดเ้ ลย ตัวอยา่ งเชน่ List คือการกาหนดให้เซลล์ที่เราตอ้ งการมี drop down list โผล่ขึน้ มา ให้เราพิมพ์คาว่า Banana, Apple, Orange ลงไปใน ช่อง B2:B4 เอาเมา้ ส์คลก๊ิ ที่ช่อง D2 แลว้ ไปที่ Data Validation เลอื ก List และ ตั้งค่าตามรูปดา้ นลา่ ง อย่าลืมต๊กิ ช่อง In-cell dropdown ดว้ ยนะครบั คราวนี้มาลองดูผลทไ่ี ด้กันครบั ในช่อง D2 จะเห็นว่ามปี ุ่มลกู ศรให้เรากด ได้ พอเอาเม้าส์ไปคลก๊ิ จะเหน็ วา่ มี List ให้ เราเลือกใช้ได้ตามทีเ่ รากาหนดไวน้ น้ั เอง 32
Chapter 15 คานวณค่าท่ีเราต้องการดว้ ย what-if analysis Excel มีความสามารถในการวเิ คราะห์ผลหลายอย่างเลย และเคร่อื งมอื อกี ตัวนงึ ทใ่ี ช้ ง่าย แตม่ ปี ระโยชน์สูงคือ What-If Analysis ประกอบด้วย Scenario Manager, Goal Seek และ Data Table คู่มอื เราจะสอนแคส่ องตัวหลังนะครบั เวลาจะใชง้ าน ให้เราไปทีแ่ ท๊บ Data > What-if Analysis เรม่ิ กนั ที่ Goal Seek สมมติเรากูเ้ งินซื้อบ้านราคา 2,000,000 บาท (ดาวนไ์ ปแลว้ 500,000) อตั รา ดอกเบีย้ 5% ตอ่ ปี กรู้ ะยะเวลา 10 ปี เราตอ้ งผ่อนเดือนละ 15,909.83 บาท ซ่ึงเรา เขียนสูตรไวใ้ นเซลล์ B6 (อันนี้เราสามารถหาได้จากสูตร =-PMT(5%/12, 10*12, 1,500,000) ใน Excel ครับ) สมมตเิ ราอยากผ่อนเดอื นละ 25,000 บาท อตั ราดอกเบี้ย 5% เหมอื นเดมิ เรา อยากจะร้วู ่าระยะเวลาท่ีผอ่ นจะลดลงเหลอื กีป่ ี? แบบนีเ้ ราสามารถใช้ Goal Seek มาตอบคาถามน้ีใหเ้ ราไดเ้ ลยครับ เปิด Goal Seek ขนึ้ มา แล้วใส่ค่าตามรปู ดา้ นบน ได้เลย Set Cell B6 to value 25000 by changing cell B4 เสรจ็ แล้วกด OK และดูเวทมนต์ของ Excel ฮะ ระยะเวลาจาก 10 ปจี ะเหลือแค่ 5.76 ปีเท่าน้นั 33
ใช้ Data Table เพ่ือปรบั เปลี่ยน parameter ในสูตรของเรา แลว้ ถ้าเราอยากจะลองเปล่ียนทง้ั ระยะเวลากู้ และอัตราดอกเบยี้ พรอ้ มกันเลย เพื่อดู ว่าเงนิ ผ่อนตอ่ เดือนของเราจะเป็นเท่าไร ทาได้ไหม? กท็ าไดง้ ่ายๆเลยด้วย Data Table ครับ ไปทแ่ี ท๊บ Data > What-If Analysis > Data Table แตก่ ่อนที่เราจะเรยี กใช้ Data Table เราตอ้ งเซท็ อัพ spreadsheet ของเรากอ่ น ครับ ทาตามรูปดา้ นลา่ งไดเ้ ลย ในชอ่ ง E2 พิมพ์ว่า =B6 ครบั เราแคจ่ ะดงึ สูตร PMT ทเี่ ราเขยี นไว้มาใส่ในชอ่ ง E2 เฉยๆเป็นเหมอื น scenario ตั้งตน้ ของเรา เสร็จแลว้ ในเซลล์ E3:E6 คอื อตั ราดอกเบยี้ ทีเ่ ราอยากลองเปลย่ี นดู (5.0%, 5,5%, 6.0%, 6,5%) ส่วน F2:I2 คือระยะเวลากทู้ ่เี ราอยากลองขอธนาคาร (10, 15, 20, 25 ปี) เอาเมา้ ส์ลากคลมุ ต้งั แต่ E2:I6 ครับ แล้วเปิด Data Table ขน้ึ มา พิมพ์เงือ่ นไข ลงไปในช่อง Row Input Cells และ Column Input Cells ตามรปู ไดเ้ ลยครบั • Row input cells (คือค่า 10, 15, 20, 25) ให้พิมพ์ B4 ลงไปเพ่ือบอก Excel วา่ แนวนอนน้ีคอื ระยะเวลา ที่เราอยากทดสอบ • Column input cells (คอื คา่ 5% - 6.5%) ใหพ้ ิมพ์ B3 ลงไปเพ่ือบอก Excel วา่ แนวตั้งนค้ี อื อตั ราดอกเบย้ี ทีเ่ ราอยากทดสอบ • เสรจ็ แลว้ กด OK จะไดผ้ ลลัพธต์ ามดา้ นล่างครับ $15,909.83 10 15 20 25 5.00% $ 15,910 $ 11,862 $ 9,899 $ 8,769 5.50% $ 16,279 $ 12,256 $ 10,318 $ 9,211 6.00% $ 16,653 $ 12,658 $ 10,746 $ 9,665 6.50% $ 17,032 $ 13,067 $ 11,184 $ 10,128 34
Chapter 16 วิเคราะหผ์ ลสถิติดว้ ย Analysis ToolPak Analysis ToolPak เปน็ เครอ่ื งมอื ลับ (Add-ins) พิเศษใชร้ นั ผลสถิติพื้นฐานถึง ขั้นสูงอย่าง Regression ทซี่ อ่ นอยู่ใน Excel ตอ้ งเรยี กมันขน้ึ มากอ่ น โดยเข้าไปท่ี File > Options แลว้ เขา้ ไปที่ Add-ins ตามรูปดา้ นล่าง ในช่อง Manage เลือก Excel Add-ins แลว้ กด Go หนา้ ตา่ ง Add-ins ใหม่จะเด้งขน้ึ มา ใหเ้ ราเลือก Analysis ToolPak แล้วกด OK ไดเ้ ลยครับ เวลาจะใชง้ าน ใหเ้ ราไปทแี่ ท๊บ Data แล้วกด Data Analysis ตามรูปดา้ นล่างได้เลย 35
คูม่ อื น้จี ะสอนใชง้ านตัวหลักๆ ไดแ้ ก่ • Sampling ใช้ในการสุ่มตัวอยา่ งจากข้อมูลของเรา • Independent T-Test ใชเ้ ปรยี บเทยี บความคา่ เฉลย่ี สองกลมุ่ • Correlation ใชห้ าความสัมพันธ์ระหวา่ งตวั แปรเชิงปริมาณ • Regression ใชส้ รา้ งโมเดลสาหรบั พยากรณ์ (Predictive modeling) มาเรม่ิ กันท่ี (Random) Sampling การสุ่มตัวอยา่ งแบบแรนดอมถอื วา่ เปน็ คณุ ภาพที่สาคัญทีส่ ุดของการศึกษา ประชากรแบบ observational study อย่างเช่นงานวิจัยตลาด การเกบ็ แบบสอบถาม เป็นตน้ ใน Excel เราสามารถใช้ Data Analysis ในการสุ่ม ตัวอยา่ งใหเ้ ราได้ ตามจานวนท่ีเราต้องการ สมมติเรามีข้อมูลคะแนนสอบของนกั เรยี น 20 คน เป็น ผูช้ าย 10 คน และผ้หู ญงิ 10 คน ส่วนคอลัม่ A คือ Case Identifier เพ่ือบอกวา่ นค่ี ือนกั เรยี นคนที่เทา่ ไร ต้งั แต่คน ท่ี 1 ถงึ 20 คดิ ซะวา่ มนั เป็นไอดีนักเรยี น ขนั้ ตอนการใช้งาน Sampling ให้เราเขา้ ไปที่ Data > Data Analysis > Sampling 36
• ช่อง Input Range ให้เราเลือกเฉพาะคอล่มั A ท่ีเป็น case identifier ของนักเรียนแต่ละคน A1:A21 และเชค็ กลอ่ ง Labels ดว้ ยเพราะว่าแถวท่ี หน่ึงเปน็ ชือ่ คอลัม่ • Sampling Method ใหเ้ ราเลือกแบบ Random และใส่จานวนกลมุ่ ตัวอยา่ ง ท่ีเราตอ้ งการ ในตวั อย่างน้เี ราให้ Excel สุ่มออกมาให้เรา 5 คน • Output Options สามารถเลอื กได้วา่ จะให้ Excel เซฟผลของเราท่ีไหน ใน ตวั อยา่ งน้ีเราเซฟผลใน E1 ของชี้ตเดียวกนั เลย (ถา้ อยากเซฟผลในชี้ตใหมก่ ็ เลอื ก New Worksheet Ply และตงั้ ชอื่ ชีต้ นั้นได้เลยครับ) กดปุ่ม OK เพื่อรันผล Excel จะสุ่มตวั อย่างออกมาให้เรา n=5 วางอยู่ในเซลล์ E1:E5 สรุปเราดึงรายชอ่ื นกั เรยี นคนที่ 13, 12, 5, 19, 15 ออกมาครบั เราสามารถใช้ VLOOKUP() เพ่ือไปดงึ เพศและคะแนนสอบของเดก็ เหลา่ น้อี อกมาไดใ้ น step ต่อไป 37
ถัดมามาลองรนั Independent T-Test กนั ตอ่ ฮะ ให้เรากลับเข้าไปที่ Analysis ToolPak แลว้ เลอื ก t-Test: Two Sample Assuming Equal Variances ภาษาคนคือ ทดสอบคา่ เฉลย่ี ของสองกลมุ่ ทีม่ ีการ กระจายตัวขอ้ มลู เทา่ กัน (หรอื ใกลเ้ คยี ง) เรายังใช้ขอ้ มูลนักเรียนของเราเม่อื ตะก้ี ใน ลองรนั t-test นะครบั โดยตง้ั คา่ ตามในรปู ด้านล่างได้เลย • Variable 1 Range: เลอื กคะแนนของนักเรียนชายใส่ลงไป C2:C11 • Variable 2 Range: เลอื กคะแนนของนักเรยี นหญงิ ใส่ลงไป C12:C21 38
• Hypothesized Mean Difference: ใส่คา่ 0 ลงไปไดเ้ ลยครับ ชอ่ งน้ีคือ เรามสี มมติฐานวา่ คะแนนสอบของนกั เรียนชายและนกั เรียนหญงิ ไมต่ ่างกนั เลย (mean difference เลยเทา่ กับศูนย)์ แปลภาษาคนวา่ นักเรียนชายและ หญงิ เกง่ พอกัน • Alpha: คา่ เร่มิ ต้นท่ี 0.05 ไมต่ อ้ งเปลย่ี นครบั ปกตนิ กั สถติ มิ กั ใช้ alpha ที่ 0.05 หลายๆคนน่าจะเคยไดย้ ินประโยคทีบ่ อกว่า p-value < 0.05 เจอผล ซกิ (เดว๋ เรากาลังจะไดอ้ า่ นผลตรงน้ดี ้วยกนั ฮะ) • Output Range: กดเซฟผลในช่อง E1 ไดเ้ ลยครบั มาลองอา่ นผลกันฮะ คะแนนสอบเฉล่ยี ของนกั เรยี นชาย (variable 1) อยทู่ ่ชี ่อง F4 ส่วนคะแนนสอบเฉล่ยี ของนกั เรียนหญิง (variable 2) อยู่ทช่ี อ่ ง G4 ผหู้ ญิงได้ คะแนนสอบมากกว่าผู้ชายอยู่ 25.50 – 23.40 = 2.10 แตม้ แตเ่ รามีสมมติฐานวา่ คะแนนสอบของทงั้ สองกล่มุ เท่ากับศูนย์ เราจะ ”ปฏเิ สธ” หรือ ”ไมป่ ฏเิ สธ” สมมตฐิ านนี้ดี (เวลาเรียนสถิติเราจะไมใ่ ชค้ าว่ายอมรบั สมมตฐิ านนะครับ) 39
ให้เราดูทชี่ อ่ ง F13 คือคา่ p-value ในทางสถิติน่นั เอง โดยหลกั การทางสถติ ิคือ เราจะปฏิเสธสมมติฐานถา้ เกดิ p-value < alpha ตะกเี้ ราต้ังค่า alpha = 0.05 ตอนท่ีเรารนั ใชไ่ หมครับ? แสดงว่าตอนนี้ p-value (0.122) มันมากกวา่ alpha (0.05) เราจงึ สรุปผลวา่ “ p-value > alpha เราไมส่ ามารถปฏเิ สธสมมติฐานน้ไี ด้ คะแนนสอบ เฉลี่ยของนักเรยี นชายและหญงิ แตกต่างกันอย่างไมม่ ีนัยสาคญั ทางสถิติ หรือภาษาบา้ นๆจะบอกว่าจากขอ้ มูลทีเ่ ราเก็บมา (based on evidence) นกั เรียน ชายและหญิงเก่งพอๆกันกไ็ ด้ (เพราะคะแนนต่างกันแบบไมซ่ ิก) อ่านจบตรงน้ี ยินดตี อ้ นรบั ทุกคนเข้าสู่โลกของสถติ คิ รบั ไม่ยากเลยใชป่ ่าว? ตอนน้ี จาแค่ p-value < alpha เราจะปฏเิ สธสมมติฐานกพ็ อครบั ถดั ไปเราไปลองรัน Correlation & Linear Regression ตอ่ นะครบั รนั Correlation เพื่อหาความสัมพันธ์ระหวา่ งตวั แปร numerical ไปที่ Data > Data Analysis > Correlation 40
• Input Range: เลือกตัวแปรใส่ลงไปท้ังหมด ลากคลมุ เซลล์ B1:G399 • Grouped By: เลือก Columns เพราะตวั แปรเราแต่ละตวั เกบ็ ในคอลม่ั • Labels in first row: ติ๊กช่องนี้ดว้ ยครบั เพราะแถวแรกเปน็ ชอื่ ตวั แปร • Output Range: เซฟผล correlation ท่ีเรารนั ในเซลล์ I1 ไดเ้ ลย กด OK เพ่ือรันผล เราจะได้ตาราง Correlation Matrix ดงั ภาพดา้ นลา่ งนะครับ การอ่านคา่ งา่ ยมากๆ เส้นทแยงมมุ ในตาราง correlation matrix จะเท่ากับ 1 เสมอ เพราะแกนตงั้ และแกนนอนคอื ตวั แปรตวั เดยี วกัน ถ้าเราอยากดูคา่ correlation หรือตวั ยอ่ ส้ันๆใชต้ ัว “r” ท่ีบอกความสัมพันธร์ ะหวา่ ง MPG กบั CYL เราสามารถดูทชี่ อ่ ง J3 ได้คา่ r เทา่ กบั -0.7754 ถา้ อยากดคู วามสัมพันธ์ระหว่าง DISP กบั HP ใหด้ ูช่อง L5 ไดค้ ่า r เทา่ กับ 0.87325 41
เร่ืองสนุกๆของค่า Correlation • ค่า r จะว่ิงอยูร่ ะหวา่ ง -1 ถึง +1 โดยเราจะอา่ นคา่ absolute ยิ่งเขา้ ใกล้ | 1 | แปลว่าความสัมพันธส์ ูงมาก • เครือ่ งหมาย +/- แคบ่ อกทิศทางความสัมพันธ์ของสองตัวแปรเทา่ น้นั เครื่องหมาย + ตัวแปรสองตวั เปลีย่ นแปลงในทิศทางเดียวกนั ถ้าติดลบคอื ตวั แปรสองตัวเปลี่ยนแปลงในทิศทางตรงกนั ขา้ มกัน เช่น ในรูปด้านล่างจะ เหน็ วา่ MPG กบั HP คา่ correlation ตอ้ งติดลบแน่ๆ (r = -0.75829) เพราะแรงมา้ สูงข้ึน (HP) ระยะทางที่วิ่งได้ต่อน้ามันหนึ่งลิตร (Mile per gallon) จะลดลง make sense? • Correlation ใช้ได้ถ้าเกิดความสัมพันธ์ของทง้ั สองตวั แปรเปน็ เส้นตรง เท่านั้น และไมม่ ี outlier ฉะนัน้ เราควรดกู ราฟ scatter plot ก่อนนะ MPG 50 45 40 50 100 150 200 250 35 HP 30 25 20 15 10 5 0 0 หรือถา้ ไมใ่ ช้ Analysis ToolPak เราก็สามารถหาคา่ correlation ระหวา่ งตัวแปร สองตวั ได้งา่ ยๆดว้ ยฟงั ชน่ั CORREL() ตวั อย่างเชน่ การพิมพ์สูตร =CORREL(B2:B399,E2:E399) ในช่อง I9 จะไดค้ า่ r ระหว่าง MPG และ HP เท่ากับ -0.75829 ดังรปู ในหน้าถดั ไปเลยครับ 42
จบ correlation แลว้ ฮะ เรามาตอ่ กันทีโ่ มเดลสุดทา้ ย Regression ที่ใช้กันเยอะ มาก ในงาน Machine Learning/ Data Mining/ Predictive Models สรา้ ง Predictive Model ด้วย Linear Regression ไปที่ Data > Data Analysis > (Linear) Regression จาไว้เสมอวา่ หวั ใจของ Regression คือการสรา้ งโมเดลทีใ่ ช้พยากรณค์ า่ y ครบั เขยี นเปน็ ฟงั ช่นั ได้ ประมาณนี้ y = f(x) ถ้าเรารู้ x เรากจ็ ะสามารถพยากรณค์ า่ y ได้น่ันเองครับ โดย เงอ่ื นไขหลกั ของการสรา้ ง Linear Regression (อย่างที่ชอื่ มันบอกเลย) คือมันใช้ โมเดลความสัมพันธ์ที่เป็นเส้นตรง และตัวแปรตามตอ้ งเปน็ numerical data 43
โมเดลแรกที่เราจะลองรนั กันเรยี กว่า Simple Linear Regression คือมีตวั แปร ต้น (x) แคต่ ัวเดียว และมีตวั แปรตาม (y) แคต่ วั เดยี วเชน่ กนั ครับ เราใช้ข้อมลู ชดุ เดิมกบั ที่รันผล correlation ไปเมื่อตะกน้ี ะครับ เลอื กตัวแปรใส่ลงไป ใน dialog box เหมอื นรูปด้านบนไดเ้ ลยครบั โดยท่ี Input Y Range คือตัวแปร ตาม (MPG B1:B399) ส่วน Input X Range คือตวั แปรตน้ (HP E1:E399) ติ๊ก เลือกช่อง Labels ด้วย (เพราะวา่ Row ทหี่ นึ่งเปน็ ช่อื ตัวแปร) แล้วกดเซฟผล Regression ของเราในชี้ตใหม่ ชื่อวา่ “Simple Model” เราจะไดผ้ ลออกมาหน้าตา เหมอื นรูปด้านลา่ งนะครบั มาลองอ่านผลดว้ ยกนั ฮะ ค่าสถติ ติ ัวแรกทีต่ ้องดคู อื คา่ R Square ในช่อง B5 เท่ากบั 0.57499 เป็นค่าทเ่ี รา ใช้ดูว่าโมเดลเราทางานได้ดขี นาดไหนในภาพรวม ภาษาบ้านๆคือโมเดลของเรา อธบิ ายค่า y ได้กี่เปอรเ์ ซ็นต์ (ชอ่ื ทางการเรียกวา่ explained variance) โดย R Square จะวงิ่ อยรู่ ะหว่าง 0-1 ค่ายง่ิ เข้าใกล้หนึง่ แปลวา่ โมเดลเราทางานได้ดี 44
ตวั ถดั มาทตี่ ้องอยูใ่ นช่อง F12 คือค่า p-value ของโมเดล โดยเราอยากเห็นค่า p- value < alpha (0.05) ทเ่ี รากาหนดไวค้ รับ ถ้า p-value น้อยกวา่ อลั ฟา่ ท่ีเราตอ้ ง ไว้ตอนแรก แปลว่าโมเดลเราทางานได้จริง (our model works!) และสุดท้ายใหเ้ ราดูคา่ coefficient ในชอ่ ง B17 และ B18 ครับ เพ่ือเอามาเขยี น สมการ (โมเดล) ท่ีเราสรา้ งข้นึ มา ได้ดงั น้ี MPG = 39.592 + (-0.1529)*HP MPG 50 45 40 y = -0.1529x + 39.592 35 30 50 100 150 200 250 25 HP 20 15 10 5 0 0 โดยท่ี 39.592 เราเรียกวา่ ค่า intercept หรือจุดตดั แกนต้ัง ส่วน -0.1529 เรียกวา่ ค่า slope (หรือความชันของเส้นตรง) แปลความหมายไดว้ า่ “ ถา้ HP เพ่ิมขึน้ หนึ่งหน่วย (ปจั จยั อน่ื คงท)ี่ MPG จะลดลง -0.1529 หน่วย โดยมีชว่ งความเช่อื มน่ั ท่ี 95% อยู่ระหว่าง [-0.166, -0.140] 45
และนีค่ อื การรัน Simple Linear Regression เพื่อใช้สรา้ งโมเดลเบ้ืองต้นครบั ตอนนีเ้ ราจะเก็บสมการ MPG = 39.592 + (-0.1529)*HP อยูใ่ นใจ ตอ่ ไปเวลาเรา เหน็ รถยนตค์ ันใหม่ แล้วเรารู้วา่ รถยนตค์ ันนนั้ มีแรงมา้ HP = 150 เราสามารถแทน คา่ 150 ลงไปในสมการได้เลยเพ่ือคานวณคา่ MPG (คานวณแลว้ ได้ค่า MPG = 9.01345) ซ่งึ การทา prediction ของเราอยา่ ลมื วา่ มนั มี error เกดิ ข้ึนมา ตลอดเวลา ไม่มีใครทายถกู 100% ตอนนี้ความแมน่ ยาของเราอยูท่ ่ีประมาณ 57.4% เอง หากดจู ากคา่ R Square จบแล้วครบั กับ Analysis ToolPak ตอนน้ีทุกคนนา่ จะพอเห็นภาพว่าเราสามารถ ทาอะไรได้อกี เยอะเลยกับโมดลู ฟรตี วั น้ี correlation & regression ถือวา่ เป็นพ่ี น้องกันครับ และเราสามารถหยบิ สถิตสิ องตวั น้ีมาใชค้ ่กู นั ได้เสมอๆ โดยท่ี correlation ใช้หาความสัมพันธ์ ส่วน regression ใช้เพื่อสรา้ งโมเดลท่ี quantify การเปล่ียนแปลงของคา่ y ทเี่ กดิ จากการเปล่ียนแปลงของค่า x You are now ready! ตอนน้คี ณุ พรอ้ มแลว้ ครับ ที่จะทา Multiple Linear Regression ตอ่ ไป โดยชื่อ “Multiple” แปลวา่ เรามีตัวแปรต้น (x) มากกว่าหน่ึงตวั ในโมเดลครบั แตน่ อกนน้ั อ่านคา่ ทุกอยา่ งเหมือนเดิมเลย 1) ดคู า่ R Square 2) ดคู า่ p-value ในตาราง ANOVA และ 3) เขียนโมเดลของเราโดยใช้ค่า coefficient ทีร่ นั ได้ Chapter 17 & 18 เราจะแนะนาการใชง้ าน Developer Tools และการจากัดการ เข้าถงึ และใส่รหสั เพ่ือปกปอ้ งขอ้ มลู สาคญั ในไฟลง์ านของเรา 46
Chapter 17 การสรา้ ง form ดว้ ย developer tools เราสามารถใช้ Developer Tools เพ่ือสรา้ งฟอร์มต่างๆไดใ้ น Excel ครบั กอ่ นท่ี เราจะใชค้ าสั่งตา่ งๆของเครือ่ งมอื ตวั น้ไี ด้ เราตอ้ งเรยี กแทบ๊ Developer ข้ึนมา ก่อน โดยเขา้ ไปที่ File > Options > Customize Ribbon แลว้ ต๊ิกเลือก Developer ทางด้านขวามือ แลว้ กด OK ครับ เสร็จเรยี บรอ้ ยครบั เวลาจะใชง้ านไปทแ่ี ท๊บ Developer ได้เลย กดท่ีปุ่ม Insert ตามรปู ด้านล่างครับ จะมี Form Controls (สองแถวบน) ให้เราเลอื กใช้หลายตัวเลย Tutorial นเี้ ราจะ สอนใช้ตัว Check Box กบั Scroll Bar นะครบั งน้ั เรามาเร่มิ กนั ที่ Check Box (Form Control) กันก่อนเลยฮะ 47
Search
