Как в excel сделать сводную таблицу из нескольких листов excel?

24 ответов на вопрос “Как в excel сделать сводную таблицу из нескольких листов excel?”

  1. MR_woody Ответить

    Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:

    Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
    Чтобы объединить несколько таблиц в списке полей сводной таблицы:
    Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.
    Можно импортировать несколько таблиц из других источников данных, в том числе из текстовых файлов, веб-каналов данных, данных листа Excel и т. д. Вы можете добавить эти таблицы в модель данных в Excel, создать связи между ними, а затем создать сводную таблицу с помощью модели данных.
    Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
    Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.
    Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.
    В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.
    В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.
    Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.

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

    Нажмите кнопку ОК, чтобы начать импорт и заполнить список полей.
    Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.

    Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле “ОбъемПродаж” из таблицы “ФактПродажиЧерезИнтернет”.
    Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
    Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.

    Работа с другими типами баз данных
    Для использования других реляционных баз данных, например Oracle, может понадобиться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
    Вы можете импортировать несколько таблиц из приложения Access. Подробнее об этом можно узнать в учебнике по импорту данных в Excel и созданию модели данных.

    Импорт таблиц из других источников

    Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
    Подключение к базе данных Oracle
    Подключение к базе данных Access
    Подключение к базе данных IBM DB2
    Подключение к базе данных MySQL
    Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
    Добавление данных листа в модель данных с помощью связанной таблицы
    Создание связи между двумя таблицами
    Создание связей в представлении диаграммы

    Использование модели данных для создания новой сводной таблицы

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

    В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.

    Выберите вариант Выбрать подключение.
    На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.

    Нажмите кнопку Открыть, а затем — ОК, чтобы отобразить список полей, содержащий все таблицы в модели.

    Дополнительные сведения о сводных таблицах и модели данных

    Создание модели данных в Excel
    Получение данных с помощью надстройки Power Pivot
    Упорядочение полей сводной таблицы с помощью списка полей
    Создание сводной таблицы для анализа данных на листе
    Создание сводной таблицы для анализа внешних данных
    Изменение диапазона исходных данных для сводной таблицы
    Обновление данных в сводной таблице
    Удаление сводной таблицы

  2. Kesho Ответить

    Типичная задача при обработке информации полученной из разных источников. Типовое решение – взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?
    Однако решение существует! И оно не очень сложное.
    Для решения этой задачи нам понадобиться надстройка ЁXCEL.
    Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

    Важно:
    Количество столбцов во всех таблицах должно быть одинаково;
    Кроме таблиц на листах не должно быть никакой информации.
    Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку “Таблицы”, в выпавшем меню выбираем команду “Объединить таблицы”:

    В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем “ОК”:

    Программа сформирует запрос – объединит таблицы и выведет информационное сообщение:

    Осталось совсем немного. Переходим в главном меню во вкладку “Данные” в разделе “Получение внешних данных” нажимаем кнопку “Существующие подключения”:

    В открывшемся диалоговом окне выбираем “Подключения в этой книге” – “Запрос из Excel Files” и нажимаем “Открыть”:

    В открывшемся диалоговом окне устанавливаем переключатели в положения “Отчет сводной таблицы” и “Новый лист”, нажимаем “ОК”:

    Программа создаст в книге новый лист на который выведет макет сводной таблицы. Обратите внимание – программа создаст в сформированной таблице новый столбец с названиями листов из которых были получены данные:

    Достройте сводную таблицу до необходимого состояния, добавьте в любую из связанных таблиц новую строчку, обновите сводную таблицу – в ней появятся добавленные данные.
    Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код (Как вставлять макросы?):
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.RefreshAll
    End Sub
    Важно:
    Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте – необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).
    Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль “ЭтаКнига” следующий код:
    Private Sub Workbook_Open()
    Dim q As String
    On Error Resume Next
    q = Application.ThisWorkbook.Path & “\” & Application.ThisWorkbook.Name ‘Определяем текущий путь к файлу
    With ActiveWorkbook.Connections(“Запрос из Excel Files”).ODBCConnection ‘Имя запроса
    .Connection = “ODBC;DSN=Excel Files;DBQ=” & q & _
    “;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;” ‘Меняем строку подключения
    End With
    End Sub
    Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

  3. Kagagar Ответить


    В любой момент всё можно вернуть в исходный вид. Для этого нужно кликнуть на иконку в правом верхнем углу этого окошка.

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

    Вставить временную шкалу

    Если вы кликните на соответствующую кнопку на панели инструментов, то, скорее всего, увидите вот такую ошибку. Дело в том, что в нашей таблице нет ячеек, у которых будет формат данных «Дата» в явном виде.

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

    Затем нужно будет построить сводную таблицу.
    Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.

    Затем нас попросят выбрать диапазон значений.

    Для этого достаточно выделить всю таблицу целиком.

    Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

    Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

    Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

    На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».

    Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.

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

    Обновить

    Если вы внесли какие-нибудь изменения в исходные данные и по каким-то причинам это не отобразилось в сводной таблице, вы всегда можете обновить её вручную. Для этого достаточно нажать на соответствующую кнопку на панели инструментов.

    Источник данных

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

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

    Действия

    При помощи этого инструмента вы сможете:
    очистить таблицу;
    выделить;
    переместить её.

    Вычисления

    Если расчетов в таблице недостаточно или они не отвечают вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие варианты.

    К ним относятся:
    вычисляемое поле;

    вычисляемый объект;

    порядок вычислений (в списке отображаются добавленные формулы);

    вывести формулы (информации нет, так как нет добавленных формул).

    Сервис

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

    Показать

    При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.

    Благодаря этому вы сможете:
    настроить отображение боковой панели со списком полей;

    включить или выключить кнопки «плюс/мину»с;

    настроить отображение заголовков полей.

    Конструктор

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

    Можно настроить:
    промежуточные итоги:
    не показывать;
    показывать все итоги в нижней части;
    показывать все итоги в заголовке.

  4. YoungFlexMG Ответить

    Начнем с требований к исходной таблице.
    каждый столбец должен иметь заголовок;
    в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» – названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
    в таблице должны отсутствовать полностью незаполненные строки и столбцы;
    в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
    избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

    Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, …), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
    Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц.
    Несколько облегчит процесс построения Сводной таблицы, тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 (Вставка/ Таблицы/ Таблица). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица. Все поля окна будут автоматически заполнены, нажмите ОК.

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

    В таблице имеются столбцы:
    Товар – наименование партии товара, например, «Апельсины»;
    Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
    Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
    Дата поставки – Дата поставки Товара Поставщиком;
    Регион продажи – Регион, в котором была реализована партия Товара;
    Продажи – Стоимость, по которой удалось реализовать партию Товара;
    Сбыт – срок фактической реализации Товара в Регионе (в днях);
    Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
    Через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен) откорректируем Имя таблицы на «Исходная_таблица».

    Создание Сводной таблицы

    Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
    Имея исходную таблицу в формате EXCEL 2007, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица.

    В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.

    На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).

    Структура Сводной таблицы в общем виде может быть представлена так:

    Заполним сначала раздел Названия строк. Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец – синонимы).

    Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже).
    Теперь поставим галочку в Списке полей у поля Продажи.

    Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
    Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу).
    Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

    Детализация данных Сводной таблицы

    Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

    Обновление Сводной таблицы

    Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице. Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить. Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы, вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить.

    Удаление Сводной таблицы

    Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ – удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

    Изменение функции итогов

    При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы.
    Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество.

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

  5. его любимая Ответить

    А вот с этого раздела статьи, начинается самое интересное. И начнем работу с выбора в меню «Вставка», блок «Таблицы», пиктограмма «Сводная таблица». Не забываем при этом указать курсором базу исходных данных или табличку с которой мы будет делать сводную.

    В открывшимся окне мы выбираем несколько условий создания сводной таблицы – это диапазон нужных исходных данных и куда же следует запихнуть сводную табличку, толи рядышком на одном листе, ну или создать новый лист и на нём разместить. А поскольку курсор уже стоял на таблице, Excel быстро и автоматически определил структуру таблицы и подставил в графу диапазона. Нажимаем «ОК» и получаем:

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

    Вот мы получили и наш первый результат, но он нас не устраивает так как у нас не суммируется количество фруктов которые были проданы, а значит, нам нужно с области «СТРОКИ» перетянуть заголовок столбца «Вес, кг» и у нас создаётся та конструкция сводной таблицы, которую мы хотим.

    Ну вот форма то та, конечно, но вот результат не тот, а именно поле «Вес, кг» собирает по критерию — количество значений, а нам надо суммировать, а значит подводим курсор мыши к области значений «ЗНАЧЕНИЕ» и на указаном поле «Количество по полю Вес, кг», нажимаем левую кнопку мыши вызывая контекстное меню. Нам нужно выбрать последний пункт «Параметр полей значений».

    В возникшем меню мы изменяем «Пользовательское имя» на нормальный вид ну или тот какой нам будет нужен, выбираем нужную операцию по полю, нам нужна «Сумма», а не «Количество». При большой необходимости можно изменить формат ячеек поля, но на данный момент нам это не так уже и нужно.

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

    Видите, наука о том как создаются сводные таблицы не столь сложная, но знать и разбираться в этом вопросе нужно каждому уважающему себя пользователю Excel. Также с помощью сводных таблиц в Excel есть возможность создать уникальный список своих значений.
    Ну что же сводная таблица с выборкой фруктов у нас сделана. Но что же делать если нам нужно и интересно знать, а как же всё-таки происходит движение по странам. Да и любому будет интересно получить данные из сводной таблицы под разными углами, а поскольку мы уже отформатировали таблицу и всё сделали для идеальной работы. Мы просто копируем нашу табличку и в поле необходимой области «СТРОКИ» меняем вычисляемые значения местами. Указываем первым вычисляемым значением «Страна», вот и всё с 1 исходной таблицы данных мы получили 2 сводные таблицы нужных нам данных.

    Еще стоить поговорить о том, что при манипуляциях со сводными таблицами, Excel дополнительно формирует новое меню в панеле управления для работы с данными таблиц:

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

    Но не стоит забывать что данные в нашей таблице самостоятельно не умеют обновляться, а это значит то что при изменении в исходных данных (для примера я просто убрал данные 2 строк) у нас изменился итог, а значится надо сообщить об этом нашей сводной таблице:

    Для этого, в меню «Работа со сводными таблицами» переходим в закладку «Анализ» и нажимаем кнопочку «Обновить» и все данные пересчитались, ввиду изменений, которые мы внесли в нашу исходную таблицу:

    Ну вот в принципе о том как происходит работа со сводными таблицами в Excel и всё, а уж остальное поможет практика и другие статьи моего сайта. О возможностях получения данных с таблиц, вам поможет статья: “Как получить данные со сводной таблицы”. Но тему сводных таблиц я не закрываю так у них есть еще много возможностей, которые я рассмотрю в других статьях и видеоуроках.
    Пример можно взять здесь.
    А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
    Не забудьте поблагодарить автора!
    Золото убило больше душ, чем железо – тел.
    В. Скотт

  6. Celas Ответить

    Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для “линковки” каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.
    Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.
    Функция имеет, по сути, единственный параметр – ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, “А1” в ссылку и возвращает значение ячейки А1.
    Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).
    Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:
    INDIRECT(“‘”&A2&”‘!B:B”)

    Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:
    ‘Название листа’!A1
    Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для “автоматизации” формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:
    ” ‘ ” & A1 & ” ‘!B:B
    где все, что подчеркнуто – текст, а выделено жирным – ссылки.
    ВАЖНО! Обратите внимание на кавычки и конкатенацию (“склеивание”) при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.
    Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.
    COUNTA(INDIRECT(“‘”&A2&”‘!B:B”))-1
    Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один – количество обществ, второй – количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 “Циклы в Excel без VBA”, пишем формулу первого цикла:
    =IF(A2=””;””;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;””;A2+1);A2))
    Затем пишем формулу второго цикла:
    IF(A2=””;””;COUNTIF($A$1:A2;A2))
    Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

  7. Gathis Ответить

    Рекомендуемые сводные таблицы

    На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

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

    Фильтрация с помощью Временной Шкалы

    К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) – интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

    Новый механизм расчета сводных таблиц (Data Object Model)

    В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной “движок” Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

    Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии – построить сводную сразу по нескольким диапазонам данных.

    Связи и сводная по нескольким таблицам

    На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

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

    Если попытаться забросить в одну сводную поля из разных таблиц, то в списке полей появится подсказка о возможной недостающей связи, которую надо создать:

    Функция подсчета количества уникальных элементов

    Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю – Параметры поля) добавится еще одна новая – Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.

    В приведенном выше примере для сравнения подсчитывается общее количество проданных товаров и количество уникальных наименований по каждому городу.

    Экспресс-просмотр

    Тем, кто работает со сводными таблицами, известна простая, но крайне полезная функция: если сделать двойной щелчок по любой ячейке с числовым результатом в области значений, то на отдельный лист будет выведена детализация по этой ячейке. Это позволяет оперативно проверить исходные данные и понять откуда получился такой результат.
    Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.
    При выделении в сводной любой ячейки с числовым результатом рядом появляется смарт-тег Экспресс-просмотра. Нажав на него, можно выбрать нужную нам таблицу (если сводная строится по нескольким таблицам) и интересующее нас поле для детализации. Так, например, если я выделю ячейку с результатами продаж менеджера Иванова и выберу поле Заказчик для детализации:

    … то получу на выходе новую сводную таблицу с подробными результатами продаж именно Иванова по всем заказчикам:

    Недостатки

    Из замеченного в процессе работы:
    В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
    Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как “умных” таблиц.
    Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные – Подключения (Data – Connections).

  8. Mugal Ответить

    Это можно сделать вручную и автоматически.
    Вручную:
    Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
    В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).

    Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).
    Настройка автоматического обновления при изменении данных:
    На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
    Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
    В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

    Некоторые секреты форматирования

    Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
    Группировка по дате в сводной таблице Excel:
    Источник информации – отчет с данными.
    Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.

    Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:

    В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

    Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:

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

    Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».

    Работа с итогами

    У нас есть сводный отчет такого вида:

    Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
    Как в сводной таблице сделать итоги сверху:
    «Работа со сводными таблицами» – «Конструктор».
    На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».

    Получается следующий вид отчета:

    Уже нет той перегруженности, которая затрудняла восприятие информации.
    Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

    Получим отчет без дополнительных сумм:

    Детализация информации

    Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
    В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:

    На новом листе откроется таблица с данными о продажах товара.

    Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

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

    В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
    Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.

    Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.

    Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

    Жмем ОК – сводная таблица меняется.

  9. VideoAnswer Ответить

Добавить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *