เรียนรู้วิธีการสร้างสูตรและใช้ฟังก์ชันที่มีอยู่แล้วภายในเพื่อทําการคํานวณและแก้ไขปัญหา
สำคัญ
ผลลัพธ์จากการคํานวณของสูตรและฟังก์ชันเวิร์กชีต 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
ค่าคงที่คือค่าที่ไม่ได้คํานวณและจะยังคงเหมือนเดิมเสมอ ตัวอย่างเช่น วันที่ 9/10/2551 ตัวเลข 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 บนเวิร์กชีตที่ตั้งชื่อว่า การตลาด ในเวิร์กบุ๊กเดียวกัน
- อ้างอิงไปยังเวิร์กชีตที่ชื่อว่า การตลาด
- อ้างอิงไปยังช่วงของเซลล์จาก B1 ถึง B10
- เครื่องหมายอัศเจรีย์ (!) จะคั่นการอ้างอิงเวิร์กชีตจากการอ้างอิงช่วงของเซลล์
หมายเหตุ
ถ้าเวิร์กชีตที่อ้างอิงมีช่องว่างหรือตัวเลขอยู่ภายใน ให้เพิ่มเครื่องหมายอัญประกาศเดี่ยว (') ก่อนและหลังชื่อเวิร์กชีต เช่น ='123'! A1 หรือ ='รายได้ประจําเดือนมกราคม'! A1.
ความแตกต่างระหว่างการอ้างอิงแบบสัมบูรณ์ แบบสัมพัทธ์ และการอ้างอิงแบบผสม
การอ้างอิงสัมพัทธ์
การอ้างอิงเซลล์แบบสัมพัทธ์ในสูตร เช่น 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 (เรียงตามประเภท)