Evolcom.ru

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

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

Как подсчитать количество строк в массиве ТОЛЬКО ЕСЛИ сумма чисел для каждой строки> 0 в Excel?

В Excel у меня есть массив из 150 столбцов x 360 строк со случайными числами (например, от A2 до ET361).

В каждом столбце (т.е. от ячейки B1 до ET1), сколько строк выше нуля находится в столбцах перед этим столбцом?

B1 необходимо рассчитать количество ячеек (от A2 до A361), которые> 0.
C1 должен вычислить количество строк (A2:B2, A3:B3, . A361:B361), где сумма каждой строки> 0.
D1 должен вычислить количество строк (A2:C3, . A361:C361), где сумма каждой строки> 0.

Я попытался использовать формулу COUNTIF, но она возвращает только количество ячеек, а не количество строк.
Я думаю, что мне нужны вложенные формулы ROWS() и IF()? Я также не хочу создавать еще одну матрицу 150 x 360 для решения этой проблемы, поскольку хочу сэкономить место в своем файле Excel.

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

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

Мне нужно вычислить для каждой ячейки матрицы количество строк над ней, для которых сумма столбцов в каждой строке больше нуля. Решение Барри не будет работать в этом случае (я проверил), потому что формула «промежуточный итог» не работает для ячеек, в которых есть формула «промежуточный итог».

Есть ли у нас другие варианты?

3 ответа 3

Хотя я не смог найти решение с одной формулой (возможно, другие смогут! ), я нашел то, что занимает гораздо меньше места в электронной таблице, чем другая матрица 150 x 360.

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

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

Отправной точкой является расчетный столбец для строк в столбце данных.

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

Вспомогательная колонна

В правой части данных я создал дополнительный столбец L.

Ячейка L1 содержит КОЛИЧЕСТВО строк в этом столбце, сумма которых больше нуля.

Для сумм строк вместо простой суммы столбцов в каждой строке (опять же, только для столбца A) я использую сумму интервалов, возвращаемую функцией OFFSET. Эта функция имеет вид

В ячейке L3 первое из выражений — SUM(OFFSET(. )) . Оно вычисляет сумму строк для диапазона, который находится на 0 строк вниз от ячейки A2 и на 0 столбцов вправо, с высотой в 1 строку и шириной, равной значению в ячейке L2. В данном случае L2 имеет значение 1.

Формула копируется на 360 строк, вычисляя сумму высоты строки, определенной в диапазоне 1, и ширины, определенной значением ячейки L2.

Поэтому, если значение в L2 было изменено на 2, формулы в столбце вычислят суммы значений в столбцах A и B для каждой из 360 строк. В ячейке L1 в диапазоне A2:B361 будет отображаться количество строк с суммой больше нуля.

Вычисление количества строк для одного столбца данных

Датабаза данных

Функциональность таблицы данных Excel позволяет быстро определить влияние на расчет изменения значения одного (или двух) из входных данных для этого расчета. Настройка осуществляется путем нажатия кнопки «What-If Analysis» на вкладке «Data Tools Data» на ленте.

На прилагаемом рисунке показано, как устроена таблица данных.

Читайте так же:
Как перенести повторяющиеся строки в столбцы в Excel?

Создается таблица с данными в диапазоне R1:S10. В верхней части таблицы, в ячейке S1, находится ячейка результата, для которой будут изменены входные данные. В этом случае ячейка результата содержит формулу =L1 , которая является просто ссылкой на формулу COUNTIF в верхней части вспомогательного столбца L.

Ячейки R2 — R10 уже имеют значения «что-если». Показанные значения — 1, 2, . 9-е поле представляет собой ширину диапазонов, которые будут возвращены с помощью OFFSET. Аналогично, «ячейка ввода столбца» — это ячейка L1, ячейка в верхней части вспомогательного столбца, которая определяет, сколько строк суммируется.

Мы вводим в виджете ширину 1-9 (эквивалент столбцов «A», «A:B», «A:C» и др. ), и таблица данных подсчитывает количество строк, сумма которых превышает 0 для каждого из этих промежутков между столбцами.

Настройка таблицы данных

На последнем изображении показан конечный результат. Таблица данных подсчитывает количество строк для каждого столбца во входных данных, то есть количество сумм строк (бывших столбцов) больше 0. Эти подсчеты возвращаются в ячейки S2:S10 таблицы данных. Я переместил отсчеты в первую строку входных данных с помощью функции TRANSPOSE .

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

Kak poschitat povtory 1 Как подсчитывать повторения в Excel? Добрый день, уважаемый читатель!

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

Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Kak poschitat povtory 2 Как подсчитывать повторения в Excel? Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:

  1. С помощью функции CUTESLY;
  2. С помощью функции CUTESLIMN;
  3. С помощью функции DLSTR;
  4. С помощью функций VBA.
Читайте так же:
Как перечислить все комментарии к новому листу или книге в Excel?

Используя функцию СЧЁТЕСЛИ

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

Kak poschitat povtory 3 1 Как подсчитывать повторения в Excel?

=CALCULATION($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11 указывается диапазон ячеек, в которых необходимо подсчитать повторяющиеся данные. Важно! Не допускается указывать произвольный диапазон данных. Он особенный, потому что это может быть просто диапазон ячеек или ссылка на конкретную ячейку.

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

Больше информации! Помимо прямого извлечения данных, функция SCRUTESLY может работать с подстановочными знаками. Существует два типа символов, «?» и «*», которые можно использовать только при работе с текстовыми символами. Символ «*» позволяет подставлять любое количество значений, а символ «? » символ заменяет только один символ.

При работе с числовыми значениями необходимо использовать операторы сравнения: «>», «<", "<>» и «=». Для подсчета числовых значений больше нуля пишите «>0», а для подсчета непустых ячеек — «<>«.

Используем функцию СЧЁТЕСЛИМН

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

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

Kak poschitat povtory 4 Как подсчитывать повторения в Excel?

Обратите внимание, что и для B$2, и для C$2 существует такое же написание, как и для предыдущей функции CHOTESLI, единственное отличие — число. Функция может работать со 127 диапазонами, но в нашем примере их всего два.

Читайте так же:
Как подсчитать общее количество кликов в указанной ячейке в Excel?

Работаем с функцией ДЛСТР

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

Kak poschitat povtory 5 Как подсчитывать повторения в Excel?

=(TLSTR($B$2) -LSTR(SUBSTITUTE($B$2;B5;»»))))/LSTR(B5) Используя функцию TLSTR, мы подсчитаем, сколько символов находится в ячейках «$B$2» и «B5», и результатом будет «71». Используя функцию DELETE, мы заменяем текущее значение на «empty», и в результате получаем «47». Затем мы вычитаем наш остаток «71-47 = 24» из общего количества символов и делим на количество символов в единственном значении «24/6 = 4», в результате получаем, сколько раз в строке встречается искомый результат ….. Ответ: 4. (Это результат, если рассматривается только первая строка поиска).

С помощью функций VBA

Последний вариант — использовать функцию VBA для подсчета количества повторений. Вы можете использовать функцию, которую я нашел ранее, но я не писал ее сам.

Kak poschitat povtory 6 Как подсчитывать повторения в Excel?

Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:

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