Как уже упоминалось, то, что вы хотите, будет естественным для решения для базы данных. Вот подход, который использует способность Excel подключаться к внешним источникам данных. Я не много работал с этим подходом, поэтому его следует считать экспериментальным, по крайней мере для меня, и могут быть скрытые ошибки, которые я еще не раскрыл.
Идея состоит в том, чтобы связать данные рабочей книги с самим собой, с помощью инструмента Microsoft Query Tool определить запрос объединения для таблиц данных в тематических листах рабочей книги и вернуть результат запроса в виде сводной таблицы в сводной таблице. Данные в этой таблице могут быть отсортированы и отфильтрованы по мере необходимости.
Настройка предметных листов в соответствии с линиями, которые вы наметили (разработано несколько). Чтобы данные на листах были опознаны инструментом запросов как "таблицы", каждый список книг должен иметь имя. Например, диапазон A1:E11 в листе Subj1 также был назван Subj1 с аналогичными именами для двух других таблиц данных. [1]
Чтобы настроить запрос, начните с выбора « From Other Sources / « From Microsoft Query на вкладке « Data » на ленте, выберите « Файлы Excel» в качестве источника данных и укажите саму книгу в качестве исходного файла.
Инструмент запросов откроется с видимым мастером запросов. Каждое из заданных вами имен диапазонов будет отображаться в виде таблиц, а заголовки столбцов - в качестве полей. Идите дальше и выберите поля из первой таблицы Subj1, которые будут включены в запрос. Затем дважды нажмите кнопку « Далее», чтобы перейти к диалоговому окну «Завершение мастера запросов». Там выберите переключатель « View data or edit query in Microsoft Query и нажмите « Готово».
Откроется сам инструмент Query Tool с графическим представлением запроса, как указано выше, и списком результатов запроса. Нажмите кнопку SQL в строке главного меню, открывая редактор SQL.
Измените запрос так, чтобы он объединял все диапазоны данных (три в моем примере):
SELECT subj1.subject, subj1.title, subj1.status
FROM subj1
UNION ALL
SELECT subj2.subject, subj2.title, subj2.status
FROM subj2
UNION ALL
SELECT subj3.subject, subj3.title, subj3.status
FROM subj3
После нажатия ОК сохраните запрос. Теперь вы можете встраивать результаты запроса, выбрав File / Return Data to Microsoft Excel , следуя инструкциям по импорту результатов в Summary таблицу в виде сортируемой и фильтруемой таблицы.
Поскольку Excel считает, что данные для таблицы получены из внешнего источника данных, вам необходимо обновить таблицу после редактирования списков книг. Refresh можно получить, щелкнув правой кнопкой мыши в таблице, чтобы вызвать контекстное меню. [2]
Если впоследствии вы захотите изменить запрос (например, добавить категорию), вы можете получить к нему доступ через Data / Connections на ленте. Нажмите « Свойства» в диалоговом окне « Подключения к Workbook Connections », а затем перейдите на вкладку « Definition ». Отредактируйте запрос непосредственно в Command text поле « Команда» ; Excel будет жаловаться, если вы нажмете Изменить запрос.
1. Заштрихованные синим цветом области в столбце E и строке 11 предназначены для удобства в будущем при добавлении дополнительных книг (или категорий). Их можно перетаскивать вниз или вправо, чтобы освободить место для большего количества данных при сохранении имени диапазона. (Включение лишних строк в имя диапазона приведет к тому, что в итоговой таблице появятся пустые строки; динамически определенные диапазоны не распознаются как таблицы инструментом запросов.)
2. Выбор « Свойства Table / External Data Properties в контекстном меню вызовет некоторые полезные свойства форматирования таблицы, в том числе отключение автоматического изменения размера столбцов таблицы при обновлении.
