Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Как выбрать значение по 2-м критериям?
 
#1
30.06.2011 15:09:01
Уважаемые, подскажите, как формулами по 2-м критериям в нужную ячейку подставить значение, выбранное из другого листа?    
(см.пример)  
в листе mx в выделенную ячейку надо подставить цену, выбранную из листа price.  
выбирается цена по 2-м критериям: поставщик и товар (просто у одного поставщика может быть несколько товаров)  
 
...или как-то это макросом проще сделать? Голову уже сломал((( ВПР-ом не получается, или надо какую-то мегаформулу изобретать....  
кстати, сам лист mx практически полностью заполняется макросом.
 
 
 
#2
30.06.2011 15:14:40
Вот такая формула:  
=СУММПРОИЗВ((D3=price!$A$4:$A$30)*(E3=price!$B$3:$H$3)*price!$B$4:$H$30)  
см. файл
Редко но метко ...
 
 
 
#3
30.06.2011 15:25:45
Уууууух... низкий поклон вам!  
Благодарствую! буду разбираться в этой чудо-формуле)))  
 
ЗЫ: а макросами оно не проще будет?  
.. как я понимаю механизм работы таков: находим по первому критерию номер строки, затем по второму критерию номер столбца, затем в переменную выдергиваем значение найденной ячейки. Или как-то проще можно реализовать?
 
 
 
#4
30.06.2011 15:28:32
{quote}{login=GIG_ant}{date=30.06.2011 03:14}{thema=}{post}Вот такая формула:  
=СУММПРОИЗВ((D3=price!$A$4:$A$30)*(E3=price!$B$3:$H$3)*price!$B$4:$H$30)  
см. файл{/post}{/quote}  
 
вопрос: а вот это "$" оно принципиально?
 
 
 
#5
30.06.2011 15:28:54
{quote}{login=Денис}{date=30.06.2011 03:25}{thema=}{post}Уууууух... низкий поклон вам!  
Благодарствую! буду разбираться в этой чудо-формуле)))  
 
ЗЫ: а макросами оно не проще будет?  
.. как я понимаю механизм работы таков: находим по первому критерию номер строки, затем по второму критерию номер столбца, затем в переменную выдергиваем значение найденной ячейки. Или как-то проще можно реализовать?{/post}{/quote}  
 
Можно и макросами, и еще пару вариантов формулами, все зависит от того что вам больше подходит. Если количество строк в документе умеренное - подойдут формулы, если строк десятки тысяч  - быстрее будет макрос. Выбирать в конечном итоге ВАМ.
Редко но метко ...
 
 
 
#6
30.06.2011 15:30:26
{quote}{login=Денис}{date=30.06.2011 03:28}{thema=Re: }{post}{quote}{login=GIG_ant}{date=30.06.2011 03:14}{thema=}{post}Вот такая формула:  
=СУММПРОИЗВ((D3=price!$A$4:$A$30)*(E3=price!$B$3:$H$3)*price!$B$4:$H$30)  
см. файл{/post}{/quote}  
 
вопрос: а вот это "$" оно принципиально?{/post}{/quote}  
 
Принципиально - если вы хотите протягивать формулу вниз. Без знака $ в формуле сдвинутся диапазоны поиска.
Редко но метко ...
 
 
 
#7
30.06.2011 15:39:10
Понял.  
 
строк может быть 2-3 тысячи. вот и задумываюсь о макросе...  
 
...или формулу Вашу макросом при заполнении этого листа в ячейку втыкать, а потом из нее значение брать и в ту же ячейку записывать))) как вариант, если ничего лучше не придумаю))
 
 
 
#8
30.06.2011 16:00:23
если офис версии 2007 или 2010 попробуйте суммеслимн(), как и суммпроизв() корректно отработает, только если на листе price связки материал - поставщик не дублируются
 
 
 
#9
30.06.2011 16:01:03
{quote}{login=Денис}{date=30.06.2011 03:39}{thema=}{post}Понял.  
 
строк может быть 2-3 тысячи. вот и задумываюсь о макросе...  
 
...или формулу Вашу макросом при заполнении этого листа в ячейку втыкать, а потом из нее значение брать и в ту же ячейку записывать))) как вариант, если ничего лучше не придумаю)){/post}{/quote}  
 
Для начала побробуйте на своих 2-3 тысячах строк формулу, а уж если будет недостаточно быстро считать (в чем я лично сомневаюсь), тогда пишите, сообразим макрос.
Редко но метко ...
 
 
 
#10
30.06.2011 16:01:51
{quote}{login=Денис}{date=30.06.2011 03:09}{thema=Как выбрать значение по 2-м критериям?}{post}...ВПР-ом не получается, или надо какую-то мегаформулу изобретать....{/post}{/quote}  
на примере файла от GIG_ant (там товар приведен в соответствие):  
=ВПР(D3;price!$A$4:$H$30;ПОИСКПОЗ(E3;price!$B$3:$H$3;0)+1;0)  
Может выдавать ошибку #Н/Д в случае отсутствия в таблице данных по фирме/товару, в отличии от СУММПРОИЗВ/СУММ, которые вернут ноль
 
 
 
#11
30.06.2011 16:02:38
хотя для действующего варианта ана не подойдет
 
 
 
#12
30.06.2011 16:05:13
{quote}{login=tagron}{date=30.06.2011 04:00}{thema=}{post}если офис версии 2007 или 2010 попробуйте суммеслимн(), как и суммпроизв() корректно отработает, только если на листе price связки материал - поставщик не дублируются{/post}{/quote}  
 
По моему мнению ваше предложение не подойдет для данного случая. Цитата из справки Ексель:  
"В отличие от аргументов диапазона и условия в функции СУММЕСЛИ, в функции СУММЕСЛИМН каждый диапазон_условия обязательно должен иметь то же количество строк и столбцов, что и диапазон_суммирования"  
 
Так как колличество фирм может быть больше или меньше чем товаров функция СУММЕСЛИМН тут не подойдет.
Редко но метко ...
 
 
 
#13
30.06.2011 16:52:11
Позволю себе обратить внимание Денис, на следующие замечание от MCH, в котором сказано: "на примере файла от GIG_ant (там товар приведен в соответствие)......".  
Может лучше сделать выбор поставщика и товара списком, дабы исключить ошибки при вводе товара. А то бывает так, что формула правильная, а блин какой-нибудь пробел или буковку пропустишь и уже капец.
 
 
 
#14
01.07.2011 08:20:11
<=ВПР... Может выдавать ошибку #Н/Д...>  
Но ВПР() намного легче, чем СУММПРОИЗВ(). На больших таблицах разница во времени пересчета будет заметна.
 
 
 
#15
01.07.2011 16:52:07
Спасибо вам, уважаемые.  
 
Понял. разобрался. Все варианты формул работают.  
 
Формула ошибок выдавать не должна: лист "mx" заполняется программно, наименования поставщиков и товаров синхронятся при каждом вводе новой поставки - они не могут быть в листах несовпадающими. тут затык был только в том, чтобы еще цену подставлять.    
...думал сначала ее программно из "price" выдергивать - но не смог разобраться как ее находить, решил проще будет формулу в ячейку вставлять, а затем из этой ячейки значение уже в переменную выдергивать (пошел по обходному пути)
 
 
 
#16
01.07.2011 16:59:12
... лист "mx" формируется не сразу одним махом, он потихоньку в течении дня набивается данными - по мере прихода поставок. а вечером из этого листа уже всем скопом данные в 1С выдергиваются и он при закрытии смены очищается.  
он (этот лист)набивается информацией построчно - когда приемщик оформляет приход и принимает товар.  
..так что, по идее тормозить не должно на формулах - она считается для конкретной одной ячейки каждый раз. (просто, повторюсь, не смог реализовать программное выдергивание цены).
 
 
 
Страницы: 1
Наверх