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

как научитьбся выводить в сводную таблицу в Экселе не цифровые значения аггрегированных функций, а текстовые значения?

IDName1YearName22Борлинг0Свидерский2Борлинг2Тюпаев1Дорофеев2Веретильная1Дорофеев2Григорьев1Дорофеев1Ларкомб1Дорофеев3Пчелинцева

По стрчокам идут Name1, по столбцам Года, а в пересечении Фамилии.
...
Рейтинг: 0 / 0
18.06.2008, 10:15:28
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35380569
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
up
...
Рейтинг: 0 / 0
18.06.2008, 15:48:48
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35380651
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
ZalagaevКоллеги,

как научитьбся выводить в сводную таблицу в Экселе не цифровые значения аггрегированных функций, а текстовые значения?

IDName1YearName22Борлинг0Свидерский2Борлинг2Тюпаев1Дорофеев2Веретильная1Дорофеев2Григорьев1Дорофеев1Ларкомб1Дорофеев3Пчелинцева

По стрчокам идут Name1, по столбцам Года, а в пересечении Фамилии.

Не думаю, что это возможно в такой постановке.
...
Рейтинг: 0 / 0
18.06.2008, 16:09:03
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35381219
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Zalagaevкак научитьбся выводить в сводную таблицу в Экселе не цифровые значения аггрегированных функций, а текстовые значения?Сначала объясни чего тебе надо. Исходную таблицу ты показал - молодец, теперь покажи как должна выглядеть итоговая сводная.
...
Рейтинг: 0 / 0
18.06.2008, 19:18:34
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35381389
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
White Owl...теперь покажи как должна выглядеть итоговая сводная.

По-моему, он ее вполне ясно описал на словах:

0123БорлингСвидерскийТюпаевДорофеевВеретильнаяГригорьевЛаркомбПчелинцева
...
Рейтинг: 0 / 0
18.06.2008, 21:48:00
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35381420
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Ну я не знаю.... Мне кажется что единственная сводная которая может иметь смысл на этих данных будет такого типа:
Count of Name2 Year Name1 Name2 0 1 2 3 Grand TotalБорлинг Свидерский 1 1 Тюпаев 1 1Борлинг Total 1 1 2Дорофеев Веретильная 1 1 Григорьев 1 1 Ларкомб 1 1 Пчелинцева 1 1Дорофеев Total 1 2 1 4Grand Total 1 1 3 1 6
...
Рейтинг: 0 / 0
18.06.2008, 22:24:03
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35395616
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
KL (XL) White Owl...теперь покажи как должна выглядеть итоговая сводная.

По-моему, он ее вполне ясно описал на словах:

0123БорлингСвидерскийТюпаевДорофеевВеретильнаяГригорьевЛаркомбПчелинцева

вот это именно то, что мне нужно!!!!! В аксе такое я делаю легко перекрестным запросом, причем даже ячейки объединяются очень удобно, а при переносе данных в Эксель автоматически .. получаются только цифры...
...
Рейтинг: 0 / 0
26.06.2008, 10:51:40
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35395707
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
Zalagaev[quot KL (XL)][quot White Owl]В аксе такое я делаю легко перекрестным запросом, причем даже ячейки объединяются очень удобно, а при переносе данных в Эксель автоматически .. получаются только цифры...
Так мож как-то через QueryTable сделать, если сильно нада?... Если данные из этого же файла, можно макросом прописывать его путь в параметрах...
...
Рейтинг: 0 / 0
26.06.2008, 11:12:40
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35396015
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
В аксе у меня перекрестный запрос. Когда я использую Внешние данные в Экселе, то этот запрос возвршается не в виде таблицы перекрестного запроса с уже подставленными значениями, а как обычный, еще не сведенный запрос. Вот поэтому я и спрашивал, как в Экселе сделать нормальную Сводную таблицу с текстовыми значениями на перекрещении столбика и ряда.
...
Рейтинг: 0 / 0
26.06.2008, 12:24:06
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35396179
Deggasad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deggasad
Участник
ZalagaevВ аксе у меня перекрестный запрос. Когда я использую Внешние данные в Экселе, то этот запрос возвршается не в виде таблицы перекрестного запроса с уже подставленными значениями, а как обычный, еще не сведенный запрос. Вот поэтому я и спрашивал, как в Экселе сделать нормальную Сводную таблицу с текстовыми значениями на перекрещении столбика и ряда.
1) формулами
2) макросами
...
Рейтинг: 0 / 0
26.06.2008, 13:02:28
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35397326
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
ZalagaevКогда я использую Внешние данные в Экселе, то этот запрос возвршается не в виде таблицы перекрестного запроса с уже подставленными значениями, а как обычный, еще не сведенный запрос.
А у меня получилось. "Данные/Импорт данных/Импортировать внешние данные..." - создаю QueryTable. Потом правой кнопкой - "Изменить запрос". Выбираю тип SQL и ввожу:
Код: plaintext
TRANSFORM First(Value) AS [First-Value] SELECT Row FROM [Лист1$] GROUP BY Row PIVOT Column;
. Все работает так, как Вы хотите...
...
Рейтинг: 0 / 0
26.06.2008, 17:39:00
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35397333
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
Ах, да... Row, Сolumn и Value - это поля таблицы на Лист1
...
Рейтинг: 0 / 0
26.06.2008, 17:40:41
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35397508
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyberВсе работает так, как Вы хотите...

Не совсем ;-) Григорьев убирается как ненужный свидетель, за неимением своей уникальной комбинации Name1 и Year (Дорофеев-2 уже занят Веретильной). Но мне понравилось.

KL
[MVP - Microsoft Excel]
...
Рейтинг: 0 / 0
26.06.2008, 18:49:48
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35398294
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
А, ну да... действительно убирается - из-за функции First().

Автор пишет, что он такое делал в Access перекрестным запросом. Вот мне интересно - как? У меня он упорно хочет статистическую функцию для поля-значения.

Другое дело, если в Access воспользоваться сводной таблицей... Но все равно вид получается чуток не такой, как было приведено...
...
Рейтинг: 0 / 0
27.06.2008, 11:36:47
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35398348
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
Ой, сам догадался...
Код: plaintext
TRANSFORM First(Data.Name2) AS [First-Name2] SELECT Data.Name1 FROM [Data$] AS Data GROUP BY Data.Name1, Data.Name2 PIVOT Data.Year;

Все работает!!!
...
Рейтинг: 0 / 0
27.06.2008, 11:52:02
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35399194
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
kyber ZalagaevКогда я использую Внешние данные в Экселе, то этот запрос возвршается не в виде таблицы перекрестного запроса с уже подставленными значениями, а как обычный, еще не сведенный запрос.
А у меня получилось. "Данные/Импорт данных/Импортировать внешние данные..." - создаю QueryTable. Потом правой кнопкой - "Изменить запрос". Выбираю тип SQL и ввожу:
Код: plaintext
TRANSFORM First(Value) AS [First-Value] SELECT Row FROM [Лист1$] GROUP BY Row PIVOT Column;
. Все работает так, как Вы хотите...

да. спасибо. я что-то не заметил, где в Экселе можно было поменять запрос до нужного вида. еще раз - спасибо.
...
Рейтинг: 0 / 0
27.06.2008, 16:07:45
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35399211
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyberОй, сам догадался...
Код: plaintext
TRANSFORM First(Data.Name2) AS [First-Name2] SELECT Data.Name1 FROM [Data$] AS Data GROUP BY Data.Name1, Data.Name2 PIVOT Data.Year;

Все работает!!!

Красиво!
...
Рейтинг: 0 / 0
27.06.2008, 16:13:46
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35447624
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
Коллеги, что-то мне теперь это не очень нравится. Слышком много пустых ды, как их можно упорядочить? В данном примере на Донлинка тратится 3 строчки, а на Ёорофеева целых 4, вместо 1 строчки на Донлинка и 2-х строчек на Ёорофеева.

Zalagaev KL (XL) White Owl...теперь покажи как должна выглядеть итоговая сводная.


0123ДонлинкКвидерскийХрюпаевЁорофеевИвановГригорьевКозлоффПивнева
...
Рейтинг: 0 / 0
23.07.2008, 16:19:34
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35448427
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
Мдя, много вы хотите :)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
TRANSFORM First(Tab.Name2)
SELECT Tab.Name1
FROM

(SELECT D1.Name1, D2.Year, D2.Name2, Count(*) As [RowId]
FROM [Data$] As D1 INNER JOIN [Data$] As D2 ON D1.Name2<=D2.Name2
WHERE D1.Name1 = D2.Name1 And D1.Year= D2.Year
GROUP BY D1.Name1, D2.Year, D2.Name2) As Tab

GROUP BY Tab.Name1, Tab.RowId
PIVOT Tab.Year;

Вроде все работает как надо, особенно если в базу не вводить дубликаты строк.

P.S. Выложу завтра примерчик в формате Excel 2003 - дома стоит 2007 и пытается запихнуть QueryTable внутрь ListObject... Думаю, возможны проблемы с совместимостью обновляющего макроса.
...
Рейтинг: 0 / 0
24.07.2008, 00:32:18
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35448488
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
2 kyber ,

Я вижу у тебя классно получаются SQL запросы :-) Может поможешь, если не сложно?

Дано:
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
Сам пока допетрил только до такого (запрос используется в ADO):

Последовательно создаю запрос для каждого Acc. в таком виде (все ссылки представлены ввиде констант для наглядности):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT `CC`, 
SUM(`Acc.  5115 ` /  `Total Days` * Jan) AS Acc. 5115_Jan, 
SUM(`Acc.  5115 ` /  `Total Days` * Feb) AS Acc. 5115_Feb, 
SUM(`Acc.  5115 ` /  `Total Days` * Mar) AS Acc. 5115_Mar, 
SUM(`Acc.  5115 ` /  `Total Days` * Apr) AS Acc. 5115_Apr, 
SUM(`Acc.  5115 ` /  `Total Days` * May) AS Acc. 5115_May, 
SUM(`Acc.  5115 ` /  `Total Days` * Jun) AS Acc. 5115_Jun, 
SUM(`Acc.  5115 ` /  `Total Days` * Jul) AS Acc. 5115_Jul, 
SUM(`Acc.  5115 ` /  `Total Days` * Aug) AS Acc. 5115_Aug, 
SUM(`Acc.  5115 ` /  `Total Days` * Sep) AS Acc. 5115_Sep, 
SUM(`Acc.  5115 ` /  `Total Days` * Oct) AS Acc. 5115_Oct, 
SUM(`Acc.  5115 ` /  `Total Days` * Nov) AS Acc. 5115_Nov, 
SUM(`Acc.  5115 ` /  `Total Days` * Dec) AS Acc. 5115_Dec 
FROM [Data$L10:CT12] GROUP BY `CC`

полученные рекордсеты (см. ниже) поочередно сливаю на временный лист
5115_Jan 5115_Feb 5115_Mar 5115_Apr 5115_May 5115_Jun 5115_Jul 5115_Aug 5115_Sep 5115_Oct 5115_Nov 5115_Dec02MARC 0 0 0 0 4.292 4.154 4.292 4.292 4.154 4.292 4.154 4.29212FFR3 0 0 0 0 6.296 6.093 6.296 6.296 6.093 6.296 6.093 6.296
и потом транспонирую с пом. цикла в нужный формат.

Наверняка все проще гораздо :-)

Заранее спасибо!

ЗЫ: отдельно для White Owl . Это конечно прекрасный повод поглумиться над моим невежеством в SQL, но за дельные советы буду крайне признателен.
...
Рейтинг: 0 / 0
24.07.2008, 02:45:50
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35448756
Zalagaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Zalagaev
Участник
c нетерпением жду продолжения!
...
Рейтинг: 0 / 0
24.07.2008, 10:22:14
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35449036
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
KL (XL)2 kyber ,
Я вижу у тебя классно получаются SQL запросы :-)
Я просто польщен - от тебя такое услышать :)

KL (XL)Может поможешь, если не сложно?
Очень интересно, попробую...

P.S. "Cost", "CC" (Cost-center???), количество дней... Постановка задачи, случаем, называется не Activity Based Costing?
...
Рейтинг: 0 / 0
24.07.2008, 11:43:02
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35449099
KL (XL)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KL (XL)
Участник
kyberP.S. "Cost", "CC" (Cost-center???), количество дней... Постановка задачи, случаем, называется не Activity Based Costing?
Наверное :-) Это не мое, а для приятеля. Я ему написал на скорую руку макрос который превращает его Excel с бюджетом по з/п, бонусу и соц. льготам в текстовый файл экспортируемый потом в SAP или что-то в этом роде. CC - это действительно Cost-center, Acc. - это Cost-account.
Спасибо.
...
Рейтинг: 0 / 0
24.07.2008, 11:57:54
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35449463
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
kyberP.S. Выложу завтра примерчик в формате Excel 2003 - дома стоит 2007 и пытается запихнуть QueryTable внутрь ListObject... Думаю, возможны проблемы с совместимостью обновляющего макроса.
Как и обещал, выкладываю...
...
Рейтинг: 0 / 0
24.07.2008, 13:40:03
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #35450591
kyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kyber
Гость
KL (XL)Наверняка все проще гораздо :-)
Вот такой вариант в Access может чем-то помочь? (см. запрос Output)

А вообще, исходные данные в очень неудобном виде...
...
Рейтинг: 0 / 0
24.07.2008, 19:07:54
| Ответить | Цитировать | Написать  
Сводная таблица в Экселе - Строки вместо чисел
    #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
| Ответить | Цитировать | Написать  
47 сообщений из 47, показаны все 2 страниц
  все  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Сводная таблица в Экселе - Строки вместо чисел
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=61&tid=2180033]:
 0ms
get settings:
 6ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 199ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 185ms
total:  469ms
созд. / загр.: 469ms
x
x
Закрыть


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