Dùng CUT-PASTE làm sai hàm trong ô (1 người xem)

[voda]

Chào các bạn!
Trong khi dùng Excel để tính toán, tôi gặp trường hợp sau:
-Khi dùng lệnh cut-paste trong vùng dữ liệu tham chiếu của hàm thì hàm bị lệch đi dù cho sheet đã protect. Cụ thể như sau:
=ROUND((SUM(E23:M23)+SUM(#REF!))/(COUNT(E23:M23)+COUNT(#REF!)),1)
-Tôi khắc phục bằng cách là không dùng cut-paste nữa.
Có ai gặp tình trạng này chưa và chỉ dùm tôi cách giải quyết.
Cám ơn các bạn rất nhiều.

 

[anhphuong]

1/ Bạn thử dùng lệnh Paste Special/Formulas xem thế nào
2/ Dùng địa chỉ tuyệt đối vì khi Paste với địa chỉ tương đối thì các tham chiếu thường hay bị sai về nội dung trong đó nên nó không xác định đươc và báo lỗi
3/ Hình như post bài này ở đây không đúng địa chỉ thì phải(Xin lỗi BQT và voda nha nếu không đúng)
Thân
Thân

 

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

Dear all,
--------

- Lệnh PasteSpecial mất hiệu lực nếu chúng ta thực hiện lệnh Cut.

- #REF cho chúng ta biết địa chỉ ô không có thực, trong trường hợp bạn di chuyển cell (cut/paste) thì địa chỉ ô luôn luôn đảm bảo là có thực, không thể xảy ra #REF được. Theo mình phỏng đoán bạn đã thực hiện thao tác Copy/Paste và độ chuyển vị của ô sao chép vượt quá giới hạn, ví dụ:
Tại ô D1 bạn có công thức: =A1, giờ bạn copy công thức này vào ô A2, lỗi #REF xảy ra do Excel không xác định được tham chiếu trước cột A.
- Nhiều bài viết tác giả tưởng là mất nhưng thực ra nó nằm ở đâu chính tác giả cũng không biết. Các bạn chú ý trước khi đặt đề tài sẽ giúp cho diễn đàn gọn gàng ngăn nắp hơn!

 

[voda]

-Cảm ơn ý kiến của các bạn.
-Vì mình trình bày không rõ nên các bạn chưa nắm hết ý của mình. Mình xin trình bày lại:
-Vấn đề ở đây không phải là sao chép công thức từ ô này sang ô khác mà là sao chép dữ liệu (cut-paste) trong ô hoặc vùng tham chiếu của hàm.( Chứ không phải là ô chứa hàm).
-Ví dụ: A1=3, B1=5, ô C1 chứa công thức:=A1+B1. Khi ta CUT dữ liệu từ 1 ô bất kỳ ( kể cả ô trống) vào ô A1 hoặc B1 thì công thức ở ô C1 bị báo lỗi: =A1+#REF.(Ngay khi ô C1 đã được protect)
-Điều này rất bất tiện với những chương trình có người dùng rộng rãi. Khi lỗi xảy ra, họ không biết cách xử lý và nhất là đối với những sheet đã protect.(Không thể mở để chỉnh lại hàm).
-Mình xin lỗi vì post nhầm topic. Lần sau sẽ cẩn thận hơn.

 

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

Dear voda,
----------
Không biết công việc này bạn có phải làm thường xuyên không, để đơn giản chúng ta thực hiện theo hai cách:
1. Trước khi cut, bạn chỉnh lại tham chiếu trong công thức, ví dụ ô C3 là ô sẽ thay thế ô A1, công thức hiệu chỉnh thành: = C3+B3
2. Thay vì cut Cell bạn copy Cell để chèn dữ liệu thay thế rồi xóa Cell thay thế đi (coi như là sự di chuyển giá trị chứ không phải di chuyển Cell). Nếu Cell di chuyển là một công thức thì bạn phải copy công thức đó trong chế độ Edit để đảm bảo công thức không bị thay đổi do sử dụng tham số tương đối.
Các cách trên chỉ áp dụng đối với trường hợp thủ công và không thường xuyên. Nếu cần thiết phải làm tự động thì đành phải tạo thủ tục VBA vậy, giải thuật như trên.
Chúc bạn thành công!

 

[Tuấn Giang]

@ Voda:

Đâu có nhỉ? Em vẫn làm được bình thường mà? Kể cả ô khác có dữ liệu hay rỗng, em CUT-PASTE, kết quả vẫn cho ra bình thường, đâu có báo lỗi như của bác nhỉ?

Best Regards,

 

[voda]

Đâu có nhỉ? Em vẫn làm được bình thường mà?

Bạn dùng phiên bản nào? Mình dùng Excel 2003.

Không biết công việc này bạn có phải làm thường xuyên không,

Công việc này mình không làm. Mình gặp khó khăn trong trường hợp sau đây:
-Mình làm một chương trình cọng điểm, xếp loại học sinh cho giáo viên sử dụng. Tất cả hàm và công thức được protect (nghĩa là không cho phép đọc và chỉnh sửa.) Vùng dữ liệu thì không khoá (vì để nhập điểm số vào). Trong quá trình sử dụng, khi nhập dữ liệu vào, người dùng có thể dùng các lệnh về sao chép. Họ dùng copy thì không sao. Nhưng khi dùng lệnh cut thì các ô chứa hàm (thể hiện kết quả việc tính toán )bị lệch như mình đã trình bày.
-Điều này nói lên một điều là: Cái khoá của Excel không an toàn. Và phải cảnh giác với lệnh CUT.
-Có cách gì khi người sử dụng dùng lệnh CUT mà hàm vẫn giữ nguyên (để việc tính toán đúng)không?
Mong các bạn giúp đỡ.

 

[Tuấn Giang]

Bạn dùng phiên bản nào? Mình dùng Excel 2003.

Công việc này mình không làm. Mình gặp khó khăn trong trường hợp sau đây:
-Mình làm một chương trình cọng điểm, xếp loại học sinh cho giáo viên sử dụng. Tất cả hàm và công thức được protect (nghĩa là không cho phép đọc và chỉnh sửa.) Vùng dữ liệu thì không khoá (vì để nhập điểm số vào). Trong quá trình sử dụng, khi nhập dữ liệu vào, người dùng có thể dùng các lệnh về sao chép. Họ dùng copy thì không sao. Nhưng khi dùng lệnh cut thì các ô chứa hàm (thể hiện kết quả việc tính toán )bị lệch như mình đã trình bày.
-Điều này nói lên một điều là: Cái khoá của Excel không an toàn. Và phải cảnh giác với lệnh CUT.
-Có cách gì khi người sử dụng dùng lệnh CUT mà hàm vẫn giữ nguyên (để việc tính toán đúng)không?
Mong các bạn giúp đỡ.

Em dùng 2003 luôn. Đã thử đi thử lại không vấn đề gì xảy ra hết. Kể cả việc để dữ liệu trong ô định cut là dạng text, khi cut - paste, ô kết quả vẫn cho kết quả (nó không cộng ô dạng text kia vào kết quả).

Bác xem lại thư coi sao nhé.

Best Regards,

 

[thanhtri]

Lổi này hình như là... "bó tay". Bản office nào cũng thế, dù là office2000, 2002, 2003 hay thậm chí là office2007 cũng gặp lỗi này. Có lẽ là do 1 tính năng của Office. Đễ ý sẽ thấy rằng: Nếu A1=5, B1=6, C1=A1+B1 thì khi ta dời cell A1 hoặc cell B1 đi đâu, cell C1 cũng "đuổi theo". VD: ta cut cell A1 và paste vào cell H5 thì C1 sẽ= H5+B1
Miss TT

 

[voda]

Tóm lại, có 2 trường hợp các bạn ạ!:
-Khi ta cut 1 ô bên ngoài vào ô tham chiếu của hàm thì bị báo lỗi.
-Khi ta cut ô tham chiếu của hàm đến 1 ô khác thì hàm bị thay đổi (chạy theo ô đích) như ví dụ của bạn thanhtri.
Điều này sẽ dẫn đến hậu quả:
-Ta có thể cố tình làm sai lệch công thức và hàm của 1 chương trình tính toán dù chương trình ấy được khoá nghiêm chỉnh.
-Ta có thể vô tình làm sai lệch công thức và hàm, làm cho kết quả tính toán bị sai mà không hề hay biết. Điều này thật tai hại.

Nếu có trường hợp dùng Cut mà hàm không bị lệch như bạn Tuấn Giang thì ta có thể tắt tính năng khó chịu này đi. Đó là điều mình muốn nhờ các bạn giúp đỡ.
Cám ơn các bạn rất nhiều.

 

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

Dear all,
--------
Theo em đây không thể được coi là một lỗi của Excel mà người dùng cố tìm cách khắc phục. Thay thế cell này bởi cell khác thì cell bị thay thế đương nhiên mất đi. Mặc dù chúng ta không nhận thấy sự thay thế này, (đặc biệt nếu chỉ dựa vào quan sát bằng mặt thường mà suy luận khi cut/paste cell thì cell bị thay thế vẫn giữ nguyên) nhưng sự thật là có sự thay đổi hoàn toàn giữa cell bị thay thế với cell được thay thế.
Hình dung như chúng ta sao chép một tập tin đến một thư mục khác, nếu tập tin đó đã tồn tại thì Windows sẽ yêu cầu chúng ta xác nhận có ghi đè thay thế hay không). Nếu chúng ta xác nhận ghi đè thì tập tin đang tồn tại mất đi, được thay thế bằng tập tin đã copy. Cut/Paste cell cũng giống như thế. Sự thay thế dễ nhận thấy nhất là toàn bộ các thuộc tính tại địa chỉ đó là thuộc tính của Cell được cut (được thay thế). Hay nói cách khác một cell này đã được thay thế hoàn toàn bằng một cell kia mà "cell kia" đã bị xoá khỏi bộ nhớ, chỉ tồn tại duy nhất 1 cell. Điều này là hiển nhiên, nếu cell bị thay thế vẫn còn tồn tại mới là phiền phức đối với người dùng Excell.
Chúng ta cũng phân biệt quá trình cut/Paste Cell với quá trình di chuyển cell (cut/Insert cut cells). Quá trình cut/Insert cut cells không có sự thay thế mà đơn thuần chỉ là sự sắp xếp, bố trí lại vị trí của cell trên bảng tính.
Chúng ta cũng phân biệt với quá trình copy/paste cell, quá trình này thực chất là tạo ra bản sao (chứ không thay thế) từ cell được copy.
Vậy nên theo em, chúng ta không nên coi đây là một lỗi của Excel thì may ra giải pháp mới được hoàn hảo!

 

[Tuấn Giang]

@ All:

Em đã thử trên rất nhiều máy khác nhau và kết quả không có gì khác biệt. Có nghĩa là Excel không hề báo lỗi gì hết, vẫn tính toán kết quả chính xác.

@ daovietcuong:

Em không đồng ý với bác Cường ở những điểm này:

1. Hình dung như chúng ta sao chép một tập tin đến một thư mục khác, nếu tập tin đó đã tồn tại thì Windows sẽ yêu cầu chúng ta xác nhận có ghi đè thay thế hay không).

Chuyện này chỉ xảy ra khi chúng ta copy/paste file mới tại cùng thư mục với file gốc. Nếu anh lưu khác thư mục, Windows vẫn cho phép anh lưu cùng tên như file gốc.

2. Chúng ta cũng phân biệt quá trình cut/Paste Cell với quá trình di chuyển cell (cut/Insert cut cells). Quá trình cut/Insert cut cells không có sự thay thế mà đơn thuần chỉ là sự sắp xếp, bố trí lại vị trí của cell trên bảng tính.

Anh thử lại xem, trường hợp anh cop (cut)/paste 1 cell thay thế vào cell bị thay thế, Excel sẽ không hỏi mà đè ngay dữ liệu mới lên dữ liệu cũ. Nhưng nếu anh move một cell vào cell bị thay thế, Excel sẽ hỏi xem anh có xác nhận thay thế dữ liệu hay không.

Đây là đôi điều nhận xét của em. Mong chúng ta sẽ hiểu hơn về vấn đề này.

Best Regards,

 

[voda]

Nguyên văn bởi Đào Việt Cường
Vậy nên theo em, chúng ta không nên coi đây là một lỗi của Excel thì may ra giải pháp mới được hoàn hảo!

Theo mình nghĩ vấn đề không ở chỗ Excel có lỗi hay không.(Bởi vì khi tạo phần mềm, người lập trình chắc có ý đồ tạo ra 2 chức năng khác biệt là copy và cut). Vấn để là làm sao khắc phục cái nó tạo ra:
-Một chương trình đã khoá, lại có thể bị làm lệch đi mà người dùng không biết hoặc biết cũng không thể chỉnh sửa được. Điều này có thể gây ra những hậu quả nghiêm trọng khó lường.
Mình có thể khắc phục bằng cách cảnh báo đối với người dùng. Nhưng không chắc họ có tuân thủ hay không. Viết chương trình đã đau đầu rồi mà lại gánh thêm nỗi lo này nữa! Thật khổ phải không các bạn!

Nguyên văn bởi Tuấn giang
Em đã thử trên rất nhiều máy khác nhau và kết quả không có gì khác biệt. Có nghĩa là Excel không hề báo lỗi gì hết, vẫn tính toán kết quả chính xác.

Đây là điều rất lạ. Nếu không phải là lỗi mà là bản chất của Excel thì sao có lúc lại không bị gì hết. Mình nhờ bạn kiểm tra thêm nhất là ở các máy tình cờ không có quan hệ gì với nhau.
Xin chân thành cảm ơn tất cả các bạn.

 

[Tuấn Giang]

Em đã check lại và đã tìm ra được cái này:

Possible causes and solutions

- Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas
- Change the formulas, or restore the cells on the worksheet by clicking Undo immediately after you delete or paste the cells...

Đã thử qua các hàm: Sum, Subtotal, Vlookup, Hlookup, Match, Index, Sumif, Countif... Không sao cả. Chỉ có hàm IF (và có thể các hàm tương tự IF) thì mới báo lỗi hay sao ấy. Điều này có nghĩa rằng đây là thuộc tính của Excel, và chúng ta đừng bao h cố gắng thay đổi nó.

Best Regards,

 

[voda]

Nguyên văn bởi Tuấn Giang
Đã thử qua các hàm: Sum, Subtotal, Vlookup, Hlookup, Match, Index, Sumif, Countif... Không sao cả.

-Nhờ bạn thử lại bằng những hàm cụ thể sau đây:
=SUM(A1:B1)+SUM(C11) Thực hiện lệnh CUT với vùng dữ liệu (A1:B1)(C11).
=COUNTIF(A1:B1,C1) Thực hiện lệnh CUT đối với ô điều kiện là C1.
-Khi ta thực hiện các phép tính cọng trừ nhân chia trên cell : A1+B1...đều bị lệch cả.
-Dĩ nhiên nếu là thuộc tính của Excel thì ta cố gắng thay đổi làm gì. Nhưng như tôi đã trình bày ở trên, điều này đã gây ra những hậu quả thật nghiêm trọng đối với công việc của chúng ta, và ở điểm này thì liệu excel có còn là một công cụ tuyệt vời nữa hay không?

 

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

Dear all,
--------
Không phải là "thuộc tính" mà theo em nên gọi là "Bản chất" thì mới đúng vì đó là vấn đề thuộc về quy tắc - quan điểm phát triển và ứng dụng MS Excel. Những cái gì thuộc về quy luật thì chúng ta phải tuân theo chứ không nên bẻ gãy nó!
Em thấy Excel vẫn tuyệt vời vì giả sử rằng nếu như em thực hiện lệnh CUT A2:B2 chèn vào A1:B1 mà công thức =SUM(A1:B1) không báo lỗi thì những rắc rối sẽ lớn hơn gấp nhiều lần. (Em đoán Tuấn Gian chưa thực hiện đúng thao tác này nên không thấy lỗi phát sinh thôi, có thể mô tả thao tác của voda cũng chưa được cụ thể lắm!)
Vấn đề là chúng ta không muốn điều đó xảy ra, chúng ta bắt Excel phải tuân theo ý muốn của chúng ta - điều đó tạo nên điều tuyệt vời rồi. Trong bài toán này, hình như voda xây dựng một ứng dụng (có vẻ vô cùng "hoành tráng") theo đó môi trường ứng dụng nhiều người dùng được ưu tiên hàng đầu? Vấn đề gặp phải là phải kiểm soát hành động của người dùng trong việc thay đổi cấu trúc dữ liệu mà không tuân thủ quy tắc của chương trình. Điều này có thể cần phải xem xét chủ yếu về cách thức tổ chức cơ sở dữ liệu và thiết kế của chương trình. Biết đâu vấn đề này được giải quyết triệt để thì chúng ta khỏi cần quan tâm cách xử lý cái #REF làm gì nữa.
Nếu có thể chia sẻ, voda có thể gửi file và mô tả chi tiết yêu cầu ứng dụng, chắc mọi người sẽ nhanh chóng tìm ra hướng giải quyết cụ thể hơn!

 

[voda]

Nguyên văn bởi Đào Việt Cường
thực hiện lệnh CUT A2:B2 chèn vào A1:B1 mà công thức =SUM(A1:B1) không báo lỗi thì những rắc rối sẽ lớn hơn gấp nhiều lần.

Mình nghĩ ĐVC thử nêu ra một số rắc rối cụ thể.
Chứ trong thực tế mình lại thấy ngược lại.
-Có 1 chương trình tính toán mà công thức và hàm trong các ô được bảo vệ rất cẩn thận. ( Nghĩa là người tạo ra nó không muốn chương trình bị thay đổi.) Thế mà chỉ cần với lệnh CUT-PASTE, bất kỳ người dùng nào cũng có thể làm rối tung tất cả.
Mình nghĩ bằng lệnh CUT-PASTE, mình cũng có thể làm hỏng chương trình của các bạn dù các bạn đã bảo vệ nó.
-Chương trình của mình làm chẳng "hoành tráng" gì đâu. Chỉ là một file cọng điểm, xếp loại HS cuối học kỳ. Mình dùng các hàm sum, count, if ,round...và các phép tính cọng trừ nhân chia...Người dùng là giáo viên (khá đông).Cũng vì lệnh CUT-PASTE mà:
-Khi thì bị báo lỗi. (Không sử dụng được nữa)
-Khi thì điểm số, xếp loại bị lệch mà không hay biết. Điều này gây phiền hà rất lớn. ( Còn hơn việc tính toán nhầm phải bù tiền!)
-Nếu nó là bản chất rồi thì ta dùng macro vô hiệu hoá nó được không? Nhờ các bạn giỏi VBA chỉ giáo dùm.

 

[Tuấn Giang]

OK, em đã check theo công thức của bác, đúng là nó báo lỗi thật. Nhưng mà lạ! Nếu em để công thức là =sum(A11), khi CUT vùng khác paste vào thì nó không báo lỗi, nhưng nếu công thức =sum(A1:B1)+sum(C11) thì nó báo lỗi. Trong hướng dẫn thì chỉ nói:

Possible causes and solutions

- Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas

Thật khó hiểu quá!

Và nếu không có cách nào khắc phục thì đôi khi chỉ một sự vô tình chúng ta cũng có thể làm hỏng cả một chương trình.

Bác nào có ý kiến không ạ?

Best Regards,

 

[thanhtri]

Em nghĩ cái bạn voda quan tâm nhất chính là làm sao cho những cell đã protect phải "bất khả xâm phạm" cho dù mấy cái vụ cut-paste có là lỗi hay ko cũng ko quan trọng. Cái bạn ấy cần nhất là làm sao cho công thức mà bạn ấy đã Protect phải dc bảo toàn nguyên vẹn. Nếu người ta cố tình nhập liệu sai thì cùng lắm chỉ sai kết quả thôi chứ ko dc sai công thức. Có đúng thế ko? Em nghĩ các anh nên nghiên cứu theo hướng này thì hơn.
Miss TT

 

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

Mình nghĩ ĐVC thử nêu ra một số rắc rối cụ thể.
Chứ trong thực tế mình lại thấy ngược lại.

Dear voda,
----------
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:A3)/3 là một kết quả đúng - điều đó thật tai hại. 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.
Em thấy chủ đề này rất thú vị, vì chúng ta có dịp "mổ sẻ" Excel tới từng Cell! Theo sự hiểu biết có giới hạn của em thì một Worksheet được cấu tạo không phải đơn giản là các Cell ghép lại, cũng không đơn giản như là một trang được kẻ thành nhiều ô bởi các đường kẻ ngang dọc mà nó được cấu tạo bởi tập hợp Cells, trong đó mỗi Cell là một đối tượng. Mỗi đối tượng này có những thuộc tính chung như tập hợp của nó. Nhưng mỗi đối tượng lại có những đặc tính riêng để phân biệt Cell này với Cell.
Vì bảng tính là một tập hợp Cells bao gồm cố định 16.777.216 Cell(với Office 2007 thì nhiều hơn) nên chúng ta hiểu vì sao số lượng các Phần tử trong Tập hợp không hề thay đổi khi ta thêm, bớt Cell.
Những điều này chỉ nhằm lý giải rằng, mỗi mội Cell trên Worksheet tồn tại độc lập tuyệt đối với nhau và chúng có sự tồn tại vật lý thực sự. Chúng ta không thể dựa vào vị trí của Cell để xét đoán sự tồn tại của nó.

-Có 1 chương trình tính toán mà công thức và hàm trong các ô được bảo vệ rất cẩn thận.

Mức độ cẩn thận cần xem xét ở khía cạnh nào, em thấy mặc định khi sheet đã được Protect thì Excel đã ngăn cấm hành động Cut,Copy rồi.
Nên chăng để dễ hiểu voda cụ thể hoá bằng một file minh hoạ để mọi người ngâm cái cứu được không ạ!

 

[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)