Mã lệnh hữu ích

TỔNG QUAN BÀI VIẾT

Nhóm hàm Excel tĩnh

INDEX and MATCH with multiple criteria	INDEX(return_range,MATCH(criteria1&criteria2,range1&range2,1),1) INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) , 0, 1), 0)) INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) (range3=criteria3*) , 0, 1), 0))
Hàm lấy tên các sheet	Set a defined name "SheetNames" in the workbook with the formula: =TRANSPOSE(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")) You then just use the formula "=SheetNames" to generate the spill array.
Hàm nối chuỗi theo điều kiện	=TEXTJOIN(", ", TRUE, IF(A1:A10=5, B1:B10, ""))
Hàm lấy giá trị cột bằng chữ	=CHAR(64 + COLUMN())
Công thức định dạng màu xen kẽ trong bảng	=AND(MOD(ROW();2)=1;NOT(ISBLANK($A20)))
Hàm trả về tên sheet	=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Nhóm hàm Excel mảng động

Hàm FILTER với wildcart	=FILTER(A5:B10, ISNUMBER(SEARCH(B1,A5:A10)))
Hàm nối bảng dọc	=INDEX(Range,SEQUENCE(ROWS(Range)),{2}) =LET(a,Range,INDEX(a,SEQUENCE(ROWS(Range)),{2})) =LET(a,A1:B2,b,ROWS(a),INDEX(a,SEQUENCE(b),{1,2,3})) =LET(a,"*",b,A1:A2,c,ROWS(a),d,ROWS(b),e,c+d,IF(SEQUENCE(e)<=c,INDEX(a,SEQUENCE(c),{1}),INDEX(b,SEQUENCE(e)-c,{1})))
Hàm lọc giá trị có trong dãy A nhưng không có trong dãy B	=LET(a,range1,FILTER(a,ISNA(MATCH(a,range2, 0))) =LET(a,RangeA,b,RangeB,LET(c,XLOOKUP(a,b,b,TRUE),FILTER(a,c=TRUE)))
Hàm nối bảng ngang	=LET(a,RNG1,b,RNG2,c,COLUMNS(a),d,COLUMNS(b),e,c+d,r,ROWS(a),IF(SEQUENCE(1,e)<=c,INDEX(a,SEQUENCE( r),SEQUENCE(1,c)), INDEX(b,SEQUENCE(r ),SEQUENCE(1,e)-c))) Hoặc đặt hàm LAMBDA HMerge =LAMBDA(d_1,d_2,LET(c_1,COLUMNS(d_1),c_2,COLUMNS(d_2),c_0,c_1+c_2,r,ROWS(d_1),IF(SEQUENCE(1,c_0)<=c_1,INDEX(d_1,SEQUENCE( r),SEQUENCE(1,c_1)),INDEX(d_2,SEQUENCE(r ),SEQUENCE(1,c_0)-c_1)))) và công thức =HMerge(HMerge(HMerge(B3:C5,E3:G5),HMerge(I3:K5,M3:O5)),HMerge(Q3:S5,A2:C4))
Hàm Lambda nối 3 mảng hoặc nhiều hơn	Đặt hàm LAMBDA Append =LAMBDA(a,b,IF(SEQUENCE(ROWS(a)+ROWS(b))<=ROWS(a),INDEX(a,SEQUENCE(ROWS(a)),{1,2}), INDEX(b,SEQUENCE(ROWS(a)+ROWS(b))-ROWS(a),{1,2}))) Và công thức =Append(Append(Data1,Data2),Data3)
Hàm tách cột	=INDEX(B10#, 0, 2) trong đó B10# là ví dụ bảng cần tách, 0 là ví dụ chỉ số dòng, 2 là ví dụ chỉ số cột
Hàm kết hợp để reverse một bảng	=LET(a,RNG,INDEX(a,SEQUENCE(ROWS(a),1,ROWS(a),-1)))
Hàm trả vè số dòng tối đa từ một bảng	=LET(a,Range,FILTER(a,ROW(a)<MaxRows+ROW(INDEX(a,1,1))))
Hàm loại dòng tiêu đề	=LET(a,UNIQUE(Range#),INDEX(a, SEQUENCE(ROWS(a)-1, 1, 2), SEQUENCE(1, COLUMNS(a))))
Hàm tách một chuỗi có nhiều phần tử được ngăn cách bởi dấu ;	=FILTERXML("<t><s>" & SUBSTITUTE(A1, ";", "</s><s>") & "</s></t>", "//s[position()>0]")
Hàm tạo số thứ tự chiều dọc	=SEQUENCE(ROWS(Rng),1,1,1)
Hàm tạo số thứ tự chiều ngang	=SEQUENCE(1,COLUMNS(Rng),1,1)
Hàm liệt kê các giá trị trùng trong dãy	=LET(a,Rng,FILTER(UNIQUE(a),COUNTIF(a,UNIQUE(a))>1))

PowerQuery

Công thức MCODE lấy giá trị trong bảng DataTable đưa vào làm tham số trong PowerQuery	Excel.CurrentWorkbook(){[Name="ten_bang"]}[Content]{0}[ten_cot] (trong đó 0 là số thứ tự dòng, bắt đầu từ dòng số 0)

Code VBA

Tắt tính toán sheet + khi mở file	Sub Auto_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If Left(Trim(ws.Name), 1) = "+" Then ws.EnableCalculation = False Next ws Set ws = Nothing End Sub
Code tự ẩn sheet (đặt trong sheet)	Private Sub Worksheet_Deactivate() Me.Visible = xlSheetHidden End Sub
Cách mới tự chạy code khi mở file	Đặt đoạn code trong Mục ThisWorkbook Private Sub Workbook_Open() End Sub

×