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

การเตรียมข้อมูลให้อยู่ในลักษณะของ Database

คือการจัดข้อมูลให้มีลักษณะดังนี้

• ทำให้บรรทัดบนสุดของข้อมูลเป็นชื่อหัวตาราง (ไม่จำเป็นต้องอยู่บรรทัดบนสุดของ sheet ก็ได้)
• และในบรรทัดถัดจากนั้น ในแต่ละคอลัมน์จะเป็นข้อมูลเรื่องเดียวกันทุกบรรทัด โดยสอดคล้องกับหัวตาราง
  เช่น ถ้าหัวตารางคือ สินค้า ข้อมูลบรรทัดถัดไปในคอลัมน์นั้นก็จะเป็นชื่อสินค้า เป็นต้น
• บรรทัดล่างสุดก็ไม่ต้องมีการสรุปข้อมูลอะไรทั้งสิ้น เพราะเราจะไปสรุปทีเดียวด้วยการใช้ PivotTable
• นอกจากนี้รอบๆ ฐานข้อมูลไม่ควรมีข้อมูลที่ไม่เกี่ยวข้องอยู่ใน Cell ที่ติดกับตาราง

 

แบบที่ถูกต้อง	แบบที่ไม่ควรทำ
แบบนี้ ok เลย	เพราะไม่มีหัวตาราง
	เพราะรอบๆ ตารางมีข้อมูลที่ไม่เกี่ยวข้องติดอยู่

มันไม่ง่ายอย่างที่คิด

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

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

ดังนั้นสิ่งที่สำคัญมาก คือ ความสามารถในการเตรียมข้อมูลให้เรียบร้อยซะก่อน ซึ่งทำได้หลายวิธี แต่มีหลักการสำคัญคือ การคิดย้อนกลับ หรือ Backward Thinking

Backward Thinking

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

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

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

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

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

วิธีการเรียกใช้งาน 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 ก็ได้
  

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

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

จะเห็นว่า การจะใช้ 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 นั้นมีหัวคอลัมน์มาด้วยนะ

From Web

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

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

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

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

นอกจากนี้ เรายังสามารถ 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)

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

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

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

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

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

 

รายละเอียดลองอ่านได้จากบทความ งานเสร็จฉับไว ด้วย Flash Fill