Định dạng Conditional Formatting (1 người xem)

[hngiatuong]

Mình có yêu cầu như trong file, Xin được giúp đỡ, cảm ơn nhiều

 

[vu_tuan_manh_linh]

Condictinal Formatting không thể tham chiếu giữa 2 sheet khác nhau!! Dùng tạm cách củ chuối này đi bạn!

 

[phucbugis]

Condictinal Formatting không thể tham chiếu giữa 2 sheet khác nhau!! Dùng tạm cách củ chuối này đi bạn!

đúng là Excel 97-2003 ko liên kết trực tiếp giữa các sheet, nhưng bạn vẫn có thể dùng Name.

khi cài Conditional Formatting (có liên kết giữa 2 sheet) xong ở các phiên bản sau 2003, sau khi lưu thì xuất hiện thông báo

- nếu ko dùng Name:


- khi dùng Name


bài này thì mình phải chuyển toàn bộ các cell sang số thì mới đối chiếu được với cột phòng bên sheet Diengiai.

tham khảo thêm:
dùng hàm: Extracting numbers from alphanumeric strings (microsoft)
dùng VBA: Function ExtractNumber (ozgrid)

link: https://www.mediafire.com/?y4jv0a5wkjz3gv5

 

[hngiatuong]

chuyển toàn bộ các cell sang số thì mới đối chiếu được với cột phòng bên sheet Diengiai.
(tham khảo thêm: Extracting numbers from alphanumeric strings (microsoft))

Cảm ơn các Anh đã giúp đỡ. Mình thấy hài lòng rồi. Nhưng nếu trường hợp khách hàng đã trả phòng (tức là khi nhập thêm ngày trả phòng) thì mình muốn phòng đó lại trở về màu trắng, vậy phải làm thế nào. VD ở đây là F4, F5 sheet Diengiai khách đã trả phòng.

 

[phucbugis]

Cảm ơn các Anh đã giúp đỡ. Mình thấy hài lòng rồi. Nhưng nếu trường hợp khách hàng đã trả phòng (tức là khi nhập thêm ngày trả phòng) thì mình muốn phòng đó lại trở về màu trắng, vậy phải làm thế nào. VD ở đây là F4, F5 sheet Diengiai khách đã trả phòng.

mình thấy dùng Conditional Formatting (CF) và áp dụng cho Excel 97_2003 vào bài toán của bạn có nhược điểm là:

- việc xác định vùng đã cài CF ko phải dễ, chẳng thấy ô Applies To đâu cả (Excel 2007 trở về sau thì khác)



- công thức rất dài (vì nhiều điều kiện) + khó hiểu. ---> khi chỉnh sửa công thức lại phải đưa ra ngoài sheet
- Khi dùng công thức mảng ---> ảnh hưởng đến tốc độ của file đang chạy.

'- - -
mình quyết định chuyển sang dùng VBA:chính xác + dễ điều chỉnh vùng đã cài + dễ hiểu.

'- - -
bạn mở file, cho chạy Macro, nhập thông tin tại sheet Diengiai, xem kết quả sheet Theodoi

Link: https://www.mediafire.com/?ndjpdmi4imd3t47

 

[hngiatuong]

bạn mở file, cho chạy Macro, nhập thông tin tại sheet Diengiai, xem kết quả sheet Theodoi

Viết bằng VBA thì tốt quá. Cảm ơn phucbugis Nhưng sao khi mở file lại báo lỗi nhỉ:
Run-time error ‘438”
Object doesn’t support thí property or method

ArrDSphong.Interior.TintAndShade = 0 'clear color

Làm ơn chỉ bảo giúp.

 

[phucbugis]

Viết bằng VBA thì tốt quá. Cảm ơn phucbugis Nhưng sao khi mở file lại báo lỗi nhỉ:
Run-time error ‘438”
Object doesn’t support thí property or method

ArrDSphong.Interior.TintAndShade = 0 'clear color

Làm ơn chỉ bảo giúp.

hehe, do mình Record Macro của Excel 2013 nên code nó khác -> ko chạy trên 2003 được.

bạn sửa:
ArrDSphong.Interior.TintAndShade = 0 thành

ArrDSphong.Interior.ColorIndex = xlNone

rng.Interior.TintAndShade = 0 thành

rng.Interior.ColorIndex = xlNone

là chạy được.

 

[hngiatuong]

ko chạy trên 2003 được.

Khi sửa lại như trên nó lại báo
If wsFunc.CountIfs(vung1, phongso, vung2, Cells(3, rng.Column())) > 0 Then
Anh có thể xem lại giúp được không?
Xin cảm ơn

 

[vodoi2x]

Thử công thức Formating conditional này cho đơn giản

chú ý

Sheet TheoDoi chỉ cần gõ số phòng (bấm Ctrl+1 hoặc Format/cells... để thấy định dạng number tự động thêm chữ "PHÒNG")

chú ý:

+ Phần cột G bên DienGiai phải gõ đúng tiêu đề tương ứng như dòng 3 sheet TheoDoi

+ Các Formating conditional được định dạng riêng cho từng vùng của dãy riêng biệt (4 cái riêng: cột A, Cột B:C, Cột D, cột E) - vào data formating conditional để rõ công thức áp dụng

+ có sử dụng 2 name động cho cột F, G : để đảm bảo số liệu cập nhập ở DienGiai được tính vào, bấm Ctrl+F3 để thấy 2 name này

cuối cùng xem tất cả trong file kèm

 

[hngiatuong]

Thử công thức Formating conditional này cho đơn giản

Cảm ơn vodoi2x Mình thử rồi không được. khi nhập ngày trả phòng thì màu nền không trở lại.

 

[vodoi2x]

Cảm ơn vodoi2x Mình thử rồi không được. khi nhập ngày trả phòng thì màu nền không trở lại.

tất cả các bài trên
và trong file , có thấy nhắc NGÀY đâu?

nghĩa là NGAY TRA đã nhập thì không format màu nữa???

 

[vodoi2x]

Cảm ơn vodoi2x Mình thử rồi không được. khi nhập ngày trả phòng thì màu nền không trở lại.

vậy thì thêm name nNGAYTRA ,

xem file kèm

vẫn cần

Thử công thức Formating conditional này cho đơn giản

chú ý

Sheet TheoDoi chỉ cần gõ số phòng (bấm Ctrl+1 hoặc Format/cells... để thấy định dạng number tự động thêm chữ "PHÒNG")

chú ý:

+ Phần cột G bên DienGiai phải gõ đúng tiêu đề tương ứng như dòng 3 sheet TheoDoi

+ Các Formating conditional được định dạng riêng cho từng vùng của dãy riêng biệt (4 cái riêng: cột A, Cột B:C, Cột D, cột E) - vào data formating conditional để rõ công thức áp dụng

+ có sử dụng 2 name động cho cột F, G : để đảm bảo số liệu cập nhập ở DienGiai được tính vào, bấm Ctrl+F3 để thấy 2 name này

cuối cùng xem tất cả trong file kèm

 

[hngiatuong]

thêm name nNGAYTRA ,

xem file kèm

Đúng ý mình rồi đấy. Cảm ơn Vodoi2x nhé. Thật tuyệt.
Nhưng cho mình hỏi chút:
bấm Ctrl+1 để thấy định dạng number tự động thêm chữ "PHÒNG" chỗ này mình chưa hiểu ý lắm có thể giải nghĩa thêm được không?
Cảm ơn

 

[vodoi2x]

Đúng ý mình rồi đấy. Cảm ơn Vodoi2x nhé. Thật tuyệt.
Nhưng cho mình hỏi chút:
bấm Ctrl+1 để thấy định dạng number tự động thêm chữ "PHÒNG" chỗ này mình chưa hiểu ý lắm có thể giải nghĩa thêm được không?
Cảm ơn

Thì tại sheet TheoDoi, bạn chỉ nhập số phòng ví dụ phòng 1 chỉ nhập số 1 không có chữ "PHÒNG", - tại các ô này bạn bấm Ctrl+1 thì sẽ thấy vào

Format \ Cell.... ==> định dạng dùng là custom xem trong TYPE sẽ thấy ....

b/c lại kết quả

 

[hngiatuong]

b/c lại kết quả

Rất cảm ơn anh. Giờ thì mình đã hiểu. Nhưng mà đang loay hoay tìm cách nới thêm danh sách. Vì danh sách hiện tại chỉ cho đến dòng 22 của Diengiai, nếu khi danh sách dài thêm thì bên theo dõi không nhận.

 

[vodoi2x]

Rất cảm ơn anh. Giờ thì mình đã hiểu. Nhưng mà đang loay hoay tìm cách nới thêm danh sách. Vì danh sách hiện tại chỉ cho đến dòng 22 của Diengiai, nếu khi danh sách dài thêm thì bên theo dõi không nhận.

Ds gì, số phòng?

nếu là sheet DienGiai, thì đang để đến tận dòng 2000 - xem Name nPhongSo sẽ thấy $F$4:$F$2000

Sheet DienGiai Bạn phải gõ vào cột vào cột F (số phòng) , một cách liên tiếp các dòng thì mới có hiệu ứng - (nhập như thật đó)

Tức là cột F G phải có số liệu thì mới xét kết quả, và cột I nhập khi đã trả phòng

 

[hngiatuong]

Name nPhongSo $F$4:$F$2000
cột F nhập một cách liên tiếp

Name nPhongSo $F$4:$F$2000 chỗ này thì nhìn thấy rồi.

cột F nhập một cách liên tiếp (chỗ này bây giờ mới hiểu)
Cảm ơn Anh nhiều nhé. Chúc anh có 1 giấc mơ đẹp

 

[vodoi2x]

Name nPhongSo $F$4:$F$2000 chỗ này thì nhìn thấy rồi.

cột F nhập một cách liên tiếp (chỗ này bây giờ mới hiểu)
Cảm ơn Anh nhiều nhé. Chúc anh có 1 giấc mơ đẹp

Cảm ơn bạn, cứ diễn đàn kiểu này giấc mơ đẹp làm chi,

Vì nghe nói dữ liệu ở sheet diễn giải của bạn nhiều (chưa cả năm) như thế khi đó phần dữ liệu xét như nSOPHONG sẽ là vùng lớn gồm nhiều dòng --> làm chạy chậm . Trong khi chúng ta chỉ cần xét phân đang cho thuê hiên thời --> bắt đầu từ ít nhất dòng có ô I (Ngày trả) là Rỗng, ==> để cải tiến tốc độ xử lý, thì bạn nên thành công thức của name nPHONGSO thành công thức sau

=OFFSET(Diengiai!$F$4,MATCH(TRUE,Diengiai!$I$4:$I$2000="",0)-1,,COUNTA(Diengiai!$F$4:$F$2000)-MATCH(TRUE,Diengiai!$I$4:$I$2000="",0)+1)

khi đó vùng tính toán sẽ giới hạn nhỏ hơn, mà kết quả vẫn đảm bảo chính xác.

Bạn thay như thế sẽ hiệu quả hơn công thức cũ của nPHONGSO

các tên khác nDAYNHA, nNGAYTRA không cần đổi gì cả vì nó được suy ra từ name nPHONGSO

bạn cứ Ctrl+F3 bấm vào phần Referto của công thức sẽ thấy vùng xét (và vùng mới đã giảm số dòng hơn)

 

[hngiatuong]

khi đó vùng tính toán sẽ giới hạn nhỏ hơn, mà kết quả vẫn đảm bảo chính xác.

Bạn thay như thế sẽ hiệu quả hơn công thức cũ của nPHONGSO

Mình đã thử, thấy vẫn đúng. Xin cảm ơn nhiều. Còn cuộc sống cần phải có nhiều giấc mơ chứ. Như mình đang mơ sẽ dựa vào diễn đàn này để học hỏi thêm về những gì bản thân còn thiếu và VBA. Rất cảm ơn tất cả các A/C đã giúp đỡ.

 

[hngiatuong]

b/c lại kết quả

Báo cáo kết quả là file làm việc chuẩn xác, nhưng khi làm đến đây mình nảy ra 1 ý tưởng mới là:
Giả sử khi bảng Diengiai tăng lên số dòng là 2000, trong đó phòng số 101 được khách hàng thuê đi, trả lại nhiều lần và hiện có 1 khách hàng đang thuê. Nhìn vào bảng Theodoi thấy phòng 101 đang được tô màu, bây giờ khách hàng yêu cầu trả phòng bằng cách nào đó để khi bấm chuột lên phòng 101 tại Theodoi thì con chuột được chuyển đến phòng 101 tại Diengiai đang thuê đó để điền ngày trả vào, chứ với khoảng 2000 dòng thì tìm hơi lâu. Ý tưởng này có vẻ như phức tạp nên mình không biết có thực hiện được không nhỉ?
Mà sao khi mình bấm chọn Báo cáo: Từ ngày, đến ngày thì nó lại mất chữ "từ ngày", "đến ngày" trong sheet Baocao. Làm ơn giải thích.

 

[vodoi2x]

Báo cáo kết quả là file làm việc chuẩn xác, nhưng khi làm đến đây mình nảy ra 1 ý tưởng mới là:
Giả sử khi bảng Diengiai tăng lên số dòng là 2000, trong đó phòng số 101 được khách hàng thuê đi, trả lại nhiều lần và hiện có 1 khách hàng đang thuê. Nhìn vào bảng Theodoi thấy phòng 101 đang được tô màu, bây giờ khách hàng yêu cầu trả phòng bằng cách nào đó để khi bấm chuột lên phòng 101 tại Theodoi thì con chuột được chuyển đến phòng 101 tại Diengiai đang thuê đó để điền ngày trả vào, chứ với khoảng 2000 dòng thì tìm hơi lâu. Ý tưởng này có vẻ như phức tạp nên mình không biết có thực hiện được không nhỉ?

Không có gì là phức tạp, xem file kèm
Bấm Name để xem thêm 2 name mới: nPHONGSOfull và nLinkNGAYTRA

bấm Formatting conditional ... để thấy đã thay đổi: chỉ dùng 1 công thức điều kiện cho cả vùng A4:I14 -- và thêm điều kiện tô xanh nhạt "các ô có ít nhất 1 lần đã thuê và đã trả" để rõ, khi bấm link sẽ trả về vị trí ngày trả (gần nhất - cuối dãy của ngày trả)

tìm hiều về HyperLink sẽ hiểu vấn đề

Chú ý chỉ có các phòng đang được tô màu mới bấm link được, các phòng kia chứng tỏ chưa có mặt trong sheet diengiai,

Phần nhập ở cột G (cột Dãy nhà) thì phải chuẩn theo đúng bên dòng 3 của sheet Theodoi nhé (vì như Dãy KHÁC XA với Dẫy ), tôi đã sửa cột N ở she et TheoDoi --> để validation cột G bên she et diengiai chọn cho đúng chuẩn này (xem sẽ rõ)

Mà sao khi mình bấm chọn Báo cáo: Từ ngày, đến ngày thì nó lại mất chữ "từ ngày", "đến ngày" trong sheet Baocao. Làm ơn giải thích.

Tôi vẫn thấy được đó thôi

Hay bạn nói là khi bấm nút xổ xuống -- thế thì không được đâu, vì bản chất dữ liệu chỉ có ngày tháng còn các từ trong " ..." là từ do format mà thôi (nhưng in ra vẫn có)

 

[hngiatuong]

Hay bạn nói là khi bấm nút xổ xuống -- thế thì không được đâu, vì bản chất dữ liệu chỉ có ngày tháng còn các từ trong " ..." là từ do format mà thôi (nhưng in ra vẫn có)

Cảm ơn Anh thật nhiều. File chạy rất tốt, mình đã thỏa mãn.
Còn vấn đề kia là khi bấm vào ô D5 để chọn ngày cần báo cáo. Chẳng hạn chọn các ngày 4/6, 8/5, 8/6 thì vẫn có chữ "Đến ngày 4/6/2012" nhưng khi chọn đến ngày 20/8/2012 thì chỉ còn "20/8/2012" thôi. Kiến thức của mình còn nông quá nên không hiểu được. Nhưng thôi phần này cứ để thể cũng chẳng sao. Lần nữa xin được cảm ơn Anh.

 

[vodoi2x]

Cảm ơn Anh thật nhiều. File chạy rất tốt, mình đã thỏa mãn.
Còn vấn đề kia là khi bấm vào ô D5 để chọn ngày cần báo cáo. Chẳng hạn chọn các ngày 4/6, 8/5, 8/6 thì vẫn có chữ "Đến ngày 4/6/2012" nhưng khi chọn đến ngày 20/8/2012 thì chỉ còn "20/8/2012" thôi. Kiến thức còn nông quá nên không hiểu được. Nhưng thôi phần này cứ để thể cũng chẳng sao. Lần nữa cảm ơn Anh.

Xem lại định dạng ngày 20/8/2012 ở sheet nhập có đúng là "số" hay không, hay nó là text??? cách tốt nhất là bạn để định dạng (ở ô nhập) là dd mmm, yyyy khi nhập sai bít ngay (mmm có thể theo vietnam style nhé - tìm bài trên diễn đàn mà xử lý)

Kiến thức nông cũng phải xử lý, vì đây là sai cơ bản

Tôi vừa xem lại đúng là tại she et nhập (DienGiai) cột B 20/8/2012 của bạn được excel hiểu là TEXT không phải SỐ - nên dẫn đến cái sai trên nhé ==> phải xử lý lại ngay, và như tôi nói trên

 

[hngiatuong]

vì đây là sai cơ bản ==> phải xử lý lại ngay, và như tôi nói trên

Thật lòng cảm ơn. Mĩnh nghĩ nếu là như vậy mĩnh sẽ giải quyết được.
Cảm ơn, cảm ơn Anh.

 

[hngiatuong]

Làm phiền anh 1 chút nữa. Xin anh cho 1 lời khuyên (nằm trong file).
Thôi thì Tiễn Phật tiễn đến Tây thiên anh ạ.

 

[vodoi2x]

Làm phiền anh 1 chút nữa. Xin anh cho 1 lời khuyên (nằm trong file).
Thôi thì Tiễn Phật tiễn đến Tây thiên anh ạ.

Tôi nghĩ bài như thế này của bạn chắc phải làm VBA

và có 2 hình thức

- báo cáo động luôn của tháng hiện tại, khi có các số liệu nhập vào B:B, hay vào I:I sheet DienGiai

- Hoặc bấm nút làm báo cáo 1 lần vào cuối tháng

DÙ cái nào, thì cũng khác xa với chủ đề hiện tại ==> bạn nên mở topic mới hoàn toàn --> để các thành viên có thể giúp

Vậy nha,

 

[hngiatuong]

DÙ cái nào, thì cũng khác xa với chủ đề hiện tại ==> bạn nên mở topic mới hoàn toàn --> để các thành viên có thể giúp

Vậy nha,

Có lẽ tạm thời mình dừng lại tại đây, nếu nhu cầu công việc tiếp theo đòi hỏi, mình sẽ mở lại topic khác. Xin được cảm ơn Anh. Bye.