Chia sẻ công thức Excel tính tổng các số trước ký hiệu trong chấm công

 Trong nhiều tình huống nhập liệu, đặc biệt là trong chấm công thì hay nhập liệu kết hợp cả số và chữ. Ví dụ: 6.5N - tức làm việc ca ngày 6.5 tiếng

Tình huống ví dụ: Các cột ngày được chấm với ký hiệu N đằng sau và trước đó là số giờ công

 Trường hợp 1: Tính tổng các số trước chữ N

Và tính cả các trường hợp sau:

- Có xuất hiện các ô trống trong vùng dữ liệu

- Số nhập vào trước chữ N thì có thể là số thập phân hoặc số nguyên

- Nếu không có chứa chữ N mà chỉ có con số thì mặc định là N

Công thức tính tổng:

=SUMPRODUCT(--LEFT("0"&B2:J2&"N",FIND("N","0"&B2:J2&"N")-1))

Trường hợp 2: Tính tổng các số trước chữ N nhưng không tính ngày chủ nhật

Và tính cả các trường hợp như tình huống 1

=SUMPRODUCT(LEFT("0"&B2:J2&"N",FIND("N","0"&B2:J2&"N")-1)*(WEEKDAY(B$1:J$1)<>1))

Trong đó: B1:J1 là dòng chứa giá trị ngày tháng

Trường hợp 3: Trong dữ liệu có cả ký hiệu khác nhưng chỉ tính tổng các số trước chữ N

Công thức tính tính tổng tất cả các số trước chữ N:

=SUM(--IF(ISNUMBER(FIND("N",B2:J2)),LEFT(B2:J2,LEN(B2:J2)-1),0))

Nhấn Ctrl+Shift+Enter để chạy công thức

Công thức tính tổng tất cả các số trước chữ N và loại trừ chủ nhật

=SUM(--IF(ISNUMBER(FIND("N",B2:J2))*(WEEKDAY($B$1:$J$1)<>1),LEFT(B2:J2,LEN(B2:J2)-1),0))

Trong đó B1:J1 là dòng chứa giá trị ngày tháng. Nhấn Ctrl+Shift+Enter để chạy công thức.

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

Một số lỗi khi dùng công thức mảng trong Excel

 Công thức mảng được dùng khá phổ biến trong các phép tính nhiều điều kiện, phức tạp. Có thể chia thành 3 loại: Công thức tính toán theo mảng giá trị, Công thức trả về mảng kết kết quả và Công thức tính toán theo mảng đồng thời trả về mảng giá trị. Khi đùng công thức mảng có thể phát sinh các lỗi hoặc sự cố sau:

KHÔNG NHẤN CTRL+SHIFT+ENTER KHI CHẠY CÔNG THỨC

Đối với phiên bản MS Office không phải là Office 365 thì nhiều tình huống sử dụng công thức mảng bắt buộc phải nhấn Ctrl+Shift+Enter. Nếu nhấn Enter bình thường thì kết quả sẽ trả ra sai, thiếu hoặc lỗi. Khi đó người dùng vào lại nội dung công thức rồi nhấn tổ hợp phím Ctrl+Shift+Enter để chạy lại

Trường hợp công thức mảng phải nhấn Ctrl+Shift+Enter chủ yếu trong các trường hợp công thức mảng đó dùng các hàm như: SUM, IF, IFERROR, COUNT, COUNTA, MAX, MIN, AVERAGE. Nhưng với Office 365 thì chỉ cần nhấn Enter bình thường là được

CÔNG THỨC MẢNG TRẢ VỀ KẾT QUẢ LỖI #VALUE!, #NUM!

#VALUE! được hiểu là sai hoặc lỗi kiểu giá trị. Trường hợp này chủ yếu do 1 trong 2 nguyên nhân sau:

- Do thực hiện phép tính +, -, *, / mà dữ liệu có chứa giá trị kiểu Text. Ví dụ như hình ảnh: Cột đơn giá có 1 ô là dữ liệu Text và thực hiện phép nhân mảng thì kết quả trả về lỗi #VALUE!

- Do lệch tọa độ hoặc khác nhau số phần tử giữa 2 mảng. Ví dụ như hình ảnh: Mảng 1 từ A2:A7, mảng 2 từ B2:B8 nhiều phần tử hơn mảng 1 nên công thức trả về giá trị lỗi. Trường hợp nếu thực hiện phép tính +, -, *, / mà lệch số phần tử thì kết quả lỗi thường là #N/A

Lỗi #NUM! thường là dùng phép tính mảng có sử dụng đến các phép chia mảng, tính căn hoặc lũy thừa trong mảng. Lỗi này thường gặp khi dùng phép chia mảng trong hàm AGGREGATE với tham số 14, 15 nhưng ko có kết quả thỏa mãn

CÔNG THỨC MẢNG CHẠY CHẬM (Calculating nhích từng % và chờ rất lâu)

Nguyên nhân là do người dùng đưa vào mảng có quá nhiều phần tử cho tính toán. Đặc biệt hay gặp là đưa cả cột hoặc cả dòng Excel vào trong công thức mảng. Ví dụ: =SUM(IF(A:A>0,...

Khi gặp tình trạng này thì người dùng xử lý như sau:

- Phương án tạm thời: Giảm kích thước của mảng khi đưa vào công thức. Ví dụ: A:A thì thay bằng A1:A1000

- Phương án tối ưu: Thay bằng các công thức tương đương nhưng tốc độ tính toán nhanh hơn, VBA hoặc SQL trong Excel,...

CÔNG THỨC TRẢ VỀ MẢNG KẾT QUẢ NHƯNG LỖI #SPILL!

Lỗi này #SPILL! xuất hiện khi dùng Office 365 với các hàm hoặc công thức trả về mảng kết quả, nhưng các ô bên dưới hoặc bên cạnh công thức có chứa dữ liệu nên không đủ phạm vi cho dữ liệu trả về

Cách khắc phục: Kiểm tra dữ liệu các ô bên dưới hoặc bên cạnh công thức => thực hiện xóa hoặc chuyển dữ liệu các ô đó sang nơi khác và cập nhật lại công thức.

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 tiền điện nước sinh hoạt

 Tiền điện, nước sinh hoạt bản chất là tính theo phương pháp lũy tiến dựa trên số tiêu thụ thực tế

CÔNG THỨC EXCEL TÍNH TIỀN NƯỚC SINH HOẠT

Bước 1: Tạo bảng đơn giá theo quy định

Cấu trúc bảng đơn giá gồm 03 cột:

- Cột 1: Mức min cho từng khoảng

Ví dụ: 0-10m3 thì mức min là 0, từ 10-20m3 thì mức min là 10, 20-30m3 thì mức min là 20 và cuối cùng là 30

- Cột 2: Đơn giá tương ứng của từng mức

- Cột 3 là cột phụ để trừ đơn giá. Giả cột mức min là cột D, cột đơn giá là cột E thì công thức tại cột phụ là: =E2-N(E1)

Bước 2: Lấy công thức tính tiền nước theo bảng giá đã tạo trước đó

Giả sử ô A1 nhập số khối nước tiêu thụ thì công thức tính tiền nước là:

=SUMPRODUCT(TEXT(A1-$D$2:$D$5,"0.00;\0")*$F$2:$F$5)

Trong đó: D2:D5 và F2:F5 là thông tin từ bảng đơn giá đã tạo ở Bước 1

Một số tính thành sẽ áp dụng theo 3 mức và đơn giá khác nhau (có thể theo từng loại hình hộ sinh hoạt) thì sẽ chỉnh sửa trong bảng giá

CÔNG THỨC EXCEL TÍNH TIỀN ĐIỆN SINH HOẠT

Bước 1: Tạo bảng đơn giá theo quy định

Cấu trúc bảng đơn giá gồm 03 cột:

- Cột 1: Mức min cho từng khoảng

Ví dụ: 0-50kwh thì mức min là 0, từ 50-100kwh  thì mức min là 50kwh,..., và cuối cùng là 400kwh

- Cột 2: Đơn giá tương ứng của từng mức

- Cột 3 là cột phụ để trừ đơn giá. Giả cột mức min là cột D, cột đơn giá là cột E thì công thức tại cột phụ là: =E2-N(E1)

Bước 2: Sử dụng công thức tính tiền điện sinh hoạt theo bảng giá đã tạo trước đó

Giả sử ô A1 nhập số kwh tiêu thụ thì công thức tính tiền điện là:

=SUMPRODUCT(TEXT(A1-$D$2:$D$7,"0.00;\0")*$F$2:$F$7)

Trong đó: D2:D7 và F2:F7 là thông tin từ bảng đơn giá đã tạo ở Bước 1

Công thức không dùng qua bảng phụ:

=SUMPRODUCT(TEXT(A1-{0;1;2;4;6;8}*50,"0.00;\0")*{1728;58;288;538;307;96})

Thay ô A1 bằng địa chỉ ô chứa số KWH điện sử dụng

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 thưởng lũy tiến

 Trong nhiều doanh nghiệp, đặc biệt về tính lương thưởng, chính sách khuyến mại, chiết khấu thì áp dụng theo hình thức tính lũy tiến.

Ví dụ bài toán tính thưởng lũy tiến theo doanh số. Giả sử 1 nhân viên sale đạt doanh số là 250 triệu thì 100 triệu đầu tiên hưởng 5%, 100 triệu tiếp theo hưởng 5.5% và 50 triệu còn lại hưởng tỷ lệ 5.8%

=100*5%+100*5.5%+50*5.8%=13.4 triệu

CÔNG THỨC EXCEL TÍNH THƯỞNG LŨY TIẾN

Bước 1: Tạo cột phụ điền vào mức Min từng khoảng doanh số

Bước 2: Tạo cột phụ tính ra mức tỷ lệ trừ giữa các mức liền kề. Tức mức sau trừ mức liền trước.

Công thức cho cột này là: =C2-N(C1)

Bước 3: Công thức tính thưởng lũy tiến

Giả sử ô E1 nhập doanh số thực tế thì công thức tính là:

=SUMPRODUCT(TEXT(E1-$B$2:$B$8,"0;\0")*$D$2:$D$8)
Trong đó: B2:B8 và D2:D8 là 2 cột phụ tạo ở bước 1, 2

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ìm giá theo ngày niêm yết gần nhất

 Trong thực tế chính sách giá bán hàng luôn thay đổi theo thời gian, một sản phẩm có thể có nhiều lần thay đổi giá. Đồng thời phải đảm bảo nguyên tắc: Khi thay đổi giá thì số liệu của những thời điểm trước đó không được thay đổi theo.

Ví dụ tình huống: Sản phẩm A niêm yết giá thay đổi vào các ngày 01/01, 15/02, 20/03 => Tức từ 01/01-14/02 áp dụng 150,000đ, từ ngày 15/02-19/03 áp dụng 160,000đ và từ 20/03 trở đi áp dụng giá 165,000đ

CÔNG THỨC EXCEL TÌM GIÁ THEO NGÀY NIÊM YẾT GẦN NHẤT

- Trường hợp với máy tính dùng Office 2010 trở lên thì công thức tại cột G là:

=AGGREGATE(14,6,$B$2:$B$8/($C$2:$C$8=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=E2)/($C$2:$C$8<=F2),1))/($A$2:$A$8=E2),1)

- Trường hợp máy tính dùng Office 2019 trở lên thì công thức tại cột G là:

=MAXIFS($B$2:$B$8,$C$2:$C$8,MAXIFS($C$2:$C$8,$A$2:$A$8,E2,$C$2:$C$8,"<="&F2),$A$2:$A$8,E2)

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 SUMIF và SUMIFS mảng kết hợp cùng hàm SUMPRODUCT trong Excel

 Hàm SUMIF và SUMIFS là 2 hàm tính tổng nhiều điều kiện được sử dụng rất nhiều trong thực tế. Trong đó SUMIF là hàm tính tổng thỏa mãn 1 điều kiện và SUMIFS là hàm tính tổng thỏa mãn nhiều điều kiện. Khi đó, nếu dùng SUMIFS với 1 điều kiện thì tương đương như dùng SUMIF và chỉ khác nhau chút ít về thứ tự các tham số.

HƯỚNG DẪN SỬ DỤNG HÀM SUMIFS KẾT HỢP VỚI HÀM SUMPRODUCT

Tình huống ví dụ như sau: Tính tổng chi phí của 3 mã bộ phận TC, VP, NS thì công thức thông thường sẽ là dùng 3 hàm SUMIFS cộng lại với nhau

=SUMIFS(D:D,C:C,"NS")+SUMIFS(D:D,C:C,"TC")+SUMIFS(D:D,C:C,"VP")

Nếu đưa 3 giá trị TC, VP, NS vào trong 1 mảng và truyền vào hàm SUMIFS và kết hợp với SUMPRODUCT thì công thức như sau:

=SUMPRODUCT(SUMIFS(D:D,C:C,{"KD","TC","VP"}))

Ngoài ra, nếu đưa 3 giá trị KD, TC, VP nhập vào 3 ô F1:F3 thì công thức sẽ là

=SUMPRODUCT(SUMIFS(D:D,C:C,F1:F3))

Tình huống này dùng chỉ 1 điều kiện thì có thể dùng hàm SUMIF như sau:

=SUMPRODUCT(SUMIF(C:C,{"KD","TC","VP"},D:D))

Như vậy, trong trường hợp cần tính tổng của nhiều đối tượng đồng thời mà kết hợp giữa SUMIFS và SUMPRODUCT với mảng thì công thức sẽ được rút ngắn đi rất nhiều

Trường hợp có từ 2 điều kiện trở lên. Giả sử cần tính tổng chi phí của 3 mã bộ phận TC, VP, NS và mã chi phí loại F (cột mã chi phí có ký tự đầu tiên là F) thì công thức ghép giữa SUMPRODUCT và SUMIFS là

=SUMPRODUCT(SUMIFS(D:D,B:B,"F*",C:C,{"KD","TC","VP"}))

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 hợp ký hiệu chấm công tay

 Nhiều doanh nghiệp hiện nay vẫn sử dụng hình thức chấm công tay thông qua các ký hiệu. Ví dụ: x là 1 công, p là 1 ngày phép,...khi đó người dùng thường sử dụng hàm Countif để tổng hợp công

=COUNTIF(B1:Z1,"x") => để đếm số ký hiệu x trong bảng chấm công

Trong thực tế dữ liệu chấm công theo chỉ tiêu của doanh nghiệp sẽ có nhiều quy định về ký hiệu tùy theo nhu cầu quản lý và theo dõi Ví dụ như tình huống ký hiệu tính công đi làm: x là 1 công, x/2 là nửa công, 1.5x là 1.5 công, 2x là 2 công,...Khi đó người dùng sẽ phải dùng từng hàm COUNTIF lại với nhau để tính ra tổng công trong tháng

=COUNTIF(B1:Z1,"x")+COUNTIF(B1:Z1,"x/2")*0.5+COUNTIF(B1:Z1,"1.5x")*1.5+COUNTIF(B1:Z1,"2x")*2

Và kết quả là càng có nhiều ký hiệu chấm công thì công thức sẽ càng dài.

Hướng dẫn dùng công thức mảng kết hơp SUMPRODUCT và COUNTIF để rút ngăn công thức chấm công tay theo ký hiệu

Cùng với ví dụ của công thức bên trên là sử dung ký hiệu x là 1 công, x/2 là nửa công, 1.5x là 1.5 công, 2x là 2 công thì công thức mảng khi kết hợp giữa SUMPRODUCT và COUNTIF như sau:

=SUMPRODUCT(COUNTIF(B1:Z1,{"X","X/2","1.5X","2X"})*{1,0.5,1.5,2})

Trong đó: 

- B1:Z1: Là dòng đầu tiên của lưới dữ liệu chấm công

- {"X","X/2","1.5X","2X"} là danh sách các ký hiệu chấm công được đặt trong dấu {...}. Ký hiệu chấm công là text nên đặt trong dấu nháy kép cho từng ký hiệu và ngăn cách nhau bằng dấu phẩy. Trường hợp máy tính sử dụng dấu ; ngăn cách các tham số thì thay bằng dấu ;

- {1,0.5,1.5,2} là danh sách con số quy đổi công tương ứng với các ký hiệu ở trên. Danh sách ký hiệu có bao nhiêu phần tử thì danh sách con số quy đổi công cũng tương ứng bằng đó phần tử

=> Khi đó, nếu dùng theo phương pháp này mà có nhiều ký hiệu chấm công thì công thức sẽ được rút ngắn đi rất nhiều

Ví dụ công thức tổng hợp chấm công theo ký hiệu sử dụng hơn 26 ký hiệu. Công thức sẽ ngắn hơn nhiều so với sử dụng 26 hàm COUNTIFS cộng lại với nhau

=SUMPRODUCT(COUNTIFS(AP8:BT8,{"1","0.5","S","C","D","G","PX","CX","BX","KX","CL","CX2","CT","1.5","2","CN","L","L2","OL","OL2","CD","CD2","CX","B","KL3"},$AP$7:$BT$7,"<"&H8)*{1,0.5,1,1,1,0.5,0.5,0.5,0.5,0.5,0.5,0.5,1,1.5,2,1,1,0.5,1,0.5,1,1,0.5,1,0.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

Chia sẻ công thức Excel đếm không trùng lặp

 Trong thực tế có nhiều bài toán tình huống cần đếm số đối tượng từ bảng dữ liệu chi tiết như:

- Có bao nhiêu mã hàng có phát sinh

- Có bao nhiêu nhân viên có doanh số phát sinh

...

Tuy nhiên, từ bảng dữ liệu chi tiết thì một đối tượng có thể phát sinh ở nhiều dòng và cần đếm loại trùng. Để xử lý tình huống này thì nhiều người hay chọn cách dùng Remove Duplicate để loại trùng hoặc dùng Pivot Table để tạo ra danh sách duy nhất rồi đếm (COUNTA) lại.

Bài viết này sẻ cách đếm dữ liệu không trùng lắp bằng sử dụng công thức:

Ví dụ từ A1:A12 là danh sách phát sinh của nhiều đối tượng và một đối tượng có thể lắp lại nhiều lần. Khi đó công thức sẽ kết hợp giữa SUMPRODUCT và COUNTIF như sau:

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

Khi sử dụng công thức này thì người dùng chỉ cần thay vùng địa chỉ A2:A12 theo dữ liệu thực tế là được. 

Lưu ý: Đây là 1 dạng công thức tạo mảng và tính toán với mảng nên sẽ chạy khá chậm khi dữ liệu lớn và kéo cong thức cho nhiều dò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

Chia sẻ công thức Excel đánh lại số phiếu chứng từ tự động

Tính ứng dụng của việc đánh lại số chứng từ trên Excel:

- Dữ liệu trên Excel và đánh lại số phiếu trước khi import vào phần mềm kế toán

- Đánh lại số chứng từ để đảm bảo số chứng từ liên tục, giúp thuận tiện cho việc theo dõi, in ấn

Tải file có công thức excel đánh lại số chứng từ tự động: DOWNLOAD

Công thức mẫu - Kết hợp 3 hàm: TEXT, SUMPRODUCT và COUNTIF

=TEXT(SUMPRODUCT(1/COUNTIF($B$5:B5,$B$5:B5)),"""PX""0000")

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 quy đổi ra số lượng tiền theo mệnh giá

 Chia sẻ công thức Excel quy đổi tiền ra theo mệnh giá. Công thức này rất hữu ích đối với các doanh nghiệp vẫn còn chi trả lương bằng tiền mặt hoặc phát thưởng bằng tiền mặt. Mục đích giúp phòng nhân sự hoặc thủ quỹ chuẩn bị tiền theo mệnh giá để thuận tiện cho công tác chi trả, tránh nhầm lẫn

Tải file excel quy đổi tiền có sẵn công thức mẫu: DOWNLOAD

Công thức chia sẻ: 

=QUOTIENT($A6-SUMPRODUCT($B6:B6*$B$5:B$5),C$5)

Diễn giải tình huống công thức:

- $A6 là ô chứa số tiền cần quy đổi

- $B6:B6 là cột trống dùng để tham chiếu giả lập

- $B$5:B$5 là dòng chứa các mệnh giá

- C$5 là lần lượt các mệnh giá cần quy đổi

Quy luật hoạt động của công thức: Quy đổi đơn vị tiền tệ lớn trước => Phần lẻ sẽ tiếp tục quy đổi sang đơn vị tiền tệ nhỏ 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

Chia sẻ công thức Excel tổng hợp lương cả năm từ 12 sheet lương tháng

CÔNG THỨC TỔNG HỢP LƯƠNG 12 THÁNG TỪ 12 SHEET LƯƠNG

- Tải file Excel mẫu có công thức sẵn tổng cộng lương cả năm từ 12 sheets: DOWNLOAD

- Diễn giải tình huống công thức:

+ Lương mỗi tháng được đặt ở 1 sheet, cả 12 sheet đều đảm bảo cùng cấu trúc số cột, cùng thứ tự cột (có thể khác số dòng)

+ Tên các sheet được đặt theo quy tắc: T01, T02,...,T12

+ Công thức tổng hợp dùng hàm SUMIFS phối hợp với INDIRECT

=SUMIFS(INDIRECT("'"&C$3&"'!J:J"),INDIRECT("'"&C$3&"'!A:A"),$A4)

Trong đó: 

 - "J:J" là tọa độ cột tổng thu nhập của từng sheet. Nếu bảng lương thực tế ở cột khác thì chỉ cần thay tọa độ tương ứng là xong. Ví dụ: "X:X"

- "A:A" là cột chứa thông tin mã nhân viên của từng Sheet

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 quy đổi lương NET sang GROSS

 Chia sẻ công thức Excel quy đổi lương Net Grosss trong Excel để tính ra thu nhập tính thuế

File Excel mẫu có chứa công thức quy đổi lương Net Gross: DOWNLOAD

Diễn giải tình huống công thức:

- Ô A5 cột thu nhập cần quy đổi (số tiền cần quy đổi). Khi dùng công thức vào bảng tính thì chỉ cần thay địa chỉ ô A5 là được.

- Công thức chi tiết:

Công thức 1 (cách 1) - Sử dụng hàm LOOKUP: 

=LOOKUP(A5,{0,475,925,1605,2725,4225,6185}*10^4+10^-6,100*(A5-{0,25,75,165,325,585,985}*10^4)/{95,90,85,80,75,70,65})

Công thức 2 (cách 2) - Sử dụng hàm IF:

=IF(A5>6185*10^4,(A5-985*10^4)/65,IF(A5>4225*10^4,(A5-585*10^4)/70,IF(A5>2725*10^4,(A5-325*10^4)/75,IF(A5>1605*10^4,(A5-165*10^4)/80,IF(A5>925*10^4,(A5-75*10^4)/85,IF(A5>475*10^4,(A5-25*10^4)/90,A5/95))))))*100

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ẻ 05 công thức Excel tính thuế TNCN

Chia sẻ 05 công thức tính thuế TNCN trên Excel với giả lập là Thu nhập chịu thuế được nhập tại ô A1. Khi ứng dụng vào bảng tính cụ thể thì chỉ cần thay A1 bằng chính địa chỉ ô trên bảng tính đang làm là được.

Công thức 1 - Dùng hàm ROUND kết hợp với hàm MAX: =ROUND(MAX(0,5%*A1,10%*A1-250000,15%*A1-750000,20%*A1-1650000,25%*A1-3250000,30%*A1-5850000,35%*A1-9850000),-3)

Công thức 2 - Dùng hàm ROUND kết hợp với hàm LOOKUP: =ROUND(LOOKUP(A1,{0,5,10,18,32,52,80}*10^6+10^-6,A1*{5,10,15,20,25,30,35}/100-{0,25,75,165,325,585,985}*10^4),-3)

Công thức 3 - Dùng hàm SUMPRODUCT: =SUMPRODUCT((A1>={0;5;10;18;32;52;80}*10^6)*(A1-{0;5;10;18;32;52;80}*10^6))*5%

Công thức 4 - Dùng hàm SUMPRODUCT kết hợp với hàm TEXT: =SUMPRODUCT(--TEXT(A1-{0;5;10;18;32;52;80}*10^6,"0;\0"))*5%

(Đây là công thức ngắn nhất)

Công thức 5 - Tương đương với hàm IF: =(A1<=5*10^6)*A1*5%+(A1>5*10^6)*(A1<=10*10^6)*(10%*(A1-5*10^6)+2.5*10^5)+(A1>10*10^6)*(A1<=18*10^6)*(15%*(A1-10*10^6)+7.5*10^5)+(A1>18*10^6)*(A1<=32*10^6)*(20%*(A1-18*10^6)+19.5*10^5)+(A1>32*10^6)*(A1<=52*10^6)*(25%*(A1-32*10^6)+47.5*10^5)+(A1>52*10^6)*(A1<=80*10^6)*(30%*(A1-52*10^6)+97.5*10^5)+(A1>80*10^6)*(35%*(A1-80*10^6)+181.5*10^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

Sử dụng hàm LOOKUP mảng thay thế hàm IF lồng ghép

 Trong Excel nâng cao thì hàm Lookup là hàm khá đa năng trong tìm kiếm và được ứng dụng như:

- Tìm kiếm tương đối trong khoảng điều kiện: Giống Vlookup tìm kiếm kiểu TRUE

- Giá trị truyền vào là tập hợp mảng giá trị

Nhưng bản chất của Lookup là tìm kiếm tương đối và loại bỏ giá trị lỗi khi tìm kiếm nên được mệnh danh là hàm tìm kiếm cao cấp, ứng dụng trong những tình huống tìm kiếm nhiều điều kiện, tìm kiếm theo mảng rất tốt.

Videos dưới dây sẽ hướng dẫn sử dụng hàm lookup theo ví dụ đơn giản là tìm kiếm trong khoảng giá trị (cách dùng giống với Vlookup tìm kiếm tương đối)

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

MỘT SỐ ĐẶC ĐIỂM NHẬN DẠNG CÔNG THỨC MẢNG TRONG EXCEL

 MỘT SỐ ĐẶC ĐIỂM NHẬN DẠNG CÔNG THỨC MẢNG TRONG EXCEL 

Công thức mảng là một dạng công thức phức tạp được tính toán theo mảng giá trị và kết quả có thể trả về là 1 mảng giá trị hoặc 1 ô kết quả. Công thức mảng có những đặc điểm nhận dạng như sau:

- Có xuất hiện ký hiệu { và } trong công thức: Ký hiệu trên xuất hiện do sử dụng phím Ctrl+Shift+Enter của công thức mảng hoặc do chính người dùng truyền mảng tĩnh vào trong công thức

Ví dụ 1: {=SUM(IF((LP="X")*(MA_KH="KH001"),THANH_TIEN,0))}

Ví dụ 2: =LOOKUP(A1,{1,10,30,60,120},{10,30,50,100,150}*1000)

- Có xuất hiện so sánh một vùng (mảng) với 1 giá trị hoặc 2 vùng (mảng) so sánh với nhau

Ví dụ 3: A1:A10="X"

Ví dụ 4: A1:A10*B1:B10<>C1:C10

- Các tham số trong 1 hàm nào đó thường là giá trị đơn nhưng giờ đưa vào cả 1 mảng.

Ví dụ 5: LEFT(A1:A10,2)

Ví dụ 6: Find("@",A1:A10)

- Có sử dụng hàm mà tham số được gợi ý là array: SUMPRODUCT, MMULT, INDEX

Ví dụ 7: =SUMPRODUCT((LP="X")*(MA_KH="KH001")*THANH_TIEN)

- Kết quả của 1 phép tính là 1 vùng liên tục, khi click vào ô đơn lẻ để sửa công thức rồi nhấn Enter thì sẽ thông báo lỗi: You can't change part of array

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