MS Excel: Cần trợ giúp để lấy giá trị từ một trang tính khác có nhiều điều kiện

MS Excel: Cần trợ giúp để lấy giá trị từ một trang tính khác có nhiều điều kiện

Kính thưa tất cả,

Hôm nay tôi đến đây để tìm kiếm sự giúp đỡ cho một tình huống tôi đang gặp phải. Tôi có một tệp Excel với 2 trang tính khác nhau. Vì tôi đang làm việc trên Dữ liệu chính thức và không thể chia sẻ dữ liệu thực tế, nên tôi đã tạo một tệp mẫu để giải thích rõ hơn.

Tôi muốn chọn các giá trị từ Bảng 2 theo các điều kiện sau:

Điều kiện thứ nhất - Phù hợp với ID nhân viên (Emp ID)

Điều kiện thứ 2 - Ngày thi đấu có trong Bảng 1

Điều kiện thứ 3 - nếu Ngày không khớp, thì nó sẽ tìm kiếm mục nhập sớm nhất có thể và đặt giá trị

Hình ảnh của Tờ 1

Hình ảnh của Tờ 2

Dữ liệu văn bản của Bảng 1

Ngày ID nhân viên Việc kinh doanh Nhận xét
5-tháng 1-16 12345 ! ở đây đầu ra phải là Biz 2 của Ngày 3-Jan-16
13-01-16 12345 ! ở đây đầu ra phải là Biz 4 của Ngày 12-01-16
7-tháng 2-16 12345 ! ở đây đầu ra phải là Biz 6 của Ngày 5-Feb-16
21-02-16 12345 ! ở đây đầu ra phải là Biz 7 của Ngày 16-02-16
3-tháng 1-16 33456 ! ở đây đầu ra phải là Biz 11 của Ngày 3-Jan-16
7-tháng 1-16 33456 ! ở đây đầu ra phải là Biz 12 của Ngày 4-Jan-16
12-03-16 33456 ! ở đây đầu ra phải là Biz 16 của Ngày 14-03-16

Dữ liệu văn bản của Bảng 2

Ngày ID nhân viên Việc kinh doanh Thẳng đứng
1-tháng 1-16 12345 Kinh doanh 1 Dọc 1
2-tháng 1-16 12345 Kinh doanh 1 Dọc 2
3-tháng 1-16 12345 Kinh doanh 2 Dọc 3
8-tháng 1-16 12345 Kinh doanh 3 Dọc 4
12-01-16 12345 Kinh doanh 4 Dọc 5
22-01-16 12345 Kinh doanh 5 Dọc 6
5-tháng 2-16 12345 Kinh doanh 6 Dọc 7
16-02-16 12345 Kinh doanh 7 Dọc 8
28-02-16 12345 Kinh doanh 8 Dọc 9
1-tháng 1-16 33456 Kinh doanh 9 Dọc 10
2-tháng 1-16 33456 Kinh doanh 10 Dọc 11
3-tháng 1-16 33456 Kinh doanh 11 Dọc 12
4-tháng 1-16 33456 Kinh doanh 12 Dọc 13
11-01-16 33456 Kinh doanh 13 Dọc 14
20-02-16 33456 Kinh doanh 14 Dọc 15
3-3-16 33456 Kinh doanh 15 Dọc 16
14-03-16 33456 Kinh doanh 16 Dọc 17




Trả lời:

Xem ảnh chụp màn hình

Trong cột E của Sheet2, tôi đã sử dụng một cột trợ giúp, nối ID và Ngày

Công thức trong C2 là: =INDEX(Sheet2!$C$2:$C$18,MATCH(B2&A2,Sheet2!$E$2:$E$18,1))

Trong D2: =INDEX(Sheet2!$D$2:$D$18,MATCH(B2&A2,Sheet2!$E$2:$E$18,1))

Tôi xin lưu ý rằng Quy tắc 3 của bạn (nếu Ngày không khớp, thì phải tìm mục nhập sớm nhất có thể và đặt giá trị) yêu cầu hàng cuối cùng trong Trang tính 1 phải định vị Biz15 (và Dọc 16) thay vì Biz 16 vì ngày 3 tháng 3 sớm hơn ngày 12 tháng 3 trong khi ngày 14 tháng 3 muộn hơn .

Nếu bạn cần một giải pháp trong đó các ID khớp nhau nhưng lại cần ngày gần nhất , thì tôi nghĩ bạn sẽ cần đến phương pháp VBA.

lời chúc tốt đẹp nhất



Trả lời:

Đây là giải pháp VBA tìm ngày gần nhất chứ không phải ngày sớm nhất .

Cột E trong Sheet2 không cần thiết

Trong C2 nhập =Biz(A2,B2), trong D2 nhập =Vert(A2,B2) và kéo cả hai xuống các cột

Tôi có một hàm cho Biz và Vert nhưng chỉ cần viết một hàm duy nhất là được. Tuy nhiên, nó sẽ là một công thức mảng và một số người thích dùng chúng.

lời chúc tốt đẹp nhất

Hàm Biz(myDate, myId)
Biz = "Không tìm thấy"
mymin = 9999
Với Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Đối với j = 2 Đến LastRow
Nếu .Cells(j, "B") = myId thì
kiểm tra = Abs(myDate - .Cells(j, "A"))
Nếu kiểm tra < mymin thì
mymin = kiểm tra
myindex = j
Kết thúc nếu
Kết thúc nếu
Tiếp theo j
Biz = .Cells(myindex, "C")
Kết thúc bằng
Chức năng kết thúc

Hàm Vert(myDate, myId)
Vert = "Không tìm thấy"
mymin = 9999
Với Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Đối với j = 2 Đến LastRow
Nếu .Cells(j, "B") = myId thì
kiểm tra = Abs(myDate - .Cells(j, "A"))
Nếu kiểm tra < mymin thì
mymin = kiểm tra
myindex = j
Kết thúc nếu
Kết thúc nếu
Tiếp theo j
Vert = .Cells(myindex, "D")
Kết thúc bằng
Chức năng kết thúc



Trả lời:

Xin chào Bernard,

Cảm ơn bạn đã trả lời nhanh chóng. Giải pháp của bạn khá gần với những gì tôi đang tìm kiếm, tuy nhiên, như bạn đã khuyên, sẽ không cần sheet 2 - điều này là không thể. Sheet 2 trong trường hợp thực tế là bắt buộc. Để tôi kể cho bạn nghe bối cảnh những gì tôi đang làm.

Tôi đang tạo một bảng thông tin nhân sự, nơi chúng ta sẽ hiển thị tất cả các khía cạnh của nhân sự trong một chế độ xem duy nhất. Tỷ lệ hao hụt là một trong những thành phần chính của bảng thông tin này và động lực kinh doanh của chúng tôi là nhân viên liên tục di chuyển đến nhiều LoB & Hàng đợi khác nhau theo thời gian. Trong vòng một tháng, chúng ta có thể dễ dàng thấy 20% chuyển động, do đó chúng ta phải duy trì dữ liệu lịch sử để xác định vị trí thực sự của nhân viên đã bắt đầu từ chức. Nhân viên thường khởi xướng từ chức trong một LoB và thông báo trong thời gian thông báo trong một LoB khác, nhưng tỷ lệ hao hụt phải được gắn thẻ trong LoB nơi đã bắt đầu Từ chức. Ngoài ra, cùng một Dữ liệu lịch sử được tham chiếu trong Đánh giá hiệu suất, Thủ tục kỷ luật, v.v. Ngoài ra, dữ liệu trang tính 2 có tính chất gia tăng và ít nhất 40 - 50 mục được thêm vào mỗi ngày và sẽ tiếp tục được thêm vào trong cả năm (tháng 1 - tháng 12). Do đó, chúng ta phải giữ trang tính 2 riêng biệt.

Vì tôi đã cung cấp cho bạn toàn bộ thông tin cơ bản, vui lòng gợi ý xem mã VBA được đề cập ở trên có thể được sử dụng nguyên trạng hay cần phải điều chỉnh gì không.

Cảm ơn bạn một lần nữa.

Trân trọng,

Ankur Goswami



Trả lời:

Xin chào Bernard,

Còn một điều nữa. Tôi đã thử sao chép và dán mã nhưng không được. Tôi chắc chắn mình đã làm sai cách. Bạn có thể hướng dẫn tôi một chút được không?

Trân trọng,

Ankur Goswmai



Trả lời:

Cột E trong trang tính 2 không bắt buộc - không phải toàn bộ trang tính

Để tôi có thể tìm ra lỗi sai ở đâu thì cách dễ nhất là tôi phải có một bản sao sổ làm việc của bạn

Hoặc đăng một bản sao lên OneDrive (hoặc trang web chia sẻ tệp tương tự) và cho chúng tôi biết URL hoặc truy cập trang web của tôi (xem liên kết people..... ở cuối tin nhắn), lấy địa chỉ email của tôi và gửi tệp cho tôi qua email riêng tư

lời chúc tốt đẹp nhất



Trả lời:

CHÀO,

Trong ô C2 của trang tính 1, nhập công thức này và sao chép xuống

=LOOKUP(2,1/((Sheet2!$A$2:$A$18<=Sheet1!A2)*(Sheet2!$B$2:$B$18=Sheet1!B2)),Sheet2!$C$2:$C$18)

Trong ô D2 của trang tính 1, nhập công thức này và sao chép xuống

=LOOKUP(2,1/((Sheet2!$A$2:$A$18<=Sheet1!A2)*(Sheet2!$B$2:$B$18=Sheet1!B2)),Sheet2!$D$2:$D$18)

Để công thức của tôi hoạt động, điều quan trọng là phải đảm bảo rằng Emp ID của sheet2 trước tiên phải theo thứ tự tăng dần và sau đó là Date cũng theo thứ tự tăng dần.

Comments

Post a Comment

Popular posts from this blog

Excel 2016 - mở tất cả các tệp trong MỘT phiên bản

Image
Excel 2016 - mở tất cả các tệp trong MỘT phiên bản Xin chào, Tôi gặp sự cố với Excel 2016 (16.0.10228.20051 32-bit): Tôi có một phiên bản Excel đang chạy. (NB - hộp kiểm " Bỏ qua các ứng dụng khác sử dụng Trao đổi dữ liệu động (DDE) " trong Tùy chọn nâng cao KHÔNG được chọn.) Điều tôi cần là - đối với tất cả các tệp Excel mà tôi sắp mở để mở trong phiên bản Excel đầu tiên và duy nhất của mình e, luôn luôn và bất kể: - tôi mở bao nhiêu tập tin cùng một lúc, - nơi chứa các tập tin (tức là trên ổ đĩa cục bộ hoặc trên OneDrive nên hệ thống cần tải chúng xuống trước khi mở) và kể cả khi mở tệp Excel từ các ứng dụng Office khác, tức là tệp đính kèm Outlook. Hành vi mà tôi thấy trong Excel 2016 tốt nhất là không nhất quán: 1. Khi mở nhiều tệp bổ sung cùng một lúc từ đĩa (chọn nhiều tệp/nhấp chuột phải/Mở) - nó sẽ mở n tệp đầu tiên trong phiên bản Excel đầu tiên, nhưng sau đó từ tệp n +1, nó bắt đầu mở chúng trong phiên bản mới . 2. Một biến thể của trường hợp trên là k...
Read more

Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể.

Image
Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể. Trong excel 2003, trong mục tùy chỉnh thanh công cụ, người ta có thể lấy một nút và chỉnh sửa hình ảnh của nút đó. Điều tôi muốn làm trong excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy cập nhanh và để nút đó chạy một macro cụ thể. Ví dụ, tạo nút có chữ G lớn và khi nhấp vào, nút sẽ thực hiện truy vấn web và nhập giá vàng từ kitco.com. Có cách nào để thực hiện việc này trong Excel 2010 không? Trả lời: Xin chào, Để bật tab Nhà phát triển 1. Trên tab Tệp , chọn Tùy chọn để mở hộp thoại Tùy chọn . 2. Nhấp vào Tùy chỉnh Ribbon ở phía bên trái của hộp thoại. 3. Trong mục Chọn lệnh từ ở phía bên trái hộp thoại, chọn Lệnh phổ biến . 4. Trong mục Tùy chỉnh Dải băng ở phía bên phải hộp thoại, chọn Tab chính trong hộp danh sách thả xuống, sau đó chọn hộp kiểm Nhà phát triển . 5. Nhấp vào OK . Để biết thêm thông tin về cách tạo ...
Read more

Mở tài liệu Excel và Word từ Outlook Lỗi - Không đủ bộ nhớ

Image
Mở tài liệu Excel và Word từ Outlook Lỗi - Không đủ bộ nhớ Chào mọi người, Tôi biết có nhiều bài viết liên quan đến vấn đề này nhưng câu trả lời không thỏa đáng với môi trường của tôi. Chúng tôi xử lý số lượng tài liệu mỗi ngày và lưu vào máy tính để bàn -> việc bỏ chặn không cắt được tài liệu đó cũng như vô hiệu hóa chế độ xem được bảo vệ bằng phần mềm ransomware gần đây mà NHS nhận được (bảo mật cần được giữ nguyên). Thông báo có nội dung: 'Không thể mở hoặc lưu thêm bất kỳ tài liệu nào vì không có đủ bộ nhớ hoặc dung lượng ổ đĩa' -> mặc dù việc thực hiện 2 giải pháp trên sẽ khắc phục được điều này (đó không phải là bộ nhớ, chỉ là một tin nhắn ngu ngốc nếu tôi thành thật) . Tuy nhiên, tôi chỉ cần có thể mở tệp ở chế độ xem được bảo vệ, không gặp lỗi. Có ai có thể sửa lỗi này 'đúng cách' chưa? Nếu vậy xin vui lòng cho tôi biết để tôi có thể triển khai rộng rãi hoạt động kinh doanh này. Tôi đã tìm thấy cài đặt gpe trong cấu hình người dùng -> thành phần windo...
Read more