ทราบไหมครับว่า Excel มีเทคนิคอ้างอิงช่วงเซลล์ตั้ง 3 แบบ !
3 แบบที่ว่า มีอะไรบ้างน่ะหรือครับ?
1 Colon ( : ) หมายถึง Range
2 Comma ( , ) หมายถึง Union
3 Space ( ) หมายถึง Intersection
คนทั่วไปจะรู้จักแค่ 2 แบบคือ colon (A2:A10) กับ comma (A2,A3,A4,A5)
คนส่วนน้อยจะรู้จักแบบที่ 3 คือ space
คนส่วนน้อยยิ่งกว่าน้อยจะรู้จักการนำทั้ง 3 แบบมา “ฟีเจอร์ริ่ง” กัน
ฟีเจอร์ริ่ง ยังไงน่ะหรือครับ?
ขออธิบายแบบละเอียดยิบซิบซิบมือผ่านบทความนี้ครับ ^__^
สมมติมีข้อมูลหน้าตาแบบนี้
ถ้าเขียนสูตรเป็น
=SUM(C3:C7 A5:D5)
ผลลัพธ์ที่ได้คือ 300
ทำไมถึงได้ 300 น่ะหรือครับ?
ความหมายของสูตรนี้คือ หาจุดตัด (Intersection) ของ C3:C7 กับ A5:D5
นั่นก็คือ C5
จากนั้นครอบด้วย SUM หรือก็คือ
=SUM(C5)
=SUM (300)
=300
เจ๋งดีใช่ไหมล่ะ ^^
คราวนี้ถ้าเขียนสูตรเป็น
=SUM(B3:C7 A4:D5)
ผลลัพธ์ที่ได้คือ 550
ทำไมถึงได้ 550 น่ะหรือครับ?
ความหมายของสูตรนี้คือ หาจุดตัดของ B3:C7 กับ A4:D5
ผลลัพธ์คือ B4:C5
จากนั้นครอบด้วย SUM หรือก็คือ
=SUM(B4:C5)
=20+30+200+300
=550
เริ่มสนุกแล้วใช่ไหมล่ะ ^__^
มาดูกันต่อ
=SUM(A3:A7,D3)
ผลลัพธ์ที่ได้คือ 11,115
สูตรนี้เทียบเท่ากับ
=SUM(A3:A7)+D3
เพราะ comma ( , ) คือ union
นั่นคือรวม D3 เข้าไปด้วยกับ A3:A7 นั่นเอง
= (1+10+100+1,000+10,000) + 4
= 11,115
จริงๆแล้วเขียนสูตรเป็น SUM(A3:A7)+D3 ก็ได้
แต่ SUM(A3:A7,D3) มันดูคูลกว่า
ใช่ไหมล่ะ ^^
แต่ถ้าปรับสูตรเป็น
=SUM(A3:A7:D3)
ผลลัพธ์กลับเป็น 111,110 !
ทำไมน่ะหรือครับ?
เพราะสูตรนี้ เสมือนการลาก A3:A7 ไปทางขวาจนถึง D3 นั่นเอง
SUM(A3:A7:D3) จึงเทียบเท่ากับ
=SUM(A3:A7)
=111,110
“ฮ้า” เลยใช่ไหมล่ะ ^^
คราวนี้ลองเขียนสูตรเป็น
=SUM(A3:A7,A5:D5)
ผลลัพธ์ที่ได้คือ 12,111
ความหมายของสูตรนี้เทียบเท่ากับ
=SUM(A3:A7)+SUM(A5:D5)
เพราะ comma ( , ) คือ union
นั่นก็คือรวม A5:D5 เข้าไปด้วยกับ A3:A7 นั่นเอง
= (1+10+100+1,000+10,000) + (100+200+300+400)
= 11,111 + 1,000
= 12,111
จริงๆแล้วเขียนสูตรเป็น SUM(A3:A7)+SUM(A5:D5) ก็ได้
แต่ SUM(A3:A7,A5:D5) มันดูคูลกว่า
“เฮ๊” เลยใช่ไหมล่ะ ^^
แต่ถ้าเขียนสูตรเป็น
=SUM(A3:A7:A5:D5)
ผลลัพธ์ที่ได้กลับเป็น 111,110
ความหมายของสูตรนี้ เสมือนการลาก A3:A7 ไปทางขวาจนถึง D5
จึงมีค่าเทียบเท่า
=SUM(A3:D7)
หืมม์.. รู้สูตรพวกนี้แล้วใช้ประโยชน์อะไรได้น่ะหรือครับ?
โดยทั่วไปคงไม่มีตั้งใจใครเขียนสูตร SUM(A3:A7:A5:D5) แน่นอน
แต่บางครั้งสูตรนี้อาจเกิดจากความ “ไม่ตั้งใจ”
“ไม่ตั้งใจ” ยังไงน่ะหรือครับ?
เช่น ใช้ฟังก์ชันพวก INDIRECT, OFFSET แล้วผลลัพธ์ไม่ได้ดังหวัง
=SUM(INDIRECT(G2&H2):INDIRECT(G3&H3))
=SUM(OFFSET(INDIRECT(G7),1,0,12,2))
เหตุผลอาจเกิดจากการอ้างอิงเซลล์แบบ “ไม่ตั้งใจ” ก็เป็นได้น๊า ^__^
เพิ่มเติมนิดนึงสำหรับผู้ที่สนใจเทคนิคเชิงลึก
ฟังก์ชันที่สามารถส่งค่ากลับมาเป็นช่วงเซลล์ (Cell Range) หรือเซลล์อ้างอิง (Cell Reference) มี 9 ฟังก์ชัน ได้แก่
อ้อ ! ฟังก์ชัน INDIRECT กับ OFFSET เป็นฟังก์ชันที่คำนวณใหม่ทุกครั้งที่เกิดการเปลี่ยนแปลง (Volatile Function) โดยส่วนตัวแล้วไม่แนะนำให้ใช้ เพราะทำให้ไฟล์คำนวณช้ามาก (ยกเว้นจำเป็นจริงๆ)
ถ้าเป็นไปได้ ใช้ INDEX แทนดีกว่าครับ
ส่วน SINGLE กับ XLOOKUP เป็นฟังก์ชันใหม่ใกล้คลอด น่าจะได้ใช้กันในเร็ววัน ^__^
เอาล่ะ เป็นยังไงกันบ้างครับ
ถ้าคุณอ่านบทความนี้แล้วรู้สึกว่ามีหลายเรื่องที่ไม่เคยรู้มาก่อน
ไม่เป็นไรครับ เพราะคนส่วนใหญ่ก็ไม่รู้เรื่องพวกนี้เหมือนกัน
คนที่รู้เรื่องนี้มีน้อยมากๆ
แต่ว่า… ถ้าคุณอ่านจนถึงบรรทัดนี้
แสดงว่าคุณกลายเป็นคนส่วนน้อยนั้นแล้วล่ะครับ ^__^
สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้เลยครับ
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
Reference:
https://www.youtube.com/watch?v=0936NMCAIXY
https://exceloffthegrid.com/cell-ranges-basic-things-99-users-dont-know/
