Cách tính hiểu trong Excel
Trong bài viết này, Học Excel Online sẽ hướng dẫn những điều cơ bản về tạo và sử dụng các công thức Excel. Và bởi vì một trong những cách hiệu quả nhất để học là thông qua thực hành, chúng tôi cũng sẽ thảo luận về một số công thức ví dụ để làm cho mọi việc trở nên dễ hiểu hơn. Công thức Excel những điều cơ bảnTrong MS Excel, công thức excel là các phương trình thực hiện các tính toán khác nhau trong các bảng tính. Mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, khái niệm công thức bảng tính Excel vẫn giống nhau trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn. Show
Công thức Microsoft Excel những điều cơ bản Các thành phần của công thức Microsoft ExcelKhi bạn tạo một công thức trong Excel, bạn có thể sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc tất cả các phần sau: ·
Các thành phần của công thức Microsoft Excel Các toán tử trong các bảng tính ExcelĐể nói với Microsoft Excel loại công việc bạn muốn thực hiện trong một công thức, bạn sử dụng các ký hiệu đặc biệt được gọi là các toán tử. Có 4 loại toán tử trong Excel:
Sử dụng toán tử số học trong công thức ExcelCác toán tử này được sử dụng để thực hiện các phép toán cơ bản như cộng, trừ, nhân và chia. Toán tửÝ nghĩCông thức ví dụ + Phép cộng =A2+B2 Phép trừ Ví dụ: nếu bạn có giá cả trong ô A2 và VAT trong ô B2, bạn có thể tính số tiền thuế GTGT bằng cách sử dụng công thức tỷ lệ phần trăm sau: = A2 * B2 Sử dụng toán tử số học trong công thức Excel Các toán tử so sánh trong các công thức ExcelTrong các công thức Microsoft Excel, so sánh, hoặc hợp lý, toán tử được sử dụng để so sánh hai giá trị. Kết quả của việc so sánh luôn là một giá trị logic của TRUE hoặc FALSE. Các toán tử logic sau có sẵn trong Excel: Toán tử so sánhÝ nghĩaCông thức ví dụ = Bằng =A2=B2 <> Không bằng =A2<>B2 > Lớn hơn =A2>B2 < Nhỏ hơn =A2 Ví dụ, công thức = A1 = B1 trả về TRUE nếu các ô A1 và B1 có cùng một giá trị (số, văn bản hoặc ngày tháng), FALSE nếu không. Toán tử nối văn bảnToán tử nối văn bản trong Excel là ký hiệu và (&). Bạn có thể sử dụng nó để kết hợp hai hoặc nhiều chuỗi văn bản trong một chuỗi duy nhất. Ví dụ: nếu bạn có mã quốc gia trong cột A và số điện thoại trong cột B, bạn có thể sử dụng công thức sau để nhận số điện thoại kết hợp với mã quốc gia: = A1 & & B1 Trong công thức trên, chúng ta thêm một khoảng trống vào giữa để làm cho các con số dễ đọc hơn: Toán tử nối văn bản Kết quả tương tự có thể đạt được bằng cách sử dụng chức năng CONCATENATE. Các toán tử tham chiếu trong các công thức Excel và các hàmĐể cung cấp vùng dữ liệu cho MS Excel công thức và đối số riêng biệt trong hàm Excel, các toán tử sau đây được sử dụng. Dấu hai chấm (:) nó là một toán tử phạm vi cho phép bạn tạo một tham chiếu cho nhiều ô nằm giữa 2 ô bạn chỉ định. Ví dụ, phạm vi A1: A00 bao gồm 100 ô từ A1 đến A100. Để tìm trung bình của 100 ô này, bạn sử dụng công thức sau: = AVERAGE (A1: A00) Bạn cũng có thể tham khảo toàn bộ cột (A: A) hoặc toàn bộ hàng (1: 1). Ví dụ: công thức sau tìm tổng số tất cả các số trong cột A: = SUM (A: A). Dấu phẩy (,) được sử dụng để phân biệt đối số trong các công thức bảng tính Excel. Ví dụ, công thức = IF (A1> 0, tốt, xấu) đọc như sau: nếu A1 lớn hơn không, trả lại tốt, nếu không xấu. Chú thích. Dấu phẩy là dấu phân cách mặc định ở Bắc Mỹ và một số nước khác. Tại các quốc gia Châu Âu, dấu phẩy được giữ như Biểu tượng thập phân và dấu phân cách thường được đặt thành dấu chấm phẩy (;). Trong trường hợp này, bạn cần phải tách biệt đối số với các dấu chấm phẩy, ví dụ: = IF (A1> 0, tốt, xấu). Vì vậy, nếu bạn đang cố gắng tạo ra một công thức trong bảng tính của bạn, nhưng Excel không chấp nhận nó và đưa ra lỗi formula invalid formula, hãy chuyển tới Regional Settings (Control Panel> Region and Language> Additional Settings) và kiểm tra ký hiệu nào được đặt là dấu phân cách ở đó. Đó là dấu mà bạn cần sử dụng để phân biệt đối số trong công thức Excel của bạn. Khoảng trắng nó là một toán tử giao lộ cho phép bạn lấy được các ô tương ứng với hai tham chiếu mà bạn chỉ định. Ví dụ: nếu bạn có một danh sách các mục trong cột A và một số dữ liệu có liên quan trong các cột khác, bạn có thể nhận được giá trị tại giao điểm của một cột và hàng cho trước bằng cách sử dụng công thức như sau: = B3: D3 C2: C4 Các toán tử tham chiếu trong các công thức Excel và các hàm Các loại công thức ExcelCông thức mà bạn tạo trong bảng tính Excel của bạn có thể đơn giản hoặc phức tạp:
Hơn nữa trong hướng dẫn này, bạn sẽ tìm thấy các bước chi tiết để tạo cả hai loại công thức bảng tính Excel. Cách tạo các công thức trong ExcelNhư đã đề cập, bất kỳ công thức Excel nào bắt đầu với dấu bằng (=). Vì vậy, bất cứ công thức nào bạn sẽ viết, bắt đầu bằng cách gõ = trong ô chỉ định hoặc trong thanh công thức Excel. Và bây giờ, chúng ta hãy cùng xem xét cách bạn có thể tạo các công thức khác nhau trong Excel. Làm thế nào để tạo các công thức đơn giản trong Excel Mặc dù các công thức Excel đơn giản chỉ thực hiện một phép tính, chúng có thể thực hiện theo nhiều cách khác nhau. Bạn có thể cung cấp dữ liệu nguồn dưới dạng hằng số, tham chiếu ô, hoặc các tên được xác định, và thực hiện tính toán bằng cách sử dụng các toán tử toán học hoặc các hàm Excel. Cách tạo các công thức tiên tiến trong ExcelKhi bạn có một số kinh nghiệm với các công thức Excel đơn giản, bạn có thể muốn thực hiện một số tính toán trong một công thức. Và các ví dụ sau cho thấy cách bạn có thể làm điều này. Tạo các công thức phức tạp với các hằng số và các toán tử toán học Đối với một công thức phức tạp để chính xác, một số thao tác nhất định phải được thực hiện trước. Lệnh mặc định hoạt động của các công thức Excel là:
Ví dụ: bạn có thể sử dụng các công thức sau để tính tổng số và hoa hồng: Cách tạo các công thức tiên tiến trong Excel Và bây giờ, hãy phân tích các công thức này để xem cách Microsoft Excel tính toán chúng như thế nào: Công thức: = $B2*$D2 + $B2 * $D2 * $C2
Công thức hoa hồng: = ($B2 * $D2 + $B2 * $D2 * $C2) * 10% Để tính khoản hoa hồng 10%, bạn cần nhân tổng cộng với 10%, do đó bạn kèm theo tính toán trước đó trong ngoặc và nhận được kết quả mong muốn. Tất nhiên, không có gì ngăn cản bạn nhân tổng số đã được tính trong cột E với 10%, trong trường hợp này công thức sẽ thành một phép tính đơn giản = E2 * 10%. Tuy nhiên, trong các bảng tính lớn, nên viết các công thức được tính toán độc lập một cách hợp lý. Công thức Excel với hàm lồng nhauTrong công thức Microsoft Excel, lồng hàm bên trong một hàm khác bằng cách sử dụng một hàm như một đối số của một hàm khác. Trong các phiên bản hiện tại của Excel 2016, 2013, 2010 và 2010, bạn có thể sử dụng tối đa 64 hàm lồng nhau. Trong các phiên bản cũ của Excel 2003 và phiên bản thấp hơn, chỉ được phép có 7 hàm. Dưới đây là một ví dụ rất đơn giản về công thức Excel lồng nhau có chứa hàm SUM để tìm tổng số, và chức năng ROUND để tròn số đó đến số nguyên gần nhất (0 chữ số thập phân): = ROUND (SUM (B2: B6), 0) Công thức Excel với hàm lồng nhau Trong tất cả các hàm Excel, hàm IF lồng thường xuyên hơn tất cả các hàm khác. Như bạn có thể biết, hàm IF được sử dụng để đánh giá một điều kiện cụ thể và trả về một giá trị khi điều kiện được đáp ứng và một giá trị khác khi điều kiện không được đáp ứng. Tuy nhiên, thực tế bạn thường phải đối phó với các tình huống có nhiều hơn hai kết quả có thể xảy ra. Và nếu trường hợp này, bạn có thể viết một số hàm IF và lồng nó vào nhau: Công thức Excel với hàm lồng nhau 01 Công thức mảng trong ExcelCác công thức mảng trong Excel là một bước cải tiến. Một công thức mảng Excel đơn có thể thực hiện hàng ngàn phép tính và thay thế hàng trăm công thức thông thường. Nghiên cứu công thức mảng chắc chắn đòi hỏi thời gian và công sức, nhưng xứng đáng. Vì hướng dẫn này dành cho người mới bắt đầu, tôi sẽ không hăm dọa bạn bởi các định nghĩa các hằng số mảng và các công thức nhiều dòng phức tạp. Tôi sẽ chỉ hiển thị một ví dụ rất đơn giản của một công thức mảng Excel thể hiện những gì nó có thể làm. Giả sử bạn có 2 cột số, cột A và B. Và bạn muốn biết có bao nhiêu lần cột B lớn hơn hoặc bằng cột A khi giá trị trong cột B lớn hơn 0. Nhiệm vụ này đòi hỏi phải so sánh hai dãy và bạn có thể làm điều này bằng cách sử dụng công thức mảng sau: = SUM ((B2: B10> = A2: A10) * (B2: B10> 0)) Công thức mảng trong Excel Chú thích. Để nhập công thức mảng Excel một cách chính xác, bạn phải nhấn Ctrl + Shift + Enter thay vì gõ Enter thông thường. Hàm người dùng tự tạoMặc dù Microsoft Excel có hàng trăm hàm được xây dựng, bạn vẫn có thể thấy mình phải đối mặt với một thách thức mà không có hàm Excel phù hợp. Trong trường hợp này, bạn có thể tạo ra hàm đó hoặc có ai đó tạo ra nó cho bạn Các hàm tùy chỉnh này được gọi là Các hàm do người dùng tạo (UDF), và chúng đặc biệt hữu ích cho các tính toán toán hoặc kỹ thuật cao cấp. Giống như macro, các hàm do người dùng tạo được viết bằng VBA (Visual Basic for Applications). Các tham chiếu ô tuyệt đối, tương đối và hỗn hợp trong các công thức Excel Có 3 loại tham chiếu ô trong Excel: tuyệt đối ($A$1), tương đối (A1) và hỗn hợp ($A1 hoặc A$1). Cả ba loại trên đề cập đến cùng một ô, và dấu đô la ($) chỉ được sử dụng cho một mục đích nó nói với Microsoft Excel rằng có thay đổi hay không thay đổi các tham chiếu ô khi công thức được di chuyển hoặc sao chép sang các ô khác. Tham chiếu ô tuyệt đối ($A$1) dấu $ trước khi các tọa độ của hàng và cột làm cho tham chiếu tĩnh và cho phép bạn sao chép công thức mà không thay đổi tài liệu tham khảo. Tham chiếu ô tương đối (A1) một tham chiếu ô không có dấu hiệu thay đổi dựa trên vị trí tương đối của hàng và cột trong một bảng tính. Tham chiếu ô hỗn hợp có thể có 2 loại:
Hình dưới đây cho thấy các loại tham chiếu khác nhau hoạt động như thế nào trong thực tế. Hàm người dùng tự tạo Mẹo và các phím tắt tiết kiệm thời gian cho các công thức ExcelCác công thức trong Excel là một công cụ đa diện mạnh mẽ và chúng có thể giải quyết rất nhiều công việc trong bảng tính của bạn. Tất nhiên, học các khía cạnh khác nhau của hàm Excel và công thức sẽ tốn thời gian, vì vậy bạn có thể cảm thấy không có đủ thời gian để tìm hiểu tất cả mọi thứ. Vâng, một cách tốt để tìm thêm thời gian là để tiết kiệm một thời gian
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia cùng giảng viên Nguyễn Đức Thanh hay DT Nguyen. · · · Bài viết liên quan Bài viết tập trung tài liệu từ kênh YouTube Học Excel Online Hàm IF nhiều điều kiện trong Excel Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu Cách tạo menu chuyển động đẹp trong Excel sử dụng VBA Cách lấy danh sách ngẫu nhiên không trùng lặp trong Excel Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại Khóa học liên quanKhóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm Chi tiết khóa học Khóa học Trọn bộ Power Query nền tảng cho công việc báo cáo dữ liệu Chi tiết khóa học Bài viết tập trung tài liệu từ kênh YouTube Học Excel Online Hàm IF nhiều điều kiện trong Excel Nếu tiết kiệm và đầu tư bây giờ, thì sau bao năm bạn có thể nghỉ hưu Cách tạo menu chuyển động đẹp trong Excel sử dụng VBA Cách lấy danh sách ngẫu nhiên không trùng lặp trong Excel Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại Đăng ký học qua Email Hộ kinh doanh Học Excel Online. Khóa học Excel Khóa học VBA Khóa học SQL Khóa học Google Apps Script Khóa học Power BI YouTube EXCELVBAPOWER BISQLGoogle Apps ScriptGoogle SheetsPower QueryPythonPower Platform © Học Excel Online. All rights reserved. Video liên quan |
