Выборочный сбор строки таблицы. Как сделать выборку в Excel по условию

Выборочный сбор строки таблицы. Как сделать выборку в Excel по условию

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

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

=ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””)))

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

Объединить листы Excel в один онлайн. Слить лист в Excel

Объединение нескольких листов в один рабочий лист по одному — сложная задача, но, к счастью, у нас есть функция под названием «Консолидация» в Excel, а также, начиная с Excel 2010, мы можем использовать «Power Query» как объединение рабочих листов. В этой статье мы покажем вам, как объединить рабочие листы в один.

Получение данных на нескольких листах довольно распространено, но объединение всех данных листа в один — это работа человека, который получает данные на разных листах.

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

Самый простой и быстрый способ объединить данные нескольких листов в один — использовать встроенную функцию Excel. «Объединить . » Например, посмотрите на приведенные ниже данные в таблицах Excel.

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

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

Шаг 1: Создайте новый рабочий лист и назовите его «Сводный лист».

Шаг 2: Поместите курсор в первую ячейку рабочего листа, перейдите на вкладку ДАННЫЕ и нажмите на опцию «Консолидировать».

Шаг 3: Это откроется под «КОНСОЛИДАЦИЯ» окно.

Шаг 4: Поскольку мы консолидируем все данные по 4 регионам, выберите опцию «СУММ» в раскрывающемся списке функций в Excel.

Шаг 5: Далее нам нужно выбрать диапазон ссылок от первого листа до последнего листа. Поместите курсор в поле ссылки, перейдите на лист EAST и выберите данные.

Шаг 6: Нажмите кнопку «ДОБАВИТЬ», чтобы добавить первую справочную область.

Теперь это добавлено в список ссылок.

Шаг 7: Далее вам просто нужно перейти на лист «Юг», и эталонный диапазон будет выбран автоматически.

Шаг 8: Снова нажмите «ДОБАВИТЬ», и вторая ссылка на лист будет добавлена ​​в список. Таким образом повторите то же самое для всех листов.

Теперь мы добавили все 4 листа ссылок. Еще одна вещь, которую нам нужно сделать, наконец, — это выбрать каждый выбранный диапазон регионов, включая заголовок строки и заголовок столбца таблицы данных, поэтому, чтобы внести то же самое в консолидированный лист, установите флажки «Верхний ряд» и «Левая колонка» а также установите флажок «Создайте ссылки на исходные данные».

Хорошо, нажмите «ОК», и у нас будет сводная таблица, как показано ниже.

Как вы можете видеть выше, у нас есть два сгруппированных номера листов: 1 и 2. Если вы нажмете на 1, отобразится вся консолидированная таблица региона, а если вы нажмете 2, то отобразится разбивка каждой зоны.

Выглядит нормально, но это не тот вид слияния рабочих листов, поэтому на самом деле слияние объединяет все рабочие листы в один без каких-либо вычислений; для этого нам нужно использовать опцию Power Query.

Объединение листов с помощью Power Query

Power Query — это надстройка для версий Excel 2010 и 2013, а также встроенная функция для версий Excel 2016 и более поздних версий.

Перейдите на вкладку «Данные» и выберите « Получить данные» из этого выберите «Из файла» тогда, Из книги Excel.

Сводная таблица с текстом в значениях. Отображение различных вычислений в полях значений сводной таблицы

Вместо того чтобы писать собственные формулы в вычисляемых полях, можно использовать функцию " Показать значения как" для быстрого представления значений различными способами. Он также предоставляет несколько новых параметров вычислений, таких как % от родительского итога или %Running Total In .

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

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

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

Примечание:  Поле значения добавляется в сводную таблицу, а номер версии добавляется к имени поля. При необходимости можно изменить имя поля.

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

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

Примечание:  В Excel для Mac меню " Показать значения как" не отображаются все те же параметры, что и в Excel для Windows, но они доступны. Выберите пункт "Дополнительные параметры" в меню, если вы не видите нужный вариант в списке.

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

Доступны следующие варианты вычисления:

Без вычислений	Значение, введенное в данное поле.
% от общей суммы	Отображает значения в процентах от общего итога всех значений или точек данных в отчете.
% от суммы по столбцу	Отображает все значения в каждом столбце или ряду в процентах от общего значения столбца или ряда.
% от суммы по строке	Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Доля	Значения в процентах от значения базового элемента в соответствующем базовом поле .
% от суммы по родительской строке	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле ).
Отличие	Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле .
Приведенное отличие	Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле .
С нарастающим итогом в поле	Значение в виде нарастающего итога для последовательных элементов в базовом поле .
% от суммы с нарастающим итогом в поле	Вычисляет значение в процентах для последовательных элементов в базовом поле, которые отображаются как выполняющиеся итоги.
Сортировка от минимального к максимальному	Отображает ранг выбранных значений в определенном поле, перечисляя наименьший элемент в поле как 1 и каждое большее значение с более высоким значением ранга.
Сортировка от максимального к минимальному	Отображает ранг выбранных значений в определенном поле, перечисляя наибольший элемент в поле как 1 и каждое меньшее значение с более высоким значением ранга.
Индекс	Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

В приведенном выше примере три поля значений были добавлены во второй раз в область значений. к их именам добавлено слово "_2". Ниже поле "Сумма 1/1/14" отображается дважды в отчете сводной таблицы, поэтому вы можете увидеть значение для каждого города и его родительского (восточная или западная область). Вы также увидите процент общего итога для каждого региона (в строках 6 и 9).

Дополнительные сведения

Вы всегда можете задать вопрос специалистуили попросить помощи в сообществе.

Объединить листы Excel в одну книгу. Консолидация данных из нескольких листов на одном.

Самый быстрый способ консолидировать данные в Excel (в одной или нескольких книгах) — использовать встроенную функцию Excel Консолидация .

Рассмотрим следующий пример. Предположим, у вас есть несколько отчетов из региональных офисов вашей компании, и вы хотите объединить эти цифры в основной рабочий лист, чтобы у вас был один сводный отчет с итогами продаж по всем товарам.

Как вы видите на скриншоте ниже, четыре объединяемых листа имеют схожую структуру данных, но разное количество строк и столбцов:

Чтобы объединить всю эту информацию на одном листе, выполните следующие действия:

1. Правильно расположите исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
   • Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не помещайте данные на лист, куда вы планируете выводить консолидированные данные.
   • Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
   • Ни в одном списке нет пустых строк или столбцов.
2. Запустите инструмент «Консолидация». На новом листе, где вы планируете поместить результаты, щелкните верхнюю левую ячейку, начиная с которой должны отображаться консолидированные данные, затем на ленте перейдите на вкладку « Данные » и нажмите кнопку « Консолидация» .

Совет. Желательно объединить данные в пустой лист. Если на вашем основном листе уже есть данные, убедитесь, что имеется достаточно места (пустые строки и столбцы) для записи результатов.

1. Настройте параметры консолидации. Появляется диалоговое окно « Консолидация », и вы делаете следующее:
   • В поле « Функция» выберите одну из функций, которую вы хотите использовать для консолидации данных (количество, среднее, максимальное, минимальное и т. д.). В этом примере мы выбираем Сумма .
   • В справочном окне, нажав в поле Ссылка на значок, выберите диапазон на первом листе. Затем нажмите кнопку « Добавить» , чтобы присоединить его к списку диапазонов. Повторите этот шаг для всех листов, которые вы хотите объединить.

Если один или несколько листов находятся в другой книге, используйте кнопку « Обзор», чтобы найти эту книгу и использовать ее.

1. Настройте параметры обновления . В том же диалоговом окне Консолидация выберите любой из следующих параметров:
   • Установите флажки « Подписи верхней строки » и / или « Значения левого столбца » в разделе « Использовать в качестве имён», если вы хотите, чтобы заголовки строк и / или столбцов исходных диапазонов были также скопированы.
   • Установите флажок « Создать связи с исходными данными », если нужно, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как на следующем скриншоте:

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

Как видите, функция консолидации Excel очень полезна для сбора данных. Однако у нее есть несколько ограничений. В частности, он работает только для числовых значений и всегда обрабатывает эти числа тем или иным образом (сумма, количество, среднее и т. д.). Исходные цифры вы здесь не увидите.

Если вы хотите объединить листы в Excel, просто скопировав и объединив их содержимое, вариант консолидации не подходит. Чтобы объединить всего парочку из них, создав как бы единый массив данных, то вам из стандартных возможностей Excel не подойдёт ничего, кроме старого доброго копирования / вставки.

Но если вам предстоит таким образом обработать десятки листов, ошибки при этом будут практически неизбежны. Да и затраты времени весьма значительны.

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

Excel собрать данные с нескольких листов формула. Спосо. Сборка данных с листов с помощью Power Query

Если вы ещё не сталкивались в своей работе с Power Query, то очень советую копнуть в этом направлении. Использование этой бесплатной и уже встроенной по умолчанию в Excel надстройки, способно полностью перевернуть весь ваш процесс сбора и анализа данных, упростив всё в разы. С задачей сбора данных с листов Power Query справляется весьма успешно.

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query , если у вас Excel 2016 или новее, то вкладку Данные (Data) . Нажмите кнопку Получить данные / Создать запрос - Из файла - Книга Excel (Get Data / New Query - From file - From Excel) и укажите наш файл с исходными листами:

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit) :

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

То, что останется после удаления шагов - это список всех объектов, которые Power Query "видит" во внешних файлах, а это:

• листы (Sheet)
• "умные таблицы" (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

В исходном файле может быть много всего лишнего, что нам не требуется собирать: случайные ненужные листы, служебные именованные диапазоны, побочные умные таблицы и т.п. Очень важно отфильтровать этот "информационный мусор", т.к. в будущем из-за любого подобного объекта наш запорс будет, скорее всего, вылетать с ошибкой или некорректно собирать данные. Для решения этой задачи можно использовать несколько подходов.

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind . Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы , то дополнительно можно отфильтровать ещё по столбцу Hidden .

В-третьих, если вы точно знаете размер таблиц , которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

Для подсчёта количества строк можно использовать аналогичную функцию Table.RowCount . Получившийся столбец затем можно использовать для фильтрации "мусорных" таблиц.

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова " Товар ", то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

Здесь:

- имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю) - имя столбца на листе, из которого мы хотим извлечь данные
• {0} - номер строки (считая с нуля), откуда мы хотим взять данные

После фильтрации "мусора" все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data .

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data , отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь "навести блеск", а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) .
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар .

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка ) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home - Close & Load) :

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl + Alt + .

Плюсы такого подхода:

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

Двссыл сбор данных с нескольких листов. Функция ДВССЫЛ() в EXCEL

Группы статей
• стандартные функции

Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ("Лист1!B3") эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что саму ссылку ( Лист1!B3 ) также можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая строка! С помощью этой функции можно транспонировать таблицы, выводить значения только из четных/ нечетных строк, складывать цифры числа и многое другое.

Функция ДВССЫЛ()

— это текстовая строка в формате ссылки (т.е. указаны столбец и строка): = ДВССЫЛ("B3") или = ДВССЫЛ("Лист1!B3") или =ДВССЫЛ("Лист1!B3") . Первая формула эквивалентна формуле = B3 , вторая - = Лист1!B3 , третья = Лист1!B3 Если какая-либо ячейка (например, А1 ) содержит текстовую строку в формате ссылки (например, Лист1!B3 ), то в ДВССЫЛ() можно указать ссылку на эту ячейку = ДВССЫЛ(А1) Эта запись будет эквивалентна = ДВССЫЛ("Лист1!B3") , которая в свою очередь будет эквивалентна = Лист1!B3 . Зачем все это нужно - читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание : Формат ссылки = Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 - это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =RC, то это ссылка на ячейку С4 .  Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами ).

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Рассмотрим несколько задач

Задача1 - Формируем ссылки на листы

Пусть на листах Лист1 , Лист2 , Лист3 и Лист4 в одних и тех же ячейках находятся однотипные данные (Продажи товаров за квартал) См. файл примера .

Сформируем итоговую таблицу Продажи за год на другом листе. В этой таблице будут присутствовать данные с 4-х листов.

Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии си пронумеруем строки таблицы (см. столбец А).

Чтобы вывести данные с других листов используем формулу =ДВССЫЛ("Лист"&C$9&"!B"&$A10+3)

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1 , Лист2 , Лист3 и Лист4 , и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Вышеуказанная формула в ячейке С12 эквивалентна формуле =ДВССЫЛ("Лист1!B4") , формула в ячейке D12 эквивалентна =ДВССЫЛ("Лист2!B4") , т.е. ссылается на другой лист! Весь смысл использования функции ДВССЫЛ() состоит в том, чтобы написать формулу в ячейке С12 и затем ее скопировать в другие ячейки (вправо и вниз), например с помощью. Теперь данные с 4-х различных листов сведены в 1 таблицу!

Задача2 - ссылки на четные/ нечетные строки

C помощью ДВССЫЛ() можно вывести только четные или нечетные строки из исходной таблицы. В качестве исходной используем предыдущую таблицу Продажи за год .

Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H 12 эквивалентна =ДВССЫЛ("B13")

C помощью формулы =ДВССЫЛ(СИМВОЛ(65+N$26)&$A12*2+10) можно вывести только нечетные строки, а с помощью формулы =ДВССЫЛ(СИМВОЛ(65+B$26)&$A28+11) вообще произвольные строки, номера которых заданы в столбце А .

Задача3 - транспонирование таблиц/ векторов

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

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(C12)+СТРОКА($B$11)-СТОЛБЕЦ($B$11);СТРОКА(C12)-СТРОКА($B$11)+СТОЛБЕЦ($B$11))

О транспонировании таблиц можно.

Примечание : О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.

Задача 4 - использование с именами

Создадим несколько имен .

Имя И мя2 - это, т.е. массив чисел, а не ссылка.

Также массив значений будет возвращать функция СМЕЩ() . см. Имя5 .

Имя Имя3 - это, которая возвращает число, а не ссылку.

Создадим табличку, в которой укажем эти имена. Постараемся найти сумму значений, которые вернут эти имена, использовав формулу =СУММ(ДВССЫЛ(A2)) .

Сборка данных со всех листов книги в одну таблицу. Сводная таблица из нескольких листов

Как ни странно, но начиная с Excel 2007 пропала очевидная возможность создания сводной таблицы из нескольких листов. В Excel 2003 это можно было сделать во время создания простой сводной ( Меню - Данные - Отчет сводной таблицы и диаграммы ), выбрав в меню "Из нескольких диапазонов консолидации". Хоть на офф.сайте Microsoft для версии Excel 2007 и выше и предлагают сделать это при помощи клавиш: ALT + D + P у меня данное сочетание не вызвало никакой реакции со стороны самого Excel ни на русской раскладке, ни на английской. Скорее всего локализация ОС и Офиса так же имеет значение. Но я не стал далее ничего пытаться делать, т.к. знаю более короткий путь, чем изменение локализации. Чтобы создавать сводную из нескольких диапазонов для начала необходимо добавить кнопку на панель быстрого доступа:

(File) - Параметры(Options) - Панель быстрого доступа(Quick Access Toolbar)
• Excel 2007 - Кнопка офис - Параметры Excel(Excel options) - Панель быстрого доступа(Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды(All Commands) . Ищем там Мастер сводных таблиц и диаграмм(PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

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

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

Жмем кнопочку, которую мы так старательно создали на панели быстрого доступа.
НаПервом шагенеобходимо указать в нескольких диапазонах консолидации(Multiple consolidation ranges)

Далее наВтором шагеExcel предложит создать одно поле страницы или создать свои поля страниц. Т.к. Excel как правило создает весьма малопонятные наименования для полей страниц по умолчанию, лучше выбрать второй пункт - Создать поля страницы(I will create the page fields) .
Правда, создать можно будет не более 4-х полей страниц, но как правило для большинства задач этого вполне хватает. К тому же нам это потребуется лишь для того, чтобы задать понятные наименования для полей страницы. А уж для этого 1-го поля за глаза хватит.

Третьим шагомнеобходимо будет указать все диапазоны, из которых планируется создать сводную (диапазоны могут быть как на одном листе, так и на разных листах и даже в разных книгах) :
Количество полей страниц указываем 1(How mane page fields do you want?). Становится активным окно для указания имени поля(Field one). Даем имена для полей, поочередно выделяя их в списке диапазонов:

1. Например на рисунке выше столбец Менеджер расположен в области значений и нет возможности перенести его в область строк или столбцов. Можно лишь убрать его из отображения, выключив в фильтре поля Столбец. Все дело в том, что сводные таблицы, созданные на основе нескольких диапазонов консолидации, содержат только три базовых поля: Строка , Столбец и Значение . Поле Строка всегда создается на основе первого столбца указанного диапазона и может содержать только одну область. Поле Столбец содержит все, что расположено правее первого столбца и комбинирует столбцы всех диапазонов в одно поле заголовков.
2. Тот же столбец Менеджер содержит значения по количеству, но не суммы, т.к. это текстовые поля. Именно из-за этого значения для остальных столбцов тоже отражены не суммами, а количеством. Это легко изменить, поменяв функцию поля на Сумма. В этом случае столбец Менеджер будет содержать нули. Но главная особенность в том, что этот столбец трактуется сводной изначально как значения и не может содержать текст. А это значит, что нет никакой возможности узнать значения из таблицы исходных данных.

Как объединить несколько листов Excel в одну таблицу. Создание сводной таблицы Excel из нескольких листов

Сводная таблица применяется для быстрого анализа большого объема данных. Она позволяет объединять информацию из разных таблиц и листов, подсчитать общий результат. Этот универсальный аналитический инструмент существенно расширяет возможности программы Excel.

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

Сводная таблица в Excel

Для примера используем таблицу реализации товара в разных торговых филиалах.

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.

Самое рациональное решение – это создание сводной таблицы в Excel:

1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
2. В меню «Вставка» выбираем «Сводная таблица».
3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

• Первая строка заданного для сведения данных диапазона должна быть заполнена.
• В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
• В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.



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

Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
3. Следующий этап – «создать поля». «Далее».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».

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

Добавим к нашей исходной таблице еще один столбец, где с помощью простой формулы вычислим номер каждой строки в таблице:

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

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

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

Дополнительно можно отключить общие и промежуточные итоги на вкладке Конструктор - Общие итоги и Промежуточные итоги (Design - Grand Totals, Subtotals) и там же переключить сводную в более удобный табличный макет кнопкой Макет отчета (Report Layout) .

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

Теперь скопируем сводную (на этот же лист или на другой) и вставим как значения, а затем введем в область значений свою формулу, которая будет извлекать код контейнера по номеру строки, найденному в сводной:

Функция ЕСЛИ (IF) , в данном случае, проверяет, чтобы очередная ячейка в сводной была не пустой. Если пустая, то выводим пустую текстовую строку "", т.е. оставляем ячейку незаполненной. Если не пустая, то извлекаем из столбца Контейнер исходной таблицы Поставки содержимое ячейки по номеру строки с помощью функции ИНДЕКС (INDEX) .

Пожалуй, единственным не очень очевидным моментом тут является задвоенное слово Контейнер в формуле. Такая странная форма записи:

… нужна лишь для того, чтобы ссылка на столбец Контейнер была абсолютной (как ссылка со знаками $ для обычных "не умных" таблиц) и не сползала на соседние столбцы при копировании нашей формулы вправо.

В будущем, при изменении данных в исходной таблице Поставки , необходимо не забыть обновить нашу вспомогательную сводную с номерами строк, щелкнув по ней правой кнопкой мыши и выбрав команду Обновить (Refresh) .