Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск по двум таблицам по разным условиям без макросов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск по двум таблицам по разным условиям без макросов (Формулы/Formulas)
Поиск по двум таблицам по разным условиям без макросов
book Дата: Понедельник, 09.07.2018, 20:28 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!

Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.

Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".

У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).

Понимаю, что быстродействие - это отдельная задача, но все же прошу уважаемое сообщество посмотреть, может кто-то предложит более эффективный способ. В файле - 2 листа: описание задачи и мое решение.

Заранее большое спасибо.
К сообщению приложен файл: 8929116.xlsx(13.6 Kb)


--
С уважением,
Андрей.
 
Ответить
СообщениеДобрый день!

Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.

Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".

У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).

Понимаю, что быстродействие - это отдельная задача, но все же прошу уважаемое сообщество посмотреть, может кто-то предложит более эффективный способ. В файле - 2 листа: описание задачи и мое решение.

Заранее большое спасибо.

Автор - book
Дата добавления - 09.07.2018 в 20:28
_Boroda_ Дата: Понедельник, 09.07.2018, 21:09 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13509
Репутация: 5528 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Пара вариантов
Массивный
Код
=ЕСЛИОШИБКА(ИНДЕКС(Исключения;ПОИСКПОЗ(C3&B3;I$3:I$5&J$3:J$5;);3);ВПР(C3;F$3:G$7;2;))

Обычный
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3&B3=I$3:I$5&J$3:J$5);K$3:K$5);ВПР(C3;F$3:G$7;2;))
К сообщению приложен файл: 8929116_1.xlsx(13.7 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПара вариантов
Массивный
Код
=ЕСЛИОШИБКА(ИНДЕКС(Исключения;ПОИСКПОЗ(C3&B3;I$3:I$5&J$3:J$5;);3);ВПР(C3;F$3:G$7;2;))

Обычный
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3&B3=I$3:I$5&J$3:J$5);K$3:K$5);ВПР(C3;F$3:G$7;2;))

Автор - _Boroda_
Дата добавления - 09.07.2018 в 21:09
book Дата: Понедельник, 09.07.2018, 21:15 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо _Boroda_!
Вижу Вы добавили 2-й вариант. Все работает!
Верно понимаю, что:
если формула короче, то она и считается быстрее?
формула "массива" считается дольше? в чем же ее преимущество?


--
С уважением,
Андрей.


Сообщение отредактировал book - Понедельник, 09.07.2018, 21:26
 
Ответить
СообщениеСпасибо _Boroda_!
Вижу Вы добавили 2-й вариант. Все работает!
Верно понимаю, что:
если формула короче, то она и считается быстрее?
формула "массива" считается дольше? в чем же ее преимущество?

Автор - book
Дата добавления - 09.07.2018 в 21:15
_Boroda_ Дата: Понедельник, 09.07.2018, 22:15 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13509
Репутация: 5528 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не обязательно. На оба вопроса. Все зависит от формулы. Например СУММ((А=а)*(В=в)*С) и СУММ(ЕСЛИ(А=а;ЕСЛИ(В=в;С))) - вторая длинее, но считает быстрее - она последовательно отсекает условия и считает только там, где ИСТИНА. А первая считает всё подряд
Сложение быстрее деления, СУММЕСЛИ быстрее СУММПРОИЗВ, волатильные функции (погуглите) лучше не использовать, по возможности нужно делать так, чтобы не считать одно и то же по несколько раз (как у Вас сначала считается ПОИСКПОЗ, потом или тот же ПОИСКПОЗ, или ВПР), ЕСЛИОШИБКА тоже не очень хорошо - у нее первый аргумент считается всегда (но часто альтернатива ЕСЛИОШИБКА еще хуже), иногда лучше сделать допстолбец, чем писать все в одну формулу - быстрее работать будет. В общем, все более-менее логично, просто немного порассуждать нужно. Хотя обычно объемы не такие уж и большие, поэтому скорости работы формул особо не отличаются. Вот если таких формул много или объемы большие, тогда да


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе обязательно. На оба вопроса. Все зависит от формулы. Например СУММ((А=а)*(В=в)*С) и СУММ(ЕСЛИ(А=а;ЕСЛИ(В=в;С))) - вторая длинее, но считает быстрее - она последовательно отсекает условия и считает только там, где ИСТИНА. А первая считает всё подряд
Сложение быстрее деления, СУММЕСЛИ быстрее СУММПРОИЗВ, волатильные функции (погуглите) лучше не использовать, по возможности нужно делать так, чтобы не считать одно и то же по несколько раз (как у Вас сначала считается ПОИСКПОЗ, потом или тот же ПОИСКПОЗ, или ВПР), ЕСЛИОШИБКА тоже не очень хорошо - у нее первый аргумент считается всегда (но часто альтернатива ЕСЛИОШИБКА еще хуже), иногда лучше сделать допстолбец, чем писать все в одну формулу - быстрее работать будет. В общем, все более-менее логично, просто немного порассуждать нужно. Хотя обычно объемы не такие уж и большие, поэтому скорости работы формул особо не отличаются. Вот если таких формул много или объемы большие, тогда да

Автор - _Boroda_
Дата добавления - 09.07.2018 в 22:15
book Дата: Понедельник, 09.07.2018, 22:31 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вот если таких формул много или объемы большие, тогда да

Наверное, это мой вариант... Уже решил: как только получу нужные мне цифры, и Дб будет равен Кр, буду стараться изучить про быстродействие. Сейчас файл работает очень медленно...
Еще раз спасибо! :)
PS ...а есть способ узнать, какие именно формулы "тормозят" в твоем файле?


--
С уважением,
Андрей.
 
Ответить
Сообщение
Вот если таких формул много или объемы большие, тогда да

Наверное, это мой вариант... Уже решил: как только получу нужные мне цифры, и Дб будет равен Кр, буду стараться изучить про быстродействие. Сейчас файл работает очень медленно...
Еще раз спасибо! :)
PS ...а есть способ узнать, какие именно формулы "тормозят" в твоем файле?

Автор - book
Дата добавления - 09.07.2018 в 22:31
_Boroda_ Дата: Понедельник, 09.07.2018, 22:48 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13509
Репутация: 5528 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Обычно достаточно посмотреть на них и подумать. Если не помогает, то последовательно брать одинаковые формулы и вставлять вместо них значения. Проверять скорость работы. Откатываться обратно и заменять значениями другой блок формул, ...
Или можно здесь в отдельной теме показать. Весь файл, конечно, не влезет, но кусочек с пояснениями типа "Эти формулы тянутся вниз на 100500 строк, а в этом листе на самом деле 200600 строк данных" может помочь


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеОбычно достаточно посмотреть на них и подумать. Если не помогает, то последовательно брать одинаковые формулы и вставлять вместо них значения. Проверять скорость работы. Откатываться обратно и заменять значениями другой блок формул, ...
Или можно здесь в отдельной теме показать. Весь файл, конечно, не влезет, но кусочек с пояснениями типа "Эти формулы тянутся вниз на 100500 строк, а в этом листе на самом деле 200600 строк данных" может помочь

Автор - _Boroda_
Дата добавления - 09.07.2018 в 22:48
book Дата: Понедельник, 09.07.2018, 23:02 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Или можно здесь в отдельной теме показать.

Да, наверное, так и сделаю. А вопрос мой был вызван тем, что на какой-то из тем видел, как автор дал время расчета формул с точностью до секунды. Неужели обычным секундомером мерили ?...


--
С уважением,
Андрей.
 
Ответить
Сообщение
Или можно здесь в отдельной теме показать.

Да, наверное, так и сделаю. А вопрос мой был вызван тем, что на какой-то из тем видел, как автор дал время расчета формул с точностью до секунды. Неужели обычным секундомером мерили ?...

Автор - book
Дата добавления - 09.07.2018 в 23:02
Gustav Дата: Вторник, 10.07.2018, 08:43 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1625
Репутация: 663 ±
Замечаний: 0% ±

начинал с Excel 4.0...
А раздел Google Docs для этого топика по каким соображениям выбран? %)


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Вторник, 10.07.2018, 08:45
 
Ответить
СообщениеА раздел Google Docs для этого топика по каким соображениям выбран? %)

Автор - Gustav
Дата добавления - 10.07.2018 в 08:43
book Дата: Вторник, 10.07.2018, 10:21 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А раздел Google Docs для этого топика по каким соображениям выбран?

Виноват. Торопился, разместил тему в разделе, где было мое последнее сообщение. Не знаю, как перенести.
[moder]Переместил[/moder]


--
С уважением,
Андрей.
 
Ответить
Сообщение
А раздел Google Docs для этого топика по каким соображениям выбран?

Виноват. Торопился, разместил тему в разделе, где было мое последнее сообщение. Не знаю, как перенести.
[moder]Переместил[/moder]

Автор - book
Дата добавления - 10.07.2018 в 10:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск по двум таблицам по разным условиям без макросов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!