Скрытие повторяющихся данных в отчете
При составлении простых отчетов, содержащих отсортированные данные, могут возникать ситуации, когда в нескольких строках отчета в одном поле повторяются одни и те же данные, например наименование клиента, адрес и пр. Очевидно, что отчет будет выглядеть лучше, если данные в поле не будут повторяться, а будут печататься каждый раз только в первой строке. Каждое поле в области данных отчета имеет свойство Не выводить повторы (Hide Dublicates). Если установить это свойство равным Да (Yes) для поля, содержащего повторяющиеся данные, данные повторяться не будут. Для сравнения на рис. 10.22 и 10.23 приводится один и тот же отчет с разными значениями свойства Не выводить повторы (Hide Dublicates) для поля «Название» (CompanyName).
Отбор повторяющихся значений в EXCEL
Из исходной таблицы с повторяющимися значениями отберем только те значения, которые имеют повторы. Теперь при добавлении новых значений в исходный список, новый список будет автоматически содержать только те значения, которые повторяются.
Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний. В некоторых ячейках исходного списка имеются повторы.
Задача
Создадим новый список, который содержит только те значения, которые повторяются. Дополнительное условие: при добавлении новых значений в исходный список, новый список должен автоматически включать только повторяющиеся значения.
Решение
Список значений, которые повторяются, создадим в столбце B с помощью формулы массива . (см. файл примера ).
Введем в ячейку B5 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0) );»»)
Вместо ENTER нужно нажать CTRL + SHIFT + ENTER .
ИсхСписок — это Динамический диапазон (ссылка на исходный список в столбце А ).
Скопируйте формулу вниз с помощью Маркера заполнения (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).
В файле примера также приведены перечни, содержащие неповторяющиеся значения и уникальные значения.
С помощью Условного форматирования в исходном списке можно выделить повторяющиеся значения.
Тестируем
1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)
2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название
3. Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)
4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено
5. Список повторяющихся значений (столбец B) автоматически будет обновлен, чтобы включить новое название.
СОВЕТ:
Созданный список повторяющихся значений является динамическим, т.е. при добавлении новых значений в исходный список, новый список будет автоматически обновляться. Это очень ресурсоемкая задача и годится для небольших списков 50-100 значений. Если динамический список не нужен, то можно пойти другим путем: см. статью Отбор повторяющихся значений с помощью фильтра .
Скрытие повторяющихся данных в отчете
При составлении простых отчетов, содержащих отсортированные данные, могут возникать ситуации, когда в нескольких строках отчета в одном поле повторяются одни и те же данные, например наименование клиента, адрес и пр. Очевидно, что отчет будет выглядеть лучше, если данные в поле не будут повторяться, а будут печататься каждый раз только в первой строке. Каждое поле в области данных отчета имеет свойство Не выводить повторы (Hide Dublicates). Если установить это свойство равным Да (Yes) для поля, содержащего повторяющиеся данные, данные повторяться не будут. Для сравнения на рис. 10.22 и 10.23 приводится один и тот же отчет с разными значениями свойства Не выводить повторы (Hide Dublicates) для поля "Название" (CompanyName).
Рис. 10.22. Свойство Не выводить повторы равно Нет
Рис. 10.23. Свойство Не выводить повторы равно Да
Рис. 10.24. Помещение группирующего поля в область данных отчета
Если в отчете используется группировка, то не всегда группирующее поле выносится в отдельный раздел (заголовок или примечание группы). Иногда его оставляют в области данных отчета, вынося итоговые значения в раздел примечания группы. В этом случае также используется свойство Не выводить повторы (Hide Dublicates), для группирующего поля, чтобы не выводить повторяющиеся значения. Отчет при этом будет выглядеть так, как это показано на рис. 10.24.