Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: вы хотите пронумеровать записи (строки) и заголовки столбцов в базе данных Excel. В дальнейшем при использовании автофильтра некоторые записи будут скрыты, но номера записей пересчитаются и пропусков в нумерации не будет.
В базе данных (рис. 1) записи и столбцы пронумерованы. После применения автофильтра в столбце G (> 335) и скрытии двух столбцов (D и F), пропуски в автонумерации не появились (рис. 2).
Примечание. Поскольку во втором ряду нумерация основана на формулах, после скрытия столбцов нажмите F9, чтобы пересчитать формулы (скрытие столбцов само по себе не является действием, приводящим к пересчету формул).
Рис. 1. Полная база данных
Скачать заметку в формате Word или pdf, примеры в формате Excel
Рис. 2. Ваша цель — иметь метки строк/столбцов, которые нумеруют только видимые строки/столбцы
Решение: для начала задайте имя базе данных, расположенной в диапазоне А2:G13 (рис. 3). [1]
Рис. 3. Создание имени
Это исключит строку 1 из выбора по умолчанию, когда вы примените автофильтр. Также требуется добавить пробел в ячейку сразу после последней записи (в нашем примере — в ячейку А14). Это нужно, чтобы победить досадную ошибку в Excel, вследствие которой показывается последняя запись (строка), независимо от того, соответствует ли она критериям фильтра. На рис. 4а в ячейке А14 ничего нет. После применения фильтра по столбцу G (больше 345) показывается последняя запись со значением 342. На рис. 4б в ячейке А14 имеется пробел. После применения того же фильтра последняя строка не отражается.
Рис. 4. Добавьте пробел, чтобы фильтр работал корректно в отношении последней записи диапазона
В ячейку А3 введите формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B3) и скопируйте её в диапазон А4:А13. Обратите внимание, что первая строка в ссылке является абсолютной, а вторая — относительной. Поэтому при копировании вдоль столбца формула будет меняться. Например, в ячейке А13 формула примет вид: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B13).
В ячейку А2 введите формулу: =ЕСЛИ(ЯЧЕЙКА("ширина";A1)=0;0;1). В ячейку B2 введите формулу: =ЕСЛИ(ЯЧЕЙКА("ширина";B1)=0;0;МАКС($A2:A2)+1) и скопируйте ее вдоль рада в ячейки С2:G2. Обратите внимание, что первая ссылка на столбец абсолютная, а вторая относительная. Формула в G2 примет вид: =ЕСЛИ(ЯЧЕЙКА("ширина";G1)=0;0;МАКС($A2:F2)+1).
Рассмотрим подробнее работу формул. Начнем с автоматической нумерации записей. Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка). 3-й тип соответствует функции СЧЁТЗ. Формула в ячейке А3 использует тот факт, что функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает скрытые ячейки из расчета.
Рассмотрим формулу в А10: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B12). Общее количество текстовых значений в диапазоне B$3:B12 составляет восемь, но только пять значений являются видимыми. Поэтому формула возвращает значение 5, которое является порядковым номером видимых строк!
Примечание. К сожалению, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только для подсчета итогов по столбцам. Попытка применить ее в ряду 2 приведет к неудаче. Вот что сказано в справке Excel по этой функции: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных, скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Для автоматической нумерации столбцов используйте функцию ЯЧЕЙКА. Синтаксис этой функции ЯЧЕЙКА(тип_сведений;[ссылка]). При указании типа_сведений "ширина", функция возвращает ширину столбца для верхней левой ячейки аргумента ссылка.
Формула в ячейке A2 =ЕСЛИ(ЯЧЕЙКА("ширина";A1)=0;0;1) возвращает 0, если столбец является скрытым и 1 — в противном случае. Формула в D2 =ЕСЛИ(ЯЧЕЙКА("ширина";D1)=0;0;МАКС($A2:C2)+1). Поскольку ЯЧЕЙКА("ширина";D1)=0 (столбец D скрыт; см. рис. 2), формула возвращает 0. Аналогично формула возвращает 0 и в ячейке Е2 (столбец также скрыт, см. рис. 2). Формула в следующей (теперь уже видимой) ячейке — F2: =ЕСЛИ(ЯЧЕЙКА("ширина";F1)=0;0;МАКС($A2:E2)+1). Поскольку ЯЧЕЙКА("ширина";F1)>0, формула =ЕСЛИ() вернет МАКС($A2:E2)+1. Фрагмент МАКС() вернет наибольшее значение, которое будет соответствовать последней видимой строк. В нашем примере на рис. 2 это значение 3 для столбца С. Следовательно, значение в F2 = 4.
Резюме: вы можете построить формулы для автонумерации записей (строк) и заголовков столбцов в базе данных, к которой применяется автофильтр и в которой некоторые столбцы могут будут скрыты.
[1] Зачем это сделано из дальнейшего текста Джелена не понятно — Прим. Багузина
