lấy địa chỉ ô Merge and center

[nguyendangkhoi]

Các anh chị cho em hỏi trong excel có hàm nào (hay dùng vb cũng được) lấy được địa chỉ của ô Merge and center không ?

Em ví dụ

ở cột A1 đến A3 và A4 đến A5 em trộn Merge and center. có công thức nào hay macro mà khi mình nhấn vào A1 thì nó sẽ hiểu và lấy địa chỉ là A1:A3, khi nhấn vào A4 thì nó sẽ hiểu và lấy địa chỉ là A4:A5.

Cám ơn các anh chị nhiều

 

[phamduylong]

Các anh chị cho em hỏi trong excel có hàm nào (hay dùng vb cũng được) lấy được địa chỉ của ô Merge and center không ?

Em ví dụ

ở cột A1 đến A3 và A4 đến A5 em trộn Merge and center. có công thức nào hay macro mà khi mình nhấn vào A1 thì nó sẽ hiểu và lấy địa chỉ là A1:A3, khi nhấn vào A4 thì nó sẽ hiểu và lấy địa chỉ là A4:A5.

Cám ơn các anh chị nhiều

Đây là hàm lấy địa chỉ vùng ô MergeCells:

Function MergeCellsAddress(mycell As Range) As String
If mycell.MergeCells = True Then
  MergeCellsAddress = mycell.Resize(mycell.Offset(1, 0).Row - mycell.Row, mycell.Offset(0, 1).Column - mycell.Column).Address(0, 0)
Else
  MergeCellsAddress = mycell.Address(0, 0)
End If
End Function

Ví dụ:MergeCellsAddress(A1) > A1:A3
Bạn vận dụng nó vào bài của bạn.

 

[tedaynui]

Đây là hàm lấy địa chỉ vùng ô MergeCells:

Function MergeCellsAddress(mycell As Range) As String
If mycell.MergeCells = True Then
  MergeCellsAddress = mycell.Resize(mycell.Offset(1, 0).Row - mycell.Row, mycell.Offset(0, 1).Column - mycell.Column).Address(0, 0)
Else
  MergeCellsAddress = mycell.Address(0, 0)
End If
End Function

Ví dụ:MergeCellsAddress(A1) > A1:A3
Bạn vận dụng nó vào bài của bạn.

Tuy nhiên, hàm này không tự Update (kể cả F9). Muốn Update thì phải F2+Enter
Test lại bằng cách Merge/UnMerge sẽ thấy ngay

TDN

 

[phamduylong]

Tuy nhiên, hàm này không tự Update (kể cả F9). Muốn Update thì phải F2+Enter
Test lại bằng cách Merge/UnMerge sẽ thấy ngay
TDN

Ý đồ của anh là người dùng vận dụng code trong function đưa vào 1 thủ tục thực hiện 1 ý đồ nào đó

MergeCellsAddress = mycell.Resize(mycell.Offset(1, 0).Row - mycell.Row, mycell.Offset(0, 1).Column - mycell.Column).Address(0, 0)

Chứ hàm này không giải quyết được vấn đề gì. Excel chỉ cần biết ô đầu tiên của MergeCells. (SUM(A1, B5), SUM(A1:A3;B5) cũng như nhau.

 

[ChanhTQ@]

Bạn nhấn double vô ô trộn vậy nha!

Các anh chị cho em hỏi trong excel có dùng vb lấy được địa chỉ của ô Merge and center không ?
Em ví dụ Ở cột A1 đến A3 và A4 đến A5 em trộn Merge and center. có công thức nào hay macro mà khi mình nhấn vào A1 thì nó sẽ hiểu và lấy địa chỉ là A1:A3, khi nhấn vào A4 thì nó sẽ hiểu và lấy địa chỉ là A4:A5.
Cám ơn các anh chị nhiều

Chép macro này vô macro sự kiện nha:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Target.MergeCells = True Then
   Dim MergeAddress As String
 
   MergeAddress = Target.Address
   MsgBox MergeAddress, , Target.Cells(1, 1).Value
 End If
End Sub

(Phải chuột vô thanh SheetName Chọn dòng cuối để mở cửa sổ VB & chép macro trên vô;
Sau đó bạn bấm double vô 1 trong những ô đã được trộn xem sao.

Chúc thành công.

 

[ndu96081631]

Tại sao lại không dùng MergeArea Properties nhỉ? Vì đây là cách chính xác nhất để lấy địa chỉ của vùng đang được Merge
Lấy 1 ví dụ thể này để các bạn dể hình dung:
- Ta có vùng B312 đã được merge
- Vậy sau khi xây dựng hàm, kiểm tra lại bằng hàm ROWS của Excel ta phải nhận được kết quả thế này:
ROWS(UDF(B3)) = ROWS(UDF(C3)) = ROWS(UDF(D3)) =
= ROWS(UDF(B4)) = ROWS(UDF(C4)) = ROWS(UDF(D4))
=......
= ROWS(UDF(B12)) = ROWS(UDF(C12)) = ROWS(UDF(D12))
và = 10
Đúng chứ?
Ngoài ra còn phải lường trước trường hợp tham chiếu không nằm trong vùng Merge ---> Vẫn cho kết quả chính xác (như đối với 1 vùng bình thường)
Tôi đề nghị code như sau:

Function MerRng(Rng As Range) As Range
  Application.Volatile
  On Error GoTo Normal
  Set MerRng = Rng.MergeArea
  Exit Function
Normal: Set MerRng = Rng
End Function

Và tôi cho rằng MergeArea là sự lựa chọn hợp lý nhất đối với yêu cầu của tác giả

 

[nguyendangkhoi]

tra em tuong dùng MergeCellsAddress lấy được ô địa chỉ A1:A3 rồi ghép thêm hàm sum(MergeCellsAddress) là nó sẽ cộng được. ai dè không được.

Ccác anh có cách nào sum các ô tương ứng của các ô bị merge and center không.

ví dụ:em có bản tính tại A1 trộn từ A1:A3 và tại ô C1 đến C3 là số bây giờ em muốn là tại ô B1 mình sẽ sum(C1:C3). (cộng theo số ô bị merge and center)

File ví dụ đính kèm

 

[ndu96081631]

tra em tuong dùng MergeCellsAddress lấy được ô địa chỉ A1:A3 rồi ghép thêm hàm sum(MergeCellsAddress) là nó sẽ cộng được. ai dè không được.

Ccác anh có cách nào sum các ô tương ứng của các ô bị merge and center không.

ví dụ:em có bản tính tại A1 trộn từ A1:A3 và tại ô C1 đến C3 là số bây giờ em muốn là tại ô B1 mình sẽ sum(C1:C3). (cộng theo số ô bị merge and center)

File ví dụ đính kèm

Thì ra là thế ---> Ngay từ đầu bạn đưa file lên thì chắc chắn mọi người đã dùng cách khác rồi ... Tại bạn dẩn dắt mọi người đi theo hướng của bạn mà thôi... Tuy nhiên cũng cảm ơn vì nhờ đó mà phát hiện ra MergeArea... Ẹc... Ẹc...
Lở rồi, thử áp dụng hàm tự tạo vừa làm vào file bạn xem sao:
=SUM(OFFSET(MerRng(A1),,2))

 

[nguyendangkhoi]

Thì ra là thế ---> Ngay từ đầu bạn đưa file lên thì chắc chắn mọi người đã dùng cách khác rồi ... Tại bạn dẩn dắt mọi người đi theo hướng của bạn mà thôi... Tuy nhiên cũng cảm ơn vì nhờ đó mà phát hiện ra MergeArea... Ẹc... Ẹc...
Lở rồi, thử áp dụng hàm tự tạo vừa làm vào file bạn xem sao:
=SUM(OFFSET(MerRng(A1),,2))

Cám ơn anh rất nhiều (ủa mà cộng cái này có nhiều cách lắm hả anh, mình dùng hàm của excel không được không anh).

Anh giải thích giùm em công thức được không ạh (em nhìn công thức mà hỏng hiểu gì hết )

 

[ndu96081631]

Anh giải thích giùm em công thức được không ạh (em nhìn công thức mà hỏng hiểu gì hết )

Hàm MerRng là hàm tự tạo.. như đã trình bày ở trên... Ở đây MerRng(A1) hay MerRng(A2) hay MerRng(A3) đều cũng được biến đổi thành A1:A3 (vì vùng này đã được Merge)
Còn lại là hàm Offset --> Chuyên dùng để dịch chuyển 1 vùng từ vị trí này sang vị trí khác
===>
Offset(MerRng(A1),,2) tương đương với Offset(A1:A3,,2) ---> Dịch chuyển vùng A1:A3 sang 2 cột ---> Trở thành C1:C3 ---> Ra đến kết quả này thì SUM chúng lại thôi!
Có lẽ bạn nên tham khảo thêm hàm OFFSET thì hay hơn

Cám ơn anh rất nhiều (ủa mà cộng cái này có nhiều cách lắm hả anh, mình dùng hàm của excel không được không anh).

Gữi bạn cách dùng công thức thường đây...
Gõ vào cell B1 công thức:

=IF(A1="","",SUM($C1:$C$11)-SUM($B2:$B$12))

rồi kéo fill xuống

 

[nguyendangkhoi]

Hàm MerRng là hàm tự tạo.. như đã trình bày ở trên... Ở đây MerRng(A1) hay MerRng(A2) hay MerRng(A3) đều cũng được biến đổi thành A1:A3 (vì vùng này đã được Merge)
Còn lại là hàm Offset --> Chuyên dùng để dịch chuyển 1 vùng từ vị trí này sang vị trí khác
===>
Offset(MerRng(A1),,2) tương đương với Offset(A1:A3,,2) ---> Dịch chuyển vùng A1:A3 sang 2 cột ---> Trở thành C1:C3 ---> Ra đến kết quả này thì SUM chúng lại thôi!
Có lẽ bạn nên tham khảo thêm hàm OFFSET thì hay hơn

Gữi bạn cách dùng công thức thường đây...
Gõ vào cell B1 công thức:

=IF(A1="","",SUM($C1:$C$11)-SUM($B2:$B$12))

rồi kéo fill xuống

nhưng em không hiểu là tại sao nó lại sum chính xác được C1:C3

Các anh giỏi quá

 

[ndu96081631]

nhưng em không hiểu là tại sao nó lại sum chính xác được C1:C3

Các anh giỏi quá

Thật ra cũng đơn giản thôi... Ý nghĩa của công thức là: Sum toàn bộ dử liệu từ dòng hiện hành đến hết... sau đó trừ bớt đi những gì ta đã SUM ở dưới... chỉ vậy thôi!
----------
Đúng ra nên gõ công thức từ cell B11 rồi kéo fill ngược lên mới thấy được ý nghĩa của công thức

 

[nguyendangkhoi]

Cám ơn anh ndu96081631 rất nhiều

 

[nguyendangkhoi]

Các anh chị cho em hỏi mình có thể tạo 1 hàm tự tạo để sum theo yêu cầu trên không

Cám ơn các anh chị

 

[ndu96081631]

Các anh chị cho em hỏi mình có thể tạo 1 hàm tự tạo để sum theo yêu cầu trên không

Cám ơn các anh chị

Trời... thế cái MerRng chẳng phải hàm tự tạo là gì? Bạn còn muốn thế nào nữa đây?

 

[nguyendangkhoi]

Dạ em muốn là mình gõ thẳng vào ô luôn không cần phải sử dụng công thức của Excel
VD : =TongMG(ô-MG;cột cần tính tổng)
ô MG : là địa chỉ ô Merge And Center
cột cần tính tổng : là cái cột mình cần tính tổng

 

[ndu96081631]

Dạ em muốn là mình gõ thẳng vào ô luôn không cần phải sử dụng công thức của Excel
VD : =TongMG(ô-MG;cột cần tính tổng)
ô MG : là địa chỉ ô Merge And Center
cột cần tính tổng : là cái cột mình cần tính tổng

Cũng hơi chua vì Offset không làm việc đúng như ta nghĩ (trong trường hợp có merge cell)
tuy nhiên tạm thời làm cho bạn theo kiểu "củ chuối" này:

Function TongMG(Cel As Range, Cot As Long)
  Dim MerRng As Range
  Application.Volatile
  On Error GoTo Normal
  Set MerRng = Cel.MergeArea
  GoTo Tinh
Normal: Set MerRng = Cel
Tinh:
  If Cel = "" Then
    TongMG = ""
  Else
    TongMG = WorksheetFunction.Sum(MerRng.Offset(, Cot - 1).Resize(MerRng.Rows.Count))
  End If
End Function

Thử cú pháp hàm tại cell B1:
=TongMG(A1,3)
Cho kết quả = 6

 

[nguyendangkhoi]

Cũng hơi chua vì Offset không làm việc đúng như ta nghĩ (trong trường hợp có merge cell)
tuy nhiên tạm thời làm cho bạn theo kiểu "củ chuối" này:

Function TongMG(Cel As Range, Cot As Long)
  Dim MerRng As Range
  Application.Volatile
  On Error GoTo Normal
  Set MerRng = Cel.MergeArea
  GoTo Tinh
Normal: Set MerRng = Cel
Tinh:
  If Cel = "" Then
    TongMG = ""
  Else
    TongMG = WorksheetFunction.Sum(MerRng.Offset(, Cot - 1).Resize(MerRng.Rows.Count))
  End If
End Function

Thử cú pháp hàm tại cell B1:
=TongMG(A1,3)
Cho kết quả = 6

Cảm ơn anh rất nhiều Anh đúng là chuyên gia GPE