Страницы: 1
RSS
Маршрут прохождения детали по участкам, подсчет чисел в массиве по возрастанию с расстановкой порядкового номера в другой таблице
 
Здравствуйте! Напишите пожалуйста формулу. Я уже голову сломал и ничего подобного в инете не нашел.
Есть табличка, в заголовке которой указаны названия участков (см. в файле синяя шапка). В эту таблицу приходят номера операций.
Нужно подсчитать числа в таблице по возрастанию и вывести их в промежуточную табличку (см. в файле желтым цветом) в строку слева-направо и в столбик сверху-вниз. Для понимания, что должно получиться, в файле я вручную вбил результат подсчета (см. файл красный шрифтом)
Далее, на основе это таблички я смогу с помощью формулы СЦЕПИТЬ загнать цифры в ячейки конечной таблички (см. файл серым цветом).
То есть, в желтых ячейках должна быть формула, которая будет считать.
Изменено: maxrus163 - 25.03.2018 15:46:52
 
Непонятен алгоритм подсчета. В нижней таблице есть номера 11;12;, но в верхней их нет, и наоборот в верхней есть 2;9;, в нижней нет?
 
Неудачное расположение таблицы жёлтой. А если будет больше 4 строк, то таблицу с синей шапкой смещать? Могу предложить реализацию макросом, но при другом дизайне. Этот мне не нравится.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
AleksSid, описываю: Ищем первую цифру по возрастанию, это "1", записываем в А1 "1". Далее ищем следующую цифру, это "3" - это вторая цифра по возрастанию, записываем "2" в С1. Далее третья цифра по возрастанию - "4", записываем "3" в А2 и так далее.
Изменено: maxrus163 - 25.03.2018 15:32:00
 
Alemox,более 4 обычно не бывает. Макрос это как крайняя мера. Давайте пока без них.
Изменено: maxrus163 - 25.03.2018 15:29:04
 
Такой вариант:
Формула массива:
=ЕСЛИОШИБКА(ПОИСКПОЗ(НАИМЕНЬШИЙ(A$6:A$22;СТРОКА());НАИМЕНЬШИЙ($A$6:$G$22;СТРОКА(СМЕЩ($H$1;;;МАКС($A$6:$G$22))));0);"")
Обычная:
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕПИТЬ(A1;" ";A2;" ";A3;" ";A4));" ";",")
Изменено: Bema - 25.03.2018 16:53:30 (Поправил решение)
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Я не силён в формулах массивов, но со скрытым листом смог наваять.
Если что коллеги помогут избавиться придумав по круче формулу.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 

Монстрик массивный, но без доп столбцов, листов и ......

Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUMPRODUCT(SMALL(IF(A$6:A$22<>"";
MATCH(A$6:A$22;SMALL($A$6:$G$22;ROW(A1:INDEX(A:A;COUNT($A$6:$G$22))));0)
);ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))*10^(
((INT(LOG10(MAX(A$6:A$22)))+1)*(COUNT(A$6:A$22)-ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))+
1+COUNT(A$6:A$22)-ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))));
REPT(REPT("#";INT(LOG10(MAX(A$6:A$22)))+2)&"(";COUNT(A$6:A$22)));"0(";" "));" 0";" ");" ";",");"")

Если операций до 99, то
Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUMPRODUCT(SMALL(IF(A$6:A$22<>"";
MATCH(A$6:A$22;SMALL($A$6:$G$22;ROW(A1:INDEX(A:A;COUNT($A$6:$G$22))));0)
);ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))*10^(
(2*(COUNT(A$6:A$22)-ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))+
1+COUNT(A$6:A$22)-ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))));
REPT("###(";COUNT(A$6:A$22)));"0(";" "));" 0";" ");" ";",");"")

Изменено: БМВ - 25.03.2018 22:12:39
По вопросам из тем форума, личку не читаю.
 
Bema, Alemox, БМВ,попробовал все ваши решения. И за серую табличку спасибо ) У всех разный подход и у всех работает! Спасибо за отклик и труды.
Решение Bema самое простое, которое я могу понять и внедрить в свой рабочий файл. Кстати, я когда сам тыркался, то многие из этих формул использовал, но не смог все в одном написать.
Решение Alemox с доп. листом. Пока еще не совсем понял как там работает, но думаю смогу разобраться. Но в рабочем файле у меня около 150 одинаковых листов и каждый из них должен независимо работать. Получается, придется создавать столько же вспомогательных таблиц или листов. Это усложнение.
Решение БМВ, для моего настоящего уровня знания формул Эксель, просто недосягаемо понять. Но, конечно, круто сделано без таблиц-посредников! Заметил подтормаживание моего компа, при работе формул. Когда удаляю все цифры в исходной таблице, то в серой таблице цифры удаляются в течение примерно секунд 5. Также когда ввожу новые числа в исходную таблицу, в серой маршрут записывается с инерцией. Не представляю, что будет если размножить формулу на 150 листов в без того загруженную книгу. Надо пробовать конечно.
Изменено: maxrus163 - 25.03.2018 22:23:49
 
БМВ,операций меньше 99, максимум 20-25 бывает. Уж настолько сложных деталей мы не изготавливаем ) Хочется, конечно вашу формулу внедрить. Но побаиваюсь внедрять то, чего не понимаю. Вдруг, что-то пойдет не так, сам отладить не смогу. Но это, конечно, очень круто! Сейчас файл весит рабочий 2,8мб, всего там около 170 листов. Все напичканы формулами. Вашу формулу мне надо будет вставить примерно на 150 листов. Конечно редко бывают большие проекты, когда задействованы все листы, обычно до 70 листов. Но все же бывают. Как думаете, сильно будет затормаживать комп? Еще планирую сделать сетевую версию файла, чтобы одновременно два технолога могли работать.
Изменено: maxrus163 - 25.03.2018 22:39:41
 
Еще раз, всем спецам спасибо! Мое признание и уважение!
 
maxrus163, немного оптимизировал, исходя из условий, снизив универсальность.
Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUM(SMALL(IF(A$6:A$22<>"";
MATCH(A$6:A$22;SMALL($A$6:$G$22;ROW(A1:INDEX(A:A;COUNT($A$6:$G$22))));0)
);ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))*
10^((3*(COUNT(A$6:A$22)-ROW($A$1:INDEX($A:$A;COUNT(A$6:A$22))))+1)));
REPT("###(";COUNT(A$6:A$22)));"0(";" "));" 0";" ");" ";",");"")
Притормаживать будет. Может не самый большой массив, но расчет многопроходный. По этому, если известно количество операций на участке, то лучше использовать вариант Bema,
Просто было интересно реализовать слияние без макросов и в одной формуле.
По вопросам из тем форума, личку не читаю.
 
Формула для желтой таблички, чуть проще чем у Bema:
Код
=ЕСЛИОШИБКА(РАНГ(НАИМЕНЬШИЙ(A$6:A$22;СТРОКА(A1));$A$6:$G$22;1);"")
 
Цитата
maxrus163 написал:
формулу мне надо будет вставить примерно на 150 листов.
Это можно сделать  следующим образом, выделить все нужные листы и вставить формулу в нужную ячейку. Автоматически она будет вставлена в эту ячейку на все листы. Ну а про быстродействие, я выше написал, хотя немного подправил и работает пошустрее, однако есть изъян, формула работает до 5 операций на раздел, что делает её ограниченной.
Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUM(SMALL(IF(A$2:A$36<>"";
MATCH(A$2:A$36;SMALL($A$2:$G$36;ROW($A$1:INDEX($A$1:$A$36;COUNT($A$2:$G$36))));0)
);ROW($A$1:INDEX($A$1:$A$36;COUNT(A$2:A$36))))*
10^((3*(COUNT(A$2:A$36)-ROW($A$1:INDEX($A$1:$A$36;COUNT(A$2:A$36))))+1)));
REPT("###(";COUNT(A$2:A$36)));"0(";" "));" 0";" ");" ";",");"")
По вопросам из тем форума, личку не читаю.
 
БМВ, этот вариант вполне себе быстро работает. Про ограничение не понял немного. Имеете ввиду про 5 цифр в каждую ячейку серой таблички?
 
MCH, спасибо!
 
Цитата
maxrus163 написал:
Про ограничение
в таблице не может быть более 5 операций в одном столбце.
И немного с оптимизацией от MCH и 71й строкой
Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUM(SMALL(IF(A$2:A$71<>"";
RANK.EQ(A$2:A$71;$A$2:$G$71;1)
);ROW($A$1:INDEX($A$1:$A$71;COUNT(A$2:A$71))))*
10^((3*(COUNT(A$2:A$71)-ROW($A$1:INDEX($A$1:$A$71;COUNT(A$2:A$71))))+1)));
REPT("###(";COUNT(A$2:A$71)));"0(";" "));" 0";" ");" ";",");"")
Изменено: БМВ - 26.03.2018 12:54:12
По вопросам из тем форума, личку не читаю.
 
БМВ, на каждом рабочем листе в исходной табличке будет 71 строка, по ней будут раскиданы, как правило, до 10 цифр. Ну и на каком-либо участке (в серой табличке) через запитую может быть записано до 4 цифр. В Вашем примере,36 строк в исходной таблице - работает все отлично! Буду пробовать вставлять к себе.
 
Смотрите выше и там я добавил проверку при вводе, чтоб не более одной операции на строке и дубликаты нет возможности выбрать.

Что-то захватила задача с головой :-). Еще одна оптимизация. и по длинне, и по скорости.
Код
=IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(
SUM(LARGE(IF(B$2:B$71<>"";RANK.EQ(B$2:B$71;$A$2:$G$71;1));ROW($A$1:INDEX($A$1:$A$71;COUNT(B$2:B$71))))*
10^((3*(ROW($A$1:INDEX($A$1:$A$71;COUNT(B$2:B$71)))-1)+1)));
REPT("###(";COUNT(B$2:B$71)));"0(";" "));" 0";" ");" ";",");"")
Да и вставлять формулу сразу на несколько листов можно быстро.
Встать в ячейку с формулой на листе где она есть.
выделить нужные листы куда нужно скопировать c SHIFT или удерживая CTRL
Войти в редактор формулы (F2)
ENTER ( В данном случае, так как формула масиивная, то CTRL+SHIFT+ENTER)
.  
Изменено: БМВ - 26.03.2018 20:45:06
По вопросам из тем форума, личку не читаю.
 
БМВ, спасибо за внимание к теме, и видимо, без Вас мне никак. Можете меня пинать, отцы экселя, но ученик снова лажает (
Вчера до ночи пытался в рабочем файле все это внедрить, и ничего не получается. А потом до меня дошло, что я упустил, один важный нюанс при постановки задачи.
В шапке исходной таблицы, слева направо 3 ячейки статичные, а 4 остальные динамичные, то есть  меняются участки в зависимости от названия операции, и если операций мало, то и участков в заголовке тоже меньше.
Так вот, варинат Bema, у меня работает при условии, что в шапке все ячейки заполнены, но на практике конечно вся шапка не всегда заполняется.
Вариант БМВ, вообще не могу заставить работать, пробовал формулы из разных примеров. Все тщетно (
Мужики, не дайте трудам этой темы остаться не востребованными. Помогите.
Прилагаю лист с рабочей книги. Смотрите ТК5. Справа, табличка в черной рамке - это исходные числа. В шапке 4 ячейки подсветил красным - они меняются в зависимости от выбранных операций из выпадающего списка на коричневых строках слева. Также серым выделил, ячейки, где нужно собрать маршрут. Слева, еще есть похожая итоговая табличка, на нее не обращайте внимания - это для ручного ввода.
Изменено: maxrus163 - 27.03.2018 07:48:14
 
В общем, после некоторых мучений, удалось приручить вариант Bema, с формулой MCH, путем введения условия на каждую динамическую ячейку.
 
maxrus163,  В приложении 6.1 адаптированная для вашего варианта мое решение, но обратите внимание на выделенные желтым ячейки. Я делал вставку значений из вашей таблицы и пришли нули, а не пусто. что и влечет сбой. Удалите их, и смотрите, все ок. то есть формула работает, но кормить ей надо нормальные данные.

Глядя на ваш шаблон, что-то там не все гладко и в остальном. О 150 листах при таких формулах я б не заикался. Одни тесты на число ли это - более чем много.
Зачем такие сложности AB:AH? там просто =IF(($A12=AB$6)*(AB$6<>"");$B12;"") , тем более что не понял куда операция 3 пропала.
В AA22 - вакханалия сплошная.



По вопросам из тем форума, личку не читаю.
 
БМВ, спасибо, учту ваши замечания.
Согласен, не все там пока гладко. Формулы не всегда короткие, как могли бы быть, потому что, многие формулы и приемы их использования пока еще не знаю. Пишу как могу. С опытом, оптимизирую.
А цифра "3" пропала, это потому что в этой таблице я задавал условие на убирание операций, которые идут подряд на одном участке (деталь маршрута не меняет), и чтобы не мешали правильно формировать желтую табличку.
 
При копировании значений из таблицы своего примера у меня почему-то не приходят нули, как у вас показано. Да и в формулах там при значении ЛОЖЬ ставиться "". Откуда там нули могут быть?  Что-то не так делаю?
Цитата
БМВ написал:
=IF(($A12=AB$6)*(AB$6<>"");$B12;"")

Что-то, не могу въехать в это условие, никогда не применял.
Знак * это связь какая-то между первыми и вторыми скобками или умножить? АВ$6<>"" - любое значение, кроме пустого?
Страницы: 1
Наверх