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

У меня есть сводная таблица, которая сворачивает данные по категориям, высчитывая промежуточные суммы. По одному из полей мне нужно показать итоговые значения по строкам, используя не сумму, а среднее.

В примерах:

Развернутая таблица:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
                                                  Дата   1   |  Дата   2   |  Дата   3   |  Дата   4         
Категория     Подкатегория    Подподкатегория     А     Б  |  А     Б  |  А     Б  |  А     Б
=============================================================================================
    А              АА               ААА            2       1   |   3       1   |   4       1   |   3       1 
    А              АБ               АБА            2       1   |   3       1   |   4       1   |   3       1 
    А              АБ               АББ            4       1   |   3       1   |   5       1   |   3       1 
    А              АВ               АВА            4       1   |   3       1   |   5       1   |   3       1 
    Б              БА               БАА            6       1   |   5       1   |   6       1   |   5       1 
    Б              БА               БАБ            6       1   |   5       1   |   6       1   |   5       1 
    Б              ББ               ББА            8       1   |   5       1   |   7       1   |   5       1 
    В              ВА               ВАА            8       1   |   5       1   |   7       1   |   5       1

Если ее свернуть, данные группируются, и высчитываются суммы:
Код: plaintext
1.
2.
3.
4.
5.
              Дата   1   |  Дата   2   |  Дата   3   |  Дата   4         
Категория     А     Б  |  А     Б  |  А     Б  |  А     Б
=========================================================
    А         12       4   |  12       4   |  18       4   |  12       4 
    Б         20       3   |  15       3   |  19       3   |  15       3 
    В          8       1   |   5       1   |   7       1   |   5       1

Теперь, если я добавляю общий итог для строк, эксель показывает суммы по полям по всем датам:
Код: plaintext
1.
2.
3.
4.
5.
                Итоги     
Категория      А     Б 
======================
    А          54      16  
    Б          69      12  
    В          25       4

Но мне нужно, чтобы итоги по колонке А рассчитались как среднее (сумма деленная на 4 в данном случае):
Код: plaintext
1.
2.
3.
4.
5.
                 Итоги     
Категория      А       Б 
========================
    А          13 . 5      16  
    Б          17 . 25     12  
    В           6 . 25      4

Если задать среднее всему полю, то оно будет высчитываться и в промежуточных итогах, а это плохо.

Как быть?
...
Рейтинг: 0 / 0
28.09.2010, 12:24
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869733
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К.
Как быть?Здравствуйте.
Добавить ещё одно поле. Но в промежуточных итогах всегда будет та операция, которую Вы выбрали для всего поля.
...
Рейтинг: 0 / 0
28.09.2010, 12:53
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869808
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Ммм.. А что положить в это новое поле?
...
Рейтинг: 0 / 0
28.09.2010, 13:20
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869862
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К.Ммм.. А что положить в это новое поле?Тоже самое что и в имеющемся, но задать вычисления - "Среднее".
...
Рейтинг: 0 / 0
28.09.2010, 13:40
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869877
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Но тогда в итогах будет показано среднее от средних значений подгрупп за все даты, а мне нужно среднее от их суммы. Т.е. сумма деленная на количество дат.
...
Рейтинг: 0 / 0
28.09.2010, 13:46
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869954
PlanB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PlanB
Участник
вычисляемые поля?
...
Рейтинг: 0 / 0
28.09.2010, 14:07
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36869996
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К.Но тогда в итогах будет показано среднее от средних значений подгрупп за все даты, а мне нужно среднее от их суммы. Т.е. сумма деленная на количество дат.Ничего не понял. Давайте пример.
...
Рейтинг: 0 / 0
28.09.2010, 14:22
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870090
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Пример:

Если добавить поле, в котором считать среднее, то развернутой таблице, итоги будут правильные:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
                                                  Дата   1   |  Дата   2   |  Дата   3   |  Дата   4    |   Итоги        
Категория     Подкатегория    Подподкатегория     А     Б  |  А     Б  |  А     Б  |  А     Б   |  А     Б
===========================================================================================================
    А              АА               ААА            2       1   |   3       1   |   4       1   |   3       1    |   3       1 
    А              АБ               АБА            2       1   |   3       1   |   4       1   |   3       1    |   3       1 
    А              АБ               АББ            4       1   |   3       1   |   5       1   |   3       1    |   3 . 75    1 
    А              АВ               АВА            4       1   |   3       1   |   5       1   |   3       1    |   3 . 75    1 
    Б              БА               БАА            6       1   |   5       1   |   6       1   |   5       1    |   5 . 5     1 
    Б              БА               БАБ            6       1   |   5       1   |   6       1   |   5       1    |   5 . 5     1 
    Б              ББ               ББА            8       1   |   5       1   |   7       1   |   5       1    |   6 . 25    1 
    В              ВА               ВАА            8       1   |   5       1   |   7       1   |   5       1    |   6 . 25    1

Но если ее свернуть, то в итогах будет среднее от среднего (не знаю, как иначе сказать)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
    
              Дата   1   | Дата   2   | Дата   3   | Дата   4   |   Итоги        
Категория    А      Б  | А     Б  | А     Б  | А     Б  | А       Б
===================================================================
    А         3        4   |  3       4   |  4 . 5     4   |  3       4   |  3 . 375     4 
    Б         6 . 66     3   |  5       3   |  6 . 3     3   |  5       3   |  5 . 74      3 
    В         8        1   |  5       1   |  7       1   |  5       1   |  6 . 25      1

А хочется, чтобы было как в примере из первого поста:
Код: plaintext
1.
2.
3.
4.
5.
                 Итоги     
Категория      А       Б 
========================
    А          13 . 5      16  
    Б          17 . 25     12  
    В           6 . 25      4

Т.е. среднее от суммы

PS: почему, блин, в тему сообщения лезет этот смайлик? Я же его не ставлю..
...
Рейтинг: 0 / 0
28.09.2010, 14:51
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870168
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
Петр К.,

Это называется различный расчет метрик на разном уровне.
Excel для этого туповат, если особо хитрую формулу не придумаете, то никак.

Олап кубики или другие инструменты анализа.
...
Рейтинг: 0 / 0
28.09.2010, 15:18
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870381
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Ок, а если с другого конца.

Допустим, даты у меня лежат в фильтре.
Как узнать, какое количество элементов фильтра выбрано?
...
Рейтинг: 0 / 0
28.09.2010, 16:11
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870384
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К. , Вы пример выложите?
У меня скоро рабочий день заканчивается.
...
Рейтинг: 0 / 0
28.09.2010, 16:12
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870523
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Serge 007, так ведь выложил же.
Или вас интересует сама экселька?
...
Рейтинг: 0 / 0
28.09.2010, 16:53
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870564
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
На всякий случай прицепил эксельку.

Ничего нового там, впрочем, нет.
...
Рейтинг: 0 / 0
28.09.2010, 17:07
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870567
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К.Serge 007, так ведь выложил же.
Или вас интересует сама экселька?А Вас интересует картинка на форуме или решение в xls?!
...
Рейтинг: 0 / 0
28.09.2010, 17:07
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870588
Фотография Serge 007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serge 007
Участник
Петр К.Ничего нового там, впрочем, нет.
1. В первом посте Вы нарисовали такую желаемую табличку:
автор Итоги
Категория А Б
========================
А 13.5 16
Б 17.25 12
В 6.25 4

Объясните, каким образом появилось значение 13.5?
В примере так не получается.

2. Доп. столбцы в исходнике использовать можно?
...
Рейтинг: 0 / 0
28.09.2010, 17:17
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36870728
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
> Объясните, каким образом появилось значение 13.5
Это сумма итоговых данных по группе деленная на количество дней, т.е.:
(12 + 12 + 18 + 12) / 4 = 13.5
(20 + 15 + 19 + 15) / 4 = 17.25
( 8 + 5 + 7 + 5) / 4 = 6.25


> Доп. столбцы в исходнике использовать можно?
Все, что угодно.
...
Рейтинг: 0 / 0
28.09.2010, 18:13
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36871436
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
Петр К.,

все таки если бы выложили Xls с частью таблицы и исх данными было бы всем счастье
...
Рейтинг: 0 / 0
29.09.2010, 08:41
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36871591
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Shamanus, выложил выше.

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

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

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

Прицепил пример того, что я имею ввиду. На листе, который называется "Например" есть поле "Среднее от суммы", которое я руками делю на 3, потому-что в исходных данных три даты.
Как сделать так, чтобы если пользователь в фильтре указал две даты, поле "Среднее от суммы" делилось на два?
...
Рейтинг: 0 / 0
29.09.2010, 10:25
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36871657
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
Петр К.,

уточнение, верно ли я понял что сейчас Вы видите Значения в красном прямоугольнике, а хотели бы видеть то, что в зеленом?

Макросы пользовать можно или только с исходными данными играть?
...
Рейтинг: 0 / 0
29.09.2010, 10:50
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36871682
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Shamanus, совершенно верно.
Итог по одной колонке как в красном прямоугольнике, а по другой - как в зеленом.
...
Рейтинг: 0 / 0
29.09.2010, 10:57
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36873675
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Serge 007, Shamanus, зачем вы исчезли? :)
Господа, поделитесь мыслями.
...
Рейтинг: 0 / 0
30.09.2010, 10:02
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36874090
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
Петр К.,

нечем делиться
решения на уровне входных данных я не нашел пока, только 2 сводные таблицы и макрос по скрытию столбцов
...
Рейтинг: 0 / 0
30.09.2010, 12:08
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36874379
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Shamanus, если это решает задачу, то почему бы и нет.

А можно написать макрос, который бы считал количество выбранных дат в фильтре, и подставлял это значение в вычисляемое поле сводной таблицы?
...
Рейтинг: 0 / 0
30.09.2010, 13:29
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36874432
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
Петр К.,

ну это легко
...
Рейтинг: 0 / 0
30.09.2010, 13:52
| Ответить | Цитировать | Написать  
Excel 2007, сводные таблицы, итоги
    #36874463
Петр К.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Петр К.
Гость
Shamanus, вот здесь "If .PivotItems(i).Visible = True Then" говорит "Type mismatch".
...
Рейтинг: 0 / 0
30.09.2010, 14:03
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Excel 2007, сводные таблицы, итоги
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&fpage=148&tid=2177592]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 66ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 237ms
total:  393ms
созд. / загр.: 393ms
x
x
Закрыть


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