1. Hiển thị danh sách sổ xuống dạng Yes/No

Đối với bài toán có các mục xuất hiện trong danh sách sổ xuống ít như bài toán tạo bảng câu hỏi với câu trả lời là Yes hoặc No hay bài toán lựa chọn giới tính với câu trả lời là Nam hoặc Nữ thì các bạn không cần tạo vùng dữ liệu riêng mà chỉ cần thực hiện thao tác sau:

• Chọn ô hoặc dãy ô trong bảng tính nơi bạn muốn đặt danh sách tùy chọn -> vào thẻ Data -> chọn Data Validation trong nhóm Data Tools
• Hộp thoại Data Validation xuất hiện:
• Trong tab Settings mục Allow -> chọn List (Tích chọn ô In-cell dropdown và tích chọn ô Ignore blanks nếu người dùng được phép để ô trống)
• Trong mục Source nhập Yes, No (đối với bài toán tạo bảng câu hỏi) hoặc Nam, Nữ (đối với bài toán lựa chọn giới tính)
• Nhấp chọn OK -> thu được kết quả như hình minh họa:

2. Tạo danh sách sổ xuống tự động cập nhật khi loại bỏ hoặc thêm mới

Các bước thực hiện như sau:
Bước 1: Nhập các mục bạn muốn xuất hiện trong danh sách sổ xuống. Sau đó, bạn nhấp chọn một ô bất kỳ trong vùng dữ liệu vừa nhập và nhấn Ctrl + T để chuyển đổi danh sách thành một bảng.

• Chú ý: Sau khi tạo bảng, bạn truy cập vào tab Design của thẻ ngữ cảnh Table Tools -> vào nhóm Properties -> trong mục Table Name bạn sẽ thấy tên bảng hoặc đặt lại tên bảng nếu muốn.

Bước 2: Chọn một ô bất kỳ trong bảng -> vào thẻ Formulas -> chọn Define Name trong nhóm Defined Names

• Hộp thoại New Name xuất hiện -> gõ tên vào ô Name (không dùng dấu cách hay dấu gạch ngang khi đặt tên).
• Trong mục Refers to nhập =”tên bảng” (tên bảng được lấy từ mục Table Name hoặc do bạn đặt ở Bước 1) -> nhấp chọn OK

Bước 3: Chọn ô hoặc dãy ô trong bảng tính nơi bạn muốn đặt danh sách sổ xuống -> vào thẻ Data -> chọn Data Validation trong nhóm Data Tools

Bước 4: Hộp thoại Data Validation xuất hiện:

• Trong tab Settings mục Allow -> chọn List (Tích chọn ô In-cell dropdown và nếu người dùng được phép để ô trống thì bạn tích chọn ô Ignore blanks )
• Trong mục Source nhập =(tên vùng dữ liệu được đặt ở Bước 2)
• Nhấp chọn OK

Sau khi hoàn thành các bước trên, bạn sẽ nhận được danh sách sổ xuống tự động cập nhật theo vùng dữ liệu gốc như hình minh họa:

• Tạo danh sách sổ xuống trong Excel
• Internet Download Manager v6.38 Build 2 mới nhất – Fix lỗi fake serial key
• Chèn ngày hoặc giờ hiện tại vào ô
• Cách tạo biểu đồ tự động cập nhật dữ liệu
• Các thủ thuật hay trong Excel
• Dùng PivotTable để lập báo cáo động trong Excel
• Tại sao nên sử dụng biểu đồ thu nhỏ trong Excel?
• Định dạng có điều kiện trong Excel
• Các thủ thuật giúp in bảng tính Excel chuyên nghiệp
• Lợi ích của việc đặt tên ô, vùng dữ liệu
• Thủ thuật tìm và sửa dữ liệu sai lệch, không chính xác

Bài toán 1: Lập báo cáo doanh thu bán hàng của mỗi nhân viên bằng cách sử dụng PivotTable.

Giả sử dữ liệu bán hàng của các nhân viên như hình bên dưới:

Để sử dụng PivotTable lập báo cáo doanh thu bán hàng của mỗi nhân viên các bạn thực hiện như sau:

Bước 1: Xác định các cột dữ liệu liên quan đến báo cáo

• Doanh thu bán hàng của nhân viên dựa vào cột Số tiền để tính
• Đối tượng báo cáo ở đây là cột Nhân viên

Bước 2: Tiến hành lập báo cáo bằng PivotTable

1. Chọn bảng hoặc ô (bao gồm các tiêu đề cột) có chứa dữ liệu bạn muốn sử dụng.

2. Vào thẻ Insert -> chọn PivotTable trong nhóm Tables

3. Hộp thoại Create PivotTable xuất hiện, bạn chọn các tùy chọn sau:

• Khung Table/Range: chứa địa chỉ bảng dữ liệu là Table1, có thể chọn lại nếu chưa đúng.
• Tích chọn Use an external data source nếu sử dụng nguồn dữ liệu bên ngoài Excel, ví dụ các tập tin cơ sở dữ liệu hay XML…
• Chọn New Worksheet để đặt PivotTable trong trang tính mới (nếu các bạn muốn báo cáo xuất hiện trong trang tính hiện tại hãy chọn Existing Worksheet, sau đó chọn một ô để đặt PivotTable).

4. Nhấp chọn OK và giao diện xây dựng PivotTable sẽ xuất hiện trên một bảng tính mới như sau:

5. Trong PivotTable Fields hãy chọn các cột liên quan đến báo cáo. Vì chúng ta muốn biết tổng số tiền mỗi nhân viên bán hàng bán được là bao nhiêu nên chúng ta sẽ chọn trường Số tiền và Nhân viên bán hàng. Sau khi chọn trường Nhân viên bán hàng được thêm vào khu vực Rows, còn trường Số tiền được thêm vào khu vực Values. Ngoài ra, bạn có thể nhấp, giữ và kéo một trường đến khu vực mong muốn.

6. PivotTable sẽ tính toán và hiển thị doanh thu bán hàng của mỗi nhân viên như sau:

Trong báo cáo, đổi tên các trường dữ liệu:

A3: Row Labels –> Nhân viên bán hàng

B3: Sum of Số tiền –> Doanh Thu

A12: Grand Total –> Tổng doanh thu

Bài toán 2: Sắp xếp doanh thu bán hàng của các nhân viên từ cao xuống thấp dựa vào báo cáo doanh thu của Bài toán 1

1. Sau khi có được báo cáo doanh thu bán hàng của các nhân viên, bạn nhấp chuột chọn một ô bất kỳ trong cột Doanh thu (không nhấp chọn tiêu đề cột)

2. Vào thẻ Data -> chọn Sort Largest to Smallest trong nhóm Sort & Filter

3. Thu được kết quả là bảng doanh thu bán hàng đã được sắp xếp theo thứ tự doanh thu từ cao xuống thấp của các nhân viên

Bài toán 3: Dựa vào báo cáo doanh thu của Bài toán 1, lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất để trao thưởng.

Để tiến hành lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất các bạn thực hiện các thao tác sau:

1. Trong cột Nhân viên bán hàng (ô A3), tại vị trí nút lọc (hình mũi tên trỏ xuống) -> nhấp chọn Value Filters -> chọn Top 10…

2. Hộp thoại Top 10 Filter xuất hiện, hiệu chỉnh số 10 thành số 3 và nhấp chọn OK (nếu muốn hiển thị TOP 5 thì bạn chỉnh số 10 thành số 5, còn muốn để TOP 10 thì dữ nguyên, …)

3. Kết quả thu được sau khi lọc là TOP 3, nếu muốn sắp xếp thì nhấp chọn Sort Largest to Smallest trong nhóm Sort & Filter của thẻ Data. Xem hình minh họa

Chú ý: Nếu bạn thay đổi bất kỳ dữ liệu nào trong bảng nguồn của mình, PivotTable sẽ không tự động cập nhật. Để cập nhật, bạn nhấp chọn PivotTable -> thẻ ngữ cảnh PivotTable Tools xuất hiện -> chọn tab Analyze -> chọn Refresh trong nhóm Data.

Bài toán 4: Lập báo cáo doanh thu bán hàng theo tháng, dựa vào báo cáo của Bài toán 1?

1. Yêu cầu của bài toán là lập doanh thu bán hàng theo tháng nên đối tượng báo cáo sẽ là Tháng.

Tại bảng PivotTable Fields của Bài toán 1 ta bỏ chọn trường Nhân viên và tích chọn trường Tháng.

2. PivotTable sẽ tự động điều chỉnh hoặc xoay trục để hiển thị dữ liệu mới. Bạn sẽ thu được báo cáo doanh thu bán hàng theo tháng như hình minh họa:

Cột Row Labels (ô A3) được đổi tên thành Tháng

Cột Sum of Order Amount (ô B3) đổi tên thành Doanh thu

Cột Grand Total (ô A7) đổi tên thành Tổng doanh thu

• Cách tạo biểu đồ tự động cập nhật dữ liệu
• Sử dụng Goal Seek trong Excel để tìm mục tiêu
• Sách hay về lập trình VBA Excel- Làm chủ VBA cho Microsoft Office 2013
• Thêm ô, hàng, cột bằng phím tắt trong Excel
• Chuyển dữ liệu từ hàng thành cột và ngược lại trong Excel

Bài toán 1: Sử dụng Goal Seek để tìm kiếm mục tiêu đạt điểm vượt qua lớp.

Giả sử bạn có bảng điểm như hình bên dưới:

Điểm trung bình hiện có là 5.1 và bạn cần ít nhất 5.5 để đạt điểm vượt qua lớp. Điều này sẽ phụ thuộc vào điểm của môn Vật lý, vậy bạn cần đạt điểm môn Vật lý tối thiểu là bao nhiêu để vượt qua lớp, Goal Seek sẽ giúp bạn giải quyết vấn đề này.

1. Vào thẻ Data-> nhấp chọn lệnh What-If Analysis trong nhóm Data Tools -> chọn Goal Seek… từ menu thả xuống.

2. Hộp thoại Goal Seek xuất hiện:

Trong đó:

• Set cell: Ô chứa công thức tính toán của bài toán, ví dụ công thức tính điểm vượt qua lớp trong ô C11.
• To value: nhập giá trị kết quả mong muốn, ví dụ điểm để vượt qua lớp là 5.5.
• By changing cell: Đây là ô nơi Goal Seek sẽ đưa ra giá trị cần để đạt được kết quả vừa nhập vào ô To value, ví dụ điểm môn Vật lý cần đạt được trong ô C10.

3. Khi bạn đã hoàn tất, nhấp OK.

4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, môn Vật Lý cần đạt được 8.5 điểm để vượt qua lớp.

Bài toán 2: Sử dụng Goal Seek để tìm thời hạn vay cụ thể với khoản trả góp hàng tháng là 3,500,000 đồng.

Giả sử bạn định vay một khoản 500,000,000 đồng để mua nhà, trong thời hạn 120 tháng, với lãi suất 7.5%/năm. Sử dụng công thức =PMT(Lãi suất vay/12, số tháng trả góp, số tiền vay), bạn tính được số tiền trả góp hàng tháng là -5,935,088.46 đồng (số âm thể hiện khoản phải trả).

Tuy nhiên, bạn chỉ có khả năng trả góp 3,500,000 đồng/tháng, nên cần phải vay trong thời hạn dài hơn. Bạn có thể sử dụng Goal Seek để tìm ra số tháng vay cụ thể với khoản trả góp hàng tháng là 3,500,000 đồng.

1. Vào thẻ Data-> nhấp chọn lệnh What-If Analysis trong nhóm Data Tools -> chọn Goal Seek… từ menu thả xuống.

2. Hộp thoại Goal Seek xuất hiện:

Trong đó:

• Set cell: Đây là ô chứa công thức tính toán của bài toán, ví dụ công thức tính số tiền trả góp hàng tháng trong ô B6.
• To value: Đây là khoản trả góp hàng tháng mà bạn có thể chi trả được, ví dụ khoản trả góp ở đây là 3,500,000 đồng/tháng.
• By changing cell: Đây là ô nơi Goal Seek sẽ đưa ra được số tháng vay cụ thể với khoản trả góp hàng tháng vừa được nhập vào ô To value, trong ví dụ này là ô B3.

3. Khi bạn đã hoàn tất, nhấp OK.

4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng.

Chú ý:

• Trong bài toán 2, giá trị nhập vào ô To value là -3,500,000 (số âm thể hiện khoản phải trả), nếu bạn nhập số dương thì Goal Seek sẽ thông báo không tìm được dữ liệu theo yêu cầu.
• Trong quá trình phân tích mô hình dữ liệu, Goal Seek sẽ thực hiện số lần lặp tối đa là 100 để tìm giá trị yêu cầu. Nếu Goal Seek không tìm được giá trị cuối cùng, có nghĩa là mô hình dữ liệu của bạn không hoạt động. Hãy điều chỉnh lại mô hình để đạt được kết quả như ý muốn.
• Bạn có thể sử dụng Goal Seek để giải quyết nhiều bài toán thực tế khác nữa, chỉ cần bạn đưa ra được kết quả mong muốn và Goal Seek sẽ giúp bạn tìm ra các mục tiêu.

• Tìm tổng mà không cần sử dụng công thức
• Thiết lập định dạng cho số, ngày tháng, các tham số trong hàm
• VBA Excel Hàm trong Excel VBA
• 15 Hàm Excel thường được sử dụng (phần 2)
• Hàm Excel thường được sử dụng (phần 1)

Biểu đồ thu nhỏ (Sparkline) là dạng biểu đồ được vẽ trọn vẹn trong một ô, biểu diễn một series dữ liệu gồm các ô trên cùng dòng hoặc cùng cột. Bạn có thể tạo nhanh loại biểu đồ này để theo dõi xu hướng hay sự biến động của các series dữ liệu.

Trong hình ảnh bên dưới, khi sử dụng biểu đồ thu nhỏ sẽ theo dõi được rõ ràng dữ liệu bán hàng của từng nhân viên.

Có ba loại biểu đồ thu nhỏ khác nhau: Line, Column, Win/Loss. Có thể hiển thị các điểm đánh dấu tại các điểm quan trọng, như điểm cao nhất và thấp nhất, để làm cho chúng dễ đọc hơn.

Tạo biểu đồ thu nhỏ:

1. Chọn dòng hoặc cột chứa dữ liệu cần vẽ biểu đồ thu nhỏ

2. Vào thẻ Insert -> trong nhóm Sparklines-> chọn loại biểu đồ thu nhỏ mong muốn.

3. Hộp thoại Create Sparklines sẽ xuất hiện.

• Nhấp chọn Data Range nếu muốn thay đổi dòng hoặc cột dữ liệu muốn biểu đồ hiển thị
• Nhấp chọn Location Range để lựa chọn ô chứa Sparklines
• Nhấp chọn OK

4. Biểu đồ thu nhỏ sẽ xuất hiện trong ô đã chỉ định.

5. Nhấp đúp chuột vào Fill handle để tạo biểu đồ thu nhỏ trong các ô liền kề. Sparklines sẽ được tạo cho các ô đã chọn.

• Hiệu chỉnh biểu đồ thu nhỏ:

Hiển thị các điểm đánh dấu cao nhất và thấp nhất trên Sparkline

1. Nhấp chọn một biểu đồ thu nhỏ bạn muốn thay đổi.

2. Vào tab Design trong nhóm thẻ ngữ cảnh Sparkline Tools -> chọn các tùy chọn mong muốn trong nhóm Show.

3. Các biểu đồ thu nhỏ sẽ cập nhật để hiển thị các điểm đánh dấu đã chọn.

Chú ý:

1. Vào nhóm Style của tab Design để lựa chọn các kiểu hiển thị của Sparkline:

2. Vào nhóm Type của tab Design để lựa chọn loại Sparkline:

3. Mỗi loại biểu đồ thu nhỏ sẽ phù hợp với một loại dữ liệu nhất định. Ví dụ: biểu đồ Win/Loss phù hợp nhất với loại dữ liệu có giá trị dương và âm (chẳng hạn như thu nhập ròng).

Xóa biểu đồ thu nhỏ:

• Chọn một hay nhiều ô chứa Sparklines

• Vào tab Design trong nhóm thẻ ngữ cảnh Sparkline Tools -> chọn Clear trong nhóm Group -> thu được kết quả như hình minh họa:

• Cách tạo biểu đồ tự động cập nhật dữ liệu
• VBA Excel loại bỏ mật khẩu bảo vệ workbook và worksheet
• Một số kinh nghiệm trong bảng tính Excel
• Các thủ thuật hay trong Excel
• Hạn chế sử dụng công cụ hợp nhất ô (Merger Cells)

Sử dụng định dạng có điều kiện trong Excel cho phép bạn tự động thay đổi màu sắc, màu chữ và các kiểu đường viền của một hoặc nhiều ô, hàng, cột khi thỏa mãn điều kiện nhất định. Thông qua định dạng có điều kiện, bạn sẽ dễ dàng quan sát, đối chiếu, đánh giá, thậm chí nhận diện các giá trị không hợp lệ hay đột biến của dữ liệu.

Ví dụ: Trong bảng sau, các ô có giá trị lớn hơn $4000 sẽ được tô màu đỏ.

Bạn sẽ định dạng dữ liệu có điều kiện bằng những quy tắc định dạng (Formatting rules) được Excel cung cấp, hoặc bạn có thể tạo các quy tắc mới.

1. Chọn các ô muốn áp dụng định dạng có điều kiện

2. Vào thẻ Home -> chọn Conditional Formatting trong nhóm Styles.

3. Chọn quy tắc định dạng mong muốn trong danh sách hiện ra.Ở ví dụ trên, bạn muốn làm nổi bật các ô có giá trị lớn hơn $4000 do đó chúng ta sẽ chọn quy tắc Highlight Cell Rules -> chọn Greater than

Chú ý:

• Data Bars: thêm các thanh màu vào ô, có độ dài tỷ lệ tương ứng với giá trị dữ liệu trong ô.

• Color Scales: thay đổi màu nền, màu sắc sẽ tương xứng với giá trị dữ liệu trong ô. Tô màu các ô bằng cách phối 2 hoặc 3 màu. Ví dụ: trong thang màu Green – Yellow – Red , các giá trị cao nhất là màu xanh lá cây, các giá trị trung bình là màu vàng và các giá trị thấp nhất là màu đỏ.

• Icon Sets: thêm các biểu tượng vào ô, dạng biểu tượng sẽ tương ứng với giá trị dữ liệu trong ô.

4. Hộp thoại Greater Than xuất hiện, nhập giá trị điều kiện vào ô bên dưới dòng chữ “Format cells that are LESS THAN“. Xét theo điều kiện ở ví dụ trên chúng ta sẽ nhập vào ô giá trị là $4000.

5. Chọn kiểu định dạng bạn muốn trong danh sách hiện ra. Từ yêu cầu của ví dụ trên chúng ta sẽ chọn Light Red Fill with Dark Red Text, sau đó nhấn OK .

6. Định dạng có điều kiện sẽ được áp dụng cho các ô đã chọn. Từ kết quả thu được ta dễ dàng nhận xét được các nhân viên bán hàng nào đạt được mục tiêu doanh số $4000 mỗi tháng.

Chú ý: Bạn có thể áp dụng nhiều quy tắc định dạng có điều kiện cho một phạm vi ô hoặc trang tính như hình minh họa:

Xóa định dạng có điều kiện:

1. Vào thẻ Home -> chọn Conditional Formatting trong nhóm Styles.

2. Trong danh sách hiện ra nhấp chọn Clear Rules -> chọn một trong các quy tắc xóa hiện ra.

3. Các định dạng có điều kiện sẽ được gỡ bỏ.

Chú ý: Vào thẻ Home -> chọn Conditional Formatting trong nhóm Styles -> trong danh sách hiện ra nhấp chọn Manage Rules -> để chỉnh sửa hoặc xóa các quy tắc riêng lẻ. Điều này đặc biệt hữu ích nếu bạn đã áp dụng nhiều quy tắc cho một bảng tính.

• VBA Excel Events, Application Object
• Hàm Excel thường được sử dụng (phần 1)
• 15 Hàm Excel thường được sử dụng (phần 2)
• Tổng hợp các phím tắt trong Excel
• Thiết lập định dạng cho số, ngày tháng, các tham số trong hàm