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

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

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

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

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

[ДЫСЫпы]

Цитата: Сообщение от andrey_k подробнее могу завтра рассказать

Если можно с примерами, если не кода, то на пальцах))

Цитата: Сообщение от andrey_k азными эвристическими методами, фактически - случайным перебором вариантов

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

Цитата: Сообщение от andrey_k чтобы сузить диапазон поиска.

Вот над этим и думаю, реализовать пока не могу.
Пример

После топорного заполнения, диапазон поиска сузился до строк с отрицательными значениями.
Сижу думаю, будет ли толк, если обрабатывать теперь только эти строки?

[obmanthik]

Цитата: Сообщение от Chet умножаете числа на проценты (коэффициенты)? даже не использую функцию с процентами,допустим 68%, в формулу просто ввожу =произвед(А8;0,68) отображаются целые, а не получаются как результат; для округления используйте ф-ции ОКРУГЛ формула как =произвед(А;В), пытаюсь округлить, выводиться ошибка... в одно число - должно получиться при сложении челых чисел, получаемых в результате формулы с произведением допустим 5698, а получается 5697 или 5699 наши бухгалтера тож проверяют расчеты из Эксела на калькуляторе

был всегда уверен что при расчетах береться число из ячейки , а не дополнительное вычесление произведений с последующим округлением

[regist]

Цитата: Сообщение от obmanthik был всегда уверен что при расчетах береться число из ячейки , а не дополнительное вычесление произведений с последующим округлением

для расчёта берётся, то вы получили предыдущими расчётами, а на экране округляется до того количества знаков, которое вы указали ( но в рассчётах всё равно все эти знаки после запятой будут учитываться, если не хотите чтоб учитывались, то надо использовать ф-цию ОКРУГЛ).

[ДЫСЫпы]

Цитата: Сообщение от obmanthik был всегда уверен что при расчетах береться число из ячейки

Так и есть.

У вас проблема в том, что Вы на калькуляторе видимые числа считаете, а не те что в ячейках.


Сделайте в формате ячеек число десятичных знаков до двух и проверяйте хоть где, результат будет совпадать.
Или как Вам советовали, округляйте до целого.

[ДЫСЫпы]

Зацените что получилось.
Скачать пример
Если не лень и есть время, погоняйте с разными значениями. Найдете ошибку буду признателен
Если не найдете, останется только применить к реальной задаче.
Значения вводите в нижней таблице "Проверка", после чего жмете кнопку "Поиск решения".
На остальные кнопки жать не нужно, они для выявления ошибок.
Жду Ваших замечаний

PS
Решение может не совпадать с вашими числами, но будет верным.

[luivilla]

В экселевском файле есть столбец с числами. Числа отображают количество минут 15, 23, 7... Всегда целое число. Нужно как-то преобразовать числа так, чтобы они отображались в формате ЧЧ:ММ:СС, то есть 00:15:00, 00:23:00, 00:07:00
Как это можно сделать максимально быстро и эффективно?

[ДЫСЫпы]

Цитата: Сообщение от luivilla быстро и эффективно?

Быстро и эффективно, в соседней ячейке вбить формулу и смотреть туда.

[andrey_k]

Цитата: Сообщение от luivilla В экселевском файле есть столбец с числами. Числа отображают количество минут 15, 23, 7... Всегда целое число. Нужно как-то преобразовать числа так, чтобы они отображались в формате ЧЧ:ММ:СС, то есть 00:15:00, 00:23:00, 00:07:00 Как это можно сделать максимально быстро и эффективно?

можно иначе - используем факт, что 1 - это сутки, тогда минуты будут ММ/24/60, где ММ - минуты (явно числом или ссылка на ячейку.
Присваиваем ячейку с этой формулой формат ЧЧ:ММ:СС, и получаем нужный результат

[rusk1234]

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

Спасибо! За полугодие 2012, я все ввел вручную. За другие предыдущие года пока мне дали отбой. Но Ваше решение проверю на исходных данных нескольких месяцев.

P.S. Но хотелось бы решение в рамках полной задачи, т.е. на все строки и столбцы моего примера с возможностью редактирования, т.к. при нажатии кнопок добавить строки и столбцы ничего не происходит...

[михаил201]

Помогите придумать функцию которая могла бы вырезать числа (года) из ячеек со смешанным содержанием: 2001-2002 (диапазон годов); 2005 (один год); «Не указан год» - (если в ячейке не найден год)

Например:

98-2009 время обучения на курсах………………………………………………1998-2009
1995-2000 учеба в институте……………………………..…………………………1995-2001
00 время поступления………………………………………………………………….2000
00-05 работал в магазине 1000 мелочей…………………………………….2000-2005
Обучался в институте пять лет…………………………………………………….«Не указан год»

[ДЫСЫпы]

Цитата: Сообщение от rusk1234 Но хотелось бы решение в рамках полной задачи

Можете попробовать, скачайте здесь
Для решения реальной задачи, заполните Шаблон, можно руками, можно случайным способом нажав кнопку.
Далее на листе "Картошка" ищем результат. Один раз мне повезло))

Проблемы:
- Не оптимизированы циклы, размышляю над этим. В качестве временной меры сделал для них ручное управление (Цикл стр/с-б) примерное соотношение 1 к 10.
На странице Тест, хорошо видно как в некоторых местах и в конце при найденном решении, идет холостой прогон.
- При больших значениях, возникает проблема времени, вчера например гонял более 5 часов, решения не нашел. Для решения этой проблемы, ничего умней кроме как применения делителя и округления не нашел, с ними тоже можно экспериментировать.


Цикл стр/с-б, с стр разбираюсь, большие значения не нужны 10 хватает(??), с-б по логике(???) и шаге=1 не может быть меньше суммы по столбцу

, но тогда уходим в сутки/недели перебора, чтобы избавиться ввел делитель и округление.
В "тесте" по умолчанию стоит 50 и при этом все находится))
Вообщем вопросов пока больше чем ответов, думаю не спеша и каждый день, очень интересно решить.
Пробуйте гонять разные суммы и с разными настройками на странице "Тест", там хотя бы одно верное решение существует.
Пишите вопросы и замечания, пригодятся.

И еще...
Один раз решение было найдено вчистую, без делителей и скруглений и причем быстро, что навело на размышления, а будет ли хоть одно решение при забивании календаря от балды?
Для пробы заполнил календарь случайным образом и разными суммами, стал искать решение по получившимся итоговым. Через долго тоже нашел.
Отказываться от случайного заполнения и вычислять нужные дни? Это уже за гранью моих скромных способностей. Вряд ли осилю))

[andrey_k]

Цитата: Сообщение от михаил201 Помогите придумать функцию которая могла бы вырезать числа (года) из ячеек со смешанным содержанием: 2001-2002 (диапазон годов); 2005 (один год); «Не указан год» - (если в ячейке не найден год)

если у Вас во всех случаях будет строка вида число1 "тире" число2 "пробел" описание, тогда алгоритм такой:
0. Убираем концевые пробелы в строке (в начале и конце строки - СЖПРОБЕЛЫ() )
1. проверяем первый символ - если не цифра или год - "год не указан"
2. ищем "тире" - считаем, что все символы от 1-го до предшествующего "тире" - это число1. Если "тире" не находим, то число2 отсутствует - у нас вариант с одним годом.
3. ищем пробел, от найденного в п.2. "тире" или 1-го символа - считаем, что все символы до пробела - число2.
4. преобразуем число1 и число2 в вид ГГГГ (если >100, то ничего не делаем, иначе сравниваем с 100, если <100, но >12, то +1900, иначе +2000)
5. формируем строку вывода.
формулами запишу позже (если кто другой не опередит со своим решением)

[andrey_k]

михаил201, выложил предварительный вариант.
Для наглядности разложил функцию по-этапно.
Скриншот

http://narod.ru/disk/55277813001.3bf...1%8F.xlsx.html

[михаил201]

Цитата: Сообщение от andrey_k формулами запишу позже (если кто другой не опередит со своим решением)

Спасибо, с нетерпением жду формулы!

[ДЫСЫпы]

Цитата: Сообщение от михаил201 с нетерпением жду формулы!

Ловите здесь
Без разбивки не выходит, Ескель ругается на сложную формулу, хотя если еще подумать, может и выйдет. Но как идея и направление для размышлений, сойдет
Кстати у Вас ошибка здесь или так надо?

Цитата: Сообщение от михаил201 1995-2000 учеба в институте……………………………..…………………………1995-2001