Как строить таблицы в excel: Сводная таблица в Excel. Как сделать?

Сводная таблица в Excel. Как сделать?

Автор Влад Каманин На чтение 6 мин. Обновлено 23.07.2020

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

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

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

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

Она содержит данные:

• Даты заказов;
• Регион в котором расположен клиент;
• Тип клиента;
• Клиент;
• Количество продаж;
• Выручка;
• Прибыль.

Теперь, представим, что наш руководитель поставил задачу вычислить:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

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

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

Для создания таблицы выполните следующие действия:

• Выделите любую ячейку в таблице с данными;
• Нажмите на вкладку “Вставка” => “Сводная таблица”:

• Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
  • Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
• Система по умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе “На существующий лист”.

• Нажмите “ОК”.

После нажатия кнопки “ОК” таблица будет создана.

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

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

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

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

Ниже вы узнаете подробней об областях:

• Кэш
• Область “Значения”
• Область “Строки”
• Область “Столбцы”
• Область “Фильтры”

Что такое кэш сводной таблицы

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

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

Кэш данных увеличивает размер Excel-файла.

Область “Значения”

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

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

Область “Строки”

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

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

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

Область “Фильтры”

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

Сводные таблицы в Excel. Примеры

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

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

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

Пример 1. Какой объем выручки у региона Север?

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

• создать сводную таблицу и поле “Регион” перенести в область “Строки”;
• поле “Выручка” разместить в области “Значения”
• задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

• переместить поле “Клиент” в область “Строки”;
• поле “Выручка” разместить в области “Значения”;
• задать финансовый числовой формат ячейкам со значениями.

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

Для этого:

• поместим поле “Регион” в область “Строки”;
• поместим поле “Клиент” в область “Строки” под поле “Регион”;
• зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента “Регион” и “Клиент” в области “Строки” друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

• поле “Выручка” разместим в область “Значения”.

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

Для сортировки данных выполните следующие шаги:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

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

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

Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе “ Сводные таблицы в Excel“. Успей зарегистрироваться по ссылке!

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

Автор Влад Каманин На чтение 4 мин. Обновлено 15.08.2020

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

Кликните по кнопке ниже и мы пришлем Вам Excel файл с примером графика, построенного по данным таблицы на E-mail:

Видеоурок

Как построить простой график в Excel

Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:

На основе этих данных нам нужно нарисовать график. Для этого нам потребуется:

• Выделить данные таблицы, включая даты и курсы валют левой клавишей мыши:

• На панели инструментов перейти во вкладку “Вставка” и в разделе “Диаграммы” выбрать “График”:

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

• Система построила нам график:

Как построить график в Excel на основе данных таблицы с двумя осями

Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:

Для добавления данных курса Евро на наш график необходимо сделать следующее:

• Выделить созданный нами график в Excel левой клавишей мыши и перейти на вкладку “Конструктор” на панели инструментов и нажать “Выбрать данные”:

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

• Готово. График для курсов валют Евро и Доллара построен:

Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:

• Перейти в раздел “Конструктор” на панели инструментов и выбрать пункт “Изменить тип диаграммы”:

• Перейти в раздел “Комбинированная” и для каждой оси в разделе “Тип диаграммы” выбрать подходящий тип отображения данных:

Ниже мы рассмотрим как улучшить информативность полученных графиков.

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

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

• Нажать на графике левой клавишей мыши;
• Нажать на “зеленый крестик” в правом верхнем углу графика;
• Во всплывающем окне поставить галочку напротив пункта “Название диаграммы”:

• Над графиком появится поле с названием графика. Кликните по нему левой клавишей мыши и внесите свое название:

Как подписать оси в графике Excel

Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:

• Щелкните левой клавишей мыши по графику. В правом верхнем углу графика появится “зеленый крестик”, нажав на который раскроются настройки элементов диаграммы:

• Щелкните левой клавишей мыши на пункте “Названия осей”. На графике под каждой осью появятся заголовки, в которые вы можете внести свой текст:

Как добавить подписи данных на графике Excel

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

На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:

• Щелкнуть правой кнопкой мыши по линии графика, на которую хотим добавить данные. В раскрывающемся меню выбрать пункт “Добавить подписи данных”:

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

• Кликните правой кнопкой мыши на любом значении линии графика. В всплывающем окне выберите пункт “Формат подписей данных”:

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

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

 

Настроив все параметры у нас получился такой график курса валют:

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

Инструкция как работать с таблицами в Excel (простыми словами)

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

Отличается работа с таблицами Excel и от построения таблиц в других электронных документах, таких форматов, как Microsoft Word. И все это может несколько напугать новичка. Но, как говорится, не боги горшки обжигали.

Преимущества таблиц Excel

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

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

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

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

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

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

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

1

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

2

Второй – воспользоваться горячими клавишами Ctrl + T. Далее появится небольшое окошко, в котором можно более точно указать диапазон, входящий в таблицу, а также дать Excel понять, что в таблице содержатся заголовки. В качестве них будет выступать первая строка.

3

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

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

Базовые особенности работы с таблицами

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

4

Если вы собираетесь использовать сразу несколько таблиц в вашем документе, то рекомендуем дать более понятные имена. В будущем тогда будет значительно проще понять, какая из них за что отвечает. Особенно это важно при работе с Power Query и Power Pivot. Давайте присвоим таблице имя «Отчет».

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

5

Увидеть название таблицы можно и при ручном вводе формулы.

6

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

7

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

8

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

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

=Отчет[Продажи]

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

9

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

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

Таблицы Excel могут действительно сделать жизнь проще. И одна из причин этого – возможность гибко настраивать ее характеристики. Давайте рассмотрим, как это можно сделать.

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

10

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

11

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

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

Вот маленькая демонстрация того, как новая строка автоматически добавляется к таблице.

12

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

То же касается и новых столбцов.

13

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

14

Но и это еще не все. Можно внести определенные изменения в функционал таблицы.

Внесение настроек в таблицу

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

15

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

16

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

17

Но одна из самых интересных возможностей любой таблицы – срезы.

18

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

19

После этого в отдельной панели появляется перечень уникальных значений определенной колонки.

20

Чтобы применить фильтр, необходимо кликнуть по нужной категории.

21

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

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

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

22

Но не все так просто…

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

1. Нет возможности использовать представления. Простыми словами, нельзя запомнить ряд настроек листа, таких как фильтр, свернутые строки или столбцы и так далее.
2. Нельзя использовать эту книгу одновременно с другим человеком через функцию совместного использования.
3. Возможна лишь вставка окончательных итогов. 
4. В таблице нельзя использовать формулы массивов, что накладывает серьезные ограничения при работе с большими объемами данных.
5. Ячейки в таблице не могут быть объединены. Впрочем, даже в обычном диапазоне эту функцию рекомендуют использовать с осторожностью.
6. Невозможно транспонировать таблицу так, чтобы заголовки располагались в строках. Чтобы это сделать, ее нужно переконвертировать в формат обычного диапазона.

Некоторые дополнительные особенности таблиц

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

Функционал таблиц предусматривает возможность автоматического подсчета итогов. Для этого необходимо выделить нужный столбец + одна ячейка под ним, в которую будет выводиться результат и нажать на кнопку «Сумма» на вкладке «Главная».

2324

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

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

25

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

Таблица и именованный диапазон

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

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

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

= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

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

Выводы

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

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

Оцените качество статьи. Нам важно ваше мнение:

Как создать таблицу в Excel, оформление таблицы

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

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

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

Создание таблица excel, основа основ

Диапазон адресов будет такой-то A1:Z1, к примеру. Данные таблицы по столбцам вносим в диапазон A2:Z100 к примеру. У вас скорей всего будет меньше строк и столбцов.

Если у вас есть столбец с датой, а в ячейке после набора 11.11.2011 получается что-то похожее на 41853, то нужно сделать клик по ячейке правой кнопкой мыши и в контекстном меню выбрать Формат ячейки. Либо нажать ctrl+1. Во второй вкладке выбрать тип дата.

Собственно это нужно будет делать скорей всего со всем столбцом… Если нужно внести число, начинающееся с нуля, например 007 то или в формате ячеек выбираем текстовый формат, либо перед числом пишем одиночную кавычку ‘007 вот так. Очень надёжно. Иначе у вас в ячейке будет просто число 7.

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

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

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

Но, во-первых, легче фильтровать таблицу с первой строки, чем если у вас блок таблицы в другом месте. Кроме того очень удобно в таком виде экспортировать таблицу в access как базу данных, или в sqlite3, если вы будите использовать таблицу excel как базу данных в android. Но это в будущем…)))

В этом видео подробно показано, как создать таблицу в Excel:

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

 С уважением, авторы сайта Компьютерапия

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

Обзор таблиц Excel — служба поддержки Office

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

Узнайте об элементах таблицы Excel

Таблица может включать в себя следующие элементы:

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

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

• Строки с полосами Альтернативная заливка или полосатость строк помогает лучше различать данные.

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

• Итоговая строка После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора таких функций, как СУММ, СРЕДНЕЕ и т. Д.Когда вы выбираете один из этих вариантов, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЙ ИТОГО, которая игнорирует строки, которые были скрыты с помощью фильтра по умолчанию. Если вы хотите включить скрытые строки в свои вычисления, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЙ ИТОГ.

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

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

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

Создать таблицу

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

Чтобы быстро создать таблицу в Excel, сделайте следующее:

1. Выберите ячейку или диапазон данных.

2. Выберите Home > Format as Table .

3. Выберите стиль стола.

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

Также посмотрите видео о создании таблицы в Excel.

Эффективная работа с табличными данными

Excel имеет некоторые функции, которые позволяют эффективно работать с данными таблиц:

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

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

Экспорт таблицы Excel на сайт SharePoint

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

См. Также

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

Проблемы совместимости таблиц Excel

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

в Excel — Easy Excel Tutorial

Вставить сводную таблицу | Перетащите поля | Сортировать | Фильтр | Изменить итоговый расчет | Двумерная сводная таблица

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

Наш набор данных состоит из 213 записей и 6 полей. Идентификатор заказа, продукт, категория, сумма, дата и страна.

Вставьте сводную таблицу

Чтобы вставить сводную таблицу , выполните следующие шаги.

1. Щелкните любую ячейку в наборе данных.

2. На вкладке «Вставка» в группе «Таблицы» щелкните «Сводная таблица».

Появляется следующее диалоговое окно. Excel автоматически выбирает данные за вас. Местоположение по умолчанию для новой сводной таблицы — Новый рабочий лист.

3. Щелкните OK.

Перетащите поля

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

1. Поле продукта в области Строки.

2. Поле «Сумма» в области «Значения».

3. Из поля Страна в область Фильтры.

Ниже представлена ​​сводная таблица. Бананы — наш основной экспортный продукт. Вот насколько простыми могут быть сводные таблицы!

Сортировка

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

1. Щелкните любую ячейку в столбце «Сумма суммы».

2. Щелкните правой кнопкой мыши и выберите «Сортировка, сортировка от наибольшего к наименьшему».

Результат.

Фильтр

Поскольку мы добавили поле «Страна» в область «Фильтры», мы можем фильтровать эту сводную таблицу по стране. Например, какие товары мы больше всего экспортируем во Францию?

1.Щелкните раскрывающийся список фильтров и выберите Франция.

Результат. Яблоки — наш основной экспортный продукт во Францию.

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

Изменить итоговый расчет

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

1. Щелкните любую ячейку в столбце «Сумма суммы».

2. Щелкните правой кнопкой мыши и выберите Параметры поля значений.

3. Выберите тип расчета, который вы хотите использовать. Например, нажмите «Подсчитать».

4. Щелкните OK.

Результат. 16 из 28 заказов во Францию ​​были заказами Apple.

Двумерная сводная таблица

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

1. Из поля Country в область Rows.

2. Поле продукта в области Столбцы.

3. Поле «Сумма» в области «Значения».

4. Поле Категория в область Фильтры.

Ниже представлена ​​двумерная сводная таблица.

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

Excel: Работа с таблицами

Введение

Эта статья опубликована на сайте Microsoft (на голландском языке)

Если вы предпочитаете смотреть видео, вот 45 записей моего январского вебинара «Modern Excel» 2020

С выпуском Excel 2007 Microsoft представила новый концепция работы с таблицами данных.Эта новая функциональность (не на удивление) называется «Таблицы». Фактически, таблицы в Excel 2007 — это преемник функции Excel 2003 «Список» с дополнительной функциональностью.

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

Создание стола

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

Рисунок 1: Выберите область таблицы

Затем на вкладке «Главная» ленты найдите группу «Стили». Нажмите кнопку с надписью «Форматировать как таблицу» (см. Рис. 2):

Рисунок 2: Кнопка «Форматировать как таблицу» в группе стилей на вкладке «Главная».

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

Рисунок 3: Галерея форматов таблиц.

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

Рисунок 4: Диалог с вопросом, какой диапазон ячеек необходимо преобразовать в стол.

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

Рисунок 5: Диапазон ячеек после преобразования в таблицу

Вместе с коллегой по программе Excel MVP Фредериком Ле Гуэном я разработал небольшая надстройка, которая сделает вашу жизнь за столом немного проще.

Инструмент добавляет вкладку на ленту под названием Инструменты таблицы:

И контекстное меню, которое отличается от того, щелкаете ли вы правой кнопкой мыши внутри стол или вне стола:

• В таблице отображаются имена столбцов, чтобы вы могли быстро перейти к столбцу

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

• Если вы используете поле редактирования имени таблицы на вкладке ленты TableTools, он не только переименует таблицу, но и автоматически обновит все запросы, которые используют эту таблицу с новым именем
• Если вы измените имя столбца в таблице, инструмент обновит все Запросы PowerQuery с этим новым именем.
• Обратите внимание, что инструмент имеет улучшенный интерфейс для преобразования диапазона в таблицу, которая позволяет вам давать имя таблице прямо при определении стол
  

Специальные функции стола

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

1. Встроенная функция автофильтрации и сортировки

Если ваша таблица имеет строку заголовка, она всегда будет иметь фильтр и выпадающие списки сортировки на месте в строке заголовка.См. Рисунок 6:

Рисунок 6: раскрывающиеся списки сортировки и фильтрации

2. Простой выбор

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

Рисунок 7: выбор всего столбца данных в таблице

Вы также можете выбрать всю область данных или всю таблицу, щелкнув возле верхнего левого угла стола (указатель мыши изменится на стрелка, указывающая на юго-восток, см. рисунок 8).

Рисунок 8: выбор всех данных в таблице или всей таблицы на расстоянии одного или двух кликов.

3. Строка заголовка остается видимой во время прокрутки

Если ваш стол больше, чем умещается на экране, и вы прокручиваете его вниз, В Excel 2007 появилась приятная новая функция: буквы столбца временно заменены названиями столбцов таблицы (но только пока вы находитесь внутри Таблица!). См. Рисунок 9.

Рисунок 9: Имена заголовков таблиц в заголовке столбца Excel при прокрутке

4.Автоматическое расширение стола

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

5. Автоматическое переформатирование

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

6. Автоматическая корректировка диапазона источников диаграмм и других объектов

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

Параметры стола на ленте

После того, как вы выбрали любую из ячеек в таблице, вы на ленте появится новая вкладка с названием «Работа с таблицами», «Дизайн». Рисунок 10 показано, как будет выглядеть лента после того, как вы перейдете на эту вкладку.

Рисунок 10: Лента после перехода на вкладку «Работа с таблицами».

Каждая группа на этой вкладке обсуждается в следующих параграфах.

Группа недвижимости

Группа свойств (см. Рис. 11 ниже) позволяет выполнять два вещей:

Рисунок 11: Группа свойств на вкладке «Инструменты для таблиц»

1. изменить Имя таблицы

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

2. Изменить размер стола

Щелкните этот элемент управления, чтобы изменить размер таблицы.

Группа инструментов

В этой группе (см. Рисунок 12) есть три элемента управления:

Рисунок 12: Группа Tools на вкладке Table Tools

1. Подведите итоги с помощью сводной таблицы

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

2. Удалить дубликаты

Еще одна новая функция, добавленная в Excel 2007. После щелкнув этот элемент управления, вы увидите диалоговое окно, в котором вы можете выбрать столбцы, которые вы хотите использовать, чтобы определить, в таблице уникальна. (см. рисунок 13)

Рисунок 13: Диалог удаления дубликатов

3. Преобразовать в диапазон

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

Группа данных внешней таблицы

Эта группа (показанная на рисунке 14) полностью посвящена исходным данным таблица и применяется только в том случае, если данные в таблице были импортированы в Excel с помощью базы данных, веб-запроса или списка sharepoint.

Рисунок 14: Группа External Table Data на вкладке Table Tools лента

В этой группе 5 кнопок:

1.Экспорт данных

Фактически, это комбинированная кнопка. Если вы ее нажмете, вам предложат два варианты,

«Экспорт таблицы в список SharePoint» и «Экспорт таблицы в Visio. PivotDiagram «. Что именно это, выходит за рамки этого статья.

2. Обновить

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

3. Свойства диапазона данных

Эта кнопка может использоваться для изменения свойств внешних данных. вы основали свою таблицу на.

4. Открыть в браузере

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

5. Отменить ссылку

Если ваша таблица является списком sharepoint, эта кнопка отключает таблицу из списка.

Группа опций стиля таблицы

В этой группе находятся элементы управления, которые определяют, как стили таблицы применяется к вашей таблице (см. рисунок 15).

Рисунок 15: Группа параметров стиля таблицы на вкладке Инструменты таблицы лента

1. Строка заголовка

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

2. Итоговая строка

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

3. Ряды с полосами

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

4. Первая колонка

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

5. Последний столбец

Форматирует последний столбец вашей таблицы иначе, чем другой. столбцы.

6. Многослойные колонны

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

Группа стилей таблиц

Последняя группа на вкладке «Инструменты для таблиц» позволяет быстро изменять стиль вашей таблицы (см. рисунок 16).

Рисунок 16. Группа «Стили таблиц» на вкладке «Инструменты для таблиц» ленты

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

1. Новый стиль таблицы

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

2. Прозрачный

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

Создание ссылок на ячейки в таблице (структурированные ссылки)

Excel 2007 представляет новый синтаксис для ссылки на ячейки внутри таблицы.Чтобы увидеть, как это работает, щелкните ячейку справа от таблицы, нажмите знак =, введите СУММ (а затем щелкните любую ячейку с данными внутри таблицы. Вы получите такую ​​формулу:

Excel 2007: = СУММ (Таблица3 [[# Эта строка]; [Скидка]])

Этот синтаксис был упрощен в Excel 2010 и 2013:

= СУММ (Таблица3 [@Discount])

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

Table3: Имя вашей таблицы

[#This Row] в Excel 2007, @ в Excel 2010-2013: обозначает данные. происходит из той же строки, в которой ячейка формулы находится в

[Скидка]: столбец внутри таблицы

Еще несколько примеров:

Описание

Excel 2007

Excel 2010, 2013

Вся таблица

= Таблица1

= Таблица1

Та же строка в таблице

= Таблица1 [[# эта строка] [Скидка]]

= Таблица1 [@Discount]

Заголовок таблицы

= Таблица1 [#Headers]

= Таблица1 [#Headers]

Весь стол (2)

= Таблица1 [# Все]

= Таблица1 [# Все]

Итоговая строка таблицы

= Таблица1 [#Totals]

= Таблица1 [#Totals]

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

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

Обращение к таблице из другой книги

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

Обычно вы должны ссылаться на имя диапазона «TableName» в книге «WorkbookName.xls «, используя: [WorkbookName.xls]! TableName
Но хотя таблица представлена ​​именем диапазона, вы не должны использовать синтаксис имени диапазона в качестве источника. Вместо этого вы должны использовать это:

WorkbookName! TableName

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

Заключение

Как вы видели, таблицы — отличное дополнение к функциям Excel. Большинство этих функций уже были частью функции списка Excel 2003.Но таблицы Excel 2007-2013 основаны на этой функции в значительной степени улучшая это. Наиболее важные преимущества:

• Встроенная функция автофильтрации и сортировки (фильтрация и сортировка улучшена в 2007 г.)
• Простой выбор
• Строка заголовка остается видимой во время прокрутки (Новое в 2007 году)
• Автоматическое расширение стола
• Автоматическое переформатирование (Новое в 2007 г.)
• Автоматическая настройка дальности исходных диаграмм и других объектов

Ссылки

Если вас интересует VBA, прочтите о таблицах Excel 2007 и VBA здесь.

Рон де Брюэн написал хорошая надстройка для облегчения работы с таблицами.

Как построить сводную таблицу с моделью данных

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

Обзор

Прежде чем мы зайдем слишком далеко, давайте подпрыгнем на 30 000 футов. Для начала, что такое модель данных? Модель данных предоставляет способ организации таблиц и формул, которые можно использовать в сводной таблице. Модель данных поставляется с Excel 2016+ для Windows и ранее была доступна как надстройка Power Pivot. Остальная часть этой статьи представлена ​​в Excel 2016 для Windows.

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

• Мы можем создать сводную таблицу, в которой используются различные поля из нескольких таблиц.
• Формулы, которые мы можем писать, намного превосходят те, которые доступны в традиционной сводной таблице. Для написания формул используется язык DAX, который предоставляет множество мощных функций.
• Мы можем выбирать строки и столбцы, используя именованные наборы.
• Мы можем напрямую подключиться к источнику данных (вместо того, чтобы копировать / вставить данные в рабочий лист), использовать запрос Get & Transform (для очистки данных до их поступления) и подключиться к нескольким источникам данных (например, к файлу csv , таблица базы данных и книга Excel) в одной модели.
• После создания мы можем просто обновлять отчет в последующих периодах (вместо того, чтобы выполнять весь процесс экспорта, очистки, импорта и слияния в единую таблицу данных).

И это лишь некоторые из основных моментов.

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

Видео

Детали

Наш план — создать сводную таблицу из двух таблиц.Одна таблица данных содержит транзакции, а другая таблица хранит план счетов. Раньше нам нужно было использовать VLOOKUP или что-то еще, чтобы сначала объединить эти таблицы в единую таблицу для использования с традиционной сводной таблицей. Здесь мы будем использовать модель данных. Мы вместе пройдем через эти шаги:

• Включить модель данных
• Импортировать таблицы данных
• Определить отношения
• Создайте сводную таблицу

Приступим.

Включить модель данных

Сначала нам нужно включить надстройку Power Pivot.Если у вас есть Excel 2016+ для Windows, просто щелкните команду ленты Data> Manage Data Model , как показано ниже:

Примечание. В зависимости от размера экрана вы можете видеть только значок, но не метку.

При первом нажатии на нее предлагается включить надстройки:

После того, как вы нажмете «Включить», все будет готово, и вы увидите вкладку ленты Power Pivot. Ура!

Примечание. Если вы используете более раннюю версию Excel для Windows, вам необходимо загрузить и установить бесплатную надстройку Power Pivot с веб-сайта Microsoft и следовать инструкциям по установке для вашей версии Excel.

Импортировать таблицы данных

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

Шаг импорта таблиц данных будет зависеть от того, где находятся ваши исходные данные. Чтобы начать работу, щелкните команду ленты Power Pivot> Управление .Откроется окно Power Pivot, показанное ниже.

Используйте команду Get External Data , чтобы указать на базовый источник данных.

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

Примечание. Если вы создаете модель данных внутри книги, содержащей таблицы, вместо этого вы можете использовать команду Power Pivot> Добавить в модель данных.

Затем мы делаем то же самое, чтобы извлечь данные из файла Excel LookupTable. Обновленное окно Power Pivot показано ниже.

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

Определить отношения

Есть несколько способов определить отношения, но мой любимый способ — использовать визуальную диаграмму. Чтобы переключиться из представления данных (показано выше) и представления диаграммы (показано ниже), просто щелкните команду Home> Diagram View . Теперь мы увидим таблицы с названиями столбцов (вместо транзакций с данными), как показано ниже.

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

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

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

В окне Power Pivot мы просто нажимаем команду PivotTable> PivotTable и выбираем New Worksheet или Existing Worksheet в открывшемся диалоговом окне Create PivotTable .Как только мы нажимаем ОК, бац, мы видим знакомую панель полей сводной таблицы.

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

И, да, мы можем выбирать поля из одной или обеих таблиц для нашего отчета. Например, нам нужно AcctName из LookupTable в Rows и поле Amount из DataTable как Values.И, бац… готово!

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

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

Файлы примеров:

Как использовать таблицы данных Excel

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

Сравнить результаты в таблице данных

с ФЭУ функция, вы можете рассчитать ежемесячный платеж за заем, исходя из процентной ставки, количества периодов и суммы кредита. Если вы измените ставку, периоды или сумму, формула результат изменяется, поэтому вы можете увидеть влияние этих различных переменных.

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

Создание таблицы данных с 1 переменной

В этом примере вы создадите таблицу данных, которая показывает ежемесячный платежи на срок от 1 до 6 лет.Количество платежей будет варьироваться от 12 до 72.

ПРИМЕЧАНИЕ. Ячейки ввода должны находиться на том же листе, что и данные. стол.

Информация о кредите находится в ячейках C2: C4 с количеством платежей. в ячейке C3.

Для настройки таблицы данных:

1. В ячейках B8: B13 введите количество платежей по кредитам на условиях От 1 до 6 лет
2. В ячейке C7 введите функцию PMT, ссылаясь на информацию о ссуде. ячейки: = PMT (C2 / 12, C3, C4)
3. Выберите ячейки B7: C13 — ячейки заголовка и ячейки для результаты
4. На вкладке «Данные» ленты в группе «Работа с данными» щелкните «Что, если». Анализ, а затем щелкните Таблица данных.

5. Щелкните поле ячейки ввода столбца, а затем щелкните ячейку C3, который содержит переменную количества платежей.

6. Щелкните OK, чтобы закрыть диалоговое окно.
7. Выберите ячейки с ежемесячными платежами и отформатируйте их как «Валюта». На снимке экрана ниже формат: «Валюта» с отрицательным числа в квадратных скобках и красным.

8. Щелкните одну из ячеек с рассчитанными ежемесячными платежами и Строка формул показывает, что ячейка содержит функцию ТАБЛИЦА с ячейка C3 в качестве второго аргумента. Фигурные скобки в начале и конец формулы указывают, что это формула массива

Очистить таблицу данных

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

Чтобы удалить таблицу данных с рабочего листа:

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

Для очистки только результирующих значений:

1. Выберите все результирующие значения в таблице данных.
2. На клавиатуре нажмите клавишу Delete

Создание таблицы данных с 2 переменными

В этом примере вы создадите таблицу данных с двумя переменными. Это покажет ежемесячные платежи на срок от 1 до 6 лет, а процентные ставки от 2% до 6%.

ПРИМЕЧАНИЕ. Ячейки ввода должны находиться на том же листе, что и данные. стол.

Информация о кредите находится в ячейках C2: C4, а процентная ставка — в C2, а количество платежей в ячейке C3.

Для настройки таблицы данных:

1. В ячейках B8: B13 введите количество платежей по кредитам на условиях От 1 до 6 лет
2. В ячейках C7: G7 введите процентные ставки от 2% до 6%
3. В ячейке B7 введите функцию PMT, ссылаясь на информацию о ссуде. ячейки: = PMT (C2 / 12, C3, C4)
4. Выберите ячейки B7: G13 — ячейки заголовка и ячейки для результаты
5. На вкладке «Данные» ленты в группе «Работа с данными» щелкните «Что, если». Анализ, а затем щелкните Таблица данных.
6. Щелкните поле ячейки ввода строки, а затем щелкните ячейку C2, которая содержит переменную процентной ставки.
7. Щелкните поле ячейки ввода столбца, а затем щелкните ячейку C3, который содержит переменную количества платежей.

8. Щелкните OK, чтобы закрыть диалоговое окно.
9. Выберите ячейки с ежемесячными платежами и отформатируйте их как «Валюта».На снимке экрана ниже формат: «Валюта» с отрицательным числа в квадратных скобках и красным.

10. Щелкните одну из ячеек с рассчитанными ежемесячными платежами и Строка формул показывает, что ячейка содержит функцию ТАБЛИЦА с ячейка C2 в качестве первого аргумента и C3 в качестве второго аргумента. В фигурные скобки в начале и в конце формулы означают, что это функция массива.

Загрузить образец файла

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

Дополнительные уроки по функциям

СУМ

ВПР

ИНДЕКС и МАТЧ

Счетные функции

КОСВЕННАЯ

Видео о функциях

7 способов найти и удалить повторяющиеся значения в Microsoft Excel

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

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

В этом руководстве мы рассмотрим 7 различных методов поиска и удаления повторяющихся значений из ваших данных.

Видеоурок

Что такое повторяющееся значение?

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

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

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

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

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

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

Удаление повторяющихся значений в данных — очень распространенная задача. Это настолько распространено, что на ленте есть специальная команда.

Выберите ячейку внутри данных, из которой вы хотите удалить дубликаты, перейдите на вкладку Data и щелкните команду Remove Duplicates .

Excel выберет весь набор данных и откроет окно «Удалить дубликаты».

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

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

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

Найдите и удалите повторяющиеся значения с помощью расширенных фильтров

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

Выберите ячейку внутри данных, перейдите на вкладку Data и щелкните команду Advanced filter.

Откроется окно расширенного фильтра.

1. Вы можете выбрать: Отфильтровать список на месте или Копировать в другое место . Фильтрация списка на месте скроет строки, содержащие любые дубликаты, а при копировании в другое место будет создана копия данных.
2. Excel угадывает диапазон данных, но вы можете настроить его в диапазоне списка . Диапазон критериев можно оставить пустым, а поле Копировать в необходимо будет заполнить, если была выбрана опция Копировать в другое место .
3. Установите флажок Только уникальные записи .

Нажмите OK , и вы удалите повторяющиеся значения.

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

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

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

предназначены только для анализа ваших данных, верно?

Вы также можете использовать их для удаления повторяющихся данных!

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

Сначала создайте сводную таблицу на основе ваших данных.Выберите ячейку внутри ваших данных или весь диапазон данных ➜ перейдите на вкладку Insert ➜ выберите PivotTable ➜ нажмите OK в диалоговом окне Create PivotTable.

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

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

1. Выберите опцию Показать в табличной форме .
2. Выберите параметр «Повторить все метки элементов ».

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

Теперь у вас есть сводная таблица, которая имитирует набор данных в виде таблицы!

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

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

Найдите и удалите повторяющиеся значения с помощью Power Query

Power Query — это преобразование данных, поэтому вы можете быть уверены, что он может находить и удалять повторяющиеся значения.

Выберите таблицу значений, из которой вы хотите удалить дубликаты ➜ перейдите на вкладку Data ➜ выберите запрос From Table / Range .

Удаление дубликатов на основе одного или нескольких столбцов

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

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

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

Вы также можете получить доступ к этой команде из вкладки Home ➜ Удалить строки ➜ Удалить дубликаты .

  = Table.Distinct (# "Предыдущий шаг", {"Марка", "Модель"})  

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

Удаление дубликатов на основе всей таблицы

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

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

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

  = Table.Distinct (# "Предыдущий шаг")  

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

Хранить дубликаты в одном столбце или во всей таблице

В Power Query также есть команды для сохранения дубликатов для выбранных столбцов или для всей таблицы.

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

Найти и удалить повторяющиеся значения с помощью формулы

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

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

  = [@Make] & [@Model] & [@Year]  

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

  = TEXTJOIN ("", FALSE, CarList [@ [Make]: [Year]])  

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

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

  = СЧЁТЕСЛИМН ($ E $ 3: E3, E3)  

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

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

Добавьте фильтры в список данных.

• Перейдите на вкладку Data и выберите команду Filter .
• Используйте сочетание клавиш Ctrl + Shift + L.

Теперь вы можете фильтровать по столбцу «Счетчик». Фильтрация по 1 произведет все уникальные значения и удалит все дубликаты.

Затем вы можете выбрать видимые ячейки из получившегося фильтра, чтобы скопировать и вставить в другое место. Используйте сочетание клавиш Alt +; для выбора только видимых ячеек.

Найти и удалить повторяющиеся значения с условным форматированием

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

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

Затем нужно выбрать столбец объединенных данных.

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

Откроется окно условного форматирования повторяющихся значений.

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

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

Теперь, когда значения выделены, вы можете фильтровать повторяющиеся или уникальные значения с помощью опции фильтра по цвету. Обязательно добавьте фильтры к своим данным. Перейдите на вкладку Data и выберите команду Filter или используйте сочетание клавиш Ctrl + Shift + L.

1. Щелкните переключатель фильтра.
2. Выберите в меню Фильтр по цвету .
3. Отфильтруйте цвет, используемый в условном форматировании, чтобы выбрать повторяющиеся значения, или отфильтруйте Без заливки, чтобы выбрать уникальные значения.

Затем вы можете выбрать только видимые ячейки с помощью сочетания клавиш Alt +;.

Найдите и удалите повторяющиеся значения с помощью VBA

В VBA есть встроенная команда для удаления дубликатов в объектах списка.

  Sub RemoveDuplicates ()   Dim DuplicateValues ​​As Range   Установить DuplicateValues ​​= ActiveSheet.ListObjects ("CarList"). Диапазон   DuplicateValues.RemoveDuplicates Columns: = Array (1, 210, 310), Header: Концевой переводник  

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