การอ้างอิงเซลล์สัมพัทธ์และสัมบูรณ์และการจัดรูปแบบ

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

การนำทางของโรงเรียน

1. ทำไมคุณถึงต้องการสูตรและฟังก์ชั่น?
2. การกำหนดและสร้างสูตร
3. การอ้างอิงเซลล์สัมพัทธ์และสัมบูรณ์และการจัดรูปแบบ
4. ฟังก์ชั่นที่มีประโยชน์ที่คุณควรรู้จัก
5. การค้นหาแผนภูมิสถิติและตารางสาระสำคัญ

บันทึก: เราแค่คิดว่าคุณรู้อยู่แล้วว่าเซลล์เป็นหนึ่งในกำลังสองในสเปรดชีตจัดเรียงเป็นคอลัมน์และแถวซึ่งอ้างอิงโดยตัวอักษรและตัวเลขที่ทำงานในแนวนอนและแนวตั้ง.

การอ้างอิงเซลล์คืออะไร?

"การอ้างอิงเซลล์" หมายถึงเซลล์ที่เซลล์อื่นอ้างอิง ตัวอย่างเช่นถ้าในเซลล์ A1 คุณมี = A2 จากนั้น A1 หมายถึง A2.

ตรวจสอบสิ่งที่เราพูดในบทที่ 2 เกี่ยวกับแถวและคอลัมน์เพื่อให้เราสามารถสำรวจการอ้างอิงเซลล์เพิ่มเติม.

เซลล์ในสเปรดชีตถูกอ้างถึงโดยแถวและคอลัมน์ คอลัมน์เป็นแนวตั้งและติดป้ายกำกับด้วยตัวอักษร แถวเป็นแนวนอนและมีป้ายกำกับตัวเลข.

เซลล์แรกในสเปรดชีตคือ A1 ซึ่งหมายถึงคอลัมน์ A, แถวที่ 1, B3 หมายถึงเซลล์ที่อยู่ในคอลัมน์ที่สองแถวที่สามและอื่น ๆ.

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

ประเภทของการอ้างอิงเซลล์

การอ้างอิงเซลล์มีสามประเภท.

สัมบูรณ์ - นี่หมายถึงการอ้างอิงเซลล์ยังคงเหมือนเดิมถ้าคุณคัดลอกหรือย้ายเซลล์ไปยังเซลล์อื่น ๆ สิ่งนี้ทำได้โดยการยึดแถวและคอลัมน์ดังนั้นจึงไม่เปลี่ยนแปลงเมื่อคัดลอกหรือย้าย.

สัมพัทธ์ - การอ้างอิงสัมพัทธ์หมายถึงที่อยู่ของเซลล์เปลี่ยนไปเมื่อคุณคัดลอกหรือย้าย; เช่นการอ้างอิงเซลล์สัมพันธ์กับตำแหน่งของมัน.

แบบผสม - ซึ่งหมายความว่าคุณสามารถเลือกที่จะยึดทั้งแถวหรือคอลัมน์เมื่อคุณคัดลอกหรือย้ายเซลล์เพื่อให้การเปลี่ยนแปลงหนึ่งและอื่น ๆ ไม่ได้ ตัวอย่างเช่นคุณสามารถยึดการอ้างอิงแถวจากนั้นเลื่อนเซลล์ลงสองแถวและข้ามสี่คอลัมน์และการอ้างอิงแถวยังคงเหมือนเดิม เราจะอธิบายเพิ่มเติมด้านล่าง.

อ้างอิงญาติ

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

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

เมื่อคุณย้ายเซลล์ไปทางขวาหมายเลขคอลัมน์จะเพิ่มขึ้น เมื่อคุณเลื่อนลงหมายเลขแถวจะเพิ่มขึ้น เซลล์ที่ชี้ไปที่การอ้างอิงเซลล์เปลี่ยนแปลงเช่นกัน นี่คือภาพประกอบด้านล่าง:

ต่อจากตัวอย่างของเราและดูกราฟด้านล่างถ้าคุณคัดลอกเนื้อหาของเซลล์ A1 สองไปทางขวาและสี่ลงคุณย้ายมันไปที่เซลล์ C5.

เราคัดลอกเซลล์สองคอลัมน์ไปทางขวาและสี่ลง ซึ่งหมายความว่าเราได้เปลี่ยนเซลล์ที่อ้างถึงสองข้ามและสี่ลง A1 = A2 ตอนนี้คือ C5 = C6 แทนที่จะอ้างถึง A2 ตอนนี้เซลล์ C5 หมายถึงเซลล์ C6.

ค่าที่แสดงคือ 0 เนื่องจากเซลล์ C6 ว่างเปล่า ในเซลล์ C6 เราพิมพ์“ I am C6” และตอนนี้ C5 แสดง“ I am C6”

ตัวอย่าง: สูตรข้อความ

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

เขียนสูตร = RIGHT (A3, LEN (A3) - FIND (“,”, A3) - 1) หรือคัดลอกข้อความไปยังเซลล์ C3 อย่าคัดลอกเซลล์จริงเฉพาะข้อความคัดลอกข้อความมิฉะนั้นจะอัปเดตข้อมูลอ้างอิง.

คุณสามารถแก้ไขเนื้อหาของเซลล์ที่ด้านบนของสเปรดชีตในช่องถัดจากที่มีคำว่า "fx" ช่องนั้นยาวกว่าเซลล์ที่มีความกว้างดังนั้นจึงง่ายต่อการแก้ไข.

ตอนนี้เรามี:

ไม่มีอะไรซับซ้อนเราเพิ่งเขียนสูตรใหม่ลงในเซลล์ C3 ตอนนี้คัดลอก C3 ไปยังเซลล์ C2 และ C4 สังเกตผลลัพธ์ด้านล่าง:

ตอนนี้เรามีชื่อแรกของ Alexander Hamilton และ Thomas Jefferson.

ใช้เคอร์เซอร์เพื่อไฮไลต์เซลล์ C2, C3 และ C4 ชี้เคอร์เซอร์ไปที่เซลล์ B2 และวางเนื้อหา ดูสิ่งที่เกิดขึ้น - เราได้รับข้อผิดพลาด:“ #REF” เพราะเหตุนี้?

เมื่อเราคัดลอกเซลล์จากคอลัมน์ C ถึงคอลัมน์ B มันอัปเดตข้อมูลอ้างอิงหนึ่งคอลัมน์ทางซ้าย = RIGHT (A2, LEN (A2) - FIND (“,”, A2) - 1).

มันเปลี่ยนทุกการอ้างอิงถึง A2 เป็นคอลัมน์ทางด้านซ้ายของ A แต่ไม่มีคอลัมน์ทางด้านซ้ายของคอลัมน์ A ดังนั้นคอมพิวเตอร์ไม่ทราบว่าคุณหมายถึงอะไร.

ตัวอย่างสูตรใหม่ใน B2 คือ = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) และผลลัพธ์คือ #REF:

การคัดลอกสูตรไปยังช่วงของเซลล์

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

โดย "ช่วง" เราหมายถึงมากกว่าหนึ่งเซลล์ ตัวอย่างเช่น (C1: C10) หมายถึงเซลล์ทั้งหมดจากเซลล์ C1 ถึงเซลล์ C10 ดังนั้นจึงเป็นคอลัมน์ของเซลล์ อีกตัวอย่างหนึ่ง (A1: AZ1) คือแถวบนสุดจากคอลัมน์ A ถึงคอลัมน์ AZ.

หากช่วงข้ามห้าคอลัมน์และสิบแถวคุณจะระบุช่วงโดยการเขียนเซลล์มุมบนซ้ายและขวาล่างหนึ่งเช่น A1: E10 นี่คือพื้นที่สี่เหลี่ยมจัตุรัสที่ข้ามแถวและคอลัมน์และไม่ใช่แค่ส่วนหนึ่งของคอลัมน์หรือบางส่วนของแถว.

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

ตอนนี้คัดลอกสูตรในเซลล์ C3 (= B3 + C2) ไปยังส่วนที่เหลือของคอลัมน์เพื่อให้ยอดคงเหลือสะสมสำหรับงบประมาณของเรา Excel จะอัปเดตการอ้างอิงเซลล์เมื่อคุณคัดลอก ผลลัพธ์ที่แสดงด้านล่าง:

อย่างที่คุณเห็นแต่ละเซลล์ใหม่อัพเดท ญาติ ไปยังตำแหน่งใหม่ดังนั้นเซลล์ C4 จะอัปเดตสูตรเป็น = B4 + C3:

Cell C5 อัปเดตเป็น = B5 + C4 และอื่น ๆ :

การอ้างอิงแบบสัมบูรณ์

การอ้างอิงแบบสัมบูรณ์ไม่เปลี่ยนแปลงเมื่อคุณย้ายหรือคัดลอกเซลล์ เราใช้เครื่องหมาย $ ในการอ้างอิงแบบสัมบูรณ์ - โปรดระลึกไว้เสมอว่าคิดว่าเครื่องหมายดอลลาร์เป็นจุดยึด.

ตัวอย่างเช่นป้อนสูตร = $ A $ 1 ในเซลล์ใดก็ได้ $ ที่ด้านหน้าของคอลัมน์ A หมายถึงอย่าเปลี่ยนคอลัมน์, $ ที่ด้านหน้าของแถว 1 หมายถึงอย่าเปลี่ยนคอลัมน์เมื่อคุณคัดลอกหรือย้ายเซลล์ไปยังเซลล์อื่น ๆ.

ดังที่คุณเห็นในตัวอย่างด้านล่างในเซลล์ B1 เรามีการอ้างอิงสัมพัทธ์ = A1 เมื่อเราคัดลอก B1 ไปที่สี่เซลล์ด้านล่างการอ้างอิงสัมพัทธ์ = A1 เปลี่ยนเป็นเซลล์ทางซ้ายดังนั้น B2 กลายเป็น A2, B3 กลายเป็น A3 ฯลฯ เซลล์เหล่านั้นเห็นได้ชัดว่าไม่มีค่าที่ป้อนเข้าดังนั้นเอาต์พุตจึงเป็นศูนย์.

อย่างไรก็ตามหากเราใช้ = $ A1 $ 1 เช่นใน C1 และเราคัดลอกไปยังสี่เซลล์ด้านล่างการอ้างอิงนั้นเป็นแบบสัมบูรณ์ดังนั้นมันจึงไม่เปลี่ยนแปลงและเอาต์พุตจะเท่ากับค่าในเซลล์ A1 เสมอ.

สมมติว่าคุณกำลังติดตามความสนใจของคุณเช่นในตัวอย่างด้านล่าง สูตรใน C4 = B4 * B1 คือ“ อัตราดอกเบี้ย” *“ ยอดคงเหลือ” =“ ดอกเบี้ยต่อปี”

ตอนนี้คุณได้เปลี่ยนงบประมาณของคุณและได้บันทึกอีก $ 2,000 เพื่อซื้อกองทุนรวม สมมติว่าเป็นกองทุนอัตราคงที่และจ่ายดอกเบี้ยในอัตราเดียวกัน ป้อนบัญชีใหม่และยอดคงเหลือลงในสเปรดชีตแล้วคัดลอกสูตร = B4 * B1 จากเซลล์ C4 ไปยังเซลล์ C5.

งบประมาณใหม่มีลักษณะดังนี้:

กองทุนรวมใหม่จะได้รับดอกเบี้ย 0 ดอลลาร์ต่อปีซึ่งไม่ถูกต้องเนื่องจากอัตราดอกเบี้ยชัดเจน 5 เปอร์เซ็นต์.

Excel เน้นเซลล์ที่สูตรอ้างอิง คุณสามารถเห็นด้านบนว่าการอ้างอิงไปยังอัตราดอกเบี้ย (B1) ถูกย้ายไปที่เซลล์ว่าง B2 เราควรทำการอ้างอิงถึง B1 อย่างสมบูรณ์โดยการเขียน $ B $ 1 โดยใช้เครื่องหมายดอลลาร์เพื่อยึดการอ้างอิงแถวและคอลัมน์.

เขียนการคำนวณแรกใน C4 เพื่ออ่าน = B4 * $ B $ 1 ตามที่แสดงด้านล่าง:

จากนั้นคัดลอกสูตรนั้นจาก C4 ถึง C5 สเปรดชีตตอนนี้มีลักษณะดังนี้:

เนื่องจากเราคัดลอกสูตรหนึ่งเซลล์ลงดังนั้นเพิ่มแถวทีละสูตรใหม่คือ = B5 * $ B $ 1 คำนวณอัตราดอกเบี้ยของกองทุนรวมอย่างถูกต้องในขณะนี้เนื่องจากอัตราดอกเบี้ยถูกยึดไว้กับเซลล์ B1.

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

ชื่อสามารถอ้างถึงหนึ่งเซลล์หรือช่วงและคุณสามารถใช้ชื่อในสูตรตัวอย่างเช่น = interest_rate * 8 เป็นสิ่งเดียวกับการเขียน = $ B $ 1 * 8.

อ้างอิงผสม

การอ้างอิงแบบผสมคือเมื่อใด ทั้ง แถว หรือ คอลัมน์ถูกยึด.

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

ป้อนราคาต่อเอเคอร์เป็น = $ B2 * C2 ในเซลล์ D2 คุณกำลังบอกว่าคุณต้องการที่จะยึดราคาต่อคอลัมน์ปอนด์ จากนั้นคัดลอกสูตรนั้นไปยังแถวอื่น ๆ ในคอลัมน์เดียวกัน:

ตอนนี้คุณต้องการทราบมูลค่าของสินค้าคงคลังของเมล็ด คุณต้องการราคาต่อปอนด์และจำนวนปอนด์ในสินค้าคงคลังเพื่อทราบมูลค่าของสินค้าคงคลัง.

เราเพิ่มสองคอลัมน์:“ ปอนด์ของเมล็ดพันธุ์ในสินค้าคงคลัง” และจากนั้น“ มูลค่าของสินค้าคงคลัง” ตอนนี้คัดลอกเซลล์ D2 ไปยัง F4 และสังเกตว่าการอ้างอิงแถวในส่วนแรกของสูตรดั้งเดิม ($ B2) ได้รับการอัปเดตเป็นแถว 4 แต่คอลัมน์ยังคงอยู่เนื่องจาก $ anchors เป็น“ B. ”

นี่คือการอ้างอิงแบบผสมเนื่องจากคอลัมน์เป็นแบบสัมบูรณ์และแถวนั้นสัมพันธ์กัน.

การอ้างอิงแบบวงกลม

การอ้างอิงแบบวงกลมคือเมื่อสูตรอ้างถึงตัวมันเอง.

ตัวอย่างเช่นคุณไม่สามารถเขียน c3 = c3 + 1 การคำนวณแบบนี้เรียกว่า“ การวนซ้ำ” หมายถึงการทำซ้ำตัวเอง Excel ไม่รองรับการทำซ้ำเพราะคำนวณทุกอย่างเพียงครั้งเดียว.

หากคุณลองทำโดยพิมพ์ SUM (B1: B5) ในเซลล์ B5:

หน้าจอคำเตือนปรากฏขึ้น:

Excel จะบอกคุณว่าคุณมีการอ้างอิงแบบวงกลมที่ด้านล่างของหน้าจอดังนั้นคุณอาจไม่สังเกตเห็น หากคุณมีการอ้างอิงแบบวงกลมและปิดสเปรดชีตแล้วเปิดใหม่อีกครั้ง Excel จะแจ้งให้คุณทราบในหน้าต่างป๊อปอัปที่คุณมีการอ้างอิงแบบวงกลม.

หากคุณมีการอ้างอิงแบบวงกลมทุกครั้งที่คุณเปิดสเปรดชีต Excel จะแจ้งให้คุณทราบด้วยหน้าต่างป๊อปอัพที่คุณมีการอ้างอิงแบบวงกลม.

อ้างอิงถึงแผ่นงานอื่น ๆ

“ สมุดงาน” คือชุดของ“ แผ่นงาน” เพียงแค่ใส่หมายความว่าคุณสามารถมีหลายสเปรดชีต (แผ่นงาน) ในไฟล์ Excel เดียวกัน (สมุดงาน) ดังที่คุณเห็นในตัวอย่างด้านล่างเวิร์กบุ๊กตัวอย่างของเรามีแผ่นงานมากมาย (สีแดง).

แผ่นงานโดยค่าเริ่มต้นจะมีชื่อว่า Sheet1, Sheet2 และอื่น ๆ คุณสร้างขึ้นใหม่โดยคลิกที่“ +” ที่ด้านล่างของหน้าจอ Excel.

คุณสามารถเปลี่ยนชื่อเวิร์กชีทเป็นสิ่งที่มีประโยชน์เช่น“ เงินกู้” หรือ“ งบประมาณ” โดยคลิกขวาที่แท็บแผ่นงานที่แสดงที่ด้านล่างของหน้าจอโปรแกรม Excel เลือกเปลี่ยนชื่อและพิมพ์ชื่อใหม่.

หรือคุณสามารถดับเบิลคลิกที่แท็บและเปลี่ยนชื่อได้.

ไวยากรณ์สำหรับการอ้างอิงแผ่นงานคือเซลล์ = แผ่นงาน! คุณสามารถใช้การอ้างอิงชนิดนี้เมื่อมีการใช้ค่าเดียวกันในสองแผ่นตัวอย่างอาจเป็น:

• วันนี้วันที่
• อัตราการแปลงสกุลเงินจากดอลลาร์เป็นยูโร
• ทุกสิ่งที่เกี่ยวข้องกับแผ่นงานทั้งหมดในสมุดงาน

ด้านล่างนี้เป็นตัวอย่างของแผ่นงาน“ ดอกเบี้ย” ที่อ้างอิงถึงแผ่นงาน“ เงินกู้” เซลล์ B1.

หากเราดูแผ่นงาน“ เงินกู้” เราสามารถดูการอ้างอิงกับจำนวนเงินกู้:

ถัดไป ...

เราหวังว่าคุณจะเข้าใจการอ้างอิงเซลล์อย่างแน่นหนารวมถึงความสัมพันธ์แบบสัมบูรณ์และแบบผสม มีจำนวนมากอย่างแน่นอน.

สำหรับบทเรียนวันนี้ในบทที่ 4 เราจะพูดถึงฟังก์ชั่นที่มีประโยชน์ที่คุณอาจต้องการทราบสำหรับการใช้ Excel ทุกวัน.