Khóa Chức Năng Sort Trong Excel / TOP 3 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Có nhiều cấp độ bảo mật trong excel như:

Bảo vệ khi mở file Excel (Protect an Excel file): ngăn người dùng tự ý mở file Excel, bao gồm mở chỉ để xem nội dung hoặc mở để xem và sửa nội dung trong file.

Bảo vệ cấu trúc Workbook (Protect Workbook): ngăn người dùng tự ý thay đổi các sheet trong workbook: đổi tên, thêm mới, xóa, ẩn, bỏ ẩn… mà những việc đó ảnh hưởng tới cấu trúc của workbook.

Bảo vệ nội dung trong 1 sheet (Protect Sheet): ngăn người dùng tự ý thay đổi nội dung của 1 ô hay 1 vùng trong ô, bao gồm nhiều tùy chọn để có thể cho phép hay không cho phép sử dụng một vài tính năng trong khi sheet đang bị khóa.

Bảo vệ thông qua thiết lập bảo vệ trong VBA

Tuy có nhiều cấp độ bảo mật nhưng tính năng bảo mật trong Excel lại dễ bị phá. Vì vậy mục đích chính của việc bảo mật trong excel nhằm tránh cho người sử dụng có những lỗi vô ý làm mất dữ liệu hoặc phá vỡ tính liên kết sẵn có của nội dung trong Excel.

Những bước bảo vệ cơ bản

Bảo vệ khi mở file Excel (File Protect)

Với phiên bản Excel 2007 trở về sau việc khóa file được thực hiện ở cửa sổ Lưu File

Bấm chọn Save as (phím tắt F12) để mở cửa sổ Lưu file. Trong cửa sổ Lưu file chúng ta chú ý vào mục Tools cạnh nút Save

Always creat backup : Luôn tạo 1 bản lưu dự phòng cho file của chúng ta, nhằm mục đích chúng ta không mở lại được file gốc (quên mật khẩu)

Password to open: Mật khẩu để mở file. Khi đặt mật khẩu mở file thì mỗi khi mở file Excel đều sẽ yêu cầu phải nhập mật khẩu mới cho phép chúng ta truy cập vào nội dung file

Password to modify: Mật khẩu để sửa file. Ngoài việc mở file để xem nội dung, chúng ta có thể thiết lập để không cho phép người khác tự ý sửa file khi đã mở được file, muốn sửa cần phải có mật khẩu để mở lớp bảo vệ này.

Read-only recommended: khuyến cáo file chỉ nên đọc. Khi file excel của chúng ta có tình trạng bảo vệ mở và sửa file thì chúng ta có thể đặt 1 thông báo tới người mở file rằng chỉ nên đọc chứ không nên sửa nội dung, nhằm tránh gây ra những sai sót do việc không hiểu cấu trúc nội dung của file.

Bảo vệ WorkSheet (Protect Workbook)

Trong tab Review, chúng ta chú ý tới chức năng Protect Workbook. Khi sử dụng chức năng này chúng ta có thể thiết lập bảo vệ cho cấu trúc của Workbook, tránh việc người dùng tự ý sửa, xóa sheet, thay đổi vị trí các cấu trúc trong workbook.

Chèn thêm sheet (Insert)

Xóa sheet (Delete)

Đổi tên sheet (Rename)

Di chuyển hoặc copy sheet (Move or Copy)

Tô màu cho sheet (Tab Color)

Ẩn sheet (Hide)

Mở ẩn sheet (Unhide)

Với những giới hạn như vậy chúng ta chỉ có thể sử dụng những sheet đã cho sẵn mà không thể làm thay đổi cấu trúc các sheet trong workbook.

Bảo vệ nội dung trong Sheet (Protect Sheet)

Chức năng này nằm trong tab Review, bên cạnh chức năng Protect Workbook

+Allow all users of this worksheet to: Cho phép thực hiện những điều sau đây trong khi Sheet đang được Protect bằng việc đánh dấu chọn vào mục tương ứng:

Select unlocked cells: Chọn tới các ô không bị khóa. Mặc định là cho phép chọn tới các ô không bị khóa (hầu hết đều sử dụng nội dung này)

Format cells: sử dụng chức năng định dạng ô. Mặc định là không cho phép. Khi khóa sheet thì bạn có thể cho phép / không cho phép người dùng thực hiện chức năng định dạng, mục đích để bảo vệ những thiết lập định dạng ban đầu theo ý muốn của mình, không cho người khác tự ý thay đổi định dạng (font chữ, màu sắc, loại dữ liệu…). Áp dụng ngay cả với những ô khóa hay không bị khóa.

Format columns + Format rows: sử dụng chức năng định dạng cột, hàng. Mục đích để ngăn người dùng định dạng các vấn đề về cột, hàng (ví dụ như ẩn cột, ẩn hàng, mở ẩn, chỉnh độ rộng, tô màu…)

Insert columns + Insert rows: Sử dụng chức năng chèn thêm cột, hàng

Delete columns + Delete rows: Sử dụng chức năng xóa cột, hàng

Use PivotTable & PivotChart: Sử dụng chức năng pivot (bảng báo cáo động, biểu đồ động)

Edit Objects: Sử dụng chức năng chỉnh sửa các đối tượng

Edit scenarios: Sử dụng chức năng kịch bản (chức năng scenarios trong excel)

Tuy nhiên chúng ta cần chú ý thêm 1 nội dung nữa là: thiết lập Cell nào là cell bị khóa (locked cell), cell nào không bị khóa (unlocked cell)

Trong bất kỳ cell nào trong sheet đều mặc định là ở chế độ lock

Chức năng đặt tên – define name giúp dễ nhớ các vùng, bảng dữ liệu – dễ quản lý dữ liệu và áp dụng vào công thức, giúp công thức ngắn gọn dễ hiểu,…

1. Chức năng đặt tên – chức năng define name giúp bạn như thế nào?

– Chức năng đặt tên – define name g iúp dễ nhớ các vùng, bảng dữ liệu – dễ quản lý dữ liệu và áp dụng vào công thức.

– Giúp công thức ngắn gọn dễ hiểu, dễ sửa chữa – chỉ cần thay đổi nội dung trong vùng dữ liệu thì kết quả công thức sẽ thay đổi theo mà không phải sửa lại công thức.

– Thực hiện một số công thức phức tạp, hoặc với số lượng lớn công thức cần xử lý.

– Làm đơn giản hóa Excel, gồm các hàm, hoặc các công thức phức tạp.

– Tránh sai sót, nhầm lẫn trong việc quản lý dữ liệu hay trong việc lập công thức.

2. Thao tác với Define name trong Excel

Chọn ô, dãy ô mà bạn muốn đặt tên.

Cách 1: Nhấp hộp Name ở bên trái ngoài cùng của thanh công thức rồi gõ nhập tên muốn đặt. Nhấn Enter.

Giao diện Define Name (New Name)

Những qui định về việc đặt tên cho các ô, các công thức, các hằng :

1. Các ký tự được phép: Ký tự đầu tiên của 1 tên phải là mẫu tự, hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là các mẫu tự, các số, các dấu chấm, và các ký tự gạch dưới. Ví dụ : _01_01_04 hay HH_01.

2. Các tên không thể là một tham chiếu ô, VD: R1C1 hay A$1.

3. Có thể dùng nhiều từ, nhưng không có khoảng trống. Các ký tự gạch dưới hay dấu chấm có thể được dùng để nối từ. Ví dụ: Product_01 hay Product.01.

4. Số ký tự : Một tên có thể lên đến 255 ký tự.

5. Loại kiểu chữ dùng trong tên: Excel không phân biệt ký tự in hoa và ký tự thường trong các tên.

Lưu ý: Excel không nhận ra các nhãn trong công thức nên bạn hãy vào Tools/Options/Calculation. Bên trong Workbook options bạn hãy chọn hộp kiểm Accept labels in formulas.

3. Một số ứng dụng trong sử dụng tên vùng

Bạn đang có nhu cầu: khi bạn nhập mã vào 1 ô, thì ô bên cạnh sẽ hiện tên đầy đủ của hàng hóa đó.

Ví dụ tại ô D2 bạn nhập mã BUT01 – bạn muốn kết quả tại ô E2 là Bút bi Thiên Long.

Bạn sẽ dụng hàm vlookup như sau: Tại ô E2 bạn nhập =vlookup($D$2,$A$1:$B$6,2,0). nhìn vào công thức trên, bạn sẽ thấy địa chỉ làm căn cứ tham chiếu cho hàm được thể hiện bằng tọa độ.

Như vậy sẽ khó khăn cho bạn khi muốn kiểm tra xem dữ liệu tham chiếu ở đâu, lại có nhiều ký hiệu tọa độ khiến bạn khó nhớ, khó sử dụng công thức.

Ta hãy áp dụng chức năng đặt tên vùng như sau:

Bôi đen vùng A1:B6 , đặt tên vùng là DanhMuc (theo quy ước đặt tên không phân biệt chữ hoa, chữ thường. Do đó đặt chữ hoa xen lẫn chỉ để dễ phân biệt các từ).

Đặt tên ô D2 là GiaTri

Khi đó bạn tạo hàm vlookup là =vlookup(GiaTri,DanhMuc,2,0).

Hàm cho kết quả tương tự như hàm đã tạo ở trên, nhưng bạn dễ nhớ hơn và sử dụng nhanh hơn.

Với 1 bảng số liệu gồm 5 cột, 101 hàng, tại sheet 1

Ở sheet 2, bạn cần làm các yêu cầu

Tại ô A2, bạn nhập 1 mã hàng, dùng công thức để xác định Tên (ô B2), Nhà sản xuất (C2), Giá nhập (D2), Chi tiết (E2).

Như vậy bạn cần làm tối thiểu 4 hàm vlookup cho 4 ô.

Nếu nhập công thức không dùng tên vùng, ta sẽ thấy B2 = vlookup($A$2,Sheet1!$A$1:$B$101,2,0), và nếu quên đặt dấu giá trị tuyệt đối, bạn sẽ bị sai kết quả khi sao chép công thức từ ô B2 sang C2, D2, E2.

Tất nhiên bạn phải sửa giá trị 2 ở công thức B2 thành 3 khi sang ô C2 (tham chiếu tại cột thứ 3 – tìm hiểu kỹ hàm vlookup), thành 4 khi sang ô D2.

Như vậy rất khó theo dõi nội dung hàm, cũng như cần chú ý nhiều hơn khi lập hàm.

Nếu dùng tên vùng thì sao:

Đặt tên vùng tại sheet 1 từ A1:B101 là DanhMuc.

Đặt tên vùng tại sheet 2 ô A2 là GiaTri.

Hàm vlookup sẽ là vlookup(GiaTri,DanhMuc,2,0).

Bạn không cần phải nhớ dấu giá trị tuyệt đối cho tham chiếu vùng, cho ô giá trị, vì khi đặt tên vùng nó đã tự tham chiếu bằng giá trị tuyệt đối cho vùng được đặt tên.

Khi bạn thêm/ bớt ô, cột, hàng trong vùng DanhMuc thì sao?

Công thức dạng tham chiếu tuyệt đối vlookup($A$2,Sheet1!$A$1:$B$101,2,0) không tự thay đổi khi DanhMuc thay đổi. Các giá trị tham chiếu giữ nguyên. Do đó, phần thêm/bớt có thể không được tính hoặc tính sai.

Công thức dạng tham chiếu tên vlookup(GiaTri,DanhMuc,2,0) sẽ tự thay đổi khi nội dung của vùng đặt tên được thay đổi. Nếu nội dung trong DanhMuc tăng/ giảm thì DanhMuc cũng thay đổi theo. Do đó, nó được dùng để tính toán.

Một tác dụng nữa của chức năng đặt tên vùng là thay thế công thức làm đơn giản hóa công thức

Theo ví dụ trên, có yêu cầu là tại sheet 2, ô A2 cho mã, ô B2 cho số lượng, tại C2 yêu cầu tính trực tiếp ra kết quả thành tiền. Bạn sẽ đặt công thức là C2 =B2*(vlookup(GiaTri,DanhMuc,2,0)).

Để đơn giản công thức trên, ta tạo 1 tên là DonGia. Bạn vào Define Name, tại bảng New Name, mục Name nhập DonGia, mục Refer to bạn nhập =vlookup(GiaTri,DanhMuc,2,0) rồi ấn ok.

Khi đó công thức C2 có thể đơn giản hóa là =B2*DonGia.

Việc áp dụng chức năng đặt tên vùng vào công thức tính mang lại rất nhiều lợi ích, cũng như thú vị hơn khi bạn sử dụng Excel, giúp đem lại cảm giác gần gũi, linh hoạt, đẹp mắt hơn. Ta không thấy các công thức khô khan với các tọa độ, dài dòng khó hiểu với các công thức ghép nối chằng chịt. Với chức năng đặt tên, mọi thứ đơn giản hơn rất nhiều, dễ tìm, dễ nhớ, dễ sử dụng không chỉ cho người viết ra mà còn cho cả người xem.

Cái tên nói lên tất cả, và chỉ cần thông qua 1 bước đặt tên, bạn đã dễ dàng kiểm soát được những điều tưởng như rất rắc rối, khô khan và khó khăn.

Bạn đã học qua cách dùng các hàm và các kỹ thuật phức tạp trong excel, Hôm nay tôi hướng dẫn các bạn cách làm một ứng dụng nho nhỏ nhưng rất hữu ích, đặt biệt nó rất cần thiết đối với các bạn kế toán thường phải làm tem nhãn để dán lên tài sản cố định, hay các bạn nhân viên hành chính nhân sự phải in phiếu lương, thư mời…

Ví dụ tôi có bản danh sách nhân viên nằm ở Sheet2 như sau:

Và trên phiếu lương tôi cần xuất 3 thông tin xuất hiện đó là Mã Nhân Viên, Họ tên nhân viên và Phòng ban.

Theo danh sách bên trên thì mỗi nhân viên ứng với 1 con số ở bên cột No. Như vậy Cách đơn giản nhất là dùng Hàm Vlookup để gọi thông tin của từng nhân viên dựa trên số thứ tự của nó tương ứng. do đó tôi thiết kế một cái nhãn ở một trang tính khác như sau:

A B 1 Số thứ Tự 2 3 Mã Nhân Viên =vlookup(B1,sheet2!A:D,4,0)4 Họ Và Tên =vlookup(B1,sheet2!A:E,5,0)

5 Phòng Ban =vlookup(B1,sheet2!A:F,6,0)

Mỗi khi bạn nhập các số từ 1 cho đến số cuối cùng trong bản danh sách trên thì mọi thông tin của nhân viên đó được gọi ra để bạn tiến hành in.Và lưu ý là số thứ tự trong cột A không được trùng nhau.

Tuy nhiên cách làm như trên thì mỗi lần in chỉ hiện ra thông tin của một nhân viên trên một trang giấy như thế thì mất thời gian và tốn kém khi danh sách nhân viên, hay tài sản dài hàng trăm, hàng ngàn dòng.

Lúc này đòi hỏi ta thiết kết lại trang in sao cho mối lần in là nhiều nhất (tùy theo khổ giấy in đặc biệt là loại giấy có thiết kế và định dạng sẵn có keo dán.

Ở đây tôi in trên nền giấy in sẵn A5 với bốn nhãn nên tôi thiết kế trang in tương ứng như sau:

Cũng theo cách làm trên mỗi khi ta nhập một giá trị tương ứng với số thứ tự trong cột A của sheet2 vào ô D3 thì các nhãn sẽ tự động điền thông tin của 4 nhân viên có số thứ tự liền kề với giá trị của ô D3.

Như vậy thay vì in 1 lần 1 người như ở cách làm thứ 1, lúc này một lần in ta có thể in được 4 người. Nếu khổ giấy của bạn có thể in nhiều hơn thì bạn chỉ việc copy cả ba dòng chứa mã nhân viên, Họ Tên và phòng ban rồi dán xuống bên dưới. hoặc bên phải. Sau đó chỉnh sửa lại giá trị Lookup_value của hàm Vlookup thành D3+4; D3+5 v.v.

Đến đây thì ứng dụng hoàn thành 90% rồi. Sở dĩ như vậy vì nếu bạn làm việc cho công ty lớn, mỗi phòng ban có nhiều người, Nếu in theo thứ tự từ 1~ hết nhân viên trong công ty sau đó mới bọc nhãn ra, sắp xếp lại rồi bàn giao cho từng phòng. Làm như thế này cũng rất mất thời gian phân loại và sắp xếp sau khi in.

Lúc này bạn nghĩ đến việc làm sao để in theo phòng ban để không phải sắp xếp các bản in?

Cách thứ nhất,rất đơn giản đó là bạn chuyển sang Sheet2 chứa danh sách nhân viên, ta thiết lập chức năng Data Filter ở dòng thứ 1 rồi dùng chức năng Sort để sắp sếp cột Phongban theo thứ tự Alphabet, và sau đó đánh số lại cột No (Cột A), Lúc này ta có thể in theo từng phòng ban rồi.

Cách thứ 2:Như bạn thấy ở danh sách nhân viên bên trên tôi có thêm cột P.ban_No được đánh số thứ tự theo số người trong mỗi phòng ban. Đây là điểm mấu chốt của cách 2, Vậy làm sao để đánh số thứ tự theo từng phòng ban? Ta dùng Hàm IF để thực hiện việc đánh số tự động.

Để Hàm Vlookup tìm kiếm chính xác và không bị lỗi thì ta phải thiết lập Data Validation cho cột phòng ban ở danh sách nhân viên (Sheet2) và ô D1 của trang in

Lưu ý lúc này Lookup_Array là vùng từ cột C:F, và công thức hàm bên trên ta có thể lấy vùng tham chiếu từ C:F áp dụng cho tất cả các công thức trên.

Tối ưu thêm kết quả in:

Theo thiết lập trên thì ta đã hoàn chỉnh được chức năng in tự động giống với Merge Mail rồi, tuy nhiên sẽ có vấn đề phát sinh trong quá trình in, ví dụ như trang in bị rách một phần, làm cho chất lượng của một vùng nào đó của kết quả in bị xấu hoặc bị mất, lúc này ta muốn in lại một nhân viên bất kỳ mà không phải dò tìm nhân viên đó ở vị trí nào trong danh sách.

Giả sử như cột A đã được đánh số theo thứ tự từ 1 ở dòng số 2 thì, ta sẽ định vị được vị trí của nhân viên là kết quả của Hàm Match trừ đi 1 đơn vị. từ đó ta dùng hàm Vlookup để trả về số thứ tự của nó trong mỗi phòng ban, nó thuộc phòng ban nào.

Ta đặt công thức ở cột C2 như sau: =Vlookup(MATCH(D2,Sheet2!D:D,0)-1,Sheet2!A:B,2,0)&” “&Vlookup(MATCH(D2,Sheet2!D:D,0)-1,Sheet2!A:F,6,0)

lúc này mỗi khi nhập mã nhân viên vào ô D2 thì C2 sẽ hiện ra vị trí của nhân viên đó ở phòng ban nào. Căn cứ vào đó ta sẽ nhập lại tùy chọn ở ô D1 và D3 và in lấy kết quả.

Thêm một ý tưởng nữa là chúng ta sẽ dự báo số lần in cho mỗi một phòng ban và số lần in còn lại để giúp ta chuẩn bị giấy in tốt hơn.

Dự báo số trang in (ô D4) : Ta dùng Hàm CountIF để đếm số nhân viên căn cứ vào giá trị của ô D4 (Phòng ban) sau đó chia số nhân viên trong mỗi lần inC4=countif(sheet2!D:D,Sheet1!D1)D4=C4/ 4

Dự báo số trang in còn lại (ô D5) ta dùng Hàm And để kết hợp với Hàm IF để xuất ra các hướng dẫn cho người in.

Excel 2016 cung cấp cho chúng ta tính năng Sort và Filter vô cùng mạnh mẽ với những bổ sung về tính năng và thay đổi trên giao diện trực quan hơn so với các phiên bản trước đây.

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn cách sử dụng các tùy chọn sắp xếp và lọc dữ liệu một cách rõ ràng và căn bản nhất.

1. Tính năng Sort

Bạn có thể Sort dữ liệu theo từng cột hoặc nhiều cột cùng 1 lúc. Bạn có thể sắp xếp dữ liệu của bạn theo cách tăng dần, giảm dần.

Sắp xếp theo từng cột

Để sắp xếp dữ liệu theo từng cột, bạn tiến thành theo các bược sau:

Kết quả

Sắp xếp cùng lúc nhiều cột

Cửa sổ Sort hiện ra

2. Tính năng này tự nhận biết các tiêu đề của các cột, bạn chọn Last Name từ danh sách thả xuống của mục ‘Sort by’

3. Bạn nhấp nút Add Level

4. Sau đó bạn chọn tiếp cột Sale từ danh sách thả xuống.

Kết quả. Các dòng dữ liệu được sắp xếp tăng dần đầu tiên theo cột Last Name và thứ 2 là cột Sales

2. Tính năng Filter

Khi nào bạn cần lọc dữ liệu ( Filter) là khi bạn chỉ muốn hiện thị dữ liệu theo một hoặc nhiêu tiêu chí. Nó cung cấp cho bạn nhiều cách để lọc dữ liệu dựa trên các văn bản, giá trị hoặc ngày tháng.

2. Trên tab Data , trong nhóm Sort & Filter, các bạn chọn Filter.

Các bạn đã thấy có các mũi tên ở các tiêu đề cột rồi phải không.

3. Hãy chọn vào mũi tên ở cột Country

4. Đầu tiên các bạn bỏ chọn mục Select All, và chọn mục con USA.

5. Bấm nút OK.

Kết quả là Excel chỉ hiện các dòng tương ứng USA của cột Country.

7. Bỏ chọn Select All để bỏ chọn hết các tiêu chi con, và chọn tiếp Qtr 4.

8. Nhấn OK

Kết quả là Excel đã thể hiện các dòng dữ liệu bán hàng phù hợp với tiêu chí USA và Qtr 4

9. Để bỏ tính năng Filter đi, chúng ta cứ việc quy trở lại nơi bắt đầu, trên tab Data, trong nhóm Sort & Filter, chọn Clear để bỏ trạng thái Filter và mũi tên đi.

Cách Sử Dụng Excel 2016 – Cách Cố Định Dòng Cột Trong Excel

Thế Giới Excel