Đổ đầy ô đã hợp nhất xuống - Mẹo Excel

Mục lục

Các ô được hợp nhất là vấn đề ngày nay, nhưng đây là các ô được hợp nhất theo chiều dọc. Hãy nhìn vào hình bên dưới. Nếu có 7 hàng cho khu vực Trung Tây, thì từ Trung Tây chỉ xuất hiện trên hàng đầu tiên. Sau đó, ai đó đang hợp nhất sáu ô trống còn lại với ô Trung Tây để tạo ra một ô đã hợp nhất có chiều cao 7 ô.

Các ô hợp nhất là ác.

Đây là một cách khủng khiếp để gửi dữ liệu. Nếu ai đó thành thạo Excel, họ có thể muốn sắp xếp dữ liệu, thêm tổng phụ, sử dụng bảng tổng hợp. Các ô được hợp nhất ngăn chặn điều này.

Tệ hơn nữa, bạn đang nhận được một tệp mới từ trụ sở mỗi ngày có cấu trúc như thế này. HQ đang gửi các tập tin tương tự đến 500 văn phòng chi nhánh. Chỉ là một tiền đồn nhỏ, bạn không có nhiều cơ hội để thuyết phục HQ rằng đây là một cách khủng khiếp để gửi dữ liệu.

Bài viết hôm nay là về cách nhanh chóng giải quyết vấn đề này với Power Query. Giải pháp Power Query sẽ dễ dàng hơn vào các ngày từ 2 đến 9999 so với các bước sau trong Excel:

Các chuyên gia Excel có thể đề xuất các bước sau mỗi ngày:

  1. Chọn tất cả các ô bằng cách chọn hình chữ nhật ở trên và bên trái của A1.
  2. Nhấp vào trình khởi chạy hộp thoại ở góc dưới bên phải của nhóm Căn chỉnh của tab Trang chủ.
  3. Bấm vào hộp Hợp nhất các ô hai lần để bỏ chọn nó.
  4. Bấm OK để đóng hộp thoại Định dạng ô
  5. Chọn từ cuối cột A trở lại A1.
  6. Trang chủ, Tìm & Chọn, Đi đến Đặc biệt, Khoảng trống, OK
  7. Gõ = UpArrow. Nhấn Ctrl + Enter
  8. Chọn từ cuối cột A trở lại A1.
  9. Ctrl + C để sao chép. Nhấp chuột phải và dán các giá trị

Nhưng có một cách dễ dàng hơn nhiều. Các công cụ Power Query mới được tích hợp sẵn cho các phiên bản Windows của Office 365 và Excel 2016. Nếu bạn có phiên bản Windows của Excel 2010 hoặc Excel 2013, bạn có thể tải xuống Power Query miễn phí từ Microsoft.

Đây là chiến lược với Power Query:

  1. Lập kế hoạch mà bạn sẽ lưu sổ làm việc của mỗi ngày từ HQ trong cùng một thư mục có cùng tên.
  2. Mở một sổ làm việc trống mới sẽ giữ dữ liệu cố định từ HQ.

  3. Trong sổ làm việc trống, hãy chọn Dữ liệu, Lấy dữ liệu, Từ tệp, Từ sổ làm việc.

    Bắt đầu quy trình Power Query
  4. Duyệt đến thư mục và tệp từ bước # 1.

  5. Khi cửa sổ Power Query mở ra, hãy lưu ý rằng các ô đã hợp nhất đã biến mất. Bạn có Trung Tây ở hàng 1 và sau đó là sáu ô chứa "null". Chọn cột này bằng cách nhấp vào tiêu đề Khu vực.

    Power Query tự động xóa các ô đã hợp nhất

  6. Trong Power Query, hãy chọn tab Chuyển đổi. Chọn Fill, Down. Từ Midwest được sao chép từ hàng 1 sang hàng 2 đến hàng 7. Các biến đổi tương tự xảy ra trong toàn bộ tập dữ liệu.

    Điền vào các ô trống với giá trị ở trên

  7. Trang chủ, Đóng và Tải. Power Query sẽ đóng. Trong khoảng 10-20 giây, dữ liệu đã được làm sạch từ HQ sẽ xuất hiện trong một trang tính mới trong sổ làm việc.

    Trả lại dữ liệu cho Excel
  8. Lưu sổ làm việc với tên chẳng hạn như CleanedDataFromHQ.xlsx.
  9. Khi dữ liệu được chọn, bạn sẽ thấy bảng Truy vấn & Kết nối ở phía bên phải. Nếu bạn không thấy bảng, hãy chuyển đến Dữ liệu, Truy vấn & Kết nối.

  10. Bấm chuột phải vào truy vấn trong bảng Truy vấn & Kết nối. Chọn Thuộc tính.

    Mở Thuộc tính cho truy vấn này

  11. Chọn "Làm mới dữ liệu khi mở tệp". Bấm OK.

    Đặt truy vấn để chạy mỗi khi bạn mở sổ làm việc.

Sau đó, quy trình làm việc của bạn sẽ trở thành: (a) Nhận sổ làm việc từ HQ qua e-mail. (b) Lưu tệp đính kèm vào đúng thư mục với tên chính xác. (c) Mở sổ làm việc CleanedDataFromHQ.xlsx. Dữ liệu mới sẽ tải vào sổ làm việc.

Xem video

Bản ghi video

Học Excel từ Podcast, Tập 2221: Đổ đầy ô đã hợp nhất xuống.

Này, chào mừng bạn trở lại với netcast. Tôi là Bill Jelen, tôi ở thành phố Calumet, Elizabeth xuất hiện và nói rằng cô ấy nhận được tệp này - tệp của chúng tôi, như tệp này - mỗi ngày và điều đó thật kinh khủng. Ở đây trong Cột A, không chỉ là họ đặt Trung Tây và để lại một loạt ô trống, đó là một ô hợp nhất. Ổn thỏa? Họ đang gửi-- HQ đang gửi những tệp này đến các địa điểm trên thế giới với những ô hợp nhất kỳ lạ này ở đây. Các ô hợp nhất là ác.

Được rồi, đây là cách để loại bỏ điều này. Trước hết, tôi sẽ nhấp chuột phải, Di chuyển hoặc Sao chép, Tạo bản sao và chuyển nó sang cuốn sách mới. Vì vậy, chúng tôi giả vờ nó giống như sổ làm việc mà Elizabeth đang nhận. File, Save As, và mỗi khi Elizabeth nhận được một trong các sổ làm việc này, tôi muốn cô ấy đặt nó vào cùng một vị trí với cùng một tên. Ổn thỏa? Chính xác cùng tên-- hoàn hảo. Vì vậy, bây giờ chúng ta chỉ có một tệp duy nhất, một tờ duy nhất, tôi sẽ đóng tệp. Và sau đó chúng ta sẽ tạo một sổ làm việc hoàn toàn mới. Tôi sẽ chèn một trang tính mới vào đây và trang tính mới này sẽ được gọi là Báo cáo. Và trên ô trống này, Báo cáo của chúng tôi sẽ thực hiện Dữ liệu, Lấy dữ liệu, Từ tệp, Từ sổ làm việc, trỏ đến tệp mà chúng tôi sẽ lưu nó vào-- vì vậy, với cùng một tên chính xác mỗi lần - nhấp vào Nhập , chọn trang tính đầu tiên,và sau đó nhấp vào Chỉnh sửa. Ổn thỏa. Trước hết, điều này thật tuyệt vời - truy vấn nguồn sẽ không có bất kỳ ô hợp nhất nào vô nghĩa, chúng ngay lập tức chuyển các ô hợp nhất thành các ô riêng lẻ và tất cả các ô này đều trống. Chọn cột đó, nó đã được chọn ở đây, và sau đó Biến đổi, Tô, Tô xuống, tương tự như vậy, rồi đến Trang chủ, Đóng & Tải. Được rồi, đây là sổ làm việc của tôi.

Bây giờ, tôi thấy một số có thêm một số cột ở đây. Hãy để tôi nhấp chuột phải và chúng tôi sẽ Chỉnh sửa.

Và có vẻ như bất cứ điều gì ở bên phải của chi phí cần phải biến mất. Có bao nhiêu thứ bổ sung ngoài đó? Nhấp chuột phải và Xóa các cột đó-- hoàn hảo-- Đóng & Tải. Được rồi, bây giờ, quy trình công việc trở nên, mỗi khi Elizabeth nhận được báo cáo mới từ trụ sở chính, Cô ấy sẽ lưu nó vào cùng một chỗ với tên đáng tin cậy hoặc bất cứ điều gì-- bất cứ điều gì - chúng tôi gọi nó, rồi mở sổ làm việc này, và sau đó ở đây nhấp vào Làm mới.

Hoặc, nếu chúng tôi muốn làm việc tự động, hãy nhấp chuột phải vào đây, đi xuống Thuộc tính, ngay bên ngoài màn hình của bạn ở cuối màn hình, rồi nói, Mỗi khi tôi mở tệp này, hãy làm mới dữ liệu. Vì vậy, mỗi khi tôi mở sổ làm việc này, nó sẽ chuyển sang sổ làm việc mới mà chúng tôi nhận được từ trụ sở chính, nó sẽ thay thế các ô trống đã hợp nhất đó bằng các giá trị cần có và cuộc sống thật tuyệt vời như vậy.

Power Query được đề cập trong cuốn sách của tôi, nhưng nó cũng được đề cập trong cuốn sách này của Ken Puls và Miguel Escobar, M dành cho (DỮ LIỆU) MONKEY.

Được rồi, vậy Elizabeth: Làm cách nào để loại bỏ các ô hợp nhất theo chiều dọc? Trụ sở của tôi liên tục gửi cho tôi những tập tin này mỗi ngày. Vì vậy, kế hoạch: Chúng tôi sẽ lưu sổ làm việc ở một nơi đáng tin cậy với một tên đáng tin cậy; tạo một sổ làm việc báo cáo trống, Dữ liệu, Lấy Dữ liệu, Từ Tệp, Từ Sổ làm việc, chỉ định trang tính, BAM! Các ô hợp nhất đã biến mất. Tôi chọn cột đó và Điền xuống, Đóng & Tải; sau đó mỗi khi bạn nhận được sổ làm việc mới, hãy lưu sổ làm việc mới đó ở nơi đáng tin cậy với cùng một tên đáng tin cậy; Tôi mở sổ làm việc Báo cáo và Làm mới hoặc chúng tôi chỉ đặt sổ làm việc Báo cáo thành Làm mới tự động khi mở.

Để tải xuống sổ làm việc từ video hôm nay là URL trong mô tả YouTube.

Tôi muốn cảm ơn Elizabeth vì đã có mặt trong buổi hội thảo của tôi ở Thành phố Calumet, tôi muốn cảm ơn bạn đã ghé qua. Hẹn gặp lại bạn lần sau cho một netcast khác từ.

Tải xuống tệp Excel

Để tải xuống tệp excel: fill-merge-cells-down.xlsx

Power Query là một công cụ tuyệt vời - nó cho phép bạn giải quyết nhiều loại

saeed Alimohammadi

thú vị bài viết...

Đề XuấT