Перенос данных по столбцам, объединив строки

[amadeus017]

Доброго времени суток, уважаемые форумчане!
Возникла проблема, с которой хочу к вам обратиться.
Есть таблица, в которую загружаются данные по клиентам (лист "Данные"), нужно расчитать период задолженности (сколько месяцев просрочки, гр.14"N") и
период задолженности в днях (гр.16 "P"), после чего, перенести полученные данные, на лист "Результат".

Что сделано:
На листе "Данные" в гр. 10-15 (J - Q), стоят формулы, которые расчитываются каждую строку отдельно, по каждой сумме. Теперь необходимо, с листа
"Данные", перенести суммы, сгруппировав по клиентам, это я сделал путем сводной таблицы (Лист1 гр.1-3"A-C"), полученные данные (счет, Тип, ИНН),
перенес на лист "Результат", затем сцепил эти данные, получив уникальное значение (Лист "Результат" гр.4"D", такой же как и на листе "Данные",
сцепил "счета ДЗ" + "Тип" + "ИНН", в гр.12"L"). После чего, на листе "Результат", в каждой ячейке в гр.9-32"I-AF", прописал формулу "суммеслимн"
и в каждую строку.
С формулами, у меня получилось, но файл стал громадным и компьютер зависал при его расчетах.
Можно это переложить на макрос?

[amadeus017]

Данная тема размещена на других форумах, но без результатно (в т.ч. и этот форум).

http://www.planetaexcel.ru/forum/ind...203-perenos-da...
http://www.programmersforum.ru/showthread.php?t=284362
http://www.excel-vba.ru/forum/index.php?topic=4139.0
http://www.cyberforum.ru/ms-excel/thread1567914.html
http://www.excelworld.ru/forum/10-19856-1

[IgorGO]

не мудрено...

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

не знаю как остальные, особенно, на других форумах, но думаю задача не решена не потому, что очень сложная, а потому что никто за нее не брался

много текста, меня отпугнуло именно это
и, видимо, поэтому первый ответ в Вашей теме был от Вас же

[amadeus017]

Задача сводится к тому, что на лист "Результат", нужно разнести по колонкам (графам) данные из листа "Данные". На данный момент, в каждой ячейки стоит формула, "суммеслимн", которую и хочется заменить макросом (чтоб комп не зависал). Все остальное, можно сделать вручную (все, что до переноса данных).

[IgorGO]

у Вас на листе данные в 14-й колонке формула выглядит так:

=ЕСЛИ(И(RC[-1]<1);"01";ЕСЛИ(И(RC[-1]>=1;RC[-1]<2);"02";ЕСЛИ(И(RC[-1]>=2;RC[-1]<3);"03";ЕСЛИ(И(RC[-1]>=3;RC[-1]<4);"04";ЕСЛИ(И(RC[-1]>=4;RC[-1]<5);"05";ЕСЛИ(И(RC[-1]>=5;RC[-1]<6);"06";ЕСЛИ(И(RC[-1]>=6;RC[-1]<7);"07";ЕСЛИ(И(RC[-1]>=7;RC[-1]<8);"08";ЕСЛИ(И(RC[-1]>=8;RC[-1]<9);"09";ЕСЛИ(И(RC[-1]>=9;RC[-1]<10);"10";ЕСЛИ(И(RC[-1]>=10;RC[-1]<11);"11";ЕСЛИ(И(RC[-1]>=11;RC[-1]<12);"12";ЕСЛИ(И(RC[-1]>=12;RC[-1]<15);"13";ЕСЛИ(И(RC[-1]>=15;RC[-1]<18);"14";ЕСЛИ(И(RC[-1]>=18;RC[-1]<21);"15";ЕСЛИ(И(RC[-1]>=21;RC[-1]<24);"16";ЕСЛИ(И(RC[-1]>=24;RC[-1]<27);"17";ЕСЛИ(И(RC[-1]>=27;RC[-1]<30);"18";ЕСЛИ(И(RC[-1]>=30;RC[-1]<33);"19";ЕСЛИ(И(RC[-1]>=33;RC[-1]<36);"20";ЕСЛИ(И(RC[-1]>=36;RC[-1]<48);"21";ЕСЛИ(И(RC[-1]>=48;RC[-1]<60);"22";ЕСЛИ(И(RC[-1]>=60;RC[-1]<72);"23";ЕСЛИ(И(RC[-1]>=72);"24"))))))))))))))))))))))))

ни много, ни мало 24 вложенных ЕСЛИ
предлагаю ее заменить формулой из 2 ЕСЛИ:

=ЕСЛИ(RC[-1]>36;МИН(24;20+ОКРВВЕРХ((RC[-1]-36)/12;1));ЕСЛИ(RC[-1]>12;12+ОКРВВЕРХ((RC[-1]-12)/3;1);ОКРВВЕРХ(RC[-1];1)))

это раз!
разница в результатах только в том, что у Вас значения <10 возвращались как строки с лидирующим нулем, а моя формула возвращает числа от 1 до 24, которые так же легко посчитать как и строки "01", "02", "03" ... "09", "10" ... "24"
вообщем-то строки тоже элементарно получить (не очень усложняя формулу):

=ТЕКСТ(ЕСЛИ(RC[-1]>36;МИН(24;20+ОКРВВЕРХ((RC[-1]-36)/12;1));ЕСЛИ(RC[-1]>12;12+ОКРВВЕРХ((RC[-1]-12)/3;1);ОКРВВЕРХ(RC[-1];1)));"00")

только зачем???? числа от 1 до 24 поддаются такой же обработке...

второе:
зачем Вы собираете ключ из Счет ДЗ + Тип + ИНН???
если во всех строках Тип = "Н-001", а ИНН = "-"
какой смысл в этом действии????? если поле Счет ДЗ - уже есть ключом
или реальных данных гораздо больше и там РЕАЛЬНО для одного счета ДЗ есть разные Тип и ИНН???

[Hugo121]

Есть похожие макросы на кибере и на excelworld.

[amadeus017]

Добрался до форума, чтобы посмотреть на мою тему и выразить всем свою благодарность за проделанную Вами, IgorGO и Hugo121, работу! Всегда прислушиваюсь к мнению IgorGo, и охотно пользуюсь его подсказками, в том числе и сейчас, хочу попробовать его подсказкой по формулам, я в своей практике, ранее такими не пользовался. Заодно отвечу, что в реальности, ИНН и Типы, разные, вот для чего нужна колонка "ключ", для формул которыми я воспользовался при расчетах.
На данную тему, помогли с ответом на других форумах, и прошу эту тему считать, закрытой. Помогли с макросом, который я сегодня попробовал в работе, пока все устраивает. Макрос, ниже.

[amadeus017]

Sub ertert()
Dim x, y(), i&, rw&, k$
x = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value

With Sheets("Результат")
x = .Range("A7:C" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
ReDim y(1 To UBound(x), 1 To 24)

With CreateObject("Scripting.Dictionary" )
.CompareMode = 1
For i = 1 To UBound(x, 1)
k = x(i, 1) & x(i, 3) & x(i, 2) 'Счет ДЗ - Тип - ИНН
.Item(k) = i
Next i

With Sheets("Данные")
x = .Range("A10:N" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x, 1)
k = x(i, 1) & x(i, 2) & x(i, 10)
If .Exists(k) Then
rw = .Item(k)
y(rw, x(i, 14)) = y(rw, x(i, 14)) + x(i, 9)
End If
Next i
End With

With Sheets("Результат")
.Range("I7").Resize(UBound(y), 24).Value = y
.Activate
End With
End Sub

[Hugo121]

Строка
x = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
лишняя, и ещё нужно позаботиться убрать возможные фильтры (я в своём варианте это сделал).

[amadeus017]

Сообщение от Hugo121

Строка
x = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
лишняя, и ещё нужно позаботиться убрать возможные фильтры (я в своём варианте это сделал).

Хорошо, попробую убрать эту строку и запустить макрос без неё.