Задачка по excel: проверка сложного условия [решили уже]
а почему такие жесткие условия?
единственный вариант - писать вручную формулу для первой строки и макрос который отмасштабирует ее явным образом на все остальные ячейки через сумму квадратов например. Формула будет огромная конечно.
единственный вариант - писать вручную формулу для первой строки и макрос который отмасштабирует ее явным образом на все остальные ячейки через сумму квадратов например. Формула будет огромная конечно.
Если какое-либо условие должно выполняться для каждой строки, то оно же должно выполняться и для суммы по столбцам. Мне так кажется.
мда, и это зампроректор МГУ...
бабруйский, а если там будет три столбца с условием a*b=c для каждой строки, как думаешь - выполнится для суммы?
бабруйский, а если там будет три столбца с условием a*b=c для каждой строки, как думаешь - выполнится для суммы?
А сумма этих выражений будет?
Можешь считать, что я просто дал направление для размышления.
Можешь считать, что я просто дал направление для размышления.
твое "направление" уже было описано топикстартером.
Только там сумма квадратов разностей (a*b-c=0) что более сильное условие, но ты похоже не догнал.
Только там сумма квадратов разностей (a*b-c=0) что более сильное условие, но ты похоже не догнал.
Мне не хочется с тобой спорить:
2. Для каждой строки, между значениями столбцов должно выполняться определенное соотношение (например, значение в последнем столбце равно сумме значений во всех остальных столбцах). Соотношение во всех строках одинаково.Но если для тебя это так важно, то, разумеется, более верное условие это суммировать уже формулы.
а почему такие жесткие условия?Макросы нельзя использовать, потому что некоторые клиенты сидят не под виндой и используют не MS Excel для редактирования файлов.
Чтобы был понятнее контекст - речь идет о продукте, который администрирует составление разного рода отчетов. Пользователи предоставляют данные для отчетов, которые могут быть корявыми. Регулирующие органы, которые являются конечными потребителями этих отчетов, проверяют адекватность данных всякими валидациями. Т.е. "соотношение должно выполняться в каждой строчке" - это не данность, а то что надо проверить.
Задача возникла в рамках подготовки стандартного пакета для одной такой отчетности. Соответственно, пакет должен работать из коробки без всяких дальнейших вмешательств со стороны разработчика (при условии конечно что клиент ниче ломать не будет)
А нельзя просто написать отдельную софтинку, которая будет доставать данные из файла и валидировать их как угодно? Или вам надо, чтобы поставщики данных, заполняя лист Data в опенофисе, сразу видели, где косяки?
А нельзя просто написать отдельную софтинку, которая будет доставать данные из файла и валидировать их как угодно? Или вам надо, чтобы поставщики данных, заполняя лист Data в опенофисе, сразу видели, где косяки?Да, именно этого и хотелось бы: чтобы интерактивно показывало где косяк без дополнительных телодвижений. Если не выйдет, будем расматривать отдельную софтину - это как раз сейчас план Б. Но надежда пока теплится
Через формулы массивов элементарно, в одну ячейку.
Хз куда тут выложить экселевский файл.
Хз куда тут выложить экселевский файл.
зазипуй и выкладывай в аплоад
потому что некоторые клиенты сидят не под виндой и используют не MS Excel дляничего хорошего не выйдет
зазипуй и выкладывай в аплоадвыложу через 1,5 часа.
выложил:
а встроенная в эксель data validation не катит?
выложу через 1,5 часа.Пятерка вам. То что нужно, спасибо!
а встроенная в эксель data validation не катит?Было бы красиво, но есть большие сомнения по поводу работы ее не под MS Excel, и она не распространяется на неограниченное количество ячеек
круто
что-то оно сразу после открытия показывает 999889. даже для двух строк это число показывает.
хотя псследнйи столбец заполнен по формуле - должно быть валидно.
либре офис
хотя псследнйи столбец заполнен по формуле - должно быть валидно.
либре офис
А у меня этот файл просто намертво подвешивает libreoffice calc при открытии. Version: 4.2.5.2 / Win.
видимо в либре оффсе у тебя ниже списка не пустые ячейки, а заполнены какими-то символами. можно наложить еще немного условий, и этого не будет, правда тут уже самим докумекать можно, с работы выкладывать файлы возможность забанена.
После обновления файл таки открылся, но проблема такая же как у 'а.
Немножко поигравшить выяснилось, что LibreOffice обрабатывает формулу
всего лишь как C3^2 вместо C2^2+C3^2.
Баг ли это или невозможность работать с такой формулой - сложно сказать.
Немножко поигравшить выяснилось, что LibreOffice обрабатывает формулу
=SUM($Data.$C$2:$Data.$C$3*$Data.$C$2:$Data.$C$3)
всего лишь как C3^2 вместо C2^2+C3^2.
Баг ли это или невозможность работать с такой формулой - сложно сказать.
нет никаких гарантий что в убогих пародиях на офис функции массивов будут работать, как и просто сложные функции экселя особенно появившиеся в 2007-2010
я же сразу сказал что решения в общем виде не будет, надо писать огромную формулу (макросом естесно, ен руками) которая в явном виде ссылается на все ячейки соответствующих столбцов листа data
я же сразу сказал что решения в общем виде не будет, надо писать огромную формулу (макросом естесно, ен руками) которая в явном виде ссылается на все ячейки соответствующих столбцов листа data
про "жесткость условий" я больше имел в виду, почему на валидационном листе можно только одну ячейку использовать, без промежуточных вспомогательных
нет никаких гарантий что в убогих пародиях на офис функции массивов будут работатьс этим не поспоришь.
Оставить комментарий
parazit-demon
Итак, дано:1. Есть xls с двумя листами: Data и Validation
На листе Data будет располагаться пользовательская таблица, в которой заранее известное число столбцов, но неизвестное число строк (например, данные о финансовых активах). Окончание данных можно определить по пустым значениям во всей строке
2. Для каждой строки, между значениями столбцов должно выполняться определенное соотношение (например, значение в последнем столбце равно сумме значений во всех остальных столбцах). Соотношение во всех строках одинаково.
3. Лист Data заполняется внешним источником, который не всегда эти соотношения соблюдает
4. Задача в том, чтобы на листе Validation проверить, выполняется ли это соотношение для всех строк сразу.
Условия:
1. Можно использовать только встроенные формулы. Пользовательские функции и макросы не прокатят, так как их иногда блокируют.
2. Формула должна быть обязательно в одной ячейке. Нельзя пользоваться вспомогательными ячейками для промежуточных результатов
3. Нельзя изменять лист Data
4. Формула вводится до заполнения листа Data данными, и должна работать при любом количестве строк, которые пользователь туда ввел.
Дополнительное условие:
5. Очень желательно, чтобы решение было масштабируемым. Таких валидаций надо сделать очень много, все они разные, но описаны в стандартизированной форме (эта форма очень похожа на эксельную формулу, но только для одной строки). Идея в том, чтобы алгоритмически подавая на вход это стандартное описание, получать на выходе эксельную формулу с валидацией.
Пока каменный цветок не выходит. Самое лучшее, что придумалось - это сравнивать сумму квадратов разностей левой и правой части равенства с нулем, представив сумму квадратов разностей комбинащией функций SUMSQR и SUMPRODUCT. Но дополнительное условие не выполняется - раскладывать элементы соотношения в квадрат разности - гемор тот еще. Кроме того, это сработает если в отношении используются только +,-,* (что впрочем 99% соотношений точно покроет).
Есть ли у знатоков более элегантные версии решения?