Dùng CUT-PASTE làm sai hàm trong ô

[voda]

Trước hết, xin cảm ơn các bạn đã nhiệt tình góp ý .Điều này rất bổ ích đối với mình. Mong sự tiếp tục góp ý của dear all.

Nguyên văn bởi Đào Việt Cường
Thế này nhé, bạn có một công thức tính điểm trung bình ba môn học của một học sinh, giả sử: = SUM(A1:C1)/3
Vì sơ ý hoặc cũng có thể là cố tình của người khác, hành động Cut/Paste một ô nào đó (Ví dụ là A2) vào A1 làm thay đổi hẳn kết quả của phép tính. Trong trường hợp này nếu Excel không thông báo cho bạn biết số liệu đã được "ghi đè" bởi giá trị khác, bạn sẽ vẫn đinh linh =SUM(A1:C1)/3 là một kết quả đúng - điều đó thật tai hại.

-Khi ta cut ô A2 vào A1, thì giá trị của ô A2 sẽ được ghi đè lên ô A1. Như thế ô A1 sẽ nhận giá trị mới của ô A2. Giá trị các ô tham chiếu thay đổi dẫn đến kết quả thay đổi là điều tất nhiên. Tại sao phải báo lỗi? Và Excel không chỉ báo lỗi mà còn làm biến dạng công thức và hàm trong ô :
Ví dụ: =(A1+B1+C1)/3 trở thành =(#REF+B1+C1)/3 làm hỏng chương trình!

Nguyên văn bởi Đào Việt Cường
Chưa kể rằng, theo logic cell chứa công thức có tham chiếu tới A2 nếu không báo lỗi thì nó vẫn sử dụng A2 làm tham chiếu chứ không phải A1 - điều này cũng rất tệ hại.

Điều tệ hại này đã xãy ra khi ta cut ô A1 sang ô A2.
Công thức =(A1+B1+C1)/3 trở thành =(A2+B1+C1)/3
Trường hợp này Excel lại không báo lỗi.

Nguyên văn bởi Đào Việt Cường
...em thấy mặc định khi sheet đã được Protect thì Excel đã ngăn cấm hành động Cut,Copy rồi.

Mình dùng Format/cells/protection để chọn vùng bào vệ ( Tất cả các vùng trừ vùng nhập dữ liệu của người dùng)
Sau đó vào Tools/protection/protect sheet tạo mật khẩu để protect.
Như thế trong vùng nhập dữ liệu không ngăn cấm hành động Cut/Copy.

Mình gởi kèm file để cụ thể hoá những khó khăn đã gặp. Mình nghĩ bất kỳ chương trình tính toán nào cũng có thể gặp phải điều này. Mong muốn các bạn chỉ cho hướng khắc phục.

 

[Mr Okebab]

Mình dùng Format/cells/protection để chọn vùng bào vệ ( Tất cả các vùng trừ vùng nhập dữ liệu của người dùng)
Sau đó vào Tools/protection/protect sheet tạo mật khẩu để protect.
Như thế trong vùng nhập dữ liệu không ngăn cấm hành động Cut/Copy.

Thứ nhất : File của bạn có Macrosheet
Thứ hai : Đúng như bạn nói, trong vùng nhập liệu bạn có toàn quyền làm việc với các cell, kể cả cut, copy . .
Mà vùng kết quả lại phụ thuộc vào vùng nhập liệu. Do đó để tránh lỗi xảy ra (đương nhiên khi địa chỉ tham chiếu thay đổi) thì bạn nên có giải pháp chống việc cut, copy . . .
VD như :
tại Sheet 1 :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub
. .. . . .

Hoặc tất cả các công thức ta dùng VBA, sau khi ta nhấn tính toán thì cột O mới bắt đầu tính toán và cho ra giá trị

Đây chỉ là một gợi ý. Dù sao thì việc bảo vệ của 1 sheet không cho phép qui định đến từng chi tiết nhỏ của cell (VD như cấm copy, cấm di chuyển . . )

Thân!

 

[Tuấn Giang]

@ Đào Việt Cường:

Em lại vẫn không đồng ý với bác về điều này:

Nguyên văn bởi Đào Việt Cường
Thế này nhé, bạn có một công thức tính điểm trung bình ba môn học của một học sinh, giả sử: = SUM(A1:C1)/3
Vì sơ ý hoặc cũng có thể là cố tình của người khác, hành động Cut/Paste một ô nào đó (Ví dụ là A2) vào A1 làm thay đổi hẳn kết quả của phép tính. Trong trường hợp này nếu Excel không thông báo cho bạn biết số liệu đã được "ghi đè" bởi giá trị khác, bạn sẽ vẫn đinh linh =SUM(A1:C1)/3 là một kết quả đúng - điều đó thật tai hại.

Chúng ta nên đọc kỹ phần thông báo của Excel. Ở đây em đã nhấn mạnh, nếu ta Cut/Paste 1 ô dữ liệu thì không báo lỗi. (Chỉ lỗi với hàm If), nhưng khi Cut/Paste một vùng dữ liệu thì nó lại báo lỗi. Cái chúng ta cần bàn là tại sao 1 ô dữ liệu và một vùng dữ liệu khi Cut/Paste lại có sự khác nhau?

Em đã gặp trường hợp như thế này: Em có một sheet dữ liệu, sau đó em muốn copy một vùng từ A3:B10 và Paste vào một sheet khác. Nhưng điều xảy ra là sau khi Paste, vùng chọn của em không phải là Từ A3:B10 mà nó Bold luôn từ A3:C10, (và tương tự nếu em dùng lệnh Cut/Paste, và điều này không xảy ra nếu ta Copy, Cut/Paste một cell riêng lẻ).

Thật sự thì em đã không giải thích được điều này và đành chấp nhận nó như là bản chất của Excel. Mong các bác xem xét lại.

Best Regards,

 

[thanhtri]

Cái vụ này lúc trước em gặp hoài mà ko đễ ý. Số là em cũng có 1 bảng tính có 1 vài vùng đã protect, những vùng khác cho người ta toàn quyền nhập liệu. Nhưng ác 1 cái là đôi khi người ta hay dùng chế độ "Freeze Panes" đễ cố định dòng cột. Đáng lý ra ô đầu tiên cần nhập là ô D4 chẳng hạn, nhưng người ta đã kéo bảng tính xuống tới D20 rồi mà vẫn ko biết. Nhập xong 1 lúc, phát hiên ra thì phải làm sao? Chắc chắn họ sẽ bôi đen cả vùng dữ liệu vừa nhập rồi kéo đến đúng ô D4 (cũng xem như là cut-paste rồi) Thế là xuất hiện cái thằng ông nội #REF.
Theo như anh Hiếu, dùng Macro đễ chống việc Cut-Paste cũng hay. Nhưng trong tình huống em vừa nêu ở trên thì người ta phải làm sao? Nhập lại toàn bộ a? Quá cay!
Miss TT

 

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

Dear all,
--------
Làm bất cứ điều gì cũng phải cẩn thận, những sai sót không thể không tránh khỏi nhưng hạn chế tối đa những sai sót đó ngay từ ban đầu thì các phiền phức gặp phải sẽ ít hơn.
Excel là một bảng tính điện tử được coi là công cụ tính toán năng động nhưng ngược lại nó không tạo ra các ràng buộc quan hệ giữa các đối tượng tính toán như các hệ quản trị cơ sở dữ liệu. Nói chung là nó có tính hai mặt và người dùng phải biết khai thác những mặt tích cực, hạn chế mặt tiêu cực.
Trong trường hợp này cứ cho rằng "cái thằng ông nội #FEF" (nguyên văn bởi thanhtri) là mặt thứ hai. Vậy phải hạn chế "cái thằng" đó như thế nào? Cách mà Mr Okebab đã nêu là một gợi ý hữu ích. Nhưng đây chỉ gợi ý thôi và để xử lý thì dựa vào đó có thể thực hiện theo nhiều cách khác nhau (chúng ta có thể dựa vào Application.CutCopyMode để xem xét hành động đang là xlCopy, xlCut hay là không từ đó mới đưa ra phương án ngăn chặn thích hợp. Tuy nhiên, phải nhấn mạnh rằng dù chúng ta có ngăn chặn được hành vi cut/paste tùy tiện của người dung thì cũng không thể ngăn chặn được sai sót do số liệu nhập sai. Mà hậu quả của sai sót này có thể còn nghiêm trọng hơn hậu quả của #REF.
Theo em, để hạn chế những rắc rối đã đề cập, tốt hơn hết là cho phép người dùng nhập liệu thông qua màn hình đơn (Data Form hoặc User Form) chứ không cho họ trực tiếp can thiệp trên bảng dữ liệu. Nếu việc nhập liệu trực tiếp trên bảng dữ liệu là bắt buộc và chúng ta cho phép họ "tùy cơ hành sự" thì cần phải kiểm tra tức thời giá trị hiện hành mà họ đưa vào - kể cả qua con đường Copy, Cut hay Entry. Bước tiếp theo là thiết lập lại công thức để đảm bảo Formula lúc nào cũng "tươi roi rói".
Hi vọng ý kiến đóng góp trên có ích!

 

[Mr Okebab]

Cái vụ này lúc trước em gặp hoài mà ko đễ ý. Số là em cũng có 1 bảng tính có 1 vài vùng đã protect, những vùng khác cho người ta toàn quyền nhập liệu. Nhưng ác 1 cái là đôi khi người ta hay dùng chế độ "Freeze Panes" đễ cố định dòng cột. Đáng lý ra ô đầu tiên cần nhập là ô D4 chẳng hạn, nhưng người ta đã kéo bảng tính xuống tới D20 rồi mà vẫn ko biết. Nhập xong 1 lúc, phát hiên ra thì phải làm sao? Chắc chắn họ sẽ bôi đen cả vùng dữ liệu vừa nhập rồi kéo đến đúng ô D4 (cũng xem như là cut-paste rồi) Thế là xuất hiện cái thằng ông nội #REF.
Theo như anh Hiếu, dùng Macro đễ chống việc Cut-Paste cũng hay. Nhưng trong tình huống em vừa nêu ở trên thì người ta phải làm sao? Nhập lại toàn bộ a? Quá cay!
Miss TT

Tất cả các công thức nếu liêu quan đến phần không được Protect đều phải được ghi lại bằng VBA

VD : Range("D6").FormulaR1C1 = "=PRODUCT(R[-1]C[-1]:R[2]C[-1])"
(Tất nhiên D6 phải được khóa)

Sau đó mặc cho ai di chuyển các ô thì khi nhập liệu xong, các công thức mới được nhập vào và tính toán

Thân!

 

[voda]

Nhờ các bạn mà mình sáng ra nhiều vấn đề. Có thể tóm tắt như sau:
1. Về lệnh CUT các ô là vùng tham chiếu của hàm:
-Trường hợp 1: Ta CUT ô ở ngoài vào ô tham chiếu (tạm gọi là CUT vào).
Kết quả: xuất hiện lỗi #REF
Lý do: Excel quan niệm ô tham chiếu ấy không còn tồn tại.
Điều này gây khó khăn cho người dùng vì trên thực tế ô tham chiếu ấy vẫn tồn tại.
-Trường hợp 2: Ta CUT ô tham chiếu ra 1 ô khác ở ngoài (tạm gọi là CUT ra)
Kết quả: Không xuất hiện lỗi. Trong hàm, ô tham chiếu có địa chỉ mới ( là ô mới dời đến)
Lý do: Excel rất thông minh. nghĩ ta dời vị trí của ô nên hàm dời theo.
Điều này vừa tạo thuận lợi, vừa gây khó khăn cho người dùng. Thuận lợi khi ta chuyển vị trí ô. Khó khăn khi ta chỉ chuyển giá trị của ô.
2.Vậy thì vấn đề đặt ra là gì?
Vấn đề không phải là: -Excel bị lỗi hay là không?
-Lệnh CUT có lợi hay hại?
Theo mình, chúng ta đối mặt với vấn đề lớn hơn: đó là vấn đề bảo vệ, bảo mật. Một chương trình được tạo ra, không có sai sót, được protect hẳn hoi thế mà có thể bị "crack" bất cứ lúc nào, với bất kỳ người dùng nào miễn là họ chỉ cần biết dùng lệnh CUT!
3.Hướng khắc phục:
Mình đã dùng nhiều cách:
-Biến địa chỉ ô tham chiếu thành địa chỉ tuyệt đối : không có kết quả
-Dùng Define/name đặt tên cho ô , vùng tham chiếu: chẳng ăn thua gì.
-Cảnh báo với người sử dụng: không an toàn.
Mình nghĩ chỉ còn 1 cách là:

Nguyên văn bởi Mr Okebab
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub
. .. . . .
Hoặc tất cả các công thức ta dùng VBA, sau khi ta nhấn tính toán thì cột O mới bắt đầu tính toán và cho ra giá trị
Đây chỉ là một gợi ý. Dù sao thì việc bảo vệ của 1 sheet không cho phép qui định đến từng chi tiết nhỏ của cell (VD như cấm copy, cấm di chuyển . . )

-Bạn nói là chỉ gợi ý nhưng theo mình đây là cách duy nhất để khắc phục tình trạng bảo mật kém của Excel: dùng VBA.
Xin chân thành cám ơn các bạn.

 

[thanhtri]

Còn ko nữa thì làm cách nào đó cho người ta CUT ko dc, COPY thì OK, vậy là ổn rồi (Vì theo như em biết thì COPY chưa thấy xuất hiện bất cứ lỗi gì cả). Vậy thì trong trường hợp em đã nêu ở trên, người ta sẽ bôi đen vùng dử liệu vừa nhập, copy đến cell D4, phần dư thừa phía dưới thì xóa đi. Thế là xong. Hiii
Cảnh báo cho người dùng là điều ko tưởng. Vì theo như em nói: "Một chương trình tốt nhất phải là chương trình có thể phục vụ cho những người NGU nhất và những người LƯỜI nhất 1 cách ổn định nhất". Đúng ko? Họ ko biết (hay giã vờ ko biết) thì phải làm sao?
Còn nữa, theo như bạn Tuan Giang thử nghiệm thì thấy có 1 vài hàm xuất hiện lỗi khi dùng lệnh CUT, còn 1 vài hàm thì ko. Vậy khi làm chương trình mình hảy khéo léo chọn lựa hàm là dc rồi. Em thí nghiệm sơ bộ, thấy rằng thay vì cộng 2 cell A1 và B1 bằng cách C1= A1 + B1, mình có thể dùng C1= SUM (A1:B1). Hàm SUM này ko bị lỗi khi CUT.
Tất nhiên đây chỉ là những giãi pháp tạm thời trước khi tìm dc 1 giãi pháp hửu hiệu, nhưng it ra cũng có thể áp dụng dc. (Em vẫn thích cách cuối cùng nhất vì những người dùng ngu nhu em dù có cố tình hay vô ý CUT... BỤP... BỤP... BỤP... cũng chẳng xi nhê gi) Heeeeeeeeeeeee
Miss TT

 

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

Điều này gây khó khăn cho người dùng vì trên thực tế ô tham chiếu ấy vẫn tồn tại.

Dear voda,
----------
Em thực sự vẫn chưa thoả mãn về cách đánh giá này. Nhưng mà thôi, vấn đề cuối cùng vẫn là ngăn chặn tới cùng những con người... cẩu thả, trong khi chương trình của mình rất ngon lành.

Em đưa ra hai giải pháp hi vọng hạn chế tối đa rắc rối đã nêu:

Giải pháp 1: Sử dụng màn hình đơn Data Form hoặc User Form
Với giải pháp này, người dùng không thể trực tiếp can thiệp vào cơ sở dữ liệu. Đây là điều kiện tiên quyết nếu nhất thiết không cho họ quyền chỉnh sửa cấu trúc dữ liệu (việc di rời một phần của bản ghi này qua một bản ghi khác được coi là thay đổi cấu trúc dữ liệu). Chúng ta cũng có thể tạo ra loại màn hình thứ 3 thiết kế trên sheet nhưng cách này mất nhiều công lập trình hơn, tạm thời không đề cập đến
File đính kèm minh hoạ cách thức ứng cả hai màn hình nhập liệu. Với User form, chúng ta nên tạo 1 Add in sao cho ứng dụng dùng chung trong mọi trường hợp. AddIn này em đã có dịp giới thiệu trên diễn đàn, chỉ cần tải về và bổ sung các mã lệnh cần thiết là hoàn thành ứng dụng.
Giải pháp 2: Làm tươi công thức tại thời điểm cần thiết
Cách này có vẻ đơn giản hơn, ít phải thiết kế hơn. Song nếu ứng dụng có sử dụng các biến cố Worksheet_Change, Worksheet_SelectionChange() sẽ làm giảm hiệu năng hoạt động của chương trình.
Thiết nghĩ giải thuật và mã lệnh không cần nhắc tới nữa, Mr Okebab đã đề cập đến rồi

(Trong file đính kèm có hiệu chỉnh tiêu đề bảng và cắt giảm tối đa những gì không cần thiết. Đây cũng là một yếu tố quan trọng của một ứng dụng Excel luôn đề cao tính kỹ thuật hơn hình thức)