Tiếp theo: 3. Quản lý dữ liệu trong Trang tính Có nhiều cách để làm việc với tệp Microsoft Excel. Bạn có thể: Nếu nhóm của bạn sử dụng Microsoft Excel® và Trang tính, thì sau đây là một số phương pháp hay nhất khi sử dụng mỗi sản phẩm. Mở một tệp Excel trên Drive và chỉnh sửa tệp đó trong Trang tính. Mọi thay đổi bạn thực hiện đều được lưu ở định dạng Excel gốc. Chỉ Trình duyệt Chrome Bản xem trước của tệp mở ra.
2.2 Sử dụng Excel và Trang tính cùng nhau
Chuyển đổi bảng tính Excel hiện có thành tệp Trang tính nếu:
Tiếp tục làm việc trong bảng tính Excel hiện có nếu:
2.3 Chỉnh sửa tệp Excel trong Trang tính
Giờ đây, bạn có thể chỉnh sửa, chia sẻ, xem lịch sử phiên bản, cộng tác theo thời gian thực với người khác và nhiều hoạt động khác. Mọi thay đổi bạn thực hiện đều được lưu vào tệp Microsoft Office gốc.
2.4 Nhập dữ liệu Excel vào Trang tính
Bạn có thể nhập dữ liệu trong một tệp Excel vào tệp Trang tính mới [hoặc hiện có]. Tệp Excel của bạn sẽ không thay đổi, ngay cả khi bạn thay đổi tệp Trang tính.
- Trong Trang tính, hãy tạo bảng tính mới hoặc mở bảng tính hiện có.
- Nhấp vào TệpNhập.
- Chọn tệp Excel rồi nhấp vào Chọn.
- Chọn một tùy chọn nhập:
- Tạo bảng tính mới
- Chèn [các] trang tính mới
- Thay thế bảng tính
- Nhấp vào Nhập dữ liệu.
- Nếu được nhắc, hãy nhấp vào Mở ngay.
2.5 Chuyển đổi tệp Excel thành tệp Trang tính
Thay vì nhập dữ liệu, bạn có thể chuyển đổi một tệp Excel thành tệp Trang tính và tiếp tục làm việc với tệp đó trong Trang tính. Tệp Excel của bạn sẽ không thay đổi.
- Mở Drive rồi nhấp đúp vào tệp Excel.
Một bản xem trước của tệp sẽ mở ra.
- Ở trên cùng, hãy nhấp vào Mở bằng Google Trang tính.
- Nhấp vào TệpLưu dưới dạng Google Trang tính.
Cách tự động chuyển đổi tất cả các tệp Office tải lên trong tương lai sang Google Trang tính:
- Trong Drive, ở trên cùng, hãy nhấp vào biểu tượng Cài đặt Cài đặt.
- Bên cạnh mục Chuyển đổi nội dung tải lên, hãy bật tùy chọn Chuyển đổi các tệp đã tải lên sang định dạng của trình biên tập Google Tài liệu.
Lưu ý: Các tệp đã tải lên trước đó sẽ không được chuyển đổi.
Trong Drive, bạn sẽ thấy tệp Excel và tệp Trang tính mới. Bạn có thể giữ tệp Excel hoặc xóa tệp đó. Nếu giữ tệp Excel thì để tránh nhầm lẫn, bạn có thể đổi tên tệp thành [Đã lưu trữ] My doc.xls.
Mẹo: Hãy dùng các biểu tượng sau đây để phân biệt nhanh tệp Trang tính và tệp Excel trong Drive:
Trang tính Excel
2.6 Chia sẻ bản sao của một tệp Trang tính ở định dạng Excel
Bạn có thể làm việc với một bảng tính trong Trang tính, rồi gửi một bản sao qua email dưới dạng tệp Excel [hoặc PDF].
- Trong Trang tính, hãy mở tệp.
- Nhấp vào TệpGửi qua email dưới dạng tệp đính kèm.
Trong mục Đính kèm dưới dạng, hãy chọn Microsoft Excel.
- Nhập địa chỉ email, tiêu đề và thư.
- [Tùy chọn] Chọn hộp Gửi một bản sao cho chính tôi.
- Nhấp vào Gửi.
Bạn cũng có thể tải một bản sao của tệp Trang tính xuống ở định dạng Excel, PDF, CSV, ODS hoặc các định dạng khác.
- Trong Trang tính, hãy nhấp vào TệpTải xuống dưới dạng.
- Chọn định dạng tệp bạn muốn tải xuống.
Quay lại đầu trang
Google, Google Workspace, nhãn và các biểu trưng liên quan là nhãn hiệu của Google LLC. Tất cả tên công ty và sản phẩm khác là nhãn hiệu của các công ty liên kết với các nhãn hiệu đó.
Thông tin này có hữu ích không?
Chúng tôi có thể cải thiện trang này bằng cách nào?
Chủ doanh nghiệp thường coi Excel như là công cụ hàng đầu của họ, bởi đây là công cụ phục vụ hầu hết cho các công việc quan trọng của họ, Excel là công cụ bắt kịp cho nhiều thứ. Excel là công cụ rất hữu ích nhưng phải làm việc với nó thường xuyên, bạn phải xây dựng bao công thức mỗi ngày và bạn phải cần rất nhiều thời gian để xây dựng các công thức chính xác. Bài viết này mang đến cho bạn một số mẹo hay giúp bạn cải thiện năng suất và tiết kiệm thời gian với công thức Excel.
1. Những điều cơ bản về công thức trong Excel bạn cần nắm vững
Trước khi áp dụng bất kỳ công thức hoặc hàm nào vào để tính toán và thống kê số liệu bạn cần phải nắm bắt được những lưu ý cơ bản sau đây:
- Tất cả các hàm và công thức tính trong Excel đều bắt đầu bằng dấu " = " Nếu không có thì coi như công thức đó không có giá trị.
- Cú pháp nhập để tính giá trị là " = công thức hoặc phép tính " kết thúc bằng enter để hoàn thành.
- Công thức đúng sẽ trả về kết quả. Công thức sai thì kết quả xuất hiện : #N/A, #REF!, #NAME?,....
2. 20 công thức trong Excel và các hàm bạn nên nhớ
Các công thức Excel cơ bản sẽ giúp bạn xác định mối quan hệ giữa các giá trị trong các ô của bảng tính, thực hiện các phép tính trong excel bằng các giá trị đó và trả về giá trị kết quả trong ô bạn chọn. Các công thức bạn có thể tự động thực hiện bao gồm tổng, trừ, tỷ lệ phần trăm, chia, trung bình…
Trong các phiên bản Excel gần đây thì bạn có thể thể tìm và chèn các công thức tính trong excel vào ô tính cụ thể ở trong tab Formulas trên thanh menu.
Các công thức trong Excel
Sử dụng các công thức của các hàm trong Excel sẽ khiến bạn tiết kiệm rất nhiều thời gian của mình.
>> Xem thêm: Cách ẩn công thức trong bảng tính excel đơn giản nhất
2.1. Hàm Sum
Tất cả công thức trong Excel được thực hiện bằng dấu = và theo sau là các thẻ văn bản cụ thể biểu thị công thức bạn muốn Excel thực hiện.
Công thức sum trong Excel là một trong những công thức cơ bản nhất, cho phép bạn tìm tổng của 2 hoặc nhiều các giá trị khác nhau. Muốn thực hiện tính tổng của các giá trị chỉ cần nhập công thức =SUM[giá trị 1, giá trị 2,...].
Các giá trị ở đây cũng có thể là các số thực, hoặc bằng số trong một ô cụ thể của bảng tính của bạn.
Ví dụ về cách sử dụng của hàm SUM trong Excel:
Quan sát bảng dữ liệu dưới đây, yêu cầu tính tổng điểm của các môn học của từng học sinh.
- Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức hàm: =SUM[C9:F9]
Hàm Sum trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Hàm Sum trong Excel - Hình 2
2.2. Percentage
Đây là công thức tính tỷ lệ phần trăm trong Excel, bạn hãy nhập các ô tìm tỷ lệ phần trăm, để chuyển đổi giá trị thập phân thành tỷ lệ phần trăm, chọn ô và nhấp vào tab trang chủ chọn Percentage.
Percentage trong Excel
Không có công thức Excel để tính phần trăm cụ thể, nhưng chúng ta có thể quy đổi và dạng tỉ lệ phần trăm để các bạn không bị vướng mắc.
Cách sử dụng của Percentage trong Excel:
Quan sát ví dụ sau, yêu cầu tính tổng phần trăm trong tổng số của các dữ liệu trong bảng.
- Công thức áp dụng như sau: Tỷ lệ phần trăm [%] = 1 phần / tổng số
- Bước 1: Nhập công thức tính tỷ lệ phần trăm [%] = 1 phần / tổng số [Total] vào ô muốn hiển trị kết quả.
Đầu tiên, bạn nhấp vào ô C2 để tính % của Cherries trong tổng số. Tiếp theo bạn nhập dấu chia [/], sau đó click vào ô tổng số B10.
Tính phần trăm trong tổng số trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả. Lưu ý để mẫu số làm tham chiếu tuyệt đối, bạn phải nhập $ theo cách thủ công.
Cuối cùng, đặt trỏ chuột vào ô kết quả đầu tiên cho xuất hiện dấu + rồi kéo xuống hết các ô Excel tiếp theo để đảm bảo kết quả được hiển thị đầy đủ.
Tính phần trăm trong tổng số trong Excel - Hình 2
2.3. Phép trừ
Để thực hiện các phép trừ trong Excel, bạn chỉ cần nhập ô cần thực hiện phép trừ và sử dụng công thức hàm SUM hoặc là sử dụng định dạng.
Cách sử dụng của phép trừ bằng hàm Sum:
Quan sát bảng dữ liệu sau, yêu cầu dùng hàm SUM để thực hiện phép trừ với các số liệu cụ thể trong bảng tính.
- Bước 1: Trong ô A3 muốn hiển thị kết quả, bạn nhập công thức như sau: =SUM[A1;-B1;-C1;-D1;-E1]
Thực hiện phép trừ bằng hàm SUM - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Thực hiện phép trừ bằng hàm SUM - Hình 2
2.4. Phép nhân
Để thực hiện công thức nhân trong Excel, bạn chỉ cần thực hiện nhập công thức theo định dạng và công thức này dùng dấu hoa thị để biểu thị công thức nhân.
Ví dụ với phép tính:
Phép nhân trong Excel
2.5. Phép chia
Phân chia trong Excel là một trong những chức năng đơn giản nhất bạn có thể thực hiện. Để làm như vậy, hãy chọn một ô trống, nhập dấu bằng, "=," và theo dõi nó với hai [hoặc nhiều] giá trị bạn muốn chia bằng dấu gạch chéo về phía trước, "/," ở giữa.
Phép chia trong Excel
2.6. Hàm DATE
Công thức DATE trong Excel được ký hiệu = DATE [year,month,day]. Công thức này sẽ trả về một ngày tương ứng với các giá trị được nhập trong ngoặc đơn - ngay cả các giá trị được gọi từ các ô khác.
- Tạo ngày từ các giá trị ô:
Hàm Day trong Excel [1]
- Tự động thiết lập ngày hôm nay nhập công thức:
Hàm Day trong Excel [2]
- Trong cả hai cách sử dụng công thức ngày của Excel, ngày trả về của bạn phải ở dạng "mm / dd / yy" - trừ khi chương trình Excel của bạn được định dạng khác nhau.
2.7. ARRAY
Mảng số rất hữu ích để thực hiện nhiều công thức cùng một lúc trong một ô để bạn có thể thấy một tổng, hiệu, sản phẩm hoặc thương số cuối cùng.
Cách sử dụng của công thức mảng Array:
Ví dụ: Quan sát bảng dữ liệu sau, dựa vào số lượng bán và đơn giá, yêu cầu tính doanh thu cho sản phẩm.
Công thức mảng trong Excel - Hình 1
- Bước 1: Dùng chuột bôi đen vùng dữ liệu C2:C7, sau đó nhập công thức: =A2:A7*B2:B7
Công thức mảng trong Excel - Hình 2
- Bước 2: Nhấn tổ hợp phím Ctrl + Shift+ Enter để nhận kết quả.
Công thức mảng trong Excel - Hình 3
2.8. Hàm COUNT
Công thức COUNT trong Excel được ký hiệu =COUNT[Value1, Value2,....]
Trong đó:
- Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
- Value 2: Là tham số không bắt buộc.
Công thức này sẽ trả về một giá trị bằng với số lượng mục được tìm thấy trong phạm vi ô mong muốn của bạn. Ví dụ: nếu có 8 ô có giá trị được nhập giữa A1 và A10, = COUNT [A1: A10] sẽ trả về giá trị 8.
Công thức COUNT trong Excel đặc biệt hữu ích cho các bảng tính lớn , trong đó bạn muốn xem có bao nhiêu ô chứa các mục thực tế. Công thức này sẽ không thực hiện bất kỳ phép toán nào về giá trị của các ô. Công thức này chỉ đơn giản là tìm ra có bao nhiêu ô trong một phạm vi được chọn bị chiếm dụng với một cái gì đó.
Ví dụ về cách sử dụng hàm COUNT:
Quan sát bảng dữ liệu sau, yêu cầu dùng công thức Excel COUNT để đếm dữ liệu trong cột.
- Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức sau: =COUNT[E4:E7]
Cách sử dụng hàm COUNT trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Cách sử dụng hàm COUNT trong Excel - Hình 2
2.9. AVERAGE
Để thực hiện công thức tính trung bình trong Excel, sử dụng công thức theo hàm average theo định dạng: =AVERAGE[number1,number2,...], hoặc =AVERAGE[giá trị đầu:giá trị cuối]. Điều này sẽ tính trung bình của tất cả các giá trị hoặc phạm vi của các ô có trong ngoặc đơn.
Việc tìm trung bình của một phạm vi ô trong Excel giúp bạn không phải tìm các tổng riêng lẻ và sau đó thực hiện một phương trình phân chia riêng trên tổng số của bạn. Sử dụng = AVERAGE làm mục nhập văn bản ban đầu của bạn, bạn có thể để Excel thực hiện tất cả công việc cho bạn.
Cách sử dụng hàm AVERAGE trong Excel:
Quan sát ví dụ sau, yêu cầu tính lương trung bình theo các tháng dựa vào các số liệu trong bảng.
- Bước 1: Tại ô C9 cần hiển thị kết quả, bạn nhập công thức sau: =AVERAGE[C3:C8]
Cách dùng hàm AVERAGE trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Cách dùng hàm AVERAGE trong Excel - Hình 2
2.10. SUMIF
Công thức hàm sumif trong Excel: =SUMIF[range, criteria, [sum_range]].
Trong đó:
- Range: Là vùng được chọn có chứa các ô điều kiện.
- Criteria: Là điều kiện để thực hiện hàm này.
- Sum_range: Vùng dữ liệu cần tính tổng.
Cách sử dụng hàm SUMIF trong Excel:
Giả sử bạn muốn xác định lợi nhuận bạn đã tạo từ danh sách khách hàng tiềm năng được liên kết với mã vùng cụ thể hoặc tính tổng tiền lương của một số nhân viên - nhưng chỉ khi họ vượt quá một mức cụ thể. Với hàm SUMIF, bạn có thể dễ dàng cộng tổng các ô đáp ứng các tiêu chí nhất định.
Ví dụ: Quan sát bảng dữ liệu dưới đây, yêu cầu tính tổng thành tiền của các hóa chất có số lượng >200 bằng hàm SUMIF.
- Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức như sau: =SUMIF[D2:D10; ">200"; E2:E10]
Trong đó:
- D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
- ">200": Là điều kiện theo yêu cầu của ví dụ [có số lượng > 200].
- E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Hàm SUMIF trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Hàm SUMIF trong Excel - Hình 2
2.11. TRIM
Công thức TRIM trong Excel có nhiệm vụ loại bỏ các khoảng trắng trước và sau văn bản được nhập vào ô. Thay vì siêng năng loại bỏ và thêm khoảng trắng khi cần, bạn có thể dọn sạch mọi khoảng cách không đều bằng cách sử dụng chức năng TRIM, được sử dụng để xóa khoảng trắng thừa khỏi dữ liệu [ngoại trừ khoảng trắng giữa các từ]. Cú pháp: =TRIM[text]
Trong đó:
- TRIM: Lệnh thực hiện thao tác
- Text: Ô dữ liệu có đoạn văn bản chứa khoảng trắng cần loại bỏ.
Cách sử dụng của hàm TRIM:
Quan sát ví dụ sau, yêu cầu loại bỏ khoảng trống của dữ liệu bằng hàm TRIM.
- Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thức: =TRIM[B2]. Trong đó, B2 là ô tham chiếu.
Hàm Trim trong Excel - Hình 1
- Bước 2: Nhấn Enter để hiển thị kết quả. Sau đó giữ chuột và kéo xuống hết các ô Excel còn lại để đảm bảo kết quả được hiển thị hết.
Hàm Trim trong Excel - Hình 2
2.12. VLOOKUP
Hàm VLOOKUP thực hiện tra cứu dọc bằng cách tìm kiếm một giá trị trong cột đầu tiên của bảng và trả về giá trị trong cùng một hàng ở vị trí index_number.
Hàm VLOOKUP là một hàm tích hợp trong các hàm của Excel. Nó có thể được sử dụng như một hàm bảng tính [WS] trong Excel. Là một hàm trang tính, hàm VLOOKUP có thể được nhập như một phần của công thức trong một ô của trang tính.
Cú pháp: =VLOOKUP[lookup_value, table_array, col_index_num, [range_lookup]]
Trong đó:
- Lookup_value: là giá trị cần dò tìm
- table_array: là bảng cần dò tìm
- col_index_num: là vị trí cột cần lấy giá trị
- range_lookup: nhập vào số 0 để tìm chính xác và nhập vào số 1 để tìm gần đúng
Cách sử dụng của hàm Vlookup:
Quan sát ví dụ sau, yêu cầu xếp loại học sinh theo thứ hạng yếu, trung bình, khá, giỏi.
- Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thưc sau: =VLOOKUP[B2,$E$2:$G$9,2,0]
Trong đó:
- B2: Giá trị cần dò tìm giá trị
- $E$2:$G$9: Bảng đối chiếu giá trị [đã khóa]
- 2: Vị trí cột cần dò tìm
- 0: Chọn giá trị tuyệt đối để dò tìm
Hàm Vlookup trong Excel - Hình 1
- Bước 2: Kết quả sau khi hoàn thành như sau.
Hàm Vlookup trong Excel - Hình 2
Quan sát bảng dữ liệu bạn có thể thấy có lỗi #N/A xảy ra. Nguyên nhân là do giá trị cần tìm phải là số nguyên dương từ 1-10, lỗi xảy ra do không chấp nhập số trong khoảng hoặc chứa dấu phẩy.
Vì vậy khi sử dụng hàm Vlookup, bạn cần lưu ý chọn điều kiện cho giá trị hiển thị, tuyệt đối hay tương đối để đảm bảo kết quả hiển thị đúng với mục đích tìm kiếm của mình.
Sau khi sửa lại công thức đúng, bạn có kết quả như sau:
Hàm Vlookup trong Excel - Hình 3
2.13. AutoSum
Có lẽ SUM [] là hàm được sử dụng nhiều nhất trong Microsoft Excel. Tuy nhiên, nhiều người dùng gõ chức năng này bằng tay, điều này thực sự không hiệu quả. Thay vào đó, bạn có thể sử dụng tùy chọn AutoSum có sẵn trên Formulas trên thanh menu hoặc sử dụng đơn giản phím tắt Alt + =
AutoSum rất thông minh để đoán phạm vi tổng và bạn có thể dễ dàng thay đổi phạm vi bên trong chức năng.
Autosum trong Excel
Cách sử dụng Autosum để tính tổng:
Quan sát bảng dữ liệu sau, yêu cầu tính tổng điểm có trong bảng dữ liệu.
- Bước 1: Mở File Excel cần tính tổng, sau đó chọn Autosum để mà hình tự động nhập hàm tính tổng.
Cách tính tổng với Autosum - Hình 1
- Bước 2: Nhấn Enter để hiển thị kết quả.
Cách tính tổng với Autosum - Hình 2
2.14. MIN/MAX
Sử dụng hàm MIN để tìm số nhỏ nhất trong một phạm vi giá trị và hàm MAX để tìm số cao nhất.
Cú pháp hàm MAX: =MAX[number1, number2,...]
Trong đó:
- Number 1, Number 2 là giá trị cần so sánh.
Ví dụ về hàm Max: Dựa vào bảng số điểm về điểm số của các bạn học sinh, yêu cầu tìm điểm CAO nhất trong lớp.
- Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: = MAX[C2:E6], trong đó C2:E6 là vùng dữ liệu có chứa giá trị lớn nhất.
Ví dụ về hàm Max trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Ví dụ về hàm Max trong Excel - Hình 2
Cú pháp hàm Min: =MIN[number1, number2,...]
Trong đó:
- Number 1, Number 2 là giá trị cần so sánh.
Ví dụ về hàm MIN:
Dựa vào bảng số điểm về điểm số của các bạn học sinh, yêu cầu tìm điểm THẤP nhất trong lớp.
- Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: = MAX[E2:E6], trong đó E2:E6 là vùng dữ liệu có chứa giá trị nhỏ nhất.
Ví dụ về hàm Min trong Excel - Hình 3
- Bước 2: Nhấn Enter để nhận kết quả.
Ví dụ về hàm Min trong Excel - Hình 4
>> Xem thêm: Công thức tính lợi nhuận trong Excel chi tiết và đầy đủ nhất
2.15. CONCAT / CONCATENATE
Các hàm CONCAT và CONCATENATE thực hiện cùng một điều: cả hai kết hợp nhiều ô, phạm vi hoặc chuỗi dữ liệu vào một ô. Việc sử dụng phổ biến nhất của chức năng này là kết hợp tên và họ vào một ô.
Công thức hàm CONCATENATE: =CONCATENATE [text1, [text2], …]
Trong đó:
- Text 1: Là chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
- Text 2: Chuỗi có thể có hoặc không.
Cách sử dụng hàm CONCATENATE trong Excel:
Quan sát bảng dữ liệu sau, yêu cầu nối Họ và Tên bằng hàm CONCATENATE.
- Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: =CONCATENATE[B4,C4]
Hàm CONCATENATE trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả.
Hàm CONCATENATE trong Excel - Hình 2
2.16. NOW
Bạn muốn xem ngày giờ hiện tại bất cứ khi nào bạn mở một bảng tính cụ thể? Nhập = NOW[] vào ô nơi bạn muốn xem. Nếu bạn muốn xem một ngày trong tương lai từ ngay bây giờ, bạn có thể sử dụng công thức như =NOW[]+32. Hàm now không nhận bất kỳ đối số nào vì vậy đừng đặt bất cứ thứ gì vào giữa các dấu ngoặc.
Cách sử dụng của hàm NOW trong Excel:
Quan sát bảng dữ liệu sau, yêu cầu dùng hàm NOW để trả về ngày và thời gian hiện tại.
- Bước 1: Trong ô kết quả, bạn nhập công thức sau: =NOW[]
Hàm NOW trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả sau khi áp dụng công thức hàm.
Hàm NOW trong Excel - Hình 2
2.17. CONDITIONAL FORMATTING
Về mặt kỹ thuật, đây không phải là một công thức, nhưng nó là một công cụ cực kỳ hữu ích được tích hợp ngay trong Excel. Nếu bạn về Home –> Styles –> Conditional formatting, bạn có thể chọn nhiều tùy chọn sẽ đưa ra kết quả nếu một số điều là đúng. Bạn có thể làm rất nhiều thứ với cách làm trên.
Cách sử dụng của Conditional Formatiing trong Excel.
Ví dụ minh họa: Quan sát bảng dữ liệu sau, yêu cầu làm nổi bật các ô có chứa chữ Việt Nam trong cột Quốc Tịch.
- Bước 1: Dùng chuột bôi đen vùng dữ liệu cần làm nổi bật. Trên thanh công cụ bạn lần lượt chọn các thao tác như sau: Chọn Home->Conditional formatting ->Highlight Cells Rules->Text that Contains…
Ví dụ về cách sử dụng Conditional Formatiing trong Excel - Hình 1
- Bước 2: Hộp thoại Text that Contains hiện ra, bạn nhập chữ Việt Nam vào ô Format Cells that contain the text . Sau đó nhấn OK để hoàn tất.
Ví dụ về cách sử dụng Conditional Formatiing trong Excel - Hình 2
2.18. INDEX + MATCH
Sự kết hợp của 2 hàm chức năng để truy xuất dữ liệu đa chiều, này sẽ giúp bạn khắc phục những hạn chế gây ra những khó khăn của hàm vlookup, công thức kết hợp hàm:
= INDEX [danh sách các giá trị, MATCH [những gì bạn muốn tra cứu, cột tra cứu, định danh sắp xếp]]
Bạn có thể tìm kiếm toàn bộ bảng tính cho các giá trị thay vì chỉ tìm kiếm cột ngoài cùng bên trái.
Cách sử dụng của hàm INDEX và hàm MATCH trong Excel:
Quan sát bảng dữ liệu sau, dựa vào Sản phẩm và hãng sản xuất, yêu cầu điền đơn giá vào cột D trong bảng 1.
Cách thực hiện như sau:
- Bước 1: Trong ô D4 để hiển thị kết quả, bạn nhập công thức sau:
=INDEX[$B$15:$E$18,MATCH[B4,$B$15:$B$18,0],MATCH[C4,$B$15:$E$15,0]]
Trong đó, kết quả trả về giá trị tương ứng của sản phẩm Mouse là hàng 4 trong bảng 2, cột 2 [Cột Samsung] trong Bảng 2 là 5.
Hàm INDEX kết hợp với hàm Match trong Excel - Hình 1
- Bước 2: Copy công thức để hiển thị kết quả ở những ô tiếp theo.
Hàm INDEX kết hợp với hàm Match trong Excel - Hình 2
2.19. PROPER
PROPER rất hữu ích khi cơ sở dữ liệu của bạn có nhiều văn bản được định dạng kỳ lạ trông lộn xộn với chữ viết hoa ở sai vị trí. Vì thế khi sử dụng hàm PROPER sẽ giúp bạn thực hiện thao tác chuyển chữ thường thành chữ in hoa một cách nhanh chóng.
Cú pháp hàm: =PROPER[text]
Trong đó:
- Text: Dữ liệu chứa đoạn văn bản muốn chuyển đổi.
Cách sử dụng của hàm PROPER trong Excel:
Quan sát bảng dữ liệu sau, yêu cầu chuyển chữ đổi chữ bằng hàm PROPER.
- Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức sau: =PROPER[B14]
Hàm PROPER trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả. Sau đó dùng trỏ chuột kéo xuống hết các ô Excel bên dưới để hiển thị kết quả.
Hàm PROPER trong Excel - Hình 2
2.20. AND
Đây là một hàm logic khác trong các hàm Excel và nó sẽ kiểm tra xem một số thứ là đúng hay sai. Ví dụ: = AND [A1 = "TỐT", B2> 10] sẽ xuất TRUE nếu A1 là TỐT và giá trị của B2 lớn hơn 10. Bạn cũng có thể kiểm tra nhiều giá trị hơn hai, chỉ cần thêm nó vào dấu phẩy khác.
Cú pháp hàm AND: =AND[Điều kiện 1,Điều kiện 2,…]
Trong đó:
- Điều kiện 1, điều kiện 2 là mệnh đề Logic.
Cách sử dụng của hàm AND trong Excel.
Quan sát bảng dữ liệu sau, yêu cầu xét điều kiện sinh giỏi của các học sinh trong bảng bằng hàm AND.
- Bước 1: Trong ô E3 cần hiển thị kết quả, bạn nhập công thức sau: =AND[C3>=5, D3>=5]
Trong đó:
- AND: Lệnh hàm bắt buộc phải có
- C3>=5 và D3>=5: Điều kiện để xét học sinh giỏi là điểm môn toàn và văn lớn hơn hoặc bằng 5.
Hàm AND trong Excel - Hình 1
- Bước 2: Nhấn Enter để nhận kết quả. Sau đó dùng trỏ chuột kéo xuống hết các ô Excel bên dưới để hiển thị kết quả.
Với kết quả như bảng, những bạn hiển thị kết quả FLASE là Lê B và Phạm D không đáp ứng được tiêu chí của đề bài là cả 2 môn toán và văn lớn hơn hoặc bằng 5 điểm nên 2 bạn này không được học sinh giỏi.
Hàm AND trong Excel - Hình 2
Đây là một số những công thức trong Excel được dùng phổ biến và hỗ trợ đắc lực cho người sử dụng thực hiện cho các công việc khi dùng Excel. Nếu bạn đọc quan tâm có thể tham khảo thêm các khóa học tin học văn phòng khác trên Unica để hiểu rõ hơn về các thủ thuật, cách sử dụng hàm trong Excel thông qua các khoá học Excel giúp bạn xử lý số liệu, báo cáo một cách nhanh chóng và chính xác nhất từ đó giảm thiểu tối đa thời gian cũng như nâng cao hiệu suất công việc.
Xin cảm ơn và chúc bạn thành công.
Chúc bạn thành công!
Unica gợi ý cho bạn: Khóa học "Làm chủ excel qua 100+ chuyên đề từ cơ bản đến nâng cao"
XEM TRỌN BỘ KHÓA HỌC TẠI ĐÂY
Tags: Excel