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

Вход

Регистрация

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

 

= Мир MS Excel/Выборка строк в отдельный лист по совпадению в столбце - Мир MS Excel

Старая форма входа
  • Страница 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)
 
Ответить
СообщениеДоброго времени суток.
Имеется xls документ, состоящий из 560 тыс строк и порядка 136 столбцов.

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

Автор - 1qaz2WSX
Дата добавления - 21.03.2024 в 01:05
bigor Дата: Четверг, 21.03.2024, 09:24 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1100
Репутация: 210 ±
Замечаний: 0% ±

нет
макросы могут меняться от 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)
 
Ответить
Сообщение
макросы могут меняться от 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 тыс строк это не для таблиц, тут уже БД нужна

Автор - bigor
Дата добавления - 21.03.2024 в 09:24
1qaz2WSX Дата: Четверг, 21.03.2024, 11:44 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

=ЕСЛИОШИБКА(ИНДЕКС($Лист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.
 
Ответить
Сообщение
=ЕСЛИОШИБКА(ИНДЕКС($Лист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.

Автор - 1qaz2WSX
Дата добавления - 21.03.2024 в 11:44
bigor Дата: Четверг, 21.03.2024, 12:05 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1100
Репутация: 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 до докуда дотянем формулу)
ЕСЛИОШИБКА в случае если индекс вернет ошибку, заменяет ее пустой строкой
 
Ответить
СообщениеЭто работает примерно так:
аггрегат с параметрами 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 до докуда дотянем формулу)
ЕСЛИОШИБКА в случае если индекс вернет ошибку, заменяет ее пустой строкой

Автор - bigor
Дата добавления - 21.03.2024 в 12:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка строк в отдельный лист по совпадению в столбце (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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