Cách thực hiện một phép tính (chẳng hạn như hàm VLOOKUP) cho mỗi mục đã được Alt + Nhập trong một ô.
Xem video
- Người xem tải xuống dữ liệu từ một hệ thống mà mỗi mục được phân tách bằng Alt + Enter
- Bill: Tại sao bạn lại làm điều này? Người xem: Đó là cách tôi kế thừa dữ liệu. Tôi muốn giữ nó theo cách đó.
- Bill: Bạn muốn làm gì với 40% giá trị không có trong bảng? Người xem: Không có câu trả lời
- Bill: Có một cách phức tạp để giải quyết vấn đề này nếu bạn có các công cụ Power Query mới nhất.
- Thay vào đó, một Macro VBA để giải quyết nó - macro sẽ hoạt động trở lại Excel 2007
- Thay vì thực hiện hàm VLOOKUP, hãy thực hiện một loạt các Tìm & Thay thế bằng VBA
Bản ghi video
Học Excel Từ, Podcast Tập 2150: Vlookup Mỗi Alt + Giá trị đã Nhập trong Mỗi Ô.
Chào. Chào mừng bạn trở lại netcast. Tôi là Bill Jelen. Ngày nay, một trong những câu hỏi kỳ lạ hơn. Ai đó đã nói, này, tôi muốn thực hiện một VLOOKUP cho mỗi giá trị trong ô và khi tôi mở tệp Excel, dữ liệu đã được ALT + Nhập. Vì vậy, có 4 mục theo thứ tự này và tất cả chúng được phân tách bằng ALT + ENTER, sau đó chỉ có 2 ở đây và 6 ở đây, v.v.
Tôi quay lại với người đã gửi cái này. Tôi nghĩ, đây là một cách thực sự tồi để lưu trữ dữ liệu này. Tại sao anh làm điều này? Và anh ấy như thể, tôi như thể tôi không làm vậy. Đây là cách mà dữ liệu được tải xuống. Tôi nói, nếu tôi tách nó ra thành các hàng riêng biệt thì có được không? Không, bạn cần phải giữ nó theo cách này.
Ổn thỏa. Vì vậy, không có cách nào tốt để thực hiện một VLOOKUP cho từng mục riêng lẻ và ngày mai, vào tập ngày mai, 2151, tôi sẽ chỉ cho bạn cách chúng ta có thể sử dụng một tính năng hoàn toàn mới trong Power Query để thực hiện điều này nhưng bạn có để có Office 365 để có điều đó.
Vì vậy, hôm nay, tôi muốn sử dụng một phương pháp sẽ quay ngược trở lại và những gì tôi đã làm ở đây là tôi đã tạo một trang tính mới với LOOKUPTABLE, vì vậy đây là các mục. Tôi cũng nhận thấy rằng có rất nhiều thứ, khoảng 40% những thứ ở đây, không nằm trong loại CÓ THỂ NHÌN ĐƯỢC. Tôi nói, bạn muốn làm gì ở đó, và không có câu trả lời nào cho câu hỏi đó, vì vậy tôi sẽ để họ như cũ nếu tôi không tìm thấy đối tượng phù hợp.
Được rồi, vì vậy, những gì tôi có ở đây là tôi có một trang tính có tên LOOKUPTABLE và bạn sẽ thấy rằng tệp của tôi ngay bây giờ được lưu trữ dưới dạng xlsx và tôi sẽ sử dụng macro VBA. Để sử dụng macro VBA, bạn không thể có nó dưới dạng xlsx. Nó trái với các quy tắc. Vì vậy, bạn phải SAVE AS và lưu nó là xlsm. FILE, SAVE AS và thay đổi nó từ WORKBOOK thành WORKBLED WORKBOOK XLSM MACRO, hoặc BINARY WORKBOOK - một trong hai cách đó sẽ hoạt động - được rồi và nhấp vào LƯU.
Được rồi, bây giờ chúng tôi được phép chạy macro. ALT + F11 để truy cập trình ghi macro. Bạn bắt đầu với màn hình lớn màu xám này. INSERT, MODULE, và có mô-đun của chúng tôi, và đây là mã. Vì vậy, tôi gọi nó là ReplaceInPlace và tôi xác định một trang tính. Đó là Bảng tra cứu. Bạn sẽ đặt tên trang tính bảng tra cứu thực của mình ở đó, và sau đó bảng tra cứu của tôi bắt đầu ở cột A, là cột 1. Vì vậy, tôi đi đến hàng cuối cùng trong cột 1, nhấn phím KẾT THÚC và mũi tên LÊN, hoặc tất nhiên, CONTROL + mũi tên LÊN sẽ làm điều tương tự, tìm ra hàng đó là gì và sau đó chúng ta sẽ đi từ mỗi hàng từ 2 đến FinalRow. Tại sao 2? Chà, bởi vì các tiêu đề ở hàng 1. Vì vậy, tôi muốn thay thế, bắt đầu từ hàng 2 cho đến hàng cuối cùng, và vì vậy, đối với mỗi hàng từ 2 đến FinalRow, FromValue là gì 's trong cột A và ToValue là những gì trong cột B.
Bây giờ, nếu vì lý do nào đó, dữ liệu của bạn ở trong J và K, thì J này sẽ là cột thứ 10, vì vậy bạn đặt 10 ở đó và K sẽ là cột thứ 11, và sau đó, trong Lựa chọn, chúng ta sẽ thay thế Từ Giá trị đến Giá trị To. Điều này thực sự quan trọng ở đây. xlPart, xlPart - và đó là chữ L, không phải số 1 - xlPart nói rằng điều đó sẽ cho phép chúng ta thay thế một phần của ô vì các số phần đó đều được phân tách bằng ký tự dòng cấp. Mặc dù bạn không thể nhìn thấy nó, nó vẫn ở đó. Vì vậy, điều đó sẽ cho phép chúng tôi không vô tình cập nhật sai và sau đó xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Ổn thỏa. Vì vậy, đây là vĩ mô nhỏ của chúng tôi ở đây. Hãy thử nó. Chúng tôi sẽ lấy dữ liệu này và tôi không muốn phá hủy bất cứ thứ gì nên tôi chỉ lấy dữ liệu gốc và sao chép nó sang bên phải. Ổn thỏa. Vì vậy, chúng tôi có lựa chọn của chúng tôi ở đó. Thực ra, tôi sẽ bắt đầu từ điểm này. CONTROL + BACKSPACE, rồi ALT + F8 để nhận danh sách tất cả các macro. Đã có REPLACEINPLACE của chúng tôi. Tôi sẽ nhấp vào CHẠY và ở bất cứ nơi nào nó tìm thấy một mục trong LOOKUPTABLE, nó thay thế số mục đó bằng mục đó, dường như đang thực hiện một hàm VLOOKUP, mặc dù chúng tôi không giải quyết nó bằng một hàm VLOOKUP.
Ổn thỏa. Vì vậy, này, cuốn sách hoàn toàn mới đã ra mắt - Power Excel With, Phiên bản 2017, 617 Bí ẩn trong Excel đã được giải đáp - tất cả các loại mẹo mới tuyệt vời trong đó.
Tóm tắt của ngày hôm nay: người xem tải xuống dữ liệu từ một hệ thống trong đó mỗi mục được phân tách bằng ALT + ENTER, sau đó cần thực hiện VLOOKUP ở mỗi mục và bạn biết đấy, tại sao tôi lại làm điều này; vì vậy, người đó nói, tôi không làm điều đó nhưng tôi cần phải giữ nó theo cách này; và 40% giá trị không có trong bảng, tốt, không có câu trả lời; vì vậy tôi đoán họ sẽ thêm những mục đó vào bảng; bây giờ, ngày mai, chúng ta sẽ nói về cách giải quyết vấn đề này với Power Query, nhưng hôm nay, macro này sẽ hoạt động trở lại tất cả các phiên bản Windows của Excel, ít nhất là quay trở lại Excel 2007; vì vậy, thay vì một VLOOKUP, chỉ một loạt các tìm và thay thế bằng VBA.
Chà, này. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2150.xlsm
