SetEnvironmentVariable "test", Empty 'удаление переменной

Success := SetEnvironmentVariable(PChar(VarName), nil)

Table.Pivot(Таблица, List.Distinct([Custom.Value.results.specGroups.specGuruModelcard.name]),  "Custom.Value.results.specGroups.specGuruModelcard.name",  "Custom.Value.results.specGroups.specGuruModelcard.value",  each if List.Count(_)>1 then _ else _{0})

let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
               name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.FromRecords(Json.Document([Content])[productModsSearch][search][results]),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,col[results]),{{"vendor",each Table.FromRecords({Record.SelectFields(_,col[vendor])})},
            {"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),{"specGuruModelcard",Table.FromRecords})},
            {"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3

=ВПР(C6;ЕСЛИ({1;0};данные!$C$5:$C$10;данные!$A$5:$A$10);2;)

=СУММ((Лист2!A2:A19=ЕСЛИ((ДЛСТР(Z4:DZ4)>0)*(ОСТАТ(СТОЛБЕЦ(F4:DF4);6)=0);W4:DW4))*Лист2!E2:E19)

=ВПР(C6;данные!$C$5:$D$10;2;)

Table.FromRecords(Json.Document([Content])[productModsSearch][search][results])

Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_})))

Table.SelectColumns(_,col[results])

Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]}))

let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3

Option Explicit
Private Declare Function GetClassName& Lib "user32" Alias "GetClassNameA" (ByVal hwnd&, ByVal lpClassName$, ByVal nMaxCount&)
Private Declare Function AccessibleObjectFromWindow& Lib "oleacc" (ByVal hwnd&, ByVal dwId&, riid As GUID, xlWB As Object)
Private Declare Function GetDesktopWindow& Lib "user32" ()
Private Declare Function GetWindow& Lib "user32" (ByVal hwnd&, ByVal wCmd&)
Private Const GW_HWNDNEXT = 2
Private Const GW_CHILD = 5
Private Const OBJID_NATIVEOM = &HFFFFFFF0
Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type
Private IDispatch As GUID, oWnd As Window
Public Sub AddReference()
    Dim i&
    With IDispatch
        .lData1 = &H20400: .iData2 = &H0: .iData3 = &H0
        .aBData4(0) = &HC0: .aBData4(1) = &H0: .aBData4(2) = &H0
        .aBData4(3) = &H0: .aBData4(4) = &H0: .aBData4(5) = &H0
        .aBData4(6) = &H0: .aBData4(7) = &H46
    End With
    Referece2AllWorkbooks 0, "EXCEL7", 0, 0, 0, Application.UserLibraryPath & "MyFunction.xla"
    Set oWnd = Nothing
End Sub
Private Function Referece2AllWorkbooks&(hWndStart&, ClassName$, level&, lHolder&, lCnt&, sFile$)
    Dim hwnd&, sWindowTitle$, sClassName$, wb as Workbook
    If level = 0 Then
        If hWndStart = 0 Then
            hWndStart = GetDesktopWindow()
        End If
    End If
    
    'Increase recursion counter
    '--------------------------
    level = level + 1
    
    'Get first child window
    '----------------------
    hwnd = GetWindow(hWndStart, GW_CHILD)
    
    Do While hwnd > 0
        'Search children by recursion
        '----------------------------
        lHolder = Referece2AllWorkbooks(hwnd, ClassName, level, lHolder, lCnt, sFile)
        
        'get the class name
        '------------------
        sClassName = Space$(255)
        r = GetClassName(hwnd, sClassName, 255)
        sClassName = Left$(sClassName, r)
        
        If sClassName Like ClassName & "*" Or sClassName = ClassName Then
            Referece2AllWorkbooks = hwnd
            lHolder = hwnd
            AccessibleObjectFromWindow hwnd, OBJID_NATIVEOM, IDispatch, oWnd
            If Not oWnd Is Nothing Then
                If oWnd.Visible Then
                    lCnt = lCnt + 1
                    On Error Resume Next
                    For Each wb In oWnd.Application.Workbooks
                        wb.VBProject.References.AddFromFile sFile
                    Next
                End If
            End If
        End If
        
        'Get next child window
        '---------------------
        hwnd = GetWindow(hwnd, GW_HWNDNEXT)
    Loop
    Referece2AllWorkbooks = lHolder
    
End Function

let
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = (tbl as table, column as text, columns as list) as table => Table.ExpandTableColumn(tbl, column, columns, List.Transform(columns,each column & "." & _)),
    fn1 = each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
    fn2 = {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})},
    step1 = (Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn1)),fn2)),
    step2  = fn(fn(fn(fn(step1, "results", col[results]),"results.vendor", col[vendor]),"results.specGroups", {"specGuruModelcard"}),"results.offers", {"count"}),
    step3 = fn(fn(step2, "results.specGroups.specGuruModelcard", {"name", "value"}),"results.titles", {"raw"}),
    step4 = Table.ReplaceValue(step3,"",null,Replacer.ReplaceValue,{col[value]}),
    step5 = Table.Pivot(step4,List.Distinct(Table.Column(step4,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step5

=ПРОСМОТР(ТРАНСП(RndUnicArr(СУММ($E:$E)))-1;МУМНОЖ((СТРОКА($E$1:$E$54)>=ТРАНСП(СТРОКА($E$1:$E$54)))*Ч(ТРАНСП($E$1:$E$54));СТРОКА($E$1:$E$54)^0);$A$2:$A$54)

Sub d()
    Dim start&, count&
    start = 3: count = 5
    Dim rng As Range
    With ActiveDocument.Shapes(1).TextFrame.TextRange
        Set rng = .Characters(start)
        rng.End = .Characters(start + count).End
        rng.Select
    End With
End Sub

function RndUnicArr(cnt) {
    return Array.apply(null, Array(cnt))
        .map(Number.call, Number)
        .map(function(n){return [Math.random(),n]})
        .sort().map(function(n){return n[1]})
}

=ArrayFormula(ПРОСМОТР(RndUnicArr(СУММ($E:$E));МУМНОЖ((СТРОКА($E$1:$E$54)>=ТРАНСП(СТРОКА($E$1:$E$54)))*Ч(ТРАНСП($E$1:$E$54));СТРОКА($E$1:$E$54)^0);A2:A54))

=СЧЁТЕСЛИ(A$1:A$10;B1&"*")