Содержание

Пять способов, как в «Экселе» посчитать количество заполненных ячеек

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

Метод 1: отображение количества значений в строке состояния

Смотрите также: “Как объединить ячейки в Экселе без потери данных”

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

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

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

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

Порой бывает, что по умолчанию показатель “Количество” не включен в строку состояния, однако это легко поправимо:

Щелкаем правой клавишей мыши по строке состояния.
В открывшемся перечне обращаем вниманием на строку “Количество”. Если рядом с ней нет галочки, значит она не включена в строку состояния. Щелкаем по строке, чтобы добавить ее.

Как осуществлять расчеты в программе Excel

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

В этой небольшой заметке речь пойдет об одном из аспектов использования таблиц Excel – для создания форм математических расчетов.

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

Три человека (Иван, Петр и Василий) ведут общую предпринимательскую деятельность, заключающуюся в оптовом приобретении овощей в одних регионах страны, их транспортировке в регионы с повышенным спросом и реализации товара по более высокой цене. Иван занимается закупкой, Василий — реализацией и оба они получают по 35 % от чистой прибыли.

Петр – транспортирует товар, его доля – 30 %. По этой схеме партнеры работают постоянно и ежемесячно (или даже чаще) им приходится подсчитывать, сколько денег кому положено.

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

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

Перед началом работы в Excel
хочу обратить внимание на то, что
каждая ячейка в таблице Excel имеет свои координаты
, состоящие из буквы вертикального столбца и номера горизонтальной строки.
Работа в Excel. Создание расчетной таблицы
:
Открываем таблицу и подписываем ячейки, в которые будем вводить исходные данные (закупочная стоимость товара, его количество, цена реализации товара, расстояние транспортировки, стоимость топлива).
Затем подписываем еще несколько промежуточных полей, которые будут использоваться таблицей для вывода промежуточных результатов расчета (потрачено на закупку товара, выручено от реализации товара, стоимость транспортировки, прибыль).

Метод 2: применение функции СЧЕТЗ

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

Функция СЧЕТ3 выполняет задачу по подсчету всех заполненных ячеек в заданном диапазоне (пустые не учитываются). Формула функции может выглядет по-разному:

В первом случае функция выполнит подсчет всех перечисленных ячеек. Во втором – определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N. Обратите внимание, что количество аргументов функции ограничено на отметке 255.

Давайте попробуем применить функцию СЧЕТ3 на примере:

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

Также можно кликнуть по значку «Вставить функцию» рядом со строкой формул.

Функция СЧЕТ подойдет, если вы работаете исключительно с числами. Ячейки, заполненные текстовыми значениями, этой функцией учитываться не будут. В остальном СЧЕТ почти идентичен СЧЕТЗ из ранее рассмотренного метода.

Алгоритм действий также похож на тот, что мы рассмотрели выше:

Выбираем ячейку, где будет сохранен и отображен результат подсчета значений.
Заходим в Мастер функций любым удобным способом, выбираем в категории “Статистические” необходимую строку СЧЕТ и щелкаем OK.

Что такое процент?

Проце́нт (лат. per cent — на сотню) — одна сотая часть. О. Обозначает долю чего-либо по отношению к целому. Например, 25 % от 500 руб. означает 25 частей по 5 руб. каждая, то есть 125 руб.

Базовая формула расчета процента от суммы выглядит так:

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

Метод 4: оператор СЧЕТЕСЛИ

Наконец, мы подошли к функции, которая способна сделать подсчет по определенным условиям. Оператор СЧЕТЕСЛИ создан именно для этой задачи. Все ячейки без данных и те, которые не отвечают заданным пользователем параметрам, данная функция игнорирует.

Синтаксис СЧЕТЕСЛИ типичен для всех операторов, работающих с условиями:

Диапазон – область ячеек, внутри которой необходимо найти количество совпадений по заданному условию.

Критерий – конкретное условие, совпадение по которому ищет функция. Условие указывается в кавычках, может быть задано как в виде точного совпадения с введенным числом или текстом, или же как математическое сравнение, заданное знаками «не равно» («»), «больше» («>») и «меньше» («/=<�»).

Разберем наглядно применение функции СЧЕТЕСЛИ:

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

Для лучшего понимания работы с функцией СЧЕТЕСЛИ попробуем изменить условие:

Давайте теперь определим сколько раз в этом же столбце встречаются любые другие значения, кроме слова «бег».
Выбираем ячейку, заходим в Мастер функций, находим оператор СЧЕТЕСЛИ, жмем ОК.
В поле «Диапазон» вводим координаты того же столбца, что и в примере выше. В поле «Критерий» добавляем знак не равно («») перед словом «бег».

Выполняем уже привычные шаги по вставке функции СЧЕТЕСЛИ в нужную результирующую ячейку.
В поле диапазон указываем нужный интервал ячеек столбца. Задаем условие “>350” в поле “Критерий” и жмем OK.

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

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

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

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

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

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

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

Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.

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

Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:

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

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

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

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

Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:

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

Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.

Чуть более подробно остановимся на логике формирования формулы.

Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки).

Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).

При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.

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

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

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

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

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

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

Метод 5: использование оператора СЧЕТЕСЛИМН

Смотрите также: “Как объединить строки в Экселе”

СЧЕТЕСЛИМН – функция, полностью аналогичная СЧЕТЕСЛИ, но позволяет ввести несколько диапазонов с одинаковыми или разными условиями.

Например, нам нужно посчитать количество товаров, которые проданы более 300 шт, а также, товары, чья стоимость более 6000 руб.

Разберем, как это сделать при помощи функцией ЧТОЕСЛИМН:

С определенным текстом или значением

Функция СЧЁТЕСЛИ – позволяет рассчитать количество блоков, которые соответствуют заданному критерию. В качестве аргумента прописывается диапазон – В2:В13, и через «;» указывается критерий – «>5».

Например, есть таблица, в которой указано, сколько килограмм определенного товара было продано за день. Посчитаем, сколько товаров было продано весом больше 5 килограмм. Для этого нужно посчитать сколько блоков в столбце Вес, где значение больше пяти. Функция будет выглядеть следующим образом: =СЧЁТЕСЛИ(В2:В13;»>5″). Она рассчитает количество блоков, содержимое в которых больше пяти.

Метод 6: функция СЧИТАТЬПУСТОТЫ

В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.

Порядок действий практически ничем не отличается от вышеперечисленных:

Где введены числа

Функция СЧЁТ – подсчитывает блоки, заполненные только числовыми значениями. Выделяем Н1, ставим «=», пишем функцию «СЧЁТ». В качестве аргумента функции укажите нужный диапазон (F1:G10). Если диапазонов несколько, разделите их «;» – (F1:G10;B3:C8).

Всего заполнено 20 блоков. Тот, в котором записан текст, был не учтен, но посчитаны те, которые заполнены датой и временем.

[expert_bq id=»1570″]Я пытаюсь подсчитать следующее в excel У меня есть несколько уникальных значений для X скажем, 4 , каждое из которых появляется дважды в случайном порядке в наборе данных. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Всем добрый день, сегодня я открываю рубрику «Функции» и начну с функции СЧЁТЕСЛИ. Честно говоря, не очень-то и хотел, ведь про функции можно почитать просто в справке Excel. Но потом вспомнил свои начинания в Excel и понял, что надо. Почему? На это есть несколько причин:

Excel посчитать количество одинаковых значений в столбце

Создание списка уникальных значений в Excel с помощью формул массивов | Exceltip

Перед началом работы в Excel
хочу обратить внимание на то, что
каждая ячейка в таблице Excel имеет свои координаты
, состоящие из буквы вертикального столбца и номера горизонтальной строки.
Работа в Excel. Создание расчетной таблицы
:
Открываем таблицу и подписываем ячейки, в которые будем вводить исходные данные (закупочная стоимость товара, его количество, цена реализации товара, расстояние транспортировки, стоимость топлива).
Затем подписываем еще несколько промежуточных полей, которые будут использоваться таблицей для вывода промежуточных результатов расчета (потрачено на закупку товара, выручено от реализации товара, стоимость транспортировки, прибыль).

Например, если в ячейке Текст находится текст Альфа, данная формула возвращает значение 3, поскольку в диапазоне Диапазон2 текст Альфа присутствует в трех ячейках: А2, А8 и А10. Обратите внимание, что регистр в данном случае не учитывается.

Подсчет уникальных значений в Excel

Мне нужно подсчитать уникальные значения в диапазоне (C2:C2080) в excel. Погуглил формулу:

12 ответов

У меня есть лист excel со значениями в них . Я хотел бы знать, есть ли у них способ, если бы я мог просто дать мне подсчет всех уникальных значений.

Подсчет различных значений в excel-частотной функции да, я прочитал Подсчет различных значений в excel-частотной функции Я пытаюсь сосчитать столбец с разными числами столбец содержит (поиск) 1 3 7 9 5 1 3 9 4 результат ищем; C1 C2 1 = 2 2 = 0 3 = 2 4 = 1 etc

Вы можете использовать его в качестве функции рабочего листа , ссылаясь на любой диапазон, например “=CountUnique(N8:O9)”

Он обрабатывает текстовые и числовые значения и обрабатывает пустые ячейки как одно значение

Для этого требуется ссылка на библиотеку сценариев Microsoft для объекта словаря .

EDIT: вышеизложенное будет обрабатывать пустые записи в столбце

Вы также можете написать макрос VBA (не уверен, что это то, что вам нужно.)

Что-то в этом роде (учитывая электронную таблицу с A1-A11 заполненными и B1-B11 пустыми):

функция JustinG работает очень хорошо (и быстро), пока количество уникальных элементов не превысит 32 767 из-за некоторого ограничения в Excel.

Затем он будет обрабатывать более уникальные предметы.

Для тех, кто все еще пытается использовать метод словаря @JustinG’s, вам нужно будет немного изменить код, если вы используете более новую версию VBA.

Вам нужно будет ссылаться на «Microsoft Scripting Runtime» и префикс Dictionary терминов с Scripting , как показано ниже:

У меня странная проблема. Я хочу подсчитать уникальные записи в определенном столбце. Здесь я приложил свой файл csv. Я делаю это, чтобы получить уникальные записи в столбце: dat

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

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

Массив-ввод:
(Ctrl+Shift+Enter, и не включайте фигурные скобки)

Он работает как с числами, так и с текстом, обрабатывает пустые ячейки, обрабатывает ошибки в ссылочных ячейках и работает в VBA. Это также одно из самых компактных решений, которые я видел. Используя его в VBA, он, по-видимому, автоматически обрабатывает необходимость быть формулой массива.

Обратите внимание, что он обрабатывает ошибки, просто включая их в число uniques. Например, если у вас есть две ячейки, возвращающие #DIV/0!, и три ячейки, возвращающие #VALUE. эти 5 ячеек добавят 2 к окончательному количеству уникальных значений. Если вы хотите, чтобы ошибки были полностью исключены, для этого его нужно будет изменить.

В моих тестах этот вариант из приведенного выше работает только с числами, а не с текстом, и не обрабатывает ошибки в ссылочных ячейках (возвращает ошибку, если какая-либо из ссылочных ячеек возвращает ошибку):

Когда вы введете эту формулу в виде массива, она будет выглядеть примерно так:

Формула работает на меня. Есть несколько вещей, которые могут привести к тому, что это не сработает. Во-первых, все целевые ячейки должны иметь в себе значение. Другой пример того, где это может не сработать, — это если у вас есть одна ячейка со значением 31 и другая ячейка с текстовым значением «31». Он распознает их как разные ценности.

Это формула массива . Вместо того, чтобы нажимать просто enter, чтобы подтвердить это, вы должны нажать ctrl+shift+enter.

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

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

С осени 2018 года эту проблему можно решить гораздо проще с помощью новых функций динамического массива (в настоящее время доступно только для клиентов Office 365 — это решение не работает на Excel 2016 / 2019):

Функция UNIQUE приведет к массиву уникальных значений в диапазоне, которые можно подсчитать с помощью COUNTA .

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

Excel посчитать количество одинаковых значений в столбце — Вэб-шпаргалка для интернет предпринимателей!

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

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции