Excel как закрепить итоговую строку при применении фильтра
Здравствуйте.
Извините за большое количество текста. сам вопрос в последних трех строках. сначала предыстория.
Ситуация следующая: есть табличка, довольно большая по количеству строк. суть простая, в первой колонке Покупатель (название), а во второй - сумма, есть ещё пару колонок, но это не важно. Важно то, что в таблице порядка 1000 строк, и она продолжает расти.
А т.к. в таблице так много строк, то включен фильтр. Делается отбор по Покупателю и смотрим сколько раз и по сколько денег он платил.
но чтобы каждый раз не мотать прокрутку туда-сюда при изменении фильтра, включено закрепление областей. Закреплена верхняя строка (заголовок таблицы).
далее самое интересное.
начнем с самого начала. В таблице фильтр включен, но отображаются все строки. Включаем Закрепление областей, начинаем работать.
допустим, делаем отбор по Покупателю Иван.
теперь у нас отображаются строки 1, 54, 90, 158, 567 и т.д.
снова в фильтре делаем Отобразить все.
теперь отображаются строки 1, 2, 3, 4, 5, 6 и т.д.
прокручиваем страницу вниз - первая строка видна, остальные прокручиваются.
изменяем фильтр, ставим отбор по Покупателю Андрей.
теперь у нас отображаются строки 1, 89, 144, 145, 590 и т.д. казалось бы, все отлично.
сохраняем документ, закрываем, открываем снова.
видим что у нас стоит фильтр по Андрею и отображаются те же самые строки: 1, 89, 144, 145, 590 и т.д.
делаем в фильтре Отобразить все. и снова у нас отображаются строки 1, 2, 3, 4, 5, 6 и т.д.
И вот если в этот момент мы захотим воспользоваться прокруткой, то у нас не сдвинется с места ни одна ячейка!
ползунок прокрутки справа двигается, а все ячейки стоят на месте.
если снять закрепление областей, и снова поставить, то все становится хорошо, до следующего открытия файла.
как я понял, суть в том, что при открытии, почему-то область закрепления смещается. и закрепляет не линию после первой строки. а Закрепляет линию ПЕРЕД 89 строкой (это в случае, если на момент закрытия Excel, фильтр стоял по Андрею).
а т.к. 89-я строка не влазит в один экран, а находится где-то ниже, то у нас весь первый экран закреплен и не пролистывается.
как сделать так, чтобы при открытии файла закреплялась только первая строка, а не та, где стоял фильтр?
каждый день снимать-ставить закрепление - не вариант.
каждый день заканчивать работу с файлом, выключая фильтр - тоже не вариант.
p.s. буду благодарен за любые подсказки в решении данного вопроса.
Здравствуйте.
Извините за большое количество текста. сам вопрос в последних трех строках. сначала предыстория.
Ситуация следующая: есть табличка, довольно большая по количеству строк. суть простая, в первой колонке Покупатель (название), а во второй - сумма, есть ещё пару колонок, но это не важно. Важно то, что в таблице порядка 1000 строк, и она продолжает расти.
А т.к. в таблице так много строк, то включен фильтр. Делается отбор по Покупателю и смотрим сколько раз и по сколько денег он платил.
но чтобы каждый раз не мотать прокрутку туда-сюда при изменении фильтра, включено закрепление областей. Закреплена верхняя строка (заголовок таблицы).
далее самое интересное.
начнем с самого начала. В таблице фильтр включен, но отображаются все строки. Включаем Закрепление областей, начинаем работать.
допустим, делаем отбор по Покупателю Иван.
теперь у нас отображаются строки 1, 54, 90, 158, 567 и т.д.
снова в фильтре делаем Отобразить все.
теперь отображаются строки 1, 2, 3, 4, 5, 6 и т.д.
прокручиваем страницу вниз - первая строка видна, остальные прокручиваются.
изменяем фильтр, ставим отбор по Покупателю Андрей.
теперь у нас отображаются строки 1, 89, 144, 145, 590 и т.д. казалось бы, все отлично.
сохраняем документ, закрываем, открываем снова.
видим что у нас стоит фильтр по Андрею и отображаются те же самые строки: 1, 89, 144, 145, 590 и т.д.
делаем в фильтре Отобразить все. и снова у нас отображаются строки 1, 2, 3, 4, 5, 6 и т.д.
И вот если в этот момент мы захотим воспользоваться прокруткой, то у нас не сдвинется с места ни одна ячейка!
ползунок прокрутки справа двигается, а все ячейки стоят на месте.
если снять закрепление областей, и снова поставить, то все становится хорошо, до следующего открытия файла.
как я понял, суть в том, что при открытии, почему-то область закрепления смещается. и закрепляет не линию после первой строки. а Закрепляет линию ПЕРЕД 89 строкой (это в случае, если на момент закрытия Excel, фильтр стоял по Андрею).
а т.к. 89-я строка не влазит в один экран, а находится где-то ниже, то у нас весь первый экран закреплен и не пролистывается.
как сделать так, чтобы при открытии файла закреплялась только первая строка, а не та, где стоял фильтр?
каждый день снимать-ставить закрепление - не вариант.
каждый день заканчивать работу с файлом, выключая фильтр - тоже не вариант.
p.s. буду благодарен за любые подсказки в решении данного вопроса. Гость
И вот если в этот момент мы захотим воспользоваться прокруткой, то у нас не сдвинется с места ни одна ячейка!
ползунок прокрутки справа двигается, а все ячейки стоят на месте.
если снять закрепление областей, и снова поставить, то все становится хорошо, до следующего открытия файла.
как я понял, суть в том, что при открытии, почему-то область закрепления смещается. и закрепляет не линию после первой строки. а Закрепляет линию ПЕРЕД 89 строкой (это в случае, если на момент закрытия Excel, фильтр стоял по Андрею).
а т.к. 89-я строка не влазит в один экран, а находится где-то ниже, то у нас весь первый экран закреплен и не пролистывается.
как сделать так, чтобы при открытии файла закреплялась только первая строка, а не та, где стоял фильтр?
каждый день снимать-ставить закрепление - не вариант.
каждый день заканчивать работу с файлом, выключая фильтр - тоже не вариант.
p.s. буду благодарен за любые подсказки в решении данного вопроса. Автор - Гость
Дата добавления - 23.03.2012 в 09:00
Как подсчитать промежуточные итоги только видимых ячеек после фильтрации в Excel?
Предположим, вы вычислили общее значение из столбца с функцией Sum. При фильтрации данных в столбце функция Sum добавляет как видимые, так и скрытые ячейки. Если вы хотите получить промежуточные итоги только для видимых ячеек после фильтрации, методы из этого руководства вам помогут.
Подытог только видимых ячеек после фильтрации с помощью функции Промежуточный итог
Фактически, функция «Промежуточный итог» может помочь вам суммировать только видимые ячейки после фильтрации в Excel. Пожалуйста, сделайте следующее.
Синтаксис
=SUBTOTAL(function_num,ref1,[ref2],…)
аргументы
- Номер_функции (Обязательно): число от 1 до 11 или от 101 до 111, которое указывает функцию, используемую для промежуточного итога.
- Ref1 (Обязательно): именованный диапазон или ссылка, для которой требуется подытог.
- Ref2 (Необязательно): именованный диапазон или ссылка, по которым вы хотите вычислить промежуточные итоги.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=SUBTOTAL(9,C2:C13)
Примечание:
С этого момента, когда вы фильтруете данные столбца, функция ПРОМЕЖУТОЧНЫЙ ИТОГ суммирует только видимые ячейки, как показано на скриншоте ниже.
Подытог только видимые ячейки после фильтрации с помощью удивительного инструмента
Здесь рекомендую СУМВИДИМЫЕ ячейки Функция Kutools for Excel для вас. С помощью этой функции вы можете легко суммировать только видимые ячейки в определенном диапазоне несколькими щелчками мыши.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода результата, щелкните Кутулс > Kutools Функции > Статистический И математика > НЕВЕРОЯТНО. Смотрите скриншот:
2. в Аргументы функций в диалоговом окне выберите диапазон, в котором будет произведен промежуточный итог, а затем нажмите кнопку OK кнопку.
Затем вы увидите, что формула создана в выбранной ячейке. Когда вы фильтруете данные столбца, суммируются только видимые ячейки.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме
Удалять только видимые строки или столбцы в Excel
В Excel мы обычно скрываем некоторые важные строки или столбцы информации, иногда нам нужно удалить только видимые строки или столбцы, если вы удалите их напрямую с помощью клавиши Delete, скрытые значения будут удалены одновременно. Как удалить видимые строки или столбцы, не удаляя скрытые строки или столбцы в Excel? В этом руководстве будут представлены способы удаления только видимых строк.
Суммировать значения без промежуточных итогов или исключить их в Excel
Предположим, у вас есть список данных, смешанных с несколькими ячейками промежуточных итогов, когда вам нужно суммировать итоги, все промежуточные итоги включаются в окончательное суммирование. Смотрите скриншот ниже. Что бы вы сделали для суммирования значений без промежуточных итогов в Excel? В этой статье мы покажем вам быстрый способ этого добиться.
Одновременная фильтрация нескольких столбцов в Excel
Когда вы применяете функцию «Фильтр», после фильтрации одного столбца следующие столбцы будут фильтроваться только на основе результата предыдущего отфильтрованного столбца. Это означает, что только критерий И может применяться к более чем одному столбцу. Как в этом случае применить критерии И и ИЛИ для одновременной фильтрации нескольких столбцов на листе Excel? Ваш метод в этой статье.
Отфильтровать или выбрать ячейки по цвету ячеек в Excel
Обычно вы можете заливать ячейки разными цветами для любых целей в Excel. Если у вас есть рабочий лист с использованием разных цветов для обозначения разного типа содержимого, и вы хотите отфильтровать или выбрать эти ячейки по цвету ячеек, вы можете сделать это с помощью методов, описанных в этом руководстве:
Как отфильтровать данные, исключая нижнюю итоговую строку в Excel?
Если в конце ваших данных есть итоговая строка, когда вы фильтруете данные, обычно конечная итоговая строка также будет отфильтрована, но иногда вам нужно сохранить итоговую строку при применении функции фильтрации, как показано на следующем снимке экрана. показано. Как можно исключить нижнюю итоговую строку из фильтра на листе?
Данные фильтра исключают нижнюю итоговую строку, создавая таблицу
Таблица в Excel может помочь вам решить эту проблему, пожалуйста, сделайте следующее:
1. Удалите существующую итоговую строку, которую вы рассчитали, а затем выберите диапазон данных.
2. Затем нажмите Вставить > Настольные, в выскочившем Создать таблицу диалоговое окно, отметьте В моей таблице есть заголовки если у диапазона данных есть заголовки, см. снимок экрана:
3, Затем нажмите OK кнопка, таблица была создана сразу, затем выберите таблицу под Дизайн вкладка, проверка Итоговая строка в Параметры стиля таблицы группа, и итоговая строка была вставлена в конец таблицы, см. снимок экрана:
4. И теперь, когда вы фильтруете эту таблицу, нижняя итоговая строка будет сохранена как обычно, см. Снимок экрана:
Данные фильтра исключают нижнюю итоговую строку путем вставки формулы
Если вы не хотите создавать таблицу, SUBTOTAL также может оказать вам услугу, пожалуйста, сделайте следующее:
1. Введите эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (9; B2: B13) в нижний ряд, см. снимок экрана:
2, Затем нажмите Enter ключ, чтобы получить общее значение, и теперь, если вы отфильтруете эти данные, итоговая строка будет исключена и сохранена в конце данных.
Как заморозить сразу верхнюю и нижнюю строку в Excel?
В Excel мы можем легко закрепить верхнюю строку рабочего листа. Иногда существует большой рабочий лист с длинными данными списка, и мы хотим зафиксировать как верхнюю, так и нижнюю строки, чтобы мы могли одновременно просматривать строку с верхними заголовками и последнюю строку. У нас нет прямого способа решить эту проблему, к счастью, мы можем использовать некоторые обходные пути для решения этой задачи.
Закрепите верхнюю и нижнюю строки, просматривая данные рядом
Обычно большинство из нас рассматривают возможность просмотра данных в двух разных окнах бок о бок. Пожалуйста, сделайте следующее:
1. Откройте свою книгу и активируйте желаемый рабочий лист.
2. Затем нажмите Смотреть > New Window чтобы открыть этот рабочий лист в новом окне, см. снимок экрана:
3. Затем нажмите Смотреть > Упорядочить все, В Расставить окна диалоговое окно, выберите горизонтальный вариант под Организовать, смотрите скриншоты:
![]() | ![]() | ![]() |
Функции: Если есть несколько открытых книг, вам нужно проверить Windows, активна ли книга.
4. Затем нажмите OK, и теперь вы можете видеть, что две книги расположены по горизонтали, вы можете настроить вертикальную высоту обоих окон по своему усмотрению и прокрутить вниз до нижней строки на листе ниже. Смотрите скриншот:
5. После замораживания нижней строки теперь вам нужно заморозить верхнюю строку на приведенном выше листе, щелкните одну ячейку в приведенном выше окне и нажмите Смотреть > Замерзшие оконные стекла > Freeze Top Row, см. снимок экрана:
6. И теперь верхний ряд был заморожен, вам просто нужно прокрутить верхнее окно, одновременно просматриваются и верхний ряд, и нижний ряд.
Заморозьте верхнюю и нижнюю строки, создав таблицу
За исключением описанного выше способа, мы также можем заморозить верхнюю и нижнюю строки, вставив таблицу. Для этого выполните следующие действия:
1. Выберите нужный диапазон данных и нажмите Вставить > Настольные, В Создать таблицу диалог, проверьте В моей таблице есть заголовки вариант, см. скриншоты:
![]() | ![]() | ![]() |
2. И таблица была создана, щелкните одну ячейку в диапазоне данных и прокрутите вниз, заголовки таблицы автоматически стали меткой строки, см. Снимок экрана:
3. Затем вам нужно закрепить только нижнюю строку, выберите всю нижнюю строку и нажмите Смотреть > Трещина, см. снимок экрана:
4. И теперь вы можете просматривать одновременно верхний и нижний ряды.
Внимание: Когда вы прокручиваете данные, вы должны поместить курсор в диапазон данных.
При работе с большими наборами данных в Excel вам может потребоваться заблокировать определенные строки или столбцы, чтобы вы могли видеть их содержимое при прокрутке в другую область рабочего листа. Ниже вы найдете подробные инструкции о том, как закрепить одну или несколько строк, зафиксировать один или несколько столбцов или «заморозить» и то, и другое одновременно, а также как снять блокировку. В Excel есть специальные инструменты, чтобы зафиксировать шапку таблицы, первые столбцы или же и то, и другое сразу.
Возможность закрепить строку — очень важный лайфхак в Экселе. Научившись этому простому приему, вы сможете просматривать любую область таблицы, не теряя из поля зрения строку с именами столбцов или так называемую «шапку» таблицы.
Это можно легко сделать с помощью меню Закрепить области и нескольких других функций.
Итак, как закрепить строку в Excel? Разберём самое важное:
«Замораживание» на экране части информации - это всего лишь несколько щелчков мышью. Вы просто нажимаете вкладку «Вид» > и выбираете один из параметров в зависимости от того, что вы хотите заблокировать (как на скриншоте ниже).
А теперь объясним подробнее.
Как закрепить верхнюю строку в Excel.
Если ваша таблица содержит большое количество строк, то при прокрутке вниз шапка с названиями колонок скроется из виду. И вам будет достаточно сложно ориентироваться в изобилии цифр. Ведь довольно сложно запомнить, что записано в каждом из столбцов. Или придется постоянно прокручивать таблицу вверх-вниз. Поэтому эту проблему нужно обязательно решить, закрепив одну или несколько верхних строк таблицы.
Рассмотрим, как зафиксировать строку в Excel при прокрутке таблицы вниз. Перейдите на вкладку «Вид» и действуйте так, как показано на рисунке.
Это действие заблокирует самую первую строку в вашем рабочем листе Excel, чтобы она оставалась на виду при навигации по данным.
Выбираем пункт "Закрепить верхнюю строку".
Вы можете визуально определить, что шапка таблицы «заморожена», видя серую линию под ней:
Закреплена первая строка
Как «заморозить» несколько строк.
Но очень часто бывает, что шапка таблицы расположена в нескольких верхних строках. Если вы хотите заблокировать несколько строчек (начиная с первой), то выполните следующие действия:
- Выберите строку (или просто первую позицию в ней), информацию выше которой вы хотите видеть постоянно. Например, если заголовки столбцов занимают первые две строки, то установите курсор в первую ячейку третьей строки.
- На вкладке «Вид» нажмите
Например, чтобы заблокировать две верхние строки на листе, мы выбираем ячейку A3 и выбираем первый пункт в меню:
Закрепляем область выше и левее курсора.
Как видно на скриншоте, если мы хотим зафиксировать первые две строчки, устанавливаем курсор на третью. В результате вы сможете прокручивать содержимое листа, а на экране всегда будет оставаться шапка таблицы.
Предупреждения:
- Учтите, что при закреплении области таблицы будут зафиксированы все строки и столбцы, которые находятся выше и левее указанной ячейки. Поэтому, если вы хотите закрепить только строки, то установите курсор в ячейку первого столбца перед использованием инструмента.
- Microsoft Excel при прокрутке таблицы позволяет замораживать только содержимое верхней части рабочего листа. Невозможно заблокировать что-либо в середине.
- Убедитесь, что все фиксируемые данные видны в момент «замораживания». Если какие-то данные не видны, то они будут скрыты.
- Если вы вставите строку перед той, что была закреплена (в нашем случае, между первой и второй), то она тоже будет зафиксирована.
Как зафиксировать столбец в Excel
Теперь рассмотрим, как закрепить столбец в Excel при прокрутке вправо. Эта операция выполняется аналогично рассмотренным выше действиям со строками.
Как заблокировать первый столбец.
Чтобы закрепить столбец при прокрутке вправо, выберите в выпадающем меню — «Закрепить первый столбец».
Это сделает первую слева колонку постоянно видимой во время горизонтальной прокрутки вправо.
Как «заморозить» несколько столбцов.
Чтобы зафиксировать более одного столбца, это то, что вам нужно сделать:
- Выберите столбец (или первую позицию в нем) справа от последнего столбца, который вы хотите заблокировать.
- Перейдите на вкладку «Вид» и нажмите
Например, чтобы заморозить первые два столбца, выделите всю колонку C или просто поставьте курсор в ячейку C1 и нажмите как на скриншоте:
Зафиксируем первые два столбца
Это позволит вам постоянно видеть первые 2 позиции с левой стороны при перемещении по рабочему листу.
Предупреждения:
- Вы можете закрепить столбец только слева, на краю вашей таблицы. Столбцы в середине таблицы не могут быть зафиксированы.
- Все столбцы, которые нужно заблокировать, должны быть видны. Все, которые не видны, будут скрыты после фиксации.
- Если вы вставите колонку перед той, что была закреплена, новая колонка будет также закрепленной. В нашем примере, если вы вставите столбец между A и B, то Excel при прокрутке зафиксирует уже 3 первых столбца.
Как закрепить строку и столбец одновременно.
Помимо блокировки по отдельности, Microsoft Excel позволяет замораживать одновременно ячейки по вертикали и горизонтали. Вот как делается закрепление областей :
- Выберите клетку ниже и справа от той области, которую вы хотите заблокировать. К примеру, если мы хотим постоянно видеть на экране шапку таблицы, состоящую из двух строчек, а также первые две колонки слева, то ставим курсор в C3.
- На вкладке «Вид» выберите .
Да, это так просто :)
Примечание. Фиксируется весь диапазон, который находится выше и левее курсора.
Таким же образом вы можете заморозить столько по вертикали и горизонтали, сколько захотите. Но при условии, что вы начинаете с верхнего ряда и самой левой колонки.
Например, чтобы заблокировать верхнюю строку и первые 2 столбика, вы выбираете C2; чтобы заморозить первые две строчки и первые две колонки, выберите C3 и т. д.
Обратите внимание, что, чтобы закрепить строку и столбец, действуют те же правила, о которых мы говорили выше. Зафиксировано будет только то, что видно на экране. Все, что находится выше и правее и не попадает на экран на момент совершения операции, будет просто скрыто.
Как разблокировать строки и столбцы?
Чтобы разблокировать «зафиксированные» данные, перейдите на вкладку «Вид» и нажмите «Снять закрепление …». Эта кнопка появляется в меню, только если вы закрепили какие-то строки либо столбцы.
При этом уже не важно, где расположен курсор и в каком месте листа вы находитесь.
Почему не работает?
Если кнопка отключена (выделена серым цветом) то, скорее всего, это происходит по следующим причинам:
- Вы находитесь в режиме редактирования. Например, происходит ввод формулы или редактирование данных. Чтобы выйти из режима редактирования, нажмите Enter или Esc.
- Ваш рабочий лист защищен. Пожалуйста, сначала снимите защиту рабочей книги, а затем действуйте, как описано выше в этой статье.
Другие способы закрепления столбцов и строк.
Еще один способ несколько нестандартный способ - это разделить экран на несколько частей. Особенность этого приёма заключается в следующем:
Разделение панелей делит ваш экран на две или же четыре области, которые можно просматривать независимо друг от друга. При прокрутке внутри одной из них ячейки в другой остаются на прежнем месте.
Чтобы разделить окно на части, выберите позицию снизу или справа от того места, в котором вы хотите это сделать, и нажмите соответствующую кнопку. Чтобы отменить операцию, снова нажмите её же.
Еще один способ - использование «умной» таблицы для блокировки её заголовка.
Если вы хотите, чтобы заголовок всегда оставался фиксированным вверху при прокрутке вниз, преобразуйте диапазон в таблицу Excel:
Самый быстрый способ создать таблицу в Excel - нажать Ctl + T.
Однако при этом учитывайте, что если шапка состоит более чем из 1 строчки, то зафиксируется только первая из них. В результате может получиться не очень красиво.
Печать заголовка таблицы на каждой странице
Если вы хотите повторить одну или несколько верхних строк листа Excel на каждой напечатанной странице, переключитесь на вкладку «Разметка страницы» и нажмите кнопку «Печатать заголовки». Далееперейдите на вкладку «Лист» и выберите сквозные строки.
Несколько советов и предупреждений.
Как вы только что видели, фиксация областей в Excel - одна из самых простых задач. Однако, как это часто бывает с Microsoft, внутри скрывается гораздо больше.
Предостережение: предотвращение полного исчезновения строк и столбцов при закреплении областей рабочей книги.
Когда вы блокируете несколько строк или столбцов в электронной таблице, вы можете непреднамеренно скрыть некоторые из них, и в результате потом вы уже не увидите их. Чтобы избежать этого, убедитесь, что всё, что вы хотите зафиксировать, находится в поле зрения в момент осуществления операции.
Например, вы хотите заморозить первые две строчки, но первая в настоящее время не видна, как показано на скриншоте ниже. В результате первая из них не будет отображаться позже, и вы не сможете переместиться на нее. Тем не менее, вы все равно сможете установить курсор в ячейку в скрытой замороженной позиции с помощью клавиш управления курсором (которые со стрелками) и при необходимости даже изменить её.
Excel может зафиксировать области несколько иначе, чем вы ожидали.
Вы мне не верите? Тогда попробуйте выбрать ячейку A3, когда первые две не видны (просто находятся чуть выше видимой части таблицы), и щелкните . Что вы ожидаете? Что строки 1 - 2 будут заморожены? Нет! Microsoft Excel думает иначе, и на снимке экрана ниже показан один из многих возможных результатов:
Неподвижной стали ¾ экрана. Вряд ли с такой логикой программы можно согласиться.
Поэтому, пожалуйста, помните, что данные, которые вы собираетесь зафиксировать, всегда должны быть полностью видны.
Совет: Как замаскировать линию, отделяющую закрепленную область.
Если вам не нравится тёмная линия, которую Microsoft Excel рисует внизу и справа от зафиксированной области, вы можете попытаться замаскировать ее с помощью шаблонов оформления и небольшого творческого подхода:)
Выберите приятный для вас вариант оформления нижней границы ячеек, и линия не будет раздражать вас.
Вот как вы можете заблокировать строку в Excel, зафиксировать столбец или одновременно сделать и то, и другое.
Читайте также: