Khi thay đổi địa chỉ tham chiếu của hàm SUM() này, thì hàm SUM() kia tự động thay đổi

hoanglocphat · 29/10/13

Chào các bạn! nhờ các bạn giúp đỡ công thức sau:

Trong File của tôi, Cell D2 đang là công thức:=SUM(D7

10)

và Cell E2 đang là Cthức =SUM(F7:F10)

Tôi muốn khi Cell D2 thay đổi =SUM(D11

12)
Thì Cell E2 sẽ tự động thay đổi tương ứng theo :=SUM(F11:F12)

Tương tự D2=SUM(D6

8) thì E2=SUM(F6:F8)
Xin cảm ơn!

leonguyenz · 29/10/13

hoanglocphat đã viết:
Chào các bạn! nhờ các bạn giúp đỡ công thức sau:
Trong File của tôi, Cell D2 đang là công thức:=SUM(D710)
và Cell E2 đang là Cthức =SUM(F7:F10)
Tôi muốn khi Cell D2 thay đổi =SUM(D1112)
Thì Cell E2 sẽ tự động thay đổi tương ứng theo :=SUM(F11:F12)
Tương tự D2=SUM(D68) thì E2=SUM(F6:F8)
Xin cảm ơn!

Đưa vùng cần SUM vào Name, các cột khác cần SUM theo kích thước của Name thì dùng hàm OFFSET.
Ví dụ đặt Name ABC =D6

8, D2 =SUM(ABC), E2 =SUM(OFFSET(ABC,,2) tương đương với =SUM(F6:F8). Khi thay đổi kích thước Name ABC thì kết quả SUM tại 2 ô sẽ thay đổi.

VU_BAO · 29/10/13

bạn xem thử cách làm của mình nha

bạn xem file đính kèm.

hoanglocphat · 29/10/13

leonguyenz đã viết:
Đưa vùng cần SUM vào Name, các cột khác cần SUM theo kích thước của Name thì dùng hàm OFFSET.
Ví dụ đặt Name ABC =D68, D2 =SUM(ABC), E2 =SUM(OFFSET(ABC,,2) tương đương với =SUM(F6:F8). Khi thay đổi kích thước Name ABC thì kết quả SUM tại 2 ô sẽ thay đổi.

Chào bạn
Do tôi thường xuyên thay đổi thì fải thay đổi kích thước Name thì fức tạp quá
Không biết bạn nào có cách khác không. Xin cảm ơn

thanhtrg · 31/10/13

hoanglocphat đã viết:
Chào các bạn! nhờ các bạn giúp đỡ công thức sau:

Trong File của tôi, Cell D2 đang là công thức:=SUM(D710)

và Cell E2 đang là Cthức =SUM(F7:F10)

Tôi muốn khi Cell D2 thay đổi =SUM(D1112)
Thì Cell E2 sẽ tự động thay đổi tương ứng theo :=SUM(F11:F12)

Tương tự D2=SUM(D68) thì E2=SUM(F6:F8)
Xin cảm ơn!

Vấn đề của bạn...vô cùng đơn giản

leonguyenz · 31/10/13

thanhtrg đã viết:
Vấn đề của bạn...vô cùng đơn giản

Code của bạn làm mình phải vào Task Manager để tắt Excel, hình như dính lỗi Vòng lặp vô tận của sự kiện mà admin kyo đã nói ở đây: [h=1][Gỡ rối VBA] Lỗi vòng lặp lặp vô tận - Lỗi từ vòng lặp[/h]

thanhtrg · 31/10/13

leonguyenz đã viết:
Code của bạn làm mình phải vào Task Manager để tắt Excel, hình như dính lỗi Vòng lặp vô tận của sự kiện mà admin kyo đã nói ở đây: [Gỡ rối VBA] Lỗi vòng lặp lặp vô tận - Lỗi từ vòng lặp

Bạn xem lại giúp lỗi ở chỗ nào chứ trong đoạn code đó không sử dụng vòng lặp . Ở máy tôi vẫn hoạt động bình thường không có vấn đề gì

leonguyenz · 31/10/13

thanhtrg đã viết:
Bạn xem lại giúp lỗi ở chỗ nào chứ trong đoạn code đó không sử dụng vòng lặp . Ở máy tôi vẫn hoạt động bình thường không có vấn đề gì

Sry, không phải lỗi vòng lặp mà là lặp sự kiện, mình dẫn link sai.
Mình thử nới dòng trong hàm SUM cột D thì bị lỗi như sau:

Nhấn End hoặc Debug, Excel bị tình trạng nhấp nháy dữ liệu liên tục, phải thoát bằng Task Manager.
Excel mình cài sẵn kiểm tra biến Option Explicit.

hoanglocphat · 5/11/13

Nhờ các bạn giúp đỡ code cho trường hợp này:
Tôi có sưu tầm được đoạn code của anh NDU như sau

Mã:

Function Noi(ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String
    Dim Tmparr, SubArr, Arr(), Item, n As Long, tmp As String
    'On Error Resume Next
    For Each SubArr In sArray
        Tmparr = IIf(IsArray(SubArr), SubArr, Array(SubArr))
        For Each Item In Tmparr
            tmp = IIf(TypeName(Item) = "Error", "", Trim(CStr(Item)))
            If IgnoreBlanks = False Or Len(tmp) Then
                n = n + 1
                ReDim Preserve Arr(1 To n)
                Arr(n) = tmp
            End If
        Next
    Next
    If n Then Noi = Join(Arr, Sep)
End Function

Code cho kết qủa như cell C1
Tôi muốn khi thay đổi địa chỉ tham chiếu ở cell C1 thì công thức ở cell J1 và K1 sẽ thay đổi tương ứng theo ( cách thay đổi như đã nói ở bài #1)
Cảm ơn sự giúp đỡ của các bạn!

ndu96081631 · 5/11/13

hoanglocphat đã viết:
Nhờ các bạn giúp đỡ code cho trường hợp này:
Tôi có sưu tầm được đoạn code của anh NDU như sau

Mã:

Function [COLOR=#ff0000][SIZE=4]Noi[/SIZE][/COLOR](ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String End Function

Tôi chả bao giờ dùng tên hàm kiểu vậy cả.
Tên gốc là Function JoinText
- Hoặc là các bạn tự mình viết hàm mà xài
- Hoặc là các bạn nên để nguyên những gì tôi đã viết (nhất là mấy cái tên)
- Các bạn có thể cải tiến về mặt giải thuật (chuyện này tôi cảm ơn). Riêng phần tên hàm, tên biến, tên Sub... làm ơn để nguyên giùm cái. Tôi rất ghét mấy cái tên dạng "chế" đọc nghe chẳng giống ai như ở trên
Một yêu cầu rất nhỏ gọi là tôn trọng tác giả... và tôi chỉ yêu cầu thế thôi, chắc các bạn không cảm thấy phiền chứ?
--------------
Đây là code gốc:

Mã:

Function JoinText(ByVal Delimiter As String, ByVal IgnoreBlanks As Boolean, ParamArray Arrays()) As String
  Dim aTmp, Arr(), Item, tmp As String
  Dim i As Long, n As Long
  'On Error Resume Next
  For i = LBound(Arrays) To UBound(Arrays)
    aTmp = Arrays(i)
    If Not IsArray(aTmp) Then aTmp = Array(aTmp)
    For Each Item In aTmp
      tmp = IIf(TypeName(Item) = "Error", "", Trim(CStr(Item)))
      If IgnoreBlanks = False Or Len(tmp) Then
        n = n + 1
        ReDim Preserve Arr(1 To n)
        Arr(n) = tmp
      End If
    Next
  Next
  If n Then JoinText = Join(Arr, Delimiter)
End Function

thanhtrg · 5/11/13

leonguyenz đã viết:
Sry, không phải lỗi vòng lặp mà là lặp sự kiện, mình dẫn link sai.
Mình thử nới dòng trong hàm SUM cột D thì bị lỗi như sau:
View attachment 111676
Nhấn End hoặc Debug, Excel bị tình trạng nhấp nháy dữ liệu liên tục, phải thoát bằng Task Manager.
Excel mình cài sẵn kiểm tra biến Option Explicit.

Đoạn code trên chỉ hoạt động trong EX2003, đoạn code sau hoạt động trong mọi phiên bản

ptm0412 · 5/11/13

thanhtrg đã viết:
Đoạn code trên chỉ hoạt động trong EX2003, đoạn code sau hoạt động trong mọi phiên bản

Gởi thanhtrg,

Bạn đưa code lên 2 lần, thì cả 2 đều sai cơ bản về thủ tục cho sự kiện Change:

Code thứ nhất bạn dùng:

Mã:

Set target = [D2]
[E2] = Replace([D2].Formula, "D", "F")

Nghĩa là với bất kỳ thay đổi của bất kỳ ô nào, code cũng chạy. Thí dụ E2: Bị code làm thay đổi, code chạy lần nữa. Kết quả là lặp sự kiện vô tận.

Code thứ 2 bạn dùng:

Mã:

If target = [D2] Then [E2] = Replace([D2].Formula, "D", "F")

(So sánh giá trị target và giá trị D2)

Nghĩa là bất kỳ ô nào, sau khi thay đổi có giá trị bằng ô D2, cũng làm cho code chạy.
Ngoài ra, nếu xóa, hay làm gì đó với 2 ô trở lên 1 lúc thì bị lỗi Type Mismatch (giá trị nhiều ô không so sánh với giá trị 1 ô được)

Code đúng phải là:

Mã:

If Not Intersect(Target, [D2]) Is Nothing Then

thanhtrg · 6/11/13

ptm0412 đã viết:
Gởi thanhtrg,

Bạn đưa code lên 2 lần, thì cả 2 đều sai cơ bản về thủ tục cho sự kiện Change:

Code thứ nhất bạn dùng:

Mã:

Set target = [D2] [E2] = Replace([D2].Formula, "D", "F")

Nghĩa là với bất kỳ thay đổi của bất kỳ ô nào, code cũng chạy. Thí dụ E2: Bị code làm thay đổi, code chạy lần nữa. Kết quả là lặp sự kiện vô tận.

Code thứ 2 bạn dùng:

Mã:

If target = [D2] Then [E2] = Replace([D2].Formula, "D", "F")

(So sánh giá trị target và giá trị D2)

Nghĩa là bất kỳ ô nào, sau khi thay đổi có giá trị bằng ô D2, cũng làm cho code chạy.
Ngoài ra, nếu xóa, hay làm gì đó với 2 ô trở lên 1 lúc thì bị lỗi Type Mismatch (giá trị nhiều ô không so sánh với giá trị 1 ô được)

Code đúng phải là:

Mã:

If Not Intersect(Target, [D2]) Is Nothing Then

Thực ra chỉ cần xử lý một "chút xíu" như sau :

Mã:

If target.address = "$D$2" Then [E2] = Replace(target.Formula, "D", "F")

ndu96081631 · 6/11/13

thanhtrg đã viết:
Thực ra chỉ cần xử lý một "chút xíu" như sau :

Mã:

If target.address = "$D$2" Then [E2] = Replace(target.Formula, "[COLOR=#ff0000][B]D[/B][/COLOR]", "F")

Bài này tôi nghĩ chẳng đơn giản như vậy!
Ở trên người ta chỉ đưa ví dụ có mỗi hàm SUM thôi. Nếu người ta dùng hàm DSUM hoặc ROUND (hoặc cái gì đó tương tự) thì sao?

ptm0412 · 6/11/13

thanhtrg đã viết:
Thực ra chỉ cần xử lý một "chút xíu" như sau :

Mã:

If target.address = "$D$2" Then [E2] = Replace(target.Formula, "D", "F")

Nếu chỉ sửa như thế, bạn thử test nếu bạn tô khối và xóa 1 lần 3 ô D2

4. Hoặc bạn tô khối D2

3 và sửa D3, Ctrl Enter, xem E2 có thay đổi theo không?

thanhtrg · 6/11/13

ptm0412 đã viết:
Nếu chỉ sửa như thế, bạn thử test nếu bạn tô khối và xóa 1 lần 3 ô D35. Hoặc bạn tô khối D34 và sửa D3, Ctrl Enter, xem E2 có thay đổi theo không?

Nếu vậy thì...càng khoẻ

Mã:

If target.Column = 4 Then [E2] = Replace([D2].Formula, "D", "F")

ptm0412 · 6/11/13

thanhtrg đã viết:
Nếu vậy thì...càng khoẻ

Mã:

If target.Column = 4 Then [E2] = Replace([D2].Formula, "D", "F")

Nghĩa là sửa, xóa, thêm, ... bất kỳ ô nào trong cột D, code cũng chạy mặc dù không cần thiết?

thanhtrg · 6/11/13

ptm0412 đã viết:
Nghĩa là sửa, xóa, thêm, ... bất kỳ ô nào trong cột D, code cũng chạy mặc dù không cần thiết?

Vấn đề là bảo đảm code luôn luôn sẵn sàng hoạt động trong mọi tình huống vì không biết trước cấu trúc dữ liệu thay đổi thế nào .

ptm0412 · 6/11/13

Với tôi thì vấn đề là:
- Code chạy ra đúng kết quả
- Không có lệnh thừa, cũng không thiếu
- Chạy khi cần thiết, và chạy đúng số lần cần thiết
- Đúng trong mọi trường hợp (nếu do khả năng mà không test hết trường hợp được, thì cũng ít nhất là đúng cho nhiều trường hợp)
- Nếu có thể, phải bảo đảm tốc độ
- Sẵn sàng sửa sai khi được góp ý

Tôi thấy cách này nhiều người dùng, và đúng, nên tôi học theo:

If Not Intersect(Target, [D2]) Is Nothing Then

Còn bạn nghĩ khác cũng là quyền của bạn.

hoanglocphat · 6/11/13

ndu96081631 đã viết:
Bài này tôi nghĩ chẳng đơn giản như vậy!
Ở trên người ta chỉ đưa ví dụ có mỗi hàm SUM thôi. Nếu người ta dùng hàm DSUM hoặc ROUND (hoặc cái gì đó tương tự) thì sao?

Thực tế tôi chỉ sử dụng hàm Sum() thôi!
Nhờ các bạn viết code cho bài # 9 .
Cảm ơn các bạn.

Khi thay đổi địa chỉ tham chiếu của hàm SUM() này, thì hàm SUM() kia tự động thay đổi

Thành viên thường trực

File đính kèm

Thành viên gạo cội

Thành viên chính thức

File đính kèm

Thành viên thường trực

Thành viên hoạt động

File đính kèm

Thành viên gạo cội

Thành viên hoạt động

Thành viên gạo cội

Thành viên thường trực

File đính kèm

Huyền thoại GPE

Thành viên hoạt động

File đính kèm

Bad Excel Member

Thành viên hoạt động

Huyền thoại GPE

Bad Excel Member

Thành viên hoạt động

Bad Excel Member

Thành viên hoạt động

Bad Excel Member

Thành viên thường trực

Facebook

Group