Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » luckyrichpaulp
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 След.
Изменяется цвет диапазона при копировании в другую книгу.
 
#
30.11.2022 03:51:27
Ответ тут oszone.net
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
23.11.2022 11:49:14
Цитата
Дмитрий(The_Prist) Щербаков написал:
для этого используется два слеша подряд: //
Спасибо!
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
23.11.2022 07:00:01
Всё, победил! Поменял тип с number на text. Теперь выгружается столбец.
Спасибо большое, Дмитрий!!!

Хотел спросить еще: в коде Power Query можно делать пометки как в VBA, печатая знак '
???
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
23.11.2022 06:46:40
Цитата
Дмитрий(The_Prist) Щербаков написал:
в столбце F пусто должно быть
Кстати, я не имел ввиду полностью, только по тем строкам где есть ключ и пусто, должно быть так, а остальные, где нет ключа, надо заполнить данными, со строки ниже.
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
23.11.2022 04:07:58
Цитата
Дмитрий(The_Prist) Щербаков написал:

это если мышкой наклацать почти все. Только в группировке добавьте все нужные столбцы и просуммируйте, не забыв после Пользовательского столбца раскрыть все данные из полученной после группировки таблицы.
Дмитрий волшебник :)
В общем, полученный новый столбец перенес в конец, его раскрыл (только выбрал элементы затрат, всего и ключ), фильтранул по ключу (убрал null) - готово. Однако в загруженных данных в excel почему-то столбец Ст4ц пустой %(
Пробовал удалять Ст4ц и выводить его из раскрывающегося пользовательского - пусто.
Пробовал вообще все столбцы из раскрывающегося пользовательского - пусто + еще два столбца пустых получаются (год и еще какой-то)
Пробовал делать дубль, удаляя старый - пусто.
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
22.11.2022 12:02:46
Цитата
Дмитрий(The_Prist) Щербаков написал:
Ну, объединение значений из разных строк так себе затея - если только через группировку.
Но стартовать по-любому надо с заполнения столбцов E и F. Выделяете либо оба, либо по одному -вкладка Преобразование - Заполнить. И выбираете вверх или вниз. По одному предпочтительнее, чтобы видели результат отдельно для каждого столбца. Потому что возможно, для F надо будет создавать отдельный столбец с проверкой столбца E на значения.

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

Код
    ...
    #"Заполнено вверх" = Table.FillUp(#"Измененный тип2",{"№ стр"}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Заполнено вверх", "Пользовательская", each if [Уникальный ключ] <> null then [Ст4ц] else Table.FillUp(#"Измененный тип3", {"Ст4ц"})
in
    #"Условный столбец добавлен"

И сейчас прихожу к выводу, что вариант 2 был бы предпочтительнее - объединить в одной ячейке разрезанное название позиции и удалить лишние строки. Так будет и файл меньше и тормозов меньше и удобнее лицезреть.
Изменено: luckyrichpaulp - 22.11.2022 12:05:14
 
Перейти    
Объединение данных с соседних строк в одну ячейку Power Query
 
#
22.11.2022 10:42:07
Здравствуйте.

Имеется несколько однотипных выгруженных файлов (12)
Структура у них такая, что при импорте в Power Query наименование расходов по некоторым позициям разбивается на несколько строк.
Далее при фильтрации строки выпадают из видимости и из-за этого не видно полного наименования расходов.

Хотелось бы либо с помощью Power Query это исправить или до импорта обычным функционалом excel.
Видится два варианта решения: добавление в столбы E и F данных с нижней строки либо в столбец G данных с верхних строк в нижнюю.

Помогите или посоветуйте куда копать, пожалуйста.

Код
let
    Источник = Folder.Files("C:\Users\ХХХ\Documents\Рабочее\Расходы\04 Сохраненки\2022\7У"),
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательский", each Extract7y([Folder Path]&[Name])),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Добавлен пользовательский объект", "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4", "Name.5"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Name.1", Int64.Type}, {"Name.2", Int64.Type}, {"Name.3", type text}, {"Name.4", type text}, {"Name.5", type text}}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Измененный тип", "Name.5", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name.5.1", "Name.5.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"Name.5.1", type text}, {"Name.5.2", type text}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип1",{"Content", "Name.3", "Name.5.2", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Развернутый элемент Пользовательский" = Table.ExpandTableColumn(#"Удаленные столбцы", "Пользовательский", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Развернутый элемент Пользовательский",{{"Name.1", "Год"}, {"Name.2", "Пер"}, {"Name.4", "Часть"}, {"Name.5.1", "СтрПдр"}, {"Column1", "№ стр"}, {"Column2", "Ст4ц"}, {"Column3", "Наименование расходов"}, {"Column4", "1.ФОТ"}, {"Column5", "2.ЕСН"}, {"Column6", "3.МАТ"}, {"Column7", "4.ТОП"}, {"Column8", "5.ЭЛЭ"}, {"Column9", "6.ПМЗ"}, {"Column10", "7.АМР"}, {"Column11", "8.ПРЗ"}, {"Column12", "ВСЕГО"}, {"Column13", "Уникальный ключ"}}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Уникальный ключ", type text}, {"Ст4ц", type text}, {"№ стр", type text}, {"СтрПдр", type text}, {"Наименование расходов", type text}, {"Год", type text}, {"Часть", type text}})
in
    #"Измененный тип2"
 
Перейти    
Отключить/включить нужные надстройки одним кликом без внесения изменений руками в раздел параметров "надстройки", Не открывая настроек Excel включить/отключить нужные надстройки, в том числе надстройки COM
 
#
17.10.2019 05:01:36
Здравствуйте.

Можно ли добавить кнопочку в панель быстрого доступа с макросом, который будет отключать и включать нужные надстройки?
Нужно для облегчения работы с некоторыми файлами. Применять надстройки тогда, когда они нужны.

Спасибо.
 
Перейти    
Не открывая книг макросом удалить в них строчки выше шапки таблицы
 
#
16.10.2019 10:48:13
Здравствуйте.

В папку выгружаются файлы однотипной формы, только разные названия, но по маске "ГГГГ_ММ_название".XLS, где "_" - пробел.
За отчетный период в папку добавляется 16 файлов. То есть максимум может лежать в папке 12 мес х 16 = 192 файла.
Есть форма с запросом PowerQuery, что обрабатывает и выводит в таблицу. Каждый отчетный период запрос обновляется для добавления информации из выгруженных файлов.
И вот запросу мешают две строки. Можно конечно и вручную продолжать удалять.
Скрытый текст

Но если кому, кто в теме VBA, не сложно, то помогите с макросом :)

Те файлы, что ранее были обработаны, они так и лежат в папке уже с удаленными строчками, нужно запускать макрос по мере появления новых файлов.
Наверно в макросе должна быть проверка на наличии в открываемом файле этих строк. Если есть, то режем, если нет, то закрываем, дальше ищем. Или на проверку новых файлов.
 
Перейти    
Удаление идентичных, но противоположных по значению, строк, в сумме дающих ноль
 
#
25.01.2019 08:52:35
Здравствуйте. И вновь еще вопрос по теме.

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

Прошу помощи.
 
Перейти    
Сбор данных из динамического диапазона в закрытых книгах, Собрать данные с закрытых книг, при смещающемся диапазоне
 
#
21.01.2019 06:50:32
Есть основная рабочая книга с формой 7У (сводной общей по предприятию). Так же выгружены отдельные отчеты 7У в одну папку с рабочей книгой по каждому подразделению.

В основной книге есть общая форма (итоговая в целом по предприятию), из которой свожу общие цифры. Там используется формула ГПР. А для определения номера строки я зацепляюсь поиском за первую шапку с названиями элементов через поиск по столбцу "А" №ра первого столбца "1", в котором прописываются номера строк. Этот столбец всегда выгружается из SAP первым - "A".

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

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

В общем через ДВССЫЛ с закрытой книгой не получится, так понимаю. Либо сделать аналогичную формулу через ДВССЫЛ на открытые книги. Открывать все книги, копировать формулу в таблицу, вычислять, потом найденные данные копировать самих в себя как значения и закрывать файлы.

Конечно хотелось бы через макрос. Подсобите, пожалуйста.

ЗЫ: открыть и закрыть книги смогу через такую бадягу (она уже работает в книге):
Скрытый текст

Нужно будет прописать полные пути с называнием файлов и скорректировать под них код макроса. Это мне по плечу (ранее помогли, теперь правлю под себя). А вот найти в них данные и перенести в сводную табличку в нужное место, это нет :(
 
Перейти    
Фильтр и скрытие столбцов по критерию, Скрытие лишних столбцов и фильтр видимого столбца по критерию для последующей печати
 
#
06.12.2018 05:15:37
Здравствуйте. Прошу помощи.

Есть форма для распечатки планов по конкретному подразделению и нужному периоду. Ненужные периоды скрываются.
Либо конкретный месяц, либо квартал вместе со всеми месяцами соответственно выводится.
Ставится фильтр на строку 9-ю: не равно нулю и не равно "Ошибка". Что бы скрыть ненужное.
Далее на печать.

Хочется автоматизировать процесс ибо подразделений 8 штук.
Добавил флажки для выбора периода.
Было бы удобно: указываем флажок и все фильтруется, скрывается лишнее.
Убираем флажок - фильтр отменяется, скрытые периоды появляются.
Выбрали квартал - автоматом фильтр по кварталу и так же активируются флажки по периодам (печатаются 4 столбика с данными).
Убрали флажок с квартала - снялись остальные флажки и отменился фильтр.
Плюс, если квартал активирован, то можно вручную снять ненужный како-то период, он должен скрыться.

Как это реализовать? Подсобите, пожалуйста.
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
07.11.2018 04:28:15
Ігор Гончаренко,

Гениально :)
Огромное спасибо!

а "Option Explicit" в данном случае необходим?
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:52:21
Действительно.
Огромное Вам спасибо!!!
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:47:35
Понятно, а вот еще тогда можно уточнить: все макросы нужны и которые в листе и который в модуле. При чем в модуле имеет абсолютную ссылку на лист  - With Worksheets("Лист1"). Его унифицировать можно еще как-то?
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:41:38
Спасибо!!! :) Только диапазон ограничен да? 267 строк и по столбец "CC"?
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:36:03
Цитата
Sanja написал:
Можно более 'продвинутый' способ использовать
Так круче конечно, но вот что сообщает макрос.
Скрытый текст
Изменено: luckyrichpaulp - 06.11.2018 12:37:25
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:06:44
Задействованы, но добавить еще левее столбец и строку выше, никто не мешает. Делов 3 секунды. Поэтому и думаю, что это наиболее легкий способ для любой книги.
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 12:01:20
Походу это оффтоп пошел...

Диапазоны ячеек, содержащие цифры корректировки, либо пустые, т.к. не содержат корректировок в этом периоде. При расчете нового периода их нужно очистить.
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 09:29:29
Цитата
SAS888 написал:
Если Вы их определяете визуально
Просто знаю, что именно эти данные нужны временно - по названию. Столбец 2-й из 3-х по подразделению. Подразделений восемь. А строки... По строкам есть своя нумерация по разделам, но неидеальная. Можно конечно по заливке определить, но это вариант более индивидуальный и придется править под другие книги.
А если вот единички проставить в "А" и 1-ю строку - думаю это универсально. Можно макрос в любую книгу забирать и пользоваться. Проставлять единички в нужные строки столбца "А" и столбцы строки "1".
Вообще легко, сразу добавил лишний столбец слева и строку сверху на лист и вперед. Если добавил строки лишние, сразу им указал единички кому надо. Добавил столбец - поставил единичку, если надо.
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 06:35:36
Цитата
SAS888 написал:
по каким критериям нужно очищать требуемые ячейки

Критериев нет. Данные просто в этих диапазонах нужно очистить, что бы потом вводить новые, на основе которых считаются итоговые цифры.
Просто подумал, проставить единички в нужных столбцах и в строках, это легче чем "ковырять" постоянно код.
 
Перейти    
Макрос очистки ячеек через выставление меток в нужных столбцах и строках.
 
#
06.11.2018 06:19:36
Здравствуйте.

Есть код, который работает по нажатию кнопки. Такого типа код часто использую, где ежемесячно нужно очищать данные. Вручную правлю диапазоны. И вот ладно бы один раз задал и забыл. Есть таблицы, где диапазоны могут расшириться или сместиться на какое-то количество строк вниз или вверх. После этих манипуляций снова приходится править данный код.

Каким образом можно унифицировать код? Что бы и в других книгах можно было бы применять после некоторой корректировки.
Вот думал, если например в столбце A и строке 1 указывать "1"ку на вроде метки, по нужным ячейкам, тогда макрос перебирает их и нужные очищает при совпадении? Как это еще написать (с этим туговато)...
PS: хотя лучше не привязываться именно к столбцу "А" и первой строке. Лучше любой нужный столбец и нужная строка по необходимости в конкретной задаче, так будет универсальней.

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


Помогите, пожалуйста!
Изменено: luckyrichpaulp - 06.11.2018 06:23:40
 
Перейти    
Суммирование данных за изменяемый период
 
#
03.07.2018 10:49:22
Vik_tor, вот спасибо огромное! Есть пища для размышлений.

=СУММЕСЛИМН(B7:M7;B6:M6;">="&C1;B6:M6;"<="&C2) - удивительно, она работает ) А я зачем-то перевожу месяца в значения, потом через ВПР ищу и подставляю, что бы иметь возможность использовать данные конструкции ">=". А оно работает и с текстом :)

=СУММ(СМЕЩ(B7;0;ПОИСКПОЗ(C1;B6:M6)):СМЕЩ(B7;0;ПОИСКПОЗ(C2;B6:M6))) - хороший пример наглядный. Почему-то изначально у меня ПОИСКПОЗ ассоциировался со столбцами, но ни как со строками.

Еще раз спасибо, думаю смогу дополнить поиск еще определенной строки. Видать так же надо будет со смещением делать.
 
Перейти    
Суммирование данных за изменяемый период
 
#
03.07.2018 09:32:58
Есть форма, где через ГПР ищется цифра по месяцу и номеру строки в нужном диапазоне. Диапазоны заданы с 12 месяцев по декабрь для каждого предприятия отдельно.

Суть в чем. Я нахожу данные конкретной строки только одного конкретного месяца.  =ОКРУГЛ(ГПР($L$9;ВСЕГОфпг;2;ЛОЖЬ);8)
А есть потребность найти нарастающим с января по нужный месяц, скажем за полугодие с января по июнь.

Что можно придумать?
 
Перейти    
Удаление идентичных, но противоположных по значению, строк, в сумме дающих ноль
 
#
18.04.2018 05:11:45
Здравствуйте. Хотелось бы вернуться к вопросу и доработать макрос.
Можно на лист RESULT данные выводить в нужном формате?
А именно, всё кроме столбцов "сумма" и "количество", должно быть текстом.
А то например данные в столбце "материал" изменяет, обрезая впереди идущие нули.
Изменено: luckyrichpaulp - 18.04.2018 05:12:26
 
Перейти    
Удаление идентичных, но противоположных по значению, строк, в сумме дающих ноль
 
#
15.03.2018 03:53:34
Всё получилось!
Убрал "Set" из строки Set Arr = ws2.Range("A1:L" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).Value
 
Перейти    
Удаление идентичных, но противоположных по значению, строк, в сумме дающих ноль
 
#
19.02.2018 04:19:42
Grr, можно вернуться к вопросу еще раз.

Доработал макрос под себя. Теперь хочу внедрить его в основную книгу, что бы автоматом это работало. Так вот почему-то не формирует новых данных на листе RESULT. Вроде всё поправил, в силу своих знаний )

Помогите, пожалуйста.
Код:
Скрытый текст
 
Перейти    
Планирование расходов по филиалам
 
#
19.02.2018 04:07:18
Добавил прогноз по временным рядам. В ручном режиме можно сверяться, корректировать.
 
Перейти    
Планирование расходов по филиалам
 
#
10.01.2018 04:40:23
Я вообще думал, как-то можно в расчет внедрить временные ряды.
 
Перейти    
Планирование расходов по филиалам
 
#
09.01.2018 12:57:51
Пытливый, принцип формирования прописан конечно. Но планирование по филиалам отдано на наши плечи. Как хотите, так и делайте. раньше были расчеты в зависимости от объемов. Сейчас недофинансирование. Так что сколько дадут, то и надо раскидать. Поэтому вот и хотелось бы более равномерно придумать разбивку, что бы нарастающим не сильно перекидывать вручную. Хотелось бы автоматизировать в формулах сразу перекидку). Расходы отрабатываются неравномерно по периодам (по разным причинам), какие раз в квартал (ну тут индивидуально по строке).
 
Перейти    
Страницы: 1 2 След.
Наверх