=СУММ(ЕСЛИ(СТРОКА(C2:C1000)<=85; 758+100; 758))+СУММ(ЕСЛИ(СТРОКА(C2:C1000)>85; 758; 0))

function onOpen() {
 var ui = SpreadsheetApp.getUi();
 ui.createMenu('Мой склад')
    .addItem('Запустить скрипт', 'runScript')
    .addToUi();
}

function runScript() {
 clearCells();
 var dates = promptDates(); // Запрашиваем дату и время перед началом выполнения скрипта
 getProductInfo(dates.date, dates.time); // Передаем выбранную дату и время в функцию getProductInfo
}

function getProductInfo(date, time) {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var lastRow = sheet.getLastRow();
 var ui = SpreadsheetApp.getUi();
 var outputColumnQuantity = 13;
 var outputColumnPrice = 19;
 var calcColumnR = 18;
 var calcColumnQ = 17;
 var outputColumnIP = 22;
 var startRow = 4;

 var start = new Date();
 var ip = getIPAddress();

 var startMessage = 'Выполнение скрипта начато. Скрипт начал выполняться с IP-адреса: ' + ip + '. Это может занять некоторое время.';
 ui.alert('Выполнение скрипта', startMessage, ui.ButtonSet.OK);

 var deletedRowsData = []; // Массив для хранения информации об удаленных строках

 for (var i = startRow; i <= lastRow; i++) {
    var searchTerm = sheet.getRange(i, 5).getValue();

    if (searchTerm !== "" && searchTerm !== null) {
      var token = 'ТУТ_ЕСТЬ_ТОКЕН';
      var headers = { 'Authorization': 'Bearer ' + token };
      var baseUrl = 'https://online.moysklad.ru/api/remap/1.2/entity/assortment';
      var url = baseUrl + '?filter=code=' + searchTerm + '&expand=stocks,salePrices';
      var response = UrlFetchApp.fetch(url, { headers: headers });
      var jsonResponse = JSON.parse(response.getContentText());

      var stock = '';
      var salePriceFree = '';
      var ip = response.getHeaders()['X-Forwarded-For'] || '';

      if (jsonResponse.rows && jsonResponse.rows.length > 0) {
        var stockValue = jsonResponse.rows[0].stock;

        if (stockValue !== null && stockValue !== undefined) {
          stock = stockValue;

          if (stockValue === 0 || stockValue <= 20) {
            deletedRowsData.push({
              code: searchTerm,
              name: jsonResponse.rows[0].name,
              quantity: stockValue,
              row: i
            });
            sheet.deleteRow(i); // Удаляем строку, если остаток равен 0 или меньше 20
            lastRow--; // Уменьшаем переменную lastRow, так как количество строк в таблице уменьшилось
            i--; // Уменьшаем переменную i, чтобы корректно перейти к следующей строке после удаления
            continue; // Пропускаем остальную часть цикла после удаления строки
          }
        } else {
          stock = '';
        }

        var salePrices = jsonResponse.rows[0].salePrices || [];
        for (var j = 0; j < salePrices.length; j++) {
          if (salePrices[j].priceType && salePrices[j].priceType.name === "Свободная") {
            salePriceFree = (salePrices[j].value / 100).toFixed(2).replace('.', ',');
            break;
          }
        }
      }

      sheet.getRange(i, outputColumnQuantity).setValue(stock);
      sheet.getRange(i, outputColumnPrice).setValue(salePriceFree);

      var price = parseFloat(salePriceFree.replace(',', '.'));
      var priceR = (price * 0.97).toFixed(2).replace('.', ',');
      var priceQ = (price * 0.93).toFixed(2).replace('.', ',');

      sheet.getRange(i, calcColumnR).setValue(priceR);
      sheet.getRange(i, calcColumnQ).setValue(priceQ);
      sheet.getRange(i, outputColumnIP).setValue(ip);

      // Вызываем функцию для выбора даты и времени для каждой строки
      sheet.getRange(i, 11).setValue(date); // Записываем выбранную дату в столбец K
      sheet.getRange(i, 12).setValue(time); // Записываем выбранное время в столбец L
    }
  }

 var rowsToDeleteCount = deletedRowsData.length;
 ui.alert('Удаление строк', 'Будет удалено строк: ' + rowsToDeleteCount, ui.ButtonSet.OK);

 if (rowsToDeleteCount > 0) {
    var csvContent = "Код | Наименование | Остаток | Номер строки\n";
    deletedRowsData.forEach(function (row) {
      csvContent += row.code + " | " + row.name + " | " + row.quantity + " | " + row.row + "\n";
    });

    var fileName = "Удаленные_строки.csv";
    var mimeType = "text/csv";
    var blob = Utilities.newBlob(csvContent, mimeType, fileName);
    var csvData = Utilities.base64Encode(blob.getBytes());
    var fileUrl = "data:" + mimeType + ";base64," + csvData;
    var fileLink = '<a href="' + fileUrl + '" download="' + fileName + '">Скачать файл</a>';
    var dialogTitle = 'Файл CSV создан';
    var htmlOutput = '<script>' +
      'function closeDialog() {' +
      '  google.script.host.close();' +
      '}' +
      '</script>' +
      fileLink +
      '<script>' +
      'document.querySelector("a").addEventListener("click", closeDialog);' +
      '</script>';

    var html = HtmlService.createHtmlOutput(htmlOutput);
    html.setWidth(300);
    html.setHeight(80);
    SpreadsheetApp.getUi().showModelessDialog(html, dialogTitle);
  } else {
    ui.alert('Удаление строк', 'Без остатка и остаток 20 и меньше не найдено строк для удаления.', ui.ButtonSet.OK);
  }

 var end = new Date();
 var executionTime = (end - start) / 1000;
 var endMessage = 'Скрипт завершен. На выполнение скрипта затрачено ' + executionTime + ' секунд.';
 ui.alert('Скрипт завершен', endMessage, ui.ButtonSet.OK);
}

function promptDates() {
 var ui = SpreadsheetApp.getUi();
 var response = ui.prompt('Введите дату (формат: ДД.ММ.ГГГГ)', ui.ButtonSet.OK_CANCEL);
 var dateInput = '';
 var timeInput = '';

 if (response.getSelectedButton() === ui.Button.OK) {
    dateInput = response.getResponseText();

    var timeResponse = ui.prompt('Введите время (формат: ДД.ММ.ГГГГ ЧЧ:ММ:СС)', ui.ButtonSet.OK_CANCEL);

    if (timeResponse.getSelectedButton() === ui.Button.OK) {
      timeInput = timeResponse.getResponseText();
    }
  }

 return {
    date: dateInput,
    time: timeInput
  };
}

function clearCells() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var startRow = 4;
 var endRow = sheet.getLastRow();
 var columnsToClear = [11, 12, 13, 17, 18, 19]; // K, L, M, Q, R, S

 for (var colIndex of columnsToClear) {
    var range = sheet.getRange(startRow, colIndex, endRow - startRow + 1);
    range.clearContent();
  }
}

// Функция для получения IP-адреса пользователя
function getIPAddress() {
 var ipAddress = '';
 try {
    var ipResponse = UrlFetchApp.fetch('https://api64.ipify.org?format=json');
    var json = JSON.parse(ipResponse.getContentText());
    ipAddress = json.ip || '';
  } catch (error) {
    ipAddress = '';
  }
 return ipAddress;
}

If wbReestr Is Nothing Then     Set wbReestr = Workbooks.Open("C:\Users\lifan\OneDrive\Рабочий стол\reestr.xlsm") End If 

reestrName = "reestr" '
    reestrExtension = ".xlsm" '     reestrPath = Dir(ThisWorkbook.Path & "\" & reestrName & reestrExtension)    Do While reestrPath = ""         If reestrExtension = ".xlsm" Then            reestrExtension = ".xlsx"             reestrPath = Dir(ThisWorkbook.Path & "\" & reestrName & reestrExtension)        Else             Exit Do        End If    Loop

Set shSheet1 = ThisWorkbook.Worksheets("Лист1")

Dim headerRow As Range

    For Each targetSheet In wbReestr.Worksheets
        Set headerRow = targetSheet.Rows(1)
        If Not headerRow.Find("Название колонки1") Is Nothing And _
           Not headerRow.Find("Название колонки2") Is Nothing And _
           Not headerRow.Find("Название колонки3") Is Nothing Then
            Set shSheet1 = targetSheet
            Exit For
        End If
    Next targetSheet

Sub dsd()
    Dim selectedColumns As Variant
    Dim col As Variant
    Dim lr As Long
    Dim i As Long
    Dim colNum As Long
    
    On Error Resume Next
    selectedColumns = Application.InputBox("Выберите столбцы для обработки (через запятую без пробелов, например, 5,7)", Type:=2)
    On Error GoTo 0
    
    If VarType(selectedColumns) = vbBoolean Then Exit Sub
    
    selectedColumns = Split(selectedColumns, ",")
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each col In selectedColumns
        colNum = CLng(col)
        lr = Cells(Rows.Count, colNum).End(xlUp).Row
        
        For i = lr To 2 Step -1
            If Cells(i, colNum).Value = Cells(i - 1, colNum).Value And Not IsEmpty(Cells(i, colNum)) Then
                Range(Cells(i - 1, colNum), Cells(i, colNum)).Merge
                With Range(Cells(i - 1, colNum), Cells(i, colNum))
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
            End If
        Next i
    Next col
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Sub dsd()
    Dim colArray As Variant
    Dim col As Variant
    Dim lr As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    colArray = Array(5, 7)
    
    For Each col In colArray
        lr = Cells(Rows.Count, col).End(xlUp).Row
        
        For i = lr To 2 Step -1
            If Cells(i, col).Value = Cells(i - 1, col).Value And Not IsEmpty(Cells(i, col)) Then
                Range(Cells(i - 1, col), Cells(i, col)).Merge
                With Range(Cells(i - 1, col), Cells(i, col))
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
            End If
        Next i
    Next col
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Sub GetExcelInstanceByPID()
    Dim vPID As Variant
    Dim xlApp As Object
    Dim processes As Object
    Dim process As Object
    
    ' Поменяйте на свой PID процесса Excel
    vPID = Shell("excel.exe /x", vbNormalFocus)
    
    Set processes = GetObject("winmgmts://./root/cimv2").ExecQuery("SELECT * FROM Win32_Process WHERE ProcessId = " & vPID)
    
    For Each process In processes
        If process.Name = "EXCEL.EXE" Then
            ' Найден процесс Excel, получаем к нему доступ
            Set xlApp = GetObject("winmgmts://./root/cimv2:Win32_Process.Handle='" & process.Handle & "'").Associators_("Win32_Process")
            
            ' Отображаем информацию о найденном экземпляре Excel
            MsgBox "Найден экземпляр Excel." & vbCrLf & _
                   "PID: " & vPID & vbCrLf & _
                   "Имя процесса: " & process.Name & vbCrLf & _
                   "Информация о процессе: " & process.ExecutablePath
                   
            ' Можно использовать xlApp для управления этим экземпляром
            ' Например: xlApp.Visible = True
            Exit For
        End If
    Next process
    
    If xlApp Is Nothing Then
        MsgBox "Не удалось найти процесс Excel с PID: " & vPID
    End If
End Sub

Sub onTabChange(control As IRibbonControl, ByRef cancelDefault)
    If control.Id = "Название_закладки" Then
        ' Например:
        ' RibbonUI.InvalidateControl "Название_кнопки" ' Обновление определенной кнопки на панели Ribbon
    End If
End Sub

import psutil

def get_excel_process():
    for proc in psutil.process_iter(['pid', 'name']):
        if 'excel' in proc.info['name'].lower():  # Найти процесс Excel
            return proc

# Получение процесса Excel
excel_process = get_excel_process()
if excel_process:
    print(f"Найден процесс Excel с PID {excel_process.info['pid']}")
else:
    print("Процесс Excel не найден")

=ЕСЛИ(СЧЁТЕСЛИ(C5:C4999;">50")>0; АГРЕГАТ(17; 3; D5:D4999; 3); "")

Sub Photo()
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim numPhotos As Long
    Dim id As String
    Dim result As String
    Dim photoUrl As String
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        numPhotos = Cells(i, 2).Value
        
        id = Cells(i, 1).Value
        
        result = ""
        
        For j = 1 To numPhotos
            Dim formattedNumber As String
            formattedNumber = Format(j, "000")
            
            photoUrl = "http://URL/" & id & "/photo_" & formattedNumber & ".jpg"
            
            If result = "" Then
                result = photoUrl
            Else
                result = result & "," & photoUrl
            End If
        Next j
        
        Cells(i, 3).Value = result
    Next i
End Sub

Private Sub DeleteCol(control As IRibbonControl)
    Call MergeAndProcessData
End Sub

'Это готовый вариант 1 части выполнения
Sub MergeAndProcessData()
    Dim selectedFiles As FileDialog
    Dim fileName As Variant
    Dim mergedData() As Variant
    Dim headerRow() As Variant
    Dim totalRows As Long
    Dim currentRow As Long
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim lastRow As Long
    Dim sumQty As Double
    Dim curBlock As String
    Dim curName As String

    Set selectedFiles = Application.FileDialog(msoFileDialogFilePicker)
    selectedFiles.AllowMultiSelect = True

    If selectedFiles.Show = -1 Then
        totalRows = 0
        Dim totalCols As Long
        For Each fileName In selectedFiles.SelectedItems
            Workbooks.Open fileName
            totalRows = totalRows + ActiveSheet.UsedRange.Rows.Count - 1
            totalCols = ActiveSheet.UsedRange.Columns.Count
            ActiveWorkbook.Close SaveChanges:=False
        Next fileName

        ReDim mergedData(1 To totalRows, 1 To totalCols)
        ReDim headerRow(1 To 1, 1 To totalCols)

        currentRow = 1

        For Each fileName In selectedFiles.SelectedItems
            Workbooks.Open fileName
            If currentRow = 1 Then
                For j = 1 To totalCols
                    headerRow(1, j) = ActiveSheet.Cells(1, j).Value
                Next j
            End If
            For i = 2 To ActiveSheet.UsedRange.Rows.Count
                If ActiveSheet.Cells(i, 16).Value <> "Отклонено" Then
                    For j = 1 To totalCols
                        mergedData(currentRow, j) = ActiveSheet.Cells(i, j).Value
                    Next j
                    currentRow = currentRow + 1
                End If
            Next i
            ActiveWorkbook.Close SaveChanges:=False
        Next fileName

        Set ws = ThisWorkbook.Worksheets.Add
        ws.Range("A1").Resize(UBound(headerRow, 1), UBound(headerRow, 2)).Value = headerRow
        ws.Range("A2").Resize(currentRow - 1, UBound(mergedData, 2)).Value = mergedData

        ws.Columns("U:R").Delete
        ws.Columns("P:P").Delete
        ws.Columns("H:C").Delete
        ws.Columns("A:A").Delete

        lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        For i = lastRow To 2 Step -1
            curBlock = ws.Cells(i, 3).Value
            curName = ws.Cells(i, 6).Value
            sumQty = ws.Cells(i, 9).Value
            For j = i - 1 To 1 Step -1
                If ws.Cells(j, 3).Value = curBlock And ws.Cells(j, 6).Value = curName Then
                    sumQty = sumQty + ws.Cells(j, 9).Value
                    ws.Rows(j).Delete
                    i = i - 1
                    lastRow = lastRow - 1
                End If
            Next j
            ws.Cells(i, 9).Value = sumQty
        Next i

        Dim startingColumn As Long
        startingColumn = 10 
        ws.Cells(1, startingColumn).Value = "Остаток, шт."
        ws.Cells(1, startingColumn + 1).Value = "Процент продаж"
        ws.Cells(1, startingColumn + 2).Value = "Казань"
        ws.Cells(1, startingColumn + 3).Value = "НН"
        ws.Cells(1, startingColumn + 4).Value = "Москва"
        ws.Cells(1, startingColumn + 5).Value = "Краснодар"

        Dim lastRowProcessed As Long
        lastRowProcessed = ws.Cells(Rows.Count, 1).End(xlUp).Row
        For i = lastRowProcessed To 2 Step -1
            block= ws.Cells(i, 3).Value
            count= ws.Cells(i, 9).Value
            If block= "April Cash&Carry" Then
                ws.Cells(i, 12).Value = count
            ElseIf block= "April cash&carry (Москва)" Then
                ws.Cells(i, 14).Value = count
            ElseIf block= "April Cash&Carry (Нижний Новгород)" Then
                ws.Cells(i, 13).Value = count
            ElseIf block= "Цветы Краснодара" Then
                ws.Cells(i, 15).Value = count
            End If
            For j = i - 1 To 2 Step -1
                If ws.Cells(j, 6).Value = ws.Cells(i, 6).Value Then
                    ws.Cells(j, 12).Value = ws.Cells(j, 12).Value + ws.Cells(i, 12).Value 
                    ws.Cells(j, 13).Value = ws.Cells(j, 13).Value + ws.Cells(i, 13).Value 
                    ws.Cells(j, 14).Value = ws.Cells(j, 14).Value + ws.Cells(i, 14).Value 
                    ws.Cells(j, 15).Value = ws.Cells(j, 15).Value + ws.Cells(i, 15).Value 
                    ws.Rows(i).Delete
                    Exit For
                End If
            Next j
        Next i

        For i = 2 To lastRowProcessed
            ws.Cells(i, 9).Value = ws.Cells(i, 12).Value + ws.Cells(i, 13).Value + ws.Cells(i, 14).Value + ws.Cells(i, 15).Value
        Next i

        For i = 2 To lastRowProcessed
            If IsEmpty(ws.Cells(i, 12).Value) Then ws.Cells(i, 12).Value = 0 
            If IsEmpty(ws.Cells(i, 13).Value) Then ws.Cells(i, 13).Value = 0 
            If IsEmpty(ws.Cells(i, 14).Value) Then ws.Cells(i, 14).Value = 0 
            If IsEmpty(ws.Cells(i, 15).Value) Then ws.Cells(i, 15).Value = 0 
        Next i

        ws.Columns("L").ColumnWidth = 10
        ws.Columns("M").ColumnWidth = 10
        ws.Columns("N").ColumnWidth = 10
        ws.Columns("O").ColumnWidth = 10

        ws.Columns("B:B").Cut
        ws.Columns("I:I").Insert Shift:=xlToRight

        ws.Columns("I:I").Insert Shift:=xlToRight

        ws.Cells(1, 9).Value = "Заявлено, шт."
        ws.Cells(1, 11).Value = "Остаток, шт."

        ws.Cells(1, 5).Value = "Наименование Предзаказа"
        ws.Cells(1, 8).Value = "Менеджер по закупке"
        ws.Cells(1, 10).Value = "Куплено, шт."

        ws.Cells.EntireColumn.AutoFit

        Set selectedFiles = Nothing
        Set ws = Nothing
    End If

End Sub

=ЕСЛИ(И(B2>=I2; B2<=J2); G2; ЕСЛИ(И(B2<J2; B2+1<=J2); G2-1; ЕСЛИ(И(B2>J2; B2>=J2); G2+1; "")))

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sHt As String
    Dim strOk As Variant
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    sHt = Me.Name
    
    Set ws1 = ThisWorkbook.Sheets("МС")
    Set ws2 = ThisWorkbook.Sheets("Клик")
    
    If Not Intersect(Target, ws2.UsedRange) Is Nothing Then
        Dim lastName As String
        lastName = Split(Target.Value, " ")(2) 
        
        Dim foundRange As Range
        Set foundRange = ws1.Columns(4).Find(lastName, LookIn:=xlValues, LookAt:=xlPart)
        
        If Not foundRange Is Nothing Then
            Dim amount As Double
            amount = Target.Offset(0, 2).Value
            
            Dim amountRange As Range
            Set amountRange = ws1.Columns(3).Find(amount, LookIn:=xlValues, LookAt:=xlWhole)
            
            If Not amountRange Is Nothing Then
                ws1.Cells(amountRange.Row, 1).Select
                ws1.Cells(amountRange.Row, 1).Interior.Color = RGB(192, 192, 192) 
            Else
                MsgBox "У ИП " & Target.Value & " не найдена накладная с суммой " & amount & " в МойСклад", vbInformation
            End If
        Else
            MsgBox "У ИП " & Target.Value & " не найдена накладная в МойСклад", vbInformation
        End If
    End If
End Sub