Hỏi về hàm kiểm tra xem ô có nằm trong miền không? (2 người xem)

[tranan76]

Giả sử tại ô A1 =sum(A2:A10)
vậy khi bạn nhập số liệu trên ô A5 thì có hàm nào chỉ ra rằng ô A5 nằm trong miền công thức của ô A1 không? Xin được giúp đỡ

 

[ThuNghi]

Theo tôi bạn dùng conditional Format, đặt A2:A10 là mien, dùng countif(mien,$a2)>0 thì cho màu gì đó.

 

[Nguyễn Duy Tuân]

Đơn giản nhất bạn làm thế này:
b1- Đặt con trỏ ô tại ô bạn muốn kiểm tra
b2- Vào menu Tools\Formula Auditing\Trace Dependents

Nếu muốn tự động hóa, bạn hãy Record Macro (b2) sau đó gài tên macro vào trong sự kiện "Workbook_SheetChange" của VBA.

 

[tranan76]

Cảm ơn 2 bạn đã trả lời, nhưng ý mình là kiểm tra để tự động viết lại công thức trong ô A1 cơ. tức là nếu mình nhập lại ô A5 thì công thức vẫn là sum(A2:A10) còn nếu nhập vào ô A12 thì công thức tại A1 là sum(A2:A12). Vấn đề nhập ô tiếp theo thì mình đã giải quyết được. nhưng vấn đề quay lại để sửa mình xin nhờ các bạn

 

[Nguyễn Duy Tuân]

Vậy thì tốt nhất bạn lập công thức "chờ", có nghĩa là bạn đặt công thức là = SUM(A1:A1000) luôn. Cũng có thể làm hoàn toàn theo ý bạn, nhưng mất thời gian, có lẽ không nhất thiết như vậy.

 

[Tuấn Giang]

Không có code.

 

[Đào Việt Cường]

Giải Pháp Excel > Kiến thức Excel > Lập Trình với Excel > Trung tâm giải thích code.
Hỏi về hàm kiểm tra xem ô có nằm trong miền không?


Dear all,
--------
Bài viết trong "Trung tâm giải thích code" mà chả thấy code đâu... liệu có đáng để trả lời?
- Nếu bạn muốn áp dụng công thức trên Cell mà không thông qua VBA thì có thể sử dụng chức năng Auto Sum (Alt + =) sẵn có. Tất nhiên vùng tính tổng của bạn phải "đầy", nghĩa là không có Cell nào rỗng (nếu rỗng bạn Replace All rỗng thành 0)
- Nếu là lập trình thì bạn kiểm tra chỉ số dòng và chỉ số cột của Cell chứa công thức tính tổng phải đảm bảo "ngoài vùng phủ sóng". Bạn cứ viết code đi rồi mọi người giải thích cho bạn!

 

[tranan76]

--------
Bài viết trong "Trung tâm giải thích code" mà chả thấy code đâu... liệu có đáng để trả lời?

Xin tiếp thu phê bình của bạn. Không phải vì tôi ngại up lên mà sợ phải giải thích dài dòng.Tôi xin gửi code lên đây.

Mục đích là khi nhập diễn giải vào cột E (vd: trục a: 355/113) thì sẽ tự động tính, điền vào là trục a: 355/113 = 3,142 và viết công thức tổng khối lượng vào cột G của công việc tương ứng.

Khó khăn gặp phải: giả sử quay lại sửa công thức diễn giải tại ô E10 chẳng hạn, thì thay vì công thức tại G7 là =tongkl(E8:E12) thì nó lại là =tongkl(E8:E10)
Mong được giúp đỡ

 

[nvson]

.... nhưng ý mình là kiểm tra để tự động viết lại công thức trong ô A1 cơ. tức là nếu mình nhập lại ô A5 thì công thức vẫn là sum(A2:A10) còn nếu nhập vào ô A12 thì công thức tại A1 là sum(A2:A12). Vấn đề nhập ô tiếp theo thì mình đã giải quyết được. nhưng vấn đề quay lại để sửa mình xin nhờ các bạn

Theo mình thì bạn sử dụng chức năng LIST có sẵn trong Excel:
Đặt chuột vào ô A2, nhấn Data/ List/ Create list --->OK
Bạn nhập các giá trị trong vùng (List sẽ tự động thay đổi kích thước)
Đặt con trỏ trong vùng List rồi nhấn Data/ List/ Total Row
Bây giờ ô cuối cùng trong List đó sẽ có công thức và nó sẽ tự động thay đổi khi bạn thêm, bớt dữ liệu.
Mặc định là hàm Sum, nếu bạn muốn dùng hàm khác thì nhấn chuột vào ô đó, nhấn tiếp vào mũi tên bên cạnh để chọn hàm.
.....

 

[tranan76]

Theo mình thì bạn sử dụng chức năng LIST có sẵn trong Excel

Đặt chuột vào ô A2, nhấn Data/ List/ Create list --->OK

Cám ơn bạn. mình muốn tự động hóa hoàn toàn cơ. nếu làm như vậy thì vẫn hơi thủ công. bạn có thể tham khảo file gửi kèm để xem có giúp được mình không. Vấn đề chắc cũng khá phức tạp đây.

 

[Đào Việt Cường]

Dear tranan76,
---------------
Mình cảm thấy phức tạp là do bạn muốn vậy thôi!
Mình có cảm giác như bạn lạm dụng VBA quá trong khi chưa khai thác những chức năng căn bản của Excel. Điều này đã dẫn đến những rắc rối không đáng có như trường hợp của bạn. (Bạn xem lại nhé: khi mình nhập công thức =355/113+100/81 để tính toán vào 1 Cell thì lập tức công thức đó lại biến thành 4,37616082158855 = 4,376. Cần phải giới hạn phạm vi của Taget trong vùng bảng tính toán thôi!)

Mình không có kiến gì về các thủ tục mà bạn xây dựng để tính tổng khối lượng, mà góp ý bạn làm theo cách này bạn xem có được không:
Tạo thêm cột phụ để điền các tham số cần tính tổng. Đây sẽ là số liệu để bạn tính toán, ví dụ:

    [B][COLOR=dimgray]A               B                  C               D                 E             F           [/COLOR][/B]
[B][COLOR=dimgray]1[/COLOR][/B]Mã đơn giá   Hạng mục          Đơn vị tính    Chỉ tiêu 1      Chỉ tiêu 2     Khối lượng 
[B][COLOR=dimgray]2[/COLOR][/B]  AF.12112     Bê tông dầm ngang   m2                                  =SUBTOTAL(9; $F3:$F4)
[B][COLOR=#696969][COLOR=#696969]3[/COLOR]  = $D3 &"/" & $ $E3 & " = " & $F3                    355           113         =  $D3/$E3[/COLOR][/B][COLOR=#696969]
[B][COLOR=#696969][B]4[/B][/COLOR]  = $D4 &"/" & $ $E4 & " = " & $F4                 100           81          =  $D4/$E4[/B]
[B]5   ....[/B]
[B]________________________________________________________________________________________________[/B]
[B]6    Cộng khối lượng                                                        =SUBTOTAL(9; $F2:$F5)[/B]
[/COLOR]

 

[tranan76]

Cám ơn bạn đã đọc và trả lời, đúng là câu chữ không thể hiện được hết ý.
1.Thực ra đặc thù của dự toán xây dựng là kết quả phải được diễn giải rõ ràng từ phép tính nào ra vì nó là bóc tách từ bản vẽ ra(tất cả phần mềm dự toán hiện nay đều như thế).
2.Tôi đã dùng VBA thì đâu đến nỗi không biết mấy cái hàm và phép toán ghép chuỗi như thế. Nếu trường hợp một phép tính rất dài mà chia cột như bạn thì chia bao nhiêu cột cho vừa? Mà nhập vào 1 ô thì chắc chắn nhanh hơn nhập vào nhiều ô rồi. Chưa kể số dòng của 1 bảng dự toán của 1 công trình cái nhà bạn ở thôi đã đến vài trăm đến hàng nghìn dòng mà có dòng nào công thức giống dòng nào đâu. Có công việc thì 1 vài dòng diễn giải, có việc vài chục dòng. như vậy nếu làm như bạn thì thủ công quá.
VD: Tường tầng 1 trục A: 0,22*(4+2,5+3)*3,6.
3.Cái bảng mình đã lập thì gõ công thức vào cột E không cần dấu "=" đằng trước nên khi bạn gõ =355/113+100/81 thì nó không thể hiện được mục đích là đúng rồi.
4.Nếu bạn có biện pháp nào hay thì có thể chỉ giúp mình với.
5 Cảm ơn bạn đã đọc và trả lời

 

[Đào Việt Cường]

Dear tranan76,
---------------
Mình thì chẳng ưa cái anh chàng VBA tí nào, chẳng qua thấy các anh chị trên diễn đàn hào hứng với "nó" quá nên đem lòng ganh tị chút thôi!
Vẫn một tư tưởng thoát ly, mình cung cấp thêm cho bạn một giải pháp khác xem có đạt yêu cầu không nhé:
Diễn giải để thống nhất yêu cầu:
Bạn muốn tính khối lượng của từng công việc, sau đó cộng tổng các khối lượng thực hiện để làm đơn giá. Yêu cầu của bạn là làm sao dựa vào diễn giải (chủ yếu là các phép tính số học đơn giản) để đưa ra kết quả tương ứng.

Hướng giải quyết: Sử dụng EVALUATE(<Biểu thức>)

Cách thức:
Bước 1: Thiết lập biểu thức cho EVALUATE bằng cách nhất quán diễn giải công thức.

Nếu bạn không muốn mở riêng cột công thức mà muốn tận dụng luôn phần điễn giải thì phải đảm bảo các mục diễn giải phải nhất quán nhau sao cho có dấu hiệu phân biệt đâu là công thức. Cụ thể trong file minh hoạ của bạn mình nhận thấy công thức cần tính toán là chuỗi nằm giữa dấu : và dấu =. Nhưng để đơn giản mình đề nghị không biểu diễn phần kết quả trong diễn giải vì phần này được phản ánh trên cột Khối lượng rồi - cái mà chúng ta đang thảo luận.Ví dụ thêm cho cụ thể:

"Phòng khách: 0,22*4*3,6 = 3,168"

bạn diễn giải thành:

"Phòng khách: 0,22*4*3,6"

Chúng ta coi chuỗi "0,22*4*3,6" là công thức cần tính toán và kết quả bằng bao nhiêu là nhiệm vụ của chúng ta!

Bạn cũng phải lưu ý trong diễn giải phải thoả mãn quy tắc như đã nêu và chắc chắn rằng sau dấu : phải có một khoản trắng (và chỉ một mà thôi!)

Bước 2: Lập công thức:
- Bạn phải trích ra được chuỗi công thức từ diễn giải, sử dụng hàm RIGHT() kết hợp hàm LEN(), lưu ý nên sử dụng tham chiếu tương đối dòng để áp dụng cho các dòng còn lại.
- Đặt Name cho công thức, referer to của Name có dạng: =EVALUATE(<Công thức>)
- Tại cell cần tính khối lượng bạn áp F3 để insert Name vừa tạo và áp dụng cho các row còn lại.

Minh hoạ: trên file của bạn
- Đặt con trỏ tại cell G9 (tính khối lượng công việc cho Phòng khách, trong hạng mục có mã đơn giá AE.12121)
- Ctrl+F3 để định nghĩa một Name với tham chiếu như sau (bạn có thể copy công thức dưới đây):

=EVALUATE(RIGHT(Sheet1!E9;LEN(Sheet1!E9)-FIND(":";Sheet1!E9)-1))

- OK, quay trở lại Worksheet, F3 để insert Name bạn vừa tạo, kết quả: 3,168

Đối với các tổng hạng mục, mình khuyên bạn nên dùng hàm SUBTOTAL thay cho hàm SUM (lý do: bạn dùng nhiều khắc thấy!).

Hi vọng cách làm này sẽ làm bạn hài lòng!

 

[tranan76]

Cảm ơn bạn Cường đã nhiệt tình đọc và trả lời mình. các vấn đề bạn gợi ý thì tôi đã giải quyết được trong file gửi kèm rồi Cũng dùng EVALUATE í mà. Chỉ có một điều mà tôi phải hỏi trên đây là (xin được nhắc lại)
mỗi khi tôi gõ diễn giải vào cột E thì nó tự động ghi công thức =tongkl(Ex:Ex) (x thay cho số dòng). vào cột G của từng công việc.
Như vậy nếu tôi nhập lần lượt thì không sao. Giả sử tôi đang nhập đến cột E12, lúc đó công thức tại G7 tự động là =tongkl(E8:E12) là OK. Nhưng lúc này tôi muốn sửa lại ô E10, thay vì giữ nguyên công thức tại G7 tự động là =tongkl(E8:E12) thì nó lại bị là =tongkl(E8:E10). Thế tôi mới phải hỏi xem bạn có cách nào không xử lý để nó biết là E10 nằm trong (E8:E12) và giữ nguyên công thức cũ . Chứ không sau khi tôi sửa tại E10 lại phải quay lại E12 ấn F2 rồi Enter (Vấn đề chính là đây).
Chúc bạn luôn vui vẻ, nhiều may mắn.

P/s: Excell làm được nhiều việc rồi nhưng có phải cứ copy và dán hoặc kéo và thả là xong đâu. Muôn tự động hóa cần phải có VBA nữa bạn à.

 

[Đào Việt Cường]

Dear tranan76,
---------------
Hị hị... đừng bắt mình phải Alt+F11 mà!
Vấn đề của bạn mình đoán nguyên nhân là do bạn sử lý biến cố chưa tốt thôi.
Bạn xem lại đi, cái này mình đã nhắc bạn rồi mà, giả sử tại cell E30 mình lập công thức để tính toán thử: =355/113+100/81 vậy mà thủ tục Worksheet_Change của bạn lại sửa thành 4,37616082158855 = 4,376.
Điều này không thể chấp nhận được vì E30 ngoài vùng tính toán, nó không phải chịu tác động bởi VBA được.

Nếu bạn vẫn muốn giải quyết theo hướng này thì mình lưu ý bạn vấn đề thế này vậy:
1. Khi sử dụng biến cố Worksheet_Change, điều đầu tiên bạn quan tâm đến thủ tục này là sự kiện xảy ra tại Cell nào. Đương nhiên là Target rồi, nhưng không phải bất cứ Target nào bị "Change" là bạn cho thi hành lệnh bên dưới. Vì vậy bạn cần phải kiểm tra Target xem có thuộc phạm vi để cho phép tính toán Khối lượng hay không.
2. Khi Target tại ô tính tongkl tức là bạn đang sửa tham chiếu cho hàm của bạn và việc đúng hay sai tham chiếu là do ý đồ của bạn thôi. Trường hợp này bạn chỉ cần chắc chắn tham chiếu đúng đến vùng tính tổng là được.
Quan trọng là trường hợp thay đổi giá trị của một công việc nào đó trong hạng mục!? Khi đó hàm tongkl sẽ tự động tính lại kết quả dựa vào tham chiếu hiện thời. Nếu tham chiếu trong hàm tongkl được bảo toàn thì mọi việc đều suôn sẻ, nhưng do trong thủ tục Worksheet_Change bạn đã thiết lập lại Formula mà trong đó tham chiếu không còn chính xác nữa. Khắc phục nhược điểm này, bạn chỉ cần... xoá quách Sub Worksheet_Change đi là ổn. Nói cụ thể hơn thì thủ tục này gần như vô nghĩa bởi vì khi bạn sử dụng công thức trên Excel thì công thức đó luôn luôn thực hiện khi có sự thay đổi, trừ phi bạn tắt tính năng AutoCalculate đi.

Như vậy là cũng không cần phải giải thích code nữa chứ ạ!

 

[tranan76]

Bạn Cường vẫn chưa đọc kỹ hết bài của mình
1. Bạn nhập ở cột E không được có dấu "=" ở trước
VD 355/113+100/81 chứ không được nhập là =355/113+100/81
2. hiện tại biến cố Worksheet_Change của mình làm 2 việc
a. tính toán và viết vào cột e dòng 355/113+100/81 = 4,376
b. viết công thưc =tongkl(Ex:Ex) vào cột G.

Nếu nói như bạn thì công việc b mình tự gõ vào, mà không cho tự động thì mới giải quyết được. vậy thì với mỗi công tác mình đều phải tự gõ công thức vào cột G à? Không copy được vì có khi công việc 1 là = tongkl(E8:E12) mà công việc 2 lại là =tongkl(E14:E30).

Thực ra mình đã có cách giải quyết nhưng muốn hỏi các cao thủ xem có cách nào hay hơn không.

 

[Đào Việt Cường]

Dear tranan76,
--------------
Mình không nghĩ với bài toán này nhu cầu tự động hoá của bạn lại cao đến vậy! Nhưng mà không sao, đành phải Alt + F11 ra vậy:

Sub Worksheet_Change(ByVal Target As Range)
Dim chuoi As String
Dim cot, hang As Long
'Dim hang As Double
'Dim cot As Long
On Error GoTo cuoi
If Cells(Target.Row, Target.Column - 1).Value = "" Then
    
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        On Error Resume Next:       Application.EnableEvents = False
        chuoi = Target.Value
        Target = TKL(chuoi)
        Target.Select
        chuoi = Target.Value
        With ActiveCell.Characters(Start:=1, Length:=Len(chuoi)).Font
        .ColorIndex = 1
        End With
        If vitri("=", chuoi) > 0 Then
        With ActiveCell.Characters(Start:=vitri("=", chuoi) + 1, Length:=Len(chuoi) - vitri("=", chuoi)).Font
        .ColorIndex = 5
        End With
        Else
        With ActiveCell.Characters(Start:=vitri("=", chuoi) + 1, Length:=Len(chuoi) - vitri("=", chuoi)).Font
        .ColorIndex = 1
        End With
        End If
    Cells(timnguoc("", 4, Target.Row), 7).Select
    ActiveCell.Formula = "=tongkl(E" & timnguoc("", 4, Target.Row) + 1 & ":E" & [B]Target.Row[/B] & ")"
 Else
 End If
    Cells(Target.Row + 1, Target.Column).Select
      Application.EnableEvents = True
        On Error GoTo 0:                End If
    
    
cuoi:
 End Sub

Mình Debug qua thì thấy rằng giải thuật của bạn chưa được hoàn hảo lắm:
Khi bạn sửa công thức tính đơn giá của công việc thì vùng cần tính tổng là vùng bao gồm toàn bộ các công việc chứ không phải là từ công việc đầu tiên đến công hiện hành (đang sửa). Dòng lệnh dưới đây chỉ dõ điều đó:

ActiveCell.Formula = "=tongkl(E" & timnguoc("", 4, Target.Row) + 1 & ":E" & Target.Row & ")"

Để sửa lại, theo mình có 2 giải pháp:
- Định trước địa chỉ vùng cần tính tổng khối lượng cho hạnh mục rồi sử dụng tham chiếu đó cho tongkl - cái này chẳng khác gì làm tay, không ổn
- Tạo nhóm vụ việc theo hạng mục, xây dựng thủ tục để tự động xác định vùng tính tổng theo nhóm mà có chứa công việc đang được sửa.
Xác định được hướng làm rồi, mình nghĩ viết code không khó!