เรียนรู้วิธีการสร้างสูตรและใช้ฟังก์ชันที่มีอยู่แล้วภายในเพื่อทําการคํานวณและแก้ไขปัญหา
สำคัญ
ผลลัพธ์จากการคํานวณของสูตรและฟังก์ชันเวิร์กชีต Excel จํานวนหนึ่งอาจแตกต่างกันเล็กน้อยระหว่างพีซีที่ใช้ Windows ที่ใช้สถาปัตยกรรม x86 หรือ x86-64 กับพีซีที่ใช้ Windows RT ที่ใช้สถาปัตยกรรม ARM เรียนรู้เพิ่มเติมเกี่ยวกับความแตกต่าง
สำคัญ
บทความนี้อธิบายถึง XLOOKUP และ VLOOKUP ที่คล้ายคลึงกัน ลองใช้ฟังก์ชัน XLOOKUP ใหม่ ซึ่งเป็นเวอร์ชันที่ได้รับการปรับปรุงของ VLOOKUP ที่ทํางานในทุกทิศทางและส่งกลับรายการที่ตรงกันตามค่าเริ่มต้น ใช้งานง่ายและสะดวกกว่ารุ่นก่อน
สร้างสูตรที่อ้างอิงถึงค่าในเซลล์อื่น
เลือกเซลล์
พิมพ์เครื่องหมายเท่ากับ (=)
หมายเหตุ
สูตรใน Excel จะขึ้นต้นด้วยเครื่องหมายเท่ากับ
เลือกเซลล์หรือพิมพ์ตำแหน่งเซลล์ในเซลล์ที่เลือก
ใส่ตัวดําเนินการ ตัวอย่างเช่น ใช้สําหรับ
-ลบเลือกเซลล์ถัดไปหรือพิมพ์ตำแหน่งเซลล์ในเซลล์ที่เลือก
กด Enter ผลลัพธ์ของการคํานวณจะปรากฏในเซลล์ที่มีสูตร
ดูสูตร
เมื่อคุณใส่สูตรลงในเซลล์ สูตรนั้นจะปรากฏใน แถบสูตรด้วย
เมื่อต้องการดูสูตรในแถบสูตร ให้เลือกเซลล์
ใส่สูตรที่มีฟังก์ชันในตัว
เลือกเซลล์ว่าง
พิมพ์เครื่องหมาย
=เท่ากับ แล้วพิมพ์ฟังก์ชัน ตัวอย่างเช่น พิมพ์=SUMเพื่อดูยอดขายรวมพิมพ์วงเล็บ
(เปิดเลือกช่วงของเซลล์ แล้วพิมพ์วงเล็บปิด
)
กด Enter เพื่อดูผลลัพธ์
ดาวน์โหลดเวิร์กบุ๊กบทช่วยสอนสูตร
ดาวน์โหลดเวิร์กบุ๊ก เริ่มต้นใช้งานสูตร ถ้าคุณยังใหม่กับ Excel หรือแม้ว่าคุณจะมีประสบการณ์ใช้งาน Excel อยู่บ้าง คุณสามารถทําตามคําแนะนําสูตรทั่วไปของ Excel ได้ในคําแนะนํานี้ ด้วยตัวอย่างในโลกแห่งความเป็นจริงและภาพที่เป็นประโยชน์ คุณจะสามารถใช้ SUM, COUNT, AVERAGE และ VLOOKUP ได้เหมือนมืออาชีพ
เจาะลึกสูตร
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับองค์ประกอบสูตรที่เฉพาะเจาะจง ให้เรียกดูแต่ละส่วนในรายการต่อไปนี้
ส่วนต่างๆ ของสูตร Excel
สูตรอาจประกอบด้วยองค์ประกอบใดองค์ประกอบหนึ่งหรือทั้งหมด ได้แก่ ฟังก์ชัน การอ้างอิง ตัวดําเนินการ และค่าคงที่
- ฟังก์ชัน เช่น ฟังก์ชัน PI() ส่งกลับค่าของ Pi คือ 3.142...
- การอ้างอิง เช่น A2 ส่งกลับค่าในเซลล์ A2
- ค่าคงที่ คือ ค่าของจํานวนหรือค่าของข้อความที่คุณใส่ลงในสูตรโดยตรง เช่น 2
- ตัวดําเนินการ เช่น ตัวดําเนินการ ^ (แคเรท) ทําหน้าที่ยกกําลังตัวเลข และตัวดําเนินการ * (เครื่องหมายดอกจัน) ทําหน้าที่คูณตัวเลข
การใช้ค่าคงที่ในสูตร Excel
ค่าคงที่เป็นค่าที่ไม่ได้คํานวณและคงที่อยู่เสมอ ตัวอย่างเช่น วันที่ 10/9/2008, เลข 210 และข้อความ "รายได้ประจําไตรมาส" ทั้งหมดนี้เป็นค่าคงที่ นิพจน์หรือค่าที่เป็นผลลัพธ์จากนิพจน์ไม่ใช่ค่าคงที่ ถ้าคุณใช้ค่าคงที่ในสูตรแทนการอ้างอิงเซลล์ (เช่น =30+70+110) ผลลัพธ์จะเปลี่ยนแปลงเมื่อคุณแก้ไขสูตรเท่านั้น โดยทั่วไปแล้ว ที่ที่ดีที่สุดที่จะใส่ค่าคงที่คือในเซลล์แต่ละเซลล์ ซึ่งคุณสามารถเปลี่ยนได้อย่างง่ายๆ ถ้าจําเป็น แล้วอ้างอิงเซลล์เหล่านั้นลงในสูตร
การใช้การอ้างอิงในสูตร Excel
การอ้างอิงจะระบุเซลล์หรือช่วงของเซลล์บนเวิร์กชีต และบอก Excel ว่าจะหาค่าหรือข้อมูลที่คุณต้องการใช้ในสูตรได้จากที่ใด ใช้การอ้างอิงเพื่อรวมข้อมูลจากส่วนต่างๆ ของเวิร์กชีตในสูตรเดียว หรือเพื่อใช้ค่าจากเซลล์เดียวในหลายๆ สูตร คุณยังสามารถอ้างอิงไปยังเซลล์ในเวิร์กชีตอื่น ในเวิร์กบุ๊กเดียวกัน และไปยังเวิร์กบุ๊กอื่นได้อีกด้วย การอ้างอิงไปยังเซลล์ในเวิร์กบุ๊กอื่นจะเรียกว่าลิงก์หรือการอ้างอิงภายนอก
สไตล์การอ้างอิง A1
ตามค่าเริ่มต้น Excel จะใช้สไตล์การอ้างอิง A1 ซึ่งอ้างอิงถึงคอลัมน์ที่มีตัวอักษร (A ถึง XFD สําหรับคอลัมน์ทั้งหมด 16,384 คอลัมน์) และอ้างอิงแถวด้วยตัวเลข (1 ถึง 1,048,576) ตัวอักษรและตัวเลขเหล่านี้เรียกว่าหัวแถวและหัวคอลัมน์ เมื่อต้องการอ้างอิงเซลล์ ให้ใส่อักษรคอลัมน์ตามด้วยหมายเลขแถว ตัวอย่างเช่น B2 อ้างอิงไปยังเซลล์ที่จุดตัดของคอลัมน์ B และแถว 2
| เมื่อต้องการอ้างอิง | ใช้ |
|---|---|
| เซลล์ในคอลัมน์ A และแถวที่ 10 | A10 |
| ช่วงเซลล์ในคอลัมน์ A และแถวที่ 10 ถึง 20 | A10:A20 |
| ช่วงเซลล์ในแถวที่ 15 และคอลัมน์ B ถึง E | B15:E15 |
| เซลล์ทั้งหมดในแถวที่ 5 | 5:5 |
| เซลล์ทั้งหมดในแถวที่ 5 ถึง 10 | 5:10 |
| เซลล์ทั้งหมดในคอลัมน์ H | H:H |
| เซลล์ทั้งหมดในคอลัมน์ H ถึง J | H:J |
| ช่วงเซลล์ในคอลัมน์ A ถึง E และแถวที่ 10 ถึง 20 | A10:E20 |
การทำการอ้างอิงไปยังเซลล์หรือช่วงของเซลล์บนเวิร์กชีตอื่นในเวิร์กบุ๊กเดียวกัน
ในตัวอย่างต่อไปนี้ ฟังก์ชัน AVERAGE จะคำนวณค่าเฉลี่ยสำหรับช่วง B1:B10 บนเวิร์กชีตที่ตั้งชื่อว่า การตลาด ในเวิร์กบุ๊กเดียวกัน
- อ้างอิงไปยังเวิร์กชีตชื่อ Marketing
- อ้างอิงไปยังช่วงของเซลล์จาก B1 ถึง B10
- เครื่องหมายอัศเจรีย์ (!) จะแยกการอ้างอิงเวิร์กชีตจากการอ้างอิงช่วงของเซลล์
หมายเหตุ
ถ้าเวิร์กชีตที่อ้างอิงมีช่องว่างหรือตัวเลขอยู่ในชื่อ ให้เพิ่มเครื่องหมายอัญประกาศเดี่ยวก่อนและหลังชื่อเวิร์กชีต เช่น ='123'! A1 หรือ ='January Revenue'! ก 1.
ความแตกต่างระหว่างการอ้างอิงแบบสัมบูรณ์ การอ้างอิงแบบสัมพัทธ์ และแบบผสม
การอ้างอิงสัมพัทธ์
การอ้างอิงเซลล์สัมพัทธ์ในสูตร เช่น A1 จะยึดตามตําแหน่งสัมพัทธ์ของเซลล์ที่มีสูตรและเซลล์ที่มีการอ้างอิงถึง ถ้าตําแหน่งของเซลล์ที่มีสูตรเปลี่ยนแปลง การอ้างอิงก็จะเปลี่ยนไป ถ้าคุณคัดลอกหรือเติมสูตรตามแถวหรือตามคอลัมน์ จะมีการปรับการอ้างอิงโดยอัตโนมัติ ตามค่าเริ่มต้น สูตรใหม่จะใช้การอ้างอิงแบบสัมพัทธ์ ตัวอย่างเช่น ถ้าคุณคัดลอกหรือเติมการอ้างอิงแบบสัมพัทธ์ในเซลล์ B2 ไปยังเซลล์ B3 การอ้างอิงจะปรับจาก =A1 เป็น =A2 โดยอัตโนมัติ
การอ้างอิงแบบสัมบูรณ์
การอ้างอิงเซลล์แบบสัมบูรณ์ในสูตร เช่น $A$1 จะอ้างอิงไปยังเซลล์ในตําแหน่งที่เฉพาะเจาะจงเสมอ ถ้าตําแหน่งของเซลล์ที่มีสูตรเปลี่ยนแปลง การอ้างอิงแบบสัมบูรณ์จะยังคงเหมือนเดิม ถ้าคุณคัดลอกหรือเติมสูตรตามแถวหรือตามคอลัมน์ จะไม่มีการปรับการอ้างอิงแบบสัมบูรณ์ ตามค่าเริ่มต้น สูตรใหม่จะใช้การอ้างอิงแบบสัมพัทธ์ คุณจึงอาจจําเป็นต้องเปลี่ยนการอ้างอิงเหล่านั้นให้เป็นการอ้างอิงแบบสัมบูรณ์ ตัวอย่างเช่น ถ้าคุณคัดลอกหรือเติมการอ้างอิงแบบสัมบูรณ์ในเซลล์ B2 ไปยังเซลล์ B3 การอ้างอิงจะเหมือนกันทั้งในสองเซลล์คือ =$A$1
การอ้างอิงแบบผสม
การอ้างอิงแบบผสมมีทั้งคอลัมน์แบบสัมบูรณ์และแถวแบบสัมพัทธ์ หรือแถวแบบสัมบูรณ์และคอลัมน์แบบสัมพัทธ์ การอ้างอิงคอลัมน์แบบสัมบูรณ์จะอยู่ในรูปแบบ $A 1, $B 1 และอื่นๆ การอ้างอิงแถวสัมบูรณ์จะอยู่ในรูปแบบ A$1, B$1 และอื่นๆ ถ้าตําแหน่งของเซลล์ที่มีสูตรเปลี่ยนแปลง การอ้างอิงสัมพัทธ์จะเปลี่ยน และการอ้างอิงแบบสัมบูรณ์จะไม่เปลี่ยนแปลง ถ้าคุณคัดลอกหรือเติมสูตรตามแถวหรือตามคอลัมน์ การอ้างอิงแบบสัมพัทธ์จะถูกปรับเปลี่ยนโดยอัตโนมัติ และการอ้างอิงแบบสัมบูรณ์จะไม่ปรับ ตัวอย่างเช่น ถ้าคุณคัดลอกหรือเติมการอ้างอิงแบบผสมจากเซลล์ A2 ถึง B3 การอ้างอิงจะปรับจาก =A$1 เป็น =B$1
สไตล์การอ้างอิงสามมิติ
การอ้างอิงไปยังเวิร์กชีตหลายแผ่นได้โดยสะดวก
ถ้าคุณต้องการวิเคราะห์ข้อมูลในเซลล์หรือช่วงของเซลล์ที่อยู่ตําแหน่งเดียวกันบนเวิร์กชีตหลายๆ แผ่นภายในเวิร์กบุ๊กหนึ่ง ให้ใช้การอ้างอิงสามมิติ การอ้างอิงสามมิติประกอบด้วยการอ้างอิงเซลล์หรือช่วงเซลล์ที่นําหน้าด้วยช่วงของชื่อเวิร์กชีต Excel จะใช้เวิร์กชีตใดก็ตามที่มีการจัดเก็บอยู่ระหว่างชื่อแรกและชื่อสุดท้ายของการอ้างอิง ตัวอย่างเช่น =SUM(Sheet2:Sheet13! B5) บวกทุกค่าในเซลล์ B5 บนทุกเวิร์กชีตตั้งแต่ Sheet 2 ถึง Sheet 13
- คุณสามารถใช้การอ้างอิงสามมิติเพื่ออ้างอิงไปยังเซลล์บนเวิร์กชีตอื่น กำหนดชื่อ และสร้างสูตรโดยใช้ฟังก์ชันต่อไปนี้ ได้แก่ SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA และ VARPA
- คุณไม่สามารถใช้การอ้างอิงสามมิติในสูตรอาร์เรย์ได้
- คุณไม่สามารถใช้การอ้างอิงสามมิติกับตัวดําเนินการจุดตัด (ช่องว่างเดียว) หรือในสูตรที่ใช้จุดตัดโดยนัย
สิ่งที่เกิดขึ้นเมื่อคุณย้าย คัดลอก แทรก หรือลบเวิร์กชีต
ตัวอย่างต่อไปนี้อธิบายสิ่งที่เกิดขึ้นเมื่อคุณย้าย คัดลอก แทรก หรือลบเวิร์กชีตที่อยู่ในการอ้างอิงสามมิติ ตัวอย่างนี้ใช้สูตร =SUM(Sheet2:Sheet6! A2:A5) เพื่อบวกเซลล์ A2 ถึง A5 บนเวิร์กชีตที่ 2 ถึง 6
- แทรกหรือคัดลอก ถ้าคุณแทรกหรือคัดลอกแผ่นงานระหว่าง Sheet2 ถึง Sheet6 (จุดสิ้นสุดในตัวอย่างนี้) Excel จะรวมค่าทั้งหมดในเซลล์ A2 ถึง A5 จากแผ่นงานที่เพิ่มเข้ามาไว้ในการคำนวณด้วย
- ลบ ถ้าคุณลบแผ่นงานที่อยู่ระหว่าง Sheet2 ถึง Sheet6 ออก Excel จะเอาค่าจากแผ่นงานเหล่านั้นออกจากการคํานวณ
- ย้าย ถ้าคุณย้ายแผ่นงานที่อยู่ระหว่าง Sheet2 ถึง Sheet6 ไปยังตำแหน่งที่ตั้งซึ่งอยู่นอกช่วงแผ่นงานที่อ้างอิงถึง Excel จะเอาค่าจากแผ่นงานเหล่านั้นออกจากการคำนวณ
- ย้ายจุดสิ้นสุด ถ้าคุณย้าย Sheet2 หรือ Sheet6 ไปที่ตำแหน่งที่ตั้งอื่นภายในเวิร์กบุ๊กเดียวกัน Excel จะปรับการคำนวณให้เข้ากับช่วงใหม่ของแผ่นงานที่อยู่ระหว่างแผ่นงานทั้งสอง
- ลบจุดสิ้นสุด ถ้าคุณลบ Sheet2 หรือ Sheet6 Excel จะปรับการคำนวณให้เข้ากับช่วงของแผ่นงานที่อยู่ระหว่างแผ่นงานทั้งสอง
สไตล์การอ้างอิง R1C1
คุณยังสามารถใช้สไตล์การอ้างอิงที่มีการใส่หมายเลขให้กับแถวและคอลัมน์บนเวิร์กชีตได้เช่นกัน สไตล์การอ้างอิง R1C1 มีประโยชน์ในการคํานวณตําแหน่งแถวและคอลัมน์ในแมโคร ในสไตล์แบบ R1C1 Excel จะระบุตําแหน่งที่ตั้งของเซลล์ด้วย "R" ตามด้วยหมายเลขแถวและ "C" ตามด้วยหมายเลขคอลัมน์
| อ้างอิง | ความหมาย |
|---|---|
| R[-2]C | การอ้างอิงสัมพัทธ์ไปยังเซลล์ที่อยู่เหนือขึ้นไปสองแถวและภายในคอลัมน์เดียวกัน |
| R[2]C[2] | การอ้างอิงสัมพัทธ์ไปที่เซลล์สองแถวลงมาและสองคอลัมน์ทางขวา |
| R2C2 | การอ้างอิงสัมบูรณ์ไปยังเซลล์ที่อยู่ในแถวที่สองและในคอลัมน์ที่สอง |
| R[-1] | การอ้างอิงสัมพัทธ์ไปที่ทั้งแถวเหนือเซลล์ที่ใช้งานอยู่ |
| R | การอ้างอิงสัมบูรณ์ไปที่แถวปัจจุบัน |
เมื่อคุณบันทึกแมโคร Excel จะบันทึกคําสั่งบางคําสั่งโดยใช้สไตล์การอ้างอิงแบบ R1C1 ตัวอย่างเช่น ถ้าคุณบันทึกคําสั่ง เช่น เลือกปุ่ม ผลรวมอัตโนมัติ เพื่อแทรกสูตรที่หาผลรวมช่วงเซลล์ Excel ก็จะบันทึกสูตรโดยใช้สไตล์การอ้างอิงแบบ R1C1 ไม่ใช่แบบ A1
คุณสามารถเปิดหรือปิดลักษณะการอ้างอิงเซลล์แบบ R1C1 ได้ด้วยการตั้งค่าหรือล้างกล่องกาเครื่องหมายลักษณะการอ้างอิงแบบ R1C1 ใต้ส่วน การทํางานกับสูตร ในประเภท สูตร ของกล่องโต้ตอบ ตัวเลือก เมื่อต้องการแสดงกล่องโต้ตอบนี้ ให้เลือกแท็บ ไฟล์
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ
ดูเพิ่มเติม
- สลับระหว่างการอ้างอิงแบบสัมพัทธ์ การอ้างอิงแบบสัมบูรณ์ และการอ้างอิงแบบผสมสําหรับฟังก์ชัน
- การใช้ตัวดำเนินการการคำนวณในสูตร Excel
- ลำดับการดำเนินการของ Excel ในสูตร
- การใช้ฟังก์ชันและฟังก์ชันที่ซ้อนกันในสูตร Excel
- กำหนดและใช้ชื่อในสูตร
- แนวทางและตัวอย่างของสูตรอาร์เรย์
- ลบหรือนำสูตรออก
- วิธีการหลีกเลี่ยงสูตรที่ใช้งานไม่ได้
- ค้นหาและแก้ไขข้อผิดพลาดในสูตร
- แป้นพิมพ์ลัดและแป้นฟังก์ชัน Excel
- ฟังก์ชันของ Excel (เรียงตามประเภท)