เทคนิคอ้างอิงเซลล์ที่คน 95% อาจยังไม่รู้ [Cell Range in Excel]

ทราบไหมครับว่า 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/

วิศวกรรีพอร์ต

คนธรรมดาผู้มีประสบการณ์ทำงานหลากหลายตำแหน่ง คลุกคลีกับการทำรีพอร์ตมาโดยตลอด สุดท้ายค้นพบแนวทางของตัวเอง จึงอยากแบ่งปันเคล็ดลับและประสบการณ์ให้กับผู้สนใจ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.