การกรองข้อมูล เป็นวิธีที่สะดวกและรวดเร็วในการค้าหาข้อมูลให้มีความเฉพาะเจาะจงมากขึ้น ซึ่งการกรองข้อมูลจะแสดงเฉพาะข้อมูลที่เราต้องการและจะซ่อนข้อมูลที่เราไม่ต้องการที่จะแสดงไว้ เราสามาทำการกรองข้อมูลได้โดยการใช้เครื่องมือ Filter ในการกรอง ซึ่งถือว่าเป็นเครื่องมือพื้นฐานที่ผู้ใช้งานโปรแกรม Microsoft Excel จำเป็นต้องทราบเพื่อนำไปใช้ในการทำงาน Show
เปิดหน้าข้อมูลที่เราต้องการจะกรองขึ้นมา คลิกเลือก Sort & Filter > แล้วเลือก Filter เมื่อเราเลือก Filter แล้วจะเห็นว่าส่วนหัวของคอลัมน์จะมีเลือกศรเพิ่มเข้ามา ทำการคลิกลูกศรแล้วเลือกข้อมูลที่เราต้องการให้แสดง บ่อยครั้งที่เรามีข้อมูลดิบจำนวนหนึ่ง แต่ข้อมูลที่เรากำลังสนใจมันจำเพาะเจาะจงกว่านั้น ดังนั้นการกรองข้อมูลให้แสดงเฉพาะสิ่งที่เราสนใจเป็นสิ่งที่จำเป็นอย่างมาก ซึ่งทำได้ด้วยเครื่องมือ Filter นั่นเอง เครื่องมือ Filter (โดยเฉพาะแบบ Basic) ถือว่าเป็นทักษะพื้นฐาน ที่บริษัท/โรงงาน คาดหวังให้คนทำงานเกี่ยวกับ Excel ทำได้ ดังนั้นใครยังไม่เข้าใจหรือยังใช้ไม่เป็น ผมอยากให้ดูวีดีโอชุดนี้ครับ ไฟล์ประกอบ : inwexcel-filter-practice (.Xlsx) กรองข้อมูลใน Excel ตอนที่ 1/2 : Filter & Sortประเด็นสำคัญ ของ Filter
ประเด็นสำคัญ ของ Sortหาก Sort จากคอลัมน์เดียว ให้กดที่ปุ่มสี่เหลี่ยมที่หัวตาราง จะง่ายกว่า โดย
หากต้องการ Sort แบบเป็นลำดับขั้น ให้เลือกที่เครื่องมือ Sort ที่อยู่บน Ribbon ข้างๆเครื่องมือ Filter
กรองข้อมูลใน Excel ตอนที่ 2/2 : Advanced FilterAdvanced Filter เป็นการกรองข้อมูลชั้นสูง ซึ่งมีความยืดหยุ่นในการกรองข้อมูลมาก สามารถกรองได้ทีละหลายๆเงื่อนไข นอกจากนี้ยังสามารถตัดข้อมูลที่ซ้ำกันออกได้ด้วย ประเด็นสำคัญ ของ Advanced Filter
0 0 ปกติเวลาใส่เลข Running ให้กับข้อมูล 1,2,3,4… หากข้อมูลถูก Filter เลข Running เหล่านั้นอาจจะขาดตอน เช่น 1,3,4,6 แต่เทคนิคนี้สามารถให้เลขยังคงแสดงเป็น 1,2,3,4 ได้ แม้จะถูก Filter แล้วก็ตาม ทำยังไง มาดูกันครับ! วีดีโอสอนการใช้งานสรุปเทคนิคที่ใช้
ขอบคุณสำหรับคำถามเทคนิคนี้ ถูกสอบถามโดยคุณ Farzeed Phewkam ใน Facebook Page ของ inwexcel ซึ่งมีประโยชน์มากครับ ขอบคุณมากสำหรับคำถามดีๆครับ 0 0 บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่
การเรียงข้อมูล (Sort) และ การกรองข้อมูล (Filter) เป็นทักษะพื้นฐานของการวิเคราะห์ข้อมูล ดังนั้นเราควรจะทำทั้งสองเรื่องนี้ได้อย่างคล่องแคล่วครับ ซึ่งปกติแล้ว เราควรจะทำการ Sort ข้อมูลก่อนที่จะใช้ Filter ครับ แต่เราสามารถทำการ Sort ในเครื่องมือ Filter ได้ด้วย ผมเลยชอบใช้ Filter มากกว่า สะดวกมาก การ Sortการ Sort หรือการเรียงข้อมูลช่วยให้เราเห็นภาพข้อมูลในลักษณะที่เป็นระเบียบเรียบร้อยมากขึ้น รวมถึงทำให้เราสังเกตเห็นแนวโน้มหรือความผิดปกติได้ง่ายขึ้นด้วย ดังนั้นการ Sort เป็นสิ่งทีเราควรทำเป็นอันดับแรกๆ เมื่อได้ข้อมูลมาเป็นลักษณะตาราง โดยเฉพาะตารางที่มีจำนวนข้อมูลเยอะๆ เป็นต้น เครื่องมือในการ Sort ข้อมูลนั้น แรกสุดควรเตรียมข้อมูลให้อยู่ในลักษณะ Database คือให้บรรทัดบนสุดของข้อมูลบริเวณที่จะ Sort/Filter เป็นชื่อหัวตาราง ไม่ใช่ตัวข้อมูลจริงๆ และรอบๆ ฐานข้อมูลไม่ควรมีข้อมูลที่ไม่เกี่ยวข้องอยู่ใน Cell ที่ติดกับตาราง เช่น แบบที่ถูกต้องแบบที่ไม่ควรทำ แบบนี้ ok เลย เพราะไม่มีหัวตาราง เพราะรอบๆ ตารางมีข้อมูลที่ไม่เกี่ยวข้องติดอยู่ โดยที่เราสามารถกดใช้เครื่องมือ Sort ซึ่งเลือกได้ 2 ที่ คือ
โดยการ Sort ข้อมูลมีอยู่ 2 ประเภทใหญ่ๆ คือ ต้องการ Sort คอลัมน์เดียวแบบนี้เป็นแบบ Basic สุด คือ ไม่สนใจว่าคอลัมน์อื่นจะเรียงยังไง ซึ่งเทคนิคนี้สามารถใช้ปุ่มสี่เหลี่ยม Filter ช่วยในการ Sort ได้ (อย่างที่บอกไปแล้วตอนต้นว่าเครื่องมือ Filter มัน Sort ได้ด้วย) ซึ่งการ Filter นั้นเลือกได้ 2 ที่ คือ
การ Sort ตรวจหาสิ่งผิดปกติหากคุณ Import Data มาใหม่ๆ ผมแนะนำให้ลองกด Sort ในช่องสำคัญๆ ที่คิดว่าจะต้องมีค่าบางอย่างตามที่เราต้องการ เช่น วันที่ ต้องอยู่ในช่วงวันที่เรากำหนด ไม่ขาดไม่เกิน หรือ ยอดขายไม่ควรมีเลขแปลกๆ ที่มากเกินไป น้อยเกินไป หรือมีข้อมูลขยะปนอยู่ ถ้า Sort แล้วเจอข้อมูลแปลกๆ ก็ควรที่จะกลับไปตรวจสอบแล้วจัดการข้อมูลตั้งแต่ต้นตอว่าเกิดจากอะไร เพราะหากไม่ตรวจให้ดีก่อน คุณอาจจะทำงานแบบสูญเปล่าไปอีกหลายชั่วโมงเลยก็ได้ (Garbage in, Garbage Out) Data ก่อน SortData หลัง Sortดูเหมือนว่าข้อมูลจะปกติดีพบเห็นข้อมูลที่ผิดปกติ เช่น มีตัวอักษรในช่องยอดขาย หรือมีค่ายอดขายที่มาก/น้อยผิดปกติ Sort มากไปน้อยSort น้อยไปมากเมื่อพบสิ่งปกติแล้ว คุณก็ต้องกลับไปดูแหล่งที่มาของข้อมูลอีกครั้งว่ามีอะไรผิดพลาดในขั้นตอนไหน
ต้องการ Sort แบบเป็นลำดับขั้นหลายๆ ชั้นซ้อนกันเช่น ถ้ามีคอลัมน์ A B C มีข้อมูลดังนี้ แล้วผมอยากได้ผลลัพธ์สุดท้ายว่า
ผมสามารถทำได้ 2 วิธี คือ 1.ใช้ปุ่มสี่เหลี่ยม Filter ช่วย Sort และ 2.ใช้เครื่องมือ Sort บน Ribbon ใช้ปุ่มสี่เหลี่ยม Filter ช่วย Sortวิธีนี้ต้องกด Sort หลายรอบ ขึ้นอยู่กับว่าจะเรียงกี่คอลัมน์ โดยให้กด Sort ที่ตัวลำดับความสำคัญอันรองๆ ก่อน แล้วค่อยกด Sort ตัวที่เป็นลำดับความสำคัญหลักใน Step หลังสุด นั่นคือ
ใช้เครื่องมือ Sort บน Ribbon วิธีนี้เป็นการใช้ Tool สำเร็จรูปมาช่วย ให้ไปที่ [Data] –> Sort & Filter –> Sort โดยที่แต่ละ Level ที่จะ Sort เราจะต้องเลือกว่าจะเป็น Sort by คอลัมน์อะไร เรียงโดยอะไร (มักจะเป็น Value) และเรียงจากน้อยไปมากหรือมากไปน้อย โดยที่สามารถกด Add Level เพื่อเพิ่มลำดับการ Sort ได้ แต่วิธีการใช้เครื่องมือนี้ Level ที่อยู่ระดับข้างบน จะถือว่าเป็นคอลัมน์หลัก นั่นคือ เราต้อง Add Level จากบนลงล่าง ตามรูป ซึ่งจะเห็นว่าลำดับการเรียงจะต้องทำตรงกันข้ามกับวิธีใช้ปุ่ม Filter นะครับ เมื่อเรียงไปมาแล้ว จะยกเลิกการเรียง ให้กลับเรียงแบบตอนแรกยังไง?การ Sort นั้นมักเปิดปัญหาอย่างหนึ่งที่คนใช้งานทั่วๆ ไปมักจะเจอกัน นั่นก็คือ พอเรียงข้อมูลไปแล้ว อยากจะให้กลับมาเรียงแบบตอนแรกสุด ก็ทำไม่ได้แล้ว ซึ่งต่างจากการทำ Filter ที่กำลังจะพูดถึง เพราะ Filter กรองข้อมูลแล้ว ยกเลิกการกรองกลับมาเป็นแบบเดิมได้ เทคนิคที่จะช่วยให้ Sort กลับมาเป็นแบบเดิมได้คือ ต้องสร้างคอลัมน์เพิ่ม 1 คอลัมน์ แล้วใส่เลข Running แบบการเรียงตั้งต้นเอาไว้ก่อน เพื่อที่จะได้เรียงกลับมาเหมือนเดิมได้หากต้องการภายหลัง ซึ่งคุณสามารถใช้ Fill Handle มาช่วยทำเลข Running ได้อย่างรวดเร็วครับ
ถ้าหัวตารางมีหลายบรรทัดทำยังไง?ถ้าหัวตารางมีหลายบรรทัด แล้วเรากดปุ่ม Filter ไปตรงๆ ปุ่ม Filter จะไปโผล่ที่บรรทัดบนสุด ซึ่งมันจะคิดว่าบรรทัดถัดลงมาเป็น Data ซึ่งจริงๆ แล้วคือชื่อหัวตารางบรรทัดที่สองต่างหาก แบบรูปข้างล่างนี้ผิดเห็นๆ เพราะมีคำว่า ชื่อ ซึ่งเป็นหัวตารางบรรทัดล่าง มาให้เลือกใน Filter ด้วย วิธีแก้คือ ให้เลือกเฉพาะ Cell ที่เป็นหัวตารางจริงๆ ของเราบรรทัดสุดท้ายก่อนจะกด Filter (ในที่นี้ ตัวอย่างของผมคือบรรทัดที่ 3 ช่อง A3:C3) หรือจะให้ง่ายก็สามารถกดเลือกทั้งบรรทัดเลยก็ได้ แล้วค่อยกด Filter จะได้ผลว่าปุ่ม Filter โผล่ออกมาที่บรรทัดที่ต้องการได้อย่างถูกต้อง การ Filterบ่อยครั้งที่เรามีข้อมูลดิบจำนวนหนึ่ง แต่ข้อมูลที่เรากำลังสนใจมันจำเพาะเจาะจงกว่านั้น ซึ่งเป็นแค่ส่วนหนึ่งของข้อมูลทั้งหมด ดังนั้นการกรองข้อมูลให้แสดงเฉพาะสิ่งที่เราสนใจเป็นสิ่งที่จำเป็นอย่างมาก ซึ่งทำได้ด้วยเครื่องมือ Filter นั่นเอง เมื่อเตรียมข้อมูลเป็น Database แล้ว ให้ Click ที่ช่องใดช่องหนึ่งของตารางข้อมูล (เช่น A1) แล้วกดใช้เครื่องมือ Filter ซึ่งเลือกได้ 2 ที่ คือ
เวลากด Filter แล้วจะมีปุ่มสี่เหลี่ยมขึ้นมาที่ด้านขวาของหัวตาราง เราสามารถกดปุ่มนั้นเพื่อเข้าสู่เมนูการกรองข้อมูล ซึ่งเมื่อกดปุ่มกรองที่คอลัมน์ไหน มันก็จะกรองข้อมูลโดยใช้หลักเกณฑ์คอลัมน์นั้น เราจะลองทำการกรองดู โดยให้ลองเลือกให้ติ๊กถูกเหลือแค่ Food โดยให้ติ๊ก Select All ออกก่อน แล้วค่อยกดเลือก Food และ/หรืออย่างอื่นที่ต้องการ แล้วกด Ok วิธีสังเกตว่ามีการกรองข้อมูลเกิดขึ้นจะเห็นว่าเมื่อกรองข้อมูลแล้ว จะมีเครื่องหมายกรวยกรอง โผล่ขึ้นมาที่คอลัมน์ที่มีการกรองเกิดขึ้น รวมถึงจะเห็นว่า Row Number จะหลายเป็นสีฟ้า แถมเลขก็มีการข้ามลำดับด้วย การกรองด้วย Filter มีอยู่ 2 ลักษณะ ดังนี้
Text Filters จะเลือกได้กรณีข้อมูลเป็น Text ซึ่งมีเงื่อนไขให้เลือกดังนี้
นอกจากนี้ยังใช้เครื่องหมาย Wildcard คือ * หรือ ? ได้ด้วย (จะอธิบายใน Level Advanced) Number Filters นอกจากนี้ยังใช้เครื่องหมาย Wildcard คือ * หรือ ? ได้ด้วย (จะอธิบายใน Level Advanced) Filter ด้วยสี (Filter by Color)นอกจากนี้เรายังสามารถใช้การ Filter ด้วยสี (Filter by Color) ได้ด้วย อันนี้มีประโยชน์เวลานั่งตรวจงานแล้วทำ Highlight สีบางช่องที่สนใจเอาไว้ ทำให้สามารถเช็คหรือหาตอนหลังได้ง่ายขึ้น โดยมันจะดูว่าเรามีการใส่สี Fonts หรือพื้นหลังแบบไหนบ้าง แล้วมันจะขึ้นมาให้เลือกตามนั้นเลย
Filter VS HideFilter กับ Hide ก็เป็นการทำให้ข้อมูลมองไม่เห็นเหมือนกัน แต่มีจุดที่ต่างกันพอสมควร แต่ประเด็นที่สำคัญเวลาเราทำงานกับมันมีอยู่ 2 เรื่องที่เจอบ่อยๆ คือ 1. การ Copy ข้อมูลออกมา Paste ที่อื่น กับ 2. การ Input ข้อมูลหรือสูตรลงไปในบริเวณข้อมูลที่ Filter/Hide การ Copy ข้อมูลออกมา Paste ที่อื่นหากเรา Filter ข้อมูลแล้ว Copy ไป Paste ที่อื่น ข้อมูลที่ถูกกรองทิ้งจะไม่ถูก copy ไปด้วย แต่ข้อมูลที่ถูก Hide ไว้ยังถูก Copy ไปอยู่ดี (เหมือนกับตอนปกติที่ไม่มีการ Hide) FilterHideข้อมูลมีการ “Filter เอานาย ก ออกไป”แล้ว Copy/Paste ไปไว้ที่อื่นข้อมูลมีการ “Hide เอานาย ก ออกไป” (ไม่มีการ Filter) แล้ว Copy/Paste ไปไว้ที่อื่น จะเห็นว่ามีเส้นประขึ้นมาเป็นกรอบหลายๆ ชุด (เส้นประมีการแบ่งแยกบริเวณกัน) ซึ่งบ่งบอกว่า มีการแบ่งข้อมูลออกเป็นหลายก้อนเมื่อ Paste ลงที่อื่นแล้ว จะเหลือแค่ข้อมูลที่มองเห็นหลัง Filter จริงๆจะเห็นว่ามีเส้นประขึ้นมาเป็นกรอบเพียงชุดเดียว (เส้นประไม่แบ่งแยกบริเวณ) ซึ่งบ่งบอกว่า ข้อมูลมีเพียงก้อนเดียวเมื่อ Paste แล้วข้อมูลที่เคยถูก Hide อยู่ก็กลับถูกแสดงออกมา ไม่เหมือนการใช้วิธี Filter ครับ
การ Input ข้อมูลลงไปหากเรา Filter ข้อมูลแล้ว Input ข้อมูลลงไปในบริเวณข้างเคียง แล้วลาก Fill Handle หรือ Copy สูตรลงมา มันจะข้ามบรรทัดที่ข้อมูลถูกกรองทิ้งไป คือมีการกรอกข้อมูลแต่บรรทัดที่มองเห็นอยู่เท่านั้น แต่ข้อมูลที่ถูก Hide ไว้จะถูกกรอกข้อมูลหรือสูตรอยู่เช่นเดิม (เหมือนกับตอนปกติที่ไม่มีการ Hide) FilterHideข้อมูลมีการ “Filter เอานาย ก ออกไป”จากนั้นมีการกรอกข้อมูลข้างๆ แล้วลาก Copy ลงมาข้อมูลมีการ “Hide เอานาย ก ออกไป” (ไม่มีการ Filter) จากนั้นมีการกรอกข้อมูลข้างๆ แล้วลาก Copy ลงมากรอกข้อมูลตอน Filter เอาไว้หลัง Clear Filter กรอกข้อมูลตอน Hide เอาไว้หลัง Unhideจะเห็นข้อมูลขึ้นมาเฉพาะบรรทัดที่มองเห็นข้อมูล (ไม่ได้ถูกกรอกทิ้ง) เท่านั้นจะเห็นข้อมูลขึ้นมาทุกบรรทัดตามปกติ (ที่บรรทัดที่ 2 ไม่ขึ้น เพราะตอนกรอกข้อมูล ผมเริ่มที่บรรทัดที่ 3 แล้วลากลง)
0 0 อย่างที่ผมเคยบอกไปแล้วว่าการแก้ปัญหาหนึ่งๆ ใน Excel นั้นสามารถทำได้หลายวิธีมาก ซึ่งปัญหาการนับข้อมูลแบบไม่ซ้ำกันในคอลัมน์ก็เช่นกัน จะมีวิธีไหนบ้างมาดูกันครับ!! สถานการณ์ของเราคือ…สมมติว่าเรามีข้อมูลอยู่ตารางหนึ่ง เป็นตารางการขายของในช่วงเดือนที่ผ่านมา… โจทย์คือ อยากรู้ว่ามี Sales อยู่กี่คนที่ทำการขายสินค้าได้ในช่วงเวลาที่เราเก็บข้อมูลมา สมมติข้อมูลเป็นแบบนี้ (มีแค่ 10 บรรทัดพอ) => distinct-count ถ้าเราเจอปัญหาแบบนี้ จะแก้ไขได้ยังไงมาดูกันครับ! วิธีที่ 1 : ใช้ PivotTableวิธีนี้ง่ายสุดๆ นั่นคือให้สร้าง PivotTable ลากสิ่งที่เราต้องการจะนับแบบไม่ซ้ำไปที่ Row Label แค่นี้มันก็จะ List item แบบไม่ซ้ำกันมาให้แล้วครับ ซึ่งถ้าจะนับ จะลากเม้าส์แล้วดูที่ Status Bar หรืออาจใช้ COUNTA มาช่วยอีกทีก็ได้ครับ ซึ่งจะได้ 4 คนครับ แต่วิธีนี้ก็มีข้อเสียคือ ต้องคอย Refresh Pivot หากข้อมูล Source Data เปลี่ยนนั่นเองครับ จะลาก item มานับจำนวนว่าแต่ละ sales มี order กี่ครั้งด้วยก็ได้ วิธีที่ 2 : ใช้ Remove duplicatesวิธีนี้ค่อนข้างลูกทุ่งครับ ให้ Copy คอลัมน์ที่ต้องการออกมา แล้วไปที่เครื่องมือ [Data] –> Data Tools –> Remove Duplicates จากนั้น ok เป็นอันจบ วิธีที่ 3 : ใช้ Advanced Filter ก็ได้วิธีนี้ง่ายเหมือนกัน คือ ไปที่ [Data] –> Sort & Filter –> Advanced แล้ว ต้องติ๊ก Unique records only แล้วเลือก copy to โดยใส่ชื่อ Field ที่ต้องการจะ list แบบไม่ซ้ำ แต่มีข้อเสียคือ ถ้าข้อมูลเปลี่ยน ต้องกดใหม่ทุกครั้ง ซึ่งในระยะยาวถือว่าไม่ค่อยเหมาะครับ วิธีที่ 4 : ถ้ายอมให้ Sort ได้ใช้ IF ก็ได้นะวิธีนี้ค่อนข้างง่ายครับ แต่มีข้อจำกัดคือ คุณต้องเรียงคอลัมน์ที่คุณจะนับซะก่อนครับ พอเรียงแล้ว เราก็แค่เช็คว่า “ถ้าช่องที่อยู่ติดกันข้างบนมันซ้ำกันกับบรรทัดตัวเอง ก็ไม่ต้องนับ” วิธีที่ 5 : ถ้าไม่ให้ Sort ก็ใช้ COUNTIF ซิถ้าข้อมูลของเราไม่สามารถ Sort ได้ เช่น ถ้า Sort ปุ๊ป VLOOKUP พัง หรือ Sort แล้วทำให้กรอกข้อมูลลำบาก เราก็ต้องใช้วิธีอื่น เช่น COUNTIF ครับ ซึ่ง COUNTIF จะเป็นการนับว่าใน range มีคำที่เราสนใจ (ตั้งว่าเป็น criteria) อยู่กี่ตัว? หากเราลองเขียนดูจะได้ดังนี้ มันก็นับออกมาจริงๆ นั่นแหละว่าแต่ละบรรทัดมีตัวซ้ำกันกี่ตัว… แล้วเราจะกำจัดตัวที่เกินยังไงดี? ถ้า 4 + 4 + 4 + 4 เราต้องทำให้เป็น แค่ 1… แปลว่าแต่ละอันเราต้องหารด้วย 4 ถึง 4 ครั้งนั่นเอง ( 4×4 หรือ 4^2) = E2/(E2^2) = 1/E2 หรือ = 1/COUNTIF($C$2:$C$11,C2) นั่นเองครับ ซึ่งพอ SUM รวมกันก็จะได้ 4 อยู่ดี! แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน) วิธีที่ 6 : ใช้ COUNTIF แบบ Array Formulaวิธีนี้คล้ายๆ กับวิธีข้างบนแหละครับ ถ้าสังเกตุคือ วิธีข้างบน = 1/COUNTIF(xxx) แล้วเอาผลลัพธ์มาบวกกัน แต่แทนที่เราจะให้ผลลัพธ์มันแสดงแยกช่องกัน แล้วค่อยเอามาบวกทีหลัง คราวนี้เราจะใช้การเขียนสูตรแบบ Array เพื่อให้ทุกอย่างอยู่ในช่องเดียว เช่น =1/COUNTIF(data_range,data_range) =1/COUNTIF(C2:C11,C2:C11) ถ้าลองลากแล้วกด F9 จะเห็นแบบนี้ครับ นั่นแสดงว่าถ้าเรารวบตอนจบด้วย SUM ก็จะได้ 4 เช่นกัน นั่นคือ =SUM(1/COUNTIF(C2:C11,C2:C11)) แต่เราต้องกด Ctrl+Shift+Enter ด้วยเพราะเป็นการเขียนสูตรแบบ Array Formula แต่ถ้าเราเปลี่ยน SUM เป็น SUMPRODUCT เราจะกด Enter ได้ตามปกติ เพราะ SUMPRODUCT รองรับการเขียนแบบ Array ในตัวอยู่แล้ว ถ้างั้นผมก็สามารถเขียนสูตรแบบ Array ได้ว่า =SUMPRODUCT(1/COUNTIF(data_range,data_range)) หรือ =SUMPRODUCT(1/COUNTIF(C2:C11,C2:C11)) ก็จะได้ 4 เช่นกันครับ แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน) วิธีที่ 7 : ใช้ COUNTIF แต่ Flag 1, 0 ทำไง?วิธีนี้มีการพลิกแพลงการใช้ COUNTIF เล็กน้อย โดยจะใส่เงื่อนไขไว้กันการนับเบิ้ลลงไป นั่นก็คือเราจะเริ่มนับจากแถวบนสุดจนถึงตัวเองเท่านั้น ถ้านับแล้วเจอซ้ำมากกว่า 1 ตัวเราจะไม่นับมันอีก เพราะมันจะถูกนับไปก่อนหน้านั้นแล้ว ทีนี้ก็ใส่เงื่อนไขเพิ่ม ว่าถ้านับแล้วมากกว่า 1 ก็ให้เป็น 0 ไปซะ เพราะแสดงว่าซ้้ำแล้ว วิธีที่ 8 : ใช้ฟังก์ชั่น FREQUENCYฟังก์ชั่นนี้เป็นฟังก์ชั่นที่ไม่ค่อยมีคนใช้เป็นเท่าไหร่ แถมเป็นฟังก์ชั่นประเภท Array ด้วย ผมจึงต้องขอโอกาสนี้แนะนำการใช้งานของมันคร่าวๆ ให้ดูก่อนแล้วกันครับ ฟังก์ชั่น FREQUENCY นี้ออกแบบมาใช้เป็นทำตารางแจกแจงความถี่โดยเฉพาะ ดังนั้นมันจึงออกแบบมารองรับข้อมูลที่เป็นตัวเลขเท่านั้น โดยให้เราใส่ค่าที่ต้องการนับ และใส่ช่วงที่เป็นค่าขอบบนของการจัดกลุ่มลงไป เช่น =FREQUENCY(data_array,bins_array)
เนื่องจาก FREQUENCY เป็นฟังก์ชั่นประเภท Array ดังนั้นวิธีเขียนต้องลากช่วง Range ที่จะแสดงผลลัพธ์ก่อน แล้วค่อยเขียนสูตร จากนั้นค่อยกด Ctrl+Shift+Enter ซึ่งถ้าเราใส่ช่วงค่าขอบบนซ้ำกัน มันจะแสดงแค่อันแรกอันเดียว อันอื่นจะเป็น 0 หมด ทีนี้ถ้าเราลองให้ bin_array เป็นค่า data_array ไปเลยล่ะ จะเกิดอะไรขึ้นมาดูกัน? ทีนี้แปลว่าเราเอาความสามารถนี้มาประยุกต์ใช้ได้ หากใช้ IF เช็คว่า FREQUENCY มันนับ >0 แปลว่าเป็นตัวที่ไม่ซ้ำนั่นเอง เราจะนับเป็น 1 แต่ถ้านับได้ 0 เราก็แสดงเป็น 0 ไป ซึ่งเราจะเขียน IF หรือ จะใส่ให้เป็น TRUE/FALSE แล้วแปลงเป็น 1,0 ด้วยการใส่ — ครอบไปก็ได้ ดังนั้นหากเราเอาสูตรแบบนี้ไปนับกับวันที่ มันก็จะต้องทำงานได้ เพราะวันที่ก็คือตัวเลข แต่ถ้าข้อมูลที่ต้องการนับเป็น Text มันจะต้องพลิกแพลงมากหน่อยครับ ( ค่อนข้าง Advanced มาก) Concept ก็คือ เราต้องแปลง Text ให้กลายเป็นเลขซะก่อน โดยใช้ MATCH แบบ Exact Match มาช่วย จากนั้นเราก็ใช้ FREQUENCY ได้แล้ว เพราะทุกอย่างกลายเป็นเลข ตัวไหนที่ซ้ำกันก็จะแสดงเป็นตำแหน่งแรกเหมือนกันหมด วิธีที่ 9 : ใช้ Measure ในเครื่องมือ PowerPivotวิธีนี้เป็นการใช้เครื่องมือ PowerPivot ที่สามารถใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป แต่หลายคนอาจยังไม่เคยลองใช้ แต่จริงๆ แล้วมันทรงพลังมากๆ ครับ เพราะเราสามารถใส่สูตรคำนวณซับซ้อนลงไป (เรียกว่า Measure) แล้วหมุนด้วยเครื่องมือคล้ายๆ PivotTable ได้เลย พูดง่ายๆ คือ แทนที่จะสรุปข้อมูลด้วยฟังก์ชั่นพื้นฐานอย่าง SUM COUNT AVERAGE ที่ทำใน PivotTable ถ้าคุณเปลี่ยนไปใช้ PowerPivot คุณจะสร้างสูตรซับซ้อนแค่ไหนมารองรับก็ได้!! (แต่อาจต้องเรียนรู้ฟังก์ชั่นของ PowerPivot เพิ่มนิดหน่อย) แต่ในเคสนี้ ไม่ต้องใช้สูตรอะไรยากมากมาย ขั้นแรก ให้สร้าง Measure โดยใส่สูตรที่ต้องการก่อน ถ้าเป็น PowerPivot version ใหม่ๆ หน่อย ก็จะมีฟังก์ชั่น DISTINCTCOUNT ให้เลย สามารถเขียนว่า แล้วมันก็จะกลายเป็น Field List ตัวใหม่ ให้ลากไปอยู่ใน Value ของ PowerPivot ได้เลย ซึ่งมันนับไม่ซ้ำจริงๆ เอาไปประกอบกับ FieldList อื่นๆ อย่างวันที่ได้อีก เจ๋งสุดๆ วิธีที่ 10 : คุณล่ะว่าไง?จริงๆ แล้วผมว่า Excel ยังมีวิธีนับอีกเยอะแยะครับ แล้วแต่จินตนาการของคุณแล้วล่ะ! ใครมีเทคนิคการนับอะไรดีๆ ก็แชร์ให้คนอื่นได้เรียนรู้ได้นะครับ 0 0 แต่ก่อนตอนผมใช้ Excel ใหม่ๆ มีปัญหาอย่างหนึ่งที่ผมหงุดหงิดมาก นั่นคือ ผมต้องการลาก Fill Handle แล้วให้ Excel สร้างตัวอักษร A-Z หรือ ก-ฮ โดยอัตโนมัติ แต่สิ่งที่ Excel ทำให้มันกลับแค่เป็นการ Copy ตัว A หรือ ก ที่ผมพิมพ์ลงมาให้ซะงั้น! ผมงงมากเลย เพราะขนาด series ที่เป็นตัวเลขดูเหมือนว่าจะยากๆ เช่น 1,4,7,10,… มันยังฉลาดใส่มาให้ได้ แต่ทำไมแค่ A-Z หรือ ก-ฮ ดันไม่ใส่มาให้ฟะ!? ตอนนั้นก็ต้องใช้วิธีพิมพ์เอาเองบ้าง ใช้ฟังก์ชัน CHAR มาช่วยบ้าง ซึ่งก็ไม่ใช่วิธีที่ดีเท่าไหร่ จนผมได้พยายามค้นใน Internet ก็รู้ว่าจริงๆ แล้ว Excel ทำแบบที่ผมต้องการได้ ด้วยการสร้างสิ่งที่เรียกว่า Custom List นั่นเอง Custom ListCustom List เป็นเครื่องมือที่ช่วยให่เราสามารถสร้าง List รายการแบบที่เรากำหนดได้เอง ซึ่งจะทำให้ Excel สามารถ Auto Fill ข้อมูล หรือ Sort ข้อมูลตาม List ที่เรากำหนดได้ วิธีการสร้าง Custom List
การเรียกใช้ Custom List ที่สร้างไว้แล้วใช้ใน AutoFillวิธีใช้ Custom List ก็ง่ายแสนง่าย แค่พิมพ์ตัวใดตัวหนึ่งที่อยู่ใน List แล้วลาก Fill Handle ยาวลงมาก็ใช้ได้แล้ว ใช้ใน Sortเวลาจะเรียงข้อมูลด้วย Custom List จะมีวิธียุ่งยากเล็กน้อย คือ ให้เราเลือก Sort แบบ Custom Sort (หรือไปที่ Ribbon [Data] => Sort ก็ได้) จากนั้นตรง Order ให้เลือก Custom List… แล้วเลือก List ที่ต้องการ จากนั้นกด OK จากนั้น Excel ก็จะเรียงข้อมูลตาม List ที่เราต้องการครับ ก่อนจากลา…ก็จบแล้วนะครับกับบทความเรื่อง Custom List นี้ หวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ เพราะถ้าใครรู้เทคนิคนี้ ก็สามารถเลิกพิมพ์ List อะไรซ้ำๆ ไปอีกตลอดชีวิต อย่าง A-Z เนี่ย ผมใช้บ่อยมากๆ เลย ลดเวลาไปได้มากครับ แต่มันจะเจ๋งกว่านี้อีก ถ้าเรามี Custom List อื่นๆ เตรียมไว้ด้วย ดังนั้นถ้าใครมี Custom List เจ๋งๆ ก็แชร์กันมาได้นะครับ เผื่อจะเป็นประโยชน์กับเพื่อนๆ คนอื่นด้วย ^^ |