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,RNG1,b,RNG2,c,ROWS(a),d,ROWS(b),e,c+d,IF(SEQUENCE(e)<=c,INDEX(a,SEQUENCE(c),{1,2,3}),INDEX(b,SEQUENCE(e)-c,{1,2,3})))

 

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)

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Ị]

 

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
       r = ActiveCell.Row
       c = Split(Cells(1, ActiveCell.Column).Address, "$")(1)
       [B6] = Range(c & [B2]) 'Gan tham so tu thang
       [B7] = Range("I" & [B3]) 'Gan tham so den thang
       [B8] = Range([B4] & r) 'Gan tham so MIS
       Application.Goto Reference:=Worksheets([B5].Value2).Range("A4"), scroll:=True
       Application.SendKeys ("^q")
    End If
End Sub

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