Top 7 # Cách Xây Dựng Dashboard Trong Excel Xem Nhiều Nhất, Mới Nhất 3/2023 # Top Trend

Dashboard excel là một chức năng trong excel, giúp bạn tạo ra một dashboard (bảng điều khiển) có dạng báo cáo từ những con số đầu vào khô khan, giúp việc trình bày có tính sinh động và trực quan. Biết cách xây dựng dashboard trong excel là bạn đã sở hữu kỹ năng nâng cao tuyệt vời mà bao người mong muốn có được. Bài viết hướng dẫn này sẽ giúp bạn cách xây dựng dashboard trong excel.

Chức năng dashboard trong excel

Dashboard tuy trông giống như một báo cáo, nhưng dashboard không phải là một báo cáo.

Báo cáo chỉ thu thập và hiển thị dữ liệu ở một nơi duy nhất.

Dashboard thu thập dữ liệu từ một hay nhiều nhóm dữ liệu (có thể từ các báo cáo) để tạo ra các kết quả trực quan. Từ đó, dashboard giúp bạn ra được những quyết định đúng đắn, có cơ sở khoa học rõ ràng.

Các dashboard có thể là tĩnh hoặc tương tác. Dashboard tương tác thì gười dùng có thể thực hiện các lựa chọn và thay đổi chế độ xem và dữ liệu sẽ tự động cập nhật.

Cách xây dựng dashboard trong excel

Để xây dựng được một dashboard thực sự hiệu quả (dashboard có thể sử dụng được, không phải làm đi làm lại vì một số trục trặc kỹ thuật), người làm dashboard cần có một số kiến thức và kỹ năng excel nhất định.

Các kiến thức và kỹ năng này bao gồm:

Biết cách sử dụng các hàm tổng hợp dữ liệu như SUMIFS, COUNTIFS, SUMPRODUCTS

Các kĩ năng sử dụng và lồng ghép các hàm

Các kĩ năng sử dụng Shapes, vẽ đồ thị và sử dụng Sparklines trong Excel.

Nếu bạn đã trang trị đầy đủ các kiến thức và kỹ năng trên, bạn có thể bắt đầu đến với cách xây dựng dashboard trong excel.

1. Xác định mục đích

– Bạn phải biết chính xác bạn muốn gì tại dashboard, hình dung một cách rõ ràng mục đích mà dashboard được tạo ra, phục vụ cho điều gì, cần lấy dữ liệu gì hoặc từ dữ liệu đã có thể cho ra dashboard loại gì.

– Không chỉ là xác định “loại” dashboard, bạn cũng cần chắc chắn về tính thường xuyên của dashboard: dashboard sử dụng một lần hay dashboard cần được cập nhật lại, với mức độ thường xuyên như thế nào?

– Bạn cũng cần phải biết đối tượng hướng đến của dashboard: đó là khách hàng, quản lý, đối tác hay là ai. Điều này sẽ vạch đúng con đường bạn cần đi.

– Một lưu ý mà bạn cũng nên để tâm là phiên bản Microsoft Office mà đối tượng của dashboard sử dụng. Ví dụ như nếu đối tượng sử dụng Excel 2003 thì khi bạn sử dụng hàm IFERROR sẽ gây ra lỗi do phiên bản Excel 2003 chưa hỗ trợ hàm excel n

2. Xác định và xử lý dữ liệu nguồn

Lấy dữ liệu

Lý tưởng nhất là khách hàng cung cấp dữ liệu excel cho bạn và bạn chỉ cần xử lý, làm dashboard.

Nếu bạn có dữ liệu nhưng lại ở dạng CSV hoặc tệp văn bản, bạn có thể xử lý nó qua định dạng excel.

Nếu bạn có quyền truy cập vào cơ sở dữ liệu lưu trữ dữ liệu, bạn có thể tạo kết nối và cập nhật gián tiếp.

Làm sạch dữ liệu

Dữ liệu mà bạn nhận được có thể chưa được “sạch”, nên bạn cần thực hiện thanh lọc dữ liệu trước xây dựng dashboard.

Bạn có thể cần thực hiện nhiều việc: loại bỏ các khoảng trắng hàng đầu, dấu hoặc kép, tìm và xóa các bản sao, xóa khoảng trống và lỗi,… Trong một số trường hợp, bạn thậm chí có thể cần cơ cấu lại dữ liệu như tạo bảng Pivot. Mọi thứ tùy thuộc vào dữ liệu mà bạn nhận được.

3. Lên phác thảo cho dashboard

Vẽ phác thảo là bước cần thiết cho bất kỳ điều gì, kể cả dashboard. Bước này giúp bạn xác định được bạn muốn gì và sẽ có gì, một cách gần như chính xác và chắn chắn.

Bạn có thể phác theo ra giấy, hay một số công cụ (ví dụ như powerpoint), chỉ cần đảm bảo được việc lên ý tưởng này có được bản phác thảo tốt nhất.

4. Thực hiện tạo dashboard

Khi đã hoàn thành xong 3 bước trên, bước 4 trở nên cực kỳ dễ dàng, bạn chỉ cần sử dụng các công cụ có excel, kết hợp với các hàm trong excel để tạo nên một dashboard hoàn chỉnh.

Kết

Nhìn chung, dashboard đem đến cho bạn các nhìn trực quan sinh động, hô biến các con số khô khan để cho ra một bản trình bày “biết nói”, giúp bạn đưa ra quyết định một cách chính xác. Do đó, nắm được cách xây dựng dashboard trong excel là một kỹ năng quan trọng mà bạn nên thuần thục.

Điều gì thực sự khiến cho Microsoft Excel giữ vững được chỗ đứng top đầu trong việc kiểm soát dữ liệu doanh nghiệp?

Excel tích hợp vô cùng nhiều các tính năng mà các phần mềm khác đã đơn giản hóa cho người dùng.

Excel tạo ra một hệ thống liên kết rất nhiều người có thể sử dụng

Dữ liệu Excel là nền tảng cho mọi hệ thống kiểm soát dữ liệu khác

Excel tiết kiệm chi phí và thời gian đào tạo cho người dùng cá nhân và doanh nghiệp

Chính vì thế rất nhiều doanh nghiệp và nhân viên văn phòng khẳng định rằng: ” Dù bạn có sử dụng qua rất nhiều công cụ hỗ trợ kiểm soát khác nhau thì cuối cùng bạn vẫn phải quay về với Excel “. Vậy thì sao bạn không nắm vững các chức năng của Excel để không tốn thời gian và công sức ngay từ đầu?

Khóa học Xây dựng dữ liệu và lập báo cáo Dashboard trên Excel cung cấp những thao tác chuẩn, nền tảng, có tính ứng dụng cao. Để tạo ra một báo cáo đáp ứng mọi yêu cầu của người dùng. Với mục tiêu học thật, học một lần và dùng lâu dài. Khóa học sẽ giúp người học sử dụng và giải quyết nhiều bài toán trong doanh nghiệp. Đồng thời có thể tạo ra một hệ thống dữ liệu nhiều người có thể dùng.

Dữ liệu nhân sự: phù hợp với học viên quen thuộc về nhân sự với ví dụ xuyên suốt về khung năng lực.

Dữ liệu kinh doanh: dành cho học viên quen thuộc với số liệu kinh doanh.

Tất cả những ai có nhu cầu biết cách xây dựng cấu trúc dữ liệu trên Excel để ra được các số liệu phân tích trong 1 phút đều cần nắm vững các kiến thức trong khóa học này.

Xây dựng dữ liệu chuẩn, nhẹ, dễ dùng từ nhu cầu thực tế

Lập báo cáo trực quan, sinh động, không lỗi

Làm chủ kiến thức, sử dụng lâu dài

Thành thạo các kỹ năng trong lớp Chuyên gia Excel Phần 1 và Phần 2

Nguyễn Hòa Anh – Founder Officelab

Hình thức lớp học: Online qua video bài giảng có sẵn

Ưu đãi 50% cho học viên đăng kí trong tháng 8, chỉ còn 600.000đ

Ưu đãi 60% cho học viên đã đăng kí các khóa học trước đó, chỉ còn 480.000đ

Ngân hàng Ngoại Thương (Vietcombank)

CTK: Nguyễn Hòa Anh

STK: 1014446284

Chi nhánh Thành Công

Email: tinhocmos.edu.vn@gmail.com

Hotline: 0914444343 (Ms.Quỳnh Anh)

Address: phòng 1206, tầng 12, nhà A, đại học Ngoại Thương, 91 Chùa Láng, Đống Đa, Hà Nội.

Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn bạn cách tạo biểu đồ hình tròn, thêm hoặc xóa chú thích biểu đồ, đặt tên cho biểu đồ, hiển thị số phần trăm, phóng to hoặc xoay biểu đồ hình tròn.

Cách tạo biểu đồ hình tròn trong Excel:

1. Chuẩn bị dữ liệu nguồn để tạo biểu đồ.

Không giống loại biểu đồ khác, biểu đồ hình tròn trong Excel đòi hỏi nguồn dữ liệu sắp xếp thành một cột hoặc một hàng. Vì vậy mỗi biểu đồ chỉ thể hiện một loại dữ liệu.

Nói chung, một biểu đồ hình tròn đầy đủ bao gồm:

Chỉ một loại dữ liệu được vẽ trên biểu đồ.

Tất cả các giá trị dữ liệu lớn hơn không.

Không có hàng rỗng hoặc cột rỗng.

Không có nhiều hơn 7 – 9 dữ liệu thành phần, bởi vì quá nhiều phần sẽ khiến biểu đồ của bạn khó hiểu.

Đối với hướng dẫn này, chúng tôi sẽ tạo một biểu đồ hình tròn từ dữ liệu sau:

2. Chèn biểu đồ hình tròn vào trang tính hiện tại

Ngay sau khị bạn sắp xếp nguồn dữ liệu, chọn nó, đi đến thẻ Insert và chọn loại biểu đồ bạn muốn (chúng tôi sẽ phân loại biểu đồ hình tròn sau).

Trong ví dụ này, chúng tôi tạp kiểu biểu đồ hình tròn 2D thông thường nhất:

Chú ý. Chèn tiêu đề cột hoặc hàng nếu bạn muốn chúng tự động xuất hiện trong biểu đồ của bạn.

3. Chọn kiểu biểu đồ hình tròn (tùy chọn)

Khi biểu đồ hình tròn vừa chèn vào trang tính, bạn có thể đi đến nhóm Design, thử nhiều kiểu biểu đồ khác nhau trong nhóm Charts và chọn kiểu phù hợp nhất với dữ liệu của bạn.

Kiểu biểu đồ mặc định (kiểu 1) trong Excel 2013 được chèn vào trang tính như sau:

Cách tạo nhiều kiểu biểu đồ hình tròn khác nhau trong Excel:

Biểu đồ hình tròn 2D

Đây là tiêu chuẩn và là dạng phổ biến nhất. Vào tab

Biểu đồ hình tròn 3D

Biểu đồ hình tròn 3D tương tự 2D, nhưng nó sẽ hiển thị dưới dạng khối 3 chiều.

Miếng của biểu đồ hình tròn (Pie of Pie chart) và thanh của biểu đồ hình tròn (Bar of Pie chart)

Nếu biểu đồ của bạn có quá nhiều phần nhỏ, bạn có thể muốn tạo Pie of Pie chart. Phần này được gộp từ nhiều phần nhỏ hơn và sẽ hiển thị phần thêm bên ngoài.

Bar of Pie chart tương tự như Pie of Pie chart, ngoại trừ phần được chọn được hiển thị thành cột (thay vì thành hình tròn).

Chọn loại dữ liệu cho biểu đồ phụ.

Chọn loại dữ liệu cho biểu đồ phụ

Để tự chọn các loại dữ liệu tạo thành biểu đồ phụ, thực hiện các bước sau:

2. Trên bảng Format Data Series, trong Series Options, chọn một trong các tùy chọn sau trong danh sách Split Series By:

Value – cho phép bạn chỉ định giá trị tối thiểu được chuyển sang biểu đồ bổ sung.

Percentage value – nó giống Value, nhưng ở đây bạn cần chỉ định tỷ lệ phần trăm tối thiểu.

Custom – cho phép bạn tự chọn bất kỳ phần nào trên biểu đồ hình tròn, sau đó chỉ định đặt nó vào biểu đồ chính hoặc phụ.

Trong hầu hết các trường hợp, việc đặt ngưỡng tỷ lệ phần trăm là sự lựa chọn hợp lý nhất, nhưng mọi thứ tùy vào dữ liệu nguồn và sở thích cá nhân của bạn. Hình sau thể hiện sự chia tách chuỗi dữ liệu theo Percentage value:

Ngoài ra, bạn có thể cấu hình các cài đặt sau:

Thay đổi độ rộng cho mỗi phần biểu đồ. Gap Width đại diện cho độ rộng của một phần biểu đồ hình tròn tương ứng với 1% trong biểu đồ phụ. Để thay đổi độ rộng, kéo thanh trượt hoặc gõ con số chính xác vào hộp tỷ lệ phần trăm.

Thay đổi kích thước của biểu đồ phụ. Những con số trong hộp Second Plot Size đại diện cho tỷ lệ trong biểu đồ phụ tương ứng với 1% trong biểu đồ chính. Kéo thanh trượt để tăng hoặc giảm kích thước biểu đồ phụ, hoặc gõ con số chính xác vào hộp phần trăm.

Nếu bạn có nhiều hơn một chuỗi dữ liệu, bạn có thể sử dụng biểu đồ Doughnut thay cho biểu đồ hình tròn. Tuy nhiên, trong biểu đồ Doughnut, thật khó để ước tính tỷ lệ giữa các phần tử trong các chuỗi khác nhau, và đó là lý do tại sao nên sử dụng các loại biểu đồ khác, chẳng hạn biểu đồ thanh ( bar chart) hoặc biểu đồ cột ( column chart).

Thay đổi kích thuớc trong biểu đồ Doughnut

1. Nhấp chuột phải vào bất kỳ chuỗi dữ liệu nào trong biểu đồ Doughnut, chọn tùy chọn

2. Trong bảng , đến thẻ tùy chọn thay đổi kích thước lỗ bằng cách di chuyển thanh trượt bên dưới Series Options, Doughnut Hole Size hoặc nhập phần trăm trực tiếp vào ô số.

Tùy chọn và nâng cao biểu đồ hình tròn:

Cách thêm nhãn cho dữ liệu trong biểu đồ hình tròn

Trong ví dụ biểu đồ hình tròn này, chúng ta sẽ thêm nhãn cho tất cả điểm dữ liệu. Để làm điều này, hãy nhấp vào nút Chart Elements ở góc trên bên phải của biểu đồ và chọn Data Labels.

Ngoài ra, bạn cũng có thể muốn thay đổi vị trí các nhãn này bằng cách nhấn vào mũi tên kế bên Data Labels. So với các dạng biểu đồ khác, biểu đồ hình tròn cung cấp nhiều lựa chọn nhất về vị trí tiêu đề:

Nếu bạn muốn hiển thị nhãn dữ liệu bên ngoài hình tròn, chọn Data Callout:

Chú ý. Nếu bạn đặt nhãn bên trong miếng cắt biểu đồ, màu đen mặc định của nhãn có thể khó đọc trên nền tối như màu xanh đậm trong ví dụ. Tốt nhất, bạn hãy thay đổi màu nhãn (nhấp vào nhãn, đến thẻ ); hoặc bạn có thể thay đổi màu của từng miếng cắt biểu đồ.

Nếu biểu đồ hình tròn của bạn có nhiều hơn ba lát cắt, bạn có thể muốn đặt tên trực tiếp thay vì vừa phải xem chú thích vừa phải đối chiếu với biểu đồ.

Cách nhanh nhất để thực hiện điều này là chọn một trong những cách chú thích lại biểu đồ trong thẻ Layout 1 và 4 là những cách có nhãn dữ liệu.

Để có nhiều lựa chọn hơn, bấm vào nút Chart Elements (dấu chéo màu xanh lá) ở góc trên bên phải của biểu đồ, nhấp vào mũi tên kế bên Data Labels, và chọn More options… Bảng Format Data Labels sẽ mở ra ơ bên phải trang tính. Mở thẻ Label Options, đánh dấu chọn Category Name.

Ngoài ra, bạn có thể sử dụng những tùy chọn sau:

Trong danh sách Separator, chọn cách tách dữ liệu hiển thị trên nhãn (sang dòng mới New Line trong ví dụ)

Duới Label Position, chọn nơi đặt nhãn dữ liệu (Outside End trong ví dụ)

Ghi chú. Bây giờ bạn đã thêm nhãn dữ liệu vào biểu đồ hình tròn, bảng chú thích trở nên dư thừa và bạn có thể xóa nó bằng cách nhấp vào nút Chart Elements, bỏ chọn hộp Legend.

Cách hiển thị tỷ lệ phần trăm trên biểu đồ hình tròn:

Khi dữ liệu nguồn được vẽ trên biểu đồ là phần trăm, % sẽ tự động xuất hiện trên các nhãn dữ liệu ngay khi bạn bật tùy chọn Data Labels trong Chart Elements, hoặc chọn tùy chọn Value trong bảng Format Data Labels, như được minh họa trong biểu đồ trên.

Nếu dữ liệu nguồn của bạn là số, bạn có thể định dạng nhãn hiển thị giá trị gốc hoặc hoặc tỷ lệ phần trăm, hoặc cả hai.

Nhấp chuột phải vào bất kỳ phần nào trên biểu đồ, chọn Format Data Labels.

Trong bảng Format Data Labels, đánh dấu chọn Value hoặc Percentage hoặc cả hai như trong ví dụ. Phần trăm sẽ được tính tự động, tương ứng với toàn bộ hình tròn là 100%.

Tách rời biểu đồ hình tròn hoặc tách từng phần của biểu đồ:

Để nhấn mạnh từng phần riêng lẻ của biểu đồ hình tròn, bạn có thể tách rờinó, ví dụ tách tất cả các phần ra riêng biệt. Hoặc bạn có thể nhấn mạnh bằng cách kéo một phần ra khỏi phần còn lại của biểu đồ.

Tách rời toàn bộ biểu đồ hình tròn:

Cách nhanh nhất để tách là nhấp vào biểu đồ hình tròn để tất cả các phần được chọn, kéo chúng ra khỏi trung tâm biểu đồ bằng chuột.

Hoặc làm theo những bước sau:

1. Nhấp phải vào bất kỳ miếng nào, chọn Format Data Series.

2. Trong bảng Format Data Series, mở thẻ Series Options, kéo thanh trượt Pie Explosion để tăng hoặc giảm khoảng cách giữa hai miếng biểu đồ. Hoặc, gõ khoảng cách trực tiếp vào hộp phần trăm.

Kéo một miếng riêng lẻ ra khỏi biểu đồ hình tròn:

Để thu hút sự chú ý của người dùng đến một phần biểu đồ cụ thể, bạn có thể di chuyển nó ra khỏi phần còn lại của biểu đồ hình tròn.

Cách nhanh nhất là chọn nó và kéo ra xa bằng chuột. Để chọn một phần biểu đồđơn lẻ, nhấp vào nó, và sau đó nhấp vào nó một lần nữa để chỉ phần này được chọn.

Ngoài ra, bạn có thể chọn phần biểu đồ mà bạn muốn di chuyển ra, nhấp chuột phải vào nó, chọn Format Data Series. Sau đó, đi đến Series Options từ bảng Format Data Series, thiết lập Point Explosion:

Chú ý. Nếu bạn muốn tách ra nhiều phần, bạn sẽ phải lặp lại quá trình cho từng phần một. Không thể kéo một nhóm các các phần cắt khác nhau ra khỏi biểu đồ, bạn có thể “Explode” toàn bộ chúng

Khi tạo biểu đồ hình tròn trong Excel, thứ tự các loại dữ liệu được xác định bởi thứ tự dữ liệu trên bảng tính. Tuy nhiên, bạn có thể xoay đồ thị 360 độ để quan sát từ nhiều góc độ khác nhau.

Ta thực hiện theo các bước sau:

1. Nhấp phải vào bất cứ phần nào của biểu đồ và chọn Format Data Series.

2. Trong bảng Format Data Series, dưới Series Options, kéo thanh trượt Angle of First Slice từ 0 tới xoay theo chiều kim đồng hồ. Hoặc, gõ con số cụ thể vào ô.

3D Rotation cho biểu đồ hình tròn 3D:

Đối với biểu đồ hình tròn 3D, chúng ta có nhiều sự lựa chọn hơn. Để thực hiện tính năng 3D-rotation, nhấp phải vào bất kỳ phần nào trong biểu đồ và chọn 3D-rotation.

Hộp thoại Format Chart Area với các tùy chọn 3D Rotations sau:

Xoay quanh theo trục ngang trong X Rotation

Xoay quanh theo trục dọc trong Y Rotation

Độ nghiêng (lĩnh vực xem trên biểu đồ) trong Perspective

Chú thích. Các biểu đồ hình tròn Excel có thể xoay quanh các trục ngang và dọc, nhưng không gồm trục sâu (trục Z). Vì vậy, bạn không thể chọn độ xoay trong hộp Z Rotation.

Khi bạn nhấp vào mũi tên lên và xuống trong hộp xoay, biểu đồ trong Excel sẽ xoay ngay lập tức để phản ánh những thay đổi. Vì vậy, bạn có thể tiếp tục nhấp vào các mũi tên để di xoay biểu đồ theo cho đến khi nó ở đúng vị trí.

Sắp xếp biểu đồ hình tròn theo kích thước:

Theo nguyên tắc chung, biểu đồ hình tròn dễ hiểu hơn khi các phần được sắp xếp từ lớn đến nhỏ. Cách nhanh nhất để làm điều này là sắp xếp dữ liệu nguồn trên bảng tính. Nếu chưa sắp xếp dữ liệu nguồn, bạn có thể xếp lại cách sau.

1. Tạo một PivoteTable từ bảng nguồn của bạn. Các bước chi tiết được giải thích trong hướng dẫn Bảng Excel Pivot dành cho người mới bắt đầu.

2. Đặt tên các loại trong Row field và dữ liệu số trong Values field. Kết quả của PivotTable sẽ giống như thế này:

3. Nhấp vào AutoSort bên cạnh Row Labels, sau đó nhấp vào More Sort Options…

4. Trong hộp thoại Sort, chọn sắp xếp dữ liệu theo Value theothứ tự tăng dần hoặc giảm dần:

5. Tạo biểu đồ hình tròn từ PivotTable và làm mới nó bất cứ khi nào cần.

Nếu bạn không hài lòng với màu mặc định của biểu đồ hình tròn Excel, bạn có thể:

Chọn màu sắc cho từng phần riêng biệt

Như bạn thấy trong hình ở trên, việc lựa chọn màu cho biểu đồ trong Excel khá hạn chế, và nếu bạn định tạo một biểu đồ hợp thời trang và lôi cuốn, bạn có thể chọn từng phần riêng lẻ. Ví dụ: nếu bạn đã chọn đặt nhãn dữ liệu bên trong các phần, chữ đen có thể khó đọc trên các màu tối.

Để thay đổi màu sắc của một miếng nào đó, hãy nhấp vào phần đó và sau đó nhấp lại vào đó để chỉ chọn một miếng cắt này. Chuyển tới tab Format, nhấp vào Shape Fill và chọn màu bạn muốn:

Định dạng một biểu đồ hình tròn trong Excel:

Khi bạn tạo một biểu đồ tròn trong Excel để trình bày hoặc xuất sang các ứng dụng khác, bạn có thể muốn mang lại cho nó một cái nhìn bắt mắt.

Để truy cập vào tính năng định dạng, hãy nhấp chuột phải vào bất kỳ phần nào của biểu đồ và chọn Format Data Series từ trình đơn. Format Data Series sẽ xuất hiện ở bên phải bảng tính của bạn, bạn chuyển sang tab Effects (phần thứ hai) và thử các tùy chọn Shadow, Glow và Soft Edges.

Có nhiều tùy chọn có sẵn trên tab Format chẳng hạn như:

Thay đổi kích thước biểu đồ tròn (chiều cao và chiều rộng)

Thay đổi hình dạng và màu

Sử dụng các hiệu ứng hình dạng khác nhau

Sử dụng các kiểu WordArt cho các phần chú thích

Và hơn thế nữa

Chúng ta tiếp tục tìm hiểu cách tự xây dựng công thức đọc số tiền bằng chữ trong excel. Trong bài viết tại phần 1, chúng ta đã tìm hiểu xong về Sự phức tạp của việc đọc số tiền bằng chữ theo tiếng việt và Các bước xây dựng hàm đọc số bằng chữ tiếng việt, trong đó đã tìm hiểu tới phần đọc thành chữ cho hàng tỷ. Các bạn có thể xem lại bài viết tại địa chỉ:

Hướng dẫn cách tự xây dựng công thức đọc số tiền bằng chữ trong excel từ A đến Z – Phần 1

Tải về file mẫu tại địa chỉ: http://bit.ly/2Upapa1

Các bước xây dựng hàm đọc số bằng chữ tiếng việt

Đọc vị trí hàng trăm triệu

Tại vị trí này, việc đọc giống như đọc ở vị trí hàng trăm tỷ. Tuy nhiên có thêm một số yêu cầu:

Phần bên trái (từ vị trí hàng trăm triệu tới hàng trăm tỷ) có số nào lớn hơn 0 không. Nếu không có thì bằng rỗng, nếu có thì xét thêm trường hợp sau:

Nếu có bất kỳ số nào hàng triệu, mà tại vị trí F3 (hàng trăm triệu) bằng 0 thì đọc là “không trăm“

Còn lại tùy theo số tại F3 mà đọc thành chữ tương ứng từ “một” đến “chín“, kết hợp thêm chữ ” trăm” (có dấu cách trước chữ trăm)

Công thức đọc số tiền bằng chữ tại vị trí hàng trăm triệu:

F5=IF(SUM(C3:F3)=0,””,IF(SUM(F3:H3)=0,””,IF(F3=0,”không trăm”,CHOOSE(F3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Trong đó:

SUM(C3:F3)=0 để xét phần từ vị trí hàng trăm triệu trở về bên trái (hàng trăm tỷ) có số nào lớn hơn 0 không

Đọc vị trí hàng chục triệu

Vị trí này đọc giống như hàng chục tỷ. Công thức tại vị trí hàng chục triệu:

Trong đó:

SUM(C3:G3)=0 để xét từ vị trí hàng chục triệu trở về bên trái (hàng trăm tỷ) có số nào lớn hơn 0 không

Còn lại thì đọc theo số hàng chục từ “mười” đến “chín mươi“

Đọc vị trí hàng triệu

Vị trí này đọc giống như hàng tỷ. Công thức tại vị trí hàng triệu:

Trong đó:

SUM(F3:H3)=0 xét toàn bộ số hàng triệu, nếu đều = 0 thì không đọc hàng triệu

Còn lại trường hợp có số hàng triệu thì đọc giống như cách đọc hàng tỷ, nhưng thay chữ “tỷ” bằng chữ “triệu” (vị trí H4) có kèm thêm dấu cách.

Như vậy kết quả hàng triệu như sau:

Đọc vị trí hàng nghìn

Các vị trí ở hàng nghìn: trăm nghìn, chục nghìn, nghìn được đọc giống như ở hàng triệu. Điểm chú ý duy nhất là đơn vị ở hàng nghìn có 2 cách đọc: ngàn/ nghìn tùy theo địa phương. Do đó cần tạo danh sách chọn đơn vị này tại dòng 4, cột K (chữ số đơn vị hàng nghìn)

Công thức tại các vị trí:

I5=IF(SUM(C3:I3)=0,””,IF(SUM(I3:K3)=0,””,IF(I3=0,”không trăm”,CHOOSE(I3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Như vậy kết quả hàng nghìn như sau:

Đọc vị trí hàng đơn vị

Gồm các chữ số thể hiện tiền ” lẻ“, gồm trăm đồng, chục đồng, đồng.

Ở hàng đơn vị có phân ra 2 loại là “Lẻ/chẵn” và cách đọc cũng có sự khác nhau.

Có bất kỳ số nào ở hàng đơn vị thì sẽ là “lẻ” và đọc chi tiết từng đồng

Không có bất kỳ số nào ở hàng đơn vị (đều = 0) thì đọc là “đồng chẵn”

Kết thúc đọc tiền bằng chữ là dấu “./.” để thể hiện đã kết thúc đoạn bằng chữ.

Như vậy phần hàng trăm và hàng chục không ảnh hưởng gì, chỉ ảnh hưởng ở hàng đơn vị cuối cùng.

Đọc chữ số hàng trăm đồng:

L5=IF(SUM(C3:L3)=0,””,IF(SUM(L3:N3)=0,””,IF(L3=0,”không trăm”,CHOOSE(L3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Trong đó:

N3=0 là khi ngoài 2 logic trên sẽ là đồng lẻ (không đọc chữ “lẻ”, chỉ đọc là “đồng”), số 0 sẽ không đọc.

Các số còn lại thì đọc giống các chữ số hàng đơn vị khác. Kết thúc với dấu “./.“

Kết quả cuối cùng như sau:

Xử lý viết hoa chữ cái bắt đầu

Có 1 nguyên tắc là phải viết hoa chữ cái bắt đầu trong cả dãy số tiền bằng chữ. Tuy nhiên chúng ta phải xác định được tại vị trí nào là bắt đầu, và tại vị trí đó sẽ tách ký tự đầu tiên ra để viết hoa.

Logic trên có thể diễn giải như sau (viết công thức trên dòng 6):

Nếu tại vị trí đọc ra chữ tương ứng là ô trống (giá trị rỗng) thì trả về giá trị rỗng (không xử lý)

Nếu tại vị trí đó không rỗng thì xét tổng số ô trống ở phía trước nó có bằng với vị trí số đó không, nếu đúng thì sẽ xử lý viết hoa chữ cái đầu tiên, nếu không thì trả về giá trị đọc bằng chữ đã có.

Tại C6 ta có công thức:

IF(C5=””,””,IF(COUNTIF(B5:B5,””)=C2,UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)-1),C5))

Trong đó:

C5 là vị trí kết quả đã đọc bằng chữ cần xử lý

COUNTIF(B5:B5,””) là đếm số ô trống trong vùng từ B5. Với các vị trí khác sẽ bắt đầu đếm từ B5 và tăng dần lên (sang bên phải)

UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)-1) là hàm xử lý viết hoa chữ cái đầu tiên trong nội dung ở C5

Xem lại bài viết: Hướng dẫn cách chỉ viết hoa chữ cái đầu tiên trong Excel

Fillright công thức tại C6 tới N6 (trong đó chú ý vị trí B5 đầu tiên trong hàm COUNTIF phải giữ nguyên, không được thay đổi; có thể cố định vị trí này với phím F4)

Kết quả thu được như sau:

(nên làm bước này trước, bởi làm ở bước sau thì hàm xử lý viết hoa sẽ rất dài, dễ bị rối)

Ghép các từ đã đọc thành kết quả số tiền bằng chữ

Từng vị trí chữ số đã được đọc, việc còn lại chỉ đơn giản làm ghép lại các từ này là xong.

Ở đây chúng ta có 12 vị trí nên sẽ phải ghép theo nguyên tắc sau:

Mỗi vị trí (mỗi ô ở dòng 6) sẽ nối với vị trí bên cạnh nó và phải thêm 1 dấu cách ở giữa 2 chữ này.

Dùng hàm TRIM để loại bỏ những dấu cách thừa trong kết quả ghép được.

Tại ô B2 chúng ta có kết quả như sau:

B2=TRIM(C6&” “&D6&” “&E6&” “&F6&” “&G6&” “&H6&” “&I6&” “&J6&” “&K6&” “&L6&” “&M6&” “&N6)

Nếu việc ghép như trên là dài và mất nhiều công, bạn có thể tìm hiểu về hàm TEXTJOIN ở các phiên bản Excel2016 trở đi, công việc sẽ đơn giản hơn rất nhiều.

Tham khảo bài viết: GHÉP NỐI KÝ TỰ VỚI HÀM CONCAT VÀ TEXTJOIN TRONG EXCEL

Như vậy là chúng ta đã hoàn thành được hàm đọc số bằng chữ trong Excel rồi. Logic tuy dài dòng nhưng hoàn toàn có thể phân tích được, sử dụng những hàm Excel mà ai cũng biết. Bài toán này sẽ giúp các bạn luyện tập rất tốt cho việc phân tích logic, phát triển logic thành công thức, hàm trong Excel.

Ngoài ra tính ứng dụng của hàm này rất đơn giản, chúng ta chỉ cần làm như sau:

Gán số cần đọc vào vị trí Nhập số

Gán kết quả đọc tiền bằng chữ vào vị trí kết quả cần đọc.