Нормализация данных из таблиц Excel в Интеграле

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

Из этой единой простыни мы сделаем такую нормализованную структуру таблиц:

Начнем мы с проекта – скопируем данные по проектам:

С ними мы пойдем в универсальный загрузчик – меню Загрузка, выберем Создать новую таблицу из файла и вставим данные в поле для импорта.

Нажмем кнопку Проверить и увидим предварительный просмотр нашей будущей таблицы.

Колонку Статус в таблице проектов сделаем справочным значением. Номер проекта мы сделаем уникальным, чтобы однозначно находить их по номерам – так все повторяющиеся значения в этой нашей таблице будут проигнорированы при импорте.

Итак, грузим проекты.

Теперь они становятся доступны в меню Таблицы – давайте на них посмотрим. Видим здесь три проекта.

Кнопкой «плюс» справа добавим сюда колонку Этап, которая будет числовой. Эту колонку мы преобразуем в таблицу с помощью контекстного меню:

В таблицу этапобавим колонку Срок – здесь будут записаны даты сдачи этапа. Ещё мы добавим сюда колонку Ответственный – это будет ссылка на Пользователя.

Зададим колонке псевдоним – Ответственный, чтобы всё выглядело как в исходном экселе.

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

Давайте добавим сюда ещё колонку Задача – это будет короткий текст названия задачи. Задачу мы тоже преобразуем в таблицу:

Добавим задаче колонку Срок, потом добавим колонку Детали – это будет многострочный текст, и колонку Исполнитель – в виде ссылки на пользователя. Зададим этой колонке псевдоним Исполнитель так же, как сделали это для ответственного за этап минутой раньше.

Теперь мы можем загрузить все наши данные в Интеграл, сохранив всё в этой структуре. Чтобы этапы правильно привязались к проектам, в экселе мы добавим к ним колонку проект – переместим её ближе к этапам и скопируем этапы вместе с этой колонкой.

Идем в меню Загрузка и выбираем таблицу для загрузки – Этап проекта. Вставим сюда данные и нажмем кнопку Проверить

Мы видим наши проекты и этапы в таблице для предварительного просмотра. Здесь есть галка «Взять значение родителя или его ID из первой колонки» – эта галка даст загрузчику понять, под какой проект записывать каждый этап.

Теперь мы укажем в настройках, что первая колонка содержит заголовок, нажмем «Проверить», чтобы обновить данные, и теперь их можно загрузить.

Грузим.

Теперь в таблице проектов мы увидим у каждого проекта его этапы – они все оказались в подчиненных таблицах. Давайте на них посмотрим.

Теперь надо загрузить Задачи – делаем всё похожим образом, однако здесь есть ещё одна тонкость – номер этапа не определяет однозначно договор, к которому этап относится. Ведь у каждого договора есть этап №1.

Мы должны вычислить идентификатор этапа, чтобы привязать задачи к нему. Сейчас мы это сделаем.

Идем в меню Запросы и создаем запрос «Проект-этап», в который добавляем колонки Проект и Этап. Затем ещё раз добавим Этап и применим к нему функцию для получения ID.

Теперь нам надо получить уникальную комбинацию Проект-этап, для этого мы сцепим эти два значения через дефис функцией CONCAT (Конкат) – аналог функции «Сцепить» в экселе. Зададим имена формулам колонок Проект и Этап, а потом используем их в функции CONCAT – поставим между ними дефис. Переместим вычисленную колонку левее.

Запустим запрос. Его можно экспортировать в эксель и вставить в нашу книгу как справочник для вычисления нужного идентификатора этапа.

Давайте скопируем полученный отчет в наш эксель.

Продублируем здесь колонку Этап и добавим в ней функцию ВПР – вертикальный поиск результата. Первым аргументом в эту функцию мы подставим такую же сцепленную комбинацию Проект-Этап с добавлением дефиса, вторым параметром выделим нашу таблицу из отчета, нажав после этого кнопку F4 или добавив вручную значок доллара перед буквами и цифрами адресов ячеек. Эти доллары нужны для того, чтобы, когда мы будем копировать эту формулу, она оставалась привязана к зафиксированному диапазону ячеек – тому, что мы тут указали.

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

Теперь мы размножим формулу поиска, растянув её вниз, и увидим вычисленные уникальные номера нужных нам этапов.

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

Теперь мы копируем таблицу задач вместе с номерами этапов и идем в меню Загрузка.

Здесь мы выбираем таблицу Задача, вставляем наши данные и нажимаем кнопку Проверить.

Ставим галку, что значение ID этапа надо брать из первой колонки.

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

Загружаем наши данные в систему и идем в меню Таблицы, здесь выбираем Проект, в нём – Этап и наблюдаем наши задачи по этапам.

Вот так за несколько минут мы сделали нормализованные таблицы из сплошной простыни экселя.

Спасибо!