Bạn đang xem bài viết Hàm If And, If Or, Left, Date Lồng Nhau Trong Excel được cập nhật mới nhất tháng 9 năm 2023 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
1. CÁCH DÙNG HÀM IF VỚI HÀM AND VÀ HÀM OR TRONG EXCEL 1,1 HÀM IFHàm IF là một trong những hàm logic giúp người dùng kiểm tra một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE hoặc trả về một giá trị khác nếu điều kiện là FALSE.
Cú pháp:
=IF( logical_test, value_if_true, value_if_false)Trong đó:
1,2 HÀM ANDHàm AND ít được dùng độc lập mà thường được dùng để mở rộng tính hữu dụng của các hàm thực hiện các kiểm nghiệm logic như hàm IF chẳng hạn.
Hàm AND trong excel là hàm sẽ trả về kết quả là TRUE nếu tất cả các đối số của hàm định trị là TRUE và trả về kết quả là FALSE nếu có ít nhất một đối số của hàm định trị là FALSE.
Cú pháp:
= AND(logical1, [logical2], ...)Trong đó:
logical1: Là điều kiện thứ nhất bạn muốn kiểm nghiệm. Đây là đối số bắt buộc.
logical2: Là những điều kiện khác mà bạn muốn kiểm nghiệm. Đây là đối số tùy chọn. Tối đa bạn có thể thêm là 255 đối số.
logical1 và logical2 là các mệnh đề logic. Hàm này cho phép sử dụng nhiều hơn 1 mệnh đề logic.
Các đối số (điều kiện) phải chỉ định về các giá trị logic hay các mảng hoặc tham chiếu có chứa giá trị logic.
Nếu một đối số mảng hoặc tham chiếu có chứa văn bản hoặc ô trống thì những đối số đó sẽ được bỏ qua.
Nếu dải ô được chỉ định không chứa giá trị logic thì hàm AND trả về lỗi #VALUE!.
Các lưu ý khi dùng hàm AND 1,3 HÀM ORCú pháp:
Hàm OR cũng giống như hàm AND khi nó có nhiệm vụ kiểm tra điều kiện của các giá trị nhưng khác ở chỗ chỉ cần một trong hai hoặc nhiều điều kiện là đúng thì giá trị hiển nhiên là True. Còn tất cả sai thì hiển nhiên là False rồi.
= OR (logical1, [logical2], ...)
logical1: bắt buộc. Là một giá trị logic.
logical2 : tùy chọn. Là một giá trị logic. Có thể lên tới 255 điều kiện khác nhau.
Trong đó của hàm OR rất giống với AND:
Chức Năng: Trả về TRUE nếu bất kỳ đối số nào là TRUE, trả về FALSE nếu tất cả các đối số là FALSE.
2. CÁCH SỬ DỤNG HÀM IF KẾT HỢP NHIỀU ĐIỀU KIỆN:
Hàm AND: Nếu điều kiện kiểm tra chứa hàm AND, Excel sẽ trả về TRUE (Đúng) nếu tất cả các điều kiện được đáp ứng; nếu không sẽ trả về FALSE (Sai).
Hàm OR: Trong trường hợp sử dụng hàm OR trong kiểm tra, Excel sẽ trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng; nếu khác sẽ trả về FALSE (Sai).
Nói tóm lại, có thể có 2 loại điều kiện cơ bản – với hàm logic AND và OR. Do đó, hàm IF của bạn nên nhúng một hàm AND và OR để làm phép thử logic tương ứng.
Để minh họa rõ hơn, chúng ta hãy xem xét một vài ví dụ của hàm IF với nhiều điều kiện.
Giả sử, bạn có một bảng với kết quả của hai điểm thi:
Ví dụ: Hãy đưa ra kết quả từ bảng danh sách học sinh gồm Họ và Tên, Điểm 1 phải lớn hơn hoặc bằng 60 và Điểm 2 phải lớn hơn hoặc bằng 90. Chỉ khi đáp ứng cả hai điều kiện trên, học sinh mới vượt qua kỳ thi.
Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó vào đối số kiểm tra hàm IF của bạn:
Trong đó:
Công thức IF/AND:
Sau khi nhập công thức cho ô E5, nhấn Enter được kết quả trả về như hình phía bên dưới:
Ví dụ: Vẫn là ví dụ về bảng học sinh gồm Họ và Tên, Điểm 1 phải lớn hơn hoặc bằng 60 và Điểm 2 phải lớn hơn hoặc bằng 90. Hãy đưa ra kết quả nếu ít nhất một trong các điều kiện được chỉ định được đáp ứng cả hai điều kiện trên, học sinh mới vượt qua kỳ thi.
Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó vào đối số kiểm tra hàm IF của bạn:
Trong đó:
Công thức IF/OR:
Sau khi nhập công thức cho ô E5, nhấn Enter được kết quả trả về như hình phía bên dưới:
Trong bảng trên, giả sử bạn có các tiêu chí sau để đánh giá điều kiện đạt của học sinh:
Nếu một trong các điều kiện trên được đáp ứng thì bạn đó được coi là đã vượt qua, nếu không thì trượt.
Công thức có vẻ phức tạp, nhưng thực ra cũng không quá khó lắm. Bạn chỉ cần biểu thị hai điều kiện là các câu lệnh AND và đặt chúng trong hàm OR vì không yêu cầu cả hai điều kiện được đáp ứng, chỉ cần một trong hai điều kiện đáp ứng là đủ:
Trong đó:
Cuối cùng, sử dụng hàm OR ở trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai).
Kết quả là bạn sẽ nhận được công thức IF sau với nhiều điều kiện AND/OR:
a) CÔNG THỨC HÀM IF CHO NGÀY THÁNG VỚI HÀM DATEVALUEĐể hàm IF có thể nhận dạng được ngày tháng trong một biểu thức logic, bạn phải đặt nó trong hàm DATEVALUE như thế này: DATEVALUE(“11/15/2023”). Công thức hoàn chỉnh có dạng như sau:
=IF(D4<DATEVALUE("11/15/2023"),"completed","Coming soon")Như minh họa của hình bên dưới, công thức hàm IF này đáng giá ngày tháng trong cột D và trả về giá trị “Completed” nếu như trò chơi này diễn tra trước ngày 15 tháng 11, còn ngược lại thì công thức sẽ trả về giá trị “Coming soon”.
Để chỉ ra những ngày đã diễn ra cách đây hơn 30 ngày, bạn có thể dùng công thức sau:
VIDEO HƯỚNG DẪN CƠ BẢN HÀM IF AND OR MONTH DATE:Hướng Dẫn Cách Lồng Ghép Hàm, Viết Hàm Lồng Nhau Trong Excel
Có 2 trường hợp chính thường xảy ra việc lồng hàm:
Trường hợp 1: Lồng ghép hàm để tránh lỗi cho hàm chính
Trường hợp 2: Đảm bảo đủ tính logic của vấn đề
Lồng ghép hàm để tránh lỗi cho hàm chínhĐây là trường hợp thường xảy ra với các hàm tham chiếu. Bởi việc tham chiếu rất dễ xảy ra lỗi. Do đó để tránh lỗi thì chúng ta lồng ghép thêm các hàm bẫy lỗi, hàm logic để tránh lỗi.
Ví dụ thường thấy là việc sử dụng hàm Vlookup
Hàm VLOOKUP rất dễ xảy ra lỗi nếu như:
Giá trị tìm kiếm là ô trống
Giá trị tìm kiếm không xuất hiện trong cột đầu tiên của vùng bảng tìm kiếm
Giá trị col_index_num (cột chứa kết quả) vượt quá số cột hiện có của bảng tìm kiếm
Do đó để tránh lỗi thì chúng ta hay thêm một số hàm như:
Hàm IF + hàm IsBlank để biện luận nếu giá trị tìm kiếm là ô trống
Hàm COUNTIF để đếm xem giá trị tìm kiếm có nằm trong cột đầu tiên của vùng bảng tìm kiếm không
Hàm MATCH để xác định cột chứa kết quả là cột thứ mấy trong bảng
Do đó mục đích chính là sử dụng hàm VLOOKUP, nhưng để tránh lỗi của hàm này thì có thể phải dùng tới rất nhiều hàm phụ để lồng ghép vào hàm chính.
Do đó để tránh báo kết quả lỗi #N/A thì chúng ta có thể thêm một số hàm để bẫy lỗi và phát hiện nguyên nhân gây ra lỗi
Nếu đếm giá trị F2 trong vùng A2:A8 ra kết quả bằng 0 (kết quả hàm COUNTIF=0) thì trả về dòng chữ “Không có mã này”
Nếu kết quả hàm COUNTIF ra khác không (tức là trường hợp giả thiết hàm IF sai, tại value_if_false) sẽ sử dụng hàm VLOOKUP
Tại G3 xét giả thiết giá trị tìm kiếm là ô trống
Nếu F3 là ô trống (mệnh đề F3=””) thì trả về kết quả là ô trống (hai dấu nháy kép thể hiện ô trống)
Nếu F3 không phải ô trống (tức là trường hợp giả thiết hàm IF sai, tại value_if_false) sẽ sử dụng hàm VLOOKUP để tìm kết quả.
Tuy nhiên nếu F2 là ô trống thì sao? Giá trị ở F3 không phải ô trống nhưng là giá trị không có trong cột A thì sao? Trường hợp này chúng ta phải lồng ghép việc bẫy lỗi cả ở G2 và G3 vào 1 hàm như sau:
=IF(F2=””,””,IF(COUNTIF($A$2:$A$8,F2)=0,”Không có mã này”,VLOOKUP(F2,$A$2:$D$8,4,0)))
Trong đó:
IF(F2=””,””, là xét trường hợp giá trị tìm kiếm là ô trống
IF(COUNTIF($A$2:$A$8,F2)=0,”Không có mã này”, là xét trường hợp giá trị tìm kiếm không nằm ở cột đầu tiên trong bảng tìm kiếm. Hàm IF này nằm ở mệnh đề value_if_false của hàm IF đầu tiên để xét khi F2 không phải là ô trống
VLOOKUP(F2,$A$2:$D$8,4,0) giá trị sẽ thực thi khi mà cả 2 giả thiết hàm IF đều sai. Hàm Vlookup nằm ở mệnh đề value_if_false của hàm IF thứ 2, mà hàm IF thứ 2 lại nằm trong mệnh đề value_if_false của hàm IF thứ 1 nên được hiểu là khi cả 2 mệnh đề đều sai.
Như vậy dù công thức nhìn có vẻ dài và phức tạp, lồng nhiều hàm IF vào nhau, nhưng thực chất thì chỉ đơn giản là sử dụng hàm VLOOKUP cộng thêm việc bẫy lỗi của hàm VLOOKUP mà thôi.
* Lưu ý: Ngoài các lỗi thường thấy, chúng ta còn phải biết về lỗi Loại dữ liệu. Bởi nếu không đúng loại dữ liệu thì chúng ta không thể tính toán đúng được.
Đảm bảo đủ tính logic của vấn đềViệc đảm bảo đủ tính logic của vấn đề thường phức tạp hơn nhiều so với việc bẫy lỗi. Bởi việc phân tích đủ và chính xác tính logic của những trường hợp phức tạp khá khó thực hiện, ngay cả với những ai đã làm tốt Excel.
Do đó để làm quen với việc này, chúng ta nên hình thành 1 thói quen đó là phân tích logic của vấn đề trước khi thực hiện giải quyết vấn đề đó. Khi đã phân tích đúng và đủ tính logic của vấn đề thì chúng ta có thể xác định được đâu là điểm bắt đầu, đâu là điểm kết thúc. Từ đó chúng ta sẽ biết cần sử dụng hàm nào, lồng ghép các hàm theo thứ tự nào, khi nào thì hoàn thành công thức.
Mục tiêu: Điền kết quả vào cột Xếp loại là 2 giá trị “Đỗ” hoặc “Trượt” dựa theo điều kiện ở cột Khóa học và Điểm thi
Nội dung logic của điều kiện:
Logic 1: Nếu điểm thi Excel dưới 7 là trượt. Tại đây ta có: Điểm thi môn Excel phụ thuộc vào giá trị ở cột Khóa học (cột C) và cột Điểm thi (cột D). Hai điều kiện này xét đồng thời do đó cần sử dụng hàm AND để kết hợp 2 nội dung này. Hàm IF để xét tính logic: nếu thỏa mãn đồng thời 2 điều kiện của logic 1 thì kết quả là “Trượt”
Logic 2: logic 2 sẽ thực hiện khi mệnh đề logic 1 là sai. Do đó sau khi xét mệnh đề đúng của logic 1 xong thì chúng ta sẽ xét ngay logic 2. Logic 2 là kết hợp điều kiện Khóa học = Word, điểm thi dưới 8 nên sẽ dùng hàm AND. Logic 2 được biểu diễn dưới hàm IF để xét: nếu thỏa mãn đồng thời 2 điều kiện của logic 2 thì kết quả là “Trượt”
Còn lại nếu cả 2 logic trên đều sai (tức là mệnh đề sai ở logic 2 cũng là mệnh đề sai của logic 1) thì kết quả trả về không phải là “Trượt”, khi đó sẽ là “Đỗ”
Cách viết hàm như sau:
E2=IF(AND(C2=”Excel”,D2<7),”Trượt”,IF(AND(C2=”Word”,D2<8),”Trượt”,”Đỗ”))
Logic 1 = IF(AND(C2=”Excel”,D2<7),”Trượt”,…. xét logic 2 tại vị trí dấu …. mệnh đề value_if_false của hàm IF
Hướng Dẫn Cách Dùng Hàm Vlookup Thay Thế Hàm If Lồng Nhau Trong Excel
Điều kiện để sử dụng hàm VLOOKUP thay thế hàm IF lồng nhau
Không phải trong mọi trường hợp chúng ta đều làm được điều này. Chỉ trong một số điều kiện nhất định thì chúng ta mới có thể sử dụng hàm VLOOKUP thay thế hàm IF lồng. Cụ thể:
Giá trị trong các điều kiện là dạng Số
Các điều kiện có tuân thủ theo thứ tự tăng dần và có tính liên tục
1. Đề bài: Xác định mức thưởng dựa theo điểm KPIs đạt được của từng nhân viên
Trong trường hợp này chúng ta hoàn toàn có thể sử dụng được hàm VLOOKUP để thay thế cho hàm IF lồng, vì thỏa mãn cả 2 điều kiện:
Bảng điểm KPIs xét thưởng phần điểm KPIs là các giá trị số (cột I)
Các giá trị trong cột I có thể sắp xếp được theo thứ tự tăng dần
Cách sử dụng hàm VLOOKUP thay thế hàm IF lồng nhau Thiết lập bảng điều kiệnKhông phải lúc nào chúng ta cũng có sẵn bảng điều kiện (như bảng Điểm KPIs xét thưởng) như trong ví dụ trên. Cũng không phải lúc nào điều kiện của bảng điều kiện cũng đảm bảo ngay được nguyên tắc: là dữ liệu dạng số và sắp xếp theo thứ tự tăng dần.
Do đó việc đầu tiên chúng ta cần làm đó là thiết lập lại bảng điều kiện.
Cụ thể như ở ví dụ trên ta có bảng điều kiện ban đầu là:
Như vậy chúng ta thấy các điều kiện chưa có sự thống nhất với nhau về khoảng cách, dấu của các mốc điều kiện trước và sau cho những khoảng nối tiếp nhau.
Do đó chúng ta có thể lập lại bảng điều kiện theo đúng tiêu chuẩn như sau:
Như vậy các điều kiện đều cùng phạm vi và cùng dấu điều kiện. Chúng ta có thể thay đổi các giá trị để đảm bảo đúng được tính logic của điều kiện ban đầu.
Cách viết hàm VLOOKUPKhi đó dùng hàm VLOOKUP như sau:
=VLOOKUP(D3, $I$3:$J$10, 2, 1)
Trong đó:
Giá trị tham chiếu là điểm KPIs theo cột D, từ ô D3
Vùng bảng tham chiếu là bảng Điểm KPIs, tính từ cột Giá trị trở đi (Cột giá trị phải là cột đầu tiên trong vùng bảng tham chiếu)
Kết quả tham chiếu là cột Mức thưởng, là cột thứ 2 trong vùng bảng tham chiếu
Phương thức tìm kiếm: Chọn số 1 là tìm kiếm tương đối
Nguyên tắc tìm kiếm tương đối của hàm VLOOKUP: Tìm theo giá trị NHỎ HƠN và GẦN NHẤT với giá trị tham chiếu.
Như vậy chúng ta đã có thể sử dụng duy nhất 1 hàm VLOOKUP là thu được kết quả tương đương với 7 hàm IF lồng nhau, mà kết quả vẫn chính xác.
Ngoài ra các bạn có thể tìm hiểu thêm về hàm VLOOKUP theo các bài viết sau:
Hàm Vlookup trong Excel, hướng dẫn sử dụng chi tiết và có ví dụ cụ thể Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp Bài tập Excel dùng hàm VLOOKUP có lời giải Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel
Tìm Hiểu Hàm Left Trong Excel Và Cách Sử Dụng Hàm Left
Khi sử dụng excel bạn không thể nào bỏ qua hàm LEFT thông dụng. Tuy nhiên, không ít người gặp phải khó khăn khi áp dụng nó trong thực hiện bảng tính. Để bạn đọc có thể hiểu hơn chúng tôi sẽ hướng dẫn về cách sử dụng hàm LEFT chi tiết.
Khái niệm hàm LEFT trong ExcelHàm LEFT trả về một hoặc nhiều ký tự đầu tiên trong chuỗi. Bên cạnh đó, nó còn được được gọi là hàm cắt chuỗi ký tự bên trái.
Cú pháp hàm LEFT=LEFT (text;[num_chars]) ADVERTISEMENT
Trong đó:
Text: Đối số bắt buộc. Nó là chuỗi văn bản, tham chiếu ô tới chuỗi các ký tự cần trích xuất.
Num_chars: Đối số tùy chọn.
Num_chars có điều kiện là phải lớn hơn hoặc bằng không. Nếu num_chars đưa ra kết quả < 0 thì hàm này sẽ được trả về là lỗi #VALUE!. Trong trường hợp Num_chars vượt quá độ dài văn bản, hàm LEFT sẽ trả về toàn bộ văn bản.
Còn trường hợp khác đó là các bạn bỏ qua num_chars thì nó sẽ được hiểu mặc định num_chars = 1.
Ví dụ minh họaBài toán đặt ra là: Để trích xuất 9 ký tự đầu tiên tính từ văn bản cơ trong ô A2 với dòng chữ chúng tôi ta sẽ dùng công thức như sau:
=LEFT (A2, 9)
Lúc này kết quả trả về sẽ là: tinhocvan
Hoặc tham chiếu đến ô chứa chuỗi văn bản.
Hàm LEFT là một loại hàm văn bản. Do đó, nó sẽ trả về kết quả là chuỗi văn bản, ngay cả trong trường hợp giá trị nguồn là số. Khi thực hiện hàm LEFT với dữ liệu số, bạn cần kết hợ với hàm VALUE để kết quả trả về là số.
Hướng dẫn cách sử dụng hàm LEFTHàm LEFT khá thông dụng. Bởi vậy cách sử dụng hàm LEFT được xem là kiến thức cơ bản mà bất cứ ai sử dụng Excel cũng nên biết. Trên thực tế các hàm trong Excel không hề khó sử dụng, bạn chỉ cần bỏ công tìm hiểu một chút là có thể thành thạo.
Cách sử dụng hàm LEFT để phân xuất chuỗi ký tự conTrong trường hợp sử dụng hàm LEFT để phân xuất một phần của chuỗi văn bản đứng trước một ký tự nhất định, ta làm một ví dụ để tìm hiểu cách làm:
Bạn cần lấy họ (first name) trong cột tên đầy đủ (full name) mà tiêu đề đã cho trước đó hoặc cần tách mã quốc gia trừ cột số điện thoại? Bạn cần lưu ý mỗi tên và mã chứa số lượng ký tự khác nhau.
Theo đó trong trường hợp họ và tên được phân cách bởi loại (dấu cách), ta sẽ tìm vị trí của ký tự khoảng trống được tính nhờ hàm SEARCH hoặc FIND, cụ thể như sau:
=LEFT (A2, SEARCH(” “, A2))
Tiếp theo để có thể phát triển thêm công thức và loại đi khoảng trống sẵn có ta sẽ thực hiện trừ 1 từ kết quả của hàm SEARCH. Công thức:
=LEFT (A2, SEARCH(” “, A2)-1)
=LEFT (A2, SEARCH(“-“, A2)-1)
Công thức để tách chuỗi ký tự con đứng trước bất cứ kí tự nào đó được đúc kết là:
LEFT (chuỗi, SEARCH (ký tự,chuỗi)-1)
Cách dời n ký tự cuối khỏi chuỗi ký tựChức năng của hàm LEFT là phần xuất chuỗi ký tự con từ chuỗi ký tự văn bản có sẵn. Vậy khi muốn dời một số ký tự từ phần cuối của chuỗi văn bản, đồng thời lại đặt phần còn lại vào một ô khác cần làm như thế nào? Để giải được ví dụ trên ta sẽ kết hợp dùng hàm LEFT và LEN theo công thức sau:
LEFT (chuỗi, LEN(chuỗi) – số_kí_tự_muốn_dời)
Nguyên tắc hoạt động của công thức là: Hàm LEN lấy tổng số ký tự trong một chuỗi có trong văn bản tách rồi trừ đi số lượng ký tự không mong muốn từ tổng độ dài của ký tự văn bản đó. Hàm LEFT lúc này đóng vai trò là trả về số ký tự còn lại cần lấy.
Ví dụ: Để dời 7 ký tự cuối cùng trong ô A2, bạn cần áp dụng công thức như sau:
=LEFT (A2, LEN(A2)-7)
Cách buộc hàm LEFT trả kết quả về là một sốĐặc tính của hàm LEFT là sẽ luôn luôn trả về cho bạn một kết quả là văn bản chữ. Thậm chí ngay cả khi bạn đặt một vài chữ số trong chuỗi ký tự, kết quả nhận được vẫn là văn bản.
Bởi vậy, câu hỏi được đặt ra cho nhiều người đó là làm thế nào để kết quả của hàm LEFT có thể giúp bạn trả về là con số chứ không phải chuỗi văn bản? Rất dễ dàng, bạn chỉ cần sử dụng hàm VALUE. Công thức giúp bạn sử dụng đó là:
VALUE(LEFT ())
Ví dụ minh họa:
Làm cách nào để phân xuất 2 ký tự đầu tiên của chuỗi trong ô A2. Sau đó chuyển kết quả sang con số cần dùng, bạn sẽ áp dụng công thức sau:
=VALUE(LEFT (A2,2))
Kết quả trả về tương tự bảng sau:
Tại sao hàm LEFT bị lỗi và cách khắc phụcCách sử dụng hàm LEFT không chỉ đòi hỏi bạn áp dụng đúng công thức mà còn phải đáp ứng được các điều kiện cần và đủ. Nếu hàm LEFT không thực hiện được trong trang tính, bạn sẽ vướng phải một số nguyên nhân quan trọng như sau:
NUM_CHARS nhỏ hơn 0Nếu công thức hàm LEFT trả về #VALUE (lỗi), để biết được thao tác đã sai ở đâu bạn cần kiểm tra lại giá trị của num_chars trong công thức. Theo đó nếu num_chars là số âm, quá đơn giản bạn chỉ cần loại bỏ các cấu trúc lỗi là xong.
Thông thường, lỗi cách sử dụng hàm LEFT này xuất hiện khi num_chars mang nhiều chức năng khác nhau. Để khắc phục, bạn chỉ cần copy hàm đó vào một ô khác hay chọn trên thanh công thức. Trên thanh công thức bạn chỉ cần nhấn F9 để xem hàm tương đương nhận thấy giá trị nhỏ hơn 0, nên kiểm tra lại hàm lỗi chi tiết nhất và sửa chữa chúng
Ví dụ:
Phân xuất mã quốc gia từ số điện thoại bất kỳ được cung cấp sẽ áp dụng công thức sau:
LEFT (A2, SEARCH(“-“, A2)-1)
Theo đó, loại hàm SEARCH trong phần num_chars sẽ tìm ra vị trí của dấu (-) đầu tiên trong chuỗi nguồn. Bạn có thể thêm thao tác trừ 1 để xóa dấu (-) khỏi kết quả trả về. Nếu bạn thay thế -1 với -11, kết quả sẽ là lỗi #VALUE. Lý do vì num_chars tương đương với số âm
Khoảng trống đứng đầu trong văn bản gốcBạn nên kiểm tra những khoảng trống hay còn gọi là dấu cách ở đầu giá trị nguồn. Ngoài ra, nếu bạn đã thực hiện copy dữ liệu từ một trang web hay từ một loại nguồn bên ngoài, sẽ là nguyên nhân gây ra lỗi của công thức hàm.
Theo đó, cách tốt nhất để loại bỏ những dấu cách trong đầu trang tính. Ngoài ra, bạn nên sử dụng hàm TRIM hoặc CELL CLEANER trong công thức tính.
Trong Excel, hầu như ngày tháng được mặc định là số nguyên như từ 1/1/1900 là số 1. Để hiểu hơn về c ách sử dụng hàm LEFT trong trường hợp này bạn có thể áp dụng:
Trong ô A1 có 11- tháng 1 – 2023, khi thực hiện phân xuất bằng công thức hàm LEFT (A1,2). Lúc này bạn sẽ nhận được kết quả là 42 – 2 chữ số đầu tiên trong số 42746 biểu thị ngày 11/1/2023.
?Hàm Left Trong Excel Là Gì? Hướng Dẫn Sử Dụng Hàm Left Đơn Giản
Qua đó, chúng ta có thể tóm gọn lại tác dụng của việc sử dụng hàm Left như sau:
– Giúp lấy những ký tự mình muốn ra khỏi 1 chuỗi các ký tự dài.
– Đơn giản, dễ dàng, tiện dụng và nhanh chóng.
– Có thể làm được trên nhiều chuỗi dữ liệu khác nhau.
Cách sử dụng hàm Left trong Excel để cắt chuỗi các ký tự bên tráiCách này dùng để lấy những ký tự như số, chữ ở phía bên trái của chuỗi ký tự ra một ô khác được chúng ta chọn. Mà vẫn không làm mất đi chuỗi ký tự ban đầu.
Hướng dẫn thực hiện nhanh hàm lấy ký tự trong ExcelBước 1: Chọn một ô bất kỳ để nhập công thức
Mẹo nhỏ: Bạn nên chọn một ô gần với chuỗi dữ liệu để sau khi làm xong dễ dàng so sánh.
Bước 2: Nhập công thức =Left(
Bước 3: Ở phần Text, bạn chọn ô có chuỗi ký tự sau đó bấm dấu phẩy”,” hoặc dấu chấm phẩy “;” tùy theo công thức yêu cầu.
– Không được để trống
– Chú ý đến dấu ngăn cách giữa Text và Num_chars (có thể là dấu “,” hoặc chấm phẩy “;”)
– Nếu bạn nhập sai dấu ngăn cách sẽ dẫn đến lỗi. Excel sẽ báo để bạn có thể sửa lại lỗi.
Bước 4: Ở phần Num_chars bạn hãy nhập số lượng ký tự muốn lấy. Rồi sau đó bấm dấu ngoặc phải “)” (ở trong ví dụ này mình lấy số là 3 vì đề bài yêu cầu lấy ra 3 ký tự bên trái).
– Không được để trống, nếu để trống máy sẽ tự động điền 0 hoặc 1.
– Num_chars phải lớn hơn giá trị 0
– Ở cuối công thức bắt buộc phải có dấu ngoặc phải “)”.
Bước 5: Nhấn phím Enter và bạn đã có được chuỗi ký tự bạn muốn.
Bước 6: Nếu có nhiều chuỗi ký tự. Thì bạn hãy giữ chuột ở ô vuông nhỏ góc phải của ô dữ liệu đồng thời kéo xuống.
Hàm cắt chuỗi có điều kiện trong Excel là gì?Hàm cắt chuỗi là một loại hàm thuộc dạng công thức được mặc định sẵn. Hàm này có chức năng tách một chuỗi các ký tự thành các phần tùy vào mục đích của người sử dụng. Các hàm cơ bản thường sử dụng nhất trong nhóm hàm cắt chuỗi có thể kể đến như hàm RIGHT, LEFT, MID, TRIM,…
Trong cuộc sống, khi bạn thao tác với các bảng dữ liệu. Chắc chắn sẽ phát sinh rất nhiều trường hợp mà bạn cần phải sử dụng đến nhóm hàm cắt chuỗi có điều kiện trong Excel. Một số mục đích sử dụng của nhóm hàm này có thể kể đến như sau:
Tách riêng một ô chứa chuỗi ký tự thành từng ô khác nhauCác bảng danh sách như thông tin nhân viên, học viên hoặc khách hàng,… thường yêu cầu bạn phải tách riêng Họ, Tên đệm, Tên từ một ô dữ liệu có chứa họ tên đầy đủ của những người này.
Nếu bạn thực hiện nhập thủ công tên, họ, tên đệm vào từng ô cho cả danh sách hiển nhiên là rất tốn thời gian và công sức. Chính vì vậy, hàm cắt chuỗi có điều kiện trong Excel sẽ giúp các bạn giải quyết vấn đề này một cách vô cùng hiệu quả và nhanh chóng.
Xác định các ký tự cần lấy nằm trong một dãy có nhiều ký tự khác nhau.Là kế toán của một công ty. Sếp yêu cầu bạn phải thực hiện trả thưởng cho nhân viên dựa vào các ký tự đặc biệt nào đó. Dãy ký tự này nằm trong dãy mã số nhân viên. Ví dụ nhân viên A có mã số là ABCDGD, nhân viên B có mã số là ABCDNV và ông C có mã nhân viên là DEGHNV.
Nếu hai ký tự cuối chứa GD mức thưởng là 1.000.000đ, NV có mức thưởng là 500.000đ. Lúc này hàm cắt chuỗi sẽ giúp bạn xác định ra hai ký tự cuối trong dãy mã số nhân viên là gì. Để bạn dễ dàng hơn khi tiến hành trả thưởng cho nhân viên A, nhân viên B, và nhân viên C.
Trong quá trình nhập liệu, có thể xảy ra những trường hợp người cung cấp thông tin gõ dư quá nhiều các ký tự không cần thiết. Ví dụ như dấu trắng ( khoảng cách) ở đầu hoặc ở cuối họ và tên, số ký hiệu thứ tự nhân viên,…
Điều này sẽ làm ảnh hưởng đến kết quả tính toán cho toàn bộ danh sách. Kiểm tra và chỉnh sửa thủ công theo từng hạng mục lúc này là một việc vô cùng mất thời gian. Chính vì thế, hàm cắt chuỗi có điều kiện trong Excel sẽ thay các bạn thực hiện điều này mà không hề tốn quá nhiều thời gian và công sức.
Hàm Left: Left sẽ giúp người dùng tách các ký tự trong một dãy từ trái sang phải thành một phần riêng biệt.
Cấu trúc là: =LEFT( chuỗi ký tự, số lượng ký tự cần lấy).
Một số hàm lấy ký tự trong chuỗi khác cũng được sử dụng nhiều như:
– Hàm RIGHT: là một hàm thuộc nhóm hàm cắt chuỗi. Có công dụng lấy các ký tự trong một dãy từ phải sang trái tạo thành một phần riêng biệt.
Cấu trúc là: = RIGHT( chuỗi ký tự, số lượng ký tự cần lấy).
– Hàm MID: Cũng là hàm dùng để lấy một số ký tự nằm trong chuỗi. Tuy nhiên, thay vì từ trái hay từ phải qua. Hàm MID sẽ cho phép chúng ta lấy được những ký tự nằm ở giữa chuỗi lớn.
Cấu trúc là: =MID(chuỗi ký tự, vị trí bắt đầu lấy,số lượng ký tự cần lấy ra)
– Hàm TRIM: Tuy không được sử dụng để tách và xuất các ký tự trong chuỗi ra thành một ô riêng biệt. Tuy nhiên, hàm TRIM lại cực kỳ hữu ích khi giúp người sử dụng có thể loại bỏ những khoảng trắng mà không cần thiết ở đầu và cuối chuỗi ký tự. Điều này rất cần thiết ở trong các trường hợp cần tính toán số lượng ký tự trong một chuỗi mà không bao gồm các khoảng trắng đầu và cuối.
Cấu trúc: =TRIM(chuỗi ký tự)
Những lỗi thường gặp khi sử dụng hàm Left trong Excel và cách khắc phục.Cách sử dụng hàm Left không chỉ đơn thuần đòi hỏi bạn áp dụng đúng công thức mà còn phải đáp ứng được những điều kiện cần và đủ. Nếu hàm LEFT không thực hiện được trong trang tính, bạn sẽ vướng phải một số lỗi quan trọng như sau:
Nếu công thức hàm LEFT trả về kết quả #VALUE (lỗi). Để biết được thao tác đã làm sai ở đâu. Bạn cần kiểm tra lại giá trị của Num_chars ở trong công thức. Theo đó nếu num_chars là một số âm. Quá đơn giản! Bạn chỉ cần loại bỏ đi các cấu trúc lỗi là xong.
Thông thường, lỗi về cách sử dụng hàm LEFT này xuất hiện khi num_chars chứa nhiều chức năng khác nhau. Để khắc phục thì bạn chỉ cần copy hàm đó vào một ô khác hoặc chọn trên thanh công thức. Trên thanh công thức bạn chỉ cần nhấn F9 để xem hàm tương đương nhận thấy được giá trị nhỏ hơn 0. Nên bạn hãy kiểm tra lại hàm lỗi chi tiết nhất và sửa chữa chúng.
Muốn phân xuất mã quốc gia từ số điện thoại bất kỳ được cung cấp. Sẽ áp dụng công thức sau:
LEFT (A2, SEARCH(“-“, A2)-1)
Theo đó, hàm SEARCH ở trong phần num_chars sẽ tìm ra vị trí của dấu (-) đầu tiên có trong chuỗi nguồn. Bạn có thể thực hiện thêm thao tác trừ 1 để xóa dấu (-) khỏi kết quả trả về. Nếu bạn thay thế -1 với -11, kết quả sẽ trả về là lỗi #VALUE. Nguyên nhân vì num_chars đã tương đương với số âm.
Bạn nên kiểm tra lại những khoảng trống hay còn gọi là những dấu cách ở đầu giá trị nguồn. Ngoài ra, nếu bạn có thực hiện copy dữ liệu từ một trang web hay từ một loại nguồn bên ngoài. Đây sẽ là nguyên nhân gây ra lỗi của công thức hàm.
Theo đó, phương pháp tốt nhất để loại bỏ những dấu cách trong đầu trang tính. Ngoài ra, bạn nên sử dụng đến hàm Trim hoặc Cell Cleaner trong công thức tính.
Trong Excel, hầu như ngày tháng được cài mặc định là số nguyên như từ 1/1/1900 là số 1. Để hiểu hơn về cách sử dụng hàm Left ở trong trường hợp này bạn có thể áp dụng:
Trong ô A1 có 11- tháng 1 – 2023, khi thực hiện phân xuất bởi công thức LEFT (A1,2). Lúc này bạn sẽ thu được kết quả là 42 – 2 chữ số đầu tiên trong số 42746 biểu thị ngày 11/1/2023.
Một số mẹo nhỏ khi sử dụng hàm Left trong ExcelLeft là một loại hàm trong Excel khá thông dụng. Bởi vậy cách sử dụng lệnh của hàm này được xem là kiến thức cơ bản mà bất kỳ ai sử dụng Excel cũng nên biết. Chỉ cần bạn bỏ công để tìm hiểu một chút là có thể thành thạo được rồi.
Trong trường hợp bạn sử dụng hàm để phân xuất một phần của chuỗi văn bản đứng trước một ký tự nhất định. Chúng ta thực hiện một ví dụ để tìm hiểu cách làm:
Bạn muốn lấy họ (first name) trong cột tên đầy đủ (full name). Tiêu đề đã cho sẵn trước đó hoặc cần tách mã quốc gia trừ cột số điện thoại? Bạn chỉ cần lưu ý mỗi tên và mã chứa số lượng ký tự khác nhau.
Theo đó, trong trường hợp họ và tên được phân cách bởi ký tự dấu cách. Ta sẽ tìm vị trí của ký tự khoảng trống được tính nhờ vào hàm Search hoặc Find, cụ thể như sau:
=LEFT (A2, SEARCH(” “, A2))
Tiếp theo để có thể phát triển công thức. Đồng thời loại đi khoảng trống sẵn có ta sẽ thực hiện trừ 1 từ kết quả ở hàm Search. Công thức:
=LEFT (A2, SEARCH(” “, A2)-1)
Tương tự, bạn có thể lấy mã quốc gia ở cột của số điện thoại trong công thức. Khác biệt duy nhất ở chỗ là khi dùng hàm Search đặt vị trí dấu (“-“) ở trước khoảng trống. Công thức áp dụng:
=LEFT (A2, SEARCH(“-“, A2)-1)
Công thức để tách chuỗi ký tự con đứng trước bất kỳ kí tự nào đó được đúc kết là:
LEFT (chuỗi, SEARCH (ký tự,chuỗi)-1)
Chức năng của hàm chính là phân xuất chuỗi ký tự con từ chuỗi ký tự văn bản có sẵn. Vì vậy khi muốn dời một số ký tự từ phần cuối của chuỗi văn bản. Đồng thời, lại đặt phần còn lại vào một ô khác thì cần làm như thế nào? Để giải được ví dụ trên ta sẽ kết hợp dùng hàm Len và Left theo công thức: LEFT (chuỗi, LEN(chuỗi) – số kí_tự_ muốn dời).
Nguyên tắc hoạt động của công thức này là: Hàm LEN sẽ lấy tổng số ký tự trong một chuỗi có trong văn bản. Tách rồi trừ đi lượng ký tự không mong muốn. Tính từ tổng độ dài của ký tự văn bản đó. Hàm Left lúc này đóng vai trò là trả lại số ký tự còn lại cần lấy.
Đặc tính của hàm tách chuỗi là sẽ luôn luôn thu về cho bạn một kết quả là văn bản chữ. Thậm chí ngay cả khi bạn đặt một vài chữ số ở trong chuỗi ký tự. Kết quả thu được vẫn là dạng văn bản.
Bởi vậy, câu hỏi được đặt ra cho nhiều người đó là làm thế nào để kết quả của Left có thể giúp bạn thu về là con số chứ không phải chuỗi văn bản? Rất dễ dàng, bạn chỉ cần sử dụng đến hàm VALUE. Công thức để giúp bạn sử dụng đó là: VALUE(LEFT ()).
Hàm Date Trong Excel, Cách Dùng, Cấu Trúc Hàm
Định nghĩa hàm Date, hàm ngày tháng trong Excel
Hàm DATE được tích hợp sẵn trên Excel cho phép người dùng tạo một ngày với đầy đủ các thành phần năm, tháng và ngày. Đặc biệt hàm DATE cực kỳ hữu ích trong trường hợp cung cấp ngày làm đầu vào cho các hàm khác như hàm SUMIFS hay hàm COUNTIFS, vì người dùng có thể dễ dàng tập hợp các giá trị năm, tháng và ngày từ một ô tham chiếu hoặc kết quả công thức.
Mục đích
Tạo các ngày hợp lệ từ năm, tháng và ngày.
Giá trị trả về
Trả về kết quả là giá trị số của một ngày cụ thể trong Excel.
Cú pháp
= DATE (năm, tháng, ngày)
Các tham số
Năm – Năm sử dụng khi tạo ngày.
Tháng – Tháng để sử dụng khi tạo ngày.
Ngày – Ngày để sử dụng khi tạo ngày.
Lưu ý
– Hàm DATE trong Excel trả về kết quả là giá trị số của một ngày cụ thể. Định dạng kết quả một ngày để hiển thị là một ngày (gồm năm, tháng, ngày).
– Nếu năm nằm trong khoảng giữa 0 và 1900, Excel sẽ thêm 1900 vào năm.
– Tháng có thể lớn hơn 12 và nhỏ hơn 0. Nếu tháng lớn hơn 12, Excel sẽ thêm tháng vào tháng đầu tiên trong năm. Nếu tháng nhỏ hơn hoặc bằng 0, Excel sẽ trừ đi giá trị tuyệt đối của tháng cộng thêm 1 (ví dụ ABS (tháng) + 1) tính từ tháng đầu tiên của năm.
– Ngày có thể tương đối hoặc tuyệt đối. Nếu ngày lớn hơn các ngày trong tháng cụ thể, Excel sẽ thêm ngày vào ngày đầu tiên của tháng được chỉ định. Nếu ngày nhỏ hơn hoặc bằng 0, Excel sẽ trừ giá trị tuyệt đối của ngày và cộng thêm 1 (ví dụ ABS (ngày) + 1) tính từ ngày đầu tiên của tháng được chỉ định.
Một số ví dụ về hàm Date – Ví dụ 1: Đánh dấu ngày lớn hơn Công thức chung:
=A1gt;DATE(year,month,day)
Giải thích:
Nếu muốn đánh dấu các ngày lớn hơn hoặc nhỏ hơn một ngày cụ thể với định dạng có điều kiện, bạn có thể sử dụng công thức đơn giản dựa trên hàm DATE. Ví dụ, nếu bạn có ngày trong các ô B4: G11 và làm nổi bật các ô chứa ngày lớn hơn ngày 1/8/2023, chọn phạm vi và tạo quy tắc CF mới sử dụng công thức này:
=B4gt;DATE(2023,8,1)
Sau khi lưu rule, bạn sẽ nhìn thấy các ngày lớn hơn ngày 8/1/2023 được đánh dấu.
Ý nghĩa công thức:
Hàm DATE tạo một ngày hợp lệ trên Excel với các giá trị năm, tháng, và ngày. Sau đó, chỉ đơn giản là vấn đề so sánh mỗi ngày trong phạm vi với ngày được tạo bằng hàm DATE. Tham chiếu B4 hoàn toàn tương đối, vì vậy sẽ cập nhật theo rule được áp dụng cho mỗi ô trong vùng và bất kỳ ngày nào lớn hơn 8/1/2023 sẽ được đánh dấu.
So sánh lớn hơn hoặc bằng:
Bạn có thể sử dụng tất cả các toán tử tiêu chuẩn trong công thức này để điều chỉnh hành vi nếu cần. Ví dụ, để làm nổi bật tất cả các ngày lớn hơn hoặc bằng ngày 8/1/2023, sử dụng công thức:
=B4gt;=DATE(2023,8,1)
Sử dụng một ô khác để làm đầu vào:
Không cần mã hóa ngày tháng vào rule. Để tạo ra một rule tương tác linh hoạt hơn, sử dụng một ô khác làm biến trong công thức. Ví dụ, nếu muốn sử dụng ô C2 làm ô đầu vào, đặt tên cho ô C2 “input”, nhập ngày và sử dụng công thức này:
=B4gt;input
Sau đó thay đổi ngày trong ô C2 thành bất kỳ thứ gì bạn muốn và rule định dạng có điều kiện sẽ phản hồi ngay lập tức.
– Ví dụ 2: Lấy ngày cuối cùng của tháng Công thức chung:
=EOMONTH(date,0)
Giải thích:
Cách dễ nhất để tính ngày cuối cùng của tháng là sử dụng hàm EOMONTH.
Trong ví dụ trên, công thức trong ô B5 là:
=EOMONTH(B5,0)
Ý nghĩa công thức:
Đối số thứ 2 (tháng) của hàm EOMONTH cho phép bạn lấy được ngày cuối cùng trong những tháng tiếp theo hoặc trong tháng trước. Khi bạn sử dụng số 0 trong nhiều tháng, EOMONTH sẽ trả lại kết quả vào ngày cuối cùng trong tháng đó.
Để lấy ngày cuối cùng của tháng trước, sử dụng công thức:
=EOMONTH(date,-1)
Để lấy ngày cuối cùng của tháng tiếp theo, sử dụng công thức:
=EOMONTH(date,1)
Giải pháp thay thế thông minh:
Nếu bạn là người thích xây dựng các công thức thông minh để phân loại, bạn cũng có thể sử dụng hàm DATE trong Excel để lấy ngày cuối cùng của tháng:
=DATE(YEAR(date),MONTH(date)+1,0)
Thủ thuật với công thức này là cung cấp số 0 làm đối số ngày. Khi bạn cung cấp số 0 làm đối số ngày trong hàm DATE, hàm date sẽ quay trở lại ngày đầu tiên của tháng trước cho đến ngày cuối cùng của tháng đó. Vì vậy, bằng cách cộng thêm 1 vào đối số tháng, và sử dụng 0 cho đối số ngày, hàm DATE cuộn lại cho ngày cuối cùng của tháng “ban đầu”.
– Ví dụ 3: Bôi đen các ngày có cùng tháng và năm Công thức chung:
=TEXT(A1,”myyyy”)=TEXT(date,”myyyy”)
Giải thích:
Nếu muốn sử dụng định dạng có điều kiện để đánh dấu các ngày cùng tháng cùng năm với nhau, bạn có thể sử dụng công thức đơn giản dựa trên hàm TEXT.
=TEXT(B4,”myyyy”)=TEXT(DATE(2023,6,1),”myyyy”)
Sau khi lưu rule, bạn sẽ nhìn thấy tất cả các ngày cùng tháng 6 năm 2023 được đánh dấu.
Ý nghĩa công thức:
Công thức này sử dụng hàm TEXT để ghép tháng và năm của mỗi ngày. Sau đó, 2 ngày được kiểm tra sự bình đẳng. TEXT là một hàm hữu ích cho phép bạn chuyển đổi một số thành văn bản ở định dạng văn bản mà bạn chọn. Trong trường hợp này định dạng là định dạng ngày tùy chỉnh “myyyy”, có nghĩa là đối số tháng không có số 0 ở đầu và đối số năm có 4 chữ số. Ví dụ, nếu A1 chứa ngày 9/6/2023, TEXT (A1, “myyyy”) sẽ tạo chuỗi văn bản “62023”.
Sử dụng các ô khác cho đầu vào:
Bạn không cần mã hoá một ngày vào rule. Để tạo rule linh hoạt hơn, bạn có thể sử dụng các ô khác làm biến. Ví dụ, nếu bạn đặt tên cho ô E2 là “date”, bạn có thể viết lại công thức như sau:
=TEXT(B4,”myyyy”)=TEXT(date,”myyyy”)
Và bất cứ khi nào bạn thay đổi ngày trong E2, rule định dạng có điều kiện sẽ cập nhật ngay lập tức. Điều này đơn giản hóa công thức và giúp bạn đọc dễ hơn.
– Ví dụ 4: Tính tổng giá trị theo ngày Công thức chung:
=SUMIFS(amount,start_date,”gt;”amp;A1,end_date,”lt;gt;
Giải thích:
Để tính tổng các giá trị giữa 2 ngày, bạn có thể sử dụng hàm SUMIFS. Trong ví dụ trên, ô H5 sử dụng công thức này:
=SUMIFS(amount,date,”gt;”amp;H5,date,”lt;gt;
Công thức này tính tổng số tiền trong cột D khi một ngày trong cột C nằm giữa một ngày trong cột H5 và một ngày trong cột H6. Trong ví dụ, H5 chứa ngày 15/9/2023 và H6 chứa ngày 15/10 /2023.
Ý nghĩa của công thức
Hàm SUMIFS hỗ trợ toán tử logic ( logical operator) của Excel (tức là “=”, “gt;”, “gt; =”,…) và nhiều tiêu chí khác.
Để ghép khớp ngày giữa hai giá trị, chúng ta phải sử dụng hai tiêu chí. SUMIF yêu cầu mỗi tiêu chí phải được nhập dưới dạng một vùng tiêu chuẩn:
“gt;”amp;H5,date
“lt;”amp;h6,date criteria=””gt;
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (amp;).
Cần chú trọng khi sử dụng dấu lớn hơn hoặc bằng (“gt; =”) và ít hơn hoặc bằng (“lt;=”) nếu=”” muốn=”” bao=”” gồm=”” (ghép)=”” ngày=”” bắt=”” đầu=”” hoặc=”” ngày=”” kết=”” thúc=”” cũng=”” như=”” ngày=”” giữa=””gt;
– Ví dụ 5: Tính số ngày trong năm Công thức chung:
=date-DATE(YEAR(date),1,0)
Giải thích:
Nếu muốn tính số ngày từ một định dạng ngày cụ thể (tức là ngày thứ bao nhiêu trong năm), bạn có thể sử dụng công thức sử dụng kết hợp các hàm DATE và YEAR.
Ví dụ: với ngày 1/6/2023 trong ô B4, sử dụng công thức sau sẽ trả lại kết quả 153:
=B4-DATE(YEAR(B4),1,0)
Và ngày 1/6/2023 là ngày thứ 153 trong năm 2023.
Ý nghĩa của công thức:
Công thức này tận dụng lợi thế đó là thực tế ngày tháng chỉ là các số liên tiếp trong Excel. Nó xác định ngày cuối cùng của năm trước và trừ đi kết quả ngày ban đầu trong ô B4. Kết quả trả lại là ngày thứ bao nhiêu trong năm.
Lưu ý đối số ngày trong hàm DATE được cung cấp bằng 0. Hàm DATE còn có chức năng là có thể xử lý các giá trị DAY nằm ngoài phạm vi và điều chỉnh kết quả phù hợp.
Tính số ngày đến ngày hiện tại trong năm
Để điều chỉnh công thức trả lại kết quả tính số ngày kể từ ngày thứ n trong năm cho ngày hiện tại, chỉ cần sử dụng hàm TODAY:
=TODAY()-DATE(YEAR(TODAY()),1,0)
– Ví dụ 6: Chuyển đổi định dạng dd/mm/yy (ngày/tháng/năm) thành mm/dd/yy (tháng/ngày/năm) Công thức chung:
=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))
Giải thích:
Để chuyển đổi định dạng ngày tháng từ định dạng dd/mm/yy (ngày/tháng/năm) sang mm/dd/yy (tháng/ngày/năm), bạn có thể sử dụng công thức dựa trên hàm DATE. Trong ví dụ trên, công thức trong ô C5 là:
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))
Chuyển đổi các giá trị văn bản trong B5 29/02/16 thành một định dạng ngày thích hợp trên Excel.
Ý nghĩa của công thức:
Giá trị cốt lõi của công thức này là hàm DATE, được sử dụng để ghép một giá trị ngày thích hợp trên Excel. Hàm DATE yêu cầu giá trị năm, tháng và ngày hợp lệ, vì vậy chúng được phân tích cú pháp từ chuỗi văn bản ban đầu như sau:
Giá trị năm được trích bằng hàm RIGHT:
RIGHT(B5,2)+2000
Hàm RIGHT nhận được nhiều nhất 2 ký tự từ giá trị gốc. Số 2000 được thêm vào kết quả để tạo ra năm hợp lệ. Số này làm đối số năm trên hàm DATE.
Giá trị tháng được trích bằng:
MID (B5,4,2)
MID lấy 4-5 ký tự. Kết quả được đưa vào hàm DATE làm đối số tháng.
Giá trị ngày được trích bằng:
LEFT (B5,2)
LEFT lấy 2 ký tự cuối cùng của giá trị văn bản gốc, đưa vào hàm DATE làm đối số ngày.
Ba giá trị được trích ở trên được đưa vào hàm DATE như sau:
= DATE (2023, “02”, “29”)
Mặc dù tháng và ngày được cung cấp dưới dạng văn bản, hàm DATE sẽ tự động chuyển đổi sang số và trả về kết quả ngày hợp lệ.
Lưu ý: giá trị năm 2023 đã được tự động chuyển đổi thành số khi thêm 2000 vào.
Thêm vấn đề về dấu cách:
Nếu giá trị văn bản gốc ban đầu chứa thêm dấu cách ở đầu hoặc cuối, bạn có thể sử dụng thêm hàm TRIM để xóa:
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))
– Ví dụ 7: Tạo hàng loạt ngày giống nhau, chỉ khác năm Công thức chung:
=DATE(YEAR(date)+1,MONTH(date),DAY(date))
Giải thích:
Nếu cần tạo một loạt ngày tháng giống nhau, có số năm tăng dần, bạn có thể sử dụng công thức có các hàm DAY, MONTH, YEAR, và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, ngày trong ô B6 là ngày bắt đầu và công thức trong ô B7 là:
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị năm. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và tháng, và và công thêm 1 vào năm.
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
= DATE (2010 + 1,1,15)
= DATE (2011,1,15)
= 1/15/2011
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 1/15/2011, số năm nhiều hơn 1 năm từ định dạng ban đầu .
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức xuống các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có số năm tăng dần.
=DATE(YEAR(date)+1,1,1)
– Ví dụ 8: Tạo hàng loạt ngày và năm giống nhau, chỉ khác tháng Công thức chung:
=DATE(YEAR(date),MONTH(date)+1,DAY(date))
Giải thích:
Nếu muốn tạo một loạt ngày có công thức tăng dần các tháng, nhưng ngày và năm giống nhau bạn có thể sử dụng các hàm DAY, MONTH, YEAR và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, định dạng ngày trong ô B6 là ngày bắt đầu và công thức sử dụng trong ô B7 là:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị tháng. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và năm, và và cộng thêm 1 vào giá trị tháng.
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
=DATE(2010,1+1,15)
=DATE(2010,2,15)
=2/15/2010
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 2/15/2010, số tháng nhiều hơn 1 lần số tháng từ định dạng ban đầu.
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức ở các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có giá trị tháng tăng dần.
Lưu ý: nếu định dạng ngày bắt đầu từ 31/1, công thức trên sẽ bỏ qua tháng 2 và chuyển tiếp sang tháng 3. Lý do là vì không có ngày 2/31/2010, do đó Excel sử dụng giá trị ngày để chuyển sang ngày 3/3/2010.
= EOMONTH (B6,1)
– Ví dụ 9: Tính tổng theo năm Công thức chung:
=SUMIFS(sum_range,date,”gt;=”amp;DATE(year),date,”lt;gt;
Giải thích:
Nếu muốn tính tổng theo năm, bạn có thể sử dụng hàm SUMIFS với 2 tiêu chí.
Trong ví dụ trên, công thức trong ô C6 là:
=SUMIFS(amount,date,”gt;=”amp;DATE(G6,1,1),date,”lt;gt;
Kết quả là tổng số tiền trong năm 2011. Bạn có thể sao chép công thức để tính tổng số tiền cho năm 2012 và 2013.
Ý nghĩa công thức:
Đối số đầu tiên của SUMIFs là phạm vi tổng (“sum_range”), và các tiêu chí được cung cấp dưới dạng một hoặc nhiều cặp vùng tiêu chuẩn.
Trong ví dụ này, phạm vi tổng là một vùng có tên gọi “amount” (E3:E2931), và các tiêu chí được cung cấp theo hai cặp, cả hai đều sử dụng một vùng có tên gọi là “date” (B3: B2931).
Trong mỗi trường hợp, hàm DATE được sử dụng trong các tiêu chí để xây dựng 2 định dạng ngày hợp lệ, cả hai đều sử dụng cùng một năm:
1. Ngày đầu tiên của năm 2011
2. Ngày cuối cùng của năm 2011
date, “gt;=”amp;DATE(G6,1,1)
date, “lt;=”amp;date(g6,12,31) date=”” is=””gt;lt;=gt;
Kết quả công thức trả về là tổng số tiền tất cả các khoản chỉ trong năm 2011.
Vì đang sử dụng một tham chiếu ô để cung cấp kết quả cho một năm, bạn cùng có thể sao chép công thức để tính tổng giá trị cho các năm 2012 và 2013.
– Ví dụ 10: Tính tổng các ngày khác nhau Công thức chung:
=SUMIF(range,”gt;”amp;DATE(year,month,day),sum_range)
Giải thích:
Để tính tổng giá trị các ngày khác nhau, bạn có thể sử dụng hàm SUMIF.
Trong ví dị trên, công thức trong ô H5 là:
=SUMIF(date,”gt;”amp;DATE(2023,10,1),amount)
Công thức này tính tổng số tiền trong cột D khi ngày trong cột C lớn hơn ngày 1/10/2023.
Ý nghĩa công thức:
Hàm SUMIF hỗ trợ toán tử logic của Excel (tức là “=”, “gt;”, “gt; =”, …), do đó bạn có thể sử dụng hàm theo ý muốn của mình.
Trong ví dụ này để khớp các ngày lớn hơn ngày 1/10/2023, bạn sử dụng toán tử lớn hơn (gt;) với hàm DATE để tạo ngày:
“gt;”amp;DATE(2023,10,1)
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (amp;).
Ngày là tham chiếu ô
Nếu bạn muốn hiển thị ngày trên bảng tính để có thể dễ dàng thay đổi, bạn sử dụng công thức này:
=SUMIF(date,”gt;”amp;A1,amount)
Trong đó A1 là tham chiếu đến ô có chứa ngày hợp lệ.
Hàm SUMIFS thay thế:
Ngoài ra bạn cũng có thể sử dụng hàm SUMIFS. Hàm SUMIFS có thể xử lý nhiều tiêu chí, và thứ tự của các đối số khác với SUMIF. Công thức hàm SUMIFS tương đương là:
=SUMIFS(amount, date,”gt;”amp;DATE(2023,10,1))
Lưu ý rằng phạm vi tổng hợp luôn đứng đầu tiên trong hàm SUMIFS.
– Ví dụ 11: Chuyển đổi văn bản thành ngày Công thức chung:
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Giải thích:
Để chuyển đổi văn bản ở định dạng ngày không phù hợp thành định dạng ngày phù hợp trên Excel, bạn có thể phân tích cú pháp văn bản và ghép thành một ngày phù hợp với công thức dựa trên một số hàm như: DATE, LEFT, MID và hàm RIGHT.
Lưu ý: Trước khi bạn sử dụng công thức, tham khảo một số cách khác bên dưới để kiểm tra Excel phát hiện ra các văn bản và ngày không có công thức.
Trong ví dụ được trên, công thức trong ô C6 là:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Công thức này trích các giá trị năm, tháng và ngày tách biệt, và sử dụng hàm DATE để tập hợp cvào ngày 24 /10/2000.
Excel sẽ không nhận ra các giá trị văn bản này là định đạng ngày, do đó, để tạo một ngày hợp lệ, bạn cần phải phân tích cú pháp văn bản thành các thành phần (năm, tháng, ngày) và sử dụng các thành phần này để tạo một ngày bằng hàm DATE.
Ý nghĩa công thức:
Hàm DATE lấy 3 đối số: năm, tháng và ngày. Hàm LEFT trích 4 ký tự còn lại và cung cấp làm đối số năm cho hàm DATE. Hàm MID trích 5-6 ký tự và lấy các ký tự này là đối số tháng, ngày cho hàm DATE, và hàm RIGHT trích 2 ký tự bên phải và cung cấp đối số ngày cho hàm DATE. Kết quả cuối cùng là một định dạng ngày hợp lệ trên Excel, có thể được định dạng bằng bất kỳ định dạng nào mà bạn muốn.
Ví dụ khác:
Trong hàng số 8, định dạng ngày là chúng tôi (ngày. tháng. năm) không được Excel công nhận, và công thức trong C8 là:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Thêm số 0 để chuyển đổi định dạng văn bản thành định dạng ngày:
Trong một số trường hợp bạn sẽ nhận thấy rằng có định dạng văn bản mà Excel sẽ nhận ra. Trong trường hợp nàu bạn có thể buộc Excel chuyển đổi các giá trị văn bản thành định dạng ngày tháng bằng cách thêm số 0 vào giá trị. Khi bạn thêm số 0, Excel sẽ cố gắng buộc các giá trị văn bản thành các con số.
Để chuyển đổi định dạng văn bản thành định dạng ngày bằng cách thêm số 0, bạn sử dụng Paste Special:
Bước 1: Thêm số 0 vào ô không sử dụng và sao chép vào clipboard.
Bước 2: Chọn ngày bị lỗi.
Bước 3: Chọn Paste Special =gt; Values =gt; Add .
Để chuyển đổi các ngày bằng cách thêm số 0 bằng cách sử dụng công thức:
=A1+0
Trong đó A1 có chứa định dạng ngày không hợp lệ.
Chuyển đổi Text thành Column:
Một cách khác để Excel nhận biết định dạng ngày tháng là sử dụng text thành các tính năng column:
Chọn cột ngày tháng, sau đó thử Data =gt; Text to columns =gt; Fixed =gt; Finish.
Đôi khi bạn có thể sử dụng giải pháp này để sửa mọi thứ cùng một lúc.
– Ví dụ 12: Xác nhận giá trị hợp lệ giữa các ngày Công thức chung:
=AND(A1gt;=date1),A1lt;gt;
Giải thích:
Lưu ý: Excel được tích hợp một số quy tắc xác nhận dữ liệu hợp lệ cho định dạng ngày tháng.
Để cho phép người dùng chỉ nhập đối số ngày giữa hai ngày, bạn có thể xác nhận dữ liệu bằng một công thức tùy chỉnh dựa trên hàm AND.
Trong ví dụ trên, việc xác nhận dữ liệu được áp dụng cho C5: C9 là:
=AND(C5gt;=DATE(2023,6,1),C5lt;gt;
Ý nghĩa công thức:
Các quy tắc xác nhận dữ liệu được kích hoạt khi người dùng thêm hoặc thay đổi giá trị trong ô.
Hàm AND lấy nhiều đối số (logic) và trả về kết quả TRUE chỉ khi tất cả các đối số trở về TRUE. Hàm DATE tạo ra một ngày hợp lệ trên Excel với các giá trị năm, tháng, và năm.
Nếu chỉ muốn cho phép định dạng ngày tháng trong tháng 6 năm 2023, bạn sử dụng hàm AND với 2 đối số.
Đối số đầu tiên kiểm tra đầu vào C5 lớn hơn hoặc bằng ngày 1/6/2023:
C5gt;=DATE(2023,6,1)
Đối số thứ 2 kiểm tra tính hợp lý đầu vào C5 nhỏ hơn hoặc bằng ngày 30/6/2023:
Cập nhật thông tin chi tiết về Hàm If And, If Or, Left, Date Lồng Nhau Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!