คนส่วนใหญ่คุ้นเคยกับการใช้การอ้างอิงแบบสัมพัทธ์ในExcel ^{(in Excel)}เนื่องจากการอ้างอิงเซลล์ใน สเปรดชีต Excelมีค่าเริ่มต้นเป็นวิธีการอ้างอิงแบบสัมพัทธ์

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

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

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

การ อ้างอิง^(References)แบบสัมพัทธ์ทำงานอย่างไรในExcel

เมื่อคุณป้อนค่าลงใน สเปรดชีต Excelแต่ละเซลล์จะมีตัวอักษรและตัวเลขเฉพาะที่กำหนดให้กับเซลล์นั้น นี่แสดงถึงคอลัมน์และแถวของเซลล์นั้น

ตัวอย่างเช่น ค่าของ “1” ในสเปรดชีตด้านล่างจะอยู่ในคอลัมน์ A และแถวที่ 2 ดังนั้น “การอ้างอิง” ไปยังเซลล์นี้คือA2

ถ้าคุณต้องการคำนวณในเซลล์ถัดไปตามเซลล์นี้ โดยการเพิ่ม 1 คุณจะต้องเขียนสูตรต่อไปนี้:

=A2+1

สูตรนี้จะแทรกค่าจาก A2 ลงในสูตร คำนวณแล้วส่งออกผลลัพธ์ไปยังเซลล์ที่มีสูตรนี้

เมื่อคุณกดEnterคุณจะเห็นผลลัพธ์

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

ในเซลล์ถัดไป การอ้างอิงถึง A2 จะกลายเป็น A3 ในเซลล์ด้านล่างนั้น A3 จะกลายเป็น A4 กล่าวอีกนัยหนึ่งExcelรู้ว่าคุณต้องการเพิ่ม 1 ในเซลล์ก่อนหน้า ดังนั้นExcelจะอัปเดตตัวเลข (การอ้างอิงแถว) ตามที่คุณลากลง

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

คอลัมน์ทางด้านขวาประกอบด้วย B2 ทางด้านขวาของคอลัมน์ที่มี C2 เป็นต้น

นี่เป็นตัวอย่างง่ายๆ ว่าการกำหนดแอดเดรสแบบสัมพัทธ์ทำงานอย่างไรสำหรับการอ้างอิงคอลัมน์และเซลล์

การ อ้างอิง^(References)แบบสัมบูรณ์ทำงานอย่างไรในExcel

^(Absolute)การอ้างอิง แบบ สัมบูรณ์ ใน Excelช่วยให้คุณสามารถอ้างอิงเซลล์เดียวกันได้ แทนที่จะให้Excel อัปเดต^{(Excel automatically update)}การอ้างอิงแถวหรือคอลัมน์ให้กับคุณโดยอัตโนมัติ การอ้างอิง "ผสม" คือถ้าคุณล็อกเฉพาะแถวหรือคอลัมน์ และ "การอ้างอิงแบบสัมบูรณ์" คือเมื่อคุณล็อกทั้งสองอย่าง

มาดูตัวอย่างกัน

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

เมื่อต้องการทำเช่นนี้ คุณจะต้องป้อนสูตรที่มีลักษณะดังนี้:

=B$2*A3

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

คุณจะสังเกตได้ว่าวิธีนี้ใช้ได้เพราะคุณลากลงในเซลล์ในคอลัมน์เดียวกันเท่านั้น ดังนั้นคุณจึงไม่ต้องล็อกคอลัมน์ (B) โดยวางเครื่องหมายดอลลาร์ ($) ไว้ข้างหน้า

ปัญหาคือถ้าคุณต้องการใช้สูตรเดียวกันทางด้านขวาของสูตรดั้งเดิม การอ้างอิง "B" จะเปลี่ยนไป และสูตรจะไม่อ้างอิง B2 ตามที่ตั้งใจไว้อีกต่อไป

มาดูวิธีการใช้การอ้างอิงแบบสัมบูรณ์แทนการอ้างอิงแบบผสมเพื่อให้การกรอกทั้งสองทิศทางทำงานได้อย่างถูกต้อง

การใช้การอ้างอิง^(References)แบบสัมบูรณ์^(Absolute) ในExcel . อย่างถูกต้อง^(Excel)

หากต้องการใช้การอ้างอิงที่ถูกต้องในสูตรนี้ คุณต้องพิจารณาให้แน่ชัดว่าคุณกำลังพยายามทำอะไรอยู่

ในกรณีนี้ เราต้องการลักษณะการทำงานต่อไปนี้เมื่อกรอกทางด้านขวา

• อ้างอิงค่าในเซลล์ B2 . เสมอ
• อ้างอิงค่าในคอลัมน์ A . เสมอ
• เลื่อนการอ้างอิงของแถวสำหรับคอลัมน์ A ไปยังแถวปัจจุบันของสูตร

เมื่อพิจารณาจากพฤติกรรมเหล่านี้แล้ว คุณก็รู้แล้วว่าคุณต้อง “ล็อก” อะไร และคุณไม่ต้องทำอะไร ต้องล็อกทั้ง "B" และ "2" (ไม่เปลี่ยนแปลง) ต้องล็อกคอลัมน์ A ด้วย

ดังนั้นสูตรของคุณใน B3 ต้องมีลักษณะดังนี้: =$B$2*$A3

ตอนนี้ เมื่อคุณลากเซลล์เดียวกันนี้ลงหรือขึ้น สูตรจะทำงานตามที่ตั้งใจไว้

การใช้การอ้างอิงแบบสัมบูรณ์อย่างถูกต้องอาจทำได้ยาก ดังนั้นจึงต้องใช้เวลาในการพิจารณาอย่างรอบคอบว่าคุณต้องการให้ Excel อัปเดตสูตรอย่างไรในขณะที่คุณเติมคอลัมน์หรือแถว^{(columns or rows)}ในทิศทางใดทิศทางหนึ่ง

ขี่จักรยานผ่านประเภทอ้างอิง^{(Reference Types)}ในExcel

คุณสามารถเร่งความเร็วในกระบวนการเมื่อคุณพิมพ์สูตร^{(typing formulas)}ที่มีการอ้างอิงแบบสัมบูรณ์โดยกดแป้นF4ซึ่งจะทำให้การอ้างอิงเซลล์เป็นแบบสัมบูรณ์

เคอร์เซอร์สามารถอยู่ด้านใดด้านหนึ่งของการอ้างอิงเซลล์ (หรือแม้แต่ตรงกลาง) เมื่อคุณกด F4 และเคอร์เซอร์จะยังแปลงการอ้างอิงเดียวนั้นเป็นแบบสัมบูรณ์

หากคุณไม่ต้องการสัมบูรณ์ (เช่น ผสมแทน) ให้แตะ F4 ต่อไปจนกว่าข้อมูลอ้างอิงจะออกมาตามที่คุณต้องการ

หากคุณต้องการเพิ่มการอ้างอิงถึงเซลล์อื่นๆ ในสูตร ให้วางเคอร์เซอร์ไว้ที่นั่นแล้วเริ่มวนซ้ำใน F4 อีกครั้ง

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

How to Use Absolute References in Excel

Most people are familiar with using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• รักษาการอ้างอิงเซลล์เมื่อคัดลอกสูตรใน Excel

• วิธีการลบบรรทัดว่างใน Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel

• วิธีสร้าง Pivot Table แบบง่ายใน Excel

• วิธีใช้ Flash Fill ใน Excel

• วิธีการเรียงตามตัวอักษรใน Excel

• วิธีสร้างรายการแบบหล่นลงที่เชื่อมโยงหลายรายการใน Excel

• วิธีการคำนวณคะแนน Z ใน Excel

• การเชื่อมต่อ Excel กับ MySQL

• การใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel

• วิธีแก้ไขแถวใน Excel

• 5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel

• วิธีติดตามผู้อยู่ในอุปการะใน Excel

• จัดกลุ่มแถวและคอลัมน์ในแผ่นงาน Excel

• วิธีผสานข้อมูลในไฟล์ Excel หลายไฟล์

• วิธีสร้างมาโครหรือสคริปต์ VBA ใน Excel

• 2 วิธีในการใช้ฟังก์ชัน Transpose ของ Excel