Category: Spreadsheet
-
ใช้ฟังก์ชัน FILTER กรองเอาเฉพาะตัวเลขในคอลัมภ์
วันนี้เจอปัญหาหนึ่งในข้อมูลที่เราเก็บไว้ใช้เอง คือ ในคอลัมภ์นั้นมีทั้งตัวเลข ตัวอักษร และไม่มีข้อมูล แต่เราอยากได้เฉพาะตัวเลข ไม่เอาตัวอักษรและเซลล์ที่ไม่มีข้อมูล โดยปกติ Excel ทำได้โดยใช้ Autofilter ในตารางได้อยู่แล้วล่ะ แต่ประเด็นคือเราอยากใช้สูตร เพราะไม่อยากมาคลิก 😂 ก็คือขี้เกียจนั่นแหละ เพราะต้องเอาไปนับเลขที่ไม่ซ้ำ และใช้ในการคำนวณต่ออีก และคิดว่าฟังก์ชัน FILTER คงทำได้แหละ และไปค้นเจอใน Google Groups นี้ และก็ตรงกับที่อยากใช้พอดี การใช้ฟังก์ชัน FILTER กรองเราเฉพาะตัวเลขในคอลัมภ์ โดยฟังก์ชัน FILTER จะใช้กรองข้อมูลที่ตรงกับเงื่อนไขที่ระบุไว้จาก Range อื่น และเราจะใช้ร่วมกับอีก 3 ฟังก์ชัน (แต่ไม่ยากนะ 😁 อย่าพึ่งรีบหนีไปล่ะ) ก็คือฟังก์ชัน ใครอยากดูไฟล์ตัวอย่าง คลิกตรงนี้และ File > Save as > Download a copy จาก OneDrive ได้เลยนะครับ จากภาพตัวอย่างข้อมูลจะอยู่ในคอลัมภ์…
-
ข้อดีของการใช้ Format as Table ใน Excel
Table ใน Excel เป็นอีกฟังก์ชันหนึ่งที่หลายคนไม่ได้ใช้งาน ซึ่งมักทำให้เสียเวลาในการทำอะไรบางอย่างไปโดยไม่จำเป็น อย่างเช่นการเติมสี จัดรูปแบบเซลล์ ทำแถบสีให้แถวคู่แถวคี่ ใส่ตัวกรองข้อมูล และ freeze แถวบนสุด เป็นต้น โพสต์นี้จึงอยากแนะนำข้อดีของการใช้ Format as Table ใน Excel ว่าจะช่วยให้เราทำงานได้ง่ายและเร็วขึ้นได้ยัง Table คืออะไร? สำหรับผมแล้ว table (ที่ไม่ใช้ data tables ในส่วนของการทำ what-if analysis) คือ การตั้งชื่อ range เพื่อให้เราสามารถเรียกใช้งานได้ง่ายขึ้นมาก ๆ โดยใช้ปุ่ม Format As Table ในแถบ Home หรือกด Ctrl + T สำหรับใครที่ต้องการไฟล์ตัวอย่าง ให้คลิกลิงค์นี้ แล้วเลือกดาวน์โหลดไปลองทำตามดูได้นะครับ 1. ไม่ต้องเสียเวลาปรับสีและรูปแบบ เป็นข้อดึที่เห็นเป็นรูปอธรรมที่สุด ทำแล้วเห็นเลย เพียงแค่เลือกรูปแบบที่โดนใจจากรายการที่มีให้ หลังจากนั้นก็ไม่ต้องทำอะไรแล้ว โดยเฉพาะส่วนที่เรียกว่า…
-
สูตรสุ่มตัวเลขแบบไม่ซ้ำใน Excel
⚠ ข้อควรระวัง – สูตรนี้ใช้ได้เฉพาะใน Excel 365 และ Excel 2021 ซึ่งสนับสนุนการใช้ dynamic array เท่านั้น โดยจะใช้ฟังก์ชัน SORTBY, SEQUENCE และ RANDARRAY ทำงานร่วมกัน โดยจะเริ่มจากง่าย ๆ ไม่ต้องใส่ตัวเลือกอะไรเยอะแยะไปก่อน โดยที่ n คือ จำนวนสุ่มที่เราต้องการ และตัวอย่างในภาพคือ 10 ซึ่งจะเห็นว่าสูตรนี้เหมือนแค่เอาเลข 1 ถึง 10 มาเรียงสลับตำแหน่งกันเฉย ๆ ซึ่งบางท่านไม่ได้ต้องการแบบนี้ การสุ่มจำนวนเต็มแบบไม่ซ้ำ คราวนี้หากเราใส่ตัวเลือกเพิ่มเติมเข้าไปในฟังก์ชัน SEQUENCE ดังนี้ โดยตัวอย่างในภาพคือ เราต้องการสุ่ม 10 เลข เริ่มจาก 100 และเพิ่มขึ้นทีละ 3 อย่างไรก็ตาม หากจำนวนน้อย ๆ ก็ดูเหมือนว่าค่าที่สุ่มได้ดูค่าใกล้ ๆ กันอยู่ซึ่งบางคนอาจไม่ชอบ เพราะจะมีลำดับการเพิ่มขึ้นทีละ 3…
-
สูตร Excel สำหรับแยกชื่อและนามสกุล ด้วยฟังก์ชัน TEXTBEFORE และ TEXTAFTER
ผมเคยเขียนวิธีการแยกชื่อ-สกุลออกมากันไปแล้วตอนหนึ่ง โดยใช้ Text to Columns ในแถบ Data แต่สำหรับบางคนอาจไม่ได้ต้องการแยกแบบถาวร คืออยากให้ถ้าแก้ไขชื่อ หรือสกุลในช่องที่รวมกันไว้ แล้วให้ข้อมูลใน 2 ช่องที่แยกเป็นชื่อกับสกุลนั้นก็เปลี่ยนแปลงตามไปด้วย ซึ่งถ้าความต้องการเป็นแบบนั้น เราก็จะต้องให้สูตร หรือไม่ก็ฟังก์ชันแหละ โดยสำหรับผู้ใช้ Office 365 ตอนนี้ฟังก์ชัน TEXTBEFORE() กับ TEXTAFTER() น่าจะเปิดให้ใช้แล้ว สำหรับผู้ที่ไม่มี 2 ฟังก์ชันดังกล่าว ก็สามารถใช้ LEFT() และ RIGHT() รวมกับ FIND() และ LEN() ได้ แต่ก่อนอื่นมาดูข้อมูลก่อน จากภาพตารางข้อมูลด้านบน ช่องข้อมูลหลักของเราจะเป็นคอลัมภ์ A ซึ่งจะเป็นชื่อ-สกุล รวมอยู่ในเซลล์เดียวกัน แยกด้วย spacebar ส่วนคอลัมภ์ B จะเป็นชื่อที่เราจะใช้ TEXTBEFORE() แยกออกมา และคอลัมภ์ C จะใช้ TEXTAFTER() การใช้งาน TEXTBEFORE()…
-
การทำตัวเลือก drop down แบบค้นหาได้
เราส่วนมากใช้ Google กันจนเคยชินกับการใช้ตัวเลือกที่เขาเสนอมาให้ ซึ่งมันสะดวกมาก เพราะบางทีเราก็ไม่ค่อยแน่ใจนักว่าจะพิมพ์อะไรต่อไปดี 😅 ตัวอย่างที่งานฝ่ายบุคคลพบบ่อยได้แก่ คนที่ชื่อ “นา” แผนก Operation นี่เขาชื่อจริงว่าอะไรนะ โดยเฉพาะคนที่ไม่ได้ส่งเมล์หาคนคนนั้นบ่อย ๆ แล้วถ้าเราใช้ Excel จะพอมีวิธีไหนช่วยกรองข้อมูลเฉพาะที่ตรงกับที่เราพิมพ์เข้าไปได้บ้าง แล้วให้ cell ที่ต้องการนำข้อมูลที่เกี่ยวข้องขึ้นมาแสดงผลเอง มาดูวิธีกันครับ ก่อนอื่นข้อมูลตัวอย่างอยู่ในซีตชื่อ “data” โดยข้อมูลนี้ได้จากการสุ่มมาจาก https://kidhaina.com/thainamegenerator.html และเหมือนเช่นเคย (สำหรับผม 😁) ข้อมูลทำการ Format as Table แล้ว และตั้งชื่อเป็น tblData มีอีก 2 คอลัมภ์ที่นำข้อมูลที่มีอยู่มาเชื่อมกัน โดยคอลัมภ์ i หรือ forlookup จะใช้แสดงผลลัพธ์เมื่อผู้ใช้เลือกชื้อใดชื่อหนึ่ง ส่วนคอลัมภ์ j หรือ forsearch ใช้เพื่อช่วยให้เราสามารถค้นหาได้ทั้งภาษาไทยและภาษาอังกฤษ ดังภาพ ฟังก์ชันที่ใช้กรองข้อมูล สำหรับการกรองข้อมูลที่มีอยู่เทียบกับข้อมูลที่ถูกพิมพ์เขามา เราจะใช้ฟังก์ชัน FILTER(), SEARCH() และ…
-
การใส่เครื่องหมายคำพูดในสูตร Excel
อาจมีบางครั้งเหมือนกันที่เราต้องการใช้ผลลัพธ์ใน Excel ที่เป็นเครื่องหมายคำพูด หรือเครื่องหมายอัญประกาศ หรือบางคนก็เรียกว่า “ฟันหนู” (มีหลายชื่อเหมือนกันแฮะ) โดยในภาษาอังกฤษก็คือ double quote (“) หรือเรียกเต็มคือ double quotation mark นั่นเอง และก็เป็นที่รู้กันอยู่แล้วว่า double quote ใช้สำหรับกำหนดช่วงของข้อความในสูตร Excel เช่น ถ้าเราใช้สูตร =A1&” นิ้ว” และในเซลล์ A1 มีค่าเท่ากับ 3 ผลลัพธ์ในเซลล์ที่เราใส่สูตรไว้จะแสดงเป็น 3 นิ้ว ดังนั้นถ้าเราใส่เครื่องหมายคำพูดไม่ครบคู่ในสูตร เซลล์นั้น ๆ ก็จะ error ดังตัวอย่างในภาพด้านล่างนี้ และหากทำได้ Excel จะแก้ไขให้ได้ สำหรับสูตรที่ไม่ซับซ้อน จากตัวอย่างด้านบน หากเราตอบไว้ใช่ Excel จะแก้ไขให้ได้ โดยเพิ่มเครื่องหมายคำพูดขึ้นมาอีกตัว ซึ่งเป็นวิธีที่เรียกว่า ใส่ escape string ในสูตร ดังภาพด้านล่าง หรือหากใครใช้สูตรที่ซับซ้อนเกรงว่าจะงง เราสามารถใช้ฟังก์ชัน…
-
การตัดข้อมูลซ้ำออกใน Excel
สำหรับใครที่เจอปัญหาที่จะต้องกรอง หรือตัดข้อมูลซ้ำออกบ่อย ๆ โดยเฉพาะข้อมูลที่มาจาก Microsoft Forms หรือ Google Forms ซึ่งตั้งแต่ก่อนจะต้องใช้ advance filter มาช่วยจัดการ แต่ค่อนข้างจะใช้หลายคลิกถึงจะได้เฉพาะข้อมูลที่ต้องการ รวมถึงหากข้อมูลเปลี่ยนไปข้อมูลที่กรองออกมาแล้ว จะไม่เปลี่ยนแปลงตาม (ไม่ dynamic update) ตอนนี้ใน Excel มีฟังก์ชันชื่อ UNIQUE มาช่วยแก้ไขปัญหานั้นแล้ว ฟังก์ชัน UNIQUE ใน Excel ⚠ แต่ต้องบอกไว้ก่อนว่า จะใช้ได้เฉพาะผู้ที่เป็นสมาชิก Office 365 หรือ Excel 2021 เป็นต้นไป โดยฟังก์ชันนี้จะกรองเอาเฉพาะข้อมูลที่ไม่ซ้ำกันมาให้เรา การใช้งานฟังก์ชัน UNIQUE วิธีใช้งานง่าย ๆ คือใช้สูตร =UNIQUE(A2:A40) ใน cell แรกที่เราต้องการจะใส่ข้อมูลที่ผลการกรอกข้อมูลจะออกมา ไวยากรณ์ (Syntax) array ก็คือ range ข้อมูล หรือจะใส่ข้อมูลเป็น array…
-
การนับแบบหลายเงื่อนไขใน Excel
ในบางครั้งเราก็ต้องการรู้จำนวนบางสิ่ง โดยที่มีหลายเกณฑ์ หรือหลายเงื่อนไข เช่น อยากรู้ว่าพนักงานในฝ่ายวิศวกรรมมีเพศชายกี่คน เพศหญิงกี่คน หรือ อยากรู้ว่า รถรุ่นนี้ สีแดงในภาคตะวันออกขายได้กี่คัน เป็นต้น เมื่อใช้ Excel เราจะใช้สูตรอะไร หรือฟังก์ชันไหนมาช่วยดี มาหาคำตอบกันในโพสต์นี้ครับ สรุปย่อ ใน Excel มีฟังก์ชันชื่อ COUNTIFS() สำหรับใช้นับแบบหลายเกณฑ์ หรือเงื่อนไข โดยจำนวนที่นับได้นั้น คือ จำนวนที่เข้าทุกเกณฑ์ หรือทุกเงื่อนไขที่ใส่เข้าไปในฟังก์ชัน หรือพูดให้ง่ายก็คือ COUNTIFS() ใช้เงื่อนไขแบบ “และ (AND)” ดังนั้น ⚠️”ข้อมูลที่ตรงแค่บางเงื่อนไขจะไม่ถูกนับ” นะ ตัวอย่าง ขอยกตัวอย่างเป็นการนับพนักงานในแต่ละแผนกก็แล้วกันครับ โดยผลลัพธ์ที่ได้จะเป็นไปตามนี้ ซึ่งจะได้มาจากการนับข้อมูล ซึ่งผมกำหนดให้เป็นตาราง (Format as Table) ไว้แล้ว ดังภาพด้านล่าง หรือจะดูจากลิงค์ หรือดาวน์โหลด “ไฟล์ตัวอย่าง” มาลองใช้ดูก็ได้ 😁 โดยตารางนี้จะชื่อ EmployeeTable และมีหัวตารางชื่อ Firstname, Lastname,…
-
การทำตัวเลือกแบบ drop down แบบหลายชั้นใน Excel
สรุปย่อ การทำตัวเลือกแบบ drop down หลายชั้น หรือตัวเลือกในเซลล์หนึ่งขึ้นอยู่กับข้อมูลที่เราเลือกในเซลล์อื่น เราจะใช้ data validation, named range, และ INDIRECT() ทำงานร่วมกัน ตัวอย่าง สมมติว่าเราต้องการให้ตัวเลือกในคอลัมภ์ A เป็นประเภทของสินค้า ซึ่งมี 3 อย่าง ก็คือ ผลไม้ เนื้อสัตว์ และพืชผัก และข้อมูลในคอลัมภ์ B จะขึ้นอยู่กับสิ่งที่เราเลือกในคอลัมภ์ A เช่น ถ้าคอลัมภ์ A เลือกเป็น “พืชผัก” ในคอลัมภ์ B ควรจะมีตัวเลือกเป็น ผักหัว ผักใบ ผักสลัด เป็นต้น ดังภาพ ขั้นตอนที่ 1 กำหนดตัวเลือกและกำหนดชื่อ (named range) จากภาพตัวอย่างด้านบน จะเห็นว่าผมใส่ข้อมูลที่จะใช้เป็นตัวเลือกไว้ในคอลัมภ์ E ถึง G โดยประเภทที่จะเลือกในคอลัมภ์ A จะอยู่ในแถวที่…
-
การใช้ฟังก์ชัน LAMBDA ใน Excel
สรุป ฟังก์ชัน LAMBDA ใช้สำหรับ สร้างฟังก์ชันใหม่ เพิ่มเข้าไปในไฟล์ Excel (workbook) โดยไม่ต้องเขียน Macro(s) หรือ VBA code 😊👍 เพิ่มเข้าไปในไฟล์ ซึ่งจะช่วยให้เราไม่ต้องพิมพ์ หรือ copy-paste สูตรที่เขียนขึ้นซ้ำแล้วซ้ำอีก รวมทั้งฟังก์ชันที่สร้างจาก LAMBDA() นี้ จะสามารถเรียกใช้ตลอดในไฟล์ Excel นั้น ๆ เสมือนเป็นหนึ่งในฟังก์ชันที่มีมากับ Excel ข้อจำกัดในการใช้งาน! ตอนนี้ฟังก์ชัน LAMBDA อยู่ในช่วงทดลองใช้งานในกลุ่ม Office Insiders อยู่ ดังนั้นสำหรับผู้ใช้งานทั่วไป จะยังไม่เห็นฟังก์ชันนี้ เปิดให้ใช้งานทั่วไปแล้ว (ข้อมูล ณ วันที่ 25 เม.ย. 2566) รูปแบบการใช้งาน (Syntax) อาร์กิวเมนต์ (Arguments) อาร์กิวเมนต์ คำอธิบาย จำเป็นต้องมี? parameterX ใช้กำหนดค่า หรือตัวแปรต้นที่จะใช้ประกอบการคำนวณ แต่จะมีได้ไม่เกิน…