MS Excel - решение практических задач

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

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[rusk1234]

Цитата: Сообщение от ДЫСЫпы Я правильно понял, что отработанные дни можно вставлять куда угодно, лишь бы их количество не превышало заданное и суммы по строкам и столбцам совпадали и не важно, что в один день человек собирал 1 кг, а на другой допустим тонну?

Совершенно верно!
В принципе, неважно какими средствами будет решена данная задача, например - Access или программа. Главное - чтобы на выходе можно было бы распечатать результат и естественно входящие данные можно было бы редактировать (количество людей, дней в месяце, т.е. количество столбцов и т.п.)

[ДЫСЫпы]

Вообщем получается вот что..
Пример: Сдача картофеля
1. Жмем на кнопку "Раб дни"
Не совсем красиво получилось случайное заполнение календаря, но этим пока можно пренебречь, это решаемо
2. Жмем на кнопку "Заполнить"
Здесь засада
Пробовал заполнять перебором, получается ерунда, с шагом например 0,5, очень долго и разница в суммах более 3000. Отказался.
Сейчас заполняет строки пропорционально каждому человеку который работал в данный день и если сумма столбцам ОК, то по строкам получается полная чушь.
Никак не могу сообразить как увязать с отработанным человеком за месяц. Как вывести этот коэффициент?.
Будут идеи, пишите. Завтра продолжу.
Задачка весьма интересная

[andrey_k]

Цитата: Сообщение от ДЫСЫпы ..Задачка весьма интересная

Это почти классическая задача про распределение ресурсов - смотрите экономическую математику (или математику для экономистов). Есть эвристические алгоритмы ее решения или (что обычно легче автоматизируется) симплекс-методом, т.е. составлением матрицы уравнений с ограничениями (построение симплекса) и решением.

[andrey_k]

Цитата: Сообщение от rusk1234 Совершенно верно! В принципе, неважно какими средствами будет решена данная задача, например - Access или программа. Главное - чтобы на выходе можно было бы распечатать результат и естественно входящие данные можно было бы редактировать (количество людей, дней в месяце, т.е. количество столбцов и т.п.)

Есть ли доп.информация кроме сводной по отработке (сумме рабочих дней) и выработке за день и по работнику за месяц? Например, рабочие дни по каждому в течение месяца - намного сокращает количество вариантов - сейчас прогнал больше 100000 итераций (больше 4 часов прямого счета на двух ядрах с HT), и похоже, еще около того будет.

[ДЫСЫпы]

Все. Больше не осиляю, хотя рассуждал вроде логично))
Сдача картофеля
Теперь глядя на результат никакой логики и не видно))

Есть идея, сгонять итоговую сумму(68000) из левой верхней ячейки к нижней правой предварительно отсортировав по убыванию Итог за месяц. Как это реализовать пока не знаю, попробую завтра.
Или
Оставить все как есть, зациклить и разгонять отрицательные суммы, до их уничтожения.
Подумаю над этим.
Пока бросаю, потому что тупить начал

Цитата: Сообщение от andrey_k смотрите экономическую математику (или математику для экономистов)

Есть анек бородатый:
- Слышь, где Африка?
- норд-ост сэр
- Чего? Ты мне рукой покажи

Это шутка конечно, но однажды мне похожую задачу приходилось решать в 1С (себестоимость подгоняли по чудному), при этом со мной был бух (80 LVL) который доходчиво карандашом на листочке нарисовал расчет, мне осталось тока закодировать сие.

[obmanthik]

помогите, пожалуйста. забиваю произведения с процентами 0,42, 0,67 и т.д, в ячейках ввожу числовое представление, Число разрядов 0 получаю целые числа, но при суммирование этих ячеек появляеться разница в одино число при пересчете на калькуляторе.(разницу обнаружил при просмотре проф. программы)

[Chet]

Цитата: Сообщение от obmanthik забиваю произведения с процентами

умножаете числа на проценты (коэффициенты)?

Цитата: Сообщение от obmanthik числовое представление, Число разрядов 0 получаю целые числа

отображаются целые, а не получаются как результат; для округления используйте ф-ции ОКРУГЛ

Цитата: Сообщение от obmanthik разница в одино число

в одно число -

Цитата: Сообщение от obmanthik при пересчете на калькуляторе

наши бухгалтера тож проверяют расчеты из Эксела на калькуляторе

[rusk1234]

Цитата: Сообщение от andrey_k Есть ли доп.информация кроме сводной по отработке (сумме рабочих дней) и выработке за день и по работнику за месяц? Например, рабочие дни по каждому в течение месяца - намного сокращает количество вариантов - сейчас прогнал больше 100000 итераций (больше 4 часов прямого счета на двух ядрах с HT), и похоже, еще около того будет.

Мне задачу поставили именно в таком виде, т.к. строгого учета не было. В итоге рабочие дни раскидывал на глазок примерно равномерно, т.к. сдвиг рабочих дней в начало или конец месяца сильно влияют на пропорции производительности труда. Затем по строкам-суммам месяца выводил коэффициент для каждой строки, выбирал строку с максимальным количеством рабочих дней за базовую и производил заполнение столбцов исспользуя рассчитанные строковые коэффициенты. В итоге всетаки получается расхождение полученных построчных сумм от заданных сумм построкам. Далее производил ручной подгон данных ячеек в 2-3 столбцах. В итоге расчет одного месяца занимал 2-3 часа.

[andrey_k]

Цитата: Сообщение от rusk1234 Большая просьба! Скинуть мне готовое решение задачи с LibreOffice, если возможно

Вот что получилось за 2 дня прогонов в LibreOffice (Excel 2010 категорически не хочет заниматься этой задачей - причины уже названы).
Сходимость не полная - у некоторых людей не удалось определить их рабочие дни.
Примерное чистое время просчета - около 8 часов, около 300 тыс.итераций.
Возможно, если начать заново, решит быстрее (по ходу дела уточнял параметры Решателя).
120702 Нова Сдача картофеля.xlsx

[ДЫСЫпы]

Цитата: Сообщение от rusk1234 В итоге рабочие дни раскидывал на глазок

В этом весь корень зла)) В том смысле, что был бы диапазон без пустот, решение минута, две.
Просто втупую увеличиваем ячейки на единицу.
Пример

Но как только появляются дыры, начинается бег с препятствиями.
Бог с ними с коэфиициентами, если упростить задачу до вида

и подобрать числа для ячеек=1, все равно не могу найти решения.
А оно есть!

[andrey_k]

Цитата: Сообщение от ДЫСЫпы ... и подобрать числа для ячеек=1, все равно не могу найти решения. А оно есть!

Конечно, есть. По теории это матрица из 14 строк и 32 столбца, не считая уравнений с ограничениями. В данном примере переменных заведомо 28, т.к. часть дней уже указана с нулевой выработкой (я их поэтому в расчете не учитывал), а уравнений меньше, т.е. задача имеет много решений. Поскольку это реальная задача (есть реальный смысл), то хотя бы одно решение есть точно, поэтому есть куда стремиться
Очень сильно усложняет неизвестность с рабочими днями - из-за это сложность на несколько порядков возрастает.

[ДЫСЫпы]

andrey_k, у Вас по первой строке рабочих дней 15, а заполнено 17.
Это ошибка или я чего то не понял?

Добавлено через 22 минуты
Запросил помощи в разделе программирование, голову сам себе уже настолько заморочил, что она не соображает))

[andrey_k]

Цитата: Сообщение от ДЫСЫпы andrey_k, у Вас по первой строке рабочих дней 15, а заполнено 17. Это ошибка или я чего то не понял?

Я об этом написал - это одна из недооптимизаций - по нескольким людям не удалось корректно определить их рабочие дни

[ДЫСЫпы]

Цитата: Сообщение от andrey_k по нескольким людям не удалось корректно определить их рабочие дни

Ну тогда задача получается увы нерешенной Или решенной неверно))

Цитата: Сообщение от andrey_k По теории это матрица из 14 строк и 32 столбца, не считая уравнений с ограничениями

Мне бы кусок кода, как это применить, а то у меня мысль только в одном направлении работает

Цитата: Сообщение от ДЫСЫпы зациклить и разгонять отрицательные суммы, до их уничтожения.

Да и здесь полной ясности нет, одни только предположения

[andrey_k]

Цитата: Сообщение от ДЫСЫпы Ну тогда задача получается увы нерешенной Или решенной неверно)) Мне бы кусок кода, как это применить, а то у меня мысль только в одном направлении работает

Я уже говорил - принципиально такие задачи или сводятся к линейным - тогда решаются симплекс-методом (подробнее могу завтра рассказать), или констатируется их нелинейность - тогда разными эвристическими методами, фактически - случайным перебором вариантов с учетом ограничений задачи и заранее (обычно - в процессе решения) уточнением этих ограничений, чтобы сузить диапазон поиска. Во втором случае обычно ограничиваются каким-то заданным диапазоном точности и количеством итераций.