Array Formula

เข้าสู่โลกใหม่ของการเขียนสูตร Excel ด้วย Array Formula

บทความนี้ผมตั้งใจจะให้เป็นบทความที่จะสอน Concept ของ Array Formula แบบสั้นๆ พอได้ไอเดียและพอเห็นภาพว่า Array Formula คืออะไร? มีข้อดีข้อเสียอย่างไร ? ใครอยากรู้เชิญอ่านได้เลยครับ รับรองว่าได้ความรู้ดีๆ เพียบเช่นเคย! เมื่อความรู้ดั้งเดิมมีข้อจำกัด ในสมัยก่อน…วิชาวิทยาศาสตร์อย่าง ฟิสิกส์ เคมี ที่หลายๆ คนเคยเรียน สามารถอธิบายปัญหาและปรากฏการณ์ต่างๆ ที่เกิดขึ้นบนโลกนี้ได้เป็นอย่างดี นักวิทยาศาสตร์ยุคแรกๆ เรียนรู้… ทำความเข้าใจกฎเกณฑ์ที่เกี่ยวกับมัน จนคิดว่ารู้จักมันดีในระดับหนึ่งแล้ว แต่ต่อมา เมื่อพวกเขาเหล่านั้นได้พบเจอสิ่งต่างๆ มากขึ้นเรื่อยๆ พวกเขาเริ่มตระหนักได้ว่า ยังมีอีกหลายเรื่องที่วิทยาศาสตร์แบบเดิมอธิบายไม่ได้ เช่น สิ่งที่อยู่นอกจักรวาล การเคลื่อนที่ระดับความเร็วแสง หรือ Read more…

By Sira Ekabut, ago
Array Formula

วิธี Extract ข้อมูลหลายๆ รายการ แบบใช้และไม่ใช้ Array Formula

จากบทความก่อนหน้าได้เกริ่นเรื่องการทำงานของ Array Formula ไปแล้ว คราวนี้จะขอยกตัวอย่างการใช้ที่ค่อนข้าง Advance ขึ้นแล้วนั่นก็คือ การ Extract ข้อมูลหลายๆ รายการ ออกมาตามเงื่อนไขที่กำหนด เช่น โจทย์คือ “แสดงทุกรายการที่อยู่ใน group a ” ออกมา จะเห็นว่าแบบนี้ใช้ VLOOKUP ตรงๆ ไม่ได้แน่นอน เพราะ VLOOKUP จะเจอแค่ข้อมูลที่เจอเป็นแถวแรกเท่านั้น แล้วถ้าเจอแบบนี้เราจะต้องเขียนสูตรยังไง มาดู VDO สอนเทคนิคการทำข้างล่างนี้ได้เลยครับ หลักการ 1. หาแถวที่อยู่ใน Group ที่ต้องการ ใช้ IF เพื่อเช็คว่าข้อมูลในแถวนั้นๆ Read more…

By Sira Ekabut, ago
Array Formula

แฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน!

Excel นั้นยิ่งใช้ ยิ่งศึกษา ยิ่งพบความน่าพิศวง... เพราะมันมีอะไรหลายอย่างมากๆ ที่ถูกเก็บซ่อนเอาไว้ หรือ ไม่ได้แสดงให้เห็นอย่างเด่นชัดนัก วันนี้ผมจะขอมาแฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน! เอาให้เพื่อนๆ ของคุณงงไปเลยว่าคุณรู้เรื่องพวกนี้ได้ยังไง

1. ใช้ Space เป็นเครื่องหมายเชื่อม Cell Reference ก็ได้

เพื่อนๆ คงรู้จักตัวเชื่อม Cell Reference  อย่าง colon (:) ที่ใช้เชื่อมข้อมูลเป็นช่วง หรือตัว comma (,) ที่ใช้เชื่อม Cell ที่ไม่ต่อเนื่องกัน เป็นอย่างดีอยู่แล้ว แต่ผมพนันเลยว่า หลายๆ คนคงไม่รู้จักตัวเชื่อมที่เป็นช่องว่าง (space) แน่นอน ถ้าเปรียบ comma (,) เป็นตัวเชื่อมในวิชาตรรกศาสตร์หรือเซ็ตแล้ว มันจะคล้ายเครื่องหมาย union เพราะเป็นการเชื่อม Range หลายๆ อันเข้าด้วยกัน แต่เจ้าตัวเชื่อมที่เป็นช่องว่าง (space) นั้น ทำหน้าที่เป็นเครื่องหมาย intersect นั่นคือจะให้ผลลัพธ์ที่เป็นช่องที่ซ้อนทับกัน ของ Range ที่เราเลือกไว้นั่นเอง (Intersection) excel-secret-8 ตัวอย่างเช่น =B1:C15 A7:E8 (มี space คั่น) จะให้ผลเป็น Range B7:C8 ที่ระบายสีตามรูปนั่นเอง (อาจจะเอาไปใช้ต่อได้ เช่น SUM เป็นต้น)

2. อ้างอิงข้อมูลจากหลายชีทด้วย 3D Reference

รู้หรือไม่ว่าเราสามารถอ้างอิง Cell Reference แบบ 3 มิติได้ นั่นคือ เป็นการอ้างอิง Cell Reference แบบทะลุข้าม Worksheet นั่นเอง ทำยังไงมาดูกันครับ excel 3d cell reference แทนที่จะใส่การอ้างอิงไปที่ชีทเดี่ยวๆ ตามการอ้างอิงปกติ (เช่น =Sheet1!B2 ) เราสามารถใส่ชื่อชีทแบบเป็นช่วงได้ โดยใส่ชีทที่เป็นจุดเริ่มต้น ตามด้วยเครื่องหมาย colon (:) และ ตามด้วยชีทที่เป็นจุดสิ้นสุดลงไปได้เลย เช่น =SUM(Sheet1:Sheet3!B2:D4) เพียงเท่านี้ มันก็จะทำการบวกข้อมูลช่อง B2:D4 เริ่มจาก Sheet1 ไปจนถึง Sheet3 นั่นคือจะรวม Sheet1, Sheet3 และ Sheet ทุกอันที่คั่นอยู่ระกว่างทั้งสอง Sheet ด้วย! คำว่า "Sheet ทุกอันที่คั่นอยู่ระกว่างทั้งสอง Sheet" หมายความว่า หากในอนาคตเรามีการเพิ่ม Sheet แล้วย้าย Sheet นั้นมาอยู่ระหว่าง Sheet 1 กับ 3 นี้ มันก็จะถูกบวกเข้าไปในผลรวมไปด้วย!! ฟังก์ชั่นที่สามารถใช้ 3D-Reference ได้ ดูได้ที่นี่ => http://office.microsoft.com/en-001/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

3. Paste เป็น Value กับ Format พร้อมกันได้ง่ายๆ

หากเราต้องการ Copy ข้อมูลที่เป็นสูตรเพื่อที่จะมา Paste ลงอีกที่นึงเป็น Value กับ Format พร้อมกัน ปกติเราจะทำได้อย่างมากคือ Paste เป็น Value ทีนึง และ Paste Format อีกทีนึง ไม่สามารถทำพร้อมกันได้ และบางทีก็ชอบมีปัญหาด้วย โดยเฉพาะเวลามี Cell ที่ Merge กันอยู่จะทำให้ Paste ไม่ลง paste-value-format ผมมีเทคนิคเจ๋งๆ มาแนะนำให้ครับ นั่นก็คือ ให้เปิดโปรแกรม Excel อีกอันนึงขึ้นมา (ให้เหมือนเราเข้าโปรแกรม Excel ใหม่อีกรอบ) แล้วสร้างไฟล์ใหม่หรือเปิดไฟล์งานที่ต้องการ Paste ห้ามกด New Workbook หรือ Open Workbook จากโปรแกรม Excel เดิม) จากนั้นค่อยกด Copy จากไฟล์ต้นฉบับ แล้ว Paste ลงไปที่อีกไฟล์นึงไปตรงๆ ไม่ต้องมี Special อะไรทั้งสิ้น เพียงเท่านี้มันจะเป็นการ Paste เป็น Value กับ Format พร้อมกันโดยอัตโนมัติ การทำแบบนี้มีข้อเสียคือ เราจะไม่สามารถ Link หรืออ้างอิงสูตรข้ามไฟล์ในลักษณะนี้ได้ ซึ่งต่างจากการเปิด Workbook ใหม่ หรือ Open File จาก Excel โปรแกรมเดิม (more…)

By Sira Ekabut, ago
Array Formula

การ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่)

Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!? ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup ข้อมูลแบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่ นั่นคือ หากเราจะหาคำว่า B1 จากตารางที่มีทั้งคำว่า b1 และคำว่า B1 Excel จะเอาผลลัพท์จากคำที่เจอก่อน (วิ่งจากบนลงล่าง) โดยไม่สนว่ามันจะเป็นตัวพิมพ์เดียวกับคำที่เราต้องการหรือไม่ เพราะ Excel มันทำงาน Lookup โดยมองทั้งสองคำนั้นเหมือนๆ กันนั่นเอง ทางแก้ไข วิธีที่จะ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่) ได้ Read more…

By Sira Ekabut, ago