Как сделать distinct в excel
Обновлено: 08.07.2024
Эти предикаты задают записи, выбираемые с помощью запросов SQL.
Синтаксис
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM таблица
Инструкция SELECT, содержащая эти предикаты, состоит из следующих частей:
Используется по умолчанию, если вы не указываете ни один из предикатов. Ядро СУБД Microsoft Access выбирает все записи, которые удовлетворяют условиям в инструкции SQL. Следующие два примера эквивалентны и возвращает все записи из таблицы Employees:
Исключает записи, содержащие повторяющиеся данные в выбранных полях. Для включения в результаты запроса значения каждого из полей, перечисленных в инструкции SELECT, должны быть уникальными. Например, у нескольких сотрудников, перечисленных в таблице Employees, могут быть одинаковые фамилии. Если две записи содержат "Глазков" в поле LastName, следующая инструкция SQL возвращает только одну запись, содержащую значение "Глазков":
Если опустить DISTINCT, этот запрос возвратит обе записи с фамилией "Глазков".
Если предложение SELECT содержит несколько полей, запись будет включена в результаты только в том случае, если сочетание значений всех таких полей уникально.
Выходные данные запроса, использующего DISTINCT, не является обновляемыми и не отражают изменения, внесенные другими пользователями.
Данные не просто повторяют поля, но и не повторяются. Например, можно создать запрос, который соединяет таблицы Customers и Orders по полю CustomerID. Таблица "Клиенты" не содержит повторяют поля CustomerID, но таблица Orders содержит, так как у каждого клиента может быть множество заказов. В следующей SQL показано, как использовать DISTINCTROW для создания списка компаний, у кого есть хотя бы один заказ, но нет сведений об этих заказах:
Если опустить DISTINCTROW, этот запрос создаст несколько строк для каждой компании, от которой поступало более одного заказа.
DISTINCTROW действует только в том случае, если вы выбираете поля из некоторых (но не всех) таблиц, используемых в запросе. DISTINCTROW игнорируется, если запрос содержит только одну таблицу или вы включаете поля из всех таблиц.
TOP n [PERCENT]
Возвращает записи, относящиеся к верхней или нижней части диапазона, заданного предложением ORDER BY. Предположим, что вы хотите получить имена 25 лучших студентов из группы 1994 г.:
Если не включить предложение ORDER BY, запрос вернет из таблицы Students произвольный набор, включающий 25 записей, которые удовлетворяют предложению WHERE.
Предикат TOP не выбирает между равными значениями. Если в предыдущем примере двадцать пятый и двадцать шестой средний балл совпадают, запрос вернет 26 записей.
Вы также можете использовать зарезервированное слово PERCENT для возвращения определенного процента записей из верхней или нижней части диапазона, заданного предложением ORDER BY. Предположим, что вместо 25 лучших студентов вы хотите получить 10 процентов худших студентов группы:
Предикат ASC позволяет вернуть нижние значения. Значение после TOP должно быть целым числом без знака.
If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that?
In SQL Server I would do something like
7 Answers 7
Simpler than you might think:
- Click the Data Ribbon Menu
- Select the Advanced Button in the Sort & Filter section
- Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only
Excel 2007 and 2010:
Use the Remove Duplicates menu option under the Data header.
Excel 2003:
Easy way:
- Make sure your data has a header
- Data --> Filter --> Advanced Filter
- Check Unique Records Only
- Select Copy to another location
- Click OK
Write a macro with the following code:
That gives you your distinct list. You may want to copy your list to another sheet first.
6,079 9 9 gold badges 34 34 silver badges 41 41 bronze badges Thanks! This is a proof that Stack Exchange is way better than any other source for technical information. All other google results are useless, and unranked. Also I wonder how experts-exchange surviveOr (a simple crude way):
and copy down. It just copies the first occurrence of each value across (in the row that it occurs in).
I use two main methods to get distinct values - the first is the invaluable Remove Duplicates tool as suggested by Nathan DeWitt.
The downside to Remove Duplicates is that you lose data. Despite the power of Ctrl + z you may not wish to do this for various reasons - eg you may have filters currently selected that make it quite messy to remove duplicates to get your distinct list of values.
The other method, not mentioned above is to use a Pivot Table.
Let's say this is your table and you want to get the distinct list of Platforms.
Insert a Pivot Table with the table as your data source.
Now select the Platform(s) column as your Row Labels .
Voila! A list of distinct values.
If you wish to tidy this up, you can switch off Field Headers and set Grand Totals to be Off for Rows and Columns .
In Excel, there are several ways to filter for unique values—or remove duplicate values:
To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.
Learn about filtering for unique values or removing duplicate valuesFiltering for unique values and removing duplicate values are two similar tasks, since the objective is to present a list of unique values. There is a critical difference, however: When you filter for unique values, the duplicate values are only hidden temporarily. However, removing duplicate values means that you are permanently deleting duplicate values.
A duplicate value is one in which all values in at least one row are identical to all of the values in another row. A comparison of duplicate values depends on the what appears in the cell—not the underlying value stored in the cell. For example, if you have the same date value in different cells, one formatted as "3/8/2006" and the other as "Mar 8, 2006", the values are unique.
Check before removing duplicates: Before removing duplicate values, it's a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.
Follow these steps:
Select the range of cells, or ensure that the active cell is in a table.
Click Data > Advanced (in the Sort & Filter group).
In the Advanced Filter popup box, do one of the following:
To filter the range of cells or table in place:
Click Filter the list, in-place.
To copy the results of the filter to another location:
Click Copy to another location.
In the Copy to box, enter a cell reference.
Alternatively, click Collapse Dialog to temporarily hide the popup window, select a cell on the worksheet, and then click Expand .
Check the Unique records only, then click OK.
The unique values from the range will copy to the new location.
When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.
Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
Follow these steps:
Select the range of cells, or ensure that the active cell is in a table.
On the Data tab, click Remove Duplicates (in the Data Tools group).
Do one or more of the following:
Under Columns, select one or more columns.
To quickly select all columns, click Select All.
To quickly clear all columns, click Unselect All.
If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.
Note: Data will be removed from all columns, even if you don't select all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in those columns, then the entire row will be removed, including other columns in the table or range.
Click OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain. Click OK to dismiss this message.
Undo the change by click Undo (or pressing Ctrl+Z on the keyboard).
Problems removing duplicates from outlined or subtotaled dataYou cannot remove duplicate values from outline data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.
Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.
Quick formatting
Follow these steps:
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the small arrow for Conditional Formatting, and then click Highlight Cells Rules, and select Duplicate Values.
Enter the values that you want to use, and then choose a format.
Advanced formatting
Follow these steps:
Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow for Conditional Formatting, and then click Manage Rules to display the Conditional Formatting Rules Manager popup window.
Do one of the following:
To add a conditional format, click New Rule to display the New Formatting Rule popup window.
To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. If necessary, choose another range of cells by clicking Collapse button in the Applies to popup window temporarily hide it. Choose a new range of cells on the worksheet, then expand the popup window again . Select the rule, and then click Edit rule to display the Edit Formatting Rule popup window.
Under Select a Rule Type, click Format only unique or duplicate values.
In the Format all list of Edit the Rule Description, choose either unique or duplicate.
Click Format to display the Format Cells popup window.
Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then click OK. You can choose more than one format. The formats that you select are displayed in the Preview panel.
In Excel for the web, you can remove duplicate values.
Remove duplicate values
When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.
Important: You can always click Undo to get back your data after you have removed the duplicates. That being said, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
Follow these steps:
Select the range of cells, or ensure that the active cell is in a table.
On the Data tab, click Remove Duplicates .
In the Remove Duplicates dialog box, unselect any columns where you don't want to remove duplicate values.
Note: Data will be removed from all columns, even if you don't select all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in Column1 and Column2, then the entire row will be removed, including data from Column3.
Click OK, and a message will appear to indicate how many duplicate values were removed. Click OK to dismiss this message.
Note: If you want to get back your data, simply click Undo (or press Ctrl+Z on the keyboard).
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Возвращает таблицу с одним столбцом, которая содержит уникальные значения из указанного столбца. Иными словами, повторяющиеся значения удаляются, и возвращаются только уникальные значения.
Эту функцию нельзя использовать для возвращения значений в ячейку или столбец на листе. Вместо этого функцию DISTINCT можно вложить в формулу, чтобы получить список уникальных значений, которые можно передать в другую функцию для подсчета их количества, суммирования или других операций.
Синтаксис
Параметры
| Термин | Определение |
|---|---|
| столбец | Столбец, из которого должны быть возвращены уникальные значения. Это может быть также выражение, возвращающее столбец. |
Возвращаемое значение
Столбец уникальных значений.
Remarks
На результаты функции DISTINCT влияет текущий контекст фильтра. Например, в приведенном ниже примере результаты формулы для создания меры будут меняться при фильтрации таблицы для отображения только определенного региона или периода времени.
Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).
Связанные функции
Существует другая версия функции DISTINCT, DISTINCT (таблица), которая возвращает таблицу, удаляя повторяющиеся строки из другой таблицы или выражения.
Функция VALUES похожа на функцию DISTINCT. Ее также можно использовать для получения списка уникальных значений, и, как правило, она возвращает те же результаты, что и DISTINCT. Однако в некоторых контекстах функция VALUES возвращает еще одно особое значение. Дополнительные сведения см. в разделе Функция VALUES.
Пример
Приведенная ниже формула подсчитывает количество уникальных клиентов, разместивших заказы через Интернет. В следующей таблице показаны возможные результаты при добавлении формулы в отчет.
Список значений, которые возвращает функция DISTINCT, нельзя вставить непосредственно в столбец. Вместо этого результаты функции DISTINCT необходимо передать в другую функцию, которая подсчитывает, фильтрует или суммирует значения из списка. Чтобы максимально упростить пример, в этом случае таблица с уникальными значениями была передана в функцию COUNTROWS.
| Метки строк | Accessories | Bikes | Clothing | Grand Total |
|---|---|---|---|---|
| 2005 | 1013 | 1013 | ||
| 2006 | 2677 | 2677 | ||
| 2007 г. | 6792 | 4 875 | 2867 | 9 309 |
| 2008 | 9 435 | 5451 | 4196 | 11377 |
| Grand Total | 15 114 | 9132 | 6 852 | 18484 |
Кроме того, обратите внимание, что результаты не являются аддитивными. То есть общее число уникальных клиентов в 2007 году не является суммой уникальных клиентов по категориям Accessories, Bikes и Clothing в этом году. Причина в том, что один и тот же клиент может быть учтен в нескольких категориях.
Читайте также: