Chia sẻ công thức Excel đánh số thứ tự theo nhiều tình huống

 Trong các bảng biểu, báo cáo, form dữ liệu,...thì đa số đều cần cột số thứ tự (Stt), bài viết này sẽ chia sẻ công thức đánh số thứ tự theo các tình huống phổ biến như sau:

Tình huống số 1: Số thứ tự không sử dụng công thức (Tức chỉ ở dạng Value)

Cách thức hiện: 

- Cách 1: Nhập số 1 ở ô dữ liệu đầu tiên. Giữ phím Ctrl và kéo xuống dưới cho các ô còn lại 

- Cách 2: Nhập số 1 và số 2 ở 2 ô đầu tiên. Kéo xuống cho các ô còn lại

Theo cách này thì không sử dụng công thức, khi thêm hoặc chèn xóa dòng thì phải kéo lại số thứ tự. Tuy nhiên cách này lại có ưu điểm trong trường hợp sử dụng Sort. Cụ thể: Khi sử dụng chức năng Sort thì dữ liệu sẽ bị xáo trộn lại, người dùng muốn đưa về thứ tự ban đầu thì Sort lại theo cột Số thứ tự

Tình huống 2: Số thứ tự sử dụng công thức và loại bỏ dòng trống

Cách thực hiện: Sử dụng hàm IF kết hợp với hàm MAX. Trong đó hàm MAX sẽ tham chiếu từ dòng tiêu đề. Ví dụ: Nếu dòng tiêu đề bắt đầu từ dòng 1 thì công thức là: =IF(B2="","",MAX($A$1:A1)+1) hoặc nếu dòng tiêu đề từ dòng 3 thì công thức là: =IF(B2="","",MAX($A$3:A3)+1)

Theo cách này thì khi xóa dòng,sort sắp xếp lại dữ liệu bên trong vùng dữ liệu thì số thứ tự sẽ tự động được tính lại.

Tình huống 3: Số thứ tự được đánh lại tăng dần theo kết quả Filter

Cách thực hiện: Dùng hàm SUBTOTAL hoặc AGGREGATE

Xem hướng dẫn chi tiết: TẠI ĐÂY

Tình huống này áp dụng khá phổ biến khi Filter trên báo cáo

Tình huống 4: Số thứ tự đánh tăng dần theo từng nhóm đối tượng

Cụ thể: Cùng một đối tượng thì số thứ tự sẽ được đánh tăng dần từ 1 theo số lần xuất hiện từ trên xuống dưới. 

Cách thực hiện: Dùng hàm LOOKUP hoặc COUNTIF

Xem hướng dẫn chi tiết: TẠI ĐÂY

Tình huống 5: Cùng một đối tượng thì cùng số thứ tự

Ví dụ: Cùng một ngày thì đánh cùng số thứ tự và tăng dần theo ngày

Cách thực hiện: Dùng hàm IF và hàm MAX. Trong đó cột A là Stt, cột B là ngày. Nếu dòng tiêu đề là dòng số 1 thì công thức là =IF(B2<>B1,MAX($A$1:A1)+1,A1) hoặc dòng tiêu đề là dòng số 3 thì công thức là =IF(B4<>B3,MAX($A$3:A3)+1,A3)

Tình huống này thường áp dụng khi đánh số phiếu, số chứng từ trong kế toán

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 đánh số thứ tự lặp lại

 Trong Excel, nhiều trường hợp người dùng có nhu cầu đánh số thứ tự lặp lại. Giả lập 2 tình huống lặp lại như sau

Trường hợp 1: Đánh liên tục 1 số rồi đến bao nhiêu lần thì đánh tiếp số tiếp theo. Ví dụ: 30 số 1, tiếp theo là 30 số 2,...

Trường hợp 2: Đánh số tăng dần đến bao nhiêu rồi lặp lại từ đầu. Ví dụ: Từ 1 đến 30, đến dòng số 31 lại từ 1,...

CHIA SẺ CÔNG THỨC EXCEL ĐÁNH SỐ THỨ TỰ LẶP LẠI

Trường hợp 1: Đánh liên tục 1 số rồi đến bao nhiêu lần thì đánh số tiếp theo. Ví dụ 5 lần

=INT((ROW(1:1)-1)/5)+1

=> Chỉ cần thay số 5 là xong

Trường hợp 2: Đánh liên tục tăng dần rồi đến số bao nhiêu thì lặp lại. Ví dụ: Đánh đến 5

=MOD(ROW(1:1)-1,5)+1

=> Chỉ cần thay số 5 là xong

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 phép tính toán học báo lỗi #VALUE! và cách khắc phục

 Trong Excel, khi người dùng sử dụng các phép tính cơ bản như +, -, *, /, ^ thì kết quả trả về là #VALUE!.  Khi đó người dùng sử dụng sai giá trị => Tức trong phép tính đó có giá trị là văn bản Text

Lúc này người dùng cần phân biệt 2 trường hợp như sau:

Trường hợp 1: Con số ở dạng Text

Với trường hợp này thì khi thực hiện các phép tính cơ bản +, - , *, /, ^ thì con số dạng text vẫn thực hiện tính toán được bình thường

Ví dụ ô A1 và B1 là 2 con số ở dạng text nhưng khi thực hiện phép tính cộng thì vẫn thực hiện được bình thường.

Lưu ý: Nếu sử dụng hàm SUM (cụ thể =SUM(A1:B1)) thì kết quả của phép tính trả về là 0

Trường hợp 2: Dữ liệu là Text hoàn toàn

Với trường hợp này khi thực hiện phép tính cơ bản +, -, *, /, ^ thì kết quả của phép tính sẽ trả về là #VALUE! => Tức sử dụng sai kiểu giá trị

Ví dụ 1:  ô A1 là chữ A thì sẽ không cộng được với B1

Ví dụ 2: ô A2 là dấu cách (Space) thì sẽ không cộng được với ô B2

Đối với ví dụ thứ 2 thì trong Excel có khá nhiều ký tự không nhìn thấy trực tiếp như: Dấu cách (Space), Tab, Xuống dòng (Alt+Enter),.... Khi đó sẽ có 2 cách xử lý như sau:

*** Cách 1: Dùng hàm N

=N(A1)+N(B1)

=> Hàm N sẽ kiểm tra ô dữ liệu có phải là con số hay không. Nếu là con số thì lấy nguyên con số đó, nếu không phải là con số thì hàm N sẽ trả về là 0

Cách 2: Dùng hàm SUM

=SUM(A1)+SUM(B1)

=> Lợi dụng đặc tính của hàm SUM là loại bỏ giá trị text ra khỏi phép tính tổng. Nên khi đó phép tính vẫn tực hiện bình thườ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

Thủ thuật thay thế hàm VALUE trong công thức Excel

 Trong nhiều trường hợp dữ liệu Excel là con số nhưng ở dạng Text như:

- Do người dùng nhập liệu dạng Text

- Dữ liệu xuất ra từ phần mềm

- Công thức tách số từ trong 1 chuỗi

...

Khi đó, thường dùng hàm VALUE để chuyển sang dạng NUMBER. Có 2 cách thay thế được hàm VALUE:

Cách 1: Dùng 2 dấu trừ (--)

Ô A1 nhập là '100 thì công thức tại ô B1 là =--A1

Cách 2: Dùng phép tính *1

Ô A1 nhập là '100 thì công thức tại ô B1 là =A1*1

Giả sử tình huống tách chuỗi đơn giản là: Lấy 2 số cuối của chuỗi

Công thức hay áp dụng là: =VALUE(RIGHT(A2,2))

Công thức thay thế ngắn gọn hơn: =--RIGHT(A2,2) hoặc =RIGHT(A2,2)*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

Chia sẻ công thức Excel làm tròn thời gian tăng ca

 Cách tính thời gian tăng ca cơ bản sẽ là:

Thời gian tăng ca=Giờ ra (out)- Giờ ra quy định

Ví dụ: Giờ ra quy định là 17:00. Nếu Giờ out>Giờ ra quy định thì sẽ tính tăng ca.

Tuy nhiên, trong thực tế thì hay xảy ra vấn đề là: Giờ out thường phát sinh khá lẻ. Ví dụ 18:23. Khi đó sẽ phát sinh bài toán làm tròn thời gian tăng ca hay còn được gọi là block số phút tăng ca

Bài toán này sẽ chia thành 2 công thức là: Công thức tính thời gian tăng ca và công thức làm tròn thời gian tăng ca

Tình huống giả lập như sau: Giờ ra quy định là 17:00, sau thời gian này sẽ xét tính tăng ca và thời gian tăng ca sẽ tính theo số phút

Công thức tính thời gian tăng ca

   *** Công thức: =MAX(A2-"17:00",0)*1440

   *** Cách dùng: Thay địa chỉ ô A2 theo dữ liệu của bảng tính. Nếu giờ out quy định khác thì sửa bên trong công thức. Ví dụ: Nếu giờ out quy định là 17:30 thì công thức là =MAX(A2-"17:30",0)*1440

   *** Lưu ý: Khi chạy công thức, nếu kết quả trả về format Time thì hay format lại về General

Công thức làm tròn thời gian tăng ca Block n phút (giả sử n=20)  

  *** Công thức: =FLOOR(B2,20)

  *** Cách dùng: Ô B2 chính là ô đã tính số phút tăng ca. Số phút tăng ca block là 20. Tức là: Nếu <20 phút thì không tính năng ca, nếu >=20 và <40 thì tính tăng ca,....

  *** Gộp tính số phút chung vào 1 công thức: =FLOOR(MAX(A2-"17:00",0)*1440,20)

Công thức làm tròn thời gian tăng ca theo khoảng đồng hồ

Ví dụ: Từ 17:00-17:14 thì không tính, từ 17:15-17:44 thì tính 30 phút, từ 17:45-18:00 là 1 tiếng,...

  *** Công thức: =MROUND(B2,30)

  *** Cách dùng: ô B2 chính là ô đã tính số phút tăng ca, 30 là biên độ số phút làm tròn.

  *** Gộp tính số phút chung vào 1 công thức: =MROUND(MAX(A2-"17:00",0)*1440,30)

Công thức làm tròn thời gian tăng ca theo khoảng đồng hồ (tiếp theo)

Ví dụ: Từ 17:00-17:19 không tính, từ 17:20-17:49 thì tính 30 phút, từ 17:50 đến 16:19 là 1 tiếng...

   *** Công thức =MROUND(B2-10,30)

   *** Gộp chung vào 1 công thức =MROUND(MAX(A2-"17:00",0)*1440-10,30)

Ngoài ra, nhiều doanh nghiệp có những quy định làm tròn thời gian lắt léo hơn hoặc có phân ca kip thì công thức phải lồng ghép phức hợp hơn. Bài viết này đưa ra 2 trường hợp đơn giản và hay gặp về tính làm tròn thời gian tăng ca

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 phép tính trong Excel

 Hàm QUOTIENT

   *** Cấu trúc: =QUOTIENT(numerator,denominator)

   *** Cách dùng hàm QUOTIENT: Dùng để tính thương số của 1 phép chia nhưng không lấy phần dư. numerator là số bị chia và denominator là số chia. Ví dụ: =QUOTIENT(10,4) sẽ có kết quả là 2

Hàm MOD

   *** Cấu trúc: =MOD(number,divisor)

   *** Cách dùng hàm MOD: Dùng để lấy phần dư của 1 phép chia. Ví dụ: =MOD(10,3) thì kết quả là 1

Hàm SQRT

   *** Cấu trúc: =SQRT(number)

   *** Cách dùng hàm SQRT: Dùng để lấy căn bậc 2 của 1 số. Ví dụ: SQRT(16) thì kết quả trả về là 4

Hàm POWER

   *** Cấu trúc: =POWER(number,power)

   *** Cách dùng hàm POWER: Dùng để thực hiện phép tính mũ của 1 số. Ví dụ: =POWER(10,3) sẽ tương đương với =10^3

Hàm ABS

   *** Cấu trúc: =ABS(number)

   *** Cách dùng hàm ABS: Sử dụng để lấy giá trị tuyệt đối của một số, tức kết quả luôn là 1 số dương. Ví dụ: =ABS(-10) thì kết quả là 10

Hàm FACT

   *** Cấu trúc: =FACT(number)

   *** Cách dùng hàm FACT: Sử dụng để tính giai thừa của một số. Ví dụ: =FACT(4) thì kết quả là 24

Ngoài ra trong Excel còn có nhiều hàm phép tính toán học khác như Log, Ln, các hàm lượng giác,...

=> Người dùng có thể vào thư viện Library trong thanh công cụ Excel để tham khảo thê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ác hàm làm tròn số trong Excel

 Làm tròn số được sử dụng rất nhiều trong tính toán, thống kê dữ liệu. Trong bài viết sẽ cung cấp cách dùng những hàm làm tròn cơ bản và nâng cao:

Hàm INT

   *** Cấu trúc: =INT(number)

   *** Cách dùng: Là hàm lấy ra phần nguyên nhưng luôn nhỏ hơn số gốc. Ví dụ: INT(A1). Giả sử A1 là 10.2 thì sẽ trả về là 10. Nhưng A1 là số âm -10.2 thì sẽ trả về -11

Hàm QUOTIENT

   *** Cấu trúc: =QUOTIENT(numerator,denominator)

   *** Cách dùng: Là hàm lấy phần nguyên kết quả 1 phép tính chia. Ví dụ: =QUOTIENT(100,3) thì kết quả là 33

Hàm ROUND

   *** Cấu trúc: =ROUND(number,num_digits)

   *** Cách dùng: Là hàm làm tròn theo cả phần nguyên và phần thập phân nhưng hoạt động theo cả 2 nguyên tắc làm tròn lên và làm tròn xuống. 

         + Làm tròn phần thập phân: Ví dụ: ROUND(10.66,1) thì kết quả là 10.7, ROUND(10.62,1) thì kết quả là 10.6

         + Làm tròn phần nguyên. Ví dụ: ROUND(1056,-1) thì kết quả là 1057. ROUND(10.6,0) thì làm tròn thành 11. Khi đó: -1 làm tròn hàng đơn vị, -2 là làm tròn hàng chục, -3 là làm tròn hàng trăm. Trong đó, -3 được làm tròn nhiều trong kế toán khi đưa số tiền về con số hàng ngàn

   *** Ứng dụng: Là hàm làm tròn phổ biến nhất

Hàm ROUNDUP và ROUNDDOWN

   *** Cấu trúc: =ROUNDUP(number,num_digits), =ROUNDDOWN(number,num_digits)

   *** Cách dùng: Giống như hàm ROUND nhưng ROUNDUP chỉ làm tròn lên và ROUNDDOWN chỉ làm tròn xuống

   *** Ứng dụng: Cũng là hàm làm tròn được ứng dụng rất phổ biến

Hàm MROUND

   *** Cấu trúc: =MROUND(number,multiple)

   *** Cách dùng hàm MROUND: Là hàm làm tròn theo bội số của Multiple và lấy bội số gần nhất với số cần làm tròn. Ví dụ: MROUND(10.2,3) thì kết quả là 9

Hàm CEILING và FLOOR

   *** Cấu trúc: =CEILING(number,significance) và =FLOOR(number,significance)

   *** Cách dùng hàm CEILING và hàm FLOOR: Là 2 hàm làm tròn lên và làm tròn xuống theo bội số, giống hàm MROUND. Nhưng CEILING lấy bội số gần nhất lớn hơn số gốc, FLOOR lấy bội số gần nhất nhỏ hơn số gốc. Ví dụ CEILING(10.2,3) thì trả về 12, FLOOR(10.2,3) thì kết quả là 9

HÀM ODD và EVEN

   *** Cấu trúc: =ODD(number) và EVEN(number)

   *** Cách dùng hàm ODD và EVEN: Là 2 làm hàm tròn lấy phần nguyên lớn hơn với số dương và nhỏ hơn với số âm. ODD lấy số lẻ, EVEN lấy số chẵ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 VỀ SỐ (NUMBER) TRONG EXCEL (PHẦN 1)

 NHÓM HÀM VỀ SỐ (NUMBER) TRONG EXCEL (Phần 1)

Nhóm hàm con số trong Excel được ứng dụng chủ yếu trong xử lý con số như: Làm tròn, lấy phần nguyên, lấy phần lẻ của phép chia....Nhóm hàm này gồm các hàm như: INT, MOD, QUOTIENT, ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, MROUND, N

1. Con số trong Excel

- Khi nhập con số bất kỳ vào 1 ô trong Excel thì mặc định con số đó sẽ bên phía tay phải của ô.

- Trường hợp nếu ô được định dạng là Text hoặc đưa dấu nháy đơn (') khi nhập con số thì Excel sẽ hiểu con số đó là Text

2. Các hàm number có 1 tham số

- Hàm INT: Là làm làm tròn, lấy phần nguyên gần nhất nhỏ hơn hoặc bằng số ban đầu. Cấu trúc hàm INT: =INT(number). Có 4 tình huống khi dùng hàm INT

+ Tình huống 1: =INT(123) thì kết quả trả về là 123. Vì 123 bản chất là số nguyên

+ Tình huống 2: =INT(123.55) thì kết quả trả về là 123. Vì 123 là phần nguyên của số 123.55 và 123<123.55

+ Tình huống 3: =INT(-123.55) thì kết quả trả về là -124. Vì -124 là số nguyên và -124<123.55

+ Tình huống 4: =INT(A1). Trong đó, giá trị của ô A1 là: 01/01/2020 08:00 thì hàm INT trả về kết quả là: 01/01/2020. Tình huống này được ứng dụng khá nhiều trong chấm công, đặc biệt đối với dữ liệu xuất ra từ máy chấm công

- Hàm N: là làm trả về nguyên số đó nếu ô giá trị là con số. Ngược lại, nếu ô giá trị là văn bản thì hàm N trả về 0. Cấu trúc hàm N là: =N(value). Có 2 tình huống ứng dụng hàm N phổ biến:

+ Tình huống 1: Kiểm tra 1 ô nào đó có phải kiểu số hay không. Ví dụ: N(A1). Nếu là kiểu số thì trả về nguyên số đó, nếu A1 là kiểu text thì kết quả là 0.

+ Tình huống 2: Dùng trong thay thế hàm IF. Ví dụ: Nếu ô A1 là số thì lấy số đó *10% nhưng nếu là Text thì lấy ra là 0. Công thức sẽ là: =N(A1)*10%

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