Обработка формул Excel в VBA

@Artlokos · Регистрация: 19.06.2017

Студворк — интернет-сервис помощи студентам

Добрый вечер! Буду благодарен за любую помощь.
Суть проблемы.

Есть книга Excel с двумя листами. Первый лист ежедневно заполняется данными, второй лист создан для хранения эталонов.
Есть формула, которая сравнивает в каждой строке ячейки А1 и B1 из первого листа с ячейками А1 и В1 из второго листа.
Необходимо написать макрос, чтоб значения ячеек А1 и В1 из первого листа сравнивались с ячейками А1,В1 А2,В2 .... Аn,Bn второго листа и по окончанию таблицы на втором листе начиналось сравнение ячеек А2,В2 на первом листе с ячейками А1,В1 А2,В2 .... Аn,Bn второго листа. и так до конца таблицы на первом листе.

И второй вопрос. Можно ли уже готовую формулу excel вставить в код VBA и циклом переходить к новой строке и этой формулой обрабатывать данные.

Заранее спасибо!!!

@Narimanych · 21.11.2019, 18:14

Artlokos,

Сообщение от Artlokos

Необходимо написать макрос, чтоб значения ячеек А1 и В1 из первого листа сравнивались с ячейками А1,В1 А2,В2 .... Аn,Bn второго листа и по окончанию таблицы на втором листе начиналось сравнение ячеек А2,В2

И что делать при сравнении?

@Artlokos · 23.11.2019, 16:11 **[ТС]**

при совпадении значений в ячейках А и В из второго листа со значениями в ячейках А и В из первого листа, взять значение из ячейки С из второго листа и вставить в соответствующую ячейку С в первом листе.
при несовпадении ставить прочерк и переходить к следующей строке на первом листе

@Narimanych · 23.11.2019, 16:40

Artlokos,

Приложите файлик с данными... строчек 5-10 на каждом листе...

Добавлено через 15 минут
Artlokos,

Без проверки на данных...
Проверьте:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub MMM()
 
LR1 = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
LR2 = ThisWorkbook.Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row
 
Application.ScreenUpdating = False
For i = 1 To LR1
Flag = True
 For j = 1 To LR2
    If Sheets(1).Cells(i, 1).Value = Sheets(2).Cells(j, 1).Value And Sheets(1).Cells(i, 2).Value = Sheets(2).Cells(j, 2).Value Then
     Sheets(1).Cells(i, 3).Value = Sheets(2).Cells(j, 3).Value
     Flag = False
     Exit For
    End If
 Next
 If Flag = True Or Sheets(1).Cells(i, 3).Value = "" Then Sheets(1).Cells(i, 3).Value = "-"
Next
Application.ScreenUpdating = True
 
MsgBox "complete"
End Sub

@Artlokos · 25.11.2019, 09:48 **[ТС]**

Прошу прощения за поздний ответ. прикрепил файл для примера.

@Narimanych · 25.11.2019, 11:50

Artlokos,

Из вашего примера :
1.брать ячейку F1.
2. проверять каждую пару ячеек В и С из листа Каталог с парами ячеек (А,В) и (С,D) листа Поставки с учетом коэффицентов в ячейках Е и F листа Каталог.
3. после проверки при совпадении....

При совпадении чего? -Как вариант пару ячеек В и С из листа Каталог с парами или (А,В) или (С,D) листа Поставки

Дальше сами можете варьировать.

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub MMM()
 
LR1 = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
LR2 = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
If LR1 >= LR2 Then LR = LR1 Else LR = LR2
LR3 = ThisWorkbook.Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LR
Flag = True
 For j = 1 To LR2
    If Sheets(1).Cells(i, 1).Value = Sheets(2).Cells(j, 2).Value And Sheets(1).Cells(i, 2).Value = Sheets(2).Cells(j, 3).Value Or Sheets(1).Cells(i, 3).Value = Sheets(2).Cells(j, 2).Value And Sheets(1).Cells(i, 4).Value = Sheets(2).Cells(j, 3).Value Then
     Sheets(1).Cells(i, 6).Value = Sheets(2).Cells(j, 4).Value
     Flag = False
     Exit For
    End If
 Next
 If Flag = True Or Sheets(1).Cells(i, 6).Value = "" Then Sheets(1).Cells(i, 6).Value = "-"
Next
Application.ScreenUpdating = True
 
MsgBox "complete"
End Sub

@Artlokos · 25.11.2019, 12:56 **[ТС]**

попровал применить Ваш код, очень многое стало ясно. Огромное спасибо!!!

У меня остался еще один вопрос. В ячейке F листа Поставки есть формула для сравнения для пар ячеек с учетом коэффицентов. Возможно ли в скрипте сделать так чтоб через цикл менялись ячейки, используемые в формуле.

Другими словами в этой формуле выделенные ячейки должны меняться :

каталог!B$2 -> каталог!B$3
каталог!С$2 -> каталог!С$3
каталог!D$2 -> каталог!D$3 и так до конца листа Каталог

ЕСЛИ(ИЛИ(И(ABS((каталог!B$2-$A1))<=ABS(каталог!E$2);ABS(каталог!C$2-B1)<=ABS(каталог!F$2));И(ABS((каталог!B$2-$C1))<=ABS(каталог!E$2);ABS(каталог!C$2-D1)<=ABS(каталог!F$2)));каталог!D$2;"-")

а затем макрос переходит к следующей ячейке в листе Поставки и уже для нее заново применяет эту же формулу

@Narimanych · 25.11.2019, 14:18

Artlokos,

Посмотрите в прикрепленном файле и по аналогии сделайте сами

@Artlokos · 27.11.2019, 09:40 **[ТС]**

сделал по аналогии и столкнулся с такой проблемой, простые формулы работают без проблем, а если взять сложную вроде такой, где много вложенных условий, то вылетают разные ошибки.

И(ABS((каталог!B$2-$A1))<=ABS(каталог!E$2);ABS(каталог!C$2-B1)<=ABS(каталог!F$2))

главная загвоздка наступает когда дохожу до логического И, никак не могу сообразить как правильно составить формулу в коде vba.

@Hugo121 · 27.11.2019, 11:43

Добрый день.
Как я понимаю смысл логического И - то в коде это будет вложенные ЕСЛИ:

Visual Basic
1
2
3
4
5
если а=б то
  если в=г то
    делаем что-то
  конец если
конец если

Но к "как правильно составить формулу в коде vba" это кажется малопрменимо, да и зачем?...
Хотя мне приходилось сочинять простые формулы в коде, чтоб затем воткнуть их в ячейки для юзеров. Сложные бессмысленно

@Artlokos · 27.11.2019, 12:01 **[ТС]**

Смысл в том, что другая программа, написанная сторонней организацией, выдает результат в таблицу Excel, а нам необходимо в каждой строке брать две пары ячеек и сравнивать их с эталонной парой и в конце принимать решение, если хотя бы одна из пар совпадает с эталоном, то вносить в столбец с результатами название эталона.
Формула работает для одной позиции в списке эталонов, а нужно проверить весь список с эталонами на предмет совпадения.
Дело в том, что одну большую сложную формулу нужно применить много раз.
У меня получилось сделать такие формулы по отдельности

a = ABS(каталог!B$2-A1)<=ABS(каталог!E$2)
b = ABS(каталог!C$2-B1)<=ABS(каталог!F$2)

c = ABS(каталог!В$2-C1)<=ABS(каталог!E$2)
d = ABS(каталог!C$2-D1)<=ABS(каталог!F$2)

теперь нужно сделать ЕСЛИ (a И b) ИЛИ (c И d) == true, то берем название эталона, если false, то " - " и break внутреннего цикла и переход к следующей строке проверки.
Вот с этим как раз и нужна помощь

@Hugo121 · 27.11.2019, 12:14

Кодом это можно сделать так:
1. пробегаете один раз по своему эталону, запоминаете в scipting.dictionary или collection (что сложнее в целом) свои пары с эталонами
2. пробегаете один раз по присланной таблице, сверяетесь с заполненным объектом (мнгновенно), вносите при наличии совпадения результат.
Если данных более десятка тысяч строк - есть смысл работать не с ячейками, а с массивом данных диапазона.
И никаких сложных или простых формул. Мы ведь в ветке VBA

@Artlokos · 27.11.2019, 12:20 **[ТС]**

Можете подсказать ссылку на материалы для более подробного изучения, чтоб не лопатить кучу лишней информации?

@Hugo121 · 27.11.2019, 12:36

Вообще словарь/коллекция применимы если ищется точное совпадение (а не больше/меньше), иначе толку от них мало.
По примеру можно так эту сложную формулу на лист забивать (просто включите рекордер!):

Visual Basic
1
2
3
4
5
6
7
8
9
10
Sub Macro1()
 
    With Range("F1")
    .FormulaR1C1 = _
        "=IF(OR(AND(ABS((каталог!R2C[-4]-RC1))<=ABS(каталог!R2C[-1]),ABS(каталог!R2C[-3]-RC[-4])<=ABS(каталог!R2C)),AND(ABS((каталог!R2C[-4]-RC3))<=ABS(каталог!R2C[-1]),ABS(каталог!R2C[-3]-RC[-2])<=ABS(каталог!R2C))),каталог!R2C[-2],""-"")"
    .AutoFill Destination:=Range("F1:F43")
    .Select
    End With
    
End Sub

Новые блоги и статьи Все статьи Все блоги /
Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Символьное дифференцирование igorrr37 13.02.2026 / * Программа принимает математическое выражение в виде строки и выдаёт его производную в виде строки и вычисляет значение производной при заданном х Логарифм записывается как: (x-2)log(x^2+2) -. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73

	Excel Обработка формул Excel в VBA 21.11.2019, 17:39. Показов 1994. Ответов 13 Метки excel, vba excel, формулы, vba excel макрос (Все метки) Добрый вечер! Буду благодарен за любую помощь. Суть проблемы. Есть книга Excel с двумя листами. Первый лист ежедневно заполняется данными, второй лист создан для хранения эталонов. Есть формула, которая сравнивает в каждой строке ячейки А1 и B1 из первого листа с ячейками А1 и В1 из второго листа. Необходимо написать макрос, чтоб значения ячеек А1 и В1 из первого листа сравнивались с ячейками А1,В1 А2,В2 .... Аn,Bn второго листа и по окончанию таблицы на втором листе начиналось сравнение ячеек А2,В2 на первом листе с ячейками А1,В1 А2,В2 .... Аn,Bn второго листа. и так до конца таблицы на первом листе. И второй вопрос. Можно ли уже готовую формулу excel вставить в код VBA и циклом переходить к новой строке и этой формулой обрабатывать данные. Заранее спасибо!!! 0

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73
	23.11.2019, 16:11 [ТС]
	при совпадении значений в ячейках А и В из второго листа со значениями в ячейках А и В из первого листа, взять значение из ячейки С из второго листа и вставить в соответствующую ячейку С в первом листе. при несовпадении ставить прочерк и переходить к следующей строке на первом листе 0

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73
	25.11.2019, 12:56 [ТС]
	попровал применить Ваш код, очень многое стало ясно. Огромное спасибо!!! У меня остался еще один вопрос. В ячейке F листа Поставки есть формула для сравнения для пар ячеек с учетом коэффицентов. Возможно ли в скрипте сделать так чтоб через цикл менялись ячейки, используемые в формуле. Другими словами в этой формуле выделенные ячейки должны меняться : каталог!B$2 -> каталог!B$3 каталог!С$2 -> каталог!С$3 каталог!D$2 -> каталог!D$3 и так до конца листа Каталог ЕСЛИ(ИЛИ(И(ABS((каталог!B$2-$A1))<=ABS(каталог!E$2);ABS(каталог!C$2-B1)<=ABS(каталог!F$2));И(ABS((каталог!B$2-$C1))<=ABS(каталог!E$2);ABS(каталог!C$2-D1)<=ABS(каталог!F$2)));каталог!D$2;"-") а затем макрос переходит к следующей ячейке в листе Поставки и уже для нее заново применяет эту же формулу 0

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73
	27.11.2019, 09:40 [ТС]
	сделал по аналогии и столкнулся с такой проблемой, простые формулы работают без проблем, а если взять сложную вроде такой, где много вложенных условий, то вылетают разные ошибки. И(ABS((каталог!B$2-$A1))<=ABS(каталог!E$2);ABS(каталог!C$2-B1)<=ABS(каталог!F$2)) главная загвоздка наступает когда дохожу до логического И, никак не могу сообразить как правильно составить формулу в коде vba. 0

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73
	27.11.2019, 12:01 [ТС]
	Смысл в том, что другая программа, написанная сторонней организацией, выдает результат в таблицу Excel, а нам необходимо в каждой строке брать две пары ячеек и сравнивать их с эталонной парой и в конце принимать решение, если хотя бы одна из пар совпадает с эталоном, то вносить в столбец с результатами название эталона. Формула работает для одной позиции в списке эталонов, а нужно проверить весь список с эталонами на предмет совпадения. Дело в том, что одну большую сложную формулу нужно применить много раз. У меня получилось сделать такие формулы по отдельности a = ABS(каталог!B$2-A1)<=ABS(каталог!E$2) b = ABS(каталог!C$2-B1)<=ABS(каталог!F$2) c = ABS(каталог!В$2-C1)<=ABS(каталог!E$2) d = ABS(каталог!C$2-D1)<=ABS(каталог!F$2) теперь нужно сделать ЕСЛИ (a И b) ИЛИ (c И d) == true, то берем название эталона, если false, то " - " и break внутреннего цикла и переход к следующей строке проверки. Вот с этим как раз и нужна помощь 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	27.11.2019, 12:14
	Кодом это можно сделать так: 1. пробегаете один раз по своему эталону, запоминаете в scipting.dictionary или collection (что сложнее в целом) свои пары с эталонами 2. пробегаете один раз по присланной таблице, сверяетесь с заполненным объектом (мнгновенно), вносите при наличии совпадения результат. Если данных более десятка тысяч строк - есть смысл работать не с ячейками, а с массивом данных диапазона. И никаких сложных или простых формул. Мы ведь в ветке VBA 0

@Artlokos 3 / 6 / 0 Регистрация: 19.06.2017 Сообщений: 73
	27.11.2019, 12:20 [ТС]
	Можете подсказать ссылку на материалы для более подробного изучения, чтоб не лопатить кучу лишней информации? 0