Как в Эксель посчитать количество ячеек, значений, чисел
Поделиться, добавить в закладки или распечатать статью
Привет друзья. Сегодня учимся подсчитывать ячейки в Excel. Эти функции решают широкий спектр задач для представителей многих профессий. Выполняя промежуточные расчеты, они становятся базовыми для автоматизации ваших расчетов. Я знаю многих менеджеров, которые используют функции подсчета для работы со своей внушительной номенклатурой товаров.
Если количество значений просто нужно знать, не используя в расчетах – удобно смотреть данные в строке состояния:
Вы можете выбрать показатели, выводимые в строке, кликнув по ней правой кнопкой мыши.
Если нужно использовать количество значений в дальнейших расчетах – используйте функции, описанные ниже. Для удобства записи, будем считать, что массиву данных, для которых ведется счёт, присвоено имя «массив». В своих формулах вы можете использовать нужный диапазон данных вместо имени.
Как посчитать количество ячеек в Эксель
Для подсчета количества ячеек в Excel предусмотрено две функции:
- ЧСТРОК(массив) – считает количество строк в выбранном диапазоне, независимо от того, чем заполнены его ячейки. Формула даёт результат только для прямоугольного массива из смежных ячеек, иначе возвращает ошибку;
- ЧИСЛСТОЛБ(массив) – аналогична предыдущей, но считает количество столбцов массива
В Эксель нет функции, чтобы определить количество ячеек в массиве, но это можно легко посчитать, умножив количество строк на количество столбцов: =ЧСТРОК(массив)*ЧИСЛСТОЛБ(массив).
Как посчитать пустые ячейки в Excel
Иногда нужно посчитать количество пустых ячеек в массиве. Для этого можно воспользоваться функцией СЧИТАТЬПУСТОТЫ(массив). Функция работает только с непрерывными прямоугольными массивами.
Функция считает ячейку пустой, если в ней ничего не записано, или формула внутри нее возвращает пустую строку.
Как в Эксель посчитать количество значений и чисел
Чтобы посчитать количество чисел в массиве, используйте функцию СЧЁТ(значение1;значение2;…). Вы можете задать список значений через точку с запятой, или целый массив сразу:
Если нужно определить количество ячеек, содержащих значения, воспользуемся функцией СЧЁТЗ(значение1;значение2;…). В отличие от предыдущей функции, она посчитает не только числа, а и любые комбинации символов. Если ячейка непустая – она будет посчитана. Если в ячейке формула, которая возвращает ноль или пустую строку – функция ее тоже включит в свой результат.
Если нужно посчитать ячейки, которые удовлетворяют какому-то условию, используйте функцию СЧЁТЕСЛИ(массив;критерий). Здесь 2 обязательных аргумента:
- Массив – диапазон ячеек, среди которых производится подсчет. Можно задавать только прямоугольный диапазон смежных ячеек;
- Критерий – условие, по которому происходит отбор. Текстовые условия и числовые со знаками сравнения запишите в кавычках. Равенство числу записываем без кавычек. Например:
- «>0» – считаем ячейки с числами больше нуля
- «Excel» – считаем ячейки, в которых записано слово «Excel»
- 12 – счет ячеек с числом 12
Если нужно учесть несколько условий, используйте функцию СЧЁТЕСЛИМН(массив1;критерий1;;…). Функция может содержать до 127 пар «массив-критерий».
Если вы в используете разные массивы в одной такой функции – все они должны содержать одинаковое количество строк и столбцов.
Как определить наиболее часто встречающееся число
Чтобы найти число, которое чаще всего встречается в массиве, есть в Эксель функция МОДА(число1;число2;…). Результатом её выполнение будет то самое число, которое встречается чаще всего. Чтобы определить их количество — можно воспользоваться комбинацией формул суммирования и формул массива.
Если таких чисел несколько – будет выведено то, которое раньше других встречается в списке. Функция работает только с числовыми данными.
Вот, пожалуй и весь список функций, который я хочу вам презентовать в рамках этого поста. Их вполне достаточно, чтобы решить самые популярные задачи счёта ячеек. Комбинируйте их с другими функциями (например, текстовыми, даты и времени), чтобы получить максимальный результат.
В следующей статье мы изучим функции суммирования. Заходите почитать, даже если уверены, что всё об этом знаете. Думаю, там буде кое-что именно для вас!
Поделиться, добавить в закладки или распечатать статью
Средняя арифметическая как оценка математического ожидания
Теория вероятностей занимается изучением случайных величин. Для этого строятся различные характеристики, описывающие их поведение. Одной из основных характеристик случайной величины является математическое ожидание, являющееся своего рода центром, вокруг которого группируются остальные значения.
Формула матожидания имеет следующий вид:
где M(X) – математическое ожидание
xi – это случайные величины
pi – их вероятности.
То есть, математическое ожидание случайной величины — это взвешенная сумма значений случайной величины, где веса равны соответствующим вероятностям.
Математическое ожидание суммы выпавших очков при бросании двух игральных костей равно 7. Это легко подсчитать, зная вероятности. А как рассчитать матожидание, если вероятности не известны? Есть только результат наблюдений. В дело вступает статистика, которая позволяет получить приблизительное значение матожидания по фактическим данным наблюдений.
Математическая статистика предоставляет несколько вариантов оценки математического ожидания. Основное среди них – среднее арифметическое.
Среднее арифметическое значение рассчитывается по формуле, которая известна любому школьнику.
где xi – значения переменной, n – количество значений.
Среднее арифметическое – это соотношение суммы значений некоторого показателя с количеством таких значений (наблюдений).
Функция МИН
В статистических подсчетах нередко нужно не только определить среднее значение, среднеквадратическое отклонение и вычислить другие показатели
Также важно значение наименьшего и наибольшего числа, в том числе, для получения указанных показателей. Практическое применение этой функции довольно обширное:
- На рынке акций для определения времени, когда цела была наиболее низкой.
- Для определения слабых мест в годовом бюджете (например, в каком месяце доходы компании были минимальными) с целью их дальнейшего исправления. Например, можно определить наименее доходный месяц и проанализировать факторы, которые этому способствовали.
Существует огромное количество других ситуаций, когда можно использовать функцию МИН. В самом общем виде она выглядит следующим образом: =МИН(число1;число2;…). Принцип заполнения аргументов этой функции аналогичен функции МАКС.
Функция МАКС
Как становится понятно из названия, эта функция ищет максимальное значение в определенной числовой выборке. Ситуации, в которых она может использоваться, в принципе, те же за тем лишь исключением, что все в противоположную сторону. Например, компания может с помощью функции МАКС определить самый доходный месяц и понять, каковы причины этого успеха.
Функции СРЗНАЧ и СРЗНАЧА
Стандартная функция СРЗНАЧ определяет среднее арифметическое в числовой выборке. Общий вид формулы такой же, как и для любой другой выборки значений. Сначала пишется название функции, после чего в скобках приводятся числа и диапазоны, которые необходимо обработать с помощью этой функции. То есть, общий вид формулы следующий: =СРЗНАЧ(число1;число2;…).
Как мы поняли, можно использовать как обычные числа (очень полезно для использования значений, которые не будут меняться в течение ближайшего времени), ссылки на ячейку (они применяются для тех значений, которые в будущем изменятся) и на диапазон (в этом случае будет использоваться целый набор чисел за один раз). Чтобы после ввода одного аргумента начать записывать другой, достаточно нажать на соответствующее поле в мастере функций или просто нажать на клавишу Tab.
Максимальное количество аргументов, которые можно использовать в этой функции – 255. При этом обязательным аргументом является только первое число. В качестве аргументов не могут использоваться текстовые и логические значения. Они просто не учитываются формулой, в которой используется указанный оператор. Основное отличие функции СРЗНАЧА от СРЗНАЧ заключается в том, что текстовые значения и «ЛОЖЬ» считаются нулевыми, а значение «Истина» приравнивается к единице.
Функция РАНГ.СР
С помощью функции РАНГ.СР пользователь может вернуть ранг числа. Если несколько чисел в одном диапазоне относятся к одному рангу, то возвращается среднее. Имеет три аргумента, два из которых – обязательные:
- Число. Это то число, для которого осуществляется определение ранга.
- Ссылка. Это массив чисел, или ссылка на этот массив.
- Порядок. Это число, которое влияет на способ, в который значения будут упорядочиваться.
Таким образом, статистические функции Excel – это превосходный инструмент для обработки больших массивов информации.
Символ & (амперсанд) в Excel
Амперсанд в Excel при использовании в формулах является оператором конкатенации и нужен как более лаконичная замена функции СЦЕПИТЬ. Ниже две формулы, делающие абсолютно одно и то же действие – объединяющие ячейку A1, пробел и ячейку B1:
Очевидно, вторая формула короче, к тому же не использует вложенность (не имеет скобок), что иногда бывает полезно при составлении сложных формул.
Еще одно свойство амперсанда – т.к. процедура сцепки является текстовой по самой её сути, конкатенация амперсандом не исключение, и даже при сцепке двух числовых значений формат результирующего значения ячейки будет текстовым.
Математические функции
ABS
Чтобы определить модуль числа, используйте функцию ABS.
СЛЧИС и СЛУЧМЕЖДУ
И функция СЛЧИС (RAND), и функция СЛУЧМЕЖДУ (RANDBEWTEEN) могут генерировать случайные числа на лету. СЛЧИС (RAND) создает длинные десятичные числа от нуля до 1. СЛУЧМЕЖДУ (RANDBETWEEN) генерирует случайные целые числа между двумя заданными числами.
ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ и ЦЕЛОЕ
Чтобы округлить значения в большую или меньшую сторону, используйте функцию ОКРУГЛ (ROUND). Для принудительного округления до заданного количества цифр в большую сторону используйте ОКРУГЛВВЕРХ (ROUNDUP). Для принудительного округления в меньшую сторону используйте ОКРУГЛВНИЗ (ROUNDDOWN). Чтобы полностью удалить десятичную часть числа, используйте функцию ЦЕЛОЕ (INT).
ОКРУГЛТ, ОКРВВЕРХ и ОКРВНИЗ
Чтобы округлить значения до ближайшего кратного, используйте функцию ОКРУГЛТ (MROUND). Функция ОКРВНИЗ (FLOOR) и функция ОКРВВЕРХ (CEILING) также округляются до заданного кратного. ОКРВНИЗ (FLOOR) заставляет округлять вниз, а ОКРВВЕРХ (CEILING) заставляет округлять вверх.
ОСТАТ
Функция ОСТАТ (MOD) возвращает остаток после деления. Это звучит скучно, но ОСТАТ (MOD) появляется во всех видах формул, особенно в формулах, которые должны делать что-то «каждый раз». В приведенном ниже примере вы увидите, как ОСТАТ (MOD) возвращает ноль каждое третье число, когда делитель равен 3:
СУММПРОИЗВ
Функция СУММПРОИЗВ (SUMPRODUCT) — это мощный и универсальный инструмент для работы со всеми видами данных. Вы можете использовать СУММПРОИЗВ (SUMPRODUCT) для простого подсчета и суммирования на основе критериев, и вы можете использовать его изящными способами, которые просто не работают с СЧЁТЕСЛИМН (COUNTIFS) и СУММЕСЛИМН (SUMIFS). В приведенном ниже примере мы используем СУММПРОИЗВ (SUMPRODUCT) для подсчета и суммирования заказов в марте.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является «агрегатной функцией», которая может выполнять ряд операций с набором данных. В общем, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) может выполнять 11 операций, включая СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЁТ (COUNT), МАКС (MAX), МИН (MIN) и т.д.
Ключевой особенностью ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является то, что он будет игнорировать строки, которые были «отфильтрованы» из таблицы Excel и строки, которые были скрыты вручную. В приведенном ниже примере ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) используется для подсчета и суммирования только 7 видимых строк в таблице:
АГРЕГАТ
Как и ПРОМЕЖУТОЧНЫе.ИТОГИ (SUBTOTAL), функция АГРЕГАТ (AGGREGATE) может также выполнять ряд агрегатных операций над набором данных и, при необходимости, игнорировать скрытые строки. Ключевые различия заключаются в том, что АГРЕГАТ (AGGREGATE) может выполнять больше операций (всего 19), а также может игнорировать ошибки.
В приведенном ниже примере АГРЕГАТ (AGGREGATE) используется для выполнения операций МИН (MIN), МАКС (MAX), НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL), игнорируя ошибки. Обычно ошибка в ячейке B9 не позволяет этим функциям возвращать результат.
РОСТ
Синтаксис:
РОСТ(известные_значения_у, известные_значения_х, новые_значения_х, конст)
Результат: Аппроксимирует экспериментальной кривой известные_значения_у и извест-ные_значения_х и возвращает значения этой кривой, соответствующие значениям х, которые определяются аргументом новые_значения_х.
Аргументы:
- известные_значения_у — множество значений у, которые уже изиестны для соотношения у — b*m/\х (если массив известные_значения_у имеет один столбец, то каждый столбец массива известные_значения_х интерпретируется как отдельная переменная; если массив известные^ значения_у имеет одну строку, то каждая строка массива известные_значения_х интерпретируется как отдельная переменная; если какие-либо числа в массиве известные_значения_у равны 0 или отрицательны, то функция РОСТ возвращает значение ошибки #ЧИСЛО!);
- известные_значения_х — необязательное множество значений х, которые уже известны для соотношения у = b *m/\х (массив известные_значения_х может содержать одно или несколько множеств переменных; если используется только одна переменная, то извест-ные_значения_у и известные_значения_х могут иметь любую форму при условии, что они имеют одинаковую размерность; если используется более одной переменной, то известные:_значения_у должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец); если аргумент известные_значения_х опущен, то предполагается, что это массив {1;2;3;…} такого же размера, как и известные_значения_у);
- новые_значения_х — новые значения х, для которых функция РОСТ возвращает соответствующие значения у (аргумент новые_значения_х должен содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_х\ таким образом, если аргумент известные_значения_у — это один столбец, то аргументы известные_значения_х и но-вые_значения_х должны иметь такое же количество столбцов; если аргумент известные_значения_у — это одна строка, то аргументы известные_зна-чения_х и новые__значения_х должны иметь такое же количество строк; если аргумент новые_значения_х опущен, то предполагается, что он совпадает с аргументом известные_значения_х если оба аргумента известные_значения_х и новые_ значе-ния_х опущены, то предполагается, что это массив {1;2;3;…} такого же размера, как и извест-ныезначения_у);
- конст — логическое значение; если аргумент конст отсутствует или имеет значение ИСТИНА, то b вычисляется традиционно; если аргумент конст имеет значение ЛОЖЬ, то Ъ полагается равным 1 и значения т подбираются так, чтобы выполнялось соотношение у=m/\х.
Вычисление средних значений
Функция СРЗНАЧ
Возвращает среднее значение (среднее арифметическое) аргументов. СРЗНАЧ(число1, . )
Число1. Неотклонимый аргумент. 1-ое число, ссылка на ячейку либо спектр, для которого требуется вычислить среднее значение.
Число2, . Необязательный аргумент. Доп числа, ссылки на ячейки либо спектры, для которых требуется вычислить среднее значение. Аргументов быть может не наиболее 255.
Аргументы могут быть числами, именами либо ссылками на спектры либо ячейки, содержащие числа. Учитываются логические значения и текстовые представления чисел, которые конкретно введены в перечень аргументов. Если аргумент является ссылкой на спектр либо ячейку, содержащую текст либо логические значения, либо ссылкой на пустую ячейку, то такие значения игнорируются; но ячейки, которые содержат нулевые значения, учитываются. Аргументы, являющиеся значениями ошибок либо текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
Если логические значения и текстовые представления чисел нужно учесть в расчетах, используйте функцию СРЗНАЧА.
Функция СРЗНАЧ вычисляет среднее значение, другими словами центр набора чисел в статистическом распределении. Существует три более всераспространенных метода определения среднего значения, обрисованных ниже.
Среднее значение — это среднее арифметическое, которое рассчитывается методом сложения набора чисел с следующим делением приобретенной суммы на их количество. К примеру, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
Медиана — это число, которое является серединой огромного количества чисел, другими словами половина чисел имеют значения огромные, чем медиана, а половина чисел имеют значения наименьшие, чем медиана. К примеру, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
Мода — это число, более нередко встречающееся в данном наборе чисел. К примеру, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении огромного количества чисел эти величины оценки степени централизации равны. При ассиметричном распределении огромного количества чисел они могут различаться.
Функция СРЗНАЧЕСЛИ
Если требуется вычислить среднее значение лишь для тех значений, которые удовлетворяют определенным аспектам, употребляют функцию СРЗНАЧЕСЛИ.
Возвращает среднее значение (среднее арифметическое) всех ячеек в спектре, которые соответствуют данному условию.
СРЗНАЧЕСЛИ(спектр, условие, «диапазон_усреднения»)
Спектр — одна либо несколько ячеек для вычисления среднего, включающих числа либо имена, массивы, либо ссылки, содержащие числа.
Условие — условие в форме числа, выражения, ссылки на ячейку либо текста, которое описывает ячейки, участвующие в вычислении среднего. К примеру, условие быть может выражено последующим образом: 32, «32», «>32», «яблоки» либо B4.
Диапазон_усреднения — фактическое огромное количество ячеек для вычисления среднего. Если он не указан, употребляется спектр.
Ячейки в спектре, которые содержат значения ИСТИНА либо ЛОЖЬ, игнорируются. Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее. Если спектр является пустым либо текстовым значением, то функция СРЗНАЧЕСЛИ возвращает значение ошибки #ДЕЛ/0!. Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0. Если ни одна ячейка в спектре не соответствует условию, функция СРЗНАЧЕСЛИ возвращает ошибку «#ДЕЛ/0!».
В этом аргументе можно применять подстановочные знаки: вопросительный символ (?) и звездочку (*). Вопросительный символ соответствует хоть какому одиночному символу; звездочка — хоть какой последовательности знаков. Если необходимо отыскать сам вопросительный символ либо звездочку, то перед ними следует поставить символ тильды (
Значение «диапазон_усреднения» не непременно обязано совпадать по размеру и форме с спектром. При определении фактических ячеек, для которых рассчитывается среднее, в качестве исходной употребляется верхняя левая ячейка в «диапазон_усреднения», а потом добавляются ячейки с совпадающим размером и формой. К примеру:
СРЗНАЧ() и СРЗНАЧА()
Редко кто задумывался, а ведь вычисление среднего значения – сугубо статистическая процедура: именно поэтому это операция и помещена в статистический пакет.
Наверно, особо не стоит останавливаться на правилах использования формулы: функция СРЗНАЧ() принимает на вход массив аргументов и дает на выходе среднее значение по всем ячейкам, содержащим числа(!). Это очень важный момент, который далеко не все знают. Поясним на примере.
Пусть дан диапазон А1:С2 и мы ищем среднее значение по всем 6 ячейкам диапазона:
Применение функции СРЗНАЧ()
Однако, результат функции СРЗНАЧ(А1:С2) будет не 8,7, а 13. Почему? (4+15+11+22)/6 = 8,7 ведь?
Да, это правильно, но функция СРЗНАЧ() берет в расчет только те ячейки, где «встречает» числа. Текстовая информация и пустые ячейки просто игнорируются. Поэтому в данном примере СРЗНАЧ() усредняет по 4 ячейкам и выдает правильный ответ – 13.
А вот если нужно произвести усреднение по всему диапазону, вне зависимости от типа данных, нужно использовать функцию СРЗНАЧА().
Принцип работы такой же, как и у СРЗНАЧ(), только на вход будут поступать абсолютно все ячейки. Результат в нашем примере будет уже ожидаемый – 8,7.
Применение функции СРЗНАЧА()
Замечание
Выбор той или иной функции происходит в зависимости от задачи. В реальной жизни они могут понадобится в одинаковой мере.
Например, менеджеру нужно узнать среднедневную выручку за месяц на основании продаж за каждый день. Допустим, за несколько дней ячейки оставлены пустыми. Есть два варианта, почему так произошло:
1. В эти дни не было ни одной продажи. Тогда эти дни должны принимать участие в расчете среднего значения и менеджеру нужно использовать СРЗНАЧА() – так он исключит игнорирование пустых ячеек.
2. Эти дни были выходными. Тогда пропуски сами по себе никакой информации не несут и их надо игнорировать: фактически, эти дни не принимают участие в статистической выборке и функция СРЗНАЧ() поможет их пропустить.
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
ЧАСТОТА
Синтаксис:
ЧАСТОТА(массив_данных, массив_карманов)
Результат: Распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов («карман» соответствует понятию интервала в математике) частотное распределение показывает, сколько исходных значений попадает в каждый интервал.
Аргументы:
- массив_данных — массив или ссылка на множество данных, для которых вычисляются частоты; если аргумент массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей;
- массив_карманов — массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_дан-ных\ если аргумент массив_карманов не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе массив_данных.
ПРИМЕЧАНИЕ
Функция ЧАСТОТА не учитывает ни текст, ни пустые ячейки.