Страницы: 1
RSS
Поиск и подстановка номера заказа по его статусу формулой
 
Всем привет! В файле примере есть два листа Лист1 и Лист2. Необходима формула, которая на Лист1 будет подтягивать номера заказов с Лист2, по их статусу. На Лист2 умная таблица, с внешним подключением к БД, соответственно, на ней данные всегда обновляются и меняются, поэтому, формула должна видеть изменения на Лист2 и при появлении заказа с нужными статусами (нужных всего 3 статуса), показывать номера заказа на Лист1. Надеюсь доступно изъяснился, хотя, с этим проблема, уже не раз тут получал за это замечание от модераторов.

Спасибо!
Всем Добра, миру мир! Иностранцам Писа!
 
dredd812, добрый день.
Можно использовать PQ: вы создаете запрос к умной таблице на листе 2, внутри запроса оставляете только фильтры со статусами и выводите это на первый лист в виде новой умной таблицы или же в виде сводной.
Эту задачу можно также решить с помощью VBA.

А еще посмотрите тему про многоразовый ВПР, если прям формулами надо.
Изменено: Framed - 14.09.2019 18:29:07
 
Framed, Здравствуйте!  Я с огромным удовольствием воспользовался бы вашим предложение, если бы понимал, как делать запрос к умной таблице, со сводной тоже пробовал фокус, но увы, сводная скидывает условное форматирование, да и как сделать, что бы вместо кол-ва и суммы сводная ставила номер заказа и писала статус заказа, я увы не понимаю... Про vba даже говорить не буду, уровень моего знания в этой области равен примерно -100 в 15й степени)

С ВПР, тоже пытался воплотить в жизнь, но что то делаю не так и у меня не работает формула, вот собственно поэтому и обратился сюда...
Изменено: dredd812 - 14.09.2019 18:37:55
Всем Добра, миру мир! Иностранцам Писа!
 
См. вариант. Поменяйте значение на Лист2 и вернитесь на Лист1.
 
dredd812,

Скрытый текст

Если честно, я проморгал, что необходимо выгрузить только статусы и заметил это только сейчас; тем не менее, может быть код выше вам пригодится - он выгрузит вам на первый лист все данные строки исходной таблицы, если в соответствующей ячейке статуса была информация.
А так, Юрий вам уже ответил.
Изменено: Framed - 14.09.2019 20:00:13
 
Юрий М, Знаете что, Юрий?! Вот я по человечески попросил помочь с формулой... Разве нет? 8)  Зачем применять волшебство?!  :oops:

Я Вам безгранично благодарен!!! Спасибо, это конечно гениальное и очень шикарное решение, но... Я почему обратился за формулой, в формуле, я ещё хоть что-то могу понять, если поменяется таблица, столбцы, строки и т.д., мне снова придётся приходить сюда, отвлекать таких людей как вы, своими глупыми и точно на ваш взгляд банальными вопросами, потому как в VBA, я знаю только один код-макрорекордер и то на 2%. :(

Но решение, правда, гениальное!!!!  :idea:

P.S.: Обращение к Юрию, это не возмущение и не наезд, это, крайняя форма восхищения...Я вам белой завистью завидую, что для вас это несколько строк кода, мне бы так... Юр, уже не в первый раз, СПАСИБО!!!
Изменено: dredd812 - 14.09.2019 23:29:44
Всем Добра, миру мир! Иностранцам Писа!
 
Framed, Уважаемый, вы уж простите меня Бога ради, но не понимаю я в VBA, можно формулку, а? Если таковой нет, то и искать не буду... Вот, то что макрос делает эту задачу, я уже знаю, Уважаемый Юрий М, мне это показал, я конечно, как "умный" залез в VBA посмотрел код, ни чего не понял, закрыл его и просто сижу умиляюсь, как это всё работает, но увы...не понимаю...(((

Мыл посуду дома...И меня осенило, я понял, что такое PQ, но ни когда им не пользовался!)))
Изменено: dredd812 - 14.09.2019 23:30:00
Всем Добра, миру мир! Иностранцам Писа!
 
Да ничего гениального там нет ))
Если поменяется количество строк - не страшно: макрос сам найдёт последнюю строку. Если значения будут в других столбцах - поменять не сложно.
 
Юрий М, Юра, я подозреваю, что для вас это, как два пальца об асфальт... Но мне то как это сделать?)))) Я обычно, если имею файл с рабочим макросом, ставлю на него пароль, что бы даже самому его не знать, дабы не "поломать" к Кузькиной маме...), бывают написаны комментарии в макросе, зелеными буковками, вот там я ещё что-то могу пошурудить, но только сохранив в отдельный файл.
Изменено: dredd812 Frant - 14.09.2019 23:36:20
Всем Добра, миру мир! Иностранцам Писа!
 
так вам просто статусы собрать чтоли?  (массивка)
Код
=ЕСЛИОШИБКА(ИНДЕКС(Таблица1[Заказ];НАИМЕНЬШИЙ(ЕСЛИ(ЕТЕКСТ(Таблица1[Статус]);СТРОКА(Таблица1[Заказ])-1;"");СТРОКА(A1)));"")

бонусом PQ
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    remove = Table.SelectColumns(from,{"Заказ", "Статус"}),
    to = Table.SelectRows(remove, each ([Статус] <> null))
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, Шикарный вариант примера, скачал, да, всё работает, но я так понял, это и есть запрос через PQ?! Попробовал вставить формулу в А2, ни чего не сработало :(
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
dredd812 Frant написал:
это и есть запрос через PQ
там два варианта - на формулах (слева) и на PQ (справа)
Цитата
dredd812 Frant написал:
Попробовал вставить формулу в А2,
как вставляли? показывайте. Формулы массива вводятся сочетанием ctrl+shift+enter
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
Юрий М написал:
Если поменяется количество строк - не страшно: макрос сам найдёт последнюю строку. Если значения будут в других столбцах - поменять не сложно.
Юрий, добрый день! Вы не подскажите, потстрокам кода, какая за что отвечает, как раз так и получилось, что количество столбцов поменялось, да и отчет не с первого столбца... А как макрос на это переориентировать не пойму...

Спасибо!
Всем Добра, миру мир! Иностранцам Писа!
 
buchlotnik, грешен, прошу прощения... Не вводил формулу массива...Признаю. Варианты, изумительные! Надо с ними ночь переспать, а завтра, нет, во вторник на свежую голову понять и осознать. Примного благодарен!

К сожалению, столкнулся ещё с одной проблемой, в столбце со статусом, написана формула, которая собственно и выставляет статусы заказам, ваша формула принимает формулы за значения...  на рисунке 1 видно немер заказа, хотя (на рисунке 2) строка в столбце статус пустая...
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
dredd812 Frant написал:
какая за что отвечает
Привет! Вы про макрос из #4?
 
Юрий М, Да, да :)
Всем Добра, миру мир! Иностранцам Писа!
 
Поменял файл в #4 - добавил комментарии. Должно быть понятно ))
 
Наиогромнейшее спасибо!!! Завтра буду пробовать применить на деле :-) :idea:
Всем Добра, миру мир! Иностранцам Писа!
 
Юрий М, Добрый день!
Не подскажите, как дописать код, что бы не щёлкать лист туда-сюда, а что бы макрос работал при обновлении умной таблицы на 2м листе и что бы на первом листе не менялся формат ячеек и условное форматирование оставалось, переключил пару раз листы, шрифт сбрасывается, условное форматирование удаляется :-(
В остальном, код замечательно работает!
Заранее спасибо!
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
dredd812 Frant написал:
что бы не щёлкать лист туда-сюда,
Не понял смысла: ведь, чтобы посмотреть результат, Вам в любом случае нужно будет переключиться на другой лист. Так зачем её менять, если смотреть её не нужно? И макрос не будет срабатывать при любом изменении на листе с исходными данными...
Сделал с сохранением форматирования (см. файл). А условное форматирование просто сделайте сразу на Лист1 - зачем его каждый раз копировать?.
 
Цитата
Юрий М написал:
Не понял смысла: ведь, чтобы посмотреть результат,
Дело в том, что, на листе2, умная таблица, подключённая к бд, статусы меняются формулой а не руками, соответственно, на лист2 заглядывать нет необходимости, атм очень много данных, которые не нужны, ну а что-бы отработал макрос, приходится щелкать листами :-)

Цитата
Юрий М написал:
А условное форматирование просто сделайте сразу на Лист1 - зачем его каждый раз копировать?.
Да, я сразу его сделал на Лист1, но макрос при обновлении данных, сбивает диапазон условного форматирования, если у меня стои условное форматирование на диапазон А1:В50, то после отработки макроса, оно остаётся только на диапазоне, где есть номер заказа и его статус, ниже всё удаляется... А потом, если заказов становится больше, то УФ уже не срабатывает, т.к. макрос там всё почистил :-)
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
dredd812 Frant написал:
на лист2 заглядывать нет необходимости
Не заглядывайте )) А когда понадобится - увидите нужный результат. Вы сами подумайте: при многократном изменении формулами каждый раз будет запускаться макрос, который будет формировать новую таблицу на листе. Т.е. макрос отработал, а результат Вас не интересует. Тогда зачем его запускать? И учтите ещё один момент: придётся использовать событие Calculate, которое будет запускать макрос при любом (любом!) срабатывании любой формулы.
По поводу УФ - мой макрос его не трогает. Установите УФ на диапазон с запасом.
P.S. Сейчас посмотрел в новом варианте не исправил: макрос удаляет только значения. Форматирование текста остаётся. Измените строку так:
Код
Range(Cells(2, 1), Cells(LastRow + 1, 2)).Clear
 
Юрий М, У меня почему то меняется размер шрифта. По поводу листа2, наверное я не правильно выражаюсь, дело в том, что, в умной таблице внешнее подключение к БД, обновление данного подключения настроено на каждые 3 минуты, если есть изменение в статусе заказа, соответственно формула и меняет этот статус на нужный мне, но если не переходить с листа на лист, макрос не увидит данное изменение и не поменяет инфо на 1м листе.
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
dredd812 Frant написал:
У меня почему то меняется размер шрифта
Какой размер шрифта был на втором листе - такой и получите на первом.
Цитата
dredd812 Frant написал:
макрос не увидит данное изменение и не поменяет инфо на 1м листе.
Не поменяет, и что? Вы же всё равное этого не увидите. А вот когда переключитесь на этот лист - увидите последнее изменение. Какой смысл менять каждые 3 минуты, если Вы всё равно не смотрите (не переходите) на итоговый лист?
Если не убедил, то используйте событие Calculate на втором листе. Но это просто нелепо.
 
Простите меня, Бога ради, лист1 и есть окончательный и информативный, информация должна обновляться без помощи человека, она отображается на экране, у которого нет ни клавиатуры, не мышки... на листе2, всё работает без макроса, там умная таблица с внешними данными, она обновляется 1 раз в 3 минуты, я поэтому и хочу сделать так, что бы макрос запускался на листе1, при изменениях на листе2.
Всем Добра, миру мир! Иностранцам Писа!
 
Цитата
Юрий М написал:
используйте событие Calculate на втором листе
 
Уважаемые знатоки, снова прошу помочь с формулой... Но именно с формулой, потому как, из файла убрано много данных, которые просто утяжеляют его да и тут они не нужны, пример во вложении.
Всем Добра, миру мир! Иностранцам Писа!
 
=IF(COUNTIF($A$2:$A$14;O2)-COUNTIFS($A$2:$A$14;O2;$F$2:$F$14;"Готов к выдаче");IF(COUNTIF($A$2:$A$14;O2)-COUNTIFS($A$2:$A$14;O2;$F$2:$F$14;"Ожидание");"Собирается";"Ожидание");"Готов к выдаче")
По вопросам из тем форума, личку не читаю.
 
БМВ,

Михаил, добрый день!
Спасибо, пошёл переводить)))
Изменено: dredd812 Frant - 17.11.2019 16:23:53
Всем Добра, миру мир! Иностранцам Писа!
 
После Вас все время чистить? Теперь вернитесь и САМИ удалите бездумное цитирование!
Страницы: 1
Наверх