Дано: выгрузки из 1С (урезанный пример вложением). Далее посредством PQ привожу в нормализованную таблицу.
Проблема: не могу сформировать отличительный признак контрагента от договора. Сначала в качестве признака использовал вывод ИНН, но, оказалось, он не всегда вносится. Визуальным признаком является только наличие отступов (красные черточки на скрине). Но Excel показывает, что у них единый формат, а для PQ это вообще ничего не значит.
Скрин с отступами и форматами
Где сие отличие в Excel'e зафиксировано и как можно отследить?
Еще, вероятно, посредством VBA можно как-то вычленить признак через разное количество объединения ячеек у контрагентов и договоров. Может есть более простые и разумные пути ? Подскажите. Как вы это делаете или сделали бы ?
Там где на скрине написано "Отступ 0". Если поставишь курсор там где красные точки, то отступ будет в свойствах уже не 0. В VBA по этому признаку и можно разбивать/формировать отчёт. Как в PQ обратиться к отступ я не знаю, я только по VBA
У каждой строки свой отступ. У ООО Пупкин один отступ (например он равен 1, у Абсолют Банк он равен 2, у названия договора равен 3). Идёшь в цикле построчно и проверяешь чему равен отступ, то это и будет отличающимся признаком к чему данная строка относится (организация, контрагенты, договоры)
для примера. Поставь курсор на 11, 12, 13, 14 строки и запусти макрос.
Код
Sub test()
Dim indent As Long
'MsgBox ActiveCell.IndentLevel
indent = ActiveCell.IndentLevel
Select Case indent
Case 0
MsgBox "Ты стоишь на номере Счёта (76.05)", , ""
Case 2
MsgBox "Ты стоишь на названии Организации", , ""
Case 4
MsgBox "Ты стоишь на названии Контрагента", , ""
Case 6
MsgBox "Ты стоишь на номере Договора", , ""
Case Else
MsgBox "Фиг знает куда ты встал мышкой", , ""
End Select
End Sub
И вот зная эти цифры IndentLevel (отступа) уже можно формировать либо нужный массив, либо сразу производить расчёты и т.д. Бежишь в цикле, например, с 12 строки вниз до конца и проверяешь IndentLevel у каждой строки и принимаешь решение, что это за строка (к чему она относится к Организации, Контрагенту или Договору)
Sub Test()
Dim arrData, indent As Long, lastRow As Long, arrOut, iRow As Long, i As Long
Dim Account As String, Organization As String, Partner As String, Contract As String, INN As String
With ActiveSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'arrData = .Range("A12:E" & lastRow).Value 'массив не видит уровень отступа
ReDim arrOut(1 To lastRow, 1 To 7)
For i = 11 To lastRow
indent = Cells(i, 1).IndentLevel
Select Case indent
Case 0
Account = Cells(i, 1)
Case 2
Organization = Cells(i, 1)
Case 4
Partner = Cells(i, 1)
INN = Cells(i, 3) 'ИНН
Case 6
Contract = Cells(i, 1)
End Select
'заполняем массив
If indent = 6 Then
iRow = iRow + 1
arrOut(iRow, 1) = Account
arrOut(iRow, 2) = Organization
arrOut(iRow, 3) = Partner
arrOut(iRow, 4) = INN
arrOut(iRow, 5) = Contract
arrOut(iRow, 6) = .Cells(i, 4) 'дебет
arrOut(iRow, 7) = .Cells(i, 5) 'кредит
End If
Next i
End With
'выгружаем на новый лист
With Worksheets.Add
.[A1] = "Счёт": .[B1] = "Организация": .[C1] = "Контрагент": .[D1] = "ИНН": .[E1] = "Договор": .[F1] = "Дебет": .[G1] = "Кредит"
.Range("A2").Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut
.Columns("A:F").AutoFit
End With
End Sub
Сергей Евдокимов написал: Далее посредством PQ привожу в нормализованную таблицу
Если есть уже какие-то наработки в PQ то наверное достаточно будет этого:
Код
Sub Макрос1()
Dim r As Range
For Each r In ActiveSheet.Range("A11:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Cells(r.Row, 6) = r.IndentLevel
Next
End Sub
А ещё бывают такие, не очень приятные, моменты, например идет сч.10.09, потом сч.10.10 и вроде всё нормально, а потом сч.10.11.1 и всё сдвигается на один порядок. Когда-то писал подобную обработку, анализировал ещё цвет
Сергей Евдокимов, это Вы формируете выгрузки 1С или в таком виде поступают к Вам? Отчет внешне похож на "стандартный", а значит его выгрузку из 1С можно с высокой степенью вероятности (близкой к 100%) сразу сделать в формате плоской таблицы. Соответственно, снимаем сразу эту проблему. В эту сторону не копали?
Нифига, господа, не получилось у меня. Две 1С-ки, три юр.лица, 5 счетов, 10 (пока) отчетов. Все надо сверстать в кучу. Один, типо, холдинг.
Но проблема в том что чудо-система 1С (8.3.) ставит разные отступы в каждом отчете. Так, как ей хочется. И никак нельзя сформировать единый отчет сразу по всем нужным мне счетам. Только отдельно по каждому счету. Почему так странно задумано - не знаю.
Владимир, копал, но решение не смог выкопать (отчеты могу крутить сам). Вообщем пока в тупике. Подгонять VBA-код отдельно по каждую выгрузку - совсем лениво. Но,видимо, придется, если ума на большее не хватит.
Msi2102 - очень красивый лаконичный код. Спасибо. Задействую.
Да, понимаю суть его совета. Ранее в торговле всё настраивал и как-то всё настраивалось. А сейчас 1С-бухгалтерия полукастрированная. Пороюсь еще. Быть может, дело выгорит.
P/S/ New, спасибо вам за труд. Жаль не могу использовать. __________________________________ P/S/S/ Похоже нашел за что зацепится: счет, КАГ и договор - имеют разные кодировки цвета строк. И этого уже должно быть достаточно.
Сергей Евдокимов написал: Владимир, копал, но решение не смог выкопать (отчеты могу крутить сам)
можете пару скринов окна настроек отчета 1С скинуть? Хочу понять, настраиваемая они или нет? П.С. в 1С крутил практически все возможные отчеты, начиная с 1С 7.7
написал: можете пару скринов окна настроек отчета 1С скинуть? Хочу понять, настраиваемая они или нет?
Да. Ни вопрос. Только доступ к 1С послезавтра будет. Не на работе сейчас. Но я с удовольствием воспользуюсь вашей экспертной оценкой. Быть может новые 1С-возможности для меня откроете)
Цитата
написал: в 1С крутил практически все возможные отчеты, начиная с 1С 7.7
Sub Test1()
Dim r As Range
For Each r In ActiveSheet.Range("A11:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Cells(r.Row, 6) = r.Rows.OutlineLevel
Next
End Sub
Скажите, плиз, кто сведущ в М-коде. Я в правильных местах ставлю буферизацию? А то уж слишком тягомотно всё крутится.
Те самые места..
Если верно понимаю, то буферить нужно перед тем как система будет читать каждую ячейку (элемент списка) для дальнейших с нею действий. И дабы ей (системе) облегчить труд - закидываем в память. Всё так? Или же наивно и глубинно заблуждаюсь? Подскажите.
Собрал воедино код. Записал в надстройку. Нехай. Быть может, еще сгодится.
Код
Sub ОтсупУровеньЦвет()
'Идентифицируют отличительные признаки ячеек в 1-м столбце по трем критериям: отступ, уровень, цвет.
Dim r As Range
On Error Resume Next
Set cn = Application.InputBox("Выберите любой диапазон для вывода отличий" & Chr(13) & "", "Где вывести?", Selection.Address, Type:=8)
If cn Is Nothing Then Exit Sub
Cells(1, cn.Column) = "Отступы"
Cells(1, cn.Column + 1) = "Уровень"
Cells(1, cn.Column + 2) = "Цвет"
For Each r In ActiveSheet.Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Cells(r.Row, cn.Column) = r.IndentLevel
Cells(r.Row, cn.Column + 1) = r.Rows.OutlineLevel
Cells(r.Row, cn.Column + 2) = r.Interior.Color
Next
End Sub