Chia sẻ Excel công thức đọc số thành chữ

 Bài viết chia sẻ công thức Excel đọc số tiền thành chữ theo các tình huống:

Cách dùng: Thay ô A1 trong công thức (5 chỗ trong công thức)

Máy tính sử dụng dấu , ngăn cách các tham số trong công thức: 

=IF(A1<0,"Âm ", MID("KMHBBNSBTC",LEFT(ROUND(A1,0))+1,1)) & MID(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(TEXT(A1,SUBSTITUTE("0\*0\=0\/0\*0;0\*0\=0\/0\*0",0,"0\-0\+0")),LEN(ROUND(ABS(A1),0))*2-1),"0-0+0*",""),"0-0+0/",""),"0-0+0",""),"0+0",""),"0+"," lẻ"),"+0","+"),"+5","+ lăm"),"1+"," mười"),"+1","+ mốt"),"_=","_"),0," không"),1," một"),2," hai"),3," ba"),4," bốn"),5," năm"),6," sáu"),7," bảy"),8," tám"),9," chín"),"+"," mươi"),"-"," trăm"),"*"," ngàn ,"),"/"," triệu "),",=","="),"="," tỷ,")&"  ",",  ","")),2-(A1<0),999)&" đồng chẵn."

Máy tính sử dụng dấu ; ngăn cách các tham số trong công thức:

=IF(A1<0;"Âm " ;MID("KMHBBNSBTC";LEFT(ROUND(A1;0))+1;1)) & MID(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(TEXT(A1;SUBSTITUTE("0\*0\=0\/0\*0;0\*0\=0\/0\*0";0;"0\-0\+0"));LEN(ROUND(ABS(A1);0))*2-1);"0-0+0*";"");"0-0+0/";"");"0-0+0";"");"0+0";"");"0+";" lẻ");"+0";"+");"+5";"+ lăm");"1+";" mười");"+1";"+ mốt");"_=";"_");0;" không");1;" một");2;" hai");3;" ba");4;" bốn");5;" năm");6;" sáu");7;" bảy");8;" tám");9;" chín");"+";" mươi");"-";" trăm");"*";" ngàn ,");"/";" triệu ");",=";"=");"=";" tỷ,")&"  ";",  ";""));2-(A1<0);999)&" đồng chẵn."

Youtube: Excel Thỉnh Vũ - YouTube

Tiktok: Đào tạo Excel (@excelthinhvu) | TikTok

Đào tạo Excel cho người đi làm và doanh nghiệp

{ĐT+Zalo} 038 696 1334

Xem chi tiết

Đánh số thứ tự trong Excel theo ký hiệu chữ và La Mã

 Trong nhiều biểu mẫu người dùng không đánh số thứ tự theo dạng số 1,2,3,.... mà cần theo dạng ký hiệu A, B, C, D,... hoặc I, II, III, IV,...

Trường hợp 1: Đánh số thứ tự từ A và kết thúc tối đa đến Z

Công thức tại ô bất kỳ: =CHAR(ROW(65:65))

Thực hiện kéo xuống dưới thì kết quả tự tăng lên B, C, D,...

Trường hợp 2: Đánh số thứ tự theo ký hiệu La Mã

Công thức tại ô bất kỳ: =ROMAN(ROW(1:1))

Thì kết quả trả về là I, kéo xuống dưới sẽ tự tăng lên thành II, III, IV,... theo đúng ký hiệu số La Mã

Thông thường việc đánh số thứ tự theo ký hiệu chữ và La Mã thường là đánh kiểu ngắt quãng trong các biểu mẫu có cấu trúc phân nhóm. Khi đó phải có dữ liệu làm căn cứ đánh số và công thức sẽ thực hiện lồng ghép với các hàm khác.

Tình huống ví dụ: Báo cáo được thiết lập ở dạng phân nhóm theo chi nhánh, mỗi chi nhánh có các cửa hàng. Và số thứ tự sẽ được đánh như sau: Chi nhánh được đánh theo số La Mã, Cửa  hàng được đánh số tăng dần từ 1 trong từng chi nhánh

Công thức sẽ căn cứ vào cột C để đánh số La Mã chi nhánh, và căn cứ vào cột B để đánh số thứ tự tăng dần cho cửa hàng.

Công thức tại ô A2 là: 

=IF(C2="",ROMAN(COUNTBLANK($C$2:C2)),IF(B1<>"",1,N(A1)+1))

Trong thực tế thì số liệu sẽ có ở nhiều cột và bố cục thông tin khác nhau, cần có căn cứ cụ thể và quy tắc đánh số thứ tự

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

Cách giảm độ dài của công thức Excel có sử dụng con số

 Trong một công thức có sử dụng nhiều con số, đặc biệt là các con số tròn hàng trăm, ngàn hoặc hàng triệu thì việc gõ nhiều số 0 sẽ dễ gây nhầm lẫn hoặc công thức trở lên rất dài.

Ví dụ: Nếu ngày công >=26 thì chuyên cần là 1 triệu, >=25 thì chuyên cần là 500 ngàn, >=24 thì chuyên cần là 300 ngàn. Còn lại không có chuyên cần

Khi đó công thức là: =IF(A1>=26,1000000, IF(A1>=25,500000,IF(A1>=24,300000,0)))

Từ đây có thể nhận thấy rõ, nếu có nhiều điều kiện hơn hoặc số tiền lớn hơn thì việc gõ số 0 nhiều dễ nhầm lẫn (gõ thừa hoặc thiếu) làm kết quả sai và đồng thời công thức thêm rất dài

Thủ thuật gõ chữ E

Cấu trúc: ...En

Ví dụ: 3E6 thì chính là 3 triệu. Tức n là bao nhiêu số 0 đằng sau

Trường hợp này khi nhấn Enter thì công thức tự động chuyển thành 1 loạt số 0 đằng sau chứ không giữ chữ E. Thủ thuật này sẽ giúp người dùng gõ nhanh hơn và chính xác hơn chứ chưa rút ngắn công thức

Thủ thuật gõ mũ ^

Cấu trúc: x*10^n

Ví dụ: 3*10^6

Trường hợp này thì khi nhấn Enter công thức vẫn giữ nguyên mũ ^. Do vậy khi dùng thủ thuật này với con số tròn hàng triệu, hàng tỉ  thì công thức sẽ rút ngắn đi tương đối

Thủ thuật tách số nhân bên ngoài

Trong tình huống ví dụ ở trên ta có công thức là: 

=IF(A1>=26,1000000, IF(A1>=25,500000,IF(A1>=24,300000,0))) thì phân tích sẽ thấy rằng

1000000 =10*10^5

5000000 = 5*10^5

300000 = 3*10^5

Như vậy ta sẽ đưa 10^5 ra bên ngoài hàm IF thì công thức sẽ thành

=IF(A1>=26,10, IF(A1>=25,5,IF(A1>=24,3,0)))*10^5

Thủ thuật này sẽ giúp chúng ta giảm độ dài của công thức đáng kể

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ách chuỗi theo ký tự xuống dòng trong ô Alt Enter

 Trong nhập liệu thực tế thì thường phát sinh tình huống nhập liệu vào trong ô nhưng ngăn cách giữa các khối bởi dấu xuống dòng trong ô (Alt+Enter). Ví dụ như nhập các số điện thoại, danh sách người phụ thuộc, mô tả các đặc tính sản phẩm,....Và khi đó sẽ cần tách khối này ra các ô độc lập. 

Ngoài cách tách phổ biến hay dùng là Text To Columns thì bài viết này sẽ hướng dẫn tách bằng công thức. 

Giả sử ô A1 chứa các danh sách các số điện thoại được ngăn cách nhau bởi dấu Alt+Enter

Trong Excel thì dấu Alt Enter xuống dòng tương đương với mã code là 10 và hàm CHAR(10) đưa mã code 10 sang dấu xuống dòng. 

Thì công thức tại ô B1 như sau:

=TRIM(MID(SUBSTITUTE(CHAR(10)&$A1,CHAR(10),REPT(" ",500)),500*COLUMN(A:A),500))

Trong công thức này thì người dùng chỉ cần thay địa chỉ $A1 bằng địa chỉ tương ứng của dữ liệu trên bảng tính, các thông số khác dữ nguyên. => Thực hiện kéo công thức sang ngang để tách dữ liệu ra các cột khác nhau

Trường hợp muốn tách ra các dòng khác nhau thì công thức là:

=TRIM(MID(SUBSTITUTE(CHAR(10)&A$1,CHAR(10),REPT(" ",500)),500*ROW(1:1),500))

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ấy ra danh sách số còn thiếu trong dãy số liên tục

 Một số trường hợp cần tìm ra số còn thiếu trong dãy số liên tục như sau:

- Danh sách số hóa đơn cần tìm ra số bị khuyết

- Đánh mã nhân sự, đối tượng theo danh sách tăng dần và cần tìm ra số thứ tự bị khuyết

-...

Tình huống giả lập tìm số hóa đơn khuyết: Danh sách từ A2:A15 và trong đó có những số hóa đơn bị khuyết

Bước 1: Tìm số hóa đơn nhỏ nhất và lớn nhất trong dải số

- Số hóa đơn nhỏ nhất: 

=AGGREGATE(15,6,--$A$2:$A$15,1)

- Số hóa đơn lớn nhất:

=AGGREGATE(14,6,--$A$2:$A$15,1)

Bước 2: Tạo mảng danh sách số liên tục căn cứ vào số nhỏ nhất và lớn nhất

=ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1)))

Bước 3: Dùng hàm MATCH tham chiếu theo mảng liên tục đã tạo

=MATCH(ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1))),--A2:A15,0)

Bước 4: Đưa hàm AGGREGATE để lấy ra danh sách các số. Công thức tại ô B2 và kéo xuống cho các ô còn lại để ra danh sách số hóa đơn khuyết

=AGGREGATE(15,6,ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1)))/ISNA(MATCH(ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1))),--$A$2:$A$15,0)),ROW(1:1))

Với Office 365 thì công thức thay thế sẽ ngắn gọn hơn:

=LET(x,--$A$2:$A$15, y,ROW(INDIRECT(AGGREGATE(15,6,x,1) & ":"& AGGREGATE(14,6,x,1))),FILTER(y,ISNA(MATCH(y,x,0))))

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 lập công thức tính dồn khi kéo cho các ô liên tiếp trong Excel

 NHỮNG TRƯỜNG HỢP LẬP CÔNG THỨC TÍNH DỒN TRONG THỰC TẾ:

- Đánh số thứ tự tăng dần bằng công thức nhưng chỉ đánh cho dòng có dữ liệu

- Tính tổng, trung bình, đếm dồn

- Đánh số lần trùng lặp

...

NHẬN DẠNG CÔNG THỨC TÍNH DỒN:

Công thức đó có quét vùng dữ liệu nhưng vùng này được cố định điểm đầu hoặc điểm cuối mà không cố định cả vùng

Ví dụ: $A$1:A1, A1:$A$1

Khi kéo công thức thì phần không cố định (Tức phần không có dấu $) sẽ được tịnh tiến

MỘT SỐ TÌNH HUỐNG SỬ DỤNG CÔNG THỨC TÍNH DỒN

Tình huống 1: Công thức đánh Số thứ tự tăng dần mà chỉ đánh cho dòng có dữ liệu

- Cột B là cột nhận dạng xem có dữ liệu hay ko, nếu có thì mới đánh Stt

- Điểm tịnh tiến bắt đầu từ A1, khi kéo công thức thì sẽ tìm ra Stt trước đó lớn nhất và +1 để ra số tiếp theo

- Công thức:

=IF(B2="","",MAX($A$1:A1)+1)

Tình huống 2: Công thức tính số tồn theo dòng

- Tính số tồn tại cột F theo từng sản phẩm theo nguyên tắt: Cộng nhập, trừ xuất

- Điểm bắt đầu tịnh tiến là từ dòng số 2

- Công thức:

=SUMIFS($D$2:D2,$C$2:C2,C2,$E$2:E2,"N")-SUMIFS($D$2:D2,$C$2:C2,C2,$E$2:E2,"X")

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ấy ra danh sách số ngẫu nhiên không trùng

 Trong một số tình huống thực tế cần lấy ra dải số ngẫu nhiên không trùng lặp để giao việc hay trúng thưởng,...như:

- Lấy ra 30 mã nhân viên bất kỳ trong 100 nhân viên không trùng lặp

- Lấy ra 10 mã số khách hàng hay đối tượng cụ thể trong danh sách 1000

-...

Bài viết này sẽ chia sẻ công thức Excel lấy ra danh sách dải số ngẫu nghiên trong khoảng dải số mà không trùng lặp:

Ví dụ tình huống như sau: 

Cần lấy ra dải số ngẫu nhiên trong khoảng từ 10 đến 100 mà các số lấy ra không được phép trùng lặp

Công thức đặt tại ô A2 của sheet bất kỳ như sau:

=AGGREGATE(RANDBETWEEN(14,15),6,ROW($10:$100)/(COUNTIF($A$1:A1,ROW($10:$100))=0),RANDBETWEEN(1,SUMPRODUCT(--(COUNTIF($A$1:A1,ROW($10:$100))=0))))

và kéo xuống dưới để chạy công thức.

Lưu ý: 

- Phần ROW($10:$100) là khoảng số từ 10 đến 100. Trường hợp người dùng thay thế bằng khoảng số khác thì thay tương ứng. Ví dụ từ 1000 đến 2000 thì sẽ là ROW($1000:$2000)

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

 Từ bảng số liệu chi tiết, người dùng thường có nhu cầu cần đếm số phát sinh duy nhất theo một đối tượng nào đó như: Đếm xem có phát sinh bao nhiêu sản phẩm, bao nhiêu nhân viên,....

Để giải quyết bài toán này thì có nhiều cách như:

- Copy ra 1 vùng khác và dùng Remove Duplicate để loại trùng lặp rồi đếm

- Dùng Pivot Table

Bài viết dưới đây sẽ chia sẻ về công thức đếm giá trị không trùng lặp như sau:

Giả sử tình huống bảng số liệu như hình ảnh. Cần đếm từ B2:B12 có bao nhiêu mã sản phẩm.

Thì công thức đếm không trùng lặp là:

=SUMPRODUCT(1/COUNTIF(B3:B12,B3:B12))

Với Office 365 thì có hỗ trợ hàm UNIQUE thì có thể dùng công thức

=COUNTA(UNIQUE(B3:B12))

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ách tên Tỉnh thành từ chuỗi địa chỉ

 Trong nhiều lĩnh vực như quản lý nhân sự, nhân khẩu, thông tin xuất xứ,...thì cần lấy tên tỉnh từ chuỗi địa chỉ để quản trị thông tin, trích lọc dữ liệu và tổng hợp báo cáo theo tỉnh/thành

Bài viết sau đây sẽ chia sẻ công thức tách tên tỉnh/thành ngắn gọn và dễ sử dụng như sau:

Giả sử ô A2 chứa chuỗi địa chỉ tỉnh thành (địa chỉ luôn ở bên tay phải của chuỗi), sử dụng dấu phẩy (,) ngăn cách trong chuỗi

Lập công thức tại ô B2 để lấy ra tên Tỉnh/Thành:

Công thức:

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

Cách sử dụng công thức:

- Thay ô A2 tương ứng

- Nếu chuỗi địa chỉ không dùng dấu phẩy (,) mà dùng dấu ; hoặc dấu - thì thay bằng dấu tương tứng. Ví dụ sử dụng dấu - thì công thức là: =TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",99)),99))

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ạo tên ngắn viết tắt

 Trong thiết lập email, tên tài khoản nội bộ trong doanh nghiệp thì thường sử dụng chính họ và tên viết tắt để tạo. Ví dụ: Vũ Đức Thỉnh thì sẽ tạo thành ThinhVD

Bài viết dưới đây sẽ hướng dẫn cách dùng Excel giải quyết bài toán này như sau:

Bước 1: Chuyển họ tên có dấu thành không dấu

- Cách 1: Dùng Unikey theo hướng dẫn Link hướng dẫn

- Cách 2: Dùng VBA theo hướng dẫn Link hướng dẫn

Bước 2: Dùng công thức hoặc thủ thuật để chuyển họ và tên không dấu thành tên ghép viết tắt

- Cách dùng thủ thuật Flash Fill: Tạo mẫu tên ghép viết tắt cho 2 dòng đầu tiên (Ví dụ C2,C3 nhập tay), Bôi đen từ C2 đến hết các dòng dữ liệu còn lại trong cột C => rồi vào Data chọn Flash Fill hoặc nhấn phím Ctrl + E. Lưu ý cách này chỉ dùng với Office 2013 trở lên

- Cách dùng công thức có hàm TEXTJOIN Với trường hợp Office 2019 hoặc Office 365 thì công thức là:

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",9)),9)) & LEFT(TEXTJOIN("",TRUE,LEFT(TRIM(MID(SUBSTITUTE(" "&B2," ",REPT(" ",99)),99*ROW($1:$9),99)))),LEN(TEXTJOIN("",TRUE,LEFT(TRIM(MID(SUBSTITUTE(" "&B2," ",REPT(" ",99)),99*ROW($1:$9),99)))))-1)

- Cách dùng công thức qua các cột phụ dành cho tất cả các bản Office

   + B1: Insert thêm 2 cột để tách Tên và Họ tên lót không dấu (C và D) thì

             Công thức tại C2 là: =TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",9)),9))

             Công thức tại D2 là: =LEFT(B2,LEN(B2)-LEN(C2)-1)

   + B2: Insert thêm 5-7 cột để lấy chữ cái đầu tiên của cột Họ và tên lót (Từ cột E đến I)

             Công thức tại E2 là: 

             =LEFT(TRIM(MID(SUBSTITUTE(" "&$D2," ",REPT(" ",99)),99*COLUMN(A:A),99)))

            Thực hiện kéo sang ngang và xuống dưới cho các ô còn lại

    + B3: Lập công thức ghép Tên và các chữ cái lấy ra

             Công thức tại ô J2 là: =C2&E2&F2&G2

Ngoài ra có một số trường hợp dùng thủ thuật Ctrl H để tách họ tên, dùng VBA để lập hàm riêng cho trường hợp nà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

Chia sẻ công thức Excel đánh số phiếu thu chi tăng dần

 Trong kế toán quỹ thì thường phát sinh nhu cầu đánh lại số phiếu để phục vụ import vào phần mềm kế toán hoặc phục vụ thuận tiện cho lưu trữ chứng từ, in ấn...

Khi đánh số phiếu thường sẽ phát sinh nhu cầu thêm tiền tố (PT, PC) và hậu tố (Ví dụ: /2109 tức của tháng 09 năm 2021) => Ví dụ số phiếu chi là: PC0005/2109: Tức phiếu chi số 0005

Tình huống giả lập (như hình ảnh). Trong đó:

- Cột ngày được sắp xếp tăng dần

- Mỗi dòng tương ứng với 1 phiếu. Nếu dòng đó có phát sinh số tiền bên thu thì đánh PT, và bên chi là PC

- Số phiếu gồm 11 ký tự: 2 ký tự đầu là loại phiếu (PT, PC), 4 ký tự sau là số thứ tự của phiếu được đánh tăng dần theo loại phiếu thu và phiếu chi, 5 ký tự đằng sau là hậu tố thể hiện tháng và năm

Công thức mẫu:

=IF(C4<>"","PT"&TEXT(IFERROR(LOOKUP(2,1/($C$3:C3>0)/(LEFT($A$3:A3,2)="PT"),MID($A$3:A3,3,4))+1,1),"0000"),"PC"&TEXT(IFERROR(LOOKUP(2,1/($D$3:D3>0)/(LEFT($A$3:A3,2)="PC"),MID($A$3:A3,3,4))+1,1),"0000"))&TEXT(B4,"""/""yymm")

Cách dùng công thức:

- Dòng số 4 là dòng bắt đầu của dữ liệu, cột C là cột số tiền thu

- PT và PC là tiền tố của số phiếu

- $A$3:A3,$C$3:C3, $D$3:D3 là dòng tiêu đề của từng cột. Bắt buộc phải để cố định 1 nửa

- 0000 là format số phiếu tăng dần ở giữa có 4 ký tự. Nếu muốn đánh 3 ký tự thì sửa thành 000

- yymm là kiểu format lấy hậu tố có cả tháng và năm. Nếu chỉ lấy hậu tố theo năm thì sửa thành yy

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ấy danh sách duy nhất

 Trong thực tế, nhiều trường hợp cần lấy ra danh sách duy nhất từ dữ liệu phát sinh chi tiết để thuận tiện cho việc làm báo cáo. Thông thường người dùng hay tạo bằng cách dùng Remove Duplicate hoặc Pivot Table

Trong bài viết này sẽ chia sẻ về công thức Excel lấy danh sách duy nhất.

Hàm UNIQUE với Office 365

Hàm UNIQUE là hàm rất hay có trong Microsoft Excel 365 => loại trùng và công thức tự trả về danh sách cả mảng giá trị duy nhất

Ví dụ: Từ A2:A11 là vùng dữ liệu phát sinh chi tiết có trùng lặp

Công thức :

= UNIQUE(A2:A11)

Hàm INDEX kết hợp cùng AGGREGATE, COUNTIF, ROW và IFERROR

Ví dụ: Từ A2:A11 là vùng dữ liệu phát sinh chi tiết có trùng lặp

Công thức:

=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,ROW($1:$10)/(COUNTIF($B$1:B1,$A$2:$A$11)=0),1)),"")

Cách sử dụng:

- $A$2:$A$11: Thay bằng vùng dữ liệu tương ứng

- ROW($1:$10): Độ lớn dữ liệu tương ứng. Ví dụ dữ liệu có 1000 dòng thì sẽ là ROW($1:$1000)

- $B$1:B1: Tọa độ ô bên trên của ô làm công thức. Ví dụ: Công thức đặt tại ô C3 thì thay bằng $C$3:C3

=> Thực hiện kéo xuống dưới bao giờ xuất hiện dòng trống

Lưu ý: Nếu số dòng dữ liệu nguồn lớn thì công thức kết hợp này sẽ làm file excel chạy chậ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

Chia sẻ công thức Excel giờ công của ca làm việc đêm sang ngày hôm sau

 Trong nhiều doanh nghiệp sản xuất, dịch vụ thường phát sinh ca làm việc buổi đêm với giờ vào (In) và giờ ra (Out) là hai ngày khác nhau. Khi đó, nếu chỉ xét về thời gian thì giờ Out sẽ nhỏ hơn giờ In.

Giả sử quy định giờ vào là 20:00 và giờ ra là 06:00, Nghỉ giữa ca 1 tiếng từ 00:00 đến 01:00. Khi đó sẽ có những tình huống như sau:

- Vào trước 20:00 thì tính từ thời điểm 20:00, vào sau 20:00 thì tính từ thời điểm vào thực tế

- Về trước 00:00 thì tính giờ ra thực tế. Về trong khoảng từ 00:00 đến 01:00 thì tính thời điểm về là 00:00

- Vào trong khoảng thời gian từ 00:00 đến 01:00  thì tính thời điểm vào là 01:00, vào sau thời điểm 01:00 thì tính theo thời điểm vào thực tế

- Về trước 06:00 thì tính theo thời điểm về thực tế. Về sau 06:00 thì tính đến 06:00

CÁC BƯỚC XỬ LÝ LẬP CÔNG THỨC NHƯ SAU:

Bước 1: Quy giờ IN theo giờ chuẩn

Công thức: 

=MAX(--A2,TIME(20,0,0))*(--A2>TIME(6,0,0))+(--A2<TIME(6,0,0))*MAX(A2,TIME(1,0,0))

Giải thích: Xét giờ vào trước hoặc sau 20:00 và trước hoặc sau 01:00 để lập công thức

Bước 2: Quy giờ OUT theo giờ chuẩn

Công thức:

=MIN(--B2,TIME(6,0,0))*(--B2<TIME(12,0,0))+MIN(--B2,TIME(0,0,0))*(--B2>TIME(20,0,0))

Giải thích: Xét giờ ra trước và sau 00:00 hoặc trước và sau 06:00 để lập công thức

Bước 3: Tính giờ công

Công thức:

=IF(C2>D2,1-C2+D2,D2-C2)*24-(C2>=TIME(20,0,0))

Giải thích: Xét theo giờ quy chuẩn vào và quy chuẩn ra để lập công thức

*** Đối với doanh nghiệp có quy định giờ làm ca đêm khác thì chỉ cần thay đổi hàm TIME trong công thức

*** Với doanh nghiệp có quy định làm tròn thời gian thì lồng thêm các hàm làm tròn hoặc hàm IF để xử lý

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 phép năm

 Bài toán tình huống tính phép của một nhân sự bất kỳ như sau:

Tính ngày nghỉ phép cho năm 2021, một năm 12 ngày phép: 

- Nếu vào làm trước năm 2021 thì trước năm 2021 thì được tính từ tháng 1/2021. 

- Nếu nhân sự vào trong lăm 2021: nếu ngày vào làm từ ngày trước ngày 15 (<=15) thì sẽ được tính phép từ tháng đó, nếu sau ngày 15 (>16) thì sẽ tính từ tháng sau

- Nếu nhân sự nghỉ việc trong năm 2021: Nếu ngày nghỉ việc sau ngày 15 (>=15) thì được tính phép hết tháng đó, nếu trước ngày 15 (<15) thì sẽ tính phép đến hết tháng trước đó

- Cứ thêm 3 năm thâm niên thì được tăng thêm 1 ngày phép

Giả sử cột ngày vào làm là cột A (bắt đầu từ A3), cột ngày nghỉ là cột B (bắt đầu từ B3) thì công thức tại ô C3 là:

=DATEDIF(EOMONTH(MAX(A3,DATE(2021,1,1))-15,0),MIN(IF(B3="",9^9,EOMONTH(B3,(DAY(B3)>=15)-1)),DATE(2021,12,31))+1,"m")+INT((DATEDIF(EOMONTH(A3-15,0),MIN(IF(B3="",9^9,EOMONTH(B3,(DAY(B3)>=15)-1)),DATE(2021,12,31))+1,"y"))/3)

Cách dùng công thức: Chỉ cần thay hàm DATE tương ứng là ngày bắt đầu và kết thúc của 1 năm bất kỳ và thay địa chỉ ô A3, B3 trong công thức

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 giờ làm việc hành chính trừ giờ nghỉ

 Trong thực tế tính công theo giờ công thì không đơn thuần lấy giờ ra trừ đi giờ vào để ra thời gian chấm công, mà còn phụ thuộc vào các yếu tố khác như:

- Đến sớm hơn giờ vào quy định thì lấy theo giờ vào quy định. Ví dụ: Giờ IN quy định là 8:00 mà nhân sự đến từ lúc 7:45 thì vẫn tính từ lúc 8:00

- Về muộn hơn giờ ra quy định thì vẫn lấy theo giờ ra quy định. Ví dụ: Giờ OUT quy định là 17:00 mà nhân sự về lúc 17:45 thì giờ hành chính vẫn tính đến 17:00 (sau đó là tính thời gian tăng ca riêng)

- Nghỉ giữa giờ 1 tiếng. Ví dụ từ 12:00 đến 13:00. Thì sẽ phải loại 1 tiếng nghỉ trưa này đi. Tuy nhiên có phát sinh tình huống  nhân sự nghỉ buổi sáng hoặc nghỉ buổi chiều thì không xét trừ 1 tiếng nghỉ trưa

Hướng dẫn lập công thức xử lý theo hình ảnh dưới đây:

Lập công thức quy về giờ vào (IN) chuẩn: Công thức tại cột D (ô D3)

- Xét tình huống vào trước và sau 8:00

  *** Công thức: =MAX(--B3,TIME(8,0,0))

  *** Diễn giải công thức: Nếu >8:00 thì lấy giờ out thực tế, nếu <8:00 thì lấy đúng 8:00

- Xét tiếp tình huống vào trước và vào sau: 13:00

  *** Công thức xét cả vào trước sau 8:00 và trước sau 13:00 : 

=IF(--B3>=TIME(13,0,0),--B3,IF(--B3>TIME(12,0,0),TIME(13,0,0),MAX(--B3,TIME(8,0,0))))

  *** Diễn giải công thức Nếu >13:00 thì lấy từ giờ out thực tế, nếu <13:00 và lớn hơn 12:00 thì lấy 13h, còn lại thì lấy theo giờ vào theo công thức xét ở tình huống 8:00 ở trên

=> Như vậy công thức xử lý triệt để giờ IN là:

=IF(--B3>=TIME(13,0,0),--B3,IF(--B3>TIME(12,0,0),TIME(13,0,0),MAX(--B3,TIME(8,0,0))))

Lập công thức quy về giờ ra (OUT) chuẩn (theo công hành chính): Công thức tại ô E3

- Xét tình huống về trước và sau 17:00

  *** Công thức: =MIN(--C3,TIME(17,0,0))

  *** Diễn giải công thức: Nếu >17:00 thì lấy 17:00, ngược lại thì lấy đúng giờ ra thực tế

- Xét tiếp tình huống ra trước và ra sau 12:00

  *** Công thức: =IF(--C3<TIME(12,0,0),--C3,IF(--C3<TIME(13,0,0),TIME(12,0,0),MIN(--C3,TIME(17,0,0))))

  *** Diễn giải công thức: Nếu ra trước 12h thì lấy đúng giờ ra thực tế, nếu ra >12:00 và <13:00 thì xét ra lúc 12:00, còn lại lấy theo tình huống ra trước sau 17:00

=> Như vậy công thức xử lý triệt để giờ OUT là: 

=IF(--C3<TIME(12,0,0),--C3,IF(--C3<TIME(13,0,0),TIME(12,0,0),MIN(--C3,TIME(17,0,0))))

Lập công thức tính giờ công hành chính

Qua 2 bước lập công thức xét giờ IN và giờ OUT thì ta sẽ được kết quả như sau:

- Nếu giờ OUT quy đổi - IN quy đổi<=4 thì không trừ 1 tiếng nghỉ trưa

- Nếu giờ OUT quy đổi - IN quy đổi>4 thì trừ 1 tiếng nghỉ trưa

=> Như vậy công thức tính giờ công hành chính là: 

=IF(E3-D3<4,E3-D3,E3-D3-1)*24

Ngoài ra, nếu trường hợp quên chấm công vào hoặc quên chấm công ra thì người dùng phải sửa tay dữ liệu chấm công hoặc lồng hàm IF xét ô trố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 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

Chia sẻ công thức Excel tách ngày tháng ra khỏi chuỗi văn bản phức tạp

 Trong thực tế thường xuyên phát sinh những số liệu có nhiều thông tin nhập trong 1 ô (phần diễn giải) và để thuận tiện hơn cho thống kê theo dõi thì sẽ cần phải bóc tách những trường thông tin đó ra. Trong đó ngày tháng được nhập trong nội dung diễn giải cũng là tình huống hay gặp phải như.

Bài viết dưới đây sẽ chia sẻ công thức sử dụng để tách ngày tháng Date ra khỏi chuỗi văn bản phức tạp, có nhiều thông tin khác đi cùng

Giả lập: ô A1 chứa chuỗi phức tạp trong đó có dữ liệu ngày tháng DATE

Thì công thức tách ngày tháng Date là: 

=MID(A1,SEARCH(" ??/??/????",A1)+1,10)

Nhưng công thức trên tách ra thì vẫn là 1 giá trị văn bản. Để chuyển đổi sang giá trị đúng ngày tháng thì có 2 cách:

Cách 1: Dùng hàm DATEVALUE lồng thêm vào nếu định dạng của đồng hồ mày tính trùng với định dạng của chuỗi tách ra (ví dụ: cùng là dd/mm/yyyy) thì công thức lồng là:

=DATEVALUE(MID(A1,SEARCH(" ??/??/????",A1)+1,10))

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

=DATE(MID(A1,SEARCH(" ??/??/????",A1)+7,4),MID(A1,SEARCH(" ??/??/????",A1)+4,2),MID(A1,SEARCH(" ??/??/????",A1)+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 đá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