Dò tìm bậc Lương vào bảng tổng từ sheet khác.

[Yumi_000]

Chào mọi người,
Em tạo công thức tìm chức danh công việc của công nhân dựa vào bậc lương của các sheet khác mà sao công thức báo lỗi hoài.
Gửi file lên mọi người xem dùm em với nhé.
Cảm ơn nhiều.
Giúp em sớm nha các bác.
Yumi.

 

[ndu96081631]

Chào mọi người,
Em tạo công thức tìm chức danh công việc của công nhân dựa vào bậc lương của các sheet khác mà sao công thức báo lỗi hoài.
Gửi file lên mọi người xem dùm em với nhé.
Cảm ơn nhiều.
Giúp em sớm nha các bác.
Yumi.

Name đâu mà nhiều thê?
Tôi chỉ cần 2 name là đủ làm yêu cầu của bạn ---> Cái này gọi là VLLOKUP trên nhiều sheet (Lở bạn có 200 sheets, chả lẽ đặt 200 name)
Xem file nha!
Yêu cầu duy nhất: Cấu trúc các sheet phải giống nhau...

 

[viendo]

Tôi chỉ cần 2 name là đủ làm yêu cầu của bạn

Phiền Anh NDU và các AC giải thích giúp công thức trong Name để xác định sheet tham chiếu. Ngẫm tới ngẫm lui mà không có hiểu

=INDIRECT("'"&INDEX(ShName,MATCH(1,COUNTIF(INDIRECT("'"&ShName&"'!B10:B100"),BH!$B6),0),)&"'!B10:E100")

Thanks.

 

[tigertiger]

Name đâu mà nhiều thê?
Tôi chỉ cần 2 name là đủ làm yêu cầu của bạn ---> Cái này gọi là VLLOKUP trên nhiều sheet (Lở bạn có 200 sheets, chả lẽ đặt 200 name)
Xem file nha!
Yêu cầu duy nhất: Cấu trúc các sheet phải giống nhau...

Yêu cầu duy nhất: Cấu trúc các sheet phải giống nhau..

và Giá trị tìm phải là duy 1 trong 1 sheet -> không có MATCH() sẽ sai

đúng không a?

 

[ndu96081631]

Phiền Anh NDU và các AC giải thích giúp công thức trong Name để xác định sheet tham chiếu. Ngẫm tới ngẫm lui mà không có hiểu
Thanks.

Công thức này:

VLOOKUP(BH!$B6,INDIRECT("'"&INDEX(ShName,MATCH(1,COUNTIF(INDIRECT("'"&ShName&"'!B10:B100"),BH!$B6),0),)&"'!B10:E100"),4,0)

Đầu tiên bắt nguồn từ:

COUNTIF(INDIRECT("'"&ShName&"'!B10:B100"),BH!$B6)

1> Nghĩa là đếm giá trị B6 trong tất cả các sheet có vùng tham chiếu là B10:B100
2> Công thức này sẽ tạo ra 1 mãng là kết quả của từng COUNIF (trong từng sheet)
3> Giã định rằng giá trị B6 là duy nhất (chỉ tồn tại duy nhất trong 1 sheet, duy nhất trong 1 cell nào đó, hoặc không tồn tại) ---> Vậy mãng thu được sẽ có dạng:

0;0;...;1;0;0;0;0....

Với tổng số phần tử đúng bằng tổng số Sheet... và số 1 nằm tại vị trị nào thì nó cũng ngầm chỉ định vị trí của sheet (mà giá trị B6 tồn tại)
4> Dùng INDEX kết hợp với hàm MATCH(1,....,0) ta tìm được tên sheet
5> Phối hợp tiếp với INDIRECT cùng VLOOKUP ta hoàn toàn có thể tìm được giá trị ta cần!
(chú ý: Name ShName chứa tên của tất cả các Sheet)
Giã định tại mục 3 chính là điều mà Tigertiger đã nhận xét

 

[anhtuan1066]

Cải tiến thêm 1 bước, bỏ luôn cột phụ (chứa tên sheet, phòng ngừa người dùng xóa hoặc thêm sheet thì công thức sẽ sai)
File mới có tất cả 4 name, trong đó có 2 name xác định tên sheet (Get_SheetName đã từng đăng trên diển đàn)... Hơi "mắc cổ" 1 chút, các bạn chịu khó tìm hiểu nhé

NoSs =GET.WORKBOOK(4+NOW()*0)

AlSh =SUBSTITUTE(GET.WORKBOOK(1+NOW()*0),"["&GET.WORKBOOK(16+NOW()*0)&"]","")

DK =MAX((COUNTIF(INDIRECT("'"&AllSh&"'!B10:B100"),BH!$B6))*TRANSPOSE(ROW(INDIRECT("1:"&NoSs))))

XL =VLOOKUP(BH!$B6,INDIRECT("'"&INDEX(AllSh,,DK)&"'!B10:E100"),4,0)

Cuối cùng là công thức tại B6

B6 =IF(DK<2,"",IF(OR(XL="D",XL="D+"),"LDP","CNMAY"))

Ưu điểm của file mới: Có thể thêm, xóa bớt sheet thoải mãi mà ko ảnh hưởng gì đến công thức
Yêu cầu vẩn như trên: Cấu trúc các sheet nhập liệu phải giống nhau và không chứa dử liệu trùng

 

[tigertiger]

Yêu cầu vẩn như trên: Cấu trúc các sheet nhập liệu phải giống nhau và không chứa dử liệu trùng

Nếu có giống nhau thì xử lý thế nào?

Hoặc giá trị trên nhiều SHEET thì sao a?

hỏi thế nếu nặng thì bỏ qua a ah, vì hình như thế không thực tế

A chỉ cần trả lời có / không khả thi? và có nên xét thế hay không?
.

 

[anhtuan1066]

Nếu có giống nhau thì xử lý thế nào?

Hoặc giá trị trên nhiều SHEET thì sao a?

hỏi thế nếu nặng thì bỏ qua a ah, vì hình như thế không thực tế

A chỉ cần trả lời có / không khả thi? và có nên xét thế hay không?
.

OK... về nguyên tắc là xử lý được hết nếu biết trước yêu cầu của tác giã, đúng ko?
Cũng giống như mấy câu hỏi dò tìm theo MAX, MIN chẳng hạn! Giã sử có 2 hoặc nhiều giá trị MAX, MIN thì lấy cái nào?
Tóm lại vẩn là: Tác giã yêu cầu trước, ta sẽ chiều theo (tôi đã nghĩ đến hướng này rồi, nhưng nếu "rào đón" tất cả các điều kiện thì công thức sẽ rất phức tạp... Ah! Phức tạp thôi chứ ko phải là ko thể làm được)
Hà... hà...
-------------------
Một ví dụ về cải tiến theo ý Tigertiger!
Name củ của tôi:

DK =MAX((COUNTIF(INDIRECT("'"&AllSh&"'!B10:B100"),BH!$B6))*TRANSPOSE(ROW(INDIRECT("1:"&NoSs))))

Tôi sửa lại thành:

DK =MAX((COUNTIF(INDIRECT("'"&AllSh&"'!B10:B100"),BH!$B6)>0)*TRANSPOSE(ROW(INDIRECT("1:"&NoSs))))

"Rào" trường hợp 1 giá trị tồn tại trong nhiều sheet hoặc nhiều cell
Khi ấy với thuật toán trên nó sẽ lấy theo:
- Nếu giá trị trùng nằm trên nhiều sheet thì ưu tiên lấy sheet nằm bên phải
- Nếu giá trị trùng nằm trong 1 sheet trên nhiều cells thì ưu tiên lấy giá trị đầu tiên (dòng trên)
Giãi quyết được 1 đoạn khá dài đấy chứ

 

[Yumi_000]

Cải tiến thêm 1 bước, bỏ luôn cột phụ (chứa tên sheet, phòng ngừa người dùng xóa hoặc thêm sheet thì công thức sẽ sai)
File mới có tất cả 4 name, trong đó có 2 name xác định tên sheet (Get_SheetName đã từng đăng trên diển đàn)... Hơi "mắc cổ" 1 chút, các bạn chịu khó tìm hiểu nhé




Cuối cùng là công thức tại B6

Ưu điểm của file mới: Có thể thêm, xóa bớt sheet thoải mãi mà ko ảnh hưởng gì đến công thức
Yêu cầu vẩn như trên: Cấu trúc các sheet nhập liệu phải giống nhau và không chứa dử liệu trùng

Sao file của bác anhtuan1066 em mở ra,ở cột cần tìm giá trị báo lỗi là sao? bác xem lại giúp em với.
rất cảm ơn mọi người đã ra tay giúp đỡ cho bài gửi của em .
Yumi.

 

[ndu96081631]

Sao file của bác anhtuan1066 em mở ra,ở cột cần tìm giá trị báo lỗi là sao? bác xem lại giúp em với.
rất cảm ơn mọi người đã ra tay giúp đỡ cho bài gửi của em .
Yumi.

Ah... cái file của anhtuan có dùng các hàm Macro4 nên để chạy được thì:
- Đặt Security ở mức Medium (Trong menu Tools\Macro\Security)
- Mở file gặp thông báo thì bấm Yes
Thử lại xem!

 

[anhtuan1066]

Bài toán trên vẩn còn rất nhiều chổ cần cải tiến
- Giã định rằng Sheet BH nằm ở 1 ví trí bất kỳ khác (nằm xen vào giữa các sheet hoặc nằm ở cuối cùng)... Vậy công thức sẽ phải sửa lại thế nào đây?
Mời các bạn tiếp tục!
(Khó đây! Tôi vẩn đang loay hoay vì chưa nghĩ ra được biện pháp tối ưu nhất)

 

[Yumi_000]

Bài toán trên vẩn còn rất nhiều chổ cần cải tiến
- Giã định rằng Sheet BH nằm ở 1 ví trí bất kỳ khác (nằm xen vào giữa các sheet hoặc nằm ở cuối cùng)... Vậy công thức sẽ phải sửa lại thế nào đây?
Mời các bạn tiếp tục!
(Khó đây! Tôi vẩn đang loay hoay vì chưa nghĩ ra được biện pháp tối ưu nhất)

Em vẫn đang chờ cao kiến của các bác đây.NẾu không tìm được giải pháp nào tốt hơn,em sẽ để sheet BH ở vị trí nhất định như trong file,vậy chắc là không có vấn đề gì đúng không ạ?
Yumi.

 

[anhtuan1066]

Em vẫn đang chờ cao kiến của các bác đây.NẾu không tìm được giải pháp nào tốt hơn,em sẽ để sheet BH ở vị trí nhất định như trong file,vậy chắc là không có vấn đề gì đúng không ạ?
Yumi.

Có rồi đây! Với file cải tiến mới này thì bạn có thể đặt Sheet BH nằm ở bất cứ ví trí nào (tên sheet có thể đổi thoải mái)
Vẩn dựa trên nền của file củ, chỉ sửa lại 1 tí ở name DK
Công thức ở file củ:

DK =MAX((COUNTIF(INDIRECT("'"&AllSh&"'!B10:B100"),BH!$B3))*TRANSPOSE(ROW(INDIRECT("1:"&NoSs))))

F6 =IF(DK<2,"",IF(OR(XL="D",XL="D+"),"LDP","CNMAY"))

Sửa lại thành:

DK =MAX(IF(AllSh=GET.WORKBOOK(38+NOW()*0),"",(COUNTIF(INDIRECT("'"&AllSh&"'!B10:B100"),BH!$B6))*TRANSPOSE(ROW(INDIRECT("1:"&NoSs)))))

F6 =IF(DK=0,"",IF(OR(XL="D",XL="D+"),"LDP","CNMAY"))