Выборка строк в отдельный лист по совпадению в столбце

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

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

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

1qaz2WSX

Дата: Четверг, 21.03.2024, 01:05 | Сообщение № 1

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

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

Сообщений: 2

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

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

Доброго времени суток.
Имеется xls документ, состоящий из 560 тыс строк и порядка 136 столбцов.

Существует ли какая то упрощенная формула, чтобы можно было выбрать все строки, содержащие в столбце T и совпадающие по критерию (содержит) с определенным значением на Лист2
Почему прошу именно подсказать формулу, потому, что макросы могут меняться от LibreOffice/OnlyOffice к Microsoft Exel
За одно гляньте в приложенный файлик сколько в реальности параметров собирает по каждому из нас оператор сотовой связи )
Заранее спасибо за подсказку.

К сообщению приложен файл: sample.xls (16.0 Kb)

Ответить

bigor

Дата: Четверг, 21.03.2024, 09:24 | Сообщение № 2

Группа: Проверенные

Ранг: Старожил

Сообщений: 1105

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

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

нет

Цитата 1qaz2WSX, 21.03.2024 в 01:05, в сообщении № 1 (

)

макросы могут меняться от LibreOffice/OnlyOffice к Microsoft Exel

формулы могут так же меняться, плюс они более зависимы от версии продукта.

Код

=IFERROR(INDEX($Лист1.$A$1:$FH$100;AGGREGATE(15;6;ROW(A1:A100)/ISNUMBER(SEARCH($A$1;$Лист1.$T$1:$T$100));ROW(A1));COLUMN(A1));"")

написана в calc LibreOffice. Работает, правда на 560 тыс столбцов, думаю, будет сильно тормозить. При открытии в OnlyOffice диапазоны A1, A1:A100 меняются на XFD и формула не работает. В excel не проверял
Но 560 тыс строк это не для таблиц, тут уже БД нужна

К сообщению приложен файл: 0139579.xls (376.5 Kb)

Ответить

1qaz2WSX

Дата: Четверг, 21.03.2024, 11:44 | Сообщение № 3

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

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

Сообщений: 2

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

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

Цитата bigor, 21.03.2024 в 09:24, в сообщении № 2 (

)

=ЕСЛИОШИБКА(ИНДЕКС($Лист1.$A$1:$FH$100;АГРЕГАТ(15;6;СТРОКА(A1:A100)/ЕЧИСЛО(ПОИСК($A$1;$Лист1.$T$1:$T$100));СТРОКА(A1));СТОЛБЕЦ(A1));"")

БлагоДарю Вам за работающий ответ, но мне нужно его немного подкорректировать под свои нужды и если Вам не сложно, то не могли бы Вы разъяснить немного написанное.

Правильно ли я понимаю, что из ячейки А1 на листе2 берется искомое значение и осуществляется поиск в столбце Т начиная со строки 1 по 100, и при этом поиск идет только в том случае, если значения строки/столбца A1 не пустое. А что делает в таком случае АГРЕГАТ и ИНДЕКС?

1) ЕСЛИОШИБКА(значение;значение_если_ошибка)
значение Обязательный аргумент. Аргумент, проверяемый на наличие ошибки.
value_if_error Обязательный. Значение, возвращаемое, если формула принимает ошибку. Вычисляются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, или #NULL!

2) ИНДЕКС(массив; номер_строки; [номер_столбца])
Если массив содержит только одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца является необязательным.
Если массив содержит более одной строки и более одного столбца и используется только номер_строки или номер_столбца, ИНДЕКС возвращает массив всей строки или столбца в массиве.

Номер_строки Обязательный, если column_num отсутствует. Выбирает строку в массиве, из которой требуется возвратить значение. Если номер_строки опущен, требуется номер_столбца.
Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если номер_столбца опущен, требуется номер_строки.

3) АГРЕГАТ(номер_функции;параметры;массив;[k])
Номер_функции — обязательный аргумент. Число от 1 до 19, определяющее функцию, которую необходимо использовать.
14 - НАИБОЛЬШИЙ
15 - НАИМЕНЬШИЙ
Параметры — обязательный аргумент. Числовое значение, определяющее, какие значения при вычислении функции следует пропускать.
5 - Пропуск скрытых строк

4) СТРОКА([ссылка])
Ссылка Необязательный. Ячейка или диапазон ячеек, для которых требуется вернуть номер строки.

5) СТОЛБЕЦ([ссылка])
ссылка — необязательный аргумент. Ячейка или диапазон ячеек, для которого требуется возвратить номер столбца.

6) ЕЧИСЛО(значение)
Каждая из функций Епроверяет указанное значение и возвращает в зависимости от результата значение ИСТИНА или ЛОЖЬ.

7) ПОИСК ( искомый_текст ; просматриваемая_строка ;[нач_позиция])
Искомый_текст — текст, который требуется найти.
Просматриваемая_строка — текст, в которой ищется Искомый_текст .
Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

Цитата bigor, 21.03.2024 в 09:24, в сообщении № 2 (

)

=ЕСЛИОШИБКА(ИНДЕКС($Лист1.$A$1:$FH$100;АГРЕГАТ(15;6;СТРОКА(A1:A100)/ЕЧИСЛО(ПОИСК($A$1;$Лист1.$T$1:$T$100));СТРОКА(A1));СТОЛБЕЦ(A1));"")

Ответить

Сообщение

Цитата bigor, 21.03.2024 в 09:24, в сообщении № 2 (

)

=ЕСЛИОШИБКА(ИНДЕКС($Лист1.$A$1:$FH$100;АГРЕГАТ(15;6;СТРОКА(A1:A100)/ЕЧИСЛО(ПОИСК($A$1;$Лист1.$T$1:$T$100));СТРОКА(A1));СТОЛБЕЦ(A1));"")

Автор - 1qaz2WSX
Дата добавления - 21.03.2024 в 11:44

bigor

Дата: Четверг, 21.03.2024, 12:05 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1105

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

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

нет

Это работает примерно так:
аггрегат с параметрами 15;6 ищет k наименьшее значение игнорируя ошибки выражения СТРОКА(A1:A100)/ЕЧИСЛО(ПОИСК($A$1;$Лист1.$T$1:$T$100));СТРОКА(A1))
СТРОКА(A1:A100) дает нам значения от 1 до 100, ЕЧИСЛО(ПОИСК($A$1;$Лист1.$T$1:$T$100)) при успешном поиске значения из А1 в ячейках столбца Т - возвращает 1, в итоге получаем массив номеров строк, где есть искомое значение и ошибок, где его нет. СТРОКА(A1) определяет k, порядковый номер наименьшего значения. Для первой строки 1, следовательно из массива выбирается первое наименьшее значение, для второй строки 2 итд
индекс - возвращет из диапазона $Лист1.$A$1:$FH$100 значение соответсвующее номеру строки, возвращаемого аггрегат и номеру столбца заданного СТОЛБЕЦ(A1) (нужен для протягивания вправо, тогда возвращаются номера столбцов от 1 до докуда дотянем формулу)
ЕСЛИОШИБКА в случае если индекс вернет ошибку, заменяет ее пустой строкой

Ответить

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