Ситуация: Есть таблица со значениями целого типа (столбец А) и процентами (строка 1) по которым надо распределить это целое число, при этом получаемые значения тоже должны быть целыми (пример во вложении). Проблема: если пойти по простому пути (который понятно, что не верный), т.е. использовать формулу округления, то распределение в некоторых случаях правильным не получается. Т.е. если суммировать полученный результат то он не совпадает с исходным числом (столбец АС) Вопрос: есть ли в Excel стандартная функция для решения моей проблемы? Если такой функции нет, то как решить мою проблему?
Ситуация: Есть таблица со значениями целого типа (столбец А) и процентами (строка 1) по которым надо распределить это целое число, при этом получаемые значения тоже должны быть целыми (пример во вложении). Проблема: если пойти по простому пути (который понятно, что не верный), т.е. использовать формулу округления, то распределение в некоторых случаях правильным не получается. Т.е. если суммировать полученный результат то он не совпадает с исходным числом (столбец АС) Вопрос: есть ли в Excel стандартная функция для решения моей проблемы? Если такой функции нет, то как решить мою проблему?abwabw
Да собственно ничего если делать все правильно и с уважением относиться к участникам форумов! В правилах хорошего тона давать крос ссылки на все заведенные вами темы. Это значительно упрощает жизнь участников ведь 60% посетителей всех этих форумов ОДНИ И ТЕЖЕ ЛЮДИ!!!
Цитата
А ещё на sql.ru и excel-vba.ru и что?
Да собственно ничего если делать все правильно и с уважением относиться к участникам форумов! В правилах хорошего тона давать крос ссылки на все заведенные вами темы. Это значительно упрощает жизнь участников ведь 60% посетителей всех этих форумов ОДНИ И ТЕЖЕ ЛЮДИ!!!Poltava
Да ничего, в принципе. Просто так Вы показываете, что Вам наплевать на других участников и печетесь только о своей проблеме. Правда, остальные тоже это понимают, т.к. большая часть завсегдатаев обитает на всех форумах, на которых Вы отметились. Следовательно им тоже становится наплевать на Вас с Вашей проблемой - ведь она Ваша, как ни крути. Не их. Создавая одну тему на нескольких форумах сразу(кросспостинг) Ваше право не давать ссылки, но по правилам хорошего тона делать это не следует, т.к. если на одном из них дадут какое-то решение, то на других форумах участники вправе знать, что оно есть и можно не пытаться идти тем же путем, что экономит время помогающим. И не только им. Выданное решение может оказаться неподходящим для Вас и Вы будете ждать другого. Но на других форумах с большой вероятностью будут предлагать такое же. Вот тут Вы скажете: "Мне это уже предлагали, не то". Помогающему будет жаль своего впустую протраченного на Вас времени. И ладно бы просто потратил и не подошло - Вы уже видели это решение на другом форуме, но не сказали, что оно есть. Если бы Вы сразу дали ссылку - он, возможно, сразу бы искал иное решение, т.к. владел бы информацией по развитию ситуации на дружественных форумах. Как следствие - можете остаться вообще без решения. Так что указывать ссылки или нет - решать Вам.
Да ничего, в принципе. Просто так Вы показываете, что Вам наплевать на других участников и печетесь только о своей проблеме. Правда, остальные тоже это понимают, т.к. большая часть завсегдатаев обитает на всех форумах, на которых Вы отметились. Следовательно им тоже становится наплевать на Вас с Вашей проблемой - ведь она Ваша, как ни крути. Не их. Создавая одну тему на нескольких форумах сразу(кросспостинг) Ваше право не давать ссылки, но по правилам хорошего тона делать это не следует, т.к. если на одном из них дадут какое-то решение, то на других форумах участники вправе знать, что оно есть и можно не пытаться идти тем же путем, что экономит время помогающим. И не только им. Выданное решение может оказаться неподходящим для Вас и Вы будете ждать другого. Но на других форумах с большой вероятностью будут предлагать такое же. Вот тут Вы скажете: "Мне это уже предлагали, не то". Помогающему будет жаль своего впустую протраченного на Вас времени. И ладно бы просто потратил и не подошло - Вы уже видели это решение на другом форуме, но не сказали, что оно есть. Если бы Вы сразу дали ссылку - он, возможно, сразу бы искал иное решение, т.к. владел бы информацией по развитию ситуации на дружественных форумах. Как следствие - можете остаться вообще без решения. Так что указывать ссылки или нет - решать Вам.The_Prist
Errare humanum est, stultum est in errore perseverare
Спасибо за участие. А то я математику уже нашёл (Метод Хэйра-Нимейера (метод Гамильтона), а так же методы "делителей" (Джефферсона–д’Ондта, Вебстера–Сент-Лагюе, и т.д.)). Но алгоритмы там сложно реализуемые без использования VBA.
Спасибо за участие. А то я математику уже нашёл (Метод Хэйра-Нимейера (метод Гамильтона), а так же методы "делителей" (Джефферсона–д’Ондта, Вебстера–Сент-Лагюе, и т.д.)). Но алгоритмы там сложно реализуемые без использования VBA.abwabw
1. Нормально по правилам округления получаем округленные значения во всех ячейках.
2. Для каждой ячейки вычисляем разницу [исходное значение] - [округленное].
3. Смотрим насколько отличается общая сумма по округленным от заданной суммы.
4. Если эта общая разница [заданная]-[округленная] положительна, то добавляем единичку в то значения, у которого разница по конкретной строке [исходное]-[округленное] положительна и максимальна. Затем во второе наибольшее значение, затем в третье и так далее, пока не будет исчерпана вся общая разница.
5. Если эта общая разница [заданная]-[округленная] отрицательна, то отнимаем единичку от того значения, у которого разница по конкретной строке [исходное]-[округленное] отрицательна и минимальна (т.е. наименьшая с учетом знака). Затем из второго наименьшего, затем из третьего и так далее, пока не будет исчерпана вся общая разница.
При этом достигается наименьшее отклонение от исходных значений Решение можно адаптировать под текущую задачу, либо реализовать макросом/UDF
1. Нормально по правилам округления получаем округленные значения во всех ячейках.
2. Для каждой ячейки вычисляем разницу [исходное значение] - [округленное].
3. Смотрим насколько отличается общая сумма по округленным от заданной суммы.
4. Если эта общая разница [заданная]-[округленная] положительна, то добавляем единичку в то значения, у которого разница по конкретной строке [исходное]-[округленное] положительна и максимальна. Затем во второе наибольшее значение, затем в третье и так далее, пока не будет исчерпана вся общая разница.
5. Если эта общая разница [заданная]-[округленная] отрицательна, то отнимаем единичку от того значения, у которого разница по конкретной строке [исходное]-[округленное] отрицательна и минимальна (т.е. наименьшая с учетом знака). Затем из второго наименьшего, затем из третьего и так далее, пока не будет исчерпана вся общая разница.
При этом достигается наименьшее отклонение от исходных значений Решение можно адаптировать под текущую задачу, либо реализовать макросом/UDFMCH
Сообщение отредактировал MCH - Четверг, 24.10.2013, 14:17
Сделал сравнение, разных алгоритмов (см. файл в предыдущем посте) Не смотря на то, что алгоритм предложенный AndreTM (jakim предложил аналогичный) прост в реализации, он дает определенную погрешность и зависит от сортировки исходных данных. Наименьшее квадратичное отклонение дает метод, находящийся на листе "Вар.2", "Вар.1" - дает наименьшее относительное отклонение
Сделал сравнение, разных алгоритмов (см. файл в предыдущем посте) Не смотря на то, что алгоритм предложенный AndreTM (jakim предложил аналогичный) прост в реализации, он дает определенную погрешность и зависит от сортировки исходных данных. Наименьшее квадратичное отклонение дает метод, находящийся на листе "Вар.2", "Вар.1" - дает наименьшее относительное отклонениеMCH
Сообщение отредактировал MCH - Пятница, 25.10.2013, 08:50
С другой стороны, теория говорит, что задача поставлена немного некорректно. Потому что потеря точности вычислений (в данной задаче) при округлении до целых - очень велика. Действительно, проценты (что уже есть сотые доли) заданы с точностью до двух знаков после запятой - соответственно, "точные" вычисления возможны только с учётом четырех знаков. Даже использование округления до двух знаков - приводит СКО в среднем к 0.015. Так что тут всё уже зависит от исходных требований - то ли увеличивать разрядность, выигрывая в скорости/простоте расчёта, то ли усложнять алгоритм, но выигрывать в точности...
Михаил, я это отлично понимаю
С другой стороны, теория говорит, что задача поставлена немного некорректно. Потому что потеря точности вычислений (в данной задаче) при округлении до целых - очень велика. Действительно, проценты (что уже есть сотые доли) заданы с точностью до двух знаков после запятой - соответственно, "точные" вычисления возможны только с учётом четырех знаков. Даже использование округления до двух знаков - приводит СКО в среднем к 0.015. Так что тут всё уже зависит от исходных требований - то ли увеличивать разрядность, выигрывая в скорости/простоте расчёта, то ли усложнять алгоритм, но выигрывать в точности...AndreTM
Друзья, помогите, пожалуйста, доработать "формулу". Похоже, мои знания в Excel еще не настолько велики, чтобы самостоятельно разобраться. Вот файл, который ранее выкладывали здесь – он идеально подходит для решения моих задачи, но есть один момент: необходимо, чтобы количество было кратно определенному числу. Получается, что округление сейчас происходит в диапазоне на "+1", а, нужно например, на "+12". Я добавил столбец "B" в файле, по которому нужно, чтобы числа были кратны.
Заранее Спасибо!
AndreTM,
Друзья, помогите, пожалуйста, доработать "формулу". Похоже, мои знания в Excel еще не настолько велики, чтобы самостоятельно разобраться. Вот файл, который ранее выкладывали здесь – он идеально подходит для решения моих задачи, но есть один момент: необходимо, чтобы количество было кратно определенному числу. Получается, что округление сейчас происходит в диапазоне на "+1", а, нужно например, на "+12". Я добавил столбец "B" в файле, по которому нужно, чтобы числа были кратны.
Единственное, число, которое указано для распределения в столбце "A", должно быть равно сумме чисел, по которым производилось распределение в столбце "EL". Возможно сделать так?
DrMini,
Большое спасибо!
Единственное, число, которое указано для распределения в столбце "A", должно быть равно сумме чисел, по которым производилось распределение в столбце "EL". Возможно сделать так?magos1986
Спасибо большое, но к сожалению, эта формула отменяет принцип, того, что делала предыдущая формула, когда разносила данные в разных пропорциях.
Nic70y,
Спасибо большое, но к сожалению, эта формула отменяет принцип, того, что делала предыдущая формула, когда разносила данные в разных пропорциях.magos1986
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
