=НАИМЕНЬШИЙ(ЕСЛИ(Лист2!$A$1:A99=Лист1!$A$2;СТРОКА(Лист2!$A$1:A99));СТРОКА(Лист1!G1))

=ИНДЕКС(ВЫБОР(ОКРВВЕРХ(СТОЛБЕЦ()/3;1);Лист2!$A:$A;Лист2!$B:$B);$G2+(ОСТАТ(СТОЛБЕЦ();3)=0))

Private WithEvents qt As QueryTable
Private Sub ss()
    Set qt = [Консолидация].ListObject.QueryTable
    qt.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Mode=Read;Extended Properties=text;Data Source=" & Me.path
End Sub
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    ss
End Sub
Private Sub Workbook_Open()
    ss
End Sub
Private Sub qt_BeforeRefresh(Cancel As Boolean)
    Dim Command$()
    Dim f$, s$, v&
    f = Me.path:
    s = Dir$(f & "\*.xls*")
    Do
        If Not s Like "~$*" And s <> Me.Name Then
            ReDim Preserve Command(v):
            Command$(v) = Application.Text$(s, _
                """select *,'""@""' From [Лист1$] IN '" & _
                f & "\""@""' [excel 12.0 xml;HDR=No]""")
                v = v + 1
        End If
        s = Dir$()
    Loop While s <> ""
    qt.CommandText = Join(Command, " union all ")
    DoEvents
End Sub

Private Sub CommandButton1_Click()
      Dim path$, DbPath$, fieldnames$, values$, connStr$, sql$
10  On Error GoTo CommandButton1_Click_Error
    
20    path = ThisWorkbook.path: DbPath = path & "\Table.accdb"
30    fieldnames = "[Дата обработки Заявки], [Название организации], [Кол-во техники, шт], [СУММА СЧЕТА], [Вывезли?]"
40    values = Join(Array(Format(Me.[B3], "\#yyyy-MM-dd#"), "'" & [B2] & "'", [B4], [B5], [B6] = "Да"), ",")
50    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Mode=Read;Extended Properties=text;Data Source=" & path
60    sql = "insert into TOV (" & fieldnames & ") IN '" & DbPath & "' values (" & values & ")"
70    CreateObject("adodb.recordset").Open sql, connStr
        Debug.Print Application.VBE.ActiveCodePane.CodeModule.Name
80  On Error GoTo 0
90    Exit Sub
    
CommandButton1_Click_Error:
      With Application.VBE.ActiveCodePane
          MsgBox "Ошибка " & Err.Number & " (" & Err.Description & _
              ") в процедуре " & .CodeModule.ProcOfLine(.TopLine, 0) & _
              " модуля " & .CodeModule.Name & " на строке " & Erl
      End With
End Sub

=СЧЁТ(1/ЧАСТОТА(ЕСЛИ(($J14:$AC14=AG14)*$J16:$AC300;$H16:$H300);$H16:$H300))

=ВПР(I2;Если({1;0};R3:R20;Q3:Q20);2;)

Sub Latkir()
    If Not Selection.Information(wdWithInTable) Then Exit Sub
    Select Case Selection.Rows(1).index
        Case 1 To 3, 8 To 11
            ActivateKeyboardLayout kb_lay_ru, 0
        Case 4 To 7
            ActivateKeyboardLayout kb_lay_en, 0
    End Select
End Sub

=ТЕКСТ('расчет еу'!G5;"""Недостача составляет ""0_ ""долла"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ('расчет еу'!G5-11;100);9);10);"[<1]р;[>4]ров;ра")&""",_ 00_ ")&"цен"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(100*ОКРУГЛ(ОСТАТ('расчет еу'!G5;1);2)-11;100);9);10);"[<1]т;[>4]тов;та")

CreateObject("Wscript.Shell").run "cmd /c taskkill /f /im outlook.exe & start outlook.exe", 0, false

=--ЗАМЕНИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(D8;ПОИСК(",";D8&",";ПОИСК("texturef";D8))-1);"texturef";ПОВТОР(" ";99));99));"-";ПОВТОР(" ";99));99;99))&0;2;;",")

Private Sub CommandButton1_Click()
    If TextBox7 <> "" Then
        Select Case True
            Case TextBox1 & ComboBox3 = ""
                MsgBox "Заполните № заявки, либо выберите особую услугу"
            Case ComboBox3 & TextBox10 = "монтаж", ComboBox3 & TextBox10 = "проектирование"
                MsgBox "Заполните участок"
            Case TextBox1 <> "" And ComboBox3 & TextBox2 & ComboBox5 & ComboBox1 & ComboBox4 = "", _
                 ComboBox3 & TextBox2 & ComboBox5 & ComboBox1 & ComboBox4 & TextBox1 = "конс."
                MsgBox "Заполните все поля"
            Case Else
                dodav
        End Select
    Else
        MsgBox "Не указана дата"
    End If
End Sub

=СЧЁТЕСЛИ('2017'!$D$1:$D$27;A2)

=СЧЁТЕСЛИ('2017'!$G$1:$G$27;A2)

=Ч(D1)+B2-C2

Sub dd()
    Dim a As Variant
    With [Таблица1].ListObject
        .ListRows.Add 1
        With .ListColumns("Столбец2").DataBodyRange
            .Cells(1).Clear
            .Formula = .Formula
        End With
        .ListRows(1).Delete
    End With
End Sub

Sub d()
    Dim r1 As Range
    Application.ScreenUpdating = 0: Application.EnableEvents = 0
    With Selection
        .UnMerge
        On Error GoTo er
        Set r1 = .RowDifferences(.Find(Empty))
        r1.EntireRow.Hidden = 1
        .SpecialCells(12).EntireRow.Delete
        .EntireRow.Hidden = 0
        r1.EntireColumn.Hidden = 1
        .SpecialCells(12).EntireColumn.Delete
        .EntireColumn.Hidden = 0
    End With
er: Application.ScreenUpdating = 1: Application.EnableEvents = 1
End Sub

=СУММ(ПРОСМОТР(("1."&A1)+СТРОКА($1:$366)-1;--(A$2:L$2&A1);A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&A1;0)))*МУМНОЖ(-(("1."&A1)+СТРОКА($1:$366)-{0;1}<=B6:C6);{1:-1}))

=СУММЕСЛИМН(B7:B46;C7:C46;">="&B2-B1*30;C7:C46;"<="&B2)