Sancho: /* Теоретическая база решения */

2012-04-05T05:36:45Z

Теоретическая база решения

Sancho: Основной текст

2012-04-04T19:28:21Z

Основной текст

New page

<section begin=title /><noinclude>{{DISPLAYTITLE:Инструкции: {{IopC|1}}. </noinclude>Сортировка и фильтрация данных при помощи формул<noinclude>}}</noinclude><section end=title />
{{BreadCrumbL10n|RU/documentation/how_tos/calc}}

{{RUfromforum|21887}}
<section begin=toc />
<noinclude>__TOC__</noinclude>
== Задача ==

{{Tip|Требуется отсортировать данные в таблице так, чтобы порядок сортировки сохранялся и после внесения изменений.}}

== Решение ==

;Инструментарий: Для решения задачи потребуются:
:* функции '''COUNTA''', '''OFFSET''', '''SMALL''', '''LARGE''', '''MATCH''';
:* меню {{Menu|Данные|Фильтр}}, {{Menu|Найти и Заменить}}.

=== Шаг 1. Сортировка данных ===

Простейший способ сортировки в {{IopC}} – использование пункта меню {{Menu|Данные|Сортировка}}.

Однако, данный способ не сохраняет порядок сортировки после внесения изменений в таблицу. Для обхода проблемы необходимо применять формулы. Пример на первом листе ''«Automatic sorting»'' во вложении. На нём показана сортировка 20 строк, используются из которых только 16. Можно сортировать большее количество строк изменяя значение '''$21''', используемое в формулах, для большого числа ячеек удобно использовать меню {{Menu|Правка|Найти и Заменить}}, вставив формулы в неиспользуемые строки, выделив их маркером заполнения (под маркером заполнения понимается черный квадратик в правом нижнем углу активной ячейки). Сведения о маркере заполнения есть в Справке.

У приведённого метода есть недостатки:
* Построение формул требует времени и определённой подготовки пользователя, нежели простое использование меню {{Menu|Данные|Сортировка}}.
* Необходимо сделать копию данных, потому что формулы не могут сортировать "на месте", так что размер таблицы будет расти.

Использование формул для больших объемов данных не эффективно т. к. занимает много времени. С помощью меню {{Menu|Данные|Сортировка}} выполняется только один проход по таблице. Но формулы для каждой строки сортирует все 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. Фильтрация данных ===

Простейший способ фильтрации — меню {{Menu|Данные|Фильтр}}. Однако, здесь та же проблема — надо повторно фильтровать при изменении данных в таблице. Автоматизируем процесс с помощью формул. За примером идём на второй лист таблицы во вложении — ''«Automatic subset»''. Снова работаем с 20 ячейками, 16 из которых содержат значения. Если надо сортировать больше значений, используем тот же приём что и при сортировке ({{Menu|Найти и Заменить}}). При фильтрации формулы более эффективны, чем при сортировке.

Описание работы сортировки.

Ячейка '''B2''' – счётчик ('''COUNTA'''), в котором хранится значение, определяющее количество используемых ячеек в первом столбце. В столбце '''C''' находится формула, определяющая применение фильтра для текущей позиции. В примере фильтруются нечётные значения, но можно использовать любые условия которые возвращают ''ИСТИНА/ЛОЖЬ''. Столбец '''D''' содержит номера позиций соответствующих условию ''ИСТИНА''.

Посмотрим на адрес '''D5'''. '''D4=4''' определяет третье значение ''ИСТИНА'' в четвёртой строке (''61''). Функция '''OFFSET''' по адресу '''D5''' выбирает оставшиеся 12 значений из столбца '''С''' (''16-4''). Эти значения ('''С6-С17''') соответствуют значениям после '''61''' (''908-703''). В {{IopC|1}} функция, возвращающая ''ИСТИНУ'', имеет значение ''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(…))''', как проверку в столбце '''С'''.

Поскольку это учебное пособие, листы таблицы во вложении содержат несколько столбцов для промежуточных расчётов. Это сделано для лучшего понимания принципов работы. В зависимости от ваших требований и мастерства, вы можете комбинировать формулы, используя меньше вспомогательных столбцов. Во вложении не используются формулы массивов<ref>{{SecTitle|RU/kb/module/calc/function/array}} <br />[[Documentation/How_Tos/Using_Arrays]]</ref>, но данный способ позволяет сортировать\фильтровать и с меньшим количеством вспомогательных столбцов (возможно даже без них), за счёт усложнения формул.

Без сомнения, есть много альтернативных методов написания этих формул. Можно указывать функцию '''OFFSET''' вместо '''INDEX''', используя другие параметры. Выбор методов рассмотренных в руководстве, обусловлен их простотой для понимания и любовью автора к ним (о вкусах не спорят). Электронные таблицы позволяют использовать множество методов для достижения одной цели; поиск и выбор условия ''ИСТИНА'' в столбце '''С''' второго листа возможен и иным способом. Но данный пример протестирован и гарантированно работает. Выделение цветом на втором и третьем листах сделано условным форматированием для наглядного выделения нечётных чисел. Таблица отлично работает и с форматированием по-умолчанию.

Перед использованием данных методов в «промышленной» эксплуатации в них необходимо хорошо разобраться, в противном случае предпочтительно использовать стандартные методы из меню {{Menu|Данные|Сортировка}} и {{Menu|Данные|Фильтр}}.

{{Load|OOo_Sorting_and_Filtering_data_with_formulas.ods|OOo Sorting and Filtering data with formulas.ods}}

=== Достоинства и недостатки ===

;Недостатки: Повышенные требования к знанию и пониманию набора функций электронных таблиц; слабо подходит для объёмных таблиц.

;Достоинства: Универсальность и независимость от изменения данных; многократное использование.

== Теоретическая база решения ==

* {{SecTitle|RU/kb/00000544}};
* {{SecTitle|RU/kb/00000698}};
* {{SecTitle|RU/kb/00000650}};
* {{SecTitle|RU/kb/00000744}};
* {{SecTitle|RU/kb/00000717}};
* {{SecTitle|RU/kb/00000649}}.

<section end=toc />

<section begin=signature/>
{{SignYear|Sancho||2012}}
<section end=signature/>

{{RUhowtoBottom}}

@@ Line 99: / Line 99: @@
 <section begin=signature/>
 {{SignYear|Sancho||2012}}
 <section end=signature/>
 {{RUhowtoBottom}}

RU/ht/00000002 - Revision history

Sancho: /* Теоретическая база решения */

Sancho: Основной текст