NộI Dung
- Cách định cấu hình Excel 2007 và Excel 2010 để bỏ qua ô trống khi tạo biểu đồ hoặc đồ thị
- Phương pháp 1: Định cấu hình cách Excel xử lý các ô bị ẩn và trống
- Phương pháp 2: Sử dụng công thức để chuyển đổi ô trống thành # N / A
- Kết thúc
Robbie chủ yếu viết về Skyrim nhưng đôi khi cũng làm sáng tỏ những điều kỳ lạ của các ứng dụng Microsoft như Excel và Outlook.
Cách định cấu hình Excel 2007 và Excel 2010 để bỏ qua ô trống khi tạo biểu đồ hoặc đồ thị
Đây là vấn đề mà chính tôi đã gặp phải, nơi tôi muốn tạo biểu đồ khi hầu hết các ô trong phạm vi mà bạn muốn lập biểu đồ đều trống (chứa các ô trống).
Để giải quyết vấn đề này và tạo biểu đồ hiển thị dữ liệu chính xác, bạn có thể bị cám dỗ như tôi đã từng làm trước đây sao chép các giá trị dữ liệu sang một phạm vi mới để dữ liệu liền kề (không có khoảng trống). Điều này có thể cực kỳ tốn thời gian và rất khó chịu, đặc biệt là nếu lượng dữ liệu lớn. Excel được cho là sẽ làm cho cuộc sống tập thể của chúng ta trở nên dễ dàng hơn, vì vậy tôi quyết định tìm kiếm giải pháp cho vấn đề liên quan đến càng ít công việc thủ công càng tốt.
Trong trường hợp của tôi, tôi quan tâm đến việc lập biểu đồ trung bình hàng tuần của số doanh thu hàng ngày mà nhóm của tôi đã thực hiện. Khi tôi tạo biểu đồ bằng cách chọn phạm vi dữ liệu, tất cả những gì tôi nhận được là biểu đồ không có dữ liệu.
Chúng ta sẽ xem xét hai phương pháp để giải quyết vấn đề này:
- Cách thay đổi cách biểu đồ xử lý với các ô trống hoặc ô trống
- Cách sử dụng công thức để thay đổi nội dung của các ô trống thành # N / A (Excel tự động bỏ qua các ô có # N / A khi bạn tạo biểu đồ)
Trong hình bên dưới, bạn có thể thấy:
- Dữ liệu tôi sử dụng để tạo biểu đồ (ở bên trái)
- Biểu đồ ban đầu trống (trên cùng bên phải)
- Biểu đồ đã hoàn thành (dưới cùng bên phải)
Phương pháp 1: Định cấu hình cách Excel xử lý các ô bị ẩn và trống
Phương pháp đầu tiên chúng tôi sẽ sử dụng để tạo biểu đồ hiển thị dữ liệu chính xác là thay đổi cách Excel 2007 và Excel 2010 xử lý các ô trống trong biểu đồ.
Theo mặc định trong biểu đồ, khi Excel nhìn thấy một ô trống, nó sẽ tạo ra một khoảng trống. Khi bạn có một phạm vi dữ liệu với nhiều ô trống hơn các ô có dữ liệu, điều này có nghĩa là Excel tạo ra một biểu đồ đầy khoảng trống một cách hiệu quả.
Để bắt đầu, chúng ta cần tạo một biểu đồ về doanh số bán hàng trung bình hàng tuần.
- Chúng tôi tạo một biểu đồ đường bằng cách chọn dữ liệu trong B3: B31 và D3: D31 (nhấp và giữ Ctrl phím để chọn các cột không liền kề)
- Nhấn vào Chèn và chọn Hàng nút trong Biểu đồ nhóm và chọn Hàng
Bây giờ chúng ta đã tạo biểu đồ, chúng ta cần thay đổi cách Excel xử lý các ô trống. Để làm điều này:
- Nhấp chuột phải biểu đồ và chọn Chọn dữ liệu
- Nhấp chuột Ô ẩn và ô trống
- Khoảng trống là cài đặt mặc định như chúng ta đã thảo luận trước đây
- Số không trong ví dụ của chúng tôi sẽ dẫn đến dữ liệu được biểu đồ dưới dạng giá trị, không, không, không, không, không, không, giá trị
- Kết nối các điểm dữ liệu với đường thẳng làm chính xác như nó nói, kết nối các điểm dữ liệu với nhau
Hình dưới đây cho thấy biểu đồ của chúng tôi được tạo bằng cách sử dụng Số không (trái) và Kết nối các điểm dữ liệu với đường thẳng (đúng). Khoảng trống chúng ta đã thấy với biểu đồ trống ở trên.
- Tôi cũng thu dọn trục hoành bằng cách nhấp chuột phải nó và lựa chọn Định dạng trục
- Phía dưới cái Tùy chọn trục tab, tôi đã thay đổi Đơn vị chính đến 7 ngày cố định
- Tôi đã thay đổi Trục vị trí đến Trên dấu tích để nó hiển thị đầy đủ ngày tháng
Phương pháp 2: Sử dụng công thức để chuyển đổi ô trống thành # N / A
Phương pháp thứ hai để đảm bảo rằng Excel lập biểu đồ dữ liệu có chứa nhiều khoảng trống hoặc ô trống một cách chính xác là sử dụng công thức. Để đạt được điều này, chúng tôi sẽ sử dụng thực tế là khi Excel nhìn thấy # N / A trong một ô, nó không đưa nó vào biểu đồ. Công thức chúng ta sẽ sử dụng trong E3 là:
= IF (D3 = "", # N / A, D3)
Các NẾU hàm xem xét nội dung của ô D3 và NẾU D3 bằng “” (nói cách khác là ô trống) sau đó nó thay đổi ô hiện tại thành # N / A. Nếu Excel tìm thấy thứ gì đó trong ô, nó sẽ chỉ cần sao chép ô đó. Ví dụ, nó sẽ sao chép nội dung của D3 sang E3.
Tôi có một bài báo thảo luận về NẾU chức năng chi tiết hơn. Tôi cũng bao gồm việc sử dụng IFERROR để ngăn chặn các lỗi mong đợi và sử dụng NẾU với các chức năng logic VÀ, HOẶC và KHÔNG PHẢI. Nó có thể được tìm thấy ở đây:
- Tiếp theo, tôi sao chép công thức xuống cột E.
- Sau đó, tôi tạo một biểu đồ chính xác như chúng ta đã làm ở trên bằng cách sử dụng ngày tháng trong cột B và kết quả của các công thức trong cột E
Như bạn có thể thấy từ hình trên, biểu đồ hiển thị chính xác dữ liệu, ngay cả khi Excel đang sử dụng Hiển thị các ô trống như Khoảng trống (cài đặt mặc định cho tất cả các biểu đồ được tạo trong Excel 2007 và Excel 2010).
Sau đó, chúng tôi sẽ thu gọn biểu đồ như đã làm ở trên,
- Thu dọn trục hoành
- Xóa Huyền thoại
- Thêm một Tiêu đề (nhấn vào Bố trí với biểu đồ đã chọn, hãy chọn Tiêu đề biểu đồ và chọn loại tiêu đề bạn thích)
Kết thúc
Thông thường, dữ liệu đến với bạn từ các bảng tính mà bạn không có quyền kiểm soát và không phải lúc nào cũng được định dạng chính xác theo cách bạn muốn. Thay vì chỉnh sửa lại dữ liệu để phù hợp với những gì bạn muốn, việc Excel làm việc với bất kỳ thách thức định dạng nào mà dữ liệu có thể gặp phải luôn tốt hơn và nhanh hơn nhiều và tạo ra kết quả bạn muốn mà không cần phải chỉnh sửa thủ công.
Trong bài viết này, chúng tôi đã xem xét một vấn đề mà tôi thường gặp:
Cách tạo biểu đồ bằng cách sử dụng dữ liệu có chứa nhiều ô trống. Chúng tôi đã xem xét hai phương pháp về cách giải quyết vấn đề này:
- Thay đổi cách Excel xử lý các ô trống trong chính biểu đồ
- Sử dụng NẾU câu lệnh để thay đổi các ô trống thành # N / A (Excel 2007 và Excel 2010 bỏ qua các ô chứa # N / A khi tạo biểu đồ)
Sử dụng NẾU các câu lệnh trong công thức linh hoạt hơn việc thay đổi cách xử lý của Excel với các ô trống trong biểu đồ vì bạn có thể yêu cầu nó thay đổi bất kỳ điều gì thành # N / A, không chỉ các ô trống. Điều này có nghĩa là nếu bạn nhận được bảng tính có rác hoặc dữ liệu mà bạn không muốn, bạn cũng có thể yêu cầu Excel bỏ qua nó cho mục đích tạo biểu đồ từ dữ liệu.
Tôi hy vọng rằng bạn đã tìm thấy bài viết này hữu ích và nhiều thông tin. Xin vui lòng để lại bất kỳ ý kiến bạn có thể có bên dưới và cảm ơn rất nhiều vì đã đọc!
Bài viết này chính xác và đúng theo hiểu biết tốt nhất của tác giả. Nội dung chỉ dành cho mục đích thông tin hoặc giải trí và không thay thế cho lời khuyên cá nhân hoặc lời khuyên chuyên nghiệp trong các vấn đề kinh doanh, tài chính, pháp lý hoặc kỹ thuật.
