Danh mục: Excel

Ứng dụng bảng tính Excel của Microsoft Office là một công cụ mạnh mẽ, sở hữu đầy đủ tính năng để làm việc với các bảng tính, thống kê, theo dõi, báo cáo phức tạp. Với số lượng câu lệnh, tính năng khổng lồ bạn sẽ mất rất nhiều thời gian để có thể sử dụng thành thạo, nhưng nếu nắm được một số mẹo dưới đây sẽ giúp giảm đáng kể thời gian khi làm việc trên Excel.

Những thao tác và mẹo trong bài viết này được thực hiện trên Microsoft Excel 2016 – Ứng dụng bảng tính mới nhất của Microsoft.

1. Sử dụng mẫu

Một trong những cách tiết kiệm thời gian tốt nhất cho hầu hết mọi tác vụ là sử dụng các mẫu (template). Bạn có thể dùng chúng trong Microsoft Word để phục vụ các chương trình nghị sự, bản tin hay báo cáo. Trong Excel, mẫu lại càng hữu ích. Đối với timeline dự án, lịch, hóa đơn, ngân sách và nhiều hơn nữa, lợi dụng những công cụ định dạng sẵn, lại cực kỳ thuận tiện này chắc chắn sẽ giúp tiết kiệm được nhiều thời gian và công sức.

Việc thu thập các mẫu bảng tính cần thiết cũng dễ dàng như khi sử dụng chúng. Trang web của Microsoft cung cấp rất nhiều mẫu Excel khác nhau cho lịch, kế hoạch, công cụ theo dõi, bảng thời gian,… Vertex24 có nhiều lựa chọn mẫu thú vị cho lịch, ngân sách, danh sách việc cần làm và bảng cân đối. Một nguồn mẫu Excel khổng lồ cho hóa đơn, hàng tồn kho, form đăng nhập,… là Spreadsheet 123.

2. Cố định hàng và cột trong Excel

Nếu có một bảng dài chứa hàng tấn dữ liệu, bạn có thể phải cuộn chuột xuống hoặc lên rất lâu để có thể tìm đến vùng mình cần, nhưng khi tìm được dữ liệu lại không biết nó là cái gì, do dòng tiêu đề không thể nhìn thấy. Điều này rất mất thời gian khi phải kéo lên kéo xuống liên tục chỉ để xem tiêu đề của dữ liệu. Tuy nhiên, nếu đóng băng các hàng và cột thì những tiêu đề vẫn sẽ được giữ nguyên khi di chuyển chuột trong bảng tính.

Vào tab View > Freeze Panes trên dải Ribbon. Trong menu thả xuống của Freeze Panes, chọn Freezze Panes (đóng băng cả hàng trên cùng và cột đầu tiên), Freeze Top Row (đóng băng hàng trên cùng của bảng tính), Freeze First Column (đóng băng cột đầu tiên của bảng tính).

Bây giờ, khi cuộn chuột xuống dưới, lên trên, sang phải, sang trái, bạn sẽ thấy cột và hàng đã đóng băng không bị di chuyển và luôn ở trong tầm mắt. Để hủy bỏ đóng băng những hàng, cột này, chỉ cần chọn lệnh Freeze Panes lần nữa và nhấp vào Unfreeze Panes.

Lưu ý: Với những phiên bản Excel cũ hơn, quá trình trên có thể khác biệt đôi chút. Chọn ô chung cho cả hàng và cột muốn đóng băng, sau đó nhấp vào Freeze Panes.

3. Sử dụng Fill Handle

Fill Handle là một dấu chấm đen xuất hiện ở góc dưới bên phải khi chọn một hoặc một nhóm các ô trong một cột. Biết cách sử dụng Fill Handle sẽ giúp bạn tiết kiệm được hàng tấn thời gian khi cần phải điền dữ liệu như số thứ tự, ngày, tháng, năm liên tục vào nhiều ô. Có vài cách để sử dụng Fill Handle.

Đầu tiên, có thể nhanh chóng tạo ra danh sách các số đếm bằng cách nhập 1 vào ô đầu tiên, 2 vào ô bên dưới nó, rồi chọn cả hai ô, Fill Handle xuất hiện, kéo xuống các ô bên dưới cho đến khi đạt đến số mong muốn. Không nhất thiết phải điền từ 1, bạn có thể làm từ bất cứ số nào như 15, 20, nhưng nó chỉ có thể tạo ra một dãy số tiến, liên tục. Nghĩa là nếu điền 15, bạn sẽ tạo đc danh sách từ 15, 16, 17, 18,…

Khi sử dụng tính năng này cho ngày tháng, chỉ cần điền vào một ngày bất kỳ như 05/01/2017, chọn ô đó và kéo xuống dưới, Excel sẽ tự động tăng ngày tháng đúng y như lịch. Thao tác này cũng có thể áp dụng khi cần tạo cột cho các tháng trong năm hay các ngày trong tuần.

Một cách sử sụng hữu ích khác là khi cần điền giá trị tương tự vào toàn bộ cột trong bảng tính. Ví dụ, điền vào ô chữ cái A, chọn ô và nhấp đúp chuột vào Fill Handle. Thao tác này sẽ nhập giá trị A cho tất cả các ô còn lại của cột trong bảng tính.

4. Chuyển đổi giữa cột và hàng

Nếu bạn có một bảng tính sử dụng các đề mục trong cột hoặc hàng (hoặc cả 2) và nghĩ rằng khi đổi cột cho hàng thì bảng tính sẽ trực quan hơn thì hãy sử dụng mẹo này để thực hiện nhanh chóng và dễ dàng. Nó giúp bạn tiết kiệm nhiều thời gian hơn khi không phải gõ lại các tiêu đề.

Dưới đây là các bước để di chuyển các ô từ hàng tiêu đề sang cột tiêu đề:

• Chọn các ô trong cột chứa tiêu đề
• Nhấp chuột phải, chọn Copy hoặc nhấp vào nút Copy trên tab Home của dải Ribbon
• Chọn ô trong hàng và cột bạn muốn các tiêu đề bắt đầu.
• Nhấp chuột phải và chọn Paste Special hoặc nhấp Paste > Paste Special trên tab Home của dải Ribbon
• Tích vào hộp checkbox Transpose ở góc dưới bên phải
• Nhấn OK và xem kết quả

5. Truy cập máy tính

Bên cạnh việc hỗ trợ add-in máy tính từ bên ngoài, Excel cũng tích hợp sẵn tính năng máy tính, do đó nếu không muốn cài thêm add-in, bạn có thể tận dụng lợi thế của máy tính có sẵn này. Điều này sẽ tiện lợi trong trường hợp muốn tính toán nhanh chóng phép tính không được xây dựng trong bảng tính.

Excel cho phép thêm máy tính vào cả dải Ribbon và thanh công cụ Quick Access.

Để thêm máy tính vào 2 vị trí này, bắt đầu bằng cách chọn File > Options. Sau đó, chọn Customize Ribbon (thêm máy tính vào dải Ribbon) hoặc Quick Access Toolbar (thêm vào Quick Access), tùy thuộc vào vị trí bạn muốn.

Trong Choose commands from: > All Commands > Calculator và nhấn Add >> để thêm máy tính vào thanh công cụ Quick Access. Nếu thêm vào Ribbon thì cần tạo một nhóm tùy chỉnh rồi thêm máy tính vào đó.

6. Liên kết các tab hoặc các cột

Nếu file Excel của bạn có chứa nhiều bảng tính với dữ liệu được tham chiếu chéo giữa chúng thì việc tạo ra một liên kết trực tiếp sẽ giúp truy cập nhanh hơn. Điều này đặc biệt hữu ích nếu cần chia sẻ bảng tính với những người khác để họ có thể di chuyển đến dữ liệu được tham chiếu một cách nhanh chóng.

Thực hiện theo các bước đơn giản dưới đây để tạo liên kết:

• Chọn ô có chứa dữ liệu muốn liên kết
• Nhấp chuột phải chọn Hyperlink hoặc đi đến tab Insert, chọn Hyperlink
• Trong cửa sổ pop-up hiện ra, chọn Place in this Document
• Sau đó nhập văn bản để hiển thị trong ô đó, nhập một tham chiếu ô cụ thể nếu muốn và bảng tính trong file Excel với dữ liệu đang liên kết đến
• Nhấn OK

7. Sử dụng phím tắt của Excel

Thường xuyên sử dụng các phím tắt khi thao tác trên Excel chắc chắn là một giải pháp tiết kiệm thời gian hiệu quả.

Dưới đây là một số phím tắt có thể tăng tốc độ công việc một cách đáng kể:

• F4 hoặc Fn + F4 để lặp lại lệnh cuối cùng, bao gồm thay đổi định dạng như màu sắc hoặc phông chữ
• Alt + H, E, A để xóa các nội dung, định dạng và dữ liệu đính kèm khác trong ô được chọn
• Ctrl + W để đóng bảng tính và Ctrl + O để mở bảng mới
• Ctrl + Shift + : (dấu hai chấm) để nhập vào thời gian hiện tại và Ctrl + Shift + ; (dấu chấm phẩy) để nhập vào ngày hiện tại
• Shift + F9 để tính toán các bảng tính đang hoạt động
• Shift + Tab để di chuyển đến ô trước hoặc tùy chọn
• Ctrl + Home để di chuyển đến đầu bảng tính
• Ctrl + Page Down để di chuyển đến bảng tiếp theo trong một file Excel và Ctrl + Page Up để di chuyển đến bảng tính trước đó

8. Làm việc với AutoSum

Một số người có thể nghĩ rằng làm việc với các công thức trong Excel quá tốn thời gian. Nhưng ngay cả đối với các phương trình đơn giản, các hàm tích hợp này chắc chắn có thể giúp người dùng thao tác nhanh hơn.

Trừ khi bạn đã di chuyển hoặc xóa nó khỏi ribbon, nút AutoSum sẽ nằm trên tab Home. Tính năng tiện dụng này cung cấp cho người dùng các công thức phổ biến nhất với một cú nhấp chuột. Bạn có thể thêm, đếm hoặc tính trung bình cho một nhóm số hay tìm ra số có giá trị lớn nhất (max) hoặc nhỏ nhất (min) trong tập hợp đó. Chỉ cần nhấp vào mũi tên trên nút AutoSum để chọn công thức bạn cần.

Ngoài tính năng AutoSum, tab Formulas còn chứa nhiều tùy chọn hơn. Mỗi công thức được đặt vào một danh mục cụ thể để người dùng dễ tìm kiếm. Bạn có thể chọn từ các hàm liên quan đến tài chính, logic, toán học, thống kê hoặc kỹ thuật.

Nhưng đối với các công thức thường được sử dụng, tính năng AutoSum rất nhanh chóng và tiện lợi.

9. Sử dụng Conditional formatting (định dạng có điều kiện) đơn giản

Conditional formatting (Định dạng có điều kiện) là một trong những tính năng Excel khác mà nhiều người có thể thấy hơi đáng sợ. Tuy nhiên, đối với dữ liệu những dữ liệu đặc biệt, nó là một công cụ tuyệt vời.

Ví dụ, bạn có một bảng dữ liệu từ một cuộc khảo sát và muốn xem có bao nhiêu câu trả lời Yes và bao nhiêu câu trả lời No ngay lập tức. Hãy áp dụng các bước sau đây:

1. Chọn các ô chứa câu trả lời Yes/No.

2. Trên tab Home, bấm vào hộp drop-down Conditional Formatting.

3. Chọn Highlight Cells Rules và sau đó là Text That Contains.

4. Nhập từ Yes trong hộp bên trái và chọn định dạng cho từ hộp bên phải.

5. Thực hiện theo các bước tương tự đối với câu trả lời No.

Sau đó, bạn sẽ thấy tất cả các câu trả lời Yes và No được định dạng theo cách đã chọn, do đó rất dễ nhìn.

Nếu có kế hoạch tiếp tục tăng thêm dữ liệu, bạn cũng có thể áp dụng định dạng có điều kiện này cho toàn bộ cột hoặc hàng thay vì chỉ một nhóm các ô. Bằng cách này, dữ liệu trong tương lai sẽ được định dạng tự động ngay khi bạn nhập chúng.

10. Chèn biểu đồ nhanh chóng

Tính năng Charts trong Excel là công cụ tuyệt vời để hiển thị dữ liệu một cách trực quan. Và, bạn có thể chọn từ nhiều loại biểu đồ khác nhau như biểu đồ tròn, đường, cột, v.v…

Sử dụng ví dụ về định dạng có điều kiện cho câu trả lời Yes/No ở trên, bạn có thể chèn biểu đồ chỉ bằng vài cú nhấp chuột.

1. Chọn các ô chứa câu trả lời Yes/No.

2. Trên tab Insert, nhấp vào Recommended Charts. Với tùy chọn này, Excel sẽ lấy dữ liệu và đưa nó vào loại biểu đồ phù hợp nhất.

3. Nếu thích loại biểu đồ Excel tự động chọn, bấm OK và nó sẽ được chèn vào bảng tính.

Đây là cách dễ và nhanh nhất để tạo biểu đồ trong Excel. Quá trình này chỉ mất một phút để hoàn thành. Tuy nhiên, nếu không thích biểu đồ được tạo, bạn có thể nhấp vào tab All Charts trong cửa sổ pop-up và thử nghiệm với các loại biểu đồ khác.

11. Sắp xếp với các bộ lọc

Nếu có một bảng tính chứa nhiều cột dữ liệu, bạn có thể muốn sắp xếp hoặc lọc tất cả thông tin theo một cột nhất định. Mặc dù có một số cách để thực hiện điều này, nhưng sử dụng bộ lọc vẫn là cách nhanh và an toàn nhất.

1. Chọn toàn bộ trang tính bằng cách nhấp vào nút hình tam giác bên cạnh cột đầu tiên ở trên cùng bên trái.

2. Trên tab Home, nhấp vào Sort & Filter.

3. Chọn Filter.

Bước này sẽ làm một mũi tên xuất hiện ở hàng đầu tiên của mỗi cột. Khi bấm vào các mũi tên, người dùng có thể sắp xếp toàn bộ trang tính theo cách đã chọn để sắp xếp cột đó. Nếu đó là một trường ngày, người dùng có thể sắp xếp từ ngày cũ nhất đến mới nhất và nếu đó là một trường văn bản, có thể sắp xếp theo thứ tự bảng chữ cái.

Ngoài ra, người dùng có thể lọc dữ liệu để chỉ xem các mục mình muốn. Khi bấm vào một mũi tên, bạn sẽ thấy các hộp kiểm bên cạnh các mục. Đánh dấu hoặc bỏ đánh dấu các hộp này sẽ lọc dữ liệu, giúp bạn thấy chính xác những gì mình cần.

Điều tuyệt vời khi sử dụng tính năng Filter là nó không ảnh hưởng tiêu cực đến phần còn lại của dữ liệu. Nếu muốn sắp xếp toàn bộ trang tính, Excel sẽ điều chỉnh tất cả các cột. Và nếu muốn lọc, Excel sẽ chỉ ẩn những gì người dùng không muốn thấy.

12. Sử dụng Format Painter

Nếu đã sử dụng các ứng dụng Microsoft Office khác như Word, bạn có thể đã quen thuộc với Format Painter. Trong Excel, công cụ tiện lợi này cho phép bạn áp dụng định dạng tương tự từ một hoặc nhiều ô cho các ô khác chỉ bằng một cú nhấp chuột.

1. Chọn ô, nhóm ô, hàng hoặc cột mà bạn muốn sao chép định dạng từ đó.

2. Trên tab Home, nhấp vào Format Painter.

3. Chọn ô, nhóm ô, hàng hoặc cột mà bạn muốn sao chép định dạng.

Đây là một cách tuyệt vời để nhanh chóng áp dụng định dạng của các ô này cho các ô khác mà không cần thao tác theo cách thủ công.

13. Chuyển đổi và xem các cửa sổ

Có khi nào bạn cần làm việc với nhiều cửa sổ Excel cùng một lúc không? Có thể bạn cần xem lại dữ liệu, so sánh nó hoặc thậm chí sao chép dữ liệu từ workbook này sang workbook khác. Excel cung cấp một bộ tính năng xem hữu ích để trợ giúp cho việc này. Hãy tìm tới tab View để kiểm tra các tùy chọn này.

• Arrange All: Khi nhấp vào nút này, bạn có thể sắp xếp tất cả các workbook đang mở của mình trên một màn hình. Hãy chọn chế độ xem ngang, dọc hoặc xếp tầng theo ý thích.
• View Side by Side: Tùy chọn này cho phép bạn chọn hai workbook mở để đặt cạnh hoặc chồng lên nhau, rất phù hợp khi cần so sánh.
• Switch Windows: Nhấp vào nút này khi bạn mở nhiều workbook Excel cùng một lúc và muốn chuyển đổi nhanh chóng giữa chúng. Không cần phóng to hoặc thu nhỏ tối đa các cửa sổ với tính năng tuyệt vời này.

14. Cài đặt các add-in tiện dụng

Office add-ins tương tự như tiện ích mở rộng trình duyệt ở chỗ chúng là công cụ để nâng cao trải nghiệm, dữ liệu và hiệu quả cho ứng dụng. Và trong Excel, bạn có nhiều add-in hữu ích để lựa chọn tùy theo nhu cầu.

Chọn tab Insert và bấm Store. Sau đó bạn có thể kiểm tra các công cụ theo danh mục. Chẳng hạn, danh mục Productivity chứa các add-in để tự động hóa tài liệu và thư viện template mẫu. Và danh mục Project Management có các add-in cho biểu đồ Gantt và bộ đếm thời hoạt động.

Một số add-in là miễn phí trong khi những add-in khác yêu cầu phải trả tiền, vì vậy hãy chắc chắn kiểm tra các chi tiết trước khi cài đặt.

Trên đây chỉ là vài thủ thuật nhỏ giúp bạn đọc tiết kiệm thời gian khi sử dụng Microsoft Excel. Nếu yêu thích cũng như thường xuyên sử dụng Excel, bạn có thể đóng góp những mẹo tiết kiệm thời gian mà mình đã tìm được và chia sẻ với mọi người ở phần bình luận bên dưới bài viết nhé.

Nguồn: Sưu tầm

Excel là phần mềm được sử dụng rất phổ biến trong công việc, đặc biệt nếu bạn là kế toán của các doanh nghiệp. Trong bài viết sau đây, cunghoctin.com  xin chia sẻ đến các bạn một số hàm thường dùng khi sử dụng excel.

Các hàm trong excel có rất nhiều để xử lý công việc hiệu quả chúng ta cần kết hợp sử dụng chúng một cách linh hoạt.

1. Nhóm hàm thống kê

Đầu tiên là nhóm hàm thống kê, bao gồm:

• SUM

– Tính tổng tất cả các số trong vùng dữ liệu được chọn.

– Công thức: SUM(Number1, Number2, Number3…).

– Number1, Number2, Number3… : là các số cần tính tổng.

Đây là công thức dùng hai dải ô: =SUM(A2:A4,C2:C3) tính tổng các số trong các dải ô A2:A4 và C2:C3. Bạn nhấn Enter để được tổng 39787.

• SUMIF

– Tính tổng giá trị của các ô được chỉ định với điều kiện cụ thể. Học excel kế toán các bạn cần đặc biệt chú ý đến hàm này vì nó thường dùng trong nghiệp vụ kết chuyển cuối tháng khi làm Nhật ký chung, làm các bảng tổng hợp nhập – xuất hàng…

– Công thức: SUMIF(Range, Criteria, Sum_range) = SUMIF(Vùng chứa điều kiện, Điều kiện, Vùng cần tính tổng)

– Trong đó:

+ Range: Dãy số muốn xác định tổng

+ Criteria: điều kiện muốn tính tổng

+ Sum_range: các ô chứa giá trị cần tính tổng

• AVERAGE

– Hàm tính giá trị trung bình của các đối số nhập vào.

– Công thức AVERAGE(Number1, Number2, Number3…)

– Number1, Number2, Number3… là các số nhập vào cần tính giá trị trung bình.

• SUMPRODUCT

– Tính tích của dãy ô, sau đó tính tổng của các tích đó.

– Công thức: SUMPRODUCT(Array1, Array2, Array3…)

– Trong đó: Array1, Array2, Array3… lần lượt là các dãy ô bạn muốn tính tích, sau đó tính tổng các tích.

• COUNT

– Đếm dữ liệu của các ô chứa dữ liệu kiểu số trong dãy được nhập.

– Công thức: COUNT(Value1, Value2, Value3…)

– Trong đó, Value1, Value2, Value3… là dãy hay mảng dữ liệu được chọn.

1.6. COUNTA

– Đếm dữ liệu của tất cả của tất cả các ô chứa dữ liệu.

– Công thức: COUNTA(Value1, Value2, Value3…)

1.7. COUNTIF

– Đếm các ô chứa giá trị thỏa mãn điều kiện cho trước.

– Công thức: COUNTIF(Range, Criteria)

– Trong đó:

+ Range là dãy dữ liệu muốn đếm.

+ Criteria: điều kiện cho các ô được đếm

2. Nhóm hàm tìm kiếm

• VLOOKUP

– Trả về giá trị tìm kiếm theo cột đưa từ bảng tham chiếu với bảng cơ sở dữ liệu theo đúng giá trị dò tìm.

– VLOOKUP thường rất hay được sử dụng nên khi học excel kế toán cần lưu ý đến hàm này.

– Công thức: VLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup]) = VLOOKUP(Giá trị dò tìm, Bảng tham chiếu, Cột cần lấy, X)

– Nếu X = 0 thì kết quả dò tìm chính xác, X = 1 là kết quả dò tìm một cách tương đối.

• HLOOKUP

– Tìm kiếm giá trị như hàm VLOOKUP nhưng bằng cách so sánh với giá trị trong hàng đầu tiên của bảng tham chiếu nhập vào.

– Công thức: HLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup])

3. Nhóm hàm logic

• AND

– Công thức: AND(Logical1, Logical2,…)

– Trong đó, Logical1, Logical2,… là các biểu thức có điều kiện. Các đối số nhập vào phải là giá trị logic hoặc mảng/ tham chiếu có chứa giá trị logic.  

• OR

– Công thức: OR(Logical1, Logical2…)

– Trong đó, Logical1, Logical2… là các biểu thức điều kiện.

• NOT

– Đây là hàm đảo ngược giá trị của đối số nhập vào.

– Công thức: NOT(Logical)

– Trong đó Logical là biểu thức logic hoặc một giá trị

4. Nhóm hàm điều kiện

– Hàm IF là một trong số các hàm trong excel quen thuộc thường được dùng để tính bảng lương cho nhân viên, tính thuế thu nhập cá nhân, thưởng doanh số…

– Công thức: IF(logical-test,[value_if_true],[value_if_true]) = IF(Điều kiện, Giá trị 1, Giá trị 2)

Hàm trả về giá trị 1 nếu điều kiện đúng, trả về giá trị 2 nếu điều kiện sai.

5. Nhóm hàm toán học

• ​ABS

– Hàm ABS giúp lấy giá trị tuyệt đối của một số.

– Công thức: ABS(Number)

– Trong đó, Number là một giá trị số, một biểu thức/ tham chiếu

• PRODUCT

– Tính tích của một dãy số nhập vào.

– Cú pháp: PRODUCT(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là dãy số cần tính tích.

• ​MOD

– Tính giá trị dư của phép chia

– Công thức: MOD(Number, pisor)

– Trong đó, number là số bị chia, pisor là số chia.

Excel là phần mềm rất hữu ích giúp kế toán tiết kiệm thời gian cũng như đem lại hiệu quả công việc cao. Học excel  có nhiều cách học excel online, học tại trung tâm,… Đối với các bạn có ít thời gian thì đăng ký một lớp học trực tuyến của Kyna sẽ là một gợi ý tốt.

Trên là những hàm(Công thức) excel thường dùng. Hướng dẫn sử dụng chi tiết từng Hàm excel mời các bạn xem những bài chi tiết cho từng hàm.

Quy tắc định dạng từng hàng dễ áp ​​dụng như quy tắc từng cột trong Microsoft Excel, nhưng bạn có thể không nhận được kết quả như mong đợi.

Windows: Phạm vi phải đọc

Định dạng có điều kiện trong Microsoft Excel đã có từ lâu, nhưng tôi thấy rằng hầu hết các quy tắc đều đánh giá các cột; nó hiếm khi đơn giản như vậy, nhưng công bằng mà nói rằng hầu hết các quy tắc có điều kiện đều so sánh các giá trị từ cột này sang cột khác. Nó vốn có trong cách chúng ta cấu trúc dữ liệu. Tuy nhiên, đôi khi chúng ta cần so sánh các giá trị từ hàng này sang hàng khác. Tin tốt là việc triển khai quy tắc định dạng so sánh hàng không khó hơn so với so sánh các cột. Trong bài viết này, chúng tôi sẽ sử dụng quy tắc để so sánh các giá trị từ hàng này sang hàng khác. Nhưng mấu chốt là, bất cứ khi nào bạn so sánh giá trị này với giá trị khác, cho dù theo cột hay hàng, bạn có thể không nhận được kết quả như mong đợi.

Tôi đang sử dụng Microsoft 365 trên hệ thống Windows 10 64-bit. (Cố gắng ngừng nâng cấp lên Windows 11 cho đến khi xử lý xong tất cả các lỗi.) Excel Online sẽ hiển thị các định dạng có điều kiện hiện có và cho phép bạn thêm các quy tắc cài sẵn. Tuy nhiên, bạn không thể áp dụng các biểu thức làm quy tắc. Để thuận tiện cho bạn, bạn có thể tải xuống các tệp .xlsx và .xls trình diễn.

Yêu cầu về định dạng

Hãy giữ điều này đơn giản nhất có thể cho bây giờ. Giả sử bạn muốn biết khi nào hoa hồng có vẻ thấp hơn bình thường một chút. Hình A hiển thị một bảng theo dõi đơn giản về doanh số và tiền hoa hồng cho năm nhân viên bằng cách sử dụng đối tượng Bảng, mặc dù, bạn có thể làm như vậy bằng cách sử dụng một phạm vi dữ liệu bình thường. Giả sử bạn muốn biết liệu một tỷ lệ phần trăm có thấp hơn dự kiến ​​hay không khi so sánh với hoa hồng ở hàng trên. Bây giờ, điều này sẽ không thực sự cung cấp cho chúng tôi phân tích mà chúng tôi thực sự cần trong tình huống này, nhưng nó cung cấp cho chúng tôi một chút ví dụ giả định để so sánh các giá trị từ hàng này sang hàng khác. Chúng tôi sẽ giải quyết lỗ hổng hợp lý sau.

Hình A

Để thực hiện yêu cầu đơn giản này, hãy làm nổi bật số tiền hoa hồng thấp hơn 25% so với hàng ở trên:

1. Chọn tập dữ liệu — trong trường hợp này là B4: E14. Nếu bạn chỉ muốn đánh dấu ô hoa hồng, chỉ chọn cột giá trị đó. Nhưng đối với ví dụ này, tôi muốn làm nổi bật toàn bộ bản ghi.
2. Bấm vào tab Trang đầu, bấm Định dạng có Điều kiện trong nhóm Kiểu, rồi chọn Quy tắc Mới từ danh sách thả xuống.
3. Trong ngăn trên cùng, chọn Sử dụng công thức để xác định ô cần định dạng.
4. Trong ngăn dưới cùng, nhập biểu thức =$E4<($E5 - ($E5 *0.25)). Dấu chấm sau các ký tự)) là dấu chấm câu và không phải là một phần của biểu thức.
5. Bấm vào nút Định dạng, bấm vào tab Tô, sau đó chọn một màu đánh dấu. Tôi chọn màu đỏ vì chúng tôi đang tìm kiếm các giá trị thấp hơn.
6. Bấm OK để quay lại ngăn đầu tiên, ngăn này hiển thị quy tắc và định dạng (Hình B).
7. Bấm OK để áp dụng định dạng và quay lại trang tính, được hiển thị trong Hình C. Bây giờ, đừng lo lắng về các ô ở hàng 1. Chúng tôi sẽ sử dụng chúng một chút.

Hình B

Hình C

Với một đánh giá nhanh, tất cả đều có vẻ tốt, ngoại trừ giá trị trong E11. Khi so sánh với các khoản hoa hồng khác, nó có vẻ phù hợp. Vấn đề là giá trị mà nó được so sánh với: $ 500 trong E12. Quy tắc đang hoạt động như bình thường. Hãy nhớ rằng, chúng tôi không so sánh toàn bộ tập hợp các giá trị hoa hồng nói chung. Chúng tôi đang so sánh chúng từ hàng này sang hàng khác. Đúng, nó không hoạt động tốt như chúng ta có thể thích vì nó hơi có chủ đích, vì vậy bạn có thể thấy dễ dàng như thế nào khi nghĩ rằng bạn đã hiểu đúng logic, chỉ để tìm ra một sai sót.

Điều quan trọng cần lưu ý là tham chiếu tuyệt đối cho cột E là điều cần thiết nếu bạn muốn định dạng toàn bộ ô. Tương tự, hàng phải tương đối.

Tại thời điểm này, bạn có thể thắc mắc về các ô được định dạng trong hàng 1. C1 là ô đầu vào. Bằng cách tham chiếu tỷ lệ phần trăm theo cách này, bạn có thể thay đổi tỷ lệ phần trăm một cách nhanh chóng mà không cần sửa đổi quy tắc thực tế.

Cách sửa đổi quy tắc

Bằng cách thêm một ô đầu vào, bạn có thể nhanh chóng cập nhật tỷ lệ phần trăm được sử dụng trong quy tắc có điều kiện. Hãy làm điều đó ngay bây giờ:

1. Bấm vào tab Trang đầu, bấm Định dạng có Điều kiện trong nhóm Kiểu, sau đó chọn Quản lý Quy tắc từ danh sách thả xuống.
2. Từ trình đơn thả xuống Hiển thị Quy tắc Định dạng, hãy chọn Trang tính này.
3. Trong ngăn kết quả, chọn quy tắc và chọn Chỉnh sửa quy tắc trong hàng menu ở trên.
4. Nhấp vào bên trong quy tắc và nhấn F2 để bạn có thể chỉnh sửa dễ dàng.
5. Thay thế giá trị 0,25 bằng một tham chiếu tuyệt đối đến $ C $ 1 (Hình dung).
6. Bấm OK hai lần để áp dụng và quay lại trang tính.

Hình dung

Quy tắc sẽ không hoạt động chính xác cho đến khi bạn nhập giá trị đầu vào vào C1, vì vậy hãy nhập .25 ngay bây giờ. Quy tắc làm nổi bật các bản ghi giống như trước đây. Thay đổi .25 thành .10 để bạn có thể thấy nó cập nhật. Như bạn có thể thấy trong Hình Equy tắc bao gồm một bản ghi nữa, hàng 4. Khi bạn thấp hơn phần trăm, bạn có thể tăng số lượng bản ghi được đánh dấu và ngược lại. Nếu bạn muốn đánh dấu các bản ghi vượt quá bản ghi bên dưới theo một tỷ lệ phần trăm cụ thể, chỉ cần thay đổi < sign in the rule to >. Trên thực tế, bạn có thể sử dụng cả hai quy tắc, nhưng bạn có thể chọn một màu khác, chẳng hạn như màu xanh lá cây, để làm nổi bật mức hoa hồng cao hơn bình thường.

Hình E

Làm thế nào để sửa lỗi logic

Như vậy, quy tắc không hoạt động theo cách chúng ta có thể muốn vì chúng ta đang so sánh hàng này với hàng khác và bỏ qua toàn bộ — ngược lại, Trung bình cộng Uỷ ban. Tôi khuyên bạn nên sử dụng tùy chọn Quản lý quy tắc để xóa quy tắc hiện tại trước khi tiếp tục. Nó không hoàn toàn cần thiết, nhưng sẽ dễ dàng hơn để xem các kết quả mới.

Điều đầu tiên chúng ta phải xem xét là làm thế nào để bao gồm hoa hồng trung bình một cách nhanh chóng và dễ dàng trong quy tắc mới của chúng tôi. Chúng tôi có thể thêm nó vào quy tắc trực tiếp, nhưng chúng tôi sẽ kết thúc với một quy tắc phức tạp. Cách dễ dàng hơn là nhập hàm tính trung bình trong C2

=AVERAGE(Table12[Commission])

Nếu bạn không sử dụng đối tượng Table, hãy nhập

=AVERAGE(E4:E14)

Bây giờ, hãy sửa lỗi logic của chúng ta bằng cách sử dụng một quy tắc mới:

1. Chọn tập dữ liệu và sử dụng Định dạng có điều kiện để thêm quy tắc mới, như bạn đã làm trước đó.
2. Nhập biểu thức =$E4<($C$2-($C$2*$C$1)). Hãy nhớ rằng dấu chấm không được bao gồm trong biểu thức và việc tham chiếu tuyệt đối / tương đối là quan trọng.
3. Nhấp vào nút Định dạng, áp dụng màu tô (tôi đã chọn màu vàng) và nhấp vào OK hai lần để quay lại trang tính.

Như bạn có thể thấy trong Hình F, quy tắc mới này chỉ làm nổi bật hai bản ghi. Logic mới chính xác hơn với nhu cầu của chúng ta. Nhưng chúng tôi đã loại bỏ hoàn toàn tham chiếu từng hàng khi làm như vậy, nhưng điều này vẫn thường xảy ra. Đó là lý do tại sao tôi bắt đầu với một ví dụ giả tạo như vậy. Điều này có thể dễ dàng xảy ra khi so sánh cột với cột.

Hình F

Như trước đây, bạn có thể thay đổi tỷ lệ phần trăm trong C1 để cập nhật toàn bộ chế độ xem. Ngoài ra, nếu bạn muốn thấy hoa hồng vượt quá mức trung bình theo tỷ lệ phần trăm cụ thể (C1), hãy thay đổi < in the rule to >. Cuối cùng, vì chúng ta đang sử dụng một đối tượng Table, nên tất cả đều là động. Hàm AVERAGE () sẽ tự động cập nhật khi bạn sửa đổi, thêm hoặc xóa các bản ghi. Nếu bạn đang sử dụng phạm vi dữ liệu bình thường, điều đó sẽ không xảy ra.

Bạn có thể tự hỏi tại sao tôi bắt đầu với một ví dụ từng hàng chỉ để loại bỏ hoàn toàn nó. Đầu tiên, tôi muốn có một ví dụ siêu dễ để giới thiệu ý tưởng đánh giá quy tắc định dạng theo hàng. Như tôi đã đề cập hai lần, việc tham khảo là rất quan trọng. Ngoài ra, đối với những quy tắc đơn giản nhất, đôi khi chúng không đầy đủ và bạn cần phải suy nghĩ lại về nỗ lực đầu tiên của mình. Không có gì sai với điều đó; Tôi làm nó suốt.

Microsoft Excel là một chương trình ứng dụng thuộc bộ Microsoft Office, là một công cụ mạnh mẽ để thực hiện các bảng tính chuyên nghiệp. Hiện nay, Excel được sử dụng rộng rãi trong môi trường doanh nghiệp nhằm phục vụ các công việc tính toán, thống kê dữ liệu. Trong bài viết này, mình sẽ hướng dẫn tự học Excel cơ bản qua các video và ví dụ minh hoạ.

Nội dung chính:

1. Giới thiệu chung về hàm
2. Hàm xử lý ngày tháng
3. Hàm xử lý chuỗi
4. Hàm số học
5. Hàm dò tìm
6. Hàm thống kê
7. Hàm luận lý
8. Download Full bộ tài liệu

1. Giới thiệu chung về hàm:

Cú pháp tổng quát: =TÊN_HÀM(các tham số)

• Các tham số cách nhau bởi dấu phẩy (có thể thay đổi dấu ngăn cách giữa các tham số bởi ký hiệu khác, các bạn xem tại đây)
• Tham số có thể là công thức, địa chỉ ô, địa chỉ vùng, hoặc giá trị hằng; tuy vậy bao giờ cũng phải thỏa mãn kiểu của từng tham số. Có tham số bắt buộc và có tham số tùy chọn. Hàm luôn trả về một giá trị.

Excel được dùng để làm gì?

Các chương trình bảng tính điện tử ban đầu được dựa trên bảng tính giấy và thường được sử dụng cho các công việc kế toán. Như vậy, bố cục của bảng tính trong Microsoft Excel cũng có nét tương đồng với bảng tính giấy. Dữ liệu liên quan sẽ được lưu giữ trong các bảng – là một tập hợp các hộp chữ nhật nhỏ hoặc các ô được sắp xếp thành các hàng và các cột.

Tất cả các phiên bản Excel và các chương trình bảng tính khác có thể lưu trữ một số trang bảng tính vào trong một tệp chung và lưu giữ trực tiếp trên máy tính. Tệp dữ liệu đó thường được gọi là sổ làm việc (workbook) và mỗi trang trong sổ làm việc là một bảng tính riêng biệt (worksheet).

Giới thiệu các ô và các cột trong bảng tính Excel

Khi bạn nhìn vào màn hình Excel – hay bất kỳ màn hình bảng tính nào khác – bạn sẽ thấy một bảng tính hình chữ nhật được tạo thành bởi các hàng (Rows) và các cột (Columns).

Trong các phiên bản mới của Excel, mỗi bảng tính chứa khoảng một triệu hàng và 16.000 cột số liệu, do đó bạn cần phải có sơ đồ địa chỉ để theo dõi vị trí của các dữ liệu.

Các hàng ngang được xác định bằng các con số (1, 2, 3), còn các cột dọc thì được xác định bởi các chữ cái trong hệ thống bảng chữ cái (A, B, C). Đối với các cột lớn hơn 26, chúng sẽ được xác định bằng hai hay nhiều chữ cái, ví dụ như AA, AB, AC hoặc AAA, AAB…

Điểm giao nhau giữa một cột và một hàng là một hình hộp chữ nhật nhỏ và được gọi là một ô. Ô là đơn vị cơ bản để lưu trữ dữ liệu trong trang tính, và bởi vì một trang tính có chứa đến hàng triệu ô như vậy nên mỗi ô sẽ được xác định bởi tham chiếu ô của nó.

Tham chiếu ô là sự kết hợp giữa ký tự cột và số hàng, ví dụ như A3, B6 và AA345. Trong các tham chiếu ô, ký tự cột luôn là thứ được liệt kê đầu tiên.

Kiểu dữ liệu, công thức và hàm trong Excel

Các kiểu dữ liệu mà một ô có thể chứa bao gồm:

• Số
• Văn bản
• Ngày và thời gian
• Giá trị Boolean
• Công thức

Các công thức được sử dụng để tính toán, thường kết hợp dữ liệu chứa trong các ô khác. Những ô dữ liệu này nằm ở các Worksheets khác nhau hay thậm chí là ở trong các Workbooks khác nhau.

Tạo một công thức được bắt đầu bằng cách nhập dấu “=” vào ô – nơi bạn muốn câu trả lời được hiển thị. Công thức có thể bao gồm các tham chiếu ô đến vị trí của dữ liệu và một hay nhiều hàm của bảng tính.

Các hàm trong Excel và các bảng tính điện tử khác là những công thức tích hợp được thiết kế để đơn giản hóa việc thực hiện một loạt các phép tính – từ các thao tác phổ biến như nhập số liệu ngày giờ cho đến các thao tác phức tạp hơn như tìm kiếm những thông tin cụ thể nằm trong các bảng dữ liệu lớn.

Microsoft Excel và dữ liệu tài chính

Bảng tính thường được sử dụng để lưu trữ dữ liệu tài chính. Các công thức và hàm được sử dụng trên loại dữ liệu này bao gồm:

• Thực hiện các phép tính cơ bản như tính tổng số liệu của các hàng và hoặc các cột.
• Tìm các giá trị xác định lãi hoặc lỗ.
• Tính toán kế hoạch trả nợ cho các khoản vay hoặc thế chấp.
• Tìm giá trị trung bình, tối đa, tối thiểu và các giá trị thống kê khác trong một phạm vi dữ liệu được chỉ định.
• Thực hiện các thao tác phân tích dữ liệu, tại đó các biến số được sửa đổi cùng một lúc để xem nó ảnh hưởng đến các dữ liệu khác như chi phí và lợi nhuận như thế nào.

Công dụng khác của Excel

Những công dụng khác mà Excel có thể được sử dụng phổ biến bao gồm:

• Vẽ biểu đồ hoặc biểu đồ dữ liệu để hỗ trợ người dùng xác định xu hướng dữ liệu.
• Định dạng dữ liệu thích hợp, giúp cho các dữ liệu quan trọng trở nên dễ tìm kiếm và dễ nắm bắt nội dung.
• In dữ liệu và biểu đồ để sử dụng trong các báo cáo.
• Sắp xếp và lọc dữ liệu để tìm kiếm thông tin cụ thể.
• Liên kết dữ liệu bảng tính và biểu đồ để sử dụng trong các chương trình khác như Microsoft PowerPoint và Word.
• Nhập dữ liệu vào các chương trình quản lý cơ sở dữ liệu khác để phân tích.

Giải pháp thay thế Excel

Các chương trình bảng tính hiện tại khác có thể sử dụng thay thế, bao gồm:

• Google Sheet: Một chương trình bảng tính dựa trên nền tảng web hoàn toàn miễn phí.
• Excel Online: Phiên bản ứng dụng Excel thu nhỏ, dựa trên web và hoàn toàn miễn phí.
• OpenOffice Calc: Một chương trình bảng tính có thể tải xuống miễn phí.
• LibreOffice Calc: Một phần mềm tương tự như Microsoft Excel nhưng miễn phí.