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