Подсчет уникальных значений в Excel
В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых значений, в том числе с учетом регистра букв.
При работе с большим набором данных в Excel вам часто может потребоваться знать, сколько в вашей таблице повторяющихся и сколько уникальных записей.
И вот о чем мы сейчас поговорим:
Если вы регулярно посещаете этот блог, вы уже знаете формулу Excel для подсчета дубликатов. А сегодня мы собираемся изучить различные способы подсчета уникальных значений в Excel. Но для ясности давайте сначала определимся с терминами.
- Уникальные значения – те, которые появляются в списке только один раз.
- Различные – это все, которые имеются в списке без учета повторов, то есть уникальные плюс первое вхождение повторяющихся.
Следующий рисунок иллюстрирует эту разницу:
А теперь давайте посмотрим, как можно их посчитать с помощью формул и функций сводной таблицы.
Далее вы найдете несколько примеров для подсчета уникальных данных разных типов.
Считаем уникальные значения в столбце.
Предположим, у вас есть столбец с именами на листе Excel, и вам нужно подсчитать, сколько там есть неповторяющихся. Самое простое решение состоит в том, чтобы использовать функцию СУММ в сочетании с ЕСЛИ и СЧЁТЕСЛИ :
Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы корректно ввести её. Как только вы это сделаете, Excel автоматически заключит всё выражение в <фигурные скобки>, как показано на скриншоте ниже. Ни в коем случае нельзя вводить фигурные скобки вручную, это не сработает.
В этом примере мы считаем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:
Этот метод подходит и для текстовых, и для цифровых данных. Недостатком является то, что в качестве уникального он будет пересчитывать любое содержимое, в том числе и ошибки.
Далее в этом руководстве мы обсудим несколько других подходов для подсчета уникальных значений разных типов. И поскольку в основном они являются вариациями этой базовой формулы, имеет смысл подробно рассмотреть её. Если вы поймете, как это работает, то сможете настроить ее для своих данных. Если кого-то не интересуют технические подробности, вы можете сразу перейти к следующему примеру.
Как работает формула подсчета уникальных значений?
Как видите, здесь используются 3 разные функции – СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждая из них:
- Функция СЧЁТЕСЛИ считает, сколько раз каждое отдельное значение появляется в анализируемом диапазоне.
В этом примере СЧЁТЕСЛИ(A2:A10;A2:A10)возвращает массив <3:2:2:1:1:2:3:2:3>.
- Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть, уникальные) и заменяет все остальные цифры нулями.
Итак, функция ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0) преобразуется в ЕСЛИ(<3:2:2:1:1:2:3:2:3>) = 1,1,0).
И далее она превращается в массив чисел <0:0:0:1:1:0:0:0:0>. Здесь 1 означает уникальное значение, а 0 – появляющееся более 1 раза.
- Наконец, функция СУММ складывает числа в этом итоговом массиве и выводит общее количество уникальных значений. Что нам и нужно.
Совет. Чтобы узнать, какой результат дает какая-то определенная часть выражения, выделите эту часть в строке формул и нажмите функциональную клавишу F9.
Подсчет уникальных текстовых значений.
Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:
Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.
Не забывайте нажимать Ctrl + Shift + Enter , чтобы правильно ввести формулу массива, и вы получите результат, подобный этому:
Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.
Как сосчитать уникальные числовые значения.
Чтобы посчитать уникальные числа в списке данных, используйте формулу массива точно так же, как мы только что делали при подсчете текстовых данных. Отличие заключается в том, что вы используете ЕЧИСЛО вместо ЕТЕКСТ:
Пример и результат вы видите на скриншоте чуть выше.
Примечание. Поскольку Microsoft Excel хранит дату и время как числа, они также участвуют в подсчёте.
Уникальные значения с учетом регистра.
Если для вас принципиально различие в заглавных и прописных буквах, то самым простым способом подсчета будет создание вспомогательного столбца со следующей формулой массива для идентификации повторяющихся и уникальных элементов:
А затем используйте простую функцию СЧЁТЕСЛИ для подсчета уникальных значений:
А теперь посмотрим, как можно посчитать количество значений, которые появляются хотя бы один раз, то есть так называемых различных значений.
Подсчет различных значений.
Используйте следующую универсальное выражение:
Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter , вместо обычного Enter.
Кроме того, вы можете использовать функцию СУММПРОИЗВ и записать формулу обычным способом:
=СУММПРОИЗВ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))
Например, чтобы сосчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:
Этот способ подходит не только для подсчета в столбце, но и для диапазона данных. К примеру, у нас под имена отведено две колонки. Тогда делаем так:
Этот метод подходит для текста, чисел, дат.
Единственное ограничение – диапазон должен быть непрерывным и не содержать пустых ячеек и ошибок.
Если в вашем диапазоне данных есть пустые ячейки, то можно изменить:
Тогда в расчёт попадёт и будет засчитана и пустая ячейка.
Как это работает?
Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере, результат работы функции СЧЕТЕСЛИ представляет собой числовой массив: <3:2:2:1:3:2:1:2:3>.
После этого выполняется ряд операций деления, где единица делится на каждую цифру из этого массива. Это превращает все неуникальные значения в дробные числа, соответствующие количеству повторов. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента равные 0,5 (1/2 = 0,5). А если появляется 3 раза, в массиве создаются 3 элемента 0,333333.
В нашем примере результатом вычисления выражения 1/СЧЁТЕСЛИ(A2:A10;A2:A10) является массив <0.333333333333333:0.5:0.5:1:0.333333333333333:0.5:1:0.5:0.333333333333333>.
Пока не слишком понятно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций складывает числа в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько раз он появлялся. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), окончательный результат представляет собой общее количество всех встречающихся значений.
Как и в случае подсчета уникальных значений в Excel, вы можете использовать варианты универсальной формулы для обработки отдельно чисел, текста или же с учетом регистра.
Помните, что все приведенные ниже выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter .
Подсчет различных значений без учета пустых ячеек
Если столбец, в котором вы хотите совершить подсчет, может содержать пустые ячейки, вам следует в уже знакомую нам формулу массива добавить функцию ЕСЛИ. Она будет проверять ячейки на наличие пустот (основная формула Excel, описанная выше, в этом случае вернет ошибку #ДЕЛ/0):
Вот как, к примеру, можно посчитать количество индивидуальных значений, игнорируя пустые ячейки:
Число Фибоначчи. Почему оно так популярно в природе?
Таинственное число Фибоначчи, равное 1,618, будоражит умы ученых уже на протяжении нескольких тысячелетий. Кто-то считает это число строителем мироздания, кто-то называет его числом Бога, а кто-то, не мудрствуя лукаво, просто применяет его на практике и получает невероятные архитектурные, художественные и математические творения. Число Фибоначчи было обнаружено даже в пропорциях знаменитого «Витрувианского человека» Леонардо Да Винчи, который утверждал, что знаменитое число, пришедшее из математики, руководит всей Вселенной.
«Витрувианский человек» Леонардо да Винчи обладает идеальными пропорциями, основанными на знании свойств числа Фибоначчи
Кто такой Фибоначчи?
Леонардо Пизанский считается самым первым крупным математиком в истории средневековой Европы. Несмотря на это, свое знаменитое прозвище «Фибоначчи» ученый получил далеко не из-за своих экстраординарных математических способностей, но из-за своего везения, так как «боначчи» по-итальянски означает «удачливый». Перед тем как стать одним из самых известных математиков раннего Средневековья, Леонардо Пизанский изучал точные науки у самых продвинутых учителей своего времени, которыми считались арабы. Именно благодаря этой деятельности Фибоначчи, в Европе появились десятичная система счисления и арабские цифры, которыми мы пользуемся до сих пор.
В одном из своих самых известных трудов под названием «Liber abaci», Леонардо Пизанский приводит уникальную закономерность чисел, которые при постановке в ряд образуют линию цифр, каждая из которых является суммой двух предыдущих чисел.
Последовательность Фибоначчи
Иными словами, последовательность Фибоначчи выглядит так:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987 и так далее.
Каждое число из ряда Фибоначчи, разделенное на последующее, имеет значение, стремящееся к уникальному показателю, которое составляет 1,618. Первые числа ряда Фибоначчи не дают настолько точное значение, однако по мере нарастания, соотношение постепенно выравнивается и становится все более точным.
Леонардо Пизанский — тот самый создатель числа Фибоначчи
Где используется число Фибоначчи
Из-за своего повсеместного применения в природе, золотое сечение (именно так число Фибоначчи иногда называют в искусстве и математике) считается одним из самых гармонизирующих законов мироздания, который упорядочивает структуру окружающего нас мира и направляет жизнь на развитие. Так, правило золотого сечения применяется природой для образования траекторий движения вихревых потоков в ураганах, при образовании эллиптических галактик, к которым относится и наш Млечный Путь, при «строительстве» раковины улитки или ушной раковины человека, направляет движение косяка рыб и показывает траекторию движения испуганной стаи оленей, врассыпную убегающую от хищника.
Проявление золотого сечения в природе
Эстетичность такой гармонизации мироздания воспринимается человеком, который всегда стремился улучшить окружающую его действительность, в качестве стабилизирующего природу закона. Находя золотое сечение в лице того или иного человека, мы инстинктивно воспринимаем собеседника в качестве гармоничной личности, чье развитие происходит без сбоев и нарушений. Этим можно объяснить то, почему иногда нам по непонятным причинам больше нравится одно лицо, чем другое. Оказывается, о наших возможных симпатиях позаботилась природа!
Как вы считаете, является ли повсеместное применение числа Фибоначчи в природе совпадением или свидетельством наличия некоего вселенского разума? Давайте попробуем обсудить этот вопрос в нашем Telegram-чате.
Наиболее распространенное определение золотого сечения гласит, что меньшая часть так относится к большей, как большая часть относится ко всему целому. Уникальное правило встречается во всех областях природы, науки и искусства, позволив некоторым именитым исследователям Средних Веков сделать предположение, что три основные части золотого сечения олицетворяют собой христианских Отца, Сына и Святого Духа.
Правилу золотого сечения следуют даже галактики. Наш Млечный Путь в этом плане не является исключением
Что такое золотое сечение
С точки зрения математики, золотое сечение представляет собой некую идеальную пропорцию, к которой каким-то образом стремится все живое и неживое в природе.
Так выглядит «золотое сечение»
Используя основные принципы ряда Фибоначчи, растут семечки в центре подсолнуха, движется спираль ДНК, был построен Парфенон и написана самая знаменитая картина в мире — «Джоконда» Леонардо Да Винчи.
Даже коты неосознанно (хотя, кто знает?) следуют принципу золотого сечения, становясь любимцами большей части населения планеты
Есть ли в природе гармония? Несомненно, есть. А ее доказательством служит число Фибоначчи, происхождение которого нам еще только предстоит отыскать.
Личный код удачи в нумерологии: зачем он нужен и как рассчитать
Дата рождения каждого из нас не случайна. День, месяц и год, в который мы приходим в этот мир – это уникальный код, от которого зависит наша судьба. Цифры рождения — это личная программа, которая формирует энергетическое поле и влияет на то, каким будет человек и как сложиться его жизнь.
Наши эксперты посвятили свою жизнь изучению этого вопроса. И сейчас они готовы подарить это знание всем людям, а главное поделиться тем, как узнать и включить личный код удачи и успеха.
Древние знания
Древнегреческий философ, математик и мистик Пифагор утверждал, что миром правят числа. И был несомненно прав. Благодаря его глубоким знаниям появилась основополагающая наука о взаимосвязи материального мира и чисел под названием — Нумерология. Действительно, если подумать наша жизнь полностью окружена цифрами. Номера телефонов и квартир, банковских счетов и денежных купюр.
Но самыми главными в жизни человека остаются цифры даты его рождения. Это его личный код, понимание и разгадка которого дает ему ключ от сейфа своей души.
Личный код. Что это и зачем он нужен
Наверняка вы не раз задавались вопросом – почему одним всегда везет, а других на каждом шагу подстерегают проблемы и испытания? У одних и в семье все складывается и на работе, а бывают несчастные, у которых все, как по неправильному сценарию? У каждого из нас есть особенное энергетическое поле, которое влияет на все, происходящее с нами в этой жизни.
В основе этого поля лежит персональный код человека. Изучением этого кода как раз и занимается наука Нумерология. Но и для простых людей, если не уходить в сложные расчеты, доступны некоторые знание, овладев которыми каждый сможет рассчитать личный код и изменить свою жизнь к лучшему. Персональный Код Удачи будет усиливать вибрации вашего поля, увеличивать силу мысли и помогать в достижении целей.
Расчет личного кода
Персональный код человека состоит из четырех цифр. Для расчета необходимо всего лишь суммировать числа своего рождения. Сначала день, потом месяц и затем год. Пока, в конце концов, у вас не выйдет одна цифра. На примере это выглядит так.
- Дата рождения человека 28 июня 1984 года.
- Первое число получаем, сведя к одну число дня рождения.
- 2 + 8 = 10 = 1 + 0 = 1
- Второе число кода получаем из даты месяца.
- 0 + 6 = 6
- Третье число получаем, суммировав год рождения.
- 1 + 9 + 8 + 4 = 22 = 2 + 2 = 4
- Четвертое число – сумма трех предыдущих
- 1 + 6 + 4 = 11 = 1 + 1 = 2
- Итого получаем персональный код человека – 1-6-4-2
Эта комбинация цифр и будет являться индивидуальным кодом для включений в жизни ресурсов удачи и любви.
Как использовать личный код удачи
Индивидуальный код удачи можно активизировать просто, обращаясь к нему. Чтобы везение сопутствовало всегда, это нужно делать как можно чаще. Подружитесь с ним! Вы увидите, как что-то меняется в окружении и в вас самих. Дела удаются легче и проще, а в голову приходят светлые идеи. Если просто рассчитать код и забыть о нем, ничего не изменится. Как и в любых практиках волшебный инструмент нужно использовать.
Как активировать код
Отлично работает вербальное обращение к коду, то есть его повторение вслух. Очень удобно использовать свой личный код в пинкодах на телефоны и кредитные карты и паролях на сайтах. Можно написать на листке и носить в бумажнике или просто вычерчивать его на полях ежедневника в свободное время. Можно и нужно придумывать свои способы активации, главное одно, чем чаще к нему обращаться, тем лучше.
Расшифровка персонального кода
Итак, мы подошли к самому интересному. Какую же информацию дают нам цифры нашего кода?
- Если у человека в коде присутствует цифра «1», он самодостаточный и уверенный в себе. У него сильная воля. Такой человек умеет ставить перед собой цели и добиваться их. И чем единиц больше, тем сильнее развиты эти качества. Если «1» вовсе отсутствует – у человека скорее всего заниженная самооценка.
- Двойки невероятно энергичные, жизненная сила в них так и кипит, а если двоек несколько, энергия просто переливается через край.
И соответственно, если двоек в коде нет человек очень спокойный и даже инфантильный. Обычно такие люди плывут по течению и не любят перемен.
- Тройки, это люди с развитой интуицией. Если троек несколько, то это однозначно необычные личности с неординарными экстрасенсорными способностями. Если тройки отсутствуют, у человека могут быть проблемы с эмпатией и общением с другими людьми и даже с самим собой.
- Четверки невероятно коммуникабельны и могут с любым найти общий язык. Если «4» несколько человек может добиться больших результатов в спорте. Без «4» человек закрыт и даже скрытен от окружающих, любит одиночество.
- Пятерки добрые и покладистые. Они очень чуткие и терпеливые к другим. Если такой цифры нет могут быть грубыми и даже черствыми к несчастьям других
- Очень трудолюбивые и ответственные шестерки часто добиваются успеха в жизни. Если «6» в коде нет это говорит о лени и не желании прилагать усилия к чему-либо.
- Семерки — это настоящие везунчики и этим все сказано. Отсутствие «7» говорит об очень мягком характере и подверженности чужому мнению.
- Восьмерка – это богатство, и чем ее больше, тем легче путь к нему у человека. Если «8» нет путь к успеху у человека довольно тернист.
- Девятки милосердные, такие люди думают о других даже больше, чем о себе. А отсутствие в коде этой цифры говорит о некоей грубости и даже заносчивости.
Универсальные комбинации
Что касается общих для всех значений цифр есть универсальные комбинации для всех людей, независимо от их персонального кода.
Так четыре «8» приносят богатство каждому, кто активизирует такую комбинацию.
3-3-3-5-7-9-9 — включает энергию изобилия, такая комбинация также способна решить финансовые проблемы.
1-8-5-1-5-1-8 — помогает справиться с любыми препятствиями на пути.
5-1-1-2-4-6-1 — придаст своему владельцу уверенности и сил, помогает гармонизировать ресурс любви и личные отношения.
8-9-3-1-5-4-2 — комбинация удачи в начинаниях, сулит удачный и быстрый старт.
Персональный денежный код
Точно также, как индивидуальный код человека можно рассчитать и персональный денежный код. Это также просто и быстро. Необходимо всего лишь сложить все числа даты вашего рождения, пока не получится целое число. Например:
- 17.11.1992.
- 1+7+1+1+1+9+9+0=29, 2+9=11, 1+1=2.
- Затем прибавьте к числам дня рождения месяц рождения:
- 1+7+1+1=10, 1+0=1.
- Отдельно сложите числа дня и месяца:
- 1+7=8.
- 1+1=2.
- Ваш персональный денежный код — 2182.
Его нужно использовать так же, как код удачи. Можно сделать обои на рабочем столе компьютера, написать на стикерах — напоминалках и развесить в кабинете или, если кардинально, сделать с любимыми цифрами тату. Предела взаимодействия с персональными кодами нет. Больше о личном коде и других тайнах нумерологии смогут рассказать наши эксперты.
Чем отличаются Числовые коды от Числовых рядов? ПО кодам понятно и как применять, по Рядам не понятно.
Более 20 экспертов в области эзотерики готовы поговорить с вами по телефону или в чате.