Viết hàm tự tạo theo kiểu Excel 365

[Ngô Hải Đăng]

Nghiên cứu trên diễn đàn thì phát hiện được cái Application.Caller và sau đây là ý tưởng của mình:
1. Code trên ThisWorkbook

Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If IsUDF Then
        SetResult
        rCaller.Formula = sFormula
    End If
End Sub

Private Sub SetResult()
    Dim r0&, c0&
    On Error Resume Next
    r0 = UBound(aResult, 1) - LBound(aResult, 1)
    c0 = UBound(aResult, 2) - LBound(aResult, 2)
    On Error GoTo 0
    If c0 = 0 Then
        rCaller.Resize(1, r0 + 1) = aResult
    Else
        rCaller.Resize(r0 + 1, c0 + 1) = aResult
    End If
End Sub

2. Code trên Module

Option Explicit

Public IsUDF As Boolean
Public rCaller As Range
Public aResult As Variant
Public sFormula As String

Function MyUDF()
    If IsUDF Then
        MyUDF = aResult
        IsUDF = False
        Set rCaller = Nothing
        If IsArray(aResult) Then Erase aResult Else aResult = Empty
    Else
        IsUDF = True
        Set rCaller = Application.Caller
        sFormula = rCaller.Formula
        
        'Dim tmp As String: tmp = "1 GIA TRI"
        'Dim tmp: tmp = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
        Dim tmp(10, 15) As Long
        aResult = tmp
    End If
End Function

3. Gõ =MyUDF() trên Sheet để test.

Có thể thử với kết quả là 1 giá trị, mảng 1 chiều và mảng 2 chiều. Mong được học hỏi thêm kinh nghiệm từ mọi người.

 

[excel_lv1.5]

Tôi đang nói về kỹ thuật tạo hàm trả về mảng động trên Worksheet mà. Bạn có hiểu có chế của loại hàm này trên worksheet không? Nếu hiểu thì bạn có thể lấy ví dụ mà các công cụ hay ngôn ngữ bạn nói để tạo ra nó, chay trên các phiên bản Excel thấp hơn Excel 365 nhé.

Bạn nói excel 365, exel 365 có sẵn 2 công cụ đó, phiên bản thấp hơn như 2013 vẫn có 2 công cụ đó, power pivot , power query cũng chạy trả kết qủa ở worksheet

Nhờ hỗ trợ cú pháp truy vấn trong ADODB

Em đang có vướng mắc xin nhờ mọi người giúp. Dữ liệu giả định và kết quả cần có em đã để trong file đính kèm. Em đang mắc về cú pháp truy vấn - JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng) - Tính tổng doanh thu theo từng KH (cần quy đổi giá trị đối với trường hợp Currency là USD)...

www.giaiphapexcel.com

Bài #12 tôi sử dụng power query và Dax trong Model, hàm Dax trong model sẽ bị biến đổi theo điều kiện chọn lựa, VBA hay công thức bên excel không làm được điều này

 

[befaint]

họ xử lý co giãn không khó

Họ đặc biệt quan tâm, quan tâm cực kỳ sâu sắc nhé.
Google Sheets có đặc điểm riêng về số lượng cells trên một file, nó không phải là 1 hằng số.
Và mọi bài toán đều xuất phát từ lý thuyết cấu trúc bảng dữ liệu chuẩn. Không chơi trò đẩy cái đã có đi nơi khác để chiếm chỗ, những cái bị đẩy đi sẽ va lung tung.
Nên đồng ý cái từ "chủ quan" thôi.

 

[HeSanbi]

cái đó + cái bài số 1 + ...
Có thể có nhiều cách viết khác nhau Or xài nhiều Tools khác nhau của Bill ===> và cái đích hướng tới là cái gì mà cho ta kết quả là ok rồi

Ý bác dùng tools chắc là nhờ Solver



@Nguyễn Duy Tuân
Cái vụ co giãn dòng là viết cho vui thôi anh.
Một lập trình viên có kinh nghiệm không bao giờ làm như vậy.

Có những công thức người viết sử dụng INDIRECT, ... tức là không tham chiếu trực tiếp vùng đối tượng, thì việc co giản vùng dữ liệu dưới hoặc phải sẽ làm hỏng ứng dụng của người ta.

Excel cho mình có thể tạo nhiều Trang, Không ai dại đi để dữ liệu dưới hoặc phải hoặc trái hoặc trên vùng dữ liệu co giãn, mà không cho nó vào trang khác.

Người làm như vậy chắc là có tài xử lý dữ liệu động.

Theo em nếu bỏ chất xám ra làm điều như vậy thì nên để chất xám làm chuyện khác lại có ích hơn nhiều.

Cái vụ co giãn em sử dụng API làm 1 phát 1 nhờ các phương thức như:
UseRanged, CurrentRegion kết hợp Bắt sự kiện Worksheet_Change

Sau khi thử qua, em thấy thật phí sức. Để thời gian nguyên cứu Python, JavaScript hoặc Big Data còn có tương lai gấp bội.

 

[Nguyễn Duy Tuân]

Bạn nói excel 365, exel 365 có sẵn 2 công cụ đó, phiên bản thấp hơn như 2013 vẫn có 2 công cụ đó, power pivot , power query cũng chạy trả kết qủa ở worksheet

Nhờ hỗ trợ cú pháp truy vấn trong ADODB

Em đang có vướng mắc xin nhờ mọi người giúp. Dữ liệu giả định và kết quả cần có em đã để trong file đính kèm. Em đang mắc về cú pháp truy vấn - JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng) - Tính tổng doanh thu theo từng KH (cần quy đổi giá trị đối với trường hợp Currency là USD)...

www.giaiphapexcel.com

Bài #12 tôi sử dụng power query và Dax trong Model, hàm Dax trong model sẽ bị biến đổi theo điều kiện chọn lựa, VBA hay công thức bên excel không làm được điều này

Tôi đã xem #12 bạn nói, đó là Tool đổ về vùng dữ liệu trên worksheet chứ không phải hàm trên worksheet. Ngôn ngữ hay cách viết công thức là trong bản thân tool, kết quả là data chứ không phải formula có tham gia của hàm.

 

[Kiều Mạnh]

Ý bác dùng tools chắc là nhờ Solver
Ko cần tới cái đó ... mà mạnh cũng chưa biết xài nó như thế nào luôn

 

[thuyyeu99]

Họ đặc biệt quan tâm, quan tâm cực kỳ sâu sắc nhé.
Google Sheets có đặc điểm riêng về số lượng cells trên một file, nó không phải là 1 hằng số.
Và mọi bài toán đều xuất phát từ lý thuyết cấu trúc bảng dữ liệu chuẩn. Không chơi trò đẩy cái đã có đi nơi khác để chiếm chỗ, những cái bị đẩy đi sẽ va lung tung.
Nên đồng ý cái từ "chủ quan" thôi.

Cũng có thể họ nghĩ như vậy

 

[Kiều Mạnh]

Gửi @HeSanbi chỉ cần 3 cái sau là xong he ... còn cái khác mạnh ko có biết
1/ Application.Caller
2/ Evaluate
3/ Application.Index

 

[Nguyễn Duy Tuân]

Ý bác dùng tools chắc là nhờ Solver



@Nguyễn Duy Tuân
Cái vụ co giãn dòng là viết cho vui thôi anh.
Một lập trình viên có kinh nghiệm không bao giờ làm như vậy.

Có những công thức người viết sử dụng INDIRECT, ... tức là không tham chiếu trực tiếp vùng đối tượng, thì việc co giản vùng dữ liệu dưới hoặc phải sẽ làm hỏng ứng dụng của người ta.

Excel cho mình có thể tạo nhiều Trang, Không ai dại đi để dữ liệu dưới hoặc phải hoặc trái hoặc trên vùng dữ liệu co giãn, mà không cho nó vào trang khác.

Người làm như vậy chắc là có tài xử lý dữ liệu động.

Theo em nếu bỏ chất xám ra làm điều như vậy thì nên để chất xám làm chuyện khác lại có ích hơn nhiều.

Cái vụ co giãn em sử dụng API làm 1 phát 1 nhờ các phương thức như:
UseRanged, CurrentRegion kết hợp Bắt sự kiện Worksheet_Change

Sau khi thử qua, em thấy thật phí sức. Để thời gian nguyên cứu Python, JavaScript hoặc Big Data còn có tương lại gấp bội.

Chính vì thế mình mới nói là nó không phải dễ khi xét cả những đặc tính hỗ trợ. Vì chơi với Excel nên mọi người cũng muốn khai thác cho thỏa đam mê và hiểu biết thôi, bao năm nay chỉ hàm và công thức thuần túy mọi người vẫn yêu Excel. Những bài toán cụ thể mà muốn chạy độc lập thì người ta dùng các công cụ hay ngôn ngữ lập trình khác tạo ra thì khỏe quá rồi.

 

[excel_lv1.5]

Tôi đã xem #12 bạn nói, đó là Tool đổ về vùng dữ liệu trên worksheet chứ không phải hàm trên worksheet. Ngôn ngữ hay cách viết công thức là trong bản thân tool, kết quả là data chứ không phải formula có tham gia của hàm.

Vâng , bạn nói về mảng động thì tôi vẫn viết hàm để tạo mạng động đó thôi, tôi chỉ dùng Filter 1 lần nhưng khi đưa vào hoàn cảnh và điều kiện nó sẽ tự động filter theo nhiều chiều khác nữa, công thức đó tôi cũng kéo ra worksheet y như hàm excel
Bạn nói mới chớm nở ,đơn giản thô, là chỉ bao gồm VBA và hàm của excel??? Các công cụ trên Microsoft viết hỗ trợ cho excel có từ lâu nên không tính ???
Có lẽ tôi hiểu sai bạn, nên xin phép dừng ở đây

 

[HeSanbi]

Gửi @HeSanbi chỉ cần 3 cái sau là xong he ... còn cái khác mạnh ko có biết
1/ Application.Caller
2/ Evaluate
3/ Application.Index

Hết hồn
Bác biết Evaluate cứ gọi nó là nó gọi lại một thủ tục hai lần chưa.
Bác tìm hiểu thêm đi rồi sẽ biết vì sao Microsoft làm như vậy.

Và Evaluate không thể tác động lên Đối tượng tĩnh.
Chẳng hạn như tô màu nền, chữ, ...., hoặc xóa vùng.

 

[Kiều Mạnh]

Hết hồn
Bác biết Evaluate cứ gọi nó là nó gọi lại một thủ tục hai lần chưa.
Bác tìm hiểu thêm đi rồi sẽ biết vì sao Microsoft làm như vậy.

Và Evaluate không thể tác động lên Đối tượng tĩnh.
Chẳng hạn như tô màu nền, chữ, ...., hoặc xóa vùng.

Có thể Mạnh chưa hiểu hết nhưng Mạnh đang xài 3 cái đó... thật đấy có điều làm biếng úp code xong họ lại cho vào dll xong úp cho xem phim nhột lắm .... sự thật là thế

Code két quan trọng nhất là ý tưởng và sự khởi đầu ... có thể sau này ai đó viết lại hay gấp 10 lần nhưng đó cũng chỉ là thứ lông vịt ... không phải là người khai phá ra nó

Vì lý do đó nên Mạnh Ko úp code chứ ko hẹp hòi chi mấy thứ đó

 

[ptm0412]

Có những công thức người viết sử dụng INDIRECT, ... tức là không tham chiếu trực tiếp vùng đối tượng, thì việc co giản vùng dữ liệu dưới hoặc phải sẽ làm hỏng ứng dụng của người ta.
Excel cho mình có thể tạo nhiều Trang, Không ai dại đi để dữ liệu dưới hoặc phải hoặc trái hoặc trên vùng dữ liệu co giãn, mà không cho nó vào trang khác.

Theo như tôi được xem demo ATool của Tuân về kết quả động (co giãn tức là động đậy rồi) và tự nghiên cứu hàm mảng của Excel 365 như Filter, SortBy, ... thì có sự giống và khác biệt:
- Cả 2 đều có thể co giãn kích thước trên bảng tính
- Khi giãn dòng hoặc cột, hàm mảng của 365 nếu đụng dữ liệu khác bên dưới hoặc bên phải sẽ bị lỗi Spill, ATool thì không. Đây là niềm tự hào của anh Tuân và đó là chính đáng.

Quay lại ý chính:
Nói về cấu trúc dữ liệu thì các table dữ liệu nên để ở những sheet khác nhau. Trường hợp có ba bốn cái danh mục mà mỗi danh mục có 3 cột quất cho 3, 4 sheet thì cũng phí, nên có thể để trên cùng 1 sheet nhưng cần trải ra theo hàng ngang và cách nhau ít nhất 1 cột trống. Không chơi kiểu trải xuống theo hàng dọc.
Ý chính của chính:
Đã là dữ liệu gốc thì không hề có công thức, chứ đừng nói công thức mảng. Công thức mảng (dù của 365 hay của ATool) chỉ nên xài trên báo cáo, trên mẫu trang in. Báo cáo phức tạp sẽ có thể có nhiều những SubReport là những bảng kết quả khác nhau, bổ sung cho nhau, hoặc mô tả chi tiết cho nhau. Do nhu cầu in báo cáo sẽ có tình trạng các bảng kết qủa trên cần trải dọc xuống chứ không theo hàng ngang.
Về điều này thì đúng là sẽ cần bỏ qua lỗi Spill của 365, và khi tôi xem demo thì anh Tuân đã làm được.

 

[HeSanbi]

@ptm0412
Bác cho em xem demo ấy ra sao?

 

[Ngô Hải Đăng]

Thử cho vài đối số của hàm vào xem sao ... xong tính tiếp
VD: [A1] = Hàm(value1, value2, value3)

Bác xem file giùm em xem tính tiếp được không?

 

[Kiều Mạnh]

Lưu lại 1 tấm hình sau này nhắc lại cho Vui he

 

[HeSanbi]

Lưu lại 1 tấm hình sau này nhắc lại cho Vui he

View attachment 248009

Để minh chứng cho điều gì?

 

[Kiều Mạnh]

Bác xem file giùm em xem tính tiếp được không?

1/ Viết lại thành 1 cái Hàm chung nhất có thể gán bất cứ cái Array nào đã tính toán xong vào đó xong gán lên Sheet là Xong
2/ bỏ hết code trong ThisWorkbook đi ... thừa ko cần thiết

VD: cái Hàm Sau
[A1] =TaoArr(10,10) thì nó ra 10 dòng và 10 cột

Function TaoArr(dong As Long, cot As Long) As Variant
    Rem Cu Phap: =TaoArr(10,10) Tao ra 10 dong x 10 cot
    Dim i As Long, J As Long
    ReDim Arr(1 To dong, 1 To cot)
    For i = 1 To dong
        For J = 1 To cot
            Arr(i, J) = i & "_" & J
        Next
    Next
    TaoArr = ResizeArray(Arr)
End Function

Khó nhất cái Hàm ResizeArray này .... chỉ cần viết 1 cái đó thôi còn bỏ hết đi cho sạch

 

[Kiều Mạnh]

Để minh chứng cho điều gì?

thì ngoài cái ý kiếm của bài 30 đấy ... sau này mò ra mới thấy Mạnh nói đúng thế thôi
Hoặc có thể có cách viết khác mà ko cần tới nó ???!!!!

 

[HeSanbi]

thì ngoài cái ý kiếm của bài 30 đấy ... sau này mò ra mới thấy Mạnh nói đúng thế thôi

Cái bài 30 đấy, Tôi hết hồn là vì, chỉ có Evaluate thôi mà bác làm thế giới tưởng cái gì ghê gớm, mà bác giấu bấy lâu.

Còn Evaluate chạy hai lần khi gọi thì tôi đang đố bác vì sao lại như vậy.
Nó còn chức năng ghê gớm trong đấy.

Nếu bác không tin thì các hàm viết vào ô Excel đều chạy hai lần. Bác thử tìm hiểu xem có gì "hot"!

Tìm hiểu xong rồi ứng dụng luôn cho những gì bác chưa biết.

 

[Kiều Mạnh]

Cái bài 30 đấy, Tôi hết hồn là vì, chỉ có Evaluate thôi mà bác làm thế giới tưởng cái gì ghê gớm, mà bác giấu bấy lâu.

Còn Evaluate chạy hai lần khi gọi thì tôi đang đố bác vì sao lại như vậy.
Nó còn chức năng ghê gớm trong đấy.

Nếu bác không tin thì các hàm viết vào ô Excel đều chạy hai lần. Bác thử tìm hiểu xem có gì "hot"!

Tìm hiểu xong rồi ứng dụng luôn cho những gì bác chưa biết.

có thể là vầy
cái thứ đó ứng dụng nhiều thứ mà mạnh làm theo hướng này còn bạn làm theo hướng kia
và ta chưa hiểu nhau cái gì đó trong cách trình bày và abcdxyz thế thôi .... nên ====> có sự lệch pha + suy nghĩ thế thôi

Thôi ko bàn lại cái nữa he ... vậy thôi và để đấy nó mới vui