Đăng ký học Excel và VBA cùng GPE tháng 10 - TPHCM

Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

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

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi voda, 12 Tháng năm 2007.

  1. voda

    voda Thành viên danh dự

    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.
     
  2. anhphuong

    anhphuong Thân Thương

    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
     
  3. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    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!
     
    Lần chỉnh sửa cuối: 12 Tháng năm 2007
  4. voda

    voda Thành viên danh dự

    -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.
     
  5. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    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!
     
  6. Tuấn Giang

    Tuấn Giang Thành viên chính thức

    @ 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,
     
  7. voda

    voda Thành viên danh dự

    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 đỡ.
     
  8. Tuấn Giang

    Tuấn Giang Thành viên chính thức

    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,
     
  9. thanhtri

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

    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
     
  10. voda

    voda Thành viên danh dự

    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.
     
  11. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    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!
     
    Lần chỉnh sửa cuối: 15 Tháng năm 2007
  12. Tuấn Giang

    Tuấn Giang Thành viên chính thức

    @ 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:

    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.

    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,
     
    Lần chỉnh sửa cuối: 15 Tháng năm 2007
  13. voda

    voda Thành viên danh dự

    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!
    Đâ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.
     
  14. Tuấn Giang

    Tuấn Giang Thành viên chính thức

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

    Đã 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,
     
    Lần chỉnh sửa cuối: 15 Tháng năm 2007
  15. voda

    voda Thành viên danh dự

    -Nhờ bạn thử lại bằng những hàm cụ thể sau đây:
    =SUM(A1:B1)+SUM(C1:D1) Thực hiện lệnh CUT với vùng dữ liệu (A1:B1)(C1:D1).
    =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?
     
  16. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    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!
     
  17. voda

    voda Thành viên danh dự

    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.
     
  18. Tuấn Giang

    Tuấn Giang Thành viên chính thức

    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(A1:D1), 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(C1:D1) thì nó báo lỗi. Trong hướng dẫn thì chỉ nói:

    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,
     
    Lần chỉnh sửa cuối: 15 Tháng năm 2007
  19. thanhtri

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

    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
     
  20. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    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ó.
    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 ạ!
     

Chia sẻ trang này