Một vài chức năng hữu ích trong Excel là gì?
Dưới đây là tổng quan ngắn gọn về khoảng 100 hàm Excel quan trọng mà bạn nên biết, có liên kết đến các ví dụ chi tiết. Chúng tôi cũng có một danh sách lớn các công thức ví dụ, một danh sách đầy đủ hơn về các hàm Excel và video đào tạo. Nếu bạn chưa quen với các công thức Excel, hãy xem phần giới thiệu này Show
Ghi chú. Excel hiện bao gồm các công thức Mảng động, cung cấp các chức năng mới quan trọng Tải xuống. 101 Hàm Excel PDF Hàm ngày và giờExcel cung cấp nhiều hàm để làm việc với ngày và giờ. BÂY GIỜ và HÔM NAYBạn có thể lấy ngày hiện tại bằng hàm TODAY và ngày giờ hiện tại bằng hàm NOW. Về mặt kỹ thuật, hàm NOW trả về ngày và giờ hiện tại, nhưng bạn chỉ có thể định dạng dưới dạng thời gian, như minh họa bên dưới
Ghi chú. đây là các hàm dễ bay hơi và sẽ tính toán lại với mỗi thay đổi trang tính. Nếu bạn muốn một giá trị tĩnh, hãy sử dụng phím tắt ngày và giờ NGÀY, THÁNG, NĂM, NGÀYBạn có thể sử dụng các hàm NGÀY, THÁNG và NĂM để phân tách bất kỳ ngày nào thành các thành phần thô của nó và hàm DATE để đặt mọi thứ lại với nhau
GIỜ, PHÚT, GIÂY và THỜI GIANExcel cung cấp một tập hợp các hàm song song cho thời gian. Bạn có thể sử dụng các hàm GIỜ, PHÚT và GIÂY để trích xuất các phần thời gian và bạn có thể tập hợp THỜI GIAN từ các thành phần riêng lẻ bằng hàm THỜI GIAN
NGÀY và NĂMFRACBạn có thể sử dụng hàm DATEDIF để tính thời gian giữa các ngày theo năm, tháng hoặc ngày. DATEDIF cũng có thể được định cấu hình để nhận tổng thời gian theo mệnh giá "chuẩn hóa", tôi. e. "2 năm 6 tháng 27 ngày" Sử dụng YEARFRAC để lấy năm phân số
EDATE và EOMONTHMột tác vụ phổ biến với ngày tháng là dịch chuyển một ngày về phía trước (hoặc lùi lại) theo một số tháng nhất định. Bạn có thể sử dụng các hàm EDATE và EOMONTH cho việc này. EDATE di chuyển theo tháng và giữ lại ngày. EOMONTH hoạt động theo cách tương tự, nhưng luôn trả về ngày cuối cùng của tháng
WORKDAY và NETWORKDAYSĐể tìm ra một ngày n ngày làm việc trong tương lai, bạn có thể sử dụng hàm WORKDAY. Để tính số ngày làm việc giữa hai ngày, bạn có thể sử dụng NETWORKDAYS
Video. Cách tính ngày đến hạn với WORKDAY
Ghi chú. Cả hai chức năng sẽ tự động bỏ qua các ngày cuối tuần (thứ Bảy và Chủ Nhật) và cũng sẽ bỏ qua các ngày lễ, nếu được cung cấp. Nếu bạn cần linh hoạt hơn về những ngày được coi là cuối tuần, hãy xem NGÀY LÀM VIỆC. Hàm INTL và NETWORKDAYS. hàm INTL. WEEKDAY và WEEKNUMĐể tìm ra ngày trong tuần từ một ngày, Excel cung cấp hàm WEEKDAY. WEEKDAY trả về một số từ 1-7 cho biết Chủ Nhật, Thứ Hai, Thứ Ba, v.v. Sử dụng hàm WEEKNUM để lấy số tuần trong một năm nhất định
Kỹ thuậtĐỔIHầu hết các chức năng Kỹ thuật đều khá kỹ thuật. bạn sẽ tìm thấy rất nhiều hàm cho số phức trong phần này. Tuy nhiên, chức năng CONVERT khá hữu ích cho việc chuyển đổi đơn vị hàng ngày. Bạn có thể sử dụng CONVERT để thay đổi đơn vị cho khoảng cách, trọng lượng, nhiệt độ, v.v. Chức năng thông tinISBLANK, ISERROR, ISNUMBER và ISFORMULAExcel cung cấp nhiều hàm để kiểm tra giá trị trong một ô, bao gồm ISNUMBER, ISTEXT, ISLOGICAL, ISBLANK, ISERROR và ISFORMULA Các hàm này đôi khi được gọi là hàm "IS" và tất cả đều trả về TRUE hoặc FALSE dựa trên nội dung của ô Excel cũng có các hàm ISODD và ISEVEN sẽ kiểm tra một số để xem số đó là chẵn hay lẻ Nhân tiện, màu xanh lục trong ảnh chụp màn hình ở trên được áp dụng tự động với công thức định dạng có điều kiện Hàm logicCác hàm logic Excel là khối xây dựng chính của nhiều công thức nâng cao. Hàm logic trả về giá trị boolean TRUE hoặc FALSE. Nếu bạn cần kiến thức cơ bản về các công thức logic, video này sẽ đưa ra nhiều ví dụ VÀ, HOẶC và KHÔNGCốt lõi của các hàm logic của Excel là hàm AND, hàm OR và hàm NOT. Trong màn hình bên dưới, mỗi chức năng này được sử dụng để chạy thử nghiệm đơn giản trên các giá trị trong cột B
IFERROR và IFNAHàm IFERROR và hàm IFNA có thể được sử dụng như một cách đơn giản để bẫy và xử lý lỗi. Trong màn hình bên dưới, VLOOKUP được sử dụng để truy xuất chi phí từ một mục menu. Cột F chỉ chứa hàm VLOOKUP, không xử lý lỗi. Cột G hiển thị cách sử dụng IFNA với VLOOKUP để hiển thị thông báo tùy chỉnh khi nhập một mục không nhận dạng được 0Trong khi IFNA chỉ bắt lỗi #N/A, thì hàm IFERROR sẽ bắt mọi lỗi công thức Hàm IF và IFSHàm IF là một trong những hàm được sử dụng nhiều nhất trong Excel. Ở màn hình bên dưới, IF kiểm tra điểm thi và cho điểm "đạt" hoặc "không đạt" Nhiều hàm IF có thể được lồng vào nhau để thực hiện các bài kiểm tra logic phức tạp hơn Tính năng mới trong Excel 2019 và Excel 365, hàm IFS có thể chạy nhiều kiểm tra logic mà không cần các IF lồng nhau 1Hàm tra cứu và tham chiếuVLOOKUP và HLOOKUPExcel cung cấp một số hàm để tra cứu và lấy dữ liệu. Nổi tiếng nhất là VLOOKUP 2Hơn. 23 điều cần biết về VLOOKUP HLOOKUP hoạt động giống như VLOOKUP, nhưng yêu cầu dữ liệu được sắp xếp theo chiều ngang 3CHỈ SỐ và MATCHĐối với các tra cứu phức tạp hơn, INDEX và MATCH mang đến sự linh hoạt và mạnh mẽ hơn 4Cả hàm INDEX và hàm MATCH đều là những hàm mạnh xuất hiện trong tất cả các loại công thức Hơn. Cách sử dụng INDEX và MATCH TRA CỨUHàm LOOKUP có các hành vi mặc định giúp nó trở nên hữu ích khi giải một số bài toán nhất định. LOOKUP giả định rằng các giá trị được sắp xếp theo thứ tự tăng dần và luôn thực hiện khớp gần đúng. Khi LOOKUP không thể tìm thấy kết quả khớp, nó sẽ khớp với giá trị nhỏ nhất tiếp theo. Trong ví dụ dưới đây, chúng tôi đang sử dụng LOOKUP để tìm mục nhập cuối cùng trong một cột Hàng và cộtBạn có thể sử dụng hàm ROW và hàm COLUMN để tìm số hàng và cột trên trang tính. Lưu ý cả ROW và COLUMN trả về giá trị cho ô hiện tại nếu không có tham chiếu nào được cung cấp Hàm hàng cũng thường xuất hiện trong các công thức nâng cao xử lý dữ liệu với số hàng tương đối Hàng và cộtHàm ROWS và hàm COLUMNS cung cấp số lượng hàng trong tham chiếu. Trong màn hình bên dưới, chúng tôi đang đếm các hàng và cột trong Bảng Excel có tên "Table1" Lưu ý ROWS trả về số lượng hàng dữ liệu trong bảng, không bao gồm hàng tiêu đề. Nhân đây, đây là 23 điều cần biết về Bảng Excel SIÊU LIÊN KẾTBạn có thể sử dụng hàm HYPERLINK để tạo liên kết bằng công thức. Lưu ý HYPERLINK cho phép bạn tạo cả liên kết bên ngoài và liên kết nội bộ 5NHẬN DỮ LIỆUHàm GETPIVOTDATA rất hữu ích để truy xuất thông tin từ các bảng tổng hợp hiện có 6CHỌNChức năng CHỌN rất hữu ích bất cứ lúc nào bạn cần đưa ra lựa chọn dựa trên một số 7Video. Cách sử dụng chức năng CHỌN CHUYỂN ĐỔIHàm TRANSPOSE cung cấp cho bạn một cách dễ dàng để chuyển dữ liệu dọc sang ngang và ngược lại 8Ghi chú. TRANSPOSE là một công thức và do đó, động. Nếu bạn chỉ cần thực hiện thao tác chuyển đổi một lần, hãy sử dụng Dán đặc biệt để thay thế BÙ LẠIHàm OFFSET hữu ích cho tất cả các loại dải động. Từ vị trí bắt đầu, nó cho phép bạn chỉ định độ lệch của hàng và cột cũng như kích thước cột và hàng cuối cùng. Kết quả là một phạm vi có thể đáp ứng linh hoạt với các điều kiện và đầu vào thay đổi. Bạn có thể cung cấp phạm vi này cho các hàm khác, như trong màn hình bên dưới, nơi OFFSET tạo một phạm vi được cung cấp cho hàm SUM 9GIÁN TIẾPHàm INDIRECT cho phép bạn tạo tham chiếu dưới dạng văn bản. Khái niệm này ban đầu hơi khó hiểu, nhưng nó có thể hữu ích trong nhiều tình huống. Dưới đây, chúng tôi đang sử dụng GIÁN TIẾP để lấy các giá trị từ ô A1 trong 5 trang tính khác nhau. Mỗi tham chiếu là động. Nếu tên trang tính thay đổi, tham chiếu sẽ cập nhật 0Hàm INDIRECT cũng được sử dụng để "khóa" các tham chiếu để chúng không thay đổi khi các hàng hoặc cột được thêm hoặc xóa. Để biết thêm chi tiết, hãy xem các ví dụ được liên kết ở cuối trang hàm INDIRECT thận trọng. cả OFFSET và INDIRECT đều là các hàm không ổn định và có thể làm chậm các bảng tính lớn hoặc phức tạp Hàm THỐNG KÊĐẾM và ĐẾMBạn có thể đếm các số bằng hàm COUNT và các ô không trống bằng COUNTA. Bạn có thể đếm các ô trống bằng COUNTBLANK, nhưng trong màn hình bên dưới, chúng tôi đang đếm các ô trống bằng COUNTIF, cách này thường hữu ích hơn 1COUNTIF và COUNTIFSĐối với số đếm có điều kiện, hàm COUNTIF có thể áp dụng một tiêu chí. Hàm COUNTIFS có thể áp dụng nhiều tiêu chí cùng một lúc 2Video. Cách sử dụng hàm COUNTIF TỔNG, TỔNG, TỔNG, TỔNGĐể tính tổng mọi thứ, hãy sử dụng hàm SUM. Để tính tổng có điều kiện, hãy sử dụng SUMIF hoặc SUMIFS. Theo cùng một mẫu với các hàm đếm, hàm SUMIF chỉ có thể áp dụng một tiêu chí trong khi hàm SUMIFS có thể áp dụng nhiều tiêu chí 3Video. Cách sử dụng hàm SUMIF TRUNG BÌNH, AVERAGEIF và AVERAGEIFSTheo cùng một mẫu, bạn có thể tính trung bình bằng AVERAGE, AVERAGEIF và AVERAGEIFS 4TỐI THIỂU, TỐI ĐA, LỚN, NHỎBạn có thể tìm các giá trị lớn nhất và nhỏ nhất với MAX và MIN, cũng như các giá trị lớn nhất và nhỏ nhất thứ n với LARGE và SMALL. Trong màn hình bên dưới, dữ liệu là phạm vi được đặt tên C5. C13, được sử dụng trong tất cả các công thức 5Video. Cách tìm giá trị nhỏ nhất hoặc lớn nhất thứ n MINIFS, MAXIFSMINIFS và MAXIFS. Các hàm này cho phép bạn tìm các giá trị tối thiểu và tối đa với các điều kiện 6Ghi chú. MINIFS và MAXIFS mới trong Excel thông qua Office 365 và Excel 2019 CÁCH THỨCHàm MODE trả về số xuất hiện phổ biến nhất trong một dải ô 7THỨ HẠNGĐể xếp hạng các giá trị lớn nhất đến nhỏ nhất hoặc nhỏ nhất đến lớn nhất, hãy sử dụng hàm RANK Video. Cách xếp hạng các giá trị bằng hàm RANK Hàm MATHABSĐể thay đổi giá trị âm thành dương, hãy sử dụng chức năng ABS 8RAND và RANDBETWEENCả hàm RAND và hàm RANDBETWEEN đều có thể tạo số ngẫu nhiên một cách nhanh chóng. RAND tạo các số thập phân dài từ 0 đến 1. RANDBETWEEN tạo ra các số nguyên ngẫu nhiên giữa hai số đã cho 9LÀM TRÒN, LÀM TRÒN, LÀM TRÒN, LÀM TRÒN, INTĐể làm tròn các giá trị lên hoặc xuống, hãy sử dụng hàm ROUND. Để buộc làm tròn đến một số chữ số nhất định, hãy sử dụng ROUNDUP. Để buộc làm tròn xuống, hãy sử dụng ROUNDDOWN. Để loại bỏ hoàn toàn phần thập phân của một số, hãy sử dụng hàm INT 0MROUND, TRẦN, SÀNĐể làm tròn các giá trị về bội số gần nhất, hãy sử dụng hàm MROUND. Hàm FLOOR và hàm CEILING cũng làm tròn thành bội số đã cho. FLOOR lực làm tròn xuống, và CEILING lực làm tròn lên 1chế độHàm MOD trả về số dư sau phép chia. Điều này nghe có vẻ nhàm chán và lập dị, nhưng MOD xuất hiện trong tất cả các loại công thức, đặc biệt là những công thức cần phải làm điều gì đó "lần thứ n". Trong màn hình bên dưới, bạn có thể thấy cách MOD trả về 0 cho mỗi số thứ ba khi số chia là 3 TÓM TẮTHàm TÓM TẮT là một công cụ mạnh mẽ và linh hoạt khi xử lý tất cả các loại dữ liệu. Bạn có thể sử dụng TÓM TẮT để dễ dàng đếm và tính tổng dựa trên các tiêu chí và bạn có thể sử dụng nó theo những cách tinh tế mà không hoạt động với COUNTIFS và SUMIFS. Trong màn hình bên dưới, chúng tôi đang sử dụng TÓM TẮT để đếm và tính tổng các đơn đặt hàng trong tháng 3. Xem trang TÓM TẮT để biết chi tiết và liên kết đến nhiều ví dụ 2TỔNG PHỤHàm SUBTOTAL là một "hàm tổng hợp" có thể thực hiện một số thao tác trên một tập hợp dữ liệu. Tóm lại, SUBTOTAL có thể thực hiện 11 thao tác, bao gồm SUM, AVERAGE, COUNT, MAX, MIN, v.v. (xem trang này để biết danh sách đầy đủ). Tính năng chính của SUBTOTAL là nó sẽ bỏ qua các hàng đã được "lọc ra" khỏi Bảng Excel và, tùy chọn, các hàng đã bị ẩn theo cách thủ công. Trong màn hình bên dưới, SUBTOTAL được sử dụng để chỉ đếm và tính tổng 7 hàng hiển thị trong bảng 3TỔNG HỢPGiống như SUBTOTAL, hàm TỔNG HỢP cũng có thể chạy một số thao tác tổng hợp trên một tập hợp dữ liệu và có thể tùy ý bỏ qua các hàng bị ẩn. Sự khác biệt chính là AGGREGATE có thể chạy nhiều hoạt động hơn (tổng cộng 19) và cũng có thể bỏ qua các lỗi Trong màn hình bên dưới, TỔNG HỢP được sử dụng để thực hiện các thao tác TỐI THIỂU, TỐI ĐA, LỚN và NHỎ trong khi bỏ qua các lỗi. Thông thường, lỗi trong ô B9 sẽ ngăn các hàm này trả về kết quả. Xem trang này để biết danh sách đầy đủ các hoạt động mà TỔNG HỢP có thể thực hiện 4Chức năng VĂN BẢNTRÁI, PHẢI, GIỮAĐể trích xuất các ký tự từ bên trái, bên phải hoặc ở giữa văn bản, hãy sử dụng các hàm LEFT, RIGHT và MID 5LENHàm LEN sẽ trả về độ dài của chuỗi văn bản. LEN xuất hiện trong rất nhiều công thức đếm từ hoặc ký tự TÌM, TÌM KIẾMĐể tìm văn bản cụ thể trong một ô, hãy sử dụng hàm TÌM hoặc hàm TÌM KIẾM. Các hàm này trả về vị trí số của văn bản phù hợp, nhưng TÌM KIẾM cho phép ký tự đại diện và TÌM phân biệt chữ hoa chữ thường. Cả hai hàm sẽ đưa ra lỗi khi không tìm thấy văn bản, vì vậy hãy thêm hàm ISNUMBER để trả về TRUE hoặc FALSE (ví dụ tại đây) 6THAY THẾ, THAY THẾĐể thay thế văn bản theo vị trí, hãy sử dụng chức năng REPLACE. Để thay thế văn bản bằng cách so khớp, hãy sử dụng hàm SUBSTITUTE. Trong ví dụ đầu tiên, REPLACE loại bỏ hai dấu hoa thị (**) bằng cách thay thế hai ký tự đầu tiên bằng một chuỗi rỗng (""). Trong ví dụ thứ hai, SUBSTITUTE loại bỏ tất cả các ký tự băm (#) bằng cách thay thế "#" bằng "" 7MÃ, CHARĐể tìm ra mã số cho một ký tự, hãy sử dụng hàm CODE. Để dịch mã số trở lại một ký tự, hãy sử dụng hàm CHAR. Trong ví dụ bên dưới, CODE dịch từng ký tự trong cột B sang mã tương ứng. Trong cột F, CHAR dịch mã trở lại ký tự 8Video. Cách sử dụng hàm CODE và CHAR CẮT, SẠCHĐể loại bỏ khoảng trống thừa trong văn bản, hãy sử dụng hàm TRIM. Để xóa ngắt dòng và các ký tự không in được khác, hãy sử dụng CLEAN 9Video. Cách làm sạch văn bản với TRIM và CLEAN CONCAT, TEXTJOIN, CONCATENATETính năng mới trong Excel qua Office 365 là CONCAT và TEXTJOIN. Hàm CONCAT cho phép bạn nối (nối) nhiều giá trị, bao gồm một dải giá trị không có dấu phân cách. Hàm TEXTJOIN thực hiện tương tự, nhưng cho phép bạn chỉ định dấu phân cách và cũng có thể bỏ qua các giá trị trống 0Excel cũng cung cấp hàm CONCATENATE nhưng không cung cấp các tính năng đặc biệt. Tôi sẽ không bận tâm đến nó và thay vào đó sẽ nối trực tiếp với ký tự dấu và (&) trong một công thức CHÍNH XÁCHàm EXACT cho phép bạn so sánh hai chuỗi văn bản theo cách phân biệt chữ hoa chữ thường UPPER, LOWER, PROPERĐể thay đổi kiểu chữ, hãy sử dụng hàm UPPER, LOWER và PROPER 1Video. Cách thay đổi trường hợp với công thức CHỮCuối cùng nhưng không kém phần quan trọng là chức năng TEXT. Hàm văn bản cho phép bạn áp dụng định dạng số cho các số (bao gồm ngày, giờ, v.v. ) dưới dạng văn bản. Điều này đặc biệt hữu ích khi bạn cần nhúng một số đã định dạng vào một tin nhắn, chẳng hạn như "Giảm giá kết thúc vào [ngày]" 2Hơn. Ví dụ chi tiết về định dạng số tùy chỉnh Các hàm Mảng độngMảng động là mới trong Excel 365 và là một bản nâng cấp lớn cho công cụ công thức của Excel. Là một phần của bản cập nhật mảng động, Excel bao gồm các hàm mới tận dụng trực tiếp mảng động để giải các bài toán mà theo truyền thống khó giải bằng các công thức thông thường. Nếu bạn đang sử dụng Excel 365, hãy đảm bảo rằng bạn biết các chức năng mới này Chức năng.Video. Các hàm mảng động mới trong Excel (khoảng 3 phút) điều hướng nhanhABS, TỔNG HỢP, VÀ, TRUNG BÌNH, AVERAGEIF, AVERAGEIFS, TRẦN, CHAR, CHỌN, SẠCH, MÃ, CỘT, CỘT, CONCAT, CONCATENATE, CONVERT, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, NGÀY, DATEDIF, NGÀY, EDATE, Một số chức năng hữu ích nhất trong Excel là gì?11 hàm được sử dụng nhiều nhất trong Excel . 1 – TỔNG () TỔNG. Đây có lẽ là công thức cơ bản và thường được sử dụng nhất của Excel. . 2 – TRUNG BÌNH () TRUNG BÌNH. Như tên gợi ý, công thức này trả về giá trị trung bình của một dải ô. . 3 – ĐẾM(). 4 – TỐI ĐA(). 5 PHÚT(). 6 – NẾU(). 7 – VLOOKUP(). 8 – CHỈ SỐ() & MATCH() 5 hàm chính được sử dụng trong Excel là gì?Các hàm được sử dụng thường xuyên nhất trong Excel là. . Tự động tính tổng; hàm NẾU; hàm TÌM KIẾM; Hàm VLOOKUP; hàm HLOOKUP; hàm MATCH; CHỌN chức năng; hàm NGÀY; 10 hàm trong Excel là gì?Đọc phần bên dưới để biết tổng quan đầy đủ về từng chức năng của MS Excel. . Định dạng bảng. Những gì nó làm. chuyển đổi dữ liệu của bạn thành một cơ sở dữ liệu tương tác. . bảng tổng hợp. Những gì nó làm. tóm tắt dữ liệu và tìm các giá trị duy nhất. . biểu đồ. . ĐẾM. . SUMIFS. . Câu lệnh IF. . NỐI. . VLOOKUP 7 công thức Excel cơ bản là gì?Tổng quan về công thức trong Excel. Mạo từ Xlookup. Mạo từ VLOOKUP. Bài báo hàm tổng. Bài báo hàm COUNTIF. Mạo từ hàm NẾU. Mạo từ NẾU. Bài báo SUMIF. Bài báo |