Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Макрос с формулами, Как прописать в макросе формулу, чтобы после его выполнения в ячейках уже был итог расчета, а не формула
 
#1
27.04.2024 13:31:46
Доброго дня!
Прошу помощи перевести формулы в макрос, чтобы по нажатию кнопки нужные значения рассчитывались на весь массив и в конкретных ячейках был прописан итог расчета, а не формула по которой он посчитан

Сейчас формулы рассчитываются с 4 строки и до конца массива (он все время меняется, бывает 1 000 строк, бывает 200 000 строк) в качестве переменной для определения последней строки обработки на данный момент используется "PosStr" - последняя строка рабочей таблицы, ну или же можно ориентироваться на последнюю заполненную ячейку в столбце А, ниже уже расчеты не нужны

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

Понимаю, что запрос весьма дерзкий, но если кому не сложно и есть свободное время...
Буду рад любой помощи, хоть 1-2 формулы уже огромный плюс
Спасибо!!!

сами формулы:
1. в столбец P, начиная с 4 строки
=ЕСЛИОШИБКА(ОКРУГЛ(СРЗНАЧЕСЛИ($C4:$O4;">0");1);0)

2.в столбец R, начиная с 4 строки
=Лист2!$Y4+Лист2!$AB4

3.в столбец U, начиная с 4 строки
=ЕСЛИОШИБКА(ВПР($A4;Лист5'!$A:$X;24;0)+ВПР($A4;'Лист5'!$A:$AA;27;0);0)

4.в столбец Z, начиная с 4 строки
=Лист2!$W4+Лист2!$AB4/'Лист7'!$R$3

5.в столбец AC, начиная с 4 строки
=ЕСЛИОШИБКА(ВПР($A4;'Лист5'!$A:$V;22;0)+ВПР($A4;'Лист5'!$A:$AA;27;0)/'Лист7'!$R$6;0)

6.в столбец AJ, начиная с 4 строки
Код
=ГИПЕРССЫЛКА("https://www.wildberries.ru/catalog/"&Лист2!E4&"/detail.aspx?targetUrl=GP";"открыть")
*прописал в виде кода, чтобы не отображалось в виде ссылки. если такое вообще возможно, чтобы в ячейке отображалась сама ссылка, или слово "открыть", но не формула гиперссылки

7.в столбец AG, начиная с 4 строки
=ЕСЛИ($R4=0;"";ЕСЛИ($Z4>$AA4*1,25;"Рост";ЕСЛИ($Z4<$AA4*0,75;"Падение";"")))

8.в столбец AW, начиная с 4 строки
=СУММ($AP4:$AV4)

9.в столбец AX, начиная с 4 строки
=ЕСЛИОШИБКА(ИНДЕКС(Лист3!$O$W;ПОИСКПОЗ($A4;Лист3!$O:$O;0);ПОИСКПОЗ(Лист1!$BA$2;Лист3!$O$1:$W$1;0));0)

10.в столбец Y, начиная с 4 строки
=ЕСЛИОШИБКА(ВПР($A4;'Лист5'!$A:$W;23;0);0)
 
 
 
#2
27.04.2024 13:35:43
mitya528,
В чем цель замены формул на значения? Они долго обновляются?
Вы представляете, сколько всего хотите сделать чужими руками просто так?

для самого простого примера:
Код
Sub aaa()
lLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For n = 4 To lLastRow
ActiveSheet.Range("R" & n) = Sheets("Лист2").Range("Y" & n) + Sheets("Лист2").Range("AB" & n)
Next
End Sub
Изменено: evgeniygeo - 27.04.2024 13:39:02
 
 
 
#3
27.04.2024 13:44:49
evgeniygeo,
Цитата
написал:
В чем цель
цель ускорить сам процесс расчета, и дальнейшее использование итогов
Цитата
написал:
Они долго обновляются?
да, в некоторые моменты возникают проблемы, причем на разных этапах (пересчет формулы, сохранение/открытие файла, большой размер самого файла
Цитата
написал:
Вы представляете, сколько всего хотите сделать чужими руками
представляю, понимаю, что запрос выглядит весьма наглым, решил не создавать 10 разных тем, объединил в одну. если кто то может помочь и ему это не трудно - огромное спасибо, если мой запрос для кого то перебор - я понимаю, готов остаться ни с чем)
искал в интернете информацию, пробовал разные варианты - все не то, получить результат самостоятельно не смог. потому буду рад хотя бы частичной помощи, хоть наводка ... Дальше попробую додумать как это делается
 
 
 
#4
27.04.2024 13:47:46
evgeniygeo,
Цитата
написал:
для самого простого примера
Большое спасибо!
Уверен, что даже частичное решение задумки положительно повлияет на результат
 
 
 
#5
27.04.2024 13:48:24
mitya528,
в таком случае, советую разобраться с этим (добавил 1) и дальше собираться по кусочкам:
Код
Sub aaa()
lLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For n = 4 To lLastRow
On Error Resume Next
ActiveSheet.Range("P" & n) = Round(Application.AverageIfs(ActiveSheet.Range("C" & n & ":O" & n), ActiveSheet.Range("C" & n & ":O" & n), ">0"), 1)
ActiveSheet.Range("R" & n) = Sheets("Лист2").Range("Y" & n) + Sheets("Лист2").Range("AB" & n)
Next
End Sub

Почитайте еще здесь
Изменено: evgeniygeo - 27.04.2024 14:00:53
 
 
 
#6
27.04.2024 13:59:36
evgeniygeo,
Цитата
написал:
добавил 1
И еще раз спасибо! уже далеко не первый раз Вы меня выручаете
 
 
 
#7
27.04.2024 14:14:06
mitya528,
Название темы должно отражать Вашу Задачу, а не Способ, которым Вы пытаетесь ее решить.
Это на будущее.
И помогающим тоже...
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
27.04.2024 14:16:19
Цитата
mitya528 написал:
решил не создавать 10 разных тем, объединил в одну
А это уже более серьезное нарушение!
Одна задача- одна тема!
Согласие есть продукт при полном непротивлении сторон
 
 
 
#9
27.04.2024 14:18:00
Sanja,
Цитата
написал:
Одна задача- одна тема!
Понял, учту оба замечания
 
 
 
#10
02.05.2024 08:08:37
Еще раз большое спасибо evgeniygeo, неплохой старт в решении моих задач обеспечил)
с большей частью разобрался, если вдруг кому пригодится:
Код
Sub aaa()
PosStr = Sheets("Второй").UsedRange.Rows.Count ' конец диапазона (последняя строка), до которого нужно производить расчеты
For n = 5 To PosStr ' сам диапазон, начиная с 5 строки и до конца диапазона (PosStr)- последней строки
On Error Resume Next ' в случае ошибки продолжить выполнение макроса

Sheets("Первый").Range("P" & n) = Round(Application.AverageIfs(Sheets("Первый").Range("C" & n & ":O" & n), Sheets("Первый").Range("C" & n & ":O" & n), ">0"), 1) 'формула 1

Sheets("Первый").Range("R" & n) = Sheets("Второй").Range("Y" & n) + Sheets("Второй").Range("AB" & n) 'Формула 2

Sheets("Первый").Range("U" & n) = Application.WorksheetFunction.VLookup(Sheets("Первый").Range("A" & n), Sheets("Третий").Range("A:X"), 24, False) + _
                                  Application.WorksheetFunction.VLookup(Sheets("Первый").Range("A" & n), Sheets("Третий").Range("A:AA"), 27, False) 'Формула 3
                                  
Sheets("Первый").Range("Z" & n) = Sheets("Второй").Range("W" & n) + (Sheets("Второй").Range("AB" & n) / Sheets("Четвертый").Range("R3")) 'Формула 4

Sheets("Первый").Range("AC" & n) = Application.WorksheetFunction.VLookup(Sheets("Первый").Range("A" & n), Sheets("Третий").Range("A:V"), 22, False) + _
                                  (Application.WorksheetFunction.VLookup(Sheets("Первый").Range("A" & n), Sheets("Третий").Range("A:AA"), 27, False) / Sheets("Четвертый").Range("R6")) 'Формула 5
                                  
 If Sheets("Первый").Range("R" & n) = 0 Then 'если 0, то
 Sheets("Первый").Range("AG" & n) = "" 'ничего
 ElseIf Sheets("Первый").Range("Z" & n) > (Sheets("Первый").Range("AA" & n) * 1.25) Then 'если больше, то
 Sheets("Первый").Range("AG" & n) = "Рост" 'рост
 ElseIf Sheets("Первый").Range("Z" & n) < (Sheets("Первый").Range("AA" & n) * 0.75) Then 'если меньше, то
 Sheets("Первый").Range("AG" & n) = "Падение" ' падение
 End If 'конец блока если                                      ' формула 7

Sheets("Первый").Range("AW" & n) = Application.WorksheetFunction.Sum(Sheets("Первый").Range("AP" & n & ":AV" & n)) ' формула 8

Sheets("Первый").Range("AX" & n) = WorksheetFunction.Index((Sheets("Пятый").Range("O2:W" & Cells(Rows.Count, 15).End(xlUp).Row)), _
                                  (WorksheetFunction.Match(Sheets("Первый").Range("A" & n), Sheets("Пятый").Range("O2:O" & Cells(Rows.Count, 15).End(xlUp).Row), 0)), _
                                  (WorksheetFunction.Match(Sheets("Первый").Range("AX2"), Sheets("Пятый").Range("O1:W1"), 0))) ' формула 9

Sheets("Первый").Range("Y" & n) = Application.WorksheetFunction.VLookup(Sheets("Первый").Range("A" & n), Sheets("Третий").Range("A:W"), 23, False) ' формула 10 (в случае ошибки ячейка остается пустой, что само по себе равно 0)
                                  
Next 'продолжить (далее)
End Sub
Цитата
написал:
6.в столбец AJ, начиная с 4 строкиКод=ГИПЕРССЫЛКА("https://www.wildberries.ru/catalog/"&Лист2!E4&"/detail.aspx?target...)
*прописал в виде кода, чтобы не отображалось в виде ссылки. если такое вообще возможно, чтобы в ячейке отображалась сама ссылка, или слово "открыть", но не формула гиперссылки
эта задача пока остается не решенной, если для нее вообще есть решение.

Если вдруг у кого то возникнет желание дополнить тему, оптимизировать макрос - буду очень рад и признателен)
Изменено: mitya528 - 02.05.2024 08:25:04
 
 
 
Страницы: 1
Наверх