Изменение диапазона в сводной таблице - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Изменение диапазона в сводной таблице - Мир MS Excel

Старая форма входа

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_

Изменение диапазона в сводной таблице (Pivot Table Сводные таблицы)

Изменение диапазона в сводной таблице

Дата: Понедельник, 19.08.2013, 15:52 | Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Создал много сводных таблиц, которые используют данные Лист1, количество строк в котором каждый день увеличивается. Когда создавал, в форме где спрашивался диапазон и на каком листе создать, щелкал далее.
И только потом дошло, что новые строки с Лист1 в сводную таблицу не попадут. Где в сводной таблице можно поправить диапазон?

Ответить

Дата: Понедельник, 19.08.2013, 16:04 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Нашел:
- Работа со сводными таблицами
- Параметры
- Изменить источник данных

Ответить

Дата: Понедельник, 19.08.2013, 16:13 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Используйте таблицы Ctrl+T в версиях выше 2003 или списки в версиях ниже 2007 или динамически именованые диапазоны во всех версиях

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Вторник, 20.08.2013, 01:13 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16691

Репутация: 6491 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Или можно использовать динамический диапазон (см. Вставка - Имя). Для 2007 и выше - Формулы - Диспетчер имен
А через Параметры Вы
1. замучаетесь каждый раз менять
2. когда-нибудь поменять возможно вообще забудете (я так один раз лет 10 назад людям зарплату посчитал - во крику-то было!)

К сообщению приложен файл: Din_diap_cv_tab.xls (20.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Среда, 21.08.2013, 01:29 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)

замучаетесь каждый раз менять

Одной замены достаточно, указав в качестве диапазона все столбцы, например $A:$Z
На скорость выполнения не повлияло. Но есть и недостаток, в сводной таблице появляется подгруппа "пусто".

Ответить

Дата: Среда, 21.08.2013, 01:35 | Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)

Или можно использовать динамический диапазон (см. Вставка - Имя).

не нашел Вставка - Имя
Excel 2007

Ответить

Дата: Среда, 21.08.2013, 10:17 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Начиная с 1 января за каждый рабочий день добавляется примерно 500 строк.

Ответить

Дата: Среда, 21.08.2013, 10:20 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата (SergeyKorotun, 21.08.2013 в 01:29, в сообщении №5)

указав в качестве диапазона все столбцы, например $A:$Z. На скорость выполнения не повлияло

Чем больше данных в указанном диапазоне, тем больше будут видны "тормоза"

Цитата (_Boroda_, 21.08.2013 в 01:43, в сообщении №7)

Для 2007 и выше - Формулы - Диспетчер имен

Добавлю: Для всех версий Ctrl+F3

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 21.08.2013, 11:22 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Цитата (Serge_007, 21.08.2013 в 10:20, в сообщении №9)

Добавлю: Для всех версий Ctrl+F3

Это я знаю, а как создать динамический диапазон, про который писал _Boroda_

Ответить

Дата: Среда, 21.08.2013, 11:25 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Что значит "Как создать"? Саша же выложил файл, жмите Ctrl+F3 и смотрите формулу динамического диапазона...

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 21.08.2013, 12:02 | Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

В случае незаполненных некоторых ячеек в столбце диапазон выделяется неверно.
Можно СЧЁТЗ заменить чем нибудь другим. Например:
Sheets(1).UsedRange.Rows.Count
Sheets(1).UsedRange.Columns.Count
И как создать динамический диапазон макросом?

К сообщению приложен файл: 9102280.xls (20.5 Kb)

Ответить

Дата: Среда, 21.08.2013, 12:20 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

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

К сообщению приложен файл: SergeyKorotun-P.xls (58.0 Kb)

Ответить

Дата: Среда, 21.08.2013, 12:22 | Сообщение № 13

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Цитата (SergeyKorotun, 21.08.2013 в 12:02, в сообщении №12)

И как создать динамический диапазон макросом?

Элементарно.
Включаем макрорекордер и пишем
[vba]

Код

Sub Макрос2()
'
' Макрос2 Макрос
'

'
     ActiveWorkbook.Names.Add Name:="табл_", RefersToR1C1:= _
         "=OFFSET(Лист1!R1C1,,,COUNTA(Лист1!C1),COUNTA(Лист1!R1))"
     ActiveWorkbook.Names("табл_").Comment = ""
End Sub

[/vba]

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Среда, 21.08.2013, 12:37 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

Если макросом, то как-то так.
[vba]

Код

Sub NameAdd()
ActiveWorkbook.Names("Табл_").RefersToR1C1 = "=" & Sheets(1).Name & "!" & _
Sheets(1).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
End Sub

[/vba]

Ответить

Дата: Среда, 21.08.2013, 12:51 | Сообщение № 15

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Цитата (Michael_S, 21.08.2013 в 12:20, в сообщении №13)

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

То я специально удалил, чтобы продемонстрировать ошибку выделения.
Michael_S, ваш формула

Код

=СМЕЩ(Лист1!$A$1;;;ПРОСМОТР("яяя";Лист1!$A:$A;СТРОКА(Лист1!$A:$A));СЧЁТЗ(Лист1!$1:$1))

не всегда срабатывает, смотрите приложенный файл.
Макросы еще не тестировал.

К сообщению приложен файл: 5782406.xls (27.5 Kb)

Ответить

Дата: Среда, 21.08.2013, 13:04 | Сообщение № 16

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

SergeyKorotun, ваши примеры выходят из пределов разумной организации данных.

Ответить

Дата: Среда, 21.08.2013, 13:09 | Сообщение № 17

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Сводные таблицы предназначены для работы с данными, оформленными в виде списка (таблицы), т.е. фактически - базой данных

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 21.08.2013, 13:17 | Сообщение № 18

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

RAN, добавьте в ваш макрос Range("табл_").Select и на присоединенном файле в 16 сообщении увидите, что диапазон выделяется неверно.

Цитата (Michael_S, 21.08.2013 в 13:04, в сообщении №17)

SergeyKorotun, ваши примеры выходят из пределов разумной организации данных

Таблицы разные бывают. И правильнее будет использовать в качестве ранга все столбцы. А пример мне _Boroda_ подбросил, я только удалил значения из нескольких ячеек.

Ответить

Дата: Среда, 21.08.2013, 13:29 | Сообщение № 19

Группа: Админы

Ранг: Местный житель

Сообщений: 16691

Репутация: 6491 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата (SergeyKorotun, 21.08.2013 в 13:17, в сообщении №19)

А пример мне _Boroda_

Понятно. То есть я за Вас Ваш пример нарисовал и я же виноват и оказался. Извините, больше так не буду. По крайней мере, с Вашими вопросами.

Цитата (SergeyKorotun, 21.08.2013 в 13:17, в сообщении №19)

Таблицы разные бывают. И правильнее будет использовать в качестве ранга все столбцы.

Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Среда, 21.08.2013, 18:42 | Сообщение № 20

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация: 15 ±

Замечаний: 0% ±

Excel 2007

Цитата (_Boroda_, 21.08.2013 в 13:29, в сообщении №20)

Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.

Мы то в курсе. Но "нормальные таблицы" хранят не в екселе и юзер прямого доступа к базам не имеет.
От удалит он случайно одно значение в ключевом поле (в нашем случае в столбце А) и если помечать диапазон, а не столбцы для сводной таблицы, он за каждый новый день в сводной таблице будет терять данные из последней строки. В случае выбора столбцов в качестве диапазона он получит неверные данные в сводной таблице только за 1 день.

Ответить

Изменение диапазона в сводной таблице (Pivot Table Сводные таблицы)

Страница 1 из 2
1
2
»

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!