TIỆN ÍCH MIỄN PHÍ

• Add-in hỗ trợ lâp ngân sách và bcqt, download
• Add-in xóa name rác và style rác trong file Excel, download

UNIQUE FUNCTION CAN BE USED IN DATA TABLE

Download

HÀM KHÔNG DÙNG VBA ĐỌC SỐ RA CHỮ

Download

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. 

INDEX and MATCH with multiple criteria

INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) , 0, 1), 0))

INDEX(return_range,MATCH(criteria1&criteria2,range1&range2,1),1)

INDEX(return_range, MATCH(1, INDEX((range1=criteria1) * (range2=criteria2) *(range3=criteria3) , 0, 1), 0))

 

HÀM EXCEL 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 LẤY GIÁ TRỊ CÓ TRONG DÃY A NHƯNG KHÔNG CÓ TRONG DÃY B

=LET(a,RangeA,b,RangeB,LET(c,XLOOKUP(a,b,b,TRUE),FILTER(a,c=TRUE)))

 

HÀM EXCEL NỐI BẢNG NGANG

DÙNG HÀM LET

=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)))

DÙNG 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 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})))

=Append(Append(Data1,Data2),Data3)

HÀM TÁCH CỘT, HÀNG

=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 LẤY MỘT PHẦN CỦA BẢNG

=LET(a,A1:C10,OFFSET(a,0,0,ROWS(a),COLUMNS(a)))

HÀM JOINIF

=TEXTJOIN(", ", TRUE, IF(A1:A10=5, B1:B10, ""))

HÀM LẤY GIÁ TRỊ CỘT BẰNG CHỮ

=CHAR(64 + COLUMN())

HÀM TRÀN 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 TRÀN TẠO SỐ THỨ TỰ 

chiều dọc =SEQUENCE(ROWS(Rng),1,1,1)

chiều ngang =SEQUENCE(1,COLUMNS(Rng),1,1)

 

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)

ví dụ: Excel.CurrentWorkbook(){[Name="T_TTC"]}[Content]{0}[GIÁ TRỊ]

 Access.Database(File.Contents(Excel.CurrentWorkbook(){[Name="INFO"]}[Content]{0}[Value]), [CreateNavigationProperties=true])

Code tự động refresh query

Sub Auto_Open()

Dim t As Double
t = Now() + TimeSerial(0, 5, 0)
'TimeSerial(Hour,Minute,Second)

Application.OnTime t, "Query"
Application.OnTime t, "Auto_Open"
                          
End Sub

Private Sub Query()
Application.Run ("'C:\miniMis\miniSql.xlam'!Query_Workbook")
End Sub

 

Code tự động tắt tính toán sheet +

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 Refresh All Pivot Tables in Workbook

Sub refreshAllPivots()

Dim Sheet As Worksheet, Pivot As PivotTable

    For Each Sheet In ThisWorkbook.Worksheets

       For Each Pivot In Sheet.PivotTables

           Pivot.RefreshTable

           Pivot.Update

      Next

   Next

End sub

Sub to Refresh

FullCalculate

Hàm Filter with wildcart

Solution

With range A5:B10

=FILTER(A5:B10, ISNUMBER(SEARCH(B1,A5:A10)))

 

With Table1

=FILTER(Table1, ISNUMBER(SEARCH(B1,Table1[Value])))

 

Công thức định dạng màu xen kẽ trong bảng

=AND(MOD(ROW();2)=1;NOT(ISBLANK($A20)))

VBA làm chức năng drilldown

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range([B1].Value2)) Is Nothing Then 'Vung tra cuu
       
    Dim cell As Range: Set cell = ActiveCell     ' Get the active cell
    If cell.HasFormula = False Then Exit Sub     ' Check if the cell contains a formula
    If InStr(1, cell.Formula, "SUM", vbTextCompare) = 0 Then Exit Sub     ' Check if the formula contains SUMIFS function
    ' If the formula contains SUM or SUMIFS function, continue running the code
       
       Application.Calculation = xlManual
       r = ActiveCell.Row
       c = Split(Cells(1, ActiveCell.Column).Address, "$")(1)
       [B13] = Range([B3] & r) 'Loai du lieu
       [B14] = Range(c & [B7]) 'Thuc te ngan sach
       [B15] = Range([B5] & r) '[MIS]
       [B16] = Range([B6] & r) '[Account]
       [B17] = Range(c & [B8]) '[Tu thang]
       [B18] = Range(c & [B9]) '[Den thang]
       [B19] = Range(c & [B10]) '[UsedProfit]
       [B20] = Range(c & [B11]) '[Center]
       [B21] = Range([B4] & r) '[Exclude]
           
    ws = [B2].Value2
    If Sheets(ws).Visible = False Then Sheets(ws).Visible = True
    Sheets(ws).Select
    Application.Calculation = xlAutomatic
    Application.Run ("'C:\miniMis\miniSql.xlam'!Query_Tab")
    Sheets(ws).Range("A7").Select
    End If
End Sub

FUNCTION Return Current Sheet name

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

CODE VBA TỰ ẨN SHEET

Private Sub Worksheet_Deactivate()
    Me.Visible = xlSheetHidden
End Sub

CHIA MÀN HÌNH TRONG TRÌNH DUYỆT EDGE

edge://flags/#edge-split-screen

Sub to Query

Sub QUERY1()
    With CreateObject("ADODB.Recordset")
       .Open ("SELECT * FROM [Sheet1$]"), "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
       & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=No"""
       ActiveSheet.[A5].CopyFromRecordset .DataSource
    End With
End Sub