Note: The other languages of the website are Google-translated. Back to English

Làm cách nào để sao chép định dạng nguồn của ô tra cứu khi sử dụng Vlookup trong Excel?

Trong các bài trước, chúng ta đã nói về việc giữ màu nền khi các giá trị vlookup trong Excel. Ở đây trong bài viết này, chúng tôi sẽ giới thiệu một phương pháp sao chép tất cả định dạng ô của ô kết quả khi thực hiện Vlookup trong Excel. Hãy làm như sau.

Sao chép định dạng nguồn khi sử dụng Vlookup trong Excel với chức năng Do người dùng xác định


Sao chép định dạng nguồn khi sử dụng Vlookup trong Excel với chức năng Do người dùng xác định

Giả sử bạn có một bảng như hình minh họa bên dưới. Bây giờ bạn cần kiểm tra xem một giá trị được chỉ định (trong cột E) có nằm trong cột A hay không và trả về giá trị tương ứng với định dạng trong cột C. Hãy làm như sau để đạt được nó.

1. Trong trang tính có chứa giá trị bạn muốn vlookup, nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh. Xem ảnh chụp màn hình:

2. Trong phần mở đầu Microsoft Visual Basic cho các ứng dụng , vui lòng sao chép mã VBA bên dưới vào cửa sổ Mã.

Mã VBA 1: Vlookup và trả về giá trị với định dạng

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. Sau đó nhấn vào Chèn > Mô-đunvà sao chép mã VBA 2 bên dưới vào cửa sổ Mô-đun.

Mã VBA 2: Vlookup và trả về giá trị với định dạng

Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = " "
        xDic.Add Application.Caller.Address, " "
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
    End If
    Application.ScreenUpdating = True
End Function

4. nhấp chuột CÔNG CỤ > dự án. Sau đó kiểm tra Thời gian chạy Microsoft Script hộp trong Tài liệu tham khảo - VBAProject hộp thoại. Xem ảnh chụp màn hình:

5. Nhấn nút Khác + Q chìa khóa để thoát khỏi Microsoft Visual Basic cho các ứng dụng cửa sổ.

6. Chọn một ô trống bên cạnh giá trị tra cứu, sau đó nhập công thức =LookupKeepFormat(E2,$A$1:$C$8,3) vào Thanh công thức, và sau đó nhấn đăng ký hạng mục thi Chìa khóa.

Chú thích: Trong công thức, E2 chứa giá trị bạn sẽ tra cứu, $ A $ 1: $ C $ 8 là phạm vi bảng và số 3 nghĩa là giá trị tương ứng bạn sẽ trả về nằm trong cột thứ ba của bảng. Vui lòng thay đổi chúng khi bạn cần.

7. Tiếp tục chọn ô kết quả đầu tiên, và sau đó kéo Fill Handle xuống để nhận tất cả các kết quả cùng với định dạng của chúng như ảnh chụp màn hình bên dưới.


Các bài liên quan:


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (42)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
nó cung cấp cho tôi lỗi biên dịch, lỗi cú pháp

xin vui lòng giúp đỡ
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Ngày tốt,
Mã đã được cập nhật trong artcle. Cảm ơn bình luận của bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi cũng gặp lỗi trình biên dịch.
Nó sẽ được sửa nếu bạn thay đổi biến sau bằng "" thực tế. Không ';' ở giữa.
LookupKeepFormat = ""
xDic.Add Application.Caller.Address, ""
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,
Xin lỗi vì nhầm lẫn, mã đã được cập nhật trong bài viết.
Sai lầm "" phải là hai dấu ngoặc kép "". Cảm ơn bình luận của bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi cũng gặp lỗi tương tự.

Bạn sẽ phải thay đổi "" cho thực tế "", không có ';' như được chỉ ra dưới đây
LookupKeepFormat = ""
xDic.Add Application.Caller.Address, ""

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,
Xin lỗi vì nhầm lẫn, mã đã được cập nhật trong bài viết. Cảm ơn bạn đã chia sẻ.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Điều này là rất tốt, cảm ơn bạn! Vấn đề duy nhất là, tôi thấy nó hoạt động tốt nếu tôi đang tìm kiếm trong cùng một trang tính, nhưng không thể làm cho nó hoạt động khi tôi đang cố gắng thực hiện tra cứu trong một trang tính riêng biệt với dữ liệu nguồn. Sẽ tiếp tục cố gắng
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Julia, hãy sửa lại dòng này:
trong Hàm LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset (0, xCol - 1) .Address & "|" & LookupRng.Parent.Name

trong Sub Worksheet_Change:
Trang tính (Tách (xDic.Items (I), "|") (1)). Phạm vi (Tách (xDic.Items (I), "|") (0)). Sao chép
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Này Hugo,


Tôi có cùng một vấn đề như Julia. Nó không hoạt động trên các trang tính khác. Bạn có thể giúp viết mã cho toàn bộ hàm và trang tính con không? Tôi không chắc chắn nơi thay thế / chèn xDic.Add Application.Caller.Address, xFindCell.Offset (0, xCol - 1) .Address & "|" & LookupRng.Parent.Nam và Sheets (Split (xDic.Items (I), "|") (1)). Range (Split (xDic.Items (I), "|") (0)). Sao chép


cảm ơn đáp lại
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đánh giá rất cao Hugo tiếp theo!
Thật không may, giống như Vi, tôi là một người mới làm quen để tìm ra nơi để chèn các bản sửa lỗi mã đề xuất của bạn ...

Cảm ơn một lần nữa, chúc một ngày tốt lành :)
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào


Tôi đã cố gắng sử dụng mã nhưng tôi gặp lỗi trong hình đính kèm. Bất kỳ hỗ trợ sẽ được đánh giá rất cao.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,
Xin lỗi vì nhầm lẫn, mã đã được cập nhật trong bài viết. Cảm ơn bình luận của bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

Tôi không gặp lỗi và nó thực hiện việc tra cứu, nhưng vì giá trị tra cứu của tôi nằm trên một trang tính khác (một trường hợp có thể xảy ra hơn), nó không kéo định dạng. Có sự điều chỉnh đối với mã mà tôi có thể thực hiện cho điều đó không? (Hãy nói thật cụ thể về nơi cần phải thay đổi vì tôi là người mới viết mã) Cảm ơn bạn! Tôi rất vui được thêm tính năng này vào một trong các bảng tính của mình !!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, bạn may mắn cho câu hỏi này, làm cách nào để chúng ta có thể tra cứu định dạng trên các trang tính?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cũng đang tìm kiếm sự tinh chỉnh.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Ngoài ra, nếu tôi thêm công thức của bạn như một phần của câu lệnh "Nếu" (xem bên dưới), nó sẽ định dạng ô theo cách mà nó muốn LOL (hoặc ít nhất là nó có vẻ như vậy. Một ô, văn bản được làm mờ và in đậm với đường viền trên ô; một ô khác, văn bản được căn giữa)


= IF ($ F19 = "", "", LookupKeepFormat (F19, 'Item #s'! $ A $ 1: $ M $ 1226,2))
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đã thử cái này và cái chỉ kéo nền màu và gặp lỗi tương tự. Lỗi biên dịch: Đã phát hiện tên không rõ ràng. Tôi nhấp vào OK và nó đánh dấu xDic. Bất kỳ đề xuất? Tôi không quá quen thuộc với tất cả những điều này vì vậy xin vui lòng giúp đỡ / giải thích :) cảm ơn trước
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Jeni,
Đừng quên bật tùy chọn Microsoft Script Runtime như đã đề cập trong bước 4.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào. Tôi đã tạo một bảng tính trống và sao chép ví dụ của bạn trong Excel 2013, nhưng vẫn gặp lỗi Biên dịch: Lỗi cú pháp và Dim I As Long được đánh dấu. Có điều gì tôi đang thiếu? Tôi rất thích làm việc này. Cảm ơn bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Hi Laura,
Đừng quên bật tùy chọn Microsoft Script Runtime như đã đề cập trong bước 4.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi đã sử dụng mã trên trong Excel 2010 mà không có vấn đề gì cho đến nay. Tuy nhiên, gần đây tôi đã được nâng cấp lên Office 2016 và bây giờ mã bị lỗi Excel mỗi khi tôi cố gắng điền vào nhiều hơn một hàng. Thật không may, nó không phải là lỗi cho tôi ngoài "Microsoft Excel đã ngừng hoạt động". Tôi tự hỏi liệu bạn đã gặp vấn đề này trước đây chưa và nếu có điều gì tôi cần làm để nó hoạt động trong năm 2016. Cảm ơn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Leigh,
Mã hoạt động tốt trong Excel 2016. Chúng tôi đang cố gắng nâng cấp mã để giải quyết vấn đề. Cảm ơn bình luận của bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Cảm ơn vì mã. Tôi không nhận được bất kỳ thông báo lỗi nào nhưng công thức chỉ hoạt động như một vlookup bình thường. bạn có thể hỗ trợ tôi không? Cảm ơn vì đã dành thời gian cho tôi.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào

Tôi có chính xác vấn đề tương tự, bạn đã tìm ra cách giải quyết nó chưa?

Cảm ơn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
xin chào, tôi gặp lỗi "khi biên dịch Lỗi: Đã phát hiện ra tên bất thường: xDic
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
xin chào, tôi gặp lỗi "khi biên dịch Lỗi: Đã phát hiện ra tên bất thường: xDic
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi mới sử dụng VBA và đã thử sử dụng mã này trong bảng tính của mình, nhưng định dạng văn bản trên tab Rec2 không chuyển sang tab Rec khi sử dụng tra cứu. Mọi sự trợ giúp sẽ rất được trân trọng. Cảm ơn Pat
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đây là tệp và ảnh
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi gặp cùng một lỗi tên không rõ ràng - đã có ai giải quyết được lỗi này chưa?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi gặp cùng một lỗi tên không rõ ràng - đã có ai giải quyết được lỗi này chưa?
Không có bình luận nào được đăng ở đây
Tải thêm

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL