HƯỚNG DẪN CÁCH VIẾT CÔNG THỨC LẬP BÁO CÁO CHI TIẾT TỰ ĐỘNG TRONG EXCEL

Trong công việc chúng ta thường gặp phải yêu cầu về lập báo cáo chi tiết để có thể theo dõi được nội dung cụ thể, chỉ liên quan riêng tới 1 đối tượng cụ thể cần báo cáo. Mục đích để tách riêng dữ liệu liên quan tới đối tượng cần báo cáo để giúp thống kê, phân tích những dữ liệu đó. Kỹ thuật lập báo cáo chi tiết cũng là một kỹ thuật cao, đòi hỏi nhiều yếu tố về:

• Trình bày báo cáo khoa học, đẹp mắt trong excel
• Cách để lấy nội dung báo cáo từ dữ liệu ban đầu ở bảng kê theo đúng điều kiện lập báo cáo

Sau đây chúng ta cùng tìm hiểu về 1 công thức giúp việc lập báo cáo chi tiết trở nên rất dễ dàng và thuận tiệnthông qua ví dụ sau:

Chúng ta có 1 bảng kê thông tin bán hàng của từng mặt hàng theo các ngày (Bảng A2:E12), trong đó đã được sắp xếp theo thứ tự cột Tên mặt hàng.

YÊU CẦU

Lập báo cáo chi tiết cho từng mặt hàng (vùng G1:J12), biết rằng khi thay đổi tên hàng thì báo cáo sẽ tự động thay đổi nội dung tương ứng với mặt hàng đó.

CÁCH THỰC HIỆN

BƯỚC 1: TẠO DANH SÁCH CHỌN TÊN MẶT HÀNG

Ở bảng kê bán hàng có 3 mặt hàng là Hàng A, Hàng B, Hàng C. Do đó chúng ta có thể tạo danh sách chọn tên mặt hàng tại ô H2 như sau:

Chọn tab Data / Data Validation

Trong bảng Data Validation:

• Mục Allow chúng ta chọn List
• Mục Data nhập nội dung như sau: Hàng A, Hàng B, Hàng C

Bấm ok

Khi đó chúng ta tại ô H2 chúng ta có thể chọn tên hàng theo danh sách gồm 3 mặt hàng này.

BƯỚC 2: CÔNG THỨC LẤY DỮ LIỆU VÀO BÁO CÁO

Căn cứ vào vị trí tên hàng xác định được ở trên, lấy các nội dung gồm: Ngày, Tên nhân viên, số lượng, thành tiền tương ứng theo mặt hàng đó => Sử dụng hàm OFFSET

Bởi vì kết quả trả về là 1 mảng dữ liệu nên chúng ta cần sử dụng tới công thức mảng.

Khi viết công thức mảng, chúng ta chọn toàn bộ vùng sẽ thể hiện kết quả (G5:J12), sau đó nhập công thức và kết thúc công thức này chúng ta sẽ dùng tổ hợp phím Ctrl + Shift + Enter

Cấu trúc hàm OFFSET gồm:

=OFFSET(reference, rows, cols, [height], [width])

• references: Tên hàng được chọn để lập báo cáo tại ô H2 trùng với tên hàng trong cột A => Có thể sử dụng hàm Index+Match để xác định vị trí nào ở cột A có kết quả đúng
• row: số dòng bắt đầu tính từ kết quả, ở đây tính tại dòng đầu tiên xuất hiện tên hàng nên row = 0
• cols: Số cột bắt đầu tính từ kết quả, ở đây lấy kết quả bắt đầu từ cột B tới các cột khác (cột B cách cột A là 1 cột) nên cols = 1
• Độ lớn của vùng cần lấy bởi hàm OFFSET sẽ là:

+) Height (số dòng): Bằng số dòng có Tên hàng tại H2 trùng với cột Tên hàng => Sử dụng hàm COUNTIF để đếm số lần xuất hiện tên hàng này

+) Weight (số cột): ở đây báo cáo chi tiết cần 4 cột, do đó lấy 4 cột tương ứng theo tên hàng này.

Trong đó:

• Hàm Index + Match được viết như sau:

INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0))

• Hàm COUNTIF được viết như sau:

COUNTIF($A$3:$A$12,$H$2)

Công thức hoàn chỉnh là:

=OFFSET(INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0)),0,1,COUNTIF($A$3:$A$12,$H$2),4)

Chúng ta có kết quả như sau:

BƯỚC 3: CÁCH BẪY LỖI

Khi sử dụng công thức OFFSET cho vùng bảng G5:J12, tên hàng là Hàng A, chúng ta thu được 4 dòng kết quả tương ứng theo bảng kê. Như vậy là kết quả đã đúng.

Nhưng vùng bảng G5:J12 có tới 8 dòng. Vậy những dòng thừa sẽ xuất hiện lỗi #N/A

Để bẫy lỗi này, không để lỗi hiển thị thì chúng ta làm như sau:

Sử dụng chức năng Conditional Formatting:

• Chọn toàn bộ bảng tính từ G5:J12
• Trong tab Home > Chọn Conditional formatting > Chọn New Rule
• Mục Select a Rule Type chọn Format only cells that conain
• Mục Format only cells with chọn Errors

Chúng ta sẽ thiết lập định dạng cho các ô có chứa lỗi trong vùng bảng này trong mục Format > Chọn màu chữ là màu trắng

Bấm ok và chúng ta sẽ thu được kết quả như sau:

NHỮNG ĐIỀU CẦN LƯU Ý

• Do hàm OFFSET lấy ra kết quả là 1 mảng liền nhau, do đó dữ liệu trong bảng kê (phần nguyên liệu để lập báo cáo) sẽ phải được sắp xếp theo thứ tự theo tên hàng
• Các cột kết quả trong báo cáo chi tiết sẽ lấy tương ứng theo vị trí trong bảng kê tương ứng theo vị trí cột tên hàng. Do đó tùy theo vị trí các vùng dữ liệu cần lấy trong báo cáo chi tiết mà chúng ta có thể thiết lập các tham số: Row, Cols, Height, Weight trong hàm offset cho phù hợp. Để thuận lợi hơn khi làm việc này thì chúng ta đặt các cột kết quả liền kề nhau, tránh việc cột điều kiện lập báo cáo (tên hàng) nằm xen giữa các cột kết quả.

Tải về file mẫu: http://bit.ly/2IhmiYq

Video hướng dẫn chi tiết

Xem thêm:

Hướng dẫn cách lập báo cáo tổng hợp tự động với Excel và VBA

Nếu bạn muốn tìm hiểu thêm các kiến thức khác về Excel, hãy tham gia ngay khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. 

HƯỚNG DẪN TÍNH LỢI SUẤT DÒNG TIỀN KHÔNG ĐỊNH KỲ BẰNG HÀM TÀI CHÍNH XIRR

Liên quan đến mô hình tài chính nói riêng và kế hoạch tài chính nói chung, việc hiểu tại sao dùng hàm tài chính XIRR và IRR là thực sự quan trọng. Dùng công thức đơn giản IRR trong Excel có thể là thiếu đầy đủ, khi công thức giả định tất cả kỳ hạn của mỗi dòng tiền trong chuỗi dòng tiền là bằng nhau (định kỳ). Thực tế, điều này không thường xuyên xảy ra, đặc biệt nếu chúng ta có khoản đầu tư chi phí ban đầu trả trước trong năm, trong khi các khoản lợi tức thu về thường nhận vào cuối năm. Với một chuỗi dòng tiền không định kỳ như vậy, sử dụng XIRR đem đến sự linh hoạt trong việc diễn tả từng kỳ hạn cụ thể tương ứng với từng dòng tiền trong chuỗi, kết quả là việc tính toán trở nên chính xác hơn.

Ý nghĩa việc sử dụng Tỷ suất hoàn vốn nội bộ (Internal Rate of Return – IRR)

Tỷ suất hoàn vốn nội bộ là tỷ suất chiết khấu mà tại đó Giá trị hiện tại ròng (Net Present Value – NPV) của tất cả dòng tiền trong tương lai của một khoản đầu tư bằng 0. Khi NPV của một khoản đầu tư bằng 0, không có nghĩa là khoản đầu tư đó tốt hay xấu, mà có nghĩa là chúng ta sẽ tính toán được tỷ suất sinh lời (lợi suất) của khoản đầu tư, chính bằng giá trị IRR.

Hạn chế trong việc sử dụng IRR

Khi ta dùng công thức “=IRR()” trong Excel, chúng ta đang tính toán dựa trên các khoảng thời gian bằng nhau (định kỳ) của mỗi dòng tiền (hay chính là mỗi ô trong bảng). Điều này sẽ mất đi tính chính xác khi chuỗi dòng tiền (chi/thu) bao gồm các dòng tiền không định kỳ hay vào các thời điểm khác nhau trong năm. Đây chính là lý do ta dùng XIRR thay cho việc sử dụng IRR. Việc sử dụng hàm IRR trong Excel cơ bản được thực hiện như sau:

Hướng dẫn dùng XIRR trong tính toán lợi suất dòng tiền không định kỳ

Khi ta dùng công thức “=XIRR()” trong Excel, ta có thể phản ánh được tính linh hoạt về kỳ hạn của mỗi dòng tiền mà trong đó mỗi dòng tiền (thu/chi) có thể phát sinh tại mỗi thời điểm khác nhau

Để thực hiện, ta sử dụng cú pháp: XIRR(values, dates, [guess])

Hai chuỗi giá trị bắt buộc (values và dates) trong công thức trên phản ánh:

Values_bắt buộc: Chuỗi Giá trị mỗi dòng tiền

Dates_bắt buộc: Chuỗi Ngày tương ứng của mỗi dòng tiền

Và mức độ chính xác phản ánh thông qua:

Guess_không bắt buộc: Nếu bỏ qua, guess giả định có giá trị là 0,1 (nghĩa là độ chính xác 10%)

Việc sử dụng hàm XIRR trong Excel cơ bản được thực hiện như sau:

Kết quả việc thực hiện hai hàm IRR và XIRR là:

Từ tính toán trên, ta thấy XIRR và IRR cho ra hai kết quả khác biệt rõ ràng. Qua đó cho thấy ý nghĩa của XIRR, với kết quả thu được phản ánh đúng đặc điểm về thời gian của mỗi dòng tiền trong chuỗi dòng tiền không định kỳ.

CÁCH DÙNG HÀM COUNT VÀ COUNTA ĐỂ ĐẾM Ô TRONG EXCEL

HÀM EXCEL COUNT – ĐẾM CÁC Ô CÓ SỐ:

Bạn sử dụng hàm COUNT trong Excel để đếm số ô có chứa các giá trị số.

CÚ PHÁP CỦA HÀM EXCEL COUNT NHƯ SAU:

COUNT(giá trị1, [giá trị2], …)
Trìn đó giá trị1, giá trị2,… là các tham chiếu ô hoặc các dãy trong đó bạn muốn đếm các ô bằng các con số.

Trong các phiên bản hiện đại của Excel 2016, Excel 2013, Excel 2010 và Excel 2007, chức năng COUNT chấp nhận tới 255 đối số. Trong các phiên bản Excel trước, bạn có thể cung cấp tới 30 ‘giá trị’.

Ví dụ: công thức sau trả về tổng số ô số trong dải A1:A100:

=COUNT(A1:A100)

Chú thích. Trong hệ thống Excel nội bộ, ngày tháng được lưu trữ dưới dạng số sê-ri và do đó hàm Excel COUNT sẽ đếm luôn cả ngày và thời gian.

SỬ DỤNG HÀM COUNT TRONG EXCEL – NHỮNG ĐIỀU CẦN NHỚ

Dưới đây là các quy tắc hoạt động của hàm Excel COUNT.

Nếu một (các) đối số của một công thức Excel Count là một tham chiếu hoặc dải ô, chỉ số, ngày tháng và thời gian được tính. Các ô có thể đế trắng hoặc chứa bất cứ dữ liệu nào nhưng không phải dữ liệu số.
Nếu bạn nhập các giá trị trực tiếp vào đối số Excel COUNT, các giá trị sau sẽ được tính: số, ngày tháng, thời gian, giá trị Boolean của TRUE và FALSE, và biểu diễn văn bản của các con số (nghĩa là một số được đặt trong dấu ngoặc kép như “5”).
Ví dụ: COUNT công thức sau đây trả về 4, vì các giá trị sau được tính: 1, “2”, 1/1/2016 và TRUE.

=COUNT(1, “apples”, “2”, 1/1/2016, TRUE)

Học Word Excel cơ bản

VÍ DỤ CÔNG THỨC COUNT TRONG EXCEL

Dưới đây là một vài ví dụ về việc sử dụng hàm COUNT trong Excel với các giá trị khác nhau.

Để đếm các ô với các giá trị số trong một dãy, sử dụng một công thức tính đơn giản như =COUNT(A2:A10). Ảnh chụp màn hình sau minh họa những loại dữ liệu nào được tính và những dữ liệu được bỏ qua:

Để đếm một vài phạm vi không tiếp giáp, chúng ta cần đưa tất cả chúng vào công thức Excel COUNT. Ví dụ: để đếm ô có số trong cột B và D, bạn có thể sử dụng công thức tương tự như sau:

=COUNT(B2:B7, D2:D7)

Mẹo:

• Nếu bạn muốn đếm những số thỏa mãn các tiêu chí nhất định thì hãy dùng COUNTIF hoặc COUNTIFS.
• Nếu ngoài số, bạn muốn đếm cả các ô chứ văn bản, giá trị logic và các lỗi thì sử dụng hàm COUNTA.
• Hàm Excel COUNTA – đếm các ô có chứa giá trị (không tính ô để trống)
• Hàm COUNTA trong Excel đếm số ô trong dải không tính ô để trống.

Các hàm thường dùng trong excel

CÚ PHÁP CỦA HÀM EXCEL COUNTA GIỐNG VỚI COUNT:

COUNTA (value1, [value2], …)
Trong đó value1, value2,… là tham chiếu ô hoặc dải ô nơi bạn muốn tính các ô không phải là ô trống.

Ví dụ, để đếm ô có giá trị trong dãy A1: A100, sử dụng công thức sau:

=COUNTA(A1:A100)

Để đếm các ô không rỗng trong một số phạm vi không liền kề, sử dụng một công thức COUNTA tương tự như sau:

=COUNTA(B2:B10, D2:D20, E2:F10)

Như bạn thấy, phạm vi được cung cấp cho công thức Excel COUNTA không nhất thiết cần phải có cùng kích thước, nghĩa là mỗi dải ô có thể chứa một số hàng và cột khác nhau.

Xin lưu ý rằng chức năng COUNTA của Excel đếm các ô có chứa bất kỳ loại dữ liệu nào, bao gồm:

• – Số
• – Ngày / thời gian
• – Giá trị văn bản
• – Các giá trị Boolean của TRUE và FALSE
• – Các giá trị lỗi như #VALUE hoặc # N/A
• – Chuỗi văn bản trống (“”)

Trong một số trường hợp, bạn có thể bị bối rối bởi kết quả của hàm COUNTA vì nó khác với những gì bạn nhìn thấy bằng mắt thường. Vấn đề là một công thức Excel COUNTA có thể đếm các ô mà trực quan nhìn có vẻ là ô rỗng, nhưng về mặt kỹ thuật chúng không phải ô rỗng. Ví dụ, nếu bạn vô tình gõ một khoảng trống trong một ô, ô đó cũng sẽ được đếm. Hoặc, nếu một ô có chứa một số công thức trả về một chuỗi rỗng, thì ô đó cũng sẽ được đếm.

Nói cách khác, các ô duy nhất mà hàm COUNTA không đếm là những ô hoàn toàn trống rỗng.

Ảnh chụp màn hình sau đây thể hiện sự khác biệt giữa hàm Excel COUNT và COUNTA:

Mẹo. Nếu bạn chỉ muốn nhanh chóng đếm các ô không bị bỏ trống trong một phạm vi đã chọn, chỉ cần nhìn vào Thanh trạng thái ở góc dưới cùng bên phải của cửa sổ Excel:

HÀM EXCEL COUNTIF – ĐẾM CÁC Ô ĐÁP ỨNG ĐIỀU KIỆN CHO SẴN:

Hàm COUNTIF được dùng để đếm các ô đáp ứng được một tiêu chí nhất định. Cú pháp của nó đòi hỏi 2 đối số bắt buốc:

COUNTIF (range, criteria)
Trong đối số đầu tiên, bạn xác định một phạm vi mà bạn muốn đếm các ô. Và trong tham số thứ hai, bạn chỉ định một điều kiện cần được đáp ứng.

Ví dụ: để đếm bao nhiêu ô trong phạm vi A2:A15 là “Apples”, bạn sử dụng công thức COUNTIF sau đây:

=COUNTIF(A2:A15, “apples”)

Thay vào đó nếu gõ tiêu chí trực tiếp vào công thức, bạn có thể nhập một tham chiếu ô như trong ảnh chụp màn hình sau:

HÀM EXCEL COUNTIFS – ĐẾM Ô PHÙ HỢP VỚI MỘT SỐ TIÊU CHÍ:

Hàm COUNTIFS tương tự như COUNTIF, nhưng nó cho phép chỉ định nhiều phạm vi và nhiều tiêu chí. Cú pháp của nó như sau:

COUNTIFS(criteria_range1, range1, [criteria_rang2, range2] …)

Hàm COUNTIFS đã được giới thiệu trong Excel 2007 và có sẵn trong tất cả các phiên bản về sau của Excel như 2010, 2013 và 2016.

Ví dụ: để đếm bao nhiêu “apples” (cột A) đã kiếm được 200 đô la và doanh số bán hàng nhiều hơn (cột B), bạn sử dụng công thức COUNTIFS sau:

=COUNTIFS(A2:A15,”apples”, B2:B15,”>=200″)

Và một lần nữa, để làm cho công thức COUNTIFS của bạn linh hoạt hơn, bạn có thể cung cấp các tham chiếu ô làm tiêu chí:

ĐẾM SỐ Ô TRONG MỘT DẢI (CÁC HÀM ROWS VÀ COLUMNS)

Nếu bạn cần phải tìm ra tổng số các ô trong một phạm vi hình chữ nhật, hãy sử dụng hàm ROWS và COLUMNS để trả về số hàng và cột trong một mảng, tương ứng:

=ROWS(range)*COLUMNS (range)

Ví dụ, để tìm ra có bao nhiêu ô có trong một phạm vi nhất định, như phạm vi A1:D7, sử dụng công thức sau:

=ROWS(A1:D7)*COLUMNS(A1:D7)

Xem thêm: Hàm đánh số thứ tự tự động trong excel với hàm COUNTA