Chia Sẻ Và Bảo Quản Tài Liệu
Thiết lập và lựa chọn hiệu chỉnh dữ liệu
Trong phần này, chúng ta sẽ tạo một bảng tính mẫu, thiết lập các thuộc tính cần thiết, và hướng dẫn cách nhập cũng như xuất dữ liệu thông qua XML và bản đồ XML.
Lưu một bảng tính mẫu
Một bảng tính mẫu là một tệp tin mới chứa các tiêu đề cột và hàng, cùng với các công thức, định dạng và các thành phần khác đã được thiết lập sẵn.
Để thiết kế một bảng tính mẫu hiệu quả, bước đầu tiên là thiết lập bảng tính với các công thức, định dạng và yếu tố cần thiết Để bảo vệ mẫu bảng tính khỏi sự thay đổi, người dùng có thể tạo mật khẩu cho mẫu Ngoài ra, cũng có thể thiết lập mật khẩu để hạn chế quyền đọc và truy cập vào các tập tin, cũng như yêu cầu mật khẩu khi người dùng muốn thay đổi mẫu.
Khi lưu bảng tính mẫu trong Excel, mặc định sẽ được lưu tại thư mục: Users / UserName / AppData / Roaming / Microsoft / Template Việc lưu mẫu tại vị trí này giúp Excel hiển thị chúng trong hộp thoại chọn mẫu Ngoài ra, người dùng cũng có thể lưu mẫu bảng tính trên mạng chia sẻ để nhóm người dùng có quyền truy cập dễ dàng.
Các bước lưu bảng tính mẫu
Bước 1 Chọn File Save As
Trong hộp thoại "Save As", chọn loại tệp là "Excel Template (*.xltx)" để lưu mẫu Excel Nếu tệp chứa Macro, hãy chọn "Excel Macro-Enabled Template (*.xltm)" Để sử dụng mẫu với các phiên bản Excel cũ hơn, chọn "Excel 97-2003 Template (*.xlt)".
Bước 3 Chọn Tools General Options
Bước 4 Trong hộp thoại Options nhập mật khẩu để mở và một mật khẩu riêng để kiểm soát việc sửa đổi
Bước 5 Sau đó chọn Ok Chọn Save As trong hộp thoại Save As
Thiết lập thuộc tính bảng tính
Khi tạo, nhập liệu và chỉnh sửa bảng tính trong Excel, chương trình sẽ lưu lại các thuộc tính như kích thước tệp, thời gian tạo và sửa đổi cuối cùng, cùng tên tác giả Các thuộc tính này thường ở chế độ chỉ đọc (Read Only), nhưng người dùng có thể thiết lập và chỉnh sửa các thuộc tính nâng cao và tùy chọn khác theo nhu cầu.
We can familiarize ourselves with the basic features offered by Excel To set advanced properties for a spreadsheet, navigate to the File tab, select Info, and then choose Properties followed by Advanced Properties.
Sau đó ta thiết lập các thuộc tính trong hộp thoại Advanced Properties:
Các Tab General, Statistics và Contents cung cấp thông tin chi tiết về bảng tính, bao gồm Tiêu đề và Môn học được thiết lập trong Tab Summary Tab Custom cho phép người dùng quản lý các thuộc tính nâng cao như Check By, Client, Project và Typist, với tùy chọn để giữ mặc định hoặc tự thiết lập theo danh sách có sẵn Sau khi chọn, người dùng chỉ cần Click Add Để thay đổi tên, loại hoặc giá trị của thuộc tính, hãy Click chọn Modify.
Thiết lập bảo mật và chia sẻ tài liệu
Trong nhiều trường hợp, chúng ta có thể chia sẻ bảng tính và cho phép người dùng chỉnh sửa theo ý muốn, như thay đổi định dạng, thêm hoặc xóa dữ liệu, và chèn bảng Tuy nhiên, hầu hết chúng ta đều muốn bảo vệ bảng tính, đặc biệt là những bảng tính quan trọng Do đó, việc bảo vệ bảng tính là rất cần thiết.
- Cách 1: Tab File Info Protect Workbook
- Cách 2: Vào Tab Review group Changes
Chúng ta có thể thiết lập bảo vệ bảng tính và một số Sheet quan trong bằng cách:
Bảo vệ cấu trúc bảng tính là một tính năng quan trọng giúp người dùng kiểm soát các worksheets, kích thước và vị trí Khi cấu trúc bảng tính được bảo vệ (Protect Structure), người dùng không thể thực hiện các thao tác như chèn, xóa, đổi tên hoặc hiển thị các worksheet bị ẩn Đồng thời, khi cửa sổ bảng tính được bảo vệ (Protect Windows), người dùng cũng không thể thay đổi kích thước hoặc vị trí của cửa sổ, đảm bảo tính toàn vẹn cho bảng tính.
To secure specific sheets in a spreadsheet with multiple worksheets, use the "Protect Worksheet" feature In the Protect Sheet dialog box, enter a password and select various permissions for users under the "Allow all users of this worksheet to:" section.
Bạn có thể thiết lập một khu vực trong bảng tính được bảo vệ, cho phép người dùng chỉnh sửa thông qua chức năng "Cho phép người dùng chỉnh sửa".
Ranges” trong Maintab Review group Changes
- Trong hộp thoại Allow User to Edit Ranges chọn New :
Thiết lập mật khẩu cho tập tin Bước 1 Mở tập tin muốn tạo mật khẩu Tab File Info
Protect Workbook Encrypt with Password
Bước 3 Nhập lại mật khẩu vừa đặt
Bước 4 Click Ok hoàn thành
Chia sẻ và Bảo quản tài liệu
Bước 1 Chọn Tab Review Group Changes Share
Bước 2 Trong cửa sổ Share Workbook Tab Editing Allow changes by more than one user at the same time This also allows workbook merging
Bước 3 Tab Advanced cho phép bạn thiết lập một số tính năng cho tập tin được chia sẻ
Bước 4 Click Ok để hoàn thành
Theo dõi sự thay đổi của tập tin
Sau khi chia sẻ tập tin với người khác hoặc nhóm làm việc, bạn có thể theo dõi các thay đổi của tập tin để xem những chỉnh sửa, bổ sung hoặc thay đổi dữ liệu nào đã được thực hiện Để thực hiện điều này, bạn cần thiết kế quy trình theo các bước cụ thể.
Bước 1 Chọn Tab Review group Changes Track Changes
Bước 2 Trong hộp thoại Highlight Changes lựa chọn những điều kiện When , Who, Where thiết hợp theo yêu cầu của bạn
Bước 3 Click Ok để hoàn thành
Với thiết lập này thì những vị trí mà dữ liệu thay đổi sẽ được làm dấu với
Xem chi tiết những thay đổi của tập tin Bước 1 Trong Tab Review group Changes Track Changes
Bước 2 Trong hộp thoại Select Changes to Accept or Reject bạn sẽ lựa chọn xem chi tiết thay đổi của dữ liệu khi nào , của ai, hay vùng nào,
Trong bước 3, hộp thoại "Chấp nhận hoặc từ chối thay đổi" sẽ hiển thị chi tiết các dữ liệu đã được thay đổi, cho phép bạn quyết định chấp nhận hoặc không những thay đổi này.
Thao Tác Với Công Thức Và Hàm
Kiểm tra công thức
Chức năng Kiểm tra công thức trong Excel sử dụng các tracer, là những mũi tên hai đầu, để chỉ ra các ô liên quan trong công thức.
Bạn có thể sử dụng các tracer để tìm ra ba loại ô như sau:
Precedents là các ô được tham chiếu trong công thức, có thể là trực tiếp hoặc gián tiếp Ví dụ, ô B4 chứa công thức và ô B2 là precedent trực tiếp của B4 Nếu ô B2 chứa công thức /2, thì ô A2 trở thành precedent trực tiếp của B2 và cũng là precedent gián tiếp của B4.
Dependents là các ô được tham chiếu trực tiếp hoặc gián tiếp bởi một công thức trong ô khác Chẳng hạn, ô B2 là dependent trực tiếp của ô A2, trong khi ô B4 là dependent gián tiếp của ô A2.
Các ô chứa giá trị lỗi có thể được tham chiếu trực tiếp hoặc gián tiếp trong công thức, dẫn đến việc phát sinh lỗi tương tự trong công thức đó.
Hình 2.2.2: Minh họa một bảng tính với ba ví dụ của các mũi tên tracer
- Ô B4 chứa công thức , và B2 chứa công thức /2 Những cái mũi tên (màu xanh) chỉ ra các precedent(trực tiếp và gián tiếp) của B4
- Ô D4 chứa công thức và D2 chứa công thức 0, gây ra lỗi #DIV/0!, do đó, lỗi này cũng xuất hiện ở ô D4 Mũi tên (màu đỏ) chỉ ra nguồn gốc của lỗi
Ô G4 sử dụng công thức =Sheet2!A1, và khi ô này tham chiếu đến các ô khác trên một trang tính khác, Excel sẽ hiển thị một mũi tên đứt khúc kèm theo biểu tượng bảng tính ở cuối mũi tên.
Truy tìm các Precedent của ô Bước 1 Chọn ô chứa công thức mà bạn muốn truy tìm precedent của nó
Bước 2 Chọn Tab Formulas Group Fomula Auditing Trace Precedents Excel sẽ thêm một mũi tên vào mỗi cái precedent trực tiếp
Bước 3 Tiếp tục lập lại bước 2 để thêm các cấp precedent (nếu có)
Truy tìm các Dependent của ô Bước 1 Chọn ô chứa công thức mà bạn muốn truy tìm Dependent của nó
Bước 2 Chọn Tab Formulas Group Fomula Auditing Trace
Bước 3 Tiếp tục lập lại bước 2 để thêm các cấp Dependent (nếu có)
Gỡ bỏ những mũi tên Tracer
Chọn Tab Formulas Group Fomula Auditing Remove Arrows
Để gỡ bỏ các mũi tên precedent mỗi lần một cấp, chọn Remove Precedent Arrows
Để gỡ bỏ các mũi tên dependent mỗi lần một cấp, và chọn Remove Dependent Arrows
Các thông báo lỗi thường gặp
Khi Excel không thể tính toán một công thức, chương trình sẽ hiển thị lỗi bắt đầu bằng dấu # Dưới đây là danh sách các thông báo lỗi thường gặp trong Excel.
Thông báo lỗi Nguyên nhân
#DIV/0! Trong công thức có phép tính chia cho 0
#N/A Công thức tham chiếu đến ô có giá trị không tìm thấy hoặc nhập hàm thiếu đối số
#NAME? Trong công thức có tên hàm hoặc tên ô sai
#NULL Xảy ra khi xác định giao giữa 2 vùng nhưng vùng giao nhau là rỗng
#NUM! Dữ liệu số bị sai
#REF! Xảy ra khi trong công thức có tham chiếu đến một địa chỉ không hợp lệ
#VALUE! Trong công thức có các toán hạng và toán tử sai kiểu
Sửa chữa lỗi công thức
Khi bạn thiếu dấu ngoặc đơn hoặc đặt sai vị trí trong công thức Excel, chương trình sẽ hiển thị hộp thoại cảnh báo Nếu công thức được gợi ý trong hộp thoại đúng với ý định của bạn, hãy nhấn Yes để Excel tự động sửa lại; nếu không, nhấn No và tự chỉnh sửa công thức theo ý muốn.
Sử dụng chức năng kiểm tra lỗi công thức
Khi sử dụng Microsoft Word, bạn có thể nhận thấy những đường gợn sóng màu xanh xuất hiện dưới các từ hoặc cụm từ không đúng theo quy tắc ngữ pháp Công cụ kiểm tra ngữ pháp hoạt động bằng cách áp dụng một bộ quy tắc để phân tích văn bản của bạn Trong quá trình bạn nhập liệu, nó sẽ theo dõi từng câu chữ và thông báo bằng các đường gợn sóng khi phát hiện lỗi ngữ pháp hoặc cú pháp.
Excel có chức năng kiểm tra lỗi công thức tương tự như trình kiểm tra ngữ pháp Tính năng này hoạt động âm thầm, giám sát các công thức của bạn bằng cách sử dụng một bộ quy tắc để phát hiện lỗi Khi phát hiện vấn đề, nó sẽ hiển thị một dấu hiệu báo lỗi dưới dạng tam giác màu xanh ở góc trái phía trên ô chứa công thức.
Khi bạn chọn ô có dấu hiệu báo lỗi trong Excel, một smart tag sẽ xuất hiện bên cạnh, và khi di chuột lên biểu tượng này, một thông báo mô tả lỗi sẽ hiện ra Bên góc phải của biểu tượng có nút nhấn cho phép bạn mở danh sách các cách xử lý lỗi để lựa chọn.
- Help on This Error : Tìm hiểu thông tin về lỗi qua hệ thống Help của Excel
- Show Calculation Steps : Chạy chức năng Evaluate Formula (đánh giá công thức)
- Ignore Error : Bỏ qua, giữ nguyên công thức sai như vậy
- Edit in Formula : Hiển thị công thức trong chế độ chỉnh sửa
(Edit) trên thanh công thức (formula bar) Chẳng qua là để cho bạn tự sửa lại công thức
- Error-Checking : Hiện các tùy chọn của chức năng Error
Checking từ hộp thoại Option để bạn chọn
Thiết lập các tùy chọn cho việc kiểm tra lỗi
Chức năng kiểm tra lỗi công thức trong Word tương tự như kiểm tra ngữ pháp, với nhiều tùy chọn để điều chỉnh cách thức hoạt động và các lỗi được đánh dấu Bạn có thể xem các tùy chọn này bằng hai cách khác nhau.
- Chọn Office, Excel Options để hiển thị hộp thoại Excel Options, và chọn Formulas
- Chọn Error-Checking Options trong danh sách xổ xuống của cái biểu tượng báo lỗi (như đã nói trong bài trước)
Cả hai cách đều mở ra những tùy chọn cho Error Checking và Error Checking Rules như minh họa:
To enable Background Error Checking, use the checkbox that toggles the automatic function of the Formula Error Checker If you disable this feature, you will need to manually select "Formulas" and then "Error Checking" whenever you want to check for formula errors.
Indicate Errors Using This Color : Chọn màu cho dấu chỉ báo lỗi
(cái tam giác nhỏ xíu ở góc bên trái ô có lỗi)
Reset Ignored Errors : Nếu bạn đã bỏ qua một hoặc nhiều lỗi, bạn có thể cho hiển thị lại các lỗi đó bằng cách nhấn nút này
Khi chức năng Kiểm Tra Lỗi Công Thức được kích hoạt, các ô chứa công thức có kết quả là giá trị lỗi như #DIV/0!, #NAME? hoặc bất kỳ giá trị lỗi nào trước đó sẽ được đánh dấu.
Trong phiên bản Excel 2007, tùy chọn "Inconsistent Calculated Column Formula in Tables" cho phép Excel kiểm tra các công thức trong cột tính toán của bảng Khi tùy chọn này được kích hoạt, Excel sẽ đánh dấu các ô có công thức không đồng nhất với những công thức khác trong cùng cột Ngoài ra, smart tag xuất hiện ở ô có lỗi cung cấp lệnh "Restore to Calculated Column Formula", giúp người dùng cập nhật công thức để đảm bảo tính nhất quán với các công thức khác trong cột.
Khi tùy chọn "Cells Containing Years Represented as 2 Digits" được kích hoạt, chức năng Formula Error Checker sẽ đánh dấu các công thức có giá trị ngày tháng với năm chỉ gồm 2 chữ số, gây ra sự mơ hồ giữa các năm 1900 và 2000 Danh sách tùy chọn trong smart tag sẽ cung cấp hai lệnh: "Convert XX to 19XX" và "Convert XX to 20XX", giúp chuyển đổi năm 2 chữ số thành năm 4 chữ số.
Numbers Formatted as Text or Preceded by an Apostrophe :
Định dạng có điều kiện ( Conditionnal Formatting )
Khi sử dụng bảng dữ liệu Excel, nhiều người dùng thường áp dụng chức năng lọc (Filter) để tách lọc dữ liệu theo quy tắc cụ thể Tuy nhiên, nếu bạn muốn làm nổi bật phần dữ liệu đó với định dạng riêng biệt, chức năng lọc sẽ không đáp ứng được yêu cầu này Để thực hiện điều đó, bạn cần sử dụng chức năng định dạng có điều kiện (Conditional Formatting) trong Excel.
Chức năng Conditional Formatting cho phép bạn thiết lập định dạng tùy chỉnh cho các ô được chọn, dựa trên việc dữ liệu trong những ô này đáp ứng các điều kiện đã được xác định trước.
Định dạng khác biệt cho các ô dữ liệu đáp ứng điều kiện giúp người xem dễ dàng nhận diện thông tin phù hợp, đồng thời vẫn giữ được cái nhìn tổng thể về các dữ liệu khác.
Trong hình dưới đây, nhân viên gặp khó khăn trong việc phân biệt giữa đơn hàng quá hạn mà chưa cung cấp đủ số lượng và đơn hàng gần đến hạn nhưng vẫn thiếu số lượng yêu cầu.
Chức năng định dạng giúp người dùng dễ dàng nhận diện các đơn hàng quá hạn với màu đỏ nhạt và các đơn hàng sắp tới hạn nhưng chưa đủ số lượng bằng màu vàng.
Chức năng Định dạng Có Điều kiện cho phép tự động định dạng dữ liệu theo các tiêu chí nhất định, giúp người sử dụng dễ dàng nhận diện và xử lý những vấn đề quan trọng trong bảng tính.
Since the release of Office 2007, Excel's Conditional Formatting feature has been enhanced with multiple condition levels, offering a variety of visually appealing formatting options such as Data Bars, Color Scales, and Icon Sets.
Các định dạng theo mẫu sẵn
Bước 1 Chọn vùng muốn định dạng
Bước 2 Vào Tab Home group Style Conditional Formatting
Bước 3 Chọn định dạng điều kiện muốn thiết lập
Bảng dưới đây liệt kê các đối tượng có trong hộp thoại Conditional Formatting Rules Manager Đối tượng Tác dụng
Để lọc ra các điều kiện đang được sử dụng trong bảng tính, bạn có thể chọn "Current Selection" để xem định dạng có điều kiện cho vùng/ô được chọn Ngoài ra, bạn cũng có thể chuyển sang mục "This Worksheet" để xem toàn bộ các định dạng có điều kiện đang áp dụng trong tập tin bảng tính.
New Rule Nút sử dụng để tạo một luật mới
Edit Rule Nút sử dụng để sửa luật đang chọn
Delete Rule Nút sử dụng để xóa luật đang chọn
Sử dụng các nút để điều chỉnh thứ tự ưu tiên của luật được chọn, bạn có thể di chuyển lên trên luật liền kề phía trên hoặc xuống dưới luật liền kề phía dưới.
Cột lựa chọn ngừng thực thi các luật tiếp theo, nếu luật tương ứng mục chọn đã đúng
Hộp thoại New Formatting Rule
In Excel versions from 2007 onwards, creating a new rule requires the use of the New Formatting Rule dialog box This dialog box is divided into two main sections: Select a Rule Type and Edit the Rule Description.
Format all cells based on their values Định dạng có điều kiện dựa trên giá trị có trong ô
Định dạng có điều kiện cho phép người dùng định dạng các ô dựa trên nội dung dữ liệu chứa trong đó Ví dụ, bạn có thể áp dụng định dạng cho các ô chứa chữ "officelab.vn", ô trống hoặc dữ liệu có ngày hôm qua.
Định dạng có điều kiện cho phép bạn chỉ định các giá trị cao nhất hoặc thấp nhất trong dữ liệu, với tùy chọn định dạng theo số lượng cụ thể hoặc phần trăm.
Format only values that are above or below average Định dạng có điều kiện cho các dữ liệu trên hoặc dưới trung bình theo nhiều tiêu chí khác nhau
Format only unique or duplicate values Định dạng có điều kiện để nhận diện các dữ liệu duy nhất hoặc trùng lặp
Use a formula to determine which cells to format Định dạng có điều kiện dựa theo công thức do người sử dụng xây dựng Để định dạng với Conditional Formatting
Để đánh dấu nhanh các ô thỏa mãn các điều kiện cơ bản Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional
Bước 4 Trong trình đơn Conditional Formatting, chọn Highlight
Bước 5 Trong trình đơn con Highlight Cells Rules, chọn loại điều kiện bạn muốn sử dụng
Bước 6 Nhập các chỉ số cần thiết và kiểu định dạng trong hộp thoại điều kiện, nhấn Ok để xác nhận việc định dạng
Để đánh dấu nhanh các ô theo luật cao/thấp Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional
Bước 4 Trong trình đơn Conditional Formatting, chọn Top/Bottom Rules
Bước 5 Trong trình đơn con Top/Bottom Rules, chọn loại luật cao/thấp bạn muốn sử dụng
Bước 6 Trong hộp thoại điều kiện, nhập các chỉ số và kiểu định dạng bạn muốn sử dụng
Bước 7 Nhấn Ok để xác nhận điều kiện và thực hiện định dạng
Để định dạng theo độ lệch Data Bars Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn Data Bars
Bước 5 Trong trình đơn con Data Bars, chọn loại màu bạn muốn sử dụng
Để định dạng theo độ biến thiên Color Scales Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn Color Scales
Bước 5 Trong trình đơn con Color Scales, chọn loại màu bạn muốn sử dụng
Để định dạng theo biểu tượng phân nhóm Icon Sets Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn Icon Sets
Bước 5 Trong trình đơn con Icon Sets, chọn tập hợp biểu tượng bạn muốn sử dụng
Để tạo một định dạng tùy biến bằng hộp thoại New Formatting Rule Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn New Rule
Bước 5 Trong hộp thoại New Formatting Rule, đặt các điều kiện mong muốn
Bước 6 Nhấn Ok để xác nhận điều kiện và thực hiện việc định dạng
Để xem các định dạng có điều kiện đang được sử dụng Bước 1 Trên thanh Ribbon, chọn thẻ Home
Bước 2 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 3 Trong trình đơn Conditional Formatting, chọn Manage Rules
Bước 4 Trong mục Show formatting rules for, chọn This Worksheet
Để sửa một định dạng có điều kiện Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn Manage Rules
Bước 5 Trong hộp thoại Conditional Formatting Rules Manager, chọn định dạng bạn muốn sửa
Bước 6 Nhấn nút Edit Rule để mở hộp thoại Edit Formatting Rule
Bước 7 Điều chỉnh các tùy chọn điều kiện, sau đó nhấn nút Ok để xác nhận thay đổi và đóng hộp thoại Edit Formatting Rule
Bước 8 Đặt lại vùng được áp dụng định dạng có điều kiện trong cột Applies to (nếu cần)
Bước 9 Nhấn Ok để xác nhận và thực hiện việc định dạng
Để xóa một định dạng có điều kiện Bước 1 Chọn vùng dữ liệu bạn cần định dạng
Bước 2 Trên thanh Ribbon, chọn thẻ Home
Bước 3 Trong nhóm Styles, nhấp chuột lên nút Conditional Formatting
Bước 4 Trong trình đơn Conditional Formatting, chọn Manage Rules
Bước 5 Trong hộp thoại Conditional Formatting Rules Manager, chọn định dạng bạn muốn xóa
Bước 6 Nhấn nút Delete Rule để xóa định dạng
Bước 7 Nhấn Ok để xác nhận việc xóa định dạng và đóng hộp thoại Conditional Formatting Rules Manager.
Thao tác lựa chọn cho công thức
Mặc định, Excel tự động tính toán các công thức trong bảng tính khi bạn mở tệp hoặc thay đổi bất kỳ thành phần nào liên quan đến công thức.
Nếu bảng tính của bạn có kích thước lớn với nhiều công thức liên quan, quá trình tính toán có thể mất thời gian, đôi khi lên đến vài phút.
Trong quá trình tính toán lại các công thức, con trỏ chuột sẽ chuyển sang chế độ chờ, và bạn sẽ không thể thực hiện bất kỳ thao tác nào làm thay đổi bảng tính.
Nếu bạn muốn tiết kiệm thời gian khi nhập và thay đổi công thức trong bảng tính, bạn có thể tạm thời tắt tính năng tự động tính toán Dưới đây là hướng dẫn đơn giản để thực hiện điều này.
Trong cửa sổ Excel Tab File Options Formulas
Để ngăn chặn việc tự động tính toán các công thức khi thay đổi giá trị liên quan, hãy vào hộp thoại tùy chọn tính toán và chọn chế độ Manual.
Sau đây là danh sách xác định các tùy chọn có sẵn:
Tính năng tự động trong bảng tính cho phép bạn tính toán tất cả các công thức phụ thuộc và cập nhật các biểu đồ mở rộng hoặc nhúng mỗi khi có sự thay đổi về giá trị, công thức hoặc tên Đây là cài đặt mặc định cho mọi bảng tính mới được tạo ra.
Tính toán tự động, ngoại trừ các bảng dữ liệu, cho phép cập nhật tất cả các công thức phụ thuộc và biểu đồ liên quan Khi tùy chọn này được kích hoạt, các bảng dữ liệu sẽ chỉ được tính toán lại khi người dùng nhấn nút CalculateNow trên thanh công thức hoặc sử dụng phím tắt F9 trong bảng tính.
Để tính toán các bảng tính và cập nhật mở rộng hoặc nhúng vào biểu đồ, bạn cần nhấp vào nút lệnh "Calculate Now" trên thanh công thức trong Ribbon, hoặc sử dụng phím tắt F9 hoặc tổ hợp phím "Ctrl + =" trong bảng tính.
Trước khi lưu bảng tính, hãy tính toán lại để cập nhật các bổ sung hoặc biểu đồ nhúng Nếu bạn không muốn tự động cập nhật các công thức và biểu đồ phụ thuộc mỗi lần lưu, bạn có thể tắt tùy chọn này.
Enable Iterative Calculation: Kích hoạt tùy chọn này để bạn thiết lập hai tính năng sau:
Lặp lại tối đa (Maximum Iterations) là thiết lập quan trọng cho phép bạn xác định số lần tối đa mà một bảng tính sẽ được tính toán lại khi thực hiện tìm kiếm hoặc giải quyết các tài liệu tham khảo, với giá trị mặc định là 100.
- Thay đổi tối đa (Maximum Change): Thiết lập số lượng thay đổi tối đa các giá trị trong mỗi lần lặp (mặc định 0,001).
Công thức thống kê
Hàm SUMIF cho phép bạn tính tổng theo một điều kiện nào đó trong một vùng dữ liệu
Ví dụ: Tính tổng tiền theo từng loại phòng?
=SUMIF( vùng chứa điều kiện, điều kiện, vùng tính tổng)
Dựa theo điều kiện đề bài ta sử dụng công thức SUMIF để tính:
Vùng chứa điều kiện ( $A$4:$A$13) : vì đề bài yêu cầu tính tổng tiền theo phòng Loại phòng chính là điều kiện Quét vùng dữ liệu cột Loại phòng
Trong vùng (1), để tính tổng tiền cho Loại phòng A, điều kiện cần thiết là "A" Bạn có thể áp dụng nhiều phương pháp khác nhau cho đối số thứ hai này.
- Bạn có thể gõ lại điều kiện cần và điều kiện được đặt trong cập dấu nháy kép
- Hoặc bạn có thể click trỏ chuột vào ô dữ liệu chứa điều kiện và nhớ cố định ô dữ liệu đó
Hàm SUMIF cho phép bạn tính tổng theo một điều kiện cụ thể, nhưng khi đề bài yêu cầu nhiều điều kiện, hàm này không đủ khả năng đáp ứng Do đó, Excel đã cung cấp hàm SUMIFS để hỗ trợ tính tổng theo nhiều điều kiện cùng lúc.
Ví dụ: Tính tổng tiền theo loại phòng và có số người lớn hơn 2
Tương tự hàm tính tổng theo điều kiện ta có các hàm thống kê theo điều kiện :
- Đếm theo điều kiện: Countif, Counifs
- Tính trung bình theo điều kiện: Averageif, Averageifs
Các nhóm hàm thống kê
=SUMIFS(vùng tính tổng, vùng điều kiện 1, điều kiện1, vùng điều kiện 2, điều kiện2,…)
=COUNTIF(Vùng điều kiện, điều kiện)
=COUNTIFS(Vùng điều kiện 1, điều kiện 1, Vùng điều kiện 2, điều kiện 2, ….)
= AVERAGEIF (Vùng điều kiện, điều kiện, vùng cần tính)
= AVERAGEIFS ( Vùng cần tính,vùng điều kiện 1, điều kiện 1, vùng điều kiện 2, điều kiện 2, ….)
Độ lệch tuyệt đối trung bình là một chỉ số quan trọng để đo lường sự biến đổi của tập dữ liệu, được tính bằng cách lấy trung bình của các độ lệch tuyệt đối so với giá trị trung bình Ví dụ, với tập dữ liệu (2, 4, 6), độ lệch tuyệt đối trung bình là 1.333333.
Tính trung bình cộng VD: AVERAGE(2,4,6) 4
Tính trung bình cộng của các giá trị, bao gồm cả những giá trị logic
VD: AVERAGEA(2,3,“true”,4) 2.5 AVERAGEA(2,3,“not true”,4) 2.25 AVERAGEIF (range, criteria1)
Tính trung bình cộng của các giá trị trong một mảng theo một điều kiện
VD: Xem chi tiết ở phía trên
Tính trung bình cộng của các giá trị trong một mảng theo nhiều điều kiện
VD: Xem chi tiết ở phía trên
COUNT (value1, value2, ) Đếm ô chứa giá trị số trong danh sách
VD: COUNT(2,3,“true”,4) 3 COUNTA (value1, value2, ) Đếm số ô có chứa giá trị (không rỗng) trong danh sách
COUNTBLANK (range) Đếm các ô rỗng trong một vùng
VD: COUNTBLANK (A2:A20) COUNTIF (range, criteria) Đếm số ô thỏa một điều kiện cho trước bên trong một dãy
VD: Xem chi tiết ở phía trên
COUNTIFS (range1, criteria1, range2, criteria2, …) Đếm số ô thỏa nhiều điều kiện cho trước
VD: Xem chi tiết ở phía trên
Tính bình phương độ lệch các điểm dữ liệu từ trung bình mẫu của chúng, rồi cộng các bình phương đó lại
FREQUENCY (data_array, bins_array)
Để đếm số lượng giá trị xuất hiện thường xuyên trong một dãy giá trị, bạn cần sử dụng hàm dưới dạng công thức mảng để trả về một mảng chứa các số này.
Trả về trung bình nhân của một dãy các số dương Thường dùng để tính mức tăng trưởng
Cú pháp Ý nghĩa trung bình, trong đó lãi kép có các lãi biến đổi được cho trước…
Trả về trung bình điều hòa (nghịch đảo của trung bình cộng) của các số
Tính độ nhọn của tập số liệu, biểu thị mức nhọn hay mức phẳng tương đối của một phân bố so với phân bố chuẩn
LARGE (array, k) Trả về giá trị lớn nhất thứ k trong một tập số liệu MAX (number1, number2, ) Trả về giá trị lớn nhất của một tập giá trị
Hàm MAXA (number1, number2, ) trả về giá trị lớn nhất trong một tập hợp các giá trị, bao gồm cả giá trị logic và văn bản Trong khi đó, hàm MEDIAN (number1, number2, ) được sử dụng để tính toán giá trị trung vị của một tập hợp số.
MIN (number1, number2, ) Trả về giá trị nhỏ nhất của một tập giá trị
MINA (number1, number2, ) Trả về giá trị nhỏ nhất của một tập giá trị, bao gồm cả các giá trị logic và text
MODE (number1, number2, ) Trả về giá trị xuất hiện nhiều nhất trong một mảng giá trị
PERCENTILE (array, k) Tìm phân vị thứ k của các giá trị trong một mảng dữ liệu
Trả về thứ hạng (vị trí tương đối) của một trị trong một mảng dữ liệu, là số phần trăm của mảng dữ liệu đó
Trả về hoán vị của các đối tượng
Tính điểm tứ phân vị của tập dữ liệu Thường được dùng trong khảo sát dữ liệu để chia các tập hợp thành nhiều nhóm…
RANK (number, ref, order) Tính thứ hạng của một số trong danh sách các số SKEW (number1, number2, )
Độ lệch của phân phối mô tả sự không đối xứng quanh trị trung bình Hàm SMALL (array, k) được sử dụng để trả về giá trị nhỏ nhất thứ k trong một tập hợp số Trong khi đó, hàm STDEV (number1, number2, ) giúp ước lượng độ lệch chuẩn dựa trên mẫu.
STDEVA (value1, value2, ) được sử dụng để ước lượng độ lệch chuẩn dựa trên mẫu, bao gồm cả các giá trị logic Trong khi đó, STDEVP (number1, number2, ) tính toán độ lệch chuẩn cho toàn bộ tập hợp dữ liệu.
Hàm STDEVPA (value1, value2, ) tính toán độ lệch chuẩn cho toàn bộ tập hợp dữ liệu, bao gồm cả chữ và các giá trị logic Trong khi đó, hàm VAR (number1, number2, ) trả về phương sai dựa trên mẫu.
Hàm VARA (value1, value2, …) tính toán phương sai dựa trên mẫu, bao gồm cả các giá trị logic và văn bản, trong khi hàm VARP (number1, number2, ) trả về phương sai dựa trên toàn bộ tập hợp.
VARPA (value1, value2, …) Trả về phương sai dựa trên toàn thể tập hợp, bao gồm cả các trị logic và text
Tính trung bình phần trong của một tập dữ liệu, bằng cách loại tỷ lệ phần trăm của các điểm dữ liệu ở đầu và ở cuối tập dữ liệu.
Công thức mảng
Excel nổi bật với tính năng mạnh mẽ cho phép tính toán với các mảng dữ liệu trong công thức Hiểu rõ khái niệm này giúp người dùng tạo ra những công thức thực hiện các phép tính phức tạp một cách dễ dàng Bài viết này sẽ giới thiệu các khái niệm về mảng số liệu cần thiết cho những ai muốn trở thành chuyên gia sử dụng công thức trong Excel, cùng với các ví dụ về công thức mảng hữu ích.
Công thức mảng trong Excel không lưu trữ số liệu trong các ô mà xử lý trực tiếp trong bộ nhớ máy tính Kết quả tính toán từ công thức mảng sẽ được trả về trên bảng tính, có thể là nhiều ô hoặc chỉ một ô.
Khi sử dụng công thức mảng trong tính toán, công thức sẽ được bao quanh bởi hai dấu ngoặc nhọn {} Những dấu ngoặc này không được nhập thủ công mà sẽ tự động xuất hiện khi người dùng nhấn tổ hợp phím Ctrl+Shift+Enter để thực hiện tính toán.
Nếu bạn thực hiện tính toán hoặc sửa chữa mà quên nhấn tổ hợp phím, công thức của bạn sẽ trả về giá trị không chính xác hoặc hiển thị thông báo lỗi #VALUE! Error.
Công thức mảng trả kết quả về một vùng nhiều ô
Để tính cột Thành Tiền, bạn cần nhập công thức E2 vào ô F2 và sau đó sao chép công thức này xuống các ô F3 đến F7 Trong quá trình này, chúng ta sử dụng tổng cộng sáu công thức để hoàn thành việc tính toán cho cột Thành Tiền.
Ngoài cách này chúng ta có thể dùng một công thức mảng để tính ra kết quả cho cả cột
Thành Tiền và lưu kết quả trả về tại F2:F7 Để tạo một công thức mảng tính toán cho trường hợp này hãy làm theo các bước sau:
Chọn vùng các ô sẽ lưu kết quả trả về của công thức mảng, trong ví dụ này chọn vùng F2:F7
Nhập vào công thức sau D7*E2:E7 (sau khi chọn vùng thì gõ công thức này vào)
Vì đây là công thức mảng bạn hãy nhấn tổ hợp phím
Ctrl+Shift+Enter để nhận kết quả công thức trả về (Công thức thông thường thì chỉ cần Enter)
Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:
Là cách tính toán đảm bảo sự chính xác về kết quả (tránh trường hợp vô tình sao chép sai công thức do chạy địa chỉ tham chiếu)
Công thức mảng giúp ngăn chặn việc vô tình xóa hoặc chỉnh sửa công thức trong một ô nào đó trong vùng công thức mảng, vì nó không cho phép thực hiện những thao tác này trên các ô thuộc vùng công thức.
Dùng công thức mảng sẽ giúp tránh trường hợp người chưa thành thạo Excel làm xáo trộn các công thức của bạn
Công thức mảng trả kết quả về một ô
Để tính tổng số ký tự của một dãy chuỗi Loại Hàng, thay vì tính số ký tự từng chuỗi và sử dụng hàm Sum, bạn có thể sử dụng công thức mảng để thực hiện việc này một cách hiệu quả Nhập công thức vào ô cần tính để nhận kết quả.
Và để kết thúc công thức mảng bạn nhấn tổ hợp phím Ctrl + Shift + Enter
Ví dụ 1: Tính tổng có điều kiện
Bạn cần tính Tổng Tiền theo Loại Hàng với Số Lượng > 0 Ta tính Tổng tiền mặt hàng Tủ Lạnh theo điều kiện:
Công thức mảng sử dụng hàm IF để kiểm tra từng ô trong dãy, tạo ra một mảng mới với các giá trị thỏa mãn điều kiện Mảng mới này sau đó được chuyển vào hàm SUM để tính tổng.
- Trong công thức mảng nếu có nhiều điều kiện thì nếu các điều kiện đồng thời ( toán tử AND ) thì bạn dùng dấu “*” để kết các điều kiện
- Và nếu các điều kiện không đồng thời ( hoặc điều kiện này, hoặc điều kiện khác ) thì bạn dùng dấu “+” để kết các điều kiện
Ví dụ 2: Tìm giá trị lớn nhất, nhỏ nhất
Bạn cần tìm Thành Tiền nhỏ nhất của Loại Hàng là Ampli, thì bạn có thể sử dụng công thức mảng như sau:
Tìm giá trị lớn nhất tương tự, ví dụ bạn cần tìm số lượng lớn nhất của Loại Hàng là Cassette :
Một số khái niệm thêm về Mảng trong Excel
Mảng (array) là khái niệm quen thuộc trong lập trình, đại diện cho tập hợp các phần tử có thể có quan hệ hoặc độc lập với nhau Trong Excel, mảng có thể là một chiều hoặc hai chiều, với chiều được xác định bởi các dòng và cột Cụ thể, mảng một chiều là một vùng số liệu trên bảng tính có thể là một dòng (nằm ngang) hoặc một cột (nằm dọc), trong khi mảng hai chiều là vùng số liệu hình chữ nhật bao gồm nhiều dòng và nhiều cột Lưu ý rằng Excel không hỗ trợ mảng ba chiều, nhưng VBA thì có.
Mảng một chiều có thể được hình dung như một hàng ngang hoặc một cột dọc Trong mảng ngang, các phần tử được phân cách bằng dấu phẩy, trong khi đó, trong mảng dọc, chúng được phân cách bằng dấu chấm phẩy.
Để nhập các giá trị vào mảng chuỗi gồm các ngày trong tuần như "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", bạn cần chọn vùng dữ liệu và nhập công thức mảng Để hoàn tất, hãy nhấn tổ hợp phím Ctrl + Shift + Enter.
Khi làm việc với mảng có 5 phần tử, nếu bạn chọn 7 ô để nhập dữ liệu, ô thứ 6 và thứ 7 sẽ xuất hiện lỗi #NA Để chuyển đổi mảng ngang thành mảng dọc và ngược lại, bạn có thể sử dụng hàm TRANSPOSE.
Ví dụ: Bạn chuyển mảng chuỗi dọc phía trên thành mảng ngang:
Sử dụng các hàm xây dựng sẵn
Hàm trong Excel được sử dụng để tính toán và trả về các giá trị, chuỗi ký tự hoặc thông báo lỗi Excel sở hữu một bộ hàm phong phú, được phân loại theo từng nhóm, phục vụ cho việc tính toán trên nhiều loại dữ liệu và mục đích khác nhau.
Các hàm về chuỗi
Chuyển đổi một mã số trong bộ mã ANSI (có miền giá trị từ 1 - 255) sang ký tự tương ứng
VD: Char(169) © CLEAN (text) Loại bỏ tất cả những ký tự không in ra được trong chuỗi
CODE (text) Trả về mã số của ký tự đầu tiên chuỗi text
Nối nhiều chuỗi thành một chuỗi
VD: =CONCATENATE("Đại học Công nghệ"," Xuất Sắc") Đại học Công nghệ Xuất Sắc
Chuyển đổi một số thành dạng tiền tệ (dollar Mỹ), có kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn
So sánh hai chuỗi Nếu giống nhau thì trả về TRUE, nếu khác nhau thì trả về FALSE Có phân biệt chữ hoa và chữ thường
VD: =EXACT("Đồng Nai","ĐỒNG NAI") FALSE
FIND (find_text, within_text, start_num)
Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi (within_text), tính theo ký tự đầu tiên
VD: =FIND("n","Đại học Công nghệ Xuất Sắc",1)
FIXED (number, decimals, no_commas)
Chuyển đổi một số thành dạng văn bản (text), có hoặc không kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn
Trả về một hay nhiều ký tự đầu tiên bên trái của một chuỗi, theo số lượng được chỉ định
VD: =LEFT("Đại học Công nghệ Xuất Sắc",7) Đại học
LEN (text) Đếm số ký tự trong một chuỗi
VD: =LEN("Đại học Công nghệ Đồng Nai") 26
LOWER (text) Đổi tất cả các ký tự trong một chuỗi văn bản thành chữ thường
VD: =LOWER("ĐẠI HỌC CÔNG NGHỆ XUẤT SẮC")
đại học công nghệ xuất sắc
MID (text, start_num, num_chars)
Trả về một hoặc nhiều ký tự liên tiếp bên trong một chuỗi, bắt đầu tại một vị trí cho trước
VD: =MID("ĐẠI HỌC CÔNG NGHỆ XUẤT SẮC",5,3)
PROPER (text) Đổi ký tự đầu tiên trong chuỗi thành chữ in hoa, và đổi các ký tự còn lại thành chữ in thường
VD: = PROPER("ĐẠI HỌC CÔNG NGHỆ XUẤT SẮC")
Đại Học Công Nghệ Xuất Sắc
REPLACE (old_text, start_num, num_chars, new_text)
Thay thế một phần của chuỗi bằng một chuỗi khác, với số lượng các ký tự được chỉ định
VD: =REPLACE("KHOA CÔNG NGHỆ THÔNG TIN",6,9,"CN") KHOA CN THÔNG TIN REPT (text, times)
Lặp lại một chuỗi với số lần được cho trước
Trả về một hay nhiều ký tự tính từ bên phải của một chuỗi, theo số lượng được chỉ định
VD: =RIGHT("KHOA CÔNG NGHỆ THÔNG TIN",9)
SEARCH (find_text, within_text, start_num)
Tìm vị trí bắt đầu của một chuỗi con (find_text) trong một chuỗi(within_text), tính theo ký tự đầu tiên
VD: =SEARCH("nghệ","Công nghệ thông tin",1) 6
Chuyển đổi một số thành dạng văn bản (text) theo định dạng được chỉ định
VD: =TEXT(37,"#oC") 37oC ( 37 độ C)
Xóa tất cả những khoảng trắng vô ích trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ
VD: =TRIM("Khoa Công nghệ Thông Tin") Khoa Công nghệ Thông Tin
UPPER (text) Đổi tất cả các ký tự trong chuỗi thành chữ in hoa
VD: =UPPER("Đại học Công nghệ Xuất Sắc") ĐẠI HỌC CÔNG NGHỆ XUẤT SẮC
VALUE (text) Chuyển một chuỗi thành một số
Các hàm ngày và giờ (Date & Time)
Khi trả về các số thể hiện một ngày cụ thể, nếu ô có định dạng là General trước khi nhập hàm, kết quả sẽ hiển thị dưới dạng ngày tháng năm.
Hàm EVALUE chuyển đổi chuỗi văn bản ngày tháng năm, như "02/09/13", thành giá trị số tuần tự có thể tính toán được, ví dụ: EVALUE("02/09/13") sẽ trả về 41519.
Trả về phần ngày của một giá trị ngày tháng, được đại diện bởi số tuần tự Kết quả trả về là một số nguyên từ 1 đến 31
DAYS360 (start_date, end_date, method)
Trả về số ngày giữa hai ngày dựa trên cơ sở một năm có
360 ngày (12 tháng, mỗi tháng có 30 ngày) để dùng cho các tính toán tài chính
Trả về số tuần tự đại diện cho một ngày cụ thể tính từ một mốc thời gian đã cho, sau đó cộng thêm một số tháng được chỉ định.
Trả về số tuần tự thể hiện ngày cuối cùng của một tháng cụ thể, tính từ một mốc thời gian đã cho và cách mốc thời gian này một số tháng được chỉ định.
VD: =EOMONTH("30/04/13",1) 31/05/2013 HOUR (serial_number)
Trả về phần giờ của một giá trị thời gian Kết quả trả về là một số nguyên từ 0 đến 23
Trả về phần phút của một giá trị thời gian Kết quả trả về là một số nguyên từ 0 đến 59
Trả về phần tháng của một giá trị ngày tháng, được đại diện bởi số tuần tự Kết quả trả về là một số nguyên từ 1 đến 12
(start_date, end_date, holidays)
Trả về tất cả số ngày làm việc trong khoảng thời gian giữa start_date và end_date, loại trừ các ngày cuối tuần và ngày nghỉ lễ.
Hàm trả về số tuần tự đại diện cho ngày giờ hiện tại Nếu ô có định dạng là General trước khi nhập hàm, kết quả sẽ hiển thị dưới dạng ngày tháng năm và giờ phút giây.
Trả về phần giây của một giá trị thời gian Kết quả trả về là một số nguyên từ 0 đến 59
VD: = SECOND ("1:30:20 PM") 20 TIME (hour, minute, second)
Trả về phần thập phân của một giá trị thời gian (từ 0 đến nhỏ hơn 1) Nếu định dạng của ô là General trước khi
Cú pháp Ý nghĩa hàm được nhập vào, kết quả sẽ được thể hiện ở dạng giờ phút giây
Trả về phần thập phân của giá trị thời gian từ 0 đến dưới 1, được thể hiện bởi hàm time_text, cho phép chuyển đổi chuỗi văn bản có dạng thời gian thành giá trị thời gian có thể tính toán.
Hàm này trả về số tuần tự biểu thị ngày tháng hiện tại Nếu ô có định dạng là General trước khi nhập hàm, kết quả sẽ hiển thị dưới dạng ngày tháng năm.
Trả về thứ trong tuần tương ứng với ngày được cung cấp Kết quả trả về là một số nguyên từ 1 đến 7
Trả về một số cho biết tuần thứ mấy trong năm
WORKDAY (start_day, days, holidays)
Trả về một chuỗi thể hiện số ngày làm việc trong khoảng thời gian nhất định, bao gồm cả ngày trước và sau ngày bắt đầu làm việc, đồng thời loại trừ các ngày cuối tuần và ngày nghỉ (nếu có).
Trả về phần năm của một giá trị ngày tháng, được đại diện bởi số tuần tự Kết quả trả về là một số nguyên từ
(start_date, end_date, basis)
Trả về tỷ lệ của một khoảng thời gian trong một năm VD: =YEARFRAC("01/01/2013","30/06/2013") 50%
Các hàm tìm kiếm (Lookup & Reference)
Hàm tìm kiếm giá trị lookup_value trong cột đầu tiên của bảng table_array theo tiêu chuẩn dò tìm range_lookup và trả về giá trị tương ứng từ cột col_index_num nếu tìm thấy.
- range_lookup = 1: Tìm tương đối, danh sách các giá trị dò tìm của bảng
Table_array phải sắp xếp theo thứ tự tăng dần Nếu tìm không thấy sẽ trả về giá trị lớn nhất nhưng nhỏ hơn lookup_value
- range_lookup = 0: Tìm chính xác, danh sách các giá trị dò tìm của bảng
Table_array không cần sắp xếp thứ tự Nếu tìm không thấy sẽ trả về lỗi #N/A
Chức năng tìm kiếm giá trị lookup_value trong dòng đầu tiên của bảng table_array theo tiêu chuẩn dò tìm range_lookup, và trả về giá trị tương ứng trong dòng thứ row_index_num nếu tìm thấy.
Cú pháp: Ý nghĩa của các đối số của hàm Hlookup tương tự như hàm
VLOOKUP(lookup_value, Table_array, col_index_num, range_lookup)
HLOOKUP(lookup_value, Table_array, row_index_num, range_lookup)
- Chức năng: Hàm trả về vị trí của lookup_value trong mảng lookup_array theo cách tìm match_type
Cú pháp của hàm tìm kiếm trong Excel bao gồm ba loại match_type: match_type = 1 cho phép tìm tương đối với danh sách giá trị trong Table_array phải được sắp xếp theo thứ tự tăng dần, nếu không tìm thấy, hàm sẽ trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn lookup_value match_type = 0 cho phép tìm chính xác, danh sách giá trị trong Table_array không cần sắp xếp, nhưng nếu không tìm thấy, kết quả sẽ là lỗi #N/A Cuối cùng, match_type = -1 cho phép tìm tương đối với danh sách giá trị trong Table_array phải được sắp xếp theo thứ tự giảm dần, và nếu không tìm thấy, hàm sẽ trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn lookup_value.
MATCH(lookup_value, lookup_array, match_type)
Chức năng: trả về giá trị trong ô ở hàng thứ row_num, cột thứ column_num trong mảng array
Các hàm thông tin (ISfunction)
Các hàm thông tin được sử dụng để xác định kiểu dữ liệu của một giá trị hoặc ô, nhằm kiểm tra xem nó có đáp ứng một điều kiện cụ thể nào đó hay không Ví dụ, chúng có thể xác định xem ô dữ liệu có phải là giá trị số hay không, hoặc có phải là chuỗi ký tự hay không.
Các hàm thông tin luôn trả về giá trị TRUE hoặc FALSE, giúp xử lý các trường hợp có dữ liệu ngoại lệ trong bảng dữ liệu cần tính toán.
ISERROR(value): trả về giá trị TRUE nếu value là một lỗi bất kỳ, ngược lại thì trả về giá trị FALSE
ISNA(value): trả về giá trị TRUE nếu value là lỗi #N/A, ngược lại thì trả về giá trị FALSE
ISNUMBER(value): trả về giá trị TRUE nếu value là giá trị số, ngược lại thì trả về giá trị FALSE
ISTEXT(value): trả về giá trị TRUE nếu value là một chuỗi, ngược lại thì trả về FALSE.
Các hàm Cơ sở dữ liệu
Các hàm cơ sở dữ liệu thực hiện việc thống kê các mẫu tin trong cơ sở dữ liệu, dựa trên các trường đáp ứng điều kiện của vùng tiêu chuẩn đã được thiết lập trước đó.
Cú pháp chung: database: địa chỉ vùng CSDL (Chọn địa chỉ tuyệt đối để sao chép)
INDEX(array, row_num, column_num )
Hàm trong cơ sở dữ liệu được cấu trúc như sau: Tên hàm(database, field, criteria) Trong đó, "field" đại diện cho cột cần tính toán, có thể là tên trường, địa chỉ ô của trường hoặc số thứ tự của trường (cột đầu tiên trong vùng dữ liệu được tính là 1 và tăng dần sang trái) "Criteria" là địa chỉ vùng điều kiện mà hàm sẽ áp dụng.
Tên hàm Ý nghĩa và ví dụ
Tính trung bình cộng các giá trị trong cột field của các mẫu tin thỏa điều kiện criteria
ERAGE($A$1:$D$8,D1,F1:F2) DMAX(daTabase, field, criteria)
Tìm trị lớn nhất trong cột field của các mẫu tin thỏa điều kiện criteria
=DMAX($A$1:$D$8,D1,F1:F2) DMIN(daTabase, field, criteria)
Tìm trị nhỏ nhất trong cột field của các mẫu tin thỏa điều kiện criteria
=DMIN($A$1:$D$8,D1,F1:F2) DCOUNT(daTabase, field, criteria) Đếm các ô kiểu số trong cột field của các mẫu tin thỏa điều kiện criteria
UNT($A$1:$D$8,D1,F1:F2) DCOUNTA(daTabase, field, criteria) Đếm các ô khác rỗng trong cột field của các mẫu tin thỏa điều kiện criteria
Các lệnh xử lý dữ liệu
1.1.1.6 Lọc dữ liệu tự động (AutoFilter)
Lệnh Tab DataGroup Sort & FilterFilter cho phép người dùng lọc các mẫu tin từ cơ sở dữ liệu theo các tiêu chí nhất định Kết quả hiển thị chỉ những mẫu tin đáp ứng điều kiện, trong khi các mẫu tin không thỏa mãn sẽ bị ẩn tạm thời.
- Chọn vùng CSDL với tiêu đề là một hàng
Để lọc dữ liệu trong Excel, bạn chọn Tab Data, sau đó vào Group Sort & Filter và chọn Filter Excel sẽ tự động hiển thị các nút thả bên cạnh tên các field, cho phép bạn chọn điều kiện lọc tương ứng với từng field.
- Chọn điều kiện lọc trong hộp liệt kê của từng field tương ứng
- Chọn Text Fillter để thực hiện chức năng lọc nâng cao theo yêu cầu của người dùng:
Show rows where: cho phép người dùng chọn điều kiện và nhập giá trị so sánh ở combobox kế bên Người dùng có thể kết hợp với điều kiện “và”,
1.1.1.7 Lọc dữ liệu nâng cao (Advanced Filter)
Chức năng: Lệnh Tab Data Group Sort & Filter Advanced dùng để trích ra các mẩu tin theo các điều kiện chỉ định trong vùng điều kiện được tạo trước
Bước 1 : Tạo vùng điều kiện lọc Sử dụng một trong hai cách sau:
Để tạo vùng điều kiện, bạn có thể sử dụng tên trường, với ít nhất hai hàng: hàng đầu chứa các tên field điều kiện và các hàng còn lại dùng để mô tả điều kiện.
- Chọn các ô trống trong bảng tính để làm vùng điều kiện
- Sao chép tên field điều kiện làm tiêu đề của vùng điều kiện
Nhập các điều kiện trực tiếp vào ô tương ứng với tên trường Các điều kiện trên cùng một hàng sẽ được hiểu là thỏa mãn đồng thời (AND), trong khi các điều kiện trên các hàng khác nhau sẽ được coi là thỏa mãn không đồng thời (OR).
Để tạo vùng điều kiện, bạn cần sử dụng công thức với hai ô: ô đầu tiên chứa tiêu đề như "điều kiện" hoặc có thể để trống nhưng không được trùng với tên trường, trong khi ô thứ hai sẽ chứa công thức mô tả điều kiện.
- Chọn hai ô trống trong bảng tính để làm vùng tiêu chuẩn
- Nhập tiêu đề ở ô trên của vùng tiêu chuẩn
Nhập công thức vào ô bên dưới để mô tả điều kiện, sử dụng mẫu tin đầu tiên trong cơ sở dữ liệu làm tiêu chí so sánh Hàm AND được áp dụng để thiết lập các điều kiện cho đối tượng điểm.
A >5 kiện thỏa mãn đồng thời, hàm OR dùng để lập các điều kiện thỏa mãn không đồng thời
Bước 2: Vào Tab Data Group Sort & Filter Advanced , xuất hiện hộp thoại gồm các tùy chọn sau:
Filter the list, in-place: kết quả hiển thị trực tiếp trên vùng CSDL
Copy to another location: kết quả được đặt tại một vị trí khác
- List range: chọn địa chỉ vùng CSDL
- Criteria range: chọn địa chỉ vùng tiêu chuẩn
- Copy to: chọn địa chỉ của ô đầu tiên trong vùng kết quả (phải chọn mục Copy to another location)
Chỉ lấy các bản ghi duy nhất: nếu có nhiều mẫu tin giống nhau, chỉ chọn một mẫu tin đại diện; trong trường hợp không có sự trùng lặp, hãy lấy tất cả các mẫu tin đáp ứng tiêu chí của vùng tiêu chuẩn, mặc dù chúng có thể giống nhau.
Chức năng: Thống kê dữ liệu theo từng nhóm trong cơ sở dữ liệu Ví dụ tính tổng tiền lương theo từng nhóm ĐƠN VỊ
Cách thực hiện: Xét CSDL BẢNG LƯƠNG dưới đây Vấn đề đặt ra là cần tính tổng tiền lương theo từng nhóm ĐƠN VỊ
Để sắp xếp dữ liệu theo ĐƠN VỊ, bạn hãy sử dụng lệnh Tab Data, sau đó chọn Group Sort & Filter và nhấn Sort Mục đích của việc này là để các mẫu tin có cùng ĐƠN VỊ nằm liền kề nhau, giúp dễ dàng theo dõi và phân tích thông tin.
- Chọn bảng CSDL cần tổng hợp với tiêu đề là một hàng
- Vào Tab DataGroup OutlineSubtotals, xuất hiện hộp thoại Subtotal :
At each change in: chọn tên trường cần tổng hợp
Use function : chọn hàm sử dụng tính toán hay thống kê
Add subtotal to : Chọn tên trường chứa dữ liệu cần thực hiện tính toán hay thống kê
Replace current subtotals : Thay thế các dòng tổng hợp cũ để ghi dòng tổng hợp mới
Page break between groups: Tạo ngắt trang giữa các nhóm
Summary below data: Thêm dòng tổng hợp sau mỗi nhóm
Làm việc với màn hình kết quả sau khi tổng hợp
Click vào các nút để chọn các mức dữ liệu bạn muốn xem
Chỉ hiển thị tổng chính (Grand Total Only)
Hiển thị tổng chính và tổng phụ (Grand Total And Subtotal)
Hiển thị chi tiết tất cả các mẫu tin cùng các tổng hợp (All Record).
