Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
поиск уникального значения(много данных + ряд условий), банально - поиск уникальных значений
 
Доброго времени суток,

Господа прошу помочь, сижу на работе 3-юю неделю. Excel вроде знаю норм, а написать формулы не получается. Название темы звучит в сотый раз, но не так все просто  , прочитайте пжлста до конца.

Есть вкладка данные. Есть вкладка итог. Надо что бы в итоге были лишь уникальные номера. Плюс есть ряд условий: дата, год, объект.
1. Прошу помочь только формулами екселя, не предлагайте только макросы и  через форматирование
2. Вкладка данные: данные могут быть, а могут и не быть - "столбец чистые данные"(сам набросал, но проблема в данных через слэш)
3. В итоге не должны попадать значения которые шли через слэш
4. Прошу выложить максимально простую, если возможно. Так как в данных может быть десятки тысяч строк
5. Должны в итоге быть только уникальные значения

Я построил на работе(блин у нас политика коммерческой тайны, выслать не могу). Построил через наименьшее+индекс+ряд условий и в массиве, но дубли хоть че делай не убрать.
На будущее чтобы не обращаться повторно - вышлите, если сделаете, вариант где будут отображаться и данные через слешь, но разбитые уже на две строки, напрмер: в данных есть строчка "дор0008/пор3654", в итоге что бы это было "2" строки как "8" так и "3654"


Помогите пжлста !
 
В какие столбцы нужно вставить результат?
 
Результат вкладка итог, номера столбец
 
Нужно вставить данные на лист "итог", начиная с ячейки "B9"?
 
да, верно
 
На листе "итог", в строке 3 есть данные и есть данные в столбцах P:R. Эти данные нужно учитывать?
 
Darkvisor,  c учетом
Цитата
Darkvisor написал:
в данных может быть десятки тысяч строк
формульные варианты ведут в тупик, особенно с учетом второго требования.
Цитата
Darkvisor написал:
в итоге что бы это было "2" строки как "8" так и "3654"
а вот PowerQuery в данном случае сделает все быстро и надежно. Без этого условия, можно и на просто MS Query сделать.

файл положите в с:\temp, или надо разово путь подправить.
 
Цитата
Karataev написал: На листе "итог", в строке 3 есть данные и есть данные в столбцах P:R. Эти данные нужно учитывать
Эти столбцы для постройки 3-тей строки. Чисто выборка, того что отображать под "номер"

Цитата
в данных может быть десятки тысяч строкформульные варианты ведут в тупик, особенно с учетом второго требования.
Я сог построить как и писал выше, через "Построил через наименьшее+индекс+ряд условий и в массиве", но как и писал упёрся в дубли. А индекс в индексе это норм, или уже перебор? :)

Цитата
Без этого условия, можно и на просто MS Query сделать.файл положите в с:\temp, или надо разово путь подправить.
Понял, тогда отпадает.
Я как понял Вы построили как раз на PQ, а можно без него? чисто формулами.

Не пояснил почему не подходит вариант с доп файлами: это файл будет использоваться разными коллегами + доп файл мне не "протащить" на работу. Работаю как в оборонке ей богу
 
то что сделано  - MSQuery, Механизм встроенный с давних времен. Доп. файлов или макросов не требует. Единственное - привязывается паразит к пути до файла источника абсолютному.

Формулами от дублей избавится можно, но вот как это будет при тысячах строк данных - я не могу сказать. Но точно нужно диапазон данных расчетным делать
Изменено: БМВ - 11 Фев 2019 21:28:47
 
а где эта привязка? что бы у своих там настроить так же.
Сделай пжлста ещё формулами, очень прошу
 
Darkvisor, у нас на форуме к незнакомым людям принято обращаться на Вы.
 
Darkvisor, из листа "данные" из какого столбца нужно скопировать данные на лист "Итог" в столбец "B"?
 
Цитата
Юрий М написал:
к незнакомым людям
так то к людям. А медведь то не … :-)

Формулы выше,

Connection edit ниже
 
Цитата
Юрий М написал:
Darkvisor , у нас на форуме к незнакомым людям принято обращаться на Вы.
Извиняюсь, не собирался никому хамить. Глупо хамить когда тебе помогают

1. БМВ Ваш "замут" с датой(начальная и конечная), не приметил сразу. Блин это надо запомнить, объём файла можно уменьшить из за формул - отдельно спасибо !
2. СЧЁТЕСЛИ($B$8:B8;данные!$C$1:$C$66) - никак не пойму для чего это.
Цитата
Karataev написал:
Darkvisor, из листа "данные" из какого столбца нужно скопировать данные на лист "Итог" в столбец "B"?
Из листа "данные D:D" в "итог" B9 и ниже, условие тут же - выше
Изменено: Darkvisor - 12 Фев 2019 13:35:33
 
Цитата
Darkvisor написал: 1. Ваш "замут" с датой
это не замут, это оптимизация,

Цитата
объём файла(из за формул) - отдельно спасибо !
не понял про какой объем

Цитата
2. СЧЁТЕСЛИ($B$8:B8;данные!$C$1:$C$66)
вы просили удалить дубликаты. В примере их нет.
 
с допстолбцами
 
Цитата
Darkvisor написал:
Единственное - привязывается паразит к пути до файла источника абсолютному.
Михаил, а Вы видели этот прием?
Параметризация путей к данным в Power Query
Изменено: IKor - 12 Фев 2019 10:50:46
 
Цитата
IKor написал:
Михаил, а Вы видели этот прием?
Коллега, у медведя даже на картинках представлено, что он использовал. Power Query <> Microsoft Query :) В Microsoft Query строка подключения к источнику статическая, увы, можно изменять только макросом.
 
IKor,  Если это мне, так я не на PQ делал.

Darkvisor,  посмотрел ,ну можно сделать формулой даже с разделением по строка того что через слэш, я так понимаю буквенный префикс всегда 3 буквы и нужно понимать сколько слэшей может быть. Но на маленьком примере работать будет, на большом - есть сомнения.
Смогу посмотреть только вечером, надо части соединить воедино, но очки дома забыл работаю как крот.
 
У БМВ менее массивным можно сделать вариант, так как нет дополнительных переменных, зато тормозит сильно по продлении. А у Артура вариант думаю должен быстрее работать, но есть дополнительные колонки. Для меня массивы не просты в использовании. Я конечно наглею, понимаю, но все же сможете потратить время и подправить?
Не ненадо не утруждайся. Я взял Ваш файл, подставил свои данные. Блин жуть как медленно работает если продляю на 200-300 ячеек. Скорее всего это из-за массива, который в последней строчке получился $B$11:B90 это я продлил на 80 ячеек. Мало того ещё и обращается в $D$1:$D$10000. То есть по другому у просматривает 79 значений в десятитысячном диапазоне. Короче жесть. Может можно переделать что бы обращался не 79 раз? Капец горю на работе, презентация завтра

У Вас менее массивным можно сделать вариант, так как нет дополнительных переменных, зато тормозит сильно по продлении. А у Артура вариант думаю должен быстрее работать, но есть дополнительные колонки. Для меня массивы не просты в использовании. Я конечно наглею, понимаю, но все же сможете потратить время и подправить?

Цитата
artyrH написал: с допстолбцами
Тоже спасибо Вам, рабочий вариант. Из минусов только дополнительные колонки в вкладке данные, их можно реализовать так же как это сделал BMW? Сам бы легко переделал но не знаком с такими как агрегат, двойные минусы, и ПРОПНАЧ вообще первый раз вижу.
господа извиняюсь перед всеми если что. Сейчас с телефона пишу
 
Artyr и всё-таки Ваш вариант, к сожалению, не подходит так как дополниельдопо колонки работать не будут. Так как у меня настроены подключения через power pivot, а он когда обновляет данные с источника, то не продляется рядом формулы. Я хоть и решил эту проблему, путем постройкой ещё одной таблицы, там все продляется. Сам понимаешь влияет на вес файла
 
Просто ради интереса, практическое применение при большом объеме нулевое

_2 - с допстолбцами для деления по слэшу
_3 - без них

Darkvisor,  я всеж рекомендую QUERY
Изменено: БМВ - 12 Фев 2019 19:42:32 (файлы более корректные ниже)
 
Доброе время суток.
Цитата
Darkvisor написал:
Так как у меня настроены подключения через power pivot
Так где есть Power Pivot, как правило, есть и Power Query - вот по существу и простое решение задачи, если уж по каким-то странным причинам не устраивает Microsoft Query.
 
а что сделать нужно с query?  В данном случае.
Цитата
БМВ написал: Просто ради интереса, практическое применение при большом объеме нулевое_2 - с допстолбцами для деления по слэшу_3 - без них
Посмотрел, я сломал свой Моск. Это через чур для меня. Как происходит подмена названий колонок, я так и не понял. Ваш вариант рабочий. Как работает Ваша доп табличка(охристые цветом) я вообще не понял(((. И все же вариант Артура и БМВ можно объединить?(писал выше) Взять лучшее от обоих. Если дробь, то исключать! И пожалуйста попроще, обратившись на форум я понял! Я неандертальцев в екселе
Изменено: Darkvisor - 12 Фев 2019 15:40:45
 
Цитата
Андрей VG написал:
не устраивает Microsoft Query
Андрей, приветствую. MS там норм до тех пор пока нет значений через слэш. Я не делал выделеония цифр и из плюсов это автообновление при смене критериев без кода.

На самом деле, если объединить решение, а именно MS query занимается фильтром (а если данные уже берутся из источника через запрос, то можно и напрямую брать), на отдельном листе. Так как на выходе умная таблица, то оргаизовать формулы для выделения номеров не проблема по образу _2. собственно и уже на этот результат натравить формулы для сбора итога - будет достаточно просто, так как объем обработки будет после фильтра в разы меньше.

Разместить С:\TEMP

P.S.  :D
Цитата
Darkvisor написал:
Excel вроде знаю норм,
Цитата
Darkvisor написал:
И пожалуйста попроще, обратившись на форум я понял! Я неандертальцев в екселе
Изменено: БМВ - 12 Фев 2019 15:47:11
 
Цитата
БМВ написал:
Разместить С:\TEMP
а что разместить? В этом варианте толькотпоявилось подключение. Да вижу обращение в темпы, а этот файл где теперь взять. И заранее спрошу: да, если это необходимо, то я могу разместить на веб ресурсе компании некий файл, для работы отчета, только подскажите где корректируется путь?  В строке подключения, тупо C:\temp? И все?
 
Darkvisor,  Запрос идет к таблице самого файла, но так уж повелось, что путь до него надо указать. то есть сохраните файл переименуйте  и соединении укажите полный путь к нему, с учетом имени файла.
 
Цитата
БМВ написал:
Запрос идет к таблице самого файла, но так уж повелось, что путь до него надо указать. то есть сохраните файл переименуйте  и соединении укажите полный путь к нему, с учетом имени файла.
то есть: если его выложу на веб ресурс компании-> возьму путь общий путь до файла на ресурсе -> пропишу к нему же-> а именно вот это место:
DSN=Excel Files;DBQ=C:\temp\Copy of пример641_4.xlsx;DefaultDir=C:\temp;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
->сохраню-> все?
теперь все кто заходят не видят эту ошибку и он рабочий?
Изменено: Darkvisor - 12 Фев 2019 19:07:29
 
Darkvisor, хм, WEB немного напрягает. Дело в том, что при открытии книги происходит скачивание во временную папку, и предположить куда и под каким именем сложно. Если есть сетевой ресурс SMB, FTP …. то тогда можно туда, при этом , можно разнести данные и этот отчет по разным книгам если есть желание.

Чуть ускорил варианту _2 и _3

Вопрос, а в данных строки с пустыми номерами в каком количестве при ваших выгрузках? Если их много, то удалить бы до обработки. Считать проще формулам.
Изменено: БМВ - 12 Фев 2019 19:36:42
 
Цитата
БМВ написал:
Darkvisor , хм, WEB немного напрягает. Дело в том, что при открытии книги происходит скачивание во временную папку, и предположить куда и под каким именем сложно. Если есть сетевой ресурс SMB, FTP …. то тогда можно туда, при этом , можно разнести данные и этот отчет по разным книгам если есть желание.
Не особо понял про скачивание, причем здесь скачивание, если речь вроде идёт об обращении к доп файлу. Просто сам отчет будет лежать как раз таки на Веб ресурсе, для его общего использования, где я и замкну путём на него.
Веб ресурс у нас норм работает, связывал так уже разные файлы, работало стабильно.
Все же правильно я думал? там только пути менять и все?
Цитата
БМВ написал:
Вопрос, а в данных строки с пустыми номерами в каком количестве при ваших выгрузках? Если их много, то удалить бы до обработки. Считать проще формулам.
По истечению времени их не будет совсем. Как бы тогда всё выглядело?

Извините что только сейчас говорю: Слэшь всегда находится в одном месте(4-ым знаком)
И ещё вопрос, наверно главный - Уважаемый! подскажите, как вы сменили/привязали названия шапок таблиц, например числовое поле(NumberArray) или поле объекта(ObjArray)? к тому же Rowlnd не понимаю на что влияет- удаляю всё ломается, с ним работает :) Где это всё прописано, покажите пжлста? Я не просто пишу на форум сделайте мне и всё, а я буду в носу ковырять. Хочу сам что то уметь и разбираться(я так привык),а не клянчить.
Страницы: 1 2 След.
Читают тему (гостей: 1)
Наверх