Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Наименования оборудования, где коэффициент износа >0, перенести в другую таблицу
 
#1
24.06.2020 19:33:15
Здравствуйте! Помогите, пожалуйста, с решением. Задача состоит в следующем:

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

Сложность №1 - обе таблицы имеют растягивающийся диапазон, а значит вторая таблица должна вмещать ровно столько строк, сколько было отобрано из первой таблицы по заданному условию (коэффициент >0).

Сложность №2 - во второй таблице не должно быть пустых строк ни между наименованиями, ни в конце. Я пробовал и через формулы массива, и через ИНДЕКС, но у меня всегда появлялись пустые строки между наименованиями.

Сложность №3 - это должна быть не сводная таблица, т.к. планируется добавить к ней еще столбцы (в примере это столбцы 1, 2 и 3) и работать с ними тоже с помощью формул.

Пример прикрепил. На листе 1 - исходная таблица, на листе 2 - желаемый результат.

Есть ли решение этой задачи без макроса?
 
 
 
#2
24.06.2020 19:55:47
Цитата
flashertheone написал:
у меня всегда появлялись пустые строки между наименованиями
А у меня пустые строки после наименований. И как это избежать только формулами - не представляю
НА МЯСО В ПРОФИЛЕ
 
 
 
#3
24.06.2020 20:00:40
flashertheone, если наименование оборудования уникально т.к. без повторений

ИЛИ ТПРОСТО БЕЗ ЛИШНЕГО)
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица1[Наименование];ПОИСКПОЗ(1;ИНДЕКС((СЧЁТЕСЛИ(Результат!$A$1:A1;Таблица1[Наименование])=0)/(Таблица1[Значение]>0);0);0));"")
то формулами так ...
Код
=ЕСЛИОШИБКА(ИНДЕКС('Исходная таблица'!$A$2:ИНДЕКС('Исходная таблица'!$A:$A;СЧЁТЗ('Исходная таблица'!$A:$A);1);ПОИСКПОЗ(1;ИНДЕКС((СЧЁТЕСЛИ(Результат!$A$1:A1;'Исходная таблица'!$A$2:ИНДЕКС('Исходная таблица'!$A:$A;СЧЁТЗ('Исходная таблица'!$A:$A);1))=0)/('Исходная таблица'!$B$2:ИНДЕКС('Исходная таблица'!$B:$B;СЧЁТЗ('Исходная таблица'!$A:$A);1)>0);0);0));"")
Изменено: Mershik - 24.06.2020 20:04:33
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#4
24.06.2020 20:16:39
Михаил Л и Mershik - у вас одинаковые по сути решения, только первое через формулу массива и "умные" адреса, а другое - через обычные ИНДЕКС + ПОИСКПОЗ. Но пустые строки в конце в обоих решениях присутствуют. А наименования могут быть и с повторениями.
 
 
 
#5
24.06.2020 20:23:20
flashertheone,
Цитата
flashertheone написал:
у вас одинаковые по сути решения

это честно странно звучит, так можно сказать про любое решение в котором использоваться похожие функции)))
Цитата
flashertheone написал:
А наименования могут быть и с повторениями.
если он должны быть отдельными строками то мой вариант не подойдет так как только уникальные отражать будет (если будет 149 раз покажет 1).
держите еще вариант)
Код
=ИНДЕКС(Таблица1[Наименование];АГРЕГАТ(15;4;СТРОКА(Таблица1[Наименование])*(Таблица1[Значение]>0);СТРОКА()-1))
Изменено: Mershik - 24.06.2020 20:28:31
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#6
24.06.2020 20:28:13
Mershik,
Да, я понял. Возьму тогда вариант Михаила, если не найдется более хорошего решения. Там и формулы покороче)
Спасибо за попытку)
 
 
 
#7
24.06.2020 20:32:23
В принципе, вариант Михаила почти идеальный. Формулы короткие, да и работают не только с уникальными значениями. Вот если бы заставить вторую таблицу менять размеры в зависимости от заполненности... Но похоже, что без макроса нереально. А то иначе получается, что если вторая таблица слишком маленькая, ее придется растягивать, чтобы увидеть все наименования. Если слишком большая - появятся пустые строки, которые вообще не нужны.
 
 
 
#8
24.06.2020 20:34:04
flashertheone, да только макросом или с его помощью протягивать умную)) и проще ее создавать макросом
Изменено: Mershik - 24.06.2020 20:40:13
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#9
24.06.2020 20:42:03
Цитата
flashertheone написал:
без макроса нереально
Реально в PQ
Меня бы устроило и макросом, типа
Код
 ActiveSheet.ListObjects("Таблица2").Range.AutoFilter Field:=1, Criteria1:="<>"
НА МЯСО В ПРОФИЛЕ
 
 
 
#10
24.06.2020 20:43:05
Тогда, если не сложно, выложите решение макросом. Может быть пересмотрю свое отношение к ним)
 
 
 
#11
24.06.2020 20:48:26
Цитата
flashertheone написал:
если не сложно, выложите решение макросом
Знал бы я как по-быстрому привязать макрос на событие изменения первой таблицы - выложил бы
НА МЯСО В ПРОФИЛЕ
 
 
 
#12
24.06.2020 20:56:22
Михаил Л, ну я в макросах вообще не силен, поэтому отчасти и не люблю их) А если через PQ сделать, то эту таблицу смогут просматривать только те пользователи, у кого установлен PQ? И сам Excel должен быть, наверное, не ниже 2013, правильно?
Изменено: flashertheone - 24.06.2020 20:57:55
 
 
 
#13
24.06.2020 21:38:05
Цитата
flashertheone написал:
если через PQ сделать, то эту таблицу смогут просматривать только те пользователи, у кого установлен PQ? И сам Excel должен быть, наверное, не ниже 2013, правильно?
Если будет сделано через PQ, то надо будет для отображения верного результата принудительно обновлять таблицу. Автоматически изменяться таблица не будет.
Для Excel 2010,2013 нужна отдельная надстройка. В Excel 2016 и выше встроено PQ.
Чем мешают пустые строки? Глаза мазолят?
НА МЯСО В ПРОФИЛЕ
 
 
 
#14
24.06.2020 22:30:04
Михаил Л, нет, просто планировал сделать несколько подобных таблиц на одном листе, одну под одной. А то листов и так достаточно в исходном документе, не хочется плодить ещё, разнося каждую табличку по отдельному листу. Но, видимо, придется именно так и делать)
 
 
 
#15
25.06.2020 04:54:12
Поменяйте концепцию, если это возможно.. Сделайте заполнение Исходной таблицы на основании таблицы Результат. А если есть необходимость использовать новое наименование - сначала добавьте его в таблицу Результат. А в таблицу Исходная таблица добавляйте наименования, используя выпадающий список. И только те, которые имеют Значение. А в таблице Результат у Вас будет столбец Значение, где с помощью =ВПР() или =СУММЕСЛИ() будет вестись подсчет Значения. И фильтром скроете всё, что меньше критического значения.
Изменено: Михаил Лебедев - 25.06.2020 07:10:08
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#16
25.06.2020 14:11:36
Михаил Лебедев, думал над вашим предложением... К сожалению, такой вариант не подходит. Исходная таблица - это массив данных, который будет заполнять пользователь. Там будут разные Значения - где-то будет 1, где-то меньше 1, где-то вообще не будет (пустая ячейка). И скрывать это фильтром не нужно, т.к. таблица предназначена не для этого. А делать такой же столбец Значение в таблице Результат - это почти то же самое, что объединить обе таблицы. Тогда зачем все эти фильтры, ВПРы и прочее? Как говорится, бери, да заполняй сразу всё руками в таблице Результат)

А вот использовать эту идею немного в другом русле кажется неплохой идеей (хотя и не совсем то, что я хотел). Использовать одну из формул выше для того, чтобы подтягивать значения из исходной таблицы, но не в таблицу Результат, а на технический лист (можно его даже скрыть будет). И уже из этих значений будет формироваться список в таблице Результат. Но опять же, это ручное заполнение.
 
 
 
Страницы: 1
Наверх