Скрытые ячейки в excel

Метод 2: отмена группировки ячеек

Группировка – инструмент Эксель, который также позволяет временно скрывать диапазон строк или столбцов, объединяя их в группы, сворачиваемые и раскрываемые при помощи специальных управляющих кнопок. Разберем метод раскрытия сгруппированных ячеек подробнее:

1. Сначала определяем, что на листе есть скрытые при помощи группировки данные. Если это так, то над панелью названий столбцов, либо слева от панели номеров строк будут отображаться значки в виде плюса (“+”). Нажатие на значок приведет к раскрытию сгруппированных ячеек.
2. Еще один способ, пользуясь которым можно раскрыть сгруппированные ячейки – нажать на кнопки с цифрами, расположенные в то же области, что и значки “+”, описанные выше. Если цифр несколько – выбираем максимальную из них и кликаем по ней левой кнопкой мыши.
3. И после Шага 1, и после Шага 2 скрытые строки или столбцы будут раскрыты.
4. Можно отменить группировку совсем. Для этого выделяем группу столбцов на координатной панели (в нашем случае – горизонтальной), переходим во вкладку “Данные”, находим в ленте кнопку “Структура”, при нажатии на которую будет предложен перечень действий с ячейками. Выбираем команду “Разгруппировать”.
5. Аналогично действуем в случае со скрытыми при помощи группировки строками, за исключением того, что нам нужно теперь выбрать диапазон строк на вертикальной координатной панели.
6. Для удаления группировки можно также использовать сочетание клавиш Shift+Alt+Стрелка влево.

Метод 3: выключение фильтра

Фильтрация данных таблицы – действенный метод поиска и систематизации больших объемов данных. Однако использование данного инструмента делает часть строк в таблице скрытой. Давайте посмотрим, как убрать фильтр, когда он больше не нужен.

1. Находим столбец, который отфильтрован по определенному признаку. Активный фильтр обозначается пиктограммой воронки, расположенной рядом с перевернутой стрелкой.
2. Жмем на пиктограмму фильтра и переходим в окно с настройками фильтрации. Чтобы отобразить данные, отмечаем галочкой каждого значение, либо выбираем верхний пункт “Выделить все”. Затем жмем OK.
3. После отмены фильтра в таблице отобразятся все скрытые элементы.

Если фильтр в таблице больше не нужен, переходим на вкладку “Данные”, где нажимаем кнопку “Фильтр” в разделе инструментов “Сортировка и фильтр”.

Алгоритмы скрытия

Существует несколько вариантов выполнить данную процедуру. Давайте выясним, в чем их суть.

Способ 1: сдвиг ячеек

Наиболее интуитивно понятным вариантом, с помощью которого можно добиться нужного результата, является сдвиг ячеек. Для того, чтобы произвести данную процедуру, наводим курсор на горизонтальную панель координат в месте, где находится граница. Появляется характерная направленная в обе стороны стрелка. Кликаем левой кнопкой мыши и перетягиваем границы одного столбца к границам другого, насколько это можно сделать.

После этого, один элемент будет фактически скрыт за другим.

Способ 2: использование контекстного меню

Намного удобнее для данных целей использовать контекстное меню. Во-первых, это проще, чем двигать границы, во-вторых, таким образом, удаётся достигнуть полного скрытия ячеек, в отличие от предыдущего варианта.

1. Кликаем правой кнопкой мыши по горизонтальной панели координат в области той латинской буквы, которой обозначен столбец подлежащий скрытию.
2. В появившемся контекстном меню жмем на кнопку «Скрыть».

После этого, указанный столбец будет полностью скрыт. Для того, чтобы удостоверится в этом, взгляните на то, как обозначены столбцы. Как видим, в последовательном порядке не хватает одной буквы.

Преимущества данного способа перед предыдущим заключается и в том, что с помощью него можно прятать несколько последовательно расположенных столбцов одновременно. Для этого их нужно выделить, а в вызванном контекстном меню кликнуть по пункту «Скрыть». Если вы хотите произвести данную процедуру с элементами, которые не находятся рядом друг с другом, а разбросаны по листу, то выделение обязательно нужно проводить с зажатой кнопкой Ctrl на клавиатуре.

Способ 3: использование инструментов на ленте

Кроме того, выполнить эту процедуру можно при помощи одной из кнопок на ленте в блоке инструментов «Ячейки».

1. Выделяем ячейки, расположенные в столбцах, которые нужно спрятать. Находясь во вкладке «Главная» кликаем по кнопке «Формат», которая размещена на ленте в блоке инструментов «Ячейки». В появившемся меню в группе настроек «Видимость» кликаем по пункту «Скрыть или отобразить». Активируется ещё один список, в котором нужно выбрать пункт «Скрыть столбцы».

После этих действий, столбцы будут спрятаны.

Как и в предыдущем случае, таким образом можно прятать сразу несколько элементов, выделив их, как было описано выше.

Урок: Как отобразить скрытые столбцы в Excel

Как видим, существуют сразу несколько способов скрыть столбцы в программе Эксель. Наиболее интуитивным способом является сдвиг ячеек. Но, рекомендуется все-таки пользоваться одним из двух следующих вариантов (контекстное меню или кнопка на ленте), так как они гарантируют то, что ячейки будут полностью спрятаны. К тому же, скрытые таким образом элементы потом будет легче обратно отобразить при надобности.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Скрытие/отображение ненужных строк и столбцов

Постановка задачи

Предположим, что у нас имеется вот такая таблица, с которой приходится “танцевать” каждый день:

Кому таблица покажется маленькой – мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.

Задача – временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,

• скрывать подробности по месяцам, оставляя только кварталы
• скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
• скрывать ненужные в данный момент города (я работаю в Москве – зачем мне видеть Питер?) и т.д.

В реальной жизни примеров таких таблиц – море.

Способ 1. Скрытие строк и столбцов

Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide) :

Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide) .

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

Способ 2. Группировка

Если выделить несколько строк или столбцов, а затем выбрать в меню Данные – Группа и структура – Группировать (Data – Group and Outline – Group) , то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):

Более удобный и быстрый способ – использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.

Такой способ скрытия ненужных данных гораздо удобнее – можно нажимать либо на кнопку со знаком “+” или “–“, либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа – тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.

Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением – через меню Данные – Группа и структура – Создать структуру (Data – Group and Outline – Create Outline) . К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.

В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :

Способ 3. Скрытие помеченных строк/столбцов макросом

Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:

Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert – Module) и скопируем туда текст двух простых макросов:

Как легко догадаться, макрос Hide скрывает, а макрос Show – отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик – Вставить – Кнопка (Developer – Insert – Button) .

Способ 4. Скрытие строк/столбцов с заданным цветом

Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие “х” проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:

Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:

. и хотите их скрывать одним движением, то предыдущий макрос придется “допилить”. Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:

Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.

24 комментария

У меня на работе такое сплошь и рядом, пользователи не зная о опции «скрыть» просто мышкой уменьшают ширину колонок (или высоту строк) пока она станет незаметно. Приходилось также мышкой «оттягивать» их обратно.

Теперь зная об изменении размеров мне будет легче, спасибо.

Только на прошлой неделе столкнулся с этой ситуацией, но мысль в сторону изменения высоты строк не пошла и заблудилась) спасибо!

еще один вариант я нашла как победить это бедствие. Сняла защиту, все строки сделала высокой высоты, отобразила — им хоть бы хны. А потом придумала — нужно выделить 1выше и одну ниже от скрытого диапазона, скрыть этот диапазон (любым способом — мышью или меню) затем не снимая выделения со всей скратой части через верхнее меню Формат-Видимость все это отобразить.

Дарья! Да вы гений. Благодарю, ваш метод работает. (до этого ни высота, ни просто показать, не работали. Уже не знал как раскрыть эти «поганые» строчки в Excele Спасибо.

Метод 4: форматирование ячеек

Чтобы скрыть данные отдельных ячеек от посторонних глаз, в программе Excel предусмотрено специальное форматирование, когда формат ячейки задается в виде “ ;;; “. Давайте посмотрим, как определить подобные ячейки и поменять их формат, сделав скрытое содержимое видимым.

1. Определяем ячейки со скрытыми данными – такая ячейка выглядит пустой, но при ее выделении в строке формул отобразятся данные.
2. Выделяем область со скрытыми данными и щелкаем по ней правой кнопкой мыши. В появившемся меню выбираем строку “Формат ячеек…”.
3. В открывшемся окне с параметрами форматирования ячеек на первой вкладке “Число” проверяем установленный формат – он должен быть задан как “;;;”.
4. Чтобы не ошибиться с выбором формата ячеек и снизить вероятность некорректного отображения данных (если Вы не уверены в том, какие данные были внесены) – выбирайте “Общий” формат. Если Вы точно знаете, что все данные были внесены в виде чисел (или, дат, времени, текста и т.д.) – выбирайте соответствующий пункт в списке форматов и затем жмите ОК.
5. Смена формата отображения позволила вновь увидеть скрытые данные в выделенных ячейках. Если данные выглядят неправильно, следует “поиграть” с выбором формата, пока не будет определен корректный вариант.

24 комментария

У меня на работе такое сплошь и рядом, пользователи не зная о опции «скрыть» просто мышкой уменьшают ширину колонок (или высоту строк) пока она станет незаметно. Приходилось также мышкой «оттягивать» их обратно.

Теперь зная об изменении размеров мне будет легче, спасибо.

Только на прошлой неделе столкнулся с этой ситуацией, но мысль в сторону изменения высоты строк не пошла и заблудилась) спасибо!

еще один вариант я нашла как победить это бедствие. Сняла защиту, все строки сделала высокой высоты, отобразила — им хоть бы хны. А потом придумала — нужно выделить 1выше и одну ниже от скрытого диапазона, скрыть этот диапазон (любым способом — мышью или меню) затем не снимая выделения со всей скратой части через верхнее меню Формат-Видимость все это отобразить.

Дарья! Да вы гений. Благодарю, ваш метод работает. (до этого ни высота, ни просто показать, не работали. Уже не знал как раскрыть эти «поганые» строчки в Excele Спасибо.

Скрытие/отображение ненужных строк и столбцов

Постановка задачи

Предположим, что у нас имеется вот такая таблица, с которой приходится “танцевать” каждый день:

Кому таблица покажется маленькой – мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.

Задача – временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,

• скрывать подробности по месяцам, оставляя только кварталы
• скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
• скрывать ненужные в данный момент города (я работаю в Москве – зачем мне видеть Питер?) и т.д.

В реальной жизни примеров таких таблиц – море.

Способ 1. Скрытие строк и столбцов

Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide) :

Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide) .

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

Способ 2. Группировка

Если выделить несколько строк или столбцов, а затем выбрать в меню Данные – Группа и структура – Группировать (Data – Group and Outline – Group) , то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):

Более удобный и быстрый способ – использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.

Такой способ скрытия ненужных данных гораздо удобнее – можно нажимать либо на кнопку со знаком “+” или “–“, либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа – тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.

Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением – через меню Данные – Группа и структура – Создать структуру (Data – Group and Outline – Create Outline) . К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.

В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :

Способ 3. Скрытие помеченных строк/столбцов макросом

Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:

Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert – Module) и скопируем туда текст двух простых макросов:

Как легко догадаться, макрос Hide скрывает, а макрос Show – отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик – Вставить – Кнопка (Developer – Insert – Button) .

Способ 4. Скрытие строк/столбцов с заданным цветом

Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие “х” проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:

Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:

. и хотите их скрывать одним движением, то предыдущий макрос придется “допилить”. Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:

Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.

21 комментарий

У меня на работе такое сплошь и рядом, пользователи не зная о опции «скрыть» просто мышкой уменьшают ширину колонок (или высоту строк) пока она станет незаметно. Приходилось также мышкой «оттягивать» их обратно.

Теперь зная об изменении размеров мне будет легче, спасибо.

Только на прошлой неделе столкнулся с этой ситуацией, но мысль в сторону изменения высоты строк не пошла и заблудилась) спасибо!

еще один вариант я нашла как победить это бедствие. Сняла защиту, все строки сделала высокой высоты, отобразила — им хоть бы хны. А потом придумала — нужно выделить 1выше и одну ниже от скрытого диапазона, скрыть этот диапазон (любым способом — мышью или меню) затем не снимая выделения со всей скратой части через верхнее меню Формат-Видимость все это отобразить.

Дарья! Да вы гений. Благодарю, ваш метод работает. (до этого ни высота, ни просто показать, не работали. Уже не знал как раскрыть эти «поганые» строчки в Excele Спасибо.

В статье рассмотрены три разных способа, как скрыть строки в Excel на ваших листах, а также объясняется, как показать скрытые строки в Excel.

Если вы не хотите, чтобы пользователи видели определенные строки рабочего листа, то тогда можно скрыть такие строки из поля видимости. Этот метод часто используется для скрытия конфиденциальных данных или формул, но вы также можете скрыть неиспользуемые или несущественные области, чтобы пользователи фокусировались на соответствующей информации.

С другой стороны, при обновлении собственных листов или изучении унаследованных книг вам обязательно нужно отобразить все строки и столбцы для просмотра всех данных и понимания зависимостей. В этой статье вы узнаете обо всех вариантах.

Метод 2: отмена группировки ячеек

Группировка – инструмент Эксель, который также позволяет временно скрывать диапазон строк или столбцов, объединяя их в группы, сворачиваемые и раскрываемые при помощи специальных управляющих кнопок. Разберем метод раскрытия сгруппированных ячеек подробнее:

1. Сначала определяем, что на листе есть скрытые при помощи группировки данные. Если это так, то над панелью названий столбцов, либо слева от панели номеров строк будут отображаться значки в виде плюса (“+”). Нажатие на значок приведет к раскрытию сгруппированных ячеек.
2. Еще один способ, пользуясь которым можно раскрыть сгруппированные ячейки – нажать на кнопки с цифрами, расположенные в то же области, что и значки “+”, описанные выше. Если цифр несколько – выбираем максимальную из них и кликаем по ней левой кнопкой мыши.
3. И после Шага 1, и после Шага 2 скрытые строки или столбцы будут раскрыты.
4. Можно отменить группировку совсем. Для этого выделяем группу столбцов на координатной панели (в нашем случае – горизонтальной), переходим во вкладку “Данные”, находим в ленте кнопку “Структура”, при нажатии на которую будет предложен перечень действий с ячейками. Выбираем команду “Разгруппировать”.
5. Аналогично действуем в случае со скрытыми при помощи группировки строками, за исключением того, что нам нужно теперь выбрать диапазон строк на вертикальной координатной панели.
6. Для удаления группировки можно также использовать сочетание клавиш Shift+Alt+Стрелка влево.

Другие ошибки

Также, пользователь может столкнуться с другими ошибками. В зависимости от причины, их можно исправить соответствующим образом.

Формула не растягивается

Растягивание необходимо в том случае, когда несколько ячеек должны проводить одинаковые вычисления с разными значениями. Но бывает, что этого не происходит автоматически. Тогда, следует проверить, что установлена функция автоматического заполнения, которая расположена в параметрах.

Кроме того, рекомендуется повторить действия для растягивания. Возможно, ошибка была в неправильной последовательности.

Неверно считается сумма ячеек

Сумма также считается неверно, если в книге находятся скрытые ячейки. Их пользователь не видит, но система проводит расчет. В итоге, программа отображает одно значение, а реальная сумма должна быть другой.

Такая же проблема возникает, если отображены значения с цифрами после запятой. В таком случае их требуется округлить, чтобы вычисление производилось правильно.

Формула не считается автоматически

Эксель не будет считать формулу автоматически, если данная функция отключена в настройках. Пользователь может устранить данную проблему, если перейдет в параметры, которые находятся в разделе «Файл».

В открытом окне следует перейти к настройке автоматического перерасчета и установить флажок на соответствующей команде. После этого требуется сохранить изменения.

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.

Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Скачать заметку в формате Word или pdf, примеры в формате Excel

ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.

В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы номер_функции от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;С2:F2) рис. 2), скрытие столбца не повлияет на результат.

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

Почему я упоминаю об этой странности? Потому что есть малоизвестное сочетание клавиш для суммирования видимых строк, полученных в результате фильтрации. Попробуйте эти шаги:

1. Выбрать любую ячейку в вашем наборе данных.
2. Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
3. Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
4. Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
5. Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.

Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.