Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Превратить последовательные диапазоны в список, сохраняя последовательность
 
#1
09.08.2018 14:45:19
Здравствуйте!
Нужна ваша помощь. Есть проблема, к решению которой призвали меня, но я столкнулся с трудностью. Нужно на основании товарной накладной, в которой указываются номера товаров, когда поштучно, когда диапазоном, создать пономерную таблицу этих самых товаров. Т.е. товарная накладная - это такая укороченная за счёт диапазонов номеров версия этой требуемой таблицы.

Прикрепляю файл.

Например, есть диапазон 2698858950-2698858981, его нужно превратить в:
2698858950
2698858951
2698858952
2698858953
2698858954
2698858955
...
2698858981

Но это полбеды, т.к. ниже должен пойти следующий диапазон... боюсь, тут нужно применить массивы, а плохо ими владею...
Изменено: _Vadim_ - 09.08.2018 14:45:42
 
 
 
#2
09.08.2018 15:31:12
Пытаюсь очень некрасиво решить поставленную задачу, но сталкиваюсь с затруднением, почему-то не работает функция.
Вот, имеется в ячейке диапазон: 2698858950-2698858981. Хочу получить первое число: 2698858950, для этого пытаюсь использовать функцию:
Цитата
ПСТР(P13;1;ПОИСК(P13;"-"))
Т.е. функция пстр должна вернуть текст с начала текста до знака "-". Но функция выдаёт только ошибку #ЗНАЧ!. Не понимаю, почему, может кто-нибудь объяснит? "-" копировал из самой ячейки и вставлял в формулу - всё равно не работает...
Изменено: _Vadim_ - 09.08.2018 15:32:01
 
 
 
#3
09.08.2018 15:35:15
Цитата
_Vadim_ написал:
ПОИСК(P13;"-"))
ПОИСК("-";P13)

По основному вопросу, у вас есть первый номер и есть количество в графе 10, то есть можно спокойно опираться на это.
только не понятно что должно получится в каком формате?
Изменено: БМВ - 09.08.2018 15:39:19
По вопросам из тем форума, личку не читаю.
 
 
 
#4
09.08.2018 15:42:48
совсем недавно была тема и решение предложили макросом
я себе стащил  :)
 
 
 
#5
09.08.2018 16:20:27
Смотрите формульный вариант.
 
 
 
#6
09.08.2018 16:22:26
Размялся, массивная.

=--TRIM(LEFT(SUBSTITUTE(INDEX('М0001 (1 вариант)'!$P$13:$P$32;MATCH(ROW()-13;MMULT(TRANSPOSE('М0001 (1 вариант)'!$BC$12:$BC$31)*(ROW('М0001 (1 вариант)'!$BC$12:$BC$31)>ROW('М0001 (1 вариант)'!$BC$12))*(ROW('М0001 (1 вариант)'!$BC$13:$BC$32)>=TRANSPOSE(ROW('М0001 (1 вариант)'!$BC$13:$BC$32)));ROW('М0001 (1 вариант)'!$BC$13:$BC$32)^0)));"-";REPT(" ";99));99))+ROW()-13 -LOOKUP(ROW()-13;MMULT(TRANSPOSE('М0001 (1 вариант)'!$BC$12:$BC$31)*(ROW('М0001 (1 вариант)'!$BC$12:$BC$31)>ROW('М0001 (1 вариант)'!$BC$12))*(ROW('М0001 (1 вариант)'!$BC$13:$BC$32)>=TRANSPOSE(ROW('М0001 (1 вариант)'!$BC$13:$BC$32)));ROW('М0001 (1 вариант)'!$BC$13:$BC$32)^0))
Изменено: БМВ - 09.08.2018 16:22:38
По вопросам из тем форума, личку не читаю.
 
 
 
#7
09.08.2018 16:32:12
Добрый день!

Вариант на PowerQuery.
 
 
 
#8
09.08.2018 16:45:40
_Vadim_, а можно вопрос? Зачем у вас в файле так много узких столбцов и так много объединённых ячеек? Очень интересно, правда.
 
 
 
#9
09.08.2018 16:47:54
ADimov, Вангую - так экспортируется из другой программы или приходит от партнера поле экспорта из другой программы.
Изменено: БМВ - 09.08.2018 16:48:20
По вопросам из тем форума, личку не читаю.
 
 
 
#10
09.08.2018 16:52:43
БМВ, ааа) возможно) не приходилось сталкиваться с подобным просто)
 
 
 
#11
10.08.2018 11:38:53
Ребят! Спасибо всем огромное, но особенное спасибо БМВ) Всё именно то, что нужно) я вообще просто поражаюсь, как можно такие сложные формулы с обработкой строк создавать, это при том, что меня самого длинные формулы не особо пугают, могу в них разобраться, но в этой области мне это воспринимается чем-то за гранью рассудка)
Спасибо Вам ещё раз!)
 
 
 
#12
10.08.2018 11:54:39
_Vadim_,  Вчера вечером с трудом остановил себя от того чтоб не опираться на столбец с количеством, а использовать только столбец с диапазоном. По идее это не сложно

TRANSPOSE('М0001 (1 вариант)'!$BC$12:$BC$31 меняется на  
TRANSPOSE(IFERROR(1+TRIM(right(SUBSTITUTE('М0001 (1 вариант)'!$P$12:$P$31):"-";REPT(" ";99);99))-TRIM(LEFT(SUBSTITUTE('М0001 (1 вариант)'!$P$12:$P$31):"-";REPT(" ";99);99));0)

но есть ли смысл усложнять сложное.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
13.08.2018 08:53:52
БМВ, благодарю ещё раз!) И ещё раз, моё восхищение вашим навыкам Excel)
Изменено: _Vadim_ - 13.08.2018 08:54:22
 
 
 
#14
13.08.2018 09:28:03
Цитата
_Vadim_ написал:
моё восхищение вашим навыкам Excel
Спасибо на добром слове, но я, да собственно как и все, просто на определенном витке спирали развития и знаю и умею чуть больше/меньше чем другие, ну а интерес помогает перейти на следующий виток, чего и всем желаю.
По вопросам из тем форума, личку не читаю.
 
 
 
#15
13.08.2018 16:02:01
БМВ, помогите, пожалуйста!
Создал рабочий файл с вашей формулой, но что-то пошло не так) Посмотрите, пожалуйста. Дал опробовать файл, люди занесли данные, но на одном номере формула дала сбой. Они заносили серию одиночных номеров, но вместо того, чтобы перейти к следующему номеру, формула внезапно стала "протягивать" последний номер. Причём, если из столбца количества вообще убрать все значения, формула не ругается, а всё равно работает.
Помогите, пожалуйста!
 
 
 
#16
13.08.2018 17:06:37
_Vadim_,  Туту все просто, нужно диапазон взять побольше вместо 32 - 52 и вместо 31 - 51 например,по идее можно его расчетным сделать и загнать в имя, будет и формула короче и на количество можно внимание не обращать.
Для красоnы проверку на ошибку поставил, чтоб протягивать можно было дольшe;
Изменено: БМВ - 13.08.2018 17:08:26
По вопросам из тем форума, личку не читаю.
 
 
 
#17
13.08.2018 21:40:55
Вариант с авто-настройкой на диапазон, через имена формулы сокращены по записи.
По вопросам из тем форума, личку не читаю.
 
 
 
#18
14.08.2018 08:49:57
БМВ, это гениально! я ведь изначально думал над тем, что нужен, наверное, макрос, который бы каким-то образом менял диапазоны в формулах, автоматизированная замена, но я думал, что проблема будет в том, что диапазон не будет заполнен весь, что формула будет возвращать ошибку и когда увидел, что формула работает и при меньшем, чем диапазон, кол-ве номеров, ошибочно подумал, что всё в порядке. А оказалось, что всё таки проблема по прежнему существует, просто я её неправильно понимал.
А тут Вы в последнем случае вообще без массивов обошлись... какой-то модуль написали на VBA... буду изучать)

Хотел бы спросить, а можно ли подобно тому, как Вы удлинили диапазон по столбцу P, где номера представлены, удлинить диапазон и по столбцу BC, в котором представлено количество? Просто позиций, номеров может быть, строго говоря, до 150 и данная проблема, что нумерация вышла за пределы диапазона действия формулы, может возникнуть вновь.
Изменено: _Vadim_ - 14.08.2018 08:53:07
 
 
 
#19
14.08.2018 10:03:56
Цитата
_Vadim_ написал:
Хотел бы спросить, а можно ли подобно тому, как Вы удлинили диапазон по столбцу P, где номера представлены, удлинить диапазон и по столбцу BC,
Вроде я менял и там и там в версии _1 но да можно, хотя в _2 это решается по иному и тоже сделано, но наполовину.

_Vadim_,
немного теории : изначально формула обрабатывала диапазон заданный !$P$13:$P$32. Когда появились значений в 33 строке и далее, то она дала сбой.
Тут надо делать выбор между определением диапазона с запасом (отсутсвие данных не меняет результат) или делать диапазон вычисляемым. При этом может получится, что потери при расчете диапазона окажутся выше, чем если указать диапазон с запасом.
Вычислить не сложно у нас есть начало, например P13 и непрерывный диапазон достаточно посчитать значения, отнять то, что попало из шапки и сместить на размер шапки.
КСБ!Qrows =COUNTA(КСБ!$P:$P)+11. В примере получим 25+(-1+12)=36
далее укажем диапазон  через конструкцию RangeP13to =КСБ!$P$13:INDEX(КСБ!$P:$P;КСБ!Qrows) что даст нам диапазон КСБ!$P$13:$P$36
аналлогично поступаем с другими нужными диапазонами. (я вчера забыл в именах подправить, но может это и к лучшему, у ас есть пример как записать и расчетный диапазон и фиксированный. но в целом должно быть
RangeBC13to =КСБ!$BC$13:INDEX(КСБ!$BC:$BC;КСБ!Qrows)
RangeBC12to =КСБ!$BC$12:INDEX(КСБ!$BC:$BC;КСБ!Qrows-1)

Без массивов я не обошелся, просто спрятав те части формул которые требовали массивного ввода в имена, автоматически получились массивы.
По вопросам из тем форума, личку не читаю.
 
 
 
#20
14.08.2018 11:01:00
БМВ, понял, спасибо Вам большое!)
 
 
 
#21
14.08.2018 12:13:34
Ну зачем такие потуги с формулами, добавим немного кода в макрос и вуаля.
Изменено: TheBestOfTheBest - 14.08.2018 12:13:46
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#22
14.08.2018 12:21:52
Цитата
TheBestOfTheBest написал:
Ну зачем такие потуги с формулами
Как зачем, - интересно. :-)
Но в целом да, при наличии кода определенного объема , проще добавить еще немного кода, чем городить формулы.  - практично, но неинтересно :-)
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх