Công thức 3D thống kê đơn giản trong Excel

 Trong nhiều tình huống bố cục dữ liệu, file excel có nhiều sheet nhưng cùng số dòng, số cột hoặc cùng vị trí thống kê ở các sheet. Khi đó người dùng cần tính tổng từ các sheet

Giả sử tình huống dữ liệu tổng hợp chi phí có cấu trúc giống nhau gồm 4 cột (các chỉ tiêu) và các dòng (các bộ phận), mỗi năm tương ứng với 1 sheet, tên sheet chính là số năm

HƯỚNG DẪN LẬP CÔNG THỨC 3D TRONG EXEL VỚI CÁC HÀM THỐNG KÊ ĐƠN GIẢN

- Tính tổng từ các sheet cùng cấu trúc số dòng và số cột

=SUM('2018:2022'!B2)

Trong đó: '2018:2022' để chỉ danh sách các sheet từ 2018 đến 2022, B2 là tọa độ ô Chi phí kế hoạch cho bộ phận KD ở các sheet năm => Thực hiện kéo công thức xuống dưới cho các ô còn lại

- Tương tự như vậy sẽ có các công thức tính Trung bình, lớn nhất, nhỏ nhất

=AVERAGE('2018:2022'!B2)

=MAX('2018:2022'!B2)

=MIN('2018:2022'!B2)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Các hàm thống kê khác trong Excel (Phần 2)

  Trong Excel, ngoài các hàm thống kê cơ bản (SUM, COUNT, MAX, MIN,...) và các hàm thống kê có điều kiện (SUMIF, SUMIFS, COUNTIFS,...) thì còn có các hàm thống kê khác:

COUNTA(value1,value2,...)

Là hàm đếm tất cả các giá trị (kể cả giá trị lỗi) nhưng trừ ô Blank. Trường hợp ô có chứa giá trị rỗng dạng "" do công thức trả về hoặc do copy paste thì đều được hàm COUNTA đếm

Ví dụ từ ô A1:A5 chứa các giá trị text, number, lỗi, logic (TRUE/FALSE) thì đều được hàm COUNTA đếm

MAXA(value1,value2,...)

Là tìm giá trị lớn nhất và có xét đến cả giá trị là Text và Logic

- Giá trị Text tương đương với 0

- Giá trị TRUE tương đương với 1

- Giá trị FALSE tương đương với 0

Ví dụ hàm MAXA tham chiếu vào vùng là các con số <1, nhưng trong vùng có giá trị TRUE nên được hiểu tương đương với 1 và lấy 1 là kết quả. Còn nếu dùng hàm MAX thì kết quả là 0.9

MINA(value1, value2, ...)

Là hàm tìm giá trị nhỏ nhất và có xét đến cả text và logic

- Giá trị Text tương đương với 0

- Giá trị TRUE tương đương với 1

- Giá trị FALSE tương đương với 0

Ví dụ hàm MINA tham chiếu vào vùng chứa các con số >0, nhưng giá trị text được hiểu tương đương với 0 nên hàm MINA trả về 0. Còn với hàm MIN thì trả về là 10

AVERAGEA(value1, value2, ...)

Là hàm tìm giá trị trung bình và có xét đến cả text và logic

- Giá trị Text tương đương với 0

- Giá trị TRUE tương đương với 1

- Giá trị FALSE tương đương với 0

Ví dụ hàm AVERAGEA tham chiếu vào vùng chứa các con số, text và logic thì đó giá trị trung bình đương hiểu như sau: =(10+20+30+50+0+1)/6. Trong đó 0 là text, 1 là TRUE và 6 là 6 giá trị. Còn đối với hàm AVERAGE thì trả về là 27.5

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Các hàm thống kê khác trong Excel (Phần 1)

 Trong Excel, ngoài các hàm thống kê cơ bản (SUM, COUNT, MAX, MIN,...) và các hàm thống kê có điều kiện (SUMIF, SUMIFS, COUNTIFS,...) thì còn có các hàm thống kê khác:

Hàm MEDIAN(number1, number2, number3,...)

Hàm này để tính toán số trung vị của 1 dãy số

Trường hợp 1: Nếu số phần tử được đưa vào hàm MEDIAN là chẵn. Ví dụ: 2,1,10,4,6,8 thì MEDIAN sẽ lấy 2 số ở giữa theo thứ tự tăng dần là 4, 6 và lấy giá trị trung bình của 2 số này là kết quả. Khi đó trung bình của 4 và 6 là 5

Trường hợp 2: Nếu số phần tử được đưa vào hàm MEIDAN là lẻ. Ví dụ: 2,1,10,4,6 thì làm MEIDAN sẽ lấy số ở giữa theo thứ tự tăng dần làm kết quả. Khi đó, số 4 là số ở giữa và hàm MEDIAN trả về là 4

Hàm MODE(number1, number2, number3,...)

Hàm này trả về số có tần suất xuất hiện nhiều nhất

Ví dụ trong cột A, số 8 xuất hiện nhiều nhất nên kết quả hàm MODE trả về là 8. Trường hợp có từ 2 số trở lên có cùng tần suất xuất hiện nhiều nhất thì hàm MODE trả về số đầu tiên

Hàm MODE.MULT(number1, number2, number3,...)

Hàm này trả về số có tần suất xuất hiện nhiều nhất. Trường hợp có từ 2 số trở lên có cùng tần suất xuất hiện nhiều nhất thì hàm MODE.MULT trả về tất cả các số có cùng tần suất xuất hiện nhiều nhất

Ví dụ trong cột A có số 9 và 8 có cùng tần suất nhiều nhất là 3 lần xuất hiện thì hàm MODE.MULT sẽ trả về cả 2 số này. Với Office 365 thì sẽ trả về luôn cả 2. Với Office 2019 trở về thì khi nhấn Enter chỉ trả về 1 số. Để xuất hiện cả 2 số thì thay thế bằng công thức: =INDEX(MODE.MULT($A$1:$A$9),Row(1:1)) và thực hiện kéo công thức xuống dưới

Hàm FREQUENCY(data_array,bins_array)

FREQUENCY là 1 hàm mảng để đếm các giá trị trong các khoảng. Có bao nhiêu khoảng thì hàm FREQUENCY trả về bấy nhiêu kết quả

Ví dụ 1: Thống kê trong vùng từ A1:A10 với tham số thứ 2 là 10 => Tức sẽ gồm 2 khoảng: <=10 và >10. Khi đó hàm này trả về là 8 và 2. Tức 8 số <=10 và 2 số >10

Ví dụ 2: Thống kê trong vùng từ A1:A10 với tham số thứ 2 là {9;12} => Tức sẽ gồm 3 khoảng: <=9, >9 và <=12, >12 thì công thức =FREQUENCY(A1:A10,{9;12}) trả về kết quả là 8,1,1

Hàm TRIMMEAN(array, percent)

Là hàm tính giá trị trung bình trong khoảng ở giữa sau khi đã loại bỏ các giá trị đầu và cuối theo tỷ lệ % percent

Ví dụ 1: Mảng gồm 05 phần tử, loại bỏ 40% tức 40%*5=2, hàm TRIMMEAN sẽ loại bỏ 1 phần tử ở đầu và 1 phần tử ở cuối. =TRIMMEAN(A1:A5,0.4)

Ví dụ 2: Mảng gồm 20 phần tử, loại bỏ 20% tức 20%*20=4, hàm TRIMMEAN sẽ loại bỏ 2 phần tử ở đầu và 2 phần tử ở cuối

Trường hợp nếu % số phần tử loại trừ là số lẻ thì sẽ làm tròn xuống theo bội số của 0.2. Ví dụ: 30% của 10 phần tử thì làm tròn xuống thành 20%

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Nhóm hàm thống kê Cơ sở dữ liệu trong Excel DSUM, DMAX, DMIN, DCOUNT, DCOUNTA, DAVERAGE

 Nhóm hàm Cơ sở dữ liệu là nhóm hàm thống kê có điều kiện trong Excel, hỗ trợ tham chiếu theo vùng điều kiện. Nhóm hàm thống kê trong cơ sở dữ liệu gồm các hàm hay dùng như: DSUM, DMAX, DMIN, DCOUNT, DCOUNTA, DAVERAGE

(Hình ảnh dữ liệu mẫu để làm ví dụ mẫu cho các tình huống bên dưới)

HƯỚNG DẪN SỬ DỤNG HÀM DSUM TRONG EXCEL

   *** Cấu trúc hàm DSUM: =DSUM(database,field,criteria)

         - Database: Bảng dữ liệu nguồn, lấy từ dòng tiêu đề: A2:C15

         - Field: Tên tiêu đề cột tính tổng: "DOANHTHU"

         - Criteria: Vùng điều kiện bên ngoài (Điều kiện phải có chứa tiêu đề)

   *** Ví dụ 1: Tính tổng doanh thu của mã hàng SP1. Thì công thức là:

           =DSUM(A2:C15,C2,E2:E3)

          Trong đó: E2:E3 là vùng điều kiện có tiêu đề cột. Tức E2 là tiêu đề cột, E3 là điều kiện tính tổng

          Trường hợp này nếu thay bằng SUMIF thì công thức là: =SUMIF(A2:A15,E3,C2:C15)

   *** Ví dụ 2: Tính tổng doanh thu loại hàng SP và doanh thu >10000

          Công thức: =DSUM(A2:C15,C2,E2:F3)

           Trường hợp này nếu thay bằng SUMIFS thì: =SUMIFS(C2:C15,A2:A15,E3,C2:C15,F3)

HƯỚNG DẪN SỬ DỤNG HÀM DMAX, DMIN, DAVERAGE TRONG EXCEL

   *** Cấu trúc hàm DMAX: =DMAX(database,field,criteria)

         - Database: Bảng dữ liệu nguồn, lấy từ dòng tiêu đề: A2:C15

         - Field: Tên tiêu đề cột tính tổng: "DOANHTHU"

         - Criteria: Vùng điều kiện bên ngoài (Điều kiện phải có chứa tiêu đề)

   *** Cấu trúc hàm DMIN: =DMIN(database,field,criteria)

   *** Cấu trúc hàm DAVERAGE: =DAVERAGE(database,field,criteria)

Tương tự như đối với hàm DSUM thì hàm DMAX, DMIN, DAVERAGE có cấu trúc và cách dùng giống nhau. Chỉ khác là kết quả hàm DMAX trả về là giá trị lớn nhất theo vùng điều kiện, DMIN trả về giá trị nhỏ nhất theo vùng điều kiện và DAVERAGE trả về giá trị trung bình theo vùng điều kiện

Ví dụ 1: Tính doanh số lớn nhất, nhỏ nhất, trung bình của loại hàng HH

=DMAX(A2:C15,C2,E2:E3)

=DMIN(A2:C15,C2,E2:E3)

=DAVERAGE(A2:C15,C2,E2:E3)

Trường hợp này nếu thay bằng hàm MAXIFS, MINIFS, AVERAGEIFS thì công thức tương ứng là:

=MAXIFS(C2:C15,A2:C15,C3)

=MINIFS(C2:C15,A2:C15,C3)

=AVERAGEIFS(C2:C15,A2:C15,C3)

HƯỚNG DẪN SỬ DỤNG HÀM DCOUNT VÀ DCOUNTA

Cấu trúc của DCOUNT và DCOUNTA cũng tương tự như DSUM, DMAX, DMIN, DAVERAGE

   *** Cấu trúc hàm DCOUNT: =DCOUNT(database,field,criteria)

   *** Cấu trúc hàm DCOUNTA: =DCOUNTA(database,field,criteria)

Trong Excel thì hàm COUNT để đếm con số và COUNTA đếm giá bất kỳ. Khi đó: DCOUNT là hàm để đếm con số có điều kiện và DCOUNTA là đếm giá trị bất kỳ có điều kiện

Như vậy, các hàm thống kê cơ sở dữ liệu DSUM, DCOUNT, DMAX, DMIN, DAVERAGE, DCOUNTA có cấu trúc và cách dùng giống nhau, chỉ khác nhau về mục đích tính toán.

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Sự khác biệt giữa hàm SUBTOTAL và AGGREGATE

 Có 2 hàm thống kê tùy chọn được sử dụng phổ biến nhất hiện nay gồm:

- SUBTOTAL: Có ở tất cả các phiên bản Excel

- AGGREGATE: Chỉ có từ Excel 2010 trở lên

NHỮNG ĐẶC ĐIỂM KHÁC BIỆT VỀ 2 HÀM NÀY

Xét về số lượng kiểu thống kê trong hàm

Hàm AGGREGATE nhiều tùy chọn loại thống kê hơn so với SUBTOTAL như: MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE, QUARTILE. Trong đó, kiểu thống kê LARGE, SMALL được dùng rất phổ biến để xét lớn nhỏ nhiều điều kiện nâng cao với mảng, kết hợp với các hàm INDEX, OFFSET,...để tìm kiếm nhiều điều kiện

Xét về kiểu loại trừ

Hàm AGGREGATE có mạnh hơn SUBTOTAL ở tham số thứ 2 của AGGREGATE với đa dạng các tình huống loại trừ gồm: Loại giá trị lỗi, loại Subtotal và Aggregate, loại dòng ẩn và kết hợp. Đặc biệt tính năng kết hợp loại giá trị lỗi được khai thác rất phổ biến

Xét về xử lý mảng

Hàm AGGREGATE cho phép hoạt động với mảng với tham số đầu tiên là 14 - Large hoặc 15-Small, khi đó sẽ có tham số thứ 4 của hàm AGGREGATE là tham số k để lấy giá trị lớn hoặc nhỏ thứ bao nhiêu trong mảng nhiều điều kiện

Xét về kết hợp với GROUP OUTLINE

Tính năng tạo GROUP OUTLINE tự động, phần tạo thống kê tự động cho từng nhóm chỉ chạy với SUBTOTAL mà chưa có kết hợp với AGGREGATE. Tham khảo chi tiết hơn: TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM THỐNG KÊ TRONG EXCEL (PHẦN 4) - SỬ DỤNG KÝ TỰ ĐẠI DIỆN WILDCARD

 Trong Excel, các hàm thống kê có điều kiện đuôi ...IF và ...IFS là những hàm thống kê phổ biến, nhưng ở cấp độ nâng cao hơn chút thì nó còn sử dụng so sánh điều kiện theo kiểu "ký tự đại diện wildcard" với dấu *, ? và dấu ~. Trong đó, dấu * được dùng phổ biến nhất, đấu ? thì ít gặp hơn và dấu ~ thì khá hiếm gặp.

Các hàm COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS đều sử dụng được kiểu so sánh ký tự đại diện Wildcard.

Ví dụ dưới dây sẽ sử dụng hàm SUMIFS, COUNTIFS và với dữ liệu theo hình ảnh dưới đây:

Sử dụng ký tự wildcard đại diện là *

Ví dụ: =SUMIFS(B:B,A:A,"HH*") thì kết quả là 58 => Tức chỉ cần 2 ký tự đầu tiên là HH thì sẽ tính tổng

Sử dụng ký tự wildcard là ?

Ví dụ: =SUMIFS(B:B,A:A,"SP??") thì kết quả là 30 => Tức chỉ cần 2 ký tự đầu tiên là SP và đủ 4 ký tự thì sẽ tính tổng

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM THỐNG KÊ TRONG EXCEL (PHẦN 2) - NHÓM HÀM THỐNG KÊ THEO THỨ HẠNG

 Thống kê theo thứ hạng thì có thể nghĩ đến từ "...thứ mấy". Ví dụ: Lớn thứ 2, Xếp thứ bao nhiêu,...

Để thuận tiện cho việc diễn giải cấu trúc và cách dùng của hàm thì các hàm bên dưới sẽ được lấy ví dụ theo hình ảnh sau:

Hàm LARGE

   *** Cấu trúc: =LARGE(array,k)

   *** Cách dùng hàm LARGE: Hàm LARGE là hàm tìm giá trị lớn thứ mấy trong tập hợp giá trị. Ví dụ: =LARGE(A1:A8,2) thì kết quả hàm LARGE trả về là 170. Khi đó nếu tham số thứ 2 của hàm LARGE là 1 thì tương đương với hàm MAX

   *** Ứng dụng hàm LARGE: Sử dụng để lấy top bao nhiêu phần tử có giá trị lớn nhất (ví dụ Top 5, top 10,...), được dùng nhiều trong các báo cáo thông kê, tổng hợp quản trị

Hàm SMALL

   *** Cấu trúc: =SMALL(array,k)

   *** Cách dùng hàm SMALL: Hàm SMALL là hàm tìm giá trị nhỏ thứ mấy trong tập hợp giá trị. Ví dụ: =SMALL(A1:A8,2) thì kết quả hàm SMALL trả về là 53. Khi đó nếu tham số thứ 2 của hàm LARGE là 1 thì tương đương với hàm MIN

   *** Ứng dụng hàm SMALL: Sử dụng để lấy top bao nhiêu phần tử có giá trị nhỏ nhất nhất,  được dùng nhiều trong các báo cáo thông kê, tổng hợp quản trị

Hàm RANK

   *** Cấu trúc: =RANK(number,ref,order)

   *** Cách dùng hàm RANK: Hàm RANK là hàm trả về thứ tự xếp hạng của từng giá trị trong tập hợp giá trị. Ví dụ: =RANK(89,A1:A8,0) thì kết quả trả về là 4. Tham số 0 là sắp xếp giảm dần thì số 89 là số lớn thứ 4. Và ngược lại tham số 1 là lấy thứ hạng theo sắp xếp tăng dần. =RANK(89,A1:A8,0) thì kết quả trả về là 5

   *** Ứng dụng hàm RANK: Sử dụng nhiều trong đánh giá kết quả, chấm điểm,....

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM THỐNG KÊ TRONG EXCEL (PHẦN 3) - NHÓM HÀM THỐNG KÊ CÓ ĐIỀU KIỆN ĐƠN GIẢN

 Trong Excel, nhóm hàm thống kê có điều kiện được biết đến với hậu tố là ...IF hoặc ...IFS như: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS. Các hàm này bản chất cách sử dụng khá giống nhau, chỉ khác nhau về mục đích tính toán

Để diễn giải rõ hơn về cách dùng cơ bản các hàm này thì các chủ đề về các hàm thống kê cơ bản trong Excel sẽ theo số liệu như hình ảnh dưới đây:

Hàm SUMIF

   *** Cấu trúc: =SUMIF(range,criteria,sum_range)

   *** Cách dùng hàm SUMIF: Là hàm tính tổng 1 điều kiện trong Excel. Ví dụ: SUMIF(A:A,"HH001",B:B) thì kết quả trả về là 37.

   *** Ứng dụng SUMIF: Hàm SUMIF là hàm thống kê có điều kiện cơ bản được nhiều người biết đến và được dùng phổ biến trong tính tổng 1 điều kiện, trong các báo cáo tổng hợp đơn giản

Hàm SUMIFS

   *** Cấu trúc: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

   *** Cách dùng hàm SUMIFS: Là hàm tính tổng nhiều điều kiện trong Excel. Ví dụ: SUMIFS(B:B,A:A,"HH001",B:B,">10") thì kết quả là 20. 

   *** Ứng dụng hàm SUMIFS: Là hàm thống kê có điều kiện cơ bản được sử dụng nhiều trong các báo cáo tổng hợp nhiều điều kiện. Trong SUMIFS có thể đưa vào hơn 100 cặp điều kiện

Hàm COUNTIF

   *** Cấu trúc: =COUNTIF(range,criteria)

   *** Cách dùng hàm COUNTIF: Là hàm đếm 1 điều kiện trong Excel. Ví dụ: COUNTIF(A:A,"HH001") thì kết quả trả về là 3

   *** Ứng dụng hàm COUNTIF: Là hàm đếm 1 điều kiện phổ biến. Được dùng nhiều trong kiểm tra dữ liệu có trùng hay không, trong các báo cáo thống kê đơn giản

Hàm COUNTIFS

   *** Cấu trúc: =COUNTIFS(range1,criteria1,range1,criteria1,...)

   *** Cách dùng hàm COUNTIFS: Là hàm đếm nhiều điều kiện trong Excel. Ví dụ: COUNTIF(A:A,"HH001",B:B,"<10") thì kết quả trả về là 2

   *** Ứng dụng hàm COUNTIFS: Là hàm đếm nhiều điều kiện phổ biến. Được dùng nhiều trong kiểm tra dữ liệu có trùng với thông tin ở nhiều cột, trong các báo cáo thống kê nhiều điều kiện.

Hàm AVERAGEIF

   *** Cấu trúc: =AVERAGEIF(range,criteria,average_range)

   *** Cách dùng hàm AVERAGEIF: Là hàm tính trung bình 1 điều kiện trong Excel. Ví dụ: AVERAGEIF(A:A,"HH001",B:B) thì kết quả trả về là 12.3

   *** Ứng dụng AVERAGEIF: Hàm AVERAGEIF là hàm thống kê có điều kiện cơ bản được nhiều người biết đến và được dùng phổ biến trong tính trung bình 1 điều kiện, trong các báo cáo tổng hợp đơn giản

Hàm AVERAGEIFS

   *** Cấu trúc: =AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

   *** Cách dùng hàm AVERAGEIFS: Là hàm tính trung bình nhiều điều kiện trong Excel. Ví dụ: AVERAGEIFS(B:B,A:A,"HH001",B:B,"<10") thì kết quả trả về là 8.5

   *** Ứng dụng AVERAGEIFS: Hàm AVERAGEIF là hàm thống kê có điều kiện cơ bản được nhiều người biết đến và được dùng phổ biến trong tính trung bình nhiều điều kiện, trong các báo cáo tổng hợp đơn giản, báo cáo quản trị

Hàm MAXIFS - Chỉ có từ Office 2016 trở lên

   *** Cấu trúc: =MAXIFS(max_range,criteria_range1,criteria1,criteria_range2,criteria2...)

   *** Cách dùng hàm MAXIFS: Là hàm tìm giá trị lớn nhất thỏa mãn nhiều điều kiện trong Excel. Ví dụ: MAXIFS(B:B,A:A,"HH002") thì kết quả trả về là 16

   *** Ứng dụng hàm MAXIFS: Trong Excel không có hàm MAXIF nên dùng xét 1 hay nhiều điều kiện để tìm giá trị lớn nhất thì luôn dùng MAXIFS. Hàm này được ứng dụng nhiều trong các báo cáo, đặc biệt là báo cáo quản trị

Hàm MINIFS - Chỉ có từ Office 2016 trở lên

   *** Cấu trúc: =MINIFS(min_range,criteria_range1,criteria1,criteria_range2,criteria2...)

   *** Cách dùng hàm MINIFS: Là hàm tìm giá trị lớn nhất thỏa mãn nhiều điều kiện trong Excel. Ví dụ: MINIFS(B:B,A:A,"HH002") thì kết quả trả về là 13

   *** Ứng dụng hàm MINIFS: Trong Excel không có hàm MINIF nên dùng xét 1 hay nhiều điều kiện để tìm giá trị lớn nhất thì luôn dùng MINIFS. Hàm này được ứng dụng nhiều trong các báo cáo, đặc biệt là báo cáo quản trị

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tự động đánh lại số thứ tự tăng dần khi dùng chức năng Filter

 Tính ứng dụng của việc đánh lại Stt tự động khi dùng Filter trên Excel:

- Bảng nhập liệu filter hiển thị nhanh chóng ra bao nhiêu dòng kết quả được đánh số tăng dần

- Bảng nhập liệu kiêm luôn báo cáo

- Filter trên báo cáo

Tải file excel có công thức mẫu đánh lại số thứ tự khi filter: DOWNLOAD

Công thức 1 - Dùng hàm AGGREGATE - Áp dụng với Excel 2010 trở lên

=AGGREGATE(3,3,$B$3:B3)

Công thức 2 - Dùng hàm SUBTOTAL - Áp dụng với tất cả các phiên bản Excel 

=SUBTOTAL(3,$B$3:B3)

Trong đó: B là cột dữ liệu dùng để tham chiếu đếm khi Filter

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Sử dụng hàm Max và Min để rút ngắn công thức hàm IF

 Đối với dữ liệu xét điều kiện là số, thời gian và ngày tháng thì có thể sử dụng hàm Max và Min thay thế cho hàm IF trong một số tình huống như:

- Lấy số lớn nhất: Lấy số tối đa là bao nhiêu. Ví dụ: Tiền thưởng = doanh số * tỷ lệ thưởng nhưng không vượt quá 5 triệu

- Lấy số nhỏ nhất: Số tối thiểu là bao nhiêu. Ví dụ: Lương = Số công * đơn giá lương nhưng không thấp hơn lương cơ bản

- Tính thời gian tăng ca, đi muộn, về sớm,....

Với những tình huống như vậy thì người dùng hay nghĩ đến việc dùng hàm IF. Videos dưới đây sẽ hướng dẫn chi tiết cách sử dụng hàm Max, Min để thay thế hàm IF, giúp công thức ngắn gọn hơn.

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM THỐNG KÊ TRONG EXCEL (PHẦN 1)

 NHÓM HÀM THỐNG KÊ TRONG EXCEL (PHẦN 1)

Có lẽ trong Excel, hàm thống kê được sử dụng nhiều nhất. Có thể liệt kê các hàm thống kê gồm:

- Nhóm hàm thống kê đơn giản: SUM, COUNT, COUNTA, MAX, MIN, AVERAGE

- Nhóm hàm thống kê cao cấp hơn: LARGE, SMALL, RANK

- Nhóm hàm thống kê có điều kiện: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS

- Nhóm hàm thống kê tích hợp: SUBTOTAL, AGGREGATE

1. Lưu ý khi sử dụng hàm thống kê

Nhắc tới hàm thống kê thì có thể đoán được ngay là dùng để tính toán. Như vậy hàm thống kê dùng đối với các con số trong bảng tính Excel

2. Nhóm hàm thống kê đơn giản

- Hàm SUM: Có lẽ đây là hàm cơ bản được sử dụng nhiều nhất. Kết quả hàm SUM trả về là tổng các con số truyền vào trong hàm SUM. Cấu trúc của hàm SUM: =SUM(number1,number2,...). Trong Excel cơ bản thì chúng ta dùng hàm SUM theo các tình huống phổ biến như sau:

+ Tình huống 1: Tổng 1 vùng dữ liệu liên tục. Cấu trúc: =SUM(A1:A10)

+ Tình huống 2: Tổng 1 vùng dữ liệu rời rạc. Cấu trúc: =SUM(A1:A10,C1:C10,D5:D9)

+ Tình huống 3: Tổng của dữ liệu hỗn độn. Cấu trúc: =SUM(A1:A10,100,C1:C5) => Tức là tổng của 2 vùng và số 100

+ Tình huống 4: Tổng của vùng giao nhau. Cấu trúc =SUM(A1:B10 B2:E5) thì vùng giao nhau là B2:B5 sẽ được tính tổng

Những lưu ý khi dùng hàm SUM:

+ Lưu ý 1: Trong vùng dữ liệu truyền vào hàm SUM có giá trị là Text thì Excel sẽ loại giá trị đó khi tính tổng

+ Lưu ý 2: Trong vùng dữ liệu truyền vào hàm SUM có giá trị lỗi (#N/A, #REF!, #VALUE!, #NAME?, #DIV/0!...) thì kết quả hàm SUM trả về là giá trị lỗi tương ứng

+ Lưu ý 3: Trường hợp hàm SUM dùng với công thức mảng thì thường phải nhấn phím Ctrl+Shift+Enter. Phần này được đề cập trong bài học về Công thức mảng trong Excel

- Hàm COUNT: Là hàm sử dụng để đếm số trong 1 vùng dữ liệu. Hàm COUNT chỉ đếm những ô là con số, những ô là text và lỗi sẽ được loại bỏ. Cấu trúc hàm COUNT là: =COUNT(value1,value2,...). Hàm COUNT thường được dùng trong thống kê như trong Chấm công. 

Ví dụ: Đi làm thì chấm công là 1, các trường hợp nghỉ thì chấm theo chữ cái như NP, KP...

- Hàm COUNTA: Là hàm để đếm những ô có giá trị. Hàm COUNT đếm tất cả những ô có giá trị, kể cả là số, ngày tháng, thời gian, chuỗi văn bản, giá trị lỗi, giá trị trống (khác với NULL). Cấu trúc hàm COUNTA là: =COUNTA(value1,value2,...)

Ví dụ như hình ảnh: Ô A1 là NULL, ô A3 là rỗng nhưng giá trị rỗng này được trả về từ 1 công thức nên vẫn được đếm trong COUNTA

- Hàm MAX: Là hàm tìm giá trị lớn nhất trong vùng dữ liệu. Các tình huống của hàm MAX cũng giống của hàm SUM. Cấu trúc hàm MAX là: =MAX(number1,number2,...)

- Hàm MIN: Là hàm tìm ra giá trị nhỏ nhất trong vùng dữ liệu. Các tình huống của hàm MIN cũng giống hàm SUM. Cấu trúc hàm MIN là: =MIN(number1,number2,...)

- Hàm AVERAGE: Là hàm tính ra giá trị trung bình của 1 vùng dữ liệu, các tình huống hàm AVERAGE cũng giống hàm SUM. Cấu trúc hàm AVERGAGE là: =AVERAGE(number1,number2,...)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết