Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Изменение диапазона в сводной таблице (Pivot Table Сводные таблицы)
Изменение диапазона в сводной таблице
SergeyKorotun Дата: Понедельник, 19.08.2013, 15:52 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Создал много сводных таблиц, которые используют данные Лист1, количество строк в котором каждый день увеличивается. Когда создавал, в форме где спрашивался диапазон и на каком листе создать, щелкал далее.
И только потом дошло, что новые строки с Лист1 в сводную таблицу не попадут. Где в сводной таблице можно поправить диапазон?
 
Ответить
СообщениеСоздал много сводных таблиц, которые используют данные Лист1, количество строк в котором каждый день увеличивается. Когда создавал, в форме где спрашивался диапазон и на каком листе создать, щелкал далее.
И только потом дошло, что новые строки с Лист1 в сводную таблицу не попадут. Где в сводной таблице можно поправить диапазон?
Автор - SergeyKorotun
Дата добавления - 19.08.2013 в 15:52
SergeyKorotun Дата: Понедельник, 19.08.2013, 16:04 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Нашел:
- Работа со сводными таблицами
- Параметры
- Изменить источник данных
 
Ответить
СообщениеНашел:
- Работа со сводными таблицами
- Параметры
- Изменить источник данных
Автор - SergeyKorotun
Дата добавления - 19.08.2013 в 16:04
Serge_007 Дата: Понедельник, 19.08.2013, 16:13 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

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

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеИспользуйте таблицы Ctrl+T в версиях выше 2003 или списки в версиях ниже 2007 или динамически именованые диапазоны во всех версиях
Автор - Serge_007
Дата добавления - 19.08.2013 в 16:13
_Boroda_ Дата: Вторник, 20.08.2013, 01:13 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеИли можно использовать динамический диапазон (см. Вставка - Имя). Для 2007 и выше - Формулы - Диспетчер имен
А через Параметры Вы
1. замучаетесь каждый раз менять
2. когда-нибудь поменять возможно вообще забудете (я так один раз лет 10 назад людям зарплату посчитал - во крику-то было!)
Автор - _Boroda_
Дата добавления - 20.08.2013 в 01:13
SergeyKorotun Дата: Среда, 21.08.2013, 01:29 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)
замучаетесь каждый раз менять

Одной замены достаточно, указав в качестве диапазона все столбцы, например $A:$Z
На скорость выполнения не повлияло. Но есть и недостаток, в сводной таблице появляется подгруппа "пусто".
 
Ответить
Сообщение
Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)
замучаетесь каждый раз менять

Одной замены достаточно, указав в качестве диапазона все столбцы, например $A:$Z
На скорость выполнения не повлияло. Но есть и недостаток, в сводной таблице появляется подгруппа "пусто".
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 01:29
SergeyKorotun Дата: Среда, 21.08.2013, 01:35 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)
Или можно использовать динамический диапазон (см. Вставка - Имя).

не нашел Вставка - Имя
Excel 2007
 
Ответить
Сообщение
Цитата (_Boroda_, 20.08.2013 в 01:13, в сообщении №4)
Или можно использовать динамический диапазон (см. Вставка - Имя).

не нашел Вставка - Имя
Excel 2007
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 01:35
SergeyKorotun Дата: Среда, 21.08.2013, 10:17 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Начиная с 1 января за каждый рабочий день добавляется примерно 500 строк.
 
Ответить
СообщениеНачиная с 1 января за каждый рабочий день добавляется примерно 500 строк.
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 10:17
Serge_007 Дата: Среда, 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
 
Ответить
Сообщение
Цитата (SergeyKorotun, 21.08.2013 в 01:29, в сообщении №5)
указав в качестве диапазона все столбцы, например $A:$Z. На скорость выполнения не повлияло
Чем больше данных в указанном диапазоне, тем больше будут видны "тормоза"

Цитата (_Boroda_, 21.08.2013 в 01:43, в сообщении №7)
Для 2007 и выше - Формулы - Диспетчер имен
Добавлю: Для всех версий Ctrl+F3
Автор - Serge_007
Дата добавления - 21.08.2013 в 10:20
SergeyKorotun Дата: Среда, 21.08.2013, 11:22 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата (Serge_007, 21.08.2013 в 10:20, в сообщении №9)
Добавлю: Для всех версий Ctrl+F3

Это я знаю, а как создать динамический диапазон, про который писал _Boroda_
 
Ответить
Сообщение
Цитата (Serge_007, 21.08.2013 в 10:20, в сообщении №9)
Добавлю: Для всех версий Ctrl+F3

Это я знаю, а как создать динамический диапазон, про который писал _Boroda_
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 11:22
Serge_007 Дата: Среда, 21.08.2013, 11:25 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

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

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЧто значит "Как создать"? Саша же выложил файл, жмите Ctrl+F3 и смотрите формулу динамического диапазона...
Автор - Serge_007
Дата добавления - 21.08.2013 в 11:25
SergeyKorotun Дата: Среда, 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)
 
Ответить
СообщениеВ случае незаполненных некоторых ячеек в столбце диапазон выделяется неверно.
Можно СЧЁТЗ заменить чем нибудь другим. Например:
Sheets(1).UsedRange.Rows.Count
Sheets(1).UsedRange.Columns.Count
И как создать динамический диапазон макросом?
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 12:02
Michael_S Дата: Среда, 21.08.2013, 12:20 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Для сводной в первой строке заголовки должны быть в каждой ячейке. У вас в примере одна пустая.
К сообщению приложен файл: SergeyKorotun-P.xls (58.0 Kb)
 
Ответить
СообщениеДля сводной в первой строке заголовки должны быть в каждой ячейке. У вас в примере одна пустая.
Автор - Michael_S
Дата добавления - 21.08.2013 в 12:20
RAN Дата: Среда, 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]

Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение
Цитата (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]
Автор - RAN
Дата добавления - 21.08.2013 в 12:22
Michael_S Дата: Среда, 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]
 
Ответить
СообщениеЕсли макросом, то как-то так.
[vba]
Код
Sub NameAdd()
     ActiveWorkbook.Names("Табл_").RefersToR1C1 = "=" & Sheets(1).Name & "!" & _
         Sheets(1).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
End Sub
[/vba]
Автор - Michael_S
Дата добавления - 21.08.2013 в 12:37
SergeyKorotun Дата: Среда, 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)
 
Ответить
Сообщение
Цитата (Michael_S, 21.08.2013 в 12:20, в сообщении №13)
Для сводной в первой строке заголовки должны быть в каждой ячейке. У вас в примере одна пустая.

То я специально удалил, чтобы продемонстрировать ошибку выделения.
Michael_S, ваш формула
Код
=СМЕЩ(Лист1!$A$1;;;ПРОСМОТР("яяя";Лист1!$A:$A;СТРОКА(Лист1!$A:$A));СЧЁТЗ(Лист1!$1:$1))
не всегда срабатывает, смотрите приложенный файл.
Макросы еще не тестировал.
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 12:51
Michael_S Дата: Среда, 21.08.2013, 13:04 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
SergeyKorotun, ваши примеры выходят из пределов разумной организации данных.
 
Ответить
Сообщение SergeyKorotun, ваши примеры выходят из пределов разумной организации данных.
Автор - Michael_S
Дата добавления - 21.08.2013 в 13:04
Serge_007 Дата: Среда, 21.08.2013, 13:09 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

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

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеСводные таблицы предназначены для работы с данными, оформленными в виде списка (таблицы), т.е. фактически - базой данных
Автор - Serge_007
Дата добавления - 21.08.2013 в 13:09
SergeyKorotun Дата: Среда, 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_ подбросил, я только удалил значения из нескольких ячеек.
 
Ответить
СообщениеRAN, добавьте в ваш макрос Range("табл_").Select и на присоединенном файле в 16 сообщении увидите, что диапазон выделяется неверно.
Цитата (Michael_S, 21.08.2013 в 13:04, в сообщении №17)
SergeyKorotun, ваши примеры выходят из пределов разумной организации данных

Таблицы разные бывают. И правильнее будет использовать в качестве ранга все столбцы. А пример мне _Boroda_ подбросил, я только удалил значения из нескольких ячеек.
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 13:17
_Boroda_ Дата: Среда, 21.08.2013, 13:29 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

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
 
Ответить
Сообщение
Цитата (SergeyKorotun, 21.08.2013 в 13:17, в сообщении №19)
А пример мне _Boroda_
Понятно. То есть я за Вас Ваш пример нарисовал и я же виноват и оказался. Извините, больше так не буду. По крайней мере, с Вашими вопросами.

Цитата (SergeyKorotun, 21.08.2013 в 13:17, в сообщении №19)
Таблицы разные бывают. И правильнее будет использовать в качестве ранга все столбцы.
Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.
Автор - _Boroda_
Дата добавления - 21.08.2013 в 13:29
SergeyKorotun Дата: Среда, 21.08.2013, 18:42 | Сообщение № 20
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата (_Boroda_, 21.08.2013 в 13:29, в сообщении №20)
Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.

Мы то в курсе. Но "нормальные таблицы" хранят не в екселе и юзер прямого доступа к базам не имеет.
От удалит он случайно одно значение в ключевом поле (в нашем случае в столбце А) и если помечать диапазон, а не столбцы для сводной таблицы, он за каждый новый день в сводной таблице будет терять данные из последней строки. В случае выбора столбцов в качестве диапазона он получит неверные данные в сводной таблице только за 1 день.
 
Ответить
Сообщение
Цитата (_Boroda_, 21.08.2013 в 13:29, в сообщении №20)
Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.

Мы то в курсе. Но "нормальные таблицы" хранят не в екселе и юзер прямого доступа к базам не имеет.
От удалит он случайно одно значение в ключевом поле (в нашем случае в столбце А) и если помечать диапазон, а не столбцы для сводной таблицы, он за каждый новый день в сводной таблице будет терять данные из последней строки. В случае выбора столбцов в качестве диапазона он получит неверные данные в сводной таблице только за 1 день.
Автор - SergeyKorotun
Дата добавления - 21.08.2013 в 18:42
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Изменение диапазона в сводной таблице (Pivot Table Сводные таблицы)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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