Автоматическая перенумерация строк при применении фильтра в EXCEL

При применении фильтра строки скрываются целиком. Если в таблице имелась нумерация строк, то она будет сбита. Но, есть способ обойти эту проблему.


Пусть имеется таблица с нумерацией строк (в столбце введены числа от 1 до 16).

Если к таблице применить фильтр (нажав в строке заголовков таблицы CTRL+SHIFT+L и установив критерии отбора), то нумерация строк будет сбита (см. столбец А ).

Для того, чтобы сохранить нумерацию строк, используем вместо последовательности констант (от 1 до 16) - формулу.

Решение основано на использовании функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , которая «чувствует», когда строка скрыта фильтром .

Столбец с нумерацией расположим в столбце А (заголовок таблицы разместим в строке 6 ). Полезные данные, которые вводятся БЕЗ ПРОПУСКОВ, расположим в столбце В (см. Файл примера ):

Записав в ячейке A 7 формулу =ЕСЛИОШИБКА(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B7);A6+1;A6);1)

и скопировав ее в ячейки ниже, получим желаемый результат. Если строка скрыта, то функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() возвращает 0 (ЛОЖЬ) и счетчик номера строки не увеличивается на 1, а остается, как у предыдущей строки.

После применения фильтра нумерация строк таблицы автоматически изменится.


Комментарии
Только для авторизованных пользователей

Аноним, 19 сентября 2016 г.
Все работает, спасибо!
Аноним, 4 апреля 2017 г.
ничего не работает, не спасибо
Михаил, 6 апреля 2017 г.
Реальность другая: все работает, Вы не разобрались. Задавайте вопросы, если не понятно.
Аноним, 7 июля 2017 г.
СПАСИБО!!!
Аноним, 10 июля 2017 г.
Спасибо!
Аноним, 4 марта 2018 г.
Подтверждаю, все работает, спасибо автору!
Аноним, 6 мая 2018 г.
Все работает, спасибо!
Аноним, 6 мая 2018 г.
Есть другая проблема: как можно прописать в эту формулу еще то, чтобы она не считала повторяющиеся значения? Может кто подскажет?!
Аноним, 25 мая 2018 г.
Спасибо!
Аноним, 10 апреля 2019 г.
Возможен еще такой вариант, данную формулу протягиваю в столбце А. В столбце В прописан ассортимент который нужно пронумеровать=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$7:B7)
Аноним, 25 сентября 2019 г.
Отлично! То, что надо!!!
Аноним, 15 января 2020 г.
Супер!!!!!! Огромное спасибо!
Аноним, 10 августа 2020 г.
Пожалуйста, объясните построение и значение составляющих в формуле
Михаил, 10 августа 2020 г.
Поясняю. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B7) подсчитывает значения, т.к. первый аргумент =3 (функция СЧЕТЗ), но не как обычно в диапазоне ячеек, а всего лишь в единственной ячейке справа. Если строка скрыта, ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает 0 (в этом собственно и смысл этой функции), а если нет, то 1, т.к. в ячейке справа содержится только одно значение. Значение 0 эквивалентно логическому ЛОЖЬ, поэтому, если строка скрыта, то функция ЕСЛИ() возвратит А6. Нумерация строки не меняется, т.е. остается, такой же как предыдущая. Число 1 эквивалентно ИСТИНА. Поэтому, если строка не скрыта, то функция ЕСЛИ() возвратит А6+1. Нумерация строки меняется.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved