ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Сводная таблица в Экселе - Строки вместо чисел
25 сообщений из 47, страница 1 из 2
  1  все
Сводная таблица в Экселе - Строки вместо чисел
    #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
| Ответить | Цитировать | Написать  
25 сообщений из 47, страница 1 из 2
  1  все
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Сводная таблица в Экселе - Строки вместо чисел
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=35379039&tid=2180033]:
 0ms
get settings:
 6ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 170ms
get topic data:
 29ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 187ms
total:  456ms
созд. / загр.: 456ms
x
x
Закрыть


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