Сводная таблица из нескольких файлов excel

Добавление графиков/диаграмм

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

Чтобы создать диаграмму/график, нужно:

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

Рис. 11 – выбор типа используемой диаграммы

Кликните на необходимую форму. На листе автоматически отобразится уже заполненная диаграмма:

Рис. 12 – результат добавление диаграммы

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

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

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

Тематические видеоролики:

Microsoft Excel для Начинающих #1

Microsoft Excel для Начинающих #2

Заполнение

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

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

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

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

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

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

Группировка строк в таблице (Power Query)

​ экране все данные​​Отображение и скрытие структурированных​ что попроще.​ загрузке, редактирование или​В​Средства OLAP​ всех остальных несгруппированных​Excel автоматически добавляет вычисляемые​ функция — группировка по​Важно:​.​.​511​124​Данные​нажмите кнопку​ для группировки строк​ во избежание ошибок​ данных​antal10​ создание запроса с​Group By​

​) невозможно. Перед ее​​ элементов в поле.​​ столбцы в сводную​​ времени. Приложение автоматически​​ Если во время удаления​​Нажмите кнопку​Выполните одно или несколько​410​750​в группе​Разгруппировать​ со 2-й по​

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

​В полях, упорядоченных по​ таблицу, используемую для​ обнаруживает связи между​ многоуровневой структуры подробные​Применить стили​ из указанных ниже​1 202​200​Структура​..​

​ 5-ю, у которых​Структурируйте внешнюю группу.​

• ​ стилями​ найти в настройках​

• ​Power Query​ столбца, который вы​

Группировка по одному или нескольким столбцам

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

​1 074​щелкните стрелку под​ ​Важно:​​ имеется итоговая строка​Структурирование внешней группы​Копирование структурированных данных​ панели быстрого доступа,​. Видео показано, что​​ использовали на шаге​​Создавать срезы для иерархий​

Группировка по одному или нескольким столбцам

​ только элементы, имеющие​ и времени

Excel​ и группирует их,​ строки или столбцы​​Для форматирования структурированных данных​​Отображение или скрытие подробных​​750​​2 276​

​ пунктом​​ Если во время разгруппировки​​ 6, выберите строки​Выделите все подчиненные итоговые​Скрытие или удаление многоуровневой​ туда ее можно​ окно​ 1 (по умолчанию)​​ OLAP со сгруппированными​​ одинаковые следующие уровни.​ также автоматически свернет​ когда пользователь добавляет​ с подробными данными​ также можно применять​ данных для группы​​200​​Важно:​Группировать​​ структуры подробные данные​​ со 2-й по​ строки и соответствующие​ структуры​ и вынести.​Редактора запросов​​ выбран в раскрывающемся​​ полями нельзя.​

​ Например, если в​​ данные таким образом,​​ в сводные таблицы​

​ могут также не​​ автоформаты.​​    ​​1 074​​ При выборе не включайте​​, а затем выберите​​ скрыты, строки с​

​ 5-ю. Для группировки​ им строки с​​Создание итогового отчета с​​paradoxs​​отображаются после редактирования​​ списке​​ТолькоExcel 2016 : чтобы отключить​​ поле есть два​ чтобы они отображались​ строки полей времени.​​ отображаться

Для просмотра​К началу страницы​​Чтобы показать подробные данные​​2 276​ итоговую строку J​​Создать структуру​​ подробными данными могут​ строк с 7-й​ данными.​ диаграммой​: Честно сказать не​​ запроса из книги​​Группировать по​

​ группировку по времени​​ уровня «Страна» и​​ в периодах даты​

Агрегирование столбца с помощью агрегатной функции

​ Созданную группу можно​​ данных выделите номера​​Если символы структуры документа​​ группы, нажмите соответствующую​​Важно:​​ (общий итог).​​.​​ также не отображаться.​​ по 9-ю, у​​В приведенном ниже примере​​Убедитесь в том, что​​ нашёл такой кнопки….​​ Excel. Для просмотра​

Выполнение операции со строками

​. При необходимости можно​​ в сводных таблицах​​ «Город», нельзя группировать​​ и времени максимального​​ перетащить в сводную​ ​ или буквы видимых​ ​ (​​ этой группе кнопку​

​  При выделении столбцов​​На вкладке​​Структурирование данных вручную​ Для отображения данных​ которых имеется итоговая​ строка 6 содержит​ в первой строке​

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

​ соседних строк или​​,​​.​​ не включайте в​Данные​Важно:​ перетащите указатель через​​ строка 10, выделите​​ промежуточные итоговые данные​ каждого столбца данных,​​: Эта кнопка нужна?​​без загрузки или​ столбца из раскрывающегося​ в моделях данных)​​ стран.​​Например, если в списке​В сводной таблице щелкните​ столбцов соответственно. На​​и​​Чтобы скрыть подробные данные​​ диапазон данных итоговый​​в группе​​ Группируя уровни структуры вручную,​ номера видимых строк​​ строки с 7-й​

support.office.com>

Шаг 4. Закрепляем заголовок

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

1. Если заголовок расположен только в одной первой строке листа, процесс его фиксации достаточно прост. Переключаемся во вкладку “Вид”, нажимаем кнопку “Закрепить области”, в появившемся перечне выбираем функцию “Закрепить верхнюю строку”.
2. В результате, строка с названием таблицы будет зафиксирована в самом верху листа и никуда не денется при пролистывании документа вниз.

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

1. Выделяем ячейку, которая расположена слева под областью, которую планируется закрепить. В нашем случае мы будем фиксировать текст заголовка вместе с шапкой таблиц, а значит ставим курсор на ячейку A5. Выделив нужную ячейку, нажимаем на кнопку “Закрепить области” (вкладка “Вид”), и из предложенного перечня на этот раз выбираем функцию “Закрепить области”.
2. После этого произойдет фиксация всех строк, расположившихся выше выбранной нами ячейки.

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

Глава 7. Сводные таблицы

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

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

• Дата совершения продажи;
• Наименование товара;
• Наименование покупателя товара;
• Сумма сделки.

Относительно этих данных может возникнуть множество вопросов:

• Какая общая сумма продаж?
• Кто самый активный покупатель?
• Какой самый популярный товар по общей сумме сделки?
• Как распределены продажи в течение года, есть ли сезонность у товаров?
• Растут или падают продажи в течение нескольких лет?

На все эти вопросы помогают ответить сводные таблицы.

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

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

Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.

Поля сводной таблицы

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

Наименование товараПокупательСуммаНазвания строкНазвания столбцов

Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

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

Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель

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

Скачать пример сводных таблиц Excel: svodnie-tablici

Как создать новый лист в Excel

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

Как создать лист в Эксель из строки состояния

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

Рядом с ярлычками существующих листов расположен значок добавления нового. В зависимости от версии Excel выглядит он как знак «+» или дополнительная вкладка справа без названия. Для добавления нового листа необходимо щелкнуть по этому значку.

Щелкните по кнопке «+». Произойдет создание листа в Эксель. По умолчанию, новый лист добавляется в конец вкладок и получает название «Лист n».

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

Как вставить новый лист в Excel из контекстного меню

Основная задача Эксель, работа с таблицами. Вы можете создать новые листы, содержащие диаграммы, графики, макросы, диалоги или встроенные шаблоны.

Чтобы вставить новый лист в Excel (либо перечисленное выше), можно воспользоваться контекстным меню. Сделайте следующее:

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

2. Вызвать контекстное меню правой кнопкой мыши, выбрать строку «Вставить…».

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

4. Нажимаем кнопку «ОК» и видим, что добавился новый лист с названием «Лист2».

Как в Экселе добавить лист из ленты

Разберемся как в Экселе добавить лист из ленты.

1. Переходим во вкладку «Главная». Здесь нас интересует блок с названием «Ячейки».

2. Нажимаем кнопку «Вставить» и выбираем пункт «Вставить лист».

Таким образом, можно в Эксель добавить новый лист

Обратите внимание, что лист будет добавлен перед листом, который открыт в данный момент

Как создать лист в Excel горячими клавишами

На последок оставил мой самый любимый способ создать лист в Excel.

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

«Shift» + «F11»

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

Одновременная вставка нескольких листов

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

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

Сколько было выделено листов, столько новых листов и будет добавлено в книгу Excel. Поэтому, если нужно одновременно вставить 10 листов, а у нас по умолчанию имеется 3 созданных, выделяем 3 имеющихся, добавляем. Теперь у нас 6 листов. Далее, выделяем 4 листа и снова добавляем.

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

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
3. Жмем «Свойства» – открывается перечень настроек.
4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

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

Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:

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

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

Табличный процессор Эксель может справиться даже с самыми объемными и сложными документами. Составить отчет и обобщить данные не составит труда для программы.

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

С помощью вкладки «вставка», которая находится в главном меню панели инструментов программы, выберите клавишу создания рекомендуемых сводных таблиц;

Выбор окна вставки для дальнейшего создания

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

Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт».

Диалоговое окно создания и просмотра предварительных вариантов

• Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
• Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
  Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
  Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);

Пример отфильтрованной таблицы

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

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

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

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

Форма создания персональной пользовательской таблицы-сводки

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

Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.

Проводить анализ внешних данных тоже можно с помощью сводных таблиц.

Советуем прочитать также эти статьи:

• Макросы в Excel — Инструкция по использованию
• Выпадающий список в Excel — Инструкция по созданию
• Как закрепить строку в Excel — Подробные инструкции.

Как создать таблицу в Эксель: Простые приемы форматирования ячеек

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

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

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

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

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

Благодаря контекстной вкладке Конструктор можно увидеть, как будет выглядеть таблица при использовании разных форматов (с помощью оперативного просмотра). Установите указатель мыши над одной из пиктограмм форматов в группе Стили таблиц, и таблица изменится в соответствии с выбранным стилем. Чтобы получить доступ ко всем табличным форматам, используйте полосу вертикальной прокрутки. Щелкните на кнопке Дополнительные параметры — она обозначена горизонтальной полоской над направленным вниз треугольником, — чтобы открыть окно коллекции табличных форматов. Установите указатель мыши над миниатюрой стиля, находящейся в разделе Светлый, Средний или Темный, чтобы увидеть, каким образом изменится внешний вид таблицы.

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

Удаляем сводную таблицу

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

Если сводная таблица расположена на листе, где есть другие данные, самый простой способ ее удалить — выделить полностью сводную таблицу вместе с фильтрами (если они есть) и нажать кнопку Delete.

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

   Сообщество Excel Analytics | обучение Excel

Вам может быть интересно:

• ABC анализ в сводной таблице в Excel
• Чек-лист идеальной сводной таблицы в Excel
• Условное форматирование в сводной таблице Excel
• Как добавить вычисляемое поле в сводную таблицу
• Как создать сводную таблицу из нескольких таблиц Excel

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

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

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

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

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

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.

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

Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

В результате мы имеем вот такую таблицу:

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

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика.

Сортировка и фильтрация

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

Эти инструменты относятся к заголовкам строк и, соответственно, столбцов

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

Если нужно сформировать отчет только по какой-то одной товарной категории (например, «Зелень»), то с помощью фильтра отключаем все ненужные и получаем результат:

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

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

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

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

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

Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?

Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.

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

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

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

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

Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.

Фильтрация полей отчета

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

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

Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.

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

Если позже вы решите отображать расходы и доходы за весь период, снова установите флажок в поле (Все) раскрывающегося списка фильтрации и щелкните на кнопке ОК.

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

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

1. Выбираем ячейку и открываем Мастер сводных таблиц и диаграмм. Если он уже есть на панели быстрого доступа, нужно нажать на соответствующий значок. О том, как добавить инструмент на панель, рассказано в разделе «Использование Мастера сводных таблиц».
2. Данные находятся в двух источниках, поэтому выбираем пункт «В нескольких диапазонах консолидации». Также нужно выбрать разновидность отчета – «сводная таблица».

12

1. На следующем шаге выбираем пункт «Создать поля данных» и нажимаем «Далее».
2. Появится окно со списком диапазонов. Туда можно добавить несколько таблиц. Выделяем мышкой первую таблицу и нажимаем «Добавить», то же делаем со второй таблицей.

13

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

14

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

Проводим дополнительные вычисления

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

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

Меняем структуру квартальных продаж менеджеров на процентнуюСкриншот: Skillbox

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

Сводная таблица самостоятельно рассчитала процент продаж за квартал для каждого менеджераСкриншот: Skillbox Media

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

Так сводная таблица выглядит в свёрнутом видеСкриншот: Skillbox Media

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Как работать со сводными таблицами в Excel

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

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

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

1. Открываем вкладку «Анализ» (в более ранних версиях – «Параметры») и кликаем по кнопке «Источник данных».

19

1. На экране появится начальная таблица, по которой был составлен отчет, и окно для выбора диапазона. Добавляем к ней столбец с новыми данными и выделяем новый диапазон мышью.
2. Обновляем сводную таблицу – в списке появятся новые поля. Их нужно распределить по областям.

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

Если отчет не переведен в динамический формат, все еще есть способы обновить информацию в нем после внесения изменений в источник. Обновим сводную таблицу вручную. Для этого кликнем по любой ячейке отчета и по пункту «Обновить» в контекстном меню. Также можно воспользоваться кнопкой с тем же названием на вкладке «Параметры»/«Анализ».

20

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

1. Выбираем любую ячейку отчета и открываем вкладку «Параметры»/«Анализ». Находим пункт «Сводная таблица» – в нем нужно выбрать пункт «Параметры».

21

1. Находим в настройках пункт «Разметка и формат». Этот раздел состоит из нескольких пунктов. Требуется поставить галочки в графах, показанных на изображении ниже, и нажать кнопку «ОК».

22