Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Помогите сократить формулу с функцией ВПР
 
#1
28.10.2013 18:20:18
Несколько вопросов:
1.Подбираю функцией ВПР значение из таблицы с 3-мя колонками значений, Вот что получилось:
=ЕСЛИ(ЕОШИБКА(ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!A3:B102"  ;)  ;2;ЛОЖЬ))=ИСТИНА;0;ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!A3:B102"  ;)  ;2;ЛОЖЬ))+ЕСЛИ(ЕОШИБКА(ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!D3:E102"  ;)  ;2;ЛОЖЬ))=ИСТИНА;0;ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!D3:E102"  ;)  ;2;ЛОЖЬ))+ЕСЛИ(ЕОШИБКА(ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!G3:H102"  ;)  ;2;ЛОЖЬ))=ИСТИНА;0;ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!G3:H102"  ;)  ;2;ЛОЖЬ))+ЕСЛИ(ЕОШИБКА(ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!J3:K102"  ;)  ;2;ЛОЖЬ))=ИСТИНА;0;ВПР(A4;ДВССЫЛ("'"&$A$1&"'"&"!J3:K102"  ;)  ;2;ЛОЖЬ))
Фактически ищу в каждой колонке, если не нахожу ставлю ноль и потом все складываю.
Можно ли как то это упростить?

нужна именно формула

Вопросы удалил. Один вопрос - одна тема. [МОДЕРАТОР]
 
 
 
#2
28.10.2013 18:38:27
Не вникая... по п.2 так-ли уж необходимо МАКРОСОМ подставлять формулу? Может все расчеты в макросе прописать?
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
28.10.2013 18:44:15
формула массива

=СУММ(ЕСЛИОШИБКА((ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A4)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25");0))
 
 
 
#4
28.10.2013 19:55:19
В макросе не могу, надо в ячейке.
Не меняя формулу, можно ее как-то вставить макросом?
 
 
 
#5
28.10.2013 20:24:12
Цитата
В макросе не могу, надо в ячейке.
Почему?
 
 
 
#6
28.10.2013 21:36:24
Поиск значения производится на разных листах в зависимости от выбираемого из списка значения в другой ячейке, при этом сам поиск тоже разный, зависит от типа позиции.
Это расчет цены мебели. На примере:
В отдельной ячейке выбор модели кухни, от неё зависит лист с которого берутся цены на основные элементы
Есть таблица для заполнения, при нажатии на строку открывается форма в которой выбирают тип элемента: ящик в сборе, каркас, фасад и тд. В зависимости от сделанного выбора, в соседнюю ячейку подставляется формула поиска цены и открывается лист для выбора элемента, при нажатии на который выбранное значение подставляется в таблицу.
На этом этапе можно было бы сразу подставить цену, но она должна меняется при смене модели, так же планируется пересчёт при выборе типа цены (розница, опт) - а эти цены на разных листах. Листы с разными типами элементов имеют разную структуру. Ну в общем как то так. При этом для визуального контроля хочется видеть формулу, что бы видеть путь, от куда взялось значение.
Немного сумбурно, но там на самом деле много нюансов.
Итак, можно ли упростить формулу, и можно ли ее вставить из макроса?
Изменено: alexnf - 28.10.2013 21:40:58 (дополнил)
 
 
 
#7
29.10.2013 00:07:42
А  что мешает записать макрорекордером вставку формулы, что предложил Футурама3000?
У меня вот получилось:
Код
    ActiveCell.FormulaArray = _
    "=SUM(IFERROR((INDIRECT(""'""&R1C1&""'""&""!A1:H25"")=RC[-1])*INDIRECT(""'""&R1C1&""'""&""!b1:i25""),0))"
SkyPro
 
 
 
#8
29.10.2013 10:44:13
Не могу вставить такую длинную формулу, получается только:
ActiveCell.FormulaArray = _
"=IF(ISERROR(VLOOKUP(RC[-6],INDIRECT(""'""&R6C4&""'""&""!A3:B102""),2,FALSE))=TRUE,0,VLOOKUP(RC[-6],INDIRECT(""'""&R6C4&""'""&""!A3:B102""),2,FALSE))"
Если больше, то ошибка
Но это только поиск по диапазону A3:B102 надо еще прибавить значение из диапазонов D3:E102, G3:H102, J3:K102
Может можно это короче описать другими формулами?
Нужно найти значение цены по названию в 4-х таблицах, каждая из 2-х столбцов (название и цена)
 
 
 
#9
29.10.2013 11:22:12
alexnf!
А у Вашего файла есть потенциальный преемник? :D
Однако, незавидная участь его ждет...
"Разборки" с Вашей формулой ему без "бутылки" лучше не начинать!
Самый оптимальный путь - написать (заказать) макрос.
 
 
 
#10
29.10.2013 11:40:32
Не понимаю, почему только макрос.
Обычная функция ВПР, только поискать надо в 4-х таблицах, а не в одной. Название листа, в котором эти таблицы, берется из другой ячейки. Вот и вся суть формулы.
Изменено: alexnf - 29.10.2013 11:40:53
 
 
 
#11
29.10.2013 11:48:28
предложенная формула от futurama3000 работает со всем диапазоном сразу и не нужно её делить на куски как вы это делаете. у него A1:H25 то есть вся таблица из примера, у вас A3:B102 (сообщение #8.) .
в примере таблица с первой строки в формуле диапазон с третей - с чем это связано.
Изменено: V - 29.10.2013 11:50:28
 
 
 
#12
29.10.2013 12:15:16
A3:B102 это мой диапазон первой таблицы в рабочем файле, A1:H25 это все 4 таблицы в примере.
Формула от futurama3000 в моем примере дает #ИМЯ?
если честно, то я ее не понял
Код
=СУММ(ЕСЛИОШИБКА((ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")  =A4)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25")  ;0))

Вот это:
Код
ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")
дает ссылку на массив на указанном листе, а A4 искомое в этом массиве значение
Выражение
Код
ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A4
всегда дает ЛОЖЬ
А умножение его на
Код
ДВССЫЛ("'"&$A$1&"'"&"!b1:i25")
дает 0 (не понимаю почему тут диапазон смещен вправо на одну ячейку b1:i25)

что за функция ЕСЛИОШИБКА - если имелась ввиду ЕОШИБКА - то мало аргументов

эксель у меня 2003
Изменено: alexnf - 29.10.2013 12:22:04
 
 
 
#13
29.10.2013 12:21:48
Цитата
не понимаю почему тут диапазон смещен вправо на одну ячейку
по тому что у вас искомое значение в 1 столбце а необходимый результат во 2 относительно первого.
с помощью F9 и выделения пройдитесь по ф-циям и проверьте вашу формулу - где именно получается #имя. вот пример как это делается.
П.С. надеюсь не забыли что формула массива.
Изменено: V - 29.10.2013 12:24:10
 
 
 
#14
29.10.2013 12:25:02
Имя получается из за ЕСЛИОШИБКА
 
 
 
#15
29.10.2013 12:29:23
ну да в 2003 этой ф-ции нет. для вас вариант ЕСЛИ(ЕОЩИБКА(формула);0;формула)
 
 
 
#16
29.10.2013 12:34:29
Цитата
Вот и вся суть формулы.
Если вы все САМИ прекрасно понимаете, то почему же сами не сделаете или не довернете?!.
Уважаемая Матрена Батьковна высказала свое предупреждение, так стоит задуматься и, как минимум, привести таблицы в божеский, простите - удобоваримый для его величества XL'я, вид. Попробуете?.. ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#17
29.10.2013 12:46:35
V, правильно ли я Вас понял:
{=СУММ(ЕСЛИ(ЕОШИБКА(ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A5)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25");0;(ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A5)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25")))}
результат 0
 
 
 
#18
29.10.2013 13:23:29
почти. проверьте скобки для ЕОШИБКА. (я вас в формуле не хватает).
интересно откуда 0 при такой записи и вводе формулы как формулы массива (CTRL+Shift+Enter) должно быть #ЗНАЧ
Изменено: V - 29.10.2013 13:25:33
 
 
 
#19
29.10.2013 13:34:45
V, спасибо Вам за помощь.
Вот рабочий вариант для примера:
Код
{=СУММ(ЕСЛИ(ЕОШИБКА((ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A4)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25"));0;(ДВССЫЛ("'"&$A$1&"'"&"!A1:H25")=A4)*ДВССЫЛ("'"&$A$1&"'"&"!b1:i25")))}

Сейчас по пытаюсь вставить из макроса в рабочем файле.
 
 
 
#20
29.10.2013 14:38:20
А вот формула для подстановки в макрос:

Искомое значение на 6 ячеек левее RC[-6]
Диапазон поиска A3:K102 на листе указанном в ячейке R6C4
Код
ActiveCell.FormulaArray = _
"=SUM(IF(ISERROR((INDIRECT(""'""&R6C4&""'""&""!A3:K102"")=RC[-6])*INDIRECT(""'""&R6C4&""'""&""!B3:L102"")),0,(INDIRECT(""'""&R6C4&""'""&""!A3:K102"")=RC[-6])*INDIRECT(""'""&R6C4&""'""&""!B3:L102"")))"

Все работает. Спасибо всем откликнувшимся.
Изменено: alexnf - 29.10.2013 14:40:35
 
 
 
Страницы: 1
Наверх