Biểu đồ quản trị sản xuất.
Quản lý thu chi nội bộ doanh nghiệp.
Báo cáo quản trị nhân sự.
Khi thực hiện mailmerge trên word thì nhiều người gặp tình trạng các trường (cột) tiêu đề lại trỏ ra là F1, F2, F3, F4,.... hoặc có cột thì hiển thị tiêu đề và có cột thì hiển thị F
Cách khắc phục: Bỏ trộn ô cho dòng tiêu đề, xóa các dòng bên trên và để dòng tiêu đề ở dòng số 1 của bảng Excel => sau đó thực hiện lại thao tác Mailmerge trên Word
Pivot Table là công cụ quan trọng trong lập báo cáo nhanh chỉ bằng phương pháp kéo thả mà gần như không phải dùng công thức. Bài viết gồm 30 videos hướng dẫn chi tiết về sử dụng Pivot cơ bản trong Excel, được chia thành 3 videos (10 thủ thuật/1 video)
10 Thủ thuật cơ bản quan trọng của Pivot Table (Phần 1)
Trong bảng số liệu thông tin nhân sự thì quản lý càng cao thì càng được ưu tiên xếp lên trên. Tuy nhiên, nếu sắp xếp theo vần Alphabet theo dạng A-Z hoặc Z-A thì sẽ không chính xác
CÁCH SORT BẢNG DỮ LIỆU THEO CHỨC VỤ TỪ CAO ĐẾN THẤP TRONG EXCEL
Cách 1: Đánh chỉ số cho chức vụ
Giả sử trong doanh nghiệp có khoảng vài chục chức vụ thì sẽ đánh chỉ số từ 01 và trong bảng dữ liệu chi tiết chỉ cần Sort A-Z theo cột Chức vụ theo chỉ số này
Công thức: =TRIM(MID(A1,Find("-",A1)+1,99))
Khi đó cột chỉ số chức vụ để phục vụ sắp xếp và cột công thức để phục vụ cho hiển thị, in ấn, mailmerge,...
Cách 2: Sort theo Custom List
Bước 1: Tạo danh sách Custom List
File => Options => Advance => Edit Custom List. Trong cửa sổ hiển thị thì đưa danh sách các chức vụ vào List Entries => Nhấn Add
Data => Sort => Bảng Sort hiển thị thì mục Columns chọn cột chức vụ, Order thì chọn Custom List => Và trỏ đén List đã tạo ở Bước 1
15:56
{Đt Zalo} - 038 696 1334
Trong bộ tính năng Calculate của Excel thì người dùng thường được biết đến với 2 tính năng Automatic và Manual.
Trong đó:
- Automatic: Cập nhật tức thời công thức
- Manual: Không tự động cập nhật công thức.
Tham khảo chi tiết cách sử dụng 2 tính năng này TẠI ĐÂY
Bên cạnh đó thì bộ tính năng Calculate còn có 2 tính năng bên cạnh là: Calculate Now và Calculate Sheet.
Calculate Now
Tính năng này được sử dụng trong các tình huống sau:
- Khi người dùng đang mặc định trạng thái Manual và muốn cập nhật lại toàn bộ công thức trong file thì bấm chọn tính năng này hoặc bấm phím F9 (hoặc Fn+F9)
- Khi người dùng cần kiểm tra tức thời 1 công thức nào đó hoặc 1 đoạn hay 1 hàm nào đó trong công thức thì bôi đen đoạn đó trong công thức và nhấn F9 để kiểm tra kết quả (Lưu ý: Khi kiểm tra xong thì nhấn Ctrl+Z để trở về công thức gốc)
Calculate Sheet
Tính năng này được sử dụng khi người dùng đang mặc định trạng thái Manual, file đó có nhiều sheet và nhiều công thức => Nếu chọn tính năng Calculate Now thì sẽ làm quá trình cập nhật lâu hơn. Khi đó tính năng Calculate Sheet chỉ cập nhật tính lại cho các công thức ở sheet hiện thời đang thao tác và tiết kiệm thời gian chờ.
{Đt Zalo} - 038 696 1334
Trong nhiều tình huống người dùng nhập liệu (quy cách, thông số kỹ thuật,...) thì hay sử dụng dấu sao * . Ví dụ: 100*200*50. Tuy nhiên, một số hàm của Excel sử dụng dấu * là 1 ký tự trong tìm kiếm hoặc so sánh theo ký tự đại diện kiểu Wildcard (tham khảo các hàm excel sử dụng ký tự đại diện kiểu Wildcard TẠI ĐÂY )
Khi đó, nếu trường dữ liệu này có đưa vào điều kiện trong tính toán, thống kê, tìm kiếm thì kết quả công thức trả về rất dễ bị sai lệch. Lúc này 1 giải pháp đưa ra là thay thế dấu * bằng chữ x. Có 2 cách thay thế như sau:
CÁCH 1: DÙNG CỘT PHỤ TẠO CÔNG THỨC THAY THẾ
=SUBSTITUTE(A1,"*","x")
Sau đó Copy Paste Value cột sau khi thay thế vào cột gốc hoặc dùng cột phụ đưa vào công thức
CÁCH 2: DÙNG TÍNH NĂNG FIND AND REPLACE (PHÍM TẮT: CTRL+H)
Bước 1: Bôi đen vùng dũ liệu cần thay thế và nhấn tổ hợp phím Ctrl+H
Bước 2: Trong bảng Find and Replace hiển thị thì điền như sau:
- Mục Find what: ~*
- Mục Replace with: x
Bước 3: Nhấn Replace All
{Đt Zalo} - 038 696 1334
Với những người dùng Pivot Table thì bảng PivotTable Fields được biết đến là bảng hay Panel điều khiển để tạo ra báo cáo tự động với công cụ Pivot - bằng cách dùng con trỏ để kéo thả. Bài viết này sẽ chia sẻ thêm một số tùy chọn để người dùng có thể khai thác thêm.
ẨN/HIỆN BẢNG ĐIỀU KHIỂN PIVOTTABLE FIELDS
Trong một số trường hợp người dùng vô tình tắt đi muốn mở lại thì thao tác như sau:
Cách 1: Click chuột phải vào vùng báo cáo Pivot và chọn Show Field List
Mặc định của Pivot là mỗi lần thực hiện thao tác kéo thả thì màn hình báo cáo bên tay trái sẽ tự động được cập nhập. Trong một số trường hợp người dùng sử dụng khá thành thạo Pivot và lập báo cáo Pivot với nhiều cột dữ liệu, hoặc dữ liệu cho Pivot khá lớn thì việc cập nhật tức thời sẽ gây ra hiện tượng chậm file.
{Đt Zalo} - 038 696 1334
Trong nhiều tình huống liên quan đến phân quyền dữ liệu thì có thể chia thành các cấp độ như sau:
1. Không có password thì mở được file, tức không xem được nội dung trong file
2. Chỉ xem được nội dung trong file mà không được phép sửa
3. Được sử dụng bình thường nhưng không được phép thêm sửa hay xóa, đổi tên sheet
4. Được phép dùng file nhưng không được can thiệp sửa xóa những sheet nào đó
5. Được phép dùng file và dùng các sheet nhưng không được phép can thiệp vào vùng dữ liệu nào đó
Cấp độ 1: PASSWORD BẢO VỆ FILE
Vào File => Info => Protect workbook => Encrypt with password
Cấp độ 2: CHỈ XEM NỘI DUNG TRONG FILE MÀ KHÔNG ĐƯỢC PHÉP SỬA
Phần này có các các cách khác nhau theo nhu cầu như sau:
Cách 1: Vào File => Info => Protect workbook => Chọn Always open readonly hoặc Mark as final
Cách 2: File => Save As => trong cửa sổ Save As chọn Tools => General Opitons
- Khai báo Password to open và không khai báo Password to modify: Có password thì mới mở được file, và khi mở được file thì được phép chỉnh sửa
- Khai báo Password to open và khai báo Password to modify: Tức có password để mở file nhưng ko có password modify thì chỉ được xem mà ko được sửa file
Cấp độ 3: PASSWORD BẢO VỆ CẤU TRÚC
Tức không được phép thêm sheet, đổi tên sheet, chèn sheet, xóa sheet
Vào File => Info => Protect workbook => Protect workbook structure => Nhập password khai báo
Cấp độ 4: PASSWORD BẢO VỆ SHEET
Xem hướng dẫn chi tiết TẠI ĐÂY
Cấp độ 5: PASSWORD BẢO VỆ VÙNG TRONG SHEET
Xem hướng dẫn chi tiết TẠI ĐÂY
Ngoài ra có thể tham khảo cách ẩn sheet không cho phép unhide TẠI ĐÂY
{Đt Zalo} - 038 696 1334
Trong nhiều tình huống nhập liệu thì nhiều thông tin văn bản được đặt trong dấu đóng mở ngoạc () mà người dùng cần lấy riêng thông tin ra
Lưu ý: Cách này chỉ áp dụng với Office 2013 trở lên
Bước 1: Nhập tay 2-3 kết quả mẫu ở cột bên cạnh
Ví dụ: Dữ liệu từ ô A1 thì cột bên cạnh sẽ nhập kết quả mẫu ở ô B1 và B2
Bước 2: Bôi đen từ ô đầu tiên chứa kết quả mẫu xuống dưới và nhấn tổ hợp phím Ctrl+E
CÔNG THỨC TÁCH ĐOẠN KÝ TỰ VĂN BẢN TRONG DẤU ĐÓNG MỞ NGOẠC
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
Cách dùng: Thay A1 trong công thức bằng địa chỉ ô dữ liệu tương ứng
{Đt Zalo} - 038 696 1334
Trong quản lý thời hạn của sản phẩm, hợp đồng hay công việc nào đó thì việc đưa ra cảnh báo về cận hạn, quá hạn là điều rất cần thiết để tránh lãng phí và tăng mức độ chuyên nghiệp, hiệu quả trong quản lý.
Tham khảo công thức Excel tính ngày hết hạn TẠI ĐÂY
Ví dụ mẫu về cảnh báo quá hạn, cận hạn (Dữ liệu từ A3:C12)
Tình huống 1: Dùng Conditional Formatting tô màu cam cho những dòng quá hạn
Bước 1: Bôi đen vùng dữ liệu từ A3:C12 rồi vào Home => Conditional Formatting => New Rule => Use a formula to determine which cells to format.
Bước 2: Đưa công thức vào phần Format values where this formula is true:
Công thức: =AND(ISNUMBER($C3),$C3<TODAY())
Bước 1: Bôi đen vùng dữ liệu từ A3:C12 rồi vào Home => Conditional Formatting => New Rule => Use a formula to determine which cells to format.
Bước 2: Đưa công thức vào phần Format values where this formula is true:
Công thức: =AND($C3>=TODAY(),$C3-TODAY()<=7)
Bước 3: Nhấn vào nút Format để chọn định dạng
Và kết quả
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Group Sheet trong Excel là một tính năng tạo nhóm các sheets lại thành Groups. Khi đó, chỉ cần nhập liệu hoặc format ở 1 ô nào đó trong sheet bất kỳ của Group thì các sheet kia tự nhận giá trị hoặc format cho các ô đó
Đặc điểm nhận dạng trạng thái Group Sheets: Phần tên file Excel trên thanh công cụ sẽ xuất hiện chữ Group
Để tạo Group thì chỉ cần thao tác đơn giản như sau: Giữ phím Control và bấm vào các sheets cần đưa vào Group. Khi đó các sheet trong Group trong trạng thái được chọn
Thao tác trên nhập liệu, làm công thức hoặc format đồng thời trong Group Sheet: Chỉ cần đặt con trỏ tại 1 sheet bất kỳ trong Group, tiến hành nhập liệu, làm công thức hoặc format cho ô hoặc vùng ô bất kỳ => kết quả: Các sheets còn lại trong Group nhận kết quả lặp lại tương ứng
Đặc điểm khác của Group Sheet: Khi tạo Group sheet thì nhiều tính năng của Excel sẽ bị ẩn đi. Khi đó có thể hiểu các tính năng bị ẩn sẽ không hoạt động được trong trạng thái Group Sheet
Cách thoát khỏi trạng thái Group Sheets: Chỉ cần bấm con chuột sang 1 sheet khác không thuộc Group hoặc tạo 1 sheet mới thì trạng thái Group sẽ biến mất
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Khi tạo biểu đồ trên Excel thì thường người dùng sẽ vào Insert => chọn Chart hoặc dùng Pivot Chart, Power Pivot. Dữ liệu để tạo biểu đồ thường là dữ liệu tổng hợp, khi đó con số đưa vào biểu đồ thường là con số lớn nên khi đó cần định dạng hiển thị con số trên biểu đồ như: Đưa về đơn vị tính cao hơn (đvt ngàn đồng, đvt triệu đồng) hoặc thay đổi khoảng giá trị hiển thị trên biểu đồ
HƯỚNG DẪN FORMAT NUMBER TRÊN BIỂU ĐỒ EXCEL
Bước 1: Chèn biểu đồ từ vùng dữ liệu (Ví dụ biểu đồ hình cột). Kết quả thì Biểu đồ sẽ được hiển thị và tự động format
- Phần Bounds: Dải số hiển thị trong khoảng nào. Như hình ảnh thì hiển thị từ 0 đến 1.4 tỷ. Giả sử điều chỉnh giá trị Minimum thành 500 triệu thì biểu đồ sẽ hiển thị từ 500 triệu đến 1.4 tỷ và độ cao của các cột sẽ thay đổi theo
- Phần Number: Cho phép người dùng khai báo định dạng con số. Mặc định thì sẽ được tích vào Link to Source => Tức hiển thị format theo dữ liệu nguồn
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Thông thường nhiều biểu mẫu mang tính chất thu thập thông tin, khảo sát, đánh giá... thì khi thiết kế trên Excel cần điền 1 loạt các dấu chấm đằng sau để điền thông tin khi in ra
Ví dụ như hình ảnh: Ô A7:D7 được merge lại thành 1 ô. Nhưng chỉ cần điền chữ Họ và tên, phần còn lại sẽ hiển thị 1 loạt dấu chấm mà không cần gõ tay các dấu chấm đó
Bước 1: Nhập thông tin vào ô bất kỳ (Ví dụ: Họ và tên:)
Có thể kéo rộng ô đó ra hoặc Merge Cell cho nhiều ô (tùy theo bố cục của form biểu mẫu)
Bước 2: Vào Format Cell => Trong thẻ Number thì chọn Custom. Ở mục Type thì khai báo đoạn mã format sau: @*.
Tương tự như vậy, nếu muốn hiển thị theo dạng nét liền thì khai báo đoạn mã format sau vào Custom: @*_
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Trong yêu cầu nhập liệu ở một số tình huống thì không được bỏ trống ô dữ liệu, nếu không có thì phải điền số 0 hoặc ký tự nào đó (phụ thuộc vào kiểu dữ liệu). Ví dụ: Cột số lượng hay giá trị thì điền vào số 0. Cột giá trị Text thì điền vào dấu -,....
HƯỚNG DẪN CÁCH ĐIỀN SỐ 0 HÀNG LOẠT VÀO CÁC Ô TRỐNG
Bước 1: Bôi đen vùng dữ liệu từ A1:E9 rồi nhấn tổ hợp phím Ctrl+G => Chọn Special => Chọn Blank và nhấn Ok
Kết quả là: Các ô trống sẽ được chọn
Tương tự như vậy thì hoàn toán có thể điền ký tự bất kỳ nào đó vào các ô trống
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Copy và Paste là tính năng rất mạnh trong Excel với nhiều tùy chọn khi dán dữ liệu, trong đó có thủ thuật dán theo dạng hình ảnh động => Tức khi thay đổi dữ liệu hoặc báo cáo thì hình ảnh cũng cập nhật lại theo
Tình ứng dụng:
- Trong 1 sheet báo cáo có nhiều nội dung, nhưng chỉ muốn cho người dùng xem một nội dung nào đó thì sẽ tạo ảnh động và copy ảnh sang sheet khác rồi ẩn sheet báo cáo gốc đi
- Không muốn cho người dùng can thiệp vào dữ liệu, công thức
HƯỚNG DẪN TẠO ẢNH ĐỘNG THAY ĐỔI THEO DỮ LIỆU BẰNG TÍNH NĂNG COPY PASTE TRONG EXCEL
Tình huống ví dụ: Báo cáo đã có sẵn công thức, thực hiện tạo ảnh động cho báo cáo đó
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Trong thiết kế biểu mẫu, báo cáo quản trị thì thường xuất hiện các biểu theo dõi theo từng nhóm, trong mỗi nhóm có những chỉ tiêu con
Tình huống ví dụ, biểu mẫu kế hoạch sản xuất theo dõi theo chỉ tiêu sản phẩm. Nếu chỉ điền thông tin ở cột A cho dòng đầu tiên và các dòng sau bỏ trống (như hình ảnh) thì khi Filter theo cột A sẽ bị thiếu thông tin.
Bản chất là các ô ở cột A vẫn điền đầy đủ giá trị (không merge cell, không bỏ trống) nhưng sử dụng conditional formatting để hiển thị ô đầu tiên, các ô sau vẫn có giá trị nhưng nhưng chữ màu trắng
Bước 1: Tạo công thức
Công thức: =COUNTIF($A$3:A3,A3)>1
Bước 2: Đưa công thức vào Conditional Formatting
Bôi đen từ ô A3 xuống dưới => Home => Conditional Formatting => New Rule => Use a formula to determine which cells to format và đưa công thức vào mục: Format value wheres this formula is true
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334
Solver là một Add-in có sẵn trong Excel để xử lý bài toán ngược như: Từ danh sách số có sẵn, tìm ra các con số có tổng bằng số mong muốn
Bản chất Solver sẽ chạy lần lượt các phương án Solutions để tìm ra kết quả đúng hoặc gần đúng nhất. Khi đó nếu công thức càng phức tap hoặc số liệu càng lớn thì càng nhiều Solutions và Solver chạy càng lâu
HƯỚNG DẪN SỬ DỤNG SOLVER TRONG EXCEL
Tình huống giả lập: Cần tìm ra các số từ A2:A20 sao cho tổng bằng 1245
Vào File => Options => Add-in => Go => Và tích vào Solver
=SUMPRODUCT(A2:A20,B2:B20)
Set Objective: Đưa vào ô C1
To: Chọn Max, Min hoặc Value Of (nhập tay số tổng mong muốn)
By changing variable cells: Quét vào cột B từ B2:B20
Bước 4: Nhấn Solve và chờ máy tính chạy chạy tìm ra kết quả
Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334