Поиск итоговых строк функцией СМЕЩ
Задача
Переделать таблицу с промежуточными итогами (отчёт из 1С) в плоскую таблицу. Проблема - нет группировок, итоговые строки никак не выделены, отфильтровать их невозможно - текст везде разный.
Детали примера
Бывает, что вместо выгрузки из учётной системы – из 1С, например, мы не выгружаем данные в файл, а копируем и вставляем. Либо отчёт сводной таблицы был скопирован и вставлен значениями в отдельное место.
В обоих случаях мы теряем структуру (группировки) и/или цвет итоговых строк. А это значит, что переделать отчёт в плоскую таблицу, как я показывала в этом видео: https://excelforum.ru/trick/page42 уже не получится. Точнее, получится, но не сразу.
Исходная таблица
Хотим получить такую плоскую
Главный вопрос в этой задаче – как автоматизировать маркировку (отметки) строк с итогами?
К счастью, в таблице есть строгая математическая закономерность – число в итоговой строке точно складывается из нескольких чисел, расположенных под ним. То есть, как только нарастающим итогом мы получим нужную сумму, то следующая строка – это следующий итог и с ним нужно проделать аналогичные расчёты по той же логике.
Первое слово ИТОГ я поставила вручную, а жёлтым выделила ячейку строки, которой нет нужды участвовать в формуле.
Стартовую формулу я написала в третьей строке данных, и смысле её следующий: она вычисляет сумму от ячейки C12 до предыдущей (относительно ячейки с формулой) строки и если получившаяся сумма равна значению первого итога в ячейке С11, то мы нашли следующую итоговую строку.
Конечно, протягивание этой формулы вниз до конца таблицы даёт нам только один результат, ведь мне пришлось зафиксировать знаками $$ начальную ссылку для расчёта суммы и ячейку с первым итогом (об абсолютных ссылках подробно я рассказываю тут: https://excelforum.ru/courses/page3).
На самом деле мы просто проверили, что формула работает.
Следующий шаг состоит в том, чтобы заставить Excel самостоятельно распознавать последнюю известную итоговую строку и делать ссылки для начала суммирования и сравнения с итогом самостоятельно.
Дополнительный столбец с номерами строк содержит простую формулу, где функция ЕСЛИ проверяет, записано ли в соседнем столбце слово ИТОГ и если это так, заносит в ячейку номер текущей строки.
А так как работать с функцией СМЕЩ будет удобнее в пределах таблицы, то я из номера строки листа вычитаю количество строк, расположенных и получаю стартовый номер 1. Далее просто копирую вниз. В ячейках, где строки обычные, не итоговые, будет стоять значение ЛОЖЬ. И пусть, нам это не мешает.
На самом деле мы просто проверили, что формула работает.
Следующий шаг состоит в том, чтобы заставить Excel самостоятельно распознавать последнюю известную итоговую строку и делать ссылки для начала суммирования и сравнения с итогом самостоятельно.
В этом нам помогут дополнительный столбец с номерами итоговых строк и функция массива СМЕЩ.
Дополнительный столбец с номерами строк содержит простую формулу, где функция ЕСЛИ проверяет, записано ли в соседнем столбце слово ИТОГ и если это так, заносит в ячейку номер текущей строки.
А так как работать с функцией СМЕЩ будет удобнее в пределах таблицы, то я из номера строки листа вычитаю количество строк, расположенных и получаю стартовый номер 1. Далее просто копирую вниз. В ячейках, где строки обычные, не итоговые, будет стоять значение ЛОЖЬ. И пусть, нам это не мешает.
Пока формула будет показывать номера только двух строк и это нормально, ведь формула в столбце I:I ещё не автоматизирована и тоже распознаёт всего две итоговых строки.
Формулу для поиска ссылки на ячейку с предыдущим итогом протестируем в свободной ячейке рядом (потом мы внедрим её в основную формулу).
Логика поиска ссылки следующая: ячейка с предыдущим (относительно ячейки с текущей формулой) итогом находится в строке с максимальным номером, который указан в колонке J:J (диапазон поиск максимального номера также всегда начинается от первой записи, поэтому зафиксирован знаками $$). Поэтому достаточно сместить стартовую ссылку C10 на такое же число строк вниз, с чем функция СМЕЩ прекрасно справляется.
Формулу для поиска ссылки на ячейку с предыдущим итогом протестируем в свободной ячейке рядом (потом мы внедрим её в основную формулу).
Логика поиска ссылки следующая: ячейка с предыдущим (относительно ячейки с текущей формулой) итогом находится в строке с максимальным номером, который указан в колонке J:J (диапазон поиск максимального номера также всегда начинается от первой записи, поэтому зафиксирован знаками $$). Поэтому достаточно сместить стартовую ссылку C10 на такое же число строк вниз, с чем функция СМЕЩ прекрасно справляется.
После ввода формулы в ячейке мы увидим значение ячейки с последним итогом, то есть 40 876 745,00
Формула для поиска стартовой ячейки суммирования полностью аналогична предыдущей, только суммировать мы начинаем с ячейки, расположенной на 1 ниже итоговой, поэтому к номеру строки добавляем в формуле +1.
Шаг последний – меняем в главной формуле две прямые ссылки на ссылки, созданные функцией СМЕЩ (как переносить формулы друг в друга, я рассказываю на открытом уроке Создание мегаформул: https://excelforum.ru/raspisanie. Урок можно посмотреть в записи.
Финал. Формулы обоих столбцов I:I и J:J копируем вниз, в итоговых строках везде появляется слово ИТОГ. Теперь можно выполнить обработку, которую я показываю здесь: https://excelforum.ru/trick/page42 и плоская таблица готова.
История примера – я получила письмо, в котором чуть ли ни первым предложением звучала просьба помочь переделать таблицу формулами, без макросов. Знакомое желание :)) Кажется, и Power Query здесь тоже не помощник. Как видите, функции массива могут многое. С ними формулы работают по нашим правилам, а не мы по их.
Поэтому желаю вам долгой и крепкой дружбы с формулами - и с простыми, и с профессиональными. Буду рада, если вы сможете присоединиться к обучению на курсе Формулы массива, первый урок можно посмотреть бесплатно: https://excelforum.ru/courses/page6
Пусть мощь формул окажется в ваших руках.
А я буду рада помочь этому сбыться.