ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Сводная таблица в Экселе - Строки вместо чисел
22 сообщений из 47, страница 2 из 2
  2  все
Сводная таблица в Экселе - Строки вместо чисел
    #35450638
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
KL (XL) ЗЫ: отдельно для White Owl . Это конечно прекрасный повод поглумиться над моим невежеством в SQL, но за дельные советы буду крайне признателен.Ну что-ж, я совсем злодей что-ли? Иногда я тоже бываю добрым :)


KL (XL) Дано:
CC Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Days Acc. 5115 Acc. 513512FFR3 0 0 0 0 31 30 31 31 30 31 30 31 245 49.756 002MARC 0 0 0 0 31 30 31 31 30 31 30 31 245 33.924 6.730
Надо получить:
Acc CC Month CostAcc. 5115 02MARC Jan 0Acc. 5115 02MARC Feb 0Acc. 5115 02MARC Mar 0Acc. 5115 02MARC Apr 0Acc. 5115 02MARC May 4.292Acc. 5115 02MARC Jun 4.154Acc. 5115 02MARC Jul 4.292Acc. 5115 02MARC Aug 4.292Acc. 5115 02MARC Sep 4.154Acc. 5115 02MARC Oct 4.292Acc. 5115 02MARC Nov 4.154Acc. 5115 02MARC Dec 4.292Acc. 5115 12FFR3 Jan 0Acc. 5115 12FFR3 Feb 0Acc. 5115 12FFR3 Mar 0Acc. 5115 12FFR3 Apr 0Acc. 5115 12FFR3 May 6.296Acc. 5115 12FFR3 Jun 6.093Acc. 5115 12FFR3 Jul 6.296Acc. 5115 12FFR3 Aug 6.296Acc. 5115 12FFR3 Sep 6.093Acc. 5115 12FFR3 Oct 6.296Acc. 5115 12FFR3 Nov 6.093Acc. 5115 12FFR3 Dec 6.296Acc. 5135 02MARC Jan 0Acc. 5135 02MARC Feb 0Acc. 5135 02MARC Mar 0Acc. 5135 02MARC Apr 0Acc. 5135 02MARC May 852Acc. 5135 02MARC Jun 824Acc. 5135 02MARC Jul 852Acc. 5135 02MARC Aug 852Acc. 5135 02MARC Sep 824Acc. 5135 02MARC Oct 852Acc. 5135 02MARC Nov 824Acc. 5135 02MARC Dec 852Acc. 5135 12FFR3 Jan 0Acc. 5135 12FFR3 Feb 0Acc. 5135 12FFR3 Mar 0Acc. 5135 12FFR3 Apr 0Acc. 5135 12FFR3 May 0Acc. 5135 12FFR3 Jun 0Acc. 5135 12FFR3 Jul 0Acc. 5135 12FFR3 Aug 0Acc. 5135 12FFR3 Sep 0Acc. 5135 12FFR3 Oct 0Acc. 5135 12FFR3 Nov 0Acc. 5135 12FFR3 Dec 0
.....
Наверняка все проще гораздо :-)Ну с точки зрения формального sql это не такая уж простая задача. У sql сложности с транспонированием таблиц. Но конечно если очень нужно, то можно из первой таблицы получить вторую вот примерно так (исходная таблица имеет имя #t, ну и некоторые колоноки я переименовал):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
select 'Acc. 5115' as "Acc", CC, 'Jan' as "Month", Acc_5115/Total_Days * "Jan" as "Cost" from #t
union select 'Acc. 5115', CC, 'Feb', Acc_5115/Total_Days * "Feb"  from #t
union select 'Acc. 5115', CC, 'Mar', Acc_5115/Total_Days * "Mar"  from #t
union select 'Acc. 5115', CC, 'Apr', Acc_5115/Total_Days * "Apr"  from #t
union select 'Acc. 5115', CC, 'May', Acc_5115/Total_Days * "May"  from #t
union select 'Acc. 5115', CC, 'Jun', Acc_5115/Total_Days * "Jun"  from #t
union select 'Acc. 5115', CC, 'Jul', Acc_5115/Total_Days * "Jul"  from #t
union select 'Acc. 5115', CC, 'Aug', Acc_5115/Total_Days * "Aug"  from #t
union select 'Acc. 5115', CC, 'Sep', Acc_5115/Total_Days * "Sep"  from #t
union select 'Acc. 5115', CC, 'Oct', Acc_5115/Total_Days * "Oct"  from #t
union select 'Acc. 5115', CC, 'Nov', Acc_5115/Total_Days * "Nov"  from #t
union select 'Acc. 5115', CC, 'Dec', Acc_5115/Total_Days * "Dec"  from #t
union select 'Acc. 5135', CC, 'Jan', Acc_5135/Total_Days * "Jan"  from #t
union select 'Acc. 5135', CC, 'Feb', Acc_5135/Total_Days * "Feb"  from #t
union select 'Acc. 5135', CC, 'Mar', Acc_5135/Total_Days * "Mar"  from #t
union select 'Acc. 5135', CC, 'Apr', Acc_5135/Total_Days * "Apr"  from #t
union select 'Acc. 5135', CC, 'May', Acc_5135/Total_Days * "May"  from #t
union select 'Acc. 5135', CC, 'Jun', Acc_5135/Total_Days * "Jun"  from #t
union select 'Acc. 5135', CC, 'Jul', Acc_5135/Total_Days * "Jul"  from #t
union select 'Acc. 5135', CC, 'Aug', Acc_5135/Total_Days * "Aug"  from #t
union select 'Acc. 5135', CC, 'Sep', Acc_5135/Total_Days * "Sep"  from #t
union select 'Acc. 5135', CC, 'Oct', Acc_5135/Total_Days * "Oct"  from #t
union select 'Acc. 5135', CC, 'Nov', Acc_5135/Total_Days * "Nov"  from #t
union select 'Acc. 5135', CC, 'Dec', Acc_5135/Total_Days * "Dec"  from #t
order by  1 , 2 , 3 ;
...
Рейтинг: 0 / 0
24.07.2008, 20:00:13
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35450786
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
2 Kyber & White Owl,

Огромное спасибо обоим! Я в шоке - она ж фунциклирует. Пока остановился на решении Kyber из-за его относительной малогабаритности. В приложении рабочий пример.

KL
...
Рейтинг: 0 / 0
24.07.2008, 22:04:48
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35452088
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
KL (XL)...она ж фунциклирует...

Немного поторопился :-( Пример, который я привел вначале, оказался с брачком. В столбце "СС" не уникальные значения как можно заключить из первоначального примера. Реальная таблица больше похожа на это:
CC Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Days Acc 5115 Acc 513512FFR3 0 0 0 0 31 30 31 31 30 31 30 31 245 49.756 002MARC 0 0 0 0 31 30 31 31 30 31 30 31 245 33.924 6.73012FFR3 0 0 0 0 31 30 31 31 30 31 30 31 245 32.016 002MARC 0 0 0 0 31 30 31 31 30 31 30 31 245 19.345 012FFR3 0 0 0 0 31 30 31 31 30 4 0 0 157 56.895 24.35602MARC 0 0 0 0 0 0 15 31 30 31 30 31 168 16.456 0

Сейчас код такой (файл с примером выше0:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
Option Explicit

Sub Build_List()
    Dim Con As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim Sql As String
    
    Application.ScreenUpdating = False
    
    Set Con = New ADODB.Connection
    With Con
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
      .Open
    End With
    Set RS = New ADODB.Recordset
    Sql = "SELECT Acc.Account, Data.CC, Months.Month, Months.Id, " & _
        "Choose(Acc.Id,`Acc 5115`,`Acc 5135`)*" & _
        "Choose(Months.Id,`Jan`,`Feb`,`Mar`,`Apr`,`May`,`Jun`,`Jul`,`Aug`,`Sep`,`Oct`,`Nov`,`Dec`)/" & _
        "`Total Days` AS Cost FROM [Data$A1:P3] AS Data, [Months$A1:B13] AS Months, [Acc$A1:B3] AS Acc ORDER BY 1,2,4;"
        
    RS.Open Sql, Con
    With Sheets("Report")
        .Range("a2").CopyFromRecordset RS
        .Columns( 4 ).Delete
        .Columns( 1 ).Replace "* ", ""
        .Activate
    End With
    Con.Close: Set RS = Nothing: Set Con = Nothing
    
End Sub

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

Acc CC Month CostAcc1 CC1 Jan # Feb # Mar # Apr # May # Jun # Jul # Aug # Sep # Oct # Nov # DEc # CC2 Jan # Feb # Mar # Apr # May # Jun # Jul # Aug # Sep # Oct # Nov # DEc #Acc 2 CC1 Jan # Feb # Mar # Apr # May # Jun # Jul # Aug # Sep # Oct # Nov # DEc # CC2 Jan # Feb # Mar # Apr # May # Jun # Jul # Aug # Sep # Oct # Nov # DEc #



Попытки подставить в разных местах запроса " GROUP BY Data.CC " приводят к разного рода ошибкам.

Спасибо.
...
Рейтинг: 0 / 0
25.07.2008, 13:30:32
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35452970
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
Вот сваял такое:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
    Sql = _
        "SELECT Acc.Account, Data.CC, Months.Month, Months.Id, " & _
        "sum(Choose(Acc.Id,`Acc 5115`,`Acc 5135`)*" & _
        "Choose(Months.Id,`Jan`,`Feb`,`Mar`,`Apr`,`May`,`Jun`,`Jul`,`Aug`,`Sep`,`Oct`,`Nov`,`Dec`)/" & _
        "`Total Days`) AS Cost " & _
        "FROM [Data$A1:P7] AS Data, [Months$A1:B13] AS Months, [Acc$A1:B3] AS Acc " & _
        "GROUP BY Acc.Account, Data.CC, Months.Month, Months.Id ORDER BY 1,2,4;"

Боюсь даже на реальном файле пробовать (1000 записей, 30 CC, 12 Acc) - думаю будет считать вечность :-)

Еще бы строки с нулевым значением "Cost" удалить, но "WHERE Cost <> 0" никак не всовывается :-(
...
Рейтинг: 0 / 0
25.07.2008, 17:57:05
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453060
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
KL (XL) KL (XL)...она ж фунциклирует...

Немного поторопился :-( Пример, который я привел вначале, оказался с брачком. В столбце "СС" не уникальные значения как можно заключить из первоначального примера. Реальная таблица больше похожа на это:
CC Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Days Acc 5115 Acc 513512FFR3 0 0 0 0 31 30 31 31 30 31 30 31 245 49.756 002MARC 0 0 0 0 31 30 31 31 30 31 30 31 245 33.924 6.73012FFR3 0 0 0 0 31 30 31 31 30 31 30 31 245 32.016 002MARC 0 0 0 0 31 30 31 31 30 31 30 31 245 19.345 012FFR3 0 0 0 0 31 30 31 31 30 4 0 0 157 56.895 24.35602MARC 0 0 0 0 0 0 15 31 30 31 30 31 168 16.456 0А в чем тогда уникальность строк?
Вот например если мы возьмем CC=02MARC, по колонке Jun. Какой итоговый Cost у нас должен быть?
А вообще, я бы посоветовал забыть про sql на таких данных и сделать простыми циклами. Будет быстрее и проще.
...
Рейтинг: 0 / 0
25.07.2008, 18:33:04
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453117
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
KL (XL)Боюсь даже на реальном файле пробовать (1000 записей, 30 CC, 12 Acc) - думаю будет считать вечность :-)
Не, ничего 12-16 сек вполне сносно, хотя практически то же время, что мои циклы транспонирования.
...
Рейтинг: 0 / 0
25.07.2008, 19:07:01
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453160
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
Таки точно ABC :)

Ну, насколько я понимаю, тебе нужно обработать каждую строку, а потом сложить стоимость у повторяющихся cost-centers. Если да - то это превосходно сделает инструкция "GROUP BY".
...
Рейтинг: 0 / 0
25.07.2008, 20:11:06
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453194
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
White OwlА в чем тогда уникальность строк?
Уникальность строк в том, что каждая принадлежит уникальному сотруднику. Просто я Employee ID не включил в таблицу как впрочем и другие столбцы.
White OwlВот например если мы возьмем CC=02MARC, по колонке Jun. Какой итоговый Cost у нас должен быть?
Для CC=02MARC, по колонке Jun итоговый Cost должен быть:
для Acc 5115: 6.522,73
для Acc 5135: 82,41
White OwlА вообще, я бы посоветовал забыть про sql на таких данных и сделать простыми циклами. Будет быстрее и проще.
Это-то я и пытаюсь понять
...
Рейтинг: 0 / 0
25.07.2008, 20:46:16
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453206
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
KL (XL)для Acc 5115: 6.522,73
Ага... Значит точка - это разделитель разрядов.... А я думаю, почему это у меня другие, как говорил мой шеф, нумеры... :)
...
Рейтинг: 0 / 0
25.07.2008, 20:54:56
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453207
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyberТаки точно ABC :)

Ну, насколько я понимаю, тебе нужно обработать каждую строку, а потом сложить стоимость у повторяющихся cost-centers. Если да - то это превосходно сделает инструкция "GROUP BY".

Спасибо, kyber!
Если я не ошибаюсь, то твой пример - это как раз то, что я написал выше, только я вывел еще поле "Months.Id" для правильной сортировке по месяцам:

Код: plaintext
1.
2.
3.
4.
5.
6.
    Sql = _
        "SELECT Acc.Account, Data.CC, Months.Month, Months.Id, " & _
        "sum(Choose(Acc.Id,`Acc 5115`,`Acc 5135`)*" & _
        "Choose(Months.Id,`Jan`,`Feb`,`Mar`,`Apr`,`May`,`Jun`,`Jul`,`Aug`,`Sep`,`Oct`,`Nov`,`Dec`)/" & _
        "`Total Days`) AS Cost " & _
        "FROM [Data$A1:P7] AS Data, [Months$A1:B13] AS Months, [Acc$A1:B3] AS Acc " & _
        "GROUP BY Acc.Account, Data.CC, Months.Month, Months.Id ORDER BY 1,2,4;"
...
Рейтинг: 0 / 0
25.07.2008, 20:55:03
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453210
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyber KL (XL)для Acc 5115: 6.522,73
Ага... Значит точка - это разделитель разрядов.... А я думаю, почему это у меня другие, как говорил мой шеф, нумеры... :)
Это на самом деле не принципиально на данной стадии - все равно числа те же. Вся ирония ситуации в том, что я использую запятую для разрядов, а для русского форума перевожу ее в точку :-)
...
Рейтинг: 0 / 0
25.07.2008, 20:59:40
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453224
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
White OwlВот например если мы возьмем CC=02MARC, по колонке Jun. Какой итоговый Cost у нас должен быть?
В финале должна получиться такая таблица (Cost округлен до целых):
Acc CC Month Cost5115 02MARC Jan 05115 02MARC Feb 05115 02MARC Mar 05115 02MARC Apr 05115 02MARC May 6.7405115 02MARC Jun 6.5235115 02MARC Jul 8.2095115 02MARC Aug 9.7775115 02MARC Sep 9.4615115 02MARC Oct 9.7775115 02MARC Nov 9.4615115 02MARC Dec 9.7775115 12FFR3 Jan 05115 12FFR3 Feb 05115 12FFR3 Mar 05115 12FFR3 Apr 05115 12FFR3 May 21.5815115 12FFR3 Jun 20.8855115 12FFR3 Jul 21.5815115 12FFR3 Aug 21.5815115 12FFR3 Sep 20.8855115 12FFR3 Oct 11.7965115 12FFR3 Nov 10.0135115 12FFR3 Dec 10.3475135 02MARC Jan 05135 02MARC Feb 05135 02MARC Mar 05135 02MARC Apr 05135 02MARC May 855135 02MARC Jun 825135 02MARC Jul 855135 02MARC Aug 855135 02MARC Sep 825135 02MARC Oct 855135 02MARC Nov 825135 02MARC Dec 855135 12FFR3 Jan 05135 12FFR3 Feb 05135 12FFR3 Mar 05135 12FFR3 Apr 05135 12FFR3 May 4.8095135 12FFR3 Jun 4.6545135 12FFR3 Jul 4.8095135 12FFR3 Aug 4.8095135 12FFR3 Sep 4.6545135 12FFR3 Oct 6215135 12FFR3 Nov 05135 12FFR3 Dec 0
...
Рейтинг: 0 / 0
25.07.2008, 21:08:47
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35453338
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
KL (XL) White OwlВот например если мы возьмем CC=02MARC, по колонке Jun. Какой итоговый Cost у нас должен быть?
В финале должна получиться такая таблица (Cost округлен до целых):
Acc CC Month Cost5115 02MARC Jan 05115 02MARC Feb 05115 02MARC Mar 05115 02MARC Apr 05115 02MARC May 6.7405115 02MARC Jun 6.5235115 02MARC Jul 8.2095115 02MARC Aug 9.7775115 02MARC Sep 9.4615115 02MARC Oct 9.7775115 02MARC Nov 9.4615115 02MARC Dec 9.7775115 12FFR3 Jan 05115 12FFR3 Feb 05115 12FFR3 Mar 05115 12FFR3 Apr 05115 12FFR3 May 21.5815115 12FFR3 Jun 20.8855115 12FFR3 Jul 21.5815115 12FFR3 Aug 21.5815115 12FFR3 Sep 20.8855115 12FFR3 Oct 11.7965115 12FFR3 Nov 10.0135115 12FFR3 Dec 10.3475135 02MARC Jan 05135 02MARC Feb 05135 02MARC Mar 05135 02MARC Apr 05135 02MARC May 855135 02MARC Jun 825135 02MARC Jul 855135 02MARC Aug 855135 02MARC Sep 825135 02MARC Oct 855135 02MARC Nov 825135 02MARC Dec 855135 12FFR3 Jan 05135 12FFR3 Feb 05135 12FFR3 Mar 05135 12FFR3 Apr 05135 12FFR3 May 4.8095135 12FFR3 Jun 4.6545135 12FFR3 Jul 4.8095135 12FFR3 Aug 4.8095135 12FFR3 Sep 4.6545135 12FFR3 Oct 6215135 12FFR3 Nov 05135 12FFR3 Dec 0
Ну тогда все довольно просто. Добавь в свой макрос стейтмент типа такого:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SourceTable = "[Data$A1:P7]"
Sql = "select [Acc], [CC], [Month], sum([Cost1]) as [Cost] from (" & _
" select 'Acc. 5115' as [Acc], [CC], 'Jan' as [Month], [Acc 5115]/[Total Days]*[Jan] as [Cost1] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Feb', [Acc 5115]/[Total Days]*[Feb] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Mar', [Acc 5115]/[Total Days]*[Mar] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Apr', [Acc 5115]/[Total Days]*[Apr] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'May', [Acc 5115]/[Total Days]*[May] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Jun', [Acc 5115]/[Total Days]*[Jun] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Jul', [Acc 5115]/[Total Days]*[Jul] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Aug', [Acc 5115]/[Total Days]*[Aug] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Sep', [Acc 5115]/[Total Days]*[Sep] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Oct', [Acc 5115]/[Total Days]*[Oct] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Nov', [Acc 5115]/[Total Days]*[Nov] from " & SourceTable & _
" union select 'Acc. 5115', CC, 'Dec', [Acc 5115]/[Total Days]*[Dec] from " & SourceTable
Sql = Sql & _
" union select 'Acc. 5135', CC, 'Jan', [Acc 5135]/[Total Days]*[Jan] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Feb', [Acc 5135]/[Total Days]*[Feb] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Mar', [Acc 5135]/[Total Days]*[Mar] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Apr', [Acc 5135]/[Total Days]*[Apr] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'May', [Acc 5135]/[Total Days]*[May] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Jun', [Acc 5135]/[Total Days]*[Jun] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Jul', [Acc 5135]/[Total Days]*[Jul] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Aug', [Acc 5135]/[Total Days]*[Aug] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Sep', [Acc 5135]/[Total Days]*[Sep] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Oct', [Acc 5135]/[Total Days]*[Oct] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Nov', [Acc 5135]/[Total Days]*[Nov] from " & SourceTable & _
" union select 'Acc. 5135', CC, 'Dec', [Acc 5135]/[Total Days]*[Dec] from " & SourceTable & _
" ) group by [Acc], [CC], [Month]"
И оно превратит тебе первую таблицу во вторую.
Запрос формируется в два приема, потому что VBA не хочет принимать столько много разрывов строки. Конечно ее можно сфорировать и циклом, было бы желание...
...
Рейтинг: 0 / 0
26.07.2008, 01:01:25
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35454793
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
А меня заинтересовала сама задачка... Как раз сейчас я начинаю заниматься Activity Based Costing - мне кажется, как раз это и считается в твоем примере.

KL (XL) , если у тебя есть еще какие-то файлы по этой задаче (меня интересует только внутренняя логика), ты можешь бросить мне на kyber@ua.fm? Думаю, с конфиденциальностью проблем быть не должно - я живу в Днепропетровске, не знаю, о какой компании идет речь, и полный набор данных мне не нужен. Спасибо.
...
Рейтинг: 0 / 0
28.07.2008, 11:48:39
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35456655
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyberА меня заинтересовала сама задачка... Как раз сейчас я начинаю заниматься Activity Based Costing - мне кажется, как раз это и считается в твоем примере.

KL (XL) , если у тебя есть еще какие-то файлы по этой задаче (меня интересует только внутренняя логика), ты можешь бросить мне на kyber@ua.fm? Думаю, с конфиденциальностью проблем быть не должно - я живу в Днепропетровске, не знаю, о какой компании идет речь, и полный набор данных мне не нужен. Спасибо.

Вообще файл не мой и к тому же гад на испанском (переводить замучаешься). Если хочешь, на след. неделю могу попробовать прислать более подробный пример-схему. Но уже сейчас ясно, что White Owl был прав - транспонирование гораздо быстрее делать через цикл.
...
Рейтинг: 0 / 0
29.07.2008, 02:05:29
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35458948
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
KL (XL)Но уже сейчас ясно, что White Owl был прав - транспонирование гораздо быстрее делать через цикл.Дык! :)
Сталкиваясь с SQL люди почему-то думают что это такие волшебные команды которые сами сразу делают все выборки без вских там переборов данных. На самом то деле, внутри каждого SELECT'а прячется куча циклов, причем эти циклы частенько не оправданны. Поэтому, чаще всего, обращение к листу Экселя через ADO будет намного медленнее чем самостоятельная обработка данных через VBA и функции Экселя.
...
Рейтинг: 0 / 0
29.07.2008, 19:46:53
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35696821
A-Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A-Nik
Гость
KL, посмотри мой вариант :-)
Попробуй запустить его на реальном файле и скажи время выполнения ;-)
...
Рейтинг: 0 / 0
05.12.2008, 17:03:53
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35697523
A.Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.Nik
Гость
Что-то я засомневался в хорошей скорости работы того варианта для большой таблицы. Посему предлагаю оптимизированный вариант. Мой прогноз для таблицы в 1000 строк - пару десятых долей секунды :-)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
Private Sub CommandButton1_Click()
Dim CCuniq As Range, Rslt1stCol As Range, StartT As Single
'TmpArr = Application.Transpose(Application.Transpose([transpose(offset(a3,q4:q5,))]))
StartT = Timer
Application.ScreenUpdating = False
Range([cc], [Acc]).Sort Key1:=[cc].Resize( 1 ), Order1:=xlAscending, Header:=xlYes
With WorksheetFunction
  TmpArr = .Transpose(Evaluate("IF(" & [cc].Address & "=" & [cc].Offset(- 1 ).Address & ","""",row(" & [cc].Address & "))"))
  TmpArr = Split(.Trim(Join(TmpArr, " ")), " ")
  CCuCnt% = UBound(TmpArr) +  1 
  Set CCuniq = [Acc].Offset( 1 , [columns(Acc)]).Resize(CCuCnt,  1 )
  CCuniq = .Transpose(TmpArr)
  CCuniq.Offset(CCuCnt).Resize( 1 ) = [cc].Row + [rows(cc)]
  CCuniq.Offset(,  1 ).FormulaArray = "=index(" & [cc].EntireColumn.Address & "," & CCuniq.Address & ")"
  
  RsltTblLen =  12  * CCuCnt * [columns(Acc)]
  Set Rslt1stCol = [Resoult].Resize(RsltTblLen)
  'Acc
  Rslt1stCol = .Transpose(Split(Replace(Join(Evaluate("Transpose(Transpose(REPT(Acc&"" ""," & CCuCnt *  12  & ")))"), ""), "Acc ", ""), " "))
  'CC
  [Resoult].Offset(,  1 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT(" & CCuniq.Offset(,  1 ).Address & "&"" "",12))"), ""), " "))
  [Resoult].Offset(,  1 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  1 )
  'Monthes
  [Resoult].Offset(,  2 ).Resize( 12 ) = .Transpose(Array("Jen", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
  [Resoult].Offset(,  2 ).Resize( 12 ).Copy [Rslt1stCol].Offset(,  2 )
  '1..12
  [Resoult].Offset(,  4 ).Resize( 12 ) = [row(a1:a12)]
  [Resoult].Offset(,  4 ).Resize( 12 ).Copy [Rslt1stCol].Offset(,  4 )
  '1..CC_unique_Count
  [Rslt1stCol].Offset(,  5 ) = .Transpose(Split(Join(Evaluate("Transpose(REPT(ROW(A1:A" & CCuCnt & ")&"" ""," & CCuCnt *  12  & "))"), ""), " "))
  'Beg Range
  [Resoult].Offset(,  6 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT(" & CCuniq.Address & "&"" "",12))"), ""), " "))
  [Resoult].Offset(,  6 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  6 )
  'End Range
  [Resoult].Offset(,  7 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT((" & CCuniq.Offset( 1 ).Address & "-1)&"" "",12))"), ""), " "))
  [Resoult].Offset(,  7 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  7 )
  'Cost
  ShName$ = [Acc].Parent.Name & "!"
  AccRng$ = ShName$ & [Acc].EntireColumn.Address
  TotDaysRng$ = ShName$ & [cc].Offset(,  13 ).EntireColumn.Address
  MonthesRng$ = ShName$ & [cc].Offset(,  1 ).Resize(,  12 ).EntireColumn.Address
  Mon$ = [Resoult].Offset(,  4 ).Address( 0 ,  0 )
  AccCnt$ = [Resoult].Offset(,  5 ).Address( 0 ,  0 )
  CCbeg$ = [Resoult].Offset(,  6 ).Address( 0 ,  0 )
  CCend$ = [Resoult].Offset(,  7 ).Address( 0 ,  0 )
  [Resoult].Offset(,  3 ).FormulaArray = "=SUM(INDEX(" & AccRng & "," & CCbeg & "," & AccCnt & "):INDEX(" & AccRng & "," & CCend & "," & AccCnt & ")/INDEX(" & TotDaysRng & "," & CCbeg & "):INDEX(" & TotDaysRng & "," & CCend & ")*INDEX(" & MonthesRng & "," & CCbeg & "," & Mon & "):INDEX(" & MonthesRng & "," & CCend & "," & Mon & "))"
  Rslt1stCol.Offset(,  3 ).FillDown
  Rslt1stCol.Offset(,  3 ) = Rslt1stCol.Offset(,  3 ).Value
  Rslt1stCol.Offset(,  4 ).Resize(,  4 ).ClearContents
  CCuniq.Resize(CCuniq.Rows.Count +  1 ,  2 ).ClearContents
End With
[Time_] = Timer - StartT
End Sub
...
Рейтинг: 0 / 0
06.12.2008, 03:40:19
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35697524
A.Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.Nik
Гость
Что-то я засомневался в хорошей скорости работы того варианта для большой таблицы. Посему предлагаю оптимизированный вариант. Мой прогноз для таблицы в 1000 строк - пару десятых долей секунды :-)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
Private Sub CommandButton1_Click()
Dim CCuniq As Range, Rslt1stCol As Range, StartT As Single
'TmpArr = Application.Transpose(Application.Transpose([transpose(offset(a3,q4:q5,))]))
StartT = Timer
Application.ScreenUpdating = False
Range([cc], [Acc]).Sort Key1:=[cc].Resize( 1 ), Order1:=xlAscending, Header:=xlYes
With WorksheetFunction
  TmpArr = .Transpose(Evaluate("IF(" & [cc].Address & "=" & [cc].Offset(- 1 ).Address & ","""",row(" & [cc].Address & "))"))
  TmpArr = Split(.Trim(Join(TmpArr, " ")), " ")
  CCuCnt% = UBound(TmpArr) +  1 
  Set CCuniq = [Acc].Offset( 1 , [columns(Acc)]).Resize(CCuCnt,  1 )
  CCuniq = .Transpose(TmpArr)
  CCuniq.Offset(CCuCnt).Resize( 1 ) = [cc].Row + [rows(cc)]
  CCuniq.Offset(,  1 ).FormulaArray = "=index(" & [cc].EntireColumn.Address & "," & CCuniq.Address & ")"
  
  RsltTblLen =  12  * CCuCnt * [columns(Acc)]
  Set Rslt1stCol = [Resoult].Resize(RsltTblLen)
  'Acc
  Rslt1stCol = .Transpose(Split(Replace(Join(Evaluate("Transpose(Transpose(REPT(Acc&"" ""," & CCuCnt *  12  & ")))"), ""), "Acc ", ""), " "))
  'CC
  [Resoult].Offset(,  1 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT(" & CCuniq.Offset(,  1 ).Address & "&"" "",12))"), ""), " "))
  [Resoult].Offset(,  1 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  1 )
  'Monthes
  [Resoult].Offset(,  2 ).Resize( 12 ) = .Transpose(Array("Jen", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
  [Resoult].Offset(,  2 ).Resize( 12 ).Copy [Rslt1stCol].Offset(,  2 )
  '1..12
  [Resoult].Offset(,  4 ).Resize( 12 ) = [row(a1:a12)]
  [Resoult].Offset(,  4 ).Resize( 12 ).Copy [Rslt1stCol].Offset(,  4 )
  '1..CC_unique_Count
  [Rslt1stCol].Offset(,  5 ) = .Transpose(Split(Join(Evaluate("Transpose(REPT(ROW(A1:A" & CCuCnt & ")&"" ""," & CCuCnt *  12  & "))"), ""), " "))
  'Beg Range
  [Resoult].Offset(,  6 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT(" & CCuniq.Address & "&"" "",12))"), ""), " "))
  [Resoult].Offset(,  6 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  6 )
  'End Range
  [Resoult].Offset(,  7 ).Resize( 12  * CCuCnt) = .Transpose(Split(Join(Evaluate("Transpose(REPT((" & CCuniq.Offset( 1 ).Address & "-1)&"" "",12))"), ""), " "))
  [Resoult].Offset(,  7 ).Resize( 12  * CCuCnt).Copy [Rslt1stCol].Offset(,  7 )
  'Cost
  ShName$ = [Acc].Parent.Name & "!"
  AccRng$ = ShName$ & [Acc].EntireColumn.Address
  TotDaysRng$ = ShName$ & [cc].Offset(,  13 ).EntireColumn.Address
  MonthesRng$ = ShName$ & [cc].Offset(,  1 ).Resize(,  12 ).EntireColumn.Address
  Mon$ = [Resoult].Offset(,  4 ).Address( 0 ,  0 )
  AccCnt$ = [Resoult].Offset(,  5 ).Address( 0 ,  0 )
  CCbeg$ = [Resoult].Offset(,  6 ).Address( 0 ,  0 )
  CCend$ = [Resoult].Offset(,  7 ).Address( 0 ,  0 )
  [Resoult].Offset(,  3 ).FormulaArray = "=SUM(INDEX(" & AccRng & "," & CCbeg & "," & AccCnt & "):INDEX(" & AccRng & "," & CCend & "," & AccCnt & ")/INDEX(" & TotDaysRng & "," & CCbeg & "):INDEX(" & TotDaysRng & "," & CCend & ")*INDEX(" & MonthesRng & "," & CCbeg & "," & Mon & "):INDEX(" & MonthesRng & "," & CCend & "," & Mon & "))"
  Rslt1stCol.Offset(,  3 ).FillDown
  Rslt1stCol.Offset(,  3 ) = Rslt1stCol.Offset(,  3 ).Value
  Rslt1stCol.Offset(,  4 ).Resize(,  4 ).ClearContents
  CCuniq.Resize(CCuniq.Rows.Count +  1 ,  2 ).ClearContents
End With
[Time_] = Timer - StartT
End Sub
...
Рейтинг: 0 / 0
06.12.2008, 03:42:50
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35700742
A-Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A-Nik
Гость
Нашёл один баг и одну ошибку в алгоритме. После работы над одибками решил проверить на условных данных - 1000 строк, 30СС и 12 Асс :-) Как я и предполагал, время выполнения - пару десятых секунды :-) Мой комп справляется чуть менее, чем за 0,5 с.
...
Рейтинг: 0 / 0
08.12.2008, 17:33:17
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35700802
A-Nik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A-Nik
Гость
KL, только не молчи! Скажи свой отзыв ! Ведь для тебя старался ! :-) Пошло, не пошло, понравилось, не понравилось ну и, самое главное, время выполнения на твоих данных ;-)
...
Рейтинг: 0 / 0
08.12.2008, 17:52:50
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35700989
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
A-Nik,

Спасибо, посмотрю на досуге. На этой неделе времени вообще нет - зашиваюсь :-)
...
Рейтинг: 0 / 0
08.12.2008, 19:28:54
| Ответить | Цитировать | Написать  
22 сообщений из 47, страница 2 из 2
  2  все
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Сводная таблица в Экселе - Строки вместо чисел
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=35452088&tid=2180033]:
 0ms
get settings:
 6ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 179ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 189ms
total:  438ms
созд. / загр.: 438ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]