Автоматическое поддержание синхронизации двух таблиц данных Excel (без VBA)

Question

Я собираю рабочую книгу для отслеживания портфеля акций. Основной лист содержит таблицу со списком транзакций. Исходя из этого, я хотел бы создать обзорную таблицу на другом листе, содержащую только одну строку для каждого уникального символа запаса, которая включает в себя такие вещи, как базис затрат, возврат и т.д. Проблема в том, что ничто из того, что я пробовал, корректно не обновляет обзорную таблицу при добавлении строк к таблице транзакций. Самое близкое, что у меня есть, это что-то вроде следующего:

http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/

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

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

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

Sheet1
Symbol     Type    Shares    Price
F          Buy     100       12
MSFT       Buy     100       25  
MSFT       Buy     50        28
F          Buy     100       16
GOOG       Buy     25        550

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
------------------------------------------------------------ // Table Boundary
GOOG                                25
{=UNIQUE_LIST(Sheet1[Symbol], A5)}  =SUMIF(Sheet1[Symbol], A5, Sheet1[Shares])
{=UNIQUE_LIST(Sheet1[Symbol], A6)}  =SUMIF(Sheet1[Symbol], A6, Sheet1[Shares])
... // Formulas start at row 2 and extend down tons of extra rows
    // Table boundary didn't extend to include GOOG

В идеале я хочу, чтобы Sheet2 выглядел больше так, где формулы копируются только при необходимости

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
GOOG                                25
------------------------------------------------------------ // Table Boundary
// Table extended and no extra formulas

Добавление строки

Answer 1

Ты пишешь:

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

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

Answer 2

Помните, что обычно есть много способов достичь того же результата с помощью Excel. Может быть, вы можете использовать функции базы данных. В приведенном вами примере, если вы ссылаетесь на базу данных как Sheet1!A:D или определите имя, которое ссылается на эту ссылку, тогда вы можете добавить столько строк, сколько захотите. К сожалению, функции базы данных уродливы и сложны в использовании из-за того, как вы должны указать критерии.

Другим вариантом будет использование массива forumla. что-то вроде:{= сумма (если (лист1!a2:A999 = "MSFT", Лист1!б2:b999,0))}

Answer 3

Нил,

Если вы используете XL 2003 или более позднюю версию, попробуйте поместить исходные данные в список (XL 2003) или таблицу (то же самое, переименовано в XL 2007, 2010). Затем используйте свой список / таблицу в качестве источника сводной таблицы на другом листе. Это отвечает вашим требованиям как к данным, так и к отчету, насколько я их понимаю. Исходный источник будет расширяться и сжиматься со списком / таблицей.

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

Answer 4

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

http://www.get-digital-help.com/2011/01/31/tracking-a-stock-portfolio-in-excel-auto-update/

Answer 5

Я много работаю с сводными таблицами. Когда вы установите свой массив, введите вручную конечную ячейку.

Например, у меня есть данные, которые начинаются с диапазона A11:C2500.

Если я предполагаю, что данные вырастут до строки 10000, я вручную изменю массив на чтение A11:C15000, чтобы обеспечить пространство для роста.

Если вы сделаете это, вы увидите свою таблицу Pivot, когда новые элементы будут добавлены в список транзакций.