Evolcom.ru

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

Как подсчитать, сколько раз ячейка была изменена в Excel?

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

В этой статье я хочу объяснить, как подсчитать количество повторений значения в таблице или ячейке. Давайте начнем с порядка. У нас есть стол:

И вам нужно подсчитать количество повторений каждого элемента:

Самый простой способ создания разворотной таблицы — поместить данные из столбца A в поля строки и значения. Вы можете узнать больше о создании и использовании таблиц pivot в этой статье видеоурока: Обзор перекрестных таблиц.

Но если по каким-то причинам сводная не Ваш вариант — в Excel имеется функция СЧЁТЕСЛИ (COUNTIF) , при помощи которой все это можно сделать тоже буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
=COUNTIF( $A$2:$A$30 , A2 )
Диапазон ( $A$2:$A$30 ) — указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий ( A2 ) — указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ( $A$2:$A$30 ;»Яблоко») . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия «*банан*» можно подсчитать количество ячеек, в которых встречается слово «банан» (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав «банан*» — значения, начинающиеся на «банан» (бананы, банановый сок, банановая роща и т.п.). «?» — заменяет лишь один символ, т.е. указав «бан?н» можно подсчитать строки и со значением «банан» и со значением «банон» и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ( $A$2:$A$30 ;»*») , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и ?) не получится применить к числовым значениям — исключительно к тексту. Т.е. если если указать в качестве критерия «12*», то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>12″)

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

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;»<>«)

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

Так же не могу не написать про небольшую особенность функции СЧЁТЕСЛИ (а так же СЧЁТЕСЛИМН , СУММЕСЛИ , СУММЕСЛИМН и им подобных) — данные функции всегда стремятся преобразовать все значения аргументов к типам(в отличии от той же ВПР , которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число 23 — оно будет воспринято как число. Если тоже число будет записано как текст — «23» , то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и «23» у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
000 34889913131323455
00 34889913131323455
000 34889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго — два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СЧЁТЕСЛИ после преобразования все три этих значения будет считать как число 348899131313234 00 и если записать функцию так: =СЧЁТЕСЛИ( $A$1:$A$3 ; A1 ) , то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.

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

Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $D$1 ):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР( $D$1 )-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 )
ДЛСТР — подсчитывает количество символов в указанной ячейке/строке( $D$1 , D3 )
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) — заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан( D3 ) пошаговый разбор формулы:

  • Используя функцию DLSTR, получаем количество символов в строке с исходным текстом ( $D$1 ) =(170-DLSTR(SUBSTITUTE( $D$1 ; D3 ;»»))/DLSTR( D3 ) ;
  • С помощью SUBSTITUTE заменить все значения Banana( D3 ) в строке на пробелы и получить количество символов в строке после этой замены =(170-155)/DLSTR( D3 ) с помощью DLSTR ;
  • Вычесть из общего количества символов в строке после замены и разделить результат на количество символов в критерии =(170-155)/5 .

У нас есть номер 3. Это то, что нам было нужно.

Подсчет внутри ячеек диапазона

Однако есть и другая сложная ситуация — когда имеется ряд ячеек, в каждой из которых наше слово встречается более одного раза. И нам нужно подсчитать ВСЕ повторения. Для подсчета повторений будут использоваться ячейки с A1 по A10. Слово для подсчета повторений появится в ячейке B1 (это будет «банан»):

Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР( A1:A10 )-ДЛСТР(ПОДСТАВИТЬ( A1:A10 ; B1 ;»»)))/ДЛСТР( B1 ))

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

И простая пользовательская функция, которая также подсчитывает повторения в ячейке:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) — Len(Replace(sTxt, sCntWord, ""))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(InsertModule) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt — текст, в котором подсчитываем кол-во вхождения.
sCntWord — текст для подсчета. Может быть символом или словом.

Пример подсчета повторов.xls (70,5 КБ, 12 634 скачиваний)

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