000818.ru

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

Как объединить уникальные значения в Excel?

Тому, кто давно работает с Excel, задача «подсчет уникальных значений» в таблице вполне знакома. Можно подсчитать и специальной функцией, и сводной таблицей, но вот подсчет формулой знаком не всем — способ довольно интересный, рекомендую. Но обо всем по порядку.

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

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

Подсчет уникальных значений. Специальная возможность Excel

В табличном редакторе есть специальная кнопка — Данные — Удалить дубликаты. Здесь останавливаться на этом способе не буду, т.к. на сайте есть отдельная статья . Когда вы удалите дубликаты, то можно подсчитать оставшиеся уникальные города функцией СЧЁТЗ.

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

Считаем неповторяющиеся значения сводной таблицей

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

Выделяем таблицу с городами. Идем на вкладку Вставка на ленте (для версии 2007 и выше) в разделе. Самая левая кнопка — Сводная таблица. Жмем ее.

На отдельном листе формируем сводную таблицу — перетаскиваем/добавляем Города в название строк.

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

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

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

Подсчет уникальных значений формулой

Переходим к самому интересному — как посчитать уникальные города формулой? Здесь нам поможет одна хитрость и формула СЧЁТЕСЛИ . Специальной функции для подсчета уникальных значений в таблице нет, но проявим изобретательность. По факту нам нужно для каждого города понимать сколько раз он встречается в таблице и вместо этого итогового количества учитывать город 1 раз. Если город встречается 4 раза, то 1 строка, исходя из логики предыдущего предложения должна учитываться как четверть раза, т.е. 1 деленный на 4.

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

Теперь вы можете вычислить сумму этой колонки

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

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

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

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

Это стало модой.

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

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Несколько условий в функции ЕСЛИ

Когда любое из указанных условий выполнено, последующие уже не проверяются. Поэтому указание их в правильном порядке имеет решающее значение. Excel просто «не заметил» бы условия B2*0,9 и B2*0,95, если бы мы начали с B2*1, поскольку они были бы включены в интервал B2*1, проверяемый первым (если B2*1 меньше 0,9, то он также меньше 1). В этом случае у нас было бы только два варианта: меньше 1 и альтернатива. 1 или больше.

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

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

Подсказка функции

Не забудьте закрыть все скобки в конце, в противном случае Excel выдаст ошибку

Ошибка из-за нехватки скобки

Создание связанных таблиц

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

Способ 1: прямое связывание таблиц формулой

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

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

Таблица заработной платы в Microsoft Excel

Второй лист состоит из табличного списка сотрудников с указанием их зарплат. В обоих случаях список сотрудников представлен в одинаковом порядке.

Таблица со ставками сотрудников в Microsoft Excel

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

    На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.

Переход на второй лист в Microsoft Excel

Связывание с ячейкой второй таблицы в Microsoft Excel

Две ячейки двух таблиц связаны в Microsoft Excel

Маркер заполнения в Microsoft Excel

Все данные столбца второй таблицы перенесены в первую в Microsoft Excel

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

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

Читайте так же:
Как определить / проверить дату выходного дня в Excel?

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

Вставить функцию в Microsoft Excel

Переход в окно аргуметов функции ИНДЕКС в Microsoft Excel

Выбор формы функции ИНДЕКС в Microsoft Excel

«Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

«Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

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

Аргумент Массив в окне аргументов функции ИНДЕКС в Microsoft Excel

Окно аргументов функции ИНДЕКС в Microsoft Excel

Переход в окно аргуметов функции ПОИСКПОЗ в Microsoft Excel

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

«Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

«Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; ; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

2) Новый список с использованием формул

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

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

Читайте так же:
Как объединить и обновить таблицу из другой таблицы в Excel?

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

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

    Вводим следующую формулу в ячейку «E2»

=ИНДЕКС( $B$2:$B$225 ; НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ( $B$2:$B$225 ; $B$2:$B$225 ;0);"")=СТРОКА( $1:$224 ); ПОИСКПОЗ( $B$2:$B$225 ; $B$2:$B$225 ;0);""); СТРОКА()-1);1)

uniq.rec.excel3

Формула для подсчета уникальных значений добавляется во вторую колонку:

    Вводим следующую формулу в ячейку «D2»

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

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

Для вычисления уникальных значений текста в спектре можно использовать формулу, которая использует несколько функций: FREQUENCY, SEARCH, ARROW и SUMM PROCESS. Вот как выглядит формула в F5 в примере:

Который возвращает 4, поскольку B5 имеет 4 уникальных имени: B14.

Примечание. Очередной метод подсчета уникальных значений — применять функцию СЧЁТЕСЛИ. Это еще наиболее обычная формула, но она может медлительно работать с большенными наборами данных. В Excel 365 вы сможете применять наиболее ординарную и резвую формулу, основанную на UNIQUE.

Разъяснение

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

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

Результатом MATCH является массив:

Поскольку SEARCH всегда возвращает позицию первого совпадения, значения, которые встречаются в данных более одного раза, возвращают ту же позицию. Поскольку «Джим» встречается в списке три раза, он три раза появляется в этом массиве под номером 1.

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

Этот массив передается в FREQUENCY в качестве аргумента data_array. Аргумент bins_array строится из этой части формулы:

Который строит чередующийся список цифр для каждого значения в данных:

На этом шаге частота устанавливается следующим образом:

FREQUENCY возвращает массив чисел, указывающих количество любого числа в наборе данных, упорядоченных по ячейкам. Если число уже подсчитано, FREQUENCY возвращает ноль. Это главная особенность данной формулы. Результатом работы FREQUENCY является такой массив:

Примечание: FREQUENCY постоянно возвращает массив с одним элементом больше, чем bins_array .

Теперь мы можем переписать эту формулу таким образом:

Затем мы рассматриваем значения больше нуля (> 0) и преобразуем их в TRUE или FALSE, а затем используем двойное отрицательное число (-) для преобразования значений TRUE и FALSE в 1 и 0. Теперь у нас есть

В этом случае SUMMPROIZV просто складывает числа и возвращает значение 4.

Обработка пустых ячеек

Если в спектре есть пустые ячейки, формула вернет ошибку # H / D. Если есть пустые ячейки, вы можете использовать формулу массива, которая использует функцию ЕСЛИ для их удаления:

Примечание: добавление IF превращает эту формулу в формулу массива, что требует нажатия клавиш control-shift-ввод.

Нажмите здесь для получения дополнительной информации. Эта страница.

Остальные методы подсчета уникальных значений

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

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

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