Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.
Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".
У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).
Понимаю, что быстродействие - это отдельная задача, но все же прошу уважаемое сообщество посмотреть, может кто-то предложит более эффективный способ. В файле - 2 листа: описание задачи и мое решение.
Заранее большое спасибо.
Добрый день!
Есть «Реестр» операций, в котором наряду с другими данными есть три текстовых столбца «Контрагент», «Статья Бюджета» и «Группа Статей Бюджета». Надо заполнить текстовый столбец «Группа Статей Бюджета». Есть две таблицы соответствия этих параметров: «Исключения» и «Обычная зависимость». Сложность в том, что при разных комбинациях «Контрагент» - «Статья» надо искать значения «Группы» в разных таблицах соответствия. Задачу надо решить без макросов.
Коротко алгоритм поиска выглядит так: Если текущая пара "Контрагент-Статья Бюджета" есть в таблице "Исключения", то Группа выбирается из таблицы "Исключения"; иначе - Группа выбирается из таблицы "Обычная зависимость".
У меня есть решение задачи, но я не уверен, что оно хорошее. С точки зрения быстродействия. У меня в этом файле около 6000 строк в «Реестре» и еще много других формул массива и формул СУММЕСЛИМН и СУММПРОИЗВ. Обсчет файла занимает около минуты. (Excel 2016).
Понимаю, что быстродействие - это отдельная задача, но все же прошу уважаемое сообщество посмотреть, может кто-то предложит более эффективный способ. В файле - 2 листа: описание задачи и мое решение.
Спасибо _Boroda_! Вижу Вы добавили 2-й вариант. Все работает! Верно понимаю, что: если формула короче, то она и считается быстрее? формула "массива" считается дольше? в чем же ее преимущество?
Спасибо _Boroda_! Вижу Вы добавили 2-й вариант. Все работает! Верно понимаю, что: если формула короче, то она и считается быстрее? формула "массива" считается дольше? в чем же ее преимущество?book
-- С уважением, Андрей.
Сообщение отредактировал book - Понедельник, 09.07.2018, 21:26
Не обязательно. На оба вопроса. Все зависит от формулы. Например СУММ((А=а)*(В=в)*С) и СУММ(ЕСЛИ(А=а;ЕСЛИ(В=в;С))) - вторая длинее, но считает быстрее - она последовательно отсекает условия и считает только там, где ИСТИНА. А первая считает всё подряд Сложение быстрее деления, СУММЕСЛИ быстрее СУММПРОИЗВ, волатильные функции (погуглите) лучше не использовать, по возможности нужно делать так, чтобы не считать одно и то же по несколько раз (как у Вас сначала считается ПОИСКПОЗ, потом или тот же ПОИСКПОЗ, или ВПР), ЕСЛИОШИБКА тоже не очень хорошо - у нее первый аргумент считается всегда (но часто альтернатива ЕСЛИОШИБКА еще хуже), иногда лучше сделать допстолбец, чем писать все в одну формулу - быстрее работать будет. В общем, все более-менее логично, просто немного порассуждать нужно. Хотя обычно объемы не такие уж и большие, поэтому скорости работы формул особо не отличаются. Вот если таких формул много или объемы большие, тогда да
Не обязательно. На оба вопроса. Все зависит от формулы. Например СУММ((А=а)*(В=в)*С) и СУММ(ЕСЛИ(А=а;ЕСЛИ(В=в;С))) - вторая длинее, но считает быстрее - она последовательно отсекает условия и считает только там, где ИСТИНА. А первая считает всё подряд Сложение быстрее деления, СУММЕСЛИ быстрее СУММПРОИЗВ, волатильные функции (погуглите) лучше не использовать, по возможности нужно делать так, чтобы не считать одно и то же по несколько раз (как у Вас сначала считается ПОИСКПОЗ, потом или тот же ПОИСКПОЗ, или ВПР), ЕСЛИОШИБКА тоже не очень хорошо - у нее первый аргумент считается всегда (но часто альтернатива ЕСЛИОШИБКА еще хуже), иногда лучше сделать допстолбец, чем писать все в одну формулу - быстрее работать будет. В общем, все более-менее логично, просто немного порассуждать нужно. Хотя обычно объемы не такие уж и большие, поэтому скорости работы формул особо не отличаются. Вот если таких формул много или объемы большие, тогда да_Boroda_
Вот если таких формул много или объемы большие, тогда да
Наверное, это мой вариант... Уже решил: как только получу нужные мне цифры, и Дб будет равен Кр, буду стараться изучить про быстродействие. Сейчас файл работает очень медленно... Еще раз спасибо! :) PS ...а есть способ узнать, какие именно формулы "тормозят" в твоем файле?
Вот если таких формул много или объемы большие, тогда да
Наверное, это мой вариант... Уже решил: как только получу нужные мне цифры, и Дб будет равен Кр, буду стараться изучить про быстродействие. Сейчас файл работает очень медленно... Еще раз спасибо! :) PS ...а есть способ узнать, какие именно формулы "тормозят" в твоем файле?book
Обычно достаточно посмотреть на них и подумать. Если не помогает, то последовательно брать одинаковые формулы и вставлять вместо них значения. Проверять скорость работы. Откатываться обратно и заменять значениями другой блок формул, ... Или можно здесь в отдельной теме показать. Весь файл, конечно, не влезет, но кусочек с пояснениями типа "Эти формулы тянутся вниз на 100500 строк, а в этом листе на самом деле 200600 строк данных" может помочь
Обычно достаточно посмотреть на них и подумать. Если не помогает, то последовательно брать одинаковые формулы и вставлять вместо них значения. Проверять скорость работы. Откатываться обратно и заменять значениями другой блок формул, ... Или можно здесь в отдельной теме показать. Весь файл, конечно, не влезет, но кусочек с пояснениями типа "Эти формулы тянутся вниз на 100500 строк, а в этом листе на самом деле 200600 строк данных" может помочь_Boroda_
Да, наверное, так и сделаю. А вопрос мой был вызван тем, что на какой-то из тем видел, как автор дал время расчета формул с точностью до секунды. Неужели обычным секундомером мерили ?...
Да, наверное, так и сделаю. А вопрос мой был вызван тем, что на какой-то из тем видел, как автор дал время расчета формул с точностью до секунды. Неужели обычным секундомером мерили ?...book
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
