Разбиваем диапазон ячеек на отдельные группы формулами. Часть вторая.
Перед вами вторая часть занятия, на котором мы продолжаем знакомится с тем, как выполнить распределение ячеек по группам и столбцам формулами. На предыдущем занятии мы рассматривали первоначальное распределение ячеек диапазона по группам с помощью функций ОСТАТ и С ТРОКА. Непосредственное разделение по частям исходного диапазона выполнялось нами практически вручную после отбора необходимых данных по номерам групп фильтром или инструментом «Выделить группу ячеек». При большом количестве групп данный способ неудобен.
Сегодня мы с вами рассмотрим разделение диапазона данных в Excel на отдельные группы с учетом требований к ним. В ходе работы будут использоваться функции СТОЛБЕЦ, ТЕКСТ и инструмент «Выделение группы ячеек». Вместе с приемами, рассмотренными на прошлом занятии, они обеспечивают автоматическое разделение диапазона данных на части, соответствующие заданным требованиям.
Вначале вспомним принцип работы по выбору ячеек для каждой группы. Вначале добавляется заголовок к исходному диапазону для удобства работы. Следом проверяется номер строки лдля каждой ячейки. Это выполняется с помощью функции СТРОКА.
Затем запускается деление найденного номера строки на количество элементов в группе. Чтобы это сделать, используют функцию ОСТАТ, где первым параметром указывают найденный номер строки, а вторым – количество позиций. Все это выполняется одной вложенной формулой. Таким образом происходит цикличная нумерация элементов групп в исходном диапазоне. При желании для того, чтобы номера позиций начинались с нуля, к функции нахождения номера строки в первом параметре функции ОСТАТ прибавляют значение, равное разнице между количеством элементов и фактическим номером строки для первой ячейки с данными исходного диапазона.
В примере ниже общий диапазон необходимо распределить по трем столбцам, а первая ячейка с данными располагается в второй строке листа. В итоге получаем формулу
=ОСТАТ(СТРОКА()+1;3)
Именно так происходит отбор в ситуации, когда требуется разместить ячейки про очереди в заданные колонки новой таблицы.
Если же необходимо выбрать группы ячеек, идущих подряд в исходном диапазоне, выполняют дополнительную нумерацию, начиная с определенного результата работы функции ОСТАТ. В ней мы каждый раз увеличиваем номер группы, когда повторяется начальный результат вычислений функциями ОСТАТ и СТРОКА. Проверка выполняется с помощью функции ЕСЛИ, и все это тоже записывается в виде одной составной формулы. Отличие состоит в том, что значение в первой строке группы задаем принудительно. Так же можно выполнить проверку на ошибки в результате с помощью функции ЕСЛИОШИБКА или с помощью использования функции проверки свойств ЕОШ в составном варианте ЕСЛИ. Приведем ниже вариант с использованием функции ЕСЛИОШИБКА.
=ЕСЛИОШИБКА(ЕСЛИ(ОСТАТ(СТРОКА()+10;12)=0;C1+1;C1);1)
А вот так выгляди та же проверка на ошибки с помощью составного ЕСЛИ.
=ЕСЛИ(ЕОШ(C1+1);1;ЕСЛИ(ОСТАТ(СТРОКА()+10;12)=0;C1+1;C1))
Итак, номера ячеек для каждой группы получены. Теперь для их распределения по колонкам достаточно разместить их в те колонки, чью заголовки содержат число, равное номеру нужной группы. Однако тут есть один нюанс. Необходимо сделать так, чтобы при этом в колонки с группами попадало значение, тип которого в принципе не совпадает с типом данных ячеек исходного диапазона. Проще всего такую проверку сделать с помощью функции ЕСЛИ. При совпадении номера группы и номера столбца вывести значение из исходного диапазона, предварительно задав для него принудительно текстовый формат с помощью функции ТЕКСТ. Если же совпадения нет, то вывести ноль. После создания групп восстановить в каждой из них исходный формат данных не составит труда, но об этом немного позже. Для того, чтобы при протягивании вправо адрес ячейки исходного диапазона не сдвигался, к нему необходимо применить смешанную адресацию с закреплением столбца. Так же смешанную адресацию с закреплением столбца надо применить к ячейке, содержащей номер группы и смешанную адресацию с закреплением строки к ячейке с номером проверочного столбца.
Ниже приведен пример вывода данных в отдельные столбцы из выписки движения средств.
Обратите внимание, что даты принудительно выведены в виде числовых значений, но в текстовом формате. Использовалась формула
=ЕСЛИ($B2=C$1;ТЕКСТ($A2;”@”);0)
А вот так распределились по группам ежегодных данных ежемесячные показатели из соответствующего диапазона. Обратите внимание, что, несмотря что группа содержат числа, теперь они воспринимаются программой именно как текст! Формула в данном случае выглядит так.
=ЕСЛИ($C2=D$1;ТЕКСТ($B2;”@”);0)
Распределив группы по столбцам, поступаем следующим образом. Выделяем полученный диапазон с распределенными по группам ячейками из исходного диапазона и запускаем инструмент «выделить группу ячеек». Чтобы его открыть, можно нажать клавишу F5, а затем нажать кнопку «Выделить». Устанавливаем переключатель «Формулы» и оставляем флажок у варианта «числа», нажимаем ОК, а потом сочетание клавиш Ctrl и “-“ (минус). Устанавливаем переключатель в положение «Удалить ячейки со сдвигом вверх» и нажимаем «ОК». Готово, группы выбраны.
7
Остался важный нюанс. Теперь числовые данные и данные дат в полученных группах надо снова перевести в нужный формат. Вначале все данные, которые можно перевести, переводим в числовой формат. Чтобы это сделать, вначале все результаты вычислений для групп копируем и вставляем в виде значений. Затем в любой пустой ячейке записываем единицу и копируем ее. Теперь выделяем все диапазоны групп и щелкаем по ним правой кнопкой. В появившемся контекстном меню выбираем вариант специальная вставка и жмем на него.
. Откроется окно, где надо отметить пункт «Умножить» и нажать ОК. В результате все ячейки, которые можно перевести в число, примут числовой формат.
Остается только выделить группу, где должны отображаться даты и задать нужный вариант с помощью стандартной настройки в формате ячеек и при необходимости настроить внешний вид – задать ширину столбцов, настроить заголовки, границы и так далее. Итог работы показан ниже.
Однако в ходе работы остались недочеты, от которых желательно избавиться. Прежде всего номера колонок, по которым распределяются группы, мы указывали вручную, а это неудобно. Кроме этого, применялся промежуточный диапазон, в который выводились номера групп, а это тоже и не удобно, и не всегда возможно, в любом случае это приводит к дополнительным затратам рабочего времени и диапазонов листа.
И здесь нам приходит на помощь функция СТОЛБЕЦ. При распределении по группам вместо сравнивания результатов вычислений из отдельного диапазона и групп можно просто сравнить в сами вычисления номеров групп и номеров столбцов в проверочной функции ЕСЛИ, которую мы применяли. Номер же столбца можно получить как разницу номера столбца любой ячейки из начального столбца и номера столбца ячейки с формулой. Естественно, при записи формулы не нужно забывать о способах адресации на исходные ячейки, фиксируя знаком $ неизменяемые части. В результате получаем итоговый универсальный вариант (подразумевается, что исходный диапазон находится в колонке А).
=ЕСЛИ(ОСТАТ(СТРОКА()+2;3)=СТОЛБЕЦ()-СТОЛБЕЦ($A$1)-1;ТЕКСТ($A1;”@”);0)
В случае с нашими группами из непрерывных областей при извлечении таких групп ситуация немного другая. Дело в том, что комбинирование функций ОСТАТ и СТРОКА позволяет нам вывести номера групп в чередующемся порядке. Если же номер группы должен идти подряд для всего ряда элементов, то этот вариант в данном случае не подойдет. Вместо него мы должны найти целую часть от деления номера строки на количество элементов в группе. Удобнее всего для этого воспользоваться функцией ЧАСТНОЕ. Формула, которая будет находить номер группы, получится следующей
=ЧАСТНОЕ(СТРОКА()-2;12)
Обратите внимание на две тонкости. Прежде всего теперь номера групп начинаются с нуля. Чтобы они начинались с 1, требуется к результату работы функции ЧАСТНОЕ прибавлять единицу, а это увеличивает формулу. Во-вторых, для того, чтобы счет элементов в группе шел с начала, необходимо первый параметр функции ЧАСТНОЕ для расчета по первой ячейке исходных данных обнулять. Именно поэтому мы от результата вычисления текущего номера строки для ячейки с формулой отняли фактический номер строки первой ячейки исходного диапазона.
Теперь же, зная номер группы и имея возможность автоматически вычислить нужную колонку для группы, получаем итоговую формулу
=ЕСЛИ(ЧАСТНОЕ(СТРОКА()-1;12)=СТОЛБЕЦ()-СТОЛБЕЦ($C$1);ТЕКСТ($B1;”@”);0)
Обратите внимание на следующее.
- Так как номер строки первой ячейки исходного диапазона равен единице, то мы и вычитали от результата вычисления функции СТРОКА() именно единицу, а не двойку, как в предыдущем примере.
- Для определения нужной колонки мы от результата работы функции СТОЛБЕЦ() отнимаем номер столбцы первой ячейки с формулой, имеющей адрес С1. Как вариант, можно было бы отнимать номер столбца ячейки B1, но тогда бы пришлось к результату вычитания прибавить единицу, и формулы приняла бы немного более длинный вид
=ЕСЛИ(ЧАСТНОЕ(СТРОКА()-1;12)=СТОЛБЕЦ()-СТОЛБЕЦ($B$1)-1;ТЕКСТ($B1;”@”);0)
Результат работы от данного добавления не меняется.
Получив группы, удаляем из них ненужные ячейки с помощью инструмента «Выделить группу ячеек». О нем достаточно подробно рассказано как в текущем материале, так и на предыдущих занятиях, поэтому не будем повторяться. Затем оформляем полученную таблицу в соответствии с заданными требованиями и получаем уже итоговый результат. Как видите, и в этом случае для автоматического распределения данных по группам не потребовалось дополнительных инструментов.
Ну и завершая наше занятия, приведем еще пару примеров.
Вот так выглядит начальная таблица, выгруженная из 1С
А вот так та же таблица, но данные обработаны нашим и формулами с разделением на две группы – бухучет и количественный учет – и разнесением позиций отчета по графам.
Как видно, таблица получилась намного нагляднее и удобнее для расчетов и анализа.
На этом наше занятие завершатся. Мы научились применять функции и формулы не только для вычислений, но и для преобразований исходных таблиц в нужный вид с отбором требуемых групп из исходных ячеек в отдельные колонки новой таблицы. Желающие могут повторить для закрепления указанные действия, используя как исходный файл с начальным примером, так и выгрузку из учебной базы 1С, на которой базировался последний пример. Если остались или появились дополнительные вопросы, обращайтесь к нам по адресам и телефонам, указанным на контактной странице, мы обязательно постараемся помочь. Кроме этого, на нашем ка
Желаем всем успеха в работе и доброго дня!
