Cell Reference / Defined Name

ใส่เครื่องหมาย $ ในสูตรยังไงไม่ให้งง

พื้นฐานที่สำคัญมากในการเขียนสูตร Excel คือการใส่เครื่องหมาย $ ใน Cell Reference (เช่น A1 C4 ) เวลาเขียนสูตร ซึ่งถ้าใครงงหรือทำไม่เป็น จะไม่สามารถเขียนสูตรที่ซับซ้อนในอนาคตได้เลย ดังนั้นเรามาปูพื้นฐานตรงนี้ให้แน่นยิ่งขึ้นดีกว่า ทำไมต้องใส่เครื่องหมาย $ ล่ะ? เพราะปกติว่าเขียนสูตรที่มี Cell Reference แล้วเรา Copy Cell ช่องนั้นไป Paste ที่อื่น Cell Reference มันจะเลื่อนไปตามทิศทางของการ Copy ซึ่งบางกรณีการเลื่อนนั้นก็เป็นประโยชน์กับเรา เช่น เวลา Copy ลงมาข้างล่าง Read more…

By Sira Ekabut, ago
Cell Reference / Defined Name

เจาะลึกทุกแง่มุมการทำ Dropdown 2 ชั้น (Ver. ปรับปรุงใหม่)

ก่อนหน้านี้ผมได้เคยเขียนอธิบายวิธีทำ Dropdown List 2 ชั้น และ 3 ชั้นขึ้นไปแล้ว… หลายๆ คนก็นำไปใช้ประโยชน์ได้เป็นอย่างดี แต่ปัญหาอยู่ที่ บางคนทำตามแล้วมัน Error!! เอ๊ะ แล้วทำไมบางคนทำตามแล้วไม่ Error แต่บางคนทำแล้ว Error ล่ะ? เรามาไขคำตอบกันในบทความนี้ครับ ยาวหน่อย แต่ได้ความรู้มากมาย รับรองเลย!! ใครไม่เคยอ่านบทความ 2 อันก่อนหน้ามาก่อน สามารถอ่านแค่บทความนี้อันเดียวก็เข้าใจได้ครับ ไม่ต้องย้อนกลับไปอ่านให้เสียเวลา ^^ หลักการทำ Dropdown Dropdown List เกิดจากการใช้ [fusion_builder_container hundred_percent=”yes” Read more…

By Sira Ekabut, ago
Cell Reference / Defined Name

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

การมีชื่อนั้นเป็นสิ่งพิเศษมากนะครับ เพราะชื่อ ทำให้เราสามารถอ้างอิงไปยังสิ่งต่างๆ ได้ง่ายมากยิ่งขึ้น (ลองคิดดูว่าถ้าแต่ละคนในสังคมไม่มีชื่อ จะวุ่นวายแค่ไหน!!) ผมยกตัวอย่างง่ายๆ เช่น ถ้าหากคุณไม่รู้จักชื่อคนในห้องสัมมนา คุณอาจต้องอ้างอิงถึงคนคนนั้นด้วยพิกัด เช่น “เฮ้ย คนที่นั่งเก้าอี้ทางซ้ายสุดของแถวหลังสุดน่ะ” ตรงนี้คล้ายๆ กับการใช้การอ้างอิงด้วยตำแหน่ง อย่าง  A1, B3 ที่ผมได้อธิบายไปแล้วนั่นเอง แต่ในทางกลับกัน ถ้าหากคุณรู้จักชื่อของเค้าแล้ว เช่น นายสมเทพ คุณก็สามารถเรียกเค้าได้เลย และไม่ว่าเค้าจะย้ายไปนั่งที่เก้าอี้ตัวอื่น คุณก็ยังสามารถอ้างอิงถึงเค้าด้วยการเรียกชื่อได้เหมือนเดิม แถมคนอื่นยังเข้าใจง่ายขึ้นด้วยว่าคุณกำลังเรียกใครอยู่ กลับมาที่ Excel… แทนที่เราจะใช้การเรียกชื่อ Cell ตามมาตรฐานที่มีอยู่เดิม เช่น A1 B3 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