=СУММПРОИЗВ((D9:D16="Долг")*(E9:E16>0)*(D10:D17="Оплата")*E10:E17)

Private Sub Bnt_НачальнаяДата_Click()            'выбираем начальную дату периода
    On Error Resume Next
    calendar.Show
    If calendar.Value > 0 Then Me.txt_НачальнаяДата.Value = Format(calendar.Value, "dd.mm.yyyy")
End Sub

Private Sub Btn_КонечнаяДата_Click()             'выбираем конечную дату периода по клику
    On Error Resume Next
    calendar.Show
    If calendar.Value > 0 Then Me.txt_КонечнаяДата.Value = Format(calendar.Value, "dd.mm.yyyy")
End Sub

Private Sub txt_КонечнаяДата_Change()
    DateFinish = CDate(Me.txt_КонечнаяДата)
End Sub

Private Sub txt_НачальнаяДата_Change()
    DateStart = CDate(Me.txt_НачальнаяДата)
End Sub

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"МОЩНОСТЬ,#(lf)Вт", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"МОЩНОСТЬ,#(lf)Вт"}, "Атрибут", "Значение")
in
    #"Unpivoted Other Columns"

Format(application.edate(Now, -1 ) - 1, "dd MMMM yyyy")

let
    Source = Table.NestedJoin(
                 Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
                 "Оператор",
                 Excel.CurrentWorkbook(){[Name="Апрель2020"]}[Content],
                 "Оператор",
                 "a",
                 3
             ),
    Expand = Table.ExpandTableColumn(Source, "a", {"Оператор", "Время"}, {"a.Оператор", "a.Время"}),
    Merge  = Table.FromRecords(Table.CombineColumns(Expand,{"Оператор", "Время", "a.Оператор", "a.Время"},(a)=>
                 [
                     Оператор=List.RemoveNulls({a{0},a{2}}){0},
                     Время=List.Sum({a{1},a{3}})
                 ],"a")[a]),
    Group  = Table.Group(Merge, {"Оператор"}, {{"Время", each List.Sum([Время]), type number}})
in
    Group

=ArrayFormula(rank(abs(B12:D12-E12),abs(B12:D12-E12),2))

=ArrayFormula(rank(transpose(mmult(transpose(rank(abs(B12:D14-E12:E14),abs(B12:D14-E12:E14),2)),{1;1;1})),transpose(mmult(transpose(rank(abs(B12:D14-E12:E14),abs(B12:D14-E12:E14),2)),{1;1;1})),2))

=index(sort(transpose({abs(B12:D12-E12);B$10:D$10})),1,2)

=index(Sort({mmult(abs(TRANSPOSE(B12:D14-E12:E14)),{1;1;1}),transpose(B10:D10)}),1,2)

=ArrayFormula(transpose(Index(Sort({Index(Sort({mmult(transpose(rank(abs(B12:D14-E12:E14),abs(B12:D14-E12:E14),2)),{1;1;1}),{1;2;3}}),0,2),{1;2;3}}),0,2)))

=Transpose(Query(ArrayFormula(--RegexReplace( ImportXML("https://omsk.nuipogoda.ru/погода-на-завтра","//table[@class='weather']//span[@class='ht']"),"[+°]","")),"select  date '"&text(today()+1,"yyyy-MM-dd")&"',min(Col1),max(Col1),avg(Col1)  group by date '"&text(today()+1,"yyyy-MM-dd")&"' label Min(Col1) 'Минимальная температура', Avg(Col1) 'Средняя температура',Max(Col1) 'Максимальная температура', date '"&text(today()+1,"yyyy-MM-dd")&"' 'Дата'  format date '"&text(today()+1,"yyyy-MM-dd")&"' 'dd.MM.YYYY'"))

=ЧИСТВНДОХ(ЕСЛИ(СТРОКА(A1:ИНДЕКС(A:A;B3+1))=1;B4;-B8);ДАТАМЕС(СЕГОДНЯ();СТРОКА(A1:ИНДЕКС(A:A;B3+1))-1))

=Transpose(Query(ARRAYFORMULA(iferror(--substitute(REGEXREPLACE(IMPORTHTML("http://www.pogodaiklimat.ru/forecast/28698_7.htm","table",0)&"","(\d+)(\.)(\d+)\x0a.*$","$3/$1/"&year(now())),"+",""),)),"select Col1, Min(Col4),Max(Col4),Avg(Col4) group by Col1 label Col1 'Дата',Min(Col4) 'Минимальная температура', Avg(Col4) 'Средняя температура',Max(Col4) 'Максимальная температура' format Col1'dd.MM.YYYY'"))

=ArrayFormula(Transpose(Query(ArrayFormula({Arrayformula(CEILING((Row(A1:Index(A:A,Rows(IMPORTHTML("https://www.yr.no/place/Russia/Omsk/Omsk/long.html","table",3 ))))-1)/4,1)),iferror(--REGEXREPLACE(IMPORTHTML("https://www.yr.no/place/Russia/Omsk/Omsk/long.html","table",3)&"","(\d{2})(\/)(\d{2})\/(\d{4})|[^\d\/:.]|.*\–","$3$2$1$2$4"),)}),"Select max(Col2),Min(Col5),Max(Col5),Avg(Col5) where Col1>0 group by Col1  label Max(Col2) 'Дата',Min(Col5) 'Минимальная температура', Avg(Col5) 'Средняя температура',Max(Col5) 'Максимальная температура' format Max(Col2) 'dd.MM.YYYY'")))

=Indirect("meteocenter.asia!B4")

=ArrayFormula({{"Дата",TODAY()};{{"Макс";"Мин";"Средняя"},ArrayFormula(IMPORTXML("http://www.meteocenter.asia/?m=e&p=28698","//table[@class='tab_z']//td[contains(text(),'"&Textjoin(" температура') or contains(text(),'",0,"Мин.","Макс.","Среднесуточная")&" температура')]/following-sibling::td[1]"))}})

=Transpose(Query(Arrayformula(iferror(--REGEXREPLACE(IMPORTHTML("http://www.pogodaiklimat.ru/forecast/28698_7.htm","table",0)&"","(?:(\d+)*\x0a.*)|\+","$1"),)),"select Col1, Min(Col4),Avg(Col4),Max(Col4) group by Col1 label Col1 'Дата',Min(Col4) 'Минимальная температура', Avg(Col4) 'Средняя температура',Max(Col4) 'Максимальная температура'"))

=ArrayFormula(Transpose(Query(ArrayFormula({Arrayformula(CEILING(Row(A1:Index(A:A,Rows(IMPORTHTML("https://www.yr.no/place/Russia/Omsk/Omsk/long.html","table",3 ))))/4,1)),iferror(--REGEXREPLACE(IMPORTHTML("https://www.yr.no/place/Russia/Omsk/Omsk/long.html","table",3)&"","\/[^\d]|.*\–|[^\d\/:.]",""),)}),"Select max(Col2),Min(Col5),Avg(Col5),Max(Col5) group by Col1 label Max(Col2) 'Дата',Min(Col5) 'Минимальная температура', Avg(Col5) 'Средняя температура',Max(Col5) 'Максимальная температура' format Max(Col2) 'dd.MM.YYYY'")))

SELECT *
FROM
    (
        SELECT
            t1.a            ,
            COUNT(t1.b) AS b,
            t2.d
        FROM
            `Лист1$` t1
            INNER JOIN
                `Лист2$` t2
            ON
                t1.a,
                t2.c
        GROUP BY
            t1.a,
            t2.d
    )
ORDER BY
    a,
    b

Public Sub InsPict()
    Dim arr, fldPath$, art$, fName$, i&, r0, lrow&, oDic As Object, IShape As Shape, Zm
    Dim v As Variant
    Set oDic = CreateObject("Scripting.Dictionary")
    r0 = 4
    lrow = Cells(Rows.Count, 3).End(xlUp).Row
    arr = Cells(r0, 3).Resize(lrow - r0 + 1).Value
    For i = 1 To UBound(arr)
        v = oDic(arr(i, 1))
        If IsEmpty(v) Then
            oDic(arr(i, 1)) = Array(i + r0 - 1)
        Else
            ReDim Preserve v(UBound(v) + 1)
            v(UBound(v)) = i + r0 - 1
            oDic(arr(i, 1)) = v
        End If
    Next i
    For Each IShape In ActiveSheet.Shapes
        If IShape.Type <> 8 Then IShape.Delete
    Next
    fldPath = ThisWorkbook.Path & "\images\"    'путь к папке с изображениями
    Application.ScreenUpdating = False
    fName = Dir(fldPath & "*.jpg")
    Do While fName <> ""
        art = Split(fName, ".")(0)
        If oDic.Exists(art) Then
            For Each v In oDic(art)
                With Cells(v, 2)
                    Set IShape = ActiveSheet.Shapes.AddPicture(fldPath & fName, False, True, .Left + 1, .Top + 1, -1, -1)
                    Zm = WorksheetFunction.Min(.Width / IShape.Width, .Height / IShape.Height)
                    IShape.Height = IShape.Height * Zm - 2
                End With
            Next
        End If
        fName = Dir
    Loop
    Application.ScreenUpdating = True
End Sub