Blog

บล็อกนี้ จะแนะนำเรื่องพื้นฐานสำหรับ Excel นั้นคือระบบการอ้างอิงตำแหน่งแบบ A1 Notation หรือ ภาษาไทยจะใช้คำว่า สัญกรณ์ A1

สิ่งสำคัญในการทำงานด้วย Excel ก็คือการอ้างอิงตำแหน่งสำหรับการคำนวณ เพราะในการทำงานจริง สมมติมีจำนวนยอดจำหน่ายของสินค้า สัก 100 ชนิด เราต้องการทราบยอดจำหน่ายรวมของทั้งหมด เราคงไม่เขียนสูตรเป็น 1000 + 290 + 2500 +…

แบบนั้นเรียกว่าเครื่องคิดเลข ไม่ใช่ Excel

Excel ออกแบบมาเป็นเครื่องช่วยคำนวณอย่างรวดเร็ว โดยทำงานในลักษณะตาราง ถ้าเป็น แนวนอน จะเรียกว่า Row หรือ แถว แนวตั้งเรียกว่า Column หรือ สดมถ์ (แต่ทับศัพท์ว่าคอลัมน์จะคุ้นเคยมากกว่า) และจุดตัดระหว่าง แถว กับ คอลัมน์ เรียกว่า Cell หรือ ช่อง

การอ้างอิงตำแหน่ง หรือ Reference ใน Excel เป็นเรื่องพื้นฐานที่สำคัญมาก เพราะการใช้สูตรหรือฟังก์ชันต่าง ๆ ล้วนแล้วแต่ต้องการอ้างอิงตำแหน่งทั้งสิ้น ไม่ว่าจะเป็นการอ้างอิง Cell หรือ Range ก็ตาม

การอ้างอิงตำแหน่งแบบ A1 Notation

ระบบการอ้างอิงตำแหน่งแบบ สัญกรณ์ A1 จะเป็นมาตรฐานตั้งต้นสำหรับ Excel และซอฟต์แวร์ประเภทตารางคำนวณอื่น ๆ

• ตำแหน่งของแถว จะอ้างอิงเป็นตัวเลข 1 2 3 ไปเรื่อย ๆ จากบนลงล่าง
• ตำแหน่งของคอลัมน์ จะอ้างอิงเป็นตัวอักษรโรมัน A B C ไปเรื่อย ๆ จากซ้ายไปขวา
• การอ้างอิงตำแหน่งเซลล์หรือช่อง ใช้ตำแหน่งคอลัมน์กับแถว เช่น A1 B2 C3

อย่างเช่น ถ้าเราอ้างอิง H5 ก็จะหมายถึงเซลล์หรือช่องที่เป็นจุดตัดของคอลัมน์ H กับแถว 5

การเอาไปใช้ประกอบฟังก์ชัน

การอ้างอิงแบบ A1 Notation จะนำไปใช้งานในการอ้างอิงตำแหน่งของตัวที่จะเอาไปคำนวณ ตัวอย่าง เราใส่จำนวนยอดจำหน่ายสินค้าในคอลัมน์ D ตั้งแต่ D2 จนถึง D151 ราคาจำหน่ายต่อหน่วยที่คอลัมน์ E ตั้งแต่ E2 จนถึง E151

ถ้าเราต้องการแสดงจำนวนยอดจำหน่ายของสินค้าทั้งหมด โดยให้แสดงผลลัพธ์ในคอลัมน์ H ตรงช่อง H2 เราคงไม่พิมพ์ว่า

= 113,101 + 66,041 + 9,651 + 71,695 +… ไปจนถึงตัวเลขสุดท้าย

เพราะไม่งั้น มันคือเครื่องคิดเลขดี ๆ นี่เอง ไม่ต้องใช้ Excel เป็นตารางคำนวณกันพอดี เราจะพิมพ์สูตร โดยอ้างอิงตำแหน่งเซลล์ว่า

= = D2+D3+D4+D5+D6+D7+D8+D9+D10+D11 + ไปจนถึง D151

ซึ่งจะเป็นการอ้างอิงตำแหน่งของเซลล์ และจะสะดวกเวลาเปลี่ยนตัวเลขโดยไม่เปลี่ยนสูตรคำนวณ

แต่ (อีกรอบ) เราคงไม่เขียนสูตรซื่อ ๆ ตรง ๆ แบบข้างบนนั้นแน่นอน เพราะถ้าเกิดต้องคำนวณสัก 1,000 หรือ 100,000 คงเขียนกันตาเหลือก

Range

Excel ช่วยให้เราทำงานง่ายขึ้น โดยการอ้างอิงตำแหน่งเซลล์แบบช่วง หรือ Range ได้ ถ้าให้นิยามสั้น ๆ Range ก็คือช่วงเซลล์ที่เราต้องการคำนวณ จากช่อง (Cell) หนึ่งไปอีกช่อง (Cell) หนึ่ง

เช่นจะคำนวณผลรวมตั้งแต่ D2 มาจนถึง D151 แทนที่จะเขียน

 =D2+D3+D4+D5+D6+D7+D8+D9+D10+D11…D151

ซึ่งจะยาวยืดยุ่งยาก ยิ่งถ้าข้อมูลมีเป็นร้อยเป็นพัน หรือ จนถึงหลักหมื่น คงพิมพ์ไม่ไหว ตรวจสอบก็ลำบาก ก็ต้องใช้ Range หรือการกำหนด “ช่วง” เข้ามาแทน

กำหนดช่วงแบบต่อเนื่อง

ถ้าต้องการกำหนดช่วงที่ต่อเนื่องกัน ให้ใช้โคลอน (:) โดยเริ่มที่เซลล์แรกคั่นด้วย : แล้วต่อด้วยเซลล์สุดท้าย เช่น D2:D151 ก็หมายถึงช่วงตั้งแต่ D2 ไปจนถึง D151

เมื่อเราใช้ function Sum มาผสมกับการอ้างอิงตำแหน่งแบบช่วง หรือ Range ก็จะได้เป็น

=SUM(D2:D151)

และช่วงที่ต่อเนื่องนี้ ไม่จำเป็นต้องเป็นแค่คอลัมน์ หรือ แถว เดียว เราจะใช้รวมกันก็ได้ เช่น เรามีตัวเลขอยู่ในเซล B1 ถึง B6 กับ C1 ถึง C6 กับ D1 ถึง D6

ถ้าเราต้องการบวกข้อมูลทั้งหมด ทุกเซลล์ที่กล่าวถึงรวมกัน เราสามารถใช้เครื่องหมาย โคลอน (:) นี้ ใช้เพื่อแสดงช่วงว่าจากไหนถึงไหน  B1:D6 ได้เลย

=SUM(B1:D6)

ถ้าเราต้องการทั้งคอลัมน์ ไม่สนใจว่าจะไปสิ้นสุดที่แถวไหน ใช้คอลัมน์นั้น โดยไม่ต้องใส่เลขแถวกำกับ เช่น

=SUM(B:B)

ถ้าเราต้องการทั้งแถว ไม่สนใจว่าจะไปสิ้นสุดที่คอลัมน์ไหน ใช้ตัวเลขของแถวนั้น โดยไม่ต้องใส่คอลัมน์กำกับ เช่น

=SUM(2:2)

เช่นเดียวกัน ถ้าเราต้องการ คอลัมน์ A จนถึง C ทั้งหมด ก็ใช้

=SUM(A:C)

กำหนดช่วงแบบไม่ต่อเนื่อง (Union)

แต่บางครั้ง ช่วงที่เราต้องการก็ไม่ได้อยู่ติดกัน การอ้างอิงตำแหน่ง ที่มีการเว้นช่วงไม่ได้อยู่ติดกัน จะเป็น การใช้จุลภาค (, หรือ comma) เป็นการ union หรือ รวมช่วงที่แยกกัน เข้าด้วยกัน

ยกตัวอย่าง เราจะหาผลรวม (SUM) ของข้อมูลใน ช่วง A1 – A12 กับ  C1 – C12) เราจะใช้ : แบบด้านบนไม่ได้ เพราะถ้าใช้

=SUM(A1:C12)

จะหมายถึง คอลัมน์ A B และ C แต่เราจะไม่ได้เอา B

เราจะเขียนแบบซื่อ ๆ ก็ได้

=SUM(A1:A12)+SUM(C1:C12)

แต่เราสามารถเขียนสั้นกว่านั้น โดยใช้จุลภาคคั่น

=SUM(A1:A12, C1:C12)

กำหนดช่วงแบบเอาเฉพาะส่วนที่มีร่วมกันอยู่ (Intersect)

ถ้าเราต้องการเอาช่วงข้อมูล เฉพาะในส่วนที่คาบเกี่ยวกัน เราจะใช้การเว้นวรรค ในการกำหนด ตัวอย่างเช่น ถ้าเราต้องการหาผลรวมระหว่างช่วง B1 ถึง B12 กับช่วง A2 ถึง D3 เราจะเขียนแบบนี้

==SUM(B1:B12 A2:D3)

นั่นคือ ส่วนที่จะเอามาบวกกัน จะเป็นช่วง B2 ถึง B3 เท่านั้น ซึ่งเป็นส่วนที่ทั้งสองช่วงซ้อนทับกันอยู่

ถ้าเกิดเรากำหนดช่วงผิด เช่นเราจะเอาช่วง

=SUM(A1:A18 C1:C18)

จะได้ผลลัพธ์เป็น #NULL! เพราะไม่มีส่วนที่ทั้งสองช่วงซ้อนทับกัน อย่างที่เขียนถึงในตอน Error ใน Excel

Relative References

เวลาเราเขียนสูตรโดยอ้างอิงตำแหน่ง เวลาต้องคำนวณต่อเนื่องกัน หลายคนมักจะชอบ “ลากสูตร” กัน อันนี้เป็นจุดเด่น ที่สะดวกดี

“ลากสูตร” ในที่นี้ หมายถึง เขียนสูตรที่เซลล์ใดเซลล์หนึ่ง แล้วเอาเมาส์ไปวางตรงมุมขวาล่าง ให้เคอร์เซอร์เป็นเครื่องหมายบวก แล้วคลิกซ้ายลากลง ขึ้น ซ้าย ขวา ตามใจชอบ ว่าจะวางสูตรต่อเนื่องไปทางไหน จะได้ สูตรจะเหมือนเดิม แต่ตัวอ้างอิงจะเปลี่ยนไปตามทิศทางการลาก

รูปแบบการอ้างอิงตำแหน่งที่เปลี่ยนไปตามทิศทางที่ชัดเจน แบบนี้เรียกว่า Relative References (การอ้างอิงแบบสัมพัทธ์) หรือ การอ้างอิงที่แปรผันไปตามที่เราลากไปซ้าย ขวาหรือว่า บน ล่าง ซึ่งมีประโยชน์มาก และใช้งานสะดวกมาก

ลองย้อนกลับไปที่ตัวอย่างแรกสุด ของบล็อกนี้ เราใส่จำนวนยอดจำหน่ายสินค้าในคอลัมน์ D ตั้งแต่ D2 จนถึง D151 ราคาจำหน่ายต่อหน่วยที่คอลัมน์ Eตั้งแต่ E2 จนถึง E151

ถ้าเราต้องการรายได้จากยอดจำหน่าย เป็น ยอดจำหน่าย x ราคาต่อหน่วย โดยให้แสดงผลลัพธ์ในคอลัมน์ E ตรงช่อง E2 เราพิมพ์

= D2*E2

จากนั้น เราไม่จำเป็นต้องเขียนสูตรที่ช่อง E3 เพื่อให้มันคำนวณ D3*E3 เลย เราแค่เอาเมาส์ไปวางตรงมุมขวาล่าง ให้เคอร์เซอร์เป็นเครื่องหมายบวก แล้วลากมาเลยจนถึงเซลล์สุดท้ายที่เราต้องการ Excel จะเติมเต็มส่วนที่เหลือให้เราเอง

Tips: หรือ พอเคอร์เซอร์เป็นเครื่องหมายบวก เราจะดับเบิลคลิกก็ได้เหมือนกัน แต่ต้องระวังความไม่ต่อเนื่อง เพราะมันจะไปสุดที่แพตเทิร์นหนึ่งเท่านั้น เช่น ถ้าหากว่า มีช่องว่าง ในเซลล์อ้างอิง มันจะนึกว่าหมดช่วงที่ต้องการแล้ว จะหยุดอยู่ตรงนั้น

Absolute References

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

=AVERAGE(A2:A18)/E2

แต่ทีนี้ ถ้า เราจะลากไปทางซ้าย แบบ Relative References เหมือนในตัวอย่างข้างบน เพื่อจะคำนวณของ คอลัมน์ B และ C ต่อไปด้วย มันจะกลายเป็นว่า E2 ก็จะเลื่อนเป็น F2 G2 ซึ่งผิดประเด็นที่ต้องการ เราต้องการแค่ E2 สำหรับการเอาไปหาร เท่านั้น

ดังนั้น เราต้องบังคับในสูตรว่า การอ้างอิงเฉพาะตำแหน่ง E2 นี้ จะต้องเป็นตำแหน่งเดิม ไม่เลื่อนตาม โดยการใส่เครื่องหมายดอลลาร์ ($) กำกับ จะพิมพ์ใส่ หรือจะกด F4 ก็ได้เหมือนกัน สูตรจะได้แบบนี้

=AVERAGE(A2:A18)/$E$2

 การใส่เครื่องหมาย $ เป็นการระบุตำแหน่งคงที่ หรือ Absolute References (การอ้างอิงแบบสัมบูรณ์) $E$2 จะตรึง E2 ไว้ คือ ตรึงคอลัมน์ E และตรึงแถว 2 ไว้ ไม่เลื่อนตำแหน่งไปที่อื่น

อ้างอิงจากเว็บของไมโครซอฟต์

นอกเหนือจากการอ้างอิงตำแหน่งแบบ A1 Notation แล้ว ยังมีอีกแบบหนึ่งเรียกว่า R1C1 Notation ซึ่งจะกล่าวถึงต่อไป