Sub dd()
    Dim cell(3) As Range, sel(1) As Object, sh As Worksheet, shts As Sheets, I, r As Range, l, t
    Application.EnableEvents = 0
    With ThisWorkbook.Windows(1)
        Set shts = .SelectedSheets: Set sh = ActiveSheet: Set cell(0) = .VisibleRange(1, 1)
        Set sel(0) = Selection: Set cell(1) = ActiveCell
        Set r = [Лист2!A4]: r.Parent.Select
        Set cell(2) = .VisibleRange(1, 1): Set cell(3) = ActiveCell
        Set sel(1) = Selection
        Application.Goto r, 1
        For I = 1 To .Panes.Count
            If Not Intersect(r, .Panes(I).VisibleRange) Is Nothing Then
                With .Panes(I)
                    Dim pic
                    l = .PointsToScreenPixelsX(r.Left) + 1
                    t = .PointsToScreenPixelsY(r.Top) + 1
                    AppActivate (Application.Caption)
                    DoEvents
                    Set pic = ActiveWindow.RangeFromPoint(l, t)
                    Debug.Print pic.Name
                    Stop
                End With
            End If
        Next
        Application.Goto cell(2): sel(1).Select: cell(3).Activate
        shts.Select: sh.Activate: Application.Goto cell(0), 1: sel(0).Select: cell(1).Activate
        Erase cell, sel: Set sh = Nothing: Set shts = Nothing: Set r = Nothing
    End With
    Application.EnableEvents = 1
End Sub

=ВПР(АГРЕГАТ(14;6;--ПСТР(C3;ДЛСТР(C3)-СТРОКА($1:$99);4);1);$S$2:$T$21;2)

With wb.Worksheets(1)
        With .Range("a" & .Rows.Count).End(xlUp)
            Set cell = IIf(IsEmpty(.Value), .Cells, .Offset(1))
        End With
    End With

Sub d()
    Dim r As Range
    With [index(2!A:A,rows(A:A))].End(xlUp)
        Set r = IIf(.Row > 4, .Offset(1), [2!A4])
    End With
    [1!A:A].Replace "s", "=zz1", 1
    On Error Resume Next
    With [1!ZZ1].Dependents
        .Value = "s"
        .EntireRow.Copy r
        .EntireRow.Delete
    End With
End Sub

=ЛЕВБ(B3;ДЛСТР(B3)-ДЛСТР(СЖПРОБЕЛЫ(0&ПРАВБ(ПОДСТАВИТЬ(B3;",";ПОВТОР(" ";999));999))))

=ЛЕВБ(B3;ПРОСМОТР(;-1/(ПСТР(B3;СТРОКА(1:99);1)=",");СТРОКА(1:99))-1)

=ПРОСМОТР(2;МУМНОЖ(Ч(-ТЕКСТ(ПСТР(L$2;2;99)-ПСТР($B$3:$F$6;2;99);"0;999;0")=МАКС(-ТЕКСТ(ПСТР(L$2;2;99)-ПСТР($B$3:$F$6;2;99);"0;999;0")));ТРАНСП(СТОЛБЕЦ($B$3:$F$6)))^0;ИНДЕКС($B$3:$F$6;;СТРОКА(L1)))

0,341606

=(Выпечка!D53+Выпечка!D59*Выпечка!D200)*'Выпечка Расчёт'!H3

=СЧЁТЕСЛИ(СМЕЩ(ИНДЕКС(A:A;ПОИСКПОЗ(9^9;A:A));;;-10);0)

=СЧЁТ(1/(B2=ОТБР(A2:A7)))

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