Доброго времени суток. Имеется xls документ, состоящий из 560 тыс строк и порядка 136 столбцов.
Существует ли какая то упрощенная формула, чтобы можно было выбрать все строки, содержащие в столбце T и совпадающие по критерию (содержит) с определенным значением на Лист2 Почему прошу именно подсказать формулу, потому, что макросы могут меняться от LibreOffice/OnlyOffice к Microsoft Exel За одно гляньте в приложенный файлик сколько в реальности параметров собирает по каждому из нас оператор сотовой связи ) Заранее спасибо за подсказку.
Доброго времени суток. Имеется xls документ, состоящий из 560 тыс строк и порядка 136 столбцов.
Существует ли какая то упрощенная формула, чтобы можно было выбрать все строки, содержащие в столбце T и совпадающие по критерию (содержит) с определенным значением на Лист2 Почему прошу именно подсказать формулу, потому, что макросы могут меняться от LibreOffice/OnlyOffice к Microsoft Exel За одно гляньте в приложенный файлик сколько в реальности параметров собирает по каждому из нас оператор сотовой связи ) Заранее спасибо за подсказку.1qaz2WSX
написана в calc LibreOffice. Работает, правда на 560 тыс столбцов, думаю, будет сильно тормозить. При открытии в OnlyOffice диапазоны A1, A1:A100 меняются на XFD и формула не работает. В excel не проверял Но 560 тыс строк это не для таблиц, тут уже БД нужна
написана в calc LibreOffice. Работает, правда на 560 тыс столбцов, думаю, будет сильно тормозить. При открытии в OnlyOffice диапазоны A1, A1:A100 меняются на XFD и формула не работает. В excel не проверял Но 560 тыс строк это не для таблиц, тут уже БД нужнаbigor
БлагоДарю Вам за работающий ответ, но мне нужно его немного подкорректировать под свои нужды и если Вам не сложно, то не могли бы Вы разъяснить немного написанное.
Правильно ли я понимаю, что из ячейки А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 на листе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
Это работает примерно так: аггрегат с параметрами 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
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
