Newcomposers.ru

IT Мир
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сведение таблиц в excel

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

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

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

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

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

  1. Заранее откройте исходные файлы
  2. Создайте новую пустую книгу (Ctrl + N)
  3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

  • Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
  • Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

    Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels) . Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

    После нажатия на ОК видим результат нашей работы:

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

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

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

    Такую таблицу можно выполнить двумя разными способами.

    1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
    2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
    Рассмотрим первый способ.

    Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

    Шаг первый.

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

    Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

    Настройка ленты

    либо войти во вкладку

    «Файл» => «Параметры» => «Настройка ленты».

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

    Из списка выбираем «Мастер сводных таблиц и диаграмм»

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

    В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

    Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

    После нажмите «Ок».

    Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

    Мастер сводных на панели

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

    • Кликнуть по кнопке мастера построения сводных таблиц.
    • На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

    Консолидация диапазонов
    Во втором окне выбрать «Создать одно поле страницы»

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

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

    На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

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

    Данный способ заключается в использовании запроса надстройки Power Query.

    О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

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

    Шаг первый.

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

    Шаг два.

    Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

    Power Query из таблицы

    Шаг три.

    Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

    Слияние запросов

    Шаг четыре.

    Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

    Закрыть и загрузить

    Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

    Общие сведения о сводных таблицах в Excel

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

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

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

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

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

    Создав сводную таблицу, Вы можете использовать её для ответа на различные вопросы с помощью перегруппировки или сведения данных. Например, если необходимо ответить на вопрос: “Какова общая сумма продаж по месяцам?”, мы можем придать нашей сводной таблице следующий вид:

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

    1. Выделите таблицу или ячейки (включая заголовки столбцов), содержащие данные, которые необходимо использовать.
    2. На вкладке Вставка щелкните команду Сводная таблица.
    3. В появившемся диалоговом окне Создание сводной таблицы введите необходимые настройки, а затем нажмите OK. В нашем примере мы воспользуемся Таблицей1 в качестве источника данных и поместим сводную таблицу на новом листе.
    4. На новом листе появится пустая сводная таблица и список полей.
    5. После создания сводной таблицы, Вам потребуется решить какие поля необходимо в нее добавить. Каждое поле является заголовком столбца источника данных. В списке полей сводной таблицы установите флажок для каждого поля, которое необходимо добавить. В нашем примере мы хотим подсчитать общую сумму продаж каждого из продавцов, поэтому установим флажки в полях Продавец и Сумма заказа.
    6. Выбранные поля будут добавлены в одну из четырех областей, которые расположены под списком полей. В нашем примере поле Продавец было добавлено в область Строки, в то время как Сумма заказа – в область Значения. Кроме этого, Вы можете щелкнуть по нужному полю и, удерживая левую кнопку мыши, перетащить его в требуемую область.
    7. Сводная таблица вычислит и подведет итоги для выбранных полей. В нашем примере сводная таблица подсчитала общую сумму продаж каждого из продавцов.

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

    Если Вы измените какие-либо данные на рабочем листе источника данных, сводная таблица не обновится автоматически. Чтобы обновить ее вручную, выберите сводную таблицу, перейдите на вкладку Анализ и нажмите Обновить.

    Умные Таблицы Excel – секреты эффективной работы

    В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

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

    Как создать Таблицу в Excel

    В наличии имеется обычный диапазон данных о продажах.

    Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

    Есть горячая клавиша Ctrl+T.

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

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

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

    Структура и ссылки на Таблицу Excel

    Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

    Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

    А также при наборе формулы вручную.

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

    =Отчет[#Все] – на всю Таблицу
    =Отчет[#Данные] – только на данные (без строки заголовка)
    =Отчет[#Заголовки] – только на первую строку заголовков
    =Отчет[#Итоги] – на итоги
    =Отчет[@] – на всю текущую строку (где вводится формула)
    =Отчет[Продажи] – на весь столбец «Продажи»
    =Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

    Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

    Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

    Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

    то она автоматически переделается в

    Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

    А теперь о том, как Таблицы облегчают жизнь и работу.

    Свойства Таблиц Excel

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

    2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

    Очень удобно, не нужно специально закреплять области.

    3. В таблицу по умолчанию добавляется автофильтр, который можно отключить в настройках. Об этом чуть ниже.

    4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.


    Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

    5. Новые столбцы также автоматически включатся в Таблицу.

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

    Помимо указанных свойств есть возможность сделать дополнительные настройки.

    Настройки Таблицы

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

    С помощью галочек в группе Параметры стилей таблиц

    можно внести следующие изменения.

    — Удалить или добавить строку заголовков

    — Добавить или удалить строку с итогами

    — Сделать формат строк чередующимися

    — Выделить жирным первый столбец

    — Выделить жирным последний столбец

    — Сделать чередующуюся заливку строк

    — Убрать автофильтр, установленный по умолчанию

    В видеоуроке ниже показано, как это работает в действии.

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

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

    Однако самое интересное – это создание срезов.

    Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

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

    Для фильтрации Таблицы следует выбрать интересующую категорию.

    Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

    Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

    Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

    Ограничения Таблиц Excel

    Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

    1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

    2. Текущую книгу нельзя выложить для совместного использования.

    3. Невозможно вставить промежуточные итоги.

    4. Не работают формулы массивов.

    5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

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

    Множество других секретов Excel вы найдете в онлайн курсе.

    Металловедение

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

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

    Выход из ситуации существует, к тому же достаточно простой.

    В Excel есть очень полезная функция «ВПР». Порядок ввода данных следующий: ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

    Разберем каждый в отдельности:

    1. Справа от нашей первой таблицы, которую мы составили сами, добавляем столбец.

    2. В первой ячейке столбца вводим «=ВПР(«.

    3. Выделяем первую ячейку в первом столбце («номер плавки»).


    4. Выделим вторую таблицу и нажмем «F4», для того чтобы задать абсолютную адресацию (перед адресом ячеек появились символы «$», а значит, границы таблицы, в которых будет производиться поиск, будут заданы жестко и при переходе к другой строке, т.е. плавке, ссылка на диапазон сохранится).


    5. Вводим номер столбца — «2».
    6. Выбираем логическое выражение «Ложь», т.к. нам нужно точное совпадение номера плавки.


    7. Нажимаем «Enter» — и получаем соответствующее значение временного сопротивления, которое функция «ВПР» достала из второй таблицы.
    8. Далее осталось скопировать формулу во все нижестоящие строки, например, воспользовавшись автозаполнением (навести мышь на правый нижний угол ячейки, нажать левую кнопку и потянуть вниз).
    9. Все готово.

    Хотите знать больше? Тогда советую подписаться на обновления блога.

    Читать еще:  Условное форматирование таблицы в excel
    Ссылка на основную публикацию
    Adblock
    detector