Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

Наличието на списък с дублиращи се стойности, да създадете списък, състоящ се само от уникалните стойности. Когато добавяте нови стойности на първоначалния списък с уникален списък ценности да се актуализират автоматично.

Да предположим, че колона А има списък с дублиращи се стойности, например, списък с имената на фирмите.

Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

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

Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

За по-голяма яснота, уникалните стойности в списъка на източниците са подчертани с помощта на условно форматиране.

Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

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

За да създадете динамичен обхват:

  • Формули раздела в Дефинирани имена на групи, кликнете върху Задаване на име;
  • в полето Име тип: Iskhodnyy_spisok;
  • в областта на Range, въведете формулата = OFFSET ($ A $ UnikalnyeZnacheniya 5 ;;; COUNTA (UnikalnyeZnacheniya $ A $ 5: !! $ A $ 30))
  • натиснете ОК.

Създаване на списък на отделните стойности в колона В при използване на масив с формула (вж. Пример файл). За да направите това, въведете следната формула в клетка B5:

Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

След въвеждане на формула вместо ENTER трябва да натиснете CTRL + SHIFT + ENTER. След това, за да копирате формулата надолу, например, с помощта на маркери необходими. За всички стойности на първоначалния списък са гарантирани уникални стойности, показани в списъка, трябва да се направи от размера на уникален списъка на ценности, равен на размера на първоначалния списък (в случай, когато всички стойности не се повтарят в първоначалния списък). В случай на списък източник на голям брой повтарящи се стойности, списък на уникални стойности може да се направи по-малък чрез премахване на излишни формула, за да се избегнат ненужни изчисления, инхибиране лист реализация.

Нека да работим по формула:

  • Тук използването на COUNTIF на функциите () е необичайно: като критерий (вторият аргумент) не е уточнено една стойност, но цяла поредица от Iskhodnyy_spisok. Следователно, функцията не връща една стойност, но цяла поредица от нули и единици. Тя връща 0, ако стойността на стартовата листа не е намерена в B4 диапазон: B4 (B4: B5 и т.н.), и 1, ако е намерен. Например, в клетъчна В5 COUNTIF формула (Ь $ 4: В5; Iskhodnyy_spisok) връща масив. Т.е. Първоначалния списък Намерени 4 ценности «OOO рог и копита» (В5). Array лесно да видите като натиснете F9 (в лентата с формули акцент израз COUNTIF (В $ 4 :. B5; Iskhodnyy_spisok) Натиснете F9 формула показва вместо своя резултат.);
  • MATCH () - връща позицията на водещата нула в масива от предишната стъпка. Първият нула отговаря на стойност все още не е намерен в списъка на източник (т.е. стойност "на компания с добра репутация" формула в клетка B5);
  • INDEX () - възстановява стойността на своята позиция в Iskhodnyy_spisok гама;
  • IFERROR () подтиска проблем, който се появява, когато съответства на функция () се опитва в масива от нули и единици връща COUNTIF (). Намери 0, което не е (ситуация възниква в клетка B12., Когато всички уникалните стойности са получени от първоначалния списък.)

Формулата ще работи, а ако списъкът източник съдържа числови стойности.

Ако таблицата източник съдържа пропуски, ще трябва да използвате различен формула за масив (виж лист проба файл проходи.)
= IFERROR (INDEX ($ A $ 5: $ A $ 19;
MATCH (0; IF (ISBLANK ($ A $ 5: А19); ""; COUNTIF ($ B $ 4: В4; $ $ 5: $ A $ 19)), 0)
); "")

Избор на уникални стойности (премахване на дубликати от списък) в MS Excel - съвместим с Microsoft Excel

По принцип е възможно да се направи без използването на формули за масиви. поставяне на допълнително колона режийни за междинни изчисления (вж. листа "Без ПТ" в пример файл).

СЪВЕТ:
Списък на уникални стойности могат да бъдат създадени по различни начини, например, като се използва Enhanced филтъра (вж. Член Избор на уникални низове с помощта на Advanced Filter), осеви таблици или меню Data / Операция Data / Премахване на дубликати. Всеки метод има своите предимства и недостатъци. Но в тази статия искаме да, когато добавяте нови стойности на първоначалния списък на отделните стойности списък следва да се актуализират автоматично, така че да съставите списък с помощта на формули.

SOVET2:
За тези, които създадете списък с уникални стойности, за да се образува падащия списък следва да вземе предвид факта, че по-горе формула връща празен текст "". което изисква внимателно боравене, особено в изчисляването на стойностите (вместо обичайната функция COUNTA () трябва да използвате COUNTIF () със специални аргументи). Например, виж. Член Dynamic падащия списък в MS EXCE L.

сродни статии

Прочети други статии, които решават подобни проблеми в MS Excel. Това ще ви позволи да се реши широк клас от подобни проблеми.