Excel: как генерировать динамические таблицы на основе значений другой таблицы

Question

В Excel я хотел бы создать динамическую таблицу, основанную на значениях в другой ячейке.

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

Вот что я хотел бы сделать:

1 - я введите значение в ячейку рядом с Sum (здесь это $100

2 - затем я распределяю процент от 100% до A , B , C , D:

[Table 1]

|------|--------------|
| Sum  |     $100     |
|------|--------------|

|------|--------------|    
| Unit | Distribution |
|------|--------------|
| A    |     0.5      |
|------|--------------|
| B    |              |
|------|--------------|
| C    |              |
|------|--------------|
| D    |     0.5      |
|------|--------------|

A и D имеют по 50% каждый, а B и C имеют 0%. Структура таблицы выше всегда должна быть одинаковой.

Теперь я хотел бы автоматически сгенерировать вторую таблицу динамически на основе введенных значений ячеек в таблице 1, которая дает вывод:

[Table 2]

|------|--------------|    
| Unit |    Value     |
|------|--------------|
| A    |     $50      |
|------|--------------|
| D    |     $50      |
|------|--------------|

Под динамической генерацией таблицы я подразумеваю, что генерируются только значения A и B , в то время как C и D не генерируются, потому что их значения распределения были 0%.

С другой стороны, другой дистрибутив будет генерировать другую таблицу:

[Table 1]

|------|--------------|
| Sum  |     $100     |
|------|--------------|

|------|--------------|    
| Unit | Distribution |
|------|--------------|
| A    |     0.75     |
|------|--------------|
| B    |              |
|------|--------------|
| C    |     0.125    |
|------|--------------|
| D    |     0.125    |
|------|--------------|

[Table 2]

|------|--------------|    
| Unit |    Value     |
|------|--------------|
| A    |     $75      |
|------|--------------|
| C    |     $12.50   |
|------|--------------|
| D    |     $12.50   |
|------|--------------|

Почему я хочу генерировать Таблицу 2 динамически: потому что на самом деле у меня есть большое количество потенциальных "Единиц" для распределения с какой-то другой арифметикой, и я только хочу получить согласованный табличный вывод со значениями> $ 0. Я скопирую / вставлю эти цифры в мое бухгалтерское приложение.

Вопросы:

1) Какова правильная "терминология" и "методы" для того, что я пытаюсь достичь? Я прошу это, чтобы иметь возможность найти ресурсы в Интернете, на случай, если никто не сможет помочь мне с моим конкретным вариантом использования. Использую ли я что-то вроде сводных таблиц, динамических диапазонов и т.д.? (У меня нет опыта с этим). Я был бы очень признателен, насколько это возможно.

2) Лучший случай, можете ли вы помочь мне с примером решения для вышеуказанных потребностей? Если не полностью, существуют ли какие-то определенные инструменты и / или формулы Excel, о которых я должен знать (что-то вроде таблиц динамической фильтрации для значений> 0)?

Answer 1

ОП здесь - я смог сделать эту работу довольно хорошо сейчас, поэтому я включил мое "решение" для справки. Однако я не уверен, что это лучший подход.

Я создал две таблицы, с таблицей 2 расчетов на основе соответствующих формул из таблицы 1.

Затем я добавил фильтр в таблицу 2, отображая только строки со значениями больше 0, основанные на ячейках в соответствующем столбце. Чтобы добавить фильтр, нажмите стрелку вниз в столбце таблицы, по которой вы хотите выполнить фильтрацию.

Это отфильтровывает строки, которые имеют пустые значения; однако он не динамически обновляет фильтрацию при изменении данных таблицы 1.

Чтобы динамически обновлять таблицу 2 на основе входных данных из таблицы 1, я сохранил файл как файл Excel с поддержкой макросов («Сохранить как»> «Книга с поддержкой макросов Excel», .xlsm) и добавил код VBA из этого ответа: https://superuser.com/a/501736/455679

Чтобы добавить код VBA, щелкните правой кнопкой мыши Лист на нижней панели Excel (обычно Sheet1) и выберите команду « Просмотреть код» в контекстном меню. Затем вставьте и сохраните следующий код, где Table2 должен быть именем фактически отфильтрованной таблицы:

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2")
         .AutoFilter.ApplyFilter
    End With
End Sub

Нефильтрованные значения:

Динамически фильтруемые значения:

Обратите внимание, что из-за фильтрации строк таблица 1 и таблица 2 не должны совместно использовать одни и те же строки, поскольку это может отфильтровать таблицу 1 на основе ее собственных значений.