15 Công thức Excel, Phím tắt & Thủ thuật Bàn phím giúp bạn tiết kiệm rất nhiều thời gian – Đối với hầu hết các nhà tiếp thị, cố gắng tổ chức và phân tích bảng tính trong Microsoft Excel có thể cảm thấy như đi vào một bức tường gạch nhiều lần. Bạn đang sao chép thủ công các cột và viết nguệch ngoạc xuống dạng toán dài trên một mẩu giấy, trong khi tự nghĩ: “Phải có cách tốt hơn để làm điều này.”
Sự thật mà nói, có – bạn chỉ chưa biết điều đó.
Excel có thể khó khăn theo cách đó. Một mặt, nó là một công cụ đặc biệt mạnh mẽ để báo cáo và phân tích dữ liệu tiếp thị. Mặt khác, nếu không được đào tạo bài bản, bạn sẽ dễ dàng cảm thấy như nó đang chống lại bạn. Để bắt đầu, có hơn một tá công thức quan trọng Excel có thể tự động chạy cho bạn để bạn không phải lướt qua hàng trăm ô bằng máy tính trên bàn.
Công thức Excel là gì?
Công thức Excel 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 toán học 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 và thậm chí ngày / lần.
Chúng tôi sẽ giới thiệu tất cả những điều này, và nhiều hơn nữa, trong bài đăng trên blog này.
Cách chèn công thức trong Excel
Bạn có thể tự hỏi tab “Công thức” trên thanh công cụ điều hướng trên cùng trong Excel có nghĩa là gì. Trong các phiên bản gần đây hơn của Excel, menu ngang này – được hiển thị bên dưới – cho phép bạn tìm và chèn các công thức Excel vào các ô cụ thể của bảng tính.
Bạn càng sử dụng nhiều công thức khác nhau trong Excel, bạn càng dễ nhớ chúng và thực hiện chúng theo cách thủ công. Tuy nhiên, bộ biểu tượng ở trên là một danh mục công thức tiện dụng mà bạn có thể duyệt và tham khảo lại khi bạn trau dồi kỹ năng bảng tính của mình.
Các công thức Excel cũng được gọi là “hàm.” Để chèn một bảng tính vào bảng tính của bạn, hãy chọn một ô mà bạn muốn chạy công thức, sau đó nhấp vào biểu tượng ở bên trái, “Chèn Hàm” để duyệt các công thức phổ biến và những gì chúng làm. Cửa sổ duyệt đó sẽ trông như thế này:
Bạn muốn một trải nghiệm duyệt được sắp xếp nhiều hơn? Sử dụng bất kỳ biểu tượng nào chúng tôi đã tô sáng (bên trong hình chữ nhật màu đỏ dài trong ảnh chụp màn hình đầu tiên ở trên) để tìm các công thức liên quan đến nhiều chủ đề phổ biến – như tài chính, logic và hơn thế nữa. Khi bạn đã tìm thấy công thức phù hợp với nhu cầu của mình, hãy nhấp vào “Chèn Hàm”, như hiển thị trong cửa sổ bên trên.
Bây giờ, chúng ta hãy đi sâu hơn vào một số công thức Excel quan trọng nhất và cách thực hiện từng công thức trong các tình huống điển hình.
Để giúp bạn sử dụng Excel hiệu quả hơn (và tiết kiệm rất nhiều thời gian), chúng tôi đã biên soạn một danh sách các công thức thiết yếu, phím tắt và các thủ thuật và chức năng nhỏ khác mà bạn nên biết.
LƯU Ý: Các công thức sau áp dụng cho Excel 2017 . Nếu bạn đang sử dụng phiên bản Excel cũ hơn một chút, vị trí của từng tính năng được đề cập bên dưới có thể hơi khác nhau.
1. SUM
Tất cả các công thức Excel bắt đầu bằng dấu bằng, =, theo sau là 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 bạn có thể nhập vào bảng tính, cho phép bạn tìm tổng (hoặc tổng) của hai hoặc nhiều giá trị. Để thực hiện công thức SUM, hãy nhập các giá trị bạn muốn thêm vào bằng cách sử dụng định dạng, = SUM (value 1, value 2, v.v.) .
Các giá trị bạn nhập vào công thức SUM có thể là số thực hoặc bằng số trong một ô cụ thể trong bảng tính của bạn.
- Ví dụ, để tìm SUM của 30 và 80, hãy nhập công thức sau vào một ô trong bảng tính của bạn: = SUM (30, 80) . Nhấn “Enter” và ô sẽ tạo ra tổng số của cả hai số: 110.
- Ví dụ, để tìm SUM của các giá trị trong các ô B2 và B11, hãy nhập công thức sau vào một ô trong bảng tính của bạn: = SUM (B2, B11) . Nhấn “Enter” và ô sẽ tạo ra tổng số các số hiện được điền vào các ô B2 và B11. Nếu không có số nào trong một ô, công thức sẽ trả về 0.
Hãy nhớ rằng bạn cũng có thể tìm thấy tổng giá trị của một danh sách các số trong Excel. Để tìm SUM của các giá trị trong các ô từ B2 đến B11, hãy nhập công thức sau vào một ô trong bảng tính của bạn: = SUM (B2: B11) . Lưu ý dấu hai chấm giữa cả hai ô, thay vì dấu phẩy. Xem cách nó có thể trông trong bảng tính Excel cho nhà tiếp thị nội dung, bên dưới:
2. IF
Công thức IF trong Excel được ký hiệu = IF (logic_test, value_if_true, value_if_false) . Điều này cho phép bạn nhập một giá trị văn bản vào ô “nếu” một cái gì đó khác trong bảng tính của bạn là đúng hoặc sai. Ví dụ: = IF (D2 = “Gryffindor”, “10”, “0”) sẽ trao 10 điểm cho ô D2 nếu ô đó chứa từ “Gryffindor”.
Đôi khi chúng ta muốn biết giá trị xuất hiện bao nhiêu lần trong bảng tính của mình. Nhưng cũng có những lúc chúng ta muốn tìm các ô chứa các giá trị đó và nhập dữ liệu cụ thể bên cạnh nó.
Chúng ta sẽ quay trở lại ví dụ của Sprung cho cái này. Nếu chúng tôi muốn trao 10 điểm cho tất cả những người thuộc nhà Gryffindor, thay vì nhập thủ công 10 chữ bên cạnh tên của mỗi học sinh Gryffindor, chúng tôi sẽ sử dụng công thức IF-THEN để nói: Nếu học sinh ở Gryffindor, thì anh ta hoặc cô ấy nên được mười điểm.
- Công thức: IF (logic_test, value_if_true, value_if_false)
- Logical_Test: Kiểm tra logic là phần “IF” của câu lệnh. Trong trường hợp này, logic là D2 = “Gryffindor.” Đảm bảo giá trị Logical_Test của bạn nằm trong dấu ngoặc kép.
- Value_if_True: Nếu giá trị là đúng – nghĩa là, nếu sinh viên sống ở Gryffindor – giá trị này là giá trị mà chúng tôi muốn được hiển thị. Trong trường hợp này, chúng tôi muốn nó là số 10, để chỉ ra rằng học sinh đã được trao 10 điểm. Lưu ý: Chỉ sử dụng dấu ngoặc kép nếu bạn muốn kết quả là văn bản thay vì số.
- Value_if_False: Nếu giá trị là sai – và học sinh không sống ở Gryffindor – chúng tôi muốn ô hiển thị “0”, cho 0 điểm.
- Công thức trong ví dụ dưới đây: = IF (D2 = “Gryffindor”, “10”, “0”)
3. Percentage
Để thực hiện công thức tỷ lệ phần trăm trong Excel, hãy nhập các ô bạn đang tìm tỷ lệ phần trăm cho định dạng, = A1 / B1 . Để chuyển đổi giá trị thập phân kết quả thành tỷ lệ phần trăm, tô sáng ô, nhấp vào tab Trang chủ và chọn “Tỷ lệ phần trăm” từ danh sách thả xuống số.
Không có “công thức” Excel cho tỷ lệ phần trăm trên mỗi se, nhưng Excel giúp dễ dàng chuyển đổi giá trị của bất kỳ ô nào thành tỷ lệ phần trăm để bạn không bị mắc kẹt khi tự mình tính toán và nhập lại các số.
Cài đặt cơ bản để chuyển đổi giá trị của một ô thành phần trăm nằm trong Home tab của Excel . Chọn tab này, tô sáng (các) ô bạn muốn chuyển đổi thành tỷ lệ phần trăm và nhấp vào menu thả xuống bên cạnh Định dạng có điều kiện (nút menu này lúc đầu có thể nói “Chung”). Sau đó, chọn “Percentage” từ danh sách các tùy chọn xuất hiện. Điều này sẽ chuyển đổi giá trị của từng ô bạn đã tô sáng thành tỷ lệ phần trăm. Xem tính năng này dưới đây.
Hãy ghi nhớ nếu bạn đang sử dụng các công thức khác, chẳng hạn như công thức chia (ký hiệu = A1 / B1 ), để trả về các giá trị mới, các giá trị của bạn có thể hiển thị dưới dạng số thập phân theo mặc định. Chỉ cần làm nổi bật các ô của bạn trước hoặc sau khi bạn thực hiện công thức này và đặt định dạng của các ô này thành “Percentage” từ tab Trang chủ – như được hiển thị ở trên.
4. Subtraction
Để thực hiện công thức trừ trong Excel, hãy nhập các ô bạn đang trừ theo định dạng, = SUM (A1, -B1) . Điều này sẽ trừ một ô bằng công thức SUM bằng cách thêm dấu âm trước ô bạn đang trừ. Ví dụ: nếu A1 là 10 và B1 là 6, = SUM (A1, -B1) sẽ thực hiện 10 + -6, trả về giá trị là 4.
Giống như tỷ lệ phần trăm, trừ cũng không có công thức riêng trong Excel, nhưng điều đó không có nghĩa là không thể thực hiện được. Bạn có thể trừ bất kỳ giá trị nào (hoặc các giá trị bên trong các ô) theo hai cách khác nhau.
- Sử dụng công thức = SUM. Để trừ nhiều giá trị với nhau, hãy nhập các ô bạn muốn trừ theo định dạng = SUM (A1, -B1), bằng dấu âm (được biểu thị bằng dấu gạch nối) trước ô có giá trị bạn đang trừ. Nhấn enter để trả về sự khác biệt giữa cả hai ô có trong ngoặc đơn. Xem nó trông như thế nào trong ảnh chụp màn hình ở trên.
- Sử dụng định dạng, = A1-B1 . Để trừ nhiều giá trị với nhau, chỉ cần nhập một dấu bằng theo sau là giá trị hoặc ô đầu tiên của bạn, dấu gạch nối và giá trị hoặc ô bạn đang trừ. Nhấn Enter để trả về sự khác biệt giữa cả hai giá trị.
5. Multiplication
Để thực hiện công thức nhân trong Excel, hãy nhập các ô bạn đang nhân theo định dạng, = A1 * B1 . Công thức này sử dụng dấu hoa thị để nhân ô A1 với ô B1. Ví dụ: nếu A1 là 10 và B1 là 6, = A1 * B1 sẽ trả về giá trị là 60.
Bạn có thể nghĩ rằng nhân giá trị trong Excel có công thức riêng hoặc sử dụng ký tự “x” để biểu thị phép nhân giữa nhiều giá trị. Trên thực tế, nó dễ như dấu hoa thị – *.
Để nhân hai hoặc nhiều giá trị trong bảng tính Excel, hãy chọn một ô trống. Sau đó, nhập các giá trị hoặc ô bạn muốn nhân với nhau theo định dạng, = A1 * B1 * C1 … v.v … Dấu hoa thị sẽ nhân hiệu quả từng giá trị có trong công thức.
Nhấn Enter để trả lại sản phẩm bạn muốn. Xem nó trông như thế nào trong ảnh chụp màn hình ở trên.
6. Division
Để thực hiện công thức phân chia trong Excel, hãy nhập các ô bạn đang chia theo định dạng, = A1 / B1 . Công thức này sử dụng dấu gạch chéo về phía trước, “/,” để chia ô A1 cho ô B1. Ví dụ: nếu A1 là 5 và B1 là 10, = A1 / B1 sẽ trả về giá trị thập phân là 0,5.
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. Kết quả phải ở định dạng sau: = B2 / A2 , như được hiển thị trong ảnh chụp màn hình bên dưới.
Nhấn Enter và thương số mong muốn của bạn sẽ xuất hiện trong ô bạn đã tô sáng ban đầu.
7. DATE
Công thức DATE Excel được ký hiệu = DATE (năm, tháng, ngày) . 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. Ví dụ: nếu A1 là 2018, B1 là 7 và C1 là 11, = DATE (A1, B1, C1) sẽ trở lại vào 7/11/2018.
Tạo ngày trong các ô của bảng tính Excel có thể là một nhiệm vụ hay thay đổi mọi lúc. May mắn thay, có một công thức tiện dụng để giúp định dạng ngày của bạn dễ dàng. Có hai cách để sử dụng công thức này:
- Tạo ngày từ một loạt các giá trị ô. Để thực hiện việc này, hãy chọn một ô trống, nhập “= DATE” và trong ngoặc đơn, nhập các ô có giá trị tạo ngày bạn muốn – bắt đầu bằng năm, sau đó là số tháng, sau đó là ngày. Định dạng cuối cùng sẽ giống như thế này: = DATE (năm, tháng, ngày). Xem nó trông như thế nào trong ảnh chụp màn hình dưới đây.
- Tự động thiết lập ngày hôm nay. Để thực hiện việc này, hãy chọn một ô trống và nhập chuỗi văn bản sau: = DATE (NĂM (HÔM NAY ()), THÁNG (HÔM NAY ()), NGÀY (HÔM NAY ())) . Nhấn enter sẽ trả về ngày hiện tại bạn đang làm việc trong bảng tính Excel của mình.
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.
8. ARRAY
Một công thức mảng trong Excel bao quanh một công thức đơn giản trong các ký tự dấu ngoặc kép sử dụng định dạng, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)} . Bằng cách nhấn ctrl + shift + centre, điều này sẽ tính toán và trả về giá trị từ nhiều phạm vi, thay vì chỉ các ô riêng lẻ được thêm vào hoặc nhân với nhau.
Việc tính tổng, sản phẩm hoặc thương số của từng ô là dễ dàng – chỉ cần sử dụng công thức = SUM và nhập các ô, giá trị hoặc phạm vi ô bạn muốn thực hiện số học đó. Nhưng những gì về nhiều phạm vi? Làm thế nào để bạn tìm thấy giá trị kết hợp của một nhóm lớn các tế bào?
Mảng số là một cách 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. Ví dụ: nếu bạn đang tìm kiếm tổng doanh thu bán hàng từ một số đơn vị đã bán, thì công thức mảng trong Excel là hoàn hảo cho bạn. Đây là cách bạn làm điều đó:
- Để bắt đầu sử dụng công thức mảng, nhập “= SUM” và trong ngoặc đơn, nhập phạm vi đầu tiên trong hai (hoặc ba hoặc bốn) ô bạn muốn nhân với nhau. Đây là tiến trình của bạn có thể trông như thế nào: = SUM (C2: C5
- Tiếp theo, thêm dấu hoa thị sau ô cuối cùng của phạm vi đầu tiên bạn đưa vào công thức. Điều này là viết tắt của phép nhân. Theo dấu hoa thị này, nhập phạm vi ô thứ hai của bạn. Bạn sẽ nhân phạm vi ô thứ hai này với ô thứ nhất. Tiến trình của bạn trong công thức này sẽ giống như sau: = SUM (C2: C5 * D2: D5)
- Sẵn sàng nhấn Enter? Không quá nhanh … Vì công thức này quá phức tạp, Excel dành một lệnh bàn phím khác cho các mảng. Khi bạn đã đóng dấu ngoặc đơn trên công thức mảng của mình, hãy nhấn C trl + Shift + Enter . Điều này sẽ nhận ra công thức của bạn dưới dạng một mảng, bao bọc công thức của bạn bằng các ký tự dấu ngoặc và trả lại thành công sản phẩm của bạn trong cả hai phạm vi được kết hợp.
Trong tính toán doanh thu, điều này có thể cắt giảm đáng kể thời gian và công sức của bạn. Xem công thức cuối cùng trong ảnh chụp màn hình ở trên.
9. COUNT
Công thức COUNT trong Excel được ký hiệu = COUNT ( Ô bắt đầu: Ô kết thú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ó tám ô 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ế. Đừng để bị lừa: 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ì đó.
Sử dụng công thức in đậm ở trên, bạn có thể dễ dàng chạy một số ô đang hoạt động trong bảng tính của mình. Kết quả sẽ trông giống như thế này:
10. AVERAGE
Để thực hiện công thức trung bình trong Excel, hãy nhập các giá trị, ô hoặc phạm vi ô mà bạn đang tính trung bình theo định dạng, = AVERAGE (number1, number2, v.v.) hoặc = AVERAGE (Giá trị bắt đầ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.
Để tham khảo, trung bình của một nhóm số bằng tổng của các số đó, chia cho số mục trong nhóm đó.
11. SUMIF
Công thức SUMIF trong Excel được ký hiệu =SUMIF(range, criteria, [sum range]). Điều này sẽ trả về tổng của các giá trị trong một phạm vi ô mong muốn, tất cả đều đáp ứng một tiêu chí. Ví dụ: = SUMIF (C3: C12, “> 70.000”) sẽ trả về tổng giá trị giữa các ô C3 và C12 từ chỉ các ô lớn hơn 70.000.
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ể. Làm điều đó bằng tay nghe có vẻ hơi tốn thời gian, để nói rằng ít nhất.
Với hàm SUMIF, không cần phải như vậy – bạn có thể dễ dàng cộng tổng các ô đáp ứng các tiêu chí nhất định, như trong ví dụ về lương ở trên.
- Công thức : =SUMIF(range, criteria, [sum range]).
- range: Phạm vi đang được thử nghiệm bằng tiêu chí của bạn.
- criteria: Tiêu chí xác định ô nào trong Criteria_range1 sẽ được thêm vào cùng nhau
- [Sum_range]: Một phạm vi ô tùy chọn bạn sẽ thêm vào ngoài Phạm vi đầu tiên được nhập. Lĩnh vực này có thể được bỏ qua.
Trong ví dụ dưới đây, chúng tôi muốn tính tổng số tiền lương lớn hơn 70.000 đô la. Hàm SUMIF đã thêm số tiền vượt quá số đó trong các ô từ C3 đến C12, với công thức = SUMIF (C3: C12, “> 70.000”) .
12. TRIM
Công thức TRIM trong Excel được ký hiệu = TRIM (text) . Công thức này sẽ loại bỏ bất kỳ khoảng trắng nào được nhập trước và sau văn bản được nhập vào ô. Ví dụ: nếu A2 bao gồm tên “Steve Peterson” với các khoảng trắng không mong muốn trước tên đầu tiên, = TRIM (A2) sẽ trả về “Steve Peterson” không có khoảng trắng trong một ô mới.
Email và chia sẻ tập tin là những công cụ tuyệt vời tại nơi làm việc ngày nay. Đó là, cho đến khi một trong những đồng nghiệp của bạn gửi cho bạn một bảng tính với một số khoảng cách thực sự thú vị. Những không gian giả mạo đó có thể gây khó khăn cho việc tìm kiếm dữ liệu, mà chúng còn ảnh hưởng đến kết quả khi bạn cố gắng thêm các cột số.
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ông thức : = TRIM (text).
- Text: Văn bản hoặc ô mà bạn muốn xóa khoảng trắng.
Dưới đây là ví dụ về cách chúng tôi sử dụng hàm TRIM để xóa các khoảng trắng thừa trước danh sách tên. Để làm như vậy, chúng tôi đã nhập = TRIM (“A2”) vào Thanh công thức và sao chép tên này cho mỗi tên bên dưới nó trong một cột mới bên cạnh cột có khoảng trắng không mong muốn.
13. LEFT, MID, and RIGHT
Giả sử bạn có một dòng văn bản trong một ô mà bạn muốn chia thành một vài phân đoạn khác nhau. Thay vì nhập lại thủ công từng đoạn mã vào cột tương ứng, người dùng có thể tận dụng một loạt các hàm chuỗi để giải cấu trúc chuỗi khi cần: TRÁI, TRUNG hoặc PHẢI.
LEFT
- Mục đích : Được sử dụng để trích xuất các số hoặc ký tự X đầu tiên trong một ô.
- Công thức : =LEFT(text, number_of_characters)
- text : Chuỗi mà bạn muốn trích xuất từ.
- Number_of_char character : Số lượng ký tự bạn muốn trích xuất bắt đầu từ ký tự ngoài cùng bên trái.
Trong ví dụ dưới đây, chúng tôi đã nhập = LEFT (A2,4) vào ô B2 và sao chép nó vào B3: B6. Điều đó cho phép chúng tôi trích xuất 4 ký tự đầu tiên của mã.
MID
- Mục đích : Được sử dụng để trích xuất các ký tự hoặc số ở giữa dựa trên vị trí.
- Công thức : =MID(text, start_position, number_of_characters)
- text : Chuỗi mà bạn muốn trích xuất từ.
- Start_poseition : Vị trí trong chuỗi mà bạn muốn bắt đầu trích xuất từ. Ví dụ, vị trí đầu tiên trong chuỗi là 1.
- Number_of_char character : Số lượng ký tự bạn muốn trích xuất.
Trong ví dụ này, chúng tôi đã nhập = MID (A2,5,2) vào ô B2 và sao chép nó vào B3: B6. Điều đó cho phép chúng tôi trích xuất hai số bắt đầu ở vị trí thứ năm của mã.
RIGHT
- Mục đích: Được sử dụng để trích xuất các số hoặc ký tự X cuối cùng trong một ô.
- Công thức: =RIGHT(text, number_of_characters)
- Text : Chuỗi mà bạn muốn trích xuất từ.
- Number_of_char character : Số lượng ký tự bạn muốn trích xuất bắt đầu từ ký tự ngoài cùng bên phải.
Vì lợi ích của ví dụ này, chúng tôi đã nhập = RIGHT (A2,2) vào ô B2 và sao chép nó vào B3: B6. Điều đó cho phép chúng tôi trích xuất hai số cuối của mã.
14. VLOOKUP
Đây là một oldie, nhưng một goodie – và nó sâu hơn một chút so với một số công thức khác mà chúng tôi đã liệt kê ở đây. Nhưng nó đặc biệt hữu ích cho những lúc bạn có hai bộ dữ liệu trên hai bảng tính khác nhau và muốn kết hợp chúng thành một bảng tính.
Đồng nghiệp của tôi, Rachel Sprung – người có hướng dẫn ” Cách sử dụng Excel ” là phải đọc cho bất kỳ ai muốn tìm hiểu – sử dụng danh sách tên, địa chỉ email và công ty làm ví dụ. Nếu bạn có một danh sách tên người bên cạnh địa chỉ email của họ trong một bảng tính và danh sách địa chỉ email của những người đó bên cạnh tên công ty của họ, nhưng bạn muốn tên, địa chỉ email và tên công ty của những người đó xuất hiện ở một nơi – đó là nơi VLOOKUP xuất hiện.
Lưu ý: Khi sử dụng công thức này, bạn phải chắc chắn rằng ít nhất một cột xuất hiện giống hệt nhau trong cả hai bảng tính. Quét các bộ dữ liệu của bạn để đảm bảo cột dữ liệu bạn đang sử dụng để kết hợp thông tin của bạn giống hệt nhau, bao gồm không có khoảng trắng thừa.
- Công thức: VLOOKUP(lookup value, table array, column number, [range lookup])
- lookup value: Giá trị giống hệt bạn có trong cả hai bảng tính. Chọn giá trị đầu tiên trong bảng tính đầu tiên của bạn. Trong ví dụ của Sprung sau đây, điều này có nghĩa là địa chỉ email đầu tiên trong danh sách hoặc ô 2 (C2).
- table array: Phạm vi các cột trên Bảng 2 bạn sẽ lấy dữ liệu của mình, bao gồm cột dữ liệu giống với giá trị tra cứu của bạn (trong ví dụ của chúng tôi, địa chỉ email) trong Bảng 1 cũng như cột dữ liệu bạn ‘ đang cố gắng sao chép vào Trang tính 1. Trong ví dụ của chúng tôi, đây là “Trang tính 2! A: B.” “A” có nghĩa là Cột A trong Bảng 2, là cột trong Bảng 2 nơi dữ liệu giống hệt với giá trị tra cứu của chúng tôi (email) trong Bảng 1 được liệt kê. “B” có nghĩa là Cột B, chứa thông tin chỉ có trong Bảng 2 mà bạn muốn dịch sang Bảng 1.
- column number: Mảng bảng cho Excel biết vị trí (cột nào) dữ liệu mới bạn muốn sao chép vào Bảng 1 được đặt. Trong ví dụ của chúng tôi, đây sẽ là cột “Nhà”, cột thứ hai trong mảng bảng của chúng tôi, làm cho cột số 2.
- [range lookup]: Sử dụng FALSE để đảm bảo bạn chỉ lấy các giá trị khớp chính xác.
- Công thức với các biến từ ví dụ của Sprung bên dưới: = VLOOKUP (C2, Sheet2! A: B, 2, FALSE)
Trong ví dụ này, Trang 1 và Trang 2 chứa các danh sách mô tả thông tin khác nhau về cùng một người và luồng chung giữa hai người là địa chỉ email của họ. Giả sử chúng tôi muốn kết hợp cả hai bộ dữ liệu để tất cả thông tin về ngôi nhà từ Trang 2 chuyển sang Trang 1. Đây là cách nó sẽ hoạt động:
15. RANDOMIZE
Có một bài viết tuyệt vời giống như công thức RANDOMIZE của Excel để xáo trộn một cỗ bài. Toàn bộ bộ bài là một cột và mỗi thẻ – 52 trong một bộ bài – là một hàng. “Để xáo trộn bộ bài”, Steve McDonnell viết, “bạn có thể tính toán một cột dữ liệu mới, điền vào mỗi ô trong cột với một số ngẫu nhiên và sắp xếp sổ làm việc dựa trên trường số ngẫu nhiên.”
Trong tiếp thị, bạn có thể sử dụng tính năng này khi bạn muốn chỉ định một số ngẫu nhiên cho danh sách liên hệ – như nếu bạn muốn thử nghiệm một chiến dịch email mới và phải sử dụng tiêu chí mù để chọn ai sẽ nhận được. Bằng cách gán số cho các số liên lạc đã nói, bạn có thể áp dụng quy tắc, bất kỳ liên hệ nào có số 6 trở lên sẽ được thêm vào chiến dịch mới.
- Công thức: RAND ()
- Bắt đầu với một cột liên lạc. Sau đó, trong cột liền kề với nó, hãy gõ RAND () – – không có dấu ngoặc kép – bắt đầu bằng hàng liên hệ trên cùng.
- Ví dụ dưới đây: RANDBETweEN (dưới cùng, trên cùng)
- RANDBETweEN cho phép bạn ra lệnh cho phạm vi số mà bạn muốn được chỉ định. Trong trường hợp của ví dụ này, tôi muốn sử dụng từ một đến 10.
- bottom: Số thấp nhất trong phạm vi.
- top: Số cao nhất trong phạm vi,
- Công thức trong ví dụ dưới đây: = RANDBETweEN (1,10)
Cách chèn công thức trong Excel cho toàn bộ cột
Để chèn công thức trong Excel cho toàn bộ cột trong bảng tính của bạn, hãy nhập công thức vào ô trên cùng của cột bạn muốn và nhấn “Enter”. Sau đó, tô sáng và nhấp đúp vào góc dưới bên phải của ô này để sao chép công thức vào mọi ô bên dưới nó trong cột.
Đôi khi, bạn có thể muốn chạy cùng một công thức trên toàn bộ một hàng hoặc cột của bảng tính. Ví dụ, giả sử bạn có một danh sách các số trong cột A và B của bảng tính và muốn nhập tổng số riêng lẻ của mỗi hàng vào cột C.
Rõ ràng, sẽ quá tẻ nhạt khi điều chỉnh các giá trị của công thức cho từng ô để bạn tìm thấy tổng số của mỗi hàng tương ứng. May mắn thay, Excel cho phép bạn tự động cạnh tranh cột; tất cả bạn phải làm là nhập công thức trong hàng đầu tiên. Kiểm tra các bước sau:
- Nhập công thức của bạn vào một ô trống và nhấn “Enter” để chạy công thức.
- Di con trỏ qua góc dưới bên phải của ô chứa công thức. Bạn sẽ thấy một biểu tượng “+” nhỏ, đậm xuất hiện.
- Mặc dù bạn có thể nhấp đúp vào biểu tượng này để tự động điền toàn bộ cột theo công thức của mình, bạn cũng có thể nhấp và kéo con trỏ xuống bằng tay để chỉ điền vào một độ dài cụ thể của cột.
Khi bạn đã đến ô cuối cùng trong cột bạn muốn nhập công thức của mình, hãy thả chuột để sao chép công thức. Sau đó, chỉ cần kiểm tra từng giá trị mới để đảm bảo nó tương ứng với các ô chính xác.
Phím tắt Excel
1. Nhanh chóng chọn hàng, cột hoặc toàn bộ bảng tính.
Có lẽ bạn đang bị khủng hoảng thời gian. Ý tôi là, ai không? Không có thời gian, không có vấn đề. Bạn có thể chọn toàn bộ bảng tính của mình chỉ bằng một cú nhấp chuột. Tất cả bạn phải làm chỉ đơn giản là nhấp vào tab ở góc trên bên trái của trang tính của bạn để làm nổi bật tất cả mọi thứ cùng một lúc.
Đối với máy Mac:
- Chọn Cột = Command + Shift + Down/Up
- Chọn Hàng = Command + Shift + Right/Left
Dành cho PC:
- Chọn Cột = Control + Shift + Down/Up
- Chọn Hàng = Control + Shift + Right/Left
Phím tắt này đặc biệt hữu ích khi bạn làm việc với các tập dữ liệu lớn hơn, nhưng chỉ cần chọn một phần cụ thể của nó.
2. Nhanh chóng mở, đóng hoặc tạo một bảng tính.
Cần mở, đóng hoặc tạo một bảng tính khi đang bay? Các phím tắt sau đây sẽ cho phép bạn hoàn thành bất kỳ hành động nào ở trên trong thời gian chưa đến một phút.
Đối với máy Mac:
- Mở = Command + O
- Đóng = Command+ W
- Tạo mới = Command+ N
Dành cho PC:
- Mở = Control + O
- Đóng = Control + F4
- Tạo mới = Control + N
3. Định dạng số thành tiền tệ.
Có dữ liệu thô mà bạn muốn biến thành tiền tệ? Cho dù đó là số liệu lương, ngân sách tiếp thị hoặc bán vé cho một sự kiện, giải pháp rất đơn giản. Chỉ cần tô sáng các ô bạn muốn định dạng lại và chọn Control + Shift + $ .
4. Chèn ngày giờ hiện tại vào một ô.
Cho dù bạn đang đăng nhập các bài đăng trên phương tiện truyền thông xã hội hoặc theo dõi các nhiệm vụ bạn đang kiểm tra danh sách việc cần làm, bạn có thể muốn thêm dấu ngày và thời gian vào bảng tính của mình. Bắt đầu bằng cách chọn ô mà bạn muốn thêm thông tin này.
Sau đó, tùy thuộc vào những gì bạn muốn chèn, hãy thực hiện một trong những điều sau đây:
- Chèn ngày hiện tại = Control +; (semi-colon)
- Chèn thời gian hiện tại = Control + Shift +; (semi-colon)
- Chèn ngày giờ hiện tại = Control +; (semi-colon) , KHÔNG GIAN, và sau đó Control + Shift +; (semi-colon) .
Thủ thuật Excel khác
1. Tùy chỉnh màu của các tab của bạn.
Nếu bạn có rất nhiều trang tính khác nhau trong một sổ làm việc – điều tốt nhất với chúng tôi – sẽ giúp bạn dễ dàng xác định nơi bạn cần đến bằng cách mã hóa màu các tab. Ví dụ: bạn có thể gắn nhãn báo cáo tiếp thị của tháng trước bằng màu đỏ và tháng này bằng màu cam.
Chỉ cần nhấp chuột phải vào một tab và chọn “Tab Color.” Một cửa sổ bật lên sẽ xuất hiện cho phép bạn chọn một màu từ một chủ đề hiện có hoặc tùy chỉnh một màu để đáp ứng nhu cầu của bạn.
2. Thêm một bình luận cho một tế bào.
Khi bạn muốn tạo ghi chú hoặc thêm nhận xét vào một ô cụ thể trong trang tính, chỉ cần nhấp chuột phải vào ô bạn muốn nhận xét, sau đó nhấp vào Insert Comment. Nhập bình luận của bạn vào hộp văn bản và nhấp bên ngoài hộp bình luận để lưu nó.
Các ô chứa ý kiến hiển thị một hình tam giác nhỏ, màu đỏ ở góc. Để xem bình luận, di chuột qua nó.
3. Sao chép và sao chép định dạng.
Nếu bạn đã từng dành thời gian định dạng một tờ theo ý thích của mình, bạn có thể đồng ý rằng đó không hẳn là hoạt động thú vị nhất. Trên thực tế, nó khá tẻ nhạt.
Vì lý do đó, có khả năng là bạn không muốn lặp lại quy trình vào lần tới – bạn cũng không phải làm thế. Nhờ Công cụ định dạng của Excel, bạn có thể dễ dàng sao chép định dạng từ một khu vực của trang tính sang một khu vực khác.
Chọn những gì bạn muốn sao chép, sau đó chọn tùy chọn Format Painter – biểu tượng bút vẽ – từ bảng điều khiển. Sau đó, con trỏ sẽ hiển thị một cọ vẽ, nhắc bạn chọn ô, văn bản hoặc toàn bộ bảng tính mà bạn muốn áp dụng định dạng đó, như hiển thị bên dưới:
4. Xác định các giá trị trùng lặp.
Trong nhiều trường hợp, các giá trị trùng lặp – như nội dung trùng lặp khi quản lý SEO – có thể gây rắc rối nếu không được xử lý. Trong một số trường hợp, mặc dù, bạn chỉ cần nhận thức được nó.
Dù tình huống có thể là gì, thật dễ dàng để hiển thị bất kỳ giá trị trùng lặp hiện có nào trong bảng tính của bạn chỉ bằng một vài bước nhanh chóng. Để làm như vậy, nhấp vào tùy chọn Conditional Formatting và chọn Highlight Cell Rules > Duplicate Values
Trong tiếp thị, việc sử dụng Excel là không thể tránh khỏi – nhưng với những thủ thuật này, nó không phải quá khó khăn. Như họ nói, thực hành làm cho hoàn hảo. Bạn càng sử dụng các công thức, phím tắt và thủ thuật này, chúng sẽ càng trở thành bản chất thứ hai.
Tâm Trần – ATPSOFTWARE
————————————————————-
Liên hệ ATP Software
Website : https://atpsoftware.vn/
Group : https://www.facebook.com/groups/CongDongATP/
Page : https://www.facebook.com/atpsoftware.tools/
Hotline : 0931 9999 11 – 0967 9999 11 – 1800 0096
Có thể bạn quan tâm:
>> Chia sẻ bộ tài liệu Digital Marketing độc nhất vô nhị
>> Kinh doanh kiếm tiền với Amazon.com hiệu quả nhất từ A-Z
>> Hướng dẫn doanh nghiệp kê khai thuế qua mạng chi tiết!
