Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống


Bạn có thể sử dụng các công thức sau trong Excel để bỏ qua các ô trống khi thực hiện các tính toán:

Công thức 1: Bỏ qua các ô trống trong một cột

=IF(A2<>"", A2+10, "")

Công thức cụ thể này thêm 10 vào giá trị trong ô A2 chỉ khi giá trị trong ô A2 không trống.A2 only if the value in cell A2 is not blank.

Công thức 2: Bỏ qua các ô trống trong nhiều cột

=IF(AND(A2<>"", B2<>""), A2+B2, "")

Công thức cụ thể này thêm các giá trị trong các ô A2 và B2 chỉ khi cả hai ô không trống.A2 and B2 only if both cells are not blank.

Các ví dụ sau đây cho thấy cách sử dụng từng công thức trong thực tế.

Ví dụ 1: Bỏ qua các ô trống trong một cột

Giả sử chúng ta có dữ liệu sau đây trong Excel cho thấy các điểm được ghi bởi các cầu thủ bóng rổ khác nhau:

Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống

Bây giờ giả sử chúng ta sử dụng công thức sau để thêm 10 vào mỗi giá trị trong cột A:

=A2+10

Ảnh chụp màn hình sau đây cho thấy cách sử dụng công thức này:

Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống

Lưu ý rằng 10 được thêm vào mỗi ô trong cột A ngay cả khi ô trong cột A trống.

Thay vào đó, chúng ta có thể sử dụng công thức sau để thêm 10 vào mỗi ô trong cột A và bỏ qua hoàn toàn các ô trống:

=IF(A2<>"", A2+10, "")

Ảnh chụp màn hình sau đây cho thấy cách sử dụng công thức này:

Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống

Lưu ý rằng 10 được thêm vào mỗi ô trong cột A ngay cả khi ô trong cột A trống.

Thay vào đó, chúng ta có thể sử dụng công thức sau để thêm 10 vào mỗi ô trong cột A và bỏ qua hoàn toàn các ô trống:

Lưu ý rằng 10 chỉ được thêm vào mỗi ô trong cột A không trống.

Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống

Ví dụ 2: Bỏ qua các ô trống trong nhiều cột

=IF(AND(A2<>"", B2<>""), A2+B2, "")

Ảnh chụp màn hình sau đây cho thấy cách sử dụng công thức này:

Hướng dẫn excel ignore blank cells - excel bỏ qua các ô trống

Lưu ý rằng 10 được thêm vào mỗi ô trong cột A ngay cả khi ô trong cột A trống.

Thay vào đó, chúng ta có thể sử dụng công thức sau để thêm 10 vào mỗi ô trong cột A và bỏ qua hoàn toàn các ô trống:

Lưu ý rằng 10 chỉ được thêm vào mỗi ô trong cột A không trống.

Ví dụ 2: Bỏ qua các ô trống trong nhiều cột
How to Ignore #N/A Values When Using Formulas in Excel

Làm thế nào tôi có thể loại bỏ công thức từ ô trống Excel, ví dụ tôi có công thức này trong một trong các ô trống trong Excel một cách tự động.

Nội dung chính ShowShow

  • Cách loại bỏ các ô trống trong Excel
  • Khi không loại bỏ các ô trống bằng cách chọn khoảng trống
  • Cách trích xuất danh sách dữ liệu bỏ qua chỗ trống
  • Cách thức hoạt động của công thức
  • Cách xóa các ô trống sau ô cuối cùng với dữ liệu
  • Bạn cũng có thể quan tâm

=IF('filepath[filename.xls]Sheet1'!$A$1:A$65536="","",'filepath[filename.xls]Sheet1'!$A$1:A$65536).

Làm thế nào tôi có thể loại bỏ công thức từ ô trống Excel, ví dụ tôi có công thức này trong một trong các ô trống trong Excel một cách tự động.

Nội dung chính ShowOct 5, 2015 at 17:09

Cách loại bỏ các ô trống trong ExcelKinfe

Khi không loại bỏ các ô trống bằng cách chọn khoảng trống1 gold badge2 silver badges13 bronze badges

5

Cách trích xuất danh sách dữ liệu bỏ qua chỗ trống

Sub Macro1()



For Each c In Worksheets("your sheet name").Range("your range")

   If c.Value = "" Then c.Select: Selection.ClearContents
   Next c

End Sub

Cách thức hoạt động của công thức

Cảm ơn

Đã hỏi ngày 5 tháng 10 năm 2015 lúc 17:09Oct 5, 2015 at 17:09Oct 5, 2015 at 17:30

KinfekinfeKinfeBalinti

2951 Huy hiệu vàng2 Huy hiệu bạc13 Huy hiệu đồng1 gold badge2 silver badges13 bronze badges1 gold badge11 silver badges13 bronze badges

3

Private Sub Worksheet_Calculate()
    Dim cell As Range
    On Error GoTo finish
    Application.EnableEvents = False

    For Each cell In UsedRange
       If cell.Text = "" Then cell.Clear
    Next

finish:
    Application.EnableEvents = True

End Sub

Đã trả lời ngày 5 tháng 10 năm 2015 lúc 17:41Oct 5, 2015 at 17:41Oct 5, 2015 at 17:41

A.S.HA.S.HA.S.HA.S.H

28.9K5 Huy hiệu vàng22 Huy hiệu bạc48 Huy hiệu đồng5 gold badges22 silver badges48 bronze badges5 gold badges22 silver badges48 bronze badges

Thật không may, không có cách nào để Excel cung cấp một ô thực sự trống nếu nó chứa một công thức, nhưng tôi có cách để làm trống các ô:

  • Tạo một câu lệnh IF rằng, nếu sai, trả về ERROR.TYPE(1).ERROR.TYPE(1). ERROR.TYPE(1).

  • Từ đó, chọn phạm vi của các ô bạn muốn xóa "khoảng trống" dự định và sử dụng "Tìm và chọn" >> "Chuyển đến Đặc biệt"."Find and Select" >> "Go to Special". "Find and Select" >> "Go to Special".

  • Nhấp vào nút "Công thức" và để lại "lỗi" khi hộp duy nhất được kiểm tra. Nhấp vào OK sẽ làm nổi bật tất cả các ô được gán giá trị "#N/A"."Formulas" radio button and leave "Errors" as the only box checked. Clicking OK will highlight all the cells that were assigned the value "#N/A". "Formulas" radio button and leave "Errors" as the only box checked. Clicking OK will highlight all the cells that were assigned the value "#N/A".

  • Bây giờ chỉ cần nhấn phím xóa.

J. Chomel

8.02115 Huy hiệu vàng42 Huy hiệu bạc65 Huy hiệu Đồng15 gold badges42 silver badges65 bronze badges15 gold badges42 silver badges65 bronze badges

Đã trả lời ngày 26 tháng 7 năm 2016 lúc 18:09Jul 26, 2016 at 18:09Jul 26, 2016 at 18:09

Hướng dẫn sẽ dạy bạn cách loại bỏ các không gian trống trong Excel để cung cấp cho bảng tính của bạn một cái nhìn rõ ràng và chuyên nghiệp.

Các tế bào trống không tệ nếu bạn cố tình để chúng ở đúng nơi vì lý do thẩm mỹ. Nhưng các tế bào trống ở những nơi sai chắc chắn là không mong muốn. May mắn thay, có một cách tương đối dễ dàng để loại bỏ chỗ trống trong Excel, và trong một khoảnh khắc bạn sẽ biết tất cả các chi tiết của kỹ thuật này.

Cách loại bỏ các ô trống trong Excel

Xóa các ô trống trong Excel là dễ dàng. Tuy nhiên, phương pháp này không được áp dụng trong tất cả các tình huống. Để giữ cho bản thân về mặt an toàn, xin vui lòng đảm bảo tạo một bản sao lưu của bảng tính của bạn và đọc những cảnh báo này trước khi bạn làm bất cứ điều gì khác.backup copy of your worksheet and read these caveats before you do anything else.backup copy of your worksheet and read these caveats before you do anything else.

Với một bản sao lưu được lưu trữ trong một vị trí lưu, thực hiện các bước sau để xóa các ô trống trong Excel:

  1. Chọn phạm vi mà bạn muốn loại bỏ khoảng trống. Để nhanh chóng chọn tất cả các ô có dữ liệu, nhấp vào ô trên bên trái và nhấn Ctrl + Shift + End. Điều này sẽ mở rộng lựa chọn đến ô được sử dụng cuối cùng.Ctrl + Shift + End. This will extend the selection to the last used cell.Ctrl + Shift + End. This will extend the selection to the last used cell.
  2. Nhấn F5 và nhấp vào Đặc biệt. Hoặc chuyển đến nhóm Tab Home> Định dạng và nhấp vào Tìm & Chọn> Chuyển đến Đặc biệt:F5 and click Special… . Or go to the Home tab > Formats group, and click Find & Select > Go to Special: F5 and click Special… . Or go to the Home tab > Formats group, and click Find & Select > Go to Special:

  3. Trong hộp thoại đi đến đặc biệt, chọn khoảng trống và bấm OK. Điều này sẽ chọn tất cả các ô trống trong phạm vi.Blanks and click OK. This will select all the blank cells in the range. Blanks and click OK. This will select all the blank cells in the range.

  4. Nhấp chuột phải vào bất kỳ khoảng trống nào đã chọn và chọn Xóa Xóa khỏi menu ngữ cảnh:Delete… from the context menu: Delete… from the context menu:

  5. Tùy thuộc vào bố cục dữ liệu của bạn, chọn chuyển các ô bên trái hoặc chuyển các ô lên và nhấp vào OK. Trong ví dụ này, chúng tôi đi với tùy chọn đầu tiên:

Đó là nó. Bạn đã loại bỏ thành công các khoảng trống trong bảng của mình:

Tips:

  • Nếu một cái gì đó đã trở nên tồi tệ, đừng hoảng sợ và ngay lập tức nhấn Ctrl + Z để lấy lại dữ liệu của bạn.Ctrl + Z to get your data back.Ctrl + Z to get your data back.
  • Nếu bạn chỉ muốn làm nổi bật các ô trống thay vì loại bỏ, bạn sẽ tìm thấy một vài phương pháp khác nhau trong bài viết này: Cách chọn và làm nổi bật các ô trống trong Excel.

Khi không loại bỏ các ô trống bằng cách chọn khoảng trống

Kỹ thuật đặc biệt> Blanks hoạt động tốt cho một cột hoặc hàng. Nó cũng có thể loại bỏ thành công các ô trống trong một loạt các hàng hoặc cột độc lập như trong ví dụ trên. Tuy nhiên, nó có thể gây bất lợi cho dữ liệu có cấu trúc. Để ngăn chặn điều này xảy ra, xin vui lòng rất cẩn thận khi loại bỏ khoảng trống trong bảng tính của bạn và ghi nhớ các cảnh báo sau:

1. Xóa các hàng và cột trống thay vì các ô

Nếu dữ liệu của bạn được sắp xếp trong một bảng nơi các cột và hàng chứa thông tin liên quan, việc xóa các ô trống sẽ làm rối dữ liệu. Trong trường hợp này, bạn chỉ nên loại bỏ các hàng trống và các cột trống. Các hướng dẫn được liên kết giải thích làm thế nào để làm điều này một cách nhanh chóng và an toàn.

2. Không hoạt động cho các bảng Excel

Không thể xóa bất kỳ ô riêng lẻ nào trong bảng Excel (so với phạm vi), bạn chỉ được phép xóa toàn bộ các hàng bảng. Hoặc bạn có thể chuyển đổi bảng thành phạm vi trước, sau đó loại bỏ các ô trống.

3. Có thể làm hỏng các công thức và phạm vi được đặt tên

Các công thức Excel có thể điều chỉnh theo nhiều thay đổi được thực hiện đối với dữ liệu được tham chiếu. Nhiều, nhưng không phải tất cả. Trong một số tình huống, các công thức đề cập đến các ô đã bị xóa có thể bị phá vỡ. Vì vậy, sau khi loại bỏ các khoảng trống, hãy xem nhanh các công thức liên quan và/hoặc các phạm vi được đặt tên để đảm bảo chúng hoạt động bình thường.

Cách trích xuất danh sách dữ liệu bỏ qua chỗ trống

Nếu bạn sợ rằng việc loại bỏ các ô trống trong một cột có thể xử lý dữ liệu của bạn, hãy để lại cột ban đầu và trích xuất các ô không trống ở một nơi khác. Phương pháp này có ích, khi bạn đang tạo một danh sách tùy chỉnh hoặc danh sách xác thực dữ liệu thả xuống và muốn đảm bảo không có chỗ trống trong đó.

Với danh sách nguồn trong A2: A11, nhập công thức mảng dưới đây trong C2, nhấn Ctrl + Shift + Enter để hoàn thành chính xác và sau đó sao chép công thức xuống một vài ô nữa. Số lượng ô mà bạn sao chép công thức phải bằng hoặc lớn hơn số lượng mục trong danh sách của bạn.Ctrl + Shift + Enter to complete it correctly, and then copy the formula down to a few more cells. The number of cells where you copy the formula should be equal to or greater than the number of items in your list.Ctrl + Shift + Enter to complete it correctly, and then copy the formula down to a few more cells. The number of cells where you copy the formula should be equal to or greater than the number of items in your list.

Công thức để trích xuất các tế bào không trống:

=IF(AND(A2<>"", B2<>""), A2+B2, "")
0

Ảnh chụp màn hình sau đây cho thấy kết quả:

Cách thức hoạt động của công thức

Khó khăn ngay từ cái nhìn đầu tiên, khi nhìn gần hơn, logic của công thức rất dễ tuân theo. Trong tiếng Anh đơn giản, công thức trong C2 có đọc như sau: Trả về giá trị đầu tiên trong phạm vi A2: A11 Nếu ô đó không trống. Trong trường hợp lỗi, hãy trả về một chuỗi trống ("").

Đối với những người dùng Excel chu đáo, những người tò mò muốn biết các loại hạt và bu lông của mọi công thức mới, đây là sự phá vỡ chi tiết:

Bạn có chức năng chỉ mục trả về giá trị từ $ 2 $ 2: $ A $ 11 dựa trên số hàng được chỉ định (không phải là số hàng thực, số hàng tương đối trong phạm vi). Trong một kịch bản đơn giản hơn, chúng tôi có thể đặt chỉ mục ($ 2 $ 2: $ A $ 11, 1) trong C2 và nó sẽ lấy cho chúng tôi một giá trị trong A2. Vấn đề là chúng ta cần phục vụ thêm 2 điều nữa:

  • Đảm bảo A2 không trống
  • Trả về giá trị không trống thứ 2 trong C3, giá trị không trống thứ 3 trong C4, v.v.

Cả hai nhiệm vụ này đều được xử lý bởi hàm nhỏ (mảng, k):

=IF(AND(A2<>"", B2<>""), A2+B2, "")
1

Trong trường hợp của chúng tôi, đối số mảng được tạo động theo cách sau:

  • =IF(AND(A2<>"", B2<>""), A2+B2, "")
    
    2 Xác định ô nào trong phạm vi đích không trống và trả về đúng với chúng, nếu không thì sai. Mảng kết quả của Đúng và Sai đi đến bài kiểm tra logic của hàm IF.
  • Nếu đánh giá từng phần tử của mảng đúng/sai và trả về một số tương ứng cho true, một chuỗi trống cho sai:

    =IF(AND(A2<>"", B2<>""), A2+B2, "")
    
    3

=IF(AND(A2<>"", B2<>""), A2+B2, "")
4 chỉ cần thiết để trả về một mảng các số từ 1 đến 10 (vì có 10 ô trong phạm vi của chúng tôi), nếu có thể chọn một số cho các giá trị thực.

Kết quả là, chúng ta nhận được mảng {1; ""; 3; ""; 5; 6; ""; 8; ""; 10} và hàm nhỏ phức tạp của chúng ta biến thành một hàm đơn giản này:

=IF(AND(A2<>"", B2<>""), A2+B2, "")
5

Như bạn thấy, đối số mảng chỉ chứa số lượng ô không trống (nhớ bạn, đây là các vị trí tương đối của các phần tử trong mảng, tức là A2 là phần tử 1, A3 là phần tử 2, v.v.).relative positions of the elements in the array, i.e. A2 is element 1, A3 is element 2, and so on).relative positions of the elements in the array, i.e. A2 is element 1, A3 is element 2, and so on).

Trong đối số K, chúng tôi đặt hàng (A1) hướng dẫn hàm nhỏ trả về số nhỏ nhất của 1. Do việc sử dụng tham chiếu ô tương đối, số hàng tăng lên theo mức tăng 1 khi bạn sao chép công thức xuống. Vì vậy, trong C3, K sẽ thay đổi thành hàng (A2) và công thức sẽ trả về số lượng ô không trống thứ 2, v.v.

Tuy nhiên, chúng tôi không thực sự cần các số ô không trống, chúng tôi cần các giá trị của chúng. Vì vậy, chúng tôi di chuyển về phía trước và tổ chức năng nhỏ vào đối số ROW_NUM của chỉ mục buộc nó phải trả về một giá trị từ hàng tương ứng trong phạm vi.

Như một liên lạc hoàn thiện, chúng tôi gửi kèm toàn bộ cấu trúc trong hàm iferror để thay thế các lỗi bằng các chuỗi trống. Lỗi là không thể tránh khỏi vì bạn không thể biết có bao nhiêu ô không trống trong phạm vi mục tiêu, do đó bạn sao chép công thức vào số lượng ô lớn hơn.

Với những điều trên, chúng ta có thể xây dựng công thức chung này để trích xuất các giá trị bỏ qua khoảng trống:

{= Iferror (index (phạm vi, nhỏ (if (không (isblank (phạm vi)), hàng ($ a $ 1: $ a $ 10), ""), hàng (a1))

Trong đó "phạm vi" là phạm vi với dữ liệu gốc của bạn. Vui lòng chú ý hàng đó ($ 1 $ 1: $ a $ 10) và hàng (A1) là các phần không đổi và không bao giờ thay đổi cho dù dữ liệu của bạn bắt đầu từ đâu và bao nhiêu ô bao gồm.

Cách xóa các ô trống sau ô cuối cùng với dữ liệu

Các ô trống có chứa định dạng hoặc các ký tự không thể in có thể gây ra nhiều vấn đề trong Excel. Ví dụ: cuối cùng bạn có thể có kích thước tệp lớn hơn nhiều so với cần thiết hoặc có một vài trang trống được in. Để tránh những vấn đề này, chúng tôi sẽ xóa (hoặc rõ ràng) các hàng và cột trống có chứa định dạng, khoảng trắng hoặc các ký tự vô hình không xác định.

Cách định vị ô đã sử dụng cuối cùng trên trang tính

Để di chuyển đến ô cuối cùng trên trang tính chứa dữ liệu hoặc định dạng, nhấp vào bất kỳ ô nào và nhấn Ctrl + End.Ctrl + End.Ctrl + End.

Nếu các phím tắt trên đã chọn ô cuối cùng với dữ liệu của bạn, điều đó có nghĩa là các hàng và cột còn lại thực sự trống và không cần thao tác nữa. Nhưng nếu nó đưa bạn đến một ô trống trực quan, hãy biết rằng Excel không xem xét trống tế bào đó. Nó có thể là một ký tự không gian đơn thuần được tạo ra bởi một cú đánh khóa tình cờ, một định dạng số tùy chỉnh cho ô đó hoặc một ký tự không thể in được nhập từ cơ sở dữ liệu bên ngoài. Bất kể lý do nào, tế bào đó không trống.

Xóa các ô sau tế bào cuối cùng với dữ liệu

Để xóa tất cả nội dung và định dạng sau ô cuối cùng với dữ liệu, hãy làm như sau:

  1. Nhấp vào tiêu đề của cột trống đầu tiên ở bên phải dữ liệu của bạn và nhấn Ctrl + Shift + End. Điều này sẽ chọn một loạt các ô giữa dữ liệu của bạn và ô được sử dụng cuối cùng trên trang tính.Ctrl + Shift + End. This will select a range of cells between your data and the last used cell on the sheet.Ctrl + Shift + End. This will select a range of cells between your data and the last used cell on the sheet.
  2. Trên tab Trang chủ, trong nhóm chỉnh sửa, nhấp vào Xóa> Xóa tất cả. Hoặc nhấp chuột phải vào lựa chọn và nhấp vào Xóa Xóa> Toàn bộ cột:Delete… > Entire column: Delete… > Entire column:

  3. Nhấp vào tiêu đề của hàng trống đầu tiên bên dưới dữ liệu của bạn và nhấn Ctrl + Shift + End.Ctrl + Shift + End.Ctrl + Shift + End.
  4. Nhấp vào Xóa> Xóa tất cả trên tab Home hoặc nhấp chuột phải vào lựa chọn và chọn Xóa,> Toàn bộ hàng.Delete… > Entire row.Delete… > Entire row.
  5. Nhấn CTRL + S để lưu sổ làm việc.Ctrl + S to save the workbook.Ctrl + S to save the workbook.

Kiểm tra phạm vi đã sử dụng để đảm bảo hiện tại chỉ chứa các ô có dữ liệu và không có chỗ trống. Nếu phím tắt CTRL + kết thúc chọn một ô trống một lần nữa, hãy lưu sổ làm việc và đóng nó. Khi bạn mở bảng tính một lần nữa, ô được sử dụng cuối cùng sẽ là ô cuối cùng có dữ liệu.Ctrl + End shortcut selects a blank cell again, save the workbook and close it. When you open the worksheet again, the last used cell should be the last cell with data.Ctrl + End shortcut selects a blank cell again, save the workbook and close it. When you open the worksheet again, the last used cell should be the last cell with data.

Mẹo. Cho rằng Microsoft Excel 2007 trở lên chứa hơn 1.000.000 hàng và hơn 16.000 cột, bạn có thể muốn giảm kích thước không gian làm việc để ngăn người dùng của bạn vô tình nhập dữ liệu vào các ô sai. Đối với điều này, bạn chỉ cần loại bỏ các ô trống khỏi chế độ xem của chúng như được giải thích trong cách ẩn các hàng và cột không sử dụng (trống). Given that Microsoft Excel 2007 and higher contains over 1,000,000 rows and more than 16,000 columns, you may want to reduce the workspace size to prevent your users from unintentionally entering data into wrong cells. For this, you can simply remove empty cells from their view as explained in How to hide unused (blank) rows and columns. Given that Microsoft Excel 2007 and higher contains over 1,000,000 rows and more than 16,000 columns, you may want to reduce the workspace size to prevent your users from unintentionally entering data into wrong cells. For this, you can simply remove empty cells from their view as explained in How to hide unused (blank) rows and columns.

Đó là cách bạn xóa trống trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm