0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро подсчитать количество скрытых листов в Excel?

Как посчитать сумму с разных листов в Excel?

Рассмотрим возможности суммирования данных из разных ячеек книги с помощью трехмерных ссылок в Excel.

Приветствую всех дорогих читателей блога TutorExcel. Ru блога.

Практически каждый из нас при работе в Excel хотя бы раз наверняка применял операцию сложения с помощью оператора плюс «+» и различных функций (СУММ, СУММЕСЛИ, СУММЕСЛИМН и т.п.).

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

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

Как сложить листы в Excel?

Вариант 1. Ручной ввод.

Давайте начнем с медленной версии, с одной стороны, но с более четкой и очевидной формой, с другой.

Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:

Сложение с помощью оператора плюс "+"

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

Ручной ввод через СУММ

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

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

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

Вариант 2. Полуавтоматический ввод.

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

Читайте так же:
Как быстро совместить имя и фамилию в одной ячейке в Excel?

Полуавтоматический ввод через СУММ

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

Таким образом, запись для добавления 10 или 20 листов будет одинаковой — Название первого листа:Название последнего листа!Диапазон.

Давайте немного подробнее остановимся на логике формирования формулы. Напомним, что оператор двоеточия в Excel используется для объединения диапазона, который представляет собой ссылку на все ячейки между первой и последней ячейкой (включая сами ячейки). Например, диапазон A1:A10 — это двумерная ссылка, в которой находятся все ячейки между первой (A1) и последней (A10).

Листы добавляют еще одно измерение, давая нам трехмерную ссылку. Например, диапазон Sheet1:Sheet3!A1:A10 содержит все данные между Sheet1!A1:A10 и Sheet3!A1:A10.

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

Особенности трехмерных ссылок

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

  • Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
    Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
  • Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
  • Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).
Читайте так же:
Как автоматически добавить / ввести текущую дату / время в ячейку двойным щелчком в Excel?

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

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.

Подсчет числа строк в Microsoft Excel

Подсчет строк в Microsoft Excel

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

Определение количества строк

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

Способ 1: указатель в строке состояния

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

Отображение количества строк в строке состояния в Microsoft Excel

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

Читайте так же:
Как быстро убрать разрывы строк в Excel?

Отображение количества строк в строке состояния с неполными столбцами в Microsoft Excel

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

Включение отображение количества в строке состояния в Microsoft Excel

Способ 2: использование функции

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

Его можно ввести в любую пустую ячейку электронной таблицы, а аргумент «Массив» устанавливается на координаты области, в которой будет вестись подсчет.

Функция ЧСТРОК в Microsoft Excel

Просто нажмите кнопку «Enter», чтобы вывести результат на экран.

Результат функции ЧСТРОК в Microsoft Excel

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

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

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

Переход в Мастер функций в Microsoft Excel

Переход в окно аргументов функции ЧСТРОК в Microsoft Excel

Аргументы функции ЧСТРОК в Microsoft Excel

Результат обработки функции ЧСТРОК в Microsoft Excel

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

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

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

Выделение диапазона в Microsoft Excel

Переход к условному форматированию в Microsoft Excel

Форматирование в Microsoft Excel

Включение фильтра в Microsoft Excel

Включение сортировки по цвету в Microsoft Excel

подсчет строк по условию в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Читайте так же:
Как активировать ячейку для редактирования одним щелчком мыши в Excel?

Помимо этой статьи, на сайте еще 12677 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Отобразить-скрытые-ячейки-Excel

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

Вот скриншот файла, где строки 2:15 вырваны. Это совершенно очевидно.

Скытые-ячейи-Excel

Далее, я попытался отобразить строки нормальными способами, которые знал. Но они не сработали.
Я выделил строки с 1 по 16, щелкнул правой кнопкой мыли, из выпадающего меню выбрал Показать. Строки 2:15 остались для меня не видимыми.

Показать-ячейи-Excel

Затем я перешел на вкладку Главная в группе Ячейки. Выберите опцию Формат -> Скрыть или Отобразить -> Отображать строки. Это мне тоже не помогло. Что-то скрывалось за этими строками.

Отобразить-строки-Excel

  • Рабочий лист не был защищен.
  • Я также попробовал переключиться с ячейки A1 на нижнюю ячейку с помощью клавиатуры, чтобы увидеть изменения в окне Имя. Он изменился с A1 на A16, что означало, что строки 2:15 были скрыты.

Тогда почему я не могу их показывать?

В целях тестирования я скрыл строки 18:19, а затем отобразил весь лист после них:

Странная ситуация, 18 и 19 строки отобразились, строки 2:15 продолжали быть скрытыми. Более странной ситуации я не встречал с момента начала использования Excel. Тем не менее отгадка пришла неожиданно.
Все дело в высоте ячеек!
Когда я попытался изменить высоту ячеек на 15, все скрытые ячейки отобразились. Для этого выделяем строки с 1 по 16, щелкаем правой кнопкой по номерам строк, из выпадающего меню выбираем Высота строки. В появившемся диалоговом окне указываем 15 (это высота, заданная по умолчанию на моем компьютере, у вас она может быть другой).

Читайте так же:
Как в Excel вывести отрицательные числа в скобках?

Высота-строки-Excel

Это интересное наблюдение заставило меня провести несколько тестов с высотой линии, и вот что вышло:

  • Для линий <=0,07 линия считается скрытой, и мы можем отобразить ее с помощью обычных инструментов Excel
  • Для линий от 0,08 до 0,67 линия выглядит скрытой, но это не так. Мы не можем отображать такие линии обычным способом, только изменяя высоту линии.
  • Для линий с высотой >=0.68 мы можем видеть маленькие строки, что означает, что они не скрыты.

Как все это получилось? Только путем экспериментов.

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

Вам также могут быть интересны следующие статьи

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

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector