วิธีค้นหา Last Cell

[สมเกียรติ]

สูตรจาก http://www.techonthenet.com/excel/qu...last_value.php

Data range is formatted as text
If your range contains only text values, you could use the following formula:

=INDEX(range,MATCH(REPT("z",255),range))

• where range is the range that you wish to check

For example, if you wanted to find the last text value in column A, you would use the following formula:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

For example, if you wanted to find the last text value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(REPT("z",255),C2:C10))

Data range is formatted as numeric
If your range contains only numbers, you could use the following formula:

=INDEX(range,MATCH(9.99999999999999E+307,range))

• where range is the range that you wish to check

For example, if you wanted to find the last numeric value in column A, you would use the following formula:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

For example, if you wanted to find the last numeric value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(9.99999999999999E+307,C2:C10))

Data range contains both text and numeric
If your range contains both text and numeric values (ie: there must be at least one text and one numeric value in your range) and you want to find the last value, you could use the following formula:

=INDEX(range,MAX(MATCH(9.99999999999999E+307,range),MATCH(REPT("z",255),range)))

• where range if the range that you wish to check

For example, if you wanted to find the last value (either text or numeric) in column A, you would use the following formula:

=INDEX(A:A,MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A)))

For example, if you wanted to find the last value (either text or numeric) in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MAX(MATCH(9.99999999999999E+307,C2:C10),MATCH(REPT("z",255),C2:C10)))

======================================

VBA จาก http://j-walk.com/ss/excel/tips/tip30.htm
The LASTINCOLUMN function

Function LASTINCOLUMN(rngInput As Range)
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Columns(1).EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINCOLUMN = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

The LASTINROW function

Function LASTINROW(rngInput As Range) As Variant
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Rows(1).EntireRow
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINROW = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

Link อื่นที่น่าสนใจ
http://www.ozgrid.com/VBA/ExcelRanges.htm
http://www.mvps.org/dmcritchie/excel/lastcell.htm

[สมเกียรติ]

สำหรับคนไทยที่ใช้ข้อมูลเป็นตัวอักษรไม่ว่าจะเป็นไทยหรืออังกฤษ ควรเปลี่ยนสูตรในส่วนของ
MATCH(REPT("z",255),range)
เป็น
MATCH(REPT("ฮ",255),range)
หรือ
MATCH("ๆ",range)
หรือ
MATCH(CHAR(250),range)

ส่วนกรณีที่เป็นตัวเลข ไม่จำเป็นต้องใช้ตามฝรั่งเขาหรอกครับ
MATCH(9.99999999999999E+307,range)
ใช้แค่
MATCH(999999999999,range)
ขอให้ใส่ตัวเลขอะไรก็ได้ที่มากกว่าตัวเลขที่เก็บอยู่ในตารางก็พอแล้ว

[สมเกียรติ]

Match ทำหน้าที่คืนค่าเป็นตำแหน่งรายการสุดท้ายให้ได้ก็เพราะ

1. โครงสร้างสูตร Match ที่ใช้ ไม่ได้กำหนด option หลังสุดในวงเล็บเป็นเลข 0 หรือ -1
2. และ ค่าที่นำไปใช้หา เป็นค่าที่มากกว่าค่าที่เก็บไว้

ซึ่งในกรณีที่ไม่ได้กำหนด option ไว้หรือกำหนด option = 1 จะถือว่า Match จะหาค่าจากตารางที่ต้องเรียงค่าจากน้อยไปมาก ดังนั้นเมื่อสูตร Match พยายามเปรียบเทียบค่าไปเรื่อยๆทีละค่า แต่กลับไม่พบว่ามีค่านั้นอยู่ จึงคืนค่าเป็นเลขบอกตำแหน่งสุดท้ายให้ในที่สุด

ที่ผมใช้ Char(250) เพราะเป็นอักขระที่น่าจะมากกว่าตัวอักษรทั้งปวง ไม่ว่าไทยอังกฤษ หรือแค่ใช้ "ๆ" ก็พอแล้วและสะกดสั้นกว่า Char(250)

ขอแนะนำให้พวกเราใช้สูตร Match ด้วยความเข้าใจ ไม่จำเป็นต้องไปใส่เลข 9 หลายๆตัวแถมต้องมี E+307 หรือไม่จำเป็นต้องใส่สูตร Rept("ฮ",255) เพื่อทำให้กลายเป็น ฮฮฮฮฮฮฮฮ... 255 ตัวหรอกครับ คนที่เห็นเราใช้สูตรจะได้มองปั้บก็รู้ปุ้บว่า เราใช้สูตรด้วยความเข้าใจ ไม่ได้ลอกเขามาใช้

[สมเกียรติ]

MATCH("ฮๆ",range)
แก้เป็น
MATCH("ๆ",range)

ผู้ค้นพบวิธีนี้มาจากลูกศิษย์ที่อบรมหลักสูตรหันมาใช้ Excel จัดการฐานข้อมูล เมื่อ 13-15 มิถุนายน ครับ

[สมเกียรติ]

ผู้เข้าอบรมเมื่อสัปดาห์ที่ผ่านมาพบว่าสูตร Match ที่ทำหน้าที่หาตำแหน่งสุดท้ายจะทำงานเพี้ยนเมื่อมีค่าอื่นที่ใช้ภาษาต่างกันอยู่ในเซลล์ที่ติดกันครับ เช่นเดิมมีแค่ข้อมูลภาษาอังกฤษ แต่พอพิมพ์ภาษาไทยในเซลล์ที่ติดกัน จะคำนวณหาตำแหน่งเซลล์ผิดพลาด

[สมเกียรติ]

LastRow = Cells(65535, Range("No_Col").Column).End(xlUp).Row

โดย
No_Col
เป็นเซลล์ใน column ที่ต้องการ

[BPhirarak]

อีกวิธีใช้ match("*",range,-1) เท่าที่ลองยังไม่พบข้อผิดพลาดครับอาจารย์

บรรพต

[สมเกียรติ]

ขอบคุณครับ เป็นสูตรใช้กับตัวอักษรไทยหรืออังกฤษก็ได้ (เว้นตัวเลข)

[BPhirarak]

ฝากลองช่วยดูอีก 2 สูตรครับ ไม่แน่ใจว่ามีข้อจำกัดหรือไม่

=MAX(IF(--ISBLANK($D$1:$D$100),0,ROW($D$1:$D$100)))

อันนี้ไม่ต้องกด ctrl+shift+enter
=LOOKUP(0,--(ISBLANK($D1:$D100)),ROW($D$1:$D$100))
บรรพต

[สมเกียรติ]

ยังไม่ได้ทดสอบสูตรต่อไปนี้ครับ

= Match("*",A:A,1) for Text Columns and
= Match(BigNumber, A:A,-1) for Number columns