Пример из практики. Понадобилось разобрать вот такие строки из 0 и 1, что на фото 1 в ячейке A2.
Это кусочки BMP, что, впрочем, неважно.
Каждая последовательность длиной 4 байта, т.е. 32 бит. Нужно было извлечь из таких последовательностей серии единиц и измерить длину этих серий.
Для данного примера нужно было получить на выходе 1 2 1 2 7.
Можно было начать с распределения символов по столбцам, использовав штатную Экселевскую приблуду Данные/Текст по столбцам. Однако, это требует ручной установки 31 разделителя, что, конечно же, влом. Хотелось, чтобы было так: загрузил на лист кучку байт и сразу получил результат.
Поэтому пришлось нагородить набор костыликов.
В ячейке B2 избавился от лишних нулей формулой СЖПРОБЕЛЫ. Предварительно пришлось нули заменить на пробелы формулой ПОДСТАВИТЬ, а после сжатия вернуть их на место этой же формулой.
В C2 заехал 0. Это чисто служебный нолик, для дальнейшего копирования формул вправо и вниз.
В D2 — формула (видна на фото 2).
Находит позицию первого нуля. В E2 — второго и т.д. Как видим, в сжатой последовательности (B2) первый ноль — в позиции 2, второй — в 5-й, третий — в 7-й и 4-й ноль — в 10-й. В последовательности всего 4 нуля, и поэтому в H2 отобразилась бы ошибка #ЗНАЧ, если бы не обработка этой ошибки формулой ЕСЛИОШИБКА. Она заменяет #ЗНАЧ на 99. «Почему 99?» — вы можете спросить. Это число нам понадобится в дальнейших расчетах, терпение.
Переходим к фото 3.
Здесь в ячейках AK2:AZ2 собираются в кучку серии единиц. Фактически это аналог того самого Текст по столбцам.
Формула ПСТР выводит в ячейку определённое количество символов из B2, начиная с заданной позиции. Эта позиция определяется позицией найденного на предыдущем шаге нуля плюс 1. Количество символов для вывода вычисляется как разность позиций соседних найденных нулей минус 1.
Формула обернута в другую, обрабатывающую ошибку и заменяющую её пустотой ("").
На фото 4 видно, как подсчитывается количество единиц в каждой серии.
Для этого использована формула ДЛСТР (ячейка BB). Как видите, она тоже не одна в поле воин. Ей помогает формула ЕСЛИ, подавляющая нули.
ОК, глянем на фото 5 — там монстр.
Формула СЦЕПИТЬ не умеет соединять значения из диапазона ячеек, поэтому пришлось вручную тыкать в ячейки, чтоб задать адреса, и вводить & и " ". Копипаст, конечно, облегчил это дело, но всё равно неприятно.
Также задействовал формулу СЖПРОБЕЛЫ, чтобы осталось только по одному пробелу между сериями единиц.
Ну, ладно, в итоге после всех этих манипуляций в ячейке BR2 появились заветные 1 2 1 2 7.
Вы можете спросить: «А при чём здесь Таблицы Гугл?». А при том, что в них вот это вот всё делается одной формулой:
=ARRAYFORMULA(JOIN(" ",LEN(SPLIT(A1;«0»))))
Вполне возможно, что представленный выше алгоритм не оптимален для решения этой задачи, и кто-то подскажет более короткий путь. Более того, буду очень признателен, если кто-то покажет, что и в Excel можно решить её одной формулой. Но мне кое-что непонятно.
Почему в Таблицах Гугл есть формула SPLIT, а в Excel приходится городить огород с ПСТР?
Текст по столбцам не беру в расчёт, потому что там юзер должен кучу кнопок нажимать для получения результата.
Почему в Таблицах Гугл есть формула JOIN, а в Excel СЦЕПИТЬ не умеет работать с диапазоном, а также не позволяет задать унивесальный соединитель?
Почему в Excel только три формулы для работы с массивами, а в Таблицах Гугл достаточно в одной ячейке обернуть предыдущие вычисления в функцию ARRAYFORMULA, и результаты будут выведены в массив ячеек? Кроме того, она позволяет использовать массивы в функциях, не предназначенных для этого.
Будет когда-нибудь в Excel аналог ARRAYFORMULA?
Люблю и Excel (давно), и Таблицы Гугл (недавно), и от несовершенства могучего старичка больно.
Автор: человек со стажем
