Не понимал что можно менять только последнюю размерность.
Обойти это ограничесние можно вариантами что предложили

Большое спасибо)))
Проще перебрать на условие отдельно, потом задать массив и заполнить его тогда

Всем привет!
Подскажите пожалуйства, как расширять динамический массив по мере появления новых данных, при их сборе циклом?
Второй день ковыряюсь, ошибку все найти не могу)))  Статей много прочёл, но с места сдвинуться не могу))

Всем привет!
Имеется файл объем разбросан в ручную на каждую единицу изменения.
Подскажите как в Power Query сложить два столбца, в исходных столбцах часто встречается различный текст, который и мешает банальному действию сложить.
Либо выполнить действие, также как делает функция просмотр в excel т.е. выводит результат из вектора результатов на основании подходящих значений просматриваемого вектора.

Добавил:

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

let    from = Excel.CurrentWorkbook(){[Name="AOSR"]}[Content],    typ = Table.TransformColumnTypes(from,{{"Дата начала", type date}, {"Дата окончания", type date}}),    #"Removed Other Columns" = Table.SelectColumns(typ,{"Дата начала", "Дата окончания", "Наименование работ", "Подрядчик уполномоченный"}),    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Подрядчик уполномоченный", "3"}, {"Наименование работ", "2"}}),    add = Table.AddColumn(let    from = Excel.CurrentWorkbook(){[Name="AOSR"]}[Content],    typ = Table.TransformColumnTypes(from,{{"Дата начала", type date}, {"Дата окончания", type date}}),    #"Removed Other Columns" = Table.SelectColumns(typ,{"Дата начала", "Дата окончания", "Наименование работ", "Подрядчик уполномоченный"}),    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Подрядчик уполномоченный", "3"}, {"Наименование работ", "2"}}),    add = Table.AddColumn(#"Renamed Columns", "tmp", each [    a = Duration.Days([Дата окончания]-[Дата начала])+1,                                                даты = List.Dates([Дата начала], a ,#duration(1,0,0,0)),                                                подрядчик = [3],                                                работы = Text.Combine(List.Transform(Text.Split([2],"!!!"), each try Number.ToText(Number.From(_)/a, "0.000") otherwise _))                                                                                            ][[даты],[работы],[подрядчик]]                        )[[tmp]],    exp = Table.ExpandRecordColumn(add, "tmp", {"даты", "работы", "подрядчик"}, {"даты", "работы", "подрядчик"}),    to = Table.ExpandListColumn(exp, "даты") in    to#"Renamed Columns", "tmp", each [    a = Duration.Days([Дата окончания]-[Дата начала])+1,                                                даты = List.Dates([Дата начала], a ,#duration(1,0,0,0)),                                                подрядчик = [3],                                                работы = Text.Combine(List.Transform(Text.Split([2],"!!!"), each try Number.ToText(Number.From(_)/a, "0.000") otherwise _))                                                                                            ][[даты],[работы],[подрядчик]]                        )[[tmp]],    exp = Table.ExpandRecordColumn(add, "tmp", {"даты", "работы", "подрядчик"}, {"даты", "работы", "подрядчик"}),    to = Table.ExpandListColumn(exp, "даты") in    to

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

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

let    from = Excel.CurrentWorkbook(){[Name="AOSR"]}[Content],    typ = Table.TransformColumnTypes(from,{{"Дата начала", type date}, {"Дата окончания", type date}}),    #"Removed Other Columns" = Table.SelectColumns(typ,{"Дата начала", "Дата окончания", "Наименование работ", "Подрядчик уполномоченный"}),    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Подрядчик уполномоченный", "3"}, {"Наименование работ", "2"}}),    add = Table.AddColumn(#"Renamed Columns", "tmp", each [    a = Duration.Days([Дата окончания]-[Дата начала])+1,                                                даты = List.Dates([Дата начала], a ,#duration(1,0,0,0)),                                                работы = Text.Combine(List.Transform(Text.Split([2],"!!!"), each try Number.ToText(Number.From(_)/a, "0.000") otherwise _))                                                подрядчик = [3]                                            ][[даты],[работы],[подрядчик]]                        )[[tmp]],    exp = Table.ExpandRecordColumn(add, "tmp", {"даты", "работы", "подрядчик"}, {"даты", "работы", "подрядчик"}),    to = Table.ExpandListColumn(exp, "даты") in    to

Добрый день Всем!
Как в power query выполнить действие (в моем случае - деление) со значением отмеченных тройным восклицанием. И полученный результат поместить в изначальную ячейку, откуда были извлечены для обработки без восклицательных знаков.
Нужно именно в power query.
Спасибо большое за внимание!

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

Стресовая ситуация на работе сложилась, времени не дает руководство совсем на раздумья
нужно сопоставить две таблицы каждая по 45 тыс. строк. ( количество строк в таблицах отличается)

Как формулами или макросами подсказать excel'ю что если уже такая строка была, то брать следующее точно подходящее значение.
бывает в одной титулованной позции может быть по 5 или 6 одинково именнованые строки.
Желательно без формул массива, так как не думаю что компьютер потянет такие вычесления)))
Спасибо Всем за участие!

Добрый день всем!
данная таблица делается в power query каждый день.
как дать команду PQ  что бы он рассортировал список в предпросмотре, после со списком будет производиться дальнейшая работа в PQ по вычеленению данных

он сортирует                    Должно быть
Том 1 книга 1                   Том 1 книга 1
том 10 книга 1                 Том 2 книга 1
том 100 книга 1               Том 3 книга 1
Том 2 книга 1                   .........
Том 3 книга 1                   Том 10 книга 1
                                        ...............
                                        Том 100 книга 1
Спасибо за участие

Добрый день всем!
Извините за английскую версию, но так удобней писать, код в VBA

как склеить формулу из диапазонов, которые определяются заранее кодом в VBA
Они определяются правильно (перепроверял много раз) и объявлены в начале кода переменными as Range
Последняя формула осталась, и  код готов.

Диапазоны rng1 И rng2 находятся в той же книге, но на другом листе

Должна в ячейке получиться такая
=INDEX(P!R1C3:R3C8;2;MATCH(R4C2;P!R1C4:R1C8;1))
Всем спасибо заранее!

Добрый день!
Подскажите как поменять в расширенном редакторе код для захвата однотипных данных с разными именами файлов, имеющими разные имена листов.
Тобишь, объединить данные примера 1 с данными примера 2,
#"-_Sheet" = Source{[Item="-",Kind="Sheet"]}[Data], -проблема в помеченном красном, в макросе подсавил бы звездочку. Но в query не канает.
Пусть хватает хоть все пустые листы главное, собирать с разными именами листов.

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

У меня в книге, 8 конкретных листов нужны. Остальные добавляются и удаляются пользователем по мере необходимости
Можно не нарошно удалить один из этих листов, А восставить потом проблемно очень.
Так как, пользователя определелить что можно удалять что нельзя?

Подскажите готовый макрос по перехвату команды по удалении листа из книги, Что бы определенные листы было не возможно удалить из книги.
с параметром Private Sub Worksheet_BeforeDelete()

Тоже не корректно отрабатывает.
Но идею хорошую подали добавил еще одно условие в макрос. Теперь он готов.

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

Sub PasteSums() ScreenUpdating = False For i = 10 To 42022    K = i        If Cells(i, 5).Value <> "" And Cells(i + 1, 5).Value <> "" Then            Cells(i, 15).FormulaR1C1 = "=SUM(RC[3]:RC[3])"            'Range("R" & i).Interior.Color = 65535        Else            If Cells(i, 5).Value <> "" And Cells(i, 8).Value <> "" Then                Do                    K = K + 1                    If K > 42022 Then GoTo finish                Loop Until Cells(K, 5).Value <> ""                    Cells(i, 15).FormulaR1C1 = "=SUM(R[1]C[3]:R[" & K - i - 1 & "]C[3])"                    'Range("R" & i + 1 & ":R" & k - 1).Interior.Color = 6750054                    i = K - 1            End If        End If Next i finish: ScreenUpdating = True End Sub

Всем Добрый день!
третий час бьюс над мелочью.
помогите доработать макрос по расстановке сумм по каждой позции.

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

Sub PasteSums() 'ScreenUpdating = False For i = 1 To 25    k = i        If Cells(i, 5).Value <> "" And Cells(i, 8 ) .Value <> "" Then            Do                k = k + 1                If k > 25 Then GoTo finish            Loop Until Cells(k, 5).Value <> ""                Cells(i, 15).FormulaR1C1 = "=SUM(R[1]C[3]:R[" & k - i - 1 & "]C[3])"                i = k - 1        End If Next i finish: ScreenUpdating = True End Sub

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

Добрый день всем!
Задачка у меня следующая.
Есть табель за весь текущий год. Задача стоит найти с какие по какие даты был человек.
формулы

находят только самые крайние даты, Самую раннюю и самую позднюю.
А как найти разрывы в датах которые могут быть посередине?
Разбивать на месяца тоже не вариант. Так как человек может уехать в начале  месяца и приехать к концу месяца, то формулы отразят что он целый месяц работал. А по факту только два, например 1 и 31 числа.
Руками перебирать тоже - не вариант список почти на 4 500 имен.
Спасибо за участие всем.

Добрый день форумчане!
Дайте подсказку по доработке макроса для раскрытия строк по условию.
Раскрываться должны строки которые не содержат пустые или нулевые значения в столбце "F"+ раскрыться должны наименования под раздела и раздела к которым относится эта строка.

Например.
в графе F поз. 1.3.1.1 имеется значение оно раскрывается + также макрос должен раскрыть позиции 1.3.1, .1.3 и 1

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

Пример моего кода Sub HideRows() ScreenUpdating = False Dim qUant&, NumPos$, e, n&    e = ActiveSheet.PageSetup.PrintTitleRows    n = Range(e).Row + Range(e).Rows.Count    e = Range(e).Rows.Count qUant = Cells(n, 3).End(xlDown).Row Range(Cells(n, 3), Cells(qUant - 1, 3)).EntireRow.Hidden = True For i = qUant To n Step -1    If Cells(i, 6).Value <> Empty _    Then NumPos = Cells(i, 2).Value        If NumPos <> Empty Then Rows(i).EntireRow.Hidden = False            Do                a = WorksheetFunction.Substitute(NumPos, ".", "")                b = Len(a)                c = Len(NumPos)                d = c - b                f = WorksheetFunction.Substitute(NumPos, ".", "/", d)                g = WorksheetFunction.Find("/", f)                h = Left(NumPos, g - 1) '                h = Left(NumPos, WorksheetFunction.Find("/", WorksheetFunction.Substitute(NumPos, ".", "/", Len(NumPos) - Len(WorksheetFunction.Substitute(NumPos, ".", "")))) - 1)                NumPos = h                Columns("B:B").Find(What:=h, SearchOrder:=xlByRows, SearchDirection:=xlNext).EntireRow.Hidden = False            Loop While Len(h) > 2             Next i ScreenUpdating = True End Sub

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

Спасибо, то что нужно.

Лист КС-2  отмасштабирован и в ручную разбит на печатаемые листы,Также вручную в конце каждой печатаемой страницы (Кроме последней) есть строка "Итого без ндс" с суммой соотвествующих столбцов именно текущей страницы где просматривается строка.
В перспективе в новый такой файл нужен будет макрос что бы строку "итого без НДС" вставлялась автоматом после каждой разбивки на страницы и масштабирования. Но и что бы эти строки написанные макросом двигались вместе с разбивками и пересчитывала суммы текущей страницы и что бы сумма этих макросных строк "Итого без НДС" была равна сумме на самой последей странице Файл может быть бесконечным по количеству строк, а столбцы не изменны .

В связи со сказанным выше прошу помочь в подсказках по написанию макроса т.к в VBA я всего 2-ю неделю, а именно:
1) Как привязаться кодом VBA  к разбиению т.е. к синей полужирной полосе при разметке каждой страницы
2) Как кодом привязаться к номерам уже разбитых страниц, что бы указать где применять данный код именно везде кроме первой и последней странице

Спасибо всем огромное за участие.

Поиск и подстановка нужного массива по условию с нужным количеством столбцов

А есть возможность ее привязать к поиску нужного результат, что бы в соседней ячейка я печатаю ПГ-2 он мне нужный результат.
Спасибо.

Все понял она массивная. Недгодался.

Я и говорю в отпуск, формула большая и мелкий фрагмент остался вот и крыша едет догнать вашу мысль не могу. пришлите с файлом плиз.

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

Добрый день форумчане!
Наконец-то с понедельника в отпуск. Напоследок озадачили огромным отчетом по материалам к счастью в excele))))
Из огромной формулы остался последний фрагмент почти самый пустяковый до которого додуматься не могу.
Нужна  формула что бы она согласно выбора нужного ПГ из 1-го столбца  ведергивала обозначение соответсвующего СМ из первой строки
Например строке ПГ-2 соответствует СМ-3, а ПГ-3 соответует  СМ-2 потому что стоят значения на их перекрестиии
И если можно дайте обратную формулу что бы при выборе нужно СМ из первой строки выдавался нужный ПГ-1 из первого столбца
Позже, не без вашей помощи ))))) я применю это к остальным отдельным буквенным обозначениям такие как Д, П и пр.
Спасибо заранее всем за участие!

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

Спасибо! оч помогли.