06 Apr

การจัดเรียง และ กรองข้อมูล (Sort & Filter)

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


 

การเรียงข้อมูล (Sort)   และ การกรองข้อมูล (Filter) เป็นทักษะพื้นฐานของการวิเคราะห์ข้อมูล ดังนั้นเราควรจะทำทั้งสองเรื่องนี้ได้อย่างคล่องแคล่วครับ ซึ่งปกติแล้ว เราควรจะทำการ Sort ข้อมูลก่อนที่จะใช้ Filter ครับ แต่เราสามารถทำการ Sort ในเครื่องมือ Filter ได้ด้วย ผมเลยชอบใช้ Filter มากกว่า สะดวกมาก

การ Sort

การ Sort หรือการเรียงข้อมูลช่วยให้เราเห็นภาพข้อมูลในลักษณะที่เป็นระเบียบเรียบร้อยมากขึ้น รวมถึงทำให้เราสังเกตเห็นแนวโน้มหรือความผิดปกติได้ง่ายขึ้นด้วย ดังนั้นการ Sort เป็นสิ่งทีเราควรทำเป็นอันดับแรกๆ เมื่อได้ข้อมูลมาเป็นลักษณะตาราง โดยเฉพาะตารางที่มีจำนวนข้อมูลเยอะๆ เป็นต้น

เครื่องมือในการ Sort ข้อมูลนั้น  แรกสุดควรเตรียมข้อมูลให้อยู่ในลักษณะ Database คือให้บรรทัดบนสุดของข้อมูลบริเวณที่จะ Sort/Filter เป็นชื่อหัวตาราง ไม่ใช่ตัวข้อมูลจริงๆ และรอบๆ ฐานข้อมูลไม่ควรมีข้อมูลที่ไม่เกี่ยวข้องอยู่ใน Cell ที่ติดกับตาราง เช่น

 

แบบที่ถูกต้อง แบบที่ไม่ควรทำ
 database-style

แบบนี้ ok เลย

database-style-wrong

เพราะไม่มีหัวตาราง

database-style-wrong2

เพราะรอบๆ ตารางมีข้อมูลที่ไม่เกี่ยวข้องติดอยู่

โดยที่เราสามารถกดใช้เครื่องมือ Sort ซึ่งเลือกได้ 2 ที่ คือ

  1. ที่ [Home] –> Editing –> Sort & Filter –> เลือกการ Sort ที่ต้องการ
  2. ที่ [Data] –> Sort & Filter –> เลือกการ Sort ที่ต้องการ

โดยการ Sort ข้อมูลมีอยู่ 2 ประเภทใหญ่ๆ คือ

ต้องการ Sort คอลัมน์เดียว

แบบนี้เป็นแบบ Basic สุด คือ ไม่สนใจว่าคอลัมน์อื่นจะเรียงยังไง ซึ่งเทคนิคนี้สามารถใช้ปุ่มสี่เหลี่ยม Filter ช่วยในการ Sort ได้ (อย่างที่บอกไปแล้วตอนต้นว่าเครื่องมือ Filter มัน Sort ได้ด้วย) ซึ่งการ Filter นั้นเลือกได้ 2 ที่ คือ

  1. ที่ [Home] –> Editing –> Sort & Filter –> Filter
  2. ที่ [Data] –> Sort & Filter –> Filter
    • ถ้าข้อมูลเป็น Text จะมีให้เลือก Sort A->Z และในทิศกลับกัน
    • ถ้าข้อมูลเป็น Number จะมีให้เลือก Smallest to Largest และในทิศกลับกัน

การ Sort ตรวจหาสิ่งผิดปกติ

หากคุณ Import Data มาใหม่ๆ ผมแนะนำให้ลองกด Sort ในช่องสำคัญๆ ที่คิดว่าจะต้องมีค่าบางอย่างตามที่เราต้องการ เช่น วันที่ ต้องอยู่ในช่วงวันที่เรากำหนด ไม่ขาดไม่เกิน หรือ ยอดขายไม่ควรมีเลขแปลกๆ ที่มากเกินไป น้อยเกินไป หรือมีข้อมูลขยะปนอยู่

ถ้า Sort แล้วเจอข้อมูลแปลกๆ ก็ควรที่จะกลับไปตรวจสอบแล้วจัดการข้อมูลตั้งแต่ต้นตอว่าเกิดจากอะไร เพราะหากไม่ตรวจให้ดีก่อน คุณอาจจะทำงานแบบสูญเปล่าไปอีกหลายชั่วโมงเลยก็ได้ (Garbage in, Garbage Out)

Data ก่อน Sort Data หลัง Sort
ดูเหมือนว่าข้อมูลจะปกติดี พบเห็นข้อมูลที่ผิดปกติ เช่น มีตัวอักษรในช่องยอดขาย หรือมีค่ายอดขายที่มาก/น้อยผิดปกติ
 strange-data Sort มากไปน้อยstrange-data2Sort น้อยไปมาก

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

strange-data3

 

Read More

06 Apr

การอ้างอิงด้วยการตั้งชื่อ (Define Name)

name5

การมีชื่อนั้นเป็นสิ่งพิเศษมากนะครับ เพราะชื่อ ทำให้เราสามารถอ้างอิงไปยังสิ่งต่างๆ ได้ง่ายมากยิ่งขึ้น (ลองคิดดูว่าถ้าแต่ละคนในสังคมไม่มีชื่อ จะวุ่นวายแค่ไหน!!)

ผมยกตัวอย่างง่ายๆ เช่น ถ้าหากคุณไม่รู้จักชื่อคนในห้องสัมมนา คุณอาจต้องอ้างอิงถึงคนคนนั้นด้วยพิกัด เช่น “เฮ้ย คนที่นั่งเก้าอี้ทางซ้ายสุดของแถวหลังสุดน่ะ” ตรงนี้คล้ายๆ กับการใช้การอ้างอิงด้วยตำแหน่ง อย่าง  A1, B3 ที่ผมได้อธิบายไปแล้วนั่นเอง

แต่ในทางกลับกัน ถ้าหากคุณรู้จักชื่อของเค้าแล้ว เช่น นายสมเทพ คุณก็สามารถเรียกเค้าได้เลย และไม่ว่าเค้าจะย้ายไปนั่งที่เก้าอี้ตัวอื่น คุณก็ยังสามารถอ้างอิงถึงเค้าด้วยการเรียกชื่อได้เหมือนเดิม แถมคนอื่นยังเข้าใจง่ายขึ้นด้วยว่าคุณกำลังเรียกใครอยู่

กลับมาที่ Excel… แทนที่เราจะใช้การเรียกชื่อ Cell ตามมาตรฐานที่มีอยู่เดิม เช่น A1 B3 หรือเรียก Range ว่า A2:D5 เราสามารถตั้งชื่อ Cell หรือ Range นั้นๆ ด้วยคำที่ต้องการได้เลย จะเป็นภาษาอังกฤษหรือภาษาไทยก็ได้ เช่น sales, ยอดขาย, ภาษี เป็นต้น

ซึ่งเมื่อคนอื่นมาอ่านไฟล์ของเรา ก็อาจจะเห็นสูตรที่เราเขียนโดยใช้ชื่อในการอ้างอิง ทำให้สามารถเข้าใจงาน Excel เราได้ง่ายขึ้นไปด้วย

การตั้งชื่อ โดยใช้ Name Box

ให้เลือก Cell หรือ Range ที่ต้องการ  แล้วพิมพ์ชื่อที่ต้องการตั้งลงใน Name Box ได้เลย (อยู่ด้านซ้ายของช่อง Formula Bar ที่เอาไว้ใส่สูตร) เช่น

  • หากเราอยากจะตั้งชื่อช่อง C1 ว่า animal ก็ให้เราเลือกที่ช่อง C1 จากนั้นพิมพ์คำว่า animal ลงไปใน Name box แล้วกด Enter
    name1
  • หากเราอยากจะตั้งชื่อ Range B3:B5 ว่า salary ก็ให้เราเลือกที่ Range B3:B5 ก่อน จากนั้นพิมพ์คำว่า salary ลงไปใน Name box แล้วกด Enter
    name2
  • เมื่อเราตั้งชื่อไปแล้ว เราสามารถ Select Cell/Range ที่ตั้งชื่อไว้แล้วง่ายๆ โดยการเลือก Dropdown ที่ Name box นั่นเอง
    name3

การอ้างอิงถึงชื่อที่ตั้งไว้แล้ว

เราสามารถอ้างอิงถึงชื่อที่ตั้งไว้ได้เปรียบเสมือนว่าชื่อนั้นเป็น Cell Reference ปกติเลย ไม่ว่าชื่อนั้นจะเป็น Cell หรือ Range  เช่น หากเราพิมพ์ลงไปว่า =animal จะได้ค่าเหมือนกับ =C1 ซึ่งจะได้ค่าเป็น “ช้าง” ถ้าเราพิมพ์ว่า =SUM(salary) จะได้ค่าเหมือนกับ =SUM(B3:B5) ซึ่งจะได้ผลลัพธ์เป็น 600

การตั้งชื่อครั้งละหลายๆ ชื่อ โดยใช้ เครื่องมือ Create from Selection

เป็นการตั้งชื่อที่ทำได้อย่างรวดเร็ว เพราะมันสามารถให้เราครอบข้อมูลจำนวนมากๆ แล้วตั้งชื่อตามหัวคอลัมน์แต่ละอัน หรือ หัวแถวแต่ละแถวได้โดยอัตโนมัติ วิธีคือให้เลือกพื้นที่ที่ต้องการ  แล้วไปที่ [Formula] –> Defined Names –> Create from Selection (มี Shortcut คือกด Ctrl+Shift+F3)

name4

ชื่อ sales จะแทน B3:B6, product แทน C3:C6, payment แทน D3:D6 (ไม่รวมหัวคอลัมน์)

Scope ของชื่อ อยากให้ชื่อเป็นที่รู้จักแค่ไหน?

แท้จริงแล้ว ชื่อที่ถูกตั้งขึ้นมา สามารถกำหนดให้ถูกเรียกใช้งานได้จากขอบเขตตามที่เราต้องการเรียกว่า Scope นั่นคือ

  • ตั้ง Scope ให้เป็นระดับ Workbook : ให้เรียกใช้ชื่อได้จาก Sheet ใดก็ได้
  • ตั้ง Scope ให้เป็นระดับ Worksheet : ให้เรียกใช้ชื่อได้จาก Sheet ใด Sheet หนึ่งเท่านั้น
  • ถ้าชื่อซ้ำกัน หากเราทำการเรียกใช้ชื่อจาก Sheet ไหน Excel จะมองชื่อที่อยู่ใน Scope ของ Worksheet นั้นก่อนการมองชื่อระดับ Workbook เสมอ ตรงนี้ Make sense มากนะครับ เช่น ถ้าเราพูดถึงคนที่ชื่อว่า “โนบิตะ” คนส่วนใหญ่ในโลกนี้จะนึกถึงโนบิตะในเรื่องโดราเอมอน (เหมือนชื่อระดับ Workbook ที่เรียกได้ว่ามีความเป็นสากลมาก) แต่ถ้าเราเป็นคนที่อยู่ในบ้านที่มีคนชื่อโนบิตะอยู่พอดีเลย คนในบ้านนั้นก็ต้องคิดถึงคนที่ชื่อโนบิตะในบ้านนั้นก่อน (เหมือนกับการอ้างอิงชื่อระดับ Worksheet ที่มีความเป็น local มากกว่า)

ซึ่งการตั้งชื่อด้วย Namebox และ Create from Selection ที่แนะนำไปนั้น จะเป็นการสร้าง Defined Name ที่เป็นระดับ Workbook โดยอัตโนมัติ ซึ่งหากเราใช้วิธี Namebox ในการตั้งชื่อเดียวกันซ้ำในอีก Sheet หนึ่ง คุณจะพบปัญหา เพราะแทนที่มันจะตั้งชื่อขึ้นมาใหม่ มันดั้นเด้งกลับไปยังช่องแรกที่ถูกตั้งชื่อไว้แทน นี่แหละครับ ถึงคราวต้องใช้ความรู้เรื่องของ Scope ในการตั้งชื่อแล้วล่ะ! ทำยังไงมาดูกันครับ

การตั้งชื่ออย่างละเอียดด้วย Define name

ให้คลิ๊กที่ Cell/Range ที่ต้องการตั้งชื่อแล้วไปที่ [Formula] –> Defined Names –> Define Name ได้เลย ซึ่งคราวนี้คุณจะสามารถกำหนดเงื่อนไขต่างๆได้มากกว่าเดิม เช่น Defined Name จะใช้ชื่อว่าอะไร มี Scope เป็นระดับไหน และอ้างอิงไปที่ Cell/Range ไหน (ซึ่งอ้างไปยัง Sheet อื่นได้นะ)

เช่น ใน Sheet1 ช่อง A1 ผมมีค่า 10 แล้วตั้งชื่อว่า age ซึ่งผมใช้ Namebox ในการตั้งขื่อไปก่อน ซึ่งปกติชื่อแรกที่ถูกตั้งขึ้นมาจะเป็นระดับ Workbook โดยอัตโนมัติ

หากว่าใน Sheet2 ช่อง B1 ผมมีค่า 20 อยากจะตั้งชื่อว่า age เหมือนกัน (ซ้ำกับ sheet1) ผมจะสามารถทำได้ก็ต่อเมื่อผมต้องตั้งชื่อ age ใน Sheet2 ให้เป็นระดับ Sheetแทน โดยเปลี่ยน scope ในเครื่องมือ Define Name ให้เป็น sheet2 แทน (ระดับ worksheet) นั่นเอง

name5

สิ่งที่เกิดขึ้นคือ หากคุณทำการอ้างอิงชื่อที่ตั้งขึ้นมา เช่น ใส่สูตรว่า =age เมื่อคุณอยู่ที่ sheet ต่างกัน ก็จะได้ผลลัพธ์ต่างกันไปด้วย  เช่น

  • หากอ้างอิงจาก sheet2 คุณจะได้ค่า 20 (ได้ชื่อระดับ sheet กลับไป)
  • แต่หากคุณอ้างอิงจาก sheet 1 หรือ sheet อื่นที่ไม่ใช่ sheet2 คุณจะได้เลข 10 กลับไป (เป็น name ระดับ workbook)

อย่างที่บอกไปว่าชื่อระดับ worksheet จะได้รับ priority สูงกว่าเสมอ เพราะมีความเจาะจงมากกว่านั่นเอง  

การจัดการชื่อด้วย Name Manager

เมื่อคุณทำการตั้งชื่อไปแล้วด้วยวิธีการต่างๆ ที่พูดถึงก่อนหน้านี้ คุณสามารถเข้ามาจัดการซึ่งรวมถึง การเพิ่ม แก้ไข หรือ ลบ ชื่อเหล่านั้นได้ง่ายๆ โดยใช้เครื่องมือที่ชื่อว่า Name Manager ซึ่งอยู่ที่ [Formula] –> Defined Names –> Name Manager
name-manager

เครื่องมือนี้เหมาะกับการจัดการชื่อจำนวนมากๆ ได้ เพราะว่าจะเห็นภาพรวมของชื่อทั้งหมดที่ถูกตั้งไว้ใน Workbook นั้นๆ

อย่างไรก็ตาม หากมีชื่อเยอะเกินไปจนดูไม่ไหว คุณก็ยังสามารถ Filter ชื่อตามหมวดหมู่ที่ต้องการได้ เช่น อยากเห็นเฉพาะชื่อที่มี Error อยู่ หรือ ชื่อที่มี Scope เป็น Sheet เป็นต้น

name-manager2

05 Apr

การค้นหาข้อมูล และ Wildcard เครื่องหมายมหัศจรรย์

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


 

ปกติแล้วเวลาพูดถึงเรื่องการค้นหาข้อมูล คนส่วนใหญ่คงนึกถึงเครื่องมือ Find (Ctrl+F) ซึ่งผมก็ใช้มันบ่อยมากเช่นกัน แต่วันนี้ผมจะมาเปิดเผยความสามารถลับๆ ของมันที่หลายคนอาจยังไม่รู้ เป็นยังไงมาดูกันเลย!

การใช้เครื่องมือ Find เพื่อค้นหาข้อมูล

เครื่องมือนี้ เอาไว้ค้นหาสิ่งที่ต้องการ มีวิธีสั่งงานโดยให้ไปที่ [Home] –> Editing–> Find & Select –> Find… หรือ กด Ctrl+F ก็ได้ ง่ายกว่าเยอะ โดยที่ก่อนจะทำการค้นหา ถ้าเราเลือก Range เอาไว้ก่อน แล้วค่อยกด Ctrl+F มันจะหาเฉพาะ Range ที่เราเลือก (แต่ถ้าเลือกที่ Cell เดียว มันจะหาทั้งหมดเลย เพราะคงไม่มีใครหา Cell เดียวจริงมั๊ยครับ?)

find

จากนั้นใส่คำค้นหาที่ต้องการใน Find what: จากนั้นก็จะให้เลือกเอาว่าจะกด Find Next หรือ Find All

Find Next ก็จะทำให้ Active Cell เด้งไปยังผลลัพธ์ที่อยู่ถัดไป ส่วน Find All ก็จะ List ผลลัพธ์ทั้งหมดที่เจอมาให้เลย

โดยที่การค้นหาของเรา สามารถกำหนด Options >> เพิ่มเติมได้ ซึ่งจะมีเงื่อนไขให้เลือกเพิ่ม คือ

  • Format… ระบุรูปแบบที่ต้องการ ซึ่งหากขี้เกียจระบุเองก็สามารถกด Choose Format from cell แล้วจิ้มไปยัง Cell ที่มีรูปแบบที่ต้องการได้เลย
  • Within: เอาไว้ระบุว่าจะหาเฉพาะใน Sheet หรือใน Workbook
  • Look in: ตรงนี้มีให้เลือก 2 แบบ ซึ่งสำคัญมากๆๆ นั่นก็คือFormulasหรือValues
    • Formula : จะเป็นการค้นหาจากสิ่งที่อยู่ใน Formula Bar ของแต่ละช่อง
    • Value : เป็นการค้นหาจากผลลัพธ์ที่แสดงออกมาใน Cell แต่ละช่อง
  • Match Case : ติ๊กเลือกถ้าต้องการสนใจตัวพิมพ์เล็กตัวพิมพ์ใหญ่ว่าต้องตรงกัน
  • Match entire cell contents : ติ๊กเลือกอันนี้ ถ้าต้องการให้เจอทั้งคำใน Cell เท่านั้น

การใช้เครื่องมือ Find เพื่อเลือกข้อมูลหลายๆ Cell

ที่ผมบอกว่าเครื่องมือ Find มันเจ๋งก็คือตรงความสามารถ Find All นี่แหละ ปกติเวลาคนหาแล้วกด Find All มันจะ List ผลการค้นหาทั้งหมดที่เจอมาให้ แต่สังเกตว่ายังเลือกช่องเดียวอยู่

findall-1

ตรงนี้แหละ ให้กด Ctrl+A เพื่อเลือกผลลัพธ์ทุกอัน คุณจะเห็นว่า Cell ต่างๆ ใน Excel ก็จะถูกเลือกไปด้วย ซึ่งอันนี้แหละเป็นเทคนิคการ Select Cell ที่เจ๋งมาก

findall-2

Read More

05 Apr

การจัดการข้อมูลเบื้องต้น

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


 

บ่อยครั้งที่ข้อมูลที่เราจะต้องนำมาวิเคราะห์ประมวลผลใน Excel นั้น ไม่ได้อยู่ในรูปแบบที่นำมาวิเคราะห์ได้ทันที ซึ่งอาจมีหลายสาเหตุด้วยกัน เช่น

  • ข้อมูลอยู่ในไฟล์อื่นนอก Excel
  • ข้อมูลอยู่ในสภาพไม่เรียบร้อย มีข้อมูลขยะที่พิมพ์ผิดปนอยู่มาก
  • ข้อมูลมีรูปแบบไม่เป็นไปตามต้องการ เช่น
    • มีข้อมูลชื่อและนามสกุลอยู่ในช่องเดียวกันแต่เราต้องการแยกช่อง หรือ
    • มีข้อมูลวันที่อยู่ในรูปแบบ text เช่น 20150630 แต่เราต้องการนำไปแยกวิเคราะห์ในมุมมอง ปี หรือ เดือน เป็นต้น

Backward Thinking

แน่นอนว่าก่อนที่เราจะเริ่มจัดการข้อมูล เราควรจะต้องรู้ก่อนว่า “สุดท้ายแล้วเราอยากได้อะไร?” เมื่อรู้แล้วค่อยคิดย้อนกลับมายังจุดเริ่มต้นว่า “เราต้องเตรียมอะไร?” ซึ่งเทคนิคการคิดแบบนี้เรียกว่า การคิดย้อนหลังหรือ Backward Thinking ซึ่งเป็นเทคนิคการคิดที่เจ๋งสุดๆ เลย สามารถนำไปประยุกต์ใช้มากมาย แม้แต่การค้นหาเป้าหมายชีวิตของตนเองก็ยังได้

แต่เอาล่ะ กลับมาที่ Excel ก่อนจะเตลิดไปไกลดีกว่าครับ ผมขอยกตัวอย่างดังนี้

backward-thinking

ดังนั้นเรามาดูเทคนิคในการจัดการข้อมูลกันเบื้องต้นดีกว่า ว่าเราจะทำอะไรได้บ้าง

การแตกข้อมูลออกเป็นหลายๆ คอลัมน์ ด้วย Text to Column

บางทีคุณอาจเจอสถานการณ์ที่ข้อมูลที่คุณมีนั้น อยู่ในรูปแบบที่ปนกันอยู่ในคอลัมน์เดียวกัน เช่น
นาย ศิระ เอกบุตร ซึ่งจะเห็นว่ามีคำนำหน้าชื่อ ชื่อ นามสกุล 3 อย่างปนอยู่ในช่องเดียวกัน แต่เราต้องการแยกมันออกมาเป็น 3 ช่อง เราสามารถใช้เครื่องมือ Text to Column มาช่วยได้เป็นต้น

text-col-1

วิธีการเรียกใช้งาน Text to Column ให้เลือกข้อมูลต้นฉบับที่ต้องการจะแยกข้อมูลก่อน (A1:A2) จากนั้นไปที่ [Data] –> Data Tools –> Text to Column

สิ่งที่จำเป็นจะต้องรู้ต่อไปก็คือ การทำงานของ Text to Column นั้น จะมี 2 Mode ให้เลือก ในการที่จะใช้เป็นเกณฑ์ในการแบ่งข้อมูลจากคอลัมน์เดียวเป็นหลายๆ คอลัมน์ นั่นก็คือ

  • Delimited : ใช้กับกรณีที่ข้อมูลมีอักขระพิเศษบางตัวทำหน้าที่เป็นตัวแบ่งคอลัมน์ โดย Excel จะถามว่าไฟล์ที่เราเลือกนั้นมีตัวแบ่งคอลัมน์ คือ อักขระอะไร ซึ่งมีตัวแบ่งที่ใช้กันบ่อยๆ เช่น Tab, Semicolon, Comma (มักใช้กับไฟล์ นามสกุล CSV (Comma Separated Value), Space (ช่องว่าง), Pipeline (เครื่องหมาย | ที่อยู่บนๆ ปุ่ม Enter) เป็นต้น
  • Fixed Width : ใช้กับกรณีที่ข้อมูลสามารถถูกแบ่งคอลัมน์ด้วยความกว้างของจำนวนตัวอักษรที่แน่นอน โดยแต่ละคอลัมน์ไม่จำเป็นต้องเท่ากัน เช่น คอลัมน์แรกกว้าง 3 คอลัมน์ต่อไปอาจกว้าง 5 ก็ได้
    text-col-2

ในตัวอย่างของผม จะเห็นว่ามีตัวแบ่งที่ชัดเจนเลย นั่นก็คือ ช่องว่าง ดังนั้นจะต้องเลือกโหมดเป็น Delimited แล้วเลือก Delimiter เป็น Space นั่นเอง (Excel2013 อาจแสดงภาษาไทยเพี้ยนๆ ตอน Preview ก็โปรดให้อภัยมันด้วยนะครับ)

text-col-3

พอเลือกเสร็จมันจะแสดงหน้าจอตัวอย่างให้ดู และถามว่าในแต่ละคอลัมน์นั้นคือข้อมูลประเภทอะไร ถ้าขี้เกียจแก้ก็ปล่อยเป็น General ไปก็ได้ครับ ทีนี้เราสามารถเลือกได้ด้วยว่าเมื่อทำเสร็จจะให้แบ่งข้อมูลแล้วไปไว้ที่ไหน ถ้าเราไม่ได้แก้ตรงนี้จะเป็นการทับข้อมูลต้นฉบับของเราไปเลยครับ

text-col-4

จะเห็นว่า การจะใช้ Text to Column ได้เราจะต้องลองสังเกตข้อมูลของเราดูก่อน ว่าควรจะใช้ตัวแบ่งแบบไหนมาช่วยนั่นเอง ซึ่งความรู้ตรงนี้จะมีประโยชน์ในหัวข้อถัดไปอย่างมาก นั่นก็คือ การ Import ข้อมูลนั่นเอง

 

 

Import เอาข้อมูลจากไฟล์อื่นเข้ามาที่ Excel

บางทีข้อมูลที่เราต้องใช้ ไม่ได้อยู่ใน Excel ดังนั้น จึงหลีกเลี่ยงไม่ได้ที่เราอาจต้องใช้ Excel ร่วมกับข้อมูลที่อยู่ข้างนอก เช่น เป็น Text File, ไฟล์ Database บน Access, หรือแม้กระทั่งข้อมูลในตารางที่อยู่บน Website

อาจมีหลายสาเหตุที่เรายังต้องพึ่งพาข้อมูลจากภายนอก เช่น ข้อมูลมีขนาดใหญ่ เช่น มากกว่า 1 ล้านบรรทัด ทำให้ใส่ใน Excel ไม่ได้ จึงต้องไปใส่ไว้ในที่อื่น เช่น  Access แทน หรือบางทีข้อมูลจากมีการอัปเดทอยู่เสมอๆ เช่น ราคาหรือข้อมูลข้องหุ้นต่างๆ ที่อยู่ในเว็บไซต์ เป็นต้น

การนำเข้าข้อมูลจากฐานข้อมูลภายนอก ทำได้โดยไปที่ [Data] –> Get External data ซึ่งมีอยู่หลาย Source ให้เลือก ผมจะขอพูดเฉพาะตัวที่น่าจะใช้บ่อยๆ ดังนี้

From Text

วิธีนี้ใช้กับข้อมูลที่อยู่ในรูปแบบ Text ซึ่งรวมถึงไฟล์ที่เป็นนามสกุลพวก .txt หรือ .csv (comma separated value) ซึ่งเมื่อลองกด Import ข้อมูลดูแล้ว จะพบว่ามีเมนูให้เลือก 2 แบบ คือ Delimited กับ Fixed Width ซึ่งเป็นแบบเดียวกับการใช้ Text to Column เป๊ะๆ เลย (สบายเราล่ะ) แต่สิ่งที่อาจต้องเลือกเพิ่มคือ พวกรหัสภาษาของไฟล์ต้นฉบับ ซึ่งถ้ามีภาษาไทยอยู่ ผมแนะนำให้เลือก Thai เช่น 874:Thai (Windows) ครับ ไม่งั้นภาษาจะออกมาเน่าๆ และบางทีก็อาจต้องติ๊ก My Data has Headers ด้วย เพื่อบอก Excel ว่า ในไฟล์ที่กำลังจะ import นั้นมีหัวคอลัมน์มาด้วยนะ

text-import

From Web

อันนี้เหมาะกับการดึงข้อมูลใน Website โดยสามารถเลือกได้ด้วยว่าจะดึงข้อมูลจากตารางไหนที่อยู่ใน Website หน้านั้นๆ โดยมันจะแสดงหน้าตา Website จริงๆ มาให้เราเลือกเลย

สมมติผมอยากได้ข้อมูลเกี่ยวกับรายได้หนัง ผมอาจใส่เว็บไซต์ Address จาก boxofficemojo: เป็น http://www.boxofficemojo.com/yearly/chart/?yr=2015&p=.htm แบบนี้ก็ได้

จากนั้นผมก็เลือกตรงลูกศรที่มีข้อมูลที่เราต้องการ จากสีเหลืองจะเป็นสีเขียว และผมอาจต้องกำหนด Options บางอย่าง เพื่อให้ข้อมูลไม่เพี้ยน เช่น บางที Excel ก็แปลงข้อมูลเป็นวันที่ให้เอง ซึ่งผมไม่ต้องการ จึงต้องเลือก Disable date recognition ไว้ด้วย

web-query

ผลลัพธ์ที่ได้จะออกมาเป็นตารางให้โดยอัตโนมัติเลย

web-query2

นอกจากนี้ เรายังสามารถ Save ลักษณะการ Import นั้นๆ (Save Web Query) ไว้ใช้ภายหลังได้อีกด้วย โดยการกดที่ icon แผ่น disk ข้างๆ คำว่า Options… ซึ่งพอ Save แล้วเราสามารถเปิดใช้ภายหลังโดยการ Open Query File ที่ Save ไว้ในเมนู Open File ปกติเลย 

การ import ข้อมูลที่อยู่ใน Access

การ import ข้อมูลที่อยู่ใน Access จริงๆ แล้วสามารถทำได้ 2 ที่ คือ From Access ตรงๆ และ From Other Sources –> From Microsoft Query แต่ผมว่าวิธีแรกง่ายกว่าครับ

From Access

อันนี้เราสามารถเลือกไฟล์ Access ได้เลยโดยตรงเลย พอเลือกไฟล์ Access แล้วมันก็จะให้เลือก Table/Query ที่เราต้องการจะ Import ต่อไป (ถ้าเป็น Query ตรงช่อง Type จะขึ้นว่า VIEW)

from-access

เมื่อ Import แล้ว จะมีให้เลือกว่าจะให้ Import เป็น Table หรือกลายเป็น PivotTable เลย

from-access2

ซึ่งหาก Import เป็น PivotTable Report จะสามารถ import ข้อมูลมามากกว่าจำนวนแถวที่ Excel รับไหวได้ด้วย คือ มากกว่า 1,048,576 Records ก็ยังได้

มหัศจรรย์การจัดการข้อมูลด้วย Flash Fill ( Excel 2013 เท่านั้น)

ใน Excel 2013 มีเครื่องมือสุดเจ๋งที่ชื่อว่า Flash Fill มาให้เราใช้ ซึ่งความสามารถที่สุดแสนจะชาญฉลาดของมัน คือ การคาดคะเนการกรอกข้อมูลตามตัวอย่างที่เราใส่ลงไป โดยมันจะพยายามเรียนรู้หา Pattern ในตัวอย่างที่เราใส่ลงไป ซึ่งเราสามารถนำมาประยุกต์ใช้ประโยชน์ได้มากมาย เช่น

  • นำมาแบ่งคำ จาก 1 คอลัมน์ ออกมาเป็นหลายๆ คอลัมน์
  • รวมข้อมูลจากหลายคอลัมน์ มาไว้ที่คอลัมน์เดียว ในรูปแบบที่ต้องการ
  • แยกเอาบางส่วนของคำออกมา
  • เปลี่ยน Format ของข้อความให้อยู่ในรูปแบบที่ต้องการ

วิธีการใช้คือ ให้เรากรอกตัวอย่างลงไปให้มันดูก่อน อย่างน้อย 1 ช่อง (ยิ่งทำตัวอย่างให้ดูเยอะยิ่งแม่น)จากนั้น ให้กด Ctrl+E หรือไปที่ [Data]–> Data Tools–> Flash Fill เพื่อทำการ Fill ข้อมูลได้เลย

flash1 flash2 flash3

Excel เข้าใจผิด ก็พิมพ์แก้ให้มันหน่อย จากนั้นมันก็จะเก่งขึ้น

flash4 flash5

พูดง่ายๆ ว่า หากคุณต้องการผลลัพธ์อันรวดเร็ว โดยลักษณะงานเป็นแบบ Adhoc คือไม่ได้ต้องทำเป็นประจำ วิธีใช้ Flash Fill นี้เหมาะกับคุณมากครับ เพราะเราแค่ใส่ตัวอย่างแล้วกด Ctrl+E ให้ก็เสร็จแล้ว เร็วกว่าการมานั่งเขียนสูตรมาก (กว่าจะคิดออกว่าจะเขียนสูตรยังไงก็แย่แล้ว)

แต่ถ้าหากเราต้องทำงานแบบนี้เป็นแบบ Routine คือทำประจำ เช่น รายงานที่ต้องทำทุกอาทิตย์หรือทุกเดือน วิธีที่ดีกว่าคือการลงทุนสร้างสูตรคำนวณ ซึ่งเราจะได้เรียนรู้กันในบทถัดๆ ไปนั่นเอง  วิธีการจัดการข้อมูลด้วยการใส่สูตรมีข้อดีคือ เมื่อข้อมูลต้นทางหรือ Data Source เปลี่ยนไป ผลลัพธ์จะเปลี่ยนตามทันที โดยที่เราไม่ต้องมานั่งใส่ตัวอย่างใหม่ หรือมานั่งกดสั่ง Flash Fill ให้ทำงานเลย วิธีการเขียนสูตรเรียกได้ว่าเป็นการ “ลงทุนระยะยาว” คือ คุณอาจต้องยอมเหนื่อยตอนแรกในการสร้างสูตร ซึ่งอาจจะยากกว่าใช้เครื่องมืออย่าง Flash Fill แต่จะทำให้เราสบายในระยะยาว เพราะเราแทบไม่ต้องทำอะไรอีกเลย นอกจากเอา Data ใหม่เข้ามาใส่ใน Template เดิมที่เราเขียนสูตรไว้แล้ว

03 Apr

การคัดลอก ตัด แปะ แทรก

f4-cycle

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


 

ผมเชื่อว่าการ Copy / Paste ข้อมูล เป็นของชอบของหลายๆ คนเลยล่ะ เพราะมันมีช่วยให้คุณทำงานสบายขึ้นมาก แต่จะสบายมากขึ้นกว่าเดิมอีก หากคุณใช้ Feature นี้ได้อย่างเต็มประสิทธิภาพ

การคัดลอก ตัด แปะ แทรกนั้น สามารถแบ่งเป็น Action mทำที่ต้นทางกับปลายทางได้ดังนี้

copy-paste-concept

Copy vs Cut :  การ Copy ต้นฉบับจะยังคงอยู่ที่เดิม แต่การ Cut ต้นฉบับจะหายไปเลย

Paste vs Insert : การ Paste เป็นการแปะทับลงไปยังเป้าหมายเลย (ข้อมูลเดิมในเป้าหมายหายไป) แต่ Insert เป็นการแทรก (ข้อมูลเดิมในเป้าหมายถูกเลื่อนไปยังที่อื่น)

การคัดลอกข้อมูล (Copy) –> แปะข้อมูล (Paste)

การคัดลอกข้อมูลด้วย Copy & Paste (คัดลอก & แปะ)

  1. คลิ๊กที่ cell/range ต้นทาง กด Ctrl+C เพื่อ Copy หรือคัดลอกข้อมูลเอาไว้
  2. คลิ๊กที่ cell/range ปลายทาง กด Ctrl+V เพื่อ Paste หรือ วางสิ่งที่คัดลอกมา
  3. หากเป็นการ Paste แบบปกติ ข้อมูลจะมาทั้งสูตรและ Format

Tips : หาก Range ปลายทางมีจำนวนช่องที่เอาข้อมูลต้นทางไปลงได้แบบเป็นจำนวน x เท่าพอดีๆ  ข้อมูลจะถูก Copy และ Paste ไปด้วยในลักษณะวนซ้ำ Pattern ไปเรื่อยๆ จนเต็มพื้นที่ แต่ถ้าเลือกปลายทางจำนวนช่องไม่ลงพอดี ก็จะ Paste เท่ากับต้นฉบับเท่านั้น

copy-paste-pattern

 

การ Copy & Paste สูตร กับการเลื่อนตำแหน่งของ Cell Reference

หากคุณลองสังเกตดู เวลาเราเขียนสูตรแล้ว Copy ไปยังช่องอื่น สูตรที่ช่องปลายทางจะไม่เหมือนกับช่องต้นฉบับ เช่น หากในช่อง B1 เขียนสูตรว่า =E1+3 หากเรา Copy สูตรนี้ลงมา Paste ที่ช่อง A7 สูตรมันจะกลายเป็น =D7+3

copy-paste-Relative-cut

 

โดยมันมีหลักการในการเลื่อนของสูตรเวลาถูก Copy ดังนี้

  • หากต้นฉบับเป็นค่าที่เป็น Value (ไม่ใช่สูตร) : ค่านั้นจะถูก Copy มาตรงๆ ไม่เปลี่ยนแปลง
  • หากต้นฉบับเป็นสูตร ที่มีตัวCell Reference : จะมีลักษณะการเลื่อนขึ้นอยู่กับประเภทของ Cell Reference ว่าเป็นCell Referenceแบบใด ซึ่งมีอยู่2ประเภทใหญ่ๆ คือ
    • แบบที่ไม่ได้ Lock ตำแหน่ง Cell Reference และ
    • แบบที่มีการ Lock ตำแหน่ง Cell Reference 

Read More