Ребята, всем привет! 👋 Подготовила этот урок по запросу нашей подписчицы.
Ее задача состоит в следующем:
Есть таблицы с данными: лист 2, лист 3, лист 4 и тд... В которых работают сотрудники.
Как сделать, что бы при добавлении данных в лист 2,3,4... Данные добавлялись в лист1?
📣 Если у Вас также есть не решенные задачи, пишите в комментариях, и мы подберем для Вас решение. Всем добра! 🌞
Ну а мы начинаем урок.
📽 Видео обзор по уроку приведен внизу статьи 👇
Продемонстрирую решение задачи на подобном примере. Главное понять суть. А далее приведенный пример вы всегда можете адаптировать под Ваши нужды. Итак,
✅ 7 шагов решения задачи:
Задачу решаем при помощи Microsoft Power Query для Excel — эта надстройка, которая обеспечивает работу в области обнаружения и преобразования данных и предоставляет дополнительные возможности для информационных работников, специалистов по бизнес-аналитике и других пользователей Excel.
Power Query позволяет следующее:
▶ Идентифицировать данные, представляющие для вас интерес, поступающие из источников, с которыми вы работаете (такими как реляционные базы данных, текстовые, Excel- и XML-файлы, каналы OData, веб-страницы, распределенная файловая система Hadoop (HDFS) и т. д.).
▶ Обнаруживать релевантные данные в пределах(*) и за пределами своей организации, используя поисковые возможности Excel.
▶ Комбинировать и формировать данные из многочисленных, разрозненных источников данных в целях подготовки данных для дальнейшего анализа в таких инструментах, как Excel и Power Pivot, или визуализации с помощью инструментов наподобие Power View и Power Map.
▶ Предоставлять другим сотрудникам организации совместный доступ к созданным вами запросам, чтобы их можно было легко находить с помощью поиска. (*)
📍 ШАГ 1.
Создаем столько листов, сколько требует решение вашей задачи (в моем примере будет 3 листа ) .
🔔 Главное - все таблицы должны иметь одинаковую структуру.
📍 ШАГ 2.
Создаем запрос (из таблиц). Для этого:
- выделяем нужную таблицу
- на вкладке данные находим "Создать запрос" --->> "Из таблицы"
- в появившимся окошке указываем - "Таблица с заголовками"--->> ОК
После чего, Вас перенаправит в редактор запросов:
где в Параметрах запроса Вы (для удобства дальнейшего использования) указываете свое имя таблицы (Например, Т_1):
📍 ШАГ 3.
Сохраняем запрос. Для этого, на вкладке Главная выбираем "Закрыть и загрузить в..."
И указываем как следует просматривать данные в таблице. На этом этапе мы можем создать "Только подключение" (выбрать соответствующий пункт) и жмем "Загрузить":
📍 ШАГ 4.
Проделываем действия по Шагу 3 в отношении всех имеющихся таблиц!
Таким образом, перейдя на вкладке "Данные" --->> "Показать запросы" видим все подключения которые мы создали:
📍 ШАГ 5.
Создаем сводную таблицу. Т.к. Все подготовительные действия у нас уже выполнены, то остается собственно только создать сводную таблицу.
- выделяем любую таблицу (лучше конечно первую, чтобы не запутаться)
- на вкладке данные находим "Создать запрос" --->> "Из таблицы" (напомню, такой шаг мы уже делали, но сейчас это нужно чтобы создать сводный отчет)
- в редакторе запросов в свойствах задаем имя (например, Сводная)
- на вкладке "Главная" выбираем "Закрыть и загрузить"
В вашей книге буден создан новый лист в котором как раз таки и будет размещена сводная таблица (переименовать лист вы можете самостоятельно), а также задать нужное форматирование сводной таблицы.
Но все же, по порядку.
Сейчас (если все правильно сделано) вы получили такой результат:
Конечно, как я говорила выше такое форматирование вас может не устроить, поэтому, на вкладке Конструктор снимаем галочку "чередующиеся строки":
И получаем вид:
А уже, непосредственно на вкладке "Главная" наводим красоту по Вашему усмотрению (я задала границы, и изменила цвет шрифта и заливку в шапке таблицы):
🔔 Наша сводная таблица практически готова. Но остался один момент.
Сейчас в сводную у нас добавлена только одна таблица из 3-х, а поэтому наша задача добавить оставшиеся запросы.
📍 ШАГ 6.
Добавляем запросы в сводную:
- на вкладке "Запрос" находим "Изменить":
- в открывшемся редакторе запросов вы видите, что данные из первой таблицы у нас уже в запросе:
- добавляем оставшиеся:
Т.к Т-1 уже есть в запросе, то
- из выпадающего списка выбираем Т_2
- жмем ОК
Аналогично добавляем оставшийся запрос Т_3:
Теперь наша сводная содержит все запросы:
На этом этапе редактор запросов можно закрыть, конечно же сохранив изменения:
Управлять свойствами сводной таблицы вы можете перейдя на вкладке "Конструктор" --->> "Свойства"
📍 ШАГ 7.
Вносим данные и обновляем сводную. После того как внесены данные в рабочие таблицы, для получения сведений в сводной остается только нажать на волшебную кнопку "Обновить" или "Обновить все"
И наслаждаться результатом.
📽 ВИДЕООБЗОР по уроку
На этом сегодня все. Спасибо, что вы с нами 💕. Продолжение следует…
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности. За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
#excel #сводные таблицы excel #запросы excel #power query excel #задачи excel
