Инструкции: Calc. Сортировка и фильтрация данных при помощи формул
Материал для этой статьи сформулирован на основе обсуждения
в community.i-rs.ru.
Contents
Задача
 |
Требуется отсортировать данные в таблице так, чтобы порядок сортировки сохранялся и после внесения изменений. |
Решение
- Инструментарий
- Для решения задачи потребуются:
- функции COUNTA, OFFSET, SMALL, LARGE, MATCH;
- меню Данные → Фильтр, Найти и Заменить.
Шаг 1. Сортировка данных
Простейший способ сортировки в InfraOffice.pro Calc – использование пункта меню Данные → Сортировка.
Однако, данный способ не сохраняет порядок сортировки после внесения изменений в таблицу. Для обхода проблемы необходимо применять формулы. Пример на первом листе «Automatic sorting» во вложении. На нём показана сортировка 20 строк, используются из которых только 16. Можно сортировать большее количество строк изменяя значение $21, используемое в формулах, для большого числа ячеек удобно использовать меню Правка → Найти и Заменить, вставив формулы в неиспользуемые строки, выделив их маркером заполнения (под маркером заполнения понимается черный квадратик в правом нижнем углу активной ячейки). Сведения о маркере заполнения есть в Справке.
У приведённого метода есть недостатки:
- Построение формул требует времени и определённой подготовки пользователя, нежели простое использование меню Данные → Сортировка.
- Необходимо сделать копию данных, потому что формулы не могут сортировать "на месте", так что размер таблицы будет расти.
Использование формул для больших объемов данных не эффективно т. к. занимает много времени. С помощью меню Данные → Сортировка выполняется только один проход по таблице. Но формулы для каждой строки сортирует все 20 пунктов, а затем удаляет из них 19 (описание работы алгоритма в следующем абзаце). Таким образом, для 20 строк, выполняются 20 отдельных проходов. На современных ПК незаметна задержки на 20 строках, но представьте себе сортировку на 20 000 строк, что предполагает 20 000 проходов для каждой.
Посмотрим, как работают формулы на листе «Automatic sorting» таблицы. Ячейка B2 – счётчик (COUNTA), в котором хранится значение определяющее количество используемых ячеек в первом столбце. Колонка C содержит порядковые номера по возрастанию. Формула в столбце D, проверяет не является ли пустой текущая строка в столбце A. Если строка пуста, то в текущую строку столбца D ничего не заносится. В противном случае, функция OFFSET выбирает 16 используемых элементов, затем функция SMALL выполняет сортировку и выбирает наименьшее значение для первой строки в столбце D. SMALL(…;1) = MIN(…). В строке ниже, SMALL снова выполняет сортировку и находит второе наименьшее SMALL(…;2). Ячейка D17 содержит наибольшее из 16 значений SMALL(…;16). В столбце E, с помощью функции LARGE, выполняется обратная операция.
Шаг 2. Фильтрация данных
Простейший способ фильтрации — меню Данные → Фильтр. Однако, здесь та же проблема — надо повторно фильтровать при изменении данных в таблице. Автоматизируем процесс с помощью формул. За примером идём на второй лист таблицы во вложении — «Automatic subset». Снова работаем с 20 ячейками, 16 из которых содержат значения. Если надо сортировать больше значений, используем тот же приём что и при сортировке (Найти и Заменить). При фильтрации формулы более эффективны, чем при сортировке.
Описание работы сортировки.
Ячейка B2 – счётчик (COUNTA), в котором хранится значение, определяющее количество используемых ячеек в первом столбце. В столбце C находится формула, определяющая применение фильтра для текущей позиции. В примере фильтруются нечётные значения, но можно использовать любые условия которые возвращают ИСТИНА/ЛОЖЬ. Столбец D содержит номера позиций соответствующих условию ИСТИНА.
Посмотрим на адрес D5. D4=4 определяет третье значение ИСТИНА в четвёртой строке (61). Функция OFFSET по адресу D5 выбирает оставшиеся 12 значений из столбца С (16-4). Эти значения (С6-С17) соответствуют значениям после 61 (908-703). В Calc функция, возвращающая ИСТИНУ, имеет значение 1. Функция MATCH ищет первое значение 1 в оставшихся 12 ячейках. В данном случае, это будет при третьей проверке ( ячейка C8). Таким образом, MATCH возвращает значение 3, и, при добавление к D4=4, формула определяет позицию следующего значения ИСТИНА в седьмом пункте (С8).
Формула в D12-D21 возвращает ошибку, т. к. функция MATCH больше не находит значений ИСТИНА. В столбце Е проверяется — была ли ошибке в столбце D, и, при выполнении условия, строка остаётся пустой. В противном случае, функция INDEX выбирает позицию, определённую по столбцу D — Е5 выбрана седьмым пунктом (D5=7) в столбце А (897).
Шаг 3. Фильтрация и сортировка данных
Теперь объединим данные приёмы.
Пример на листе «Automatic subset and sort» вложения. Обратите внимание, т. к. используется функция COUNTA, в ячейке B2 на первом листе, формулы не будут работать корректно, если какие-либо ячейки диапазона А2-А17 пусты.
Если ваш список содержит пустые ячейки и вы хотите использовать формулы для динамической сортировки, используйте способ как на третьем листе и формулу NOT(ISEMPTY(…)), как проверку в столбце С.
Поскольку это учебное пособие, листы таблицы во вложении содержат несколько столбцов для промежуточных расчётов. Это сделано для лучшего понимания принципов работы. В зависимости от ваших требований и мастерства, вы можете комбинировать формулы, используя меньше вспомогательных столбцов. Во вложении не используются формулы массивов[1], но данный способ позволяет сортировать\фильтровать и с меньшим количеством вспомогательных столбцов (возможно даже без них), за счёт усложнения формул.
Без сомнения, есть много альтернативных методов написания этих формул. Можно указывать функцию OFFSET вместо INDEX, используя другие параметры. Выбор методов рассмотренных в руководстве, обусловлен их простотой для понимания и любовью автора к ним (о вкусах не спорят). Электронные таблицы позволяют использовать множество методов для достижения одной цели; поиск и выбор условия ИСТИНА в столбце С второго листа возможен и иным способом. Но данный пример протестирован и гарантированно работает. Выделение цветом на втором и третьем листах сделано условным форматированием для наглядного выделения нечётных чисел. Таблица отлично работает и с форматированием по-умолчанию.
Перед использованием данных методов в «промышленной» эксплуатации в них необходимо хорошо разобраться, в противном случае предпочтительно использовать стандартные методы из меню Данные → Сортировка и Данные → Фильтр.
Достоинства и недостатки
- Недостатки
- Повышенные требования к знанию и пониманию набора функций электронных таблиц; слабо подходит для объёмных таблиц.
- Достоинства
- Универсальность и независимость от изменения данных; многократное использование.
Теоретическая база решения
Переводчик:
Александр Лбов
