surkenny,

Да, всегда сначала число, потом текст. Число - либо целое, либо десятичное с разделителем "."

Таблицу вложила. Спасибо)

Добрый день.
Помогите, пжл, отделить число от текста в столбце. Исходный столбец и новые столбцы должны выглядеть так:

Исходный столбец	Объем	Мера
5л	5	л
100кг	100	кг
2.5мл	2.5	мл

Пробовала по поиску чисел/текста такой формулой (нашла где-то в Интернете):
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Исходый столбец]), each if Value.Is(Value.FromText(_), type number) then _ else null)))
Для текста соответсвенно type text.

работает только для целых чисел, но если попадается десятичное число (н-р, 2.5), то точка определяется как текст, и с поле Объем выводится число 25, а в в Меру - .л

Есть ли какое-то более изящное решение?

Спасибо

Спасибо! Ловите благодарность

Добрый день. Помогите, пжл, с макросом:

Макрос идет на лист Sheet1, ищет последнюю строку в таблице и очищает ее. Все работает хорошо.

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

Спасибо! Файл всегда один с названием месяца, так что ваш подход прекрасно подходит!

Добрый день.
Я работаю с Power Query,  и создаю несколько запросов.
Для некоторых запросов источниками данных являются отчеты, которые хранятся в отдельной папке в формате pdf, один отчет для каждого месяца.
Эти отчеты сохраняются пользователями в папке, при этом их названия включают месяц отчета и время выгрузки этого отчета.

В базе данных Power Query мне нужно создать запрос, который в зависимости от выбранного в файле Excel месяца будет подключаться к отчету соответствующего месяца. Но путь к файлу просто так прописать не представляется возможным, так как имя отчета содержит в себе не только месяц, но и другую инфу, которая не регулируется (время выгрузки файла всегда разное).

"C:\Users\User\Reports\444759-NetAssetValues-2022_04_30_15_59_07_01.pdf"

Можно ли в Power Query прописать правило, которое искало бы источник данных в папке по подстроке? То есть найти файл, который содержит "444759-NetAssetValues-2022_04"?


Либо макрос, который бы искал сначала файл, содержащий нужный месяц, а затем переименовывал его в названии, и Power Query бы подключался к источнику с унифицированным названием.

Спасибо!

Прикрепила файл для общего представления.
Когда договоримся с исполнителем, подчищу, пришлю все доп. файлы и т.д.

Добрый день!

Ищу помощь в доделывании файла с базой данных в Excel, не хватает времени.

Описание задачи:

Есть внутрення база данных с клиентскими транзакциями финансовой организации, не очень большая ( 2000 строк, около 10 новых записей в месяц).
Состоит из разных видов транзакций, их около 8.
Раньше это был файл Excel, состоящий из 8 разных Листов, на каждом свой тип транзакции и соответствующие столбцы. Если пользователю нужно было ввести новую транзакцию, он открывал соответствующий Лист, копипейстил предыдущую строку, менял там что необходимо и сохранял. Также в файле были листы с необходимыми документами, которые заполнялись на основании введенных данных: н-р, счет на оплату и т.д. В счете на оплату нужно было ввести номер транзакции, и остальные поля подтягивались из Базы данных. Затем пользователь сохранял счет в pdf, клал в папку соответствующего клиента.
Также периодически необходимо делать отчет на базе этой БД, и тогда пользователь вручную фильтровал все листы по нужному временному интервалу, копировал нужные столбцы, вставлял в другой файл, и т.д.

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

Что сделано:

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

В форме с подготовкой отчета пользователь выбирает дату отчета, нажимает на кнопку "сформировать", и тогда происходит следующее: вся база данных является источником для запроса Power Query, в котором есть фильтры по дате. Фильтры установлены как ссылка на ячейки, где пользователь вводит интервал дат для формировании отчета. На кнопке "сформировать" просто лежит макрос, который обновляет этот запрос Power Query и выгружает на отдельный лист.

Что нужно сделать:
I. Доделать саму базу данных

1. Все описанное выше я тестировала на нескольких типах транзакций, например из 8 типов сейчас в выпадающем списке есть только 2. Поля для заполнения транзакций вместо 20 полей имеют только 10 тестовых, строчка для копирования данных в БД макросом пока не имеет формул (нужно вписать около 50 различных формул примерно, но очень простых: например, комиссия равна ячейка с процентом умноженная на ячейку с суммой транзакции, и т.д.) Формулы есть в других файлах, их не нужно будет придумывать, только адаптировать для этого файла.

2. Форма для выгрузки отчета тоже почти готова, там есть фильтр для дат, нужно будет добавить еще фильтр для типа транзакций.

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

II. Затем нужно будет настроить заполнение отчетов для печати, счетов и других документов, это я не начинала делать:

1. Есть внутрення форма в Excel для печати отчета (с логотипом компании, какими-то доп полями и т.д.). При нажатии на кнопку "сформировать отчет" должен выводиться отчет не просто как таблица Excel, а сразу в эту форму для печати.
2. Как только пользователь вводит в БД новую транзакцию, при нажатии на кнопку "добавить" (или например сделать еще одну кнопку "добавить и выставить счет") должен формироваться счет, сохраняться в pdf в определенной папке с названием, соответствующим имени клиента.


Прошу оценить стоимость работ за 2 блока задания (отдельно за 1 и за 2, и если сразу два)

Спасибо.

Пытливый, спасибо за совет! Честно говоря, я даже не знала о существовании раздела "работа", с удовольствием им воспользуюсь!

Помогите, пожалуйста, с задачей:

Дана таблица с операциями с валютой. Какие-то операции являются обменом валют, какие-то пополнением счета и т.д., что в таблице в явном виде не указывается.
Есть номер транзакции, и для каждой транзакции несколько операций. Для каждой операции указывается сумма, валюта, эквивалент в USD и тип. Для некотрых операций эквивалент в USD не заполнен, что и предстоит сделать.

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

Это можно сделать только для обменных операций:

1) Сначала необходимо понять, какие из операций являются обменом валют:
если количество операций с ненулевой Суммой по одному номеру транзакции равно 3 и более, при этом одна операция имеет тип "сбор", одна операция (не являющаяся сбором) имеет Сумму меньше 0, а другие операции (одна и более, тоже не являющиеся сбором) каждая имеет сумму больше 0

или наоборот
одна операция (не являющаяся сбором) имеет Сумму больше 0, а другие операции (одна и более, тоже не являющиеся сбором) каждая имеет сумму меньше 0,

то есть одна валюта может обмениваться на одну другую валюту или на несколько других.

2) Если одна валюта меняется на другую, то есть одна операция "не сбор" меньше нуля, другая больше нуля, тогда эквивалент в USD одной валюты равен эквиваленту USD другой валюты в этой транзакции, умноженному на -1.

Транзакция	Валюта	Сумма	Эквивалент в USD	Тип	Эквивалент пары
111	USD	130	130	сбор
111	EUR	-1000	-1100
111	CHF	983	0		=(-1100) * -1

3) Если одна валюта меняется сразу на несколько, или несколько меняется на одну, то эквивалент этой одной валюты равен сумме всех других эквивалентов в этой транзакции

115	USD	130	130	сбор
115	RUB	51000	1000
115	EUR	1000	1100
115	SEK	-500	0		=(1000 + 1100) *-1

4) При этом, если одна валюта меняется на несколько других, и среди этих других есть хотя бы один неопределенный эквивалент, то значение эквивалента для обмениваемой валюты определить невозможно, подставить значение null


Помогите, пожалуйста, с такой задачей. Возможно, лучше ее разбить на несколько этапов?

Добрый день.

Помогите, пжл, с задачей:
Есть запрос Power Query. Из него необходимо выгружать данные с фильтрацией до датам в зависимости от того, какой диапазон дат введет пользователь.
Даты вводятся в этой же книге Excel в определенных ячейках.

Если зайти в сам запрос и там вручную отфильтровать даты, то получается такой код:
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each [Date] >= #datetime(2022, 4, 14, 0, 0, 0) and [Date] <= #datetime(2022, 5, 31, 0, 0, 0)),

Как в этом коде заменить конкретные даты, введенные вручную, на ссылку на ячейку в листе Excel?

Спасибо

 Боже, какие дурацкие ошибки
Спасибо. В оригинальном файле названия листов написаны корректно, но вот Sells вместо Cells... Спасибо еще раз)

Вот файл, спасибо

Добрый день.

Мне необходимо скопировать строку на одном листе и вставить ее в конец умной таблицы на другом листе.

Нашла на сайте статью о том, как написать макрос для этого.
https://www.planetaexcel.ru/techniques/11/1233/

Sub Add_Sell()
   Worksheets("Форма ввода").Range("A20:E20").Copy                        
   n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                
   Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues          
End Sub

Но в моем случае выдается ошибка "Object doesnt support this property or method".
И дебаггер выделяет строку Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues  

Подскажите, пожалуйста, в чем может быть проблема и как исправить.
Спасибо!

Добрый день, помогите, пожалуйста, решить следующую задачу:

дана таблица с остатками счетов. В ней есть фамилия, валюта, остаток и статус счета (открыт/закрыт).
При вводе новой строки с новой транзакцией мне необходимо, чтобы в поле "Остаток" автоматически подставлялась сумма, равная остатку на последний день у этого пользователя у этой валюты.
Я использовала для этого такую формулу:

=суммаеслимн(С2:С9,A2:A9,A10,B2:B9,B10,D2:D9,максеслимн(D2:D9,A2:A9,A10,B2:B9,B10)

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

Формула работает правильно, хоть и кажется не очень оптимальной, но теперь мне нужно добавить еще одно условие:
если по этой фамилии по этой валюте в позднейшую дату статус счета "закрыт"б то вернуть нужно 0.

Никак не могу понять, как это реализовать. Пыталась через поиск номера строки максимальной даты - формула СТРОКА(МАКС...)) выводит ошибку.

surkenny, Спасибо! Вы мне уже помогаете не в первый раз! Вообще меня интересует тема вложенных запросов - хочу нанять репетитора на пару уроков, чтобы объяснил синтаксис, структуру и т.д. при сложных запросов. Было бы здорово, если бы вы согласились помочь. Если да, напишите, пожалуйста в личное сообщение с условиями оплаты уроков. Спасибо!

В столбце "Обменная валюта" просто указание валюты - USD, EUR и т.д. (добавила в файл)
Спасибо

Всем добрый день!

Помогите, пжл, с задачей:

Дана таблица с транзакциями с валютами. Мне необходимо среди всех транзакций определить какие из них являются обменом валют и для каждой записи по обмену валю определить обменную пару.
Например, для транзакции 111 три записи: одна запись на сбор за транзакцию в размере 130 руб, вторая запись - на уменьшение USD (-1000), третья - на добавление CHF (+983). Значит, это обмен USD на CHF. И в столбце "Обменная пара" необходимо напротив USD записать CHF, а напротив CHF - USD.

Некоторые транзакции обмена могут быть немного "подпорченными": например, как в случае транзакции 114. У этой транзакции 4 записи:
сбор 130 RUB, USD +1000, EUR -950 и появляется еще одна валюта SEK со значением 0. Это "лишняя" запись, от которой нужно избавиться.

Итого получается примерно такая хронология условий:
1) Если Сумма равна нулю - то запись необходимо удалить
2) Если количество записей по одному номеру транзакции равно 3, при этом у одной транзакции тип "сбор", а у двух других отличается знак (одна больше нуля, другая меньше нуля), то значит это обмен и для этой записи необходимо в столбец "Обменная валюта" найти парную валюту по условию:
3) номер транзакции равен номеру транзакции этой записи, тип транзакции не равен "сбору", валюта не равна валюте записи.

Если это не обмен, то ничего не возвращать.

Как-то так.
Подразумеваю, что нужно делать подзапросы внутри запроса, с чем я еще не очень знакома.

Буду признательна за помощь!

surkenny, Супер, спасибо большое! Именно то, что нужно!
Буду осваивать создание подзапросов в запросе

Спасибо! Поправила описание задачи - ошиблась с фамилией.
У одного номера транзакции может быть разное количество ответственных  (как в примере - для транзакции 1537 два ответственных, для 1538 - три). У Иванова и Сидорова тип транзакции проставлен всегда, у Петрова - никогда. Нужно проставить значение для Петрова - тип транзакции всегда равен типу этой же транзакции у Иванова.

Вообще, у меня сейчас получилось решить задачу двойной сортировкой и заполнением значений вниз: я отсортировала таблицу по 1)номеру транзакций и 2)типу транзакции. Отфильтровала только значение Иванов и Петров (получилось, что для каждого Иванова тип заполнен и он на первом месте в сортировке, Для Петрова не заполнен. И заполнила значения вниз - то есть значение Иванова спустилось на Петрова.

Но тут выяснилась другая проблема: может быть такое, что у одного номера транзакции ответственный только Петров. Тогда "заполнить значение вниз" работает некорректно и тянет значение из другого номера транзакции. Можно ли как-то сюда вписать условие, что если у номера транзакции ответственный только Петров, то значение равно 0?

Я думаю как-то реализовать это с помощью аналога СЧЕТЕСЛИ, можно ли это сделать в Power Query?  Например, добавить вспомогательный столбец со счетом фамилии Иванов для каждого номера транзакции. Затем отфильтровать столбец, убрав значение 0. И затем проделать ту же процедуру с заполнением вниз?

Спасибо, поправила описание задачи - для Петрова

Добрый день!

Подскажите, пожалуйста, можно ли в Power Query осуществлять поиск по условию в этой же таблице?
Объединение таблиц подошло бы, но таблица только одна.

Условие:
Дан столбец с номером транзакции и ответственным. За один и тот же номер транзакции может быть несколько ответственных.
В столбце тип транзакции указывается тип, при этом для ответственного Петрова тип не проставлен.
Необходимо заполнить поле тип для пользователя Петров значением, равным типу этого же номера транзакции у ответственного Иванова.

В случае в Merge можно было бы как-то объединить таблицы по двум ключевым столбцам,

Но как быть, если таблица только одна? Можно ли осуществлять поиск по значениям в этой же таблице? Что-то вроде аналога формулы поиск+индекс?
Нашла много материала на эту тему, но только про случаи с двумя отдельными таблицами.  

Xel, спасибо.  

Добрый день.
Помогите разобраться!

Во вложении файл с исходной таблицей на листе Sheet1.
В таблице 3 столбца: link, from, to.
Таблица загружена в PQ и в нем проделаны следующие манипуляции:

Добавлен столбец Address по формуле:

Text.Range ([Link], (Text.PositionOf([Link], "address") +8, 42))

То есть из столбца со ссылкой Link извлекается адрес, который состоит из 42 символов.

Затем добавляется новый столбец по формуле:

= Table.AddColumn(#"Added Custom", "ValueMultiplicator", each if [from] = [to] then 0 else if [from] = [Client Address] then -1 else 1)

То есть адрес в столбце From равен столбце To, мультипликатор должен быть равен 0, иначе:
Если адрес в столбце From равен адресу в новом столбце Address (извлеченному из ссылки), тогда мультипликатор равен -1, иначе 1.

В итоге получается таблица, в которой добавлен столбец с мультипликатором, для 3 адресов все считается верно, а для двух адресов (начинаются на 0x8f и 0xD1) считается неверно: в тех случаях, когда столбец From равен адресу из столбца Address, присваивается значение 1, хотя должно быть -1.

При этом, если проделать ту же манипуляцию в  Excel, и прописать формулу в ячейке (ЕСЛИ...), то все работает правильно. Для сравнения в таблицу добавлен столбец  с расчетом Excel.

Помогите разобраться, в чем дело.  

Спасибо

Еще вопрос: а что если таблица вообще не должна вызываться?
Поясню:
у меня есть несколько запросов. Каждый из этих запросов подключается к ссылке, которая прописана в определенной ячейке файла. Например, всего 3 запроса. И потом все эти 3 запроса обьединяются в один аппендом.

У каждого запроса прописана ссылка на ячейку:

let
   Source = Json.Document(Web.Contents(Excel.CurrentWorkbook() { [Name = "Transactions_Link"]}[Content]{1}[Transactions_Link])),
   result = Source[result],
 
И затем в общем запросе append прописывается:

let
   Source = Table.Combine({Table1, Table2, Table3}),


Например, в одном случае у меня только 2 ссылки, третья ссылка отсутствует и ячейка пустая.

Как можно прописать условие:
Если "Ячейка" пустая, то вернуть 0 в запрос Table3.
И тогда Append не сломается.

Или это можно реализовать оптимальнее?
Например, можно ли вообще создавать запрос по условию?
Если в "Ячейке" есть ссылка, то создать запрос такой-то. Если нет сслыки, то вообще не создавать.
И append прописать циклом с поиском всех доступных запросов?

Буду очень благодарна за совет!

Добрый день, господа!

Ситуация такая: база данных подключена к API сервиса, и по номеру контракта вытаскивает с сайта его имя.
В случае, если на сайте есть такой контракт, вытаскивается таблица с именем.
В случае, если контракта нет, выводится другая по формату таблица, и нужной информации в ней, соответсвенно, нет.

Можно ли установить условие, если из источника подтягивается одна таблица, то значение равно 0, если другая таблица - то имя контракта?

Спасибо