есть однотипные листы условно лист1, лист2, лист3 и тд
В каждом листе есть 2 определенных одинаковых столбца с нужными мне данными (ну понять хотя бы на 1м, а второй по аналогии)
я хочу собрать данные из этих столбцов в 1 большой единый список (столбец) с уникальными значениями пробовал так =UNIQUE(FLATTEN('Лист1!D7:D;'Лист2'!D7:D)
Но! 1. Листов и столбцов очень много, там будет формула на страницу 2. Выводит в том числе пустые ячейки
хотел сделать список названий листов и попробовать через INDIRECT("'"&F6&"'!D7:D"))), где F6 - ячейка с название листа, но не придумал как это все красиво сделать есть идеи?
Друзья
есть однотипные листы условно лист1, лист2, лист3 и тд
В каждом листе есть 2 определенных одинаковых столбца с нужными мне данными (ну понять хотя бы на 1м, а второй по аналогии)
я хочу собрать данные из этих столбцов в 1 большой единый список (столбец) с уникальными значениями пробовал так =UNIQUE(FLATTEN('Лист1!D7:D;'Лист2'!D7:D)
Но! 1. Листов и столбцов очень много, там будет формула на страницу 2. Выводит в том числе пустые ячейки
хотел сделать список названий листов и попробовать через INDIRECT("'"&F6&"'!D7:D"))), где F6 - ячейка с название листа, но не придумал как это все красиво сделать есть идеи?yurakhl
Сообщение отредактировал yurakhl - Среда, 24.04.2024, 19:14
хотел сделать список названий листов и попробовать через INDIRECT("'"&F6&"'!D7:D"))), где F6 - ячейка с название листа, но не придумал как это все красиво сделать есть идеи?
Есть. Если список нужных перебираемых рабочих листов поместить в колонку A, то уникальные значения колонок D с этих листов можно получить по формуле: [vba]
[/vba] Список имен рабочих листов предполагается в диапазоне A1:A20 (переменная sheetlist). Границы диапазона можно отредактировать по своей потребности.
[p.s.]Если выделение некоторых переменных в формуле кажется чрезмерным (или надуманным), то можно и подсократиться, без особых потерь прозрачности:[/p.s.] [vba]
хотел сделать список названий листов и попробовать через INDIRECT("'"&F6&"'!D7:D"))), где F6 - ячейка с название листа, но не придумал как это все красиво сделать есть идеи?
Есть. Если список нужных перебираемых рабочих листов поместить в колонку A, то уникальные значения колонок D с этих листов можно получить по формуле: [vba]
[/vba] Список имен рабочих листов предполагается в диапазоне A1:A20 (переменная sheetlist). Границы диапазона можно отредактировать по своей потребности.
[p.s.]Если выделение некоторых переменных в формуле кажется чрезмерным (или надуманным), то можно и подсократиться, без особых потерь прозрачности:[/p.s.] [vba]
и еще вопрос, можно ли как то в списке отделить 1 лист от другого
Т.е. идет колонка Лист1 A B C D E Лист2 F G H и высший пилотаж это чтобы эти заголовки были в колонке на 1 левее
и еще я хочу к этим значениям привязать статусы построчно т.е. А может быть в статусе X и Н Б может быть в статусе Y и Z
самый очевидный способ это когда все листы будут заполнены и массив сформирован, просто скопировать и вставить значения но это грустный вариант, т.к не будут меняться значения при изменении информации на листах
Спасибо большое! прошу прощения за новую тему. в той мне показалось некорректно оформил задачу
1 вопрос, если необходимых столбцов несклько, то я просто делаю новые переменные
и еще вопрос, можно ли как то в списке отделить 1 лист от другого
Т.е. идет колонка Лист1 A B C D E Лист2 F G H и высший пилотаж это чтобы эти заголовки были в колонке на 1 левее
и еще я хочу к этим значениям привязать статусы построчно т.е. А может быть в статусе X и Н Б может быть в статусе Y и Z
самый очевидный способ это когда все листы будут заполнены и массив сформирован, просто скопировать и вставить значения но это грустный вариант, т.к не будут меняться значения при изменении информации на листахyurakhl
Сообщение отредактировал yurakhl - Четверг, 25.04.2024, 09:49
Живой пример, пожалуйста, показывайте! И не скриншоты, а именно таблицу, с общим доступом хотя бы на просмотр.
Сделайте два-три листа с данными (по 5-6 строчек, по 3-4 колонки) и одним итоговым, на котором "нарисуйте" вручную то, что хотите видеть в результате. Если данные в оригинале конфиденциальные, то замените их на какие-нибудь придуманные (Товар 1, Товар 2, Товар 3 и т.д.)
Без примера с данными, которыми можно поиграться в процессе создания окончательной формулы, дальнейший разговор бесперспективен. А так - да, всё, о чём спрашиваете, в принципе реализуемо: и "прокладки" в списке значений типа "Лист1", "Лист2"; и заголовки в колонке "левее" - в колонке "левее" можно просто выводить имя листа, с которого "приехало" значение; и т.д., и т.п.
Живой пример, пожалуйста, показывайте! И не скриншоты, а именно таблицу, с общим доступом хотя бы на просмотр.
Сделайте два-три листа с данными (по 5-6 строчек, по 3-4 колонки) и одним итоговым, на котором "нарисуйте" вручную то, что хотите видеть в результате. Если данные в оригинале конфиденциальные, то замените их на какие-нибудь придуманные (Товар 1, Товар 2, Товар 3 и т.д.)
Без примера с данными, которыми можно поиграться в процессе создания окончательной формулы, дальнейший разговор бесперспективен. А так - да, всё, о чём спрашиваете, в принципе реализуемо: и "прокладки" в списке значений типа "Лист1", "Лист2"; и заголовки в колонке "левее" - в колонке "левее" можно просто выводить имя листа, с которого "приехало" значение; и т.д., и т.п.Gustav
ВОПРОСЫ: Одни и те же названия документов могут встречаться на разных листах? Т.е. уникальность какая имеется в виду - только по "Названию документа" или по паре "Имя листа + Названию документа" ?
В названии листа "WBS Инженерная подготовка ЦУ" нет точки после WBS - должна быть или нет?
ВОПРОСЫ: Одни и те же названия документов могут встречаться на разных листах? Т.е. уникальность какая имеется в виду - только по "Названию документа" или по паре "Имя листа + Названию документа" ?
В названии листа "WBS Инженерная подготовка ЦУ" нет точки после WBS - должна быть или нет?Gustav
Вот у меня получаются такие дублирования записей с разных листов: [vba]
Код
Лист Название документа ==================================
ИРД после НС Акт итоговой проверки, ЗОС Сети после НС Акт итоговой проверки, ЗОС
ИРД после НС Акт о расторжении договора врменного ресурсоснабжения Сети после НС Акт о расторжении договора врменного ресурсоснабжения
ИРД после НС Акт приема-передачи сетей Сети после НС Акт приема-передачи сетей
Сети до НС Альбом концепции Проектирование ОС Альбом концепции
ИРД до НС Альбом Концепций ИРД до НС Альбом концепций
[/vba] Так я должен оставить в списке только первую строку из этих пар, потому что она встретилась раньше, так? Кстати, обратите внимание на последнюю пару: слово "концепций" отличается регистром первой буквы - тем не менее, UNIQUE считает это разными уникальными значениями.
Я в ваш файл добавил отладочный лист "Уникальные пары Лист+Документ" и вывел на него текущий список, как его рассчитывает формула. Предлагаю сначала отладить список на этом листе, а потом уже перейти к колонкам статусов. Формула на текущий момент выглядит вот так: [vba]
[/vba] Подсмотрел у профи начальный символ подчеркивания в именах переменных. Хорошая практика! Во-первых, переменная (а не функция или именованный диапазон) сразу видна. Во-вторых, при таком синтаксисе можно не бояться, что оставшаяся часть имени совпадет с адресом ячейки, чего функция LET не разрешает: нельзя назвать переменную s1, но можно _s1 (или s_1 или s1_).
Вот у меня получаются такие дублирования записей с разных листов: [vba]
Код
Лист Название документа ==================================
ИРД после НС Акт итоговой проверки, ЗОС Сети после НС Акт итоговой проверки, ЗОС
ИРД после НС Акт о расторжении договора врменного ресурсоснабжения Сети после НС Акт о расторжении договора врменного ресурсоснабжения
ИРД после НС Акт приема-передачи сетей Сети после НС Акт приема-передачи сетей
Сети до НС Альбом концепции Проектирование ОС Альбом концепции
ИРД до НС Альбом Концепций ИРД до НС Альбом концепций
[/vba] Так я должен оставить в списке только первую строку из этих пар, потому что она встретилась раньше, так? Кстати, обратите внимание на последнюю пару: слово "концепций" отличается регистром первой буквы - тем не менее, UNIQUE считает это разными уникальными значениями.
Я в ваш файл добавил отладочный лист "Уникальные пары Лист+Документ" и вывел на него текущий список, как его рассчитывает формула. Предлагаю сначала отладить список на этом листе, а потом уже перейти к колонкам статусов. Формула на текущий момент выглядит вот так: [vba]
[/vba] Подсмотрел у профи начальный символ подчеркивания в именах переменных. Хорошая практика! Во-первых, переменная (а не функция или именованный диапазон) сразу видна. Во-вторых, при таком синтаксисе можно не бояться, что оставшаяся часть имени совпадет с адресом ячейки, чего функция LET не разрешает: нельзя назвать переменную s1, но можно _s1 (или s_1 или s1_).Gustav
Ну, в общем, я то, что хотел с этой формулой сделать на данном этапе - сделал. Проверяйте, высказывайтесь. Кстати, по ходу выяснилось, что у вас там местами в названиях документов присутствую концевые пробелы - по одному, а то и по два. Они, конечно, повлияли на результаты уникализации и, увы, не лучшим образом. Поэтому не удивляйтесь в ряде случаев неожиданным результатам. И хорошо бы все такие пробелы искоренить (в процессе искоренения результат формулы будет, естественно, автоматически исправляться).
Текущая редакция формулы во всей своей красе выглядит так: [vba]
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
_rem1; "------- внутренний 'цикл' по диапазонам одного листа -------"; _rgarr; REDUCE(; _rangelist; LAMBDA(_rgtot; _range; LET( _dir; "'WBS." & _sheet & "'!" & _range; _ref; INDIRECT(_dir); VSTACK(_rgtot; IFNA(FILTER(_ref; _ref<>""))) ))); _rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) ))); _rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>"")));
_rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE);
_rem6; "------- сравнение 2-х копий колонки 'Название документа' с вертикальным сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
_rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE);
_rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
_sharr6 )
[/vba] В Ваш файл на лист "Уникальные пары Лист+Документ" я ее зарядил - как "Вариант 1". Плюс к этому добавил на этот же лист еще 2 варианта представления списка. Надеюсь, один из них подойдёт (скажите, какой).
Ну, в общем, я то, что хотел с этой формулой сделать на данном этапе - сделал. Проверяйте, высказывайтесь. Кстати, по ходу выяснилось, что у вас там местами в названиях документов присутствую концевые пробелы - по одному, а то и по два. Они, конечно, повлияли на результаты уникализации и, увы, не лучшим образом. Поэтому не удивляйтесь в ряде случаев неожиданным результатам. И хорошо бы все такие пробелы искоренить (в процессе искоренения результат формулы будет, естественно, автоматически исправляться).
Текущая редакция формулы во всей своей красе выглядит так: [vba]
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
_rem1; "------- внутренний 'цикл' по диапазонам одного листа -------"; _rgarr; REDUCE(; _rangelist; LAMBDA(_rgtot; _range; LET( _dir; "'WBS." & _sheet & "'!" & _range; _ref; INDIRECT(_dir); VSTACK(_rgtot; IFNA(FILTER(_ref; _ref<>""))) ))); _rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) ))); _rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>"")));
_rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE);
_rem6; "------- сравнение 2-х копий колонки 'Название документа' с вертикальным сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
_rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE);
_rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
_sharr6 )
[/vba] В Ваш файл на лист "Уникальные пары Лист+Документ" я ее зарядил - как "Вариант 1". Плюс к этому добавил на этот же лист еще 2 варианта представления списка. Надеюсь, один из них подойдёт (скажите, какой).Gustav
Вариант 2 самый подходящий! Единственное я писал выше что уникальность ОБЩАЯ. т.е. Выписка ЕГРН должна встретиться всего 1 раз. И если на следующем листе она снова будет ее уже не писать.
Можно я распишу что я понял я что нет, а вы мне поясните что просиходит? ))))
Цитата
[=LET( _sheetlist; 'Реестр WBS'!E6:E17;
Обозвали диапазон с названиями листов шитлист.(у меня в оригинальном файле другое название листа со списком) Ок ясно
Обозвали диапазоны с нужными колонками рейнджлист. Понятно
Цитата
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
Обозвали наш «главный" массив shARR – ок понял. Почему в первом аргументе {""\""} – что это дает? Вообще фигурные скобки в аргументе массива не понимаю. Это сдвиг куда то? Или что?
Тут мозг вообще взрывается. Мы ВНУТРИ функции reduсe, в ее лямбде делаем новую функцию reduсe. Но сам REDUCE понятен. Мы берем список листов, и перебираем их по очереди, подставляя в индирект, а потом через VSTACK делаем вертикальный массив и фильтруем от пустых и Н/А
Цитата
_rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell})); VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) )));
Берем rgarr из предыдущего шага. А в лямбде что значит {_sheet \ _cell} ???? И почему в FILTER(_shrgarr; _rgarr<>"") Т.е. не могу понять принцип фильтрации. В тех строках где rgarr не пустой?. А у нас массивы не наложатся в 1 колонку? Т.е. в принципе не понятно мне каждый новый массив он в новую колонку идет? потому что дальше мы делаем еще нумерацию и тд, и потом обрезаем 2 первых колонки, а когда у нас их стало больше я не понимаю
Цитата
_rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>"")));
Ен могу понять фильтрацию. Мы берем вторую сколонку где "не пустые" массива sharr??? Называем обновленный массив _sharr2 – понятно
Цитата
_rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE);
Называем обновленный массив _sharr3 – понятно Сортируем по 2ой и потом по 3ей колонке – понятно
Внутри функции сорт опять в фигурных скобках непонятная штука. Мы нумеруем все строки по порядку. Не могу понять синтаксис и нумерация происходит до того или после того как идет сортировка. И куда записываются эти номера чтобы потом их вернуть - в новый столбец?
Цитата
_rem6; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
Вообще не понятно, особенно аргумент ({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""};
Цитата
_rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE);
Фильруем 3ий массив, там где у 4го единички (это номера из SEQUENCE я так понял). Не могу понять а список не уменьшится? те. разве фильтр не сократит число строк в массиве?
Цитата
_rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
Это понятно, просто обрезаем
Цитата
_rem9; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1); {_sharr7 \ INDEX(_sharr6;;2)} )
Тут все надо объяснять )))
Да отлично! Спасибо огромное
Вариант 2 самый подходящий! Единственное я писал выше что уникальность ОБЩАЯ. т.е. Выписка ЕГРН должна встретиться всего 1 раз. И если на следующем листе она снова будет ее уже не писать.
Можно я распишу что я понял я что нет, а вы мне поясните что просиходит? ))))
Цитата
[=LET( _sheetlist; 'Реестр WBS'!E6:E17;
Обозвали диапазон с названиями листов шитлист.(у меня в оригинальном файле другое название листа со списком) Ок ясно
Обозвали диапазоны с нужными колонками рейнджлист. Понятно
Цитата
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
Обозвали наш «главный" массив shARR – ок понял. Почему в первом аргументе {""\""} – что это дает? Вообще фигурные скобки в аргументе массива не понимаю. Это сдвиг куда то? Или что?
Тут мозг вообще взрывается. Мы ВНУТРИ функции reduсe, в ее лямбде делаем новую функцию reduсe. Но сам REDUCE понятен. Мы берем список листов, и перебираем их по очереди, подставляя в индирект, а потом через VSTACK делаем вертикальный массив и фильтруем от пустых и Н/А
Цитата
_rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell})); VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) )));
Берем rgarr из предыдущего шага. А в лямбде что значит {_sheet \ _cell} ???? И почему в FILTER(_shrgarr; _rgarr<>"") Т.е. не могу понять принцип фильтрации. В тех строках где rgarr не пустой?. А у нас массивы не наложатся в 1 колонку? Т.е. в принципе не понятно мне каждый новый массив он в новую колонку идет? потому что дальше мы делаем еще нумерацию и тд, и потом обрезаем 2 первых колонки, а когда у нас их стало больше я не понимаю
Цитата
_rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>"")));
Ен могу понять фильтрацию. Мы берем вторую сколонку где "не пустые" массива sharr??? Называем обновленный массив _sharr2 – понятно
Цитата
_rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE);
Называем обновленный массив _sharr3 – понятно Сортируем по 2ой и потом по 3ей колонке – понятно
Внутри функции сорт опять в фигурных скобках непонятная штука. Мы нумеруем все строки по порядку. Не могу понять синтаксис и нумерация происходит до того или после того как идет сортировка. И куда записываются эти номера чтобы потом их вернуть - в новый столбец?
Цитата
_rem6; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
Вообще не понятно, особенно аргумент ({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""};
Цитата
_rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE);
Фильруем 3ий массив, там где у 4го единички (это номера из SEQUENCE я так понял). Не могу понять а список не уменьшится? те. разве фильтр не сократит число строк в массиве?
Цитата
_rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
Это понятно, просто обрезаем
Цитата
_rem9; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1); {_sharr7 \ INDEX(_sharr6;;2)} )
Единственное я писал выше что уникальность ОБЩАЯ. т.е. Выписка ЕГРН должна встретиться всего 1 раз. И если на следующем листе она снова будет ее уже не писать.
Разумеется, это всё уже учтено.
Ну, и прежде, чем начну отвечать на Ваши вопросы, которые, к слову сказать, очень хороши (спасибо!), для наглядности приведу здесь полную формулу для принятого варианта 2. А то Вы ее в своих вопросах уже порезали на цитаты, а целиком, возможно, не все наши читатели ее видели. Да и мне так будет удобнее ее комментировать.
Итак, одобренная заказчиком формула - для варианта 2 )): [vba]
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
_rem1; "------- внутренний 'цикл' по диапазонам одного листа -------"; _rgarr; REDUCE(; _rangelist; LAMBDA(_rgtot; _range; LET( _dir; "'WBS." & _sheet & "'!" & _range; _ref; INDIRECT(_dir); VSTACK(_rgtot; IFNA(FILTER(_ref; _ref<>""))) ))); _rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) ))); _rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>""))); _rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE); _rem6; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1); _rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE); _rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2); _rem9; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1); {_sharr7 \ INDEX(_sharr6;;2)} )
Единственное я писал выше что уникальность ОБЩАЯ. т.е. Выписка ЕГРН должна встретиться всего 1 раз. И если на следующем листе она снова будет ее уже не писать.
Разумеется, это всё уже учтено.
Ну, и прежде, чем начну отвечать на Ваши вопросы, которые, к слову сказать, очень хороши (спасибо!), для наглядности приведу здесь полную формулу для принятого варианта 2. А то Вы ее в своих вопросах уже порезали на цитаты, а целиком, возможно, не все наши читатели ее видели. Да и мне так будет удобнее ее комментировать.
Итак, одобренная заказчиком формула - для варианта 2 )): [vba]
_rem3; "------- внешний 'цикл' по заданному списку листов ----------"; _sharr; REDUCE({""\""}; _sheetlist; LAMBDA(_shtot; _sheet; LET(
_rem1; "------- внутренний 'цикл' по диапазонам одного листа -------"; _rgarr; REDUCE(; _rangelist; LAMBDA(_rgtot; _range; LET( _dir; "'WBS." & _sheet & "'!" & _range; _ref; INDIRECT(_dir); VSTACK(_rgtot; IFNA(FILTER(_ref; _ref<>""))) ))); _rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
VSTACK(_shtot; IFNA(FILTER(_shrgarr; _rgarr<>""))) ))); _rem4; "------- уникализация пар 'Имя листа + Название документа' --"; _sharr2; UNIQUE(IFNA(FILTER(_sharr; INDEX(_sharr;;2)<>""))); _rem5; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; SORT({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; 2;TRUE; 3;TRUE); _rem6; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1); _rem7; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;3;TRUE); _rem8; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2); _rem9; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1); {_sharr7 \ INDEX(_sharr6;;2)} )
Вообще фигурные скобки в аргументе массива не понимаю. Это сдвиг куда то? Или что?
Фигурные скобки { } обозначают массивы. Так же, как и в Excel, внутри формул ГТ можно создавать массивы как списки значений, заключенные между фигурными скобками { }. Разделителями элементов массива могут быть (для локали RU): * Обратный слэш \ - позволяет разделять данные по столбцам при записи строки в массиве. В примере ={1\2} цифра 1 будет помещена в первую ячейку, а цифра 2 – в соседнюю ячейку справа ("горизонтальный" массив). * Точка с запятой ; - отделяет строки при записи столбцов в массив. В примере ={1; 2} цифра 1 будет помещена в первую ячейку, а цифра 2 – в соседнюю ячейку под ней ("вертикальным" массив). Примечание. В тех странах, где десятичным разделителем служит точка, при создании массива используется не обратный слэш, а запятая.
С появлением функций LET и LAMBDA и возможностью использования внутри них переменных, эти переменные стали эффективно использоваться и внутри фигурных скобок, выведя операции с массивами на новый "духозахватывающий" уровень. Например, если имеется массив _arr1, содержащий 5 строк х 4 столбца, и массив _arr2, содержащий 5 строк х 3 столбца, то операция горизонтального объединения этих массивов посредством обратного слэша {_arr1 \ _arr2} создаст новый массив, содержащий 5 строк x 7 столбцов. Если же имеется массив _arr3, содержащий 7 строк х 5 столбцов, и массив _arr4, содержащий 3 строки х 5 столбцов, то операция вертикального объединения этих массивов посредством точки с запятой {_arr3 ; _arr4} создаст новый массив, содержащий 10 строк x 5 столбцов. Как можно заметить, при горизонтальном соединении массивы должны иметь одинаковое количество строк, а при вертикальном - одинаковое количество столбцов. При невыполнении этих условий возникает ошибка.
Для объединения массивов также можно использовать и специальные функции (появились много позже фигурных скобок): * HSTACK - для горизонтального объединения. В русскоязычном Excel соответствующая функция называется ГСТОЛБИК. * VSTACK - для вертикального объединения. В русскоязычном Excel - ВСТОЛБИК.
Это начальное значение функции REDUCE, так называемый "аккумулятор" или "сумматор" или "накопитель" - в зависимости от характера основной операции, выполняемой во встроенной функции LAMBDA. Если происходит арифметическое суммирование элементов массива (второй параметр функции REDUCE), то в простейшем случае каждое следующее взятое число (предположим, что массив - числовой) прибавляется к сумматору, увеличивая его текущее (суммарное) значение. После прибавления последнего элемента это суммарное значение и становится результатом выполнения функции REDUCE. Исходное же значение (до сложения первого элемента массива) и задается первым параметром функции REDUCE. Для чисел обычно это 0, но может быть и не 0.
Если же REDUCE выполняет нечто вроде слияния текстовых строк (конкатенации), то начальным значением для такого случая тоже должна быть текстовая строка. Обычно это пустая строка "", но может быть и не пустой, а, скажем, некоторой начальной фразой предложения.
В массиве _sharr в нашей формуле происходит "сложное" накопление пар {Имя_листа \ Название_документа}, т.е. формирование набора горизонтальных строк (массивов) из двух элементов. В качестве начального значения для такого набора (а по сути вертикального массива) не может быть выбрано простое одиночное значение (числовое 0 или текстовое ""), но вполне может быть выбран горизонтальный массив из двух пустых текстовых значений {""\""}.
Берем rgarr из предыдущего шага. А в лямбде что значит {_sheet \ _cell} ????
После рассуждений выше о фигурных скобках и массивах здесь уже всё совсем просто. Смотрим на фрагмент: [vba]
Код
_rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
[/vba] Функция MAP обрабатывает массив _rgarr, который содержит названия документов из диапазонов только что обработанного листа (еще не уникальные, но уже без пустых ячеек). Внутри функции LAMBDA переменная _cell представляет собой текущий обрабатываемый элемент массива, т.е. одно название документа. Переменная _sheet поступает извне и внутри функции MAP не меняется. Она содержит имя только что обработанного листа. Соответственно выражение {_sheet \ _cell} фактически представляет собой горизонтальную строку из двух элементов {Имя_листа \ Название_документа}. После отработки функции MAP переменная _shrgarr содержит двумерный массив из 2 столбцов и строк в количестве, равном количеству элементов массива _rgarr, которое равно количеству всех непустых названий документов, собранных на текущем листе из 6 заданных диапазонов, перечисленных в массиве _rangelist.
И почему в FILTER(_shrgarr; _rgarr<>"") Т.е. не могу понять принцип фильтрации. В тех строках где rgarr не пустой?
Данная функция FILTER возвращает строки массива _shrgarr, у которых не пуста вторая колонка, т.е."Название документа". По-другому это выражение можно было бы записать как: [vba]
Код
FILTER(_shrgarr; INDEX(_shrgarr;;2)<>"")
[/vba] Но зачем так усложнять, если мы еще помним, что только что в функции MAP приставляли имя листа слева от названий документов, содержащихся в массиве _rgarr. Данная фильтрация здесь предназначена в основном для "удаления" именно начальных пустых строк "внешней" (по листам) функции REDUCE {""\""} или одиночной пустой строки "" "внутренней" (по 6 диапазонам) функции REDUCE.
Вообще фигурные скобки в аргументе массива не понимаю. Это сдвиг куда то? Или что?
Фигурные скобки { } обозначают массивы. Так же, как и в Excel, внутри формул ГТ можно создавать массивы как списки значений, заключенные между фигурными скобками { }. Разделителями элементов массива могут быть (для локали RU): * Обратный слэш \ - позволяет разделять данные по столбцам при записи строки в массиве. В примере ={1\2} цифра 1 будет помещена в первую ячейку, а цифра 2 – в соседнюю ячейку справа ("горизонтальный" массив). * Точка с запятой ; - отделяет строки при записи столбцов в массив. В примере ={1; 2} цифра 1 будет помещена в первую ячейку, а цифра 2 – в соседнюю ячейку под ней ("вертикальным" массив). Примечание. В тех странах, где десятичным разделителем служит точка, при создании массива используется не обратный слэш, а запятая.
С появлением функций LET и LAMBDA и возможностью использования внутри них переменных, эти переменные стали эффективно использоваться и внутри фигурных скобок, выведя операции с массивами на новый "духозахватывающий" уровень. Например, если имеется массив _arr1, содержащий 5 строк х 4 столбца, и массив _arr2, содержащий 5 строк х 3 столбца, то операция горизонтального объединения этих массивов посредством обратного слэша {_arr1 \ _arr2} создаст новый массив, содержащий 5 строк x 7 столбцов. Если же имеется массив _arr3, содержащий 7 строк х 5 столбцов, и массив _arr4, содержащий 3 строки х 5 столбцов, то операция вертикального объединения этих массивов посредством точки с запятой {_arr3 ; _arr4} создаст новый массив, содержащий 10 строк x 5 столбцов. Как можно заметить, при горизонтальном соединении массивы должны иметь одинаковое количество строк, а при вертикальном - одинаковое количество столбцов. При невыполнении этих условий возникает ошибка.
Для объединения массивов также можно использовать и специальные функции (появились много позже фигурных скобок): * HSTACK - для горизонтального объединения. В русскоязычном Excel соответствующая функция называется ГСТОЛБИК. * VSTACK - для вертикального объединения. В русскоязычном Excel - ВСТОЛБИК.
Это начальное значение функции REDUCE, так называемый "аккумулятор" или "сумматор" или "накопитель" - в зависимости от характера основной операции, выполняемой во встроенной функции LAMBDA. Если происходит арифметическое суммирование элементов массива (второй параметр функции REDUCE), то в простейшем случае каждое следующее взятое число (предположим, что массив - числовой) прибавляется к сумматору, увеличивая его текущее (суммарное) значение. После прибавления последнего элемента это суммарное значение и становится результатом выполнения функции REDUCE. Исходное же значение (до сложения первого элемента массива) и задается первым параметром функции REDUCE. Для чисел обычно это 0, но может быть и не 0.
Если же REDUCE выполняет нечто вроде слияния текстовых строк (конкатенации), то начальным значением для такого случая тоже должна быть текстовая строка. Обычно это пустая строка "", но может быть и не пустой, а, скажем, некоторой начальной фразой предложения.
В массиве _sharr в нашей формуле происходит "сложное" накопление пар {Имя_листа \ Название_документа}, т.е. формирование набора горизонтальных строк (массивов) из двух элементов. В качестве начального значения для такого набора (а по сути вертикального массива) не может быть выбрано простое одиночное значение (числовое 0 или текстовое ""), но вполне может быть выбран горизонтальный массив из двух пустых текстовых значений {""\""}.
Берем rgarr из предыдущего шага. А в лямбде что значит {_sheet \ _cell} ????
После рассуждений выше о фигурных скобках и массивах здесь уже всё совсем просто. Смотрим на фрагмент: [vba]
Код
_rem2; "------- дописывание имени листа перед названием документа --"; _shrgarr; MAP(_rgarr; LAMBDA(_cell; {_sheet \ _cell}));
[/vba] Функция MAP обрабатывает массив _rgarr, который содержит названия документов из диапазонов только что обработанного листа (еще не уникальные, но уже без пустых ячеек). Внутри функции LAMBDA переменная _cell представляет собой текущий обрабатываемый элемент массива, т.е. одно название документа. Переменная _sheet поступает извне и внутри функции MAP не меняется. Она содержит имя только что обработанного листа. Соответственно выражение {_sheet \ _cell} фактически представляет собой горизонтальную строку из двух элементов {Имя_листа \ Название_документа}. После отработки функции MAP переменная _shrgarr содержит двумерный массив из 2 столбцов и строк в количестве, равном количеству элементов массива _rgarr, которое равно количеству всех непустых названий документов, собранных на текущем листе из 6 заданных диапазонов, перечисленных в массиве _rangelist.
И почему в FILTER(_shrgarr; _rgarr<>"") Т.е. не могу понять принцип фильтрации. В тех строках где rgarr не пустой?
Данная функция FILTER возвращает строки массива _shrgarr, у которых не пуста вторая колонка, т.е."Название документа". По-другому это выражение можно было бы записать как: [vba]
Код
FILTER(_shrgarr; INDEX(_shrgarr;;2)<>"")
[/vba] Но зачем так усложнять, если мы еще помним, что только что в функции MAP приставляли имя листа слева от названий документов, содержащихся в массиве _rgarr. Данная фильтрация здесь предназначена в основном для "удаления" именно начальных пустых строк "внешней" (по листам) функции REDUCE {""\""} или одиночной пустой строки "" "внутренней" (по 6 диапазонам) функции REDUCE.
тут как было непонятно так и осталось...... Делаем ВЕРТИКАЛЬНЫЙ массив из 3 как бы кусков: пустая строка а затем 2 раза все строки из 2ой колонки (имена документов) из массива sharr3. Не могу понять смысла действия. Объясните что в лямбде делаем? т.е. когда делаем МАР, обычно в лямбде только 2 аргумента, ямя ячейки; и что с ней сделать. а у нас здесь 2 имени и потом странная формула "минус минус(Б не равно А) ".
ну и давайте перейдем ко второму вопросу - как в строку записать возможные статусы, чтобы при добавлении новых значений в массив строки не "уползали" выше или ниже?
еще раз спасибо !!!
у меня по прежнему трещит мозг по поводу размерности массива
в первом reduce мы делаем заранее 2-столбчатый массив:
тут как было непонятно так и осталось...... Делаем ВЕРТИКАЛЬНЫЙ массив из 3 как бы кусков: пустая строка а затем 2 раза все строки из 2ой колонки (имена документов) из массива sharr3. Не могу понять смысла действия. Объясните что в лямбде делаем? т.е. когда делаем МАР, обычно в лямбде только 2 аргумента, ямя ячейки; и что с ней сделать. а у нас здесь 2 имени и потом странная формула "минус минус(Б не равно А) ".
ну и давайте перейдем ко второму вопросу - как в строку записать возможные статусы, чтобы при добавлении новых значений в массив строки не "уползали" выше или ниже?yurakhl
Сообщение отредактировал yurakhl - Суббота, 27.04.2024, 10:28
ну и давайте перейдем ко второму вопросу - как в строку записать возможные статусы, чтобы при добавлении новых значений в массив строки не "уползали" выше или ниже?
Давайте перейдём. Я посмотрел, что в основном сейчас это поле пустует, но кое-где увидел два непустых значения: "СОЗД" и "УТВ". Вот их и выводить в строчку напротив названий документов? А сколько уникальных их всего может быть? 5-6, как Вы показали на листе "что хочу"? Существует утвержденный список всех возможных статусов?
В каком стиле их показывать? Можно так, с перечислением самих значений статусов в строке ( _ означает пустую ячейку): [vba]
ну и давайте перейдем ко второму вопросу - как в строку записать возможные статусы, чтобы при добавлении новых значений в массив строки не "уползали" выше или ниже?
Давайте перейдём. Я посмотрел, что в основном сейчас это поле пустует, но кое-где увидел два непустых значения: "СОЗД" и "УТВ". Вот их и выводить в строчку напротив названий документов? А сколько уникальных их всего может быть? 5-6, как Вы показали на листе "что хочу"? Существует утвержденный список всех возможных статусов?
В каком стиле их показывать? Можно так, с перечислением самих значений статусов в строке ( _ означает пустую ячейку): [vba]
созд и утв - это примеры. Их можно ов расчет не брать
в вашем примере разбивки статусов - с перечислением самих статусов, потому что могут быть документы с уникальными значаниям статуса, я думаю их не будет больше 5 на 1 документ,
созд и утв - это примеры. Их можно ов расчет не брать
в вашем примере разбивки статусов - с перечислением самих статусов, потому что могут быть документы с уникальными значаниям статуса, я думаю их не будет больше 5 на 1 документ,yurakhl
Сообщение отредактировал yurakhl - Суббота, 27.04.2024, 13:23
Разродился формулой с колонками статусов. Представлена в ячейке B3 на новом листе "Уникальные 2". Количество колонок статусов типа "статус 1" рассчитывается и заполняется автоматически по максимальному требуемому количеству статусов.
Формула значительно изменилась в средней части, так что вопросы, наверное, придется задавать заново Выявился лайфхак по изучению "внутренностей" формулы: в исследовательских целях в самую последнюю строку формулы (перед заключительной скобкой функции LET) можно подставлять любую переменную, определенную выше в этой функции LET (внутри формулы об этом присутствует соответствующий комментарий). Так что непременно воспользуйтесь в учебных целях и обязательно на пустом листе, чтобы формулу ничего не ограничивало ни вниз, ни вправо (иначе будет появляться ошибка #REF!), поскольку разные переменные возвращают разный объем данных. Думаю, количество вопросов должно уменьшиться после самостоятельного заглядывания внутрь различных переменных.
Переделка средней части в основном была вызвана следующими обнаруженными обстоятельствами: * функция UNIQUE считает разными одинаковые слова с различиями в регистре, "концепция" и "Концепция" (в то время как, например, функция SUMIF считает их одинаковыми)
* функция SORT при сортировке по возрастанию ставит слова из кириллицы раньше (выше), чем слова из латиницы (т.е. в смешанном списке сначала идут русские слова, а затем иностранные). В принципе, конечно, точно так же работает и ручная сортировка в меню ГТ, но мое Excel-ное воспитание не позволяет с этим смириться (в Excel первой привычно идёт латиница).
Для преодоления указанных обстоятельств было решено временно перевести все названия документов в нижний регистр (с последующим восстановлением), а вместо функций UNIQUE и SORT текстовые строки обрабатывать с помощью функции QUERY с предложениями "group by" и "order by".
Кстати, вычистил в вашем файле концевые пробелы с помощью команды меню "Данные \ Очистка данных \ Удалить пробелы", так что количество этих "шероховатостей" в возращаемом формулой результате должно уменьшиться.
_rem6; "------- формирование массива пустых строк для выравнивания по кол-ву колонок статусов --"; _addemptyarr; ArrayFormula(TRIM(SPLIT(REPT(" ,"; _statcount+1);",")));
_rem7; "------- уникализация сочетаний только 'Название документа' (здесь они еще в нижнем регистре) --"; _sharr24; QUERY(_sharr22; "select Col1, count(Col2) group by Col1 label count(Col2) ''");
_rem9; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; QUERY({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; "order by Col2, Col4");
_remA; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
_remB; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;4;TRUE);
_remC; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
_remD; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1);
_remF; "------- окончательный вид результата с заголовками колонок статусов в нужном количестве _statcount --"; _sharr9; {{""\""\_statheader} ; CHOOSECOLS(_sharr8; 1; 2; SEQUENCE(_statcount; 1; -_statcount))};
_remG; "------- LIFEHACK: в исследовательских целях в следующую строку можно подставлять любую переменную, определенную выше в текущей функции LET --"; _sharr9 )
[/vba]
Разродился формулой с колонками статусов. Представлена в ячейке B3 на новом листе "Уникальные 2". Количество колонок статусов типа "статус 1" рассчитывается и заполняется автоматически по максимальному требуемому количеству статусов.
Формула значительно изменилась в средней части, так что вопросы, наверное, придется задавать заново Выявился лайфхак по изучению "внутренностей" формулы: в исследовательских целях в самую последнюю строку формулы (перед заключительной скобкой функции LET) можно подставлять любую переменную, определенную выше в этой функции LET (внутри формулы об этом присутствует соответствующий комментарий). Так что непременно воспользуйтесь в учебных целях и обязательно на пустом листе, чтобы формулу ничего не ограничивало ни вниз, ни вправо (иначе будет появляться ошибка #REF!), поскольку разные переменные возвращают разный объем данных. Думаю, количество вопросов должно уменьшиться после самостоятельного заглядывания внутрь различных переменных.
Переделка средней части в основном была вызвана следующими обнаруженными обстоятельствами: * функция UNIQUE считает разными одинаковые слова с различиями в регистре, "концепция" и "Концепция" (в то время как, например, функция SUMIF считает их одинаковыми)
* функция SORT при сортировке по возрастанию ставит слова из кириллицы раньше (выше), чем слова из латиницы (т.е. в смешанном списке сначала идут русские слова, а затем иностранные). В принципе, конечно, точно так же работает и ручная сортировка в меню ГТ, но мое Excel-ное воспитание не позволяет с этим смириться (в Excel первой привычно идёт латиница).
Для преодоления указанных обстоятельств было решено временно перевести все названия документов в нижний регистр (с последующим восстановлением), а вместо функций UNIQUE и SORT текстовые строки обрабатывать с помощью функции QUERY с предложениями "group by" и "order by".
Кстати, вычистил в вашем файле концевые пробелы с помощью команды меню "Данные \ Очистка данных \ Удалить пробелы", так что количество этих "шероховатостей" в возращаемом формулой результате должно уменьшиться.
_rem6; "------- формирование массива пустых строк для выравнивания по кол-ву колонок статусов --"; _addemptyarr; ArrayFormula(TRIM(SPLIT(REPT(" ,"; _statcount+1);",")));
_rem7; "------- уникализация сочетаний только 'Название документа' (здесь они еще в нижнем регистре) --"; _sharr24; QUERY(_sharr22; "select Col1, count(Col2) group by Col1 label count(Col2) ''");
_rem9; "------- запоминание исходного порядка (нумерация) и сортировка по 'Название документа' --"; _sharr3; QUERY({_sharr2 \ SEQUENCE(ROWS(_sharr2))}; "order by Col2, Col4");
_remA; "------- сравнение 2-х копий колонки 'Название документа' со сдвигом на 1 элемент --"; _sharr4; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr3;;2)}; {INDEX(_sharr3;;2);""}; LAMBDA(_a; _b; --(_b<>_a))); ROWS(_sharr3); 1);
_remB; "------- оставляем в массиве только первые записи из повторяющихся (с первым встретившимся листом), восстановление исходного порядка --"; _sharr5; SORT(FILTER(_sharr3; _sharr4 = 1) ;4;TRUE);
_remC; "------- оставляем в массиве только 2 первые колонки 'Имя листа + Название документа' --"; _sharr6; ARRAY_CONSTRAIN(_sharr5; ROWS(_sharr5); 2);
_remD; "------- в первой колонке 'Имя листа' стираем повторяющиеся значения, оставляя только первое из них --"; _sharr7; ARRAY_CONSTRAIN(MAP({"";INDEX(_sharr6;;1)}; {INDEX(_sharr6;;1);""}; LAMBDA(_a; _b; IF(_b<>_a;_b;""))); ROWS(_sharr6); 1);
_remF; "------- окончательный вид результата с заголовками колонок статусов в нужном количестве _statcount --"; _sharr9; {{""\""\_statheader} ; CHOOSECOLS(_sharr8; 1; 2; SEQUENCE(_statcount; 1; -_statcount))};
_remG; "------- LIFEHACK: в исследовательских целях в следующую строку можно подставлять любую переменную, определенную выше в текущей функции LET --"; _sharr9 )
1. Спасибо большое за вашу помощь! 2. Если Вам духовно приятно просвещать молодежь, то, несмотря на то, что формула видоизменилась, прошу вас ответить на мои вопросы в просветительских целях. Потому что очень интересно как вы дошли до того, до чего дошли)))) Если нет времени и другие заботы - то конечно не нужно, никаких обид, вы по сути здесь благотворительностью занимаетесь)))))
3. Я видимо криво сформулировал ТЗ, потому что делается не совсем то что я хотел. Давайте я еще раз распишу что я хочу, и решим как быть дальше))
У нас есть листы WBS с названиями документов, эти документы очень часто повторяются, и по сути то, что мы делали изначально, — это убирали все дубли и делали некий общий свод документов с разбивкой по листам - некий справочник, теперь я, находясь на листе со списком этих документов, хочу прописать для каждого документа все его возможные статусы.
В итоге у меня получится лист-справочник. Который содержит Документ Лист(категорию) Его статусы
Т.е. я хочу дополнить статусы к документам НЕ ПЕРЕХОДЯ на другие листы, потому что там документы повторяются внури листа и между листами и к тому же визуально разбросаны по листу и заполнять статусы неудобно
Следующий шаг, я в обратную сторону хочу прописать формулу, которая ограничивала бы выбор статусов для каждого документа на листах WBS.-ках, согласно информации из листа-справочника
в голове примерно =arrayformula(ifna(xlookup(сверяем название документа со справочником и подставляем ему статус))) Только мне нужно не сам статус подставить а выпадающий список, где можно выбрать один из вариантов согласно перечню из справочника
Возможно, у вас есть идея проще, как реализовать это
спасибо
Уважаемый Gustav!
1. Спасибо большое за вашу помощь! 2. Если Вам духовно приятно просвещать молодежь, то, несмотря на то, что формула видоизменилась, прошу вас ответить на мои вопросы в просветительских целях. Потому что очень интересно как вы дошли до того, до чего дошли)))) Если нет времени и другие заботы - то конечно не нужно, никаких обид, вы по сути здесь благотворительностью занимаетесь)))))
3. Я видимо криво сформулировал ТЗ, потому что делается не совсем то что я хотел. Давайте я еще раз распишу что я хочу, и решим как быть дальше))
У нас есть листы WBS с названиями документов, эти документы очень часто повторяются, и по сути то, что мы делали изначально, — это убирали все дубли и делали некий общий свод документов с разбивкой по листам - некий справочник, теперь я, находясь на листе со списком этих документов, хочу прописать для каждого документа все его возможные статусы.
В итоге у меня получится лист-справочник. Который содержит Документ Лист(категорию) Его статусы
Т.е. я хочу дополнить статусы к документам НЕ ПЕРЕХОДЯ на другие листы, потому что там документы повторяются внури листа и между листами и к тому же визуально разбросаны по листу и заполнять статусы неудобно
Следующий шаг, я в обратную сторону хочу прописать формулу, которая ограничивала бы выбор статусов для каждого документа на листах WBS.-ках, согласно информации из листа-справочника
в голове примерно =arrayformula(ifna(xlookup(сверяем название документа со справочником и подставляем ему статус))) Только мне нужно не сам статус подставить а выпадающий список, где можно выбрать один из вариантов согласно перечню из справочника
Возможно, у вас есть идея проще, как реализовать это
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Выявился лайфхак по изучению "внутренностей" формулы: в исследовательских целях в самую последнюю строку формулы (перед заключительной скобкой функции LET) можно подставлять любую переменную, определенную выше в этой функции LET (внутри формулы об этом присутствует соответствующий комментарий). Так что непременно воспользуйтесь в учебных целях и обязательно на пустом листе, чтобы формулу ничего не ограничивало ни вниз, ни вправо (иначе будет появляться ошибка #REF!), поскольку разные переменные возвращают разный объем данных. Думаю, количество вопросов должно уменьшиться после самостоятельного заглядывания внутрь различных переменных.
