Excel: Hi-light due dates

Tình huống:

Bạn dùng Excel để quản lý một danh sách công việc có thời hạn hoàn tất (deadlines / due dates). Bạn có nhu cầu khi nhìn vào file Excel này, sẽ biết ngay công việc (task) nào đã hoàn tất, sắp đến hạn, hoặc công việc nào đã hết hạn mà chưa hoàn tất. File Excel đó cần thể hiện như sau:

Điều kiện hi-light là:

Giải pháp:

Sử dụng tính năng Conditional Formatting trong Excel. Bạn có thể download file mẫu ở đây.

Các bước thực hiện:

1. Giả sử dữ liệu nằm ở vùng B6:D15 như sau (xem trong file mẫu)

2. Chọn toàn bộ vùng (B6:D15), click Conditional Formatting trên thanh menu 

3. Chọn New Rule…

4. Chọn loại rule là  “Use a formula to determine which cell to format”

5. Gõ điều kiện Format vào ô Format values where this formula is true

6. Điều kiện đầu tiên – Format font chữ màu xám đối với những công việc đã hoàn tất:

• Gõ =$D6=”Yes”
• Click Format… - Chọn màu font về xám như yêu cầu format ở trên.
• Click OK

7. Thêm điều kiện format thứ 2: hi-light màu cam cho những công việc nào sẽ đến hạn trong vòng 7 ngày

• Click Conditional Formatting trên thanh Menu – Manage Rules…
• Click New Rule…
• Chọn loại rule là  “Use a formula to determine which cell to format”
• Gõ điều kiện Format vào ô Format values where this formula is true:
• Gõ: =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”)
• Click Format… - Chọn màu nền về màu cam như yêu cầu format ở trên.
• Click OK

7. Thêm điều kiện format thứ 3: hi-light font màu trắng trên nền đỏ cho những công việc nào đã quá hạn.

• Click Conditional Formatting trên thanh Menu – Manage Rules…
• Click New Rule…
• Chọn loại rule là  “Use a formula to determine which cell to format”
• Gõ điều kiện Format vào ô Format values where this formula is true:
• Gõ: =AND($C6<=TODAY(),$D6<>”Yes”)
• Click Format… - Chọn màu nền về màu đỏ và font màu trắng như yêu cầu format ở trên.
• Click OK

Và thế là xong, kể từ giờ khi bạn nhìn vào file excel của bạn, bạn sẽ dễ dàng biết được công việc nào sắp hết hạn, đã hết hạn để có thể ưu tiên cho chúng hợp lý hơn.

Chúc các bạn một ngày cuối tuần vui vẻ.

Nguồn: Chandoo.org

Continue Reading...

Excel: Hi-light due dates

Excel: format Text để thể hiện số 0 phía trước số đếm

Nếu bạn muốn gõ số: 00023 hoặc 023.340 hoặc 23.345000 trong Excel, bạn sẽ thấy Excel tự động chuyển chúng thành 23 hoặc 23.34 hoặc 23.345. Tóm lại là Excel sẽ tự động bỏ những số không phía trước và phía sau dấu thập phân.

Thay vì gõ thêm ký tự này “’” cho từng ô dữ liệu, bạn có thể format toàn phạm vi cần gõ dữ liệu dạng này thành dạng Text.

Thao tác rất đơn giản, trong Excel 2007 - Bạn sẽ làm như sau:

Cơm thêm:

Giả sử bạn cần format như sau (cột bên phải)

Hãy dùng các bước sau:

1. Chọn vùng cần format – Ctrl +1

2. Trong Tab Number – chọn Custom

3. Nhập vào chỗ Type: 00000 – OK

Vậy là xong, hy vọng bài viết này từ Chandoo.org sẽ giúp ích cho các bạn khi nhập dữ liệu có chữ số dưới dạng format như mong muốn.

Chúc các bạn cuối tuần vui vẻ :).

Continue Reading...

Excel: format Text để thể hiện số 0 phía trước số đếm

Excel: So sánh 2 danh sách

“Mọi sự so sánh đều khập khiễng” – biết vậy nhưng có khi nào bạn thôi không so sánh :)?

Bạn nào dùng Excel nhiều sẽ hay có nhu cầu so sánh dữ liệu trên 2 danh sách (list) như sau:

Bài viết sau đây biên tập lại từ chandoo.org sẽ hướng dẫn bạn chi tiết các bước thực hiện:

1. Công thức sử dụng

Về căn bản, chúng ta sẽ dùng hàm COUNTIF.

• Cú pháp: =COUNTIF(lst2,value)=0
• Giải thích cú pháp: Kiểm tra giá trị “value” có tồn tại đâu đó trong “lst2” hay không

2. Dữ liệu giả định

3. Tìm và Tô sáng (hi-light) Dữ liệu chỉ có trong List 1

• Chọn các giá trị trong List 1 (Giả sử thuộc B21:B29)
• Chọn Conditional Formatting – New Rule… – Use a formula to determine which cells to format
• Dùng Rule sau: =COUNTIF(lst2,B21)=0
• Đương nhiên trước đó bạn đã phải đặt tên (name) data range lst2 và lst1
• Chọn màu hi-light – OK
• Bạn sẽ thấy những dữ liệu nào có trong list 1 mới được hi-light

4. Tìm và hi-light Dữ liệu chỉ có trong List 2

Quy trình làm tương tự (giả sử dữ liệu của list 2 ở C21:C28) – chỉ có ngược lại

Rule: =COUNTIF(lst1,C21)=0

5. Hi-light dữ liệu nào có cả trong 2 list

• Chọn dữ liệu trong list 1 (giả sử B21:B29)
• Dùng Conditional Formatting với Rule: =COUNTIF(lst2,B21)>0
• Chọn dữ liệu trong list 2 (giả sử C21:C28)
• Dùng Conditional Formatting với Rule: =COUNTIF(lst1,C21)>0

5. Bonus

Hãy download file example – trong sheet hilight searched value – bạn sẽ biết cách : Tạo 1 ô Search –> hilight giá trị cần tìm trong List 1 và List 2

Hy vọng bài viết này giúp bạn tiết kiệm thời gian trong công việc :).

“Nguồn: Chandoo.org”

Continue Reading...

Excel: So sánh 2 danh sách

[Excel tip] Tạo nhanh cùng nội dung cho nhiều cells

Đôi khi, bạn cần tạo nhanh nội dung giống nhau trong nhiều cells. Có nhiều cách để làm việc này (Ctrl + C và Ctrl +V là cách được dùng nhiều nhất).

Tuy nhiên cách sau đây có lẽ là nhanh nhất:

 

 

 

 

1. Chọn tất cả các cells sẽ có cùng dữ liệu
2. Nhập dữ liệu cần tạo
3. Ctrl + Enter

Như vậy là xong – rất đơn giản và tiện lợi.

Bạn có thể xem hình minh họa sau để làm theo

“Nguồn: chandoo.org”

Continue Reading...

[Excel tip] Tạo nhanh cùng nội dung cho nhiều cells

Excel: lên kế hoạch mua quà Giáng Sinh / lễ Tết

Giáng sinh sắp đến rồi bạn sẽ chuẩn bị quà gì cho gia đình, bạn bè, người yêu, đồng nghiệp và các con/cháu/em của bạn?

Chandoor.org vừa cập nhật sheet Excel sau đây giúp bạn lên kế hoạch mua quà nhanh nhất, tiết kiệm nhất, và không bỏ sót người nào.

Bạn có thể down sheet Excel này tại đây.

Sau đây là mô tả sơ bộ 1 số cột:

• Cột A: Tên người nhận quà
• Cột B: Món quà (có thể ghi địa chỉ cửa hàng/nơi bán)
• Cột C: Phân loại người nhận quà
• Cột D: Độ tuổi người nhận
• Cột E: Chi phí dự tính
• Cột F: Chi phí thực tế
• Cột G: Chi phí phát sinh hoặc số tiền tiết kiệm được (so với Dự tính ban đầu ở cột E)

Còn đây là những Ô có giá trị đặc biệt cần quan tâm:

• Ô G3: Bạn đã mua mấy món quà / tổng số quà
• Ô G4: Món quà mắc nhất mua cho ai
• Ô G5: Số món quà mắc hơn dự tính / số món quà bằng hoặc rẻ hơn dự tính
• Ô G6: Tổng chênh lệch chi phí giữa dự tính và thực tế
• Ô G7: Tổng chi phí thực tế
• Ô G8: Tổng chi phí dự tính
• Ô G9: Tổng Dự tính chi phí thực tế (món quà nào chưa có chi phí thực tế – chưa mua -  sẽ lấy chi phí dự tính đưa vào)

Chúc bạn mua được những món quà vừa ý – còn mình phải nhanh chân lên danh sách đây :).

Continue Reading...

Excel: lên kế hoạch mua quà Giáng Sinh / lễ Tết

5 Tuyệt chiêu shorcut Excel

Rất nhiều người (nhất là các bà các cô và các bác lớn tuổi) cho rằng bỏ công ra học phím tắt là mất nhiều hơn được, chỉ tổ tốn nơ-ron thần kinh mà chả tiết kiệm mấy. Thật ra, sử dụng phím tắt không chỉ tiết kiệm rất nhiều thời gian mà còn rất có lợi cho sức khỏe của bạn, giúp giảm nguy cơ bị RSI (Repetitive Stress Injury).

Và đương nhiên là mỗi người có bộ phím tắt riêng tùy theo việc họ sử dụng máy tính để làm gì. Sau đây là 5 tuyệt chiêu shortcut Excel mà tôi tâm đắc nhất – rất mong được nghe thêm tuyệt chiêu khác từ các bạn . (Tất cả các shortcut này đều được sử dụng trên Excel 2007)

1. Ctrl + 1 – Bật cửa sổ Format Cells

Tuyệt chiêu number one – vì có ai làm việc trên Excel mà lại không cần format dữ liệu cho sạch đẹp, bắt mắt?

2. Ctrl + Shift + L – Bật/tắt Filter

Nếu bạn thường xuyên làm việc với 1 bảng dữ liệu lớn cần filter – shortcut này giúp bạn tiết kiệm ít nhất 3-5 giây mỗi khi sử dụng – quá tuyệt còn gì!!!

3. Ctrl + PageUp/PageDown – Chuyển qua/lại giữa các Sheet trong cùng 1 Workbook

Bạn đã từng phải khổ sở khi phải dùng chuột chuyển tới/chuyển lui giữa một rừng Sheet – Bạn có thể vui rồi…

4. F4 – thay đổi Cell Reference

Thay vì phải gõ trực tiếp F2 rồi đổi thành $F2, F$2, hoặc $F$2 – bạn hãy dùng F4 – nhanh và tránh được nhiều sai sót.

5. F2 – thay đổi nội dung Cell

Lệnh này thì chắc chắn là các bạn sẽ dùng quá thường xuyên rồi – không phải giải thích gì thêm nữa .

Các bạn hãy chia sẻ bộ shortcut Excel yêu thích của mình cho mọi người học hỏi nhé.

Nguồn: http://chandoo.org

Continue Reading...

5 Tuyệt chiêu shorcut Excel

Excel: Pivot table là gì?

Pivot table là một trong những tính năng hữu dụng nhất của Excel.

Bài viết này trích sử dụng thông tin từ chandoo.org để giải thích và hướng dẫn cách tạo 1 pivot table trong Excel.

1. Ngữ cảnh sử dụng:

Bạn có 1 bảng dữ liệu Excel về thông tin kinh doanh với một số cột như sau:

 

Link download file dữ liệu này.

Pivot table sẽ giúp bạn làm được điều sau một cách đơn giản và dễ dàng:

Thống kê dữ liệu (ví dụ tính trung bình doanh thu – sales – của mỗi khu vực – region)

2. Các bước tạo Pivot table

• Chọn vùng dữ liệu dùng để tạo Pivot table

Trong ví dụ trên, tôi tô sáng phần dữ liệu từ ô B3 đến H1082 trong sheet Example Data

• Click Insert trên Ribbon Menu – Pivot Table

 

3. Khi cửa sổ Create pivot table wizard hiện ra – click OK

Excel sẽ tạo 1 sheet mới và đặt Pivot table của bạn vào sheet đó như sau:

4. Tạo Pivot table report bằng cách chỉnh sửa PivotTable Field List bên tay phải

Trong ví dụ này, tôi click chọn các field sau: Month, Region, Product, Net Sales – và đây là kết quả:

Để có 1 Pivot table report tốt hơn, bạn có thể tinh chỉnh PivotTable Field List như sau:

Và đây là kết quả:

Bạn cũng có thể tinh chỉnh PivotTable Field List sao cho tạo ra Pivot table report hợp lý nhất theo ý bạn – cái này thì không có công thức chung, bạn cứ làm vài lần sẽ “ngộ” ra thôi :).

Continue Reading...

Excel: Pivot table là gì?

Mẫu biểu đồ Excel/Ppoint

http://www.juiceanalytics.com cung cấp miễn phí 17 mẫu biểu đồ (chart template) đẹp và tiện dụng trong Excel và Powerpoint.

Bạn có thể click vào đây để chọn và download file mẫu dưới dạng xls/ppt về.

Continue Reading...

Mẫu biểu đồ Excel/Ppoint

Sorting và scroll bar

Trong bài Tạo scroll bar trong Excel các bạn đã tạo 1 Dashboard căn bản, có 1 table có thể cuộn lên xuống được.

Yêu cầu của xếp bạn cao hơn một chút – làm sao có thể sắp xếp dữ liệu trong bảng đó một cách dễ dàng như ví dụ minh họa ở trên.

Trước tiên bạn download file mẫu ở đây.

Tiếp theo, các bạn có thể xem file mình tạo dựa trên file mẫu ở đây.

Trong file này, 3 sheet: Data, Dashboard, Calculation là của file mẫu – không sửa chữa gì cả. Hai sheet: 01Dashboard và 01Calculation là do mình tạo dựa trên file mẫu - hướng dẫn này chủ yếu đề cập đến 2 sheet này.

Sheet Data sẽ là sheet dữ liệu dùng chung.

Sau đây là các bước thực hiện (giả dụ bạn chỉ có file mẫu – chưa có 2 sheet 01Dashboard và 01Calculation)

1. Tạo sheet 01Calculation

(Chỉ tạo đến dòng 6 mà thôi – Ô D6 để trống – không nhập giá trị)

Right click ô D6 – click Name a range – đánh vào mysort để đặt tên cho ô này là mysort.

2. Tạo scroll bar – tham khảo bài Tạo Scroll bar trong Excel

Kết quả sẽ là 1 Scroll bar như sau trong sheet mới 01Dashboard

(Chưa có các nút KPI)

3. Tạo nút KPI 1 -> KPI 5

Chọn tab Developer – click Insert – click Option Button (Form Control)

vẽ 1 Option Button

Click lên Option Button này – xóa chữ Option 01

Right click Option Button này – click Format Control – sang tab Control và cấu hình như sau:

Lưu ý phần Cell link cần cấu hình chính xác.

Bạn lặp lại các thao tác trên để có đủ 5 Option Button cho 5 KPI – sau đó di chuyển chúng vào các ô KPI.

4. Cấu hình Conditional Formatting sao cho khi click nút KPI sẽ tạo ra giá trị sắp xếp trong ô ‘01Calculatioin’!$D$6

Trong sheet 01Dashboard thực hiện các bước sau cho nút KPI 1:

Chọn (tô sáng) range D4:D14 – click Conditional Formatting – click Manage Rules… - click New Rule… - cấu hình như sau:

Click OK – OK

Thực hiện tương tự với các range E4:E14, F4:F14, G4:G14, H4:H14 cho các ô KPI 2, 3, 4, 5 (thay =mysort =1 bằng =mysort =2 và tương tự)

Kiểm tra lại – khi click lên nút KPI 1 -> ô 01Calculation!D6 phải thể hiện số 1 – và tương tự cho các nút KPI khác.

5. Tạo bảng dữ liệu sắp xếp trong sheet 01Calculation

Trong sheet 01Dashboard - tạo thêm phần từ dòng 08 đến 108.

Cột B đến cột G: tạo giá trị trung gian để sắp xếp trong sheet 01Dashboard

• Cột B và cột C: lấy dữ liệu từ sheet Data
• Cột D: lấy dữ liệu của KPI được xác định bằng ô 01Calculation!D6 (dùng OFFSET)
• Cột E: mục đích là tạo ra 1 giá trị KPI duy nhất – không trùng
• Cột F: sắp xếp các giá trị của cột E từ cao xuống thấp bằng hàm LARGE
• Cột G: xếp hạng giá trị trong cột F so với cột E bằng hàm MATCH

Cột I đến cột N: tạo giá trị dùng để sắp xếp trong 01Dashboard (dùng OFFSET)

• Cột I: lấy tên sản phẩm trong sheet Data dựa trên cột G
• Cột J,K,L,M,N: lấy giá trị KPI tương ứng trong sheet Data dựa trên cột G

Kiểm tra lại – so sánh sheet 01Calculation của bạn với sheet Calculation của file mẫu xem có giống không

6. Hoàn tất các giá trị trong sheet 01Dashboard (dùng OFFSET)

• Cột A và B: lấy giá trị Thứ tự và Tên sản phẩm trong sheet 01Calculation dựa trên ô 01Calculation!D4
• Cột D, E, F, G, H: lấy giá trị KPI tương tứng trong sheet 01Calculation dựa trên ô 01Calculation!D4

Kết quả là bạn tạo được 1 Dashboard có: scroll bar kết hợp với nút sắp xếp rất linh hoạt cho mục đích báo cáo.

Chúc bạn thành công - nếu có thắc mắc gì xin bạn comment hoặc gửi email về support@cuocsongonline.com

Hẹn gặp lại vào bài tiếp theo của loạt bài về Dashboard này nhé :).

 

Continue Reading...

Sorting và scroll bar

Tạo scroll bar trong Excel

Trong Dashboard, scroll bar giúp người dùng xem được toàn bộ dữ liệu chỉ trên 1 màn hình.

Ví dụ:

Download file mẫu ở đây.

Hướng dẫn chi tiết:

1. Tạo 1 bảng dữ liệu thô về sản phẩm (sheet Data)

2. Tạo sheet Dashboard

3. Tạo sheet Calculation như file mẫu

4. Bật Developer Ribbon (trong Excel 2007)

• Click Office button –  Click Excel Options – Click Show Developer tab in the Ribbon – Click OK

5. Tạo Scroll Bar

• Click Developer – Click Insert – Click Scroll bar (Form Control)
• Drag chuột vẽ 1 Scroll bar khoảng 10 ô dọc
• Click chuột phải lên scroll bar mới vẽ – click Format Control – sang tab Control - chỉnh thông số như sau

 

– lưu ý Cell link chỉ đến tab Calculation

• Viết lệnh OFFSET trong các ô cần kéo dữ liệu (bạn có thể tham khảo file mẫu).

Ví dụ: =OFFSET(Data!E5,Calculation!$D$5,0,1,1)

Data!E5 -> xác định cột chứa dữ liệu

Calculation!$D$5 -> xác định giá trị hiện tại của scroll bar

Vậy là bạn đã hoàn tất 1 scroll bar :D.

Nguồn: http://chandoo.org

Continue Reading...

Tạo scroll bar trong Excel

Dashboard trong Excel

Dashboard là gì?

Như hình minh họa trên, dashboard là 1 dạng báo cáo dành cho cấp quản lý – cung cấp thông tin tổng quan trên 1 màn hình.

Tại sao cần dùng dashboard?

Đối với dạng biểu đồ bình thường trong Excel, thông tin thường nằm rải rác, phân tán, và khó gom lại thành một bức tranh tổng quan về một vấn đề. Đây chính là nhu cầu mà các xếp hay cần.

Làm thế nào để tạo 1 dashboard trong Excel?

Bạn cần sử dụng nhiều công cụ đồ thị và hàm riêng lẻ

Vì lý do đó, phần viết về dashboard này sẽ chia làm nhiều bài.

Hôm nay chỉ là giới thiệu sơ lược (teaser :D).

 

Hẹn gặp lại các bạn :).

Như đã hứa –

• Phần 1: Tạo Scroll bar trong Excel
• Phần 2: Kết hợp Scroll bar và Sorting

Continue Reading...

Dashboard trong Excel