макрос, который бы находил одинаковые значения в столбцах C и D по разным данным столбца…
- 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
Assistant
Хорошо, вот макрос на VBA для Excel, который выполнит описанную задачу.
Он будет искать группы строк, где значения в столбцах C и D совпадают, но значения в столбце A при этом различаются. Если найдена такая группа ровно из 3-х строк, макрос заменит значения в столбце C этих трех строк (в порядке их следования в таблице) на 1, 1.5 и 1.8.
Как использовать макрос:
- Откройте вашу книгу Excel.
- Нажмите
Alt + F11, чтобы открыть редактор VBA (Visual Basic for Applications). - В окне проекта (слева) выберите
Insert->Module. - Вставьте следующий код в открывшийся модуль.
- Закройте редактор VBA.
- Убедитесь, что у вас активен лист с данными.
- Нажмите
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Пояснения к коду:
Scripting.Dictionary: Макрос использует объектScripting.Dictionary(требует подключения библиотеки "Microsoft Scripting Runtime" или создания объекта черезCreateObject, что и сделано для простоты). Это эффективный способ хранения и поиска уникальных комбинаций значений.- Главный словарь
matchingGroups: Ключом этого словаря является строка, объединяющая отформатированные значения из столбцов C и D (например, "1.50|-2.50"). Значением является другой словарь. - Внутренний словарь
aValueRows: Для каждой комбинации C/D (ключ главного словаря) создается внутренний словарь. Ключом внутреннего словаря является уникальное значение из столбца A, а значением - номер строки, в которой это значение A встретилось вместе с данной комбинацией C/D. Макрос сохраняет только первый встреченный номер строки для каждого уникального A в группе C/D. - Первый проход: Макрос сканирует все строки данных. Для каждой строки он формирует ключ C/D и добавляет номер строки во внутренний словарь соответствующей группы, если значение A уникально для этой группы.
- Второй проход: Макрос перебирает все группы, собранные в первом проходе. Если количество уникальных значений A в группе (т.е., количество элементов во внутреннем словаре
aValueRows.Count) равно ровно 3, то это и есть нужная группа для замены. - Сортировка строк: Номера строк из найденной группы извлекаются, копируются в массив, и массив сортируется по возрастанию номеров строк. Это важно для того, чтобы замена (1, 1.5, 1.8) происходила в правильном порядке - сначала для строки с наименьшим номером, потом для следующей, и т.д.
- Замена: Значения 1, 1.5, 1.8 последовательно записываются в столбец C отсортированных строк.
Format(..., "0.00"): Использование функцииFormatпри создании ключа C/D помогает избежать возможных проблем с точностью сравнения чисел с плавающей точкой. Значения 2.50 и 2.5 могут храниться по-разному, но их форматированное представление "2.50" будет одинаковым.- Обработка ошибок и пустых ячеек: Добавлены базовые проверки на ошибки или пустые ячейки в столбцах A, C, D, чтобы макрос не прерывался некорректно.
- Оптимизация: Отключение
ScreenUpdatingиCalculationускоряет работу макроса на больших объемах данных.
В вашем примере данных есть одна такая группа из 3-х строк:
- Строка с "Главный инженер" (1.50, -2.50)
- Строка с "Руводитель отдела" (1.50, -2.50)
- Строка с "Начальник отдела" (1.50, -2.50)
После запуска макроса столбец C в этих трех строках должен измениться:
- "Главный инженер" -> C = 1
- "Руводитель отдела" -> C = 1.5
- "Начальник отдела" -> C = 1.8
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.