入黎免費幫你砌excel表 / 教你formula (6)

我有追嗰個post
冇人答你點解out咗

lm學野

預左唔會有人答
講句soundbite出黎拋下浪頭係人都得
你問深佢一層，問下佢「點解」，佢其實唔會答到你
好似以前有人笑我唔識sumproduct
我問番佢sumproduct同sum有咩分別
咪又係潛左..

用緊新版excel，分唔到兩者有咩分別
我記得舊版唔可以用sum代替sumproduct
但新版多數都得
其實有咩分別？

想問下如果我有堆data係咁:

我記得舊版唔可以用sum代替sumproduct
>去到07咁舊呢兩個function都可以做到相同既野,再舊就真係唔知
第1,2,3基本上做緊同一樣野
分別係:
sumproduct如果入面做d簡單既array operation
係可以唔用ctrl shift enter
sum 就一般都要用ctrl shift enter
所以有d人好鍾意用sumproduct黎「慳」左個ctrl shift enter
同埋sumproduct係計得快少少少少少
但真係好少
如果performance係critical，盡量用sumifs之類先係路

而第4個就完全係另一樣野,所有數都會加埋左一齊

簡單講用sumproduct主要都係為左慳個ctrl shift enter
而唔係有啲咩特別野得佢先計到
sumproduct計到既野，用sum計都係一樣

但偏偏有d人就當sumproduct係寶咁
覺得自己識多左個sumproduct就勁左一百倍
但其實只係一知半解
唔知原來自己做緊既野根本就係sum

好d既寫法
由a2開始loop到落最底
如果A2<>A1
就換file (save上一個file + 根據product code開個新file)
再抄果行過去
如果A2=A1 就正常咁抄過去,唔使換file

差d既寫法,唔理佢使唔使換file
由a2開始loop到落最底
直接根據個product code開個file出黎
抄果行過去, save, close

會唔會有code？
因為vba唔係話特別識，所以唔係太明，只係識睇小小
依家都係上網睇住人地d code左抄右抄出黎
同埋d data每日都會變，唔係fix，每日都有變動，要不斷copy and paste，所以想用vba fix左佢

而家寫唔到俾你
夜啲都冇人代答既話你tg啲file俾我

諗住收工前整到
但唔夠時間

用咗d時間搵個unique array點整最好

原來係咁
其實我指新版連ctrl shift enter都自動幫你做埋

從來唔用ctrl shift enter
edit好煩

真係array起上黎唔用都唔得
除非以後改到乜野array formula都唔使再用ctrl shift enter

我以為搵unique value再做幾次filter會容易啲

最煩係下手懶醒trace下formula撳enter
搞到冇咗array formula

咁咪抵撚佢死

我當年就係抵撚死嗰個

我真係恭喜你呀

佢啲data順序其實唔使理佢unique
拆到個code唔同左就換另一個file就得

Sub saveExcels()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim header As Range
    Dim table As Range
    
    Dim maxRow As Integer
    Dim maxCol As Integer
    
    Dim code As Range
    Dim uniqueCode() As Variant
    Dim codeLen As Integer
    
    Dim i As Integer
    Dim j As Integer
    Dim arr() As Range
    Dim count() As Integer
    
    Dim newWB As Workbook
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    Set header = ws.Range("_header")
    Set table = ws.Range("_table")
    
    maxRow = table(999999, 1).End(xlUp).Row - table(1)(1).Row + 1
    maxCol = table(1, 9999).End(xlToLeft).Column - table(1)(1).Column + 1
    
    Set code = table.Columns(1).Range(Cells(1, 1), Cells(maxRow, 1))
    uniqueCode = getUnique(code)
    codeLen = UBound(uniqueCode)
    
    ReDim arr(codeLen, maxRow)
    ReDim count(codeLen)
    
    For i = 0 To codeLen
        count(i) = 0
    Next i
    
    For i = 1 To maxRow
        j = Application.Match(code(i), uniqueCode, 0) - 1
        Set arr(j, count(j)) = table.Rows(i)
        count(j) = count(j) + 1
    Next i
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    For i = 0 To codeLen
        Set newWB = Workbooks.Add
        With newWB.Sheets(1)
            .Rows(1).Value = header.Value
            For j = 0 To count(i) - 1
                .Rows(j + 2).Value = arr(i, j).Value
            Next j
        End With
        newWB.SaveAs wb.Path + "\" + CStr(arr(i, 1).Cells(1, 1).Value) + ".xlsx"
        newWB.Close
    Next i
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Function getUnique(inputRange As Range) As Variant()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In inputRange
        dict(cell.Value) = 1
    Next cell
    
    getUnique = dict.Keys()
End Function

named range:


簡單複雜化solution嚟啦, 可以應用係唔同情況
分開兩part做係唔想開住好多excel / 又閂又開
目的係開一個Excel就放哂d data然後閂左佢, 跟手做下一個
同埋假設code係唔跟順序

Thank you幾位巴打
我自己後尾都研究到點打可以做到個效果
晏小小send上黎比你哋睇下，比下feedback我
(唔係太識打，send左上黎之後唔好笑我)

hi 巴打
我有個google sheet
每個tab 會有人名 同註冊日期 (每兩星期就會有新tab)
如果我想有個sheet show 番全部出現過既人同註冊日期 有冇咩計

理論上可以indirect做
但長遠用appscript可能好d

我係咁

都想睇下邊到有得改/打得唔好既地方

樓主寫code 應該都有一手