Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Макрос, выравнивающий поиск в диапазоне при вычислениях
 
#1
03.04.2018 10:49:08
Добрый день, уважаемые форумчане.
Можете помочь имеется таблица где в диапазоне столбца H:H, происходят вычисления.
Если в столбце H:H в формуле где, СУММПРОИЗВ() значение указать с символом $$, то при копировании таблицы возникает ошибка что новая таблица обращается к старой таблице.
Может кто знает какой то макрос который заставлял последнее значение СУММПРОИЗВ(D3:D6;E3:E6) подниматься вверх на столько на сколько оно опустилось строк от ряда (Наименование Количество Цена Цена доставки на 1 единицу Сумма товара Сумма доставки Сумма товара с доставкой), эту строку считать как -1
то следующая строка 0, следующая 1, 2, 3 и т.д. на столько надо уменьшить значение в СУММПРОИЗВ(D3:D6;E3:E6).
Идентификатор в ячейке A2 рядом с ячейкой наименование, для идентификации таблицы к которой обращаться для вычислений, чтобы вычисления производились в той таблице номер которой указан не знаю как это реализовать в автоматическом режиме (то есть сколько на листе найдется слово Наименование то такой номер требуется вывести в этой ячейке)
то есть для данного примера:
строка 3 - СУММПРОИЗВ(D3 (уменьшить B3, должно получиться D3):D6 (уменьшить B3, должно получиться D6);E3 (уменьшить B3, должно получиться E3):E6 (уменьшить B3, должно получиться E6))
строка 4 - СУММПРОИЗВ(D4 (уменьшить B4, должно получиться D3):D7 (уменьшить B4, должно получиться D3);E4 (уменьшить B4, должно получиться D3):E7 (уменьшить B4, должно получиться D3))
строка 5 - СУММПРОИЗВ(D5 (уменьшить B5, должно получиться D3):D8 (уменьшить B5, должно получиться D3);E5 (уменьшить B5, должно получиться D3):E8 (уменьшить B5, должно получиться D3))
строка 6 - СУММПРОИЗВ(D6 (уменьшить B6, должно получиться D3):D9 (уменьшить B6, должно получиться D3);E6 (уменьшить B6, должно получиться D3):E9 (уменьшить B6, должно получиться D3))
Заранее спасибо.
Изменено: ymal_qeb - 03.04.2018 10:50:54
 
 
 
#2
03.04.2018 11:05:06
ymal_qeb, добрый и Вам. Сможете в файле в столбце Н показать какие диапазоны должна брать СУММПРОИЗВ исходя из Вашей логики? Решение нужно только макросом?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#3
03.04.2018 11:07:31
Bema, не обязательно макросом, значения которые нужно брать показаны для данного примера, в столбце M:M, но если вставить значение столбца M:M, и полностью скопировать таблицу, то получается, что вторая таблица будет обращать к первой сейчас приложу пример того про, что я говорю
Изменено: ymal_qeb - 03.04.2018 11:09:46
 
 
 
#4
03.04.2018 11:12:23
ymal_qeb, вы же сразу укажите что количество товаров может быть разное, что у вас может в таблице быть и больше и меньше 4 наименований...  
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#5
03.04.2018 11:13:11
a.i.mershik, да количество товаров может быть разное.
 
 
 
#6
03.04.2018 11:58:19
a.i.mershik, можете подсказать а вообще можно ли как либо манипулировать ни значением ячейки, а номером ячейки через другую ячейку
пример есть ячейка B3, а в ячейке A1 (указано значение 1), можно ли от B3 отнять значение указанное значение A1, что бы получилось B2.
Изменено: ymal_qeb - 03.04.2018 11:58:39
 
 
 
#7
03.04.2018 12:12:30
ymal_qeb, можно , просто много нюансов...
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#8
03.04.2018 13:18:18
Нашел на форуме файл где подменяются значения, возможно его можно будет оптимизировать к таблице.
 
 
 
#9
03.04.2018 13:39:16
В диапазоне H R, вывел примерный вид подмены значений
Изменено: ymal_qeb - 03.04.2018 13:46:42
 
 
 
#10
03.04.2018 14:04:44
использовал в столбце S значение подставить вместо тех которые есть, можете подсказать из за чего возникает ошибка?
Изменено: ymal_qeb - 03.04.2018 14:07:05
 
 
 
#11
03.04.2018 14:38:52
ymal_qeb, кажется работает.
1 - обязательно в столбце А должны стоять не повторяющиеся цифры от минимального к максимальному. (напротив слова наименование)
2 - Должно быть написано слово "ДОСТАВКА"  на 3 строки ниже от последнего значения "наименования"
вроде все ...а дальше уже вниз по листу будет все нормально при копировании или протяжке формулы..но форма таблиц  должна быть одинакова.


И лучше проверить в ручном режиме правильность подсчета

З.Ы, формула громоздкая и почти наугад делалась - что может и не вспомню как делал хахаха, Справа от таблицы -  мысли в слух можно удалять.
Изменено: a.i.mershik - 03.04.2018 14:41:29
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#12
03.04.2018 14:53:33
Цитата
a.i.mershik написал:
формула громоздкая и почти наугад делалась - что может и не вспомню как делал хахаха, Справа от таблицы -  мысли в слух можно удалять.
Формула просто огромна!!!!!
 
 
 
#13
03.04.2018 15:01:15
a.i.mershik, все что в диапазоне N9:Q9 до N23:Q23 Вы про это имеете, что можно удалить?
 
 
 
#14
03.04.2018 15:03:55
ymal_qeb, со столбца L до конца можно удалить в формуле не участвует.
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#15
03.04.2018 15:35:25
a.i.mershik, в диапазоне F:F и G:G когда копируешь строку (например я скопировал строку 10 и вставил ее) и ссылка начинает ссылаться не на тот диапазон, можете  можете подсказать есть другой способ обращения к ячейке?
и еще а можно как либо значение которое ставиться напротив Наименования, как либо сделать чтобы значение подставлялось автоматически, (например по слову доставка) сколько оно встречалось на листе?
 
 
 
#16
03.04.2018 15:38:57
ymal_qeb, вы так тяжело пишите, что я не понимаю вообще ничего иногда. это нужно?!
внес изменения в счет "наименований"  
Изменено: a.i.mershik - 03.04.2018 15:44:43
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#17
03.04.2018 15:45:37
Цитата
ymal_qeb написал:
a.i.mershik , в диапазоне F:F и G:G когда копируешь строку (например я скопировал строку 10 и вставил ее) и ссылка начинает ссылаться не на тот диапазон, можете  можете подсказать есть другой способ обращения к ячейке?
по этому пункту, да (a.i.mershik, простите если не всегда точно выражаю свою мысль).
Цитата
a.i.mershik написал:
и еще а можно как либо значение которое ставиться напротив Наименования, как либо сделать чтобы значение подставлялось автоматически, (например по слову доставка) сколько оно встречалось на листе?
а вот этот пункт, это то значение которое вставляется в диапазоне A:A, для уникализации таблиц, можно ли его как то рассчитать автоматически.
 
 
 
#18
03.04.2018 15:46:39
ymal_qeb, выше смотрите
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#19
03.04.2018 16:13:58
a.i.mershik, спасибо большое, Вы говорили проверить расчет в ручном режиме, я проверил все работает без ошибок.
 
 
 
#20
03.04.2018 16:14:26
ymal_qeb, удачи.
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#21
03.04.2018 16:15:52
a.i.mershik, Вам тоже.
 
 
 
#22
04.04.2018 12:00:31
a.i.mershik, можете подсказать еще по одному вопросу, а когда добавляешь столбец, вроде бы ссылается на верный диапазон, но выдает где должны проводиться вычисления выдает 0.
 
 
 
#23
04.04.2018 13:05:35
ymal_qeb, в формуле есть ссылки на столбцы сделанные исходя из начального нахождения данных т.е. если они были в столбце  "С" - а формуле есть часть которая вносилась не ссылкой, а вручную допустим:

&":$C1048576")

т.е. в формуле нужно поменять букву C на другую соответсвющую нахождения данных после добавления столбца

Цитата
=ЕСЛИОШИБКА(((E3*F3)*H3)/СУММПРОИЗВ(ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($E:$E;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":"&ЯЧЕЙКА("адрес";ИНДЕКС(ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($E:$E;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":$e1048576");ПОИСКПОЗ("Доставка";ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($D:$D;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":$d1048576");0)-3;)));ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($F:$F;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":"&ЯЧЕЙКА("адрес";ИНДЕКС(ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($F:$F;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":$f1048576");ПОИСКПОЗ("Доставка";ДВССЫЛ(ЯЧЕЙКА("адрес";ИНДЕКС($D:$D;ПОИСКПОЗ(НАИБОЛЬШИЙ(B$2:B2;1);$B:$B;0)-1))&":$d1048576");0)-3;))))/E3;0)

Изменено: a.i.mershik - 04.04.2018 13:09:19
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#24
04.04.2018 13:09:01
разобрался, a.i.mershik, еще раз спасибо.
 
 
 
Страницы: 1
Наверх