Hướng dẫn một số tình huống tính trung bình trong Excel

 Các tình huống tính trung bình thì thường thấy trong các báo cáo tổng hợp, quản trị hoặc các số liệu về điểm thi, điểm đánh giá, KPI,...Bài viết sẽ chia sẻ một số tình huống tình trung bình thường gặp.

Tình huống 1: Tính trung bình cộng cơ bản

Đây là tình huống phổ biến và cơ bản nhất trong tính trung bình như: Tình lương trung bình của công ty trong tháng, Tính doanh số trung bình của năm,...

Công thức cho tình huống này là sử dụng hàm AVERAGE. Ví dụ: B2:B13 là tổng doanh số của từng tháng trong năm thì doanh số trung bình =AVERAGE(B2:B13)

Trường hợp nếu các ô dữ liệu là rời rạc thì có thể đưa danh sách ô hoặc dải ô vào trong hàm AVERAGE. Ví dụ: =AVERAGE(B2, D2:F2, H2)

Lưu ý: Nếu có giá trị là văn bản text đưa vào hàm AVERAGE thì kết quả tính toán sẽ bỏ qua giá trị này

Tình huống 2: Tính trung bình cộng có điều kiện

Tình huống này phổ biến và đa dạng nhất, vì nó phụ thuộc vào điều kiện dưa vào, có thể là 1, 2, 3 hay nhiều điều kiện. Và sử dụng phổ biến nhất là hàm AVERAGEIFS

Ví dụ với tình huống số liệu mẫu như hình ảnh dưới dây

- Tính doanh số trung bình với điều kiện doanh số >0

=AVERAGEIFS(C2:C10,C2:C10,">0")

- Tính doanh số trung bình của chi nhánh SG và doanh số phải >0

=AVERAGEIFS(C2:C10,C2:C10,">0",B2:B10,"SG")

Tương tự như vậy với các tình huống tham chiếu nhiều điều kiện hơn

Tình huống 3: Tính trung bình cộng gia quyền

Hình ảnh về mẫu ví dụ và tính giá trung bình gia quyền theo số lượng của mã hàng SPA

Công thức:

=SUMPRODUCT(B2:B10*C2:C10*(A2:A10="SPA"))/SUMIFS(B2:B10,A2:A10,"SPA")

Bản chất của công thức là: Tổng thành tiền của SPA chia cho tổng số lượng của SPA. Khi đó, nếu cột  D là cột thành tiền (Số lượng*Đơn giá) thì công thức sẽ gọn hơn như sau:

=SUMIFS(D2:D10,A2:A10,"SPA")/SUMIFS(B2:B10,A2:A10,"SPA")

Liên hệ tư vấn khóa học Excel cho người đi làm & đặ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 xét đến các trường hợp dữ liệu trống

 Trong Excel, khi lập công thức cần xét theo điều kiện dữ liệu trống, hoặc chính dữ liệu trống có ý nghĩa trong thống kê

MỘT SỐ TÌNH HUỐNG PHỔ BIẾN KHI LÀM CÔNG THỨC XÉT DŨ LIỆU TRỐNG

Trường hợp 1: So sánh với ô trống khi dùng hàm IF hoặc công thức mảng

Sử dụng: 2 dấu nháy kép cạnh nhau

- Trong hàm IF: =IF(A1="",.... hoặc IF(A1<>"")

- Trong công thức mảng: A1:A10="" hoặc A1:A10<>""

Ví dụ: =SUMPRODUCT(B1:B10*(A1:A10<>""))

Trường hợp 2: So sánh trong các hàm thống kê nhiều điều kiện như SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS

- Tính tổng số tiền những ô có cột A trống

=SUMIFS(D:D,A:A,"=")

- Tính tổng số tiền những ô có cột A không trống

=SUMIFS(D:D,A:A,"<>")

Liên hệ tư vấn khóa học Excel cho người đi làm & đặ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 tính tổng và đếm theo điều kiện khi Filter

 Filter là một tính năng rất phổ biến với người dùng trong Excel dùng để trích lọc dữ liệu và tính toán nhanh theo kết quả Filter, và việc tính toán nhanh này thường được sử dụng kết hợp cùng hàm SUBTOTAL hoặc AGGREGATE. Đây là 2 hàm hỗ trợ thống kê bỏ qua các dòng ẩn khi Filter

Nhưng trong thực tế, nhiều trường hợp người dùng có nhu cầu thống kê theo điều kiện khi Filter. Khi đó sẽ phải kết hợp lồng ghép thêm

Tình huống ví dụ: Khi Filter theo cột tháng hoặc theo cột sản phẩm thì các chỉ tiêu KH, OK, NG được tính toán theo kết quả Filter

CÔNG THỨC TÍNH TỔNG THEO ĐIỀU KIỆN KHI FILTER

Ví dụ: Tính tổng cột số lượng của từng chỉ tiêu KH, OK, NG khi Filter cột sản phẩm theo SPA

Công thức: =SUMPRODUCT(SUBTOTAL(9,OFFSET($D$4,ROW($5:$999)-ROW($4:$4),))*($C$5:$C$999=B1))

Trong đó:

- D4: là ô tiêu đề cột số lượng

- Row(5:999): là dữ liệu bắt đầu từ dòng số 5 đến dòng 999

- Row(4:4) là dòng tiêu đề

- B1 là giá trị điều kiện KH nhập tại ô B1

Kết quả khi thực hiện Filter cột Sản phẩm theo SPA

CÔNG THỨC ĐẾM THEO ĐIỀU KIỆN KHI FILTER

Tương tự như tình huống tính tổng theo Filter, ví dụ: Đếm chỉ tiêu NG khi Filter SPA, có bao nhiêu dòng >10

Công thức:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET($D$4,ROW($5:$999)-ROW($4:$4),))*($C$5:$C$999="NG")>10))

Liên hệ tư vấn khóa học Excel cho người đi làm & đặ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ính tổng lũy kế theo ngày, tháng, quý và năm

 Thực tế  trong quản trị thì có nhiều  báo cáo tình huống tính toán lũy kế theo thời gian như:

- Doanh thu 10 ngày đầu, 1 tháng đầu, 1 quý đầu của từng sale

- Sản lượng sản xuất 5 ngày đầu, 10 ngày đầu, ....

- Công nợ lũy kế từng tháng

...

CHIA SẺ CÔNG THỨC EXCEL TÍNH TỔNG LŨY KẾ

Giả lập bài toán tính doanh số lũy kế theo nhân viên bán hàng

Công thức tính doanh số lũy kế theo ngày

=SUMIFS(C:C,B:B,"NV1",A:A,">=" & DATE(2022,1,1), A:A,"<" &DATE(2022,1,1)+30)

Trong đó:

- DATE(2022,1,1) là ngày bắt đầu tính (người dùng thay bằng ngày khác tùy nhu cầu)

- 30 là trong 30 ngày đầu tiên (người dùng thay bằng số ngày tương ứng)

Công thức tính doanh số lũy kế theo tháng

=SUMIFS(C:C,B:B,"NV1",A:A,">=" & DATE(2022,1,1), A:A,"<" &EDATE(DATE(2022,1,1),3))

Trong đó:

- DATE(2022,1,1) là ngày bắt đầu

- EDATE(DATE(2022,1,1),3) là tính từ ngày bắt đầu đến sau đó 3 tháng

Công thức tính doanh số lũy kế theo quý

=SUMIFS(C:C,B:B,"NV1",A:A,">=" & DATE(2022,1,1), A:A,"<" &EDATE(DATE(2022,1,1),2*3))

Trong đó:

- DATE(2022,1,1) là ngày bắt đầu

- EDATE(DATE(2022,1,1),2*3) là tính từ ngày bắt đầu đến sau đó 2 quý

Công thức tính doanh số lũy kế theo năm

=SUMIFS(C:C,B:B,"NV1",A:A,">=" & DATE(2022,1,1), A:A,"<" &EDATE(DATE(2022,1,1),3*12))

- DATE(2022,1,1) là ngày bắt đầu

- EDATE(DATE(2022,1,1),3*12) là tính từ ngày bắt đầu đến sau đó 3 nă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

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

Chia sẻ công thức Excel xếp thứ hạng có điều kiện

 Hàm RANK được sử dụng rất phổ biến trong Excel để sắp xếp thứ hạng. Tuy nhiên, trong nhiều tình huống thực tế thì việc sắp xếp thứ hạng phải thỏa mãn điều kiện nào đó. Ví dụ: Trong 1 công ty có nhiều phòng kinh doanh, việc sắp xếp thứ hạng của từng sale sẽ theo từng bộ phận (ai là người có doanh số đứng đầu bộ phận đó),...

CHIA SẺ CÔNG THỨC EXCEL XẾP THỨ HẠNG CÓ ĐIỀU KIỆN

Giả sử bài toán cấn lập công thức xếp hạng theo cột doanh số và phụ thuộc vào điều kiện chi nhánh. Giả sử chi nhánh HN có 6 nhân viên thì đánh thứ hạng từ 1 đến 6, tương tự SG có 6 nhân viên cũng xếp thứ hạng từ 1 đến 6

Công thức xếp thứ hạng có điều kiện

=COUNTIFS($A$2:$A$13,A2,$C$2:$C$13,">="&C2)

Công thức này được coi như hàm RANK có điều kiệ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

Chia sẻ công thức Excel xếp thứ hạng liên tục

 Hàm RANK trong Excel được sử dụng phổ biến để sắp xếp thứ hạng của các con số, được sử dụng nhiều trong xếp loại, đánh giá,...Tuy nhiên, trong thực tế có những trường hợp trùng số

Ví dụ như hình ảnh. Cùng ở mức doanh số 1,4 tỷ - xếp thứ 2 (lớn nhất là 1.5 tỷ). Khi đó, đến giá trị tiếp theo là 1,3 tỷ thì hàm RANK sẽ trả về thứ hạng là 4 => Tức bị khuyết thứ hạng số 3. Lúc này một số trường hợp sẽ yêu cầu xếp thứ hạng không được khuyết số. Tức NV1 và NV4 sẽ đồng hạng 2, NV6 sẽ xếp thứ 3

CHIA SẺ CÔNG THỨC EXCEL XẾP THỨ HẠNG LIÊN TỤC

Với Office 2010 trở lên

=SUMPRODUCT(--(INDEX($B$2:$B$13,AGGREGATE(15,6,ROW(INDIRECT("1:"& COUNT($B$2:$B$13)))/(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW(INDIRECT("1:"& COUNT($B$2:$B$13)))),ROW(INDIRECT("1:"&SUMPRODUCT(--(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW(INDIRECT("1:"& COUNT($B$2:$B$13)))))))))>=B2))

Với Office 365

=SUM(--(UNIQUE($B$2:$B$13)>=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

Chia sẻ công thức Excel tính tổng các số trên trung bình

 Trong Excel thường phát sinh nhu cầu tính toán quản trị những con số trên trung bình và chiếm tỷ trọng bao nhiêu như:

- Tổng doanh số trên trung bình và tỷ trọng tương ứng

- Tổng chi phí lương của của những người cao nhất và tỷ trọng tương ứng

-...

CHIA SẺ CÔNG THỨC EXCEL TÍNH TỔNG CÁC SỐ TRÊN TRUNG BÌNH

Giả lập tình huống: Tính tổng doanh số trên trung bình

=SUMIFS($B$2:$B$16,$B$2:$B$16,">="& AVERAGE($B$2:$B$16))

và tỷ trọng doanh số trên trung bình

=SUMIFS($B$2:$B$16,$B$2:$B$16,">="& AVERAGE($B$2:$B$16))/SUM($B$2:$B$16)

Tương tự tính tổng doanh số dưới trung bình

=SUMIFS($B$2:$B$16,$B$2:$B$16,"<"& AVERAGE($B$2:$B$16))

và tỷ trọng doanh số tương ứng

=SUMIFS($B$2:$B$16,$B$2:$B$16,"<"& AVERAGE($B$2:$B$16))/SUM($B$2:$B$16)

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ính tổng những số cao nhất

 Trong Excel về mặt thống kê quản trị có thể phát sinh nhu cầu tính tổng những con số cao nhất như:

- Tổng doanh số của 5 người cao nhất là bao nhiêu và chiếm tỷ trọng bao nhiêu % doanh số

- Tổng doanh thu của 5 mã hàng bán chạy nhất và chiếm tỷ trọng bao nhiêu %

-...

CHIA SẺ CÔNG THỨC TÍNH TỔNG NHỮNG SỐ CAO NHẤT

Tình huống giả lập: Tính tổng doanh số của 5 nhân viên có doanh số cao nhất

=SUMPRODUCT(LARGE($B$2:$B$16,ROW($1:$5)))

và tỷ trọng doanh số của 5 nhân viên cao nhất

=SUMPRODUCT(LARGE($B$2:$B$16,ROW($1:$5)))/SUM($B$2:$B$16)

Tương như như vậy ta có công thức tổng doanh số của 5 nhân viên có doanh số thấp nhất

=SUMPRODUCT(SMALL($B$2:$B$16,ROW($1:$5)))

và tỷ trọng doanh số của 5 nhân viên thấp nhất

=SUMPRODUCT(SMALL($B$2:$B$16,ROW($1:$5)))/SUM($B$2:$B$16)

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 so sánh và đếm số lượng tăng giảm giữa 2 danh sách

 Trong một số tình huống công việc liên quan đến đối chiếu số liệu, thống kê tăng giảm, biến động giữa 2 bảng danh sách cần:

- Số lượng các mã có ở danh sách này mà không có ở danh sách kia và ngược lại

- Số lượng các mã có ở cả 2 danh sách

Ví dụ như trong nhân sự, thường phải làm báo cáo tăng giảm nhân sự thì cần đếm xem: Kỳ này tăng bao nhiêu nhân sự so với danh sách kỳ trước, kỳ này giảm bao nhiêu nhân sự so với danh sách kỳ trước, có bao nhiêu nhân sự làm việc ở cả 2 kỳ

(Hình ảnh cho các tình huống hướng dẫn bên dưới)

CÔNG THỨC SO SÁNH GIỮA 2 DANH SÁCH - Mã đối tượng có ở bảng này mà không có ở bảng kia và ngược lại

Phương pháp: Tìm kiếm từng mã ở bảng này có ở bảng kia hay không

Công thức: Hàm MATCH

- Tìm kiếm mã ở bảng 1 có ở bảng 2:

=MATCH(A2,$C$2:$C$13,0)

- Tìm kiếm mã ở bảng 2 có ở bảng 1:

=MATCH(C2,$A$2:$A$11,0)

=> Nếu kết quả trả về là #N/A thì mã đó không có ở danh sách kia và nếu trả về là con số thì mã đó có ở danh sách kia

CÔNG THỨC THỐNG KÊ ĐẾM SỐ LƯỢNG TĂNG GIẢM GIỮA 2 DANH SÁCH

Phương pháp: Dùng hàm SUMPRODUCT để thống kê số lượng tăng giảm

Công thức đếm số lượng tăng danh sách 2 so với danh sách 1:

=SUMPRODUCT(--ISERROR(MATCH(C2:C13,A2:A11,0)))

Công thức đếm số lượng giảm danh sách 2 so với danh sách 1:

=SUMPRODUCT(--ISERROR(MATCH(A2:A11,C2:C13,0)))

Công thức đếm số lượng mã có ở cả 2 danh sách:

=SUMPRODUCT(--ISNUMBER(MATCH(C2:C13,A2:A11,0)))

Theo ví dụ ở trên thì: Danh sách 1 có 10 mã, danh sách 2 có 12 mã. Nhưng danh sách 2 có 5 mã mới không có ở danh sách 1 (nhân sự vào mới vào), có 3 mã có ở danh sách 1 mà không có ở danh sách 2 (nhân sự nghỉ việc) và có 7 mã có ở cả 2 danh sách

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

Hướng dẫn dùng hàm MINIFS hoặc AGGREGATE để tìm giá trị nhỏ nhất có điều kiện và đối tượng tương ứng với giá trị đó

 Trong thực tế, có rất nhiều bài toán quản trị cần tìm giá trị nhỏ nhất thỏa mãn một hoặc nhiều điều kiện, và tìm ra đối tượng tương ứng với giá trị đó. Ví dụ như:

- Tìm ra  doanh số nhỏ nhất và của nhân viên nào

- Tìm ra giá bán thấp nhất của mặt hàng nào, thời gian bán hàng tương ứng

-....

(Hình ảnh dữ liệu phục vụ cho các ví dụ minh họa bên dưới)

HƯỚNG DẪN TÌM GIÁ TRỊ NHỎ NHẤT CÓ ĐIỀU KIỆN

Tình huống 1: Tìm ra doanh số nhỏ nhất của KH004

Công thức dành cho mọi bản Office:

=MIN(IF(B4:B15="KH004",C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên:

=AGGREGATE(15,6,C4:C15/(B4:B15="KH004"),1)

Công thức dành cho Office 2016 trở lên

=MINIFS(C4:C15,B4:B15,"KH004")

Tình huống 2: Tìm ra doanh số nhỏ nhất của KH004 trước ngày 15/01/2021

Công thức cho mọi bản Office:

=MIN(IF((B4:B15="KH004")*(A4:A15<=Date(2021,1,15)),C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên: 

=AGGREGATE(14,6,C4:C15/(B4:B15="KH004")/(A4:A15<=Date(2021,1,15)),1)

Công thức dành cho Office 2016 trở lên:

=MINIFS(C4:C15,B4:B15,"KH004",A4:A15,"<="&Date(2021,1,15))

Tình huống 3: Tìm ra khách hàng có doanh số lớn nhất

=LOOKUP(2,1/(C4:C15=MIN(C4:C15)),B4:B15)

Trường hợp có nhiều hơn 1 khách hàng có doanh số nhỏ nhất thì phải dùng hàm TEXTJOIN để liệt kê ra danh sách khách hàng có doanh số nhỏ nhất. Hàm TEXTJOIN có trong Office 2019 trở lên và Office 365

==TEXTJOIN(",",TRUE,IF(C4:C15=MIN(C4:C15),B4:B15,""))

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

Hướng dẫn dùng hàm MAXIFS hoặc AGGREGATE để tìm giá trị lớn nhất có điều kiện và đối tượng tương ứng với giá trị đó

Trong thực tế, có rất nhiều bài toán quản trị cần tìm giá trị lớn nhất thỏa mãn một hoặc nhiều điều kiện, và tìm ra đối tượng tương ứng với giá trị đó. Ví dụ như:

- Tìm ra đơn hàng có doanh số lớn nhất và của khách hàng nào, vào thời gian nào

- Tìm ra giá bán cao nhất của mặt hàng nào, thời gian bán hàng tương ứng

- Tìm ra chi phí lớn nhất là bao nhiêu và của loại chi phí nào

-....

(Hình ảnh dữ liệu phục vụ cho các ví dụ minh họa bên dưới)

HƯỚNG DẪN TÌM GIÁ TRỊ LỚN NHẤT CÓ ĐIỀU KIỆN

Tình huống 1: Tìm ra doanh số lớn nhất của KH004

Công thức dành cho mọi bản Office:

=MAX(IF(B4:B15="KH004",C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên:

=AGGREGATE(14,6,C4:C15/(B4:B15="KH004"),1)

Công thức dành cho Office 2016 trở lên

=MAXIFS(C4:C15,B4:B15,"KH004")

Tình huống 2: Tìm ra doanh số lớn nhất của KH004 trước ngày 15/01/2021

Công thức cho mọi bản Office:

=MAX(IF((B4:B15="KH004")*(A4:A15<=Date(2021,1,15)),C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên: 

=AGGREGATE(14,6,C4:C15/(B4:B15="KH004")/(A4:A15<=Date(2021,1,15)),1)

Công thức dành cho Office 2016 trở lên:

=MAXIFS(C4:C15,B4:B15,"KH004",A4:A15,"<="&Date(2021,1,15))

Tình huống 3: Tìm ra khách hàng có doanh số lớn nhất

=LOOKUP(2,1/(C4:C15=MAX(C4:C15)),B4:B15)

Trường hợp có nhiều hơn 1 khách hàng có doanh số lớn nhất thì phải dùng hàm TEXTJOIN để liệt kê ra danh sách khách hàng có doanh số lớn nhất. Hàm TEXTJOIN có trong Office 2019 trở lên và Office 365

==TEXTJOIN(",",TRUE,IF(C4:C15=MAX(C4:C15),B4:B15,""))

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 đánh số thứ tự theo một đối tượng nhất định

 Bài toán giả lập: Trong một bảng dữ liệu có nhiều mã đối tượng khác nhau. Cần đánh số thứ tự tăng dần theo từng mã đối tượng => Tức mỗi mã đối tượng sẽ được đánh tăng dần từ 1 (như hình ảnh)

Khi đó:

- Theo đối tượng A thì ô B2 là A => Stt là 1, ô B3 là A => Stt là 2

- Theo đối tượng B thì ô B3 là B => Stt là 1, ô B8 là B => Stt là 2

....

Công thức đánh lại số thứ tự theo mã đối tượng là: 

=IFERROR(LOOKUP(10^9,$A$1:A1/($B$1:B1=B2)),0)+1

Trong đó:

- Bắt buộc phải có 1 dòng đệm phía trước. Giả sử dữ liệu bắt đầu từ B2 thì công thức sẽ được quét từ B1

- Vùng quét sẽ cố định 1 nửa: Cố định điểm đầu, điểm dưới không cố định để công thức chạy tịnh tiến cho các dòng còn lại

Hoặc:

=COUNTIF($B$2:B2,B2)

Kết quả: Có bao nhiêu đối tượng thì sẽ đánh lại số thứ tự bấy nhiêu lần từ 1

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

ĐẾM SỐ PHẦN TỬ XUẤT HIỆN NHƯNG KHÔNG TÍNH TRÙNG TRONG EXCEL

 ĐẾM SỐ PHẦN TỬ XUẤT HIỆN NHƯNG KHÔNG TÍNH TRÙNG TRONG EXCEL 

Đây là dạng bài toán phát sinh trong quản trị giúp thống kê có bao nhiêu đối tượng có phát sinh trong dữ liệu. Ví dụ có thể liệt kê những bài toán như:

- Có bao nhiêu mã vật tư có phát sinh mua (bán) trong tháng? Vì: Danh mục vật tư có thể nhiều nhưng trong kỳ chỉ có 1 vài mã phát sinh

- Có bao nhiêu hóa đơn trong bảng kê chứng từ? Vì 1 số hóa đơn có thể bị nhập ở nhiều dòng do 1 hóa đơn có nhiều mã hàng

- Tháng này bán được cho bao nhiêu khách hàng? Vì 1 khách hàng có thể mua hàng nhiều lần

- Nhân viên kinh doanh này bán được bao nhiêu mã hàng? Vì 1 NVBH có thể bán 1 mặt hàng nhiều lần

....

Để thống kê bài toán này thì một số phương pháp được dùng như:

- Pivot Table rồi đếm

- Copy và Remove Duplicate rồi đếm

- Công thức phức tạp: Dùng công thức mảng SUMPRODUCT kết hợp với COUNTIFS

Phương pháp dùng công thức:

=SUMPRODUCT(1/COUNTIFS(A1:A100,A1:A100,B1:B100,B1:B100,...))

Khi đó, có bao nhiêu điều kiện sẽ truyền vào COUNTIFS bấy nhiêu cặp tham số.

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