Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Фильтр, выдающий список всех значений, по запросу - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Фильтр, выдающий список всех значений, по запросу (Формулы/Formulas)
Фильтр, выдающий список всех значений, по запросу
dminicab Дата: Вторник, 22.10.2024, 23:28 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
Доброго времени суток, всем!
Прошу помочь в написании формулы. По сути нужно сделать "фильтр".

Есть некое значение в ячейке A4 (Лист 3), нужно чтобы формула находила в таблице (Лист 4) все строки, которые соответствуют данному значению, и выводила их все списком, а не первое попавшееся. У меня ума хватило только на ИНДЕКС/ПОИСКПОЗ, но так возвращается только первое совпадение. А нужно, чтобы возвращался список всех совпадений.

Пример в таблице:
https://docs.google.com/spreads....5435555

Покорнейше прошу о помощи pray
 
Ответить
СообщениеДоброго времени суток, всем!
Прошу помочь в написании формулы. По сути нужно сделать "фильтр".

Есть некое значение в ячейке A4 (Лист 3), нужно чтобы формула находила в таблице (Лист 4) все строки, которые соответствуют данному значению, и выводила их все списком, а не первое попавшееся. У меня ума хватило только на ИНДЕКС/ПОИСКПОЗ, но так возвращается только первое совпадение. А нужно, чтобы возвращался список всех совпадений.

Пример в таблице:
https://docs.google.com/spreads....5435555

Покорнейше прошу о помощи pray
Автор - dminicab
Дата добавления - 22.10.2024 в 23:28
doober Дата: Вторник, 22.10.2024, 23:56 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 970
Репутация: 331 ±
Замечаний: 0% ±

Excel 2010
При такой структуре данных никак

 
Ответить
СообщениеПри такой структуре данных никак
Автор - doober
Дата добавления - 22.10.2024 в 23:56
dminicab Дата: Среда, 23.10.2024, 00:12 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
doober, а что со структурой не так? какой она должна быть?
 
Ответить
Сообщениеdoober, а что со структурой не так? какой она должна быть?
Автор - dminicab
Дата добавления - 23.10.2024 в 00:12
dminicab Дата: Среда, 23.10.2024, 00:14 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
doober, я могу структуру изменить при необходимости
 
Ответить
Сообщениеdoober, я могу структуру изменить при необходимости
Автор - dminicab
Дата добавления - 23.10.2024 в 00:14
Hugo Дата: Среда, 23.10.2024, 00:15 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3690
Репутация: 790 ±
Замечаний: 0% ±

365
Ну для тайминга можно, а объединения наверное и в гуглах зло...
В Экселе я бы UDF добавил и вывел рядом все модули с повторами.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD
 
Ответить
СообщениеНу для тайминга можно, а объединения наверное и в гуглах зло...
В Экселе я бы UDF добавил и вывел рядом все модули с повторами.
Автор - Hugo
Дата добавления - 23.10.2024 в 00:15
dminicab Дата: Среда, 23.10.2024, 00:25 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
Hugo, я могу объединения убрать, это не проблема
 
Ответить
СообщениеHugo, я могу объединения убрать, это не проблема
Автор - dminicab
Дата добавления - 23.10.2024 в 00:25
Hugo Дата: Среда, 23.10.2024, 11:21 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3690
Репутация: 790 ±
Замечаний: 0% ±

365
Так я в файле тайминг сделал, уберите объединения, заполните все строки данными и сделайте аналогично и с модулями.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD
 
Ответить
СообщениеТак я в файле тайминг сделал, уберите объединения, заполните все строки данными и сделайте аналогично и с модулями.
Автор - Hugo
Дата добавления - 23.10.2024 в 11:21
Gustav Дата: Среда, 23.10.2024, 11:58 | Сообщение № 8
Группа: Админы
Ранг: Участник клуба
Сообщений: 2794
Репутация: 1160 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Я бы на Лист4 сначала подготовил единый рассчётный массив с "ликвидацией" (заполнением) объединенных ячеек, а потом бы к нему уже обращался с Лист3.

Единственная формула для формирования такого массива - для ячейки G1 на Лист4 (вставил в файл):
[vba]
Код
=BYCOL(A1:E45; LAMBDA(col; SCAN(;col; LAMBDA(acc;val; IF(val="";acc;val)))))
[/vba]

P.S. А дальше, если правильно понял задумку, добавил на Лист3_Gustav две протягиваемые формулы в ячейки B4 (Модуль) и C4 (Тайминг) соответственно:
[vba]
Код
=JOIN(CHAR(10);UNIQUE(FILTER('Лист4'!$G$2:$G$45;'Лист4'!$K$2:$K$45=A4)))
[/vba]
[vba]
Код
=JOIN(CHAR(10);UNIQUE(FILTER('Лист4'!$I$2:$I$45;'Лист4'!$K$2:$K$45=A4)))
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЯ бы на Лист4 сначала подготовил единый рассчётный массив с "ликвидацией" (заполнением) объединенных ячеек, а потом бы к нему уже обращался с Лист3.

Единственная формула для формирования такого массива - для ячейки G1 на Лист4 (вставил в файл):
[vba]
Код
=BYCOL(A1:E45; LAMBDA(col; SCAN(;col; LAMBDA(acc;val; IF(val="";acc;val)))))
[/vba]

P.S. А дальше, если правильно понял задумку, добавил на Лист3_Gustav две протягиваемые формулы в ячейки B4 (Модуль) и C4 (Тайминг) соответственно:
[vba]
Код
=JOIN(CHAR(10);UNIQUE(FILTER('Лист4'!$G$2:$G$45;'Лист4'!$K$2:$K$45=A4)))
[/vba]
[vba]
Код
=JOIN(CHAR(10);UNIQUE(FILTER('Лист4'!$I$2:$I$45;'Лист4'!$K$2:$K$45=A4)))
[/vba]
Автор - Gustav
Дата добавления - 23.10.2024 в 11:58
dminicab Дата: Пятница, 25.10.2024, 00:13 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
Gustav, спасибо! Задумку поняли правильно, вот только нужно чтобы каждое значение списка возвращалось отдельной строкой, а не все в одной ячейке. Предполагается, что возвращаемые значения будут ссылочного типа, поэтому нужно, чтобы они кликабельность сохранили :(
 
Ответить
СообщениеGustav, спасибо! Задумку поняли правильно, вот только нужно чтобы каждое значение списка возвращалось отдельной строкой, а не все в одной ячейке. Предполагается, что возвращаемые значения будут ссылочного типа, поэтому нужно, чтобы они кликабельность сохранили :(
Автор - dminicab
Дата добавления - 25.10.2024 в 00:13
Gustav Дата: Пятница, 25.10.2024, 11:39 | Сообщение № 10
Группа: Админы
Ранг: Участник клуба
Сообщений: 2794
Репутация: 1160 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата dminicab, 25.10.2024 в 00:13, в сообщении № 9 ()
нужно чтобы каждое значение списка возвращалось отдельной строкой, а не все в одной ячейке

Да, это усложняет задачу. А как Вы при этом собираетесь вводить значения в колонку A на Лист3 ? Где-то (например, на другом настроечном листе) создадите заранее список этих значений и он потом подтянется в результирующую формулу, которая в этом случае будет формировать не 2, а сразу 3 колонки ? Или будете вручную вводить очередное значение, отступив нужное количество строк от предыдущего сверху? При этом "нужное количество строк" должно быть достаточным для развертывания списков колонки 2 и особенно колонки 3, рассчитанных для предыдущего сверху значения в колонке A.

P.S. Если же на Лист3 в колонку A предполагается ввод только одного значения в ячейку A4, то тогда нет необходимости в подсчете кол-ва строк, которые будет занимать результат. В этом случае формулы упрощаются путем исключения из них объединяющей функции JOIN:
[vba]
Код
=UNIQUE(FILTER('Лист4'!$G$2:$G$45;'Лист4'!$K$2:$K$45=A4))
[/vba]
[vba]
Код
=UNIQUE(FILTER('Лист4'!$I$2:$I$45;'Лист4'!$K$2:$K$45=A4))
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата dminicab, 25.10.2024 в 00:13, в сообщении № 9 ()
нужно чтобы каждое значение списка возвращалось отдельной строкой, а не все в одной ячейке

Да, это усложняет задачу. А как Вы при этом собираетесь вводить значения в колонку A на Лист3 ? Где-то (например, на другом настроечном листе) создадите заранее список этих значений и он потом подтянется в результирующую формулу, которая в этом случае будет формировать не 2, а сразу 3 колонки ? Или будете вручную вводить очередное значение, отступив нужное количество строк от предыдущего сверху? При этом "нужное количество строк" должно быть достаточным для развертывания списков колонки 2 и особенно колонки 3, рассчитанных для предыдущего сверху значения в колонке A.

P.S. Если же на Лист3 в колонку A предполагается ввод только одного значения в ячейку A4, то тогда нет необходимости в подсчете кол-ва строк, которые будет занимать результат. В этом случае формулы упрощаются путем исключения из них объединяющей функции JOIN:
[vba]
Код
=UNIQUE(FILTER('Лист4'!$G$2:$G$45;'Лист4'!$K$2:$K$45=A4))
[/vba]
[vba]
Код
=UNIQUE(FILTER('Лист4'!$I$2:$I$45;'Лист4'!$K$2:$K$45=A4))
[/vba]
Автор - Gustav
Дата добавления - 25.10.2024 в 11:39
dminicab Дата: Пятница, 25.10.2024, 22:06 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 20% ±

Google
Решил задачу, спасибо огромное всем за помощь! Все оказалось проще, чем я думал) сделал через filter, как подсказывали :ok:
 
Ответить
СообщениеРешил задачу, спасибо огромное всем за помощь! Все оказалось проще, чем я думал) сделал через filter, как подсказывали :ok:
Автор - dminicab
Дата добавления - 25.10.2024 в 22:06
Gustav Дата: Суббота, 26.10.2024, 17:41 | Сообщение № 12
Группа: Админы
Ранг: Участник клуба
Сообщений: 2794
Репутация: 1160 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Я в свою очередь тоже решил задачу в постановке своего сообщения №10, с автоматическим расчетом областей, занимаемых каждой категорией. Список категорий вводится в диапазон F1:F10 - см. по ссылке ярко-жёлтую заливку на Лист3_Gustav. Сама формула расположена в ячейке A11, а диапазон полученного результата окрашен в бледно-зеленый цвет (A11:C31).

Формула в ячейке A11 такова:
[vba]
Код
=LET(
_params; UNIQUE(FILTER(F1:F10; F1:F10<>""));
_array; REDUCE({"Раздел"\"Колонка"\"Строка в разделе"\"Значение"}; _params; LAMBDA(_acc; _val;
{_acc;
LET(
_row; MATCH(_val; _params; 0);
_param; _val;
_col21; UNIQUE(FILTER('Лист4'!$G$2:$G$45; 'Лист4'!$K$2:$K$45=_param));
_col31; UNIQUE(FILTER('Лист4'!$I$2:$I$45; 'Лист4'!$K$2:$K$45=_param));

_col12; {SEQUENCE(ROWS(_param);1;_row;0) \ SEQUENCE(ROWS(_param);1;1;0) \ SEQUENCE(ROWS(_param);1;1;1) \ _param};
_col22; {SEQUENCE(ROWS(_col21);1;_row;0) \ SEQUENCE(ROWS(_col21);1;2;0) \ SEQUENCE(ROWS(_col21);1;1;1) \ _col21};
_col32; {SEQUENCE(ROWS(_col31);1;_row;0) \ SEQUENCE(ROWS(_col31);1;3;0) \ SEQUENCE(ROWS(_col31);1;1;1) \ _col31};
{_col12;_col22;_col32}
)}));
_numOfParts; MAX(INDEX(_array;;1));
_rowsPerPart; MAX(INDEX(_array;;3));
_continRows; MAP(INDEX(_array;;1); INDEX(_array;;3); LAMBDA(_a;_b;IF(ISNUMBER(_a);(_a - 1) * _rowsPerPart + _b; "Строка")));

_finalArray; MAKEARRAY(_numOfParts * _rowsPerPart; 3; LAMBDA(_r; _c; IFNA(FILTER(INDEX(_array;;4); INDEX(_array;;2)=_c; _continRows=_r);"")));
FILTER(_finalArray; (INDEX(_finalArray;;2) <> "") + (INDEX(_finalArray;;3) <> ""))
)
[/vba]
К сообщению приложен файл: 5072655.png (131.0 Kb)

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЯ в свою очередь тоже решил задачу в постановке своего сообщения №10, с автоматическим расчетом областей, занимаемых каждой категорией. Список категорий вводится в диапазон F1:F10 - см. по ссылке ярко-жёлтую заливку на Лист3_Gustav. Сама формула расположена в ячейке A11, а диапазон полученного результата окрашен в бледно-зеленый цвет (A11:C31).

Формула в ячейке A11 такова:
[vba]
Код
=LET(
_params; UNIQUE(FILTER(F1:F10; F1:F10<>""));
_array; REDUCE({"Раздел"\"Колонка"\"Строка в разделе"\"Значение"}; _params; LAMBDA(_acc; _val;
{_acc;
LET(
_row; MATCH(_val; _params; 0);
_param; _val;
_col21; UNIQUE(FILTER('Лист4'!$G$2:$G$45; 'Лист4'!$K$2:$K$45=_param));
_col31; UNIQUE(FILTER('Лист4'!$I$2:$I$45; 'Лист4'!$K$2:$K$45=_param));

_col12; {SEQUENCE(ROWS(_param);1;_row;0) \ SEQUENCE(ROWS(_param);1;1;0) \ SEQUENCE(ROWS(_param);1;1;1) \ _param};
_col22; {SEQUENCE(ROWS(_col21);1;_row;0) \ SEQUENCE(ROWS(_col21);1;2;0) \ SEQUENCE(ROWS(_col21);1;1;1) \ _col21};
_col32; {SEQUENCE(ROWS(_col31);1;_row;0) \ SEQUENCE(ROWS(_col31);1;3;0) \ SEQUENCE(ROWS(_col31);1;1;1) \ _col31};
{_col12;_col22;_col32}
)}));
_numOfParts; MAX(INDEX(_array;;1));
_rowsPerPart; MAX(INDEX(_array;;3));
_continRows; MAP(INDEX(_array;;1); INDEX(_array;;3); LAMBDA(_a;_b;IF(ISNUMBER(_a);(_a - 1) * _rowsPerPart + _b; "Строка")));

_finalArray; MAKEARRAY(_numOfParts * _rowsPerPart; 3; LAMBDA(_r; _c; IFNA(FILTER(INDEX(_array;;4); INDEX(_array;;2)=_c; _continRows=_r);"")));
FILTER(_finalArray; (INDEX(_finalArray;;2) <> "") + (INDEX(_finalArray;;3) <> ""))
)
[/vba]
Автор - Gustav
Дата добавления - 26.10.2024 в 17:41
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Фильтр, выдающий список всех значений, по запросу (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!