TIỆN ÍCH MIỄN PHÍ

• Add-in xóa name rác và style rác trong file Excel, download
• Add-in dịch tự động trong Microsoft Word, download, hướng dẫn sử dụng

UNIQUE FUNCTION CAN BE USED IN DATA TABLE

Download

 

INDEX and MATCH with multiple criteria

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 EXCEL NỐI BẢNG DỌC

=LET(d,DATA,r,ROWS(d),INDEX(d,SEQUENCE(r),{1,2,3,4}))

=LET(d,DATA,r,ROWS(d),r0,r,IF(SEQUENCE(r0)<=r,INDEX(d,SEQUENCE(r),{1,2,3})))

=LET(d_1,data1,d_2,data2,r_1,ROWS(d_1),r_2,ROWS(d_2),r_0,r_1+r_2,IF(SEQUENCE(r_0)<=r_1,
INDEX(d_1,SEQUENCE(r_1),{1,2,3}),
INDEX(d_2,SEQUENCE(r_0)-r_1,{1,2,3})))

 

HÀM EXCEL NỐI BẢNG NGANG

DÙNG HÀM LET

=LET(d_1,B3:C5,d_2,E3:G5,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)))

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

 

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
 

Sub to Refresh

FullCalculate

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

 

Sub to Query

Sub Query()
    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"""
        Sheet1.Range("A2").CopyFromRecordset .DataSource
    End With
End Sub