Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

13 Июль 2020, 03:28 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Часто задаваемые вопросы по LibreOffice и Apache OpenOffice.org
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Функция обработки дублей в Автофильтре  (Прочитано 860 раз)
0 Пользователей и 1 Гость смотрят эту тему.
luu
Участник
**
Offline Offline

Сообщений: 48


« Стартовое сообщение: 25 Январь 2020, 18:42 »

Привет всем!
Не уверен, что правильно формулирую вопрос в теме.
Есть ли какой-то вариант решения вот такой задачи:

Есть столбец с данными, к которому применен автофильтр. Пользователь активно с ним работает.
Есть ли какой-то способ в отдельной ячейке выводить значение, по которому сделана выборка автофильтра, если оно единственное и произвольные символы или ошибку в том случае, если в выборке больше одного значения?
В примере показаны значения типа "Дата", но нужно общее решение, для текста в том числе. Смотрел в сторону функции AGGREGATE, но так и не смог придумать, как прикрутить её для решения

Вот пример:
Столбец с датами в разнобой. Автофильтр не используется. --> В ячейке "ХХХ".
Выборка автофильтра по одной дате - 08.05.19 --> В ячейке отображается эта дата "08.05.19"

--->

Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 213


« Ответ #1: 25 Январь 2020, 20:22 »

Если на одну дату (дату-время) м.б. только одна строка (запись), это значит что данный столбец по-сути является "индексом" таблицы, её "первичным ключом". Отловить дубликат тут проще простого, для этого в ячейке A1 нужно написать формулу =МОДА(A1:A100000). Как только появляется дубликат - ячейку A1 можно условным форматированием сделать красной и со шрифтом размером 40, и если закрепить заголовок - не заметить такое "выпрыг" размера строки оператору - нереально (в отличие от мало приметного XXX).

Но МОДА работает только по числам и датам. Если нужно ловить дубликаты по тексту - это будет накладнее, а на 100000 строк будет аццки тормозить. На Форуме проскакивало несколько UDF-функций, возвращающих список дубликатов из видимых строк.

Но, во-первых, сдается мне что это нормальная ситуация (дубликаты в тексте), а во-вторых, - ну хватает всему миру в базах данных - одного столбца-индекса - значит и вам должно его хватить.
Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
Bigor
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 931


« Ответ #2: 25 Январь 2020, 22:09 »

Мне кажется, задача не имеет решения формулами. Т.к. они обрабатывают все ячейки, а не только видимые.

ps был не прав Грустный
Код:
=IF(SUBTOTAL(105;A2:A24)=SUBTOTAL(104;A2:A24);SUBTOTAL(104;A2:A24);"XXXX")

правда вместо текста возвращает 0, и с текстом вообще не работает Грустный
« Последнее редактирование: 25 Январь 2020, 22:27 от Bigor » Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
luu
Участник
**
Offline Offline

Сообщений: 48


« Ответ #3: 26 Январь 2020, 07:26 »

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

Насколько мне удалось понять, эти функции могут работать как со всем диапазоном, так и с результатами выборки фильтра, т.е. аргумент функции, указывающий по какой внутренней функции считать значения, работает примерно так:
Просто число функции обрабатывает весь диапазон, а число функции + 100 обрабатывает диапазон без скрытых ячеек:
1. ИТОГ(5;A2:A24)
- находит минимальное значение из всего диапазона (функция под кодом 5)
2. ИТОГ(105;A2:A24)
- находит минимальное значение из всего диапазона за вычетом скрытых фильтром ячеек (функция под кодом 5+100 = 105)

Пока решил попробовать на отдельном столбце высчитывать кол-во символов в ячейках с текстовыми данными и сравнивать их. Но это неподходящий вариант, т.к. в таком случае часто бывыает так, что кол-во символов совпадает. Более того, даже отдельные буквы и т.д. посчитать не вариант, т.к. могут встречаться пары записей типа: "Бетон-Плита" и "Плита-Бетон". И их необходимо разделять как разные
« Последнее редактирование: 26 Январь 2020, 10:27 от luu » Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 213


« Ответ #4: 26 Январь 2020, 12:24 »

luu - ну вот наконец-то вы озвучили саму проблему. Вам нужно добиться запрета в автофильтре двух и более галок (постоянно или в опр. момент). Для этого поищите на Форуме функцию на Basic, отображающую в ячейке набор текущих условий автофильтра. Такая вроде была. Для Excel такая есть (файл вложен), работает сносно, если не залазить за >10k уникальных значений:
Код:
Function GetCriteria(r As Range)
    On Error Resume Next
    GetCriteria = "Все"
    With r.Worksheet.AutoFilter
        FilterIndex = r.Column - .Range.Column + 1
        GetCriteria = .Filters(FilterIndex).Criteria1
        GetCriteria = GetCriteria & .Filters(FilterIndex).Criteria2
    End With
End Function

Функция в Calc с опциями совместимости - не работает, надо переписывать. Или найти готовую.

* АвтофильтрНетДвумГалкам.xls (34.5 Кб - загружено 1 раз.)
« Последнее редактирование: 26 Январь 2020, 12:27 от economist » Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
luu
Участник
**
Offline Offline

Сообщений: 48


« Ответ #5: 26 Январь 2020, 14:26 »

Не могу даже посмотреть, как это работает - у меня нет Excel'я - только Calc.
И все-таки не совсем то, что мне нужно. Мне хотелось бы оставить возможность пользователю фильтровать как угодно, для первоначальной работы с данными и возможности сравнивать их. Но когда он наконец сделает выбор в пользу одного параметра в столбце - сообщить ему об этом.

Но тем не менее спасибо, попробую разобраться, может получится переписать
Записан
luu
Участник
**
Offline Offline

Сообщений: 48


« Ответ #6: 26 Январь 2020, 14:56 »

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

* Дата_тест.ods (26.02 Кб - загружено 2 раз.)
Записан
Bigor
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 931


« Ответ #7: 26 Январь 2020, 15:02 »

а текстовое значение выбранных автофильтром значений
Ну это элементарно, через index (match)
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
luu
Участник
**
Offline Offline

Сообщений: 48


« Ответ #8: 26 Январь 2020, 19:55 »

а текстовое значение выбранных автофильтром значений
Ну это элементарно, через index (match)
Честно говоря, не понимаю.
В структуре Index(match()), а точнее в MATCH(WHAT;WHERE;TYPE) что указывать в WHAT? Точнее, как дать на это ссылку?


Сорян! Допёр  Афро
« Последнее редактирование: 26 Январь 2020, 20:08 от luu » Записан
Bigor
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 931


« Ответ #9: 26 Январь 2020, 20:09 »

для направления
Код:
=IF(SUBTOTAL(105;D3:D97)=SUBTOTAL(104;D3:D97);INDEX(B:B;MATCH(SUBTOTAL(104;D3:D97);D3:D97;0)+2);"XXXX")
аналогично сделайте для компаний.

справку по функциям, если с английским не очень, проще читать от excel Улыбка
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
luu
Участник
**
Offline Offline

Сообщений: 48


« Ответ #10: 26 Январь 2020, 20:17 »

Да-да, так и сделал!

Спасибо за подсказку!  Всё хорошо

* Дата_тест.ods (34.54 Кб - загружено 2 раз.)
Записан
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!