Организация данных Excel: формулы массива? Таблицы? Названный диапазон?

Question

Я пытаюсь сделать огромный лист Excel разумно обслуживаемым, но он огромен в направлении «сто таблиц-дБ», а не в направлении «таблицы сотен тысяч строк».

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

| Indicator                | Units       | 2010 | 2015 | 2020 | 2025 | Source     | 
| GDP                      | $Gazillion  | 300  |  350 | 400  | 450  | BLS        |
| Population               | Millions    | 350  |  400 | 450  | 500  | Census     |
| PetMonkeyPopulation      | Thousands   | 50   | 60   | 70   | 80   | SimiansRUs |

И тогда будет возможность иметь еще один лист, который выглядит так:

|                          | 2010 | 2015 | 2020 | 2025 |
| MonkeysPerCapita         | .1   |  .2  | .3   | .4   |
| MonkeysPerDollar         | .01  |  .01 | .01  | .01  |
| GDPPerCapita             | 300  |  400 | 450  | 600  |

Есть ли какой-нибудь стандартный способ сделать такие вещи ремонтопригодными?

Answer 1

Ненавижу публиковать ответы без ответов, но вам действительно следует перенести это в настоящую базу данных. Он будет более стабильным, быстрым и простым в использовании и обновлении. Затем (или если я предположил, что это неправильно, и он уже находится в базе данных), вы можете настроить запросы, которые Excel может использовать для извлечения соответствующих данных или расчетов в электронную таблицу для составления отчетов или манипуляций.

Answer 2

Я согласен с другими респондентами; вы говорите о данных и метаданных, которые действительно предназначены для баз данных.

Но, если вы стремитесь (или ограничены) заставить его работать в Excel, единственное, что я могу придумать, - это логически разбить ваш отдельный лист на несколько листов для того, что бы было вашими таблицами данных, если бы они были у вас. , Это может быть похоже на модель ER (http://db.grussell.org/section006.html).

Таким образом, для переменных, которые часто меняются (например, данные о ежедневных продажах), храните их в листе основных данных. Другие переменные, которые не часто меняются, но являются частью основных данных (например, имена торговых представителей), могут помещаться в другие файлы (или на другие листы - это ваши метаданные). Эта книга в совокупности станет вашей «базой данных». (Вы в основном создаете отношения между несколькими плоскими файлами - так работали самые первые базы данных.)

Убедитесь, что у вас есть хотя бы одно общее поле с уникальным идентификатором как минимум между двумя листами (т. Е. Если у вас есть лист для SalesRepData с SalesRepID и Territory на нем, и у вас есть еще один лист для TerritoryData с Territory и CityNames на нем, затем вы можете получить торгового представителя по городу между двумя листами на другой лист).

Когда вы создаете отчет, который требует данных из нескольких листов (или файлов), вы будете использовать функции vlookup и index/match для связывания ваших листов.

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

Удачи!