Как в экселе столбец сделать по алфавиту


Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Лучшие новости сайта


»

1 Май 2011              287545 просмотров

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись - т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.


 
при помощи встроенных возможностей Excel 2007 и выше
В Excel 2007 и 2010 это сделать проще простого - есть специальная команда, которая так и называется - Удалить дубликаты (Remove Duplicates). Расположена она на вкладке Данные (Data) подраздел Работа с данными (Data tools)

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data) -Удалить дубликаты (Remove Duplicates).

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

Появится окно с параметрами удаления дубликатов

Ставите галочки напротив тех столбцов, дубликаты в которых надо удалить и жмете Ок. Если в выделенном диапазоне так же расположены заголовки данных, то лучше поставить флаг Мои данные содержат заголовки, чтобы случайно не удалить данные в таблице(если они вдруг полностью совпадают со значением в заголовке).


 
Способ 1: Расширенный фильтр
В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты. Но зато есть такой замечательный инструмент, как Расширенный фильтр. В 2003 этот инструмент можно найти в Данные -Фильтр -Расширенный фильтр. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне.В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data), группа Сортировка и фильтр (Sort & Filter) - Дополнительно (Advanced)
Как его использовать: запускаем указанный инструмент - появляется диалоговое окно:
  • Обработка: Выбираем Скопировать результат в другое место (Copy to another location).
  • Исходный диапазон (List range): Выбираем диапазон с данными(в нашем случае это А1:А51).
  • Диапазон критериев (Criteria range): в данном случае оставляем пустым.
  • Поместить результат в диапазон (Copy to): указываем первую ячейку для вывода данных - любую пустую(на картинке - E2).
  • Ставим галочку Только уникальные записи (Unique records only).
  • Жмем Ок.

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

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

Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place).


 
Способ 2: Формулы
Этот способ сложнее в понимании для неопытных пользователей, но зато он создает список уникальных значений, не изменяя при этом исходные данные. Ну и он более динамичен: если изменить данные в исходной таблице, то изменится и результат. Иногда это бывает полезно. Попытаюсь объяснить на пальцах что и к чему: допустим, список с данными у Вас расположен в столбце А(А1:А51, где А1 - заголовок). Выводить список мы будем в столбец С, начиная с ячейки С2. Формула в C2 будет следующая:
{=ИНДЕКС($A:$A;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C:C1;$A:$A)=0;СТРОКА($A:$A));1))}
{=INDEX($A:$A;SMALL(IF(COUNTIF($C:C1;$A:$A)=0;ROW($A:$A));1))}
Детальный разбор работы данной формулы приведен в статье:
Надо отметить, что эта формула является . Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш - Ctrl+Shift+Enter. После того, как мы ввели эту формулу в C2 мы её должны скопировать и вставить в несколько строк так, чтобы точно отобразить все уникальные элементы. Как только формула в нижних ячейках вернет #ЧИСЛО! - это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать и сделать формулу более универсальной(не протягивая каждый раз до появления ошибки) можно использовать нехитрую проверку:
для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A:$A;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C:C1;$A:$A)=0;СТРОКА($A:$A));1));"")}
{=IFERROR(INDEX($A:$A;SMALL(IF(COUNTIF($C:C1;$A:$A)=0;ROW($A:$A));1));"")}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C:C1;$A:$A)=0;СТРОКА($A:$A));1));"";ИНДЕКС($A:$A;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C:C1;$A:$A)=0;СТРОКА($A:$A));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C:C1;$A:$A)=0;ROW($A:$A));1));"";INDEX($A:$A;SMALL(IF(COUNTIF($C:C1;$A:$A)=0;ROW($A:$A));1)))}
Тогда вместо ошибки #ЧИСЛО!(#NUM!) у вас будут пустые ячейки(не совсем пустые, конечно - с формулами :-)).

Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье:


 
Способ 3: код VBA
Данный подход потребует разрешения макросов и базовых знаний о работе с ними. Если не уверены в своих знаниях для начала рекомендую прочитать эти статьи:

Оба приведенных ниже кода следует помещать в стандартный модуль. Макросы должны быть разрешены.

Исходные данные оставим в том же порядке - список с данными расположен в столбце "А"(А1:А51, где А1 - заголовок). Только выводить список мы будем не в столбец С, а в столбец Е, начиная с ячейки Е2:

С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value, то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить - вместо [E2] поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:

Или другой столбец:

здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения А2 на С2 изменилась и цифра 1 на 3. Это указание на номер столбца, в котором необходимо определить последнюю заполненную ячейку, чтобы код не просматривал лишние ячейки. Подробнее про это можно прочитать в статье:

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

Sub Extract_Unique()

    Dim x, avArr, li As Long

    Dim avVals

    Dim rVals As Range, rResultCell As Range

    

    On Error Resume Next

    'запрашиваем адрес ячеек для выбора уникальных значений

    Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8)

    If rVals Is Nothing Then 'если нажата кнопка Отмена

        Exit Sub

    End If

    'если указана только одна ячейка - нет смысла выбирать

    If rVals.Count = 1 Then

        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.excel-vba.ru"

        Exit Sub

    End If

    'отсекаем пустые строки и столбцы вне рабочего диапазона

    Set rVals = Intersect(rVals, rVals.Parent.UsedRange)

    'если указаны только пустые ячейки вне рабочего диапазона

    If rVals Is Nothing Then

        MsgBox "Недостаточно данных для выбора значений", vbInformation, "www.excel-vba.ru"

        Exit Sub

    End If

    avVals = rVals.Value

    'запрашиваем ячейку для вывода результата

    Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8)

    If rResultCell Is Nothing Then 'если нажата кнопка Отмена

        Exit Sub

    End If

    'определяем максимально возможную размерность массива для результата

    ReDim avArr(1 To Rows.Count, 1 To 1)

    'при помощи объекта Коллекции(Collection)

    'отбираем только уникальные записи,

    'т.к. Коллекции не могут содержать повторяющиеся значения

    With New Collection

        On Error Resume Next

        For Each x In avVals

            If Len(CStr(x)) Then 'пропускаем пустые ячейки

                .Add x, CStr(x) 'если добавляемый элемент уже есть в Коллекции - возникнет ошибка

                'если же ошибки нет - такое значение еще не внесено,

                'добавляем в результирующий массив

                If Err = 0 Then

                    li = li + 1

                    avArr(li, 1) = x

                Else

                    'обязательно очищаем объект Ошибки

                    Err.Clear

                End If

            End If

        Next

    End With

    'записываем результат на лист, начиная с указанной ячейки

    If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr

End Sub


 
Способ 4: Сводные таблицы
Несколько нестандартный способ извлечения уникальных значений.
  • Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
  • В диалоговом окне Создание сводной таблицы(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
  • указываем место размещения Сводной таблицы:
    • На новый лист (New Worksheet)
    • На существующий лист (Existing Worksheet)
  • подтверждаем создание нажатием кнопки OK

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

  • выделил диапазон A1:B51 на листе Извлечение по критерию
  • вызвал меню вставки сводной таблицы: вкладка Вставка(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
    выбрал вставить на новый лист(New Worksheet)
  • назвал этот лист Уникальные сводной таблицей
  • поле Данные поместил в область строк
  • поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий
    Отбор уникальных сводной таблицей

В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши -Обновить(Refresh) или вкладка Данные(Data) -Обновить все(Refresh all) -Обновить(Refresh). А если исходные данные пополняются динамически и того хуже - надо будет заново указывать диапазон исходных данных. И еще один минус - данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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

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

Скачать пример:

   (107,5 KiB, 10 988 скачиваний)

Также см.:


Статья помогла? Поделись ссылкой с друзьями!   Плейлист   Видеоуроки

Поиск по меткам


Источник: http://www.excel-vba.ru/chto-umeet-excel/kak-poluchit-spisok-unikalnyxne-povtoryayushhixsya-znachenij/


Лучшие новости сайта


Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Как в экселе столбец сделать по алфавиту

Похожие новости: