Đề Xuất 2/2023 # 14 Mẹo Tiết Kiệm Thời Gian Khi Dùng Microsoft Excel # Top 4 Like | Beiqthatgioi.com

Đề Xuất 2/2023 # 14 Mẹo Tiết Kiệm Thời Gian Khi Dùng Microsoft Excel # Top 4 Like

Cập nhật nội dung chi tiết về 14 Mẹo Tiết Kiệm Thời Gian Khi Dùng Microsoft Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

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.

Một mẫu hóa đơn cơ bản được tạo trong Excel

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.

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.

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 đề.

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.

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.

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.

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

Bạn có thể tham khảo nhiều phím tắt Excel hơn trong bài viết: Tổng hợp phím tắt cho Microsoft Excel 2016-P1

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.

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.

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.

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.

Công Thức Trong Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian

Trong MS Excel, công thức 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.

Tất cả các công thức Excel bắt đầu với một dấu bằng (=).

Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm. Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:

Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5

Sử dụng hàm SUM: = SUM (B1: B5)

Nhấn phím Enter để hoàn thành công thức. Làm xong!

Khi 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: ·

Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·

Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel của bạn, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. Ví dụ, để tổng hợp giá trị trong tất cả các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·

Tên – được xác định cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name). ·

Các hàm – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·

Toán tử – các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được thực hiện.

Để 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ố học – để thực hiện các phép toán cơ bản.

So sánh (hợp lý) – để so sánh các giá trị.

Kết nối – để kết hợp các giá trị văn bản vào một chuỗi duy nhất.

Tham chiếu – để tạo ra các cơn thịnh nộ và các đối số riêng biệt trong các hàm của Excel.

Sử dụng toán tử số học trong công thức Excel

Cá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.

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

Trong 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:

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ản

Toá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:

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).

Các công thức Excel đơn giản chỉ thực hiện một thao tác toán học, ví dụ = 10 * 5 hoặc = SUM (A1: A10)

Các công thức Excel phức tạp (tiên tiến) bao gồm nhiều tính toán, ví dụ = 10 * 5 + 20 hoặc = SUM (A1: A10) / 2

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 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 Excel

Khi 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à:

Phép tính trong dấu ngoặc đơn

Lũy thừa

Nhân và chia, tùy điều kiện nào đến trước trong một công thức

Cộng và trừ, tùy điều kiện nào đến trước trong một công thức

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ông thức: = $B2*$D2 + $B2 * $D2 * $C2

Lần nhân thứ nhất: $B2 * $D2 (giá * số lượng. = Số tiền)

Phép nhân số thứ 2 và thứ 3: $B2*$D2*$C2 (giá * số lượng * VAT% = số VAT)

Phép cộng: Số tiền + Số tiền thuế VAT = tổng số

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ý.

Trong 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.

Cá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:

Mặ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:

Cột tuyệt đối và hàng tương đối ($A1) – dấu hiệu $ ở phía trước của cột chữ cái khóa tham chiếu đến cột được chỉ định, vì vậy cột không bao giờ thay đổi. Tham chiếu hàng tương đối, không có dấu đô la, thay đổi tùy thuộc vào hàng mà công thức được sao chép.

Cột tương đối và hàng tuyệt đối (A$1) – tham chiếu của hàng được khóa bởi $ không thay đổi, và tham chiếu của cột đó thay đổi.

Cá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 🙂

Để chuyển đổi giữa các tham chiếu tuyệt đối, tương đối và hỗn hợp trong một công thức, sử dụng phím F4.

Để chỉnh sửa một công thức, nhấn F2, hoặc nhấp đúp vào một ô, hoặc nhấp vào thanh công thức.

Để gỡ lỗi công thức trong Excel, hãy chọn một phần công thức và nhấn F9. Điều này sẽ cho phép bạn xem các giá trị thực tế đằng sau các tham chiếu ô.

Để sao chép công thức tới tất cả các ô trong một cột, nhập công thức vào ô đầu tiên, chọn ô đó và di con trỏ qua hình vuông nhỏ ở góc dưới cùng bên phải cho đến khi nó chuyển thành chữ thập đỏ đen (được gọi là Fill handle). Nhấp đúp vào chữ thập đó và bạn sẽ nhận được công thức được sao chép qua toàn bộ cột.

Để chuyển đổi các công thức sang các giá trị, hãy chọn tất cả các ô với các công thức mà bạn muốn chuyển đổi, nhấn Ctrl + C để sao chép các công thức đó, sau đó nhấn Shift + F10, sau đó nhấn V, và sau đó nhấn Enter. Shift + F10 + V là phím tắt cho các giá trị đặc biệt Paste của Excel. Nếu bạn không chắc chắn sẽ nhớ lối tắt này, sau đó chỉ cần nhấn phím tắt thông thường để dán Ctrl + V, nhấp vào mũi tên nhỏ ở bên phải của nút Paste để mở danh sách thả xuống và chọn Paste Values.

Để 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 aka DT Nguyen

Cách Tính Thời Gian Trong Excel

Trong bài viết này, Học Excel văn phòng sẽ hướng dẫn những công thức hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ…

Cách tính chênh lệch thời gian trong Excel

Công thức 1. Trừ thời gian

Như bạn đã biết, thời gian trong Excel thường được định dạng số thập phân để trông giống thời gian. Vì chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.

Công thức đơn giản nhất để tính chênh lệch thời gian là:

Tùy vào cấu trúc dữ liệu, chênh lệch thời gian thực tế có thể có nhiều dạng khác nhau, ví dụ:

=A2-B2

Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2

=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)

Tính toán chênh lệch thời gian giữa hai thời gian cụ thể

=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))

Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian

Trong hệ thống Excel, thời gian biểu thị ở phần thập phân, bạn sẽ có kết quả như sau:

Để áp dụng định dạng thời gian, nhấn để mở hộp thoại Format Cells, chọn từ Category list và gõ mã thời gian vào hộp .

Bây giờ, để xem công thức tính chênh lệch thời gian và mã thời gian hoạt động như thế nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn có thể sao chép công thức vào cột C và E:

=$B2-$A2

Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời gian trong mỗi cột:

Công thức 2. Tính chênh lệch thời gian bằng hàm TEXT

Một kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT .

Tính toán chênh lệch giờ giữa hai thời gian: =TEXT(B2-A2, “h”)

Tính toán chênh lệch giờ và phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)

Tính toán chênh lệch giờ, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)

Kết quả của hàm TEXT luôn là văn bản. Các giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.

Nếu kết quả là một số âm, công thức TEXT sẽ hiển thị lỗi #VALUE!.

Công thức 3. Tính giờ, phút, giây giữa hai thời gian

Để hiển thị chênh lệch giữa hai thời gian bằng một đơn vị (giờ, phút, giây), bạn cần thực hiện các phép tính sau.

Phép tính giờ giữa hai thời gian =(End time – Start time) * 24

Giả sử thời gian bắt đầu trong ô A2 và thời gian kết thúc trong ô B2, bạn hãy sử dụng phép tính đơn giản là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24.

=(B2-A2) * 24

Để có số Complete hours, sử dụng hàm INT để làm tròn đến số nguyên hần nhất:

=INT((B2-A2) * 24) Tổng số phút giữa hai thời gian:

Để tính số phút giữa hai thời gian, nhân chênh lệch thời gian với 1440

=(End time – Start time) * 1440

Công thức có thể cho kết quả âm hoặc dương, kết quả âm xảy ra khi thời gian kết thúc lớn hơn thời gian bắt đầu như hàng 5:

=(B2-A2)*1440 Tổng số giây giữa hai thời gian:

Để tính số giây giữa hai thời gian, nhân chênh lệch thời gian với 86400

=(End time – Start time) * 86400

Trong ví dụ, công thức sẽ như sau:

Công thức 4. Tính chênh lệch thời gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị khác

Để tính chênh lệch giữa hai thời gian trong cùng một đơn vị, bỏ qua các đơn vị thời gian khác, hãy sử dụng một trong các hàm sau:

Chênh lệch giờ, bỏ qua phút và giây:

=HOUR(B2-A2)

Chênh lệch phút, bỏ qua giờ và giây:

=MINUTE(B2-A2)

Chênh lệch giây, bỏ qua giờ và phút:

=SECOND(B2-A2)

Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.

Công thức 5. Tính thời gian trôi qua kể từ thời gian bắt đầu đến bây giờ

Để tính thời gian đã trôi qua, bạn có thể sử dụng hàm Now và Time. Trường hợp khoảng thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm để lấy kết quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời gian bắt đầu.

Giả sử ô A2 chứa thời điểm bắt đầu, công thức cho kết quả sau với cột B được định dạng thời gian phù hợp, trong ví dụ này nó có dạng

Trong trường hợp khoảng thời gian trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời gian sau, ví dụ d ‘days’ h:mm:ss như trong hình sau:

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2

Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”

Bạn sử dụng các để lấy kết quả thời gian tương tứng và hàm để tính chênh lệch ngày.Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :

Khi tính toán chênhh lệch thờ gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị kết quả âm trong Excel.

Nếu không muốn sử dụng phương pháp 1, bạn có thể sử dụng công thức sau:

Cộng/trừ thời gian trong Excel

Về cơ bản, có 2 cách cộng/trừ thời gian trong Excel:

Sử dụng hàm TIME

Sử dụng phép toán dựa trên số giờ (24), số phút (1440), số giây (86400) trong một ngày.

Tham khảo ngay: Giáo trình Excel 2020

Cộng/trừ giờ vào thời gian cụ thể

Hàm thời gian để cộng thêm dưới 24 giờ =Start time + TIME(N hours, 0, 0)

Ví dụ, trong ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, công thức sẽ là:

=A2 + TIME(2, 0, 0)

Chú ý. Nếu bạn thử cộng hơn 23 giờ bằng hàm số giờ được chỉ định sẽ chia cho 24 và phần nguyên sẽ thêm vào thời gian bắt đầu. Ví dụ, nếu bạn cộng thêm 25 giờ vào 6/2/20155 10:00 AM (ô A4) bằng công thức =A4 + TIME(25, 0, 0), kết quả sẽ là: 06/02/2015 11:00, cụ thể là A4 + 1 giờ

Công thức để cộng bất kỳ số giờ nào (dưới/trên 24 giờ)

Công thức sau không có giới hạn về số giờ bạn muốn cộng thêm:

=Start time + (N hours/24)

Ví dụ, để cộng thêm 28 giờ vào ô A2, nhập công thức sau:

Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các công thức sau:

=A2-(3/24) =A2-TIME(3,0,0)

Nếu bạn trừ số giờ lớn hơn 23, sử dụng công thức đầu tiên.

Cách cộng/trừ phút vào thời gian cụ thể

Cộng/trừ dưới 60 phút

Sử dụng hàm TIME để cộng trừ số phút bạn muốn vào tham số thứ 2:

=Start time + TIME(0, N minutes, 0)

Đây là một vài công thức thực tế để tính số phút trong Excel:

Công 20 phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)

Cộng trừ trên 60 phút

Trong phép tính của bạn, chia số phút cho 1440 – số phút trong một ngày và thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, thay dấu + thành dấu -. Ví dụ :

Cộng thêm 200 phút: =A2 + (200/1440)

Trừ đi 300 phút: =A2 -(300/1440)

Để cộng thêm số giây ít hơn 60 vào thời gian cụ thể, sử dụng hàm TIME:

=Start time + TIME(0, 0, N seconds)

Để cộng thêm số giây lớn hơn 59, sử dụng công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng công thức tương tự nhưng thay dấu + thành dấu -.

Trên trang tính Excel, công thức trông như sau:

Phép cộng Excel thường sử dụng hàm SUM, và áp dụng định dạng phù hợp cho kết quả.

Giả sử bạn có một vài thời gian trong cột B và bạn muốn cộng chúng với nhau. Bạn viết công thức như sau , kết quả hiển thị theo định dạng mặc định như

Trong một vài trường hợp, định dạng thời gian mặc định có thể ổn, nhưng đôi khi bạn muốn hiển thị tổng thời gian dưới dạng phút và giây, hoặc chỉ hiển thị giây. Khi đó, bạn không cần phép tính nào cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa công thức SUM.

Nhấp phải vào ô tính và chọn Format Cells từ danh sách tùy chọn, nhấn để mở hộp thoại Format Cells. Chọn từ Category list và gõ một trong các định dạng sau vào hộp :

Hiển thị thời gian dưới dạng phút và giây: [m]:ss

Hiển thị thời gian dưới dạng giây: [ss]

Kết quả như sau:

=SUM($B$2:$B$4)

Chú ý. Định dạng thời gian chỉ áp dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là bạn lấy giá trị nhỏ hơn trừ cho giá trị lớn hơn, kết quả sẽ hiển thị .

Hướng Dẫn Sử Dùng Hàm Thời Gian Year, Date, Month Trong Excel Có Bài Tập

Hàm YEAR để lấy giá trị Năm từ một đối tượng thời gian

Để lấy được giá trị Số năm từ một đối tượng thời gian thì chúng ta có thể sử dụng hàm YEAR trong Excel.

Cấu trúc của hàm YEAR

=YEAR(serial_number)

Trong đó serial_number là một giá trị về thời gian, là dữ liệu dạng số, không phải dạng Text

Như vậy chúng ta thấy: Dù giá trị thời gian viết dưới định dạng nào thì hàm YEAR vẫn lấy đúng số Năm trong giá trị thời gian đó.

Ví dụ: Để xác định năm hiện tại là năm nào thì chúng ta kết hợp hàm YEAR với hàm TODAY như sau:

=YEAR(TODAY())

Kết quả của hàm YEAR ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm MONTH để lấy giá trị Tháng từ một đối tượng thời gian

Để lấy được giá trị Số Tháng từ một đối tượng thời gian thì chúng ta có thể sử dụng hàm MONTH trong Excel.

Cấu trúc của hàm MONTH

=MONTH(serial_number)

Trong đó serial_number là một giá trị về thời gian, là dữ liệu dạng số, không phải dạng Text

Xét một số ví dụ áp dụng hàm tách số tháng từ 1 giá trị thời gian như sau:

Kết quả của hàm MONTH ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm DAY để lấy giá trị Ngày từ một đối tượng thời gian

Tương tự như hàm YEAR, hàm MONTH ở trên, hàm DAY sẽ giúp chúng ta xác định số Ngày từ một giá trị thời gian trong Excel.

Cấu trúc hàm DAY

=DAY(serial_number)

serial_number là một giá trị thời gian, dữ liệu dạng số.

Kết quả của hàm DAY ra một giá trị có loại dữ liệu là dạng Number, không phải dạng thời gian.

Hàm DATE để tạo ra 1 giá trị thời gian từ việc ghép các đối tượng Năm, Tháng, Ngày

Để tạo ra một giá trị thời gian thì chúng ta phải dùng hàm DATE. Hàm Date giúp xác định ra một giá trị thời gian có đầy đủ Ngày, Tháng, Năm và được viết đúng định dạng chuẩn là Thời gian.

Cấu trúc của hàm DATE

=DATE(year, month, day)

Một số ví dụ về việc ứng dụng hàm Date để tạo ra giá trị biểu diễn thời gian:

Trong kết quả tại D4 và D5 có sử dụng hàm IF để biện luận giá trị năm. Nếu số năm nhỏ hơn hoặc bằng 18 (năm hiện tại là 2018) thì sẽ coi năm đó thuộc năm 2000 trở đi. Còn không phải thì sẽ tính cho những năm thuộc 1900 tới trước 2000

Kết quả tại D3 có sử dụng hàm TEXT kết hợp hàm DATE để thay đổi cách định dạng giá trị ngày theo ý muốn.

Để có thể làm tốt bài tập trên, các bạn cần tìm hiểu thêm một số kiến thức về các hàm: VLOOKUP, CHOOSE, SUMIFS, WEEKDAY

Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Bạn đang đọc nội dung bài viết 14 Mẹo Tiết Kiệm Thời Gian Khi Dùng Microsoft Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!