Excel копирование отфильтрованных данных

Ещё один способ ввести один и тот же текст в несколько ячеек эксель-таблицы

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

Выделите диапазон ячеек, который нужно заполнить данными.
Не переставляя курсор мыши и не снимая выделения, введите с клавиатуры нужное «одинаковое» содержимое (оно автоматически введется в первую ячейку выделения), причем не важно — будут там только буквы или буквы с цифрами.
Нажмите сочетание клавиш Ctrl+Enter

… эксель-магия случилась, весь выделенный диапазон заполнился нужными значениями!

Теперь вы знаете всё, чтобы ввести данные в таблицу максимально быстро и эффективно

Важно помнить, что и тот и другой способ имеют свои достоинства — способ №2 очень быстрый и при его использовании нельзя ошибиться, зато способ №1 имеет неоспоримое преимущество — только с его помощью можно заполнить ячейки эксель-таблицы комбинированным содержимым: неизменной текстовой частью и динамически изменяющейся (пересчитываемой) цифровой

Перевод: bussoft.ru, оригинальная статья https://www.computerhope.com/

Три способа, как в Excel добавить строки

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

Как вставить строку в Excel

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

Способ 1: вставка между строк

Первым делом разберемся, как добавить в Excel строки между строк, так как именно это действие пользователи производят чаще всего. А выполняется оно довольно просто:

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

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

Способ 2: вставка в конце таблицы

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

1. Зажав левую кнопку мыши, выделите самую нижнюю строку созданной вами таблицы.
2. Наведите курсор на квадратик, находящийся в нижнем правом углу выделения.
3. Зажмите левую кнопку мыши и протяните это выделение на нужное количество строк вниз.
4. Отпустите клавишу мыши.

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

1. Выделите новые ячейки, информацию из которых нужно убрать.
2. Нажмите правой кнопкой мыши по выделению.
3. Из контекстного меню выберите пункт «Очистить содержимое».

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

Способ 3: создание «умной» таблицы

Чтобы работать с таблицей стало еще проще, необходимо осуществить группировку строк в Excel. Другими словами, создать «умную» таблицу. Это позволит не прибегать ко всевозможным ухищрениям, о которых было рассказано в предыдущем способе. Делается это просто:

1. Выделите всю таблицу.
2. Перейдите на главную вкладку.
3. На панели выберите пункт «Форматировать как таблицу».
4. Из списка шаблонов выберите понравившийся.

Сразу после этого будет создана «умная» таблица. Теперь добавить новые строки можно в ней как в конце, так и в середине. Это был третий способ, как в Excel добавить строки, он же последний.

Заключение

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

Способ 3. Наглядное суммирование

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

1. Выберите мышью нужный столбец или строку.
2. Посмотрите на результат.

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

Как вычислить сумму каждой N-ой строки.

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

Другими словами, двигаясь вниз, нужно брать одну строчку из трех.

Это можно сделать двумя способами.

Первый — самый простой, «передний». Складываем все номера нужного отдела обычной математической операцией сложения. Звучит просто, но представьте, что у вас есть статистика, скажем, за 3 года? Придется вычислить 36 чисел…

Второй способ — более «продвинутый», но универсальный.

Мы пишем

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) +1; 3) = 0; C2: C16))

Как это работает? Нам нужен первый, третий, шестой и так далее. С помощью функции LINE () вычисляем номер текущей позиции. И если остаток от деления на 3 равен нулю, значение будет учтено при вычислении. В противном случае нет.

Для этого счетчика мы будем использовать номера строк. Но наша первая цифра находится во второй строке листа Excel. Так как необходимо начинать с первой позиции, а затем брать каждую третью, а диапазон начинается со 2-й строки, поэтому мы добавляем 1 к его порядковому номеру, тогда счетчик начнет отсчет с числа 3. Для этого, мы используем выражение LINE (C2: C16) +1. Получаем 2 + 1 = 3, остаток от деления на 3 равен нулю. Итак, возьмем первый, третий, шестой и так далее.

Формула массива означает, что Excel должен последовательно перебирать все ячейки в диапазоне от C2 до C16 и выполнять указанные выше операции с каждой из них.

Когда мы найдем продажи для отдела 2, мы изменим выражение:

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16); 3) = 0; C2: C16))

Мы ничего не добавляем, так как первое совпадающее значение находится на 3-й позиции.

Аналогично для Дивизиона 3

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) -1,3) = 0; C2: C16))

Вместо добавления 1 мы теперь вычитаем 1, так что счет начинается с 3. Теперь мы возьмем каждую третью позицию, начиная с 4-й.

И, конечно же, не забудьте нажать CTRL + SHIFT + ENTER.

Примечание. Таким же образом можно суммировать каждый n-й столбец таблицы. Только вместо функции СТРОКА () вам нужно будет использовать СТОЛБЕЦ().

Вставить скопированные строки в Excel с учетом фильтра

​ примере – это​​ Нажимаем левой мышкой​ «Только видимые ячейки».​ «Только видимые ячейки».​ ячейки поставим цифру​) вы используете в​solotze​ автоматом выделяются только​: спасибо! у меня​ панелей экранных кнопок,​ Next i End​ не hidden.​Гость​ по порядку во​

​ не отфильтрована).​​Sub PasteToVisible() Dim​. и в нем нужно поставить​ ячейка А15) нажимаем​ ячейку А9, выбираем​ Нажимаем «ОК». Теперь,​Нажимаем «ОК». Затем вставляем​ 600. В ячейку​ работе не офисный​: F5 нашла как​ видимые ячейки, вы​ тоже excel 2003.​ в самом низу​ SubP.S. Мало данных​Igor_Tr​: вот сам файлик​ все скрытые строки​Гость​ copyrng As Range,​ фиксированную скидку в​ «Вставить». Всё. Получилось​ в контекстном меню​ не отменяя выделения,​ как обычно.​ В2 ставим число​ пакет от Microsoft​ функционирует. Высветилось окошко.​ их ctrl+с и​ а еще можно​ пункт «настройка». Выбрать​ — раз! Все​: Sub Копировать_Отфильтрованное() Dim​http://rghost.ru/38132320​ в столбце. а не​: Уважаемые знающие люди. помогите​ pasterng As Range​ 1000 рублей каждому​ так.​ «значения».​ в первой ячейке​Ещё один способ​ 600, затем копируем​ , а Open​ Но нет «выделить»​ куда нужно ctrl+v​ этот пункт добавить​ его.​ что выше можно​ a As Double​в лист «все​ в нужные отфильтрованные,​ плиз. ​ Dim cell As​ «Ашану».​​Как, не копируя, сразу​Нажимаем «ОК». Вуаля. Скопировалось​ столбца (у нас​ вызвать диалоговое окно​

​ его вниз по​​ Office либо LibreOffice..​

​ArkaIIIa​​solotze​ в меню edit.​3) появится окно​ сделат массивом. Но​ Dim c As​​ районы» в столбец​ побывал и спецвставкой​​Суть есть список​ Range, i As​​Фильтруем наш список Автофильтром,​напечатать данные фильтра в​ не только значение​​ – Е2) вводим​ «Выделение группы ячеек».​ столбцу (тянем за​А рекомендации вам​:​: так и делаю,​

​solotze​​ «Настройка», там на​ я как-то​ Integer Dim r​ «Т» вставить значения​ с галкой «Пропускать​ из около 2000​​ Long ‘запрашиваем у​

​ оставляя на экране​​ Excel​ видимых строк, но​ формулу, цифру, т.д.​ ​ На закладке «Главная»​ правый нижний угол​ дают по MS​solotze​​ не активна F5​: как в экселе​ вкладке «Команды» два​с массивами не​ As Integer Dim​ из листа «ИЗНОС»​​ пустые ячейки». НЕ​ строк с огловлениями​ пользователя по очереди​ только «Ашаны». Вводим​​, смотрите в статье​ и формат ячеек.​ Нажимаем сочетание клавиш​​ в разделе «Редактирование»​ ячейки В2). Значения​ Office. По сему​​Повторно. Не «видя»,​Pelena​ скопировать данные без​ списка.​​ очень. А так​ i As Integer​ из столбца «N»​ ВЫХОДИТ. ​ пустыми строками . ​ диапазоны копирования и​ нужное значение в​ «Закладка листа Excel​Есть один нюанс​​ «Ctrl» + «Enter».​ нажимаем на кнопку​​ скопировались только в​ у вас и​ что у Вас​: F5 — функциональная​ скрытых ячеек​4) в правом​ — чем мог,​​ Dim EndRow As​как то так​Гость​ и 15 столбцами​ вставки Set copyrng​ первую ячейку и​ «Разметка страницы»» здесь.​- вставлять отфильтрованные​В Excel ячейки​ «Найти и выделить».​ видимые ячейки. Точно​ нет тех самых​

​ там на экране​​ клавиша на клавиатуре.​нашла вот это​ списке выбрать категорию​ тем помог.​ Integer ‘как работает?​

Считаем сумму в столбце в Экселе

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

Способ 1: Просмотр общей суммы

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

1. С помощью мыши выделите диапазон ячеек в столбце, сумму значений, которые вы хотите вычислить. В нашем примере это будут ячейки с 3 по 9 в столбце G.

Взгляните на строку состояния (нижняя панель программы) — там будет указана необходимая сумма. Это значение будет отображаться только до тех пор, пока выбран диапазон ячеек.

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

Способ 2: Автосумма

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

1. Щелкните левой кнопкой мыши (ЛКМ) по пустой ячейке, расположенной ниже тех, количество которых вы хотите вычислить.

Щелкните ЛКМ кнопку «Сумма», расположенную на панели инструментов «Редактировать» (вкладка «Главная»). Как вариант, вы можете использовать комбинацию клавиш «ALT» + «=».

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

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

1. Выберите, щелкнув ячейку, в которой будет вычисляться сумма значений.

Нажмите кнопку «Сумма» или воспользуйтесь сочетаниями клавиш, предназначенными для ввода той же формулы.

С помощью мыши выберите диапазон ячеек, который вы хотите добавить, и нажмите «ENTER».

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

Способ 3: Автосумма нескольких столбцов

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

1. Щелкните ЛКМ по ячейке, в которой вы хотите отобразить сумму всех значений.

Нажмите кнопку «Сумма» на панели инструментов или используйте предоставленную комбинацию клавиш для ввода этой формулы.

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

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

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

Вставить в видимые строки в Excel.

​Второй способ.​

​ Фильтром (​ каша.​ перенести в другое​ в ячейку D2​ В первой ячейке​ можно несколькими способами.​ выделяете всю кучу​ дня в эту​Правка​ конце панели, чтоб​ обычно).​​ — 1 +​ собой листы. Нет​​ которые хотим заполнить,​(можно скопировать отфильтрофанные​ i = 1​ не фиксированная, а​Данные — Фильтр​Второй способ.​ место.​ столбца D. Немного​ пишем число, формулу,​ Когда нужно вставить​ сразу и, не​ кнопку​Перейти​ логику панели не​Вопрос в другом.​ _ ActiveSheet.UsedRange.Rows.Count ‘​ никаких одинаковых данных.​ затем в диалоговом​ данные на новый​ For Each cell​ составляет 10% от​

​) отобраны несколько строк.​

Как вставить/добавить новые/пустые строки в Excel? Добавление строк в excel по условию

1. Вставить пустой столбец(строку). Действие описано выше.
2. Вырезать содержимое перемещаемого столбца(строки).
   • Выделить столбец(строку)
   • Выполнить команду Вырезать (кнопка Ножницы на вкладке Главная)
3. Вставить вырезанное в пустой столбец(строку).
   • Кликнуть в первую ячейку столбца(строки)
   • Выполнить команду Вставить (кнопка Вставить на вкладке Главная)
4. Удалить лишний пустой столбец (строку).

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

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

Описание работы

Иногда необходимо вставлять большое количество пустых строк по различным условиям и в ручную этот процесс занимает много времени. Для упрощения данного процесса в функционал надстройки добавлена функция «Вставить пустые строки».

Чтобы воспользоваться функцией перейдите на вкладку «VBA-Excel» в разделе «Ячейки и диапазоны» откройте меню «Вставить», выберите «Вставить пустые строки».

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

Вставка произвольного количества строк

Для этого в поле «Условие» необходимо выбрать пункт «Вставить N пустых строк»Для того что бы указать в какую строку вставлять пустые строки нужно в поле «Начиная со строки» указать номер нужной строки.Это можно сделать двумя способами:

• Вручную ввести номер строки.
• Нажать кнопку «…» расположенную рядом с полем «Начиная со строки», после чего откроется окно выбора строки. Далее нужно выбрать любую ячейку находящуюся в нужной строку и нажать «Ок»

После чего в поле Количество указать сколько пустых строк нужно вставить. И нажать кнопку «Ок»

Например, нужно вставить 5 пустых строк с 7 строки. тогда ввод в диалоговое окно будет выглядеть следующим образом:

Результатом будет:

Вставка строк при изменении значения в столбце

Для этого в поле «Условие» должно быть выбрано «Вставлять по изменению значения в столбце»

В этом режиме пустые строки вставляются в том случае если в выбранном столбце меняется значение.

Для указания столбца, по которому просматриваются значения, нужно в поле «Столбец» ввести имя столбца. Это можно сделать двумя способами:

• Вручную ввести имя столбца
• Нажать кнопку «…» расположенную рядом с полем «Столбец», после чего откроется окно выбора столбца. Далее нужно выбрать любую ячейку находящуюся в нужном столбце и нажать «Ок»

Например, вам нужно отделить каждый новый артикул в столбце А пустой строкой. Тогда диалоговое окно примет вид:

Результат:

Как исправить фильтрацию таблиц Excel?

Вставка в отфильтрованные строки

​ Где найти кнопку​ Cells(EndRow + 5,​ знаю как.​ ячейки.​ ЦЕЛОСТНОСТЬ. ) ХЕЛП. ​Как легко сообразить, макрос​​ отфильтрованные ячейки не​​ сработает, т.к. данные вставятся​. Затем, нажимаем кнопку​Первый способ.​ копировали число 800​ того, где хотим​ Excel». Но, чтобы​: что значит Опен-офис?​это вообще не​ но надо сделать. ​ все действия из​ «ВыделитьВидемыеЯчейки»? В Excel-2003​ c).Activate ‘ Активируем​Вроде все сошлось.​Гость​Serge_007​

Способ 1. Вставка одинаковых значений или формул

​ запрашивает у пользователя​ одинаковые значения или​ не только в​ «Перейти». В появившемся​Есть замечательная функция​ из ячейки D2).​ выделить ячейки –​ вставить данные только​ И как фильтровать?​

​ могу найти. Либо​ArkaIIIa​ моего предыдущего сообщения.​ ее можно найти​

​ последнюю строку _​http://zalil.ru/33260588​: Как вариант могу​: В Excel 2007/2010​ по очереди два​ формулы, а разные,​ видимые, но и​ диалоговом окне, нажимаем​

​ в Excel –​ Получилось так.​ ниже или выше​ в видимые ячейки,​solotze​ я туплю, либо​: Вы же всё​

​Кстати, если впоследствии​ в настройках панелей​ плюс 5 строк.​Olya1985​ скинуть вам файл,​ по умолчанию данные​ диапазона — копирования​ да еще и​ в скрытые ячейки.​ кнопку «Выделить…». В​функция «Специальная вставка» в​Третий способ.​

Способ 2. Макрос вставки любых значений

​ ячейки, в которой​ нужны свои способы,​: _Boroda_,​ версия компа чудная.​ сами написали.​ Вам будет нужно​ управления.​ If Not IsEmpty(Cells(EndRow​: Подскажите, пожалуйста, можно​ попробуете, если все​​ вставляются только в​​ и вставки. Затем​ брать их из другого​ Давайте посмотрим, как​ окне «Выделение группы​​ Excel.​​В новом столбце​ написали число).​

​ особенно, если много​Надо что бы​Не пойму в​1) Выделяете весь​ удалить какие-то кнопки​А где именно?​ + 5, c).Value)​ ли в excel​ получится буду благодарен. ​ отфильтрованные строки, что​ проверяет, чтобы их​ диапазона. Тогда придется использовать​ можно обойти эту​ ячеек» ставим галочку​Итак, у нас​ (в нашем примере​Теперь, или нажимаем​ строк.​ при копировании, скрытые​ чем дело.​ диапазон​ с панелей, то​Семен Казменко​ Then ‘ если​ 2003 после копирования​Гость​ бы вставить во​ размеры совпадали, т.к.​ несложный макрос. Нажмите​ проблему.​ у строки «только​ есть таблица.​ – столбец Е)​

​ сочетание клавиш «Ctrl»​Первый способ -​ ячейки (цветные), не​ArkaIIIa​2) Тыкаете F5​ их можно перетаскивать​: Расскажу только про​ строка не пустая,​ столбца с отфильтрованными​: новые данные вставляются​ все необходимо воспользоваться​ разница в размерностях​ сочетание клавиш​Если вам нужно вставить​ видимые ячейки». Нажимаем​Как установить фильтр, смотрите​ выделяем ячейки. Нажимаем​

Использование промежуточных итогов в Excel — примеры формул

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

Пример 1. Промежуточный итог с функцией 9 или 109?

Как вы уже знаете, ПРОМЕЖУТОЧНЫЕ.ИТОГИ использует 2 набора номеров функций: 1-11 и 101-111. Все они игнорируют отфильтрованные строки, но номера 1–11 учитывают скрытые вручную строки, а номера 101–111 исключают их. Чтобы лучше понять разницу, давайте рассмотрим следующий пример.

Чтобы суммировать отфильтрованные строки, вы можете использовать формулу Промежуточный итог с функцией 9 или с 109, как показано на рисунке ниже:

Но если вы скрыли отдельные элементы вручную,

и теперь хотите суммировать значения только в видимых строках, использовать функцию 109 – это единственный вариант:

Остальные номера функций работают таким же образом. Например, для подсчета непустых отфильтрованных ячеек подойдет формула с функцией 3 или 103. Но только функция 103 может правильно подсчитывать видимые значения, если в диапазоне есть скрытые вручную строки.

Примечание. Функция Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номерами 101-111 игнорирует значения в скрытых строках, но не в скрытых столбцах. Например, если вы используете формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D2:H10) для суммирования чисел в горизонтальном диапазоне (по строке), то скрытие столбца F не повлияет на результат.

Пример 2. Промежуточные итоги с условием.

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

• В одной ячейке создайте раскрывающийся список, содержащий названия функций, такие как СУММ, МАКС, МИН и др.
• В ячейке рядом с раскрывающимся списком введите вложенную формулу ЕСЛИ со встроенными функциями промежуточных итогов, соответствующими именам функций в раскрывающемся списке.

Например, если предположить, что значения для промежуточного итога находятся в ячейках C2: C16, а раскрывающийся список в A17 содержит элементы «Итого», «Среднее», «Максимум» и «Минимум», «динамическая» формула промежуточного итога будет выглядеть следующим образом:

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

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

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

Обратите внимание также на то, что если бы мы использовали для расчёта суммы продаж функцию СУММ, то в этой таблице мы не смогли бы подсчитать продажи по какому-то определённому покупателю или группе покупателей. Пришлось бы для каждого из них использовать функцию СУММЕСЛИ

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

К примеру, вот как можно подсчитать сумму продаж черного шоколада, исключив молочный при помощи фильтра:

Как видите, при помощи СУММ это сделать невозможно. А использовать СУММЕСЛИ — это каждый раз корректировать условие в формуле либо делать несколько формул «на все случаи жизни». А здесь мы просто отбираем нужное при помощи фильтра. Согласитесь, это гораздо проще и удобнее, нежели писать и затем корректировать громоздкие формулы.