Basic Formula

ติวเข้มก่อนเข้าโรงเรียนเวทมนตร์ #2 : ข้อควรระวังในการเขียนวันที่ในสูตร

จากตอนที่แล้วที่ผมได้อธิบายเฉลยข้อสอบเข้าโรงเรียนจอมเวทเทพเอ็กเซล ไป 2 เรื่องแล้ว วันนี้มาต่ออีกเรื่องซึ่งเป็นข้อที่มีคนผิดมากที่สุดครับ โดยโจทย์ที่ถามคือ… 8) ตามรูป หากเขียนสูตรว่า =IF(A1<31/12/2016,”ยังไม่หมดเขต”,”หมดเขตแล้ว”) ผลลัพธ์จะได้ออกมาเป็นอะไร? (A1 คือ วันที่ 29 สิงหาคม ค.ศ. 2016) ข้อนี้หลายคนโดนหลอกอย่างจัง เพราะข้อนี้คำตอบคือ “หมดเขตแล้ว” สาเหตุคือ เวลาเราเขียนค่าที่เป็นวันที่ลงไปในสูตร หากเราดันไปเขียนว่า =A1<31/12/2016 มันจะกลายเป็น เอา 31 หารด้วย 12 แล้วหารด้วย 2016 แทน (ไม่เชื่อลองเขียนสูตรว่า =31/12/2016 Read more…

By Sira Ekabut, ago
Logic Function

IF vs CHOOSE vs VLOOKUP เปรียบเทียบฟังก์ชั่นทำหน้าที่ตัดสินใจ

“ชีวิตคนเราเต็มไปด้วยการตัดสินใจ…” จริงๆ แล้วคนเราตัดสินใจอยู่ตลอดเวลา (บางทีก็ตัดสินใจที่จะอยู่เฉยๆ 555) บางทีเราก็ต้องเลือกระหว่าง Choice 2 อย่าง แต่บางทีก็มี Choice มากมายนับไม่ถ้วน… การทำงานกับ Excel ก็เช่นกัน หลายๆ ครั้งเราก็ต้องตัดสินใจ “ว่าถ้าข้อมูลเป็นแบบนี้ๆ แล้วจะให้เกิดเหตุการณ์อะไรขึ้น??” ซึ่งจริงๆ แล้วการตัดสินใจใน Excel นั้นมีฟังก์ชั่นที่มาช่วยในเรื่องนี้หลายตัวด้วยกัน แต่ในบทความนี้ผมจะขอยกตัวอย่างฟังก์ชั่นที่น่าสนใจมากๆ 3 อัน นั่นคือ IF, CHOOSE, และ VLOOKUP นั่นเอง ซึ่งผมบอกเลยว่า นี่คือ “หัวใจสำคัญ” ที่จะทำให้คุณใช้ Read more…

By Sira Ekabut, ago
Logic Function

Logic Function สิ่งสำคัญที่คุณต้องเชี่ยวชาญให้ได้

co-create บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


  ในส่วนนี้จะปูพื้นฐานฟังก์ชั่นที่จำเป็นมากๆ ต่อการใช้งานส่วนใหญ่ในชีวิตจริง ทั้งใช้ในการเขียนสูตรปกติ และใช้ในเครื่องมืออื่นๆ ของ Excel เช่น Conditional Formatting, Advanced Filter เป็นต้น

ฟังก์ชั่นที่ใช้ในการคำนวณสรุปผล

การ "สรุปผล" จากข้อมูลจำนวนมาก สามารถทำได้หลากหลายวิธี เช่น หาผลรวม (SUM) , หาค่ามากที่สุด (MAX), ค่าน้อยที่สุด (MIN), หาค่ากลางของข้อมูล ซึ่งมีหลายประเภท เช่น  ค่าเฉลี่ย(AVERAGE) ฐานนิยม (MODE)  มัธยฐาน(MEDIAN), หาค่าการกระจายตัวของข้อมูล ซึ่งก็มีหลายประเภทอีกเช่นกัน เช่น ค่าพิสัย, ค่าเบี่ยงเบนมาตรฐาน

การสรุปผลแบบ Basic

table1-fix การหาค่ากลางของข้อมูล คุณเองจะต้องเข้าใจความหมายของตัวสรุปผลแต่ละตัว และใช้ให้เหมาะกับแต่ละสถานการณ์ เพื่อตีความข้อมูลที่มีอยู่อย่างถูกต้อง โดยเฉพาะการสรุปผลข้อมูลโดยใช้การวัดค่ากลางหรือการกระจายของข้อมูล ซึ่งมีให้เลือกอยู่หลายตัว และไม่ตรงไปตรงมาเหมือนการหาผลรวมธรรมดา ซึ่งจริงๆ แล้วเป็นสิ่งที่คุณน่าจะเคยเรียนมาในวิชาคณิตศาสตร์ หรือวิชาสถิติเบื้องต้นกันบ้างแล้ว แต่ใครไม่เคยเรียนหรือลืมไปแล้วก็ไม่เป็นไร เดี๋ยวผมจะทวนให้แบบเร็วๆ ครับ เราวัดค่ากลางของข้อมูล เพื่อใช้เป็น “ตัวแทน” กลุ่มของข้อมูลนั้นๆ เพื่อความสะดวกในการตีความและทำความเข้าใจ ซึ่งค่ากลางนั้นมีอยู่หลายแบบ แต่ตัวที่เป็นที่นิยม มีดังนี้
  • Mean (ค่าเฉลี่ยเลขคณิต)=AVERAGE
    • เป็นค่ากลางที่นิยมที่สุด คำนวณโดยเอาข้อมูลทุกค่าบวกกันแล้วหารด้วยจำนวนข้อมูล เช่น = AVERAGE(A1:A5) มันจะเอา (A1+A2+A3+A4+A5)/5
    • ถ้ามีค่ามากหรือน้อยผิดปกติอาจจะดึงค่า MEAN ไปในทิศทางนั้นๆ อาจให้ผลไม่ดีได้
  • Mode (ฐานนิยม) = MODE
    • เป็นการวัดค่ากลาง โดยจะแสดงข้อมูลที่เกิดขึ้นบ่อยที่สุด (มีความถี่สูงสุด)
  • Median (มัธยฐาน)= MEDIAN
    • เป็นการวัดค่ากลาง โดยจะนำค่ามากเรียงกันจากน้อยไปมาก แล้วดูตำแหน่งตรงกลาง
    • สามารถใช้ได้แม้กับข้อมูลที่มีค่าที่มากหรือน้อยผิดปกติ เพราะค่าเหล่านั้นจะไม่ส่งผลใดๆ ต่อการคำนวณเนื่องจากจะถูกเรียงลำดับอยู่ที่หัวและท้าย
    • มีฟังก์ชั่นคล้ายๆ กัน คือ QUARTILE (ควอไทล์) และ PERCENTILE (เปอร์เซ็นต์ไทล์) ซึ่ง Concept คล้ายกัน แต่จะแบ่งช่วงข้อมูลต่างกัน

การหาค่าการกระจายของข้อมูล

เราวัดการกระจายของข้อมูล เพื่อดูว่าข้อมูลแต่ละตัวกระจายกันหรือห่างกันมากแค่ไหน กลุ่มข้อมูลที่กระจายกันมากๆ ค่ากลางหรือตัวแทนของข้อมูลอาจจะไม่ได้ใกล้เคียงกับข้อมูลบางตัวก็ได้ เช่น เรามีกลุ่มข้อมูล 2 กลุ่ม มีค่าเฉลี่ยเท่ากัน คือ 70 แต่กลุ่มแรกมีการกระจายตัวน้อย (ข้อมูลมีการเกาะกลุ่มอยู่ที่ใกล้ๆ 70) อีกกลุ่มมีการกระจายตัวมาก เช่น อาจกระจาย 40-100 เลยก็ได้ การวัดการกระจายตัวที่นิยม มีอยู่ 3 แบบ คือ
  • Range (พิสัย) คือการหาผลต่างของค่าสูงสุดและค่าต่ำสุดของข้อมูลนั้น
    • ดังนั้นสามารถใช้สูตร MAX มาลบด้วย MIN ได้ เช่น =MAX(A1:A10)-MIN(A1:A10)
    • ข้อมูลที่ได้จะค่อนข้างหยาบ เพราะใช้ข้อมูลแค่ 2 ตัวคือ ค่าสูงสุดและต่ำสุดเท่านั้น
  • Standard Deviation (ส่วนเบี่ยงเบนมาตรฐาน) เป็นการวัดการกระจายที่มีความนิยมมากที่สุด หลักการคล้ายๆ การหาค่าเฉลี่ยของระยะห่างระหว่างข้อมูลแต่ละตัวกับค่าเฉลี่ยเลขคณิต
    • STDEV หรือ S ใช้กับข้อมูลที่เป็นกลุ่มตัวอย่าง (S=Sample) และ
    • STDEVP หรือ P เอาไว้ใช้กับข้อมูลทั้งหมด (P=Populations)
(more…)

By Sira Ekabut, ago
Lookup / Reference Functions

เทคนิคลับสุดยอด VLOOKUP ข้อมูลเร็วขึ้น 100 เท่า! (ขึ้นไป)

เพื่อนๆ เคยทำงานที่ต้อง Lookup ข้อมูลเยอะๆ ใน Excel รึเปล่าครับ? หลายๆคนอาจใช้เทคนิคปรับโหมดการคำนวณจาก Automatic เป็น Manual (ที่ต้องกด F9 เพื่อคำนวณ) เพื่อให้มันคำนวณทีเดียวเมื่อเราเขียนสูตรทุกอย่างพร้อมแล้ว ที่ต้องทำอย่างนั้นเพราะว่ามันนานมากกกกก สิ่งที่ทำให้มันช้ามักจะเกิดขึ้นกับสูตรประเภทที่ต้อง Lookup ข้อมูล โดยเฉพาะอย่างยิ่งถ้ามีข้อมูลที่ต้อง Lookup เป็นหมื่นหรือเป็นแสนแถว บางทีรอหลายชั่วโมงก็ยังไม่เสร็จ และถ้าสังเกตให้ดี การ Lookup ที่ช้าจะเป็นการ Lookup ประเภท Exact Match เท่านั้น (ต้องเจอผลลัพธ์เป๊ะๆ) ซึ่งจะไม่เกิดอาการช้ากับการ Lookup แบบ Read more…

By Sira Ekabut, ago
Data Validation

สอนทำเกม Sudoku ด้วย Excel

วันนี้ผมจะมาแนะนำวิธีใช้ Excel สร้างเกม Sudoku อย่างง่ายขึ้นมา โดยที่จะมำให้ Excel ไฟล์นี้สามารถตรวจสอบได้ว่าเลขที่เราใส่ลงไปผิดกติกาของเกม Sudoku หรือไม่ แต่อันนี้จะไม่ฉลาดพอที่สามารถเฉลยเกม Sudoku ได้นะครับ ถ้าอยากได้แบบนั้นเดี๋ยวมี Link ให้อีกทีนึงท้ายบทความครับ

กติกาเบื้องต้นเกม Sudoku

sudoku
  • มีตาราง 9 x 9
  • ใส่ได้แค่เลข 1-9
  • ใส่เลขห้ามซ้ำกัน
    • ในแต่ละแถว
    • ในแต่ละคอลัมน์
    • ในแต่ละกรอบตาราง Block ใหญ่ 3 x 3 (เหมือนที่ตีกรอบหนาไว้)
  • จะมีเลขใส่มาให้เบื้องต้นบางส่วนก่อน ถ้าเกมง่ายๆก็จะใส่มาให้เยอะหน่อย ถ้าเกมยาก ก็จะใส่มาให้น้อย
  • เราจะเล่นเกม โดยมีเป้าหมายคือ ใส่เลขต่อให้ครบทุกช่อง โดยไม่ทำผิดกติกาข้างบน ถ้าทำได้ก็ถือว่าจบเกม

วิธีสร้างตัวตรวจกติกาเกม Sudoku

โหลดไฟล์ตัวอย่างได้ที่นี่
  • ไฟล์ก่อนกรอกเลข (แสดงสูตรให้เห็น) sudoku-inwexcel(.xlsx)
  • ไฟล์หลังกรอกเลข (ซ่อนสูตร + lock cell พร้อมให้ user เล่น) sudoku-inwexcel2(.xlsx)
Concept : เช็คว่ามีการทำผิดกติกาหรือไม่ ไล่ตามกติกาที่เขียนข้างบน แล้วนับว่ามีการทำผิดกติกากี่อัน/กี่ครั้ง จากนั้นนำผลการนับที่ได้มารวมกัน ถ้าสรุปแล้วมากกว่า 0 แสดงว่ามีการทำผิดกติกา (more…)

By Sira Ekabut, ago
Logic Function

เจาะลึกสูตร Excel : IF

สูตร IF คืออะไร? สูตร IF (หรือจะเรียกให้ถูกคือฟังก์ชัน IF) คือ ฟังก์ชั่น (Function) ซึ่งทำหน้าที่เลือกใช้สูตรคำนวณได้ 2 แบบ ขึ้นอยู่กับเงื่อนไขที่เราระบุลงไปว่าจริงหรือเท็จ? ถ้าเงื่อนไขเป็นจริง (TRUE) จะทำการคำนวณด้วยสูตรแบบหนึ่ง ถ้าเงื่อนไขเป็นเท็จ (FALSE) จะคำนวณด้วยสูตรอีกแบบหนึ่ง ** ผมใช้คำว่า “สูตร” เพราะใส่สูตรยาวๆ ซับซ้อนแค่ไหนก็ได้ ผมแนะนำให้มองว่า IF 1 ตัว สามารถแตกกิ่งก้านสาขาการตัดสินใจ (Decision Tree) ออกไปได้ 2 กิ่ง Read more…

By Sira Ekabut, ago