Hàm OR

[luongcp]

Gửi các bạn,

Giả sử A1 chứa ngày tháng năm (VD A1 = 5/6/2005)

Nếu ngày tháng A1 = 1/1 hoặc 30/4 hoặc 1/5 hoặc 2/9 thì hiển thị "Lễ Tết"

Để thực hiện việc này, mình dùng hàm :

If(or(day(A1)&month(A1))="11";day(A1)&month(A1)="304";day(A1)&month(A1)="15"; v.v... ); "Lễ Tết"; Ngày thường")

Tôi thấy như vậy khá dài dòng nên nếu viết công thức cho gọn hơn :

If(day(A1)&month(A1)=or("1";"304";"15";"29");"Lễ Tết";"Ngày thường")

nhưng EXCEL không cho phép

Vậy có cách nào không các bác giúp tôi nhé ...

 

[handung107]

Chẳng có cách nào khác ngòai việc bạn hãy đặt tên cho đọan công thức DAY(A1)&MONTH(A1) thôi.
Bạn đặt con trỏ ngay Cell đầu tiên của dãy mà bạn muốn áp dụng công thức. Bạn vào Insert/Name/Define, trong hộp Name, bạn gõ : NgayThang chẳng hạn, trong hộp Refers To bạn nhập : = DAY($A1)&MONTH($A1)
Bây giờ công thức của bạn sẽ trở thành :
If(or(NgayThang="11";NgayThang="304";NgayThang="15"; v.v... ); "Lễ Tết"; Ngày thường")
Bạn thấy đó, kỹ năng đặt tên cho ô, vùng, dãy, hằng số, công thức trong nhiều trường hợp rất quan trọng và cần thiết

 

[luongcp]

Cám ơn chị Dung .... Thật tiếc là không có cách gọn hơn, vì dù đã đặt tên cho Cthức nhưng nếu không chỉ có 4 ngày lễ như em liệt kê (chẳng hạn nước nào đó có cả mười mấy ngày lễ) thì sẽ khá dài.

Còn 1 VĐ nữa,

Mục đích của em là : If những ngày nào nhằm vào ngày lễ tết thì cho hiển thị số 3 (Ở công thức trên em cho hiển thị "Lễ Tết" cho tiện đó mà) --> (Tức là nhân hệ số khi tính lương cho những người đi làm vào ngày lễ tết)

Tuy nhiên em chỉ xác định được những ngày lễ và tết tây, còn tết ta (4 ngày) thì khi rớt vào ngày tháng này khi rơi vào ngày tháng khác nên em chưa làm đựơc. Vậy chị Dung có quy những ngày dương lịch nhằm ngày tết ta về ngày âm lịch không ?

 

[handung107]

Nếu hàm quá dài, bạn hãy đặt tên cho từng đọan công thức. Td : ThangMotDenThangSau, ThangSauDenThangMuoiHai ...Vấn đề này, bạn hãy xem lại các bài viết trên diễn đàn.
http://www.webketoan.com/forum/showthread.php?t=6878
http://www.webketoan.com/forum/showthread.php?t=6680
Còn vấn đề đổi ngày âm lịch sang dương lịch, có lẽ bạn phải viết hàm bằng VBA, rồi lưu thành Add-in để sử dụng kết hợp với công thức trên thôi

 

[KKBS]

Theo tôi thì các bạn không nên dùng hàm OR cho trường hợp này vì sẽ không tổng quát. Các bạn nên gõ tất cả những ngày nghỉ của công ty vào một sheet khác, trong ví dụ là sheet 2. Cột A của sheet 2 là ngày nghỉ, cột B của sheet 2 là diễn giải của ngày nghỉ. Như vậy, nếu giám đốc công ty muốn cho thêm một ngày nghỉ thì cứ vào đây mà gõ

Giả sử ô A1 của Sheet 1 là ngày đang xét, ta sẽ đặt công thức cho ô B1 trong sheet 1 như sau

B1 = IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,0)),"Ngày thường","Lễ Tết")

Giải thích :
- Nếu ngày đang xét không là ngày nghỉ thì nó không có mặt trong cột A của sheet 2, như vậy thì hàm VLOOKUP cho ra lỗi, khi đó ISERROR cho ra TRUE và như vậy B1 có giá trị là "Ngày thường"
- Ngược lại, ngày đang xét là ngày nghỉ thì nó có mặt trong cột A của sheet 2, VLOOKUP cho ra một giá trị chứ không phải lỗi, như vậy ISERROR cho ra FALSE, khi đó B1 có giá trị là "Lễ Tết".

Bạn cũng nên đổi tên sheet 2 lại thành NGAYNGHI (hay HOLIDAYS cũng được!)

Thân,
KKBS.

 

[ForestC]

Cách của anh KKBS đưa ra là rất tổng quát và hợp lý. Tôi nghĩ công việc của bạn luongcp nên thực hiện theo cách này, chỉ có điều nếu dùng nguyên 1 sheet cho chỉ một công việc này thôi thì e rằng hơi lãng phí, chính vì thế theo tôi sheet này sẽ có chức năng như một sheet danh mục, trong đó có thể dùng để liệt kê danh sách của nhiều thứ: danh sách ngày lễ, danh sách tài khoản, danh sách khách hàng, danh sách etc ...

 

[luongcp]

Cám ơn thầy KKBS đã cho 1 PA kha khá hiệu quả ...

Bậy giờ, Cty mình luôn chỉ cho nghỉ 8 ngày hưởng lương : 4 ngày lễ như đã nói và 4 ngày tết ta (30 --> mùng 3 tháng giêng âm lịch), những ngày khác ai nghỉ thỉ nghỉ nhưng không được trả lương ...

Vậy vẫn cần thiết 1 hàm CanChi để quy về, nếu không thì trong sheet holidays của thầy KKBS phải điểu chỉnh ngày dương lịch tương ứng với ngày tết âm lịch vào mỗi năm tết đến ...

Cách đây hơn 1 năm tôi có 1 hàm CanChi() (Code VBA lấy từ internet nhưng quên của ai rồi ... bây giờ tìm không thấy nữa) ...

 

[KKBS]

Tất cả các ngày nghỉ đều phải được đánh vào cho từng năm. Một năm có mấy ngày à, chịu khó gõ vào chừng 5 phút là xong.

Thân,
KKBS.

 

[handung107]

Nếu phải dùng thêm cột phụ hay bảng phụ thì vấn đề bao giờ cũng dễ giải quyết. Nhưng nếu trong một năm chỉ có 4 ngày nghỉ lễ và 4 ngày nghỉ Tết mà phải sử dụng thêm 1 Sheet phụ nữa, rồi lại dùng hàm VLOOKUP thì e rằng hơi phí nếu File này còn phải giải quyết thêm nhiều thứ khác nữa. Và nếu chỉ có bấy nhiêu thôi, sử dụng hàm IF vẫn hiệu quả và gọn như thường
Vấn đề chính ở đây là chỉ có bạn mới hiểu công việc của bạn thế nào để có những giải pháp sắp xếp cho hiệu quả.

 

[luongcp]

Đành vậy ...

Vì các ngày lễ năm nào cũng như nhau nên nếu dùng hàm ngaythang thì không cần tham chiếu năm. Nhưng dùng PA thầy KKBS thì các ngày lễ cũng phải nhập đầy đủ ngày tháng năm trong cột A của sheet holidays --> vì A1 (chứa nguyên liệu ngày tháng năm) trong sheet ban đầu phải tương ứng với dãy tham chiếu

Thế thì sao ... Thầy phải đắp lỗ hổng cho PA của thấy nhé

 

[luongcp]

Lại nói về việc đặt tên cho Cthức như chị Dung, có vấn đề không ổn :

Giả sử A1:A31 = {1/5/2005 : 31/5/2005}

Tại B1 ta dùng Name/define đặt NgTh = day(A1)&month(A1) --> Không có vấn đề gì khi chép Cthức xuống B31

Nhưng dãy của em như sau :

A1 = 1/5/2005
A2 = rỗng
A3 = rỗng
A4 = 2/5/2005
A5 = rỗng
A6 = rỗng
A7 = 3/5/2005
A8 = rỗng
...
Nên khi chép Cthức xuống sẽ không ổn ...

Vậy chị Dung xử lý như thế nào ???

 

[handung107]

Vì thế, tôi mới nói, chỉ có bạn mới hiểu cách thiết kế File của bạn như thế nào để có giải pháp xử lý thích hợp, nếu bạn muốn có ý kiến cho đúng, bạn phải nêu nhiều điều kiện đi kèm hay gửi File, chứ mỗi lúc, bạn đưa thêm một yếu tố liên quan, tôi không biết nói sao nữa.

 

[handung107]

Bạn có thể xử lý lỗi, xử lý chuỗi rỗng ngay khi đặt tên cho công thức.
Td : =IF(A1="","",DAY(A1)&MONTH(A1))
Tôi nghĩ đây là vấn đề rất bình thường, không có vấn đề gí quá khó cả

 

[luongcp]

Cho em xin lỗi vì lại gây bực mình cho chị, em đã đánh máy nhầm : Đúng ra là

Tại B1 và B2 thì công tức là day&month A1

Nhưng tại B3 và B4 thì CThức lại là day&month A3

Tại B5 và B6 thì CThức là day&month A5

Tại B7 và B8 thì CThức là day&month A7



A1 = 1/5/2005 B1 = if(or(day(A1)&month(A1)="304";day(A1)&month(A1)="15";"Ngày Lễ";"Ngày Thường"))
A2 = rỗng B2 = if(or(day(A1)&month(A1)="304";day(A1)&month(A1)="15";"Đêm Lễ";"Đêm Thường"))
A3 = 2/5/2005 B3 = if(or(day(A3)&month(A3)="304";day(A3)&month(A3)="15";"Ngày Lễ";"Ngày Thường"))
A4 = rỗng B4 = if(or(day(A3)&month(A3)="304";day(A3)&month(A3)="15";"Đêm Lễ";"Đêm Thường"))
A5 = 3/5/2005 B5 = if(or(day(A5)&month(A5)="304";day(A5)&month(A5)="15";"Ngày Lễ";"Ngày Thường"))
A6 = rỗng B6 = if(or(day(A5)&month(A5)="304";day(A5)&month(A5)="15";"Đêm Lễ";"Đêm Thường"))
A7 = 4/5/2005
...

Như vậy thì việc đặt tên Cthức NgTh = day(A1)&month(A1) sẽ không ổn đúng không --> Vì khi fill xuống thì tại B2 sẽ là day(A2)&month(A2), trong khi mình muốn tại B2 Cthức vẫn là day(A1)&month(A1)

Xin lỗi vì quá lằng nhằng ...

Em sẽ gưi File cho chị ngay

 

[handung107]

Thế thì bạn cố định A1 lại bằng cách dùng tham chiếu tuyệt đối ? $A$1

 

[luongcp]

Thiệt là áy náy vì câu hỏi quá lằng nhằng và cẩu thả ...

Xin lỗi chị Dung và các bạn vậy ...

Chị xem thử thông tin em sửa lại, có lẽ không thể đặt tên CThức trong trường hợp này ???

 

[handung107]

Thật sự tôi cũng không thể hiểu hết các yêu cầu cụ thể của bạn, trong File bạn gửi cho tôi, bạn chỉ cần chú ý các tham chiếu cột, dòng, tương đối và tuyệt đối là xong.
Còn theo yêu cầu trên của bạn, thí dụ nếu A2 rỗng, thì lấy kết quả là DAY(A1)&MONTH(A1), còn không thì DAY(A2)&MONTH(A2) chứ đâu có khó khăn gì ?

Bạn nên bắt đầu đặt tên cho công thức tại B2 :
NgayThang=IF(A2<>"",DAY($A2)&MONTH($A2),DAY($A1)&MONTH($A1))

(Nếu bạn mở lại hộp Name, bạn sẽ thấy Excel tự động đặt tên cho công thức NgayThang tại B1 như sau :
NgayThang=IF(A1<>"",DAY($A1)&MONTH($A1),DAY($A65526)&MONTH($A65526))

Tôi không biết còn những gì gây khó khăn cho bạn nữa, nhưng bạn cần bình tĩnh xem xét các điều kiện rồi thử rút ra cho mình những cái phù hợp dựa trên nền tảng công thức sẵn có

 

[handung107]

Tôi gởi File đã chỉnh sửa xong, phần đặt tên NgTh tôi đã nói ở phần trên, trong File của bạn có yêu cầu thêm điều kiện là phân biệt "Ca ngày lễ", "Ca đêm Lễ", "Ca ngày Thường", "Ca đêm thường', nên tôi đề nghị bạn đặt thêm tên cho đọan công thức NgayDem nữa để có thể rút gọn công thức chính. Mời bạn xem File, bạn cần chú ý phần đặt tên trong hộp Insert/Name

 

[luongcp]

Chị Dung chưa giải quyết hết vấn đề rồi, trong công thức dài dòng của em còn có cả Ca ngày chủ nhật và ca đêm chủ nhật (weekday(A10)=1;"Chủ nhật"....)

 

[handung107]

Vấn đề là tôi nêu ra cho bạn một ý tưởng và bạn bổ sung , thêm vào những gì cần thiết, chứ nếu mỗi lúc bạn muốn thêm điều kiện vào thì sao ?
Thêm điều kiện là ngày CN thì cũng thế thôi. Ta đặt tên thêm như sau :
NgCN = IF(Sheet1!$A10<>"",WEEKDAY(Sheet1!A10),WEEKDAY(Sheet1!$A9))
Công thức trong Cell sẽ trở thành :
=IF(OR(NgTh="15",NgTh="304",NgTh="29",NgTh="11"),NgayDem&" Lễ",IF(NgCN=1,NgayDem&" CN",NgayDem&" Thường"))