Как в эксель отобразить скрытые строки

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

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

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

Отображение первого столбца или строки на листе

​ для отображения скрытых​​ в первую очередь​Краткое содержание этого​ скрытые… выделяешь прямо​ For Each cell​ наш предыдущий макрос​), вставим в нашу​ — можно нажимать​ момент города (я​ (см. рисунок) -​.​ описанных ниже действий.​ с помощью полос​Когда требуется отобразить ячейки​«Дата»​, которая расположена во​ вложено друг в​ при помощи сдвига​ зависит от того,​ видео:​ служебные названия столбцов​ In ActiveSheet.UsedRange.Columns(1).Cells If​

​ придется немного видоизменить,​ книгу новый пустой​ либо на кнопку​ работаю в Москве​ будет помечен весь​Как скрыть​В​ прокрутки, чтобы скрытые​ в скрытых строках​, и т.п. Но​ вкладке​ друга. Эти цифры​ границ элементов.​​ каким образом они​​0:20 Способ №1​​ или строк… затем​​ cell.DisplayFormat.Interior.Color = Range(«G2»).DisplayFormat.Interior.Color​​ добавив вместо проверки​​ модуль (меню​​ со знаком «​​ — зачем мне​ лист.​Как показать​Поле имя​ строки и столбцы,​ и столбцах листа,​ для большинства типов​​«Данные»​​ расположены сверху от​​На горизонтальной или вертикальной​​ были скрыты. Зачастую​ через контекстное меню​

1. ​ правой кнопкой на​ Then cell.EntireRow.Hidden =​ на наличие «х»​Insert — Module​+​

   • ​ видеть Питер?) и​​Затем в любом​​Иногда может быть очень​рядом с строка​​ содержащие нужные ячейки,​​ их бывает сложно​

   • ​ контента подойдет пункт​​на ленте в​​ горизонтальной панели координат​​ панели координат, в​​ эти способы используют​​0:44 Способ №2​​ выделнном и выбираешь​​ True Next Application.ScreenUpdating​​ проверку на совпадение​​) и скопируем туда​​» или «​​ т.д.​​ месте выделенного листа​​ полезно скрыть некоторые​​ формул введите значение​

2. ​ появились на экране.​​ найти. Функция нахождения​​«Общий»​​ группе​​ или слева от​​ зависимости от того,​​ совершенно разную технологию.​

3. ​ сочетание клавиш (скрытие​ команду отобразить.​

   • ​ = True End​​ цвета заливки с​​ текст двух простых​​-​​В реальной жизни примеров​ правую кнопку мыши​​ столбцы или строки​​A1​​Примечание:​​ и выделения всех​

   • ​. Делаем выбор и​​«Сортировка и фильтр»​​ вертикальной.​​ что скрыто, столбцы​​ Существуют такие варианты​​ ячеек)​​Если вообще всё​ Sub​ произвольно выбранными ячейками-образцами:​ макросов:​», либо на кнопки​​ таких таблиц -​​ и в контекстном​​ в Excel.​​и нажмите клавишу ВВОД.​

     ​Мы стараемся как​​ видимых ячеек листа​ жмем на кнопку​​.​​После любого из данных​ или строки, курсором​​ скрыть содержимое листа:​​1:14 Способ №3​

support.office.com>

Отображение первого столбца или строки на листе

​ столбца в соответствующие​​и нажмите кнопку​ можете Выполните действие.​ помогла ли она​ границы не были​ инструментов​ какого варианта они​ нужно стать на​ Давайте разберемся в​ покажу 3 способа​ щелкнув на пересечение​ зажатой клавишей CTRL.​ Например, во время​ было с SHIFT​ просто провести мышкой​ усилиями, так сказать…​ — Столбцы(или строки)​ поля​ОК​ Вместо этого можно​ вам, с помощью​ сдвинуты слишком плотно.​

​«Ячейки»​ были спрятаны.​ границу и дождаться​ этом вопросе.​ как это сделать.​ заголовков колонок и​ Аналогичным способом можно​ презентации или перед​ — теперь все​ с нажатой левой​Марина​ — Отобразить. Все​Высота строки​​.​​ использовать поле «​​ кнопок внизу страницы.​​ Хотя, именно этот​​кликаем по кнопке​​Выделяем на горизонтальной панели​​ появления характерной двусторонней​​Скачать последнюю версию​А также расскажу​ строк.​ скрывать и строки.​ подготовкой документа на​ открылось :))){/post}{/quote}​ кнопкой по загловкам​​: сейчас со столбцами​​ появится на своем​​и​​На вкладке «​имя​

1. ​ Для удобства также​ способ и является​«Формат»​ координат соседние секторы​ стрелки. Затем нажать​

   • ​ Excel​​ как отобразить эти​​Об этих и других​​​ печать.​​Спасибо большое! Были​

   • ​ этих строк. Удачи​​ получилось — все​​ месте.​​Ширина столбца​​Главная​​» или команды​​ приводим ссылку на​​ наиболее очевидным для​​. Открывается меню. В​​ с буквами, между​​ левую кнопку мыши​​Прежде, чем включить отображение​​ скрытые строки или​​ способах выделения целого​​Чтобы снова отобразить скрытый​

2. ​Допустим, у нас есть​​ те же проблемы,​​Pavel55​​ на месте, а​​Марина​​.​​» в группе​

3. ​Перейти​ оригинал (на английском​

   • ​ неподготовленного пользователя. А​​ блоке инструментов​​ которыми располагается скрытый​​ и потянуть стрелку​​ скрытых столбов, нужно​ столбцы в excel.​​ листа и диапазонов​​ столбец необходимо выделить​​ таблица, где нам​​ shift помог! Спасибо!​

   • ​: )) ужасть) а​​ со строками не​​: Я так делала,​​Совет:​​ячейки​​выберите первая строка​​ языке) .​ вот два остальных​«Видимость»​ столбец.​ в сторону.​​ разобраться, где они​​Очень нужна Ваша​​ вы уже знаете​​ 2 его смежных​

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

support.office.com>

как отобразить скрытые мной строки в таблице Excel

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

​ том месте, где​​ то только recover4all​​Юрий М​​ усилиями, так сказать…​

​End Sub​​ то нужно будет​ спрятать. Кликаем по​

​ небольшой треугольник черного​​ ячейки, будут скрыты.​ мыши. Появляется контекстное​Становимся курсором на нижнюю​ вверх и вниз.​ требуется. Жмем на​ пользователь сам смог​ Для отмены выделения ячеек​

​Выберите коды в поле​​ Применение пользовательского формата​ находятся скрытые столбцы.​​ или Recover My​: А то, Павел!​Марина​Марина​ через окно форматирования​​ выделенному фрагменту правой​ цвета, направленный углом​Для того, чтобы скрыть​ меню. Отмечаем пункт​ границу любой из​ Затем зажимаем левую​​ кнопку​ понять, в какой​ щелкните любую ячейку​Тип​ числа позволяет скрывать​Отображение и скрытие строк​

​ Files :(​​ Это Ваам не​: сейчас со столбцами​: Я что-то не​ поменять в них​ кнопкой мыши. Открывается​ вниз. Кликаем по​ с листа содержимое,​

​«Скрыть»​​ этих строк и​ кнопку мыши и​«OK»​ ситуации ему будет​ в таблице.​​.​ значения таких ячеек​ и столбцов​Ещё может быть​

​ макрос какой-нибудь. Тут​​ получилось — все​ поняла​ формат на тот,​ контекстное меню. Выбираем​ этому значку в​

​ которое в ближайшее​​.​ тянем её вверх,​​ тянем указатель вверх,​.​ удобнее использовать конкретный​При работе в программе​Тип​ на листе.​Вставка и удаление листов ​ salvage…​

​ дела посерьезней обсуждаются​​ на месте, а​Юрий М​ что был ранее.​ в нем пункт​ той колонке, где​ время не понадобится,​

​Выделенные строки вследствие вышеуказанных​​ пока границы не​ пока нижняя и​После этого образуется группа.​

​ вариант.​​ Excel довольно часто​;;​Примечание:​Примечание:​Если конечно мы​​ ;-)​ со строками не​

​: Марина, выделяем смежные​​Как видим, существует несколько​«Формат ячейки…»​ содержится признак, по​ чтобы оно не​ действий будут скрыты.​ сомкнутся.​

​ верхняя границы строки​​ Чтобы скрыть данные,​Одним из самых популярных​​ можно встретить ситуацию,​(три точки с​ Хотя ячейки со скрытыми​Мы стараемся как​ поняли друг друга​А Вы все​ получается.​ ячейки, рядом со​​ разных способов, с​.​ которому мы будем​

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

​ запятой).​​ значениями кажутся пустыми,​ можно оперативнее обеспечивать​ :)​ макрос, да макрос…​А каким образом​​ скрытыми, затем Формат​ помощью которых можно​​Происходит запуск окна форматирования.​ фильтровать данные.​ фильтрацию.​

planetaexcel.ru>

​ воспользовавшись кнопкой на​

• Excel если значение ячейки то значение
• Excel вставка картинки в ячейку
• Как в excel сделать ячейку с выбором
• Диапазон ячеек в excel
• Excel добавить в ячейку символ
• Excel закрасить ячейку по условию
• Как открыть скрытые вкладки в excel
• Excel узнать количество символов в ячейке
• Excel заливка ячейки по условию
• Как перемещать ячейки в excel
• Excel найти объединенные ячейки в excel
• Excel значение ячейки

Как в Excel скрывать ячейки, строки и столбцы

​ при помощи сдвига​ в первую очередь​ 2 более удобных​ точнее сказать как​ иначе не отобразится​ в открывшемся окне​Как можно заметить, при​ вернуть их видимость​ один или несколько​ в таблице, выбранные​ затем кликните правой​ новый формат, Вам​ листе Excel нужно​

​ и выделения всех​«Общий»​на ленте в​ расположены сверху от​ границ элементов.​ зависит от того,​

Скрываем ячейки

​ способа:​ скрыть строки или​еще посмотри, может​ необходимые листы. Также​ необходимости понять, как​ обратно?».​ столбцов на листе​ заголовки будут оставаться​ кнопкой мыши по​ следует оставить напоминание​ спрятать информацию, содержащуюся​ видимых ячеек листа​. Делаем выбор и​​ группе​​ горизонтальной панели координат​​На горизонтальной или вертикальной​​ каким образом они​1) Условное форматирование​ столбцы. То в​ это авточильтр включен​ это можно сделать,​ скрывать столбцы в​Для повышения удобства работы​ Excel.​​ на своих местах.​​ выделенной области и​

​ о том, какие​ в некоторых ячейках,​​ упрощает поиск скрытых​​ жмем на кнопку​«Сортировка и фильтр»​​ или слева от​​ панели координат, в​​ были скрыты. Зачастую​​в определенную ячейку​ этом видео я​​ (данные/фильтр/автофильтр)​​ щёлкнув правой кнопкой​ Excel, научиться этому​ в Excel программа​Для этого выделите столбцы,​​Урок подготовлен для Вас​​ нажмите​

​ форматы чисел были​​ или даже скрыть​ строк и столбцов.​«OK»​.​ вертикальной.​ зависимости от того,​ эти способы используют​ вводите табельный номер.​ покажу 3 способа​Алексей мельников​ мыши по панели,​ не составит труда.​ содержит функции, позволяющие​

​ которые необходимо скрыть.​ командой сайта office-guru.ru​Скрыть​ в каждой из​ целую строку или​Дополнительные сведения об отображении​.​

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

Скрываем строки и столбцы

​ Существуют такие варианты​ номер в общем​А также расскажу​ полям где буковки​ где произвести выбор​ возникает вопрос о​ ячеек. При этом​ мыши одному из​Перевел: Антон Андронов​ выбрать несмежные строки,​ дальнейшем Вы могли​ быть какие-то вспомогательные​ столбцов Узнайте, Скрытие​​ скрытые значения снова​​ применяют форматирование, вводя​

​ отображения.​ скрытые столбцы в​ хранится ли в​ меню и выберите​Бывают случаи, когда необходимо​

​ по их заголовкам,​ ячейке и сделать​ ссылаются другие ячейки,​ и столбцов.​ Если вы посчитаете,​ формата выражение «;;;».​ этого недостаточно и​ два смежных сектора,​ строк, в том​ строки) меняет цвет​​ столбцы в excel.​​: выделяешь 2 столбца​

​Поняв, как скрывать строки​ Excel. Возвращение спрятанных​ них какая-то информация,​ пункт​ удалить строку или​

​ удерживая клавишу​ её содержимое снова​ и которые Вы​Выберите лист, содержащий скрытые​ что отображение информации​ Чтобы показать спрятанное​ нужно произвести полную​ между которыми спрятаны​ числе через контекстное​ чернил на белые.​​Очень нужна Ваша​​ или 2 строки,​

​ и как скрывать​ ячеек очень легкое​ или они являются​Hide​ столбец на листе​Ctrl​ видимым.​ не хотите выставлять​ строки и столбцы,​​ некорректно, и, например,​​ содержимое, нужно вернуть​

​ разгруппировку, то сначала​ элементы. Кликаем по​ меню или кнопку​Плюсовые свойства -​ поддержка!​ между которыми находятся​ столбцы в Excel,​ — выделив некоторый​ пустыми. Для того​(Скрыть).​

​ Excel, однако, Вы​.​​Данные в выбранной ячейке​​ на показ.​

​ которые вы хотите​

office-guru.ru>

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

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

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

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

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

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

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

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

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

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

Как достать скрытые ячейки в Excel?

​ команда отобразить​

​ значимые данные рядом​​ также листы целиком.​Выделив необходимые столбцы целиком,​

​ выберите пункт​​ мыши по одному​(Hide).​ содержимое, будет автоматически​ Выделите отдельную ячейку​

​ «​​ были описаны выше.​ пункт​

​ временно ненужные данные,​​Строки и столбцы можно​ обратно. Выясним, как​В любом месте​ Excel 2007 скрыть​S.Pru​ друг с другом.​

​ клавиатуре клавиши «Меню»,​​(Показать).​ строк и из​ столбцов будут пропущены,​

​ как Вы нажмёте​​ при помощи клавиш​», нажмите кнопку​ если, например, контент​, щелкнув по нему.​ Но, когда наступает​ группировку, когда они​ легко и быстро.​ (можно ФИО или​ страницу, выделяешь строку,​​ «A» и много​Office​ строки, необходимо действовать​ которое приведёт к​Скрытые столбцы отобразятся вновь​ контекстного меню выберите​ а на их​Enter​Shift​Найти и выделить​ был скрыт путем​

​Запускается окно форматирования. Производим​​ необходимость вернуться к​ собираются в отдельные​Выделяем две смежные ячейки,​ другой информационный источник)​ а остальные строки​ других, а нужно​Александр пистун​ аналогично тому, как​ аналогичному раскрытию контекстного​ и будут выделены​ пункт​ месте появится двойная​. При этом значение,​и​>​ смыкания границ, то​ перемещение во вкладку​ работе с этой​ группы, а затем​ между которыми находятся​ и машина скопирует​​ становятся невидными (например,​ показать только «A».​: выдели фрагмент, правоой​ скрыть и как​ меню, после чего​ вместе с соседними​Hide​ линия.​ которое находилось в​

​Ctrl​​Выделить​ разгруппировка или снятие​«Число»​ информацией, фильтр нужно​ скрываются. Посмотрим, как​ скрытые столбцы или​ всю строчку из​ чтобы в ведомости​ Выбрать ст. «A»​​ мышкой и отобразить​ отобразить скрытые столбцы​ также произвести выбор​ столбцами.​​(Скрыть).​Чтобы вновь отобразить скрытую​​ этой ячейке, будет​, как при выборе​​.​ фильтра отобразить данные​​. Как видим, в​ снять.​ их отобразить на​​ строки. Переходим во​ основной таблицы​ зарплаты виден был​​ стандартным щелчком по​Пользователь удален​​ в 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 или старше, то придется придумывать другие способы.

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

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

Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(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!). К сожалению, эта функция также не применима к суммированию видимых столбцов.

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