Excel thay thế giá trị ô nếu có điều kiện

Chào mọi người

Tệp quản lý sản xuất trong công ty của tôi quá lớn và tôi phải thường xuyên thay thế các công thức bằng các giá trị. tôi muốn nó tự động hóa bằng macro

tôi cần một cái gì đó như thế này. NẾU giá trị trong hàng Z cũ hơn 30 ngày, hãy thay thế các công thức trong hàng đó [cột từ A đến Y] bằng các giá trị mặc dù tại thời điểm macro này bắt đầu, phải tắt các macro có tên "xxx" và "yyy". Khi macro kết thúc, nó phải bật lại cả hai macro

Nếu có thể, tôi muốn có 2 ô trong trang tính có tên là "Dane", nơi tôi có thể chỉ định phạm vi hàng để quét và thay thế bằng các giá trị nếu có

Tôi đính kèm ảnh chụp màn hình của tập tin


Thx rất nhiều cho bất kỳ sự giúp đỡ

Cảm ơn bạn rất nhiều, nó đã làm gần như những gì tôi cần mặc dù tôi muốn chỉnh sửa nó một chút. Đầu tiên, tôi phải tham chiếu ngày đến cột cuối cùng trong trường hợp của tôi, đó là cột DL vì tôi nhận được thông báo lỗi [Lỗi thời gian chạy 6] khi tôi sử dụng mã của bạn. Hơn nữa, macro đang sao chép và dán các giá trị trong tất cả các cột ở bên trái từ DL, điều mà thực sự tôi không muốn vì Sau cột Z, tôi vẫn cần có công thức

Bạn có thể vui lòng thay đổi mã mà tôi có thể chỉ định phạm vi cột mà tôi muốn sao chép và dán làm giá trị không?

Về "Dane", ý tôi là theo dõi

Nếu tôi chạy mã của bạn trên tệp của tôi +- 10 000 hàng thì sẽ mất nhiều thời gian vì nó luôn bắt đầu từ hàng đầu tiên và kết thúc ở hàng cuối cùng với dữ liệu, vì vậy tôi muốn chỉ định trong bảng "Dane" trong các ô . B1 phạm vi hàng tôi muốn macro kiểm tra và sao chép giá trị nếu tiêu chí được đáp ứng

Nếu tôi đặt giá trị 100 trong A1 và 500 trong B1, macro của bạn sẽ chỉ đi qua các hàng lớn hơn hoặc bằng 100 và nhỏ hơn hoặc bằng 500


Cảm ơn bạn trước

Tháng trước, một trong những đồng nghiệp của tôi có một bộ dữ liệu trong Excel và anh ấy đang đập đầu để làm sạch nó. Vì tôi là người duy nhất trong văn phòng vào lúc đó, anh ấy hỏi tôi có thể giúp gì không. Tôi đã sử dụng một kỹ thuật đơn giản bằng cách sử dụng Tìm và Thay thế trong Excel và dữ liệu của anh ấy hoàn toàn sạch sẽ và bóng bẩy. Anh ấy cảm ơn tôi, thu dọn đồ đạc và rời văn phòng

Anh ấy cảm ơn tôi, thu dọn đồ đạc và rời văn phòng

Tính năng Tìm và Thay thế Excel siêu mạnh nếu bạn biết cách sử dụng tốt nhất

Sử dụng TÌM và THAY THẾ trong Excel [4 Ví dụ]

Tìm và thay thế trong Excel có thể tiết kiệm rất nhiều thời gian và đó là điều quan trọng nhất hiện nay

Trong blog này, tôi sẽ chia sẻ 4 mẹo tuyệt vời mà tôi đã chia sẻ với hàng trăm đồng nghiệp trong văn phòng của mình. Câu trả lời luôn giống nhau – “Ước gì tôi biết điều này sớm hơn. Nó có thể đã giúp tôi tiết kiệm rất nhiều lao động nặng nhọc”

#1 Để Thay đổi Tham chiếu Ô bằng Tìm và Thay thế Excel

Đôi khi bạn làm việc với nhiều công thức, cần phải thay đổi tham chiếu ô trong tất cả các công thức. Bạn có thể mất nhiều thời gian nếu thay đổi thủ công trong từng ô có công thức

Đây là nơi Excel Tìm và Thay thế có ích. Nó có thể dễ dàng tìm thấy một tham chiếu ô trong tất cả các công thức trong trang tính [hoặc trong các ô đã chọn] và thay thế nó bằng một tham chiếu ô khác

Ví dụ: giả sử bạn có một tập dữ liệu khổng lồ với công thức sử dụng $A$1 làm một trong các tham chiếu ô [như hình bên dưới]. Nếu bạn cần thay đổi $A$1 bằng $B$1, bạn có thể làm điều đó bằng Tìm và Thay thế trong Excel

Dưới đây là các bước để làm điều này

1. Chọn các ô có công thức mà bạn muốn thay thế tham chiếu. Nếu bạn muốn thay thế trong toàn bộ trang tính, hãy chọn toàn bộ trang tính
2. Vào Trang chủ -> Tìm và chọn -> Thay thế [Phím tắt – Control + H]
3. Trong hộp thoại Tìm và Thay thế, hãy sử dụng thông tin chi tiết sau
   • Tìm gì. $A$1 [tham chiếu ô bạn muốn thay đổi].
   • Thay thế bằng. $B$1 [tham chiếu ô mới].
4. Nhấp vào Thay thế tất cả

Điều này sẽ ngay lập tức cập nhật tất cả các công thức với tham chiếu ô mới

Lưu ý rằng điều này sẽ thay đổi tất cả các phiên bản của tài liệu tham khảo đó. Ví dụ: nếu bạn có tham chiếu $A$1 hai lần trong một công thức, cả hai trường hợp sẽ được thay thế bằng $B$1

#2 Để Tìm và Thay thế Định dạng trong Excel

Đây là một tính năng thú vị khi bạn muốn thay thế định dạng hiện có bằng một số định dạng khác. Ví dụ: bạn có thể có các ô có màu nền màu cam và bạn muốn thay đổi màu nền của tất cả các ô này thành màu đỏ. Thay vì thực hiện việc này theo cách thủ công, hãy sử dụng Tìm và Thay thế để thực hiện tất cả việc này cùng một lúc

Dưới đây là các bước để làm điều này

1. Chọn các ô mà bạn muốn tìm và thay thế định dạng. Nếu bạn muốn tìm và thay thế một định dạng cụ thể trong toàn bộ trang tính, hãy chọn toàn bộ trang tính
2. Chuyển đến Trang chủ -> Tìm và chọn -> Thay thế [Phím tắt – Control + H]
3. Bấm vào nút Tùy chọn. Thao tác này sẽ mở rộng hộp thoại và hiển thị cho bạn nhiều tùy chọn hơn
4. Nhấp vào nút Tìm định dạng. Nó sẽ hiển thị một trình đơn thả xuống với hai tùy chọn - Định dạng và Chọn định dạng từ ô
   • Bạn có thể chỉ định thủ công định dạng mà bạn muốn tìm bằng cách nhấp vào nút Định dạng hoặc bạn có thể chọn định dạng từ một ô trong trang tính. Để chọn định dạng từ một ô, hãy chọn tùy chọn 'Chọn định dạng từ ô' rồi nhấp vào ô mà bạn muốn chọn định dạng
   • Khi bạn chọn một định dạng từ một ô hoặc chỉ định nó theo cách thủ công từ hộp thoại định dạng ô, bạn sẽ thấy đó là bản xem trước ở bên trái của nút định dạng
5. Bây giờ, bạn cần chỉ định định dạng mà bạn muốn thay vì định dạng đã chọn ở bước trước. Nhấp vào nút Thay thế bằng Định dạng. Nó sẽ hiển thị một trình đơn thả xuống với hai tùy chọn - Định dạng và Chọn định dạng từ ô
   • Bạn có thể chỉ định nó theo cách thủ công bằng cách nhấp vào nút Định dạng hoặc bạn có thể chọn định dạng hiện có trong trang tính bằng cách nhấp vào ô có định dạng đó
   • Khi bạn chọn một định dạng từ một ô hoặc chỉ định nó theo cách thủ công từ hộp thoại định dạng ô, bạn sẽ thấy đó là bản xem trước ở bên trái của nút định dạng
6. Nhấp vào nút Thay thế tất cả

Bạn có thể sử dụng kỹ thuật này để thay thế rất nhiều thứ trong định dạng. Nó có thể chọn và thay thế các định dạng như màu nền, đường viền, loại/kích thước/màu phông chữ và thậm chí cả các ô được hợp nhất

#3 Để Thêm hoặc Xóa Dấu ngắt dòng

Bạn làm gì khi phải chuyển đến một dòng mới trong một ô Excel

Bạn nhấn Alt + Enter

Và bạn sẽ làm gì khi bạn muốn hoàn nguyên điều này?

Bạn xóa thủ công. phải không?

Hãy tưởng tượng bạn có hàng trăm ngắt dòng mà bạn muốn xóa. xóa ngắt dòng thủ công có thể mất nhiều thời gian

Đây là tin tốt, bạn không cần phải làm điều này một cách thủ công. Tính năng Tìm và Thay thế trong Excel có một thủ thuật thú vị giúp thực hiện điều đó trong tích tắc

Dưới đây là các bước để loại bỏ tất cả các ngắt dòng cùng một lúc

1. Chọn dữ liệu mà bạn muốn xóa ngắt dòng
2. Vào Trang chủ -> Tìm và chọn -> Thay thế [Phím tắt – Control + H]
3. Trong Hộp thoại Tìm và Thay thế
   • Tìm gì. Nhấn Control + J [bạn có thể không nhìn thấy gì ngoại trừ dấu chấm nhấp nháy].
   • Thay thế bằng. Ký tự dấu cách [nhấn phím cách một lần].
4. Nhấp vào Thay thế tất cả

Và Woosh. Nó sẽ loại bỏ tất cả các ngắt dòng khỏi trang tính của bạn một cách kỳ diệu

#4 Đến Xóa văn bản bằng ký tự đại diện Ký tự

Cái này giúp tôi tiết kiệm hàng giờ. Tôi nhận được một danh sách như hình dưới đây và tôi phải xóa văn bản giữa dấu ngoặc đơn

Nếu bạn có một tập dữ liệu khổng lồ, việc xóa dấu ngoặc đơn và văn bản giữa nó có thể khiến bạn mất hàng giờ. Nhưng Tìm và Thay thế trong Excel có thể thực hiện việc này trong vòng chưa đầy 10 giây

1. Chọn dữ liệu
2. Chuyển đến Trang chủ -> Tìm và chọn -> Thay thế [Phím tắt – Control + H]
3. Trong Hộp thoại Tìm và Thay thế
   • Tìm gì. [*]
     Lưu ý rằng tôi đã sử dụng dấu hoa thị, là ký tự đại diện đại diện cho bất kỳ số lượng ký tự nào
   • Thay thế bằng. Để trống.

Tôi hy vọng bạn thấy những mẹo này hữu ích. Nếu có thủ thuật nào khác giúp bạn tiết kiệm thời gian, hãy chia sẻ với chúng tôi

Bạn cũng có thể thích các mẹo & hướng dẫn Excel sau đây

• 24 thủ thuật Excel giúp bạn vượt qua công việc hàng ngày
• 10 cách siêu gọn gàng để làm sạch dữ liệu trong bảng tính Excel
• 10 mẹo nhập dữ liệu Excel bạn không thể bỏ qua
• Bị bảng tính Excel chậm?
• Cách lọc các ô có định dạng phông chữ đậm trong Excel
• Cách chuyển đổi dữ liệu trong Excel

14 suy nghĩ về "Nhận được nhiều hơn từ Tìm và Thay thế trong Excel [4 Mẹo tuyệt vời]"

1. John Philippatos

   Ok, giả sử rằng tôi cần loại trừ các ô có giá trị gia tăng cụ thể, trong khi tôi cần tìm tất cả các ô khác không có chúng. Thí dụ;
   5 ô chứa từ “Bánh mì” và 5 ô khác chứa từ “Bánh mì trắng”. Giả sử tôi cần lệnh “Tìm” để chỉ hiển thị cho tôi các ô có giá trị “Bánh mì”. Nếu tôi nhấn CTRL + F và gõ “Bánh mì”, nó sẽ trả về tất cả 10 ô. Có ký tự đại diện nào sẽ làm ngược lại với “*” không? . Nếu tôi nhấn CTRL + F và nhập “#Wh#Bread”, nó sẽ chỉ cung cấp cho tôi 5 ô chỉ có giá trị “Bread”?
   Ngoài ra, nếu bạn chọn nhiều ô bằng cách giữ phím CTRL, có cách nào để bỏ chọn một ô đã chọn nhầm mà không phải lặp lại toàn bộ quá trình từ đầu không?
   Cảm ơn trước

2. Scott Hayden

   Bài đăng này là một vị cứu tinh. Cám ơn rất nhiều

3. Kristin P

   #4 Giải thích cách xóa từ vừa tiết kiệm cho tôi ít nhất vài giờ hôm nay. Cảm ơn bạn

4. Amr Ibrahim

   Sumit. Đây thực sự là một blog tuyệt vời. Tôi đã tải xuống ebook siêu hữu ích của bạn, sau đó đến đây để khám phá thêm nhiều thủ thuật hữu ích
   Cảm ơn bạn rất nhiều vì đã dành thời gian để viết, minh họa và chia sẻ kinh nghiệm quý báu của bạn. Điều này thực sự cao quý
   Nhiều đánh giá cao

   • Sumit Bansal

     Cảm ơn vì những lời tốt đẹp Amr. Rất vui vì bạn thấy các hướng dẫn và sách điện tử hữu ích 🙂

5. Kevin lehrbass

   Sumit, bạn nói đúng, đây là một bài viết tuyệt vời. Thanks

   • Sumit Bansal

     Cảm ơn Kevin. Rất vui vì bạn thích nó 🙂

6. Chaudhary Balkrishna

   Lời khuyên tuyệt vời Cảm ơn ngài

7. Chris vĩ mô

   Thật tuyệt vời, tôi vẫn còn nhớ lần đầu tiên tôi phát hiện ra rằng tôi có thể sử dụng Tìm/Thay thế để sửa đổi công thức. Thay đổi cuộc sống của tôi. Tôi sử dụng Tìm/Thay thế nhiều nhất khi cập nhật đường dẫn tệp cho các liên kết bên ngoài tới các sổ làm việc khác

   Một mẹo hay khác là bạn có thể thực hiện Tìm/Thay thế trên nhiều trang tính bằng cách chỉ cần tô sáng [hay còn gọi là nhóm] tất cả các trang tính bạn muốn tìm kiếm bên trong [Tôi đã từng nhấp qua từng tab và thực hiện Tìm/Thay thế cho tab đang hoạt động, rất tốn thời gian

   • Sumit Bansal

     Mẹo tuyệt vời Chris. Điều này chắc chắn sẽ tiết kiệm rất nhiều thời gian

8. Dave Bruns

   Danh sách mẹo hay. Tom Urtis có một mẹo khác mà bạn có thể muốn thêm vào – sử dụng tìm và thay thế để di chuyển công thức mà không thay đổi tham chiếu. http. //www. tập bản đồ. com/toms-tutorials-for-excel/toms-tutorials-for-excel-sao chép-công thức-trong khi-giữ-tham chiếu tương đối và tuyệt đối của họ/

   • Sumit Bansal

     Mẹo hay của Tom. Cảm ơn vì đã chia sẻ Dave 🙂

   • Sumit Bansal

     dave. Bạn đã cho tôi ý tưởng và tôi đã thử cách khác để làm điều tương tự [để sao chép tài liệu tham khảo]
     Chọn tất cả các ô có tham chiếu công thức cần sao chép -> thay đổi định dạng thành văn bản -> nhấp F2 và nhấn Control + Enter. Bây giờ sao chép bất cứ nơi nào bạn muốn, thay đổi định dạng chung, nhấp vào F2 và nhấn Control + Enter

     • Dave Bruns

       Sumit – Cách tiếp cận thú vị; . Mới thử nghiệm trên máy Mac [cần sử dụng control-U thay vì F2]. hoạt động tuyệt vời