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

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

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

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

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

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

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

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

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

0,341606

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

=ПРОСМОТР(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)))

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

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

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

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