Перенос отфильтрованных строк с одного листа на другой

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос отфильтрованных строк с одного листа на другой (Формулы/Formulas)

Alex1018

Дата: Вторник, 08.10.2019, 11:55 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

К сообщению приложен файл: 5492027.xlsx (76.5 Kb)

Сообщение отредактировал Alex1018 - Вторник, 08.10.2019, 11:57

Ответить

китин

Дата: Вторник, 08.10.2019, 12:59 | Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

какие столбцы переносятся?
сколько строчек в листе Спецификация по линиям?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Alex1018

Дата: Вторник, 08.10.2019, 13:19 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата китин, 08.10.2019 в 12:59, в сообщении № 2 ()

какие столбцы переносятся?сколько строчек в листе Спецификация по линиям?

1) Из первого листа переносятся столбцы "Номер изометрии", "Наименование", "Тип размер, мм", "Нормативный документ", "Материал", "Ед. изм." и количество, выбранное по соответствующему акту из столбцов Z:GU.

Если на конкретном примере:

Лист "Спецификация по линиям". Столбец "AB". Это Акт № 3 от 10.09.2019. В "AB5" внесли руками "3,8", в "AB6" внесли "2" и так далее.
Нужно чтобы на листе "Акт приема-передачи", при вбитом значении номера Акта в "I2", начиная с 19 строки перенеслись все искомые значения по тому количеству заполненных строк, которые видны при применении фильтра на листе "Спецификация по линиям" в столбце "AB"

2) В исходном документе на листе "Спецификация по линиям" 3851 строка, строки будут добавляться.

Не до конца уверен, что смог доходчиво изложить свою мысль.

Ответить

китин

Дата: Вторник, 08.10.2019, 15:18 | Сообщение № 4

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

вот такой массивный монстр получился

Код

=ЕСЛИОШИБКА(ИНДЕКС('Спецификация по линиям'!O$5:O$403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$403;;ПОИСКПОЗ($B$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($Z$5:$Z$403)-4);СТРОКА(A1)))&" "&ИНДЕКС('Спецификация по линиям'!P$5:P$403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$403;;ПОИСКПОЗ($B$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($Z$5:$Z$403)-4);СТРОКА(A1)))&" "&ИНДЕКС('Спецификация по линиям'!Q$5:Q$403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$403;;ПОИСКПОЗ($B$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($Z$5:$Z$403)-4);СТРОКА(A1)))&СИМВОЛ(10)&ИНДЕКС('Спецификация по линиям'!R$5:R$403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$403;;ПОИСКПОЗ($B$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($Z$5:$Z$403)-4);СТРОКА(A1)));"")

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
остальные формулы попроще

К сообщению приложен файл: 5754785.xlsx (81.7 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Alex1018

Дата: Вторник, 08.10.2019, 18:38 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Излишнее цитирование удалено

Огромное спасибо!

Монстр работает. Сейчас разберусь, почему в листе "Акт приема-передачи" при добавлении строк всё слетает, и ух, заживём!)

Сообщение отредактировал Pelena - Среда, 09.10.2019, 07:17

Ответить

Alex1018

Дата: Среда, 09.10.2019, 15:46 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

На листе "Акт приема-передачи" в "B19" не могу добавить формулу "ЕСЛИ(ЕПУСТО('Спецификация по линиям'!Q$5:Q$5403);'Спецификация по линиям'!P$5:P$5403;'Спецификация по линиям'!Q$5:Q$5403)".

На конкретном примере:

Если на листе "Спецификация по линиям" в столбце "Q" появляется запись, то данные из "P" меняются на данные из "Q" в листе "Акт приема-передачи". Аналогично для "S" и "R" и для "U" и "T".

Иными словами: как правильно в

=ЕСЛИОШИБКА(ИНДЕКС('Спецификация по линиям'!O$5:O$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&" "&ИНДЕКС('Спецификация по линиям'!P$5:P$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&" "&ИНДЕКС('Спецификация по линиям'!R$5:R$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&СИМВОЛ(10)&ИНДЕКС('Спецификация по линиям'!T$5:T$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)));"")

добавить

ЕСЛИ(ЕПУСТО('Спецификация по линиям'!Q$5:Q$5403);'Спецификация по линиям'!P$5:P$5403;'Спецификация по линиям'!Q$5:Q$5403)
ЕСЛИ(ЕПУСТО('Спецификация по линиям'!S$5:S$5403);'Спецификация по линиям'!R$5:R$5403;'Спецификация по линиям'!S$5:S$5403)
ЕСЛИ(ЕПУСТО('Спецификация по линиям'!U$5:U$5403);'Спецификация по линиям'!T$5:T$5403;'Спецификация по линиям'!U$5:U$5403)

?

Вопрос решился путем добавления дополнительных трех столбцов в лист "Спецификация по линиям", куда и встали формулы, которые не получалось добавить в лист "Акт приема-передачи" в "B19", откуда теперь и берутся данные для общей формулы массива

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

К сообщению приложен файл: ________.xlsx (91.5 Kb)

Сообщение отредактировал Alex1018 - Четверг, 10.10.2019, 07:40

Ответить

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

На листе "Акт приема-передачи" в "B19" не могу добавить формулу "ЕСЛИ(ЕПУСТО('Спецификация по линиям'!Q$5:Q$5403);'Спецификация по линиям'!P$5:P$5403;'Спецификация по линиям'!Q$5:Q$5403)".

На конкретном примере:

Если на листе "Спецификация по линиям" в столбце "Q" появляется запись, то данные из "P" меняются на данные из "Q" в листе "Акт приема-передачи". Аналогично для "S" и "R" и для "U" и "T".

Иными словами: как правильно в

=ЕСЛИОШИБКА(ИНДЕКС('Спецификация по линиям'!O$5:O$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&" "&ИНДЕКС('Спецификация по линиям'!P$5:P$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&" "&ИНДЕКС('Спецификация по линиям'!R$5:R$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)))&СИМВОЛ(10)&ИНДЕКС('Спецификация по линиям'!T$5:T$5403;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС('Спецификация по линиям'!$Z$5:$GU$5403;;ПОИСКПОЗ($K$2;'Спецификация по линиям'!$Z$2:$GU$2;0))<>0;СТРОКА($AC$5:$AC$5403)-4);СТРОКА(I1)));"")

добавить

ЕСЛИ(ЕПУСТО('Спецификация по линиям'!Q$5:Q$5403);'Спецификация по линиям'!P$5:P$5403;'Спецификация по линиям'!Q$5:Q$5403)
ЕСЛИ(ЕПУСТО('Спецификация по линиям'!S$5:S$5403);'Спецификация по линиям'!R$5:R$5403;'Спецификация по линиям'!S$5:S$5403)
ЕСЛИ(ЕПУСТО('Спецификация по линиям'!U$5:U$5403);'Спецификация по линиям'!T$5:T$5403;'Спецификация по линиям'!U$5:U$5403)

?

Вопрос решился путем добавления дополнительных трех столбцов в лист "Спецификация по линиям", куда и встали формулы, которые не получалось добавить в лист "Акт приема-передачи" в "B19", откуда теперь и берутся данные для общей формулы массива

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

Автор - Alex1018
Дата добавления - 09.10.2019 в 15:46

Страница 1 из 1
1