Задачка такая: В книге "Лист с данными" имеется список для передачи на другие листы по условию.
Условие для ячейки "Лист1!S2" 1 • Если список содержит текст "Корпусные плиты" тогда первое совпадение. 2 • Если не содержит, тогда ищем "Фасадные плиты" и первое совпадение. 3 • Если и того и другого нет, тогда пусто
Условие для ячейки "Лист2!:Лист!6S2" все тоже самое, только с чередованием значений. Например если "Корпусные плиты" содержаться на четырех строк из шести, тогда: Лист1!S2 = Первому Лист2!S2 = Второму Лист3!S2 = Третьему Лист4!S2 = Четвертому совпадению А Лист5!S2 уже ищет текст "Фасадные плиты" = Первая совпадения Лист6!S2 = Вторая совпадения
В общем аналогия сортировки списка с разнесением по листам
Задачка такая: В книге "Лист с данными" имеется список для передачи на другие листы по условию.
Условие для ячейки "Лист1!S2" 1 • Если список содержит текст "Корпусные плиты" тогда первое совпадение. 2 • Если не содержит, тогда ищем "Фасадные плиты" и первое совпадение. 3 • Если и того и другого нет, тогда пусто
Условие для ячейки "Лист2!:Лист!6S2" все тоже самое, только с чередованием значений. Например если "Корпусные плиты" содержаться на четырех строк из шести, тогда: Лист1!S2 = Первому Лист2!S2 = Второму Лист3!S2 = Третьему Лист4!S2 = Четвертому совпадению А Лист5!S2 уже ищет текст "Фасадные плиты" = Первая совпадения Лист6!S2 = Вторая совпадения
В общем аналогия сортировки списка с разнесением по листамSobirjon
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+5;33));1))&""
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
Так нужно?
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+5;33));1))&""
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter_Boroda_
_Boroda_, Что то не могу разобраться, когда пытаюсь перенести на рабочую книгу, формула сбивается. Заметил что, сбивается если названия листа не Лист1, Лист2 ... У меня они называются Plates (1), Plates (2).... В формуле не могу найти где участвуют их названия :help:
_Boroda_, Что то не могу разобраться, когда пытаюсь перенести на рабочую книгу, формула сбивается. Заметил что, сбивается если названия листа не Лист1, Лист2 ... У меня они называются Plates (1), Plates (2).... В формуле не могу найти где участвуют их названия :help:Sobirjon
Сообщение отредактировал Sobirjon - Суббота, 20.07.2019, 05:47
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("(";ЯЧЕЙКА("имяфайла";A1))+1;1));1))&""
для любого количества листов
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("(";ЯЧЕЙКА("имяфайла";A1))+1;33);")";));1))&""
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("(";ЯЧЕЙКА("имяфайла";A1))+1;1));1))&""
для любого количества листов
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("(";ЯЧЕЙКА("имяфайла";A1))+1;33);")";));1))&""
В целом книге или именно листы которые содержат данную формулу? Обидно, что ни как не разгадал причину, почему формула перестает работать выложенный _Boroda_,
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+5;33));1))&""
когда листы называются по другому. Сверил с Вашим, отличается только +5;33 от вашей +1;1 и +1;33....... Сегодня ночью спать не буду :facepalm:
В целом книге или именно листы которые содержат данную формулу? Обидно, что ни как не разгадал причину, почему формула перестает работать выложенный _Boroda_,
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+5;33));1))&""
когда листы называются по другому. Сверил с Вашим, отличается только +5;33 от вашей +1;1 и +1;33....... Сегодня ночью спать не буду :facepalm:Sobirjon
дает полное имя файла с путем и листом. Примерно вот так "G:\Моя\Стереть\[4463481_1 (1).xlsx]Лист1" 2. С помощью ПОИСК("]" мы ищем, на какой позиции у нас находится "]", добавляем к этой позиции 5 3. И с помощью ПСТР берем из п.1. все то, что находится правее позиции из п.2. Сначала лист назывался "Лист1". Нам оттуда нужно забрать только 1, а "Лист" - лишнее. В этом лишнем 4 символа, да плюс еще сама позиция "]" - получится 5 (ее-то мы и прибавляем в п.2) А теперь лист называется "Plates (1)". Будем отбрасывать кусок "Plates " - это 7 символов, плюс 1 - получаем 8. Вспоминаем, что в Excel есть автопреобразование. В частности, число в скобках само преобразуется в отрицательное число, то есть (1) = -1. Поэтому, когда мы отсекаем из "Plates (1)" кусок "Plates " и получаем текст "(1)", то Excel думает, что это минус единица. Чтобы сделать из нее обычную единицу, ставим перед ПСТР минус Итог, аналогичный второй формуле Елены
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);-ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+8;33));1))&""
Кусок {1;10} и единичка второго аргумента ЛЕВБ говорят нам о том, что листов (и значений в таблице 'Лист с данными'!L2:L7) должно быть не большеменьше 9. Если больше (до 99, например), то в ЛЕББ второй аргумент должен быть 2, а кусок *{1;10};99 переписываем так *{1;100};9999 Или можно изменить логику, но это уже наверное не так уж и нужно.
Все очень просто 1. Кусок
Код
ЯЧЕЙКА("имяфайла";A1)
дает полное имя файла с путем и листом. Примерно вот так "G:\Моя\Стереть\[4463481_1 (1).xlsx]Лист1" 2. С помощью ПОИСК("]" мы ищем, на какой позиции у нас находится "]", добавляем к этой позиции 5 3. И с помощью ПСТР берем из п.1. все то, что находится правее позиции из п.2. Сначала лист назывался "Лист1". Нам оттуда нужно забрать только 1, а "Лист" - лишнее. В этом лишнем 4 символа, да плюс еще сама позиция "]" - получится 5 (ее-то мы и прибавляем в п.2) А теперь лист называется "Plates (1)". Будем отбрасывать кусок "Plates " - это 7 символов, плюс 1 - получаем 8. Вспоминаем, что в Excel есть автопреобразование. В частности, число в скобках само преобразуется в отрицательное число, то есть (1) = -1. Поэтому, когда мы отсекаем из "Plates (1)" кусок "Plates " и получаем текст "(1)", то Excel думает, что это минус единица. Чтобы сделать из нее обычную единицу, ставим перед ПСТР минус Итог, аналогичный второй формуле Елены
Код
=ИНДЕКС('Лист с данными'!L:L;ЛЕВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСК({"Корпусные плиты";"Фасадные плиты"};'Лист с данными'!L2:L7)^0*СТРОКА('Лист с данными'!L2:L7)*{1;10};99);-ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+8;33));1))&""
Кусок {1;10} и единичка второго аргумента ЛЕВБ говорят нам о том, что листов (и значений в таблице 'Лист с данными'!L2:L7) должно быть не большеменьше 9. Если больше (до 99, например), то в ЛЕББ второй аргумент должен быть 2, а кусок *{1;10};99 переписываем так *{1;100};9999 Или можно изменить логику, но это уже наверное не так уж и нужно._Boroda_
Строки с наименованием увеличил до 10. У меня почему-то начинается повторении. Пример прикладываю, для удобства вычисление оставил только на одном листе.
_Boroda_, Буду очень благодарен, если поможете разобраться в части кода, как всё же работает отбор. Когда добавляю еще 2 условия Например
Строки с наименованием увеличил до 10. У меня почему-то начинается повторении. Пример прикладываю, для удобства вычисление оставил только на одном листе.Sobirjon
Правильно. Когде значения закончились, то ПОИСК дает ошибку, ЕСЛИОШИБКА преобразовывает ее в 999999, ЛЕВБ берет левый символ - девятку - а ИНДЕКС показывает нам значение из этой девятой строки Если у Вас ячейка К1 пуста, то можно так
даст массив {2;10000;10000;10000:10000;30;10000;10000:10000;10000;10000;4000:10000;10000;500;10000:10000;10000;10000;6000:7;10000;10000;10000:10000;80;10000;10000:10000;10000;900;10000} девятый наименьший - 10000, его первый символ - 1, ИНДЕКСом вынимаем ячейку К1, в ней пусто
Если же последняя заполненная строка в столбце К больше 9, но меньше 10, то {1;10;100;1000} должно быть уже кратно 100 - {1;100;10000;1000000}, а второй кусок у ЕСЛИОШИБКА еще на 0 больше- 10000000
* Формула создавалась под конкретный пример с условием ограниченного кол-ва ячеек и листов. Для общего случая должна быть более другая формула, в которой там не как тут
Правильно. Когде значения закончились, то ПОИСК дает ошибку, ЕСЛИОШИБКА преобразовывает ее в 999999, ЛЕВБ берет левый символ - девятку - а ИНДЕКС показывает нам значение из этой девятой строки Если у Вас ячейка К1 пуста, то можно так
даст массив {2;10000;10000;10000:10000;30;10000;10000:10000;10000;10000;4000:10000;10000;500;10000:10000;10000;10000;6000:7;10000;10000;10000:10000;80;10000;10000:10000;10000;900;10000} девятый наименьший - 10000, его первый символ - 1, ИНДЕКСом вынимаем ячейку К1, в ней пусто
Если же последняя заполненная строка в столбце К больше 9, но меньше 10, то {1;10;100;1000} должно быть уже кратно 100 - {1;100;10000;1000000}, а второй кусок у ЕСЛИОШИБКА еще на 0 больше- 10000000
* Формула создавалась под конкретный пример с условием ограниченного кол-ва ячеек и листов. Для общего случая должна быть более другая формула, в которой там не как тут_Boroda_
Если будут предложении, буду очень признателен. Сейчас прихожу к выводу, что было бы здорово иметь возможность добавлять условии и менять при необходимости
Если будут предложении, буду очень признателен. Сейчас прихожу к выводу, что было бы здорово иметь возможность добавлять условии и менять при необходимостиSobirjon
_Boroda_, Вложил! Исходные данные находятся DataImport!L2:L21, Критерии DataImport!N2:N11 Расположение критериев не важно, в ячейке, или внутри формулы. Там уж как карты лягут.
_Boroda_, Вложил! Исходные данные находятся DataImport!L2:L21, Критерии DataImport!N2:N11 Расположение критериев не важно, в ячейке, или внутри формулы. Там уж как карты лягут.Sobirjon
_Boroda_, Спасибо, за проявленный интерес. Можете не заморачиваться. Огромное Вам спасибо! Потребность отпадает, всё же пришел к мнению, что задуманное надо реализовать через макрос.
_Boroda_, Спасибо, за проявленный интерес. Можете не заморачиваться. Огромное Вам спасибо! Потребность отпадает, всё же пришел к мнению, что задуманное надо реализовать через макрос.Sobirjon