Как создать сводную таблицу в Excel: пошаговая {инструкция}

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большая часть юзеров не подозревает, какие способности находятся в их руках. Представим, что сводные таблицы еще не выдумали. Вы работаете в компании, которая реализует свою продукцию разным клиентам. Для простоты в ассортименте лишь 4 позиции. Продукцию часто покупает пара 10-ов клиентов, которые находятся в различных регионах. Любая сделка заносится в базу данных и представляет отдельную строчку.

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

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

Данную табличку необходимо заполнить, т.е. просуммировать выручку по подходящим товарам и регионам. Это несложно создать при помощи функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет создать не по выручке, а по прибыли?

— Можно ли продукты показать по строчкам, а регионы по столбцам?

— Можно ли такие таблицы созодать для всякого менеджера в отдельности?

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

Разглядим, как сделать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

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

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

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

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

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

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

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

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

Поглядим, как это работает в действии. Сделаем пока такую же таблицу, как уже была сотворена при помощи функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строчки перетащим поле «Область» (регион продаж), в Столбцы – «Продукт».

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

На ее построение потребовалось практически 5-10 секунд.

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

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

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

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

Источник данных сводной таблицы Excel

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

1. Наилучший формат для данных – это Таблица Excel. Она хороша тем, что у всякого поля есть наименование и при добавлении новейших строк они автоматом врубаются в сводную таблицу.

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

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

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

В целом требований незначительно, но их необходимо знать.

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

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

либо
через команду во вкладке Данные – Обновить все.

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

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

Ниже находится видеоурок о том, как в Excel сделать ординарную сводную таблицу.

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

Работа в Microsoft Excel почти во всем состоит из построения, дизайна и вычисление таблиц.

Фактически любой, кто открывает Excel сталкивается с задачками сотворения, дизайна, преобразования, сортировки и печати таблиц.

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

Как нарисовать таблицу в эксель

Пользоваться сетью.

Применить все границы.

Если в Вашей таблице уже заполнены данные, то можно создать просто обрамление таблицы excel границами.

Для этого выделите спектр Вашей таблицы, позже зайдите на вкладке «Основная» в «Границы» и там выберете «Все границы»:

Как отформатировать таблицу.

Пожалуй, самый красивый метод оформить таблицу в excel – это создать форматирование таблицы.

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

Как продолжить таблицу в эксель вниз.

Как растянуть таблицу в excel.

Как отменить умную таблицу в excel.

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

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

Как перевернуть таблицу в excel.

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

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

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

Таковым образом в пару кликов можно развернуть таблицу в excel на 90 градусов.

Как сортировать таблицу в excel.

Чтоб отсортировать таблицу в excel по возрастанию довольно поместить курсор вовнутрь столбца по которому требуется упорядочить таблицу в excel, и на вкладке «Данные» в разделе «Сортировка и фильтры» избрать порядок сортировки:

Как сделать таблицу в Excel — пошаговая {инструкция}

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

Как создать таблицу в Экселе?

Создать таблицу в Майкрософт Эксель можно 2-мя методами: вручную, пошагово следуя приведённым ниже советам, и автоматом. Чтоб сделать сводный перечень данных, не будет нужно глядеть обучающие видео; совладать с задачей, как и зафиксировать строчку в Excel, сумеет хоть какой чайник.

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

Автоматом

Эксель может начертить таблицу в стопроцентно автоматическом режиме; для этого начинающему юзеру пригодится:

• Открыть лист Майкрософт Эксель, перейти на вкладку «Вставка» и надавить на клавишу «Таблица».

• Очертить указателем мыши ориентировочные границы заготовки (юзер может сделать «неточную» таблицу, а потом расширить её) и надавить на клавишу «ОК» диалогового окошка.

• Если необходимо — разрешить добавление к списку данных заголовков. Для этого довольно установить галочку в подходящем чекбоксе — это так же просто, как научиться созодать диаграммы в Excel.

• На листе Экселя покажется готовая таблица.

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

• До того времени, пока все заглавия не окажутся на собственных местах.

• Непревзойденно! Юзер научился созодать автоматические таблицы Excel. Остаётся заполнить пустые поля числами и начать работать с информацией.

В ручном режиме

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

• В любом месте листа Excel вписать в ячейках приготовленные данные.

• Отыскать на вкладке «Основная» выпадающий перечень «Границы» и избрать в нём параметр «Все границы».

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

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

Оформление таблицы в Excel

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

Изменение цвета ячеек

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

• Юзер выделяет требуемые блоки.

• Находит на вкладке «Основная» выпадающее меню «Заливка» и выбирает для строк, столбцов и отдельных квадратиков приглянувшиеся цвета.

Совет: возвратить всё «как было» можно, опять вызвав выпадающее меню и кликнув по параметру «Нет заливки».

Изменение высоты и ширины ячеек

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

• Выделить на листе Excel требуемую строчку.

• Щёлкнуть по её порядковому номеру правой кнопкой мыши, избрать пункт «Высота строчки».

• И задать в диалоговом окошке требуемое значение.

• То же сделать для вертикальных рядов данных — лишь заместо «Высоты строчки» кликнуть по пт «Ширина столбца».

• Юзер может устанавливать по горизонтали и вертикали любые значения в границах разумного; чтоб возвратиться к начальным, довольно пользоваться композицией кнопок Ctrl + Z.

Изменение начертания и размера шрифта

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

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

• Рядом, пользуясь клавишами B и I, найти, должен ли шрифт быть полужирным либо наклонным.

• И установить размер знаков — кликнув по подходящему в выпадающем перечне либо воспользовавшись клавишами «Больше/Меньше».

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

Сглаживание надписей

Задать положение в тексте ячейки по высоте и ширине можно последующим образом:

• Выделить нужные области таблицы Экселя правой кнопкой мыши и в открывшемся контекстном меню избрать параметр «Формат ячеек».

• На вкладке «Сглаживание» отметить в выпадающих перечнях метод привязки текста к центральной точке.

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

Изменение стиля таблицы

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

Чтоб поменять стиль в Excel, необходимо:

• Переключиться на вкладку «Конструктор».

• Отметить галочками, должны ли быть выделены жирным 1-ый и крайний столбцы.

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

• А открыв расположенное здесь же выпадающее меню «Стили», обладатель компа сумеет избрать один из тех 10-ов стопроцентно готовых шаблонов — и применить его одним щелчком мыши.

Вопросцы от чайников

Как видно из приведённых примеров, выстроить таблицу в Экселе весьма просто; в заключение будут даны ответы на самые нередко встречающиеся вопросцы от начинающих юзеров.

Как добавить столбец либо строчку?

Чтоб добавить строчку либо столбец в автоматом сделанной таблице Excel, следует:

• Для столбца — выделить заголовок правой кнопкой мыши и избрать в открывшемся меню опцию «Столбцы справа/слева».

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

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

Как посчитать сумму в таблице?

Чтоб отыскать сумму данных в таблице Эксель, необходимо:

• Выделить соответственный столбец, перейти на вкладку «Основная» и надавить на клавишу «Автосумма».

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

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

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

Для автоматической сортировки сведений, содержащихся в таблице Эксель, следует:

• Выделить мышью столбец, открыть выпадающее меню «Сортировка».

• Дальше избрать один из обычных вариантов обработки данных (в вырастающем либо убывающем порядке) либо, включив «Настраиваемую сортировку», задать в новеньком окне характеристики подборки: номер столбца.

• Параметр упорядочивания.

• И порядок: прямой либо оборотный.

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

Как вставить изображение в таблицу?

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

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

Подытоживаем

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