Import data từ sheet tới sheet khác (1 người xem)

[giatthang]

Nhờ các bạn giúp mình bài toán này với.
Mình có một file excel có 2 sheet (1 sheet là dữ liệu chấm công của nhân viên, 1 sheet mình cần import số liệu từ sheet chấm công.).
Cụ thể như file đính kèm mình update lên. Mình muốn mọi số liệu trong sheet "7.2017" sẽ được import vào sheet "Data".
Ví dụ: Vũ Tiến Thành được chấm công từ ngày 01/07/2017 tới 31/07/2017 sẽ lấy các giá trị sang sheet "Data" tương ứng từ 01/07/2017 tới 31/07/2017

Nếu chấm bằng mắt thường chắc die quá, nhờ các bạn giúp mình với

 

[vanthinh3101]

Nhờ các bạn giúp mình bài toán này với.
Mình có một file excel có 2 sheet (1 sheet là dữ liệu chấm công của nhân viên, 1 sheet mình cần import số liệu từ sheet chấm công.).
Cụ thể như file đính kèm mình update lên. Mình muốn mọi số liệu trong sheet "7.2017" sẽ được import vào sheet "Data".
Ví dụ: Vũ Tiến Thành được chấm công từ ngày 01/07/2017 tới 31/07/2017 sẽ lấy các giá trị sang sheet "Data" tương ứng từ 01/07/2017 tới 31/07/2017

Nếu chấm bằng mắt thường chắc die quá, nhờ các bạn giúp mình với

Bạn thử công thức này tại sheet "Data" nhé!

C3=IFERROR(VLOOKUP($B3,'7.2017'!$B$10:$AH$26,MATCH(VALUE(LEFT(Data!C$2,2)),'7.2017'!$D$10:$AH$10,0)+2,0),"")

Sau đó Fill xuống, Fill sang phải

 

[giatthang]

Bạn thử công thức này tại sheet "Data" nhé!

C3=IFERROR(VLOOKUP($B3,'7.2017'!$B$10:$AH$26,MATCH(VALUE(LEFT(Data!C$2,2)),'7.2017'!$D$10:$AH$10,0)+2,0),"")

Sau đó Fill xuống, Fill sang phải

Vẫn không được bạn ơi, nó báo hàm không đúng. Bạn ghi hàm vào file rồi upload giúp mình được chứ?
Cảm ơn bạn nhiều

 

[vanthinh3101]

Vẫn không được bạn ơi, nó báo hàm không đúng. Bạn ghi hàm vào file rồi upload giúp mình được chứ?
Cảm ơn bạn nhiều

Bạn thử lại, thay dấu "," bằng dấu ";" nhé!

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);"")

 

[giatthang]

Bạn thử lại, thay dấu "," bằng dấu ";" nhé!

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);"")

Cảm ơn bạn rất nhiều. Về hàm đã okie rồi bạn ạ.
Tuy nhiên với giá trị "Null" trong sheet '7.2017' nó trả giá trị về là "0", như hình mình upload (Màu đỏ).
Mình muốn với data bên sheet '7.2017' giá trị bằng Null thì Sheet "Data' cũng bằng 0.
Nhờ bạn xử lý giúp với

 

[vanthinh3101]

Cảm ơn bạn rất nhiều. Về hàm đã okie rồi bạn ạ.
Tuy nhiên với giá trị "Null" trong sheet '7.2017' nó trả giá trị về là "0", như hình mình upload (Màu đỏ).
Mình muốn với data bên sheet '7.2017' giá trị bằng Null thì Sheet "Data' cũng bằng 0.
Nhờ bạn xử lý giúp với

Bạn sửa:

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);0)

 

[giatthang]

Bạn sửa:

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);0)

Kết quả vẫn như cũ bạn ạ. Giá trị null vẫn trả về "0"

Mình search trên mạng thì có bạn có câu hỏi giống mình như câu answer mình ko hiểu.
https://www.mrexcel.com/forum/excel-questions/643417-iferror-formula-returns-0-a.html

 

[giatthang]

Cách khắc phục vấn đề trên như sau:

"Format Cells | Custom and type 0;-0; and Apply. Or you will have to incorporate in the formula itself."
Nguồn https://www.mrexcel.com/forum/excel-questions/620322-vlookup-returning-0-instead-blank.html
Cảm ơn @vanthinh3101 rất nhiều.

 

[befaint]

Kết quả vẫn như cũ bạn ạ. Giá trị null vẫn trả về "0"

Format cells hoặc hủy check show zero value

 

[giatthang]

Bạn sửa:

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);0)

Bạn ơi, cho mình hỏi chút.
Khi mình điều chỉnh để upload vào hệ thống, cần điều chỉnh một số hàm, giá trị không phải là dạng text mà là hàm thì sử dụng hàm trên không trả về kết quả đúng nữa.
Bạn giúp mình với. Híc, mình loay hay cả buổi chiều chưa xong.

 

[vanthinh3101]

Bạn ơi, cho mình hỏi chút.
Khi mình điều chỉnh để upload vào hệ thống, cần điều chỉnh một số hàm, giá trị không phải là dạng text mà là hàm thì sử dụng hàm trên không trả về kết quả đúng nữa.
Bạn giúp mình với. Híc, mình loay hay cả buổi chiều chưa xong.

Bạn sửa lại công thức:

Q3=IFERROR(VLOOKUP($C3;'7.2017'!$C$12:$AI$26;MATCH(DATEVALUE(Data!Q$2);'7.2017'!$E$10:$AI$10;0)+2;0);0)

Nếu bạn muốn giữ nguyên các vùng tô màu trong sheet Data thì làm như sau:
- Đặt công thức cho ô Q3 như trên
- Copy ô Q3
- Chọn vùng dữ liệu cần copy công thức, chọn Paste Special/Formulas

 

[giatthang]

Bạn sửa lại công thức:

Q3=IFERROR(VLOOKUP($C3;'7.2017'!$C$12:$AI$26;MATCH(DATEVALUE(Data!Q$2);'7.2017'!$E$10:$AI$10;0)+2;0);0)

Nếu bạn muốn giữ nguyên các vùng tô màu trong sheet Data thì làm như sau:
- Đặt công thức cho ô Q3 như trên
- Copy ô Q3
- Chọn vùng dữ liệu cần copy công thức, chọn Paste Special/Formulas

Mình hiểu và làm được rồi. Cảm ơn bạn nhiều.
Hóa ra sử khác biệt ở hai lệnh trên là do hàm.
DATEVALUE và VALUE. Mình thấy VALUE chỉ chọn giá trị trả về khi format cell là General hoặc text. Trong khi cell đó đang là kiểu Custom (D).

Mình muốn hỏi bạn hai câu hỏi nữa câu hỏi nữa.
1. Giữ nguyên định dạng fill màu background
Trong Sheet Cột E9 đến AI9 mình đang để Conditional Formating có dạng fill là màu xanh.
Mình muốn set các ô E10, E12, E13, E14... F10, F12, F13, F14 cũng có màu xanh giống các ô từ E9 đến AI9. Khi các ô này thay đổi màu thì thì các ô tương ứng ở các hàng dưới đó cũng thay đổi màu theo. (Ý mình là cứ thứ 7 và CN sẽ là màu xanh để dễ phân biệt) thì phải làm thế nào hả bạn.

2. Với những cell nào không tìm thấy giá trị phù hợp nó sẽ cho giá trị là "0" (Như ảnh đính kèm).
Tuy nhiên phần mềm của mình nó không hiểu giá trị là 0. Có cách nào bỏ giá trị 0 này thành "Null" không ban. Ngoại trừ cách chỉnh trong Advanced--> Show a zero in cells that have zero value vì thực chất cách này nếu mình mình copy các giá trị này sang file excel khác thì vẫn hiện là 0.

 

[vanthinh3101]

Mình hiểu và làm được rồi. Cảm ơn bạn nhiều.
Hóa ra sử khác biệt ở hai lệnh trên là do hàm.
DATEVALUE và VALUE. Mình thấy VALUE chỉ chọn giá trị trả về khi format cell là General hoặc text. Trong khi cell đó đang là kiểu Custom (D).

Mình muốn hỏi bạn hai câu hỏi nữa câu hỏi nữa.
1. Giữ nguyên định dạng fill màu background
Trong Sheet Cột E9 đến AI9 mình đang để Conditional Formating có dạng fill là màu xanh.
Mình muốn set các ô E10, E12, E13, E14... F10, F12, F13, F14 cũng có màu xanh giống các ô từ E9 đến AI9. Khi các ô này thay đổi màu thì thì các ô tương ứng ở các hàng dưới đó cũng thay đổi màu theo. (Ý mình là cứ thứ 7 và CN sẽ là màu xanh để dễ phân biệt) thì phải làm thế nào hả bạn.

Bạn quét vùng dữ liệu $Q$3:$AU$16, chọn Conditional Formatting/New Rule/Use a formula to determine which cells to format.
Tại ô Format Values where this formula is true bạn điền công thức:

=OR(WEEKDAY(DATEVALUE(Q$2))=1;WEEKDAY(DATEVALUE(Q$2))=7)

Phần Preview chọn Format, hộp thoại Format Cells hiện ra, bạn chọn tab Fill và chọn màu, sau đó OK/OK

2. Với những cell nào không tìm thấy giá trị phù hợp nó sẽ cho giá trị là "0" (Như ảnh đính kèm).
Tuy nhiên phần mềm của mình nó không hiểu giá trị là 0. Có cách nào bỏ giá trị 0 này thành "Null" không ban. Ngoại trừ cách chỉnh trong Advanced--> Show a zero in cells that have zero value vì thực chất cách này nếu mình mình copy các giá trị này sang file excel khác thì vẫn hiện là 0.

Bạn chỉ cần sửa lại giá trị 0 cuối cùng trong công thức phía trên thành "Null" là được.

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);"Null")

 

[giatthang]

Bạn quét vùng dữ liệu $Q$3:$AU$16, chọn Conditional Formatting/New Rule/Use a formula to determine which cells to format.
Tại ô Format Values where this formula is true bạn điền công thức:

Phần Preview chọn Format, hộp thoại Format Cells hiện ra, bạn chọn tab Fill và chọn màu, sau đó OK/OK

Mình chưa hiểu ý lắm. Ý mình là ở sheet 7.2017 trong file Phanca_TS_DieuChinhCaTrongLich.xls ấy bạn a. Mình muốn các ngày thứ 7, CN được chọn màu fill là xanh thì các cột tương ứng cũng như vậy.

Bạn chỉ cần sửa lại giá trị 0 cuối cùng trong công thức phía trên thành "Null" là được.

C3=IFERROR(VLOOKUP($B3;'7.2017'!$B$10:$AH$26;MATCH(VALUE(LEFT(Data!C$2;2));'7.2017'!$D$10:$AH$10;0)+2;0);"Null")

[/QUOTE]
Mình đã thử và không được. Trong Sheet data mình muốn chỉ cho các kết quả là: C1, C2, P, O hoặc HC ngoài ra các giá trị trả về phải là Null (Blank) hết

 

[vanthinh3101]

Mình chưa hiểu ý lắm. Ý mình là ở sheet 7.2017 trong file Phanca_TS_DieuChinhCaTrongLich.xls ấy bạn a. Mình muốn các ngày thứ 7, CN được chọn màu fill là xanh thì các cột tương ứng cũng như vậy.

Mình đã thử và không được. Trong Sheet data mình muốn chỉ cho các kết quả là: C1, C2, P, O hoặc HC ngoài ra các giá trị trả về phải là Null (Blank) hết[/QUOTE]
Mình bận mất rồi, có gì chiều mình xem lại và trả lời bạn nhé!

 

[giatthang]

Mình đã thử và không được. Trong Sheet data mình muốn chỉ cho các kết quả là: C1, C2, P, O hoặc HC ngoài ra các giá trị trả về phải là Null (Blank) hết

Mình bận mất rồi, có gì chiều mình xem lại và trả lời bạn nhé!
Okie. Cảm ơn bạn nhiều.

 

[vanthinh3101]

Mình bận mất rồi, có gì chiều mình xem lại và trả lời bạn nhé!

Okie. Cảm ơn bạn nhiều.[/QUOTE]
Bạn xem file đính kèm nhé!
Cách tô màu cho các cột thứ 7, chủ nhật tương tự như cách mình hướng dẫn bên trên.
Mình phải làm 3 lần vì có các ô Merge Cell, tốt nhất không có Merge Cell thì dễ làm và chính xác hơn

 

[giatthang]

Okie. Cảm ơn bạn nhiều.
Bạn xem file đính kèm nhé!
Cách tô màu cho các cột thứ 7, chủ nhật tương tự như cách mình hướng dẫn bên trên.
Mình phải làm 3 lần vì có các ô Merge Cell, tốt nhất không có Merge Cell thì dễ làm và chính xác hơn

Cảm ơn bạn rất rất nhiều. Mình từ đã làm được rồi.
Tuy nhiên khi change thời gian chấm công của tháng. Ví dụ tháng 10/2017 trong sheet 7.2017 thì các giá trị trong sheet Data không tự động nhận mà kết quả blank hết.

Chúc bạn một ngày làm việc vui vẻ và hiệu quả.

 

[vanthinh3101]

Cảm ơn bạn rất rất nhiều. Mình từ đã làm được rồi.
Tuy nhiên khi change thời gian chấm công của tháng. Ví dụ tháng 10/2017 trong sheet 7.2017 thì các giá trị trong sheet Data không tự động nhận mà kết quả blank hết.

Chúc bạn một ngày làm việc vui vẻ và hiệu quả.

Bạn lưu ý đến các phần tham chiếu trong công thức.
Nếu vùng dữ liệu thay đổi thì cần phải cập nhật vào công thức, có như vậy ra kết quả đúng được.

 

[giatthang]

Mình hiểu, nhưng làm sao để match Q2 --> AU2 trong Sheet "Data" với ngày trong tháng của Sheet '7.2017'
Bạn giúp mình với

 

[vanthinh3101]

Mình hiểu, nhưng làm sao để match Q2 --> AU2 trong Sheet "Data" với ngày trong tháng của Sheet '7.2017'
Bạn giúp mình với

Bạn xem trong công thức, dùng Match để tìm dữ liệu thuộc (Q2:AU2) ở Sheet Data trong (E10:AI10) ở Sheet 7.2017

 

[giatthang]

Bạn xem trong công thức, dùng Match để tìm dữ liệu thuộc (Q2:AU2) ở Sheet Data trong (E10:AI10) ở Sheet 7.2017

Mình thử shet data các hàm sau
Q2=MATCH(VALUE(LEFT('7.2017'!E$10;2));'7.2017'!$E$10:$AL$10;0) --> N/A
Q2=MATCH('7.2017'!E10;'7.2017'!E10:AI10;0) --> 1 Tuy nhiên các giá trị trong sẽ khác sẽ bằng null.

Khoai kinh

 

[vanthinh3101]

Mình thử shet data các hàm sau
Q2=MATCH(VALUE(LEFT('7.2017'!E$10;2));'7.2017'!$E$10:$AL$10;0) --> N/A
Q2=MATCH('7.2017'!E10;'7.2017'!E10:AI10;0) --> 1 Tuy nhiên các giá trị trong sẽ khác sẽ bằng null.

Khoai kinh

Bạn có 2 lỗi:
- Bạn đang tìm dữ liệu thuộc Sheet Data trong Sheet 7.2017 nhưng công thức thì lại là tìm dữ liệu thuộc Sheet 7.2017 trong Sheet 7.2017
- Bạn đừng dùng hàm left, để tách chuỗi. Trong sheet 7.2017 đang hiển thị là các số 1, 2, 3...31 nhưng thực chất giá trị là ngày bạn nhé! Bạn cứ dùng hàm Datevalue như của mình hướng dẫn là được.

 

[giatthang]

Mình tìm đc ra hàm rồi, cảm ơn bạn @vanthinh3101
Cho mình hỏi câu cuối cùng nhé. Tại sao từ Q6 tới Y6 hiện báo sai hàm mặc dù vẫn ra kết quả nhỉ?
Q6=IF(WEEKDAY(Q2,2)<6,"N","")

Các ngày trong tuần gán bằng N, nhưng Thứ 7, CN gán là Null. Hàm thì mình xem đi xem lại không thấy sai.
Q6 mình để format general, Q2 để kiểu date
Chi tiết như hình đính kèm