написал: Цитата написал: #"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows6","","COMMISSION",Replacer.ReplaceValue,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value2","Здесь часть инфы скрываю,так как это ссылка валютный счет","",Replacer.ReplaceText,{"Column1"}), #"Filtered Rows10" = Table.SelectRows(#"Replaced Value1", each true), #"Filtered Rows7" = Table.SelectRows(#"Filtered Rows10", each [Column1] <> ""),Возможно, ошибка заключается в том, что Вы делаете замену "пустоты", а на самом деле там "null"
Точно! Я посмотрела еще раз код. Видимо замозолилась уже совсем. Ошибка оказывается была в этой части кода. Я там оператор не так поставила
Код
#"Filtered Rows6" = Table.SelectRows(#"Filled Down2", each ([Column1] <> "ВНУТРЕННИЙ ПЕРЕВОД" and [Column1] <> "ВХОДЯЩИЙ ПЕРЕВОД" and [Column1] <> "ИСХОДЯЩИЙ ВАЛЮТНЫЙ ПЕРЕВОД" and [Column1] <> "ИСХОДЯЩИЙ ПЕРЕВОД" and [Column1] <> "КОМИССИИ" and [Column1] <> "КОМИССИЯ ЗА ПЛАТЕЖИ В РУБЛЯХ")),
Я не совсем верно прописала условие, в результате комиссия за валютные платежи в строках где это было текстом прописано отжалась вместе с фильтром. А вторая строчка с этой комиссией где осталась ссылка на валютный счет переименовалась на пустоту и я это благополучно ее удалила. Теперь вот так шаг выглядит
Код
Filtered Rows6" = Table.SelectRows(#"Filled Down2", each [Column1] <> "ВНУТРЕННИЙ ПЕРЕВОД" or [Column1] <> "ВХОДЯЩИЙ ПЕРЕВОД" or [Column1] <> "ИСХОДЯЩИЙ ВАЛЮТНЫЙ ПЕРЕВОД" or [Column1] <> "ИСХОДЯЩИЙ ПЕРЕВОД" or not Text.Contains([Column1], "КОМИСС") or [Column1] <> "КОМИССИЯ ЗА ПЛАТЕЖИ В РУБЛЯХ"),
Да я сама уже задолбалась. Не один день сижу над этим. А смысл чтобы перестать эту тупую выписку ручками переносить в эксель каждый раз. И ладно бы формат для всех типов исходящий/входящий/коммисия совпадал. Так нет же. Все через ж. При выгрузке все это получается в одной каше построчно. При чем формат даты - штатовский, формат цифр - штатовский. Для валютного перевода свифт код (который буквами) для рублевого бик (цифровое значение). ИНН для входящих и исходящих есть, а если физик, то в поле инн одни нули. Кароче полный микс всевозможных данных, разбирая которые можно застрелиться.
написал: По такому описанию могу предложить одно: сюда скиньте код запроса и лучше укажите, в каком шаге PQ Вас не понимает. Данные Вы не раскроете, но мы, возможно, поймём проблему.
Добралась до компа. Вот код всего запроса. Зеленым выделен шаг где я исправляю ошибку. Дальше идет красным шаг, где я преждевременно заменила валютный счет на пустоту. Все что идет ниже этих 2 шагов должно учитывать замену значения, которая зеленым цветом. Но оно почемуто продолжает отфильтровывать пустые значения, в которых на самом деле должна остаться комиссия.
Скрытый текст
Source = Pdf.Tables(File.Contents("Здесь путь к файлу"), [Implementation="1.2"]), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Id], "Page")), #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Id", "Name", "Kind"}), #"Removed Top Rows" = Table.Skip(#"Removed Columns",1), #"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows", each ([Data.Column1] = "Валюта / Тип счета" or [Data.Column1] = "Дата записи" or [Data.Column1] = "Имя/Адрес" or [Data.Column1] = "Код налога" or [Data.Column1] = "Описание платежа" or [Data.Column1] = "Сумма платежа")), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filtered Rows1", {{"Data.Column4", type text}, {"Data.Column5", type text}, {"Data.Column7", type text}, {"Data.Column8", type text}, {"Data.Column9", type text}, {"Data.Column10", type text}, {"Data.Column11", type text}, {"Data.Column12", type text}, {"Data.Column13", type text}, {"Data.Column14", type text}, {"Data.Column15", type text}}, "ru-RU"),{"Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}), #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Merged", Text.Clean, type text}}), #"Transposed Table" = Table.Transpose(#"Cleaned Text"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Transposed Table1" = Table.Transpose(#"Promoted Headers"), #"Filtered Rows2" = Table.SelectRows(#"Transposed Table1", each ([Column1] <> "RUB" and [Column1] <> "USD")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2","12/ИНН - ","",Replacer.ReplaceText,{"Column1"}), #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Contains([Column1], "/202") then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}), #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Custom.1", each if not Text.Contains([Column1], "A.C") then 0 else 1), #"Filtered Rows3" = Table.SelectRows(#"Added Conditional Column1", each ([Custom.1] = 0)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"Custom.1"}), #"Added Conditional Column2" = Table.AddColumn(#"Removed Columns1", "Custom.1", each if Text.Contains([Column1], ".") then [Column1] else null), #"Filled Down1" = Table.FillDown(#"Added Conditional Column2",{"Custom.1"}), #"Filtered Rows4" = Table.SelectRows(#"Filled Down1", each not Text.Contains([Column1], "/202")), #"Added Conditional Column3" = Table.AddColumn(#"Filtered Rows4", "Custom.2", each if [Column1] = "ИСХОДЯЩИЙ ВАЛЮТНЫЙ ПЕРЕВОД" then [Column1] else if [Column1] = "ИСХОДЯЩИЙ ПЕРЕВОД" then [Column1] else if [Column1] = "ВХОДЯЩИЙ ПЕРЕВОД" then [Column1] else if [Column1] = "ВНУТРЕННИЙ ПЕРЕВОД" then [Column1] else if Text.Contains([Column1], "КОМИСС") then "КОМИССИИ" else null), #"Filtered Rows5" = Table.SelectRows(#"Added Conditional Column3", each not Text.Contains([Column1], ".")), #"Filled Down2" = Table.FillDown(#"Filtered Rows5",{"Custom.2"}), #"Filtered Rows6" = Table.SelectRows(#"Filled Down2", each ([Column1] <> "ВНУТРЕННИЙ ПЕРЕВОД" and [Column1] <> "ВХОДЯЩИЙ ПЕРЕВОД" and [Column1] <> "ИСХОДЯЩИЙ ВАЛЮТНЫЙ ПЕРЕВОД" and [Column1] <> "ИСХОДЯЩИЙ ПЕРЕВОД" and [Column1] <> "КОМИССИИ" and [Column1] <> "КОМИССИЯ ЗА ПЛАТЕЖИ В РУБЛЯХ")), #"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows6","","COMMISSION",Replacer.ReplaceValue,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value2","Здесь часть инфы скрываю,так как это ссылка валютный счет","",Replacer.ReplaceText,{"Column1"}), #"Filtered Rows10" = Table.SelectRows(#"Replaced Value1", each true), #"Filtered Rows7" = Table.SelectRows(#"Filtered Rows10", each [Column1] <> ""), #"Filtered Rows8" = Table.SelectRows(#"Filtered Rows7", each not Text.StartsWith([Column1], "04") or not Text.StartsWith([Column1], "")), #"Filtered Rows9" = Table.SelectRows(#"Filtered Rows8", each ([Column1] <> "000000000000")), #"Added Conditional Column4" = Table.AddColumn(#"Filtered Rows9", "Custom.3", each if Text.Contains([Column1], "Здесь инфу скрываю") then "EXPENSE REPORT/SALARY" else if Text.Contains([Column1], "Здесь инфу скрываю") then "EXPENSE REPORT/SALARY" else [Custom.2]), #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column4",{"Custom.2"}), #"Added Conditional Column5" = Table.AddColumn(#"Removed Columns2", "Custom.2", each if Text.Contains([Custom.3], "EXPENSE REPORT") then 0 else if Text.Contains([Column1], "0") then [Column1] else if Text.Contains([Column1], "1") then [Column1] else if Text.Contains([Column1], "2") then [Column1] else if Text.Contains([Column1], "3") then [Column1] else if Text.Contains([Column1], "4") then [Column1] else if Text.Contains([Column1], "5") then [Column1] else if Text.Contains([Column1], "6") then [Column1] else if Text.Contains([Column1], "7") then [Column1] else if Text.Contains([Column1], "8") then [Column1] else if Text.Contains([Column1], "9") then [Column1] else if Text.Contains([Column1], "RUMM") then [Column1] else null), #"Filtered Rows11" = Table.SelectRows(#"Added Conditional Column5", each [Custom.2] <> null), #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows11",{"Custom.2"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns3", {{"Custom", type date}}, "en-US"), #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Custom.1", type number}}, "en-US"), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type with Locale1", {{"Custom.1", type text}}, "ru-RU"), "Custom.1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", type number}, {"Custom.1.2", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "NAME"}, {"Custom", "DATE"}, {"Custom.1.1", "INCOMING"}, {"Custom.1.2", "OUTGOING"}, {"Custom.3", "TYPE"}}) in #"Renamed Columns"
Сразу говорю, файлом не могу поделиться. Это банковская выписка с конфиденциальной информацией. Итак вот собственно в чем проблема. Я через power query ( далее PQ) загружаю файл для обработки и проведения в человеческий табличный вид. Файл PQ не распознаёт чтобы входящие / исходящие платежи были в виде таблицы. Приходиться грузить всё страницы целиком и делать пляски с бубном чтобы шаг за шагом очищать это безобразие. Я дошла до самого конца. Но когда я загрузила файл, за 10 шагов до конца я обнаружила, что предприняла неправильное действие. Я заменила определённое значение на пустоту. Следующим шагом я отфильтровала пустые строки. Но я совершенно забыла, что пустыми остались в этой колонке еще строчки, где в других колонках банковская комиссия. Поэтому перед шагом с заменой значений я вставила шаг, чтобы в пустые строки заменить значение на комиссию. Я ожидала, что PQ меня поймёт и дальнейшие действия будет производить с учётом исправления. Но у него почему-то всё оставшееся шаги применяются, как если бы я не вставила эту замену значения на комиссию. Скажите пожалуйста, если я в самом коде запроса поменяю нумерацию шага, это поможет исправить проблему? Или в PQ какой то счётчик в мозгах стоит, что шагу присваивается уникальное значение и переименовка не меняет сути? . Как мне исправить проблему, чтобы не удалять и не переделывать эти последние 10 шагов?
Добрый день! У меня недавно принудительно на работе всем винду 10 поставили. Плююсь до сих пор,но сделать ничего не могу. Так вот, поставила любимую надстройку плекс, но к сожалению, один из самых нужных макросов не работает до конца корректно. А именно сборка листов. Сам макрос большую часть работает правильно. Но в результате он собирает нужные данные на итогвый лист и при этом - не удаляет созданные макросом пустые листы. В итоге мне потом приходится все эти пустые листы удалять из книги, что не очень удобно и отнимает драгоценное время (даже с хитрыми фокусами по груповвому выделению и удалению мне жалко даже этих пару минут на выделение и удаление).
*Если это чем то может помочь, все файлы эксель на компьютере - не хранятся. Все хранится на one drive, а не на самом компе. Доступ к любому файлу - через синхронизацию с майкрософт one drive.
P.S не вижу смысла файл прикладывать, т.к. у других пользователей это скорее всего будет работать корректно.
Ну так я же не говорю о полном копировании формул. Эксель даёт справку по конкретной формуле. Но допустим если вы - не продвинутый пользователь, то связка Index - match вам ни о чем не скажет. А если у вас есть поиск по функциональности "найти определенное значение из таблицы, удовлетворяющее условию, и выдать найденное значение в ячейку ." И из списка вам выдаст index match с четким описанием как работает связка этих функций - вы легко адаптируете под свою задачу.
У меня есть идея. Мне кажется, что 90% запросов от пользователей на решение каких то задач повторяются постоянно. Под эти задачи уже есть некий набор формул, которые их решают. В текущей версии Эксель нигде нет библиотеки формул не конкретно аккаунта, а всех пользователей, что то вроде копилки идей как у нас на форуме. Я на сайте Майкрософта разместила эту идею по созданию онлайн библиотеки, в которой пользователь прямо из экселя мог бы найти решение своей типичной задачи. Огромная просьба - если Вам близка моя идея, поддержите! Чем больше пользователей проголосует, тем раньше майкрософт приступит к реализации. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/filters/new?category_id=143388
К сожалению цвет - единственный параметр, который позволяет определить печать или нет лист. Это громадный файл со счетами. В этом месяце допустим выпускаем на 80 клиентов из 100 счета, в следующем на 84 из 100. Но из этих 84 какие-то могут остаться, какие-то добавится, а какие то убраться. Параметр только один - сколько раз клиент воспользовался услугой, если количество раз 0, то и счёт выпускать не надо. Соответственно выпускаемые счета помечаем нужным цветом, остальные оставляем как есть. Написала этот макрос и теперь можно не протыкивать сто листов с нужными клиентами. Невозможно заранее прописать какие именно листы понадобятся. А данные по количеству пользования услугой вообще во внешнем источнике находятся, и их приходится ручками в каждый счёт проставлять.
Option Explicit
Public Sub SelectByTabColor()
Dim wsNames() As String
Dim wsColor() As Integer
Dim ws As Worksheet
Dim ind As Integer
ReDim wsNames(0)
ReDim wsColor(0)
wsNames(0) = ActiveSheet.Name
wsColor(0) = ActiveSheet.Tab.ColorIndex
For Each ws In ThisWorkbook.Sheets
If ws.Tab.ColorIndex = wsColor(0) Then
ReDim Preserve wsNames(UBound(wsNames) + 1)
ReDim Preserve wsColor(UBound(wsColor) + 1)
wsNames(UBound(wsNames)) = ws.Name
wsColor(UBound(wsColor)) = ws.Tab.ColorIndex
End If
Next ws Sheets(wsNames).Select
End Sub' Сам макрос.
Опять же можно было бы включить в плекс. Чтобы не выбирать из узкого списка, а иметь возможность загружать курсы с любых источников, будь то Центробанк Украины или курсы валют с сайта forbes. Я просто спрашиваю возможно ли вообще технически сделать это или нет.
Скажите, а можно на форуме создать отдельную вкладку для предложений по улучшению плекса? Так же хотела предложить добавить в надстройку макрос, он делает активными всё страницы такого же цвета как и активный. Допустим сейчас выбран лист с красной заливкой - макрос ищет во всей книге и все красные листы делает активными. Очень удобно, когда с помощью цвета выделяют только те листы в большой книге с сотней листов, которые нужно напечатать/разослать сегодня.
Извиняюсь. Можно спросить на похожую тему?Допустим надо не с банка Украины данные брать, а с другого источника. Возможно ли прописать код, который будет разбирать код хтмл и искать в нем курсы валют? Или надо для каждого конкретного источника прописывать свой код?
Добрый день! Пользуюсь плексом уже продолжительное время. Очень удобно и спасибо за такую коллекцию полезных макросов. Однако возник вопрос. Дело в том, что я работаю в международной компании. Для некоторых внутренних процессов использование некоторых макросов - реально облегчает жизнь. Некоторые самые полезные макросы, я начала вручную "вытаскивать" из надстройки ( как например форма frmKife, которая разбивает выделенную таблицу на несколько листов в зависимости от значения, заливки или горизонтальной пустой строки,) и сетапить на отдельный модуль, который запускается из простой книги с макросом, и еще я меняю описание форм и ошибок на английский язык. Заморачиватся приходится из за необходимости вести презентацию рассчетов с использованием макросов. Кол-во таких макросов, которые я вручную перевожу и выделяю на отдельный модуль - растет. Копаться среди 10ка макросов не удобно. Скажите, есть ли способ не выдирать макросы из надстройки, а каким то образом изменить название макросов на английский именно в ленте плекс? Код я все равно правлю с описанием ошибок на английском. Или проще сделать отдельную свою надстройку с теми же макросами, но на английском и настроить к ним свою ленту?
Добрый день! Прошу помощи и совета. Не могу никак решить задачу. Суть проблемы вот в чем. Мне нужно, чтоб при открытии книги в ней автоматически появлялся курс доллара за определенный период. Для этого я создала запрос в соответствии с параметрическим запросом из этого урока {http://www.planetaexcel.ru/techniques/11/129/ }. Я немного подкорректировала файл запроса так, чтобы и начальную дату тоже определять из значения ячейки. Сам запрос работает без ошибок, и при открытии файла данные загружаются. Но проблема в том, что из за региональных настроек, сами курсы валют загружаются неправильно. У меня региональные настройки - Английские. То есть разделителем считается не запятая, а точка. Запятая интерпретируется как разделитель тысяч. И поэтому когда данные грузятся, то получается не 62,1234 а 621,1234. Как то так. Сразу оговорюсь, что я не могу менять эти настройки перманентно, так как много других файлов из экселя грузятся в сап. Я решила пойти хитрым путем. Я написала макрос обработки события- при открытии книги менять разделители. Но дальше продвинутся не могу. Дело в том, что сам запрос в ВБА нигде не отражается, что странно, я же при редактировании файла указала выполнять загрузку при открытии файла, и мне странно что это не добавилось в Microsoft Exel Objects во вкладку эта книга. Мне каким то образом надо сделать, чтоб после макроса изменения разделителя целой и дробной части выполнился запрос, загрузил данные, и после этого сделать обратный ход и вернуть разделитель точку.
Вот текст из файла запроса (сам файл почему то не прикрепляется к сообщению)
Код
WEB
1
[URL=http://www.cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01235&date_req1=[]http://www.cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01235&date_req1=["insert[/URL] start date"]&date_req2=["insert last date"]&rt=1&mode=1
Selection=AllTables
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
Изменено: Ag123Дарья - 20.12.2016 23:08:12(не позволяет прикрепить файл запроса)
Спасибо за помощь проверила работает. Нашла правда одну ошибку. Дело в том, что в максимальный показатель входит только 20 уровень. Сейчас при проверке он считает 19 лвл вместе с 20 и суммирует войска. Как мне исправить эту ошибку? То есть диапазон хорошо прокачаны включает в себя 15-19 уровень влючительно. И только 20 выводится как максимальный.
Вот. Просили друзья помочь с табличкой для клана. Да застопорилась. Дальше ни ни. В ячейке 0 25 должен выдавать сумму из граф количество если уровень меньше 6, в соседней ячейке тоже самое, но от 6 до 11 и так далее.
Такая трудность. Есть таблица. В ней есть: графы уровень, потом графа с ненужным для расчета значением потом графа количество, после идет опять же не нужные для расчета 2 графы, эти графы в том же количестве (5ти)повторяются друг за другом пять раз Таким образом образуются 5 блоков данных ко каждому виду.Ниже идут строки для каждого человека по этим блокам. Мне нужно Чтоб эксел суммировал ячейки "количество" для нужной строки отдельно только если уровень в 1 графе в каждом блоке меньше 6ти. то есть вот что я хочу 1 блок 2 блок 3 блок и так далее уровень * количество * * уровень * количество **уровень * количество ** 1 1 10 5 2 3 В итоге эксел должен суммировать количество с 1 блока и количества с 3 блока и получить 4, потому что уровень и там и там меньше 6, а количество из 2 блока считать не должен, так как уровень больше 5. Звездочками пишу опущенные графы.