Взаимосвязь размеров двух и более "умных" таблиц

@hardwester · Регистрация: 18.05.2015

Author24 — интернет-сервис помощи студентам

Добрый вечер

Не могли бы вы посоветовать что делать в следующей ситуации. В файле excel'а существует несколько "умных" таблиц (точнее 3), которые отображают этапы расчетов. Первая из этих таблиц служит для ввода данных, вторая - это таблица, где производятся промежуточные расчеты, третья - таблица с конечными результатами. При вводе данных в первую таблицу (в том числе копирования большого массива данных) "умная" таблица автоматически изменяет свой размер. Все три таблицы в конечном варианте имеют (должны иметь) одинаковое количество строк.

Вопрос мой таков: возможно ли для второй и третьей "умной" таблицы задать автоувеличение количества в них строк до размера первой "умной" таблицы? Иными словами можно ли для "умных" таблиц задавать динамический диапазон по определённым условиям?

Возможно ли сделать указанное выше без помощи VBA? Возможно ли на основе "Диспетчера имен" и функций типа "АДРЕС", "ИНДЕКС" и подобных?

Написать подобный макрос для меня, в принципе, не такая сложная задача, однако, есть потребность более глубоко изучить встроенные функции Excel'а, в том числе использование "умных" таблиц, диспетчера имен и прочего.

Fairuza · 18.05.2015, 21:29

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

@hardwester · 18.05.2015, 21:39 **[ТС]**

Сложилась такая ситуация, что важен не только конечный результат, но понимание структуры промежуточного вычисления. Безусловно, можно миновать промежуточные вычисления, но вопрос останется: будет существовать не три, а две "умные" таблицы и вторая не будет автоувеличиваться по размеру первой таблицы.

Конечно, это можно делать вручную - обычной протяжкой таблицы (таблиц), но тут уже взыграл спортивный интерес к возможностям excel'я, так как над решением уже несколько дней работаю.

Fairuza · 18.05.2015, 21:50

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

@hardwester · 18.05.2015, 21:58 **[ТС]**

Да, в ней происходит аккумуляция первой таблицы (исходных данных) и второй таблицы (расчетов). Ничто не мешает поместить расчеты второй таблицы в третью, но, опять же, это лишает возможности при необходимости видеть промежуточные результаты и вносить в них ручную корректировку (так как они осуществляются автоматически). И оставляет вопрос: возможна ли автоувеличение размера одной "умной" таблицы при изменении размера другой.

Во вложении прилагаю файл.

@AntikBantik · 19.05.2015, 07:03

А вместо 2 и 3 умной таблицы не пойдут сводные таблицы? В сводные накидать формулы и он будет автоматом расти.

Fairuza · 19.05.2015, 09:00

Наверное нужно их просто соединить в одну таблицу и скрыть вторую и третью до необходимости

@hardwester · 19.05.2015, 14:42 **[ТС]**

Да, в данном конкретном случае их можно соединить (немного потеряв в наглядности представления данных). Возможно пример был недостаточно красочным.
Но, допустим, существует потребность большого объема ввода данных (до 6 000 - 7 000 строк) в таблицу для ввода (и столбцов там будет значительно больше), которые в последствие в разных таблицах на разных листах будут пересчитываться. Из-за этого и возникает потребность связывания размеров таблиц и автоувеличения их размеров, чтобы не заниматься этим вручную и не запоминать все таблицы в которых надо выполнить "протяжку".

Fairuza · 19.05.2015, 14:47

Воводите в первую таблицу, на другом листе =ДВССЫЛ() подтягиваете определитель для следующей таблицы и все должно стать автоматом. Нет?

@KoGG · 20.05.2015, 14:23

Таблицы взаимоувязаны. Макросы должны быть включены.

@hardwester · 20.05.2015, 16:52 **[ТС]**

Благодарю.
Пришёл к выводу, что без макросов решить данную задачу встроенными функциями excel'я невозможно. Функцией =ДВССЫЛ() работает в данном случае как-то непредсказуемо и непонятно.
Уже написал макрос. Благодарю, KoGG. Ваш макрос тоже посмотрел. У него есть небольшой недостаток - он проверяет размер таблицы только по первому столбцу, который не обязательно всегда будет заполнен. Его преимущество, относительно моего - он взаимоувязывает все три таблицы, при условии, что хоть одна из них изменяет свой размер. Хотя в моём конкретном случае это не требуется, но буду знать на будущее как это сделать.
Кому интересно, прилагаю файл со своим макросом. На этом считаю, что ответ получен и тему можно закрыть.

@spm1377 · 05.03.2016, 20:22

Спасибо. (появилось представление)
Мне подходит вариант от KoGG (идет проверка только по первому столбцу), но помогите отредактировать скрипт для сравнения таблиц на разных листах.

@KoGG · 05.03.2016, 21:55

Visual Basic

Private Sub Worksheet_Change(ByVal Target As Range)
 ' процедура находится в объектном модуле листа "Смета проекта"
 '   ListObjects("Себестоимость") находится на данном листе с именем "Смета проекта"
 '   ListObjects("Накрутка")  находится  листе с именем "Лист2"
    Dim LastRow1&, LastRow2&, Ra As Range
    With Me.ListObjects("Себестоимость")
        .LastRow1 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
        With Sheets("Лист2")
            With .ListObjects("Накрутка")
                .LastRow2 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count 
            End With
            If LastRow2 < LastRow1 Then
                Application.EnableEvents = False
                .ListObjects("Накрутка").Resize .Range("$N$30:$Z$" & LastRow1)
                .Range("N31:Z31").Copy .Range(.Cells(LastRow2 + 1, "N"), .Cells(LastRow1, "Z"))
                Application.EnableEvents = True
            End If
        End With
    End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
 ' процедура находится в объектном модуле листа "Лист2"
    Dim LastRow1&, LastRow2&
    With Me.ListObjects("Накрутка")
        .LastRow1 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count 
        With Sheets("Смета проекта")
            With .ListObjects("Себестоимость")
                .LastRow2 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count 
            End With
            If LastRow2 < LastRow1 Then
                Application.EnableEvents = False
                .ListObjects("Себестоимость").Resize .Range("$B$30:$L$" & LastRow1)
                .Range("B31:L31").Copy .Range(.Cells(LastRow2 + 1, "B"), .Cells(LastRow1, "L"))
                Application.EnableEvents = True
            End If
        End With
    End With
End Sub

@KoGG · 06.03.2016, 14:08

Тут я вчера лишние точки перед LastRow1 и LastRow2 наставил - их конечно надо убрать - и строку 1-ю не надо копировать, а только ее формат.

Visual Basic

Private Sub Worksheet_Change(ByVal Target As Range)
 ' процедура находится в объектном модуле листа "Смета проекта"
 '   ListObjects("Себестоимость") находится на данном листе с именем "Смета проекта"
 '   ListObjects("Накрутка")  находится  листе с именем "Лист2"
    Dim LastRow1&, LastRow2&, Ra As Range
    With Me.ListObjects("Себестоимость")
        LastRow1 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
         Debug.Print "Себестоимость", .HeaderRowRange.Row, .DataBodyRange.Rows.Count
        With Sheets("Лист2")
            With .ListObjects("Накрутка")
                LastRow2 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
            End With
            If LastRow2 < LastRow1 Then
                Application.EnableEvents = False
                .ListObjects("Накрутка").Resize .Range("$N$30:$Z$" & LastRow1)
                .Range("N31:Z31").Copy
                .Cells(LastRow2 + 1, "N").PasteSpecial Paste:=xlPasteFormats
                Application.EnableEvents = True
            End If
        End With
    End With
End Sub

@spm1377 · 06.03.2016, 21:27

Спасибо большое работает как надо, но тут меня понесло - захотел таблицы по листам разнести с "автоувеличением" ... сделал так отрабатывает только первый лист и второй

Visual Basic

Private Sub Worksheet_Change(ByVal Target As Range)
 ' процедура находится в объектном модуле листа "Смета проекта"
 '   ListObjects("Себестоимость") находится на данном листе с именем "Смета проекта"
 '   ListObjects("Накрутка")  находится  листе с именем "Лист2"
    Dim LastRow1&, LastRow2&, Ra As Range
    With Me.ListObjects("Себестоимость")
        LastRow1 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
         Debug.Print "Себестоимость", .HeaderRowRange.Row, .DataBodyRange.Rows.Count
        With Sheets("Лист2")
            With .ListObjects("Накрутка")
                LastRow2 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
            End With
            If LastRow2 < LastRow1 Then
                Application.EnableEvents = False
                .ListObjects("Накрутка").Resize .Range("$N$30:$Z$" & LastRow1)
                .Range("N31:Z31").Copy
                .Cells(LastRow2 + 1, "N").PasteSpecial Paste:=xlPasteFormats
                Application.EnableEvents = True
            End If
        End With
        With Sheets("Лист3")
            With .ListObjects("Прочие")
                LastRow3 = .HeaderRowRange.Row + .DataBodyRange.Rows.Count
            End With
            If LastRow3 < LastRow1 Then
                Application.EnableEvents = False
                .ListObjects("Прочие").Resize .Range("$N$30:$Z$" & LastRow1)
                .Range("N31:Z31").Copy
                .Cells(LastRow3 + 1, "N").PasteSpecial Paste:=xlPasteFormats
                Application.EnableEvents = True
            End If
        End With
    End With
End Sub

@Shevr98 · 16.08.2022, 14:59

Добрый день. Есть к примеру две умные таблицы одна под одной, подскажите как сделать так, чтоб при увеличение 1 таблицы вниз… вторая таблица смещалась на равное количество добавленных строк в 1 таблице

Добавлено через 1 минуту
Добрый день. Есть к примеру две умные таблицы одна под одной, подскажите как сделать так, чтоб при увеличение 1 таблицы вниз… вторая таблица смещалась на равное количество добавленных строк в 1 таблице

@spm1377 0 / 0 / 0 Регистрация: 05.03.2016 Сообщений: 2
	05.03.2016, 20:22	12
	Спасибо. (появилось представление) Мне подходит вариант от KoGG (идет проверка только по первому столбцу), но помогите отредактировать скрипт для сравнения таблиц на разных листах. 0

@hardwester 0 / 0 / 0 Регистрация: 18.05.2015 Сообщений: 5
		1
	Взаимосвязь размеров двух и более "умных" таблиц 18.05.2015, 20:39. Показов 7411. Ответов 15 Метки нет (Все метки) Добрый вечер Не могли бы вы посоветовать что делать в следующей ситуации. В файле excel'а существует несколько "умных" таблиц (точнее 3), которые отображают этапы расчетов. Первая из этих таблиц служит для ввода данных, вторая - это таблица, где производятся промежуточные расчеты, третья - таблица с конечными результатами. При вводе данных в первую таблицу (в том числе копирования большого массива данных) "умная" таблица автоматически изменяет свой размер. Все три таблицы в конечном варианте имеют (должны иметь) одинаковое количество строк. Вопрос мой таков: возможно ли для второй и третьей "умной" таблицы задать автоувеличение количества в них строк до размера первой "умной" таблицы? Иными словами можно ли для "умных" таблиц задавать динамический диапазон по определённым условиям? Возможно ли сделать указанное выше без помощи VBA? Возможно ли на основе "Диспетчера имен" и функций типа "АДРЕС", "ИНДЕКС" и подобных? Написать подобный макрос для меня, в принципе, не такая сложная задача, однако, есть потребность более глубоко изучить встроенные функции Excel'а, в том числе использование "умных" таблиц, диспетчера имен и прочего. 0

Fairuza 6002 / 3197 / 716 Регистрация: 23.11.2010 Сообщений: 10,684
	18.05.2015, 21:29	2
	Почему все нельзя делать в одной таблице, т.е. миновать вторую с промежуточными? 0

@hardwester 0 / 0 / 0 Регистрация: 18.05.2015 Сообщений: 5
	18.05.2015, 21:39 [ТС]	3
	Сложилась такая ситуация, что важен не только конечный результат, но понимание структуры промежуточного вычисления. Безусловно, можно миновать промежуточные вычисления, но вопрос останется: будет существовать не три, а две "умные" таблицы и вторая не будет автоувеличиваться по размеру первой таблицы. Конечно, это можно делать вручную - обычной протяжкой таблицы (таблиц), но тут уже взыграл спортивный интерес к возможностям excel'я, так как над решением уже несколько дней работаю. 0

Fairuza 6002 / 3197 / 716 Регистрация: 23.11.2010 Сообщений: 10,684
	18.05.2015, 21:50	4
	т.е. третья таблица - это продолжение расчетов, а не скомпонованные итоги по типу сводной? 0

@AntikBantik 83 / 60 / 21 Регистрация: 03.12.2014 Сообщений: 232
	19.05.2015, 07:03	6
	А вместо 2 и 3 умной таблицы не пойдут сводные таблицы? В сводные накидать формулы и он будет автоматом расти. 0

Fairuza 6002 / 3197 / 716 Регистрация: 23.11.2010 Сообщений: 10,684
	19.05.2015, 09:00	7
	Наверное нужно их просто соединить в одну таблицу и скрыть вторую и третью до необходимости 0

@hardwester 0 / 0 / 0 Регистрация: 18.05.2015 Сообщений: 5
	19.05.2015, 14:42 [ТС]	8
	Да, в данном конкретном случае их можно соединить (немного потеряв в наглядности представления данных). Возможно пример был недостаточно красочным. Но, допустим, существует потребность большого объема ввода данных (до 6 000 - 7 000 строк) в таблицу для ввода (и столбцов там будет значительно больше), которые в последствие в разных таблицах на разных листах будут пересчитываться. Из-за этого и возникает потребность связывания размеров таблиц и автоувеличения их размеров, чтобы не заниматься этим вручную и не запоминать все таблицы в которых надо выполнить "протяжку". 0

Fairuza 6002 / 3197 / 716 Регистрация: 23.11.2010 Сообщений: 10,684
	19.05.2015, 14:47	9
	Воводите в первую таблицу, на другом листе =ДВССЫЛ() подтягиваете определитель для следующей таблицы и все должно стать автоматом. Нет? 0

@Shevr98 0 / 0 / 0 Регистрация: 02.08.2022 Сообщений: 11
	16.08.2022, 14:59	16
	Добрый день. Есть к примеру две умные таблицы одна под одной, подскажите как сделать так, чтоб при увеличение 1 таблицы вниз… вторая таблица смещалась на равное количество добавленных строк в 1 таблице Добавлено через 1 минуту Добрый день. Есть к примеру две умные таблицы одна под одной, подскажите как сделать так, чтоб при увеличение 1 таблицы вниз… вторая таблица смещалась на равное количество добавленных строк в 1 таблице 0

Взаимосвязь размеров двух и более "умных" таблиц

Решение