Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash-preview-04-17· GoogleHugging Face LMArena Human Preference 140K

макрос, который бы находил одинаковые значения в столбцах C и D по разным данным столбца…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Googlegemma-3-27b-it’s answer.
  1. User

    макрос, который бы находил одинаковые значения в столбцах C и D по разным данным столбца A, и заменял их последовательно на следующие: если совпадают значения одновременно в 3 строках столбца A, то значение в столбце C равно последовательно 1,17, затем 1,5, затем 1,8: Тип ЗС "Приоиритет влияния компонент " "Влияние на компонент " "Интерес к компоненту" Заказчик 0,01 2,50 2,50 Надзорный орган 0,01 2,50 0,50 Отраслевой исполнитель 0,01 2,50 -2,50 Проектировщик 0,01 1,50 1,50 Руководитель направления 0,01 2,50 1,50 Руководитель ИТ-отдела 0,01 0,50 1,50 Исполнительный директор 0,01 0,50 -0,50 Начальник ФЭО 0,01 0,50 -1,50 Главный инженер 0,01 1,50 -2,50 Руводитель отдела 0,01 1,50 -2,50 Начальник отдела 0,01 1,50 -2,50 Дочернее общество 0,01 2,50 -1,50

  2. Gemini Assistant

    Хорошо, вот макрос на VBA для Excel, который выполнит описанную задачу.

    Он будет искать группы строк, где значения в столбцах C и D совпадают, но значения в столбце A при этом различаются. Если найдена такая группа ровно из 3-х строк, макрос заменит значения в столбце C этих трех строк (в порядке их следования в таблице) на 1, 1.5 и 1.8.

    Как использовать макрос:

    1. Откройте вашу книгу Excel.
    2. Нажмите Alt + F11 , чтобы открыть редактор VBA (Visual Basic for Applications).
    3. В окне проекта (слева) выберите Insert -> Module.
    4. Вставьте следующий код в открывшийся модуль.
    5. Закройте редактор VBA.
    6. Убедитесь, что у вас активен лист с данными.
    7. Нажмите Alt + F8, выберите макрос ReplaceCValuesForMatchingGroups и нажмите Выполнить.

    Код макроса:

    Sub ReplaceCValuesForMatchingGroups()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim matchingGroups As Object ' Используем Scripting.Dictionary
        Dim key As String
        Dim aValueRows As Object ' Внутренний Dictionary для хранения уникальных значений из столбца A и номера строки
        Dim aValue As String
        Dim cValueFormatted As String ' Отформатированное значение для ключа словаря
        Dim dValueFormatted As String ' Отформатированное значение для ключа словаря
        Dim rowNumber As Long
        Dim rowsToModify As Collection ' Коллекция для временного хранения номеров строк
        Dim rowArray() As Variant      ' Массив для сортировки номеров строк
        Dim i As Long, j As Long, k As Long
        Dim tempRow As Variant         ' Для временного хранения при сортировке
    
        ' Значения для замены в столбце C
        Dim replacementValues As Variant
        replacementValues = Array(1, 1.5, 1.8) ' Массив 0-индексирован: replacementValues(0)=1, (1)=1.5, (2)=1.8
    
        ' Устанавливаем активный рабочий лист
        Set ws = ThisWorkbook.ActiveSheet ' Макрос работает на текущем активном листе
    
        ' Отключаем обновление экрана и автоматические расчеты для ускорения
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
    
        ' Находим последнюю строку с данными в столбце A
        ' Предполагаем, что столбец A содержит данные во всех строках, которые нужно обработать
        lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
        ' Проверяем, есть ли данные, кроме заголовков (предполагаем заголовки в строке 1)
        If lastRow < 2 Then
            MsgBox "Нет данных для обработки (или только заголовки).", vbInformation
            GoTo CleanUp
        End If
    
        ' Создаем главный словарь для хранения групп.
        ' Ключ: Комбинация значений из C и D (например, "2.50|-1.50")
        ' Значение: Внутренний словарь
        Set matchingGroups = CreateObject("Scripting.Dictionary")
    
        ' Первый проход: Идентифицируем потенциальные группы
        ' Перебираем строки, начиная со второй (предполагая, что в первой - заголовки)
        For i = 2 To lastRow
            ' Считываем значения из столбцов A, C, D
            ' Используем Format для C и D, чтобы избежать проблем с точностью чисел с плавающей точкой при сравнении
            On Error Resume Next ' Включаем обработку ошибок на случай, если в C или D не число
            cValueFormatted = Format(ws.Cells(i, 3).Value, "0.00") ' Столбец C
            dValueFormatted = Format(ws.Cells(i, 4).Value, "0.00") ' Столбец D
            On Error GoTo 0      ' Отключаем обработку ошибок
    
            ' Пропускаем строки, если C или D пустые, содержат ошибки, или форматирование дало пустую строку
            If IsError(ws.Cells(i, 3).Value) Or IsError(ws.Cells(i, 4).Value) Or _
               Trim(cValueFormatted) = "" Or Trim(dValueFormatted) = "" Then
                GoTo NextRow
            End If
    
            aValue = Trim(CStr(ws.Cells(i, 1).Value)) ' Столбец A - преобразуем в строку и убираем пробелы
    
            ' Пропускаем строки, если A пустое
            If aValue = "" Then
                GoTo NextRow
            End If
    
            ' Формируем ключ словаря из значений C и D
            key = cValueFormatted & "|" & dValueFormatted
    
            ' Проверяем, есть ли эта комбинация C/D уже в главном словаре
            If Not matchingGroups.Exists(key) Then
                ' Если нет, добавляем новую запись и создаем внутренний словарь для значений из столбца A
                Set aValueRows = CreateObject("Scripting.Dictionary")
                matchingGroups.Add key, aValueRows
            Else
                ' Если есть, получаем ссылку на существующий внутренний словарь
                Set aValueRows = matchingGroups(key)
            End If
    
            ' Проверяем, было ли уже это значение из столбца A записано для данной комбинации C/D
            If Not aValueRows.Exists(aValue) Then
                ' Если нет, добавляем значение A и номер текущей строки во внутренний словарь.
                ' Мы сохраняем только ПЕРВЫЙ встреченный номер строки для каждого УНИКАЛЬНОГО значения A
                ' в рамках данной группы C/D. Это соответствует требованию "по разным данным столбца A".
                aValueRows.Add aValue, i
            End If
    
    NextRow:
            Set aValueRows = Nothing ' Освобождаем ссылку на внутренний словарь перед следующей итерацией
        Next i
    
        ' Второй проход: Обрабатываем найденные группы
        ' Перебираем все ключи (комбинации C/D) в главном словаре
        For Each key In matchingGroups.Keys
            Set aValueRows = matchingGroups(key)
    
            ' Проверяем, соответствует ли количество уникальных значений из столбца A (для данного C/D) - трём
            If aValueRows.Count = 3 Then
                ' Найдена группа из 3-х строк с совпадающими C/D и различными A
    
                ' Извлекаем номера строк из значений внутреннего словаря в коллекцию
                Set rowsToModify = New Collection
                For Each rowNumber In aValueRows.Items ' Items() внутреннего словаря - это номера строк, которые мы там сохранили
                    rowsToModify.Add rowNumber
                Next rowNumber
    
                ' Копируем номера строк из коллекции в массив для последующей сортировки
                ReDim rowArray(1 To rowsToModify.Count)
                For j = 1 To rowsToModify.Count
                    rowArray(j) = rowsToModify(j)
                Next j
    
                ' Сортируем массив номеров строк по возрастанию (простая сортировка пузырьком для 3-х элементов)
                For j = 1 To UBound(rowArray) - 1
                    For k = j + 1 To UBound(rowArray)
                        If rowArray(j) > rowArray(k) Then
                            tempRow = rowArray(j)
                            rowArray(j) = rowArray(k)
                            rowArray(k) = tempRow
                        End If
                    Next k
                Next j
    
                ' Применяем значения замены в столбце C в соответствии с отсортированными номерами строк
                ' rowArray(1) -> replacementValues(0) = 1
                ' rowArray(2) -> replacementValues(1) = 1.5
                ' rowArray(3) -> replacementValues(2) = 1.8
                For j = 1 To UBound(rowArray)
                    ws.Cells(rowArray(j), 3).Value = replacementValues(j - 1) ' Индекс массива replacementValues на 1 меньше
                Next j
    
            End If ' Конец проверки Count = 3
    
            Set aValueRows = Nothing ' Освобождаем ссылку на внутренний словарь
        Next key
    
    CleanUp:
        ' Освобождаем главный словарь
        Set matchingGroups = Nothing
        Set ws = Nothing
    
        ' Восстанавливаем обновление экрана и автоматические расчеты
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    
        MsgBox "Макрос выполнен.", vbInformation
    
    End Sub
    

    Пояснения к коду:

    1. Scripting.Dictionary: Макрос использует объект Scripting.Dictionary (требует подключения библиотеки "Microsoft Scripting Runtime" или создания объекта через CreateObject, что и сделано для простоты). Это эффективный способ хранения и поиска уникальных комбинаций значений.
    2. Главный словарь matchingGroups: Ключом этого словаря является строка, объединяющая отформатированные значения из столбцов C и D (например, "1.50|-2.50"). Значением является другой словарь.
    3. Внутренний словарь aValueRows: Для каждой комбинации C/D (ключ главного словаря) создается внутренний словарь. Ключом внутреннего словаря является уникальное значение из столбца A, а значением - номер строки, в которой это значение A встретилось вместе с данной комбинацией C/D. Макрос сохраняет только первый встреченный номер строки для каждого уникального A в группе C/D.
    4. Первый проход: Макрос сканирует все строки данных. Для каждой строки он формирует ключ C/D и добавляет номер строки во внутренний словарь соответствующей группы, если значение A уникально для этой группы.
    5. Второй проход: Макрос перебирает все группы, собранные в первом проходе. Если количество уникальных значений A в группе (т.е., количество элементов во внутреннем словаре aValueRows.Count) равно ровно 3, то это и есть нужная группа для замены.
    6. Сортировка строк: Номера строк из найденной группы извлекаются, копируются в массив, и массив сортируется по возрастанию номеров строк. Это важно для того, чтобы замена (1, 1.5, 1.8) происходила в правильном порядке - сначала для строки с наименьшим номером, потом для следующей, и т.д.
    7. Замена: Значения 1, 1.5, 1.8 последовательно записываются в столбец C отсортированных строк.
    8. Format(..., "0.00"): Использование функции Format при создании ключа C/D помогает избежать возможных проблем с точностью сравнения чисел с плавающей точкой. Значения 2.50 и 2.5 могут храниться по-разному, но их форматированное представление "2.50" будет одинаковым.
    9. Обработка ошибок и пустых ячеек: Добавлены базовые проверки на ошибки или пустые ячейки в столбцах A, C, D, чтобы макрос не прерывался некорректно.
    10. Оптимизация: Отключение ScreenUpdating и Calculation ускоряет работу макроса на больших объемах данных.

    В вашем примере данных есть одна такая группа из 3-х строк:

    • Строка с "Главный инженер" (1.50, -2.50)
    • Строка с "Руводитель отдела" (1.50, -2.50)
    • Строка с "Начальник отдела" (1.50, -2.50)

    После запуска макроса столбец C в этих трех строках должен измениться:

    • "Главный инженер" -> C = 1
    • "Руводитель отдела" -> C = 1.5
    • "Начальник отдела" -> C = 1.8

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.