Всех приветствую! Вопрос к большому лагерю помогающих на форуме. Озадачили по работе вопросом извлечения уникальных текстовых данных из массива с одновременным удалением дубликатов в столбцах. Можно ли это сделать формулой? Если нет, то вполне подойдет раскраска дубликатов через УФ. Сам пока в ступоре. Чтобы проще понять вопрос, сделал простой пример с хотелками и комментариями :). То, что должно получиться находится под зелёной чертой. В принципе, можно и макросом - объяснить начальникам что такое макрос и куда нажать, "чтоб оно считало", я смогу. Заранее спасибо за отклики и поимощь.
Всех приветствую! Вопрос к большому лагерю помогающих на форуме. Озадачили по работе вопросом извлечения уникальных текстовых данных из массива с одновременным удалением дубликатов в столбцах. Можно ли это сделать формулой? Если нет, то вполне подойдет раскраска дубликатов через УФ. Сам пока в ступоре. Чтобы проще понять вопрос, сделал простой пример с хотелками и комментариями :). То, что должно получиться находится под зелёной чертой. В принципе, можно и макросом - объяснить начальникам что такое макрос и куда нажать, "чтоб оно считало", я смогу. Заранее спасибо за отклики и поимощь.Che79
Оба варианта - очень круты и работают на большом реальном массиве! Спасибо вам большое!!! Если с формулой мне, в общем, понятно, то с УФ не очень. _Boroda_, Саша, если не затруднит, можете кратко пояснить прям по слагаемым множителям? Мне для саморазвития
Оба варианта - очень круты и работают на большом реальном массиве! Спасибо вам большое!!! Если с формулой мне, в общем, понятно, то с УФ не очень. _Boroda_, Саша, если не затруднит, можете кратко пояснить прям по слагаемым множителям? Мне для саморазвития Che79
Делай нормально и будет нормально!
Сообщение отредактировал Che79 - Понедельник, 02.10.2017, 23:21
Почему на форуме в пьяном виде? В глазах двоится, а то и троится? Меня здесь не много, я один, поэтому тем, кто отвечает на вопросы, не нужно обижать меня "выканьем" - мы здесь одна семья отвечальников
А по поводу пояснить - конечно Для простоты представим, что мы рассматриваем ячейку С40, тогда формула будет для нее интерпретироваться УФ-ом так (неважно, что в самом УФ она записывается ссылками на первую ячейку диапазона - В38 - для каждой ячейки внутри Excel ссылки без $ скользят:
1. ПОИСК(C40;$A$38:B$47) - ищет значение текущей ячейки в диапазоне справа (от строки 38 до строки 47). Или находит, или не находит. Если находит, то дает какое-то число (единицу в нашем случае), если не находит, то дает ошибку. Кстати, ПОИСК здесь не совсем верно использовать, ведь существуют имена, которые являются частью других имен. Пример Александр и Александра Тогда вернее было бы вот так написать
С новой формулой возвращаемся обратно к пункту 1 - 1. C40=$A$38:B$47 - если значение текущей ячейки равно какой-то ячейке диапазона справа (от строки 38 до строки 47), по получаем ИСТИНА, иначе - ЛОЖЬ. Итог - массив из ЛОЖЬ и, может быть, ИСТИНА. 2. 1/(п.1) - делим 1 на полученное в п.1. Получаем массив из ошибок деления на ноль и, может быть, единиц (там, где совпадение). 3. СЧЁТ(п.2) - СЧЁТ игнорирует ошибки и считает только числа. Если у нас есть совпадения (одно или несколько), то СЧЁТ даст какое-то число (1, 2, 3, ... - нам не так уж и важно какое, главное, что не ноль). А если совпадение не обнаружено, то СЧЁТ даст нам ноль 4. 5. 6. СЧЁТ(1/(C40=C$37:C39)) - аналогично пунктам 1-3, только ищем совпадения не справа, а сверху 7. Складываем полученное в п.3 и в п.6. Если или справа, или сверху (или и там, и там) совпадения есть, то эта сумма даст нам какое-то число. Неважно какое, главное, что не ноль 8. ДЛСТР(С40) - дает нам длину строки проверяемой ячейки. Если в ячейке ничего нет, то ДЛСТР даст ноль, иначе - какое-то число 9. Умножаем п.7 на п.8 - получаем какое-то число только в том случае, если и в п.7, и в п.8 у нас получились числа, отличные от нуля, что означает, что или справа, или сверху от текущей ячейки есть ее повтор и, при этом, сама ячейка не пустая 10. Для УФ признаком срабатывания является значение ИСТИНА. А в Excel ИСТИНА - это любое число, отличное от нуля. Следовательно, УФ сработает тогда, когда мы в нашей формуле получим любое ненулевое значение
Если что-то не совсем ясно пояснил, то могу дописать
Почему на форуме в пьяном виде? В глазах двоится, а то и троится? Меня здесь не много, я один, поэтому тем, кто отвечает на вопросы, не нужно обижать меня "выканьем" - мы здесь одна семья отвечальников
А по поводу пояснить - конечно Для простоты представим, что мы рассматриваем ячейку С40, тогда формула будет для нее интерпретироваться УФ-ом так (неважно, что в самом УФ она записывается ссылками на первую ячейку диапазона - В38 - для каждой ячейки внутри Excel ссылки без $ скользят:
1. ПОИСК(C40;$A$38:B$47) - ищет значение текущей ячейки в диапазоне справа (от строки 38 до строки 47). Или находит, или не находит. Если находит, то дает какое-то число (единицу в нашем случае), если не находит, то дает ошибку. Кстати, ПОИСК здесь не совсем верно использовать, ведь существуют имена, которые являются частью других имен. Пример Александр и Александра Тогда вернее было бы вот так написать
С новой формулой возвращаемся обратно к пункту 1 - 1. C40=$A$38:B$47 - если значение текущей ячейки равно какой-то ячейке диапазона справа (от строки 38 до строки 47), по получаем ИСТИНА, иначе - ЛОЖЬ. Итог - массив из ЛОЖЬ и, может быть, ИСТИНА. 2. 1/(п.1) - делим 1 на полученное в п.1. Получаем массив из ошибок деления на ноль и, может быть, единиц (там, где совпадение). 3. СЧЁТ(п.2) - СЧЁТ игнорирует ошибки и считает только числа. Если у нас есть совпадения (одно или несколько), то СЧЁТ даст какое-то число (1, 2, 3, ... - нам не так уж и важно какое, главное, что не ноль). А если совпадение не обнаружено, то СЧЁТ даст нам ноль 4. 5. 6. СЧЁТ(1/(C40=C$37:C39)) - аналогично пунктам 1-3, только ищем совпадения не справа, а сверху 7. Складываем полученное в п.3 и в п.6. Если или справа, или сверху (или и там, и там) совпадения есть, то эта сумма даст нам какое-то число. Неважно какое, главное, что не ноль 8. ДЛСТР(С40) - дает нам длину строки проверяемой ячейки. Если в ячейке ничего нет, то ДЛСТР даст ноль, иначе - какое-то число 9. Умножаем п.7 на п.8 - получаем какое-то число только в том случае, если и в п.7, и в п.8 у нас получились числа, отличные от нуля, что означает, что или справа, или сверху от текущей ячейки есть ее повтор и, при этом, сама ячейка не пустая 10. Для УФ признаком срабатывания является значение ИСТИНА. А в Excel ИСТИНА - это любое число, отличное от нуля. Следовательно, УФ сработает тогда, когда мы в нашей формуле получим любое ненулевое значение
Если что-то не совсем ясно пояснил, то могу дописать_Boroda_
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
В глазах двоится, а то и троится? Меня здесь не много, я один, поэтому тем, кто отвечает на вопросы, не нужно обижать меня "выканьем" - мы здесь одна семья отвечальников
