Cd-m.ru

ПК Журнал СД-М
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать количество строк в excel

Как подсчитать количество строк в excel

Функция ЧСТРОК в Excel предназначена для определения числа строк, содержащихся в константе массива или числа строк в диапазоне ячеек, ссылка на который передана в качестве аргумента этой функции, и возвращает определенное числовое значение.

Как посчитать количество строк листа в Excel

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

Пример 1. Определить число строк, которые можно заполнить на одном листе в табличном редакторе Excel.

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

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

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

Пример 2. На листе Excel находится таблица, первый столбец которой (id) находится в столбце A:A. Определить число пустых строк между началом листа и шапкой таблицы.

Вид части листа с данными:

Для определения искомого значения используем формулу:

С помощью функции ПОИСКПОЗ определяем номер строки, в которой находится значение названия первого столбца таблицы. Функция ИНДЕКС возвращает ссылку на ячейку, в которой было определено это значение. Поскольку эта функция возвращает данные ссылочного типа, в результате вычислений выражение типа A1:ИНДЕКС принимает вид A1:A10. Поскольку нас интересуют только пустые строки, вычитаем 1 из значения, найденного функцией ЧСТРОК.

Правила использования функции ЧСТРОК в Excel

Функция ЧСТРОК имеет следующую синтаксическую запись:

  • массив – обязательный для заполнения, принимает константу массива или ссылку на диапазон ячеек, для которого производится подсчет количества строк.
  1. Если в качестве аргумента функции передано числовое значение, оно будет интерпретировано как константа массива с одним элементом, поэтому функция ЧСТРОК вернет значение 1. Например, результат выполнения =ЧСТРОК(5) будет 1.
  2. Если аргумент функции указан в виде логических или текстовых данных, рассматриваемая функция вернет код ошибки #ЗНАЧ!
  3. При использовании констант массивов для разделения строк используют знак «:». Например, константа массива <1;2:4;6:1:7:0>содержит 3 строки.

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

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

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

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

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

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

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

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

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

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

Для вывода результата на экран достаточно будет нажать кнопку Enter.

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

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

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

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

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

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

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

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

Переходим во вкладку «Главная». На ленте в блоке инструментов «Стили» жмем на кнопку «Условное форматирование». Выбираем пункт «Правила выделения ячеек». Далее открывается пункт различных правил. Для нашего примера мы выбираем пункт «Больше…», хотя для других случаев выбор может быть остановлен и на иной позиции.

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

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

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

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

Читать еще:  Как подключить модем к телевизору

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

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

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

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

В строке состояния выводится число примерно такого вида:

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Функция СЧЁТЕСЛИМН считает количество ячеек по условию в Excel

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

Примеры использования функции СЧЁТЕСЛИМН в Excel

С помощью функции СЧЁТЕСЛИМН можно рассчитать количество ячеек, соответствующих критериям, применяемым к столбцу с числовыми значениями. Например, в ячейках A1:A9 содержится числовой ряд от 1 до 9. Функция =СЧЁТЕСЛИМН(A1:A9;»>2″;A1:A9;»<6″) вернет значение 3, поскольку между числами 2 и 6 расположены 3 числа (3, 4 и 5). Также критерии отбора данных могут быть применены к другим столбцам таблицы.

Как посчитать количество позиций в прайсе по условию?

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

Вид исходной таблицы:

Пример 1.

Для расчета количества телевизоров компании LG, стоимость которых не превышает 20000 рублей используем следующую формулу:

СЧЁТЕСЛИМН.

  • A2:A11 –диапазон первого условия, ячейки которого хранят текстовые данные с названием фирмы и величиной диагонали;
  • «LG*» – условие поиска с подстановочным знаком «*» (любое количество символов после «LG»;
  • B2:B11 – диапазон второго условия, содержащий значения стоимости товаров;
  • «<20000» – второе условие поиска (цена меньше 20000).

количество позиций в прайсе.

Как посчитать долю группы товаров в прайс-листе?

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

Вид исходной таблицы:

Пример 2.

Для получения искомого значения используем формулу:

Для поиска сразу нескольких значений в векторе данных (столбце B:B) в качестве аргумента условие1 была передана константа массива <"LG";"Samsung";"Bosch">, поэтому формулу необходимо выполнить в качестве формулы массива. Функция СУММ подсчитывает число элементов, содержащихся в массиве значений, возвращаемых функцией СЧЁТЕСЛИМН. Функция СЧЁТ возвращает число непустых ячеек в диапазоне A2:A21, то есть число строк в таблице. Соотношение полученных величин является искомым значением.

В результате вычислений получим:

доля группы товаров в прайс-листе.

Как посчитать количество ячеек по нескольким условиям в Excel?

Пример 3. В таблице приведены данные о количестве отработанных часов сотрудником на протяжении некоторого периода. Определить, сколько раз сотрудник работал сверх нормы (более 8 часов) в период с 03.08.2018 по 14.08.2018.

Вид таблицы данных:

Пример 3.

Для вычислений используем следующую формулу:

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

Как посчитать количество ячеек содержащих определенное значение в Exel

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

Количество ячеек, которые содержат определенный текст

В примере, активная ячейка содержит следующую формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.

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

X или Y

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

Количество ячеек, которые содержат x или y

Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».

Решение одной формулой

Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:

Эта формула основана на формуле, которая находит текст внутри ячейки:

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.

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

Нам нужно сложить эти цифры, но мы не хотим, чтобы удвоился счет. Таким образом, мы должны убедиться, что любое значение больше нуля. Чтобы сделать это, мы вернем все значения, которые больше 0, в Истина или Ложь, а затем с помощью двойного отрицания (—) переведем массив в формат 1 и 0.

И, наконец, СУММПРОИЗВ суммирует полученные числа.

Вспомогательный столбец решений

Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как «бесконечное множество»). Формула:

Читать еще:  Скачать Windows Handy Backup на русском языке

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).

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

Ошибки

Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.

Количество ячеек, которые содержат ошибки

В примере, активная ячейка содержит следующую формулу:

СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.

Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:

(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:

СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.

Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.

Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода формула будет выглядеть следующим образом:

Пять символов

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

Количество ячеек, содержащих 5 символов

В примере, активная ячейка содержит следующую формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять символов путем сопоставления содержимого каждой ячейки с шаблоном «. », который поставляется в качестве критерия для СЧЁТЕСЛИ. «?» символ является подстановочным в Excel, что означает «любой одиночный символ», так что эта модель будет считать ячейки, которые содержат любые пять символов. Подсчет ячеек, которые соответствуют этому шаблону возвращает число, в данном примере, это число 3.

Положительные числа

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

Количество ячеек, содержащих положительные значения

В примере, активная ячейка содержит следующую формулу:

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

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

Отрицательные числа

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

Количество ячеек, содержащих отрицательные значения

В примере, активная ячейка содержит следующую формулу:

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

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

Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:

Если в ячейке А1 находится значение «-5», критерии будут «<-5» после конъюнкции.

Цифры

Для подсчета количества ячеек, которые содержат числа, используйте функцию СЧЁТ. Однако в общем виде формулы (выше) rng представляет собой диапазон ячеек.

количество ячеек, которые содержат числа

В примере, активная ячейка содержит следующую формулу:

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

Нечетные числа

Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.

Количество ячеек, которые содержат нечетные числа

В примере, формула в ячейке E4 является:

Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).

Функция СУММПРОИЗВ непосредственно работает с массивами.

Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.

В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:

ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.

В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:

После того, как мы присвоили значения истина / ложь числам с помощью двойного отрицания, мы получили:

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

Текст

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

Количество ячеек, которые содержат текст

В примере, активная ячейка содержит следующую формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.

  • Логические значения истина и ложь не учитываются, как текст
  • Числа не подсчитываются «*», если они не будут введены в виде текста
  • Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.

Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:

Двойной дефис принуждает результат ЕТЕКСТ от логического значения ИСТИНА или ЛОЖЬ перейти к 1 и 0. СУММПРОИЗВ затем суммирует эти значения вместе, чтобы получить результат.

Чувствительная к регистру версия

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

НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:

Там, где текст является текстом, который вы ищете, и rng диапазон ячеек, которые вы хотите подсчитать. Там нет необходимости использовать групповые символы, так как НАЙТИ возвратит число, если текст найден в любом месте в ячейке.

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

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений <"а","b","а","а","а">. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным. Соответственно, подсчитываются остальные значения, т.е. "а" и "b". Ответ очевиден: количество уникальных значений равно 2.

Задача

Произведем подсчет числа уникальных текстовых значений в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в диапазоне кроме текстовых значений содержатся также и числа, то формула подсчитает и их. Чтобы игнорировать числовые значения нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Читать еще:  Как раздать WiFi с iPhone

Если в список постоянно добавляются значения, то можно создать Динамический именованный диапазон Исходный_список.

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

Примечание: Уникальные значения в файле примера выделены с помощью Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL).

Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст (""), а первая их игнорирует.

Приведем пример, когда это бывает важно.

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;"") определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в "пустых" ячейках С31:С34 на самом деле содержатся 4 значения "", которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.

СОВЕТ: Как подсчитать уникальные числовые значения показано в одноименной статье Подсчет уникальных числовых значений.

СОВЕТ: Как подсчитать уникальные числовые значения с дополнительными условиями (критериями) показано в статье Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL.

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

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

Значения 5, 6, 7 и 6 выводятся в виде трех уникальных значений: 5, 6 и 7.

Значения "Кирилл", "Сергей", "Сергей", "Сергей", результат — это два уникальных значения — "Кирилл" и "Сергей".

Существует несколько способов подсчета уникальных значений среди повторяющихся.

С помощью диалогового окна " Расширенный фильтр " можно извлекать уникальные значения из столбца данных и вставлять их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек содержит заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Откроется диалоговое окно " Расширенный фильтр ".

Выберите команду скопировать в другое место.

В поле Копировать в введите ссылку на ячейку.

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

Установите флажок только уникальные записи и нажмите кнопку ОК.

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

В пустой ячейке, расположенной под последней ячейкой диапазона, введите функцию строки . Используйте диапазон уникальных значений, которые вы только что скопировали в качестве аргумента, исключая заголовки столбцов. Например, если диапазон уникальных значений — B2: B45, вы вводите = Rows (B2: B45).

Для выполнения этой задачи используйте сочетание функций Если, сумм, Частота, ПОИСКПОЗи ДЛСТР .

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Добавьте итог с помощью функции сумм .

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

Возвращает расположение текстового значения в диапазоне с помощью функции ПОИСКПОЗ . Возвращаемое значение затем используется в качестве аргумента функции FREQUENCY , чтобы можно было оценивать соответствующие текстовые значения.

Поиск пустых ячеек с помощью функции Len . Длина пустых ячеек равна 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Функция ПОИСКПОЗ осуществляет поиск определенного элемента в диапазоне ячеек и возвращает относительное расположение этого элемента в диапазоне. Например, если в диапазоне a1: A3 содержатся значения 5, 25 и 38, функция формула = Match (25; a1: A3; 0) возвращает число 2, так как значение 25 является вторым элементом диапазона.

Функция ДЛСТР возвращает число символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция сумм (a1: A5) складывает все числа, содержащиеся в ячейкАх от a1 до A5.

Функция Если возвращает одно значение, если указанное условие имеет значение true, и другое, если условие имеет значение false.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

Итак, давайте соединим функции SUM() — суммирует, IF() — проверка условия,FREQUENCY() — подсчитывает кол-во значений, попадающих в определенный интервал, LEN() — считает кол-во символов, MATCH() — ищет позицию элемента в массиве:

1. Вычисление количества уникальных числовых значений

=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10;A2:A10)>0;1))

2. Вычисление количества уникальных числовых и текстовых значений (не работает, если есть пустые ячейки)

=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10;B2:B10;0);ПОИСКПОЗ(B2:B10;B2:B10;0))>0;1))

3. Вычисление количества уникальных значений (универсальная формула)

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»);ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»))>0;1))

Последнюю формулу нужно вводить как формулу массива, т.е. нажать не простоEnter, а Ctrl + Shift + Enter. После этого в строке формул мы увидим, что формула взята в фигурные скобки (<>), это признак того, что введенная формула массива.

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