Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 След.
Условное форматирование. Сравнение дат и заполненность ячеек строк
 
evghtcone, вроде как по Вашему условию.
Строки с 8 по 10 можете удалить. (дублируют Ваши данные только с авто-столбцом),
тогда в УФ нужно будет заменить 11 на требуемый номер строки:
Код
=ДВССЫЛ(ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ();4);1;"")&11)=1
сократил немного код (Вариант 2)
Изменено: ymal_qeb - 30.11.2020 14:14:51
Поиск ближайшего значения для требуемой даты в Power Query
 
PooHkrd, Андрей VG, спасибо Ваши варианты работают как надо.
Поиск ближайшего значения для требуемой даты в Power Query
 
Добрый день, уважаемые форумчане.
В диапазоне A2:C12 содержатся данные для объединения.
В примере 1 если объединять по 2 столбцам (Дата;ИНН) данные из Таблица2 и Таблица1 и будет точное совпадение то данные объединятся. (Результат: Таблица2_2)
В примере 2 в Таблице3 нет полного сопоставления строки с Таблица1 и данные из столбца [Наименование], не подтягиваются. (Результат: Таблица3_2)
Как можно объединить данные по нечеткому совпадения столбца [Дата] (найти ближайшее наибольшее значение ниже даты указанной в столбце I:I) и точному совпадению столбца [ИНН].
Можно ли как-то добиться результата как в диапазоне M2:O4
файл примера во вложении.

----------------
вроде как получило, но не знаю на сколько это надежно и как быстро будет работать на большом диапазоне данных (пример 8.1), может есть более оптимальный вариант решения.

Заранее спасибо.
Изменено: ymal_qeb - 23.07.2019 12:55:38
Разделить и разнести данные таблицы по общему разделителю в Power Query
 
Теперь все понятно, еще раз спасибо.
Разделить и разнести данные таблицы по общему разделителю в Power Query
 
Андрей VG, PooHkrd, спасибо оба варианта работают.
Осталось разобраться только как они работают :)  
Разделить и разнести данные таблицы по общему разделителю в Power Query
 
Добрый день, уважаемые форумчане.
В диапазоне А2:Е8 содержатся исходные данные.
Как можно разделить данные на разные строки по разделителю "*", "/", в нескольких столбцах, чтобы не создавалось дубликатов при разделении данных.
Можно ли как-то разнести данные как в диапазоне М2:М8
файл примера во вложении.

Заранее спасибо.
PQ. Несколько форматов данных (в различных вариациях) в одном столбце
 
PooHkrd, спасибо, постараюсь пересмотреть модель и оставить только даты.
PQ. Несколько форматов данных (в различных вариациях) в одном столбце
 
Dark1589, Можете подсказать а как стоит?
PQ. Несколько форматов данных (в различных вариациях) в одном столбце
 
Добрый день, уважаемые форумчане.

Можно ли как-то в PQ при выгрузке данных на лист совместить несколько различных форматов данных.
--------------------------
Если исходные данные завернуть в Table.TransformColumns и задать формат (type date), то данные отличимые от формата дата после выгрузки не выгружаются, а в области запросы книги напротив запроса выводится ошибка, (хотя если открыть запрос, то ошибок нет).
А если завернуть данные в Table.TransformColumns и задать формат (type any), то данные выводятся нормально, но дата выводится как число (а не привычного формата для даты)
--------------------------
Или такого вида как в результате можно достичь только с помощью макроса?

Заранее спасибо.
Нумерация строк в Power Query (новая дата +1, для повторяющихся дат +1 к предыдущему)
 
Цитата
artyrH написал:
совсем запутался, но сделал)
Вот это да.   8-0
Большое спасибо, artyrH.
Хотя после предложенных вариантов PooHkrd, (в посте 4), переосмыслил свои изначальную концепцию, остановился на варианте, в столбце (Индекс2), новая дата с 1 и потом +1.
Но все же спасибо за Ваши старания, artyrH.
Нумерация строк в Power Query (новая дата +1, для повторяющихся дат +1 к предыдущему)
 
PooHkrd, большое спасибо.
Изменено: ymal_qeb - 04.03.2019 13:39:14
Нумерация строк в Power Query (новая дата +1, для повторяющихся дат +1 к предыдущему)
 
PooHkrd, спасибо
Цитата
ymal_qeb написал:
если дата уникальная, то начать нумерацию с 1 и прибавлять плюс один для последующих уникальных дат, а
это решает эту часть задачи, а как добиться:
Цитата
ymal_qeb написал:
если дата повторяющаяся, то прибавить плюс один к предыдущему значению это даты.
Нумерация строк в Power Query (новая дата +1, для повторяющихся дат +1 к предыдущему)
 
Добрый день, уважаемые форумчане.
Как пронумеровать строки по принципу:
если дата уникальная, то начать нумерацию с 1 и прибавлять плюс один для последующих уникальных дат, а
если дата повторяющаяся, то прибавить плюс один к предыдущему значению это даты.
---------
Через Excel, решение нашел через: СЧЁТЕСЛИ, МАКС. (в диапазоне - E:F)
---------
Пробовал в PQ принцип - индекс + ссылки на предыдущую ячейку Таблица[ячейка]{[Index] - 1}, но возникает ошибка (Expression.Error: В ходе вычислений была обнаружена циклическая ссылка.), и дальнейшее редактирование невозможно.
можете подсказать, где я допустил ошибку и как можно достичь результата как в диапазоне E:F.

Заранее спасибо.
Отбор последнего совпадающего значения при объединении данных в Power Query
 
PooHkrd, спасибо.
Ваш запрос обновляется гораздо быстрее - при первом 7,5 при повторном 4-5 сек.
Изменено: ymal_qeb - 12.02.2019 16:41:53
Отбор последнего совпадающего значения при объединении данных в Power Query
 
PooHkrd, простите (просто в исходнике есть дата и исходя из нее формируется номер), машинально ответил.
если как в примере но по максимальному значению второй таблицы колонки №
----
подскажите, а Table.Join, тоже начинает тупить на больших массивах?
Изменено: ymal_qeb - 12.02.2019 16:32:07
Отбор последнего совпадающего значения при объединении данных в Power Query
 
Цитата
PooHkrd написал:
последнее найденной значений правой таблицы?
Да.
Изменено: ymal_qeb - 12.02.2019 16:32:30
Отбор последнего совпадающего значения при объединении данных в Power Query
 
Добрый день, уважаемые форумчане.
Пытался найти аналог ИНДЕКС+ПОИСПОЗ excel, в PQ.
Нашел способ это можно сделать, через объединить, но заметил странность (продемонстрирована на Листе 2 [строка 2-3], если в правой таблице повторяется значение, то он дублирует значение левой строки и добавляет 2 или последующее значение правой).
Использовал JoinKind.LeftOuter, для добавления данных к левой таблице из правой.
Как можно оставить оставить только последнее найденной значений правой таблицы?
На Листе 3, удалось достичь желаемого эффекта (на 100 тысяч строк у меня обновилось за 16,5 сек., при повторных обновлениях результат 9-10 сек.)
но может это можно сделать как-то более правильно.
-------
Можете подсказать отличия Table.NestedJoin и Table.Join?, по справочнику не совсем понятны их различия.

Заранее спасибо.
Изменено: ymal_qeb - 12.02.2019 15:09:20
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, да все верно.
------
в правильную сторону я мыслил, в примере поста 27, только Ваш вариант намного короче и наверное обновляется  по быстрее...
PooHkrd, большое спасибо.
Значение в столбце как исходные данные и конечный результат в Power Query
 
спасибо, за пример я понял Вашу мысль
Цитата
PooHkrd написал:
столбец с формулой должен быть добавлен к таблице, после того, как она будет сформирована вашим запросом.
вот здесь в моем случае немного иная ситуация, (Вы ранее говорили "Делать расчеты вместо формул Экселя формулами PQ. Смешение этих способов заведет вас в тупик.", я прислушался к Вашему совету, в исходном файлы практически все формулы которые были на Excel заменил на PQ, в остальной части еще не разобрался)
Цитата
PooHkrd написал:
Т.е. данный столбец не должен видеть сам запрос, т.к. он всегда забирает из ячеек только их значения, и возвращает в таблице на выходе он только значения текстовые/числовые/даты, но только не формулы.
для меня в конечном варианте и не нужна формула в ячейке, а нужно как раз значение.
---------------------------------------
PooHkrd, не надо ворошить истории ветки, я повторю:
* - в первом посте при создании я хотел получить столбец который можно было редактировать и поле нажатия кнопки обновить значения сохранялись.
* - Андрей VG, предложил, вариант Self Referencing Tables in Power Query. (очень хороший способ, но мне немного не подошел, так как у меня уже был столбец с формулой на PQ, я хотел добавить ему возможности редактирования)
* - на Листе 1, у меня представлена таблица с исходными данными, я выбираю "Из таблицы или диапазона", добавляю условный столбец. (Лист 7)
* - как можно добиться чтобы этот столбец стал редактируемым (в тот момент когда я выгружаю запрос на лист, если заменить значение в этом столбце и нажать обновить, то оно сохранилось, а не заменялось [стандартными данными (данными из формулы)] из столбца)?
Значение в столбце как исходные данные и конечный результат в Power Query
 
Цитата
ymal_qeb написал:
Подскажите, как заменить значения в диапазоне G:Q где идут знаки #, :-:, на другие введенные вручную и после обновления получился тот-же эффект как Self Referencing Tables in Power Query, (для примера на Листе 7 в ячейке G2, набрать 07.12.2018, то после обновления данные снова станут #), это делается с помощью другого действия или это вообще не возможно.
можно ли на Листе 2, как-то добиться того же эффекта как при использовании Self Referencing Tables, если столбец не присоединяется а уже существует с формулой PQ.
-----------
поэкспериментировал на Листе 3, вроде как удалось достичь желаемого эффекта (единственное, чтобы символы появились надо обновлять таблицу 2 раза).
-----------
PooHkrd, можете подсказать может есть более правильный способ, достичь такого эффекта, что ячейку можно редактировать и данные сохранялись после обновления, если в столбце присутствует формула PQ?
Изменено: ymal_qeb - 11.02.2019 13:08:12
Значение в столбце как исходные данные и конечный результат в Power Query
 
интересно получается, надо будет прочитать про выполнение логических операций в мат. логике.
Значение в столбце как исходные данные и конечный результат в Power Query
 
Цитата
PooHkrd написал:
нет, просто or надо заменить на and
а можете объяснить почему?
В справочнике пишут and это "И", а or это "ИЛИ"
Изменено: ymal_qeb - 08.02.2019 17:15:30
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, к сожалению не помогло, в строке 7 все также 2, а не :-:.
может нужно заменить <> на = и добавить оператор "not"?
----------
изменение <> на = + not помогло
Код
if [Способ передачи] = "Почта РФ" and not ( [Отправка Оригинал] = "" or [Отправка Оригинал] = "#" ) then 1+1 else ":-:"
Изменено: ymal_qeb - 08.02.2019 17:12:15
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, можете подсказать где ошибка? пытался формулу Excel
Код
=ЕСЛИ(
      И(B2="Почта РФ";НЕ(ИЛИ(A2="";A2="#")));1+1;
      ":-:")
перевести на PQ, но результат получается немного иной (строка 7)
Код
if [Способ передачи] = "Почта РФ" and [Отправка Оригинал] <> "" or [Отправка Оригинал] <> "#" then 1+1 else ":-:"
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, Не совсем до конца понял Ваше высказывание в посте 16.
Вы имеете ввиду, что надо отвязаться от классической логики Excelя, ссылка на ячейку и надо искать решения отталкиваясь от задачи.
Можете подсказать а как правильнее обозначить пустую ячейку: "" или null?
Спасибо, буду искать методы решение своей задачи.
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, спасибо большое за помощь.
Цитата
PooHkrd написал:
Про знаки сегодня уже не успеваю глянуть.
Если Вас не затруднит и будет свободное время посмотрите.
Если не сложно, можете дать ссылок где можно почитать где реализовались методы как при формулах ИНДЕКС, ПОИСКПОЗ, ЕСЛИОШИБКА, ТЕКСТ, ДАТАЗНАЧ.
аналог оператора "или" это - or?
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, на будущее запомню про порядок в формулах.
в приложенном файле исправленные формулы.
Код
    #"Условный столбец добавлен11" = Table.AddColumn(#"Условный столбец добавлен9", "Уточение-1-", each if [Дата документа] = "" then "" else if [Получили Мы] <> "" and [Получили Мы] <> "#" then "-" else "#")
Если не Вас не затруднит можете проверить правильно ли я написан аналог формулы:
Код
=ЕСЛИ($A5="";"";
  ЕСЛИ(
   И(НЕ(N5="");НЕ(N5="#"));
"-";
"#"))
Я так понимаю IF это аналог формулы ЕСЛИ в excel then это "то будет это", а else это "иначе"
Цитата
PooHkrd написал:
Все ваши формулы можно реализовать через PQ.
Правильно ли я Вас понимаю, что в PQ есть и аналоги формул ИНДЕКС, ПОИСКПОЗ?

Подскажите а выпадающие списки это тоже делается через PQ, или стандартным способом через Вкладку Данные --> Проверка данных?

Подскажите, как заменить значения в диапазоне G:Q где идут знаки #, :-:, на другие введенные вручную и после обновления получился тот-же эффект как Self Referencing Tables in Power Query, (для примера на Листе 7 в ячейке G2, набрать 07.12.2018, то после обновления данные снова станут #), это делается с помощью другого действия или это вообще не возможно.
Изменено: ymal_qeb - 07.02.2019 16:20:40
Значение в столбце как исходные данные и конечный результат в Power Query
 
PooHkrd, переосмыслил свой вчерашний файл.
В исходном файле по Вашему совету добавил, столбец индекса к исходным данным.
Цитата
PooHkrd написал:
Делать расчеты вместо формул Экселя формулами PQ.
Хотел спросит Вашего совета о том, что Вы думаете о том где применять формулы PQ:
На Листе 3, это мое представление конечного результата (за исключение хотелось бы убрать столбец Q:Q)
Столбцы - 1, 3, 4, 5, 16 - это исходные данные (полученные в исходном файле объединением нескольких файлов)
Столбцы - 6, 7, 8, 9, 13, 14 - это я так понимаю, что можно реализовать формулами PQ,
Столбцы - 10, 15 - здесь я так лучше использовать метод (Self Referencing Tables in Power Query), предложенный Андрей VG, проверил вроде умная таблица в которой выводится результат PQ, нормально воспринимает выпадающий список, (пример на Листе 2 Столбцы - K:K, P:P)
Столбцы - 2, 11, 12 - в данных столбцах я так понимаю, что можно использовать формулы только excel.
---------------------------
Возможно я что-то делал не так, попробовал, на Листе 5, через кнопки в PQ, добавил несколько условных столбцов, потом проделал метод (Self Referencing Tables in Power Query), но получается что добавляются копии столбцов они пустые, а в оригиналах нельзя менять данные.
---------------------------
Кажется понял свою ошибку на Листе 5, попробовал другой метод на Листе 6, то есть как я понял формулу надо писать в дублируемом запросе.
Можете подсказать верна ли моя идея?
Изменено: ymal_qeb - 07.02.2019 13:02:23
Значение в столбце как исходные данные и конечный результат в Power Query
 
Цитата
PooHkrd написал:
Спрашивайте - отвечаем
хорошо.
Спасибо Андрей VG, PooHkrd,
Буду пробовать, возникнут вопросы обращусь. :)  
Значение в столбце как исходные данные и конечный результат в Power Query
 
Цитата
PooHkrd написал:
У вас тогда при изменении источников каждый раз будут съезжать индексы.
Получается, что каждому источнику нужно добавить уникальный идентификатор, по типу индекс.1, индекс.2, индекс.3, индекс.4, индекс.4, иначе при объединении этих таблиц будет возникать за дублирование.
------
PooHkrd, ключевые поля в БД это, что по похожее на суррогатный ключ.
При скрытии никогда не пользовался группировкой столбцов, действительно Ваша идея была правильная отключив "Задать ширину столбца", а зачем постарался показать в примере (при использовании метода Self Referencing Tables in Power Query он заменяет формулы на значения, получается нужен дополнительный столбец, а также столбец А не нужен, а был нужен новый столбец с формулой в столбце B:B), получается, что столбцы A, H, I, не несут никакой информации и могут быть скрыты.
------
наверняка есть более правильные способы решения, но я пока про них не знаю.
Изменено: ymal_qeb - 06.02.2019 17:07:23
Страницы: 1 2 3 4 5 6 След.
Наверх